#Script
Name: CO Cashout Functions
Handler: co
###Script:###
var foreignCurrencyName = 'HNL'; // for Exchange Rate Task Type
var currencyFormat = '0.00'; // for Helper.ToNumber(num,format)
var nowDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
// Task Start/End Dates
var openDate = getWorkPeriodData('start');
var closeDate = getWorkPeriodData('end');
// Task Default parameters
var userName = 'Admin';
var isCompleted = '';
var customData = [];
var content = '';
var state = '';
// Task Filter parameters
var startFilter = getWorkPeriodData('start');
var endFilter = getWorkPeriodData('end');
// gets Last Workperiod Data
function getWorkPeriodData(dataType) {
var SQL = "SELECT [Id],convert(varchar(25),[StartDate],126),convert(varchar(25),[EndDate],126),[StartDescription],[EndDescription] FROM [WorkPeriods] WHERE 1=1 AND [Id] = (SELECT MAX([Id]) FROM [WorkPeriods])";
var res = sql.Exec(SQL);
var wpdata = res[0].split(',');
var retval = '';
switch (dataType) {
case 'id':
retval = wpdata[0];
break;
case 'start':
retval = wpdata[1].replace('T',' ');
break;
case 'end':
retval = wpdata[2].replace('T',' ');
break;
case 'isOpen':
retval = wpdata[1]==wpdata[2] ? true : false;
break;
case 'descStart':
retval = wpdata[3];
break;
case 'descEnd':
retval = wpdata[4];
break;
default:
retval = wpdata[0];
break;
}
return retval;
}
// gets Count or Total data for a Task
function getCount(tenderType,denom,ct,countDate) {
// getCount('Cash HNL','100')
// getCount('Cash HNL','100','countTotal')
// getCount('Cash HNL','100','denomTotal')
// getCount('Cash HNL','Float')
// getCount('Cash HNL','Balance')
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
denom = typeof(denom)==='undefined' || denom=='' ? 'tenderTotal' : denom;
ct = typeof(ct)==='undefined' || ct=='' ? 'countTotal' : ct;
var taskType = 'CO ' + tenderType;
var taskName = tenderType + ' ' + countDate;
var msg = '';
// special Counts for Credit Cards
var isCard = tenderType.indexOf('Card') > -1 ? true : false;
//var isNeg = denom.indexOf('DEVO')>-1 || denom.indexOf('TIPS')>-1 ? true : false;
var isDEVO = denom.indexOf('DEVO')>-1 ? true : false;
// getTasks(taskType, completedFilter, nameLike, startFilter, endFilter, contentLike, fieldFilter, stateFilter, callback)
var counts = gqlEXEC(getTasks(taskType,isCompleted,taskName));
//return counts;
counts = JSON.parse(counts);
//return counts.errors;
if (counts.errors) {
var parm = "tenderType:"+tenderType+", denom:"+denom+", ct:"+ct;
throwError('getCount()',taskType,taskName,'',parm,true)
//dlg.ShowMessage(msg);
return -1;
}
counts = counts.data.tasks;
var denomTotal = 0;
var tenderTotal = 0;
var floatTotal = 0;
var retVal = -1;
var taskCount = counts.length;
if (taskCount==0) {
//msg = 'getCount() WARNING !!!\r\n\r\n';
//msg+= 'No value found for Denom ['+denom+'] of Task Type ['+taskType+'] with Task Name ['+taskName+']';
var parm = "tenderType:"+tenderType+", denom:"+denom+", ct:"+ct;
throwError('getCount()',taskType,taskName,'',parm,true)
//dlg.ShowMessage(msg);
//return -1;
}
for (var c=0; c<taskCount; c++) {
var countFields = counts[c].customData;
var fieldCount = countFields.length;
for (var f=0; f<fieldCount; f++) {
if (countFields[f].name.indexOf('Count') > -1) {
var denomName = countFields[f].name.substr(5,5);
var denomCount = countFields[f].value;
if (!parseInt(denomName)) {
denomTotal = Number(denomCount);
} else {
denomTotal = Number(denomName) * Number(denomCount);
}
//isNeg = countFields[f].name.indexOf('DEVO') > -1 || countFields[f].name.indexOf('TIPS') > -1 ? true : false;
isDEVO = countFields[f].name.indexOf('DEVO') > -1 ? true : false;
isTIPS = countFields[f].name.indexOf('TIPS') > -1 ? true : false;
if (isDEVO) {
floatTotal += denomTotal;
} else if (isTIPS) {
tenderTotal += 0;
} else {
tenderTotal += denomTotal;
}
if (denom==denomName) {
if (ct=='countTotal') {
retVal = denomCount;
} else {
retVal = Helper.Format(denomTotal,currencyFormat);
}
}
} else if (countFields[f].name.indexOf('Float') > -1 && denom=='Float') {
retVal = Helper.Format(countFields[f].value,currencyFormat);
} else if (countFields[f].name.indexOf('Balance') > -1 && denom=='Balance') {
retVal = Helper.Format(countFields[f].value,currencyFormat);
} else if (countFields[f].name.indexOf('Account') > -1 && denom=='Account') {
retVal = Helper.Format(countFields[f].value,currencyFormat);
} else if (countFields[f].name.indexOf('PlusMinus') > -1 && denom=='PlusMinus') {
retVal = Helper.Format(countFields[f].value,currencyFormat);
}
}
}
if (denom=='Float' && isCard) {
retVal = Helper.Format(floatTotal,currencyFormat);
}
if (denom=='tenderTotal') {
retVal = Helper.Format(tenderTotal,currencyFormat);
}
if (retVal==-1) {
//msg = 'getCount() WARNING !!!\r\n\r\n';
//msg+= 'No value found for Denom ['+denom+'] of Task Type ['+taskType+'] with Task Name ['+taskName+']';
var parm = "tenderType:"+tenderType+", denom:"+denom+", ct:"+ct;
//throwError('getCount()',taskType,taskName,'',parm,true)
//dlg.ShowMessage(msg);
//return -1;
retVal = ct=='countTotal' ? 0 : Helper.Format(0,currencyFormat);
}
return retVal;
}
// sets Count or Total data for a Task
function setCount(tenderType,countName,countValue,countDate){
// setCount('Cash HNL','100','5')
// setCount('Cash HNL','Float','2500')
// setCount('Cash HNL','Account','{ACCOUNT TOTAL:Cash HNL}')
// setCount('Cash HNL','PlusMinus','0')
// setCount('Cash USD','20','6')
// setCount('Cash USD','Account','10')
// setCount('Card USD','VISA','100')
openDate = getWorkPeriodData('start');
countName = countName=='Total' || countName=='Float' || countName=='Balance' || countName=='Account' || countName=='PlusMinus' ? countName : 'Count'+countName;
countValue = typeof(countValue)==='undefined' || countValue=='' ? '0' : countValue;
countValue = tenderType.indexOf('Card') > -1 || countName.indexOf('Float') > -1 ? Helper.Format(countValue,currencyFormat) : countValue;
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
var isCard = tenderType.indexOf('Card') > -1 ? true : false;
var msg = '';
msg+='setCount()';
msg+="\r\ntenderType:"+tenderType;
msg+="\r\ncountName:"+countName;
msg+="\r\ncountValue:"+countValue;
//dlg.ShowMessage(msg);
var ident = tenderType + '_' + countDate;
var taskType = 'CO ' + tenderType;
var taskName = tenderType + ' ' + countDate;
var isCompleted = '';
var userName = 'Admin';
var content = '';
var state = '';
var customData = [];
customData.push({name:"Id",value:ident});
customData.push({name:countName,value:countValue});
var startFilter = getWorkPeriodData('start');
var endFilter = getWorkPeriodData('end');
// getTasks(taskType, completedFilter, nameLike, startFilter, endFilter, contentLike, fieldFilter, stateFilter, callback)
var tasks = gqlEXEC(getTasks(taskType,isCompleted,taskName));
//return tasks;
tasks = JSON.parse(tasks);
//return tasks.errors[0].message;
var taskExists = tasks.errors ? false : true;//tasks.data.tasks.length > 0 ? true : false;
//return taskExists;
if (!taskExists) {
var parm = "tenderType:"+tenderType+", countName:"+countName+", countValue:"+countValue;
throwError('setCount()',taskType,taskName,'',parm,true)
return -1;
} else {
var counts = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName, customData, content, state));
}
//return counts;
var tenderFloat = Number(getTotal(tenderType,'Float').replace(',',''));
var tenderAccount = Number(getTotal(tenderType,'Account').replace(',',''));
var tenderTotal = Number(getTotal(tenderType).replace(',',''));
//return tenderTotal;
if (isCard) {
var DEVO = Number(getTotal(tenderType,'DEVO').replace(',',''));
var TIPS = Number(getTotal(tenderType,'TIPS').replace(',',''));
//tenderFloat = DEVO + TIPS;
tenderFloat = (-1 * DEVO); // this is NOT really a Float, but a value that is removed from the CC Close Report, so we add it back in by negating it
}
if (tenderTotal==-1) {
var parm = "tenderType:"+tenderType+", countName:"+countName+", countValue:"+countValue;
throwError('setCount()',taskType,taskName,taskIdent,parm,true)
return -1;
}
//return tenderTotal;
customData.push({name:"Total",value:Helper.Format(tenderTotal,currencyFormat)});
var tenderBalance = tenderTotal - tenderFloat;
customData.push({name:"Balance",value:Helper.Format(tenderBalance,currencyFormat)});
var tenderPlusMinus = tenderBalance - tenderAccount;
customData.push({name:"PlusMinus",value:Helper.Format(tenderPlusMinus,currencyFormat)});
//dlg.AskQuestion(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName, customData, content, state),"Ok");
var upd = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName, customData, content, state));
//return upd;
return tenderTotal;
}
// gets Total data for a Task via getCount()
function getTotal(tenderType,countName,countDate) {
// getTotal('Card HNL')
// getTotal('Card HNL','AMEX')
// getTotal('Cash HNL')
// getTotal('Cash HNL','100')
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
var total = getCount(tenderType,countName,'denomTotal',countDate);
return Helper.Format(total,currencyFormat);
}
// initializes all Count and Total data to 0 for Tasks
function initCounts() {
var cashAccounts = ['Cash USD','Cash HNL'];
var cashDenoms = [1000,500,100,50,20,10,5,2,1];
for (var a=0; a < cashAccounts.length; a++) {
for (var d=0; d < cashDenoms.length; d++) {
setCount(cashAccounts[a],cashDenoms[d],0);
}
setCount(cashAccounts[a],'Float',0.00);
setCount(cashAccounts[a],'Total',0.00);
setCount(cashAccounts[a],'Balance',0.00);
setCount(cashAccounts[a],'Account',0.00);
setCount(cashAccounts[a],'PlusMinus',0.00);
}
setCount('Cash USD','Float',200.00);
setCount('Cash HNL','Float',2500.00);
var cardAccounts = ['Card USD','Card HNL'];
var cardTypes = ['AMEX','DISC','MAST','VISA','OTHR'];
for (var a=0; a < cardAccounts.length; a++) {
for (var d=0; d<cardTypes.length; d++) {
setCount(cardAccounts[a],cardTypes[d],0);
}
setCount(cardAccounts[a],'Total',0.00);
setCount(cardAccounts[a],'Account',0.00);
setCount(cardAccounts[a],'PlusMinus',0.00);
}
return 0;
}
// starts (adds) a Count Task
function openCounts(countDate) {
// create the Tasks
openDate = getWorkPeriodData('start');
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
var accounts = ['Cash USD','Cash HNL','Card USD','Card HNL'];
for (var a=0; a<accounts.length; a++) {
var taskType = 'CO ' + accounts[a];
var taskName = accounts[a] + ' ' + countDate;
var ident = accounts[a] + '_' + countDate;
customData = [{name:"Id",value:ident}];
isCompleted = 'false';
var counts = gqlEXEC(addTasks([taskType],[taskName],isCompleted,customData,userName));
}
var xr = openXR();
var wp = openWorkperiod();
return 0;
}
// ends (completes) Count Tasks
function closeCounts(countDate) {
// Complete the Tasks
openDate = getWorkPeriodData('start');
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
var accounts = ['Cash USD','Cash HNL','Card USD','Card HNL'];
for (var a=0; a<accounts.length; a++) {
var taskType = 'CO ' + accounts[a];
var taskName = accounts[a] + ' ' + countDate;
var ident = accounts[a] + '_' + countDate;
customData = [{name:"Id",value:ident}];
isCompleted = 'true';
var counts = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName, customData));
}
var xr = closeXR();
var wp = closeWorkperiod();
return 0;
}
// starts (adds) a Workperiod Task
function openWorkperiod(countDate) {
// create the Tasks
openDate = getWorkPeriodData('start');
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
var wpId = getWorkPeriodData('id');
var isOpen = getWorkPeriodData('isOpen');
var dateStart = getWorkPeriodData('start');
var dateEnd = getWorkPeriodData('end');
var descStart = getWorkPeriodData('descStart');
var descEnd = getWorkPeriodData('descEnd');
var taskType = 'Workperiod';
var taskName = taskType + ' ' + countDate;
var ident = taskType + '_' + countDate;
var isCompleted = 'false';
var customData = [];
customData.push({name:"Id",value:ident});
customData.push({name:"id",value:wpId});
customData.push({name:"isOpen",value:isOpen});
customData.push({name:"dateStart",value:dateStart});
customData.push({name:"dateEnd",value:dateEnd});
customData.push({name:"descStart",value:descStart});
customData.push({name:"descEnd",value:descEnd});
var counts = gqlEXEC(addTasks([taskType],[taskName],isCompleted,customData,userName));
return 0;
}
// ends (completes) Workperiod Tasks
function closeWorkperiod(countDate) {
// Complete the Tasks
openDate = getWorkPeriodData('start');
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
var wpId = getWorkPeriodData('id');
var isOpen = getWorkPeriodData('isOpen');
var dateStart = getWorkPeriodData('start');
var dateEnd = getWorkPeriodData('end');
var descStart = getWorkPeriodData('descStart');
var descEnd = getWorkPeriodData('descEnd');
var taskType = 'Workperiod';
var taskName = taskType + ' ' + countDate;
var ident = taskType + '_' + countDate;
var isCompleted = 'true';
var customData = [];
customData.push({name:"Id",value:ident});
customData.push({name:"id",value:wpId});
customData.push({name:"isOpen",value:isOpen});
customData.push({name:"dateStart",value:dateStart});
customData.push({name:"dateEnd",value:dateEnd});
customData.push({name:"descStart",value:descStart});
customData.push({name:"descEnd",value:descEnd});
var counts = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName, customData));
return 0;
}
// starts (adds) Exchange Rate Task
function openXR(currencyName,countDate) {
// create the Tasks
openDate = getWorkPeriodData('start');
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
currencyName = typeof(currencyName)==='undefined' || currencyName=='' ? foreignCurrencyName : currencyName;
var SQL = "SELECT [Id],[Name],[ExchangeRate],[InverseExchangeRate],[Rounding],[CurrencySymbol] FROM [ForeignCurrencies] WHERE [Name]='"+currencyName+"'";
var res = sql.Exec(SQL);
var xrdata = res[0].split(',');
var rateName = xrdata[1];
var rate = xrdata[2];
var rateInv = 1/rate;
var rateIsInverted = xrdata[3];
var taskType = 'Exchange Rate';
var taskName = taskType + ' ' + currencyName + ' ' + countDate;
var ident = taskType + '_' + currencyName + '_' + countDate;
isCompleted = 'false';
customData = [];
customData.push({name:"Id",value:ident});
customData.push({name:"rateName",value:rateName});
customData.push({name:"rate",value:rate});
customData.push({name:"rateInv",value:rateInv});
customData.push({name:"rateIsInverted",value:rateIsInverted});
customData.push({name:"rateDate",value:countDate});
var xr = gqlEXEC(addTasks([taskType],[taskName],isCompleted,customData,userName));
return 0;
}
// ends (completes) Exchange Rate Task
function closeXR(currencyName,countDate) {
// Complete the Task
openDate = getWorkPeriodData('start');
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
currencyName = typeof(currencyName)==='undefined' || currencyName=='' ? foreignCurrencyName : currencyName;
var SQL = "SELECT [Id],[Name],[ExchangeRate],[InverseExchangeRate],[Rounding],[CurrencySymbol] FROM [ForeignCurrencies] WHERE [Name]='"+currencyName+"'";
var res = sql.Exec(SQL);
var xrdata = res[0].split(',');
var rateName = xrdata[1];
var rate = xrdata[2];
var rateInv = 1/rate;
var rateIsInverted = xrdata[3];
var taskType = 'Exchange Rate';
var taskName = taskType + ' ' + currencyName + ' ' + countDate;
var ident = taskType + '_' + currencyName + '_' + countDate;
isCompleted = 'true';
customData = [];
customData.push({name:"Id",value:ident});
customData.push({name:"rateName",value:rateName});
customData.push({name:"rate",value:rate});
customData.push({name:"rateInv",value:rateInv});
customData.push({name:"rateIsInverted",value:rateIsInverted});
customData.push({name:"rateDate",value:countDate});
var xr = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName, customData));
return 0;
}
// updates Exchange Rate Task
function setXR(currencyName,countDate) {
// Update the Task
openDate = getWorkPeriodData('start');
countDate = typeof(countDate)==='undefined' || countDate=='' ? openDate : countDate;
currencyName = typeof(currencyName)==='undefined' || currencyName=='' ? foreignCurrencyName : currencyName;
var SQL = "SELECT [Id],[Name],[ExchangeRate],[InverseExchangeRate],[Rounding],[CurrencySymbol] FROM [ForeignCurrencies] WHERE [Name]='"+currencyName+"'";
var res = sql.Exec(SQL);
var xrdata = res[0].split(',');
var rateName = xrdata[1];
var rate = xrdata[2];
var rateInv = 1/rate;
var rateIsInverted = xrdata[3];
var taskType = 'Exchange Rate';
var taskName = taskType + ' ' + currencyName + ' ' + countDate;
var ident = taskType + '_' + currencyName + '_' + countDate;
isCompleted = '';
customData = [];
customData.push({name:"Id",value:ident});
customData.push({name:"rateName",value:rateName});
customData.push({name:"rate",value:rate});
customData.push({name:"rateInv",value:rateInv});
customData.push({name:"rateInv",value:rateInv});
customData.push({name:"rateIsInverted",value:rateIsInverted});
customData.push({name:"rateDate",value:countDate});
var xr = gqlEXEC(updateTaskByIdentifier([taskType], [ident], isCompleted, taskName, customData));
return 0;
}
function throwError(func,taskType,taskName,taskIdent,parm,halt) {
func = typeof(func)==='undefined' || func=='' ? 'unknown' : func;
taskType = typeof(taskType)==='undefined' || taskType=='' ? 'unknown' : taskType;
taskName = typeof(taskName)==='undefined' || taskName=='' ? 'unknown' : taskName;
taskIdent = typeof(taskIdent)==='undefined' || taskIdent=='' ? 'unknown' : taskIdent;
parm = typeof(parm)==='undefined' || parm=='' ? 'unknown' : parm;
halt = typeof(halt)==='undefined' || halt=='' ? true : halt;
var errmsg = 'ERROR in '+func;
errmsg += "\r\n"+'taskType: '+taskType;
errmsg += "\r\n"+'taskName: '+taskName;
errmsg += "\r\n"+'taskIdent: '+taskIdent;
errmsg += "\r\n"+'parm: '+parm;
errmsg += "\r\n"+'halt: '+halt;
errmsg += '';
dlg.AskQuestion(errmsg,"Ok");
}
// main GraphQL Execute Function
function gqlEXEC(query, callback) {
//return query;
var data = gql.Exec(query); // returns JSON as String
var dobj = JSON.parse(data); // converts JSON String to Object
return data;
};
// Query for addTasks
function addTasks(taskTypes,taskNames,isCompleted,customData,userName,content,state) {
var q = '';
q+= 'mutation m{';
for (var t=0; t<taskNames.length; t++) {
var taskType = taskTypes[t];
var taskName = taskNames[t];
q += 'm'+t+': ';
q+= 'addTask(';
q+= 'task:{';
q+= 'taskType:"'+taskType+'"';
q+= ',name:"'+taskName+'"';
q+= ',isCompleted:'+isCompleted;
q+= ',userName:"'+userName+'"';
q+= typeof(content)==='undefined' ? '' : ',content:' + '"'+content+'"';
q+= typeof(state)==='undefined' ? '' : ',state:' + '"'+state+'"';
q+= ',customData:[';
if (customData) {
for (var d=0; d<customData.length; d++) {
q+= (d==0 ? '' : ',');
q+= '{name:"'+customData[d].name+'",value:"'+customData[d].value+'"}';
}
}
q+= ']';
q+= '}';
q+= ')';
q+= '{id,name,identifier,content,isCompleted,userName,customData{name,value}}';
q += ((t+1) != taskNames.length ? ', ' : '');
}
q+= '}';
return q;
};
// Query for getTasks
function getTasks(taskType, completedFilter, nameLike, startFilter, endFilter, contentLike, fieldFilter, stateFilter, callback) {
var q = '';
q+= '{tasks:getTasks(';
q+= 'taskType:"'+taskType+'"';
q+= (startFilter ? ',startDate:"'+startFilter+'"' : '');
q+= (endFilter ? ',endDate:"'+endFilter+'"' : '');
q+= (completedFilter!='' ? ',isCompleted:'+completedFilter : '');
q+= (nameLike ? ',nameLike:"'+nameLike+'"' : '');
q+= (contentLike ? ',contentLike:"'+contentLike+'"' : '');
q+= (stateFilter ? ',state:"'+stateFilter+'"' : '');
q+= (fieldFilter ? ',customFields:[{name:"'+fieldFilter.name+'",value:"'+fieldFilter.value+'"}]' : '');
q+= ')';
q+= '{id,isCompleted,identifier,name,state,content,contentText,customData{name,value},stateLog{state,start,end},stateDuration{state,duration},startDate,endDate,userName}';
q+= '}';
return q;
};
// Query for updateTask (by identifier and taskType)
function updateTaskByIdentifier(taskTypes, taskIdents, isCompleted, taskName, customData, content, state){
var q = 'mutation m {';
for (var t=0; t<taskIdents.length; t++) {
var taskIdent = taskIdents[t];
var taskType = taskTypes[t];
q += 'm'+t+': updateTask(';
q += 'identifier:"'+taskIdent+'"';
q += ', taskType:"'+taskType+'"';
q += ', task:{';
q += 'taskType:"'+taskType+'"';
q += (isCompleted!='' ? ',isCompleted:'+isCompleted : '');
q += (taskName ? ', name:"'+taskName+'"' : '');
q += content ? ',content:"'+content+'"' : '';
q += state ? ',state:"'+state+'"' : '';
q += ',customData:[';
if (customData) {
for (var d=0; d<customData.length; d++) {
q+= (d==0 ? '' : ',');
q+= '{name:"'+customData[d].name+'",value:"'+customData[d].value+'"}';
}
}
q+= ']';
q += '}';
q += ')';
q+= '{id,isCompleted,identifier,name,state,content,contentText,customData{name,value},stateLog{state,start,end},stateDuration{state,duration},startDate,endDate,userName}';
//q += '}';
q += ((t+1) != taskIdents.length ? ', ' : '');
}
q += '}';
return q;
};
