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 });
        }
Пример #2
0
        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);
        }