public IList GetMailQueryResult(Int32[] ids, Int32?idlingua, bool countOnly = false, ContentItem advItem = null) // idcontent) {
// dynamic content = _orchardServices.ContentManager.Get(idcontent);
// content = _orchardServices.ContentManager.Get(idcontent, VersionOptions.DraftRequired);
{
IHqlQuery query;
if (ids != null && ids.Count() > 0)
{
//if (content.QueryPickerPart != null && content.QueryPickerPart.Ids.Length > 0) {
Dictionary <string, object> tokens = new Dictionary <string, object>();
if (advItem != null)
{
tokens.Add("Content", advItem);
}
query = IntegrateAdditionalConditions(_queryPickerServices.GetCombinedContentQuery(ids, tokens, new string[] { "CommunicationContact" }), idlingua);
}
else
{
query = IntegrateAdditionalConditions(null, idlingua);
}
// Trasformo in stringa HQL
var stringHQL = ((DefaultHqlQuery)query).ToHql(false);
// Rimuovo la Order by per poter fare la query annidata
// TODO: trovare un modo migliore per rimuovere la order by
stringHQL = stringHQL.ToString().Replace("order by civ.Id", "");
string queryForEmail = "";
if (countOnly)
{
queryForEmail = "SELECT count(EmailRecord) as Tot";
}
else
{
queryForEmail = "SELECT cir.Id as Id, TitlePart.Title as Title, EmailRecord.Email as EmailAddress";
}
queryForEmail += " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord as civr join " +
"civr.ContentItemRecord as cir join " +
"civr.TitlePartRecord as TitlePart join " +
"cir.EmailContactPartRecord as EmailPart join " +
"EmailPart.EmailRecord as EmailRecord " +
"WHERE civr.Published=1 AND EmailRecord.Validated AND EmailRecord.AccettatoUsoCommerciale AND civr.Id in (" + stringHQL + ")";
var fullStatement = _transactionManager.GetSession()
.CreateQuery(queryForEmail)
.SetCacheable(false);
IList lista = fullStatement
.SetResultTransformer(Transformers.AliasToEntityMap)
.List();
return(lista);
}
protected override DriverResult Editor(QueryPickerPart part, IUpdateModel updater, dynamic shapeHelper)
{
var settings = part.Settings.GetModel <QueryPickerPartSettings>();
IEnumerable <KeyValuePair <int, string> > queryList;
IEnumerable <KeyValuePair <int, string> > oneShotList;
if (settings.IsForHqlQueries)
{
queryList = _queryPickerService.GetHqlQueries().Select(x =>
new KeyValuePair <int, string>(x.Id, ((dynamic)x).TitlePart.Title));
oneShotList = new List <KeyValuePair <int, string> >();
}
else
{
queryList = _queryPickerService.GetUserDefinedQueries().Select(x =>
new KeyValuePair <int, string>(x.Id, ((dynamic)x).TitlePart.Title));
oneShotList = _queryPickerService.GetOneShotQueries().Select(x =>
new KeyValuePair <int, string>(x.Id, ((dynamic)x).TitlePart.Title));
}
var model = new QueryPickerVM {
SelectedIds = part.Ids,
AvailableQueries = queryList,
OneShotQueries = oneShotList
};
if (updater != null && updater.TryUpdateModel(model, Prefix, null, null))
{
if (HttpContext.Current.Request.Form[Prefix + ".SelectedIds"] == null)
{
part.Ids = new int[] {};
}
else
{
part.Ids = model.SelectedIds;
}
}
var resultRecordNumber = 0;
if (!settings.IsForHqlQueries)
{
// TODO: rendere dinamico e injettabile l'array dei contenttypes
var combinedQueries = _queryPickerService.GetCombinedContentQuery(model.SelectedIds, null, null);
resultRecordNumber = combinedQueries.Count();
model.TotalItemsCount = resultRecordNumber;
}
return(ContentShape("Parts_QueryPicker_Edit",
() => shapeHelper.EditorTemplate(TemplateName: "Parts/QueryPicker_Edit",
Model: model,
Prefix: Prefix)));
}
public IList GetSmsQueryResult(Int32[] ids, Int32?idlingua, bool countOnly = false, ContentItem advItem = null)
{
IHqlQuery query;
if (ids != null && ids.Length > 0)
{
Dictionary <string, object> tokens = new Dictionary <string, object>();
if (advItem != null)
{
tokens.Add("Content", advItem);
}
query = IntegrateAdditionalConditions(_queryPickerServices.GetCombinedContentQuery(ids, tokens, new string[] { "CommunicationContact" }), idlingua);
}
else
{
query = IntegrateAdditionalConditions(null, idlingua);
}
//// prove #GM
//var ris1 = _orchardServices.ContentManager.HqlQuery()
// .ForType(new string[] { "CommunicationContact" })
// .Join(alias => alias.ContentPartRecord<SmsContactPartRecord>().Property("SmsRecord", "smsrecord"))
// .Join(alias => alias.ContentPartRecord<TitlePartRecord>())
// ;
//var ris2 = ris1.List();
//return ris2.ToList();
// Trasformo in stringa HQL
var stringHQL = ((DefaultHqlQuery)query).ToHql(false);
// Rimuovo la Order by per poter fare la query annidata
// TODO: trovare un modo migliore per rimuovere la order by
stringHQL = stringHQL.ToString().Replace("order by civ.Id", "");
//var queryForSms = "SELECT distinct cir.Id as Id, TitlePart.Title as Title, SmsRecord.Prefix as SmsPrefix, SmsRecord.Sms as SmsNumber FROM " +
// "Orchard.ContentManagement.Records.ContentItemVersionRecord as civr join " +
// "civr.ContentItemRecord as cir join " +
// "civr.TitlePartRecord as TitlePart join " +
// "cir.SmsContactPartRecord as SmsPart join " +
// "SmsPart.SmsRecord as SmsRecord " +
// "WHERE civr.Published=1 AND civr.Id in (" + stringHQL + ")";
string queryForSms = "";
if (countOnly)
{
queryForSms = "SELECT count(SmsRecord) as Tot";
}
else
{
queryForSms = "SELECT civr as ContentItemVersionRecord, cir as ContentItemRecord, TitlePart as TitlePartRecord, SmsPart as SmsContactPartRecord, SmsRecord as SmsRecord";
}
queryForSms += " FROM Orchard.ContentManagement.Records.ContentItemVersionRecord as civr join " +
"civr.ContentItemRecord as cir join " +
"civr.TitlePartRecord as TitlePart join " +
"cir.SmsContactPartRecord as SmsPart join " +
"SmsPart.SmsRecord as SmsRecord " +
"WHERE civr.Published=1 AND SmsRecord.Validated AND SmsRecord.AccettatoUsoCommerciale AND civr.Id in (" + stringHQL + ")";
// Creo query ottimizzata per le performance
var fullStatement = _transactionManager.GetSession()
.CreateQuery(queryForSms)
.SetCacheable(false);
var lista = fullStatement
.SetResultTransformer(Transformers.AliasToEntityMap) //(Transformers.AliasToEntityMap) //(Transformers.AliasToBean<SmsHQL>())
.List(); // .List<SmsHQL>();
return(lista);
}