LXAPI.git - Gitblit

			@@ -283,7 +283,7 @@
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
			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
			@@ -397,7 +397,7 @@
			inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
			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
			@@ -997,5 +997,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
			}
			}