/// <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); }