public List <C_SAP_STATION_MAP> GetSAPStationMapBySkuOrderBySAPCodeASC(string SkuNo, OleExec DB)
{
List <C_SAP_STATION_MAP> mapList = new List <C_SAP_STATION_MAP>();
string sql = string.Empty;
DataTable dt = new DataTable();
Row_C_SAP_STATION_MAP row = null;
C_SAP_STATION_MAP map = null;
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
sql = $@"SELECT * FROM C_SAP_STATION_MAP WHERE SKUNO='{SkuNo}' ORDER BY SAP_STATION_CODE ASC";
dt = DB.ExecSelect(sql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
row = (Row_C_SAP_STATION_MAP)NewRow();
row.loadData(dr);
map = row.GetDataObject();
mapList.Add(map);
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(mapList);
}
public string GetMAXSAPStationCodeBySkuAndWorkorderType(string SkuNo, string WorkorderType, OleExec DB)
{
string result = "";
string sql = string.Empty;
DataTable dt = new DataTable();
Row_C_SAP_STATION_MAP row = null;
C_SAP_STATION_MAP map = null;
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
sql = $@"SELECT MAX(SAP_STATION_CODE) FROM C_SAP_STATION_MAP WHERE SKUNO='{SkuNo}' AND WORKORDER_TYPE='{WorkorderType}' ORDER BY EDIT_TIME";
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
result = dt.Rows[0][0].ToString();
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000007", new string[] { SkuNo + "," + WorkorderType });
throw new MESReturnMessage(errMsg);
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
/// <summary>
/// add by fgg HWD 不看工單類型，MRB只取排序后的最後一個拋賬點
/// </summary>
/// <param name="skuno"></param>
/// <param name="DB"></param>
/// <returns></returns>
public string GetMAXSAPStationCodeBySku(string skuno, OleExec DB)
{
string result = "";
string sql = string.Empty;
DataTable dt = new DataTable();
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
sql = $@"select sap_station_code from c_sap_station_map c where skuno = '{skuno}' order by c.sap_station_code";
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
result = dt.Rows[dt.Rows.Count - 1]["sap_station_code"].ToString();
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000224", new string[] { skuno });
throw new MESReturnMessage(errMsg);
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
public string DeleteSAPStationMap(string ID, OleExec sfcdb)
{
string result = string.Empty;
string DeleteString = string.Empty;
Row_C_SAP_STATION_MAP row = (Row_C_SAP_STATION_MAP)NewRow();
string sql = string.Empty;
DataTable dt = new DataTable();
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
sql = $@"SELECT * FROM C_SAP_STATION_MAP WHERE ID='{ID}'";
dt = sfcdb.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
row.loadData(dt.Rows[0]);
DeleteString = row.GetDeleteString(DBType);
result = sfcdb.ExecSQL(DeleteString);
}
else
{
result = "0";
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
/// <summary>
/// 增、刪、改 SAP 與 站位的映射
/// </summary>
/// <param name="Map"></param>
/// <param name="Operation"></param>
/// <param name="Bu"></param>
/// <param name="DB"></param>
/// <returns></returns>
public string UpdateSAPStationMap(C_SAP_STATION_MAP Map, string Operation, string Bu, OleExec DB)
{
string sql = string.Empty;
string result = string.Empty;
Row_C_SAP_STATION_MAP row = (Row_C_SAP_STATION_MAP)NewRow();
T_C_SAP_STATION_MAP table = new T_C_SAP_STATION_MAP(DB, DBType);
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
if (Map.ID != null && !Map.ID.Equals(""))
{
row = (Row_C_SAP_STATION_MAP)GetObjByID(Map.ID, DB);
}
row.SKUNO = Map.SKUNO;
row.STATION_NAME = Map.STATION_NAME;
row.WORKORDER_TYPE = Map.WORKORDER_TYPE;
row.SAP_STATION_CODE = Map.SAP_STATION_CODE;
row.EDIT_EMP = Map.EDIT_EMP;
row.EDIT_TIME = Map.EDIT_TIME;
switch (Operation.ToUpper())
{
case "ADD":
row.ID = GetNewID(Bu, DB);
sql = row.GetInsertString(DBType);
break;
case "UPDATE":
sql = row.GetUpdateString(DBType);
break;
case "DELETE":
sql = row.GetDeleteString(DBType);
break;
default:
sql = $@"SELECT * FROM C_SAP_STATION_MAP";
break;
}
result = DB.ExecSQL(sql);
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
public C_PACKING GetPackingBySkuAndType(string SkuNo, string PackType, OleExec DB)
{
string sql = string.Empty;
DataTable dt = new DataTable();
C_PACKING Packing = new C_PACKING();
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
sql = $@"SELECT * FROM C_PACKING WHERE SKUNO='{SkuNo}' AND PACK_TYPE='{PackType}' AND ROWNUM=1";
dt = DB.ExecSelect(sql).Tables[0];
Row_C_PACKING row = (Row_C_PACKING)NewRow();
if (dt.Rows.Count > 0)
{
row.loadData(dt.Rows[0]);
Packing = row.GetDataObject();
}
}
return(Packing);
}
public List <C_PACKING> GetPackingBySku(string SkuNo, OleExec DB)
{
string sql = string.Empty;
DataTable dt = new DataTable();
List <C_PACKING> Packing = new List <C_PACKING>();
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
sql = $@"SELECT * FROM C_PACKING WHERE SKUNO='{SkuNo}'";
dt = DB.ExecSelect(sql).Tables[0];
Row_C_PACKING row = (Row_C_PACKING)NewRow();
for (int i = 0; i < dt.Rows.Count; i++)
{
row.loadData(dt.Rows[i]);
Packing.Add(row.GetDataObject());
}
}
return(Packing);
}
/// <summary>
/// 返回所有料號的稱重參數
/// </summary>
/// <param name="DB"></param>
/// <returns></returns>
public List <TVC_WT_CONFIG> GetAllWTList(OleExec DB)
{
List <TVC_WT_CONFIG> WTList = new List <TVC_WT_CONFIG>();
string sql = $@"Select ID,SKUNO,BMinValue,BMaxValue,CMinValue,CMaxValue,
Case when online_flag=0 then 'NO' else 'YES' end as OnLine_Flag,
Edit_Emp,Edit_Time,Approve_Emp,Approve_Time
From C_WT_CONFIG
Order by case when approve_emp is null then 0 else 1 end asc,
case when approve_time is null then edit_time
when approve_time>edit_time then approve_time else edit_time end desc";
DataTable dt = null;
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
dt = DB.ExecSelect(sql).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//這種方式可增加速度
if (dr["APPROVE_TIME"] is DBNull)
{
WTList.Add(new TVC_WT_CONFIG
{
ID = dr["ID"].ToString(),
SKUNO = dr["SKUNO"].ToString(),
BMINVALUE = dr["BMINVALUE"].ToString(),
BMAXVALUE = dr["BMAXVALUE"].ToString(),
CMINVALUE = dr["CMINVALUE"].ToString(),
CMAXVALUE = dr["CMAXVALUE"].ToString(),
ONLINE_FLAG = dr["ONLINE_FLAG"].ToString(),
EDIT_EMP = dr["EDIT_EMP"].ToString(),
EDIT_TIME = (DateTime)dr["EDIT_TIME"],
APPROVE_EMP = dr["APPROVE_EMP"].ToString(),
APPROVE_TIME = null
});
}
else
{
WTList.Add(new TVC_WT_CONFIG
{
ID = dr["ID"].ToString(),
SKUNO = dr["SKUNO"].ToString(),
BMINVALUE = dr["BMINVALUE"].ToString(),
BMAXVALUE = dr["BMAXVALUE"].ToString(),
CMINVALUE = dr["CMINVALUE"].ToString(),
CMAXVALUE = dr["CMAXVALUE"].ToString(),
ONLINE_FLAG = dr["ONLINE_FLAG"].ToString(),
EDIT_EMP = dr["EDIT_EMP"].ToString(),
EDIT_TIME = (DateTime)dr["EDIT_TIME"],
APPROVE_EMP = dr["APPROVE_EMP"].ToString(),
APPROVE_TIME = (DateTime)dr["APPROVE_TIME"]
});
}
//速度太慢
//Row_C_WT_CONFIG RowCWT = (Row_C_WT_CONFIG)this.NewRow();
//RowCWT.loadData(dr);
//WTList.Add(RowCWT.GetDataObject());
}
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(WTList);
}