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