public DataTable carregarParcelasIDParcela(int idparcela)
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select
*
from parcelacontapagar p
left join compra c on c.com_id = p.com_id
left join fornecedor f on f.for_id=c.for_id where par_id = @idparcela order by par_pai,par_numero,par_id";
fb.executeQuery(sql, out dt, "@idparcela", idparcela);
fb.desconecta();
}
return(dt);
}
public DataTable pesquisarItemVendaIDCategoriaData(int idcategoria, DateTime datai, DateTime dataf)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select i.*,p.*, (i.ite_valor*i.ite_quantidade) as ite_total from ItemVenda i
left join produto p on p.pro_id = i.pro_id
left join categoria c on c.cat_id = p.cat_id
where i.ite_data between @datai and @dataf and c.cat_id = @idcategoria order by ite_id",
out dt, "@datai", datai.Date, "@dataf", dataf.Date, "@idcategoria", idcategoria);
fb.desconecta();
}
return(dt);
}
public DataTable carregarParcelas()
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select
*
from parcelacontapagar p
inner join compra c on c.com_id=p.com_id
left join fornecedor f on f.for_id=c.for_id order by par_status desc,par_dtvencimento asc";
fb.executeQuery(sql, out dt);
fb.desconecta();
}
return(dt);
}
public DataTable pesquisarquantidadeItensVendidosData(DateTime datai, DateTime dataf)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select i.pro_id, p.pro_codigo,p.pro_nome, ite_valor as pro_valor, sum(ite_quantidade) as pro_quantidade from itemvenda i
inner join produto p on p.pro_id = i.pro_id
where i.ite_data between @datai and @dataf
group by i.pro_id,p.pro_codigo, p.pro_nome, ite_valor",
out dt, "@datai", datai.Date, "@dataf", dataf.Date);
fb.desconecta();
}
return(dt);
}
public DataTable pesquisar(string parametro, string campo)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from cliente c left join cidade cid on cid.cid_id=c.cid_id where
UPPER(cli_nome) like UPPER(@parametro) or
UPPER(cli_endereco) like UPPER(@parametro)
order by cli_nome",
out dt, "@parametro", '%' + parametro + '%');
fb.desconecta();
}
dt = mostrar(dt);
return(dt);
}
public decimal totalPacelaContaPagarCompra(int idcompra)
{
decimal total = 0;
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
DataTable dt = new DataTable();
fb.executeQuery("select sum(par_valor) as total from parcelacontapagar where com_id=@com_id", out dt, "@com_id", idcompra);
try
{
total = decimal.Parse(dt.Rows[0]["total"].ToString());
}
catch { }
fb.desconecta();
}
return(total);
}
public string retornaAliquota(int idProduto)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from produto where pro_id = @id", out dt, "@id", idProduto);
fb.desconecta();
}
string aliquota = "";
if (dt.Rows.Count > 0)
{
aliquota = dt.Rows[0]["pro_aliquota"].ToString();
}
return(aliquota);
}
public DataTable carregarParcelasIDFornecedor(int idfornecedor, DateTime datai, DateTime dataf)
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select
*
from parcelacontapagar p
left join compra c on c.com_id = p.com_id
left join fornecedor f on f.for_id=c.for_id where c.for_id = @idfornecedor
and par_dtvencimento between @datai and @dataf
order by par_pai,par_numero,par_id";
fb.executeQuery(sql, out dt, "@idfornecedor", idfornecedor, "@datai", datai, "@dataf", dataf);
fb.desconecta();
}
return(dt);
}
public DataTable pesquisarTelefone(string ddd, string telefone)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from cliente c left join cidade cid on cid.cid_id=c.cid_id where
(cli_ddd1 = @ddd and cli_telefone1=@telefone) or
(cli_ddd2 = @ddd and cli_telefone2=@telefone) or
(cli_ddd3 = @ddd and cli_telefone3=@telefone)
order by cli_nome",
out dt, "@ddd", ddd, "@telefone", telefone);
fb.desconecta();
}
dt = mostrar(dt);
return(dt);
}
public DataTable pesquisarvendaaberta(int idcaixa)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from Venda v
left join cliente c on c.cli_id=v.cli_id
left join funcionario f on f.fun_id=v.fun_id
left join caixa ca on ca.cai_id = v.cai_id
where ven_status = 1 and v.cai_id = @idcaixa order by cli_nome asc, ven_data desc, ven_hora desc",
out dt, "@idcaixa", idcaixa);
fb.desconecta();
}
ven_id = 0;
if (dt.Rows.Count > 0)
{
ven_id = int.Parse(dt.Rows[0]["ven_id"].ToString());
cli_id = int.Parse(dt.Rows[0]["cli_id"].ToString());
fun_id = int.Parse(dt.Rows[0]["fun_id"].ToString());
ven_data = DateTime.Parse(dt.Rows[0]["ven_data"].ToString());
ven_hora = DateTime.Parse(dt.Rows[0]["ven_hora"].ToString());
ven_dataf = "";
ven_horaf = "";
try
{
ven_dataf = DateTime.Parse(dt.Rows[0]["ven_dataf"].ToString()).ToShortDateString();
ven_horaf = DateTime.Parse(dt.Rows[0]["ven_horaf"].ToString()).ToString("T");
}
catch { }
ven_total = decimal.Parse(dt.Rows[0]["ven_total"].ToString());
ven_desconto = decimal.Parse(dt.Rows[0]["ven_desconto"].ToString());
ven_status = int.Parse(dt.Rows[0]["ven_status"].ToString());
ven_senha = dt.Rows[0]["ven_senha"].ToString();
cai_id = int.Parse(dt.Rows[0]["cai_id"].ToString());
}
return(dt);
}
public DataTable pesquisar(string parametro, string campo)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery("select * from funcionario f left join cidade c on c.cid_id=f.cid_id where UPPER(fun_" + campo + ") like UPPER(@parametro) order by fun_nome",
out dt, "@parametro", '%' + parametro + '%');
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
fun_id = int.Parse(dt.Rows[0]["fun_id"].ToString());
cid_id = int.Parse(dt.Rows[0]["cid_id"].ToString());
fun_nome = dt.Rows[0]["fun_nome"].ToString();
fun_rg = dt.Rows[0]["fun_rg"].ToString();
fun_cpf = dt.Rows[0]["fun_cpf"].ToString();
fun_sexo = dt.Rows[0]["fun_sexo"].ToString();
fun_dtnascimento = DateTime.Parse(dt.Rows[0]["fun_dtnascimento"].ToString());
fun_endereco = dt.Rows[0]["fun_endereco"].ToString();
fun_numero = dt.Rows[0]["fun_numero"].ToString();
fun_bairro = dt.Rows[0]["fun_bairro"].ToString();
fun_cep = dt.Rows[0]["fun_cep"].ToString();
fun_ddd1 = dt.Rows[0]["fun_ddd1"].ToString();
fun_ddd2 = dt.Rows[0]["fun_ddd2"].ToString();
fun_ddd3 = dt.Rows[0]["fun_ddd3"].ToString();
fun_telefone1 = dt.Rows[0]["fun_telefone1"].ToString();
fun_telefone2 = dt.Rows[0]["fun_telefone2"].ToString();
fun_telefone3 = dt.Rows[0]["fun_telefone3"].ToString();
fun_informacao = dt.Rows[0]["fun_informacao"].ToString();
fun_email = dt.Rows[0]["fun_email"].ToString();
fun_usuario = dt.Rows[0]["fun_usuario"].ToString();
fun_senha = dt.Rows[0]["fun_senha"].ToString();
fun_nivel = dt.Rows[0]["fun_nivel"].ToString();
fun_status = int.Parse(dt.Rows[0]["fun_status"].ToString());
}
return(dt);
}
public decimal carregarParcelasPagasBoleto(int idcompra)
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select sum(par_valor) as par_valor
from parcelacontapagar where com_id = @idcompra and par_descricao = 'Boleto'";
fb.executeQuery(sql, out dt, "@idcompra", idcompra);
fb.desconecta();
}
try
{
return(decimal.Parse(dt.Rows[0]["par_valor"].ToString()));
}
catch { return(0); }
}
public decimal carregarParcelaBoletoValor(int idparcela)
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select
*
from parcelacontapagar p where par_id = @idparcela and par_descricao='Boleto' order by par_pai,par_numero,par_id";
fb.executeQuery(sql, out dt, "@idparcela", idparcela);
fb.desconecta();
}
decimal valor = 0;
try { valor = decimal.Parse(dt.Rows[0]["par_valor"].ToString()); }
catch { }
return(valor);
}
public DataTable pesquisarID(int id)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from funcionario f left join cidade c on c.cid_id=f.cid_id
where fun_id=@idfuncionario",
out dt, "@idfuncionario", id);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
fun_id = int.Parse(dt.Rows[0]["fun_id"].ToString());
fun_nome = dt.Rows[0]["fun_nome"].ToString();
}
return(dt);
}
public decimal carregarQtdeParcelasCheque(int idcompra)
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select
count(par_id) as qtde
from parcelacontapagar p where com_id = @idcompra and par_descricao='Cheque' order by par_pai,par_numero,par_id";
fb.executeQuery(sql, out dt, "@idcompra", idcompra);
fb.desconecta();
}
decimal qtde = 0;
try { qtde = decimal.Parse(dt.Rows[0]["qtde"].ToString()); }
catch { }
return(qtde);
}
public decimal totalSuprimentoIdCaixa(int idcaixa)
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
decimal total = 0;
if (fb.conecta())
{
fb.executeQuery(@"select sum(tra_valor) as total
from transacao where cai_id=@cai_id and tra_descricao='SUPRIMENTO'", out dt, "@cai_id", idcaixa);
fb.desconecta();
}
try
{
total = decimal.Parse(dt.Rows[0][0].ToString());
}
catch { }
return(total);
}
public int pegaIdAberto()
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from caixa where cai_status = 1",
out dt);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
return(int.Parse(dt.Rows[0]["cai_id"].ToString()));
}
else
{
return(0);
}
}
public bool isAberto()
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery("select * from caixa where cai_status = 1",
out dt);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public int pesquisarTotalItensVenda(int idvenda)
{
int total = 0;
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery("select count(ite_id) as total from ItemVenda where ven_id = @ven_id",
out dt, "@ven_id", idvenda);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
total = int.Parse(dt.Rows[0]["total"].ToString());
}
return(total);
}
public DataTable pesquisarPreVenda()
{
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select * from venda v
left join cliente c on c.cli_id = v.cli_id where ven_status = 2";
fb.executeQuery(sql, out dt);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
ven_id = int.Parse(dt.Rows[0]["ven_id"].ToString());
}
return(dt);
}
public int retornaIdMarca(int idProduto)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from produto where pro_id = @id", out dt, "@id", idProduto);
fb.desconecta();
}
int idmarca = 0;
if (dt.Rows.Count > 0)
{
int.TryParse(dt.Rows[0]["mar_id"].ToString(), out idmarca);
}
return(idmarca);
}
public int pesquisarIdVendaAtual()
{
int Total = 0;
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select max(VEN_ID) as Total from Venda v ",
out dt, "@datai", "", "@dataf", "");
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
Total = int.Parse(dt.Rows[0]["Total"].ToString());
}
return(Total);
}
public DataTable buscarTelefone(string numero)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select * from cliente c
left join cidade cid on cid.cid_id=c.cid_id
where
cli_ddd1||cli_telefone1=@numero or
cli_ddd2||cli_telefone2=@numero or
cli_ddd3||cli_telefone3=@numero
order by cli_nome",
out dt, "@numero", numero);
fb.desconecta();
}
mostrar(dt);
return(dt);
}
public int pesquisarTotalCupons(DateTime datai, DateTime dataf)
{
int Total = 0;
string DT1 = Convert.ToString(datai).ToString().Substring(0, 10);
DT1 = DT1.Replace("/", ".");
string DT2 = Convert.ToString(dataf).ToString().Substring(0, 10);
DT2 = DT2.Replace("/", ".");
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select count(VEN_ID) as Total from Venda v
where (ven_data between @datai and @dataf) ",
out dt, "@datai", DT1, "@dataf", DT2);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
Total = int.Parse(dt.Rows[0]["Total"].ToString());
//fun_id = int.Parse(dt.Rows[0]["fun_id"].ToString());
//ven_id = int.Parse(dt.Rows[0]["ven_id"].ToString());
//cli_id = int.Parse(dt.Rows[0]["cli_id"].ToString());
//ven_data = DateTime.Parse(dt.Rows[0]["ven_data"].ToString());
//ven_hora = DateTime.Parse(dt.Rows[0]["ven_hora"].ToString());
//ven_total = decimal.Parse(dt.Rows[0]["ven_total"].ToString());
//ven_desconto = decimal.Parse(dt.Rows[0]["ven_desconto"].ToString());
//ven_status = int.Parse(dt.Rows[0]["ven_status"].ToString());
//ven_senha = dt.Rows[0]["ven_senha"].ToString();
//cai_id = int.Parse(dt.Rows[0]["cai_id"].ToString());
}
return(Total);
}
public DataTable pesquisar()
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery("select * from Empresa",
out dt);
fb.desconecta();
}
emp_id = 0;
if (dt.Rows.Count > 0)
{
emp_id = int.Parse(dt.Rows[0]["emp_id"].ToString());
emp_cnpj = dt.Rows[0]["emp_cnpj"].ToString();
emp_razao = dt.Rows[0]["emp_razao"].ToString();
emp_fantasia = dt.Rows[0]["emp_fantasia"].ToString();
emp_telefone1 = dt.Rows[0]["emp_telefone1"].ToString();
emp_telefone2 = dt.Rows[0]["emp_telefone2"].ToString();
emp_telefone3 = dt.Rows[0]["emp_telefone3"].ToString();
emp_endereco = dt.Rows[0]["emp_endereco"].ToString();
emp_numero = dt.Rows[0]["emp_numero"].ToString();
emp_bairro = dt.Rows[0]["emp_bairro"].ToString();
emp_cidade = dt.Rows[0]["emp_cidade"].ToString();
emp_uf = dt.Rows[0]["emp_uf"].ToString();
emp_email = dt.Rows[0]["emp_email"].ToString();
emp_site = dt.Rows[0]["emp_site"].ToString();
emp_logo = dt.Rows[0]["emp_logo"].ToString();
emp_cep = dt.Rows[0]["emp_cep"].ToString();
emp_ie = dt.Rows[0]["emp_ie"].ToString();
emp_im = dt.Rows[0]["emp_im"].ToString();
}
return(dt);
}
public DataTable relatorioCaixa(int idcaixa)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery(@"select f1.fun_nome as fun_nome1, f2.fun_nome as fun_nome2, c.*, p.*,cli_nome from caixa c
left join funcionario f1 on f1.fun_id = c.fun_id
left join funcionario f2 on f2.fun_id = c.fun_idfechamento
left join venda v on v.cai_id = c.cai_id
left join parcelacontareceber p on p.ven_id = v.ven_id
left join cliente cli on cli.cli_id= p.cli_id
where c.cai_id=@id
",
out dt, "@id", idcaixa);
fb.desconecta();
}
return(dt);
}
public string carregarXMLIDVenda(int ven_id)
{
string xml = "";
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
fb.executeQuery(@"select * from sat where ven_id = @idvenda", out dt, "@idvenda", ven_id);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
xml = dt.Rows[0]["sat_xml"].ToString();
hora = dt.Rows[0]["sat_hora"].ToString();
data = dt.Rows[0]["sat_data"].ToString();
}
return(xml);
}
public int pesquisarproxcupom()
{
int total = 0;
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{
fb.executeQuery("select max(ven_id) as total from ItemVenda",
out dt, "@ven_id", "");
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
total = int.Parse(dt.Rows[0]["total"].ToString());
}
total++;
return(total);
}
public DataTable carregarcaixa(DateTime datai, DateTime dataf)
{
string DT1 = Convert.ToString(datai).ToString().Substring(0, 10);
DT1 = DT1.Replace("/", ".");
string DT2 = Convert.ToString(dataf).ToString().Substring(0, 10);
DT2 = DT2.Replace("/", ".");
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
DataTable dt = new DataTable();
if (fb.conecta())
{
string sql = @"select * from caixa
where cai_datainicial between @datai and @dataf and cai_status = 0 order by cai_id";
fb.executeQuery(sql, out dt, "@datai", DT1, "@dataf", DT2);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
cai_id = int.Parse(dt.Rows[0]["cai_id"].ToString());
fun_id = int.Parse(dt.Rows[0]["fun_id"].ToString());
fun_idfechamento = int.Parse(dt.Rows[0]["fun_idfechamento"].ToString());
cai_valorinicial = decimal.Parse(dt.Rows[0]["cai_valorinicial"].ToString());
cai_valorfinal = decimal.Parse(dt.Rows[0]["cai_valorfinal"].ToString());
cai_datainicial = DateTime.Parse(dt.Rows[0]["cai_datainicial"].ToString());
cai_datafinal = DateTime.Parse(dt.Rows[0]["cai_datafinal"].ToString());
cai_horainicial = DateTime.Parse(dt.Rows[0]["cai_horainicial"].ToString());
cai_horafinal = DateTime.Parse(dt.Rows[0]["cai_horafinal"].ToString());
cai_status = int.Parse(dt.Rows[0]["cai_status"].ToString());
cai_informacao = dt.Rows[0]["cai_informacao"].ToString();
cai_numero = int.Parse(dt.Rows[0]["cai_numero"].ToString());
}
return(dt);
}
public DataTable pesquisarAniversarioMes(string mes)
{
DataTable dt = new DataTable();
//FBBanco fb = new FBBanco();
SQLBanco fb = new SQLBanco();
if (fb.conecta())
{ //extract(DAY from current_date)
fb.executeQuery(@"select c.*,cid.*
from cliente c left join cidade cid on cid.cid_id=c.cid_id
where
(extract(MONTH from cli_dtnascimento) = @mesi)
order by cli_dtnascimento,cli_nome",
out dt, "@mesi", mes);
fb.desconecta();
}
if (dt.Rows.Count > 0)
{
id = int.Parse(dt.Rows[0]["cli_id"].ToString());
nome = dt.Rows[0]["cli_nome"].ToString();
endereco = dt.Rows[0]["cli_endereco"].ToString();
numero = dt.Rows[0]["cli_numero"].ToString();
bairro = dt.Rows[0]["cli_bairro"].ToString();
telefone1 = dt.Rows[0]["cli_telefone1"].ToString();
telefone2 = dt.Rows[0]["cli_telefone2"].ToString();
telefone3 = dt.Rows[0]["cli_telefone3"].ToString();
ddd1 = dt.Rows[0]["cli_ddd1"].ToString();
ddd2 = dt.Rows[0]["cli_ddd2"].ToString();
ddd3 = dt.Rows[0]["cli_ddd3"].ToString();
cpf = dt.Rows[0]["cli_cpf"].ToString();
cid_nome = dt.Rows[0]["cid_nome"].ToString();
cid_uf = dt.Rows[0]["cid_uf"].ToString();
dtnascimento = DateTime.Parse(dt.Rows[0]["cli_dtnascimento"].ToString());
}
return(dt);
}