<?php


class data_quality_check
{

    function __construct()
    {

    }
    
    function data_quality_check()
    {
        self::__construct();
    }         

    static function get_customer_statistics()
    {

        // 15 customer accounts
        // 12 unique email addresses
        $query_sql = "select count(*) as cnt_all, count(distinct customers_email_address) as cnt_distinct from " . TABLE_CUSTOMERS . "  ";
        $query = mh_db_query($query_sql);
        $result = mh_db_fetch_array($query);
        return $result;
    }


    static function get_abandoned_account_cnt()
    {


        // x customers never placed an order

        // this query does not work on some servers as index are not applied
        // http://stackoverflow.com/questions/25934210/mysql-index-not-used-in-left-join-query-on-some-server
        // https://stackoverflow.com/questions/18660252/mysql-why-does-left-join-not-use-an-index
        $query_sql_bug = "select count(*) as cnt from " . TABLE_CUSTOMERS . " c
                        left join " . TABLE_ORDERS . " o on (c.customers_id = o.customers_id)
                      where o.orders_id is null";

        $query_sql = "select cnt_all, cnt_o, (cnt_all - cnt_o) as cnt from (
        select count(distinct c.customers_id ) as cnt_o, (select count(*) as cnt_all
           from " . TABLE_CUSTOMERS . " c) as cnt_all
           from " . TABLE_CUSTOMERS . " c
        inner join  " . TABLE_ORDERS . " o
           on (c.customers_id = o.customers_id)) a; ";


        $query = mh_db_query($query_sql);
        $result = mh_db_fetch_array($query);
        return $result;
    }


    static function get_guest_account_redundancy()
    {

        // number of customers | number of accounts per customer

        // 3 customers with guest accounts have 5 accounts
        // 7 customers with guest accounts have 2 accounts


        // count of duplicate guest accounts

        // todo
        // select guests only
        // xtc: customers.account_type = 1
        // cowoa: customers.COWOA_account

        $query_sql = "select * from (select count(*) as cnt_unique, a.c as count_redundant from  (
                        select customers_email_address, Count(*) c
                           FROM " . TABLE_CUSTOMERS . "
                           GROUP BY customers_email_address HAVING c > 1
                      ) a
                      group by a.c ) r
                      order by cnt_unique desc
                      ";

        $query = mh_db_query($query_sql);
        $result = array();
        $weighted_avg = 0;
        $cnt_unique = 0;
        $cnt_redundant = 0;
        $weight_sum = 0;
        while ($item = mh_db_fetch_array($query)) {
            $weight = $item['count_redundant'] * $item['cnt_unique'];
            $weight_sum += $weight;
            $cnt_unique += $item['cnt_unique'];
            $result[$item['count_redundant']] = array('count_redundant' => $item['count_redundant'],
                'cnt_unique' => $item['cnt_unique'],
                'weight' => $weight
            );
        }

        $weighted_avg = ($cnt_unique > 0) ? round($weight_sum / $cnt_unique, 2) : 0;

        $result['summary'] = array('weighted_avg' => $weighted_avg,
            'weight_sum' => $weight_sum,
            'cnt_unique' => $cnt_unique,
            'cnt_redundant' => $weight_sum,
        );

        return $result;

    }


    static function get_orphan_order_cnt()
    {
        // find orders w/o customer accounts
        // todo: rewrite
        $query_sql = "select count(*) as cnt from " . TABLE_ORDERS . " o
                        inner join " . TABLE_CUSTOMERS . " c on (c.customers_id = o.customers_id)
                      where c.customers_id is null";


        $query_sql = "select cnt_all, cnt_c, (cnt_all - cnt_c) as cnt from (
                select count(distinct o.orders_id ) as cnt_c, (select count(*) as cnt_all
                   from " . TABLE_ORDERS . " o) as cnt_all
                   from " . TABLE_ORDERS . " o
                inner join  " . TABLE_CUSTOMERS . " c
                   on (c.customers_id = o.customers_id)) a; ";

        $query = mh_db_query($query_sql);
        $result = mh_db_fetch_array($query);
        return $result;

    }

} 