<?php
/**
 * orderhistory
 *
 * historical orders
 *
 * @version    1.0
 * @author     Erik Timmers, amgate <erik.timmers@amgate.com>
 * @copyright  Amgate, 2010
 * @since      18-01-2010 - initial creation
 * @package    CDVB0100
 */

class cdvb100_orderhistory extends stdModule
{

    /**
     * cdvb100_page
     *
     * PHP4 constructor
     *
     * @access public
     * @return void
     */
    function cdvb100_orderhistory()
    {
        $this->stdModule('cdvb0100');
        $this->setVariable('modcode', 'cdvb0100');
    }

    /**
     * store
     *
     * store the cart, in other words the order, for later use
     *
     * @access public
     * @param  array $aCart the cart; order
     * @return void
     */
    function store($aCart)
    {

        $aUsr = $this->getUsrData($_SESSION['session_usr_id']);
        $aOrg = $this->getOrgData($aUsr["USR_ORG_ID"]);

        $aInsert = array( 'ord_usr_id'        => $_SESSION['session_usr_id']
                        , 'ord_org_id'        => $aUsr['USR_ORG_ID']             // or $_SESSION['session_org_id']
                        , 'ord_usr_name'      => $aUsr['USR_GREETING']
                        , 'ord_usr_nawstreet' => $aUsr['GATT_STREETNAME']
                        , 'ord_usr_nawzip'    => $aUsr['GATT_ZIPCODE']
                        , 'ord_usr_nawcity'   => $aUsr['GATT_CITY']
                        , 'ord_org_name'      => $aOrg['ORG_NAME']
                        , 'ord_debiteurnr'    => $aOrg['GATT_DEB_NUMBER']
                        , 'ord_totalprice'    => $aCart['price']["incvat"]
                        );
        $newOrderId = $this->storeOrderHeader($aInsert);

        foreach((array)$aCart['products'] as $aProduct) {

            $aInsert = array( 'orl_prd_id'      => $aProduct['id']
                            , 'orl_prd_name'    => $aProduct['name']
                            , 'orl_prd_code'    => $aProduct['code']
                            , 'orl_amount'      => $aProduct['amount']
                            , 'orl_prd_price'   => $aProduct['price']["incvat"]["piece"]
                            , 'orl_total_price' => $aProduct['price']["incvat"]["total"]
                            );

            $this->storeOrderLine($newOrderId, $aInsert);
        }



    }

    /**
     * getUsrData
     *
     * get current userdata from id
     *
     * @access public
     * @param  int    $uid  id of user
     * @return array
     */
    function getUsrData($uid)
    {
        global $g_obj_i3a;
        $q = array(I3A_AND
                  , array(I3A_FLD, 'USR_ORG_ID')
                  , array(I3A_FLD, 'get_user_greeting(USR_ID,0) as USR_GREETING')
                  , array(I3A_FLD, 'USR_ID', " = " . (int)$uid)
                  , array(I3A_FLD, 'GATT_STREETNAME')
                  , array(I3A_FLD, 'GATT_ZIPCODE')
                  , array(I3A_FLD, 'GATT_CITY')
                  );
        $g_obj_i3a->searchUsers($aUser, $q);

        return $aUser[0];
    }

    /**
     * getUsrData
     *
     * get current organisation data from id
     *
     * @access public
     * @param  int    $oid  id of organisation
     * @return array
     */
    function getOrgData($oid) {
        global $g_obj_i3a;
        $q = array( 'AND'
                  , array(I3A_FLD, 'ORG_ID', " = " . (int)$oid)
                  , array(I3A_FLD, 'ORG_NAME')
                  , array(I3A_FLD, 'GATT_DEB_NUMBER')

                  );

        $g_obj_i3a->searchOrganizations($aOrgs, $q);

        return $aOrgs[0];
    }

    /**
     * storeOrderHeader
     *
     * store the general order data in database. returns newly generated id
     *
     *   $aInsert = array( 'ord_usr_id'        => id of user (integer)
     *                   , 'ord_org_id'        => id of organisation (integer)
     *                   , 'ord_usr_name'      => full name of user (string)
     *                   , 'ord_usr_nawstreet' => address of user (string)
     *                   , 'ord_usr_nawzip'    => zipcode of user (string)
     *                   , 'ord_usr_nawcity'   => city of address of user (string)
     *                   , 'ord_org_name'      => name of organisation (clientele) of user (string)
     *                   , 'ord_debiteurnr'    => debiteur number of clientele (string)
     *                   , 'ord_totalprice'    => total price as string (string)
     *
     *
     * @access public
     * @param  array    $aInsert    array of data to be inserted in database
     * @return integer
     */
    function storeOrderHeader($aInsert)
    {
        $sSql = " INSERT INTO tbl_dvb_order
                            ( ord_id
                            , ord_org_id
                            , ord_org_name
                            , ord_debiteurnr
                            , ord_usr_id
                            , ord_usr_name
                            , ord_usr_nawstreet
                            , ord_usr_nawzip
                            , ord_usr_nawcity
                            , ord_datetime
                            , ord_totalprice
                            )
                       VALUES
                            ( NULL
                            , " . (int)$aInsert['ord_org_id'] . "
                            , '" . mysql_real_escape_string($aInsert['ord_org_name']) . "'
                            , '" . mysql_real_escape_string($aInsert['ord_debiteurnr']) . "'
                            , " . (int)$aInsert['ord_usr_id'] . "
                            , '" . mysql_real_escape_string($aInsert['ord_usr_name']) . "'
                            , '" . mysql_real_escape_string($aInsert['ord_usr_nawstreet']) . "'
                            , '" . mysql_real_escape_string($aInsert['ord_usr_nawzip']) . "'
                            , '" . mysql_real_escape_string($aInsert['ord_usr_nawcity']) . "'
                            , now()
                            , '" . mysql_real_escape_string($aInsert['ord_totalprice']) . "'
                            )
                ";
        $this->_db->Execute($sSql);
        $nIdNew = $this->_db->lastInsertID();
        return $nIdNew;
    }

    /**
     * storeOrderLine
     *
     * store data of one product of the order in the database, prices as string
     *
     *   $aInsert = array( 'orl_prd_id'      => id of product (integer)
     *                   , 'orl_prd_name'    => name of product (string)
     *                   , 'orl_prd_code'    => code of product (integer)
     *                   , 'orl_amount'      => number of items ordered (integer)
     *                   , 'orl_prd_price'   => price per piece (string)
     *                   , 'orl_total_price' => price per product = amount * price per piece (string)
     *                   );
     *
     * @access public
     * @param  int    $idOrder  id of the order
     * @param  array  $aInsert  all data to be inserted
     * @return integer
     */
    function storeOrderLine($idOrder, $aInsert)
    {
        $price = preg_replace('/(\D*)([\.\d]+)/i', '$2', $aInsert['orl_prd_price']);
        $totPrice = preg_replace('/(\D*)([\.\d]+)/i', '$2', $aInsert['orl_total_price']);
        
        $sSql = " INSERT INTO tbl_dvb_orderline
                            ( orl_id
                            , orl_ord_id
                            , orl_prd_id
                            , orl_prd_name
                            , orl_prd_code
                            , orl_amount
                            , orl_prd_price
                            , orl_total_price
                            , orl_prd_price_num
                            , orl_total_price_num
                            )
                       VALUES
                            ( NULL
                            , " . (int)$idOrder . "
                            , " . (int)$aInsert['orl_prd_id'] . "
                            , '" . mysql_real_escape_string($aInsert['orl_prd_name']) . "'
                            , '" . mysql_real_escape_string($aInsert['orl_prd_code']) . "'
                            , " . (int)$aInsert['orl_amount'] . "
                            , '" . mysql_real_escape_string($aInsert['orl_prd_price']) . "'
                            , '" . mysql_real_escape_string($aInsert['orl_total_price']) . "'
                            , " . (float)$price . "
                            , " . (float)$totPrice . "
                            )
                ";
        $this->_db->Execute($sSql);
        $nIdNew = $this->_db->lastInsertID();
        return $nIdNew;

    }

    /**
     * getCountHistory
     *
     * get number of historical orders of user
     *
     * @access public
     * @param  int    $uid  id of user
     * @return integer
     */
    function getCountHistory($uid)
    {
        $sSql = " SELECT count(*) as cnt
                    FROM tbl_dvb_order
                   WHERE ord_usr_id = " . (int)$uid . "
                ";
        $rst = $this->_db->Query($sSql);
        $nRec = $rst->Row('cnt');
        $rst->Close();
        return $nRec;
    }

    /**
     * getUserHistory
     *
     * get (part of) user's order history. Returns the header-info and count of products
     *
     * @access public
     * @param  int    $uid      id of user
     * @param  int    $offset   start record of list
     * @param  int    $limit    numer of records to fetch
     * @return array
     */
    function getUserHistory($uid, $offset, $limit)
    {
        $aReturn = array();
        $sSql = " SELECT ord_id
                       , date_format(ord_datetime, '%d-%m-%Y') as ord_date
                       , ord_totalprice
                       , sum(orl_amount) as ord_amountofproducts
                    FROM tbl_dvb_order
                        JOIN tbl_dvb_orderline 
                          ON orl_ord_id = ord_id
                   WHERE ord_usr_id = " . (int)$uid . "
                   GROUP BY ord_id, ord_datetime, ord_totalprice
                   ORDER BY ord_datetime desc
                ";
        // get $limit records from $offset
        $rst = $this->_db->Query($sSql, $offset, $limit);
        while (!$rst->EOF()) {
           $aReturn[] = $rst->Row();
           $rst->MoveNext();
        }
        
        //$sSql = " SELECT orl_prd_id
        //               , orl_prd_name
        //               , orl_prd_code
        //               , orl_amount
        //               , date_format(ord_datetime, '%d-%m-%Y') as ord_date
        //               , YEAR(ord_datetime) as ord_year
        //            FROM tbl_dvb_orderline
        //                JOIN tbl_dvb_order ditjaar
        //                  ON orl_ord_id = ditjaar.ord_id
        //                 AND YEAR(ord_datetime) IN (" . date('Y') . ", " . (date('Y')-1) . ")
        //           WHERE ord_usr_id = " . (int)$uid . "  
        //           ORDER BY orl_prd_name, ord_datetime                         
        //        ";
        //$rst = $this->_db->Query($sSql);
        //while (!$rst->EOF()) {
        //    $row = $rst->row();
        //    if (!is_array($aReturn[$row['orl_prd_id']])) {
        //        $aReturn[$row['orl_prd_id']] = array();
        //    }
        //               
        //    $aReturn[$row['orl_prd_id']]['orl_prd_name'] = $row['orl_prd_name'];
        //    $aReturn[$row['orl_prd_id']]['orl_prd_code'] = $row['orl_prd_code'];
        //    if ($row['ord_year'] == date('Y')) {
        //        $aReturn[$row['orl_prd_id']]['totalthisyear'] += (int)$row['orl_amount'];
        //        $aReturn[$row['orl_prd_id']]['thisyear'][] = $row['ord_date'];
        //    } else {
        //        $aReturn[$row['orl_prd_id']]['totallastyear'] += (int)$row['orl_amount'];
        //        $aReturn[$row['orl_prd_id']]['lastyear'][] = $row['ord_date'];
        //    }
        //
        //    $rst->MoveNext();
        //}                
               
        return $aReturn;

    }

    /**
     * getHistoricalOrder
     *
     * get order lines of a historical order 
     *
     * @access public
     * @param  int    $uid  id of user (so user onlu can see one's own orders)
     * @param  int    $oid  id of order
     * @return array
     */
    function getHistoricalOrder($uid, $oid)
    {
        $sSql = " SELECT orl_prd_id
                       , orl_prd_name
                       , orl_prd_code
                       , orl_amount
                       , orl_prd_price
                       , orl_total_price
                    FROM tbl_dvb_order
                    JOIN tbl_dvb_orderline ON orl_ord_id = ord_id
                   WHERE ord_usr_id = " . (int)$uid . "
                     AND ord_id = " . (int)$oid . "
                ";
        $rst = $this->_db->Query($sSql);
        while (!$rst->EOF()) {
           $aReturn[] = $rst->Row();
           $rst->MoveNext();
        }
        return $aReturn;

    }

    function getProcurement($oid, $aPeriod)
    {
        switch($aPeriod['intval']) {
            case CDVB_INTVAL_MONTH:
                $sSqlPeriod = ", date_format(ord_datetime, '%Y%m') as orl_period ";
                break;
            case CDVB_INTVAL_WEEK:
                $sSqlPeriod = ", date_format(ord_datetime, '%x%v') as orl_period ";
                break;
            case CDVB_INTVAL_YEAR:
                $sSqlPeriod = ", date_format(ord_datetime, '%Y') as orl_period ";
                break;

        }
        
        $sSqlWhere = " AND ord_datetime > '" . date('Y-m-d', $aPeriod['dateStart']) . "'
                       AND ord_datetime <= '" . date('Y-m-d', $aPeriod['dateEnd']) . "'";
        
        $sSql = " SELECT orl_prd_id
                       , orl_prd_name
                       , orl_prd_code
                       , orl_amount
                       , orl_prd_price_num
                       , orl_total_price_num
                       , ord_datetime
                    " . $sSqlPeriod . "
                    FROM tbl_dvb_order
                    JOIN tbl_dvb_orderline ON orl_ord_id = ord_id  
                   WHERE ord_org_id = " . (int)$oid . "
                    " . $sSqlWhere . "
                ";
        $rst = $this->_db->Query($sSql);
        while (!$rst->EOF()) {
            $aRow = $rst->Row();
            // product column
            $aReturn[$aRow['orl_prd_id']]['orl_prd_name'] = $aRow['orl_prd_name'];
            // Totals column
            $aReturn[$aRow['orl_prd_id']]['count'] += (int)$aRow['orl_amount'];
            $aReturn[$aRow['orl_prd_id']]['price'] += (float)$aRow['orl_total_price_num'];
            // Several period columns
            $aReturn[$aRow['orl_prd_id']]['periods'][$aRow['orl_period']]['count'] += (int)$aRow['orl_amount'];
            $aReturn[$aRow['orl_prd_id']]['periods'][$aRow['orl_period']]['price'] += (float)$aRow['orl_total_price_num'];
            
            $aReturn['total']['count'] += (int)$aRow['orl_amount'];
            $aReturn['total']['price'] += (float)$aRow['orl_total_price_num'];
            $aReturn['total']['periods'][$aRow['orl_period']]['count'] += (int)$aRow['orl_amount'];
            $aReturn['total']['periods'][$aRow['orl_period']]['price'] += (float)$aRow['orl_total_price_num'];

            $rst->MoveNext();
        }
        return $aReturn;                
        
    }
    
    

}


?>
