ZQHApi.git - Gitblit

			@@ -27,7 +27,7 @@
			try
			{
			//获取设备类型数据
			sql = @"select code,name from T_Dict where dict_type='CHLX' ";
			sql = @"select code,name,iparent_id from T_Dict where dict_type='CHLX' ";
			var data = DapperHelper.selecttable(sql);
			mes.code = "200";
			mes.Message = "查询成功!";
			@@ -424,6 +424,7 @@
			public static ToMessage InventoryFileSelect(string stu_torgcode,string stu_torgtypecode,string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
			{
			var dynamicParams = new DynamicParameters();
			string search0 = "";
			string search = "";
			string search1 = "";
			try
			@@ -460,7 +461,8 @@
			}
			if (stocktypecode != "" && stocktypecode != null)
			{
			search += "and D.code=@stocktypecode ";
			search0+= " where code = @stocktypecode";
			search += " and CTE.code <>@stocktypecode and CTE.iparent_id<>@stocktypecode and CTE.dict_type='CHLX' ";
			dynamicParams.Add("@stocktypecode", stocktypecode);
			}
			if (materialtypecode != "" && materialtypecode != null)
			@@ -479,25 +481,80 @@
			}
			// --------------查询指定数据--------------
			var total = 0; //总条数
			var sql = @"select A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
			C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
			//var sql = @"select A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
			// C.code as materialtypecode,C.name as materialtypename,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 TMateriel_Info A
			// left join TUom B on A.uom_code=B.code
			// left join TMateriel_Type C on A.materieltype_code=C.code
			// left join T_Dict D on A.stocktype_code=D.code and D.dict_type='CHLX'
			// 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;
			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 TMateriel_Info A
			left join TUom B on A.uom_code=B.code
			left join TMateriel_Type C on A.materieltype_code=C.code
			left join T_Dict D on A.stocktype_code=D.code and D.dict_type='CHLX'
			left join T_Sec_Stck T on A.stck_code=T.code
			left join TUser U on A.lm_user=U.usercode
			left join (
			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;
			var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
			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 WHERE RowNum >= "+ startNum + " AND RowNum <="+ endNum + " ORDER BY "+ prop + " "+order+"";
			var data = DapperHelper.selectdata(sql, dynamicParams);
			mes.code = "200";
			mes.Message = "查询成功!";
			mes.count = total;
			mes.data = data.ToList();
			mes.data = data;
			}
			catch (Exception e)
			{