public ResultList <SimpleResultItem> LoadFieldsByParams(SendMail.Model.IndexedCatalogs ctg, IList <SendMail.Model.EntitaType> tEnt, KeyValuePair <SendMail.Model.FastIndexedAttributes, string> par, int da, int per)
{
if (String.IsNullOrEmpty(par.Value))
{
return(null);
}
if (tEnt == null)
{
tEnt = new List <SendMail.Model.EntitaType>();
}
if (tEnt.Count == 0)
{
tEnt.Add(SendMail.Model.EntitaType.ALL);
}
ResultList <SimpleResultItem> res = new ResultList <SimpleResultItem>();
res.Da = da;
string field = string.Empty;
using (var dbcontext = new FAXPECContext())
{
var querable = dbcontext.RUBR_ENTITA.AsQueryable();
switch (par.Key)
{
case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
field = "RAGIONE_SOCIALE";
querable = dbcontext.RUBR_ENTITA.Where(x => x.RAGIONE_SOCIALE.ToUpper().Contains(par.Value.ToUpper()));
break;
case SendMail.Model.FastIndexedAttributes.COGNOME:
field = "COGNOME";
querable = dbcontext.RUBR_ENTITA.Where(x => x.COGNOME.ToUpper().Contains(par.Value.ToUpper()));
break;
case SendMail.Model.FastIndexedAttributes.FAX:
field = "FAX";
querable = dbcontext.RUBR_CONTATTI.Where(x => x.FAX.ToUpper().Contains(par.Value)).Select(z => z.RUBR_ENTITA);
break;
case SendMail.Model.FastIndexedAttributes.MAIL:
field = "MAIL";
querable = dbcontext.RUBR_CONTATTI.Where(x => x.MAIL.ToUpper().Contains(par.Value)).Select(z => z.RUBR_ENTITA);
break;
case SendMail.Model.FastIndexedAttributes.TELEFONO:
field = "TELEFONO";
querable = dbcontext.RUBR_CONTATTI.Where(x => x.TELEFONO.ToUpper().Contains(par.Value)).Select(z => z.RUBR_ENTITA);
break;
case SendMail.Model.FastIndexedAttributes.UFFICIO:
querable = dbcontext.RUBR_ENTITA.Where(x => x.UFFICIO.ToUpper().Contains(par.Value));
break;
default:
throw new ArgumentException("Parametro non implementato");
}
if (!tEnt.Contains(SendMail.Model.EntitaType.ALL) && !tEnt.Contains(SendMail.Model.EntitaType.UNKNOWN))
{
var referrals = tEnt.Select(t => t.ToString()).ToArray();
querable = querable.Where(x => referrals.Contains(x.REFERRAL_TYPE));
}
try
{
int tot = querable.Count();
res.Per = (tot > per) ? per : tot;
res.Totale = tot;
List <RUBR_ENTITA> list = querable.OrderBy(f => f.ID_REFERRAL).Skip(res.Da).Take(res.Per).ToList();
foreach (RUBR_ENTITA r in list)
{
res.List.Add(
new SimpleResultItem(
// r.GetValue("descr").ToString(),
r.RAGIONE_SOCIALE,
r.ID_REFERRAL.ToString(),
String.Format("{0} {1} {2}", r.DISAMB_PRE, r.RAGIONE_SOCIALE, r.DISAMB_POST),
r.REFERRAL_TYPE,
"R",
100));
}
}
catch (Exception ex)
{
ManagedException mEx = new ManagedException("Errore nella ricerca in rubrica dell'entità o contatto Data Layer E075 Dettagli Errore: " + ex.Message,
"ERR_075", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
_log.Error(err);
int tot = 0;
res.List = null;
throw mEx;
}
}
return(res);
}
public ResultList <SimpleResultItem> LoadSimilarityFieldsByParams(SendMail.Model.IndexedCatalogs ctg, IList <SendMail.Model.EntitaType> tEnt, KeyValuePair <SendMail.Model.FastIndexedAttributes, string> par, int da, int per)
{
throw new NotImplementedException();
//if (String.IsNullOrEmpty(par.Value)) return null;
//if (tEnt == null) { tEnt = new List<SendMail.Model.EntitaType>(); }
//if (tEnt.Count == 0) { tEnt.Add(SendMail.Model.EntitaType.ALL); }
//ResultList<SimpleResultItem> res = new ResultList<SimpleResultItem>();
//res.Da = da;
//res.Per = per;
//res.Totale = per;
//string queryRubrica = "SELECT distinct r.RAGIONE_SOCIALE AS rag_soc"
// + ", {0} as descr"
// + ", r.DISAMB_PRE as prefix"
// + ", r.DISAMB_POST as suffix"
// + ", id_referral AS ids"
// + ", 'R' as SRC"
// + ", REFERRAL_TYPE as subtype"
// + ", utl_match.edit_distance_similarity('{1}', lower({0})) AS sim"
// + " FROM rubr_entita r {2}"
// + " WHERE {3}"
// + " order by 8 desc, 1";
//string campi = "";
//switch (par.Key)
//{
// case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
// case SendMail.Model.FastIndexedAttributes.COGNOME:
// case SendMail.Model.FastIndexedAttributes.FAX:
// case SendMail.Model.FastIndexedAttributes.MAIL:
// case SendMail.Model.FastIndexedAttributes.TELEFONO:
// case SendMail.Model.FastIndexedAttributes.UFFICIO:
// campi += par.Key.ToString();
// break;
// default:
// throw new ArgumentException("Parametro non implementato");
//}
//string innerJoin = "";
//if (par.Key.Equals(SendMail.Model.FastIndexedAttributes.FAX) ||
// par.Key.Equals(SendMail.Model.FastIndexedAttributes.MAIL) ||
// par.Key.Equals(SendMail.Model.FastIndexedAttributes.TELEFONO))
//{
// innerJoin = "INNER JOIN rubr_contatti c ON c.REF_ID_REFERRAL = r.id_referral";
//}
//string whereConds = null;
//if (!tEnt.Contains(SendMail.Model.EntitaType.ALL) && !tEnt.Contains(SendMail.Model.EntitaType.UNKNOWN))
//{
// whereConds += "REFERRAL_TYPE in (";
// whereConds += String.Join(", ", tEnt.Select(t => String.Format("'{0}'", t.ToString())).ToArray());
// whereConds += ") and ";
//}
//whereConds += "length(" + par.Key.ToString() + ") >= " + par.Value.Length;
//string query = string.Format(queryRubrica, campi, par.Value, innerJoin, whereConds);
//using (OracleCommand oCmd = base.CurrentConnection.CreateCommand())
//{
// if (per > 0) { oCmd.CommandText = OrderedTOracleDB.GetOrderedQuery(query, da, per); }
// else { oCmd.CommandText = query; }
// try
// {
// using (OracleDataReader r = oCmd.ExecuteReader())
// {
// if (r.HasRows)
// {
// res.List = new List<SimpleResultItem>();
// while (r.Read())
// {
// res.List.Add(
// new SimpleResultItem(
// r.GetValue("descr").ToString(),
// r.GetValue("ids").ToString(),
// String.Format("{0} {1} {2}", r.GetValue("prefix"), r.GetValue("rag_soc"), r.GetValue("suffix")),
// r.GetValue("subtype").ToString(),
// r.GetValue("SRC").ToString(),
// Convert.ToInt64(r.GetValue("sim"))));
// }
// }
// }
// }
// catch
// {
// res.List = null;
// }
//}
//return res;
}
public ResultList <SimpleResultItem> LoadFieldsByParams(SendMail.Model.IndexedCatalogs ctg, IList <SendMail.Model.EntitaType> tEnt, KeyValuePair <SendMail.Model.FastIndexedAttributes, string> par, int da, int per)
{
if (String.IsNullOrEmpty(par.Value))
{
return(null);
}
if (tEnt == null)
{
tEnt = new List <SendMail.Model.EntitaType>();
}
if (tEnt.Count == 0)
{
tEnt.Add(SendMail.Model.EntitaType.ALL);
}
ResultList <SimpleResultItem> res = new ResultList <SimpleResultItem>();
res.Da = da;
string queryCountBase = "SELECT count(*) from ({0})";
string queryRubrica = "SELECT distinct r.RAGIONE_SOCIALE AS rag_soc"
+ ", r.DISAMB_PRE as prefix"
+ ", r.DISAMB_POST as suffix"
+ ", {0} as descr"
+ ", LISTAGG(r.ID_REFERRAL, ';') within group (order by r.ID_REFERRAL) over (partition by NVL(r.DISAMB_PRE,' ')||r.RAGIONE_SOCIALE||NVL(r.DISAMB_POST,' ')) AS ids" //(partition by {0})
+ ", 'R' as SRC"
+ ", REFERRAL_TYPE as subtype"
+ " FROM rubr_entita r {1}"
+ " WHERE {2}"
+ " order by 1";
string campi = "";
switch (par.Key)
{
case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
case SendMail.Model.FastIndexedAttributes.COGNOME:
case SendMail.Model.FastIndexedAttributes.FAX:
case SendMail.Model.FastIndexedAttributes.MAIL:
case SendMail.Model.FastIndexedAttributes.TELEFONO:
case SendMail.Model.FastIndexedAttributes.UFFICIO:
campi += par.Key.ToString();
break;
default:
throw new ArgumentException("Parametro non implementato");
}
string innerJoin = "";
if (par.Key.Equals(SendMail.Model.FastIndexedAttributes.FAX) ||
par.Key.Equals(SendMail.Model.FastIndexedAttributes.MAIL) ||
par.Key.Equals(SendMail.Model.FastIndexedAttributes.TELEFONO))
{
innerJoin = "INNER JOIN rubr_contatti c ON c.REF_ID_REFERRAL = r.id_referral";
}
string whereConds = null;
if (!tEnt.Contains(SendMail.Model.EntitaType.ALL) && !tEnt.Contains(SendMail.Model.EntitaType.UNKNOWN))
{
whereConds += "REFERRAL_TYPE in (";
whereConds += String.Join(", ", tEnt.Select(t => String.Format("'{0}'", t.ToString())).ToArray());
whereConds += ") and ";
}
whereConds += "length(" + par.Key.ToString() + ") >= " + par.Value.Length + " and ";
switch (par.Key)
{
case SendMail.Model.FastIndexedAttributes.COGNOME:
case SendMail.Model.FastIndexedAttributes.MAIL:
case SendMail.Model.FastIndexedAttributes.UFFICIO:
case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
whereConds += "lower(" + par.Key.ToString() + ") like '%" + par.Value.ToLower() + "%'";
break;
case SendMail.Model.FastIndexedAttributes.FAX:
case SendMail.Model.FastIndexedAttributes.TELEFONO:
whereConds += par.Key.ToString() + " like '%" + par.Value.ToLower() + "%'";
break;
default:
throw new ArgumentException("Parametro non implementato");
}
string query = string.Format(queryRubrica, campi, innerJoin, whereConds);
string queryCount = String.Format(queryCountBase, query);
using (OracleCommand oCmd = base.CurrentConnection.CreateCommand())
{
//count
int tot = 0;
oCmd.CommandText = queryCount;
try
{
tot = Convert.ToInt32(oCmd.ExecuteScalar());
res.Per = (tot > per) ? per : tot;
res.Totale = tot;
}
catch
{
tot = 0;
res.List = null;
}
if (tot > 0)
{
if (per > 0)
{
oCmd.CommandText = OrderedTOracleDB.GetOrderedQuery(query, da, per);
}
else
{
oCmd.CommandText = query;
}
try
{
using (OracleDataReader r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
res.List = new List <SimpleResultItem>();
while (r.Read())
{
res.List.Add(
new SimpleResultItem(
r.GetValue("descr").ToString(),
r.GetValue("ids").ToString(),
String.Format("{0} {1} {2}", r.GetValue("prefix"), r.GetValue("rag_soc"), r.GetValue("suffix")),
r.GetValue("subtype").ToString(),
r.GetValue("SRC").ToString(),
100));
}
}
}
}
catch
{
res.List = null;
}
}
else if ((par.Key != SendMail.Model.FastIndexedAttributes.FAX) &&
(par.Key != SendMail.Model.FastIndexedAttributes.TELEFONO))
{
res = LoadSimilarityFieldsByParams(ctg, tEnt, par, 1, per);
}
}
return(res);
}
