@bipsync/apiclient/sample-reports/files-to-xlsx.js

var APIClient = require( '@bipsync/apiclient' );


APIClient.generateReportTool( {

    'formats' : [ 'xlsx' ],

    fetch : function() {

        APIClient( 'https://devenvone-api.bipsync.dev' )
            .then( function( client ) {

                client.request( 'v1/contenttype/file' )
                    .then( function( contentType ) {
                        return client.requestAll( 'v1/field', { body : { limit : 50 } } )
                            .then( function( fields ) {
                                return fields.filter( function( field ) {
                                    return field.contentType == contentType.id &&
                                           field.options !== undefined &&
                                           field.options.behaviours !== undefined &&
                                           field.options.behaviours.includes( 'primaryFileType' );
                                } );
                            } );
                    } )
                    .then( function( fileTypeFields ) {
                        var fileTypeField = fileTypeFields.shift(),
                            fileTypes = {};

                        Object.keys( fileTypeField.options.values ).forEach( function ( id ) {
                            var fileType = fileTypeField.options.values[ id ];
                            if ( fileType.disabled !== true ) {
                                fileTypes[ id ] = fileType;
                            }
                        } );

                        // fetch all companies (will only include user companies or those with attachments)
                        client.request( 'v1/category', { body : {
                            classification : 'company',
                            select : 'name,ticker,attachments',
                            limit : 500,
                            sort: { 'name':  1 }
                        } } )
                        .then( function( response ) {

                            var attachmentIds = new Set();
                            // filter down to categories with attachments that have a file type
                            return response.results.filter( function( category ) {

                                if ( category.attachments === undefined ) {
                                    return false;
                                }

                                var attachments = category.attachments.filter( function( attachment ) {
                                    return attachment.fields !== undefined &&
                                        attachment.fields[ fileTypeField.id ] !== undefined;
                                } );

                                if ( !attachments.length ) {
                                    return false;
                                }

                                category.attachments = attachments;

                                return category;
                            } );

                        } )
                        .then( function( categories ) {

                            var output = {
                                    fileTypes : fileTypes,
                                    categories : []
                                };

                            categories = categories.sort( function( a, b ) {
                                if (a.name < b.name) {
                                    return -1;
                                } else if (a.name > b.name) {
                                    return 1;
                                }
                                return 0;
                            } );

                            var chain = Promise.resolve();

                            categories.forEach( function( category ) {
                                var attachmentArray = category.attachments;
                                delete( category.attachments );

                                attachmentArray.forEach( function( attachment ) {
                                    var type = attachment.fields[ fileTypeField.id ];

                                    chain = chain.then( function() {
                                        return client.request( 'v2/attachment/' + attachment.id + '/document', { body : {
                                            dateFormat : 'c'
                                        } } );
                                    } )
                                    .catch( function() {} )
                                    .then( function( file ) {
                                        if ( file !== undefined && file.uploadDate !== undefined ) {
                                            category[ type ] = new Date( file.uploadDate );
                                        }
                                    } );

                                } );

                            } );

                            chain.then( function() {
                                output.categories = categories;
                                client.output( output );
                            } );

                        } );

                    } );
                } );

    },

    render : function( helpers, view ) {

        var categories = view.categories,
            fileTypes = view.fileTypes;

        var worksheet = helpers.getExcelWorksheet( 'Worksheet' ),
            headers = [
                { header: 'Company Name', key: 'name', width: 30 },
                { header: 'Ticker', key: 'ticker', width: 10 }
            ];

        Object.keys( fileTypes ).forEach( function ( id ) {
            headers.push( {
                header: fileTypes[ id ].name,
                key: id,
                width: 15
            } );
        } );
        worksheet.columns = headers;

        categories.forEach( function( category ) {
            var row = worksheet.addRow( category ),
                dateNow = new Date(),
                fiveDaysAgo = new Date();

            fiveDaysAgo.setDate( fiveDaysAgo.getDate() - 5 );

            Object.keys( fileTypes ).forEach( function ( id ) {

                var cell = row.getCell( id );
                if ( !cell.value ) {
                    return;
                }

                // Convert date string to a Date
                var dateValue = new Date( cell.value );
                cell.value = dateValue;

                // Conditional Styling
                if ( dateValue.getTime() < fiveDaysAgo.getTime() ) {
                    // Red if it's older than 5 days ago
                    cell.font = {
                        color: { argb: '00FF0000' }
                    };
                } else if ( dateValue.getTime() < dateNow.getTime() ) {
                    // Green if it's within the last 5 days
                    cell.font = {
                        color: { argb: 'CC00FF00' }
                    };
                }

            } );

            row.commit();
        } );

        helpers.commitWorkbook();

    }

} );