public string[] CheckIsMatSPExists(string strProducts, string strMaterials)
{
string[] test = new string[3];
try
{
ProcParam param = new ProcParam(5)
{
ProcedureName = "MASTER_PACK.CHECK_IS_MAT_SP_EXISTS"
};
param.AddParamInput(0, "strPROD_COLLECTION", strProducts);
param.AddParamInput(1, "strMAT_COLLECTION", strMaterials);
param.AddParamOutput(2, "strPROD_OUT", OracleDbType.Varchar2, 255);
param.AddParamOutput(3, "strMAT_OUT", OracleDbType.Varchar2, 255);
param.AddParamOutput(4, "strOUT_PROD_EXITST_IN_SP", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
test[0] = param.ReturnValue(2).GetHashCode() == 0 ? "" : ((OracleString)param.ReturnValue(2)).Value;
test[1] = param.ReturnValue(3).GetHashCode() == 0 ? "" : ((OracleString)param.ReturnValue(3)).Value;
test[2] = param.ReturnValue(4).GetHashCode() == 0 ? "" : ((OracleString)param.ReturnValue(4)).Value;
}
catch (Exception ex)
{
throw ex;
}
return(test);
}
public string InsertMaterial(ref Material material, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(16)
{
ProcedureName = "MASTER_PACK.M_MATERIAL_INS"
};
param.AddParamInOutput(0, "strMTL_SEQ_NO", OracleDbType.Varchar2, 255, material.MTL_SEQ_NO); //OracleDbType.Varchar2, prod.PROD_SEQ_NO
param.AddParamInput(1, "strMTL_CODE", material.MTL_CODE);
param.AddParamInput(2, "strMTL_NAME", material.MTL_NAME);
param.AddParamInput(3, "strMTL_GRADE", material.MTL_GRADE);
param.AddParamInput(4, "strMTL_COLOR", material.MTL_COLOR);
param.AddParamInput(5, "strUNIT", material.UNIT);
param.AddParamInput(6, "strSTD_QTY", material.STD_QTY);
param.AddParamInput(7, "strMIN_QTY", material.MIN_QTY);
param.AddParamInput(8, "strMAX_QTY", material.MAX_QTY);
param.AddParamInput(9, "strPARTY_ID", material.PARTY_ID);
if (material.MTL_IMAGE != null)
{
param.AddParamBLOBInput(10, "strMTL_IMAGE", OracleDbType.Blob, this.BitmapToByteArray(material.MTL_IMAGE)); //this.BitmapToByteArray(prod.PROD_IMAGE)
}
else
{
param.AddParamInput(10, "strMTL_IMAGE", DBNull.Value); //this.BitmapToByteArray(prod.PROD_IMAGE)
}
param.AddParamInput(11, "strLOCATION_ID", material.LOCATION_ID);
param.AddParamInput(12, "strREMARK", material.REMARK);
param.AddParamInput(13, "strUSER_ID", userid);
param.AddParamInput(14, "strREC_STAT", (material.REC_STAT ? "Y" : "N"));
param.AddParamOutput(15, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString mtlSeq = (OracleString)param.ReturnValue(0);
OracleString result = (OracleString)param.ReturnValue(15);
if (!result.IsNull)
{
material.MTL_SEQ_NO = mtlSeq.Value;
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public string InsertParty(ref Party party, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(15);
param.ProcedureName = "MASTER_PACK.M_PARTY_INS";
param.AddParamInOutput(0, "strPARTY_ID", OracleDbType.Varchar2, 100, party.PARTY_ID);
param.AddParamInput(1, "strPARTY_NAME", party.PARTY_NAME);
param.AddParamInput(2, "strPARTY_TYPE", party.PARTY_TYPE);
param.AddParamInput(3, "strADD1", party.ADD1);
param.AddParamInput(4, "strADD2", party.ADD2);
param.AddParamInput(5, "strADD3", party.ADD3);
param.AddParamInput(6, "strADD4", party.ADD4);
param.AddParamInput(7, "strTEL", party.TEL);
param.AddParamInput(8, "strFAX", party.FAX);
param.AddParamInput(9, "strEMAIL", party.EMAIL);
param.AddParamInput(10, "strPIC", party.PIC);
param.AddParamInput(11, "strREMARK", party.REMARK);
param.AddParamInput(12, "strREC_STAT", (party.REC_STAT ? "Y" : "N"));
param.AddParamInput(13, "strUSER_ID", userid);
param.AddParamOutput(14, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(14);
if (!result.IsNull)
{
resultMsg = result.Value;
OracleString resultPartyID = (OracleString)param.ReturnValue(0);
if (!resultPartyID.IsNull)
{
party.PARTY_ID = resultPartyID.Value;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public string InsertTransferOrder(ref TransferOrderHdr TO_HDR, string userid)
{
string resultMsg = string.Empty;
try
{
#region "Transaction Header"
ProcParam paramHDR = new ProcParam(10);
paramHDR.ProcedureName = "TO_PACK.TO_HDR_INS";
paramHDR.AddParamInOutput(0, "strTO_NO", OracleDbType.Varchar2, 255, TO_HDR.TO_NO);
paramHDR.AddParamInput(1, "strTO_DATE ", TO_HDR.TO_DATE);
paramHDR.AddParamInput(2, "strREF_NO", TO_HDR.REF_NO);
paramHDR.AddParamInput(3, "strPROD_TYPE", TO_HDR.PROD_TYPE);
paramHDR.AddParamInput(4, "strTO_DEST", TO_HDR.TO_DEST);
paramHDR.AddParamInput(5, "strDELIVERY_DATE", TO_HDR.DELIVERY_DATE);
paramHDR.AddParamInput(6, "strREMARK", TO_HDR.REMARK);
paramHDR.AddParamInput(7, "strREC_STAT", (TO_HDR.REC_STAT ? "Y" : "N"));
paramHDR.AddParamInput(8, "strUSER_ID", userid);
paramHDR.AddParamOutput(9, "RESULTMSG", OracleDbType.Varchar2, 255);
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(paramHDR);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)paramHDR.ReturnValue(0);
OracleString result = (OracleString)paramHDR.ReturnValue(9);
if (!result.IsNull)
{
TO_HDR.TO_NO = resultDB.Value;
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public string DeleteDeliveryOrder(string doNo, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam paramHDR = new ProcParam(3)
{
ProcedureName = "DO_PACK.DO_HDR_DEL"
};
paramHDR.AddParamInput(0, "strDO_NO ", doNo);
paramHDR.AddParamInput(1, "strUSER_ID", userid);
paramHDR.AddParamOutput(2, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(paramHDR);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)paramHDR.ReturnValue(2);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public Version GetLastestVersion(string curVersion)
{
Version resultVersion = Version.Parse(curVersion);
try
{
ProcParam procPara = new ProcParam(2)
{
ProcedureName = "ADMINISTRATOR_PACK.GET_APP_VERSION"
};
procPara.AddParamReturn(0, "ReturnValue", OracleDbType.Varchar2, 10);
procPara.AddParamInput(1, "strCURV", resultVersion.ToString());
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.DataAc.LastException != null)
{
throw GlobalDB.Instance.DataAc.LastException;
}
var resultDB = (OracleString)procPara.ReturnValue(0);
if (!resultDB.IsNull)
{
resultVersion = Version.Parse(resultDB.ToString());
}
}
catch (Exception ex)
{
resultVersion = Version.Parse(curVersion);
}
return(resultVersion);
}
public ProductCard GetProductCardInfo(string serialNo, string mode, string userid, out string resultMessage)
{
resultMessage = string.Empty;
ProductCard pcCard = null;
try
{
ProcParam procPara = new ProcParam(5)
{
ProcedureName = "SCANNER_PACK_VER.GET_PC_INFO_PROCESS"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamInput(2, "strPROCESS", mode);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pcCard = new ProductCard();
pcCard.SERIAL_NO = OraDataReader.Instance.GetString("SERIAL_NO");
pcCard.PROD_SEQ_NO = OraDataReader.Instance.GetString("PROD_SEQ_NO");
pcCard.PRODUCT_NO = OraDataReader.Instance.GetString("PRODUCT_NO");
pcCard.PRODUCT_NAME = OraDataReader.Instance.GetString("PRODUCT_NAME");
pcCard.MTL_TYPE = OraDataReader.Instance.GetString("MTL_TYPE");
pcCard.JOB_NO = OraDataReader.Instance.GetString("JOB_NO");
pcCard.SHIFT = OraDataReader.Instance.GetString("SHIFT");
pcCard.LINE_NO = OraDataReader.Instance.GetInteger("LINE_NO");
pcCard.QTY = OraDataReader.Instance.GetInteger("QTY");
pcCard.BOX_SCANNED = OraDataReader.Instance.GetInteger("NO_OF_BOX");
pcCard.BOX_QTY = OraDataReader.Instance.GetInteger("TOT_BOX");
pcCard.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
}
}
else
{
OracleString resultDB = (OracleString)procPara.ReturnValue(3);
if (!resultDB.IsNull)
{
resultMessage = resultDB.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pcCard);
}
public MaterialCard ScanMat_Stock(string serialno, out string resultMsg)
{
resultMsg = string.Empty;
MaterialCard mtl = null;
try
{
ProcParam procPara = new ProcParam(3)
{
ProcedureName = "SCANNER_MAT_PACK.SCAN_MAT_STOCK"
};
procPara.AddParamRefCursor(0, "IO_CURSOR");
procPara.AddParamInput(1, "strSERIAL_NO", serialno);
procPara.AddParamOutput(2, "RESULTMSG", OracleDbType.Varchar2, 30);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
if (OraDataReader.Instance.OraReader.HasRows)
{
OraDataReader.Instance.OraReader.FetchSize = OraDataReader.Instance.OraReader.RowSize;
while (OraDataReader.Instance.OraReader.Read())
{
mtl = new MaterialCard();
mtl.PARTY_ID = OraDataReader.Instance.GetString("PARTY_ID");
mtl.PARTY_NAME = OraDataReader.Instance.GetString("PARTY_NAME");
mtl.MTL_CODE = OraDataReader.Instance.GetString("MTL_CODE");
mtl.MTL_NAME = OraDataReader.Instance.GetString("MTL_NAME");
mtl.MTL_GRADE = OraDataReader.Instance.GetString("MTL_GRADE");
mtl.MTL_COLOR = OraDataReader.Instance.GetString("MTL_COLOR");
mtl.QTY = OraDataReader.Instance.GetDecimal("QTY");
mtl.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
mtl.MIN_QTY = OraDataReader.Instance.GetDecimal("MIN_QTY");
mtl.MAX_QTY = OraDataReader.Instance.GetDecimal("MAX_QTY");
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
OracleString resultDB = (OracleString)procPara.ReturnValue(2);
if (!resultDB.IsNull)
{
resultMsg = resultDB.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(mtl);
}
public bool Press_CheckExistMachine(string mcNo)
{
bool isValid = false;
try
{
ProcParam procPara = new ProcParam(2)
{
ProcedureName = "SCANNER_PACK_PRESS.CHK_EXISTS_MC"
};
procPara.AddParamReturn(0, "ReturnValue", OracleDbType.Int32, 120);
procPara.AddParamInput(1, "strNo", mcNo);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleDecimal objResult = (OracleDecimal)procPara.ReturnValue(0);
if (!objResult.IsNull)
{
isValid = (objResult.ToInt32() > 0);
}
}
catch (Exception ex)
{
throw ex;
}
return(isValid);
}
public int GeneratePrintSEQ()
{
int printSeq = 0;
try
{
ProcParam procSEQ = new ProcParam(1);
procSEQ.ProcedureName = "GEN_NO.PRINT_SEQ";
procSEQ.AddParamReturn(0, "ReturnValue", OracleDbType.Int32, 100);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procSEQ);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleDecimal result = (OracleDecimal)procSEQ.ReturnValue(0);
if (!result.IsNull)
{
printSeq = result.ToInt32();
}
}
catch (Exception ex)
{
printSeq = 0;
}
return(printSeq);
}
private string GetBomRevisionSeq(string fileid, string userid)
{
string result = string.Empty;
try
{
ProcParam param = new ProcParam(3)
{
ProcedureName = "INTERFACE_PACK.UP_BOM_REVISION"
};
param.AddParamReturn(0, "ReturnValue", Oracle.DataAccess.Client.OracleDbType.Varchar2, 255);
param.AddParamInput(1, "strFILE_ID", fileid);
param.AddParamInput(2, "strUser_id", userid);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)param.ReturnValue(0);
if (!resultDB.IsNull)
{
result = resultDB.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public string GetRevisionLastUpdate()
{
string result = string.Empty;
try
{
ProcParam param = new ProcParam(1)
{
ProcedureName = "INTERFACE_PACK.RET_LAST_REV"
};
param.AddParamReturn(0, "ReturnValue", Oracle.DataAccess.Client.OracleDbType.Date, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
object oResult = param.ReturnValue(0);
if (!oResult.Equals(DBNull.Value))
{
OracleDate resultDB = (OracleDate)oResult;
if (!resultDB.IsNull)
{
result = resultDB.Value.ToString("dd-MM-yyyy HH:mm:ss");
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public ProductCard_Status GetProductCardStatus(string serialNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
ProductCard_Status pcCard = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_VER.GET_PC_STATUS"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pcCard = new ProductCard_Status();
pcCard.SERIAL_NO = OraDataReader.Instance.GetString("SERIAL_NO");
pcCard.MC_NO = OraDataReader.Instance.GetString("MC_NO");
pcCard.PRODUCT_NO = OraDataReader.Instance.GetString("PRODUCT_NO");
pcCard.PRODUCT_NAME = OraDataReader.Instance.GetString("PRODUCT_NAME");
pcCard.STATUS = OraDataReader.Instance.GetString("STATUS");
if (!OraDataReader.Instance.IsDBNull("PROCESS_DATE"))
{
pcCard.PROCESS_DATE = OraDataReader.Instance.GetDateTime("PROCESS_DATE");
}
pcCard.QTY = OraDataReader.Instance.GetInteger("QTY");
pcCard.NG_QTY = OraDataReader.Instance.GetInteger("NG_QTY");
pcCard.REP_QTY = OraDataReader.Instance.GetInteger("REP_QTY");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
pcCard = null;
throw ex;
}
return(pcCard);
}
public string DeleteMTLType(string mtlTypeNo, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(3);
param.ProcedureName = "MASTER_PACK.M_MTL_TYPE_DEL";
param.AddParamInput(0, "strSEQ_NO", mtlTypeNo);
param.AddParamInput(1, "strUSER_ID", userid);
param.AddParamOutput(2, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(2);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public bool CheckExistLocation(string location)
{
bool isValid = false;
try
{
ProcParam procPara = new ProcParam(2)
{
ProcedureName = "SCANNER_MAT_PACK.CHK_EXISTS_LOCATION"
};
procPara.AddParamReturn(0, "ReturnValue", OracleDbType.Int32, 120);
procPara.AddParamInput(1, "strLOCATION", location);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleDecimal objResult = (OracleDecimal)procPara.ReturnValue(0);
if (!objResult.IsNull)
{
isValid = (objResult.ToInt32() > 0);
}
}
catch (Exception ex)
{
throw ex;
}
return(isValid);
}
public bool IsAuthenticationPass(string login, string password)
{
bool isValid = false;
try
{
ProcParam procPara = new ProcParam(3)
{
ProcedureName = "ADM_PKG.CHECK_AUTHENTICATION_USER"
};
procPara.AddParamReturn(0, "ReturnValue", Oracle.DataAccess.Client.OracleDbType.Varchar2, 30);
procPara.AddParamInput(1, "strLogin", login);
procPara.AddParamInput(2, "strPwd", password);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.DataAc.LastException != null)
{
throw GlobalDB.Instance.DataAc.LastException;
}
OracleString result = (OracleString)procPara.ReturnValue(0);
if (!result.IsNull)
{
isValid = !string.IsNullOrEmpty(result.Value);
}
}
catch (Exception ex)
{
throw ex;
}
return(isValid);
}
public ResponseResult StartReplenish(string jobno, string mcno, int noOfBag, string userid)
{
response = new ResponseResult();
try
{
ProcParam procPara = new ProcParam(6)
{
ProcedureName = "SCANNER_MAT_CHK_PACK.START_REPLENISH"
};
procPara.AddParamInput(0, "strJOB_NO", jobno);
procPara.AddParamInput(1, "strMC_NO", mcno);
procPara.AddParamInput(2, "strNO_OF_BAG", noOfBag);
procPara.AddParamInput(3, "strUSER_ID", userid);
procPara.AddParamOutput(4, "strREP_NO", OracleDbType.Varchar2, 30);
procPara.AddParamOutput(5, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.DataAc.LastException != null)
{
throw GlobalDB.Instance.DataAc.LastException;
}
var resultMsg = (OracleString)procPara.ReturnValue(5);
response.Message = resultMsg.ToString();
if (response.Message == "OK")
{
response.Data = ((OracleString)procPara.ReturnValue(4)).ToString();
}
else
{
response.Data = string.Empty;
}
}
catch (Exception ex)
{
response.Status = false;
response.Message = ex.Message;
response.Data = string.Empty;
}
return(response);
}
public string InsertLoadingOrder(ref LoadingOrder loadingOrd, string userid)
{
string resultMsg = string.Empty;
try
{
#region "Transaction Header"
ProcParam paramHDR = new ProcParam(10)
{
ProcedureName = "LOADING_PACK.LOADING_HDR_INS"
};
paramHDR.AddParamInOutput(0, "strLOADING_NO", OracleDbType.Varchar2, 255, loadingOrd.LOADING_NO);
paramHDR.AddParamInput(1, "strLOADING_DATE ", loadingOrd.LOADING_DATE);
paramHDR.AddParamInput(2, "strDELIVERY_DATE", loadingOrd.DELIVERY_DATE);
paramHDR.AddParamInput(3, "strTRUCK_NO", loadingOrd.TRUCK_NO);
paramHDR.AddParamInput(4, "strCONTAINER_NO", loadingOrd.CONTAINER_NO);
paramHDR.AddParamInput(5, "strWH_ID", loadingOrd.WH_ID);
paramHDR.AddParamInput(6, "strREMARK", loadingOrd.REMARK);
paramHDR.AddParamInput(7, "strREC_STAT", (loadingOrd.REC_STAT ? "Y" : "N"));
paramHDR.AddParamInput(8, "strUSER_ID", userid);
paramHDR.AddParamOutput(9, "RESULTMSG", OracleDbType.Varchar2, 255);
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(paramHDR);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)paramHDR.ReturnValue(0);
OracleString result = (OracleString)paramHDR.ReturnValue(9);
if (!result.IsNull)
{
loadingOrd.LOADING_NO = resultDB.Value;
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public string UpdateProduct(Product prod, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(17)
{
ProcedureName = "MASTER_PACK.M_PRODUCT_UPD"
};
param.AddParamInput(0, "strPROD_SEQ_NO", prod.PROD_SEQ_NO);
param.AddParamInput(1, "strPRODUCT_NO", prod.PRODUCT_NO);
param.AddParamInput(2, "strPRODUCT_NAME", prod.PRODUCT_NAME);
param.AddParamInput(3, "strMATERIAL_TYPE", prod.MATERIAL_TYPE);
param.AddParamInput(4, "strPRODUCTION_TYPE", prod.PRODUCTION_TYPE);
param.AddParamInput(5, "strMC_NO", prod.MC_NO);
param.AddParamInput(6, "strBOX_QTY", prod.BOX_QTY);
param.AddParamInput(7, "strUNIT", prod.UNIT);
if (prod.PROD_IMAGE != null)
{
param.AddParamBLOBInput(8, "strPROD_IMAGE", OracleDbType.Blob, this.BitmapToByteArray(prod.PROD_IMAGE)); //this.BitmapToByteArray(prod.PROD_IMAGE)
}
else
{
param.AddParamInput(8, "strPROD_IMAGE", DBNull.Value); //this.BitmapToByteArray(prod.PROD_IMAGE)
}
//param.AddParamBLOBInput(8, "strPROD_IMAGE", OracleDbType.Blob, this.BitmapToByteArray(prod.PROD_IMAGE));
param.AddParamInput(9, "strREMARK", prod.REMARK);
param.AddParamInput(10, "strREC_STAT", (prod.REC_STAT ? "Y" : "N"));
param.AddParamInput(11, "strUSER_ID", userid);
//add new parameter on 07-Jun-2011
param.AddParamInput(12, "strCUST_PROD_NO", prod.CUST_PROD_NO);
param.AddParamInput(13, "strBUYER", prod.BUYER_PRICE);
param.AddParamInput(14, "strSELLING", prod.SELLING_PRICE);
param.AddParamInput(15, "strCOST", prod.COST_PRICE);
param.AddParamOutput(16, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(16);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public ResponseResult StartMixing(decimal percen, int noOfBag, string userid)
{
response = new ResponseResult();
try
{
ProcParam procPara = new ProcParam(5)
{
ProcedureName = "SCANNER_MAT_CHK_PACK.START_MIXING"
};
procPara.AddParamInput(0, "strSTD_PERCEN", percen);
procPara.AddParamInput(1, "strNO_OF_BAG", noOfBag);
procPara.AddParamInput(2, "strUSER_ID", userid);
procPara.AddParamOutput(3, "strMIXED_NO", OracleDbType.Varchar2, 30);
procPara.AddParamOutput(4, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.DataAc.LastException != null)
{
throw GlobalDB.Instance.DataAc.LastException;
}
var resultMsg = (OracleString)procPara.ReturnValue(4);
response.Message = resultMsg.ToString();
if (response.Message == "OK")
{
response.Data = ((OracleString)procPara.ReturnValue(3)).ToString();
}
else
{
response.Data = string.Empty;
}
}
catch (Exception ex)
{
response.Status = false;
response.Message = ex.Message;
response.Data = string.Empty;
}
return(response);
}
public ProductCard Horizontal_GetUpdatePC_QCInfo(string serialNo, string qcStatus, string userid, out string resultMessage)
{
resultMessage = string.Empty;
ProductCard pcCard = null;
try
{
ProcParam procPara = new ProcParam(5)
{
ProcedureName = "SCANNER_PACK_HOZ.GET_UPD_PC_QC"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamInput(2, "strQC_STATUS", qcStatus);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pcCard = new ProductCard();
pcCard.JOB_NO = OraDataReader.Instance.GetString("JOB_NO");
pcCard.LINE_NO = OraDataReader.Instance.GetInteger("LINE_NO");
pcCard.JOB_LOT = OraDataReader.Instance.GetString("JOB_LOT");
pcCard.PROD_SEQ_NO = OraDataReader.Instance.GetString("PROD_SEQ_NO");
pcCard.PRODUCT_NO = OraDataReader.Instance.GetString("PRODUCT_NO");
pcCard.PRODUCT_NAME = OraDataReader.Instance.GetString("PRODUCT_NAME");
pcCard.MTL_TYPE = OraDataReader.Instance.GetString("MTL_TYPE");
pcCard.SERIAL_NO = OraDataReader.Instance.GetString("SERIAL_NO");
pcCard.QTY = OraDataReader.Instance.GetInteger("QTY");
pcCard.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(3);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pcCard);
}
public Pallet GetUpdatePalletLoading(string loadNo, string palletno, string userid, out string resultMessage)
{
resultMessage = string.Empty;
Pallet pallet = null;
try
{
ProcParam procPara = new ProcParam(5)
{
ProcedureName = "SCANNER_PACK_FG.GET_UPD_PALLET_LOADING"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strPALLET_NO", palletno);
procPara.AddParamInput(2, "strLOADING_NO", loadNo);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pallet = new Pallet();
pallet.PALLET_NO = OraDataReader.Instance.GetString("PALLET_NO");
pallet.SO_NO = OraDataReader.Instance.GetString("SO_NO");
pallet.PALLET_SEQ = OraDataReader.Instance.GetInteger("PALLET_SEQ");
pallet.PALLET_TOTAL = OraDataReader.Instance.GetInteger("PALLET_TOTAL");
pallet.PALLET_STATUS = OraDataReader.Instance.GetString("PALLET_STATUS");
pallet.PALLET_BOX = OraDataReader.Instance.GetInteger("PALLET_BOX");
pallet.PALLET_PCS = OraDataReader.Instance.GetInteger("PALLET_PCS");
pallet.PARTY_NAME = OraDataReader.Instance.GetString("PARTY_NAME");
pallet.ETA = OraDataReader.Instance.GetString("ETA");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(3);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pallet);
}
public string UploadProductBom_SAGE50(DataTable dt, string userid)
{
try
{
ProcParam param = new ProcParam(8)
{
ProcedureName = "MASTER_PACK.UPLOAD_PURCHASE_ORDER"
};
var arrASSEMBLY_ID = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Assembly ID")).ToArray();
param.AddParamInput(0, "strASSEMBLY_ID", arrASSEMBLY_ID, OracleDbType.Varchar2);
var arrASSEMBLY_DESC = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Assembly Description")).ToArray();
param.AddParamInput(1, "strASSEMBLY_DESC", arrASSEMBLY_DESC, OracleDbType.Varchar2);
var arrREVISION_NO = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Revision Number")).ToArray();
param.AddParamInput(2, "strREVISION_NO", arrREVISION_NO, OracleDbType.Decimal);
var arrCOMPONENT_ID = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Component ID")).ToArray();
param.AddParamInput(3, "strCOMPONENT_ID", arrCOMPONENT_ID, OracleDbType.Varchar2);
var arrCOMPONENT_QTY_NEEDED = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Component Qty Needed")).ToArray();
param.AddParamInput(4, "strCOMPONENT_QTY_NEEDED", arrCOMPONENT_QTY_NEEDED, OracleDbType.Decimal);
var arrCOMPONENT_DESC = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Component Description")).ToArray();
param.AddParamInput(5, "strCOMPONENT_DESC", arrCOMPONENT_DESC, OracleDbType.Varchar2);
param.AddParamInput(6, "strUSER_ID", ArrayOf <object> .Create(dt.Rows.Count, userid), OracleDbType.Varchar2);
param.AddParamOutput(7, "RESULTMSG", OracleDbType.Varchar2, 255, "OK", dt.Rows.Count);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param, dt.Rows.Count);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
else
{
if (((Oracle.DataAccess.Types.OracleString[])(param.ReturnValue(7)))[0].Value == "OK")
{
return("OK");
}
else
{
return("Error");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public string UpdateReturnProductCard(string qcReturnNo, string serialNo, string userid, out int totalBox)
{
totalBox = -1;
string resultMsg = string.Empty;
try
{
ProcParam procPara = new ProcParam(5);
procPara.ProcedureName = "SCANNER_RETRUN_PACK.UPD_PC_FG_RETURN";
procPara.AddParamInput(0, "strRT_NO", qcReturnNo);
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamOutput(2, "strTOTAL_BOX", OracleDbType.Decimal, 255);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)procPara.ReturnValue(3);
OracleDecimal resultDB = (OracleDecimal)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMsg = result.Value;
totalBox = resultDB.ToInt32();
}
}
catch (Exception ex)
{
totalBox = -1;
throw ex;
}
return(resultMsg);
}
public bool CheckApiAuthen(string username, string password, out string userRole)
{
userRole = string.Empty;
bool result = false;
try
{
ProcParam procPara = new ProcParam(6)
{
ProcedureName = "ADMINISTRATOR_PACK.CHECK_AUTHENTICATION_API"
};
procPara.AddParamInput(0, "strUSER_USR", username);
procPara.AddParamInput(1, "strUSER_PWD", password);
procPara.AddParamOutput(2, "strUSER_ID", OracleDbType.Varchar2, 30);
procPara.AddParamOutput(3, "strUSER_NAME", OracleDbType.Varchar2, 30);
procPara.AddParamOutput(4, "strROLE", OracleDbType.Varchar2, 30);
procPara.AddParamOutput(5, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.DataAc.LastException != null)
{
throw GlobalDB.Instance.DataAc.LastException;
}
var resultMsg = (OracleString)procPara.ReturnValue(5);
if (resultMsg == "OK")
{
userRole = ((OracleString)procPara.ReturnValue(4)).ToString();
result = true;
}
}
catch (Exception ex)
{
result = false;
}
return(result);
}
public string UploadVendorMaster(DataTable dt, string partyType, string userid)
{
try
{
ProcParam param = new ProcParam(7)
{
ProcedureName = "MASTER_PACK.UPLOAD_PARTY_MASTER"
};
var arrCustomerId = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Vendor ID")).ToArray();
param.AddParamInput(0, "strCUSTOMER_ID", arrCustomerId, OracleDbType.Varchar2);
var arrCustomerName = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Vendor Name")).ToArray();
param.AddParamInput(1, "strCUSTOMER_NAME", arrCustomerName, OracleDbType.Varchar2);
var arrTerm = dt.AsEnumerable().ToList().Select(r => r.Field <string>("Due Days")).ToArray();
param.AddParamInput(2, "strTERM", arrTerm, OracleDbType.Varchar2);
//var arrPriceLevel = dt.AsEnumerable().ToList().Select(r => r.Field<string>("Pricing Level")).ToArray();
//param.AddParamInput(3, "strPRICE_LEVEL", arrPriceLevel, OracleDbType.Varchar2);
param.AddParamInput(3, "strPRICE_LEVEL", ArrayOf <object> .Create(dt.Rows.Count, string.Empty), OracleDbType.Varchar2);
param.AddParamInput(4, "strPARTY_TYPE", ArrayOf <object> .Create(dt.Rows.Count, partyType), OracleDbType.Varchar2);
param.AddParamInput(5, "strUSER_ID", ArrayOf <object> .Create(dt.Rows.Count, userid), OracleDbType.Varchar2);
param.AddParamOutput(6, "RESULTMSG", OracleDbType.Varchar2, 255, "OK", dt.Rows.Count);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param, dt.Rows.Count);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
else
{
if (((Oracle.DataAccess.Types.OracleString[])(param.ReturnValue(6)))[0].Value == "OK")
{
return("OK");
}
else
{
return("Error");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public PickQty GetPickInfo(string pickNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
PickQty pickQty = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_FG.GET_PICK_INFO"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strPICK_NO", pickNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pickQty = new PickQty();
pickQty.PICK_NO = OraDataReader.Instance.GetString("PICK_NO");
pickQty.QTY = OraDataReader.Instance.GetInteger("QTY");
pickQty.PICKED_QTY = OraDataReader.Instance.GetInteger("PICKED_QTY");
pickQty.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pickQty);
}
public bool UserAuthentication(string userName, string password)
{
this.userid = string.Empty;
string result = string.Empty;
try
{
ProcParam procPara = new ProcParam(3);
procPara.ProcedureName = "ADMINISTRATOR_PACK.USER_LOGON";
procPara.AddParamReturn(0, "ReturnValue", OracleDbType.Raw, 255);
procPara.AddParamInput(1, "strVal", userName);
procPara.AddParamInput(2, "strPwd", password);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
//OracleString objResult = (OracleString)procPara.Parameters[0].Value;
OracleBinary objResult = (OracleBinary)procPara.ReturnValue(0);
//byte[] resultByte = this.GetCurrectByteArray(objResult);
//result = System.Text.Encoding.Default.GetString(resultByte, 0, resultByte.Length);
if (!objResult.IsNull)
{
result = Encoding.Default.GetString(objResult.Value, 0, objResult.Length);
}
else
{
result = "";
}
}
catch (Exception ex)
{
throw ex;
}
if (result != "")
{
//Authentication pass
this.userid = result;
return(true);
}
else
{
//Authentication fail
return(false);
}
}
public QCReturn GetQcReturnInfo(string qcReturnNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
QCReturn qcReturn = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_RETRUN_PACK.GET_FG_RETURN"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strRT_NO", qcReturnNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
qcReturn = new QCReturn();
qcReturn.WH_ID = OraDataReader.Instance.GetString("WH_ID");
qcReturn.RT_NO = OraDataReader.Instance.GetString("RT_NO");
qcReturn.NO_OF_LABEL = OraDataReader.Instance.GetInteger("NO_OF_LABEL");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(qcReturn);
}
public LoadQty FGPress_GetLoadInfo(string loadNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
LoadQty loadQty = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_FG_PRESS.GET_LOADING_INFO"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strLOADING_NO", loadNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
loadQty = new LoadQty();
loadQty.LOADING_NO = OraDataReader.Instance.GetString("LOADING_NO");
loadQty.LOADED_BOX = OraDataReader.Instance.GetInteger("LOADED_BOX");
loadQty.LOADED_QTY = OraDataReader.Instance.GetInteger("LOADED_QTY");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(loadQty);
}