#!/usr/bin/perl
  use lib '/var/www/html/test-scb.impelsys.com/crons/reader_crons'; 
  use lib '/var/www/html/test-scb.impelsys.com/crons/ioffline_crons'; 
  use rd_dimension;
  use io_summary;

  use Class::Date;
  use Date::Simple(':all');
  use POSIX;
  require 'db_config.pl';
  require 'global_function_library.pl';

  ###############################################################################################
  # Author : Arun Yakkundi
  #Date    : 12 March 2010
  #Desc    : Purpose of this process is to create iOffline related data - Dimension tables
  ###############################################################################################

  sub mailError {
    ######
    # Send mail only in case of error
    ######
  
    my ($dbConn, $errorMsg) = @_;
    if (length($errorMsg) gt 0) {
      $sqlTokens = "SELECT `key`, value FROM iplat_configurations WHERE category='iPublishCentral' AND configtype='REPORT_CRON_TOKEN'";

      $lhQuery = $dbConn->prepare($sqlTokens) or $errorMsg .= "Error:Preparing MYSQL query failed: $DBI::errstr $sqlTokens\n";
      $lhQuery->execute() or $errorMsg .= "Error:Preparing MYSQL query failed: $DBI::errstr\n";

      while (@results = $lhQuery->fetchrow_array()) {
        if ($results[0] eq 'IPC_CRON_STATUS_MAIL_TO_IDS') {
          @toMailIds = split(',' , $results[1]);
          $mailToId = "";
          $mailCcId = "";
          foreach $mailId (@toMailIds) {
            if ($mailToId eq "") {
              $mailToId = $mailId;
            } else {
              if ($mailCcId eq "") {
                $mailCcId = $mailId;
              } else {
                $mailCcId = $mailCcId . "," . $mailId;
              }
            }
          } # end of for loop
        } elsif ($results[0] eq 'IPC_CRON_ADMIN_MAIL_IDS') {
           $mailFromId = $results[1];
        } elsif($results[0] eq 'IPC_CRON_MAIL_BODY_TEMPALTE') {
           $mailBodyTemplate = $results[1];
        } elsif($results[0] eq 'IPC_CRON_MAIL_SUBJECT_TEMPALTE') {
           $mailSubjectTemplate = $results[1];
           $codePath = $ENV{PWD};
           if ($codePath =~ m/sites\/(.*?)\//) {
             $mailSubjectTemplate .= ",SiteCode:".$1."";
           }
        } else {
           # All tokens will begin with { and end with }, if token is not above matched case then it will be a token
           @arrCronTokens[$tokenIndex] = $results[0];
           $tokenIndex++;
        }
      }
      $mailSubjectTemplate   = parseToken($mailSubjectTemplate,$errorMsg, @arrCronTokens);
      $mailBodyTemplate      = parseToken($mailBodyTemplate,$errorMsg, @arrCronTokens);
      my %arrMailCredentials = ();
      $arrMailCredentials{"mailFrom"}       = $mailFromId;
      $arrMailCredentials{"mailTo"}         = $mailToId;
      $arrMailCredentials{"mailReply"}      = $mailFromId;
      $arrMailCredentials{"mailCc"}         = $mailCcId;
      $arrMailCredentials{"mailBcc"}        = "";
      $arrMailCredentials{"mailSubject"}    = $mailSubjectTemplate.'-(ioffline donwload)';
      $arrMailCredentials{"mailHtmlBody"}   = $mailBodyTemplate;
      $arrMailCredentials{"mailPlainBody"}  = "";
      $arrMailCredentials{"mailAttachment"} = "";
      sendAlertMail(%arrMailCredentials);
    }
    
    return 1;    
  }


  # Variables used in the error handling & reporting by mail
  my $errorMsg            = "";
  my $mailToId            = "";
  my $mailCcId            = "";
  my $mailFromId          = "";
  my $mailSubjectTemplate = "";
  my $mailBodyTemplate    = "";
  my @arrCronTokens       = ();
  my $tokenIndex          = 0;
  my $queryResult         = 0;
  my $sqlDelete           = "";
  ####  
  my $today               = "";
  my $sql;
  my $rowsAffected;
  my $objAffiliate;

  ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
  my $startTime           = time();

  $rowsAffected           = 0;
  $sql                    = "";
  $isArchivedData         = 0;
  #$parseStartDate = today();
  #$parseStartDate = $parseStartDate - 1;
  #$parseEndDate = $parseStartDate;

  my $parseStartDate      = "";
  my $parseEndDate        = "";
  my $tmpDate             = "";

  if (!defined($ARGV[0])){
    $parseStartDate = today();
    $parseStartDate = $parseStartDate-1;
    $parseEndDate = $parseStartDate;
  } else {
    if ($#ARGV == 0) {
      $parseStartDate = Date::Simple->new($ARGV[0]);
      # if invalid date halt
      if (!defined($parseStartDate)){
        die "\nPlease input vaild dates in specified format\nusage: perl <program.pl> YYYY-MM-DD YYYY-MM-DD\n";
      }
      $parseEndDate = $parseStartDate;
    }
    if ($#ARGV == 1){
      $parseStartDate = Date::Simple->new($ARGV[0]);
      $parseEndDate   = Date::Simple->new($ARGV[1]);
      # if invalid date halt
      if (!defined($parseStartDate)){
         die "\nPlease input vaild dates in specified format\nusage: perl <program.pl> YYYY-MM-DD YYYY-MM-DD\n";
      }
      # if invalid date halt
      if (!defined($parseEndDate)){
        die "\nPlease input vaild dates in specified format\nusage: perl <program.pl> YYYY-MM-DD YYYY-MM-DD\n";
      }
      if ($parseStartDate > $parseEndDate){
        $tmpDate        = $parseStartDate;
        $parseStartDate = $parseEndDate;
        $parseEndDate   = $tmpDate;
      }
    }
  }

  $codePath = $ENV{PWD};
  $siteCode = "";
  if ($codePath =~ m/sites\/(.*?)\//) {
    $siteCode = $1;
  }
  printf "\nStarted the ETL for iOffline Dimension, Fact & Summary for Reports (%4d-%02d-%02d %02d:%02d:%02d)\n",$year+1900,$mon+1,$mday,$hour,$min,$sec;
  #object for dimension subroutine
  $objRdDim  = new rd_dimension($eupConnection, $siteCode, $parseStartDate, $parseEndDate);
  $objIOSum  = new io_summary($eupConnection, $siteCode, $parseStartDate, $parseEndDate);

  # --------- Dimension Tables ---------------------------

  $errorMsg  = $objRdDim->populateAccessDateDimension();
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }
  $errorMsg = $objRdDim->populateEupUsers();
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }
  $errorMsg = $objRdDim->populateInstitutionDimension();
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }  
  $errorMsg = $objRdDim->populateBookDetailsDimension();
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }  
  $errorMsg = $objRdDim->populateProductDetailsDimension();
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }
  
  # Populating from iOffline Download DC data     
  $errorMsg = $objIOSum->populateIOfflineUserActivityDimension();
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }
  
  # ----------------- FACT TABLES ------------------ #
  $errorMsg = $objIOSum->populateiOfflineFact($parseStartDate, $parseEndDate);
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }
   
  $errorMsg = $objIOSum->cleanupDB();
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }

  #----------------- Summary Tables For Title Download -------------------#
  $errorMsg = $objIOSum->IDSByTitle($parseStartDate, $parseEndDate);
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }
 
  $errorMsg = $objIOSum->IDSByTitleUsers($parseStartDate, $parseEndDate);
  if (length($errorMsg) gt 0) {
    mailError($eupConnection,$errorMsg);
    die();
  }

  $errorMsg = $objIOSum->IDSByTitleGeography($parseStartDate, $parseEndDate);
    if(length($errorMsg) gt 0) {
     mailError($eupConnection,$errorMsg);
     die();
   }
  #----------------- Summary Tables For iOffline Download -------------------#
  $errorMsg = $objIOSum->IDSByUsers($parseStartDate, $parseEndDate);
  if (length($errorMsg) gt 0) {
     mailError($eupConnection,$errorMsg);
     die();
   }
 
  $errorMsg = $objIOSum->IDSByGeography($parseStartDate, $parseEndDate);
   if(length($errorMsg) gt 0) {
     mailError($eupConnection,$errorMsg);
     die();
   }

   $eupConnection->disconnect();        
  ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
   my $timeDiff = time() - $startTime ;
   my $difMin   = int($timeDiff / 60);
   my $diffSec  = sprintf("%02d", $timeDiff - 60 * $difMin);

   printf "\nEnd of ETL for iOffline of Dimension, Fact And Summary Table End Time:%4d-%02d-%02d %02d:%02d:%02d [%2d\:%02d]\n",$year+1900,$mon+1,$mday,$hour,$min,$sec,$difMin,$diffSec;

;