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 }));
}
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 = "成功！" }));
}
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 }));
}
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 }));
}
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 }));
}
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 }));
}
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 }));
}
}
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);
}
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 = "导出成功" }));
}
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 }));
}
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 }));
}
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);
}
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 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())));
}
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);
}
public ActionResult Down(string ids)
{
NSession.CreateSQLQuery("update EmailTemplate set Enable=0 where Id in(" + ids + ")").UniqueResult();
NSession.Flush();
return(Json(new { IsSuccess = true }));
}
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 }));
}