<?php
namespace app\index\model;
use app\index\model\MySqlite;

class Commission extends MySqlite
{
    public function __construct()
    {
        parent::__construct($tablename = '');
        if ($tablename) {
            $this->tablename = $tablename;
        } else {
            $this->tablename = uncamelize(basename(__CLASS__));
        }
    }

    /**
     * @override
     */
    public function list($where='')
    {
        $res = parent::list($where);
        // var_dump($res);
        // exit;
        return $res;
    }

    /**
     * total_profit
     */
    public function totalProfit($name, $month)
    {
        $sql = "select sum(amount*(price-base_price)-agency-shipping-project) as total_profit from commission where seller='$name' and month='$month' and type=0";
        $res = $this->query($sql)->fetchArray(SQLITE3_ASSOC);
        return $res['total_profit'];
    }

    /**
     * 特批 特批1和特批2 汇总
     */
    public function tepi($name, $month)
    {
        $sql_tepi1 = "select sum((amount*price-agency-shipping)*(CASE WHEN (price/base_price)<1.05 THEN 0.01 WHEN (price/base_price)<1.1 THEN 0.015 ELSE 0.02 END)) as tp1 from commission where seller='$name' and month='$month' and type=1";
        // echo $sql_tepi1 . '<br>';
        $res_tepi1 = $this->query($sql_tepi1)->fetchArray(SQLITE3_ASSOC);
        // var_dump($res_tepi1);
        // slqite3 整数乘 1.0 可强制使用浮点除法  php 调用是 报无 floor 函数
        // stackoverflow 找到的解决办法 (CEIL and FLOOR in SQLite)
        // cast ( x as int ) - ( x < cast ( x as int )) 
        // 针对这个没负数情况简化为  cast ( x as int ) 
        // eg:select sum( (amount*price-agency-shipping) * ((cast( (((price/base_price - 1)/0.1)*0.01+0.01)* 100 as int))*1.0/100) ) as tp2 from commission where seller='刘莹' and type=2;
        $sql_tepi2 = "select sum( (amount*price-agency-shipping) * ((cast( (((price/base_price - 1)/0.1)*0.01+0.01)* 100 as int))*1.0/100) ) as tp2 from commission where seller='$name' and month='$month' and type=2;";
        // echo $sql_tepi2 . "<br/>";
        $res_tepi2 = $this->query($sql_tepi2)->fetchArray(SQLITE3_ASSOC);
        // var_dump($res_tepi2);
        // // exit;
        return $res_tepi1['tp1'] + $res_tepi2['tp2'];
    }
}