/// <summary>
/// 更新完工數量以及判斷是否應該關結工單
/// </summary>
/// <param name="wo"></param>
/// <param name="count"></param>
/// <param name="DB"></param>
/// <returns></returns>
public int UpdateFinishQty(string wo, double count, OleExec DB)
{
int result = 0;
string sql = string.Empty;
DataTable dt = new DataTable();
if (this.DBType.Equals(DB_TYPE_ENUM.Oracle))
{
sql = $@"SELECT * FROM R_WO_BASE WHERE WORKORDERNO='{wo}' AND WORKORDER_QTY-FINISHED_QTY>={count}";
dt = DB.ExecSelect(sql, null).Tables[0];
if (dt.Rows.Count > 0)
{
sql = $@"UPDATE R_WO_BASE SET FINISHED_QTY=CASE WHEN (FINISHED_QTY IS NULL) THEN {count} ELSE FINISHED_QTY+{count} END,
EDIT_TIME='{GetDBDateTime(DB)}' WHERE WORKORDERNO='{wo}'";
result = DB.ExecSqlNoReturn(sql, null);
sql = $@"UPDATE R_WO_BASE SET CLOSED_FLAG='1',CLOSE_DATE='{GetDBDateTime(DB)}' WHERE WORKORDERNO='{wo}'
AND FINISHED_QTY>=WORKORDER_QTY";
result = DB.ExecSqlNoReturn(sql, null);
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
public int UpdateWoQty(string WO, int CutQty, string Emp_NO, OleExec sfcdb)
{
string strSql = $@"update r_wo_base set workorder_qty=workorder_qty-{CutQty},edit_emp='{Emp_NO}',edit_time=sysdate where workorderno='{WO}'";
int result = sfcdb.ExecSqlNoReturn(strSql, null);
return(result);
}
public int DeleteById(string Id, OleExec DB)
{
string strSql = $@"delete c_route_detail_directlink where id=:Id";
OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":Id", Id) };
int res = DB.ExecSqlNoReturn(strSql, paramet);
return(res);
}
public int ReplaceRPanelSn(string NewSn, string OldSn, OleExec DB, DB_TYPE_ENUM DBType)
{
int result = 0;
string strSql = string.Empty;
if (this.DBType == DB_TYPE_ENUM.Oracle)
{
strSql = $@"UPDATE r_panel_sn R SET R.SN='{NewSn}' WHERE R.SN='{OldSn}'";
result = DB.ExecSqlNoReturn(strSql, null);
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
public int ReplaceSnKP(string newSn, string oldSn, OleExec sfcdb)
{
int result = 0;
string sql = string.Empty;
if (this.DBType == DB_TYPE_ENUM.Oracle)
{
sql = $@"UPDATE R_SN_KEYPART_DETAIL R SET R.SN='{newSn}' WHERE R.SN='{oldSn}'";
result = sfcdb.ExecSqlNoReturn(sql, null);
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
/// <summary>
/// 添加或者更新一個 C_SKU_DETAIL 記錄
/// 需要傳遞一個完完整整的 C_SKU_DETAIL 對象，包括 ID
/// </summary>
/// <param name="SkuDetail"></param>
/// <param name="Bu"></param>
/// <param name="DB"></param>
/// <returns></returns>
public int AddOrUpdateSkuDetail(string Operation, C_SKU_DETAIL SkuDetail, OleExec DB)
{
Row_C_SKU_DETAIL SkuDetailRow = (Row_C_SKU_DETAIL)NewRow();
string sql = string.Empty;
int result = 0;
if (this.DBType.Equals(DB_TYPE_ENUM.Oracle))
{
if (SkuDetail != null && SkuDetail.ID != null)
{
switch (Operation.Trim().ToUpper())
{
case "ADD":
SkuDetailRow = (Row_C_SKU_DETAIL)ConstructRow(SkuDetail);
sql = SkuDetailRow.GetInsertString(this.DBType);
break;
case "UPDATE":
SkuDetailRow = (Row_C_SKU_DETAIL)GetObjByID(SkuDetail.ID, DB);
SkuDetailRow.SKUNO = SkuDetailRow.SKUNO;
SkuDetailRow.CATEGORY = SkuDetail.CATEGORY;
SkuDetailRow.CATEGORY_NAME = SkuDetail.CATEGORY_NAME;
SkuDetailRow.VALUE = SkuDetail.VALUE;
SkuDetailRow.EXTEND = SkuDetail.EXTEND;
SkuDetailRow.VERSION = SkuDetail.VERSION;
SkuDetailRow.BASETEMPLATE = SkuDetail.BASETEMPLATE;
SkuDetailRow.EDIT_EMP = SkuDetail.EDIT_EMP;
SkuDetailRow.EDIT_TIME = SkuDetail.EDIT_TIME;
sql = SkuDetailRow.GetUpdateString(this.DBType);
break;
}
result = DB.ExecSqlNoReturn(sql, null);
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
public int SetPanelInValid(string PanelNo, OleExec DB, DB_TYPE_ENUM DBType)
{
string strSql = string.Empty;
int result = 0;
string errMsg = string.Empty;
if (DBType.Equals(DB_TYPE_ENUM.Oracle))
{
strSql = $@"UPDATE R_SN SET VALID_FLAG='0',CURRENT_STATION='UNDO_LOADING' WHERE ID IN
(SELECT SN FROM R_PANEL_SN WHERE PANEL='{PanelNo}')";
result = DB.ExecSqlNoReturn(strSql, null);
}
else
{
errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
public int AddReplaceSNRecord(R_REPLACE_SN ReplaceSn, string Bu, OleExec DB, DB_TYPE_ENUM DBType)
{
int result = 0;
string strSql = string.Empty;
Row_R_REPLACE_SN row = null;
if (ReplaceSn != null)
{
row = (Row_R_REPLACE_SN)ConstructRow(ReplaceSn);
if (row.ID == null)
{
row.ID = GetNewID(Bu, DB);
}
strSql = row.GetInsertString(DBType);
result = DB.ExecSqlNoReturn(strSql, null);
}
return(result);
}
/// <summary>
/// 刪除一個 C_SKU_DETAIL 記錄
/// </summary>
/// <param name="SkuDetailId"></param>
/// <param name="DB"></param>
/// <returns></returns>
public int DeleteSkuDetail(string SkuDetailId, OleExec DB)
{
int result = 0;
string sql = string.Empty;
if (this.DBType.Equals(DB_TYPE_ENUM.Oracle))
{
if (SkuDetailId.Length > 0)
{
sql = $@"DELETE FROM C_SKU_DETAIL WHERE ID='{SkuDetailId}'";
result = DB.ExecSqlNoReturn(sql, null);
}
}
else
{
string errMsg = MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { DBType.ToString() });
throw new MESReturnMessage(errMsg);
}
return(result);
}
public void SubmitWoInfo(JObject requestValue, JObject Data, MESStationReturn StationReturn)
{
//wo
string wo = Data["wo"].ToString();
if (string.IsNullOrEmpty(wo))
{
//StationReturn.Status = StationReturnStatusValue.Fail;
//StationReturn.MessageCode = "MES00000006";
//StationReturn.MessagePara = new List<object>() { "Skuno" };
//StationReturn.Data = "";
//return;
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000006", new string[] { "WO" }));
}
OleExec sfcdb = DBPools["SFCDB"].Borrow();
T_R_WO_BASE t_wo = new T_R_WO_BASE(sfcdb, DB_TYPE_ENUM.Oracle);
if (!t_wo.CheckDataExist(wo, sfcdb))
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000008", new string[] { wo }));
}
R_WO_HEADER wo_header = new T_R_WO_HEADER(sfcdb, DB_TYPE_ENUM.Oracle).GetDetailByWo(sfcdb, wo);
//sku info
string skuno = Data["skuno"].ToString();//wo_header.MATNR
if (string.IsNullOrEmpty(skuno))
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000006", new string[] { "SKUNO" }));
}
string skuver = Data["sku_ver"].ToString();//wo_header.REVLV
C_SKU c_sku = new T_C_SKU(sfcdb, DB_TYPE_ENUM.Oracle).GetSku(skuno, sfcdb, DB_TYPE_ENUM.Oracle).GetDataObject();
if (c_sku == null)
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000007", new string[] { skuno }));
}
//route exchange from name
string route_name = Data["route_name"].ToString();
if (string.IsNullOrEmpty(route_name))
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000006", new string[] { "ROUTE" }));
}
C_ROUTE c_route = new T_C_ROUTE(sfcdb, DB_TYPE_ENUM.Oracle).GetByRouteName(route_name, sfcdb);
if (c_route == null)
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000007", new string[] { route_name }));
}
//station route check
string station_name = Data["station"].ToString();
if (string.IsNullOrEmpty(station_name))
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000006", new string[] { "STATION" }));
}
List <C_ROUTE_DETAIL> c_route_detail = new T_C_ROUTE_DETAIL(sfcdb, DB_TYPE_ENUM.Oracle).GetByRouteIdOrderBySEQASC(c_route.ID, sfcdb);
if (c_route_detail != null && c_route_detail.Count > 0)
{
C_ROUTE_DETAIL check = c_route_detail.Find(t => t.STATION_NAME == station_name);
if (check == null)
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000007", new string[] { station_name }));
}
}
else
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000007", new string[] { route_name }));
}
//data record
Row_R_WO_BASE row_wobase = (Row_R_WO_BASE)t_wo.NewRow();
row_wobase.ID = t_wo.GetNewID(this.BU, sfcdb);
row_wobase.WORKORDERNO = wo;
row_wobase.PLANT = Data["factory"].ToString();
row_wobase.RELEASE_DATE = DateTime.Now;
row_wobase.DOWNLOAD_DATE = Convert.ToDateTime(Data["date"].ToString());
row_wobase.PRODUCTION_TYPE = "BTO";
row_wobase.WO_TYPE = Data["wo_type"].ToString();
row_wobase.SKUNO = skuno;
row_wobase.SKU_VER = skuver;
row_wobase.SKU_NAME = c_sku.SKU_NAME;
//row_wobase.SKU_SERIES = null;
//row_wobase.SKU_DESC = null;
row_wobase.CUST_PN = c_sku.CUST_PARTNO;
row_wobase.ROUTE_ID = c_route.ID;
row_wobase.START_STATION = station_name;
row_wobase.KP_LIST_ID = Data["kp_list_id"].ToString();
row_wobase.CLOSED_FLAG = "0";
row_wobase.WORKORDER_QTY = Convert.ToDouble(Data["qty"].ToString());
row_wobase.STOCK_LOCATION = wo_header.LGORT;
row_wobase.CUST_ORDER_NO = wo_header.ABLAD;
row_wobase.EDIT_EMP = this.LoginUser.EMP_NO;
row_wobase.EDIT_TIME = DateTime.Now;
string sql = row_wobase.GetInsertString(DB_TYPE_ENUM.Oracle);
try
{
int res = sfcdb.ExecSqlNoReturn(sql, null);
if (res == 0)
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000021", new string[] { wo }));
}
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
}
catch (Exception ex)
{
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
throw ex;
}
}
public void AddReasonCode(JObject requestValue, JToken Data, MESStationReturn StationReturn)
{
string reason_code = Data["REASON_CODE"].ToString();
if (string.IsNullOrEmpty(reason_code))
{
StationReturn.MessageCode = "MES00000006";
StationReturn.Status = StationReturnStatusValue.Fail;
return;
}
string en_desc = Data["ENGLISH_DESC"].ToString();
string cn_desc = Data["CHINESE_DESC"].ToString();
OleExec sfcdb = DBPools["SFCDB"].Borrow();
int res = 0;
T_C_REASON_CODE t_reasonCode = null;
Row_C_REASON_CODE r_ReasonCode = null;
try
{
t_reasonCode = new T_C_REASON_CODE(sfcdb, DB_TYPE_ENUM.Oracle);
r_ReasonCode = (Row_C_REASON_CODE)t_reasonCode.NewRow();
r_ReasonCode.ID = t_reasonCode.GetNewID(this.BU, sfcdb);
r_ReasonCode.REASON_CODE = reason_code;
r_ReasonCode.CHINESE_DESCRIPTION = cn_desc;
r_ReasonCode.ENGLISH_DESCRIPTION = en_desc;
r_ReasonCode.EDIT_EMP = this.LoginUser.EMP_NAME;
r_ReasonCode.EDIT_TIME = this.GetDBDateTime();
string insertSQL = r_ReasonCode.GetInsertString(DB_TYPE_ENUM.Oracle);
res = sfcdb.ExecSqlNoReturn(insertSQL, null);
if (res > 0)
{
StationReturn.MessageCode = "MES00000001";
StationReturn.Status = StationReturnStatusValue.Pass;
StationReturn.Data = "";
}
else
{
StationReturn.MessageCode = "MES00000021";
StationReturn.MessagePara = new List <object>()
{
"Reason Code"
};
StationReturn.Status = StationReturnStatusValue.Fail;
StationReturn.Data = "";
}
if (sfcdb != null)
{
this.DBPools["SFCDB"].Return(sfcdb);
}
}
catch (Exception ex)
{
if (sfcdb != null)
{
this.DBPools["SFCDB"].Return(sfcdb);
}
throw ex;
}
}