public JsonResult GetNotQueList()
{
IList<object[]> objs = NSession.CreateSQLQuery(string.Format(@"select * from (
select SKU,SUM(Qty) as Qty,(select isnull(SUM(Qty),0) from WarehouseStock where SKU=OP.SKU ) as NowQty,(select count(Id) from SKUCode where SKU=OP.SKU and IsOut=0) as unPeiQty,COUNT(O.Id) as'OrderQty' from Orders O left join OrderProducts OP On O.Id=OP.OId where O.IsOutOfStock=1 and OP.IsQue=1 and O.Status<>'作废订单' group by SKU
) as tbl where NowQty>0")).List<object[]>();
var list = new List<QueCount>();
foreach (var objectse in objs)
{
var oc = new QueCount
{
SKU = objectse[0].ToString(),
Qty = Utilities.ToInt(objectse[1]),
NowQty = Utilities.ToInt(objectse[2]),
UnPeiQty = Utilities.ToInt(objectse[3]),
OrderQty = Utilities.ToInt(objectse[4])
};
list.Add(oc);
}
return Json(new { total = list.Count, rows = list });
}
public ActionResult QueData(string order, string sort, string s, string p, string a, string c)
{
string orderby = "";
if (!string.IsNullOrEmpty(order) && !string.IsNullOrEmpty(sort))
{
orderby = " order by " + sort + " " + order;
}
s = SqlWhere(a, p, s, "");
s = s.Replace("SKU", "OP.SKU");
if (s.Length > 0)
{
s = " and " + s;
}
string sqlss = "";
if (!string.IsNullOrEmpty(c))
{
string cs = "";
IList <object> objectes = NSession.CreateSQLQuery(@"with a as(
select * from ProductCategory where ID=" + c + @"
union all
select x.* from ProductCategory x,a
where x.ParentId=a.Id)
select Name from a").List <object>();
foreach (object item in objectes)
{
cs += "'" + item + "',";
}
if (cs.Length > 0)
{
cs = cs.Trim(',');
}
sqlss = " where OP.SKU in (select SKU from Products where Category in (" + cs + "))";
}
IList <object[]> objs = NSession.CreateSQLQuery(string.Format(@"select * from (
select *,(Qty-BuyQty-UnPeiQty) as NeedQty,(SQty-BuyQty-UnPeiQty) as SNeedQty from( select * ,(select COUNT(Id) from SKUCode where IsOut=0 and SKUCode.SKU=tbl1.SKU) as UnPeiQty,
isnull((select SUM(Qty) from Orders O left join OrderProducts OP On O.Id=OP.OId where O.IsOutOfStock=1 and O.Enabled=1 and O.Status in ('已处理','待拣货') and OP.IsQue=1 and OP.SKU=tbl1.SKU),0) as 'SQty'
from (
select OP.SKU,SUM(OP.Qty) as Qty,MIN(O.CreateOn) as MinDate,P.Standard,
(select isnull(SUM(Qty-DaoQty),0) from PurchasePlan
where Status<>'异常' and Status<>'已收到' and SKU=OP.SKU ) as BuyQty
from Orders O left join OrderProducts OP On O.Id=OP.OId
left join Products P on OP.SKU=P.SKU
where O.Enabled=1 and O.IsStop=0 and O.Status in ('已处理','待拣货') and OP.SKU is not null {0} group by OP.SKU,P.Standard)
as tbl1 ) as tbl2 ) as tbl3 where SQty>0 or (Qty-UnPeiQty)>0 {1} {2}", s, sqlss, orderby)).List <object[]>();
List <QueCount> list = new List <QueCount>();
foreach (object[] objectse in objs)
{
QueCount oc = new QueCount {
SKU = objectse[0].ToString(), Qty = Utilities.ToInt(objectse[1]), MinDate = Convert.ToDateTime(objectse[2]), BuyQty = Utilities.ToInt(objectse[4]), NeedQty = Utilities.ToInt(objectse[7]), SNeedQty = Utilities.ToInt(objectse[8]), UnPeiQty = Utilities.ToInt(objectse[5]), SQty = Utilities.ToInt(objectse[6])
};
if (objectse[3] is DBNull || objectse[3] == null)
{
}
else
{
oc.Standard = objectse[3].ToString();
}
if (!(objectse[2] is DBNull) && !(objectse[2] == null))
{
oc.Field1 = objectse[2].ToString();
}
//oc.NeedQty -= oc.UnPeiQty;
if (oc.NeedQty <= 0)
{
oc.NeedQty = 0;
}
if (oc.SNeedQty <= 0)
{
oc.SNeedQty = 0;
}
list.Add(oc);
}
return(Json(list));
}
public ActionResult QueData(string order, string sort, string s, string p, string a, string c)
{
string orderby = "";
if (!string.IsNullOrEmpty(order) && !string.IsNullOrEmpty(sort))
{
orderby = " order by " + sort + " " + order;
}
s = SqlWhere(a, p, s, "");
s = s.Replace("SKU", "OP.SKU");
if (s.Length > 0)
{
s = " and " + s;
}
string sqlss = "";
if (!string.IsNullOrEmpty(c))
{
string cs = "";
IList<object> objectes = NSession.CreateSQLQuery(@"with a as(
select * from ProductCategory where ID=" + c + @"
union all
select x.* from ProductCategory x,a
where x.ParentId=a.Id)
select Name from a").List<object>();
foreach (object item in objectes)
{
cs += "'" + item + "',";
}
if (cs.Length > 0)
{
cs = cs.Trim(',');
}
sqlss = " where OP.SKU in (select SKU from Products where Category in (" + cs + "))";
}
IList<object[]> objs = NSession.CreateSQLQuery(string.Format(@"select * from (
select *,(Qty-BuyQty-UnPeiQty) as NeedQty,(SQty-BuyQty-UnPeiQty) as SNeedQty from( select * ,(select COUNT(Id) from SKUCode where IsOut=0 and SKUCode.SKU=tbl1.SKU) as UnPeiQty,
isnull((select SUM(Qty) from Orders O left join OrderProducts OP On O.Id=OP.OId where O.IsOutOfStock=1 and O.Enabled=1 and O.Status in ('已处理','待拣货') and OP.IsQue=1 and OP.SKU=tbl1.SKU),0) as 'SQty'
from (
select OP.SKU,SUM(OP.Qty) as Qty,MIN(O.CreateOn) as MinDate,P.Standard,
(select isnull(SUM(Qty-DaoQty),0) from PurchasePlan
where Status<>'异常' and Status<>'已收到' and SKU=OP.SKU ) as BuyQty
from Orders O left join OrderProducts OP On O.Id=OP.OId
left join Products P on OP.SKU=P.SKU
where O.Enabled=1 and O.IsStop=0 and O.Status in ('已处理','待拣货') and OP.SKU is not null {0} group by OP.SKU,P.Standard)
as tbl1 ) as tbl2 ) as tbl3 where SQty>0 or (Qty-UnPeiQty)>0 {1} {2}", s, sqlss, orderby)).List<object[]>();
List<QueCount> list = new List<QueCount>();
foreach (object[] objectse in objs)
{
QueCount oc = new QueCount { SKU = objectse[0].ToString(), Qty = Utilities.ToInt(objectse[1]), MinDate = Convert.ToDateTime(objectse[2]), BuyQty = Utilities.ToInt(objectse[4]), NeedQty = Utilities.ToInt(objectse[7]), SNeedQty = Utilities.ToInt(objectse[8]), UnPeiQty = Utilities.ToInt(objectse[5]), SQty = Utilities.ToInt(objectse[6]) };
if (objectse[3] is DBNull || objectse[3] == null)
{
}
else
{
oc.Standard = objectse[3].ToString();
}
if (!(objectse[2] is DBNull) && !(objectse[2] == null))
{
oc.Field1 = objectse[2].ToString();
}
//oc.NeedQty -= oc.UnPeiQty;
if (oc.NeedQty <= 0)
{
oc.NeedQty = 0;
}
if (oc.SNeedQty <= 0)
{
oc.SNeedQty = 0;
}
list.Add(oc);
}
return Json(list);
}