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