/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TMC_ROLL_DEPLOY_LOG GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_BATCH_NO,C_MAT_CODE,C_STL_GRD,C_SPEC,C_OLDAREA,C_NEWAREA,C_EMPID,C_EMPNAME,D_MOD from TMC_ROLL_DEPLOY_LOG ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TMC_ROLL_DEPLOY_LOG model = new Mod_TMC_ROLL_DEPLOY_LOG();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TB_BCBZ GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,D_START,D_END,C_BC,C_BZ,C_EMP_ID,D_MOD_DT from TB_BCBZ ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TB_BCBZ model = new Mod_TB_BCBZ();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TPB_SLABWH GetModel_Interface_Trans(string C_SLABWH_CODE)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_SLABWH_CODE,C_SLABWH_NAME,D_START_DATE,D_END_DATE,N_STATUS,C_REMARK,C_EMP_ID,D_MOD_DT from TPB_SLABWH ");
strSql.Append(" where C_SLABWH_CODE=:C_SLABWH_CODE ");
OracleParameter[] parameters =
{
new OracleParameter(":C_SLABWH_CODE", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_SLABWH_CODE;
Mod_TPB_SLABWH model = new Mod_TPB_SLABWH();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TMC_ROLL_APPLY GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_TITLE,C_CONTENT,C_EMPID,C_EMPNAME,D_MOD,C_STATUS,C_APPROVEID,D_APPROVEDATE from TMC_ROLL_APPLY ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TMC_ROLL_APPLY model = new Mod_TMC_ROLL_APPLY();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TQB_STD_MAIN GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_STD_TYPE,C_STD_CODE,C_STD_DESC,C_STL_GRD,C_PROD_NAME,C_PROD_KIND,C_USES,n_EDIT_NUM,C_EDITION,C_IS_LF,C_IS_RH,C_IS_HL,C_IS_XM,C_IRON_REQUIRE,C_STOCK_REQUIRE,C_DELIVERY_STATE,C_PHYSICS_TIME,N_IS_CHECK,N_STATUS,C_REMARK,C_EMP_ID,D_MOD_DT from TQB_STD_MAIN ");
strSql.Append(" where C_ID=:C_ID and N_IS_CHECK=1 and N_STATUS=1");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TQB_STD_MAIN model = new Mod_TQB_STD_MAIN();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TMO_ORDER_MATCH GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_ORDER_NO,C_MATCH_ID,C_OLD_ORDER_NO,C_TYPE,D_DT from TMO_ORDER_MATCH ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TMO_ORDER_MATCH model = new Mod_TMO_ORDER_MATCH();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TMP_PLAN GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_NO,C_CON_NO,D_PLANSEND_DT,C_MAT_CODE,C_MAT_NAME,C_SPEC,C_STL_GRD,C_QUALIRY_LEV,N_WGT,N_SENDNUM_WGT,C_ATSTATION,C_AREA,C_ADDR,C_SHIPVIA,C_CGC,C_ORGO_CUST,C_SEND_STOCK_DEPT,N_STATUS,D_ORRE_AOG_DT,C_SEND_STOCK,C_AOG_STOCK_DEPT,C_AOG_STOCK,C_SEND_COM,C_SALE_COM,C_AOG_COM,C_SEND_AREA,C_SEND_ADDR,C_SEND_SITE,C_AOG_SITE,N_OUT_STOCK_WGT,N_SIGN_WGT,N_BACK_WGT,N_DAMAGE_WGT,D_FOR_PLAN_DT,C_FOR_PALN_ID,D_APPROVE_DT,C_APPROVE_ID,C_ORDER_TYPE,N_SORO_BACK,C_REMARK,C_BUSINESS_DEPT,C_BUSINESS_TYPE,C_SALESMAN,C_EMP_ID,C_EMP_NAME,D_MOD_DT from TMP_PLAN ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TMP_PLAN model = new Mod_TMP_PLAN();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TSC_SLAB_MAIN GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_PLAN_ID,C_ORD_NO,C_STOVE,C_STA_ID,C_STA_CODE,C_STA_DESC,C_STL_GRD,C_STL_GRD_PRE,C_MAT_CODE,C_MAT_NAME,C_SPEC,N_LEN,N_QUA,N_WGT,C_STD_CODE,C_SLAB_TYPE,D_CCM_DATE,C_CCM_SHIFT,C_CCM_GROUP,C_CCM_EMP_ID,C_MOVE_TYPE,C_SC_STATE,D_ESC_DATE,D_LSC_DATE,C_QKP_STATE,C_KP_ID,C_CON_NO,C_CUS_NO,C_CUS_NAME,D_WL_DATE,C_WL_SHIFT,C_WL_GROUP,C_WL_EMP_ID,D_WE_DATE,C_WE_SHIFT,C_WE_GROUP,C_WE_EMP_ID,C_STOCK_STATE,C_MAT_TYPE,C_QGP_STATE,C_SLABWH_CODE,C_SLABWH_AREA_CODE,C_SLABWH_LOC_CODE,C_SLABWH_TIER_CODE,C_Q_RESULT,D_Q_DATE,C_Q_NOTE,N_WGT_METER,C_QF_NAME,C_DESIGN_NO,C_ZRBM,C_IS_DEPOT,C_ISXM,C_XMGX,C_ISFREE from TSC_SLAB_MAIN ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TSC_SLAB_MAIN model = new Mod_TSC_SLAB_MAIN();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TMC_TECH_CONSULT GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_QUEST_ID,C_CUST_NAME,C_CUST_CODE,C_STL_GRD,C_USE_DESC,C_REMARK,C_EMP_ID,C_EMP_NAME,D_MOD_DT,C_DEPT,C_EMP,D_PLAN_DT,D_FINISH_DT,C_REAL_TIME,C_RESULT,C_LEAVE_Q,C_REMARK2,N_CUST_EVAL,N_STATE,D_CUST_EVAL_DT,N_XG_EVAL,C_XG_EVAL_EMP,D_XG_EVAL_DT,C_ORDER_NO from TMC_TECH_CONSULT ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TMC_TECH_CONSULT model = new Mod_TMC_TECH_CONSULT();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetListByPage(int pageSize, int startIndex, string classID, string empID, string start, string end, string title, string state)
{
StringBuilder where = new StringBuilder();
string item = "C_ID,C_QUEST_ID,(select a.c_name from tmc_question a where a.c_id=TMC_TECH_CONSULT.c_quest_id)QUEST_NAME,C_CUST_NAME,C_CUST_CODE,C_STL_GRD,C_USE_DESC,C_REMARK,C_EMP_ID,C_EMP_NAME,D_MOD_DT,C_DEPT,C_EMP,D_PLAN_DT,D_FINISH_DT,C_REAL_TIME,C_RESULT,C_LEAVE_Q,C_REMARK2,N_CUST_EVAL,N_STATE,decode(N_STATE,0,'未处理',1,'已处理',2,'已评分',3,'办结')as N_STATE2,D_CUST_EVAL_DT,N_XG_EVAL,C_XG_EVAL_EMP,D_XG_EVAL_DT,C_ORDER_NO";
string table = "TMC_TECH_CONSULT";
string order = "D_MOD_DT desc";
if (!string.IsNullOrEmpty(empID))
{
where.Append(" and C_EMP_ID='" + empID + "'");
}
if (!string.IsNullOrEmpty(classID))
{
where.Append(" and C_QUEST_ID='" + classID + "'");
}
if (!string.IsNullOrEmpty(title))
{
where.Append(" and C_STL_GRD like '%" + title + "%'");
}
if (!string.IsNullOrEmpty(start) && !string.IsNullOrEmpty(end))
{
where.Append(" and D_MOD_DT between to_date('" + start + "', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + Convert.ToDateTime(end).AddDays(1).ToString() + "', 'yyyy-mm-dd hh24:mi:ss')");
}
if (!string.IsNullOrEmpty(state))
{
where.Append("and N_STATE=" + state + "");
}
string sql = ByPage.GenerateByPageSql(pageSize, startIndex, item, table, order, where.ToString());
return(DbHelperOra.Query(sql));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TMC_GIVEMARK_ITEM GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,C_NAME,C_FLAG,N_SCORE,N_STATUS,C_REMARK from TMC_GIVEMARK_ITEM ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TMC_GIVEMARK_ITEM model = new Mod_TMC_GIVEMARK_ITEM();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_TMC_CUST_BOOK GetModel(string C_ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select C_ID,D_ZF_DT,C_CUST_NAME,C_AREA,C_CUST_MANAGE,C_CUST_EMP,C_CUST_EMP_TEL,C_MEETING_CUST,C_MEETING_XG,C_MAIN_CONTENT,C_NEED_S_Q,C_LEAVE_Q,C_STL_GRD,C_PRO_USE,C_SITE,C_REMARK,N_TYPE from TMC_CUST_BOOK ");
strSql.Append(" where C_ID=:C_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":C_ID", OracleDbType.Varchar2, 100)
};
parameters[0].Value = C_ID;
Mod_TMC_CUST_BOOK model = new Mod_TMC_CUST_BOOK();
DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 根据区域主键获取地点主键
/// </summary>
/// <param name="pk_address"></param>
/// <returns></returns>
public DataSet GetAreaAddress(string pk_address)
{
string strsql = "SELECT T.ADDRCODE,T.PK_ADDRESS,T.PK_AREACL,T.PROVINCE FROM TMB_NCADDRESS T WHERE T.PK_AREACL='" + pk_address + "'";
return(DbHelperOra.Query(strsql));
}
public DataSet GetList(string batch, string stlgrd, string stove, string std)
{
string strSql = $@"SELECT MAX(T.C_PDF_PATCH)PDF FROM TQC_ZZS_FILE T WHERE T.C_BATCH_NO='{batch}' AND T.C_STOVE='{stove}' AND T.C_STL_GRD='{stlgrd}' AND T.C_STD_CODE='{std}' ";
return(DbHelperOra.Query(strSql));
}
public DataTable GetBySql(string sql)
{
return(DbHelperOra.Query(sql).Tables[0]);
}
/// <summary>
/// 转库操作，分摊重量到每一支，创建转库单并入库NC
/// </summary>
/// <param name="lineCode"></param>
/// <param name="inventoryItems"></param>
/// <param name="userId"></param>
public void SaveInventory(string lineCode, List <WWInventoryItem> inventoryItems, string userId)
{
List <string> zkIds = new List <string>();
// 转库单ID对应的转库数据
Dictionary <string, List <Mod_TRC_ROLL_PRODCUT> > dic = new Dictionary <string, List <Mod_TRC_ROLL_PRODCUT> >();
TransactionHelper.BeginTransaction();
try
{
var arrZkId = GetZKId(inventoryItems.Count);
// 验证
foreach (var item in inventoryItems)
{
List <Mod_TRC_ROLL_PRODCUT> list = new List <Mod_TRC_ROLL_PRODCUT>();
string sql = $@"
select t.* from TRC_ROLL_PRODCUT T
WHERE T.C_BATCH_NO='{item.BatchNo}' AND T.C_STL_GRD='{item.Gz}' AND T.C_STD_CODE='{item.Zxbz}'
AND T.C_SPEC='{item.Spec}'
AND T.c_judge_lev_zh = '{item.ZLDJ}'
AND T.C_LINEWH_CODE = '{item.InventoryCode}'
AND T.c_move_type IN ('QE','E')
";
list.AddRange(DbHelperOra.Query(sql).Tables[0].DataTableToList2 <Mod_TRC_ROLL_PRODCUT>());
// 验证库存是否足够
if (list.Count < item.Number)
{
throw new Exception("库存不够，不能转库");
}
if (list.Count > item.Number && list.Sum(w => w.N_WGT) <= item.Amt)
{
throw new Exception("库存量不够，请确认支数与重量");
}
if (list.Count == item.Number)
{
// 如果全部转库，则转库总重量等于库存重量
item.Amt = list.Sum(w => w.N_WGT);
}
else
{
// 更新库存量（将支数与转库的重量分摊到每支上重新计算结余的重量）
UpdateInventory(list, item.Amt, item.Number);
}
// 订单物料信息
var mtrlItem = dal_TB_MATRL_MAIN.GetModel(item.MtrlCode);
var batchItem = list.FirstOrDefault();
var zkId = arrZkId[inventoryItems.IndexOf(item)];
zkIds.Add(zkId);
// 转换仓库code为ID
string getLinewhId = "select t.C_ID from TPB_LINEWH T WHERE T.C_LINEWH_CODE='{0}'";
var targetLinewhId = TransactionHelper.GetSingle(string.Format(getLinewhId, lineCode)) as string;
var lineWhId = TransactionHelper.GetSingle(string.Format(getLinewhId, item.InventoryCode)) as string;
if (string.IsNullOrEmpty(userId))
{
userId = "1001NC100000002SS9XB";
}
// 添加转库单
dalZkd.Add(new Mod_TRC_ROLL_ZKD
{
C_BATCH_NO = item.BatchNo,
C_BZYQ = item.BZYQ,
C_EMP_ID = userId,
C_F_DW = mtrlItem.C_PK_MEASDOC,
C_JUDGE_LEV_ZH = batchItem.C_JUDGE_LEV_ZH,
C_LINEWH_CODE = item.InventoryCode,
C_MAT_CODE = item.MtrlCode,
C_MAT_DESC = batchItem.C_MAT_DESC,
C_MBLINEWH_CODE = lineCode,
C_SPEC = batchItem.C_SPEC,
C_STL_GRD = batchItem.C_STL_GRD,
C_STOVE = batchItem.C_STOVE,
C_ZKD_NO = zkId,//转库单号
C_ZYX1 = batchItem.C_ZYX1,
C_ZYX2 = batchItem.C_ZYX2,
C_ZYX4 = batchItem.C_STD_CODE,
C_Z_DW = mtrlItem.C_PK_MEASDOC,
D_MOD_DT = DateTime.Now,
D_PRODUCE_DATE = batchItem.D_MOD_DT,
N_NUM = item.Number,
N_SJNUM = item.Number,
N_SJWGT = item.Amt,
N_STATUS = 3,
N_WGT = item.Amt,//list.Take(item.Number).Sum(w => w.N_WGT),
C_LINEWH_ID = lineWhId,
C_MBLINEWH_ID = targetLinewhId,
}, true);
dic.Add(zkId, list);
}
TransactionHelper.Commit();
}
catch (Exception ex)
{
TransactionHelper.RollBack();
throw ex;
}
// 发送NC
foreach (var item in zkIds)
{
var zkdItem = dalZkd.GetListBydh(item).Tables[0].DataTableToList2 <Mod_TRC_ROLL_ZKD>().FirstOrDefault();
try
{
string directory = $"{AppDomain.CurrentDomain.BaseDirectory}\\NCXML";
if (Directory.Exists(directory) == false)
{
Directory.CreateDirectory(directory);
}
var result4i = dal_zk4i.SendXml_GP(directory, item);
//var result4a = dal_zk4a.SendXml_GP(directory, item);
if (result4i != "1")
{
throw new Exception(GetNCError(result4i));
}
//if (result4a != "1")
//{
// throw new Exception(GetNCError(result4a));
//}
}
catch (Exception ex)
{
// 上传NC异常删除转库单
dalZkd.Delete(zkdItem.C_ID);
throw new Exception(ex.Message);
}
foreach (var pItem in dic[zkdItem.C_ZKD_NO].Take((int)(zkdItem.N_NUM ?? 0)))
{
// 设置转库及未上传NC状态
DbHelperOra.ExecuteSql($@"UPDATE TRC_ROLL_PRODCUT SET " +
// 标记转库单号，用于撤销
$"C_ZKD_NO='{item}', " +
// 标记委外出库状态
$"C_MOVE_TYPE='QM_{pItem.C_MOVE_TYPE}'," +
$"C_LINEWH_CODE='{lineCode}',C_IS_TB='N' WHERE C_ID='{pItem.C_ID}'");
}
}
}
/// <summary>
/// 更新线材详情
/// </summary>
/// <param name="arrAllId"></param>
/// <param name="arrUpdateId"></param>
/// <param name="txtamt"></param>
/// <param name="moveType"></param>
public void UpdateDetail(
List <string> arrAllId,
List <string> arrUpdateId,
decimal txtamt,
string moveType)
{
var states = new string[] { "QE", "E" };
if (arrUpdateId.Count > arrAllId.Count)
{
throw new Exception("数据错误，请刷新页面重试");
}
if (states.Concat(new string[] { "S" }).Any(x => x == moveType) == false)
{
throw new Exception("数据错误，请刷新页面重试");
}
var listUpdateMoveType = new List <Mod_TRC_ROLL_PRODCUT>();
var allIds = string.Join("','", arrAllId);
var updateIds = string.Join("','", arrUpdateId);
var selectSql = @"SELECT * FROM trc_roll_prodcut where c_id in ('{0}') order by c_id";
string allSql = string.Format(selectSql, allIds);
string updateSql = string.Format(selectSql, updateIds);
TransactionHelper.BeginTransaction();
try
{
var allData = DbHelperOra.Query(allSql).Tables[0].DataTableToList2 <Mod_TRC_ROLL_PRODCUT>();
if (allData.Any(l => states.Any(x => x == l.C_MOVE_TYPE) == false))
{
throw new Exception("系统检测到数据状态已发生变化，请重试");
}
if (arrAllId.Count == arrUpdateId.Count)
{
var s2 = arrAllId.OrderBy(w => w).Zip(arrUpdateId.OrderBy(w => w), (x, y) =>
{
return(new { x, y });
}).Any(l => l.x != l.y);
if (s2)
{
throw new Exception("数据错误，请刷新页面重试");
}
// 修改所有线材的重量
var totalAmt = allData.Sum(w => w.N_WGT);
// 如果重量一致不修改
if (totalAmt != txtamt)
{
UpdateInventory(allData, txtamt, arrAllId.Count);
}
listUpdateMoveType.AddRange(allData);
}
else
{
var uptData = DbHelperOra.Query(updateSql).Tables[0].DataTableToList2 <Mod_TRC_ROLL_PRODCUT>();
if (uptData.Any(l => states.Any(x => x == l.C_MOVE_TYPE) == false))
{
throw new Exception("系统检测到数据状态已发生变化，请重试");
}
// 如果重量一致不修改
if (uptData.Sum(w => w.N_WGT) != txtamt)
{
// 修改了重量
allData = allData.OrderBy(w => uptData.Any(l => w.C_ID == l.C_ID) == false).ToList();
if (allData.Count != arrAllId.Count || uptData.Count != arrUpdateId.Count)
{
throw new Exception("数据错误，请刷新页面重试");
}
var s = arrAllId.OrderBy(w => w).Zip(allData.OrderBy(w => w.C_ID), (x, y) =>
{
return(new
{
x,
y.C_ID
});
}).Any(p => p.x != p.C_ID);
var s1 = arrUpdateId.OrderBy(w => w).Zip(uptData.OrderBy(w => w.C_ID), (x, y) =>
{
return(new
{
x,
y.C_ID
});
}).Any(p => p.x != p.C_ID);
if (s | s1)
{
throw new Exception("数据错误，请刷新页面重试");
}
var totalamt = allData.Sum(x => x.N_WGT);
if (txtamt >= totalamt)
{
throw new Exception($"不得超过总重量:{totalamt}");
}
UpdateInventory(allData, txtamt, uptData.Count);
}
listUpdateMoveType.AddRange(uptData);
}
// 修改批次状态
foreach (var item in listUpdateMoveType)
{
string uptMoveTypeSql = $"UPDATE TRC_ROLL_PRODCUT SET C_MOVE_TYPE='{moveType}' WHERE C_ID='{item.C_ID}'";
TransactionHelper.ExecuteSql(uptMoveTypeSql);
}
TransactionHelper.Commit();
}
catch (Exception ex)
{
TransactionHelper.RollBack();
throw ex;
}
}
public DataSet Get_QD_FX(string empID)
{
string strSql = $@"SELECT TS.C_DETAILNAME,
JP.N_WGT AS JP_WGT,
GS.N_WGT AS GS_WGT,
PT.N_WGT AS PT_WGT,
JK.N_WGT AS JK_WGT， ZB.N_WGT AS ZB_WGT,
ZB.N_WGT_JK AS ZB_WGT_JK,
(NVL(JP.N_WGT, 0) + NVL(GS.N_WGT, 0) + NVL(PT.N_WGT, 0)) SUMWGT,
((NVL(JP.N_WGT, 0) + NVL(GS.N_WGT, 0) + NVL(PT.N_WGT, 0)) -
NVL(ZB.N_WGT, 0)) CZ_WGT,
(NVL(JK.N_WGT, 0) - NVL(ZB.N_WGT_JK, 0)) CZ_WGT_JK
FROM (SELECT T.C_DETAILNAME
FROM TS_DIC T
WHERE T.C_TYPECODE = 'ConArea'
AND T.C_DETAILCODE NOT IN ('6', '7', '10', '11')
ORDER BY T.C_INDEX) TS
LEFT JOIN (SELECT SUM(T.N_WGT) N_WGT, T.C_AREA
FROM V_CON_ORDER_TYPE T
WHERE T.C_CON_NO IN
(select distinct a.c_task_id
from TMB_FLOWPROC t
inner join tmf_fileinfo a
on a.c_id = t.c_file_id
WHERE T.C_STEP_EMP_ID = '{empID}')
AND T.N_STATUS IN (0, 1, 2, 4)
AND T.C_TYPE = '精品线材'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
GROUP BY T.C_AREA) JP --精品线材
ON JP.C_AREA = TS.C_DETAILNAME
left join (SELECT SUM(T.N_WGT) N_WGT, T.C_AREA
FROM V_CON_ORDER_TYPE T
WHERE T.C_CON_NO IN
(select distinct a.c_task_id
from TMB_FLOWPROC t
inner join tmf_fileinfo a
on a.c_id = t.c_file_id
WHERE T.C_STEP_EMP_ID = '{empID}')
AND T.N_STATUS IN (0, 1, 2, 4)
AND T.C_TYPE = '高速线材'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
GROUP BY T.C_AREA) GS --高速线材--普碳钢
ON GS.C_AREA = TS.C_DETAILNAME
left join (SELECT SUM(T.N_WGT) N_WGT, T.C_AREA
FROM V_CON_ORDER_TYPE T
WHERE T.C_CON_NO IN
(select distinct a.c_task_id
from TMB_FLOWPROC t
inner join tmf_fileinfo a
on a.c_id = t.c_file_id
WHERE T.C_STEP_EMP_ID = '{empID}')
AND T.N_STATUS IN (0, 1, 2, 4)
AND T.C_TYPE = '普碳钢'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
GROUP BY T.C_AREA) PT --普碳钢
ON PT.C_AREA = TS.C_DETAILNAME
left join (SELECT SUM(T.N_WGT) N_WGT, T.C_AREA
FROM V_CON_ORDER_TYPE T
WHERE T.C_CON_NO IN
(select distinct a.c_task_id
from TMB_FLOWPROC t
inner join tmf_fileinfo a
on a.c_id = t.c_file_id
WHERE T.C_STEP_EMP_ID = '{empID}')
AND T.N_STATUS IN (0, 1, 2, 4)
AND T.C_FLAG = 'Y'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
GROUP BY T.C_AREA) JK --监控钢种
ON JK.C_AREA = TS.C_DETAILNAME
LEFT JOIN (SELECT T.C_AERA_ID, T.N_WGT, T.N_WGT_JK
FROM TMB_AREAPLAN T
WHERE TO_CHAR(T.D_START, 'YYYY-MM') =
TO_CHAR(SYSDATE, 'YYYY-MM')) ZB --签单指标
ON ZB.C_AERA_ID = TS.C_DETAILNAME";
return(DbHelperOra.Query(strSql));
}
/// <summary>
/// 火运/汽运审核
/// </summary>
/// <param name="fyd">发运单</param>
/// <param name="emp">制单人</param>
/// <param name="conno">合同号</param>
/// <param name="cust">客户</param>
/// <param name="stlgrd">钢种</param>
/// <param name="spec">规格</param>
/// <param name="ischeck">是否审核</param>
/// <param name="flag">是否监控</param>
/// <param name="startDT">提报开始时间</param>
/// <param name="endDT">提报截至时间</param>
/// <returns></returns>
public DataSet GetTrainMain(string fyd, string emp, string conno, string cust, string stlgrd, string spec, string ischeck, string flag, string startDT, string endDT, string fyfs, string station, string area, string xfwgt)
{
string strSql = $@"SELECT T.C_ID,
T.C_STATION,
T.C_SHIPVIA,
T.D_DT,
T.D_CHECKTIME,
T.C_CHECKEMP,
T.C_EMPNAME,
T.C_ISCHECK,
A.C_AREA,
A.C_CONNO,
A.C_DH_COMPANY,
A.C_STL_GRD,
A.C_SPEC,
A.N_WGT,
NVL(B.C_FLAG, 'N') C_FLAG,
A.C_ID AS C_ID2
FROM TMC_TRAIN_MAIN T
INNER JOIN TMC_TRAIN_ITEM A
ON A.C_PKID = T.C_ID
LEFT JOIN TMB_MONI_STLGRD B
ON B.C_STL_GRD = A.C_STL_GRD WHERE T.C_ISCHECK='{ischeck}'";
if (!string.IsNullOrEmpty(fyd))
{
strSql += $" AND T.C_ID = '{fyd}'";
}
if (!string.IsNullOrEmpty(emp))
{
strSql += $" AND T.C_EMPNAME = '{emp}'";
}
if (!string.IsNullOrEmpty(conno))
{
strSql += $" AND A.C_CONNO = '{conno}'";
}
if (!string.IsNullOrEmpty(cust))
{
strSql += $" AND A.C_DH_COMPANY='{cust}'";
}
if (!string.IsNullOrEmpty(stlgrd))
{
strSql += $" AND A.C_STL_GRD='{stlgrd}'";
}
if (!string.IsNullOrEmpty(spec))
{
strSql += $" AND A.C_SPEC LIKE '%{spec}%'";
}
if (!string.IsNullOrEmpty(flag))
{
strSql += $" AND B.C_FLAG='{flag}'";
}
if (!string.IsNullOrEmpty(startDT) && !string.IsNullOrEmpty(endDT))
{
strSql += $@" AND T.D_DT BETWEEN TO_DATE('{startDT}', 'yyyy-mm-dd hh24:mi:ss') AND to_date('{endDT} 23:59:59', 'yyyy-mm-dd hh24:mi:ss')";
}
if (!string.IsNullOrEmpty(fyfs))
{
strSql += $" AND T.C_SHIPVIA='{fyfs}'";
}
if (!string.IsNullOrEmpty(station))
{
strSql += $" AND T.C_STATION LIKE '%{station}%'";
}
if (!string.IsNullOrEmpty(area))
{
strSql += $" AND A.C_AREA = '{area}'";
}
if (!string.IsNullOrEmpty(xfwgt))
{
DataView dv = DbHelperOra.Query(strSql).Tables[0].DefaultView;
dv.Sort = "N_WGT ASC";
DataTable dt = dv.ToTable();
string listID = "";
var list = new List <string>();
decimal wgt = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
wgt = wgt + Convert.ToDecimal(dt.Rows[i]["N_WGT"]);
list.Add(dt.Rows[i]["C_ID2"].ToString());
if (wgt >= Convert.ToDecimal(xfwgt))
{
break;
}
}
listID = "'" + string.Join("','", list.ToArray()) + "'";
if (!string.IsNullOrEmpty(listID))
{
strSql += $" AND A.C_ID IN({listID})";
}
return(DbHelperOra.Query(strSql));
}
else
{
return(DbHelperOra.Query(strSql));
}
}
/// <summary>
/// 按类统计已初始化订单信息
/// </summary>
/// <param name="strIniID">方案主键</param>
/// <param name="strGZ">钢种</param>
/// <param name="strBZ">标准</param>
/// <param name="strRollID">轧线</param>
/// <param name="strCCMID">连铸</param>
/// <param name="rolltype">轧钢是否排产</param>
/// <param name="ccmtype">炼钢是否排产</param>
/// <param name="strTJ">统计类别:1轧钢2连铸，3钢种,4产线规格</param>
/// <returns></returns>
public DataTable GetOrderIniTJ(string strIniID, string strGZ, string strBZ, string strRollID, string strCCMID, int?rolltype, int?ccmtype, string strTJ)
{
string sql = "";
string sql2 = " ";
if (strTJ == "1")
{
sql = sql + " SELECT SUM(X.N_WGT) N_WGT ,X.C_ROLL_DESC FROM ";
sql2 = sql2 + " GROUP BY X.C_ROLL_DESC ORDER BY X.C_ROLL_DESC ";
}
if (strTJ == "2")
{
sql = sql + " SELECT SUM(X.N_WGT) N_WGT ,X.C_CCM_DESC FROM ";
sql2 = sql2 + " GROUP BY X.C_CCM_DESC ORDER BY X.C_CCM_DESC ";
}
if (strTJ == "3")
{
sql = sql + " SELECT SUM(X.N_WGT) N_WGT ,X.C_STL_GRD,X.C_STD_CODE FROM ";
sql2 = sql2 + " GROUP BY X.C_STL_GRD,X.C_STD_CODE ORDER BY X.C_STL_GRD ";
}
if (strTJ == "4")
{
sql = sql + " SELECT SUM(X.N_WGT) N_WGT, X.C_ROLL_DESC,X.C_SPEC FROM ";
sql2 = sql2 + " GROUP BY X.C_ROLL_DESC,X.C_SPEC ORDER BY X.C_ROLL_DESC,X.C_SPEC ";
}
sql = sql + "(SELECT a.c_id,A.C_INITIALIZE_ID, A.C_ORDER_ID, A.N_WGT, A.C_ROLL_STA_ID, ( SELECT M.C_STA_DESC FROM TB_STA M WHERE M.C_ID= A.C_ROLL_STA_ID) C_ROLL_DESC, A.N_MACH_WGT, A.C_CCM_STA_ID, ( SELECT M.C_STA_DESC FROM TB_STA M WHERE M.C_ID= A.C_CCM_STA_ID) C_CCM_DESC, A.N_MACH_WGT_CCM, A.C_ID, T.C_ORDER_NO, T.C_CON_NO, T.C_CON_NAME, T.C_AREA, T.C_MAT_CODE, T.C_MAT_NAME, T.C_TECH_PROT, T.C_SPEC, T.C_STL_GRD, T.C_STD_CODE, T.C_FREE_TERM, T.C_FREE_TERM2, T.C_PRO_USE, T.D_NEED_DT, T.D_DELIVERY_DT, T.C_PACK, T.C_LEV FROM TPP_INITIALIZE_ORDER A, TMO_CON_ORDER T WHERE A.C_ORDER_ID = T.C_ID AND A.C_INITIALIZE_ID='" + strIniID + "'";
if (rolltype != null)
{
sql = sql + " AND A.N_EXEC_STATUS=" + rolltype + " ";
}
if (ccmtype != null)
{
sql = sql + " AND A.N_LGPC_STATUS=" + ccmtype + " ";
}
if (strGZ.Trim().Length > 0)
{
sql = sql + " AND UPPER( T.C_STL_GRD) LIKE UPPER( '" + strGZ + "') ";
}
if (strBZ.Trim().Length > 0)
{
sql = sql + " AND UPPER( T.C_STD_CODE) LIKE UPPER( '" + strBZ + "') ";
}
if (strRollID.Trim().Length > 0)
{
sql = sql + " AND A.C_ROLL_STA_ID = '" + strRollID + "' ";
}
if (strCCMID.Trim().Length > 0)
{
sql = sql + " AND A.C_CCM_STA_ID = '" + strCCMID + "' ";
}
sql = sql + " ) X ";
sql = sql + sql2;
return(DbHelperOra.Query(sql).Tables[0]);
}
/// <summary>
/// 销售组织
/// </summary>
/// <returns></returns>
public DataSet GetSaleOrg()
{
string strSql = $"SELECT DISTINCT T.C_SALESORG FROM TMB_STLGRD_PRICE_WGT T";
return(DbHelperOra.Query(strSql));
}
public DataSet GetAddrNCPK(string name)
{
string strSql = $"SELECT T.C_ADDR_NCPK FROM TMB_NCADDRESS_COST T WHERE T.C_ADDRNAME='{name}'";
return(DbHelperOra.Query(strSql));
}
public DataSet GetAddrList(string where)
{
string strSql = "SELECT T.C_ID, T.ADDRCODE,T.ADDRNAME AS C_NAME, T.PK_AREACL AS N_PARENTID FROM TMB_NCADDRESS T";
return(DbHelperOra.Query(strSql));
}
/// <summary>
/// 校验当前区域下发订单是否满足排产指标
/// </summary>
/// <param name="wgt">订单排产量</param>
/// <param name="area">区域</param>
/// <param name="orderNo">订单号</param>
/// <returns></returns>
public bool CheckStl_Grd_ZB(decimal wgt, string area, string orderNo, string xday)
{
//TRUNC((TO_CHAR(SYSDATE, 'DD')-1)/10)
bool result = true;
string strSql = $@"SELECT T.N_DAY_JK_ZB,T.N_DAY_JP_ZB,T.N_DAY_PZ_ZB,T.C_FLAG,T.C_AREA
FROM TMO_ORDER_PCZB T
WHERE T.C_AREA = '{area}'
AND T.C_FLAG = 'Y'
AND T.N_TYPE={xday}
AND TO_CHAR(T.D_PLAN_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')";
DataTable dtZB = DbHelperOra.Query(strSql).Tables[0]; //获取排产指标
if (dtZB.Rows.Count > 0) //判定是否指标控制
{
string strSql2 = $@"SELECT * FROM V_CON_ORDER_TYPE T WHERE T.C_ORDER_NO='{orderNo}'";
DataTable dt2 = DbHelperOra.Query(strSql2).Tables[0]; //获取当前订单基本信息
if (dt2.Rows.Count > 0) //获取钢种监控/精品/品种
{
if (dt2.Rows[0]["C_FLAG"].ToString() == "Y") //监控钢种
{
#region //监控
//获取当前区域当月旬监控提报排产总量
string strSql3 = $@"SELECT NVL(SUM(T.N_WGT),0) N_WGT
FROM V_ORDER_PLAN T
WHERE T.C_FLAG = 'Y' AND T.C_AREA='{dt2.Rows[0]["C_AREA"].ToString()}'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}";
decimal pcwgt = Convert.ToDecimal(DbHelperOra.GetSingle(strSql3)) + wgt;
if (pcwgt > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_JK_ZB"]))//判定是否超出旬监控指标
{
result = false;
}
else
{
if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_JK"]))//判定是否满足单项监控指标
{
result = false;
}
}
#endregion
}
else
{
switch (dt2.Rows[0]["C_TYPE"].ToString())
{
case "普碳钢":
#region //普碳钢
//获取当前区域当前月当旬品种总排产量
string strSql4 = $@"SELECT NVL(SUM(T.N_WGT), 0) N_WGT
FROM V_ORDER_PLAN T
WHERE T.C_TYPE IN ('普碳钢')
AND T.C_AREA = '{dt2.Rows[0]["C_AREA"].ToString()}'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}";
decimal pcwgt_pt = Convert.ToDecimal(DbHelperOra.GetSingle(strSql4)) + wgt;
if (pcwgt_pt > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_PZ_ZB"])) //判定是否超出旬品种指标
{
result = false;
}
else
{
if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_PZ"])) //判定是否满足单项品种指标
{
result = false;
}
}
#endregion
break;
case "高速线材":
#region //高速线材
//获取当前区域当前月当旬品种总排产量
string strSql6 = $@"SELECT NVL(SUM(T.N_WGT), 0) N_WGT
FROM V_ORDER_PLAN T
WHERE T.C_TYPE IN ('普碳钢')
AND T.C_AREA = '{dt2.Rows[0]["C_AREA"].ToString()}'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}";
decimal pcwgt_pz = Convert.ToDecimal(DbHelperOra.GetSingle(strSql6)) + wgt;
if (pcwgt_pz > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_PZ_ZB"])) //判定是否超出旬品种指标
{
result = false;
}
else
{
if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_PZ"])) //判定是否满足单项品种指标
{
result = false;
}
}
#endregion
break;
case "精品线材":
#region //精品线材
//获取当前区域当前月当旬精品总排产量
string strSql5 = $@"SELECT NVL(SUM(T.N_WGT), 0) N_WGT
FROM V_ORDER_PLAN T
WHERE T.C_TYPE IN ('精品线材')
AND T.C_AREA = '{dt2.Rows[0]["C_AREA"].ToString()}'
AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')
AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}";
decimal pcwgt_jp = Convert.ToDecimal(DbHelperOra.GetSingle(strSql5)) + wgt;
if (pcwgt_jp > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_JP_ZB"])) //判定是否超出旬精品指标
{
result = false;
}
else
{
if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_JP"])) //判定是否满足单项精品指标
{
result = false;
}
}
#endregion
break;
}
}
}
}
return(result);
}
/// <summary>
/// 获取NC调价单
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public DataSet GetNCStlGrd(string date)
{
string strSql = $"SELECT * FROM TMB_STLGRD_PRICE T WHERE T.C_DAY='{date}'";
return(DbHelperOra.Query(strSql));
}
public DataSet GetSaleOutList(string date)
{
string str = $@"---销售出库汇总
SELECT TT.MAXAREA,
TT.AREA,
T2.N_ZKWGT, --当日库存
T3.N_YJWGT, --当日预警库存
TA.ZZB,
TA.N_KFY AS KFYL,
TA.JKZB,
TA.N_QYFY,
TB.N_FYWGT,
TC.QYCK,
TD.HYCK,
TE.SLABWGT,
TF.JKWGT,
TG.CRCK,
TH.YJCK --预警出库
FROM (SELECT decode(T.C_DETAILNAME,
'北方销售一部',
'北方',
'北方销售二部',
'北方',
'普碳钢销售部',
'北方',
'硬线销售部',
'北方',
'南方销售一部',
'南方',
'南方销售二部',
'南方',
'南方销售三部',
'南方',
'南方销售四部',
'南方',
'精特钢销售一部',
'精特',
'精特钢销售二部',
'精特',
'弹簧钢销售部',
'精特',
'纯铁销售部',
'精特') MAXAREA,
T.C_DETAILNAME AS AREA
FROM TS_DIC T
WHERE T.C_TYPECODE = 'ConArea'
AND T.C_DETAILCODE NOT IN ('6', '7', '10', '11','23')
ORDER BY T.C_INDEX) TT
LEFT JOIN (
--当日库存
SELECT SUM(T.N_WGT) N_ZKWGT, T.C_SALE_AREA AS AREA
FROM TRD_ROLL_XCDRKC_INFO T
WHERE T.D_MOD_DT >=
TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS') - 1
AND T.D_MOD_DT <=
(TRUNC(TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS') - 1) + 1 -
1 / 86400)
GROUP BY T.C_SALE_AREA) T2
ON T2.AREA = TT.AREA
LEFT JOIN (
--当日预警库存
SELECT SUM(T.N_WGT) N_YJWGT, T.C_SALE_AREA AS AREA
FROM TRD_ROLL_XCDRKC_INFO T
LEFT JOIN TMB_MONI_STLGRD M
ON M.C_STL_GRD = T.C_STL_GRD
LEFT JOIN V_ROLL_XC2 V --钢种分类
ON V.C_MAT_CODE = T.C_MAT_CODE
WHERE T.D_MOD_DT >=
TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS') - 1
AND T.D_MOD_DT <=
(TRUNC(TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS') - 1) + 1 -
1 / 86400)
AND (CASE
WHEN M.C_FLAG = 'Y' THEN --监控钢种开始算超期库存
CASE
WHEN CEIL(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD') -
TO_DATE(TO_CHAR((NVL(T.D_QR_DATE,
T.D_PRODUCE_DATE)),
'YYYY-MM-DD'),
'YYYY-MM-DD')) >
(SELECT A.N_YJ_DAYS
FROM TB_OVERDUE_CONFIGURE A
WHERE A.C_TYPE = '线材'
AND A.C_STL_TYPE = '监控钢种') THEN
'Y'
ELSE
'N'
END
ELSE --其他按钢种分类算超期库存
CASE
WHEN CEIL(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD') -
TO_DATE(TO_CHAR((NVL(T.D_QR_DATE,
T.D_PRODUCE_DATE)),
'YYYY-MM-DD'),
'YYYY-MM-DD')) >
(SELECT A.N_YJ_DAYS
FROM TB_OVERDUE_CONFIGURE A
WHERE A.C_TYPE = '线材'
AND A.C_STL_TYPE = V.C_TYPE) THEN
'Y'
ELSE
'N'
END
END) = 'Y'
GROUP BY T.C_SALE_AREA) T3
ON T3.AREA = TT.AREA
LEFT JOIN (
--当日库存， 总指标，监控指标，区域汽运需求
SELECT T.N_ZKWGT,
T.N_WGT AS ZZB,
(NVL(T.N_ZKWGT,0)-NVL(T.N_DPWGT,0)) N_KFY,--可发运量
T.N_FYJKZB AS JKZB,
T.N_QYFY,
T.C_DEPT AS AREA
FROM TMD_FYZB T
WHERE T.D_DAY >= TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')
AND T.D_DAY <=
(TRUNC(TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')) + 1 -
1 / 86400)) TA
ON TA.AREA = TT.AREA
LEFT JOIN (
--汽运制单量
SELECT SUM(T.N_FYWGT) N_FYWGT, T.C_AREA
FROM (SELECT T.N_FYWGT AS N_FYWGT,
(SELECT MAX(A.C_AREA)
FROM TMO_CON_ORDER A
WHERE A.C_ORDER_NO = T.C_NO) C_AREA
FROM TMD_DISPATCHDETAILS T
LEFT JOIN TMD_DISPATCH B
ON B.C_ID = T.C_DISPATCH_ID
WHERE B.D_CREATE_DT >=
TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')
AND B.D_CREATE_DT <=
(TRUNC(TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')) + 1 -
1 / 86400)
AND B.C_SHIPVIA IN
('0001NC10000000003ILR', '0001NC10000000003ILV')
AND B.C_STATUS IN ('3', '8')) T
GROUP BY T.C_AREA) TB
ON TB.C_AREA = TT.AREA
LEFT JOIN (
--汽运出库
SELECT SUM(TT.N_JZ) QYCK, TT.AREA
FROM (SELECT T.N_JZ,
(SELECT MAX(B.C_AREA)
FROM TMD_DISPATCHDETAILS A
LEFT JOIN TMO_CON_ORDER B
ON B.C_ORDER_NO = A.C_NO
WHERE A.C_ID = T.C_PK_NCID) AREA
FROM TMD_DISPATCH_SJZJB T
LEFT JOIN TMD_DISPATCH A
ON A.C_ID = T.C_DISPATCH_ID
WHERE A.C_SHIPVIA IN
('0001NC10000000003ILR', '0001NC10000000003ILV')
AND T.D_CKSJ >=
TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')
AND T.D_CKSJ <=
(TRUNC(to_date('{date}', 'YYYY-MM-DD HH24:MI:SS')) + 1 -
1 / 86400)) TT
GROUP BY TT.AREA) TC
ON TC.AREA = TT.AREA
LEFT JOIN (
--火运出库
SELECT SUM(TT.N_JZ) HYCK, TT.AREA
FROM (SELECT T.N_JZ,
(SELECT MAX(B.C_AREA)
FROM TMD_DISPATCHDETAILS A
LEFT JOIN TMO_CON_ORDER B
ON B.C_ORDER_NO = A.C_NO
WHERE A.C_ID = T.C_PK_NCID) AREA
FROM TMD_DISPATCH_SJZJB T
LEFT JOIN TMD_DISPATCH A
ON A.C_ID = T.C_DISPATCH_ID
WHERE A.C_SHIPVIA IN ('0001NC10000000003ILQ')
AND T.D_CKSJ >=
TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')
AND T.D_CKSJ <=
(TRUNC(to_date('{date}', 'YYYY-MM-DD HH24:MI:SS')) + 1 -
1 / 86400)) TT
GROUP BY TT.AREA) TD
ON TD.AREA = TT.AREA
LEFT JOIN (
---外销钢坯
SELECT SUM(TT.N_JZ) SLABWGT, TT.AREA
FROM (SELECT T.N_JZ,
(SELECT MAX(B.C_AREA)
FROM TMD_DISPATCHDETAILS A
LEFT JOIN TMO_CON_ORDER B
ON B.C_ORDER_NO = A.C_NO
WHERE A.C_ID = T.C_PK_NCID) AREA
FROM TMD_DISPATCH_SJZJB T
WHERE T.N_STATUS = 6
AND T.D_CKSJ >=
TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')
AND T.D_CKSJ <=
(TRUNC(to_date('{date}', 'YYYY-MM-DD HH24:MI:SS')) + 1 -
1 / 86400)) TT
GROUP BY TT.AREA) TE
ON TE.AREA = TT.AREA
LEFT JOIN (
--监控出库
SELECT SUM(TT.N_JZ) JKWGT, TT.AREA
FROM (SELECT T.N_JZ,
(SELECT MAX(B.C_AREA)
FROM TMD_DISPATCHDETAILS A
LEFT JOIN TMO_CON_ORDER B
ON B.C_ORDER_NO = A.C_NO
WHERE A.C_ID = T.C_PK_NCID) AREA
FROM TMD_DISPATCH_SJZJB T
LEFT JOIN TMB_MONI_STLGRD M
ON M.C_STL_GRD = T.C_STL_GRD
WHERE M.C_FLAG = 'Y'
AND T.D_CKSJ >=
TO_DATE('{date}', 'YYYY-MM-DD HH24:MI:SS')
AND T.D_CKSJ <=
(TRUNC(to_date('{date}', 'YYYY-MM-DD HH24:MI:SS')) + 1 -
1 / 86400)) TT
GROUP BY TT.AREA) TF
ON TF.AREA = TT.AREA
LEFT JOIN (
--次日出库
SELECT SUM(TT.N_JZ) CRCK, TT.AREA
FROM (SELECT T.N_JZ,
(SELECT MAX(B.C_AREA)
FROM TMD_DISPATCHDETAILS A
LEFT JOIN TMO_CON_ORDER B
ON B.C_ORDER_NO = A.C_NO
WHERE A.C_ID = T.C_PK_NCID) AREA
FROM TMD_DISPATCH_SJZJB T
where T.D_CKSJ >=
TO_DATE('{date} 00:00:00',
'YYYY-MM-DD HH24:MI:SS') + 1
AND T.D_CKSJ <=
TO_DATE('{date} 08:00:00',
'YYYY-MM-DD HH24:MI:SS') + 1) TT
GROUP BY TT.AREA) TG
ON TG.AREA = TT.AREA
LEFT JOIN (
--预警出库
SELECT SUM(TT.N_JZ) YJCK, TT.AREA
FROM (SELECT TMD.N_JZ,
TMD.AREA,
TMD.D_QR_DATE,
TMD.C_FLAG,
TMD.C_TYPE
FROM (SELECT T.N_JZ,
(SELECT MAX(B.C_AREA)
FROM TMD_DISPATCHDETAILS A
LEFT JOIN TMO_CON_ORDER B
ON B.C_ORDER_NO = A.C_NO
WHERE A.C_ID = T.C_PK_NCID) AREA,
(SELECT MAX(A.D_QR_DATE)
FROM TRC_ROLL_PRODCUT A
WHERE A.C_FYDH = T.C_DISPATCH_ID
AND A.C_BATCH_NO = T.C_BATCH_NO
AND A.C_MOVE_TYPE = 'S') D_QR_DATE,
M.C_FLAG,
v.C_TYPE
FROM TMD_DISPATCH_SJZJB T
LEFT JOIN TMB_MONI_STLGRD M
ON M.C_STL_GRD = T.C_STL_GRD
LEFT JOIN V_ROLL_XC2 V --钢种分类
ON V.C_MAT_CODE = T.C_MAT_CODE
where T.D_CKSJ >=
TO_DATE('{date} 00:00:00',
'YYYY-MM-DD HH24:MI:SS') + 1
AND T.D_CKSJ <=
TO_DATE('{date} 08:00:00',
'YYYY-MM-DD HH24:MI:SS') + 1) TMD
WHERE (CASE
WHEN TMD.C_FLAG = 'Y' THEN --监控钢种开始算超期库存
CASE
WHEN CEIL(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD') -
TO_DATE(TO_CHAR(TMD.D_QR_DATE,
'YYYY-MM-DD'),
'YYYY-MM-DD')) >
(SELECT A.N_YJ_DAYS
FROM TB_OVERDUE_CONFIGURE A
WHERE A.C_TYPE = '线材'
AND A.C_STL_TYPE = '监控钢种') THEN
'Y'
ELSE
'N'
END
ELSE --其他按钢种分类算超期库存
CASE
WHEN CEIL(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD') -
TO_DATE(TO_CHAR(TMD.D_QR_DATE,
'YYYY-MM-DD'),
'YYYY-MM-DD')) >
(SELECT A.N_YJ_DAYS
FROM TB_OVERDUE_CONFIGURE A
WHERE A.C_TYPE = '线材'
AND A.C_STL_TYPE = TMD.C_TYPE) THEN
'Y'
ELSE
'N'
END
END) = 'Y') TT
GROUP BY TT.AREA) TH
ON TH.AREA = TT.AREA";
return(DbHelperOra.Query(str));
}
/// <summary>
/// 获取火运计划子表数据
/// </summary>
/// <param name="pkID"></param>
/// <returns></returns>
public DataSet GetList(string pkID)
{
string strSql = $"SELECT * FROM TMC_TRAIN_ITEM T WHERE T.C_PKID='{pkID}'";
return(DbHelperOra.Query(strSql));
}
/// <summary>
/// 获取产品类型
/// </summary>
/// <returns></returns>
public DataSet GetProcType()
{
string strsql = "SELECT C_ID,C_NAME FROM TMB_PROC_TYPE WHERE N_STATUS=0 ORDER BY N_SORT";
return(DbHelperOra.Query(strsql));
}
/// <summary>
/// 获取火运报备计划-发运日计划申请
/// </summary>
/// <param name="conno"></param>
/// <returns></returns>
public DataSet GetTrain(string conno, string stlgrd, string spec, string custname, string startdt, string enddt, string area)
{
string strSql = $@"SELECT T.C_AREA,
T.C_CON_NO,
T.C_ORDER_NO,
B.C_NO AS C_CUSTNO,
B.C_NAME AS C_CUSTNAME,
B.C_EXTEND1 AS C_KH_BANK,
B.C_TAXPAYERNO AS C_TAXNO,
B.C_EXTEND2 AS C_ACCOUNT,
B.C_EXTEND4 AS C_TEL,
B.C_EXTEND3 AS C_ADDRESS,
T.C_SPEC,
T.C_STL_GRD,
T.D_DT,
T.C_MAT_CODE,
T.C_STD_CODE,
T.N_WGT, --原订单量
TMC.N_WGT AS PLANWGT, --计划量
TRC.N_WGT KCWGT,
(SELECT A.C_NAME
FROM TS_CUSTFILE A
WHERE A.C_NC_M_ID = T.C_RECEIPTCORPID) AS C_DH_COMPANY
FROM TMO_CON_ORDER T
LEFT JOIN TS_CUSTFILE B
ON B.C_NO = T.C_CUST_NO
LEFT JOIN (SELECT SUM(C.N_WGT) N_WGT, C.C_BILLCODE
FROM TMC_TRAIN_ITEM C
WHERE C.C_FLAG = '1'
GROUP BY C.C_BILLCODE) TMC
ON TMC.C_BILLCODE = T.C_ORDER_NO
LEFT JOIN (SELECT SUM(T.N_WGT) N_WGT,
T.C_MAT_CODE,
T.C_STD_CODE,
T.C_SALE_AREA,
T.C_CON_NO,
T.C_CUST_NAME
FROM TRC_ROLL_PRODCUT T
WHERE T.C_MOVE_TYPE = 'E' AND T.C_IS_QR = 'Y'
GROUP BY T.C_MAT_CODE,
T.C_STD_CODE,
T.C_SALE_AREA,
T.C_CON_NO,
T.C_CUST_NAME) TRC
ON TRC.C_MAT_CODE = T.C_MAT_CODE
AND TRC.C_STD_CODE = T.C_STD_CODE
AND TRC.C_SALE_AREA = T.C_AREA
AND SUBSTR(TRC.C_CON_NO, 0, 15) = SUBSTR(T.C_CON_NO, 0, 15)
AND TRC.C_CUST_NAME = T.C_CUST_NAME
WHERE T.N_STATUS = 2
AND NVL(TRC.N_WGT, 0) > 0";
if (!string.IsNullOrEmpty(area))
{
strSql += $" AND T.C_AREA='{area}'";
}
if (!string.IsNullOrEmpty(conno))
{
strSql += $" AND T.C_CON_NO LIKE '%{conno}%'";
}
if (!string.IsNullOrEmpty(stlgrd))
{
strSql += $" AND T.C_STL_GRD LIKE '%{stlgrd}%'";
}
if (!string.IsNullOrEmpty(spec))
{
strSql += $" AND T.C_SPEC LIKE '%{spec}%'";
}
if (!string.IsNullOrEmpty(custname))
{
strSql += $" AND T.C_CUST_NAME LIKE '%{custname}%'";
}
if (!string.IsNullOrEmpty(startdt) && !string.IsNullOrEmpty(enddt))
{
strSql += $@" AND T.D_DT >= TO_DATE('{startdt}', 'yyyy-mm-dd hh24:mi:ss') AND T.D_DT<= to_date('{enddt} 23:59:59', 'yyyy-mm-dd hh24:mi:ss')";
}
return(DbHelperOra.Query(strSql));
}
public DataSet GetList()
{
string cmdText = "SELECT C_ID,C_STL_TYPE,N_YJ_DAYS FROM TB_OVERDUE_CONFIGURE";
return(DbHelperOra.Query(cmdText));
}