/// <summary>
/// 根据产品编号查询销售总数
/// </summary>
/// <param name="productIds">产品编号</param>
/// <returns></returns>
public IEnumerable <CsOrderView.CsOrderTotalByProduct> TotalNumber(string productIds)
{
var strSql = " select a.ProductId,sum(ProductNumber) as Total from CsOrderDetail a " +
" inner join " +
" CsOrder b on a.OrderId = b.OrderId " +
" where a.ProductId IN(" + (productIds.IsNullOrEmpty() ? "0" : productIds) + ")" +
" and Convert(varchar(7),b.OrderDate,23)='" + DateTime.Now.ToString("yyyy-MM") + "' GROUP BY a.ProductId";
return(DbClient.Query <CsOrderView.CsOrderTotalByProduct>(strSql));
}
/// <summary>
/// 根据openId查询该用户的返利信息
/// </summary>
/// <param name="openId"></param>
/// <returns></returns>
public UserRebateView GetUserRebateInfo(string openId)
{
var strSql = new StringBuilder();
strSql.Append("select a.UserId,a.UserName,a.UserBalance,a.TotalWight,SUM(b.RebateMoney) as RebateMoney,");
strSql.Append("SUM(c.DiscountMoney) as DiscountMoney from CsUsers a left join CsRebate b on a.UserId =b.UserId");
strSql.Append("left join CsOrder c on a.UserId =c.UserId");
strSql.Append($"where a.OpenId='{openId}'");
strSql.Append("group by a.UserId,a.UserName,a.UserBalance,a.TotalWight");
return(DbClient.Query <UserRebateView>(strSql.ToString()).FirstOrDefault());
}
public List <CsParts> GetModelPage(CsPartsEnum order, string where, int pageIndex, int pageSize, out int total)
{
var strSql = new StringBuilder();
strSql.Append($"SELECT * FROM ( SELECT TOP ({pageSize})");
strSql.Append($"ROW_NUMBER() OVER ( ORDER BY {order} DESC ) AS ROWNUMBER,* ");
strSql.Append(" FROM CrabShop.dbo.[CsParts] ");
strSql.Append($" WHERE 1 = 1 {where} ");
strSql.Append(" ) A");
strSql.Append($" WHERE ROWNUMBER BETWEEN {(pageIndex - 1) * pageSize + 1} AND {pageIndex * pageSize}; ");
total = DbClient.ExecuteScalar <int>($"SELECT COUNT(1) FROM CrabShop.dbo.[CsParts] WHERE 1 = 1 {where};");
return(DbClient.Query <CsParts>(strSql.ToString()).ToList());
}
/// <summary>
/// 根据openid 得到订单列表
/// </summary>
/// <param name="openId"></param>
/// <returns></returns>
public List <CsOrder> GetModelListByOpenId(string openId, int num, int size, out int total)
{
var strSql = new StringBuilder();
strSql.Append($"SELECT top ({size}) * FROM ( SELECT ");
strSql.Append($"ROW_NUMBER() OVER ( ORDER BY OrderId DESC ) AS ROWNUMBER,b.* ");
strSql.Append(" FROM CsOrder b inner join CsUsers c on b.UserId = c.UserId ");
strSql.Append($" WHERE 1 = 1 and RowStatus=1 and c.OpenId='{openId}' ");
strSql.Append(" ) A");
strSql.Append($" WHERE ROWNUMBER BETWEEN {(num - 1) * size + 1} AND {num * size}; ");
total = DbClient.ExecuteScalar <int>($"SELECT COUNT(1) FROM CsOrder b inner join CsUsers c on b.UserId = c.UserId WHERE 1 = 1 and RowStatus=1 and c.OpenId='{openId}';");
return(DbClient.Query <CsOrder>(strSql.ToString()).ToList());
}
public List <CsParts> GetModelList(string where)
{
var strSql = $"SELECT * FROM CrabShop.dbo.[CsParts] WHERE 1 = 1 {where}";
return(DbClient.Query <CsParts>(strSql).ToList());
}
public CsParts GetModel(int primaryKey)
{
var strSql = "SELECT * FROM CrabShop.dbo.[CsParts] WHERE PartId = @primaryKey";
return(DbClient.Query <CsParts>(strSql, new { primaryKey }).FirstOrDefault());
}
public CsDistrict GetModel(string strWhere)
{
var strSql = "SELECT top 1 * FROM CrabShop.dbo.[CsDistrict] WHERE " + strWhere;
return(DbClient.Query <CsDistrict>(strSql, null).FirstOrDefault());;
}
public CsUsers GetModelByTelPhone(string telPhone)
{
var strSql = $"select top 1 * from CsUsers where UserPhone='{telPhone}' ";
return(DbClient.Query <CsUsers>(strSql).FirstOrDefault());
}
public CsUsers GetModel(string openId)
{
var strSql = "SELECT * FROM CrabShop.dbo.[CsUsers] WHERE OpenId = @openId";
return(DbClient.Query <CsUsers>(strSql, new { openId }).FirstOrDefault());
}
public CsOrderDetail GetModel(int primaryKey)
{
var strSql = "SELECT * FROM CrabShop.dbo.[CsOrderDetail] WHERE DetailId = @primaryKey";
return(DbClient.Query <CsOrderDetail>(strSql, new { primaryKey }).FirstOrDefault());
}