static public void integrityTextReport(IAMDatabase db, DataTable dtS, List <MailAddress> recipents)
{
StringBuilder errors = new StringBuilder();
DataTable dtL = db.Select("select l.text from logs l where text like 'Integrity check error: Multiplus entities%' and l.date >= DATEADD(day,-1,getdate()) and l.enterprise_id = " + dtS.Rows[0]["enterprise_id"] + " group by l.text");
if (dtL == null)
{
return;
}
DataTable dtErrors = new DataTable();
dtErrors.Columns.Add("text", typeof(String));
Dictionary <String, String> title = new Dictionary <string, string>();
title.Add("text", "Texto");
List <String> duplicatedEntities = new List <String>();
foreach (DataRow dU in dtL.Rows)
{
try
{
DataRow newItem = dtErrors.NewRow();
newItem["text"] = dU["text"];
dtErrors.Rows.Add(newItem.ItemArray);
//Captura somente os IDs das entidades
Regex rex = new Regex(@"\((.*?)\)");
Match m = rex.Match(dU["text"].ToString());
if (m.Success)
{
String[] entities = m.Groups[1].Value.Replace(" ", "").Split(",".ToCharArray());
duplicatedEntities.AddRange(entities);
}
}
catch (Exception ex)
{
errors.AppendLine("Error processing registry: " + ex.Message);
}
}
Dictionary <String, String> title2 = new Dictionary <string, string>();
title2.Add("id", "Entity ID");
title2.Add("login", "Login");
title2.Add("full_name", "Nome Completo");
title2.Add("change_password", "Ultima troca de senha");
title2.Add("last_login", "Ultimo Login ");
DataTable dtUsr = new DataTable();
dtUsr.Columns.Add("id", typeof(Int64));
dtUsr.Columns.Add("login", typeof(String));
dtUsr.Columns.Add("full_name", typeof(String));
dtUsr.Columns.Add("change_password", typeof(DateTime));
dtUsr.Columns.Add("last_login", typeof(DateTime));
//select e.id, e.login, e.full_name, e.change_password, e.last_login from entity e where id in (10583, 13065) order by e.full_name
DataTable dtU = db.Select("select e.id, e.login, e.full_name, e.change_password, e.last_login from entity e where id in (" + String.Join(",", duplicatedEntities) + ") order by e.full_name");
if (errors.ToString() != "")
{
db.AddUserLog(LogKey.Report, null, "Report", UserLogLevel.Error, 0, 0, 0, 0, 0, 0, 0, "Report error", errors.ToString());
}
ReportBase rep1 = new ReportBase(dtErrors, title);
List <Attachment> atts = new List <Attachment>();
try
{
using (MemoryStream ms1 = new MemoryStream(Encoding.UTF8.GetBytes(rep1.GetTXT())))
{
atts.Add(new Attachment(ms1, "integrity-check.txt"));
if (dtU != null)
{
ReportBase rep2 = new ReportBase(dtU, title2);
using (MemoryStream ms2 = new MemoryStream(Encoding.UTF8.GetBytes(rep2.GetTXT())))
{
atts.Add(new Attachment(ms2, "integrity-users.txt"));
sendEmail(db, dtS.Rows[0]["title"].ToString(), recipents, dtL.Rows.Count + " erros de integridade", false, atts);
}
}
else
{
sendEmail(db, dtS.Rows[0]["title"].ToString(), recipents, dtL.Rows.Count + " erros de integridade", false, atts);
}
}
}
catch (Exception ex)
{
db.AddUserLog(LogKey.Report, DateTime.Now, "Report", UserLogLevel.Error, 0, 0, 0, 0, 0, 0, 0, "Erro sending report", ex.Message);
}
}
static public void usersTextReport(IAMDatabase db, DataTable dtS, List <MailAddress> recipents)
{
StringBuilder errors = new StringBuilder();
DataTable dtU = db.Select("select e.*, c.name context_name from entity e inner join context c on c.id = e.context_id where e.deleted = 0 and c.enterprise_id = " + dtS.Rows[0]["enterprise_id"] + " order by c.name, e.full_name");
if ((dtU == null) || (dtU.Rows.Count == 0))
{
return;
}
DataTable dtUsers = new DataTable();
dtUsers.Columns.Add("context_name", typeof(String));
dtUsers.Columns.Add("full_name", typeof(String));
dtUsers.Columns.Add("login", typeof(String));
dtUsers.Columns.Add("create_date", typeof(DateTime));
dtUsers.Columns.Add("last_login", typeof(DateTime));
dtUsers.Columns.Add("locked", typeof(String));
Dictionary <String, String> title = new Dictionary <string, string>();
title.Add("context_name", "Contexto");
title.Add("full_name", "Nome completo");
title.Add("login", "Login");
title.Add("create_date", "Data de criação");
title.Add("last_login", "Ultimo login");
title.Add("locked", "Bloqueado");
List <Int64> fields = new List <Int64>();
DataTable dtF = db.Select("select distinct f.id, f.name, rp.[order] from report_mapping rp inner join field f on rp.field_id = f.id order by rp.[order], f.name");
if ((dtF != null) && (dtF.Rows.Count > 0))
{
foreach (DataRow dr in dtF.Rows)
{
fields.Add((Int64)dr["id"]);
dtUsers.Columns.Add("f_" + dr["id"], typeof(String));
title.Add("f_" + dr["id"], dr["name"].ToString());
}
}
DataTable dtUsers2 = dtUsers.Clone();
String fieldFilter = String.Join(",", fields);
DateTime dateRef = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).AddDays(-1);
foreach (DataRow dU in dtU.Rows)
{
try
{
DataRow newItem = dtUsers.NewRow();
newItem["context_name"] = dU["context_name"];
newItem["full_name"] = dU["full_name"];
newItem["login"] = dU["login"];
newItem["create_date"] = dU["create_date"];
newItem["last_login"] = (dU["last_login"] == DBNull.Value ? DBNull.Value : dU["last_login"]);
newItem["locked"] = (((Boolean)dU["locked"]) ? "Y" : "N");
if (fields.Count > 0)
{
//Primeiro realiza a busca e preenchimento dos dados da entidade
try
{
DataTable dtUserData = db.Select("select efe.field_id, efe.value from [entity] e inner join entity_field efe on efe.entity_id = e.id where e.id = " + dU["id"] + " group by efe.field_id, efe.value");
foreach (DataRow dUD in dtUserData.Rows)
{
if (newItem["f_" + dUD["field_id"]] == DBNull.Value)
{
newItem["f_" + dUD["field_id"]] = dUD["value"];
}
}
}
catch { }
//Primeiro realiza a busca e preenchimento dos dados com as informações dos plugins de entrada
try
{
DataTable dtUserData = db.Select("select ife.field_id, ife.value from [identity] i inner join identity_field ife on ife.identity_id = i.id inner join resource_plugin rp on i.resource_plugin_id = rp.id where rp.enable_import = 1 and rp.permit_add_entity = 1 and i.entity_id = " + dU["id"] + " and ife.field_id in (" + fieldFilter + ") and not exists (select 1 from identity_block_inheritance bi where bi.identity_id = i.id) group by ife.field_id, ife.value");
foreach (DataRow dUD in dtUserData.Rows)
{
if (newItem["f_" + dUD["field_id"]] == DBNull.Value)
{
newItem["f_" + dUD["field_id"]] = dUD["value"];
}
}
}
catch { }
//Depois com os outros plugins
try
{
DataTable dtUserData = db.Select("select ife.field_id, ife.value from [identity] i inner join identity_field ife on ife.identity_id = i.id where i.entity_id = " + dU["id"] + " and ife.field_id in (" + fieldFilter + ") and not exists (select 1 from identity_block_inheritance bi where bi.identity_id = i.id) group by ife.field_id, ife.value");
foreach (DataRow dUD in dtUserData.Rows)
{
if (newItem["f_" + dUD["field_id"]] == DBNull.Value)
{
newItem["f_" + dUD["field_id"]] = dUD["value"];
}
}
}
catch { }
}
dtUsers.Rows.Add(newItem.ItemArray);
//Caso a criação seja do dia anterior ou deste dia inclui na segunda tabela tb.
if (((DateTime)dU["create_date"]).CompareTo(dateRef) == 1)
{
dtUsers2.Rows.Add(newItem.ItemArray);
}
}
catch (Exception ex)
{
errors.AppendLine("Error processing registry: " + ex.Message);
}
}
if (errors.ToString() != "")
{
db.AddUserLog(LogKey.Report, null, "Report", UserLogLevel.Error, 0, 0, 0, 0, 0, 0, 0, "Report error", errors.ToString());
}
ReportBase rep1 = new ReportBase(dtUsers, title);
ReportBase rep2 = new ReportBase(dtUsers2, title);
List <Attachment> atts = new List <Attachment>();
try
{
using (MemoryStream ms1 = new MemoryStream(Encoding.UTF8.GetBytes(rep1.GetTXT())))
using (MemoryStream ms2 = new MemoryStream(Encoding.UTF8.GetBytes(rep1.GetXML("Usuários", ""))))
using (MemoryStream ms3 = new MemoryStream(Encoding.UTF8.GetBytes(rep2.GetTXT())))
using (MemoryStream ms4 = new MemoryStream(Encoding.UTF8.GetBytes(rep2.GetXML("Usuários", ""))))
{
atts.Add(new Attachment(ms1, "all.txt"));
//atts.Add(new Attachment(ms2, "all.xls"));
atts.Add(new Attachment(ms3, "created.txt"));
//atts.Add(new Attachment(ms4, "created.xls"));
sendEmail(db, dtS.Rows[0]["title"].ToString(), recipents, dtUsers2.Rows.Count + " criados deste " + dateRef.ToString("yyyy-MM-dd HH:mm:ss"), false, atts);
}
}
catch (Exception ex)
{
db.AddUserLog(LogKey.Report, DateTime.Now, "Report", UserLogLevel.Error, 0, 0, 0, 0, 0, 0, 0, "Erro sending report", ex.Message);
}
/*
* DataTable created = db.Select("select * from vw_entity_mails where create_date between CONVERT(datetime, convert(varchar(10),DATEADD(DAY, -1, GETDATE()),120) + ' 00:00:00', 120) and CONVERT(datetime, convert(varchar(10),getdate(),120) + ' 23:59:59', 120) order by context_name, full_name");
* DataTable all = db.Select("select * from vw_entity_mails order by context_name, full_name");
* Dictionary<String, String> title = new Dictionary<string, string>();
* title.Add("context_name", "Contexto");
* title.Add("full_name", "Nome completo");
* title.Add("login", "Login");
* title.Add("create_date", "Data de criação");
* title.Add("last_login", "Ultimo login");
* title.Add("mail", "E-mail");
* title.Add("locked", "Bloqueado");
*
* ReportBase rep1 = new ReportBase(created, title);
* ReportBase rep2 = new ReportBase(all, title);
*
* List<Attachment> atts = new List<Attachment>();
*
* using (MemoryStream ms1 = new MemoryStream(Encoding.UTF8.GetBytes(rep1.GetTXT())))
* using (MemoryStream ms2 = new MemoryStream(Encoding.UTF8.GetBytes(rep1.GetXML("Usuários", ""))))
* using (MemoryStream ms3 = new MemoryStream(Encoding.UTF8.GetBytes(rep2.GetTXT())))
* using (MemoryStream ms4 = new MemoryStream(Encoding.UTF8.GetBytes(rep2.GetXML("Usuários", ""))))
* {
* atts.Add(new Attachment(ms1, "created.txt"));
* atts.Add(new Attachment(ms2, "created.xls"));
* atts.Add(new Attachment(ms3, "all.txt"));
* atts.Add(new Attachment(ms4, "all.xls"));
*
* sendEmail(db, "Listagem de usuários em " + DateTime.Now.ToString("dd/MM/yyyy"), recipents, created.Rows.Count + " usuários criados de " + DateTime.Now.AddDays(-1).ToString("dd/MM/yyyy") + " até " + DateTime.Now.ToString("dd/MM/yyyy"), false, atts);
* }*/
}