/// <summary>
/// OBA工站創建LOT,返回LOT信息
/// </summary>
/// <param name="user"></param>
/// <param name="packNo"></param>
/// <param name="DB"></param>
public R_LOT_STATUS CreateLotByPackno(User user, string packNo, OleExec DB)
{
T_R_PACKING tRPacking = new T_R_PACKING(DB, this.DBType);
Row_R_PACKING rowRPacking = tRPacking.GetRPackingByPackNo(DB, packNo);
PalletBase palletBase = new PalletBase(rowRPacking);
T_C_SKU tCSku = new T_C_SKU(DB, this.DBType);
T_R_LOT_STATUS tRLotStatus = new T_R_LOT_STATUS(DB, this.DBType);
T_C_AQLTYPE tCAqlType = new T_C_AQLTYPE(DB, this.DBType);
T_R_LOT_PACK tRLotPack = new T_R_LOT_PACK(DB, this.DBType);
T_C_SKU_AQL tCSkuAql = new T_C_SKU_AQL(DB, this.DBType);
C_SKU_AQL cSkuAql = tCSkuAql.GetSkuAql(DB, rowRPacking.SKUNO);
List <C_AQLTYPE> cAqlTypeList = tCAqlType.GetAqlTypeBySkunoAndLevel(rowRPacking.SKUNO, cSkuAql.DEFAULLEVEL, DB);
if (cAqlTypeList.Count == 0)
{
throw new Exception(MESDataObject.MESReturnMessage.GetMESReturnMessage("MSGCODE20180625165842",
new string[] { }));
}
Row_C_SKU rCSku = tCSku.GetSku(rowRPacking.SKUNO, DB, this.DBType);
Row_R_LOT_STATUS rowRLotStatus = (Row_R_LOT_STATUS)tRLotStatus.NewRow();
rowRLotStatus.ID = tRLotStatus.GetNewID(user.BU, DB, this.DBType);
rowRLotStatus.LOT_NO = SNMaker.SNmaker.GetNextSN("OBALOT", DB);
rowRLotStatus.SKUNO = rowRPacking.SKUNO;
rowRLotStatus.AQL_TYPE = cSkuAql.AQLTYPE;
rowRLotStatus.LOT_QTY = palletBase.GetSnCount(DB);
rowRLotStatus.REJECT_QTY = cAqlTypeList.Where(t => t.LOT_QTY > rowRLotStatus.LOT_QTY).OrderBy(t => t.LOT_QTY).Take(1).ToList <C_AQLTYPE>()[0].REJECT_QTY;
rowRLotStatus.SAMPLE_QTY = cAqlTypeList.Where(t => t.LOT_QTY > rowRLotStatus.LOT_QTY).OrderBy(t => t.LOT_QTY).Take(1).ToList <C_AQLTYPE>()[0].SAMPLE_QTY;
rowRLotStatus.SAMPLE_QTY = rowRLotStatus.SAMPLE_QTY > rowRLotStatus.LOT_QTY ? rowRLotStatus.LOT_QTY : rowRLotStatus.SAMPLE_QTY;
rowRLotStatus.PASS_QTY = 0;
rowRLotStatus.FAIL_QTY = 0;
rowRLotStatus.CLOSED_FLAG = "0";
rowRLotStatus.LOT_STATUS_FLAG = "0";
rowRLotStatus.SAMPLE_STATION = "OBA";
rowRLotStatus.AQL_LEVEL = cSkuAql.DEFAULLEVEL;
rowRLotStatus.LINE = "";
rowRLotStatus.EDIT_EMP = user.EMP_NO;
rowRLotStatus.EDIT_TIME = tRPacking.GetDBDateTime(DB);
Row_R_LOT_PACK rowRLotPack = (Row_R_LOT_PACK)tRLotPack.NewRow();
rowRLotPack.ID = tRLotPack.GetNewID(user.BU, DB, this.DBType);
rowRLotPack.LOTNO = rowRLotStatus.LOT_NO;
rowRLotPack.PACKNO = packNo;
rowRLotPack.EDIT_EMP = user.EMP_NO;
rowRLotPack.EDIT_TIME = rowRLotStatus.EDIT_TIME;
DB.ThrowSqlExeception = true;
DB.ExecSQL(rowRLotStatus.GetInsertString(this.DBType));
DB.ExecSQL(rowRLotPack.GetInsertString(this.DBType));
DB.ThrowSqlExeception = false;
return(rowRLotStatus.GetDataObject());
}
public void AutoConvert()
{
T_R_WO_HEADER R_WO_HEADER;
T_R_WO_BASE R_WO_BASE;
T_C_SKU C_SKU;
T_C_ROUTE C_ROUTE;
OleExec Sfcdb;
string Rows_ID = "";
Sfcdb = this.DBPools["SFCDB"].Borrow();
R_WO_HEADER = new T_R_WO_HEADER(Sfcdb, DB_TYPE_ENUM.Oracle);
DataTable dt = R_WO_HEADER.GetConvertWoList(Sfcdb, DB_TYPE_ENUM.Oracle);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
R_WO_BASE = new T_R_WO_BASE(Sfcdb, DB_TYPE_ENUM.Oracle);
Rows_ID = R_WO_BASE.GetNewID(BU, Sfcdb);
Row_R_WO_BASE Rows = (Row_R_WO_BASE)R_WO_BASE.NewRow();
C_SKU = new T_C_SKU(Sfcdb, DB_TYPE_ENUM.Oracle);
Row_C_SKU Rows_SKU = (Row_C_SKU)C_SKU.GetSku(dr["MATNR"].ToString(), Sfcdb, DB_TYPE_ENUM.Oracle);
C_ROUTE = new T_C_ROUTE(Sfcdb, DB_TYPE_ENUM.Oracle);
Row_C_ROUTE Rows_Route = (Row_C_ROUTE)C_ROUTE.GetRouteBySkuno(dr["MATNR"].ToString(), Sfcdb, DB_TYPE_ENUM.Oracle);
if (Rows != null && Rows_SKU != null && Rows_Route != null)
{
Rows.ID = Rows_ID;
Rows.WORKORDERNO = dr["AUFNR"].ToString();
Rows.SKUNO = dr["MATNR"].ToString();
Rows.CUSTOMER_NAME = Rows_SKU.CUST_SKU_CODE;
Rows.CUST_PN = Rows_SKU.CUST_PARTNO;
Rows.WORKORDER_QTY = Convert.ToDouble(dr["GAMNG"]);
Rows.SKU_VER = dr["REVLV"].ToString();
Rows.SKU_NAME = Rows_SKU.SKU_NAME;
Rows.SKU_DESC = Rows_SKU.DESCRIPTION;
Rows.ROHS = dr["ROHS_VALUE"].ToString();
Rows.ROUTE_ID = Rows_Route.ID; //路由應該加版本//Rows.KP_LIST_ID
Rows.CLOSED_FLAG = "0";
Rows.EDIT_EMP = "LLF";
string str = Sfcdb.ExecSQL(Rows.GetInsertString(DB_TYPE_ENUM.Oracle));
}
}
}
}
public void Download(string SKUNO, string PLANT, OleExec Sfcdb)
{
ZRFC_GET_PROD_MASTER zrfc_GET_PROD_MASTER = new ZRFC_GET_PROD_MASTER();
zrfc_GET_PROD_MASTER.SetValues(SKUNO, PLANT);
zrfc_GET_PROD_MASTER.CallRFC();
DataTable skuno_table = zrfc_GET_PROD_MASTER.GetTableValue("PROD_MASTER");
T_C_SKU t_c_sku = new T_C_SKU(Sfcdb, DB_TYPE_ENUM.Oracle);
Row_C_SKU row_C_SKU = (Row_C_SKU)t_c_sku.NewRow();
if (skuno_table.Rows.Count > 0)
{
row_C_SKU.ID = t_c_sku.GetNewID(BU, Sfcdb);
row_C_SKU.BU = this.BU;
row_C_SKU.SKUNO = skuno_table.Rows[0]["MATNR"].ToString();
row_C_SKU.VERSION = skuno_table.Rows[0]["REVLV"].ToString();
row_C_SKU.SKU_NAME = skuno_table.Rows[0]["BISMT"].ToString();
row_C_SKU.C_SERIES_ID = "";
row_C_SKU.CUST_PARTNO = skuno_table.Rows[0]["BISMT"].ToString();
row_C_SKU.CUST_SKU_CODE = "";
row_C_SKU.SN_RULE = "";
row_C_SKU.PANEL_RULE = "";
row_C_SKU.DESCRIPTION = skuno_table.Rows[0]["MAKTX"].ToString();
row_C_SKU.LAST_EDIT_USER = this.LoginUser.EMP_NO;
row_C_SKU.LAST_EDIT_TIME = DateTime.Now;
row_C_SKU.SKU_TYPE = "";
row_C_SKU.AQLTYPE = "";
String skuno = row_C_SKU.SKUNO;
string sql = $@"DELETE FROM C_SKU WHERE SKUNO='{skuno}'";
Sfcdb.ExecSQL(sql);
sql = row_C_SKU.GetInsertString(DB_TYPE_ENUM.Oracle);
Sfcdb.ExecSQL(sql);
}
}
/// <summary>
/// convert wo
/// </summary>
protected void ConvertWO()
{
if (_downloadWO != "")
{
dtConvertWO = R_WO_HEADER.GetConvertWoTableByWO(SFCDB, DB_TYPE_ENUM.Oracle, _downloadWO);
}
else
{
dtConvertWO = R_WO_HEADER.GetConvertWoTable(SFCDB, DB_TYPE_ENUM.Oracle, arrayConvertWO);
}
if (dtConvertWO.Rows.Count > 0)
{
foreach (DataRow row in dtConvertWO.Rows)
{
rowSku = null;
rowRoute = null;
rowWOBase = null;
rowWOType = null;
routeDetailList = null;
keypartList = null;
keypartIDList = null;
linkStationList = null;
C_Series = null;
sql = "";
try
{
rowSku = (Row_C_SKU)C_SKU.GetSku(row["MATNR"].ToString(), SFCDB, DB_TYPE_ENUM.Oracle);
if (rowSku == null)
{
throw new Exception(" sku " + row["MATNR"].ToString() + " can't exist");
}
//if (!string.Equals(rowSku.VERSION.ToString(), row["REVLV"].ToString()))
//{
// throw new Exception(" The sku version is not the same," + rowSku.VERSION.ToString() + "," + row["REVLV"].ToString());
//}
C_Series = T_Series.GetDetailById(SFCDB, rowSku.C_SERIES_ID);
if (C_Series == null)
{
throw new Exception(" the series of " + row["MATNR"].ToString() + " can't exist");
}
rowWOType = WOType.GetWOTypeByWO(SFCDB, row["AUART"].ToString());
if (rowWOType == null)
{
throw new Exception("get wo type fail");
}
rowRoute = (Row_C_ROUTE)C_ROUTE.GetRouteBySkuno(rowSku.ID, SFCDB, DB_TYPE_ENUM.Oracle);
if (rowRoute == null)
{
throw new Exception(" the route of " + row["MATNR"].ToString() + " can't exist");
}
routeDetailList = RouteDetail.GetByRouteIdOrderBySEQASC(rowRoute.ID, SFCDB);
if (routeDetailList == null || routeDetailList.Count == 0)
{
throw new Exception("get route detail fail by " + rowRoute.ID);
}
keypartIDList = t_c_kp_list.GetListIDBySkuno(rowSku.SKUNO, SFCDB);
if (keypartIDList.Count > 0 && keypartIDList.Count != 1)
{
throw new Exception("skuno:" + row["MATNR"].ToString() + " have more keypart id");
}
//HWD的邏輯
//linkStationList = routeDetailList.Where(link => link.STATION_NAME == "LINK").ToList();
//if (linkStationList != null && linkStationList.Count > 0)
//{
// keypartList = Keypart.GetKeypartListBySkuVersion(SFCDB, row["MATNR"].ToString(), row["REVLV"].ToString());
// keypartIDList = keypartList.Select(id => id.KEYPART_ID).Distinct().ToList();
// if (keypartIDList == null || keypartIDList.Count != 1)
// {
// throw new Exception("get keypart id fail by skuno:" + row["MATNR"].ToString() + " and version:" + row["REVLV"].ToString());
// }
//}
rowWOBase = (Row_R_WO_BASE)R_WO_BASE.NewRow();
rowWOBase.ID = R_WO_BASE.GetNewID(BU, SFCDB);
rowWOBase.WORKORDERNO = row["AUFNR"].ToString();
rowWOBase.PLANT = row["WERKS"].ToString();
rowWOBase.RELEASE_DATE = InterfacePublicValues.GetDBDateTime(SFCDB, DB_TYPE_ENUM.Oracle);
rowWOBase.DOWNLOAD_DATE = InterfacePublicValues.GetDBDateTime(SFCDB, DB_TYPE_ENUM.Oracle);
rowWOBase.PRODUCTION_TYPE = "BTO";//HWD原有邏輯寫死
rowWOBase.WO_TYPE = rowWOType.WORKORDER_TYPE;
rowWOBase.SKUNO = row["MATNR"].ToString();
rowWOBase.SKU_VER = row["REVLV"].ToString();
rowWOBase.SKU_SERIES = C_Series.SERIES_NAME;
rowWOBase.SKU_NAME = rowSku.SKU_NAME;
rowWOBase.SKU_DESC = rowSku.DESCRIPTION;
rowWOBase.CUST_PN = rowSku.CUST_PARTNO;
rowWOBase.CUST_PN_VER = "";
rowWOBase.CUSTOMER_NAME = rowSku.CUST_SKU_CODE;
rowWOBase.ROUTE_ID = rowRoute.ID;
rowWOBase.START_STATION = routeDetailList[0].STATION_NAME;
rowWOBase.KP_LIST_ID = (keypartIDList != null && keypartIDList.Count > 0) ? keypartIDList[0] : "";
rowWOBase.CLOSED_FLAG = "0";
rowWOBase.WORKORDER_QTY = Convert.ToDouble(row["GAMNG"]);
rowWOBase.INPUT_QTY = 0;
rowWOBase.FINISHED_QTY = 0;
rowWOBase.SCRAPED_QTY = 0;
rowWOBase.STOCK_LOCATION = row["LGORT"].ToString();
rowWOBase.PO_NO = "";
rowWOBase.CUST_ORDER_NO = row["ABLAD"].ToString();
rowWOBase.ROHS = row["ROHS_VALUE"].ToString();
rowWOBase.EDIT_EMP = "interface";
rowWOBase.EDIT_TIME = InterfacePublicValues.GetDBDateTime(SFCDB, DB_TYPE_ENUM.Oracle);
SFCDB.ThrowSqlExeception = true;
sql = rowWOBase.GetInsertString(DB_TYPE_ENUM.Oracle);
SFCDB.ExecSQL(rowWOBase.GetInsertString(DB_TYPE_ENUM.Oracle));
SFCDB.CommitTrain();
}
catch (Exception ex)
{
WriteLog.WriteIntoMESLog(SFCDB, BU, "MESInterface", "MESInterface.HWD.DownLoadWO", "ConvertWO", ip + ";" + row["AUFNR"].ToString() + ";ConvertWO fail," + ex.Message.ToString(), sql, "interface");
}
}
}
}
/// <summary>
/// convert wo
/// </summary>
protected void ConvertWO()
{
if (_downloadWO != "")
{
//如果指定了工單Download,且工單不存在于R_WO_BASE表中,則返回該工單R_WO_Header記錄
dtConvertWO = R_WO_HEADER.GetConvertWoTableByWO(SFCDB, DB_TYPE_ENUM.Oracle, _downloadWO);
}
else
{
//否則抓取所有不存在于R_WO_BASE表中工單,并結合CONVERTWO=00251的設定返回工單R_WO_Header記錄
//MBD 正常工單類型,且ModelType不包含094的才轉
dtConvertWO = R_WO_HEADER.GetConvertWoTableForMBD(SFCDB, DB_TYPE_ENUM.Oracle, arrayConvertWO);
}
if (dtConvertWO.Rows.Count > 0)
{
//循環所有待轉工單
foreach (DataRow row in dtConvertWO.Rows)
{
rowSku = null;
rowRoute = null;
rowWOBase = null;
rowWOType = null;
routeDetailList = null;
keypartList = null;
keypartIDList = null;
linkStationList = null;
C_Series = null;
sql = "";
try
{
//判斷料號是否存在
rowSku = (Row_C_SKU)C_SKU.GetSku(row["MATNR"].ToString(), SFCDB, DB_TYPE_ENUM.Oracle);
if (rowSku == null)
{
throw new Exception(" sku " + row["MATNR"].ToString() + " not exist");
}
if (rowSku.C_SERIES_ID == null)
{
throw new Exception(" the series of " + row["MATNR"].ToString() + " can not be null ");
}
//從C_Series表中獲取系列信息
C_Series = T_Series.GetDetailById(SFCDB, rowSku.C_SERIES_ID);
if (C_Series == null)
{
throw new Exception(" the series of " + row["MATNR"].ToString() + " not exist");
}
//從R_WO_TYPE表中獲取工單類型:這張表需要事先設定
rowWOType = WOType.GetWOTypeByWO(SFCDB, row["AUART"].ToString());
if (rowWOType == null)
{
throw new Exception("get wo type fail");
}
//從R_Sku_Route&C_Route表中獲取默認路由
rowRoute = (Row_C_ROUTE)C_ROUTE.GetDefaultRouteBySkuno(rowSku.ID, SFCDB, DB_TYPE_ENUM.Oracle);
if (rowRoute == null)
{
throw new Exception(" the route of " + row["MATNR"].ToString() + " not exist");
}
//從C_Route_Detail表中獲取路由
routeDetailList = RouteDetail.GetByRouteIdOrderBySEQASC(rowRoute.ID, SFCDB);
if (routeDetailList == null || routeDetailList.Count == 0)
{
throw new Exception("get route detail fail by " + rowRoute.ID);
}
//???KeyParts如何轉
keypartIDList = t_c_kp_list.GetListIDBySkuno(rowSku.SKUNO, SFCDB);
if (keypartIDList.Count > 0 && keypartIDList.Count != 1)
{
throw new Exception("skuno:" + row["MATNR"].ToString() + " have more keypart id");
}
rowWOBase = (Row_R_WO_BASE)R_WO_BASE.NewRow();
rowWOBase.ID = R_WO_BASE.GetNewID(BU, SFCDB);
rowWOBase.WORKORDERNO = row["AUFNR"].ToString();
rowWOBase.PLANT = row["WERKS"].ToString();
rowWOBase.RELEASE_DATE = InterfacePublicValues.GetDBDateTime(SFCDB, DB_TYPE_ENUM.Oracle);
rowWOBase.DOWNLOAD_DATE = InterfacePublicValues.GetDBDateTime(SFCDB, DB_TYPE_ENUM.Oracle);
rowWOBase.PRODUCTION_TYPE = "BTO";//原有邏輯寫死
rowWOBase.WO_TYPE = rowWOType.WORKORDER_TYPE;
rowWOBase.SKUNO = row["MATNR"].ToString();
rowWOBase.SKU_VER = row["REVLV"].ToString();
rowWOBase.SKU_SERIES = C_Series.SERIES_NAME;
rowWOBase.SKU_NAME = rowSku.SKU_NAME;
rowWOBase.SKU_DESC = rowSku.DESCRIPTION;
rowWOBase.CUST_PN = rowSku.CUST_PARTNO;
rowWOBase.CUST_PN_VER = "";
rowWOBase.CUSTOMER_NAME = rowSku.CUST_SKU_CODE;
rowWOBase.ROUTE_ID = rowRoute.ID;
rowWOBase.START_STATION = routeDetailList[0].STATION_NAME;
//??????
rowWOBase.KP_LIST_ID = (keypartIDList != null && keypartIDList.Count > 0) ? keypartIDList[0] : "";
rowWOBase.CLOSED_FLAG = "0";
rowWOBase.WORKORDER_QTY = Convert.ToDouble(row["GAMNG"]);
rowWOBase.INPUT_QTY = 0;
rowWOBase.FINISHED_QTY = 0;
rowWOBase.SCRAPED_QTY = 0;
rowWOBase.STOCK_LOCATION = row["LGORT"].ToString();
rowWOBase.PO_NO = "";
rowWOBase.CUST_ORDER_NO = row["ABLAD"].ToString();
rowWOBase.ROHS = row["ROHS_VALUE"].ToString();
rowWOBase.EDIT_EMP = "interface";
rowWOBase.EDIT_TIME = InterfacePublicValues.GetDBDateTime(SFCDB, DB_TYPE_ENUM.Oracle);
SFCDB.ThrowSqlExeception = true;
sql = rowWOBase.GetInsertString(DB_TYPE_ENUM.Oracle);
SFCDB.ExecSQL(rowWOBase.GetInsertString(DB_TYPE_ENUM.Oracle));
SFCDB.CommitTrain();
}
catch (Exception ex)
{
WriteLog.WriteIntoMESLog(SFCDB, BU, "MESInterface", "MESInterface.MBD.DownLoadWO", "ConvertWO", ip + ";" + row["AUFNR"].ToString() + ";ConvertWO fail," + ex.Message.ToString(), sql, "interface");
}
}
}
}