protected void btnImport_Click(object sender, ImageClickEventArgs e) { StorageProductModel model = new StorageProductModel(); model.CompanyCD = UserInfo.CompanyCD; string ProductNo = string.Empty; string ProductName = string.Empty; string BarCode = string.Empty; model.StorageID = ddlStorage.SelectedValue; XBase.Model.Office.SupplyChain.ProductInfoModel pdtModel = new XBase.Model.Office.SupplyChain.ProductInfoModel(); pdtModel.ProdNo = txtProductNo.Value; pdtModel.ProductName = txtProductName.Value; pdtModel.BarCode = HiddenBarCode.Value.Trim(); pdtModel.Specification = txtSpecification.Value; pdtModel.Manufacturer = txtManufacturer.Value; pdtModel.Material = ddlMaterial.SelectedValue; pdtModel.FromAddr = txtFromAddr.Value; pdtModel.ColorID = sel_ColorID.SelectedValue; pdtModel.TypeID = this.hidTypeID.Value; string StorageCount = txtStorageCount.Value; string StorageCount1 = txtStorageCount1.Value; model.ProductCount = StorageCount; string BatchNo = this.ddlBatchNo.SelectedValue; string EFIndex = hiddenEFIndex.Value .Trim (); string EFDesc = hiddenEFDesc.Value .Trim (); string EFName = hiddenEFIndexName.Value.Trim(); string sidex= "ExtField"+EFIndex; //ProductNo = txtProductNo.Value; //ProductName = txtProductName.Value; // BarCode = HiddenBarCode.Value.Trim(); string orderBy = txtorderBy.Value; if (!string.IsNullOrEmpty(orderBy)) { if (orderBy.Split('_')[1] == "a") { orderBy = orderBy.Split('_')[0] + " asc"; } else { orderBy = orderBy.Split('_')[0] + " desc"; } } //DataTable dt = StorageSearchBus.GetProductStorageTableBycondition(model, ProductNo, ProductName, orderBy, BarCode); DataTable dt = StorageSearchBus.GetProductStorageTableBycondition(model, pdtModel, StorageCount1, EFIndex, EFDesc, orderBy,BatchNo); if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { OutputToExecl.ExportToTableFormat(this, dt, new string[] { "仓库编号", "仓库名称","批次", "所属部门", "物品编号", "物品名称", "规格","颜色", "基本单位", "基本数量", "单位","数量",EFName }, new string[] { "StorageNo", "StorageName", "BatchNo", "DeptName", "ProductNo", "ProductName", "Specification", "ColorName", "UnitID", "ProductCount", "CodeName", "StoreCount", sidex }, "现有库存查询列表"); } else { OutputToExecl.ExportToTableFormat(this, dt, new string[] { "仓库编号", "仓库名称", "批次", "所属部门", "物品编号", "物品名称", "规格","颜色", "基本单位", "基本数量", "单位", "数量" }, new string[] { "StorageNo", "StorageName", "BatchNo", "DeptName", "ProductNo", "ProductName", "Specification", "ColorName", "UnitID", "ProductCount", "CodeName", "StoreCount" }, "现有库存查询列表"); } }
/// <summary> /// 进销存日报表明细 /// </summary> /// <param name="CompanyCD"></param> /// <param name="HappenDate"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable GetBuyingSellingStockingByDayDetail(ProductInfoModel model, string DailyDate, string BatchNo, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { return BuyingSellingStockingDBHelper.GetBuyingSellingStockingByDayDetail(model, DailyDate, BatchNo, pageIndex, pageCount, OrderBy, ref totalCount); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 进销存日报表总计 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="BatchNo"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <returns></returns> public static DataTable GetSumBuyingSellingStockingByDay(ProductInfoModel model, string DailyDate, string BatchNo, string EFIndex, string EFDesc) { try { return BuyingSellingStockingDBHelper.GetSumBuyingSellingStockingByDay(model, DailyDate, BatchNo, EFIndex, EFDesc); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 进销存汇总表 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable GetStorageInAndOutTotalInfo(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, out DataTable dt, ref int totalCount) { try { return MonthDayDBHelper.GetStorageInAndOutTotalInfo(model, DailyDate, EndDate, BatchNo, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, out dt, ref totalCount); } catch (System.Exception ex) { throw ex; } }
//采购用 public static DataTable GetProductInfoTableBycondition(ProductInfoModel model, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { return ProductInfoDBHelper.GetProductInfoTableBycondition(model, pageIndex, pageCount, OrderBy, ref totalCount); } catch (Exception ex) { throw ex; } }
public static DataTable GetProductInfoBatchTableBycondition(ProductInfoModel model, string QueryID, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { DataTable dt = new DataTable(); if (QueryID == "-1") { return ProductInfoDBHelper.GetProductInfoBycondition(model); } else { return ProductInfoDBHelper.GetProductInfoBatchTableBycondition(model, QueryID, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount); } } catch (Exception ex) { return null; throw ex; } }
/// <summary> /// 插入物品档案信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertProductInfo(ProductInfoModel model, out string ID, Hashtable htExtAttr) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; ID = "0"; try { bool succ = false; LogInfoModel logModel = InitLogInfo(model.ProdNo); logModel.Element = ConstUtil.LOG_PROCESS_INSERT; succ = ProductInfoDBHelper.InsertProductInfo(model, out ID, htExtAttr); if (!succ) logModel.Remark = ConstUtil.LOG_PROCESS_FAILED; else logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS; LogDBHelper.InsertLog(logModel); return succ; } catch (Exception ex) { WriteSystemLog(userInfo, ex); return false; } }
/// <summary> /// 进销存汇总表 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable GetAllStorageInAndOutInfo(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, out DataTable dt, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("SELECT CONVERT(varchar(10),a.DailyDate,120) as DailyDate,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.PhurInCount)) as PhurInCount,SUM(Convert(numeric(22,2),a.MakeInCount)) as MakeInCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.DispInCount)) as DispInCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.OtherInCount)) as OtherInCount,SUM(Convert(numeric(22,2),a.SendInCount)) as SendInCount,SUM(Convert(numeric(22,2),a.SubSaleBackInCount)) as SubSaleBackInCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.TakeInCount)) as TakeInCount,SUM(Convert(numeric(22,2),a.InTotal)) as InTotal,SUM(Convert(numeric(22,2),a.SaleFee)) as SaleFee,SUM(Convert(numeric(22,2),a.PhurBackFee)) as PhurBackFee,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.InitInCount)) as InitInCount,SUM(Convert(numeric(22,2),a.InitBatchCount)) as InitBatchCount,SUM(Convert(numeric(22,2),a.SaleBackInCount)) as SaleBackInCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.RedInCount)) as RedInCount,SUM(Convert(numeric(22,2),a.BackInCount)) as BackInCount,"); searchSql.AppendLine(" "); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.SaleOutCount)) as SaleOutCount,SUM(Convert(numeric(22,2),a.TakeOutCount)) as TakeOutCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.DispOutCount)) as DispOutCount,SUM(Convert(numeric(22,2),a.BadCount)) as BadCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.OtherOutCount)) as OtherOutCount,SUM(Convert(numeric(22,2),a.SendOutCount)) as SendOutCount,SUM(Convert(numeric(22,2),a.SubSaleOutCount)) as SubSaleOutCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.OutTotal)) as OutTotal,SUM(Convert(numeric(22,2),a.PhurFee)) as PhurFee,SUM(Convert(numeric(22,2),a.SaleBackFee)) as SaleBackFee,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),a.PhurBackOutCount)) as PhurBackOutCount,SUM(Convert(numeric(22,2),a.RedOutCount)) as RedOutCount,SUM(Convert(numeric(22,2),a.LendCount)) as LendCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),ABS(a.CheckCount))) as CheckCount,SUM(Convert(numeric(22,2),ABS(a.AdjustCount))) as AdjustCount,"); searchSql.AppendLine(" SUM(Convert(numeric(22,2),ABS(a.TodayCount))) as TodayCount "); searchSql.AppendLine("FROM officedba.StorageDaily a "); searchSql.AppendLine("where a.CompanyCD=@CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //开始日期 if (!string.IsNullOrEmpty(DailyDate)) { searchSql.AppendLine(" and a.DailyDate>=@DailyDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DailyDate", DailyDate)); } //结束日期 if (!string.IsNullOrEmpty(EndDate)) { searchSql.AppendLine(" and a.DailyDate<=@EndDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", EndDate)); } searchSql.AppendLine(" GROUP BY CONVERT(varchar(10),a.DailyDate,120)"); //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 dt = GetAllTotal(model, DailyDate, EndDate, BatchNo, EFIndex, EFDesc, false); return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 进销存汇总表明细 /// </summary> /// <param name="CompanyCD"></param> /// <param name="Happendate"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable GetTotalInAndOutDetail(ProductInfoModel model, string StartDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select a.ID,a.BillNo,a.ProductID,b.ProductName,a.StorageID,c.StorageName,a.BillType,"); searchSql.AppendLine(" a.BatchNo,Convert(numeric(22," + userInfo.SelPoint + "),a.HappenCount) as HappenCount,a.HappenDate as OperateDate,Convert(numeric(22," + userInfo.SelPoint + "),a.ProductCount) as ProductCount,a.Creator,d.EmployeeName,"); searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.HappenDate, 23),'') as HappenDate,"); searchSql.AppendLine(" case when a.BillType=1 then '期初库存录入'"); searchSql.AppendLine(" when a.BillType=2 then '期初库存批量导入'"); searchSql.AppendLine(" when a.BillType=3 then '采购入库单'"); searchSql.AppendLine(" when a.BillType=4 then '生产完工入库单'"); searchSql.AppendLine(" when a.BillType=5 then '其他入库单'"); searchSql.AppendLine(" when a.BillType=6 then '红冲入库单'"); searchSql.AppendLine(" when a.BillType=7 then '销售出库单'"); searchSql.AppendLine(" when a.BillType=8 then '其他出库单'"); searchSql.AppendLine(" when a.BillType=9 then '红冲出库单'"); searchSql.AppendLine(" when a.BillType=10 then '借货申请单'"); searchSql.AppendLine(" when a.BillType=11 then '借货返还单'"); searchSql.AppendLine(" when a.BillType=12 then '调拨出库'"); searchSql.AppendLine(" when a.BillType=13 then '调拨入库'"); searchSql.AppendLine(" when a.BillType=14 then '日常调整单'"); searchSql.AppendLine(" when a.BillType=15 then '期末盘点单'"); searchSql.AppendLine(" when a.BillType=16 then '库存报损单'"); searchSql.AppendLine(" when a.BillType=17 then '领料单'"); searchSql.AppendLine(" when a.BillType=18 then '退料单'"); searchSql.AppendLine(" when a.BillType=19 then '配送单'"); searchSql.AppendLine(" when a.BillType=20 then '配送退货单'"); searchSql.AppendLine(" when a.BillType=21 then '门店销售管理'"); searchSql.AppendLine(" when a.BillType=22 then '门店销售退货'"); searchSql.AppendLine(" end as strBillText"); searchSql.AppendLine("from officedba.StorageAccount a"); searchSql.AppendLine("left join officedba.ProductInfo b on a.ProductID=b.ID"); searchSql.AppendLine("left join officedba.StorageInfo c on a.StorageID=c.ID"); searchSql.AppendLine("left join officedba.EmployeeInfo d on a.Creator=d.ID"); searchSql.AppendLine("where a.HappenDate >=@StartDate and a.HappenDate<=@EndDate and a.CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", StartDate)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", EndDate)); //物品编号 if (!string.IsNullOrEmpty(model.ProdNo)) { searchSql.AppendLine(" and b.ProdNo like @ProdNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", "%" + model.ProdNo + "%")); } //物品名称 if (!string.IsNullOrEmpty(model.ProductName)) { searchSql.AppendLine(" and b.ProductName like @ProductName"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", "%" + model.ProductName + "%")); } //物品规格 if (!string.IsNullOrEmpty(model.Specification)) { searchSql.AppendLine(" and b.Specification like @Specification"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", "%" + model.Specification + "%")); } //仓库 if (!string.IsNullOrEmpty(model.StorageID)) { if (int.Parse(model.StorageID) > 0) { searchSql.AppendLine(" and a.StorageID=@StorageID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } } //仓库 if (!string.IsNullOrEmpty(BatchNo)) { searchSql.AppendLine(" and a.BatchNo=@BatchNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and b.ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 进销存日报表明细表 /// </summary> /// <param name="CompanyCD"></param> /// <param name="Happendate"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable GetBuyingSellingStockingByDayDetail(ProductInfoModel model, string HappenDate, string BatchNo, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select a.ID,a.BillNo,a.ProductID,b.ProductName,a.StorageID,c.StorageName,a.BillType,"); searchSql.AppendLine(" a.BatchNo,Convert(numeric(22," + userInfo.SelPoint + "),a.HappenCount) as HappenCount,a.HappenDate as OperateDate,Convert(numeric(22," + userInfo.SelPoint + "),a.ProductCount) as ProductCount,a.Creator,d.EmployeeName,"); searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.HappenDate, 23),'') as HappenDate,"); searchSql.AppendLine(" case when a.BillType=1 then '期初库存录入'"); searchSql.AppendLine(" when a.BillType=2 then '期初库存批量导入'"); searchSql.AppendLine(" when a.BillType=3 then '采购入库单'"); searchSql.AppendLine(" when a.BillType=4 then '生产完工入库单'"); searchSql.AppendLine(" when a.BillType=5 then '其他入库单'"); searchSql.AppendLine(" when a.BillType=6 then '红冲入库单'"); searchSql.AppendLine(" when a.BillType=7 then '销售出库单'"); searchSql.AppendLine(" when a.BillType=8 then '其他出库单'"); searchSql.AppendLine(" when a.BillType=9 then '红冲出库单'"); searchSql.AppendLine(" when a.BillType=10 then '借货申请单'"); searchSql.AppendLine(" when a.BillType=11 then '借货返还单'"); searchSql.AppendLine(" when a.BillType=12 then '调拨出库'"); searchSql.AppendLine(" when a.BillType=13 then '调拨入库'"); searchSql.AppendLine(" when a.BillType=14 then '日常调整单'"); searchSql.AppendLine(" when a.BillType=15 then '期末盘点单'"); searchSql.AppendLine(" when a.BillType=16 then '库存报损单'"); searchSql.AppendLine(" when a.BillType=17 then '领料单'"); searchSql.AppendLine(" when a.BillType=18 then '退料单'"); searchSql.AppendLine(" when a.BillType=19 then '配送单'"); searchSql.AppendLine(" when a.BillType=20 then '配送退货单'"); searchSql.AppendLine(" when a.BillType=21 then '门店销售管理'"); searchSql.AppendLine(" when a.BillType=22 then '门店销售退货'"); searchSql.AppendLine(" end as strBillText"); searchSql.AppendLine("from officedba.StorageAccount a"); searchSql.AppendLine("left join officedba.ProductInfo b on a.ProductID=b.ID"); searchSql.AppendLine("left join officedba.StorageInfo c on a.StorageID=c.ID"); searchSql.AppendLine("left join officedba.EmployeeInfo d on a.Creator=d.ID"); searchSql.AppendLine("where CONVERT(CHAR(10), a.HappenDate, 23)=@Happendate and a.CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Happendate", HappenDate)); //物品ID if (model.ID > 0) { searchSql.AppendLine(" and a.ProductID=@ProductID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", model.ID.ToString())); } //仓库 if (!string.IsNullOrEmpty(BatchNo)) { searchSql.AppendLine(" and a.BatchNo=@BatchNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 进销存汇总 总计 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="BatchNo"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <returns></returns> public static DataTable GetAllTotal(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, bool flag) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("SELECT sum(InTotal) as InTotalCount,sum(OutTotal) as outTotalCount,sum(SaleFee) as SaleFeeCount,"); searchSql.AppendLine(" sum(PhurFee) as PhurFeeCount,sum(PhurBackFee) as PhurBackFeeCount,sum(SaleBackFee) as SaleBackFeeCount"); searchSql.AppendLine("FROM officedba.StorageDaily a left join officedba.ProductInfo b on a.ProductID=b.ID and a.CompanyCD=b.CompanyCD "); searchSql.AppendLine("where a.CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //开始日期 if (!string.IsNullOrEmpty(DailyDate)) { searchSql.AppendLine(" and a.DailyDate>=@DailyDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DailyDate", DailyDate)); } //结束日期 if (!string.IsNullOrEmpty(EndDate)) { searchSql.AppendLine(" and a.DailyDate<=@EndDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", EndDate)); } if (flag) { //物品编号 if (!string.IsNullOrEmpty(model.ProdNo)) { searchSql.AppendLine(" and b.ProdNo=@ProdNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", model.ProdNo)); } //物品名称 if (!string.IsNullOrEmpty(model.ProductName)) { searchSql.AppendLine(" and b.ProductName like '%'+ @ProductName + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", model.ProductName)); } //物品规格 if (!string.IsNullOrEmpty(model.Specification)) { searchSql.AppendLine(" and b.Specification like '%'+ @Specification + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", model.Specification)); } //仓库 if (!string.IsNullOrEmpty(model.StorageID)) { if (int.Parse(model.StorageID) > 0) { searchSql.AppendLine(" and a.StorageID=@StorageID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } } //批次 if (!string.IsNullOrEmpty(BatchNo)) { searchSql.AppendLine(" and a.BatchNo=@BatchNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and b.ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 门店进销存月报表 /// </summary> /// <param name="model">产品信息</param> /// <param name="SumModel">汇总方式</param> /// <param name="SubStoreID">部门</param> /// <param name="dStime">开始时间</param> /// <param name="dEtime">结束时间</param> /// <param name="BatchNo">批次</param> /// <param name="EFIndex">扩展索引</param> /// <param name="EFDesc">扩展条件</param> /// <param name="pageIndex">页数</param> /// <param name="pageCount">每页数</param> /// <param name="OrderBy">排序</param> /// <param name="totalCount">总数</param> /// <returns></returns> public static DataTable GetSubStoreMonthReport(ProductInfoModel model, bool SumModel, string SubStoreID , DateTime dStime, DateTime dEtime, string BatchNo, string EFIndex, string EFDesc , int pageIndex, int pageCount, string OrderBy, ref int totalCount) { return SubStoreMonthReportDBHelper.GetSubStoreMonthReport(model, SumModel, SubStoreID, dStime, dEtime, BatchNo, EFIndex, EFDesc , pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 插入物品档案信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertProductInfo(ProductInfoModel model, out string ID, Hashtable htExtAttr) { //SQL拼写 StringBuilder sql = new StringBuilder(); sql.AppendLine("INSERT INTO officedba.ProductInfo"); sql.AppendLine(" (CompanyCD "); sql.AppendLine(" ,ProdNo "); sql.AppendLine(" ,PYShort "); sql.AppendLine(" ,ProductName "); sql.AppendLine(" ,ShortNam "); sql.AppendLine(" ,BarCode "); sql.AppendLine(" ,TypeID "); sql.AppendLine(" ,BigType "); sql.AppendLine(" ,GradeID "); if (int.Parse(model.Source) > 0) { sql.AppendLine(" ,Source "); } sql.AppendLine(" ,UnitID "); sql.AppendLine(" ,Brand "); sql.AppendLine(" ,Specification "); sql.AppendLine(" ,ColorID "); sql.AppendLine(" ,Size "); sql.AppendLine(" ,StockIs "); if (!string.IsNullOrEmpty(model.ABCType)) { sql.AppendLine(" ,ABCType "); } sql.AppendLine(" ,Remark "); sql.AppendLine(" ,Creator "); sql.AppendLine(" ,CreateDate "); sql.AppendLine(" ,CheckStatus "); sql.AppendLine(" ,CheckUser "); sql.AppendLine(" ,CheckDate "); sql.AppendLine(" ,UsedStatus "); sql.AppendLine(" ,ModifiedDate "); sql.AppendLine(" ,ModifiedUserID "); sql.AppendLine(" ,FromAddr "); sql.AppendLine(" ,DrawingNum "); sql.AppendLine(" ,ImgUrl "); sql.AppendLine(" ,FileNo "); sql.AppendLine(" ,PricePolicy "); sql.AppendLine(" ,Params "); sql.AppendLine(" ,Questions "); sql.AppendLine(" ,ReplaceName "); sql.AppendLine(" ,Description "); sql.AppendLine(" ,MinusIs "); sql.AppendLine(" ,StorageID "); sql.AppendLine(" ,SafeStockNum "); sql.AppendLine(" ,MinStockNum "); sql.AppendLine(" ,MaxStockNum "); sql.AppendLine(" ,CalcPriceWays "); sql.AppendLine(" ,StandardCost "); sql.AppendLine(" ,PlanCost "); sql.AppendLine(" ,StandardSell "); sql.AppendLine(" ,SellMin "); sql.AppendLine(" ,SellMax "); sql.AppendLine(" ,TaxRate "); sql.AppendLine(" ,InTaxRate "); sql.AppendLine(" ,SellTax "); sql.AppendLine(" ,SellPrice "); sql.AppendLine(" ,TransferPrice "); sql.AppendLine(" ,Discount "); sql.AppendLine(" ,StandardBuy "); sql.AppendLine(" ,TaxBuy "); sql.AppendLine(" ,Manufacturer "); sql.AppendLine(" ,Material "); sql.AppendLine(" ,GroupUnitNo "); sql.AppendLine(" ,SaleUnitID "); sql.AppendLine(" ,InUnitID "); sql.AppendLine(" ,StockUnitID "); sql.AppendLine(" ,MakeUnitID "); sql.AppendLine(" ,IsBatchNo "); sql.AppendLine(" ,BuyMax) "); sql.AppendLine(" VALUES "); sql.AppendLine(" (@CompanyCD "); sql.AppendLine(" ,@ProdNo "); sql.AppendLine(" ,@PYShort "); sql.AppendLine(" ,@ProductName "); sql.AppendLine(" ,@ShortNam "); sql.AppendLine(" ,@BarCode "); sql.AppendLine(" ,@TypeID "); sql.AppendLine(" ,@BigType "); sql.AppendLine(" ,@GradeID "); if (int.Parse(model.Source) > 0) { sql.AppendLine(" ,@Source "); } sql.AppendLine(" ,@UnitID "); sql.AppendLine(" ,@Brand "); sql.AppendLine(" ,@Specification "); sql.AppendLine(" ,@ColorID "); sql.AppendLine(" ,@Size "); sql.AppendLine(" ,@StockIs "); if (!string.IsNullOrEmpty(model.ABCType)) { sql.AppendLine(" ,@ABCType "); } sql.AppendLine(" ,@Remark "); sql.AppendLine(" ,@Creator "); sql.AppendLine(" ,@CreateDate "); sql.AppendLine(" ,@CheckStatus "); sql.AppendLine(" ,@CheckUser "); sql.AppendLine(" ,@CheckDate "); sql.AppendLine(" ,@UsedStatus "); sql.AppendLine(" ,@ModifiedDate "); sql.AppendLine(" ,@ModifiedUserID "); sql.AppendLine(" ,@FromAddr "); sql.AppendLine(" ,@DrawingNum "); sql.AppendLine(" ,@ImgUrl "); sql.AppendLine(" ,@FileNo "); sql.AppendLine(" ,@PricePolicy "); sql.AppendLine(" ,@Params "); sql.AppendLine(" ,@Questions "); sql.AppendLine(" ,@ReplaceName "); sql.AppendLine(" ,@Description "); sql.AppendLine(" ,@MinusIs "); sql.AppendLine(" ,@StorageID "); sql.AppendLine(" ,@SafeStockNum "); sql.AppendLine(" ,@MinStockNum "); sql.AppendLine(" ,@MaxStockNum "); sql.AppendLine(" ,@CalcPriceWays "); sql.AppendLine(" ,@StandardCost "); sql.AppendLine(" ,@PlanCost "); sql.AppendLine(" ,@StandardSell "); sql.AppendLine(" ,@SellMin "); sql.AppendLine(" ,@SellMax "); sql.AppendLine(" ,@TaxRate "); sql.AppendLine(" ,@InTaxRate "); sql.AppendLine(" ,@SellTax "); sql.AppendLine(" ,@SellPrice "); sql.AppendLine(" ,@TransferPrice "); sql.AppendLine(" ,@Discount "); sql.AppendLine(" ,@StandardBuy "); sql.AppendLine(" ,@TaxBuy "); sql.AppendLine(" ,@Manufacturer "); sql.AppendLine(" ,@Material "); sql.AppendLine(" ,@GroupUnitNo "); sql.AppendLine(" ,@SaleUnitID "); sql.AppendLine(" ,@InUnitID "); sql.AppendLine(" ,@StockUnitID "); sql.AppendLine(" ,@MakeUnitID "); sql.AppendLine(" ,@IsBatchNo "); sql.AppendLine(" ,@BuyMax) "); sql.AppendLine(" SET @ID= @@IDENTITY "); //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); //设置存储过程名 comm.CommandText = sql.ToString(); //设置保存的参数 SetSaveParameter(comm, model); //添加返回参数 comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int)); ArrayList lstCmd = new ArrayList(); SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); lstCmd.Add(comm); if (htExtAttr != null) lstCmd.Add(cmd); //执行登陆操作 bool isSucc = SqlHelper.ExecuteTransWithArrayList(lstCmd); //设置ID //model.ID = int.Parse(comm.Parameters["@ProdID"].Value); ID = comm.Parameters["@ID"].Value.ToString(); return isSucc; }
/// <summary> /// 物品档案 /// </summary> /// <returns>DataTable</returns> public static DataTable GetProductInfoBatchTableBycondition(ProductInfoModel model, string QueryID, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //定义查询的命令 SqlCommand comm = new SqlCommand(); string sql = ""; sql += "select e.ID,isnull(Convert(numeric(14," + userInfo.SelPoint + "),e.StandardBuy),0) as StandardBuy,e.ProdNo,e.ModifiedDate,e.ProductName,cbt.TypeName as ColorName,"; sql += "nn1.CodeName as SaleUnitName,nn2.CodeName as InUnitName,nn3.CodeName as StockUnitName,nn4.CodeName as MakeUnitName,"; sql += " isnull(e.Source,'')as Source,isnull(e.PYShort,'')as PYShort,isnull(Convert(numeric(14," + userInfo.SelPoint + "),e.InTaxRate),0) as InTaxRate,"; sql += " isnull(Convert(numeric(14," + userInfo.SelPoint + "),e.TaxBuy),0)as TaxBuy,"; sql += " n.CodeName,isnull(f.CodeName,'') as CodeTypeName"; sql += ",e.CompanyCD,isnull(e.Specification,'') as Specification,isnull(Convert(numeric(14," + userInfo.SelPoint + "),e.TaxRate),0)as TaxRate,isnull(e.GroupUnitNo,'')GroupUnitNo,e.SaleUnitID,"; sql += " e.InUnitID,e.StockUnitID,e.MakeUnitID,e.IsBatchNo, "; sql += " isnull(Convert(numeric(14," + userInfo.SelPoint + "),e.SellTax),0) as SellTax, e.MinusIs,isnull(Convert(numeric(14," + userInfo.SelPoint + "),e.StandardCost),0)as StandardCost,"; sql += "isnull(Convert(numeric(12," + userInfo.SelPoint + "),e.Discount),0) as Discount,isnull(e.TypeID,'')as TypeID "; sql += " ,e.UnitID,isnull(Convert(numeric(14," + userInfo.SelPoint + "),e.StandardSell),0) as StandardSell ,m.CurrentStore,m.BatchNo,"; sql += " m.ProductCount from (select a.companycd,a.ProdNo,d.BatchNo,isnull(Convert(numeric(13," + userInfo.SelPoint + "), Sum((isnull(d.ProductCount,0)))),0)as ProductCount,isnull(Convert(numeric(14," + userInfo.SelPoint + "), "; sql += " Sum((isnull(d.ProductCount,0)+isnull(d.InCount,0)+isnull(d.RoadCount,0)-isnull(d.OutCount,0)-isnull(d.OrderCount,0)))),0)as CurrentStore "; sql += " from officedba.ProductInfo as a left join officedba.StorageProduct as d on a.ID=d.productID and d.companycd=@CompanyCD "; sql += " left join officedba.CodeUnitType as b on a.UnitID=b.ID "; if (!string.IsNullOrEmpty(QueryID)) { if (QueryID != "DETAIL") { if (QueryID != "0") { sql += " where d.StorageID=@StorageID1"; sql += " group by a.ProdNo,a.companycd,d.BatchNo) as m left join officedba.ProductInfo as e"; comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID1", QueryID)); } else sql += " group by a.ProdNo,a.companycd,d.BatchNo) as m left join officedba.ProductInfo as e"; } else sql += " group by a.ProdNo,a.companycd,d.BatchNo) as m left join officedba.ProductInfo as e"; } else sql += " where isnull(d.ProductCount,0)>0 group by a.ProdNo,a.companycd,d.BatchNo) as m left join officedba.ProductInfo as e"; sql += " on e.ProdNo=m.ProdNo left join officedba.CodeUnitType as n on e.UnitID=n.ID"; sql += " left join officedba.UnitGroupDetail as n1 on "; sql += " e.SaleUnitID=n1.UnitID and e.GroupUnitNo=n1.GroupUnitNo and e.CompanyCD=n1.CompanyCD"; sql += " left join officedba.CodeUnitType nn1 on nn1.ID=n1.UnitID and nn1.CompanyCD=n1.CompanyCD"; sql += " left join officedba.UnitGroupDetail as n2"; sql += " on e.InUnitID=n2.UnitID and e.GroupUnitNo=n2.GroupUnitNo and e.CompanyCD=n2.CompanyCD"; sql += " left join officedba.CodeUnitType nn2 on nn2.ID=n2.UnitID and nn2.CompanyCD=n2.CompanyCD"; sql += " left join officedba.UnitGroupDetail as n3"; sql += " on e.StockUnitID=n3.UnitID and e.GroupUnitNo=n3.GroupUnitNo and e.CompanyCD=n3.CompanyCD"; sql += " left join officedba.CodeUnitType nn3 on nn3.ID=n3.UnitID and nn3.CompanyCD=n3.CompanyCD"; sql += " left join officedba.UnitGroupDetail as n4"; sql += " on e.MakeUnitID=n4.UnitID and e.GroupUnitNo=n4.GroupUnitNo and e.CompanyCD=n4.CompanyCD"; sql += " left join officedba.CodeUnitType nn4 on nn4.ID=n4.UnitID and nn4.CompanyCD=n4.CompanyCD"; sql += " left join officedba.CodePublicType cbt on e.ColorID=cbt.ID"; sql += " left join officedba.CodeProductType "; sql += " as f on e.typeid=f.id "; if (!string.IsNullOrEmpty(QueryID)) { if (QueryID != "DETAIL") { if (QueryID != "0") { sql += " right join officedba.StorageProduct as sp on sp.ProductID=e.ID "; } } } sql += " where e.CompanyCD=@CompanyCD and m.companycd=@CompanyCD "; sql += " and e.CheckStatus='1'"; if (!string.IsNullOrEmpty(QueryID)) { if (QueryID != "DETAIL") { if (QueryID != "0") { sql += " and sp.StorageID=@StorageID and sp.deptid is null and sp.companycd=@CompanyCD "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", QueryID)); } } } //#endregion ////公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //编号 if (!string.IsNullOrEmpty(model.Specification)) { sql += " and e.Specification LIKE @Specification "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", "%" + model.Specification + "%")); } if (!string.IsNullOrEmpty(model.Manufacturer)) { sql += " and e.Manufacturer LIKE @Manufacturer "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", "%" + model.Manufacturer + "%")); } if (!string.IsNullOrEmpty(model.FromAddr)) { sql += " and e.FromAddr LIKE @FromAddr "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", "%" + model.FromAddr + "%")); } if (model.Material != "0") { sql += " and e.Material =@Material "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", model.Material)); } if (!string.IsNullOrEmpty(model.StartStorage)) { sql += " and m.ProductCount>@StartStorage "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartStorage", model.StartStorage)); } if (!string.IsNullOrEmpty(model.EndStorage)) { sql += " and m.ProductCount<@EndStorage "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndStorage", model.EndStorage)); } if (!string.IsNullOrEmpty(model.ProdNo)) { sql += " and e.ProdNo LIKE @ProdNo "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", "%" + model.ProdNo + "%")); } //名称 if (!string.IsNullOrEmpty(model.ProductName)) { sql += " AND e.ProductName LIKE @ProductName "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", "%" + model.ProductName.Trim() + "%")); } if (!string.IsNullOrEmpty(model.PYShort)) { sql += " AND e.PYShort LIKE @PYShort "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", "%" + model.PYShort + "%")); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { sql += " AND e.ExtField" + EFIndex + " LIKE '%" + EFDesc + "%' "; } if (!string.IsNullOrEmpty(model.ColorID)) { sql += " and e.ColorID =@ColorID "; comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", model.ColorID)); } if (!string.IsNullOrEmpty(model.TypeID)) { string ID = model.TypeID; string allID = ""; System.Text.StringBuilder sb = new System.Text.StringBuilder(); string[] IdS = null; ID = ID.Substring(0, ID.Length); IdS = ID.Split(','); for (int i = 0; i < IdS.Length; i++) { IdS[i] = "'" + IdS[i] + "'"; sb.Append(IdS[i]); } allID = sb.ToString().Replace("''", "','"); sql += " AND e.TypeID in (" + allID + ") "; } //指定命令的SQL文 comm.CommandText = sql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { // 多计量单位控制 _isMoreUnit = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsMoreUnit; //模板列表模块ID this.txtModifiedDate.Text = Convert.ToString(DateTime.Now.ToShortDateString()); this.txtModifiedUserID.Text = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID; hidModuleID.Value = ConstUtil.Menu_SerchProduct; //获取请求参数 string requestParam = Request.QueryString.ToString(); //通过参数个数来判断是否从菜单过来 int firstIndex = requestParam.IndexOf("&"); //从列表过来时 if (firstIndex > 0) { //返回按钮可见 product_btnback.Visible = true; //获取列表的查询条件 string searchCondition = requestParam.Substring(firstIndex); //去除参数 searchCondition = searchCondition.Replace("&ModuleID=" + ConstUtil.Menu_AddProduct, string.Empty); //设置检索条件 hidSearchCondition.Value = searchCondition; //迁移页面 hidFromPage.Value = Request.QueryString["FromPage"]; } else { //返回按钮不可见 product_btnback.Visible = false; } CodingRuleControl1.CodingType = ConstUtil.CODING_RULE_TYPE_ZERO; CodingRuleControl1.ItemTypeID = ConstUtil.CODINGA_BASE_ITEM_PRODUCT; //CodingRuleControl1.TableName = "ProductInfo"; //CodingRuleControl1.ColumnName = "ProdNo"; this.txt_CheckUser.Value = Convert.ToString(((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID); this.txtPrincipal.Value = Convert.ToString(((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID); this.UserPrincipal.Text = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeName; this.txt_CheckUserName.Text = Convert.ToString(((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeName); this.txt_CheckDate.Text = DateTime.Now.ToString("yyyy-MM-dd"); this.txt_CreateDate.Text = Convert.ToString(DateTime.Now.ToShortDateString()); BindTree(); BindCom();//绑定下拉框 if (Request["intOtherCorpInfoID"] != "" && Request["intOtherCorpInfoID"] != null) { if (ProductInfoBus.IsConfirmProduct(Request["intOtherCorpInfoID"])) { this.txt_IsConfirmProduct.Value = "1"; } ProductInfoModel model = new ProductInfoModel(); DataTable dt = ProductInfoBus.GetProductInfoByID(int.Parse(Request["intOtherCorpInfoID"])); if (dt.Rows.Count > 0) { this.txtModifiedUserID.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ModifiedUserID"); this.txtModifiedDate.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ModifiedDate"); this.divNo.InnerHtml = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ProdNo"); txt_PYShort.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "PYShort"); this.txt_Manufacturer.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Manufacturer"); txt_ProductName.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ProductName"); txt_ShortNam.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ShortNam"); txt_BarCode.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "BarCode"); txt_BigType.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "BigType"); sel_GradeID.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "GradeID"); sel_UnitID.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "UnitID"); sel_Brand.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Brand"); sel_ColorID.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ColorID"); txt_Specification.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Specification"); txt_Size.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Size"); sel_Source.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Source"); txt_FromAddr.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "FromAddr"); txt_DrawingNum.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "DrawingNum"); //txt_ImgUrl.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ImgUrl"); txt_FileNo.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "FileNo"); txt_PricePolicy.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "PricePolicy"); txt_Params.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Params"); txt_Questions.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Questions"); txt_ReplaceName.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ReplaceName"); txt_Description.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Description"); if (dt.Rows[0]["IsBatchNo"].ToString() == "1") { RdUseBatch.Checked = true; RdNotUseBatch.Checked = false; } else { RdUseBatch.Checked = false; RdNotUseBatch.Checked = true; } this.txt_CheckUserName.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CheckUserName"); this.txtPrincipal.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Creator"); this.UserPrincipal.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CreatorName"); var photoURL = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ImgUrl"); ; string StockIs = GetSafeData.ValidateDataRow_String(dt.Rows[0], "StockIs"); string MinusIs = GetSafeData.ValidateDataRow_String(dt.Rows[0], "MinusIs"); if (StockIs == "1") { rd_StockIs.Checked = true; rd_notStockIs.Checked = false; } if (StockIs == "0") { rd_notStockIs.Checked = true; rd_StockIs.Checked = false; } if (MinusIs == "1") { this.rd_MinusIs.Checked = true; this.rd_notMinusIs.Checked = false; } if (MinusIs == "0") { this.rd_notMinusIs.Checked = true; this.rd_MinusIs.Checked = false; } //chk_MinusIs.Checked=StockIs=="0"?false:true; //chk_StockIs.Checked = StockIs == "0" ? false: true; this.HdGroupNo.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "GroupUnitNo"); if (!String.IsNullOrEmpty(dt.Rows[0]["GroupUnitNo"].ToString())) { this.txtUnitGroup.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "GroupUnitNo") + "_" + GetSafeData.ValidateDataRow_String(dt.Rows[0], "GroupUnitName"); } if (!string.IsNullOrEmpty(HdGroupNo.Value)) { string GroupUnitNo = HdGroupNo.Value; DataTable dt_GroupUnit = ProductInfoBus.GetUnitGroupList(GroupUnitNo); if (dt_GroupUnit.Rows.Count > 0) { //库存单位 selStorageUnit.DataSource = dt_GroupUnit; selStorageUnit.DataTextField = "CodeName"; selStorageUnit.DataValueField = "UnitID"; selStorageUnit.DataBind(); //采购单位 selPurchseUnit.DataSource = dt_GroupUnit; selPurchseUnit.DataTextField = "CodeName"; selPurchseUnit.DataValueField = "UnitID"; selPurchseUnit.DataBind(); //销售 selSellUnit.DataSource = dt_GroupUnit; selSellUnit.DataTextField = "CodeName"; selSellUnit.DataValueField = "UnitID"; selSellUnit.DataBind(); //生产 selProductUnit.DataSource = dt_GroupUnit; selProductUnit.DataTextField = "CodeName"; selProductUnit.DataValueField = "UnitID"; selProductUnit.DataBind(); } //selSellUnit.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "SaleUnitID"); this.selStorageUnit.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "StockUnitID"); this.selPurchseUnit.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "InUnitID"); this.selSellUnit.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "SaleUnitID"); this.selProductUnit.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "MakeUnitID"); } else { //往各业务单位组里添加默认的单位 selStorageUnit.Items.Insert(0, new ListItem(sel_UnitID.SelectedItem.Text, sel_UnitID.SelectedValue)); selPurchseUnit.Items.Insert(0, new ListItem(sel_UnitID.SelectedItem.Text, sel_UnitID.SelectedValue)); selSellUnit.Items.Insert(0, new ListItem(sel_UnitID.SelectedItem.Text, sel_UnitID.SelectedValue)); selProductUnit.Items.Insert(0, new ListItem(sel_UnitID.SelectedItem.Text, sel_UnitID.SelectedValue)); } if (photoURL == "") { imgPhoto.Src = "../../../Images/Pic/Pic_Nopic.jpg"; //document.getElementById("imgPhoto").src = "../../../Images/Pic/Pic_Nopic.jpg"; } else { //document.getElementById("imgPhoto").src = "../../../Images/Photo/" + photoURL; imgPhoto.Src = "../../../Images/Photo/" + photoURL; hfPagePhotoUrl.Value = photoURL; hfPagePhotoUrl.Value = photoURL; } sel_StorageID.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "StorageID"); sel_Material.SelectedValue = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Material"); txt_SafeStockNum.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "SafeStockNum"); txt_MinStockNum.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "MinStockNum"); txt_MaxStockNum.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "MaxStockNum"); sel_ABCType.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "ABCType"); sel_CalcPriceWays.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CalcPriceWays"); txt_StandardCost.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "StandardCost"); txt_PlanCost.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "PlanCost"); txt_StandardSell.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "StandardSell"); txt_SellMin.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "SellMin"); txt_SellMax.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "SellMax"); txt_TaxRate.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "TaxRate"); txt_InTaxRate.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "InTaxRate"); txt_SellTax.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "SellTax"); txt_SellPrice.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "SellPrice"); txt_TransfrePrice.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "TransferPrice"); txt_Discount.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Discount"); txt_StandardBuy.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "StandardBuy"); txt_TaxBuy.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "TaxBuy"); txt_BuyMax.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "BuyMax"); txt_Remark.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "Remark"); txt_CreateDate.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CreateDate"); sel_CheckStatus.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CheckStatus"); if (sel_CheckStatus.Value == "1") { txt_CheckDate.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CheckDate"); divConfirmor.Attributes.Add("style", "display:block;"); this.txt_CheckUser.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CheckUser"); this.txt_CheckUserName.Text = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CheckUserName"); } sel_UsedStatus.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "UsedStatus"); txt_Code.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "TypeID");//隐藏 string Flag = GetSafeData.ValidateDataRow_String(dt.Rows[0], "TypeFlag"); txt_BigType.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "BigType");//隐藏 txt_TypeID.Value = GetSafeData.ValidateDataRow_String(dt.Rows[0], "CodeName");//隐藏 storage.Attributes.Add("style", "display:block;"); divInputNo.Attributes.Add("style", "display:none;float:left"); divNo.Attributes.Add("style", "display:block;float:left"); DataTable dt_stor = ProductInfoBus.GetStorageCount(int.Parse(Request["intOtherCorpInfoID"])); if (dt_stor.Rows[0]["ProductCount"].ToString().IndexOf('.') > -1) this.txt_Storage.Value = dt_stor.Rows[0]["ProductCount"].ToString(); //txt_TypeID.Disabled = true; switch (Flag) { case "1": this.txt_BigTypeName.Value = "成品"; break; case "2": this.txt_BigTypeName.Value = "原材料"; break; case "3": this.txt_BigTypeName.Value = "固定资产"; break; case "4": this.txt_BigTypeName.Value = "低值易耗"; break; case "5": this.txt_BigTypeName.Value = "包装物"; break; case "6": this.txt_BigTypeName.Value = "服务产品"; break; case "7": this.txt_BigTypeName.Value = "半成品"; break; } //this.txt_CheckUserName.Text = Convert.ToString(((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeName); //this.txt_CheckDate.Text = Convert.ToString(DateTime.Now.ToShortDateString()); } } } }
/// <summary> /// 查询物品信息 /// </summary> /// <param name="Model"></param> /// <returns></returns> /// public static DataTable GetProductInfo(ProductInfoModel Model, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; StringBuilder sql = new StringBuilder(); sql.AppendLine("SELECT a.ID "); sql.AppendLine(" ,a.CompanyCD "); sql.AppendLine(" ,a.ProdNo "); sql.AppendLine(" ,a.PYShort "); sql.AppendLine(" ,a.ProductName "); sql.AppendLine(" ,a.ShortNam "); sql.AppendLine(" ,a.BarCode "); sql.AppendLine(" ,a.TypeID "); sql.AppendLine(" ,(case a.BigType when '1' then '成品' when '2' then '原材料' when '3' then '固定资产' "); sql.AppendLine(" when '4' then '低值易耗' when '5' then '包装物' when '6' then '服务产品' end) BigType "); sql.AppendLine(" ,a.GradeID "); sql.AppendLine(" ,(case a.Source when '0' then '自制' when '1' then '外购' when '2' then '委外' "); sql.AppendLine(" when '3' then '虚拟件' end ) Source "); sql.AppendLine(" ,a.UnitID "); sql.AppendLine(",isnull(ExtField1,'')as ExtField1 "); sql.AppendLine(",isnull(ExtField2,'')as ExtField2 "); sql.AppendLine(",isnull(ExtField3,'')as ExtField3 "); sql.AppendLine(",isnull(ExtField4,'')as ExtField4 "); sql.AppendLine(",isnull(ExtField5,'')as ExtField5 "); sql.AppendLine(",isnull(ExtField6,'')as ExtField6 "); sql.AppendLine(",isnull(ExtField7,'')as ExtField7 "); sql.AppendLine(",isnull(ExtField8,'')as ExtField8 "); sql.AppendLine(",isnull(ExtField9,'')as ExtField9 "); sql.AppendLine(",isnull(ExtField10,'')as ExtField10 "); sql.AppendLine(",isnull(ExtField11,'')as ExtField11 "); sql.AppendLine(",isnull(ExtField12,'')as ExtField12 "); sql.AppendLine(",isnull(ExtField13,'')as ExtField13 "); sql.AppendLine(",isnull(ExtField14,'')as ExtField14 "); sql.AppendLine(",isnull(ExtField15,'')as ExtField15 "); sql.AppendLine(",isnull(ExtField16,'')as ExtField16 "); sql.AppendLine(",isnull(ExtField17,'')as ExtField17 "); sql.AppendLine(",isnull(ExtField18,'')as ExtField18 "); sql.AppendLine(",isnull(ExtField19,'')as ExtField19 "); sql.AppendLine(",isnull(ExtField20,'')as ExtField20 "); sql.AppendLine(",isnull(ExtField21,'')as ExtField21 "); sql.AppendLine(",isnull(ExtField22,'')as ExtField22 "); sql.AppendLine(",isnull(ExtField23,'')as ExtField23 "); sql.AppendLine(",isnull(ExtField24,'')as ExtField24 "); sql.AppendLine(",isnull(ExtField25,'')as ExtField25 "); sql.AppendLine(",isnull(ExtField26,'')as ExtField26 "); sql.AppendLine(",isnull(ExtField27,'')as ExtField27 "); sql.AppendLine(",isnull(ExtField28,'')as ExtField28 "); sql.AppendLine(",isnull(ExtField29,'')as ExtField29 "); sql.AppendLine(",isnull(ExtField30,'')as ExtField30 "); sql.AppendLine(" ,a.Brand "); sql.AppendLine(" ,a.Specification "); sql.AppendLine(" ,a.ColorID "); sql.AppendLine(" ,a.Size "); sql.AppendLine(" ,(case a.StockIs when '1' then '是' when '0' then '否' end) StockIs "); sql.AppendLine(" ,(case a.ABCType when 'A' then 'A类' when 'B' then 'B类' when 'C' then 'C类' end) ABCType"); sql.AppendLine(" ,a.Remark "); sql.AppendLine(" ,a.Creator "); sql.AppendLine(" ,CONVERT(VARCHAR(10),a.CreateDate ,21) AS CreateDate "); sql.AppendLine(" ,(case a.CheckStatus when '0' then '草稿' else '已审' end)CheckStatus "); sql.AppendLine(" ,a.CheckUser,a.CheckStatus as intCheckStatus "); sql.AppendLine(" ,CONVERT(VARCHAR(10),a.CheckDate ,21) AS CheckDate "); sql.AppendLine(" ,(case a.UsedStatus when '0' then '停用' else '启用' end) UsedStatus"); sql.AppendLine(" ,CONVERT(VARCHAR(10),a.ModifiedDate ,21) AS ModifiedDate "); sql.AppendLine(" ,a.ModifiedUserID "); sql.AppendLine(" ,a.FromAddr "); sql.AppendLine(" ,a.DrawingNum "); sql.AppendLine(" ,a.ImgUrl "); sql.AppendLine(" ,a.FileNo "); sql.AppendLine(" ,a.PricePolicy "); sql.AppendLine(" ,a.Params "); sql.AppendLine(" ,a.Questions "); sql.AppendLine(" ,a.ReplaceName "); sql.AppendLine(" ,a.Description "); sql.AppendLine(" ,(case a.MinusIs when '0' then '否' when '1' then '是' end) MinusIs "); sql.AppendLine(" ,a.StorageID "); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.SafeStockNum) as SafeStockNum"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.MinStockNum) as MinStockNum"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.MaxStockNum) as MaxStockNum"); sql.AppendLine(" ,'加权平均法' CalcPriceWays "); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.StandardCost) as StandardCost"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.PlanCost) as PlanCost"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.StandardSell) as StandardSell"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.SellMin) as SellMin"); sql.AppendLine(" ,a.Material "); sql.AppendLine(" ,a.Manufacturer "); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.SellMax) as SellMax"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.TaxRate) as TaxRate"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.InTaxRate) as InTaxRate"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.SellTax) as SellTax"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.SellPrice) as SellPrice"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.TransferPrice) as TransferPrice"); sql.AppendLine(" ,Convert(numeric(12," + userInfo.SelPoint + "),a.Discount) as Discount"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.StandardBuy) as StandardBuy"); sql.AppendLine(" ,a.GroupUnitNo "); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.TaxBuy) as TaxBuy"); sql.AppendLine(" ,Convert(numeric(14," + userInfo.SelPoint + "),a.BuyMax) as BuyMax"); sql.AppendLine(" ,a.SaleUnitID, n.CodeName SaleUnitName "); sql.AppendLine(" ,a.InUnitID,m.CodeName InUnitName "); sql.AppendLine(" ,a.StockUnitID, o.CodeName StockUnitName "); sql.AppendLine(" ,a.MakeUnitID,p.CodeName MakeUnitName "); sql.AppendLine(" ,(case a.IsBatchNo when '1' then '是' when '0' then '否' end) IsBatchNo "); sql.AppendLine(" ,b.CodeName TypeName "); sql.AppendLine(" ,b.TypeFlag "); sql.AppendLine(" ,f.GroupUnitName "); sql.AppendLine(" ,c.EmployeeName "); sql.AppendLine(" ,d.EmployeeName as CheckUserName,g.TypeName GradeName,h.TypeName BrandName "); sql.AppendLine(" ,i.TypeName ColorName,j.CodeName UnitName,k.TypeName MaterialName,l.StorageName StorageName "); sql.AppendLine(" FROM officedba.ProductInfo as a "); sql.AppendLine("left join officedba.EmployeeInfo as c on a.Creator=c.ID "); sql.AppendLine("left join officedba.EmployeeInfo as d on a.CheckUser=d.ID "); sql.AppendLine("left join officedba.UnitGroup as f on a.GroupUnitNo=f.GroupUnitNo and a.CompanyCD=f.CompanyCD "); sql.AppendLine("left join officedba.CodePublicType as g on g.ID = a.GradeID "); sql.AppendLine("left join officedba.CodePublicType as h on h.ID = a.Brand "); sql.AppendLine("left join officedba.CodePublicType as i on i.ID = a.ColorID "); sql.AppendLine("left join officedba.CodeUnitType as j on j.ID = a.UnitID "); sql.AppendLine("left join officedba.CodePublicType as k on k.ID = a.Material "); sql.AppendLine("left join officedba.StorageInfo as l on l.ID = a.StorageID "); sql.AppendLine("left join officedba.CodeUnitType as m on m.ID = a.InUnitID "); sql.AppendLine("left join officedba.CodeUnitType as n on n.ID = a.SaleUnitID "); sql.AppendLine("left join officedba.CodeUnitType as o on o.ID = a.StockUnitID "); sql.AppendLine("left join officedba.CodeUnitType as p on p.ID = a.MakeUnitID "); sql.AppendLine(" left join officedba.CodeProductType as b on a.TypeID=b.ID where a.CompanyCD=@CompanyCD "); #region // StringBuilder searchSql = new StringBuilder(); // searchSql.AppendLine("SELECT a.ID "); // searchSql.AppendLine(" ,a.ProdNo "); // searchSql.AppendLine(" ,a.ProductName "); // searchSql.AppendLine(" ,a.CheckStatus "); // searchSql.AppendLine(" ,isnull(a.TypeID,'') as TypeID "); // searchSql.AppendLine(" ,isnull(a.UnitID,'')as UnitID "); // searchSql.AppendLine(",isnull(ExtField1,'')as ExtField1 "); // searchSql.AppendLine(",isnull(ExtField2,'')as ExtField2 "); // searchSql.AppendLine(",isnull(ExtField3,'')as ExtField3 "); // searchSql.AppendLine(",isnull(ExtField4,'')as ExtField4 "); // searchSql.AppendLine(",isnull(ExtField5,'')as ExtField5 "); // searchSql.AppendLine(",isnull(ExtField6,'')as ExtField6 "); // searchSql.AppendLine(",isnull(ExtField7,'')as ExtField7 "); // searchSql.AppendLine(",isnull(ExtField8,'')as ExtField8 "); // searchSql.AppendLine(",isnull(ExtField9,'')as ExtField9 "); // searchSql.AppendLine(",isnull(ExtField10,'')as ExtField10 "); // searchSql.AppendLine(",isnull(ExtField11,'')as ExtField11 "); // searchSql.AppendLine(",isnull(ExtField12,'')as ExtField12 "); // searchSql.AppendLine(",isnull(ExtField13,'')as ExtField13 "); // searchSql.AppendLine(",isnull(ExtField14,'')as ExtField14 "); // searchSql.AppendLine(",isnull(ExtField15,'')as ExtField15 "); // searchSql.AppendLine(",isnull(ExtField16,'')as ExtField16 "); // searchSql.AppendLine(",isnull(ExtField17,'')as ExtField17 "); // searchSql.AppendLine(",isnull(ExtField18,'')as ExtField18 "); // searchSql.AppendLine(",isnull(ExtField19,'')as ExtField19 "); // searchSql.AppendLine(",isnull(ExtField20,'')as ExtField20 "); // searchSql.AppendLine(",isnull(ExtField21,'')as ExtField21 "); // searchSql.AppendLine(",isnull(ExtField22,'')as ExtField22 "); // searchSql.AppendLine(",isnull(ExtField23,'')as ExtField23 "); // searchSql.AppendLine(",isnull(ExtField24,'')as ExtField24 "); // searchSql.AppendLine(",isnull(ExtField25,'')as ExtField25 "); // searchSql.AppendLine(",isnull(ExtField26,'')as ExtField26 "); // searchSql.AppendLine(",isnull(ExtField27,'')as ExtField27 "); // searchSql.AppendLine(",isnull(ExtField28,'')as ExtField28 "); // searchSql.AppendLine(",isnull(ExtField29,'')as ExtField29 "); // searchSql.AppendLine(",isnull(ExtField30,'')as ExtField30 "); // /* // ,[CompanyCD],[ProdNo],[ProductName], // * [PYShort],[ShortNam] ,[BarCode], // * [TypeID],[UnitID],[CheckStatus],[Specification], // * ,[ExtField1],[ExtField30],[ColorID],[Creator],[CreateDate],[UsedStatus] // * // * // * [BigType],[GradeID],[Source] //,[Brand],[Size],[StockIs] //,[ABCType],[Remark] //,[CheckUser],[CheckDate],[ModifiedDate] //,[ModifiedUserID],[FromAddr],[DrawingNum],[ImgUrl] //,[FileNo],[PricePolicy],[Params],[Questions],[ReplaceName] //,[Description],[MinusIs],[StorageID],[SafeStockNum] //,[MinStockNum],[MaxStockNum],[CalcPriceWays],[StandardCost] //,[PlanCost],[StandardSell],[SellMin],[SellMax] //,[TaxRate],[InTaxRate],[SellTax],[SellPrice],[TransferPrice] //,[Discount],[StandardBuy],[TaxBuy],[BuyMax] //,[Manufacturer],[Material],[GroupUnitNo] //,[SaleUnitID],[InUnitID],[StockUnitID] //,[MakeUnitID],[IsBatchNo] // */ // //searchSql.AppendLine(",a.PYShort,a.ShortNam,a.BarCode,"); // searchSql.AppendLine(" ,isnull(a.Specification,'')as Specification "); // searchSql.AppendLine(" ,isnull(e.TypeName,'')as ColorName "); // searchSql.AppendLine(" ,isnull(a.Creator,'') as Creator "); // searchSql.AppendLine(" ,isnull(b.EmployeeName,'') as EmployeeName "); // searchSql.AppendLine(" ,isnull(Convert(VARCHAR(10),a.CreateDate,21),'')as CreateDate "); // searchSql.AppendLine(" ,isnull(a.UsedStatus,'')as UsedStatus "); // searchSql.AppendLine(" ,isnull(c.CodeName,'') as UnitName "); // searchSql.AppendLine(" ,isnull(d.CodeName,'')as TypeName "); // searchSql.AppendLine(" FROM [officedba].[ProductInfo] as a "); // searchSql.AppendLine("left join officedba.EmployeeInfo as b on a.Creator=b.ID and a.CompanyCD=b.CompanyCD "); // searchSql.AppendLine("left join officedba.CodeUnitType as c on a.UnitID=c.ID and a.CompanyCD=c.CompanyCD "); // searchSql.AppendLine("left join officedba.CodeProductType as d on a.TypeID=d.ID and a.CompanyCD=d.CompanyCD "); // searchSql.AppendLine("left join officedba.CodePublicType as e on a.ColorID=e.ID and a.CompanyCD=e.CompanyCD "); // searchSql.AppendLine(" where a.CompanyCD=@CompanyCD "); //#endregion #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); ////公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", Model.CompanyCD)); //编号 if (!string.IsNullOrEmpty(Model.ProdNo)) { sql.AppendLine(" and a.ProdNo LIKE @ProdNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", "%" + Model.ProdNo + "%")); } //名称 if (!string.IsNullOrEmpty(Model.ProductName)) { sql.AppendLine(" AND a.ProductName LIKE @ProductName "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", "%" + Model.ProductName + "%")); } if (!string.IsNullOrEmpty(Model.BarCode)) { sql.AppendLine(" AND a.BarCode LIKE @BarCode "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", "%" + Model.BarCode + "%")); } if (!string.IsNullOrEmpty(Model.ColorID)) { sql.AppendLine(" AND a.ColorID = @ColorID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", Model.ColorID)); } if (!string.IsNullOrEmpty(Model.Specification)) { sql.AppendLine(" AND a.Specification LIKE @Specification "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", "%" + Model.Specification + "%")); } if (!string.IsNullOrEmpty(Model.PYShort)) { sql.AppendLine(" AND a.PYShort LIKE @PYShort "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", "%" + Model.PYShort + "%")); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { sql.AppendLine(" AND a.ExtField" + EFIndex + " LIKE '%" + EFDesc + "%' "); } if (!string.IsNullOrEmpty(Model.TypeID)) { string ID = Model.TypeID; string allID = ""; System.Text.StringBuilder sb = new System.Text.StringBuilder(); string[] IdS = null; ID = ID.Substring(0, ID.Length); IdS = ID.Split(','); for (int i = 0; i < IdS.Length; i++) { IdS[i] = "'" + IdS[i] + "'"; sb.Append(IdS[i]); } allID = sb.ToString().Replace("''", "','"); sql.AppendLine(" AND a.TypeID in (" + allID + ") "); } if (!string.IsNullOrEmpty(Model.UsedStatus)) { sql.AppendLine(" AND a.UsedStatus = @UsedStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", Model.UsedStatus)); } if (!string.IsNullOrEmpty(Model.CheckStatus)) { sql.AppendLine(" AND a.CheckStatus =@CheckStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckStatus", Model.CheckStatus)); } //指定命令的SQL文 comm.CommandText = sql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 进销存日报表总计 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="BatchNo"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <returns></returns> public static DataTable GetAllTotal(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, bool flag) { try { return StorageDBHelper.GetAllTotal(model, DailyDate, EndDate, BatchNo, EFIndex, EFDesc, flag); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 采购列表物品查询 /// </summary> /// <param name="model"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable PurchaseSearchProduct(ProductInfoModel model, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { //例如:conditions=select distinct isnull(a.ProductID,b.ProductID) as ProductID from officedba.SubSellBackDetail a full join officedba.SubSellOrderDetail b on a.ProductID=b.ProductID StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select distinct e.ProdNo,e.ProductName,isnull(e.PYShort,'')as PYShort,isnull(Convert(numeric(10,2),e.InTaxRate),0) as InTaxRate,isnull(Convert(numeric(10,2),e.TaxBuy),0)as TaxBuy,"); searchSql.AppendLine(" e.ID,n.CodeName,isnull(f.CodeName,'') as CodeTypeName"); searchSql.AppendLine(",e.CompanyCD,isnull(e.Specification,'') as Specification,isnull(Convert(numeric(10,2),e.TaxRate),0)as TaxRate,"); searchSql.AppendLine(" isnull(Convert(numeric(10,2),e.SellTax),0) as SellTax, e.MinusIs,isnull(Convert(numeric(10,2),e.StandardBuy),0)as StandardBuy, isnull(Convert(numeric(6,2),e.Discount),0) as Discount,isnull(e.TypeID,'')as TypeID "); searchSql.AppendLine(" ,e.UnitID,isnull(Convert(numeric(10,2),e.StandardSell),0) as StandardSell ,m.CurrentStore from (select a.ProdNo,isnull(Convert(numeric(10,2),Sum((isnull(d.ProductCount,0)+isnull(d.InCount,0)+isnull(d.RoadCount,0)-isnull(d.OutCount,0)-isnull(d.OrderCount,0)))),0)as CurrentStore"); searchSql.AppendLine(" from officedba.ProductInfo as a left join officedba.StorageProduct as d on a.ID=d.productID "); searchSql.AppendLine(" left join officedba.CodeUnitType as b on a.UnitID=b.ID group by a.ProdNo) as m left join officedba.ProductInfo as e"); searchSql.AppendLine(" on e.ProdNo=m.ProdNo left join officedba.CodeUnitType as n on e.UnitID=n.ID left join officedba.CodeProductType "); searchSql.AppendLine(" as f on e.typeid=f.id "); searchSql.AppendLine(" where e.CompanyCD=@CompanyCD and e.CheckStatus='1'"); //#endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); ////公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //编号 if (!string.IsNullOrEmpty(model.ProdNo)) { searchSql.AppendLine(" and e.ProdNo LIKE @ProdNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", "%" + model.ProdNo + "%")); } //名称 if (!string.IsNullOrEmpty(model.ProductName)) { searchSql.AppendLine(" AND e.ProductName LIKE @ProductName "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", "%" + model.ProductName + "%")); } if (!string.IsNullOrEmpty(model.PYShort)) { searchSql.AppendLine(" AND e.PYShort LIKE @PYShort "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", "%" + model.PYShort + "%")); } if (!string.IsNullOrEmpty(model.TypeID)) { string ID = model.TypeID; string allID = ""; System.Text.StringBuilder sb = new System.Text.StringBuilder(); string[] IdS = null; ID = ID.Substring(0, ID.Length); IdS = ID.Split(','); for (int i = 0; i < IdS.Length; i++) { IdS[i] = "'" + IdS[i] + "'"; sb.Append(IdS[i]); } allID = sb.ToString().Replace("''", "','"); searchSql.AppendLine(" AND e.TypeID in (" + allID + ") "); } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
protected void btnImport_Click(object sender, ImageClickEventArgs e) { ProductInfoModel model = new ProductInfoModel(); model.ProdNo = txt_ProdNo.Value; model.ProductName = txt_ProductName.Text; model.TypeID = txt_ID.Value; model.UsedStatus = UsedStatus.Value; model.PYShort = txt_PYShort.Value; model.Specification = txt_Specification.Value; model.BarCode = this.HiddenBarCode.Value; model.CheckStatus = CheckStatus.Value; model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD; string EFIndex = HdselEFIndex.Value; string EFDesc = HdtxtEFDesc.Value; //string ProductDiyAttr2= int totalCount = 0; DataTable dt = ProductInfoBus.GetProductInfo(model, EFIndex, EFDesc, 1, 1000000, "ID desc", ref totalCount); //导出标题 string headerTitle = "物品编号|物品名称|拼音缩写|名称简称|条码|物品分类|基本单位|所属大类|"+ "计量单位组|规格型号|采购计量单位|颜色|销售计量单位|品牌|库存计量单位|档次级别|"+ "生产计量单位|尺寸|是否启用批次|ABC分类|成本核算计价方法|标准成本(元)|去税售价(元)|" + "销项税率(%)|含税售价(元)|零售价(元)|销售折扣率(%)|调拨单价(元)|去税进价(元)| 进项税率(%)|" + "含税进价(元)|是否计入库存|是否允许负库存|主放仓库|安全库存量|最低库存量|最高库存量|" + "物品来源分类|产地|图号|启用状态|批准文号|价格策略|技术参数|常见问题|替代品名称|" + "物品描述信息|厂家|材质|建档时间|建档人|审核状态|最后更新日期|" + "最后更新用户"; //string headerTitle = "建档日期|启用状态"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "ProdNo|ProductName|PYShort|ShortNam|BarCode|TypeName|UnitName|BigType|"+ "GroupUnitName|Specification|InUnitName|ColorName|SaleUnitName|BrandName|StockUnitName|GradeName|"+ "MakeUnitName|Size|IsBatchNo|ABCType|CalcPriceWays|StandardCost|StandardSell|" + "TaxRate|SellTax|SellPrice|Discount|TransferPrice|TaxBuy|InTaxRate|" + "StandardBuy|StockIs|MinusIs|StorageName|SafeStockNum|MinStockNum|MaxStockNum|" + "Source|FromAddr|DrawingNum|UsedStatus|FileNo|PricePolicy|Params|Questions|ReplaceName|" + "Description|Manufacturer|MaterialName|CreateDate|EmployeeName|CheckStatus|ModifiedDate|" + "ModifiedUserID"; //string columnFiled = "CreateDate|strUsedStatus"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dt, header, field, "物品档案列表"); }
protected void btnImport_Click(object sender, ImageClickEventArgs e) { StorageProductModel model = new StorageProductModel(); model.CompanyCD = UserInfo.CompanyCD; string ProductNo = string.Empty; string ProductName = string.Empty; string BarCode = string.Empty; model.StorageID = ddlStorage.SelectedValue; XBase.Model.Office.SupplyChain.ProductInfoModel pdtModel = new XBase.Model.Office.SupplyChain.ProductInfoModel(); pdtModel.ProdNo = txtProductNo.Value; pdtModel.ProductName = txtProductName.Value; pdtModel.BarCode = HiddenBarCode.Value.Trim(); pdtModel.Specification = txtSpecification.Value; pdtModel.Manufacturer = txtManufacturer.Value; pdtModel.Material = ddlMaterial.SelectedValue; pdtModel.FromAddr = txtFromAddr.Value; pdtModel.ColorID = sel_ColorID.SelectedValue; pdtModel.TypeID = this.hidTypeID.Value; string StorageCount = txtStorageCount.Value; string StorageCount1 = txtStorageCount1.Value; model.ProductCount = StorageCount; string BatchNo = this.ddlBatchNo.SelectedValue; string EFIndex = hiddenEFIndex.Value.Trim(); string EFDesc = hiddenEFDesc.Value.Trim(); string EFName = hiddenEFIndexName.Value.Trim(); string sidex = "ExtField" + EFIndex; //ProductNo = txtProductNo.Value; //ProductName = txtProductName.Value; // BarCode = HiddenBarCode.Value.Trim(); string orderBy = txtorderBy.Value; if (!string.IsNullOrEmpty(orderBy)) { if (orderBy.Split('_')[1] == "a") { orderBy = orderBy.Split('_')[0] + " asc"; } else { orderBy = orderBy.Split('_')[0] + " desc"; } } //DataTable dt = StorageSearchBus.GetProductStorageTableBycondition(model, ProductNo, ProductName, orderBy, BarCode); DataTable dt = StorageSearchBus.GetProductStorageTableBycondition(model, pdtModel, StorageCount1, EFIndex, EFDesc, orderBy, BatchNo); if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { OutputToExecl.ExportToTableFormat(this, dt, new string[] { "仓库编号", "仓库名称", "批次", "所属部门", "物品编号", "物品名称", "规格", "颜色", "基本单位", "基本数量", "单位", "数量", EFName }, new string[] { "StorageNo", "StorageName", "BatchNo", "DeptName", "ProductNo", "ProductName", "Specification", "ColorName", "UnitID", "ProductCount", "CodeName", "StoreCount", sidex }, "现有库存查询列表"); } else { OutputToExecl.ExportToTableFormat(this, dt, new string[] { "仓库编号", "仓库名称", "批次", "所属部门", "物品编号", "物品名称", "规格", "颜色", "基本单位", "基本数量", "单位", "数量" }, new string[] { "StorageNo", "StorageName", "BatchNo", "DeptName", "ProductNo", "ProductName", "Specification", "ColorName", "UnitID", "ProductCount", "CodeName", "StoreCount" }, "现有库存查询列表"); } }
/// <summary> /// 门店进销存月报表 /// </summary> /// <param name="model">产品信息</param> /// <param name="SumModel">汇总方式</param> /// <param name="SubStoreID">部门</param> /// <param name="dStime">开始时间</param> /// <param name="dEtime">结束时间</param> /// <param name="BatchNo">批次</param> /// <param name="EFIndex">扩展索引</param> /// <param name="EFDesc">扩展条件</param> /// <param name="pageIndex">页数</param> /// <param name="pageCount">每页数</param> /// <param name="OrderBy">排序</param> /// <param name="totalCount">总数</param> /// <returns></returns> public static DataTable GetSubStoreMonthReport(ProductInfoModel model, bool SumModel, string SubStoreID , DateTime dStime, DateTime dEtime, string BatchNo, string EFIndex, string EFDesc , int pageIndex, int pageCount, string OrderBy, ref int totalCount) { //定义查询的命令 SqlCommand comm = GetSubStoreCommand(model, SumModel, SubStoreID, dStime, dEtime, BatchNo, EFIndex, EFDesc, false); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 门店进销存月报表(导出使用) /// </summary> /// <param name="model">产品信息</param> /// <param name="SumModel">汇总方式</param> /// <param name="SubStoreID">部门</param> /// <param name="dStime">开始时间</param> /// <param name="dEtime">结束时间</param> /// <param name="BatchNo">批次</param> /// <param name="EFIndex">扩展索引</param> /// <param name="EFDesc">扩展条件</param> /// <returns></returns> public static DataTable GetSubReportToExel(ProductInfoModel model, bool SumModel, string SubStoreID , DateTime dStime, DateTime dEtime, string BatchNo, string EFIndex, string EFDesc) { return SubStoreMonthReportDBHelper.GetSubReportToExel(model, SumModel , SubStoreID, dStime, dEtime, BatchNo, EFIndex, EFDesc); }
/// <summary> /// 门店进销存月报表(导出使用) /// </summary> /// <param name="model">产品信息</param> /// <param name="SumModel">汇总方式</param> /// <param name="SubStoreID">部门</param> /// <param name="dStime">开始时间</param> /// <param name="dEtime">结束时间</param> /// <param name="BatchNo">批次</param> /// <param name="EFIndex">扩展索引</param> /// <param name="EFDesc">扩展条件</param> /// <returns></returns> public static DataTable GetSubReportToExel(ProductInfoModel model, bool SumModel, string SubStoreID , DateTime dStime, DateTime dEtime, string BatchNo, string EFIndex, string EFDesc) { //定义查询的命令 SqlCommand comm = GetSubStoreCommand(model, SumModel, SubStoreID, dStime, dEtime, BatchNo, EFIndex, EFDesc, false); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 进销存日报表总计 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="BatchNo"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <returns></returns> public static DataTable GetSumBuyingSellingStockingByDay(ProductInfoModel model, string DailyDate, string BatchNo, string EFIndex, string EFDesc) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("SELECT Convert(numeric(22," + userInfo.SelPoint + "),sum(InTotal)) as InTotalCount,Convert(numeric(22," + userInfo.SelPoint + "),sum(OutTotal)) as OutTotalCount,Convert(numeric(22," + userInfo.SelPoint + "),sum(SaleFee)) as SaleFeeCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),sum(PhurFee)) as PhurFeeCount,Convert(numeric(22," + userInfo.SelPoint + "),sum(PhurBackFee)) as PhurBackFeeCount,Convert(numeric(22," + userInfo.SelPoint + "),sum(SaleBackFee)) as SaleBackFeeCount "); searchSql.AppendLine("FROM officedba.StorageDaily a"); searchSql.AppendLine("left join officedba.ProductInfo b on a.ProductID=b.ID"); searchSql.AppendLine("where a.CompanyCD=@CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //单据编号 if (!string.IsNullOrEmpty(DailyDate)) { searchSql.AppendLine(" and CONVERT(CHAR(10), a.DailyDate, 23)=@DailyDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DailyDate", DailyDate)); } //物品编号 if (!string.IsNullOrEmpty(model.ProdNo)) { searchSql.AppendLine(" and b.ProdNo like @ProdNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", "%" + model.ProdNo + "%")); } //物品名称 if (!string.IsNullOrEmpty(model.ProductName)) { searchSql.AppendLine(" and b.ProductName like @ProductName"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", "%" + model.ProductName + "%")); } //物品规格 if (!string.IsNullOrEmpty(model.Specification)) { searchSql.AppendLine(" and b.Specification like @Specification"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", "%" + model.Specification + "%")); } //仓库 if (!string.IsNullOrEmpty(model.StorageID)) { if (int.Parse(model.StorageID) > 0) { searchSql.AppendLine(" and a.StorageID=@StorageID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } } //仓库 if (!string.IsNullOrEmpty(BatchNo)) { searchSql.AppendLine(" and a.BatchNo=@BatchNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } //条码 if (!string.IsNullOrEmpty(model.BarCode)) { searchSql.AppendLine(" and b.BarCode=@BarCode"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", model.BarCode)); } //产地 if (!string.IsNullOrEmpty(model.FromAddr)) { searchSql.AppendLine(" and b.FromAddr like @FromAddr"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", "%" + model.FromAddr + "%")); } //厂家 if (!string.IsNullOrEmpty(model.Manufacturer)) { searchSql.AppendLine(" and b.Manufacturer like @Manufacturer"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", "%" + model.Manufacturer + "%")); } //尺寸 if (!string.IsNullOrEmpty(model.Size)) { searchSql.AppendLine(" and b.Size=@Size"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Size", model.Size)); } //材质 if (!string.IsNullOrEmpty(model.Material)) { searchSql.AppendLine(" and b.Material=@Material"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", model.Material)); } //颜色 if (!string.IsNullOrEmpty(model.ColorID)) { searchSql.AppendLine(" and b.ColorID=@ColorID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", model.ColorID)); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and b.ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 获得门店进销存月报表查询语句 /// </summary> /// <param name="model">产品信息</param> /// <param name="SumModel">汇总方式</param> /// <param name="SubStoreID">部门</param> /// <param name="dStime">开始时间</param> /// <param name="dEtime">结束时间</param> /// <param name="BatchNo">批次</param> /// <param name="EFIndex">扩展索引</param> /// <param name="EFDesc">扩展条件</param> /// <param name="bTotal">是否是求总量</param> /// <returns></returns> private static SqlCommand GetSubStoreCommand(ProductInfoModel model, bool SumModel, string SubStoreID , DateTime dStime, DateTime dEtime, string BatchNo, string EFIndex, string EFDesc, bool bTotal) { //查询SQL拼写 StringBuilder sb = new StringBuilder(); StringBuilder sbLast = new StringBuilder(); //定义查询的命令 SqlCommand comm = new SqlCommand(); int id = 0; sb.AppendLine(@"SELECT SUM(ISNULL(ssd.InTotal,0)) AS InTotal ,SUM(ISNULL(ssd.OutTotal,0)) AS OutTotal ,SUM(ISNULL(ssd.SaleFee,0)) AS SaleFee ,SUM(ISNULL(ssd.SaleBackFee,0)) AS SaleBackFee "); if (!bTotal) {// 不是求总量模式 sb.AppendLine(@" ,di.DeptName ,SUBSTRING(CONVERT(VARCHAR ,ssd.DailyDate ,120) ,0 ,11) AS DailyDate ,ISNULL(({0}),0) AS LastTimeCount ,SUM(ISNULL(ssd.TodayCount,0)) AS TodayCount ,SUM(ISNULL(ssd.InitInCount,0)) AS InitInCount ,SUM(ISNULL(ssd.InitBatchCount,0)) AS InitBatchCount ,SUM(ISNULL(ssd.SendInCount,0)) AS SendInCount ,SUM(ISNULL(ssd.SubSaleBackInCount,0)) AS SubSaleBackInCount ,SUM(ISNULL(ssd.DispInCont,0)) AS DispInCont ,SUM(ISNULL(ssd.SubSaleOutCount,0)) AS SubSaleOutCount ,SUM(ISNULL(ssd.SendOutCount,0)) AS SendOutCount ,SUM(ISNULL(ssd.DispOutCount,0)) AS DispOutCount"); } if (!SumModel && !bTotal) {// 单品模式 sb.AppendLine(",pi1.ProdNo, pi1.ProductName,pi1.Specification,ssd.BatchNo"); } // 上日结算 sbLast.AppendLine(@"SELECT TOP(1) SUM(ISNULL(ssd1.TodayCount,0)) AS LasttimeCount FROM officedba.SubStorageDaily ssd1 LEFT JOIN officedba.ProductInfo pi1 ON ssd1.ProductID=pi1.ID WHERE ssd1.DailyDate=DATEADD(DAY,-1,ssd.DailyDate) AND ssd1.DeptID=ssd.DeptID "); if (!SumModel) {// 单品模式 sbLast.Append(" AND pi1.ID=ssd.ProductID AND ISNULL(ssd1.BatchNo,'')=ISNULL(ssd.BatchNo,'') "); } bool bExt = !string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc) && (int.Parse(EFIndex) > 0); if (bExt && !bTotal) { sb.AppendLine(" ,pi1.ExtField" + EFIndex + " AS ExtField "); } sb.AppendLine(@"FROM officedba.SubStorageDaily ssd LEFT JOIN officedba.ProductInfo pi1 ON ssd.ProductID=pi1.ID LEFT JOIN officedba.DeptInfo di ON ssd.DeptID=di.ID "); // 机构 sb.AppendLine(" WHERE ssd.CompanyCD=@CompanyCD "); sbLast.AppendLine(" AND ssd1.CompanyCD=@CompanyCD "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); // 分店 if (int.TryParse(SubStoreID, out id) && id > 0) { sb.AppendLine(" AND ssd.DeptID=@DeptID "); comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", id)); } // 开始时间 sb.AppendLine(" AND DATEDIFF(DAY,ssd.DailyDate,@dStime)<=0 "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@dStime", dStime.ToString())); // 结束时间 sb.AppendLine(" AND DATEDIFF(DAY,ssd.DailyDate,@dEtime)>=0 "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@dEtime", dEtime.ToString())); //颜色 if (int.TryParse(model.ColorID, out id) && id > 0) { sb.AppendLine(" AND pi1.ColorID=@ColorID "); comm.Parameters.Add(SqlHelper.GetParameter("@ColorID", id)); } //产地 if (!string.IsNullOrEmpty(model.FromAddr)) { sb.AppendLine(" AND pi1.FromAddr LIKE @FromAddr "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", string.Format("%{0}%", model.FromAddr))); } //厂家 if (!string.IsNullOrEmpty(model.Manufacturer)) { sb.AppendLine(" AND pi1.Manufacturer LIKE @Manufacturer "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", string.Format("%{0}%", model.Manufacturer))); } //尺寸 if (!string.IsNullOrEmpty(model.Size)) { sb.AppendLine(" AND pi1.Size LIKE @Size "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Size", string.Format("%{0}%", model.Size))); } //材质 if (int.TryParse(model.Material, out id) && id > 0) { sb.AppendLine(" AND pi1.Material=@Material "); comm.Parameters.Add(SqlHelper.GetParameter("@Material", id)); } //条码 if (!string.IsNullOrEmpty(model.BarCode)) { sb.AppendLine(" AND pi1.BarCode LIKE @BarCode "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", string.Format("%{0}%", model.BarCode))); } //物品编号 if (!string.IsNullOrEmpty(model.ProdNo)) { sb.AppendLine(" AND pi1.ProdNo=@ProdNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", model.ProdNo)); } //品名 if (!string.IsNullOrEmpty(model.ProductName)) { sb.AppendLine(" AND pi1.ProductName LIKE @ProductName "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", string.Format("%{0}%", model.ProductName))); } //规格 if (!string.IsNullOrEmpty(model.Specification)) { sb.AppendLine(" AND pi1.Specification LIKE @Specification "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", string.Format("%{0}%", model.Specification))); } //批次 if (!string.IsNullOrEmpty(BatchNo)) { sb.AppendLine(" AND ssd.BatchNo LIKE @BatchNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", string.Format("%{0}%", BatchNo))); } if (bExt) { sb.AppendLine(" AND pi1.ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } if (!bTotal) { sb.AppendLine(@"GROUP BY ssd.DailyDate,ssd.DeptID,di.DeptName"); sbLast.AppendLine(@"GROUP BY ssd1.DailyDate,ssd1.DeptID"); if (bExt) { sb.AppendLine(",pi1.ExtField" + EFIndex); sbLast.AppendLine(",pi1.ExtField" + EFIndex); } if (!SumModel) { sb.AppendLine(",pi1.ProdNo,pi1.ProductName,pi1.Specification,ssd.BatchNo,ssd.ProductID"); sbLast.AppendLine(",pi1.ProdNo,pi1.ProductName,pi1.Specification,ssd1.BatchNo"); } } //指定命令的SQL文 if (bTotal) {// 求总量模式 comm.CommandText = sb.ToString(); } else { comm.CommandText = string.Format(sb.ToString(), sbLast.ToString()); } return comm; }
/// <summary> /// 进销存日报表 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable GetBuyingSellingStockingByDay(ProductInfoModel model, string DailyDate, string BatchNo, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("SELECT isnull( CONVERT(CHAR(10), a.DailyDate, 23),'') as DailyDate,b.ProductName,a.ProductID,b.Specification,a.BatchNo,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),isnull((select top 1 b.TodayCount from officedba.StorageDaily b where"); searchSql.AppendLine(" a.ProductID=b.ProductID"); searchSql.AppendLine(" and b.DailyDate>=(CONVERT(CHAR(10), dateadd(dd,-1,a.DailyDate), 23))"); searchSql.AppendLine(" and b.DailyDate<CONVERT(CHAR(10), a.DailyDate, 23)"); searchSql.AppendLine(" ),0)) as YestodayCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.PhurInCount) as PhurInCount,Convert(numeric(22," + userInfo.SelPoint + "),a.MakeInCount) as MakeInCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.DispInCount) as DispInCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.OtherInCount) as OtherInCount,Convert(numeric(22," + userInfo.SelPoint + "),a.SendInCount) as SendInCount,Convert(numeric(22," + userInfo.SelPoint + "),a.SubSaleBackInCount) as SubSaleBackInCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.TakeInCount) as TakeInCount,Convert(numeric(22," + userInfo.SelPoint + "),a.InTotal) as InTotal,Convert(numeric(22," + userInfo.SelPoint + "),a.SaleFee) as SaleFee,Convert(numeric(22," + userInfo.SelPoint + "),a.PhurBackFee) as PhurBackFee,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.InitInCount) as InitInCount,Convert(numeric(22," + userInfo.SelPoint + "),a.InitBatchCount) as InitBatchCount,Convert(numeric(22," + userInfo.SelPoint + "),a.SaleBackInCount) as SaleBackInCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.RedInCount) as RedInCount,Convert(numeric(22," + userInfo.SelPoint + "),a.BackInCount) as BackInCount,"); searchSql.AppendLine(" "); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.SaleOutCount) as SaleOutCount,Convert(numeric(22," + userInfo.SelPoint + "),a.TakeOutCount) as TakeOutCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.DispOutCount) as DispOutCount,Convert(numeric(22," + userInfo.SelPoint + "),a.BadCount) as BadCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.OtherOutCount) as OtherOutCount,Convert(numeric(22," + userInfo.SelPoint + "),a.SendOutCount) as SendOutCount,Convert(numeric(22," + userInfo.SelPoint + "),a.SubSaleOutCount) as SubSaleOutCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.OutTotal) as OutTotal,Convert(numeric(22," + userInfo.SelPoint + "),a.PhurFee) as PhurFee,Convert(numeric(22," + userInfo.SelPoint + "),a.SaleBackFee) as SaleBackFee,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),a.PhurBackOutCount) as PhurBackOutCount,Convert(numeric(22," + userInfo.SelPoint + "),a.RedOutCount) as RedOutCount,Convert(numeric(22," + userInfo.SelPoint + "),a.LendCount) as LendCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),ABS(a.CheckCount)) as CheckCount,Convert(numeric(22," + userInfo.SelPoint + "),ABS(a.AdjustCount)) as AdjustCount,"); searchSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint + "),ABS(a.TodayCount)) as TodayCount,a.CreateDate,"); searchSql.AppendLine(" b.ExtField1,b.ExtField2,b.ExtField3,b.ExtField4,b.ExtField5,b.ExtField6,b.ExtField7,b.ExtField8,b.ExtField9,b.ExtField10,"); searchSql.AppendLine(" b.ExtField11,b.ExtField12,b.ExtField13,b.ExtField14,b.ExtField15,b.ExtField16,b.ExtField17,b.ExtField18,b.ExtField19,b.ExtField20,"); searchSql.AppendLine(" b.ExtField21,b.ExtField22,b.ExtField23,b.ExtField24,b.ExtField25,b.ExtField26,b.ExtField27,b.ExtField28,b.ExtField29,b.ExtField30"); searchSql.AppendLine("FROM officedba.StorageDaily a "); searchSql.AppendLine("left join officedba.ProductInfo b on a.ProductID=b.ID"); searchSql.AppendLine("where a.CompanyCD=@CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //单据编号 if (!string.IsNullOrEmpty(DailyDate)) { searchSql.AppendLine(" and CONVERT(CHAR(10), a.DailyDate, 23)=@DailyDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DailyDate", DailyDate)); } //物品编号 if (!string.IsNullOrEmpty(model.ProdNo)) { searchSql.AppendLine(" and b.ProdNo like @ProdNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", "%" + model.ProdNo + "%")); } //物品名称 if (!string.IsNullOrEmpty(model.ProductName)) { searchSql.AppendLine(" and b.ProductName like @ProductName"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", "%" + model.ProductName + "%")); } //物品规格 if (!string.IsNullOrEmpty(model.Specification)) { searchSql.AppendLine(" and b.Specification like @Specification"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", "%" + model.Specification + "%")); } //仓库 if (!string.IsNullOrEmpty(model.StorageID)) { if (int.Parse(model.StorageID) > 0) { searchSql.AppendLine(" and a.StorageID=@StorageID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } } //仓库 if (!string.IsNullOrEmpty(BatchNo)) { searchSql.AppendLine(" and a.BatchNo=@BatchNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } //条码 if (!string.IsNullOrEmpty(model.BarCode)) { searchSql.AppendLine(" and b.BarCode=@BarCode"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", model.BarCode)); } //产地 if (!string.IsNullOrEmpty(model.FromAddr)) { searchSql.AppendLine(" and b.FromAddr like @FromAddr"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", "%" + model.FromAddr + "%")); } //厂家 if (!string.IsNullOrEmpty(model.Manufacturer)) { searchSql.AppendLine(" and b.Manufacturer like @Manufacturer"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", "%" + model.Manufacturer + "%")); } //尺寸 if (!string.IsNullOrEmpty(model.Size)) { searchSql.AppendLine(" and b.Size=@Size"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Size", model.Size)); } //材质 if (!string.IsNullOrEmpty(model.Material)) { searchSql.AppendLine(" and b.Material=@Material"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", model.Material)); } //颜色 if (!string.IsNullOrEmpty(model.ColorID)) { searchSql.AppendLine(" and b.ColorID=@ColorID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", model.ColorID)); } //其他条件 if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and b.ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 扩展属性保存操作 /// </summary> /// <returns></returns> private static void GetExtAttrCmd(ProductInfoModel model, Hashtable htExtAttr, SqlCommand cmd) { try { string strSql = string.Empty; strSql = "UPDATE officedba.ProductInfo set "; foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例 { strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ","; cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim()); } int iLength = strSql.Length - 1; strSql = strSql.Substring(0, iLength); strSql += " where CompanyCD = @CompanyCD AND ProdNo = @ProdNo"; cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD); cmd.Parameters.AddWithValue("@ProdNo", model.ProdNo); cmd.CommandText = strSql; } catch { } }
/// <summary> /// 进销存汇总表 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="OrderBy"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataTable GetStorageInAndOutTotalInfo(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, out DataTable dt, ref int totalCount) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; string point = userInfo.SelPoint; string startyearmonth = ""; string endyearmonth = ""; string startmonth = Convert.ToDateTime(DailyDate).AddDays(-1).Month.ToString(); string endmonth = Convert.ToDateTime(EndDate).Month.ToString(); if (Convert.ToInt32(startmonth) < 10) startmonth = "0" + startmonth; if (Convert.ToInt32(endmonth) < 10) endmonth = "0" + endmonth; startyearmonth = Convert.ToDateTime(DailyDate).AddDays(-1).Year + startmonth; endyearmonth = Convert.ToDateTime(EndDate).AddDays(-1).Year + endmonth; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" SELECT A.*,Convert(numeric(22," + point + "),ISNULL(A.YestodayCount,0)*ISNULL(B.PeriodBeginCost,0))PeriodBeginCost,Convert(char(20),Convert(numeric(22," + point + "),ISNULL(A.YestodayCount,0)*ISNULL(B.PeriodBeginCost,0)))+' ' PeriodBeginCost1,Convert(numeric(22," + point + "),ISNULL(A.TodayCount,0)*ISNULL(C.PeriodEndCost,0))PeriodEndCost,Convert(char(20),Convert(numeric(22," + point + "),ISNULL(A.TodayCount,0)*ISNULL(C.PeriodEndCost,0)))+' ' PeriodEndCost1 "); searchSql.AppendLine(" FROM "); searchSql.AppendLine(" (SELECT a.CompanyCD, "); searchSql.AppendLine(" b.ProductName,a.ProductID,b.Specification,a.BatchNo "); if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" ,b.ExtField" + EFIndex + " "); } } searchSql.AppendLine(" ,SUM(Convert(numeric(22," + point + "),a.PhurInCount)) as PhurInCount, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.PhurInCount)))+' ' as PhurInCount1, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.MakeInCount)) as MakeInCount, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.MakeInCount)))+' ' as MakeInCount1, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.DispInCount)) as DispInCount, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.DispInCount)))+' ' as DispInCount1, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.OtherInCount)) as OtherInCount, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.OtherInCount)))+' ' as OtherInCount1, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SendInCount)) as SendInCount, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SendInCount)))+' ' as SendInCount1, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SubSaleBackInCount)) as SubSaleBackInCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.TakeInCount)) as TakeInCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.InTotal)) as InTotal, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SaleFee)) as SaleFee, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.PhurBackFee)) as PhurBackFee, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.InitInCount)) as InitInCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.InitBatchCount)) as InitBatchCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SaleBackInCount)) as SaleBackInCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.RedInCount)) as RedInCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.BackInCount)) as BackInCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SaleOutCount)) as SaleOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.TakeOutCount)) as TakeOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.DispOutCount)) as DispOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.BadCount)) as BadCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.OtherOutCount)) as OtherOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SendOutCount)) as SendOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SubSaleOutCount)) as SubSaleOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.OutTotal)) as OutTotal, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.PhurFee)) as PhurFee, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.SaleBackFee)) as SaleBackFee, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.PhurBackOutCount)) as PhurBackOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.RedOutCount)) as RedOutCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.LendCount)) as LendCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.CheckCount)) as CheckCount, "); searchSql.AppendLine(" SUM(Convert(numeric(22," + point + "),a.AdjustCount)) as AdjustCount, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SubSaleBackInCount)))+' ' as SubSaleBackInCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.TakeInCount)))+' ' as TakeInCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.InTotal)))+' ' as InTotal1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SaleFee)))+' ' as SaleFee1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.PhurBackFee)))+' ' as PhurBackFee1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.InitInCount)))+' ' as InitInCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.InitBatchCount)))+' ' as InitBatchCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SaleBackInCount)))+' ' as SaleBackInCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.RedInCount)))+' ' as RedInCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.BackInCount)))+' ' as BackInCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SaleOutCount)))+' ' as SaleOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.TakeOutCount)))+' ' as TakeOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.DispOutCount)))+' ' as DispOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.BadCount)))+' ' as BadCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.OtherOutCount)))+' ' as OtherOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SendOutCount)))+' ' as SendOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SubSaleOutCount)))+' ' as SubSaleOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.OutTotal)))+' ' as OutTotal1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.PhurFee)))+' ' as PhurFee1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.SaleBackFee)))+' ' as SaleBackFee1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.PhurBackOutCount)))+' ' as PhurBackOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.RedOutCount)))+' ' as RedOutCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.LendCount)))+' ' as LendCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.CheckCount)))+' ' as CheckCount1, "); searchSql.AppendLine(" Convert(char(20),SUM(Convert(numeric(22," + point + "),a.AdjustCount)))+' ' as AdjustCount1, "); searchSql.AppendLine(" Convert(numeric(22," + point + "),ISNULL((select top 1 b.TodayCount from officedba.StorageDaily b where "); searchSql.AppendLine(" a.ProductID=b.ProductID "); searchSql.AppendLine(" and b.DailyDate=CONVERT(CHAR(10), dateadd(dd,-1,'"+DailyDate+"'),120)),0)) as YestodayCount, "); searchSql.AppendLine(" Convert(char(20),Convert(numeric(22," + point + "),ISNULL((select top 1 b.TodayCount from officedba.StorageDaily b where "); searchSql.AppendLine(" a.ProductID=b.ProductID "); searchSql.AppendLine(" and b.DailyDate=CONVERT(CHAR(10), dateadd(dd,-1,'" + DailyDate + "'),120)),0))) as YestodayCount1, "); searchSql.AppendLine(" Convert(numeric(22," + point + "),ISNULL((select top 1 b.TodayCount from officedba.StorageDaily b where "); searchSql.AppendLine(" a.ProductID=b.ProductID "); searchSql.AppendLine(" and b.DailyDate=CONVERT(CHAR(10), '"+EndDate+"',120)),0)) as TodayCount, "); searchSql.AppendLine(" Convert(char(20),Convert(numeric(22," + point + "),ISNULL((select top 1 b.TodayCount from officedba.StorageDaily b where "); searchSql.AppendLine(" a.ProductID=b.ProductID "); searchSql.AppendLine(" and b.DailyDate=CONVERT(CHAR(10), '" + EndDate + "',120)),0)))+' ' as TodayCount1 "); searchSql.AppendLine(" FROM officedba.StorageDaily a "); searchSql.AppendLine(" left join officedba.ProductInfo b on a.ProductID=b.ID and a.CompanyCD=b.CompanyCD "); searchSql.AppendLine(" where a.CompanyCD=@CompanyCD "); searchSql.AppendLine(" and DailyDate>='"+DailyDate+"' "); searchSql.AppendLine(" and a.DailyDate<='"+EndDate+"' "); //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //开始日期 if (!string.IsNullOrEmpty(DailyDate)) { searchSql.AppendLine(" and DailyDate>=@DailyDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DailyDate", DailyDate)); } //结束日期 if (!string.IsNullOrEmpty(EndDate)) { searchSql.AppendLine(" and a.DailyDate<=@EndDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", EndDate)); } //物品编号 if (!string.IsNullOrEmpty(model.ProdNo)) { searchSql.AppendLine(" and b.ProdNo=@ProdNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", model.ProdNo)); } //物品名称 if (!string.IsNullOrEmpty(model.ProductName)) { searchSql.AppendLine(" and b.ProductName like '%'+ @ProductName + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", model.ProductName)); } //物品规格 if (!string.IsNullOrEmpty(model.Specification)) { searchSql.AppendLine(" and b.Specification like '%'+ @Specification + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", model.Specification)); } //颜色 if (!string.IsNullOrEmpty(model.ColorID)) { if (int.Parse(model.ColorID) > 0) { searchSql.AppendLine(" and b.ColorID=@ColorID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", model.ColorID)); } } //产地 if (!string.IsNullOrEmpty(model.FromAddr)) { searchSql.AppendLine(" and b.FromAddr like '%'+ @FromAddr + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", model.FromAddr)); } //厂家 if (!string.IsNullOrEmpty(model.Manufacturer)) { searchSql.AppendLine(" and b.Manufacturer like '%'+ @Manufacturer + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", model.Manufacturer)); } //尺寸 if (!string.IsNullOrEmpty(model.Size)) { searchSql.AppendLine(" and b.Size =@Size "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Size", model.Size)); } //材质 if (!string.IsNullOrEmpty(model.Material)) { if (int.Parse(model.Material) > 0) { searchSql.AppendLine(" and b.Material=@Material "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", model.Material)); } } //条码 if (!string.IsNullOrEmpty(model.BarCode)) { searchSql.AppendLine(" and b.BarCode=@BarCode "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", model.BarCode)); } //仓库 if (!string.IsNullOrEmpty(model.StorageID)) { if (int.Parse(model.StorageID) > 0) { searchSql.AppendLine(" and a.StorageID=@StorageID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } } //批次 if (!string.IsNullOrEmpty(BatchNo)) { searchSql.AppendLine(" and a.BatchNo=@BatchNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and b.ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } searchSql.AppendLine(" group by a.CompanyCD,a.ProductID,a.BatchNo,b.ProductName,b.Specification "); if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" ,b.ExtField" + EFIndex + " "); } } searchSql.AppendLine(" )A "); searchSql.AppendLine(" LEFT OUTER JOIN "); searchSql.AppendLine(" (select * from officedba.StorageCost a "); searchSql.AppendLine(" where a.YearMonth='" + startyearmonth + "') B "); searchSql.AppendLine(" ON A.ProductID=B.ProductID and A.CompanyCD=B.CompanyCD "); searchSql.AppendLine(" LEFT OUTER JOIN "); searchSql.AppendLine(" (select * from officedba.StorageCost a "); searchSql.AppendLine(" where a.YearMonth='" + endyearmonth + "') C "); searchSql.AppendLine(" ON A.ProductID=C.ProductID and A.CompanyCD=C.CompanyCD "); #endregion //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 dt = GetAllTotal(model, DailyDate, EndDate, BatchNo, EFIndex, EFDesc, true); return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 设置参数 /// </summary> /// <param name="comm"></param> /// <param name="model"></param> private static void SetSaveParameter(SqlCommand comm, ProductInfoModel model) { comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", model.ProdNo));//物品编号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@PYShort", model.PYShort));//拼音缩写 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", model.ProductName));//物品名称 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ShortNam", model.ShortNam));//名称简称 comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", model.BarCode));//条码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", model.TypeID));//物品分类ID(对应物品分类代码表ID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@BigType", model.BigType));//所属大类 comm.Parameters.Add(SqlHelper.GetParameterFromString("@GradeID", model.GradeID));//物品档次级别ID(对应分类代码表ID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@UnitID", model.UnitID));//单位ID(计量单位ID,对应计量单位表ID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@Brand", model.Brand));//品牌ID(对应分类代码表ID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", model.ColorID));//颜色ID(对应分类代码表ID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", model.Specification));//规格型号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Size", model.Size));//尺寸 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Source", model.Source));//物品来源分类(1:库存;2:生产;3:外购;4:委托加工) comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", model.FromAddr));//产地 comm.Parameters.Add(SqlHelper.GetParameterFromString("@DrawingNum", model.DrawingNum));//图号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ImgUrl", model.ImgUrl));//产品图片 comm.Parameters.Add(SqlHelper.GetParameterFromString("@FileNo", model.FileNo));//批准文号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@PricePolicy", model.PricePolicy));//价格策略 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Params", model.Params));//技术参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Questions", model.Questions));//常见问题 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ReplaceName", model.ReplaceName));//替代品名称 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Description", model.Description));//物品描述信息 comm.Parameters.Add(SqlHelper.GetParameterFromString("@StockIs", model.StockIs));//是否计入库存(0否,1是) comm.Parameters.Add(SqlHelper.GetParameterFromString("@MinusIs", model.MinusIs));//是否允许负库存(0否,1是) comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID));//主放仓库(对应仓库表ID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@SafeStockNum", model.SafeStockNum));//安全库存量 comm.Parameters.Add(SqlHelper.GetParameterFromString("@MinStockNum", model.MinStockNum));//最低库存量 comm.Parameters.Add(SqlHelper.GetParameterFromString("@MaxStockNum", model.MaxStockNum));//最高库存量 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ABCType", model.ABCType));//ABC分类(A/B/C) comm.Parameters.Add(SqlHelper.GetParameterFromString("@CalcPriceWays", model.CalcPriceWays));//成本核算计价方法 comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardCost", model.StandardCost));//标准成本 comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanCost", model.PlanCost));//计划成本 comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardSell", model.StandardSell));//标准售价 comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellMin", model.SellMin));//最低售价限制 comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellMax", model.SellMax));//最高售价限制 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaxRate", model.TaxRate));//销项税率(%) comm.Parameters.Add(SqlHelper.GetParameterFromString("@InTaxRate", model.InTaxRate));//进项税率(%) comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellTax", model.SellTax));//含税售价 comm.Parameters.Add(SqlHelper.GetParameterFromString("@SellPrice", model.SellPrice));//零售价 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TransferPrice", model.TransferPrice));//调拨单价 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Discount", model.Discount));//折扣率(%) comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardBuy", model.StandardBuy));//采购含税价 comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaxBuy", model.TaxBuy));//采购单价 comm.Parameters.Add(SqlHelper.GetParameterFromString("@BuyMax", model.BuyMax));//最高采购价限制 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark));//备注 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", model.Manufacturer));//备注 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", model.Material));//备注 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator));//建档人ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", model.CreateDate));//建档日期 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckStatus", model.CheckStatus));//审核状态(0草稿,1已审) comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckUser", model.CheckUser));//审核人ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@CheckDate", model.CheckDate));//审核日期 comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus));//启用状态(0停用,1启用) comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", model.ModifiedDate));//最后更新日期 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));//最后更新用户ID(对应操作用户U serID) comm.Parameters.Add(SqlHelper.GetParameterFromString("@GroupUnitNo", model.GroupNo));//计量单位组编号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@SaleUnitID", model.SellUnit));//销售单位 comm.Parameters.Add(SqlHelper.GetParameterFromString("@InUnitID", model.PurchseUnit));//采购单位 comm.Parameters.Add(SqlHelper.GetParameterFromString("@StockUnitID", model.StorageUnit));//库存单位 comm.Parameters.Add(SqlHelper.GetParameterFromString("@MakeUnitID", model.ProductUnit));//生产完工入库单位 comm.Parameters.Add(SqlHelper.GetParameterFromString("@IsBatchNo", model.IsBatchNo));//生产完工入库单位 }
/// <summary> /// 进销存汇总 总计 /// </summary> /// <param name="model"></param> /// <param name="DailyDate"></param> /// <param name="BatchNo"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <returns></returns> public static DataTable GetAllTotal(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, bool flag) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; string point = userInfo.SelPoint; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("SELECT Convert(numeric(22," + point + "),sum(InTotal)) as InTotalCount,Convert(char(20),Convert(numeric(22," + point + "),sum(InTotal)))+' ' as InTotalCount1,Convert(numeric(22," + point + "),sum(OutTotal)) as outTotalCount,Convert(char(20),Convert(numeric(22," + point + "),sum(OutTotal)))+' ' as outTotalCount1,Convert(numeric(22," + point + "),sum(SaleFee)) as SaleFeeCount,Convert(char(20),Convert(numeric(22," + point + "),sum(SaleFee)))+' ' as SaleFeeCount1,"); searchSql.AppendLine(" Convert(numeric(22," + point + "),sum(PhurFee)) as PhurFeeCount,Convert(char(20),Convert(numeric(22," + point + "),sum(PhurFee)))+' ' as PhurFeeCount1,Convert(numeric(22," + point + "),sum(PhurBackFee)) as PhurBackFeeCount,Convert(char(20),Convert(numeric(22," + point + "),sum(PhurBackFee)))+' ' as PhurBackFeeCount1,Convert(numeric(22," + point + "),sum(SaleBackFee)) as SaleBackFeeCount,Convert(char(20),Convert(numeric(22," + point + "),sum(SaleBackFee)))+' ' as SaleBackFeeCount1 "); searchSql.AppendLine("FROM officedba.StorageDaily a left join officedba.ProductInfo b on a.ProductID=b.ID and a.CompanyCD=b.CompanyCD "); searchSql.AppendLine("where a.CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //开始日期 if (!string.IsNullOrEmpty(DailyDate)) { searchSql.AppendLine(" and a.DailyDate>=@DailyDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DailyDate", DailyDate)); } //结束日期 if (!string.IsNullOrEmpty(EndDate)) { searchSql.AppendLine(" and a.DailyDate<=@EndDate"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", EndDate)); } if (flag) { //物品编号 if (!string.IsNullOrEmpty(model.ProdNo)) { searchSql.AppendLine(" and b.ProdNo=@ProdNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", model.ProdNo)); } //物品名称 if (!string.IsNullOrEmpty(model.ProductName)) { searchSql.AppendLine(" and b.ProductName like '%'+ @ProductName + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", model.ProductName)); } //物品规格 if (!string.IsNullOrEmpty(model.Specification)) { searchSql.AppendLine(" and b.Specification like '%'+ @Specification + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", model.Specification)); } //颜色 if (!string.IsNullOrEmpty(model.ColorID)) { if (int.Parse(model.ColorID) > 0) { searchSql.AppendLine(" and b.ColorID=@ColorID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", model.ColorID)); } } //产地 if (!string.IsNullOrEmpty(model.FromAddr)) { searchSql.AppendLine(" and b.FromAddr like '%'+ @FromAddr + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", model.FromAddr)); } //厂家 if (!string.IsNullOrEmpty(model.Manufacturer)) { searchSql.AppendLine(" and b.Manufacturer like '%'+ @Manufacturer + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", model.Manufacturer)); } //尺寸 if (!string.IsNullOrEmpty(model.Size)) { searchSql.AppendLine(" and b.Size =@Size "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Size", model.Size)); } //材质 if (!string.IsNullOrEmpty(model.Material)) { if (int.Parse(model.Material) > 0) { searchSql.AppendLine(" and b.Material=@Material "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", model.Material)); } } //条码 if (!string.IsNullOrEmpty(model.BarCode)) { searchSql.AppendLine(" and b.BarCode=@BarCode "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", model.BarCode)); } //仓库 if (!string.IsNullOrEmpty(model.StorageID)) { if (int.Parse(model.StorageID) > 0) { searchSql.AppendLine(" and a.StorageID=@StorageID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } } //批次 if (!string.IsNullOrEmpty(BatchNo)) { searchSql.AppendLine(" and a.BatchNo=@BatchNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { if (int.Parse(EFIndex) > 0) { searchSql.AppendLine(" and b.ExtField" + EFIndex + " LIKE @EFDesc"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 修改物品档案 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool UpdateProductInfo(ProductInfoModel model, Hashtable htExtAttr) { StringBuilder sql = new StringBuilder(); sql.AppendLine("UPDATE officedba.ProductInfo "); sql.AppendLine(" SET "); sql.AppendLine(" PYShort = @PYShort "); sql.AppendLine(" ,ProductName = @ProductName "); sql.AppendLine(" ,ShortNam = @ShortNam "); sql.AppendLine(" ,BarCode = @BarCode "); sql.AppendLine(" ,TypeID = @TypeID "); sql.AppendLine(" ,BigType = @BigType "); sql.AppendLine(" ,GradeID = @GradeID "); if (int.Parse(model.Source) > 0) { sql.AppendLine(" ,Source = @Source "); } sql.AppendLine(" ,UnitID = @UnitID "); sql.AppendLine(" ,Brand = @Brand "); sql.AppendLine(" ,Specification = @Specification "); sql.AppendLine(" ,ColorID = @ColorID "); sql.AppendLine(" ,Size = @Size "); sql.AppendLine(" ,StockIs = @StockIs "); if (!string.IsNullOrEmpty(model.ABCType)) { sql.AppendLine(" ,ABCType = @ABCType "); } sql.AppendLine(" ,Remark = @Remark "); sql.AppendLine(" ,Creator = @Creator "); sql.AppendLine(" ,CreateDate = @CreateDate "); sql.AppendLine(" ,CheckStatus = @CheckStatus "); sql.AppendLine(" ,CheckUser = @CheckUser "); sql.AppendLine(" ,CheckDate = @CheckDate "); sql.AppendLine(" ,UsedStatus = @UsedStatus "); sql.AppendLine(" ,ModifiedDate = @ModifiedDate "); sql.AppendLine(" ,ModifiedUserID = @ModifiedUserID "); sql.AppendLine(" ,FromAddr = @FromAddr "); sql.AppendLine(" ,DrawingNum = @DrawingNum "); sql.AppendLine(" ,ImgUrl = @ImgUrl "); sql.AppendLine(" ,FileNo = @FileNo "); sql.AppendLine(" ,PricePolicy = @PricePolicy "); sql.AppendLine(" ,Params = @Params "); sql.AppendLine(" ,Questions = @Questions "); sql.AppendLine(" ,ReplaceName = @ReplaceName "); sql.AppendLine(" ,Description = @Description "); sql.AppendLine(" ,MinusIs = @MinusIs "); sql.AppendLine(" ,StorageID = @StorageID "); sql.AppendLine(" ,SafeStockNum = @SafeStockNum "); sql.AppendLine(" ,MinStockNum = @MinStockNum "); sql.AppendLine(" ,MaxStockNum = @MaxStockNum "); sql.AppendLine(" ,CalcPriceWays = @CalcPriceWays "); sql.AppendLine(" ,StandardCost = @StandardCost "); sql.AppendLine(" ,PlanCost = @PlanCost "); sql.AppendLine(" ,StandardSell = @StandardSell "); sql.AppendLine(" ,SellMin = @SellMin "); sql.AppendLine(" ,SellMax = @SellMax "); sql.AppendLine(" ,TaxRate = @TaxRate "); sql.AppendLine(" ,InTaxRate = @InTaxRate "); sql.AppendLine(" ,SellTax = @SellTax "); sql.AppendLine(" ,SellPrice = @SellPrice "); sql.AppendLine(" ,TransferPrice =@TransferPrice "); sql.AppendLine(" ,Discount = @Discount "); sql.AppendLine(" ,StandardBuy = @StandardBuy "); sql.AppendLine(" ,TaxBuy = @TaxBuy "); sql.AppendLine(" ,BuyMax = @BuyMax "); sql.AppendLine(" ,Manufacturer = @Manufacturer "); sql.AppendLine(" ,GroupUnitNo = @GroupUnitNo "); sql.AppendLine(" ,SaleUnitID = @SaleUnitID "); sql.AppendLine(" ,InUnitID = @InUnitID "); sql.AppendLine(" ,StockUnitID = @StockUnitID "); sql.AppendLine(" ,MakeUnitID = @MakeUnitID "); sql.AppendLine(" ,Material = @Material "); sql.AppendLine(" ,IsBatchNo = @IsBatchNo "); sql.AppendLine(" where "); sql.AppendLine(" CompanyCD = @CompanyCD "); sql.AppendLine(" AND ProdNo = @ProdNo "); //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); comm.CommandText = sql.ToString(); SetSaveParameter(comm, model);//其他参数 //执行更新并设置更新结果 bool result = false; ArrayList lstCmd = new ArrayList(); SqlCommand cmd = new SqlCommand(); GetExtAttrCmd(model, htExtAttr, cmd); lstCmd.Add(comm); if (htExtAttr != null) lstCmd.Add(cmd); result = SqlHelper.ExecuteTransWithArrayList(lstCmd); return result; }