Jira Code: CCH-51
To create an Excel to send previous month KPI reports on a weekly basis. The report will send automatically on the first day of the month.
Scheduled script:
/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CROW CANYON HOME
*
******************************************************************************
* Date: 23-08-2019
* Author: Jobin & Jismi IT Services LLP
* Script Description: Created a script for sending mail to cutomers attached with monthly KPIs report.
* Date created :
******************************************************************************/
define(['N/file', 'N/search', 'N/record', 'N/render', 'N/encode', 'N/email'],
function(file, search, record, render, encode, email) {
function execute(scriptContext) {
try {
/* Email CCH Total Revenue search */
var dayBeforeToday = null;
var cchTotalRevenueWeek1 = null;
var cchTotalRevenueWeek2 = null;
var cchTotalRevenueWeek3 = null;
var cchTotalRevenueWeek4 = null;
var cchTotalRevenueWeek5 = null;
var transactionSearchObj = search.create({
type: "transaction",
filters: [
["type", "anyof", "CashSale", "CustInvc"],
"AND",
["mainline", "is", "F"],
"AND",
["department", "anyof", "2", "8"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN {netamount} ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN {netamount} ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN {netamount} ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN {netamount} ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN {netamount} ELSE 0 END",
label: "Week 5"
}),
search.createColumn({
name: "formuladate",
summary: "MAX",
formula: "{today}-1",
label: "Date"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
transactionSearchObj.run().each(function(result) {
cchTotalRevenueWeek1 = result.getValue(transactionSearchObj.columns[0]);
cchTotalRevenueWeek2 = result.getValue(transactionSearchObj.columns[1]);
cchTotalRevenueWeek3 = result.getValue(transactionSearchObj.columns[2]);
cchTotalRevenueWeek4 = result.getValue(transactionSearchObj.columns[3]);
cchTotalRevenueWeek5 = result.getValue(transactionSearchObj.columns[4]);
// getting daybefore today
dayBeforeToday = result.getValue(transactionSearchObj.columns[5]);
return true;
});
log.debug("dayBeforeToday", dayBeforeToday);
log.debug("notice 1");
/* Email CGS Total Revenue search */
var cgsTotalRevenueWeek1 = null;
var cgsTotalRevenueWeek2 = null;
var cgsTotalRevenueWeek3 = null;
var cgsTotalRevenueWeek4 = null;
var cgsTotalRevenueWeek5 = null;
var transactionSearchObj = search.create({
type: "transaction",
filters: [
["type", "anyof", "CashSale", "CustInvc"],
"AND",
["mainline", "is", "F"],
"AND",
["department", "anyof", "6", "5", "7"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN {netamount} ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN {netamount} ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN {netamount} ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN {netamount} ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN {netamount} ELSE 0 END",
label: "Week 5"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
transactionSearchObj.run().each(function(result) {
cgsTotalRevenueWeek1 = result.getValue(transactionSearchObj.columns[0]);
cgsTotalRevenueWeek2 = result.getValue(transactionSearchObj.columns[1]);
cgsTotalRevenueWeek3 = result.getValue(transactionSearchObj.columns[2]);
cgsTotalRevenueWeek4 = result.getValue(transactionSearchObj.columns[3]);
cgsTotalRevenueWeek5 = result.getValue(transactionSearchObj.columns[4]);
return true;
});
log.debug("notice 2");
/* Email CCH Retail Revenue search */
var cchRetailRevenueWeek1 = null;
var cchRetailRevenueWeek2 = null;
var cchRetailRevenueWeek3 = null;
var cchRetailRevenueWeek4 = null;
var cchRetailRevenueWeek5 = null;
var transactionSearchObj = search.create({
type: "transaction",
filters: [
["type", "anyof", "CashSale", "CustInvc"],
"AND",
["mainline", "is", "T"],
"AND",
["custtype", "anyof", "20", "15", "11"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN {netamount} ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN {netamount} ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN {netamount} ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN {netamount} ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN {netamount} ELSE 0 END",
label: "Week 5"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
transactionSearchObj.run().each(function(result) {
cchRetailRevenueWeek1 = result.getValue(transactionSearchObj.columns[0]);
cchRetailRevenueWeek2 = result.getValue(transactionSearchObj.columns[1]);
cchRetailRevenueWeek3 = result.getValue(transactionSearchObj.columns[2]);
cchRetailRevenueWeek4 = result.getValue(transactionSearchObj.columns[3]);
cchRetailRevenueWeek5 = result.getValue(transactionSearchObj.columns[4]);
return true;
});
log.debug("notice 3");
/* Email CCH Hospitality Revenue search */
var cchHospitalityRevenueWeek1 = null;
var cchHospitalityRevenueWeek2 = null;
var cchHospitalityRevenueWeek3 = null;
var cchHospitalityRevenueWeek4 = null;
var cchHospitalityRevenueWeek5 = null;
var transactionSearchObj = search.create({
type: "transaction",
filters: [
["type", "anyof", "CashSale", "CustInvc"],
"AND",
["mainline", "is", "T"],
"AND",
["custtype", "anyof", "19", "18", "3"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN {netamount} ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN {netamount} ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN {netamount} ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN {netamount} ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN {netamount} ELSE 0 END",
label: "Week 5"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
transactionSearchObj.run().each(function(result) {
cchHospitalityRevenueWeek1 = result.getValue(transactionSearchObj.columns[0]);
cchHospitalityRevenueWeek2 = result.getValue(transactionSearchObj.columns[1]);
cchHospitalityRevenueWeek3 = result.getValue(transactionSearchObj.columns[2]);
cchHospitalityRevenueWeek4 = result.getValue(transactionSearchObj.columns[3]);
cchHospitalityRevenueWeek5 = result.getValue(transactionSearchObj.columns[4]);
return true;
});
log.debug("notice 4");
/* Email # of CCH Retail SOs added search */
var cchRetailSosAddedWeek1 = null;
var cchRetailSosAddedWeek2 = null;
var cchRetailSosAddedWeek3 = null;
var cchRetailSosAddedWeek4 = null;
var cchRetailSosAddedWeek5 = null;
var salesorderSearchObj = search.create({
type: "salesorder",
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["mainline", "is", "T"],
"AND",
["custtype", "anyof", "15", "20", "11"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN 1 ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN 1 ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN 1 ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN 1 ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN 1 ELSE 0 END",
label: "Week 5"
})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count", searchResultCount);
salesorderSearchObj.run().each(function(result) {
cchRetailSosAddedWeek1 = result.getValue(salesorderSearchObj.columns[0]);
cchRetailSosAddedWeek2 = result.getValue(salesorderSearchObj.columns[1]);
cchRetailSosAddedWeek3 = result.getValue(salesorderSearchObj.columns[2]);
cchRetailSosAddedWeek4 = result.getValue(salesorderSearchObj.columns[3]);
cchRetailSosAddedWeek5 = result.getValue(salesorderSearchObj.columns[4]);
return true;
});
log.debug("notice 5");
/* Email # of CCH Wholesale SOs added search*/
var cchWholesaleSosAddedWeek1 = null;
var cchWholesaleSosAddedWeek2 = null;
var cchWholesaleSosAddedWeek3 = null;
var cchWholesaleSosAddedWeek4 = null;
var cchWholesaleSosAddedWeek5 = null;
var salesorderSearchObj = search.create({
type: "salesorder",
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["mainline", "is", "T"],
"AND",
["custtype", "noneof", "20", "11", "15", "4"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN 1 ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN 1 ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN 1 ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN 1 ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN 1 ELSE 0 END",
label: "Week 5"
})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count", searchResultCount);
salesorderSearchObj.run().each(function(result) {
cchWholesaleSosAddedWeek1 = result.getValue(salesorderSearchObj.columns[0]);
cchWholesaleSosAddedWeek2 = result.getValue(salesorderSearchObj.columns[1]);
cchWholesaleSosAddedWeek3 = result.getValue(salesorderSearchObj.columns[2]);
cchWholesaleSosAddedWeek4 = result.getValue(salesorderSearchObj.columns[3]);
cchWholesaleSosAddedWeek5 = result.getValue(salesorderSearchObj.columns[4]);
return true;
});
log.debug("notice 6");
/* Email # of CGS SOs added search*/
var cgsSosAddedWeek1 = null;
var cgsSosAddedWeek2 = null;
var cgsSosAddedWeek3 = null;
var cgsSosAddedWeek4 = null;
var cgsSosAddedWeek5 = null;
var salesorderSearchObj = search.create({
type: "salesorder",
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["mainline", "is", "T"],
"AND",
["custtype", "anyof", "4"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN 1 ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN 1 ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN 1 ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN 1 ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN 1 ELSE 0 END",
label: "Week 5"
})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count", searchResultCount);
salesorderSearchObj.run().each(function(result) {
cgsSosAddedWeek1 = result.getValue(salesorderSearchObj.columns[0]);
cgsSosAddedWeek2 = result.getValue(salesorderSearchObj.columns[1]);
cgsSosAddedWeek3 = result.getValue(salesorderSearchObj.columns[2]);
cgsSosAddedWeek4 = result.getValue(salesorderSearchObj.columns[3]);
cgsSosAddedWeek5 = result.getValue(salesorderSearchObj.columns[4]);
return true;
});
log.debug("notice 7");
/* Email # of fulfillments search */
var fulfillmentsWeek1 = null;
var fulfillmentsWeek2 = null;
var fulfillmentsWeek3 = null;
var fulfillmentsWeek4 = null;
var fulfillmentsWeek5 = null;
var itemfulfillmentSearchObj = search.create({
type: "itemfulfillment",
filters: [
["type", "anyof", "ItemShip"],
"AND",
["mainline", "is", "T"],
"AND",
["trandate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN 1 ELSE 0 END",
label: "Week 1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN 1 ELSE 0 END",
label: "Week 2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN 1 ELSE 0 END",
label: "Week 3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN 1 ELSE 0 END",
label: "Week 4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN 1 ELSE 0 END",
label: "Week 5"
})
]
});
var searchResultCount = itemfulfillmentSearchObj.runPaged().count;
log.debug("itemfulfillmentSearchObj result count", searchResultCount);
itemfulfillmentSearchObj.run().each(function(result) {
fulfillmentsWeek1 = result.getValue(itemfulfillmentSearchObj.columns[0]);
fulfillmentsWeek2 = result.getValue(itemfulfillmentSearchObj.columns[1]);
fulfillmentsWeek3 = result.getValue(itemfulfillmentSearchObj.columns[2]);
fulfillmentsWeek4 = result.getValue(itemfulfillmentSearchObj.columns[3]);
fulfillmentsWeek5 = result.getValue(itemfulfillmentSearchObj.columns[4]);
return true;
});
log.debug("notice 8");
/* Email # Missed ship dates search*/
var missedShipDatesWeek1 = null;
var missedShipDatesWeek2 = null;
var missedShipDatesWeek3 = null;
var missedShipDatesWeek4 = null;
var missedShipDatesWeek5 = null;
var salesorderSearchObj = search.create({
type: "salesorder",
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["formulanumeric: CASE WHEN ({shipdate} IS NOT NULL AND {transhippeddate} IS NULL) THEN 1 ELSE ((CASE WHEN ({shipdate} IS NOT NULL AND {transhippeddate} IS NOT NULL) THEN (CASE WHEN {shipdate} < {transhippeddate} THEN 1 ELSE 0 END) ELSE 0 END))END", "equalto", "1"],
"AND",
["mainline", "is", "T"],
"AND",
["shipdate", "within", "lastmonth"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({shipdate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({shipdate}),'DD')) <= '07')) THEN 1 ELSE 0 END",
label: "Formula (Numeric)"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({shipdate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({shipdate}),'DD')) <= '14')) THEN 1 ELSE 0 END",
label: "Formula (Numeric)"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({shipdate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({shipdate}),'DD')) <= '21')) THEN 1 ELSE 0 END",
label: "Formula (Numeric)"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({shipdate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({shipdate}),'DD')) <= '28')) THEN 1 ELSE 0 END",
label: "Formula (Numeric)"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "CASE WHEN (((TO_CHAR(TO_DATE({shipdate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({shipdate}),'DD')) <= '31')) THEN 1 ELSE 0 END",
label: "Formula (Numeric)"
})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count", searchResultCount);
salesorderSearchObj.run().each(function(result) {
missedShipDatesWeek1 = result.getValue(salesorderSearchObj.columns[0]);
missedShipDatesWeek2 = result.getValue(salesorderSearchObj.columns[1]);
missedShipDatesWeek3 = result.getValue(salesorderSearchObj.columns[2]);
missedShipDatesWeek4 = result.getValue(salesorderSearchObj.columns[3]);
missedShipDatesWeek5 = result.getValue(salesorderSearchObj.columns[4]);
return true;
});
log.debug("notice 9");
// Email CCH/Bornn Inventory turnover
var BornnInventoryTurnoverWeek1 = null;
var BornnInventoryTurnoverWeek2 = null;
var BornnInventoryTurnoverWeek3 = null;
var BornnInventoryTurnoverWeek4 = null;
var BornnInventoryTurnoverWeek5 = null;
var transactionSearchObj = search.create({
type: "transaction",
filters: [
["trandate", "within", "lastmonth"],
"AND",
["department", "anyof", "2", "8"]
],
columns: [
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "ROUND(SUM(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN ABS(NVL({cogsamount}/2,0)) ELSE 0 END) / NULLIF((SUM(ROUND(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '01') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '07')) THEN {item.totalvalue} ELSE 0 END,5)))/7, 0),5)",
label: "week1"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "ROUND(SUM(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN ABS(NVL({cogsamount}/2,0)) ELSE 0 END) / NULLIF((SUM(ROUND(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '08') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '14')) THEN {item.totalvalue} ELSE 0 END,5)))/7, 0),5)",
label: "week2"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "ROUND(SUM(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN ABS(NVL({cogsamount}/2,0)) ELSE 0 END) / NULLIF((SUM(ROUND(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '15') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '21')) THEN {item.totalvalue} ELSE 0 END,5)))/7, 0),5)",
label: "week3"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "ROUND(SUM(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN ABS(NVL({cogsamount}/2,0)) ELSE 0 END) / NULLIF((SUM(ROUND(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '22') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '28')) THEN {item.totalvalue} ELSE 0 END,5)))/7, 0),5)",
label: "week4"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "ROUND(SUM(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN ABS(NVL({cogsamount}/2,0)) ELSE 0 END) / NULLIF((SUM(ROUND(CASE WHEN (((TO_CHAR(TO_DATE({trandate}),'DD')) >= '29') AND ((TO_CHAR(TO_DATE({trandate}),'DD')) <= '31')) THEN {item.totalvalue} ELSE 0 END,5)))/7, 0),5)",
label: "week5"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count", searchResultCount);
transactionSearchObj.run().each(function(result) {
BornnInventoryTurnoverWeek1 = result.getValue(transactionSearchObj.columns[0]);
BornnInventoryTurnoverWeek2 = result.getValue(transactionSearchObj.columns[1]);
BornnInventoryTurnoverWeek3 = result.getValue(transactionSearchObj.columns[2]);
BornnInventoryTurnoverWeek4 = result.getValue(transactionSearchObj.columns[3]);
BornnInventoryTurnoverWeek5 = result.getValue(transactionSearchObj.columns[4]);
return true;
});
// code for create the date part is dynamic
var lastDateOfMonth = dayBeforeToday.split('/');
lastDateOfMonthDay = lastDateOfMonth[1];
lastMonthCount = lastDateOfMonth[0];
log.debug("lastDateOfMonthDay", lastDateOfMonthDay);
/* replace xml part code */
var xmlReplaceContent = '<Row ss:AutoFitHeight="0" ss:Height="29.25">'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s65"><Data ss:Type="String">KPIs</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s65"><Data ss:Type="String">' + lastDateOfMonth[0] + '/' + 1 + '/' + lastDateOfMonth[2] + ' - ' + lastDateOfMonth[0] + '/' + 7 + '/' + lastDateOfMonth[2] + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s65"><Data ss:Type="String">' + lastDateOfMonth[0] + '/' + 8 + '/' + lastDateOfMonth[2] + ' - ' + lastDateOfMonth[0] + '/' + 14 + '/' + lastDateOfMonth[2] + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s65"><Data ss:Type="String">' + lastDateOfMonth[0] + '/' + 15 + '/' + lastDateOfMonth[2] + ' - ' + lastDateOfMonth[0] + '/' + 21 + '/' + lastDateOfMonth[2] + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s65"><Data ss:Type="String">' + lastDateOfMonth[0] + '/' + 22 + '/' + lastDateOfMonth[2] + ' - ' + lastDateOfMonth[0] + '/' + 28 + '/' + lastDateOfMonth[2] + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s65"><Data ss:Type="String">' + lastDateOfMonth[0] + '/' + 29 + '/' + lastDateOfMonth[2] + ' - ' + dayBeforeToday + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String">CCH Total Revenue</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchTotalRevenueWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchTotalRevenueWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchTotalRevenueWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchTotalRevenueWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchTotalRevenueWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String">CGS Total Revenue</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cgsTotalRevenueWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cgsTotalRevenueWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cgsTotalRevenueWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cgsTotalRevenueWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cgsTotalRevenueWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String">CCH Retail Revenue</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchRetailRevenueWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchRetailRevenueWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchRetailRevenueWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchRetailRevenueWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchRetailRevenueWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String">CCH Hospitality Revenue</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchHospitalityRevenueWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchHospitalityRevenueWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchHospitalityRevenueWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchHospitalityRevenueWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s62"><Data ss:Type="Number">' + cchHospitalityRevenueWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String"># of CCH Retail SOs added</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchRetailSosAddedWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchRetailSosAddedWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchRetailSosAddedWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchRetailSosAddedWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchRetailSosAddedWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String"># of CCH Wholesale SOs added</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchWholesaleSosAddedWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchWholesaleSosAddedWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchWholesaleSosAddedWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchWholesaleSosAddedWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cchWholesaleSosAddedWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String"># of CGS SOs added</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cgsSosAddedWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cgsSosAddedWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cgsSosAddedWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cgsSosAddedWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + cgsSosAddedWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String"># of fulfilments</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + fulfillmentsWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + fulfillmentsWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + fulfillmentsWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + fulfillmentsWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + fulfillmentsWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String"># Missed ship dates</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + missedShipDatesWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + missedShipDatesWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + missedShipDatesWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + missedShipDatesWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s63"><Data ss:Type="Number">' + missedShipDatesWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
xmlReplaceContent = xmlReplaceContent + '<Row ss:AutoFitHeight="0" ss:Height="21">'
xmlReplaceContent = xmlReplaceContent + '<Cell><Data ss:Type="String">CCH/Bornn Inventory turnover</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s66"><Data ss:Type="Number">' + BornnInventoryTurnoverWeek1 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s66"><Data ss:Type="Number">' + BornnInventoryTurnoverWeek2 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s66"><Data ss:Type="Number">' + BornnInventoryTurnoverWeek3 + '</Data></Cell>'
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s66"><Data ss:Type="Number">' + BornnInventoryTurnoverWeek4 + '</Data></Cell>'
if (lastDateOfMonthDay > 28) {
xmlReplaceContent = xmlReplaceContent + '<Cell ss:StyleID="s66"><Data ss:Type="Number">' + BornnInventoryTurnoverWeek5 + '</Data></Cell>'
}
xmlReplaceContent = xmlReplaceContent + '</Row>'
log.debug("notice 10");
var myXMLFile = file.load({
id: '2555723'
});
log.debug("notice 11");
var xmlContent = myXMLFile.getContents();
xmlContent = xmlContent.replace("ReplacewithContent", xmlReplaceContent);
log.debug("notice 12", xmlContent);
var strXmlEncoded = encode.convert({
string: xmlContent,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
log.debug("notice 13");
var fileObj = file.create({
name: 'KPIs report.xls',
fileType: file.Type.EXCEL,
contents: strXmlEncoded,
folder: 2550002
});
var fileId = fileObj.save();
log.debug("fileId", fileId);
// for finding last month for a bussiness
var month = [];
month[1] = "January";
month[2] = "February";
month[3] = "March";
month[4] = "April";
month[5] = "May";
month[6] = "June";
month[7] = "July";
month[8] = "August";
month[9] = "September";
month[10] = "October";
month[11] = "November";
month[12] = "December";
var MonthYear = month[lastMonthCount] + ' - ' + lastDateOfMonth[2];
log.debug("MonthYear", MonthYear);
email.send({
author: 38320, //administrator Mail,
recipients: 'hormese@jobinandjismi.com',
subject: 'Monthly KPI Report: ' + MonthYear,
body: 'Please find the attached copy of KPI report for the previous month',
attachments: [fileObj],
relatedRecords: {
entityId: 38320
}
});
log.debug("notice 14");
/*context.response.writeFile(fileObj);*/
} catch (er) {
log.debug('er', er.message)
}
}
return {
execute: execute
};
});XML code for creating the excel
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>19</Author>
<LastAuthor>19</LastAuthor>
<Created>2019-08-23T09:00:40Z</Created>
<LastSaved>2019-08-23T09:31:01Z</LastSaved>
<Version>16.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9465</WindowHeight>
<WindowWidth>24000</WindowWidth>
<WindowTopX>32767</WindowTopX>
<WindowTopY>32767</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<NumberFormat ss:Format="[$$-409]#,##0.00;[Red][$$-409]#,##0.00"/>
</Style>
<Style ss:ID="s63">
<NumberFormat ss:Format="0;[Red]0"/>
</Style>
<Style ss:ID="s65">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="10" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Column ss:AutoFitWidth="0" ss:Width="155.25"/>
<Column ss:AutoFitWidth="0" ss:Width="119.25" ss:Span="4"/>
ReplacewithContent
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Unsynced/>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>15</ActiveRow>
<ActiveCol>3</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:AutoFitHeight="0"/>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row ss:AutoFitHeight="0"/>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>