public List<SearchModel> SearchByDisconnected()
{
EstablishConnection();
OracleCommand command = new OracleCommand("SELECT f.customerid,f.firstname,f.lastname,f.customerstatus,f.zipcode,f.email,f.contactnumber,d.due_amount,((SELECT SYSDATE FROM DUAL) - d.days_elapsed) AS payment_date FROM fincustomerdata f, dlqtable d WHERE d.account_number= f.customerid AND d.flag=0", conn);
OracleDataReader reader = command.ExecuteReader();
List<SearchModel> list = new List<SearchModel>();
while (reader.Read())
{
SearchModel temp = new SearchModel();
temp.customerId = reader["customerid"].ToString();
temp.firstName = reader["firstname"].ToString();
temp.lastName = reader["lastname"].ToString();
temp.consumerStatus = (reader["customerstatus"]).ToString();
temp.zipCode = reader["zipcode"].ToString();
temp.email = reader["email"].ToString();
temp.contactNumber = (reader["contactnumber"]).ToString();
temp.dueAmount = reader["due_amount"].ToString();
temp.paymentDate = reader["payment_date"].ToString();
temp.paymentDate = Regex.Replace(temp.paymentDate, "12:00:00 AM", "");
list.Add(temp);
}
conn.Close();
return list;
}
public List<SearchModel> Search(string name, string zipCode)
{
EstablishConnection();
OracleCommand command = new OracleCommand();
command.Connection = conn;
if (!name.Equals("") && zipCode.Equals(""))
{
command.CommandText = "SELECT f.customerid,f.firstname,f.lastname,f.customerstatus,f.zipcode,f.email,f.contactnumber,d.due_amount,((SELECT SYSDATE FROM DUAL) - d.days_elapsed) AS payment_date FROM fincustomerdata f, dlqtable d WHERE f.customerid = d.account_number AND f.firstname like '%' || :name || '%' ";
command.Parameters.Add(":name", name);
}
else if (name.Equals("") && (!zipCode.Equals("")))
{
command.CommandText = "SELECT f.customerid,f.firstname,f.lastname,f.customerstatus,f.zipcode,f.email,f.contactnumber,d.due_amount,((SELECT SYSDATE FROM DUAL) - d.days_elapsed) AS payment_date FROM fincustomerdata f, dlqtable d WHERE f.customerid = d.account_number AND f.zipCode = :zip";
command.Parameters.Add(":zip", zipCode);
}
else if (!name.Equals("") && (!zipCode.Equals("")))
{
command.CommandText = "SELECT f.customerid,f.firstname,f.lastname,f.customerstatus,f.zipcode,f.email,f.contactnumber,d.due_amount,((SELECT SYSDATE FROM DUAL) - d.days_elapsed) AS payment_date FROM fincustomerdata f, dlqtable d WHERE f.customerid = d.account_number AND f.firstname like '%' || :name1 || '%' AND f.zipcode = :zip1";
command.Parameters.Add(":name1", name);
command.Parameters.Add(":zip1", zipCode);
}
//command.Connection = conn;
List<SearchModel> list = new List<SearchModel>();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
SearchModel temp = new SearchModel();
temp.customerId = reader["customerid"].ToString();
temp.firstName = reader["firstname"].ToString();
temp.lastName = reader["lastname"].ToString();
temp.consumerStatus = (reader["customerstatus"]).ToString();
temp.zipCode = reader["zipcode"].ToString();
temp.email = reader["email"].ToString();
temp.contactNumber = (reader["contactnumber"]).ToString();
temp.dueAmount = reader["due_amount"].ToString();
temp.paymentDate = reader["payment_date"].ToString();
temp.paymentDate = Regex.Replace(temp.paymentDate, "12:00:00 AM", "");
list.Add(temp);
}
conn.Close();
return list;
}