protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
//设置行为参数
string orderString = hiddExpOrder.Value.Trim(); //排序
string order = "desc"; //排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "SendDate"; //要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_a"))
{
order = "asc"; //排序：升序
}
int pageCount = int.Parse(hiddExpTotal.Value); //每页显示记录数
int pageIndex = 1; //当前页
int totalCount = 0; //总记录数
string ord = orderBy + " " + order; //排序字段
DataTable dt = new DataTable();
string productID = hiddExcelProductID.Value.Trim().Length == 0 ? null : hiddExcelProductID.Value.Trim();
string custID = hiddExcelCustID.Value.Trim().Length == 0 ? null : hiddExcelCustID.Value.Trim();
string beginDate = hiddBeginDate.Value.Trim().Length == 0 ? null : hiddBeginDate.Value.Trim();
string endDate = hiddEndDate.Value.Trim().Length == 0 ? null : hiddEndDate.Value.Trim();
string isOpenBill = hiddIsOpenbill.Value.Trim().Length == 0 ? null : hiddIsOpenbill.Value.Trim();
XBase.Model.Office.SellManager.SellSendDetailsListModel model = new XBase.Model.Office.SellManager.SellSendDetailsListModel();
model.ProductID = productID;
model.CustID = custID;
model.BeginDate = beginDate;
model.EndDate = endDate;
model.IsOpenBill = isOpenBill;
model.CompanyCD = UserInfo.CompanyCD;
model.SelPointLen = UserInfo.SelPoint;
model.IsMoreUnit = UserInfo.IsMoreUnit;
dt = SellSendDetailsListBus.GetSellSendDetailListData(model, pageIndex, pageCount, ord, ref totalCount);
//导出标题
string headerTitle = "日期|客户名称|发货单编号|物品编号|品名|规格|颜色|数量|开票状态|票据类型|发票号|开票人|开票日期";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "SendDate|CustName|SendNo|ProdNo|ProductName|Specification|ColorName|ProductCount|IsOpenBillText|InvoiceTypeText|BillingNum|BillExecutorName|BillCreateDate";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "销售发货明细列表");
}
protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
//设置行为参数
string orderString = hiddExpOrder.Value.Trim();//排序
string order = "desc";//排序：降序
string orderBy = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "SendDate";//要排序的字段，如果为空，默认为"ID"
if (orderString.EndsWith("_a"))
{
order = "asc";//排序：升序
}
int pageCount = int.Parse(hiddExpTotal.Value);//每页显示记录数
int pageIndex = 1;//当前页
int totalCount = 0;//总记录数
string ord = orderBy + " " + order;//排序字段
DataTable dt = new DataTable();
string productID = hiddExcelProductID.Value.Trim().Length == 0 ? null : hiddExcelProductID.Value.Trim();
string custID = hiddExcelCustID.Value.Trim().Length == 0 ? null : hiddExcelCustID.Value.Trim();
string beginDate = hiddBeginDate.Value.Trim().Length == 0 ? null : hiddBeginDate.Value.Trim();
string endDate = hiddEndDate.Value.Trim().Length == 0 ? null : hiddEndDate.Value.Trim();
string isOpenBill = hiddIsOpenbill.Value.Trim().Length == 0 ? null : hiddIsOpenbill.Value.Trim();
XBase.Model.Office.SellManager.SellSendDetailsListModel model = new XBase.Model.Office.SellManager.SellSendDetailsListModel();
model.ProductID = productID;
model.CustID = custID;
model.BeginDate = beginDate;
model.EndDate = endDate;
model.IsOpenBill = isOpenBill;
model.CompanyCD = UserInfo.CompanyCD;
model.SelPointLen = UserInfo.SelPoint;
model.IsMoreUnit = UserInfo.IsMoreUnit;
dt = SellSendDetailsListBus.GetSellSendDetailListData(model, pageIndex, pageCount, ord, ref totalCount);
//导出标题
string headerTitle = "日期|客户名称|发货单编号|物品编号|品名|规格|颜色|数量|开票状态|票据类型|发票号|开票人|开票日期";
string[] header = headerTitle.Split('|');
//导出标题所对应的列字段名称
string columnFiled = "SendDate|CustName|SendNo|ProdNo|ProductName|Specification|ColorName|ProductCount|IsOpenBillText|InvoiceTypeText|BillingNum|BillExecutorName|BillCreateDate";
string[] field = columnFiled.Split('|');
XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "销售发货明细列表");
}
/// <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);
}