示例#1
0
        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();
        }
示例#2
0
        //-----------------------------------------------------------------------------------------------------------------------------------
        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);
                }
            }
        }
示例#3
0
        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;
            }
        }
示例#4
0
 //-----------------------------------------------------------------------------------------------------------------------------------
 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();
 }