'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;