public static DataTable GetOperByName(SqlConnection conn, string strOperName)
{
OperArgs args = GetCurrentArgs();
args.cnvcOperName.Value = strOperName;
return(SingleTableQuery.ExcuteQuery("tbOper", args.cnvcOperName, conn));
}
// 这里写你的代码
//获取某个操作员的功能列表
public static DataTable GetOneOperFuncList(SqlConnection conn, string strOperID)
{
OperFuncArgs args = new OperFuncArgs();
args.cnnOperID.Value = strOperID;
return(SingleTableQuery.ExcuteQuery("tbOperFunc", args.cnnOperID, conn));
}
// 这里写你的代码
public static DataTable GetAllFunction(SqlConnection conn)
{
FuncArgs args = GetCurrentArgs();
QueryOrderCollection order = new QueryOrderCollection();
order.Add(args.cnvcFuncName.FieldName, QueryOperationSign.OrderOperation.ASC);
return(SingleTableQuery.ExcuteQuery("tbFunc", order, conn));
}
public void UpdateCost(OperLog operLog)//,BusiLog busiLog)
{
using (SqlConnection conn = ConnectionPool.BorrowConnection())
{
//conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
//tbMaterial
DataTable dtMaterial = SingleTableQuery.ExcuteQuery("tbMaterial", trans);
DataTable dtDosage = SingleTableQuery.ExcuteQuery("tbDosage", trans);
DataTable dtFormula = SqlHelper.ExecuteDataTable(trans, CommandType.Text, "select cnvcProductType,cnvcProductClass,cnvcProductCode,cnnMaterialCostSum,cnnPackingCostSum,cnnCostSum,cnnPortionCount from tbFormula");
//DataRow[] drDosage_Raw_Pack = dtDosage.Select("cnvcProductType<>'SEMIPRODUCT'");
//DataRow[] drDosage_Semi = dtDosage.Select("cnvcProductType='SEMIPRODUCT'");
//更新原料材料价格
foreach (DataRow drDosage in dtDosage.Rows)
{
Dosage dosage = new Dosage(drDosage);
DataRow[] drMaterial = dtMaterial.Select("cnvcMaterialCode='" + dosage.cnvcCode + "'");
if (drMaterial.Length > 0)
{
Material material = new Material(drMaterial[0]);
if (dosage.cnnPrice != material.cnnPrice)
{
dosage.cnnPrice = material.cnnPrice;
dosage.cnnSum = Math.Round(dosage.cnnPrice * dosage.cnnCount, 4);
EntityMapping.Update(dosage, trans);
drDosage["cnnPrice"] = dosage.cnnPrice;
drDosage["cnnSum"] = dosage.cnnSum;
}
}
}
foreach (DataRow drFormula in dtFormula.Rows)
{
Entity.Formula formula = new AMSApp.zhenghua.Entity.Formula(drFormula);
if (formula.cnvcProductClass == "")
{
continue;
}
UpdatePrice(trans, formula, ref dtDosage, ref dtFormula);
}
string strSysTime = SqlHelper.ExecuteScalar(trans, CommandType.Text, "select getdate()").ToString();
DateTime dtSysTime = DateTime.Parse(strSysTime);
operLog.cndOperDate = dtSysTime;
EntityMapping.Create(operLog, trans);
//EntityMapping.Create(busiLog, 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 AssignOut(string strProduceSerialNo, OperLog operLog, string strWarehouse)
{
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);
string strsql1 = "SELECT * FROM tbProduceLog WHERE cnvcProduceState='6' and cnnProduceSerialNo=" + strProduceSerialNo;
DataTable dtProduceLog = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strsql1);
//rr.cndMakeDate = dtSysTime;
//EntityMapping.Create(rr,trans);
if (dtProduceLog.Rows.Count == 0)
{
throw new Exception("生产计划不在分货状态！");
}
Entity.ProduceLog pl = new ProduceLog(dtProduceLog);
string strsql2 = "select * from tbproducechecklog WHERE cnnAssignCount>0 and cnnproduceserialno=" + strProduceSerialNo;
DataTable dtProduceCheckLog = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strsql2);
if (dtProduceCheckLog.Rows.Count == 0)
{
throw new Exception("分货出库产品数量都为0");
}
DataTable dtInv = SingleTableQuery.ExcuteQuery("tbInventory", trans);
DataTable dtComputationUnit = SingleTableQuery.ExcuteQuery("tbComputationUnit", trans);
string strsql3 = "select * from tbassignlog where cnnproduceserialno=" + strProduceSerialNo;
DataTable dtAssignLog = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strsql3);
if (dtAssignLog.Rows.Count == 0)
{
throw new Exception("未找到分货流水");
}
Entity.AssignLog al = new AssignLog(dtAssignLog);
Entity.RdRecord rr = new RdRecord();
rr.cnvcRdCode = "RD010";
rr.cnvcRdFlag = "0";
rr.cnvcWhCode = strWarehouse;
rr.cnvcDepID = pl.cnvcProduceDeptID;
//rr.cnvcOperName = operLog.cnvcop
rr.cnvcComments = "分货出库";
rr.cnvcMaker = operLog.cnvcOperID;
rr.cnnProorderID = pl.cnnProduceSerialNo;
rr.cnvcState = "2";
rr.cndMakeDate = dtSysTime;
long rrid = EntityMapping.Create(rr, trans);
foreach (DataRow drProduceCheckLog in dtProduceCheckLog.Rows)
{
Entity.ProduceCheckLog pcl = new ProduceCheckLog(drProduceCheckLog);
Entity.RdRecordDetail rrd = new RdRecordDetail();
rrd.cnvcFlag = "0";
rrd.cndExpDate = pcl.cndExpDate; //Convert.ToDateTime(this.txtProduceDate.Text).AddDays(pc.cnnDays).AddDays(Convert.ToDouble(txtDays.Text));//Convert.ToDateTime(this.txtExpDate.Text);
rrd.cndMdate = pcl.cndMDate; //Convert.ToDateTime(this.txtProduceDate.Text);
rrd.cnnRdID = Convert.ToDecimal(rrid);
rrd.cnvcPOID = al.cnnAssignSerialNo.ToString();
DataRow[] drinvs = dtInv.Select("cnvcInvCode='" + pcl.cnvcInvCode + "'");
if (drinvs.Length == 0)
{
throw new Exception(pcl.cnvcInvCode + "存货档案未设置");
}
Entity.Inventory inv = new AMSApp.zhenghua.Entity.Inventory(drinvs[0]);
rrd.cnvcInvCode = inv.cnvcInvCode;
rrd.cnvcGroupCode = inv.cnvcGroupCode;
rrd.cnvcComunitCode = inv.cnvcSTComUnitCode;
DataRow[] drcus = dtComputationUnit.Select("cnvcGroupCode='" + inv.cnvcGroupCode + "' and cnbMainUnit=1");
if (drcus.Length == 0)
{
throw new Exception(inv.cnvcGroupCode + "未设置主计量单位");
}
decimal dchangerate = Convert.ToDecimal(drcus[0]["cnichangrate"].ToString());
DataRow[] drcus1 = dtComputationUnit.Select("cnvcGroupCode='" + inv.cnvcGroupCode + "' and cnvcComUnitCode='" + inv.cnvcSTComUnitCode + "'");
decimal dchangerate_st = Convert.ToDecimal(drcus1[0]["cnichangrate"].ToString());
// string strcssql = "SELECT * FROM tbCurrentStock WHERE cnvcWhCode='"+strWarehouse+"' AND cnvcInvCode='"+pcl.cnvcInvCode+"'";
// DataTable dtcs = SqlHelper.ExecuteDataTable(trans,CommandType.Text,strcssql);
string strcssql = "SELECT * FROM tbCurrentStock WHERE cnvcWhCode='" + strWarehouse + "' AND cnvcInvCode='" + pcl.cnvcInvCode + "'"
+ " and CONVERT(char(10),isnull(cndExpDate,''),121)>=CONVERT(char(10),getdate(),121) ";
string strcssql2 = "SELECT isnull(sum(cnnAvaQuantity),0) FROM tbCurrentStock WHERE cnvcWhCode='" + strWarehouse + "' AND cnvcInvCode='" + pcl.cnvcInvCode + "'"
+ " and CONVERT(char(10),isnull(cndExpDate,''),121)>=CONVERT(char(10),getdate(),121) ";
DataTable dtcs = SqlHelper.ExecuteDataTable(trans, CommandType.Text, strcssql);
decimal davaquantity = Convert.ToDecimal(SqlHelper.ExecuteScalar(trans, CommandType.Text, strcssql2).ToString());
if (dtcs.Rows.Count == 0)
{
throw new Exception(pcl.cnvcInvCode + "无库存");
}
if (davaquantity - pcl.cnnAssignCount * dchangerate_st / dchangerate < 0)
{
throw new Exception(pcl.cnvcInvCode + "库存不足");
}
// if(cs.cnnAvaQuantity - pcl.cnnAssignCount<0)
// throw new Exception(pcl.cnvcInvCode+"库存不足");
decimal dhave = 0;
foreach (DataRow drcs in dtcs.Rows)
{
Entity.CurrentStock cs = new CurrentStock(drcs);
// if(cs.cnnAvaQuantity - pcl.cnnAssignCount<0)
// throw new Exception(pcl.cnvcInvCode+"库存不足");
// cs.cnnAvaQuantity = cs.cnnAvaQuantity - pcl.cnnAssignCount;
// cs.cnnQuantity = cs.cnnQuantity - pcl.cnnAssignCount;
// EntityMapping.Update(cs,trans);
if (cs.cnnAvaQuantity > pcl.cnnAssignCount * dchangerate_st / dchangerate - dhave)
{
cs.cnnAvaQuantity = cs.cnnAvaQuantity - pcl.cnnAssignCount * dchangerate_st / dchangerate;
cs.cnnQuantity = cs.cnnQuantity - pcl.cnnAssignCount * dchangerate_st / dchangerate;
EntityMapping.Update(cs, trans);
break;
}
else
{
cs.cnnAvaQuantity = 0;
cs.cnnQuantity = 0;
EntityMapping.Update(cs, trans);
dhave += cs.cnnAvaQuantity;
}
}
rrd.cnnQuantity = pcl.cnnAssignCount * dchangerate_st / dchangerate;
EntityMapping.Create(rrd, trans);
}
//string strsql = "update tbMakeDetail set cnbCollar=1 where cnnMakeSerialNo="+strMakeSerialNo;
//SqlHelper.ExecuteNonQuery(trans,CommandType.Text,strsql);
string strsql4 = "update tbproducelog set cnvcproducestate='7' where cnnproduceserialno=" + strProduceSerialNo;
SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strsql4);
operLog.cndOperDate = dtSysTime;
operLog.cnvcComments = "生产流水：" + strProduceSerialNo;
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 static DataTable GetAllOper(SqlConnection conn)
{
return(SingleTableQuery.ExcuteQuery("tbOper", conn));
}
