<?php

/*
  MailBeez Automatic Trigger Email Campaigns
  http://www.mailbeez.com

  Copyright (c) 2010 - 2012 MailBeez

  Licensing Terms & Other Conditions of this
  Mailbeez Module:

  Unless you have our prior written consent, you must NOT directly or indirectly license,
  sub-license, sell, resell, or provide for free or make an offer to do any of these things.
  All of these things are strictly prohibited with this MailBeez Pro Module.

  One Purchase for one Shop - please contact cord@mailbeez.com for volume discounts and
  reseller agreements.

  OpenSource but not free - please respect this license agreement.

 */

// avoid autoloading
// suppress warning on PHP4 for 2nd paramenter
if (!class_exists('order', false)) {
    require_once(DIR_WS_CLASSES . 'order.php');
}



class mh_insight_order extends order
{
    var $orders_id;
    var $customers_id;

    function __construct($orders_id = 0)
    {

        // call constructor
        if ((int)$orders_id > 0) {
            if (is_callable([$this, 'order'])) {
                $this->order((int)$orders_id);
            } elseif (is_callable([get_parent_class($this), '__construct'])) {
                parent::__construct((int)$orders_id);
            } else {
                order::order((int)$orders_id);  // Assuming this is a static method call
            }
        }

        $this->orders_id = (int)$orders_id;
        $this->customers_id = $this->get_customers_id();
    }


    function mh_insight_order($orders_id = 0)
    {
        self::__construct((int)$orders_id);
    }


    function get_customers_id($orders_id = 0)
    {
        if ((int)$orders_id == 0) $orders_id = $this->orders_id;
        if ((int)$orders_id == 0) return false;
        $query_sql = "SELECT customers_id FROM " . TABLE_ORDERS . " WHERE orders_id = '" . (int)$orders_id . "'";
        $query = mh_db_query($query_sql);
        $row = mh_db_fetch_array($query);
        return (int)$row['customers_id'];
    }


    static function getOrdersTurnover($customers_id)
    {
        if ((int)$customers_id == 0) return 0;

        $query_sql = "SELECT sum(ot.value) as turnover FROM " . TABLE_ORDERS . " o INNER JOIN " . TABLE_ORDERS_TOTAL . " ot ON o.orders_id = ot.orders_id WHERE o.customers_id = '" . (int)$customers_id . "' AND ot.class = 'ot_total'";
        $query = mh_db_query($query_sql);
        $row = mh_db_fetch_array($query);
        return (float)$row['turnover'];
    }

    function getNumberOfOrders($customers_id)
    {
        if ((int)$customers_id == 0) return 0;

        $query_sql = "SELECT count(*) as number FROM " . TABLE_ORDERS . " WHERE customers_id = '" . (int)$customers_id . "'";
        $query = mh_db_query($query_sql);
        $row = mh_db_fetch_array($query);
        if (isset($row['number'])) {
            return (int)$row['number'];
        }
        return 0;
    }

    static function getLastOrderID($customers_id)
    {
        if ((int)$customers_id == 0) return false;

        $query_sql = "SELECT orders_id FROM " . TABLE_ORDERS . " WHERE customers_id = '" . (int)$customers_id . "' ORDER BY date_purchased DESC LIMIT 1 ";
        $query = mh_db_query($query_sql);
        $row = mh_db_fetch_array($query);
        if (isset($row['orders_id'])) {
            return (int)$row['orders_id'];
        }
        return 0;

    }

    static function getOrderStatuses($language_id = '')
    {
        $language_id = ($language_id == '') ? $_SESSION['languages_id'] : $language_id;

        $query_sql = "SELECT * FROM " . TABLE_ORDERS_STATUS . " os WHERE os.language_id = '" . (int)$language_id . "'";
        $query = mh_db_query($query_sql);
        $os = array();
        while ($row = mh_db_fetch_array($query)) {
            $os[$row['orders_status_id']] = $row['orders_status_name'];
        }
        return $os;
    }


    static function get_order_list($customers_id, $language_id = '', $customers_email_address = '', $order_ids = '')
    {

        if ((int)$customers_id == 0 && $customers_email_address == '' && (int)$order_ids == 0) {
            return array('rows' => 0, 'total' => 0);
        }

        $language_id = ($language_id == '') ? $_SESSION['languages_id'] : $language_id;

        $additional_order_fields = '';

        $order_ids = ($order_ids == '') ? $order_ids = "''" : $order_ids;

        $fieldlist = "
        		o.orders_id,
        		o.customers_id,
        		o.customers_name,
        		o.customers_company,
        		o.customers_street_address,
        		o.customers_city,
        		o.customers_postcode,
        		o.customers_country,
        		o.billing_name,
        		o.billing_company,
        		o.billing_street_address,
        		o.billing_city,
        		o.billing_postcode,
        		o.billing_country,
        		o.delivery_name,
        		o.delivery_company,
        		o.delivery_street_address,
        		o.delivery_city,
        		o.delivery_postcode,
        		o.delivery_country,
        		o.date_purchased,
        		o.orders_status,
        		os.orders_status_name,
        		ot.text as order_price,
        		ot.value as order_total_value,
        		sum(op.products_quantity) as amount_articles";


        if (mh_saas()) {
            $fieldlist .= ', o.orders_oid ';
        }

        if (config_customer_insight::db_field_exists('TABLE_ORDERS', 'customers_cid')) {
            $additional_order_fields .= ", o.customers_cid";
        }
        if (config_customer_insight::db_field_exists('TABLE_ORDERS', 'customers_vat_id')) {
            $additional_order_fields .= ", o.customers_vat_id";
        }
        if (config_customer_insight::db_field_exists('TABLE_ORDERS', 'customers_status_discount')) {
            $additional_order_fields .= ", o.customers_status_discount";
        }
        if (config_customer_insight::db_field_exists('TABLE_ORDERS', 'refferers_id')) {
            $additional_order_fields .= ", o.refferers_id";
        }
        if (config_customer_insight::db_field_exists('TABLE_ORDERS', 'shipping_method')) {
            $additional_order_fields .= ", o.shipping_method";
        }
        if (config_customer_insight::db_field_exists('TABLE_ORDERS', 'payment_class')) {
            $additional_order_fields .= ", o.payment_class";
        }

        $query_sql = "SELECT " . $fieldlist . $additional_order_fields . " FROM " . TABLE_ORDERS . " o
                        INNER JOIN " . TABLE_ORDERS_STATUS . " os ON o.orders_status = os.orders_status_id
                        INNER JOIN " . TABLE_ORDERS_TOTAL . " ot ON o.orders_id = ot.orders_id
                        LEFT JOIN " . TABLE_ORDERS_PRODUCTS . " op ON o.orders_id = op.orders_id
                        WHERE ot.class = 'ot_total'
                            AND ( (o.customers_id > 0 AND o.customers_id = '" . (int)$customers_id . "') OR
                                  (o.customers_email_address != '' AND
                                   o.customers_email_address = '" . $customers_email_address . "'
                                  ) OR
                                  o.orders_id in (" . $order_ids . "))
                            AND os.language_id = '" . (int)$language_id . "'
                            GROUP BY o.orders_id ORDER BY o.date_purchased DESC LIMIT 500";

        $query = mh_db_query($query_sql);
        $order_size = mh_db_num_rows($query);

        $result = array();
        if ($order_size > 0) {
            $result['total'] = $order_size;
            $items = array();
            while ($row = mh_db_fetch_array($query)) {
                if (mh_cfg('MAILBEEZ_CONVERT_TO_UTF8')) {
                    foreach ($row as $key => $value) {
                        $row[$key] = utf8_encode($value);
                    }
                }
//                $row['add_some_fields'] = ($row['db_field'] > 0) ? 'on' : 'off';
                $row['date_purchased_short'] = mh_datetime_short($row['date_purchased']);

                $row['billing_address'] = $row['billing_name'] . ($row['billing_company'] != '' ? ', ' . $row['billing_company'] : '') . ', ' . $row['billing_street_address'] . ', ' . $row['billing_postcode'] . ' ' . $row['billing_city'] . ', ' . $row['billing_country'];
                $row['delivery_address'] = $row['delivery_name'] . ($row['delivery_company'] != '' ? ', ' . $row['delivery_company'] : '') . ', ' . $row['delivery_street_address'] . ', ' . $row['delivery_postcode'] . ' ' . $row['delivery_city'] . ', ' . $row['delivery_country'];
                array_push($items, $row);
            }
            $result['rows'] = (sizeof($items) > 0) ? $items : 0;
            $result['total']++;
        }

        return $result;
    }

    static function get_order_product_list($customers_id, $language_id = '')
    {
        if ((int)$customers_id == 0) return array('rows' => 0, 'total' => 0);

        $language_id = ($language_id == '') ? $_SESSION['languages_id'] : $language_id;

        $fieldlist = "
                op.orders_products_id,
                op.products_price as price,
                sum(op.products_quantity) as total_quantity,
                sum(op.products_price) as turnover,
                p.products_model,
                p.products_image,
                p.products_price,
                p.products_date_added,
                p.products_last_modified,
                p.products_id,
                pd.products_name,
                pd.products_description,
                pd.products_url";

        if (config_customer_insight::db_field_exists('TABLE_ORDERS_PRODUCTS', 'products_ean')) {
            $fieldlist .= ", p.products_ean";
        }

        if (config_customer_insight::db_field_exists('TABLE_ORDERS_PRODUCTS', 'products_short_description')) {
            $fieldlist .= ", p.products_short_description";
        }

        $query_sql = "SELECT " . $fieldlist . " FROM " . TABLE_ORDERS . " o
                        INNER JOIN " . TABLE_ORDERS_PRODUCTS . " op ON o.orders_id = op.orders_id
                        INNER JOIN " . TABLE_PRODUCTS . " p ON op.products_id = p.products_id
                        INNER JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON p.products_id = pd.products_id
                      WHERE o.customers_id = '" . (int)$customers_id . "'
                        AND pd.language_id = '" . (int)$language_id . "'
                      GROUP BY p.products_id, p.products_price ORDER BY turnover DESC LIMIT 500";

        $query = mh_db_query($query_sql);
        $order_size = mh_db_num_rows($query);

        $result = array();

        if ($order_size > 0) {

            $result['total'] = $order_size;
            $items = array();
            while ($row = mh_db_fetch_array($query)) {
                if (mh_cfg('MAILBEEZ_CONVERT_TO_UTF8')) {
                    foreach ($row as $key => $value) {
                        $row[$key] = utf8_encode($value);
                    }
                }
                $row['products_name_short'] = substr($row['products_name'], 0, 8) . (strlen($row['products_name']) > 8 ? '...' : '');
                $row['products_date_added'] = mh_datetime_short($row['products_date_added']);
                $row['products_last_modified'] = mh_datetime_short($row['products_last_modified']);
                $row['products_price'] = mh_price($row['products_price']);

                $row['price'] = mh_price($row['price']);
                $row['turnover'] = mh_price($row['turnover']);

                $products_image = $row['products_image'];
                $products_image_src = mh_product_image_src(mh_admin_product_image(array('products_image' => $products_image, 'products_name' => $row['products_name'])));
                $row['products_image_src'] = $products_image_src;

                array_push($items, $row);
            }
            $result['rows'] = (sizeof($items) > 0) ? $items : 0;
            $result['total']++;
        }
        return $result;
    }
}

