/// <summary>
/// 获取源头订单
/// </summary>
/// <returns>返回table集合</returns>
public DataTable getData(string orderId)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = from c in context.apiSendOrder
join o in context.productsource on c.sendSource equals o.SourceCode into g
from s in g.DefaultIfEmpty()
where c.orderId == orderId
select
new
{
createTime = c.createTime,
def1 = c.def1,
def2 = c.def2,
def3 = c.def3,
def4 = c.def4,
def5 = c.def5,
detailsOrderId = c.detailsOrderId,
editTime = c.editTime,
newColor = c.newColor,
newImg = c.newImg,
newOrderId = c.newOrderId,
newSaleCount = c.newSaleCount,
newScode = c.newScode,
newSize = c.newSize,
newStatus = c.newStatus,
orderId = c.orderId,
showStatus = c.showStatus,
sendSource = s.sourceName
};
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 获取客户端用户信息
/// </summary>
public DataTable GetClientUsersTable(string UserName, string Vencode, int page, int Selpages, out string counts)
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var q = from c in context.ClientLogin
join t in context.productsource
on Convert.ToString(c.sourceId) equals t.SourceCode
into temp
from tt in temp.DefaultIfEmpty()
select new
{
Id = c.Id,
UserName = c.userName,
PassWord = c.userPwd,
sourceName = tt.sourceName,
Vencode = c.sourceId,
Def1 = c.Def1 //邮箱
};
if (UserName != "")
{
q = q.Where(a => a.UserName.Contains(UserName));
}
;
if (Vencode != "")
{
q = q.Where(a => a.Vencode == Convert.ToInt32(Vencode)).OrderByDescending(a => a.Id);
}
;
counts = q.ToList().Count.ToString();
q = q.Skip((Convert.ToInt32(page) - 1) * Convert.ToInt32(Selpages)).Take(Convert.ToInt32(Selpages));
return(LinqToDataTable.LINQToDataTable(q));
}
/// <summary>
/// 查询客户信息
/// </summary>
/// <returns></returns>
public DataTable GetDate(Dictionary <string, string> Dic, int page, int Selpages, out string counts)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = from c in context.custom select c;
if (Dic["CustomerId"] != "")//客户Id
{
q = q.Where(a => a.CustomerId == Dic["CustomerId"]);
}
if (Dic["Shop"] != "")//客户来源
{
q = q.Where(a => a.Shop == Dic["Shop"]);
}
if (Dic["Sex"] != "")//性别
{
q = q.Where(a => a.Sex == Convert.ToInt32(Dic["Sex"]));
}
if (Dic["CustomerLevel"] != "")//客户等级
{
q = q.Where(a => a.CustomerLevel == Dic["CustomerLevel"]);
}
counts = q.Count().ToString(); //返回查询数量
dt = LinqToDataTable.LINQToDataTable(q.Skip((page - 1) * Selpages).Take(Selpages)); //翻页
return(dt);
}
/// <summary>
/// 查询状态
/// </summary>
/// <param name="childorderId">子订单</param>
/// <returns></returns>
public DataTable getOrderReportStatus(string childorderId)
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = from c in context.orderCustomsResult where c.SJOrgOrderChildId == childorderId select c;
DataTable dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 获取所有角色
/// </summary>
/// <returns></returns>
public DataTable getRole()
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
DataTable dt = new DataTable();
var p = from c in context.persona select c;
dt = LinqToDataTable.LINQToDataTable <model.persona>(p);
return(dt);
}
/// <summary>
/// 根据订单编号、货号获取（供应商取消的订单，重新分配）
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public DataTable getOrderSendData(string orderId, string scode)
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = from c in context.apiSendOrder where c.orderId == orderId && c.newScode == scode select c;
DataTable dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 获取所有数据源（供应商）
/// </summary>
/// <returns></returns>
public DataTable getSource()
{
DataTable dt = new DataTable();
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = from c in context.productsource orderby c.SourceLevel descending select c.SourceLevel;
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 查询客户信息
/// </summary>
/// <returns></returns>
public DataTable GetDate(string Id)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = context.custom.Where(a => a.Id == Convert.ToInt32(Id));//通过id查询客户信息
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 显示客户地址信息
/// </summary>
/// <returns></returns>
public DataTable CustomerAddress(string CustomerId)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = context.customAddress.Where(a => a.CustomerId == CustomerId);
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 根据主订单获取子订单，并对其子订单进行查询，返回库存和供应商和价格。(库存查productstock表)
/// </summary>
/// <param name="scode">货号</param>
/// <param name="isParentOrder"></param>
/// <returns></returns>
public DataTable getScodeBalance(string scode, bool isParentOrder)
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = from c in context.productstock where c.Scode == scode select new { vencode = c.Vencode, balance = c.Balance, price = c.Pricee };
//var p = from c in context.product where c.Scode == scode select new { vencode = c.Vencode, balance = c.Balance, price = c.Pricee };
DataTable dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 编辑-根据ID获取信息
/// </summary>
/// <returns></returns>
public DataTable getDataEdit(int id)
{
List <model.users> list = new List <model.users>();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = from c in context.users where c.Id == id select c;
DataTable dt = LinqToDataTable.LINQToDataTable <model.users>(p);
return(dt);
}
/// <summary>
/// 根据季节名称查询季节表
/// </summary>
/// <returns></returns>
public DataTable GetData(string Cat1)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.Season.Where(a => a.Cat1.Contains(Cat1));
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 返回所有API订单
/// </summary>
/// <returns></returns>
public DataTable getOrderMsg(int pageIndex, int pageSize)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = (from c in context.apiOrder select c).Skip((pageIndex - 1) * pageSize).Take(pageSize);
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 根据主订单获取子订单详情
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public DataTable getOrderDetailsMsg(string orderId)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var p = (from c in context.apiOrderDetails where c.orderId == orderId select c).ToList();
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 供应商类别搜索
/// </summary>
public DataTable SearchBrandDDlist(string BrandName)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.brand.Where(a => a.BrandName.Contains(BrandName) || a.BrandAbridge.Contains(BrandName));
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 获取品牌报表
/// </summary>
/// <returns></returns>
public DataTable GetBrandReport(Dictionary <string, string> Dic, int pageIndex, int pageSize, out string counts)
{
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 != ""
group c by new { c.Cat, c.Style, c.Vencode } into t
select new
{
Cat = t.Key.Cat, //品牌缩写
Style = t.Key.Style.Count(), //款数
Balance = t.Sum(a => a.Balance), //库存数
Vencode = t.Key.Vencode, //供应商
};
var q1 = from c in q
group c by new { c.Cat, c.Vencode } into t
join p in context.brand on t.Key.Cat equals p.BrandAbridge
into pp
from ppp in pp.DefaultIfEmpty()
join b in context.productsource on t.Key.Vencode equals b.SourceCode
into bb
from bbb in bb.DefaultIfEmpty()
select new
{
Cat = t.Key.Cat, //品牌缩写
BrandName = ppp.BrandName, //品牌名称
Style = t.Sum(a => a.Style), //款数
Balance = t.Sum(a => a.Balance), //库存数
Vencode = t.Key.Vencode, //供应商
SourceName = bbb.sourceName, //供应商名称
};
if (Dic["Vencode"] != "")
{
q1 = q1.Where(a => a.Vencode == Dic["Vencode"]);
}
if (Dic["Cat"] != "")
{
q1 = q1.Where(a => a.Cat == Dic["Cat"]);
}
counts = q1.Count().ToString(); //返回查询数量
q1 = q1.OrderByDescending(a => a.Style).OrderByDescending(a => a.Balance); //排序
if (pageIndex == 0) //翻页
{
dt = LinqToDataTable.LINQToDataTable(q1.Take(pageSize));
}
else
{
dt = LinqToDataTable.LINQToDataTable(q1.Skip((pageIndex - 1) * pageSize).Take(pageSize));
}
return(dt);
}
/// <summary>
/// 获取类别报表
/// </summary>
/// <returns></returns>
public DataTable GetTypeReport(Dictionary <string, string> Dic, int pageIndex, int pageSize, out string counts)
{
string s = string.Empty;
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var q = from c in context.productstock
where c.Cat2 != null && c.Cat2 != ""
group c by new { c.Cat2, c.Style, c.Vencode } into t
select new
{
Cat2 = t.Key.Cat2,
Style = t.Key.Style.Count(),
Balance = t.Sum(a => a.Balance),
Vencode = t.Key.Vencode,
};
var q1 = from c in q
group c by new { c.Cat2, c.Vencode } into t
join p in context.producttype on t.Key.Cat2 equals p.TypeNo
into pp
from ppp in pp.DefaultIfEmpty()
join b in context.productsource on t.Key.Vencode equals b.SourceCode
into bb
from bbb in bb.DefaultIfEmpty()
select new
{
TypeNo = t.Key.Cat2,
TypeName = ppp.TypeName,
Style = t.Sum(a => a.Style),
Balance = t.Sum(a => a.Balance),
Vencode = t.Key.Vencode,
SourceName = bbb.sourceName,
};
if (Dic["Vencode"] != "")
{
q1 = q1.Where(a => a.Vencode == Dic["Vencode"]);
}
if (Dic["TypeNo"] != "")
{
q1 = q1.Where(a => a.TypeNo == Dic["TypeNo"]);
}
counts = q1.Count().ToString();
q1 = q1.OrderByDescending(a => a.Style).OrderByDescending(a => a.Balance);
if (pageIndex == 0)
{
dt = LinqToDataTable.LINQToDataTable(q1.Take(pageSize));
}
else
{
dt = LinqToDataTable.LINQToDataTable(q1.Skip((pageIndex - 1) * pageSize).Take(pageSize));
}
return(dt);
}
/// <summary>
/// 返回订单信息(查询已拆单的数据表)
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public DataTable getOrderMsg(string orderId, string scode, object o)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = (model.pbxdatasourceDataContext)o;
var p = from c in context.apiOrderDetails where c.orderId == orderId && c.detailsScode == scode select c;
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 获取淘宝品牌下拉表
/// </summary>
public DataTable GetTBbrandlist(string TBBrandName)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = from c in context.TBBrand select c;
if (TBBrandName != "")
{
q = q.Where(a => a.TBBrandName.Contains(TBBrandName));
}
return(LinqToDataTable.LINQToDataTable(q));
}
/// <summary>
/// 获取某时间段店铺订单数据(根据时间获取)
/// </summary>
/// <returns></returns>
public DataTable getData(string date1, string date2)
{
DataTable dt = new DataTable();
DateTime d1 = DateTime.Parse(date1 + " 00:00:00");
DateTime d2 = DateTime.Parse(date2 + " 23:59:59");
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
var p = from c in context.porder orderby c.OrderTime where c.OrderTime >= d1 && c.OrderTime <= d2 select c;
dt = LinqToDataTable.LINQToDataTable(p);
return(dt);
}
/// <summary>
/// 获取Hs列表
/// </summary>货品绑定hs编码 通过hs编码或者名称模糊查询hs编码
public DataTable SearchHSinfo(string HSNumber, string TypeName)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = from c in context.HSInfomation select c;
if (HSNumber != "")
{
q = q.Where(a => a.HSNumber.Contains(HSNumber));
}
if (TypeName != "")
{
q = q.Where(a => a.TypeName.Contains(TypeName));
}
dt = LinqToDataTable.LINQToDataTable(q);
return(dt);
}
/// <summary>
/// 查看客户订单信息
/// </summary>
/// <returns></returns>
public DataTable CheckOrder(string CustomerId)
{
DataTable dt = new DataTable();
try
{
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = from c in context.ProductInfo
join a in context.CustomerInfo on c.OrderId equals a.OrderId
into aa
from aaa in aa.DefaultIfEmpty()
join b in context.product on c.Scode equals b.Scode
into bb
from bbb in bb.DefaultIfEmpty()
join c in context.brand on bbb.Cat equals c.BrandAbridge
into cc
from ccc in cc.DefaultIfEmpty()
join d in context.producttype on bbb.Cat2 equals d.TypeNo
into dd
from ddd in dd.DefaultIfEmpty()
select new
{
CustomerId = aaa.CustomerId, //客户Id
OrderId = c.OrderId, //订单Id
Scode = c.Scode, //货号
Brand = bbb.Cat, //品牌缩写
BrandName = ccc.BrandName, //品牌名
Color = bbb.Clolor, //颜色
TypeNo = bbb.Cat2, //类别编号
TypeName = ddd.TypeName, //类别名称
Size = bbb.Size, //尺寸
Imagefile = bbb.Imagefile, //缩略图
SellPrice = c.SellPrice, //交易价格
Number = c.Number, //交易数量
Def1 = c.Def1, //发货状态
};
q = q.Where(a => a.CustomerId == CustomerId);
dt = LinqToDataTable.LINQToDataTable(q);
}
catch (Exception ex)
{
}
return(dt);
}
/// <summary>
/// 获取季节表
/// </summary>
/// <returns></returns>
public DataTable OnSearch(Dictionary <string, string> dic, int page, int Selpages, out string counts)
{
DataTable dt = new DataTable();
model.pbxdatasourceDataContext context = new pbxdatasourceDataContext();
var q = context.Season.Where(a => a.Cat1.Contains(dic["Cat1"]));
counts = q.Count().ToString();
if (page == 0)
{
dt = LinqToDataTable.LINQToDataTable(q.Take(Selpages));
}
else
{
dt = LinqToDataTable.LINQToDataTable(q.Skip((page - 1) * Selpages).Take(Selpages));
}
return(dt);
}
/// <summary>
/// 根据货号返回库存和供应商(库存查product表)
/// </summary>
/// <param name="scode"></param>
/// <returns></returns>
public Dictionary <string, int> getScodeBalance(string scode)
{
Dictionary <string, int> dic = new Dictionary <string, int>();
pbxdatasourceDataContext context = new pbxdatasourceDataContext();
//var p = from c in context.productstock where c.Scode == scode select new { vencode = c.Vencode, balance = c.Balance };
var p = from c in context.product where c.Scode == scode select new { vencode = c.Vencode, balance = c.Balance };
DataTable dt = LinqToDataTable.LINQToDataTable(p);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string vencode = dt.Rows[i]["vencode"].ToString();
int balance = int.Parse(dt.Rows[i]["balance"].ToString());
dic.Add(vencode, balance);
}
}
return(dic);
}
/// <summary>
/// 获取来货报表（HK数据源)--今日
/// </summary>
/// <returns></returns>
public DataTable GetLaiHuoReportT()
{
string s = string.Empty;
DataTable dt = new DataTable();
DateTime Mindate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
DateTime Maxdate = Convert.ToDateTime(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
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,
};
q1 = q1.Where(a => a.Lastgrnd >= Mindate && a.Lastgrnd < Maxdate);
q1 = q1.OrderByDescending(a => a.Balance).OrderByDescending(a => a.Lastgrnd);
dt = LinqToDataTable.LINQToDataTable(q1);
return(dt);
}
/// <summary>
/// 退货报表
/// </summary>
/// <returns></returns>
public DataTable GetReturnBalanceReport(string MinTime, string MaxTime)
{
model.pbxdatasourceDataContext context = new model.pbxdatasourceDataContext();
DataTable dt = new DataTable();
var q = from c in context.ProductInfo
where c.Def1 == "4"//退货订单
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 = decimal.Parse( t.Sum(c => Convert.ToDecimal(c.SellPrice == null ? "0" : c.SellPrice)).ToString()),//退货金额
SellPrice = t.Sum(c => Convert.ToDecimal((c.SellPrice == null || c.SellPrice == "") ? "0" : c.SellPrice)),
Balance = t.Sum(c => Convert.ToInt32(c.Number)), //退货数量
};
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>
/// 获取拆单订单的主订单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>
/// 退款报表 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>
/// 根据权限字段组成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>
/// <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);
}
