/// <summary> /// 根据单号从数据库中删除单据的表头和内容 /// </summary> /// <param name="billNum">单据号</param> private bool DeleteBillByBillNum(string billNum) { //实例化数据适配器 using (var da = new v_complete_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_complete_lot_card_out_headTableAdapter()) using (var daContent = new t_complete_lot_card_out_contentTableAdapter()) using (var daBalance = new t_complete_lot_card_balanceTableAdapter()) { //设置连接对象 daHead.Connection = daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //遍历行执行删除各行的结存记录和写回已入库结存记录 foreach (DataSetCompleteOut.v_complete_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripDeptBalance( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //修改原下部门lot卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //执行插入到成品结存清单 daBalance.InsertData( row.prev_dept_name, row.dept_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_complete_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_complete_lot_card_out_contentTableAdapter()) using (var daBalance = new t_complete_lot_card_balanceTableAdapter()) { //设置连接对象 daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daContent.Transaction = daBalance.Transaction = tran; //遍历行执行删除各行的结存记录和写回已入库结存记录 foreach (DataSetCompleteOut.v_complete_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripDeptBalance( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //修改原下部门lot卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //执行插入到成品结存清单 daBalance.InsertData( row.prev_dept_name, row.dept_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 = (int?)daContent.CountByBillNum(billNum); if (iCount.HasValue && iCount.Value <= 0) { //表头适配器 using (var daHead = new t_complete_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="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool UpdateData(FormViewUpdateEventArgs e) { //数据适配器 //当前添加语句对象 //当前数据库连接 using (var da = new v_complete_lot_card_outTableAdapter()) using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; //试运行 try { //部门名称 string deptName = e.OldValues["dept_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_complete_lot_card_out_headTableAdapter()) using (var daContent = new t_complete_lot_card_out_contentTableAdapter()) using (var daBalance = new t_complete_lot_card_balanceTableAdapter()) using (var daDept = new t_dept_lot_card_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = daDept.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = daDept.Transaction = tran; //将之前的单据内容写回 foreach (DataSetCompleteOut.v_complete_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripDeptBalance( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //修改原下部门lot卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //执行插入到成品结存清单 daBalance.InsertData( row.prev_dept_name, row.dept_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 nextDeptName = Convert.ToString(e.NewValues["next_dept_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, deptName, nextDeptName, 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 txtPrevDeptName = (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 prevDeptName = txtPrevDeptName.Text; string lotId = txtLotId.Text; string productNum = txtProductNum.Text; Int16 pnlQty = txtPnlQty.Text.Trim().Length <= 0 ? (Int16)0 : Int16.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, prevDeptName, lotId, productNum, pnlQty, pcsQty, remark ); //保存到部门lot卡结存清单 daDept.Insert( deptName, nextDeptName, lotId, productNum, pnlQty, pcsQty, "出库单写入" + (remark.Length > 0 ? ":" + remark : string.Empty), true, Session["user_name"].ToString(), DateTime.Now, DateTime.Now ); //从成品结存清单中扣除 if (!ydOperateCompleteLotCard.DecreaseCompleteBalance( daBalance, prevDeptName, lotId, pnlQty, pcsQty )) { return false; } } } } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //非数字返回失败 throw new Exception(ex.Message); } } } }
//导出数据到Excel protected void lBtnToExcel_Click(object sender, EventArgs e) { //获取数据并填充到数据表 using (var da = new v_complete_lot_card_outTableAdapter()) { //检测session中保存的查询条件 object obj = Session["complete_lot_card_out_where_content"]; string str = string.Empty; if (obj != null && obj.ToString().Length > 0) { str = obj.ToString(); } //获取前1万行数据 var tab = da.GetPagedData(0, 10000, str, "order by [bill_num] desc,[row_id]"); //设置列标题 string[] titleCol = ("单据日期,出库单号,部门名称,下部门,行号,上部门,lot卡号,生产编号,pnl数量,pcs数量," + "备注,单据备注,录入员,录入时间,修改时间").Split(','); //设置表名称 string titleTab = "成品出库lot卡清单"; //临时文件名称 string fileName = titleTab + DateTime.Now.ToString("yyMMddHHmmss") + ".xls"; //执行导出数据到excel bool isSuccess = ydPublicMethod.DataTable2Excel(tab, titleCol, ref fileName, titleTab); //如果成功则跳转到指定页面 if (isSuccess) { Response.Redirect(fileName); //停止加载后续内容 Response.End(); } } }
/// <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_complete_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_complete_lot_card_out_headTableAdapter()) using (var daContent = new t_complete_lot_card_out_contentTableAdapter()) using (var daBalance = new t_complete_lot_card_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //将之前的单据内容写回 foreach (DataSetCompleteOut.v_complete_lot_card_outRow row in tab.Rows) { //检测是否超过原下部门余数 if (ydOperateBalanceLotCrad.IsOutstripDeptBalance( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //修改原下部门lot卡余数 if (!ydOperateBalanceLotCrad.UpdateBalanceAfterInsert( tran, row.dept_name, row.next_dept_name, row.lot_id, row.pnl_qty, row.pcs_qty, true )) { return false; } //执行插入到成品结存清单 daBalance.InsertData( row.prev_dept_name, row.dept_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); } } } }