/// <summary>
/// 根据条件获取 销售发货明细列表
/// </summary>
/// <param name="model">检索条件实体</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetSellSendDetailListData(SellSendDetailsListModel model, int pageIndex, int pageCount, string ord, ref int totalCount)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine(" select p.ProdNo,p.ProductName,s2.CustID,c.CustName,");
strSql.AppendLine(" case s2.isOpenbill when 0 then '未开票' when 1 then '已开票' end as IsOpenBillText,");
strSql.AppendLine(" convert(decimal(22," + model.SelPointLen + "),s.TotalFee) as TotalTax,");
strSql.AppendLine(" convert(decimal(22," + model.SelPointLen + "),isnull(s.TaxPrice,0)) as TaxPrice,");
//多计量单位 取实际数量
if (model.IsMoreUnit)
{
strSql.AppendLine(" convert(decimal(22," + model.SelPointLen + "),isnull(s.UsedUnitCount,0)) as ProductCount,");
}
else
{
strSql.AppendLine(" convert(decimal(22," + model.SelPointLen + "),isnull(s.ProductCount,0)) as ProductCount,");
}
strSql.AppendLine(" convert(decimal(12," + model.SelPointLen + "),isnull(s.Discount,100)) as Discount,");
strSql.AppendLine(" convert(varchar(10),s.SendDate,23) as SendDate,s.SendNo,");
strSql.AppendLine(" p.Specification,p.ColorID,c2.TypeName as ColorName, ");
strSql.AppendLine(" case b.InvoiceType when 1 then '增值税发票' when 2 then '普通地税' when 3 then '普通国税' when 4 then '收据' end as InvoiceTypeText,");
strSql.AppendLine(" b.BillingNum,e.EmployeeName as BillExecutorName,convert(varchar(10),b.CreateDate,23) as BillCreateDate ");
strSql.AppendLine(" ,s2.ID as SendID,s2.CurrencyType,s2.Rate,s2.RealTotal as TaxTotalPrice,ct.CurrencyName,s2.BillStatus ");
/*销售发货单是否被引用Start*/
strSql.AppendLine(",isnull(CASE ((SELECT count(1) ");
strSql.AppendLine("FROM officedba.SellBack AS sb WHERE sb.FromType = '1' AND sb.FromBillID = s2.ID) + ");
strSql.AppendLine("(SELECT count(1) FROM officedba.StorageOutSell AS soo ");
strSql.AppendLine("WHERE soo.FromType = '1' AND soo.FromBillID = s2.ID) + ");
strSql.AppendLine("(SELECT count(1) FROM officedba.SellChannelSttl AS scs ");
strSql.AppendLine("WHERE scs.FromType = '1' AND scs.FromBillID = s2.ID) + ");
strSql.AppendLine("(SELECT count(1) FROM officedba.SellChannelSttlDetail AS scsd ");
strSql.AppendLine("WHERE scsd.FromType = '1' AND scsd.FromBillID = s2.ID) + ");
strSql.AppendLine("(SELECT count(1) FROM officedba.SellBackDetail AS sbd WHERE sbd.FromType = '1' AND sbd.FromBillID = s2.ID)) ");
strSql.AppendLine("WHEN 0 THEN '无引用' END, '被引用') AS RefText ");
/*销售发货单是否被引用END*/
strSql.AppendLine(" from officedba.sellsenddetail s ");
strSql.AppendLine(" left join officedba.productInfo p on p.ID=s.ProductID");
strSql.AppendLine(" left join officedba.CodePublicType c2 on c2.ID=p.ColorID ");
strSql.AppendLine(" left join officedba.sellsend s2 on s2.SendNo=s.SendNo and s.CompanyCD=s2.CompanyCD");
strSql.AppendLine(" left join officedba.custInfo c on c.ID=s2.CustID");
strSql.AppendLine(" left join officedba.CurrencyTypeSetting ct on ct.ID=s2.CurrencyType ");
strSql.AppendLine(" left join officedba.Billing b on b.SourceID=s2.ID and b.BillingType=7 ");
strSql.AppendLine(" left join officedba.EmployeeInfo e on e.ID=b.Executor ");
strSql.AppendLine(" where s.CompanyCD=@CompanyCD");
ArrayList arr = new ArrayList();
arr.Add(new SqlParameter("@CompanyCD", model.CompanyCD));
if (model.ProductID != null)
{
strSql.AppendLine(" and s.ProductID=@ProductID");
arr.Add(new SqlParameter("@ProductID", model.ProductID));
}
if (model.CustID != null)
{
strSql.AppendLine(" and s2.CustID=@CustID");
arr.Add(new SqlParameter("@CustID", model.CustID));
}
if (model.BeginDate != null)
{
strSql.AppendLine(" and s.SendDate >=@BeginDate");
arr.Add(new SqlParameter("@BeginDate", model.BeginDate));
}
if (model.EndDate != null)
{
strSql.AppendLine(" and s.SendDate<dateadd(day,1,@EndDate)");
arr.Add(new SqlParameter("@EndDate", model.EndDate));
}
if (model.IsOpenBill != null)
{
strSql.AppendLine(" and (s2.isOpenBill=@isOpenBill");
if (model.IsOpenBill == "0")
{
strSql.AppendLine(" or s2.isOpenBill is null ");
}
strSql.AppendLine(")");
arr.Add(new SqlParameter("@isOpenBill", model.IsOpenBill));
}
return(SqlHelper.CreateSqlByPageExcuteSqlArr(strSql.ToString(), pageIndex, pageCount, ord, arr, ref totalCount));
}
/// <summary>
/// 根据条件获取 销售发货明细列表
/// </summary>
/// <param name="model">检索条件实体</param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetSellSendDetailListData(SellSendDetailsListModel model, int pageIndex, int pageCount, string ord, ref int totalCount)
{
return(XBase.Data.Office.SellManager.SellSendDetailsListDBHelper.GetSellSendDetailListData(model, pageIndex, pageCount, ord, ref totalCount));
}
