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