//导出数据到Excel protected void lBtnToExcel_Click(object sender, EventArgs e) { //获取数据并填充到数据表 using (var da = new t_delivery_bill_lot_cardTableAdapter()) { //检测session中保存的查询条件 object obj = Session["delivery_lot_card_mgr_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"); //设置列标题 string[] titleCol = "序号,送货单序号,行号,批量卡序号,pcs数量,备注".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(); } } }
//根据传入的参数处理事情 public void ProcessRequest(HttpContext context) { //清空之前数据 context.Response.Clear(); //检测是否含有session if (context.Session.Count < 5) { //跳转 context.Response.Redirect("/Account/Login", true); //停止加载后续内容 context.Response.End(); //直接返回 return; } //执行基类的方法 if (!base.CheckIsLogin(context)) { return; } //用户传入的生产编号 string procNum = context.Request["pnum"]; //检测生产编号 if (procNum == null || procNum.Length == 0) { context.Response.Write("未提供生产编号参数!"); return; } //用户传入的送货单编号 string billNum = context.Request["bnum"]; //检测送货单编号 if (billNum == null || billNum.Length == 0) { billNum = string.Empty; } //用户传入的已经存在的批量卡和数量 string lotCardList = context.Request["lotlist"]; //检测批量卡和数量 if (lotCardList == null || lotCardList.Length == 0) { lotCardList = string.Empty; } //转换字符串 lotCardList = lotCardList.Replace("|l|", "<").Replace("|g|", ">"); //将查询到的结果保存到字典变量中 var dic = new Dictionary<string, LiItem>(); //数据适配器 using (var da = new t_complete_lot_card_waitTableAdapter()) { //获取数据 var tab = da.GetDataByProductNum(procNum); //获取当前送货单中的批量卡清单 var tabDeliveryLotCard = new DataSetDeliveryBillMgr.t_delivery_bill_lot_cardDataTable(); //传入的送货单号大于0才获取之前该送货单的交货批量卡清单 if (billNum.Length > 0) { //数据适配器获取之前的清单 using (var daDeliveryLotCard = new t_delivery_bill_lot_cardTableAdapter()) { daDeliveryLotCard.FillByBillNum(tabDeliveryLotCard, billNum); } } //获取单据数据 AddLotListWaitItem(tab, ref dic, lotCardList, tabDeliveryLotCard); } //待输出到浏览器的数据 //表格的行 string strResult = string.Empty; //将数据遍历写入页面 if (dic.Count <= 0) { //显示未找到数据 strResult = "在待入仓清单中未找到该生产编号的批量卡数据!"; } else { //实例化stringbuild类 var sb = new StringBuilder(); //加入全选按钮 sb.Append("<input class='yd-btn-select-all-lot-card yd-lot-card-cmd-btn' type='button' value='全选/全不选' />"); //加入自动识别扣数按钮 sb.Append("<input class='yd-btn-auto-lot-card yd-lot-card-cmd-btn' type='button' value='智能识别' title='根据该生产编号输入的总数量自动填写' />"); //加入筛选按钮 sb.Append("<input class='yd-tb-filter-lot-id' type='text' />"); sb.Append("<input class='yd-btn-filter-lot-card yd-lot-card-cmd-btn' type='button' value='着色筛选' />"); //加入确认按钮 sb.Append("<input class='yd-accept-lot-card-btn yd-lot-card-cmd-btn' type='button' value='确认(A)' accesskey='A' title='也可按Alt+A执行' />"); sb.Append("<hr />\n"); //加入外层div开头 sb.Append("<div class='yd-div-lot-card-list'>\n"); //循环设置取得的批量卡清单 foreach (var itm in dic) { //将实例加入项目 sb.Append(itm.Value.ToString()); } //加入外层div尾 sb.Append("</div>\n"); //取得最终结果 strResult = sb.ToString(); } //写入数据 context.Response.Write(strResult); }
//根据传入的参数处理事情 public void ProcessRequest(HttpContext context) { //清空之前数据 context.Response.Clear(); //检测是否含有session if (context.Session.Count < 5) { //跳转 context.Response.Redirect("/Account/Login", true); //停止加载后续内容 context.Response.End(); //直接返回 return; } //执行基类的方法 if (!base.CheckIsLogin(context)) { return; } //用户传入的生产编号 string proNum = context.Request["pnum"]; //检测生产编号 if (proNum == null || proNum.Length == 0) { context.Response.Write("未提供生产编号参数!"); return; } //用户传入的送货单编号 string billNum = context.Request["bnum"]; //检测送货单编号 if (billNum == null || billNum.Length == 0) { billNum = string.Empty; } //用户传入的已经存在的lot卡和数量 string lotCardList = context.Request["lotlist"]; //检测lot卡和数量 if (lotCardList == null || lotCardList.Length == 0) { lotCardList = string.Empty; } //转换字符串 lotCardList = lotCardList.Replace("|l|", "<").Replace("|g|", ">"); //将查询到的结果保存到字典变量中 var dic = new Dictionary<string, LiItem>(); //数据适配器 using (var da = new t_complete_lot_card_waitTableAdapter()) { //获取数据 var tab = da.GetDataByProductNum(proNum); //获取当前送货单中的lot卡清单 var tabDeliveryLotCard = new DataSetDeliveryBillMgr.t_delivery_bill_lot_cardDataTable(); //传入的送货单号大于0才获取之前该送货单的交货lot卡清单 if (billNum.Length > 0) { //数据适配器获取之前的清单 using (var daDeliveryLotCard = new t_delivery_bill_lot_cardTableAdapter()) { daDeliveryLotCard.FillByBillNum(tabDeliveryLotCard, billNum); } } //获取单据数据 AddLotListWaitItem(tab, ref dic, lotCardList, tabDeliveryLotCard); } //待输出到浏览器的数据 //表格的行 string strResult = string.Empty; //将数据遍历写入页面 if (dic.Count <= 0) { //显示未找到数据 strResult = "在待入仓清单中未找到该生产编号的lot卡数据!"; } else { //实例化stringbuild类 var sb = new StringBuilder(); //加入全选按钮 sb.Append("<input class='selectalllotcardbtn lotcardcmdbtn' type='button' value='全选/全不选' />"); //加入自动识别扣数按钮 sb.Append("<input class='autolotcardbtn lotcardcmdbtn' type='button' value='智能识别' title='根据该生产编号输入的总数量自动填写' />"); //加入筛选按钮 sb.Append("<input class='filterlotcardtb' type='text' />"); sb.Append("<input class='filterlotcardbtn lotcardcmdbtn' type='button' value='着色筛选' />"); //加入确认按钮 sb.Append("<input class='acceptlotcardbtn lotcardcmdbtn' type='button' value='确认(A)' accesskey='A' title='也可按Alt+A执行' />"); sb.Append("<hr />\n"); //加入表格table头 sb.Append("<table class='lotcardlisttab'>\n"); sb.Append("<tbody>\n"); //当前行第几张lot卡 int iCountLot = 0; //循环设置取得的lot卡清单 foreach (var itm in dic) { //记录当前行第几张lot卡 iCountLot++; //第一张lot卡则加入表格tr头 if (iCountLot == 1) { sb.Append("<tr>\n"); } //将实例加入项目 sb.Append(itm.Value.ToString()); //如果是第3张lot卡则换行 if (iCountLot == 4) { sb.Append("</tr>\n"); //初始为第一张lot卡 iCountLot = 0; } } //如果不是第3张lot卡则加入tr结尾符 if (!sb.ToString().EndsWith("</tr>\n")) { sb.Append("</tr>\n"); } //加入表格table尾 sb.Append("</tbody>\n"); sb.Append("</table>\n"); //取得最终结果 strResult = sb.ToString(); } //写入数据 context.Response.Write(strResult); }
/// <summary> /// 更新保存单据的表头和内容 /// </summary> /// <param name="e">待更新的数据内容</param> private bool UpdateBill(FormViewUpdateEventArgs e) { //当前单据号 string billNum = e.Keys["bill_num"].ToString(); //实例化数据适配器 using (var daHead = new t_delivery_bill_headTableAdapter()) using (var daContent = new t_delivery_bill_contentTableAdapter()) using (var daLotCard = new t_delivery_bill_lot_cardTableAdapter()) //取得数据库连接 using (var conn = daHead.Connection) { //打开数据库连接 conn.Open(); //事务 using (var tran = conn.BeginTransaction()) { //设置数据库连接 daHead.Connection = daContent.Connection = daLotCard.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daLotCard.Transaction = tran; try { //记录需要检测更新首次送货记录工具费的订单序号清单 var lOrderId = new List<string>(); //取得之前单据清单记录 var tabContent = daContent.GetDataByBillNum(billNum); //遍历各行 foreach (DataSetDeliveryBillMgr.t_delivery_bill_contentRow row in tabContent.Rows) { //执行更新订单已交货数和未交货数 daContent.UpdateOrderBalanceQty(-row.pcs_qty, row.order_id); //之前是否有设置为需要记录工具费 if (!row.Isneed_tool_amountNull() && row.need_tool_amount) { //记录到清单 if (!lOrderId.Contains(row.order_id)) { lOrderId.Add(row.order_id); } } } //先执行删除原来单据 daHead.Delete(billNum); daContent.DeleteByBillNum(billNum); daLotCard.DeleteByBillNum(billNum); //保存表头内容 daHead.Insert( Convert.ToDateTime(e.NewValues["bill_date"]), Convert.ToString(e.NewValues["cust_name"]), billNum, Convert.ToString(e.NewValues["remark"]), Convert.ToString(e.NewValues["add_person"]), Convert.ToDateTime(e.NewValues["add_time"]), Convert.ToDateTime(e.NewValues["last_change_time"]) ); //循环保存表身内容 for (int i = 0; i < 6; i++) { //当前行号 byte rowId = Convert.ToByte(i + 1); //取得之前送货单当前行内容 var tab = daContent.GetDataByBillNumAndRowId(billNum, rowId); //检测是否存在数据 if (tab.Rows.Count > 0) { var row = (DataSetDeliveryBillMgr.t_delivery_bill_contentRow)tab.Rows[0]; //取得之前写入的批量卡清单 string strLotCardListOld = row.lot_id_list.Trim(); if (strLotCardListOld.Length > 0) { //取得之前写入的批量卡清单 string productNumOld = row.product_num.Trim(); //之前的批量卡清单写回 LotCardListToWaitInStoreBalance(tran, billNum, rowId, strLotCardListOld, productNumOld); //删除该条单据清单行数据 daContent.DeleteByBillNumAndRowId(billNum, rowId); } } //当前数据所在的行 var trContentRow = (TableRow)tabDataListSon.FindControl("trContentRow" + i); //获取该行生产编号 var txtProductNum = (TextBox)trContentRow.FindControl("txtProductNum" + i); //检测生产编号存在就保存该行 if (txtProductNum == null) { throw new Exception("在第 " + rowId.ToString() + " 行未找到生产编号文本框!"); } //交货数量文本框 var txtPcsQty = (TextBox)trContentRow.FindControl("txtPcsQty" + i); int iPcsQty; if (!int.TryParse(txtPcsQty.Text, out iPcsQty)) { iPcsQty = 0; } var txtFocPcsQty = (TextBox)trContentRow.FindControl("txtFocPcsQty" + i); int iFocPcsQty; if (!int.TryParse(txtFocPcsQty.Text.Trim(), out iFocPcsQty)) { iFocPcsQty = 0; } if (iPcsQty + iFocPcsQty <= 0) { continue; } //保存生产编号到变量 string productNum = txtProductNum.Text; //输入的生产编号 if (productNum.Length <= 0) { throw new Exception("第 " + rowId.ToString() + " 行的生产编号文本框未输入内容!"); } //其他各参数 var txtOrderId = (TextBox)trContentRow.FindControl("txtOrderId" + i); var txtCustNum = (TextBox)trContentRow.FindControl("txtCustNum" + i); var txtOrderNum = (TextBox)trContentRow.FindControl("txtOrderNum" + i); //当前批量卡清单内容保存到的隐藏对象 var hdLotCardList = (HtmlInputHidden)tabDataListSon.FindControl("hdLotCardList" + i); string strLotCardList = hdLotCardList.Value.Replace("|l|", "<").Replace("|g|", ">"); var txtRemark = (TextBox)trContentRow.FindControl("txtRemark" + i); //订单序号 var orderId = txtOrderId.Text.Trim(); //执行扣减待入仓结存并将记录写入批量卡清单 DeductionWaitInStoreBalance( tran, strLotCardList, billNum, rowId, productNum ); //执行插入行 daContent.Insert( billNum, rowId, orderId, null, productNum, Convert.ToString(txtCustNum.Text), txtOrderNum.Text, null, null, iPcsQty, iFocPcsQty == 0 ? null : (int?)iFocPcsQty, null, Convert.ToString(txtRemark.Text), strLotCardList, false ); //执行更新订单已交货数和未交货数 daContent.UpdateOrderBalanceQty(iPcsQty, orderId); //记录到清单 if (!lOrderId.Contains(orderId)) { lOrderId.Add(orderId); } } //更新需要显示工具费的行 UpdateNeedToolAmount(daContent, lOrderId); //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw ex; } } } }
/// <summary> /// 执行扣减待入仓结存,若有之前的批量卡清单和数量则先写入再扣减 /// </summary> /// <param name="tran">当前事务</param> /// <param name="strLotCardList">当前扣减的批量卡清单和数量</param> /// <param name="billNum">送货单号</param> /// <param name="rowId">当前行号</param> /// <param name="productNum">之前的生产编号</param> private void DeductionWaitInStoreBalance( SqlTransaction tran, string strLotCardList, string billNum, byte rowId, string productNum ) { //***扣减当前输入的批量卡和数量*** //检测输入的内容 string strReg = "[^>]+(?=</div>)"; var matchs = Regex.Matches(strLotCardList, strReg); //查询或插入命令 using (var daLotCard = new t_delivery_bill_lot_cardTableAdapter()) using (var cmd = new SqlCommand()) { //设置连接 daLotCard.Connection = cmd.Connection = tran.Connection; //设置事务 daLotCard.Transaction = cmd.Transaction = tran; //取出匹配到的数据 foreach (Match match in matchs) { //通过冒号分出批量卡号和数量 string[] strs = match.Value.Split(new char[] { ':' }, 2, StringSplitOptions.RemoveEmptyEntries); string strLotId = strs[0]; int iQty = int.Parse(strs[1]); //部门名称 string procName = "成品仓"; //待入仓成品结余pcs总数 int totalPnlQty; int totalPcsQty; //获取该批量卡在待入仓成品仓的结存数 ydOperateBalanceLotCrad.GetProcBalanceQty( tran, procName, strLotId, null, out totalPnlQty, out totalPcsQty ); //检测pcs数量必须大于0 if (totalPcsQty < iQty) { if (totalPnlQty > 0) { throw new Exception(string.Format("批量卡 {0} 在待入仓成品的结存为pnl数!", strLotId)); } else { throw new Exception(string.Format("批量卡 {0} 在待入仓成品的结存不足扣数 {1} pcs!", strLotId, iQty)); } } //保存当前批量卡出数清单 daLotCard.InsertData(billNum, rowId, strLotId, iQty, string.Empty); //从待入仓结存中扣取数据 cmd.CommandText = "UPDATE a " + "SET [pcs_qty]=[pcs_qty]-@PcsQty OUTPUT [inserted].[pcs_qty] " + "FROM (" + "SELECT TOP 1 * " + "FROM [t_complete_lot_card_wait] " + "WHERE [proc_name]=@ProcName AND [lot_id]=@LotId AND [pcs_qty]>0 " + "ORDER BY [id] DESC" + ") a"; //设置参数值 cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@PcsQty", iQty); cmd.Parameters.AddWithValue("@ProcName", procName); cmd.Parameters.AddWithValue("@LotId", strLotId); //取得扣减后的pcs数量 int iLastQty = Convert.ToInt32(cmd.ExecuteScalar()); while (iLastQty < 0) { cmd.Parameters["@PcsQty"].Value = -iLastQty; iLastQty = Convert.ToInt32(cmd.ExecuteScalar()); } //修改查询语句删除结存小于或等于0的项 cmd.CommandText = "DELETE FROM [t_complete_lot_card_wait] " + "WHERE [proc_name]=@ProcName AND [lot_id]=@LotId AND [pcs_qty]<=0"; //设置参数值 cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@ProcName", procName); cmd.Parameters.AddWithValue("@LotId", strLotId); //执行删除 cmd.ExecuteNonQuery(); } } }
/// <summary> /// 根据单号从数据库中删除单据的表头和内容 /// </summary> /// <param name="billNum">单据号</param> private bool DeleteBillByBillNum(string billNum) { //实例化数据适配器 using (var daHead = new t_delivery_bill_headTableAdapter()) using (var daContent = new t_delivery_bill_contentTableAdapter()) using (var daLotCard = new t_delivery_bill_lot_cardTableAdapter()) //取得数据库连接 using (var conn = daHead.Connection) { //打开数据库连接 conn.Open(); //事务 using (var tran = conn.BeginTransaction()) { //设置数据库连接 daHead.Connection = daContent.Connection = daLotCard.Connection = conn; //设置事务 daHead.Transaction = daContent.Transaction = daLotCard.Transaction = tran; try { //记录需要检测更新首次送货记录工具费的订单序号清单 var lOrderId = new List<string>(); //取得之前单据清单记录 var tabContent = daContent.GetDataByBillNum(billNum); //遍历各行 foreach (DataSetDeliveryBillMgr.t_delivery_bill_contentRow row in tabContent.Rows) { //之前是否有设置为需要记录工具费 if (!row.Isneed_tool_amountNull() && row.need_tool_amount) { //记录到清单 if (!lOrderId.Contains(row.order_id)) { lOrderId.Add(row.order_id); } } //取得之前写入的批量卡清单 string strLotCardListOld = row.lot_id_list.Trim(); //是否存在批量卡清单 if (strLotCardListOld.Length > 0) { //当前生产编号 string productNum = row.product_num; //之前的清单写回 LotCardListToWaitInStoreBalance(tran, billNum, null, strLotCardListOld, productNum); } //当前交货的数量 var pcsQty = row.pcs_qty; if (!row.Isfoc_pcs_qtyNull()) { pcsQty += row.foc_pcs_qty; } //执行更新订单已交货数和未交货数 daContent.UpdateOrderBalanceQty(-pcsQty, row.order_id); } //执行删除 daHead.Delete(billNum); daContent.DeleteByBillNum(billNum); daLotCard.DeleteByBillNum(billNum); //更新需要显示工具费的行 UpdateNeedToolAmount(daContent, lOrderId); //提交事务 tran.Commit(); //返回成功 return true; } catch (Exception ex) { //回滚事务 tran.Rollback(); //抛出错误 throw ex; } } } }