airws/database/d_air.js

const field = require('../maps/field');
const mysql = require('./mysql');
const until_time = require('../until/time');
const {getUnixTimeStamp} = require("../until/time");
const code = require("../maps/rcodeMap");
const fields = require("../maps/field");
const checkArgumentsIsEmpty = require("../until/checkArgumentsIsEmpty");
const {loadFlights} = require("./d_recommend");
// 查询指定城市航班


/**
 * 查询相关航班信息
 * @param departureCity 除非城市id
 * @param targetCity 目标城市id
 * @param [routeType] 航班类型,国内或者国际
 * @param [startUnixTime] 出发时间开始,某个时间点之前
 * @param [endUnixTime] 出发时间截止,某个时间段内的
 * @returns {Promise | Promise<unknown>}
 */
function flightSearch(departureCity,targetCity,flightState,startUnixTime,endUnixTime){
    let sql=``,values=[];
    sql = `select f.id,f.originalPrice,f.currentPrice,f.sailingTime,f.langdinTime ,dep.cityname as departureCityName,tar.cityname as targetCityName
            from
            flight as f
            LEFT JOIN (select id,cityname from area ) as dep on dep.id = f.departureCity
            LEFT JOIN (select id,cityname from area ) as tar on tar.id = f.targetCity;
            where f.departureCity = ? and f.targetCity = ?`;
    values.push(departureCity,targetCity);
    if(routeType){
        sql += ` and f.routerType = ?`;
        values.push(routeType);
    }
    if(endUnixTime){
        // 如果有结束时间,没有开始时间则默认添加
        if (!startUnixTime) startUnixTime=getUnixTimeStamp();
        sql += ` and f.sailingTime <= ?`;
        values.push(endUnixTime);
    }
    if(startUnixTime){
        sql += ` and f.sailingTime >= ?`;
        values.push(startUnixTime);
    }
    sql+=`;`
    return mysql.pq(sql,values);
}



/**
 * 显示所有航班列表,管理员级别
 * @param routeType
 * @returns {Promise | Promise<unknown>}
 */
function flightList(){
    let sql=``,values=[];
    sql=`select f.* ,dep.cityname as departureCityName,tar.cityname as targetCityName
            from
            flight as f
            LEFT JOIN (select id,cityName from area ) as dep on dep.id = f.departureCity
            LEFT JOIN (select id,cityName from area ) as tar on tar.id = f.targetCity;`;
    return mysql.pq(sql,values);
}

/**
 * 航班具体信息
 * @param flightId
 * @returns {Promise<unknown>}
 */
function flightInfo(flightId){
    let sql=``,values=[];
    sql+=`select ff.*,count(flightId = ? or null) as pay,
            dep.cityname as departureCityName,tar.cityname as targetCityName
            from 
            (select * from flight where id = ?) as ff
            LEFT JOIN (select id,cityName from area ) as dep on dep.id = ff.departureCity
            LEFT JOIN (select id,cityName from area ) as tar on tar.id = ff.targetCity,
            airTickets;`
    values.push(flightId,flightId)
    return mysql.pq(sql,values);
}

/**
 * 获取航班票数
 * @param flightId
 * @returns {Promise<unknown>}
 */
function flightTicks(flightId){
    let sql=``,values=[];
    sql=`select ff.*,count(t.flightId = ? or null) as pay
            from 
            (select totalVotes from flight where id = ?) as ff,
            airTickets as t`;
    values.push(flightId,flightId);
    return mysql.pq(sql,values);
}

/**
 * 添加航班
 * @param flightName 航班名
 * @param airCode 飞机代码
 * @param originalPrice 原始价格
 * @param currentPrice 当前价格
 * @param sailingTime 起飞时间
 * @param langdinTime 到站时间
 * @param totalVotes 机票数量
 * @param routeType 线路类型,国际or国内
 * @param departureCity 出发城市
 * @param targetCity 目标城市
 * @returns {Promise<unknown>}
 */
function addFlight(flightName,
                   airCode,
                   originalPrice,
                   currentPrice,
                   sailingTime,
                   langdinTime,
                   totalVotes,
                   routeType,
                   departureCity,
                   targetCity){
    let sql = ``,values = [];
    sql = `insert into flight 
    (flightName,airCode,originalPrice,currentPrice,
    sailingTime,langdinTime,
    totalVotes,routeType,
    departureCity,targetCity
    ) values(?,?,?,?,?,?,?,?,?,?)`;
    values.push(...arguments);
    sql += ';'
    return mysql.pq(sql,values);
}



/**
 * 修改航班信息
 * @param flightId
 * @param updateOptions
 * @returns {Promise<unknown>}
 */
function updateFlight(flightId,updateOptions){
    let sql=``,values=[];
    let _arguments = Array.from(arguments);

    // 判断如果所有参数都为空时抛出异常
    sql+=`update flight set `
    let keys = Object.keys(updateOptions);
    if(keys.length < 1){
        throw {rcode:code.notParam,msg:'没有修改项'}
    }
    for(let i = 0;i<keys.length;i++){
        let field = keys[i];
        if(!updateOptions[field]){continue;}
        console.log(i);
        if(values.length > 0){sql+=','}
        sql+=`${field} = ?`
        values.push(updateOptions[field])
    }
    sql += ` where id = ?;`
    values.push(flightId);
    return mysql.pq(sql,values);
}


function recommendFlight(){

}


/**
 *
 * @param searchItems
 * @param cityName
 * @param page
 * @param limit
 * @returns {Promise | Promise<unknown>}
 */
function searchFlights(searchItems,cityName = true,page = 1,limit){
    let sql=`select`,values=[];
    if(cityName){
        sql+=' f.*,dep.cityname as departureCityName,tar.cityname as targetCityName'
        sql+=` from
            flight as f
            LEFT JOIN (select id,cityName from area ) as dep on dep.id = f.departureCity
            LEFT JOIN (select id,cityName from area ) as tar on tar.id = f.targetCity`
    }else{
        sql+=' f.*';
        sql+=` from
            flight as f`;
    }
    sql+=' where '
    // 根据类型
    let fields = Object.keys(searchItems);
    fields = fields.filter(field=>searchItems[field])
    if(fields.length<1){
        throw {rcode:code.notParam}
    }
    for(let field of fields){
        // console.log(`${field} : ${searchItems[field]}`)
        if(!searchItems[field]){
            continue;
        }

            if(values.length>=1)sql += ' and';
            if(field === 'startTime'){
                sql+=` f.sailingTime >= ?`
            }else if(field === 'endTime'){
                sql+=` f.sailingTime <= ?`
            }else if(field === 'startPrice'){
                sql+=` f.currentPrice >= ?`
            }else if(field === 'endPrice'){
                sql+=` f.currentPrice <= ?`
            }else{
                sql+=` f.${field} ${searchItems[field].action||'='} ?`
            }
            values.push(searchItems[field]);

    }
    sql+=' order by createTime desc'
    if(limit){
        sql+=` limit ?,?;`
        values.push((page-1)*limit,limit)
    }
    sql+=';'
    return mysql.pq(sql,values);
}
/**
 * 售票的航班信息
 * @param num
 * @returns {Promise<unknown>}
 */
function sellFlights(num = 5){
    let sql=``,values=[];
    // 判断状态为
    sql+=`select f.id,f.currentPrice,f.sailingTime,f.langdinTime,dep.cityname as departureCityName,tar.cityname as targetCityName
            from
            flight as f
            LEFT JOIN (select id,cityName from area ) as dep on dep.id = f.departureCity
            LEFT JOIN (select id,cityName from area ) as tar on tar.id = f.targetCity
            where flightState = ? ORDER BY f.createTime desc limit 0,?;`
    values.push(fields.flightState_sail,num);
    return mysql.pq(sql,values);
}

/**
 * 检票中的航班信息
 * @param num
 * @returns {Promise<unknown>}
 */
function wicketFlights(num = 5){
    let sql=``,values=[];
    // 判断状态为
    sql+=`select f.id,f.currentPrice,f.sailingTime,f.langdinTime,f.createTime,dep.cityname as departureCityName,tar.cityname as targetCityName
            from
            flight as f
            LEFT JOIN (select id,cityName from area ) as dep on dep.id = f.departureCity
            LEFT JOIN (select id,cityName from area ) as tar on tar.id = f.targetCity
            where flightState = ? ORDER BY f.sailingTime desc limit 0,?;`
    values.push(fields.flightState_wicket,num);
    return mysql.pq(sql,values);
}

module.exports = {
    flightSearch,
    addFlight,
    flightTicks,
    updateFlight,
    flightList,
    flightInfo,
    wicketFlights,
    searchFlights,
    sellFlights
}