statistic.js
1.68 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
'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;