private void VerVendas_Shown(object sender, EventArgs e)
{
txtNumVenda.Text = idvenda.ToString().PadLeft(5, '0');
txtNumVenda.Select(txtNumVenda.Text.Length, txtNumVenda.Text.Length);
txtNome.Text = c.RetornaQuery("select nome_cliente from vendas where id=" + idvenda.ToString(), "nome_cliente");
txtFpgto.Text = c.RetornaQuery("select fp.descricao from vendas left join forma_pgto fp on fp.id = vendas.id_forma_pgto where vendas.id=" + idvenda.ToString(), "descricao");
decimal desconto = decimal.Parse(c.RetornaQuery("select desconto from vendas where id=" + idvenda.ToString(), "desconto"));
txtValorTotal.Text = string.Format("{0:C}", Convert.ToDecimal(c.RetornaQuery("select valor_total from vendas where id=" + idvenda.ToString(), "valor_total"))).Replace("R$ ", "");
//preenche grid ...
string select = "select ROW_NUMBER() over(order by vi.id_venda) as 'Item', (select case when p.descricao is not null then p.descricao else 'adicional: ' + a.descricao end) as 'Descrição', f.nome as 'Funcionário', format(vi.vl_total_item, 'c', 'pt-br') as 'Valor Ítem' from vendas_itens vi left outer join procedimento p on p.id = vi.id_procedimento left outer join adicional a on a.id = vi.id_adicional left outer join vendas v on v.id = vi.id_venda left outer join funcionario f on f.id = vi.id_func where vi.id_venda = " + idvenda;
var conn = new OdbcConnection();
conn.ConnectionString = c.getConexaoString();
conn.Open();
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(select, conn);
OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
grdItens.ReadOnly = true;
grdItens.DataSource = ds.Tables[0];
this.grdItens.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
this.grdItens.MultiSelect = false;
grdItens.ClearSelection();
if (desconto > 0)
{
txtValorTotComDesconto.Visible = true;
lblRSDesconto.Visible = true;
lblTotDesconto.Visible = true;
decimal auxTotal = decimal.Parse(txtValorTotal.Text) - desconto;
lblDesconto.Visible = true;
txtDesconto.Text = string.Format("{0:C}", Convert.ToDecimal(desconto.ToString()));
txtDesconto.Visible = true;
txtValorTotComDesconto.Text = string.Format("{0:C}", Convert.ToDecimal(auxTotal.ToString())).Replace("R$ ", "");
}
}
private void BtnAddProcedimento_Click(object sender, EventArgs e)
{
if (cboFuncionarios.SelectedIndex != -1)
{
if (cboProcedimento.SelectedIndex != -1)
{
int procId = Convert.ToInt32(cboProcedimento.SelectedValue.ToString());
int idFunc = Convert.ToInt32(cboFuncionarios.SelectedValue.ToString());
string[] row1 = new string[] { (grdVenda.Rows.Count + 1).ToString(), c.RetornaQuery("select descricao from procedimento where id=" + procId.ToString(), "descricao").ToString(), c.RetornaQuery("select nome from funcionario where id=" + idFunc.ToString(), "nome"), string.Format("{0:C}", Convert.ToDecimal(c.RetornaQuery("select valor from procedimento where id=" + procId.ToString(), "valor").ToString())), procId.ToString(), "procedimento", idFunc.ToString() };
grdVenda.Rows.Add(row1);
CalculaPrecoTotal();
cboProcedimento.SelectedIndex = -1;
}
else
{
MessageBox.Show("Selecione um procedimento da lista!", "Procedimento", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
else
{
MessageBox.Show("Selecione um funcionário para este procedimento!", "Funcionário", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public int RetornaIdVendaAtual()
{
conexao c = new conexao();
int id = Convert.ToInt32(c.RetornaQuery("select isnull(max(id),0) as id from vendas", "id"));
id++;
return(id);
}
public void preencheGridDespesasGerais(String dtIni = null, String dtFim = null)
{
String sQuery;
if (dtIni == null)
{
dtIni = dtPickerInicial.Value.ToString("yyyy-MM-dd");
}
else
{
dtPickerInicial.Value = new DateTime(int.Parse(dtIni.Split('-')[0]), int.Parse(dtIni.Split('-')[1]), int.Parse(dtIni.Split('-')[2]));
}
if (dtFim == null)
{
dtFim = dtPickerFinal.Value.ToString("yyyy-MM-dd");
}
else
{
dtPickerFinal.Value = new DateTime(int.Parse(dtFim.Split('-')[0]), int.Parse(dtFim.Split('-')[1]), int.Parse(dtFim.Split('-')[2]));
}
//total periodo
String stotal = "select isnull(cast(sum(valor) as varchar),0) as 'valor' from despesas where convert(date, data, 103) >='" + dtIni + "' and convert(date, data, 103)<='" + dtFim + "' and deletado <> 1";
txtPrecoTotal.Text = string.Format(CultureInfo.GetCultureInfo("pt-BR"), "{0:C}", Convert.ToDecimal(c.RetornaQuery(stotal, "valor").Replace(",", "").Replace(".", ",")));
txtPrecoTotal.Text = txtPrecoTotal.Text.Replace("R$ ", "");
sQuery = "select id, descricao as 'Descrição', isnull(cast(valor as varchar),0) as 'Valor', convert(varchar,data,103) as 'Data Compra' from despesas where convert(date, data, 103) >='" + dtIni + "' and convert(date, data, 103)<='" + dtFim + "' and deletado <> 1 order by data";
//PREENCHE O GRID..
DataSet ds = null;
string select = sQuery;
var conn = new OdbcConnection();
conn.ConnectionString = c.getConexaoString();
conn.Open();
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(select, conn);
OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter);
ds = new DataSet();
dataAdapter.Fill(ds);
grdDespesasGerais.ReadOnly = true;
grdDespesasGerais.DataSource = ds.Tables[0];
grdDespesasGerais.CurrentCell = null;
grdDespesasGerais.Columns[0].Visible = false;
int g;
for (g = 0; g < grdDespesasGerais.RowCount; g++)
{
grdDespesasGerais[2, g].Value = string.Format(CultureInfo.GetCultureInfo("pt-BR"), "{0:C}", Convert.ToDecimal(grdDespesasGerais[2, g].Value.ToString().Replace(".", ",")));
}
grdDespesasGerais.ClearSelection();
conn.Close();
}
private void BtnBuscar_Click(object sender, EventArgs e)
{
DataSet ds = null;
var conn = new OdbcConnection();
conn.ConnectionString = c.getConexaoString();
conn.Open();
String dtInic = dtIni.Value.ToString("yyyy-MM-dd");
String dtFinal = dtFim.Value.ToString("yyyy-MM-dd");
if (Convert.ToDateTime(dtInic) <= Convert.ToDateTime(dtFinal))
{
//grdResumoPgtos
String sResumoPgtos;
sResumoPgtos = "select fp.descricao as 'Forma Pagamento', count(id_forma_pgto) as 'Qtde. Vendas', cast(sum(valor_total - desconto) as varchar) as 'Valor Total Vendido' from vendas left join forma_pgto fp on fp.id = vendas.id_forma_pgto";
sResumoPgtos += " where isnull(cancelado,0) <> 1 and convert(date,data,103) >= '" + dtInic + "' and convert(date,data,103) <= '" + dtFinal + "'";
sResumoPgtos += " group by id_forma_pgto, fp.descricao ";
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(sResumoPgtos, conn);
OdbcCommandBuilder commandBuilder = new OdbcCommandBuilder(dataAdapter);
ds = new DataSet();
dataAdapter.Fill(ds);
grdResumoPgtos.ReadOnly = true;
grdResumoPgtos.DataSource = ds.Tables[0];
grdResumoPgtos.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoPgtos.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoPgtos.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoPgtos.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoPgtos.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoPgtos.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
for (g = 0; g < grdResumoPgtos.RowCount; g++)
{
grdResumoPgtos[2, g].Value = string.Format(CultureInfo.GetCultureInfo("pt-BR"), "{0:C}", Convert.ToDecimal(grdResumoPgtos[2, g].Value.ToString().Replace(".", ",")));
}
grdResumoPgtos.ClearSelection();
//fim grdResumoPgtos
conn.Close();
//grdRel
String sRel;
sRel = "RelatorioVendasItens '" + dtInic + "', '" + dtFinal + "'";
OdbcDataAdapter dataAdapter2 = new OdbcDataAdapter(sRel, conn);
OdbcCommandBuilder commandBuilder2 = new OdbcCommandBuilder(dataAdapter2);
DataSet ds2 = new DataSet();
dataAdapter2.Fill(ds2);
grdRel.ReadOnly = true;
grdRel.DataSource = ds2.Tables[0];
grdRel.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdRel.ClearSelection();
//fim grdRel
//grdResumoItens
String sResumoItens;
sResumoItens = "select p.descricao as 'Descrição', count(id_procedimento) as Quantidade, cast(sum(vl_total_item) as varchar) as 'Valor total'";
sResumoItens += " from vendas_itens left join vendas v on v.id = vendas_itens.id_venda left join procedimento p on p.id = vendas_itens.id_procedimento";
sResumoItens += " where id_adicional is null and isnull(v.cancelado,0) <> 1 and convert(date,data,103) >= '" + dtInic + "' and convert(date,data,103) <= '" + dtFinal + "'";
sResumoItens += " group by id_procedimento, p.descricao";
OdbcDataAdapter dataAdapter3 = new OdbcDataAdapter(sResumoItens, conn);
OdbcCommandBuilder commandBuilder3 = new OdbcCommandBuilder(dataAdapter3);
DataSet ds3 = new DataSet();
dataAdapter3.Fill(ds3);
grdResumoItens.ReadOnly = true;
String sResumoItens2;
sResumoItens2 = "select 'adicional: ' + a.descricao as 'Descrição', count(id_adicional) as Quantidade, cast(sum(vl_total_item) as varchar) as 'Valor total' from vendas_itens ";
sResumoItens2 += " left join vendas v on v.id = vendas_itens.id_venda left join adicional a on a.id = vendas_itens.id_adicional";
sResumoItens2 += " where id_procedimento is null and isnull(v.cancelado,0) <> 1 and convert(date,data,103) >= '" + dtInic + "' and convert(date,data,103) <= '" + dtFinal + "'";
sResumoItens2 += " group by id_adicional, a.descricao";
OdbcDataAdapter dataAdapter4 = new OdbcDataAdapter(sResumoItens2, conn);
OdbcCommandBuilder commandBuilder4 = new OdbcCommandBuilder(dataAdapter4);
DataSet ds4 = new DataSet();
dataAdapter4.Fill(ds4);
String sResumoItens3;
sResumoItens3 = "select 'Desconto' as 'Descrição', count(desconto) as Quantidade , isnull(cast(sum(desconto) as varchar),0) as 'Valor total' from vendas ";
sResumoItens3 += " where isnull(cancelado,0) <> 1 and convert(date,data,103) >= '" + dtInic + "' and convert(date,data,103) <= '" + dtFinal + "' and desconto <> 0";
OdbcDataAdapter dataAdapter5 = new OdbcDataAdapter(sResumoItens3, conn);
OdbcCommandBuilder commandBuilder5 = new OdbcCommandBuilder(dataAdapter5);
DataSet ds5 = new DataSet();
dataAdapter5.Fill(ds5);
grdResumoItens.ReadOnly = true;
DataTable datasource = ds3.Tables[0];
datasource.Merge(ds4.Tables[0]);
datasource.Merge(ds5.Tables[0]);
grdResumoItens.DataSource = datasource;
grdResumoItens.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoItens.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoItens.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoItens.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoItens.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
grdResumoItens.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
for (g = 0; g < grdResumoItens.RowCount; g++)
{
if (grdResumoItens[0, g].Value.ToString() == "Desconto")
{
if (grdResumoItens[1, g].Value.ToString() == "0")
{
grdResumoItens.Rows.Remove(grdResumoItens.Rows[g]);
}
else
{
grdResumoItens[2, g].Value = "- " + string.Format(CultureInfo.GetCultureInfo("pt-BR"), "{0:C}", Convert.ToDecimal(grdResumoItens[2, g].Value.ToString().Replace(".", ",")));
}
}
else
{
grdResumoItens[2, g].Value = string.Format(CultureInfo.GetCultureInfo("pt-BR"), "{0:C}", Convert.ToDecimal(grdResumoItens[2, g].Value.ToString().Replace(".", ",")));
}
}
grdResumoItens.ClearSelection();
//fim grdRel
//
int j;
numero_de_pedidos = 0;
for (j = 0; j < grdRel.RowCount; j++)
{
if (grdRel.RowCount > 0 && j == 1)
{
numero_de_pedidos = 1;
}
if (j > 0)
{
if (Convert.ToInt32(grdRel[1, j].Value) != Convert.ToInt32(grdRel[1, j - 1].Value))
{
numero_de_pedidos++;
}
}
if (grdRel[4, j].Value.ToString().Trim() == "Desconto")
{
btemDesconto = true;
}
}
if (grdRel.RowCount == 1 && numero_de_pedidos == 0)
{
numero_de_pedidos = 1;
}
toolStripStatusLabel1.Text = "Número de vendas no período: " + numero_de_pedidos.ToString();
String sQueryTotal = "select isnull(cast(sum(valor_total) as varchar),0) as 'total' from vendas v where isnull(v.cancelado,0) <> 1 and convert(date,data,103) >= '" + dtInic + "' and convert(date,data,103) <= '" + dtFinal + "'";
txtPrecoTotal.Text = string.Format(CultureInfo.GetCultureInfo("pt-BR"), "{0:C}", Convert.ToDecimal(c.RetornaQuery(sQueryTotal, "total").Replace(",", "").Replace(".", ",").Replace("R$ ", "")));
txtPrecoTotal.Text = txtPrecoTotal.Text.Replace("R$ ", "");
if (btemDesconto)
{
lblPrecoDesc.Visible = true;
lblPrecoDesc2.Visible = true;
txtPrecoTotalDEsc.Visible = true;
String sQueryTotalDesconto = "select isnull(cast(sum(valor_total - (desconto)) as varchar),0) as 'total' from vendas v where isnull(v.cancelado,0) <> 1 and convert(date,data,103) >= '" + dtInic + "' and convert(date,data,103) <= '" + dtFinal + "'";
txtPrecoTotalDEsc.Text = string.Format(CultureInfo.GetCultureInfo("pt-BR"), "{0:C}", Convert.ToDecimal(c.RetornaQuery(sQueryTotalDesconto, "total").Replace(",", "").Replace(".", ",").Replace("R$ ", "")));
txtPrecoTotalDEsc.Text = txtPrecoTotalDEsc.Text.Replace("R$ ", "");
}
//
}
else
{
MessageBox.Show("A data inicial deve ser MENOR OU IGUAL a data final !", "Erro datas", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}