import MongoDB from 'mongodb' ; import { getCSVInfoObj } from '../utils/helper' ; import CaseInformation from '../CaseInformation' ; import FacilityInformation from '../FacilityInformation' ; import CSVDatabase from './CSVDatabase' ; class MongoDBDatabase { static instance : MongoDBDatabase ; connection ! : Promise < MongoDB . MongoClient > ; constructor ( ) { if ( ! MongoDBDatabase . instance ) { MongoDBDatabase . instance = this ; } return MongoDBDatabase . instance ; } return { Promise < string > } async connect ( ) { const dbUrl = process . env . DB_NOSQL_URI as string ; if ( ! this . connection ) { try { this . connection = MongoDB . MongoClient . connect ( dbUrl , { useNewUrlParser : true , useUnifiedTopology : true , } ) ; } catch ( e ) { throw Error ( `[MongoDBDatabase] ${ e . message }` ) ; } } if ( await this . connection === undefined ) throw Error ( '[MongoDBDatabase] Failed to connect.' ) ; return 'Successfully connected to the Mongo Database' ; } param queries param { String } queries . month param { String } queries . day param { int } queries . page param { int } queries . limit param { int } queries . maxLimit param { Object | undefined } queries . filters return { Promise < * > } async get ( queries ) { if ( queries . month > 12 ) throw Error ( 'Error: the month cannot be greater than 12' ) ; if ( queries . day > 31 ) throw Error ( 'Error: the day cannot be greater than 31' ) ; if ( queries . page < 1 || queries . limit < 1 ) throw Error ( 'Error: page or limit query can\'t be less than 1.' ) ; if ( queries . limit > queries . maxLimit ) throw Error ( `Error: limit query can't be greater than ${ queries . maxLimit }.` ) ; this . connection . then ( async ( client ) => { const db = client . db ( ) ; const collection = db . collection ( 'case_informations' ) ; let filter = { } ; const opt = { limit : queries . limit , skip : ( queries . page - 1 ) * queries . limit , projection : { _id : 0 } , } ; const sortOpt = { case_code : 1 , } ; if ( queries . month && ! queries . day ) { const date = `/2020-${ queries . month }.*/` ; filter = { $or : [ { $and : [ { date_specimen : date } , { date_onset : '' } , ] , } , { date_onset : date } , ] , } ; } else if ( queries . month && queries . day ) { const date = `2020-${ queries . month }-${ queries . day }` ; filter = { $or : [ { $and : [ { date_specimen : date } , { date_onset : '' } , ] , } , { date_onset : date } , ] , } ; } if ( queries . filters ) { console . log ( queries . filters ) ; Object . keys ( queries . filters ) . forEach ( ( key ) => { let newFilter = queries . filters [ key ] ; if ( ! Number . isNaN ( newFilter ) ) newFilter = Number ( newFilter ) ; filter [ key ] = newFilter || '' ; } ) ; } try { const result = collection . find ( filter , opt ) . sort ( sortOpt ) ; return await result . toArray ( ) ; } catch ( e ) { throw Error ( e . message ) ; } } ) ; } return { Promise < String > } async getLatestFolderID ( ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'update_history' ) ; const result = collection . find ( { } , { limit : 1 } ) . sort ( { _id : - 1 } ) ; const res = await result . toArray ( ) ; if ( res . length === 0 ) { return '' ; } return res [ 0 ] . folder_id || '' ; } return { Promise < String > } async getLastUpdateDate ( ) { let res = [ ] ; const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'update_history' ) ; const result = collection . find ( { } , { limit : 1 } ) . sort ( { _id : - 1 } ) ; res = await result . toArray ( ) ; if ( res . length === 0 ) return '' ; return new Date ( res [ 0 ] . _id . getTimestamp ( ) ) . toLocaleString ( 'en-US' , { timeZone : 'Asia/Shanghai' , } ) || '' ; } param { String } dbName param { Object } objFilters return { Promise } async count ( dbName , objFilters = null ) { return new Promise ( ( resolve , reject ) => { this . connection . then ( ( client ) => { const db = client . db ( ) ; let collection = db . collection ( dbName ) ; if ( objFilters !== null ) { const { field } = objFilters ; const { value } = objFilters ; const tempObj = { } ; tempObj [ field ] = value ; collection = collection . find ( tempObj ) ; return collection . toArray ( ( err , docs ) => { if ( err ) reject ( Error ( err . message ) ) ; resolve ( docs . length ) ; } ) ; } return collection . countDocuments ( ) . then ( ( data ) => { resolve ( data ) ; } ) . catch ( ( err ) => reject ( Error ( err ) ) ) ; } ) ; } ) ; } deprecated param { String } field param { String | Number } value return { Promise } async filter ( field , value ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'case_informations' ) ; const filter = { } ; const opts = { projection : { _id : 0 } , } ; filter [ field ] = value ; try { const result = collection . find ( filter , opts ) ; return await result . toArray ( ) ; } catch ( e ) { throw Error ( e . message ) ; } } param { String } region return { Promise < Object > } return { Promise < Object > } return { Promise < Object > } return { Promise < Object > } async getSummary ( region = null ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'case_informations' ) ; const totalFilter = { } ; const recoveriesFilter = { removal_type : 'recovered' } ; const diedFilter = { removal_type : 'died' } ; const activeCasesFilter = { removal_type : '' , date_rep_conf : { $exists : true } } ; const filters = [ totalFilter , recoveriesFilter , diedFilter , activeCasesFilter ] ; if ( region !== null ) { filters . forEach ( ( filter ) => { filter . region_res = region ; } ) ; } try { const result = collection . aggregate ( [ { $lookup : { from : 'case_informations' , pipeline : [ { $match : totalFilter } , { $group : { _id : '$case_informations' , count : { $sum : 1 } } } , { $project : { _id : 0 } } , ] , as : 'total' , } , } , { $lookup : { from : 'case_informations' , pipeline : [ { $match : recoveriesFilter } , { $group : { _id : '$removal_type' , count : { $sum : 1 } } } , { $project : { _id : 0 } } , ] , as : 'recoveries' , } , } , { $lookup : { from : 'case_informations' , pipeline : [ { $match : diedFilter } , { $group : { _id : '$removal_type' , count : { $sum : 1 } } } , { $project : { _id : 0 } } , ] , as : 'deaths' , } , } , { $lookup : { from : 'case_informations' , pipeline : [ { $match : activeCasesFilter } , { $group : { _id : '$case_informations' , count : { $sum : 1 } } } , { $project : { _id : 0 } } , ] , as : 'active_cases' , } , } , { $project : { _id : 0 , recoveries : 1 , deaths : 1 , total : 1 , active_cases : 1 , } , } , ] ) . limit ( 1 ) ; const res = await result . toArray ( ) ; res [ 0 ] . total = res [ 0 ] . total [ 0 ] . count ; res [ 0 ] . recoveries = res [ 0 ] . recoveries [ 0 ] . count ; res [ 0 ] . deaths = res [ 0 ] . deaths [ 0 ] . count ; res [ 0 ] . active_cases = res [ 0 ] . active_cases [ 0 ] . count ; let fatalityRate = res [ 0 ] . deaths / res [ 0 ] . total ; let recoveryRate = res [ 0 ] . recoveries / res [ 0 ] . total ; fatalityRate = ( fatalityRate * 100 ) . toFixed ( 2 ) ; recoveryRate = ( recoveryRate * 100 ) . toFixed ( 2 ) ; return { result : res [ 0 ] , fatalityRate , recoveryRate , } ; } catch ( e ) { throw Error ( e . message ) ; } } param { Object } queries return { Promise } async getTimeline ( queries ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'case_informations' ) ; const filter = { } ; if ( queries . region ) filter . region_res = queries . region . toLowerCase ( ) ; else if ( queries . region_res ) filter . region_res = queries . region_res . toLowerCase ( ) ; try { const result = collection . aggregate ( [ { $match : filter } , { $match : { $or : [ { date_onset : { $ne : '' } } , { date_specimen : { $ne : '' } } ] } } , { $group : { _id : { date : { $cond : { if : { $eq : [ '$date_onset' , '' ] } , then : '$date_specimen' , else : '$date_onset' , } , } , } , cases : { $sum : 1 } , recovered : { $sum : { $switch : { branches : [ { case : { $eq : [ '$removal_type' , 'recovered' ] } , then : 1 , } , ] , default : 0 , } , } , } , died : { $sum : { $switch : { branches : [ { case : { $eq : [ '$removal_type' , 'died' ] } , then : 1 , } , ] , default : 0 , } , } , } , } , } , { $sort : { '_id.date' : 1 } } , { $project : { _id : 0 , date : '$_id.date' , cases : 1 , recovered : 1 , died : 1 , } , } , ] ) ; return await result . toArray ( ) ; } catch ( e ) { throw Error ( e . message ) ; } } return { Promise } async getTopRegions ( ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'case_informations' ) ; try { const result = collection . aggregate ( [ { $match : { region_res : { $ne : '' } } } , { $group : { _id : '$region_res' , cases : { $sum : 1 } , recovered : { $sum : { $switch : { branches : [ { case : { $eq : [ '$removal_type' , 'recovered' ] } , then : 1 , } , ] , default : 0 , } , } , } , deaths : { $sum : { $switch : { branches : [ { case : { $eq : [ '$removal_type' , 'died' ] } , then : 1 , } , ] , default : 0 , } , } , } , } , } , { $sort : { cases : - 1 } } , { $project : { _id : 0 , region : '$_id' , cases : '$cases' , recovered : '$recovered' , deaths : '$deaths' , } , } , ] ) ; return await result . toArray ( ) ; } catch ( e ) { throw Error ( e . message ) ; } } param { Object } queries return { Promise } async getFacilities ( queries ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'facility_informations' ) ; Object . keys ( queries ) . forEach ( ( key ) => { if ( ! Number . isNaN ( queries [ key ] ) ) { queries [ key ] = Number ( queries [ key ] ) ; } } ) ; const filter = queries ; const opt = { projection : { _id : 0 } , } ; try { const result = collection . find ( filter , opt ) . sort ( { cf_name : 1 } ) ; return await result . toArray ( ) ; } catch ( e ) { throw Error ( e . message ) ; } } param { Object } queries return { Promise } async getFacilitiesSummary ( queries ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'facility_informations' ) ; const filter = { } ; const output = { } ; if ( queries . region ) filter . region = queries . region . toLowerCase ( ) ; if ( queries . hospital_name ) { output . hospital_name = '$_id' ; filter . cf_name = queries . hospital_name . toLowerCase ( ) ; } Object . assign ( output , { _id : 0 , total_facilities : '$total_facilities' , occupancy_rate : '0.0' , beds : { total_vacant : { $add : [ '$icu_v' , '$isolbed_v' , '$beds_ward_v' ] , } , total_occupied : { $add : [ '$icu_o' , '$isolbed_o' , '$beds_ward_o' ] , } , covid : { icu_v : '$icu_v' , icu_o : '$icu_o' , isolbed_v : '$isolbed_v' , isolbed_o : '$isolbed_o' , beds_ward_v : '$beds_ward_v' , beds_ward_o : '$beds_ward_o' , } , non_covid : { icu_v_nc : '$icu_v_nc' , icu_o_nc : '$icu_o_nc' , nonicu_v_nc : '$nonicu_v_nc' , nonicu_o_nc : '$nonicu_o_nc' , } , } , equipments : { mechvent_v : '$mechvent_v' , mechvent_o : '$mechvent_o' , mechvent_v_nc : '$mechvent_v_nc' , mechvent_o_nc : '$mechvent_v_nc' , } , } ) ; try { const result = collection . aggregate ( [ { $match : filter } , { $group : { _id : ( queries . hospital_name ? '$cf_name' : null ) , total_facilities : { $sum : 1 } , icu_v : { $sum : '$icu_v' } , icu_o : { $sum : '$icu_o' } , isolbed_v : { $sum : '$isolbed_v' } , isolbed_o : { $sum : '$isolbed_o' } , beds_ward_v : { $sum : '$beds_ward_v' } , beds_ward_o : { $sum : '$beds_ward_o' } , mechvent_v : { $sum : '$mechvent_v' } , mechvent_o : { $sum : '$mechvent_o' } , icu_v_nc : { $sum : '$icu_v_nc' } , icu_o_nc : { $sum : '$icu_o_nc' } , nonicu_v_nc : { $sum : '$nonicu_v_nc' } , nonicu_o_nc : { $sum : '$nonicu_o_nc' } , mechvent_v_nc : { $sum : '$mechvent_v_nc' } , mechvent_o_nc : { $sum : '$mechvent_v_nc' } , } , } , { $project : output } , ] ) ; const res = await result . toArray ( ) ; if ( res [ 0 ] === undefined ) throw Error ( 'Your query didn\'t match any records.' ) ; res [ 0 ] . occupancy_rate = res [ 0 ] . beds . total_occupied / ( res [ 0 ] . beds . total_occupied + res [ 0 ] . beds . total_vacant ) ; res [ 0 ] . occupancy_rate = parseFloat ( res [ 0 ] . occupancy_rate . toFixed ( 2 ) ) || 0 ; return res [ 0 ] ; } catch ( e ) { throw Error ( e . message ) ; } } param { String } field param { String } dataset return { Promise } async getListOf ( field , dataset ) { const client = await this . connection ; const db = client . db ( ) ; let collection ; switch ( dataset . toLowerCase ( ) ) { case 'case_information' : collection = db . collection ( 'case_informations' ) ; switch ( field . toLowerCase ( ) ) { case 'regions' : field = 'region_res' ; break ; case 'provinces' : field = 'prov_res' ; break ; case 'cities' : field = 'city_mun_res' ; break ; case 'removal_types' : field = 'removal_type' ; break ; case 'age_groups' : field = 'age_group' ; break ; } break ; case 'facilities_information' : collection = db . collection ( 'facility_informations' ) ; switch ( field . toLowerCase ( ) ) { case 'regions' : field = 'region' ; break ; case 'provinces' : field = 'province' ; break ; case 'cities' : field = 'city_mun' ; break ; case 'hospitals' : field = 'cf_name' ; break ; } break ; default : throw Error ( `Invalid dataset: ${ dataset }` ) ; } const project = { } ; project [ field . toLowerCase ( ) ] = 1 ; const output = { _id : 0 } ; switch ( field . toLowerCase ( ) ) { case 'cf_name' : output . name = '$_id' ; break ; case 'city_mun' : case 'city_mun_res' : output . city = '$_id' ; break ; case 'region_res' : output . region = '$_id' ; break ; case 'prov_res' : output . province = '$_id' ; break ; default : output [ field . toLowerCase ( ) ] = '$_id' ; } console . log ( field ) ; try { const result = collection . aggregate ( [ { $project : project } , { $group : { _id : `$${ field . toLowerCase ( ) }` , } , } , { $sort : { _id : 1 } } , { $project : output } , ] ) ; const res = await result . toArray ( ) ; return res ; } catch ( e ) { throw Error ( e . message ) ; } } param { String } tableName return { Promise < void > } async truncate ( tableName ) { const client = await this . connection ; const db = client . db ( ) ; const items = await db . listCollections ( ) . toArray ( ) ; const promises = items . map ( ( item ) => { if ( item . name === tableName ) { console . log ( `Dropping: ${ item . name }` ) ; return db . collection ( tableName ) . drop ( ) ; } return Promise . resolve ( ) ; } ) ; try { await Promise . all ( promises ) ; } catch ( e ) { throw Error ( `[MySQLDatabase.js] ${ e . message }` ) ; } console . log ( 'Tables Dropped.' ) ; if ( items . length === 0 ) { console . log ( 'No collections in database' ) ; return 'No collections in database' ; } return 'Table not found' ; } param { String } tableName param { Object } fieldValueObj return { Promise < String > } async insert ( tableName , fieldValueObj ) { const client = await this . connection ; const db = client . db ( ) ; const collection = db . collection ( 'update_history' ) ; try { await collection . insertOne ( fieldValueObj , { forceServerObjectId : true } ) ; return 'Successfully inserted data' ; } catch ( e ) { throw Error ( e . message ) ; } } /**
     * End Database connection
     */ async endConnection ( ) { await this . connection . then ( async ( client ) => { client . close ( ) ; } ) ; } param { CaseInformation [ ] | FacilityInformation [ ] } csArr param { int } batchSize return { Promise < boolean > } async batchInsertDatabaseFromCSV ( csArr , batchSize = 10000 ) { console . log ( `\nPerforming batch insert (batch size: ${ batchSize }):` ) ; const isSuccess = true ; let lastRowIndex = 0 ; const csArrLength = csArr . length ; const batchRuns = Math . ceil ( csArr . length / batchSize ) ; for ( let i = 0 ; i < batchRuns ; i += 1 ) { console . log ( `Running batch ${ i + 1 }/${ batchRuns }` ) ; const nextLastRowIndex = lastRowIndex + batchSize ; const sliceStart = lastRowIndex ; const sliceEnd = ( nextLastRowIndex >= csArr . length ? csArrLength : nextLastRowIndex ) ; const csBatchArr = csArr . slice ( sliceStart , sliceEnd ) ; lastRowIndex = nextLastRowIndex ; const csvInfo = getCSVInfoObj ( csBatchArr ) ; const client = await this . connection ; const db = client . db ( ) ; await db . collection ( csvInfo . csvDbName ) . insertMany ( csvInfo . csvArr , { forceServerObjectId : true } ) ; console . log ( `Inserted: ${ csvInfo . csvArr . length } rows` ) ; } return isSuccess ; } param { CSVDatabase } csvDatabase return { Promise < boolean > } async updateDatabaseFromCSV ( csvDatabase , dbName ) { console . log ( '\nBegin Updating Database.' ) ; const cs = await csvDatabase . get ( ) ; console . log ( `Truncating ${ dbName } table, because it causes anomaly when we don't.` ) ; await this . truncate ( dbName ) ; console . log ( '\nBefore Updating Database: ' ) ; console . log ( `cs length in csv: ${ cs . length }` ) ; console . log ( `cs length in database: ${ await this . count ( dbName ) }` ) ; const res = await this . batchInsertDatabaseFromCSV ( cs , 25000 ) ; console . log ( '\nAfter Updating Database: ' ) ; console . log ( `cs length in csv: ${ cs . length }` ) ; console . log ( `cs length in database: ${ await this . count ( dbName ) }` ) ; if ( res ) console . log ( '\nSuccessfully transferred data from CSV to Database.\n' ) ; else console . log ( '\nFailed to transfer data from CSV to Database.\n' ) ; return res ; } } export default MongoDBDatabase ;