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); }