public void UpdateAssignLog(AssignDetail detail, OperLog operLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
//conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
//
// AssignDetail detail = new AssignDetail();
// detail.cnnAssignSerialNo = detailLog.cnnAssignSerialNo;
// detail.cnnOrderSerialNo = detailLog.cnnOrderSerialNo;
// detail.cnvcProductCode = detailLog.cnvcProductCode;
AssignDetail detailOld = EntityMapping.Get(detail, trans) as AssignDetail;
if (detail.cnnCount != detailOld.cnnCount)
{
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
OrderSerialNo serialNo = new OrderSerialNo();
serialNo.cnvcFill = "0";
serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans));
detailOld.cnnCount = detail.cnnCount;
detailOld.cnnSum = Math.Round(detail.cnnCount * detailOld.cnnPrice, 2);
EntityMapping.Update(detailOld, trans);
AssignDetailLog detailLog = new AssignDetailLog(detailOld.ToTable());
detailLog.cnvcOperID = operLog.cnvcOperID;
detailLog.cndOperDate = dtSysTime;
detailLog.cnnSerialNo = serialNo.cnnSerialNo;
EntityMapping.Create(detailLog, trans);
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "生产流水：" + detail.cnnAssignSerialNo.ToString() + "，日志流水：" + serialNo.cnnSerialNo.ToString();
EntityMapping.Create(operLog, trans);
}
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
LogAdapter.WriteDatabaseException(sex);
throw sex;
}
catch (Exception ex)
{
trans.Rollback();
LogAdapter.WriteFeaturesException(ex);
throw ex;
}
finally
{
ConnectionPool.ReturnConnection(conn);
}
}
}
//减单
public void OrderReduce(string strOrderSerialNo, string strReduceType, string strAddComments, DataTable dtOrderReduce, OperLog operLog) //,BusiLog busiLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
//conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
OrderSerialNo serialNo = new OrderSerialNo();
serialNo.cnvcFill = "0";
serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans));
foreach (DataRow drOrderReduce in dtOrderReduce.Rows)
{
OrderReduceLog orderReduce = new OrderReduceLog(drOrderReduce);
orderReduce.cnnOrderSerialNo = decimal.Parse(strOrderSerialNo);
orderReduce.cnnReduceSerialNo = serialNo.cnnSerialNo;
orderReduce.cnvcReduceType = strReduceType;
orderReduce.cnvcReduceComments = strAddComments;
orderReduce.cndOperDate = dtSysTime;
orderReduce.cnvcOperID = operLog.cnvcOperID;
orderReduce.cnvcReduceState = "0";
orderReduce.cnnReduceCount = decimal.Parse(drOrderReduce["cnnOrderCount"].ToString());
EntityMapping.Create(orderReduce, trans);
}
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "订单流水：" + strOrderSerialNo;
EntityMapping.Create(operLog, trans);
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
LogAdapter.WriteDatabaseException(sex);
throw sex;
}
catch (Exception ex)
{
trans.Rollback();
LogAdapter.WriteFeaturesException(ex);
throw ex;
}
finally
{
ConnectionPool.ReturnConnection(conn);
}
}
}
public void AddOrder(OrderBook orderBook, DataTable dtOrderBookDetail, OperLog operLog) //,BusiLog busiLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
//conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
OrderSerialNo serialNo = new OrderSerialNo();
serialNo.cnvcFill = "0";
serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans));
orderBook.cnnOrderSerialNo = serialNo.cnnSerialNo;
orderBook.cndOrderDate = dtSysTime;
orderBook.cnvcOrderOperID = operLog.cnvcOperID;
EntityMapping.Create(orderBook, trans);
foreach (DataRow drOrderBookDetail in dtOrderBookDetail.Rows)
{
OrderBookDetail detail = new OrderBookDetail(drOrderBookDetail);
detail.cnnOrderSerialNo = serialNo.cnnSerialNo;
detail.cndOperDate = dtSysTime;
detail.cnvcOperID = operLog.cnvcOperID;
EntityMapping.Create(detail, trans);
}
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "订单流水：" + orderBook.cnnOrderSerialNo;
EntityMapping.Create(operLog, trans);
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
LogAdapter.WriteDatabaseException(sex);
throw sex;
}
catch (Exception ex)
{
trans.Rollback();
LogAdapter.WriteFeaturesException(ex);
throw ex;
}
finally
{
ConnectionPool.ReturnConnection(conn);
}
}
}
public void AddOrder(OrderBook orderBook, DataTable dtOrderBookDetail, OperLog operLog) //,BusiLog busiLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
//conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
if (orderBook.cnnOrderSerialNo > 0)
{
OrderBook oldOrder = new OrderBook();
oldOrder.cnnOrderSerialNo = orderBook.cnnOrderSerialNo;
oldOrder = EntityMapping.Get(oldOrder, trans) as OrderBook;
if (oldOrder == null)
{
throw new Exception("订单未找到");
}
oldOrder.cnvcOperID = orderBook.cnvcOperID;
oldOrder.cnvcOrderType = orderBook.cnvcOrderType;
oldOrder.cnvcProduceDeptID = orderBook.cnvcProduceDeptID;
oldOrder.cnvcOrderDeptID = orderBook.cnvcOrderDeptID;
if (oldOrder.cnvcOrderType == "WDO" || oldOrder.cnvcOrderType == "WDOSELF")
{
oldOrder.cndArrivedDate = orderBook.cndArrivedDate;
oldOrder.cnvcLinkPhone = orderBook.cnvcLinkPhone;
oldOrder.cnvcShipAddress = orderBook.cnvcShipAddress;
oldOrder.cnvcCustomName = orderBook.cnvcCustomName;
}
oldOrder.cndShipDate = orderBook.cndShipDate;
oldOrder.cndOrderDate = orderBook.cndOrderDate;
EntityMapping.Update(oldOrder, trans);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "delete from tborderbookdetail where cnnorderserialno=" + orderBook.cnnOrderSerialNo.ToString());
foreach (DataRow drOrderBookDetail in dtOrderBookDetail.Rows)
{
OrderBookDetail detail = new OrderBookDetail(drOrderBookDetail);
detail.cnnOrderSerialNo = orderBook.cnnOrderSerialNo;
EntityMapping.Create(detail, trans);
}
}
else
{
OrderSerialNo serialNo = new OrderSerialNo();
serialNo.cnvcFill = "0";
serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans));
orderBook.cnnOrderSerialNo = serialNo.cnnSerialNo;
orderBook.cndOperDate = dtSysTime;
orderBook.cnvcOperID = operLog.cnvcOperID;
EntityMapping.Create(orderBook, trans);
//SqlHelper.ExecuteNonQuery(trans,CommandType.Text,"delete from tborderbookdetail where cnnorderserialno="+orderBook.cnnOrderSerialNo.ToString()) ;
foreach (DataRow drOrderBookDetail in dtOrderBookDetail.Rows)
{
OrderBookDetail detail = new OrderBookDetail(drOrderBookDetail);
detail.cnnOrderSerialNo = serialNo.cnnSerialNo;
EntityMapping.Create(detail, trans);
}
}
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "订单流水：" + orderBook.cnnOrderSerialNo;
EntityMapping.Create(operLog, trans);
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
LogAdapter.WriteDatabaseException(sex);
throw sex;
}
catch (Exception ex)
{
trans.Rollback();
LogAdapter.WriteFeaturesException(ex);
throw ex;
}
finally
{
ConnectionPool.ReturnConnection(conn);
}
}
}
public void DeleteAssignLog(ProduceLog produceLog, OperLog operLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
SqlTransaction trans = conn.BeginTransaction();
try
{
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
OrderSerialNo serialNo = new OrderSerialNo();
serialNo.cnvcFill = "0";
serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans));
ProduceLog oldLog = new ProduceLog();
oldLog.cnnProduceSerialNo = produceLog.cnnProduceSerialNo;
oldLog = EntityMapping.Get(oldLog, trans) as ProduceLog;
if (oldLog == null)
{
throw new Exception("生产计划不存在");
}
if (oldLog.cnvcProduceState != "6")
{
throw new Exception("生产计划不在已分货状态，不能清除分货数据");
}
if (oldLog.cnvcProduceState == "7")
{
throw new Exception("已分货出库");
}
oldLog.cnvcOperID = produceLog.cnvcOperID;
oldLog.cndOperDate = dtSysTime;
//清除分货数据
string strsql = "delete from tbassignlog where cnnproduceserialno=" + produceLog.cnnProduceSerialNo.ToString();
// DataTable dtal = SqlHelper.ExecuteDataTable(trans,CommandType.Text,strsql);
// if(dtal.Rows.Count == 0) throw new Exception("未找到分货数据");
// Entity.AssignLog al = new AssignLog(dtal);
// EntityMapping.Delete(al,trans);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strsql);
strsql = "delete from tbassigndetail where cnnproduceserialno=" + produceLog.cnnProduceSerialNo.ToString();
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strsql);
strsql = "update tbproducechecklog set cnnassigncount=0 where cnnproduceserialno=" + produceLog.cnnProduceSerialNo.ToString();
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strsql);
//更新订单生产计划状态
string strUpdateOrder = "update tbOrderBook set cnvcOrderState='1' "
+
" where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='0' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") ";
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrder);
//更新生产计划状态
oldLog.cnvcProduceState = "5";
EntityMapping.Update(oldLog, trans);
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "生产流水：" + produceLog.cnnProduceSerialNo.ToString();
EntityMapping.Create(operLog, trans);
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
LogAdapter.WriteDatabaseException(sex);
throw sex;
}
catch (Exception ex)
{
trans.Rollback();
LogAdapter.WriteFeaturesException(ex);
throw ex;
}
finally
{
ConnectionPool.ReturnConnection(conn);
}
}
}
public void AddAssignLog(ProduceLog produceLog, OperLog operLog, string strwhhouse) //,BusiLog busiLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
SqlTransaction trans = conn.BeginTransaction();
try
{
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
OrderSerialNo serialNo = new OrderSerialNo();
serialNo.cnvcFill = "0";
serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans));
ProduceLog oldLog = new ProduceLog();
oldLog.cnnProduceSerialNo = produceLog.cnnProduceSerialNo;
oldLog = EntityMapping.Get(oldLog, trans) as ProduceLog;
if (oldLog == null)
{
throw new Exception("生产计划不存在");
}
if (oldLog.cnvcProduceState == "6")
{
throw new Exception("已分货");
}
if (oldLog.cnvcProduceState == "7")
{
throw new Exception("已分货出库");
}
oldLog.cnvcOperID = produceLog.cnvcOperID;
oldLog.cndOperDate = dtSysTime;
AssignLog assign = new AssignLog();
assign.cndOperDate = dtSysTime;
assign.cnnAssignSerialNo = serialNo.cnnSerialNo;
assign.cnnProduceSerialNo = oldLog.cnnProduceSerialNo;
assign.cnvcShipOperID = produceLog.cnvcOperID;
assign.cnvcShipDeptID = oldLog.cnvcProduceDeptID;
assign.cnvcOperID = produceLog.cnvcOperID;
assign.cndShipDate = oldLog.cndProduceDate;
//订单数据
string strOrderSql = @"select * from tbOrderBook where cnnOrderSerialNo in
(select cnnOrderSerialNo from tbProduceOrderLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + ")";
DataTable dtOrder = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderSql);
string strOrderDetailSql = @"select * from tbOrderBookDetail where cnnOrderSerialNo in
(select cnnOrderSerialNo from tbProduceOrderLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + ")";
DataTable dtOrderDetail = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderDetailSql);
DataTable dtDept = SingleTableQuery.ExcuteQuery("tbDept", trans);
//生产盘点数据
string strCheckSql = "select * "
+ " from tbProduceCheckLog where cnnProduceSerialNo=" +
oldLog.cnnProduceSerialNo.ToString() + " and cnnCheckCount>0 and cnbInWh=1";
DataTable dtProduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strCheckSql);
#region 分货不按可用量分
string strwhsql = "SELECT cnvcinvcode,sum(cnnAvaQuantity) as cnnwhcount FROM tbCurrentStock "
+ " WHERE cnvcwhcode='" + strwhhouse + "' AND cnvcStopFlag='0' and CONVERT(char(10),isnull(cndExpDate,''),121)>=CONVERT(char(10),getdate(),121) "
+ " group by cnvcinvcode";
DataTable dtwh = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strwhsql);
Helper.DataTableConvert(dtProduce, "cnvcInvCode", "cnnCheckCount", dtwh, "cnvcinvcode", "cnnwhcount", "");
#endregion
//订单按产品分类汇总
//分配 外订订单有限分配
Helper.DataTableConvert(dtOrderDetail, "cnnOrderSerialNo", "cnvcOrderType", dtOrder, "cnnOrderSerialNo", "cnvcOrderType", "");
Helper.DataTableConvert(dtOrderDetail, "cnnOrderSerialNo", "cnvcOrderDeptID", dtOrder, "cnnOrderSerialNo", "cnvcOrderDeptID", "");
Helper.DataTableConvert(dtOrderDetail, "cnnOrderSerialNo", "cndShipDate", dtOrder, "cnnOrderSerialNo", "cndShipDate", "");
Helper.DataTableConvert(dtOrderDetail, "cnvcOrderDeptID", "cnnPriority", dtDept, "cnvcDeptID", "cnnPriority", "");
//
dtOrderDetail.Columns.Add("cnnAssignCount");
foreach (DataRow dr in dtOrderDetail.Rows)
{
dr["cnnAssignCount"] = 0;
}
DataView dvOrder = new DataView(dtOrderDetail);
dvOrder.Sort = "cnvcOrderType desc,cnnPriority asc,cnnOrderSerialNo asc";
Hashtable hOrderSerialNo = new Hashtable();
foreach (DataRowView dv in dvOrder)
{
string strOrderSerialNo = dv["cnnOrderSerialNo"].ToString();
string strProductCode = dv["cnvcInvCode"].ToString();
string strOrderCount = dv["cnnOrderCount"].ToString();
string strAssignCount = dv["cnnAssignCount"].ToString();
decimal dOrderCount = decimal.Parse(strOrderCount);
decimal dAssignCount = Convert.ToDecimal(strAssignCount);
string strOrderType = dv["cnvcOrderType"].ToString();
DataRow[] drProduces = dtProduce.Select("cnvcInvCode='" + strProductCode + "'");
if (drProduces.Length == 0)
{
continue; //throw new Exception(strProductCode+"无可分货量");
}
// if(drProduces.Length > 0)
// {
DateTime dtMDate = Convert.ToDateTime(drProduces[0]["cndMDate"].ToString());
DateTime dtExpDate = Convert.ToDateTime(drProduces[0]["cndExpDate"].ToString());
string strSumCount = drProduces[0]["cnnCheckCount"].ToString();
decimal dSumCount = decimal.Parse(strSumCount);
string strSumAssign = drProduces[0]["cnnAssignCount"].ToString();
decimal dSumAssign = decimal.Parse(strSumAssign);
//if(dSumCount <=0) throw new Exception(strProductCode+"无可分货量");
// if(dSumCount > 0)
// {
AssignDetail assignDetail = new AssignDetail();
assignDetail.cnnAssignSerialNo = assign.cnnAssignSerialNo;
assignDetail.cnnProduceSerialNo = assign.cnnProduceSerialNo;
assignDetail.cnnOrderSerialNo = decimal.Parse(strOrderSerialNo);
assignDetail.cnvcInvCode = strProductCode;
assignDetail.cndMdate = dtMDate;
assignDetail.cndExpDate = dtExpDate;
if (dSumCount >= dOrderCount - dAssignCount)
{
assignDetail.cnnOrderCount = dOrderCount;
assignDetail.cnnAssignCount = dOrderCount - dAssignCount;
drProduces[0]["cnnCheckCount"] = dSumCount - dOrderCount + dAssignCount;
drProduces[0]["cnnAssignCount"] = dSumAssign + dOrderCount - dAssignCount;
}
else
{
if (strOrderType == "WDO" || strOrderType == "WDOSELF")
{
throw new Exception("订单流水为" + dv["cnnOrderSerialNo"].ToString() + "的外订定单" + strProductCode + "不能满足分配");
}
assignDetail.cnnOrderCount = dOrderCount;
assignDetail.cnnAssignCount = dSumCount;
drProduces[0]["cnnCheckCount"] = 0;
drProduces[0]["cnnAssignCount"] = dSumAssign + dSumCount;
}
EntityMapping.Create(assignDetail, trans);
if (!hOrderSerialNo.Contains(dv["cnnOrderSerialNo"].ToString()))
{
hOrderSerialNo.Add(dv["cnnOrderSerialNo"].ToString(), dv["cnnOrderSerialNo"].ToString());
assign.cnnOrderSerialNo = decimal.Parse(dv["cnnOrderSerialNo"].ToString());
assign.cnvcReceiveDeptID = dv["cnvcOrderDeptID"].ToString();
assign.cndShipDate = DateTime.Parse(dv["cndShipDate"].ToString());
EntityMapping.Create(assign, trans);
}
// string strOrderBookDetail = "update tbOrderBookDetail set cnnAssignCount="+Convert.ToString(assignDetail.cnnAssignCount+dAssignCount)+" where cnnOrderSerialNo="+strOrderSerialNo+" and cnvcProductCode='"+strProductCode+"'";
// SqlHelper.ExecuteNonQuery(trans,CommandType.Text,strOrderBookDetail);
// }
// }
}
foreach (DataRow drProduce in dtProduce.Rows)
{
ProduceCheckLog check = new ProduceCheckLog(drProduce);
string strSql = "update tbProduceCheckLog set cnnAssignCount=" + check.cnnAssignCount.ToString() + " where cnnProduceSerialNo=" + check.cnnProduceSerialNo.ToString() + " and cnvcInvCode='" + check.cnvcInvCode + "'";
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql);
}
//更新订单生产计划状态
string strUpdateOrder = "update tbOrderBook set cnvcOrderState='2' "
+
" where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='0' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") ";
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrder);
//更新生产计划状态
oldLog.cnvcProduceState = "6";
EntityMapping.Update(oldLog, trans);
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "分货流水：" + produceLog.cnnProduceSerialNo.ToString();
EntityMapping.Create(operLog, trans);
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
LogAdapter.WriteDatabaseException(sex);
throw sex;
}
catch (Exception ex)
{
trans.Rollback();
LogAdapter.WriteFeaturesException(ex);
throw ex;
}
finally
{
ConnectionPool.ReturnConnection(conn);
}
}
}
public void AddAssignLog(ProduceLog produceLog, OperLog operLog) //,BusiLog busiLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
//conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
OrderSerialNo serialNo = new OrderSerialNo();
serialNo.cnvcFill = "0";
serialNo.cnnSerialNo = Convert.ToDecimal(EntityMapping.Create(serialNo, trans));
ProduceLog oldLog = new ProduceLog();
oldLog.cnnProduceSerialNo = produceLog.cnnProduceSerialNo;
oldLog = EntityMapping.Get(oldLog, trans) as ProduceLog;
if (oldLog == null)
{
throw new Exception("生产计划不存在");
}
// if(oldLog.cnvcProduceState != "3")
// {
// throw new Exception("未盘点");
// }
oldLog.cnvcOperID = produceLog.cnvcOperID;
oldLog.cndOperDate = dtSysTime;
AssignLog assign = new AssignLog();
assign.cndOperDate = dtSysTime;
assign.cnnAssignSerialNo = serialNo.cnnSerialNo;
assign.cnnProduceSerialNo = oldLog.cnnProduceSerialNo;
assign.cnvcShipOperID = produceLog.cnvcOperID;
assign.cnvcShipDeptID = oldLog.cnvcProduceDeptID;
assign.cnvcOperID = produceLog.cnvcOperID;
assign.cndShipDate = oldLog.cndProduceDate;
#region
//订单流水
//收货单位
//订单、加单、减单清单
// string strOrderSql = "select b.cnvcOrderDeptID,b.cndShipDate,b.cnvcOrderType,e.cnnPriority,c.* from tbProduceOrderLog a "
// + " join tbOrderBook b on a.cnnOrderSerialNo=b.cnnOrderSerialNo "
// + " left outer join tbOrderBookDetail c on b.cnnOrderSerialNo=c.cnnOrderSerialNo "
// + " left outer join tbDept e on b.cnvcOrderDeptID=e.cnvcDeptID "
// + " where a.cnnProduceSerialNo = " + oldLog.cnnProduceSerialNo.ToString()
// + " and a.cnvcType='0' ";//and b.cnvcOrderState='1'";
// string strOrderAddSql = "select b.* from tbProduceOrderLog a "
// + " left outer join tbOrderAddLog b on a.cnnOrderSerialNo=b.cnnAddSerialNo "
// + " where a.cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString()
// + " and a.cnvcType='1' ";//and b.cnvcAddState='1'";
// string strOrderReduceSql = "select b.* from tbProduceOrderLog a "
// + " left outer join tbOrderReduceLog b on a.cnnOrderSerialNo=b.cnnReduceSerialNo "
// + " where a.cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString()
// + " and a.cnvcType='2' ";//and b.cnvcReduceState='1'";
// DataTable dtOrder = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderSql);
// DataTable dtOrderAdd = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderAddSql);
// DataTable dtOrderReduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderReduceSql);
// //合计订单
// foreach(DataRow drAdd in dtOrderAdd.Rows)
// {
// string strOrderSerialNo = drAdd["cnnOrderSerialNo"].ToString();
// string strProductCode = drAdd["cnvcProductCode"].ToString();
// DataRow[] drOrders = dtOrder.Select("cnvcProductCode='" + strProductCode + "'");
// if(drOrders.Length == 0)
// {
// DataRow[] drOrderSerialNo = dtOrder.Select("cnnOrderSerialNo=" + strOrderSerialNo);
// if(drOrderSerialNo.Length > 0)
// {
// DataRow drOrder = dtOrder.NewRow();
// drOrder["cnvcOrderDeptID"] = drOrderSerialNo[0]["cnvcOrderDeptID"];
// drOrder["cndShipDate"] = drOrderSerialNo[0]["cndShipDate"];
// drOrder["cnvcOrderType"] = drOrderSerialNo[0]["cnvcOrderType"];
// drOrder["cnnPriority"] = drOrderSerialNo[0]["cnnPriority"];
//
// drOrder["cnnOrderSerialNo"] = drAdd["cnvcProductCode"];
// drOrder["cnvcProductCode"] = drAdd["cnvcProductCode"];
// drOrder["cnvcProductName"] = drAdd["cnvcProductName"];
// drOrder["cnnOrderCount"] = drAdd["cnnAddCount"];
// drOrder["cnvcUnit"] = drAdd["cnvcUnit"];
// drOrder["cnnPrice"] = drAdd["cnnPrice"];
// drOrder["cnnSum"] = drAdd["cnnSum"];
// dtOrder.Rows.Add(drOrder);
// }
// }
//
// }
//
// foreach(DataRow drOrder in dtOrder.Rows)
// {
// //有的加减数量，无的加产品
// string strProductCode = drOrder["cnvcProductCode"].ToString();
// //订单量
// decimal dCount = decimal.Parse(drOrder["cnnOrderCount"].ToString());
// decimal dAddCount = 0;
// DataRow[] drOrderAdds = dtOrderAdd.Select("cnvcProductCode='" + strProductCode + "'");
// if(drOrderAdds.Length > 0)
// {
// //加单量
// string strOrderAddCount = drOrderAdds[0]["cnnAddCount"].ToString();
// dAddCount = decimal.Parse(strOrderAddCount);
// //dAddCount = decimal.Parse(strCount) + decimal.Parse(strOrderAddCount);
// drOrder["cnnOrderCount"] = dCount + dAddCount;
// }
// DataRow[] drOrderReduces = dtOrderReduce.Select("cnvcProductCode='" + strProductCode + "'");
// if(drOrderReduces.Length > 0)
// {
// //减单量
// decimal dReduceCount = decimal.Parse(drOrderReduces[0]["cnnReduceCount"].ToString());
// //decimal dReduceCount = decimal.Parse(strCount) - decimal.Parse(strOrderReduceCount);
// drOrder["cnnOrderCount"] = dCount + dAddCount - dReduceCount;
// }
// }
#endregion
#region 注释
//生产、加单、减单
// string strProduceSql = "select * from tbProduceDetail where cnnProduceSerialNo="+oldLog.cnnProduceSerialNo.ToString();
// string strProduceAddSql = "select * from tbProduceDetailAdd where cnvcState='2' and cnnProduceSerialNo="+oldLog.cnnProduceSerialNo.ToString();
// string strProduceReduceSql = "select * from tbProduceDetailReduce where cnvcState='2' and cnnProduceSerialNo="+oldLog.cnnProduceSerialNo;
// DataTable dtProduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strProduceSql);
// DataTable dtProduceAdd = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strProduceAddSql);
// DataTable dtProduceReduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strProduceReduceSql);
//
// foreach(DataRow drProduce in dtProduce.Rows)
// {
// //有的加减数量，无的加产品
// string strProductCode = drProduce["cnvcCode"].ToString();
// decimal dCount = decimal.Parse(drProduce["cnnCount"].ToString());
// decimal dAddCount = 0;
// DataRow[] drProduceAdds = dtProduceAdd.Select("cnvcCode='" + strProductCode + "'");
// if(drProduceAdds.Length > 0)
// {
// dAddCount = decimal.Parse(drProduceAdds[0]["cnnCount"].ToString());
// //decimal dAddCount = decimal.Parse(strCount) + decimal.Parse(strOrderAddCount);
// drProduce["cnnCount"] = dCount + dAddCount;//dAddCount.ToString();
// }
// DataRow[] drProduceReduces = dtProduceReduce.Select("cnvcCode='" + strProductCode + "'");
// if(drProduceReduces.Length > 0)
// {
// decimal dReduceCount = decimal.Parse(drProduceReduces[0]["cnnCount"].ToString());
// //decimal dReduceCount = decimal.Parse(strCount) - decimal.Parse(strOrderReduceCount);
// drProduce["cnnCount"] = dCount + dAddCount - dReduceCount;//dReduceCount.ToString();
// }
// }
// foreach(DataRow drAdd in dtProduceAdd.Rows)
// {
// string strProductCode = drAdd["cnvcCode"].ToString();
// DataRow[] drProduces = dtProduce.Select("cnvcCode='" + strProductCode + "'");
// if(drProduces.Length == 0)
// {
// DataRow drProduce = dtOrder.NewRow();
// drProduce["cnnProduceSerialNo"] = drAdd["cnnProduceSerialNo"];
// drProduce["cnvcCode"] = drAdd["cnvcCode"];
// drProduce["cnvcName"] = drAdd["cnvcName"];
// drProduce["cnnCount"] = drAdd["cnnCount"];
// drProduce["cnvcUnit"] = drAdd["cnvcUnit"];
// //drProduce["cnvcPrice"] = drAdd["cnvcPrice"];
// //drProduce["cnnSum"] = drAdd["cnnSum"];
// dtProduceAdd.Rows.Add(drProduce);
// }
//
// }
#endregion
//订单数据
string strOrderSql = "select b.cnvcOrderDeptID,b.cndShipDate,b.cnvcOrderType,f.cnnPriority,"
+ " c.cnnOrderSerialNo,c.cnvcProductCode,c.cnvcProductName, "
+ " (c.cnnOrderCount+isnull(d.cnnAddCount,0)-isnull(e.cnnReduceCount,0)) as cnnOrderCount,c.cnvcUnit,c.cnnPrice, "
+ " (c.cnnSum+isnull(d.cnnAddSum,0)-isnull(e.cnnReduceSum,0)) as cnnSum,c.cnnAssignCount "
+ " from tbOrderBookDetail c "
+ " left outer join "
+ " (select cnnOrderSerialNo,cnvcProductCode,sum(cnnAddCount) as cnnAddCount,sum(cnnSum) as cnnAddSum from "
+ " tbOrderAddLog "
+ " group by cnnOrderSerialNo,cnvcProductCode) "
+ " d on c.cnnOrderSerialNo=d.cnnOrderSerialNo and d.cnvcProductCode=c.cnvcProductCode "
+ " left outer join "
+ " (select cnnOrderSerialNo,cnvcProductCode,sum(cnnReduceCount) as cnnReduceCount,sum(cnnSum) as cnnReduceSum from "
+ " tbOrderReduceLog "
+ " group by cnnOrderSerialNo,cnvcProductCode) "
+ " e on c.cnnOrderSerialNo=e.cnnOrderSerialNo and e.cnvcProductCode=c.cnvcProductCode "
+ " left outer join tbOrderBook b on c.cnnOrderSerialNo=b.cnnOrderSerialNo "
+ " left outer join tbDept f on b.cnvcOrderDeptID=f.cnvcDeptID "
+ " where c.cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo.ToString() + ") "
+ " and c.cnnOrderCount+isnull(d.cnnAddCount,0)-isnull(e.cnnReduceCount,0)>c.cnnAssignCount ";
DataTable dtOrder = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strOrderSql);
//生产盘点数据
string strCheckSql = "select * from tbProduceCheckLog where cnnProduceSerialNo=" +
oldLog.cnnProduceSerialNo.ToString() + " and cnnCheckCount>0";
DataTable dtProduce = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strCheckSql);
//订单按产品分类汇总
//分配 外订订单有限分配
DataView dvOrder = new DataView(dtOrder);
dvOrder.Sort = "cnvcOrderType desc,cnnPriority asc,cnnOrderSerialNo asc";
Hashtable hOrderSerialNo = new Hashtable();
foreach (DataRowView dv in dvOrder)
{
string strOrderSerialNo = dv["cnnOrderSerialNo"].ToString();
string strProductCode = dv["cnvcProductCode"].ToString();
string strProductName = dv["cnvcProductName"].ToString();
string strOrderCount = dv["cnnOrderCount"].ToString();
string strAssignCount = dv["cnnAssignCount"].ToString();
decimal dOrderCount = decimal.Parse(strOrderCount);
decimal dAssignCount = Convert.ToDecimal(strAssignCount);
string strUnit = dv["cnvcUnit"].ToString();
string strPrice = dv["cnnPrice"].ToString();
string strOrderType = dv["cnvcOrderType"].ToString();
decimal dPrice = decimal.Parse(strPrice);
DataRow[] drProduces = dtProduce.Select("cnvcCode='" + strProductCode + "'");
if (drProduces.Length > 0)
{
string strSumCount = drProduces[0]["cnnCheckCount"].ToString();
decimal dSumCount = decimal.Parse(strSumCount);
string strSumAssign = drProduces[0]["cnnAssignCount"].ToString();
decimal dSumAssign = decimal.Parse(strSumAssign);
if (dSumCount > 0)
{
AssignDetail assignDetail = new AssignDetail();
assignDetail.cnnAssignSerialNo = assign.cnnAssignSerialNo;
assignDetail.cnnProduceSerialNo = assign.cnnProduceSerialNo;
assignDetail.cnnOrderSerialNo = decimal.Parse(strOrderSerialNo);
assignDetail.cnvcProductCode = strProductCode;
assignDetail.cnvcProductName = strProductName;
assignDetail.cnvcUnit = strUnit;
if (dSumCount >= dOrderCount - dAssignCount)
{
assignDetail.cnnOrderCount = dOrderCount;
assignDetail.cnnCount = dOrderCount - dAssignCount;
drProduces[0]["cnnCheckCount"] = dSumCount - dOrderCount + dAssignCount;
drProduces[0]["cnnAssignCount"] = dSumAssign + dOrderCount - dAssignCount;
}
else
{
if (strOrderType == "WDO")
{
throw new Exception("订单流水为" + dv["cnnOrderSerialNo"].ToString() + "的外订定单" + strProductName + "不能满足分配");
}
assignDetail.cnnOrderCount = dOrderCount;
assignDetail.cnnCount = dSumCount;
drProduces[0]["cnnCheckCount"] = 0;
drProduces[0]["cnnAssignCount"] = dSumAssign + dSumCount;
}
assignDetail.cnnPrice = dPrice;
assignDetail.cnnSum = assignDetail.cnnCount * dPrice;
EntityMapping.Create(assignDetail, trans);
if (!hOrderSerialNo.Contains(dv["cnnOrderSerialNo"].ToString()))
{
hOrderSerialNo.Add(dv["cnnOrderSerialNo"].ToString(), dv["cnnOrderSerialNo"].ToString());
assign.cnnOrderSerialNo = decimal.Parse(dv["cnnOrderSerialNo"].ToString());
assign.cnvcReceiveDeptID = dv["cnvcOrderDeptID"].ToString();
assign.cndShipDate = DateTime.Parse(dv["cndShipDate"].ToString());
EntityMapping.Create(assign, trans);
}
string strOrderBookDetail = "update tbOrderBookDetail set cnnAssignCount=" + Convert.ToString(assignDetail.cnnCount + dAssignCount) + " where cnnOrderSerialNo=" + strOrderSerialNo + " and cnvcProductCode='" + strProductCode + "'";
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strOrderBookDetail);
}
}
}
foreach (DataRow drProduce in dtProduce.Rows)
{
ProduceCheckLog check = new ProduceCheckLog(drProduce);
//check.cnnCheckCount = 0;
//EntityMapping.Update(check);
string strSql = "update tbProduceCheckLog set cnnCheckCount=" + check.cnnCheckCount.ToString() + ",cnnAssignCount=" + check.cnnAssignCount.ToString() + " where cnnProduceSerialNo=" + check.cnnProduceSerialNo.ToString() + " and cnvcCode='" + check.cnvcCode + "'";
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql);
}
//更新订单生产计划状态
string strUpdateOrder = "update tbOrderBook set cnvcOrderState='2' "
+
" where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='0' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") ";
string strUpdateOrderAdd = "update tbOrderAddLog set cnvcAddState='2' "
+
" where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='1' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") ";
string strUpdateOrderReduce = "update tbOrderReduceLog set cnvcReduceState='2' "
+
" where cnnOrderSerialNo in (select cnnOrderSerialNo from tbProduceOrderLog where cnvcType='2' and cnnProduceSerialNo=" + oldLog.cnnProduceSerialNo + ") ";
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrder);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrderAdd);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdateOrderReduce);
//更新生产计划状态
string strUpdteProduceAdd =
"update tbProduceDetailAdd set cnvcState='4' where cnvcState='3' and cnnProduceSerialNo=" +
oldLog.cnnProduceSerialNo;
string strUpdteProduceReduce =
"update tbProduceDetailReduce set cnvcState='4' where cnvcState='3' and cnnProduceSerialNo=" +
oldLog.cnnProduceSerialNo;
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdteProduceAdd);
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strUpdteProduceReduce);
oldLog.cnvcProduceState = "4";
EntityMapping.Update(oldLog, trans);
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "分货流水：" + produceLog.cnnProduceSerialNo.ToString();
EntityMapping.Create(operLog, trans);
trans.Commit();
}
catch (SqlException sex)
{
trans.Rollback();
LogAdapter.WriteDatabaseException(sex);
throw sex;
}
catch (Exception ex)
{
trans.Rollback();
LogAdapter.WriteFeaturesException(ex);
throw ex;
}
finally
{
ConnectionPool.ReturnConnection(conn);
}
}
}