FEDApi.git - Gitblit

			@@ -221,12 +221,17 @@


			#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 compute, 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 = "";
			try
			{
			if (compute == "last") //末道工序
			{
			search += "and P.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (wocode != "" && wocode != null)
			{
			search += "and A.wo_code like '%'+@wocode+'%' ";
			@@ -268,12 +273,17 @@
			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后面的字符
			//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,
			@@ -283,11 +293,11 @@
			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 TWoPrteEqp_Stad S on A.wo_code=S.wo and 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;
			where G.group_code<>'' " + search;
			var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
			mes.code = "200";
			mes.Message = "查询成功!";
			@@ -334,12 +344,17 @@
			#endregion

			#region[班组工资报表记录导出]
			public static ToMessage GroupSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
			public static ToMessage GroupSalaryReportExcelSearch(string compute, 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 (compute == "last") //末道工序
			{
			search += "and P.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (wocode != "" && wocode != null)
			{
			search += "and A.wo_code like '%'+@wocode+'%' ";
			@@ -381,12 +396,17 @@
			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后面的字符
			//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 产品规格,
			@@ -394,14 +414,14 @@
			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
			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 TWoPrteEqp_Stad S on A.wo_code=S.wo and 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;
			where G.group_code<>'' " + search;
			DataTable data = DapperHelper.selectdata(sql, dynamicParams);
			data.TableName = "Table"; //设置DataTable的名称
			string msg = DownLoad.DataTableToExcel(data, "班组计件工资报表");
			@@ -423,12 +443,17 @@


			#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)
			public static ToMessage PeopleSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, string rejectstepcode, int startNum, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (compute == "last") //末道工序
			{
			search += "and AA.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (wocode != "" && wocode != null)
			{
			search += "and AA.wo_code like '%'+@wocode+'%' ";
			@@ -470,7 +495,12 @@
			dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
			dynamicParams.Add("@reportclosedate", reportclosedate + " 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 AA.stepcode not in @s1";
			dynamicParams.Add("@s1", s1);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			@@ -479,19 +509,18 @@
			// --------------查询指定数据--------------
			var total = 0; //总条数
			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
			AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,AA.ratio,AA.isend,
			AA.moneys as usermoney,AA.username,AA.report_date
			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
			A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,B.ratio,P.isend,
			(B.report_qtyisnull(S.unprice,0))(B.ratio/100) as moneys,U.username,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 and K.wkshp_code=S.wkspcode
			left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
			@@ -515,12 +544,17 @@
			#endregion

			#region[人员工资明细报表导出]
			public static ToMessage PeopleSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate)
			public static ToMessage PeopleSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, string rejectstepcode)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (compute == "last") //末道工序
			{
			search += "and AA.isend=@isend ";
			dynamicParams.Add("@isend", "Y");
			}
			if (wocode != "" && wocode != null)
			{
			search += "and AA.wo_code like '%'+@wocode+'%' ";
			@@ -562,7 +596,12 @@
			dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
			dynamicParams.Add("@reportclosedate", reportclosedate + " 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 AA.stepcode not in @s1";
			dynamicParams.Add("@s1", s1);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			@@ -570,20 +609,19 @@
			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 班组报工人数
			var sql = @"select AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,AA.stepname as 工序名称,AA.isend as 是否末道工序,
			AA.task_qty as 任务数量,AA.group_code as 生产班组编码,AA.group_name as 生产班组名称,AA.report_qty as 报工数量,AA.unprice as 工序单价,AA.ratio as 分配比例,
			AA.moneys as 计件工资,AA.username as 报工人员,AA.report_date 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
			A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,B.ratio,P.isend,
			(B.report_qtyisnull(S.unprice,0))(B.ratio/100) as moneys,U.username,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 and K.wkshp_code=S.wkspcode
			left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
			@@ -1007,5 +1045,205 @@
			return mes;
			}
			#endregion


			#region[生产进度报表]
			public static ToMessage ProductionScheduleReportSearch(string status, string wocode, string routecode, string routename, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (status != "" && status != null)
			{
			switch (status)
			{
			case "START":
			search += "and AA.status='START' ";
			break;
			case "CLOSED":
			search += "and AA.status='CLOSED' ";
			break;
			default:
			search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
			break;
			}
			}
			if (wocode != "" && wocode != null)
			{
			search += "and AA.wo_code like '%'+@wocode+'%' ";
			dynamicParams.Add("@wocode", wocode);
			}
			if (routecode != "" && routecode != null)
			{
			search += "and AA.route_code like '%'+@routecode+'%' ";
			dynamicParams.Add("@routecode", routecode);
			}
			if (routename != "" && routename != null)
			{
			search += "and AA.route_name like '%'+@routename+'%' ";
			dynamicParams.Add("@routename", routename);
			}
			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 (opendate != "" && opendate != null)
			{
			search += "and AA.lm_date between @opendate and @closedate ";
			dynamicParams.Add("@opendate", opendate + " 00:00:00");
			dynamicParams.Add("@closedate", closedate + " 23:59:59");
			}
			if (search == "")
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select AA.saleOrderCode,AA.m_po,AA.wo_code,
			(case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as status,
			AA.lm_date,AA.route_code,AA.route_name,AA.partcode,AA.partname,AA.partspec,AA.plan_qty,left(AA.concat_name,len(concat_name)-1) as concat_name from
			(
			select E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.route_code,R.name as route_name,P.partcode,P.partname,P.partspec,m.plan_qty,
			(select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
			inner join TStep S on n.step_code=S.stepcode
			where n.wo_code=m.wo_code for xml path('')) as concat_name
			from TK_Wrk_Step m
			inner join TK_Wrk_Man W on m.wo_code=W.wo_code
			left join TKimp_Ewo E on W.m_po=E.wo
			inner join TMateriel_Info P on W.materiel_code=p.partcode
			inner join TFlw_Rout R on W.route_code=R.code
			group by E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
			) 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 ProductionScheduleReportExcelSearch(string status, string wocode, string routecode, string routename, string partcode, string partname, string partspec, string opendate, string closedate)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (status != "" && status != null)
			{
			switch (status)
			{
			case "START":
			search += "and AA.status='START' ";
			break;
			case "CLOSED":
			search += "and AA.status='CLOSED' ";
			break;
			default:
			search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
			break;
			}
			}
			if (wocode != "" && wocode != null)
			{
			search += "and AA.wo_code like '%'+@wocode+'%' ";
			dynamicParams.Add("@wocode", wocode);
			}
			if (routecode != "" && routecode != null)
			{
			search += "and AA.route_code like '%'+@routecode+'%' ";
			dynamicParams.Add("@routecode", routecode);
			}
			if (routename != "" && routename != null)
			{
			search += "and AA.route_name like '%'+@routename+'%' ";
			dynamicParams.Add("@routename", routename);
			}
			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 (opendate != "" && opendate != null)
			{
			search += "and AA.lm_date between @opendate and @closedate ";
			dynamicParams.Add("@opendate", opendate + " 00:00:00");
			dynamicParams.Add("@closedate", closedate + " 23:59:59");
			}
			if (search == "")
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select AA.saleOrderCode as ERP源单号,AA.m_po as ERP生产订单,AA.wo_code as 生产工单号,
			(case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as 工单状态,
			AA.lm_date as 单据日期,AA.route_code as 工艺路线编号,AA.route_name as 工艺路线名称,
			AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.plan_qty as 任务数量,left(AA.concat_name,len(concat_name)-1) as 生产进度 from
			(
			select E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.route_code,R.name as route_name,P.partcode,P.partname,P.partspec,m.plan_qty,
			(select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
			inner join TStep S on n.step_code=S.stepcode
			where n.wo_code=m.wo_code for xml path('')) as concat_name
			from TK_Wrk_Step m
			inner join TK_Wrk_Man W on m.wo_code=W.wo_code
			left join TKimp_Ewo E on W.m_po=E.wo
			inner join TMateriel_Info P on W.materiel_code=p.partcode
			inner join TFlw_Rout R on W.route_code=R.code
			group by E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
			) 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
			}
			}