Beispiel #1
0
        public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
        {
            RptConCli rpt = new RptConCli();

            rpt.Site = this.Site;
            return(rpt);
        }
        public string pesquisar()
        {
            string sql = "select p.CODCLIENTE,c.NOMEFANTASIA,p.NRPEDIDO,p.DATAEMISSAO, isnull (cast(sum(x.valorcomdesconto)as MONEY),0)VALOR" +
            " from(" +
            "select p.NRPEDIDO, CODPRODUTO, I.QUANTIDADE, I.VALOR, I.DESCONTO," +
                        " isnull(SUM(i.VALOR * i.quantidade),0) valorpedido," +
                        "(isnull(SUM(i.VALOR * i.quantidade),0)) - (isnull(SUM(i.VALOR * i.quantidade),0) * DESCONTO/100)" + "valorcomdesconto" +
                        " from PEDIDO P LEFT JOIN ITEMPEDIDO I ON P.NRPEDIDO = I.NRPEDIDO" +
                                        " INNER JOIN VENDEDOR V ON V.CPF = P.CODVENDEDOR" +
                        " group by p.NRPEDIDO,I.CODPRODUTO, I.QUANTIDADE, I.VALOR, I.DESCONTO)" +
                        " x inner join PEDIDO p on p.nrpedido=x.nrpedido,cliente as c" +
                        " where p.codcliente=c.cnpj";
            string groupBy = " group by p.NRPEDIDO,  p.DATAEMISSAO,p.CODCLIENTE,c.NomeFantasia ";

            // pesquisa por nrPedido
            if (!string.IsNullOrEmpty(txtNumPed.Text))
            {
                sql += " and p.nrPedido = " + txtNumPed.Text;
            }

            // pesquisa por tipo pedido
            if (!string.IsNullOrEmpty(cmBxTipoPed.Text))
            {
                if (cmBxTipoPed.Text=="N = Normal")
                {
                    sql += " and p.tipo = 'N'";
                }
                else sql += " and p.tipo = 'C'";

            }

            // pesquisa por situacao pedido
            if (rdbtnEfetivado.Checked)
            {
                sql += " and p.situacao ='E'";

            }

            if (rdbtnPendente.Checked)
            {
                sql += " and p.situacao ='P'";
            }

            if (rdbtnCancelado.Checked)
            {
                sql += " and p.situacao = 'C'";
            }

            //Pesquisa por data
            if (dttmDataPedido1.Checked && dttmDataPedido2.Checked)
            {
                string formatData1=dttmDataPedido1.Value.Year + "-" + dttmDataPedido1.Value.Month + "-" + dttmDataPedido1.Value.Day;
                string formatData2 = dttmDataPedido2.Value.Year + "-" + dttmDataPedido2.Value.Month + "-" + dttmDataPedido2.Value.Day;

                sql += " and p.dataEmissao between '" + formatData1 + "' and '" + formatData2+ "'";

                RptConCli rpt = new RptConCli();

            }
            else if (dttmDataPedido1.Checked)
                {
                    string formatData = dttmDataPedido1.Value.Year + "-" + dttmDataPedido1.Value.Month + "-" + dttmDataPedido1.Value.Day;

                    sql += " and p.dataEmissao= '"+formatData+"'";
                }
            else if (dttmDataPedido2.Checked)
            {
                string formatData = dttmDataPedido2.Value.Year + "-" + dttmDataPedido2.Value.Month + "-" + dttmDataPedido2.Value.Day;

                sql += " and p.dataEmissao <='" + formatData + "'";
            }

            // Pesquisa por CNPJ Cliente
            if (!string.IsNullOrEmpty(txtCnpjCli.Text))
            {

                sql += " and c.cnpj =" + txtCnpjCli.Text;

            }
            //Pesquisa por área de atuação do cliente
            if (!string.IsNullOrEmpty(cmbAreaAtuCli.Text))
            {
                sql += " and c.areaAtuacao ='" + cmbAreaAtuCli.Text + "'";

            }
            //Pesquisa por razão social do cliente
            if (!string.IsNullOrEmpty(txtRazaoSocialCli.Text))
            {
                sql += " and c.RazaoSocial like '%" + txtRazaoSocialCli.Text + "%' ";
            }
            if (!string.IsNullOrEmpty(txtNomeFantasiaCli.Text))
            {
                sql += " and c.NomeFantasia like '%" + txtNomeFantasiaCli.Text + "%' ";
            }

            sql += groupBy;

             string c = ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString;

             SqlConnection conn = new SqlConnection(c);
             conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader reader = cmd.ExecuteReader();

                DataTable table = new DataTable();
                table.Load(reader);

                dtgrdConCli.DataSource = table;

                return sql;
        }
 public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
 {
     RptConCli rpt = new RptConCli();
     rpt.Site = this.Site;
     return rpt;
 }
Beispiel #4
0
        private void FrmRelGeral_Load(object sender, EventArgs e)
        {
            try
            {
                #region Relatorio Cliente


                if (_princ == "FrmConCli")
                {
                    //Instancio o FormConsulta
                    FrmConCli x            = (FrmConCli)_pdv;
                    RptConCli objRptConPDV = new RptConCli();

                    //Instancio o Relatorio
                    RptConCli objRptConCli = new RptConCli();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();

                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datatable criado no dataset
                    dtAdapter.Fill(oDataset, "RelCliente");

                    objRptConCli.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource     = objRptConCli;
                }
                #endregion

                #region Relatorio Pedido
                if (_princ == "FrmConPDV")
                {
                    ImprimirRelPed();
                }
                #endregion

                #region relatorio vendedor

                if (_princ == "FrmConVen")
                {
                    //Instancio o FormConsulta
                    FrmConVen x = (FrmConVen)_pdv;

                    RptConVen objRptConPDV = new RptConVen();

                    //Instancio o Relatorio
                    RptConVen objRptConVen = new RptConVen();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelVendedor");

                    objRptConVen.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource     = objRptConVen;
                }

                #endregion

                #region relatorio produto

                if (_princ == "FrmConProd")
                {
                    //Instancio o FormConsulta
                    FrmConProd x = (FrmConProd)_pdv;

                    //Instancio o Relatorio
                    RptConProd objRptConProd = new RptConProd();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelProduto");

                    objRptConProd.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource     = objRptConProd;
                }

                #endregion

                #region relatorio Estoque

                if (_princ == "FrmConEstProd")
                {
                    //Instancio o FormConsulta
                    FrmConEstProd x = (FrmConEstProd)_pdv;

                    //Instancio o Relatorio
                    RptConEstoque objRptConEst = new RptConEstoque();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelEstoque");

                    objRptConEst.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource     = objRptConEst;
                }

                #endregion

                if (_princ == "FrmEmiNF")
                {
                    //Instancio o FormConsulta
                    FrmLibPDV x = (FrmLibPDV)_pdv;

                    //Instancio o Relatorio
                    RptConNF objRptConNF = new RptConNF();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();


                    string sql = string.Format("SELECT nf.*, ((nf.icms * itnf.valor)/100)*itnf.Quantidade as valoricms , itnf.*,((itnf.IPI * itnf.valor)/100)*itnf.Quantidade as valorIPI,tra.NOME as DescricaoTransp, " +
                                               " tra.UF as UfTrans, tra.CNPJ as CnpjTrans, tra.ENDERECO as EnderecoTrans, tra.MUNICIPIO as MunicipioTrans, tra.IE as IeTrans, " +
                                               " ( " +
                                               " select SUM(((n.icms * i.valor)/100)*i.Quantidade) " +
                                               " from NOTAFISCAL n inner join ItemNotaFiscal i on n.NrNotaFiscal = i.NrNotaFiscal " +
                                               " where n.NrNotaFiscal = nf.nrNotaFiscal " +
                                               " )total_ICMS, " +
                                               " ( " +
                                               " 	select SUM(((i.IPI * i.valor)/100)*i.Quantidade) "+
                                               " from NOTAFISCAL n inner join ItemNotaFiscal i on n.NrNotaFiscal = i.NrNotaFiscal " +
                                               " where n.NrNotaFiscal = nf.nrNotaFiscal " +
                                               " )total_IPI " +
                                               " FROM NOTAFISCAL nf INNER JOIN ItemNotaFiscal itnf ON nf.NrNotaFiscal = itnf.NrNotaFiscal " +
                                               " 					INNER JOIN TRANSPORTADORA tra ON nf.CodTransportadora = tra.CNPJ WHERE nf.NrPedido = {0}", x.txtbtnPedido.Text);

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(sql, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelNotaFiscal");

                    objRptConNF.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource     = objRptConNF;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void FrmRelGeral_Load(object sender, EventArgs e)
        {
            try
            {
                #region Relatorio Cliente

                if (_princ == "FrmConCli")
                {
                    //Instancio o FormConsulta
                    FrmConCli x = (FrmConCli)_pdv;
                    RptConCli objRptConPDV = new RptConCli();

                    //Instancio o Relatorio
                    RptConCli objRptConCli = new RptConCli();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();

                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datatable criado no dataset
                    dtAdapter.Fill(oDataset, "RelCliente");

                    objRptConCli.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource = objRptConCli;
                }
                #endregion

                #region Relatorio Pedido
                if (_princ == "FrmConPDV")
                {

                    ImprimirRelPed();

                }
                #endregion

                #region relatorio vendedor

                if (_princ == "FrmConVen")
                {
                    //Instancio o FormConsulta
                    FrmConVen x = (FrmConVen)_pdv;

                    RptConVen objRptConPDV = new RptConVen();

                    //Instancio o Relatorio
                    RptConVen objRptConVen = new RptConVen();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelVendedor");

                    objRptConVen.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource = objRptConVen;
                }

                #endregion

                #region relatorio produto

                if (_princ == "FrmConProd")
                {
                    //Instancio o FormConsulta
                    FrmConProd x = (FrmConProd)_pdv;

                    //Instancio o Relatorio
                    RptConProd objRptConProd = new RptConProd();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelProduto");

                    objRptConProd.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource = objRptConProd;
                }

                #endregion

                #region relatorio Estoque

                if (_princ == "FrmConEstProd")
                {
                    //Instancio o FormConsulta
                    FrmConEstProd x = (FrmConEstProd)_pdv;

                    //Instancio o Relatorio
                    RptConEstoque objRptConEst = new RptConEstoque();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    //Recebo a String SQL feita na tela de consulta
                    string StringConnection = x.pesquisar();

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(StringConnection, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelEstoque");

                    objRptConEst.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource = objRptConEst;
                }

                #endregion

                if (_princ == "FrmEmiNF")
                {
                    //Instancio o FormConsulta
                    FrmLibPDV x = (FrmLibPDV)_pdv;

                    //Instancio o Relatorio
                    RptConNF objRptConNF = new RptConNF();

                    //Instancio o Dataset
                    COMERCIALDataSet oDataset = new COMERCIALDataSet();

                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
                    //Crio a Conexão
                    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);

                    //Abro a conexão
                    sqlcon.Open();

                    string sql = string.Format("SELECT nf.*, ((nf.icms * itnf.valor)/100)*itnf.Quantidade as valoricms , itnf.*,((itnf.IPI * itnf.valor)/100)*itnf.Quantidade as valorIPI,tra.NOME as DescricaoTransp, " +
                        " tra.UF as UfTrans, tra.CNPJ as CnpjTrans, tra.ENDERECO as EnderecoTrans, tra.MUNICIPIO as MunicipioTrans, tra.IE as IeTrans, "+
                        " ( "+
                        " select SUM(((n.icms * i.valor)/100)*i.Quantidade) " +
                        " from NOTAFISCAL n inner join ItemNotaFiscal i on n.NrNotaFiscal = i.NrNotaFiscal "+
                        " where n.NrNotaFiscal = nf.nrNotaFiscal "+
                        " )total_ICMS, "+
                        " ( "+
                        " 	select SUM(((i.IPI * i.valor)/100)*i.Quantidade) " +
                        " from NOTAFISCAL n inner join ItemNotaFiscal i on n.NrNotaFiscal = i.NrNotaFiscal "+
                        " where n.NrNotaFiscal = nf.nrNotaFiscal "+
                        " )total_IPI "+
                        " FROM NOTAFISCAL nf INNER JOIN ItemNotaFiscal itnf ON nf.NrNotaFiscal = itnf.NrNotaFiscal " +
                        " 					INNER JOIN TRANSPORTADORA tra ON nf.CodTransportadora = tra.CNPJ WHERE nf.NrPedido = {0}", x.txtbtnPedido.Text);

                    SqlDataAdapter dtAdapter = new SqlDataAdapter(sql, sqlcon);

                    //Localiso o datateble criado no dataset
                    dtAdapter.Fill(oDataset, "RelNotaFiscal");

                    objRptConNF.SetDataSource(oDataset);

                    //atribiu o resultado ao CristalReportView
                    crstlRprtVwrRel.DisplayGroupTree = false;
                    crstlRprtVwrRel.ReportSource = objRptConNF;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Beispiel #6
0
        public string pesquisar()
        {
            string sql = "select p.CODCLIENTE,c.NOMEFANTASIA,p.NRPEDIDO,p.DATAEMISSAO, isnull (cast(sum(x.valorcomdesconto)as MONEY),0)VALOR" +
                         " from(" +
                         "select p.NRPEDIDO, CODPRODUTO, I.QUANTIDADE, I.VALOR, I.DESCONTO," +
                         " isnull(SUM(i.VALOR * i.quantidade),0) valorpedido," +
                         "(isnull(SUM(i.VALOR * i.quantidade),0)) - (isnull(SUM(i.VALOR * i.quantidade),0) * DESCONTO/100)" + "valorcomdesconto" +
                         " from PEDIDO P LEFT JOIN ITEMPEDIDO I ON P.NRPEDIDO = I.NRPEDIDO" +
                         " INNER JOIN VENDEDOR V ON V.CPF = P.CODVENDEDOR" +
                         " group by p.NRPEDIDO,I.CODPRODUTO, I.QUANTIDADE, I.VALOR, I.DESCONTO)" +
                         " x inner join PEDIDO p on p.nrpedido=x.nrpedido,cliente as c" +
                         " where p.codcliente=c.cnpj";
            string groupBy = " group by p.NRPEDIDO,  p.DATAEMISSAO,p.CODCLIENTE,c.NomeFantasia ";

            // pesquisa por nrPedido
            if (!string.IsNullOrEmpty(txtNumPed.Text))
            {
                sql += " and p.nrPedido = " + txtNumPed.Text;
            }

            // pesquisa por tipo pedido
            if (!string.IsNullOrEmpty(cmBxTipoPed.Text))
            {
                if (cmBxTipoPed.Text == "N = Normal")
                {
                    sql += " and p.tipo = 'N'";
                }
                else
                {
                    sql += " and p.tipo = 'C'";
                }
            }


            // pesquisa por situacao pedido
            if (rdbtnEfetivado.Checked)
            {
                sql += " and p.situacao ='E'";
            }

            if (rdbtnPendente.Checked)
            {
                sql += " and p.situacao ='P'";
            }

            if (rdbtnCancelado.Checked)
            {
                sql += " and p.situacao = 'C'";
            }


            //Pesquisa por data
            if (dttmDataPedido1.Checked && dttmDataPedido2.Checked)
            {
                string formatData1 = dttmDataPedido1.Value.Year + "-" + dttmDataPedido1.Value.Month + "-" + dttmDataPedido1.Value.Day;
                string formatData2 = dttmDataPedido2.Value.Year + "-" + dttmDataPedido2.Value.Month + "-" + dttmDataPedido2.Value.Day;

                sql += " and p.dataEmissao between '" + formatData1 + "' and '" + formatData2 + "'";

                RptConCli rpt = new RptConCli();
            }
            else if (dttmDataPedido1.Checked)
            {
                string formatData = dttmDataPedido1.Value.Year + "-" + dttmDataPedido1.Value.Month + "-" + dttmDataPedido1.Value.Day;

                sql += " and p.dataEmissao= '" + formatData + "'";
            }
            else if (dttmDataPedido2.Checked)
            {
                string formatData = dttmDataPedido2.Value.Year + "-" + dttmDataPedido2.Value.Month + "-" + dttmDataPedido2.Value.Day;

                sql += " and p.dataEmissao <='" + formatData + "'";
            }

            // Pesquisa por CNPJ Cliente
            if (!string.IsNullOrEmpty(txtCnpjCli.Text))
            {
                sql += " and c.cnpj =" + txtCnpjCli.Text;
            }
            //Pesquisa por área de atuação do cliente
            if (!string.IsNullOrEmpty(cmbAreaAtuCli.Text))
            {
                sql += " and c.areaAtuacao ='" + cmbAreaAtuCli.Text + "'";
            }
            //Pesquisa por razão social do cliente
            if (!string.IsNullOrEmpty(txtRazaoSocialCli.Text))
            {
                sql += " and c.RazaoSocial like '%" + txtRazaoSocialCli.Text + "%' ";
            }
            if (!string.IsNullOrEmpty(txtNomeFantasiaCli.Text))
            {
                sql += " and c.NomeFantasia like '%" + txtNomeFantasiaCli.Text + "%' ";
            }


            sql += groupBy;


            string c = ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString;

            SqlConnection conn = new SqlConnection(c);

            conn.Open();
            SqlCommand    cmd    = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();

            DataTable table = new DataTable();

            table.Load(reader);


            dtgrdConCli.DataSource = table;

            return(sql);
        }