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