public ActionResult GetFreightSumList(string search, int isa, int isl)
        {
            string orderby;

            var where = GetWhere("", "", search, out @orderby);
            string[] strs = new string[] { "", "", "", "" };
            string   sql  =
                "select COUNT(1) as count,SUM(Freight) as total {0} {1} from Orders where CreateOn between '2013-03-01' and '2013-04-01' and Status='已发货' group by {2} {3}";

            if (isa == 1)
            {
                strs[0] = ",platform,account";
                strs[2] = "platform,account";
            }
            if (isl == 1)
            {
                strs[1] = ",LogisticMode ";
                if (isa == 1)
                {
                    strs[3] = ",LogisticMode";
                }
                else
                {
                    strs[3] = " LogisticMode";
                }
            }
            sql = string.Format(sql, strs[0], strs[1], strs[2], strs[3]);
            IList <object[]> objList = NSession.CreateSQLQuery(sql)
                                       .List <object[]>();
            List <OrderData> os = new List <OrderData>();

            object count = NSession.CreateQuery("select count(Id) from OrderType " + where).UniqueResult();

            return(Json(new { total = count, rows = os }));
        }
        public JsonResult GetUnCountryByAreaCode(string sort, string order, int id, string s)
        {
            string orderby = " order by Id desc ";

            if (!string.IsNullOrEmpty(sort) && !string.IsNullOrEmpty(order))
            {
                orderby = "order by " + sort + " " + order;
            }
            IList <object[]> list = NSession.CreateSQLQuery("select Id,CCountry,ECountry,CountryCode,(select AreaName from LogisticsArea where ID=(select top 1 AreaCode from LogisticsAreaCountry la where c.Id= la.CountryCode and la.AreaCode in (select ID from LogisticsArea where LId =(select LId from LogisticsArea where LogisticsArea.Id=:cid)))) as AreaNane from Country c where c.Id not in (select CountryCode from LogisticsAreaCountry where AreaCode=:cid ) and (CCountry like'%" + s + "%' or ECountry like '%" + s + "%')" + orderby)
                                    .SetInt32("cid", id)
                                    .List <object[]>();
            List <CountryType> l = new List <CountryType>();

            foreach (object[] foo in list)
            {
                CountryType c = new CountryType();
                c.Id          = Utilities.ToInt(foo[0]);
                c.CCountry    = foo[1].ToStr();
                c.ECountry    = foo[2].ToStr();
                c.CountryCode = foo[3].ToStr();
                c.AreaName    = foo[4].ToStr();
                l.Add(c);
            }

            return(Json(new { total = l.Count, rows = l }));
        }
示例#3
0
 public JsonResult EditReset(string o)
 {
     try
     {
         o = o.Replace("\r", "").Replace("\t", " ").Replace("  ", " ");
         string[] rows     = o.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
         string   updateOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
         foreach (string row in rows)
         {
             string[] cels = row.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
             if (cels.Length == 2)
             {
                 try
                 {
                     NSession.CreateSQLQuery(" update WarehouseStock set Qty=" + cels[1] + " , UpdateOn='" + updateOn + "'  where SKU='" + cels[0] +
                                             "'").UniqueResult();
                     NSession.Flush();
                 }
                 catch (Exception)
                 {
                     continue;
                 }
             }
         }
     }
     catch (Exception ee)
     {
         return(Json(new { IsSuccess = false, Message = "出错了" }));
     }
     return(Json(new { IsSuccess = true, Message = "成功!" }));
 }
示例#4
0
        public JsonResult ExportQue(string s, string p, string a, string c)
        {
            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 + "))";
            }
            string sql = 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} ", s, sqlss);

            DataSet    ds      = new DataSet();
            IDbCommand command = NSession.Connection.CreateCommand();

            command.CommandText = sql;
            SqlDataAdapter da = new SqlDataAdapter(command as SqlCommand);

            da.Fill(ds);
            // 设置编码和附件格式
            Session["ExportDown"] = ExcelHelper.GetExcelXml(ds);
            return(Json(new { IsSuccess = true }));
        }
示例#5
0
 public ActionResult IsEnabled(string ids, int i)
 {
     try
     {
         NSession.CreateSQLQuery("update PrintTemplate set Enable=" + i + " where Id in(" + ids + ")");
         NSession.Flush();
     }
     catch (Exception ee)
     {
         return(Json(new { IsSuccess = false, ErrorMsg = "出错了" }));
     }
     return(Json(new { IsSuccess = true }));
 }
示例#6
0
        public JsonResult List(int page, int rows, string sort, string order, string search, string infraction = "", string c = "")
        {
            string orderby = Utilities.OrdeerBy(sort, order);

            string where = Utilities.SqlWhere(search);
            if (where != "")
            {
                where += " and  Enabled <>0";
            }
            else
            {
                where = "where Enabled <>0";
            }
            if (infraction != "")
            {
                where += InfractionWhere(infraction);
            }
            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(',');
                }
                where += " and Category in (" + cs + ")";
            }
            IList <ProductType> objList = NSession.CreateQuery("from ProductType " + where + orderby)
                                          .SetFirstResult(rows * (page - 1))
                                          .SetMaxResults(rows)
                                          .List <ProductType>();

            foreach (ProductType item in objList)
            {
                item.Infraction = Infraction(item.SKU);
            }
            object count = NSession.CreateQuery("select count(Id) from ProductType " + where).UniqueResult();

            return(Json(new { total = count, rows = objList }));
        }
        public JsonResult List(int page, int rows, string sort, string order, string search)
        {
            string orderby = Utilities.OrdeerBy(sort, order);

            string where = Utilities.SqlWhere(search);
            List <EbayType> objList =
                NSession.CreateSQLQuery(
                    "select [Id],[ItemId],[ItemTitle],[Currency],[Price],[PicUrl],[StartNum],[NowNum],[ProductUrl],[StartTime],[CreateOn],[Account],[SKU],[Status],isnull((select top 1 COUNT(Id) from SKUCode where SKUCode.SKU = Ebay.SKU and IsOut=0 group by SKU),0) as UnPeiQty from Ebay " +
                    where + orderby).AddEntity(typeof(EbayType))
                .SetFirstResult(rows * (page - 1))
                .SetMaxResults(rows)
                .List <EbayType>().ToList();
            object count = NSession.CreateQuery("select count(Id) from EbayType " + where).UniqueResult();

            return(Json(new { total = count, rows = objList }));
        }
示例#8
0
 public ActionResult Edit(DictionaryClassType obj)
 {
     try
     {
         DictionaryClassType c = GetById(obj.Id);
         NSession.CreateSQLQuery("update Dictionarys set DicCode='" + obj.Code + "' where DicCode='" + c.Code + "' ")
         .UniqueResult();
         NSession.Clear();
         NSession.Update(obj);
         NSession.Flush();
     }
     catch (Exception ee)
     {
         return(Json(new { IsSuccess = false, ErrorMsg = "出错了" }));
     }
     return(Json(new { IsSuccess = true }));
 }
示例#9
0
        public JsonResult ReP(string p)
        {
            string username = GetCurrentAccount().Username;
            string str      = "kelvin,csa,jb";

            if (str.IndexOf(username) != -1)
            {
                string ps = p.Replace("\r", "").Trim('\n').Replace("\n", "','");
                NSession.CreateQuery("update  SKUCodeType set IsOut=1,IsSend=1 where SKU in('" + ps + "') ").ExecuteUpdate();
                NSession.CreateSQLQuery("update WarehouseStock set Qty=(select COUNT(1) from skucode where SKU=WarehouseStock.SKU and IsSend=0) where SKU  in('" + ps + "')").ExecuteUpdate();
                return(Json(new { IsSuccess = true }));
            }
            else
            {
                return(Json(new { IsSuccess = false }));
            }
        }
示例#10
0
        public IList <Sores> SoreList(DateTime st, DateTime et)
        {
            IList <Sores>    sores     = new List <Sores>();
            IList <object[]> objectses = NSession.CreateSQLQuery("select COUNT(Id) as Qcount, PackBy as PackBy,SUM(PackCoefficient) as PackCoefficient from OrderPackRecord where [PackOn] between '" + st.ToString("yyyy-MM-dd") + "' and '" + et.ToString("yyyy-MM-dd") + " 23:59:59' group by [PackBy]").List <object[]>();

            foreach (var item in objectses)
            {
                object  soreadd   = NSession.CreateQuery("select SUM(Sore) from SoresAddType where Worker='" + item[1].ToString() + "' and WorkDate between '" + st.ToString("yyyy-MM-dd") + "' and '" + et.ToString("yyyy-MM-dd") + " 23:59:59'").UniqueResult();
                decimal avg       = Convert.ToDecimal((Convert.ToDouble(item[2]).ToString("f1"))) / Convert.ToDecimal(item[0]);
                decimal packsores = Convert.ToDecimal((Convert.ToDouble(item[2]).ToString("f1")));
                decimal sore      = Convert.ToDecimal(soreadd);
                decimal totalsore = sore + packsores;
                sores.Add(new Sores {
                    PackBy = item[1].ToString(), PackSores = packsores, Qcount = Convert.ToDecimal(item[0]), Avg = Convert.ToDecimal(avg.ToString("f1")), Sore = sore, TotalSores = totalsore
                });
            }
            return(sores);
        }
示例#11
0
        public ActionResult Export(string search, string c)
        {
            try
            {
                string where = Utilities.SqlWhere(search);

                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(',');
                    }
                    if (!string.IsNullOrEmpty(where))
                    {
                        where += " and Category in (" + cs + ")";
                    }
                    else
                    {
                        where = " where Category in (" + cs + ")";
                    }
                }
                List <ProductType> objList = NSession.CreateQuery("from ProductType " + where)
                                             .List <ProductType>().ToList();
                Session["ExportDown"] = ExcelHelper.GetExcelXml(Utilities.FillDataTable((objList)));
            }
            catch (Exception ee)
            {
                return(Json(new { IsSuccess = false, ErrorMsg = "出错了" }));
            }
            return(Json(new { IsSuccess = true, ErrorMsg = "导出成功" }));
        }
示例#12
0
        public ActionResult SellCount(DateTime st, DateTime et, string a, string p, string s, int page, int rows)
        {
            var           list     = GetSellCount(st, et, a, p, s, page, rows);
            List <object> footers  = new List <object>();
            var           sqlWhere = SqlWhere(st, et, a, p, s);

            if (sqlWhere.Length > 3)
            {
                sqlWhere += " and SKU is not null ";
            }
            else
            {
                sqlWhere = " where SKU is not null ";
            }
            object obj = NSession.CreateSQLQuery(string.Format(
                                                     "select COUNT(1) from ( select SKU from OrderProducts right join Orders on OId=Orders.Id   {0} group by SKU ) as tbl",
                                                     sqlWhere)).UniqueResult();

            return(Json(new { rows = list.OrderByDescending(f => f.Qty), total = obj }));
        }
 public JsonResult Forward(string ids, string t, string m)
 {
     try
     {
         //int mid = int.Parse(id.Substring(0, id.IndexOf("$")).ToString());
         //string name = id.Substring(id.IndexOf("$") + 1, id.IndexOf("~") - id.IndexOf("$") - 1);
         //string remark = id.Substring(id.IndexOf("~") + 1);
         //EbayMessageType obj = GetById(mid);
         //obj.ReplayOnlyBy = name;
         //obj.ForwardWhy = remark;
         NSession.CreateSQLQuery("Update EbayMessage set ReplayOnlyBy='" + t + "',ForwardWhy='" + m + "' where Id in(" + ids + ")").UniqueResult();
         // NSession.Update(obj);
         NSession.Flush();
         return(Json(new { Msg = 0 }));;
     }
     catch (Exception e)
     {
     }
     return(Json(new { Msg = 1 }));
 }
示例#14
0
        public JsonResult List(int page, int rows, string sort, string order, string search)
        {
            string orderby = Utilities.OrdeerBy(sort, order);

            string where = Utilities.SqlWhere(search);


            List <WarehouseStockType> objList = NSession.CreateSQLQuery("select *,(select COUNT(Id) from SKUCode S where S.SKU = WS.sku and IsOut=0 group by SKU) as UnPeiQty from WarehouseStock WS" + where + orderby).AddEntity(typeof(WarehouseStockType))
                                                .SetFirstResult(rows * (page - 1))
                                                .SetMaxResults(rows)
                                                .List <WarehouseStockType>().ToList();
            string ids = "";

            foreach (var warehouseStockType in objList)
            {
                ids += warehouseStockType.SKU + ",";
            }
            if (ids.Length > 0)
            {
                ids = ids.Trim(',');
            }
            IList <object[]> objs =
                NSession.CreateQuery("select SKU,COUNT(Id) from SKUCodeType where SKU in('" + ids.Replace(",", "','") + "') and IsOut=0 group by SKU ").List <object[]>();

            foreach (var objectse in objs)
            {
                WarehouseStockType warehouse =
                    objList.Find(x => x.SKU.Trim().ToUpper() == objectse[0].ToString().Trim().ToUpper());
                if (warehouse != null)
                {
                    warehouse.UnPeiQty = Convert.ToInt32(objectse[1]);
                    if (warehouse.UnPeiQty == 0)
                    {
                        warehouse.UnPeiQty = warehouse.Qty;
                    }
                }
            }
            object count = NSession.CreateQuery("select count(Id) from WarehouseStockType " + where).UniqueResult();

            return(Json(new { total = count, rows = objList }));
        }
        public ActionResult StockList(int page, int rows, string sort, string order, string search)
        {
            string where = "";
            string orderby = Utilities.OrdeerBy(sort, order);

            if (!string.IsNullOrEmpty(search))
            {
                where = Utilities.Resolve(search);
                if (where.Length > 0)
                {
                    where = " where " + where;
                }
            }
            IList <object[]> objList = NSession.CreateSQLQuery("select SKU,OldSKU,ProductName as Title,Price,(select COUNT(1) from SKUCode where IsOut=0 and SKU=P.SKU ) as Qty,(Price*(select COUNT(1) from SKUCode where IsOut=0 and SKU=P.SKU )) as TotalPrice from Products P " + where + " " + orderby)
                                       .SetFirstResult(rows * (page - 1))
                                       .SetMaxResults(rows)
                                       .List <object[]>();
            List <ProductData> list = new List <ProductData>();

            foreach (object[] objectse in objList)
            {
                ProductData pd = new ProductData();
                pd.SKU        = objectse[0].ToString();
                pd.Title      = objectse[2].ToString();
                pd.Price      = Convert.ToDouble(objectse[3]);
                pd.Qty        = Convert.ToInt32(objectse[4]);
                pd.TotalPrice = Math.Round(Convert.ToDouble(objectse[5]), 2);

                list.Add(pd);
            }
            object        count   = NSession.CreateQuery("select count(Id) from ProductType " + where).UniqueResult();
            object        total   = NSession.CreateSQLQuery("select SUM(qty) from (select (Price*(select COUNT(1) from SKUCode where IsOut=0 and SKU=P.SKU)) as Qty from Products P " + where + ") as t").UniqueResult();
            List <object> footers = new List <object>();

            footers.Add(new { TotalPrice = Math.Round(Convert.ToDouble(total), 2), SKU = "合计:" });
            return(Json(new { total = count, rows = list, footer = footers }));
        }
示例#16
0
        public String JiCount(DateTime st, DateTime et)
        {
            List <string> strData = new List <string>();
            StringBuilder sb      = new StringBuilder();

            strData.Add("人员");
            sb.Append("select [PackBy] as '扫描人',");
            DateTime date = st;

            while (date <= et)
            {
                string week = GetWeek("zh", date);
                strData.Add(date.ToString("MMdd"));

                sb.Append("SUM(case  when convert(varchar(10),[PackOn],120)='" + date.ToString("yyyy-MM-dd") + "' then  rcount else 0 end  ) as '" + date.ToString("MM.dd") + "(" + week + ")' ,");
                date = date.AddDays(1);
            }
            sb = sb.Remove(sb.Length - 1, 1);
            sb.Append("from  (select [PackBy] ,convert(varchar(10),[PackOn],120) [PackOn] ,COUNT(1) as 'rcount'  from OrderPackRecord where [PackOn] between '" + st.ToString("yyyy-MM-dd") + "' and '" + et.ToString("yyyy-MM-dd") + " 23:59:59' group by [PackBy] ,convert(varchar(10),[PackOn],120)) as tbl1  group by [PackBy]");
            IList <object[]> objectses   = NSession.CreateSQLQuery(sb.ToString()).List <object[]>();
            StringBuilder    jsonBuilder = new StringBuilder();

            jsonBuilder.Append("[");//转换成多个model的形式
            for (int i = 0; i < objectses.Count; i++)
            {
                jsonBuilder.Append("{");
                for (int j = 0; j < strData.Count; j++)
                {
                    jsonBuilder.Append("\"");
                    jsonBuilder.Append(strData[j]);
                    jsonBuilder.Append("\":\"");
                    jsonBuilder.Append(objectses[i][j]);
                    jsonBuilder.Append("\",");
                }
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
                jsonBuilder.Append("},");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]");
            StringBuilder jsonBuilder2 = new StringBuilder();

            jsonBuilder2.Append("[{");
            for (int j = 1; j < strData.Count; j++)
            {
                int sum = 0;

                jsonBuilder2.Append("\"");
                jsonBuilder2.Append(strData[j]);
                jsonBuilder2.Append("\":\"");
                for (int i = 0; i < objectses.Count; i++)
                {
                    sum += Convert.ToInt32(objectses[i][j]);
                }
                jsonBuilder2.Append(sum.ToString());
                jsonBuilder2.Append("\",");
            }
            jsonBuilder2.Remove(jsonBuilder2.Length - 1, 1);
            jsonBuilder2.Append("}]");
            string json = "{\"total\":" + objectses.Count + ",\"rows\":" + jsonBuilder.ToString() + ",\"footer\":" + jsonBuilder2.ToString() + "}";

            return(json);
        }
示例#17
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));
        }
示例#18
0
        public ContentResult GetSaleChart(string s, DateTime st, string p)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine("<chart caption='{0} 的销售记录({1}-{2})' subCaption='销量'  showLabels='1' showColumnShadow='1' animation='1' showAlternateHGridColor='1' AlternateHGridColor='ff5904' divLineColor='ff5904' divLineAlpha='20' alternateHGridAlpha='5' canvasBorderColor='666666' baseFontColor='666666'  lineAlpha='85' showValues='1' rotateValues='0' valuePosition='auto' canvaspadding='8' lineThickness='3'>");
            //sb.AppendLine("<chart palette='2'  caption='{0} 的销售记录({1}-{2})' subCaption='销量' caption='Sales Comparison' showValues='0' numVDivLines='10' drawAnchors='0' numberPrefix='$' divLineAlpha='30' alternateHGridAlpha='20'  setAdaptiveYMin='1'  canvaspadding='10' labelDisplay='ROTATE'>");

            DateTime      et      = st.AddDays(15);
            List <string> strData = new List <string>();
            StringBuilder sb2     = new StringBuilder();
            DateTime      date    = st;

            sb2.Append("select {0} as 'Y',");
            if (et > DateTime.Now)
            {
                et = DateTime.Now;
            }
            while (date <= et)
            {
                string week = GetWeek("zh", date);
                strData.Add(date.ToString("MM.dd") + "(" + week + ")");

                sb2.Append(" SUM(case  when convert(varchar(10),[CreateOn],120)='" + date.ToString("yyyy-MM-dd") + "' then  rcount else 0 end  ) as '" + date.ToString("MM.dd") + "(" + week + ")' ,");
                date = date.AddDays(1);
            }
            sb2 = sb2.Remove(sb2.Length - 1, 1);
            sb2.Append("  from  ( select {0} ,convert(varchar(10),[CreateOn],120) [CreateOn] ,sum(op.Qty) as 'rcount'  from Orders O left join OrderProducts OP on O.Id=OP.OId where [CreateOn] between '" + st.ToString("yyyy-MM-dd") + "' and '" + et.ToString("yyyy-MM-dd") + " 23:59:59' and SKU='{1}' {2}  group by {0} ,convert(varchar(10),[CreateOn],120)) as tbl1  group by {0} ");

            string sql = "";

            if (p != "ALL")
            {
                sql = string.Format(sb2.ToString(), "Account", s, " and Platform='" + p + "'");
            }
            else
            {
                sql = string.Format(sb2.ToString(), "Platform", s, "");
            }
            IList <object[]> objectes = NSession.CreateSQLQuery(sql).List <object[]>();

            sb.AppendLine("<categories>");
            foreach (string foo in strData)
            {
                sb.Append("<category label='" + foo + "'/>");
            }
            //sb = sb.Remove(sb.Length - 1, 1);
            sb.AppendLine("</categories>");
            foreach (object[] objs in objectes)
            {
                sb.Append("<dataset lineThickness='3' seriesName=\"" + objs[0] + "\">");

                for (int i = 1; i < objs.Length; i++)
                {
                    sb.Append("<set value='" + objs[i] + "' />");
                }


                sb.AppendLine("</dataset>");
            }

            foreach (object[] objs in objectes)
            {
                sb.Append("<dataset lineThickness='3' seriesName='ALL'>");

                for (int i = 1; i < objs.Length; i++)
                {
                    int n = 0;
                    for (int j = 0; j < objectes.Count; j++)
                    {
                        n += Convert.ToInt32(objectes[j][i]);
                    }
                    sb.Append("<set value='" + n + "' />");
                }
                sb.AppendLine("</dataset>");
                break;
            }
            sb.AppendLine("</chart>");
            return(Content(string.Format(sb.ToString(), s, st.ToShortDateString(), et.ToShortDateString())));
        }
示例#19
0
        private List <ProductData> GetSellCount(DateTime st, DateTime et, string a, string p, string s, int page = 0, int rows = 0)
        {
            var sqlWhere = SqlWhere(st, et, a, p, s);

            if (sqlWhere.Length > 3)
            {
                sqlWhere += " and SKU is not null ";
            }
            else
            {
                sqlWhere = " where SKU is not null ";
            }
            IList <object[]> objs =
                NSession.CreateSQLQuery(
                    string.Format(
                        "select SKU,SUM(Qty) as sQty,count(Orders.Id) as Qty from OrderProducts right join Orders on OId=Orders.Id   {0} group by SKU Order By sQty desc",
                        sqlWhere))
                .SetFirstResult(rows * (page - 1))
                .SetMaxResults(rows).
                List <object[]>();

            if (page == 0)
            {
                objs =
                    NSession.CreateSQLQuery(
                        string.Format(
                            "select SKU,SUM(Qty) as sQty,count(Orders.Id) as Qty from OrderProducts right join Orders on OId=Orders.Id   {0} group by SKU Order By sQty desc",
                            sqlWhere)).
                    List <object[]>();
            }
            else
            {
                objs =
                    NSession.CreateSQLQuery(
                        string.Format(
                            "select SKU,SUM(Qty) as sQty,count(Orders.Id) as Qty from OrderProducts right join Orders on OId=Orders.Id   {0} group by SKU Order By sQty desc",
                            sqlWhere))
                    .SetFirstResult(rows * (page - 1))
                    .SetMaxResults(rows).
                    List <object[]>();
            }
            string             sku  = string.Empty;
            List <ProductData> list = new List <ProductData>();

            foreach (object[] objectse in objs)
            {
                sku += objectse[0] + ",";
                ProductData pd = new ProductData();
                pd.SKU  = objectse[0].ToString();
                pd.Qty  = Convert.ToInt32(objectse[1]);
                pd.OQty = Convert.ToInt32(objectse[2]);
                list.Add(pd);
            }

            List <ProductType> products =
                NSession.CreateQuery("from ProductType where SKU in('" + sku.Trim(',').Replace(",", "','") + "')").List
                <ProductType>().ToList();

            foreach (ProductData pp in list)
            {
                ProductType product = products.Find(x => pp.SKU.Trim().ToUpper() == x.SKU.Trim().ToUpper());
                if (product != null)
                {
                    pp.Category   = product.Category;
                    pp.Status     = product.Status;
                    pp.Price      = product.Price;
                    pp.PicUrl     = product.SPicUrl;
                    pp.Title      = product.ProductName;
                    pp.TotalPrice = pp.Price * pp.Qty;
                }
            }
            return(list);
        }
示例#20
0
 public ActionResult Down(string ids)
 {
     NSession.CreateSQLQuery("update EmailTemplate set Enable=0 where Id in(" + ids + ")").UniqueResult();
     NSession.Flush();
     return(Json(new { IsSuccess = true }));
 }
示例#21
0
        public ActionResult SetPrintData(string m, string r, string d, string t)
        {
            d = d.Replace("\r", "").Replace("\n", ",");
            IList <OrderType> objectses = NSession.CreateQuery("from OrderType where IsAudit=0 and OrderNo IN('" +
                                                               d.Replace(",", "','") + "')").List <OrderType>();
            string NotPrint = "";

            foreach (OrderType c in objectses)
            {
                NotPrint += c.OrderNo + " ;";
            }
            string sql = "";

            sql = @"select (select COUNT(1) from OrderProducts where OrderProducts.OId=O.id) as 'GCount',O.IsPrint as 'PCount' ,O.Id,O.OrderNo,o.OrderExNo,O.Account,O.Platform,O.Amount,O.CurrencyCode,O.BuyerEmail,O.BuyerName,O.LogisticMode,O.IsSplit,O.IsRepeat,O.IsAudit,
O.BuyerMemo,O.SellerMemo,O.CreateOn,O.Freight,O.Weight,O.TrackCode,O.Country,OA.Addressee,OA.Street,OA.County,OA.City,OA.Province,
OA.Phone,OA.Tel,OA.PostCode,OA.CountryCode,OP.SKU,OP.Standard,OP.Remark,OP.Title,OP.Qty,OP.ExSKU,P.OldSKU,P.Category,P.SPicUrl,P.OldSKU,P.Location,P.ProductName,
R.RetuanName ,R.City as 'RCity',R.Street as 'RStreet',R.Phone as 'RPhone',R.Tel as 'RTel',R.County as 'RCounty',(select top 1 CCountry from Country where ECountry=O.Country) as CCountry,O.GenerateOn,
R.Country as 'RCountry',R.PostCode as 'RPostCode',R.Province as 'RProvince' from Orders O 
left join OrderProducts OP on o.Id=op.OId
left join OrderAddress OA on o.AddressId=oa.Id
Left Join Products P ON OP.SKU=P.SKU
left join ReturnAddress R On r.Id=" + r;


            //2013.9.22 添加 sal不打印,直接硬编码.--> 看后期是否可以变成过滤模块吧,
            sql += " where O.IsAudit=1 and  O.OrderNo IN('" + d.Replace(",", "','") + "') and O.LogisticMode not like '%sal%' and O.Account not in ('wunderschoen_dream')";
            DataSet    ds      = new DataSet();
            IDbCommand command = NSession.Connection.CreateCommand();

            command.CommandText = sql;
            SqlDataAdapter da = new SqlDataAdapter(command as SqlCommand);

            da.Fill(ds);
            ds.Tables[0].DefaultView.Sort = " OrderNo Asc";
            if (t == "多物品订单")
            {
                ds.Tables[0].DefaultView.RowFilter = " GCount >1";
            }
            else
            {
                List <string> list = new List <string>();
                DataTable     dt1  = ds.Tables[0];
                foreach (DataRow dr in dt1.Rows)
                {
                    if (list.Contains(dr["OrderNo"].ToString()))
                    {
                        dr.Delete();
                    }
                    else
                    {
                        list.Add(dr["OrderNo"].ToString());
                    }
                }
                ds.Tables[0].DefaultView.Sort = " OrderNo Asc";
            }
            DataTable dt = ds.Tables[0].DefaultView.ToTable();

            dt.Columns.Add("PrintName");
            dt.Columns.Add("AreaName");

            List <string> list2 = new List <string>();

            foreach (DataRow dr in dt.Rows)
            {
                dr["PrintName"] = CurrentUser.Realname;
                if (dr["LogisticMode"].ToString() == "BLS")
                {
                    dr["TrackCode"] = "1372100" + dr["OrderNo"].ToString();
                }

                object obj = NSession.CreateSQLQuery("select top 1 AreaName from [LogisticsArea] where LId = (select top 1 ParentID from LogisticsMode where LogisticsCode='" + dr["LogisticMode"] + "')  and Id =(select top 1 AreaCode from LogisticsAreaCountry where [LogisticsArea].Id=AreaCode  and CountryCode in (select ID from Country where ECountry='" + dr["Country"] + "') )").UniqueResult();
                dr["AreaName"] = obj;
                if (list2.Contains(dr["OrderNo"].ToString()))
                {
                }
                else
                {
                    LoggerUtil.GetOrderRecord(Convert.ToInt32(dr["Id"]), dr["OrderNo"].ToString(), "订单打印", CurrentUser.Realname + "订单打印!", CurrentUser, NSession);
                    list2.Add(dr["OrderNo"].ToString());
                }
            }
            //标记打印
            NSession.CreateQuery("update OrderType set IsPrint=IsPrint+1 where  IsAudit=1 and  OrderNo IN('" + d.Replace(",", "','") + "') and LogisticMode not like '%sal%'").ExecuteUpdate();
            ds.Tables.Clear();
            ds.Tables.Add(dt);
            if (NotPrint != "")
            {
                NotPrint = "有" + objectses.Count + "条订单没有审核,无法发打印,订单号:" + NotPrint;
            }
            PrintDataType data = new PrintDataType();

            data.Content  = ds.GetXml();
            data.CreateOn = DateTime.Now;
            NSession.Save(data);
            NSession.Flush();
            return(Json(new { IsSuccess = true, Result = data.Id }));
        }