
const db = require('../models');
const FileDBApi = require('./file');
const crypto = require('crypto');
const Utils = require('../utils');



const Sequelize = db.Sequelize;
const Op = Sequelize.Op;

module.exports = class Payroll_itemsDBApi {
    

    
    static async create(data, options) {
        const currentUser = (options && options.currentUser) || { id: null };
        const transaction = (options && options.transaction) || undefined;

        const payroll_items = await db.payroll_items.create(
            {
                id: data.id || undefined,
        
        regularDaysWorked: data.regularDaysWorked
        ||
        null
            ,
            
        absentDays: data.absentDays
        ||
        null
            ,
            
        tardinessMinutes: data.tardinessMinutes
        ||
        null
            ,
            
        overtimeHours: data.overtimeHours
        ||
        null
            ,
            
        nightDiffHours: data.nightDiffHours
        ||
        null
            ,
            
        holidayDaysWorked: data.holidayDaysWorked
        ||
        null
            ,
            
        basicPay: data.basicPay
        ||
        null
            ,
            
        overtimePay: data.overtimePay
        ||
        null
            ,
            
        nightDifferential: data.nightDifferential
        ||
        null
            ,
            
        holidayPay: data.holidayPay
        ||
        null
            ,
            
        allowances: data.allowances
        ||
        null
            ,
            
        otherEarnings: data.otherEarnings
        ||
        null
            ,
            
        grossPay: data.grossPay
        ||
        null
            ,
            
        sssEmployee: data.sssEmployee
        ||
        null
            ,
            
        philhealthEmployee: data.philhealthEmployee
        ||
        null
            ,
            
        pagibigEmployee: data.pagibigEmployee
        ||
        null
            ,
            
        withholdingTax: data.withholdingTax
        ||
        null
            ,
            
        sssEmployer: data.sssEmployer
        ||
        null
            ,
            
        philhealthEmployer: data.philhealthEmployer
        ||
        null
            ,
            
        pagibigEmployer: data.pagibigEmployer
        ||
        null
            ,
            
        sssLoanDeduction: data.sssLoanDeduction
        ||
        null
            ,
            
        pagibigLoanDeduction: data.pagibigLoanDeduction
        ||
        null
            ,
            
        companyLoanDeduction: data.companyLoanDeduction
        ||
        null
            ,
            
        otherDeductions: data.otherDeductions
        ||
        null
            ,
            
        totalDeductions: data.totalDeductions
        ||
        null
            ,
            
        netPay: data.netPay
        ||
        null
            ,
            
        ytdGrossPay: data.ytdGrossPay
        ||
        null
            ,
            
        ytdWithholdingTax: data.ytdWithholdingTax
        ||
        null
            ,
            
        ytdSssEmployee: data.ytdSssEmployee
        ||
        null
            ,
            
        ytdPhilhealthEmployee: data.ytdPhilhealthEmployee
        ||
        null
            ,
            
        ytdPagibigEmployee: data.ytdPagibigEmployee
        ||
        null
            ,
            
        ytdBasicPay: data.ytdBasicPay
        ||
        null
            ,
            
        payslipHash: data.payslipHash
        ||
        null
            ,
            
            importHash: data.importHash || null,
            createdById: currentUser.id,
            updatedById: currentUser.id,
    },
        { transaction },
    );

        
        await payroll_items.setPayrollRun( data.payrollRun || null, {
            transaction,
        });
        
        await payroll_items.setEmployee( data.employee || null, {
            transaction,
        });
        

        

        
        await FileDBApi.replaceRelationFiles(
            {
                belongsTo: db.payroll_items.getTableName(),
                belongsToColumn: 'payslipFile',
                belongsToId: payroll_items.id,
            },
            data.payslipFile,
            options,
        );
        

        return payroll_items;
    }
    

    static async bulkImport(data, options) {
        const currentUser = (options && options.currentUser) || { id: null };
        const transaction = (options && options.transaction) || undefined;

        // Prepare data - wrapping individual data transformations in a map() method
        const payroll_itemsData = data.map((item, index) => ({
                id: item.id || undefined,
                
                regularDaysWorked: item.regularDaysWorked
            ||
            null
            ,
            
                absentDays: item.absentDays
            ||
            null
            ,
            
                tardinessMinutes: item.tardinessMinutes
            ||
            null
            ,
            
                overtimeHours: item.overtimeHours
            ||
            null
            ,
            
                nightDiffHours: item.nightDiffHours
            ||
            null
            ,
            
                holidayDaysWorked: item.holidayDaysWorked
            ||
            null
            ,
            
                basicPay: item.basicPay
            ||
            null
            ,
            
                overtimePay: item.overtimePay
            ||
            null
            ,
            
                nightDifferential: item.nightDifferential
            ||
            null
            ,
            
                holidayPay: item.holidayPay
            ||
            null
            ,
            
                allowances: item.allowances
            ||
            null
            ,
            
                otherEarnings: item.otherEarnings
            ||
            null
            ,
            
                grossPay: item.grossPay
            ||
            null
            ,
            
                sssEmployee: item.sssEmployee
            ||
            null
            ,
            
                philhealthEmployee: item.philhealthEmployee
            ||
            null
            ,
            
                pagibigEmployee: item.pagibigEmployee
            ||
            null
            ,
            
                withholdingTax: item.withholdingTax
            ||
            null
            ,
            
                sssEmployer: item.sssEmployer
            ||
            null
            ,
            
                philhealthEmployer: item.philhealthEmployer
            ||
            null
            ,
            
                pagibigEmployer: item.pagibigEmployer
            ||
            null
            ,
            
                sssLoanDeduction: item.sssLoanDeduction
            ||
            null
            ,
            
                pagibigLoanDeduction: item.pagibigLoanDeduction
            ||
            null
            ,
            
                companyLoanDeduction: item.companyLoanDeduction
            ||
            null
            ,
            
                otherDeductions: item.otherDeductions
            ||
            null
            ,
            
                totalDeductions: item.totalDeductions
            ||
            null
            ,
            
                netPay: item.netPay
            ||
            null
            ,
            
                ytdGrossPay: item.ytdGrossPay
            ||
            null
            ,
            
                ytdWithholdingTax: item.ytdWithholdingTax
            ||
            null
            ,
            
                ytdSssEmployee: item.ytdSssEmployee
            ||
            null
            ,
            
                ytdPhilhealthEmployee: item.ytdPhilhealthEmployee
            ||
            null
            ,
            
                ytdPagibigEmployee: item.ytdPagibigEmployee
            ||
            null
            ,
            
                ytdBasicPay: item.ytdBasicPay
            ||
            null
            ,
            
                payslipHash: item.payslipHash
            ||
            null
            ,
            
            importHash: item.importHash || null,
            createdById: currentUser.id,
            updatedById: currentUser.id,
            createdAt: new Date(Date.now() + index * 1000),
    }));

        // Bulk create items
        const payroll_items = await db.payroll_items.bulkCreate(payroll_itemsData, { transaction });

        // For each item created, replace relation files
        
        for (let i = 0; i < payroll_items.length; i++) {
            await FileDBApi.replaceRelationFiles(
                {
                    belongsTo: db.payroll_items.getTableName(),
                    belongsToColumn: 'payslipFile',
                    belongsToId: payroll_items[i].id,
                },
                data[i].payslipFile,
                options,
            );
        }
        

        return payroll_items;
    }

    static async update(id, data,  options) {
        const currentUser = (options && options.currentUser) || {id: null};
        const transaction = (options && options.transaction) || undefined;
        

        const payroll_items = await db.payroll_items.findByPk(id, {}, {transaction});


        

        const updatePayload = {};
        
        if (data.regularDaysWorked !== undefined) updatePayload.regularDaysWorked = data.regularDaysWorked;
        
        
        if (data.absentDays !== undefined) updatePayload.absentDays = data.absentDays;
        
        
        if (data.tardinessMinutes !== undefined) updatePayload.tardinessMinutes = data.tardinessMinutes;
        
        
        if (data.overtimeHours !== undefined) updatePayload.overtimeHours = data.overtimeHours;
        
        
        if (data.nightDiffHours !== undefined) updatePayload.nightDiffHours = data.nightDiffHours;
        
        
        if (data.holidayDaysWorked !== undefined) updatePayload.holidayDaysWorked = data.holidayDaysWorked;
        
        
        if (data.basicPay !== undefined) updatePayload.basicPay = data.basicPay;
        
        
        if (data.overtimePay !== undefined) updatePayload.overtimePay = data.overtimePay;
        
        
        if (data.nightDifferential !== undefined) updatePayload.nightDifferential = data.nightDifferential;
        
        
        if (data.holidayPay !== undefined) updatePayload.holidayPay = data.holidayPay;
        
        
        if (data.allowances !== undefined) updatePayload.allowances = data.allowances;
        
        
        if (data.otherEarnings !== undefined) updatePayload.otherEarnings = data.otherEarnings;
        
        
        if (data.grossPay !== undefined) updatePayload.grossPay = data.grossPay;
        
        
        if (data.sssEmployee !== undefined) updatePayload.sssEmployee = data.sssEmployee;
        
        
        if (data.philhealthEmployee !== undefined) updatePayload.philhealthEmployee = data.philhealthEmployee;
        
        
        if (data.pagibigEmployee !== undefined) updatePayload.pagibigEmployee = data.pagibigEmployee;
        
        
        if (data.withholdingTax !== undefined) updatePayload.withholdingTax = data.withholdingTax;
        
        
        if (data.sssEmployer !== undefined) updatePayload.sssEmployer = data.sssEmployer;
        
        
        if (data.philhealthEmployer !== undefined) updatePayload.philhealthEmployer = data.philhealthEmployer;
        
        
        if (data.pagibigEmployer !== undefined) updatePayload.pagibigEmployer = data.pagibigEmployer;
        
        
        if (data.sssLoanDeduction !== undefined) updatePayload.sssLoanDeduction = data.sssLoanDeduction;
        
        
        if (data.pagibigLoanDeduction !== undefined) updatePayload.pagibigLoanDeduction = data.pagibigLoanDeduction;
        
        
        if (data.companyLoanDeduction !== undefined) updatePayload.companyLoanDeduction = data.companyLoanDeduction;
        
        
        if (data.otherDeductions !== undefined) updatePayload.otherDeductions = data.otherDeductions;
        
        
        if (data.totalDeductions !== undefined) updatePayload.totalDeductions = data.totalDeductions;
        
        
        if (data.netPay !== undefined) updatePayload.netPay = data.netPay;
        
        
        if (data.ytdGrossPay !== undefined) updatePayload.ytdGrossPay = data.ytdGrossPay;
        
        
        if (data.ytdWithholdingTax !== undefined) updatePayload.ytdWithholdingTax = data.ytdWithholdingTax;
        
        
        if (data.ytdSssEmployee !== undefined) updatePayload.ytdSssEmployee = data.ytdSssEmployee;
        
        
        if (data.ytdPhilhealthEmployee !== undefined) updatePayload.ytdPhilhealthEmployee = data.ytdPhilhealthEmployee;
        
        
        if (data.ytdPagibigEmployee !== undefined) updatePayload.ytdPagibigEmployee = data.ytdPagibigEmployee;
        
        
        if (data.ytdBasicPay !== undefined) updatePayload.ytdBasicPay = data.ytdBasicPay;
        
        
        if (data.payslipHash !== undefined) updatePayload.payslipHash = data.payslipHash;
        
        
        updatePayload.updatedById = currentUser.id;

        await payroll_items.update(updatePayload, {transaction});

        
        
        if (data.payrollRun !== undefined) {
            await payroll_items.setPayrollRun(
              
              data.payrollRun,
              
              { transaction }
            );
        }
        
        if (data.employee !== undefined) {
            await payroll_items.setEmployee(
              
              data.employee,
              
              { transaction }
            );
        }
        

        
        

        
        await FileDBApi.replaceRelationFiles(
            {
                belongsTo: db.payroll_items.getTableName(),
                belongsToColumn: 'payslipFile',
                belongsToId: payroll_items.id,
            },
            data.payslipFile,
            options,
        );
        

        return payroll_items;
    }

    static async deleteByIds(ids, options) {
        const currentUser = (options && options.currentUser) || { id: null };
        const transaction = (options && options.transaction) || undefined;

        const payroll_items = await db.payroll_items.findAll({
            where: {
                id: {
                    [Op.in]: ids,
                },
            },
            transaction,
        });

        await db.sequelize.transaction(async (transaction) => {
            for (const record of payroll_items) {
                await record.update(
                    {deletedBy: currentUser.id},
                    {transaction}
                );
            }
            for (const record of payroll_items) {
                await record.destroy({transaction});
            }
        });


        return payroll_items;
    }

    static async remove(id, options) {
        const currentUser = (options && options.currentUser) || {id: null};
        const transaction = (options && options.transaction) || undefined;

        const payroll_items = await db.payroll_items.findByPk(id, options);

        await payroll_items.update({
            deletedBy: currentUser.id
        }, {
            transaction,
        });

        await payroll_items.destroy({
            transaction
        });

        return payroll_items;
    }

    static async findBy(where, options) {
        const transaction = (options && options.transaction) || undefined;

        const payroll_items = await db.payroll_items.findOne(
            { where },
            { transaction },
        );

        if (!payroll_items) {
            return payroll_items;
        }

        const output = payroll_items.get({plain: true});

        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        output.loan_deductions_payrollItem = await payroll_items.getLoan_deductions_payrollItem({
            transaction
        });
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        output.payrollRun = await payroll_items.getPayrollRun({
            transaction
        });
        
        
        output.employee = await payroll_items.getEmployee({
            transaction
        });
        
        
        output.payslipFile = await payroll_items.getPayslipFile({
            transaction
        });
        
        

        return output;
    }

    static async findAll(
          filter,
           options
        ) {
        const limit = filter.limit || 0;
        let offset = 0;
        let where = {};
        const currentPage = +filter.page;

        

        

        offset = currentPage * limit;

        const orderBy = null;

    const transaction = (options && options.transaction) || undefined;

    let include = [

      {
        model: db.payroll_runs,
        as: 'payrollRun',
        
        where: filter.payrollRun ? {
          [Op.or]: [
            { id: { [Op.in]: filter.payrollRun.split('|').map(term => Utils.uuid(term)) } },
            {
              status: {
                [Op.or]: filter.payrollRun.split('|').map(term => ({ [Op.iLike]: `%${term}%` }))
              }
            },
          ]
        } : {},
        
      },

      {
        model: db.employees,
        as: 'employee',
        
        where: filter.employee ? {
          [Op.or]: [
            { id: { [Op.in]: filter.employee.split('|').map(term => Utils.uuid(term)) } },
            {
              employeeCode: {
                [Op.or]: filter.employee.split('|').map(term => ({ [Op.iLike]: `%${term}%` }))
              }
            },
          ]
        } : {},
        
      },



      {
        model: db.file,
        as: 'payslipFile',
      },

    ];

        if (filter) {
            if (filter.id) {
                where = {
                    ...where,
                    ['id']: Utils.uuid(filter.id),
                };
            }

            
            if (filter.payslipHash) {
                where = {
                    ...where,
                    [Op.and]: Utils.ilike(
                        'payroll_items',
                        'payslipHash',
                        filter.payslipHash,
                    ),
                };
            }
            

            
            

            
            if (filter.regularDaysWorkedRange) {
                const [start, end] = filter.regularDaysWorkedRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    regularDaysWorked: {
                    ...where.regularDaysWorked,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    regularDaysWorked: {
                    ...where.regularDaysWorked,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.absentDaysRange) {
                const [start, end] = filter.absentDaysRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    absentDays: {
                    ...where.absentDays,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    absentDays: {
                    ...where.absentDays,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.tardinessMinutesRange) {
                const [start, end] = filter.tardinessMinutesRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    tardinessMinutes: {
                    ...where.tardinessMinutes,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    tardinessMinutes: {
                    ...where.tardinessMinutes,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.overtimeHoursRange) {
                const [start, end] = filter.overtimeHoursRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    overtimeHours: {
                    ...where.overtimeHours,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    overtimeHours: {
                    ...where.overtimeHours,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.nightDiffHoursRange) {
                const [start, end] = filter.nightDiffHoursRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    nightDiffHours: {
                    ...where.nightDiffHours,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    nightDiffHours: {
                    ...where.nightDiffHours,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.holidayDaysWorkedRange) {
                const [start, end] = filter.holidayDaysWorkedRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    holidayDaysWorked: {
                    ...where.holidayDaysWorked,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    holidayDaysWorked: {
                    ...where.holidayDaysWorked,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.basicPayRange) {
                const [start, end] = filter.basicPayRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    basicPay: {
                    ...where.basicPay,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    basicPay: {
                    ...where.basicPay,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.overtimePayRange) {
                const [start, end] = filter.overtimePayRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    overtimePay: {
                    ...where.overtimePay,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    overtimePay: {
                    ...where.overtimePay,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.nightDifferentialRange) {
                const [start, end] = filter.nightDifferentialRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    nightDifferential: {
                    ...where.nightDifferential,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    nightDifferential: {
                    ...where.nightDifferential,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.holidayPayRange) {
                const [start, end] = filter.holidayPayRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    holidayPay: {
                    ...where.holidayPay,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    holidayPay: {
                    ...where.holidayPay,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.allowancesRange) {
                const [start, end] = filter.allowancesRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    allowances: {
                    ...where.allowances,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    allowances: {
                    ...where.allowances,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.otherEarningsRange) {
                const [start, end] = filter.otherEarningsRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    otherEarnings: {
                    ...where.otherEarnings,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    otherEarnings: {
                    ...where.otherEarnings,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.grossPayRange) {
                const [start, end] = filter.grossPayRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    grossPay: {
                    ...where.grossPay,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    grossPay: {
                    ...where.grossPay,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.sssEmployeeRange) {
                const [start, end] = filter.sssEmployeeRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    sssEmployee: {
                    ...where.sssEmployee,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    sssEmployee: {
                    ...where.sssEmployee,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.philhealthEmployeeRange) {
                const [start, end] = filter.philhealthEmployeeRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    philhealthEmployee: {
                    ...where.philhealthEmployee,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    philhealthEmployee: {
                    ...where.philhealthEmployee,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.pagibigEmployeeRange) {
                const [start, end] = filter.pagibigEmployeeRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    pagibigEmployee: {
                    ...where.pagibigEmployee,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    pagibigEmployee: {
                    ...where.pagibigEmployee,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.withholdingTaxRange) {
                const [start, end] = filter.withholdingTaxRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    withholdingTax: {
                    ...where.withholdingTax,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    withholdingTax: {
                    ...where.withholdingTax,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.sssEmployerRange) {
                const [start, end] = filter.sssEmployerRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    sssEmployer: {
                    ...where.sssEmployer,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    sssEmployer: {
                    ...where.sssEmployer,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.philhealthEmployerRange) {
                const [start, end] = filter.philhealthEmployerRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    philhealthEmployer: {
                    ...where.philhealthEmployer,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    philhealthEmployer: {
                    ...where.philhealthEmployer,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.pagibigEmployerRange) {
                const [start, end] = filter.pagibigEmployerRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    pagibigEmployer: {
                    ...where.pagibigEmployer,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    pagibigEmployer: {
                    ...where.pagibigEmployer,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.sssLoanDeductionRange) {
                const [start, end] = filter.sssLoanDeductionRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    sssLoanDeduction: {
                    ...where.sssLoanDeduction,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    sssLoanDeduction: {
                    ...where.sssLoanDeduction,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.pagibigLoanDeductionRange) {
                const [start, end] = filter.pagibigLoanDeductionRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    pagibigLoanDeduction: {
                    ...where.pagibigLoanDeduction,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    pagibigLoanDeduction: {
                    ...where.pagibigLoanDeduction,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.companyLoanDeductionRange) {
                const [start, end] = filter.companyLoanDeductionRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    companyLoanDeduction: {
                    ...where.companyLoanDeduction,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    companyLoanDeduction: {
                    ...where.companyLoanDeduction,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.otherDeductionsRange) {
                const [start, end] = filter.otherDeductionsRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    otherDeductions: {
                    ...where.otherDeductions,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    otherDeductions: {
                    ...where.otherDeductions,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.totalDeductionsRange) {
                const [start, end] = filter.totalDeductionsRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    totalDeductions: {
                    ...where.totalDeductions,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    totalDeductions: {
                    ...where.totalDeductions,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.netPayRange) {
                const [start, end] = filter.netPayRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    netPay: {
                    ...where.netPay,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    netPay: {
                    ...where.netPay,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.ytdGrossPayRange) {
                const [start, end] = filter.ytdGrossPayRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    ytdGrossPay: {
                    ...where.ytdGrossPay,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    ytdGrossPay: {
                    ...where.ytdGrossPay,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.ytdWithholdingTaxRange) {
                const [start, end] = filter.ytdWithholdingTaxRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    ytdWithholdingTax: {
                    ...where.ytdWithholdingTax,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    ytdWithholdingTax: {
                    ...where.ytdWithholdingTax,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.ytdSssEmployeeRange) {
                const [start, end] = filter.ytdSssEmployeeRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    ytdSssEmployee: {
                    ...where.ytdSssEmployee,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    ytdSssEmployee: {
                    ...where.ytdSssEmployee,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.ytdPhilhealthEmployeeRange) {
                const [start, end] = filter.ytdPhilhealthEmployeeRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    ytdPhilhealthEmployee: {
                    ...where.ytdPhilhealthEmployee,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    ytdPhilhealthEmployee: {
                    ...where.ytdPhilhealthEmployee,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.ytdPagibigEmployeeRange) {
                const [start, end] = filter.ytdPagibigEmployeeRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    ytdPagibigEmployee: {
                    ...where.ytdPagibigEmployee,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    ytdPagibigEmployee: {
                    ...where.ytdPagibigEmployee,
                            [Op.lte]: end,
                    },
                };
                }
            }
            
            if (filter.ytdBasicPayRange) {
                const [start, end] = filter.ytdBasicPayRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                    ytdBasicPay: {
                    ...where.ytdBasicPay,
                            [Op.gte]: start,
                    },
                };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                    ytdBasicPay: {
                    ...where.ytdBasicPay,
                            [Op.lte]: end,
                    },
                };
                }
            }
            

            if (filter.active !== undefined) {
                where = {
                    ...where,
                    active: filter.active === true || filter.active === 'true'
                };
            }

            


      

      



            if (filter.createdAtRange) {
                const [start, end] = filter.createdAtRange;

                if (start !== undefined && start !== null && start !== '') {
                    where = {
                        ...where,
                        ['createdAt']: {
                            ...where.createdAt,
                            [Op.gte]: start,
                        },
                    };
                }

                if (end !== undefined && end !== null && end !== '') {
                    where = {
                        ...where,
                        ['createdAt']: {
                            ...where.createdAt,
                            [Op.lte]: end,
                        },
                    };
                }
            }
        }
        

        

        const queryOptions = {
            where,
            include,
            distinct: true,
            order: filter.field && filter.sort
                ? [[filter.field, filter.sort]]
                : [['createdAt', 'desc']],
            transaction: options?.transaction,
            logging: console.log
        };

        if (!options?.countOnly) {
            queryOptions.limit = limit ? Number(limit) : undefined;
            queryOptions.offset = offset ? Number(offset) : undefined;
        }

        try {
            const { rows, count } = await db.payroll_items.findAndCountAll(queryOptions);

            return {
                rows: options?.countOnly ? [] : rows,
                count: count
            };
        } catch (error) {
            console.error('Error executing query:', error);
            throw error;
        }
    }

    static async findAllAutocomplete(query, limit, offset, ) {
        let where = {};
        
        

        if (query) {
            where = {
                [Op.or]: [
                    { ['id']: Utils.uuid(query) },
                    Utils.ilike(
                        'payroll_items',
                        'payslipHash',
                        query,
                    ),
                ],
            };
        }

        const records = await db.payroll_items.findAll({
            attributes: [ 'id', 'payslipHash' ],
            where,
            limit: limit ? Number(limit) : undefined,
            offset: offset ? Number(offset) : undefined,
            orderBy: [['payslipHash', 'ASC']],
        });

        return records.map((record) => ({
            id: record.id,
            label: record.payslipHash,
        }));
    }

    
};

