public DataSet SelectAllDDLCategoriaProcesso() { DataSet ds = new DataSet(); IDbConnection objConexao; IDbCommand objCommand; IDataAdapter objDataAdapter; string sql = "SELECT * FROM TID_TIPODESPESA WHERE TID_CATEGORIA <> 1;"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
//update public bool Update(MateriaPrima materia) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "UPDATE tbl_materia SET mat_nome=?nome, mat_quantidade=?quantidade WHERE mat_id=?id"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?nome", materia.Nome)); objCommand.Parameters.Add(Mapped.Parameter("?quantidade", materia.Quantidade)); objCommand.Parameters.Add(Mapped.Parameter("?id", materia.Id)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
//métodos //insert public bool Insert(Loja loja) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "INSERT INTO loj_loja(loj_codigo, loj_cnpj, loj_id, loj_endereco) VALUES( ?CodigoLOJ, ?CnpjLOJ, ?IdLOJ, ?EnderecoLOJ)"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?CodigoLOJ", loja.CodigoLOJ)); objCommand.Parameters.Add(Mapped.Parameter("?CnpjLOJ", loja.CnpjLOJ)); objCommand.Parameters.Add(Mapped.Parameter("?IdLOJ", loja.IdLOJ)); objCommand.Parameters.Add(Mapped.Parameter("?EnderecoLOJ", loja.EnderecoLOJ)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
// id maximo dos fornecedores com pedidos pra usar no for pro gráfico public DataSet TotalSuppliers() { DataSet ds = new DataSet(); System.Data.IDbConnection objConn; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConn = Mapped.Connection(); objCommand = Mapped.Command("select max(for_id) from fornecedor where(for_id) in (select for_id from pedido);", objConn); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConn.Close(); objCommand.Dispose(); objConn.Dispose(); return(ds); }
// OBTÉM O TOTAL DE ITEMS ERRADOS POR FORNECEDOR public DataSet TotalWrongItems(int id) { DataSet ds = new DataSet(); System.Data.IDbConnection objConn; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConn = Mapped.Connection(); objCommand = Mapped.Command("select sum(pei_errado) from pedido_item inner join pedido using (ped_id) inner join fornecedor using (for_id) where for_id = ?id and pei_errado is not null; ", objConn); objCommand.Parameters.Add(Mapped.Parameter("?id", id)); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConn.Close(); objCommand.Dispose(); objConn.Dispose(); return(ds); }
public DataSet GetUltimaVenda() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT ven_valortotal AS valor, pes_nome as cliente, vit_quantidade, pro_nome AS produto from tbl_venda INNER JOIN tbl_vendaitem USING (ven_codigo) INNER JOIN tbl_pessoa using (pes_codigo) INNER JOIN tbl_produto using (pro_codigo) group by ven_codigo; ", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
public static DataSet SelectAll() { DataSet ds = new DataSet(); IDbConnection objConnection; IDbCommand objCommand; IDataAdapter objDataDadapter; objConnection = Mapped.Connection(); string sql = "select * from cid_cidade order by cid_nome;"; objCommand = Mapped.Command(sql, objConnection); objDataDadapter = Mapped.adapter(objCommand); objDataDadapter.Fill(ds); objConnection.Close(); objCommand.Dispose(); objConnection.Dispose(); return(ds); }
public bool UpdateCusto(double custo, int codigo) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "Update tbl_produtos set PRO_VALORCUSTO=?custo where PRO_CODIGO = ?codigo;"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("custo", custo)); objCommand.Parameters.Add(Mapped.Parameter("?codigo", codigo)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
//métodos public DataSet SelectAll() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT * FROM ped_pedido pe left join cli_cliente c on pe.cli_id = c.cli_id left join pro_produto p on pe.pro_id = p.pro_id left join cat_categoria_produto ca on pe.cat_id = ca.cat_id ", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
// selectall public DataSet SelectAll() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("select * from tbl_comandas c inner join tbl_mesas m on c.mesas_id = m.mesas_id;", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
public bool AddProduto(int codigo, int qtd) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "Update tbl_produtos set PRO_QTDATUAL = PRO_QTDATUAL + ?quantidade where PRO_CODIGO = ?codigo;"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("quantidade", qtd)); objCommand.Parameters.Add(Mapped.Parameter("?codigo", codigo)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
public DataSet SelectAllCustoProcesso(int processo) { DataSet ds = new DataSet(); IDbCommand objCommand; IDbConnection objConexao; IDataAdapter objDataAdapter; string sql = "SELECT * FROM DES_DESPESA D INNER JOIN TID_TIPODESPESA T ON (D.TID_CODIGO = T.TID_CODIGO) WHERE D.PRO_CODIGO = ?processo ORDER BY DES_DATA DESC;"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?processo", processo)); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
//update public bool Update(Cliente cliente) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "UPDATE tbl_cliente SET cli_nome=?nome, cli_telefone=?telefone, cli_endereco=?endereco, cli_cpf=?cpf WHERE cli_codigo=?codigo"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?codigo", cliente.Codigo)); objCommand.Parameters.Add(Mapped.Parameter("?nome", cliente.Nome)); objCommand.Parameters.Add(Mapped.Parameter("?telefone", cliente.Telefone)); objCommand.Parameters.Add(Mapped.Parameter("?endereco", cliente.Endereco)); objCommand.Parameters.Add(Mapped.Parameter("?cpf", cliente.CPF)); objCommand.Parameters.Add(Mapped.Parameter("?datacadastro", cliente.Datacadastro)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
public static DataSet CountUsuariosInativo() { DataSet ds = new DataSet(); IDbConnection objConexao; IDbCommand objComando; IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); string sql = "select count(fun_cod) from fun_funcionario inner join pes_pessoa using(pes_cod) " + "where pes_ativo = 'Inativo';"; objComando = Mapped.Command(sql, objConexao); objDataAdapter = Mapped.Adapter(objComando); objDataAdapter.Fill(ds); objConexao.Close(); objComando.Dispose(); objConexao.Dispose(); return(ds); }
public DataSet GetVendasSemana() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT pes_nome, ven_valortotal, ven_data FROM tbl_venda INNER JOIN tbl_pessoa ON tbl_pessoa.pes_codigo = tbl_venda.pes_codigo WHERE ven_data BETWEEN DATE_ADD(NOW(), INTERVAL -6 DAY) AND NOW() order by pes_nome; ", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
//métodos //insert public int Insert(Venda venda) { int retorno = 0; System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "INSERT INTO tbl_venda(vnd_pagamento, vnd_data, cli_codigo, ven_codigo) VALUES (?pagamento, ?data, ?cliente, ?vendedor); SELECT LAST_INSERT_ID();"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?pagamento", venda.Pagamento)); objCommand.Parameters.Add(Mapped.Parameter("?data", venda.Data)); objCommand.Parameters.Add(Mapped.Parameter("?cliente", venda.ClienteCodigo)); objCommand.Parameters.Add(Mapped.Parameter("?vendedor", venda.VendedorCodigo)); retorno = Convert.ToInt32(objCommand.ExecuteScalar()); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(retorno); }
public DataSet GetVendasMesTotal() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT SUM(ven_valortotal) AS valor from tbl_venda where month(ven_data)=MONTH(NOW()) AND year(ven_data)=year(now());", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
//select por data e pronto public DataSet SelectData(DateTime inicio, DateTime final) { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); //objCommand = Mapped.Command("SELECT * FROM ent_entradamateria where ent_data between ?inicio and ?final", objConexao); objCommand = Mapped.Command("SELECT ite_produto, Sum(ite_quantidade) AS totalquantidade FROM ite_itenspedido WHERE ite_status='Pronto' and ite_data between '" + inicio.ToString("yyyy-MM-dd") + "' and '" + final.ToString("yyyy-MM-dd") + "'GROUP BY ite_produto", objConexao); //objCommand = Mapped.Command("SELECT ite_produto, sum(if(month(ite_data)=10, ite_quantidade, 0)) as M, sum(if(month(ite_data)=11, ite_quantidade, 0)) as M, sum(if(month(ite_data)=12, ite_quantidade, 0)) as M FROM ite_itenspedido WHERE ite_data BETWEEN '2017-09-28' and '2017-12-12' and ite_status='Pronto' GROUP BY ite_produto;", objConexao); //objCommand.Parameters.Add(Mapped.Parameter("?inicio", inicio)); select sum(valor) from crediar group by cliente; //objCommand.Parameters.Add(Mapped.Parameter("?final", final)); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
public bool UpdateTotal(int venda, decimal subtotal) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "UPDATE tbl_venda SET ven_valortotal=ven_valortotal + ?subtotal WHERE ven_codigo = ?codigo"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?subtotal", subtotal)); objCommand.Parameters.Add(Mapped.Parameter("?codigo", venda)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
//métodos //insert public bool Insert(ItensPedido itenspedido) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "INSERT INTO ite_itenspedido (ite_guid, ite_produto, ite_quantidade, ite_data, ite_status) VALUES (?pedcodigo, ?produto, ?quantidade, ?data, ?status)"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?pedcodigo", itenspedido.PedCodigo)); objCommand.Parameters.Add(Mapped.Parameter("?produto", itenspedido.Produto)); objCommand.Parameters.Add(Mapped.Parameter("?quantidade", itenspedido.Quantidade)); objCommand.Parameters.Add(Mapped.Parameter("?data", itenspedido.Data)); objCommand.Parameters.Add(Mapped.Parameter("?status", itenspedido.Status)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
public static DataSet selectAllCategorias() { DataSet ds = new DataSet(); IDbConnection conexao; IDbCommand command; IDataAdapter dataAdapter; conexao = Mapped.Connection(); string query = "SELECT * FROM categoria"; command = Mapped.Command(query, conexao); dataAdapter = Mapped.Adapter(command); dataAdapter.Fill(ds); conexao.Close(); conexao.Dispose(); command.Dispose(); return(ds); }
public DataSet GetClientesMaisCompram() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT pes_nome, SUM(ven_valortotal) FROM tbl_venda INNER JOIN tbl_pessoa ON tbl_pessoa.pes_codigo = tbl_venda.pes_codigo GROUP BY pes_nome ORDER BY SUM(ven_valortotal) DESC", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
//SELECIONA TODOS OS ORCAMENTOS CADASTRADOS public DataSet SelectAll() { DataSet ds = new DataSet(); System.Data.IDbConnection objConn; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConn = Mapped.Connection(); objCommand = Mapped.Command("SELECT * FROM orcamento", objConn); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConn.Close(); objCommand.Dispose(); objConn.Dispose(); return(ds); }
public int Insert(Pedido pedido) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "INSERT INTO tbl_venda( ven_data, ven_valortotal,pes_codigo) VALUES (?data, ?valortotal, ?cliente)"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?data", pedido.Data)); objCommand.Parameters.Add(Mapped.Parameter("?valortotal", pedido.ValorTotal)); objCommand.Parameters.Add(Mapped.Parameter("?cliente", pedido.Cliente)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(GetID(pedido.Cliente, pedido.Data)); }
//métodos //insert public bool Insert(MateriaPrima materia) { System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; string sql = "INSERT INTO tbl_materia(mat_nome, mat_quantidade, mat_estoqueMinimo) VALUES (?nome, ?quantidade, ?estoqueMinimo)"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?nome", materia.Nome)); objCommand.Parameters.Add(Mapped.Parameter("?quantidade", materia.Quantidade)); objCommand.Parameters.Add(Mapped.Parameter("?estoqueMinimo", materia.EstoqueMin)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }
public DataSet GetVendasAno() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT pes_nome, ven_valortotal, ven_data FROM tbl_venda INNER JOIN tbl_pessoa ON tbl_pessoa.pes_codigo = tbl_venda.pes_codigo where year (ven_data)= year(now()) ORDER BY ven_data DESC;", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
//selectall public DataSet SelectAll() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT * FROM tbl_materia", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
public DataSet GetVendasProduto() { DataSet ds = new DataSet(); System.Data.IDbConnection objConexao; System.Data.IDbCommand objCommand; System.Data.IDataAdapter objDataAdapter; objConexao = Mapped.Connection(); objCommand = Mapped.Command("SELECT pro_nome, SUM(vit_quantidade) FROM tbl_produto INNER JOIN tbl_vendaitem ON tbl_produto.pro_codigo = tbl_vendaitem.pro_codigo GROUP BY pro_nome ORDER BY SUM(vit_quantidade) DESC;", objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(ds); }
public DataSet SelectAll() { DataSet ds = new DataSet(); IDbCommand objCommand; IDbConnection objConexao; IDataAdapter objDataAdapter; string sql = "SELECT * FROM EST_ESTADO ORDER BY EST_ESTADO"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objDataAdapter = Mapped.Adapter(objCommand); objDataAdapter.Fill(ds); objConexao.Close(); objConexao.Dispose(); objCommand.Dispose(); return(ds); }
public bool Insert(Movimentacao mov) { IDbCommand objCommand; IDbConnection objConexao; string sql = "INSERT INTO PMV_PROCESSO_MOVIMENTACAO(PRO_CODIGO, MOV_CODIGO, PMV_DATA_MOVIMENTACAO) VALUE(?processo, ?movimentacao, ?data);"; objConexao = Mapped.Connection(); objCommand = Mapped.Command(sql, objConexao); objCommand.Parameters.Add(Mapped.Parameter("?processo", mov.Processo.Codigo)); objCommand.Parameters.Add(Mapped.Parameter("?movimentacao", mov.Codigo)); objCommand.Parameters.Add(Mapped.Parameter("?data", mov.DataMovimentacao)); objCommand.ExecuteNonQuery(); objConexao.Close(); objCommand.Dispose(); objConexao.Dispose(); return(true); }