public List<SavingSearchResult> SearchSavingContractByCritere(int pPageNumber, string pQuery, bool all, bool activeContractOnly)
{
List<SavingSearchResult> list = new List<SavingSearchResult>();
string sql = @"SELECT
SavingContracts.id,
SavingContracts.code AS contract_code,
SavingContracts.status AS contract_status,
SavingContracts.creation_date as start_date,
SavingContracts.closed_date as end_date,
SavingProducts.product_type as product_type,
Persons.identification_data as identification_data,
Tiers.client_type_code,
Tiers.id AS client_id,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
ISNULL(Persons.first_name + SPACE(1) + Persons.last_name, ISNULL(Groups.name, Corporates.name)) AS client_name,
ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name,
SavingContracts.user_id AS loan_officer_id,
SavingProducts.currency_id
FROM SavingContracts
INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
INNER JOIN Users ON SavingContracts.user_id = Users.id
INNER JOIN Tiers ON SavingContracts.tiers_id = Tiers.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";
sql += " WHERE 1=1 ";
if (activeContractOnly)
sql += " AND SavingContracts.status=1 ";
if (!all)
{
sql += @" AND Tiers.branch_id in (
select branch_id
from dbo.UsersBranches
WHERE user_id = @user_id
)";
}
sql += ") maTable";
const string closeWhere = @" WHERE ( contract_code LIKE @contractCode
OR client_name LIKE @clientName
OR user_name LIKE @UserName
OR identification_data LIKE @numberPassport
OR loanofficer_name LIKE @loanofficerName )) maTable";
QueryEntity q = new QueryEntity(pQuery, sql, closeWhere);
string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pPageNumber - 1) * 20);
using (SqlConnection conn = GetConnection())
using (OpenCbsCommand select = new OpenCbsCommand(pSqlText, conn))
{
foreach (var item in q.DynamiqParameters())
{
select.AddParam(item.Key, string.Format("%{0}%", item.Value));
}
select.AddParam("@user_id", User.CurrentUser.Id);
using (OpenCbsReader reader = select.ExecuteReader())
{
if (!reader.Empty)
{
while (reader.Read())
{
SavingSearchResult result =
new SavingSearchResult
{
Id = reader.GetInt("id"),
ClientId = reader.GetInt("client_id"),
ContractCode = reader.GetString("contract_code"),
Status = (OSavingsStatus)reader.GetSmallInt("contract_status"),
ClientTypeCode = reader.GetString("client_type_code")
};
switch (result.ClientTypeCode)
{
case "I": result.ClientType = OClientTypes.Person; break;
case "G": result.ClientType = OClientTypes.Group; break;
case "V": result.ClientType = OClientTypes.Village; break;
case "C": result.ClientType = OClientTypes.Corporate; break;
default: result.ClientType = OClientTypes.Person; break;
}
result.ClientName = reader.GetString("client_name");
result.ContractStartDate = reader.GetDateTime("start_date");
result.ContractEndDate = reader.GetNullDateTime("end_date");
result.ContractType = reader.GetString("product_type");
result.LoanOfficer = new User { Id = reader.GetInt("loan_officer_id") };
result.CurrencyId = reader.GetInt("currency_id");
list.Add(result);
}
}
}
return list;
}
}
public DialogResult SearchContracts(string pClient)
{
Visible = false;
_isSearchLoanContractForCompulsory = true;
_isSearchSavingContractForTransfer = true;
SelectedLoanContract = null;
SelectedSavingContract = null;
radioButtonCreditContract.Checked = true;
ReInitializeValues();
ReInitializeSearchParameters();
textBoxQuery.Text = pClient;
return ShowDialog();
}
private void CheckContractAndDisplayIt(SavingSearchResult saving)
{
if (!_isSearchSavingContractForTransfer)
{
try
{
ClientServices clientServices = ServicesProvider.GetInstance().GetClientServices();
IClient client = ServicesProvider.GetInstance().GetClientServices().FindTiers(saving.ClientId, saving.ClientType);
if (clientServices.CheckIfTiersIsValid(client))
{
var mainForm = (MainView)Application.OpenForms[0];
mainForm.InitializeSavingContractForm(client, saving.Id);
}
}
catch (Exception ex)
{
new frmShowError(CustomExceptionHandler.ShowExceptionText(ex)).ShowDialog();
}
}
else
{
groupBoxContractType.Enabled = true;
_isSearchSavingContractForTransfer = false;
SelectedSavingContract = saving;
DialogResult = DialogResult.OK;
}
}
public void CheckIfTransferAccountHasWrongCurrency(ISavingsContract currentContract, SavingSearchResult transferAccount)
{
if (currentContract.Product.Currency.Id != transferAccount.CurrencyId)
throw new OpenCbsSavingException(OpenCbsSavingExceptionEnum.TransferAccountHasWrongCurrency);
}