public PaginationResult <IList <FondMedium> > GetFondMedium(int fondId, string searchTerms, IEnumerable <int> index, List <Tuple <int, string, TypeColonneFond> > informationsFilters,
PaginationRequest paginationRequest)
{
ResultAndCountModel result;
try
{
if (Context.Database.Connection.State != ConnectionState.Open)
{
Context.Database.Connection.Open();
}
var cmd = Context.Database.Connection.CreateCommand();
// CREATION PARAMETRES
DataTable dtIndex = new DataTable();
dtIndex.Columns.Add("Id", typeof(int));
index.ToList().ForEach(b => dtIndex.Rows.Add(b));
DataTable dtFondInformationValue = new DataTable();
dtFondInformationValue.Columns.Add("Id", typeof(int));
dtFondInformationValue.Columns.Add("Value", typeof(string));
informationsFilters.ToList().ForEach(b => dtFondInformationValue.Rows.Add(b.Item1, GetValue(b.Item2, b.Item3)));
DataTable dtWordSearch = new DataTable();
dtWordSearch.Columns.Add("Libelle", typeof(string));
dtWordSearch.Columns.Add("WordBoundary", typeof(bool));
_wordSearchParser.Parse(searchTerms).ToList().ForEach(b => dtWordSearch.Rows.Add(b.Val, b.WordBoundary));
SqlParameter searchTermsParams = new SqlParameter("@searchTerm", SqlDbType.Structured)
{
Value = dtWordSearch
};
SqlParameter fondIdParams = new SqlParameter("@fondId", SqlDbType.Int)
{
Value = fondId
};
SqlParameter indexPraram = new SqlParameter("@index", SqlDbType.Structured)
{
Value = dtIndex
};
SqlParameter fondInformationValueParam = new SqlParameter("@FondInformationValues", SqlDbType.Structured)
{
Value = dtFondInformationValue
};
SqlParameter pageNumberParams = new SqlParameter("@pageNumber", SqlDbType.Int)
{
Value = paginationRequest.PageNumber
};
SqlParameter pageSizeParams = new SqlParameter("@pageSize", SqlDbType.Int)
{
Value = paginationRequest.PageSize
};
cmd.CommandText = "dbo.GetMediaFond";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(searchTermsParams);
cmd.Parameters.Add(fondIdParams);
cmd.Parameters.Add(indexPraram);
cmd.Parameters.Add(fondInformationValueParam);
cmd.Parameters.Add(pageNumberParams);
cmd.Parameters.Add(pageSizeParams);
result = SqlDataReaderHelper.ExecuteCommand(cmd);
}
finally
{
if (Context.Database.Connection.State == ConnectionState.Open)
{
Context.Database.Connection.Close();
}
}
var fondMediums = this.Get(b => result.Ids.Contains(b.Id),
b => b.OrderByDescending(o => o.Titre.Contains(searchTerms)), b => b.ColonneFondMedium,
b => b.Fond.InformationAffichageFonds,
b => b.ColonneFondMedium.Select(col => col.InformationFond),
b => b.Tags.Select(t => t.Tag));
return(new PaginationResult <IList <FondMedium> >(result.Count, fondMediums));
}
public PaginationResult <IList <Pretre> > GetPretreByLieu(int?typeLieuId, int?lieuId, int?fonctionId, string nomLieu,
int?contextHistoriqueId, int?anneeExercice, TriModel tri, PaginationRequest paginationRequest)
{
ResultAndCountModel result;
try
{
if (Context.Database.Connection.State != ConnectionState.Open)
{
Context.Database.Connection.Open();
}
var cmd = Context.Database.Connection.CreateCommand();
SqlParameter stypeLieuIdParams = new SqlParameter("@typeLieuId", SqlDbType.Int)
{
Value = typeLieuId
};
SqlParameter lieuIdParams = new SqlParameter("@lieuId", SqlDbType.Int)
{
Value = lieuId
};
SqlParameter fonctionIdPraram = new SqlParameter("@fonctionId", SqlDbType.Int)
{
Value = fonctionId
};
SqlParameter nomLieuParam = new SqlParameter("@nomLieu", SqlDbType.NVarChar)
{
Value = nomLieu
};
SqlParameter contextHistoriqueParam = new SqlParameter("@contextHistoriqueId", SqlDbType.Int)
{
Value = contextHistoriqueId
};
SqlParameter anneeExerciceParam = new SqlParameter("@anneeExercice", SqlDbType.Int)
{
Value = anneeExercice
};
SqlParameter pageNumberParams = new SqlParameter("@pageNumber", SqlDbType.Int)
{
Value = paginationRequest.PageNumber
};
SqlParameter pageSizeParams = new SqlParameter("@pageSize", SqlDbType.Int)
{
Value = paginationRequest.PageSize
};
SqlParameter triParams = new SqlParameter("@tri", SqlDbType.Int)
{
Value = tri.Key
};
cmd.CommandText = "dbo.GetPretreFonctionLieu";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(stypeLieuIdParams);
cmd.Parameters.Add(lieuIdParams);
cmd.Parameters.Add(fonctionIdPraram);
cmd.Parameters.Add(contextHistoriqueParam);
cmd.Parameters.Add(nomLieuParam);
cmd.Parameters.Add(anneeExerciceParam);
cmd.Parameters.Add(triParams);
cmd.Parameters.Add(pageNumberParams);
cmd.Parameters.Add(pageSizeParams);
result = SqlDataReaderHelper.ExecuteCommand(cmd);
}
finally
{
if (Context.Database.Connection.State == ConnectionState.Open)
{
Context.Database.Connection.Close();
}
}
var pretres = this.Get(b => result.Ids.Contains(b.Id), null, b => b.Photos,
b => b.Documents,
b => b.ArticlesRevue,
b => b.FonctionsLieu.Select(fl => fl.Fonction),
b => b.FonctionsLieu.Select(fl => fl.Lieu)).OrderByExtList <Pretre, int>(result.Ids.ToList(), b => b.Id);
return(new PaginationResult <IList <Pretre> >(result.Count, pretres));
}