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 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 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 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 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 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 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 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 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 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 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("0~" + BL_Common.dataTableToString(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
{
return("1~" + "Process card barcode not exist.");
}
}
catch (Exception ex)
{
return("1~" + objSql.getErrorMsg(ex.ToString()));
}
finally
{
objSql = null;
}
}
public DataTable GetmyProcessWIP(string strSerial_barcode, string strUsername)
{
SqlDataLayer objSql = new SqlDataLayer();
DataTable dt_Process = new DataTable();
DataTable dt_Final = new DataTable();
string strProcessCard = "";
try
{
strProcessCard = objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT PROCESSCARD FROM TBLWO_INWARD WHERE TRACKING_ID = '" + strSerial_barcode.Trim() + "'");
if (Convert.ToInt32(objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT CAST(COUNT(*) AS VARCHAR(50)) FROM TBLWO_INWARD WHERE PROCESSCARD = '" + strProcessCard.Trim() + "' AND TRACKING_ID = '" + strSerial_barcode.Trim() + "'")) == 0)
{
throw new Exception("Invalid Process card or serial number scanned.");
}
dt_Process = objSql.ExecuteDataset(SqlDataLayer.strLocal, "SELECT DISTINCT PROCESSSTAGE, AUTONUM FROM VW_PRODSTAGES WHERE BARCODE = '" + strProcessCard.Trim() + "' AND STATUS = 0 ORDER BY AUTONUM ASC").Tables[0]; //UID = '" + clsInit.GstrUserID.Trim() + "' AND
dt_Process.Columns.Add("Rework", typeof(string));
foreach (DataRow dr in dt_Process.Rows)
{
string strReworkFlag = string.Empty;
strReworkFlag = objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT CAST(RECNO AS VARCHAR(50)) FROM TBLREWORK WHERE WO_NO = '" + strProcessCard.Split('-').GetValue(0).ToString() + "' AND SER = '" + strSerial_barcode.Trim() + "' AND PROCESS = '" + dr["PROCESSSTAGE"].ToString() + "' and Stage_Ref = " + dr["AUTONUM"] + " AND ST = 0;");
if (strReworkFlag.Trim() == "")
{
dr["Rework"] = "0";
}
else
{
dr["Rework"] = strReworkFlag;;
}
}
dt_Final.Columns.Add("Ref", typeof(int));
dt_Final.Columns.Add("ProcessStage", typeof(string));
dt_Final.Columns.Add("Rework", typeof(string));
foreach (DataRow dr in dt_Process.Rows)
{
if (dr["Rework"].ToString() == "0")
{
if (Convert.ToInt32(objSql.ExecuteScalarString(SqlDataLayer.strLocal, "SELECT CAST(COUNT(RECNO) AS VARCHAR(50)) FROM TBLPRODUCTION WHERE BARCODE = '" + strProcessCard.Trim() + "' AND TRACKING_ID = '" + strSerial_barcode.Trim() + "' AND PROCESSDESC = '" + dr["PROCESSSTAGE"].ToString() + "' AND CRUSER = '******' AND ST = 2 and Stage_Ref = " + dr["AUTONUM"] + "")) == 0)
{
DataRow dr_Final = dt_Final.NewRow();
dr_Final["Ref"] = dr["AUTONUM"];
dr_Final["ProcessStage"] = dr["ProcessStage"];
dr_Final["Rework"] = dr["Rework"];
dt_Final.Rows.Add(dr_Final);
dt_Final.AcceptChanges();
}
}
else
{
DataRow dr_Final = dt_Final.NewRow();
dr_Final["Ref"] = dr["AUTONUM"];
dr_Final["ProcessStage"] = dr["ProcessStage"];
dr_Final["Rework"] = dr["Rework"];
dt_Final.Rows.Add(dr_Final);
dt_Final.AcceptChanges();
}
}
return(dt_Final);
}
catch (Exception ex)
{
//return "1~" + objSql.getErrorMsg(ex.ToString());
throw ex;
}
finally
{
objSql = null;
}
}
