public static object getOrderList(string lsTime, string dgStart, string dgEnd, string brandList, string operaList, string mechineList, string companyID, string productName)
{
try
{
if (string.IsNullOrEmpty(operaList))
{
return(new { code = 500, msg = "请选择操作员" });
}
if (string.IsNullOrEmpty(mechineList))
{
return(new { code = 500, msg = "请选择机器" });
}
if (string.IsNullOrEmpty(lsTime))
{
return(new { code = 500, msg = "请选择零售时间点" });
}
if (string.IsNullOrEmpty(dgStart))
{
return(new { code = 500, msg = "请选择订购开始时间" });
}
if (string.IsNullOrEmpty(dgEnd))
{
return(new { code = 500, msg = "请选择订购结束时间" });
}
if (string.IsNullOrEmpty(brandList))
{
return(new { code = 500, msg = "请选择品牌" });
}
string brandSql = "SELECT brandID ,value = ( STUFF(( SELECT ',' + convert(varchar,productID) FROM asm_product"
+ " WHERE brandID = Test.brandID FOR XML PATH('') ), 1, 1, '') )FROM asm_product AS Test where companyID = " + companyID + " and brandID in(" + brandList + ") and is_del=0 GROUP BY brandID; ";
string productID = "";
DataTable brandDt = DbHelperSQL.Query(brandSql).Tables[0];
if (brandDt.Rows.Count > 0)
{
for (int i = 0; i < brandDt.Rows.Count; i++)
{
productID += brandDt.Rows[i]["value"].ToString() + ",";
}
productID = productID.Substring(0, productID.Length - 1);
//此处目的是为了去掉历史记录里之前有的产品而现在把该产品给下架的产品ID
string sqlLd = "SELECT STUFF((SELECT ','+productID FROM asm_ldinfo where mechineID in (" + mechineList + ")"
+ " and productID in(" + productID + ") for xml path('')),1,1,'') productID";
DataTable dd = DbHelperSQL.Query(sqlLd).Tables[0];
if (dd.Rows.Count > 0)
{
productID = dd.Rows[0]["productID"].ToString();
}
if (!string.IsNullOrEmpty(productName))
{
string sqlp = "select * from asm_product where proname like '%" + productName + "%' or bh like '%" + productName + "%' or shortName like '%" + productName + "%'";
DataTable dp = DbHelperSQL.Query(sqlp).Tables[0];
if (dp.Rows.Count > 0)
{
for (int k = 0; k < dp.Rows.Count; k++)
{
productID += dd.Rows[0]["productID"].ToString() + ",";
}
productID = productID.Substring(0, productID.Length - 1);
}
}
}
productID = String.Join(",", productID.Split(',').Distinct <string>());
if (string.IsNullOrEmpty(productID))
{
return(new { code = 500, msg = "无需要补货的商品" });
}
DataTable dt = OperUtil.getCGD(mechineList, productID, dgStart, dgEnd, lsTime);
if (dt.Rows.Count > 0)
{
return(new { code = 200, db = OperUtil.DataTableToJsonWithJsonNet(dt) });
}
return(new { code = 500, msg = "暂无记录" });
}
catch
{
return(new { code = 500, msg = "系统异常" });
}
}