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