<?php
/*******************************************************************************

    Copyright 2009 Whole Foods Co-op

    This file is part of CORE-POS.

    CORE-POS is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    CORE-POS is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    in the file license.txt along with IT CORE; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

*********************************************************************************/

/* HELP

   nightly.dtrans.php

   This script archives transaction data. The main
   reason for rotating transaction data into
   multiple snapshot tables is speed. A single large
   transaction table eventually becomes slow. The 
   rotation applied here is as follows:

   dtransactions is copied into transarchive, then
   transarchive is trimmed so it contains the previous
   90 days of transactions.

   dlog_15, a lookup table of the past 15 days'
   transaction data, is reloaded using transarchive

   dtransactions is also copied to a monthly snapshot,
   transarchiveYYYYMM on the archive database. Support
   for archiving to a remote server is theoretical and
   should be thoroughly tested before being put into
   production. Archive tables are created automatically
   as are corresponding dlog and receipt views.

   After dtransactions has been copied to these two
   locations, it is truncated. This script is meant to
   be run nightly so that dtransactions always holds
   just the current day's data. 
*/

include(dirname(__FILE__) . '/../config.php');
if (!class_exists('FannieAPI')) {
    include(__DIR__ . '/../classlib2.0/FannieAPI.php');
}
if (!function_exists('cron_msg')) {
    include(__DIR__ . '/../src/cron_msg.php');
}

set_time_limit(0);

$sql = new SQLManager($FANNIE_SERVER,$FANNIE_SERVER_DBMS,$FANNIE_TRANS_DB,
        $FANNIE_SERVER_USER,$FANNIE_SERVER_PW);

$cols = $sql->tableDefinition('dtransactions');
if (isset($cols['date_id'])){
    $sql->query("UPDATE dtransactions SET date_id=DATE_FORMAT(datetime,'%Y%m%d')");
}

/* Find date(s) in dtransactions */
$datesR = $sql->query('SELECT YEAR(datetime) AS year, MONTH(datetime) as month, DAY(datetime) as day
                    FROM dtransactions
                    GROUP BY YEAR(datetime), MONTH(datetime), DAY(datetime)
                    ORDER BY YEAR(datetime), MONTH(datetime), DAY(datetime)');
$dates = array();
while($datesW = $sql->fetch_row($datesR)) {
    $dates[] = sprintf('%d-%02d-%02d', $datesW['year'], $datesW['month'], $datesW['day']);
}

$UPDATED_DLOG_SCHEMA = false;
$table_def = $sql->tableDefinition('dlog');
if (isset($table_def['description'])) {
    // most likely
    $UPDATED_DLOG_SCHEMA = true;
}

/* Load dtransactions into the archive, trim to 90 days */
$chkP = $sql->prepare("INSERT INTO transarchive SELECT * FROM dtransactions WHERE ".$sql->datediff('datetime','?').'= 0');
$chk1 = false;
foreach($dates as $date) {
    $chk1 = $sql->execute($chkP, array($date));
}
$chk2 = $sql->query("DELETE FROM transarchive WHERE ".$sql->datediff($sql->now(),'datetime')." > 92");
if ($chk1 === false) {
    echo cron_msg("Error loading data into transarchive");
} elseif ($chk2 === false) {
    echo cron_msg("Error trimming transarchive");
} else {
    echo cron_msg("Data rotated into transarchive");
}

/* reload all the small snapshot */
$chk1 = $sql->query("TRUNCATE TABLE dlog_15");
$chk2 = $sql->query("INSERT INTO dlog_15 SELECT * FROM dlog_90_view WHERE ".$sql->datediff($sql->now(),'tdate')." <= 15");
if ($chk1 === false || $chk2 === false)
    echo cron_msg("Error reloading dlog_15");
else
    echo cron_msg("Success reloading dlog_15");

$added_partition = false;
foreach($dates as $date) {
    /* figure out which monthly archive dtransactions data belongs in */
    list($year, $month, $day) = explode('-', $date);
    $dstr = $year.$month;
    $table = 'transArchive'.$dstr;

    /* store monthly archive locally or remotely as needed 
       remote archiving is very beta
    */
    if ($FANNIE_ARCHIVE_REMOTE){
        $sql = new SQLManager($FANNIE_ARCHIVE_SERVER,$FANNIE_ARCHIVE_DBMS,
            $FANNIE_ARCHIVE_DB,$FANNIE_ARCHIVE_USER,$FANNIE_ARCHIVE_PW);
        if (!$sql->table_exists($table)){
            createArchive($table,$sql);
            if ($UPDATED_DLOG_SCHEMA) {
                $model = new DTransactionsModel($sql);
                $model->normalizeLog('dlog' . $str, $table, BasicModel::NORMALIZE_MODE_APPLY);
            } else {
                createViews($dstr,$sql);
            }
        }
        $sql->addConnection($FANNIE_SERVER,$FANNIE_SERVER_DBMS,$FANNIE_TRANS_DB,
            $FANNIE_SERVER_USER,$FANNIE_SERVER_PW);
        $sql->transfer($FANNIE_TRANS_DB,
            "select * from dtransactions WHERE ".$sql->datediff('datetime',"'$date'")."= 0",
            $FANNIE_ARCHIVE_DB,"insert into $table");
    } else {
        $sql = new SQLManager($FANNIE_SERVER,$FANNIE_SERVER_DBMS,$FANNIE_ARCHIVE_DB,
                $FANNIE_SERVER_USER,$FANNIE_SERVER_PW);
        if ($FANNIE_ARCHIVE_METHOD == "partitions" && strstr($FANNIE_SERVER_DBMS, "MYSQL")) {
            // we're just partitioning
            // make a new partition if it's a new month
            if (date('j') == 1 && !$added_partition){
                $p = "p".date("Ym"); 
                $boundary = date("Y-m-d",mktime(0,0,0,date("n")+1,1,date("Y")));
                // new partition named pYYYYMM
                // ends on first day of next month
                $newQ = sprintf("ALTER TABLE bigArchive ADD PARTITION 
                    (PARTITION %s 
                    VALUES LESS THAN (TO_DAYS('%s'))
                    )",$p,$boundary);
                $newR = $sql->query($newQ);
                if ($newR === false) {
                    echo cron_msg("Error creating new partition $p");
                } else {
                    $added_partition = true;
                }
            }
        
            // now just copy rows into the partitioned table
            $loadQ = "INSERT INTO bigArchive SELECT * FROM {$FANNIE_TRANS_DB}.dtransactions
                        WHERE ".$sql->datediff('datetime', "'$date'")."= 0";
            $loadR = $sql->query($loadQ);
        } else if (!$sql->table_exists($table)){
            // 20Nov12 EL Add "TABLE".
            $query = "CREATE TABLE $table LIKE $FANNIE_TRANS_DB.dtransactions";
            if ($FANNIE_SERVER_DBMS == 'MSSQL') {
                $query = "SELECT * INTO $table FROM $FANNIE_TRANS_DB.dbo.dtransactions
                            WHERE ".$sql->datediff('datetime', "'$date'")."= 0";
            }
            $chk1 = $sql->query($query,$FANNIE_ARCHIVE_DB);
            $chk2 = true;
            if (strstr($FANNIE_SERVER_DBMS,"MYSQL")) {
                // mysql doesn't create & populate in one step
                $chk2 = $sql->query("INSERT INTO $table SELECT * FROM $FANNIE_TRANS_DB.dtransactions
                                    WHERE ".$sql->datediff('datetime', "'$date'")."= 0");
            }
            if ($chk1 === false || $chk2 === false) {
                echo cron_msg("Error creating new archive $table");
            } else {
                echo cron_msg("Created new table $table and archived dtransactions");
            }
            if ($UPDATED_DLOG_SCHEMA) {
                $model = new DTransactionsModel($sql);
                $model->normalizeLog('dlog' . $str, $table, BasicModel::NORMALIZE_MODE_APPLY);
            } else {
                createViews($dstr,$sql);
            }
        } else {
            $query = "INSERT INTO $table SELECT * FROM $FANNIE_TRANS_DB.dtransactions
                        WHERE ".$sql->datediff('datetime', "'$date'")."= 0";
            if ($FANNIE_SERVER_DBMS == 'MSSQL') {
                $query = "INSERT INTO $table SELECT * FROM $FANNIE_TRANS_DB.dbo.dtransactions
                        WHERE ".$sql->datediff('datetime', "'$date'")."= 0";
            }
            $chk = $sql->query($query,$FANNIE_ARCHIVE_DB);
            if ($chk === false) {
                echo cron_msg("Error archiving dtransactions");
            } else {
                echo cron_msg("Success archiving dtransactions");
            }
        }
    }
} // for loop on dates in dtransactions

/* drop dtransactions data 
   DO NOT TRUNCATE; that resets AUTO_INCREMENT column
*/
$sql = new SQLManager($FANNIE_SERVER,$FANNIE_SERVER_DBMS,$FANNIE_TRANS_DB,
        $FANNIE_SERVER_USER,$FANNIE_SERVER_PW);
$chk = $sql->query("DELETE FROM dtransactions");
if ($chk === false)
    echo cron_msg("Error truncating dtransactions");
else
    echo cron_msg("Success truncating dtransactions");

function createArchive($name,$db){
    global $FANNIE_SERVER_DBMS, $FANNIE_ARCHIVE_REMOTE,
        $FANNIE_ARCHIVE_DBMS, $FANNIE_ARCHIVE_DB;;

    $dbms = $FANNIE_ARCHIVE_REMOTE?$FANNIE_ARCHIVE_DBMS:$FANNIE_SERVER_DBMS;
    
    $trans_columns = "(
      `datetime` datetime default NULL,
      `register_no` smallint(6) default NULL,
      `emp_no` smallint(6) default NULL,
      `trans_no` int(11) default NULL,
      `upc` varchar(255) default NULL,
      `description` varchar(255) default NULL,
      `trans_type` varchar(255) default NULL,
      `trans_subtype` varchar(255) default NULL,
      `trans_status` varchar(255) default NULL,
      `department` smallint(6) default NULL,
      `quantity` double default NULL,
      `scale` tinyint(4) default NULL,
      `cost` double default 0.00 NULL,
      `unitPrice` double default NULL,
      `total` double default NULL,
      `regPrice` double default NULL,
      `tax` smallint(6) default NULL,
      `foodstamp` tinyint(4) default NULL,
      `discount` double default NULL,
      `memDiscount` double default NULL,
      `discountable` tinyint(4) default NULL,
      `discounttype` tinyint(4) default NULL,
      `voided` tinyint(4) default NULL,
      `percentDiscount` tinyint(4) default NULL,
      `ItemQtty` double default NULL,
      `volDiscType` tinyint(4) default NULL,
      `volume` tinyint(4) default NULL,
      `VolSpecial` double default NULL,
      `mixMatch` smallint(6) default NULL,
      `matched` smallint(6) default NULL,
      `memType` tinyint(2) default NULL,
      `staff` tinyint(4) default NULL,
      `numflag` smallint(6) default 0 NULL,
      `charflag` varchar(2) default '' NULL,
      `card_no` varchar(255) default NULL,
      `trans_id` int(11) default NULL
    )";

    if ($dbms == 'MSSQL'){
        $trans_columns = "([datetime] [datetime] NOT NULL ,
            [register_no] [smallint] NOT NULL ,
            [emp_no] [smallint] NOT NULL ,
            [trans_no] [int] NOT NULL ,
            [upc] [nvarchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [description] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [trans_type] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [trans_subtype] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [trans_status] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [department] [smallint] NULL ,
            [quantity] [float] NULL ,
            [scale] [tinyint] NULL ,
            [cost] [money] NULL ,
            [unitPrice] [money] NULL ,
            [total] [money] NOT NULL ,
            [regPrice] [money] NULL ,
            [tax] [smallint] NULL ,
            [foodstamp] [tinyint] NOT NULL ,
            [discount] [money] NOT NULL ,
            [memDiscount] [money] NULL ,
            [discountable] [tinyint] NULL ,
            [discounttype] [tinyint] NULL ,
            [voided] [tinyint] NULL ,
            [percentDiscount] [tinyint] NULL ,
            [ItemQtty] [float] NULL ,
            [volDiscType] [tinyint] NOT NULL ,
            [volume] [tinyint] NOT NULL ,
            [VolSpecial] [money] NOT NULL ,
            [mixMatch] [smallint] NULL ,
            [matched] [smallint] NOT NULL ,
            [memType] [smallint] NULL ,
            [isStaff] [tinyint] NULL ,
            [numflag] [smallint] NULL ,
            [charflag] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [card_no] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [trans_id] [int] NOT NULL )";
    }

    $db->query("CREATE TABLE $table $trans_columns",$FANNIE_ARCHIVE_DB);
}

function createViews($dstr,$db){
    global $FANNIE_SERVER_DBMS, $FANNIE_ARCHIVE_REMOTE,
        $FANNIE_ARCHIVE_DBMS, $FANNIE_ARCHIVE_DB,
        $FANNIE_SERVER,$FANNIE_SERVER_PW,$FANNIE_SERVER_USER,
        $FANNIE_ARCHIVE_SERVER,$FANNIE_ARCHIVE_USER,
        $FANNIE_ARCHIVE_PW; 

    if ($FANNIE_ARCHIVE_REMOTE){
        $db->addConnection($FANNIE_ARCHIVE_SERVER,$FANNIE_ARCHIVE_DBMS,
            $FANNIE_ARCHIVE_DB,$FANNIE_ARCHIVE_USER,
            $FANNIE_ARCHIVE_PW);
    }
    else {
        $db->addConnection($FANNIE_SERVER,$FANNIE_SERVER_DBMS,
            $FANNIE_ARCHIVE_DB,$FANNIE_SERVER_USER,$FANNIE_SERVER_PW);
    }

    $dbms = $FANNIE_ARCHIVE_REMOTE?$FANNIE_ARCHIVE_DBMS:$FANNIE_SERVER_DBMS;

    $table_def = $db->tableDefinition('transArchive' . $str);

    $dlogQ = "CREATE  view dlog$dstr as
        select 
        d.datetime as tdate, 
        d.register_no, 
        d.emp_no, 
        d.trans_no, 
        d.upc, 
        d.description,
        CASE WHEN (d.trans_subtype IN ('CP','IC') OR d.upc like('%000000052')) then 'T' 
            WHEN d.upc = 'DISCOUNT' then 'S' else d.trans_type end as trans_type, 
        CASE WHEN d.upc = 'MAD Coupon' THEN 'MA' ELSe 
           case when d.upc like('%00000000052') then 'RR' else d.trans_subtype end end as trans_subtype, 
        d.trans_status, 
        d.department, 
        d.quantity, 
        d.scale,
        d.cost,
        d.unitPrice, 
        d.total, 
        d.regPrice,
        d.tax, 
        d.foodstamp, 
        d.discount,
        d.memDiscount,
        d.discountable,
        d.discounttype,
        d.voided,
        d.percentDiscount,
        d.itemQtty, 
        d.memType,
        d.volDiscType,
        d.volume,
        d.VolSpecial,
        d.mixMatch,
        d.matched,
        d.staff,
        d.numflag,
        d.charflag,
        d.card_no, 
        d.trans_id, ";
    if (isset($table_def['pos_row_id'])) {
        $dlogQ .= "d.pos_row_id,";
    }
    if (isset($table_def['store_row_id'])) {
        $dlogQ .= "d.store_row_id,";
    }
    $dlogQ .= "concat(convert(d.emp_no,char), '-', convert(d.register_no,char), '-',
        convert(d.trans_no,char)) as trans_num

        from transArchive$dstr as d
        where d.trans_status not in ('D','X','Z') and d.emp_no not in (9999,56) and d.register_no  <> 99";

    if ($dbms == "MSSQL"){
        $dlogQ = "CREATE  view dlog$dstr as
            select 
            d.datetime as tdate, 
            d.register_no, 
            d.emp_no, 
            d.trans_no, 
            d.upc, 
            d.description,
            CASE WHEN (d.trans_subtype IN ('CP','IC') OR d.upc like('%000000052')) then 'T' 
                WHEN d.upc = 'DISCOUNT' then 'S' else d.trans_type end as trans_type, 
            CASE WHEN d.upc = 'MAD Coupon' THEN 'MA' ELSe 
               case when d.upc like('%00000000052') then 'RR' else d.trans_subtype end end as trans_subtype, 
            d.trans_status, 
            d.department, 
            d.quantity, 
            c.scale,
            d.cost,
            d.unitPrice, 
            d.total, 
            d.regPrice,
            d.tax, 
            d.foodstamp, 
            d.discount,
            d.memDiscount,
            d.discountable,
            d.discounttype,
            d.voided,
            d.percentDiscount,
            d.itemQtty, 
            d.volDiscType,
            d.volume,
            d.VolSpecial,
            d.mixMatch,
            d.matched,
            d.memType,
            d.isStaff,
            d.numflag,
            d.charflag,
            d.card_no, 
            d.trans_id,";
            if (isset($table_def['pos_row_id'])) {
                $dlogQ .= "d.pos_row_id,";
            }
            if (isset($table_def['store_row_id'])) {
                $dlogQ .= "d.store_row_id,";
            }
            $dlogQ .= "(convert(varchar,d.emp_no) +  '-' + convert(varchar,d.register_no) + '-' + 
            convert(varchar,d.trans_no)) as trans_num

            from transArchive$dstr as d
            where d.trans_status not in ('D','X','Z') and d.emp_no not in (9999,56) and d.register_no  <> 99";
    }
    $chk = $db->query($dlogQ,$FANNIE_ARCHIVE_DB);
    if ($chk === false)
        echo cron_msg("Error creating dlog view for new archive table");
}

