/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="model">model</param>
public bool ModifyRecord(CStockUpBillData model)
{
return this.stockUpBillDB.ModifyRecord(model);
}
/// <summary>
/// 自动生成拣货计划
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnPlan_click(object sender, EventArgs e)
{
UStockBB stockBB = new UStockBB();
CStockUpBillBB stockUpBillBB = new CStockUpBillBB();
CStockUpDetailBB stockUpDetailBB = new CStockUpDetailBB();
LMaterialRelationBB materialRelationBB = new LMaterialRelationBB();
CSaleDetailBB saleDetailBB = new CSaleDetailBB();
LMaterialBB materialBB = new LMaterialBB();
try
{
CStockUpBillData stockUpBillModel = new CStockUpBillData();
DataSet dsStock = new DataSet();
DataSet dsStockUpDetail = new DataSet();
DataRow[] drsStock = null;
string strInfo = "";
stockUpBillModel = stockUpBillBB.GetModel(this.IdValue);//销售备货单实例
#region 生成按单的拣货信息
//如果是按单,则直接查找按单采购物料
//dsStock = stockBB.GetVListByGroup("(saleBillNo in ('" + stockUpBillModel.saleBillNos.Replace(",", "','")
// + "') or substring(financeBillNo,1,charindex('-',financeBillNo)) like '%"
// + this.hidCustNo.Value + "%') and isOutStocking=0 and wareLocatorNo<>''");
//如果是按单,则直接查找按单采购物料
dsStock = stockBB.GetVListByGroup_ByFinanceBillNo("(stock.saleBillNo in ('" + stockUpBillModel.saleBillNos.Replace(",", "','")
+ "') or '," + stockUpBillModel.financeBillNos + ",' like '%,'+stock.financeBillNo+',%') and stock.financeBillSortNo='01' and stock.isOutStocking=0 and stock.wareLocatorNo<>''");
//获取定向的备货单明细
dsStockUpDetail = stockUpDetailBB.GetMaterielVList("stockUpBillNo='" + stockUpBillModel.stockUpBillNo
+ "' and financeBillSort='按单'");//and isnull(financeBillNo,'')<>''
if (dsStock.Tables[0].Rows.Count > 0)
{
foreach (DataRow drStockUpDetail in dsStockUpDetail.Tables[0].Rows)
{
//备货单明细合计出来的某种物料总数量
int needCount = Convert.ToInt32(drStockUpDetail["num"]);
string strMaterialNo = drStockUpDetail["materialno"].ToString();//物料号
string strFinanceBillNo = drStockUpDetail["financeBillNo"].ToString();
//string strFinanceBillSort = drStockUpDetail["financeBillSort"].ToString();
string strFinanceBillSort = "按单";
string strDocEntry = drStockUpDetail["docEntry"].ToString();//销售订单ID
string strSaleBillNo = drStockUpDetail["saleBillNo"].ToString();//销售订单号
string strLineNum = drStockUpDetail["lineNum"].ToString();//行号
string strAbsEntry = drStockUpDetail["absEntry"].ToString();//提货单编号
#region 按单的需原物料拣货
while (needCount > 0)
{
//根据先进先出，尽量使用同一托盘原则
//找到仓库内的同种物料所在的库位以及数量
//首先查找不少于此需要物料数量的库位
drsStock = dsStock.Tables[0].Select("financeBillNo='" + strFinanceBillNo
+ "' and materialNo='" + strMaterialNo + "' and num>=" + needCount.ToString());
if (drsStock.Length == 0)
{
drsStock = dsStock.Tables[0].Select("financeBillNo='" + strFinanceBillNo
+ "' and materialNo='" + strMaterialNo + "' and num>0");
}
if (drsStock.Length > 0)
{
//当前库位所拥有该物料的数量
int haveCount = Convert.ToInt32(drsStock[0]["num"]);
//如果当前库位的物料数量大于备货单所需数量,就保存当前库位
DataRow drnew = this.DtResult.NewRow();
drnew["rowId"] = Guid.NewGuid().ToString();
drnew["id"] = 0;
if (haveCount >= needCount)
{
drnew["num"] = needCount;
}
else
{
drnew["num"] = haveCount;
}
drsStock[0]["num"] = Convert.ToInt32(drsStock[0]["num"]) - Convert.ToInt32(drnew["num"]);//库存数量减去已备货数量
needCount = needCount - Convert.ToInt32(drnew["num"]);
drnew["stockUpBillNo"] = stockUpBillModel.stockUpBillNo;
drnew["palletIndex"] = drStockUpDetail["palletIndex"].ToString();
drnew["wareNo"] = drsStock[0]["wareNo"];
drnew["wareLocatorNo"] = drsStock[0]["wareLocatorNo"];
drnew["palletNo"] = drsStock[0]["palletNo"];
drnew["stockUpMaterialNo"] = strMaterialNo;//备货物料编号
drnew["materialNo"] = drsStock[0]["materialNo"];//实际拣货物料编号
drnew["wareNm"] = drsStock[0]["wareNm"];
drnew["wareLocatorNm"] = drsStock[0]["wareLocatorNm"];
drnew["materialDesc"] = drsStock[0]["materialDesc"];
drnew["financeBillNo"] = strFinanceBillNo;//采购合同号
drnew["financeBillSort"] = strFinanceBillSort;//采购合同类型
drnew["saleBillNo"] = strSaleBillNo;//销售订单号
drnew["docEntry"] = strDocEntry;//销售订单ID
drnew["lineNum"] = strLineNum;//行号
drnew["absEntry"] = strAbsEntry;//提货单号
drnew["instantState"] = "01";
drnew["isDel"] = false;
this.DtResult.Rows.Add(drnew);
drStockUpDetail["num"] = needCount;//重新赋值拣货数量
}
else
{
needCount = 0;
}
}
#endregion 按单的需原物料拣货
}
}
#endregion 生成按单的拣货信息
#region 销售订单没有按单采购
//备货需要逐个库位查找
dsStock = stockBB.GetVListByGroup("stock.financeBillSortNo='02' and stock.isOutStocking=0 and stock.wareLocatorNo<>''");
//获取备货的备货单明细
dsStockUpDetail = stockUpDetailBB.GetMaterielVList("stockUpBillNo='" + stockUpBillModel.stockUpBillNo
+ "' and financeBillSort='备货'");//根据采购合同类型，不根据采购合同号判断 and (isnull(financeBillNo,'')=''
if (dsStock.Tables[0].Rows.Count > 0)
{
foreach (DataRow drStockUpDetail in dsStockUpDetail.Tables[0].Rows)
{
//备货单明细合计出来的某种物料总数量
int needCount = Convert.ToInt32(drStockUpDetail["num"]);
string strMaterialNo = drStockUpDetail["materialno"].ToString();//物料号
string strFinanceBillNo = "";
string strFinanceBillSort = "备货";
string strSaleBillNo = drStockUpDetail["saleBillNo"].ToString();//销售订单号
string strLineNum = drStockUpDetail["lineNum"].ToString();//行号
string strAbsEntry = drStockUpDetail["absEntry"].ToString();//提货单编号
string strDocEntry = drStockUpDetail["docEntry"].ToString();//销售订单ID
while (needCount > 0)
{
#region 原物料拣货
//找到仓库内的同种物料所在的库位以及数量
drsStock = dsStock.Tables[0].Select("materialno='" + strMaterialNo
+ "' and num>=" + needCount.ToString());
if (drsStock.Length == 0)
{
drsStock = dsStock.Tables[0].Select("materialno='" + strMaterialNo + "' and num>0");
}
if (drsStock.Length > 0)
{
//当前库位所拥有该物料的数量
int haveCount = Convert.ToInt32(drsStock[0]["num"]);
//如果当前库位的物料数量大于备货单所需数量,就保存当前库位
DataRow drnew = this.DtResult.NewRow();
drnew["rowId"] = Guid.NewGuid().ToString();
drnew["id"] = 0;
if (haveCount >= needCount)
{
drnew["num"] = needCount;
}
else
{
drnew["num"] = haveCount;
}
drsStock[0]["num"] = Convert.ToInt32(drsStock[0]["num"]) - Convert.ToInt32(drnew["num"]);//库存数量减去已备货数量
needCount = needCount - Convert.ToInt32(drnew["num"]);
drnew["stockUpBillNo"] = stockUpBillModel.stockUpBillNo;
drnew["palletIndex"] = drStockUpDetail["palletIndex"].ToString();
drnew["wareNo"] = drsStock[0]["wareNo"];
drnew["wareLocatorNo"] = drsStock[0]["wareLocatorNo"];
drnew["palletNo"] = drsStock[0]["palletNo"];
drnew["stockUpMaterialNo"] = strMaterialNo;//备货物料编号
drnew["materialNo"] = drsStock[0]["materialNo"];//实际拣货物料编号
drnew["wareNm"] = drsStock[0]["wareNm"];
drnew["wareLocatorNm"] = drsStock[0]["wareLocatorNm"];
drnew["materialDesc"] = drsStock[0]["materialDesc"];
drnew["instantState"] = "01";
drnew["financeBillNo"] = strFinanceBillNo;//采购合同号
drnew["financeBillSort"] = strFinanceBillSort;//采购合同类型
drnew["saleBillNo"] = strSaleBillNo;//销售订单号
drnew["docEntry"] = strDocEntry;//销售订单ID
drnew["lineNum"] = strLineNum;//行号
drnew["absEntry"] = strAbsEntry;//提货单号
drnew["isDel"] = false;
this.DtResult.Rows.Add(drnew);
drStockUpDetail["num"] = needCount;//重新赋值拣货数量
}
else
{
break;
}
#endregion 原物料拣货
}
#region 根据物料对应关系拣货
while (needCount > 0)
{
//需要根据物料对应关系拣其他物料
DataTable dtConvertMaterial = new DataTable();
dtConvertMaterial = materialRelationBB.GetList("oldMaterialNo='" + strMaterialNo + "'").Tables[0];
if (dtConvertMaterial.Rows.Count == 0)
{
break;
}
foreach (DataRow row in dtConvertMaterial.Rows)
{
//找到仓库内的有对应关系物料所在的库位以及数量
drsStock = dsStock.Tables[0].Select("materialno='" + row["newMaterialNo"].ToString()
+ "' and num>=" + needCount.ToString());
if (drsStock.Length == 0)
{
drsStock = dsStock.Tables[0].Select("materialno='" + row["newMaterialNo"].ToString() + "' and num>0");
}
if (drsStock.Length > 0)
{
//当前库位所拥有该物料的数量
int haveCount = Convert.ToInt32(drsStock[0]["num"]);
//如果当前库位的物料数量大于备货单所需数量,就保存当前库位
DataRow drnew = this.DtResult.NewRow();
drnew["rowId"] = Guid.NewGuid().ToString();
drnew["id"] = 0;
if (haveCount >= needCount)
{
drnew["num"] = needCount;
}
else
{
drnew["num"] = haveCount;
}
drsStock[0]["num"] = Convert.ToInt32(drsStock[0]["num"]) - Convert.ToInt32(drnew["num"]);//库存数量减去已备货数量
needCount = needCount - Convert.ToInt32(drnew["num"]);
drnew["stockUpBillNo"] = stockUpBillModel.stockUpBillNo;
drnew["palletIndex"] = drStockUpDetail["palletIndex"].ToString();
drnew["wareNo"] = drsStock[0]["wareNo"];
drnew["wareLocatorNo"] = drsStock[0]["wareLocatorNo"];
drnew["palletNo"] = drsStock[0]["palletNo"];
drnew["stockUpMaterialNo"] = strMaterialNo;//备货物料编号
drnew["materialNo"] = drsStock[0]["materialNo"];//实际拣货物料编号
drnew["wareNm"] = drsStock[0]["wareNm"];
drnew["wareLocatorNm"] = drsStock[0]["wareLocatorNm"];
drnew["materialDesc"] = drsStock[0]["materialDesc"];
drnew["instantState"] = "01";
drnew["financeBillNo"] = strFinanceBillNo;//采购合同号
drnew["financeBillSort"] = strFinanceBillSort;//采购合同类型
drnew["saleBillNo"] = strSaleBillNo;//销售订单号
drnew["docEntry"] = strDocEntry;//销售订单ID
drnew["lineNum"] = strLineNum;//行号
drnew["absEntry"] = strAbsEntry;//提货单号
drnew["isDel"] = false;
this.DtResult.Rows.Add(drnew);
drStockUpDetail["num"] = needCount;//重新赋值拣货数量
}
else
{
needCount = 0;
break;
}
}
}
#endregion 根据物料对应关系拣货
}
}
#endregion 销售订单没有按单采购
#region 判断是否完全拣货
DataSet dsStockUpDetail_Temp = stockUpDetailBB.GetMaterielVList("stockUpBillNo='" + stockUpBillModel.stockUpBillNo + "'");
foreach (DataRow rowStockUpDetail in dsStockUpDetail_Temp.Tables[0].Rows)
{
int planNum = 0, factNum = 0;
DataRow[] myDataRowArray = null;
string strNewMaterialNo = "";
DataTable dtConvertMaterial = new DataTable();
//计算计划备货数量
myDataRowArray = dsStockUpDetail_Temp.Tables[0].Select("materialNo='"
+ rowStockUpDetail["materialNo"].ToString() + "'");
foreach (DataRow row in myDataRowArray)
{
planNum += Convert.ToInt32(row["num"]);
}
//计算原物料可拣货数量
//20140725 update
//myDataRowArray = this.DtResult.Select("isDel=0 and materialNo='"
// + rowStockUpDetail["materialNo"].ToString() + "'");
myDataRowArray = this.DtResult.Select("isDel=0 and materialNo=stockupmaterialNo and materialNo='"
+ rowStockUpDetail["materialNo"].ToString() + "'");
foreach (DataRow row in myDataRowArray)
{
factNum += Convert.ToInt32(row["num"]);
}
// 20140807 add
if (planNum != factNum)
{
//获取转换物料的拣货数量
dtConvertMaterial = materialRelationBB.GetList("oldMaterialNo='" + rowStockUpDetail["materialNo"].ToString() + "'").Tables[0];
if (dtConvertMaterial.Rows.Count > 0)
{
// 20140725 update
//strNewMaterialNo = dtConvertMaterial.Rows[0]["newMaterialNo"].ToString();
//myDataRowArray = this.DtResult.Select("isDel=0 and materialNo='" + strNewMaterialNo + "'");
strNewMaterialNo = dtConvertMaterial.Rows[0]["oldMaterialNo"].ToString();
myDataRowArray = this.DtResult.Select("isDel=0 and stockupmaterialNo='" + strNewMaterialNo + "' and materialNo<>stockupmaterialNo ");
foreach (DataRow row in myDataRowArray)
{
factNum += Convert.ToInt32(row["num"]);
}
}
}
//判断是否完成拣货
if (planNum != factNum)
{
strInfo += "物料【" + rowStockUpDetail["materialNo"].ToString() + "】，计划拣货【"
+ planNum.ToString() + "】件，实际拣货【" + factNum.ToString() + "】件；";
}
}
if (strInfo != "")
{
this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"当前未完成拣货，不允许保存！提示信息：" + strInfo + "\");", true);
}
#endregion 判断是否完全拣货
this.BindGridPickOutPlan();//绑定拣货计划列表
}
catch (Exception ex)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "ShowErr", "ShowErr(\"" + Server.UrlEncode(ex.Message) + "\",3);", true);
return;
}
finally
{
stockBB.Dispose();
stockUpBillBB.Dispose();
stockUpDetailBB.Dispose();
materialRelationBB.Dispose();
saleDetailBB.Dispose();
materialBB.Dispose();
}
}
//*****************************************************************************
//do it later do it later do it later
//*****************************************************************************
/// <summary>
/// 保存备货计划
/// </summary>
/// <param name="data">备货单实例</param>
/// <param name="dtStockUpDetall">备货明细数据源</param>
/// <param name="issubmit">是否提交</param>
/// <returns></returns>
public bool SaveBArrangeBill(CStockUpBillData data, DataTable dtStockUpDetall, bool isSubmit)
{
bool ret = false;
CStockUpDetailBB stockUpDetailBB = new CStockUpDetailBB(connection);
CStockUpBillBB stockUpBillBB = new CStockUpBillBB(connection);
SCommBB commBB = new SCommBB(this.connection);
SqlTransaction trans = null;
try
{
if (this.transaction == null)
{
trans = this.connection.BeginTransaction("TransSave");
stockUpDetailBB.Transaction = trans;
stockUpBillBB.Transaction = trans;
commBB.Transaction = trans;
}
else
{
stockUpDetailBB.Transaction = this.transaction;
stockUpBillBB.Transaction = this.transaction;
commBB.Transaction = this.transaction;
}
int mainId = 0;
CStockUpDetailData stockUpDetailModel = new CStockUpDetailData();
string strSaleBillNos = "", strAbsEntrys = "", strLineNums = "";
string[] saleBillNoArray = null, absEntryArray = null, lineNumArray = null;
if (data.id == 0)
{
#region 新增备货单
//新增备货主表数据
mainId = stockUpBillBB.AddRecord(data);
//新增备货单明细数据
foreach (DataRow dr in dtStockUpDetall.Rows)
{
if (dr["isdel"].ToString() == "0")
{
stockUpDetailModel = new CStockUpDetailData();
stockUpDetailModel.financeBillNo = dr["financeBillNo"].ToString();
stockUpDetailModel.financeBillSort = dr["financeBillSort"].ToString();
stockUpDetailModel.materialNo = dr["materialNo"].ToString();
stockUpDetailModel.num = Convert.ToInt32(dr["num"]);
stockUpDetailModel.instantState = "01";
stockUpDetailModel.palletIndex = Convert.ToInt32(dr["palletIndex"]);
stockUpDetailModel.stockUpBillNo = data.stockUpBillNo;
stockUpDetailModel.saleBillNo = dr["saleBillNo"].ToString();
stockUpDetailModel.DocEntry = Convert.ToInt32(dr["DocEntry"]);
stockUpDetailModel.absEntry = Convert.ToInt32(dr["absEntry"]);//销售提货单ID
stockUpDetailModel.lineNum = Convert.ToInt32(dr["lineNum"]);//销售订单行号
stockUpDetailModel.GZT = "";//分配工作台
stockUpDetailBB.AddRecord(stockUpDetailModel);
}
}
#endregion 新增备货单
}
else
{
#region 修改备货单
//修改备货主表数据
stockUpBillBB.ModifyRecord(data);
//维护备货单明细数据
foreach (DataRow dr in dtStockUpDetall.Rows)
{
if (Convert.ToInt32(dr["id"]) > 0 && dr["isdel"].ToString() == "1")//删除方法
{
stockUpDetailBB.DeleteRecord(Convert.ToInt32(dr["id"]));
}
else if (Convert.ToInt32(dr["id"]) > 0 && dr["isdel"].ToString() == "0")//修改
{
stockUpDetailModel = stockUpDetailBB.GetModel(Convert.ToInt32(dr["id"]));
stockUpDetailModel.num = Convert.ToInt32(dr["num"]);
stockUpDetailBB.ModifyRecord(stockUpDetailModel);
}
else if (Convert.ToInt32(dr["id"]) == 0 && dr["isdel"].ToString() == "0")//添加
{
stockUpDetailModel = new CStockUpDetailData();
stockUpDetailModel.financeBillNo = dr["financeBillNo"].ToString();
stockUpDetailModel.financeBillSort = dr["financeBillSort"].ToString();
stockUpDetailModel.materialNo = dr["materialNo"].ToString();
stockUpDetailModel.num = Convert.ToInt32(dr["num"]);
stockUpDetailModel.instantState = "01";
stockUpDetailModel.palletIndex = Convert.ToInt32(dr["palletIndex"]);
stockUpDetailModel.stockUpBillNo = data.stockUpBillNo;
stockUpDetailModel.saleBillNo = dr["saleBillNo"].ToString();
stockUpDetailModel.DocEntry = Convert.ToInt32(dr["DocEntry"]);//销售订单ID
stockUpDetailModel.absEntry = Convert.ToInt32(dr["absEntry"]);//销售提货单ID
stockUpDetailModel.lineNum = Convert.ToInt32(dr["lineNum"]);//销售订单行号
stockUpDetailModel.GZT = "";//分配工作台
stockUpDetailBB.AddRecord(stockUpDetailModel);
}
}
#endregion 修改备货单
}
//根据提货单ID，更改销售订单状态
strSaleBillNos = data.saleBillNos;
strAbsEntrys = data.absEntrys;//提货单ID组合
strLineNums = data.lineNums;
saleBillNoArray = strSaleBillNos.Split(',');//销售订单编号数组
absEntryArray = strAbsEntrys.Split(',');//提货单ID数组
lineNumArray = strLineNums.Split(',');
for (int n = 0; n < saleBillNoArray.Length; n++)
{
//更改提货单状态
commBB.ExecuteSql("update dbo.CSaleBill set instantState='02',stockupBillNo='" + data.stockUpBillNo
+ "' where saleBillNo='" + saleBillNoArray[n] + "' and absEntry='" + absEntryArray[n]
+ "' and lineNum='" + lineNumArray[n] + "'");
}
if (this.transaction == null) trans.Commit();
ret = true;
}
catch (Exception ex)
{
if (this.transaction == null) trans.Rollback("TransSave");
throw ex;
}
finally
{
stockUpDetailBB.Dispose();
stockUpBillBB.Dispose();
commBB.Dispose();
}
return ret;
}
/// <summary>
/// 增加一条数据
/// </summary>
/// <param name="model">model</param>
public int AddRecord(CStockUpBillData model)
{
return this.stockUpBillDB.AddRecord(model);
}
/// <summary>
/// 增加一条"销售备货单"信息
/// </summary>
/// <param name="model">model</param>
public int AddRecord(CStockUpBillData model)
{
int id = 0;
SqlTransaction trans = null;
try
{
if (this.transaction == null)
{
trans = this.connection.BeginTransaction("TransAdd");
this.stockUpBillBB.Transaction = trans;
this.errorDiaryBB.Transaction = trans;
this.operatDiaryBB.Transaction = trans;
}
id = this.stockUpBillBB.AddRecord(model);
SOperatDiaryData operatDiaryData = new SOperatDiaryData();
operatDiaryData.empId = this.empId;
operatDiaryData.functionId = "";
operatDiaryData.recordId = id.ToString();
operatDiaryData.operateContent = "销售备货单增加一条id为“" + id.ToString() + "”的记录";
this.operatDiaryBB.AddRecord(operatDiaryData);
if (this.transaction == null) trans.Commit();
}
catch (Exception ex)
{
if (this.transaction == null) trans.Rollback("TransAdd");
SErrorDiaryData errorDiaryData = new SErrorDiaryData();
errorDiaryData.empId = this.empId;
errorDiaryData.functionId = "";
errorDiaryData.errorText = "销售备货单增加记录时报错：" + ex.Message;
this.errorDiaryBB.AddRecord(errorDiaryData);
throw ex;
}
finally
{
}
return id;
}
/// <summary>
/// 更新一条"销售备货单"信息
/// </summary>
/// <param name="model">model</param>
public bool ModifyRecord(CStockUpBillData model)
{
bool ret = false;
SqlTransaction trans = null;
try
{
if (this.transaction == null)
{
trans = this.connection.BeginTransaction("TransModify");
this.stockUpBillBB.Transaction = trans;
this.errorDiaryBB.Transaction = trans;
this.operatDiaryBB.Transaction = trans;
}
this.stockUpBillBB.ModifyRecord(model);
SOperatDiaryData operatDiaryData = new SOperatDiaryData();
operatDiaryData.empId = this.empId;
operatDiaryData.functionId = "";
operatDiaryData.recordId = model.id.ToString();
operatDiaryData.operateContent = "销售备货单修改一条id为“" + model.id.ToString() + "”的记录";
this.operatDiaryBB.AddRecord(operatDiaryData);
if (this.transaction == null) trans.Commit();
ret = true;
}
catch (Exception ex)
{
if (this.transaction == null) trans.Rollback("TransModify");
SErrorDiaryData errorDiaryData = new SErrorDiaryData();
errorDiaryData.empId = this.empId;
errorDiaryData.functionId = "";
errorDiaryData.errorText = "销售备货单修改记录“" + model.id.ToString() + "”时报错：" + ex.Message;
this.errorDiaryBB.AddRecord(errorDiaryData);
throw ex;
}
finally
{
}
return ret;
}
/// <summary>
/// 增加一条数据
/// </summary>
/// <param name="model">model</param>
public int AddRecord(CStockUpBillData model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("set nocount on; ");
strSql.Append("insert into CStockUpBill(");
strSql.Append(@"stockUpBillNo,saleBillNos,absEntrys,custNo,invoiceNo,palletSortId,shippingMarkNo,stockUpEmpId,outStockDt,mark,
isrtEmpId,isrtDt,updtEmpId,updtDt,commitEmpId,commitDt,instantState,financeBillNos,lineNums)");
strSql.Append(" values (");
strSql.Append(@"@stockUpBillNo,@saleBillNos,@absEntrys,@custNo,@invoiceNo,@palletSortId,@shippingMarkNo,@stockUpEmpId,@outStockDt,@mark,
@isrtEmpId,@isrtDt,@updtEmpId,@updtDt,@commitEmpId,@commitDt,@instantState,@financeBillNos,@lineNums)");
strSql.Append("; select @@identity; set nocount off; ");
SqlParameter[] parameters = {
new SqlParameter("@stockUpBillNo", SqlDbType.VarChar,20),
new SqlParameter("@saleBillNos", SqlDbType.VarChar,1000),
new SqlParameter("@absEntrys", SqlDbType.VarChar,1000),
new SqlParameter("@custNo", SqlDbType.VarChar,20),
new SqlParameter("@invoiceNo", SqlDbType.VarChar,20),
new SqlParameter("@palletSortId", SqlDbType.Int),
new SqlParameter("@shippingMarkNo", SqlDbType.NVarChar,20),
new SqlParameter("@stockUpEmpId", SqlDbType.Int),
new SqlParameter("@outStockDt", SqlDbType.DateTime),
new SqlParameter("@mark", SqlDbType.NVarChar,500),
new SqlParameter("@isrtEmpId", SqlDbType.Int),
new SqlParameter("@isrtDt", SqlDbType.DateTime),
new SqlParameter("@updtEmpId", SqlDbType.Int),
new SqlParameter("@updtDt", SqlDbType.DateTime),
new SqlParameter("@commitEmpId", SqlDbType.Int),
new SqlParameter("@commitDt", SqlDbType.DateTime),
new SqlParameter("@instantState", SqlDbType.VarChar,10),
new SqlParameter("@financeBillNos", SqlDbType.VarChar,500),
new SqlParameter("@lineNums", SqlDbType.VarChar,500)
};
parameters[0].Value = model.stockUpBillNo;
parameters[1].Value = model.saleBillNos;
parameters[2].Value = model.absEntrys;
parameters[3].Value = model.custNo;
parameters[4].Value = model.invoiceNo;
parameters[5].Value = model.palletSortId;
parameters[6].Value = model.shippingMarkNo;
parameters[7].Value = model.stockUpEmpId;
parameters[8].Value = model.outStockDt == string.Empty ? null : model.outStockDt;
parameters[9].Value = model.mark;
parameters[10].Value = model.isrtEmpId;
parameters[11].Value = model.isrtDt == string.Empty ? null : model.isrtDt;
parameters[12].Value = model.updtEmpId;
parameters[13].Value = model.updtDt == string.Empty ? null : model.updtDt;
parameters[14].Value = model.commitEmpId;
parameters[15].Value = model.commitDt == string.Empty ? null : model.commitDt;
parameters[16].Value = model.instantState;
parameters[17].Value = model.financeBillNos;
parameters[18].Value = model.lineNums;
int id = 0;
try
{
object ret = SqlHelper.ExecuteScalar(this.connection, this.transaction, CommandType.Text, strSql.ToString(), parameters);
if (ret != null && ret != DBNull.Value)
{
id = Convert.ToInt32(ret);
}
}
catch (Exception ex)
{
throw ex;
}
return id;
}
/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="model">model</param>
public bool ModifyRecord(CStockUpBillData model)
{
bool ret = false;
StringBuilder strSql = new StringBuilder();
strSql.Append("update CStockUpBill set ");
strSql.Append("stockUpBillNo=@stockUpBillNo,");
strSql.Append("saleBillNos=@saleBillNos,");
strSql.Append("absEntrys=@absEntrys,");
strSql.Append("custNo=@custNo,");
strSql.Append("invoiceNo=@invoiceNo,");
strSql.Append("palletSortId=@palletSortId,");
strSql.Append("shippingMarkNo=@shippingMarkNo,");
strSql.Append("stockUpEmpId=@stockUpEmpId,");
strSql.Append("outStockDt=@outStockDt,");
strSql.Append("mark=@mark,");
strSql.Append("isrtEmpId=@isrtEmpId,");
strSql.Append("isrtDt=@isrtDt,");
strSql.Append("updtEmpId=@updtEmpId,");
strSql.Append("updtDt=@updtDt,");
strSql.Append("commitEmpId=@commitEmpId,");
strSql.Append("commitDt=@commitDt,");
strSql.Append("instantState=@instantState,");
strSql.Append("financeBillNos=@financeBillNos,");
strSql.Append("lineNums=@lineNums");
strSql.Append(" where id = @id ");
SqlParameter[] parameters = {
new SqlParameter("@id", SqlDbType.Int),
new SqlParameter("@stockUpBillNo", SqlDbType.VarChar,20),
new SqlParameter("@saleBillNos", SqlDbType.VarChar,1000),
new SqlParameter("@absEntrys", SqlDbType.VarChar,1000),
new SqlParameter("@custNo", SqlDbType.VarChar,20),
new SqlParameter("@invoiceNo", SqlDbType.VarChar,20),
new SqlParameter("@palletSortId", SqlDbType.Int),
new SqlParameter("@shippingMarkNo", SqlDbType.NVarChar,20),
new SqlParameter("@stockUpEmpId", SqlDbType.Int),
new SqlParameter("@outStockDt", SqlDbType.DateTime),
new SqlParameter("@mark", SqlDbType.NVarChar,500),
new SqlParameter("@isrtEmpId", SqlDbType.Int),
new SqlParameter("@isrtDt", SqlDbType.DateTime),
new SqlParameter("@updtEmpId", SqlDbType.Int),
new SqlParameter("@updtDt", SqlDbType.DateTime),
new SqlParameter("@commitEmpId", SqlDbType.Int),
new SqlParameter("@commitDt", SqlDbType.DateTime),
new SqlParameter("@instantState", SqlDbType.VarChar,10),
new SqlParameter("@financeBillNos", SqlDbType.VarChar,500),
new SqlParameter("@lineNums", SqlDbType.VarChar,500)
};
parameters[0].Value = model.id;
parameters[1].Value = model.stockUpBillNo;
parameters[2].Value = model.saleBillNos;
parameters[3].Value = model.absEntrys;
parameters[4].Value = model.custNo;
parameters[5].Value = model.invoiceNo;
parameters[6].Value = model.palletSortId;
parameters[7].Value = model.shippingMarkNo;
parameters[8].Value = model.stockUpEmpId;
parameters[9].Value = model.outStockDt == string.Empty ? null : model.outStockDt;
parameters[10].Value = model.mark;
parameters[11].Value = model.isrtEmpId;
parameters[12].Value = model.isrtDt == string.Empty ? null : model.isrtDt;
parameters[13].Value = model.updtEmpId;
parameters[14].Value = model.updtDt == string.Empty ? null : model.updtDt;
parameters[15].Value = model.commitEmpId;
parameters[16].Value = model.commitDt == string.Empty ? null : model.commitDt;
parameters[17].Value = model.instantState;
parameters[18].Value = model.financeBillNos;
parameters[19].Value = model.lineNums;
try
{
SqlHelper.ExecuteNonQuery(this.connection, this.transaction, CommandType.Text, strSql.ToString(), parameters);
ret = true;
}
catch (Exception ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 得到一个model
/// </summary>
/// <param name="id">主键值</param>
/// <returns>model</returns>
public CStockUpBillData GetModel(string billNo)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select id,stockUpBillNo,saleBillNos,absEntrys,custNo,invoiceNo,palletSortId,shippingMarkNo,stockUpEmpId,outStockDt,mark,isrtEmpId,
isrtDt,updtEmpId,updtDt,commitEmpId,commitDt,instantState,financeBillNos,lineNums from CStockUpBill");
strSql.Append(" where stockUpBillNo = @billNo ");
SqlParameter[] parameters = {
new SqlParameter("@billNo",billNo)
};
CStockUpBillData model = new CStockUpBillData();
DataSet ds = SqlHelper.ExecuteDataset(this.connection, this.transaction, CommandType.Text, strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow row = ds.Tables[0].Rows[0];
if (row["id"] != DBNull.Value)
{
model.id = Convert.ToInt32(row["id"]);
}
if (row["stockUpBillNo"] != DBNull.Value)
{
model.stockUpBillNo = Convert.ToString(row["stockUpBillNo"]);
}
if (row["saleBillNos"] != DBNull.Value)
{
model.saleBillNos = Convert.ToString(row["saleBillNos"]);
}
if (row["absEntrys"] != DBNull.Value)
{
model.absEntrys = Convert.ToString(row["absEntrys"]);
}
if (row["custNo"] != DBNull.Value)
{
model.custNo = Convert.ToString(row["custNo"]);
}
if (row["invoiceNo"] != DBNull.Value)
{
model.invoiceNo = Convert.ToString(row["invoiceNo"]);
}
if (row["palletSortId"] != DBNull.Value)
{
model.palletSortId = Convert.ToInt32(row["palletSortId"]);
}
if (row["shippingMarkNo"] != DBNull.Value)
{
model.shippingMarkNo = Convert.ToString(row["shippingMarkNo"]);
}
if (row["stockUpEmpId"] != DBNull.Value)
{
model.stockUpEmpId = Convert.ToInt32(row["stockUpEmpId"]);
}
if (row["outStockDt"] != DBNull.Value)
{
model.outStockDt = Convert.ToString(row["outStockDt"]);
}
if (row["mark"] != DBNull.Value)
{
model.mark = Convert.ToString(row["mark"]);
}
if (row["isrtEmpId"] != DBNull.Value)
{
model.isrtEmpId = Convert.ToInt32(row["isrtEmpId"]);
}
if (row["isrtDt"] != DBNull.Value)
{
model.isrtDt = Convert.ToString(row["isrtDt"]);
}
if (row["updtEmpId"] != DBNull.Value)
{
model.updtEmpId = Convert.ToInt32(row["updtEmpId"]);
}
if (row["updtDt"] != DBNull.Value)
{
model.updtDt = Convert.ToString(row["updtDt"]);
}
if (row["commitEmpId"] != DBNull.Value)
{
model.commitEmpId = Convert.ToInt32(row["commitEmpId"]);
}
if (row["commitDt"] != DBNull.Value)
{
model.commitDt = Convert.ToString(row["commitDt"]);
}
if (row["instantState"] != DBNull.Value)
{
model.instantState = Convert.ToString(row["instantState"]);
}
if (row["financeBillNos"] != DBNull.Value)
{
model.financeBillNos = Convert.ToString(row["financeBillNos"]);
}
if (row["lineNums"] != DBNull.Value)
{
model.lineNums = Convert.ToString(row["lineNums"]);
}
return model;
}
else
{
return null;
}
}
/// <summary>
/// 删除备货单明细
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void gridStockUpDetail_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
CStockUpBillBB stockUpBillBB = new CStockUpBillBB();
try
{
CStockUpBillData stockUpBillModel = new CStockUpBillData();
string Id = this.gridStockUpDetail.DataKeys[e.RowIndex].Values["id"].ToString();//ID
string rowid = this.gridStockUpDetail.DataKeys[e.RowIndex].Values["rowId"].ToString();//GUID
string materialNo = this.gridStockUpDetail.DataKeys[e.RowIndex].Values["materialNo"].ToString();//物料编号
DataRow[] myDataRowArray_Result = null;
//根据rowid查找备货明细
myDataRowArray_Result = this.DtResult.Select("rowid='" + rowid + "'");
if (myDataRowArray_Result.Length > 0)
{
//更改删除标志
myDataRowArray_Result[0]["isdel"] = "1";//删除
myDataRowArray_Result[0]["num"] = 0;
//判断备货单是否已经提交
stockUpBillModel = stockUpBillBB.GetModel(this.IdValue);
if (stockUpBillModel != null && stockUpBillModel.instantState != "01")
{
this.ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('已提交数据无法删除！');", true);
return;
}
}
}
catch (Exception ex)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "ShowErr", "ShowErr(\"" + Server.UrlEncode(ex.Message) + "\",3);", true);
return;
}
finally
{
this.BindGridStockUpDetail();//绑定备货单明细列表
this.SaveStencilDetailViewState();//保存备货信息
this.BindGridSaleBill();//显示销售订单明细列表
stockUpBillBB.Dispose();
}
}
/// <summary>
/// 保存或提交备货单公共方法
/// </summary>
/// <param name="isSubmit"></param>
protected void SubmitArrangeBill(bool isSubmit)
{
CStockUpBillBC stockUpBillBC = new CStockUpBillBC();
CStockUpBillBB stockUpBillBB = new CStockUpBillBB();
try
{
this.SaveStencilDetailViewState();//保存备货信息
this.BindGridStockUpDetail();//绑定备货单明细列表
this.BindGridSaleBill();//显示销售订单明细列表
if (this.ValidateArrangeDetail())
{
CStockUpBillData data = stockUpBillBB.GetModel(this.StockBillNo);
if (data == null)
{
#region 未生成备货单
data = new CStockUpBillData();
data.id = 0;
data.stockUpBillNo = this.StockBillNo;
data.invoiceNo = this.invoiceNo.Text;
data.stockUpEmpId = Convert.ToInt32(this.empId.Value == "" ? "0" : this.empId.Value);
data.mark = this.mark.Text;
data.custNo = this.CustomNo;
data.outStockDt = this.outStockDt.Text;
data.saleBillNos = this.lbSaleBillNos.Text;
data.lineNums = Request.QueryString["lineNums"];
data.absEntrys = Request.QueryString["absEntrys"];
data.financeBillNos = Request.QueryString["financeBillNos"];
data.shippingMarkNo = this.shippingMarkNo.Text;
data.isrtDt = DateTime.Now.ToString();
data.isrtEmpId = this.currentUser.empId;
if (isSubmit)
{
data.instantState = "02";
data.updtDt = DateTime.Now.ToString();
data.updtEmpId = this.currentUser.empId;
data.commitDt = DateTime.Now.ToString();
data.commitEmpId = this.currentUser.empId;
}
else
{
data.instantState = "01";
data.updtDt = DateTime.Now.ToString();
data.updtEmpId = this.currentUser.empId;
}
#endregion 未生成备货单
}
else
{
#region 已生成备货单
if (data.instantState != "01")
{
this.ClientScript.RegisterStartupScript(this.GetType(), "alert9", "alert(\"当前备货单已经提交,不允许继续修改备货单信息！\");", true);
return;
}
data.invoiceNo = this.invoiceNo.Text;
data.stockUpEmpId = Convert.ToInt32(this.empId.Value == "" ? "0" : this.empId.Value);
data.mark = this.mark.Text;
data.custNo = this.CustomNo;
data.outStockDt = this.outStockDt.Text;
data.shippingMarkNo = this.shippingMarkNo.Text;
data.updtDt = DateTime.Now.ToString();
data.updtEmpId = this.currentUser.empId;
if (isSubmit)
{
data.instantState = "02";
data.updtDt = DateTime.Now.ToString();
data.updtEmpId = this.currentUser.empId;
data.commitDt = DateTime.Now.ToString();
data.commitEmpId = this.currentUser.empId;
}
else
{
data.instantState = "01";
data.updtDt = DateTime.Now.ToString();
data.updtEmpId = this.currentUser.empId;
}
#endregion 已生成备货单
}
if (stockUpBillBC.SaveBArrangeBill(data, this.DtResult, isSubmit))
{
this.DtResult = null;
this.InitDtResult();//初始化备货单明细数据源
this.BindGridStockUpDetail();//绑定备货单明细列表
this.BindGridSaleBill();//绑定销售订单明细列表
if (isSubmit)//提交
{
this.ClientScript.RegisterStartupScript(this.GetType(), "alert12", "alert(\"备货单维护成功！\");window.location='"
+ Request.Params["backurl"] + "';", true);
}
else//保存
{
this.BindGridStockUpDetail();//绑定备货单明细列表
this.BindGridSaleBill();//绑定销售订单明细列表
this.ClientScript.RegisterStartupScript(this.GetType(), "alert12", "alert(\"备货单维护成功！\");", true);
}
}
else
{
this.ClientScript.RegisterStartupScript(this.GetType(), "alert12", "alert(\"备货单维护失败!\");", true);
}
}
}
catch (Exception ex)
{
this.ClientScript.RegisterStartupScript(this.GetType(), "ShowErr", "ShowErr(\"" + Server.UrlEncode(ex.Message) + "\",3);", true);
return;
}
finally
{
stockUpBillBB.Dispose();
stockUpBillBC.Dispose();
}
}
