public List<CreditSearchResult> SearchCreditContractByCriteres(int pageNumber, string pQuery, out int count)
{
int startRow = 20*(pageNumber - 1) + 1;
int endRow = 20*pageNumber;
const string query = @"
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [user_id]) row, COUNT(1) OVER(PARTITION BY [user_id]) row_count, * FROM (
SELECT
Contracts.id,
Contracts.contract_code,
Contracts.status,
Contracts.start_date,
Contracts.align_disbursed_date,
Contracts.close_date,
Persons.identification_data as identification_data,
Credit.amount,
Credit.loanofficer_id,
Tiers.client_type_code,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(ISNULL(ISNULL(Groups.name, Persons.first_name + SPACE(1) + Persons.last_name), Corporates.name),'Error!') AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name,
Credit.[amount_min],
Credit.[amount_max],
Credit.[ir_min],
Credit.[ir_max],
Credit.[nmb_of_inst_min],
Credit.[nmb_of_inst_max],
Credit.[loan_cycle],
@user_id [user_id]
FROM Contracts
INNER JOIN Credit ON Contracts.id = Credit.id
INNER JOIN Projects ON Contracts.project_id = Projects.id
INNER JOIN Tiers ON Projects.tiers_id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id)
LEFT JOIN Users ON Users.id = Credit.loanofficer_id
LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id
UNION ALL
SELECT ISNULL(Contracts.id,0) AS id, ISNULL(Contracts.contract_code,'No contract') AS contract_code,
ISNULL(Contracts.status,0) AS status, ISNULL(Contracts.start_date,'01-01-1900') AS start_date,
ISNULL(Contracts.align_disbursed_date,'01-01-1900') AS align_disbursed_date,
ISNULL(Contracts.close_date,'01-01-1900') AS close_date,
Persons.identification_data AS identification_data, ISNULL(Credit.amount,0) AS amount,
ISNULL(Credit.loanofficer_id,0) AS loanofficer_id,
CASE (Tiers.client_type_code) WHEN 'I' THEN 'V' ELSE '-' END AS client_type_code,
ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS user_name,
Villages.name AS client_name,
ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS loanofficer_name,
Credit.[amount_min],
Credit.[amount_max],
Credit.[ir_min],
Credit.[ir_max],
Credit.[nmb_of_inst_min],
Credit.[nmb_of_inst_max],
Credit.[loan_cycle],
@user_id [user_id]
FROM Villages
INNER JOIN VillagesPersons ON VillagesPersons.village_id = Villages.id
INNER JOIN Persons ON Persons.id = VillagesPersons.person_id
INNER JOIN Tiers ON Persons.id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id)
INNER JOIN Projects ON Tiers.id = Projects.tiers_id
INNER JOIN Contracts ON Contracts.project_id = Projects.id
INNER JOIN Credit ON Credit.id = Contracts.id
LEFT JOIN Users ON Users.id = Credit.loanofficer_id
) R WHERE (ISNULL(contract_code, '') + ' ' + ISNULL(client_name, '') + ' ' + ISNULL([user_name], '') + ' ' + ISNULL(identification_data, '') + ' ' + ISNULL(loanofficer_name, '')) LIKE @criteria
) T WHERE row BETWEEN @startRow AND @endRow
";
count = 0;
var list = new List<CreditSearchResult>();
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand cmd = new OpenCbsCommand(query, conn))
{
cmd.AddParam("user_id", User.CurrentUser.Id);
cmd.AddParam("startRow", startRow);
cmd.AddParam("endRow", endRow);
cmd.AddParam("criteria", string.Format("%{0}%", pQuery));
using (OpenCbsReader reader = cmd.ExecuteReader())
while (reader.Read())
{
if (count == 0) count = reader.GetInt("row_count");
var result = new CreditSearchResult
{
Id = reader.GetInt("id"),
ContractCode = reader.GetString("contract_code"),
ClientType = reader.GetString("client_type_code"),
ClientName = reader.GetString("client_name"),
ContractStartDate = reader.GetDateTime("start_date").ToShortDateString(),
ContractEndDate = reader.GetDateTime("close_date").ToShortDateString(),
ContractStatus = ((OContractStatus)reader.GetSmallInt("status")).ToString(),
LoanOfficer = new User { Id = reader.GetInt("loanofficer_id") }
};
list.Add(result);
}
}
return list;
}
private void CheckContractAndDisplayIt(CreditSearchResult pCredit)
{
if (!_isSearchLoanContractForCompulsory)
{
try
{
ClientServices clientServices = ServicesProvider.GetInstance().GetClientServices();
// if client has no contract (i.e. in Village)
if (pCredit.Id == 0)
{
MessageBox.Show(@"This client in a village has no contract yet!");
}
else
{
IClient client = clientServices.FindTiersByContractId(pCredit.Id);
if (clientServices.CheckIfTiersIsValid(client))
{
var mainForm = (MainView) Application.OpenForms[0];
mainForm.InitializeCreditContractForm(client, pCredit.Id);
}
}
}
catch (Exception ex)
{
new frmShowError(CustomExceptionHandler.ShowExceptionText(ex)).ShowDialog();
}
}
else
{
groupBoxContractType.Enabled = true;
_isSearchSavingContractForTransfer = false;
SelectedLoanContract = pCredit;
DialogResult = DialogResult.OK;
}
}