//导出数据到Excel protected void lBtnToExcel_Click(object sender, EventArgs e) { //获取数据并填充到数据表 using (var da = new t_material_price_historyTableAdapter()) { //检测session中保存的查询条件 object obj = Session["material_price_history_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] desc"); //不显示单价就把单价清空 if (!isShowPrice) { //遍历数据表 foreach (DataSetMaterialPriceHistory.t_material_price_historyRow 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> /// 根据输入的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 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="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool UpdateData(FormViewUpdateEventArgs e) { //检测是否更改了需要更新到其他部门的数据内容 if (e.OldValues["price"].Equals(e.NewValues["price"])) { //执行只更新本物料记录 e.Cancel = false; //更新事件继续执行 return false; } //数据适配器 //当前添加语句对象 //当前数据库连接 using (var da = new t_material_nameTableAdapter()) using (var daPrice = new t_material_price_historyTableAdapter()) using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; daPrice.Transaction = tran; //试运行 try { //取得数据 string supplierCode = e.NewValues["supplier_code"].ToString(); string supplierName = e.NewValues["supplier_name"].ToString(); string materialCode = Convert.ToString(e.Keys[0]); string materialName = e.NewValues["material_name"].ToString(); string materialSize = e.NewValues["material_size"].ToString(); string description = e.NewValues["description"].ToString(); string materialUnit = e.NewValues["unit"].ToString(); decimal price = e.NewValues["price"].ToString().Trim().Length <= 0 ? 0m : decimal.Parse(e.NewValues["price"].ToString().Trim()); bool isMustShow = Convert.ToBoolean(e.NewValues["is_must_show"]); string address = e.NewValues["address"].ToString(); string remark = e.NewValues["remark"].ToString(); string addPerson = e.NewValues["add_person"].ToString(); DateTime addTime = Convert.ToDateTime(e.NewValues["add_time"].ToString()); DateTime lastChangeTime = Convert.ToDateTime(e.NewValues["last_change_time"].ToString()); //执行更新 if (da.Update( supplierCode, supplierName, materialName, materialSize, description, materialUnit, price, isMustShow, address, remark, addPerson, addTime, lastChangeTime, materialCode ) <= 0) { //抛出错误 throw new Exception("更新数据到物料名称清单记录失败!"); } //保存到历史单价清单 if (daPrice.UpdateData( price, addPerson, supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit ) <= 0) { //执行添加 if (daPrice.InsertData( supplierCode, supplierName, materialCode, materialName, materialSize, materialUnit, price, addPerson ) <= 0) { throw new Exception("保存到物料历史单价清单失败!"); } } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw ex; } } } }
/// <summary> /// 根据输入的LOT格式来保存多条数据到数据库 /// </summary> /// <param name="e">传入的带有数据的事件参数</param> /// <returns></returns> private bool InsertData(FormViewInsertEventArgs e) { //数据适配器 //当前添加语句对象 //当前数据库连接 using (var da = new t_material_nameTableAdapter()) using (var daHistory = new t_material_price_historyTableAdapter()) using (var cmd = da.Adapter.InsertCommand) using (var conn = da.Connection) { //打开数据库连接 conn.Open(); //开启事务 using (var tran = conn.BeginTransaction()) { //设置事务 da.Transaction = tran; daHistory.Transaction = tran; //试运行 try { //循环设置参数值 for (int i = 0; i < cmd.Parameters.Count; i++) { //当前参数对象 var par = cmd.Parameters[i]; //设置参数值 par.Value = e.Values[i]; } //执行查询 if (cmd.ExecuteNonQuery() == 0) { //抛出错误 throw new Exception("添加数据到物料名称清单记录失败!"); } //保存到历史记录 if (daHistory.Insert( e.Values["supplier_code"].ToString(), e.Values["supplier_name"].ToString(), e.Values["material_code"].ToString(), e.Values["material_name"].ToString(), e.Values["material_size"].ToString(), e.Values["description"].ToString(), e.Values["unit"].ToString(), e.Values["price"] as decimal?, e.Values["remark"].ToString(), e.Values["add_person"].ToString(), Convert.ToDateTime(e.Values["add_time"]) ) <= 0) { //抛出错误 throw new Exception("写入单价历史记录失败!"); } //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw ex; } } } }