'use strict';

const Service = require('egg').Service;

class StatisticService extends Service {
  async count() {
    const result = await this.app.mysql.query(`
      select sum(sale_contract_count) sale_contract_count, sum(purchase_contract_count) purchase_contract_count, sum(customer_count) customer_count, sum(supplier_count) supplier_count from (
        select count(*) as sale_contract_count, 0 as purchase_contract_count, 0 as customer_count, 0 as supplier_count from contract where type = 0
        union all
        select 0 as sale_contract_count, count(*) as purchase_contract_count, 0 as customer_count, 0 as supplier_count from contract where type = 1
        union all
        select 0 as sale_contract_count, 0 as purchase_contract_count, count(*) as customer_count, 0 as supplier_count from customer where type = 0
        union all
        select 0 as sale_contract_count, 0 as purchase_contract_count, 0 as customer_count, count(*) as supplier_count from customer where type = 1
      ) t;
    `);
    return result && result.length > 0 ? result[0] : {};
  }
  async projectAnalysis() {
    const result = await this.app.mysql.query(`
      select
        project.number,
        project.name,
        s_contract.value as s_value,
        s_contract.other_expenses as s_other_expenses,
        s_contract.invoice as s_invoice,
        p_contract.value as p_value,
        p_contract.other_expenses as p_other_expenses,
        p_contract.invoice as p_invoice
      from project, contract as s_contract, contract as p_contract
      where project.sale_contract_id = s_contract.id and project.purchase_contract_id = p_contract.id
    `);
    return result;
  }
}

module.exports = StatisticService;