package com.uas.platform.b2b.publicapi.service.impl;

import com.uas.platform.b2b.dao.CommonDao;
import com.uas.platform.b2b.publicapi.model.Turnover;
import com.uas.platform.b2b.publicapi.service.TurnoverService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * ERP查询平台贸易数据接口
 *
 * Created by hejq on 2018-05-31.
 */
@Service
public class TurnoverServiceImpl implements TurnoverService {

    @Autowired
    private CommonDao commonDao;

    /**
     * 美元汇率
     */
    final Double USD_RATE = 6.4;

    /**
     * 港币汇率
     */
    final Double HKD_RATE = 0.811;

    /**
     * 查询年度代采订单交易额
     *
     * @return
     */
    @Override
    public List<Turnover> findSubstitute() {
        String sql = "select date_format(deo_entrydate,'%Y%m') x,deo_currency y,round(sum(dei_totalprice)/10000 ,2) z from purc$deputyorderitems\n" +
                " left join purc$deputyorders on dei_deoid=deo_id where date_format(deo_entrydate,'%Y')=date_format(last_day(now()), '%Y') \n" +
                " and deo_downloadstatus = '已下载' and deo_entrystatus='已提交' group by date_format(deo_entrydate,'%Y%m'),\n" +
                " deo_currency order by date_format(deo_entrydate,'%Y%m'),deo_currency";
        return commonDao.query(sql, Turnover.class);
    }

    /**
     * B2B年度代采订单统计RMB
     *
     * @return
     */
    @Override
    public List<Turnover> findSubstituteByRMB() {
        String sql = " select round(sum(dei_totalprice*case when  deo_currency='USD' then " + USD_RATE + " when deo_currency='HKD' then " + HKD_RATE + " \n" +
                " when deo_currency='RMB' then 1 end)/10000 ,2) SUM \n" +
                " from purc$deputyorderitems left join purc$deputyorders on dei_deoid=deo_id \n" +
                " where date_format(deo_entrydate,'%Y')=date_format(last_day(now()), '%Y')  and deo_downloadstatus = '已下载'\n" +
                " and deo_entrystatus='已提交'";
        return commonDao.query(sql, Turnover.class);
    }

    /**
     * B2B年度交易额折合RMB
     *
     * @return
     */
    @Override
    public List<Turnover> findRMBOrderAmount() {
        String sql = "select round(sum(YFIELD*(case when  a.xfield='USD' then " + USD_RATE + " when a.xfield='HKD' then " + HKD_RATE + " when a.xfield='RMB' then 1 end )),2) SUM from \n" +
                "(select pu_currency xField, round(sum(pd_qty * pd_price)/10000 ,2) yField \n" +
                "from purc$orderitems left join purc$orders on pd_puid=pu_id where \n" +
                "date_format(pu_date,'%Y')=date_format(last_day(now()), '%Y')    group by pu_currency\n" +
                "UNION\n" +
                "select deo_currency xField, round(sum(dei_totalprice)/10000 ,2) yField \n" +
                "from purc$deputyorderitems left join purc$deputyorders on dei_deoid=deo_id where \n" +
                "date_format(deo_entrydate,'%Y')=date_format(last_day(now()), '%Y')  and deo_downloadstatus = '已下载'  and deo_entrystatus='已提交' \n" +
                "group by deo_currency) a";
        return commonDao.query(sql, Turnover.class);
    }

    /**
     * B2B商务本年度交易总金额
     *
     * @return
     */
    @Override
    public List<Turnover> findOrderAmount() {
        String sql = "select a.xField,ROUND(SUM(a.yField)) yField from(\n" +
                "select pu_currency xField, round(sum(pd_qty * pd_price)/10000 ,2) yField \n" +
                "from purc$orderitems left join purc$orders on pd_puid=pu_id where \n" +
                "date_format(pu_date,'%Y')=date_format(last_day(now()), '%Y')  group by pu_currency\n" +
                "UNION\n" +
                "select deo_currency xField, round(sum(dei_totalprice)/10000 ,2) yField \n" +
                "from purc$deputyorderitems left join purc$deputyorders on dei_deoid=deo_id where \n" +
                "date_format(deo_entrydate,'%Y')=date_format(last_day(now()), '%Y')  and deo_entrystatus='已提交' and deo_downloadstatus = '已下载' \n" +
                "group by deo_currency) a  GROUP BY xField";
        return commonDao.query(sql, Turnover.class);
    }

    /**
     * 上月订单交易额汇总
     *
     * @return
     */
    @Override
    public List<Turnover> findLastMonthOrderAmount() {
        String sql = "select a.xField,sum(a.yField) yField from\n" +
                "(select pu_currency xField, round(sum(pd_qty * pd_price)/10000 ,2) yField from purc$orderitems\n" +
                "left join purc$orders on pd_puid=pu_id\n" +
                "where date_format(pu_date,'%Y-%m')=date_format(DATE_ADD(now(), INTERVAL -1 MONTH), '%Y-%m')\n" +
                "and pu_currency is not null group by pu_currency\n" +
                "union\n" +
                "select deo_currency xField, round(sum(deo_orderamount)/10000 ,2) yField from purc$deputyorders\n" +
                "where date_format(deo_entrydate,'%Y-%m') = date_format(DATE_ADD(now(), INTERVAL -1 MONTH), '%Y-%m')\n" +
                "and deo_downloadstatus = '已下载'  and deo_entrystatus='已提交' group by deo_currency) a\n" +
                "group by xField";
        return commonDao.query(sql, Turnover.class);
    }

    /**
     * 上月订单数
     *
     * @return
     */
    @Override
    public List<Turnover> findLastMonthOrderCount() {
        String sql = "select count(pu_code) sum from purc$orders\n" +
                "where date_format(pu_date,'%Y-%m')=date_format(DATE_ADD(now(), INTERVAL -1 MONTH), '%Y-%m')";
        return commonDao.query(sql, Turnover.class);
    }

    /**
     * 本月订单交易额汇总
     *
     * @return
     */
    @Override
    public List<Turnover> findThisMonthOrderAmount() {
        String sql = "select a.xField,sum(a.yField) yField from\n" +
                "(select pu_currency xField, round(sum(pd_qty * pd_price)/10000 ,2) yField from purc$orderitems\n" +
                "left join purc$orders on pd_puid=pu_id\n" +
                "where date_format(pu_date,'%Y-%m')=date_format(now(), '%Y-%m')\n" +
                "and pu_currency is not null group by pu_currency\n" +
                "union\n" +
                "select deo_currency xField, round(sum(deo_orderamount)/10000 ,2) yField from purc$deputyorders\n" +
                "where date_format(deo_entrydate,'%Y-%m') = date_format(now(), '%Y-%m')\n" +
                "and deo_downloadstatus = '已下载'  and deo_entrystatus='已提交' group by deo_currency) a\n" +
                "group by xField";
        return commonDao.query(sql, Turnover.class);
    }

    /**
     * 本月订单数
     *
     * @return
     */
    @Override
    public List<Turnover> findThisMonthOrderCount() {
        String sql = "select count(pu_code) sum from purc$orders\n" +
                "where date_format(pu_date,'%Y-%m')=date_format(now(), '%Y-%m')";
        return commonDao.query(sql, Turnover.class);
    }
}
