project.js
4.31 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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
'use strict';
const Service = require('egg').Service;
const objExclude = require('../util/common').objExclude;
const moment = require('moment');
class ProjectService extends Service {
async user_analysis(id, date) {
const sql = `
select pj.*, user.name as manager_name from (
select a.*,b.p_value, b.p_other_expenses,b.p_invoice from
(
select
project.number, project.name, project.manager_id, project.create_time, contract.value as s_value, contract.other_expenses as s_other_expenses, contract.invoice as s_invoice
from project, contract where project.sale_contract_id = contract.id
) a
left join
(
select
project.number, project.name, project.manager_id, project.create_time, contract.value as p_value, contract.other_expenses as p_other_expenses, contract.invoice as p_invoice
from project, contract where project.purchase_contract_id = contract.id
) b
on a.number = b.number
) pj, user
where pj.manager_id = user.id
and pj.manager_id = ?
${date !== undefined && date !== null ? ` and pj.create_time like '%${date}%'` : ''}
`;
const result = await this.app.mysql.query(sql, [ id ]);
return result;
}
async analysis(date) {
// const sql = `
// 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
// `;
const sql = `
select pj.*, user.name as manager_name from (
select a.*,b.p_value, b.p_other_expenses,b.p_invoice from
(
select
project.number, project.name, project.manager_id, project.create_time, contract.value as s_value, contract.other_expenses as s_other_expenses, contract.invoice as s_invoice
from project, contract where project.sale_contract_id = contract.id
) a
left join
(
select
project.number, project.name, project.manager_id, project.create_time, contract.value as p_value, contract.other_expenses as p_other_expenses, contract.invoice as p_invoice
from project, contract where project.purchase_contract_id = contract.id
) b
on a.number = b.number
) pj, user
where pj.manager_id = user.id
${date !== undefined && date !== null ? ` and pj.create_time like '%${date}%'` : ''}
`;
const result = await this.app.mysql.query(sql);
return result;
}
async count() {
const result = await this.app.mysql.query('select count(*) as total from project');
return result;
}
async add(data) {
const value = data;
value.create_time = moment().format('YYYY-MM-DD HH:mm:ss');
const todayCountResult = await this.app.mysql.query('select count(*) as count from project where create_time like ?', [ `%${moment().format('YYYY-MM-DD')}%` ]);
const todayCount = todayCountResult && todayCountResult.length > 0 ? todayCountResult[0].count : 0;
const count = Number(todayCount) + 1;
const number = `SF${moment().format('YYYYMMDD')}${count > 9 ? count : `0${count}`}`;
value.number = number;
const result = await this.app.mysql.insert('project', value);
return result;
}
async update(data) {
const value = data;
value.update_time = moment().format('YYYY-MM-DD HH:mm:ss');
const result = await this.app.mysql.update('project', value);
return result;
}
async find(id) {
const user = await this.app.mysql.get('project', { id });
return user;
}
async list(data) {
const { current = 1, pageSize = 10 } = data;
const other = objExclude(data, [ 'current', 'pageSize' ]);
const limit = Number(pageSize);
const offset = Number((current - 1) * pageSize);
const list = await this.app.mysql.select('project', { where: other, limit, offset, orders: [[ 'number', 'asc' ]] });
return list;
}
async remove(data) {
const result = await this.app.mysql.delete('project', data);
return result;
}
}
module.exports = ProjectService;