<?php

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

  Copyright (c) 2010 - 2015 MailBeez

  inspired and in parts based on
  Copyright (c) 2003 osCommerce

  Released under the GNU General Public License



 */

///////////////////////////////////////////////////////////////////////////////
///																																					 //
///                 MailBeez Core file - do not edit                         //
///                                                                          //
///////////////////////////////////////////////////////////////////////////////

require_once(MH_DIR_FS_CATALOG . MH_ROOT_PATH . 'common/functions/email_engine_php_mailer.php');
require_once(mh_cfg('MH_DIR_CONFIG') . 'config_email_engine.php');

class mailbeez_system
{

    const SUBSCRIPTION_USAGE_LEVEL_OK = 0.95;
    const SUBSCRIPTION_USAGE_LEVEL_WARNING = 0.975;
    const SUBSCRIPTION_USAGE_LEVEL_ALERT = 0.999;

    function __construct()
    {

    }


    public static function getUniqueCustomerCnt($refresh = false)
    {
        // The distinct customer count barely changes between requests but the
        // COUNT(DISTINCT ...) query below is expensive and used to run on every
        // admin request (config_cloudbeez.php is loaded via inc_mailbeez.php).
        // Cache the result: an in-request memo avoids repeated calls within a
        // single request, and a shared (file based) app cache avoids the DB
        // query across requests. Pass $refresh = true to force a fresh read.
        static $cnt_memo = null;

        if (!$refresh && $cnt_memo !== null) {
            return $cnt_memo;
        }

        require_once(MH_DIR_FS_CATALOG . MH_ROOT_PATH . 'common/classes/mailbeez_cache.php');

        $customer_cnt_cache = new mailbeez_cache();
        $customer_cnt_cache->app_cache_time = 60 * 60 * 1; // 12h - customer count changes slowly
        $customer_cnt_cache->set_cache_id('unique_customer_cnt', 'mailbeez_system');

        if (!$refresh) {
            $cached = $customer_cnt_cache->read_cache('app');
            if ($cached !== false) {
                $cnt_memo = $cached['value'];
                return $cnt_memo;
            }
        }

        $query_sql = "select count(distinct customers_email_address) as cnt_distinct
              from " . TABLE_CUSTOMERS . " where true ";
        // exclude amazon customers, newmail.de, meinpaket
        $query_sql .= " 
        and customers_email_address not like '%@marketplace.amazon%' 
        and customers_email_address not like '%@members.ebay%' 
        and customers_email_address not like '%@newmail.de' 
        and customers_email_address not like '%@post.meinpaket.de'
        and customers_email_address not like '%@dawanda.com'
        and customers_email_address not like '%@allyouneed%'
        and customers_email_address not like '%@checkout.idealo.de'
        and customers_email_address not like '%@customer.otto.market'
        and customers_email_address not like '%@kaufland%'        
        and customers_email_address not like '%@moebel.check24.de'        
        and customers_email_address not like '%@b.metro-marketplace.eu'        
        and customers_email_address not like '%@hood.de'       
        and customers_email_address not like '%@shoepping.at%'  
        ";



        if (MH_PLATFORM == 'zencart') {
            // zencart - exclude not approved customer
            $query_sql .= " and customers_authorization = 0 "; // 0 - approved
        }

        if (mh_cfg('MAILBEEZ_CONFIG_CHECK_SUBSCRIPTION') == 'True') {
            // todo
//            $query_sql .= "and customers_newsletter = '1'";
        }

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

        $cnt_memo = $result['cnt_distinct'];
        $customer_cnt_cache->set_cache($cnt_memo, 'app');

        return $cnt_memo;
    }

    public static function getSubscriptionInfo($uniqueCustomerCnt = 0)
    {

        require_once(MH_DIR_CONFIG . 'config_cloudbeez/classes/api_cloudbeez_consumer.php');
//        list($show_select_package, $show_waiting_info, $show_install_package, $show_update_package, $show_update_package_ok, $show_invalid_license, $show_api_error) = cloudbeez_status_handler::getStatus();

        mh_load_modules_language_files(MH_DIR_CONFIG, 'config_cloudbeez');

        $subscriptions = cloudBeezApiConsumer::getSubscriptionInfo();

        $data = array();
        $found_subscription = false;
        $status = false;
        $cnt = 0;

        if ((is_array($subscriptions) && sizeof($subscriptions) > 0)) {

            foreach ($subscriptions as $sub) {

//            $sub['error_license'] = 'exceeded cnt_customers';

                if (stristr($sub['data']['title'], 'beezdesk')) {
                    // todo
                    continue;
                }
                if (stristr($sub['data']['title'], 'ecgcheck')) {
                    // todo
                    continue;
                }

                // is a subscriptions
                if ($sub['data']['default_controller_id'] == 1) {
                    $found_subscription = true;
                    $cnt++;

                    $customer_usage = 0;
                    if ($sub['data']['max_customers'] > 0) {
                        $customer_usage = number_format($uniqueCustomerCnt / $sub['data']['max_customers'], 2, '.', '');
                    }

                    $customer_usage_perc = $customer_usage * 100 * 1;

                    if ($sub['expires_in'] == -1) {
                        $msg_code = 'WARNING_EXPIRED';
                        $status = 'ERROR';
                    } elseif ($sub['error_license']) {
                        switch ($sub['error_license']) {
                            case 'exceeded cnt_customers':
                                $msg_code = 'ERROR_EXCEED_CUSTOMER_CNT';
                                $status = 'ERROR';
                                break;
                            default;
                                $msg_code = 'OK';
                                $status = 'OK';
                        }
                    } elseif ($sub['expires_in'] < 3) {
                        $msg_code = 'WARNING_EXPIRES_SOON';
                        $status = 'WARNING';
                    } elseif ($customer_usage < mailbeez_system::SUBSCRIPTION_USAGE_LEVEL_OK) {
                        $msg_code = 'OK';
                        $status = 'OK';
                    } elseif ($customer_usage < mailbeez_system::SUBSCRIPTION_USAGE_LEVEL_WARNING) {
                        $msg_code = 'WARNING_CUSTOMER_CNT';
                        $status = 'WARNING';
                    } elseif ($customer_usage < mailbeez_system::SUBSCRIPTION_USAGE_LEVEL_ALERT) {
                        $msg_code = 'WARNING_CUSTOMER_CNT';
                        $status = 'WARNING';
                    } elseif ($customer_usage >= 1) {
                        $msg_code = 'WARNING_CUSTOMER_CNT';
                        $status = 'WARNING';
                    } else {
                        $msg_code = 'OK';
                        $status = 'OK';
                    }
                    $data = array(
                        'status' => $status,
                        'value' => array(
                            'title' => mh_lng('MAILBEEZ_CONFIG_CLOUDBEEZ_' . strtoupper($sub['data']['title'])) ? mh_lng('MAILBEEZ_CONFIG_CLOUDBEEZ_' . strtoupper($sub['data']['title'])) : $sub['data']['title'],
                            'max_customers' => $sub['data']['max_customers'],
                            'expires_in' => $sub['expires_in'],
                            'expiration_date' => $sub['expiration_date'],
                            'message' => $msg_code,
                            'customer_usage' => $customer_usage,
                            'customer_usage_perc' => $customer_usage_perc,
                        )
                    );

                    if ($status != 'ERROR' && ($sub['status'] == 'ACTIVE' || $sub['status'] == 'PASTDUE')) {
                        // first valid subscription found
                        break;
                    }
                }
            }
        }

        return array(
            'is_pro' => $found_subscription,
            'data' => $data,
            'cnt' => $cnt
        );
    }

    public static function getNewsletter2Go_credits()
    {
        if (mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE_N2G_API_VERSION') == 'v1') {
            require_once(MH_DIR_CONFIG . 'config_email_engine_n2g/classes/newsletter2go.class.php');

            try {
                $nl2go = new newsletter2go(mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE_N2G_API_KEY'));
                $credits = $nl2go->getCredits();

                if ($credits['success']) {
                    return $credits['value']['freemailcount'] + $credits['value']['emailcount'] + $credits['value']['abocount'];
                }
            } catch (Exception $e) {
                print_r($e->getMessage());
            }
            return -1;

        } else {
            require_once(MH_DIR_CONFIG . 'config_email_engine_n2g/classes/newsletter2go_v2.class.php');

            try {
                $nl2go = NL2GO\Api::getInstance();
                $result = $nl2go->getCredits();
            } catch (Exception $e) {
                print_r($e->getMessage());
                return -1;
            }
            return $result['credits_total'];
        }
    }

    public static function getNewsletter2Go_creditsThreshold($uniqueCustomerCnt)
    {
        return $uniqueCustomerCnt / 10;
        return 1100;
    }


    public static function getNewsletter2Go_status($uniqueCustomerCnt)
    {


        /*

        require_once(MH_DIR_CONFIG . 'config_customer_insight/classes/notificationbeez.php');

        $notficationsObj = new notificationbeez();

        require_once(MH_DIR_CONFIG . 'config_email_engine_n2g/viewbeez/view_n2go.php');

        $view = new view_n2go();
        $credits = $view->getCredits();
*/
        $credits = -1;
        $status = -1;
        $msg = '';
        $migration_data = false;

        $active = preg_match('/Newsletter2Go/', mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE'));

        if ($active) {

            $msg = '';
            $spf_warning = false; // todo
            $status = 'OK'; // todo
            if (mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE_N2G_API_VERSION') == 'v1') {
                require_once(MH_DIR_CONFIG . 'config_email_engine_n2g/classes/newsletter2go.class.php');

                $nl2g_status = false;
                $nl2g_reg_status = false;
                $nl2g_error = false;
                $nl2g_error_msg = '';

                try {
                    $nl2go = new newsletter2go(mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE_N2G_API_KEY'));
                    $credits_check = $nl2go->getCredits();

                    if ($credits_check['success']) {
                        $nl2g_reg_status_value = $nl2go->getStatus();
                        $nl2g_reg_status = $nl2g_reg_status_value['value'];

                        $nl2g_reg_migration_value = $nl2go->getMigrationStatus();
                        $migration_data = array(
                            'status' => $nl2g_reg_migration_value['value']['status'],
                            'date' => $nl2g_reg_migration_value['value']['date']
                        );

                        switch ($nl2g_reg_migration_value['value']['status']) {
                            case 'not_available':
                                // show option to trigger upgrade
                                $migration_data['upgrade_url'];
                                break;
                            case 'waiting':
                                // show waiting for upgrade info
                                $status = 'WARNING';
                                $msg = 'INTEGRATION_UPGRADE_WAITING';
                                break;
                            case 'available':
                                // show button to switch to V2
                                require_once(MH_DIR_CONFIG . 'config_email_engine_n2g/classes/newsletter2go_v2.class.php');

                                $migration_data['oauth_url'] = NL2GO\Api::buildEntrypoint();
                                $status = 'WARNING';
                                $msg = 'INTEGRATION_UPGRADE_AVAILABLE';
                                break;
                        }


                    } else {
                        $status = 'ERROR';
                        $msg = 'INTEGRATION_ERROR';
                    }

                } catch (Exception $e) {
                    $status = 'ERROR';
                    $msg = 'INTEGRATION_ERROR';
                }

            } else {

                require_once(MH_DIR_CONFIG . 'config_email_engine_n2g/classes/newsletter2go_v2.class.php');
                try {
                    $nl2go = NL2GO\Api::getInstance();
                    $ping = $nl2go->ping();
                } catch (Exception $e) {
                    print_r($e->getMessage());
                    $status = 'ERROR';
                    $msg = 'INTEGRATION_ERROR';
                }
            }

            if ($status == 'OK' || $msg == 'INTEGRATION_UPGRADE_WAITING' || $msg == 'INTEGRATION_UPGRADE_AVAILABLE') {
                $credits = mailbeez_system::getNewsletter2Go_credits();
                $credits_threshold = mailbeez_system::getNewsletter2Go_creditsThreshold($uniqueCustomerCnt);
                $spf_warning = !mailbeez_system::getNewsletter2Go_senderIdsStatus();

                if ($credits == 0) {
                    $msg = 'NO_CREDITS';
                    $status = 'ERROR';
                } elseif ($credits < $credits_threshold) {
                    $msg = 'LOW_CREDITS';
                    $status = 'WARNING';
                } elseif ($spf_warning) {
                    $msg = 'SPF_WARNING';
                    $status = 'WARNING';
                }
            }
        }

        return array(
            'active' => $active,
            'data' => array(
                'credits' => $credits,
                'status' => $status,
                'msg' => $msg,
                'config_url' => mh_href_link(FILENAME_MAILBEEZ, 'module=config_email_engine'),
                'migration' => $migration_data
            )
            //'notifications' => $notficationsObj->getOpenNotificationList(3, null, 'view_n2go')
        );

    }

    public static function getNewsletter2Go_senderIdsStatus()
    {
        if (mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE_N2G_API_VERSION') == 'v1') {
            return true; // not support in api V1
        } else {
            require_once(MH_DIR_CONFIG . 'config_email_engine_n2g/classes/newsletter2go_v2.class.php');
            try {
                $nl2go = NL2GO\Api::getInstance();
                return $nl2go->checkSenderids();
            } catch (Exception $e) {
                print_r($e->getMessage());
            }
        }
        return false;
    }

    public static function getMlbzEmail_status($uniqueCustomerCnt, $refresh = false)
    {

        $active = (mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE') == 'mlbz_ubvx') || (mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE') == 'mlbz_hysndr');

        return array(
            'active' => $active,
            'data' => array(
                'credits' => 0,
                'status' => config_email_engine::connection_test($refresh) ? 'OK' : 'ERROR',
                'msg' => '',
                'config_url' => mh_href_link(FILENAME_MAILBEEZ, 'module=config_email_engine'),
                'migration' => false
            )
        );
    }


    public static function getEmailStatus($refresh = false)
    {
        $email_status = 'OK';

        if(mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE') == 'PHPMailer') {
            $email_status = config_email_engine::connection_test($refresh) ? 'OK' : 'ERROR';
        }

        $query_raw = "select count(*) as cnt from " . TABLE_MAILBEEZ_TRACKING . " where message_id = '-1'";
        $query = mh_db_query($query_raw);
        $email_check_data = mh_db_fetch_array($query);
        if (mh_db_num_rows($query) && $email_check_data['cnt'] > 0) {
            $email_error_cnt = $email_check_data['cnt'];
            $email_status = 'ERROR';
        } else {
            $email_error_cnt = 0;
        }


        $data = array(
            'status' => $email_status,
            'data' => array(
                'engine_shop' => (mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE') == 'Shop'),
                'error_cnt' => $email_error_cnt,
                'config_url' => mh_href_link(FILENAME_MAILBEEZ, 'module=config_email_engine'),
            )
        );

        return $data;

    }

    public static function getLastAction()
    {
        $last_run_warning = false;
        $last_sent_warning = false;
        $last_sent_warning_offset = 72 * 60 * 60;
        $last_run_warning_offset = 36 * 60 * 60;
        $last_action_status = 'OK';

        // todo
        // check for MAILBEEZ_RUN_INIT events only


        $query_raw = "select log_date from " . TABLE_MAILBEEZ_EVENT_LOG . " where event_type = 'MAILHIVE_RUN_COMPLETE' order by batch_id DESC limit 1";
        $query = mh_db_query($query_raw);
        $last_run_data = mh_db_fetch_array($query);
        if (mh_db_num_rows($query)) {
            $last_run_date = $last_run_data['log_date'];
        } else {
            $last_run_date = false;
        }

        $query_raw = "select date_sent from " . TABLE_MAILBEEZ_TRACKING . " order by autoemail_id DESC limit 1";
        $query = mh_db_query($query_raw);
        $last_sent_data = mh_db_fetch_array($query);
        if (mh_db_num_rows($query)) {
            $last_sent_date = $last_sent_data['date_sent'];
        } else {
            $last_sent_date = false;
        }

        if (false === $last_run_date && false === $last_sent_date) {
            $last_run_warning = 'NEVER';
            $last_action_status = 'INIT';
        } elseif (false === $last_run_date) {
            // never finished sending process, but emails have been sent
            $last_run_warning = 'INCOMPLETE';
            $last_action_status = 'CHECK';
        } elseif (strtotime($last_run_date) < time() - $last_run_warning_offset) {
            $last_run_warning = 'LATE';
            $last_action_status = 'CHECK';
        }

        if (false === $last_sent_date) {
            $last_sent_warning = 'NEVER';
            $last_action_status = 'INIT';
        } elseif (strtotime($last_sent_date) < time() - $last_sent_warning_offset) {
            $last_sent_warning = 'LATE';
            $last_action_status = 'CHECK';
        }

        $data = array(
            'status' => $last_action_status,
            'data' => array(
                'last_run_date' => $last_run_date,
                'last_run_warning' => $last_run_warning,
                'last_sent_date' => $last_sent_date,
                'last_sent_warning' => $last_sent_warning,
            )
        );

        return $data;

    }


    static function getSystemStatusData($refresh = false)
    {
        list($select_package, $install_package, $update_package, , ,) = cloudbeez_status_handler::getActionUrls();

        $uniqueCustomerCnt = mailbeez_system::getUniqueCustomerCnt($refresh);
        $system_data = mailbeez_system::getLastAction();

        $subscription_info = mailbeez_system::getSubscriptionInfo($uniqueCustomerCnt);
        $n2go_status = mailbeez_system::getNewsletter2Go_status($uniqueCustomerCnt);
        $mlbz_mail_status = mailbeez_system::getMlbzEmail_status($uniqueCustomerCnt, $refresh);

        if (!$n2go_status['active'] && !$mlbz_mail_status['active']) {
            $email_status = mailbeez_system::getEmailStatus($refresh);
        } else {
            $email_status['status'] = false;
        }

        if (mh_cfg('MAILBEEZ_CONFIG_EMAIL_ENGINE_N2G_API_VERSION') == 'v1') {
            // upgrade notification
            // todo
        }


        if ((isset($subscription_info['data']['status']) && $subscription_info['data']['status'] == 'ERROR') || $n2go_status['data']['status'] == 'ERROR' || $email_status['status'] == 'ERROR') {
            //ERROR
            $status_summary = 'ERROR';
        } elseif ((isset($subscription_info['data']['status']) && $subscription_info['data']['status'] == 'WARNING') || $system_data['status'] == 'CHECK' || $n2go_status['data']['status'] == 'WARNING') {
            // WARNING
            $status_summary = 'WARNING';
        } else {
            $status_summary = 'OK';
        }

        if ($subscription_info['is_pro'] && mh_saas()) {
            $subscription_info['data']['status'] = 'OK';
        }


        $data = array(
            'subscription' => $subscription_info,
            'subscription_cnt_ok_usage' => mailbeez_system::SUBSCRIPTION_USAGE_LEVEL_OK, // todo configurable
            'subscription_cnt_warning_usage' => mailbeez_system::SUBSCRIPTION_USAGE_LEVEL_WARNING, // todo configurable
            'subscription_cnt_alert_usage' => mailbeez_system::SUBSCRIPTION_USAGE_LEVEL_ALERT, // todo configurable
            'subscription_link' => $select_package,
            'cnt_distinct' => $uniqueCustomerCnt,
            'status_summary' => $status_summary,
            'show_info' => ($status_summary != 'OK'),
            'status_n2go_active' => $n2go_status['active'],
            'status_mlbz_email_active' => $mlbz_mail_status['active'],
            'system' => $system_data,
            'is_saas' => mh_saas(),
            'MH_CATALOG_URL' => MH_CATALOG_SERVER . MH_DIR_WS_CATALOG,
            'MH_ROOT_PATH' => MH_ROOT_PATH
        );


        if ($n2go_status['active']) {
            $data['n2go_data'] = $n2go_status['data'];
        } elseif ($mlbz_mail_status['active']) {
            $data['mlbz_email_data'] = $mlbz_mail_status['data'];
        } else {
            $data['email_status'] = $email_status;
        }
        return $data;
    }


    static function cfg_pull_down_main_templates($mailbeezObj, $name)
    {
        return template_manager::cfg_pull_down_main_templates($mailbeezObj, $name);
    }

    static function load_modules($modules_installed = null)
    {
        $modules_installed = (mh_not_null($modules_installed)) ? $modules_installed : mh_cfg('MAILBEEZ_INSTALLED');

        $mailbeez_modules = explode(';', $modules_installed);

        $modules_data_array = array();
        reset($mailbeez_modules);
        foreach ($mailbeez_modules as $idx => $mailbee) {
            if (preg_match('/^config/', $mailbee)) {
                // exclude config modules
                continue;
            }
            include_once(MH_DIR_FS_CATALOG . MH_ROOT_PATH . 'mailbeez/' . $mailbee);
            $module = substr($mailbee, 0, strrpos($mailbee, '.'));
            $class = mh_get_class_name($mailbee);
            $module_path = mh_get_class_path($class);
            mh_load_modules_language_files(MH_DIR_MODULE, $module_path, MH_FILE_EXTENSION);
            if (!isset($GLOBALS[$module]) && class_exists($class)) {
                $GLOBALS[$module] = new $class;
            }

            if (!$GLOBALS[$module]->enabled) {
                continue;
            }

            $module_icon = '';
            if ($GLOBALS[$module]->icon != '') {
                $module_icon = mh_image(MH_MODULE_DIRECTORY_WS . $GLOBALS[$module]->module . '/' . $GLOBALS[$module]->icon, '', '13', '13', 'align="absmiddle"');
            }

//            if ($GLOBALS[$module]->is_submodule_of)
//                continue;


            if (!$GLOBALS[$module]->do_process && !$GLOBALS[$module]->has_submodules) {
                continue;
            }
            $modules_data_array[] = array('module' => $GLOBALS[$module]->module,
                'module_id' => str_replace('/', '__', $GLOBALS[$module]->get_module_id()),
                'title' => $module_icon . ' ' . $GLOBALS[$module]->title,
                'allow_reporting' => $GLOBALS[$module]->allow_reporting,
                'iteration' => $GLOBALS[$module]->iteration,
                'module_id_iteration' => $GLOBALS[$module]->get_module_id_iteration(),
                'do_process' => $GLOBALS[$module]->do_process,
                'has_submodules' => $GLOBALS[$module]->has_submodules,
                'is_submodule' => $GLOBALS[$module]->is_submodule_of,
                'is_service' => $GLOBALS[$module]->is_service,
                'allow_timing' => $GLOBALS[$module]->allow_timing
            );
        }
        return $modules_data_array;
    }

    static function cfg_pull_down_modules($name, $onchange_url = null, $with_reporting_only = true, $add_empty = true)
    {

        $module_id_selected = mh_get($name); // todo: selected module
        $module_id_selected_url = '';

        $data_array = mailbeez_system::load_modules(mh_cfg('MAILBEEZ_INSTALLED')); // todo: all modules

//        print_r($data_array);

        $modules_array = array();

        if ($add_empty) {
            $modules_array[] =
                array('id' => $onchange_url,
                    'text' => '--- ' . mh_cfg('MH_ALL_MODULES') . ' ---');
        }

        foreach ($data_array as $module) {
            if ($with_reporting_only && !$module['allow_reporting']) {
                continue;
            }

            $module_id = $module['module'];
            $text = $module['title'];
            $text = strip_tags($text);

            if ($module['is_submodule']) {
                $module_id = $module['module_id_iteration'];
                $text = ' -  ' . $text;
            }
            if ($module['has_submodules']) {
                $text = $text . ' (All Steps)';
            }


            $module_id_url = ($onchange_url) ? $onchange_url . '&' . $name . '=' . $module_id : $module_id;
            $module_id_selected_url = ($module_id == $module_id_selected) ? $module_id_url : $module_id_selected_url;


            $modules_array[] =
                array('id' => $module_id_url,
                    'text' => $text);
        }

        $onchange_js = ($onchange_url) ? 'onchange="javascript:location.href = this.value;"' : '';


        return mh_draw_pull_down_menu($name, $modules_array, $module_id_selected_url, 'style="max-width: 30%;" class="form-control"' . $onchange_js);
    }

    static function convert_realdate_to_db($raw_date, $fullday = false)
    {
        //10/31/2011
        $month = (int)substr($raw_date, 0, 2);
        $day = (int)substr($raw_date, 3, 2);
        $year = (int)substr($raw_date, 6, 4);
        return "$year-$month-$day " . (($fullday) ? '23:59:59' : '00:00:00');
    }

    static function is_valid_db_date($db_date)
    {
        // todo
        // validate date
        return ($db_date != '' && $db_date != '0-0-0 00:00:00');
    }

}
