public string FilterManulReview(Libraries.CommonLibrary.CBE.ViewTransactionCBE transaction)
{
//DateTime dtstartTime = transaction.StartTime;
DateTime dtstartTime = Convert.ToDateTime(transaction.StartDate);
string strstarttime = dtstartTime.ToString("dd/MM/yyyy HH:MM:ss");
//DateTime dtendTime = transaction.EndTime;
DateTime dtendTime = Convert.ToDateTime(transaction.EndDate);
string strendtime = dtendTime.ToString("dd/MM/yyyy HH:MM:ss");
string strQuery = " WHERE 1=1 ";
if (strstarttime != null && strendtime != null)
{
strQuery += " AND TRANSACTION_DATETIME BETWEEN TO_DATE('" + strstarttime + "','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('" + strendtime + "','DD/MM/YYYY HH24:MI:SS')";
}
else if (strstarttime != null)
{
strQuery += " AND TRANSACTION_DATETIME >= TO_DATE('" + strstarttime + "','DD/MM/YYYY HH24:MI:SS')";
}
else if (strendtime != null)
{
strQuery += " AND TRANSACTION_DATETIME <= TO_DATE('" + strendtime + "','DD/MM/YYYY HH24:MI:SS')";
}
if (transaction.GantryId > 0)
{
strQuery += " AND T.PLAZA_ID = " + transaction.GantryId;
}
if (transaction.VehicleClassId > 0)
{
strQuery += " AND (CV_CTP.VEHICLE_CLASS_ID = " + transaction.VehicleClassId + " OR NFPF.VEHICLE_CLASS_ID = " + transaction.VehicleClassId + " OR NFPR.VEHICLE_CLASS_ID = " + transaction.VehicleClassId + ")";
}
if (!String.IsNullOrEmpty(transaction.PlateNumber))
{
strQuery += " AND (CV_CTP.VEH_REG_NO = '" + transaction.PlateNumber + "' OR NFPF.PLATE_NUMBER = '" + transaction.PlateNumber + "' OR NFPR.PLATE_NUMBER = '" + transaction.PlateNumber + "')";
}
if (transaction.TransactionCategoryId == 0)
{
strQuery += " AND ((NVL(T.CT_ENTRY_ID, 0) = 0 AND (NVL(T.NF_ENTRY_ID_FRONT, 0) > 0 OR NVL(T.NF_ENTRY_ID_REAR, 0) > 0)) OR (NVL(T.CT_ENTRY_ID, 0) > 0 AND(NVL(T.NF_ENTRY_ID_FRONT, 0) = 0 AND NVL(T.NF_ENTRY_ID_REAR, 0) = 0)))";
}
else if (transaction.TransactionCategoryId == 1)
{
strQuery += "AND NVL(T.CT_ENTRY_ID,0) > 0 AND (NVL(T.NF_ENTRY_ID_FRONT,0) = 0 OR NVL(T.NF_ENTRY_ID_REAR,0) = 0)";
}
else if (transaction.TransactionCategoryId == 2)
{
strQuery += " AND NVL(T.CT_ENTRY_ID,0) = 0 AND (NVL(T.NF_ENTRY_ID_FRONT,0) > 0 OR NVL(T.NF_ENTRY_ID_REAR,0) > 0)";
}
else if (transaction.TransactionCategoryId == 3)
{
strQuery += " AND (NVL(CV_CTP.VEHICLE_CLASS_ID,0) <> NVL(NFPF.VEHICLE_CLASS_ID,0) AND ((NVL(CV_CTP.VEHICLE_CLASS_ID,0) <> NVL(NFPR.VEHICLE_CLASS_ID,0))))";
}
else if (transaction.TransactionCategoryId == 4)
{
strQuery += " AND NVL(T.AUDIT_STATUS,0)=1";
}
string Det = JsonConvert.SerializeObject(VaaaN.MLFF.Libraries.CommonLibrary.BLL.TransactionBLL.GetDataTableFilteredRecords(strQuery), Formatting.Indented);
return(Det.Replace("\r", "").Replace("\n", ""));
}
public ActionResult ShowTransaction(Libraries.CommonLibrary.CBE.ViewTransactionCBE transaction)
{
DateTime dtstartTime = Convert.ToDateTime(transaction.StartDate);
string strstarttime = dtstartTime.ToString("dd/MM/yyyy HH:mm:ss");
DateTime dtendTime = Convert.ToDateTime(transaction.EndDate);
string strendtime = dtendTime.ToString("dd/MM/yyyy HH:mm:ss");
//string strstarttime = transaction.StartDate;
//string strendtime = transaction.EndDate;
string strQuery = " WHERE 1=1 ";
if (strstarttime != null && strendtime != null)
{
strQuery += " AND TRANSACTION_DATETIME BETWEEN TO_DATE('" + strstarttime + "','DD/MM/YYYY HH24:MI:SS') AND TO_DATE('" + strendtime + "','DD/MM/YYYY HH24:MI:SS')";
}
else if (strstarttime != null)
{
strQuery += " AND TRANSACTION_DATETIME >= TO_DATE('" + strstarttime + "','DD/MM/YYYY HH24:MI:SS')";
}
else if (strendtime != null)
{
strQuery += " AND TRANSACTION_DATETIME <= TO_DATE('" + strendtime + "','DD/MM/YYYY HH24:MI:SS')";
}
if (transaction.GantryId > 0)
{
strQuery += " AND T.PLAZA_ID = " + transaction.GantryId;
}
if (transaction.VehicleClassId > 0)
{
strQuery += " AND (CV_CTP.VEHICLE_CLASS_ID = " + transaction.VehicleClassId + " OR NFPF.VEHICLE_CLASS_ID = " + transaction.VehicleClassId + " OR NFPR.VEHICLE_CLASS_ID = " + transaction.VehicleClassId + ")";
}
if (!String.IsNullOrEmpty(transaction.PlateNumber))
{
strQuery += " AND (CV_CTP.VEH_REG_NO = '" + transaction.PlateNumber + "' OR NFPF.PLATE_NUMBER = '" + transaction.PlateNumber + "' OR NFPR.PLATE_NUMBER = '" + transaction.PlateNumber + "')";
}
if (transaction.TransactionCategoryId == 0)
{
strQuery += " AND NVL(IS_BALANCE_UPDATED,0) <> 1";
}
else if (transaction.TransactionCategoryId == 1)// Only IKE
{
strQuery += " AND NVL(T.CT_ENTRY_ID,0) > 0 AND NVL(IS_BALANCE_UPDATED,0) <> 1";
}
else if (transaction.TransactionCategoryId == 2)//IKE + FRONT ALPR
{
strQuery += " AND NVL(T.CT_ENTRY_ID,0) > 0 AND NVL(T.NF_ENTRY_ID_FRONT,0) > 0 AND LOWER(NFPF.PLATE_NUMBER)<>'unidentified' AND NVL(T.NF_ENTRY_ID_REAR,0) = 0 AND NVL(IS_BALANCE_UPDATED,0) <> 1 ";
}
else if (transaction.TransactionCategoryId == 3)//IKE + REAR ALPR
{
strQuery += " AND NVL(T.CT_ENTRY_ID,0) > 0 AND NVL(T.NF_ENTRY_ID_FRONT,0) = 0 AND NVL(T.NF_ENTRY_ID_REAR,0) > 0 AND LOWER(NFPR.PLATE_NUMBER)<>'unidentified' AND NVL(IS_BALANCE_UPDATED,0) <> 1 ";
}
else if (transaction.TransactionCategoryId == 4)//FRONT ALPR
{
strQuery += " AND NVL(T.CT_ENTRY_ID,0) = 0 AND NVL(T.NF_ENTRY_ID_FRONT,0) > 0 AND LOWER(NFPF.PLATE_NUMBER)<>'unidentified' AND NVL(T.NF_ENTRY_ID_REAR,0) = 0 AND NVL(IS_BALANCE_UPDATED,0) <> 1 ";
}
else if (transaction.TransactionCategoryId == 5)//REAR ALPR'unidentified'
{
strQuery += " AND NVL(T.CT_ENTRY_ID,0) = 0 AND NVL(T.NF_ENTRY_ID_FRONT,0) = 0 AND NVL(T.NF_ENTRY_ID_REAR,0) > 0 AND LOWER(NFPR.PLATE_NUMBER)<>'unidentified' AND NVL(IS_BALANCE_UPDATED,0) <> 1 ";
}
else if (transaction.TransactionCategoryId == 6)//FRONT ALPR + REAR ALPR
{
strQuery += " AND NVL(T.CT_ENTRY_ID,0) = 0 AND NVL(T.NF_ENTRY_ID_FRONT,0) > 0 AND LOWER(NFPF.PLATE_NUMBER)<>'unidentified' AND NVL(T.NF_ENTRY_ID_REAR,0) > 0 AND LOWER(NFPR.PLATE_NUMBER)<>'unidentified' AND NVL(IS_BALANCE_UPDATED,0) <> 1 ";
}
else if (transaction.TransactionCategoryId == 7)//UNIDENTIFIED FRONT ALPR
{
strQuery += " AND LOWER(NFPF.PLATE_NUMBER)='unidentified' AND NVL(T.CT_ENTRY_ID,0) = 0 AND NVL(T.NF_ENTRY_ID_FRONT,0) > 0 AND NVL(T.NF_ENTRY_ID_REAR,0) = 0 AND NVL(IS_BALANCE_UPDATED,0) <> 1 ";
}
else if (transaction.TransactionCategoryId == 8)//UNIDENTIFIED REAR ALPR
{
strQuery += " AND LOWER(NFPR.PLATE_NUMBER)='unidentified' AND NVL(T.CT_ENTRY_ID,0) = 0 AND NVL(T.NF_ENTRY_ID_FRONT,0) = 0 AND NVL(T.NF_ENTRY_ID_REAR,0) > 0 AND NVL(IS_BALANCE_UPDATED,0) <> 1 ";
}
DataTable dt = new DataTable();
dt = VaaaN.MLFF.Libraries.CommonLibrary.BLL.TransactionBLL.GetDataTableFilteredRecords(strQuery);
ViewData["apiPath"] = HelperClass.GetAPIUrl();
return(PartialView("_ManualTransactions", dt));
}
