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

class UsereaccountService extends ServiceBase{
  constructor(){
     super(ServiceBase.getDaoName(UsereaccountService));
     this.restClient = system.getObject("util.restClient");
     this.idcardClient = system.getObject("util.idcardClient");
  }

  async findAll(obj){
    obj = obj || {};
    var result = await this.dao.model.findAll(
      {where:obj}
    );
    return result;
  }

  async idsByCondition(condition) {
    return await this.dao.idsByCondition(condition);
  }

  //解析Excel中，批量导入签约人员信息
  async parseItems(userId, ossurl) {
    var result = {
      code : 0,
      msg:"",
      list : [],
    };

    var isValid = true;    

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

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

      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 erridcardLines = [];

      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 < 4) {
          result.msg = "excel第" + rowNum + "行，请填写姓名、身份证、银行卡号";
          return result;
        }

        var data = {};
        data.name = this.trim(cells[0]);
        data.id_card = this.trim(cells[1]).toUpperCase();
        data.phone = this.trim(cells[2]) || "";
        data.bank_no = this.trim(cells[3]);
        
        data.rowNum = rowNum;

        
        //验证不为空
        if(!data.name) {
          result.msg = "excel第" + rowNum + "行，请填写姓名";
          return result;
        }
        if(!data.id_card) {
          result.msg = "excel第" + rowNum + "行，请填写身份证";
          return result;
        }
        if(!data.bank_no) {
          result.msg = "excel第" + rowNum + "行，请填写银行卡号";
          return result;
        }

        
        //验证是否有效
        // if(!await this.idcardClient.checkIDCard(data.id_card)) {
        //   erridcardLines.push(rowNum);
        //   data.errormsg = "身份证号码验证失败";
        // }
        if(data.phone) {
          if(!regexNumber.test(data.phone) || data.phone.substring(0, 1) != 1 || data.phone.length != 11) {
            result.msg = "excel第" + rowNum + "行，手机号格式错误";
            return result;
          }
        }
        
        if(!regexNumber.test(data.bank_no)) {
          result.msg = "excel第" + rowNum + "行，银行卡号格式错误，请填写纯数字";
          return result;
        }

        datalist.push(data);
      }

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

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

      result.code = 1;
      result.list = datalist;      
      return result;

    } catch (error) {
      console.log(error.stack);
      result.msg = "您的网络忙， 请稍后重试";
      return result;
    }
  }

  async getSignAccounts(etemplateIds, idNoList, idNameList) {
    var sql = [];
  
    sql.push("SELECT ");
    sql.push("t2.userName, t2.personsSign");
    sql.push("FROM c_econtract t1");
    sql.push("INNER JOIN p_user_eaccount t2 ON t1.usereaccount_id = t2.id");
    sql.push("WHERE t1.etemplate_id IN (:etemplateIds) AND t1.`eflowstatus` = '2'");
    sql.push("AND t2.`userName` IN (:idNameList)");
    sql.push("AND t2.`personsSign` IN (:idNoList)");

    var list = await this.dao.customQuery(sql.join(" "), {
      etemplateIds: etemplateIds,
      idNameList: idNameList,
      idNoList: idNoList,
    });
    return list || [];
  }

  async idNoByUserId3rd(userId3rd) {
    if (!userId3rd) {
      return;
    }
    let sql = "SELECT t1.`personsSign` FROM p_user_eaccount t1 INNER JOIN p_user t2 ON t1.`user_id` = t2.`id` WHERE t2.`userId3rd` = :userId3rd";
    let list = await this.dao.customQuery(sql, {userId3rd: userId3rd,});
    let rs = [];
    for(let item of list) {
      rs.push(item.personsSign);
    }
    return rs;
  }

  async getSignAccounts_dk(etemplateIds, idNoList, idNameList) {
    var sql = [];

    sql.push("SELECT ");
    sql.push("t2.userName, t2.personsSign");
    sql.push("FROM c_dkcontract t1");
    sql.push("INNER JOIN p_user_eaccount t2 ON t1.usereaccount_id = t2.id");
    sql.push("WHERE t1.dktemplate_id IN (:etemplateIds) AND t1.`eflowstatus` = '2'");
    sql.push("AND t2.`userName` IN (:idNameList)");
    sql.push("AND t2.`personsSign` IN (:idNoList)");

    var list = await this.dao.customQuery(sql.join(" "), {
      etemplateIds: etemplateIds,
      idNameList: idNameList,
      idNoList: idNoList,
    });
    return list || [];
  }

}
module.exports=UsereaccountService;
