/// <summary> /// 根据输入的LOT格式来保存多条数据到数据库 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <param name="procName">当前部门名称</param> /// <returns></returns> private bool InsertData(FormViewInsertEventArgs e, string procName) { //日期 DateTime billDate = Convert.ToDateTime(e.Values["bill_date"]); //单号 string billNum = Convert.ToString(e.Values["bill_num"]); //下部门 string nextProcName = Convert.ToString(e.Values["next_proc_name"]); //单据备注 string billRemark = Convert.ToString(e.Values["remark"]); //录入员 string addPerson = e.Values["add_person"].ToString(); //录入时间 DateTime addTime = Convert.ToDateTime(e.Values["add_time"]); //最后修改时间 DateTime lastChangeTime = Convert.ToDateTime(e.Values["last_change_time"]); //数据适配器 //当前添加语句对象 //当前数据库连接 using (var daHead = new t_mantissa_lot_card_out_headTableAdapter()) using (var daContent = new t_mantissa_lot_card_out_contentTableAdapter()) using (var daBalance = new t_mantissa_lot_card_balanceTableAdapter()) using (var daProc = new t_proc_lot_card_balanceTableAdapter()) using (var conn = daHead.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置数据库连接对象 daContent.Connection = daBalance.Connection = daProc.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = daProc.Transaction = tran; //试运行 try { //保存表头 daHead.Insert( billDate, billNum, procName, nextProcName, billRemark, addPerson, addTime, lastChangeTime ); //遍历子表执行保存 for (int iRow = 0; iRow < 10; iRow++) { //子表各控件 var tr = tabDataListSon.Rows[iRow + 1]; var litRowId = (Literal)tr.Cells[0].Controls[0]; var txtPrevProcName = (TextBox)tr.Cells[1].Controls[0]; var txtLotId = (TextBox)tr.Cells[2].Controls[0]; var txtProductNum = (TextBox)tr.Cells[3].Controls[0]; var txtPnlQty = (TextBox)tr.Cells[4].Controls[0]; var txtPcsQty = (TextBox)tr.Cells[5].Controls[0]; var txtRemark = (TextBox)tr.Cells[6].Controls[0]; //取得数据 byte rowId = Convert.ToByte(litRowId.Text); string prevProcName = txtPrevProcName.Text; string lotId = txtLotId.Text; string productNum = txtProductNum.Text; int pnlQty = txtPnlQty.Text.Trim().Length <= 0 ? 0 : int.Parse(txtPnlQty.Text.Trim()); int pcsQty = txtPcsQty.Text.Trim().Length <= 0 ? 0 : int.Parse(txtPcsQty.Text.Trim()); string remark = txtRemark.Text; //存在数据才保存 if (lotId.Length > 0 && productNum.Length > 0 && pnlQty + pcsQty > 0) { //保存到单据内容清单 daContent.Insert( billNum, rowId, prevProcName, lotId, productNum, pnlQty, pcsQty, remark ); //保存到清单部门批量卡结存清单 daProc.Insert( procName, nextProcName, lotId, productNum, pnlQty, pcsQty, "出库单写入" + (remark.Length > 0 ? ":" + remark : string.Empty), Session["user_name"].ToString(), DateTime.Now, DateTime.Now, null, null, true ); //从尾数结存清单中扣除 if (!ydOperateMantissaLotCard.DecreaseMantissaBalance( daBalance, prevProcName, lotId, pnlQty, pcsQty )) { return false; } } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //非数字返回失败 throw new Exception(ex.Message); } } } }
/// <summary> /// 根据输入的参数值来执行更新数据 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool UpdateData(FormViewUpdateEventArgs e) { //数据适配器 //当前添加语句对象 //当前数据库连接 using (var da = new v_mantissa_lot_card_outTableAdapter()) using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; //试运行 try { //部门名称 string procName = e.OldValues["proc_name"].ToString(); //当前行单号 string billNum = Convert.ToString(e.Keys[0]); //获取数据 using (var tab = da.GetDataByBillNum(billNum)) { //检查是否获取到行 if (tab.Rows.Count == 0) { //显示失败 throw new Exception("当前记录已经被其他用户删除!"); } //数据适配器 using (var daHead = new t_mantissa_lot_card_out_headTableAdapter()) using (var daContent = new t_mantissa_lot_card_out_contentTableAdapter()) using (var daBalance = new t_mantissa_lot_card_balanceTableAdapter()) using (var daProc = new t_proc_lot_card_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = daProc.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = daProc.Transaction = tran; //将之前的单据内容写回 foreach (DataSetMantissaOut.v_mantissa_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripProcBalance( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //修改原下部门批量卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //执行插入到尾数结存清单 daBalance.InsertData( row.prev_proc_name, row.proc_name, row.lot_id, row.product_num, row.pnl_qty, row.pcs_qty, "尾数出库单写回" + (row.IsremarkNull() ? string.Empty : ":" + row.remark), Session["user_name"].ToString() ); } //从单据内容中删除 daContent.DeleteByBillNum(billNum); //从单据表头中删除 daHead.Delete(billNum); //日期 DateTime billDate = Convert.ToDateTime(e.NewValues["bill_date"]); //下部门 string nextProcName = Convert.ToString(e.NewValues["next_proc_name"]); //单据备注 string billRemark = Convert.ToString(e.NewValues["remark"]); //录入员 string addPerson = e.NewValues["add_person"].ToString(); //录入时间 DateTime addTime = Convert.ToDateTime(e.NewValues["add_time"]); //最后修改时间 DateTime lastChangeTime = Convert.ToDateTime(e.NewValues["last_change_time"]); //保存表头 daHead.Insert( billDate, billNum, procName, nextProcName, billRemark, addPerson, addTime, lastChangeTime ); //遍历子表执行保存 for (int iRow = 0; iRow < 10; iRow++) { //子表各控件 var tr = tabDataListSon.Rows[iRow + 1]; var litRowId = (Literal)tr.Cells[0].Controls[0]; var txtPrevProcName = (TextBox)tr.Cells[1].Controls[0]; var txtLotId = (TextBox)tr.Cells[2].Controls[0]; var txtProductNum = (TextBox)tr.Cells[3].Controls[0]; var txtPnlQty = (TextBox)tr.Cells[4].Controls[0]; var txtPcsQty = (TextBox)tr.Cells[5].Controls[0]; var txtRemark = (TextBox)tr.Cells[6].Controls[0]; //取得数据 byte rowId = Convert.ToByte(litRowId.Text); string prevProcName = txtPrevProcName.Text; string lotId = txtLotId.Text; string productNum = txtProductNum.Text; int pnlQty = txtPnlQty.Text.Trim().Length <= 0 ? 0 : int.Parse(txtPnlQty.Text.Trim()); int pcsQty = txtPcsQty.Text.Trim().Length <= 0 ? 0 : int.Parse(txtPcsQty.Text.Trim()); string remark = txtRemark.Text; //存在数据才保存 if (lotId.Length > 0 && productNum.Length > 0 && pnlQty + pcsQty > 0) { //保存到单据内容清单 daContent.Insert( billNum, rowId, prevProcName, lotId, productNum, pnlQty, pcsQty, remark ); //保存到部门批量卡结存清单 daProc.Insert( procName, nextProcName, lotId, productNum, pnlQty, pcsQty, "出库单写入" + (remark.Length > 0 ? ":" + remark : string.Empty), Session["user_name"].ToString(), DateTime.Now, DateTime.Now, null, null, true ); //从尾数结存清单中扣除 if (!ydOperateMantissaLotCard.DecreaseMantissaBalance( daBalance, prevProcName, lotId, pnlQty, pcsQty )) { return false; } } } } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //非数字返回失败 throw new Exception(ex.Message); } } } }
/// <summary> /// 执行删除数据并处理相关数据一致性 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool DeleteData(FormViewDeleteEventArgs e) { //当前单号 string billNum = Convert.ToString(e.Keys[0]); //数据适配器 //当前数据库连接 //当前更新语句对象 using (var da = new v_mantissa_lot_card_outTableAdapter()) using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //试运行 try { //设置事务 da.Transaction = tran; //获取数据 var tab = da.GetDataByBillNum(billNum); //检查是否获取到行 if (tab.Rows.Count == 0) { //显示失败 throw new Exception("当前记录已经被其他用户删除!"); } //数据适配器 using (var daHead = new t_mantissa_lot_card_out_headTableAdapter()) using (var daContent = new t_mantissa_lot_card_out_contentTableAdapter()) using (var daBalance = new t_mantissa_lot_card_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //将之前的单据内容写回 foreach (DataSetMantissaOut.v_mantissa_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripProcBalance( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //修改原下部门批量卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //执行插入到尾数结存清单 daBalance.InsertData( row.prev_proc_name, row.proc_name, row.lot_id, row.product_num, row.pnl_qty, row.pcs_qty, "尾数出库单写回" + (row.IsremarkNull() ? string.Empty : ":" + row.remark), Session["user_name"].ToString() ); } //从单据内容中删除 daContent.DeleteByBillNum(billNum); //从单据表头中删除 daHead.Delete(billNum); } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //非数字返回失败 throw new Exception(ex.Message); } } } }
protected void fvMantissaOutAdd_DataBound(object sender, EventArgs e) { //设置单据清单可见 tabDataListSon.Visible = true; //清空出库单内容清单 for (int i = 0; i < 10; i++) { //清空清单数据到控件 ClearBillContent(i); } //新增状态 switch (fvMantissaOutAdd.CurrentMode) { //新增状态 case FormViewMode.Insert: //当前单号文本框 var tb = (TextBox)fvMantissaOutAdd.FindControl("txtBillDate"); if (tb != null) { //检查用户提供的单据日期参数标识 string strBillDate = Request["bdate"]; DateTime billDate; if (strBillDate != null && strBillDate.Length > 0 && DateTime.TryParse(strBillDate, out billDate)) { //设置默认单据日期为传入的日期 tb.Text = billDate.ToString("yyyy-MM-dd"); } else { //设置默认单据日期为当前日期 tb.Text = DateTime.Now.ToString("yyyy-MM-dd"); } } //当前单号文本框 tb = (TextBox)fvMantissaOutAdd.FindControl("txtBillNum"); if (tb != null) { //户提供的单号参数标识 string strBillNum = Request["bnum"]; Int64 billNum; if (strBillNum != null && strBillNum.Trim().Length >= 12 && Int64.TryParse(strBillNum, out billNum)) { //设置默认单号为传入的单号加1 tb.Text = (billNum + 1).ToString(); } else { //当前单号前缀字符串 string billNumPrefix = DateTime.Now.ToString("yyyyMMdd"); //数据适配器 using (var da = new t_mantissa_lot_card_out_headTableAdapter()) { //获取已经使用的最大单号 string maxBillNum = da.GetMaxBillNumLikeBillNum(billNumPrefix + "%").ToString(); //检测取得的值 if (maxBillNum.Trim().Length < 12) { //执行转换 billNum = Int64.Parse(billNumPrefix + "0001"); //设置默认单号为传入的单号加1 tb.Text = billNum.ToString(); } else { //执行转换 billNum = Int64.Parse(maxBillNum); //设置默认单号为传入的单号加1 tb.Text = (billNum + 1).ToString(); } } } } //下部门名称文本框 tb = (TextBox)fvMantissaOutAdd.FindControl("txtNextProcName"); if (tb != null) { //检查用户提供的部门名称参数标识 string nextProc = Request["nproc"]; if (nextProc != null && nextProc.Length > 0) { //设置默认部门名称 tb.Text = nextProc; } else { //设置默认部门名称 tb.Text = "包装"; } } //当前部门名称 var lit = (Literal)fvMantissaOutAdd.FindControl("litProcName"); if (lit != null) { //session中保存的当前部门 string procName = Session["proc_name"].ToString(); //写入到客户端 lit.Text = procName; } //获取是否传入了多个已入库结存表的id值 string strIdAll = Request["ids"]; if (strIdAll != null && strIdAll.Trim().Length > 0) { //分解出id号 string[] strIds = strIdAll.Split(new char[] { '|' }, 10, StringSplitOptions.RemoveEmptyEntries); //检测id号数量 if (strIds.Length > 0) { //已入库结存表数据适配器 using (var da = new t_mantissa_lot_card_balanceTableAdapter()) { //当前子表行索引 int iRow = 0; //逐行获取数据 foreach (var strId in strIds) { //检测id Int64 id; if (Int64.TryParse(strId, out id)) { //取得数据 var tab = da.GetDataById(id); //检测有无数据 if (tab.Rows.Count > 0) { //首行数据 var row = (DataSetMantissaBalance.t_mantissa_lot_card_balanceRow)tab.Rows[0]; //写入清单数据到控件 SetBillContent(row, iRow); //行号加1 iRow++; } } } } } } break; case FormViewMode.Edit: case FormViewMode.ReadOnly: //设置单据清单不可见 tabDataListSon.Visible = false; //通过出库单号获取数据清单 //取得传入的出库单号 string billNum2 = Request["bnum"]; if (billNum2 != null && billNum2.Length > 0) { //获取清单 using (var daContent = new t_mantissa_lot_card_out_contentTableAdapter()) { //获取数据 var tabContent = daContent.GetDataByBillNum(billNum2); //检测是否找到数据 if (tabContent.Rows.Count > 0) { //设置单据清单可见 tabDataListSon.Visible = true; //把内容写入页面 foreach (DataSetMantissaOut.t_mantissa_lot_card_out_contentRow row in tabContent.Rows) { //当前行号 int i = row.row_id - 1; //写入清单数据到控件 SetBillContent(row, i); } } } } break; } //当前批量卡号文本框获取焦点 var tb2 = (TextBox)fvMantissaOutAdd.FindControl("txtLotId0"); if (tb2 != null) { //设置焦点 tb2.Focus(); } }
/// <summary> /// 根据单号从数据库中删除单据的表头和内容 /// </summary> /// <param name="billNum">单据号</param> private bool DeleteBillByBillNum(string billNum) { //实例化数据适配器 using (var da = new v_mantissa_lot_card_outTableAdapter()) //取得数据库连接 using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; try { //取得之前出库单清单记录 var tab = da.GetDataByBillNum(billNum); //检测是否存在数据 if (tab.Rows.Count > 0) { //单据表头行、内容行、尾数结存和已入库结存数据适配器 using (var daHead = new t_mantissa_lot_card_out_headTableAdapter()) using (var daContent = new t_mantissa_lot_card_out_contentTableAdapter()) using (var daBalance = new t_mantissa_lot_card_balanceTableAdapter()) { //设置连接对象 daHead.Connection = daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //遍历行执行删除各行的结存记录和写回已入库结存记录 foreach (DataSetMantissaOut.v_mantissa_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripProcBalance( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //修改原下部门批量卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //执行插入到尾数结存清单 daBalance.InsertData( row.prev_proc_name, row.proc_name, row.lot_id, row.product_num, row.pnl_qty, row.pcs_qty, "尾数出库单写回" + (row.IsremarkNull() ? string.Empty : ":" + row.remark), Session["user_name"].ToString() ); } //根据出库单号删除全部出库内容记录 daContent.DeleteByBillNum(billNum); //根据出库单号删除全部出库表头记录 daHead.Delete(billNum); } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw new Exception("删除出库单记录出现错误:\n" + ex.Message); } } } }
/// <summary> /// 执行删除数据并处理相关数据一致性 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool DeleteData(ListViewDeleteEventArgs e) { //实例化数据适配器 using (var da = new v_mantissa_lot_card_outTableAdapter()) //取得数据库连接 using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; try { //当前出库单号 string billNum = e.Keys[0].ToString(); //当前行号 byte rowId = Convert.ToByte(e.Keys[1]); //取得该条出库单清单记录 var tab = da.GetDataByBillNumAndRowId(billNum, rowId); //检测是否存在数据 if (tab.Rows.Count <= 0) { throw new Exception( string.Format( "当前单据:\n单号:{0}\n行号:{1}\n已经被其他用户删除!", billNum, rowId ) ); } //单据内容、尾数结存和已入库结存数据适配器 using (var daContent = new t_mantissa_lot_card_out_contentTableAdapter()) using (var daBalance = new t_mantissa_lot_card_balanceTableAdapter()) { //设置连接对象 daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daContent.Transaction = daBalance.Transaction = tran; //遍历行执行删除各行的结存记录和写回已入库结存记录 foreach (DataSetMantissaOut.v_mantissa_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripProcBalance( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //修改原下部门批量卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.next_proc_name, row.lot_id, null, row.pnl_qty, row.pcs_qty )) { return false; } //执行插入到尾数结存清单 daBalance.InsertData( row.prev_proc_name, row.proc_name, row.lot_id, row.product_num, row.pnl_qty, row.pcs_qty, "尾数出库单写回" + (row.IsremarkNull() ? string.Empty : ":" + row.remark), Session["user_name"].ToString() ); } //根据出库单号和行号删除出库记录 daContent.Delete(billNum, rowId); //检测当前出库记录内容行数 int? iCount = daContent.CountByBillNum(billNum); if (iCount.HasValue && iCount.Value <= 0) { //表头适配器 using (var daHead = new t_mantissa_lot_card_out_headTableAdapter()) { //删除表头 daHead.Delete(billNum); } } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw new Exception("删除出库单记录出现错误:\n" + ex.Message); } } } }
/// <summary> /// 设置出库单数据 /// </summary> /// <param name="billNum">出库单单号</param> private void SetDataByMantissaOut(string billNum) { //修改标题 this.Title = "出库单预览" + billNum; //数据适配器 using (var daHead = new t_mantissa_lot_card_out_headTableAdapter()) using (var daContent = new t_mantissa_lot_card_out_contentTableAdapter()) { //获取表头数据 var tabHead = daHead.GetDataByBillNum(billNum); if (tabHead.Rows.Count > 0) { //首行数据 var row = (DataSetMantissaOut.t_mantissa_lot_card_out_headRow)tabHead.Rows[0]; //设置值到控件 litBillType0.Text = litBillType1.Text = litBillType2.Text = "出库"; lblBillDate0.Text = lblBillDate1.Text = lblBillDate2.Text = row.bill_date.ToString("yyyy年MM月dd日"); lblBillNum0.Text = lblBillNum1.Text = lblBillNum2.Text = row.bill_num; litHeadProcName0.Text = litHeadProcName1.Text = litHeadProcName2.Text = "下部门名称:"; lblHeadProcName0.Text = lblHeadProcName1.Text = lblHeadProcName2.Text = row.next_proc_name; lblAddPerson0.Text = lblAddPerson1.Text = lblAddPerson2.Text = row.add_person; lblLastChangeTime0.Text = lblLastChangeTime1.Text = lblLastChangeTime2.Text = row.last_change_time.ToString("yyyy-MM-dd HH:mm:ss"); } //获取内容数据 var tabContent = daContent.GetDataByBillNum(billNum); //当前表格序号 for (int iTable = 0; iTable < 3; iTable++) { //当前表格控件 var tSon = (Table)this.FindControl("tabDataListSon" + iTable); //遍历行 foreach (DataSetMantissaOut.t_mantissa_lot_card_out_contentRow row in tabContent.Rows) { //取得当前行序号 Int32 i = Convert.ToInt32(row.row_id) - 1; //当前行 var rSon = tSon.Rows[i + 1]; //各显示数据的控件 var litLotId = (Literal)rSon.Cells[1].FindControl("litLotId" + i.ToString() + "_" + iTable); var litPrevProcName = (Literal)rSon.Cells[2].FindControl("litPrevProcName" + i.ToString() + "_" + iTable); var litProductNum = (Literal)rSon.Cells[3].FindControl("litProductNum" + i.ToString() + "_" + iTable); var litPnlQty = (Literal)rSon.Cells[4].FindControl("litPnlQty" + i.ToString() + "_" + iTable); var litPcsQty = (Literal)rSon.Cells[5].FindControl("litPcsQty" + i.ToString() + "_" + iTable); var litRemark = (Literal)rSon.Cells[6].FindControl("litRemark" + i.ToString() + "_" + iTable); //设置数据到控件 litLotId.Text = row.lot_id; litPrevProcName.Text = row.prev_proc_name; litProductNum.Text = row.product_num; litPnlQty.Text = row.pnl_qty <= 0 ? string.Empty : row.pnl_qty.ToString(); litPcsQty.Text = row.pcs_qty <= 0 ? string.Empty : row.pcs_qty.ToString(); litRemark.Text = row.IsremarkNull() ? string.Empty : row.remark; } } } }