Below highlighted columns contacts basically will store the owner data of apartment
and tenant columns will store the data of space user from apartment
so 7 th row below screenshot showing data under the contact1 and contact2 columns it should be under the tenant columns.
(function() {
// excel
data.buttonLabel = options.button_label || "Excel export";
data.buttonIconClass = options.button_icon_class || "glyphicon glyphicon-print";
if (input && input.action == 'excel_export_owners_clicked') {
var utils = new x_sksw_warranty.WT_assetResponsibleUtils();
var realEstatePropertyID = options.real_estate_property_id || $sp.getParameter('property');
var contactDetailsText = gs.getMessage('com.ska_wt.excel_export.contact_details');
// Construct array of arrays for excel export
data.excelData = [];
// This is just an empty row in the Excel
var emptyRow = [];
// First row: Real estate property name AND address name
var realEstatePropertyRow = [];
var realEstatePropertyGR = new GlideRecord('x_sksw_warranty_real_estate_property');
if (realEstatePropertyGR.get(realEstatePropertyID)) {
var propertyName = realEstatePropertyGR.getDisplayValue();
var addressName = '';
var locationGR = new GlideRecord('x_sksw_warranty_locations');
if (locationGR.get(realEstatePropertyGR.getValue('addresses'))) {
addressName = locationGR.getDisplayValue();
}
realEstatePropertyRow.push(propertyName + ', ' + addressName);
}
// Worksheet properties
data.sheetProperties = {
fileName: contactDetailsText + ' - ' + realEstatePropertyRow[0],
worksheetName: gs.getMessage('com.ska_wt.excel_export.contact_details')
};
// Get header texts e.g. Apartment, Owner, etc.
var excelTableHeader = gs.getMessage('com.ska_wt.excel_export.apartment_owners');
// Building up the Excel content
data.excelData.push(realEstatePropertyRow);
data.excelData.push(emptyRow);
data.excelData.push([contactDetailsText.toUpperCase()]);
data.excelData.push(emptyRow);
data.excelData.push(excelTableHeader);
// Get excelTableHeader position in the excelData array, to modify it later
var excelTableHeaderArrayPosition = data.excelData.indexOf(excelTableHeader);
// Query apartment table by real estate property, add contacts data to the excel. One row = contact data for one apartment
var maxOwnersCount = 0;
var maxSpaceUsersCount = 0;
var apartmentGR = new GlideRecord('x_sksw_warranty_apartment');
apartmentGR.addQuery('real_estate_property', realEstatePropertyID);
apartmentGR.orderBy('stairway');
apartmentGR.orderBy('space_number');
apartmentGR.query();
while (apartmentGR.next()) {
var apartmentDetails = [];
// Apartment Stairway + Space number
var stairway = apartmentGR.getValue('stairway');
var spaceNumber = apartmentGR.getValue('space_number');
var apartment = ((stairway ? stairway + ' ' : '') + (spaceNumber ? spaceNumber : '')) || apartmentGR.getDisplayValue(); // or just display a '-' character? Display value is more informative IMO..
apartmentDetails.push(apartment);
// Master key usage
var masterKeyUsage = apartmentGR.getValue("master_key_usage") ? gs.getMessage(apartmentGR.getValue("master_key_usage")) : "";
apartmentDetails.push(masterKeyUsage);
// ..and the Pets ≧◔◡◔≦
var pets = apartmentGR.getValue('pets') ? gs.getMessage(apartmentGR.getValue('pets')) : "";
apartmentDetails.push(pets);
// Get owners from M2M table
var ownerGRs = utils.getCustomerGRsOfApartment({
apartmentID: apartmentGR.getUniqueValue(),
role: 'owner',
active: true
});
// Determine the maximum count of owners for alignment purposes
if (ownerGRs.length > maxOwnersCount) {
maxOwnersCount = ownerGRs.length;
}
// Loop through owners and add their details to the apartmentDetails array
for (var i = 0; i < maxOwnersCount; i++) {
if (i < ownerGRs.length) {
// Add owner details under contact headers
apartmentDetails.push(ownerGRs[i].getDisplayValue() || " ");
apartmentDetails.push(ownerGRs[i].getValue('mobile_phone') || " ");
apartmentDetails.push(ownerGRs[i].getValue('email') || " ");
} else {
// If there are fewer owners than the maximum count, add empty strings
apartmentDetails.push(" ");
apartmentDetails.push(" ");
apartmentDetails.push(" ");
}
}
// Get Space users from M2M table
var spaceUserGRs = utils.getCustomerGRsOfApartment({
apartmentID: apartmentGR.getUniqueValue(),
role: 'user',
active: true
});
// Determine the maximum count of space users for alignment purposes
//gs.addInfoMessage('Current spaceUserGRs length: ' + spaceUserGRs.length);
if (spaceUserGRs.length > maxSpaceUsersCount) {
maxSpaceUsersCount = spaceUserGRs.length;
}
// Loop through space users and add their details to the apartmentDetails array
for (var j = 0; j < maxSpaceUsersCount ; j++) {
//gs.addInfoMessage('Message'+j);
if (j < spaceUserGRs.length ) {
// Add space user details under tenant headers
gs.addInfoMessage('spaceUserGRs'+spaceUserGRs[j].getDisplayValue());
apartmentDetails.push(spaceUserGRs[j].getDisplayValue() || " ");
apartmentDetails.push(spaceUserGRs[j].getValue('mobile_phone') || " ");
apartmentDetails.push(spaceUserGRs[j].getValue('email') || " ");
} else {
// If there are fewer space users than the maximum count, add empty strings
apartmentDetails.push(" ");
apartmentDetails.push(" ");
apartmentDetails.push(" ");
}
}
// Add the apartment details row to the Excel
data.excelData.push(apartmentDetails);
}
// Replace OWNER_DATA_PLACEHOLDER, TENANT_DATA_PLACEHOLDER column labels
data.m = {
owner: gs.getMessage('com.ska_wt.contact'),
spaceUser: gs.getMessage('com.ska_wt.space_user'),
email: gs.getMessage('com.ska_wt.email'),
phoneNumber: gs.getMessage('com.ska_wt.phone_number')
};
// Split excelTableHeader: Replace OWNER_DATA_PLACEHOLDER and TENANT_DATA_PLACEHOLDER
var excelTableHeaderArray = excelTableHeader.split(',');
// Generate Excel header labels for owner and tenant data. It could be 1-n
// Use "maxOwnersCount", because this is the number how many columns do we need to have: if one apartment have more owners than the rest
var ownersDetails = [];
var spaceUsersDetails = [];
if (maxOwnersCount > 0) {
for (var i = 1; i <= maxOwnersCount; i++) {
ownersDetails.push(data.m.owner + ' ' + i);
ownersDetails.push(data.m.owner + ' ' + i + ' ' + data.m.phoneNumber);
ownersDetails.push(data.m.owner + ' ' + i + ' ' + data.m.email);
}
}
if (maxSpaceUsersCount > 0) {
for (var j =1; j <= maxSpaceUsersCount; j++) {
gs.addInfoMessage('spacusercount'+j);
spaceUsersDetails.push(data.m.spaceUser + ' ' + j);
spaceUsersDetails.push(data.m.spaceUser + ' ' + j + ' ' + data.m.phoneNumber);
spaceUsersDetails.push(data.m.spaceUser + ' ' + j + ' ' + data.m.email);
}
}
// Construct a new array; it is cleaner to follow
var newExcelTableHeaderArray = [];
newExcelTableHeaderArray.push(excelTableHeaderArray[0]);
newExcelTableHeaderArray.push(excelTableHeaderArray[3]);
newExcelTableHeaderArray.push(excelTableHeaderArray[4]);
if (maxOwnersCount > 0) {
for (var i = 0; i < ownersDetails.length; i++) {
newExcelTableHeaderArray.push(ownersDetails[i]); // Add owner details columns
}
}
if (maxSpaceUsersCount > 0) {
for (var j = 0; j < spaceUsersDetails.length; j++) {
newExcelTableHeaderArray.push(spaceUsersDetails[j]); // Add tenant details columns
}
}
// Then replace the value at excelTableHeaderArrayPosition in the Excel
data.excelData[excelTableHeaderArrayPosition] = newExcelTableHeaderArray.join(",").split(",");
data.columnsLength = data.excelData[excelTableHeaderArrayPosition].length;
// Set Excel table column widths for each column, based on the header row
// Defined in characters, not in pixels.
// You can set this value in the parent widget with options.default_column_width.
// Default value is 20.
data.excelColumnWidths = [];
var excelColumnWidth = input.default_column_width || 20;
for (var i = 0; i < data.columnsLength; i++) {
var columnWidth = {
wch: excelColumnWidth
};
data.excelColumnWidths.push(columnWidth);
}
}
})();
