protected void btnCalculate_Click(object sender, EventArgs e)
{
//执行计算结存数据
//当前输入的月份
var yearMonth = txtCalculateYearMonth.Text;
DateTime dtStart, dtEnd;
//检测输入
if (yearMonth.Length != 6 || !DateTime.TryParse(yearMonth.Insert(4, "/") + "/01", out dtStart))
{
throw new Exception("您输入的年月不符合规则！");
}
//结束日期
dtEnd = dtStart.AddMonths(1).AddDays(-1);
//当月
var iYearMonth = int.Parse(yearMonth);
//上月
var iYearMonthPrev = int.Parse(dtStart.AddMonths(-1).ToString("yyyyMM"));
//数据适配器
using (var da = new t_material_stocktakingTableAdapter())
using (var conn = da.Connection)
{
//打开数据连接
conn.Open();
//开启事务
using (var tran = conn.BeginTransaction())
{
try
{
//设置事务
da.Transaction = tran;
//删除原来月底结存数据
da.DeleteByYearMonth(iYearMonth);
//写入数据到月底结存
da.InsertFromInOutLoseBack(
iYearMonth,
Session["user_name"].ToString(),
iYearMonthPrev,
dtStart,
dtEnd
);
//提交事务
tran.Commit();
}
catch (Exception ex)
{
//回滚事务
tran.Rollback();
//抛出错误
throw ex;
}
}
}
//设置查询条件
Session["material_stocktaking_where_content"] = " where [year_month]='" + iYearMonth + "'";
//重新绑定数据
lvMaterialStocktaking.DataBind();
}
//导出数据到Excel
protected void lBtnToExcel_Click(object sender, EventArgs e)
{
//获取数据并填充到数据表
using (var da = new t_material_stocktakingTableAdapter())
{
//检测session中保存的查询条件
object obj = Session["material_stocktaking_where_content"];
string str = string.Empty;
if (obj != null && obj.ToString().Length > 0)
{
str = obj.ToString();
}
//获取前1万行数据
var tab = da.GetPagedData(0, 50000, str, "order by [id]");
//不显示单价就把单价清空
if (!isShowPrice)
{
//遍历数据表
foreach (DataSetMaterialStocktaking.t_material_stocktakingRow 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();
}
}
}