ZQHApi.git - Gitblit

			@@ -1337,7 +1337,12 @@
			}
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select A.id,T.org_code,T.org_name,A.code,A.name,A.description,A.enable,U.username as lm_user,A.lm_date from TFlw_Rout A
			var sql = @"select A.id,T.org_code,T.org_name,A.code,A.name,A.description,A.enable,U.username as lm_user,A.lm_date,
			ISNULL(STUFF((SELECT ',' + R.materiel_code
			FROM TMateriel_Route R
			WHERE CHARINDEX(',' + R.route_code + ',', ',' + A.code + ',') > 0
			FOR XML PATH('')), 1, 1, ''),'') AS isrolepart
			from TFlw_Rout A
			left join TUser U on A.lm_user=U.usercode
			left join TOrganization T on A.torg_code=T.org_code
			left join TOrganization as L on T.parent_id=L.id
			@@ -1564,6 +1569,197 @@
			}
			#endregion

			#region[工艺路线关联产品查询]
			public static ToMessage RputeInventorySearch(string stu_torgcode, string stu_torgtypecode, string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode)
			{
			var dynamicParams = new DynamicParameters();
			string search0 = "";
			string search = "";
			string search1 = "";
			try
			{
			switch (stu_torgtypecode)
			{
			case "":
			break;
			case "D":
			search1 += "and L.org_code=@stu_torgcode ";
			dynamicParams.Add("@stu_torgcode", stu_torgcode);
			break;
			case "W":
			search1 += "and F.org_code=@stu_torgcode ";
			dynamicParams.Add("@stu_torgcode", stu_torgcode);
			break;
			default:
			break;
			}
			if (partcode != "" && partcode != null)
			{
			search += "and A.partcode like '%'+@partcode+'%' ";
			dynamicParams.Add("@partcode", partcode);
			}
			if (partname != "" && partname != null)
			{
			search += "and A.partname like '%'+@partname+'%' ";
			dynamicParams.Add("@partname", partname);
			}
			if (partspec != "" && partspec != null)
			{
			search += "and A.partspec like '%'+@partspec+'%' ";
			dynamicParams.Add("@partspec", partspec);
			}
			if (stocktypecode != "" && stocktypecode != null)
			{
			search0 += " where code = @stocktypecode";
			search += " and CTE.iparent_id<>'' and CTE.dict_type='CHLX'";
			dynamicParams.Add("@stocktypecode", stocktypecode);
			}
			if (materialtypecode != "" && materialtypecode != null)
			{
			search += "and C.code=@materialtypecode ";
			dynamicParams.Add("@materialtypecode", materialtypecode);
			}
			if (storehousecode != "" && storehousecode != null)
			{
			search += "and A.stck_code=@storehousecode ";
			dynamicParams.Add("@storehousecode", storehousecode);
			}
			if (search == "")
			{
			search = "and 1=1 ";
			}
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql0 = @"WITH CTE AS (
			SELECT code,name,iparent_id,dict_type
			FROM T_Dict " + search0 + " ";
			sql0 += @"UNION ALL
			SELECT t.code,t.name,t.iparent_id,t.dict_type
			FROM T_Dict t
			INNER JOIN CTE c ON c.code = t.iparent_id)
			SELECT COUNT(1) as total FROM (
			SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
			A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
			A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
			FROM CTE
			right join TMateriel_Info A on CTE.code=A.stocktype_code
			left join TUom B on A.uom_code=B.code
			left join T_Dict D on A.stocktype_code=D.code
			left join T_Sec_Stck T on A.stck_code=T.code
			left join TUser U on A.lm_user=U.usercode
			left join (
			select distinct materiel_code from TMateriel_Route M
			left join TOrganization F on M.torg_code=F.org_code
			left join TOrganization as L on F.parent_id=L.id
			where 1=1 " + search1 + ") M on A.partcode=M.materiel_code where A.is_delete<>'1' " + search + ") AS Result";
			var data0 = DapperHelper.selectdata(sql0, dynamicParams);
			if (data0.Rows.Count > 0)
			{
			total = Convert.ToInt32(data0.Rows[0]["total"].ToString());
			}
			else
			{
			total = 0;
			}

			var sql = @"WITH CTE AS (
			SELECT code,name,iparent_id,dict_type
			FROM T_Dict " + search0 + " ";
			sql += @"UNION ALL
			SELECT t.code,t.name,t.iparent_id,t.dict_type
			FROM T_Dict t
			INNER JOIN CTE c ON c.code = t.iparent_id)
			SELECT * FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY lm_date desc) AS RowNum
			FROM(
			SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
			A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
			A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
			FROM CTE
			right join TMateriel_Info A on CTE.code=A.stocktype_code
			left join TUom B on A.uom_code=B.code
			left join T_Dict D on A.stocktype_code=D.code
			left join T_Sec_Stck T on A.stck_code=T.code
			left join TUser U on A.lm_user=U.usercode
			left join (
			select distinct materiel_code from TMateriel_Route M
			left join TOrganization F on M.torg_code=F.org_code
			left join TOrganization as L on F.parent_id=L.id
			where 1=1 " + search1 + ") M on A.partcode=M.materiel_code where A.is_delete<>'1' " + search + " ) AS Temp) AS Result";
			var data = DapperHelper.selectdata(sql, dynamicParams);
			mes.code = "200";
			mes.Message = "查询成功!";
			mes.count = total;
			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 RouteBatchPartSave(string stu_torgcode, string stu_torgtypecode, string username, string routecode, string[] rolebatchpartcode)
			{
			var sql = "";
			var dynamicParams = new DynamicParameters();
			List<object> list = new List<object>();
			try
			{
			list.Clear();
			//清除工艺路线关联的物料数据
			sql = @"delete TMateriel_Route where route_code=@route_code";
			list.Add(new { str = sql, parm = new { route_code = routecode } });
			//循环写入工艺路线物料关联表
			for (int i = 0; i < rolebatchpartcode.Length; i++)
			{
			sql = @"insert into TMateriel_Route(materiel_code,route_code,lm_user,lm_date,torg_code)
			values(@materiel_code,@route_code,@lm_user,@lm_date,@torg_code)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			materiel_code = rolebatchpartcode[i].ToString(),
			route_code = routecode,
			lm_user = username,
			lm_date = DateTime.Now.ToString(),
			torg_code = stu_torgcode
			}
			});
			}

			bool aa = DapperHelper.DoTransaction(list);
			if (aa)
			{
			mes.code = "200";
			mes.count = 0;
			mes.Message = "操作成功!";
			mes.data = null;
			}
			else
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "操作失败!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion