<?php

if (!class_exists('WP_List_Table')) {
    require_once(ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}

global $progress_sql;

class progressList extends WP_List_Table
{
    function __construct()
    {
    }

    public function display()
    {
        $singular = $this->_args['singular'];
        $this->display_tablenav('top');
        ?>
        <table class="wp-list-table <?php echo implode(' ', $this->get_table_classes()); ?>">
            <thead>
            <tr>
                <?php $this->print_column_headers(); ?>
            </tr>
            </thead>
            <tbody id="the-list"<?php
            if ($singular) {
                echo " data-wp-lists='list:$singular'";
            } ?>>
            <?php $this->display_rows_or_placeholder(); ?>
            <?php
            if ( $this->has_items() ) {
                global $progress_sql;
                $result = DBManager::get_row($progress_sql);
                $achieving_rate = 0;
                if ($result->achieving_rate) {
                    $achieving_rate = $result->achieving_rate * 100;
                }
                echo '<tr>
                    <td>合计</td>
                    <td class="sum_newSell">' . $result->sum_newSell . '</td>
                    <td class="sum_newLease">' . $result->sum_newLease . '</td>
                    <td class="sum_newBuy">' . $result->sum_newBuy . '</td>
                    <td class="sum_newTenant">' . $result->sum_newTenant . '</td>
                    <td class="sum_business">' . $result->sum_business . '</td>
                    <td class="sum_lease">' . $result->sum_lease . '</td>
                    <td class="sum_dealBusiness">' . $result->sum_dealBusiness . '</td>
                    <td class="sum_dealLease">' . $result->sum_dealLease . '</td>
                    <td class="sum_quota">' . round($result->sum_quota / 10000, 2) . '</td>
                    <td class="achieving_rate">' . round($achieving_rate, 2) . '%</td>
                    <td class="sum_accounts">' . round($result->sum_accounts / 10000, 2) . '</td>
                    <td class="sum_paid">' . round($result->sum_paid / 10000, 2) . '</td>
                    <td class="sum_stockSellH">' . $result->sum_stockSellH . '</td>
                    <td class="sum_stockLeaseH">' . $result->sum_stockLeaseH . '</td>
                    <td class="sum_stockSellC">' . $result->sum_stockSellC . '</td>
                    <td class="sum_stockLeaseC">' . $result->sum_stockLeaseC . '</td>
                </tr>';
            }
            ?>
            </tbody>
        </table>
    <?php
    }

    public function print_column_headers()
    {
        echo '<th rowspan="2">部门</th>' .
            '<th colspan="2">新增房源</th>' .
            '<th colspan="2">新增客源</th>' .
            '<th colspan="2">带看</th>' .
            '<th colspan="2">成交笔数</th>' .
            '<th colspan="2">目标业绩</th>' .
            '<th>应收业绩</th>' .
            '<th>实收业绩</th>' .
            '<th colspan="2">房源库存量</th>' .
            '<th colspan="2">客源库存量</th>' .
            '</tr><tr>' .
            '<th>出售</th><th>出租</th>' .
            '<th>求购</th><th>求租</th>' .
            '<th>买卖</th><th>租赁</th>' .
            '<th>买卖</th><th>租赁</th>' .
            '<th>金额(万)</th><th>达成率%</th>' .
            '<th>金额(万)</th>' .
            '<th>金额(万)</th>' .
            '<th>出售</th><th>出租</th>' .
            '<th>求购</th><th>求租</th>';
    }

    function column_default($item, $column_name)
    {
        switch ($column_name) {
            default:
                return $item[$column_name];
        }
    }

    function get_columns()
    {
        $columns['organization'] = '部门';
        $columns['sum_newSell'] = '出售';
        $columns['sum_newLease'] = '出租';

        $columns['sum_newBuy'] = '求购';
        $columns['sum_newTenant'] = '求租';

        $columns['sum_business'] = '买卖';
        $columns['sum_lease'] = '租赁';

        $columns['sum_dealBusiness'] = '买卖';
        $columns['sum_dealLease'] = '租赁';

        $columns['sum_quota'] = '金额(万)';
        $columns['achieving_rate'] = '达成率%';

        $columns['sum_accounts'] = '金额(万)';
        $columns['sum_paid'] = '金额(万)';

        $columns['sum_stockSellH'] = '出售';
        $columns['sum_stockLeaseH'] = '出租';

        $columns['sum_stockSellC'] = '求购';
        $columns['sum_stockLeaseC'] = '求租';

        return $columns;
    }

    function prepare_items()
    {
        global $progress_sql;
        $per_page = 10;
        $columns = $this->get_columns();
        $hidden = array();

        $this->_column_headers = array($columns, $hidden);

        $sql = "select p.id,p.consultantId,p.name as consultant_name,p.subsidiaryId,p.organization,p.date,
sum(p.newSell) as sum_newSell,sum(p.newLease) as sum_newLease,sum(p.newBuy) as sum_newBuy,sum(p.newTenant) as sum_newTenant,
sum(p.business) as sum_business,sum(p.lease) as sum_lease,sum(p.dealBusiness) as sum_dealBusiness,sum(p.dealLease) as sum_dealLease,
sum(p.quota) as sum_quota,sum(p.accounts) as sum_accounts,sum(p.paid) as sum_paid,(sum(p.paid)/sum(p.quota)) as achieving_rate,
sum(p.stockSellH) as sum_stockSellH,sum(p.stockLeaseH) as sum_stockLeaseH,sum(p.stockSellC) as sum_stockSellC,sum(p.stockLeaseC) as sum_stockLeaseC
 from (SELECT tc.name,sb.*,tc.subsidiaryId,torg.Name as organization FROM statistics_business sb
 left join tospur_consultant tc on sb.consultantId = tc.id
 left join tospur_organization torg on tc.subsidiaryId = torg.Id
 where 1 = 1";

        $search_type = $_REQUEST['search_type'];
        if (isset($_REQUEST[$search_type]) && $search_type == 'search_consultant_name') {
            $sql .= " and tc.name like '%" . $_REQUEST[$search_type] . "%'";
        }
        if (isset($_REQUEST[$search_type]) && $search_type == 'organization' && $_REQUEST[$search_type] != '-1') {
            $sql .= " and tc.subsidiaryId = " . $_REQUEST[$search_type];
        }
        $statistical_time = $_REQUEST['statistical_time'];

        if (isset($_REQUEST[$statistical_time]) && $statistical_time == 'day') {
            $sql .= " and sb.date = '" . $_REQUEST[$statistical_time] . "'";
        }
        if (isset($_REQUEST[$statistical_time]) && $statistical_time == 'week') {
            $sql .= " and sb.date >= '" . $_REQUEST[$statistical_time] . "' and sb.date < DATE_SUB('" . $_REQUEST[$statistical_time] . "', INTERVAL -1 WEEK)";
        }
        if (isset($_REQUEST[$statistical_time]) && $statistical_time == 'month') {
            $sql .= " and sb.date >= '" . $_REQUEST['year'] . "-" . $_REQUEST['month'] . "-1' and sb.date < DATE_SUB('" . $_REQUEST['year'] . "-" . $_REQUEST['month'] . "-1', INTERVAL -1 MONTH)";
        }
        if (isset($_REQUEST[$statistical_time]) && $statistical_time == 'year') {
            $sql .= " and sb.date >= '" . $_REQUEST['year'] . "-1-1' and sb.date < DATE_SUB('" . $_REQUEST['year'] . "-1-1', INTERVAL -1 YEAR)";
        }

        $sql .= " order by sb.id desc) p";
        $progress_sql = $sql;
        $sql .= " group by p.subsidiaryId";
        $result = DBManager::get_results($sql);
        $data = array();
        foreach ($result as $key => $value) {
            $achieving_rate = 0;
            if ($value->achieving_rate) {
                $achieving_rate = $value->achieving_rate * 100;
            }
            $data[$key] = array(
                'organization' => $value->organization,
                'sum_newSell' => $value->sum_newSell,
                'sum_newLease' => $value->sum_newLease,
                'sum_newBuy' => $value->sum_newBuy,
                'sum_newTenant' => $value->sum_newTenant,
                'sum_business' => $value->sum_business,
                'sum_lease' => $value->sum_lease,
                'sum_dealBusiness' => $value->sum_dealBusiness,
                'sum_dealLease' => $value->sum_dealLease,
                'sum_quota' => round($value->sum_quota / 10000, 2),
                'achieving_rate' => round($achieving_rate, 2) . '%',
                'sum_accounts' => round($value->sum_accounts / 10000, 2),
                'sum_paid' => round($value->sum_paid / 10000, 2),
                'sum_stockSellH' => $value->sum_stockSellH,
                'sum_stockLeaseH' => $value->sum_stockLeaseH,
                'sum_stockSellC' => $value->sum_stockSellC,
                'sum_stockLeaseC' => $value->sum_stockLeaseC
            );
        }

        $current_page = $this->get_pagenum();

        $total_items = count($data);
        $total_pages = ceil($total_items / $per_page);

        if ($_REQUEST['paged'] > $total_pages) {
            $current_page = $total_pages;
        }

        $data = array_slice($data, (($current_page - 1) * $per_page), $per_page);

        $this->items = $data;

        $this->set_pagination_args(array(
            'total_items' => $total_items,                  //WE have to calculate the total number of items
            'per_page' => $per_page,                     //WE have to determine how many items to show on a page
            'total_pages' => ceil($total_items / $per_page)   //WE have to calculate the total number of pages
        ));
    }
}

function function_progressList()
{
    $context = array();
    $context['req'] = $_REQUEST;
    Timber::render("progressList.html", $context);
}

function addProgressTable()
{
    $_SERVER['REQUEST_URI'] = remove_query_arg('_wp_http_referer', $_SERVER['REQUEST_URI']);
    $progressList = new progressList();
    $progressList->prepare_items();
    $progressList->display();
}

?>
