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