'use strict';

const Service = require('egg').Service;
const objExclude = require('../util/common').objExclude;
const objToQuery = require('../util/common').objToQuery;

class ContractService extends Service {
  async analysis(data) {
    const where = objToQuery(data, 'contract');
    const sql = `
      select contract.*, user.name as manager_name from contract, user where contract.manager_id = user.id
      ${where !== '' ? `and ${where}` : ''}
    `;
    const result = await this.app.mysql.query(sql);
    return result;
  }
  async count() {
    const result = await this.app.mysql.query('select count(*) as total from contract');
    return result;
  }
  async add(data) {
    const result = await this.app.mysql.insert('contract', data);
    return result;
  }
  async update(data) {
    const result = await this.app.mysql.update('contract', data);
    return result;
  }
  async find(id) {
    // const user = await this.app.mysql.get('contract', { id });
    const user = await this.app.mysql.query(`
      select id, customer_id, manager_id, number, name, value, product, invoice, date_format(invoice_time, '%Y-%m-%d') as invoice_time,
        method, date_format(method_time, '%Y-%m-%d') as method_time, other_expenses, remark, type
      from contract where id = ?
    `, [ id ]);
    const result = user && user.length > 0 ? user[0] : {};
    return result;
  }
  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([ 'contract', 'customer' ], { columns: [ 'customer.name as cname', 'contract.type' ], where: other, limit, offset, orders: [[ 'type', 'asc' ]] });
    const where = objToQuery(other, 'contract');
    const sql = `
      select contract.*, customer.name as customer_name
      from contract left join customer on (contract.customer_id = customer.id)
      ${where !== '' ? `where ${where}` : ''}
      order by contract.type asc
      limit ${offset},${limit}
    `;
    const list = await this.app.mysql.query(sql);
    return list;
  }
  async remove(data) {
    const result = await this.app.mysql.delete('contract', data);
    return result;
  }
}

module.exports = ContractService;