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

namespace VueWebCoreApi.DLL.DAL
{
    public class AppProductDAL
    {
        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 DistrInventory(User us)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select distinct  P.materiel_code as code,M.partname as name   from TK_Wrk_Man P
                      left join TMateriel_Info M  on P.materiel_code=M.partcode 
                      where P.status<>'NEW'";
                var data = DapperHelper.selectdata(sql, dynamicParams);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[App查看单据及工艺信息]
        public static ToMessage ProductOrderSearch(User us, string ordertype, string ordercode, string partcode, string startopendate, string endclosedate)
        {
            Dictionary<object, object> dList = new Dictionary<object, object>();
            var dynamicParams = new DynamicParameters();
            string search = "",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[] 
                    search += " and A.wkshp_code in @wkshp_code ";
                    dynamicParams.Add("@wkshp_code", torglist.ToArray());
                }
                
                //根据查询条件,查找符合要求的单据
                switch (ordertype)
                {
                    case "SO": //按销售订单统计
                        if (!string.IsNullOrEmpty(ordercode))
                        {
                            search += "and A.saleOrderCode like '%'+@ordercode+'%' ";
                            dynamicParams.Add("@ordercode", ordercode);
                        }
                        if (!string.IsNullOrEmpty(partcode))
                        {
                            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 (!string.IsNullOrEmpty(startopendate))
                        {
                            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 BB.wo_code as ordercode,BB.materiel_code as partcode,P.partname,P.partspec,E.saleOrderDate as lm_date,sum(BB.plan_qty) as orderqty   
                                from TKimp_Ewo E
                                inner join(
                                  select A.m_po,A.wo_code,A.plan_qty,A.materiel_code
                                  from TK_Wrk_Man A
                                  where A.status not in('NEW','CLOSED') " + search + ") as BB on E.wo=BB.m_po and E.materiel_code=BB.materiel_code left join TMateriel_Info P on E.materiel_code=p.partcode group by BB.wo_code,BB.materiel_code,P.partname,P.partspec,E.saleOrderDate  order by E.saleOrderDate desc";
                        var sorders = DapperHelper.select<AppScheduleKanban>(sql, dynamicParams).AsList();
                        if (sorders.Count == 0)
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.message = "无符合要求单据！";
                            mes.data = null;
                            return mes;
                        }
                        // 批量查询所有工单的步骤信息
                        var soCodes = sorders.Select(o => o.ordercode).Distinct().ToArray();
                        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 in @wo_codes
                                        order by M.wo_code,S.seq ";
                        dynamicParams.Add("@wo_codes", soCodes);
                        var sosteps = DapperHelper.select<StepResult>(sql, dynamicParams);
                        var stepsBySoCode = sosteps.GroupBy(s => s.wo_code).ToDictionary(g => g.Key, g => g.AsEnumerable());
                        // 组装数据
                        foreach (var order in sorders)
                        {
                            if (stepsBySoCode.TryGetValue(order.ordercode, out var orderSteps))
                            {
                                order.children = orderSteps.Select(s => new AppScheduleKanbanSub
                                {
                                    code = s.stepcode,
                                    name = s.stepname,
                                    spec = s.isend,
                                    planqty = s.plan_qty.ToString("F2"),
                                    goodqty = s.good_qty.ToString("F2")
                                }).ToList();

                                var endStep = orderSteps.FirstOrDefault(s => s.isend == "Y");
                                order.ordergoodqty = endStep?.good_qty.ToString("F2") ?? "0";
                            }
                            else
                            {
                                order.children = new List<AppScheduleKanbanSub>();
                                order.ordergoodqty = "0";
                            }
                        }
                        mes.code = "200";
                        mes.count = sorders.Count;
                        mes.data = sorders;
                        mes.message = "查询成功!";
                        break;
                    case "MO": //按生产订单统计
                        if (!string.IsNullOrEmpty(ordercode))
                        {
                            search += "and A.m_po like '%'+@ordercode+'%' ";
                            dynamicParams.Add("@ordercode", ordercode);
                        }
                        if (!string.IsNullOrEmpty(partcode))
                        {
                            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 (!string.IsNullOrEmpty(startopendate))
                        {
                            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 BB.wo_code as ordercode,BB.materiel_code as partcode,P.partname,P.partspec,E.voucherdate as lm_date,sum(BB.plan_qty) as orderqty   
                                from TKimp_Ewo E
                                inner join(
                                  select A.m_po,A.wo_code,A.plan_qty,A.materiel_code
                                  from TK_Wrk_Man A
                                  where A.status not in('NEW','CLOSED') " + search + ") as BB on E.wo=BB.m_po and E.materiel_code=BB.materiel_code left join TMateriel_Info P on E.materiel_code=p.partcode group by BB.wo_code,BB.materiel_code,P.partname,P.partspec,E.voucherdate  order by E.voucherdate desc";
                        var morders = DapperHelper.select<AppScheduleKanban>(sql, dynamicParams).AsList();
                        if (morders.Count == 0)
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.message = "无符合要求单据！";
                            mes.data = null;
                            return mes;
                        }
                        // 批量查询所有工单的步骤信息
                        var moCodes = morders.Select(o => o.ordercode).Distinct().ToArray();
                        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 in @wo_codes
                                        order by M.wo_code,S.seq ";
                        dynamicParams.Add("@wo_codes", moCodes);
                        var mosteps = DapperHelper.select<StepResult>(sql, dynamicParams);
                        var stepsByMoCode = mosteps.GroupBy(s => s.wo_code).ToDictionary(g => g.Key, g => g.AsEnumerable());
                        // 组装数据
                        foreach (var order in morders)
                        {
                            if (stepsByMoCode.TryGetValue(order.ordercode, out var orderSteps))
                            {
                                order.children = orderSteps.Select(s => new AppScheduleKanbanSub
                                {
                                    code = s.stepcode,
                                    name = s.stepname,
                                    spec = s.isend,
                                    planqty = s.plan_qty.ToString("F2"),
                                    goodqty = s.good_qty.ToString("F2")
                                }).ToList();

                                var endStep = orderSteps.FirstOrDefault(s => s.isend == "Y");
                                order.ordergoodqty = endStep?.good_qty.ToString("F2") ?? "0";
                            }
                            else
                            {
                                order.children = new List<AppScheduleKanbanSub>();
                                order.ordergoodqty = "0";
                            }
                        }
                        mes.code = "200";
                        mes.count = morders.Count;
                        mes.data = morders;
                        mes.message = "查询成功!";
                        break;
                    case "PO": //按生产工单统计
                        if (!string.IsNullOrEmpty(ordercode))
                        {
                            search += "and A.wo_code like '%'+@ordercode+'%' ";
                            dynamicParams.Add("@ordercode", ordercode);
                        }
                        if (!string.IsNullOrEmpty(partcode))
                        {
                            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 (!string.IsNullOrEmpty(startopendate))
                        {
                            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 as ordercode,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 A.status<>'CLOSED' " + search+ " group by A.wo_code,T.partcode,T.partname,T.partspec,A.lm_date  order by A.lm_date desc";
                        var orders = DapperHelper.select<AppScheduleKanban>(sql, dynamicParams).AsList();
                        if (orders.Count == 0)
                        {
                            mes.code = "300";
                            mes.count = 0;
                            mes.message = "无符合要求单据！";
                            mes.data = null;
                            return mes;
                        }
                        // 批量查询所有工单的步骤信息
                        var woCodes = orders.Select(o => o.ordercode).Distinct().ToArray();
                        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 in @wo_codes
                                        order by M.wo_code,S.seq ";
                        dynamicParams.Add("@wo_codes", woCodes);
                        var posteps =  DapperHelper.select<StepResult>(sql, dynamicParams);
                        var stepsByWoCode = posteps.GroupBy(s => s.wo_code).ToDictionary(g => g.Key, g => g.AsEnumerable());
                        // 组装数据
                        foreach (var order in orders)
                        {
                            if (stepsByWoCode.TryGetValue(order.ordercode, out var orderSteps))
                            {
                                order.children = orderSteps.Select(s => new AppScheduleKanbanSub
                                {
                                    code = s.stepcode,
                                    name = s.stepname,
                                    spec = s.isend,
                                    planqty = s.plan_qty.ToString("F2"),
                                    goodqty = s.good_qty.ToString("F2")
                                }).ToList();

                                var endStep = orderSteps.FirstOrDefault(s => s.isend == "Y");
                                order.ordergoodqty = endStep?.good_qty.ToString("F2") ?? "0";
                            }
                            else
                            {
                                order.children = new List<AppScheduleKanbanSub>();
                                order.ordergoodqty = "0";
                            }
                        }
                        mes.code = "200";
                        mes.count = orders.Count;
                        mes.data = orders;
                        mes.message = "查询成功!";
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[步骤查询结果映射类]
        // 步骤查询结果映射类
        private class StepResult
        {
            public string wo_code { get; set; }
            public string stepcode { get; set; }
            public string stepname { get; set; }
            public decimal plan_qty { get; set; }
            public decimal good_qty { get; set; }
            public string isend { get; set; }
        }
        #endregion
    }
}