PC计件工资去掉车间关联

yl

2023-03-04 e294dcf5185f22bf1e288472e675680e46c53a52

 VueWebApi/DLL/DAL/ReportManagerDAL.cs

@@ -202,7 +202,7 @@
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "委外报工记录");
                string msg = DownLoad.DataTableToExcel(data, "委外报工记录报表");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
@@ -221,7 +221,7 @@


        #region[班组工资报表记录查询]
        public static ToMessage GroupSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, int startNum, int endNum, string prop, string order)
        public static ToMessage GroupSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -264,9 +264,220 @@
                }
                if (operopendate != "" && operopendate != null)
                {
                    search += "and A.lm_date between @operopendate and @operclosedate ";
                    search += "and B.report_date between @operopendate and @operclosedate ";
                    dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
                    dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
                }
                if (rejectstepcode != "" && rejectstepcode != null) 
                {
                    string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] 
                    search+= "and A.step_code not in @s1";
                    dynamicParams.Add("@s1", s1);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,
                            isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date,B.report_date   
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                            left  join TK_Wrk_Man K on A.wo_code=K.wo_code
                            left  join TGroup G on B.usergroup_code=G.group_code
                            left  join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code 
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TUser U on A.lm_user=U.usercode 
                            where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[班组工资报表记录查看报工人员]
        public static ToMessage GroupSalaryReportSearchUser(string id)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取报工人员
                sql = @"select U.usercode,U.username   from TK_Wrk_RecordSub A
                        inner join TUser U on A.report_person=U.usercode
                        where m_id=@id";
                dynamicParams.Add("@id", id);
                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[班组工资报表记录导出]
        public static ToMessage GroupSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and M.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and M.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and M.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and T.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (groupcode != "" && groupcode != null)
                {
                    search += "and G.group_code=@groupcode ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                if (username != "" && username != null)
                {
                    search += "and U.username like '%'+@username+'%' ";
                    dynamicParams.Add("@username", username);
                }
                if (operopendate != "" && operopendate != null)
                {
                    search += "and B.report_date between @operopendate and @operclosedate ";
                    dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
                    dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
                }
                if (rejectstepcode != "" && rejectstepcode != null)
                {
                    string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[] 
                    search += "and A.step_code not in @s1";
                    dynamicParams.Add("@s1", s1);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select distinct A.wo_code as 工单编号,M.partcode as 产品编码,M.partname as 产品名称,M.partspec as 产品规格,
                            T.stepcode as 工序编码,T.stepname as 工序名称,A.task_qty as 任务数量,G.group_code as 班组编码,G.group_name as 班组名称,
                            A.good_qty as 报工数量,isnull(S.unprice,0) as 工序单价,A.good_qty*isnull(S.unprice,0) as 计件工资,U.username as 操作人员,A.lm_date as 操作时间,B.report_date as 报工时间 
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                            left  join TK_Wrk_Man K on A.wo_code=K.wo_code
                            left  join TGroup G on B.usergroup_code=G.group_code
                            left  join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code 
                            left  join TMateriel_Info M on A.materiel_code=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TUser U on A.lm_user=U.usercode 
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "班组计件工资报表");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[人员工资明细报表]
        public static ToMessage PeopleSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and AA.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and AA.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and AA.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (groupcode != "" && groupcode != null)
                {
                    search += "and AA.group_code=@groupcode ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                if (reportname != "" && reportname != null)
                {
                    search += "and AA.username like '%'+@reportname+'%' ";
                    dynamicParams.Add("@reportname", reportname);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and AA.report_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }

                if (search == "")
@@ -276,16 +487,24 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,isnull(S.unprice,0) as unprice,U.username as lm_user,A.lm_date  
                var sql = @"select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,
                            AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,
                            AA.moneys/colum as usermoney,AA.username,AA.report_date,colum
                            from (
                            select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
                            A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
                            B.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
                            (select distinct count(*)   from TK_Wrk_RecordSub S  where S.m_id=B.m_id) as colum
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                            left  join TK_Wrk_Man K on A.wo_code=K.wo_code
                            left  join TGroup G on B.usergroup_code=G.group_code
                            left  join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
                            left  join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code 
                            left  join TMateriel_Info M on A.materiel_code=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TUser U on A.lm_user=U.usercode 
                            left  join TUser U on B.report_person=U.usercode
                            ) as AA
                            where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
@@ -303,5 +522,499 @@
            return mes;
        }
        #endregion

        #region[人员工资明细报表导出]
        public static ToMessage PeopleSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and AA.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and AA.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and AA.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (groupcode != "" && groupcode != null)
                {
                    search += "and AA.group_code=@groupcode ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                if (reportname != "" && reportname != null)
                {
                    search += "and AA.username like '%'+@reportname+'%' ";
                    dynamicParams.Add("@reportname", reportname);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and AA.report_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,AA.stepname as 工序名称,
                            AA.task_qty as 任务数量,AA.group_code as 生产班组编码,AA.group_name as 生产班组名称,AA.report_qty as 报工数量,AA.unprice as 工序单价,
                            AA.moneys/colum as 计件工资,AA.username as 报工人员,AA.report_date as 报工时间,colum as 班组报工人数
                            from (
                            select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
                            A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
                            B.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
                            (select distinct count(*)   from TK_Wrk_RecordSub S  where S.m_id=B.m_id) as colum
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                            left  join TK_Wrk_Man K on A.wo_code=K.wo_code
                            left  join TGroup G on B.usergroup_code=G.group_code
                            left  join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code 
                            left  join TMateriel_Info M on A.materiel_code=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TUser U on B.report_person=U.usercode
                            ) as AA
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "人员工资明细报表");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[不良明细报表]
        public static ToMessage DefectDetailsReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string defectcode, string defectname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and M.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and M.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and M.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and T.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (defectcode != "" && defectcode != null)
                {
                    search += "and F.code like '%'+@defectcode+'%' ";
                    dynamicParams.Add("@defectcode", defectcode);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and F.name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (reportname != "" && reportname != null)
                {
                    search += "and U.username like '%'+@reportname+'%' ";
                    dynamicParams.Add("@reportname", reportname);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and A.lm_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
                            F.code as defect_code,F.name as defect_name,A.style,U.username as lm_user,A.lm_date   
                            from CSR_WorkRecord_Defect A
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                            left  join TMateriel_Info M on A.partnumber=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TDefect F on A.defect_code=F.code
                            left  join TUser U on A.lm_user=U.usercode
                            where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[不良明细报表导出]
        public static ToMessage DefectDetailsReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string defectcode, string defectname, string reportname, string reportopendate, string reportclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and M.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and M.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and M.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and T.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (defectcode != "" && defectcode != null)
                {
                    search += "and F.code like '%'+@defectcode+'%' ";
                    dynamicParams.Add("@defectcode", defectcode);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and F.name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (reportname != "" && reportname != null)
                {
                    search += "and U.username like '%'+@reportname+'%' ";
                    dynamicParams.Add("@reportname", reportname);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and A.lm_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.wo_code as 工单编号,M.partcode as 产品编码,M.partname as 产品名称,M.partspec as 产品规格,T.stepcode as 工序编码,
                            T.stepname as 工序名称,P.plan_qty as 任务数量,A.defect_qty as 不良数量,F.code as 缺陷代码,F.name as 缺陷名称,
                            (case when A.style='B' then '报工' when A.style='S' then '收料'  end) as '操作类型',U.username as 操作人员,A.lm_date as 操作时间  
                            from CSR_WorkRecord_Defect A
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code 
                            left  join TMateriel_Info M on A.partnumber=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TDefect F on A.defect_code=F.code
                            left  join TUser U on A.lm_user=U.usercode
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "不良明细报表");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[品质异常排行报表(取报工不良记录缺陷分组汇总条数)]
        public static ToMessage QuaneryDefectReportSearch()
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取报工不良分组统计数据
                sql = @"select AA.cont,AA.name  from (
                        select count(*) cont,F.name   from CSR_WorkRecord_Defect  A
                        left  join TDefect F on A.defect_code=F.code
                        group by F.code,F.name
                        ) as AA order by AA.cont desc";
                var data = DapperHelper.selecttable(sql);
                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[维修明细报表]
        public static ToMessage MaintenanceDetailsReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string style, string defectname, string repairname, string repairopendate, string repairclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and AA.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and AA.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and AA.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (style != "" && style != null)
                {
                    search += "and AA.style=@style ";
                    dynamicParams.Add("@style", style);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and AA.defect_name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (repairname != "" && repairname != null)
                {
                    search += "and AA.lm_user like '%'+@repairname+'%' ";
                    dynamicParams.Add("@repairname", repairname);
                }
                if (repairopendate != "" && repairopendate != null)
                {
                    search += "and AA.lm_date between @repairopendate and @repairclosedate ";
                    dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
                    dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,(case when AA.style='B' then '报工' when AA.style='S' then '收料' end) as style,
                            AA.plan_qty,AA.repair_qty,AA.bad_qty,AA.defect_code,AA.defect_name,AA.lm_user,AA.lm_date
                            from(
                            select F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.bad_qty,F.defect_code,
                            defect_name = STUFF(( SELECT ',' + T.name
                            FROM TDefect as T
                            where PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')>0
                            ORDER BY PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')
                            FOR XML PATH('')), 1, 1,''),U.username as lm_user,F.lm_date
                            from CSR_WorkRecord_DefectHandle as F
                            inner join TK_Wrk_Step P on F.wo_code=P.wo_code and F.step_code=P.step_code 
                            left  join TMateriel_Info M on F.partnumber=M.partcode
                            left  join TStep S on F.step_code=S.stepcode
                            left  join TUser U on F.lm_user=U.usercode
                            ) as AA
                            where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[维修明细报表导出]
        public static ToMessage MaintenanceDetailsReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string style, string defectname, string repairname, string repairopendate, string repairclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and AA.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and AA.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and AA.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (style != "" && style != null)
                {
                    search += "and AA.style=@style ";
                    dynamicParams.Add("@style", style);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and AA.defect_name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (repairname != "" && repairname != null)
                {
                    search += "and AA.lm_user like '%'+@repairname+'%' ";
                    dynamicParams.Add("@repairname", repairname);
                }
                if (repairopendate != "" && repairopendate != null)
                {
                    search += "and AA.lm_date between @repairopendate and @repairclosedate ";
                    dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
                    dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,AA.stepname as 工序名称,
                            (case when AA.style='B' then '报工' when AA.style='S' then '收料' end) as 操作类型,
                            AA.plan_qty as 任务数量,AA.repair_qty as 维修数量,AA.bad_qty as 报废数量,AA.defect_name as 缺陷名称,AA.lm_user as 维修人员,AA.lm_date as 维修时间
                            from(
                            select F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.bad_qty,F.defect_code,
                            defect_name = STUFF(( SELECT ',' + T.name
                            FROM TDefect as T
                            where PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')>0
                            ORDER BY PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')
                            FOR XML PATH('')), 1, 1,''),U.username as lm_user,F.lm_date
                            from CSR_WorkRecord_DefectHandle as F
                            inner join TK_Wrk_Step P on F.wo_code=P.wo_code and F.step_code=P.step_code 
                            left  join TMateriel_Info M on F.partnumber=M.partcode
                            left  join TStep S on F.step_code=S.stepcode
                            left  join TUser U on F.lm_user=U.usercode
                            ) as AA
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "维修明细报表");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}