/**
* Copyright (C) 2014 - present by OpenGamma Inc. and the OpenGamma group of companies
*
* Please see distribution for license.
*/
package com.opengamma.component.tool;
import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.threeten.bp.Instant;
import com.opengamma.core.id.ExternalSchemes;
import com.opengamma.core.legalentity.Capability;
import com.opengamma.core.legalentity.Rating;
import com.opengamma.core.obligor.CreditRating;
import com.opengamma.id.ExternalId;
import com.opengamma.id.ExternalIdBundle;
import com.opengamma.id.UniqueId;
import com.opengamma.master.legalentity.LegalEntityDocument;
import com.opengamma.master.legalentity.LegalEntityMaster;
import com.opengamma.master.legalentity.ManageableLegalEntity;
import com.opengamma.masterdb.legalentity.DbLegalEntityBeanMaster;
import com.opengamma.scripts.Scriptable;
import com.opengamma.util.db.DbDateUtils;
import com.opengamma.util.db.tool.DbToolContext;
/**
* Tool converting Organistaion Database to Legal Entity Database
*/
@Scriptable
public class DbConvertOrganisationsToLegalEntitiesTool extends AbstractDbTool<DbToolContext> {
private static final Logger s_logger = LoggerFactory.getLogger(DbConvertOrganisationsToLegalEntitiesTool.class);
@Override
protected void doRun(boolean write, File outputFile) throws Exception {
String getAllSql = "SELECT ID,OID,VER_FROM_INSTANT,VER_TO_INSTANT,CORR_FROM_INSTANT,CORR_TO_INSTANT," +
"PROVIDER_SCHEME,PROVIDER_VALUE,OBLIGOR_SHORT_NAME,OBLIGOR_RED_CODE,OBLIGOR_TICKER,OBLIGOR_COUNTRY," +
"OBLIGOR_REGION,OBLIGOR_SECTOR,OBLIGOR_COMPOSITE_RATING,OBLIGOR_IMPLIED_RATING,OBLIGOR_FITCH_CREDIT_RATING," +
"OBLIGOR_MOODYS_CREDIT_RATING,OBLIGOR_STANDARD_AND_POORS_CREDIT_RATING,OBLIGOR_HAS_DEFAULTED FROM ORG_ORGANISATION";
Connection conn = getDbToolContext().getDbConnector().getDataSource().getConnection();
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
// The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as
// a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement
// will be retrieved row-by-row.
ResultSet rs = stmt.executeQuery(getAllSql);
LegalEntityMaster legalEntityMaster = new DbLegalEntityBeanMaster(getDbToolContext().getDbConnector());
while (rs.next()) {
long id = rs.getLong("ID");
long oid = rs.getLong("OID");
Instant verFrom = DbDateUtils.fromSqlTimestamp(rs.getTimestamp("VER_FROM_INSTANT"));
Instant verTo = DbDateUtils.fromSqlTimestamp(rs.getTimestamp("VER_TO_INSTANT"));
Instant corrFrom = DbDateUtils.fromSqlTimestamp(rs.getTimestamp("CORR_FROM_INSTANT"));
Instant corrTo = DbDateUtils.fromSqlTimestamp(rs.getTimestamp("CORR_TO_INSTANT"));
String providerScheme = rs.getString("PROVIDER_SCHEME");
String providerValue = rs.getString("PROVIDER_VALUE");
String name = rs.getString("OBLIGOR_SHORT_NAME");
String redCode = rs.getString("OBLIGOR_RED_CODE");
String ticker = rs.getString("OBLIGOR_TICKER");
String country = rs.getString("OBLIGOR_COUNTRY");
String region = rs.getString("OBLIGOR_REGION");
String sector = rs.getString("OBLIGOR_SECTOR");
String compositeRating = rs.getString("OBLIGOR_COMPOSITE_RATING");
String impliedRating = rs.getString("OBLIGOR_IMPLIED_RATING");
String fitchCreditRating = rs.getString("OBLIGOR_FITCH_CREDIT_RATING");
String moodysCreditRating = rs.getString("OBLIGOR_MOODYS_CREDIT_RATING");
String standardAndPoorsCreditRating = rs.getString("OBLIGOR_STANDARD_AND_POORS_CREDIT_RATING");
String hasDefaulted = rs.getString("OBLIGOR_HAS_DEFAULTED");
ManageableLegalEntity legalEntity = new ManageableLegalEntity(name, ExternalIdBundle.of(ExternalSchemes.MARKIT_RED_CODE, redCode));
legalEntity.setUniqueId(UniqueId.of(DbLegalEntityBeanMaster.IDENTIFIER_SCHEME_DEFAULT, Long.toString(oid), Long.toString(id - oid)));
legalEntity.addExternalId(ExternalId.of("TICKER", ticker));
legalEntity.addAttribute("country", country);
legalEntity.addAttribute("region", region);
legalEntity.addAttribute("sector", sector);
legalEntity.addAttribute("hasDefaulted", hasDefaulted);
legalEntity.addDetail("providerScheme", providerScheme);
legalEntity.addDetail("providerValue", providerValue);
legalEntity.getCapabilities().add(new Capability("ISSUER"));
legalEntity.getRatings().add(new Rating("composite", CreditRating.valueOf(compositeRating), null));
legalEntity.getRatings().add(new Rating("implied", CreditRating.valueOf(impliedRating), null));
legalEntity.getRatings().add(new Rating("Fitch", CreditRating.valueOf(fitchCreditRating), null));
legalEntity.getRatings().add(new Rating("Moodys", CreditRating.valueOf(moodysCreditRating), null));
legalEntity.getRatings().add(new Rating("StandardAndPoors", CreditRating.valueOf(standardAndPoorsCreditRating), null));
LegalEntityDocument document = new LegalEntityDocument();
document.setCorrectionFromInstant(corrFrom);
document.setCorrectionToInstant(corrTo);
document.setVersionFromInstant(verFrom);
document.setVersionToInstant(verTo);
legalEntityMaster.add(document);
}
}
}