Creation of a Journal Record based on Dynamic Filters in search in getting the data. We are getting the data from weekly timesheet record.
/*** @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define([
"N/search",
"N/config",
"N/format",
"N/record"
], (search, config, format, record) => {
/**
* Defines the Global variables for debit and credit totals
*/
let debitTotal = 0;
let creditTotal = 0;
/**
* Defines the months Object
*/
const monthsObject = {
1: 'Jan',
2: 'Feb',
3: 'Mar',
4: 'Apr',
5: 'May',
6: 'Jun',
7: 'Jul',
8: 'Aug',
9: 'Sep',
10: 'Oct',
11: 'Nov',
12: 'Dec'
}
/**
* Defines the subsidiary object
*/
let subsidiaryObj = {
ARO: '46',
LONDON: '2',
AWRO: '13',
ESEAOR: '45',
SROP: '41',
SARO: '33',
EN: '60'
}
/**
* Defines the subsidiary payroll elements for different subsidiaries
*/
let subsidiaryPayrollFields = {
"2": {
'LONDON BASIC PAY': 'custrecord_jj_basic_pay',
'PENSION': 'custrecord_jj_emp_pension',
'OTHER BENEFITS': 'custrecord_jj_other_benefits',
'NATIONAL INSURANCE': 'custrecord_jj_national_insurance',
'TERMINATION': 'custrecord_jj_termination_ded_payroll',
'NIC DEDUCTION': 'custrecord_jj_nic_ded_payroll',
'PAYE DEDUCTION': 'custrecord_jj_paye_ded_payroll',
'PENSIONRAS DEDUCTION': 'custrecord_jj_pension_ded_payroll',
'PGLOAN DEDUCTION': 'custrecord_jj_pgloan_ded_payroll',
'PRI-MED': 'custrecord_jj_pri_med',
'STLOAN DEDUCTION': 'custrecord_jj_stloan_ded',
'UNION DEDUCTION': 'custrecord_jj_union_ded_payroll',
'TAKE HOME AMOUNT': 'custrecord_jj_take_hm_ded_payroll',
'NET DEDUCTION': 'custrecord_jj_net_ded',
'LONDON NATIONAL INSURANCE CREDIT':'custrecord_jj_lo_nat_insurance',
'LONDON PENSION CREDIT':'custrecord_jj_lo_pens_credit'
},
}
/**
* Defines the payroll elements for the debit and credit values for journal lines for different subsidiaries
*/
let payrollElements = {
"2": {
"debit": ['LONDON BASIC PAY', 'PENSION', 'NATIONAL INSURANCE', 'TERMINATION', 'OTHER BENEFITS'],
"credit": ['LONDON PENSION CREDIT','LONDON NATIONAL INSURANCE CREDIT', 'TAKE HOME AMOUNT', 'NIC DEDUCTION', 'PAYE DEDUCTION', 'PENSIONRAS DEDUCTION', 'NET DEDUCTION', 'STLOAN DEDUCTION', 'PGLOAN DEDUCTION', 'UNION DEDUCTION']
},
}
/**
* This function is used to get the current date of the journal creation from the custom record for different subisidiaries
* @param {*} dateString
* @returns
*/
const getCurrentMonthData = (dateString, subsidiary) => {
try {
log.debug('subsidiary in getCurrentMonthData',subsidiary)
let filters = [
["custrecord_jj_je_creation_date", "on", dateString],
"AND",
["isinactive", "is", "F"]
];
if (!!subsidiary) {
filters.push("AND");
filters.push(["custrecord_jj_subsidiary", "anyof", subsidiary])
}
let monthlyTimeJeCreationSearchObj = search.create({
type: "customrecord_jj_monthly_time_je_creation",
filters: filters,
columns:
[
search.createColumn({ name: "custrecord_jj_subsidiary", label: "Subsidiary" }),
search.createColumn({ name: "custrecord_jj_je_creation_date", label: "JE creation Date" }),
search.createColumn({ name: "custrecord_jj_month_je", label: "Month (JE)" }),
search.createColumn({ name: "custrecord_jj_year_je", label: "Year (JE)" })
]
});
let currentMonthDateArr = [];
monthlyTimeJeCreationSearchObj.run().each(function (result) {
let obj = {};
obj.subsidiary = result.getValue({ name: "custrecord_jj_subsidiary", label: "Subsidiary" });
obj.toDate = result.getValue({ name: "custrecord_jj_je_creation_date", label: "JE creation Date" });
obj.month = result.getValue({ name: "custrecord_jj_month_je", label: "Month (JE)" });
obj.year = result.getValue({ name: "custrecord_jj_year_je", label: "Year (JE)" });
currentMonthDateArr.push(obj);
return true;
});
return currentMonthDateArr;
} catch (error) {
log.error("--Error @getCurrentMonthData---", error);
return [];
}
}
/**
* This function is search the previous month data from the custom record to get the start date of journal creation
* @param {*} currMonthDataArr
* @param {*} dateObject
* @returns
*/
const getPreviousMonthData = (currMonthDataArr, dateObject, subsidiary) => {
try {
let prevMonthDataArr = currMonthDataArr;
let subsidiaryList = null;
if (!!subsidiary) {
subsidiaryList = subsidiary
} else {
subsidiaryList = currMonthDataArr.map(value => value.subsidiary);
}
let currentMonth = dateObject.getMonth() + 1;
let previousMonth = "";
let year = "";
if (currentMonth == 1) {
previousMonth = 12;
year = dateObject.getFullYear() - 1;
} else {
previousMonth = currentMonth - 1;
year = dateObject.getFullYear();
}
let previousMonthlyTimeJeCreationSrch = search.create({
type: "customrecord_jj_monthly_time_je_creation",
filters:
[
["formulatext: TO_CHAR({custrecord_jj_je_creation_date},'MM')", "is", previousMonth.toString()],
"AND",
["formulatext: TO_CHAR({custrecord_jj_je_creation_date},'YYYY')", "is", year.toString()],
"AND",
["custrecord_jj_subsidiary", "anyof", subsidiaryList]
],
columns:
[
search.createColumn({ name: "custrecord_jj_je_creation_date", label: "JE creation Date" }),
search.createColumn({ name: "custrecord_jj_subsidiary", label: "Subsidiary" })
]
});
previousMonthlyTimeJeCreationSrch.run().each(function (result) {
let subsidiary = result.getValue({ name: "custrecord_jj_subsidiary", label: "Subsidiary" }) || '';
for (let i = 0; i < prevMonthDataArr.length; i++) {
if (subsidiary == prevMonthDataArr[i].subsidiary) {
prevMonthDataArr[i].fromDate = result.getValue({ name: "custrecord_jj_je_creation_date", label: "JE creation Date" });
break;
}
}
return true;
});
return prevMonthDataArr;
} catch (error) {
log.error("---Error @getPreviousMonthData", error);
}
}
/**
* Function to get the data from the Time Bill records
* @param {*} subsidiaryDateArr
* @returns
*/
const timeBillSearchData = (subsidiaryDateArr) => {
try {
let customFilters = [
[
["duration", "greaterthan", "0"],
"AND",
["approvalstatus", "anyof", "3"],
"AND",
["custcol_jj_employee_type_time", "isnot", "Consultant"],
"AND",
["custcol_jj_time_type", "noneof", "9"],
// "AND", // Added for testing
// ["employee","anyof","15740"],// Added for testing
],
"AND"
];
let filterResult = subsidiaryDateArr.map(entry =>
[
["custcol_jj_regnl_off_ippf_2091", "anyof", entry.subsidiary],// COMMENTED FOR TESTING
// ["custcol_jj_regnl_off_ippf_2091", "anyof","45"],
"AND",
// ["date", "within", entry.fromDate, entry.toDate] // Commented for testing purpose
//["custcol_jj_appr_date_time", "within", entry.fromDate, entry.toDate] // This is the needed filter, currently no data is in this filter. hnece data is used
["date", "within", "1/1/2024", "31/1/2024"], // Added for January data
]);
for (let i = filterResult.length - 1; i > 0; i--) {
filterResult.splice(i, 0, "OR");
}
customFilters.push(filterResult);
return search.create({
type: "timebill",
filters: [
customFilters
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({
name: "date",
sort: search.Sort.ASC,
label: "Date"
}),
search.createColumn({ name: "employee", label: "Employee" }),
search.createColumn({ name: "customer", label: "Donor" }),
search.createColumn({ name: "subsidiarynohierarchy", label: "Regional Office (no hierarchy)" }),
search.createColumn({ name: "durationdecimal", label: "Time per month" }),
search.createColumn({ name: "hours", label: "Duration" }),
search.createColumn({ name: "approvalstatus", label: "Approval Status" }),
search.createColumn({ name: "timesheet", label: "Timesheet" }),
search.createColumn({ name: "custcol_jj_regnl_off_ippf_2091", label: "Regional Office" }),
search.createColumn({ name: "custcol_jj_appr_date_time", label: "Timesheet Approved Date" }),
search.createColumn({ name: "department", label: "Activity Code" }),
search.createColumn({ name: "class", label: "Division/Unit" }),
]
}) || [];
} catch (error) {
log.error("---Error @timeBillSearchData", error);
return [];
}
}
/**
* Function to get the filters for getting the time bill data
* @param {*} currentDate
* @param {*} subsidiary
* @returns
*/
function filterForTimeBillSearch(currentDate, subsidiary) {
try {
let configRecObj = config.load({
type: config.Type.COMPANY_INFORMATION
});
let preferredTimezone = configRecObj.getValue("timezone");
let dateObject = format.parse({
value: currentDate,
type: format.Type.DATE,
timezone: preferredTimezone
});
let dateString = format.format({
value: currentDate,
type: format.Type.DATE,
timezone: preferredTimezone
});
let currMonthDateArr = getCurrentMonthData(dateString, subsidiary);
if (currMonthDateArr.length <= 0) {
return [];
}
let monthlyTimeDataArr = getPreviousMonthData(currMonthDateArr, dateObject, subsidiary);
return monthlyTimeDataArr;
}
catch (err) {
log.error('error@filterForTimeBillSearch', err);
return [];
}
}
/**
* Function to get the Project total time and other data from the timebill records based of different activity code
* @param {*} employees
* @param {*} subsidiary
* @param {*} projects
* @param {*} subsidiaryDateArr
* @returns
*/
function getProjectTime(employees, subsidiary, projects, subsidiaryDateArr) {
try {
log.debug("subsidiaryDateArr", subsidiaryDateArr);
let timebillSearchObj = search.create({
type: "timebill",
filters: [
["duration", "greaterthan", "0"],
"AND",
["approvalstatus", "anyof", "3"],
"AND",
["custcol_jj_employee_type_time", "isnot", "Consultant"],
"AND",
["custcol_jj_time_type", "noneof", "9"],
//"AND",
// ["custcol_jj_regnl_off_ippf_2091", "anyof", subsidiary], TODO FOR TESTING
"AND",
["employee", "anyof", employees],
// ["employee", "anyof", "16071","5144","5146"], // TODO FOR TESTING!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
"AND",
["customer", "anyof", projects],
"AND",
// ["date", "within", subsidiaryDateArr[0].fromDate, subsidiaryDateArr[0].toDate] // TOTO COMMENTED ON 17/04/2024
//["custcol_jj_appr_date_time", "within", entry.fromDate, entry.toDate] // This is the needed filter, currently no data is in this filter. hnece data is used
["date", "within", "1/1/2024", "31/1/2024"],
],
columns:
[
search.createColumn({
name: "employee",
summary: "GROUP",
label: "Employee"
}),
search.createColumn({
name: "department",
summary: "GROUP",
label: "Activity Code"
}),
search.createColumn({
name: "customer",
summary: "GROUP",
label: "Donor"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{durationdecimal}",
label: "Total Time per month"
}),
search.createColumn({
name: "class",
summary: "GROUP",
label: "Division/Unit"
})
]
});
var searchResultCount = timebillSearchObj.runPaged().count;
log.debug("timebillSearchObj result count",searchResultCount);
let timeBillArray = [];
timebillSearchObj.run().each(function (result) {
let obj = {}
obj.employee = result.getValue({
name: "employee",
summary: "GROUP",
label: "Employee"
})
obj.project = result.getValue({
name: "customer",
summary: "GROUP",
label: "Donor"
})
obj.weekActivityCode = result.getValue({
name: "department",
summary: "GROUP",
label: "Activity Code"
})
obj.weekDivision = result.getValue({
name: "class",
summary: "GROUP",
label: "Division/Unit"
})
obj.time = result.getValue({
name: "formulanumeric",
summary: "SUM",
formula: "{durationdecimal}",
label: "Total Time per month"
})
timeBillArray.push(obj);
return true;
});
return timeBillArray;
} catch (error) {
log.error("---Error@getProjectTime---", error)
return [];
}
}
/**
* Function to get the payroll elements like cost and other values from the custom record Payroll and Project allocation records
* @param {*} employeeArr
* @param {*} projectArr
* @param {*} monthfilter
* @returns
*/
function getPayrollData(employeeArr, projectArr, monthfilter) {
try {
let customColumns = [
search.createColumn({ name: "custrecord_jj_month", label: "Month" }),
search.createColumn({ name: "custrecord_jj_year", label: "Year" }),
search.createColumn({ name: "custrecord_jj_payroll_emp", label: "Employee" }),
search.createColumn({ name: "custrecord_jj_payroll_currency", label: "Currency" }),
search.createColumn({
name: "custrecord_jj_project",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project"
}),
search.createColumn({
name: "custrecord_jj_proj_div",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project Division"
}),
search.createColumn({
name: "custentity_jj_payroll_code",
join: "CUSTRECORD_JJ_PAYROLL_EMP",
label: "Payroll Code"
})
];
let subsidiary = monthfilter[0].subsidiary;
let subsidiaryFieldObj = subsidiaryPayrollFields[subsidiary];
for (let key in subsidiaryFieldObj) {
customColumns.push(search.createColumn({ "name": subsidiaryFieldObj[key], "label": key }));
}
let payrollDetailSearch = search.create({
type: "customrecord_jj_payroll_details",
filters:
[
["custrecord_jj_payroll_emp", "anyof", employeeArr],
"AND",
["custrecord_jj_month", "anyof", monthfilter[0].month.toString()],
"AND",
["custrecord_jj_year", "is", monthfilter[0].year.toString()],
"AND",
["isinactive", "is", "F"],
"AND",
["custrecord_jj_project_period.custrecord_jj_project", "anyof", projectArr], // needed code
"AND",
["custrecord_jj_project_period.isinactive", "is", "F"],
"AND",
["custrecord_jj_project_period.custrecord_jj_actual_alloc","isnotempty",""],
"AND",
["custrecord_jj_project_period.custrecord_jj_project_subsidiary","anyof",subsidiary],
],
columns: customColumns
});
let payrollDetailsArr = [];
payrollDetailSearch.run().each(result => {
let obj = {};
for (let i = 0; i < customColumns.length; i++) {
obj[customColumns[i]["label"]] = result.getValue(customColumns[i])
}
payrollDetailsArr.push(obj);
return true;
})
return payrollDetailsArr;
} catch (error) {
log.error('error in getPayrollData', error);
return [];
}
}
/**
* Function to Combine the results of both time bill details and payroll allocation details
* @param {*} employeeTimeArr
* @param {*} payrollDetails
* @returns
*/
function combineResult(employeeTimeArr, payrollDetails) {
try {
let result = {};
employeeTimeArr.forEach(item1 => {
const employeeId = item1.employee;
const project = item1.project;
// Find corresponding object in array2
const matchingObject = payrollDetails.find(item2 => item2.Employee === employeeId && item2.Project === project);
// If matching object found, merge properties
if (matchingObject) {
const mergedObject = { ...item1, ...matchingObject };
if (!result[employeeId]) {
result[employeeId] = [];
}
result[employeeId].push(mergedObject);
}
});
payrollDetails.forEach(item2 => {
const employeeId = item2.Employee;
const project = item2.Project;
// Check if the employeeId already exists in the result, if not, initialize an empty array
if (!result[employeeId]) {
result[employeeId] = [];
}
// Find corresponding object in array1
const matchingObject = employeeTimeArr.find(item1 => item1.employee === employeeId && item1.project === project);
// If matching object not found, merge properties
if (!matchingObject) {
result[employeeId].push(item2);
}
});
return result;
} catch (error) {
log.error("Error in combineResult", error)
}
}
/**
* Function to calculate the total time for the employees worked on the time period of journal
* @param {*} data
* @returns
*/
function getEmployeeProjectData(data) {
try {
let journalData = data;
for (let key in data) {
for (let i = 0; i < data[key].length; i++) {
journalData[key][i].totalTime = data[key].reduce((a, result) => a + Number(result.time), 0)
}
}
return journalData;
}
catch (err) {
log.error("error@getEmployeeProjectData", err)
return {}
}
}
/**
* Function to get the Exchange Rate details
*/
function getExchangeRate(sourceCurrency, baseCurrency) {
try {
let exchangeRateValue;
let currencyrateSearchObj = search.create({
type: "currencyrate",
filters:
[
["basecurrency", "anyof", sourceCurrency],
"AND",
["transactioncurrency", "anyof", baseCurrency]
],
columns:
[
search.createColumn({
name: "exchangerate",
summary: "MAX",
label: "Exchange Rate"
}).setWhenOrderedBy({ name: 'effectivedate', join: 'currencyrate' }),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "TO_CHAR({transactioncurrency})",
label: "Formula (Text)"
})
]
});
let exchangerateObject = {};
currencyrateSearchObj.run().each(function (result) {
let transactionCurrency = result.getValue({
name: "formulatext",
summary: "GROUP",
formula: "TO_CHAR({transactioncurrency})",
label: "Formula (Text)"
});
exchangeRateValue = result.getValue({
name: "exchangerate",
summary: "MAX",
label: "Exchange Rate"
});
exchangerateObject[transactionCurrency] = exchangeRateValue;
return true;
});
return exchangerateObject;
}
catch (e) {
log.error("Error@getExchangeRate", e);
return {}
}
}
/**
* Function to get the currency data search
* @param {*} employeeArray
* @param {*} journalData
* @param {*} baseCurrency
* @returns
*/
function getCurrencyDataSearch(journalData, baseCurrency) {
try {
let currencyArray = [];
for (let key in journalData) {
// Iterate over each array inside the object values
journalData[key].forEach(item => {
// Push the Currency value to the array
currencyArray.push(item.Currency);
});
}
if (currencyArray.length > 0) {
let exchangeRateValue = getExchangeRate(baseCurrency, currencyArray);
if (Object.keys(exchangeRateValue).length > 0) {
return exchangeRateValue
}
else {
return {}
}
} else {
return {}
}
}
catch (err) {
log.error("error@getCurrencyDataSearch", err)
return {}
}
}
/**
* Function for the cost calculation based on debit and credit values
* @param {*} data
* @param {*} exchangeRateData
* @param {*} subsidiary
* @returns
*/
function costCalculation(data, exchangeRateData, subsidiary) {
try {
let finalJournalData = data;
for (let key in data) {
let totalRoundOff = 0;
for (let i = 0; i < data[key].length; i++) {
let value = data[key][i];
let exchangeRate = Number(exchangeRateData[value.Currency]);
let timePercent = Number(value.time) / Number(value.totalTime);
let roundOffTime = timePercent.toFixed(2);
if (Number(totalRoundOff) <= 1) {
if (i == data[key].length - 1) {
roundOffTime = 1 - totalRoundOff
roundOffTime = Number(roundOffTime).toFixed(2)
}
totalRoundOff = totalRoundOff + Number(roundOffTime);
}
let payElements = payrollElements[subsidiary];
payElements.debit.forEach(element => {
let payElement = value[element];
finalJournalData[key][i]['debit_' + element] = Number(roundOffTime) * Number(payElement) * exchangeRate;
})
payElements.credit.forEach(element => {
let payElement = value[element];
finalJournalData[key][i]['credit_' + element] = Number(roundOffTime) * Number(payElement) * exchangeRate;
})
}
}
return finalJournalData;
} catch (error) {
log.error("Error in costCalculation", error);
return {}
}
}
/**
* Function to get the posting period
* @param {*} year
* @param {*} month
* @returns
*/
function getPostingPeriod(year, month) {
try {
let accountingperiodSearchObj = search.create({
type: "accountingperiod",
filters:
[
["periodname", "startswith", month],
"AND",
["periodname", "contains", year.toString()]
],
columns:
[
search.createColumn({
name: "periodname",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({ name: "isyear", label: "Year" }),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
let searchResultCount = accountingperiodSearchObj.runPaged().count;
let postingPeriod
if (searchResultCount > 0) {
accountingperiodSearchObj.run().each(function (result) {
postingPeriod = result.getValue({
name: "internalid", label: "Internal ID"
})
return true;
});
return postingPeriod
}
else {
return null
}
}
catch (err) {
log.error("error@getPostingPeriod", err)
return null;
}
}
/**
* Function to add Journal debit Lines
* @param {*} costObject
* @param {*} journalEntry
* @param {*} i
*/
function addJournalLines(key, element, journalEntry, debitObjects, subsidiary) {
try {
// log.debug('element', element)
let payElement = key.split('debit_')[1];
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'account',
value: element['account_' + payElement],
});
let amount = element['debit_' + payElement].toFixed(2);
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'debit',
value: Number(amount),
});
// log.audit('debit amounts..',
// {
// amount :amount,
// PayrollCode:element['Payroll Code']
// })
debitTotal += Number(amount)
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'class',
value: element['Project Division'],
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'department',
value: element['weekActivityCode'],
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'custcol3',
value: payElement + '-' + element['Payroll Code'],
// value: payElement
});
journalEntry.commitLine({
sublistId: 'line'
});
}
catch (err) {
log.error("error@addJournalLines", err)
let errorMessage = err.name + " " + ":" + " " + err.message
createErrorFile(errorMessage, 'Data at Addding Debit Lines', subsidiary, debitObjects)
}
}
/**
* Function to get the Journal Accounts from the custom record
* @returns {number}
*/
function searchAccount() {
try {
let customrecord_jj_master_gl_data_ippf2097SearchObj = search.create({
type: "customrecord_jj_master_gl_data_ippf2097",
filters:
[
],
columns:
[
search.createColumn({ name: "custrecord_jj_field_name", label: "Field Name" }),
search.createColumn({ name: "custrecord_jj_account_ippf_2097", label: "Account" })
]
});
let searchResultCount = customrecord_jj_master_gl_data_ippf2097SearchObj.runPaged().count;
let mainAccountObj = {};
if (searchResultCount > 0) {
customrecord_jj_master_gl_data_ippf2097SearchObj.run().each(function (result) {
let fieldId = result.getValue({
name: "custrecord_jj_field_name", label: "Field Name"
})
mainAccountObj[fieldId] = result.getValue({
name: "custrecord_jj_account_ippf_2097", label: "Account"
})
return true;
});
return mainAccountObj;
}
else {
return {}
}
}
catch (err) {
log.error("Error in searchAccount", err)
return {}
}
}
/**
* Function to add the Credit lines
* @param {*} key
* @param {*} value
* @param {*} journalEntry
* @param {*} accountsObj
* @param {*} loop
* @param {*} countofCredit
*/
function addCreditJournalLines(key, value, journalEntry, accountsObj, loop, countofCredit, creditObjects, subsidiary) {
try {
log.debug("Credit details", {
key: key,
value: value,
accountsObj: accountsObj[key]
})
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'account',
value: accountsObj[key],
});
let amount = Number(value).toFixed(2)
creditTotal += Number(amount)
if (loop == countofCredit) {
log.audit('countofCredit',countofCredit)
if (creditTotal > debitTotal) {
let balance = creditTotal - debitTotal;
log.audit('value before if',value)
amount = value - balance
log.audit('amount when creditTotal > debitTotal',amount)
} else {
let balance = debitTotal - creditTotal;
log.audit('value before else',value)
amount = value + balance;
log.audit('mount when creditTotal < debitTotal',amount)
}
}
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'credit',
value: Number(amount),
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'class',
value: '1182'
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'department',
value: '10607',
});
journalEntry.setCurrentSublistValue({
sublistId: 'line',
fieldId: 'custcol3',
// value: key + '-' + accountsObj['Payroll Code'],
value: key
});
journalEntry.commitLine({
sublistId: 'line'
});
} catch (error) {
log.error('Error in addCreditJournalLines', error)
let errorMessage = error.name + " " + ":" + " " + error.message
createErrorFile(errorMessage, 'Data at Adding Credit Lines', subsidiary, creditObjects)
}
}
/**
* Function to get the created by value based on subsidiary
* @param {*} subsidiary
* @returns
*/
function findCreatedByEmployee(subsidiary) {
try {
let createdBy = '';
let customrecord_jj_journl_approverSearchObj = search.create({
type: "customrecord_jj_journl_approver",
filters:
[
["custrecord_jj_approv_subsidiary", "anyof", subsidiary],
"AND",
["isinactive", "is", "F"]
],
columns:
[
search.createColumn({ name: "custrecord_jj_approv_subsidiary", label: "Subsidiary" }),
search.createColumn({ name: "custrecord_jj_approv_person", label: "Employee" })
]
});
let searchResultCount = customrecord_jj_journl_approverSearchObj.runPaged().count;
if (searchResultCount > 0) {
customrecord_jj_journl_approverSearchObj.run().each(function (result) {
createdBy = result.getValue({
name: "custrecord_jj_approv_person", label: "Employee"
})
});
}
else {
createdBy = '10233'
}
return createdBy;
}
catch (err) {
log.error('error@findCreatedByEmployee', err)
}
}
/**
* Function for getting the Debit Objects
* @param {} costData
* @param {*} accountsObj
* @returns
*/
function gettingDebitObjects(costData, accountsObj) {
try {
let debitObjects = [];
for (const key in costData) {
if (costData.hasOwnProperty(key)) {
// log.debug('key ',key)
costData[key].forEach(entry => {
// log.debug('entry',entry)
let debitEntries = {};
for (const field in entry) {
if (field.startsWith("debit")) {
debitEntries[field] = entry[field];
// Check if the debit field corresponds to an account in accountsObj
let accountField = field.substring(6).toUpperCase();
if (accountsObj[accountField]) {
debitEntries["account_" + accountField] = accountsObj[accountField];
}
}
}
["Project Division", "weekActivityCode", "Payroll Code"].forEach(prop => {
if (entry.hasOwnProperty(prop)) {
debitEntries[prop] = entry[prop];
}
});
// log.audit('debitEntries',debitEntries)
debitObjects.push(debitEntries);
});
}
}
return debitObjects;
} catch (err) {
log.error('error@gettingDebitObjects', err);
return [];
}
}
/**
* Function to get the Total Credit Value
* @param {*} creditObjects
* @returns
*/
function totalCreditValue(creditObjects) {
try {
let totalCreditsObject = {};
// Iterate through the array of objects
for (const entry of creditObjects) {
// Iterate through the keys of each entry
for (const key in entry) {
// Check if the key contains "credit_"
if (key.startsWith("credit_")) {
// Extract the credit field name
let creditField = key.substring(7);
// If the field doesn't exist in the totalCreditsObject, initialize it to 0
if (!totalCreditsObject.hasOwnProperty(creditField)) {
totalCreditsObject[creditField] = 0;
}
// Add the value to the corresponding credit field
totalCreditsObject[creditField] += parseFloat(entry[key]);
}
}
}
return totalCreditsObject
}
catch (err) {
log.error('error@totalCreditValue', err)
}
}
/**
* Function to get the Credit Objects
* @param {*} costData
* @param {*} accountsObj
* @returns
*/
function gettingCreditObjects(costData, accountsObj) {
try {
let creditObjects = [];
for (const key in costData) {
if (costData.hasOwnProperty(key)) {
costData[key].forEach(entry => {
let creditEntries = {};
for (const field in entry) {
if (field.startsWith("credit")) {
creditEntries[field] = entry[field];
let accountField = field.substring(7).toUpperCase(); // Remove "debit-" prefix and convert to uppercase
if (accountsObj[accountField]) {
creditEntries["account_" + accountField] = accountsObj[accountField];
}
}
}
["Project Division", "weekActivityCode", "Payroll Code"].forEach(prop => {
if (entry.hasOwnProperty(prop)) {
creditEntries[prop] = entry[prop];
}
});
creditObjects.push(creditEntries);
});
}
}
return creditObjects;
} catch (err) {
log.error('error@gettingCreditObject', err);
return [];
}
}
/**
* Function to create the Journal records
* @param {*} subsidiary
* @param {*} costData
* @param {*} monthfilter
* @param {*} transactionDate
* @param {*} currency
*/
function createAutomatedJournal(subsidiary, costData, monthfilter, transactionDate, currency) {
try {
let journalId
let monthText = monthsObject[monthfilter[0].month];
let postingPeriod = getPostingPeriod(monthfilter[0].year, monthText);
let accountsObj = searchAccount();
log.debug('accountsObj', accountsObj)
let createdBy = findCreatedByEmployee(subsidiary)
/**
* Creating the Journal record
*/
let journalEntry = record.create({
type: record.Type.JOURNAL_ENTRY,
isDynamic: true,
});
journalEntry.setValue({
fieldId: 'subsidiary',
value: subsidiary
});
journalEntry.setValue({
fieldId: 'currency',
value: currency
});
journalEntry.setValue({
fieldId: 'memo',
value: 'Automated Journal'
});
journalEntry.setValue({
fieldId: 'custbody17',
value: createdBy
});
journalEntry.setValue({
fieldId: 'trandate',
value: transactionDate
});
journalEntry.setValue({
fieldId: 'postingperiod',
value: postingPeriod
});
let debitObjects = gettingDebitObjects(costData, accountsObj)
// log.debug("debitObjects", debitObjects);
let creditObjects = gettingCreditObjects(costData, accountsObj)
// log.debug("creditObjects", creditObjects);
let totalCreditsObject = totalCreditValue(creditObjects)
// log.debug("totalCreditsObject", totalCreditsObject)
let total = 0;
/**
* Adding the Debit Journal Lines
*/
debitObjects.forEach(debitElement => {
for (let key in debitElement) {
if (key.includes('debit_')) {
addJournalLines(key, debitElement, journalEntry, debitObjects, subsidiary)
}
}
});
log.audit("debit total", debitTotal);
// /**
// * Adding the Credit Journal lines
// */
let countofCredit = Object.keys(totalCreditsObject).length;
let loop = 1;
for (let key in totalCreditsObject) {
addCreditJournalLines(key, totalCreditsObject[key], journalEntry, accountsObj, loop, countofCredit, creditObjects, subsidiary)
loop++
}
log.audit("credit total", creditTotal);
journalId = journalEntry.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
log.audit('journalId', journalId)
return journalId
} catch (error) {
log.error('Error in createAutomatedJournal', error)
}
}
/**
* Function to get the project allocation records to set actual allocation
* @param {*} employeeArray
* @param {*} projects
* @param {*} month
* @param {*} year
* @returns {}
*/
function getProjectAllocations(employeeArray, projects, month, year) {
try {
let customrecord_jj_payroll_detailsSearchObj = search.create({
type: "customrecord_jj_payroll_details",
filters:
[
["isinactive", "is", "F"],
"AND",
["custrecord_jj_payroll_emp", "anyof", employeeArray],
"AND",
["custrecord_jj_project_period.isinactive", "is", "F"],
"AND",
["custrecord_jj_project_period.custrecord_jj_project", "anyof", projects],
"AND",
["custrecord_jj_month", "anyof", month],
"AND",
["custrecord_jj_year", "equalto", year],
],
columns:
[
search.createColumn({
name: "internalid",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Internal ID"
}),
search.createColumn({ name: "custrecord_jj_payroll_emp", label: "Employee" }),
search.createColumn({
name: "custrecord_jj_project",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project"
})
]
});
let searchResultCount = customrecord_jj_payroll_detailsSearchObj.runPaged().count;
let payrollObject = {}
if (searchResultCount > 0) {
customrecord_jj_payroll_detailsSearchObj.run().each(function (result) {
let payrollArray = {}
let employee = result.getValue({
name: "custrecord_jj_payroll_emp", label: "Employee"
})
let payroll = result.getValue({
name: "internalid",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Internal ID"
})
let project = result.getValue({
name: "custrecord_jj_project",
join: "CUSTRECORD_JJ_PROJECT_PERIOD",
label: "Project"
})
if (!payrollObject.hasOwnProperty(employee)) {
payrollArray[project] = payroll
payrollObject[employee] = payrollArray;
}
else {
payrollObject[employee][project] = payroll
}
return true;
});
return payrollObject
}
else {
return {}
}
}
catch (err) {
log.error("error@getProjectAllocations", err)
return {}
}
}
/**
* Function to find the sum of times of projects
* @param {} projectTotals
* @returns
*/
function sumObjectValues(projectTotals) {
return Object.values(projectTotals).reduce((acc, val) => acc + val, 0);
}
/**
* Fucntion to calculate the actual allocation
* @param {*} employeeArr
* @param {*} projectArr
* @param {*} monthfilter
* @param {*} finalData
*/
function actualAllocationCalculation(employeeArr, projectArr, monthfilter, finalData, projectArr) {
try {
let projectAllocations = getProjectAllocations(employeeArr, projectArr, monthfilter[0].month.toString(), monthfilter[0].year.toString());
// log.debug("projectAllocations", projectAllocations);
let projectTotals = {};
/**
* For getting the total time tracked by an employee against different activity code for the same project
*/
for (let employeeId in finalData) {
projectTotals[employeeId] = {};
finalData[employeeId].forEach(activity => {
let { project, time } = activity;
projectTotals[employeeId][project] = (projectTotals[employeeId][project] || 0) + parseInt(time);
});
}
// log.debug('projectTotals', projectTotals);
/**
* For getting the total time of the employee tracked in different project with in this time period
*/
let resultSum = {};
for (let key in projectTotals) {
resultSum[key] = sumObjectValues(projectTotals[key]);
}
/**
* For setting the values in the actual allocation records
*/
for (let projectId in projectTotals) {
let totalValue = resultSum[projectId];
for (let subProjectId in projectTotals[projectId]) {
let calculatedValue = (projectTotals[projectId][subProjectId] / totalValue).toFixed(2) * 100;
record.submitFields({
type: 'customrecord_jj_project_alloc',
id: projectAllocations[projectId][subProjectId],
values: {
custrecord_jj_actual_alloc: calculatedValue || ""
}
});
}
}
} catch (err) {
log.error('error@actualAllocationCalculation', err);
}
}
/**
* Function to create an error data if any errors are occurred in the map context
* @param {*} errorMessage
* @param {*} projects
* @param {*} employeeArray
*/
function createErrorFile(errorMessage, mapObject, subsidiary, data) {
try {
let customLogErrorRecord = record.create({
type: "customrecord_jj_journal_errors",
isDynamic: true,
});
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_journl_project_name",
value: subsidiary
});
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_map_data",
value: mapObject
});
if (data.length > 0) {
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_journal_data",
value: data[0]
});
}
customLogErrorRecord.setValue({
fieldId: "custrecord_jj_journal_errors",
value: errorMessage
});
let recordId = customLogErrorRecord.save({
enableSourcing: false,
ignoreMandatoryFields: false
});
}
catch (err) {
log.error("error@createErrorFile", err)
}
}
/**
* Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
* @param {Object} inputContext
* @param {boolean} inputContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Object} inputContext.ObjectRef - Object that references the input data
* @typedef {Object} ObjectRef
* @property {string|number} ObjectRef.id - Internal ID of the record instance that contains the input data
* @property {string} ObjectRef.type - Type of the record instance that contains the input data
* @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use in the map/reduce process
* @since 2015.2
*/
const getInputData = (inputContext) => {
try {
log.debug("---MAP REDUCE STARTS---");
let currentDate = new Date('1/31/2024');
// log.debug("currentDate", currentDate);
let configRecObj = config.load({
type: config.Type.COMPANY_INFORMATION
});
let preferredTimezone = configRecObj.getValue("timezone");
let dateObject = format.parse({
value: currentDate,
type: format.Type.DATE,
timezone: preferredTimezone
});
// log.debug("dateObject", dateObject);
let dateString = format.format({
value: currentDate,
type: format.Type.DATE,
timezone: preferredTimezone
});
// log.debug("dateString", dateString);
let currMonthDateArr = getCurrentMonthData(dateString);
log.debug("current Month Data Array", currMonthDateArr);
if (currMonthDateArr.length <= 0) {
//log.debug("Script will not Execute on " + dateString + "");
return [];
}
let monthlyTimeDataArr = getPreviousMonthData(currMonthDateArr, dateObject);
log.debug("prev Monthly Time Data Arr", monthlyTimeDataArr);
return timeBillSearchData(monthlyTimeDataArr);
} catch (error) {
log.error("---Error @getInputData---", error);
return [];
}
}
/**
* Defines the function that is executed when the map entry point is triggered. This entry point is triggered automatically
* when the associated getInputData stage is complete. This function is applied to each key-value pair in the provided
* context.
* @param {Object} mapContext - Data collection containing the key-value pairs to process in the map stage. This parameter
* is provided automatically based on the results of the getInputData stage.
* @param {Iterator} mapContext.errors - Serialized errors that were thrown during previous attempts to execute the map
* function on the current key-value pair
* @param {number} mapContext.executionNo - Number of times the map function has been executed on the current key-value
* pair
* @param {boolean} mapContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} mapContext.key - Key to be processed during the map stage
* @param {string} mapContext.value - Value to be processed during the map stage
* @since 2015.2
*/
const map = (mapContext) => {
let mapObject = {};
try {
let mapValue = JSON.parse(mapContext.value);
//let mapObject = {};
mapObject.employee = mapValue.values['employee'].value;
mapObject.project = mapValue.values["customer"].value;
mapObject.subsidiary = mapValue.values["custcol_jj_regnl_off_ippf_2091"].value;
mapObject.time = mapValue.values["durationdecimal"];
mapObject.activityCode = mapValue.values["department"].value;
mapObject.division = mapValue.values["class"].value;
mapContext.write({
key: mapObject.subsidiary,
value: mapObject
// value: mapObjects
});
} catch (error) {
log.error("---Error @map---", error);
let errorMessage = error.name + " " + ":" + " " + error.message
createErrorFile(errorMessage, mapObject, '', [])
}
}
/**
* Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
* automatically when the associated map stage is complete. This function is applied to each group in the provided context.
* @param {Object} reduceContext - Data collection containing the groups to process in the reduce stage. This parameter is
* provided automatically based on the results of the map stage.
* @param {Iterator} reduceContext.errors - Serialized errors that were thrown during previous attempts to execute the
* reduce function on the current group
* @param {number} reduceContext.executionNo - Number of times the reduce function has been executed on the current group
* @param {boolean} reduceContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} reduceContext.key - Key to be processed during the reduce stage
* @param {List<String>} reduceContext.values - All values associated with a unique key that was passed to the reduce stage
* for processing
* @since 2015.2
*/
const reduce = (reduceContext) => {
try {
let parsedValue = reduceContext.values.map(element => JSON.parse(element))
// log.debug("parsedValue", parsedValue);
let subsidiary = reduceContext.key;
log.debug('subsidiary', subsidiary)
let currentDate = new Date('1/31/2024');
let configRecObj = config.load({
type: config.Type.COMPANY_INFORMATION
});
let preferredTimezone = configRecObj.getValue("timezone");
let dateObject = format.parse({
value: currentDate,
type: format.Type.DATE,
timezone: preferredTimezone
});
let baseCurrency = search.lookupFields({
type: "subsidiary",
id: subsidiary,
columns: ['currency']
}).currency[0].value || null;
if (parsedValue.length <= 0)
return false
let employeeArr = parsedValue.map(element => element.employee);
// log.debug('employeeArr', employeeArr)
let projectArr = parsedValue.map(element => element.project);
log.debug('projectArr',[...new Set(projectArr)])
let monthfilter = filterForTimeBillSearch(currentDate, subsidiary)
// log.debug('monthfilter',monthfilter)
let uniqueValues = [...new Set(employeeArr)];
log.debug('uniqueValues',uniqueValues)
if (projectArr.length > 0 && employeeArr.length > 0) {
let employeeTimeArr = getProjectTime(employeeArr, subsidiary, projectArr, monthfilter);
// log.debug("employeeTimeArr", employeeTimeArr);
let payrollDetails = getPayrollData(employeeArr, projectArr, monthfilter);
// log.audit("payrollDetails", payrollDetails);
if (employeeTimeArr.length > 0 && payrollDetails.length > 0) {
let combinedData = combineResult(employeeTimeArr, payrollDetails);
// log.debug("combinedData", combinedData);
if (Object.keys(combinedData).length > 0) {
let finalData = getEmployeeProjectData(combinedData);
// log.debug("finalData", finalData);
let exchangeRateData = getCurrencyDataSearch(finalData, baseCurrency);
// log.debug("exchangeRateData", exchangeRateData);
let costCalculationObj = costCalculation(finalData, exchangeRateData, subsidiary);
// log.debug("costCalculationObj", costCalculationObj);
let jorunalData = createAutomatedJournal(subsidiary, costCalculationObj, monthfilter, new Date(dateObject), baseCurrency);
//log.debug('jorunalData', jorunalData);
//TODO Commented for Testing Purpose
// if (jorunalData) {
// let actualallocation = actualAllocationCalculation(employeeArr, projectArr, monthfilter, finalData, projectArr)
// }
}
}
}
} catch (error) {
log.error("Error in reduce", error);
}
}
/**
* Defines the function that is executed when the summarize entry point is triggered. This entry point is triggered
* automatically when the associated reduce stage is complete. This function is applied to the entire result set.
* @param {Object} summaryContext - Statistics about the execution of a map/reduce script
* @param {number} summaryContext.concurrency - Maximum concurrency number when executing parallel tasks for the map/reduce
* script
* @param {Date} summaryContext.dateCreated - The date and time when the map/reduce script began running
* @param {boolean} summaryContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Iterator} summaryContext.output - Serialized keys and values that were saved as output during the reduce stage
* @param {number} summaryContext.seconds - Total seconds elapsed when running the map/reduce script
* @param {number} summaryContext.usage - Total number of governance usage units consumed when running the map/reduce
* script
* @param {number} summaryContext.yields - Total number of yields when running the map/reduce script
* @param {Object} summaryContext.inputSummary - Statistics about the input stage
* @param {Object} summaryContext.mapSummary - Statistics about the map stage
* @param {Object} summaryContext.reduceSummary - Statistics about the reduce stage
* @since 2015.2
*/
const summarize = (summaryContext) => {
try {
log.debug("---MAP REDUCE COMPLETED---");
} catch (error) {
log.error("---Error @summarize---", error);
}
}
return { getInputData, map, reduce, summarize }
});