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