public List<SearchCustomerViewModel> GetLockedCustomers()
{
List<SearchCustomerViewModel> customersList = new List<SearchCustomerViewModel>();
long customerID;
CustomerDAL customerDALObj = new CustomerDAL();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()), connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()), connection3 = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
connection.Open();
connection2.Open();
connection3.Open();
SqlCommand command = new SqlCommand(String.Format("SELECT UserID FROM UserRoles WHERE Status = 'L' "), connection);
SqlDataReader reader = command.ExecuteReader(), reader2, reader3;
SqlCommand command2 = new SqlCommand("", connection2);
SqlCommand command3 = new SqlCommand("", connection3);
if (!reader.HasRows)
return null;
while (reader.Read())
{
var userID = reader[0].ToString();
customerID = customerDALObj.GetCustomerIDbyUserID(userID);
command2.CommandText = String.Format("SELECT CustomerName, PermanentAddress, CommunicationAddress, ContactNumber, Email FROM Customer WHERE CustomerID = {0} ", customerID.ToString());
reader2 = command2.ExecuteReader();
reader2.Read();
string customerName = reader2[0].ToString();
string permanentAddress = reader2[1].ToString();
string communicationAddress = reader2[2].ToString();
string contactNumber = reader2[3].ToString();
string email = reader2[4].ToString();
reader2.Close();
command3.CommandText = "SELECT AccountNumber FROM Account WHERE CustomerID = " + customerID.ToString();
reader3 = command3.ExecuteReader();
if (reader3.HasRows)
while (reader3.Read())
{
SearchCustomerViewModel customerDetails = new SearchCustomerViewModel();
customerDetails.accountNumber = reader3[0].ToString();
customerDetails.customerName = String.Copy(customerName);
customerDetails.permanentAddress = String.Copy(permanentAddress);
customerDetails.communicationAddress = String.Copy(communicationAddress);
customerDetails.contactNumber = String.Copy(contactNumber);
customerDetails.email = String.Copy(email);
customersList.Add(customerDetails);
}
reader3.Close();
}
reader.Close();
}
return customersList;
}
public List<SearchCustomerViewModel> GetCustomerByName(string customerName, long branchCode)
{
List<SearchCustomerViewModel> customersList = new List<SearchCustomerViewModel>();
long customerID;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()), connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
connection.Open();
connection2.Open();
SqlCommand command = new SqlCommand(String.Format("SELECT CustomerID, CustomerName, PermanentAddress, CommunicationAddress, ContactNumber, Email FROM Customer WHERE CustomerName LIKE '%{0}%' ", customerName), connection);
SqlDataReader reader = command.ExecuteReader(), reader2;
SqlCommand command2 = new SqlCommand("", connection2);
if (!reader.HasRows)
return null;
while (reader.Read())
{
customerID = Convert.ToInt64(reader[0]);
string actualCustomerName = reader[1].ToString();
string permanentAddress = reader[2].ToString();
string communicationAddress = reader[3].ToString();
string contactNumber = reader[4].ToString();
string email = reader[5].ToString();
command2.CommandText = String.Format("SELECT AccountNumber FROM Account WHERE CustomerID = {0} AND BranchCode = {1} AND Status = 'Active' ",customerID.ToString(), branchCode.ToString());
reader2 = command2.ExecuteReader();
if(reader2.HasRows)
while (reader2.Read())
{
SearchCustomerViewModel customerDetails = new SearchCustomerViewModel();
customerDetails.accountNumber = reader2[0].ToString();
customerDetails.customerName = String.Copy(actualCustomerName);
customerDetails.permanentAddress = String.Copy(permanentAddress);
customerDetails.communicationAddress = String.Copy(communicationAddress);
customerDetails.contactNumber = String.Copy(contactNumber);
customerDetails.email = String.Copy(email);
customersList.Add(customerDetails);
}
reader2.Close();
}
}
return customersList;
}
public List<SearchCustomerViewModel> GetCustomersHavingLoan(long branchCode)
{
List<SearchCustomerViewModel> customersList = new List<SearchCustomerViewModel>();
long customerID, accountNumber ;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()), connection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
connection.Open();
connection2.Open();
SqlCommand command = new SqlCommand(String.Format("SELECT CustomerID, AccountNumber FROM Account WHERE BranchCode = {0} AND Status = 'Active' AND AccountType = 'L' ", branchCode.ToString()), connection);
SqlDataReader reader = command.ExecuteReader(), reader2;
SqlCommand command2 = new SqlCommand("", connection2);
if (!reader.HasRows)
return null;
while (reader.Read())
{
customerID = Convert.ToInt64(reader[0]);
accountNumber = Convert.ToInt64(reader[1]);
command2.CommandText = String.Format("SELECT CustomerName, PermanentAddress, CommunicationAddress, ContactNumber, Email FROM Customer WHERE CustomerID = {0} ", customerID.ToString());
reader2 = command2.ExecuteReader();
if (reader2.HasRows)
{
SearchCustomerViewModel customerDetails = new SearchCustomerViewModel();
reader2.Read();
customerDetails.accountNumber = accountNumber.ToString();
customerDetails.customerName = reader2[0].ToString();
customerDetails.permanentAddress = reader2[1].ToString();
customerDetails.communicationAddress = reader2[2].ToString();
customerDetails.contactNumber = reader2[3].ToString();
customerDetails.email = reader2[4].ToString();
customersList.Add(customerDetails);
}
reader2.Close();
}
}
return customersList;
}
public List<SearchCustomerViewModel> GetCustomerByAccountNumber(long accountNumber, long branchCode)
{
List<SearchCustomerViewModel> customerList = new List<SearchCustomerViewModel>();
SearchCustomerViewModel customerDetails = new SearchCustomerViewModel();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString()))
{
connection.Open();
SqlCommand command = new SqlCommand(String.Format("SELECT CustomerID FROM Account WHERE AccountNumber = {0} AND BranchCode = {1} AND Status = 'Active' ", accountNumber.ToString(), branchCode.ToString()), connection);
SqlDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
return null;
reader.Read();
var customerID = Convert.ToInt64(reader[0]);
reader.Close();
command.CommandText = String.Format("SELECT CustomerName, PermanentAddress, CommunicationAddress, ContactNumber, Email FROM Customer WHERE CustomerID = {0} ", customerID.ToString()) ;
reader = command.ExecuteReader();
reader.Read();
customerDetails.accountNumber = accountNumber.ToString();
customerDetails.customerName = reader[0].ToString();
customerDetails.permanentAddress = reader[1].ToString();
customerDetails.communicationAddress = reader[2].ToString();
customerDetails.contactNumber = reader[3].ToString();
customerDetails.email = reader[4].ToString();
reader.Close();
}
customerList.Add(customerDetails);
return customerList;
}