using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Models;
using VueWebCoreApi.Tools;

namespace VueWebCoreApi.DLL.DAL
{
    public class AppAnalyticsDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt

        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组


        #region[App生产看板]
        public static ToMessage ProductionKanban(User us, string startopendate, string endclosedate, string defecttype)
        {
            Dictionary<object, object> dList = new Dictionary<object, object>();
            var dynamicParams = new DynamicParameters();
            string search = "", torg_codelist = "", sql = "";
            try
            {
                //获取当前用户所属组织及所有子节点组织
                mes = TOrganizationRecursion.TOrgCodeSeach(us.storg_code);
                if (mes.code == "300")
                {
                    return mes;
                }
                else
                {
                    torg_codelist = mes.data.ToString(); //获取组织集合
                }
                // --------------生产总览--------------
                string[] torglist = Array.ConvertAll<string, string>(torg_codelist.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] 
                //存储过程名
                // --------------查询工单任务数、报工数、计算工资收入--------------
                sql = @"h_p_JLApp_ProductAnalysisTop";
                dynamicParams.Add("@torglist", mes.data.ToString());
                dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
                var data0 = DapperHelper.selectProcedure(sql, dynamicParams);
                dList.Add("dt0", data0);
                // --------------生产工单--------------
                sql = @"h_p_JLApp_ProductAnalysisCenter";
                dynamicParams.Add("@torglist", mes.data.ToString());
                dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
                var data1 = DapperHelper.selectProcedure(sql, dynamicParams);
                dList.Add("dt1", data1);
                // --------------缺陷统计--------------
                sql = @"h_p_JLApp_ProductAnalysisBottom";
                dynamicParams.Add("@compute", defecttype);
                dynamicParams.Add("@torglist", mes.data.ToString());
                dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
                var data2 = DapperHelper.selectProcedure(sql, dynamicParams);
                dList.Add("dt2", data2);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.data = dList;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[App生产进度看板单据信息]
        public static ToMessage ProductionScheduleKanban(User us, string ordertype, string partcode, string startopendate, string endclosedate)
        {
            Dictionary<object, object> dList = new Dictionary<object, object>();
            List<AppScheduleKanban> list = new List<AppScheduleKanban>();
            var dynamicParams = new DynamicParameters();
            string search = "", search1 = "", sql = "", torg_codelist = "";
            DataTable dt;
            try
            {
                //获取当前用户所属组织及所有子节点组织
                mes = TOrganizationRecursion.TOrgCodeSeachNo(us.storg_code);
                if (mes.code == "300")
                {
                    return mes;
                }
                else
                {
                    torg_codelist = mes.data.ToString(); //获取组织集合
                    string[] torglist = Array.ConvertAll<string, string>(torg_codelist.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] 
                    search1 += " and A.wkshp_code in @wkshp_code ";
                    dynamicParams.Add("@wkshp_code", torglist.ToArray());
                }
                
                //根据查询条件,查找符合要求的单据
                switch (ordertype)
                {
                    case "SO": //按销售订单统计
                        if (partcode != "" && partcode != null)
                        {
                            string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] 
                            search += " and A.materiel_code in @partcode ";
                            dynamicParams.Add("@partcode", selects.ToArray());
                        }
                        if (startopendate != "" && startopendate != null)
                        {
                            search += " and convert(varchar(100),A.saleOrderDate,21)>=@startopendate and convert(varchar(100),A.saleOrderDate,21)<=@endclosedate ";
                            dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                            dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
                        }
                        //获取单据信息
                        sql = @"select distinct saleOrderCode,A.saleOrderDate,sum(A.saleOrderqty) as saleOrderqty
                                from TKimp_Ewo A 
                                where A.saleOrderCode<>'' " + search + " group by A.saleOrderCode,A.saleOrderDate  order by A.saleOrderDate desc";
                        dt = DapperHelper.selectlist(sql, dynamicParams);
                        if (dt.Rows.Count > 0)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                AppScheduleKanban cmp = new AppScheduleKanban();
                                cmp.ordercode = dt.Rows[i]["saleOrderCode"].ToString();
                                cmp.orderqty = dt.Rows[i]["saleOrderqty"].ToString();
                                cmp.children = new List<AppScheduleKanbanSub>();
                                //获取单据下产品完成信息
                                sql = @"select AA.wo,AA.partcode,AA.partname,AA.partspec,AA.saleOrderqty,isnull(BB.good_qty,0) as good_qty  from(
                                        select distinct E.wo,T.partcode,T.partname,T.partspec,sum(E.saleOrderqty) as saleOrderqty   
                                        from TKimp_Ewo E 
                                        left  join TMateriel_Info T on E.materiel_code=T.partcode 
                                        where E.saleOrderCode=@saleOrderCode
                                        group by E.wo,T.partcode,T.partname,T.partspec
                                        ) as AA
                                        left join
                                        (
                                          select M.m_po,sum(S.good_qty) as good_qty    from TK_Wrk_Man M
                                          inner join TK_Wrk_Step S on M.wo_code=S.wo_code 
                                          where  S.isend='Y' and S.good_qty>0
                                          group by M.m_po
                                        ) as BB  on AA.wo=BB.m_po";
                                dynamicParams.Add("@saleOrderCode", dt.Rows[i]["saleOrderCode"].ToString());
                                var data = DapperHelper.selectdata(sql, dynamicParams);
                                decimal good_qty = data.AsEnumerable().Select(d => d.Field<decimal>("good_qty")).Sum();  //报工总数量
                                cmp.ordergoodqty = good_qty.ToString();
                                for (int j = 0; j < data.Rows.Count; j++)
                                {
                                    AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
                                    cbp.code = data.Rows[j]["partcode"].ToString();
                                    cbp.name = data.Rows[j]["partname"].ToString();
                                    cbp.spec = data.Rows[j]["partspec"].ToString();
                                    cbp.planqty = data.Rows[j]["saleOrderqty"].ToString();
                                    cbp.goodqty = data.Rows[j]["good_qty"].ToString();
                                    cmp.children.Add(cbp);
                                }
                                list.Add(cmp);
                            }
                            mes.code = "200";
                            mes.message = "success!";
                            mes.data = list;
                            return mes;
                        }
                        else
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.message = "无符合要求单据！";
                            mes.data = null;
                        }
                        break;
                    case "MO": //按生产订单统计
                        if (partcode != "" && partcode != null)
                        {
                            string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] 
                            search += " and A.materiel_code in @partcode ";
                            dynamicParams.Add("@partcode", selects.ToArray());
                        }
                        if (startopendate != "" && startopendate != null)
                        {
                            search += " and convert(varchar(100),A.createdate,21)>=@startopendate and convert(varchar(100),A.createdate,21)<=@endclosedate ";
                            dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                            dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
                        }
                        //获取单据信息
                        sql = @"select distinct A.wo,A.createdate,sum(A.qty) as orderqty
                                from TKimp_Ewo A  
                                where 1=1  " + search + " group by A.wo,A.createdate  order by A.createdate desc";
                        dt = DapperHelper.selectlist(sql, dynamicParams);
                        if (dt.Rows.Count > 0)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                AppScheduleKanban cmp = new AppScheduleKanban();
                                cmp.ordercode = dt.Rows[i]["wo"].ToString();
                                cmp.orderqty = dt.Rows[i]["orderqty"].ToString();
                                cmp.children = new List<AppScheduleKanbanSub>();
                                //获取单据下产品完成信息
                                sql = @"select distinct E.wo,T.partcode,T.partname,T.partspec,sum(E.qty) as orderqty,isnull(BB.good_qty,0) as good_qty   
                                        from TKimp_Ewo E 
                                        left  join TMateriel_Info T on E.materiel_code=T.partcode 
                                        left  join (
                                          select M.m_po,sum(S.good_qty) as good_qty    from TK_Wrk_Man M
                                          inner join TK_Wrk_Step S on M.wo_code=S.wo_code 
                                          where  S.isend='Y' and S.good_qty>0
                                          group by M.m_po
                                        ) as BB  on E.wo=BB.m_po
                                        where E.wo=@wo
                                        group by E.wo,T.partcode,T.partname,T.partspec,BB.good_qty";
                                dynamicParams.Add("@wo", dt.Rows[i]["wo"].ToString());
                                var data = DapperHelper.selectdata(sql, dynamicParams);
                                decimal good_qty = data.AsEnumerable().Select(d => d.Field<decimal>("good_qty")).Sum();  //报工总数量
                                cmp.ordergoodqty = good_qty.ToString();
                                for (int j = 0; j < data.Rows.Count; j++)
                                {
                                    AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
                                    cbp.code = data.Rows[j]["partcode"].ToString();
                                    cbp.name = data.Rows[j]["partname"].ToString();
                                    cbp.spec = data.Rows[j]["partspec"].ToString();
                                    cbp.planqty = data.Rows[j]["orderqty"].ToString();
                                    cbp.goodqty = data.Rows[j]["good_qty"].ToString();
                                    cmp.children.Add(cbp);
                                }
                                list.Add(cmp);
                            }
                            mes.code = "200";
                            mes.message = "success!";
                            mes.data = list;
                            return mes;
                        }
                        else
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.message = "无符合要求单据！";
                            mes.data = null;
                        }
                        break;
                    case "PO": //按生产工单统计
                        if (partcode != "" && partcode != null)
                        {
                            string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] 
                            search += " and A.materiel_code in @partcode ";
                            dynamicParams.Add("@partcode", selects.ToArray());
                        }
                        if (startopendate != "" && startopendate != null)
                        {
                            search += " and convert(varchar(100),A.lm_date,21)>=@startopendate and convert(varchar(100),A.lm_date,21)<=@endclosedate ";
                            dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                            dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
                        }
                        //获取单据信息
                        sql = @"select distinct A.wo_code,T.partcode,T.partname,T.partspec,A.lm_date,sum(A.plan_qty) as orderqty
                                from TK_Wrk_Man A  
                                left  join TMateriel_Info T on A.materiel_code=T.partcode
                                where 1=1 " + search + search1 + " group by A.wo_code,T.partcode,T.partname,T.partspec,A.lm_date  order by A.lm_date desc";
                        dt = DapperHelper.selectlist(sql, dynamicParams);
                        if (dt.Rows.Count > 0)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                AppScheduleKanban cmp = new AppScheduleKanban();
                                cmp.ordercode = dt.Rows[i]["wo_code"].ToString();
                                cmp.partcode = dt.Rows[i]["partcode"].ToString();
                                cmp.partname = dt.Rows[i]["partname"].ToString();
                                cmp.partspec = dt.Rows[i]["partspec"].ToString();
                                cmp.orderqty = dt.Rows[i]["orderqty"].ToString();
                                cmp.children = new List<AppScheduleKanbanSub>();
                                //获取单据下产品完成信息
                                sql = @"select M.wo_code,T.stepcode,T.stepname,S.plan_qty,S.good_qty,S.isend    
                                        from TK_Wrk_Man M
                                        inner join TK_Wrk_Step S on M.wo_code=S.wo_code 
                                        left  join TStep T on S.step_code=T.stepcode
                                        where  M.wo_code=@wo_code
                                        order by S.seq ";
                                dynamicParams.Add("@wo_code", dt.Rows[i]["wo_code"].ToString());
                                var data = DapperHelper.selectdata(sql, dynamicParams);
                                if (data.Rows.Count > 0)
                                {
                                    cmp.ordergoodqty = data.AsEnumerable().Where(t => t.Field<string>("isend") == "Y").ToList().Select(c => c.Field<decimal>("good_qty")).First().ToString();
                                    for (int j = 0; j < data.Rows.Count; j++)
                                    {
                                        AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
                                        cbp.code = data.Rows[j]["stepcode"].ToString();
                                        cbp.name = data.Rows[j]["stepname"].ToString();
                                        cbp.spec = data.Rows[j]["isend"].ToString();
                                        cbp.planqty = data.Rows[j]["plan_qty"].ToString();
                                        cbp.goodqty = data.Rows[j]["good_qty"].ToString();
                                        cmp.children.Add(cbp);
                                    }
                                    list.Add(cmp);
                                }
                                else
                                {
                                    cmp.ordergoodqty = "0";
                                    list.Add(cmp);
                                }
                            }
                            mes.code = "200";
                            mes.message = "success!";
                            mes.data = list;
                            return mes;
                        }
                        else
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.message = "无符合要求单据！";
                            mes.data = null;
                        }
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

    }
}