private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { try { string strCheckCount = ((TextBox)e.Item.Cells[6].Controls[0]).Text; if (this.JudgeIsNull(strCheckCount)) { throw new Exception("请输入盘点量"); } if (!this.JudgeIsNum(strCheckCount)) { throw new Exception("请输入数字"); } string strProduceCount = e.Item.Cells[5].Text; decimal dProduceCount = Convert.ToDecimal(strProduceCount); decimal dCheckCount = Convert.ToDecimal(strCheckCount); if (dCheckCount > dProduceCount) { throw new Exception("盘点量过大,请手工调整"); } if (Session["tbProduceDetail"] == null) { ProduceCheckLog check = new ProduceCheckLog(); check.cnvcOperID = oper.strLoginID; check.cnnProduceSerialNo = Convert.ToDecimal(e.Item.Cells[0].Text); check.cnvcInvCode = e.Item.Cells[1].Text; check.cnnCheckCount = Convert.ToDecimal(strCheckCount); GoodsFacade gf = new GoodsFacade(); OperLog operLog = new OperLog(); operLog.cnvcOperID = oper.strLoginID; operLog.cnvcDeptID = oper.strDeptID; operLog.cnvcOperType = "调整盘点量"; gf.UpdateProduceCheck(check, operLog); } else { string strCode = e.Item.Cells[1].Text; DataTable dtProduce = (DataTable)Session["tbProduceDetail"]; DataRow[] drProduct = dtProduce.Select("cnvcInvCode='" + strCode + "'"); drProduct[0]["cnnCheckCount"] = strCheckCount; Session["tbProduceDetail"] = dtProduce; } this.DataGrid1.EditItemIndex = -1; this.BindGrid(); this.Popup("修改成功"); } catch (Exception ex) { Popup(ex.Message); } }
public void BatchUpdateProduceCheck(ArrayList alCheck, string strProduceSerialNo, OperLog operLog) { using (SqlConnection conn = ConnectionPool.BorrowConnection()) { //conn.Open(); SqlTransaction trans = conn.BeginTransaction(); try { string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString(); DateTime dtSysTime = DateTime.Parse(strSysTime); for (int i = 0; i < alCheck.Count; i++) { ProduceCheckLog check = alCheck[i] as ProduceCheckLog; ProduceCheckLog oldCheck = new ProduceCheckLog(); oldCheck.cnnProduceSerialNo = check.cnnProduceSerialNo; oldCheck.cnvcCode = check.cnvcCode; oldCheck = EntityMapping.Get(oldCheck, trans) as ProduceCheckLog; if (oldCheck == null) { throw new Exception("未找到指定产品生产库存"); } oldCheck.cnnCheckCount = check.cnnCheckCount; oldCheck.cnvcOperID = check.cnvcOperID; oldCheck.cndOperDate = dtSysTime; EntityMapping.Update(oldCheck, trans); } operLog.cndOperDate = dtSysTime; operLog.cnvcComments = "生产流水:" + strProduceSerialNo; EntityMapping.Create(operLog, trans); trans.Commit(); } catch (SqlException sex) { trans.Rollback(); LogAdapter.WriteDatabaseException(sex); throw sex; } catch (Exception ex) { trans.Rollback(); LogAdapter.WriteFeaturesException(ex); throw ex; } finally { ConnectionPool.ReturnConnection(conn); } } }
protected void btnEditConfirm_Click(object sender, System.EventArgs e) { try { ArrayList alCheck = new ArrayList(); foreach (DataGridItem dgi in this.DataGrid1.Items) { string strCheckCount = ((TextBox)dgi.Cells[9].Controls[1]).Text; if (this.JudgeIsNull(strCheckCount)) { throw new Exception("请输入盘点量"); } if (!this.JudgeIsNum(strCheckCount)) { throw new Exception("请输入数字"); } string strProduceCount = dgi.Cells[7].Text; string strAssignCount = dgi.Cells[8].Text; decimal dProduceCount = Convert.ToDecimal(strProduceCount); decimal dCheckCount = Convert.ToDecimal(strCheckCount); decimal dAssignCount = Convert.ToDecimal(strAssignCount); if (dCheckCount > dProduceCount - dAssignCount) { throw new Exception("盘点量过大,无法自动分货,请手工调整分货量"); } if (Session["tbProduceDetail"] == null) { ProduceCheckLog check = new ProduceCheckLog(); check.cnvcOperID = oper.strLoginID; check.cnnProduceSerialNo = Convert.ToDecimal(dgi.Cells[0].Text); check.cnvcCode = dgi.Cells[1].Text; check.cnnCheckCount = Convert.ToDecimal(strCheckCount); alCheck.Add(check); } else { string strCode = dgi.Cells[1].Text; DataTable dtProduce = (DataTable)Session["tbProduceDetail"]; DataRow[] drProduct = dtProduce.Select("cnvcCode='" + strCode + "'"); drProduct[0]["cnnCheckCount"] = strCheckCount; Session["tbProduceDetail"] = dtProduce; } } if (alCheck.Count > 0) { GoodsFacade gf = new GoodsFacade(); OperLog operLog = new OperLog(); operLog.cnvcOperID = oper.strLoginID; operLog.cnvcDeptID = oper.strDeptID; operLog.cnvcOperType = "调整盘点量"; gf.BatchUpdateProduceCheck(alCheck, this.txtProduceSerialNo.Text, operLog); } //this.DataGrid1.EditItemIndex = -1; this.BindGrid(); this.Popup("修改成功"); } catch (Exception ex) { Popup(ex.Message); } }
private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { // AssignDetailLog detailLog = new AssignDetailLog(); // detailLog.cnnAssignSerialNo = Convert.ToDecimal(txtProduceSerialNo.Text); // detailLog.cnnOrderSerialNo = Convert.ToDecimal(e.Item.Cells[0].Text); // detailLog.cnvcProductCode = e.Item.Cells[3].Text; // detailLog.cnnCount = Convert.ToDecimal(((TextBox) e.Item.Cells[8].Controls[0]).Text); // detailLog.cnvcOperID = oper.strLoginID; // // // detailLog.cnnPrice = Convert.ToDecimal(e.Item.Cells[6].Text); // detailLog.cnvcUnit = e.Item.Cells[5].Text; // detailLog.cnnOrderCount = Convert.ToDecimal(e.Item.Cells[7].Text); // detailLog.cnnSum = Math.Round(detailLog.cnnPrice*detailLog.cnnCount, 2);//Convert.ToDecimal(e.Item.Cells[9].Text); // // GoodsFacade gf = new GoodsFacade(); // gf.UpdateAssignLog(detailLog); // try { // if(txtProduceState.Text == "4") // throw new Exception("已分货,无法调整盘点量"); string strCheckCount = ((TextBox)e.Item.Cells[9].Controls[0]).Text; if (this.JudgeIsNull(strCheckCount)) { throw new Exception("请输入盘点量"); } if (!this.JudgeIsNum(strCheckCount)) { throw new Exception("请输入数字"); } string strProduceCount = e.Item.Cells[7].Text; string strAssignCount = e.Item.Cells[8].Text; decimal dProduceCount = Convert.ToDecimal(strProduceCount); decimal dCheckCount = Convert.ToDecimal(strCheckCount); decimal dAssignCount = Convert.ToDecimal(strAssignCount); if (dCheckCount > dProduceCount - dAssignCount) { throw new Exception("盘点量过大,无法自动分货,请手工调整分货量"); } if (Session["tbProduceDetail"] == null) { ProduceCheckLog check = new ProduceCheckLog(); check.cnvcOperID = oper.strLoginID; check.cnnProduceSerialNo = Convert.ToDecimal(e.Item.Cells[0].Text); check.cnvcCode = e.Item.Cells[1].Text; check.cnnCheckCount = Convert.ToDecimal(strCheckCount); GoodsFacade gf = new GoodsFacade(); OperLog operLog = new OperLog(); operLog.cnvcOperID = oper.strLoginID; operLog.cnvcDeptID = oper.strDeptID; operLog.cnvcOperType = "调整盘点量"; gf.UpdateProduceCheck(check, operLog); } else { // if(Session["tbProduceDetail"] == null) // { // Popup("数据异常,请查询"); // return; // } string strCode = e.Item.Cells[1].Text; //string strCheckCount = ((TextBox) e.Item.Cells[9].Controls[0]).Text; DataTable dtProduce = (DataTable)Session["tbProduceDetail"]; DataRow[] drProduct = dtProduce.Select("cnvcCode='" + strCode + "'"); drProduct[0]["cnnCheckCount"] = strCheckCount; Session["tbProduceDetail"] = dtProduce; } this.DataGrid1.EditItemIndex = -1; this.BindGrid(); this.Popup("修改成功"); } catch (Exception ex) { Popup(ex.Message); } }
public void AssignOut(string strProduceSerialNo, OperLog operLog, string strWarehouse) { using (SqlConnection conn = ConnectionPool.BorrowConnection()) { //conn.Open(); SqlTransaction trans = conn.BeginTransaction(); try { string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString(); DateTime dtSysTime = DateTime.Parse(strSysTime); string strsql1 = "SELECT * FROM tbProduceLog WHERE cnvcProduceState='6' and cnnProduceSerialNo=" + strProduceSerialNo; DataTable dtProduceLog = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strsql1); //rr.cndMakeDate = dtSysTime; //EntityMapping.Create(rr,trans); if (dtProduceLog.Rows.Count == 0) { throw new Exception("生产计划不在分货状态!"); } Entity.ProduceLog pl = new ProduceLog(dtProduceLog); string strsql2 = "select * from tbproducechecklog WHERE cnnAssignCount>0 and cnnproduceserialno=" + strProduceSerialNo; DataTable dtProduceCheckLog = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strsql2); if (dtProduceCheckLog.Rows.Count == 0) { throw new Exception("分货出库产品数量都为0"); } DataTable dtInv = SingleTableQuery.ExcuteQuery("tbInventory", trans); DataTable dtComputationUnit = SingleTableQuery.ExcuteQuery("tbComputationUnit", trans); string strsql3 = "select * from tbassignlog where cnnproduceserialno=" + strProduceSerialNo; DataTable dtAssignLog = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strsql3); if (dtAssignLog.Rows.Count == 0) { throw new Exception("未找到分货流水"); } Entity.AssignLog al = new AssignLog(dtAssignLog); Entity.RdRecord rr = new RdRecord(); rr.cnvcRdCode = "RD010"; rr.cnvcRdFlag = "0"; rr.cnvcWhCode = strWarehouse; rr.cnvcDepID = pl.cnvcProduceDeptID; //rr.cnvcOperName = operLog.cnvcop rr.cnvcComments = "分货出库"; rr.cnvcMaker = operLog.cnvcOperID; rr.cnnProorderID = pl.cnnProduceSerialNo; rr.cnvcState = "2"; rr.cndMakeDate = dtSysTime; long rrid = EntityMapping.Create(rr, trans); foreach (DataRow drProduceCheckLog in dtProduceCheckLog.Rows) { Entity.ProduceCheckLog pcl = new ProduceCheckLog(drProduceCheckLog); Entity.RdRecordDetail rrd = new RdRecordDetail(); rrd.cnvcFlag = "0"; rrd.cndExpDate = pcl.cndExpDate; //Convert.ToDateTime(this.txtProduceDate.Text).AddDays(pc.cnnDays).AddDays(Convert.ToDouble(txtDays.Text));//Convert.ToDateTime(this.txtExpDate.Text); rrd.cndMdate = pcl.cndMDate; //Convert.ToDateTime(this.txtProduceDate.Text); rrd.cnnRdID = Convert.ToDecimal(rrid); rrd.cnvcPOID = al.cnnAssignSerialNo.ToString(); DataRow[] drinvs = dtInv.Select("cnvcInvCode='" + pcl.cnvcInvCode + "'"); if (drinvs.Length == 0) { throw new Exception(pcl.cnvcInvCode + "存货档案未设置"); } Entity.Inventory inv = new AMSApp.zhenghua.Entity.Inventory(drinvs[0]); rrd.cnvcInvCode = inv.cnvcInvCode; rrd.cnvcGroupCode = inv.cnvcGroupCode; rrd.cnvcComunitCode = inv.cnvcSTComUnitCode; DataRow[] drcus = dtComputationUnit.Select("cnvcGroupCode='" + inv.cnvcGroupCode + "' and cnbMainUnit=1"); if (drcus.Length == 0) { throw new Exception(inv.cnvcGroupCode + "未设置主计量单位"); } decimal dchangerate = Convert.ToDecimal(drcus[0]["cnichangrate"].ToString()); DataRow[] drcus1 = dtComputationUnit.Select("cnvcGroupCode='" + inv.cnvcGroupCode + "' and cnvcComUnitCode='" + inv.cnvcSTComUnitCode + "'"); decimal dchangerate_st = Convert.ToDecimal(drcus1[0]["cnichangrate"].ToString()); // string strcssql = "SELECT * FROM tbCurrentStock WHERE cnvcWhCode='"+strWarehouse+"' AND cnvcInvCode='"+pcl.cnvcInvCode+"'"; // DataTable dtcs = SqlHelper.ExecuteDataTable(trans,CommandType.Text,strcssql); string strcssql = "SELECT * FROM tbCurrentStock WHERE cnvcWhCode='" + strWarehouse + "' AND cnvcInvCode='" + pcl.cnvcInvCode + "'" + " and CONVERT(char(10),isnull(cndExpDate,''),121)>=CONVERT(char(10),getdate(),121) "; string strcssql2 = "SELECT isnull(sum(cnnAvaQuantity),0) FROM tbCurrentStock WHERE cnvcWhCode='" + strWarehouse + "' AND cnvcInvCode='" + pcl.cnvcInvCode + "'" + " and CONVERT(char(10),isnull(cndExpDate,''),121)>=CONVERT(char(10),getdate(),121) "; DataTable dtcs = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strcssql); decimal davaquantity = Convert.ToDecimal(SqlHelper.ExecuteScalar(trans, CommandType.Text, strcssql2).ToString()); if (dtcs.Rows.Count == 0) { throw new Exception(pcl.cnvcInvCode + "无库存"); } if (davaquantity - pcl.cnnAssignCount * dchangerate_st / dchangerate < 0) { throw new Exception(pcl.cnvcInvCode + "库存不足"); } // if(cs.cnnAvaQuantity - pcl.cnnAssignCount<0) // throw new Exception(pcl.cnvcInvCode+"库存不足"); decimal dhave = 0; foreach (DataRow drcs in dtcs.Rows) { Entity.CurrentStock cs = new CurrentStock(drcs); // if(cs.cnnAvaQuantity - pcl.cnnAssignCount<0) // throw new Exception(pcl.cnvcInvCode+"库存不足"); // cs.cnnAvaQuantity = cs.cnnAvaQuantity - pcl.cnnAssignCount; // cs.cnnQuantity = cs.cnnQuantity - pcl.cnnAssignCount; // EntityMapping.Update(cs,trans); if (cs.cnnAvaQuantity > pcl.cnnAssignCount * dchangerate_st / dchangerate - dhave) { cs.cnnAvaQuantity = cs.cnnAvaQuantity - pcl.cnnAssignCount * dchangerate_st / dchangerate; cs.cnnQuantity = cs.cnnQuantity - pcl.cnnAssignCount * dchangerate_st / dchangerate; EntityMapping.Update(cs, trans); break; } else { cs.cnnAvaQuantity = 0; cs.cnnQuantity = 0; EntityMapping.Update(cs, trans); dhave += cs.cnnAvaQuantity; } } rrd.cnnQuantity = pcl.cnnAssignCount * dchangerate_st / dchangerate; EntityMapping.Create(rrd, trans); } //string strsql = "update tbMakeDetail set cnbCollar=1 where cnnMakeSerialNo="+strMakeSerialNo; //SqlHelper.ExecuteNonQuery(trans,CommandType.Text,strsql); string strsql4 = "update tbproducelog set cnvcproducestate='7' where cnnproduceserialno=" + strProduceSerialNo; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strsql4); operLog.cndOperDate = dtSysTime; operLog.cnvcComments = "生产流水:" + strProduceSerialNo; EntityMapping.Create(operLog, trans); trans.Commit(); } catch (SqlException sex) { trans.Rollback(); LogAdapter.WriteDatabaseException(sex); throw sex; } catch (Exception ex) { trans.Rollback(); LogAdapter.WriteFeaturesException(ex); throw ex; } finally { ConnectionPool.ReturnConnection(conn); } } }
public void UpdateAssignLog(AssignDetail detail, OperLog operLog) { using (SqlConnection conn = ConnectionPool.BorrowConnection()) { SqlTransaction trans = conn.BeginTransaction(); try { string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString(); DateTime dtSysTime = DateTime.Parse(strSysTime); Entity.ProduceCheckLog pcl = new ProduceCheckLog(); pcl.cnnProduceSerialNo = detail.cnnProduceSerialNo; pcl.cnvcInvCode = detail.cnvcInvCode; pcl = EntityMapping.Get(pcl, trans) as ProduceCheckLog; if (pcl == null) { throw new Exception("无法定位生产"); } AssignDetail detailOld = EntityMapping.Get(detail, trans) as AssignDetail; pcl.cnnAssignCount = pcl.cnnAssignCount + detail.cnnAssignCount - detailOld.cnnAssignCount; EntityMapping.Update(pcl, trans); // if(detail.cnnAssignCount != detailOld.cnnAssignCount) // { // OrderSerialNo serialNo = new OrderSerialNo(); // serialNo.cnvcFill = "0"; // serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans)); string strComments = ""; detailOld.cnnAssignCount = detail.cnnAssignCount; EntityMapping.Update(detailOld, trans); strComments = "[" + detail.cnvcInvCode + ":" + detail.cnnAssignCount + "]"; //} operLog.cndOperDate = dtSysTime; operLog.cnvcComments = "分货流水:" + detail.cnnAssignSerialNo.ToString() + strComments; EntityMapping.Create(operLog, trans); trans.Commit(); } catch (SqlException sex) { trans.Rollback(); LogAdapter.WriteDatabaseException(sex); throw sex; } catch (Exception ex) { trans.Rollback(); LogAdapter.WriteFeaturesException(ex); throw ex; } finally { ConnectionPool.ReturnConnection(conn); } } }
//入库 public void ProduceCheck(DataTable dtProduce, OperLog operLog, string strProduceDeptID, string strProduceSerialNo) { using (SqlConnection conn = ConnectionPool.BorrowConnection()) { //conn.Open(); SqlTransaction trans = conn.BeginTransaction(); try { string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString(); DateTime dtSysTime = DateTime.Parse(strSysTime); string strComments = ""; foreach (DataRow drProduce in dtProduce.Rows) { ProduceCheckLog check = new ProduceCheckLog(drProduce); check.cnvcOperID = operLog.cnvcOperID; check.cnvcProduceDeptID = strProduceDeptID; check.cndOperDate = dtSysTime; ProduceCheckLog check2 = new ProduceCheckLog(); check2.cnnProduceSerialNo = check.cnnProduceSerialNo; check2.cnvcInvCode = check.cnvcInvCode; check2 = EntityMapping.Get(check2, trans) as ProduceCheckLog; if (check2 == null) { EntityMapping.Create(check, trans); } else { check2.cnnCheckCount = check.cnnCheckCount; check2.cnvcOperID = operLog.cnvcOperID; check2.cndOperDate = dtSysTime; check2.cnnTeamID = check.cnnTeamID; check2.cnnProducerID = check.cnnProducerID; EntityMapping.Update(check2, trans); } strComments += "[" + check.cnvcInvCode + ":" + check.cnnCheckCount.ToString() + "]"; } ProduceLog pl = new ProduceLog(); pl.cnnProduceSerialNo = decimal.Parse(strProduceSerialNo); pl = EntityMapping.Get(pl, trans) as ProduceLog; if (pl == null) { throw new Exception("生产数据异常"); } pl.cnvcProduceState = "4"; pl.cnvcOperID = operLog.cnvcOperID; pl.cndOperDate = dtSysTime; EntityMapping.Update(pl, trans); operLog.cndOperDate = dtSysTime; operLog.cnvcComments = "生产流水:" + strProduceSerialNo + strComments; EntityMapping.Create(operLog, trans); trans.Commit(); } catch (SqlException sex) { trans.Rollback(); LogAdapter.WriteDatabaseException(sex); throw sex; } catch (Exception ex) { trans.Rollback(); LogAdapter.WriteFeaturesException(ex); throw ex; } finally { ConnectionPool.ReturnConnection(conn); } } }
public void AddAssignLog(ProduceLog produceLog, OperLog operLog, string strwhhouse) //,BusiLog busiLog) { using (SqlConnection conn = ConnectionPool.BorrowConnection()) { SqlTransaction trans = conn.BeginTransaction(); try { string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString(); DateTime dtSysTime = DateTime.Parse(strSysTime); OrderSerialNo serialNo = new OrderSerialNo(); serialNo.cnvcFill = "0"; serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans)); ProduceLog oldLog = new ProduceLog(); oldLog.cnnProduceSerialNo = produceLog.cnnProduceSerialNo; oldLog = EntityMapping.Get(oldLog, trans) as ProduceLog; if (oldLog == null) { throw new Exception("生产计划不存在"); } if (oldLog.cnvcProduceState == "6") { throw new Exception("已分货"); } if (oldLog.cnvcProduceState == "7") { throw new Exception("已分货出库"); } oldLog.cnvcOperID = produceLog.cnvcOperID; oldLog.cndOperDate = dtSysTime; AssignLog assign = new AssignLog(); assign.cndOperDate = dtSysTime; assign.cnnAssignSerialNo = serialNo.cnnSerialNo; assign.cnnProduceSerialNo = oldLog.cnnProduceSerialNo; assign.cnvcShipOperID = produceLog.cnvcOperID; assign.cnvcShipDeptID = oldLog.cnvcProduceDeptID; assign.cnvcOperID = produceLog.cnvcOperID; assign.cndShipDate = oldLog.cndProduceDate; //订单数据 string strOrderSql = @"select * from tbOrderBook where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + ")"; DataTable dtOrder = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderSql); string strOrderDetailSql = @"select * from tbOrderBookDetail where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + ")"; DataTable dtOrderDetail = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderDetailSql); DataTable dtDept = SingleTableQuery.ExcuteQuery("tbDept", trans); //生产盘点数据 string strCheckSql = "select * " + " from tbProduceCheckLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + " and cnnCheckCount>0 and cnbInWh=1"; DataTable dtProduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strCheckSql); #region 分货不按可用量分 string strwhsql = "SELECT cnvcinvcode,sum(cnnAvaQuantity) as cnnwhcount FROM tbCurrentStock " + " WHERE cnvcwhcode='" + strwhhouse + "' AND cnvcStopFlag='0' and CONVERT(char(10),isnull(cndExpDate,''),121)>=CONVERT(char(10),getdate(),121) " + " group by cnvcinvcode"; DataTable dtwh = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strwhsql); Helper.DataTableConvert(dtProduce, "cnvcInvCode", "cnnCheckCount", dtwh, "cnvcinvcode", "cnnwhcount", ""); #endregion //订单按产品分类汇总 //分配 外订订单有限分配 Helper.DataTableConvert(dtOrderDetail, "cnnOrderSerialNo", "cnvcOrderType", dtOrder, "cnnOrderSerialNo", "cnvcOrderType", ""); Helper.DataTableConvert(dtOrderDetail, "cnnOrderSerialNo", "cnvcOrderDeptID", dtOrder, "cnnOrderSerialNo", "cnvcOrderDeptID", ""); Helper.DataTableConvert(dtOrderDetail, "cnnOrderSerialNo", "cndShipDate", dtOrder, "cnnOrderSerialNo", "cndShipDate", ""); Helper.DataTableConvert(dtOrderDetail, "cnvcOrderDeptID", "cnnPriority", dtDept, "cnvcDeptID", "cnnPriority", ""); // dtOrderDetail.Columns.Add("cnnAssignCount"); foreach (DataRow dr in dtOrderDetail.Rows) { dr["cnnAssignCount"] = 0; } DataView dvOrder = new DataView(dtOrderDetail); dvOrder.Sort = "cnvcOrderType desc,cnnPriority asc,cnnOrderSerialNo asc"; Hashtable hOrderSerialNo = new Hashtable(); foreach (DataRowView dv in dvOrder) { string strOrderSerialNo = dv["cnnOrderSerialNo"].ToString(); string strProductCode = dv["cnvcInvCode"].ToString(); string strOrderCount = dv["cnnOrderCount"].ToString(); string strAssignCount = dv["cnnAssignCount"].ToString(); decimal dOrderCount = decimal.Parse(strOrderCount); decimal dAssignCount = Convert.ToDecimal(strAssignCount); string strOrderType = dv["cnvcOrderType"].ToString(); DataRow[] drProduces = dtProduce.Select("cnvcInvCode='" + strProductCode + "'"); if (drProduces.Length == 0) { continue; //throw new Exception(strProductCode+"无可分货量"); } // if(drProduces.Length > 0) // { DateTime dtMDate = Convert.ToDateTime(drProduces[0]["cndMDate"].ToString()); DateTime dtExpDate = Convert.ToDateTime(drProduces[0]["cndExpDate"].ToString()); string strSumCount = drProduces[0]["cnnCheckCount"].ToString(); decimal dSumCount = decimal.Parse(strSumCount); string strSumAssign = drProduces[0]["cnnAssignCount"].ToString(); decimal dSumAssign = decimal.Parse(strSumAssign); //if(dSumCount <=0) throw new Exception(strProductCode+"无可分货量"); // if(dSumCount > 0) // { AssignDetail assignDetail = new AssignDetail(); assignDetail.cnnAssignSerialNo = assign.cnnAssignSerialNo; assignDetail.cnnProduceSerialNo = assign.cnnProduceSerialNo; assignDetail.cnnOrderSerialNo = decimal.Parse(strOrderSerialNo); assignDetail.cnvcInvCode = strProductCode; assignDetail.cndMdate = dtMDate; assignDetail.cndExpDate = dtExpDate; if (dSumCount >= dOrderCount - dAssignCount) { assignDetail.cnnOrderCount = dOrderCount; assignDetail.cnnAssignCount = dOrderCount - dAssignCount; drProduces[0]["cnnCheckCount"] = dSumCount - dOrderCount + dAssignCount; drProduces[0]["cnnAssignCount"] = dSumAssign + dOrderCount - dAssignCount; } else { if (strOrderType == "WDO" || strOrderType == "WDOSELF") { throw new Exception("订单流水为" + dv["cnnOrderSerialNo"].ToString() + "的外订定单" + strProductCode + "不能满足分配"); } assignDetail.cnnOrderCount = dOrderCount; assignDetail.cnnAssignCount = dSumCount; drProduces[0]["cnnCheckCount"] = 0; drProduces[0]["cnnAssignCount"] = dSumAssign + dSumCount; } EntityMapping.Create(assignDetail, trans); if (!hOrderSerialNo.Contains(dv["cnnOrderSerialNo"].ToString())) { hOrderSerialNo.Add(dv["cnnOrderSerialNo"].ToString(), dv["cnnOrderSerialNo"].ToString()); assign.cnnOrderSerialNo = decimal.Parse(dv["cnnOrderSerialNo"].ToString()); assign.cnvcReceiveDeptID = dv["cnvcOrderDeptID"].ToString(); assign.cndShipDate = DateTime.Parse(dv["cndShipDate"].ToString()); EntityMapping.Create(assign, trans); } // string strOrderBookDetail = "update tbOrderBookDetail set cnnAssignCount="+Convert.ToString(assignDetail.cnnAssignCount+dAssignCount)+" where cnnOrderSerialNo="+strOrderSerialNo+" and cnvcProductCode='"+strProductCode+"'"; // SqlHelper.ExecuteNonQuery(trans,CommandType.Text,strOrderBookDetail); // } // } } foreach (DataRow drProduce in dtProduce.Rows) { ProduceCheckLog check = new ProduceCheckLog(drProduce); string strSql = "update tbProduceCheckLog set cnnAssignCount=" + check.cnnAssignCount.ToString() + " where cnnProduceSerialNo=" + check.cnnProduceSerialNo.ToString() + " and cnvcInvCode='" + check.cnvcInvCode + "'"; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql); } //更新订单生产计划状态 string strUpdateOrder = "update tbOrderBook set cnvcOrderState='2' " + " where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='0' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") "; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrder); //更新生产计划状态 oldLog.cnvcProduceState = "6"; EntityMapping.Update(oldLog, trans); operLog.cndOperDate = dtSysTime; operLog.cnvcComments = "分货流水:" + produceLog.cnnProduceSerialNo.ToString(); EntityMapping.Create(operLog, trans); trans.Commit(); } catch (SqlException sex) { trans.Rollback(); LogAdapter.WriteDatabaseException(sex); throw sex; } catch (Exception ex) { trans.Rollback(); LogAdapter.WriteFeaturesException(ex); throw ex; } finally { ConnectionPool.ReturnConnection(conn); } } }
public void AddAssignLog(ProduceLog produceLog, OperLog operLog) //,BusiLog busiLog) { using (SqlConnection conn = ConnectionPool.BorrowConnection()) { //conn.Open(); SqlTransaction trans = conn.BeginTransaction(); try { string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString(); DateTime dtSysTime = DateTime.Parse(strSysTime); OrderSerialNo serialNo = new OrderSerialNo(); serialNo.cnvcFill = "0"; serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans)); ProduceLog oldLog = new ProduceLog(); oldLog.cnnProduceSerialNo = produceLog.cnnProduceSerialNo; oldLog = EntityMapping.Get(oldLog, trans) as ProduceLog; if (oldLog == null) { throw new Exception("生产计划不存在"); } // if(oldLog.cnvcProduceState != "3") // { // throw new Exception("未盘点"); // } oldLog.cnvcOperID = produceLog.cnvcOperID; oldLog.cndOperDate = dtSysTime; AssignLog assign = new AssignLog(); assign.cndOperDate = dtSysTime; assign.cnnAssignSerialNo = serialNo.cnnSerialNo; assign.cnnProduceSerialNo = oldLog.cnnProduceSerialNo; assign.cnvcShipOperID = produceLog.cnvcOperID; assign.cnvcShipDeptID = oldLog.cnvcProduceDeptID; assign.cnvcOperID = produceLog.cnvcOperID; assign.cndShipDate = oldLog.cndProduceDate; #region //订单流水 //收货单位 //订单、加单、减单清单 // string strOrderSql = "select b.cnvcOrderDeptID,b.cndShipDate,b.cnvcOrderType,e.cnnPriority,c.* from tbProduceOrderLog a " // + " join tbOrderBook b on a.cnnOrderSerialNo=b.cnnOrderSerialNo " // + " left outer join tbOrderBookDetail c on b.cnnOrderSerialNo=c.cnnOrderSerialNo " // + " left outer join tbDept e on b.cnvcOrderDeptID=e.cnvcDeptID " // + " where a.cnnProduceSerialNo = " + oldLog.cnnProduceSerialNo.ToString() // + " and a.cnvcType='0' ";//and b.cnvcOrderState='1'"; // string strOrderAddSql = "select b.* from tbProduceOrderLog a " // + " left outer join tbOrderAddLog b on a.cnnOrderSerialNo=b.cnnAddSerialNo " // + " where a.cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() // + " and a.cnvcType='1' ";//and b.cnvcAddState='1'"; // string strOrderReduceSql = "select b.* from tbProduceOrderLog a " // + " left outer join tbOrderReduceLog b on a.cnnOrderSerialNo=b.cnnReduceSerialNo " // + " where a.cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() // + " and a.cnvcType='2' ";//and b.cnvcReduceState='1'"; // DataTable dtOrder = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderSql); // DataTable dtOrderAdd = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderAddSql); // DataTable dtOrderReduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderReduceSql); // //合计订单 // foreach(DataRow drAdd in dtOrderAdd.Rows) // { // string strOrderSerialNo = drAdd["cnnOrderSerialNo"].ToString(); // string strProductCode = drAdd["cnvcProductCode"].ToString(); // DataRow[] drOrders = dtOrder.Select("cnvcProductCode='" + strProductCode + "'"); // if(drOrders.Length == 0) // { // DataRow[] drOrderSerialNo = dtOrder.Select("cnnOrderSerialNo=" + strOrderSerialNo); // if(drOrderSerialNo.Length > 0) // { // DataRow drOrder = dtOrder.NewRow(); // drOrder["cnvcOrderDeptID"] = drOrderSerialNo[0]["cnvcOrderDeptID"]; // drOrder["cndShipDate"] = drOrderSerialNo[0]["cndShipDate"]; // drOrder["cnvcOrderType"] = drOrderSerialNo[0]["cnvcOrderType"]; // drOrder["cnnPriority"] = drOrderSerialNo[0]["cnnPriority"]; // // drOrder["cnnOrderSerialNo"] = drAdd["cnvcProductCode"]; // drOrder["cnvcProductCode"] = drAdd["cnvcProductCode"]; // drOrder["cnvcProductName"] = drAdd["cnvcProductName"]; // drOrder["cnnOrderCount"] = drAdd["cnnAddCount"]; // drOrder["cnvcUnit"] = drAdd["cnvcUnit"]; // drOrder["cnnPrice"] = drAdd["cnnPrice"]; // drOrder["cnnSum"] = drAdd["cnnSum"]; // dtOrder.Rows.Add(drOrder); // } // } // // } // // foreach(DataRow drOrder in dtOrder.Rows) // { // //有的加减数量,无的加产品 // string strProductCode = drOrder["cnvcProductCode"].ToString(); // //订单量 // decimal dCount = decimal.Parse(drOrder["cnnOrderCount"].ToString()); // decimal dAddCount = 0; // DataRow[] drOrderAdds = dtOrderAdd.Select("cnvcProductCode='" + strProductCode + "'"); // if(drOrderAdds.Length > 0) // { // //加单量 // string strOrderAddCount = drOrderAdds[0]["cnnAddCount"].ToString(); // dAddCount = decimal.Parse(strOrderAddCount); // //dAddCount = decimal.Parse(strCount) + decimal.Parse(strOrderAddCount); // drOrder["cnnOrderCount"] = dCount + dAddCount; // } // DataRow[] drOrderReduces = dtOrderReduce.Select("cnvcProductCode='" + strProductCode + "'"); // if(drOrderReduces.Length > 0) // { // //减单量 // decimal dReduceCount = decimal.Parse(drOrderReduces[0]["cnnReduceCount"].ToString()); // //decimal dReduceCount = decimal.Parse(strCount) - decimal.Parse(strOrderReduceCount); // drOrder["cnnOrderCount"] = dCount + dAddCount - dReduceCount; // } // } #endregion #region 注释 //生产、加单、减单 // string strProduceSql = "select * from tbProduceDetail where cnnProduceSerialNo="+oldLog.cnnProduceSerialNo.ToString(); // string strProduceAddSql = "select * from tbProduceDetailAdd where cnvcState='2' and cnnProduceSerialNo="+oldLog.cnnProduceSerialNo.ToString(); // string strProduceReduceSql = "select * from tbProduceDetailReduce where cnvcState='2' and cnnProduceSerialNo="+oldLog.cnnProduceSerialNo; // DataTable dtProduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strProduceSql); // DataTable dtProduceAdd = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strProduceAddSql); // DataTable dtProduceReduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strProduceReduceSql); // // foreach(DataRow drProduce in dtProduce.Rows) // { // //有的加减数量,无的加产品 // string strProductCode = drProduce["cnvcCode"].ToString(); // decimal dCount = decimal.Parse(drProduce["cnnCount"].ToString()); // decimal dAddCount = 0; // DataRow[] drProduceAdds = dtProduceAdd.Select("cnvcCode='" + strProductCode + "'"); // if(drProduceAdds.Length > 0) // { // dAddCount = decimal.Parse(drProduceAdds[0]["cnnCount"].ToString()); // //decimal dAddCount = decimal.Parse(strCount) + decimal.Parse(strOrderAddCount); // drProduce["cnnCount"] = dCount + dAddCount;//dAddCount.ToString(); // } // DataRow[] drProduceReduces = dtProduceReduce.Select("cnvcCode='" + strProductCode + "'"); // if(drProduceReduces.Length > 0) // { // decimal dReduceCount = decimal.Parse(drProduceReduces[0]["cnnCount"].ToString()); // //decimal dReduceCount = decimal.Parse(strCount) - decimal.Parse(strOrderReduceCount); // drProduce["cnnCount"] = dCount + dAddCount - dReduceCount;//dReduceCount.ToString(); // } // } // foreach(DataRow drAdd in dtProduceAdd.Rows) // { // string strProductCode = drAdd["cnvcCode"].ToString(); // DataRow[] drProduces = dtProduce.Select("cnvcCode='" + strProductCode + "'"); // if(drProduces.Length == 0) // { // DataRow drProduce = dtOrder.NewRow(); // drProduce["cnnProduceSerialNo"] = drAdd["cnnProduceSerialNo"]; // drProduce["cnvcCode"] = drAdd["cnvcCode"]; // drProduce["cnvcName"] = drAdd["cnvcName"]; // drProduce["cnnCount"] = drAdd["cnnCount"]; // drProduce["cnvcUnit"] = drAdd["cnvcUnit"]; // //drProduce["cnvcPrice"] = drAdd["cnvcPrice"]; // //drProduce["cnnSum"] = drAdd["cnnSum"]; // dtProduceAdd.Rows.Add(drProduce); // } // // } #endregion //订单数据 string strOrderSql = "select b.cnvcOrderDeptID,b.cndShipDate,b.cnvcOrderType,f.cnnPriority," + " c.cnnOrderSerialNo,c.cnvcProductCode,c.cnvcProductName, " + " (c.cnnOrderCount+isnull(d.cnnAddCount,0)-isnull(e.cnnReduceCount,0)) as cnnOrderCount,c.cnvcUnit,c.cnnPrice, " + " (c.cnnSum+isnull(d.cnnAddSum,0)-isnull(e.cnnReduceSum,0)) as cnnSum,c.cnnAssignCount " + " from tbOrderBookDetail c " + " left outer join " + " (select cnnOrderSerialNo,cnvcProductCode,sum(cnnAddCount) as cnnAddCount,sum(cnnSum) as cnnAddSum from " + " tbOrderAddLog " + " group by cnnOrderSerialNo,cnvcProductCode) " + " d on c.cnnOrderSerialNo=d.cnnOrderSerialNo and d.cnvcProductCode=c.cnvcProductCode " + " left outer join " + " (select cnnOrderSerialNo,cnvcProductCode,sum(cnnReduceCount) as cnnReduceCount,sum(cnnSum) as cnnReduceSum from " + " tbOrderReduceLog " + " group by cnnOrderSerialNo,cnvcProductCode) " + " e on c.cnnOrderSerialNo=e.cnnOrderSerialNo and e.cnvcProductCode=c.cnvcProductCode " + " left outer join tbOrderBook b on c.cnnOrderSerialNo=b.cnnOrderSerialNo " + " left outer join tbDept f on b.cnvcOrderDeptID=f.cnvcDeptID " + " where c.cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + ") " + " and c.cnnOrderCount+isnull(d.cnnAddCount,0)-isnull(e.cnnReduceCount,0)>c.cnnAssignCount "; DataTable dtOrder = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderSql); //生产盘点数据 string strCheckSql = "select * from tbProduceCheckLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + " and cnnCheckCount>0"; DataTable dtProduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strCheckSql); //订单按产品分类汇总 //分配 外订订单有限分配 DataView dvOrder = new DataView(dtOrder); dvOrder.Sort = "cnvcOrderType desc,cnnPriority asc,cnnOrderSerialNo asc"; Hashtable hOrderSerialNo = new Hashtable(); foreach (DataRowView dv in dvOrder) { string strOrderSerialNo = dv["cnnOrderSerialNo"].ToString(); string strProductCode = dv["cnvcProductCode"].ToString(); string strProductName = dv["cnvcProductName"].ToString(); string strOrderCount = dv["cnnOrderCount"].ToString(); string strAssignCount = dv["cnnAssignCount"].ToString(); decimal dOrderCount = decimal.Parse(strOrderCount); decimal dAssignCount = Convert.ToDecimal(strAssignCount); string strUnit = dv["cnvcUnit"].ToString(); string strPrice = dv["cnnPrice"].ToString(); string strOrderType = dv["cnvcOrderType"].ToString(); decimal dPrice = decimal.Parse(strPrice); DataRow[] drProduces = dtProduce.Select("cnvcCode='" + strProductCode + "'"); if (drProduces.Length > 0) { string strSumCount = drProduces[0]["cnnCheckCount"].ToString(); decimal dSumCount = decimal.Parse(strSumCount); string strSumAssign = drProduces[0]["cnnAssignCount"].ToString(); decimal dSumAssign = decimal.Parse(strSumAssign); if (dSumCount > 0) { AssignDetail assignDetail = new AssignDetail(); assignDetail.cnnAssignSerialNo = assign.cnnAssignSerialNo; assignDetail.cnnProduceSerialNo = assign.cnnProduceSerialNo; assignDetail.cnnOrderSerialNo = decimal.Parse(strOrderSerialNo); assignDetail.cnvcProductCode = strProductCode; assignDetail.cnvcProductName = strProductName; assignDetail.cnvcUnit = strUnit; if (dSumCount >= dOrderCount - dAssignCount) { assignDetail.cnnOrderCount = dOrderCount; assignDetail.cnnCount = dOrderCount - dAssignCount; drProduces[0]["cnnCheckCount"] = dSumCount - dOrderCount + dAssignCount; drProduces[0]["cnnAssignCount"] = dSumAssign + dOrderCount - dAssignCount; } else { if (strOrderType == "WDO") { throw new Exception("订单流水为" + dv["cnnOrderSerialNo"].ToString() + "的外订定单" + strProductName + "不能满足分配"); } assignDetail.cnnOrderCount = dOrderCount; assignDetail.cnnCount = dSumCount; drProduces[0]["cnnCheckCount"] = 0; drProduces[0]["cnnAssignCount"] = dSumAssign + dSumCount; } assignDetail.cnnPrice = dPrice; assignDetail.cnnSum = assignDetail.cnnCount * dPrice; EntityMapping.Create(assignDetail, trans); if (!hOrderSerialNo.Contains(dv["cnnOrderSerialNo"].ToString())) { hOrderSerialNo.Add(dv["cnnOrderSerialNo"].ToString(), dv["cnnOrderSerialNo"].ToString()); assign.cnnOrderSerialNo = decimal.Parse(dv["cnnOrderSerialNo"].ToString()); assign.cnvcReceiveDeptID = dv["cnvcOrderDeptID"].ToString(); assign.cndShipDate = DateTime.Parse(dv["cndShipDate"].ToString()); EntityMapping.Create(assign, trans); } string strOrderBookDetail = "update tbOrderBookDetail set cnnAssignCount=" + Convert.ToString(assignDetail.cnnCount + dAssignCount) + " where cnnOrderSerialNo=" + strOrderSerialNo + " and cnvcProductCode='" + strProductCode + "'"; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strOrderBookDetail); } } } foreach (DataRow drProduce in dtProduce.Rows) { ProduceCheckLog check = new ProduceCheckLog(drProduce); //check.cnnCheckCount = 0; //EntityMapping.Update(check); string strSql = "update tbProduceCheckLog set cnnCheckCount=" + check.cnnCheckCount.ToString() + ",cnnAssignCount=" + check.cnnAssignCount.ToString() + " where cnnProduceSerialNo=" + check.cnnProduceSerialNo.ToString() + " and cnvcCode='" + check.cnvcCode + "'"; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql); } //更新订单生产计划状态 string strUpdateOrder = "update tbOrderBook set cnvcOrderState='2' " + " where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='0' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") "; string strUpdateOrderAdd = "update tbOrderAddLog set cnvcAddState='2' " + " where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='1' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") "; string strUpdateOrderReduce = "update tbOrderReduceLog set cnvcReduceState='2' " + " where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='2' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") "; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrder); SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrderAdd); SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrderReduce); //更新生产计划状态 string strUpdteProduceAdd = "update tbProduceDetailAdd set cnvcState='4' where cnvcState='3' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo; string strUpdteProduceReduce = "update tbProduceDetailReduce set cnvcState='4' where cnvcState='3' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo; SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdteProduceAdd); SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdteProduceReduce); oldLog.cnvcProduceState = "4"; EntityMapping.Update(oldLog, trans); operLog.cndOperDate = dtSysTime; operLog.cnvcComments = "分货流水:" + produceLog.cnnProduceSerialNo.ToString(); EntityMapping.Create(operLog, trans); trans.Commit(); } catch (SqlException sex) { trans.Rollback(); LogAdapter.WriteDatabaseException(sex); throw sex; } catch (Exception ex) { trans.Rollback(); LogAdapter.WriteFeaturesException(ex); throw ex; } finally { ConnectionPool.ReturnConnection(conn); } } }