//导出数据到Excel
protected void lBtnToExcel_Click(object sender, EventArgs e)
{
//获取数据并填充到数据表
using (var da = new t_proc_material_balanceTableAdapter())
{
//检测session中保存的查询条件
object obj = Session["proc_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 [id] desc");
//不显示单价就把单价清空
if (!isShowPrice)
{
//遍历数据表
foreach (DataSetProcMaterialBalance.t_proc_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="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;
}
}
