//导出数据到Excel protected void lBtnToExcel_Click(object sender, EventArgs e) { //获取数据并填充到数据表 using (var da = new t_material_balanceTableAdapter()) { //检测session中保存的查询条件 object obj = Session["material_balance_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 [material_code]"); //不显示单价就把单价清空 if (!isShowPrice) { //遍历数据表 foreach (DataSetMaterialBalance.t_material_balanceRow row in tab.Rows) { //修改值为空 row.SetpriceNull(); } } //设置列标题 string[] titleCol = ("序号,物料类别,供应商代码,供应商名称,物料代码,物料名称," + "规格,数量,单位,单价(元),备注,录入员,录入时间,修改时间,优先扣数").Split(','); //设置表名称 string titleTab = "原料结存清单"; //临时文件名称 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(ListViewDeleteEventArgs e) { //实例化数据适配器 using (var da = new v_material_inTableAdapter()) //取得数据库连接 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_in_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置连接对象 daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daContent.Transaction = daBalance.Transaction = tran; //遍历行执行删除各行的结存记录 foreach (DataSetMaterialIn.v_material_inRow row in tab.Rows) { //执行扣减结存 if (!ydOperateMaterial.DecreaseMaterialBalance( daBalance, BillType.In, row.supplier_name, row.material_name, row.price, row.qty )) { 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_in_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_inTableAdapter()) //取得数据库连接 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_in_headTableAdapter()) using (var daContent = new t_material_in_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置连接对象 daHead.Connection = daContent.Connection = daBalance.Connection = tran.Connection; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //遍历行执行删除各行的结存记录和写回待入仓记录 foreach (DataSetMaterialIn.v_material_inRow row in tab.Rows) { //执行扣减结存 if (!ydOperateMaterial.DecreaseMaterialBalance( daBalance, BillType.In, row.supplier_name, row.material_name, row.price, row.qty )) { 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"]); //部门序号 byte procId = Convert.ToByte(e.Values["proc_id"]); //部门名称 string procName = Convert.ToString(e.Values["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_material_out_headTableAdapter()) using (var daContent = new t_material_out_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) using (var conn = daHead.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置数据库连接对象 daContent.Connection = daBalance.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.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 txtSupplierCode = (TextBox)tr.Cells[1].Controls[0]; var txtSupplierName = (TextBox)tr.Cells[2].Controls[0]; var txtMaterialCode = (TextBox)tr.Cells[3].Controls[0]; var txtMaterialName = (TextBox)tr.Cells[4].Controls[0]; var txtMaterialSize = (TextBox)tr.Cells[5].Controls[0]; var txtQty = (TextBox)tr.Cells[6].Controls[0]; var txtMaterialUnit = (TextBox)tr.Cells[7].Controls[0]; var hfPrice = (HiddenField)tr.Cells[8].Controls[0]; var txtRemark = (TextBox)tr.Cells[9].Controls[0]; //取得数据 byte rowId = Convert.ToByte(litRowId.Text); string supplierCode = txtSupplierCode.Text; string supplierName = txtSupplierName.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 = hfPrice.Value.Trim().Length <= 0 ? 0m : decimal.Parse(hfPrice.Value.Trim()); string remark = txtRemark.Text; //存在数据才保存 if (supplierCode.Length > 0 && supplierName.Length > 0 && 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, supplierCode, supplierName, materialCode, materialName, materialSize, qty, materialUnit, price, remark ) <= 0) { throw new Exception("保存到单据内容清单失败!"); } //含有数据的行数计数 iCountContent++; } } //保存表头 if (iCountContent > 0) { if (daHead.Insert( billDate, billNum, procId, procName, 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_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_material_out_headTableAdapter()) using (var daContent = new t_material_out_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //遍历行执行添加到结存记录 foreach (DataSetMaterialOut.v_material_outRow row in tab.Rows) { //执行添加结存 if (!ydOperateMaterial.AddMaterialBalance( tran, BillType.Out, 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); } } } }
/// <summary> /// 根据输入的参数值来执行更新数据 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool UpdateData(FormViewUpdateEventArgs e) { //数据适配器 //当前添加语句对象 //当前数据库连接 using (var da = new v_material_backTableAdapter()) using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; //试运行 try { //当前行单号 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_material_back_headTableAdapter()) using (var daContent = new t_material_back_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //将之前的单据内容写回 foreach (DataSetMaterialBack.v_material_backRow row in tab.Rows) { //从原料结存清单中扣除 if (!ydOperateMaterial.DecreaseMaterialBalance( daBalance, BillType.Back, row.supplier_name, row.material_name, row.price, row.qty )) { return false; } } //从单据内容中删除 if (daContent.DeleteByBillNum(billNum) <= 0) { throw new Exception("从单据内容中删除失败!"); } //从单据表头中删除 if (daHead.Delete(billNum) <= 0) { throw new Exception("从单据表头中删除失败!"); } //日期 DateTime billDate = Convert.ToDateTime(e.NewValues["bill_date"]); //单据备注 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"]); //含有数据的行数 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 txtSupplierCode = (TextBox)tr.Cells[1].Controls[0]; var txtSupplierName = (TextBox)tr.Cells[2].Controls[0]; var txtMaterialCode = (TextBox)tr.Cells[3].Controls[0]; var txtMaterialName = (TextBox)tr.Cells[4].Controls[0]; var txtMaterialSize = (TextBox)tr.Cells[5].Controls[0]; var txtQty = (TextBox)tr.Cells[6].Controls[0]; var txtMaterialUnit = (TextBox)tr.Cells[7].Controls[0]; var txtPrice = (TextBox)tr.Cells[8].Controls[0]; var txtRemark = (TextBox)tr.Cells[9].Controls[0]; //取得数据 byte rowId = Convert.ToByte(litRowId.Text); string supplierCode = txtSupplierCode.Text; string supplierName = txtSupplierName.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 (daBalance.InsertData( supplierCode, supplierName, materialCode, materialName, materialSize, qty, materialUnit, price, remark, addPerson, false ) <= 0) { throw new Exception("保存到原料结存清单失败!"); } //保存到单据内容清单 if (daContent.Insert( billNum, rowId, supplierCode, supplierName, materialCode, materialName, materialSize, qty, materialUnit, price, remark ) <= 0) { throw new Exception("保存到单据内容清单失败!"); } //含有数据的行数计数 iCountContent++; } } //保存表头 if (iCountContent > 0) { if (daHead.Insert( billDate, billNum, 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_backTableAdapter()) 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_back_headTableAdapter()) using (var daContent = new t_material_back_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = tran; //将之前的单据内容写回 foreach (DataSetMaterialBack.v_material_backRow row in tab.Rows) { //从原料结存清单中扣除 if (!ydOperateMaterial.DecreaseMaterialBalance( daBalance, BillType.Back, row.supplier_name, row.material_name, row.price, row.qty )) { 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); } } } }
/// <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; //供应商名称 string supplierName = Convert.ToString(e.Values["supplier_name"]); //送货单号 string deliveryNum = Convert.ToString(e.Values["delivery_num"]); //单据备注 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_in_headTableAdapter()) using (var daContent = new t_material_in_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) using (var daSupplier = new t_material_supplierTableAdapter()) 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 = daSupplier.Connection = daName.Connection = daPrice.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = daSupplier.Transaction = daName.Transaction = daPrice.Transaction = tran; //试运行 try { //执行更新到物料名称清单 UpdateToSupplierName( daSupplier, out supplierCode, supplierName, addPerson ); //含有数据的行数 int iCountContent = 0; //遍历子表执行保存 for (int iRow = 0; iRow < 10; iRow++) { //当前列号 var iCol = -1; //子表各控件 var tr = tabDataListSon.Rows[iRow + 1]; var litRowId = (Literal)tr.Cells[++iCol].Controls[0]; var txtMaterialType = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialCode = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialName = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialSize = (TextBox)tr.Cells[++iCol].Controls[0]; var txtQty = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialUnit = (TextBox)tr.Cells[++iCol].Controls[0]; var txtPrice = (TextBox)tr.Cells[++iCol].Controls[0]; var txtRemark = (TextBox)tr.Cells[++iCol].Controls[0]; //取得数据 byte rowId = Convert.ToByte(litRowId.Text); string materialType = txtMaterialType.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 (materialName.Length > 0 && materialUnit.Length > 0 && qty > 0) { //执行更新到物料名称清单 UpdateToMaterialName( daName, materialType, supplierCode, supplierName, ref materialCode, materialName, materialSize, materialUnit, price, addPerson ); //保存到原料结存清单 if (daBalance.InsertData( materialType, supplierCode, supplierName, materialCode, materialName, materialSize, qty, materialUnit, price, remark, addPerson, false ) <= 0) { throw new Exception("保存到原料结存清单失败!"); } //保存到单据内容清单 if (daContent.Insert( billNum, rowId, materialType, materialCode, materialName, materialSize, qty, materialUnit, price, remark ) <= 0) { throw new Exception("保存到单据内容清单失败!"); } //保存到历史单价清单 if (daPrice.UpdateData( price, addPerson, materialType, supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit ) <= 0) { //执行添加 if (daPrice.InsertData( materialType, supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit, price, addPerson ) <= 0) { throw new Exception("保存到物料历史单价清单失败!"); } } //含有数据的行数计数 iCountContent++; } } //保存表头 if (iCountContent > 0) { if (daHead.Insert( billDate, billNum, supplierCode, supplierName, deliveryNum, 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 UpdateData(FormViewUpdateEventArgs e) { //数据适配器 //当前添加语句对象 //当前数据库连接 using (var da = new v_material_inTableAdapter()) using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; //试运行 try { //当前行单号 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_material_in_headTableAdapter()) using (var daContent = new t_material_in_contentTableAdapter()) using (var daBalance = new t_material_balanceTableAdapter()) using (var daName = new t_material_nameTableAdapter()) using (var daPrice = new t_material_price_historyTableAdapter()) { //设置数据库连接 daHead.Connection = daContent.Connection = daBalance.Connection = daName.Connection = daPrice.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daBalance.Transaction = daName.Transaction = daPrice.Transaction = tran; //将之前的单据内容写回 foreach (DataSetMaterialIn.v_material_inRow row in tab.Rows) { //从原料结存清单中扣除 if (!ydOperateMaterial.DecreaseMaterialBalance( daBalance, BillType.In, row.material_type, row.supplier_name, row.material_name, row.price, row.qty )) { return false; } } //从单据内容中删除 if (daContent.DeleteByBillNum(billNum) <= 0) { throw new Exception("从单据内容中删除失败!"); } //从单据表头中删除 if (daHead.Delete(billNum) <= 0) { throw new Exception("从单据表头中删除失败!"); } //日期 DateTime billDate = Convert.ToDateTime(e.NewValues["bill_date"]); //供应商代码 string supplierCode = Convert.ToString(e.NewValues["supplier_code"]); //供应商名称 string supplierName = Convert.ToString(e.NewValues["supplier_name"]); //送货单号 string deliveryNum = Convert.ToString(e.NewValues["delivery_num"]); //单据备注 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"]); //含有数据的行数 int iCountContent = 0; //遍历子表执行保存 for (int iRow = 0; iRow < 10; iRow++) { //列号 var iCol = -1; //子表各控件 var tr = tabDataListSon.Rows[iRow + 1]; var litRowId = (Literal)tr.Cells[++iCol].Controls[0]; var txtMaterialType = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialCode = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialName = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialSize = (TextBox)tr.Cells[++iCol].Controls[0]; var txtQty = (TextBox)tr.Cells[++iCol].Controls[0]; var txtMaterialUnit = (TextBox)tr.Cells[++iCol].Controls[0]; var txtPrice = (TextBox)tr.Cells[++iCol].Controls[0]; var txtRemark = (TextBox)tr.Cells[++iCol].Controls[0]; //取得数据 byte rowId = Convert.ToByte(litRowId.Text); string materialType = txtMaterialType.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 (daBalance.InsertData( materialType, supplierCode, supplierName, materialCode, materialName, materialSize, qty, materialUnit, price, remark, addPerson, false ) <= 0) { throw new Exception("保存到原料结存清单失败!"); } //保存到单据内容清单 if (daContent.Insert( billNum, rowId, materialType, materialCode, materialName, materialSize, qty, materialUnit, price, remark ) <= 0) { throw new Exception("保存到单据内容清单失败!"); } //保存到物料名称清单 if (daName.UpdateData( price, addPerson, materialType, supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit ) <= 0) { //获取最大的物料编码 var maxCode = string.Empty; if (materialCode.Trim().Length > 0) { maxCode = materialCode.Substring(0, 1); } if (maxCode.Length > 0) { maxCode = daName.GetMaxCodeLikeMaterialCode(maxCode + "%").ToString(); } if (maxCode.Length <= 0) { maxCode = daName.GetMaxCode().ToString(); } //最大编码如果没有找到 if (maxCode.Length <= 0) { //设置默认物料代码 materialCode = "A00001"; } else { //设置默认物料代码 materialCode = string.Format( "{0}{1:00000}", maxCode.Substring(0, 1), (Math.Round(ydPublicMethod.Val(maxCode.Substring(1)), 0) + 1) ); } //执行添加 if (daName.InsertData( materialType, supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit, price, addPerson ) <= 0) { throw new Exception("保存到物料名称清单失败!"); } } //保存到历史单价清单 if (daPrice.UpdateData( price, addPerson, materialType, supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit ) <= 0) { //执行添加 if (daPrice.InsertData( materialType, supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit, price, addPerson ) <= 0) { throw new Exception("保存到物料历史单价清单失败!"); } } //含有数据的行数计数 iCountContent++; } } //保存表头 if (iCountContent > 0) { if (daHead.Insert( billDate, billNum, supplierCode, supplierName, deliveryNum, 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="daBalance">操作结存表的数据适配器</param> /// <param name="billType">单据类型</param> /// <param name="materialType">物料类别</param> /// <param name="supplierName">供应商名称</param> /// <param name="materialName">物料名称</param> /// <param name="price">单价</param> /// <param name="qty">数量</param> /// <returns></returns> internal static bool DecreaseMaterialBalance( t_material_balanceTableAdapter daBalance, BillType billType, string materialType, string supplierName, string materialName, decimal price, decimal qty ) { try { //检测是否数量不正确 if (qty < 0) { throw new Exception("执行扣减原料结存数时发生错误:\n待扣减的数量不能小于0!"); } //扣减后剩余数量 object decQty; //扣减后剩余数量 decQty = UpdateBalanceQtyByMaterialName( daBalance.Transaction, billType, materialType, supplierName, materialName, price, -qty ); //检测扣数是否成功 if (decQty == null || decQty == DBNull.Value) { throw new Exception( string.Format( "执行扣减原料结存数时发生错误:\n由供应商 {0} \n提供的物料类别: {1} \n原料 {2} \n单价 {3} 元\n不足扣数 {4:0.####} !", supplierName, materialType, materialName, billType == BillType.Out ? "***" : price.ToString(), -qty ) ); } //检测返回的结果 if (!decimal.TryParse(decQty.ToString(), out qty)) { throw new Exception("执行扣减原料结存数时发生错误:\n原料结存扣数后的返回值不是正确的小数!"); } //检测是否需要递归扣数 if (qty < 0) { //执行递归扣减 if (!DecreaseMaterialBalance( daBalance, billType, materialType, supplierName, materialName, price, -qty )) { throw new Exception("执行扣减原料结存数时发生错误:\n执行递归扣减出错!"); }; } else { //删除小于或等于0的记录清单 daBalance.DeleteEmptyData(); } //返回成功 return true; } catch (Exception ex) { //抛出错误 throw ex; } }
public void ProcessRequest(HttpContext context) { //清空之前数据 context.Response.Clear(); //检测是否含有session if (context.Session.Count < 5) { //跳转 context.Response.Redirect("/Account/Login", true); //停止加载后续内容 context.Response.End(); //直接返回 return; } //执行基类的方法 if (!base.CheckIsLogin(context)) { return; } //当前用户所在部门 string procName = context.Session["proc_name"].ToString(); //检测部门必须为指定部门 if (procName != mustProcName) { throw new Exception(string.Format("当前部门必须为{0}才能获取物料信息!", mustProcName)); } //当前角色id Int16 roleId = Convert.ToInt16(context.Session["role_id"]); //检测是否有权限 if (roleId >= 0 && roleId <= 5) { //允许显示单价 isShowPrice = true; } //当前使用的查询参数类型 var parField = ParameterType.None; //用户传入的参数 string bType = context.Request["btype"]; if (bType == null || bType.Length <= 0) { throw new Exception("未提供单据类型参数btype!"); } bType = bType.ToLower(); BillType billType; if (bType == "in") { billType = BillType.In; } else if (bType == "out") { billType = BillType.Out; } else if (bType == "lose") { billType = BillType.Lose; } else if (bType == "back") { billType = BillType.Back; } else if (bType == "other") { billType = BillType.Other; } else { throw new Exception("未提供正确的单据类型参数btype!"); } string supplierCode = context.Request["scode"]; string supplierName = context.Request["sname"]; string materialCode = context.Request["mcode"]; string materialName = context.Request["mname"]; string materialSize = context.Request["msize"]; string materialUnit = context.Request["munit"]; //检测物料参数 if (supplierCode != null && supplierCode.Length > 0) { //查询字段类型为供应商代码 parField = ParameterType.SupplierCode; //去掉空格 supplierCode = supplierCode.Trim().Replace("*", "%") + "%"; } else if (supplierName != null && supplierName.Length > 0) { //查询字段类型为供应商名称 parField = ParameterType.SupplierName; //设置参数值 supplierName = "%" + supplierName.Trim().Replace("*", "%") + "%"; } else if (materialCode != null && materialCode.Length > 0) { //查询字段类型为供应商名称物料代码 parField = ParameterType.MaterialCode; //设置参数值 materialCode = materialCode.Trim().Replace("*", "%") + "%"; } else if (materialName != null && materialName.Length > 0) { //查询字段类型为物料名称 parField = ParameterType.MaterialName; //设置参数值 materialName = "%" + materialName.Trim().Replace("*", "%") + "%"; } else if (materialSize != null && materialSize.Length > 0) { //查询字段类型为物料规格 parField = ParameterType.MaterialSize; //设置参数值 materialSize = "%" + materialSize.Trim().Replace("*", "%") + "%"; } else if (materialUnit != null && materialUnit.Length > 0) { //查询字段类型为物料单位 parField = ParameterType.MaterialUnit; //设置参数值 materialUnit = "%" + materialUnit.Trim().Replace("*", "%") + "%"; } //当前未使用特定的三种字段类型之一进行查询则不输出值直接退出 if (parField == ParameterType.None) { return; } //待输出到浏览器的数据 string strResult = string.Empty; //检测单据类型 //入仓单 if (billType == BillType.In || billType == BillType.Other) { //在供应商清单记录中查询 if (parField == ParameterType.SupplierCode || parField == ParameterType.SupplierName) { //将查询到的结果保存到泛型变量中 var lSupplier = new List<SupplierLiItem>(); //数据适配器 using (var da = new t_material_supplierTableAdapter()) { //获取数据使用的表 DataSetMaterialSupplier.t_material_supplierDataTable tab; //通过代码查询 if (parField == ParameterType.SupplierCode) { //取得数据 tab = da.GetDataLikeSupplierCode(supplierCode); } //通过名称查询 else { //取得数据 tab = da.GetDataLikeSupplierName(supplierName); } //获取单据数据 AddSupplierListItem(tab, ref lSupplier); } //如果在清单中查询到数据 if (lSupplier.Count > 0) { //将泛型变量各项目放入数组 var itms = new SupplierLiItem[lSupplier.Count]; //复制泛型变量的内容到数组 lSupplier.CopyTo(itms); //循环重新设置单只数 foreach (var itm in itms) { //将实例加入li strResult += itm.ToString(); } } } else { //在物料清单中查询 //将查询到的结果保存到泛型变量中 var lMaterial = new List<MaterialLiItem>(); //在物料结存清单中查询 if (billType == BillType.In) { //数据适配器 using (var da = new t_material_balanceTableAdapter()) { //获取数据使用的表 DataSetMaterialBalance.t_material_balanceDataTable tab; //通过供应商代码查询 if (parField == ParameterType.SupplierCode) { //取得数据 tab = da.GetDataLikeSupplierCode(supplierCode); } //通过供应商名称查询 else if (parField == ParameterType.SupplierName) { //取得数据 tab = da.GetDataLikeSupplierName(supplierName); } //通过物料代码查询 else if (parField == ParameterType.MaterialCode) { //取得数据 tab = da.GetDataLikeMaterialCode(materialCode); } //通过物料名称查询 else if (parField == ParameterType.MaterialName) { //取得数据 tab = da.GetDataLikeMaterialName(materialName); } //通过物料规格查询 else if (parField == ParameterType.MaterialSize) { //取得数据 tab = da.GetDataLikeMaterialSize(materialSize); } //通过物料名称查询 else if (parField == ParameterType.MaterialUnit) { //取得数据 tab = da.GetDataLikeMaterialUnit(materialUnit); } else { throw new Exception("检测到错误的参数名称类型!"); } //获取单据数据 AddMaterialListItem(tab, billType, ref lMaterial); } } //在物料清单中查询 //数据适配器 using (var da = new t_material_nameTableAdapter()) { //获取数据使用的表 DataSetMaterialName.t_material_nameDataTable tab; //通过供应商代码查询 if (parField == ParameterType.SupplierCode) { //取得数据 tab = da.GetDataLikeSupplierCode(supplierCode); } //通过供应商名称查询 else if (parField == ParameterType.SupplierName) { //取得数据 tab = da.GetDataLikeSupplierName(supplierName); } //通过物料代码查询 else if (parField == ParameterType.MaterialCode) { //取得数据 tab = da.GetDataLikeMaterialCode(materialCode); } //通过物料名称查询 else if (parField == ParameterType.MaterialName) { //取得数据 tab = da.GetDataLikeMaterialName(materialName); } //通过物料规格查询 else if (parField == ParameterType.MaterialSize) { //取得数据 tab = da.GetDataLikeMaterialSize(materialSize); } //通过物料名称查询 else if (parField == ParameterType.MaterialUnit) { //取得数据 tab = da.GetDataLikeMaterialUnit(materialUnit); } else { throw new Exception("检测到错误的参数名称类型!"); } //获取单据数据 AddMaterialListItem(tab, billType, ref lMaterial); } //如果在清单中查询到数据 if (lMaterial.Count > 0) { //将泛型变量各项目放入数组 var itms = new MaterialLiItem[lMaterial.Count]; //复制泛型变量的内容到数组 lMaterial.CopyTo(itms); //循环重新设置单只数 foreach (var itm in itms) { //将实例加入li strResult += itm.ToString(); } } } } //出仓单 //盘亏单 else if (billType == BillType.Out || billType == BillType.Lose) { //将查询到的结果保存到泛型变量中 var lMaterial = new List<MaterialLiItem>(); //在物料结存清单中查询 //数据适配器 using (var da = new t_material_balanceTableAdapter()) { //获取数据使用的表 DataSetMaterialBalance.t_material_balanceDataTable tab; //通过供应商代码查询 if (parField == ParameterType.SupplierCode) { //取得数据 tab = da.GetDataLikeSupplierCode(supplierCode); } //通过供应商名称查询 else if (parField == ParameterType.SupplierName) { //取得数据 tab = da.GetDataLikeSupplierName(supplierName); } //通过物料代码查询 else if (parField == ParameterType.MaterialCode) { //取得数据 tab = da.GetDataLikeMaterialCode(materialCode); } //通过物料名称查询 else if (parField == ParameterType.MaterialName) { //取得数据 tab = da.GetDataLikeMaterialName(materialName); } //通过物料规格查询 else if (parField == ParameterType.MaterialSize) { //取得数据 tab = da.GetDataLikeMaterialSize(materialSize); } //通过物料名称查询 else if (parField == ParameterType.MaterialUnit) { //取得数据 tab = da.GetDataLikeMaterialUnit(materialUnit); } else { throw new Exception("检测到错误的参数名称类型!"); } //获取单据数据 AddMaterialListItem(tab, billType, ref lMaterial); } //在物料清单中查询 //数据适配器 using (var da = new t_material_nameTableAdapter()) { //获取数据使用的表 DataSetMaterialName.t_material_nameDataTable tab; //通过供应商代码查询 if (parField == ParameterType.SupplierCode) { //取得数据 tab = da.GetDataLikeSupplierCode(supplierCode); } //通过供应商名称查询 else if (parField == ParameterType.SupplierName) { //取得数据 tab = da.GetDataLikeSupplierName(supplierName); } //通过物料代码查询 else if (parField == ParameterType.MaterialCode) { //取得数据 tab = da.GetDataLikeMaterialCode(materialCode); } //通过物料名称查询 else if (parField == ParameterType.MaterialName) { //取得数据 tab = da.GetDataLikeMaterialName(materialName); } //通过物料规格查询 else if (parField == ParameterType.MaterialSize) { //取得数据 tab = da.GetDataLikeMaterialSize(materialSize); } //通过物料名称查询 else if (parField == ParameterType.MaterialUnit) { //取得数据 tab = da.GetDataLikeMaterialUnit(materialUnit); } else { throw new Exception("检测到错误的参数名称类型!"); } //获取单据数据 AddMaterialListItem(tab, billType, ref lMaterial); } //如果在清单中查询到数据 if (lMaterial.Count > 0) { //将泛型变量各项目放入数组 var itms = new MaterialLiItem[lMaterial.Count]; //复制泛型变量的内容到数组 lMaterial.CopyTo(itms); //循环重新设置单只数 foreach (var itm in itms) { //将实例加入li strResult += itm.ToString(); } } } //盘盈单 else if (billType == BillType.Back) { //在物料盘亏清单中查询 //将查询到的结果保存到泛型变量中 var lMaterial = new List<MaterialLiItem>(); //数据适配器 using (var da = new t_material_lose_contentTableAdapter()) { //获取数据使用的表 DataSetMaterialLose.t_material_lose_contentDataTable tab; //通过供应商代码查询 if (parField == ParameterType.SupplierCode) { //取得数据 tab = da.GetDataLikeSupplierCode(supplierCode); } //通过供应商名称查询 else if (parField == ParameterType.SupplierName) { //取得数据 tab = da.GetDataLikeSupplierName(supplierName); } //通过物料代码查询 else if (parField == ParameterType.MaterialCode) { //取得数据 tab = da.GetDataLikeMaterialCode(materialCode); } //通过物料名称查询 else if (parField == ParameterType.MaterialName) { //取得数据 tab = da.GetDataLikeMaterialName(materialName); } //通过物料规格查询 else if (parField == ParameterType.MaterialSize) { //取得数据 tab = da.GetDataLikeMaterialSize(materialSize); } //通过物料名称查询 else if (parField == ParameterType.MaterialUnit) { //取得数据 tab = da.GetDataLikeMaterialUnit(materialUnit); } else { throw new Exception("检测到错误的参数名称类型!"); } //获取单据数据 AddMaterialListItem(tab, billType, ref lMaterial); } //在物料清单中查询 //数据适配器 using (var da = new t_material_nameTableAdapter()) { //获取数据使用的表 DataSetMaterialName.t_material_nameDataTable tab; //通过供应商代码查询 if (parField == ParameterType.SupplierCode) { //取得数据 tab = da.GetDataLikeSupplierCode(supplierCode); } //通过供应商名称查询 else if (parField == ParameterType.SupplierName) { //取得数据 tab = da.GetDataLikeSupplierName(supplierName); } //通过物料代码查询 else if (parField == ParameterType.MaterialCode) { //取得数据 tab = da.GetDataLikeMaterialCode(materialCode); } //通过物料名称查询 else if (parField == ParameterType.MaterialName) { //取得数据 tab = da.GetDataLikeMaterialName(materialName); } //通过物料规格查询 else if (parField == ParameterType.MaterialSize) { //取得数据 tab = da.GetDataLikeMaterialSize(materialSize); } //通过物料名称查询 else if (parField == ParameterType.MaterialUnit) { //取得数据 tab = da.GetDataLikeMaterialUnit(materialUnit); } else { throw new Exception("检测到错误的参数名称类型!"); } //获取单据数据 AddMaterialListItem(tab, billType, ref lMaterial); } //如果在清单中查询到数据 if (lMaterial.Count > 0) { //将泛型变量各项目放入数组 var itms = new MaterialLiItem[lMaterial.Count]; //复制泛型变量的内容到数组 lMaterial.CopyTo(itms); //循环重新设置单只数 foreach (var itm in itms) { //将实例加入li strResult += itm.ToString(); } } } else { throw new Exception("提供的单据类型错误!"); } //加入ul头尾 strResult = "<ul>\n" + strResult + "</ul>\n"; //写入数据 context.Response.Write(strResult); }