public static string GetQueryForAcceptedClosureReport(ViewModelClass.ClosureReportFilter filter)
{
string query = "Select * from tbl_IssuingIncomingVISA where ";
int qrylength = query.Length;
if (filter.ROLCaseNumber != null && !string.IsNullOrEmpty(filter.ROLCaseNumber.Trim()))
{
query = query + "ROLCaseNumber='" + filter.ROLCaseNumber.Trim() + "'";
}
if (filter.FinancialCPD != null && !string.IsNullOrEmpty(filter.FinancialCPD.Trim()))
{
query = query + ((query.Length > qrylength + 3) ? " and FinancialCPD='" + filter.FinancialCPD.Trim() + "'" : " CIFNo='" + filter.FinancialCPD.Trim() + "'");
}
if (filter.Status != null && !string.IsNullOrEmpty(filter.Status.Trim()) && !filter.Status.Trim().Equals("0"))
{
query = query + ((query.Length > qrylength + 3) ? " and CaseSubmissionStatus='" + filter.Status.Trim() + "'" : " CaseSubmissionStatus='" + filter.Status.Trim() + "'");
}
if (filter.FromDate != null && (filter.FromDate != new DateTime(0001, 01, 01)))
{
query = query + ((query.Length > qrylength + 3) ? " and BotEntryTime >= '" + filter.FromDate.ToString("MM-dd-yyyy") + "'" : " BotEntryTime >= '" + filter.FromDate.ToString("MM-dd-yyyy") + "'");
}
if (filter.ToDate != null && (filter.ToDate != new DateTime(0001, 01, 01)))
{
query = query + ((query.Length > qrylength + 3) ? " and BotEntryTime >= '" + filter.ToDate.ToString("MM-dd-yyyy") + "'" : " BotEntryTime >= '" + filter.ToDate.ToString("MM-dd-yyyy") + "'");
}
if (qrylength == query.Length)
{
query = "select * from tbl_IssuingIncomingVISA where CaseSubmissionStatus In ('open','close')";
}
return(query + ";");
}
public static List <tbl_IssuingIncomingVISA> AcceptedCaseClosureReportFilter(ViewModelClass.ClosureReportFilter filter)
{
List <tbl_IssuingIncomingVISA> list = new List <tbl_IssuingIncomingVISA>();
try
{
CBDB db = new CBDB();
list = db.tbl_IssuingIncomingVISA.SqlQuery(GetQueryForAcceptedClosureReport(filter)).ToList();
}
catch (Exception ex) { throw ex; }
return(list);
}