//导出数据到Excel
protected void lBtnToExcel_Click(object sender, EventArgs e)
{
//获取数据并填充到数据表
using (var da = new v_material_outTableAdapter())
{
//检测session中保存的查询条件
object obj = Session["material_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]");
//不显示单价就把单价清空
if (!isShowPrice)
{
//遍历数据表
foreach (DataSetMaterialOut.v_material_outRow 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(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_outTableAdapter())
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_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("从单据表头中删除失败！");
}
//日期
DateTime billDate = Convert.ToDateTime(e.NewValues["bill_date"]);
//部门序号
byte procId = Convert.ToByte(e.NewValues["proc_id"]);
//部门名称
string procName = Convert.ToString(e.NewValues["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"]);
//含有数据的行数
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 (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="eAdd"></param>
/// <param name="eUpdate"></param>
/// <returns></returns>
private bool CheckQty(FormViewInsertEventArgs eAdd, FormViewUpdateEventArgs eUpdate)
{
//当前验证控件
var cvQty = (CustomValidator)fvProcMaterialBalanceAdd.FindControl("cvQty");
//用户输入的内容
int yearMonthNew;
string procNameNew;
string supplierNameNew, materialTypeNew, materialNameNew, materialSizeNew, unitNew;
decimal decQtyNew, decPriceNew;
//新增时
if (eAdd != null)
{
yearMonthNew = Convert.ToInt32(eAdd.Values["year_month"]);
procNameNew = eAdd.Values["proc_name"] as string;
materialTypeNew = eAdd.Values["material_type"] as string;
supplierNameNew = eAdd.Values["supplier_name"] as string;
materialNameNew = eAdd.Values["material_name"] as string;
materialSizeNew = eAdd.Values["material_size"] as string;
unitNew = eAdd.Values["unit"] as string;
decQtyNew = Convert.ToDecimal(eAdd.Values["qty"]);
decPriceNew = Convert.ToDecimal(eAdd.Values["price"]);
}
//修改时
else
{
yearMonthNew = Convert.ToInt32(eUpdate.NewValues["year_month"]);
procNameNew = eUpdate.NewValues["proc_name"] as string;
materialTypeNew = eUpdate.NewValues["material_type"] as string;
supplierNameNew = eUpdate.NewValues["supplier_name"] as string;
materialNameNew = eUpdate.NewValues["material_name"] as string;
materialSizeNew = eUpdate.NewValues["material_size"] as string;
unitNew = eUpdate.NewValues["unit"] as string;
decQtyNew = Convert.ToDecimal(eUpdate.NewValues["qty"]);
decPriceNew = Convert.ToDecimal(eUpdate.NewValues["price"]);
}
//检测值
var dic = new Dictionary<string, string>()
{
{ "缺少年月", yearMonthNew.ToString () },
{ "缺少部门名称", procNameNew },
{ "缺少物料类型", materialTypeNew },
{ "缺少供应商名称", supplierNameNew },
{ "缺少物料名称", materialNameNew },
{ "缺少数量", decQtyNew.ToString() },
{ "缺少物料单位", unitNew },
{ "缺少单价", decPriceNew.ToString () }
};
//检测文本框输入的值
foreach (var item in dic)
{
if (string.IsNullOrWhiteSpace(item.Value))
{
cvQty.ToolTip = item.Key;
cvQty.IsValid = false;
return false;
}
};
//检测月份
if (yearMonthNew < 201001 || yearMonthNew > 209912)
{
cvQty.ToolTip = "日期输入错误";
cvQty.IsValid = false;
return false;
}
//数据适配器
using (var daBal = new t_proc_material_balanceTableAdapter())
using (var daOut = new v_material_outTableAdapter())
{
//取得年,月
int iYear = yearMonthNew / 100;
int iMonth = yearMonthNew % 100;
//取得本月第一天
var startDate = new DateTime(iYear, iMonth, 1);
//本月最后一天
var endDate = startDate.AddMonths(1).AddDays(-1);
//取得上月
var yearMonthPrev = Convert.ToInt32(startDate.AddDays(-1).ToString("yyyyMM"));
//检测上月结存数量
var qtyBalPrev = (decimal)daBal.SumProcQty(
yearMonthPrev,
procNameNew,
materialTypeNew,
supplierNameNew,
materialNameNew,
materialSizeNew,
unitNew,
decPriceNew
);
//检测本月领用数量
var qtyOut = (decimal)daOut.SumProcQty(
startDate,
endDate,
procNameNew,
materialTypeNew,
supplierNameNew,
materialNameNew,
materialSizeNew,
unitNew,
decPriceNew
);
//检测本月已经录入的结存数量
var qtyBal = (decimal)daBal.SumProcQty(
yearMonthNew,
procNameNew,
materialTypeNew,
supplierNameNew,
materialNameNew,
materialSizeNew,
unitNew,
decPriceNew
);
//总数量
var qty = qtyBalPrev + qtyOut - qtyBal;
//检测数量
if (decQtyNew <= qty)
{
cvQty.IsValid = true;
//返回成功
return true;
}
//新增时
if (eAdd != null)
{
//设置错误
cvQty.ToolTip = "结存数量不能超过上月结存+本月领用之和" + qty.ToString("0.#");
cvQty.IsValid = false;
//返回错误
return false;
}
//修改时
//之前输入内容
int yearMonthOld;
string procNameOld;
string materialTypeOld, supplierNameOld, materialNameOld, materialSizeOld, unitOld;
decimal decQtyOld, decPriceOld;
yearMonthOld = Convert.ToInt32(eUpdate.OldValues["year_month"]);
procNameOld = eUpdate.OldValues["proc_name"] as string;
materialTypeOld = eUpdate.OldValues["material_type"] as string;
supplierNameOld = eUpdate.OldValues["supplier_name"] as string;
materialNameOld = eUpdate.OldValues["material_name"] as string;
materialSizeOld = eUpdate.OldValues["material_size"] as string;
unitOld = eUpdate.OldValues["unit"] as string;
decQtyOld = Convert.ToDecimal(eUpdate.OldValues["qty"]);
decPriceOld = Convert.ToDecimal(eUpdate.OldValues["price"]);
//如果参数有修改直接返回错误
if (yearMonthOld != yearMonthNew ||
procNameOld != procNameNew ||
materialTypeOld != materialTypeNew ||
supplierNameOld != supplierNameNew ||
materialNameOld != materialNameNew ||
materialSizeOld != materialSizeNew ||
unitOld != unitNew ||
decPriceOld != decPriceNew
)
{
//设置错误
cvQty.ToolTip = "结存数量不能超过上月结存+本月领用之和" + qty;
cvQty.IsValid = false;
//返回错误
return false;
}
//用户只是修改了数量,总数量就累加之前的数量
qty += decQtyOld;
//检测数量
if (decQtyNew <= qty)
{
cvQty.IsValid = true;
//返回成功
return true;
}
//设置错误
cvQty.ToolTip = "结存数量不能超过上月结存+本月领用之和" + qty;
cvQty.IsValid = false;
//返回错误
return false;
}
}
/// <summary>
/// 执行删除数据并处理相关数据一致性
/// </summary>
/// <param name="e">传入的带有数据的事件参数</param>
/// <returns></returns>
private bool DeleteData(ListViewDeleteEventArgs e)
{
//实例化数据适配器
using (var da = new v_material_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_material_out_contentTableAdapter())
using (var daBalance = new t_material_balanceTableAdapter())
{
//设置连接对象
daContent.Connection = daBalance.Connection = tran.Connection;
//设置事务
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.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_out_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_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_material_out_headTableAdapter())
using (var daContent = new t_material_out_contentTableAdapter())
using (var daBalance = new t_material_balanceTableAdapter())
{
//设置连接对象
daHead.Connection = daContent.Connection = daBalance.Connection = tran.Connection;
//设置事务
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("删除出仓单记录出现错误:\n" + ex.Message);
}
}
}
}
