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; }
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; } }
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); }