Class.forName(bundle.getString("driver"));
conn
= DriverManager.
getConnection(bundle.
getString("url"), bundle.
getString("username"), bundle.
getString("password")); conn.setAutoCommit(false);
st = conn.createStatement();
int flag_tot ;
filter = " and type_id=20";
int visit = 0;
String qryot
= "select cust_shipping_code as ot,TO_CHAR(current_timestamp,'YYYYMMDDHH24MISS') as fechaHora from consignments c inner join arrivals a on (a.arrival_id=c.arrival_id) " + "where a.arrival_date between to_date('" + returnStringDate(this.fromDateChooser.getDate()) + "','yyyy-mm-dd HH24:MI:SS') and to_date('" + returnStringDate(this.toDateChooser.getDate()) + "','yyyy-mm-dd HH24:MI:SS') "
+ "and c.customer_id=" + this.customerTextField.getText() + " and c.product_id in(select product_id from customer_products where category_id=2" + filter + ") and c.cust_shipping_code is not null group by cust_shipping_code";
String qry
= "select cust_shipping_code as pipe1," + "cp.addressee_cust_account as pipe2,"
+ "case cp.customer_id when 100061 then '10' when 491 then '10' when 187 then '10' else substr(c.cust_shipping_code,1,2) end as pipe3,"
+ "case ac.delivery_type when 'C' then '40' when 'R' then '40' when 'D' then '10' end as pipe4,"
+ "case ac.delivery_type when 'C' then '50' when 'R' then rrel.cust_relationship_id when 'D' then rret.cust_reason_id end as pipe5,"
+ "to_char(ac.delivery_date,'dd/mm/yyyy') as pipe6,"
+ "case ac.delivery_type when 'C' then substr(ac.floor||' PISOS '||ac.color||' '||door.description||' '||CASE WHEN ac.grid_wall is null THEN '' ELSE case when ac.grid_wall='N' then 'SIN REJA' else 'CON REJA' end END||' '||CASE WHEN ac.garage is null THEN '' ELSE case when ac.garage='N' then 'SIN COCHERA' else 'CON COCHERA' end END||' '||meter.description||' '||ac.serial_meter,1,60) "
+ "when 'R' then ac.name when 'D' then '' end as pipe7,"
+ "case ac.delivery_type when 'C' then 'BP' when 'R' then ac.last_name when 'D' then '' end as pipe8,"
+ "case ac.delivery_type when 'C' then '' when 'R' then ac.sec_last_name when 'D' then '' end as pipe9,"
+ "case ac.delivery_type when 'C' then '5' when 'R' then rid.cust_identification_id when 'D' then '' end as pipe10,"
+ "case ac.delivery_type when 'C' then '' when 'R' then (case rid.cust_identification_id when '5' then 'NINGUNO' else rid.description end) when 'D' then '' end as pipe11,"
+ "case ac.delivery_type when 'C' then '' when 'R' then ac.num_identify when 'D' then '' end as pipe12,"
+ "ac.observations as pipe13,ac.delivery_type as pipe14,"
+ "ac.consignment_id as pipe15,ac.cust_account as pipe16, cp.cnsgnmnt_piece_id as pipe17 "
+ "from consignments c "
+ "inner join cnsgnmnt_pieces cp on (cp.consignment_id=c.consignment_id) "
+ "left join acknowledgment_capture ac on (ac.cust_account=cp.cust_account and ac.consignment_id=cp.consignment_id) "
+ "left join r_return_reason_qp_cust rret on(rret.qp_reason_id=ac.qp_reasons_id and rret.customer_id=c.customer_id and rret.category_id=2) "
+ "left join r_relationship_qp_cust rrel on(rrel.qp_relationship_id=ac.qp_relationship_id and rrel.customer_id=cp.customer_id) "
+ "left join r_identification_qp_cust rid on(rid.qp_identification_id=ac.qp_identify_id and rid.customer_id=cp.customer_id) "
+ "left join catmeter meter on (meter.meter_id=ac.meter_id) "
+ "left join catdoor door on (door.door_id=ac.door_id) "
+ "where c.cust_shipping_code=? and ac.delivery_type is not null and ac.delivery_type!='I' and ac.progress_ind=0";
System.
out.
println(qry
+ " busqueda__"); System.
out.
println("_____________"+qryot
); // JOptionPane.showMessageDialog(null, "el query:");
// System.exit(0);
String qryu
= "update acknowledgment_capture ac set ac.progress_ind=2 where ac.consignment_id=? and ac.cust_account=? ";
ps = conn.prepareStatement(qry);
System.
out.
println("Q:"+qryu
); psu = conn.prepareStatement(qryu);
int row = 0, deliv = 0, files = 0;
StringBuilder line = new StringBuilder();
String fechaHora, csc, path
= "", fileName
= "tsis.txt";
ot = st.executeQuery(qryot);
path = "";
int prueba_alert = 0;
while (ot.next()) {
csc = ot.getString("ot");
System.
out.
println(csc
+" el csc"); fechaHora = ot.getString("fechaHora");
System.
out.
println("AQUIIIII"); String terminacion
= csc.
substring(csc.
length() - 3, csc.
length());
if (terminacion.equals("113")) {
flag_tot = 1;
fileName = "113_" + fechaHora + ".txt";
gname_113 = fileName;
} else
if (terminacion.equals("126")) {
flag_tot = 2;
fileName = "126_" + fechaHora + ".txt";
gname_126 = fileName;
System.
out.
println("126prueba");
} else {
flag_tot = 3;
fileName = "otro_" + fechaHora + ".txt";
gname_otro = fileName;
}
HoraFecha = fechaHora;
ps.setString(1, ot.getString("ot"));
if(this.customerTextField.getText().equals("100061"))
path=csc.substring(csc.length()-5,csc.length())+"";
path = "";
current
= new File(savePath
+ path
+ fileName
);
if (current.exists()) {
current.delete();
}
files++;
rs = ps.executeQuery();
System.
out.
println("OTROOOO"); while (rs.next()) {
linea_doc = "";
if ("R".equals(rs.getString("pipe14")) || "C".equals(rs.getString("pipe14"))) {
line.append(manageEmpty(rs.getString("pipe1")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe2")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe3")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe4")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe5")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe6")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe7")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe8")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe9")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe10")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe11")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe12")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe13")));
line.append("ENTREGA|0");
line.append("|");
line.append("0");
line.append("|");
line.append("0");
line.append("|0");
} else if ("D".equals(rs.getString("pipe14"))) {
line.append(manageEmpty(rs.getString("pipe1")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe2")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe3")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe4")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe5")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe6")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe7")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe8")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe9")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe10")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe11")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe12")));
line.append("|");
line.append(manageEmpty(rs.getString("pipe13")));
line.append("DEVOLUCION|0");
line.append("|");
line.append("0");
line.append("|");
line.append("0");
line.append("|0");
}
if (this.customerTextField.getText().equals("491")) {
System.
out.
println(rs.
getString("pipe15")+" __ "+rs.
getString("pipe16"));
psu.setString(1, rs.getString("pipe15"));
psu.setString(2, rs.getString("pipe16"));
psu.executeUpdate();
// }
deliv++;
line.append(endLine);
row++;
}
// System.out.println("alcanzo a salir");
qry_visits = " select to_char(ja.dispatch_date,'ddmmyy') as fecha, c.cust_shipping_code,"
+ " cp.addressee_cust_account,ja.* from journeys_arunico ja left join cnsgnmnt_pieces cp "
+ " on ja.cnsgnmnt_piece_id = cp.cnsgnmnt_piece_id "
+ " left join consignments c on cp.consignment_id = c.consignment_id"
+ " where c.cust_shipping_code = '" + csc + "' " // and cp.cnsgnmnt_piece_id = 1667693823
+ " and ja.customer_id=491 and cp.cnsgnmnt_piece_id not in "
+ "(select acknowledgment_capture.cnsgnmnt_piece_id from acknowledgment_capture"
+ " left join cnsgnmnt_pieces on cnsgnmnt_pieces.cnsgnmnt_piece_id=acknowledgment_capture.cnsgnmnt_piece_id"
+ " where cnsgnmnt_pieces.cnsgnmnt_piece_id=cp.cnsgnmnt_piece_id ) order by ja.cnsgnmnt_piece_id,ja.dispatch_date";
System.
out.
println(csc
+" todavia llega aca"); visit = 0;
int primero = 1;
String add_acc
= "", fecha
= "";
rs_aux = st_aux.executeQuery(qry_visits);
System.
out.
println(qry_visits
); while (rs_aux.next()) {
linea_doc = "";
if (primero == 1) {
add_acc = rs_aux.getString("addressee_cust_account");
fecha = rs_aux.getString("fecha");
primero = 0;
}
if (rs_aux.getString("addressee_cust_account").equals(add_acc)) {
add_acc = rs_aux.getString("addressee_cust_account");
fecha = rs_aux.getString("fecha");
visit++;
continue;
// } else {
//
// line.append(manageEmpty(rs_aux.getString("cust_shipping_code")));
// line.append("|");
// line.append(manageEmpty(add_acc));
// line.append("|");
// line.append(manageEmpty("10"));
// line.append("|");
//
//// if (visit == 0) {
//// line.append("00");
//// line.append("|");
//// line.append("00");
//// line.append("|");
//// } else if (visit == 1) {
//// line.append("60");
//// line.append("|");
//// line.append("61");
//// line.append("|");
//// } else if (visit == 2) {
//// line.append("60");
//// line.append("|");
//// line.append("62");
//// line.append("|");
//// } else {
//// line.append("60");
//// line.append("|");
//// line.append("63");
//// line.append("|");
//// }
//
//// line.append(manageEmpty(fecha)); 11:30
//// line.append(manageEmpty("|"));
//// line.append("|||||||||||");
//
// add_acc = rs_aux.getString("addressee_cust_account");
// fecha = rs_aux.getString("fecha");
//
// visit = 1;
//
// deliv++;
// line.append(endLine);
// row++;
//
//
// }
}
if (flag_tot == 1) {
line113.append(line);
line.setLength(0);
} else if (flag_tot == 2) {
line126.append(line);
line.setLength(0);
} else {
lineotro.append(line);
line.setLength(0);
}
if (flag_tot == 1) {
total113 = (total113 + deliv);
deliv = 0;
} else if (flag_tot == 2) {
total126 = (total126 + deliv);
deliv = 0;
} else {
totalotro = (totalotro + deliv);
deliv = 0;
}
}
if (this.customerTextField.getText().equals("491")) {
// current = new File(savePath + path + fileName);
// current = new File(savePath + path + gname_113);
//
// line113.insert(0, "999999|" + total113 + "|||||||||||||||" + endLine);
//
// current = new File(savePath + path + gname_126);
// line126.insert(0, "999999|" + total126 + "|||||||||||||||" + endLine);
//
//
// current = new File(savePath + path + gname_otro);
// lineotro.insert(0, "999999|" + totalotro + "|||||||||||||||" + endLine);
if (!dir.exists()) {
dir.mkdir();
}
writeFile(line113.toString(), savePath + path + gname_113);
writeFile(line126.toString(), savePath + path + gname_126);
writeFile(lineotro.toString(), savePath + path + gname_otro);
}
}
deliv = 0;
line.setLength(0);
total113 = 0;
total126 = 0;
totalotro = 0;
line113.setLength(0);
line126.setLength(0);
lineotro.setLength(0);
conn.commit();
close(conn);
close(st);
close(ps);
close(psu);
close(rs);
close(ot);
if (files > 0) {
showMessage
("Los archivos se generaron con exito en la ruta " + savePath,
"Aviso",
JOptionPane.
INFORMATION_MESSAGE); } else {
showMessage
("No se encontraron registros para los filros especificados",
"Error",
JOptionPane.
ERROR_MESSAGE); }
}
}