segunda-feira, 20 de julho de 2015

ReportReader

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using Microsoft.Reporting.WebForms;
using System.Xml.XPath;
using System.Xml;
using System.Data.SqlClient;
using System.Collections.Generic;


namespace ReportReader
{

    public partial class Report : System.Web.UI.Page
    {
        private string reportsPath;      

        ArrayList tipos = new ArrayList();
        private ArrayList listaDataSet = new ArrayList();
        private ArrayList listaCommand = new ArrayList();

        const string ReportDefinitionNS = "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition";
        const string ReportDesignerNS = "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request["rdl"] != null)
            {
                reportsPath = Server.MapPath("~/Report/" + Request["rdl"] + ".rdl");
            }
            else
            {
                reportsPath = Server.MapPath("~/Report/Graficos_por_Regional.rdl");
            }                       
            GerarFiltros();
        }

        private void Filtrar()
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter param;

            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            ReportViewer1.LocalReport.DataSources.Clear();

            using (Stream readStream = new FileStream(reportsPath, FileMode.Open, FileAccess.Read, FileShare.Read))
                ReportViewer1.LocalReport.LoadReportDefinition(readStream);

            XPathDocument xpDoc;
            using (Stream readStream = new FileStream(reportsPath, FileMode.Open, FileAccess.Read, FileShare.Read))
                xpDoc = new XPathDocument(readStream);

            string connSett = ConfigurationManager.AppSettings["connection"].ToString().Trim();
            SqlConnection sqlConn = new SqlConnection(connSett);
            sqlConn.Open();  

            XPathNavigator xpNav = xpDoc.CreateNavigator();
            XPathExpression xpe = xpNav.Compile("/r:Report/r:DataSets/r:DataSet");
            XmlNamespaceManager nsMgr = new XmlNamespaceManager(xpNav.NameTable);
            nsMgr.AddNamespace("r", ReportDefinitionNS);
            nsMgr.AddNamespace("rd", ReportDesignerNS);
            xpe.SetContext(nsMgr);
            foreach (XPathNavigator xpNode in xpNav.Select(xpe))
            {
                parameters = new List<SqlParameter>();

                string dsName = Convert.ToString(xpNode.Evaluate("string( @Name )", nsMgr));

                string cmdTxt = Convert.ToString(xpNode.Evaluate("string( r:Query/r:CommandText )", nsMgr));
                string cmdType = Convert.ToString(xpNode.Evaluate("string( r:Query/r:CommandType )", nsMgr));
               
                if (String.IsNullOrEmpty(dsName) || String.IsNullOrEmpty(cmdTxt))
                    throw new Exception("Could not obtain query for report definition.");


                XPathExpression xpePar = xpNav.Compile("r:Query/r:QueryParameters/r:QueryParameter");
                xpePar.SetContext(nsMgr);
                List<ReportParameter> rp = new List<ReportParameter>();

                foreach (XPathNavigator xpNodePar in xpNode.Select(xpePar))
                {
                    string dsParamName = Convert.ToString(xpNodePar.Evaluate("string( @Name )", nsMgr));

                    string idControle = dsParamName.Replace("@", "");
                    string valor = "";

                    if (pnParameters.FindControl(idControle).ToString().Contains("DropDownList"))
                    {
                        valor = ((DropDownList)pnParameters.FindControl(idControle)).SelectedValue;
                    }
                    if (pnParameters.FindControl(idControle).ToString().Contains("TextBox"))
                    {
                        valor = ((TextBox)pnParameters.FindControl(idControle)).Text;

                    }

                    foreach (ListItem item in tipos)
                    {
                        if (item.Text.Equals(idControle))
                        {
                            switch (item.Value)
                            {
                                case "DateTime":
                                    param = new SqlParameter(idControle, SqlDbType.DateTime);
                                    break;
                                case "Integer":
                                    param = new SqlParameter(idControle, SqlDbType.Int);
                                    break;
                                case "VarChar":
                                    param = new SqlParameter(idControle, SqlDbType.VarChar);
                                    break;
                                case "Char":
                                    param = new SqlParameter(idControle, SqlDbType.Char);
                                    break;
                                default:
                                    param = new SqlParameter(idControle, SqlDbType.VarChar);
                                    break;
                            }
                            if (String.IsNullOrEmpty(valor))
                            {
                                param.Value = DBNull.Value;
                            }
                            else
                            {
                                param.Value = valor;
                            }

                            parameters.Add(param);
                        }
                    }

                    if (String.IsNullOrEmpty(valor))
                    {
                        valor = null;
                    }

                    rp.Add(new ReportParameter(idControle, valor));

                }

              
                using (SqlConnection sqlConn2 = new SqlConnection(connSett))
                {
                    SqlCommand sqlCmd2 = sqlConn2.CreateCommand();                  
                    sqlCmd2.CommandText = cmdTxt;

                    foreach (SqlParameter parm in parameters)
                        sqlCmd2.Parameters.Add(parm);

                    DataTable dataTable = new DataTable();
                    SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd2);
                    sqlAdapter.Fill(dataTable);

                    ReportViewer1.LocalReport.SetParameters(rp);                  
                    ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource(dsName, dataTable ));
                }
            }

            ReportViewer1.LocalReport.ReportPath = reportsPath;
            ReportViewer1.LocalReport.Refresh();

        }


        private void GerarFiltros()
        {
            Table table = new Table();
            TableRow tr = new TableRow();
            TableCell td;

            XPathDocument xpDoc;
            using (Stream readStream = new FileStream(reportsPath, FileMode.Open, FileAccess.Read, FileShare.Read))
                xpDoc = new XPathDocument(readStream);


            XPathNavigator xpNav = xpDoc.CreateNavigator();
            XPathExpression xpe = xpNav.Compile("/r:Report/r:ReportParameters/r:ReportParameter");
            XmlNamespaceManager nsMgr = new XmlNamespaceManager(xpNav.NameTable);
            nsMgr.AddNamespace("r", ReportDefinitionNS);
            nsMgr.AddNamespace("rd", ReportDesignerNS);
            xpe.SetContext(nsMgr);
            int rows = 0;
            foreach (XPathNavigator xpNode in xpNav.Select(xpe))
            {
                string parmName = Convert.ToString(xpNode.Evaluate("string( @Name )", nsMgr));
                string dataType = Convert.ToString(xpNode.Evaluate("string( r:DataType )", nsMgr));
                string prompt = Convert.ToString(xpNode.Evaluate("string( r:Prompt )", nsMgr));
                string nullable = Convert.ToString(xpNode.Evaluate("string( r:Nullable)", nsMgr));
                string dataSet = Convert.ToString(xpNode.Evaluate("string( r:ValidValues/r:DataSetReference/r:DataSetName )", nsMgr));
                string ValueField = null;
                string LabelField = null;

                DropDownList ddl = null;

                if (!String.IsNullOrEmpty(dataSet))
                {
                    ValueField = Convert.ToString(xpNode.Evaluate("string( r:ValidValues/r:DataSetReference/r:ValueField )", nsMgr));
                    LabelField = Convert.ToString(xpNode.Evaluate("string( r:ValidValues/r:DataSetReference/r:LabelField )", nsMgr));

                    XPathExpression xpeDs = xpNav.Compile("/r:Report/r:DataSets/r:DataSet");
                    xpeDs.SetContext(nsMgr);
                    foreach (XPathNavigator xpNodeDs in xpNav.Select(xpeDs))
                    {
                        string dsName = Convert.ToString(xpNodeDs.Evaluate("string( @Name )", nsMgr));
                        string cmdTxt = Convert.ToString(xpNodeDs.Evaluate("string( r:Query/r:CommandText )", nsMgr));
                        string cmdType = Convert.ToString(xpNode.Evaluate("string( r:Query/r:CommandType )", nsMgr));
                        if (dataSet.Equals(dsName))
                        {
                            string connSett = ConfigurationManager.AppSettings["connection"].ToString().Trim();
                            using (SqlConnection sqlConn = new SqlConnection(connSett))
                            {                         

                                SqlCommand sqlCmd = sqlConn.CreateCommand();

                                CommandType tipoComando = new CommandType();
                                if (cmdType.Equals("StoredProcedure"))
                                {
                                    sqlCmd.CommandType = CommandType.StoredProcedure;
                                }
                                else
                                {
                                    sqlCmd.CommandType = CommandType.Text;
                                }
                              
                                sqlCmd.CommandText = cmdTxt;

                                DataTable dataTable = new DataTable();
                                SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
                                sqlAdapter.Fill(dataTable);
                            
                          
                            ddl = new DropDownList();
                            ddl.ID = parmName;
                            ddl.DataValueField = ValueField;
                            ddl.DataTextField = LabelField;
                            ddl.DataSource = dataTable;
                            ddl.DataBind();
                            }
                        }

                    }
                }
             

                Label l = new Label();
                l.Text = prompt;

                TextBox t = new TextBox();
                t.ID = parmName;

                if ((rows % 2) == 0)
                {
                    table.Rows.Add(tr);
                    tr = new TableRow();
                }

                rows++;
                switch (dataType)
                {
                    case "DateTime":
                        t.CssClass = "data";
                        t.Text = DateTime.Now.ToString("dd/MM/yyyy");
                        t.Width = 80;
                        break;
                    case "Integer":
                        t.CssClass = "integer";
                        t.Width = 80;
                        break;
                }

                tipos.Add(new ListItem(parmName, dataType));

                td = new TableCell();
                td.Controls.Add(l);

                tr.Cells.Add(td);

                td = new TableCell();
                if (ddl == null)
                    td.Controls.Add(t);
                else
                    td.Controls.Add(ddl);

                if (String.IsNullOrEmpty(nullable))
                {
                    t.CssClass += " required";
                    RequiredFieldValidator validator = new RequiredFieldValidator();
                    validator.ControlToValidate = parmName;
                    validator.ID = "vl" + parmName;
                    validator.ErrorMessage = "*";
                    validator.ValidationGroup = "Filtro";
                    td.Controls.Add(validator);
                }
                tr.Cells.Add(td);
            }

            Button b = new Button();
            b.ID = "btnFiltrar";
            b.CssClass = "botao";
            b.Click += new EventHandler(Button1_Click);
            b.Text = "Exibir relatório";
            b.CausesValidation = true;
            b.ValidationGroup = "Filtro";

            pnFiltrar.Controls.Add(b);
            table.Rows.Add(tr);

            int totolLinhas = table.Rows.Count;
            if (totolLinhas > 1)
            {
                int totalColunas = table.Rows[0].Cells.Count;
                int totalColunasUltimaLinha = table.Rows[table.Rows.Count - 1].Cells.Count;
                if (totalColunasUltimaLinha < totalColunas)
                {
                    for (int i = totalColunasUltimaLinha; i < totalColunas; i++)
                    {
                        td = new TableCell();
                        tr.Cells.Add(td);
                    }
                }
            }
            pnParameters.Controls.Add(table);
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Filtrar();
        }
    }
}


Postar um comentário