<?php

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

class commissionList extends WP_List_Table
{
    function __construct()
    {
    }

    public function print_column_headers()
    {
        echo '<th rowspan="2">部门</th>';
        echo '<th rowspan="2">员工</th>';
        echo '<th colspan="2">目标业绩</th>';
        echo '<th colspan="3">应收业绩</th>';
        echo '<th colspan="3">实收业绩</th>';
        echo '<th colspan="3">前期未收</th>';
        echo '<th rowspan="2">到职日期</th>';
        echo '</tr>';
        echo '<tr>';
        echo '<th>金额(万)</th>';
        echo '<th>达成率%</th>';
        echo '<th>买卖</th>';
        echo '<th>租赁</th>';
        echo '<th>合计</th>';
        echo '<th>买卖</th>';
        echo '<th>租赁</th>';
        echo '<th>合计</th>';
        echo '<th>买卖</th>';
        echo '<th>租赁</th>';
        echo '<th>合计</th>';
    }

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

    function get_columns()
    {
        $columns['organization_name'] = '部门';
        $columns['consultant_name'] = '员工';
        $columns['target_amount'] = '金额(万)';
        $columns['achieving_rate'] = '达成率%';

        $columns['business'] = '应收业绩买卖';
        $columns['lease'] = '应收业绩租赁';
        $columns['accounts'] = '应收业绩合计';

        $columns['businessPaid'] = '实收业绩买卖';
        $columns['leasePaid'] = '实收业绩租赁';
        $columns['paid'] = '实收业绩合计';

        $columns['unBusinessPaid'] = '前期未收买卖';
        $columns['unLeasePaid'] = '实收业绩租赁';
        $columns['unPaid'] = '前期未收租赁';

        $columns['entry_time'] = '到职日期';

        return $columns;
    }

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

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

        $year = date('Y');
        $month = null;
        if (isset($_REQUEST['year']) && $_REQUEST['year'] != -1) {
            $year = $_REQUEST['year'];
            if (isset($_REQUEST['month']) && $_REQUEST['month'] != -1) {
                $month = $_REQUEST['month'];
            }
        }

        $commission_time_sql = " where time >= '" . $year . "-1-1' and time < DATE_SUB('" . $year . "-1-1', INTERVAL -1 YEAR)";
        $quota_time_sql = " where year = " . $year;
        if ($month != null) {
            $quota_time_sql .= " and month = " . $month;
            $commission_time_sql = " where time >= '" . $year . "-" . $month . "-1' and time < DATE_SUB('" . $year . "-" . $month . "-1', INTERVAL -1 MONTH)";
        }

        $sql = "select *,(accounts-paid) as unPaid," .
            "(business-businessPaid) as unBusinessPaid," .
            "(lease-leasePaid) as unLeasePaid" .
            " from (select consultantId,sum(accounts) as accounts,sum(case when t1.type = 0 then accounts else 0 end) business," .
            "sum(case when t1.type = 1 then accounts else 0 end) lease from (select consultantId,accounts,tcn.type from tospur_commission tcn" .
            " left join tospur_commission_log tcl on tcl.commissionId = tcn.id" .
            $commission_time_sql . " group by consultantId ) t1 group by consultantId) should" .
            " left join (select consultantId,sum(paid) as paid,sum(case when tcn.type = 0 then paid else 0 end) businessPaid," .
            "sum(case when tcn.type = 1 then paid else 0 end) leasePaid from tospur_commission tcn" .
            " left join tospur_commission_log tcl on tcl.commissionId = tcn.id" .
            $commission_time_sql .
            " group by consultantId) actual on actual.consultantId = should.consultantId" .
            " left join (select id as consultantId,name as consultant_name,time as entry_time,subsidiaryId from tospur_consultant) tc" .
            " on tc.consultantId = should.consultantId" .
            " left join (select id as organization_id,name as organization_name from tospur_organization) torg on torg.organization_id = tc.subsidiaryId";

        $sql .= " left join (select consultantId,sum(quota) as target_amount from tospur_quota" .
            $quota_time_sql .
            " group by consultantId) tq on tq.consultantId = should.consultantId where 1 = 1";

        if (isset($_REQUEST['organization']) && $_REQUEST['organization'] != -1) {
            $sql = $sql . " and torg.organization_id = " . $_REQUEST['organization'];
        }
        if (isset($_REQUEST['search_consultant_name']) && $_REQUEST['search_consultant_name'] != null) {
            $sql = $sql . " and tc.consultant_name like '%" . $_REQUEST['search_consultant_name'] . "%'";
        }

        $result = $wpdb->get_results($sql);
        $data = array();
        foreach ($result as $key => $value) {
            $target_amount = 0;
            $achieving_rate = 0;
            if ($value->target_amount) {
                $target_amount = $value->target_amount;
                $achieving_rate = round((($value->paid) / $target_amount) * 100, 2);
            }
            $data[$key] = array(
                'organization_name' => $value->organization_name,
                'consultant_name' => $value->consultant_name,
                'target_amount' => round($target_amount / 10000, 2),
                'achieving_rate' => $achieving_rate . '%',
                'business' => round($value->business / 10000, 2),
                'lease' => round($value->lease / 10000, 2),
                'accounts' => round($value->accounts / 10000, 2),
                'businessPaid' => round($value->businessPaid / 10000, 2),
                'leasePaid' => round($value->leasePaid / 10000, 2),
                'paid' => round($value->paid / 10000, 2),
                'unBusinessPaid' => round($value->unBusinessPaid / 10000, 2),
                'unLeasePaid' => round($value->unLeasePaid / 10000, 2),
                'unPaid' => round($value->unPaid / 10000, 2),
                'entry_time' => $value->entry_time
            );
        }

        $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_commissionList()
{
    $context = array();
    $context['req'] = $_REQUEST;
    $context['year'] = date('Y');
    Timber::render("commissionList.html", $context);
}

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

?>
