public java.util.List listaSolicitacoes(String setorDest, String status)
throws ClassNotFoundException, SQLException {
List dadosSol = new ArrayList();
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta =
"SELECT "
+ " idsol, "
+ " nmsolicitante, "
+ " st.nm_setor, "
+ " dtsolicitacao, "
+ " ramal, "
+ " t.descricao as tipo, "
+ " Substr(solicitacao,1,40)||'...' as solicitacao, "
+ " s.status "
+ "FROM "
+ " desenv.sadm_solicitacao s, "
+ " dbamv.setor st, "
+ " desenv.sadm_tpchamado t "
+ "where "
+ " s.setorsolcitante = st.cd_setor "
+ "and s.tipochamado_idtp = t.idtp "
+ "and s.status IN ("
+ status
+ ")"
+ "and S.setordestino = '"
+ setorDest
+ "'"
+ "order by idsol,s.status ";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
int codigo = rs.getInt("idsol");
String solictante = rs.getString("nmsolicitante");
String setor = rs.getString("nm_setor");
String data = rs.getString("dtsolicitacao");
String ramal = rs.getString("ramal");
String tipo = rs.getString("tipo");
String solicitacao = rs.getString("solicitacao");
String vstatus = rs.getString("status");
dadosSol.add(
new ListaSolicitacoes(
codigo, solictante, setor, data, ramal, tipo, solicitacao, vstatus));
}
rs.close();
st.close();
c.fechaConeccao();
return dadosSol;
}
public String consSetorUsuario(String login) throws ClassNotFoundException, SQLException {
String setorUsuario = null;
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta = "SELECT setor FROM desenv.sadm_usuario where login = '******'";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
setorUsuario = rs.getString("setor");
}
rs.close();
st.close();
c.fechaConeccao();
return setorUsuario;
}
public String conNmSetor(int codigo) throws ClassNotFoundException, SQLException {
String nm = null;
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta = "Select nm_setor From dbamv.setor Where cd_setor = '" + codigo + "'";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
nm = rs.getString("nm_setor");
}
rs.close();
st.close();
c.fechaConeccao();
return nm;
}
public int conIdUsuario(String login) throws ClassNotFoundException, SQLException {
int codigo = 0;
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta = "SELECT iduser FROM desenv.sadm_usuario where login= '******'";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
codigo = rs.getInt("iduser");
}
rs.close();
st.close();
c.fechaConeccao();
return codigo;
}
public String consultaData() throws ClassNotFoundException, SQLException {
String dtsolic = null;
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta =
"select to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') as dataSolicitacao from dual";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
dtsolic = rs.getString("dataSolicitacao");
}
rs.close();
st.close();
c.fechaConeccao();
return dtsolic;
}
public java.util.List consultaSetor() throws ClassNotFoundException, SQLException {
List dadosSetor = new ArrayList();
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta = "select cd_setor,nm_setor from dbamv.setor order by nm_setor";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
int codigo = rs.getInt("cd_setor");
String desc = rs.getString("nm_setor");
dadosSetor.add(new Setor(codigo, desc));
}
rs.close();
st.close();
c.fechaConeccao();
return dadosSetor;
}
public String consDescTipoChamado(int codigo) throws ClassNotFoundException, SQLException {
String desc = null;
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta =
"sELECT t.descricao FROM desenv.sadm_solicitacao s, desenv.sadm_tpchamado t "
+ "where s.tipochamado_idtp = t.idtp and s.idsol = '"
+ codigo
+ "'";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
desc = rs.getString("descricao");
}
rs.close();
st.close();
c.fechaConeccao();
return desc;
}
public java.util.List listaSolicitPeriodo(
String setorDest, String status, String setor01, String setor02, String dt01, String dt02)
throws ClassNotFoundException, SQLException {
List dadosSol = new ArrayList();
Conecta c = new Conecta();
Statement st = c.pegaConeccao();
String consulta =
"SELECT "
+ " s.status, "
+ " idsol, "
+ " nmsolicitante, "
+ " ramal, "
+ " lpad(st.cd_setor,4,'0')||' - '|| st.nm_setor as setor, "
+ " dtsolicitacao, "
+ " t.descricao as tipo, "
+ " u.nome as executante, "
+ " s.dtexecucao as execucao "
+ " FROM "
+ " desenv.sadm_solicitacao s, "
+ " dbamv.setor st, "
+ " desenv.sadm_tpchamado t, "
+ " desenv.sadm_usuario u "
+ " where "
+ " s.setorsolcitante between '"
+ setor01
+ "' and '"
+ setor02
+ "'"
+ " and trunc(to_date(s.dtsolicitacao, 'dd/mm/yyyy hh24:mi:ss' )) between to_date('"
+ dt01
+ "','dd/mm/yyyy') and "
+ " to_date('"
+ dt02
+ "','dd/mm/yyyy') "
+ " and s.setorsolcitante = st.cd_setor "
+ " and s.tipochamado_idtp = t.idtp "
+ " and s.usuario_iduser = u.iduser(+) "
+ " and s.status like ('"
+ status
+ "') "
+ " and S.setordestino = '"
+ setorDest
+ "'"
+ " order by dtsolicitacao, status ";
ResultSet rs = st.executeQuery(consulta);
while (rs.next()) {
int codigo = rs.getInt("idsol");
String solictante = rs.getString("nmsolicitante");
String setor = rs.getString("setor");
String data = rs.getString("dtsolicitacao");
String ramal = rs.getString("ramal");
String tipo = rs.getString("tipo");
String vstatus = rs.getString("status");
String executante = rs.getString("executante");
String dtexecucao = rs.getString("execucao");
dadosSol.add(
new ListaSolicitacoes(
codigo, solictante, setor, data, ramal, tipo, vstatus, executante, dtexecucao));
}
rs.close();
st.close();
c.fechaConeccao();
return dadosSol;
}