internal IList <RubricaEntitaType> GetContattiOrgByName(string nomeEntita)
{
List <RubricaEntitaType> listEntities = null;
using (OracleCommand oCmd = base.CurrentConnection.CreateCommand())
{
oCmd.CommandText = "SELECT VALUE(v0)"
+ " FROM v_rubr_entita_obj v0"
+ " WHERE :p_id_org IN (upper(v0.ragione_sociale), upper(v0.ufficio))";
oCmd.Parameters.Add("p_id_org", nomeEntita.ToUpper());
try
{
using (OracleDataReader r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
listEntities = new List <RubricaEntitaType>();
while (r.Read())
{
RubricaEntitaType ent = (RubricaEntitaType)(r.GetOracleValue(0));
if (!ent.RUBR_CONTATTI_REFS.IsNull)
{
ent.Contatti = new List <RubricaContatti>();
foreach (string o in ent.RUBR_CONTATTI_REFS.ListContattiRef)
{
OracleRef or = new OracleRef(base.CurrentConnection, o);
RubricaContattiType rc = (RubricaContattiType)or.GetCustomObject(OracleUdtFetchOption.Server);
rc.SetEntita(ent);
ent.Contatti.Add(rc);
}
}
listEntities.Add(ent);
}
}
}
}
catch
{
listEntities = null;
}
}
return(listEntities);
}
internal RubricaContattiType GetContattiById(long idContact)
{
RubricaContattiType cont = null;
using (OracleCommand cmd = CurrentConnection.CreateCommand())
{
cmd.CommandText = "SELECT VALUE(V0) FROM V_RUBR_CONTATTI_OBJ V0 WHERE ID_CONTACT = :pID_CONTACT";
cmd.BindByName = true;
cmd.Parameters.Add("pID_CONTACT", OracleDbType.Decimal, idContact, System.Data.ParameterDirection.Input);
using (OracleDataReader rd = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
{
while (rd.Read())
{
cont = (RubricaContattiType)rd.GetValue(0);
cont.SetEntita(CurrentConnection);
}
}
}
return(cont);
}
internal ResultList <RubricaContattiType> GetContattiByParams(List <SendMail.Model.EntitaType> tEnt, Dictionary <SendMail.Model.FastIndexedAttributes, List <string> > pars, int da, int per, bool withEntita)
{
ResultList <RubricaContattiType> res = new ResultList <RubricaContattiType>();
if (da == 0)
{
++da;
}
res.Da = da;
string query = String.Format("SELECT VALUE(V0) FROM {0} V0", ((intoIPA) ? "V_RUBR_CONTATTI_IPA_OBJ" : "V_RUBR_CONTATTI_OBJ"));
string orderby = " order by {0} asc nulls last";
string[] oBy = new string[pars.Count];
if (pars != null && pars.Count != 0)
{
query += " WHERE ";
}
string[] wherePars = new string[pars.Count];
for (int i = 0; i < pars.Count; i++)
{
KeyValuePair <SendMail.Model.FastIndexedAttributes, List <string> > p = pars.ElementAt(i);
if (p.Value == null || p.Value.Count == 0)
{
throw new ArgumentException("Parametri non validi");
}
string qPar = null;
switch (p.Key)
{
case SendMail.Model.FastIndexedAttributes.FAX:
qPar = "V0.FAX =";
oBy[i] = "V0.fax";
break;
case SendMail.Model.FastIndexedAttributes.MAIL:
qPar = "V0.MAIL =";
oBy[i] = "V0.mail";
break;
case SendMail.Model.FastIndexedAttributes.TELEFONO:
qPar = "V0.TELEFONO =";
oBy[i] = "V0.telefono";
break;
case SendMail.Model.FastIndexedAttributes.COGNOME:
qPar = "V0.ENTITA_REF.COGNOME =";
oBy[i] = "V0.ENTITA_REF.COGNOME";
break;
case SendMail.Model.FastIndexedAttributes.RAGIONE_SOCIALE:
qPar = "V0.ENTITA_REF.RAGIONE_SOCIALE =";
oBy[i] = "V0.ENTITA_REF.RAGIONE_SOCIALE";
break;
case SendMail.Model.FastIndexedAttributes.UFFICIO:
qPar = "V0.ENTITA_REF.UFFICIO =";
oBy[i] = "V0.ENTITA_REF.UFFICIO";
break;
default:
throw new NotImplementedException("Tipo di rircerca non implementato");
}
string[] qCrt = new string[p.Value.Count];
for (int j = 0; j < p.Value.Count; j++)
{
qCrt[j] = String.Format("{0} '{1}'", qPar, p.Value[j]);
}
wherePars[i] = String.Format("({0})", String.Join(" OR ", qCrt));
}
query += String.Join(" AND ", wherePars);
string queryCount = query.Replace(" VALUE(V0) ", " count(*) ");
query += String.Format(orderby, String.Join(", ", oBy));
using (OracleCommand oCmd = base.CurrentConnection.CreateCommand())
{
oCmd.CommandText = queryCount;
try
{
int tot = Convert.ToInt32(oCmd.ExecuteScalar());
res.Totale = tot;
res.Per = (tot > per) ? per : tot;
}
catch
{
res.Per = res.Totale = 0;
res.List = null;
throw;
}
if (res.Totale > 0)
{
if (res.Per > 0)
{
oCmd.CommandText = OrderedTOracleDB.GetOrderedQuery(query, da, res.Per);
}
else
{
oCmd.CommandText = query;
}
try
{
using (OracleDataReader r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
res.List = new List <RubricaContattiType>();
while (r.Read())
{
RubricaContattiType rc = r.GetValue(1) as RubricaContattiType;
if (withEntita)
{
rc.SetEntita(base.CurrentConnection);
}
res.List.Add(rc);
}
}
}
}
catch
{
res.List = null;
throw;
}
}
}
return(res);
}
