public List <LOG_ACTIONS> GetVariables(string user, string codapp, string codlog, string usermail, DateTime d, DateTime df, int da, int per, ref int tot)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
var Querable = from e in dbcontext.LOG_ACTIONS select e;
var QuerableCount = from e in dbcontext.LOG_ACTIONS select e;
if (!string.IsNullOrEmpty(user))
{
Querable = Querable.Where(x => x.USER_ID.ToUpper() == user.ToUpper());
QuerableCount = QuerableCount.Where(x => x.USER_ID.ToUpper() == user.ToUpper());
}
if (!string.IsNullOrEmpty(codapp))
{
Querable = Querable.Where(x => x.APP_CODE.ToUpper() == codapp.ToUpper());
QuerableCount = QuerableCount.Where(x => x.APP_CODE.ToUpper() == codapp.ToUpper());
}
if (!string.IsNullOrEmpty(codlog))
{
Querable = Querable.Where(x => x.LOG_CODE.ToUpper() == codlog.ToUpper());
QuerableCount = QuerableCount.Where(x => x.LOG_CODE.ToUpper() == codlog.ToUpper());
}
if (!string.IsNullOrEmpty(usermail))
{
Querable = Querable.Where(x => x.USER_MAIL.ToUpper() == usermail.ToUpper());
QuerableCount = QuerableCount.Where(x => x.USER_MAIL.ToUpper() == usermail.ToUpper());
}
list = Querable.Where(p => p.LOG_DATE >= d && p.LOG_DATE <= df).OrderByDescending(u => u.LOG_DATE).Skip(da).Take(per).ToList();
tot = QuerableCount.Where(p => p.LOG_DATE >= d && p.LOG_DATE <= df).OrderByDescending(f => f.LOG_DATE).Count();
}
return(list);
}
public List <LOG_APP_CODES> GetAll()
{
List <LOG_APP_CODES> list = null;
using (var dbcontext = new MailLogEntities())
{
list = dbcontext.LOG_APP_CODES.ToList();
}
return(list);
}
public List <LOG_ACTIONS> GetByUserMailDate(string userMail, DateTime d, DateTime df, int da, int per)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
var result = dbcontext.LOG_ACTIONS.Where(x => x.USER_MAIL.ToUpper() == userMail.ToUpper() && x.LOG_DATE >= d && x.LOG_DATE <= df).Skip(da).Take(per).ToList();
}
return(list);
}
public List <LOG_ACTIONS> GetByUserMail(string userMail)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
var result = dbcontext.LOG_ACTIONS.Where(x => x.USER_MAIL.ToUpper() == userMail.ToUpper()).ToList();
}
return(list);
}
public List <LOG_ACTIONS> GetByAppCodeDate(string appCode, DateTime d, DateTime df, int da, int per)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
var result = dbcontext.LOG_ACTIONS.Where(x => x.APP_CODE.ToUpper() == appCode.ToUpper() && x.LOG_DATE >= d && x.LOG_DATE <= df).Skip(da).Take(per).ToList();
}
return(list);
}
public List <LOG_ACTIONS> GetByAppCode(string appCode)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
var result = dbcontext.LOG_ACTIONS.Where(x => x.APP_CODE.ToUpper() == appCode.ToUpper()).ToList();
}
return(list);
}
public List <LOG_APP_ERRORS> GetByLogCode(string logCode)
{
List <LOG_APP_ERRORS> list = null;
using (var dbcontext = new MailLogEntities())
{
var result = dbcontext.LOG_APP_ERRORS.Where(x => x.LOG_CODE.ToUpper() == logCode.ToUpper()).ToList();
}
return(list);
}
public List <LOG_ACTIONS> GetRowsByDate(DateTime d, DateTime df)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
list = (from p in dbcontext.LOG_ACTIONS
where p.LOG_DATE >= d && p.LOG_DATE <= df
select p).ToList();
}
return(list);
}
public List <LOG_ACTIONS> GetAllByRows(int da, int a)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
var result = (from p in dbcontext.LOG_ACTIONS
select p);
list = result.Skip(da).Take(a).ToList();
}
return(list);
}
public LOG_ACTIONS GetRowById(string id)
{
LOG_ACTIONS result = null;
using (var dbcontext = new MailLogEntities())
{
result = (from p in dbcontext.LOG_ACTIONS
where p.LOG_UID == id
select p).FirstOrDefault();
}
return(result);
}
public List <LOG_ACTIONS> GetRowsByUser(string user)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
list = (from p in dbcontext.LOG_ACTIONS
where p.USER_ID.ToUpper() == user.ToUpper()
select p).ToList();
}
return(list);
}
public List <LOG_ACTIONS> GetRowsByUserDate(string user, DateTime d, DateTime df, int da, int per)
{
List <LOG_ACTIONS> list = null;
using (var dbcontext = new MailLogEntities())
{
list = (from p in dbcontext.LOG_ACTIONS
where p.USER_ID.ToUpper() == user.ToUpper() &&
p.LOG_DATE >= d && p.LOG_DATE <= df
select p).Skip(da).Take(per).ToList();
}
return(list);
}
public List <UserResultItem> GetStatsInBox(string account, string utente, DateTime datainizio, DateTime datafine, int tot, int record, ref int totTotale)
{
List <UserResultItem> list = new List <UserResultItem>();
try
{
using (var dbcontext = new MailLogEntities())
{
var Querable = from e in dbcontext.LOG_ACTIONS where
e.LOG_CODE == "CRB_MOV" || e.LOG_CODE == "CRB_DEL" || e.LOG_CODE == "CRB_ARK" ||
e.LOG_CODE == "CRB_RIPK" || e.LOG_CODE == "CRB_RIPC"
select e;
//var QuerableCount = from e in dbcontext.LOG_ACTIONS
// where
// e.LOG_CODE == "CRB_MOV" || e.LOG_CODE == "CRB_DEL" || e.LOG_CODE == "CRB_ARK"
// || e.LOG_CODE == "CRB_RIPK" || e.LOG_CODE == "CRB_RIPC"
// select e;
if (!string.IsNullOrEmpty(utente))
{
Querable = Querable.Where(x => x.USER_ID.ToUpper() == utente.ToUpper());
// QuerableCount = QuerableCount.Where(x => x.USER_ID.ToUpper() == utente.ToUpper());
}
if (!string.IsNullOrEmpty(account))
{
Querable = Querable.Where(x => x.USER_MAIL.ToUpper() == account.ToUpper());
// QuerableCount = QuerableCount.Where(x => x.USER_MAIL.ToUpper() == account.ToUpper());
}
var g = Querable.Where(p => p.LOG_DATE >= datainizio && p.LOG_DATE <= datafine).ToList().GroupBy(a => new { ACCOUNT = a.USER_ID, UTE = a.USER_MAIL }).ToList();
foreach (var s in g)
{
var i = new UserResultItem
{
Account = s.Key.ACCOUNT,
User = s.Key.UTE,
Operazioni = s.Count().ToString()
};
list.Add(i);
totTotale += 1;
}
//list = g.Select(s => new UserResultItem
// {
// Account = s.Key.ACCOUNT,
// User = s.Key.UTE,
// Operazioni = s.Count().ToString()
// }).OrderBy(m=>m.Account).Take(tot).Skip(record).ToList();
//totTotale = QuerableCount.Where(p => p.LOG_DATE >= datainizio && p.LOG_DATE <= datafine).ToList().GroupBy(a => new { ACCOUNT = a.USER_ID, UTE = a.USER_MAIL })
//.Select(s => new UserResultItem
//{
// Account = s.Key.ACCOUNT,
// User = s.Key.UTE,
// Operazioni = s.Count().ToString()
//}).ToList().Count();
//var l = Querable.Where(p => p.LOG_DATE >= datainizio && p.LOG_DATE <= datafine && p.LOG_CODE in ('CRB_MOV,CRB_DEL,CRB_ARK,CRB_RIPK,CRB_RIPC').
// Group
// OrderByDescending(u => u.LOG_DATE).Skip(record).Take(tot).ToList();
//totTotale = QuerableCount.Where(p => p.LOG_DATE >= datainizio && p.LOG_DATE <= datafine).OrderByDescending(f => f.LOG_DATE).Count();
//foreach(LOG_ACTIONS)
//list.Add(AutoMapperConfiguration.MapToUserResult(r));
//StringBuilder sb = new StringBuilder();
//sb.Append("SELECT ACCOUNT, UTE, TOT FROM ");
// sb.Append(" (SELECT USER_MAIL AS ACCOUNT, UPPER(USER_ID) AS UTE,")
//.Append(" COUNT(*) AS TOT, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM ")
//.Append(" LOG_ACTIONS WHERE ")
//.Append(" and cast(log_date as Date) ")
//.Append(" BETWEEN '" + datainizio + "' ")
//.Append(" U AND '" + datainizio + "' ")
//.Append(" AND (UPPER(USER_MAIL)= '" + account.ToUpper() + "') ")
//.Append(" AND LOG_CODE IN ('CRB_MOV','CRB_DEL','CRB_ARK','CRB_RIPK','CRB_RIPC')");
//if (utente.ToUpper().Trim() != "TUTTI" && (!string.IsNullOrEmpty(utente)))
//{ sb.Append(" AND UPPER(USER_ID)='" + utente.ToUpper() + "' "); }
//sb.Append(" GROUP BY UPPER(USER_ID),USER_MAIL) ");
//using (var oCmd = dbcontext.Database.Connection.CreateCommand())
//{
// oCmd.CommandText = sb.ToString();
// using (var r = oCmd.ExecuteReader())
// {
// while (r.Read())
// {
// }
// }
//}
}
}
catch (Exception e)
{
if (!e.GetType().Equals(typeof(ManagedException)))
{
ManagedException mEx = new ManagedException("Errore nella creazione lista utenti statistica per casella email e periodo: " + account.ToString() + " " + datainizio + "-" + datafine + " E079 Dettagli Errore: " + e.Message,
"ERR_079", string.Empty, string.Empty, e.InnerException);
ErrorLogInfo err = new ErrorLogInfo(mEx);
log.Error(err);
}
}
return(list);
}
