public void Insert(Folder entity)
{
using (var dbcontext = new FAXPECContext())
{
try
{
if (string.IsNullOrEmpty(entity.IdNome))
{
double? idnome = dbcontext.FOLDERS.Select(x => x.IDNOME).DefaultIfEmpty(0).Max();
decimal newidnome = default(decimal);
decimal.TryParse(idnome.ToString(), out newidnome);
entity.IdNome = (newidnome + 1).ToString();
}
decimal idnew = (decimal)dbcontext.FOLDERS.Select(x => x.ID).DefaultIfEmpty(0).Max();
entity.Id = idnew;
FOLDERS folder = DaoSQLServerDBHelper.MapToFolderDto(entity, true);
dbcontext.FOLDERS.Add(folder);
entity.Id = folder.ID;
dbcontext.SaveChanges();
}
catch (Exception ex)
{
if (ex.GetType() == typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "FOL_ORA001", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
}
}
public void Update(Folder entity)
{
using (var dbcontext = new FAXPECContext())
{
try
{
var folder = dbcontext.FOLDERS.Where(x => x.ID == (decimal)entity.Id).FirstOrDefault();
folder = DaoSQLServerDBHelper.MapToFolderDto(entity, false);
int resp = dbcontext.SaveChanges();
if (resp == 0)
{
throw new Exception("Nessun record aggiornato");
}
}
catch (Exception ex)
{
if (ex.GetType() == typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "FOL_ORA002", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
}
}
public void Insert(List <Folder> entity)
{
using (var dbcontext = new FAXPECContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
try
{
double? idnome = dbcontext.FOLDERS.Select(x => x.IDNOME).DefaultIfEmpty(0).Max();
decimal newidnome = default(decimal);
decimal.TryParse(idnome.ToString(), out newidnome);
foreach (Folder f in entity)
{
f.IdNome = newidnome.ToString();
FOLDERS folder = DaoSQLServerDBHelper.MapToFolderDto(f, true);
dbcontext.FOLDERS.Add(folder);
}
}
catch (Exception ex)
{
if (ex.GetType() == typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "FOL_ORA001", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
}
}
}
public void Update(SendMail.Model.RubricaMapping.RubricaContatti entity)
{
using (var dbcontext = new FAXPECContext())
{
using (var transaction = dbcontext.Database.Connection.BeginTransaction())
try
{
var rubrOld = dbcontext.RUBR_CONTATTI.Where(x => x.ID_CONTACT == entity.IdContact).First();
var rubrNew = DaoSQLServerDBHelper.MapToRubrContatti(entity, false);
dbcontext.RUBR_CONTATTI.Remove(rubrOld);
dbcontext.RUBR_CONTATTI.Add(rubrNew);
int res = dbcontext.SaveChanges();
}
catch (Exception ex)
{
transaction.Rollback();
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException("Errore nell'inserimento del contatto per il contatto " + entity.Mail + " >> ERR_C012 >> Dettagli Errore: " + ex.Message,
"SQL_ERR_C012", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
}
public ICollection <ContactsApplicationMapping> GetContactsByCriteria(QueryCmpNew query)
{
List <ContactsApplicationMapping> listContacts = null;
using (var dbcontext = new FAXPECContext())
{
using (var oCmd = dbcontext.Database.Connection.CreateCommand())
{
oCmd.CommandText = cmdSelectRoot + TranslateQuery(query, oCmd);
try
{
using (var r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
listContacts = new List <ContactsApplicationMapping>();
while (r.Read())
{
listContacts.Add(DaoSQLServerDBHelper.MapToContactsApplication(r));
}
}
}
}
catch
{
//todo
throw;
}
}
}
return(listContacts);
}
public void Insert(List <ActiveUp.Net.Common.DeltaExt.Action> entity)
{
using (var dbcontext = new FAXPECContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
foreach (ActiveUp.Net.Common.DeltaExt.Action a in entity)
{
try
{
ACTIONS action = DaoSQLServerDBHelper.MapToActionDto(a, true);
dbcontext.ACTIONS.Add(action);
dbcontext.SaveChanges();
a.Id = (decimal)dbcontext.ACTIONS.Select(x => x.ID).DefaultIfEmpty(0).Max();
}
catch (Exception ex)
{
transaction.Rollback();
if (ex.GetType() == typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "ACT_ORA004", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
}
transaction.Commit();
}
}
}
public void Insert(List <ActionFolder> af)
{
using (var dbcontext = new FAXPECContext())
{
using (var transaction = dbcontext.Database.BeginTransaction())
{
foreach (ActionFolder a in af)
{
try
{
ACTIONS_FOLDERS actionFolder = DaoSQLServerDBHelper.MapToActionFolder(a, true);
dbcontext.ACTIONS_FOLDERS.Add(actionFolder);
dbcontext.SaveChanges();
a.iD = (double)dbcontext.ACTIONS_FOLDERS.Select(x => x.ID).DefaultIfEmpty(0).Max();
}
catch (Exception ex)
{
transaction.Rollback();
if (ex.GetType() == typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "ACF_ORA001", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
}
transaction.Commit();
}
}
}
/// <summary>
/// Restituisce l'intera collezione degli oggetti SottoTitolo.
/// </summary>
/// <returns></returns>
public ICollection <SottoTitolo> GetAll()
{
List <SottoTitolo> sottotitoli = null;
using (var dbcontext = new FAXPECContext())
{
try
{
var l = dbcontext.COMUNICAZIONI_SOTTOTITOLI.ToList();
foreach (COMUNICAZIONI_SOTTOTITOLI s in l)
{
sottotitoli.Add(DaoSQLServerDBHelper.MapToSottotitolo(s));
}
}
catch (SqlException oex)
{
ManagedException mEx = new ManagedException(DalExMessages.SOTTOTITOLO_NON_RECUPERATO,
"DAL_SOT_001", string.Empty,
string.Empty, oex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(mEx);
throw mEx;
}
}
return(sottotitoli);
}
/// <summary>
/// Restituisce la collezione di oggetti SottoTitolo
/// collegati al Titolo passato.
/// </summary>
/// <param name="titoloKey">identificativo del Titolo</param>
/// <returns>una collection di oggetti SottoTitolo</returns>
public ICollection <SottoTitolo> FindByTitolo(decimal titoloKey)
{
List <SottoTitolo> sottotitoli = null;
using (var dbcontext = new FAXPECContext())
{
try
{
var l = dbcontext.COMUNICAZIONI_SOTTOTITOLI.Where(x => x.REF_ID_TITOLO == titoloKey).ToList();
if (l.Count > 0)
{
sottotitoli = new List <SottoTitolo>();
foreach (COMUNICAZIONI_SOTTOTITOLI t in l)
{
sottotitoli.Add(DaoSQLServerDBHelper.MapToSottotitolo(t));
}
}
}
catch (SqlException oex)
{
ManagedException mEx = new ManagedException(DalExMessages.SOTTOTITOLO_NON_RECUPERATO,
"DAL_SOT_002", string.Empty,
string.Empty, oex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(mEx);
throw mEx;
}
}
return(sottotitoli);
}
public void Update(SottoTitolo sottoTitolo)
{
using (var dbcontext = new FAXPECContext())
{
try
{
COMUNICAZIONI_SOTTOTITOLI s = DaoSQLServerDBHelper.MapToComunicazioniSottotitolo(sottoTitolo, false);
var olds = dbcontext.COMUNICAZIONI_SOTTOTITOLI.Where(x => x.ID_SOTTOTITOLO == sottoTitolo.Id).First();
dbcontext.COMUNICAZIONI_SOTTOTITOLI.Remove(olds);
dbcontext.COMUNICAZIONI_SOTTOTITOLI.Add(s);
int rowAff = dbcontext.SaveChanges();
if (rowAff == 0)
{
ManagedException mEx = new ManagedException(DalExMessages.NESSUNA_RIGA_MODIFICATA,
"DAL_TIT_009", string.Empty,
string.Empty, null);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(mEx);
throw mEx;
}
}
catch (InvalidOperationException ex)
{
ManagedException mEx = new ManagedException(DalExMessages.RUBRICA_NON_AGGIORNATA,
"DAL_UNIQUE_CODE", string.Empty,
string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(mEx);
throw mEx;
}
}
}
public void Insert(SendMail.Model.RubricaMapping.RubricaEntita entity)
{
using (var dbcontext = new FAXPECContext())
{
try
{
var rubr = DaoSQLServerDBHelper.MapToRubrEntita(entity, true);
dbcontext.RUBR_ENTITA.Add(rubr);
int r = dbcontext.SaveChanges();
if (r == 1)
{
entity.IdReferral = (long)rubr.ID_REFERRAL;
}
else
{
//Allineamento log - Ciro
ManagedException mEx = new ManagedException("Errore inserimento ",
"RUB_ORA003",
string.Empty,
string.Empty,
null);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
catch
{
throw;
}
}
}
public void Update(ActiveUp.Net.Common.DeltaExt.Action entity)
{
using (var dbcontext = new FAXPECContext())
{
try
{
var action = dbcontext.ACTIONS.Where(x => x.ID == (double)entity.Id).FirstOrDefault();
action = DaoSQLServerDBHelper.MapToActionDto(entity, false);
int resp = dbcontext.SaveChanges();
if (resp == 0)
{
throw new Exception("Nessun record aggiornato");
}
}
catch (Exception ex)
{
if (ex.GetType() == typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "ACT_ORA002", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
}
}
public ICollection <ActiveUp.Net.Mail.DeltaExt.MailUser> GetAllManaged()
{
ICollection <MailUser> users = null;
using (FAXPECContext dbcontext = new FAXPECContext())
{
try
{
var mailsenders = dbcontext.MAIL_SENDERS.Where(x => x.FLG_MANAGED != null).ToList();
foreach (MAIL_SENDERS sender in mailsenders)
{
int idMailServer = (int)sender.ID_MAILSERVER;
int idUser = (int)sender.ID_SENDER;
if (idMailServer != 0)
{
MAILSERVERS m = dbcontext.MAILSERVERS.Where(x => x.ID_SVR == idMailServer).FirstOrDefault();
MailServer s = AutoMapperConfiguration.FromMailServersToModel(m);
if (idUser != 0)
{
List <Folder> l = GetMailFolders(idUser);
users.Add(DaoSQLServerDBHelper.MapToMailUser(sender, s, l));
}
}
}
}
catch
{
users = null;
throw;
}
}
return(users);
}
public ActiveUp.Net.Mail.DeltaExt.MailUser GetManagedUserByAccount(string userName)
{
MailUser User = null;
using (FAXPECContext dbcontext = new FAXPECContext())
{
try
{
var mailsender = dbcontext.MAIL_SENDERS.Where(x => (x.FLG_MANAGED == "2" || x.FLG_MANAGED == "1") && x.MAIL.ToUpper() == userName.ToUpper()).FirstOrDefault();
int idmailserver = (int)mailsender.ID_MAILSERVER;
int idmailuser = (int)mailsender.ID_SENDER;
if (idmailserver != 0)
{
MAILSERVERS m = dbcontext.MAILSERVERS.Where(x => x.ID_SVR == idmailserver).FirstOrDefault();
MailServer s = AutoMapperConfiguration.FromMailServersToModel(m);
if (idmailuser != 0)
{
List <Folder> l = GetMailFolders(idmailuser);
User = DaoSQLServerDBHelper.MapToMailUser(mailsender, s, l);
}
}
}
catch
{
User = null;
throw;
}
}
return(User);
}
public ActiveUp.Net.Mail.DeltaExt.MailUser GetById(decimal id)
{
ActiveUp.Net.Mail.DeltaExt.MailUser user = null;
using (FAXPECContext dbcontext = new FAXPECContext())
{
var mailsender = dbcontext.MAIL_SENDERS.Where(x => x.ID_SENDER == id).FirstOrDefault();
try
{
int idmailserver = (int)mailsender.ID_MAILSERVER;
MAILSERVERS ms = dbcontext.MAILSERVERS.Where(x => x.ID_SVR == idmailserver).FirstOrDefault();
MailServer s = AutoMapperConfiguration.FromMailServersToModel(ms);
int idmailuser = (int)mailsender.ID_SENDER;
List <Folder> list = GetMailFolders(idmailuser);
user = DaoSQLServerDBHelper.MapToMailUser(mailsender, s, list);
}
catch (Exception ex)
{
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message, "ERR_ACC_006", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = id.ToString();
log.Error(err);
}
user = null;
}
}
return(user);
}
public void Insert(SendMail.Model.RubricaMapping.RubricaContatti entity)
{
using (var dbcontext = new FAXPECContext())
{
using (var dbContextTransaction = dbcontext.Database.BeginTransaction())
{
try
{
if (entity.Entita != null)
{
RUBR_ENTITA e = DaoSQLServerDBHelper.MapToRubrEntita(entity.Entita, true);
dbcontext.RUBR_ENTITA.Add(e);
entity.Entita.IdReferral = (long)e.ID_REFERRAL;
entity.RefIdReferral = (long)e.ID_REFERRAL;
RUBR_CONTATTI c = DaoSQLServerDBHelper.MapToRubrContatti(entity, true);
entity.IdContact = (long)c.ID_CONTACT;
dbcontext.RUBR_CONTATTI.Add(c);
dbcontext.SaveChanges();
dbContextTransaction.Commit();
}
else if (entity.Entita == null && entity.RefIdReferral != null)
{
RUBR_CONTATTI c = DaoSQLServerDBHelper.MapToRubrContatti(entity, true);
entity.IdContact = (long)c.ID_CONTACT;
dbcontext.RUBR_CONTATTI.Add(c);
int righe = dbcontext.SaveChanges();
dbContextTransaction.Commit();
}
else if (entity.Entita == null && entity.RefIdReferral == null)
{
dbContextTransaction.Rollback();
ManagedException mEx = new ManagedException("Errore nell'inserimento del contatto per il contatto " + entity.Mail + " >> ERR_C014 >> Dettagli Errore: Mancano riferimenti entità",
"SQL_ERR_C014", string.Empty, string.Empty, null);
ErrorLogInfo err = new ErrorLogInfo(mEx);
_log.Error(err);
throw mEx;
}
}
catch (Exception ex)
{
dbContextTransaction.Rollback();
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message,
"SQL_ERR_C013", string.Empty, string.Empty, ex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
_log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
}
}
public List <Folder> GetAllFoldersByAccount(int UserId)
{
List <Folder> list = null;
try
{
using (FAXPECContext dbcontext = new FAXPECContext())
{
using (var oCmd = dbcontext.Database.Connection.CreateCommand())
{
//var foldersSenders = dbcontext.FOLDERS_SENDERS.Where(x => x.IDSENDER == UserId).ToList();
//foreach (FOLDERS_SENDERS f in foldersSenders)
//{
// List<Folder> a = AutoMapperConfiguration.MapToFolderModel(f);
//}
string queryFolder = "SELECT FOLDERS.ID, FOLDERS.NOME, TIPO,SYSTEM,IDNOME FROM [FAXPEC].[FAXPEC].[FOLDERS],[FAXPEC].[FAXPEC].[FOLDERS_SENDERS] WHERE IDFOLDER=FOLDERS.ID AND IDSENDER=" + UserId;
oCmd.CommandText = queryFolder;
oCmd.Connection.Open();
using (DbDataReader r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
list = new List <Folder>();
while (r.Read())
{
List <ActiveUp.Net.Common.DeltaExt.Action> la = GetActionsFolder(r, UserId);
list.Add(DaoSQLServerDBHelper.MapToFolder(r, la));
}
}
}
oCmd.Connection.Close();
}
}
}
catch (Exception ex)
{
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message, "ERR_ACC_002", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = UserId.ToString();
log.Error(err);
}
}
return(list);
}
/// <summary>
/// Prende la lista di tutte le cartelle non attive per la mail selezionata
/// </summary>
/// <param name="mail"></param>
/// <returns></returns>
public List <SendersFolders> GetFoldersNONAbilitati(string mail)
{
List <SendersFolders> listaCartelle = new List <SendersFolders>();
try
{
using (var dbcontext = new FAXPECContext())
{
var oCmd = dbcontext.Database.Connection.CreateCommand();
oCmd.CommandText = "SELECT DISTINCT m.ID_SENDER, f.NOME, m.MAIL, f.IDNOME, f.SYSTEM " +
"FROM [FAXPEC].[FAXPEC].[MAIL_SENDERS] m, [FAXPEC].[FAXPEC].[folders] f, [FAXPEC].[FAXPEC].[folders_senders] fs " +
"WHERE m.mail = '" + mail + "' " +
"EXCEPT " +
"SELECT DISTINCT m.ID_SENDER, f.NOME, m.MAIL, f.IDNOME, f.SYSTEM " +
"FROM [FAXPEC].[FAXPEC].[MAIL_SENDERS] m, [FAXPEC].[FAXPEC].[folders] f, [FAXPEC].[FAXPEC].[folders_senders] fs " +
"WHERE m.mail = '" + mail + "' " +
"AND m.ID_SENDER = fs.IDSENDER " +
"AND f.ID = fs.IDFOLDER";
oCmd.Connection.Open();
using (var r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
listaCartelle = new List <SendersFolders>();
while (r.Read())
{
SendersFolders sFold = DaoSQLServerDBHelper.MapToSendersFolders(r);
listaCartelle.Add(sFold);
}
}
}
oCmd.Connection.Close();
}
}
catch (Exception ex)
{
listaCartelle = null;
//Allineamento log - Ciro
if (ex.GetType() != typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "SND_ORA004", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
}
}
return(listaCartelle);
}
public List <ActiveUp.Net.Common.DeltaExt.Action> GetAllActionsByFolder(int id, string tipo, int IdUser)
{
List <ActiveUp.Net.Common.DeltaExt.Action> list = new List <ActiveUp.Net.Common.DeltaExt.Action>();
try
{
using (FAXPECContext dbcontext = new FAXPECContext())
{
using (var oCmd = dbcontext.Database.Connection.CreateCommand())
{
string queryActions = " SELECT ACTIONS.ID, ACTIONS.NOME_AZIONE,ACTIONS.ID_NOME_DESTINAZIONE,ACTIONS.TIPO_DESTINAZIONE, " +
" ACTIONS.TIPO_AZIONE, ACTIONS.NUOVO_STATUS,ACTIONS_FOLDERS.IDFOLDER,ACTIONS.ID_FOLDER_DESTINAZIONE " + " FROM [FAXPEC].[FAXPEC].ACTIONS, [FAXPEC].[FAXPEC].ACTIONS_FOLDERS,[FAXPEC].[FAXPEC].FOLDERS_SENDERS,[FAXPEC].[FAXPEC].FOLDERS " +
" WHERE FOLDERS.ID= " + id + " AND FOLDERS_SENDERS.IDFOLDER=FOLDERS.ID " + " AND FOLDERS_SENDERS.IDSENDER = " + IdUser + " AND FOLDERS_SENDERS.IDFOLDER= ACTIONS_FOLDERS.IDFOLDER "
+ " AND ACTIONS.ID= ACTIONS_FOLDERS.IDACTION AND ((ID_FOLDER_DESTINAZIONE IN (SELECT FOLDERS_SENDERS.IDFOLDER " + " FROM [FAXPEC].[FAXPEC].FOLDERS_SENDERS WHERE IDSENDER = " + IdUser + " )) " +
"OR (ID_FOLDER_DESTINAZIONE IS NULL AND FOLDERS.TIPO IN ('I','E')))";
oCmd.CommandText = queryActions;
oCmd.Connection.Open();
using (var r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
list = new List <ActiveUp.Net.Common.DeltaExt.Action>();
while (r.Read())
{
list.Add(DaoSQLServerDBHelper.MapToAction(r));
}
}
}
oCmd.Connection.Close();
}
}
}
catch (Exception ex)
{
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message, "ERR_ACC_003", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = id.ToString();
log.Error(err);
}
}
return(list);
}
public void Insert(ContactsBackendMap entity)
{
try
{
using (FAXPECContext dbcontext = new FAXPECContext())
{
RUBR_CONTATTI_BACKEND b = DaoSQLServerDBHelper.MapToRubrContattiBackend(entity);
dbcontext.RUBR_CONTATTI_BACKEND.Add(b);
int risp = dbcontext.SaveChanges();
if (risp == 1)
{
entity.Id = (int)b.ID_MAP;
}
}
}
catch
{
throw;
}
}
public void Insert(ActiveUp.Net.Common.DeltaExt.Action entity)
{
using (var dbcontext = new FAXPECContext())
{
try
{
ACTIONS action = DaoSQLServerDBHelper.MapToActionDto(entity, true);
dbcontext.ACTIONS.Add(action);
}
catch (Exception ex)
{
if (ex.GetType() == typeof(ManagedException))
{
ManagedException mEx = new ManagedException(ex.Message, "ACT_ORA001", string.Empty, string.Empty, ex);
ErrorLogInfo er = new ErrorLogInfo(mEx);
log.Error(er);
throw mEx;
}
}
}
}
private string TranslateIClause(IClause clause, DbCommand oCmd, ref int k)
{
String cmdText = "";
if (clause is SingleClause)
{
SingleClause sc = clause as SingleClause;
if (sc.Operator == CriteriaOperator.IsNull || sc.Operator == CriteriaOperator.IsNotNull)
{
cmdText += String.Format("({0} {1})", sc.PropertyName, DaoSQLServerDBHelper.TranslateOperator(sc.Operator));
}
else
{
string par = String.Format("p{0}", k++);
cmdText += String.Format("({0} {1} :{2})", sc.PropertyName, DaoSQLServerDBHelper.TranslateOperator(sc.Operator), par);
try
{
ObjectParameter p = new ObjectParameter(par, ((SingleClause)clause).Value.ToString());
//oCmd.Parameters.Add(par, ((SingleClause)clause).Value.ToString());
}
catch
{
}
}
}
else if (clause is CombinedClause)
{
cmdText += "(";
CombinedClause cc = clause as CombinedClause;
for (int j = 0; j < cc.SubClauses.Count() - 1; j++)
{
cmdText += String.Format("{0} {1} ", TranslateIClause(cc.SubClauses.ElementAt(j), oCmd, ref k),
cc.ClauseType.ToString().ToUpper());
}
cmdText += String.Format("{0})", TranslateIClause(cc.SubClauses.Last(), oCmd, ref k));
}
return(cmdText);
}
public void Update(ActiveUp.Net.Mail.DeltaExt.MailUser u, ActiveUp.Net.Mail.Message m)
{
using (var dbcontext = new FAXPECContext())
{
try
{
MAIL_INBOX inbox = dbcontext.MAIL_INBOX.Where(x => x.ID_MAIL == int.Parse(m.Uid) && x.MAIL_ACCOUNT.ToUpper() == u.EmailAddress.ToUpper()).First();
inbox = DaoSQLServerDBHelper.MapToMailInBox(u, m);
dbcontext.SaveChanges();
}
catch (Exception e)
{
if (!e.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException("Errore nel metodo Update(ActiveUp.Net.Mail.DeltaExt.MailUser u, ActiveUp.Net.Mail.Message m) Dettaglio: " + e.Message,
"E015", string.Empty, string.Empty, e.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
_log.Error(err);
}
}
}
}
public IList <MailUser> GetUsersByMails(IList <String> mails)
{
List <MailUser> lMU = null;
string[] allMails = mails.ToArray();
try
{
using (FAXPECContext dbcontext = new FAXPECContext())
{
lMU = new List <MailUser>();
List <MAIL_SENDERS> l = (from e in dbcontext.MAIL_SENDERS
where allMails.Contains(e.MAIL)
select e).ToList();
foreach (MAIL_SENDERS m in l)
{
int idmailserver = (int)m.ID_MAILSERVER;
MAILSERVERS ms = dbcontext.MAILSERVERS.Where(x => x.ID_SVR == idmailserver).FirstOrDefault();
MailServer s = AutoMapperConfiguration.FromMailServersToModel(ms);
int idmailuser = (int)m.ID_SENDER;
List <Folder> list = GetMailFolders(idmailuser);
lMU.Add(DaoSQLServerDBHelper.MapToMailUser(m, s, list));
}
}
}
catch (Exception ex)
{
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException(ex.Message, "ERR_ACC_001", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
err.objectID = string.Empty;
log.Error(err);
}
lMU = null;
}
return(lMU);
}
public int Save(BackendUser entity)
{
int tot = 0;
try
{
using (var dbcontext = new FAXPECContext())
{
MAIL_USERS_BACKEND m = new MAIL_USERS_BACKEND();
m = DaoSQLServerDBHelper.MapToMailUsersBackend(entity);
dbcontext.MAIL_USERS_BACKEND.Add(m);
tot = dbcontext.SaveChanges();
}
if (tot != 1)
{
throw new ManagedException("Utente non inserito", "ERR_BU01", string.Empty, string.Empty, null);
}
}
catch (Exception ex)
{
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException("Errore nell'inserimento dell'utente in Users Backend Err_bu01 Dettagli Errore: " + entity.UserName + " " + ex.Message,
"ERR_BU01", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(err);
throw mEx;
}
else
{
throw;
}
}
return(tot);
}
public void Update(BackendUser entity)
{
int tot = 0;
try
{
using (var dbcontext = new FAXPECContext())
{
MAIL_USERS_BACKEND m = dbcontext.MAIL_USERS_BACKEND.FirstOrDefault(c => c.ID_USER == entity.UserId);
m = DaoSQLServerDBHelper.MapToMailUsersBackend(entity, m);
dbcontext.MAIL_USERS_BACKEND.Attach(m);
dbcontext.Entry(m).State = EntityState.Modified;
tot = dbcontext.SaveChanges();
}
if (tot != 1)
{
throw new ManagedException("Utente non aggiornato", "ERR_BU02", string.Empty, string.Empty, null);
}
}
catch (Exception ex)
{
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException("Errore nell'aggiornamento dell'utente in Users Backend Err_bu02 Dettagli Errore: " + entity.UserName + " " + ex.Message,
"ERR_BU02", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(err);
throw mEx;
}
else
{
throw;
}
}
}
public void Insert(SottoTitolo sottoTitolo)
{
using (var dbcontext = new FAXPECContext())
{
try
{
COMUNICAZIONI_SOTTOTITOLI s = DaoSQLServerDBHelper.MapToComunicazioniSottotitolo(sottoTitolo, true);
dbcontext.COMUNICAZIONI_SOTTOTITOLI.Add(s);
var resp = dbcontext.SaveChanges();
if (resp == 0)
{
throw new Exception(DalExMessages.ID_NON_RESTITUITO);
}
}
catch (InvalidOperationException ioex)
{
ManagedException mEx = new ManagedException(DalExMessages.RUBRICA_NON_INSERITA,
"DAL_RUB_002", string.Empty,
string.Empty, ioex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(mEx);
throw mEx;
}
catch (SqlException oex)
{
ManagedException mEx = new ManagedException(DalExMessages.RUBRICA_NON_INSERITA,
"DAL_RUB_001", string.Empty,
string.Empty, oex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(mEx);
throw mEx;
}
}
}
public SottoTitolo GetSottoTitoloByComCode(string comcode)
{
SottoTitolo sottotitolo = null;
using (var dbcontext = new FAXPECContext())
{
try
{
var s = dbcontext.COMUNICAZIONI_SOTTOTITOLI.Where(x => x.COM_CODE.ToUpper() == comcode.ToUpper()).First();
sottotitolo = DaoSQLServerDBHelper.MapToSottotitolo(s);
}
catch (SqlException oex)
{
ManagedException mEx = new ManagedException(DalExMessages.TITOLO_NON_RECUPERATO,
"DAL_TIT_011", string.Empty,
string.Empty, oex);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(mEx);
throw mEx;
}
}
return(sottotitolo);
}
public IList <MailUser> GetUserByServerAndUsername(decimal idServer, string userName)
{
List <MailUser> lUser = null;
using (FAXPECContext dbcontext = new FAXPECContext())
{
var mailsender = dbcontext.MAIL_SENDERS.Where(x => x.ID_MAILSERVER == idServer && x.USERNAME.ToUpper() == userName.ToUpper()).FirstOrDefault();
if (mailsender != null)
{
try
{
lUser = new List <MailUser>();
MAIL_SENDERS m = dbcontext.MAIL_SENDERS.Where(x => x.ID_MAILSERVER == idServer).FirstOrDefault();
int idmailserver = (int)m.ID_MAILSERVER;
MAILSERVERS ms = dbcontext.MAILSERVERS.Where(x => x.ID_SVR == idmailserver).FirstOrDefault();
int idmailuser = (int)mailsender.ID_SENDER;
MailServer s = AutoMapperConfiguration.FromMailServersToModel(ms);
if (idmailuser != 0)
{
List <Folder> l = GetMailFolders(idmailuser);
lUser.Add(DaoSQLServerDBHelper.MapToMailUser(mailsender, s, l));
}
}
catch
{
lUser = null;
throw;
}
}
else
{
return(lUser);
}
}
return(lUser);
}
public Dictionary <string, SimpleTreeItem> LoadTree(Int64?startNode, SendMail.Model.IndexedCatalogs catalog, int?levels)
{
Dictionary <string, SimpleTreeItem> list = null;
StringBuilder query = new StringBuilder();
switch (catalog)
{
case IndexedCatalogs.RUBR:
query.Append(" with tree(value, text, subtype, source, padre, level) as ( ");
query.Append(" select ID_REFERRAL AS VALUE, CASE ");
query.Append(" WHEN REFERRAL_TYPE in ('PA', 'AZ_PRI', 'AZ_CP', 'GRP') then RAGIONE_SOCIALE else UFFICIO end as TEXT, ");
query.Append(" REFERRAL_TYPE AS SUBTYPE, 'RUBR' AS SOURCE, ID_PADRE AS PADRE, 1 as level from ");
query.Append(" [FAXPEC].[FAXPEC].[rubr_entita] where REFERRAL_TYPE in ");
query.Append(" ('PA', 'PA_SUB', 'PA_UFF', 'AZ_PRI', 'AZ_CP', 'AZ_UFF', 'GRP') ");
query.Append(" union all ");
query.Append(" select ");
query.Append(" child.ID_REFERRAL AS VALUE, CASE ");
query.Append(" WHEN REFERRAL_TYPE in ('PA','AZ_PRI','AZ_CP','GRP') then child.RAGIONE_SOCIALE else child.UFFICIO end as TEXT, ");
query.Append(" child.REFERRAL_TYPE AS SUBTYPE, 'RUBR' AS SOURCE, child.ID_PADRE AS PADRE, parent.level + 1 from ");
query.Append(" [FAXPEC].[FAXPEC].[rubr_entita] as child join tree parent on parent.value = child.ID_PADRE where REFERRAL_TYPE in ");
query.Append(" ('PA','PA_SUB','PA_UFF','AZ_PRI','AZ_CP','AZ_UFF','GRP')) ");
query.Append(" select * from tree ");
if (levels.HasValue || startNode.HasValue)
{
query.Append(" where ");
}
if (levels.HasValue)
{
query.Append(" level <=" + levels);
query.Append(" and ");
}
if (startNode.HasValue)
{
query.Append(" tree.value >= :pID ");
}
else
{
query.Append(" tree.value >= 0 ");
}
query.Append(" order by tree.level asc");
break;
case IndexedCatalogs.IPA:
query.Append("select ID_RUB AS VALUE, ");
query.Append("'IPA' AS SOURCE, ");
query.Append("CASE WHEN ( substr(DN,1,2)= 'c=' or substr(DN,1,2) = 'o=') then RAGIONESOCIALE ");
query.Append("else UFFICIO end AS TEXT, ");
query.Append("CASE WHEN substr(DN,1,2)= 'c=' then 'GRP' ");
query.Append("WHEN substr(DN,1,2)= 'o=' then 'PA' ");
query.Append("else 'PA_UFF' end AS SUBTYPE, ");
query.Append("ID_PADRE AS PADRE ");
query.Append("from [FAXPEC].[FAXPEC].[IPA] ");
if (levels.HasValue)
{
query.Append("where level <=" + levels.Value);
}
if (startNode.HasValue)
{
query.Append("start with ID_RUB = " + startNode.Value);
}
else
{
query.Append("start with ID_RUB = 1 ");
}
query.Append("connect by NOCYCLE prior ID_RUB = ID_PADRE");
break;
}
using (var dbcontext = new FAXPECContext())
{
using (var oCmd = dbcontext.Database.Connection.CreateCommand())
{
try
{
oCmd.Connection.Open();
oCmd.CommandText = query.ToString();
using (var r = oCmd.ExecuteReader())
{
if (r.HasRows)
{
list = new Dictionary <string, SimpleTreeItem>();
bool skip = false;
while (r.Read())
{
if (!skip)
{
SimpleTreeItem it = DaoSQLServerDBHelper.MapToSimpleTreeItem(r);
list.Add(it.ExtendedValue, it);
}
}
}
}
oCmd.Connection.Close();
}
catch (Exception ex)
{
if (!ex.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException("Errore nel caricamento dal database dell'albero Data Layer E035 Dettagli Errore: " + ex.Message,
"ERR_035", string.Empty, string.Empty, ex.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(err);
throw mEx;
}
else
{
throw ex;
}
}
}
}
return(list);
}
