/**
    * Gets a resultset for populating a grid
    * @param SortDetails sortDetails Details about sorting
    * @param PaginationDetail paginationDetail Page and limit for paginating
    * @param string[] relations List of relations to return
    * @param string search text string to search for. Blank for no search.#
    * @param fitlers filters to be used to filter the grid
    *
    * @returns any[] - array of DTO objects
    */
   async getCustomersGrid(sortDetails, paginationDetail, relations, search = '', filters=[]) {
    const model = use(this.entity);

    const query = model.query().select(this.getTableName() + '.*', 'invoices.id as invoiceId');

    // TODO: refactor and move this block of code
    if (relations.length > 0) {
        //inner join relation in order to allow for sorting and ordering on relation
        for (let relation of relations) {
            const relationRepoName = use('App/Repository/' + relation + 'Repository');
            const relationRepo = new relationRepoName();

            query.leftJoin(relationRepo.getTableName(),
                model.table + '.' + this.toCamelCase(relation) + 'Id',
                relationRepo.getTableName() + '.' + relationRepo.key);

            //check sort details
            if (sortDetails.sort !== null) {
                if (sortDetails.sort.indexOf(this.toCamelCase(relation)) !== -1) {
                    sortDetails.sort = sortDetails.sort.replace(this.toCamelCase(relation), relationRepo.getTableName());
                }

                else if (sortDetails.sort.indexOf('.') === -1) {
                    sortDetails.sort = this.getTableName() + '.' + sortDetails.sort;
                }
            }
        }

    }

    if (sortDetails.sort !== null) {
        query.orderBy(sortDetails.sort, sortDetails.direction);
    }

    if (search !== '') {
        const DTOName = use(this.dto);
        const DTO = new DTOName();

        let searchQuery = '';

        this.searchFields.forEach((field) => {
            searchQuery += field + ' LIKE :term OR ';
        });

        searchQuery = searchQuery.substring(0, searchQuery.length - 3);

        query.whereRaw(searchQuery, { term: '%' + search + '%' });
    }

    const startDateFilter = filters['startDate'] === null ? '' : filters['startDate'];
    const endDateFilter = filters['endDate'] === null ? '' : filters['endDate'];
    const poNumberFilter = filters['poNumber'] === null ? '' : filters['poNumber'];
    const customerIdFilter = filters['customerId'] === null ? '' : filters['customerId'];
    const siteNameFilter = filters['siteName'] === null ? '' : filters['siteName'];
    const invoiceNumberFilter = filters['invoiceNumber'] === null ? '' : filters['invoiceNumber'];
    let postedFilter = filters['posted'] === null || filters['posted'] === 'all' ? '' : filters['posted'];
    let regNumberFilter = filters['regNumber'] === null ? '' : filters['regNumber'];

    if(poNumberFilter.trim() !== '') {
        query.whereRaw('invoices.poNumber LIKE ?', ['%' + poNumberFilter + '%']);
    }

    if(customerIdFilter !== '' && customerIdFilter !== -1 && customerIdFilter !== null) {
        query.whereRaw('customers.id = ?', [customerIdFilter]);
    } else {

        // If no customer id don't return any records
        query.whereRaw('customers.id = -1');
    }

    if(siteNameFilter.trim() !== '') {
        query.whereRaw('invoices.siteName LIKE ?', ['%' +  siteNameFilter + '%']);
    }

    if(invoiceNumberFilter.trim() !== '') {
        query.whereRaw('invoices.invoiceNumber LIKE ?', ['%' +  invoiceNumberFilter + '%']);
    }



    if(postedFilter.trim() !== '') {

        let postedValue = '';
        if(postedFilter === 'posted') {
            postedValue = 1;
        } else if(postedFilter === 'unposted') {
            postedFilter = 0;
        }

        query.whereRaw('invoices.posted = ?', [postedValue]);
    }

    if(startDateFilter.trim() !== '' && endDateFilter.trim() !== '') {
        query.whereRaw('DATE(invoices.created_at) >= ?', [startDateFilter]);
        query.whereRaw('DATE(invoices.created_at) <= ?', [endDateFilter]);
    }

    if(startDateFilter.trim() !== '' && endDateFilter.trim() !== '') {
        query.whereRaw('DATE(invoices.created_at) >= ?', [startDateFilter]);
        query.whereRaw('DATE(invoices.created_at) <= ?', [endDateFilter]);
    }


    let results = await query.paginate(paginationDetail.page, paginationDetail.limit);

    let paginationTotal = results.pages.total;

    results = await results.rows;

    let dtoResults = this.fillAll(results);
    let index = 0;

    if (relations.length > 0) {
        for (let dto of dtoResults) {
            dtoResults[index] = await this.withAll(relations, results[index], dto);
            index++;
        }
    }
    return { data: dtoResults, total: paginationTotal };

}