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': '',
    '2nd Eng': 11000008,
    'Chief Off': 90000009,
    'Cargo Eng': '',
    'Master': 90000007,


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

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,
} = 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: "127.0.0.1",
            // server: '20.231.30.144',

            // user: 'sa',
            user: 'donald2',

            database: targetDB,
            port: 1433,
            options: {
                encrypt: false,
                trustServerCertificate: true,
            },
            requestTimeout: 60000,
        }
    });
    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

    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: "127.0.0.1",
                // server: 'localhost',
                // server: '20.231.30.144',

                // user: 'sa',
                user: 'donald2',

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

        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 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;

            const mwcRoleTypeKey = getRole(IssueTo)

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

            if (i !== -1) {
                let same = swWorkProcFleetInserts[i]
                if (same.Description.length < FullJobText) swWorkProcFleetInserts[i].Description = FullJobText
                pmMap[PMWorksheetID] = same.ID
                pmSchedule[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,
            }
            pmHashes[hash] = (swWorkProcFleetInserts.push(wp) - 1)

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

            pmMap[PMWorksheetID] = ID
            pmSchedule[PMWorksheetID] = [
                IntervalRH || '-',
                IntervalCal || '-',
                IntervalCalUnit || '-',
                swWorkProcCategoryKey || '-',
            ].join('')
            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')
    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 = []
            for await (let m of iterate(source, 'tblAssembly', 'AssemblyCode')) {
                m = site.createMeter(m)
                if (m) swRHMeterInserts.push(m)
            }
            await batchStore(swRHMeterInserts, tt.swRHMeter)
            delete swRHMeterInserts
        }

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

        {
            const swRHMeterReadingInserts = []
            for await (let m of iterate(source, 'tblAssemblyLog', 'AssemblyCode')) {
                m = site.createMeterReading(m)
                if (m) swRHMeterReadingInserts.push(m)
            }
            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[j.PMWorksheetID]).sort().join(',')

            const e = await site.createEquipment({
                ...c,
                jobs,
            })
            if (e) swEquipmentInserts.push(e)
        }

        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 = []
    // 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 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.%')
        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;
            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,
            } = comp

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

            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) {
                site.swSpares.insert({
                    ID,
                    PartNumber,
                    ComponentCode,
                    swMfgKey,
                    swCompanyArticleKey,
                    swCompanyEqptKey,
                })

                swSparesInserts.push({
                    ID,
                    ...data,
                    mwcSitesKey,
                    QtyOnHand,
                    QtyInUse,
                    MinLevel,
                    AvgPrice,
                    swUnitMeasKey,
                    swCompanyArticleKey,
                    DateExpire,
                })


                // 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,
                })

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

            if (LocationID) {
                const loc = site.swSparesStore.by('LocationID', LocationID)
                if (!loc) {
                } else {
                    swSparesLocationsInserts.push({
                        ID: ++swSparesLocationsID,
                        swSparesKey: ID,
                        swSparesStoreKey: loc.ID,
                        mwcSitesKey,
                        QtyOnHandLocation: QtyOnHand,
                    })
                }
            }
            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
    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')
        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.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;
            }

            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,
                })
                continue;
            }


            swCoEqptWPInserts.push({
                ID: ++swCoEqptWPID,
                swWorkProcFleetKey,
                swCompanyEqptKey: comp.swCompanyEqptKey,
            })
            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,
            })

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

            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,
            })

            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 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] || ''
            
            if (!ComponentCode) continue;

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

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

            const WorkDetails = [
                SafetyNotes,
                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

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

            // 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 = _.get(puAll, MaintenanceCompletedID, [])
            for (let {
                PartNumber,
                ComponentCode,
                DateUsed,
                QtyUsed,
                Reason: comments,
            } 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,
                })

                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,
                })
            }

            if (swEqptHistoryInserts.length > 5000) {
                console.log(`cleanMC`)
                await cleanMC()
            }
        }

        await cleanMC()
    }
    await batchStore(_.uniqBy(swCoEqptWPInserts, ({swWorkProcFleetKey, swCompanyEqptKey}) => {
        return `${swWorkProcFleetKey}-${swCompanyEqptKey}`
    }), tt.swCoEqptWP)

    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
// )