Untitled

const Datastore = require('nedb-promises')
// const moment = require('moment')
const _ = require('lodash')
const md5 = require('md5')
const loki = require('lokijs')

const fs = require('fs')
const path = require('path')

const stringSimilarity = require('string-similarity')

const cliProgress = require('cli-progress')

const workProcCat = _.keyBy(require('./workProcCat').map(wpc => ({
    ...wpc,
    cleanDesc: wpc.Description.toLowerCase().replaceAll(/[^a-z]+/g, '')
})), 'cleanDesc')

const roleTypes = _.keyBy(require('./mwcRoleType'), 'RoleTypeName')

const roleConvert = {
    '2nd Off': 11000010,
    'Admin Off': 900000050,
    '2nd Eng': 11000008,
    'Chief Off': 90000009,
    'Cargo Eng': 900000021,
    'Master': 90000007,
    'Super': 90000008,


    'Chief Eng': 90000008,
    'Elect': 11000003,
    '3rd Eng': 11000009,
    'Cook Stwd': 900000058,
    '3rd Off': 11000011,
    '4th Eng': 11000007,
}

function clean (s) {
    return s.toLowerCase().replaceAll(/[^a-z0-9]/g, '')
}

function convertGenericName (name) {
    return name
        ? name
        .replace(/^((no\.|no|ae|me|number|num)\.*\s*\d+[abc]?)/gi, ' ')
        .replace(/\s+(no\.|no|ae|me|number|num|m)\d+$/gi, ' ')
        .replace(/^(PRT|STBD|STB|PORT|AFT|FWD|Starboard)\s+/gi, ' ')
        .replace(/(no\.|no|ae|me|number|num)\s+\d+\s+(PRT|STBD|STB|PORT|AFT|FWD|Starboard)/gi, ' ')
        .replace(/\s+\d+\s+(PRT|STBD|STB|PORT|AFT|FWD)/gi, ' ')
        .replace(/[\s\-]+\(?((no\.|no|ae|me|number|num)\.*\s*\d+[abc]?\)?)/gi, ' ')
        .replace(/\s+(PRT|STBD|STB|PORT|AFT|FWD|Starboard)(\s+|$)/gi, ' ')
        .replace(/\s+\((PRT|STBD|STB|PORT|AFT|FWD|Starboard|P|C|S|Stbd Aft|Stbd Fwd|Port Aft|Port Fwd)\)/gi, ' ')
        .replace(/\s+\((inner|outer)\)/gi, ' ')
        .replace(/\s+(P|C|S)$/gi, ' ')
        .replace(/\s+'?(A|B|C)'?\s+/g, ' ')
        .replace(/\s+\d+$/gi, '')
        .replace(/\s+/g, ' ')
        .replace(/^\-/g, '')
        .trim()
        : '';
}

const uom = require('./uom')

const {
    logger,
    InvalidReferenceError,
    queryLogger
} = require('./log')

const Entities = require('./entities')

async function main () {
    const f = fs.readdirSync(path.resolve(__dirname, '..'))
    for (let file of f) {
        if (file.endsWith('.log') || file.startsWith('injection.')) fs.unlinkSync(file)
    }

    const dryRun = false

    let START_ID = 805000000
    const targetDB = 'BGCUAT'
    // const targetDB = 'BGCUAT'
    
    const targetDBReplace = 'BGCUAT'
    // const targetDBReplace = 'BGCUAT'

    const tt = require('./target_tables')(targetDB)
    const target = require('knex')({
        client: 'mssql',
        log: { error: m => logger.error(m) },
        connection: {
            // password: 'Aa@123456',
            // password: 'mspwstr0@ng',
            // server: "192.168.1.21",

            // server: "127.0.0.1",
            // server: '20.231.30.144',

            // user: 'sa',
            // user: 'donald3',

            // database: targetDB,
            port: 1433,
            // options: {
            //     encrypt: false,
            //     trustServerCertificate: true,
            // },
            requestTimeout: 600000,

            server: "127.0.0.1",
        user: 'donald2',
        password: 'mspwstr0@ng',
        port: 1433,
        stream: false,
        options: {
            database: targetDB,
            enableArithAbort: true,
            encrypt: false,
            // instanceName: 'AV01',
            trustServerCertificate: true
        }
        }
    });
    const ent = new Entities(target, tt)

    let queryLogFile = path.resolve(__dirname, '..', 'injection.sql')
    let logLines = 0
    let closingStreams = []
    let queryLogStream = fs.createWriteStream(queryLogFile)

    const restartQueryLog = (name) => {
        closingStreams.push(new Promise(r => queryLogStream.end(r)))
        queryLogFile = path.resolve(__dirname, '..', 'injection.'+name+'.sql')
        queryLogStream = fs.createWriteStream(queryLogFile)
        logLines = 0
    }

    const logQuery = (q) => {
        if (q.startsWith('select')) return;
        q = q.replace(`[${targetDB}]`, `[${targetDBReplace}]`)
        if (q.startsWith('insert')) {
            let v = q.indexOf(' values ')
            v = v+' values '.length
            queryLogStream.write(q.substring(0, v))
            queryLogStream.write(`\n`)
            q = q.substring(v).replaceAll('), (', `),\n(`)
            q = q.replaceAll(/([(\s]{1})(false)([,)]{1})/g, '$10$3')
            q = q.replaceAll(/([(\s]{1})(true)([,)]{1})/g, '$11$3')
            let m = q.split(/\r\n|\r|\n/).length
            logLines = logLines + m
        }
        queryLogStream.write(q)
        queryLogStream.write(`\n`)
    }

    target.on('start', builder => {
        let q = builder.toString()
        q = q.replace(';select @@rowcount', ';')
        logQuery(q)
        // queryLogger.info(q)
        return builder
    })

    logger.info('Target Cleanup Start')
    for (let t of Object.values(tt)) {

        if (t.indexOf('[swFleetSystem]') !== -1) continue;
        if (t.indexOf('[mwcRoleType]') !== -1) continue;

        if (t.indexOf('[mwrChangeLogColumn]') !== -1) {
          await target.from(t)
            .where('mwrChangeLogKey', '>=', 2064)
            .delete()

            continue
        };
        
        if (t.indexOf('[mwrChangeLog]') !== -1) {
            await target.from(t)
            .where('ChangeDateTime', '>=', '2022-06-01 00:00:45.663')
            .delete()

            continue
        };

        await target.from(t)
        .where('ID', '>=', START_ID)
        .delete()
    }
    logger.info('Target Cleanup Finish')

    const dbSources = [
        'AMADI',
        'AMALI',
        'AMANI',
        'ARKAT',
    ]

    const multibar = new cliProgress.MultiBar({
        clearOnComplete: false,
        hideCursor: true
    }, cliProgress.Presets.shades_grey);


    let mwcSitesID = START_ID
    let swWorkProcCatID = START_ID
    let swWorkProcFleetID = START_ID

    const swEqptHistoryUpdate = []
    const swWorkProcFleetTaskInserts2 = []

    const _swWorkProcInserts = []
    const _swCoEqptWPInserts = []
    
    const _swWpStdTaskInserts = []
    const _swEqptWorkProcInserts = []

      
    async function batchStore(data, table, batchSize = null) {
        if (data.length === 0) return
        const fields = Object.keys(data[0]).length
        if (!batchSize) batchSize = Math.min(Math.floor(2000 / fields), 900)
        const bar1 = multibar.create(Math.ceil(data.length / batchSize), 0)
        let batches = Math.ceil(data.length / batchSize)
        try {
            for (let i = 0; i < batches; i++) {
                const c = data.splice(0, batchSize)
                let q = target(table).insert(c)
                if (dryRun) {
                    logQuery(q.toString())
                } else {
                    await q
                }
                bar1.increment()
            }
        } catch (e) {
            logger.error(e.message)
            throw e
        } finally {
            bar1.stop()
            multibar.remove(bar1)
        }
    }

    const swCompanyArticleInserts = []

    function getOrCreateWorkProcCat(Description) {
        if (!Description) return null
        const cleanDesc = Description.toLowerCase().replaceAll(/[^a-z]+/g, '')
        if (workProcCat[cleanDesc]) return workProcCat[cleanDesc].ID
        const ID = ++swWorkProcCatID
        swWorkProcCatInserts.push({
            ID,
            Description: Description.substring(0, 20),
        })
        workProcCat[cleanDesc] = { ID }
        return ID
    }

    async function * iterate (source, table, order = null, fields = undefined, perPage = 100) {
        let query
        if (typeof table === 'string') {
            query = source.from(table)
        } else {
            query = table
            table = query._single.table
        }

        const [{ '': count }] = await source(table).count()
        if (count === 0) return

        let bar
        if (multibar) bar = multibar.create(count, 0, null, {
            format: `[{bar}] {percentage}% | {value}/{total} [${table}]`
        })

        const pages = Math.ceil(count / perPage) - 1
        for (let i = 0; i <= pages; i++) {
            let q = query.clone().select(fields).limit(perPage).offset(i * perPage)
            if (order) q.orderBy(...(Array.isArray(order) ? order : [order]))
            const data = await q
            for (let d of data) {
                yield d
                if (bar) bar.increment()
            }
        }
        if (bar) bar.stop();
    }

    const sites = {}
    // Create sites
    restartQueryLog('1.sites')
    for (let db of dbSources) {
        logger.defaultMeta.db = db
        const source = require('knex')({
            client: 'mssql',
            connection: {
                // password: 'HqHZfKNjkks7',
                // server: 'amos-sql.makini.io',
                // password: 'Aa@123456',
                password: 'mspwstr0@ng',
                // server: "192.168.1.21",
                
                // server: "127.0.0.1",
                server: 'localhost',
                // server: '20.231.30.144',

                // user: 'sa',
                user: 'donald3',

                database: db,
                // port: 1433,
                options: {
                    encrypt: false,
                    trustServerCertificate: true,
                },
                requestTimeout: 6000,
            }
        });

        const mwcSitesKey = ++mwcSitesID
        await target(tt.mwcSites).insert({
            ID: mwcSitesKey,
            SiteID: db,
            SiteName: db,
        })

        const site = ent.getSite(mwcSitesKey)
        sites[db] = {
            site,
            mwcSitesKey,
            source,
        }
    }

    const swWorkProcFleetInserts = []
    const swWorkProcCatInserts = []
    const swWorkProcFleetTaskInserts = []
    let swWorkProcFleetTaskID = START_ID
    let hashes = []
    let pmHashes = {}
    let pmMap = {}
    let pmSchedule = {}
    const swWorkProcFleet = ent.db.addCollection('swWorkProcFleet', {
        unique: ['ID'],
        indices: ['JobCode', 'ComponentCode']
    })//new Datastore({ inMemoryOnly: true });
    restartQueryLog('2.wp')

    function getIntervals(WSType, Frequency) {
        switch (WSType.toLowerCase()) {
            case 'h':
                return {
                    IntervalRH: Frequency,
                }
            case 'm':
                return {
                    IntervalCalUnit: 'M',
                    IntervalCal: Frequency
                }
            default:
                InvalidReferenceError.log('Invalid WSType', { WSType }, { tblPMWorkSheet: PMWorksheetID })
                return {}
        }
    }

    function getRole(IssueTo) {
        if (roleTypes[IssueTo]) return roleTypes[IssueTo].ID
        else if (roleConvert[IssueTo]) return roleConvert[IssueTo]
        else return -1
        // else return 90000008
    }

    for (let db of dbSources) {
        logger.defaultMeta.db = db
        const {
            // site,
            source,
        } = sites[db]

        const q = source.from({
            pm: 'tblPMWorkSheet'
        }).leftJoin({
            tj: 'tblJob',
        }, 'pm.JobCode', '=', 'tj.JobCode')
        .orderBy('Active', 'DESC')
        .orderBy('SafetyNotes')

        for await (let {
            PMWorksheetID,
            JobCode,
            WSType,
            Frequency,
            FullJobText,
            SafetyNotes,
            ShipSafetyNotes,
            BriefDescription,
            IssueTo,
            ShortDescription,
            ComponentCode,
        } of iterate(source, q, null, [
            'pm.PMWorksheetID',
            'pm.ComponentCode',
            'pm.JobCode',
            'pm.ShortDescription',
            'pm.WSType',
            'pm.Frequency',
            'pm.FullJobText',
            'pm.SafetyNotes',
            'pm.ShipSafetyNotes',
            'pm.IssueTo',
            'tj.BriefDescription'
        ], 500)) {
            let stdTasks = [
                SafetyNotes ? await ent.createStdTask(SafetyNotes) : null,
                ShipSafetyNotes ? await ent.createStdTask(ShipSafetyNotes) : null
            ].filter(s => !!s)

            FullJobText = FullJobText.trim()
            ShortDescription = convertGenericName(ShortDescription.trim())

            const c = clean(ShortDescription)
            const hash = md5(WSType+Frequency+c)
            
            const {
                IntervalCal = null,
                IntervalRH = null,
                IntervalCalUnit = null,
            } = getIntervals(WSType, Frequency)
            if (!IntervalCal && !IntervalRH) continue;

            let mwcRoleTypeKey = getRole(IssueTo)

            if(!pmHashes[db]) {
                pmHashes[db] = {}
            }

            let i = (typeof pmHashes[db][hash] !== 'undefined') ? pmHashes[db][hash] : -1

            if (i !== -1) {
                let same = swWorkProcFleetInserts[i]
                if (same.Description.length < FullJobText) swWorkProcFleetInserts[i].Description = FullJobText
                // pmMap[PMWorksheetID] = same.ID
                if(!pmMap[db]) {
                    pmMap[db] = {}
                }

                pmMap[db][PMWorksheetID] = same.ID

                if(!pmSchedule[db]) {
                    pmSchedule[db] = {}
                }

                pmSchedule[db][PMWorksheetID] = [
                    same.IntervalRH || '-',
                    same.IntervalCal || '-',
                    same.IntervalCalUnit || '-',
                    same.swWorkProcCategoryKey || '-',
                ].join('')
                continue
            }

            let WpID = (swWorkProcFleet.find({ JobCode, ComponentCode }).length+1).toString().padStart(4, '0')
            const ID = ++swWorkProcFleetID
            const swWorkProcCategoryKey = getOrCreateWorkProcCat(BriefDescription)
            const Title = ShortDescription.substring(0, 75)
            const wp = {
                ID,
                Title,
                RescheduleType: IntervalRH ? 2 : 1,
                IntervalRH,
                // swDepartmentKey,
                Description: convertGenericName(FullJobText),
                IntervalCalUnit,
                IntervalCal,
                ScheduleBasis: 'C',
                IsEqptOverhaul: FullJobText.substring(0, 200).toLowerCase().indexOf('overhaul') !== -1,
                mwcRoleTypeKey,
                // EstHours,
                // GracePeriodCal,
                swWorkProcCategoryKey,
                swWorkProcFleetTypeKey: 1,
                WpID: `${JobCode}-${WpID}`,
                // IsCritical,
                ComponentCode,
                JobCode,
                tblPMWorkSheet__ComponentCode: ComponentCode,
                tblPMWorkSheet__PMWorksheetID: PMWorksheetID,
            }

            if(!pmHashes[db]) {
                pmHashes[db] = {}
            }
            pmHashes[db][hash] = (swWorkProcFleetInserts.push(wp) - 1)

            // pmHashes[hash] = (swWorkProcFleetInserts.push(wp) - 1)


            await swWorkProcFleet.insert({
                ...wp,
                stdTasks,
                ComponentCode,
                JobCode,
            })

            if(!pmMap[db]) {
                pmMap[db] = {}
            }

            if(!pmSchedule[db]) {
                pmSchedule[db] = {}
            }

            pmMap[db][PMWorksheetID] = ID
            pmSchedule[db][PMWorksheetID] = [
                IntervalRH || '-',
                IntervalCal || '-',
                IntervalCalUnit || '-',
                swWorkProcCategoryKey || '-',
            ].join('')

            // pmMap[PMWorksheetID] = ID
            // pmSchedule[PMWorksheetID] = [
            //     IntervalRH || '-',
            //     IntervalCal || '-',
            //     IntervalCalUnit || '-',
            //     swWorkProcCategoryKey || '-',
            // ].join('')

            if(stdTasks.length > 0) {
                swWorkProcFleetTaskInserts.push({
                    ID: ++swWorkProcFleetTaskID,
                    smStdTaskKey: stdTasks[0],
                    swWorkProcFleetKey: ID,
                    DisplaySequence: 1,
                })

                if(stdTasks[1]) {
                    swWorkProcFleetTaskInserts2.push({
                        // ID: ++swWorkProcFleetTaskID,
                        smStdTaskKey: stdTasks[0],
                        swWorkProcFleetKey: ID,
                        DisplaySequence: 2,
                    })
                }
            }

            // swWorkProcFleetTaskInserts.push(...stdTasks.map((smStdTaskKey, i) => ({
            //     ID: ++swWorkProcFleetTaskID,
            //     smStdTaskKey,
            //     swWorkProcFleetKey: ID,
            //     DisplaySequence: i+1,
            // })))
        }
    }
    await batchStore(swWorkProcCatInserts, 'swWorkProcCategory')
    await batchStore(swWorkProcFleetInserts.map(w => _.omit(w, 'ComponentCode', 'JobCode')), 'swWorkProcFleet')

    await batchStore(swWorkProcFleetTaskInserts, tt.swWorkProcFleetTask)
    
    delete hashes
    delete pmHashes

    restartQueryLog('3.global')
    for (let db of dbSources) {
        logger.defaultMeta.db = db

        const {
            source,
            site,
        } = sites[db]

        {
            let swMfgInserts = []
            for await (let { Manufacturer, ManufacturerID } of iterate(source, 'tblManufacturer', 'ManufacturerID')) {
                if (!Manufacturer) continue;
                const m = ent.createManufacturer({ Manufacturer })
                site.addMfgRef({ ID: m.ID,ManufacturerID })
                if (!m.$loki) swMfgInserts.push(m)
            }
            await batchStore(swMfgInserts, tt.swMfg)
            delete swMfgInserts
        }

        {
            let swRHMeterInserts = []
            let AssemblyAdded = [];

            let qmc = source.from({ a: 'tblAssembly' }).select([
            'a.AssemblyCode',
            'a.AssemblyDescription',
            'a.RunningHours',
            'a.LastUpdate',
            'l.CreatedDate as LastUpdate2',
        ]).leftJoin({ l: 'tblAssemblyLog' }, 'l.AssemblyCode', 'a.AssemblyCode')
        .orderBy('l.CreatedDate', 'DESC')
        
        for await (let m of iterate(source, qmc, 'AssemblyCode', null, 50000000)) {

                const AssemblyCode = m.AssemblyCode

                if(AssemblyAdded.includes(AssemblyCode)) continue

                m = site.createMeter({
                    ...m,
                    LastUpdate: m.LastUpdate2 || m.LastUpdate
                })

                if (m) {
                    AssemblyAdded.push(AssemblyCode)
                    swRHMeterInserts.push({ ...m, tblAssembly__AssemblyCode: AssemblyCode })
                }
            }
            await batchStore(swRHMeterInserts, tt.swRHMeter)
            delete swRHMeterInserts
        }

        {
            let swSparesStoreInserts = []
            for await (let m of iterate(source, 'tblLocation', 'LocationID')) {
                const locationId = m.LocationID
                m = site.createSparesStore(m)
                if (m) swSparesStoreInserts.push({ ...m, tblLocation__LocationID: locationId })
            }
            await batchStore(swSparesStoreInserts, tt.swSparesStore)
            delete swSparesStoreInserts
        }

        {
            const swRHMeterReadingInserts = []
            for await (let m of iterate(source, 'tblAssemblyLog', 'AssemblyCode')) {
                const AssemblyCode = m.AssemblyCode

                m = site.createMeterReading(m)
                if (m) swRHMeterReadingInserts.push({ ...m, tblAssemblyLog__AssemblyCode: AssemblyCode})
            }
            await batchStore(swRHMeterReadingInserts, tt.swRHMeterReading)
        }
    }

    await ent.populateFS()

    restartQueryLog('4.eq')
    for (let db of dbSources) {
        logger.defaultMeta.db = db
        const {
            site,
            source,
        } = sites[db]

        const select = [
            'ComponentDescription',
            'AssemblyCode',
            'ManufacturerID',
            'Active',
            'ComponentCode',
            'Type',
            'CriticalComponent',
            'SerialNumber',
            'CMSNumber',
            'TotalRunningHours',
            'Comments',
            'InstructionBookNumber',
        ]

        const allJobs = _.groupBy(await source.from('tblPMWorkSheet').select([
            'ComponentCode',
            'PMWorksheetID',
        ]), 'ComponentCode')

        const swEquipmentInserts = []

        for await (let c of iterate(
            source,
            'tblComponent',
            ['InstructionBookNumber', 'DESC'],
            select
        )) {
            if (!c.ComponentDescription) {
                InvalidReferenceError.log('Empty component name', { tblComponent: c.Reference })
                continue;
            }

           
            const jobs = (allJobs[c.ComponentCode] || []).map(j => pmSchedule[db][j.PMWorksheetID]).sort().join(',')

            const e = await site.createEquipment({
                ...c,
                jobs,
            }, db)
            console.log(db)


            if (e) swEquipmentInserts.push({ ...e, tblComponent__ComponentCode: c.ComponentCode})
        }

        await batchStore(swEquipmentInserts, tt.swEquipment, null, 4)
    }


    let swSparesID = START_ID
    let swSparesMfgID = START_ID
    let swSparesLocationsID = START_ID
    let swCompanyEqptCoArtID = START_ID
    let swCompanyEqptCoArtInserts = []
    let traceCoArt = {}
    // const swSpares = ent.db.addCollection('swSpares', {
    //     indices: ['swCompanyArticleKey', 'PartNumber', 'ComponentCode', 'swMfgKey', 'mwcSitesKey']
    // })

    const updateDateExpire = []
    restartQueryLog('5.spares')

    for (let db of dbSources) {
        logger.defaultMeta.db = db
        const {
            site,
            source,
            mwcSitesKey,
        } = sites[db]

        const swSparesInserts = []

        const swSparesMfgInserts = []
        const swSparesLocationsInserts = []

        const clearSpares = async () => {
            await batchStore(swCompanyArticleInserts, tt.swCompanyArticle)
            await batchStore(swSparesInserts, tt.swSpares)
            await batchStore(swSparesMfgInserts, tt.swSparesMfg)
            await batchStore(swSparesLocationsInserts, tt.swSparesLocations)
        }

        let ComponentCodeAdded = {};

        let sqp = source.from({ p: 'tblPart' })
        .select([
            'p.FullDescription',
            'p.ComponentCode',
            'p.UnitOfMeasure',
            'p.ManufacturersPartNumber',
            'p.InstructionBookReference',
            'p.LocationID',
            'p.PartNumber',
            'p.RemainingOnBoard',
            'p.QtyAllocated',
            'p.MinHoldingLevel',
            'p.Price',
            'pe.ExpiryDate',
        ])
        // .innerJoin({ c: 'tblComponent' }, 'c.ComponentCode', 'p.ComponentCode')
        .leftJoin({ pe: 'tblPartExpiry' }, function () {
            this.on('pe.ComponentCode', '=', 'p.ComponentCode').andOn(
                'pe.PartNumber',
                '=',
                'p.PartNumber'
            )
        })
        .whereNot('p.FullDescription', 'like', '%see no.%')
        .orderBy('pe.ExpiryDate', 'DESC')

        for await (let p of iterate(source, sqp, 'ComponentCode', null, 500)) {
            let {
                FullDescription: Description,
                ComponentCode,
                UnitOfMeasure,
                ManufacturersPartNumber: MfgPartNo,
                InstructionBookReference: DrawingNumber,
                LocationID,
                PartNumber,

                RemainingOnBoard: QtyOnHand,
                QtyAllocated: QtyInUse,
                MinHoldingLevel: MinLevel,
                Price: AvgPrice,
                ExpiryDate: DateExpire,
            } = p

            if (!ComponentCode) continue;
            if (!Description) continue;
            if (Description.toLowerCase().indexOf('see no.') !== -1) continue;


            if(!ComponentCodeAdded[ComponentCode]) {
                ComponentCodeAdded[ComponentCode] = {}
            }

            if(ComponentCodeAdded[ComponentCode][PartNumber]) {
                continue
            }

            ComponentCodeAdded[ComponentCode][PartNumber] = false
            const comp = site.swEquipment.findOne({ ComponentCode, db})
            // const comp = site.swEquipment.by('ComponentCode', ComponentCode)

            if (!comp) {
                InvalidReferenceError.log('Invalid component code', { ComponentCode }, { tblPart: PartNumber })
                continue;
            }
            UnitOfMeasure = UnitOfMeasure.trim().toUpperCase()

            const swUnitMeasKey = uom[UnitOfMeasure]

            if (!swUnitMeasKey) {
                InvalidReferenceError.log('Invalid UOM', { UnitOfMeasure }, { tblPart: PartNumber })
                continue;
            }

            const PartName = Description.split(/(\r|\n)+/)[0]

            let {
                MfgName,
                MfgModel,
                swMfgKey,
                swCompanyEqptKey,
                IsCritical
            } = comp

            const data = {
                PartName,
                Description,
                MfgPartNo,
                DrawingNumber,
                MfgName,
                MfgModel,
                swMfgKey,
                swUnitMeasKey,
                ComponentCode,
                PartNumber
            }

            const { data: article, exists } = ent.getOrCreateCompanyArticle(data, swCompanyEqptKey)
            const swCompanyArticleKey = article.ID
            let ID = ++swSparesID

            const e = site.swSpares.find({ swCompanyArticleKey })

            
            if (e.length === 0) {
                ComponentCodeAdded[ComponentCode][PartNumber] = true

                site.swSpares.insert({
                    ID,
                    PartNumber,
                    ComponentCode,
                    swMfgKey,
                    swCompanyArticleKey,
                    swCompanyEqptKey,
                    IsCritical
                })

                swSparesInserts.push({
                    ID,
                    ..._.omit(data, 'ComponentCode', 'PartNumber'),
                    mwcSitesKey,
                    QtyOnHand,
                    QtyInUse,
                    MinLevel,
                    AvgPrice,
                    swUnitMeasKey,
                    swCompanyArticleKey,
                    DateExpire,
                    tblPart__ComponentCode: ComponentCode,
                    tblPart__PartNumber: PartNumber
                })

                swCompanyEqptCoArtInserts.push({
                    ID: ++swCompanyEqptCoArtID,
                    swCompanyArtKey: swCompanyArticleKey,
                    swCompanyEqptKey,
                    PartID: PartNumber,
                    DrawingNo: DrawingNumber,
                })

                // if(!traceCoArt[swCompanyEqptKey]) {
                //     traceCoArt[swCompanyEqptKey] = {};

                //     if(!traceCoArt[swCompanyEqptKey][PartNumber]) {
                //         traceCoArt[swCompanyEqptKey][PartNumber] = false
                //     }
                // }

                // if(!traceCoArt[swCompanyEqptKey][PartNumber]) {
                //     traceCoArt[swCompanyEqptKey][PartNumber] = true;

                //     swCompanyEqptCoArtInserts.push({
                //         ID: ++swCompanyEqptCoArtID,
                //         swCompanyArtKey: swCompanyArticleKey,
                //         swCompanyEqptKey,
                //         PartID: PartNumber,
                //         DrawingNo: DrawingNumber,
                //     })
                // }

                // tblPartExpiry.swSparekey /Exiredate =  swSpares.Exiredate

                // tblMaintenanceCompleted.PMWorksheedId = 0  => unschedule

                if (DateExpire) {
                  updateDateExpire.push({
                    ID,
                    DateExpire,
                  });
                }
            } else {
                ID = _.head(e).ID
            }

            if (exists) {
                // const existing = await swSpares.findOne({ swCompanyArticleKey })
                // ID = existing.ID
            } else {
                swCompanyArticleInserts.push(article)

                if (swMfgKey) swSparesMfgInserts.push({
                    ID: ++swSparesMfgID,
                    swSparesKey: ID,
                    swMfgKey,
                    mwcSitesKey,
                    MfgPartNo,
                })


                
            }

            if (LocationID) {
                const loc = site.swSparesStore.by('LocationID', LocationID)
                if (!loc) {
                } else {
                    swSparesLocationsInserts.push({
                        ID: ++swSparesLocationsID,
                        swSparesKey: ID,
                        swSparesStoreKey: loc.ID,
                        mwcSitesKey,
                        QtyOnHandLocation: QtyOnHand,
                        tblLocation__LocationID: LocationID
                    })
                }
            }
            if (swSparesInserts.length >= 5000) await clearSpares()
        }
        await clearSpares()

    }
    // swCompanyEqptCoArtInserts = _.uniq(swCompanyEqptCoArtInserts)
    await batchStore(swCompanyEqptCoArtInserts, tt.swCompanyEqptCoArt)

    // 8.updateDateExpire
    // DATE: April 17 2023 - Donald - Datafac
    // ISSUE: swSpares.DateExpire is missing
    // SOLUTION: Create SQL query then run SQL query in database
    restartQueryLog("8.buildDateExpireSQL");


    // const buildDateExpireSQL = (ID, DateExpire) => {
    //     const q = target(tt.swSpares).where('ID', '=', ID).update({ DateExpire })
    //     logQuery(q.toString().replace(";select @@rowcount", ";"))
    // }

    // _.map(updateDateExpire, ({ ID, DateExpire }) => buildDateExpireSQL(ID, DateExpire))
    // END 8.updateDateExpire

    let swCoEqptWPID = START_ID
    let swWorkProcID = START_ID
    let swWpStdTaskID = START_ID
    let swSparesAdjustID = START_ID
    let swEqptHistoryID = START_ID
    const swCoEqptWPInserts = []
    let i = 0
    let swCompEqptWPStored = []

    let swWorkProcIDinserted = {}

    for (let db of dbSources) {
        logger.defaultMeta.db = db
        restartQueryLog('6.work.'+(++i))
        const {
            site,
            source,
            mwcSitesKey,
        } = sites[db]
        const swWorkProc = site.db.addCollection('swWorkProc', {
            unique: ['PMWorksheetID'],
        })

        const swWorkProcInserts = []
        const swWpStdTaskInserts = []
        const swEqptWorkProcInserts = []

        const q = source.from('tblPMWorkSheet')
        .select([
            'PMWorksheetID',
            'ComponentCode',
            'ShortDescription',
            'FullJobText',
            'JobCode',
            'ClassSocietySurveyCode',
            'DateLastDone',
            'HoursDone',
            'Active',
        ])
        .orderBy('Active', 'DESC')
        .orderBy('PMWorksheetID')
        .where('ComponentCode', '800.01.01')
        for await (let pm of iterate(source, q, null)) {
            let {
                PMWorksheetID,
                ComponentCode,
                ShortDescription: Title,
                FullJobText: Description,
                JobCode: ProcedureID,
                ClassSocietySurveyCode: RegulationID,
                DateLastDone: LastCompleted,
                HoursDone: LastCompletedRH,
                Active,
            } = pm

            const comp = site.swEquipment.findOne({ ComponentCode, db})
            // const comp = site.swEquipment.by('ComponentCode', ComponentCode)

            if (!comp || !comp.ID) {
                InvalidReferenceError.log('Invalid component code', { WSComponentCode: ComponentCode }, { tblPMWorkSheet: PMWorksheetID })
                continue;
            }
            if (swCompEqptWPStored.indexOf(comp.swCompanyEqptKey) !== -1) {
                logger.error('Repeating job for '+ComponentCode+' '+PMWorksheetID)
                continue;
            }

            // if(!pmMap[db]) {
            //     pmMap[db] = {}
            // }

            const wpfId = pmMap[db][PMWorksheetID]
            // const wpfId = pmMap[PMWorksheetID]
            const {
                ID: swWorkProcFleetKey,
                stdTasks,
                RescheduleType,
                IntervalCalUnit,
                IntervalCal,
                WpID,
                mwcRoleTypeKey,
                IntervalRH,
                IsEqptOverhaul,
            } = swWorkProcFleet.by('ID', wpfId)

            let uq = [comp.ID+'-'+swWorkProcFleetKey]

            if (typeof swWorkProcIDinserted[uq] !== 'undefined') {
                swWorkProc.insert({
                    PMWorksheetID,
                    ID: swWorkProcIDinserted[uq],
                    swWorkProcFleetKey,
                })
                InvalidReferenceError.log('debug PMWorksheetID', {PMWorksheetID})
                InvalidReferenceError.log('debug db', {db})

                _swWorkProcInserts.push({
                    mwcSitesKey,
                    Title: Title.substring(0, 50),
                    Description,
                    RescheduleType,
                    IntervalRH,
                    IntervalCalUnit,
                    IntervalCal,
                    IsEqptOverhaul,
                    ScheduleBasis: 'C',
                    mwcRoleTypeKey,
                    WpID,
                    tblPMWorkSheet__ComponentCode: ComponentCode,
                    tblPMWorkSheet__PMWorksheetID: PMWorksheetID,
                })

                _swCoEqptWPInserts.push({
                    swWorkProcFleetKey,
                    swCompanyEqptKey: comp.swCompanyEqptKey,
                    tblPMWorkSheet__ComponentCode: ComponentCode
                })

                _swWpStdTaskInserts.push(...stdTasks.map((smStdTaskKey, i) => ({
                    smStdTaskKey,
                    // swWorkProcKey: ID,
                    DisplaySequence: i+1,
                    mwcSitesKey,
                    tblPMWorkSheet__ComponentCode: ComponentCode,
                    tblPMWorkSheet__PMWorksheetID: PMWorksheetID,
                })))
    
                _swEqptWorkProcInserts.push({
                    mwcSitesKey,
                    swEquipmentKey: comp.ID,
                    ProcedureID,
                    swRHMeterKey: IntervalRH ? comp.swRhMeterKey : null,
                    RegulationID,
                    LastCompleted,
                    LastCompletedRH,
                    swWorkProcFleetKey,
                    Deactivate: !Active,
                    tblPMWorkSheet__PMWorksheetID: PMWorksheetID,
                    tblPMWorkSheet__ComponentCode: ComponentCode
                })

                continue;
            }


            swCoEqptWPInserts.push({
                ID: ++swCoEqptWPID,
                swWorkProcFleetKey,
                swCompanyEqptKey: comp.swCompanyEqptKey,
                tblPMWorkSheet__ComponentCode: ComponentCode
            })
            const ID = ++swWorkProcID
            swWorkProcIDinserted[uq] = ID

            swWorkProcInserts.push({
                ID,
                mwcSitesKey,
                Title: Title.substring(0, 50),
                Description,
                RescheduleType,
                IntervalRH,
                IntervalCalUnit,
                IntervalCal,
                IsEqptOverhaul,
                ScheduleBasis: 'C',
                mwcRoleTypeKey,
                WpID,
                tblPMWorkSheet__ComponentCode: ComponentCode,
                tblPMWorkSheet__PMWorksheetID: PMWorksheetID,
            })

            swWpStdTaskInserts.push(...stdTasks.map((smStdTaskKey, i) => ({
                ID: ++swWpStdTaskID,
                smStdTaskKey,
                swWorkProcKey: ID,
                DisplaySequence: i+1,
                mwcSitesKey,
                tblPMWorkSheet__ComponentCode: ComponentCode,
                tblPMWorkSheet__PMWorksheetID: PMWorksheetID,
            })))

            swEqptWorkProcInserts.push({
                // ID: ++swEqptWorkProcID,
                ID,
                mwcSitesKey,
                swEquipmentKey: comp.ID,
                ProcedureID,
                // swWorkProcKey: ID,
                // NextScheduledDate,
                // NextScheduledRH,
                // swRegAgencyKey,
                swRHMeterKey: IntervalRH ? comp.swRhMeterKey : null,
                // OpenSwEqptHistoryKey,
                // swEqptWorProcSpecialType,
                RegulationID,
                LastCompleted,
                LastCompletedRH,
                swWorkProcFleetKey,
                Deactivate: !Active,
                tblPMWorkSheet__PMWorksheetID: PMWorksheetID,
                tblPMWorkSheet__ComponentCode: ComponentCode
            })

            await swWorkProc.insert({
                PMWorksheetID,
                ID,
                swWorkProcFleetKey,
            })
        }
        await batchStore(swWorkProcInserts, tt.swWorkProc)
        await batchStore(swWpStdTaskInserts, tt.swWpStdTask)
        await batchStore(swEqptWorkProcInserts, tt.swEqptWorkProc)

        // const swEqptHistory = new Datastore({ inMemoryOnly: true });
        const swEqptHistoryInserts = []
        let swEqptSparesHistoryInserts = []
        let swSparesAdjustInserts = []

        let qmc = source.from({ mc: 'tblMaintenanceCompleted' })
        // .leftJoin({ mcs: 'tblMaintenanceCompletedClassSurvey' }, 'mcs.MaintenanceCompletedID','mc.MaintenanceCompletedID', )
        .innerJoin({ c: 'tblComponent' }, 'mc.ComponentCode', 'c.ComponentCode')
        .leftJoin({ p: 'tblPMWorksheet' }, 'mc.PMWorksheetID', 'p.PMWorksheetID')
        .select([
            'mc.ComponentCode',
            'mc.PMWorksheetID',
            'mc.MaintenanceCompletedID',
            'mc.BriefJobDescription',
            'mc.DateCompleted',
            'mc.HoursCompleted',
            'mc.Status',
            'mc.IssuedTo',
            'mc.Unscheduled',
            'mc.Created',
            'mc.DateDue',
            'mc.EngineerReport',
            'mc.ManHours',
            'mc.DeferDate',
            'mc.DeferReason',
            'mc.ReturnedDate',
            'mc.SafetyNotes',
            'mc.SpecialTools',
            'mc.FullJobText',
            // 'mcs.ClassSocietySurveyCode as cssc',
            'p.PreviousEngineerReport as ShipEqptWPNotes' 
        ])

        const classSociety = _.keyBy(await source.from('tblMaintenanceCompletedClassSurvey').select(), 'MaintenanceCompletedID');


        const puAll = _.groupBy(await source.from('tblPartUsed').select(), 'MaintenanceCompletedID')
        // const puAll = await source.from('tblPartUsed').select()

        const cleanMC = async () => {
            await batchStore(swEqptHistoryInserts, tt.swEqptHistory)
            await batchStore(swEqptSparesHistoryInserts, tt.swEqptSparesHistory)
            await batchStore(swSparesAdjustInserts, tt.swSparesAdjust)
        }

        // restartQueryLog("7.updateShipEqptWPNotes");
        
        for await (let mc of iterate(source, qmc, 'mc.MaintenanceCompletedID', null, 500)) {
            let {
                ComponentCode,
                PMWorksheetID,
                MaintenanceCompletedID,
                BriefJobDescription: Title,
                DateCompleted,
                HoursCompleted: RhReading,
                Status,
                IssuedTo: IssueTo,
                Unscheduled: IsUnscheduledMaintenance,
                Created: DateAssigned,
                DateDue,
                EngineerReport: CompletedDetails,
                ManHours,
                // cssc: RegulationID,
                DeferDate: DateDeferred,
                DeferReason: DeferredReason,
                ReturnedDate: DateRejected,
                SafetyNotes,
                SpecialTools,
                FullJobText,
                ShipEqptWPNotes
            } = mc
            let RegulationID = classSociety[MaintenanceCompletedID]?.ClassSocietySurveyCode || ''
            if (!ComponentCode) continue;

            let swWorkProcKey = null
            let swWorkProcFleetKey = null
            if (PMWorksheetID) {
                // const wp = swWorkProc.find({ PMWorksheetID, db })
                const wp = swWorkProc.by('PMWorksheetID', PMWorksheetID)
                if (!wp) {
                    InvalidReferenceError.log('MC: Invalid WS ID', { PMWorksheetID }, { tblMaintenanceCompleted: MaintenanceCompletedID })
                    continue
                }
                swWorkProcKey = wp.ID
                swWorkProcFleetKey = wp.swWorkProcFleetKey
            }

            let comp;
            
            if(ComponentCode) {
                comp = site.swEquipment.findOne({ ComponentCode, db})
                // comp =  site.swEquipment.by('ComponentCode', ComponentCode)
            }
            
            // if (!comp) {
            //     InvalidReferenceError.log('MC: Invalid ComponentCode', { MCComponentCode: ComponentCode }, { tblMaintenanceCompleted: MaintenanceCompletedID })
            //     continue
            // }

            const WorkDetails = [
                SpecialTools,
                FullJobText,
            ].join(' ').trim()

            const ID = ++swEqptHistoryID

            let mwcRoleTypeKey = null
            if (roleTypes[IssueTo]) mwcRoleTypeKey = roleTypes[IssueTo].ID
            else if (roleConvert[IssueTo]) mwcRoleTypeKey = roleConvert[IssueTo]
            // else mwcRoleTypeKey = 90000008
            else {
                mwcRoleTypeKey = 90000008
                swEqptHistoryUpdate.push({
                    ID,
                    mwcRoleTypeKey
                })
            }

            swEqptHistoryInserts.push({
                ID,
                Title: Title.substring(0, 75),
                DateCompleted,
                RhReading,
                WorkDetails: CompletedDetails,
                mwcRoleTypeKey,
                IsCancelled: Status === 'I',
                IsJobComplete: (Status === 'C') || (Status === 'I'),
                IsUnscheduledMaintenance: PMWorksheetID ? IsUnscheduledMaintenance : 1,
                // swCapitalProjectKey,
                IsEqptOverhaul: Title.toLowerCase().indexOf('overhaul') !== -1,
                DateAssigned,
                DateDue,
                mwcSitesKey,
                CompletedDetails: WorkDetails,
                ManHours,
                DateIssued: DateAssigned,
                RegulationID,
                DateDeferred,
                DeferredReason,
                DateRejected,
                ShipEqptWPNotes,
                WorkInstructions: (SafetyNotes || '').trim(),
                swWorkProcKey,
                swWorkProcFleetKey,
                swEquipmentKey: comp.ID,
                swEqptWorkProcKey: swWorkProcKey,
                EqptName: (comp.EqptName || '').substring(0, 75),
                EqptSerialNo: (comp.SerialNo || '').substring(0, 50),
                tblMaintenanceCompleted__MaintenanceCompletedID: MaintenanceCompletedID.toString(),
                tblPart__ComponentCode: ComponentCode,
            })

            // 7.updateShipEqptWPNotes
            // DATE: March 17 2023 - Donald - Datafac
            // ISSUE: swEqptHistory.ShipEqptWPNotes is missing
            // SOLUTION: Create SQL query then run SQL query in database
            // const buildShipEqptWPNotesSQL = (ID, ShipEqptWPNotes) => {
            //     const q = target(tt.swEqptHistory).where('ID', '=', ID).update({ ShipEqptWPNotes })
            //     logQuery(q.toString().replace(";select @@rowcount", ";"));
            // }
            
            // buildShipEqptWPNotesSQL(ID, ShipEqptWPNotes);
            // END 7.updateShipEqptWPNotes

            // const pu = puAll

            if(!ComponentCode) return
            const pu = _.get(puAll, MaintenanceCompletedID, [])
            for (let {
                PartNumber,
                ComponentCode,
                DateUsed,
                QtyUsed,
                Reason: comments,
                PartUsedID
            } of pu) {
                const sp = _.head(site.swSpares.find({ PartNumber, ComponentCode }))
                if (!sp) {
                    InvalidReferenceError.log('PU: Invalid Part Number', {
                        MaintenanceCompletedID,
                        PartNumber,
                        ComponentCode,
                    })
                    continue
                }

                const spID = ++swSparesAdjustID

                swEqptSparesHistoryInserts.push({
                    ID: spID,
                    swEquipmentKey: comp.ID,
                    swEqptHistoryKey: ID,
                    swSparesKey: sp.ID,
                    DateUsed,
                    QtyUsed,
                    // CurrentUnitPrice,
                    // IsAdjustInventory,
                    mwcSitesKey,
                    tblPartUsed__PartUsedID: PartUsedID,
                    tblPart__PartNumber: PartNumber,
                    tblPart__ComponentCode: ComponentCode,
                })

                swSparesAdjustInserts.push({
                    ID: spID,
                    swSparesKey: sp.ID,
                    swEquipmentKey: comp.ID,
                    swSparesAdjustTypeKey: 2,
                    DateTimeAdjusted: DateUsed,
                    QtyAdjust: QtyUsed,
                    // OldQtyOnHand,
                    // OldUnitPrice,
                    // NewUnitPrice,
                    // OldAvgPrice,
                    // NewAvgPrice,
                    // swEmployeeKey,
                    comments,
                    swEqptSparesHistoryKey: spID,
                    mwcSitesKey,
                    tblPart__PartNumber: PartNumber,
                    tblPart__ComponentCode: ComponentCode,
                })
            }

            if (swEqptHistoryInserts.length > 5000) {
                try {
                    await cleanMC()
                } catch (e) {
                    InvalidReferenceError.log('sss',e)
                }
                
            }
        }

        try {
            await cleanMC()
        } catch (e) {
            InvalidReferenceError.log('sss',e)
        }
        
    }
    await batchStore(_.uniqBy(swCoEqptWPInserts, ({swWorkProcFleetKey, swCompanyEqptKey}) => {
        return `${swWorkProcFleetKey}-${swCompanyEqptKey}`
    }), tt.swCoEqptWP)

    
    // 10.buildswEqptHistoryUpdate
    // DATE: June 12 2023 - Donald - Datafac
    // ISSUE: swSpares.DateExpire is missing
    // SOLUTION: Create SQL query then run SQL query in database
    // restartQueryLog("10.swEqptHistoryUpdate")
    // console.log("// 10.swEqptHistoryUpdate: ", swEqptHistoryUpdate.length)

    const buildswEqptHistoryUpdate = (ID, mwcRoleTypeKey) => {
        const q = target(tt.swEqptHistory).where('ID', '=', ID).update({ mwcRoleTypeKey })
        logQuery(q.toString().replace(";select @@rowcount", ";"))
    }

    

    // _.map(swEqptHistoryUpdate, ({ ID, mwcRoleTypeKey }) => buildswEqptHistoryUpdate(ID, mwcRoleTypeKey))
    // END 10.swEqptHistoryUpdate


    // 11.buildswWorkProcFleetTaskInserts2
    // DATE: June 12 2023 - Donald - Datafac
    // ISSUE: swSpares.DateExpire is missing
    // SOLUTION: Create SQL query then run SQL query in database

    // const decorateswWorkProcFleetTaskInserts2 = _.map(swWorkProcFleetTaskInserts2, (v) => ({
    //     ...v,
    //     ID: ++swWorkProcFleetTaskID, 
    // }))
    
    // restartQueryLog("11.buildswWorkProcFleetTaskInserts2")

    // closingStreams.push(new Promise(r => queryLogStream.end(r)))
    //     queryLogFile = path.resolve(__dirname, '..', 'injection.'+'11.buildswWorkProcFleetTaskInserts2'+'.sql')
    //     queryLogStream = fs.createWriteStream(queryLogFile)
    //     logLines = 0

    //     console.log(queryLogFile)
        
    // const buildswWorkProcFleetTaskInserts2 = (v) => {
    //     const q = target(tt.swWorkProcFleetTask).insert(v)

    //     InvalidReferenceError.log(q.toString().replace(";select @@rowcount", ";"), '')
    //     queryLogger.info(q.toString().replace(";select @@rowcount", ";"))

    //     logQuery(q.toString().replace(";select @@rowcount", ";"))
    // }

    

    // _.map(decorateswWorkProcFleetTaskInserts2, (v) => buildswWorkProcFleetTaskInserts2(v))
    // // END 11.buildswWorkProcFleetTaskInserts2
    // console.log("// 11.decorateswWorkProcFleetTaskInserts2: ", decorateswWorkProcFleetTaskInserts2.length)

    console.log('ALL DONE')
}

main().then(() => process.exit())


// update swWorkProcFleet
// set swWorkProcFleet.WpID = (
//
//     select top(1) CONCAT(sub.JobCode, '-' , RIGHT(CONCAT('000',CAST(sub.rn AS VARCHAR(3))),4)) from (
//
//     select wp.ID, wp.WpID,
//     cw.swCompanyEqptKey, SUBSTRING(wp.WpID, 0,5) as JobCode,
//     row_number () over (
//     partition by cw.swCompanyEqptKey, SUBSTRING(wp.WpID, 0,5) order by wp.ID
// ) rn
//
// from swWorkProcFleet wp
// inner join swCoEqptWP cw
// on cw.swWorkProcFleetKey = wp.ID
// ) sub where sub.ID = swWorkProcFleet.ID
// )