/// <summary>
/// 获取拆单订单的主订单ID
/// </summary>
/// <returns></returns>
public DataTable getSourceParentOrderId(int pageIndex, int pageSize)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = (from c in context.apiSendOrder
group c by new
{
orderId = c.orderId,
showstatus = c.showStatus,
newStatus = c.newStatus
} into g
select new
{
orderId = g.Key.orderId,
showstatus = g.Key.showstatus,
newStatus = g.Key.newStatus
}).Skip((pageIndex - 1) * pageSize).Take(pageSize);
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// Top20 品牌以及类别
/// </summary>
/// <returns></returns>
public string TopBrand()
{
StringBuilder s = new StringBuilder(200);
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var sql = @"select top 20 BrandName,SUM(Balance) as balance from productstock a left join brand b on a.Cat=b.BrandAbridge where BrandName is not null group by BrandName order by balance desc";
var sql1 = @"select top 20 TypeName,SUM(Balance) as balance from productstock a left join producttype b on a.Cat2=b.TypeNo where TypeName is not null group by TypeName order by balance desc";
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
DataTable dt1 = DbHelperSQL.Query(sql1).Tables[0];
for (int i = 0; i < 20; i++)
{
int n = i + 1;
s.Append(n + "." + dt.Rows[i]["BrandName"].ToString() + "<br />");
}
s.Append("-*-");
for (int i = 0; i < 20; i++)
{
int n = i + 1;
s.Append(n + "." + dt1.Rows[i]["TypeName"].ToString() + "<br />");
}
return(s.ToString());
}
/// <summary>
/// 退款报表 Convert.ToDateTime(Convert.ToDateTime(c.Def3).ToString("yyyy-mm-dd"))}
/// </summary>
/// <returns></returns>
public DataTable GetRefundReport(string MinTime, string MaxTime)
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = from c in context.ProductInfo
where c.Def1 == "6"//表示退款的订单
group c by Convert.ToDateTime(c.Def3 == null? "2000-01-01" : c.Def3.ToString().Substring(0, 10)) into t
select new
{
Def3 = Convert.ToDateTime(t.Key == null ? "2000-01-01" : t.Key.ToString().Substring(0, 10)), //退款日期
counts = t.Count(), //退款数量
SellPrice = t.Sum(c => Convert.ToDecimal((c.SellPrice == null || c.SellPrice == "") ? "0" : c.SellPrice)), //退款总金额c => Convert.ToDecimal(c.SellPrice)
};
if (MinTime != "")
{
if (MaxTime != "")
{
q = q.Where(a => a.Def3 >= Convert.ToDateTime(MinTime) && a.Def3 <= Convert.ToDateTime(MaxTime));
}
else
{
q = q.Where(a => a.Def3 >= Convert.ToDateTime(MinTime));
}
}
else
{
if (MaxTime != "")
{
q = q.Where(a => a.Def3 <= Convert.ToDateTime(MaxTime));
}
}
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 更新报文状态(联邦--更新数据表)
/// </summary>
/// <param name="resultStatus"></param>
/// <returns></returns>
public string updateUploadStatus2(model.productCustomsResult resultStatus)
{
string s = string.Empty;
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = (from c in context.productCustomsResult where c.productScode == resultStatus.productScode select c).SingleOrDefault();
p.BBCask = resultStatus.BBCask;
p.BBCerrorMessage = resultStatus.BBCerrorMessage;
p.BBCmessage = resultStatus.BBCmessage;
p.BBCReturnData = resultStatus.BBCReturnData;
p.BBCskuNo = resultStatus.BBCskuNo;
try
{
context.SubmitChanges();
s = "更新商检成功";
}
catch
{
s = "更新商检失败";
}
return(s);
}
/// <summary>
/// 根据权限字段组成sql语句返回集合(可带条件查询)
/// </summary>
/// <param name="dic">查询参赛</param>
/// <returns></returns>
public DataTable getData(Dictionary <string, string> dic = null)
{
List <model.users> list = new List <model.users>();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = from c in context.users select c;
foreach (var item in dic)
{
string ikey = item.Key; //名称
int ivalue = 0; //值
int.TryParse(item.Value, out ivalue);
if (item.Key == "roleId") //角色
{
p = p.Where(c => c.personaId == ivalue);
}
}
DataTable dt = LinqToDataTable.LINQToDataTable <model.users>(p);
return(dt);
}
/// <summary>
/// 更新报文状态(海关--更新数据表)
/// </summary>
/// <param name="resultStatus"></param>
/// <returns></returns>
public string updateUploadStatus1(model.orderCustomsResult resultStatus)
{
string s = string.Empty;
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = (from c in context.orderCustomsResult where c.SJOrgOrderChildId == resultStatus.SJOrgOrderChildId select c).SingleOrDefault();
//HGReturnDate,HGReturnCode,HGReturnInfo,HGAttachedFlag,HGstatus
p.HGReturnDate = resultStatus.HGReturnDate;
p.HGReturnCode = resultStatus.HGReturnCode;
p.HGReturnInfo = resultStatus.HGReturnInfo;
p.HGAttachedFlag = resultStatus.HGAttachedFlag;
p.HGstatus = resultStatus.HGstatus;
try
{
context.SubmitChanges();
s = "更新海关成功";
}
catch
{
s = "更新海关失败";
}
return(s);
}
public string GetProvice()
{
string s = string.Empty;
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
string RegionName = Request.Form["RegionName"].ToString();
var q = context.AreaTable.Where(a => a.Parent_id == 0);
dt = LinqToDataTable.LINQToDataTable(q);
s += "<option value=''>请选择</option>";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (RegionName == dt.Rows[i]["Region_name"].ToString())
{
s += "<option value='" + dt.Rows[i]["Region_id"] + "' selected='selected' >" + dt.Rows[i]["Region_name"] + "</option>";
}
else
{
s += "<option value='" + dt.Rows[i]["Region_id"] + "'>" + dt.Rows[i]["Region_name"] + "</option>";
}
}
return(s);
}
/// <summary>
/// 退款退货统计-总数
/// </summary>
/// <returns></returns>
public string Refund()
{
try
{
string s = string.Empty;
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
DateTime Mindate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
DateTime Maxdate = Convert.ToDateTime(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
var q = from c in context.RetProductInfo select c;
var Refund = 0; //--退款笔数
var RefundM = Convert.ToDecimal(0); //--退款金额
var ReturnGoods = 0; //--退货笔数
var ReturnGoodsM = Convert.ToDecimal(0); //--退货金额
var ReturnBalance = Convert.ToDecimal(0); //--退货件数
var qc = from c in context.RetBalance select c; //--判断退货数据
if (q.Where(a => a.Def1 == "3").Count() != 0)
{
Refund = q.Where(a => a.Def1 == "3").Count(); //--退款笔数
RefundM = q.Where(a => a.Def1 == "3").Sum(a => Convert.ToDecimal(a.RetPrice == null ? "0" : a.RetPrice)); //--退款金额
}
if (q.Where(a => a.Def1 == "4").Count() != 0)
{
ReturnGoods = q.Where(a => a.Def1 == "4").Count(); //--退货笔数
ReturnGoodsM = q.Where(a => a.Def1 == "4").Sum(a => Convert.ToDecimal(a.RetPrice == null ? "0" : a.RetPrice)); //--退货金额
}
if (qc.Count() != 0)
{
ReturnBalance = qc.Sum(a => Convert.ToDecimal(a.Number == null ? "0" : a.Number));//--退货件数
}
q = q.Where(a => a.Def4 >= Mindate && a.Def4 < Maxdate);
var Refund1 = 0; //--退款笔数
var RefundM1 = Convert.ToDecimal(0); //--退款金额
var ReturnGoods1 = 0; //--退货笔数
var ReturnGoodsM1 = Convert.ToDecimal(0); //--退货金额
var ReturnBalance1 = Convert.ToDecimal(0); //--退货件数
q = q.Where(a => a.Def4 >= Mindate && a.Def4 < Maxdate); //--今日
qc = qc.Where(a => a.RetTime >= Mindate && a.RetTime < Maxdate);
if (q.Count() != 0)
{
if (q.Where(a => a.Def1 == "3").Count() != 0)
{
Refund1 = q.Where(a => a.Def1 == "3").Count(); //--退款笔数
RefundM1 = q.Where(a => a.Def1 == "3").Sum(a => Convert.ToDecimal(a.RetPrice == null ? "0" : a.RetPrice)); //--退款金额
}
if (q.Where(a => a.Def1 == "4").Count() != 0)
{
ReturnGoods1 = q.Where(a => a.Def1 == "4").Count(); //--退货笔数
ReturnGoodsM1 = q.Where(a => a.Def1 == "4").Sum(a => Convert.ToDecimal(a.RetPrice == null ? "0" : a.RetPrice)); //--退货金额
}
if (qc.Count() != 0)
{
ReturnBalance1 = qc.Sum(a => Convert.ToDecimal(a.Number == null ? "0" : a.Number));//--退货件数
}
}
////--判断参数为空的时候为0
//RefundM = RefundM == null ? 0 : RefundM;
//ReturnGoodsM = ReturnGoodsM == null ? 0 : ReturnGoodsM;
//ReturnBalance = ReturnBalance == null ? 0 : ReturnBalance;
s += "[{";
s += "\"Refund\":" + Refund + ",\"RefundM\":" + Convert.ToDecimal(RefundM).ToString("f2") + ",\"ReturnGoods\":" + ReturnGoods + ",\"ReturnGoodsM\":" + Convert.ToDecimal(ReturnGoodsM).ToString("f2") + ",\"ReturnBalance\":" + Convert.ToInt32(ReturnBalance) + ",";
s += "\"Refund1\":" + Refund1 + ",\"RefundM1\":" + Convert.ToDecimal(RefundM1).ToString("f2") + ",\"ReturnGoods1\":" + ReturnGoods1 + ",\"ReturnGoodsM1\":" + Convert.ToDecimal(ReturnGoodsM1).ToString("f2") + ",\"ReturnBalance1\":" + Convert.ToInt32(ReturnBalance1) + "";
s += "}]";
return(s);
}
catch (Exception ex)
{
return(ex.ToString());
}
}
/// <summary>
/// 获取来货报表（HK数据源)
/// </summary>
/// <returns></returns>
public DataTable GetLaiHuoReport(Dictionary <string, string> Dic, int pageIndex, int pageSize, out string counts, out string Balances)
{
string s = string.Empty;
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var q = from c in context.productstock
where c.Cat != null && c.Cat != "" && c.Vencode == "1"
group c by new { c.Cat, c.Cat2, c.Lastgrnd } into t
select new
{
Cat = t.Key.Cat, //品牌缩写
Cat2 = t.Key.Cat2, //类别编号
Lastgrnd = t.Key.Lastgrnd, //来货时间
Balance = t.Sum(a => a.Balance), //来货库存
};
var q1 = from c in q
join b in context.brand on c.Cat equals b.BrandAbridge
into bb
from bbb in bb.DefaultIfEmpty()
join p in context.producttype on c.Cat2 equals p.TypeNo
into pp
from ppp in pp.DefaultIfEmpty()
select new
{
Cat = c.Cat, //品牌缩写
BrandName = bbb.BrandName, //品牌名称
Cat2 = c.Cat2, //类别编号
TypeName = ppp.TypeName, //类别名称
Balance = c.Balance, //来货库存
Lastgrnd = c.Lastgrnd, //来货时间
};
if (Dic["Cat"] != "")
{
q1 = q1.Where(a => a.Cat == Dic["Cat"]);
}
if (Dic["Cat2"] != "")
{
q1 = q1.Where(a => a.Cat2 == Dic["Cat2"]);
}
if (Dic["Mindate"] != "")
{
if (Dic["Maxdate"] != "")
{
q1 = q1.Where(a => a.Lastgrnd >= Convert.ToDateTime(Dic["Mindate"]) && a.Lastgrnd <= Convert.ToDateTime(Dic["Maxdate"]));
}
else
{
q1 = q1.Where(a => a.Lastgrnd >= Convert.ToDateTime(Dic["Mindate"]));
}
}
else
{
if (Dic["Maxdate"] != "")
{
q1 = q1.Where(a => a.Lastgrnd <= Convert.ToDateTime(Dic["Maxdate"]));
}
}
counts = q1.Count().ToString(); //返回查询数量
int?balanceCount = q1.Sum(a => a.Balance); //返回查询库存
Balances = balanceCount.ToString();
q1 = q1.OrderByDescending(a => a.Balance).OrderByDescending(a => a.Lastgrnd);//根据库存和来货时间排序
if (pageIndex == 0)
{
dt = LinqToDataTable.LINQToDataTable(q1.Take(pageSize));
}
else
{
dt = LinqToDataTable.LINQToDataTable(q1.Skip((pageIndex - 1) * pageSize).Take(pageSize));
}
return(dt);
}
/// <summary>
/// 根据子订单ID修改此商品相关信息
/// </summary>
/// <returns></returns>
public string orderUpdate(string orderchildenid, Dictionary <string, string> Dic)
{
//Dic["orderXSFS1"].ToString()销售方式------------暂未用到
//Dic["orderCHCK1"].ToString()出库源头------------暂未用到
DataTable dt = new DataTable();
try
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var c = context.OrderDetails.Where(a => a.OrderChildenId == orderchildenid);
foreach (var a in c)
{
a.OrderScode = Dic["orderScode1"].ToString();//--货号
}
//var o = context.porder.Where(a => a.OrderId == Dic["orderid1"]);
//foreach (var a in o)
//{
// a.CustomServerId = Convert.ToInt32(Dic["orderSSKF1"].ToString());//--所属客服
//}
var e = (context.OrderExpress.Where(a => a.OrderId == Dic["orderid1"])).FirstOrDefault();
e.ExpressName = Dic["CourierCompanies1"].ToString(); //--快递公司
e.ExpressNo = Dic["CourierNo1"].ToString(); //--快递编号
//context.OrderExpress.Attach(e);
//if (Dic["CourierCompanies1"].ToString() != "")
//{
//
//}
//if (Dic["CourierNo1"].ToString() != "")
//{
//
//}
if (Dic["orderSSKF1"].ToString() != "-1")
{
var o = (context.porder.Where(a => a.OrderId == Dic["orderid1"])).FirstOrDefault();
o.CustomServerId = Convert.ToInt32(Dic["orderSSKF1"].ToString());
}
//foreach (var a in e)
//{
// a.ExpressName = Dic["CourierCompanies1"].ToString();//--快递公司
// a.ExpressNo = Dic["CourierNo1"].ToString();//--快递编号
//}
var oc = context.orderComments.Where(a => a.ocOrderId == Dic["orderid1"]);
foreach (var a in oc)
{
a.ocOtherPrice = Convert.ToDecimal(Dic["orderOtherMoney1"].ToString()); //--其他费用
a.ocRemark = Dic["orderRemark"].ToString(); //--备注
a.ocPostPrice = Convert.ToDecimal(Dic["orderPostage1"].ToString()); //--邮费
}
context.SubmitChanges();
errordal.InsertErrorlog(new model.errorlog()
{
errorSrc = "pbxdata.dal->shoporderdal->orderUpdate()",
ErrorMsg = "修改",
errorTime = DateTime.Now,
operation = 2,
errorMsgDetails = "通过子订单号修改信息->" + orderchildenid,
UserId = Convert.ToInt32(Dic["UserId"].ToString())
});
return("修改成功!");
}
catch (Exception ex)
{
errordal.InsertErrorlog(new model.errorlog()
{
errorSrc = "pbxdata.dal->shoporderdal->orderUpdate()",
ErrorMsg = "修改",
errorTime = DateTime.Now,
operation = 1,
errorMsgDetails = ex.Message,
UserId = Convert.ToInt32(Dic["UserId"].ToString())
});
return("修改失败!");
}
}
/// <summary>
/// 根据子订单ID获取此商品相关信息
/// </summary>
/// <returns></returns>
public DataTable orderEdit(string orderchildenid)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = (from c in context.OrderDetails
join o in context.porder on c.OrderId equals o.OrderId
//join u in context.users on o.CustomServerId equals u.Id
join pd in context.productstock on c.OrderScode equals pd.Scode into g
from mm in g.DefaultIfEmpty()
join aso in
(from c1 in context.activeShopOrder join c2 in context.activeShop on c1.acId equals c2.acId select new { acScode = c1.asoScode, acName = c2.acName }) on mm.Scode equals aso.acScode into g1
from mm1 in g1.DefaultIfEmpty()
join os in context.orderComments on c.OrderId equals os.ocOrderId into g2
from mm2 in g2.DefaultIfEmpty()
join oe in context.OrderExpress on c.OrderId equals oe.OrderId into g3
from mm3 in g3.DefaultIfEmpty()
//join pt in context.product on c.OrderScode equals pt.Bcode into g4
//from mm4 in g4.DefaultIfEmpty()
join u in context.users on o.CustomServerId equals u.Id into g5
from mm5 in g5.DefaultIfEmpty()
orderby c.OrderSucessTime descending, c.OrderId ascending
where c.OrderChildenId == orderchildenid
select new
{
OrderId = o.OrderId, //订单编号
OrderChildenId = c.OrderChildenId,
ShopName = o.ShopName, //店铺名称
OrderTime = o.OrderTime,
OrderPayTime = o.OrderPayTime,
OrderSendTime = c.OrderSendTime,
OrderSucessTime = c.OrderSucessTime,
OrderNick = o.OrderNick,
OrderState = o.OrderState,
//OrderState1 = o.OrderState1,
PayState = o.PayState,
CustomServerId = o.CustomServerId,
DetailsName = c.DetailsName,
OrderScode = c.OrderScode,
OrderImg = c.OrderImg,
DetailsSum = c.DetailsSum,
DetailsPrice = c.DetailsPrice,
Scode = mm.Scode,
Cat = mm.Cat,
Cat2 = mm.Cat2,
Clolor = mm.Clolor,
Pricea = mm.Pricea,
Priceb = mm.Priceb,
Pricec = mm.Pricec,
Priced = mm.Priced,
Pricee = mm.Pricee,
acName = mm1.acName,
ocPostPrice = mm2.ocPostPrice,
ocOtherPrice = mm2.ocOtherPrice,
ocBanner = mm2.ocBanner,
ocRemark = mm2.ocRemark,
ocComment = mm2.ocComment,
//扣点，利润，出库源头
ExpressNo = mm3.ExpressNo,
ExpressName = mm3.ExpressName,
CustomName = mm3.CustomName,
CustomPhone = mm3.CustomPhone,
CustomCity1 = mm3.CustomCity1,
CustomCity2 = mm3.CustomCity2,
CustomCity3 = mm3.CustomCity3,
CustomAddress = mm3.CustomAddress,
CustomerName = mm5.userRealName
});
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 获取拆单订单的主订单ID
/// </summary>
/// <returns></returns>
public DataTable getSourceParentOrderId(Dictionary <string, string> Dic, int pageIndex, int pageSize, out int counts)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var q = from c in context.apiSendOrder
join d in context.apiOrderPayDetails on c.orderId equals d.orderId
group c by new { orderId = c.orderId, createTime = c.createTime, editTime = c.editTime, showStatus = c.showStatus, sendSource = c.sendSource, payId = d.payId }
into g
select new
{
orderId = g.Key.orderId,
newStatus = g.Average(c => c.newStatus),
createTime = g.Key.createTime,
editTime = g.Key.editTime,
showStatus = g.Key.showStatus,
sendSource = g.Key.sendSource,
payId = g.Key.payId
};
if (Dic["orderId"] != "") //订单ID
{
q = q.Where(a => a.orderId == Dic["orderId"]);
}
if (Dic["newStatus"] != "") //订单状态
{
q = q.Where(a => a.newStatus == Convert.ToInt32(Dic["newStatus"]));
}
#region 时间条件查询
if (Dic["createTime"] != "" && Dic["editTime"] != "") //创建时间
{
q = q.Where(a => a.createTime >= DateTime.Parse(Dic["createTime"]) && a.editTime <= DateTime.Parse(Dic["editTime"]));
}
else if (Dic["editTime"] != "")//结束时间
{
q = q.Where(a => a.editTime <= DateTime.Parse(Dic["editTime"]));
}
else if (Dic["createTime"] != "") //创建时间
{
q = q.Where(a => a.createTime >= DateTime.Parse(Dic["createTime"]));
}
#endregion
if (Dic["showStatus"] != "")//开放状态
{
q = q.Where(a => a.showStatus == Convert.ToInt32(Dic["showStatus"]));
}
if (Dic["sendSource"] != "")//供应商
{
q = q.Where(a => a.sendSource == Dic["sendSource"]);
}
if (Dic["SJstatus"] != "") //商检报关状态(0失败，1成功，2未上传)
{
var sjstatus = Dic["SJstatus"];
if (sjstatus == "2")
{
List <string> sjlist = new List <string>();
var sj = context.orderCustomsResult.Where(a => a.SJstatus == "0" || a.SJstatus == "1");
foreach (var i in sj)
{
sjlist.Add(i.SJOrgOrderChildId);
}
q = q.Where(a => !sjlist.Contains(a.payId));
}
else
{
List <string> sjlist = new List <string>();
var sj = context.orderCustomsResult.Where(a => a.SJstatus == sjstatus);
foreach (var i in sj)
{
sjlist.Add(i.SJOrgOrderChildId);
}
q = q.Where(a => sjlist.Contains(a.payId));
}
}
if (Dic["HGstatus"] != "") //海关报关状态(0失败，1成功，2未上传)
{
var hgstatus = Dic["HGstatus"];
if (hgstatus == "2")
{
List <string> hglist = new List <string>();
var sj = context.orderCustomsResult.Where(a => a.HGstatus == "0" || a.HGstatus == "1");
foreach (var i in sj)
{
hglist.Add(i.SJOrgOrderChildId);
}
q = q.Where(a => !hglist.Contains(a.payId));
}
else
{
List <string> hglist = new List <string>();
var sj = context.orderCustomsResult.Where(a => a.HGstatus == hgstatus);
foreach (var i in sj)
{
hglist.Add(i.SJOrgOrderChildId);
}
q = q.Where(a => hglist.Contains(a.payId));
}
}
if (Dic["BBCstatus"] != "") //联邦报关状态(0失败，1成功，2未上传)
{
var bbcstatus = Dic["BBCstatus"];
if (bbcstatus == "2")
{
List <string> bbclist = new List <string>();
var sj = context.orderCustomsResult.Where(a => a.HGstatus == "0" || a.HGstatus == "1");
foreach (var i in sj)
{
bbclist.Add(i.SJOrgOrderChildId);
}
q = q.Where(a => !bbclist.Contains(a.payId));
}
else
{
List <string> bbclist = new List <string>();
var sj = context.orderCustomsResult.Where(a => a.BBCstatus == bbcstatus);
foreach (var i in sj)
{
bbclist.Add(i.SJOrgOrderChildId);
}
q = q.Where(a => bbclist.Contains(a.payId));
}
}
if (Dic["Paystatus"] != "") //支付报关状态(fail失败，success成功)
{
var payStatus = string.Empty;
if (Dic["Paystatus"] == "1") //支付成功
{
payStatus = "SUCCESS";
}
else if (Dic["Paystatus"] == "0") //支付失败
{
payStatus = "FAIL";
}
if (string.IsNullOrWhiteSpace(payStatus))
{
List <string> paylist = new List <string>();
var sj = context.payCustomsResult.Where(a => a.result_code == "SUCCESS" || a.result_code == "FAIL");
foreach (var i in sj)
{
paylist.Add(i.OrderChildId);
}
q = q.Where(a => !paylist.Contains(a.payId));
}
else
{
List <string> paylist = new List <string>();
var sj = context.payCustomsResult.Where(a => a.result_code == payStatus);
foreach (var i in sj)
{
paylist.Add(i.OrderChildId);
}
q = q.Where(a => paylist.Contains(a.payId));
}
}
counts = q.ToList().Count;
var p = q.OrderByDescending(c => c.createTime).Skip((pageIndex - 1) * pageSize).Take(pageSize);
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 销售统计--根据日期
/// </summary>
/// <returns></returns>
public string ReturnInfo(Dictionary <string, string> Dic)
{
string s = string.Empty;
string Mintime = Dic["Mintime"];
string Maxtime = Dic["Maxtime"];
DateTime Mindate = Mintime == "" || Mintime == "1" ? Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")) : Convert.ToDateTime(Mintime);
DateTime Maxdate = Maxtime == "" || Maxtime == "1" ? Convert.ToDateTime(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")) : Convert.ToDateTime(Maxtime);
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var q = from c in context.apiOrderDetails
join t in context.apiOrder on c.orderId equals t.orderId
into tt
from ss in tt.DefaultIfEmpty()
select new
{
OrderId = c.orderId, //--订单号
paidPrice = ss.paidPrice, //-- 实际支付价格
orderPrice = ss.orderPrice, //--订单金额
isPay = ss.isPay, //--是否支付0：未支付，1：已支付
detailsSaleCount = c.detailsSaleCount, //--交易数量
createTime = ss.createTime, //--创建时间
};
if (Mintime == "1")
{
q = q.Where(a => a.createTime <= Maxdate && a.createTime >= Mindate);
}
else
{
if (Mintime != "")
{
if (Maxtime != "")
{
q = q.Where(a => a.createTime <= Maxdate && a.createTime >= Mindate);
}
else
{
q = q.Where(a => a.createTime >= Mindate);
}
}
else
{
if (Maxtime != "")
{
q = q.Where(a => a.createTime <= Maxdate);
}
}
}
//根据日期查询
var Count = q.Where(a => a.isPay == 1).GroupBy(a => a.OrderId).Count(); //销售所有数量
var SumPrice = q.Where(a => a.isPay == 1).Sum(a => a.orderPrice); //销售已支付总金额
SumPrice = SumPrice == null ? 0 : SumPrice;
var BalaceCount = q.Where(a => a.isPay == 1).Sum(a => a.detailsSaleCount);//销售已支付总库存
BalaceCount = BalaceCount == null ? 0 : BalaceCount;
var SumPrice1 = q.Where(a => a.isPay == 0).Sum(a => a.orderPrice);//销售未支付总金额
SumPrice1 = SumPrice1 == null ? 0 : SumPrice1;
var BalaceCount1 = q.Where(a => a.isPay == 0).Sum(a => a.detailsSaleCount);//销售未支付库存
BalaceCount1 = BalaceCount1 == null ? 0 : BalaceCount1;
//s += "<ul class='ulCount'>";
//s += "<li>今日支付笔数:<span>" + tCount + "</span>.</li>";
//s += "<li>今日支付总金额:<span>" + Convert.ToDecimal(tSumPrice).ToString("f2") + "</span>￥</li>";
//s += "<li>今日销售总件:<span>" + tBalaceCount + "</span>.</li>";
//s += "<li>今日未支付笔数:<span>" + tBalaceCount1 + "</span>.</li>";
//s += "<li>今日未支付金额:<span>" + Convert.ToDecimal(tSumPrice1).ToString("f2") + "</span>￥.</li>";
//s += "</ul>";
s = "[{\"Count\":" + Count + ",\"SumPrice\":" + Convert.ToDecimal(SumPrice).ToString("f2") + ",\"BalaceCount\":" + BalaceCount + ",\"SumPrice1\":" + Convert.ToDecimal(SumPrice1).ToString("f2") + ",\"BalaceCount1\":" + BalaceCount1 + "}]";
return(s);
}
/// <summary>
/// 获取店铺订单数据(分页)
/// </summary>
/// <returns></returns>
public DataTable getData(int pageIndex, int pageSize, string orderId, string scode, string brand, string type, string status, string shopname, string servicecustom, string buynick, string price1, string price2, string pprice1, string pprice2, string ordertime1, string ordertime2, string paytime1, string paytime2, string sendtime1, string sendtime2, string sucesstime1, string sucesstime2)
{
DataTable dt = new DataTable();
string mys = string.Empty;
mys = string.IsNullOrWhiteSpace(mys) ? string.Empty : mys;
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = (from c in context.OrderDetails
join o in context.porder on c.OrderId equals o.OrderId
//join u in context.users on o.CustomServerId equals u.Id
join pd in context.productstock on c.OrderScode equals pd.Scode into g
from mm in g.DefaultIfEmpty()
join aso in
(from c1 in context.activeShopOrder join c2 in context.activeShop on c1.acId equals c2.acId select new { acScode = c1.asoScode, acName = c2.acName }) on mm.Scode equals aso.acScode into g1
from mm1 in g1.DefaultIfEmpty()
join os in context.orderComments on c.OrderId equals os.ocOrderId into g2
from mm2 in g2.DefaultIfEmpty()
join oe in context.OrderExpress on c.OrderId equals oe.OrderId into g3
from mm3 in g3.DefaultIfEmpty()
orderby c.OrderTime descending, c.OrderId ascending
select new
{
OrderId = o.OrderId,
OrderChildenId = c.OrderChildenId,
ShopName = o.ShopName,
OrderTime = o.OrderTime,
OrderPayTime = o.OrderPayTime,
OrderSendTime = c.OrderSendTime,
OrderSucessTime = c.OrderSucessTime,
OrderNick = o.OrderNick,
OrderState = o.OrderState,
//OrderState1 = o.OrderState1,
PayState = o.PayState,
CustomServerId = o.CustomServerId,
DetailsName = c.DetailsName,
OrderScode = c.OrderScode,
OrderImg = c.OrderImg,
DetailsSum = c.DetailsSum,
DetailsPrice = c.DetailsPrice,
Cat = mm.Cat,
Cat2 = mm.Cat2,
Clolor = mm.Clolor,
Pricee = mm.Pricee,
acName = mm1.acName,
ocPostPrice = mm2.ocPostPrice,
ocOtherPrice = mm2.ocOtherPrice,
ocBanner = mm2.ocBanner,
ocRemark = mm2.ocRemark,
ocComment = mm2.ocComment,
//扣点，利润，出库源头
ExpressNo = mm3.ExpressNo,
ExpressName = mm3.ExpressName,
CustomName = mm3.CustomName,
CustomPhone = mm3.CustomPhone,
CustomCity1 = mm3.CustomCity1,
CustomCity2 = mm3.CustomCity2,
CustomCity3 = mm3.CustomCity3,
CustomAddress = mm3.CustomAddress
});
if (!string.IsNullOrWhiteSpace(orderId))
{
p = p.Where(c => c.OrderId == orderId);
}
if (!string.IsNullOrWhiteSpace(scode))
{
p = p.Where(c => c.OrderScode == scode);
}
if (!string.IsNullOrWhiteSpace(brand))
{
p = p.Where(c => c.Cat == brand);
}
if (!string.IsNullOrWhiteSpace(type))
{
p = p.Where(c => c.Cat2 == type);
}
if (!string.IsNullOrWhiteSpace(status))
{
p = p.Where(c => c.OrderState == int.Parse(status));
}
//if (!string.IsNullOrWhiteSpace(shopname))
//{
// p = p.Where(c => c.OrderId == shopname);
//}
if (!string.IsNullOrWhiteSpace(servicecustom))
{
p = p.Where(c => c.CustomServerId == int.Parse(servicecustom));
}
if (!string.IsNullOrWhiteSpace(buynick))
{
p = p.Where(c => c.OrderNick == buynick);
}
//if (!string.IsNullOrWhiteSpace(price1)&&!string.IsNullOrWhiteSpace(price2)) //成本价
//{
// p = p.Where(c => c.OrderPrice >= DateTime.Parse(price1) && c.OrderTime <= DateTime.Parse(price2));
//}
//else if (!string.IsNullOrWhiteSpace(price1))
//{
// p = p.Where(c => c.OrderTime >= DateTime.Parse(price1));
//}
//else if (!string.IsNullOrWhiteSpace(price2))
//{
// p = p.Where(c => c.OrderTime <= DateTime.Parse(price2));
//}
if (!string.IsNullOrWhiteSpace(pprice1) && !string.IsNullOrWhiteSpace(pprice2)) //价格
{
p = p.Where(c => c.DetailsPrice >= decimal.Parse(pprice1) && c.DetailsPrice <= decimal.Parse(pprice2));
}
else if (!string.IsNullOrWhiteSpace(pprice1))
{
p = p.Where(c => c.DetailsPrice >= decimal.Parse(pprice1));
}
else if (!string.IsNullOrWhiteSpace(price2))
{
p = p.Where(c => c.DetailsPrice <= decimal.Parse(pprice2));
}
if (!string.IsNullOrWhiteSpace(ordertime1) && !string.IsNullOrWhiteSpace(ordertime2))
{
p = p.Where(c => c.OrderTime >= DateTime.Parse(ordertime1) && c.OrderTime <= DateTime.Parse(ordertime2));
}
else if (!string.IsNullOrWhiteSpace(ordertime1))
{
p = p.Where(c => c.OrderTime >= DateTime.Parse(ordertime1));
}
else if (!string.IsNullOrWhiteSpace(ordertime2))
{
p = p.Where(c => c.OrderTime <= DateTime.Parse(ordertime2));
}
if (!string.IsNullOrWhiteSpace(paytime1) && !string.IsNullOrWhiteSpace(paytime2))
{
p = p.Where(c => c.OrderPayTime >= DateTime.Parse(paytime1) && c.OrderPayTime <= DateTime.Parse(paytime2));
}
else if (!string.IsNullOrWhiteSpace(paytime1))
{
p = p.Where(c => c.OrderPayTime >= DateTime.Parse(paytime1));
}
else if (!string.IsNullOrWhiteSpace(paytime2))
{
p = p.Where(c => c.OrderPayTime <= DateTime.Parse(paytime2));
}
if (!string.IsNullOrWhiteSpace(sendtime1) && !string.IsNullOrWhiteSpace(sendtime2))
{
p = p.Where(c => c.OrderSendTime >= DateTime.Parse(sendtime1) && c.OrderSendTime <= DateTime.Parse(sendtime2));
}
else if (!string.IsNullOrWhiteSpace(sendtime1))
{
p = p.Where(c => c.OrderSendTime >= DateTime.Parse(sendtime1));
}
else if (!string.IsNullOrWhiteSpace(sendtime2))
{
p = p.Where(c => c.OrderSendTime <= DateTime.Parse(sendtime2));
}
if (!string.IsNullOrWhiteSpace(sucesstime1) && !string.IsNullOrWhiteSpace(sucesstime2))
{
p = p.Where(c => c.OrderSucessTime >= DateTime.Parse(sucesstime1) && c.OrderSucessTime <= DateTime.Parse(sucesstime2));
}
else if (!string.IsNullOrWhiteSpace(sucesstime1))
{
p = p.Where(c => c.OrderSucessTime >= DateTime.Parse(sucesstime1));
}
else if (!string.IsNullOrWhiteSpace(sucesstime2))
{
p = p.Where(c => c.OrderSucessTime <= DateTime.Parse(sucesstime2));
}
p = p.Skip((pageIndex - 1) * pageSize).Take(pageSize);
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
public string ReturnInfo()
{
string s = string.Empty;
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var q = from c in context.apiOrderDetails
join t in context.apiOrder on c.orderId equals t.orderId
into tt
from ss in tt.DefaultIfEmpty()
select new
{
OrderId = c.orderId, //--订单号
paidPrice = ss.paidPrice, //-- 实际支付价格
isPay = ss.isPay, //--是否支付
detailsSaleCount = c.detailsSaleCount, //--交易数量
createTime = ss.createTime, //--创建时间
};
DateTime today = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
DateTime tomorrow = Convert.ToDateTime(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
var Count = q.Where(a => a.isPay == 1).GroupBy(a => a.OrderId).Count();
var SumPrice = q.Where(a => a.isPay == 1).Sum(a => a.paidPrice);
SumPrice = SumPrice == null ? 0 : SumPrice;
var BalaceCount = q.Where(a => a.isPay == 1).Sum(a => a.detailsSaleCount);
BalaceCount = BalaceCount == null ? 0 : BalaceCount;
var SumPrice1 = q.Where(a => a.isPay == 2).Sum(a => a.paidPrice);
SumPrice1 = SumPrice1 == null ? 0 : SumPrice1;
var BalaceCount1 = q.Where(a => a.isPay == 2).Sum(a => a.detailsSaleCount);
BalaceCount1 = BalaceCount1 == null ? 0 : BalaceCount1;
var tCount = q.Where(a => a.isPay == 1 && a.createTime <tomorrow && a.createTime> today).GroupBy(a => a.OrderId).Count();
var tSumPrice = q.Where(a => a.isPay == 1 && a.createTime <tomorrow && a.createTime> today).Sum(a => a.paidPrice);
tSumPrice = tSumPrice == null ? 0 : tSumPrice;
var tBalaceCount = q.Where(a => a.isPay == 1 && a.createTime <tomorrow && a.createTime> today).Sum(a => a.detailsSaleCount);
tBalaceCount = tBalaceCount == null ? 0 : tBalaceCount;
var tSumPrice1 = q.Where(a => a.isPay == 2 && a.createTime <tomorrow && a.createTime> today).Sum(a => a.paidPrice);
tSumPrice1 = tSumPrice1 == null ? 0 : tSumPrice1;
var tBalaceCount1 = q.Where(a => a.isPay == 2 && a.createTime <tomorrow && a.createTime> today).Sum(a => a.detailsSaleCount);
tBalaceCount1 = tBalaceCount1 == null ? 0 : tBalaceCount1;
s += "<ul class='ulCount'>";
s += "<li>今日支付笔数:<span>" + tCount + "</span>.</li>";
s += "<li>今日支付总金额:<span>" + Convert.ToDecimal(tSumPrice).ToString("f2") + "</span>￥</li>";
s += "<li>今日销售总件:<span>" + tBalaceCount + "</span>.</li>";
s += "<li>今日未支付笔数:<span>" + tBalaceCount1 + "</span>.</li>";
s += "<li>今日未支付金额:<span>" + Convert.ToDecimal(tSumPrice1).ToString("f2") + "</span>￥.</li>";
s += "</ul>";
s += "<div class='clearfix'></div>";
s += "<ul class='ulCount'>";
s += "<li>支付笔数:<span>" + Count + "</span>.</li>";
s += "<li>支付总金额:<span>" + Convert.ToDecimal(SumPrice).ToString("f2") + "</span>￥.</li>";
s += "<li>销售总件:<span>" + BalaceCount + "</span>.</li>";
s += "<li>未支付笔数:<span>" + BalaceCount1 + "</span>.</li>";
s += "<li>未支付金额:<span>" + Convert.ToDecimal(SumPrice1).ToString("f2") + "</span>￥.</li>";
s += "</ul>";
//s = "[{\"Count\":" + Count + ",\"SumPrice\":" + SumPrice + ",\"BalaceCount\":" + BalaceCount + ",\"SumPrice1\":" + SumPrice1 + ",\"BalaceCount1\":" + BalaceCount1 + ",\"tCount\":" + tCount + ",\"tSumPrice\":" + tSumPrice + ",\"tBalaceCount\":" + tBalaceCount + ",\"tSumPrice1\":" + tSumPrice1 + ",\"tBalaceCount1\":" + tBalaceCount1 + "}]";
return(s);
}
/// <summary>
/// 拆单，分配订单
/// </summary>
/// <param name="orderId">订单编号</param>
/// <param name="scode">商品货号</param>
/// <returns></returns>
public string getBalance(string orderId, string[] scode)
{
string s = string.Empty;
string splitResult = string.Empty; //分配订单返回的结果
string sqlText = string.Empty;
string cancelOrder = string.Empty; //取消订单返回结果
bll.apiorderbll apiorderBll = new bll.apiorderbll();
DataTable dtTime = apiorderBll.getOrderDetailsMsg(orderId); //获取原始订单时间
for (int f = 0; f < scode.Length; f++) //如果一个订单存在多个商品，根据订单ID和货号进行遍历分配供应商
{
DataTable dt = new DataTable(); //返回当前货号所在订单中的商品购买数量
DataTable dt1 = new DataTable(); //返回当前商品(scode)的供应商，价格，库存3个属性值集合
string detailsOrderId = string.Empty; //子订单ID
string detailsColor = string.Empty; //商品颜色
string detailsImg = string.Empty; //商品图片
string detailsTime = string.Empty; //订单插入时间
string minusBanlaceResult = string.Empty; //销售库存减去本次销售数量结果（成功、失败）
string orderScode = scode[f].ToString(); //当前商品的货号(scode)
int orderScodeNum = 0; //子订单商品购买数量
int totalBalance = 0; //当前商品（scode）的总库存(所有供应商的库存和)
int saleBalanceDef3 = 0; //当前商品(scode)已售数量(销售字段Def3的值)
#region 根据orderId,scode获取订单商品购买数量
//商品购买数量
if (!string.IsNullOrWhiteSpace(orderId) && !string.IsNullOrWhiteSpace(orderScode))
{
dt = apiorderBll.getOrderMsg(orderId, orderScode); //根据订单号和货号返回此scode货号所在订单中的商品数量。
orderScodeNum = helpcommon.ParmPerportys.GetNumParms(dt.Rows[0]["detailsSaleCount"].ToString()); //返回此scode货号所在订单中的商品数量。
detailsOrderId = dt.Rows[0]["detailsOrderId"].ToString(); //子订单ID
detailsColor = dt.Rows[0]["detailsColor"].ToString(); //商品颜色
detailsImg = dt.Rows[0]["detailsImg"].ToString(); //商品图片
detailsTime = dtTime.Rows[0]["detailsTime"].ToString(); //订单插入时间
}
#endregion
#region 当前货号的3个信息值：1.供应商，2.价格，3.库存(因product表中只存在总库存数量，所以应该去productstock表中查询这3个属性值)
Dictionary <string, decimal> dicPrice = new Dictionary <string, decimal>(); //存放库存数量，价格
Dictionary <string, int> dicLevel = new Dictionary <string, int>(); //存放数据源NO，库存数量
Dictionary <string, decimal> dicPrice1 = new Dictionary <string, decimal>(); //存放库存数量，价格
Dictionary <string, int> dicLevel1 = new Dictionary <string, int>(); //存放数据源NO，库存数量
ProductStockBLL ProductStockBll = new ProductStockBLL();
dt1 = ProductStockBll.getScodeBalance(orderScode, false); //返回供应商，价格，库存（当前货号的3个信息值：1.供应商，2.价格，3.库存）
for (int i = 0; i < dt1.Rows.Count; i++)
{
totalBalance += helpcommon.ParmPerportys.GetNumParms(dt1.Rows[i]["balance"].ToString()); //当前商品（scode）的总库存(所有供应商的库存和)
}
var m = dt1.AsEnumerable().ToArray(); //dt1转换为数组
var p = (from c in m orderby c["vencode"].ToString(), c["balance"].ToString(), c["price"].ToString() descending select c).ToList(); //已按供应商，库存，价格排序
saleBalanceDef3 = ProductStockBll.getScodeBalanceSales(orderScode); //此scode已售数量
#endregion
#region 总库存<=已售数量-1
if (totalBalance <= saleBalanceDef3 - 1)//总库存<=已售数量-1（因为在bms接收app订单的时候，product表中的def3销售字段就会加上相应的订单商品购买数量）
{
string cancelMsg = "库存不足,系统自动取消，";
//update
string orderResult = MD5DAL.AppAPIHelper.ChangeOrderStatus(orderId, 3); //给app发送取消消息
helpcommon.appOrderMsg msg = helpcommon.ReSerialize.ReserializeMethod(orderResult);
if (msg != null)
{
if (msg.Code == "0") //code：0表示取消订单成功
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
context.Connection.Open();
context.Transaction = context.Connection.BeginTransaction();
bll.sourceorderbll sourceorderBll = new bll.sourceorderbll();
ProductBll Productbll = new ProductBll();
cancelOrder += sourceorderBll.cancelOrder(orderId, context); //bms取消订单（修改相关数据表状态为12）
minusBanlaceResult = Productbll.minusBanlace(orderScode, orderScodeNum, context); //减掉此次购买商品数量 helpcommon.ParmPerportys.GetNumParms(dt1.Rows[i]["balance"].ToString());
if (cancelOrder.Contains("成功") && minusBanlaceResult.Contains("成功"))
{
context.Transaction.Commit();
}
else
{
context.Transaction.Rollback();
}
sourceorderBll = null;
Productbll = null;
}
else
{
cancelMsg += "(" + msg.Message + ")给app推送取消订单信息失败";
}
}
return(cancelMsg + orderResult); // "库存不足，请取消此订单";
}
#endregion
#region 分配供应商(一个订单多件商品，可能会被拆分到几个供应商。这里指同款商品多个或多款商品多个)
int restNum = orderScodeNum; //分配到几个供应商，依次分配后，还剩下多少需要分配
int state = 0; //
for (int i = 0; i < p.Count; i++)
{
if (state > 0)
{
continue;
}
#region 检测是否存在分配成功却又取消分配的订单（需重新分配）
DataTable dtSendSource = new bll.sourceorderbll().getOrderSendData(orderId, orderScode); //根据订单ID在apisendorder源头表中检测是否存在分配成功却又取消分配的订单（需重新分配）
sqlText += @"update apiorderdetails set detailsEditTime='" + DateTime.Now.ToString("yyyy-MM-dd") + "' where orderId='" + orderId + "' and detailsscode='" + orderScode + "';"; //子订单编辑修改的时间
#endregion
if (helpcommon.ParmPerportys.GetNumParms(p[i]["balance"].ToString()) >= orderScodeNum) //判断哪些数据源中的商品库存量>客户所购商品数量（供应商的库存已倒序）
{
#region 某个供应商存在足够多的库存，直接分配完成
//如果是取消过的订单
//update p[i]["vencode"].ToString()
//比较vencode，取 取消订单的供应商的下一个
//select * from apiSendOrder where orderId = dt.Rows[0]["orderId"].ToString()
if (dtSendSource.Rows.Count == 1) //存在分配成功却又取消分配的订单（需重新分配）【分配成功却又取消分配的订单只处理分配到一个供应商的订单，多个供应商由人工分配】
{
#region 存在分配成功却又取消分配的订单（需重新分配），因为前面已分配过，所以无需新增，修改即可
if (p[i]["vencode"].ToString() == dtSendSource.Rows[0]["sendSource"].ToString())
{
if (i + 1 < p.Count)
{
//根据订单号、货号更改订单数量，编辑时间，供应商
sqlText += @"update apiSendOrder set newSaleCount='" + orderScodeNum + "',editTime='" + DateTime.Now.ToString("yyyy-MM-dd") + "',sendSource = '" + p[i + 1]["vencode"].ToString() + "' where orderId='" + orderId + "' and newscode='" + orderScode + "';";
state++;
}
else
{
return("订单(" + orderId + ")无供应商可分配，请取消订单");
}
}
#endregion
}
else
{
#region 存在分配成功却又取消分配的订单（新增记录）
//否则insert
var sendOrderId = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999).ToString();
sqlText += @"insert into apiSendOrder(orderId,detailsOrderId,newOrderId,newScode,newColor,newSize,newImg,newSaleCount,
newStatus,createTime,editTime,showStatus,sendSource) values('"
+ orderId + "','"
+ detailsOrderId + "','"
+ sendOrderId + "','"
+ orderScode + "','"
+ detailsColor + "','"
+ "','"
+ detailsImg + "',"
+ orderScodeNum + ","
+ 1 + ",'" //新订单状态(订单当前状态：1为待确认，2为确认，3为待发货，4为发货，5交易成功，6通关异常，7，通关成功，11退货，12取消)
+ detailsTime + "','"
+ DateTime.Now.ToString("yyyy-MM-dd") + "',"
+ 0 + ",'" //审核开放(是否开放给供应商查看,0为未开放，1为开放)
+ p[i]["vencode"].ToString() + "');";
state++;
#endregion
}
#endregion
}
else
{
#region 一个供应商库存不足，需几个供应商加在一起
if (dtSendSource.Rows.Count > 1) //存在分配成功却又取消分配的订单（需重新分配）
{
return("人工处理一个订单分配到几个供应商的订单");
}
else
{
sqlText += @"insert into apiSendOrder(orderId,detailsOrderId,newOrderId,newScode,newColor,newSize,newImg,newSaleCount,
newStatus,createTime,editTime,showStatus,sendSource) values('"
+ orderId + "','"
+ detailsOrderId + "','"
+ DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + "','"
+ orderScode + "','"
+ detailsColor + "','"
+ "','"
+ detailsImg + "',"
+ helpcommon.ParmPerportys.GetNumParms(p[i]["balance"].ToString()) + ","
+ 1 + ",'" //新订单状态(订单当前状态：1为待确认，2为确认，3为待发货，4为发货，5交易成功，6通关异常，7，通关成功，11退货，12取消)
+ detailsTime + "','"
+ DateTime.Now.ToString("yyyy-MM-dd") + "',"
+ 0 + ",'"//审核开放(是否开放给供应商查看,0为未开放，1为开放)
+ p[i]["vencode"].ToString() + "');";
if (helpcommon.ParmPerportys.GetNumParms(p[i]["balance"].ToString()) >= restNum) //当分配完成后
{
restNum = restNum - helpcommon.ParmPerportys.GetNumParms(p[i]["balance"].ToString()); //剩余购买数量
state++;
}
}
#endregion
}
}
#endregion
}
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString());
con.Open();
SqlTransaction st = con.BeginTransaction();
SqlCommand command = new SqlCommand();
try
{
//def2 是否已分配(0未分配，1分配)
sqlText += @"update apiorder set def2 = 1 where orderId='" + orderId + "';";
IDataParameter[] ipara = new IDataParameter[] { new SqlParameter("@sqlText", SqlDbType.NVarChar, 4000) };
ipara[0].Value = sqlText;
command.Connection = con;
command.Parameters.AddRange(ipara);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sendSource";
command.Transaction = st;
command.ExecuteNonQuery();
st.Commit();
splitResult = "订单(" + orderId + ")分配成功";
}
catch (Exception ex) { st.Rollback(); splitResult = "分配失败，程序错误" + ex.Message + sqlText; }
return(splitResult);
}
/// <summary>
/// 获取退货率报表信息--价格
/// </summary>
/// <returns></returns>
public DataTable GetSellPriceRateReport(string SellPriceMin, string SellPriceMax)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var q = from c in context.ProductInfo
group c by c.SellPrice into t
select new
{
SellPrice = t.Key,
allcounts = t.Key.Count(),
};
var q1 = from c in context.ProductInfo
where c.Def1 == "4" || c.Def1 == "6"//退货状态
group c by c.SellPrice into t
select new
{
SellPrice = t.Key,
counts = t.Key.Count(),
};
var lq = from c in q
join a in q1 on c.SellPrice equals a.SellPrice
into aa
from aaa in aa.DefaultIfEmpty()
select new
{
SellPrice = c.SellPrice, //退货金额
Rate = Convert.ToDecimal((aaa.counts == null || aaa.counts.ToString() == "") ? 0 : aaa.counts) / Convert.ToDecimal((c.allcounts == null || c.allcounts.ToString() == "")?0:c.allcounts) * 100, //退货率
};
if (SellPriceMin != "")
{
if (SellPriceMax != "")
{
lq = lq.Where(a => Convert.ToDecimal(a.SellPrice) >= Convert.ToDecimal(SellPriceMin) && Convert.ToDecimal(a.SellPrice) <= Convert.ToDecimal(SellPriceMax));
}
else
{
lq = lq.Where(a => Convert.ToDecimal(a.SellPrice) >= Convert.ToDecimal(SellPriceMin));
}
}
else
{
if (SellPriceMax != "")
{
lq = lq.Where(a => Convert.ToDecimal(a.SellPrice) <= Convert.ToDecimal(SellPriceMax));
}
}
// string sql = @"select t.SellPrice,(1.00*counts/allcounts)*100 as rate from(
//select SellPrice,COUNT(SellPrice) as allcounts from productinfo group by SellPrice)
//t
//left join (select SellPrice,COUNT(SellPrice) as counts from productinfo where Def1=4 or Def1=6 group by SellPrice)
//tt on t.SellPrice=tt.SellPrice where 1=1 ";
// if (SellPriceMin != "")
// {
// if (SellPriceMax != "")
// {
// sql += "and t.SellPrice >='" + SellPriceMin + "' and t.SellPrice<='" + SellPriceMax + "'";
// }
// else
// {
// sql += "and t.SellPrice >='" + SellPriceMin + "'";
// }
// }
// else
// {
// if (SellPriceMax != "")
// {
// sql += "and t.SellPrice >='" + SellPriceMin + "' and t.SellPrice<='" + SellPriceMax + "'";
// }
// }
// dt = DbHelperSQL.Query(sql).Tables[0];
dt = LinqToDataTable.LINQToDataTable(lq.OrderByDescending(a => Convert.ToDecimal((a.SellPrice == null || a.SellPrice == "")?"0":a.SellPrice)));
return(dt);
}
/// <summary>
/// 获取店铺订单数据
/// </summary>
/// <returns></returns>
public int getDataCount(string orderId, string scode, string brand, string type, string status, string shopname, string servicecustom, string buynick, string price1, string price2, string pprice1, string pprice2, string ordertime1, string ordertime2, string paytime1, string paytime2, string sendtime1, string sendtime2, string sucesstime1, string sucesstime2)
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
int i = 0;
//int i = (from c in context.OrderDetails where c.OrderId == c.porder.OrderId select c).Count();
var p = (from c in context.OrderDetails
join pd in context.productstock on c.OrderScode equals pd.Scode into g
from mm in g.DefaultIfEmpty()
orderby c.OrderSucessTime descending
select new
{
OrderId = c.OrderId,
OrderChildenId = c.OrderChildenId,
ProductId = c.ProductId,
DetailsName = c.DetailsName,
OrderScode = c.OrderScode,
OrderColor = c.OrderColor,
OrderImg = c.OrderImg,
DetailsSum = c.DetailsSum,
DetailsPrice = c.DetailsPrice,
OrderSendTime = c.OrderSendTime,
OrderSucessTime = c.OrderSucessTime,
ShopName = c.porder.ShopName,
OrderPrice = c.porder.OrderPrice,
OrderTime = c.porder.OrderTime,
OrderPayTime = c.porder.OrderPayTime,
OrderEditTime = c.porder.OrderEditTime,
//OrderSendTime,
//OrderSucessTime,
OrderNick = c.porder.OrderNick,
PayState = c.porder.PayState,
OrderState = c.porder.OrderState,
OrderState1 = c.porder.OrderState1,
CustomServerId = c.porder.CustomServerId,
UserId = c.porder.UserId,
Cat = mm.Cat,
Cat2 = mm.Cat2
});
if (!string.IsNullOrWhiteSpace(orderId))
{
p = p.Where(c => c.OrderId == orderId);
}
if (!string.IsNullOrWhiteSpace(scode))
{
p = p.Where(c => c.OrderScode == scode);
}
if (!string.IsNullOrWhiteSpace(brand))
{
p = p.Where(c => c.Cat == brand);
}
if (!string.IsNullOrWhiteSpace(type))
{
p = p.Where(c => c.Cat2 == type);
}
if (!string.IsNullOrWhiteSpace(status))
{
p = p.Where(c => c.OrderState == int.Parse(status));
}
//if (!string.IsNullOrWhiteSpace(shopname))
//{
// p = p.Where(c => c.OrderId == shopname);
//}
if (!string.IsNullOrWhiteSpace(servicecustom))
{
p = p.Where(c => c.CustomServerId == int.Parse(servicecustom));
}
if (!string.IsNullOrWhiteSpace(buynick))
{
p = p.Where(c => c.OrderNick == buynick);
}
//if (!string.IsNullOrWhiteSpace(price1)&&!string.IsNullOrWhiteSpace(price2)) //成本价
//{
// p = p.Where(c => c.OrderPrice >= DateTime.Parse(price1) && c.OrderTime <= DateTime.Parse(price2));
//}
//else if (!string.IsNullOrWhiteSpace(price1))
//{
// p = p.Where(c => c.OrderTime >= DateTime.Parse(price1));
//}
//else if (!string.IsNullOrWhiteSpace(price2))
//{
// p = p.Where(c => c.OrderTime <= DateTime.Parse(price2));
//}
if (!string.IsNullOrWhiteSpace(pprice1) && !string.IsNullOrWhiteSpace(pprice2)) //价格
{
p = p.Where(c => c.DetailsPrice >= decimal.Parse(pprice1) && c.DetailsPrice <= decimal.Parse(pprice2));
}
else if (!string.IsNullOrWhiteSpace(pprice1))
{
p = p.Where(c => c.DetailsPrice >= decimal.Parse(pprice1));
}
else if (!string.IsNullOrWhiteSpace(price2))
{
p = p.Where(c => c.DetailsPrice <= decimal.Parse(pprice2));
}
if (!string.IsNullOrWhiteSpace(ordertime1) && !string.IsNullOrWhiteSpace(ordertime2))
{
p = p.Where(c => c.OrderTime >= DateTime.Parse(ordertime1) && c.OrderTime <= DateTime.Parse(ordertime2));
}
else if (!string.IsNullOrWhiteSpace(ordertime1))
{
p = p.Where(c => c.OrderTime >= DateTime.Parse(ordertime1));
}
else if (!string.IsNullOrWhiteSpace(ordertime2))
{
p = p.Where(c => c.OrderTime <= DateTime.Parse(ordertime2));
}
if (!string.IsNullOrWhiteSpace(paytime1) && !string.IsNullOrWhiteSpace(paytime2))
{
p = p.Where(c => c.OrderPayTime >= DateTime.Parse(paytime1) && c.OrderPayTime <= DateTime.Parse(paytime2));
}
else if (!string.IsNullOrWhiteSpace(paytime1))
{
p = p.Where(c => c.OrderPayTime >= DateTime.Parse(paytime1));
}
else if (!string.IsNullOrWhiteSpace(paytime2))
{
p = p.Where(c => c.OrderPayTime <= DateTime.Parse(paytime2));
}
if (!string.IsNullOrWhiteSpace(sendtime1) && !string.IsNullOrWhiteSpace(sendtime2))
{
p = p.Where(c => c.OrderSendTime >= DateTime.Parse(sendtime1) && c.OrderSendTime <= DateTime.Parse(sendtime2));
}
else if (!string.IsNullOrWhiteSpace(sendtime1))
{
p = p.Where(c => c.OrderSendTime >= DateTime.Parse(sendtime1));
}
else if (!string.IsNullOrWhiteSpace(sendtime2))
{
p = p.Where(c => c.OrderSendTime <= DateTime.Parse(sendtime2));
}
if (!string.IsNullOrWhiteSpace(sucesstime1) && !string.IsNullOrWhiteSpace(sucesstime2))
{
p = p.Where(c => c.OrderSucessTime >= DateTime.Parse(sucesstime1) && c.OrderSucessTime <= DateTime.Parse(sucesstime2));
}
else if (!string.IsNullOrWhiteSpace(sucesstime1))
{
p = p.Where(c => c.OrderSucessTime >= DateTime.Parse(sucesstime1));
}
else if (!string.IsNullOrWhiteSpace(sucesstime2))
{
p = p.Where(c => c.OrderSucessTime <= DateTime.Parse(sucesstime2));
}
i = p.Count();
return(i);
}
/// <summary>
/// 获取出货报表
/// </summary>
/// <returns></returns>
public DataTable GetShipmentReport(Dictionary <string, string> Dic, int pageIndex, int pageSize, out string counts)
{
DataTable dt = new DataTable();
// = Convert.ToDateTime(c.createTime.ToString().Substring(0, 10))
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
int Minnid = pageSize * (pageIndex - 1);
int Maxnid = pageSize * (pageIndex);
IDataParameter[] ipr = new IDataParameter[] {
new SqlParameter("Mindate", Dic["Mindate"]), //出货时间段
new SqlParameter("Maxdate", Dic["Maxdate"]), //出货时间段
new SqlParameter("sendSource", Dic["sendSource"]), //供应商
new SqlParameter("MinNid", Minnid),
new SqlParameter("MaxNid", Maxnid),
new SqlParameter("sql", ""),
};
dt = Select(ipr, "GetShipmentReport");
IDataParameter[] iprs = new IDataParameter[] {
new SqlParameter("Mindate", Dic["Mindate"]), //出货时间段
new SqlParameter("Maxdate", Dic["Maxdate"]), //出货时间段
new SqlParameter("sendSource", Dic["sendSource"]), //供应商
new SqlParameter("MinNid", "0"),
new SqlParameter("MaxNid", "999999"),
new SqlParameter("sql", ""),
};
counts = Select(iprs, "GetShipmentReport").Rows.Count.ToString();
//var q = from c in context.apiSendOrder
// group c by new { createTime = Convert.ToDateTime(c.createTime.ToString().Substring(0, 10)), c.sendSource } into t
// join a in context.productsource on t.Key.sendSource equals a.SourceCode
// into aa
// from aaa in aa.DefaultIfEmpty()
// select new
// {
// createTime = Convert.ToDateTime(t.Key.createTime.ToString().Substring(0, 10)),
// sendSource = t.Key.sendSource,
// sourceName = aaa.sourceName,
// weifa = t.Sum(a => a.newStatus == 3 ? a.newSaleCount : 0),
// yifa = t.Sum(a => a.newStatus == 4 ? a.newSaleCount : 0),
// };
//if (Dic["Mindate"] != "")
//{
// if (Dic["Maxdate"] != "")
// {
// q = q.Where(a => a.createTime >= Convert.ToDateTime(Dic["Mindate"]) && a.createTime <= Convert.ToDateTime(Dic["Maxdate"]));
// }
// else
// {
// q = q.Where(a => a.createTime >= Convert.ToDateTime(Dic["Mindate"]));
// }
//}
//else
//{
// if (Dic["Maxdate"] != "")
// {
// q = q.Where(a => a.createTime <= Convert.ToDateTime(Dic["Maxdate"]));
// }
//}
//if (Dic["sendSource"] != "")
//{
// q = q.Where(a => a.sendSource == Dic["sendSource"]);
//}
//counts = q.Count().ToString();
//if (pageIndex == 0)
//{
// dt = LinqToDataTable.LINQToDataTable(q.OrderByDescending(a => a.createTime).Take(pageSize));
//}
//else
//{
// dt = LinqToDataTable.LINQToDataTable(q.OrderByDescending(a => a.createTime).Skip((pageIndex - 1) * pageSize).Take(pageSize));
//}
return(dt);
}
/// <summary>
/// 获取某时间段的所有物流订单ID
/// </summary>
/// <returns></returns>
public string[] getExpressOrderId()
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
string[] ss = (from c in context.OrderExpress select c.OrderId).ToArray();
return(ss);
}
