private static string MontaWhere(MPSfwk.Model.Audits aud_param)
{
string Server = " AND ServerName IN ({0}) ";
string Classe = " AND ClasseName IN ({0}) ";
string Geracao = " AND convert(datetime,stuff(stuff(stuff(GeracaoDate, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))" +
" BETWEEN convert(datetime,stuff(stuff(stuff('{0}', 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))" +
" AND convert(datetime,stuff(stuff(stuff('{1}', 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))";
System.Text.StringBuilder sbWhere = new System.Text.StringBuilder();
if (!string.IsNullOrEmpty(aud_param.IDServer))
sbWhere.AppendFormat(Server, aud_param.IDServer.ToString());
if (!string.IsNullOrEmpty(aud_param.IDClasse))
sbWhere.AppendFormat(Classe, aud_param.IDClasse.ToString());
if ((!string.IsNullOrEmpty(aud_param.DTGeracaoIni)) &&
(!string.IsNullOrEmpty(aud_param.DTGeracaoFim)) &&
(aud_param.DTGeracaoIni != "000000") &&
(aud_param.DTGeracaoFim != "235900")
)
sbWhere.AppendFormat(Geracao, aud_param.DTGeracaoIni.ToString(), aud_param.DTGeracaoFim.ToString());
if (sbWhere.Length > 0)
{
sbWhere.Remove(0, 5);
sbWhere.Insert(1, " Where ", 1);
}
else
{
return string.Empty;
}
return sbWhere.ToString();
}
//-----------------------------------------------------------------------------------------------------------------------------------
public static void ListaDTsGeracao(string[] ordBY, MPSfwk.Model.Audits aud, ListBox _lstSrv, ListBox _lstCls, DropDownList _drpI, DropDownList _drpF)
{
//limpa as drpboxs...
_drpI.Items.Clear();
_drpF.Items.Clear();
List<MPSfwk.Model.Audits> DatasAudit;
string[] arrCls0 = null;
string[] arrCls1 = null;
// Cria a string com os itens selecionados para filtrar no Where...
string strHosts = setListSel(_lstSrv);
string strClasses = setListSel(_lstCls);
if (strHosts != "")
aud.IDServer = strHosts;
if (strClasses != "")
aud.IDClasse = strClasses;
//Cria a Lista de pesquisa no DB passando os filtros ordBY[0]...
DatasAudit = SqlServer.AuditXML.lstAudits(aud, 1, ordBY[0]);
//Cria o array para comparação e retirada das diferenças
var qry = (from m in DatasAudit select m).Distinct().ToList();
arrCls0 = qry.Select(x => x.CVGeracao).ToArray();
//Cria a Lista de pesquisa no DB passando os filtros ordBY[1]...
DatasAudit = SqlServer.AuditXML.lstAudits(aud, 1, ordBY[1]);
//Cria o array para comparação e retirada das diferenças
qry = (from m in DatasAudit select m).Distinct().ToList();
arrCls1 = qry.Select(x => x.CVGeracao).ToArray();
//Pega somente as datas diferentes..
var inter = arrCls0.Intersect(arrCls1);
foreach (var s in inter)
{
// Testa se for o mesmo componente, add apenas uma vez...
if (_drpI.UniqueID == _drpF.UniqueID) { _drpI.Items.Add(s); }
else
{
_drpI.Items.Add(s);
_drpF.Items.Add(s);
}
}
}
public static List<Audits> lstAudits(MPSfwk.Model.Audits aud_param, int tipLista, string ordBY)
{
SqlCommand comm = new SqlCommand();
if (tipLista == 0)
{
comm.CommandText = @"SELECT
ServerName,
ClasseName,
GeracaoDate,
UltimaAcaoDate
FROM ASPNETDB.dbo.ds_audit_xml" + MontaWhere(aud_param) +
" ORDER BY GeracaoDate DESC";
}
else if (tipLista == 1)
{
comm.CommandText = @"SELECT
ServerName,
ClasseName,
GeracaoDate,
convert(datetime,stuff(stuff(stuff(GeracaoDate, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')) ConvGeracaoDate
FROM ASPNETDB.dbo.ds_audit_xml" + MontaWhere(aud_param) +
" GROUP BY ServerName, ClasseName, GeracaoDate" +
" ORDER BY convert(datetime,stuff(stuff(stuff(GeracaoDate, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')) " + ordBY;
}
else if (tipLista == 2)
{
comm.CommandText = @"( SELECT DISTINCT ServerName, ClasseName, MAX(GeracaoDate)
FROM ASPNETDB.dbo.ds_audit_xml
WHERE GeracaoDate like '%" + aud_param.DTGeracaoFim + "%'" +
" GROUP BY ServerName, ClasseName, GeracaoDate " +
") ORDER BY ServerName, ClasseName " + ordBY;
}
else if (tipLista == 3)
{
comm.CommandText = @"SELECT
ServerName,
ClasseName,
GeracaoDate,
UltimaAcaoDate
FROM ASPNETDB.dbo.vw_ds_audit_xml_Ativos7days
WHERE GeracaoDate like '" + aud_param.IDGeracao + "%' " +
" ORDER BY ClasseName ASC, ServerName ASC";
}
else if (tipLista == 4)
{
comm.CommandText = @"SELECT
VALUE + ' ' + SEL
FROM ASPNETDB.dbo.ds_ListHosts
WHERE CHK = '1'
AND VALUE NOT IN (SELECT DISTINCT ServerName
FROM ASPNETDB.dbo.vw_ds_audit_xml_Ativos7days
WHERE GeracaoDate like '" + aud_param.IDGeracao + "%')";
}
comm.CommandType = CommandType.Text;
using (SqlDataReader dataReader = SQLServer.DataAccess.ExecuteReader(comm))
{
List<Audits> _lst = new List<Audits>();
while (dataReader.Read())
{
Audits aud = new Audits();
aud.IDServer = dataReader.GetString(0);
if (tipLista != 4)
{
aud.IDClasse = dataReader.GetString(1);
aud.IDGeracao = dataReader.GetString(2);
}
if ((tipLista == 0) || (tipLista == 3))
{ aud.DataUltimaAcao = dataReader.GetDateTime(3); }
else if (tipLista == 1)
{ aud.CVGeracao = dataReader.GetDateTime(3).ToString(); }
_lst.Add(aud);
}
return _lst;
}
}
//-----------------------------------------------------------------------------------------------------------------------------------
public static void DbXMLBindGrid(MembershipUser currentUser, MPSfwk.Model.Audits aud, GridView _grd)
{
//Busca o XML da Geração selecionada...
XmlDocument xmlDB = SqlServer.AuditXML.LerXML( aud.IDClasse,
aud.IDServer,
aud.IDGeracao);
//
string auxFile = ConfigurationManager.AppSettings["XMLData"] + currentUser.UserName.Replace("\\", "_") + "_tmp.xml";
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(xmlDB.InnerXml);
xdoc.Save(auxFile);
//
DataSet xmlDataSet;
xmlDataSet = new DataSet();
xmlDataSet.ReadXml(auxFile);
//
if (xmlDataSet.Tables.Count == 0)
{ _grd.DataSource = null; }
else
{ _grd.DataSource = xmlDataSet; }
_grd.DataBind();
}