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