/*
 * Created on Jan 8, 2004
 */

package com.k_int.it4me;
 
//import java.io.FileOutputStream;
import java.sql.*;
import java.util.Date;
import java.util.Properties;
import com.k_int.ia.data_upload.dto.*;
import com.k_int.ia.data_upload.ConfigPair;
import com.k_int.ia.data_upload.ConfigTypes;
import com.k_int.ia.data_upload.DataPublishController;
import com.k_int.ia.data_upload.UploadStatus;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author ibbo
 *
 */
public class HelpYourselfPublisher extends com.k_int.ia.data_upload.ThreadedDataPublisher{

  protected static String DC_NAMESPACE = Messages.getString("HelpYourselfPublisher.0"); //$NON-NLS-1$
  protected static String CI_NAMESPACE = Messages.getString("HelpYourselfPublisher.1"); //$NON-NLS-1$
  protected static String DCTERMS_NAMESPACE = Messages.getString("HelpYourselfPublisher.2"); //$NON-NLS-1$
  protected static String XSI_NAMESPACE = Messages.getString("HelpYourselfPublisher.3"); //$NON-NLS-1$
  protected static String IDENTITY_TRANSFORM = Messages.getString("HelpYourselfPublisher.4"); //$NON-NLS-1$
  
  static final String DATABASE_URL = Messages.getString("HelpYourselfPublisher.55"); //$NON-NLS-1$
  static final String GET_ORG_KEYS_SQL = Messages.getString("HelpYourselfPublisher.7"); //$NON-NLS-1$
  static final String GET_ORG_DETS_SQL = Messages.getString("HelpYourselfPublisher.8"); //$NON-NLS-1$
  static final String GET_AREAS_SQL = Messages.getString("HelpYourselfPublisher.9"); //$NON-NLS-1$
  static final String GET_FAM_CAT_SQL = Messages.getString("HelpYourselfPublisher.10"); //$NON-NLS-1$
  static final String GET_FUNDING_NEWS_SQL = Messages.getString("HelpYourselfPublisher.11"); //$NON-NLS-1$
  static final String GET_KEYWORDS_SQL = Messages.getString("HelpYourselfPublisher.12"); //$NON-NLS-1$
  static final String GET_ADDRESSES_SQL = Messages.getString("HelpYourselfPublisher.54"); //$NON-NLS-1$
  
  
  /**static final String[] config_fields = { "name", 
                                          "publishURL",
                                          "ODBCDSNName",
                                          "targetCollection", 
                                          "publisherIdentity", 
                                          "publisherCredentials"};
  **/
static ConfigPair[] config_fields; //= new ArrayList();
  
  static
  {
      config_fields = new ConfigPair[6];
      ConfigPair pair = new ConfigPair();
      pair.setFieldName("name");
      pair.setFieldType(ConfigTypes.TEXTFIELD);    
      config_fields[0]=pair;
     
      pair = new ConfigPair();
      pair.setFieldName("publishURL");
      pair.setFieldType(ConfigTypes.TEXTAREA);
      config_fields[1]=pair;
      
      pair = new ConfigPair();
      pair.setFieldName("ODBCDSNName");
      pair.setFieldType(ConfigTypes.TEXTFIELD);
      config_fields[2]=pair;
      
      pair = new ConfigPair();
      pair.setFieldName("targetCollection");
      pair.setFieldType(ConfigTypes.TEXTFIELD);
      config_fields[3]=pair;
      
      pair = new ConfigPair();
      pair.setFieldName("publisherIdentity");
      pair.setFieldType(ConfigTypes.TEXTFIELD);
      config_fields[4]=pair;
      
      pair = new ConfigPair();
      pair.setFieldName("publisherCredentials");
      pair.setFieldType(ConfigTypes.PASSWORD);
      config_fields[5]=pair;
     
  }
  public static Log log = LogFactory.getLog(HelpYourselfPublisher.class);

  private String targetCollection = "SHYS";
  private String odbc_dsn_name="jdbc:odbc:HelpYourself";
  private String jdbc_driver_name = "sun.jdbc.odbc.JdbcOdbcDriver";
 
  private transient Connection conn = null;
  private transient PreparedStatement get_org_keys_ps = null;
  private transient PreparedStatement get_org_details_ps = null;
  private transient PreparedStatement get_areas_ps = null;
  private transient PreparedStatement get_fam_cat_ps = null;
  private transient PreparedStatement get_keywords_ps = null;
  private transient PreparedStatement get_addresses_ps = null;

  
  public void run() {
    log.debug("Starting run");

    long start_date = 0;
    try {
      Class driver = Class.forName(jdbc_driver_name);
      conn = DriverManager.getConnection(odbc_dsn_name);
      get_org_keys_ps = conn.prepareStatement(GET_ORG_KEYS_SQL);
      get_org_details_ps = conn.prepareStatement(GET_ORG_DETS_SQL);
      get_areas_ps = conn.prepareStatement(GET_AREAS_SQL);
      get_fam_cat_ps = conn.prepareStatement(GET_FAM_CAT_SQL);
      get_keywords_ps = conn.prepareStatement(GET_KEYWORDS_SQL);
      get_addresses_ps = conn.prepareStatement(GET_ADDRESSES_SQL);

      if ( conn != null ) { 
        try {
          log.debug(Messages.getString("HelpYourselfPublisher.21")+new Date(start_date)); //$NON-NLS-1$
  
          get_org_keys_ps.clearParameters();
          get_org_keys_ps.setDate(1,new java.sql.Date(start_date));
          ResultSet rs =  get_org_keys_ps.executeQuery();
          while (rs.next()) {
            String rec_id = rs.getString(1);
            String default_authority = "HelpYourselfSheffield-UK";
            log.debug(Messages.getString("HelpYourselfPublisher.22")+rec_id); //$NON-NLS-1$
            ResourceDTO result = exportOrganisation(rec_id);
   
            log.debug("Start Upload");
            controller.upload(result,
                              "HelpYourselfSheffield-UK:"+rec_id,
                              "Sheffield Help Yourself",  // Record Source
                              "SIS",                      // Publisher
                              "SIS-Credentials",          // Publisher Credentials
                              getPublishURL(),
                              getTargetCollection(),
                              default_authority);
            log.debug("Done Upload");
            update_count++;
          }
          setStatus(UploadStatus.COMPLETED_OK);
          rs.close();
          get_org_keys_ps.close();
          conn.close();
          log.debug(Messages.getString("HelpYourselfPublisher.23")); //$NON-NLS-1$
        } catch ( com.k_int.ia.data_upload.DataPublishException dpe ) {
          dpe.printStackTrace();
        } catch (SQLException sqle) {
          log.debug(Messages.getString("HelpYourselfPublisher.24")); //$NON-NLS-1$
          sqle.printStackTrace();
        }
      }
      else {
        log.debug(Messages.getString("HelpYourselfPublisher.25")); //$NON-NLS-1$
      }
    } catch (ClassNotFoundException cnfe) {
      log.debug(Messages.getString("HelpYourselfPublisher.19")); //$NON-NLS-1$
      cnfe.printStackTrace();
    } catch (SQLException sqle) {
      log.debug(Messages.getString("HelpYourselfPublisher.20")); //$NON-NLS-1$
      sqle.printStackTrace();
    }
    finally {
      try {
        get_org_keys_ps.close();
        get_org_details_ps.close();
        get_areas_ps.close();
        get_fam_cat_ps.close();
        get_keywords_ps.close();
        get_addresses_ps.close();
        conn.close();
      }
      catch ( Exception e ) {
        e.printStackTrace();
      }
    }
    if(this.getStatus()==UploadStatus.BUSY)
        this.setStatus(UploadStatus.FAILED);
    //setStatus(Messages.getString("HelpYourselfPublisher.26")); //$NON-NLS-1$
    running = false;
    controller.notifyComplete(this);
    log.debug("Completed run");
  }
  
  public ResourceDTO exportOrganisation(String code) {
    ResourceDTO result = new ResourceDTO();
    AvailabilityDTO availability = new AvailabilityDTO();
    // result.setAvailability(availability);
    
    try {
      get_org_details_ps.clearParameters();
      get_org_details_ps.setString(1,code);
      ResultSet details = get_org_details_ps.executeQuery();
      
      if ( details.next() ) {
        result.setTitle(details.getString(3));
        result.setPublisher("Sheffield Information Services");
        // Linkage = details.getString(10)
        result.setDescription(details.getString(6));
        result.setType("text");
        // result.setIdentifier("HelpYourselfSheffield-UK:"+details.getString(2));
        result.setIdentifier(details.getString(10));
        // classification_code = getString(1);

        // Get each area from DB        
        get_areas_ps.clearParameters();
        get_areas_ps.setString(1,code);

        // Add each area served as a ci:coverage/ci:place element
        //ResultSet areas_rs = get_areas_ps.executeQuery();
        //while ( areas_rs.next() ) {
        //  availability.getCoverage().add(new SpatialCoverageDTO("UK-Place-Name",areas_rs.getString(1)));
        //}
        //areas_rs.close();

        processAvailability(result, code);
        // Add each CI:address element

        get_fam_cat_ps.clearParameters();
        get_fam_cat_ps.setString(1,code);
        
        ResultSet fam_cat_rs = get_fam_cat_ps.executeQuery();
        while ( fam_cat_rs.next() ) {
          //Element classn_element = result.createElementNS(DC_NAMESPACE, Messages.getString("HelpYourselfPublisher.48")); //$NON-NLS-1$
          //classn_element.appendChild(result.createTextNode(fam_cat_rs.getString(1)));
          //doc_root_elem.appendChild(classn_element);
          //classn_element.setAttributeNS(XSI_NAMESPACE, Messages.getString("HelpYourselfPublisher.49"), Messages.getString("HelpYourselfPublisher.50"));   //$NON-NLS-1$ //$NON-NLS-2$
        }
        fam_cat_rs.close();
        
        // Keywords
        get_keywords_ps.clearParameters();
        get_keywords_ps.setString(1,code);
        
        ResultSet keywords_rs = get_keywords_ps.executeQuery();
        while ( keywords_rs.next() ) {
          result.getSubjects().add(new SubjectDTO(keywords_rs.getString(1)));
        }
        keywords_rs.close();
        
      }
      
      details.close();
    }
    catch (SQLException sqle) {
      sqle.printStackTrace();
    }
    
    return result;
  }
  
  
  public String getODBCDSNName() {
    return odbc_dsn_name;
  }
 
  public void setODBCDSNName(String odbc_dsn_name) {
    this.odbc_dsn_name = odbc_dsn_name;    
  }
 
  public com.k_int.ia.codbif.xdevicegui.defs.XDLayoutHDO getLayout() {
    return new com.k_int.ia.codbif.xdevicegui.defs.XDLayoutHDO(
        "IT For Me Publisher",
        "com.k_int.it4me.messages",
        new com.k_int.ia.codbif.xdevicegui.defs.XDNameValuePairPanel(config_fields));
  }
  public String getTargetCollection() {
    return targetCollection;
  }
  public void setTargetCollection(String targetCollection) {
    this.targetCollection = targetCollection;
  }
 
  private void processAvailability(ResourceDTO result, String code) throws SQLException {
    get_addresses_ps.clearParameters();
    get_addresses_ps.setString(1,code);
    
    ResultSet avail_rs = get_addresses_ps.executeQuery();
    while ( avail_rs.next() ) {
      //MeetFlag,
      boolean meet_flag = YesNoToBool(avail_rs.getString(1));
      //OpenFlag,
      boolean office_flag = YesNoToBool(avail_rs.getString(2));
      //CorrespondenceFlag,
      boolean correspondence_flag = YesNoToBool(avail_rs.getString(3));
      //ConfidentialAddressFlag,
      boolean confidential_address_flag = YesNoToBool(avail_rs.getString(4));
      //ConfidentialContactFlag,
      boolean confidential_contact_flag = YesNoToBool(avail_rs.getString(5));
      //WorkDiallingCode,
      String work_dialling_code = avail_rs.getString(6);
      //TelNoWork,
      String work_tel_no = avail_rs.getString(7);
      String phone1 = null;
      if ( work_tel_no != null ) 
        phone1 = (work_dialling_code != null ? work_dialling_code+" " : "" ) +work_tel_no;
      //Fax1,
      String fax1 = avail_rs.getString(8);
      //Fax2,
      String fax2 = avail_rs.getString(9);
      //Mobile1,
      String mobile1 = avail_rs.getString(10);
      //Mobile2,
      String mobile2 = avail_rs.getString(11);
      //Minicom,
      String minicom = avail_rs.getString(12);
      //Email1,
      String email1 = avail_rs.getString(13);
      //Email2,
      String email2 = avail_rs.getString(14);
      //Address1,
      String address1 = avail_rs.getString(15);
      //Address2,
      String address2 = avail_rs.getString(16);
      //Address3,
      String address3 = avail_rs.getString(17);
      //Address4,
      String address4 = avail_rs.getString(18);
      //District,
      String district = avail_rs.getString(19);
      //City,
      String city = avail_rs.getString(20);
      //County,
      String county = avail_rs.getString(21);
      //Postcode,
      String postcode = avail_rs.getString(22);
      //Wheelchair,
      boolean wheelchair = YesNoToBool(avail_rs.getString(23));
      //DisabledToilet,
      boolean disabled_toilet = YesNoToBool(avail_rs.getString(24));
      //InductionLoop,
      boolean induction_loop = YesNoToBool(avail_rs.getString(25));
      //Ramp,
      boolean ramp = YesNoToBool(avail_rs.getString(26));
      //AccessNotes,
      String access_notes = avail_rs.getString(27);
      //CorrespondenceContact,
      String contact = avail_rs.getString(28);
      //DaysandTimes
      String days_and_times = avail_rs.getString(29);
      
      String address_type = null;
      
      if (meet_flag) address_type = "Meeting Venue";
      if (office_flag) address_type = "Office Address";
      if ( correspondence_flag ) address_type = null;
      
      if ( address_type != null && !confidential_contact_flag && !confidential_address_flag ) {
        AvailabilityDTO avail = new AvailabilityDTO();
        avail.setTimetextual(days_and_times);
        avail.getAddresses().add(new AddressDTO(address_type, address1, null, address2,city,county,"UK",postcode, null));
        avail.getContacts().add(new ContactDTO(contact,phone1,fax1,email1,"",null));
        result.getAvailability().add(avail);
        avail.setAccess_constraints(access_notes);
        
        if (wheelchair) avail.getFacilities().add("WheelchairAccess");
        if (disabled_toilet) avail.getFacilities().add("DisabledToilet");
        if (induction_loop) avail.getFacilities().add("InductionLoop");
        if (ramp) avail.getFacilities().add("Ramp");

      }
    }
    avail_rs.close();
  }
  
  private boolean YesNoToBool(String yesnotext) {
    boolean result=false;
    if ( ( yesnotext != null ) && ( yesnotext.equalsIgnoreCase("yes") ) )
      result = true;
    return result;
  }
}