public static DataTable GetDadosLogin(string tipo, string cartao, string cpf)
{
netUtil.Funcoes func = new netUtil.Funcoes();
string sql = " SELECT conv.conv_id, cart.senha, cart.cartao_id FROM conveniados conv" +
" JOIN cartoes cart ON (cart.conv_id = conv.conv_id) " +
" WHERE cart.apagado <> 'S' AND conv.apagado <> 'S' ";
if (tipo.Equals("cartao"))
{
string codigo = "";
string digito = "";
if (cartao.Length > 3)
{
codigo = cartao.Substring(0, cartao.Length - 2);
digito = cartao.Substring(cartao.Length - 2, 2);
sql += " AND(( cart.codigo = " + codigo;
sql += " AND cart.digito = " + digito + " )";
sql += " OR (cart.codcartimp = '" + cartao + "'))";
}
else
sql += " AND cart.codcartimp ='" + cartao + "'";
}
else
sql += " AND cart.titular = 'S' AND (conv.cpf = '" + cpf + "' OR conv.cpf = '" + cpf.Replace(".", "").Replace("-", "") + "')";
BD BD = new BD();
return BD.GetDataTable(sql, null);
}
public static DataTable GetDadosLoginCantinex(string tipo, string email, string cpf)
{
netUtil.Funcoes func = new netUtil.Funcoes();
string sql = " SELECT conv.conv_id, conv.senha_cantinex, cart.cartao_id FROM conveniados conv" +
" JOIN cartoes cart ON (cart.conv_id = conv.conv_id) " +
" WHERE cart.apagado <> 'S' AND conv.apagado <> 'S' ";
if (tipo.Equals("cartao"))
{
sql += "AND conv.EMAIL = '" + email.Trim() + "'";
}
else
sql += " AND cart.titular = 'S' AND (conv.cpf = '" + cpf + "' OR conv.cpf = '" + cpf.Replace(".", "").Replace("-", "") + "')";
BD BD = new BD();
return BD.GetDataTable(sql, null);
}
public static DataTable PesquisaTotal(int empres_id, string chapa, string cartao, string nome, string status, string grupo, bool total, bool empr_prog_desc, DALMisc.TipoLimite tipo_limite)
{
string sql = "SELECT DISTINCT conv.chapa, conv.titular,";
sql += " (CASE conv.liberado WHEN 'S' THEN 'Liberado' ELSE 'Bloqueado' END) AS status ";
if (!empr_prog_desc)
{
sql += " ,conv.limite_mes";
if(tipo_limite != DALMisc.TipoLimite.Total)
sql += " ,COALESCE((SELECT saldo_mes FROM saldo_conv(conv.conv_id) WHERE fechamento = (SELECT Min(data_fecha) FROM dia_fecha WHERE data_fecha >= current_timestamp AND empres_id = Conv.Empres_id)),0) AS saldo_prox";
if (total)
sql += ", COALESCE((SELECT SUM(saldo_mes) FROM saldo_conv(conv.conv_id)),0) AS saldo_total";
}
sql += " FROM Conveniados conv";
sql += " JOIN Cartoes cart ON conv.conv_id = cart.conv_id";
string where = " WHERE conv.apagado <> 'S' AND cart.apagado <> 'S' AND conv.empres_id = " + empres_id;
if (grupo != "0")
where += " AND conv.grupo_conv_emp=" + grupo;
netUtil.Funcoes funcoes = new netUtil.Funcoes();
if (chapa != "")
where += " AND conv.chapa = " + chapa;
else
if (cartao != "") //procura pelo cartao
{
string codigo = "";
string digito = "";
if (cartao.Length > 3)
{
codigo = cartao.Substring(0, cartao.Length - 2);
digito = cartao.Substring(cartao.Length - 2, 2);
sql += " AND(( cart.codigo = " + codigo;
sql += " AND cart.digito = " + digito + " )";
sql += " OR (cart.codcartimp = '" + cartao + "'))";
}
else
sql += " AND cart.codcartimp ='" + cartao + "'";
}
else
if (nome != "")//procura pelo Nome
where += " AND (conv.titular LIKE '" + (nome.Length > 3 ? "%" : "") + nome.ToUpper() + "%')";
if (!status.Equals("T"))
where += " AND conv.liberado = '" + status + "'";
BD BD = new BD();
return BD.GetDataTable(sql + where + " ORDER BY conv.titular ", null);
}
//Alterado para SqlServer
public static DataTable PesquisaPaginadaAli(int page, int empres_id, string chapa, string cartao, string nome, string status, string grupo)
{
int band_id = GetEmpresBandId(empres_id);
string sql;
sql = " DECLARE @TamanhoPagina INT; ";
sql += " DECLARE @NumeroPagina INT; ";
sql += " SET @TamanhoPagina = 20; ";
sql += " SET @NumeroPagina = " + page + ";";
sql += " WITH Paginado AS ( ";
sql += " SELECT ROW_NUMBER() OVER(ORDER BY CONV.TITULAR) AS linha, ";
sql += " conv.conv_id,";
sql += " conv.titular, \n";
sql += " coalesce(conv.limite_mes,0.00) limite_mes, \n";
sql += " coalesce(alc.dias_trab,0) dias_trab, \n";
sql += " coalesce(alc.abono_valor,0.00) abono_mes, \n";
sql += " coalesce(alc.renovacao_valor, 0.00) saldo_renovacao, \n";
sql += " alr.RENOVACAO_ID, \n";
sql += " alr.DATA_RENOVACAO, \n";
sql += " alr.TIPO_CREDITO \n";
sql += " FROM conveniados conv \n";
sql += " JOIN Cartoes cart ON cart.conv_id = conv.conv_id \n";
sql += " left join ALIMENTACAO_RENOVACAO alr on alr.EMPRES_ID = conv.EMPRES_ID \n";
sql += " left join ALIMENTACAO_RENOVACAO_CREDITOS alc on alc.RENOVACAO_ID = alr.RENOVACAO_ID and alc.CONV_ID = conv.CONV_ID \n";
string where = " WHERE conv.apagado <> 'S' AND cart.apagado <> 'S' AND cart.liberado <> 'I' AND conv.empres_id = " + empres_id;
if (grupo != "0")
where += " AND conv.grupo_conv_emp=" + grupo;
netUtil.Funcoes funcoes = new netUtil.Funcoes();
if (chapa != "")
where += " AND conv.chapa = " + chapa;
else
if (cartao != "") //procura pelo cartao
{
string codigo = "";
string digito = "";
if (cartao.Length > 3)
{
codigo = cartao.Substring(0, cartao.Length - 2);
digito = cartao.Substring(cartao.Length - 2, 2);
where += " AND(( cart.codigo = " + codigo;
where += " AND cart.digito = " + digito + " )";
where += " OR (cart.codcartimp = '" + cartao + "'))";
}
else
where += " AND cart.codcartimp ='" + cartao + "'";
}
else
if (nome != "")//procura pelo Nome
where += " AND (conv.titular LIKE '" + (nome.Length > 3 ? "%" : "") + nome.ToUpper() + "%')";
where += " AND conv.liberado = '" + status + "'";
string paginado = ") SELECT TOP (@TamanhoPagina) * FROM Paginado p WHERE linha > @TamanhoPagina * (@NumeroPagina - 1) ORDER BY titular ";
BD BD = new BD();
return BD.GetDataTable(sql + where + paginado, null);
}
//Alterado para SqlServer
public static DataTable PesquisaPaginada(int page, int empres_id, string chapa, string cartao, string nome, string status, string grupo)
{
int band_id = GetEmpresBandId(empres_id);
string sql;
sql = " DECLARE @TamanhoPagina INT; ";
sql += " DECLARE @NumeroPagina INT; ";
sql += " SET @TamanhoPagina = 10; ";
sql += " SET @NumeroPagina = " + page + ";";
sql += " WITH Paginado AS ( ";
sql += " SELECT ROW_NUMBER() OVER(ORDER BY CONV.TITULAR) AS linha, ";
sql += " conv.conv_id,";
sql += " conv.chapa, \n";
sql += " conv.titular, \n";
sql += " (case conv.liberado when 'S' then 'Liberado' else 'Bloqueado' end) as status, \n";
sql += " case \n";
sql += " when emp.band_id <> 999 then \n";
sql += " (case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_1 \n";
sql += " else \n";
sql += " b.limite_1 \n";
sql += " end) else conv.limite_mes end as limite_mes, \n";
sql += " coalesce(case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_2 \n";
sql += " else \n";
sql += " b.limite_2 \n";
sql += " end, \n";
sql += " 0) as limite_mes_2, \n";
sql += " \n";
sql += " coalesce(case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_3 \n";
sql += " else \n";
sql += " b.limite_3 \n";
sql += " end, \n";
sql += " 0) as limite_mes_3, \n";
sql += " coalesce(case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_4 \n";
sql += " else \n";
sql += " b.limite_4 \n";
sql += " end, \n";
sql += " 0) as limite_mes_4, \n";
sql += " COALESCE((case \n";
sql += " when emp.band_id <> 999 then \n";
sql += " (case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_1 \n";
sql += " else \n";
sql += " b.limite_1 \n";
sql += " end) ";
sql += " else (case when emp.tipo_credito = 2 or emp.tipo_credito = 3 then (conv.limite_mes + conv.saldo_acumulado + conv.abono_mes) \n";
sql += " else conv.limite_mes end) end) - \n";
sql += " coalesce(conv.consumo_mes_1,0), 0) as saldo_disponivel_1, \n";
sql += " coalesce((case \n";
sql += " when emp.band_id <> 999 then \n";
sql += " (case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_2 \n";
sql += " else \n";
sql += " b.limite_2 \n";
sql += " end) else 0 end) - \n";
sql += " coalesce(conv.consumo_mes_2,0), 0) as saldo_disponivel_2, \n";
sql += " COALESCE((case \n";
sql += " when emp.band_id <> 999 then \n";
sql += " (case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_3 \n";
sql += " else \n";
sql += " b.limite_3 \n";
sql += " end) else 0 end) - \n";
sql += " coalesce(conv.consumo_mes_3,0), 0) as saldo_disponivel_3, \n";
sql += " coalesce((case \n";
sql += " when emp.band_id <> 999 then \n";
sql += " (case \n";
sql += " when bc.conv_id = conv.conv_id then \n";
sql += " bc.limite_4 \n";
sql += " else \n";
sql += " b.limite_4 \n";
sql += " end) else 0 end) - \n";
sql += " coalesce(conv.consumo_mes_4,0), 0) as saldo_disponivel_4, \n";
sql += " coalesce(conv.saldo_acumulado,0) as acumulado, \n";
sql += " coalesce(conv.consumo_mes_1,0) + coalesce(conv.consumo_mes_2,0) + coalesce(conv.consumo_mes_3,0) + coalesce(conv.consumo_mes_4,0) as saldo_total, \n";
sql += " coalesce(conv.consumo_mes_1,0) + coalesce(conv.consumo_mes_2,0) + coalesce(conv.consumo_mes_3,0) + coalesce(conv.consumo_mes_4,0) as saldo_prox \n";
sql += " FROM conveniados conv \n";
sql += " JOIN Cartoes cart ON cart.conv_id = conv.conv_id \n";
sql += " join empresas emp on conv.empres_id = emp.empres_id \n";
sql += " join bandeiras b on b.band_id = emp.band_id \n";
sql += " left join bandeiras_conv bc on conv.conv_id = bc.conv_id \n";
string where = " WHERE conv.apagado <> 'S' AND cart.apagado <> 'S' AND cart.liberado <> 'I' AND conv.empres_id = " + empres_id;
if (grupo != "0")
where += " AND conv.grupo_conv_emp=" + grupo;
netUtil.Funcoes funcoes = new netUtil.Funcoes();
if (chapa != "")
where += " AND conv.chapa = " + chapa;
else
if (cartao != "") //procura pelo cartao
{
string codigo = "";
string digito = "";
if (cartao.Length > 3)
{
codigo = cartao.Substring(0, cartao.Length - 2);
digito = cartao.Substring(cartao.Length - 2, 2);
where += " AND(( cart.codigo = " + codigo;
where += " AND cart.digito = " + digito + " )";
where += " OR (cart.codcartimp = '" + cartao + "'))";
}
else
where += " AND cart.codcartimp ='" + cartao + "'";
}
else
if (nome != "")//procura pelo Nome
where += " AND (conv.titular LIKE '" + (nome.Length > 3 ? "%" : "") + nome.ToUpper() + "%')";
if (!status.Equals("T"))
where += " AND conv.liberado = '" + status + "'";
string group = " group by conv.titular, conv.conv_id, conv.CHAPA, conv.LIBERADO, EMP.BAND_ID, CONV.limite_mes, BC.CONV_ID, BC.LIMITE_1, B.LIMITE_1, "
+ " BC.LIMITE_2, B.LIMITE_2, BC.LIMITE_3, B.LIMITE_3, BC.LIMITE_4, B.LIMITE_4, CONV.CONSUMO_MES_1, CONV.CONSUMO_MES_2, conv.CONSUMO_MES_3, "
+ " CONV.CONSUMO_MES_4, CONV.SALDO_ACUMULADO, emp.tipo_credito,conv.abono_mes ";
string paginado = ") SELECT TOP (@TamanhoPagina) * FROM Paginado p WHERE linha > @TamanhoPagina * (@NumeroPagina - 1) ORDER BY titular ";
BD BD = new BD();
return BD.GetDataTable(sql + where + group + paginado , null);
}
public static int PesqPagVirtualItemCount(int empres_id, string chapa, string cartao, string nome, string status, string grupo)
{
string where = " WHERE conv.apagado <> 'S' AND cart.apagado <> 'S' AND conv.empres_id = " + empres_id;
if (grupo != "0")
where += " AND conv.grupo_conv_emp=" + grupo;
netUtil.Funcoes funcoes = new netUtil.Funcoes();
if (chapa != "")
where += " AND conv.chapa = " + chapa;
else
if (cartao != "") //procura pelo cartao
{
string codigo = "";
string digito = "";
if (cartao.Length > 3)
{
codigo = cartao.Substring(0, cartao.Length - 2);
digito = cartao.Substring(cartao.Length - 2, 2);
where += " AND(( cart.codigo = " + codigo;
where += " AND cart.digito = " + digito + " )";
where += " OR (cart.codcartimp = '" + cartao + "'))";
}
else
where += " AND cart.codcartimp ='" + cartao + "'";
}
else
if (nome != "")//procura pelo Nome
where += " AND (conv.titular LIKE '" + (nome.Length > 3 ? "%" : "") + nome.ToUpper() + "%')";
if (!status.Equals("T"))
where += " AND conv.liberado = '" + status + "'";
BD BD = new BD();
return BD.ExecuteScalar<Int32>(0, " SELECT DISTINCT count(*) FROM Conveniados conv JOIN Cartoes cart ON conv.conv_id = cart.conv_id and cart.titular = 'S' " + where, null);
}
//Alterado para SqlServer
public static Conveniados IncluiConv(Conveniados pConv, string pOperador, int cred_id, string cartao_ativo, string codigoImportacao, Bandeiras_Conv band_conv, int nProtocolo)
{
pConv.Conv_id = GeraConvId();
if (pConv.Chapa == 0)
pConv.Chapa = pConv.Conv_id;
MontadorSql mont = new MontadorSql("conveniados", MontadorType.Insert);
mont.AddField("conv_id", pConv.Conv_id);
mont.AddField("chapa", pConv.Chapa); //Matricula
mont.AddField("titular", Utils.TirarAcento(pConv.Titular.ToUpper()));
mont.AddField("liberado", pConv.Status.ToUpper());
mont.AddField("contrato", Convert.ToInt32(pConv.Conv_id));
mont.AddField("empres_id", pConv.Empresa.Empres_id);
netUtil.Funcoes func = new netUtil.Funcoes();
mont.AddField("senha", func.Crypt("E", "1111", "BIGCOMPRAS"));
mont.AddField("dtcadastro", System.DateTime.Now);
mont.AddField("banco", 0);
mont.AddField("apagado", 'N');
mont.AddField("endereco", Utils.TirarAcento(pConv.Endereco.Logradouro.ToUpper()));
mont.AddField("numero", pConv.Endereco.Numero);
mont.AddField("bairro", Utils.TirarAcento(pConv.Endereco.Bairro.ToUpper()));
mont.AddField("cidade", Utils.TirarAcento(pConv.Endereco.Cidade.ToUpper()));
mont.AddField("estado", pConv.Endereco.Uf);
mont.AddField("cep", pConv.Endereco.Cep.ToUpper());
mont.AddField("operador", pOperador);
mont.AddField("cpf", pConv.Cpf);
mont.AddField("rg", pConv.Rg.ToUpper());
mont.AddField("obs1", Utils.TirarAcento(pConv.Obs1.ToUpper()));
mont.AddField("obs2", Utils.TirarAcento(pConv.Obs2.ToUpper()));
mont.AddField("telefone1", pConv.Telefone1.ToUpper());
mont.AddField("email", pConv.Email);
mont.AddField("cargo", Utils.TirarAcento(pConv.Cargo.ToUpper()));
mont.AddField("setor", Utils.TirarAcento(pConv.Setor.ToUpper()));
mont.AddField("usa_saldo_dif", pConv.Usa_saldo_dif.ToUpper());
int incluiu;
if (pConv.Usa_saldo_dif.Equals("S") && band_conv != null)
{
MontadorSql mont2 = new MontadorSql("bandeiras_conv", MontadorType.Insert);
mont2.AddField("conv_id", band_conv.Conv_id);
if (band_conv.Limite_1 > 0)
mont2.AddField("limite_1", band_conv.Limite_1);
if (band_conv.Limite_2 > 0)
mont2.AddField("limite_2", band_conv.Limite_2);
if (band_conv.Limite_3 > 0)
mont2.AddField("limite_3", band_conv.Limite_3);
if (band_conv.Limite_4 > 0)
mont2.AddField("limite_4", band_conv.Limite_4);
mont.AddField("limite_mes", band_conv.Limite_1 + band_conv.Limite_2 + band_conv.Limite_3 + band_conv.Limite_4);
BD BD2 = new BD();
incluiu = BD2.ExecuteNoQuery(mont.GetSqlString(), mont.GetParams());
if (incluiu == 1)
{
//Log.GravaLog("FCadConv", "conv_id", "", pConv.Conv_id.ToString(), pOperador, "Inclusão", "Cadastro de bandeira de Conveniados", pConv.Conv_id.ToString(), "Conv ID: " + pConv.Conv_id, "");
}
else
{
pConv.Conv_id = 0;
return pConv;
}
}
else
mont.AddField("limite_mes", pConv.LimiteMes);
if (pConv.Grupo_conv_emp != 0)
mont.AddField("grupo_conv_emp", pConv.Grupo_conv_emp);
if (!string.IsNullOrEmpty(pConv.DtNasc))
mont.AddField("dt_nascimento", pConv.DtNasc);
BD BD = new BD();
incluiu = BD.ExecuteNoQuery(mont.GetSqlString(), mont.GetParams());
if (incluiu == 1) //se entrar cria o cartão
{
int logID = Log.GeraLogID();
Log.GravaLog(logID, "FCadConv", "conv_id", "", pConv.Conv_id.ToString(), pOperador, "Inclusão", "Cadastro de Conveniados", pConv.Conv_id.ToString(), "Conv ID: " + pConv.Conv_id, "", nProtocolo);
Cartoes cart = new DALCartao().IncluiCartao(pConv.Conv_id, Utils.TirarAcento(pConv.Titular), pConv.LimiteMes, pConv.Status, pOperador, "S", 0, cred_id, cartao_ativo, pConv.DtNasc, codigoImportacao, nProtocolo);
pConv.addCartao(cart);
}
else pConv.Conv_id = 0;
return pConv;
}