LXAPI.git - Gitblit

			@@ -124,7 +124,8 @@
			//获取最大单据号
			if (i == 1) //首单获取工单号
			{
			sql = @"select isnull(max(substring(wo_code,charindex('_',wo_code)+1,len(wo_code)-charindex('_',wo_code))),0)+1 as worknumb from TK_Wrk_Man where m_po=@erpordercode";
			sql = @"select isnull(max(cast(substring(wo_code,charindex('_',wo_code)+1,len(wo_code)-charindex('_',wo_code)) as numeric)),0)+1 as worknumb
			from TK_Wrk_Man where m_po=@erpordercode";
			dynamicParams.Add("@erpordercode", erpordercode);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			num = Convert.ToInt32(data.Rows[0]["WORKNUMB"].ToString());
			@@ -384,6 +385,83 @@
			}
			#endregion

			#region[MES报废补单工单查询]
			public static ToMessage MesBadOrderSearch(string mesordercode, string sourceorder, string partcode, string partname, string partspec, int startNum, string creatuser, string createdate, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (mesordercode != "" && mesordercode != null)
			{
			search += "and A.wo_code like '%'+@mesordercode+'%' ";
			dynamicParams.Add("@mesordercode", mesordercode);
			}
			if (sourceorder != "" && sourceorder != null)
			{
			search += "and A.m_po like '%'+@sourceorder+'%' ";
			dynamicParams.Add("@sourceorder", sourceorder);
			}
			if (partcode != "" && partcode != null)
			{
			search += "and A.materiel_code like '%'+@partcode+'%' ";
			dynamicParams.Add("@partcode", partcode);
			}
			if (partname != "" && partname != null)
			{
			search += "and B.partname like '%'+@partname+'%' ";
			dynamicParams.Add("@partname", partname);
			}
			if (partspec != "" && partspec != null)
			{
			search += "and B.partspec like '%'+@partspec+'%' ";
			dynamicParams.Add("@partspec", partspec);
			}
			if (createdate != "" && createdate != null)
			{
			search += "and CONVERT(varchar(100),A.lm_date,23)=@createdate ";
			dynamicParams.Add("@createdate", createdate);
			}
			if (creatuser != "" && creatuser != null)
			{
			search += "and U.username like '%'+@creatuser+'%' ";
			dynamicParams.Add("@creatuser", creatuser);
			}

			if (search == "")
			{
			search = "and 1=1 ";
			}
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.org_name as wkshp_name,
			A.route_code,E.name as route_name,A.stck_code,F.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,U.username as lm_user,A.lm_date,S.bad_qty
			from TK_Wrk_Man A
			left join (select wo_code,sum(bad_qty) as bad_qty from TK_Wrk_Step where bad_qty>0 group by wo_code) S on A.wo_code=S.wo_code
			left join TMateriel_Info B on A.materiel_code=B.partcode
			left join TOrganization C on A.wkshp_code=C.org_code
			left join T_Sec_Stck D on A.stck_code=D.code
			left join TFlw_Rout E on A.route_code=E.code
			left join T_Sec_Stck F on A.stck_code=F.code
			left join TUser U on A.lm_user=U.usercode
			where A.is_delete<>'1' and A.status='START' and A.wotype='PO' and S.bad_qty>0 " + 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 PartSelectRoute(string partcode)
			{
			@@ -530,6 +608,33 @@
			}
			}
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[MES工单新增、获取工单号]
			public static ToMessage AddMesOrderCodeSearch()
			{
			string sql = "";
			string wo_code = "";
			var dynamicParams = new DynamicParameters();
			try
			{
			//获取单据号
			sql = @"SELECT 'SGPO'+CONVERT(varchar(12) , getdate(), 112 )+'_'+cast(isnull(max(cast(substring(wo_code,charindex('_',wo_code)+1,len(wo_code)-charindex('_',wo_code)) as numeric)),0)+1 as varchar) as numct
			FROM TK_Wrk_Man where wo_code like '%SGPO%'";
			var data = DapperHelper.selecttable(sql);
			mes.code = "200";
			mes.Message = "查询成功!";
			mes.data = data.Rows[0]["numct"].ToString();
			}
			catch (Exception e)
			{
			@@ -2502,102 +2607,102 @@
			#region[生产管理,修改报工数据查询接口]
			public static ToMessage MesOrderStepVerifySearch(string verify, string wo_code, string partnumber, string partname, string partspec, string reportuser, string reportdateopendate, string reportdateclosedate, int startNum, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			if (verify != "" && verify != null)
			var dynamicParams = new DynamicParameters();
			string search = "";
			try
			{
			search += "and AA.verify=@verify ";
			dynamicParams.Add("@verify", verify);
			}
			if (wo_code != "" && wo_code != null)
			{
			search += "and AA.wo_code like '%'+@wo_code+'%' ";
			dynamicParams.Add("@wo_code", wo_code);
			}
			if (partnumber != "" && partnumber != null)
			{
			search += "and AA.partnumber like '%'+@partnumber+'%' ";
			dynamicParams.Add("@partnumber", partnumber);
			}
			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 (reportuser != "" && reportuser != null)
			{
			search += "and AA.usercode like '%'+@reportuser+'%' ";
			dynamicParams.Add("@reportuser", reportuser);
			}
			if (reportdateopendate != "" && reportdateopendate != null)
			{
			search += "and AA.report_date between @reportdateopendate and @reportdateclosedate ";
			dynamicParams.Add("@reportdateopendate", reportdateopendate + " 00:00:00");
			dynamicParams.Add("@reportdateclosedate", reportdateclosedate + " 23:59:59");
			}
			if (verify != "" && verify != null)
			{
			search += "and AA.verify=@verify ";
			dynamicParams.Add("@verify", verify);
			}
			if (wo_code != "" && wo_code != null)
			{
			search += "and AA.wo_code like '%'+@wo_code+'%' ";
			dynamicParams.Add("@wo_code", wo_code);
			}
			if (partnumber != "" && partnumber != null)
			{
			search += "and AA.partnumber like '%'+@partnumber+'%' ";
			dynamicParams.Add("@partnumber", partnumber);
			}
			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 (reportuser != "" && reportuser != null)
			{
			search += "and AA.usercode like '%'+@reportuser+'%' ";
			dynamicParams.Add("@reportuser", reportuser);
			}
			if (reportdateopendate != "" && reportdateopendate != null)
			{
			search += "and AA.report_date between @reportdateopendate and @reportdateclosedate ";
			dynamicParams.Add("@reportdateopendate", reportdateopendate + " 00:00:00");
			dynamicParams.Add("@reportdateclosedate", reportdateclosedate + " 23:59:59");
			}


			if (search == "")
			{
			search = "and 1=1 ";
			if (search == "")
			{
			search = "and 1=1 ";
			}
			search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定自制报工外协收料数据--------------
			var total = 0; //总条数
			var sql = @"select * from(
			select A.id,B.id as sbid,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,A.task_qty,M.wkshp_code,G.org_name as wkshp_name,A.eqp_code,E.name as eqp_name,
			M.route_code,R.name as route_name,A.step_seq,A.step_code,S.stepname,S.flwtype,D.first_choke,D.last_choke,T.unprice,B.report_person as usercode,U.username,
			B.report_date,B.report_qty,B.ng_qty,B.bad_qty,B.verify,B.bad_money
			from TK_Wrk_Record A
			inner join TK_Wrk_RecordSub B on A.id=B.m_id
			left join TK_Wrk_Man M on A.wo_code=M.wo_code
			left join TStep S on A.step_code=S.stepcode
			left join TWoPrteEqp_Stad T on A.wo_code=T.wo and A.materiel_code=T.materiel_code and A.eqp_code=T.eqp_code and A.step_code=T.step_code and M.route_code=T.route_code
			left join TMateriel_Info P on A.materiel_code=P.partcode
			left join TOrganization G on M.wkshp_code=G.org_code
			left join TEqpInfo E on A.eqp_code=E.code
			left join TFlw_Rout R on M.route_code=R.code
			left join TFlw_Rtdt D on M.route_code=D.rout_code and A.step_code=D.step_code
			left join TUser U on B.report_person=U.usercode
			where A.style='B' and B.style='B' and M.status<>'CLOSED'
			) as AA where" + search;
			//union all
			//select A.id,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,M.plan_qty as task_qty,M.wkshp_code,G.org_name as wkshp_name,A.wx_code as eqp_code,E.name as eqp_name,
			//M.route_code,R.name as route_name,A.step_code,S.stepname,S.flwtype,D.first_choke,D.last_choke,T.unprice,'' as usergroup_code,'' as usergroup_name,B.in_person as usercode,U.username,
			//B.in_time as report_date,B.sqty as report_qty,B.ng_qty,B.bad_qty
			//from TK_Wrk_OutRecord A
			//inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
			//left join TK_Wrk_Man M on A.wo_code = M.wo_code
			//left join TStep S on A.step_code = S.stepcode
			//left join TPrteEqp_Stad T on A.materiel_code = T.materiel_code and M.route_code = T.route_code and A.step_code = T.step_code and A.wx_code = T.eqp_code
			//left join TMateriel_Info P on A.materiel_code = P.partcode
			//left join TOrganization G on M.wkshp_code = G.org_code
			//left join TCustomer E on A.wx_code = E.code and E.btype = 'WX'
			//left join TFlw_Rout R on M.route_code = R.code
			//left join TFlw_Rtdt D on M.route_code = D.rout_code and A.step_code = D.step_code
			//left join TUser U on B.in_person = U.usercode
			//where A.style = 'S' and B.style = 'S' and M.status<>'CLOSED'
			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();
			}
			search = search.Substring(3);//截取索引2后面的字符
			// --------------查询指定自制报工外协收料数据--------------
			var total = 0; //总条数
			var sql = @"select * from(
			select A.id,B.id as sbid,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,A.task_qty,M.wkshp_code,G.org_name as wkshp_name,A.eqp_code,E.name as eqp_name,
			M.route_code,R.name as route_name,A.step_seq,A.step_code,S.stepname,S.flwtype,D.first_choke,D.last_choke,T.unprice,B.report_person as usercode,U.username,
			B.report_date,B.report_qty,B.ng_qty,B.bad_qty,B.verify,B.bad_money
			from TK_Wrk_Record A
			inner join TK_Wrk_RecordSub B on A.id=B.m_id
			left join TK_Wrk_Man M on A.wo_code=M.wo_code
			left join TStep S on A.step_code=S.stepcode
			left join TWoPrteEqp_Stad T on A.wo_code=T.wo and A.materiel_code=T.materiel_code and A.eqp_code=T.eqp_code and A.step_code=T.step_code and M.route_code=T.route_code
			left join TMateriel_Info P on A.materiel_code=P.partcode
			left join TOrganization G on M.wkshp_code=G.org_code
			left join TEqpInfo E on A.eqp_code=E.code
			left join TFlw_Rout R on M.route_code=R.code
			left join TFlw_Rtdt D on M.route_code=D.rout_code and A.step_code=D.step_code
			left join TUser U on B.report_person=U.usercode
			where A.style='B' and B.style='B' and M.status<>'CLOSED'
			) as AA where" + search;
			//union all
			//select A.id,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,M.plan_qty as task_qty,M.wkshp_code,G.org_name as wkshp_name,A.wx_code as eqp_code,E.name as eqp_name,
			//M.route_code,R.name as route_name,A.step_code,S.stepname,S.flwtype,D.first_choke,D.last_choke,T.unprice,'' as usergroup_code,'' as usergroup_name,B.in_person as usercode,U.username,
			//B.in_time as report_date,B.sqty as report_qty,B.ng_qty,B.bad_qty
			//from TK_Wrk_OutRecord A
			//inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
			//left join TK_Wrk_Man M on A.wo_code = M.wo_code
			//left join TStep S on A.step_code = S.stepcode
			//left join TPrteEqp_Stad T on A.materiel_code = T.materiel_code and M.route_code = T.route_code and A.step_code = T.step_code and A.wx_code = T.eqp_code
			//left join TMateriel_Info P on A.materiel_code = P.partcode
			//left join TOrganization G on M.wkshp_code = G.org_code
			//left join TCustomer E on A.wx_code = E.code and E.btype = 'WX'
			//left join TFlw_Rout R on M.route_code = R.code
			//left join TFlw_Rtdt D on M.route_code = D.rout_code and A.step_code = D.step_code
			//left join TUser U on B.in_person = U.usercode
			//where A.style = 'S' and B.style = 'S' and M.status<>'CLOSED'
			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;
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion