Skip to content

Instantly share code, notes, and snippets.

@cesarockstar1985

cesarockstar1985/gist:28b3d035ac1eb04796619c0071e34714

Created November 4, 2024 16:51
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save cesarockstar1985/28b3d035ac1eb04796619c0071e34714 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
//package cast.sn.superindustrial;
import grails.converters.JSON;
import grails.plugin.mail.MailService;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.Socket;
import java.net.URL;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.security.SecureRandom;
import java.security.cert.X509Certificate;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.net.ssl.*;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.codehaus.groovy.grails.web.context.ServletContextHolder;
import org.codehaus.groovy.grails.web.json.JSONArray;
import org.codehaus.groovy.grails.web.json.JSONObject;
import org.codehaus.groovy.grails.web.servlet.GrailsApplicationAttributes;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.datasource.DataSourceUtils;
import cast.portal.config.srv.ConfigurationService;
import cast.portal.dto.ExceptionDTO;
import cast.portal.exception.srv.ExceptionManagementService;
import cast.portal.security.ent.User;
import cast.portal.util.DataBaseHelper;
import cast.portal.util.ExceptionHolder;
import cast.portal.util.GeoHelper;
import java.sql.SQLDataException;
/**
* .CAST_SC
*/
String smsText = commandData.get("sms.text");
String[] smsTextSplited = smsText.split("¤");
String subjectCode = smsTextSplited[1];
log.info("##################################################"+subjectCode+"##################################################");
System.exit(0);
SubjectSynchronizer si = new SubjectSynchronizer(ctx, log, commandData, user);
return si.getSubject(subjectCode, user.id);
public class SubjectSynchronizer {
ApplicationContext appCtx = null;
Logger log = null;
Map<String, String> commandData;
User user = null;
ApplicationContext applicationContext;
/************* Variables para conexion a WS *************/
private static final String BASE_PRODUCTIVA ="DB_LCCAMIONERO_PROD";
private static final String BASE_PRUEBA ="DB_LCCAMIONERO_MODEL";
private static final String USERNAME ="cast";
private static final String PASSWORD ="1234";
// el hostname superindustrial.prod.cast.com.py actualmente apunta a la IP 190.52.225.134:50000, si se modifica favor documentar en notas de Osticket y notificar al Sr Diego Bendlin para modificar en el DNS de cast
private static final String url ="https://superindustrial.prod.cast.com.py/b1s/v1/";
private final String USER_AGENT = "Mozilla/5.0";
private HttpURLConnection con;
public SubjectSynchronizer(ApplicationContext appCtx, Logger log, Map<String, String> commandData, User user){
this.log = log;
this.commandData = commandData;
this.user = user;
this.appCtx = appCtx;
}
private ArrayList getSubject(Long userLoggedId) throws SQLException, ParseException{
ApplicationContext appCtx = (ApplicationContext) ServletContextHolder.getServletContext().getAttribute(GrailsApplicationAttributes.APPLICATION_CONTEXT);
ArrayList rowsQty = new ArrayList();
long added = 0;
long updated = 0;
int prospectToClient = 0;
boolean errorsGetSubjects = fale;
Connection connectionTo = null;
Connection conn = null;
ResultSet externalRs = null;
ResultSet subjectDataResultSet = null;
ResultSet saleZoneIdResultSet = null;
// ResultSet subjectDataSelectRs = null;
ResultSet photoQueryRs = null;
ResultSet prospectAttributeValueRs = null;
ResultSet subjectAttributeValueRs = null;
ResultSet subjectEventQueryRs = null;
ResultSet groupsRs = null;
PreparedStatement ps1 = null;
PreparedStatement batchSubjectInsertPreparedStatement = null;
PreparedStatement subjectUpdatePreparedStatement = null;
PreparedStatement subjectDataSelectPreparedStatement = null;
PreparedStatement allSubjectSelectPreparedStatement = null;
PreparedStatement saleZoneIdPreparedStatement = null;
PreparedStatement disabledSubjectPreparedStatement = null;
PreparedStatement subjectNoChangeUpdatePreparedStatement = null;
PreparedStatement photoQueryPreparedStatement = null;
PreparedStatement subjectAttributeValuePs = null;
PreparedStatement subjectAttributeValueInsertPs = null;
PreparedStatement subjectEventUpdatePs = null;
PreparedStatement groupsPs = null;
int currentBatchCount = 0;
ArrayList codeProspectInvalidList = new ArrayList();
ArrayList<String> stackTraceList= new ArrayList<String>();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm");
SimpleDateFormat simpleDateF = new SimpleDateFormat("yyyy-MM-dd HH:mm");
Date today = new Date();
String formattedDateU= simpleDateF.format(today);
String formattedDate= simpleDateFormat.format(today);
Date date = simpleDateFormat.parse(formattedDate);
stackTraceList.add("La importación de clientes para la fecha "+formattedDate+" no pudo ser realizada <br>");
DataSource dataSource = null;
DataSource dataSourcePortal = null;
ArrayList<HashMap> exceptionMapList = new ArrayList<HashMap>();
try{
log.info("==============================GET SUBJECT START======================");
ApplicationContext applicationContext = (ApplicationContext)ServletContextHolder.getServletContext().getAttribute(GrailsApplicationAttributes.APPLICATION_CONTEXT);
ConfigurationService configurationService;
configurationService = (ConfigurationService) applicationContext.getBean("configurationService");
String configuration = configurationService.getConfiguration("subjectsConfiguration.getSubject");
int pipeIndex = configuration.indexOf('|');
String subjectDataSource = configuration.substring(0,pipeIndex);
String subjectSql = configuration.substring(pipeIndex+1);
dataSource = (DataSource)applicationContext.getBean(subjectDataSource);
String externalFacilityConfig = configurationService.getConfiguration("external.facility");
ArrayList subjectFacilityInsertArrayDto = new ArrayList();
ArrayList subjectFacilityUpdateArrayDto = new ArrayList();
if (dataSource == null){
throw new Exception ("No bean named $subjectDataSource exists in applicationContext!");
}
conn = DataSourceUtils.getConnection(dataSource);
ps1 = conn.prepareStatement(subjectSql);
/*conn = DataSourceUtils.getConnection(dataSource);*/
boolean executeBatchFlag = true; //Bandera que indica si se ejecuta en lotes o unitariamente
dataSourcePortal = (DataSource)applicationContext.getBean("dataSource");
if (dataSourcePortal == null){
throw new Exception ("No bean named dataSource exists in applicationContext!");
}
connectionTo = DataSourceUtils.getConnection(dataSourcePortal);
connectionTo.setAutoCommit(false);
String groupSelect = "SELECT id, code, latitude, longitude FROM groups WHERE is_facility IS TRUE";
groupsPs = connectionTo.prepareStatement(groupSelect);
groupsRs = groupsPs.executeQuery();
GroupsDto groupsDto;
HashMap<String, GroupsDto> facilityGroupsDtoMap = new HashMap<String, GroupsDto>();
while (groupsRs.next()){
groupsDto = new GroupsDto();
groupsDto.id = groupsRs.getLong("id");
groupsDto.code = groupsRs.getString("code");
groupsDto.latitude = groupsRs.getDouble("latitude");
groupsDto.longitude = groupsRs.getDouble("longitude");
facilityGroupsDtoMap.put(groupsDto.code, groupsDto);
}
//def connectionTo = DriverManager.getConnection("jdbc:postgresql://localhost:5432/castPortalNG-mcruz", "castulo", "tulocas2007");
StringBuilder insertIntoSubject = new StringBuilder();
insertIntoSubject.append("INSERT INTO subject(id, version, code, subject_name, address, contact_name, telephone, latitude, longitude, ");
insertIntoSubject.append("sale_zone_id, is_active, is_prospect, update_date, localization_is_updatable, fiscal_number, company_alias, ");
insertIntoSubject.append("facility, internal_code, city, facility_group_id, facility_group_distance) ");
insertIntoSubject.append("VALUES (nextVal('subject_seq'),'0',?,?,?,?,?,?,?,?,?,?,'"+formattedDateU+"',?,?,?,?,?,?,?,?) ");
StringBuilder updateSubject = new StringBuilder();
updateSubject.append("UPDATE subject set code = ?, subject_name = ?, address = ?, contact_name = ?, telephone = ?, latitude = ?, ");
updateSubject.append("longitude = ? ,sale_zone_id = ?, is_active = ?, is_prospect = ?, update_date = '"+formattedDateU+"', ");
updateSubject.append("fiscal_number = ?,company_alias= ?, temporary_code = ?, ");
updateSubject.append("subject_photo = (CASE WHEN subject_photo IS NOT NULL THEN subject_photo ELSE ? END), city = ?, ");
updateSubject.append("internal_code = ?, facility = ?, facility_group_id = ?, facility_group_distance = ? WHERE code = ? ");
String updateSubjectNoChange = "UPDATE subject set update_date = '"+formattedDateU+"' WHERE code = ?";
StringBuilder subjectDataSelect = new StringBuilder();
subjectDataSelect.append("SELECT s.code, s.subject_name, s.address, s.contact_name, s.telephone, s.latitude, s.longitude, s.is_active, ");
subjectDataSelect.append("s.is_prospect, s.fiscal_number, s.company_alias, s.sale_zone_id, s.id, s.temporary_code, s.city, ");
subjectDataSelect.append("s.internal_code, s.facility, s.facility_group_id, s.facility_group_distance, g.code as facility_group_code ");
subjectDataSelect.append("FROM subject s LEFT JOIN groups g ON g.id = s.facility_group_id");
String saleZoneIdQuery = "SELECT id FROM sale_zone WHERE code = ? ";
String photoQuery = "SELECT subject_photo FROM subject WHERE code = ?";
photoQueryPreparedStatement = connectionTo.prepareStatement(photoQuery);
subjectNoChangeUpdatePreparedStatement = connectionTo.prepareStatement(updateSubjectNoChange);
batchSubjectInsertPreparedStatement = connectionTo.prepareStatement(insertIntoSubject.toString());
subjectUpdatePreparedStatement = connectionTo.prepareStatement(updateSubject.toString());
saleZoneIdPreparedStatement = connectionTo.prepareStatement(saleZoneIdQuery);
// allSubjectSelectPreparedStatement = connectionTo.prepareStatement(allSubjectSelectSb.toString());
// subjectDataSelectRs = allSubjectSelectPreparedStatement.executeQuery();
ps1.setString(1,code);
externalRs = ps1.executeQuery();
ResultSetMetaData rsmd = externalRs.getMetaData();
int columnCount =rsmd.getColumnCount();
StringBuilder subjectAttributeValueSb = new StringBuilder();
subjectAttributeValueSb.append("SELECT s.id, sav.id as sav_subject_attribute_value_id, av.id as av_attribute_value_id, av.attribute_id as av_attribute_id ");
subjectAttributeValueSb.append("FROM subject s JOIN subject_attribute_value sav ON ");
subjectAttributeValueSb.append("sav.subject_id = s.id JOIN attribute_value av ON av.id = sav.attribute_value_id JOIN ");
subjectAttributeValueSb.append("attribute a ON a.id = av.attribute_id WHERE s.id = ? ");
subjectAttributeValuePs = connectionTo.prepareStatement(subjectAttributeValueSb.toString());
StringBuilder subjectAttributeValueInsertSb = new StringBuilder();
subjectAttributeValueInsertSb.append("INSERT INTO subject_attribute_value (id, version, attribute_value_id, subject_id) ");
subjectAttributeValueInsertSb.append("VALUES(nextVal('subject_attribute_value_seq'), 0, ?, ?)");
subjectAttributeValueInsertPs = connectionTo.prepareStatement(subjectAttributeValueInsertSb.toString());
String eventUpdate = "UPDATE event SET subject_id = ? WHERE subject_id = ?";
subjectEventUpdatePs = connectionTo.prepareStatement(eventUpdate);
SubjectDTO subjectDTO;
HashMap<String, SubjectDTO> subjectDtoMap = new HashMap<String, SubjectDTO>();
while (externalRs.next()){
subjectDTO = new SubjectDTO();
subjectDTO.code = externalRs.getString("code");
subjectDTO.internalCode = externalRs.getString("internal_code");
subjectDTO.facility = externalRs.getInt("facility");
subjectDTO.subjectName = externalRs.getString("subject_name");
subjectDTO.address = externalRs.getString("address");
subjectDTO.contactName = externalRs.getString("contact_name");
subjectDTO.telephone = externalRs.getString("telephone");
subjectDTO.latitude = externalRs.getDouble("latitude");
subjectDTO.longitude = externalRs.getDouble("longitude");
subjectDTO.isActive = externalRs.getBoolean("is_active");
subjectDTO.isProspect = externalRs.getBoolean("is_prospect");
subjectDTO.fiscalNumber = externalRs.getString("fiscal_number");
subjectDTO.companyAlias = externalRs.getString("company_alias");
subjectDTO.saleZoneId = externalRs.getLong("sale_zone_id");
subjectDTO.id = externalRs.getLong("id");
subjectDTO.temporaryCode = externalRs.getString("temporary_code");
subjectDTO.city = externalRs.getString("city");
subjectDTO.facilityGroupId = externalRs.getLong("facility_group_id");
subjectDTO.facilityGroupDistance = externalRs.getDouble("facility_group_distance");
subjectDTO.facilityGroupCode = externalRs.getString("facility_group_code");
subjectDtoMap.put(subjectDTO.code, subjectDTO);
}
/**Desde aca inician los metodos para
* conexion a WebService**/
String methodHttp ="";
String aditionalData ="";
StringBuilder sesionId = new StringBuilder();
StringBuilder subjects = new StringBuilder();
//Login
log.info("--------- Login ---------");
methodHttp= "POST";
sesionId = getWsData("Login", methodHttp, aditionalData, sesionId);
//Consultar Clientes
methodHttp= "GET";
aditionalData = "?\$select=CardCode,CardName,Address,Phone1,Phone2,ContactPerson,CreditLimit," +
"FederalTaxID,City,MailCity,CurrentAccountBalance,AliasName,Properties3,Properties4,Properties5," +
"Properties6,Properties7&%24filter=%20(startswith(CardCode,%20\'CLI\')" +
"%20and%20Frozen%20eq%20\'tNO\')&\$orderby=CardCode";
//TODO
// Descomentar en el portal
subjects = getWsData("BusinessPartners", methodHttp, aditionalData, sesionId);
JSONObject jsonResponse = new JSONObject(subjects.toString());
JSONArray jsonArraySubjects = jsonResponse.getJSONArray("value");
//Logout
log.info("--------- Logout ---------");
methodHttp= "POST";
getWsData("Logout", methodHttp, aditionalData, sesionId);
/******************************************************************************/
//externalRs = ps1.executeQuery(); //ejecuta query del external //TODO tengo que cambiar
//ResultSetMetaData rsmd = externalRs.getMetaData();
//int columnCount =rsmd.getColumnCount();
int columnCount =0;
String subjectCode = null;
String subjectInternalCode = null;
Integer subjectFacility = null;
String name = null;
String saleZone = null;
String address = null;
String contactName = null;
String telephone = null;
String fiscalNumber = null;
String companyAlias = null;
String city = null;
Double lat = 0.0;
Double lon = 0.0;
Boolean isActive = false;
Boolean isProspect = false;
String codeProspect = null;
String properties3 = "";
String properties4 = "";
String properties5 = "";
String properties6 = "";
String properties7 = "";
byte[] photo = null;
String codeFacility = null;
long facilityGroupId= 0;
HashMap<String, Object> subjectDataMap = new HashMap<String, Object>();
SubjectDTO subjectProspectDTO;
boolean facilityNotFoundFlag = false;
GroupsDto facilityGroupDto;
Double facilitySubjectKmDistance = null;
double facilitySubjectDistance = 0;
int resultSetRow = 0; //Acumulador que indica al resultset desde donde se debe iniciar en caso de que un lote haya fallado en su ejecucion
HashMap subjectErrorMap;
boolean continueExecuteFlag = true; //TODO cambiar a true despues
int restartRow = 0; //Acumulador que indica cada cuantos clientes el acumulador debe aumentarse para el reinicio del resultset
//Recorremos el listado de clientes obtenidos del WS
HashMap<String, String> subjectData;
while(continueExecuteFlag){
added = 0;
updated = 0;
for (int i=0; i<jsonArraySubjects.size(); i++){
subjectData = new HashMap<String, String>();
subjectData = (HashMap<String, String>) jsonArraySubjects.get(i);
//log.info("Subject: "+ jsonArraySubjects.get(i));
try {
connectionTo.setAutoCommit(false);
subjectCode = subjectData.get("CardCode");
name = subjectData.get("CardName");
address = subjectData.get("Address");
if(!subjectData.get("ContactPerson").equals(null)){
contactName = subjectData.get("ContactPerson");
}else{
contactName = "-";
}
if(!subjectData.get("Phone1").equals(null) && !subjectData.get("Phone2").equals(null)){
telephone = subjectData.get("Phone1")+" - "+subjectData.get("Phone2");
}else if(subjectData.get("Phone1").equals(null) && !subjectData.get("Phone2").equals(null)){
telephone = subjectData.get("Phone2");
}else if(!subjectData.get("Phone1").equals(null) && subjectData.get("Phone2").equals(null)){
telephone = subjectData.get("Phone1");
}
/*log.info("Phone1"+subjectData.get("Phone1"));
log.info("Phone2"+subjectData.get("Phone2"));
log.info("Telephone: "+telephone);*/
lat = 0.0;
lon = 0.0;
saleZone = null;
isActive = true;
isProspect = false;
codeProspect = null; //TODO hay que agregar el codigo de prospecto cuando el cliente agregue ese dato al WS
fiscalNumber = subjectData.get("FederalTaxID");
if(!subjectData.get("AliasName").equals(null)){
companyAlias = subjectData.get("AliasName");
}else{
companyAlias = "-";
}
if(subjectData.get("City") != null){
city = subjectData.get("City");
}else if(subjectData.get("MailCity") != null){
city = subjectData.get("MailCity");
}else{
city = "-";
}
properties3 = subjectData.get("Properties3");
properties4 = subjectData.get("Properties4");
properties5 = subjectData.get("Properties5");
properties6 = subjectData.get("Properties6");
properties7 = subjectData.get("Properties7");
subjectInternalCode = subjectData.get("CardCode");
subjectFacility = 0;
switch ("tYES"){
case properties3:
codeFacility = "suc_1";
break;
case properties4:
codeFacility = "suc_2";
break;
case properties5:
codeFacility = "suc_3";
break;
case properties6:
codeFacility = "suc_4";
break;
case properties7:
codeFacility = "suc_5";
break;
}
boolean facilityGroupWithoutLocation = false;
facilityGroupId = 0;
facilitySubjectKmDistance = null;
// if (columnCount > 17 && externalRs.getString(18) != null && !externalRs.getString(18).trim
// ().equals("")){
//codeFacility = externalRs.getString(18).trim();
facilityGroupDto = facilityGroupsDtoMap.get(codeFacility);
if (facilityGroupDto != null) {
facilityGroupId = facilityGroupDto.id;
//Se valida que la latitud y la longitud sean mayor a cero para que se calcule la distancia
if(facilityGroupDto.latitude != 0 && facilityGroupDto.longitude != 0){
if(subjectDtoMap.get(subjectCode) != null && subjectDtoMap.get(subjectCode).latitude != 0.0 && subjectDtoMap.get(subjectCode).longitude != 0.0){
facilitySubjectKmDistance = getKmDistance(subjectCode, facilityGroupDto.code, facilityGroupDto.latitude, facilityGroupDto.longitude, subjectDtoMap.get(subjectCode).latitude, subjectDtoMap.get(subjectCode).longitude);
}else if(lat != 0D && lon != 0D){
facilitySubjectKmDistance = getKmDistance(subjectCode, facilityGroupDto.code, facilityGroupDto.latitude, facilityGroupDto.longitude, lat, lon);
}
}else if(subjectDtoMap.get(subjectCode) != null && (double)facilityGroupDto.latitude != (double)subjectDtoMap.get(subjectCode).latitude && (double)facilityGroupDto.longitude != (double)subjectDtoMap.get(subjectCode).longitude){
facilityGroupWithoutLocation = true;
}
}else{
log.info("El codigo de sucursal: "+codeFacility+" del cliente con codigo: "+subjectCode+" no existe como grupo de tipo sucursal");
}
// }else{
// facilityGroupId = 0;
// facilitySubjectKmDistance = null;
// }
//Consulta si existe el cliente en subject
// Busca si existe el codigo subjectCode en el mapa de registros del Portal
subjectDTO = (SubjectDTO) subjectDtoMap.get(subjectCode);
if(subjectDTO == null){ //Insert
//Consulta por codeProspect en subject si existe un prospecto creado
//antes de realizar la inserccion se verifica si existe un cliente en el external que en el portal sea prospecto
subjectProspectDTO = (SubjectDTO) subjectDtoMap.get(codeProspect);
if(subjectProspectDTO != null){ //actualiza todo menos ubicacion
subjectDataMap.put("subjectCode", subjectProspectDTO.code);
subjectDataMap.put("subjectName", subjectProspectDTO.subjectName);
subjectDataMap.put("subjectAddress", subjectProspectDTO.address);
subjectDataMap.put("subjectContactName", subjectProspectDTO.contactName);
subjectDataMap.put("subjectTelephone", subjectProspectDTO.telephone);
subjectDataMap.put("subjectLatitude", subjectProspectDTO.latitude);
subjectDataMap.put("subjectLongitude", subjectProspectDTO.longitude);
subjectDataMap.put("subjectIsActive", subjectProspectDTO.isActive);
subjectDataMap.put("subjectIsProspect", subjectProspectDTO.isProspect);
subjectDataMap.put("subjectFiscalNumber", subjectProspectDTO.fiscalNumber);
subjectDataMap.put("subjectCompanyAlias", subjectProspectDTO.companyAlias);
subjectDataMap.put("subjectSaleZoneId", subjectProspectDTO.saleZoneId);
subjectDataMap.put("temporaryCode", subjectProspectDTO.temporaryCode);
subjectDataMap.put("subjectCity", subjectProspectDTO.city);
subjectDataMap.put("facilityGroupId", subjectProspectDTO.facilityGroupId);
subjectDataMap.put("facilityGroupDistance", subjectProspectDTO.facilityGroupDistance);
subjectUpdate(subjectDataMap, subjectUpdatePreparedStatement, updated, subjectCode, name, address, contactName,
telephone, lat, lon, saleZone, isActive, isProspect, photo, codeProspect, fiscalNumber, companyAlias,
saleZoneIdPreparedStatement, saleZoneIdResultSet, codeProspect, city, subjectInternalCode, subjectFacility,
facilityGroupId, facilitySubjectKmDistance, facilityGroupWithoutLocation, executeBatchFlag);
updated = updated + 1;
prospectToClient++;
}else{
batchSubjectInsertPreparedStatement.setString(1,subjectCode);
batchSubjectInsertPreparedStatement.setString(2,name);
if(address != null){
batchSubjectInsertPreparedStatement.setString(3,address);
}else {
batchSubjectInsertPreparedStatement.setString(3,null);
}
if(contactName != null){
batchSubjectInsertPreparedStatement.setString(4,contactName);
}else {
batchSubjectInsertPreparedStatement.setString(4,null);
}
if(telephone != null){
batchSubjectInsertPreparedStatement.setString(5,telephone);
}else{
batchSubjectInsertPreparedStatement.setString(5,null);
}
//Traemos la latitud y nos aseguramos de que esté dentro del mapa de py
if (lat > -28.00 && lat < -20.00){
batchSubjectInsertPreparedStatement.setDouble(6,lat);
}else{
batchSubjectInsertPreparedStatement.setDouble(6,0.0);
}
//Traemos la longitud y nos aseguramos de que esté dentro del mapa
if(lon > -63.00 && lon < -55.00){
batchSubjectInsertPreparedStatement.setDouble(7,lon);
}else {
batchSubjectInsertPreparedStatement.setDouble(7,0.0);
}
if(saleZone != null){
saleZoneIdPreparedStatement.setString(1, saleZone);
saleZoneIdResultSet = saleZoneIdPreparedStatement.executeQuery();
//def saleZoneInstance = SaleZone.findByCode(saleZone)
if (saleZoneIdResultSet.next()){
batchSubjectInsertPreparedStatement.setLong(8, saleZoneIdResultSet.getInt(1));
}else {
batchSubjectInsertPreparedStatement.setNull(8,java.sql.Types.DOUBLE);
}
}else {
batchSubjectInsertPreparedStatement.setNull(8,java.sql.Types.DOUBLE);
}
batchSubjectInsertPreparedStatement.setBoolean(9,isActive);
batchSubjectInsertPreparedStatement.setBoolean(10,isProspect);
batchSubjectInsertPreparedStatement.setBoolean(11,true);
if(fiscalNumber != null){
batchSubjectInsertPreparedStatement.setString(12,fiscalNumber);
}else{
batchSubjectInsertPreparedStatement.setString(12,null);
}
if(companyAlias != null){
batchSubjectInsertPreparedStatement.setString(13,companyAlias);
}else{
batchSubjectInsertPreparedStatement.setString(13,null);
}
if(externalFacilityConfig.equals("0")){
subjectInternalCode = subjectCode;
subjectFacility = 0;
}else if(!externalFacilityConfig.equals("0") && (subjectInternalCode == null || subjectInternalCode.trim().equals(""))){
facilityNotFoundFlag = true;
log.info("SubjectImport: Para el cliente: "+subjectCode+" - "+name+" no se recibio su Codigo Matriz");
subjectInternalCode = subjectCode;
subjectFacility = 0;
}else if(externalFacilityConfig.equals("1")){
if(subjectInternalCode.equals(subjectCode)){
subjectFacility = 0;
}
}
if(externalFacilityConfig.equals("1") && !subjectCode.equals(subjectInternalCode)){
subjectFacility = getFacilityRandom();
SubjectFacilityDto subjectFacilityInsertDto = new SubjectFacilityDto(subjectCode, subjectInternalCode, "I", 0, 0, null);
subjectFacilityInsertArrayDto.add(subjectFacilityInsertDto);
}else if(subjectFacility == 0 && externalFacilityConfig.equals("2") && !subjectInternalCode.equals(subjectCode) ){
facilityNotFoundFlag = true;
log.info("SubjectImport: Para el cliente: "+subjectCode+" - "+name+" con la configuracion en 2, no se recibio el numero de sucursal");
subjectFacility = getFacilityRandom();
}
batchSubjectInsertPreparedStatement.setInt(14, subjectFacility);
batchSubjectInsertPreparedStatement.setString(15, subjectInternalCode);
batchSubjectInsertPreparedStatement.setString(16,city);
if(facilityGroupId > 0){
batchSubjectInsertPreparedStatement.setLong(17,facilityGroupId);
}else{
batchSubjectInsertPreparedStatement.setNull(17, java.sql.Types.BIGINT);
}
if (facilitySubjectKmDistance != null) {
batchSubjectInsertPreparedStatement.setDouble(18, facilitySubjectKmDistance);
}else{
batchSubjectInsertPreparedStatement.setNull(18, java.sql.Types.DOUBLE);
}
if(executeBatchFlag){
batchSubjectInsertPreparedStatement.addBatch();
}else{
batchSubjectInsertPreparedStatement.executeUpdate();
}
added = added + 1;
}
}else{ //UPDATE
subjectDataMap.put("subjectCode", subjectDTO.code);
subjectDataMap.put("subjectName", subjectDTO.subjectName);
subjectDataMap.put("subjectAddress", subjectDTO.address);
subjectDataMap.put("subjectContactName", subjectDTO.contactName);
subjectDataMap.put("subjectTelephone", subjectDTO.telephone);
subjectDataMap.put("subjectLatitude", subjectDTO.latitude);
subjectDataMap.put("subjectLongitude", subjectDTO.longitude);
subjectDataMap.put("subjectIsActive", subjectDTO.isActive);
subjectDataMap.put("subjectIsProspect", subjectDTO.isProspect);
subjectDataMap.put("subjectFiscalNumber", subjectDTO.fiscalNumber);
subjectDataMap.put("subjectCompanyAlias", subjectDTO.companyAlias);
subjectDataMap.put("subjectSaleZoneId",subjectDTO.saleZoneId);
subjectDataMap.put("subjectId", subjectDTO.id);
subjectDataMap.put("temporaryCode", subjectDTO.temporaryCode);
subjectDataMap.put("subjectCity", subjectDTO.city);
subjectDataMap.put("subjectInternalCode", subjectDTO.internalCode);
subjectDataMap.put("subjectFacility", subjectDTO.facility);
subjectDataMap.put("facilityGroupId", subjectDTO.facilityGroupId);
subjectDataMap.put("facilityGroupDistance", subjectDTO.facilityGroupDistance);
if(externalFacilityConfig.equals("0")){
subjectInternalCode = subjectDTO.internalCode;
subjectFacility = subjectDTO.facility;
}else if(!externalFacilityConfig.equals("0") && (subjectInternalCode == null || subjectInternalCode.trim().equals(""))){
facilityNotFoundFlag = true;
log.info("SubjectImport: Para el cliente: "+subjectCode+" - "+name+" no se recibio su Codigo Matriz");
subjectInternalCode = subjectDTO.code;
subjectFacility = 0;
}else if(externalFacilityConfig.equals("1")){
subjectFacility = subjectDTO.facility;
if(subjectInternalCode.equals(subjectCode)){
subjectFacility = 0;
}
}
if(externalFacilityConfig.equals("1") && !subjectDTO.internalCode.equals(subjectInternalCode)){
if(!subjectDTO.code.equals(subjectDTO.internalCode)){
SubjectFacilityDto subjectFacilityUpdateDto = new SubjectFacilityDto(null, subjectDTO.internalCode, "U", subjectDTO.facility, 0, null);
subjectFacilityUpdateArrayDto.add(subjectFacilityUpdateDto);
}
if(!subjectInternalCode.equals(subjectDTO.code)){
/** Hace el conteo para la nueva matriz si cambio de casa Matriz y su nueva matriz es diferente a su codigo */
SubjectFacilityDto subjectFacilityInsertDto = new SubjectFacilityDto(subjectDTO.code, subjectInternalCode, "I", 0, 0, null);
subjectFacilityInsertArrayDto.add(subjectFacilityInsertDto);
subjectFacility = getFacilityRandom();
}
}else if(subjectFacility == 0 && externalFacilityConfig.equals("2") && !subjectInternalCode.equals(subjectCode) ){
facilityNotFoundFlag = true;
log.info("SubjectImport: Para el cliente: "+subjectCode+" - "+name+" con la configuracion en 2, no se recibio el numero de sucursal");
subjectFacility = getFacilityRandom();
}else if(externalFacilityConfig.equals("2") && subjectDataMap.get("subjectFacility") != subjectFacility){
SubjectFacilityDto subjectFacilityUpdateDto = new SubjectFacilityDto((String)subjectDataMap.get("subjectCode"), subjectInternalCode, "UF", subjectFacility, (Integer)subjectDataMap.get("subjectFacility"), (String)subjectDataMap.get("subjectName"));
subjectFacilityUpdateArrayDto.add(subjectFacilityUpdateDto);
subjectFacility = getFacilityRandom();
}
boolean updateAfterProspectEvaluateFlag = true;
if(codeProspect != null && !codeProspect.equals("") && !subjectDataMap.get("temporaryCode").equals(codeProspect)){ //prospecto a
subjectProspectDTO = (SubjectDTO) subjectDtoMap.get(codeProspect);
if(subjectProspectDTO == null){
HashMap codeProspectInvalidMap = new HashMap();
codeProspectInvalidMap.put("subjectCode", subjectDTO.code);
codeProspectInvalidMap.put("subjectName", subjectDTO.subjectName);
codeProspectInvalidList.add(codeProspectInvalidMap);
}else{
//Aqui se ejecuta el query para traer la Foto del Prospecto y se guarda en un hashMap ya que el mismo no se levanta en memoria
photoQueryPreparedStatement.setString(1, subjectProspectDTO.code);
photoQueryRs = photoQueryPreparedStatement.executeQuery();
if (photoQueryRs.next()) {
subjectDataMap.put("subjectPhoto", photoQueryRs.getBytes("subject_photo"));
}
subjectUpdatePreparedStatement.setString(1, (String) subjectDataMap.get("subjectCode"));
subjectUpdatePreparedStatement.setString(2, (String) subjectDataMap.get("subjectName"));
subjectUpdatePreparedStatement.setString(3, (String) subjectDataMap.get("subjectAddress"));
subjectUpdatePreparedStatement.setString(4, (String) subjectDataMap.get("subjectContactName"));
subjectUpdatePreparedStatement.setString(5, (String) subjectDataMap.get("subjectTelephone"));
if((Double)subjectDataMap.get("subjectLatitude") == 0D && subjectProspectDTO.latitude != 0){
subjectUpdatePreparedStatement.setDouble(6, subjectProspectDTO.latitude);
}else{
subjectUpdatePreparedStatement.setDouble(6, (Double)subjectDataMap.get("subjectLatitude"));
}
if((Double)subjectDataMap.get("subjectLongitude") == 0D && subjectProspectDTO.longitude != 0){
subjectUpdatePreparedStatement.setDouble(7, subjectProspectDTO.longitude);
}else{
subjectUpdatePreparedStatement.setDouble(7, (Double)subjectDataMap.get("subjectLongitude"));
}
if(subjectDataMap.get("subjectSaleZoneId")!=null && (Double)subjectDataMap.get("subjectSaleZoneId") > 0){
subjectUpdatePreparedStatement.setLong(8, (Long)subjectDataMap.get("subjectSaleZoneId"));
}else{
subjectUpdatePreparedStatement.setNull(8, java.sql.Types.DOUBLE);
}
subjectUpdatePreparedStatement.setBoolean(9, (boolean) subjectDataMap.get("subjectIsActive"));
subjectUpdatePreparedStatement.setBoolean(10, (boolean) subjectDataMap.get("subjectIsProspect"));
subjectUpdatePreparedStatement.setString(11, (String) subjectDataMap.get("subjectFiscalNumber"));
subjectUpdatePreparedStatement.setString(12, (String) subjectDataMap.get("subjectCompanyAlias"));
subjectUpdatePreparedStatement.setString(13, codeProspect);
subjectUpdatePreparedStatement.setBytes(14, (byte[]) subjectDataMap.get("subjectPhoto")); //setea la Foto
subjectUpdatePreparedStatement.setString(15, city);
subjectUpdatePreparedStatement.setString(16, subjectInternalCode);
subjectUpdatePreparedStatement.setInt(17, subjectFacility);
facilityGroupDto = facilityGroupsDtoMap.get(subjectProspectDTO.facilityGroupCode);
if(facilityGroupId > 0 && facilitySubjectKmDistance != null){
subjectUpdatePreparedStatement.setLong(18, facilityGroupId);
subjectUpdatePreparedStatement.setDouble(19, facilitySubjectKmDistance);
}else if(facilityGroupDto != null){
boolean hasDistance = true;
if ((Long)subjectDataMap.get("facilityGroupId") > 0) {
subjectUpdatePreparedStatement.setLong(18, (Long) subjectDataMap.get("facilityGroupId"));
if(subjectDtoMap.get(subjectCode) != null && subjectDtoMap.get(subjectCode).latitude > 0 && subjectDtoMap.get(subjectCode).longitude > 0){
facilitySubjectKmDistance = getKmDistance(subjectCode, facilityGroupDto.code, facilityGroupDto.latitude, facilityGroupDto.longitude, subjectDtoMap.get(subjectCode).latitude, subjectDtoMap.get(subjectCode).longitude);
}else if(lat != 0 && lon != 0){
facilitySubjectKmDistance = getKmDistance(subjectCode, facilityGroupDto.code, facilityGroupDto.latitude, facilityGroupDto.longitude, lat, lon);
}
}else if (subjectProspectDTO.facilityGroupId > 0) {
subjectUpdatePreparedStatement.setLong(18, (Long) subjectProspectDTO.facilityGroupId);
if(subjectProspectDTO.latitude != 0 && subjectProspectDTO.longitude != 0){
facilitySubjectKmDistance = getKmDistance(subjectCode, facilityGroupDto.code, facilityGroupDto.latitude, facilityGroupDto.longitude, subjectProspectDTO.latitude, subjectProspectDTO.longitude);
}else if(lat != 0 && lon != 0){
facilitySubjectKmDistance = getKmDistance(subjectCode, facilityGroupDto.code, facilityGroupDto.latitude, facilityGroupDto.longitude, lat, lon);
}
}else{
subjectUpdatePreparedStatement.setNull(18, java.sql.Types.BIGINT);
hasDistance = false;
}
if(facilitySubjectKmDistance != null && hasDistance){
subjectUpdatePreparedStatement.setDouble(19, facilitySubjectKmDistance);
}else{
subjectUpdatePreparedStatement.setNull(19, java.sql.Types.DOUBLE);
}
}else{
subjectUpdatePreparedStatement.setNull(18, java.sql.Types.BIGINT);
subjectUpdatePreparedStatement.setNull(19, java.sql.Types.DOUBLE);
}
subjectUpdatePreparedStatement.setString(20, subjectCode);
subjectUpdatePreparedStatement.executeUpdate();
rowsQty.add(subjectDataMap.get("subjectId"));
rowsQty.add(1);
//long prospectId = (long) subjectDataMap.get("subjectId");
long prospectId = subjectProspectDTO.id;
/**
* Como el Evento tiene asociado ahora el Subject,
* Se pasa todos los eventos del Prospecto a ser eliminado
* a sus subject Correspondiente
* Asi tambien se pasa todos los atributos del Prospecto al Cliente.
* **/
long subjectId = (Long)subjectDataMap.get("subjectId");
subjectEventUpdate(subjectEventUpdatePs, subjectId, prospectId);
subjectAttributeValuesUpdate(subjectAttributeValuePs, subjectAttributeValueInsertPs, subjectId, prospectId);
deleteProspect(connectionTo, prospectId);
updated = updated + 1;
updateAfterProspectEvaluateFlag = false;
}
}
if(updateAfterProspectEvaluateFlag){
Boolean updateFlag = false;
if(subjectDataMap.get("subjectCode") != subjectCode){
updateFlag = true;
}else if(subjectDataMap.get("subjectName") != name){
updateFlag = true;
}else if (subjectDataMap.get("subjectAddress") != address){
updateFlag = true;
}else if(subjectDataMap.get("subjectContactName") != contactName){
updateFlag = true;
}else if (subjectDataMap.get("subjectTelephone") != telephone){
updateFlag = true;
}else if((Double)subjectDataMap.get("subjectLatitude") == 0 && subjectDataMap.get("subjectLatitude") != lat && lat > -28.00 && lat < -20.00 ) {
updateFlag = true;
}else if ((Double)subjectDataMap.get("subjectLongitude") == 0 && subjectDataMap.get("subjectLongitude") != lon && lon > -63.00 && lon < -55.00){
updateFlag = true;
}else if (subjectDataMap.get("subjectIsActive") != isActive) {
updateFlag = true;
}else if (subjectDataMap.get("subjectIsProspect") != isProspect){
updateFlag = true;
}else if (subjectDataMap.get("subjectFiscalNumber") != fiscalNumber){
updateFlag = true;
}else if (subjectDataMap.get("subjectCompanyAlias") != companyAlias){
updateFlag = true;
}else if(subjectDataMap.get("subjectCity") != city){
updateFlag = true;
}else if(!subjectDataMap.get("subjectInternalCode").equals(subjectInternalCode)){
updateFlag = true;
}else if(subjectDataMap.get("subjectFacility") != subjectFacility){
updateFlag = true;
}else if((long)subjectDataMap.get("facilityGroupId") != facilityGroupId){
updateFlag = true;
}else if(facilitySubjectKmDistance != null && subjectDataMap.get("facilityGroupDistance") != facilitySubjectKmDistance){
updateFlag = true;
}else if((double)subjectDataMap.get("facilityGroupDistance") != 0D && facilityGroupWithoutLocation){
updateFlag = true;
}
if(updateFlag){//Actualiza si cambió algo
subjectUpdate(subjectDataMap, subjectUpdatePreparedStatement, updated, subjectCode, name, address, contactName,
telephone, lat, lon, saleZone, isActive, isProspect, photo, subjectCode, fiscalNumber, companyAlias,
saleZoneIdPreparedStatement, saleZoneIdResultSet, codeProspect, city, subjectInternalCode,
subjectFacility, facilityGroupId, facilitySubjectKmDistance, facilityGroupWithoutLocation, executeBatchFlag);
updated = updated + 1;
}else {//Si nada cambió,setea con los valores originales
subjectNoChangeUpdatePreparedStatement.setString(1, subjectCode);
if(executeBatchFlag){
subjectNoChangeUpdatePreparedStatement.addBatch();
}else{
subjectNoChangeUpdatePreparedStatement.executeUpdate();
}
}
}
}
currentBatchCount++;
if(currentBatchCount == 500 && executeBatchFlag){
currentBatchCount = 0;
batchSubjectInsertPreparedStatement.executeBatch();
subjectUpdatePreparedStatement.executeBatch();
connectionTo.commit();
batchSubjectInsertPreparedStatement.clearBatch();
subjectUpdatePreparedStatement.clearBatch();
}
if(!executeBatchFlag){
connectionTo.commit();
}
} catch (Throwable th) {
if(executeBatchFlag){
log.debug("Error en la ejecucion del batch, se reinicia el ciclo desde el registro: "+resultSetRow+" para realizar la ejecución unitaria");
/*Si hay un error al procesar el batch se debe recorrer
* nuevamente los registros para procesarlos unitariamente con la funcion absolute del resultset
* desde la posicion en la cual quedo el lote de procesamiento que nos indica la variable resultSetRow
* Si la posicion del resultSetRow es 0 se ejecuta la funcion beforeFirst ya que funciona de la misma manera
* que el absolute(0) y es mas compatible con los distintos motores de base de datos como por ejemplo Mida*/
// if(resultSetRow == 0){
// externalRs.beforeFirst();
// }else{
// externalRs.absolute(resultSetRow);//Apunta el cursor a la fila seteada
// }
connectionTo.rollback();
batchSubjectInsertPreparedStatement.clearBatch();
subjectUpdatePreparedStatement.clearBatch();
executeBatchFlag = false;
}else{
/******************RESUMEN******************/
String stackTrace;
Writer result = new StringWriter();
PrintWriter printWriter = new PrintWriter(result);
th.printStackTrace(printWriter);
//stackTrace = result.toString();
stackTrace = th.getMessage();
subjectErrorMap = new HashMap();
subjectErrorMap.put("subjectCode",subjectCode);
subjectErrorMap.put("subjectName",name);
subjectErrorMap.put("stackTraceError",stackTrace);
exceptionMapList.add(subjectErrorMap);
/******************************************/
log.debug("Error en el proceso de ejecucion unitaria, El cliente con codigo "+subjectCode+" es agregado al correo de resumen de errores, y es descartado del proceso de importación");
log.error("Error: "+th.getMessage());
connectionTo.rollback();
}
}
restartRow++;
if(restartRow == 500){
resultSetRow = resultSetRow + 500;
restartRow = 0;
}
/*Se nulan las variables*/
subjectCode = null;
subjectInternalCode = null;
subjectFacility = null;
name = null;
saleZone = null;
address = null;
contactName = null;
telephone = null;
fiscalNumber = null;
companyAlias = null;
lat = null;
lon = null;
isActive = null;
isProspect = null;
codeProspect = null;
photo = null;
city = null;
subjectDataMap.clear();//Limpiamos map con datos de subject
facilityGroupId = 0;
codeFacility = null;
facilitySubjectKmDistance = null;
}
//TODO
try{
if(executeBatchFlag){
batchSubjectInsertPreparedStatement.executeBatch();
subjectUpdatePreparedStatement.executeBatch();
subjectNoChangeUpdatePreparedStatement.executeBatch();
connectionTo.commit();
batchSubjectInsertPreparedStatement.clearBatch();
subjectUpdatePreparedStatement.clearBatch();
subjectNoChangeUpdatePreparedStatement.clearBatch();
}else{
connectionTo.commit();
}
continueExecuteFlag = false;
}catch(Throwable th){
if(executeBatchFlag){//El fallo fue durante la ejecucion en lotes
log.debug("Error en la ejecucion del batch, se reinicia el ciclo desde el registro: "+resultSetRow+" para realizar la ejecución unitaria");
log.error("ERROR: "+th.getMessage());
/*Si hay un error al procesar el batch se debe recorrer
* nuevamente los registros para procesarlos unitariamente con la funcion absolute del resultset
* desde la posicion en la cual quedo el lote de procesamiento que nos indica la variable resultSetRow
* Si la posicion del resultSetRow es 0 se ejecuta la funcion beforeFirst ya que funciona de la misma manera
* que el absolute(0) y es mas compatible con los distintos motores de base de datos como por ejemplo Mida*/
if(resultSetRow == 0){
externalRs.beforeFirst();
}else{
externalRs.absolute(resultSetRow);//Apunta el cursor a la fila seteada
}
connectionTo.rollback();
batchSubjectInsertPreparedStatement.clearBatch();
subjectUpdatePreparedStatement.clearBatch();
executeBatchFlag = false;
}
}
}
log.info("Subjects import process finished. Created: $added, Updated: ${updated-prospectToClient}, Prospect To Client: $prospectToClient, Subjects with import errors: "+exceptionMapList.size());
//Sentencias sql para inactivar clientes
String subjectDisabledQuery = "Select count(*) from subject where (is_prospect='f' and is_active='t') and (update_date is null or update_date != ?)";
//PrepareStatement y Resultset de la consulta de id's de los clientes a inactivar
subjectDataSelectPreparedStatement = connectionTo.prepareStatement(subjectDisabledQuery);
Timestamp dateFormat = new Timestamp(date.getTime());
subjectDataSelectPreparedStatement.setTimestamp(1, dateFormat);
subjectDataResultSet = subjectDataSelectPreparedStatement.executeQuery();
//Acumulador para contar la cantidad de clientes a inactivar
int subjectDisabledCount = 0;
if(subjectDataResultSet.next()){
subjectDisabledCount = subjectDataResultSet.getInt(1);
}
log.info("Disabling subjects, total: "+subjectDisabledCount);
String disabledSubjectUpdate = "Update subject set is_active='f' where (is_prospect='f' and is_active='t') and (update_date is null or update_date != ?)";
//PrepareStatement del update para inactivar clientes
disabledSubjectPreparedStatement = connectionTo.prepareStatement(disabledSubjectUpdate);
disabledSubjectPreparedStatement.setTimestamp(1, dateFormat);
disabledSubjectPreparedStatement.executeUpdate();
if(subjectFacilityUpdateArrayDto.size() > 0){
recalculateFacilityNumberNew(connectionTo, subjectFacilityUpdateArrayDto, exceptionMapList);
}
if(subjectFacilityInsertArrayDto.size() > 0){
recalculateFacilityNumberNew(connectionTo, subjectFacilityInsertArrayDto, exceptionMapList);
}
if(facilityNotFoundFlag){
saveImporterInternalCodeException(connectionTo, "getAllSubject", externalFacilityConfig, userLoggedId);
}
validateSubjectEnterpriseFacility(connectionTo, null, null);
if(exceptionMapList.size() > 0){
errorsNotification(exceptionMapList, "Importación de clientes");
}
connectionTo.commit();
log.info("Finish disabling subjects");
}catch (Throwable e){
log.error("ERROR getAllSubjects: "+e.getMessage());
connectionTo.rollback();
log.debug("ERROR getAllSubjects: "+e.getMessage(), e);
String stackTrace;
Writer result = new StringWriter();
PrintWriter printWriter = new PrintWriter(result);
e.printStackTrace(printWriter);
stackTrace = result.toString();
stackTraceList.add(stackTrace);
}finally{
DataBaseHelper.closeRs(externalRs);
DataBaseHelper.closeRs(subjectDataResultSet);
DataBaseHelper.closeRs(saleZoneIdResultSet);
// DataBaseHelper.closeRs(externalRs);
DataBaseHelper.closeRs(photoQueryRs);
DataBaseHelper.closeRs(prospectAttributeValueRs);
DataBaseHelper.closeRs(subjectAttributeValueRs);
DataBaseHelper.closeRs(subjectEventQueryRs);
DataBaseHelper.closeRs(groupsRs);
DataBaseHelper.closePs(ps1);
DataBaseHelper.closePs(batchSubjectInsertPreparedStatement);
DataBaseHelper.closePs(subjectUpdatePreparedStatement);
DataBaseHelper.closePs(subjectDataSelectPreparedStatement);
DataBaseHelper.closePs(saleZoneIdPreparedStatement);
DataBaseHelper.closePs(disabledSubjectPreparedStatement);
DataBaseHelper.closePs(subjectNoChangeUpdatePreparedStatement);
DataBaseHelper.closePs(allSubjectSelectPreparedStatement);
DataBaseHelper.closePs(photoQueryPreparedStatement);
DataBaseHelper.closePs(subjectAttributeValuePs);
DataBaseHelper.closePs(subjectAttributeValueInsertPs);
DataBaseHelper.closePs(subjectEventUpdatePs);
DataBaseHelper.closePs(groupsPs);
DataBaseHelper.releaseConnection(connectionTo, dataSourcePortal);
DataBaseHelper.releaseConnection(conn, dataSource);
}
if (stackTraceList.size()>1){
mailNotification(stackTraceList, "Importación de clientes");
}
rowsQty.add(updated);
rowsQty.add(added);
rowsQty.add(errorsGetSubject);
rowsQty.add(codeProspectInvalidList);
return rowsQty;
}
// Metodo para consulta a web service, se usa para Login al WS y luego para obtener los datos*
public StringBuilder getWsData(String paramPath, String methodHttp, String aditionalData, StringBuilder sesionId)
throws Exception {
log.info(" INGRESA METODO getWsData ");
log.info("========== " + paramPath + " ===========");
String url_variable = url + paramPath + aditionalData;
log.info("URL: " + url_variable);
URL loginUrl = new URL(url_variable);
con = (HttpURLConnection) loginUrl.openConnection();
String CookieString="B1SESSION="+ sesionId.toString();
con.setRequestMethod(methodHttp);
con.setRequestProperty("User-Agent", USER_AGENT);
con.setRequestProperty("Accept-Language", "en-US,en;q=0.5");
con.setRequestProperty("Content-Type", "application/json");
//Se agrega la propiedad Cookie en caso que el parametro sea BusinessPartners y Logout
if(paramPath !="Login"){
con.setRequestProperty("Cookie", CookieString);
con.setRequestProperty("Prefer", "odata.maxpagesize=0");
}
//Entra en este if si el parametro es Login o Logout
if(paramPath != "BusinessPartners"){
Map autenticationMap = new HashMap();
Map postMap = new HashMap();
JSONObject jsonAuntenticationObject = new JSONObject();
if(paramPath=="Login"){
autenticationMap.put("CompanyDB", BASE_PRODUCTIVA);
autenticationMap.put("UserName", USERNAME);
autenticationMap.put("Password", PASSWORD);
jsonAuntenticationObject.putAll(autenticationMap);
}
String postData = "";
postData = (postMap as JSON).toString(); //TODO Descomentar en el portal
postData = jsonAuntenticationObject.toString();
log.info("Sending 'POST' request to URL : " + url_variable);
log.info("Post parameters : " + postData);
// Send post request
con.setDoOutput(true);
log.info("Send post request " );
DataOutputStream wr = new DataOutputStream(con.getOutputStream());
log.info("DataOutputStream " );
wr.writeBytes(postData);
wr.flush();
wr.close();
}
String responseMessage = con.getResponseMessage();
int responseCode = con.getResponseCode();
log.info("Response Message : " + responseMessage);
log.info("Response Code : " + responseCode);
BufferedReader buffered = new BufferedReader(new InputStreamReader(con.getInputStream()));
String inputLine;
StringBuffer response = new StringBuffer();
while ((inputLine = buffered.readLine()) != null) {
response.append(inputLine);
}
buffered.close();
StringBuilder respuesta = new StringBuilder(response.toString());
//log.info("Respuesta:" + respuesta.toString());
StringBuilder returnData = new StringBuilder(respuesta);
if(paramPath == "Login"){
Map<String,String> respuestaLoginMap = (Map<String,String>) JSON.parse(respuesta.toString());
log.info("respuestaLoginMap: " +respuestaLoginMap);
returnData = new StringBuilder(respuestaLoginMap.get("SessionId"));
log.info(" sid: "+returnData.toString());
}
log.info(" FIN METODO getWsData ");
return returnData;
}
public Double getKmDistance(String subjectCode, String facilityGoupCode, double facilityLatitude, double facilityLongitude, double latitude, double longitude){
//TODO
Double facilitySubjectKmDistance = null;
double facilitySubjectDistance = 0;
if(latitude != 0D && longitude != 0D){
facilitySubjectDistance = GeoHelper.metersBetweenTwoPoints(facilityLatitude, facilityLongitude, latitude, longitude);
}
if(facilitySubjectDistance > 0){
facilitySubjectKmDistance = facilitySubjectDistance / 1000;
}else if(facilitySubjectDistance == 0 && facilityLatitude == latitude && facilityLongitude == longitude && latitude != 0D && longitude != 0D){
facilitySubjectKmDistance = facilitySubjectDistance / 1000;
}
return facilitySubjectKmDistance;
}
public void updateProspectDocument(String codeProspect,String codeSubject,Connection connection) throws Throwable{
PreparedStatement documentQueueUpdatePs = null;
PreparedStatement invoiceUpdatePs = null;
PreparedStatement orderUpdatePs = null;
StringBuilder invoiceUpdateSb = new StringBuilder();
invoiceUpdateSb.append("UPDATE invoice SET customer_code = '$codeSubject' WHERE customer_code = '$codeProspect'");
StringBuilder orderUpdateSb = new StringBuilder();
orderUpdateSb.append("UPDATE orders SET customer_code = '$codeSubject' WHERE customer_code = '$codeProspect'");
StringBuilder documentQueueUpdateSb = new StringBuilder();
documentQueueUpdateSb.append("UPDATE document_queue SET subject_code = '$codeSubject', status='pending' WHERE subject_code = '$codeProspect'");
try{
invoiceUpdatePs = connection.prepareStatement(invoiceUpdateSb.toString());
orderUpdatePs = connection.prepareStatement(orderUpdateSb.toString());
documentQueueUpdatePs = connection.prepareStatement(documentQueueUpdateSb.toString());
invoiceUpdatePs.executeUpdate();
orderUpdatePs.executeUpdate();
documentQueueUpdatePs.executeUpdate();
}catch(Throwable th){
throw th;
}finally{
try{
invoiceUpdatePs.close();
}catch(Throwable th){}
try{
orderUpdatePs.close();
}catch(Throwable th){}
try{
documentQueueUpdatePs.close();
}catch(Throwable th){}
}
}
private int getFacilityRandom(){
int min = 10000;
int max = 99999999;
int range = (max - min) + 1;
int valueToReturn = (int)(Math.random() * range) + min;
return -valueToReturn;
}
class SubjectFacilityDto {
String code;
String internalCode;
String operation;
int facilityNumber;
int oldSubjectFacilityNumber;
String subjectName;
public SubjectFacilityDto(String code, String internalCode, String operation, int facilityNumber, int oldSubjectFacilityNumber, String subjectName){
this.code = code;
this.internalCode = internalCode;
this.operation = operation;
this.facilityNumber = facilityNumber;
this.oldSubjectFacilityNumber = oldSubjectFacilityNumber;
this.subjectName = subjectName;
}
}
private void subjectUpdate(Map subjectDataMap, PreparedStatement subjectUpdatePreparedStatement, Long updated, String subjectCode, String name, String address, String contactName,
String telephone, Double lat, Double lon, String saleZone, Boolean isActive, Boolean isProspect, byte[] photo,
String subjectCodeCondition, String fiscalNumber, String companyAlias, PreparedStatement saleZoneIdPreparedStatement,
ResultSet saleZoneIdResultSet, String prospectCode, String city, String subjectInternalCode, Integer subjectFacility,
long facilityGroupId, Double facilitySubjectKmDistance, boolean facilityLatLon, boolean executeBatchFlag) throws Exception{
if (subjectCode != null && !subjectCode.equals("") && subjectDataMap.get("subjectCode") != subjectCode){
subjectUpdatePreparedStatement.setString(1,subjectCode);
}else {
subjectUpdatePreparedStatement.setString(1, (String)subjectDataMap.get("subjectCode"));
}
if (name != null && !name.equals("") && subjectDataMap.get("subjectName") != name){
subjectUpdatePreparedStatement.setString(2,name);
}else {
subjectUpdatePreparedStatement.setString(2, (String)subjectDataMap.get("subjectName"));
}
if (address != null && !address.equals("") && subjectDataMap.get("subjectAddress") != address){
subjectUpdatePreparedStatement.setString(3,address);
}else {
subjectUpdatePreparedStatement.setString(3, (String)subjectDataMap.get("subjectAddress"));
}
if (contactName != null && !contactName.equals("") && subjectDataMap.get("subjectContactName") != contactName){
subjectUpdatePreparedStatement.setString(4,contactName);
}else {
subjectUpdatePreparedStatement.setString(4, (String)subjectDataMap.get("subjectContactName"));
}
if (telephone != null && !telephone.equals("") && subjectDataMap.get("subjectTelephone") != telephone){
subjectUpdatePreparedStatement.setString(5, telephone);
}else {
subjectUpdatePreparedStatement.setString(5, (String)subjectDataMap.get("subjectTelephone"));
}
if (lat > -28.00 && lat < -20.00 && (double)subjectDataMap.get("subjectLatitude") == 0){
subjectUpdatePreparedStatement.setDouble(6,lat);
}else{
subjectUpdatePreparedStatement.setDouble(6, (double)subjectDataMap.get("subjectLatitude"));
}
if(lon > -63.00 && lon < -55.00 && (double)subjectDataMap.get("subjectLongitude") == 0){
subjectUpdatePreparedStatement.setDouble(7, lon);
}else {
subjectUpdatePreparedStatement.setDouble(7, (double)subjectDataMap.get("subjectLongitude"));
}
if(saleZone != null){
saleZoneIdPreparedStatement.setString(1, saleZone);
saleZoneIdResultSet = saleZoneIdPreparedStatement.executeQuery();
if(saleZoneIdResultSet.next()){
subjectUpdatePreparedStatement.setLong(8, saleZoneIdResultSet.getInt(1));
}else{
subjectUpdatePreparedStatement.setNull(8,java.sql.Types.DOUBLE);
}
}else {
subjectUpdatePreparedStatement.setNull(8,java.sql.Types.DOUBLE);
}
if (isActive != null && subjectDataMap.get("subjectIsActive") != isActive){
subjectUpdatePreparedStatement.setBoolean(9, isActive);
}else{
subjectUpdatePreparedStatement.setBoolean(9, (boolean)subjectDataMap.get("subjectIsActive"));
}
if (isProspect != null && subjectDataMap.get("subjectIsProspect") != isProspect){
subjectUpdatePreparedStatement.setBoolean(10,isProspect);
}else {
subjectUpdatePreparedStatement.setBoolean(10, (boolean)subjectDataMap.get("subjectIsProspect"));
}
if (fiscalNumber != null && !fiscalNumber.equals("") && subjectDataMap.get("subjectFiscalNumber") != fiscalNumber){
subjectUpdatePreparedStatement.setString(11, fiscalNumber);
}else {
subjectUpdatePreparedStatement.setString(11, (String)subjectDataMap.get("subjectFiscalNumber"));
}
if (companyAlias != null && !companyAlias.equals("") && subjectDataMap.get("subjectCompanyAlias") != companyAlias){
subjectUpdatePreparedStatement.setString(12, companyAlias);
}else {
subjectUpdatePreparedStatement.setString(12, (String)subjectDataMap.get("subjectCompanyAlias"));
}
//agregar temporaryCode, a este metodo tambien se le invoca desde getAllSubjects.. entonces
//cargarle al map como temporaryCode null, antes de invocar al método
if (prospectCode != null && !prospectCode.equals("") && subjectDataMap.get("temporaryCode") != prospectCode){
subjectUpdatePreparedStatement.setString(13, prospectCode);
}else {
subjectUpdatePreparedStatement.setString(13, (String)subjectDataMap.get("temporaryCode"));
}
subjectUpdatePreparedStatement.setBytes(14, (byte[])subjectDataMap.get("subjectPhoto"));
if (city != null && !city.equals("") && subjectDataMap.get("subjectCity") != city){
subjectUpdatePreparedStatement.setString(15,city);
}else {
subjectUpdatePreparedStatement.setString(15, (String)subjectDataMap.get("subjectCity"));
}
if (subjectInternalCode != null && !subjectInternalCode.equals("") && !subjectDataMap.get("subjectInternalCode").equals(subjectInternalCode)){
subjectUpdatePreparedStatement.setString(16,subjectInternalCode);
}else {
subjectUpdatePreparedStatement.setString(16, (String)subjectDataMap.get("subjectInternalCode"));
}
if (subjectFacility != null && subjectDataMap.get("subjectFacility") != subjectFacility){
subjectUpdatePreparedStatement.setInt(17,subjectFacility);
}else{
subjectUpdatePreparedStatement.setInt(17, (int)subjectDataMap.get("subjectFacility"));
}
if (facilityGroupId > 0) {
subjectUpdatePreparedStatement.setLong(18, facilityGroupId);
//Se calcula la distancia entre el cliente y la sucursal asignada y se setea al registro Subjects
if(!facilityLatLon){
if(facilitySubjectKmDistance != null && subjectDataMap.get("facilityGroupDistance") != facilitySubjectKmDistance){
subjectUpdatePreparedStatement.setDouble(19, facilitySubjectKmDistance);
}else{
subjectUpdatePreparedStatement.setDouble(19, (Double)subjectDataMap.get("facilityGroupDistance"));
}
}else{
subjectUpdatePreparedStatement.setNull(19, java.sql.Types.DOUBLE);
}
}else{
subjectUpdatePreparedStatement.setNull(18,java.sql.Types.BIGINT);
subjectUpdatePreparedStatement.setNull(19, java.sql.Types.DOUBLE);
}
subjectUpdatePreparedStatement.setString(20,subjectCodeCondition);
if(!executeBatchFlag){
subjectUpdatePreparedStatement.executeUpdate();
}else{
subjectUpdatePreparedStatement.addBatch();
}
}
private void recalculateFacilityNumber(Connection connection, ArrayList<SubjectFacilityDto> subjectFacilityArrayDto) throws Exception{
PreparedStatement reorderPs = null;
PreparedStatement countPs = null;
PreparedStatement subjectUdatePs = null;
PreparedStatement queryCountPs = null;
PreparedStatement facilitySelectQueryPs = null;
ResultSet facilitySelectQueryRs = null;
try{
countPs = connection.prepareStatement("UPDATE subject set facility = (SELECT count(s1.*) + 1 FROM subject s1 WHERE s1.internal_code = ? AND facility > 0) WHERE code = ?");
reorderPs = connection.prepareStatement("UPDATE subject set facility = (facility -1) WHERE id = ?");
subjectUdatePs = connection.prepareStatement("UPDATE subject set facility = ? WHERE code = ?");
facilitySelectQueryPs = connection.prepareStatement("SELECT id FROM subject WHERE internal_code = ? AND facility > ? ORDER BY facility asc");
for (SubjectFacilityDto subjectFacilityDto : subjectFacilityArrayDto) {
if(subjectFacilityDto.operation.equalsIgnoreCase("I")){
countPs.setString(1, subjectFacilityDto.internalCode);
countPs.setString(2, subjectFacilityDto.code);
countPs.executeUpdate();
}else if(subjectFacilityDto.operation.equalsIgnoreCase("U")){
//Setea a la Consulta que nos trae el id de los clientes a renumerar(se resta 1)
facilitySelectQueryPs.setString(1, subjectFacilityDto.internalCode);
facilitySelectQueryPs.setInt(2, subjectFacilityDto.facilityNumber);
facilitySelectQueryRs = facilitySelectQueryPs.executeQuery();
long facilityId = 0;
while(facilitySelectQueryRs.next()){
facilityId = facilitySelectQueryRs.getLong("id");
reorderPs.setLong(1, facilityId);
reorderPs.executeUpdate();
}
}else if(subjectFacilityDto.operation.equalsIgnoreCase("UF")){
subjectUdatePs.setInt(1, subjectFacilityDto.facilityNumber);
subjectUdatePs.setString(2, subjectFacilityDto.code);
subjectUdatePs.executeUpdate();
}
}
}catch(Exception e){
throw e;
}finally{
DataBaseHelper.closeRs(facilitySelectQueryRs);
DataBaseHelper.closePs(reorderPs);
DataBaseHelper.closePs(countPs);
DataBaseHelper.closePs(facilitySelectQueryPs);
DataBaseHelper.closePs(queryCountPs);
DataBaseHelper.closePs(subjectUdatePs);
}
}
public void deleteProspect(Connection connection, long prospectId) throws Throwable{
PreparedStatement visitScheduleDeletePs = null;
PreparedStatement zoneSubjectDeletePs = null;
PreparedStatement subjectAttributeValueDeletePs = null;
PreparedStatement subjectProspectDeletePs = null;
PreparedStatement subjectProductSaleStatisticPs = null;
PreparedStatement summarySuggestedDetailPs = null;
PreparedStatement summarySuggestedPs = null;
PreparedStatement subjectLocationPs = null;
StringBuilder scheduleDeleteSb = new StringBuilder();
scheduleDeleteSb.append("DELETE FROM visit_schedule WHERE subject_id = ?");
StringBuilder zoneSubjectDeleteSb = new StringBuilder();
zoneSubjectDeleteSb.append("DELETE FROM zone_subject WHERE subject_id = ?");
StringBuilder subjectAttributeValueDeleteSb = new StringBuilder();
subjectAttributeValueDeleteSb.append("DELETE FROM subject_attribute_value WHERE subject_id = ?");
StringBuilder summarySuggestedDetail = new StringBuilder();
summarySuggestedDetail.append("DELETE FROM SUMMARY_SUGGESTED_DETAIL WHERE summary_suggested_id IN (SELECT id FROM SUMMARY_SUGGESTED WHERE subject_id = ?)");
StringBuilder summarySuggested = new StringBuilder();
summarySuggested.append("DELETE FROM SUMMARY_SUGGESTED WHERE subject_id = ?");
StringBuilder subjectProductSaleStatistic = new StringBuilder();
subjectProductSaleStatistic.append("DELETE FROM subject_product_sale_statistic WHERE subject_id = ?");
StringBuilder subjectLocationSb = new StringBuilder();
subjectLocationSb.append("DELETE FROM location WHERE subject_id = ? ");
StringBuilder prospectDeleteSb = new StringBuilder();
prospectDeleteSb.append("DELETE FROM subject WHERE id = ?");
try{
visitScheduleDeletePs = connection.prepareStatement(scheduleDeleteSb.toString());
zoneSubjectDeletePs = connection.prepareStatement(zoneSubjectDeleteSb.toString());
subjectAttributeValueDeletePs = connection.prepareStatement(subjectAttributeValueDeleteSb.toString());
summarySuggestedDetailPs = connection.prepareStatement(summarySuggestedDetail.toString());
summarySuggestedPs = connection.prepareStatement(summarySuggested.toString());
subjectProductSaleStatisticPs = connection.prepareStatement(subjectProductSaleStatistic.toString());
subjectLocationPs = connection.prepareStatement(subjectLocationSb.toString());
subjectProspectDeletePs = connection.prepareStatement(prospectDeleteSb.toString());
visitScheduleDeletePs.setLong(1, prospectId);
visitScheduleDeletePs.executeUpdate();
zoneSubjectDeletePs.setLong(1, prospectId);
zoneSubjectDeletePs.executeUpdate();
subjectAttributeValueDeletePs.setLong(1, prospectId);
subjectAttributeValueDeletePs.executeUpdate();
summarySuggestedDetailPs.setLong(1, prospectId);
summarySuggestedDetailPs.executeUpdate();
summarySuggestedPs.setLong(1, prospectId);
summarySuggestedPs.executeUpdate();
subjectProductSaleStatisticPs.setLong(1, prospectId);
subjectProductSaleStatisticPs.executeUpdate();
subjectLocationPs.setLong(1, prospectId);
subjectLocationPs.executeUpdate();
subjectProspectDeletePs.setLong(1, prospectId);
subjectProspectDeletePs.executeUpdate();
}catch(Throwable th){
throw th;
}finally{
DataBaseHelper.closePs(visitScheduleDeletePs);
DataBaseHelper.closePs(zoneSubjectDeletePs);
DataBaseHelper.closePs(subjectAttributeValueDeletePs);
DataBaseHelper.closePs(subjectProspectDeletePs);
DataBaseHelper.closePs(subjectProductSaleStatisticPs);
DataBaseHelper.closePs(summarySuggestedDetailPs);
DataBaseHelper.closePs(summarySuggestedPs);
DataBaseHelper.closePs(subjectLocationPs);
}
}
private void saveImporterInternalCodeException(Connection connection, String exceptionKey, String configurationKey, Long userLoggedId){
try{
String className = exceptionKey+" "+this.getClass().getName();
String identifierException = "4407";
exceptionKey = userLoggedId+"-"+className+"-"+identifierException;
ApplicationContext applicationContext = (ApplicationContext) ServletContextHolder.getServletContext().getAttribute(GrailsApplicationAttributes.APPLICATION_CONTEXT);
ExceptionManagementService exceptionManagementService = (ExceptionManagementService) applicationContext.getBean("exceptionManagementService");
String message = "Casa Matriz no configurada en la configuracion "+configurationKey;
ExceptionHolder exceptionHolderInstance = ExceptionHolder.getInstance();
ExceptionDTO exceptionDTOInstance = exceptionHolderInstance.getExceptionDto(exceptionKey);
if(exceptionDTOInstance != null){
exceptionDTOInstance.setCounter(exceptionDTOInstance.getCounter() + 1);
exceptionManagementService.updateExcepcionStatus(exceptionDTOInstance);
//ExceptionManagementService.updateExcepcionStatus(exceptionDTOInstance);
} else {
exceptionDTOInstance = new ExceptionDTO();
exceptionDTOInstance.setLoggedUserId(userLoggedId); //TODO cambiar esto, se debe Obtener el ID del usuario castAdmin
exceptionDTOInstance.setClassName(className);
exceptionDTOInstance.setIdentifierException("4407");
exceptionDTOInstance.setCounter(1);
exceptionDTOInstance.setCause("Subject Internal code not Import");
exceptionDTOInstance.setMessage(message);
exceptionHolderInstance.setExceptionDto(exceptionKey, exceptionDTOInstance);
exceptionManagementService.saveExcepcionStatus(exceptionDTOInstance, "",0);
}
}catch (Throwable th){
log.error("ERROR saveMNCommandException: "+th.getMessage());
log.debug("ERROR saveMNCommandException: "+th.getMessage(), th);
}
}
public void subjectAttributeValuesUpdate(PreparedStatement subjectAttributeValuePs, PreparedStatement subjectAttributeValueInsertPs,
long subjectId, long prospectId) throws SQLException{
ResultSet prospectRs = null;
ResultSet subjectRs = null;
try {
/**
* Se levanta todos los Atributos con
* sus valores asociados al Prospecto
* **/
subjectAttributeValuePs.setLong(1, prospectId);
prospectRs = subjectAttributeValuePs.executeQuery();
AttributeValueDTO pavDto;
HashMap<Long, AttributeValueDTO> pavDtoMap = new HashMap<Long, AttributeValueDTO>();
/** Traeme el id, attribute_id y attributevalue ID del prospecto*/
while (prospectRs.next()){
pavDto = new AttributeValueDTO();
pavDto.id = prospectRs.getLong("id");
pavDto.attributeId = prospectRs.getLong("av_attribute_id");
pavDto.attributeValueId = prospectRs.getLong("av_attribute_value_id");
//carga todo en el mapa para ir removiendo luego
pavDtoMap.put(pavDto.attributeId, pavDto);
}
//solo si el prospecto cuenta con atributos se levantan los clientes
if(pavDtoMap.size() > 0){
subjectAttributeValuePs.setLong(1, subjectId);
subjectRs = subjectAttributeValuePs.executeQuery();
AttributeValueDTO sav;
while (subjectRs.next()) {
sav = new AttributeValueDTO();
sav.attributeId = subjectRs.getLong("av_attribute_id");
sav.id = subjectRs.getLong("id");
sav.attributeValueId = subjectRs.getLong("av_attribute_value_id");
//se remueven las coincidencias para que no se inserten mas de dos valores de atributos para un atributo
pavDtoMap.remove(sav.attributeId);
}
for (AttributeValueDTO prospectToProcess : pavDtoMap.values()) {
//recorremos los atributos restantes y se setea e valor del atributo
subjectAttributeValueInsertPs.setLong(1, prospectToProcess.attributeValueId);
subjectAttributeValueInsertPs.setLong(2, subjectId);
subjectAttributeValueInsertPs.executeUpdate();
}
}
} catch (Throwable th) {
throw th;
}finally{
DataBaseHelper.closeRs(prospectRs);
DataBaseHelper.closeRs(subjectRs);
}
}
public void subjectEventUpdate(PreparedStatement eventUpdatePs,
long subjectId, long prospectId) throws SQLException{
eventUpdatePs.setLong(1, subjectId); //nuevo
eventUpdatePs.setLong(2, prospectId); //antiguo
eventUpdatePs.executeUpdate();
}
class AttributeValueDTO{
long id = 0;
long attributeId = 0;
long attributeValueId = 0;
String code = "";
}
class SubjectDTO {
Long id;
String code;
String internalCode;
Integer facility;
String subjectName;
String address;
String contactName;
String telephone;
Double latitude;
Double longitude;
Boolean isActive;
Boolean isProspect;
String fiscalNumber;
String companyAlias;
Long saleZoneId;
String temporaryCode;
String city;
long facilityGroupId;
double facilityGroupDistance;
String facilityGroupCode;
}
private void mailNotification(ArrayList<String> stackTraceList, String process){
try {
log.info("Send mail start: "+process);
String stackTraceString="";
for (int i = 0; i < stackTraceList.size(); i++) {
stackTraceString = stackTraceList.get(i);
}
ArrayList<String> sendTo = new ArrayList<String>();
sendTo.add("[email protected]");
String mailFrom = "[email protected]";
java.text.DateFormat dateFormat = new java.text.SimpleDateFormat("yyyy/MM/dd");
Date date = new Date();
String today = (dateFormat.format(date));
java.net.InetAddress localMachine = java.net.InetAddress.getLocalHost();
String[] localMachineData;
localMachineData = localMachine.toString().split("/");
String mailSubject = localMachineData[0].toUpperCase()+"- Monitoreo";
String mailBody = stackTraceString;
//log.info("Enviar Mail: " + mailBody);
enviarMail(mailBody, sendTo, mailFrom, mailSubject);
} catch (Throwable th) {
log.error("ERROR mailNotification: "+th.getMessage());
log.debug("ERROR mailNotification: "+th.getMessage(), th);
}
}
private void enviarMail(String mailBody, ArrayList mailTo, String mailFrom, String mailSubject){
MailService mailService = (MailService)appCtx.getBean("mailService");
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 {
to mailTo;
from mailFrom;
subject mailSubject;
body mailBody;
}
}
private void validateSubjectEnterpriseFacility(Connection connection, String subjectCode, Long subjectId) throws SQLException{
PreparedStatement subjectAttributeValuePs = null;
PreparedStatement subjectPs = null;
PreparedStatement deleteSubjectAttributeValuePs = null;
PreparedStatement subjectAttributeValueInsertPs = null;
PreparedStatement subjectAttributeValueUpdatePs = null;
ResultSet subjectAttributeValueRs = null;
ResultSet subjectRs = null;
try {
StringBuilder allSubjectAttributeValuesSb = new StringBuilder();
allSubjectAttributeValuesSb.append("SELECT sav.id, sav.subject_id, sav.attribute_value_id FROM subject_attribute_value sav ");
allSubjectAttributeValuesSb.append("JOIN attribute_value av ON sav.attribute_value_id = av.id JOIN attribute a ON ");
allSubjectAttributeValuesSb.append("av.attribute_id = a.id WHERE a.code = 'sucEmpresa' ");
if(subjectCode != null){
allSubjectAttributeValuesSb.append("AND sav.subject_id = (SELECT id FROM subject WHERE code = '"+subjectCode+"') ");
}else if(subjectId != null && subjectId != 0){
allSubjectAttributeValuesSb.append("AND sav.subject_id = $subjectId ");
}
subjectAttributeValuePs = connection.prepareStatement(allSubjectAttributeValuesSb.toString());
subjectAttributeValueRs = subjectAttributeValuePs.executeQuery();
HashMap<Long, SubjectAttributeValueDTO> allSubjectAttributeValueDTOMap = new HashMap<Long, SubjectAttributeValueDTO>();
SubjectAttributeValueDTO savDTO;
while (subjectAttributeValueRs.next()) {
savDTO = new SubjectAttributeValueDTO();
savDTO.id = subjectAttributeValueRs.getLong("id");
savDTO.subjectId = subjectAttributeValueRs.getLong("subject_id");
savDTO.attributeValueId = subjectAttributeValueRs.getLong("attribute_value_id");
allSubjectAttributeValueDTOMap.put(savDTO.subjectId, savDTO);
}
StringBuilder subjectSb = new StringBuilder();
subjectSb.append("SELECT s.id as subject_id, av.id as attribute_value_id ");
subjectSb.append("FROM subject s JOIN groups g ON g.id = s.facility_group_id ");
subjectSb.append("JOIN attribute_value av ON av.code = g.code ");
if(subjectCode != null){
subjectSb.append("WHERE s.code = '"+subjectCode+"'");
}else if(subjectId != null && subjectId != 0){
subjectSb.append("WHERE s.id = $subjectId ");
}
subjectPs = connection.prepareStatement(subjectSb.toString());
subjectRs = subjectPs.executeQuery();
long attributeId = 0;
StringBuilder subjectAttributeValueInsert = new StringBuilder();
subjectAttributeValueInsert.append("INSERT INTO subject_attribute_value(id, version, attribute_value_id, subject_id) ");
subjectAttributeValueInsert.append("VALUES (nextVal('subject_attribute_value_seq'), 0, ?, ?) ");
subjectAttributeValueInsertPs = connection.prepareStatement(subjectAttributeValueInsert.toString());
StringBuilder subjectAttributeValueUpdateSb = new StringBuilder();
subjectAttributeValueUpdateSb.append("UPDATE subject_attribute_value SET attribute_value_id = ? WHERE subject_id = ? ");
subjectAttributeValueUpdateSb.append("AND attribute_value_id = ? ");
subjectAttributeValueUpdatePs = connection.prepareStatement(subjectAttributeValueUpdateSb.toString());
while(subjectRs.next()){
savDTO = allSubjectAttributeValueDTOMap.get(subjectRs.getLong("subject_id"));
if(savDTO == null){
//Insertar
subjectAttributeValueInsertPs.setLong(1, subjectRs.getLong("attribute_value_id"));
subjectAttributeValueInsertPs.setLong(2, subjectRs.getLong("subject_id"));
subjectAttributeValueInsertPs.executeUpdate();
}else{
if(savDTO.attributeValueId != subjectRs.getLong("attribute_value_id")){
//Actualizar
subjectAttributeValueUpdatePs.setLong(1, subjectRs.getLong("attribute_value_id"));
subjectAttributeValueUpdatePs.setLong(2, subjectRs.getLong("subject_id"));
subjectAttributeValueUpdatePs.setLong(3, savDTO.attributeValueId);
subjectAttributeValueUpdatePs.executeUpdate();
}
allSubjectAttributeValueDTOMap.remove(subjectRs.getLong("subject_id"));
}
}
StringBuilder deleteSubjectAttributeValueSb = new StringBuilder();
deleteSubjectAttributeValueSb.append("DELETE FROM subject_attribute_value WHERE id = ?");
deleteSubjectAttributeValuePs = connection.prepareStatement(deleteSubjectAttributeValueSb.toString());
if (allSubjectAttributeValueDTOMap.size() > 0){
for (SubjectAttributeValueDTO subjectAttributeValueDTO : allSubjectAttributeValueDTOMap.values()) {
deleteSubjectAttributeValuePs.setLong(1, subjectAttributeValueDTO.id);
deleteSubjectAttributeValuePs.addBatch();
}
deleteSubjectAttributeValuePs.executeBatch();
deleteSubjectAttributeValuePs.clearBatch();
}
} catch (Throwable th) {
log.error("ERROR validateSubjectEnterpriseFacility: "+th.getMessage());
log.debug("ERROR validateSubjectEnterpriseFacility: "+th.getMessage(), th);
throw th;
}finally{
DataBaseHelper.closePs(subjectAttributeValuePs);
DataBaseHelper.closePs(subjectPs);
DataBaseHelper.closePs(deleteSubjectAttributeValuePs);
DataBaseHelper.closePs(subjectAttributeValueInsertPs);
DataBaseHelper.closePs(subjectAttributeValueUpdatePs);
DataBaseHelper.closeRs(subjectAttributeValueRs);
DataBaseHelper.closeRs(subjectRs);
}
}
class SubjectAttributeValueDTO {
long id;
long subjectId;
long attributeValueId;
}
class GroupsDto {
long id;
String code;
double latitude;
double longitude;
}
private void recalculateFacilityNumberNew(Connection connection, ArrayList<SubjectFacilityDto> subjectFacilityArrayDto,
ArrayList<HashMap> exceptionMapList) throws Exception{
PreparedStatement reorderPs = null;
PreparedStatement countPs = null;
PreparedStatement subjectUpdatePs = null;
PreparedStatement queryCountPs = null;
PreparedStatement facilitySelectQueryPs = null;
PreparedStatement subjectInternalCodeFacilityExistPs = null;
ResultSet facilitySelectQueryRs = null;
ResultSet subjectInternalCodeFacilityExistRs = null;
HashMap subjectErrorMap;
try{
countPs = connection.prepareStatement("UPDATE subject set facility = (SELECT count(s1.*) + 1 FROM subject s1 WHERE s1.internal_code = ? AND facility > 0) WHERE code = ?");
reorderPs = connection.prepareStatement("UPDATE subject set facility = (facility -1) WHERE id = ?");
subjectUpdatePs = connection.prepareStatement("UPDATE subject set facility = ? WHERE code = ?");
facilitySelectQueryPs = connection.prepareStatement("SELECT id FROM subject WHERE internal_code = ? AND facility > ? ORDER BY facility asc");
subjectInternalCodeFacilityExistPs = connection.prepareStatement("SELECT count(*) FROM subject WHERE internal_code = ? AND facility = ?");
SubjectFacilityDto subjectFacilityDto;
boolean executeProccessFlag = true;
for (int i = 0; i < subjectFacilityArrayDto.size(); i++) {
subjectFacilityDto = subjectFacilityArrayDto.get(i);
try {
if(subjectFacilityDto.operation.equalsIgnoreCase("I")){
countPs.setString(1, subjectFacilityDto.internalCode);
countPs.setString(2, subjectFacilityDto.code);
countPs.executeUpdate();
}else if(subjectFacilityDto.operation.equalsIgnoreCase("U")){
//Setea a la Consulta que nos trae el id de los clientes a renumerar(se resta 1)
facilitySelectQueryPs.setString(1, subjectFacilityDto.internalCode);
facilitySelectQueryPs.setInt(2, subjectFacilityDto.facilityNumber);
facilitySelectQueryRs = facilitySelectQueryPs.executeQuery();
long facilityId = 0;
while(facilitySelectQueryRs.next()){
facilityId = facilitySelectQueryRs.getLong("id");
reorderPs.setLong(1, facilityId);
reorderPs.executeUpdate();
}
}else if(subjectFacilityDto.operation.equalsIgnoreCase("UF")){
if(executeProccessFlag){
try {
subjectUpdatePs.setInt(1, subjectFacilityDto.facilityNumber);
subjectUpdatePs.setString(2, subjectFacilityDto.code);
subjectUpdatePs.executeUpdate();
} catch (Exception e) {
/******************RESUMEN******************/
String stackTrace;
Writer result = new StringWriter();
PrintWriter printWriter = new PrintWriter(result);
e.printStackTrace(printWriter);
//stackTrace = result.toString();
stackTrace = e.getMessage();
subjectErrorMap = new HashMap();
subjectErrorMap.put("subjectCode",subjectFacilityDto.code);
subjectErrorMap.put("subjectName",subjectFacilityDto.subjectName);
subjectErrorMap.put("stackTraceError",stackTrace);
exceptionMapList.add(subjectErrorMap);
/******************************************/
log.error("Error en la modificacion del numero de sucursal para el cliente: "+subjectFacilityDto.code+" - "+subjectFacilityDto.subjectName+" - "+e.getMessage());
log.debug("Error en la modificacion del numero de sucursal para el cliente: "+subjectFacilityDto.code+" - "+subjectFacilityDto.subjectName+" - "+e.getMessage(), e);
executeProccessFlag = false;
i = i - 1;
connection.rollback();
}
}else{
subjectUpdatePs.setInt(1, getFacilityRandom());
subjectUpdatePs.setString(2, subjectFacilityDto.code);
subjectUpdatePs.executeUpdate();
executeProccessFlag = true;
}
}
} catch (Exception e) {
log.debug("ERROR en el ciclo del recalculo de numero de sucursal: "+e);
}
}
}catch(Exception e){
throw e;
}finally{
DataBaseHelper.closeRs(facilitySelectQueryRs);
DataBaseHelper.closePs(reorderPs);
DataBaseHelper.closePs(countPs);
DataBaseHelper.closePs(facilitySelectQueryPs);
DataBaseHelper.closePs(queryCountPs);
DataBaseHelper.closePs(subjectUpdatePs);
DataBaseHelper.closePs(subjectInternalCodeFacilityExistPs);
DataBaseHelper.closeRs(subjectInternalCodeFacilityExistRs);
}
}
private void errorsNotification(ArrayList<HashMap> stackTraceListArrayList, String process) throws Throwable{
try {
ApplicationContext applicationContext = (ApplicationContext)ServletContextHolder.getServletContext().getAttribute(GrailsApplicationAttributes.APPLICATION_CONTEXT);
ConfigurationService configurationService;
configurationService = (ConfigurationService) applicationContext.getBean("configurationService");
MailService mailService;
mailService = (MailService) applicationContext.getBean("mailService");
log.info("Send mail start: "+process);
String stackTraceString = subjectImporterErrorMailStr(stackTraceListArrayList);
java.net.InetAddress localMachine = java.net.InetAddress.getLocalHost();
String[] localMachineData = localMachine.toString().split("/");
String mailTo = configurationService.getConfiguration("mail.to");
String[] mailToList = mailTo.split(";");
String mailFrom =configurationService.getConfiguration("mail.from");
String mailSubject =configurationService.getConfiguration("mail.subject")+
" - EDP: "+localMachineData[0]+
" - "+process;
mailService.sendMail {
to mailToList;
from mailFrom
subject mailSubject
html stackTraceString
}
log.info("Mail sent: "+process);
} catch (Throwable th) {
throw th;
}
}
private String subjectImporterErrorMailStr(ArrayList<HashMap> stackTraceListArrayList){
StringBuilder mailBodySb = new StringBuilder();
try{
String process;
process = "Resumen de clientes con errores de importación";
StringBuilder subjectErrorHtml = new StringBuilder();
subjectErrorHtml.append("<h3>Clientes con errores de importación</h3>");
subjectErrorHtml.append("</br>");
subjectErrorHtml.append("<p>Se detectaron estos clientes con errores de importación los cuales fueron excluidos del proceso. Favor revisar la excepcion y regularizar los registros.</p>");
//prospectInvalidHtml.append("<table style=\"width:50%;border-collapse:collapse;border:1px solid black;\"\">");
subjectErrorHtml.append("<table border = '1px' >");
subjectErrorHtml.append("<tr>");
subjectErrorHtml.append("<td><b>Código</b></td>");
subjectErrorHtml.append("<td><b>Nombre<b></td>");
subjectErrorHtml.append("<td><b>Excepción<b></td>");
subjectErrorHtml.append("</tr>");
for(HashMap subjectErrorMap : stackTraceListArrayList){
String subjectCode = (String) subjectErrorMap.get("subjectCode");
String subjectName = (String) subjectErrorMap.get("subjectName");
String errorMessage = (String) subjectErrorMap.get("stackTraceError");
subjectErrorHtml.append("<tr>");
subjectErrorHtml.append("<td>"+subjectCode+"</td>");
subjectErrorHtml.append("<td>"+subjectName+"</td>");
subjectErrorHtml.append("<td>"+errorMessage+"</td>");
subjectErrorHtml.append("</tr>");
}
subjectErrorHtml.append("</table>");
mailBodySb.append(subjectErrorHtml);
//processNotificationUser(connection, mailBody, process, userLoggedId);
}catch(Throwable th){
log.error("Error en el envio del correo: "+th.getMessage());
log.debug("Error en el envio del correo: "+th.getMessage(), th);
throw th;
}
return mailBodySb.toString();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment