API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 18th, 2017
118
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 99.83 KB | None | 0 0
raw download clone embed print report
  1. package com.apg.logic.dataimport.excel;
  2.  
  3. import java.io.FileInputStream;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  8. import java.text.SimpleDateFormat;
  9. import java.util.HashMap;
  10. import java.util.HashSet;
  11. import java.util.LinkedHashSet;
  12. import java.util.Locale;
  13. import java.util.Map;
  14. import java.util.Properties;
  15. import java.util.Set;
  16. import java.util.TreeMap;
  17.  
  18. import org.apache.commons.lang.StringEscapeUtils;
  19. import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  20. import org.apache.poi.ss.usermodel.Cell;
  21. import org.apache.poi.ss.usermodel.Row;
  22. import org.apache.poi.ss.usermodel.Sheet;
  23. import org.apache.poi.ss.usermodel.Workbook;
  24. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  25.  
  26. import com.apg.entity.Session;
  27. import com.apg.util.DbMan;
  28.  
  29. public class Reader {
  30.  
  31.   private static final String OTHER_TABLES = "other_tables";
  32.   private static final String FBVA_TRADE_LOG = "fbva_trade_log";
  33.   private static final String EQUIVEST_TRADE_LOG = "equivest_trade_log";
  34.   private static final String OMP_TRADE_LOG = "omp_trade_log";
  35.   private static final String SAM_MF_TRADE_LOG = "sam_mf_trade_log";
  36.   private static final String MWP_TRADE_LOG = "mwp_trade_log";
  37.   private static final String ETP_TRADE_LOG = "etp_trade_log";
  38.   private static final String PWP_TRADE_LOG = "pwp_trade_log";
  39.   private static final String MODEL_ALLOCATIONS_PREVIOUS = "model_allocations_previous";
  40.   private static final String MODEL_ALLOCATIONS_CURRENT = "model_allocations_current";
  41.   private static final String MODEL_OBJECTIVE = "model_objective";
  42.   private static final String MODELS = "models";
  43.   private static final String ASSETS = "assets";
  44.   private static final String ASSET_CLASSES = "asset_classes";
  45.   private static final String INDICES = "indices";
  46.   private static final String MODEL_TYPE = "model_type";
  47.   private static final String MANAGER_GUIDE_COVER = "manager_guide_cover";
  48.   private static final String MODEL_TACTICAL_FOCUS = "model_tactical_focus";
  49.   private static final String DISCLOSURE = "disclosure";
  50.   private static final String DISCLOSURE_TYPE = "disclosure_type";
  51.   private static final String MANAGER_RISK_CLASSIFICATION = "manager_risk_classification";
  52.   private static final String PLATFORMS = "platforms";
  53.   private static final String MANAGER_TYPE = "manager_type";
  54.   private static final String POLICY = "policy";
  55.   private static final String INSURANCE_COMPANY = "insurance_company";
  56.   private static final String ASSET_TYPE = "asset_type";
  57.   private static final String ASSET_TIER = "asset_tier";
  58.   private static final Set<String> TABLE_LOAD_ORDER = new LinkedHashSet<String>() {
  59.     private static final long serialVersionUID = 1L;
  60.     {
  61.       add(ASSET_TIER);
  62.       add(ASSET_TYPE);
  63.       add(INSURANCE_COMPANY);
  64.       add(POLICY);
  65.       add(MANAGER_TYPE);
  66.       add(PLATFORMS);
  67.       add(MANAGER_RISK_CLASSIFICATION);
  68.       add(DISCLOSURE_TYPE);
  69.       add(DISCLOSURE);
  70.       add(MODEL_TACTICAL_FOCUS);
  71.       add(MANAGER_GUIDE_COVER);
  72.  
  73.       add(MODEL_TYPE);
  74.       add(INDICES);
  75.       add(ASSET_CLASSES);
  76.       add(ASSETS);
  77.       add(MODELS);
  78.       add(MODEL_OBJECTIVE);
  79.       add(MODEL_ALLOCATIONS_CURRENT);
  80.       add(MODEL_ALLOCATIONS_PREVIOUS);
  81.       add(PWP_TRADE_LOG);
  82.       add(ETP_TRADE_LOG);
  83.       add(MWP_TRADE_LOG);
  84.       add(SAM_MF_TRADE_LOG);
  85.       add(OMP_TRADE_LOG);
  86.       add(EQUIVEST_TRADE_LOG);
  87.       add(FBVA_TRADE_LOG);
  88.       add(OTHER_TABLES);
  89.     }
  90.   };
  91.  
  92.   public void processWorkbook(Workbook workbook) throws SQLException {
  93.     int sheetCount = workbook.getNumberOfSheets();
  94.     for (int i = 0; i < sheetCount; i++) {
  95.       Sheet sheet = workbook.getSheetAt(i);
  96.       String sheetName = sheet.getSheetName();
  97.       if (ASSET_TIER.equalsIgnoreCase(sheetName)) {
  98.         tables.put(ASSET_TIER, processAssetTier(sheet));
  99.       } else if (ASSET_TYPE.equalsIgnoreCase(sheetName)) {
  100.         tables.put(ASSET_TYPE, processAssetType(sheet));
  101.       } else if (INSURANCE_COMPANY.equalsIgnoreCase(sheetName)) {
  102.         tables.put(INSURANCE_COMPANY, processInsuranceCompany(sheet));
  103.       } else if (MANAGER_TYPE.equalsIgnoreCase(sheetName)) {
  104.         tables.put(MANAGER_TYPE, processManagerType(sheet));
  105.       } else if (POLICY.equalsIgnoreCase(sheetName)) {
  106.         tables.put(POLICY, processPolicy(sheet));
  107.       } else if (PLATFORMS.equalsIgnoreCase(sheetName)) {
  108.         tables.put(PLATFORMS, processPlatform(sheet));
  109.       } else if (MANAGER_RISK_CLASSIFICATION.equalsIgnoreCase(sheetName)) {
  110.         tables.put(MANAGER_RISK_CLASSIFICATION, processManagerRiskClassification(sheet));
  111.       } else if (MODEL_TACTICAL_FOCUS.equalsIgnoreCase(sheetName)) {
  112.         tables.put(MODEL_TACTICAL_FOCUS, processModelTacticalFocus(sheet));
  113.       } else if (DISCLOSURE.equalsIgnoreCase(sheetName)) {
  114.         tables.put(DISCLOSURE_TYPE, processDisclosureType(sheet));
  115.         tables.put(DISCLOSURE, processDisclosure(sheet));
  116.       } else if (MANAGER_GUIDE_COVER.equalsIgnoreCase(sheetName)) {
  117.         tables.put(MANAGER_GUIDE_COVER, processManagerGuideCover(sheet));
  118.       } else if (MODEL_TYPE.equalsIgnoreCase(sheetName)) {
  119.         tables.put(MODEL_TYPE, processModelType(sheet));
  120.       } else if (INDICES.equalsIgnoreCase(sheetName)) {
  121.         tables.put(INDICES, processIndices(sheet));
  122.       } else if (ASSET_CLASSES.equalsIgnoreCase(sheetName)) {
  123.         tables.put(ASSET_CLASSES, processAssetClasses(sheet));
  124.       } else if (ASSETS.equalsIgnoreCase(sheetName)) {
  125.         tables.put(ASSETS, processAssets(sheet));
  126.       } else if (MODELS.equalsIgnoreCase(sheetName)) {
  127.         tables.put(MODELS, processModels(sheet));
  128.       } else if (MODEL_OBJECTIVE.equalsIgnoreCase(sheetName)) {
  129.         tables.put(MODEL_OBJECTIVE, processModelObjective(sheet));
  130.       } else if (MODEL_ALLOCATIONS_CURRENT.equalsIgnoreCase(sheetName)) {
  131.         tables.put(MODEL_ALLOCATIONS_CURRENT, processModelAllocationsCurrent(sheet));
  132.       } else if (MODEL_ALLOCATIONS_PREVIOUS.equalsIgnoreCase(sheetName)) {
  133.         tables.put(MODEL_ALLOCATIONS_PREVIOUS, processModelAllocationsPrevious(sheet));
  134.       }
  135.     }
  136.     tables.put(OTHER_TABLES, deleteAssetIdTemp());
  137.   }
  138.  
  139.   public void processTradeLogsWorkbook(Workbook workbook) throws SQLException {
  140.     int sheetCount = workbook.getNumberOfSheets();
  141.     for (int i = 0; i < sheetCount; i++) {
  142.       Sheet sheet = workbook.getSheetAt(i);
  143.       String sheetName = sheet.getSheetName();
  144.       if (PWP_TRADE_LOG.equalsIgnoreCase(sheetName)) {
  145.         tables.put(PWP_TRADE_LOG, processPWPTradeLog(sheet));
  146.       } else if (ETP_TRADE_LOG.equalsIgnoreCase(sheetName)) {
  147.         tables.put(ETP_TRADE_LOG, processETPTradeLog(sheet));
  148.       } else if (MWP_TRADE_LOG.equalsIgnoreCase(sheetName)) {
  149.         tables.put(MWP_TRADE_LOG, processMWPTradeLog(sheet));
  150.       } else if (SAM_MF_TRADE_LOG.equalsIgnoreCase(sheetName)) {
  151.         tables.put(SAM_MF_TRADE_LOG, processSAMTradeLog(sheet));
  152.       } else if (OMP_TRADE_LOG.equalsIgnoreCase(sheetName)) {
  153.         tables.put(OMP_TRADE_LOG, processOMPTradeLog(sheet));
  154.       } else if (EQUIVEST_TRADE_LOG.equalsIgnoreCase(sheetName)) {
  155.         tables.put(EQUIVEST_TRADE_LOG, processEquivestTradeLog(sheet));
  156.       } else if (FBVA_TRADE_LOG.equalsIgnoreCase(sheetName)) {
  157.         tables.put(FBVA_TRADE_LOG, processFBVATradeLog(sheet));
  158.       }
  159.     }
  160.   }
  161.  
  162.   private StringBuffer processFBVATradeLog(Sheet sheet) throws SQLException {
  163.     StringBuffer result = new StringBuffer();
  164.     result
  165.         .append("DROP TABLE IF EXISTS `trade_log_id_temp`; \n CREATE TABLE `trade_log_id_temp` (`trade_log_id` INT(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; \n insert into trade_log_id_temp (trade_log_id) select distinct tl.trade_log_id from trade_log as tl  join strategy as s on s.strategy_id=tl.strategy_id   join model_objective as mo on tl.model_objective_id=mo.model_objective_id  join model as m on m.model_id=tl.model_id  join trade_log_assets as tla on tla.trade_log_id=tl.trade_log_id  join asset as a1 on a1.asset_id=tla.asset_id_previous  join asset as a2 on a2.asset_id=tla.asset_id_current  join data_group as dg1 on dg1.data_group_id=m.data_group_id  join data_group as dg2 on dg2.data_group_id=s.platform_id  join model_direction as md on md.model_direction_id=m.model_direction_id  join model_product as mp on mp.model_product_id=m.model_product_id  join insurance_policy as ip on ip.policy_id=m.policy_id join insurance_company as ic on ip.insurance_company_id=ic.insurance_company_id where (dg1.abbreviation='SAM'and dg2.abbreviation='SAM' OR dg1.abbreviation='SWM'and dg2.abbreviation='SWM') and mp.model_product_code='VA'  order by dg1.abbreviation,ic.insurance_company_name, ip.insurance_policy_name,md.model_direction_name; \n delete from trade_log where trade_log_id in (select * from trade_log_id_temp); \n");
  166.     String lastUpdatedDate;
  167.     String lastTradeDate;
  168.     String modelObjectiveCode;
  169.     String modelObjectiveId;
  170.     Row dateRow = sheet.getRow(0);
  171.     String asOfDate = dateRow!=null?prepareCellData(dateRow.getCell(3)):null;
  172.     int objectivesCount = Integer
  173.         .parseInt(getIdByName("select count(`model_objective_id`) as `id` from `model_objective`"));
  174.     int offset = 0;
  175.     for (int i = 0; i < objectivesCount; i++) {
  176.       int startRow = 0;
  177.       Row row = sheet.getRow(startRow);
  178.       while (startRow <= sheet.getLastRowNum()) {
  179.         if (!isRowNullable(row)
  180.             && ((lastTradeDate = prepareCellData(row.getCell(0))) != null || (prepareCellData(row.getCell(1 + offset))) != null
  181.                 && prepareCellData(row.getCell(2 + offset)) == null
  182.                 && !prepareCellData(row.getCell(1 + offset)).equalsIgnoreCase("'As of Date'"))) {
  183.           lastUpdatedDate = prepareCellData(sheet.getRow(startRow - 1).getCell(0));
  184.           modelObjectiveCode = prepareCellData(sheet.getRow(startRow - 1).getCell(1 + offset));
  185.           modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  186.               + modelObjectiveCode);
  187.           startRow++;
  188.           row = sheet.getRow(startRow);
  189.           String firstCol = null;
  190.           if (isRowNullable(row) || (firstCol = prepareCellData(row.getCell(0))) == null) {
  191.             row = sheet.getRow(startRow++);
  192.             continue;
  193.           }
  194.           if (firstCol != null && firstCol.startsWith("'") && firstCol.endsWith("'")) {
  195.             firstCol = firstCol.substring(1, firstCol.length() - 1);
  196.           }
  197.           String dataGroupName = firstCol.split(":")[0].trim();
  198.           String insurancePolicyName = firstCol.split(":")[2].trim();
  199.           String modelDirectionName = firstCol.split(":")[3].trim();
  200.           String dataGroupId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = '"
  201.               + dataGroupName + "'");
  202.           String insurancePolicyId = getIdByName("select `policy_id` as `id` from `insurance_policy` "
  203.               + " where `insurance_policy_name` = '" + insurancePolicyName + "'");
  204.           String modelDirectionId = "'"
  205.               + getIdByName("select `model_direction_id` as `id` from `model_direction` where `model_direction_name` = '"
  206.                   + modelDirectionName + "'") + "'";
  207.           String strategyId = getIdByName("select `strategy_id` as `id` from `model` "
  208.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `model_objective_id`="
  209.               + modelObjectiveId + " AND `data_group_id`=" + dataGroupId + " AND `model`.`model_direction_id`="
  210.               + modelDirectionId + " AND mp.model_product_code='VA' " + " AND `policy_id`=" + insurancePolicyId);
  211.           if (strategyId == null) {
  212.             row = sheet.getRow(startRow++);
  213.             continue;
  214.           }
  215.           String modelId = getIdByName("select `model_id` as `id` from `model` "
  216.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `model_objective_id`="
  217.               + modelObjectiveId + " AND `data_group_id`=" + dataGroupId + " AND `model`.`model_direction_id`="
  218.               + modelDirectionId + " AND mp.model_product_code='VA' " + " AND `policy_id`=" + insurancePolicyId);
  219.           String tradeLogId = "(select `trade_log_id` as `id` from `trade_log` where `model_id`=" + modelId
  220.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `strategy_id`=" + strategyId + " LIMIT 1)";
  221.           if (modelId != null) {
  222.             result.append("INSERT INTO `trade_log` (`model_id`, `model_objective_id`, `strategy_id`, "
  223.                 + " `as_of_date`, `last_updated`, `last_trade_date`) VALUES (" + modelId + ", " + modelObjectiveId
  224.                 + ", " + strategyId + "," + asOfDate + ", " + lastUpdatedDate + ", " + lastTradeDate + ") "
  225.                 + "ON DUPLICATE KEY UPDATE `as_of_date`=" + asOfDate + ", `last_updated`=" + lastUpdatedDate
  226.                 + ", `last_trade_date`=" + lastTradeDate + "; \n");
  227.             for (; startRow <= sheet.getLastRowNum(); startRow++) {
  228.               row = sheet.getRow(startRow);
  229.               if (!isRowNullable(row)) {
  230.                 result.append(processTradeLogGroup(tradeLogId, prepareCellData(row.getCell(1 + offset)),
  231.                     prepareCellData(row.getCell(2 + offset)), prepareCellData(row.getCell(3 + offset)),
  232.                     prepareCellData(row.getCell(4 + offset))));
  233.               } else {
  234.                 break;
  235.               }
  236.             }
  237.           }
  238.         }
  239.         row = sheet.getRow(startRow++);
  240.       }
  241.       offset += 5;
  242.     }
  243.     return result;
  244.   }
  245.  
  246.   private StringBuffer processEquivestTradeLog(Sheet sheet) throws SQLException {
  247.     StringBuffer result = new StringBuffer();
  248.     result
  249.         .append("DROP TABLE IF EXISTS `trade_log_id_temp`; \n CREATE TABLE `trade_log_id_temp` (`trade_log_id` INT(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; \n insert into trade_log_id_temp (trade_log_id) select distinct tl.trade_log_id from trade_log as tl  join strategy as s on s.strategy_id=tl.strategy_id   join model_objective as mo on tl.model_objective_id=mo.model_objective_id  join model as m on m.model_id=tl.model_id  join trade_log_assets as tla on tla.trade_log_id=tl.trade_log_id  join asset as a1 on a1.asset_id=tla.asset_id_previous  join asset as a2 on a2.asset_id=tla.asset_id_current  join data_group as dg1 on dg1.data_group_id=m.data_group_id  join data_group as dg2 on dg2.data_group_id=s.platform_id  join model_direction as md on md.model_direction_id=m.model_direction_id  join model_product as mp on mp.model_product_id=m.model_product_id  join annuity_type as ant on ant.annuity_type_id=m.annuity_type_id where dg1.abbreviation='SAM'and dg2.abbreviation='SAM' and mp.model_product_code='VA'  order by dg1.abbreviation,ant.annuity_type_name,mp.model_product_name; \n delete from trade_log where trade_log_id in (select * from trade_log_id_temp); \n");
  250.     String lastUpdatedDate;
  251.     String lastTradeDate;
  252.     String modelObjectiveCode;
  253.     String modelObjectiveId;
  254.     String dataGroupId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = 'SAM'");
  255.     Row dateRow = sheet.getRow(0);
  256.     String asOfDate = dateRow!=null?prepareCellData(dateRow.getCell(3)):null;
  257.     int objectivesCount = Integer
  258.         .parseInt(getIdByName("select count(`model_objective_id`) as `id` from `model_objective`"));
  259.     int offset = 0;
  260.     for (int i = 0; i < objectivesCount; i++) {
  261.       int startRow = 0;
  262.       Row row = sheet.getRow(startRow);
  263.       while (startRow <= sheet.getLastRowNum()) {
  264.         if (!isRowNullable(row)
  265.             && ((lastTradeDate = prepareCellData(row.getCell(0))) != null || (prepareCellData(row.getCell(1 + offset))) != null
  266.                 && prepareCellData(row.getCell(2 + offset)) == null
  267.                 && !prepareCellData(row.getCell(1 + offset)).equalsIgnoreCase("'As of Date'"))) {
  268.           lastUpdatedDate = prepareCellData(sheet.getRow(startRow - 1).getCell(0));
  269.           modelObjectiveCode = prepareCellData(sheet.getRow(startRow - 1).getCell(1 + offset));
  270.           modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  271.               + modelObjectiveCode);
  272.           startRow++;
  273.           row = sheet.getRow(startRow);
  274.           String firstCol = null;
  275.           if (isRowNullable(row) || (firstCol = prepareCellData(row.getCell(0))) == null) {
  276.             row = sheet.getRow(startRow++);
  277.             continue;
  278.           }
  279.           if (firstCol != null && firstCol.startsWith("'") && firstCol.endsWith("'")) {
  280.             firstCol = firstCol.substring(1, firstCol.length() - 1);
  281.           }
  282.           String annuityTypeName = firstCol.split(":")[1].trim();
  283.           String modelProducName = firstCol.split(":")[2].trim();
  284.           String annuityTypeId = getIdByName("select `annuity_type_id` as `id` from `annuity_type` "
  285.               + " where `annuity_type_name` = '" + annuityTypeName + "'");
  286.           String modelProductId = "'"
  287.               + getIdByName("select `model_product_id` as `id` from `model_product` where `model_product_name` = '"
  288.                   + modelProducName + "'") + "'";
  289.           String strategyId = getIdByName("select `strategy_id` as `id` from `model` "
  290.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `model_objective_id`="
  291.               + modelObjectiveId + " AND `data_group_id`=" + dataGroupId + " AND `model`.`model_product_id`="
  292.               + modelProductId + " AND mp.model_product_code='VA' AND annuity_type_id=" + annuityTypeId);
  293.           if (strategyId == null) {
  294.             row = sheet.getRow(startRow++);
  295.             continue;
  296.           }
  297.           String modelId = getIdByName("select `model_id` as `id` from `model` "
  298.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `model_objective_id`="
  299.               + modelObjectiveId + " AND `data_group_id`=" + dataGroupId + " AND `model`.`model_product_id`="
  300.               + modelProductId + " AND mp.model_product_code='VA' AND annuity_type_id=" + annuityTypeId);
  301.           String tradeLogId = "(select `trade_log_id` as `id` from `trade_log` where `model_id`=" + modelId
  302.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `strategy_id`=" + strategyId + " LIMIT 1)";
  303.           if (modelId != null) {
  304.             result.append("INSERT INTO `trade_log` (`model_id`, `model_objective_id`, `strategy_id`, "
  305.                 + " `as_of_date`, `last_updated`, `last_trade_date`) VALUES (" + modelId + ", " + modelObjectiveId
  306.                 + ", " + strategyId + "," + asOfDate + ", " + lastUpdatedDate + ", " + lastTradeDate + ") "
  307.                 + "ON DUPLICATE KEY UPDATE `as_of_date`=" + asOfDate + ", `last_updated`=" + lastUpdatedDate
  308.                 + ", `last_trade_date`=" + lastTradeDate + "; \n");
  309.             for (; startRow <= sheet.getLastRowNum(); startRow++) {
  310.               row = sheet.getRow(startRow);
  311.               if (!isRowNullable(row)) {
  312.                 result.append(processTradeLogGroup(tradeLogId, prepareCellData(row.getCell(1 + offset)),
  313.                     prepareCellData(row.getCell(2 + offset)), prepareCellData(row.getCell(3 + offset)),
  314.                     prepareCellData(row.getCell(4 + offset))));
  315.               } else {
  316.                 break;
  317.               }
  318.             }
  319.           }
  320.         }
  321.         row = sheet.getRow(startRow++);
  322.       }
  323.       offset += 5;
  324.     }
  325.     return result;
  326.   }
  327.  
  328.   private StringBuffer processOMPTradeLog(Sheet sheet) throws SQLException {
  329.     StringBuffer result = new StringBuffer();
  330.     result
  331.         .append("DROP TABLE IF EXISTS `trade_log_id_temp`; \n CREATE TABLE `trade_log_id_temp` (`trade_log_id` INT(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; \n insert into trade_log_id_temp (trade_log_id) select distinct tl.trade_log_id from trade_log as tl join strategy as s on s.strategy_id=tl.strategy_id   join model_objective as mo on tl.model_objective_id=mo.model_objective_id  join model as m on m.model_id=tl.model_id join trade_log_assets as tla on tla.trade_log_id=tl.trade_log_id  join asset as a1 on a1.asset_id=tla.asset_id_previous join asset as a2 on a2.asset_id=tla.asset_id_current  join data_group as dg1 on dg1.data_group_id=m.data_group_id  join data_group as dg2 on dg2.data_group_id=s.platform_id  join model_direction as md on md.model_direction_id=m.model_direction_id  join model_product as mp on mp.model_product_id=m.model_product_id  where dg1.abbreviation='OMP'and dg2.abbreviation='OMP' and mp.model_product_code='MF'  order by s.name,md.model_direction_name; \n delete from trade_log where trade_log_id in (select * from trade_log_id_temp); \n");
  332.     String lastUpdatedDate;
  333.     String lastTradeDate;
  334.     String modelObjectiveCode;
  335.     String modelObjectiveId;
  336.     String dataGroupId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = 'OMP'");
  337.     Row dateRow = sheet.getRow(0);
  338.     String asOfDate = dateRow!=null?prepareCellData(dateRow.getCell(3)):null;
  339.     int objectivesCount = Integer
  340.         .parseInt(getIdByName("select count(`model_objective_id`) as `id` from `model_objective`"));
  341.     int offset = 0;
  342.     for (int i = 0; i < objectivesCount; i++) {
  343.       int startRow = 0;
  344.       Row row = sheet.getRow(startRow);
  345.       while (startRow <= sheet.getLastRowNum()) {
  346.         if (!isRowNullable(row) && (lastTradeDate = prepareCellData(row.getCell(0))) != null) {
  347.           lastUpdatedDate = prepareCellData(sheet.getRow(startRow - 1).getCell(0));
  348.           modelObjectiveCode = prepareCellData(sheet.getRow(startRow - 1).getCell(1 + offset));
  349.           modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  350.               + modelObjectiveCode);
  351.           startRow++;
  352.           row = sheet.getRow(startRow);
  353.           String firstCol = null;
  354.           if (isRowNullable(row) || (firstCol = prepareCellData(row.getCell(0))) == null) {
  355.             row = sheet.getRow(startRow++);
  356.             continue;
  357.           }
  358.           if (firstCol != null && firstCol.startsWith("'") && firstCol.endsWith("'")) {
  359.             firstCol = firstCol.substring(1, firstCol.length() - 1);
  360.           }
  361.           String strategyName = firstCol.split(":")[0].trim();
  362.           String modelDirectionName = firstCol.split(":")[1].trim();
  363.           String strategyId = getIdByName("select `strategy_id` as `id` from `strategy` "
  364.               + "join data_group as dg1 on dg1.data_group_id=`strategy`.platform_id  where `strategy`.`name` = '"
  365.               + strategyName + "' AND dg1.abbreviation='OMP'");
  366.           if (strategyId == null) {
  367.             Set<String> tableProblems = problems.get(sheet.getSheetName().toLowerCase());
  368.             if (tableProblems == null) {
  369.               tableProblems = new HashSet<String>();
  370.               problems.put(sheet.getSheetName().toLowerCase(), tableProblems);
  371.             }
  372.             tableProblems.add("Can't find strategy:" + strategyName + " in DB");
  373.             row = sheet.getRow(startRow++);
  374.             continue;
  375.           }
  376.           String modelDirectionId = "'"
  377.               + getIdByName("select `model_direction_id` as `id` from `model_direction` where `model_direction_name` = '"
  378.                   + modelDirectionName + "'") + "'";
  379.           String modelId = getIdByName("select `model_id` as `id` from `model` "
  380.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `strategy_id` = "
  381.               + strategyId + " AND `model_objective_id`=" + modelObjectiveId + " AND `data_group_id`=" + dataGroupId
  382.               + " AND `model_direction_id`=" + modelDirectionId + " AND mp.model_product_code='MF'");
  383.           String tradeLogId = "(select `trade_log_id` as `id` from `trade_log` where `model_id`=" + modelId
  384.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `strategy_id`=" + strategyId + " LIMIT 1)";
  385.           if (modelId != null) {
  386.             result.append("INSERT INTO `trade_log` (`model_id`, `model_objective_id`, `strategy_id`, "
  387.                 + " `as_of_date`, `last_updated`, `last_trade_date`) VALUES (" + modelId + ", " + modelObjectiveId
  388.                 + ", " + strategyId + "," + asOfDate + ", " + lastUpdatedDate + ", " + lastTradeDate + ") "
  389.                 + "ON DUPLICATE KEY UPDATE `as_of_date`=" + asOfDate + ", `last_updated`=" + lastUpdatedDate
  390.                 + ", `last_trade_date`=" + lastTradeDate + "; \n");
  391.             for (; startRow <= sheet.getLastRowNum(); startRow++) {
  392.               row = sheet.getRow(startRow);
  393.               if (!isRowNullable(row)) {
  394.                 result.append(processTradeLogGroup(tradeLogId, prepareCellData(row.getCell(1 + offset)),
  395.                     prepareCellData(row.getCell(2 + offset)), prepareCellData(row.getCell(3 + offset)),
  396.                     prepareCellData(row.getCell(4 + offset))));
  397.               } else {
  398.                 break;
  399.               }
  400.             }
  401.           }
  402.         }
  403.         row = sheet.getRow(startRow++);
  404.       }
  405.       offset += 5;
  406.     }
  407.     return result;
  408.   }
  409.  
  410.   private StringBuffer processSAMTradeLog(Sheet sheet) throws SQLException {
  411.     StringBuffer result = new StringBuffer();
  412.     result
  413.         .append("DROP TABLE IF EXISTS `trade_log_id_temp`; \n CREATE TABLE `trade_log_id_temp` (`trade_log_id` INT(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; \n insert into trade_log_id_temp (trade_log_id) select distinct tl.trade_log_id from trade_log as tl  join strategy as s on s.strategy_id=tl.strategy_id  join model_objective as mo on tl.model_objective_id=mo.model_objective_id join model as m on m.model_id=tl.model_id join trade_log_assets as tla on tla.trade_log_id=tl.trade_log_id join asset as a1 on a1.asset_id=tla.asset_id_previous join asset as a2 on a2.asset_id=tla.asset_id_current join data_group as dg1 on dg1.data_group_id=m.data_group_id join data_group as dg2 on dg2.data_group_id=s.platform_id join model_direction as md on md.model_direction_id=m.model_direction_id join model_product as mp on mp.model_product_id=m.model_product_id where dg1.abbreviation='SAM'and dg2.abbreviation='SAM' and mp.model_product_code='MF'  order by s.name,md.model_direction_name; \n delete from trade_log where trade_log_id in (select * from trade_log_id_temp); \n");
  414.     String lastUpdatedDate;
  415.     String lastTradeDate;
  416.     String modelObjectiveCode;
  417.     String modelObjectiveId;
  418.     String dataGroupId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = 'SAM'");
  419.     Row dateRow = sheet.getRow(0);
  420.     String asOfDate = dateRow!=null?prepareCellData(dateRow.getCell(3)):null;
  421.     int objectivesCount = Integer
  422.         .parseInt(getIdByName("select count(`model_objective_id`) as `id` from `model_objective`"));
  423.     int offset = 0;
  424.     for (int i = 0; i < objectivesCount; i++) {
  425.       int startRow = 0;
  426.       Row row = sheet.getRow(startRow);
  427.       while (startRow <= sheet.getLastRowNum()) {
  428.         if (!isRowNullable(row) && (lastTradeDate = prepareCellData(row.getCell(0))) != null) {
  429.           lastUpdatedDate = prepareCellData(sheet.getRow(startRow - 1).getCell(0));
  430.           modelObjectiveCode = prepareCellData(sheet.getRow(startRow - 1).getCell(1 + offset));
  431.           modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  432.               + modelObjectiveCode);
  433.           startRow++;
  434.           row = sheet.getRow(startRow);
  435.           if (isRowNullable(row)) {
  436.             row = sheet.getRow(startRow++);
  437.             continue;
  438.           }
  439.           String firstCol = getFirstCol(sheet, startRow, row);
  440.           String strategyName = firstCol.split(":")[0].trim();
  441.           String modelDirectionName = firstCol.split(":")[1].trim();
  442.           String strategyId = getIdByName("select `strategy_id` as `id` from `strategy` "
  443.               + "join data_group as dg1 on dg1.data_group_id=`strategy`.platform_id  where `strategy`.`name` = '"
  444.               + strategyName + "' AND dg1.abbreviation='SAM'");
  445.           if (strategyId == null) {
  446.             Set<String> tableProblems = problems.get(sheet.getSheetName().toLowerCase());
  447.             if (tableProblems == null) {
  448.               tableProblems = new HashSet<String>();
  449.               problems.put(sheet.getSheetName().toLowerCase(), tableProblems);
  450.             }
  451.             tableProblems.add("Can't find strategy:" + strategyName + " in DB");
  452.             row = sheet.getRow(startRow++);
  453.             continue;
  454.           }
  455.           String modelDirectionId = "'"
  456.               + getIdByName("select `model_direction_id` as `id` from `model_direction` where `model_direction_name` = '"
  457.                   + modelDirectionName + "'") + "'";
  458.           String modelId = getIdByName("select `model_id` as `id` from `model` "
  459.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `strategy_id` = "
  460.               + strategyId + " AND `model_objective_id`=" + modelObjectiveId + " AND `data_group_id`=" + dataGroupId
  461.               + " AND `model_direction_id`=" + modelDirectionId + " AND mp.model_product_code='MF'");
  462.           String tradeLogId = "(select `trade_log_id` as `id` from `trade_log` where `model_id`=" + modelId
  463.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `strategy_id`=" + strategyId + " LIMIT 1)";
  464.           if (modelId != null) {
  465.             result.append("INSERT INTO `trade_log` (`model_id`, `model_objective_id`, `strategy_id`, "
  466.                 + " `as_of_date`, `last_updated`, `last_trade_date`) VALUES (" + modelId + ", " + modelObjectiveId
  467.                 + ", " + strategyId + "," + asOfDate + ", " + lastUpdatedDate + ", " + lastTradeDate + ") "
  468.                 + "ON DUPLICATE KEY UPDATE `as_of_date`=" + asOfDate + ", `last_updated`=" + lastUpdatedDate
  469.                 + ", `last_trade_date`=" + lastTradeDate + "; \n");
  470.  
  471.             for (; startRow <= sheet.getLastRowNum(); startRow++) {
  472.               row = sheet.getRow(startRow);
  473.               if (!isRowNullable(row)) {
  474.                 result.append(processTradeLogGroup(tradeLogId, prepareCellData(row.getCell(1 + offset)),
  475.                     prepareCellData(row.getCell(2 + offset)), prepareCellData(row.getCell(3 + offset)),
  476.                     prepareCellData(row.getCell(4 + offset))));
  477.               } else {
  478.                 break;
  479.               }
  480.             }
  481.           }
  482.         }
  483.         row = sheet.getRow(startRow++);
  484.       }
  485.       offset += 5;
  486.     }
  487.     return result;
  488.   }
  489.  
  490.   private String getFirstCol(Sheet sheet, int startRow, Row row) {
  491.     String firstCol = prepareCellData(row.getCell(0));
  492.     int goback = 2;
  493.     while (firstCol == null && (startRow - goback) >= 0) {
  494.       firstCol = prepareCellData(sheet.getRow(startRow - goback).getCell(0));
  495.       goback++;
  496.     }
  497.     if (firstCol != null && firstCol.startsWith("'") && firstCol.endsWith("'")) {
  498.       firstCol = firstCol.substring(1, firstCol.length() - 1);
  499.     }
  500.     return firstCol;
  501.   }
  502.  
  503.   private StringBuffer processMWPTradeLog(Sheet sheet) throws SQLException {
  504.     StringBuffer result = new StringBuffer();
  505.     result
  506.         .append("DROP TABLE IF EXISTS `trade_log_id_temp`; \n CREATE TABLE `trade_log_id_temp` (`trade_log_id` INT(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; \n insert into trade_log_id_temp (trade_log_id) select distinct tl.trade_log_id from trade_log as tl  join strategy as s on s.strategy_id=tl.strategy_id  join model_objective as mo on tl.model_objective_id=mo.model_objective_id join model as m on m.model_id=tl.model_id join trade_log_assets as tla on tla.trade_log_id=tl.trade_log_id join asset as a1 on a1.asset_id=tla.asset_id_previous join asset as a2 on a2.asset_id=tla.asset_id_current join data_group as dg1 on dg1.data_group_id=m.data_group_id join data_group as dg2 on dg2.data_group_id=s.platform_id join model_direction as md on md.model_direction_id=m.model_direction_id join model_product as mp on mp.model_product_id=m.model_product_id where dg1.abbreviation='MWP'and dg2.abbreviation='MWP' and mp.model_product_code='MF'  order by s.name,md.model_direction_name; \n delete from trade_log where trade_log_id in (select * from trade_log_id_temp); \n");
  507.     String lastUpdatedDate;
  508.     String lastTradeDate;
  509.     String modelObjectiveCode;
  510.     String modelObjectiveId;
  511.     String dataGroupId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = 'MWP'");
  512.     Row dateRow = sheet.getRow(0);
  513.     String asOfDate = dateRow!=null?prepareCellData(dateRow.getCell(3)):null;
  514.     int objectivesCount = Integer
  515.         .parseInt(getIdByName("select count(`model_objective_id`) as `id` from `model_objective`"));
  516.     int offset = 0;
  517.     for (int i = 0; i < objectivesCount; i++) {
  518.       int startRow = 0;
  519.       Row row = sheet.getRow(startRow);
  520.       while (startRow <= sheet.getLastRowNum()) {
  521.         if (!isRowNullable(row)
  522.             && ((lastTradeDate = prepareCellData(row.getCell(0))) != null || (prepareCellData(row.getCell(1 + offset))) != null
  523.                 && prepareCellData(row.getCell(2 + offset)) == null
  524.                 && !prepareCellData(row.getCell(1 + offset)).equalsIgnoreCase("'As of Date'"))) {
  525.           lastUpdatedDate = prepareCellData(sheet.getRow(startRow - 1).getCell(0));
  526.           modelObjectiveCode = prepareCellData(sheet.getRow(startRow - 1).getCell(1 + offset));
  527.           modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  528.               + modelObjectiveCode);
  529.           startRow++;
  530.           row = sheet.getRow(startRow);
  531.           if (isRowNullable(row)) {
  532.             row = sheet.getRow(startRow++);
  533.             continue;
  534.           }
  535.           String firstCol = getFirstCol(sheet, startRow, row);
  536.           String strategyName = firstCol.split(":")[0].trim();
  537.           String modelDirectionName = firstCol.split(":")[1].trim();
  538.           String strategyId = getIdByName("select `strategy_id` as `id` from `strategy` "
  539.               + "join data_group as dg1 on dg1.data_group_id=`strategy`.platform_id  "
  540.               + "join model_product as mp using(model_product_id) where `strategy`.`name` = '" + strategyName
  541.               + "' AND dg1.abbreviation='MWP' AND mp.model_product_code='MF'");
  542.           if (strategyId == null) {
  543.             Set<String> tableProblems = problems.get(sheet.getSheetName().toLowerCase());
  544.             if (tableProblems == null) {
  545.               tableProblems = new HashSet<String>();
  546.               problems.put(sheet.getSheetName().toLowerCase(), tableProblems);
  547.             }
  548.             tableProblems.add("Can't find strategy:" + strategyName + " in DB");
  549.             row = sheet.getRow(startRow++);
  550.             continue;
  551.           }
  552.           String modelDirectionId = "'"
  553.               + getIdByName("select `model_direction_id` as `id` from `model_direction` where `model_direction_name` = '"
  554.                   + modelDirectionName + "'") + "'";
  555.           String modelId = getIdByName("select `model_id` as `id` from `model` "
  556.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `strategy_id` = "
  557.               + strategyId + " AND `model_objective_id`=" + modelObjectiveId + " AND `data_group_id`=" + dataGroupId
  558.               + " AND `model_direction_id`=" + modelDirectionId + " AND mp.model_product_code='MF'");
  559.           String tradeLogId = "(select `trade_log_id` as `id` from `trade_log` where `model_id`=" + modelId
  560.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `strategy_id`=" + strategyId + " LIMIT 1)";
  561.           if (modelId != null) {
  562.             result.append("INSERT INTO `trade_log` (`model_id`, `model_objective_id`, `strategy_id`, "
  563.                 + " `as_of_date`, `last_updated`, `last_trade_date`) VALUES (" + modelId + ", " + modelObjectiveId
  564.                 + ", " + strategyId + "," + asOfDate + ", " + lastUpdatedDate + ", " + lastTradeDate + ") "
  565.                 + "ON DUPLICATE KEY UPDATE `as_of_date`=" + asOfDate + ", `last_updated`=" + lastUpdatedDate
  566.                 + ", `last_trade_date`=" + lastTradeDate + "; \n");
  567.             for (; startRow <= sheet.getLastRowNum(); startRow++) {
  568.               row = sheet.getRow(startRow);
  569.               if (!isRowNullable(row)) {
  570.                 result.append(processTradeLogGroup(tradeLogId, prepareCellData(row.getCell(1 + offset)),
  571.                     prepareCellData(row.getCell(2 + offset)), prepareCellData(row.getCell(3 + offset)),
  572.                     prepareCellData(row.getCell(4 + offset))));
  573.               } else {
  574.                 break;
  575.               }
  576.             }
  577.           }
  578.         }
  579.         row = sheet.getRow(startRow++);
  580.       }
  581.       offset += 5;
  582.     }
  583.     return result;
  584.   }
  585.  
  586.   private StringBuffer processETPTradeLog(Sheet sheet) throws SQLException {
  587.     StringBuffer result = new StringBuffer();
  588.     result
  589.         .append("DROP TABLE IF EXISTS `trade_log_id_temp`; \n CREATE TABLE `trade_log_id_temp` (`trade_log_id` INT(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; \n insert into trade_log_id_temp (trade_log_id) select distinct tl.trade_log_id  from trade_log as tl join strategy as s on s.strategy_id=tl.strategy_id   join model_objective as mo on tl.model_objective_id=mo.model_objective_id  join model as m on m.model_id=tl.model_id join trade_log_assets as tla on tla.trade_log_id=tl.trade_log_id  join asset as a1 on a1.asset_id=tla.asset_id_previous join asset as a2 on a2.asset_id=tla.asset_id_current  join data_group as dg1 on dg1.data_group_id=m.data_group_id  join data_group as dg2 on dg2.data_group_id=s.platform_id  join model_direction as md on md.model_direction_id=m.model_direction_id  join model_product as mp on mp.model_product_id=m.model_product_id  where dg1.abbreviation='MWP'and dg2.abbreviation='MWP' and mp.model_product_code='ETP'  order by s.name,md.model_direction_name; \n delete from trade_log where trade_log_id in (select * from trade_log_id_temp); \n");
  590.     String lastUpdatedDate;
  591.     String lastTradeDate;
  592.     String modelObjectiveCode;
  593.     String modelObjectiveId;
  594.     String dataGroupId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = 'MWP'");
  595.     Row dateRow = sheet.getRow(0);
  596.     String asOfDate = dateRow!=null?prepareCellData(dateRow.getCell(3)):null;
  597.     int objectivesCount = Integer
  598.         .parseInt(getIdByName("select count(`model_objective_id`) as `id` from `model_objective`"));
  599.     int offset = 0;
  600.     for (int i = 0; i < objectivesCount; i++) {
  601.       int startRow = 0;
  602.       Row row = sheet.getRow(startRow);
  603.       while (startRow <= sheet.getLastRowNum()) {
  604.         if (!isRowNullable(row)
  605.             && ((lastTradeDate = prepareCellData(row.getCell(0))) != null || (prepareCellData(row.getCell(1 + offset))) != null
  606.                 && prepareCellData(row.getCell(2 + offset)) == null
  607.                 && !prepareCellData(row.getCell(1 + offset)).equalsIgnoreCase("'As of Date'"))) {
  608.           lastUpdatedDate = prepareCellData(sheet.getRow(startRow - 1).getCell(0));
  609.           modelObjectiveCode = prepareCellData(sheet.getRow(startRow - 1).getCell(1 + offset));
  610.           modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  611.               + modelObjectiveCode);
  612.           startRow++;
  613.           row = sheet.getRow(startRow);
  614.           if (isRowNullable(row)) {
  615.             row = sheet.getRow(startRow++);
  616.             continue;
  617.           }
  618.           String firstCol = getFirstCol(sheet, startRow, row);
  619.           String strategyName = firstCol.split(":")[0].trim();
  620.           String modelDirectionName = firstCol.split(":")[1].trim();
  621.           String strategyId = getIdByName("select `strategy_id` as `id` from `strategy` "
  622.               + "join data_group as dg1 on dg1.data_group_id=`strategy`.platform_id  where `strategy`.`name` = '"
  623.               + strategyName + "' AND dg1.abbreviation='MWP'");
  624.           if (strategyId == null) {
  625.             Set<String> tableProblems = problems.get(sheet.getSheetName().toLowerCase());
  626.             if (tableProblems == null) {
  627.               tableProblems = new HashSet<String>();
  628.               problems.put(sheet.getSheetName().toLowerCase(), tableProblems);
  629.             }
  630.             tableProblems.add("Can't find strategy:" + strategyName + " in DB");
  631.             row = sheet.getRow(startRow++);
  632.             continue;
  633.           }
  634.           String modelDirectionId = getIdByName("select `model_direction_id` as `id` from `model_direction` where `model_direction_name` = '"
  635.               + modelDirectionName + "'");
  636.           String modelId = getIdByName("select `model_id` as `id` from `model` "
  637.               + "join model_product as mp on mp.`model_product_id`=`model`.model_product_id where `strategy_id` = "
  638.               + strategyId + " AND `model_objective_id`=" + modelObjectiveId + " AND `data_group_id`=" + dataGroupId
  639.               + " AND `model_direction_id`=" + modelDirectionId + " AND mp.model_product_code='ETP'");
  640.           String tradeLogId = "(select `trade_log_id` as `id` from `trade_log` where `model_id`=" + modelId
  641.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `strategy_id`=" + strategyId + " LIMIT 1)";
  642.           if (modelId != null) {
  643.             result.append("INSERT INTO `trade_log` (`model_id`, `model_objective_id`, `strategy_id`, "
  644.                 + " `as_of_date`, `last_updated`, `last_trade_date`) VALUES (" + modelId + ", " + modelObjectiveId
  645.                 + ", " + strategyId + "," + asOfDate + ", " + lastUpdatedDate + ", " + lastTradeDate
  646.                 + ") ON DUPLICATE KEY UPDATE `as_of_date`=" + asOfDate + ", `last_updated`=" + lastUpdatedDate
  647.                 + ", `last_trade_date`=" + lastTradeDate + "; \n");
  648.             for (; startRow <= sheet.getLastRowNum(); startRow++) {
  649.               row = sheet.getRow(startRow);
  650.               if (!isRowNullable(row)) {
  651.                 result.append(processTradeLogGroup(tradeLogId, prepareCellData(row.getCell(1 + offset)),
  652.                     prepareCellData(row.getCell(2 + offset)), prepareCellData(row.getCell(3 + offset)),
  653.                     prepareCellData(row.getCell(4 + offset))));
  654.               } else {
  655.                 break;
  656.               }
  657.             }
  658.           }
  659.         }
  660.         row = sheet.getRow(startRow++);
  661.       }
  662.       offset += 5;
  663.     }
  664.     return result;
  665.   }
  666.  
  667.   private StringBuffer processPWPTradeLog(Sheet sheet) throws SQLException {
  668.     StringBuffer result = new StringBuffer();
  669.     result
  670.         .append("DROP TABLE IF EXISTS `trade_log_id_temp`; \n CREATE TABLE `trade_log_id_temp` (`trade_log_id` INT(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; \n insert into trade_log_id_temp (trade_log_id) select distinct tl.trade_log_id from trade_log as tl   join strategy as s on s.strategy_id=tl.strategy_id  join model_objective as mo on tl.model_objective_id=mo.model_objective_id join model as m on m.model_id=tl.model_id join trade_log_assets as tla on tla.trade_log_id=tl.trade_log_id join asset as a1 on a1.asset_id=tla.asset_id_previous join asset as a2 on a2.asset_id=tla.asset_id_current join tactical_focus as tf on tf.tactical_focus_id=tl.tactical_focus_id join data_group as dg1 on dg1.data_group_id=m.data_group_id join data_group as dg2 on dg2.data_group_id=s.platform_id where dg1.abbreviation='PWP'and dg2.abbreviation='PWP'  order by s.name, tf.tactical_focus_name; \n delete from trade_log where trade_log_id in (select * from trade_log_id_temp); \n");
  671.     String lastUpdatedDate;
  672.     String lastTradeDate;
  673.     String modelObjectiveCode;
  674.     String modelObjectiveId;
  675.     String dataGroupId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = 'PWP'");
  676.     Row dateRow = sheet.getRow(0);
  677.     String asOfDate = dateRow!=null?prepareCellData(dateRow.getCell(3)):null;
  678.     int objectivesCount = Integer
  679.         .parseInt(getIdByName("select count(`model_objective_id`) as `id` from `model_objective`"));
  680.     int offset = 0;
  681.     for (int i = 0; i < objectivesCount; i++) {
  682.       int startRow = 0;
  683.       Row row = sheet.getRow(startRow);
  684.       while (startRow <= sheet.getLastRowNum()) {
  685.         if (!isRowNullable(row)
  686.             && ((lastTradeDate = prepareCellData(row.getCell(0))) != null || (prepareCellData(row.getCell(1 + offset))) != null
  687.                 && prepareCellData(row.getCell(2 + offset)) == null
  688.                 && !prepareCellData(row.getCell(1 + offset)).equalsIgnoreCase("'As of Date'"))) {
  689.           lastUpdatedDate = prepareCellData(sheet.getRow(startRow - 1).getCell(0));
  690.           modelObjectiveCode = prepareCellData(sheet.getRow(startRow - 1).getCell(1 + offset));
  691.           modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  692.               + modelObjectiveCode);
  693.           startRow++;
  694.           row = sheet.getRow(startRow);
  695.           if (isRowNullable(row)) {
  696.             row = sheet.getRow(startRow++);
  697.             continue;
  698.           }
  699.           String firstCol = getFirstCol(sheet, startRow, row);
  700.           String strategyName = firstCol.split(":")[0].trim();
  701.           String tacticalFocusName = firstCol.split(":")[1].trim();
  702.           String strategyId = getIdByName("select `strategy_id` as `id` from `strategy` "
  703.               + "join data_group as dg1 on dg1.data_group_id=`strategy`.platform_id  where `strategy`.`name` = '"
  704.               + strategyName + "' AND dg1.abbreviation='PWP'");
  705.           if (strategyId == null) {
  706.             Set<String> tableProblems = problems.get(sheet.getSheetName().toLowerCase());
  707.             if (tableProblems == null) {
  708.               tableProblems = new HashSet<String>();
  709.               problems.put(sheet.getSheetName().toLowerCase(), tableProblems);
  710.             }
  711.             tableProblems.add("Can't find strategy:" + strategyName + " in DB");
  712.             row = sheet.getRow(startRow++);
  713.             continue;
  714.           }
  715.           String tacticalFocusId = "'"
  716.               + getIdByName("select `tactical_focus_id` as `id` from `tactical_focus` where `tactical_focus_name` = '"
  717.                   + tacticalFocusName + "'") + "'";
  718.           String modelId = getIdByName("select `model_id` as `id` from `model` where `strategy_id` = " + strategyId
  719.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `data_group_id`=" + dataGroupId);
  720.           String tradeLogId = "(select `trade_log_id` as `id` from `trade_log` where `model_id`=" + modelId
  721.               + " AND `model_objective_id`=" + modelObjectiveId + " AND `strategy_id`=" + strategyId
  722.               + " AND `tactical_focus_id`=" + tacticalFocusId + " LIMIT 1)";
  723.           if (modelId != null) {
  724.             result.append("INSERT INTO `trade_log` (`model_id`, `model_objective_id`, `strategy_id`, "
  725.                 + "`tactical_focus_id`, `as_of_date`, `last_updated`, `last_trade_date`) VALUES (" + modelId + ", "
  726.                 + modelObjectiveId + ", " + strategyId + "," + tacticalFocusId + ", " + asOfDate + ", "
  727.                 + lastUpdatedDate + ", " + lastTradeDate + ") ON DUPLICATE KEY UPDATE `as_of_date`=" + asOfDate
  728.                 + ", `last_updated`=" + lastUpdatedDate + ", `last_trade_date`=" + lastTradeDate + "; \n");
  729.             for (; startRow <= sheet.getLastRowNum(); startRow++) {
  730.               row = sheet.getRow(startRow);
  731.               if (!isRowNullable(row)) {
  732.                 result.append(processTradeLogGroup(tradeLogId, prepareCellData(row.getCell(1 + offset)),
  733.                     prepareCellData(row.getCell(2 + offset)), prepareCellData(row.getCell(3 + offset)),
  734.                     prepareCellData(row.getCell(4 + offset))));
  735.               } else {
  736.                 break;
  737.               }
  738.             }
  739.           }
  740.         }
  741.         row = sheet.getRow(startRow++);
  742.       }
  743.       offset += 5;
  744.     }
  745.     return result;
  746.   }
  747.  
  748.   private String processTradeLogGroup(String tradeLogId, String tickerOld, String tickerNew, String oldValue,
  749.       String newValue) throws SQLException {
  750.     String tickerOldId = getIdByName("select asset_id as `id` from asset where `ticker` = " + tickerOld);
  751.     String tickerNewId = getIdByName("select asset_id as `id` from asset where `ticker` = " + tickerNew);
  752.     return "INSERT INTO `trade_log_assets` (`trade_log_id`, `asset_id_previous`, `asset_id_current`, "
  753.         + " `weight_previous`, `weight_current`) VALUES (" + tradeLogId + ", " + tickerOldId + ", " + tickerNewId
  754.         + ", " + oldValue + ", " + newValue + "); \n";
  755.   }
  756.  
  757.   private StringBuffer processModelAllocationsPrevious(Sheet sheet) throws SQLException {
  758.     StringBuffer result = new StringBuffer();
  759.     int startRow;
  760.     if ("class_name".equalsIgnoreCase(sheet.getRow(1).getCell(0).getStringCellValue())) {
  761.       startRow = 2;
  762.     } else if ("class_name".equalsIgnoreCase(sheet.getRow(2).getCell(0).getStringCellValue())) {
  763.       startRow = 3;
  764.     } else {
  765.       startRow = 4;
  766.     }
  767.     Map<String, String> models = new HashMap<String, String>();
  768.     Map<String, String> dates = new HashMap<String, String>();
  769.     for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
  770.       Row row = sheet.getRow(i);
  771.       if (!isRowNullable(row)) {
  772.         String assetId = "(select asset_id as `id` from asset where `ticker` = " + prepareCellDataQuote(row.getCell(1))
  773.             + ")";
  774.         for (int col = 2; col <= row.getLastCellNum(); col++) {
  775.           String modelId;
  776.           String dateAsOf;
  777.           String modelName = prepareCellData(sheet.getRow(startRow - 1).getCell(col));
  778.           if (models.containsKey(modelName)) {
  779.             modelId = models.get(modelName);
  780.             dateAsOf = dates.get(modelName);
  781.           } else {
  782.             modelId = getIdByName("select `model_id` as `id` from `model` where `code` = " + modelName);
  783.             models.put(modelName, modelId);
  784.             dateAsOf = prepareCellData(sheet.getRow(startRow - 2).getCell(col));
  785.             dates.put(modelName, dateAsOf);
  786.           }
  787.           result.append(processModelAllocationPrevious(assetId, prepareCellData(row.getCell(col)), modelId, dateAsOf));
  788.         }
  789.       }
  790.     }
  791.     return result;
  792.   }
  793.  
  794.   private String processModelAllocationPrevious(String assetId, String value, String modelId, String dateAsOf)
  795.       throws SQLException {
  796.     if (modelId != null) {
  797.       String modelAssetId = getIdByName("select`model_asset_id` as `id` from `model_asset_previous` where `asset_id` = "
  798.           + assetId + " AND `model_id`=" + modelId);
  799.       if (value != null) {
  800.         if (modelAssetId == null) {
  801.           return "INSERT INTO `model_asset_previous` (`model_id`, `asset_id`, `weight`, `as_of_date`) VALUES ("
  802.               + modelId + ", " + assetId + ", " + value + ", " + dateAsOf + "); \n";
  803.         } else {
  804.           return "UPDATE `model_asset_previous` SET `weight`=" + value + ", `as_of_date`=" + dateAsOf
  805.               + " WHERE `model_asset_id`=" + modelAssetId + "; \n";
  806.         }
  807.       } else {
  808.         return "DELETE FROM `model_asset_previous` WHERE `model_asset_id`=" + modelAssetId + "; \n";
  809.       }
  810.     }
  811.     return "";
  812.   }
  813.  
  814.   private StringBuffer processModelAllocationsCurrent(Sheet sheet) throws SQLException {
  815.     StringBuffer result = new StringBuffer();
  816.     int startRow;
  817.     if ("class_name".equalsIgnoreCase(sheet.getRow(1).getCell(0).getStringCellValue())) {
  818.       startRow = 2;
  819.     } else if ("class_name".equalsIgnoreCase(sheet.getRow(2).getCell(0).getStringCellValue())) {
  820.       startRow = 3;
  821.     } else {
  822.       startRow = 4;
  823.     }
  824.     Map<String, String> models = new HashMap<String, String>();
  825.     Map<String, String> dates = new HashMap<String, String>();
  826.     for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
  827.       Row row = sheet.getRow(i);
  828.       if (!isRowNullable(row)) {
  829.         String assetId = "(select asset_id as `id` from asset where `ticker` = " + prepareCellDataQuote(row.getCell(1))
  830.             + ")";
  831.         for (int col = 2; col <= row.getLastCellNum(); col++) {
  832.           String modelId;
  833.           String dateAsOf;
  834.           String modelName = prepareCellData(sheet.getRow(startRow - 1).getCell(col));
  835.           if (models.containsKey(modelName)) {
  836.             modelId = models.get(modelName);
  837.             dateAsOf = dates.get(modelName);
  838.           } else {
  839.             modelId = getIdByName("select`model_id` as `id` from `model` where `code` = " + modelName);
  840.             models.put(modelName, modelId);
  841.             dateAsOf = prepareCellData(sheet.getRow(startRow - 2).getCell(col));
  842.             dates.put(modelName, dateAsOf);
  843.           }
  844.           result.append(processModelAllocationCurrent(assetId, prepareCellData(row.getCell(col)), modelId, dateAsOf));
  845.         }
  846.       }
  847.     }
  848.     return result;
  849.   }
  850.  
  851.   private String processModelAllocationCurrent(String assetId, String value, String modelId, String dateAsOf)
  852.       throws SQLException {
  853.     if (modelId != null) {
  854.       String modelAssetId = getIdByName("select`model_asset_id` as `id` from `model_asset` where `asset_id` = "
  855.           + assetId + " AND `model_id`=" + modelId);
  856.       if (value != null) {
  857.         if (modelAssetId == null) {
  858.           return "INSERT INTO `model_asset` (`model_id`, `asset_id`, `weight`, `as_of_date`) VALUES (" + modelId + ", "
  859.               + assetId + ", " + value + ", " + dateAsOf + "); \n";
  860.         } else {
  861.           return "UPDATE `model_asset` SET `weight`=" + value + ", `as_of_date`=" + dateAsOf
  862.               + " WHERE `model_asset_id`=" + modelAssetId + "; \n";
  863.         }
  864.       } else {
  865.         return "DELETE FROM `model_asset` WHERE `model_asset_id`=" + modelAssetId + "; \n";
  866.       }
  867.     }
  868.     return "";
  869.   }
  870.  
  871.   private StringBuffer processModelObjective(Sheet sheet) throws SQLException {
  872.     Set<String> codes = getCodes("SELECT `model_objective_code` as `key` FROM `model_objective`;");
  873.     StringBuffer result = new StringBuffer();
  874.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  875.       Row row = sheet.getRow(i);
  876.       if (!isRowNullable(row)) {
  877.         if (row.getCell(0) != null && codes.contains(row.getCell(0).getStringCellValue())) {
  878.           result.append("UPDATE `model_objective` SET `model_objective_name` = " + prepareCellData(row.getCell(1))
  879.               + ", `order` = " + prepareCellData(row.getCell(2)) + ", `description` = "
  880.               + prepareCellData(row.getCell(3)) + " WHERE `model_objective_code` = " + prepareCellData(row.getCell(0))
  881.               + "; \n");
  882.         } else {
  883.           result.append("INSERT INTO `model_objective` (`model_objective_code`, `model_objective_name`,`order`, "
  884.               + "`description`) VALUES (" + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1))
  885.               + "," + prepareCellData(row.getCell(2)) + "," + prepareCellData(row.getCell(3)) + "); \n");
  886.         }
  887.       }
  888.       String modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_code` = "
  889.           + prepareCellData(row.getCell(0)));
  890.       result.append(processModelObkectivePerc(modelObjectiveId, prepareCellData(row.getCell(4)), "STOCKS"));
  891.       result.append(processModelObkectivePerc(modelObjectiveId, prepareCellData(row.getCell(5)), "BONDS"));
  892.       result.append(processModelObkectivePerc(modelObjectiveId, prepareCellData(row.getCell(6)), "CASH"));
  893.     }
  894.     return result;
  895.   }
  896.  
  897.   private String processModelObkectivePerc(String modelObjectiveId, String percentage, String type) throws SQLException {
  898.     String ticker;
  899.     if ("STOCKS".equalsIgnoreCase(type)) {
  900.       ticker = "'IDX_40'";
  901.     } else if ("BONDS".equalsIgnoreCase(type)) {
  902.       ticker = "'IDX_18'";
  903.     } else {// CASH
  904.       ticker = "'IDX_31'";
  905.     }
  906.     String assetId = getIdByName("select asset_id as `id` from asset where `ticker` = " + ticker);
  907.     String objectiveBenchmarkId = getIdByName("select `objective_benchmark_id` as `id` from `objective_benchmark` where `asset_id` = "
  908.         + assetId + " AND `model_objective_id`=" + modelObjectiveId);
  909.     if (objectiveBenchmarkId == null) {
  910.       return "INSERT INTO `objective_benchmark` (`model_objective_id`,`asset_id`,`weighting`) VALUES " + "("
  911.           + modelObjectiveId + ", " + assetId + ", " + percentage + "); \n";
  912.     } else {
  913.       return "UPDATE `objective_benchmark` SET `weighting`=" + percentage + " where `asset_id` = " + assetId
  914.           + " AND `model_objective_id`=" + modelObjectiveId + "; \n";
  915.     }
  916.   }
  917.  
  918.   private StringBuffer processModels(Sheet sheet) throws SQLException {
  919.     Set<String> codes = getCodes("SELECT `code` as `key` FROM `model`;");
  920.     StringBuffer result = new StringBuffer();
  921.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  922.       Row row = sheet.getRow(i);
  923.       if (!isRowNullable(row)) {
  924.         String modelDirectionId = getIdByName("select `model_direction_id` as `id` from `model_direction` where `model_direction_name` = "
  925.             + prepareCellData(row.getCell(2)));
  926.         String annuityTypeId = getIdByName("select `annuity_type_id` as `id` from `annuity_type` where `annuity_type_name` = "
  927.             + prepareCellData(row.getCell(3)));
  928.         String modelProductId = getIdByName("select `model_product_id` as `id` from `model_product` where `model_product_code` = "
  929.             + prepareCellData(row.getCell(4)));
  930.         String policyId = getIdByName("select `policy_id` as `id` from `insurance_policy` where `insurance_policy_name` = "
  931.             + prepareCellData(row.getCell(5)));
  932.         String modelObjectiveId = getIdByName("select `model_objective_id` as `id` from `model_objective` where `model_objective_name` = "
  933.             + prepareCellData(row.getCell(7)));
  934.         String platformId = getIdByName("select `data_group_id` as `id` from `data_group` where `abbreviation` = "
  935.             + prepareCellData(row.getCell(8)));
  936.         String strategyId = getIdByName("select `strategy_id` as `id` from `strategy` where `name` = "
  937.             + prepareCellData(row.getCell(6)) + " AND `model_product_id`=" + modelProductId + " AND `platform_id`="
  938.             + platformId);
  939.         String key = prepareCellData(row.getCell(0));
  940.         if (key != null && key.startsWith("'") && key.endsWith("'")) {
  941.           key = key.substring(1, key.length() - 1);
  942.         }
  943.         if (key != null && codes.contains(key)) {
  944.           result.append("UPDATE `model` SET " + "`name` = " + prepareCellData(row.getCell(1)) + ", `"
  945.               + "model_direction_id`=" + modelDirectionId + ", `annuity_type_id` = " + annuityTypeId
  946.               + ",`model_product_id`=" + modelProductId + ",`policy_id`=" + policyId + ",`strategy_id`=" + strategyId
  947.               + ",`model_objective_id`=" + modelObjectiveId + ", `data_group_id`=" + platformId + ", `hidden` ="
  948.               + ("'YES'".equalsIgnoreCase(prepareCellData(row.getCell(9))) ? "0" : "1")
  949.               + ", `owner_id`=1, `creator_id`=1, `tracking_number`=" + prepareCellData(row.getCell(10))
  950.               + ", `res_number`=" + prepareCellData(row.getCell(11)) + ", `overview`="
  951.               + prepareCellData(row.getCell(12)) + "  WHERE `code` = " + prepareCellData(row.getCell(0)) + "; \n");
  952.         } else {
  953.           result.append("INSERT INTO `model` "
  954.               + "(`code`,`name`,`model_direction_id`, `annuity_type_id`, `model_product_id`, "
  955.               + "`policy_id`, `strategy_id`, `model_objective_id`, `hidden`, `data_group_id`, `creator_id`, "
  956.               + "`tracking_number`, `res_number`, `overview`, `owner_id`) VALUES ("
  957.               + prepareCellData(row.getCell(0))
  958.               + ","
  959.               + prepareCellData(row.getCell(1))
  960.               + ","
  961.               + modelDirectionId
  962.               + ", "
  963.               + annuityTypeId
  964.               + ", "
  965.               + modelProductId
  966.               + ", "
  967.               + policyId
  968.               + ", "
  969.               + strategyId
  970.               + ", "
  971.               + modelObjectiveId
  972.               + ", "
  973.               + ("'YES'".equalsIgnoreCase(prepareCellData(row.getCell(9))) ? "0" : "1")
  974.               + ", "
  975.               + platformId
  976.               + ", 1, "
  977.               + prepareCellData(row.getCell(10))
  978.               + ", "
  979.               + prepareCellData(row.getCell(11))
  980.               + ", "
  981.               + prepareCellData(row.getCell(12)) + ", 1); \n");
  982.         }
  983.       }
  984.     }
  985.     return result;
  986.   }
  987.  
  988.   private StringBuffer processAssets(Sheet sheet) throws SQLException {
  989.     // Set<String> codes = getCodes("SELECT `ticker` as `key` FROM `asset`;");
  990.     StringBuffer result = new StringBuffer();
  991.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  992.       Row row = sheet.getRow(i);
  993.       if (!isRowNullable(row)) {
  994.         String key = prepareCellData(row.getCell(0));
  995.         if (key != null && key.startsWith("'") && key.endsWith("'")) {
  996.           key = key.substring(1, key.length() - 1);
  997.         }
  998.         result.append("INSERT INTO `asset` " + "(`ticker`,`name`,`extern_key`, `class`, `hidden`, "
  999.             + "`data_group_id`, `extern_src`, `owner`) VALUES (" + prepareCellData(row.getCell(0)) + ","
  1000.             + prepareCellData(row.getCell(1)) + "," + prepareCellData(row.getCell(0)) + ","
  1001.             + prepareCellData(row.getCell(2)) + ", "
  1002.             + ("'YES'".equalsIgnoreCase(prepareCellData(row.getCell(5))) ? "0" : "1") + ", 1,'LPL', 1)"
  1003.             + " ON DUPLICATE KEY UPDATE `name` = " + prepareCellData(row.getCell(1)) + ", `" + "class`="
  1004.             + prepareCellData(row.getCell(2)) + ", `extern_key` = " + prepareCellData(row.getCell(0)) + ", `hidden` ="
  1005.             + ("'YES'".equalsIgnoreCase(prepareCellData(row.getCell(5))) ? "0" : "1")
  1006.             + ", `owner`=1,`data_group_id`=1, `extern_src`='LPL', `owner`=1; \n");
  1007.         String assetId = "(select `asset_id` as `id` from `asset` where `ticker` = "
  1008.             + prepareCellDataQuote(row.getCell(0)) + ")";
  1009.         result.append(processAssetDisclosures(assetId, prepareCellData(row.getCell(22))));
  1010.         result.append(processAssetPolicies(assetId, prepareCellData(row.getCell(7))));
  1011.         result.append(processAssetClass(assetId, prepareCellData(row.getCell(3)), prepareCellData(row.getCell(4))));
  1012.         result.append(processAssetRiskClassification(assetId, prepareCellData(row.getCell(5))));
  1013.         result.append(processAssetOption(assetId, prepareCellData(row.getCell(8))));
  1014.         result.append(processAssetStatistics(assetId, prepareCellData(row.getCell(9)),
  1015.             prepareCellData(row.getCell(10)), prepareCellData(row.getCell(11)), prepareCellData(row.getCell(12)),
  1016.             prepareCellData(row.getCell(13)), prepareCellData(row.getCell(14)), prepareCellData(row.getCell(15)),
  1017.             prepareCellData(row.getCell(16)), prepareCellData(row.getCell(17)), prepareCellData(row.getCell(18)),
  1018.             prepareCellData(row.getCell(19)), prepareCellData(row.getCell(20)), prepareCellData(row.getCell(21))));
  1019.         result.append(processAssetPlatformAvailability(assetId, prepareCellData(row.getCell(23)), "PWP"));
  1020.         result.append(processAssetPlatformAvailability(assetId, prepareCellData(row.getCell(24)), "SAM"));
  1021.         result.append(processAssetPlatformAvailability(assetId, prepareCellData(row.getCell(25)), "MWP"));
  1022.         result.append(processAssetPlatformAvailability(assetId, prepareCellData(row.getCell(26)), "OMP"));
  1023.         result.append(processAssetPlatformAvailability(assetId, prepareCellData(row.getCell(27)), "MS"));
  1024.         result.append(processAssetPlatformAvailability(assetId, prepareCellData(row.getCell(28)), "SWM"));
  1025.       }
  1026.     }
  1027.     return result;
  1028.   }
  1029.  
  1030.   private StringBuffer processAssetPlatformAvailability(String assetId, String yesNo, String platform)
  1031.       throws SQLException {
  1032.     String dataGroupOptionId = getIdByName("select data_group_id as `id` from `data_group` where `abbreviation`='"
  1033.         + platform + "';");
  1034.     StringBuffer buffer = new StringBuffer();
  1035.     if (dataGroupOptionId != null && assetId != null) {
  1036.       buffer.append("DELETE FROM `asset_platform_availability` WHERE `asset_id`=" + assetId
  1037.           + " AND `data_group_option_id`=" + dataGroupOptionId + "; \n");
  1038.       if (yesNo != null) {
  1039.         if ("'Y'".equalsIgnoreCase(yesNo)) {
  1040.           buffer.append("INSERT INTO `asset_platform_availability` (`asset_id`, `data_group_option_id`) " + "VALUES ("
  1041.               + assetId + ", " + dataGroupOptionId + "); \n");
  1042.         }
  1043.       }
  1044.     }
  1045.     return buffer;
  1046.   }
  1047.  
  1048.   private String processAssetOption(String assetId, String assetOption) throws SQLException {
  1049.     if (assetId != null && assetOption != null) {
  1050.       String oldAssetId = getIdByName("select asset_id as `id` from `asset_option` where `asset_id`=" + assetId);
  1051.       if (oldAssetId == null) {
  1052.         return "INSERT INTO `asset_option` (`asset_id`, `min_value_non_sac`) VALUES (" + assetId + ", " + assetOption
  1053.             + "); \n";
  1054.       } else {
  1055.         return "UPDATE `asset_option` SET `min_value_non_sac`=" + assetOption + " WHERE `asset_id`=" + assetId + "; \n";
  1056.       }
  1057.     }
  1058.     return "";
  1059.   }
  1060.  
  1061.   private StringBuffer processAssetClass(String assetId, String classNameDetailed, String className)
  1062.       throws SQLException {
  1063.     StringBuffer buffer = new StringBuffer("DELETE FROM `asset_classification` WHERE `security_id`=" + assetId + "; \n");
  1064.     if ((classNameDetailed != null || className != null) && assetId != null) {
  1065.       String asseClasstId = getIdByName("select asset_id as `id` from `asset` where `name`=" + className);
  1066.       String classNameDetailedId = getIdByName("select asset_id as `id` from `asset` where `name`=" + classNameDetailed);
  1067.       if (asseClasstId != null || classNameDetailedId != null) {
  1068.         buffer
  1069.             .append("INSERT INTO `asset_classification` (`security_id`, `asset_class_id`, `asset_class_detailed_id`, `owner`) VALUES ("
  1070.                 + assetId
  1071.                 + ", "
  1072.                 + (asseClasstId != null ? asseClasstId : classNameDetailedId)
  1073.                 + ", "
  1074.                 + (classNameDetailedId != null ? classNameDetailedId : asseClasstId) + ", 1); \n");
  1075.       }
  1076.     }
  1077.     return buffer;
  1078.   }
  1079.  
  1080.   private StringBuffer processAssetRiskClassification(String assetId, String riskClassificationName)
  1081.       throws SQLException {
  1082.     StringBuffer buffer = new StringBuffer("DELETE FROM `asset_risk_classification` WHERE `asset_id`=" + assetId
  1083.         + "; \n");
  1084.     if (riskClassificationName != null && assetId != null) {
  1085.       String riskId = getIdByName("select `risk_classification_id` as `id` from `risk_classification` where `classification_name`="
  1086.           + riskClassificationName);
  1087.       if (riskId != null) {
  1088.         buffer.append("INSERT INTO `asset_risk_classification` (`asset_id`, `risk_classification_id`) VALUES ("
  1089.             + assetId + ", " + riskId + "); \n");
  1090.       }
  1091.     }
  1092.     return buffer;
  1093.   }
  1094.  
  1095.   private StringBuffer processAssetClasses(Sheet sheet) throws SQLException {
  1096.     StringBuffer result = new StringBuffer();
  1097.     String parentId = "(SELECT `asset_id_temp`.`asset_id` FROM `asset_id_temp` LIMIT 1, 1)";
  1098.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1099.       Row row = sheet.getRow(i);
  1100.       if (!isRowNullable(row)) {
  1101.         String proxyId = getIdByName("select asset_id as `id` from asset where ticker="
  1102.             + prepareCellDataQuote(row.getCell(3)) + ";");
  1103.         String assetTypeId = getIdByName("select asset_type_id as `id` from asset_type where name="
  1104.             + prepareCellData(row.getCell(4)) + ";");
  1105.         String assetTierId = getIdByName("select asset_tier_id as `id` from asset_tier where name="
  1106.             + prepareCellData(row.getCell(5)) + ";");
  1107.         if (row.getCell(0) != null) {
  1108.             result.append("INSERT INTO `asset` "
  1109.                     + "(`ticker`,`name`,`extern_key`, `order_no`, `hidden`, `owner`, `proxy_id`, "
  1110.                     + "`asset_type_id`, `asset_tier_id`, `data_group_id`, `extern_src`, `class`, "
  1111.                     + "`parent`, `color_1`, `color_2`) VALUES ("
  1112.                     + prepareCellData(row.getCell(0))
  1113.                     + ","
  1114.                     + prepareCellData(row.getCell(1))
  1115.                     + ","
  1116.                     + prepareCellData(row.getCell(0))
  1117.                     + ","
  1118.                     + prepareCellData(row.getCell(2))
  1119.                     + ", "
  1120.                     + ("'YES'".equalsIgnoreCase(prepareCellData(row.getCell(6))) ? "0" : "1")
  1121.                     + ", 1, "
  1122.                     + proxyId
  1123.                     + ", "
  1124.                     + assetTypeId
  1125.                     + ", "
  1126.                     + assetTierId
  1127.                     + ", 1,'LPL','AC', "
  1128.                     + parentId
  1129.                     + ", "
  1130.                     + prepareCellData(row.getCell(7)) + "," + prepareCellData(row.getCell(8)) + ") ON DUPLICATE KEY UPDATE `name` = " + prepareCellData(row.getCell(1)) + ", `order_no`="
  1131.               + prepareCellData(row.getCell(2)) + ", `proxy_id`=" + proxyId + ", `asset_type_id`=" + assetTypeId
  1132.               + ", `asset_tier_id`=" + assetTierId + ", `extern_key` = " + prepareCellData(row.getCell(0))
  1133.               + ", `hidden` =" + ("'YES'".equalsIgnoreCase(prepareCellData(row.getCell(6))) ? "0" : "1")
  1134.               + ", `owner`=1,`data_group_id`=1, `extern_src`='LPL', `class`='AC', `parent`=" + parentId
  1135.               + ", `color_1`=" + prepareCellData(row.getCell(7)) + ", `color_2`=" + prepareCellData(row.getCell(8))
  1136.               + "; \n");
  1137.         } else {
  1138.         }
  1139.         String assetId = "(select asset_id as `id` from asset where `ticker` = " + prepareCellDataQuote(row.getCell(0))
  1140.             + ")";
  1141.         result.append(processAssetDisclosures(assetId, prepareCellData(row.getCell(9))));
  1142.       }
  1143.     }
  1144.     return result;
  1145.   }
  1146.  
  1147.   private StringBuffer processAssetDisclosures(String assetId, String disclosures) throws SQLException {
  1148.     StringBuffer buffer = new StringBuffer("DELETE FROM `asset_disclosure` WHERE `asset_id`=" + assetId + "; \n");
  1149.     if (disclosures != null && assetId != null) {
  1150.       disclosures = disclosures.substring(1, disclosures.length() - 1).replaceAll("\\|", ",");
  1151.       for (String disclosureId : disclosures.split(",")) {
  1152.         if (!"".equals(disclosureId)
  1153.             && getIdByName("select `disclosure_code` as `id` from `disclosure` where `disclosure_code`='"
  1154.                 + disclosureId + "'") != null) {
  1155.           buffer.append("INSERT INTO `asset_disclosure` (`asset_id`, `disclosure_code`) VALUES (" + assetId + ", '"
  1156.               + disclosureId + "'); \n");
  1157.         }
  1158.       }
  1159.     }
  1160.     return buffer;
  1161.   }
  1162.  
  1163.   private StringBuffer processAssetPolicies(String assetId, String policies) throws SQLException {
  1164.     StringBuffer buffer = new StringBuffer("DELETE FROM `asset_policy` WHERE `asset_id`=" + assetId + "; \n");
  1165.     if (policies != null && assetId != null) {
  1166.       policies = policies.substring(1, policies.length() - 1).replaceAll("\\|", ",");
  1167.       for (String policyName : policies.split(",")) {
  1168.         if (!"".equals(policyName)) {
  1169.           String policyId = getIdByName("select `policy_id` as `id` from `insurance_policy` where `insurance_policy_code`='"
  1170.               + policyName + "';");
  1171.           if (policyId != null) {
  1172.             buffer.append("INSERT INTO `asset_policy` (`asset_id`, `policy_id`) VALUES (" + assetId + ", " + policyId
  1173.                 + "); \n");
  1174.           }
  1175.         }
  1176.       }
  1177.     }
  1178.     return buffer;
  1179.   }
  1180.  
  1181.   private StringBuffer processIndices(Sheet sheet) throws SQLException {
  1182.     Set<String> codes = getCodes("SELECT `ticker` as `key` FROM `asset`;");
  1183.  
  1184.     StringBuffer result = new StringBuffer();
  1185.     result.append(createAssetIdTemp());
  1186.     String parentId = "(SELECT `asset_id_temp`.`asset_id` FROM `asset_id_temp` LIMIT 0, 1)";
  1187.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1188.       Row row = sheet.getRow(i);
  1189.       if (!isRowNullable(row)) {
  1190.         if (row.getCell(0) != null && codes.contains(row.getCell(0).getStringCellValue())) {
  1191.           result.append("UPDATE `asset` SET `name` = " + prepareCellData(row.getCell(1)) + ", `extern_key` = "
  1192.               + prepareCellData(row.getCell(0))
  1193.               + ", `hidden` =1, `owner`=1,`data_group_id`=1, `extern_src`='LPL', `class`='AC', `parent`=" + parentId
  1194.               + " WHERE `ticker` = " + prepareCellDataQuote(row.getCell(0)) + "; \n");
  1195.         } else {
  1196.           result.append("INSERT INTO `asset` " + "(`ticker`,`name`,`extern_key`, "
  1197.               + "`hidden`, `owner`, `data_group_id`, `extern_src`, `class`,`parent`) VALUES ("
  1198.               + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + ","
  1199.               + prepareCellData(row.getCell(0)) + ", 1, 1, 1,'LPL','AC'," + parentId + "); \n");
  1200.         }
  1201.         String assetId = "(select asset_id as `id` from asset where `ticker` = " + prepareCellDataQuote(row.getCell(0))
  1202.             + ")";
  1203.  
  1204.         if ("'INDEX_DEFINITION'".equalsIgnoreCase(prepareCellData(sheet.getRow(0).getCell(9)))) {
  1205.           result.append(processAssetDefinition(assetId, prepareCellData(row.getCell(9))));
  1206.         } else {
  1207.           result.append(processAssetDefinition(assetId, prepareCellData(row.getCell(15))));
  1208.         }
  1209.  
  1210.         result.append(processAssetStatistics(assetId, prepareCellData(row.getCell(2)), prepareCellData(row.getCell(3)),
  1211.             prepareCellData(row.getCell(4)), prepareCellData(row.getCell(5)), prepareCellData(row.getCell(6)),
  1212.             prepareCellData(row.getCell(7)), prepareCellData(row.getCell(8)), prepareCellData(row.getCell(9)),
  1213.             prepareCellData(row.getCell(10)), prepareCellData(row.getCell(11)), prepareCellData(row.getCell(12)),
  1214.             prepareCellData(row.getCell(13)), prepareCellData(row.getCell(14))));
  1215.       }
  1216.     }
  1217.     return result;
  1218.   }
  1219.  
  1220.   private StringBuffer createAssetIdTemp() throws SQLException {
  1221.     StringBuffer result = new StringBuffer();
  1222.     String parentId = getIdByName("select asset_id as `id` from asset where name='INDEX_ROOT';");
  1223.     if (parentId == null) {
  1224.       result.append("INSERT INTO `asset` (`parent`, `owner`, `class`, `asset_type_id`, "
  1225.           + "`asset_tier_id`, `taxable`, `industry_id`, `asset_category_id`, `order_no`, "
  1226.           + "`hidden`, `name`, `proxy_id`, `currency_id`, `ticker`, `cusip`, `isin`, `sedol`, "
  1227.           + "`start_date`, `end_date`, `yield`, `extern_src`, `extern_key`, `axis`, `data_group_id`, "
  1228.           + "`nav`, `nav_as_of_date`, `asset_class_group_id`, `sei_discount_eligible`, `color_1`, `color_2`) "
  1229.           + "VALUES (null, 1, 'FOLDER',null,null,1,null,null,0,1,'INDEX_ROOT',null,1,'INDEXROOT',null, "
  1230.           + "null,null,null,null,null,'LPL','LPL_INDEX_ROOT',null,1,null,null,null,0,null,null); \n");
  1231.     }
  1232.     parentId = getIdByName("select asset_id as `id` from asset where name='ASSET_CLASS_ROOT';");
  1233.     if (parentId == null) {
  1234.       result.append("INSERT INTO `asset` (`parent`, `owner`, `class`, `asset_type_id`, `asset_tier_id`, "
  1235.           + "`taxable`, `industry_id`, `asset_category_id`, `order_no`, `hidden`, `name`, `proxy_id`, "
  1236.           + "`currency_id`, `ticker`, `cusip`, `isin`, `sedol`, `start_date`, `end_date`, `yield`, "
  1237.           + "`extern_src`, `extern_key`, `axis`, `data_group_id`, `nav`, `nav_as_of_date`, "
  1238.           + "`asset_class_group_id`, `sei_discount_eligible`, `color_1`, `color_2`) "
  1239.           + "VALUES (null, 1, 'FOLDER',null,null,1,null,null,0,1,'ASSET_CLASS_ROOT',null,1,'ASSETCROOT', "
  1240.           + "null,null,null,null,null,null,'LPL','LPL_ASSET_CLASS_ROOT',null,1,null,null,null,0,null,null); \n");
  1241.     }
  1242.     result.append("DROP TABLE IF EXISTS `asset_id_temp`; \n" + "CREATE TABLE `asset_id_temp` (`asset_id` INT(10)) "
  1243.         + "ENGINE=InnoDB DEFAULT CHARSET=utf8; \n" + "INSERT INTO `asset_id_temp` VALUES ("
  1244.         + "(SELECT `asset`.`asset_id` FROM `asset` WHERE `asset`.`extern_key` = 'LPL_INDEX_ROOT')), "
  1245.         + "((SELECT `asset`.`asset_id` FROM `asset` WHERE `asset`.`extern_key` = 'LPL_ASSET_CLASS_ROOT')); \n");
  1246.     return result;
  1247.   }
  1248.  
  1249.   private StringBuffer deleteAssetIdTemp() {
  1250.     StringBuffer result = new StringBuffer();
  1251.     result.append("DROP TABLE IF EXISTS `asset_id_temp`; \n");
  1252.     return result;
  1253.   }
  1254.  
  1255.   private String prepareCellDataQuote(Cell cell) {
  1256.     String prepareCellData = prepareCellData(cell);
  1257.     return prepareCellData != null ? prepareCellData.startsWith("'") ? prepareCellData : "'" + prepareCellData + "'"
  1258.         : null;
  1259.   }
  1260.  
  1261.   private Object processAssetStatistics(String assetId, String oneYearReturn, String threYearReturn,
  1262.       String fiveYearReturn, String tenYearReturn, String sinceInceptionReturn, String standardDeviation, String alpha,
  1263.       String beta, String r2, String sharpe, String upCapture, String downCapture, String dataAsOf) throws SQLException {
  1264.     if ((oneYearReturn != null || threYearReturn != null || fiveYearReturn != null || tenYearReturn != null
  1265.         || sinceInceptionReturn != null || standardDeviation != null || alpha != null || beta != null || r2 != null
  1266.         || sharpe != null || upCapture != null || downCapture != null || dataAsOf != null)
  1267.         && assetId != null) {
  1268.       String assetStatisticsId = getIdByName("select asset_id as `id` from asset_statistics WHERE `asset_id`="
  1269.           + assetId + ";");
  1270.       if (assetStatisticsId == null) {
  1271.         return "INSERT INTO `asset_statistics` (`asset_id`, `1_year_return`, `3_year_return`, `5_year_return`, "
  1272.             + "`10_year_return`, `since_inception_return`, `standard_deviation`, `alpha`, `beta`, `r2`, "
  1273.             + "`sharpe`, " + "`up_capture`, `down_capture`, `data_as_of`) VALUES ("
  1274.             + assetId
  1275.             + ", "
  1276.             + oneYearReturn
  1277.             + ", "
  1278.             + threYearReturn
  1279.             + ", "
  1280.             + fiveYearReturn
  1281.             + ", "
  1282.             + tenYearReturn
  1283.             + ", "
  1284.             + sinceInceptionReturn
  1285.             + ", "
  1286.             + standardDeviation
  1287.             + ", "
  1288.             + alpha
  1289.             + ", "
  1290.             + beta
  1291.             + ", "
  1292.             + r2
  1293.             + ", "
  1294.             + sharpe + ", " + upCapture + ", " + downCapture + ", " + dataAsOf + "); \n";
  1295.       } else {
  1296.         return "UPDATE `asset_statistics` SET `1_year_return`=" + oneYearReturn + ", `3_year_return`=" + threYearReturn
  1297.             + ", `5_year_return`=" + fiveYearReturn + ", " + "`10_year_return`=" + tenYearReturn
  1298.             + ", `since_inception_return`=" + sinceInceptionReturn + ", `standard_deviation`=" + standardDeviation
  1299.             + ",`alpha`=" + alpha + ", `beta`=" + beta + ", `r2`=" + r2 + ", `sharpe`=" + sharpe + ", `up_capture`="
  1300.             + upCapture + ", `down_capture`=" + downCapture + ", `data_as_of`=" + dataAsOf + " WHERE `asset_id`="
  1301.             + assetId + "; \n";
  1302.       }
  1303.     }
  1304.     return "DELETE FROM `asset_statistics` WHERE `asset_id`=" + assetId + "; \n";
  1305.   }
  1306.  
  1307.   private String processAssetDefinition(String assetId, String definition) throws SQLException {
  1308.     if (definition == null) {
  1309.       return "DELETE FROM `asset_definition` WHERE `asset_id`=" + assetId + "; \n";
  1310.     } else {
  1311.       String assetDefinitionId = getIdByName("select asset_id as `id` from asset_definition WHERE `asset_id`="
  1312.           + assetId + ";");
  1313.       if (assetDefinitionId == null) {
  1314.         return "INSERT INTO `asset_definition` (`asset_id`, `definition`) VALUES (" + assetId + ", " + definition
  1315.             + "); \n";
  1316.       } else {
  1317.         return "UPDATE `asset_definition` SET `definition`=" + definition + " WHERE `asset_id`=" + assetId + "; \n";
  1318.       }
  1319.     }
  1320.   }
  1321.  
  1322.   private StringBuffer processModelType(Sheet sheet) throws SQLException {
  1323.     Set<String> codes = getCodes("SELECT `code` as `key` FROM `strategy`;");
  1324.     StringBuffer result = new StringBuffer();
  1325.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1326.       Row row = sheet.getRow(i);
  1327.       if (!isRowNullable(row)) {
  1328.         String platformName = row.getCell(5) == null ? null : row.getCell(5).getStringCellValue();
  1329.         String platformId = getIdByName("SELECT `data_group_id` as `id` FROM `data_group` WHERE `abbreviation`='"
  1330.             + StringEscapeUtils.escapeSql(platformName) + "';");
  1331.         String modelName = row.getCell(6) == null ? null : row.getCell(6).getStringCellValue();
  1332.         String modelId = getIdByName("SELECT `model_product_id` as `id` FROM `model_product` WHERE `model_product_name`='"
  1333.             + StringEscapeUtils.escapeSql(modelName) + "';");
  1334.         if (row.getCell(0) != null && codes.contains(row.getCell(0).getStringCellValue())) {
  1335.           result.append("UPDATE `strategy` SET `name` = " + prepareCellData(row.getCell(1))
  1336.               + ", `strategy_disclosure` = " + prepareCellData(row.getCell(2)) + ", `strategy_highlights` = "
  1337.               + prepareCellData(row.getCell(3)) + ", `strategy_trade_log_focus` = " + prepareCellData(row.getCell(4))
  1338.               + ", `model_product_id` = " + modelId + ", `platform_id` = " + platformId + " WHERE `code` = "
  1339.               + prepareCellData(row.getCell(0)) + "; \n");
  1340.         } else {
  1341.           result.append("INSERT INTO `strategy` " + "(`code`,`name`,`strategy_disclosure`, "
  1342.               + "`strategy_highlights`, `strategy_trade_log_focus`, `model_product_id`, `platform_id`) VALUES ("
  1343.               + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + ","
  1344.               + prepareCellData(row.getCell(2)) + "," + prepareCellData(row.getCell(3)) + ","
  1345.               + prepareCellData(row.getCell(4)) + "," + modelId + "," + platformId + "); \n");
  1346.         }
  1347.       }
  1348.     }
  1349.     return result;
  1350.   }
  1351.  
  1352.   private StringBuffer processManagerGuideCover(Sheet sheet) throws SQLException {
  1353.     Set<String> codes = getCodes("SELECT data_group.abbreviation as `key` " + "FROM manager_guide_cover_text "
  1354.         + "join data_group on manager_guide_cover_text.data_group_id=data_group.data_group_id; ");
  1355.     StringBuffer result = new StringBuffer();
  1356.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1357.       Row row = sheet.getRow(i);
  1358.       if (!isRowNullable(row)) {
  1359.         String name = row.getCell(0) == null ? null : row.getCell(0).getStringCellValue();
  1360.         String id = getIdByName("SELECT `data_group_id` as `id` FROM `data_group` WHERE `abbreviation`='"
  1361.             + StringEscapeUtils.escapeSql(name) + "';");
  1362.         if (row.getCell(0) != null && codes.contains(row.getCell(0).getStringCellValue())) {
  1363.           result.append("UPDATE `manager_guide_cover_text` SET `description` = " + prepareCellData(row.getCell(1))
  1364.               + ", `data_group_id` = " + id + ", `companion_bullet1` = " + prepareCellData(row.getCell(2))
  1365.               + ", `companion_bullet2` = " + prepareCellData(row.getCell(3)) + ", `companion_bullet3` = "
  1366.               + prepareCellData(row.getCell(4)) + ", `companion_bullet4` = " + prepareCellData(row.getCell(5))
  1367.               + " WHERE `data_group_id` = " + id + "; \n");
  1368.         } else {
  1369.           result.append("INSERT INTO `manager_guide_cover_text` "
  1370.               + "(`description`,`companion_bullet1`,`data_group_id`, "
  1371.               + "`companion_bullet2`, `companion_bullet3`, `companion_bullet4`) VALUES ("
  1372.               + prepareCellData(row.getCell(1)) + "," + prepareCellData(row.getCell(2)) + "," + id + ","
  1373.               + prepareCellData(row.getCell(3)) + "," + prepareCellData(row.getCell(4)) + ","
  1374.               + prepareCellData(row.getCell(5)) + "); \n");
  1375.         }
  1376.       }
  1377.     }
  1378.     return result;
  1379.   }
  1380.  
  1381.   private StringBuffer processDisclosureType(Sheet sheet) throws SQLException {
  1382.     Set<String> codes = getCodes("SELECT distinct `disclosure_type_name` as `key` FROM `disclosure_type`;");
  1383.     StringBuffer result = new StringBuffer();
  1384.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1385.       Row row = sheet.getRow(i);
  1386.       if (!isRowNullable(row)) {
  1387.         if (prepareCellData(row.getCell(3)) != null && !codes.contains(row.getCell(3).getStringCellValue())) {
  1388.           result.append("INSERT INTO `disclosure_type` " + "(`disclosure_type_name`) VALUES ("
  1389.               + prepareCellData(row.getCell(3)) + "); \n");
  1390.         }
  1391.       }
  1392.     }
  1393.     return result;
  1394.   }
  1395.  
  1396.   private StringBuffer processDisclosure(Sheet sheet) throws SQLException {
  1397.     StringBuffer result = new StringBuffer();
  1398.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1399.       Row row = sheet.getRow(i);
  1400.       if (!isRowNullable(row)) {
  1401.         String name = row.getCell(2) == null ? null : (row.getCell(3) == null ? null : row.getCell(3)
  1402.             .getStringCellValue());
  1403.         String id = getIdByName("SELECT `disclosure_type_id` as `id` FROM `disclosure_type` WHERE `disclosure_type_name`='"
  1404.             + StringEscapeUtils.escapeSql(name) + "';");
  1405.         result.append("INSERT INTO `disclosure` " + "(`disclosure_code`,`disclosure`,`disclosure_name`, "
  1406.             + "`disclosure_type_id`, `disclosure_order`) VALUES (" + prepareCellData(row.getCell(0)) + ","
  1407.             + prepareCellData(row.getCell(1)) + "," + prepareCellData(row.getCell(2)) + "," + id + ","
  1408.             + prepareCellData(row.getCell(4)) + ") ON DUPLICATE KEY UPDATE `disclosure` = "
  1409.             + prepareCellData(row.getCell(1)) + ", `disclosure_type_id` = " + id + ", `disclosure_name` = "
  1410.             + prepareCellData(row.getCell(2)) + ", `disclosure_order` = " + prepareCellData(row.getCell(4)) + "; \n");
  1411.       }
  1412.     }
  1413.     return result;
  1414.   }
  1415.  
  1416.   private StringBuffer processModelTacticalFocus(Sheet sheet) throws SQLException {
  1417.     Set<String> codes = getCodes("SELECT `tactical_focus_id` as `key` FROM `tactical_focus`;");
  1418.     StringBuffer result = new StringBuffer();
  1419.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1420.       Row row = sheet.getRow(i);
  1421.       if (!isRowNullable(row)) {
  1422.         String strategyName = row.getCell(2) == null ? null : row.getCell(2).getStringCellValue();
  1423.         String strategyId = getIdByName("SELECT `strategy_id` as `id` FROM `strategy` WHERE `name`='"
  1424.             + StringEscapeUtils.escapeSql(strategyName) + "';");
  1425.         if (row.getCell(0) != null && codes.contains(row.getCell(0).getStringCellValue())) {
  1426.           result.append("UPDATE `tactical_focus` SET `tactical_focus_name` = " + prepareCellData(row.getCell(1))
  1427.               + ", `strategy_id` = " + strategyId + ", `focus_order` = " + prepareCellData(row.getCell(3))
  1428.               + ", `definition` = " + prepareCellData(row.getCell(4)) + " WHERE `tactical_focus_id` = "
  1429.               + prepareCellData(row.getCell(0)) + "; \n");
  1430.         } else {
  1431.           result.append("INSERT INTO `tactical_focus` " + "(`tactical_focus_id`,`tactical_focus_name`,`strategy_id`, "
  1432.               + "`focus_order`, `definition`) VALUES (" + prepareCellData(row.getCell(0)) + ","
  1433.               + prepareCellData(row.getCell(1)) + "," + strategyId + "," + prepareCellData(row.getCell(3)) + ","
  1434.               + prepareCellData(row.getCell(4)) + "); \n");
  1435.         }
  1436.       }
  1437.     }
  1438.     return result;
  1439.   }
  1440.  
  1441.   private StringBuffer processManagerRiskClassification(Sheet sheet) throws SQLException {
  1442.     Set<String> codes = getCodes("SELECT `risk_classification_id` as `key` FROM `risk_classification`;");
  1443.     StringBuffer result = new StringBuffer();
  1444.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1445.       Row row = sheet.getRow(i);
  1446.       if (!isRowNullable(row)) {
  1447.         if (codes.contains(prepareCellData(row.getCell(0)))) {
  1448.           result.append("UPDATE `risk_classification` SET `classification_name` = " + prepareCellData(row.getCell(1))
  1449.               + ", `classification_code` = " + prepareCellData(row.getCell(2)) + ", `order` = "
  1450.               + prepareCellData(row.getCell(3)) + ", `risk_classification_description` = "
  1451.               + prepareCellData(row.getCell(4)) + " WHERE `risk_classification_id` = "
  1452.               + prepareCellData(row.getCell(0)) + "; \n");
  1453.         } else {
  1454.           result.append("INSERT INTO `risk_classification` "
  1455.               + "(`risk_classification_id`,`classification_name`,`classification_code`, `order`, "
  1456.               + "`risk_classification_description`) VALUES (" + prepareCellData(row.getCell(0)) + ","
  1457.               + prepareCellData(row.getCell(1)) + "," + prepareCellData(row.getCell(2)) + ","
  1458.               + prepareCellData(row.getCell(3)) + "," + prepareCellData(row.getCell(4)) + "); \n");
  1459.         }
  1460.       }
  1461.     }
  1462.     return result;
  1463.   }
  1464.  
  1465.   private StringBuffer processPlatform(Sheet sheet) throws SQLException {
  1466.     Set<String> codes = getCodes("SELECT `code` as `key` FROM `data_group`;");
  1467.     StringBuffer result = new StringBuffer();
  1468.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1469.       Row row = sheet.getRow(i);
  1470.       if (!isRowNullable(row)) {
  1471.         if (codes.contains(row.getCell(0).getStringCellValue())) {
  1472.           result.append("UPDATE `data_group` SET `name` = " + prepareCellData(row.getCell(1)) + ", `abbreviation` = "
  1473.               + prepareCellData(row.getCell(2)) + ", `data_group_id` = " + prepareCellData(row.getCell(3))
  1474.               + ", `description` = " + prepareCellData(row.getCell(4)) + " WHERE `code` = "
  1475.               + prepareCellData(row.getCell(0)) + "; \n");
  1476.         } else {
  1477.           result.append("INSERT INTO `data_group` "
  1478.               + "(`code`,`name`,`abbreviation`, `data_group_id`, `description`) VALUES ("
  1479.               + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + ","
  1480.               + prepareCellData(row.getCell(2)) + "," + prepareCellData(row.getCell(3)) + ","
  1481.               + prepareCellData(row.getCell(4)) + "); \n");
  1482.         }
  1483.       }
  1484.     }
  1485.     return result;
  1486.   }
  1487.  
  1488.   private StringBuffer processPolicy(Sheet sheet) throws SQLException {
  1489.     Set<String> codes = getCodes("SELECT `insurance_policy_code` as `key` FROM `insurance_policy`;");
  1490.     StringBuffer result = new StringBuffer();
  1491.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1492.       Row row = sheet.getRow(i);
  1493.       if (!isRowNullable(row)) {
  1494.         String insuranceCompanyName = row.getCell(2) == null ? null : row.getCell(2).getStringCellValue();
  1495.         String insuranceCompanyId = getIdByName("SELECT `insurance_company_id` as `id` FROM `insurance_company` WHERE insurance_company_name='"
  1496.             + StringEscapeUtils.escapeSql(insuranceCompanyName) + "';");
  1497.         if (row.getCell(0) != null && codes.contains(row.getCell(0).getStringCellValue())) {
  1498.           result.append("UPDATE `insurance_policy` SET `insurance_policy_name` = " + prepareCellData(row.getCell(1))
  1499.               + ", `insurance_company_id` = " + insuranceCompanyId
  1500.               + ", `disclosure_annual_contract_fee_surrender_schedule` = " + prepareCellData(row.getCell(3))
  1501.               + " WHERE `insurance_policy_code` = " + prepareCellData(row.getCell(0)) + "; \n");
  1502.         } else {
  1503.           result
  1504.               .append("INSERT INTO `insurance_policy` "
  1505.                   + "(`insurance_policy_code`,`insurance_policy_name`,`insurance_company_id`, `disclosure_annual_contract_fee_surrender_schedule`) VALUES ("
  1506.                   + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + "," + insuranceCompanyId
  1507.                   + "," + prepareCellData(row.getCell(3)) + "); \n");
  1508.         }
  1509.       }
  1510.     }
  1511.     return result;
  1512.   }
  1513.  
  1514.   private StringBuffer processManagerType(Sheet sheet) throws SQLException {
  1515.     Set<String> codes = getCodes("SELECT `security_type_id` as `key` FROM `asset_security_type`;");
  1516.     StringBuffer result = new StringBuffer();
  1517.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1518.       Row row = sheet.getRow(i);
  1519.       if (!isRowNullable(row)) {
  1520.         if (codes.contains(prepareCellData(row.getCell(0)))) {
  1521.           result.append("UPDATE `asset_security_type` SET `security_type_name` = " + prepareCellData(row.getCell(1))
  1522.               + ", `class` = " + prepareCellData(row.getCell(2)) + ", `order` = " + prepareCellData(row.getCell(3))
  1523.               + ", `color` = " + prepareCellData(row.getCell(4)) + " WHERE `security_type_id` = "
  1524.               + prepareCellData(row.getCell(0)) + "; \n");
  1525.         } else {
  1526.           result.append("INSERT INTO `asset_security_type` "
  1527.               + "(`security_type_id`,`security_type_name`,`class`, `order`, `color`) VALUES ("
  1528.               + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + ","
  1529.               + prepareCellData(row.getCell(2)) + "," + prepareCellData(row.getCell(3)) + ","
  1530.               + prepareCellData(row.getCell(4)) + "); \n");
  1531.         }
  1532.       }
  1533.     }
  1534.     return result;
  1535.   }
  1536.  
  1537.   private StringBuffer processInsuranceCompany(Sheet sheet) throws SQLException {
  1538.     Set<String> codes = getCodes("SELECT `insurance_company_code` as `key` FROM `insurance_company`;");
  1539.     StringBuffer result = new StringBuffer();
  1540.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1541.       Row row = sheet.getRow(i);
  1542.       if (!isRowNullable(row)) {
  1543.         if (codes.contains(row.getCell(0).getStringCellValue())) {
  1544.           result.append("UPDATE `insurance_company` SET `insurance_company_name` = " + prepareCellData(row.getCell(1))
  1545.               + ", `insurance_company_website` = " + prepareCellData(row.getCell(2))
  1546.               + " WHERE `insurance_company_code` = " + prepareCellData(row.getCell(0)) + "; \n");
  1547.         } else {
  1548.           result.append("INSERT INTO `insurance_company` "
  1549.               + "(`insurance_company_code`,`insurance_company_name`,`insurance_company_website`) VALUES ("
  1550.               + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + ","
  1551.               + prepareCellData(row.getCell(2)) + "); \n");
  1552.         }
  1553.       }
  1554.     }
  1555.     return result;
  1556.   }
  1557.  
  1558.   private StringBuffer processAssetTier(Sheet sheet) throws SQLException {
  1559.     Set<String> codes = getCodes("SELECT `code` as `key` FROM `asset_tier`;");
  1560.     StringBuffer result = new StringBuffer();
  1561.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1562.       Row row = sheet.getRow(i);
  1563.       if (!isRowNullable(row)) {
  1564.         if (codes.contains(row.getCell(0).getStringCellValue())) {
  1565.           result.append("UPDATE `asset_tier` SET `name` = " + prepareCellData(row.getCell(1)) + ", `order` = "
  1566.               + prepareCellData(row.getCell(2)) + ", `color` = " + prepareCellData(row.getCell(3)) + " WHERE `code` = "
  1567.               + prepareCellData(row.getCell(0)) + "; \n");
  1568.         } else {
  1569.           result.append("INSERT INTO `asset_tier` (`code`,`name`,`order`,`color`) VALUES ("
  1570.               + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + ","
  1571.               + prepareCellData(row.getCell(2)) + "," + prepareCellData(row.getCell(3)) + "); \n");
  1572.         }
  1573.       }
  1574.     }
  1575.     return result;
  1576.   }
  1577.  
  1578.   private StringBuffer processAssetType(Sheet sheet) throws SQLException {
  1579.     Set<String> codes = getCodes("SELECT `code` as `key` FROM `asset_type`;");
  1580.     StringBuffer result = new StringBuffer();
  1581.     for (int i = 1; i <= sheet.getLastRowNum(); i++) {
  1582.       Row row = sheet.getRow(i);
  1583.       if (!isRowNullable(row)) {
  1584.         if (codes.contains(row.getCell(0).getStringCellValue())) {
  1585.           result.append("UPDATE `asset_type` SET `name` = " + prepareCellData(row.getCell(1)) + ", `display_order` = "
  1586.               + prepareCellData(row.getCell(2)) + " WHERE `code` = " + prepareCellData(row.getCell(0)) + "; \n");
  1587.         } else {
  1588.           result.append("INSERT INTO `asset_type` (`code`,`name`,`display_order`) VALUES ("
  1589.               + prepareCellData(row.getCell(0)) + "," + prepareCellData(row.getCell(1)) + ","
  1590.               + prepareCellData(row.getCell(2)) + "); \n");
  1591.         }
  1592.       }
  1593.     }
  1594.     return result;
  1595.   }
  1596.  
  1597.   private Set<String> getCodes(String sql) throws SQLException {
  1598.     Set<String> res = new HashSet<String>();
  1599.     ResultSet result = connection.createStatement().executeQuery(sql);
  1600.     while (result.next()) {
  1601.       res.add(result.getString("key"));
  1602.     }
  1603.     return res;
  1604.   }
  1605.  
  1606.   private String getIdByName(String sql) throws SQLException {
  1607.     ResultSet result = connection.createStatement().executeQuery(sql);
  1608.     while (result.next()) {
  1609.       return result.getString("id");
  1610.     }
  1611.     return null;
  1612.   }
  1613.  
  1614.   public static void main(String[] args) throws Exception {
  1615.     Workbook workbook = new XSSFWorkbook(
  1616.         new FileInputStream(
  1617.             "/home/stovbun/workspaces/LPL Reports/LPL Reports/java/com/apg/logic/dataimport/excel/LPL_Provided_Upload file_2-4-11.xlsx"));
  1618.     Reader reader = new Reader(true);
  1619.     reader.processWorkbook(workbook);
  1620.     reader.processTradeLogsWorkbook(workbook);
  1621.     // reader.makeDbChanges();
  1622.     reader.printParsedData();
  1623.   }
  1624.  
  1625.   private Map<String, StringBuffer> tables;
  1626.   private Map<String, Integer> updateCount;
  1627.   private Session session;
  1628.   private DbMan dbMan;
  1629.   private Map<String, Set<String>> problems;
  1630.   private Connection connection;
  1631.  
  1632.   public Reader() throws SQLException {
  1633.     super();
  1634.     tables = new TreeMap<String, StringBuffer>();
  1635.     updateCount = new TreeMap<String, Integer>();
  1636.     session = Session.getSession();
  1637.     dbMan = session.getDbMan();
  1638.     problems = new TreeMap<String, Set<String>>();
  1639.     connection = dbMan.getDefaultConnection();
  1640.   }
  1641.  
  1642.   public Reader(boolean b) throws SQLException {
  1643.     super();
  1644.     tables = new TreeMap<String, StringBuffer>();
  1645.     updateCount = new TreeMap<String, Integer>();
  1646.     problems = new TreeMap<String, Set<String>>();
  1647.     Properties properties = new Properties();
  1648.     properties.put("user", "lpl");
  1649.     // properties.put("password", "nfAu1yq3");
  1650.     properties.put("password", "aaa");
  1651.     properties.put("jdbcCompliantTruncation", "false");
  1652.     properties.put("zeroDateTimeBehavior", "convertToNull");
  1653.     connection = DriverManager.getConnection("jdbc:mysql://192.168.0.111:3306/lpl", properties);
  1654.   }
  1655.  
  1656.   private boolean isRowNullable(Row row) {
  1657.     if (row != null) {
  1658.       for (Cell cell : row) {
  1659.         if (prepareCellData(cell) != null) {
  1660.           return false;
  1661.         }
  1662.       }
  1663.     }
  1664.     return true;
  1665.   }
  1666.  
  1667.   private String prepareCellData(Cell cell) {
  1668.     try {
  1669.       if (cell == null) {
  1670.         return null;
  1671.       }
  1672.       switch (cell.getCellType()) {
  1673.       case Cell.CELL_TYPE_BLANK:
  1674.         return null;
  1675.       case Cell.CELL_TYPE_BOOLEAN:
  1676.         return String.valueOf(cell.getBooleanCellValue());
  1677.       case Cell.CELL_TYPE_ERROR:
  1678.         break;
  1679.       case Cell.CELL_TYPE_FORMULA:
  1680.         break;
  1681.       case Cell.CELL_TYPE_NUMERIC:
  1682.         if (HSSFDateUtil.isCellDateFormatted(cell) || HSSFDateUtil.isCellInternalDateFormatted(cell)) {
  1683.           return "'" + new SimpleDateFormat("yyyy-MM-dd hh:mm:ss", Locale.US).format(cell.getDateCellValue()) + "'";
  1684.         }
  1685.         double cellValue = cell.getNumericCellValue();
  1686.         double floor = Math.floor(cellValue);
  1687.         if (floor == cellValue || cellValue - floor < Math.pow(10, -5)) {
  1688.           return String.valueOf((int) cellValue);
  1689.         }
  1690.         return String.valueOf(cellValue);
  1691.       case Cell.CELL_TYPE_STRING:
  1692.         String stringCellValue = cell.getStringCellValue().trim();
  1693.         if (stringCellValue == null || stringCellValue.equals("null") || stringCellValue.equals("")) {
  1694.           return null;
  1695.         }
  1696.         return "'" + StringEscapeUtils.escapeSql(stringCellValue) + "'";
  1697.       }
  1698.     } catch (Exception e) {
  1699.       try {
  1700.         return String.valueOf(((int) cell.getNumericCellValue()));
  1701.       } catch (Exception e1) {
  1702.         String tableName = cell.getSheet().getSheetName();
  1703.         Set<String> tableProblems = problems.get(tableName);
  1704.         if (tableProblems == null) {
  1705.           tableProblems = new HashSet<String>();
  1706.           problems.put(tableName, tableProblems);
  1707.         }
  1708.         tableProblems.add("Can't parse data in Row:" + cell.getRowIndex() + " Column:" + cell.getColumnIndex()
  1709.             + " Cell value:" + cell);
  1710.       }
  1711.     }
  1712.     return null;
  1713.   }
  1714.  
  1715.   public void printParsedData() {
  1716.     for (String tableName : TABLE_LOAD_ORDER) {
  1717.       System.out
  1718.           .println("-- -----------------------------------------------------------------------------------------------------------");
  1719.       System.out.println(tables.get(tableName));
  1720.     }
  1721.   }
  1722.  
  1723.   public void makeDbChanges() {
  1724.     for (String tableName : TABLE_LOAD_ORDER) {
  1725.       try {
  1726.         StringBuffer string = tables.get(tableName);
  1727.         if (string != null && !"".equals(string.toString())) {
  1728.           updateCount.put(tableName, dbMan.executeNonQueryTransactional(string.toString()));
  1729.         }
  1730.       } catch (SQLException e) {
  1731.         Set<String> tableProblems = problems.get(tableName);
  1732.         if (tableProblems == null) {
  1733.           tableProblems = new HashSet<String>();
  1734.           problems.put(tableName, tableProblems);
  1735.         }
  1736.         tableProblems.add(e.getMessage());
  1737.         problems.put(tableName, tableProblems);
  1738.       }
  1739.     }
  1740.   }
  1741.  
  1742.   public Map<String, Integer> getUpdateCount() {
  1743.     return updateCount;
  1744.   }
  1745.  
  1746.   public Map<String, Set<String>> getProblems() {
  1747.     return problems;
  1748.   }
  1749.  
  1750.   /**
  1751.    * @return the tables
  1752.    */
  1753.   public Map<String, StringBuffer> getTables() {
  1754.     return tables;
  1755.   }
  1756.  
  1757. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!