public static bool InsertSubStorageIn(SubStorageInModel model, string DetailProductID, string DetailSendCount, string DetailUsedUnitID, string DetailUsedUnitCount, string DetailUsedPrice, string DetailExRate, string DetailBatchNo, string length, out string ID, Hashtable htExtAttr) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; ArrayList listADD = new ArrayList(); bool result = false; ID = "0"; #region 门店库存入库单添加SQL语句 StringBuilder sqlArrive = new StringBuilder(); sqlArrive.AppendLine("INSERT INTO officedba.SubStorageIn"); sqlArrive.AppendLine("(CompanyCD,DeptID,InNo,Title,InType,Remark,Creator,CreateDate,"); sqlArrive.AppendLine("BillStatus,Confirmor,ConfirmDate,ModifiedDate,ModifiedUserID)"); sqlArrive.AppendLine("VALUES (@CompanyCD,@DeptID,@InNo,@Title,@InType,@Remark,@Creator,@CreateDate,"); sqlArrive.AppendLine("@BillStatus,@Confirmor,@ConfirmDate,getdate(),@ModifiedUserID)"); sqlArrive.AppendLine("set @ID=@@IDENTITY"); SqlCommand comm = new SqlCommand(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@InNo", model.InNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title)); comm.Parameters.Add(SqlHelper.GetParameter("@InType", 1)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator)); comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate == null ? SqlDateTime.Null : SqlDateTime.Parse(model.CreateDate.ToString()))); comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus)); comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor)); comm.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate == null ? SqlDateTime.Null : SqlDateTime.Parse(model.ConfirmDate.ToString()))); comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID)); comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int)); comm.CommandText = sqlArrive.ToString(); listADD.Add(comm); // 更新扩展属性 SqlCommand commExtAttr = UpdateExtAttr(model, htExtAttr); if (commExtAttr != null) { listADD.Add(commExtAttr); } #endregion try { #region 门店库存产品信息 int lengs = Convert.ToInt32(length); if (lengs > 0) { string[] ProductID = DetailProductID.Split(','); string[] SendCount = DetailSendCount.Split(','); string[] UsedUnitID = DetailUsedUnitID.Split(','); string[] UsedUnitCount = DetailUsedUnitCount.Split(','); string[] UsedPrice = DetailUsedPrice.Split(','); string[] ExRate = DetailExRate.Split(','); string[] BatchNo = DetailBatchNo.Split(','); for (int i = 0; i < lengs; i++) { System.Text.StringBuilder cmdsql = new System.Text.StringBuilder(); SqlCommand comms = new SqlCommand(); cmdsql.AppendLine("INSERT INTO officedba.SubStorageInDetail"); cmdsql.AppendLine("(CompanyCD,"); cmdsql.AppendLine("DeptID,"); cmdsql.AppendLine("InNo,"); cmdsql.AppendLine("SortNo,"); cmdsql.AppendLine("ProductID,"); if (userInfo.IsMoreUnit) { cmdsql.AppendLine("UsedUnitID,"); cmdsql.AppendLine("UsedUnitCount,"); cmdsql.AppendLine("ExRate,"); } cmdsql.AppendLine("BatchNo,"); cmdsql.AppendLine("SendCount)"); cmdsql.AppendLine(" Values(@CompanyCD"); cmdsql.AppendLine(" ,@DeptID"); cmdsql.AppendLine(" ,@InNo"); cmdsql.AppendLine(" ,@SortNo"); cmdsql.AppendLine(" ,@ProductID"); if (userInfo.IsMoreUnit) { cmdsql.AppendLine(" ,@UsedUnitID"); cmdsql.AppendLine(" ,@UsedUnitCount"); cmdsql.AppendLine(" ,@ExRate"); } cmdsql.AppendLine(" ,@BatchNo"); cmdsql.AppendLine(" ,@SendCount)"); comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comms.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comms.Parameters.Add(SqlHelper.GetParameter("@InNo", model.InNo)); comms.Parameters.Add(SqlHelper.GetParameter("@SortNo", i + 1)); comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID[i].ToString())); if (userInfo.IsMoreUnit) { comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", UsedUnitID[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", UsedUnitCount[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", ExRate[i].ToString())); } comms.Parameters.Add(SqlHelper.GetParameter("@BatchNo", BatchNo[i])); comms.Parameters.Add(SqlHelper.GetParameter("@SendCount", SendCount[i].ToString())); comms.CommandText = cmdsql.ToString(); listADD.Add(comms); } } #endregion if (SqlHelper.ExecuteTransWithArrayList(listADD)) { ID = comm.Parameters["@ID"].Value.ToString(); result = true; } return result; } catch (Exception ex) { throw ex; } }
public static bool UpdateSubStorageIn(SubStorageInModel model, string DetailProductID, string DetailSendCount, string DetailUsedUnitID, string DetailUsedUnitCount, string DetailUsedPrice, string DetailExRate, string DetailBatchNo, string length, string no, Hashtable htExtAttr) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; if (model.ID <= 0) { return false; } ArrayList listADD = new ArrayList(); bool result = false; #region 修改门店库存入库单 StringBuilder sqlArrive = new StringBuilder(); sqlArrive.AppendLine("Update Officedba.SubStorageIn set CompanyCD=@CompanyCD,"); sqlArrive.AppendLine("DeptID=@DeptID,InNo=@InNo,Title=@Title,InType=@InType,Remark=@Remark,"); sqlArrive.AppendLine("BillStatus=@BillStatus,Confirmor=@Confirmor,ConfirmDate=@ConfirmDate,"); sqlArrive.AppendLine("ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID where CompanyCD=@CompanyCD and InNo=@InNo and ID=@ID"); SqlCommand comm = new SqlCommand(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comm.Parameters.Add(SqlHelper.GetParameter("@Title", model.Title)); comm.Parameters.Add(SqlHelper.GetParameter("@InType", 1)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator)); comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate == null ? SqlDateTime.Null : SqlDateTime.Parse(model.CreateDate.ToString()))); comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus)); comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor)); comm.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate == null ? SqlDateTime.Null : SqlDateTime.Parse(model.ConfirmDate.ToString()))); comm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID)); comm.Parameters.Add(SqlHelper.GetParameter("@InNo", model.InNo)); comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); comm.CommandText = sqlArrive.ToString(); listADD.Add(comm); // 更新扩展属性 SqlCommand commExtAttr = UpdateExtAttr(model, htExtAttr); if (commExtAttr != null) { listADD.Add(commExtAttr); } #endregion #region 删除门店库存产品信息 System.Text.StringBuilder cmdddetail = new System.Text.StringBuilder(); cmdddetail.AppendLine("DELETE FROM officedba.SubStorageInDetail WHERE CompanyCD=@CompanyCD and InNo=@InNo"); SqlCommand comn = new SqlCommand(); comn.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comn.Parameters.Add(SqlHelper.GetParameter("@InNo", model.InNo)); comn.CommandText = cmdddetail.ToString(); listADD.Add(comn); #endregion try { #region 重新插入门店库存产品信息 int lengs = Convert.ToInt32(length); if (lengs > 0) { string[] ProductID = DetailProductID.Split(','); string[] SendCount = DetailSendCount.Split(','); string[] UsedUnitID = DetailUsedUnitID.Split(','); string[] UsedUnitCount = DetailUsedUnitCount.Split(','); string[] UsedPrice = DetailUsedPrice.Split(','); string[] ExRate = DetailExRate.Split(','); string[] BatchNo = DetailBatchNo.Split(','); for (int i = 0; i < lengs; i++) { System.Text.StringBuilder cmdsql = new System.Text.StringBuilder(); SqlCommand comms = new SqlCommand(); cmdsql.AppendLine("INSERT INTO officedba.SubStorageInDetail"); cmdsql.AppendLine("(CompanyCD,"); cmdsql.AppendLine("DeptID,"); cmdsql.AppendLine("InNo,"); cmdsql.AppendLine("SortNo,"); cmdsql.AppendLine("ProductID,"); if (userInfo.IsMoreUnit) { cmdsql.AppendLine("UsedUnitID,"); cmdsql.AppendLine("UsedUnitCount,"); cmdsql.AppendLine("ExRate,"); } cmdsql.AppendLine("BatchNo,"); cmdsql.AppendLine("SendCount)"); cmdsql.AppendLine(" Values(@CompanyCD"); cmdsql.AppendLine(" ,@DeptID"); cmdsql.AppendLine(" ,@InNo"); cmdsql.AppendLine(" ,@SortNo"); cmdsql.AppendLine(" ,@ProductID"); if (userInfo.IsMoreUnit) { cmdsql.AppendLine(" ,@UsedUnitID"); cmdsql.AppendLine(" ,@UsedUnitCount"); cmdsql.AppendLine(" ,@ExRate"); } cmdsql.AppendLine(" ,@BatchNo"); cmdsql.AppendLine(" ,@SendCount)"); comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comms.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID)); comms.Parameters.Add(SqlHelper.GetParameter("@InNo", model.InNo)); comms.Parameters.Add(SqlHelper.GetParameter("@SortNo", i + 1)); comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID[i].ToString())); if (userInfo.IsMoreUnit) { comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", UsedUnitID[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", UsedUnitCount[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", ExRate[i].ToString())); } comms.Parameters.Add(SqlHelper.GetParameter("@BatchNo", BatchNo[i])); comms.Parameters.Add(SqlHelper.GetParameter("@SendCount", SendCount[i].ToString())); comms.CommandText = cmdsql.ToString(); listADD.Add(comms); } } #endregion if (SqlHelper.ExecuteTransWithArrayList(listADD)) { result = true; } return result; } catch (Exception ex) { throw ex; } }
public static bool ConfirmSubStorageIn(SubStorageInModel Model, string DetailProductID, string DetailSendCount, string DetailUnitPrice, string DetailBatchNo, string length) { #region 确认门店库存单中信息 ArrayList listADD = new ArrayList(); bool result = false; StringBuilder strSql = new StringBuilder(); strSql.AppendLine("update officedba.SubStorageIn set "); strSql.AppendLine(" Confirmor=@Confirmor"); strSql.AppendLine(" ,BillStatus=5"); strSql.AppendLine(" ,ConfirmDate=getdate()"); strSql.AppendLine(" where"); strSql.AppendLine(" CompanyCD=@CompanyCD"); strSql.AppendLine(" and ID=@ID"); SqlCommand comm = new SqlCommand(); comm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", Model.Confirmor)); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", Model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@ID", Model.ID)); comm.CommandText = strSql.ToString(); listADD.Add(comm); #endregion #region 确认时插入物流配送中的分店存量表 try { int lengs = Convert.ToInt32(length); int id = 0; decimal count = 0m; if (lengs > 0) { string[] ProductID = DetailProductID.Split(','); string[] SendCount = DetailSendCount.Split(','); string[] UnitPrice = DetailUnitPrice.Split(','); string[] BatchNo = DetailBatchNo.Split(','); for (int i = 0; i < lengs; i++) { #region 添加门店库存流水帐 SubStorageAccountModel aModel = new SubStorageAccountModel(); aModel.BatchNo = BatchNo[i]; aModel.BillNo = Model.InNo; aModel.BillType = 1; aModel.CompanyCD = Model.CompanyCD; aModel.Creator = Model.Confirmor; aModel.DeptID = Model.DeptID; aModel.HappenDate = DateTime.Now; if (int.TryParse(ProductID[i], out id)) { aModel.ProductID = id; } if (decimal.TryParse(UnitPrice[i], out count)) { aModel.Price = count; } if (decimal.TryParse(SendCount[i], out count)) { aModel.HappenCount = count; } aModel.PageUrl = Model.Remark; listADD.Add(XBase.Data.Office.SubStoreManager.SubStorageAccountDBHelper.GetCountAndInsertCommand(aModel)); #endregion // 更新分店存量表 listADD.Add(StorageProductQueryDBHelper.UpdateProductCount(Model.CompanyCD , ProductID[i], Model.DeptID.ToString(), BatchNo[i], count)); } } if (SqlHelper.ExecuteTransWithArrayList(listADD)) { //ID = comm.Parameters["@ID"].Value.ToString(); result = true; } return result; } catch (Exception ex) { throw ex; } #endregion }
/// <summary> /// 扩展属性更新命令 /// </summary> /// <param name="model">门店库存入库单</param> /// <param name="htExtAttr">扩展属性</param> /// <returns></returns> public static SqlCommand UpdateExtAttr(SubStorageInModel model, Hashtable htExtAttr) { SqlCommand sqlcomm = new SqlCommand(); if (htExtAttr == null || htExtAttr.Count < 1) {// 没有属性需要修改 return null; } StringBuilder sb = new StringBuilder(" UPDATE officedba.SubStorageIn SET "); foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例 { sb.AppendFormat(" {0}=@{0},", de.Key.ToString()); sqlcomm.Parameters.Add(SqlHelper.GetParameter(String.Format("@{0}", de.Key.ToString()), de.Value)); } string strSql = sb.ToString(); strSql = strSql.TrimEnd(','); strSql += " WHERE CompanyCD = @CompanyCD AND InNo = @InNo "; sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); sqlcomm.Parameters.Add(SqlHelper.GetParameter("@InNo", model.InNo)); sqlcomm.CommandText = strSql; return sqlcomm; }