/// <summary> /// 得到一个视图model /// </summary> /// <param name="id">主键值</param> /// <returns>model</returns> public vCStockUpDetailData GetVModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select id,stockUpBillNo,palletIndex,palletNo,financeBillNo,materialNo,num,instantState,materialDesc,U_CNSize,U_PackWay, U_Material,U_UintQty,U_BoxQty,factNum,instantStateDesc,saleBillNo,absEntry,custNo,invoiceNo,boxNum, lineNum,financeBillSort,materialNm_CH,materialNm_EN,SubCatNum,foreignFinanceBillNo,totalNum,totalBoxNum,isNeedForklift,GZT, printNum,docEntry from vCStockUpDetail"); strSql.Append(" where id = @id "); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int) }; parameters[0].Value = id; vCStockUpDetailData model = new vCStockUpDetailData(); 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["palletIndex"] != DBNull.Value) { model.palletIndex = Convert.ToInt32(row["palletIndex"]); } if (row["palletNo"] != DBNull.Value) { model.palletNo = Convert.ToString(row["palletNo"]); } if (row["financeBillNo"] != DBNull.Value) { model.financeBillNo = Convert.ToString(row["financeBillNo"]); } if (row["materialNo"] != DBNull.Value) { model.materialNo = Convert.ToString(row["materialNo"]); } if (row["num"] != DBNull.Value) { model.num = Convert.ToDouble(row["num"]); } if (row["instantState"] != DBNull.Value) { model.instantState = Convert.ToString(row["instantState"]); } if (row["materialDesc"] != DBNull.Value) { model.materialDesc = Convert.ToString(row["materialDesc"]); } if (row["U_CNSize"] != DBNull.Value) { model.U_CNSize = Convert.ToString(row["U_CNSize"]); } if (row["U_PackWay"] != DBNull.Value) { model.U_PackWay = Convert.ToString(row["U_PackWay"]); } if (row["U_Material"] != DBNull.Value) { model.U_Material = Convert.ToString(row["U_Material"]); } if (row["U_UintQty"] != DBNull.Value) { model.U_UintQty = Convert.ToDouble(row["U_UintQty"]); } if (row["U_BoxQty"] != DBNull.Value) { model.U_BoxQty = Convert.ToDouble(row["U_BoxQty"]); } if (row["factNum"] != DBNull.Value) { model.factNum = Convert.ToDouble(row["factNum"]); } if (row["instantStateDesc"] != DBNull.Value) { model.instantStateDesc = Convert.ToString(row["instantStateDesc"]); } if (row["saleBillNo"] != DBNull.Value) { model.saleBillNo = Convert.ToString(row["saleBillNo"]); } if (row["absEntry"] != DBNull.Value) { model.absEntry = Convert.ToInt32(row["absEntry"]); } if (row["custNo"] != DBNull.Value) { model.custNo = Convert.ToString(row["custNo"]); } if (row["invoiceNo"] != DBNull.Value) { model.invoiceNo = Convert.ToString(row["invoiceNo"]); } if (row["boxNum"] != DBNull.Value) { model.boxNum = Convert.ToDouble(row["boxNum"]); } if (row["lineNum"] != DBNull.Value) { model.lineNum = Convert.ToInt32(row["lineNum"]); } if (row["financeBillSort"] != DBNull.Value) { model.financeBillSort = Convert.ToString(row["financeBillSort"]); } if (row["materialNm_CH"] != DBNull.Value) { model.materialNm_CH = Convert.ToString(row["materialNm_CH"]); } if (row["materialNm_EN"] != DBNull.Value) { model.materialNm_EN = Convert.ToString(row["materialNm_EN"]); } if (row["SubCatNum"] != DBNull.Value) { model.SubCatNum = Convert.ToString(row["SubCatNum"]); } if (row["foreignFinanceBillNo"] != DBNull.Value) { model.foreignFinanceBillNo = Convert.ToString(row["foreignFinanceBillNo"]); } if (row["totalNum"] != DBNull.Value) { model.totalNum = Convert.ToDouble(row["totalNum"]); } if (row["totalBoxNum"] != DBNull.Value) { model.totalBoxNum = Convert.ToDouble(row["totalBoxNum"]); } if (row["isNeedForklift"] != DBNull.Value) { model.isNeedForklift = Convert.ToBoolean(row["isNeedForklift"]); } if (row["GZT"] != DBNull.Value) { model.GZT = Convert.ToString(row["GZT"]); } if (row["printNum"] != DBNull.Value) { model.printNum = Convert.ToDouble(row["printNum"]); } if (row["docEntry"] != DBNull.Value) { model.docEntry = Convert.ToInt32(row["docEntry"]); } return model; } else { return null; } }
//***************************************************************************** //do it later do it later do it later //***************************************************************************** /// <summary> /// 保存排托出库信息 /// </summary> /// <param name="stockUpDetailID">备货单明细ID</param> /// <param name="strNewPalletNo">新托盘号</param> /// <param name="strOldBoxNo">原箱号</param> /// <param name="strNewBoxNo">新箱号</param> /// <param name="num">数量</param> /// <returns></returns> public bool SaveOutStockInfo(int stockUpDetailID, string strNewPalletNo, string strOldBoxNo, string strNewBoxNo, double num) { bool ret = false; SqlTransaction trans = null; BArrangeBillBoxBB arraneBillBoxBB = new BArrangeBillBoxBB(this.connection); BForkliftTaskBC forkliftTaskBC = new BForkliftTaskBC(this.connection); CDEPickOutBoxBB DEPickOutBoxBB = new CDEPickOutBoxBB(this.connection); CStockUpDetailBB stockUpDetailBB = new CStockUpDetailBB(this.connection); CSaleDetailBB saleDetailBB = new CSaleDetailBB(this.connection); SCommBB commBB = new SCommBB(this.connection); UStockBB stockBB = new UStockBB(this.connection); UStockOutInDetailBB stockOutInDetailBB = new UStockOutInDetailBB(this.connection); LMaterialBB materialBB = new LMaterialBB(this.connection); try { if (this.transaction == null) { trans = this.connection.BeginTransaction("TransSaveOutStockInfo"); this.pickOutBillBB.Transaction = trans; arraneBillBoxBB.Transaction = trans; forkliftTaskBC.Transaction = trans; DEPickOutBoxBB.Transaction = trans; stockUpDetailBB.Transaction = trans; saleDetailBB.Transaction = trans; commBB.Transaction = trans; stockBB.Transaction = trans; stockOutInDetailBB.Transaction = trans; materialBB.Transaction = trans; } else { this.pickOutBillBB.Transaction = this.transaction; arraneBillBoxBB.Transaction = this.transaction; forkliftTaskBC.Transaction = this.transaction; DEPickOutBoxBB.Transaction = this.transaction; stockUpDetailBB.Transaction = this.transaction; saleDetailBB.Transaction = this.transaction; commBB.Transaction = this.transaction; stockBB.Transaction = this.transaction; stockOutInDetailBB.Transaction = this.transaction; materialBB.Transaction = this.transaction; } CStockUpDetailData stockUpDetailModel = new CStockUpDetailData(); vCStockUpDetailData vStockUpDetailModel = new vCStockUpDetailData(); DataTable dtArrangeBillBox = new DataTable(); DataTable dtDEPickOutBox = new DataTable(); DataTable dtWareLocator = new DataTable(); DataTable dtStockUpDetail = new DataTable(); string strSql = "", strPrePalletNo = "", strPreWareLocatorNo = ""; //获取备货单明细实例 stockUpDetailModel = stockUpDetailBB.GetModel(stockUpDetailID); //拆托拣货箱信息 dtDEPickOutBox = DEPickOutBoxBB.GetVList("boxNo='" + strOldBoxNo + "'").Tables[0]; //普通拣货箱信息 dtArrangeBillBox = arraneBillBoxBB.GetVList("boxNo='" + strOldBoxNo + "' and wareNo<>''").Tables[0]; //扫描箱以前所在库位 if (dtArrangeBillBox.Rows[0]["preWareLocatorNo"].ToString() != "") { strPrePalletNo = dtArrangeBillBox.Rows[0]["palletNo"].ToString();//原托盘条码号 strPreWareLocatorNo = dtArrangeBillBox.Rows[0]["preWareLocatorNo"].ToString();//物料箱以前所在库位 } //获取排托区下线库位信息 strSql = @"select wareLocator.wareNo,wareLocator.downWareLocatorNo as wareLocatorNo from dbo.BArrangeBillBox as arrangeBillBoxBox left join dbo.LWareLocator as wareLocator on wareLocator.wareLocatorNo=arrangeBillBoxBox.wareLocatorNo where arrangeBillBoxBox.boxNo='" + strOldBoxNo + "'"; dtWareLocator = commBB.Query(strSql).Tables[0]; #region 新增销售拣货单信息 CPickOutBillData pickOutBillModel = new CPickOutBillData(); //新增销售拣货单信息 pickOutBillModel.stockUpDetailID = stockUpDetailID;//备货单明细ID //从哪个拣货库区、库位赋值 if (dtDEPickOutBox.Rows.Count > 0) { pickOutBillModel.wareNo = dtDEPickOutBox.Rows[0]["preWareNo"].ToString();//以前的仓库编号 pickOutBillModel.wareLocatorNo = dtDEPickOutBox.Rows[0]["preWareLocatorNo"].ToString();//以前的库位编号 } else { pickOutBillModel.wareNo = dtArrangeBillBox.Rows[0]["preWareNo"].ToString();//以前的仓库编号 pickOutBillModel.wareLocatorNo = dtArrangeBillBox.Rows[0]["preWareLocatorNo"].ToString();//以前的库位编号 } pickOutBillModel.preWareNo = "";//以前的仓库编号 pickOutBillModel.preWareLocatorNo = "";//以前的库位编号 pickOutBillModel.oldPalletNo = dtArrangeBillBox.Rows[0]["palletNo"].ToString();//原托盘号 pickOutBillModel.oldRegion = dtArrangeBillBox.Rows[0]["region"].ToString();//原托内区域 pickOutBillModel.oldBoxNo = strOldBoxNo;//原箱号 pickOutBillModel.newBoxNo = strNewBoxNo;//新箱号 pickOutBillModel.num = num;//数量 pickOutBillModel.isrtEmpId = this.empId;//添加人 pickOutBillModel.isrtDt = System.DateTime.Now.ToString();//添加时间 pickOutBillModel.instantState = "01";//未发货 pickOutBillBB.AddRecord(pickOutBillModel); #endregion 新增销售拣货单信息 #region 新增新的排托箱 DataTable dtArrangeBillBoxNew = new DataTable(); dtArrangeBillBoxNew = arraneBillBoxBB.GetVList("boxNo='" + strNewBoxNo + "'").Tables[0]; //新增新的排托箱 if (dtArrangeBillBoxNew.Rows.Count == 0) { BArrangeBillBoxData newArrangeBillBoxModel = new BArrangeBillBoxData(); newArrangeBillBoxModel.arriveBillNo = "";//到货单号 newArrangeBillBoxModel.financeBillNo = dtArrangeBillBox.Rows[0]["financeBillNo"].ToString();//采购订单号 newArrangeBillBoxModel.arrangeBillNo = "";//排托单号 newArrangeBillBoxModel.stockUpBillNo = stockUpDetailModel.stockUpBillNo;//备货单编号 newArrangeBillBoxModel.boxNo = strNewBoxNo;//箱号 newArrangeBillBoxModel.oldBoxNo = strOldBoxNo;//原箱号 newArrangeBillBoxModel.materialNo = stockUpDetailModel.materialNo;//转换后物料编号 newArrangeBillBoxModel.oldMaterialNo = dtArrangeBillBox.Rows[0]["materialNo"].ToString();//转换前物料编号 newArrangeBillBoxModel.factNum = 0;//数量赋0,通过拣货箱明细计算数量 newArrangeBillBoxModel.palletNo = strNewPalletNo;//托盘号 newArrangeBillBoxModel.region = "";//区域 newArrangeBillBoxModel.oldPalletNo = dtArrangeBillBox.Rows[0]["palletNo"].ToString();//原托盘号 newArrangeBillBoxModel.oldRegion = dtArrangeBillBox.Rows[0]["region"].ToString();//原托内区域 //新箱库区、库位赋值,值为下线线体 if (dtWareLocator.Rows.Count > 0 && dtWareLocator.Rows[0]["wareLocatorNo"] != DBNull.Value && dtWareLocator.Rows[0]["wareLocatorNo"].ToString() != "") { newArrangeBillBoxModel.wareNo = dtWareLocator.Rows[0]["wareNo"].ToString();//库区 newArrangeBillBoxModel.wareLocatorNo = dtWareLocator.Rows[0]["wareLocatorNo"].ToString();//库位 } newArrangeBillBoxModel.preWareNo = "";//原库区 newArrangeBillBoxModel.preWareLocatorNo = "";//原库位 newArrangeBillBoxModel.isBoxArrange = true;//是否排托 newArrangeBillBoxModel.isPalletUsing = true;//托盘是否正在使用 newArrangeBillBoxModel.acceptEmpId = this.EmpID;//接收人 newArrangeBillBoxModel.acceptDt = System.DateTime.Now.ToString();//接收时间 newArrangeBillBoxModel.isOutStocking = false;//是否正在出库 arraneBillBoxBB.AddRecord(newArrangeBillBoxModel); } #endregion 新增新的排托箱 #region 库存所有操作 DataTable dtStock = new DataTable(); DataTable dtMaterial = new DataTable(); UStockOutInDetailData stockOutInDetailModel = new UStockOutInDetailData(); //更改正式库存信息 commBB.ExecuteSql("update dbo.UStock set num=num-" + num.ToString() + " where boxNo='" + strOldBoxNo + "'"); //增加出入库明细-拣货箱出库 dtStock = stockBB.GetList("boxNo='" + strOldBoxNo + "'").Tables[0]; if (dtStock.Rows.Count > 0)//原箱有库存 { dtMaterial = materialBB.GetList("materialNo='" + dtStock.Rows[0]["materialNo"].ToString() + "'").Tables[0]; //保存出入库明细数据 stockOutInDetailModel = new UStockOutInDetailData(); stockOutInDetailModel.billNo = stockUpDetailModel.stockUpBillNo;//单据编号 stockOutInDetailModel.outInType = "06";//销售出库 stockOutInDetailModel.isOut = true;//是否出库 stockOutInDetailModel.stockDt = System.DateTime.Today.ToString();//库存日期 stockOutInDetailModel.dt = System.DateTime.Today.ToString();//操作日期 stockOutInDetailModel.wareNo = dtStock.Rows[0]["wareNo"].ToString();//仓库 stockOutInDetailModel.wareLocatorNo = dtStock.Rows[0]["wareLocatorNo"].ToString();//库位 stockOutInDetailModel.salverNo = dtStock.Rows[0]["palletNo"].ToString();//托盘号 stockOutInDetailModel.boxNo = strOldBoxNo;//箱号 stockOutInDetailModel.materialNo = dtStock.Rows[0]["materialNo"].ToString();//物料编号 stockOutInDetailModel.factMonomerNum = Convert.ToDouble(dtMaterial.Rows[0]["U_UintQty"]);//单体数量 stockOutInDetailModel.factPackNum = Convert.ToDouble(dtMaterial.Rows[0]["U_BoxQty"]);//整箱数量 stockOutInDetailModel.num = num;//数量 stockOutInDetailModel.isrtEmpId = this.empId; stockOutInDetailModel.isrtDt = System.DateTime.Now.ToString(); stockOutInDetailBB.AddRecord(stockOutInDetailModel); } #endregion 库存所有操作 #region 更改备货单明细的新托盘号、状态 //更改备货单明细的新托盘号、状态 vStockUpDetailModel = stockUpDetailBB.GetVModel(stockUpDetailID); stockUpDetailModel.palletNo = strNewPalletNo;//新托盘号 if (vStockUpDetailModel.num > vStockUpDetailModel.factNum) { stockUpDetailModel.instantState = "02";//更改状态为“备货中” } else { stockUpDetailModel.instantState = "03";//更改状态为“备货完成” } stockUpDetailBB.ModifyRecord(stockUpDetailModel); #endregion 更改备货单明细的新托盘号、状态 #region 生成从拣货区到打托区叉车任务 bool isFinish = true; //如果某个托盘的备货单明细已经全部备货完成,生成托盘的叉车任务 strSql = @"select palletNo,instantState from dbo.CStockUpDetail where stockUpBillNo='" + stockUpDetailModel.stockUpBillNo + "' and palletIndex='" + stockUpDetailModel.palletIndex.ToString() + "'"; dtStockUpDetail = commBB.Query(strSql).Tables[0]; foreach (DataRow row in dtStockUpDetail.Rows) { if (row["instantState"].ToString() == "01" || row["instantState"].ToString() == "02") { isFinish = false; break; } } if (isFinish) { //保存从拣货区到打托区叉车任务 forkliftTaskBC.SaveForkliftTask(dtStockUpDetail.Rows[0]["palletNo"].ToString(), "", "", "14", this.empId); } #endregion 生成从拣货区到打托区叉车任务 #region 更改备货单状态 //如果备货单明细的状态全部为“03 备货完成”,更改备货单状态为“08 备货完成” strSql = @"update dbo.CStockUpBill set instantState='08' where not exists(select 1 from dbo.CStockUpDetail as t where t.stockUpBillNo=dbo.CStockUpBill.stockUpBillNo and instantState<>'03')"; commBB.ExecuteSql(strSql); #endregion 更改备货单状态 #region 更改箱锁定状态 int stockId = 0, boxId = 0; object obj = null; //查找库存 obj = commBB.ExecuteScalar("select top 1 id from dbo.UStock where boxNo='" + strOldBoxNo + "' and num>0 and isOutStocking=1"); if (obj == null) { obj = commBB.ExecuteScalar("select top 1 id from dbo.UStock where palletNo='" + strPrePalletNo + "' and num>0 and isOutStocking=1 order by id"); if (obj != null) { stockId = Convert.ToInt32(obj); } } else { stockId = Convert.ToInt32(obj); } //查找物料箱 obj = commBB.ExecuteScalar("select top 1 id from dbo.BArrangeBillBox where boxNo='" + strOldBoxNo + "' and wareNo<>'' and isOutStocking=1"); if (obj == null) { obj = commBB.ExecuteScalar("select top 1 id from dbo.BArrangeBillBox where palletNo='" + strPrePalletNo + "' and wareNo<>'' and isOutStocking=1 order by id"); if (obj != null) { boxId = Convert.ToInt32(obj); } } else { boxId = Convert.ToInt32(obj); } //更改库存中原箱的是否锁定状态 strSql = "update dbo.UStock set isOutStocking=0 where id='" + stockId.ToString() + "'"; commBB.ExecuteSql(strSql); //更改原箱的是否锁定状态 strSql = "update dbo.BArrangeBillBox set isOutStocking=0 where id='" + boxId.ToString() + "'"; commBB.ExecuteSql(strSql); #endregion 更改箱锁定状态 #region 如果原箱剩余数量为零,更改原箱的托盘号 if (strNewBoxNo == strOldBoxNo) { if (dtWareLocator.Rows.Count > 0 && dtWareLocator.Rows[0]["wareLocatorNo"] != DBNull.Value && dtWareLocator.Rows[0]["wareLocatorNo"].ToString() != "") { strSql = @"update dbo.BArrangeBillBox set oldMaterialNo=materialNo,materialNo='" + stockUpDetailModel.materialNo + "',oldPalletNo=palletNo,palletNo='" + strNewPalletNo + "',preWareNo=wareNo,preWareLocatorNo=wareLocatorNo,wareNo='" + dtWareLocator.Rows[0]["wareNo"].ToString() + "',wareLocatorNo='" + dtWareLocator.Rows[0]["wareLocatorNo"].ToString() + "',oldRegion=region,region='' where boxNo='" + strOldBoxNo + "'"; commBB.ExecuteSql(strSql); } } //如果箱内剩余数量为0,清空托盘号和库区、库位信息 dtArrangeBillBox = arraneBillBoxBB.GetVList("boxNo='" + strOldBoxNo + "'").Tables[0]; if (Convert.ToInt32(dtArrangeBillBox.Rows[0]["leavingNum"]) == 0) { commBB.ExecuteSql(@"update dbo.BArrangeBillBox set oldPalletNo=palletNo,palletNo='',preWareNo=wareNo,preWareLocatorNo=wareLocatorNo, wareNo='',wareLocatorNo='' where boxNo='" + strOldBoxNo + "'"); } #endregion 如果原箱剩余数量为零,更改原箱的托盘号 #region 原库位释放 //释放库位,首先判断原托盘上是否有物料 strSql = @"update dbo.LWareLocator set isUsing=0 where not exists(select 1 from dbo.BArrangeBillBox as t where t.palletNo='" + strPrePalletNo + "' and isnull(t.wareNo,'')<>'') and wareLocatorNo='" + strPreWareLocatorNo + "' and isUsing=1 and isDel=0"; commBB.ExecuteSql(strSql); #endregion 原库位释放 #region 出入库所有操作 //库存明细数量为0,删除信息 strSql = "delete from dbo.UStock where num=0"; commBB.ExecuteSql(strSql); #endregion 出入库所有操作 if (this.transaction == null) trans.Commit(); ret = true; } catch (Exception ex) { if (this.transaction == null) trans.Rollback("TransSaveOutStockInfo"); throw ex; } finally { arraneBillBoxBB.Dispose(); forkliftTaskBC.Dispose(); stockUpDetailBB.Dispose(); commBB.Dispose(); saleDetailBB.Dispose(); DEPickOutBoxBB.Dispose(); stockBB.Dispose(); stockOutInDetailBB.Dispose(); materialBB.Dispose(); } return ret; }