tmp/wj-pack/backend/app/service/insurant.js

'use strict';

const Service = require('egg').Service;
const Moment = require('moment');
const Const = require("../const");

const InsTableName = "insurance";
const InsCompanyTable = "ins_company";
const InsCmpProdTable = "ins_company_prod";

const DateUnits = ['days','months','years'];

function mergeUpdateField(target, src) {
    for( let k in src ) {
        if( src[k] === undefined || src === null ) continue;
        target[k] = src[k];
    }
}

function formatData( data )
{
    function formatInt(data, key) {
        let value = data[key];
        if( value != undefined ) data[key] = parseInt( value );
        
        if( isNaN(data[key])) data[key] = 0;
    }

    function formatFloat(data, key) {
        let value = data[key];
        if( value != undefined ) data[key] = parseFloat( value );
        
        if( isNaN(data[key])) data[key] = 0.0;
    }
    
    formatFloat(data, "coverage_dur");
    formatInt(data, "pay_feq");
    formatFloat(data, "pay_per_amt");
    formatInt(data, "pay_dur");
    formatInt(data, "pay_feq_unit");
    formatInt(data, "pay_dur_unit");
}

class InsurantService extends Service {

    //上传保单
    async addPictures(userid, pictures) {
        return "empty";
    }


    async updateInfoById(id,  data) {
        let {app } = this;
        
       return await app.mysql.update(InsTableName, data , {where: {id} });
    }
    
    async getInfo( _id , columns) {
        let {app } = this;
        let cols  = columns? Object.keys( columns ) : null;
        let options = cols? { columns: cols}: null;
        return await app.mysql.get(InsTableName, {id: _id}, options);
    }

    async getDetail(id) {
        let {app} = this;

        let ret = await app.mysql.get(InsTableName, {id});
        if( ret ) {
          let prod = await app.mysql.query("select Prod.name as prod_name , Cmp.id as cmpy_id, Cmp.full_name as cmpy_name from ins_company_prod as Prod INNER JOIN ins_company as Cmp on Prod.cmpy_id = Cmp.id where Prod.id=?", [ret.prod_id]);
          if( prod[0] ) {
              prod = prod[0];
              ret.prod_name = prod.prod_name;
              ret.cmpy_name = prod.cmpy_name;
              ret.cmpy_id = prod.cmpy_id;
          }
        }
        return ret;
    }

    async getExtendinfo( _id )
    {
        return "empty";
    }
    
    async addbase(userid, data) {
        let {ctx } = this;
        return "empty";
    }

    async add(id,  data)
    {
        let { app } = this;

        data.user_id = id;

        formatData( data );

        console.log("adding", data);

        await this.updateInsUserNames( data );

        this.updateInsCoverageState( data );
        this.updateInsPayState( data );

        let ret = await app.mysql.insert(InsTableName, data);

        return ret;
    }

    async updateInsUserNames(ins)
    {
        //coverage_user_names
        let mysql = this.app.mysql;
        let ids = ins.coverage_user_ids;
        let names = [];
        ids = ids.split(",");
        let n = ids.length;
        for( let i=0; i<n; i++) {
            let id = ids[i];
            let ret = await mysql.get("app_user_family", {id}, {columns:["name"]});
            if( ret ) names.push( ret.name );
        }
        
        ins.coverage_user_names = names.join(",");
        console.log( "names", names, ins.coverage_user_names);
    }

    //保额保障期限的计算属性
    async updateInsCoverageState(ins)
    {
        //expire_time = effect_time + coverage_dur +  coverage_dur_unit
        //state = currTime + expire_time + effect_time   0-未生效 1-保障中 2-已失效  
        // let s = '2020-07-11T02:32:15.154Z';
        let t = ins.effect_time;
        t = t.split("T")[0];
        const effectTime = Moment(t);
        
        const currTime = Moment();
        let DurType = ins.coverage_dur_unit;
        console.log("effectTime", effectTime, effectTime.daysInMonth);

        if( DurType == Const.CoverageDurationType.Other) { //其他
           let coverage_birthOrEndDay = ins.coverage_birthOrEndDay;
           ins.expire_time = coverage_birthOrEndDay;
        } else if( DurType == Const.CoverageDurationType.Life ) { //终身
            ins.expire_time = null; //没有过期时间
        } else if( DurType == Const.CoverageDurationType.ToAge ) { //到多少岁
            let coverage_birthOrEndDay = ins.coverage_birthOrEndDay;//先拿到生日
            let age = ins.coverage_dur; //年龄
            let end = Moment(coverage_birthOrEndDay).add(age, "years");
            ins.expire_time = end.format("YYYY-MM-DD");
        } else {
            let end = effectTime.add(ins.coverage_dur, DateUnits[ins.coverage_dur_unit]);
            ins.expire_time = end.format("YYYY-MM-DD");
        }

        if( currTime.isBefore( effectTime ) ) ins.state = Const.InsanceState.UnEffect;
        else if( currTime.isAfter( Moment(ins.expire_time) ) ) ins.state = Const.InsanceState.Expired;
        else ins.state = Const.InsanceState.Effective;

        console.log("coverage", ins.state, ins.expire_time);

        return ins;
    }

    //支付相关的时间期限计算属性
    async updateInsPayState(ins)
    {
        //pay_next_time =currTime +  paids + effect_time + pay_feq + pay_dur 下次缴费时间 已逾期（小于当前时间） 下次缴费（大于当前） 已缴清（null）
        //pay_total = pay_feq + pay_dur + pay_per_amt 保费总数
        //pay_end_time = effect_time + pay_feq + pay_dur 最后一次缴费日期
        const effectTime = Moment( ins.effect_time );
        let payWay = ins.pay_feq_unit;
        if( payWay == Const.PayFreqUnit.Dun) { //趸交
           ins.pay_end_time = effectTime.format("YYYY-MM-DD");
           ins.pay_total = ins.pay_per_amt;
           ins.pay_next_time = null;
           return;
        } 
        
        //天 月 年
        let endTime = Moment( ins.effect_time ).add(ins.pay_dur, DateUnits[ins.pay_dur_unit]);
        let count = 0;
        let currTime = effectTime;
        let pay_next_time = null;
        let now = Moment();
        let paids = [];

        //todo 防止是循环
        do {
            count += 1;
            let currTimeStr = currTime.format("YYYY-MM-DD");
            console.log("currTime", currTime, now,  currTime.isBefore(now));

            if( currTime.isBefore(now) ) {
                paids.push( currTimeStr );
            } else if (!pay_next_time) {
                pay_next_time = currTime.format("YYYY-MM-DD");
            }
            
            currTime = currTime.add( ins.pay_feq,  DateUnits[ins.pay_feq_unit]);


        } while( currTime.isBefore( endTime ) );

        if( paids.length > 0 )  ins.paids = paids.join(",");
        ins.pay_total = count　* ins.pay_per_amt;
        ins.pay_end_time = currTime.format("YYYY-MM-DD");
        ins.pay_next_time = pay_next_time;

        console.log("pay", ins.pay_total, ins.pay_end_time, ins.pay_next_time);
    }

    async update(id, data) {

        let {app} = this;
        let mysql = app.mysql;

        let updated = {};
        console.log( data );
        
        mergeUpdateField(updated, data);

        let ret = await mysql.update(InsTableName, updated, {where: {id: data.id}} );

        if( data.paids != undefined ) {//更新 pay_next_time

            await this.updateNextPayTime( id );

            return "更新成功";
        }

        return ret;
    }

    async updateNextPayTime(id){
        let mysql = this.app.mysql;

        let ins = await mysql.get(InsTableName, {id}, {columns:["effect_time","paids","pay_dur","pay_dur_unit","pay_per_amt","pay_feq","pay_feq_unit","pay_end_time", "pay_next_time"]});
        if( !ins ) throw "查询保单失败!";

        //重新计算下次缴费时间
        let paids =ins.paids? ins.paids.split(","):[];

        const effectTime = Moment( ins.effect_time );
        let currTime = effectTime;
        const now = Moment();
        const endTime = Moment( ins.effect_time ).add(ins.pay_dur, DateUnits[ins.pay_dur_unit]);
        let payNextDate = null;
        do {
            let currTimeStr = currTime.format("YYYY-MM-DD");
            if( paids.indexOf( currTimeStr ) < 0 )
            {//当前没缴费
                payNextDate = currTimeStr;
                break;
            }
            currTime = currTime.add( ins.pay_feq,  DateUnits[ins.pay_feq_unit]);
        } while( currTime.isBefore( endTime ) );

        await mysql.update(InsTableName, {pay_next_time:payNextDate}, {where:{id}});
    }

    async pictureList(id) {
        let {app} = this;

        let ins = await app.mysql.select(InsTableName,{ where: {user_id: id}, columns: ["pictures"]});
        console.log( ins );

        let ret = [];
        ins.forEach( pic =>{
            let p = pic.pictures;
            if( p ) {
                let arr = p.split(",");
                ret = ret.concat(arr);
            } 
        });
        return ret;
    }

    async policyList( uid, type) {
        let app = this.app;

        //type 类型 0为全部 1 需交费 2 保障中 3 已到期
        let select = "SELECT ins.id, curdate() as time,ins.expire_time,ins.coverage_birthOrEndDay,ins.effect_time,ins.coverage_user_names, ins.coverage_dur,ins.coverage_dur_unit, ins.prod_type, ins.pay_total, ins.coverage_amt, ins.coverage_amt_unit, ins.pay_next_time, ins.pay_end_time, ins.coverage_user_ids, Prod.name AS prod_name , Cmp.id AS cmpy_id, Cmp.full_name AS cmpy_name, Cmp.cover AS cover FROM insurance AS ins INNER JOIN ins_company_prod AS Prod ON Prod.id = ins.prod_id INNER JOIN ins_company AS Cmp ON Cmp.id = Prod.`cmpy_id` WHERE ins.user_id=?"
        
        if( type == undefined || type == 0) {
            return  await app.mysql.query(`${select} LIMIT 0, 10`,[uid]);
        } else if( type == 1) {
            return  await app.mysql.query(`${select} and pay_next_time IS TRUE LIMIT 0, 10`,[uid]);
        } else if( type == 2) {
            return  await app.mysql.query(`${select} and state=1 LIMIT 0, 10`,[uid]);
        } else if( type == 3) {
            return  await app.mysql.query(`${select} and state=2 LIMIT 0, 10`,[uid]);
        }
        throw "未知的查询类型";
    }

    //保额分布
    async distributed(uid,  userId)
    {
        let {app} = this;
       
        let ret = "";

        if( !userId ) {
           ret = await app.mysql.query("select prod_type, sum(CASE WHEN coverage_amt_unit = 0 THEN 10000 * coverage_amt ELSE coverage_amt end) as amt from `insurance`  where user_id=? and ( coverage_amt_unit = 0 or coverage_amt_unit=1) GROUP BY prod_type",[uid]);
         
        } else {
           ret = await app.mysql.query("select prod_type, sum(CASE WHEN coverage_amt_unit = 0 THEN 10000 * coverage_amt ELSE coverage_amt end) as amt from `insurance`  where user_id=? AND FIND_IN_SET(?,coverage_user_ids) > 0 and ( coverage_amt_unit = 0 or coverage_amt_unit=1) GROUP BY prod_type",[uid, userId]);
        }

        console.log("distributed", uid, userId, ret);

        return ret;
    }


    async policyStatics(uid) {
        //总保单数 SELECT COUNT(id)  FROM `insurance` WHERE user_id=1
        //需要缴费 SELECT COUNT(id)  FROM `insurance` WHERE user_id=1 AND pay_next_time IS TRUE
        //保障中 SELECT COUNT(id)  FROM `insurance` WHERE user_id=1 AND state=1
        //已到期 SELECT COUNT(id)  FROM `insurance` WHERE user_id=1 AND state=2
        let {app} = this;
        let ret = await app.mysql.query("SELECT COUNT(id) AS total, SUM(CASE WHEN pay_next_time IS TRUE THEN 1 ELSE 0 END) AS pay,SUM(CASE WHEN state=1 THEN 1 ELSE 0 END) AS valid ,SUM(CASE WHEN state=2 THEN 1 ELSE 0 END) AS expire FROM `insurance` WHERE user_id=?",[uid]);
        let result = ret[0];
        if( result.total == 0 ) {
            result.pay = 0;
            result.valid = 0;
            result.expire = 0;
        }
        return result;
    }

    //todo 优化这里的统计数据使用缓存
    async updateYearPayInfoCache(uid, fid) {
       
        let app = this.app;
        let startOf = Moment().startOf('year').format("YYYY-MM-DD");

        //获取用户的所有本年需要缴费的保单
        let ins =[];
        if( !fid ) ins = await app.mysql.query("SELECT effect_time, pay_feq, pay_feq_unit, pay_per_amt, pay_per_amt_unit, pay_dur, pay_dur_unit, paids FROM `insurance` WHERE user_id=? and pay_next_time is true and Date(pay_end_time)>?",[uid, startOf]);
        else {
            ins = await app.mysql.query("SELECT effect_time, pay_feq, pay_feq_unit, pay_per_amt, pay_per_amt_unit, pay_dur, pay_dur_unit, paids FROM `insurance` WHERE user_id=? and pay_next_time is true and Date(pay_end_time)>? and FIND_IN_SET(?,coverage_user_ids) > 0",[uid, startOf, fid]);
        }
        let rmbs = [];
        let dollers = [];
        ins.forEach(item=>{
            if( item.pay_per_amt_unit == 2) dollers.push( item );
            else {
                rmbs.push( item );
            }
        });
        
        function calc( rows ) { 
            
            const defaultDate = Moment();
            let currYear = defaultDate.year();
            let ret = {total: 0, payed:0, year:currYear};
            
            rows.forEach( r =>{
                let effectiveTime = r.effect_time;
                const currentDate = Moment(effectiveTime);
                const endDate = Moment(effectiveTime).add(r.pay_dur, DateUnits[r.pay_dur_unit]);
         

                // let payList = [defaultDate];
                do {
                    const currDateFmt = currentDate.format('YYYY-MM')
                    // payList.push(currDateFmt);
                    let payYear = currentDate.year();
                    
                    if( payYear <= currYear ) {
                        ret.total += r.pay_per_amt;
                        if( r.pay_feq_unit == 3 ) { //趸交,默认已交
                            ret.payed += r.pay_per_amt;
                            break;
                        }

                        if( r.paids &&  r.paids.indexOf( currDateFmt ) > 0 ) {
                            ret.payed += r.pay_per_amt;
                        }
                    }

                    currentDate.add(r.pay_feq, DateUnits[r.pay_feq_unit]);
                } while (currentDate.isBefore(endDate));
            })
            return ret;
        }
        
        let rmb = calc( rmbs );
        let doller = calc( dollers );
        return {rmb, doller};
    }

    //今年保费缴纳情况
    async payInfo( uid, fid ) {
        //当年的缴费状态存redis,保单添加和缴费状态修改的时候，更新缓存

        //获取当年的缴费表
        return await this.updateYearPayInfoCache( uid , fid);
        

        // const currentDate = Moment(effectiveTime);
        // const endDate = Moment(effectiveTime).add(payDuration.num, dateUnits[payDuration.unit]);
        // const defaultDate = Moment();
        // let payList = [];
        // let paidList = [];
        // do {
        //     const currDateFmt = currentDate.format('YYYY-MM')
        //     payList.push(currDateFmt);
        //     if(currentDate.isBefore(defaultDate)){
        //         paidList.push(currDateFmt)
        //     }
        //     currentDate.add(payMethod.num, dateUnits[payMethod.unit])
        // } while (currentDate.isBefore(endDate));

        // if(!(paids && !paidsRef.current)){
        //     onChange && onChange(paidList);
        // }
        // paidsRef.current = paidList;
        // setList(payList);
    }

    //用户的保单诊断
    async diagnose(uid) {

        return {
            totalScore: 98,
            point1: 20, 
            point2: 20, 
            point3: 20, 
            point4: 20, 
            point5: 20, 
            risk:89.2,
            level: 20,
            title: "uid文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案" + uid,
            advice:"文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案,文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案,文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案文案"
        }
    }

    //完成pay_next_day缴费 影响 pay_next_day 和 paids 两个字段
    async payed( id ) {
    
        let mysql = this.app.mysql;
        let ins = await mysql.get(InsTableName, {id}, {columns:["effect_time","paids","pay_dur","pay_dur_unit","pay_per_amt","pay_feq","pay_feq_unit","pay_end_time", "pay_next_time"]});
        if( !ins ) throw "查询保单失败!";

        //重新计算下次缴费时间
        let pay_next_time = ins.pay_next_time;
        if( !pay_next_time ) return ins; //已经缴清

        let payNextDate = Moment( pay_next_time ).format("YYYY-MM-DD");
        let paids = [];
        if( !ins.paids ) ins.paids = "";
        paids = ins.paids.split(",");
        if( paids.indexOf(payNextDate) < 0 ) {
            paids.push(payNextDate);
        }
        ins.paids = paids.join(",");

        const effectTime = Moment( ins.effect_time );
        let currTime = effectTime;
        const now = Moment();
        const endTime = Moment( ins.effect_time ).add(ins.pay_dur, DateUnits[ins.pay_dur_unit]);
        payNextDate = null;

        do {
            let currTimeStr = currTime.format("YYYY-MM-DD");
            console.log("currTime", currTime, now,  currTime.isBefore(now));
            if( paids.indexOf( currTimeStr ) < 0 )
            {//当前没缴费
                payNextDate = currTimeStr;
                break;
            }
            currTime = currTime.add( ins.pay_feq,  DateUnits[ins.pay_feq_unit]);
        } while( currTime.isBefore( endTime ) );

        ins.pay_next_time = payNextDate;

        await mysql.update(InsTableName, {pay_next_time:ins.pay_next_time, paids: ins.paids}, {where:{id}});

        return {pay_next_time: payNextDate};
    }

    async list(userid, query) {

        let app = this.app;

        //type 类型 0为全部 1 需交费 2 保障中 3 已到期
        let select = "SELECT ins.id, curdate() as time,ins.expire_time, ins.coverage_birthOrEndDay,ins.effect_time,ins.coverage_user_names, ins.coverage_dur,ins.coverage_dur_unit, ins.prod_type, ins.pay_total, ins.coverage_amt, ins.coverage_amt_unit, ins.pay_next_time, ins.pay_end_time, ins.coverage_user_ids, Prod.name AS prod_name , Cmp.id AS cmpy_id, Cmp.full_name AS cmpy_name, Cmp.cover AS cover FROM insurance AS ins INNER JOIN ins_company_prod AS Prod ON Prod.id = ins.prod_id INNER JOIN ins_company AS Cmp ON Cmp.id = Prod.`cmpy_id` WHERE ins.user_id=?"
        let where = "";
        if( query.type != undefined) {
            where +=  `and ins.prod_type=${query.type} `;
        }
        if( query.uid != undefined) {
            where +=  `and FIND_IN_SET(${query.uid}, ins.coverage_user_ids) > 0 `;
        }

        if( query.state != undefined) {
            where += `and ins.state=${query.state} `;
        }

        let orderBy = "";
        if( query.order != undefined ) {
            switch(query.order) {
                case 0:  orderBy = "ORDER BY ins.pay_next_time DESC";
                break;
                case 1: orderBy = "ORDER BY ins.pay_next_time";
                break;
                case 2: orderBy = "ORDER BY ins.expire_time DESC";
                break;
                case 3: orderBy = "ORDER BY ins.expire_time";
            }
        }
        console.log( "-------------------------------", query);
        let sql = `${select} ${where} ${orderBy} LIMIT ${query.page*query.size},${query.size}`;
        console.log( sql );

        return  await app.mysql.query(sql,[userid]);
    }
}

module.exports = InsurantService;