cld2/app/models/client.js

/**
 * Created by MyPC on 2019/11/5.
 */
'use strict';
const hash= require('../class/hash');
const Sequelize = require('sequelize');
const Op = Sequelize.Op;

module.exports = (sequelize, DataTypes) => {

    const client = sequelize.define('CLD_client', {
        cid  : {type: DataTypes.INTEGER(11), primaryKey: true, autoIncrement: true},
        clientname: DataTypes.STRING,

        companyid: DataTypes.INTEGER,
        companyname: DataTypes.STRING,
        position: DataTypes.STRING,
        telephone: DataTypes.STRING,

        gender: DataTypes.STRING,
        nicename: DataTypes.STRING,
        qq: DataTypes.INTEGER(15),
        phone: DataTypes.STRING,
        email: DataTypes.STRING,
        area: DataTypes.STRING,
        keynum: DataTypes.STRING,
        unit: DataTypes.STRING,
        nature: DataTypes.STRING,
        local: DataTypes.STRING,
        fax: DataTypes.STRING,
        webservice: DataTypes.STRING,
        department: DataTypes.STRING,
        office: DataTypes.STRING,
        address: DataTypes.STRING,
        district: DataTypes.STRING,
        tag: DataTypes.STRING,
        //tooltip: DataTypes.STRING,

        ride: DataTypes.STRING,
        stay: DataTypes.STRING,
        landmarks: DataTypes.STRING,
        mark: DataTypes.STRING,
        priority: DataTypes.STRING,
        updatetime: DataTypes.INTEGER(11),
        createTime:DataTypes.DATE,
        servicetime: DataTypes.INTEGER(11),



    }, {});

    /*
     * 根据ID合集获得客户信息
     * */
    client.findAllInCid=async function(cid,attributes){

        if(cid==undefined){
            return [];
        }
        var condition={
            raw:true,
            where: {
                cid: {
                    [Op.or]: [cid]
            }
        }};
        if(attributes!=undefined){
            condition.attributes=attributes;
        }
        var clientList = await this.findAll(condition);

        clientList.forEach(function(v,i){
            clientList[i].cidKey=hash.hashEncode(clientList[i].cid.toString());
            //console.log(clientList[i].dataValues.cidKey);
            //console.log(hash.hashDecode(clientList[i].dataValues.cidKey.toString()));
        });
        return clientList;
    };

/*
 * 根据ID获得客户信息
 * */
client.findById=async function(cid,attributes){

    if(!hash.isNotANumber(cid)){
        cid=hash.hashDecode(cid);
        if(!hash.isNotANumber(cid)){
            return [];
        }
    }

    //console.log(cid);

    if(hash.isExistence(attributes)){
        condition.attributes=attributes;
    }

    var condition={
        raw:true,
        where: {
            cid: cid
        }};
    var detail = await this.findOne(condition);
    if(!hash.isExistence(detail)){
        return [];
    }

    var staffDetail=[];
    
    var tblName1 = 'CLD_client_staff', tblName2='CLD_staff';
    var cid = '20160923 AND 1=1;-- hack';
    var sqlQuery = 'SELECT b.* FROM ${tblName1} as a left join ${tblName2} as b on (a.sid=b.sid) where a.cid=$cid';
    
       await this.sequelize.query(sqlQuery,
    		type: sequelize.QueryTypes.SELECT,
            bind： { 
            	cid: cid
            }
        ).spread((results, metadata) => {
            staffDetail=results[0];
        });
    

    
//    await this.sequelize.query('SELECT b.* FROM CLD_client_staff as a left join CLD_staff as b on (a.sid=b.sid) where a.cid=7').spread((results, metadata) => {
//        staffDetail=results[0];
//    });
    
    
    detail['staff']=staffDetail;

    detail.cidKey=hash.hashEncode(detail.cid.toString());
    detail.cid=hash.hashEncode(detail.cid.toString());

    return detail;
};

/*
 * 根据ID合集获得客户信息
 * */
client.getClentListByClientname=async function(clientname,attributes){
    if(!hash.isExistence(clientname)){
        return [];
    }
//
    var condition={
        raw:true,
        limit:30,
        where: {
            clientname: {
                [Op.like]: '%'+clientname+'%'
    }
    }};
    if(hash.isExistence(attributes)){
        condition.attributes=attributes;
    };
    var clientList = await this.findAll(condition);
    clientList.forEach(function(v,i){
        clientList[i].cidKey=hash.hashEncode(clientList[i].cid.toString());
    });
    return clientList;
};

    return client;
};