//导出数据到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;
}
}
}
}
