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>
/// <returns>DataTable</returns>
public static DataTable GetStorageProductListBycondition(StorageProductModel model)
{
try
{
return StorageProductDBHelper.GetStorageProductTableBycondition(model);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 查询库存报损单
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetProductStorageTableBycondition(StorageProductModel model, string ProductNo, string ProductName, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
try
{
return StockSearchDBHelper.GetProductStorageTableBycondition(model, ProductNo, ProductName, pageIndex, pageCount, ord, ref TotalCount, iflist);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 仓库物品插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertStorageProduct(StorageProductModel model)
{
//获取登陆用户ID
//string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;[待修改]
string loginUserID = "admin123";
bool result = false;
if (StorageProductDBHelper.InsertStorageProduct(model, loginUserID))
{
model.ID = IDIdentityUtil.GetIDIdentity("officedba.StorageProduct").ToString();
result = true;
}
return result;
}
/// <summary>
/// 添加工艺档案记录
/// </summary>
/// <returns>DataTable</returns>
public static bool InsertStorageProduct(StorageProductModel model, string loginUserID)
{
//SQL拼写
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.StorageProduct");
sql.AppendLine(" (CompanyCD ");
sql.AppendLine(" ,StorageNo ");
sql.AppendLine(" ,StorageID ");
sql.AppendLine(" ,ProductID ");
sql.AppendLine(" ,CostPrice ");
sql.AppendLine(" ,ProductCount ");
sql.AppendLine(" ,LockCount ");
sql.AppendLine(" ,Remark ");
sql.AppendLine(" ,ModifiedDate ");
sql.AppendLine(" ,ModifiedUserID ");
sql.AppendLine("VALUES ");
sql.AppendLine(" (@CompanyCD ");
sql.AppendLine(" ,@StorageNo ");
sql.AppendLine(" ,@StorageID ");
sql.AppendLine(" ,@ProductID ");
//sql.AppendLine(" ,@CostPrice ");
sql.AppendLine(" ,@ProductCount ");
//sql.AppendLine(" ,@LockCount ");
sql.AppendLine(" ,@Remark ");
sql.AppendLine(" ,getdate() ");
sql.AppendLine(" ,'" + loginUserID + "') ");
//设置参数
SqlParameter[] param = new SqlParameter[8];
param[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
param[1] = SqlHelper.GetParameter("@StorageNo", model.StorageNo);
param[2] = SqlHelper.GetParameter("@StorageID", model.StorageID);
param[3] = SqlHelper.GetParameter("@ProductID", model.ProductID);
//param[4] = SqlHelper.GetParameter("@CostPrice", model.CostPrice);
param[4] = SqlHelper.GetParameter("@ProductCount", model.ProductCount);
//param[6] = SqlHelper.GetParameter("@LockCount", model.LockCount);
param[5] = SqlHelper.GetParameter("@Remark", model.Remark);
SqlHelper.ExecuteTransSql(sql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
/// <summary>
/// 工艺档案
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetStorageProductTableBycondition(StorageProductModel model)
{
string sql = "select * from officedba.StorageProduct where 1=1";
if (int.Parse(model.ProductID.ToString()) > 0)
{
sql += " and ProductID='" + model.ProductID + "'";
}
if (model.StorageNo != "")
sql += " and StorageNo='" + model.StorageNo + "'";
//if (!string.IsNullOrEmpty( model.CostPrice.ToString())
//{
// sql += " and CostPrice='" + model.CostPrice + "'";
//}
if (!string.IsNullOrEmpty(model.ProductCount.ToString()))
{
sql += " and ProductCount='" + model.ProductCount + "'";
}
return SqlHelper.ExecuteSql(sql);
}
/// <summary>
/// 查询库存报损单
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetProductStorageTableBycondition(StorageProductModel model, string ProductNo, string ProductName, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT a.ID ");
sql.AppendLine(" ,ISNULL(b.StorageNo,'') as StorageNo ");
sql.AppendLine(" ,ISNULL(b.StorageName,'') as StorageName ");
sql.AppendLine(" ,ISNULL(d.ProdNo,'') as ProductNo ");
sql.AppendLine(" ,ISNULL(d.ProductName,'') as ProductName ");
sql.AppendLine(" ,ISNULL(d.Specification,'') as Specification ");
sql.AppendLine(" ,ISNULL(e.CodeName,'') as UnitID ");
sql.AppendLine(" ,ISNULL(c.DeptName,'') as DeptName ");
sql.AppendLine(" ,ISNULL(a.ProductCount,0) as ProductCount ");
sql.AppendLine(" ,ISNULL(a.ProductCount,0)+ISNULL(a.RoadCount,0)+ISNULL(a.InCount,0)-ISNULL(a.OrderCount,0)-ISNULL(a.OutCount,0) as UseCount ");
sql.AppendLine(" ,ISNULL(a.OrderCount,0) as OrderCount ");
sql.AppendLine(" ,ISNULL(a.RoadCount,0) as RoadCount ");
sql.AppendLine(" ,ISNULL(a.OutCount,0) as OutCount ");
sql.AppendLine("FROM officedba.StorageProduct a ");
sql.AppendLine("left join officedba.StorageInfo b on a.StorageID=b.ID ");
sql.AppendLine("left join officedba.DeptInfo c on c.ID=a.DeptID ");
sql.AppendLine("left join officedba.ProductInfo d on d.ID=a.ProductID ");
sql.AppendLine("left join officedba.CodeUnitType e on e.ID=d.UnitID ");
sql.AppendLine(" where a.CompanyCD='" + model.CompanyCD + "'");
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine(" and a.StorageID = @StorageID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID));
}
if (!string.IsNullOrEmpty(ProductNo))
{
sql.AppendLine(" and d.ProdNo=@ProdNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", ProductNo));
}
if (!string.IsNullOrEmpty(ProductName))
{
sql.AppendLine(" and d.ProductName like '%' + @ProductName + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", ProductName));
}
comm.CommandText = sql.ToString();
if (!iflist)
{
if (!string.IsNullOrEmpty(ord))
{
sql.AppendLine(" order by " + ord);
}
return SqlHelper.ExecuteSearch(comm);
}
else
{
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount);
}
}
/// <summary>
/// 通过公司编号，为此公司做月结
/// </summary>
/// <param name="CompanyCD"></param>
/// <returns></returns>
public static bool StorageMonthEndForCompany(string CompanyCD)
{
//string LoginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
string LoginUserID = "admin";
ArrayList lstInsert = new ArrayList();//所有的插入sqlcommand命令集
DateTime nowDate = System.DateTime.Now;
string MonthNoNow = nowDate.ToString("yyyyMM");
string monthdays = DateTime.DaysInMonth(nowDate.Year, nowDate.Month).ToString();
string DateMonth = nowDate.ToString("yyyy-MM");
string EndDate = DateMonth + "-" + monthdays;//本月的结束日期
string StartDate = DateMonth + "-" + "01";//本月的开始日期（1号）
DataTable dtsp = SPInfo(CompanyCD);//查询当前对应CompanyCD在分仓存量表中，ProductID，StorageID确定的数据
if (dtsp.Rows.Count > 0)
{
for (int i = 0; i < dtsp.Rows.Count; i++)
{
decimal CountInitail = 0;//取上个月的期末数量，作为这个月的期初数量
decimal TotalInitail = 0;//取上个月的期末总额，作为这个月的期初总额
decimal RealCostInitail = 0;////取上个月的期末单位成本，作为这个月的期初成本
decimal CountInPurchase = 0;//本月采购入库总量
decimal TotalInPurchase = 0;//本月采购入库总额
decimal CountInProcess = 0;//本月生产完工入库总量
decimal TotalInProcess = 0;//本月生产完工入库总额
decimal CountInOther = 0;//本月其他入库总量
decimal TotalInOther = 0;//本月其他入库总额
decimal CountInRed = 0;//本月红冲入库总量
decimal TotalInRed = 0;//本月红冲入库总额
StorageProductModel model = new StorageProductModel();
model.CompanyCD = CompanyCD;
model.ProductID = dtsp.Rows[i]["ProductID"].ToString();
model.StorageID = dtsp.Rows[i]["StorageID"].ToString();
StorageMonthlyModel SMmodel = new StorageMonthlyModel();
SMmodel.CompanyCD = CompanyCD;
SMmodel.ProductID = dtsp.Rows[i]["ProductID"].ToString();
SMmodel.StorageID = dtsp.Rows[i]["StorageID"].ToString();
DataTable dtInitail_first = GetCountFromInitailDetail(model);//当公司第一次做月结的时候，获取公司的期初入库的数据
DataTable dtInitail = GetLastMonthInfo(SMmodel);//获取上个月的期末信息
DataTable dtInPurchase = GetCountFromInPurchaseDetail(model, MonthNoNow);//采购入库
DataTable dtInProcess = GetCountFromInProcessDetail(model, MonthNoNow);//生产完工
DataTable dtInOther = GetCountFromInOtherDetail(model, MonthNoNow);//其他入库
DataTable dtInRed = GetCountFromInRedDetail(model, MonthNoNow);//红冲入库（减少数据的）
if (dtInitail.Rows.Count > 0)
{
CountInitail = decimal.Parse(dtInitail.Rows[0]["NowCount"].ToString());
TotalInitail = decimal.Parse(dtInitail.Rows[0]["NowTotal"].ToString());
RealCostInitail = decimal.Parse(dtInitail.Rows[0]["NowRealCost"].ToString());
}
else if (dtInitail_first.Rows.Count > 0)
{
CountInitail = decimal.Parse(dtInitail_first.Rows[0]["ProductCount"].ToString());
TotalInitail = decimal.Parse(dtInitail_first.Rows[0]["TotalPrice"].ToString());
if (CountInitail.ToString() != "0.0000")
{
RealCostInitail = TotalInitail / CountInitail;
}
}
if (dtInPurchase.Rows.Count > 0)
{
CountInPurchase = decimal.Parse(dtInPurchase.Rows[0]["ProductCount"].ToString());
TotalInPurchase = decimal.Parse(dtInPurchase.Rows[0]["TotalPrice"].ToString());
}
if (dtInProcess.Rows.Count > 0)
{
CountInProcess = decimal.Parse(dtInProcess.Rows[0]["ProductCount"].ToString());
TotalInProcess = decimal.Parse(dtInProcess.Rows[0]["TotalPrice"].ToString());
}
if (dtInOther.Rows.Count > 0)
{
CountInOther = decimal.Parse(dtInOther.Rows[0]["ProductCount"].ToString());
TotalInOther = decimal.Parse(dtInOther.Rows[0]["TotalPrice"].ToString());
}
if (dtInRed.Rows.Count > 0)
{
CountInRed = decimal.Parse(dtInRed.Rows[0]["ProductCount"].ToString());
TotalInRed = decimal.Parse(dtInRed.Rows[0]["TotalPrice"].ToString());
}
SqlCommand comm = new SqlCommand();
StorageMonthlyModel SMmodel_Insert = new StorageMonthlyModel();
SMmodel_Insert.CompanyCD = CompanyCD;
SMmodel_Insert.MonthNo = MonthNoNow;
SMmodel_Insert.StartDate = StartDate;
SMmodel_Insert.EndDate = EndDate;
SMmodel_Insert.StorageID = model.StorageID;
SMmodel_Insert.ProductID = model.ProductID;
SMmodel_Insert.OldRealCost = RealCostInitail.ToString();
SMmodel_Insert.OldCount = CountInitail.ToString();
SMmodel_Insert.OldTotal = TotalInitail.ToString();
SMmodel_Insert.NowCount = dtsp.Rows[i]["ProductCount"].ToString();
if ((CountInitail + CountInPurchase + CountInProcess + CountInOther - CountInRed).ToString() != "0.0000")
{
SMmodel_Insert.NowRealCost = ((TotalInitail + TotalInPurchase + TotalInProcess + TotalInOther - TotalInRed) / (CountInitail + CountInPurchase + CountInProcess + CountInOther - CountInRed)).ToString();
SMmodel_Insert.NowTotal = (decimal.Parse(SMmodel_Insert.NowCount) * decimal.Parse(SMmodel_Insert.NowRealCost)).ToString();
}
SMmodel_Insert.ModifiedUserID = LoginUserID;
comm = InsertMonthly(SMmodel_Insert);
lstInsert.Add(comm);
}
return SqlHelper.ExecuteTransWithArrayList(lstInsert);
}
else//分仓存量表中没有没有当前公司数据
{
return false;
}
}
/// <summary>
/// 红冲入库明细中对应当前的ProductID，StorageID，CompanyCD，MonthNo查找出对应的数量和金额总数。
/// </summary>
/// <param name="model"></param>
/// <param name="MonthNo"></param>
/// <returns></returns>
protected static DataTable GetCountFromInRedDetail(StorageProductModel model, string MonthNo)
{
string sql = "select ISNULL(sum(b.ProductCount),0) as ProductCount,ISNULL(sum(b.TotalPrice),0) as TotalPrice "
+ "from officedba.StorageInRedDetail b "
+ "inner join officedba.StorageInRed a on a.InNo=b.InNo"
+ " where b.CompanyCD='" + model.CompanyCD + "' "
+ " and b.ProductID=" + model.ProductID
+ " and b.StorageID=" + model.StorageID
+ " and SUBSTRING(CONVERT(VARCHAR, a.ConfirmDate, 112), 1, 6) = " + MonthNo;
return SqlHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 当公司第一次做月结的时候，需要从期初入库明细中获取所有数据作为第一个月的期初数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
protected static DataTable GetCountFromInitailDetail(StorageProductModel model)
{
string sql = "select ISNULL(sum(b.ProductCount),0) as ProductCount,ISNULL(sum(b.TotalPrice),0) as TotalPrice "
+ "from officedba.StorageInitailDetail b "
+ "inner join officedba.StorageInitail a on a.InNo=b.InNo"
+ " where b.CompanyCD='" + model.CompanyCD + "' "
+ " and b.ProductID=" + model.ProductID
+ " and a.StorageID=" + model.StorageID;
return SqlHelper.ExecuteSql(sql.ToString());
}
/// <summary>
/// 现有存量汇总
/// </summary>
/// <param name="model"></param>
/// <param name="pdtModel"></param>
/// <param name="ProductCount1"></param>
/// <param name="EFIndex"></param>
/// <param name="EFDesc"></param>
/// <param name="orderby"></param>
/// <param name="BatchNo"></param>
/// <returns></returns>
public static string GetSumStorageInfo(StorageProductModel model, XBase.Model.Office.SupplyChain.ProductInfoModel pdtModel, string ProductCount1, string EFIndex, string EFDesc, string orderby, string BatchNo)
{
string rev = string.Empty;
StringBuilder sql = new StringBuilder();
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
sql.AppendLine("SELECT d.ExtField" + EFIndex + ",a.ID ");
}
else
{
sql.AppendLine("SELECT a.ID ");
}
sql.AppendLine(" ,ISNULL(b.StorageNo,'') as StorageNo,isnull(a.BatchNo,'') as BatchNo ");
sql.AppendLine(" ,ISNULL(b.StorageName,'') as StorageName ");
sql.AppendLine(" ,ISNULL(d.ProdNo,'') as ProductNo ");
sql.AppendLine(" ,ISNULL(d.ProductName,'') as ProductName ");
sql.AppendLine(" ,ISNULL(d.Specification,'') as Specification ");
sql.AppendLine(" ,ISNULL(e.CodeName,'') as UnitID ");
sql.AppendLine(" ,ISNULL(c.DeptName,'') as DeptName ");
sql.AppendLine(" ,ISNULL(a.ProductCount,0) as ProductCount ");
sql.AppendLine(" ,ISNULL(a.ProductCount,0)+ISNULL(a.RoadCount,0)+ISNULL(a.InCount,0)-ISNULL(a.OrderCount,0)-ISNULL(a.OutCount,0) as UseCount ");
sql.AppendLine(" ,ISNULL(a.OrderCount,0) as OrderCount ");
sql.AppendLine(" ,ISNULL(a.RoadCount,0) as RoadCount ");
sql.AppendLine(" ,ISNULL(a.OutCount,0) as OutCount,a.ProductID,g.TypeName as ColorName ");
sql.AppendLine("FROM officedba.StorageProduct a ");
sql.AppendLine("left join officedba.StorageInfo b on a.StorageID=b.ID ");
sql.AppendLine("left join officedba.DeptInfo c on c.ID=a.DeptID ");
sql.AppendLine("left join officedba.ProductInfo d on d.ID=a.ProductID ");
sql.AppendLine("left join officedba.CodeUnitType e on e.ID=d.UnitID left outer join officedba.CodePublicType g on d.ColorID=g.ID ");
sql.AppendLine(" where a.CompanyCD='" + model.CompanyCD + "'");
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine(" and a.StorageID = @StorageID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID));
}
else
{
string ListID = StorageDBHelper.GetStorageIDStr(((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID, model.CompanyCD);
sql.AppendLine(" and a.StorageID in(" + ListID + ")");
}
if (!string.IsNullOrEmpty(model.ProductCount))
{
sql.AppendLine(" and a.ProductCount >= @ProductCount ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductCount", model.ProductCount));
}
if (!string.IsNullOrEmpty(ProductCount1))
{
sql.AppendLine(" and a.ProductCount <= @ProductCount1 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductCount1", ProductCount1));
}
if (!string.IsNullOrEmpty(pdtModel.ProdNo))
{
sql.AppendLine(" and d.ProdNo=@ProdNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", pdtModel.ProdNo));
}
if (!string.IsNullOrEmpty(pdtModel.ColorID))
{
sql.AppendLine(" and d.ColorID =@ColorID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", pdtModel.ColorID));
}
if (!string.IsNullOrEmpty(pdtModel.TypeID))
{
sql.AppendLine(" and d.TypeID =@TypeID");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", pdtModel.TypeID));
}
if (!string.IsNullOrEmpty(pdtModel.ProductName))
{
sql.AppendLine(" and d.ProductName like '%' + @ProductName + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", pdtModel.ProductName));
}
if (!string.IsNullOrEmpty(pdtModel.BarCode))
{
sql.AppendLine(" and d.BarCode like '%' + @BarCode + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", pdtModel.BarCode));
}
if (!string.IsNullOrEmpty(pdtModel.Manufacturer))
{
sql.AppendLine(" and d.Manufacturer like '%' + @Manufacturer + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", pdtModel.Manufacturer));
}
if (!string.IsNullOrEmpty(pdtModel.Specification))
{
sql.AppendLine(" and d.Specification like '%' + @Specification + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", pdtModel.Specification));
}
if (!string.IsNullOrEmpty(pdtModel.FromAddr))
{
sql.AppendLine(" and d.FromAddr like '%' + @FromAddr + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", pdtModel.FromAddr));
}
if (!string.IsNullOrEmpty(pdtModel.Material))
{
sql.AppendLine(" and d.Material=@Material + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", pdtModel.Material));
}
////过滤单据：显示当前用户拥有权限查看的单据
//int empid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
//sql.AppendLine(" and ( charindex('," + empid + ",' , ','+b.CanViewUser+',')>0 or b.StorageAdmin=" + empid + " OR b.CanViewUser='' OR b.CanViewUser is null) ");
//扩展属性
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
sql.AppendLine(" and d.ExtField" + EFIndex + " like @EFDesc ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%"));
}
if (BatchNo != "0")
{
if (BatchNo == "未设置批次")
{
sql.AppendLine(" and (a.BatchNo is null or a.BatchNo='') ");
}
else
{
sql.AppendLine(" and a.BatchNo=@BatchNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo));
}
}
string ResulSQL = "select sum(isnull(StoreCount,0)) as StoreCount,sum(isnull(ProductCount,0)) as ProductCount from ( select a.*,ProductCount/ExRate as StoreCount from ( select a.*,isnull(h.ExRate,1) as ExRate,isnull(j.CodeName,'') as CodeName from (" + sql.ToString() + ") a left outer join officedba.ProductInfo g on a.ProductID=g.ID left outer join Officedba.UnitGroup p on (g.GroupUnitNo=p.GroupUnitNo and p.CompanyCD='" + model.CompanyCD + "') LEFT OUTER JOIN Officedba.UnitGroupDetail h ON (p.GroupUnitNo=h.GroupUnitNo and h.CompanyCD='" + model.CompanyCD + "' and g.StockUnitID=h.UnitID) left outer join officedba.CodeUnitType j on h.UnitID=j.ID ) a ) a ";
comm.CommandText = ResulSQL;
DataTable dt = SqlHelper.ExecuteSearch(comm);
if (dt.Rows.Count > 0)
{
rev = dt.Rows[0]["StoreCount"].ToString() + "|" + dt.Rows[0]["ProductCount"].ToString();
}
return rev;
}
/// <summary>
/// 现有存量汇总
/// </summary>
/// <param name="model"></param>
/// <param name="pdtModel"></param>
/// <param name="ProductCount1"></param>
/// <param name="EFIndex"></param>
/// <param name="EFDesc"></param>
/// <param name="orderby"></param>
/// <param name="BatchNo"></param>
/// <returns></returns>
public static string GetSumStorageInfo(StorageProductModel model, XBase.Model.Office.SupplyChain.ProductInfoModel pdtModel, string ProductCount1, string EFIndex, string EFDesc, string orderby, string BatchNo)
{
return StorageSearchDBHelper.GetSumStorageInfo(model, pdtModel, ProductCount1, EFIndex, EFDesc, orderby, BatchNo);
}
public static DataTable GetProductStorageTableBycondition(StorageProductModel model, XBase.Model.Office.SupplyChain.ProductInfoModel pdtModel, string ProductCount1, string EFIndex, string EFDesc, string orderby, string BatchNo)
{
return StorageSearchDBHelper.GetProductStorageTableBycondition(model, pdtModel, ProductCount1, EFIndex, EFDesc, orderby,BatchNo);
}
/// <summary>
/// 查询库存报损单
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetProductStorageTableBycondition(StorageProductModel model, XBase.Model.Office.SupplyChain.ProductInfoModel pdtModel, string ProductCount1, string EFIndex, string EFDesc, int pageIndex, int pageCount, string ord,string BatchNo, ref int TotalCount)
{
return StorageSearchDBHelper.GetProductStorageTableBycondition(model, pdtModel, ProductCount1, EFIndex, EFDesc, pageIndex, pageCount, ord,BatchNo, ref TotalCount);
}
/// <summary>
/// 更新工艺档案记录
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool UpdateStorageProduct(StorageProductModel model, string loginUserID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageProduct SET");
sql.AppendLine(" StorageNo = @StorageNo,");
sql.AppendLine(" StorageID = @StorageID,");
sql.AppendLine(" ProductID = @ProductID,");
//sql.AppendLine(" CostPrice = @CostPrice,");
sql.AppendLine(" ProductCount = @ProductCount,");
//sql.AppendLine(" LockCount = @LockCount,");
sql.AppendLine(" Remark = @Remark,");
sql.AppendLine(" ModifiedDate = getdate(),");
sql.AppendLine(" ModifiedUserID = '" + loginUserID + "' ");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlParameter[] param = new SqlParameter[9];
param[0] = SqlHelper.GetParameter("@StorageNo", model.StorageNo);
param[1] = SqlHelper.GetParameter("@StorageID", model.StorageID);
param[2] = SqlHelper.GetParameter("@ProductID", model.ProductID);
//param[3] = SqlHelper.GetParameter("@CostPrice", model.CostPrice);
param[3] = SqlHelper.GetParameter("@ProductCount", model.ProductCount);
// param[5] = SqlHelper.GetParameter("@LockCount", model.LockCount);
param[4] = SqlHelper.GetParameter("@Remark", model.Remark);
param[5] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
param[6] = SqlHelper.GetParameter("@ID", model.ID);
SqlHelper.ExecuteTransSql(sql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
/// <summary>
/// 更新仓库物品信息
/// </summary>
/// <param name="model"></param>
/// <param name="CompanyCD"></param>
/// <returns></returns>
public static bool UpdateStorageProduct(StorageProductModel model)
{
string loginUserID = "admin123";
return StorageProductDBHelper.UpdateStorageProduct(model, loginUserID);
}