const system = require("../../system");
const ServiceBase = require("../sve.base");
const moment = require("moment");
const uuidv4 = require('uuid/v4');
const fs = require("fs");
const xlsx = require('node-xlsx');


class EsettleofflineService extends ServiceBase {
  constructor() {
    super(ServiceBase.getDaoName(EsettleofflineService));

    this.restClient = system.getObject("util.restClient");
    this.esettleofflineitemDao = system.getObject("db.esettleofflineitemDao")

    this.idcardClient = system.getObject("util.idcardClient");
  }

  async saveSettle(settle) {
    var self = this;
    var opuser = settle.opid;
    var rs = await this.parseItems(opuser, settle.fileurl, settle.pay_complete_time);
    if (!rs.code) {
      return rs;
    }
    var itemList = rs.list;
    return self.db.transaction(async function (t) {
      if (settle.id) {
        await self.update(settle, t);
      } else {
        settle = await self.dao.create(settle, t);
      }

      await self.esettleofflineitemDao.customExecAddOrPutSql("DELETE FROM c_esettle_offline_item where esettleoffline_id = " + settle.id);

      for (var item of itemList) {
        item.month = settle.month;
        item.esettleoffline_id = settle.id;
      }

      await self.esettleofflineitemDao.model.bulkCreate(itemList, { transaction: t });

      var sql = "INSERT INTO c_esettle_offline_log (esettleoffline_id, url, opuser, created_at, updated_at) VALUES ('" + settle.id + "', '" + settle.fileurl + "', '" + opuser + "', NOW(), NOW()) ";
      await self.dao.customExecAddOrPutSql(sql);
      return rs;
    });
  }

  async pageByCondition(params) {
    var currentPage = Number(params.currentPage || 0);
    var pageSize = Number(params.pageSize || 10);

    var where = {};

    if (params.ecompanyName) {
      where.ecompanyName = {
        [this.db.Op.like]: "%" + params.ecompanyName + "%"
      };
    }

    if (params.owner_name) {
      where.owner_name = {
        [this.db.Op.like]: "%" + params.owner_name + "%"
      };
    }

    var amtBegin = params.amtBegin;
    var amtEnd = params.amtEnd;

    if (amtBegin && amtEnd) {
      where.amt = {
        [this.db.Op.between]: [amtBegin, amtEnd]
      };
    } else if (amtBegin && !amtEnd) {
      where.amt = {
        [this.db.Op.gte]: amtBegin
      };
    } else if (!amtBegin && amtEnd) {
      where.amt = {
        [this.db.Op.lte]: amtEnd
      };
    }

    this.addWhereTime(where, 'pay_complete_time', params.payBegin, params.payEnd, true);

    var orderby = [
      ["pay_complete_time", 'desc']
    ];

    var attributes = ["id", "ecompany_id", "ecompanyName", "month", "pay_complete_time", "sourceType",
      "amt", "actual_amt", "service_rate", "service_tax", "added_value_tax", "open_bank", "bankno"
    ];

    var page = await this.getPageList(currentPage, pageSize, where, orderby, attributes);
    if (page && page.rows) {
      for (var row of page.rows) {
        this.handleDate(row, ["pay_complete_time"], "YYYY-MM-DD", -8);
      }
      system.f2y4list(page.rows, ["amt", "actual_amt", "service_tax", "added_value_tax"]);
    }
    return page;
  }

  async parseItems(userId, ossurl, pay_complete_time) {
    var result = {
      code: 0,
      msg: "",
      list: [],
    };

    var isValid = false;
    // var isValid = true;
    // if(pay_complete_time) {
    //   pay_complete_time = Number(pay_complete_time.replace("-", "").replace("-", ""));
    //   isValid = pay_complete_time >= 20190601;
    // }

    var regexNumber = /^[0-9]*$/;
    var dataList = [];
    var filePath = "/tmp/esettle_offline_" + userId + ".xlsx";
    try {
      await this.restClient.execDownload("'" + ossurl + "'", filePath);

      var sheets = xlsx.parse(filePath);
      fs.unlink(filePath, e => {
        console.log("fail");
      });

      if (!sheets || sheets.length == 0) {
        result.msg = "明细excel为空";
        return result;
      }

      var sheet = sheets[0];
      if (!sheet || !sheet.data || sheet.data.length == 0) {
        result.msg = "明细无内容行";
        return result;
      }

      var rows = sheet.data;
      var datalist = [];
      var erridnoLines = [];

      for (var idx in rows) {
        if (idx == 0) {
          continue;
        }

        var cells = rows[idx];
        var rowNum = Number(idx) + 1;

        var cellLength = cells.length;
        if (cellLength == 0) {
          continue;
        }
        if (cellLength < 8) {
          result.msg = "excel第" + rowNum + "行，请至少填写姓名、身份证、银行卡号、应发金额、实发金额";
          return result;
        }

        var data = {};
        data.id_name = this.trim(cells[0]);
        data.id_no = this.trim(cells[1]).toUpperCase();
        data.acc_no = this.trim(cells[2]);
        data.open_bank = this.trim(cells[3]);
        data.amt = system.y2f(this.trim(cells[4]) || 0);
        data.income_rate = this.trim(cells[5]);
        data.income_tax = system.y2f(this.trim(cells[6]));
        data.actual_amt = system.y2f(this.trim(cells[7]) || 0);
        data.rowNum = rowNum;

        if (cellLength >= 9) {
          data.service_tax = system.y2f(this.trim(cells[8]));
        }
        if (cellLength >= 13) {
          data.added_value_rate = this.trim(cells[12]);
        }
        if (cellLength >= 10) {
          data.added_value_tax = system.y2f(this.trim(cells[9]));
        }
        if (cellLength >= 11) {
          data.total_amt = system.y2f(this.trim(cells[10]));
        }
        if (cellLength >= 12) {
          data.remark = this.trim(cells[11]);
        }

        if (!data.amt && !data.actual_amt && !data.total_amt) {
          continue;
        }

        if (!data.id_name) {
          result.msg = "excel第" + rowNum + "行，请填写姓名";
          return result;
        }

        if (!data.id_no) {
          result.msg = "excel第" + rowNum + "行，请填写身份证";
          return result;
        }

        if (!data.amt) {
          result.msg = "excel第" + rowNum + "行，请填写应发金额";
          return result;
        }

        if (!data.actual_amt) {
          result.msg = "excel第" + rowNum + "行，请填写实发金额";
          return result;
        }

        if (!data.total_amt) {
          result.msg = "excel第" + rowNum + "行，请填写总付款额";
          return result;
        }

        if (data.amt > 1000 && !data.service_tax) {
          result.msg = "excel第" + rowNum + "行，请填写服务费";
          return result;
        }

        if (data.added_value_tax && !data.added_value_rate) {
          result.msg = "excel第" + rowNum + "行，请填写增值税率";
          return result;
        }

        if (!await this.idcardClient.checkIDCard(data.id_no)) {
          erridnoLines.push(rowNum);
          data.errormsg = "身份证号码验证失败";
        }

        if (!regexNumber.test(data.acc_no)) {
          result.msg = "excel第" + rowNum + "行，银行卡号格式错误，请填写纯数字";
          return result;
        }

        datalist.push(data);
      }

      if (datalist.length == 0) {
        result.msg = "请填写明细数据";
        return result;
      }

      if (isValid && erridnoLines.length > 0) {
        result.msg = "excel第【" + erridnoLines.join("、") + "】行，身份证号格式错误！";
        return result;
      }

      var unSignLines = await this.checkSined(datalist);

      result.code = 1;
      result.list = datalist;
      if (unSignLines && unSignLines.length > 0) {
        var endstr = ""
        if (unSignLines.length >= 100) {
          endstr = "...";
        }
        result.unsignMsg = "excel第【" + unSignLines.join("、") + endstr + "】行，未进行电子签！";
      }
      return result;
    } catch (error) {
      console.log(error.stack);
      result.msg = "您的网络忙， 请稍后重试";
      return result;
    }
  }

  async checkSined(datalist) {
    var personsSignArr = [];
    var banknoArr = [];
    var unSignLines = [];

    if (!datalist || datalist.length == 0) {
      return [];
    }

    for (var data of datalist) {
      personsSignArr.push(data.id_no.toString());
      banknoArr.push(data.acc_no.toString());
      if (unSignLines.length <= 100) {
        unSignLines.push(data.rowNum);
      }
    }

    if (!personsSignArr || personsSignArr.length == 0 || !banknoArr || banknoArr.length == 0) {
      return unSignLines;
    }

    var sql = [];
    sql.push("SELECT t1.id, t1.`personsSign`, t1.`bankno` FROM `p_user_eaccount` t1");
    sql.push("INNER JOIN `c_econtract` t2 ON t1.`id` = t2.`usereaccount_id` ");
    sql.push("WHERE t2.`eflowstatus` = '2' ");
    sql.push("AND t1.`personsSign` IN (:personsSign) ");
    sql.push("AND t1.`bankno` IN (:bankno)");
    sql.push("GROUP BY t1.`id`");

    var map = [];

    var list = await this.dao.customQuery(sql.join(" "), { personsSign: personsSignArr, bankno: banknoArr });
    if (!list || list.length == 0) {

      return unSignLines;
    }
    unSignLines = [];
    for (var item of list) {
      map[item.personsSign + "_" + item.bankno] = item.id;
    }

    for (var data of datalist) {
      var item = map[data.id_no + "_" + data.acc_no];
      if (!item && unSignLines.length <= 100) {
        unSignLines.push(data.rowNum);
      }
    }
    return unSignLines;
  }

  async statOfflineSettle(params) {
    var sql = [];
    sql.push("SELECT");
    sql.push("SUM(t1.amt) AS amtCount,");
    sql.push("SUM(t1.actual_amt) AS actualAmtCount,");
    sql.push("SUM(t1.service_tax) AS serviceTaxCount,");
    sql.push("SUM(t1.added_value_tax) AS addedValueTaxCount,");
    sql.push("COUNT(DISTINCT t2.ecompany_id) AS busiCount,");
    sql.push("COUNT(DISTINCT t1.id_no) AS idNoCount");
    sql.push("FROM c_esettle_offline_item t1");
    sql.push("INNER JOIN c_esettle_offline t2 ON t1.esettleoffline_id = t2.id");
    sql.push("WHERE 1 = 1");

    var where = {};
    if (params.month) {
      sql.push("AND t2.month = :month");
      where.month = params.month;
    }

    var stat = {};
    var list = await this.dao.customQuery(sql.join(" "), where);
    if (list && list.length > 0) {
      stat = list[0] || {};
      stat.amtCount = system.f2y(stat.amtCount);
      stat.actualAmtCount = system.f2y(stat.actualAmtCount);
      stat.serviceTaxCount = system.f2y(stat.serviceTaxCount);
      stat.addedValueTaxCount = system.f2y(stat.addedValueTaxCount);
    }
    return stat;
  }


  async findPushInfos(companyId, begin, end) {
    if (!companyId) {
      return [];
    }
    let sql = [];
    sql.push("SELECT");
    sql.push("id, amt as deduct_amt");
    sql.push("FROM `c_esettle_offline` ");
    sql.push("WHERE ecompany_id = :companyId");
    sql.push("AND created_at >= :begin");
    sql.push("AND created_at <= :end");
    return await this.dao.customQuery(sql.join(" "), { companyId: companyId, begin: begin, end: end }) || [];
  }

}
module.exports = EsettleofflineService;
