import { inject , injectable } from 'inversify' ; import { FindConditions , FindManyOptions , In , IsNull , LessThan , LessThanOrEqual , MoreThanOrEqual } from 'typeorm' ; import * as apid from '../../../api' ; import Reserve from '../../db/entities/Reserve' ; import { IReserveUpdateValues } from '../event/IReserveEvent' ; import IPromiseRetry from '../IPromiseRetry' ; import IDBOperator from './IDBOperator' ; import IReserveDB , { IFindRuleOption , IFindTimeRangesOption , IFindTimeSpecificationOption , IGetManualIdsOption , IReserveTimeOption , RuleIdCountResult , } from './IReserveDB' ; @ injectable ( ) export default class ReserveDB implements IReserveDB { private op : IDBOperator ; private promieRetry : IPromiseRetry ; constructor ( @ inject ( 'IDBOperator' ) op , @ inject ( 'IPromiseRetry' ) promieRetry ) { this . op = op ; this . promieRetry = promieRetry ; } param items return public async restore ( items ) { const connection = await this . op . getConnection ( ) ; const queryRunner = connection . createQueryRunner ( ) ; await queryRunner . startTransaction ( ) ; let hasError = false ; try { await queryRunner . manager . delete ( Reserve , { } ) ; for ( const item of items ) { await queryRunner . manager . insert ( Reserve , item ) ; } await queryRunner . commitTransaction ( ) ; } catch ( err ) { console . error ( err ) ; hasError = err ; await queryRunner . rollbackTransaction ( ) ; } finally { await queryRunner . release ( ) ; } if ( hasError ) { throw new Error ( 'restore error' ) ; } } param reserve return public async insertOnce ( reserve ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = connection . createQueryBuilder ( ) . insert ( ) . into ( Reserve ) . values ( reserve ) ; const insertedResult = await this . promieRetry . run ( ( ) => { return queryBuilder . execute ( ) ; } ) ; return insertedResult . identifiers [ 0 ] . id ; } param reserve public async updateOnce ( reserve ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = connection . createQueryBuilder ( ) . update ( Reserve ) . set ( reserve ) . where ( 'id = :id' , { id : reserve . id } ) ; await this . promieRetry . run ( ( ) => { return queryBuilder . execute ( ) ; } ) ; } param values public async updateMany ( values ) { const connection = await this . op . getConnection ( ) ; const queryRunner = connection . createQueryRunner ( ) ; await queryRunner . startTransaction ( ) ; let hasError = false ; try { if ( typeof values . delete !== 'undefined' && values . delete . length > 0 ) { const deleteIds = values . delete . map ( d => { return d . id ; } ) ; await queryRunner . manager . delete ( Reserve , deleteIds ) ; } if ( typeof values . insert !== 'undefined' && values . insert . length > 0 ) { for ( const newReserve of values . insert ) { const result = await queryRunner . manager . insert ( Reserve , newReserve ) ; newReserve . id = result . identifiers [ 0 ] . id ; } } if ( typeof values . update !== 'undefined' && values . update . length > 0 ) { for ( const u of values . update ) { await queryRunner . manager . update ( Reserve , u . id , u ) ; } } await queryRunner . commitTransaction ( ) ; } catch ( err ) { console . error ( err ) ; hasError = true ; await queryRunner . rollbackTransaction ( ) ; } finally { await queryRunner . release ( ) ; } if ( hasError === true ) { throw new Error ( 'ReserveUpdateManyError' ) ; } } param reserveId return public async findId ( reserveId ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = await connection . getRepository ( Reserve ) ; const result = await this . promieRetry . run ( ( ) => { return queryBuilder . findOne ( { where : { id : reserveId } , } ) ; } ) ; return typeof result === 'undefined' ? null : result ; } param option return public async findAll ( option ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = await connection . getRepository ( Reserve ) ; return await this . promieRetry . run ( ( ) => { return queryBuilder . findAndCount ( this . createFindOption ( option ) ) ; } ) ; } private createFindOption ( option ) { const findConditions = { } ; this . setReserveTypeOption ( option . type , findConditions ) ; if ( typeof option . ruleId !== 'undefined' ) { findConditions . ruleId = option . ruleId ; } const findOption = { where : findConditions , } ; if ( typeof option . offset !== 'undefined' ) { findOption . skip = option . offset ; } if ( typeof option . limit !== 'undefined' ) { findOption . take = option . limit ; } findOption . order = { startAt : 'ASC' , } ; return findOption ; } private setReserveTypeOption ( type , findConditions ) { if ( type === 'normal' ) { findConditions . isConflict = false ; findConditions . isSkip = false ; findConditions . isOverlap = false ; } else if ( type === 'conflict' ) { findConditions . isConflict = true ; findConditions . isSkip = false ; findConditions . isOverlap = false ; } else if ( type === 'skip' ) { findConditions . isConflict = false ; findConditions . isSkip = true ; findConditions . isOverlap = false ; } else if ( type === 'overlap' ) { findConditions . isConflict = false ; findConditions . isSkip = false ; findConditions . isOverlap = true ; } } param option return public async findLists ( option ? ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = connection . getRepository ( Reserve ) ; return await this . promieRetry . run ( ( ) => { return typeof option === 'undefined' ? queryBuilder . find ( ) : queryBuilder . find ( this . createFindListOption ( option ) ) ; } ) ; } private createFindListOption ( option ) { const findConditions = { startAt : LessThanOrEqual ( ( < apid . GetReserveListsOption > option ) . endAt ) , endAt : MoreThanOrEqual ( ( < apid . GetReserveListsOption > option ) . startAt ) , } ; return { where : findConditions } ; } param programId return public async findProgramId ( programId ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = connection . getRepository ( Reserve ) ; return await this . promieRetry . run ( ( ) => { return queryBuilder . find ( { where : { programId : programId } , } ) ; } ) ; } param option return public async findTimeRanges ( option ) { if ( option . times . length === 0 ) { return [ ] ; } const connection = await this . op . getConnection ( ) ; let queryBuilder = await connection . getRepository ( Reserve ) . createQueryBuilder ( 'reserve' ) ; const newTimes = [ ] ; const timeIndex = { } ; for ( const time of option . times ) { const key = time . startAt . toString ( ) + time . endAt . toString ( ) ; if ( typeof timeIndex [ key ] === 'undefined' ) { timeIndex [ key ] = true ; newTimes . push ( time ) ; } } option . times = newTimes ; option . times = option . times . reduce ( ( acc , cur , index ) => { if ( index === 0 ) { return acc ; } if ( acc [ acc . length - 1 ] . endAt === cur . startAt ) { acc [ acc . length - 1 ] . endAt = cur . endAt ; } else { acc . push ( cur ) ; } return acc ; } , option . times . slice ( 0 , 1 ) ) ; let timesQuery = '' ; const timesValues = { } ; option . times . forEach ( ( time , i ) => { const startAtName = `startAt${ i }` ; const endAtName = `endAt${ i }` ; const baseQuery = `(reserve.endAt >= :${ startAtName } and reserve.startAt < :${ endAtName })` ; timesQuery += i === option . times . length - 1 ? baseQuery : `${ baseQuery } or ` ; timesValues [ startAtName ] = time . startAt ; timesValues [ endAtName ] = time . endAt ; } ) ; queryBuilder = queryBuilder . where ( `(${ timesQuery })` , timesValues ) ; if ( option . hasSkip === false ) { queryBuilder = queryBuilder . andWhere ( 'reserve.isSkip = :isSkip' , { isSkip : false , } ) ; } if ( option . hasConflict === false ) { queryBuilder = queryBuilder . andWhere ( 'reserve.isConflict = :isConflict' , { isConflict : false , } ) ; } if ( option . hasOverlap === false ) { queryBuilder = queryBuilder . andWhere ( 'reserve.isOverlap = :isOverlap' , { isOverlap : false , } ) ; } if ( typeof option . excludeRuleId !== 'undefined' ) { queryBuilder = queryBuilder . andWhere ( '(reserve.ruleId <> :ruleId or reserve.ruleId is null)' , { ruleId : option . excludeRuleId , } ) ; } if ( typeof option . excludeReserveId !== 'undefined' ) { queryBuilder = queryBuilder . andWhere ( 'reserve.id <> :reserveId' , { reserveId : option . excludeReserveId , } ) ; } queryBuilder = queryBuilder . orderBy ( 'reserve.startAt' , 'ASC' ) ; return await this . promieRetry . run ( ( ) => { return queryBuilder . getMany ( ) ; } ) ; } param option public async findRuleId ( option ) { const connection = await this . op . getConnection ( ) ; const whereOption = [ { ruleId : option . ruleId } ] ; if ( option . hasSkip === false ) { whereOption . push ( { isSkip : false } ) ; } if ( option . hasConflict === false ) { whereOption . push ( { isConflict : false } ) ; } if ( option . hasOverlap === false ) { whereOption . push ( { isOverlap : false } ) ; } const queryBuilder = connection . getRepository ( Reserve ) ; return await this . promieRetry . run ( ( ) => { return queryBuilder . find ( { where : whereOption , order : { startAt : 'ASC' , } , } ) ; } ) ; } param baseTime return public async findOldTime ( baseTime ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = connection . getRepository ( Reserve ) ; return await this . promieRetry . run ( ( ) => { return queryBuilder . find ( { endAt : LessThan ( baseTime ) , } ) ; } ) ; } param option return public async findTimeSpecification ( option ) { const connection = await this . op . getConnection ( ) ; const queryBuilder = connection . getRepository ( Reserve ) ; const result = await this . promieRetry . run ( ( ) => { return queryBuilder . findOne ( { where : { channelId : option . channelId , startAt : option . startAt , endAt : option . endAt , ruleId : IsNull ( ) , } , } ) ; } ) ; return typeof result === 'undefined' ? null : result ; } param option public async getManualIds ( option ) { const connection = await this . op . getConnection ( ) ; let queryBuilder = connection . createQueryBuilder ( ) . select ( 'reserve.id' ) . from ( Reserve , 'reserve' ) . where ( 'reserve.ruleId is null' ) ; if ( option . hasTimeReserve === false ) { queryBuilder = queryBuilder . andWhere ( 'reserve.programId is not null' ) ; } queryBuilder = queryBuilder . orderBy ( 'reserve.id' , 'ASC' ) ; const result = await this . promieRetry . run ( ( ) => { return queryBuilder . getMany ( ) ; } ) ; return result . map ( r => { return r . id ; } ) ; } param ruleIds param type return public async countRuleIds ( ruleIds , type ) { const connection = await this . op . getConnection ( ) ; const whereOption = { ruleId : In ( ruleIds ) , } ; this . setReserveTypeOption ( type , whereOption ) ; const queryBuilder = connection . getRepository ( Reserve ) . createQueryBuilder ( 'reserve' ) . select ( [ 'ruleId, count(ruleId) as ruleIdCnt' ] ) . where ( whereOption ) . groupBy ( 'ruleId' ) ; return await this . promieRetry . run ( ( ) => { return queryBuilder . getRawMany ( ) ; } ) ; } }