班组、个人计件工资接口修改

yl

2022-11-16 5d90ebd84612c61c7c2903a7b2c979116cc74a48

 VueWebApi/DLL/DAL/ReportManagerDAL.cs

@@ -264,7 +264,7 @@
                }
                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");
                }
@@ -276,7 +276,8 @@
                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  
                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 and P.isend='Y'
@@ -376,7 +377,7 @@
                }
                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");
                }
@@ -390,7 +391,7 @@
                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 操作时间 
                            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 and P.isend='Y'
@@ -430,42 +431,42 @@
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and M.partcode like '%'+@partcode+'%' ";
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and M.partname like '%'+@partname+'%' ";
                    search += "and AA.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and M.partspec like '%'+@partspec+'%' ";
                    search += "and AA.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and T.stepname like '%'+@stepname+'%' ";
                    search += "and AA.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (groupcode != "" && groupcode != null)
                {
                    search += "and G.group_code=@groupcode ";
                    search += "and AA.group_code=@groupcode ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                if (reportname != "" && reportname != null)
                {
                    search += "and U.username like '%'+@reportname+'%' ";
                    search += "and AA.username like '%'+@reportname+'%' ";
                    dynamicParams.Add("@reportname", reportname);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and B.report_date between @reportopendate and @reportclosedate ";
                    search += "and AA.report_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }
@@ -477,7 +478,14 @@
                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,B.report_qty,isnull(S.unprice,0) as unprice,B.report_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,B.report_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'
@@ -487,6 +495,7 @@
                            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;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
@@ -514,42 +523,42 @@
            {
                if (wocode != "" && wocode != null)
                {
                    search += "and A.wo_code like '%'+@wocode+'%' ";
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (partcode != "" && partcode != null)
                {
                    search += "and M.partcode like '%'+@partcode+'%' ";
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                }
                if (partname != "" && partname != null)
                {
                    search += "and M.partname like '%'+@partname+'%' ";
                    search += "and AA.partname like '%'+@partname+'%' ";
                    dynamicParams.Add("@partname", partname);
                }
                if (partspec != "" && partspec != null)
                {
                    search += "and M.partspec like '%'+@partspec+'%' ";
                    search += "and AA.partspec like '%'+@partspec+'%' ";
                    dynamicParams.Add("@partspec", partspec);
                }
                if (stepname != "" && stepname != null)
                {
                    search += "and T.stepname like '%'+@stepname+'%' ";
                    search += "and AA.stepname like '%'+@stepname+'%' ";
                    dynamicParams.Add("@stepname", stepname);
                }
                if (groupcode != "" && groupcode != null)
                {
                    search += "and G.group_code=@groupcode ";
                    search += "and AA.group_code=@groupcode ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                if (reportname != "" && reportname != null)
                {
                    search += "and U.username like '%'+@reportname+'%' ";
                    search += "and AA.username like '%'+@reportname+'%' ";
                    dynamicParams.Add("@reportname", reportname);
                }
                if (reportopendate != "" && reportopendate != null)
                {
                    search += "and B.report_date between @reportopendate and @reportclosedate ";
                    search += "and AA.report_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }
@@ -561,9 +570,14 @@
                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 生产班组名称,B.report_qty as 报工数量,isnull(S.unprice,0) as 工序单价,
                            B.report_qty*isnull(S.unprice,0) as 计件工资,U.username as 报工人员,B.report_date as 报工时间  
                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 and P.isend='Y'
@@ -573,6 +587,7 @@
                            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的名称
@@ -1004,7 +1019,7 @@
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    search += "and A.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (calltypecode != "" && calltypecode != null)
@@ -1049,7 +1064,7 @@
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.wkshp_code,T.org_name as wkshp_name,A.eqp_code,E.name as eqp_name,Y.name as typename,
@@ -1058,7 +1073,7 @@
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '小时'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '分'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '秒' AS respondcont,
                            (case when A.resp_user is null then '待响应' else '以响应' end) as status  
                            (case when A.resp_user is null then '待响应' else '已响应' end) as status  
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TEqpInfo E on A.eqp_code=E.code
@@ -1090,7 +1105,7 @@
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    search += "and A.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (calltypecode != "" && calltypecode != null)
@@ -1135,7 +1150,7 @@
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.wkshp_code as 车间编码,T.org_name as 车间名称,A.eqp_code as 设备编码,E.name as 设备名称,Y.name as 呼叫类型,
@@ -1144,7 +1159,7 @@
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '小时'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '分'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '秒' AS 响应时长,
                            (case when A.resp_user is null then '待响应' else '以响应' end) as 状态  
                            (case when A.resp_user is null then '待响应' else '已响应' end) as 状态  
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TEqpInfo E on A.eqp_code=E.code
@@ -1171,7 +1186,7 @@


        #region[安灯报表汇总]
        public static ToMessage AnDonReportSumSearch(string wkshopcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
        public static ToMessage AnDonReportSumSearch(string wkshopcode,string eqpcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -1179,8 +1194,13 @@
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    search += "and A.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code=@eqpcode ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (calltypecode != "" && calltypecode != null)
                {
@@ -1204,17 +1224,18 @@
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select T.org_name as wkshp_name,Y.name as calltypename,
                            (select count(*) callcount  from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as callcount,
                            (select count(*) repondcount  from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as repondcount,
                            (select CAST(DATEDIFF(ss, start_date,resp_date)/60 as INT)  from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.type=A.type and CC.status='CLOSED') as repondtime
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TAnDonType Y on A.type=Y.code
                            where T.description='W' "+search+" group by A.wkshp_code,T.org_name,A.type,Y.name ";
                var sql = @"select top 100 percent T.org_name as wkshp_name,A.eqp_code,E.name as eqp_name,Y.name as calltypename,
                             (select count(*) callcount  from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as callcount,
                             (select count(*) repondcount  from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as repondcount,
                             (select ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) as repondtime
                             from TAnDon_Task_Info A
                             left join TOrganization T on A.wkshp_code=T.org_code
                             left join TAnDonType Y on A.type=Y.code
                             left join TEqpInfo E on A.eqp_code=E.code
                             where T.description='W' " + search+" group by A.wkshp_code,T.org_name,A.type,Y.name,A.eqp_code,E.name order by T.org_name,A.eqp_code ";
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -1233,7 +1254,7 @@
        #endregion

        #region[安灯报表汇总导出]
        public static ToMessage AnDonReportSumExcelSearch(string wkshopcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate)
        public static ToMessage AnDonReportSumExcelSearch(string wkshopcode,string eqpcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
@@ -1241,8 +1262,13 @@
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    search += "and A.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code=@eqpcode ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (calltypecode != "" && calltypecode != null)
                {
@@ -1266,17 +1292,18 @@
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select T.org_name as 生产车间,Y.name as 呼叫类型,
                            (select count(*) callcount  from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as 呼叫次数,
                            (select count(*) repondcount  from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as 响应次数,
                            (select CAST(DATEDIFF(ss, start_date,resp_date)/60 as INT)  from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.type=A.type and CC.status='CLOSED') as 响应时长(分)
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TAnDonType Y on A.type=Y.code
                            where T.description='W' " + search + " group by A.wkshp_code,T.org_name,A.type,Y.name ";
                var sql = @"select top 100 percent T.org_name as 生产车间,E.name as 设备名称,Y.name as 呼叫类型,
                             (select count(*) callcount  from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.eqp_code=A.eqp_code and AA.type=A.type) as 总呼叫次数,
                             (select count(*) repondcount  from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.eqp_code=A.eqp_code and BB.type=A.type and BB.status='CLOSED') as 总响应次数,
                             (select ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) 总响应时长
                             from TAnDon_Task_Info A
                             left join TOrganization T on A.wkshp_code=T.org_code
                             left join TAnDonType Y on A.type=Y.code
                             left join TEqpInfo E on A.eqp_code=E.code
                             where T.description='W' " + search+" group by A.wkshp_code,T.org_name,A.type,Y.name,A.eqp_code,E.name order by T.org_name,A.eqp_code ";
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "安灯汇总报表");