/// <summary>
/// 获取销售状况分析结果列表
/// </summary>
/// <param name="model">检索条件实体</param>
/// <param name="userinfo">session用户信息</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="OrderBy"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetSalesFiguresList(XBase.Model.Office.OperatingModel.SalesFiguresAnalysisModel model, XBase.Common.UserInfoUtil userinfo, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
StringBuilder strSql = new StringBuilder();
//销售订单
strSql.AppendLine("select c.ProdNo,c.ProductName,c.Specification,");
strSql.AppendLine("isnull(d.CodeName,'') as UnitName,");
strSql.AppendLine("e.EmployeeName as DealerName,isnull(f.CustName,'') as CustName,a.seller,");
strSql.AppendLine("convert(decimal(22," + userinfo.SelPoint + "),isnull(sum(a.sProductCount),0)) as SellProductCount,");
strSql.AppendLine("convert(decimal(22," + userinfo.SelPoint + "),isnull(sum(a.sTotalFee*a.newRate),0)) as SellTotal,");
strSql.AppendLine("convert(decimal(22," + userinfo.SelPoint + "),isnull(sum(a.backCount),0)) as SellBackCount,");
strSql.AppendLine("convert(decimal(22," + userinfo.SelPoint + "),isnull(sum(a.backTotal*a.newRate),0)) as SellBackTotal");
strSql.AppendLine("from ");
strSql.AppendLine("(select s.ProductID,isnull(s.TotalFee,0) as sTotalFee ,0 as backTotal,0 as backCount,");
if (userinfo.IsMoreUnit)
{
strSql.AppendLine(" s.UsedUnitID as UnitID,isnull(s.UsedUnitCount,0) as sProductCount,");
}
else
{
strSql.AppendLine(" s.UnitID,isnull(s.ProductCount,0) as sProductCount,");
}
strSql.AppendLine(" s2.Seller,s2.CustID,s2.OrderDate as DealDate,isnull(s2.Rate,0) as newRate");
strSql.AppendLine("from officedba.sellorderdetail s ");
strSql.AppendLine("left join officedba.sellorder s2 on s2.OrderNo=s.OrderNo and s2.CompanyCD=s.CompanyCD");
strSql.AppendLine("where s2.BillStatus='2' and s.CompanyCD=@CompanyCD");
strSql.AppendLine("union all ");
//销售退货
strSql.AppendLine("select s3.ProductID,0 as sTotalFee,isnull(s3.TotalFee,0) as backTotal,");
if (userinfo.IsMoreUnit)
{
strSql.AppendLine(" isnull(s3.UsedUnitCount,0) as backCount,s3.UsedUnitID as UnitID,");
}
else
{
strSql.AppendLine(" isnull(s3.ProductCount,0) as backCount,s3.UnitID,");
}
strSql.AppendLine(" 0 as sProductCount,");
strSql.AppendLine(" s4.Seller,s4.CustID,s4.BackDate as DealDate,isnull(s4.Rate,0) as newRate");
strSql.AppendLine("from officedba.sellbackdetail s3 ");
strSql.AppendLine("left join officedba.sellback s4 on s4.BackNo=s3.BackNo and s4.CompanyCD=s3.CompanyCD");
strSql.AppendLine("where s4.BillStatus='2' and s3.CompanyCD=@CompanyCD");
strSql.AppendLine(") as a ");
strSql.AppendLine("left join officedba.productInfo c on c.ID=a.ProductID ");
strSql.AppendLine("left join officedba.CodeUnitType d on d.ID=a.UnitID");
strSql.AppendLine("left join officedba.EmployeeInfo e on e.ID=a.Seller");
strSql.AppendLine("left join officedba.CustInfo f on f.ID=a.CustID");
strSql.AppendLine(" where 1=1 ");
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//物品编号
if (model.ProductNo != null)
{
strSql.AppendLine(" and c.ProdNo=@ProductNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductNo", model.ProductNo));
}
//物品名称
if (model.ProductName != null)
{
strSql.AppendLine(" and c.ProductName like @ProductName");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", "%" + model.ProductName + "%"));
}
//客户
if (model.CustID != null)
{
strSql.AppendLine(" and a.CustID=@CustID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CustID", model.CustID));
}
//业务员
if (model.DealerID != null)
{
strSql.AppendLine(" and a.Seller=@Seller");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Seller", model.DealerID));
}
//开始时间
if (model.BeginDate != null)
{
strSql.AppendLine(" and a.DealDate>=@BeginDate");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BeginDate", model.BeginDate));
}
//结束时间
if (model.EndDate != null)
{
strSql.AppendLine(" and a.DealDate<dateadd(day,-1,@EndDate)");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));
}
strSql.AppendLine("group by seller,EmployeeName,CustName,c.prodNo,CodeName,ProductName,Specification");
comm.CommandText = strSql.ToString();
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount));
}
/// <summary>
/// 获取销售状况分析结果列表
/// </summary>
/// <param name="model">检索条件实体</param>
/// <param name="userinfo">session用户信息</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="OrderBy"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetSalesFiguresList(XBase.Model.Office.OperatingModel.SalesFiguresAnalysisModel model, XBase.Common.UserInfoUtil userinfo, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
return(SalesFiguresAnalysisDBHelper.GetSalesFiguresList(model, userinfo, pageIndex, pageCount, OrderBy, ref totalCount));
}
