using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Tools;

namespace VueWebApi.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(string usercode, string startopendate, string endclosedate,string defecttype)
        {
            Dictionary<object, object> dList = new Dictionary<object, object>();
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                // --------------生产总览--------------
                var sql = @"select 
                             sum(CASE AA.clmname WHEN '计划数量' THEN AA.clmqty ELSE 0 END) as 'plan_qty', 
                             sum(CASE AA.clmname WHEN '合格产出' THEN AA.clmqty ELSE 0 END) as 'good_qty', 
                             sum(CASE AA.clmname WHEN '缺陷产出' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
                             from(
                             select  isnull(sum(A.plan_qty),0) as clmqty,'计划数量'as clmname  from TK_Wrk_Man  A
                             where A.lm_date between @startopendate and @endclosedate
                             union all
                             select isnull(sum(P.good_qty),0) as report_qty,'合格产出' as 合格产出  from TK_Wrk_Man  A
                             inner join TK_Wrk_Step P on A.wo_code=P.wo_code  and P.isend='Y'
                             where A.lm_date between @startopendate and @endclosedate
                             union all
                             select (isnull(sum(P.ng_qty),0)+isnull(sum(P.bad_qty),0)) as ngqty,'缺陷产出' as 缺陷产出  from TK_Wrk_Man  A
                             inner join TK_Wrk_Step P on A.wo_code=P.wo_code 
                             where A.lm_date between @startopendate and @endclosedate
                             ) as AA";
                dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
                dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                dList.Add("dt0", data0);
                // --------------生产工单--------------
                var sql1 = @"select sum(AA.cluntqty) as wo_coum,AA.wo_status   from (
                             select COUNT(A.id) as cluntqty,
                             case when A.status='START' then '执行中' when A.status='CLOSED' then '已结束' else '未开始' end as wo_status
                             from TK_Wrk_Man A
                             where A.lm_date between @startopendate and @endclosedate
                             group by A.status
                             ) as AA group by AA.wo_status";
                var data1 = DapperHelper.selectdata(sql1, dynamicParams);
                dList.Add("dt1", data1);
                // --------------缺陷统计--------------
                var sql2 = "";
                switch (defecttype)
                {
                    case "prt": //按产品统计
                         sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.partname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty  from (
                                  select A.plan_qty,M.partname,S.ng_qty,S.bad_qty   from TK_Wrk_Man A
                                  inner join (select wo_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code) S on A.wo_code=S.wo_code
                                  inner join TMateriel_Info M on A.materiel_code=M.partcode
                                  where A.lm_date between @startopendate and @endclosedate 
                                  ) as AA
                                  group by AA.partname
                                  order by defect_qty desc";
                        break;
                    case "stp"://按工序统计
                         sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.stepname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty  from (
                                  select A.plan_qty,T.stepname,S.ng_qty,S.bad_qty   from TK_Wrk_Man A
                                  inner join (select wo_code,step_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code,step_code) S on A.wo_code=S.wo_code
                                  inner join TStep T on S.step_code=T.stepcode
                                  where A.lm_date between @startopendate and @endclosedate 
                                  ) as AA
                                  group by AA.stepname
                                  order by defect_qty desc";
                        break;
                    default:
                        break;
                }
                var data2 = DapperHelper.selectdata(sql2, 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
    }
}