public List<ProjetSearchResult> SelectProjectByCriteres(int pageNumber, string pQuery)
{
List<ProjetSearchResult> list = new List<ProjetSearchResult>();
string SELECT_FROM_PROJET_ = @" SELECT DISTINCT pro.id,pro.code,pro.name as name_project,pro.aim,pers.first_name,
pers.last_name,tie.client_type_code,tie.id as tiers_id,corp.name as companyName
FROM (Projects as pro
INNER JOIN Tiers tie on pro.tiers_id=tie.id )
LEFT JOIN Corporates corp on corp.id=tie.id
LEFT JOIN Persons pers on pers.id=tie.id ) maTable";
string CloseWhere = @" WHERE ( companyName LIKE @companyName OR code LIKE @code OR name_project LIKE @nameProject OR aim LIKE @aim OR last_name LIKE @lastName OR first_name LIKE @firtName )) maTable";
QueryEntity q = new QueryEntity(pQuery, SELECT_FROM_PROJET_, CloseWhere);
string pSqlText = q.ConstructSQLEntityByCriteresProxy(20, (pageNumber - 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));
}
using (OpenCbsReader reader = select.ExecuteReader())
{
while (reader.Read())
{
ProjetSearchResult resultat = new ProjetSearchResult();
resultat.Id = reader.GetInt("id");
resultat.Code = reader.GetString("code");
resultat.ProjectName = reader.GetString("name_project");
resultat.CompanyName = reader.GetString("companyName");
resultat.Aim = reader.GetString("aim");
resultat.TiersId = reader.GetInt("tiers_id");
resultat.Status = reader.GetChar("client_type_code");
resultat.LastName = reader.GetString("last_name");
resultat.FirstName = reader.GetString("first_name");
//resultat.ContractCode = reader.GetString("contract_code");
list.Add(resultat);
}
}
}
return list;
}
private void _CheckTiersAndDisplayIt(ProjetSearchResult result)
{
try
{
_client = ServicesProvider.GetInstance().GetClientServices().FindTiers(result.TiersId, result.Type);
_projet = ServicesProvider.GetInstance().GetProjectServices().FindProjectById(result.Id);
_projet.Client = _client;
if (result.Type == OClientTypes.Person)
((LotrasmicMainWindowForm)_mdiForm).InitializePersonForm((Person)_client,_projet);
if (result.Type == OClientTypes.Group)
((LotrasmicMainWindowForm)_mdiForm).InitializeGroupForm((Group)_client,_projet);
if (result.Type == OClientTypes.Corporate)
((LotrasmicMainWindowForm)_mdiForm).InitializeCorporateForm((Corporate)_client,_projet);
Close();
}
catch (Exception ex)
{
new frmShowError(CustomExceptionHandler.ShowExceptionText(ex)).ShowDialog();
}
}