public string BL_GetMyRights1(string strUserID)
{
SqlDataLayer objSql = new SqlDataLayer();
string strResult = string.Empty;
try
{
strResult = objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT UTYPE FROM TBLUSER WHERE UID = '" + strUserID.Trim() + "'");
if (strResult.Trim() != string.Empty)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 2, "Fetching user rights successfully", strUserID);
return(strResult.Trim());
}
else
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, "Fetching user rights failed.", strUserID);
return("1~Do not have device operational rights.");
}
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), strUserID);
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public DataTable getReceiptData(string strReceiptNo)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
if (string.IsNullOrEmpty(strReceiptNo) == true)
{
return(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT LOTNO, RECEIPT_NUM, ITEM_CODE, PROJECT_NUMBER, CAST(TXN_QTY AS VARCHAR(50)) + ' ' + UOM AS TXN_QTY, STATUS, ITEM_DESCRIPTION FROM TBLQC_DETAILS WHERE STATUS = 1").Tables[0]);
}
else
{
return(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT LOTNO, RECEIPT_NUM, ITEM_CODE, PROJECT_NUMBER, CAST(TXN_QTY AS VARCHAR(50)) + ' ' + UOM AS TXN_QTY, STATUS, ITEM_DESCRIPTION FROM TBLQC_DETAILS WHERE STATUS = 1 AND RECEIPT_NUM = '" + strReceiptNo.Trim() + "'").Tables[0]);
}
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), "-");
//return "1~" + objSql.getErrorMsg(ex.ToString());
throw ex;
}
finally
{
objSql = null;
}
}
public string OfflineInsertPhysicalCount(DataTable dtTemp)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[2];
try
{
objParameters[0] = new SqlParameter("@TEMP", SqlDbType.Structured);
objParameters[1] = new SqlParameter("@RESULT", SqlDbType.VarChar, 500);
objParameters[0].Value = dtTemp;
objParameters[1].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_Offline_Physical_count", objParameters, "@RESULT", "@RESULT") != "")
{
return(objParameters[1].Value.ToString());
}
else
{
return("1~" + string.Empty);
}
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string Validate_WIP(string strBarcode, string strProcessCard, string strFlag)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
if (strFlag == "ProcessCard")
{
return(objSql.ExecuteScalar(SqlDataLayer.strLocal, "SELECT COUNT(BARCODE) FROM TBLPROCESSCARD WHERE BARCODE = '" + strProcessCard.Trim() + "'").ToString());
}
else if (strFlag == "Serial")
{
string strWo_No = objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT WONO FROM TBLPROCESSCARD WHERE BARCODE = '" + strBarcode.Trim() + "'");
return(objSql.ExecuteScalar(SqlDataLayer.strLocal, "SELECT COUNT(SER) FROM TBLWO_INWARD WHERE WO_NO= '" + strWo_No.Trim() + "' AND SER = '" + strBarcode.Trim() + "' AND PRINTST = 1").ToString());
}
else
{
return("0~" + objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT COUNT(BARCODE) FROM TBLSTOCK WHERE BARCODE = '" + strBarcode.Trim() + "'"));
}
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public static void Initialize(string connectionString, string adapterFolder)
{
Instance = new SqlDataLayer(connectionString);
Security = new SqlDataLayer(connectionString);
Accounts = new SqlDataLayer(connectionString);
AdapterManager = new AdapterManager(adapterFolder);
}
public DataTable Get_Processes(string strProcessCard, string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
if (objSql.ExecuteScalar(SqlDataLayer.strLocal, "SELECT COUNT(BARCODE) FROM TBLPROCESSCARD WHERE BARCODE = '" + strProcessCard.Trim() + "'") != 0)
{
return(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT DISTINCT PROCESSSTAGE FROM TBLPROCESSCARD_SUBITEM INNER JOIN TBLPROCESS_ASSIGN ON TBLPROCESSCARD_SUBITEM.PROCESSSTAGE = TBLPROCESS_ASSIGN.PROCESS WHERE DBO.FNSPLIT(TBLPROCESS_ASSIGN.OPERATOR, '=', 1) = '" + strUsername.Trim() + "' AND TBLPROCESSCARD_SUBITEM.BARCODE = '" + strProcessCard.Trim() + "' AND TBLPROCESSCARD_SUBITEM.STATUS = 0").Tables[0]);
}
else
{
DataTable dt = new DataTable();
dt.Columns.Add("PROCESSSTAGE");
DataRow dtrow = dt.NewRow();
dtrow["PROCESSSTAGE"] = "Process card barcode not exist.";
dt.Rows.Add(dtrow);
return(dt);
}
}
catch (Exception ex)
{
//return "1~" + objSql.getErrorMsg(ex.ToString());
throw ex;
}
finally
{
objSql = null;
}
}
public string Validate_Tools(string strProcessType, string strProcessStage, string strAssetCode)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
int intResult = objSql.ExecuteScalar(SqlDataLayer.strLocal, "SELECT DBO.VALIDATE_CALIBRATION('" + strAssetCode.Trim() + "')");
if (intResult == 0)
{
return("1~" + "Calibration due date expired.");
}
else if (intResult == 1)
{
return("0~" + objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT dbo.TBLASSET.ASSETCODE + '=' + (SELECT dbo.TBLCATEGORY.CATNM FROM dbo.TBLCATEGORY INNER JOIN dbo.TBLASSET ON dbo.TBLCATEGORY.RECNO = dbo.TBLASSET.CATEGORY WHERE dbo.TBLCATEGORY.RECNO = dbo.TBLASSET.CATEGORY) FROM dbo.TBLASSET INNER JOIN dbo.TBLSTOCK ON dbo.TBLASSET.RECNO = dbo.TBLSTOCK.ASSETCODE WHERE DBO.TBLSTOCK.BARCODE = '" + strAssetCode.Trim() + "'"));
}
else
{
return("1~" + "Asset not found.");
}
//return "0~" + objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT CAST(COUNT(ASSETCODE) AS VARCHAR(50)) AS ASSETCODE FROM TBLTOOL_ASSIGN WHERE DBO.FNSPLIT(ASSETCODE , '=', 1) = '" + strAssetCode.Trim() + "' AND PRODUCTTYPE = '" + strProcessType.Trim() + "' AND PROCESS = '" + strProcessStage.Trim() + "'");
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public DataTable UpdateRecevingDL(string strBarcode, string strStatus, string strUserID)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[4];
try
{
objParameters[0] = new SqlParameter("@BARCODE", SqlDbType.VarChar);
objParameters[1] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[2] = new SqlParameter("@STATUS", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@RESULT", SqlDbType.VarChar, 100);
objParameters[0].Value = strBarcode.Trim();
objParameters[1].Value = strUserID.Trim();
objParameters[2].Value = strStatus.Trim();
objParameters[3].Direction = ParameterDirection.Output;
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 2, "Barcode receiving successfully.", strUserID);
//return "0~" + BL_Common.DataTableToJSONWithJSONNet(objSql.ExecuteProcedure_Table(SqlDataLayer.strLocal, "sp_ScanningReceiving", objParameters));
return(objSql.ExecuteProcedure_Table(SqlDataLayer.strLocal, "sp_ScanningReceiving", objParameters));
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), strUserID);
//return "1~" + objSql.getErrorMsg(ex.ToString());
throw ex;
}
finally
{
objParameters = null;
objSql = null;
}
}
public static int BL_Write(string strModule, string strMethod, int intType, string strDetails, string strUser)
{
if (strDetails != "")
{
string strErrorType;
switch (intType)
{
case 0:
strErrorType = "Error";
break;
case 1:
strErrorType = "Worning";
break;
case 2:
strErrorType = "Success";
break;
default:
strErrorType = "Error";
break;
}
string strPlantCode = string.Empty;
if (strUser.Trim() == null)
{
strPlantCode = "'-'";
}
else
{
strPlantCode = "(SELECT TOP(1) PLANT FROM TBLUSER WHERE UID = '" + strUser.Trim() + "')";
}
SqlDataLayer objSql = new SqlDataLayer();
string strQuery = string.Empty;
try
{
if (strDetails.Contains("This SqlTransaction has completed") == false)
{
strQuery = "INSERT INTO TBLEVENTS (MODULE, METHOD, TYPE, DETAILS, CRDATE, CRUSER, PLANT, PROGRAM) VALUES (" +
"'" + strModule.Trim() + "', '" + strMethod.Trim() + "', " +
"'" + strErrorType.Trim() + "', '" + strDetails.Replace("System.Exception:", "").Trim() + "', GETDATE(), '" + strUser.Trim() + "'," + strPlantCode + ", 'RF SERVER')";
return(objSql.ExecuteNonQuery(SqlDataLayer.strLocal, strQuery));
}
}
catch //(Exception ex)
{
//throw new Exception(ex.ToString());
}
finally
{
strQuery = string.Empty;
objSql = null;
}
}
return(1);
}
private int Update12(SqlDataLayer conn)
{
//mLog.Info($"12: Adding activecompanies column to people...");
//conn.ExecNonQuery(@"ALTER TABLE people ADD COLUMN activecompanies integer");
return(14);
}
protected void btnUpload_Click(object sender, EventArgs e)
{
SqlDataLayer _dataLayer = new SqlDataLayer(connectionString);
string fileExtension = Path.GetExtension(FileUpload.FileName); // Get File Extension
TimeKeeping timeKeepings = new TimeKeeping();
if (FileUpload.HasFile) // Check if the file is Uploaded
{
if (fileExtension == ".csv") //Check the file Extension
{
string filePath = Server.MapPath(FileUpload.FileName);
var reader = new StreamReader(File.OpenRead(filePath));
string reportString = File.ReadLines(filePath).Last(); //Footer
int ReportId = Convert.ToInt32(reportString.Split(',')[1]);
bool ReportExists = _dataLayer.CheckIfReportIdExists(ReportId);
if (!ReportExists)
{
while (!reader.EndOfStream)
{
var fileData = reader.ReadLine();
if (fileData.IndexOf("date") != 0 && fileData.IndexOf("report") != 0) //Excluding Header and Footer when processing CSV file
{
var fileRow = fileData.Split(',');
CultureInfo ci = CultureInfo.CreateSpecificCulture("en-GB");
var Date = Convert.ToDateTime(fileRow[0], ci.DateTimeFormat).ToString("d");
timeKeepings.EmployeeId = Convert.ToInt32(fileRow[2]);
timeKeepings.HoursWorked = Convert.ToDouble(fileRow[1].ToString());
timeKeepings.JobGroupId = _dataLayer.GetJobGroupIdByCode(fileRow[3].ToString());
timeKeepings.Date = Convert.ToDateTime(Date);
timeKeepings.ReportId = ReportId;
//Insert Into Database Table
_dataLayer.CreateTimeKeeping(timeKeepings);
}
}
}
else
{
ErrorMessage.Text = "Report has already been uploaded.";
}
}
else
{
ErrorMessage.Text = "Please Upload .CSV file";
}
}
else
{
ErrorMessage.Text = "No File Uploaded.";
}
}
public string InsertProcessSetup(string strWorkOrder, string strProduction, string strStage, string strSetupTime, DataTable dt_Serial, DataTable dt_Tools, string strUserID, string strPlant)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[14];
try
{
objParameters[0] = new SqlParameter("@TEMP", SqlDbType.Structured);
objParameters[1] = new SqlParameter("@TEMP_SER", SqlDbType.Structured);
objParameters[2] = new SqlParameter("@WONO", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@PARTNO", SqlDbType.VarChar);
objParameters[4] = new SqlParameter("@DESCRIPTION", SqlDbType.VarChar);
objParameters[5] = new SqlParameter("@PRODTYPE", SqlDbType.VarChar);
objParameters[6] = new SqlParameter("@STAGE", SqlDbType.VarChar);
objParameters[7] = new SqlParameter("@STARTDATE", SqlDbType.VarChar);
objParameters[8] = new SqlParameter("@ENDDATE", SqlDbType.VarChar);
objParameters[9] = new SqlParameter("@STATUS", SqlDbType.Int);
objParameters[10] = new SqlParameter("@PLANTNO", SqlDbType.VarChar);
objParameters[11] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[12] = new SqlParameter("@SETUPTIME", SqlDbType.VarChar);
objParameters[13] = new SqlParameter("@RESULT", SqlDbType.VarChar, 500);
objParameters[0].Value = dt_Tools;
objParameters[1].Value = dt_Serial;
objParameters[2].Value = strWorkOrder.Trim();
objParameters[3].Value = "";
objParameters[4].Value = "";
objParameters[5].Value = strProduction.Trim();
objParameters[6].Value = strStage.Trim();
objParameters[7].Value = DateTime.Now;
objParameters[8].Value = DateTime.Now;
objParameters[9].Value = 0;
objParameters[10].Value = strPlant;
objParameters[11].Value = strUserID;
objParameters[12].Value = strSetupTime;
objParameters[13].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_ProcessSetup", objParameters, "@RESULT", "@RESULT") != "")
{
return(objParameters[13].Value.ToString());
}
else
{
return("1~" + "");
}
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string Asset_Calibration(string strBarcode, string strLastCaliDate, string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[5];
try
{
objParameters[0] = new SqlParameter("@ASSETCODE", SqlDbType.VarChar);
objParameters[1] = new SqlParameter("@CALDATE", SqlDbType.DateTime);
objParameters[2] = new SqlParameter("@MODE", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[4] = new SqlParameter("@RESULT", SqlDbType.VarChar, 600);
objParameters[0].Value = strBarcode.Trim();
if (strLastCaliDate == string.Empty)
{
objParameters[1].Value = DateTime.Now;
}
else
{
objParameters[1].Value = Convert.ToDateTime(strLastCaliDate.Trim());
}
if (strLastCaliDate == "")
{
objParameters[2].Value = "GET";
}
else
{
objParameters[2].Value = "SET";
}
objParameters[3].Value = strUsername.Trim();
objParameters[4].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_ScanningCalibration", objParameters, "@RESULT", "@RESULT") != "")
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 2, "Executed Successfully.", strUsername);
return("0~" + objParameters[4].Value.ToString());
}
else
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, "Execution Failed.", strUsername);
return("1~Invalid assetcode scanned.");
}
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), strUsername);
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string InsertProduction(string strProcessBarcode, string strProcess, string strSerialno, DataTable dtCompBarcode, DataTable dtSerBarcode, string strRemark, string strMode, int intRework, string strUserID, string strPlant, int intRef)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[12];
try
{
objParameters[0] = new SqlParameter("@BARCODE", SqlDbType.VarChar);
objParameters[1] = new SqlParameter("@PROCESSDESC", SqlDbType.VarChar);
objParameters[2] = new SqlParameter("@TRACKING_ID", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@COMPBARCODE", SqlDbType.Structured);
objParameters[4] = new SqlParameter("@SERBARCODE", SqlDbType.Structured);
objParameters[5] = new SqlParameter("@REMARK", SqlDbType.VarChar);
objParameters[6] = new SqlParameter("@MODE", SqlDbType.VarChar);
objParameters[7] = new SqlParameter("@PLANTNO", SqlDbType.VarChar);
objParameters[8] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[9] = new SqlParameter("@REWORK", SqlDbType.Int);
objParameters[10] = new SqlParameter("@STAGE_REF", SqlDbType.Int);
objParameters[11] = new SqlParameter("@RESULT", SqlDbType.VarChar, 500);
objParameters[0].Value = strProcessBarcode.Trim();
objParameters[1].Value = strProcess.Trim();
objParameters[2].Value = strSerialno.Trim();
objParameters[3].Value = dtCompBarcode;
objParameters[4].Value = dtSerBarcode;
objParameters[5].Value = strRemark.Trim();
objParameters[6].Value = strMode.Trim();
objParameters[7].Value = strPlant;
objParameters[8].Value = strUserID;
objParameters[9].Value = intRework;
objParameters[10].Value = intRef;
objParameters[11].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_WIPScanning", objParameters, "@RESULT", "@RESULT") != "")
{
return(objParameters[11].Value.ToString());
}
else
{
return("1~" + string.Empty);
}
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string UpdatePicking(string strPackSlip, string strLocation, string strItemCode, string strQty, string strPacket, string strStatus, string strUserID)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[8];
try
{
objParameters[0] = new SqlParameter("@PACKSLIP", SqlDbType.VarChar);
objParameters[1] = new SqlParameter("@PACKBARCODE", SqlDbType.VarChar);
objParameters[2] = new SqlParameter("@ITEMBARCODE", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@LOCATION", SqlDbType.VarChar);
objParameters[4] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[5] = new SqlParameter("@QTY", SqlDbType.Float);
objParameters[6] = new SqlParameter("@STATUS", SqlDbType.Int);
objParameters[7] = new SqlParameter("@RESULT", SqlDbType.VarChar, 100);
objParameters[0].Value = strPackSlip.Trim();
objParameters[1].Value = strPacket.Trim();
objParameters[2].Value = strItemCode.Trim();
objParameters[3].Value = strLocation.Trim();
objParameters[4].Value = strUserID.Trim();
objParameters[5].Value = Convert.ToDouble(strQty);
objParameters[6].Value = Convert.ToInt32(strStatus);
objParameters[7].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_ScanningPicking", objParameters, "@RESULT", "@RESULT") != "")
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 2, "Material picking successfully", strUserID);
return("0~" + objParameters[7].Value.ToString());
}
else
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, "Material picking failed", strUserID);
return("1~Error in picking.");
}
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), strUserID);
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string getWorkOrder_PS()
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return("0~" + BL_Common.dataTableToString(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT TOP 10 WO_NO FROM TBLWO_INWARD WHERE WO_STATUS = 'Released' ORDER BY WO_NO").Tables[0])); //SELECT DISTINCT(WO_NO) FROM TBLWO_INWARD WHERE WO_STATUS = 'Released'
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string getField(string strQuery, string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return(objSql.ExecuteScalarString(SqlDataLayer.strLocal, strQuery));
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string PickSlip_KitMat_Details(string strPackSlip)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return("0~" + BL_Common.dataTableToString(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT PICK_SLIP_NO, ITEM_CODE, REPLACE(REPLACE(DESCRIPTION, '@', ''), '$', '') AS DESCRIPTION, ISSUED_QTY, PICK_QTY, UOM FROM DBO.TBLPICKING WHERE ST = 1 AND PICK_SLIP_NO = '" + strPackSlip.Trim() + "' ORDER BY PICK_SLIP_NO ASC").Tables[0]));
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string InsertCompScan(string strWorkOrder, string strPartNo, string strDescription, DataTable dtPackage, string strUserID, string strPlant)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[9];
try
{
objParameters[0] = new SqlParameter("@TEMP", SqlDbType.Structured);
objParameters[1] = new SqlParameter("@WONO", SqlDbType.VarChar);
objParameters[2] = new SqlParameter("@PARTNO", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@DESCRIPTION", SqlDbType.VarChar);
objParameters[4] = new SqlParameter("@REMARK", SqlDbType.VarChar);
objParameters[5] = new SqlParameter("@STATUS", SqlDbType.Int);
objParameters[6] = new SqlParameter("@PLANTNO", SqlDbType.VarChar);
objParameters[7] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[8] = new SqlParameter("@RESULT", SqlDbType.VarChar, 500);
objParameters[0].Value = dtPackage;
objParameters[1].Value = strWorkOrder.Trim();
objParameters[2].Value = strPartNo.Trim();
objParameters[3].Value = strDescription.Trim();
objParameters[4].Value = "";
objParameters[5].Value = 0;
objParameters[6].Value = strPlant.Trim();
objParameters[7].Value = strUserID.Trim();
objParameters[8].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_CompScanning", objParameters, "@RESULT", "@RESULT") != "")
{
return(objParameters[8].Value.ToString());
}
else
{
return("1~" + string.Empty);
}
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string getTable(string strQuery, string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return(BL_Common.dataTableToString1(objSql.ExecuteDataset(SqlDataLayer.strLocal, strQuery).Tables[0]));
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string Validate_Packet(string strWork_Order, string strPacketBarcode)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return(objSql.ExecuteScalar(SqlDataLayer.strLocal, "SELECT COUNT(RECNO) FROM TBLPICKING WHERE WORK_ORDER_NO ='" + strWork_Order.Trim() + "' AND SERIALNO = '" + strPacketBarcode.Trim() + "' AND ST = 2").ToString());
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string getWorkOrder_getProdType(string strWorkOrder)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return("0~" + BL_Common.dataTableToString(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT DISTINCT PRODTYPE FROM TBLPROCESSCARD WHERE WO_NO = '" + strWorkOrder.Trim() + "' AND WO_STATUS = 'Released'").Tables[0]));
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string getWorkOrder_Details_PS(string strWorkOrder)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return("0~" + BL_Common.dataTableToString(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT DISTINCT ASSY_ITEM_CODE, SER FROM TBLWO_INWARD WHERE WO_NO = '" + strWorkOrder.Trim() + "' AND WO_STATUS = 'Released'").Tables[0]));
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string PickSlip_KitMat(string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return("0~" + BL_Common.dataTableToString(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT DISTINCT(PICK_SLIP_NO) AS PICK_SLIP_NO FROM DBO.TBLPICKING WHERE ST = 1 ORDER BY PICK_SLIP_NO ASC").Tables[0]));
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public DataTable getTable1(string strQuery, string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return(objSql.ExecuteDataset(SqlDataLayer.strLocal, strQuery).Tables[0]);
}
catch (Exception ex)
{
//return "1~" + objSql.getErrorMsg(ex.ToString());
throw ex;
}
finally
{
objSql = null;
}
}
public string getPickSlipData(string strPackSlip, string strUserID)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return("0~" + BL_Common.dataTableToString(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT A.PICK_SLIP_NO, A.WORK_ORDER_NO, A.ITEM_CODE, REPLACE(REPLACE(A.DESCRIPTION, '@', ''), '$', '') AS DESCRIPTION, A.LOCTAOR AS LOCATOR, SUM(Isnull(A.ISSUED_QTY, 0)) AS PICK_QTY, SUM(Isnull(A.PICKED_QTY, 0)) AS PICKED_QTY, A.UOM FROM DBO.TBLPICK_SLIP_INWORD A INNER JOIN DBO.TBLPICK_SLIP_ASSIGN B ON A.PICK_SLIP_NO = B.PICK_SLIP_NO AND A.ST in (1, 2) AND B.ST = 0 AND B.PICKER = '" + strUserID.Trim() + "' AND A.PICK_SLIP_NO = '" + strPackSlip.Trim() + "' and (isnull(A.ISSUED_QTY, 0) - Isnull(A.PICKED_QTY, 0)) > 0 group by A.PICK_SLIP_NO, A.ITEM_CODE, A.WORK_ORDER_NO, A.DESCRIPTION, A.LOCTAOR, A.UOM").Tables[0]));
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), "-");
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string getPickSlipNo(string strUserID)
{
SqlDataLayer objSql = new SqlDataLayer();
try
{
return("0~" + BL_Common.dataTableToString(objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT DISTINCT (A.PICK_SLIP_NO) AS PICK_SLIP_NO FROM DBO.TBLPICK_SLIP_INWORD A INNER JOIN DBO.TBLPICK_SLIP_ASSIGN B ON A.PICK_SLIP_NO = B.PICK_SLIP_NO AND A.ST in (1, 2) AND B.ST = 0 AND B.PICKER = '" + strUserID.Trim() + "' and (isnull(A.ISSUED_QTY, 0) - Isnull(A.PICKED_QTY, 0)) > 0").Tables[0]));
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), "-");
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public string Save_Rework(DataTable dtTemp, string strWorkOrder, string strSerialNumber, string strRemark, int intStatus, string strPlant, string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[8];
try
{
objParameters[0] = new SqlParameter("@TEMP", SqlDbType.Structured);
objParameters[1] = new SqlParameter("@WONO", SqlDbType.VarChar);
objParameters[2] = new SqlParameter("@SER", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@REMARK", SqlDbType.VarChar);
objParameters[4] = new SqlParameter("@PLANTNO", SqlDbType.VarChar);
objParameters[5] = new SqlParameter("@STATUS", SqlDbType.Int);
objParameters[6] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[7] = new SqlParameter("@RESULT", SqlDbType.VarChar, 500);
objParameters[0].Value = dtTemp;
objParameters[1].Value = strWorkOrder.Trim();
objParameters[2].Value = strSerialNumber.Trim();
objParameters[3].Value = strRemark.Trim();
objParameters[4].Value = strPlant;
objParameters[5].Value = 0;
objParameters[6].Value = strUsername;
objParameters[7].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_Rework", objParameters, "@RESULT", "@RESULT") != "")
{
return(objParameters[7].Value.ToString());
}
else
{
return("1~" + string.Empty);
}
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string UpdateRecevingDL_Initial(string strBarcode, string strQty, string strStatus)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[4];
try
{
objParameters[0] = new SqlParameter("@BARCODE", SqlDbType.VarChar);
objParameters[1] = new SqlParameter("@QTY", SqlDbType.Float);
objParameters[2] = new SqlParameter("@STATUS", SqlDbType.VarChar);
objParameters[3] = new SqlParameter("@RESULT", SqlDbType.VarChar, 100);
objParameters[0].Value = strBarcode.Trim();
objParameters[1].Value = Convert.ToDouble(strQty.Trim());
objParameters[2].Value = Convert.ToInt32(strStatus);
objParameters[3].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_ScanningPicking_Initial", objParameters, "@RESULT", "@RESULT") != "")
{
//BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 2, ex.ToString(), strUserID);
return("0~" + objParameters[3].Value.ToString());
}
else
{
return("1~Error in Save.");
}
//BL_Common.dataTableToString(objSql.ExecuteProcedure_Table(SqlDataLayer.strLocal, "sp_ScanningPicking_Initial", objParameters));
//return "0~" + objParameters[3].Value.ToString();
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
public string BL_AuthenticateLogin(string strUsername, string strPassword)
{
SqlDataLayer objSql = new SqlDataLayer();
SqlParameter[] objParameters = new SqlParameter[3];
try
{
objParameters[0] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
objParameters[1] = new SqlParameter("@PASSWORD", SqlDbType.VarChar);
objParameters[2] = new SqlParameter("@RESULT", SqlDbType.VarChar, 100);
objParameters[0].Value = strUsername.Trim();
objParameters[1].Value = strPassword.Trim();
objParameters[2].Direction = ParameterDirection.Output;
if (objSql.ExecuteProcedureParam(SqlDataLayer.strLocal, "sp_Login", objParameters, "@RESULT", "@RESULT") != "")
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 2, "Login Succesfully", strUsername);
return("0~" + objParameters[2].Value.ToString());
}
else
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, "Login Failed", strUsername);
return("1~Invalid login details");
}
}
catch (Exception ex)
{
BL_Common.BL_Write(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, 0, ex.ToString(), strUsername);
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objParameters = null;
objSql = null;
}
}
