Skip to content

Instantly share code, notes, and snippets.

@cesarockstar1985

cesarockstar1985/gist:734e8911bae89fefc444f4a03fcb5975

Created February 5, 2025 16:34
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cesarockstar1985/734e8911bae89fefc444f4a03fcb5975 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cesarockstar1985/734e8911bae89fefc444f4a03fcb5975 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
//package cast.backend.migrator.autopiezas; //TODO: comentar
import cast.portal.core.srv.CommandService;
import cast.portal.iface.srv.DeviceInfoService;
import cast.portal.monitor.dto.DeviceInfoEventDTO;
import cast.portal.security.ent.User;
import cast.portal.util.DataBaseHelper;
import com.sun.org.apache.xml.internal.security.utils.Base64;
import grails.plugin.mail.MailService;
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.datasource.DataSourceUtils;
import javax.sql.DataSource;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
MigradorBackend migradorBackend = new MigradorBackend(ctx,log,user,commandData); //TODO: descomentar
return migradorBackend.executeCommand();
public class MigradorBackend {
final String PARAMETER_SEPARATOR = "¤";
final String BLOCK_SEPARATOR = "¥";
final String BLOCK_SEPARATOR_DETAILS = "€";
final String BLOCK_SEPARATOR_PAYMENT = "\$"
private DataSource dataSourcePortalNG, dataSourcePortalBackend;
private Connection connPortalBackend, connPortalNG;
PreparedStatement ps;
ResultSet rs;
private ApplicationContext appCtx;
private Logger log;
private Map<String, String> commandData;
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
SimpleDateFormat sdfTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private CommandService commandSrv;
private DeviceInfoService deviceInfoSrv;
private Random random;
private HashMap<String, String> deviceInfoEventTypesMap;
private HashMap<String, String> userAccessKeyMap = new HashMap<>();
public MigradorBackend(ApplicationContext appCtx, Logger log, User user, Map<String, String> commandData) throws Throwable {
this.appCtx = appCtx;
this.log = log;
this.commandData = commandData;
dataSourcePortalNG = (DataSource) this.appCtx.getBean("dataSource");
dataSourcePortalBackend = (DataSource) this.appCtx.getBean("castPortalBackend");
this.connPortalNG = DataSourceUtils.getConnection(dataSourcePortalNG);
this.connPortalNG.setAutoCommit(false);
this.connPortalBackend = DataSourceUtils.getConnection(dataSourcePortalBackend);
this.connPortalBackend.setAutoCommit(false);
this.commandSrv = (CommandService) appCtx.getBean("commandService");
this.deviceInfoSrv = (DeviceInfoService) appCtx.getBean("deviceInfoService");
this.random = new Random();
this.deviceInfoEventTypesMap = getDeviceInfoEventTypesMap();
}
private HashMap<String, String> getDeviceInfoEventTypesMap(){
HashMap<String, String> dieTypesNameMap = new HashMap<>();
dieTypesNameMap.put("GPS_STATUS", "gps");
dieTypesNameMap.put("WIFI_STATUS", "wifi");
dieTypesNameMap.put("FIRST_LOGIN", "firstConfiguration");
dieTypesNameMap.put("MOCK_LOCATION_STATUS", "mockLocation");
dieTypesNameMap.put("MOBILE_DATA_STATUS", "data");
dieTypesNameMap.put("CREDIT_MESSAGE", "creditMeesage");
return dieTypesNameMap;
}
public String executeCommand() throws Throwable {
PreparedStatement psNotProcessed = null, psProcessed = null, psException = null;
long startTime = System.currentTimeMillis();
try{
log.info("------ castPortalBackend Migrator STARTED ------");
ArrayList<MigrationControlDTO> messagesToProcess = loadMigrationData();
if(messagesToProcess != null && messagesToProcess.size() > 0){
//Consulta y almacena la clave de acceso de los usuarios con documentos pendientes de migracion
reloadUserData();
ArrayList<DeviceInfoDTO> deviceInfoList = new ArrayList<>();
ArrayList<DeviceInfoEventDTO> deviceInfoEventList = new ArrayList<>();
psNotProcessed = connPortalBackend.prepareStatement("UPDATE migration_control set migrated_status = 'NP' where id = ?");
psProcessed = connPortalBackend.prepareStatement("UPDATE migration_control set migrated_status = 'M', migrated_date = now() where id = ?");
psException = connPortalBackend.prepareStatement("UPDATE migration_control set migrated_status = 'P', exception_date = now(), exception_message = ? where id = ?");
HashSet<String> usersWithExceptions = new HashSet<>();
MigrationControlDTO message = null;
for (int i = 0; i < messagesToProcess.size(); i++) {
boolean messageWasProcessed = false;
boolean isDeviceInfoMessage = false;
try {
String messageString = "";
message = messagesToProcess.get(i);
log.info("------------- Message to migrate -------------: \n" + message.toString() );
if (!usersWithExceptions.contains(message.username)) {
switch (message.domainName.toLowerCase().trim()) {
case "tracking":
messageWasProcessed = true;
messageString = generateTrackingMessage(message);
break;
case "device_info":
isDeviceInfoMessage = true;
messageWasProcessed = true;
deviceInfoList.add(generateDeviceInfoDTO(message));
break;
case "device_info_event":
isDeviceInfoMessage = true;
messageWasProcessed = true;
deviceInfoEventList.add(generateDeviceEventInfoDTO(message));
break;
case "route_detail":
switch (message.eventType) {
case "SV":
messageWasProcessed = true;
messageString = generateStartVisitMessage(message);
break;
case "EV":
messageWasProcessed = true;
messageString = generateEndVisitMessage(message);
break;
case "ASR":
messageWasProcessed = true;
messageString = generateAddSubjectInRouteMessage(message);
break;
}
break;
case "subject":
switch (message.eventType) {
case "AP":
messageWasProcessed = true;
messageString = generateSubjectPotentialMessage(message);
break;
}
break;
case "delivery_detail":
switch (message.eventType) {
case "D":
messageWasProcessed = true;
messageString = generateDeliveryMessage(message);
break;
}
break;
case "workday":
switch (message.eventType) {
case "SW":
messageWasProcessed = true;
messageString = generateStartWorkdayMessage(message);
break;
case "EW":
messageWasProcessed = true;
messageString = generateEndWorkdayMessage(message);
break;
}
break;
case "receipt":
switch(message.eventType){
case "C":
messageWasProcessed = true;
messageString = generateReceiptMessage(message);
break;
case "AR":
messageWasProcessed = true;
messageString = generateAnnulationReceiptMessage(message);
break;
case "SPC":
messageWasProcessed = true;
messageString = generateReprintUpdateReceiptMessage(message);
break;
}
break;
case "orders":
switch (message.eventType) {
case "OR":
messageWasProcessed = true;
messageString = generateOrdersMessage(message);
break;
}
break;
case "subject_attribute_assignment":
switch (message.eventType) {
case "SA":
messageWasProcessed = true;
messageString = generteSubjectAttributeAssignmentMessage(message);
break;
}
break;
case "invoice":
switch(message.eventType){
case "VD":
messageWasProcessed = true;
messageString = generateInvoiceVDMessage(message);
break;
}
break;
}
if (!isDeviceInfoMessage) {
if(!messageWasProcessed) {
log.info("Discarded message. Not implemented..");
updateNotProcessed(psNotProcessed, message);
} else {
log.info("Message String: " + messageString);
String messageResponse = executeCommandService(message, messageString);
log.info("Message Response: " + messageResponse);
if (messageResponse.startsWith("0|")) {
updateProcessed(psProcessed, message);
} else {
throw new Exception("Error en el procesamiento de mensaje: " +messageResponse);
}
}
}else {
log.info("Message device info type! Added in list!");
}
log.info("------------- Message processed -------------" );
} else {
log.info("Usuario " + message.username + " no procesado se encuentra con errores.");
}
} catch (Throwable t) {
log.error("----- Error al procesar un mensaje de migracion ----");
log.error(t,t);
connPortalBackend.rollback();
if (message != null){
usersWithExceptions.add(message.username);
updateException(psException, message, t);
String body = "Error en la migracion de mensaje del castPortal3. Verificar registro en la tabla migration_control. \n Excepcion: " + t.toString() + " \nMensaje: " + message.toString();
sendMail(body, null, null, null);
}
}
connPortalBackend.commit();
}
// Al terminar evalua los device info para migrar de una vez
migrateDeviceInfoMessages(deviceInfoList);
migrateDeviceInfoEventMessages(deviceInfoEventList);
}
log.info("------ castPortalBackend Migrator FINISHED ------");
}catch (Throwable th){
log.error("ERROR executeCommand: "+th);
connPortalBackend.rollback();
log.error("ERROR executeCommand: "+th, th);
}finally{
log.info("------ castPortalBackend Migrator FINALLY ------");
DataBaseHelper.closeRs(rs);
DataBaseHelper.closePs(ps);
DataBaseHelper.closePs(psNotProcessed);
DataBaseHelper.closePs(psProcessed);
DataBaseHelper.closePs(psException);
long finishTime = System.currentTimeMillis();
DataBaseHelper.releaseConnection(connPortalBackend, dataSourcePortalBackend);
log.info("Migrator Total Process Time in Seconds: " + ((finishTime - startTime) / 1000.0));
log.info("------ castPortalBackend Migrator FINALLY ------");
}
return "";
}
private void reloadUserData() throws Throwable{
if(userAccessKeyMap != null){
log.debug("======= reloadUserData - Started ========");
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = connPortalNG.prepareStatement("SELECT username, pin FROM users WHERE username = ANY (?) ");
ps.setArray(1, connPortalNG.createArrayOf("varchar", userAccessKeyMap.keySet().toArray()));
rs = ps.executeQuery();
String pinStr = "";
while (rs.next()){
pinStr = rs.getString("pin");
pinStr = (pinStr == null || pinStr.equals("")) ? "81dc9bdb52d04dc20036dbd8313ed055" : pinStr;
userAccessKeyMap.put(rs.getString("username"), pinStr);
}
}catch (Throwable th){
log.error("ERROR: reloadUserData. "+th.getMessage());
throw th;
}finally{
log.debug("======= reloadUserData - Finished =======");
DataBaseHelper.closeRs(rs);
DataBaseHelper.closePs(ps);
}
}else{
log.info("No se encontraron username de usuarios para la consulta");
}
}
private String generteSubjectAttributeAssignmentMessage(MigrationControlDTO message) throws Throwable{
StringBuilder sb;
String query = "SELECT (select code from subject where id = subject_id) as subject_code, saa.ng_id, sav.description from subject_attribute_assignment saa join subject_attribute_value sav on sav.id = saa.subject_attribute_value_id join subject_attribute sa on sa.id = sav.subject_attribute_id where sa.id = 'autopiezas|1667919934877|1244835784|6' and saa.id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs= ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
sb.append(".SEG_ASS").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("ng_id")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("description")).append(PARAMETER_SEPARATOR);
} else{
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private String generateEndVisitMessage(MigrationControlDTO message) throws Throwable{
StringBuilder sb;
String query = "select d.id, d.subject_code, d.subject_name, d.subject_fiscal_number, " +
"d.subject_commercial_name, d.subject_address, d.visit_type, d.ev_observation, d.with_supervisor, " +
"d.visit_positive, d.ev_reason " +
"from route r join route_detail d on r.id = d.route_id where d.id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs= ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
ArrayList<String> coordinates = searchEventCoordinate(rs.getString("id"), message.eventType);
String photo = searchSubjectPhoto(rs.getString("id"));
sb.append(".CAST_EV").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
if(rs.getBoolean("visit_positive")){
sb.append("1.Positivo").append(PARAMETER_SEPARATOR); //VISIT RESULT
}else{
sb.append("2.Negativo").append(PARAMETER_SEPARATOR); //VISIT RESULT
}
if(rs.getString("ev_reason") == null){
sb.append("-1").append(PARAMETER_SEPARATOR);
}else{
sb.append(rs.getString("ev_reason")).append(PARAMETER_SEPARATOR); //VISIT REASON
}
sb.append("-1").append(PARAMETER_SEPARATOR); //next Visit Date
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT1
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT2
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT3
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT4
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT5
if(rs.getString("ev_observation") == null || (rs.getString("ev_observation")).equals("")){
sb.append("-1").append(PARAMETER_SEPARATOR);
}else{
sb.append(rs.getString("ev_observation")).append(PARAMETER_SEPARATOR);; //OBSERVATION
}
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private String generateStartVisitMessage(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
String query = "select d.id, d.subject_code, d.subject_name, d.subject_fiscal_number, " +
"d.subject_commercial_name, d.subject_address, d.visit_type, d.sv_observation, d.with_supervisor " +
"from route r join route_detail d on r.id = d.route_id where d.id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
ArrayList<String> subjectDataArray = searchSubjectData(rs.getString("subject_code"));
ArrayList<String> coordinates = searchEventCoordinate(rs.getString("id"), message.eventType);
String photo = searchSubjectPhoto(rs.getString("id"));
String visitType = rs.getString("visit_type");
sb.append(".CAST_SV").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_fiscal_number")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_commercial_name")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR); //CONTACT_NAME
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_address")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR); //PHONE NUMBER
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
if(!visitType.equalsIgnoreCase("Presencial")){
sb.append("No").append(PARAMETER_SEPARATOR); //SUBJECT_LOCALIZABLE
}else{
sb.append(subjectDataArray.get(1)).append(PARAMETER_SEPARATOR); //SUBJECT_LOCALIZABLE
}
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(photo).append(PARAMETER_SEPARATOR); //SUBJECT_PHOTO
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT1
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT2
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT3
sb.append("-1").append(PARAMETER_SEPARATOR); //OPT4
sb.append(visitType).append(PARAMETER_SEPARATOR); //OPT5
String observation = visitType;
observation += " - "+rs.getString("sv_observation");
//if(observation != null && !observation.equals("")){
sb.append(observation).append(PARAMETER_SEPARATOR);
/*}else{
sb.append("-1").append(PARAMETER_SEPARATOR);
}*/
sb.append(coordinates.get(0)).append(PARAMETER_SEPARATOR); //LATITUDE
sb.append(coordinates.get(1)).append(PARAMETER_SEPARATOR); //LONGITUDE
sb.append(coordinates.get(2)).append(PARAMETER_SEPARATOR); //altitude
sb.append(coordinates.get(3)).append(PARAMETER_SEPARATOR); //verticalAccuracy
sb.append(coordinates.get(4)).append(PARAMETER_SEPARATOR); //horizontalAccuracy
sb.append(coordinates.get(5)).append(PARAMETER_SEPARATOR); //locationTimeMillis
sb.append(coordinates.get(6)).append(PARAMETER_SEPARATOR); //isTrueLocation
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private ArrayList<String> searchSubjectData(String subjectCode) throws Throwable{
ArrayList<String> subjectDataArray = new ArrayList<>();
PreparedStatement ps = null;
ResultSet rs = null;
try {
StringBuilder sb = new StringBuilder();
sb.append("SELECT active, localizable FROM subject WHERE code = ?");
ps = connPortalBackend.prepareStatement(sb.toString());
ps.setString(1, subjectCode);
rs = ps.executeQuery();
String status = "2.Inactivo", localizable = "Si";
if(rs.next()){
if(rs.getBoolean("active")){
status = "1.Activo";
}
if(rs.getBoolean("localizable")){
localizable = "Si";
}
subjectDataArray.add(status);
subjectDataArray.add(localizable);
}else{
subjectDataArray.add("-1");
subjectDataArray.add("No");
}
} catch (Throwable th) {
log.error("ERROR searchSubjectData: "+th, th);
throw th;
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return subjectDataArray;
}
private String searchSubjectPhoto(String routeDetailId) throws Throwable{
String photo = "-1";
PreparedStatement ps = null;
ResultSet rs = null;
try {
byte [] photoBytes = null;
StringBuilder sb = new StringBuilder();
sb.append("SELECT photo FROM subject_photo WHERE route_detail_id = ?");
ps = connPortalBackend.prepareStatement(sb.toString());
ps.setString(1, routeDetailId);
rs = ps.executeQuery();
if(rs.next()){
photoBytes = rs.getBytes("photo");
}
if(photoBytes != null){
photo = Base64.encode(photoBytes);
}
} catch (Throwable th) {
log.error("ERROR searchSubjectPhoto: "+th, th);
throw th;
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return photo;
}
private String searchWorkdayPhoto(String workdayId, String status) throws Throwable{
String photo = "-1";
PreparedStatement ps = null;
ResultSet rs = null;
try {
byte [] photoBytes = null;
StringBuilder sb = new StringBuilder();
sb.append("SELECT photo FROM workday_photo WHERE workday_id = ? AND workday_status = ?");
ps = connPortalBackend.prepareStatement(sb.toString());
ps.setString(1, workdayId);
ps.setString(2, status);
rs = ps.executeQuery();
if(rs.next()){
photoBytes = rs.getBytes("photo");
}
if(photoBytes != null){
photo = Base64.encode(photoBytes);
}
} catch (Throwable th) {
log.error("ERROR searchWorkdayPhoto: "+th, th);
throw th;
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return photo;
}
private ArrayList<String> searchEventCoordinate(String routeDetailId, String eventType) throws Throwable{
ArrayList<String> coordinates = new ArrayList<>();
PreparedStatement ps = null;
ResultSet rs = null;
try {
StringBuilder sb = new StringBuilder();
sb.append("SELECT e.latitude, e.longitude, e.altitude, e.vertical_accuracy, e.horizontal_accuracy, ");
sb.append("e.localization_time_millis, e.is_true_location ");
sb.append("FROM event e JOIN event_type et ON e.event_type_id = et.id WHERE domain_id = ? AND et.code = ?");
ps = connPortalBackend.prepareStatement(sb.toString());
ps.setString(1, routeDetailId);
ps.setString(2, eventType);
rs = ps.executeQuery();
if(rs.next()){
if(rs.getString("latitude") != null && !rs.getString("latitude").equals("")){
coordinates.add(rs.getString("latitude"));
}else{
coordinates.add("0");
}
if(rs.getString("longitude") != null && !rs.getString("longitude").equals("")){
coordinates.add(rs.getString("longitude"));
}else{
coordinates.add("0");
}
if(rs.getString("altitude") != null && !rs.getString("altitude").equals("")){
coordinates.add(rs.getString("altitude"));
}else{
coordinates.add("0");
}
if(rs.getString("vertical_accuracy") != null && !rs.getString("vertical_accuracy").equals("")){
coordinates.add(rs.getString("vertical_accuracy"));
}else{
coordinates.add("0");
}
if(rs.getString("horizontal_accuracy") != null && !rs.getString("horizontal_accuracy").equals("")){
coordinates.add(rs.getString("horizontal_accuracy"));
}else{
coordinates.add("0");
}
if(rs.getString("localization_time_millis") != null && !rs.getString("localization_time_millis").equals("")){
coordinates.add(rs.getString("localization_time_millis"));
}else{
coordinates.add("0");
}
if(rs.getBoolean("is_true_location")){
coordinates.add("0");
}else{
coordinates.add("1");
}
} else{
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
}
} catch (Throwable th) {
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
coordinates.add("0");
log.error("ERROR searchEventCoordinate: "+th, th);
throw th;
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return coordinates;
}
private String generateAddSubjectInRouteMessage(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
String query = "select * from route r join route_detail d on r.id = d.route_id where d.id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
sb.append(".CAST_ASR").append(PARAMETER_SEPARATOR);
sb.append(sdf.format(rs.getDate("start_day"))).append(BLOCK_SEPARATOR); //ROUTE DATE
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);
sb.append("0").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("sequence")).append(PARAMETER_SEPARATOR); //SEQUENCE
sb.append(PARAMETER_SEPARATOR); //ROW NUMBER
sb.append("0");
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private String generateSubjectPotentialMessage(MigrationControlDTO message) throws Throwable {
PreparedStatement ps = null;
ResultSet rs = null;
StringBuilder sb = new StringBuilder();
try {
String query = "select id, temporary_code, subject_name,fiscal_number, '-1' as company_alias, telephone, address, '-1' as prospectList, location_city_id, latitude, longitude, (case active when active then 'Activo' else 'Inactivo' end) as Activo, '-1' as observation from subject where id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
/*Obtener los valores adicionales*/
String contactName = searchSubjectContacts(rs.getString("id"));
String city = searchSubjectCity(rs.getString("location_city_id"));
String photo = searchProspectPhoto(rs.getString("id"));
String regPotencial = searchRegPotencial(rs.getString("id"));
String potLiters = searchPotLiters(rs.getString("id"));
String reason = searchReason(rs.getString("id"));
/*Cargar en el mapa a retornar*/
sb.append(".AP").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("temporary_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("fiscal_number")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("company_alias")).append(PARAMETER_SEPARATOR);
sb.append(contactName).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("telephone")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("address")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("2.Prospecto").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(photo).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("latitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("longitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("Activo")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);//22 usa el 24
//Si registra potencial de volumen(si o no)
sb.append(regPotencial).append(PARAMETER_SEPARATOR);
//potencial en litros, si no hay potencial en litros retornar '-1'
sb.append(potLiters).append(PARAMETER_SEPARATOR);
sb.append(reason).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("observation")).append(PARAMETER_SEPARATOR);
}
}catch (Throwable th) {
log.error("ERROR generateSubjectPotentialMessage: "+th, th);
throw new Exception("ERROR generateSubjectPotentialMessage: "+th);
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return sb.toString();
}
/*private String generateSubjectPotentialMessage(MigrationControlDTO message) throws Throwable {
PreparedStatement ps = null;
ResultSet rs = null;
StringBuilder sb = new StringBuilder();
try {
String query = "SELECT s.id, address, s.code, commercial_name, fiscal_number, latitude, localizable, longitude, subject_name, telephone, person_type, location_city_id, location_departament_id, location_country_id, sale_condition_id, location_district_id, price_list_id FROM subject s where s.id = ? ";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
String contactName = searchSubjectContacts(rs.getString("id"));
String photo = searchProspectPhoto(rs.getString("id"));
String companyAlias = addDefaultValue(rs.getString("commercial_name"));
String telephone = addDefaultValue(rs.getString("telephone"));
String address = addDefaultValue(rs.getString("address"));
String city = searchSubjectCity(rs.getString("location_city_id"));
String departament = searchSubjectDepartament(rs.getString("location_departament_id"));
String district = searchSubjectDistrict(rs.getString("location_district_id"));
String rubro = getAttributeValue(rs.getString("id"), "rubro");
String condition_sale = getCondition(rs.getString("sale_condition_id"));
String price_list = getPriceList(rs.getString("price_list_id"));
String localizable = rs.getBoolean("localizable") ? "Si" : "No";
String personType = "2.Fisica";
if (rs.getString("person_type").equals("Juridica")) {
personType = "1.Juridica";
}
sb.append(".CAST_SSP").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("fiscal_number")).append(PARAMETER_SEPARATOR);
sb.append(companyAlias).append(PARAMETER_SEPARATOR); //company_alias
sb.append(contactName).append(PARAMETER_SEPARATOR);
sb.append(telephone).append(PARAMETER_SEPARATOR);
sb.append(address).append(PARAMETER_SEPARATOR);
sb.append(city).append(PARAMETER_SEPARATOR); //city
sb.append("2.Prospecto").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(district).append(PARAMETER_SEPARATOR); //Barrio
sb.append(departament).append(PARAMETER_SEPARATOR); // --Departament
sb.append(price_list).append(PARAMETER_SEPARATOR); // --priceList
sb.append(condition_sale).append(PARAMETER_SEPARATOR); //Condicion de venta.
sb.append(photo).append(PARAMETER_SEPARATOR);
sb.append(rs.getDouble("latitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getDouble("longitude")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR); //Codigo de usuario.
sb.append("-1").append(PARAMETER_SEPARATOR); //nombre de usuario.
sb.append(rubro).append(PARAMETER_SEPARATOR); //rubro
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("id")); //replica_id
} else {
throw new Exception("No se encontro subject con id "+message.domainId);
}
}catch (Throwable th) {
log.error("ERROR generateSubjectPotentialMessage: "+th, th);
throw new Exception("ERROR generateSubjectPotentialMessage: "+th);
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return sb.toString();
}*/
private String searchProspectPhoto(String subjectId) throws Throwable{
String photo = "-1";
PreparedStatement ps = null;
ResultSet rs = null;
try {
byte [] photoBytes = null;
StringBuilder sb = new StringBuilder();
sb.append("SELECT photo FROM subject_photo WHERE (cast_deleted = FALSE OR cast_deleted IS NULL) AND subject_id = ? ORDER BY creation_date DESC LIMIT 1");
ps = connPortalBackend.prepareStatement(sb.toString());
ps.setString(1, subjectId);
rs = ps.executeQuery();
if(rs.next()){
photoBytes = rs.getBytes("photo");
}
if(photoBytes != null){
photo = Base64.encode(photoBytes);
}
} catch (Throwable th) {
log.error("ERROR searchProspectPhoto: "+th, th);
throw th;
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return photo;
}
private String searchSubjectCity(String subjectCityId) throws Exception {
String city="-1";
try {
String query = "select description from location_city where id=?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, subjectCityId);
rs = ps.executeQuery();
if(rs.next()){
city = rs.getString("description");
}
} catch(Exception ex) {
log.error("searchSubjectCity: " + ex);
throw ex;
}
return city;
}
private String searchSubjectDepartament(String subjectDepartamentId) throws Exception {
String departament ="-1";
try {
String query = "select code ||'.'|| description as description from location_departament where id= ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, subjectDepartamentId);
rs = ps.executeQuery();
if(rs.next()){
departament = rs.getString("description");
}
} catch(Exception ex) {
log.error("searchSubjectDepartament: " + ex);
throw ex;
}
return departament;
}
private String searchSubjectDistrict(String subjectDistrictId) throws Exception {
String district="-1";
try {
String query = "select description from location_district where id=?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, subjectDistrictId);
rs = ps.executeQuery();
if(rs.next()){
district = rs.getString("description");
}
} catch(Exception ex) {
log.error("searchSubjectDistrict: " + ex);
throw ex;
}
return district;
}
private String getAttributeValue(String id, String attribute_code) throws Throwable {
try {
String query = "select code || '.' || description as value from subject_attribute_value where subject_attribute_id in (select id from subject_attribute where code = '$attribute_code' and cast_deleted is false) and id in (select subject_attribute_value_id from subject_attribute_assignment where subject_id = ?)";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, id);
rs = ps.executeQuery();
if(rs.next()){
return rs.getString("value");
}else{
return "-1";
}
} catch(Exception ex) {
log.error("getAttributeValue: " + ex);
throw ex;
}
}
private String getCondition(String condition) throws Throwable {
try {
String query = "select code || '.' || description as condition from sale_condition where id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, condition);
rs = ps.executeQuery();
if(rs.next()){
return rs.getString("condition");
}else{
return "-1";
}
} catch(Exception ex) {
log.error("getCondition: " + ex);
throw ex;
}
}
private String getPriceList(String price_list_id) throws Throwable {
try {
String query = "select code || '.' || description as price_list from price_list where id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, price_list_id);
rs = ps.executeQuery();
if(rs.next()){
return rs.getString("price_list");
}else{
return "-1";
}
} catch(Exception ex) {
log.error("getPriceList: " + ex);
throw ex;
}
}
private String searchRegPotencial(String subjectId) throws Exception {
try {
String query = "select sav.description from subject_attribute_assignment saa join subject_attribute_value sav on sav.id = saa.subject_attribute_value_id where sav.subject_attribute_id = 'autopiezas|1667919934877|1244835784|7' and saa.subject_id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, subjectId);
rs = ps.executeQuery();
if(rs.next()){
if (rs.getString("description") == "Si"){
return rs.getString("description");
} else{
return "-1";
}
}
} catch(Exception ex) {
log.error("searchSubjectContacts: " + ex);
throw ex;
}
return "-1";
}
private String searchPotLiters(String subjectId) throws Exception {
try {
String query = "select sav.description from subject_attribute_assignment saa join subject_attribute_value sav on sav.id = saa.subject_attribute_value_id where sav.subject_attribute_id = 'autopiezas|1667919934877|1244835784|1' and saa.subject_id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, subjectId);
rs = ps.executeQuery();
if(rs.next()){
return rs.getString("description");
}
} catch(Exception ex) {
log.error("searchSubjectContacts: " + ex);
throw ex;
}
return "-1";
}
private String searchReason(String subjectId) throws Exception {
try {
String query = "select coalesce(impl_potencial_motivo, '') as impl_potencial_motivo from subject where id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, subjectId);
rs = ps.executeQuery();
if(rs.next()){
return rs.getString("impl_potencial_motivo");
}
} catch(Exception ex) {
log.error("searchSubjectContacts: " + ex);
throw ex;
}
return "-1";
}
private String searchSubjectContacts(String subjectId) throws Exception {
try {
String query = "SELECT contact_name FROM subject_contacts WHERE subject_id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, subjectId);
rs = ps.executeQuery();
if(rs.next()){
return rs.getString("contact_name");
}
} catch(Exception ex) {
log.error("searchSubjectContacts: " + ex);
throw ex;
}
return "-1";
}
private String addDefaultValue(String value) {
if (value.trim().length() > 0) {
return value;
}
return "-1";
}
private void migrateDeviceInfoEventMessages(ArrayList<DeviceInfoEventDTO> deviceInfoEventList) throws Throwable {
log.info("Device info to migrate: " + deviceInfoEventList.size());
HashSet<Long> idsWithErrorsMap = new HashSet<>();
StringBuilder errorsId = new StringBuilder();
StringBuilder migratedId = new StringBuilder();
if (deviceInfoEventList.size() > 0) {
Long[] deviceInfoIdWithErrors = deviceInfoSrv.processDeviceInfoEvent(deviceInfoEventList);
if (deviceInfoIdWithErrors != null) {
for (Long deviceInfoIdWithError : deviceInfoIdWithErrors) {
idsWithErrorsMap.add(deviceInfoIdWithError);
}
}
for (DeviceInfoEventDTO deviceInfoEventDTO : deviceInfoEventList) {
if (idsWithErrorsMap.contains(deviceInfoEventDTO.id)) {
if (!errorsId.toString().equals("")) {
errorsId.append(", ");
}
errorsId.append(deviceInfoEventDTO.id);
} else {
if (!migratedId.toString().equals("")) {
migratedId.append(", ");
}
migratedId.append(deviceInfoEventDTO.id);
}
}
if (!errorsId.toString().equals("")) {
updateDeviceInfoException(errorsId.toString());
}
if (!migratedId.toString().equals("")) {
updateDeviceInfoMigrated(migratedId.toString());
}
}
}
private void migrateDeviceInfoMessages(ArrayList<DeviceInfoDTO> deviceInfoList) throws Throwable {
log.info("Device info to migrate: " + deviceInfoList.size());
HashSet<Long> idsWithErrorsMap = new HashSet<>();
StringBuilder errorsId = new StringBuilder();
StringBuilder migratedId = new StringBuilder();
if (deviceInfoList.size() > 0) {
Long[] deviceInfoIdWithErrors = deviceInfoSrv.processDeviceInfo(deviceInfoList);
if (deviceInfoIdWithErrors != null) {
for (Long deviceInfoIdWithError : deviceInfoIdWithErrors) {
idsWithErrorsMap.add(deviceInfoIdWithError);
}
}
for (DeviceInfoDTO deviceInfoDTO : deviceInfoList) {
if (idsWithErrorsMap.contains(deviceInfoDTO.id)) {
if (!errorsId.toString().equals("")) {
errorsId.append(", ");
}
errorsId.append(deviceInfoDTO.id);
} else {
if (!migratedId.toString().equals("")) {
migratedId.append(", ");
}
migratedId.append(deviceInfoDTO.id);
}
}
if (!errorsId.toString().equals("")) {
updateDeviceInfoException(errorsId.toString());
}
if (!migratedId.toString().equals("")) {
updateDeviceInfoMigrated(migratedId.toString());
}
}
}
private void updateDeviceInfoException(String errorsId) throws Throwable {
Statement st = null;
try {
st = connPortalBackend.createStatement();
int updated = st.executeUpdate("UPDATE migration_control set exception_date = now() where id in (" + errorsId + ")");
log.info("Device Info / Event marcados con error: " + updated);
String body = "Device Info / Device Info Event no migrados: " + errorsId;
sendMail(body, null, null, null);
connPortalBackend.commit();
} catch (Throwable t) {
log.error("Error al procesar actualizacion de device info no migrados: "+ t);
throw t;
} finally {
DataBaseHelper.closeStatement(st);
}
}
private void updateDeviceInfoMigrated(String migratedId) throws Throwable {
Statement st = null;
try {
st = connPortalBackend.createStatement();
int updated = st.executeUpdate("UPDATE migration_control set migrated_status = 'M', migrated_date = now() where id in (" + migratedId + ")");
connPortalBackend.commit();
} catch (Throwable t) {
log.error("Error al procesar actualizacion de device info migrados: "+ t);
throw new Exception("Error al procesar actualizacion de device info migrados: " + t);
} finally {
DataBaseHelper.closeStatement(st);
}
}
private String executeCommandService(MigrationControlDTO message, String messageString) {
String username = message.username;
String mobilePin = "81dc9bdb52d04dc20036dbd8313ed055";
if(userAccessKeyMap != null){
mobilePin = userAccessKeyMap.get(username);
}
String[] messageData = new String[8];
messageData[0] = "MOBILEORI_KEY:"+username;
messageData[1] = "MOBILEDEST_KEY:"+ username;
messageData[2] = "TEXT_KEY:"+ messageString;
messageData[3] = "SEQ_KEY:"+ "castPortalBackend|" +username+ "|" + System.currentTimeMillis();
messageData[4] = "CAST_ID:0";
messageData[5] = "IS_FULL:true";
messageData[6] = "DOCUMENT_TIME:"+ message.documentTime;
messageData[7] = "MOBILE_PIN:"+mobilePin;
//for para loguear el array messageData
for (int i = 0; i < messageData.length; i++) {
log.info("messageData["+i+"] = " + messageData[i]);
}
return commandSrv.processMessage(messageData);
}
private void updateException(PreparedStatement psException, MigrationControlDTO message, Throwable t) {
try {
psException.setString(1,t.toString());
psException.setLong(2, message.migrationControlId);
psException.executeUpdate();
} catch (Throwable e) {
log.error("Error al marcar como excepcion!" + e);
}
}
private void updateProcessed(PreparedStatement psProcessed, MigrationControlDTO message) throws Throwable {
psProcessed.setLong(1, message.migrationControlId);
psProcessed.executeUpdate();
}
private void updateNotProcessed(PreparedStatement psNotProcessed, MigrationControlDTO message) throws Throwable {
psNotProcessed.setLong(1, message.migrationControlId);
psNotProcessed.executeUpdate();
}
private DeviceInfoDTO generateDeviceInfoDTO(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
String query = "select * from device_info where id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs = ps.executeQuery();
DeviceInfoDTO di;
if (rs.next()) {
String gatewayId = rs.getString("username")+"|"+(rs.getTimestamp("persisted_date")).getTime();
di = new DeviceInfoDTO();
di.setId(message.migrationControlId);
di.setPersistedDate(rs.getTimestamp("persisted_date"));
di.setServiceCode("");
di.setMobileUser(rs.getString("username"));
di.setMobilePin("");
di.setRequestType("");
di.setRequestId("");
di.setRequest("");
di.setCurrentMillis(rs.getString("current_millis"));
di.setImei(rs.getString("imei"));
di.setAppVersion(rs.getString("app_version"));
di.setFirstConfigMillis("");
di.setChangedConfigMillis("");
di.setQueueCount("0");
di.setSpaceAvailable(rs.getString("available_space"));
di.setSpaceUsed("");
di.setMemoryAvailable(rs.getString("free_memory"));
di.setMemoryUsed("");
di.setAppOpenMillis("");
di.setAppClosedMillis("");
di.setFailedSendMessageCount("");
di.setStgStartMillis("");
di.setStgFinishMillis("");
di.setStgDetails("");
di.setStgTotalRows("");
di.setExceptionsCount("");
di.setLastCleanedCount("");
di.setGatewayId(gatewayId);
di.setDeviceInfoTime((rs.getTimestamp("persisted_date")).getTime());
di.setOpt01("-"); //CurrentHost
di.setOpt02("-"); //CurrentSavedAppVersionMillis
di.setOpt03("-"); //VersionHistory
di.setOpt04("0"); //FailedDocumentCreation
di.setOpt05(rs.getString("brand_and_model")+" Android version: "+ rs.getString("os_version")); //Platform
di.setOpt06(""); //FileconnDirPhotos
di.setOpt07("-"); //PrinterConfig
di.setOpt09(""); //LocationStatus
di.setOpt10("-"); //DataDirectorySize
di.setOpt11("-"); //AppCloseHistory
di.setOpt12(rs.getString("battery_level")); //BatteryLevel
di.setOpt13(""); //CompressDate
di.setOpt14(""); //CompressDateWithError
di.setOpt15("0"); //TrackingQueueCount
di.setOpt16(""); //GpsDisableDate
di.setOpt17(""); //GpsStatus
di.setOpt18(""); //LastCreditSmsMillis
di.setOpt19(""); //LastCreditSmsText
di.setOpt20(rs.getString("os_uptime")); //PhoneUpTimeMilli
di.setOpt21(""); //EnableData
di.setOpt22(""); //DocumentIncompleteCount
di.setOpt23("");
di.setOpt24("");
di.setOpt25("");
di.setOpt26("");
di.setOpt27("");
di.setOpt28("");
di.setOpt29("");
di.setOpt30("");
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return di;
}
private DeviceInfoEventDTO generateDeviceEventInfoDTO(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
String query = "select * from device_info_event where id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs = ps.executeQuery();
DeviceInfoEventDTO die;
if (rs.next()) {
String gatewayId = rs.getString("username")+"|"+(rs.getTimestamp("persisted_date")).getTime();
die = new DeviceInfoEventDTO();
die.setPersistedDate(rs.getTimestamp("persisted_date"));
die.setId(message.migrationControlId);
die.setServiceCode("");
die.setMobileUser(rs.getString("username"));
die.setDeviceInfoEventTime(rs.getString("document_time"));
String eventType = rs.getString("event_type");
String eventMessage = getEventMessage(eventType, rs.getString("event_details"));
if(deviceInfoEventTypesMap.get(eventType) != null){
eventType = deviceInfoEventTypesMap.get(eventType);
}
die.setEventType(eventType);
die.setEventMessage(eventMessage);
die.setGatewayId(gatewayId);
die.setImei(rs.getString("imei"));
die.setMigratedDate("");
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return die;
}
private String getEventMessage(String eventType, String eventMessage){
String eventMessageToReturn = eventMessage;
switch (eventType) {
case "GPS_STATUS":
case "WIFI_STATUS":
case "MOCK_LOCATION_STATUS":
case "MOBILE_DATA_STATUS":
if(eventMessage.equalsIgnoreCase("true")){
eventMessageToReturn = "ON";
}else{
eventMessageToReturn = "OFF";
}
break;
default:
break;
}
return eventMessageToReturn;
}
private String generateTrackingMessage(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
String query = "select * from tracking where id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
sb.append(".TRK").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("latitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("longitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("altitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("horizontal_accuracy")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("vertical_accuracy")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("location_time_millis")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("is_true_location")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("battery_level"));
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private String generateStartWorkdayMessage(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
String query = "SELECT id, sw_date, coalesce(sw_kilometer, 0) as sw_kilometer, coalesce(sw_latitude, 0) as sw_latitude, coalesce(sw_longitude, 0) as sw_longitude, sw_observation, " +
"beacon_check, ew_observation, extinguisher_check, light_check, water_check, sequence, subject_id, related_delivery_id " +
"FROM workday WHERE id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
log.info("start workday query: "+ps.toString());
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
String photo = searchWorkdayPhoto(rs.getString("id"), "started");
ArrayList<String> coordinates = searchEventCoordinate(rs.getString("id"), message.eventType);
sb.append(".CAST_SWD").append(PARAMETER_SEPARATOR);
sb.append(rs.getLong("sw_kilometer")).append(PARAMETER_SEPARATOR);
sb.append(rs.getBoolean("light_check") ? "Si" : "No").append(PARAMETER_SEPARATOR);
sb.append(rs.getBoolean("beacon_check") ? "Si" : "No").append(PARAMETER_SEPARATOR);
sb.append(rs.getBoolean("extinguisher_check") ? "Si" : "No").append(PARAMETER_SEPARATOR);
sb.append(rs.getBoolean("water_check") ? "Si" : "No").append(PARAMETER_SEPARATOR);
sb.append(photo).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT1
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT2
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT3
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT4
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT5
if(rs.getString("related_delivery_id")!="") {
sb.append(rs.getLong("related_delivery_id")).append(PARAMETER_SEPARATOR); // delivery_id
}else {
sb.append("-1").append(PARAMETER_SEPARATOR); // delivery_id
}
String observation = rs.getString("sw_observation");
if(observation != null && !observation.equals("")){
sb.append(observation).append(PARAMETER_SEPARATOR);
}else{
sb.append("-1").append(PARAMETER_SEPARATOR);
}
sb.append(rs.getDouble("sw_latitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getDouble("sw_longitude")).append(PARAMETER_SEPARATOR); // 15
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(coordinates.get(6)).append(PARAMETER_SEPARATOR); //Tipo de localizacion
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private String generateEndWorkdayMessage(MigrationControlDTO message) throws Throwable{
StringBuilder sb;
String query = "SELECT id, ew_date, coalesce(ew_kilometer, 0) as ew_kilometer, coalesce(ew_latitude, 0) as ew_latitude, " +
"coalesce(ew_longitude, 0) as ew_longitude, ew_observation,related_delivery_id, (case when impl_close_delivery is true then '1.Si' else '2.No' end) as close_delivery FROM workday WHERE id = ?";
ps = connPortalBackend.prepareStatement(query);
ps.setString(1, message.domainId);
log.info("end workday query: "+ps.toString());
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
ArrayList<String> coordinates = searchEventCoordinate(rs.getString("id"), message.eventType);
String photo = searchWorkdayPhoto(rs.getString("id"), "finished");
sb.append(".CAST_EWD").append(PARAMETER_SEPARATOR);
sb.append(rs.getLong("ew_kilometer")).append(PARAMETER_SEPARATOR);
sb.append(photo).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("close_delivery")).append(PARAMETER_SEPARATOR); // OPT1
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT2
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT3
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT4
sb.append("-1").append(PARAMETER_SEPARATOR); // OPT5
if(rs.getString("related_delivery_id") != null && !rs.getString("related_delivery_id").equals("")){
sb.append("2").append(PARAMETER_SEPARATOR); // delivery_id
}else{
sb.append("-1").append(PARAMETER_SEPARATOR);
}
String observation = rs.getString("ew_observation");
if(observation != null && !observation.equals("")){
sb.append(observation).append(PARAMETER_SEPARATOR);
}else{
sb.append("-1").append(PARAMETER_SEPARATOR);
}
sb.append(rs.getDouble("ew_latitude")).append(PARAMETER_SEPARATOR);
sb.append(rs.getDouble("ew_longitude")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(coordinates.get(6)); //Tipo de localizacion
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private String generateDeliveryMessage(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
StringBuilder deliveryDetailQuerysb = new StringBuilder();
deliveryDetailQuerysb.append("select dd.receiver_name, ds.subject_signature, dd.subject_code, dd.subject_name, dd.status, dd.creation_date, ");
deliveryDetailQuerysb.append("round(dd.total_amount,0) as total_amount, dd.impl_observation, dd.external_id, "); deliveryDetailQuerysb.append("(select external_id from delivery where id = dd.delivery_id) as delivery_id, ") ;
deliveryDetailQuerysb.append("dd.subject_commercial_name, dd.subject_fiscal_number ");
deliveryDetailQuerysb.append("from delivery_detail dd left join impl_delivery_detail_electronic_signature ds on dd.id = ds.delivery_detail_id ");
deliveryDetailQuerysb.append("where dd.id = ?");
ps = connPortalBackend.prepareStatement(deliveryDetailQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
byte [] photoBytes = null;
String photo = "-1";
byte [] photoSignatureBytes = null;
String photoSignature = "-1";
if (rs.next()) {
sb = new StringBuilder();
sb.append(".DC").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code") == null ? "-1":rs.getString("subject_code").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name") == null ? "-1":rs.getString("subject_name").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("creation_date") == null ? "-1":rs.getString("creation_date").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("impl_observation") == null || rs.getString("impl_observation") == "" ? "-1":rs.getString("impl_observation").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("total_amount") == null || rs.getString("total_amount") == "" ? "-1":rs.getString("total_amount").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("delivery_id")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("external_id")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("status") == null || rs.getString("status") == "" ? "-1":rs.getString("status").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_commercial_name") == null ? "-1":rs.getString("subject_commercial_name").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_fiscal_number") == null ? "-1":rs.getString("subject_fiscal_number").trim()).append(PARAMETER_SEPARATOR);
if(rs.getString("subject_signature") != null){
photoSignatureBytes = rs.getBytes("subject_signature");
photoSignature = Base64.encode(photoSignatureBytes);
sb.append(photoSignature).append(PARAMETER_SEPARATOR);
} else {
sb.append("-1").append(PARAMETER_SEPARATOR);
}
sb.append(rs.getString("receiver_name") == null || rs.getString("receiver_name") == "" ? "-1":rs.getString("receiver_name").trim());
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
sb.append(BLOCK_SEPARATOR_DETAILS);
StringBuilder deliveryDocumentsQuerysb = new StringBuilder();
deliveryDocumentsQuerysb.append("select subject_code, subject_name, status, document_number, (select description from impl_rebound_reason where id = impl_reason_rebound) as impl_reason_rebound, ");
deliveryDocumentsQuerysb.append("round(document_amount,0) as document_amount, external_id,delivery_detail_id from delivery_document ");
deliveryDocumentsQuerysb.append(" where delivery_detail_id = ? ");
ps = connPortalBackend.prepareStatement(deliveryDocumentsQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
int b = 0;
while (rs.next()) {
if (b == 1){
sb.append(BLOCK_SEPARATOR);
}
sb.append(rs.getString("subject_code") == null || rs.getString("subject_code") == "" ? "-1":rs.getString("subject_code").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name") == null || rs.getString("subject_name") == "" ? "-1":rs.getString("subject_name").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("status") == null || rs.getString("status") == "" ? "-1":rs.getString("status").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_number") == null || rs.getString("document_number") == "" ? "-1":rs.getString("document_number").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_amount") == null || rs.getString("document_amount") == "" ? "-1":rs.getString("document_amount").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("external_id") == null || rs.getString("external_id") == "" ? "-1":rs.getString("external_id").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("impl_reason_rebound") == null || rs.getString("impl_reason_rebound") == "" ? "-1":rs.getString("impl_reason_rebound").trim());
b = 1;
}
sb.append(BLOCK_SEPARATOR_DETAILS);
StringBuilder deliveryDocumentDetailQuerysb = new StringBuilder();
deliveryDocumentDetailQuerysb.append("select ddd.product_code, ddd.product_name, ddd.status, round(ddd.amount, 0) as amount, cast(round(ddd.quantity, 0) as numeric) as quantity, cast(round(ddd.quantity_delivered, 0) as numeric) as quantity_delivered, ddd.external_id, ");
deliveryDocumentDetailQuerysb.append(" ddd.impl_delivered_amount, ddd.impl_rejected_amount from delivery_document_detail ddd join delivery_document dd on ddd.delivery_document_id = dd.id where delivery_document_id in ");
deliveryDocumentDetailQuerysb.append("(select id from delivery_document where delivery_detail_id = ? ) ");
ps = connPortalBackend.prepareStatement(deliveryDocumentDetailQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
b = 0;
while (rs.next()) {
if (b == 1){
sb.append(BLOCK_SEPARATOR);
}
sb.append(rs.getString("product_code") == null ? "-1":rs.getString("product_code").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("product_name") == null ? "-1":rs.getString("product_name").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("status") == null ? "-1":rs.getString("status").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("amount") == null ? "-1":rs.getString("amount").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("quantity") == null ? "-1":rs.getString("quantity").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("quantity_delivered") == null ? "-1":rs.getString("quantity_delivered").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("impl_delivered_amount") == null ? "-1":rs.getString("impl_delivered_amount").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("impl_rejected_amount") == null ? "-1":rs.getString("impl_rejected_amount").trim()).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("external_id") == null ? "-1":rs.getString("external_id").trim());
b = 1;
}
return sb.toString().replaceAll("'", " ");
}
private ArrayList<MigrationControlDTO> loadMigrationData() throws Throwable {
PreparedStatement ps = null;
ResultSet rs = null;
StringBuilder excludeUsers = new StringBuilder("' '");
ArrayList<MigrationControlDTO> messagesList = new ArrayList<>();
try {
// Traer usuarios a migrar
String query = "SELECT distinct username from migration_control where migrated_status = 'P' and exception_date is not null and exception_date > current_timestamp - interval '1 day'";
ps = connPortalBackend.prepareStatement(query);
log.info(ps.toString()+" - User: "+excludeUsers.toString());
rs = ps.executeQuery();
log.info("Despues de RS - User: "+excludeUsers.toString());
while (rs.next()) {
excludeUsers.append(", '").append(rs.getString("username")).append("'");
log.info("Dentro de RS - User: "+rs.getString("username"));
}
log.info(" - User: "+excludeUsers.toString());
/*
* Para versiones del castPortalNG igual o menor al 2019.02.13 descomentar esta consulta
* y comentar la siguiente.
* */
/*query = "SELECT id, username, domain_name, domain_id, operation_type, event_type, document_time " +
"FROM migration_control WHERE migrated_status = 'P' and username not in ("+
excludeUsers.toString() + ") ORDER BY document_time LIMIT 50";*/
/*
* Desde la version superior a 2019.02.13 del castPortalNG, ya se excluyen los device info events porque los mismos se migran
* con un JOB que ya incorpora el castPortalNG
**/
query = "SELECT id, username, domain_name, domain_id, operation_type, event_type, document_time " +
"FROM migration_control WHERE migrated_status = 'P' and username not in ("+
excludeUsers.toString() + ") and username in ('595980123146') AND domain_name != 'device_info' AND domain_name != 'device_info_event' ORDER BY document_time LIMIT 100";
log.info(query);
ps = connPortalBackend.prepareStatement(query);
rs = ps.executeQuery();
MigrationControlDTO mc;
while (rs.next()) {
mc = new MigrationControlDTO();
mc.migrationControlId = rs.getLong("id");
mc.documentTime = rs.getTimestamp("document_time").getTime();
mc.eventType = rs.getString("event_type");
mc.operationType = rs.getString("operation_type");
mc.domainName = rs.getString("domain_name");
mc.domainId = rs.getString("domain_id");
mc.username = rs.getString("username");
messagesList.add(mc);
//cargamos el username de usuarios para en adelante consultar la clave de acceso de los mismos
userAccessKeyMap.put(rs.getString("username"), "");
}
} catch (Throwable t) {
log.info("---- Error al obtener datos a ser migrados ----");
throw t;
} finally {
DataBaseHelper.closePs(ps);
DataBaseHelper.closeRs(rs);
}
return messagesList;
}
// DTO
public class MigrationControlDTO {
Long migrationControlId = 0L;
String eventType = "";
Long documentTime = 0L;
String operationType = "";
String domainName = "";
String domainId = "";
String username = "";
HashMap domainIdsMap = new HashMap<>();
boolean hasError = false;
String msgError = "";
Timestamp exceptionDate = null;
@Override
public String toString() {
return "Message { domainName='" + domainName + '\'' + ", domainId='" + domainId + '\'' + ", migrationControlId='" + migrationControlId + '\'' + ", eventType='" + eventType + '\'' + ", operationType='" + operationType + '\'' + ", username='" + username + '\'' + '}';
}
}
private class DeviceInfoDTO {
private Long id; //migrationId
private Date persistedDate;
private String serviceCode;
private String mobileUser;
private String mobilePin;
private String requestType;
private String requestId;
private String request;
private String currentMillis;
private String imei;
private String appVersion;
private String firstConfigMillis;
private String changedConfigMillis;
private String queueCount;
private String spaceAvailable;
private String spaceUsed;
private String memoryAvailable;
private String memoryUsed;
private String appOpenMillis;
private String appClosedMillis;
private String failedSendMessageCount;
private String stgStartMillis;
private String stgFinishMillis;
private String stgDetails;
private String stgTotalRows;
private String exceptionsCount;
private String lastCleanedCount;
private String gatewayId;
private long deviceInfoTime;
private String opt01;
private String opt02;
private String opt03;
private String opt04;
private String opt05;
private String opt06;
private String opt07;
private String opt08;
private String opt09;
private String opt10;
private String opt11;
private String opt12;
private String opt13;
private String opt14;
private String opt15;
private String opt16;
private String opt17;
private String opt18;
private String opt19;
private String opt20;
private String opt21;
private String opt22;
private String opt23;
private String opt24;
private String opt25;
private String opt26;
private String opt27;
private String opt28;
private String opt29;
private String opt30;
public void setId(Long id) {
this.id = id;
}
public void setPersistedDate(Date persistedDate) {
this.persistedDate = persistedDate;
}
public String getServiceCode() {
return serviceCode;
}
public void setServiceCode(String serviceCode) {
this.serviceCode = serviceCode;
}
public String getMobileUser() {
return mobileUser;
}
public void setMobileUser(String mobileUser) {
this.mobileUser = mobileUser;
}
public String getMobilePin() {
return mobilePin;
}
public void setMobilePin(String mobilePin) {
this.mobilePin = mobilePin;
}
public String getRequestType() {
return requestType;
}
public void setRequestType(String requestType) {
this.requestType = requestType;
}
public String getRequestId() {
return requestId;
}
public void setRequestId(String requestId) {
this.requestId = requestId;
}
public String getRequest() {
return request;
}
public void setRequest(String request) {
this.request = request;
}
public String getCurrentMillis() {
return currentMillis;
}
public void setCurrentMillis(String currentMillis) {
this.currentMillis = currentMillis;
}
public String getImei() {
return imei;
}
public void setImei(String imei) {
this.imei = imei;
}
public String getAppVersion() {
return appVersion;
}
public void setAppVersion(String appVersion) {
this.appVersion = appVersion;
}
public String getFirstConfigMillis() {
return firstConfigMillis;
}
public void setFirstConfigMillis(String firstConfigMillis) {
this.firstConfigMillis = firstConfigMillis;
}
public String getChangedConfigMillis() {
return changedConfigMillis;
}
public void setChangedConfigMillis(String changedConfigMillis) {
this.changedConfigMillis = changedConfigMillis;
}
public String getQueueCount() {
return queueCount;
}
public void setQueueCount(String queueCount) {
this.queueCount = queueCount;
}
public String getSpaceAvailable() {
return spaceAvailable;
}
public void setSpaceAvailable(String spaceAvailable) {
this.spaceAvailable = spaceAvailable;
}
public String getSpaceUsed() {
return spaceUsed;
}
public void setSpaceUsed(String spaceUsed) {
this.spaceUsed = spaceUsed;
}
public String getMemoryAvailable() {
return memoryAvailable;
}
public void setMemoryAvailable(String memoryAvailable) {
this.memoryAvailable = memoryAvailable;
}
public String getMemoryUsed() {
return memoryUsed;
}
public void setMemoryUsed(String memoryUsed) {
this.memoryUsed = memoryUsed;
}
public String getAppOpenMillis() {
return appOpenMillis;
}
public void setAppOpenMillis(String appOpenMillis) {
this.appOpenMillis = appOpenMillis;
}
public String getAppClosedMillis() {
return appClosedMillis;
}
public void setAppClosedMillis(String appClosedMillis) {
this.appClosedMillis = appClosedMillis;
}
public String getFailedSendMessageCount() {
return failedSendMessageCount;
}
public void setFailedSendMessageCount(String failedSendMessageCount) {
this.failedSendMessageCount = failedSendMessageCount;
}
public String getStgStartMillis() {
return stgStartMillis;
}
public void setStgStartMillis(String stgStartMillis) {
this.stgStartMillis = stgStartMillis;
}
public String getStgFinishMillis() {
return stgFinishMillis;
}
public void setStgFinishMillis(String stgFinishMillis) {
this.stgFinishMillis = stgFinishMillis;
}
public String getStgDetails() {
return stgDetails;
}
public void setStgDetails(String stgDetails) {
this.stgDetails = stgDetails;
}
public String getStgTotalRows() {
return stgTotalRows;
}
public void setStgTotalRows(String stgTotalRows) {
this.stgTotalRows = stgTotalRows;
}
public String getExceptionsCount() {
return exceptionsCount;
}
public void setExceptionsCount(String exceptionsCount) {
this.exceptionsCount = exceptionsCount;
}
public String getLastCleanedCount() {
return lastCleanedCount;
}
public void setLastCleanedCount(String lastCleanedCount) {
this.lastCleanedCount = lastCleanedCount;
}
public void setGatewayId(String gatewayId){
this.gatewayId = gatewayId;
}
public void setDeviceInfoTime(Long deviceInfoTime) {
this.deviceInfoTime = deviceInfoTime;
}
public void setOpt01(String opt01) {
this.opt01 = opt01;
}
public void setOpt02(String opt02) {
this.opt02 = opt02;
}
public void setOpt03(String opt03) {
this.opt03 = opt03;
}
public void setOpt04(String opt04) {
this.opt04 = opt04;
}
public void setOpt05(String opt05) {
this.opt05 = opt05;
}
public void setOpt06(String opt06) {
this.opt06 = opt06;
}
public void setOpt07(String opt07) {
this.opt07 = opt07;
}
public void setOpt08(String opt08) {
this.opt08 = opt08;
}
public void setOpt09(String opt09) {
this.opt09 = opt09;
}
public void setOpt10(String opt10) {
this.opt10 = opt10;
}
public void setOpt11(String opt11) {
this.opt11 = opt11;
}
public void setOpt12(String opt12) {
this.opt12 = opt12;
}
public void setOpt13(String opt13) {
this.opt13 = opt13;
}
public void setOpt14(String opt14) {
this.opt14 = opt14;
}
public void setOpt15(String opt15) {
this.opt15 = opt15;
}
public void setOpt16(String opt16) {
this.opt16 = opt16;
}
public void setOpt17(String opt17) {
this.opt17 = opt17;
}
public String getOpt18() {
return opt18;
}
public void setOpt18(String opt18) {
this.opt18 = opt18;
}
public void setOpt19(String opt19) {
this.opt19 = opt19;
}
public void setOpt20(String opt20) {
this.opt20 = opt20;
}
public void setOpt21(String opt21) {
this.opt21 = opt21;
}
public void setOpt22(String opt22) {
this.opt22 = opt22;
}
public void setOpt23(String opt23) {
this.opt23 = opt23;
}
public void setOpt24(String opt24) {
this.opt24 = opt24;
}
public void setOpt25(String opt25) {
this.opt25 = opt25;
}
public void setOpt26(String opt26) {
this.opt26 = opt26;
}
public void setOpt27(String opt27) {
this.opt27 = opt27;
}
public void setOpt28(String opt28) {
this.opt28 = opt28;
}
public void setOpt29(String opt29) {
this.opt29 = opt29;
}
public void setOpt30(String opt30) {
this.opt30 = opt30;
}
}
private class DeviceInfoEventDTO {
private Long id; //migrationId
private Date persistedDate;
private String serviceCode;
private String mobileUser;
private String deviceInfoEventTime;
private String eventType;
private String eventMessage;
private String gatewayId;
private String imei;
private String migratedDate;
public void setId(Long id){
this.id = id;
}
public void setPersistedDate(Date persistedDate ){
this.persistedDate = persistedDate;
}
public void setServiceCode(String serviceCode){
this.serviceCode = serviceCode;
}
public void setMobileUser(String mobileUser){
this.mobileUser = mobileUser;
}
public void setDeviceInfoEventTime(String deviceInfoEventTime){
this.deviceInfoEventTime = deviceInfoEventTime;
}
public void setEventType(String eventType){
this.eventType = eventType;
}
public void setEventMessage(String eventMessage){
this.eventMessage = eventMessage;
}
public void setGatewayId(String gatewayId){
this.gatewayId = gatewayId;
}
public void setImei(String imei){
this.imei = imei;
}
public void setMigratedDate(String migratedDate) {
this.migratedDate = migratedDate;
}
}
private void sendMail(String mailBody, ArrayList<String> mailTo, String mailFrom, String mailSubject){
MailService mailService = (MailService)appCtx.getBean("mailService");
String serviceName;
try {
InetAddress localMachine = null;
localMachine = InetAddress.getLocalHost();
String[] localMachineData= localMachine.toString().split("/");
serviceName = localMachineData[0];
} catch (Throwable e) {
log.error("Error al obtener el nombre del servicio: " + e);
serviceName = "";
}
if (mailSubject == null) {
mailSubject = "Migrador castPortal3";
}
mailSubject = serviceName.toUpperCase() +" - " + mailSubject;
if (mailFrom == null) {
mailFrom = "[email protected]";
}
if (mailTo == null) {
mailTo = new ArrayList<>();
mailTo.add("[email protected]");
}
try {
mailService.sendTextMail(mailFrom, mailTo, mailSubject, mailBody);
} catch (Throwable tm) {
sendMailInOldFormat(mailFrom, mailTo, mailSubject, mailBody, mailService);
}
log.info("-------------------- Mail enviado -----------------------");
}
private void sendMailInOldFormat(String mailFrom, ArrayList<String> mailTo, String mailSubject, String mailBody, MailService mailService) {
mailService.sendMail { //TODO: descomentar
to mailTo;
from mailFrom;
subject mailSubject;
body mailBody;
}
}
private String generateReceiptMessage(MigrationControlDTO message) throws Throwable {
log.info("ingresa en la funcion generateReceiptMessage que trae los datos");
StringBuilder sb;
StringBuilder receiptQuerysb = new StringBuilder();
receiptQuerysb.append("select r.id as replica_id, subject_code, subject_name, document_date, subject_selected_type, receipt_number, (c.currency_symbol || '.' || currency_payment_name) as currency_payment, total_amount, total_amount_paid, receipt_balance, user_id, user_code, user_full_name, observation, corporation_subject_code, 'CONTADO' as type, (c.currency_symbol || '.' || currency_documents_name) currency_documents, currency_exchange_amount, print_text, fiscal_stamp_number from receipt r join currency c on r.currency_payment_name = c.description where r.id = ?");
ps = connPortalBackend.prepareStatement(receiptQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
sb.append(".CAST_SP2").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_date")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("receipt_number")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("currency_payment")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("total_amount")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("total_amount_paid")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("receipt_balance")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("user_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("user_full_name")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("observation")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("corporation_subject_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("currency_documents")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("currency_exchange_amount")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("type")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_selected_type")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("print_text")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("fiscal_stamp_number")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("replica_id")).append(PARAMETER_SEPARATOR);
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
sb.append(BLOCK_SEPARATOR_DETAILS);
StringBuilder receiptDocumentsQuerysb = new StringBuilder();
receiptDocumentsQuerysb.append("select document_number, document_total_amount, amount_paid, fee_pending_amount, document_date, document_expiration_date, fee_number, fee_total_amount, fee_expiration_date, document_pending_amount, document_type from receipt_documents where receipt_id = ?");
ps = connPortalBackend.prepareStatement(receiptDocumentsQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
int b = 0;
while (rs.next()) {
if (b == 1){
sb.append(BLOCK_SEPARATOR);
}
sb.append(rs.getString("document_number")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_total_amount")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("amount_paid")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_pending_amount")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_date")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_expiration_date")).append(PARAMETER_SEPARATOR);
//sb.append(rs.getString("document_type")).append(PARAMETER_SEPARATOR);
//sb.append(rs.getString("fee_number")).append(PARAMETER_SEPARATOR);
//sb.append(rs.getString("fee_total_amount")).append(PARAMETER_SEPARATOR);
//sb.append(rs.getString("fee_expiration_date")).append(PARAMETER_SEPARATOR);
b = 1;
}
sb.append(BLOCK_SEPARATOR_DETAILS);
StringBuilder receiptValuesQuerysb = new StringBuilder();
receiptValuesQuerysb.append("select (payment_method_code || '.' || payment_method_name) as payment, value_amount, ");
receiptValuesQuerysb.append("(case when bank_code != '' then (bank_code || '.' || bank_description) else '-1' end) as bank, document_number, document_date, payment_method_code, ");
receiptValuesQuerysb.append("document_expiration_date, document_drawer from receipt_values where receipt_id = ? ");
ps = connPortalBackend.prepareStatement(receiptValuesQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
b = 0;
while (rs.next()) {
if (b == 1){
sb.append(BLOCK_SEPARATOR);
}
sb.append(rs.getString("value_amount") == null ? "-1":rs.getString("value_amount") ).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("payment")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("bank") == null ? "-1":rs.getString("bank") ).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_number").length() == 0 ? "-1":rs.getString("document_number") ).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_date") == null ? "-1":rs.getString("document_date") ).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_expiration_date") == null ? "-1":rs.getString("document_expiration_date") ).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("document_drawer").length() == 0 ? "-1":rs.getString("document_drawer") ).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("payment_method_code").length() == 0 ? "-1":rs.getString("payment_method_code") ).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
b = 1;
}
return sb.toString().replaceAll("'", " ");
}
private String generateAnnulationReceiptMessage(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
StringBuilder receiptQuerysb = new StringBuilder();
receiptQuerysb.append("select fiscal_stamp_number, subject_code as subject_code, subject_name, subject_fiscal_number, receipt_number, cancelled_reason, cancelled_date, to_char(document_date, 'YYYY-MM-DD') as receipt_date, cancelled_date ");
receiptQuerysb.append("from receipt where id = ? ");
ps = connPortalBackend.prepareStatement(receiptQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
sb.append(".AR").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_fiscal_number")).append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append("-1").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("receipt_number")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("cancelled_reason")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("receipt_date")).append(PARAMETER_SEPARATOR);
//sb.append(rs.getString("fiscal_stamp_number")).append(PARAMETER_SEPARATOR);
//sb.append(rs.getString("cancelled_date")).append(PARAMETER_SEPARATOR);
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
return sb.toString();
}
private String generateReprintUpdateReceiptMessage(MigrationControlDTO message) throws Throwable {
log.info("ingresa en la funcion generateReprintUpdateReceiptMessage");
StringBuilder sb;
StringBuilder receiptQuerysb = new StringBuilder();
receiptQuerysb.append("SELECT subject_code, receipt_number, 'receipt' AS type, reprint_quantity FROM receipt WHERE id = ? ");
ps = connPortalBackend.prepareStatement(receiptQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
sb.append(".CAST_SPC").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("receipt_number")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("type")).append(PARAMETER_SEPARATOR);
sb.append(rs.getString("reprint_quantity"));
} else {
throw new Exception("Mensaje ID a procesar no encontrado ");
}
return sb.toString();
}
private String generateOrdersMessage(MigrationControlDTO message) throws Throwable {
StringBuilder sb;
StringBuilder ordersQuerysb = new StringBuilder();
ordersQuerysb.append("select subject_code, o.subject_name, subject_fiscal_number, s.impl_porcentaje_descuento, o.impl_subject_segment as segment, '-1' as cod_condicion_de_venta, '-1' as subject_type, subject_address, subject_telephone, '-1' as subject_status, '-1' as subject_credit_line, '-1' as subject_delinquency, cast(delivery_date as date) as delivery_date, '-1' as timbrado, '-1' as validez, '-1' as nro_de_factura, cast(document_date as date) as orders_date, price_list_code || '.' || price_list_name as price_list, sale_condition_code || '.' || sale_condition_name as sale_condition, '-1' condition, '-1' as vencimiento, currency_code || '.' || currency_name as currency, currency_exchange_amount as currency_exchange, '-1' as codigo_producto, '-1' as descuento_maximo, '-1' as discount_rate,'-1' as bonus_quantity, (select sum(case cast(product_vat_rate_value as int) when 5 then product_vat_amount else 0 end) from orders_detail where orders_id = o.id) as vat_05_total, (select sum(case cast(product_vat_rate_value as int) when 10 then product_vat_amount else 0 end) from orders_detail where orders_id = o.id) as vat_10_total, (select sum(case cast(product_vat_rate_value as int) when 5 then product_vatted_amount else 0 end) from orders_detail where orders_id = o.id) as vatted_05_total, (select sum(case cast(product_vat_rate_value as int) when 10 then product_vatted_amount else 0 end) from orders_detail where orders_id = o.id) as vatted_10_total, (select sum(case cast(product_vat_rate_value as int) when 0 then product_vatted_amount else 0 end) from orders_detail where orders_id = o.id) as exempt_total, total_vat_amount, total_gross_amount, total_discount_amount, '-1' as bonus_total, total_net_amount, user_code, user_full_name, '-1' as warehouse, '-1' as cash, '-1' as credito, observation, '-1' as opt1, '-1' as opt2, '-1' as opt3, '-1' as opt4, '-1' as opt5, COALESCE(impl_print_text,'-1') as opt7, '-1' as opt6, '-1' as opt8, '-1' as opt9, '-1' as opt10 from orders o join subject s on s.id = o.subject_id where o.id = ?");
ps = connPortalBackend.prepareStatement(ordersQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
if (rs.next()) {
sb = new StringBuilder();
sb.append(".CAST_SO3").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);//1
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);//2
sb.append(rs.getString("subject_fiscal_number")).append(PARAMETER_SEPARATOR);//3
sb.append(rs.getString("impl_porcentaje_descuento")).append(PARAMETER_SEPARATOR);//4
sb.append(rs.getString("segment")).append(PARAMETER_SEPARATOR);//5
sb.append(rs.getString("cod_condicion_de_venta")).append(PARAMETER_SEPARATOR);//6
sb.append(rs.getString("subject_type")).append(PARAMETER_SEPARATOR);//7
sb.append(rs.getString("subject_address")).append(PARAMETER_SEPARATOR);//8
sb.append(rs.getString("subject_telephone")).append(PARAMETER_SEPARATOR);//9
sb.append(rs.getString("subject_status")).append(PARAMETER_SEPARATOR);//10
sb.append(rs.getString("subject_credit_line")).append(PARAMETER_SEPARATOR);//11
sb.append(rs.getString("subject_delinquency")).append(PARAMETER_SEPARATOR);//12
sb.append(rs.getString("delivery_date")).append(PARAMETER_SEPARATOR);//13
sb.append(rs.getString("timbrado")).append(PARAMETER_SEPARATOR);//14
sb.append(rs.getString("validez")).append(PARAMETER_SEPARATOR);//15
sb.append(rs.getString("nro_de_factura")).append(PARAMETER_SEPARATOR);//16
sb.append(rs.getString("orders_date")).append(PARAMETER_SEPARATOR);//17
sb.append(rs.getString("price_list")).append(PARAMETER_SEPARATOR);//18
sb.append(rs.getString("sale_condition")).append(PARAMETER_SEPARATOR);//19
sb.append(rs.getString("condition")).append(PARAMETER_SEPARATOR);//20
sb.append(rs.getString("vencimiento")).append(PARAMETER_SEPARATOR);//21
sb.append(rs.getString("currency")).append(PARAMETER_SEPARATOR);//22
sb.append(rs.getString("currency_exchange")).append(PARAMETER_SEPARATOR);//23
sb.append(rs.getString("codigo_producto")).append(PARAMETER_SEPARATOR);//24
sb.append(rs.getString("descuento_maximo")).append(PARAMETER_SEPARATOR);//25
sb.append(rs.getString("discount_rate")).append(PARAMETER_SEPARATOR);//26
sb.append(rs.getString("bonus_quantity")).append(PARAMETER_SEPARATOR);//27
sb.append(rs.getString("vat_05_total")).append(PARAMETER_SEPARATOR);//28
sb.append(rs.getString("vat_10_total")).append(PARAMETER_SEPARATOR);//29
sb.append(rs.getString("vatted_05_total")).append(PARAMETER_SEPARATOR);//30
sb.append(rs.getString("vatted_10_total")).append(PARAMETER_SEPARATOR);//31
sb.append(rs.getString("exempt_total")).append(PARAMETER_SEPARATOR);//32
sb.append(rs.getString("total_vat_amount")).append(PARAMETER_SEPARATOR);//33
sb.append(rs.getString("total_gross_amount")).append(PARAMETER_SEPARATOR);//34
sb.append(rs.getString("total_discount_amount")).append(PARAMETER_SEPARATOR);//35
sb.append(rs.getString("bonus_total")).append(PARAMETER_SEPARATOR);//36
sb.append(rs.getString("total_net_amount")).append(PARAMETER_SEPARATOR);//37
sb.append(rs.getString("user_code")).append(PARAMETER_SEPARATOR);//38
sb.append(rs.getString("user_full_name")).append(PARAMETER_SEPARATOR);//39
sb.append(rs.getString("warehouse")).append(PARAMETER_SEPARATOR);//40
sb.append(rs.getString("cash")).append(PARAMETER_SEPARATOR);//41
sb.append(rs.getString("credito")).append(PARAMETER_SEPARATOR);//42
sb.append(rs.getString("observation")).append(PARAMETER_SEPARATOR);//43
sb.append(rs.getString("opt1")).append(PARAMETER_SEPARATOR);//44
sb.append(rs.getString("opt2")).append(PARAMETER_SEPARATOR);//45
sb.append(rs.getString("opt3")).append(PARAMETER_SEPARATOR);//46
sb.append(rs.getString("opt4")).append(PARAMETER_SEPARATOR);//47
sb.append(rs.getString("opt5")).append(PARAMETER_SEPARATOR);//48
sb.append(rs.getString("opt6")).append(PARAMETER_SEPARATOR);//49
sb.append(rs.getString("opt7")).append(PARAMETER_SEPARATOR);//50
sb.append(rs.getString("opt8")).append(PARAMETER_SEPARATOR);//51
sb.append(rs.getString("opt9")).append(PARAMETER_SEPARATOR);//52
sb.append(rs.getString("opt10")).append(PARAMETER_SEPARATOR);//53
sb.append("-1").append(PARAMETER_SEPARATOR);//54
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
StringBuilder ordersDetailQuerysb = new StringBuilder();
ordersDetailQuerysb.append("select product_presentation_code, product_presentation_name, '-1' as product_select, cast(product_vat_rate_value as int) as product_vat_rate, price,'-1' as stock, '-1' as promotion_name, '-1' as discount_promotion,quantity, '-1' as promo_a_aplicar,'-1' as color, (select unit from product_presentation where id = orders_detail.product_presentation_id) as product_unit, '-1' as cantidad_equivalente,'-1' as vencimiento_lote, product_gross_amount, '-1' as max_discount, case when discount_rate is null then 0 else discount_rate end as discount_rate, product_discount_amount as discount_amount, '-1' as bonificacion_maxima, bonus_quantity, '-1' as total_quantity, bonus_amount,product_net_amount, '-1' as iva_05, '-1' as iva_10, '-1' as exenta, case cast(product_vat_rate_value as int) when 5 then product_vat_amount else 0 end as vat_05_amount, case cast(product_vat_rate_value as int) when 10 then product_vat_amount else 0 end as vat_10_amount, product_vat_amount, case cast(product_vat_rate_value as int) when 5 then product_vatted_amount else 0 end as vatted_05_amount, case cast(product_vat_rate_value as int) when 10 then product_vatted_amount else 0 end as vatted_10_amount, case cast(product_vat_rate_value as int) when 0 then product_vatted_amount else 0 end as exempt_amount, '-1' as opt1, '-1' as opt2,'-1' as opt3,'-1' as opt4,'-1' as opt5,'-1' as opt6,'-1' as opt7,'-1' as opt8,'-1' as opt9,'-1' as opt10 from orders_detail where orders_id = ?");
ps = connPortalBackend.prepareStatement(ordersDetailQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
while (rs.next()) {
sb.append(BLOCK_SEPARATOR);
sb.append(rs.getString("product_presentation_code")).append(PARAMETER_SEPARATOR);//1
sb.append(rs.getString("product_presentation_name")).append(PARAMETER_SEPARATOR);//2
sb.append(rs.getString("product_select")).append(PARAMETER_SEPARATOR);//3
sb.append(rs.getString("product_vat_rate")).append(PARAMETER_SEPARATOR);//4
//sb.append(rs.getString("price_list")).append(PARAMETER_SEPARATOR);//5
sb.append(rs.getString("price")).append(PARAMETER_SEPARATOR);//6
sb.append(rs.getString("stock")).append(PARAMETER_SEPARATOR);//7
sb.append(rs.getString("promotion_name")).append(PARAMETER_SEPARATOR);//8
sb.append(rs.getString("discount_promotion")).append(PARAMETER_SEPARATOR);//9
sb.append(rs.getString("quantity")).append(PARAMETER_SEPARATOR);//10
sb.append(rs.getString("promo_a_aplicar")).append(PARAMETER_SEPARATOR);//11
sb.append(rs.getString("color")).append(PARAMETER_SEPARATOR);//12
sb.append(rs.getString("product_unit")).append(PARAMETER_SEPARATOR);//13
sb.append(rs.getString("cantidad_equivalente")).append(PARAMETER_SEPARATOR);//14
sb.append(rs.getString("vencimiento_lote")).append(PARAMETER_SEPARATOR);//15
sb.append(rs.getString("max_discount")).append(PARAMETER_SEPARATOR);//16
sb.append(rs.getString("discount_rate")).append(PARAMETER_SEPARATOR);//17
sb.append(rs.getString("discount_amount")).append(PARAMETER_SEPARATOR);//18
sb.append(rs.getString("bonificacion_maxima")).append(PARAMETER_SEPARATOR);//19
sb.append(rs.getString("bonus_quantity")).append(PARAMETER_SEPARATOR);//20
sb.append(rs.getString("total_quantity")).append(PARAMETER_SEPARATOR);//21
sb.append(rs.getString("bonus_amount")).append(PARAMETER_SEPARATOR);//22
sb.append(rs.getString("product_net_amount")).append(PARAMETER_SEPARATOR);//23
sb.append(rs.getString("iva_05")).append(PARAMETER_SEPARATOR);//24
sb.append(rs.getString("iva_10")).append(PARAMETER_SEPARATOR);//25
sb.append(rs.getString("exenta")).append(PARAMETER_SEPARATOR);//26
sb.append(rs.getString("vat_05_amount")).append(PARAMETER_SEPARATOR);//27
sb.append(rs.getString("vat_10_amount")).append(PARAMETER_SEPARATOR);//28
sb.append(rs.getString("product_vat_amount")).append(PARAMETER_SEPARATOR);//29
sb.append(rs.getString("vatted_05_amount")).append(PARAMETER_SEPARATOR);//30
sb.append(rs.getString("vatted_10_amount")).append(PARAMETER_SEPARATOR);//31
sb.append(rs.getString("exempt_amount")).append(PARAMETER_SEPARATOR);//32
sb.append(rs.getString("opt1")).append(PARAMETER_SEPARATOR);//33
sb.append(rs.getString("opt2")).append(PARAMETER_SEPARATOR);//34
sb.append(rs.getString("opt3")).append(PARAMETER_SEPARATOR);//35
sb.append(rs.getString("opt4")).append(PARAMETER_SEPARATOR);//36
sb.append(rs.getString("opt5")).append(PARAMETER_SEPARATOR);//37
sb.append(rs.getString("opt6")).append(PARAMETER_SEPARATOR);//38
sb.append(rs.getString("opt7")).append(PARAMETER_SEPARATOR);//39
sb.append(rs.getString("opt8")).append(PARAMETER_SEPARATOR);//40
sb.append(rs.getString("opt9")).append(PARAMETER_SEPARATOR);//41
sb.append(rs.getString("opt10")).append(PARAMETER_SEPARATOR);//42
}
return sb.toString();
}
private String generateInvoiceVDMessage(MigrationControlDTO message) throws Throwable {
log.info("----- Ingresa al metodo de generar mensaje venta directa ----------");
StringBuilder sb;
StringBuilder invoiceQuerysb = new StringBuilder();
invoiceQuerysb.append("select subject_code, subject_name, subject_fiscal_number, ");
invoiceQuerysb.append("subject_commercial_name, '-1' as subject_contact_name, '1' as subject_type, ");
invoiceQuerysb.append("subject_address, subject_telephone, '-1' as subject_status, '-1' as subject_credit_line, '-1' as subject_credit_available, ");
invoiceQuerysb.append("fiscal_stamp_number, to_date(fiscal_stamp_number_end, 'YYYY-MM-DD') as validez, ");
invoiceQuerysb.append("invoice_number, to_date(document_date, 'YYYY-MM-DD') as invoice_date, (price_list_code || '.' || price_list_name) as price_list, '1.Efectivo' as payment_method, ");
invoiceQuerysb.append("(select c.code || '.' || c.description from sale_condition c where c.id = sale_condition_id ) as sale_condition, ");
invoiceQuerysb.append("'-1' as condition, '-1' as vencimiento, (currency_id || '.' || currency_name) as currency, currency_exchange_amount as currency_exchange, ");
invoiceQuerysb.append("'-1' as codigo_producto, '-1' as descuento_maximo, '-1' as discount_rate,'-1' as bonus_quantity, ");
invoiceQuerysb.append("(select sum(case cast(product_vat_rate_value as int) when 5 then product_vat_amount else 0 end) ");
invoiceQuerysb.append("from invoice_detail where invoice_id = i.id) as vat_05_total, ");
invoiceQuerysb.append("(select sum(case cast(product_vat_rate_value as int) when 10 then product_vat_amount else 0 end) ");
invoiceQuerysb.append("from invoice_detail where invoice_id = i.id) as vat_10_total, ");
invoiceQuerysb.append("(select sum(case cast(product_vat_rate_value as int) when 5 then product_vatted_amount else 0 end) ");
invoiceQuerysb.append("from invoice_detail where invoice_id = i.id) as vatted_05_total, ");
invoiceQuerysb.append("(select sum(case cast(product_vat_rate_value as int) when 10 then product_vatted_amount else 0 end) ");
invoiceQuerysb.append("from invoice_detail where invoice_id = i.id) as vatted_10_total, ");
invoiceQuerysb.append("(select sum(case cast(product_vat_rate_value as int) when 0 then product_vatted_amount else 0 end) ");
invoiceQuerysb.append("from invoice_detail where invoice_id = i.id) as exempt_total, ");
invoiceQuerysb.append("total_vat_amount, total_gross_amount, total_discount_amount, ");
invoiceQuerysb.append("'-1' as bonus_total, total_net_amount, user_code, user_full_name, ");
invoiceQuerysb.append("'-1' as warehouse_code, '-1' as cash, '-1' as credito, observation, ");
invoiceQuerysb.append("'1' as opt1, '1' as opt2, subject_code as opt3,'Contado' as opt4, ");
invoiceQuerysb.append("'0' as opt5, '-1' as opt6, '-1' as opt7, ");
invoiceQuerysb.append("'-1' as opt8, '-1' as opt9, '-1' as opt10, '-1' as print_text ");
invoiceQuerysb.append("from invoice i where id = ? order by document_date ");
ps = connPortalBackend.prepareStatement(invoiceQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
String attributeAssignmentId = "-1";
if (rs.next()) {
sb = new StringBuilder();
/* if(rs.getString("opt10").equals("PROMOCIONAL")){
attributeAssignmentId = getAttributeValue(rs.getString("subject_id"), rs.getString("user_id"));
}*/
sb.append(".CAST_SI2").append(PARAMETER_SEPARATOR);
sb.append(rs.getString("subject_code")).append(PARAMETER_SEPARATOR);//1
sb.append(rs.getString("subject_name")).append(PARAMETER_SEPARATOR);//2
sb.append(rs.getString("subject_fiscal_number")).append(PARAMETER_SEPARATOR);//3
sb.append(rs.getString("subject_commercial_name").length() == 0 ? "-1":rs.getString("subject_commercial_name")).append(PARAMETER_SEPARATOR);//4
sb.append(rs.getString("subject_contact_name")).append(PARAMETER_SEPARATOR);//5
sb.append(rs.getString("subject_type")).append(PARAMETER_SEPARATOR);//6
sb.append(rs.getString("subject_address")).append(PARAMETER_SEPARATOR);//7
sb.append(rs.getString("subject_telephone")).append(PARAMETER_SEPARATOR);//8
sb.append(rs.getString("subject_status")).append(PARAMETER_SEPARATOR);//9
sb.append(rs.getString("subject_credit_line")).append(PARAMETER_SEPARATOR);//10
sb.append(rs.getString("subject_credit_available")).append(PARAMETER_SEPARATOR);//11
sb.append(rs.getString("fiscal_stamp_number")).append(PARAMETER_SEPARATOR);//12
sb.append(rs.getString("validez")).append(PARAMETER_SEPARATOR);//13
sb.append(rs.getString("invoice_number")).append(PARAMETER_SEPARATOR);//14
sb.append(rs.getString("invoice_date")).append(PARAMETER_SEPARATOR);//15
sb.append(rs.getString("price_list")).append(PARAMETER_SEPARATOR);//16
sb.append(rs.getString("payment_method")).append(PARAMETER_SEPARATOR);//17
sb.append(rs.getString("sale_condition")).append(PARAMETER_SEPARATOR);//18
sb.append(rs.getString("vencimiento")).append(PARAMETER_SEPARATOR);//19
sb.append(rs.getString("currency")).append(PARAMETER_SEPARATOR);//20
sb.append(rs.getString("currency_exchange")).append(PARAMETER_SEPARATOR);//21
sb.append(rs.getString("codigo_producto")).append(PARAMETER_SEPARATOR);//22
sb.append(rs.getString("descuento_maximo")).append(PARAMETER_SEPARATOR);//23
sb.append(rs.getString("discount_rate")).append(PARAMETER_SEPARATOR);//24
sb.append(rs.getString("bonus_quantity")).append(PARAMETER_SEPARATOR);//25
sb.append(rs.getString("vat_05_total")).append(PARAMETER_SEPARATOR);//26
sb.append(rs.getString("vat_10_total")).append(PARAMETER_SEPARATOR);//27
sb.append(rs.getString("vatted_05_total")).append(PARAMETER_SEPARATOR);//28
sb.append(rs.getString("vatted_10_total")).append(PARAMETER_SEPARATOR);//29
sb.append(rs.getString("exempt_total")).append(PARAMETER_SEPARATOR);//30
sb.append(rs.getString("total_vat_amount")).append(PARAMETER_SEPARATOR);//31
sb.append(rs.getString("total_gross_amount")).append(PARAMETER_SEPARATOR);//32
sb.append(rs.getString("total_discount_amount")).append(PARAMETER_SEPARATOR);//33
sb.append(rs.getString("bonus_total")).append(PARAMETER_SEPARATOR);//34
sb.append(rs.getString("total_net_amount")).append(PARAMETER_SEPARATOR);//35
sb.append(rs.getString("user_code")).append(PARAMETER_SEPARATOR);//36
sb.append(rs.getString("user_full_name")).append(PARAMETER_SEPARATOR);//37
sb.append(rs.getString("warehouse_code")).append(PARAMETER_SEPARATOR);//38
sb.append(rs.getString("cash")).append(PARAMETER_SEPARATOR);//39
sb.append(rs.getString("credito")).append(PARAMETER_SEPARATOR);//40
sb.append(rs.getString("observation").length() == 0 ? "-1": rs.getString("observation")).append(PARAMETER_SEPARATOR);//41
sb.append(rs.getString("opt1")).append(PARAMETER_SEPARATOR);//42
sb.append(rs.getString("opt2")).append(PARAMETER_SEPARATOR);//43
sb.append(rs.getString("opt3")).append(PARAMETER_SEPARATOR);//44
sb.append(rs.getString("opt4")).append(PARAMETER_SEPARATOR);//45
sb.append(rs.getString("opt5")).append(PARAMETER_SEPARATOR);//46
sb.append(rs.getString("opt6")).append(PARAMETER_SEPARATOR);//47
sb.append(rs.getString("opt7")).append(PARAMETER_SEPARATOR);//48
sb.append(rs.getString("opt8")).append(PARAMETER_SEPARATOR);//49
sb.append(rs.getString("opt9")).append(PARAMETER_SEPARATOR);//50
sb.append(rs.getString("opt10")).append(PARAMETER_SEPARATOR);//51
sb.append(rs.getString("opt10")).append(PARAMETER_SEPARATOR);//51
sb.append(rs.getString("print_text")).append(PARAMETER_SEPARATOR);//52
} else {
throw new Exception("Mensaje ID a procesar no encontrado");
}
StringBuilder invoiceDetailQuerysb = new StringBuilder();
invoiceDetailQuerysb.append("select product_code, product_name, '-1' as product_select, ");
invoiceDetailQuerysb.append("cast(product_vat_rate_value as int) as product_vat_rate, ");
invoiceDetailQuerysb.append("'-1' as price_list, price, product_batch_name as batch, '-1' as design, '-1' as size, ");
invoiceDetailQuerysb.append("'-1' as color, product_size_name as product_unit, '-1' as cantidad_equivalente, ");
invoiceDetailQuerysb.append("product_batch_date as vencimiento_lote, '-1' as existencia, quantity, product_gross_amount, ");
invoiceDetailQuerysb.append("'-1' as max_discount, coalesce(discount_rate,0) as discount_rate, coalesce(discount_amount,0) as discount_amount, ");
invoiceDetailQuerysb.append("'-1' as bonificacion_maxima, '-1' as bonus_quantity, '-1' as total_quantity, ");
invoiceDetailQuerysb.append("'-1' as bonus_amount,product_net_amount, '-1' as iva_05, '-1' as iva_10, '-1' as exenta, ");
invoiceDetailQuerysb.append("case cast(product_vat_rate_value as int) when 5 then product_vat_amount else 0 end as vat_05_amount, ");
invoiceDetailQuerysb.append("case cast(product_vat_rate_value as int) when 10 then product_vat_amount else 0 end as vat_10_amount, ");
invoiceDetailQuerysb.append("product_vat_amount, ");
invoiceDetailQuerysb.append("case cast(product_vat_rate_value as int) when 5 then product_vatted_amount else 0 end as vatted_05_amount, ");
invoiceDetailQuerysb.append("case cast(product_vat_rate_value as int) when 10 then product_vatted_amount else 0 end as vatted_10_amount, ");
invoiceDetailQuerysb.append("case cast(product_vat_rate_value as int) when 0 then product_vatted_amount else 0 end as exempt_amount, ");
invoiceDetailQuerysb.append("'-1' as opt1, '-1' as opt2,'-1' as opt3,'-1' as opt4,'-1' as opt5,'-1' as opt6, ");
invoiceDetailQuerysb.append("'-1' as opt7,'-1' as opt8,'-1' as opt9,'-1' as opt10 ");
invoiceDetailQuerysb.append("from invoice_detail where invoice_id = ? ");
ps = connPortalBackend.prepareStatement(invoiceDetailQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
sb.append(BLOCK_SEPARATOR);
int b=0;
while (rs.next()) {
if (b == 1){
sb.append(BLOCK_SEPARATOR_DETAILS);
}
sb.append(rs.getString("product_code")).append(PARAMETER_SEPARATOR);//0
sb.append(rs.getString("product_name")).append(PARAMETER_SEPARATOR);//1
sb.append(rs.getString("product_select")).append(PARAMETER_SEPARATOR);//2
sb.append(rs.getString("product_vat_rate")).append(PARAMETER_SEPARATOR);//3
sb.append(rs.getString("price_list")).append(PARAMETER_SEPARATOR);//4
sb.append(rs.getString("price")).append(PARAMETER_SEPARATOR);//5
sb.append(rs.getString("batch")).append(PARAMETER_SEPARATOR);//6
sb.append(rs.getString("design")).append(PARAMETER_SEPARATOR);//7
sb.append(rs.getString("size")).append(PARAMETER_SEPARATOR);//8
sb.append(rs.getString("color")).append(PARAMETER_SEPARATOR);//9
sb.append(rs.getString("product_unit")).append(PARAMETER_SEPARATOR);//10
sb.append(rs.getString("cantidad_equivalente")).append(PARAMETER_SEPARATOR);//11
sb.append(rs.getString("vencimiento_lote")).append(PARAMETER_SEPARATOR);//12
sb.append(rs.getString("existencia")).append(PARAMETER_SEPARATOR);//13
sb.append(rs.getString("quantity")).append(PARAMETER_SEPARATOR);//14
sb.append(rs.getString("product_gross_amount")).append(PARAMETER_SEPARATOR);///15
sb.append(rs.getString("max_discount")).append(PARAMETER_SEPARATOR);//16
sb.append(rs.getString("discount_rate")).append(PARAMETER_SEPARATOR);//17
sb.append(rs.getString("discount_amount")).append(PARAMETER_SEPARATOR);//18
sb.append(rs.getString("bonificacion_maxima")).append(PARAMETER_SEPARATOR);//19
sb.append(rs.getString("bonus_quantity")).append(PARAMETER_SEPARATOR);//20
sb.append(rs.getString("total_quantity")).append(PARAMETER_SEPARATOR);//21
sb.append(rs.getString("bonus_amount")).append(PARAMETER_SEPARATOR);//22
sb.append(rs.getString("product_net_amount")).append(PARAMETER_SEPARATOR);//23
sb.append(rs.getString("iva_05")).append(PARAMETER_SEPARATOR);//24
sb.append(rs.getString("iva_10")).append(PARAMETER_SEPARATOR);//25
sb.append(rs.getString("exenta")).append(PARAMETER_SEPARATOR);//26
sb.append(rs.getString("vat_05_amount")).append(PARAMETER_SEPARATOR);//27
sb.append(rs.getString("vat_10_amount")).append(PARAMETER_SEPARATOR);//28
sb.append(rs.getString("product_vat_amount")).append(PARAMETER_SEPARATOR);//29
sb.append(rs.getString("vatted_05_amount")).append(PARAMETER_SEPARATOR);//30
sb.append(rs.getString("vatted_10_amount")).append(PARAMETER_SEPARATOR);//31
sb.append(rs.getString("exempt_amount")).append(PARAMETER_SEPARATOR);//32
sb.append(rs.getString("opt1")).append(PARAMETER_SEPARATOR);//33
sb.append(rs.getString("opt2")).append(PARAMETER_SEPARATOR);//34
sb.append(rs.getString("opt3")).append(PARAMETER_SEPARATOR);//35
sb.append(rs.getString("opt4")).append(PARAMETER_SEPARATOR);//36
sb.append(rs.getString("opt5")).append(PARAMETER_SEPARATOR);//37
sb.append(rs.getString("opt6")).append(PARAMETER_SEPARATOR);//38
sb.append(rs.getString("opt7")).append(PARAMETER_SEPARATOR);//39
sb.append(rs.getString("opt8")).append(PARAMETER_SEPARATOR);//40
sb.append(rs.getString("opt9")).append(PARAMETER_SEPARATOR);//41
sb.append(rs.getString("opt10")).append(PARAMETER_SEPARATOR);//42
b=1;
}
sb.append(BLOCK_SEPARATOR);
StringBuilder receiptValuesQuerysb = new StringBuilder();
receiptValuesQuerysb.append("select (p.payment_method_code || '.' || p.payment_method_name) as payment, p.value_amount, ");
receiptValuesQuerysb.append("(case when p.bank_code != '' then (p.bank_code || '.' || p.bank_description) else '-1' end) as bank, ");
receiptValuesQuerysb.append(" p.document_number as document_number, p.document_date, p.document_expiration_date, ");
receiptValuesQuerysb.append(" coalesce(p.document_drawer, '-1') document_drawer, p.payment_method_code ");
receiptValuesQuerysb.append(" from invoice_payment_detail p join invoice i on i.id=p.invoice_id where p.invoice_id = ?");
ps = connPortalBackend.prepareStatement(receiptValuesQuerysb.toString());
ps.setString(1, message.domainId);
rs = ps.executeQuery();
b= 0;
while (rs.next()) {
if (b == 1){
sb.append(BLOCK_SEPARATOR_PAYMENT);
}
sb.append(rs.getString("value_amount") == null ? "-1":rs.getString("value_amount") ).append(PARAMETER_SEPARATOR);//0
sb.append(rs.getString("payment")).append(PARAMETER_SEPARATOR);//1
sb.append(rs.getString("bank") == null ? "-1":rs.getString("bank") ).append(PARAMETER_SEPARATOR);//2
sb.append(rs.getString("document_number").length() == 0 ? "-1":rs.getString("document_number") ).append(PARAMETER_SEPARATOR);//3
sb.append(rs.getString("document_date") == null ? "-1":rs.getString("document_date") ).append(PARAMETER_SEPARATOR);//4
sb.append(rs.getString("document_expiration_date") == null ? "-1":rs.getString("document_expiration_date") ).append(PARAMETER_SEPARATOR);//5
sb.append(rs.getString("document_drawer").length() == 0 ? "-1":rs.getString("document_drawer") ).append(PARAMETER_SEPARATOR);//6
sb.append(rs.getString("payment_method_code").length() == 0 ? "-1":rs.getString("payment_method_code") ).append(PARAMETER_SEPARATOR);//7
b = 1;
}
log.info("Mensaje a procesar: "+sb.toString());
return sb.toString().replaceAll("'", " ");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment