/// <summary>
/// 返回 SQLCommand
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static SqlCommand InsertStorageAccountBySqlCommand(StorageAccountModel model)
{
#region 库存流水账表SQL语句
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.StorageAccount");
sql.AppendLine(" (CompanyCD");
sql.AppendLine(" ,BillType");
sql.AppendLine(" ,ProductID");
sql.AppendLine(" ,StorageID");
sql.AppendLine(" ,BatchNo");
sql.AppendLine(" ,BillNo");
sql.AppendLine(" ,HappenDate");
sql.AppendLine(" ,HappenCount");
sql.AppendLine(" ,ProductCount");
sql.AppendLine(" ,Creator");
sql.AppendLine(" ,Price");
sql.AppendLine(" ,PageUrl)");
sql.AppendLine(" VALUES");
sql.AppendLine(" (@CompanyCD");
sql.AppendLine(" ,@BillType");
sql.AppendLine(" ,@ProductID");
sql.AppendLine(" ,@StorageID");
sql.AppendLine(" ,@BatchNo");
sql.AppendLine(" ,@BillNo");
sql.AppendLine(" ,getdate()");
sql.AppendLine(" ,@HappenCount");
sql.AppendLine(" ,@ProductCount");
sql.AppendLine(" ,@Creator");
sql.AppendLine(" ,@Price");
sql.AppendLine(" ,@PageUrl)");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@BillType", model.BillType));
comm.Parameters.Add(SqlHelper.GetParameter("@ProductID", model.ProductID));
comm.Parameters.Add(SqlHelper.GetParameter("@StorageID", model.StorageID));
comm.Parameters.Add(SqlHelper.GetParameter("@BatchNo", model.BatchNo));
comm.Parameters.Add(SqlHelper.GetParameter("@BillNo", model.BillNo));
comm.Parameters.Add(SqlHelper.GetParameter("@HappenCount", model.HappenCount));
comm.Parameters.Add(SqlHelper.GetParameter("@ProductCount", model.ProductCount));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@Price", model.Price));
comm.Parameters.Add(SqlHelper.GetParameter("@PageUrl", model.PageUrl));
return comm;
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool InsertStorageAccount(StorageAccountModel model, string loginUserID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.StorageAccount");
sql.AppendLine(" (CompanyCD");
sql.AppendLine(" ,BillType");
sql.AppendLine(" ,ProductID");
sql.AppendLine(" ,StorageID");
sql.AppendLine(" ,BatchNo");
sql.AppendLine(" ,BillNo");
sql.AppendLine(" ,HappenDate");
sql.AppendLine(" ,HappenCount");
sql.AppendLine(" ,ProductCount");
sql.AppendLine(" ,Creator");
sql.AppendLine(" ,Price");
sql.AppendLine(" ,PageUrl)");
sql.AppendLine(" VALUES");
sql.AppendLine(" (@CompanyCD");
sql.AppendLine(" ,@BillType");
sql.AppendLine(" ,@ProductID");
sql.AppendLine(" ,@StorageID");
sql.AppendLine(" ,@BatchNo");
sql.AppendLine(" ,@BillNo");
sql.AppendLine(" ,getdate()");
sql.AppendLine(" ,@HappenCount");
sql.AppendLine(" ,@ProductCount");
sql.AppendLine(" ,@Creator");
sql.AppendLine(" ,@Price");
sql.AppendLine(" ,@PageUrl)");
//设置参数
SqlParameter[] param = new SqlParameter[11];
param[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD);
param[1] = SqlHelper.GetParameter("@BillType", model.BillType);
param[2] = SqlHelper.GetParameter("@ProductID", model.ProductID);
param[3] = SqlHelper.GetParameter("@StorageID", model.StorageID);
param[4] = SqlHelper.GetParameter("@BatchNo", model.BatchNo);
param[5] = SqlHelper.GetParameter("@BillNo", model.BillNo);
param[6] = SqlHelper.GetParameter("@HappenCount", model.HappenCount);
param[7] = SqlHelper.GetParameter("@ProductCount", model.ProductCount);
param[8] = SqlHelper.GetParameter("@Creator", model.Creator);
param[9] = SqlHelper.GetParameter("@Price", model.Price);
param[10] = SqlHelper.GetParameter("@PageUrl", model.PageUrl);
SqlHelper.ExecuteTransSql(sql.ToString(), param);
return SqlHelper.Result.OprateCount > 0 ? true : false;
}
/// <summary>
/// 插入库存流水账
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertStorageAccount(StorageAccountModel model)
{
bool Rev = false;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into officedba.StorageAccount(");
strSql.Append("CompanyCD,BillType,ProductID,StorageID,BillNo,HappenDate,HappenCount,ProductCount,Creator,Price,PageUrl,ReMark)");
strSql.Append(" values (");
strSql.Append("@CompanyCD,@BillType,@ProductID,@StorageID,@BillNo,@HappenDate,@HappenCount,@ProductCount,@Creator,@Price,@PageUrl,@ReMark)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@BillType", SqlDbType.Int,4),
new SqlParameter("@ProductID", SqlDbType.Int,4),
new SqlParameter("@StorageID", SqlDbType.Int,4),
new SqlParameter("@BillNo", SqlDbType.VarChar,50),
new SqlParameter("@HappenDate", SqlDbType.DateTime),
new SqlParameter("@HappenCount", SqlDbType.Decimal,13),
new SqlParameter("@ProductCount", SqlDbType.Decimal,13),
new SqlParameter("@Creator", SqlDbType.Int,4),
new SqlParameter("@Price", SqlDbType.Decimal,9),
new SqlParameter("@PageUrl", SqlDbType.VarChar,500),
new SqlParameter("@ReMark", SqlDbType.VarChar,500)};
parameters[0].Value = model.CompanyCD;
parameters[1].Value = model.BillType;
parameters[2].Value = model.ProductID;
parameters[3].Value = model.StorageID;
parameters[4].Value = model.BillNo;
parameters[5].Value = model.HappenDate;
parameters[6].Value = model.HappenCount;
parameters[7].Value = model.ProductCount;
parameters[8].Value = model.Creator;
parameters[9].Value = model.Price;
parameters[10].Value = model.PageUrl;
parameters[11].Value = model.ReMark;
Rev = SqlHelper.ExecuteTransSql(strSql.ToString(), parameters) > 0 ? true : false;
return Rev;
}
public static bool ConfirmBill(StorageInitailModel model)
{
ArrayList lstConfirm = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageInitail SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" confirmDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
lstConfirm.Add(comm);
List<StorageInitailDetailModel> modelList = new List<StorageInitailDetailModel>();
string sqlSele = "select a.ID,a.CompanyCD, a.ProductID,b.StorageID,a.BatchNo,a.InNo BillNo,a.UnitPrice Price,Convert(varchar(10),b.EnterDate,23) HappenDate," +
" a.Remark, a.ProductCount from officedba.StorageInitailDetail a left join" +
" officedba.StorageInitail b on a.InNo=b.InNo and a.CompanyCD = b.CompanyCD where a.CompanyCD='" + model.CompanyCD + "' and a.InNo=(select InNo from officedba.StorageInitail where ID=" + model.ID + ")";
DataTable dt = SqlHelper.ExecuteSql(sqlSele);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StorageInitailDetailModel modelDetail = new StorageInitailDetailModel();
StorageAccountModel StorageAccountM = new StorageAccountModel();
if (dt.Rows[i]["ProductID"].ToString() != "")
{
modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString();
StorageAccountM.ProductID = Convert.ToInt32(dt.Rows[i]["ProductID"].ToString());
}
if (dt.Rows[i]["ProductCount"].ToString() != "")
{
modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString();
StorageAccountM.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
StorageAccountM.ProductCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
}
if (dt.Rows[i]["StorageID"].ToString() != "")
{
model.StorageID = dt.Rows[i]["StorageID"].ToString();
StorageAccountM.StorageID = Convert.ToInt32(dt.Rows[i]["StorageID"].ToString());
}
//插入流水账表
StorageAccountM.CompanyCD = dt.Rows[i]["CompanyCD"].ToString();
StorageAccountM.BillType = 1;
if (dt.Rows[i]["BatchNo"].ToString() != "")
{
modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString();
StorageAccountM.BatchNo = dt.Rows[i]["BatchNo"].ToString();
}
modelList.Add(modelDetail);
StorageAccountM.BillNo = dt.Rows[i]["BillNo"].ToString();
StorageAccountM.Price = Convert.ToDecimal(dt.Rows[i]["Price"].ToString());
StorageAccountM.HappenDate = Convert.ToDateTime(dt.Rows[i]["HappenDate"].ToString());
StorageAccountM.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
StorageAccountM.PageUrl = "../Office/StorageManager/StorageInitailAdd.aspx";
StorageAccountM.ReMark = dt.Rows[i]["Remark"].ToString();
SqlCommand commSA = new SqlCommand();
commSA = StorageAccountDBHelper.InsertStorageAccountCommand(StorageAccountM, "0");
lstConfirm.Add(commSA);
}
}
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commPD = new SqlCommand();
if (Exists(modelList[i].BatchNo,model.StorageID, modelList[i].ProductID, model.CompanyCD))
{
commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, model.StorageID, modelList[i].ProductCount, model, true);
}
else
{
commPD = InsertStorageProduct(modelList[i].BatchNo,modelList[i].ProductID, model.StorageID, modelList[i].ProductCount, model.CompanyCD);
}
lstConfirm.Add(commPD);
}
}
return SqlHelper.ExecuteTransWithArrayList(lstConfirm);
}
public static bool ConfirmBill(StorageOutSellModel model,out string retstrval)
{
//判断源单是无来源还是有来源，无来源则不需要更新受订量
string sqlFromType = "select a.FromType,a.CustID,b.TotalPrice from officedba.SellSend a"
+ " inner join officedba.StorageOutSell b on b.FromBillID=a.ID and b.ID=" + model.ID;
DataTable dtFrom = SqlHelper.ExecuteSql(sqlFromType);
string FromBillFromType = dtFrom.Rows[0]["FromType"].ToString();//得到的是“0”或“1”
ArrayList lstConfirm = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageOutSell SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" confirmDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
lstConfirm.Add(comm);
List<StorageOutSellDetailModel> modelList = new List<StorageOutSellDetailModel>();
string sqlSele = "select a.ProductID,a.UnitPrice,a.CompanyCD,a.OutNo,a.StorageID,a.BatchNo,a.UsedUnitCount,a.FromLineNo,a.ProductCount,b.StorageID as DefaultStorageID from officedba.StorageOutSellDetail a"
+" inner join officedba.ProductInfo b on b.ID=a.ProductID"
+ " where a.CompanyCD='" + model.CompanyCD + "'"
+ "and a.OutNo=(select OutNo from officedba.StorageOutSell where ID=" + model.ID + ")";
DataTable dt = SqlHelper.ExecuteSql(sqlSele);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StorageOutSellDetailModel modelDetail = new StorageOutSellDetailModel();
if (dt.Rows[i]["ProductID"].ToString() != "")
{
modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString();
}
if (dt.Rows[i]["StorageID"].ToString() != "")
{
modelDetail.StorageID = dt.Rows[i]["StorageID"].ToString();
}
if (dt.Rows[i]["ProductCount"].ToString() != "")
{
modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString();
}
if (dt.Rows[i]["FromLineNo"].ToString() != "")
{
modelDetail.FromLineNo = dt.Rows[i]["FromLineNo"].ToString();
}
if (dt.Rows[i]["DefaultStorageID"].ToString() != "")
{
modelDetail.DefaultStorageID = dt.Rows[i]["DefaultStorageID"].ToString();
}
modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString();
if (dt.Rows[i]["UsedUnitCount"].ToString() == "")
modelDetail.UsedUnitCount = dt.Rows[i]["ProductCount"].ToString();
else
modelDetail.UsedUnitCount = dt.Rows[i]["UsedUnitCount"].ToString();
modelList.Add(modelDetail);
#region 操作库存流水账
StorageAccountModel AccountM_ = new StorageAccountModel();
AccountM_.BatchNo = dt.Rows[i]["BatchNo"].ToString();
AccountM_.BillNo = dt.Rows[i]["OutNo"].ToString();
AccountM_.BillType=7;
AccountM_.CompanyCD = dt.Rows[i]["CompanyCD"].ToString();
AccountM_.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
AccountM_.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
AccountM_.HappenDate = System.DateTime.Now;
AccountM_.PageUrl="../Office/StorageManager/StorageOutSellAdd.aspx";
AccountM_.Price=Convert.ToDecimal(dt.Rows[i]["UnitPrice"].ToString());
AccountM_.ProductCount= Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
AccountM_.ProductID= Convert.ToInt32(dt.Rows[i]["ProductID"].ToString());
AccountM_.StorageID=Convert.ToInt32(dt.Rows[i]["StorageID"].ToString());
SqlCommand AccountCom_=StorageAccountDBHelper.InsertStorageAccountCommand(AccountM_,"1");
lstConfirm.Add(AccountCom_);
#endregion
}
}
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
StringBuilder strAddSSDetail = new StringBuilder();//增加销售发货单明细中的已出库数量
strAddSSDetail.AppendLine("update officedba.SellSendDetail set ");
strAddSSDetail.AppendLine(" OutCount =ISNULL(OutCount,0)+@ReOutCount where ");
strAddSSDetail.AppendLine(" SendNo=(select SendNo from officedba.SellSend where ID=(select FromBillID from officedba.StorageOutSell where ID=" + model.ID + "))");
strAddSSDetail.AppendLine(" and SortNo=@SortNo");
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commReSSD = new SqlCommand();
commReSSD.CommandText = strAddSSDetail.ToString();
commReSSD.Parameters.Add(SqlHelper.GetParameterFromString("@ReOutCount", modelList[i].UsedUnitCount));//回写增加的数量(多单位启用时用数量，没启用时用基本数量)
commReSSD.Parameters.Add(SqlHelper.GetParameterFromString("@SortNo", modelList[i].FromLineNo));
lstConfirm.Add(commReSSD);//循环加入数组（把SellSendDetail已经入库数量增加）
SqlCommand commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, modelList[i].StorageID, modelList[i].ProductCount, model, false);
lstConfirm.Add(commPD);
if (FromBillFromType == "1")
{
//更新主放仓库的受订量
SqlCommand commOrder = new SqlCommand();
commOrder = updateOrderCount(modelList[i].BatchNo, modelList[i].ProductID, modelList[i].DefaultStorageID, modelList[i].ProductCount, model.CompanyCD);
lstConfirm.Add(commOrder);
}
}
}
bool retval=SqlHelper.ExecuteTransWithArrayList(lstConfirm);
if (retval)
{
DataTable dtCurrtype = XBase.Data.Office.FinanceManager.CurrTypeSettingDBHelper.GetMasterCurrency(model.CompanyCD);
string IsVoucher=((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsVoucher?"1":"0";
string IsApply=((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsApply?"1":"0";
int custid = 0;
if (dtFrom.Rows[0]["CustID"].ToString().Trim() != "")
custid = Convert.ToInt32(dtFrom.Rows[0]["CustID"].ToString());
bool VocherFlag=XBase.Data.Office.FinanceManager.AutoVoucherDBHelper.AutoVoucherInsert(7, model.CompanyCD, IsVoucher,IsApply,Convert.ToDecimal(dtFrom.Rows[0]["TotalPrice"].ToString()), "officedba.StorageOutSell," + model.ID, dtCurrtype.Rows[0]["ID"].ToString()+","+dtCurrtype.Rows[0]["ExchangeRate"].ToString(),custid, out retstrval);
if (VocherFlag) retstrval = "确认成功！";
else retstrval = "确认成功！" + retstrval;
}
else retstrval = "";
return retval;
}
public static bool ConfirmBill(StorageLossModel model)
{
ArrayList lstConfirm = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageLoss SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" confirmDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
lstConfirm.Add(comm);
List<StorageLossDetailModel> modelList = new List<StorageLossDetailModel>();
string sqlSele = "select ProductID,BatchNo,LossNo,UnitPrice,CompanyCD,UsedUnitCount,ProductCount from officedba.StorageLossDetail where CompanyCD='" + model.CompanyCD + "'"
+ "and LossNo=(select LossNo from officedba.StorageLoss where ID=" + model.ID + ")";
DataTable dt = SqlHelper.ExecuteSql(sqlSele);
string sqlpri = "select StorageID from officedba.StorageLoss where ID=" + model.ID + "";
DataTable dt1 = SqlHelper.ExecuteSql(sqlpri);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StorageLossDetailModel modelDetail = new StorageLossDetailModel();
if (dt.Rows[i]["ProductID"].ToString() != "")
{
modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString();
}
if (dt.Rows[i]["ProductCount"].ToString() != "")
{
modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString();
}
modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString();
if (dt.Rows[i]["UsedUnitCount"].ToString() == "")
modelDetail.UsedUnitCount = dt.Rows[i]["ProductCount"].ToString();
else
modelDetail.UsedUnitCount = dt.Rows[i]["UsedUnitCount"].ToString();
modelList.Add(modelDetail);
#region 操作库存流水账
StorageAccountModel AccountM_ = new StorageAccountModel();
AccountM_.BatchNo = dt.Rows[i]["BatchNo"].ToString();
AccountM_.BillNo = dt.Rows[i]["LossNo"].ToString();
AccountM_.BillType = 16;
AccountM_.CompanyCD = dt.Rows[i]["CompanyCD"].ToString();
AccountM_.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
AccountM_.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
AccountM_.HappenDate = System.DateTime.Now;
AccountM_.PageUrl = "../Office/StorageManager/StorageLossAdd.aspx";
AccountM_.Price = Convert.ToDecimal(dt.Rows[i]["UnitPrice"].ToString());
AccountM_.ProductCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
AccountM_.ProductID = Convert.ToInt32(dt.Rows[i]["ProductID"].ToString());
AccountM_.StorageID = Convert.ToInt32(dt1.Rows[0]["StorageID"].ToString());
SqlCommand AccountCom_ = StorageAccountDBHelper.InsertStorageAccountCommand(AccountM_,"1");
lstConfirm.Add(AccountCom_);
#endregion
}
}
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, model.StorageID, modelList[i].ProductCount, model, false);
lstConfirm.Add(commPD);
}
}
return SqlHelper.ExecuteTransWithArrayList(lstConfirm);
}
public static string StorageCheck(StorageCheck sc)
{
#region 读取盘点单信息
StringBuilder strMainSql = new StringBuilder();
strMainSql.Append("SELECT * FROM officedba.StorageCheck WHERE ID=@ID");
SqlParameter[] MainParas = { new SqlParameter("@ID", SqlDbType.Int) };
MainParas[0].Value = sc.ID;
DataTable dtStorage = SqlHelper.ExecuteSql(strMainSql.ToString(), MainParas);
DataRow MainRow = dtStorage.Rows[0];
#endregion
#region 读取明细
StringBuilder strSql = new StringBuilder();
strSql.Append("select a.*,ISNULL(b.StandardCost,0)StandardCost from officedba.StorageCheckDetail a ");
strSql.Append("left outer join officedba.productinfo b ");
strSql.Append("on a.ProductID=b.id where a.CompanyCD=@CompanyCD and a.CheckNo=@CheckNo ");
SqlParameter[] Paras = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar),
new SqlParameter("@CheckNo",SqlDbType.VarChar)
};
Paras[0].Value = sc.CompanyCD;
Paras[1].Value = MainRow["CheckNo"];
DataTable dtDetail = SqlHelper.ExecuteSql(strSql.ToString(), Paras);
#endregion
ArrayList SqlCmdList = new ArrayList();
#region 更新盘点单 库存调整人及调整时间
StringBuilder SqlMain = new StringBuilder();
SqlMain.Append("UPDATE officedba.StorageCheck SET CheckUserID=@CheckUserID,CheckDate=@CheckDate");
SqlMain.Append(" WHERE ID=@ID ");
SqlParameter[] sPara = {
new SqlParameter("@CheckUserID",SqlDbType.Int),
new SqlParameter("@CheckDate",SqlDbType.DateTime),
new SqlParameter("@ID",SqlDbType.Int)
};
sPara[0].Value = sc.CheckUserID;
sPara[1].Value = sc.CloseDate;
sPara[2].Value = sc.ID;
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandText = SqlMain.ToString();
sqlcmd.Parameters.AddRange(sPara);
SqlCmdList.Add(sqlcmd);
#endregion
foreach (DataRow row in dtDetail.Rows)
{
StringBuilder strSubSql = new StringBuilder();
strSubSql.Append("UPDATE officedba.StorageProduct SET");
strSubSql.Append(" ProductCount=@ProductCount ");
strSubSql.Append(" WHERE CompanyCD=@CompanyCD AND ");
strSubSql.Append(" StorageID=@StorageID AND ");
strSubSql.Append(" ProductID=@ProductID");
if (row["BatchNo"].ToString() != "")
strSubSql.Append(" and BatchNo='" + row["BatchNo"].ToString().Trim() + "' ");
else
strSubSql.Append(" and BatchNo is null ");
SqlParameter[] SubParas = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar),
new SqlParameter("@StorageID",SqlDbType.Int),
new SqlParameter("@ProductID",SqlDbType.Int),
new SqlParameter("@ProductCount",SqlDbType.Decimal)
};
SubParas[0].Value = row["CompanyCD"];
SubParas[1].Value = MainRow["StorageID"];
SubParas[2].Value = row["ProductID"];
SubParas[3].Value = row["CheckCount"];
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = strSubSql.ToString();
sqlCmd.Parameters.AddRange(SubParas);
SqlCmdList.Add(sqlCmd);
#region 操作库存流水账
StorageAccountModel AccountM_ = new StorageAccountModel();
AccountM_.BatchNo = row["BatchNo"].ToString();
AccountM_.BillNo = row["CheckNo"].ToString();
AccountM_.BillType = 15;
AccountM_.CompanyCD = row["CompanyCD"].ToString();
AccountM_.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
AccountM_.HappenCount = Convert.ToDecimal(row["CheckCount"].ToString());
AccountM_.HappenDate = System.DateTime.Now;
AccountM_.PageUrl = "../Office/StorageManager/StorageCheckSave.aspx";
AccountM_.Price = Convert.ToDecimal(row["StandardCost"].ToString());
AccountM_.ProductCount = Convert.ToDecimal(row["CheckCount"].ToString());
AccountM_.ProductID = Convert.ToInt32(row["ProductID"].ToString());
AccountM_.StorageID = Convert.ToInt32(dtStorage.Rows[0]["StorageID"].ToString());
#region sql
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.StorageAccount");
sql.AppendLine(" (CompanyCD");
sql.AppendLine(" ,BillType");
sql.AppendLine(" ,ProductID");
sql.AppendLine(" ,StorageID");
sql.AppendLine(" ,BatchNo");
sql.AppendLine(" ,BillNo");
sql.AppendLine(" ,HappenDate");
// sql.AppendLine(" ,HappenCount");
sql.AppendLine(" ,ProductCount");
sql.AppendLine(" ,Creator");
sql.AppendLine(" ,Price");
sql.AppendLine(" ,PageUrl)");
sql.AppendLine(" VALUES");
sql.AppendLine(" (@CompanyCD");
sql.AppendLine(" ,@BillType");
sql.AppendLine(" ,@ProductID");
sql.AppendLine(" ,@StorageID");
sql.AppendLine(" ,@BatchNo");
sql.AppendLine(" ,@BillNo");
sql.AppendLine(" ,getdate()");
//sql.AppendLine(" ,@HappenCount");
sql.AppendLine(" ,@ProductCount");
sql.AppendLine(" ,@Creator");
sql.AppendLine(" ,@Price");
sql.AppendLine(" ,@PageUrl)");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", AccountM_.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillType", AccountM_.BillType.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", AccountM_.ProductID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", AccountM_.StorageID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", AccountM_.BatchNo));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillNo", AccountM_.BillNo));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@HappenCount", model.HappenCount.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductCount", AccountM_.ProductCount.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", AccountM_.Creator.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Price", AccountM_.Price.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PageUrl", AccountM_.PageUrl));
#endregion
//SqlCommand AccountCom_ = StorageAccountDBHelper.InsertStorageAccountCommand(AccountM_,"1");
SqlCmdList.Add(comm);
#endregion
}
bool result = SqlHelper.ExecuteTransWithArrayList(SqlCmdList);
if (result)
return "0";
else
return "1";
}
public static bool ConfirmBill(StorageInProcessModel model)
{
ArrayList lstConfirm = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageInProcess SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" confirmDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
lstConfirm.Add(comm);
List<StorageInProcessDetailModel> modelList = new List<StorageInProcessDetailModel>();
string sqlSele = "select a.CompanyCD,a.ProductID,a.StorageID,a.BatchNo,a.InNo,a.UnitPrice,a.UsedUnitCount,"
+ " Convert(varchar(10),c.EnterDate,23) HappenDate,a.ProductCount,a.Remark,a.ProductCount,a.FromLineNo,b.StorageID as DefaultStorageID"
+ " from officedba.StorageInProcessDetail a"
+ " left join officedba.ProductInfo b on b.ID=a.ProductID"
+ " left join officedba.StorageInProcess c on c.InNo=a.InNo and a.CompanyCD = c.CompanyCD "
+ " where a.CompanyCD='" + model.CompanyCD + "' and a.InNo=(select InNo from officedba.StorageInProcess where ID=" + model.ID + ")";
DataTable dt = SqlHelper.ExecuteSql(sqlSele);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StorageInProcessDetailModel modelDetail = new StorageInProcessDetailModel();
StorageAccountModel StorageAccountM = new StorageAccountModel();
StorageAccountM.CompanyCD = dt.Rows[i]["CompanyCD"].ToString();
StorageAccountM.BillType = 4;
if (dt.Rows[i]["BatchNo"].ToString() != "")
{
modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString();
StorageAccountM.BatchNo = dt.Rows[i]["BatchNo"].ToString();
}
StorageAccountM.BillNo = dt.Rows[i]["InNo"].ToString();
StorageAccountM.Price = Convert.ToDecimal(dt.Rows[i]["UnitPrice"].ToString());
StorageAccountM.HappenDate = Convert.ToDateTime(dt.Rows[i]["HappenDate"].ToString());
StorageAccountM.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
StorageAccountM.PageUrl = "../Office/StorageManager/StorageInProcessAdd.aspx";
StorageAccountM.ReMark = dt.Rows[i]["Remark"].ToString();
if (dt.Rows[i]["ProductID"].ToString() != "")
{
modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString();
StorageAccountM.ProductID = Convert.ToInt32(dt.Rows[i]["ProductID"].ToString());
}
if (dt.Rows[i]["StorageID"].ToString() != "")
{
modelDetail.StorageID = dt.Rows[i]["StorageID"].ToString();
StorageAccountM.StorageID = Convert.ToInt32(dt.Rows[i]["StorageID"].ToString());
}
if (dt.Rows[i]["ProductCount"].ToString() != "")
{
modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString();
StorageAccountM.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
StorageAccountM.ProductCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
}
if (dt.Rows[i]["UsedUnitCount"].ToString() != "")
{
modelDetail.UsedUnitCount = dt.Rows[i]["UsedUnitCount"].ToString();
}
if (dt.Rows[i]["FromLineNo"].ToString() != "")
{
modelDetail.FromLineNo = dt.Rows[i]["FromLineNo"].ToString();
}
if (dt.Rows[i]["DefaultStorageID"].ToString() != "")
{
modelDetail.DefaultStorageID = dt.Rows[i]["DefaultStorageID"].ToString();
}
modelList.Add(modelDetail);
SqlCommand commSA = new SqlCommand();
commSA = StorageAccountDBHelper.InsertStorageAccountCommand(StorageAccountM, "0");
lstConfirm.Add(commSA);
}
}
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
StringBuilder strAddMTDetail = new StringBuilder();
strAddMTDetail.AppendLine("update officedba.ManufactureTaskDetail set ");
strAddMTDetail.AppendLine(" InCount =ISNULL(InCount,0)+@ReBackNum where ");
strAddMTDetail.AppendLine(" TaskNo=(select TaskNo from officedba.ManufactureTask where ID=(select FromBillID from officedba.StorageInProcess where ID=" + model.ID + "))");
strAddMTDetail.AppendLine(" and SortNo=@SortNo");
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commReMT = new SqlCommand();
commReMT.CommandText = strAddMTDetail.ToString();
if (modelList[i].UsedUnitCount != null)
{
commReMT.Parameters.Add(SqlHelper.GetParameterFromString("@ReBackNum", modelList[i].UsedUnitCount));//回写增加的数量
}
else
{
commReMT.Parameters.Add(SqlHelper.GetParameterFromString("@ReBackNum", modelList[i].ProductCount));//回写增加的数量
}
commReMT.Parameters.Add(SqlHelper.GetParameterFromString("@SortNo", modelList[i].FromLineNo));
lstConfirm.Add(commReMT);//循环加入数组（"已入库数量"增加）
SqlCommand commPD = new SqlCommand();
if (Exists(modelList[i].BatchNo, modelList[i].StorageID, modelList[i].ProductID, model.CompanyCD))
{
commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, modelList[i].StorageID, modelList[i].ProductCount, model, true);
}
else
{
commPD = InsertStorageProduct(modelList[i].BatchNo,modelList[i].ProductID, modelList[i].StorageID, modelList[i].ProductCount, model.CompanyCD);
}
lstConfirm.Add(commPD);
SqlCommand commRoad = new SqlCommand();
commRoad = updateRoadCount(modelList[i].ProductID, modelList[i].DefaultStorageID, modelList[i].ProductCount, model);
lstConfirm.Add(commRoad);
}
}
return SqlHelper.ExecuteTransWithArrayList(lstConfirm);
}
/// <summary>
/// 记录是否存在
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool IsExist(StorageAccountModel model)
{
bool Result = true;
StringBuilder sql = new StringBuilder();
sql.AppendLine("select * from officedba.StorageAccount where ProductID="+model.ProductID+" AND StorageID="+model.StorageID+" AND CompanyCD='"+model.CompanyCD+"' ");
if (!string.IsNullOrEmpty(model.BatchNo))
sql.AppendLine(" and BatchNo='" + model.BatchNo.Trim() + "' ");
else
sql.AppendLine(" and (BatchNo is null or BatchNo='') ");
DataTable dt = SqlHelper.ExecuteSql(sql.ToString());
if (dt.Rows.Count > 0)
Result = true;
else
Result = false;
return Result;
}
public static bool ConfirmBill(StorageOutOtherModel model,out string retstrval)
{
ArrayList lstConfirm = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageOutOther SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" confirmDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
lstConfirm.Add(comm);
List<StorageOutOtherDetailModel> modelList = new List<StorageOutOtherDetailModel>();
string sqlSele = "select ProductID,StorageID,CompanyCD,OutNo,UnitPrice,BatchNo,UsedUnitCount,ProductCount,FromType,FromLineNo from officedba.StorageOutOtherDetail where CompanyCD='" + model.CompanyCD + "'"
+ "and OutNo=(select OutNo from officedba.StorageOutOther where ID=" + model.ID + ")";
DataTable dt = SqlHelper.ExecuteSql(sqlSele);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StorageOutOtherDetailModel modelDetail = new StorageOutOtherDetailModel();
if (dt.Rows[i]["ProductID"].ToString() != "")
{
modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString();
}
if (dt.Rows[i]["StorageID"].ToString() != "")
{
modelDetail.StorageID = dt.Rows[i]["StorageID"].ToString();
}
if (dt.Rows[i]["ProductCount"].ToString() != "")
{
modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString();
}
if (dt.Rows[i]["FromType"].ToString() != "")
{
modelDetail.FromType = dt.Rows[i]["FromType"].ToString();
}
if (dt.Rows[i]["FromLineNo"].ToString() != "")
{
modelDetail.FromLineNo = dt.Rows[i]["FromLineNo"].ToString();
}
modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString();
if (dt.Rows[i]["UsedUnitCount"].ToString() == "")
modelDetail.UsedUnitCount = dt.Rows[i]["ProductCount"].ToString();
else
modelDetail.UsedUnitCount = dt.Rows[i]["UsedUnitCount"].ToString();
modelList.Add(modelDetail);
#region 操作库存流水账
StorageAccountModel AccountM_ = new StorageAccountModel();
AccountM_.BatchNo = dt.Rows[i]["BatchNo"].ToString();
AccountM_.BillNo = dt.Rows[i]["OutNo"].ToString();
AccountM_.BillType = 8;
AccountM_.CompanyCD = dt.Rows[i]["CompanyCD"].ToString();
AccountM_.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
AccountM_.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
AccountM_.HappenDate = System.DateTime.Now;
AccountM_.PageUrl = "../Office/StorageManager/StorageOutOtherAdd.aspx";
if (dt.Rows[i]["UnitPrice"].ToString().Trim() == "")
AccountM_.Price = 0;
else
AccountM_.Price = Convert.ToDecimal(dt.Rows[i]["UnitPrice"].ToString());
AccountM_.ProductCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
AccountM_.ProductID = Convert.ToInt32(dt.Rows[i]["ProductID"].ToString());
AccountM_.StorageID = Convert.ToInt32(dt.Rows[i]["StorageID"].ToString());
SqlCommand AccountCom_ = StorageAccountDBHelper.InsertStorageAccountCommand(AccountM_,"1");
lstConfirm.Add(AccountCom_);
#endregion
}
}
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
if (modelList[0].FromType == "1")
{
StringBuilder strAddPRetail = new StringBuilder();//增加采购退货货单明细中的已出库数量
strAddPRetail.AppendLine("update officedba.PurchaseRejectDetail set ");
strAddPRetail.AppendLine(" OutedTotal =ISNULL(OutedTotal,0)+@ReBackNum where ");
strAddPRetail.AppendLine(" RejectNo=(select RejectNo from officedba.PurchaseReject where ID=(select FromBillID from officedba.StorageOutOther where ID=" + model.ID + "))");
strAddPRetail.AppendLine(" and SortNo=@SortNo");
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commRePR = new SqlCommand();
commRePR.CommandText = strAddPRetail.ToString();
commRePR.Parameters.Add(SqlHelper.GetParameterFromString("@ReBackNum", modelList[i].UsedUnitCount));//回写增加的数量
commRePR.Parameters.Add(SqlHelper.GetParameterFromString("@SortNo", modelList[i].FromLineNo));
lstConfirm.Add(commRePR);//循环加入数组（把PurchaseRejectDetail已经入库数量增加）
SqlCommand commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, modelList[i].StorageID, modelList[i].ProductCount, model, false);
lstConfirm.Add(commPD);
}
}
else//FromType=0的时候，也是无来源的时候，只要更新StorageProduct中数据
{
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, modelList[i].StorageID, modelList[i].ProductCount, model, false);
lstConfirm.Add(commPD);
}
}
}
bool retval = SqlHelper.ExecuteTransWithArrayList(lstConfirm);
if (retval)
{
string sqlFrom = "select TotalPrice,OtherCorpID from officedba.StorageOutOther "
+" where ID=" + model.ID + " ";
DataTable dtFrom = SqlHelper.ExecuteSql(sqlFrom);
int custid=0;
DataTable dtCurrtype = XBase.Data.Office.FinanceManager.CurrTypeSettingDBHelper.GetMasterCurrency(model.CompanyCD);
string IsVoucher = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsVoucher ? "1" : "0";
string IsApply = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsApply ? "1" : "0";
if(dtFrom.Rows[0]["OtherCorpID"].ToString().Trim()!="")custid=Convert.ToInt32(dtFrom.Rows[0]["OtherCorpID"].ToString().Trim());
bool VocherFlag = XBase.Data.Office.FinanceManager.AutoVoucherDBHelper.AutoVoucherInsert(6, model.CompanyCD, IsVoucher, IsApply, Convert.ToDecimal(dtFrom.Rows[0]["TotalPrice"].ToString()), "officedba.StorageOutOther," + model.ID, dtCurrtype.Rows[0]["ID"].ToString() +","+dtCurrtype.Rows[0]["ExchangeRate"].ToString(), custid, out retstrval);
if (VocherFlag) retstrval = "确认成功！";
else retstrval = "确认成功！" + retstrval;
}
else retstrval = "";
return retval;
}
/// <summary>
/// 发料(此处可能需要判断当前物品A所在的仓库A1在分仓存量表里是否存在，如果存在，再判断分仓存量表里的库存是否大于当前该物品的领料统计，如果小于返回提示)
/// </summary>
/// <param name="model"></param>
public static bool SendTakeMaterial(TakeMaterialModel model,out string reason)
{
//判断当前物品在分仓存量表里是否存在,如果存在，再判断分仓存量表里的库存是否大于当前该物品的领料数量，如果大于则更新分仓存量表，如果小于则返回提示
//更新分仓库存量表
//更新领料表
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
ArrayList listADD = new ArrayList();
int tempTotalSucceed = 0;
bool tempOperateResult = false;
string tempReason = "";
string intTake = "";
DataTable dbTake = GetTakeInfo(model);
if (dbTake.Rows.Count > 0)
{
intTake = dbTake.Rows[0]["ID"].ToString();
if (!string.IsNullOrEmpty(dbTake.Rows[0]["Handout"].ToString()))
{
reason = "已经发过料了";
}
else
{
#region 业务处理
DataTable dtDetail = GetTakeDetailInfo(model);
if (dtDetail.Rows.Count > 0)
{
#region 明细处理
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
decimal TakeCount = decimal.Parse(dtDetail.Rows[i]["TakeCount"].ToString());
int ProductID = int.Parse(dtDetail.Rows[i]["ProductID"].ToString());
int StorageID = int.Parse(dtDetail.Rows[i]["StorageID"].ToString());
string BatchNo = dtDetail.Rows[i]["BatchNo"].ToString();
if (string.IsNullOrEmpty(BatchNo))
{
BatchNo = "";
}
string TakeNo =dtDetail.Rows[i]["TakeNo"].ToString();
decimal Price = decimal.Parse(dtDetail.Rows[i]["Price"].ToString());
decimal productCount = InStorageProductCount(model.CompanyCD, ProductID, StorageID,BatchNo);/*现有存量*/
#region 1.判断当前物品的领料数量是否大于分仓存量表中的库存量
if (TakeCount <= productCount)
{
#region 更新分仓存量表
StringBuilder sqlUpdate = new StringBuilder();
SqlCommand comUpdate = new SqlCommand();
sqlUpdate.AppendLine("update officedba.StorageProduct set ProductCount=isnull(ProductCount,0)-@TakeCount where StorageID=@StorageID and ProductID=@ProductID and CompanyCD=@CompanyCD and isnull(BatchNo,'')=@BatchNo");
comUpdate.CommandText = sqlUpdate.ToString();
comUpdate.Parameters.Add(SqlHelper.GetParameter("@BatchNo", BatchNo));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@TakeCount", TakeCount));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@StorageID", StorageID));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID));
listADD.Add(comUpdate);
#endregion
tempTotalSucceed++;
}
else
{
if (productCount == decimal.Parse("-1"))
{
tempReason = tempReason + "第" + (i + 1) + "行中的物品在当前所选的仓库中不存在<br>";
}
else
{
tempReason = tempReason + "第" + (i + 1) + "行中的领料数量大于该物品的库存量" + Decimal.Round(productCount, 2).ToString() + "<br>";
}
}
#endregion
#region 2.写入数据到库存流水账表
StorageAccountModel modelSA = new StorageAccountModel();
modelSA.CompanyCD = model.CompanyCD;
modelSA.BillType = ConstUtil.STORAGEACCOUNT_BILLTYPE_TAKE; /*单据类型*/
modelSA.ProductID = ProductID; /*物品ID*/
modelSA.StorageID = StorageID; /*仓库ID*/
modelSA.BatchNo = BatchNo; /*批次*/
modelSA.BillNo = TakeNo; /*单据编号*/
modelSA.HappenCount = TakeCount; /*操作数量*/
modelSA.Creator = userInfo.EmployeeID; /*操作人*/
modelSA.Price = Price; /*单价*/
modelSA.ProductCount = productCount-TakeCount; /*现有存量*/
modelSA.PageUrl = "Pages/Office/ProductionManager/TakeMaterial_Add.aspx?ModuleID=" + ConstUtil.MODULE_ID_TAKEMATERIAL_EDIT + "&intTakeID=" + intTake;/*页面URL*/
listADD.Add(StorageAccountDBHelper.InsertStorageAccountBySqlCommand(modelSA));
#endregion
}
#endregion
if (dtDetail.Rows.Count > 0 && dtDetail.Rows.Count == tempTotalSucceed)
{
#region 更新领料表中的发料人和发料时间
if (!string.IsNullOrEmpty(model.TakeDate.ToShortDateString()) && !string.IsNullOrEmpty(model.Handout.ToString()))
{
//例：Update officedba.TakeMaterial set Handout=@Handout,TakeDate=@TakeDate where CompanyCD=@CompanyCD and ID=@ID
StringBuilder sqlTake = new StringBuilder();
sqlTake.AppendLine("Update officedba.TakeMaterial set Handout=@Handout,TakeDate=@TakeDate where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comTake = new SqlCommand();
comTake.CommandText = sqlTake.ToString();
comTake.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comTake.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comTake.Parameters.Add(SqlHelper.GetParameter("@Handout", model.Handout));
comTake.Parameters.Add(SqlHelper.GetParameter("@TakeDate", model.TakeDate));
listADD.Add(comTake);
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
tempOperateResult = true;
}
}
#endregion
}
reason = tempReason;
}
else
{
reason = "领料单明细中没有对应的物品需要发料";
}
#endregion
}
}
else
{
reason = "没有找到相关记录";
}
return tempOperateResult;
}
catch (Exception ex) { throw ex; }
}
public static bool ConfirmBill(StorageInPurchaseModel model, out string Msg)
{
//判断源单是无来源还是有来源，无来源则不需要更新在途量
string sqlFromType = "select a.FromType,a.ProviderID from officedba.PurchaseArrive a"
+ " inner join officedba.StorageInPurchase b on b.FromBillID=a.ID and b.ID=" + model.ID;
DataTable dtF = SqlHelper.ExecuteSql(sqlFromType);
//string FromBillFromType = SqlHelper.ExecuteScalar(sqlFromType, null).ToString();//得到的是“0”或“1”
string FromBillFromType = dtF.Rows[0]["FromType"].ToString();
ArrayList lstConfirm = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageInPurchase SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" confirmDate = getdate(),");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = getdate()");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor", model.Confirmor));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", model.ModifiedUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID));
lstConfirm.Add(comm);
List<StorageInPurchaseDetailModel> modelList = new List<StorageInPurchaseDetailModel>();
string sqlSele = "select a.CompanyCD,a.ProductID,a.StorageID,a.BatchNo,a.InNo BillNo,a.UnitPrice,c.EnterDate HappenDate,"
+ "a.ProductCount,a.Remark,a.FromLineNo,b.StorageID as DefaultStorageID,a.UsedUnitCount "
+ " from officedba.StorageInPurchaseDetail a"
+ " left join officedba.ProductInfo b on b.ID=a.ProductID"
+ " left join officedba.StorageInPurchase c on c.InNo = a.InNo and a.CompanyCD = c.CompanyCD "
+ " where a.CompanyCD='" + model.CompanyCD + "' and a.InNo=(select InNo from officedba.StorageInPurchase where ID=" + model.ID + ")";
//string sqlSele = "select ProductID,StorageID,ProductCount,FromLineNo from officedba.StorageInPurchaseDetail where CompanyCD='" + model.CompanyCD + "' and InNo=(select InNo from officedba.StorageInPurchase where ID=" + model.ID + ")";
DataTable dt = SqlHelper.ExecuteSql(sqlSele);
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StorageInPurchaseDetailModel modelDetail = new StorageInPurchaseDetailModel();
StorageAccountModel StorageAccountM = new StorageAccountModel();
if (dt.Rows[i]["ProductID"].ToString() != "")
{
modelDetail.ProductID = dt.Rows[i]["ProductID"].ToString();
StorageAccountM.ProductID = Convert.ToInt32(dt.Rows[i]["ProductID"].ToString());
}
if (dt.Rows[i]["StorageID"].ToString() != "")
{
modelDetail.StorageID = dt.Rows[i]["StorageID"].ToString();
StorageAccountM.StorageID = Convert.ToInt32(dt.Rows[i]["StorageID"].ToString());
}
if (dt.Rows[i]["ProductCount"].ToString() != "")
{
modelDetail.ProductCount = dt.Rows[i]["ProductCount"].ToString();
StorageAccountM.ProductCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
StorageAccountM.HappenCount = Convert.ToDecimal(dt.Rows[i]["ProductCount"].ToString());
}
if (dt.Rows[i]["UsedUnitCount"].ToString() != "")
{
modelDetail.UsedUnitCount = dt.Rows[i]["UsedUnitCount"].ToString();
}
if (dt.Rows[i]["FromLineNo"].ToString() != "")
{
modelDetail.FromLineNo = dt.Rows[i]["FromLineNo"].ToString();
}
if (dt.Rows[i]["DefaultStorageID"].ToString() != "")
{
modelDetail.DefaultStorageID = dt.Rows[i]["DefaultStorageID"].ToString();
}
StorageAccountM.CompanyCD = dt.Rows[i]["CompanyCD"].ToString();
StorageAccountM.BillType = 3;
if (dt.Rows[i]["BatchNo"].ToString() != "")
{
modelDetail.BatchNo = dt.Rows[i]["BatchNo"].ToString();
StorageAccountM.BatchNo = dt.Rows[i]["BatchNo"].ToString();
}
modelList.Add(modelDetail);
StorageAccountM.BillNo = dt.Rows[i]["BillNo"].ToString();
StorageAccountM.Price = Convert.ToDecimal(dt.Rows[i]["UnitPrice"].ToString());
StorageAccountM.HappenDate = Convert.ToDateTime(dt.Rows[i]["HappenDate"].ToString());
StorageAccountM.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
StorageAccountM.PageUrl = "../Office/StorageManager/StorageInPurchaseAdd.aspx";
StorageAccountM.ReMark = dt.Rows[i]["Remark"].ToString();
SqlCommand commSA = new SqlCommand();
commSA = StorageAccountDBHelper.InsertStorageAccountCommand(StorageAccountM, "0");
lstConfirm.Add(commSA);
}
}
if (modelList != null && modelList.Count > 0)//明细不为空的时候
{
StringBuilder strAddSBDetail = new StringBuilder();
strAddSBDetail.AppendLine("update officedba.PurchaseArriveDetail set ");
strAddSBDetail.AppendLine(" InCount =ISNULL(InCount,0)+@ReBackNum where ");
strAddSBDetail.AppendLine(" ArriveNo=(select ArriveNo from officedba.PurchaseArrive where ID=(select FromBillID from officedba.StorageInPurchase where ID=" + model.ID + "))");
strAddSBDetail.AppendLine(" and SortNo=@SortNo");
for (int i = 0; i < modelList.Count; i++)
{
SqlCommand commReSB = new SqlCommand();
commReSB.CommandText = strAddSBDetail.ToString();
if (modelList[i].UsedUnitCount != null)
{
commReSB.Parameters.Add(SqlHelper.GetParameterFromString("@ReBackNum", modelList[i].UsedUnitCount));//回写增加的数量
}
else
{
commReSB.Parameters.Add(SqlHelper.GetParameterFromString("@ReBackNum", modelList[i].ProductCount));//回写增加的数量
}
commReSB.Parameters.Add(SqlHelper.GetParameterFromString("@SortNo", modelList[i].FromLineNo));
lstConfirm.Add(commReSB);//循环加入数组（"已入库数量"增加）
SqlCommand commPD = new SqlCommand();
if (Exists(modelList[i].BatchNo, modelList[i].StorageID, modelList[i].ProductID, model.CompanyCD))
{
commPD = updateStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, modelList[i].StorageID, modelList[i].ProductCount, model, true);
}
else
{
commPD = InsertStorageProduct(modelList[i].BatchNo, modelList[i].ProductID, modelList[i].StorageID, modelList[i].ProductCount, model.CompanyCD);
}
lstConfirm.Add(commPD);
if (FromBillFromType == "1")
{
SqlCommand commRoad = new SqlCommand();
commRoad = updateRoadCount(modelList[i].ProductID, modelList[i].DefaultStorageID, modelList[i].ProductCount, model);
lstConfirm.Add(commRoad);
}
}
}
bool IsOK = true;
IsOK = SqlHelper.ExecuteTransWithArrayList(lstConfirm);
string retstrval = "";
if (IsOK)
{
string IsVoucher = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsVoucher ? "1" : "0";
string IsApply = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).IsApply ? "1" : "0";
decimal TotalPri = Convert.ToDecimal(model.TotalPrice);//价格合计
DataTable dtCurrtype = XBase.Data.Office.FinanceManager.CurrTypeSettingDBHelper.GetMasterCurrency(((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD);
string CurrencyInfo = dtCurrtype.Rows[0]["ID"].ToString();
string ExchangeRate = dtCurrtype.Rows[0]["ExchangeRate"].ToString();
int ProviderID = 0;
if (dtF.Rows[0]["ProviderID"].ToString() != "")
{
ProviderID = Convert.ToInt32(dtF.Rows[0]["ProviderID"].ToString());
}
bool IsTure = XBase.Data.Office.FinanceManager.AutoVoucherDBHelper.AutoVoucherInsert(5, ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD, IsVoucher, IsApply, TotalPri, "officedba.StorageInPurchase," + model.ID, CurrencyInfo + "," + ExchangeRate, ProviderID, out retstrval);
if (IsTure) retstrval = "确认成功！";
else retstrval = "确认成功！" + retstrval;
Msg = retstrval;
}
else
{
Msg = "确认失败！";
}
return IsOK;
}
/// <summary>
/// 取得库存流量最后更新的现有存量
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable RetProductCount(StorageAccountModel model)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ProductCount From officedba.StorageAccount ");
sql.AppendLine("WHERE ID=(select MAX(ID) ID from officedba.StorageAccount where ProductID=" + model.ProductID + " AND StorageID=" + model.StorageID + " AND CompanyCD='" + model.CompanyCD + "' AND (Remark IS NULL OR Remark='') ");
if (!string.IsNullOrEmpty(model.BatchNo))
sql.AppendLine(" and BatchNo='" + model.BatchNo.Trim() + "' ");
else
sql.AppendLine(" and (BatchNo is null or BatchNo='') ");
sql.AppendLine(" ) ");
DataTable dt = SqlHelper.ExecuteSql(sql.ToString());
return dt;
}
/// <summary>
/// 收料
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool ReceiveBackMaterial(BackMaterialModel model, out string reason)
{
//1.从退料单中取出明细判断源单是领料单还是无源单
//2.如果是领料单更新领料单中的退料数量,更新库存表，增加库存量
//3.如果是无源单，更新库存表，增加库存量
//4.更新退料单中的收料人和收料日期
ArrayList listADD = new ArrayList();
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
int tempTotalSucceed = 0;
bool tempOperateResult = false;
string tempReason = "";
string intBack = "";
DataTable dbBack = GetBackInfo(model);
if (dbBack.Rows.Count > 0)
{
intBack = dbBack.Rows[0]["ID"].ToString();
if (!string.IsNullOrEmpty(dbBack.Rows[0]["Receiver"].ToString()))
{
reason = "已经收过料了";
}
else
{
#region 更新领料单中的退料数量
DataTable dtBackDetail = GetBackDetailInfo(model);
if (dtBackDetail.Rows.Count > 0)
{
#region 明细处理
for (int i = 0; i < dtBackDetail.Rows.Count; i++)
{
int intFromType = int.Parse(dtBackDetail.Rows[i]["FromType"].ToString()); //源单类型
int ProductID = int.Parse(dtBackDetail.Rows[i]["ProductID"].ToString()); //产品ID
int StorageID = int.Parse(dtBackDetail.Rows[i]["StorageID"].ToString()); //仓库ID
Decimal BackCount = Decimal.Parse(dtBackDetail.Rows[i]["BackCount"].ToString());//退料数量
int FromBillID = int.Parse(dtBackDetail.Rows[i]["FromBillID"].ToString()); //源单ID
string FromBillNo = dtBackDetail.Rows[i]["FromBillNo"].ToString(); //源单编号
string BatchNo = dtBackDetail.Rows[i]["BatchNo"].ToString(); //批次
if (string.IsNullOrEmpty(BatchNo))
{
BatchNo = "";
}
string BackNo = dtBackDetail.Rows[i]["BackNo"].ToString();
decimal Price = decimal.Parse(dtBackDetail.Rows[i]["Price"].ToString());
decimal productCount = GetStorageProductCount(model.CompanyCD, ProductID, StorageID, BatchNo);/*现有存量*/
#region 1.是领料单的，更新领料单中退料数量
if (intFromType == 1)
{
int intInStorageProduct = InStorageProductCount(model.CompanyCD, ProductID, StorageID,BatchNo);
if (intInStorageProduct > 0)
{
//判断当前所选的物品，在选择的对应的仓库和批次中是否存在
StringBuilder sqlUpdate = new StringBuilder();
sqlUpdate.AppendLine("update officedba.TakeMaterialDetail set BackCount=isnull(BackCount,0)+@BackCount where ID=@FromBillID and TakeNo=@FromBillNo and CompanyCD=@CompanyCD");
SqlCommand comUpdate = new SqlCommand();
comUpdate.CommandText = sqlUpdate.ToString();
comUpdate.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@BackCount", BackCount));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID));
comUpdate.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", FromBillNo));
listADD.Add(comUpdate);
tempTotalSucceed++;
}
else
{
tempReason = tempReason + "第" + (i + 1) + "行中的物品在当前所选的仓库中不存在<br>";
}
}
else
{
tempTotalSucceed++;
}
#endregion
#region 2.更新库存
StringBuilder sqlSto = new StringBuilder();
sqlSto.AppendLine("update officedba.StorageProduct set ProductCount =isnull(ProductCount,0)+@BackCount where CompanyCD=@CompanyCD and StorageID=@StorageID and ProductID=@ProductID and isnull(BatchNo,'')=@BatchNo");
SqlCommand comSto = new SqlCommand();
comSto.CommandText = sqlSto.ToString();
comSto.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comSto.Parameters.Add(SqlHelper.GetParameter("@StorageID", StorageID));
comSto.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID));
comSto.Parameters.Add(SqlHelper.GetParameter("@BackCount", BackCount));
comSto.Parameters.Add(SqlHelper.GetParameter("@BatchNo", BatchNo));
listADD.Add(comSto);
#endregion
#region 3.写入数据到库存流水账表
StorageAccountModel modelSA = new StorageAccountModel();
modelSA.CompanyCD = model.CompanyCD;
modelSA.BillType = ConstUtil.STORAGEACCOUNT_BILLTYPE_BACK; /*单据类型*/
modelSA.ProductID = ProductID; /*物品ID*/
modelSA.StorageID = StorageID; /*仓库ID*/
modelSA.BatchNo = BatchNo; /*批次*/
modelSA.BillNo = BackNo; /*单据编号*/
modelSA.HappenCount = BackCount; /*操作数量*/
modelSA.Creator = userInfo.EmployeeID; /*操作人*/
modelSA.Price = Price; /*单价*/
modelSA.ProductCount = productCount + BackCount; /*现有存量 =原有的现有存量 +退料数量*/
modelSA.PageUrl = "Pages/Office/ProductionManager/BackMaterial_Add.aspx?ModuleID=" + ConstUtil.MODULE_ID_BACKMATERIAL_EDIT + "&intBackID=" + intBack;/*页面URL*/
listADD.Add(StorageAccountDBHelper.InsertStorageAccountBySqlCommand(modelSA));
#endregion
}
#endregion
#region 4.更新退料单
if (dtBackDetail.Rows.Count > 0 && dtBackDetail.Rows.Count == tempTotalSucceed)
{
StringBuilder sqlBack = new StringBuilder();
sqlBack.AppendLine("update officedba.BackMaterial set Receiver=@Receiver,ReceiveDate=@ReceiveDate where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comBack = new SqlCommand();
comBack.CommandText = sqlBack.ToString();
comBack.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comBack.Parameters.Add(SqlHelper.GetParameter("@Receiver", model.Receiver));
comBack.Parameters.Add(SqlHelper.GetParameter("@ReceiveDate", model.ReceiveDate));
comBack.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
listADD.Add(comBack);
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
tempOperateResult = true;
}
}
#endregion
reason = tempReason;
}
else
{
reason = "退料单明细中没有对应的物品需要收料";
}
#endregion
}
}
else
{
reason = "没有找到相关记录";
}
return tempOperateResult;
}
public static bool ConfirmBill(StorageAdjustModel model, List<StorageAdjustDetail> detail)
{
ArrayList sqllist = new ArrayList();
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.StorageAdjust SET");
sql.AppendLine(" Confirmor = @Confirmor,");
sql.AppendLine(" ConfirmDate = @ConfirmDate,");
sql.AppendLine(" BillStatus = 2,");
sql.AppendLine(" ModifiedUserID = @ModifiedUserID,");
sql.AppendLine(" ModifiedDate = @ModifiedDate ");
sql.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand sqlcomm = new SqlCommand();
sqlcomm.CommandText = sql.ToString(); ;
SqlParameter[] param = new SqlParameter[6];
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", model.ModifiedUserID));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@ModifiedDate", model.ModifiedDate));
sqlcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
sqllist.Add(sqlcomm);
for (int i = 0; i < detail.Count; i++)
{
string upsql = "";
if (detail[i].AdjustType == "1")//增加
{
upsql += " update officedba.StorageProduct set ProductCount=isnull(ProductCount,0)+@ProductCount where CompanyCD=@CompanyCD and ProductID=@ProductID and StorageID=@StorageID";
}
else
{
upsql += " update officedba.StorageProduct set ProductCount=isnull(ProductCount,0)-@ProductCount where CompanyCD=@CompanyCD and ProductID=@ProductID and StorageID=@StorageID";
}
if (detail[i].BatchNo != "")
upsql += " and BatchNo='" + detail[i].BatchNo.Trim() + "' ";
else
upsql += " and BatchNo is null ";
SqlCommand upcomm = new SqlCommand();
upcomm.CommandText = upsql;
upcomm.Parameters.Add(SqlHelper.GetParameter("@ProductCount", detail[i].AdjustCount));
upcomm.Parameters.Add(SqlHelper.GetParameter("@ProductID", detail[i].ProductID.ToString()));
upcomm.Parameters.Add(SqlHelper.GetParameter("@StorageID", model.StorageID));
upcomm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
sqllist.Add(upcomm);
#region 操作库存流水账
StorageAccountModel AccountM_ = new StorageAccountModel();
AccountM_.BatchNo = detail[i].BatchNo;
AccountM_.BillNo = model.AdjustNo;
AccountM_.BillType = 14;
AccountM_.CompanyCD = model.CompanyCD;
AccountM_.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;
AccountM_.HappenCount = Convert.ToDecimal(detail[i].AdjustCount.ToString());
AccountM_.ProductCount = Convert.ToDecimal(detail[i].AdjustCount);
AccountM_.HappenDate = System.DateTime.Now;
AccountM_.PageUrl = "../Office/StorageManager/StorageAdjustAdd.aspx";
AccountM_.Price = Convert.ToDecimal(detail[i].CostPrice.ToString());
AccountM_.ProductID = Convert.ToInt32(detail[i].ProductID.ToString());
AccountM_.StorageID = Convert.ToInt32(model.StorageID);
SqlCommand AccountCom_=new SqlCommand();
if (detail[i].AdjustType.ToString() == "1")
AccountCom_ = StorageAccountDBHelper.InsertStorageAccountCommand(AccountM_,"0");
else
AccountCom_ = StorageAccountDBHelper.InsertStorageAccountCommand(AccountM_,"1");
sqllist.Add(AccountCom_);
#endregion
}
if (SqlHelper.ExecuteTransWithArrayList(sqllist))
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 初始化库存流水账--总店
/// </summary>
/// <returns></returns>
public static void InitInfo()
{
/*获取已开通且使用进销存的企业*/
DataTable CompanyDT = StrongeInit.GetDistinctCompany();
foreach (DataRow row in CompanyDT.Rows)
{
/*获取某企业使用进销存的商品及仓库对应的记录*/
DataTable ProAndStroDT = StrongeInit.GetDistinctProductAndStronge(row["CompanyCD"].ToString());
/*获取某企业使用进销存的日期记录*/
DataTable CompanyTimedt = StrongeInit.GetDistinctDate(row["CompanyCD"].ToString());
string BeginDate = string.Empty;//企业使用进销存的开始日期
string EndDate = string.Empty;
if (CompanyTimedt.Rows.Count > 0 && CompanyTimedt != null)
{
BeginDate = CompanyTimedt.Rows[0]["EnterDate"].ToString();
EndDate = CompanyTimedt.Rows[CompanyTimedt.Rows.Count-1]["EnterDate"].ToString();
}
foreach (DataRow rowP in ProAndStroDT.Rows)
{
/*获取该企业对应的物品，仓库的所有操作记录，按日期顺序取数据*/
DataTable CompanyInfoDT = StrongeInit.GetCompanyInfo(row["CompanyCD"].ToString(), rowP["ProductID"].ToString(), rowP["StorageID"].ToString());
decimal ret = 0;//计算累计现有存量
foreach (DataRow rowCom in CompanyInfoDT.Rows)
{
StorageAccountModel model = new StorageAccountModel();
if (!GetType(rowCom["flag"].ToString()))
ret += Convert.ToDecimal(0 - Convert.ToDecimal(rowCom["ProductCount"].ToString()));
else
ret += Convert.ToDecimal(rowCom["ProductCount"].ToString());//现有存量
model.BillNo = rowCom["NoCode"].ToString();//业务单编号
model.BillType = int.Parse(rowCom["flag"].ToString());//单据类型
model.CompanyCD = rowCom["CompanyCD"].ToString();//企业编号
model.HappenCount = Convert.ToDecimal(rowCom["ProductCount"].ToString());//出入库数量
model.HappenDate = Convert.ToDateTime(rowCom["EnterDate"].ToString());//出入库时间
model.Price = Convert.ToDecimal(rowCom["Price"].ToString());//单价
model.ProductID = int.Parse(rowCom["ProductID"].ToString());//物品
model.StorageID = int.Parse(rowCom["StorageID"].ToString());//仓库
model.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;//创建人
model.ProductCount = ret;//现有存量
model.PageUrl = "";//跳转URL
model.ReMark = "";//备注
StrongeInit.InsertStorageAccount(model);//插入库存流水账
}
decimal StorageProduct = StrongeInit.GetStorageProduct(row["CompanyCD"].ToString(), rowP["ProductID"].ToString(), rowP["StorageID"].ToString());
decimal DiffCount = StorageProduct - ret;//判断分仓存量表中的现有存量和计算出的现有存量之前是否存在差异，若存在差异，则说明该物品-仓库在前期存在批量导入.DiffCount为批量导入数量
if (DiffCount > 0)//若存在差异,则在该企业最初使用进销存当天插入批量导入信息
{
//获取符合条件的库存流水账的最大ID
int maxid = StrongeInit.GetMaxID(row["CompanyCD"].ToString(), rowP["ProductID"].ToString(), rowP["StorageID"].ToString());
//更新有批量导入记录物品的库存流水账记录
StrongeInit.UpdateStorageAccount(row["CompanyCD"].ToString(), rowP["ProductID"].ToString(), rowP["StorageID"].ToString(), maxid.ToString());
//更新最大一条记录的现有存量（有批量导入的物品）
StrongeInit.UpdateMaxAccount(maxid.ToString(), StorageProduct);
StorageAccountModel model = new StorageAccountModel();
model.BillNo = "";//业务单编号
model.BillType = 2;//单据类型
model.CompanyCD = row["CompanyCD"].ToString();//企业编号
model.HappenCount = DiffCount;//出入库数量
model.HappenDate = Convert.ToDateTime(BeginDate);//出入库时间
model.Price = 0;//单价
model.ProductID = int.Parse(rowP["ProductID"].ToString());//物品
model.StorageID = int.Parse(rowP["StorageID"].ToString());//仓库
model.Creator = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID;//创建人
model.ProductCount = 0;//现有存量
model.PageUrl = "";//跳转URL
model.ReMark = "在"+BeginDate+"到"+EndDate+"期间的批量导入总和";//备注
StrongeInit.InsertStorageAccount(model);//插入库存流水账
}
}
}
}
/// <summary>
/// InOrOut:入库“0”，出库：“1”
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static SqlCommand InsertStorageAccountCommand(StorageAccountModel model,string InOrOut)
{
if (IsExist(model))
{
DataTable dt = RetProductCount(model);
if (dt.Rows.Count > 0)
{
if (InOrOut == "0")
model.ProductCount = Convert.ToDecimal(dt.Rows[0]["ProductCount"].ToString()) + model.HappenCount;
else
model.ProductCount = Convert.ToDecimal(dt.Rows[0]["ProductCount"].ToString()) - model.HappenCount;
}
}
else
{
if (InOrOut == "0")
model.ProductCount = 0 + model.HappenCount;
else
model.ProductCount = 0 - model.HappenCount;
}
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.StorageAccount");
sql.AppendLine(" (CompanyCD");
sql.AppendLine(" ,BillType");
sql.AppendLine(" ,ProductID");
sql.AppendLine(" ,StorageID");
sql.AppendLine(" ,BatchNo");
sql.AppendLine(" ,BillNo");
sql.AppendLine(" ,HappenDate");
sql.AppendLine(" ,HappenCount");
sql.AppendLine(" ,ProductCount");
sql.AppendLine(" ,Creator");
sql.AppendLine(" ,Price");
sql.AppendLine(" ,PageUrl)");
sql.AppendLine(" VALUES");
sql.AppendLine(" (@CompanyCD");
sql.AppendLine(" ,@BillType");
sql.AppendLine(" ,@ProductID");
sql.AppendLine(" ,@StorageID");
sql.AppendLine(" ,@BatchNo");
sql.AppendLine(" ,@BillNo");
sql.AppendLine(" ,getdate()");
sql.AppendLine(" ,@HappenCount");
sql.AppendLine(" ,@ProductCount");
sql.AppendLine(" ,@Creator");
sql.AppendLine(" ,@Price");
sql.AppendLine(" ,@PageUrl)");
SqlCommand comm = new SqlCommand();
comm.CommandText = sql.ToString();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillType", model.BillType.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", model.ProductID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", model.BatchNo));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillNo", model.BillNo));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@HappenCount", model.HappenCount.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductCount", model.ProductCount.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", model.Creator.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Price", model.Price.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PageUrl", model.PageUrl));
return comm;
}