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 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 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 string TEST_PROC_EXCEPTION(string val)
{
string result = string.Empty;
try
{
ProcParam param = new ProcParam(2);
param.ProcedureName = "ADMINISTRATOR_PACK.TEST_EXCEPTION";
param.AddParamInput(0, "strNewPwd", val);
param.AddParamOutput(1, "RESULTMSG", OracleDbType.NVarchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
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 void UpdateRoleProgram(List <RoleProgram> lstRoleProgUpd, string userid)
{
try
{
List <ProcParam> paraList = new List <ProcParam>();
ProcParam procDetail = null;
foreach (RoleProgram roleProg in lstRoleProgUpd)
{
procDetail = new ProcParam(6);
procDetail.ProcedureName = "ADMINISTRATOR_PACK.ROLE_PROGRAM_UPD";
procDetail.AddParamInput(0, "strROLE_ID", roleProg.ROLE_ID);
procDetail.AddParamInput(1, "strPROG_ID", roleProg.PROG_ID);
procDetail.AddParamInput(2, "strREP_ID", "001"); //DEFAULT FOR REPORT ID '001'
procDetail.AddParamInput(3, "strREC_STAT ", (roleProg.REC_STAT ? "Y" : "N"));
procDetail.AddParamInput(4, "strUSER_ID", userid);
procDetail.AddParamOutput(5, "RESULTMSG", OracleDbType.Varchar2, 255, "OK");
paraList.Add(procDetail);
}
GlobalDB.Instance.DataAc.ExecuteNonQuery(paraList);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
}
catch (Exception ex)
{
throw ex;
}
}
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 void UpdateProductionProcess(List <ProdProcess> lstProdProcess, string userid)
{
try
{
List <ProcParam> paraList = new List <ProcParam>();
ProcParam procDetail = null;
foreach (ProdProcess prodPro in lstProdProcess)
{
procDetail = new ProcParam(6);
procDetail.ProcedureName = "MASTER_PACK.M_PROD_PROCESS_UPD";
procDetail.AddParamInput(0, "strPROD_SEQ_NO", prodPro.PROD_SEQ_NO);
procDetail.AddParamInput(1, "strPROCESS_NO", prodPro.PROCESS_NO);
procDetail.AddParamInput(2, "strSTEP_NO", prodPro.STEP_NO); //DEFAULT FOR REPORT ID '001'
procDetail.AddParamInput(3, "strREC_STAT ", (prodPro.REC_STAT ? "Y" : "N"));
procDetail.AddParamInput(4, "strUSER_ID", userid);
procDetail.AddParamOutput(5, "RESULTMSG", OracleDbType.Varchar2, 255, "OK");
paraList.Add(procDetail);
}
if (paraList.Count != 0)
{
GlobalDB.Instance.DataAc.ExecuteNonQuery(paraList);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
}
}
catch (Exception ex)
{
throw ex;
}
}
public string UpdateMaterial(Material material, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(16)
{
ProcedureName = "MASTER_PACK.M_MATERIAL_UPD"
};
param.AddParamInput(0, "strMTL_SEQ_NO", material.MTL_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));
}
else
{
param.AddParamInput(10, "strMTL_IMAGE", DBNull.Value);
}
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);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(15);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
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 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 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 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 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 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 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 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);
}
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 void UpdateProductionProcess(List <ProdProcess> lstProdProcess, string strNEW_PROD_SEQ_NO, string userid)
{
ProcParam procDetail = null;
try
{
procDetail = new ProcParam(6)
{
ProcedureName = "MASTER_PACK.M_PROD_PROCESS_UPD"
};
//PROD_SEQ_NO
procDetail.AddParamInput(0, "strPROD_SEQ_NO", ArrayOf <object> .Create(lstProdProcess.Count, strNEW_PROD_SEQ_NO), OracleDbType.Varchar2);
//PROCESS_NO
var arrPROCESS_NO = (from prodP in lstProdProcess
select prodP.PROCESS_NO).ToArray();
procDetail.AddParamInput(1, "strPROCESS_NO", arrPROCESS_NO, OracleDbType.Varchar2);
//STEP_NO
var arrSTEP_NO = (from prodP in lstProdProcess
select(object) prodP.STEP_NO).ToArray();
procDetail.AddParamInput(2, "strSTEP_NO", arrSTEP_NO, OracleDbType.Int32);
//REC_STAT
var arrREC_STAT = (from prodP in lstProdProcess
select prodP.REC_STAT ? "Y" : "N").ToArray();
procDetail.AddParamInput(3, "strREC_STAT", arrREC_STAT, OracleDbType.Varchar2);
//USER_ID
procDetail.AddParamInput(4, "strUSER_ID", ArrayOf <object> .Create(lstProdProcess.Count, userid), OracleDbType.Varchar2);
//RESULTMSG
procDetail.AddParamOutput(5, "RESULTMSG", OracleDbType.Varchar2, 255, "OK", lstProdProcess.Count);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procDetail, lstProdProcess.Count);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
}
public TransferOrderInfo GetTOInfo(string toNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
TransferOrderInfo tOrder = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_MTST_VER.GET_TO_INFO"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strTO_NO", toNo);
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())
{
tOrder = new TransferOrderInfo();
tOrder.TO_NO = OraDataReader.Instance.GetString("TO_NO");
}
}
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(tOrder);
}
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 UpdateUser(User user, string userid)
{
string result = string.Empty;
try
{
ProcParam param = new ProcParam(13);
param.ProcedureName = "ADMINISTRATOR_PACK.USER_UPD";
param.AddParamInput(0, "strUSER_ID", user.USER_ID);
param.AddParamInput(1, "strUSER_NAME", user.USER_NAME);
param.AddParamInput(2, "strWARE_ID", user.WARE_ID);
param.AddParamInput(3, "strLOGIN", user.LOGIN);
param.AddParamInput(4, "strPWD", user.PWD);
param.AddParamInput(5, "strEMPLOYEE_ID", user.EMPLOYEE_ID);
param.AddParamInput(6, "strROLE_ID", user.ROLE_ID);
param.AddParamInput(7, "strREMARK", user.REMARK);
param.AddParamInput(8, "strREC_STAT", (user.REC_STAT ? "Y" : "N"));
param.AddParamInput(9, "strEMAIL", user.EMAIL);
param.AddParamInput(10, "strCOMP_ID", user.COMP_ID);
param.AddParamInput(11, "strUSER_ID", userid);
param.AddParamOutput(12, "RESULTMSG", OracleDbType.NVarchar2, 255, "OK");
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)param.ReturnValue(12);
if (!resultDB.IsNull)
{
result = resultDB.Value;
}
else
{
throw GlobalDB.Instance.DataAc.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public string UpdatePostTO(string tono, string postRef, string userid)
{
string resultMsg = string.Empty;
try
{
#region "Transaction Header"
ProcParam param = new ProcParam(4)
{
ProcedureName = "TO_PACK.TO_POSTREF_UPD"
};
param.AddParamInput(0, "strSO_NO", tono);
param.AddParamInput(1, "strPOST_REF ", postRef);
param.AddParamInput(2, "strUSER_ID", userid);
param.AddParamOutput(3, "RESULTMSG", OracleDbType.Varchar2, 255);
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(3);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public bool IsNumberOfPalateMaching(string arrNo, out string resultMessage)
{
resultMessage = string.Empty;
string result = string.Empty;
try
{
ProcParam procPara = new ProcParam(3)
{
ProcedureName = "ARRIVAL_PACK.ARR_NUMBER_PTL_MACHING"
};
procPara.AddParamReturn(0, "ReturnValue", OracleDbType.NVarchar2, 100);
procPara.AddParamInput(1, "strARRIVAL_NO", arrNo);
procPara.AddParamOutput(2, "RESULTMSG", OracleDbType.NVarchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
//inform time
//this.executionTime = oracleData.ExecutionTime;
result = procPara.Parameters[0].Value.ToString().Trim();
resultMessage = procPara.Parameters[2].Value.ToString();
}
catch (Exception ex)
{
resultMessage = ex.Message;
result = "";
}
if (result == "Y")
{
return(true);
}
else
{
return(false);
}
}