/// <summary> /// 执行删除数据并处理相关数据一致性 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool DeleteData(ListViewDeleteEventArgs e) { //实例化数据适配器 using (var da = new v_material_returnTableAdapter()) //取得数据库连接 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_material_return_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置连接对象 daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daContent.Transaction = daBalance.Transaction = tran; //遍历行执行各行写回结存记录 foreach (DataSetMaterialReturn.v_material_returnRow row in tab.Rows) { //执行添加到结存 if (!ydOperateMaterial.AddMaterialBalance( tran, BillType.Return, row.bill_num, row.row_id )) { return false; } } //根据退货单号和行号删除退货记录 if (daContent.Delete(billNum, rowId) <= 0) { throw new Exception("删除单据内容记录失败!"); } //检测当前退货记录内容行数 int? iCount = (int?)daContent.CountByBillNum(billNum); if (iCount.HasValue && iCount.Value <= 0) { //表头适配器 using (var daHead = new t_material_return_headTableAdapter()) { //删除表头 if (daHead.Delete(billNum) <= 0) { throw new Exception("删除单据表头记录失败!"); } } } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw new Exception("删除退货单记录出现错误:\n" + ex.Message); } } } }
/// <summary> /// 根据单号从数据库中删除单据的表头和内容 /// </summary> /// <param name="billNum">单据号</param> private bool DeleteBillByBillNum(string billNum) { //实例化数据适配器 using (var da = new v_material_returnTableAdapter()) //取得数据库连接 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_material_return_headTableAdapter()) using (var daContent = new t_material_return_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置连接对象 daHead.Connection = daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //遍历行执行删除各行的结存记录和写回待退货记录 foreach (DataSetMaterialReturn.v_material_returnRow row in tab.Rows) { //执行添加到结存 if (!ydOperateMaterial.AddMaterialBalance( tran, BillType.Return, row.bill_num, row.row_id )) { return false; } } //根据退货单号删除全部退货内容记录 if (daContent.DeleteByBillNum(billNum) <= 0) { throw new Exception("根据单号删除全部退货内容记录失败!"); } //根据退货单号删除全部退货表头记录 if (daHead.Delete(billNum) <= 0) { throw new Exception("根据单号删除全部退货表头记录失败!"); } } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw new Exception("删除退货单记录出现错误:\n" + ex.Message); } } } }
/// <summary> /// 根据输入的LOT格式来保存多条数据到数据库 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool InsertData(FormViewInsertEventArgs e) { //日期 DateTime billDate = Convert.ToDateTime(e.Values["bill_date"]); //单号 string billNum = Convert.ToString(e.Values["bill_num"]); //供应商代码 string supplierCode = Convert.ToString(e.Values["supplier_code"]); //供应商名称 string supplierName = Convert.ToString(e.Values["supplier_name"]); //付款方式 string returnWay = Convert.ToString(e.Values["return_way"]); //单据备注 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_material_return_headTableAdapter()) using (var daContent = new t_material_return_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) using (var daName = new t_material_nameTableAdapter()) using (var daPrice = new t_material_price_historyTableAdapter()) using (var conn = daHead.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置数据库连接对象 daContent.Connection = daBalance.Connection = daName.Connection = daPrice.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = daName.Transaction = daPrice.Transaction = tran; //试运行 try { //含有数据的行数 int iCountContent = 0; //遍历子表执行保存 for (int iRow = 0; iRow < 10; iRow++) { //子表各控件 var tr = tabDataListSon.Rows[iRow + 1]; var litRowId = (Literal)tr.Cells[0].Controls[0]; var txtMaterialCode = (TextBox)tr.Cells[1].Controls[0]; var txtMaterialName = (TextBox)tr.Cells[2].Controls[0]; var txtMaterialSize = (TextBox)tr.Cells[3].Controls[0]; var txtQty = (TextBox)tr.Cells[4].Controls[0]; var txtMaterialUnit = (TextBox)tr.Cells[5].Controls[0]; var txtPrice = (TextBox)tr.Cells[6].Controls[0]; var txtRemark = (TextBox)tr.Cells[7].Controls[0]; //取得数据 byte rowId = Convert.ToByte(litRowId.Text); string materialCode = txtMaterialCode.Text; string materialName = txtMaterialName.Text; string materialSize = txtMaterialSize.Text; decimal qty = txtQty.Text.Trim().Length <= 0 ? 0m : decimal.Parse(txtQty.Text.Trim()); string materialUnit = txtMaterialUnit.Text; decimal price = txtPrice.Text.Trim().Length <= 0 ? 0m : decimal.Parse(txtPrice.Text.Trim()); string remark = txtRemark.Text; //存在数据才保存 if (materialCode.Length > 0 && materialName.Length > 0 && materialUnit.Length > 0 && qty > 0) { //从原料结存清单扣数 if (!ydOperateMaterial.DecreaseMaterialBalance( daBalance, BillType.Out, supplierName, materialName, price, qty )) { return false; } //保存到单据内容清单 if (daContent.Insert( billNum, rowId, materialCode, materialName, materialSize, qty, materialUnit, price, remark ) <= 0) { throw new Exception("保存到单据内容清单失败!"); } //含有数据的行数计数 iCountContent++; } } //保存表头 if (iCountContent > 0) { if (daHead.Insert( billDate, billNum, supplierCode, supplierName, returnWay, billRemark, addPerson, addTime, lastChangeTime ) <= 0) { throw new Exception("保存到单据表头清单失败!"); } } //提交事务 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_material_returnTableAdapter()) 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_material_return_headTableAdapter()) using (var daContent = new t_material_return_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //将之前的单据内容写回 foreach (DataSetMaterialReturn.v_material_returnRow row in tab.Rows) { //执行添加到结存 if (!ydOperateMaterial.AddMaterialBalance( tran, BillType.Return, row.bill_num, row.row_id )) { return false; } } //从单据内容中删除 if (daContent.DeleteByBillNum(billNum) <= 0) { throw new Exception("删除单据内容记录失败!"); } //从单据表头中删除 if (daHead.Delete(billNum) <= 0) { throw new Exception("删除单据表头记录失败!"); } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //非数字返回失败 throw new Exception(ex.Message); } } } }
protected void fvMaterialReturnAdd_DataBound(object sender, EventArgs e) { //设置单据清单可见 tabDataListSon.Visible = true; //清空退货单内容清单 for (int i = 0; i < 10; i++) { //清空清单数据到控件 ClearBillContent(i); } //新增状态 switch (fvMaterialReturnAdd.CurrentMode) { //新增状态 case FormViewMode.Insert: //当前单号文本框 var tb = (TextBox)fvMaterialReturnAdd.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)fvMaterialReturnAdd.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_material_return_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)fvMaterialReturnAdd.FindControl("txtSupplierCode"); if (tb != null) { //检查用户提供的供应商代码参数标识 string supplierCode = Request["scode"]; if (supplierCode != null && supplierCode.Length > 0) { //设置默认供应商代码 tb.Text = supplierCode; } else { //设置默认供应商代码 tb.Text = string.Empty; } } //供应商名称文本框 tb = (TextBox)fvMaterialReturnAdd.FindControl("txtSupplierName"); if (tb != null) { //检查用户提供的供应商名称参数标识 string supplierName = Request["sname"]; if (supplierName != null && supplierName.Length > 0) { //设置默认供应商名称 tb.Text = supplierName; } else { //设置默认供应商名称 tb.Text = string.Empty; } } //付款方式文本框 tb = (TextBox)fvMaterialReturnAdd.FindControl("txtReturnWay"); if (tb != null) { //检查用户提供的付款方式参数标识 string returnWay = Request["way"]; if (returnWay != null && returnWay.Length > 0) { //设置默认供应商名称 tb.Text = returnWay; } else { //设置默认付款方式 tb.Text = "退货"; } } 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_material_return_contentTableAdapter()) { //获取数据 var tabContent = daContent.GetDataByBillNum(billNum2); //检测是否找到数据 if (tabContent.Rows.Count > 0) { //设置单据清单可见 tabDataListSon.Visible = true; //把内容写入页面 foreach (DataSetMaterialReturn.t_material_return_contentRow row in tabContent.Rows) { //当前行号 int i = row.row_id - 1; //写入清单数据到控件 SetBillContent(row, i); } } } } break; } //当前物料代码文本框获取焦点 var tb2 = (TextBox)fvMaterialReturnAdd.FindControl("txtMaterialCode0"); if (tb2 != null) { //设置焦点 tb2.Focus(); } }
/// <summary> /// 设置退货单数据 /// </summary> /// <param name="strBillNum">退货单单号清单</param> private void GetMaterialData(string strBillNum) { Int64 iBillNum; var l = new List<string>(); if (strBillNum != null && strBillNum.Length > 0) { //分解单号 string[] billNums = strBillNum.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); //遍历 foreach (var billNum in billNums) { if (Int64.TryParse(billNum, out iBillNum)) { l.Add(iBillNum.ToString()); } } } if (l.Count <= 0) { Response.Write("未检测到传入正确的单据编号!"); return; } //修改标题 this.Title = "退货单预览" + strBillNum; //设置公司名称 if (Session["user_name"].ToString() == "yd") { strCompanyName = "某某某"; } else { strCompanyName = "通宇"; } //表头行各单元格css cssClasses = ("rowidtdth,materialcodetdth,materialnametdth," + "materialsizetdth,qtytdth,materialunittdth,remarktdth").Split(','); //表头行各单元格显示的字符 showTexts = "行号,物料代码,物料名称,规格,数量,单位,备注".Split(','); //数据适配器 using (var daHead = new t_material_return_headTableAdapter()) using (var daContent = new t_material_return_contentTableAdapter()) using (var conn = daHead.Connection) { //设置数据库连接对象 daContent.Connection = conn; //打开数据库连接 conn.Open(); //初始化表头表身 tabHead = new DataSetMaterialReturn.t_material_return_headDataTable(); tabContent = new DataSetMaterialReturn.t_material_return_contentDataTable(); //设置填充不清除之前数据 daHead.ClearBeforeFill = false; daContent.ClearBeforeFill = false; //遍历获取数据 foreach (var billNum in l) { //获取表头数据 daHead.FillDataByBillNum(tabHead, billNum); //获取表身数据 daContent.FillDataByBillNum(tabContent, billNum); } } }