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)); }