<?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="newSell">' . $result->newSell . '</td>
                    <td class="newLease">' . $result->newLease . '</td>
                    <td class="newBuy">' . $result->newBuy . '</td>
                    <td class="newTenant">' . $result->newTenant . '</td>
                    <td class="business">' . $result->business . '</td>
                    <td class="lease">' . $result->lease . '</td>
                    <td class="dealBusiness">' . $result->dealBusiness . '</td>
                    <td class="dealLease">' . $result->dealLease . '</td>
                    <td class="quota">' . round($result->quota / 10000, 2) . '</td>
                    <td class="achieving_rate">' . round($achieving_rate, 2) . '%</td>
                    <td class="accounts">' . round($result->accounts / 10000, 2) . '</td>
                    <td class="paid">' . round($result->paid / 10000, 2) . '</td>
                    <td class="stockSellH">' . $result->stockSellH . '</td>
                    <td class="stockLeaseH">' . $result->stockLeaseH . '</td>
                    <td class="stockSellC">' . $result->stockSellC . '</td>
                    <td class="stockLeaseC">' . $result->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['newSell'] = '出售';
        $columns['newLease'] = '出租';

        $columns['newBuy'] = '求购';
        $columns['newTenant'] = '求租';

        $columns['business'] = '买卖';
        $columns['lease'] = '租赁';

        $columns['dealBusiness'] = '买卖';
        $columns['dealLease'] = '租赁';

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

        $columns['accounts'] = '金额(万)';
        $columns['paid'] = '金额(万)';

        $columns['stockSellH'] = '出售';
        $columns['stockLeaseH'] = '出租';

        $columns['stockSellC'] = '求购';
        $columns['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 q.*,sum(sb2.quota) as quota,(sum(q.paid)/sum(sb2.quota)) as achieving_rate,
sum(sb2.stockSellH) as stockSellH,sum(sb2.stockLeaseH) as stockLeaseH,
sum(sb2.stockSellC) as stockSellC,sum(sb2.stockLeaseC) as stockLeaseC
 from (select p.id,p.name,p.subsidiaryId,p.organization,
sum(p.newSell) as newSell,sum(p.newLease) as newLease,sum(p.newBuy) as newBuy,sum(p.newTenant) as newTenant,
sum(p.business) as business,sum(p.lease) as lease,sum(p.dealBusiness) as dealBusiness,sum(p.dealLease) as dealLease,
sum(p.accounts) as accounts,sum(p.paid) as paid
 from (select tc.name,tc.subsidiaryId,torg.Name as organization,sb.* 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 group by p.consultantId) q
 left join statistics_business sb2 on q.id = sb2.id";
        $progress_sql = $sql;
        $sql .= " group by q.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,
                'newSell' => $value->newSell,
                'newLease' => $value->newLease,
                'newBuy' => $value->newBuy,
                'newTenant' => $value->newTenant,
                'business' => $value->business,
                'lease' => $value->lease,
                'dealBusiness' => $value->dealBusiness,
                'dealLease' => $value->dealLease,
                'quota' => round($value->quota / 10000, 2),
                'achieving_rate' => round($achieving_rate, 2) . '%',
                'accounts' => round($value->accounts / 10000, 2),
                'paid' => round($value->paid / 10000, 2),
                'stockSellH' => $value->stockSellH,
                'stockLeaseH' => $value->stockLeaseH,
                'stockSellC' => $value->stockSellC,
                'stockLeaseC' => $value->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();
}

?>
