//internal static ActionsFolders MapToActionsFolders(IDataRecord r)
//{
// ActionsFolders a = new ActionsFolders();
// a.IdAction =int.Parse(r.GetDecimal(0).ToString());
// if (dr.IsDBNull("ID_NOME_DESTINAZIONE") == false)
// { a.IdNomeDestinazione = int.Parse(r.GetDecimal(1).ToString()); }
// a.FolderIdDestinazione = int.Parse(r.GetDecimal(2).ToString());
// a.Tipo = r.GetString(3);
// if (dr.IsDBNull("NUOVO_STATUS") == false)
// { a.NuovoStatus = r.GetString(4); }
// return a;
//}
internal static UserResultItem MapToUserResult(IDataRecord dr)
{
UserResultItem user = new UserResultItem();
user.Account = dr.GetString("ACCOUNT");
user.User = dr.GetString("UTE");
user.Operazioni = dr.GetDecimal("TOT").ToString();
return(user);
}
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);
}
