Skip to content

Instantly share code, notes, and snippets.

@Kutikov

Kutikov/FramakoEconomy.EXCEL.yaml

Last active October 4, 2020 12:09
Show Gist options
  • Save Kutikov/b36f01426167a0071aec1c2f794813f7 to your computer and use it in GitHub Desktop.
Save Kutikov/b36f01426167a0071aec1c2f794813f7 to your computer and use it in GitHub Desktop.
Download ZIP
Dubenko AYe, Kutikov OYe, Sazonov SO, Kutikov DO
Raw
FramakoEconomy.EXCEL.yaml
name: FramakoEconomy
description: 'Dubenko AYe, Kutikov OYe, Sazonov SO, Kutikov DO'
host: EXCEL
api_set: {}
script:
content: |
$("#makeObject").click(() => tryCatch(completeMedicines));
$("#addSheet").click(() => tryCatch(sortByRow));
//region IO
enum ParseType {
NUMBER,
STRING,
BOOLEAN
}
class TransportReader {
sheet: string;
cellAddress: string;
value: any;
mode: ParseType;
constructor(sheet: string, cellAddress: string, x: number, y: number, mode: ParseType) {
this.sheet = sheet;
this.value = null;
this.mode = mode;
if (x != null) {
if (y != null) {
var ordA = "a".charCodeAt(0);
var ordZ = "z".charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while (y >= 0) {
s = String.fromCharCode((y % len) + ordA) + s;
y = Math.floor(y / len) - 1;
}
this.cellAddress = s.toUpperCase() + x.toString();
} else {
this.cellAddress = cellAddress + x.toString();
}
} else {
this.cellAddress = cellAddress;
}
}
}
class TransportWritter {
sheet: string;
cellAddress: string;
value: string;
constructor(sheet: string, cellAddress: string, x: number, y: number, value: string) {
this.sheet = sheet;
this.value = value;
if (x != null) {
if (y != null) {
var ordA = "a".charCodeAt(0);
var ordZ = "z".charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while (y >= 0) {
s = String.fromCharCode((y % len) + ordA) + s;
y = Math.floor(y / len) - 1;
}
this.cellAddress = s.toUpperCase() + x.toString();
} else {
this.cellAddress = cellAddress + x.toString();
}
} else {
this.cellAddress = cellAddress;
}
}
}
class TransportReaderRange {
sheet: string;
rangeAddress: string;
value: string[][];
constructor(
sheet: string,
cellAddress1: string,
cellAddress2: string,
x1: number,
x2: number,
y1: number,
y2: number
) {
this.sheet = sheet;
this.value = [];
if (x1 != null) {
if (y1 != null) {
var ordA = "a".charCodeAt(0);
var ordZ = "z".charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while (y1 >= 0) {
s = String.fromCharCode((y1 % len) + ordA) + s;
y1 = Math.floor(y1 / len) - 1;
}
var len2 = ordZ - ordA + 1;
var s2 = "";
while (y2 >= 0) {
s2 = String.fromCharCode((y2 % len2) + ordA) + s2;
y2 = Math.floor(y2 / len2) - 1;
}
this.rangeAddress = s.toUpperCase() + x1.toString() + ":" + s2.toUpperCase() + x2.toString();
} else {
this.rangeAddress = cellAddress1 + x1.toString() + ":" + cellAddress2 + x2.toString();
}
} else {
this.rangeAddress = cellAddress1 + ":" + cellAddress2;
}
}
}
class TransportWritterRange {
sheet: string;
rangeAddress: string;
value: any[][];
constructor(
sheet: string,
cellAddress1: string,
cellAddress2: string,
x1: number,
x2: number,
y1: number,
y2: number,
value: any[][]
) {
this.sheet = sheet;
this.value = value;
if (x1 != null) {
if (y1 != null) {
var ordA = "a".charCodeAt(0);
var ordZ = "z".charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while (y1 >= 0) {
s = String.fromCharCode((y1 % len) + ordA) + s;
y1 = Math.floor(y1 / len) - 1;
}
var len2 = ordZ - ordA + 1;
var s2 = "";
while (y2 >= 0) {
s2 = String.fromCharCode((y2 % len2) + ordA) + s2;
y2 = Math.floor(y2 / len2) - 1;
}
this.rangeAddress = s.toUpperCase() + x1.toString() + ":" + s2.toUpperCase() + x2.toString();
} else {
this.rangeAddress = cellAddress1 + x1.toString() + ":" + cellAddress2 + x2.toString();
}
} else {
this.rangeAddress = cellAddress1 + ":" + cellAddress2;
}
}
}
async function readCells(transport: TransportReader[], context) {
for (let i = 0; i < transport.length; i++) {
let cell = context.workbook.worksheets.getItem(transport[i].sheet).getRange(transport[i].cellAddress);
cell.load("values");
await context.sync();
if (cell.values[0][0] !== "") {
switch (transport[i].mode) {
case ParseType.STRING:
transport[i].value = cell.values[0][0];
break;
case ParseType.NUMBER:
transport[i].value = parseFloat(cell.values[0][0]);
break;
case ParseType.BOOLEAN:
transport[i].value = parseInt(cell.values[0][0], 10) === 1;
break;
}
}
}
return transport;
}
async function readRanges(transport: TransportReaderRange[], context) {
for (let i = 0; i < transport.length; i++) {
let cell = context.workbook.worksheets.getItem(transport[i].sheet).getRange(transport[i].rangeAddress);
cell.load("values");
await context.sync();
transport[i].value = cell.values;
}
return transport;
}
async function writeCells(transport: TransportWritter[], context) {
for (let i = 0; i < transport.length; i++) {
let cell = context.workbook.worksheets.getItem(transport[i].sheet).getRange(transport[i].cellAddress);
if (transport[i].value !== NaN.toString()) {
cell.values = [[transport[i].value]];
} else {
cell.values = [[0]];
}
await context.sync();
}
return "ok";
}
async function writeRanges(transport: TransportWritterRange[], context) {
for (let i = 0; i < transport.length; i++) {
let range = context.workbook.worksheets.getItem(transport[i].sheet).getRange(transport[i].rangeAddress);
range.values = transport[i].value;
await context.sync();
}
return "ok";
}
function tryCatch(callback) {
Promise.resolve()
.then(callback)
.catch(function(error) {
console.error(error);
});
}
//endregion
class Medicine {
name: string;
row: number;
costByMg: number;
minimalDosage: number;
maximumDosage: number;
minimalCourse: number;
maximumCourse: number;
isPEP: boolean;
isPsych: boolean;
isSymptomatical: boolean;
isProfilactic: boolean;
isComorbid: boolean;
etosuksemid: boolean;
karbamazepin: boolean;
valproate: boolean;
benzoPheno: boolean;
fenitoin: boolean;
lamotrigin: boolean;
clonazepin: boolean;
topiramat: boolean;
levetiracetam: boolean;
oskarbazin: boolean;
prebabalin: boolean;
occursHospital: number = 0;
occuresRecomendations: number = 0;
meanDosageRecomendations: number = 0;
costedRecomendation: number = 0;
constructor(name: string, row: number) {
this.name = name;
this.row = row;
}
async readData(context) {
let transport: TransportReader[] = [];
let row = this.row + 4;
transport.push(new TransportReader("MedicineInfo", "B", row, null, ParseType.NUMBER));
transport.push(new TransportReader("MedicineInfo", "C", row, null, ParseType.NUMBER));
transport.push(new TransportReader("MedicineInfo", "D", row, null, ParseType.NUMBER));
transport.push(new TransportReader("MedicineInfo", "E", row, null, ParseType.NUMBER));
transport.push(new TransportReader("MedicineInfo", "F", row, null, ParseType.NUMBER));
transport.push(new TransportReader("MedicineInfo", "G", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "H", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "I", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "J", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "K", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "L", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "M", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "N", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "O", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "P", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "Q", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "R", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "S", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "T", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "U", row, null, ParseType.BOOLEAN));
transport.push(new TransportReader("MedicineInfo", "V", row, null, ParseType.BOOLEAN));
let val = await readCells(transport, context);
this.costByMg = transport[0].value;
this.minimalDosage = transport[1].value;
this.maximumDosage = transport[2].value;
this.minimalCourse = transport[3].value;
this.maximumCourse = transport[4].value;
this.isPEP = transport[5].value;
this.isPsych = transport[6].value;
this.isSymptomatical = transport[7].value;
this.isProfilactic = transport[8].value;
this.isComorbid = transport[9].value;
this.etosuksemid = transport[10].value;
this.karbamazepin = transport[11].value;
this.valproate = transport[12].value;
this.benzoPheno = transport[13].value;
this.fenitoin = transport[14].value;
this.lamotrigin = transport[15].value;
this.clonazepin = transport[16].value;
this.topiramat = transport[17].value;
this.levetiracetam = transport[18].value;
this.oskarbazin = transport[19].value;
this.prebabalin = transport[20].value;
}
static checkUpMedicine(medicines: Medicine[], primaryMedicineDatas: PrimaryMedicineData[]): void {
for (let i = 0; i < medicines.length; i++) {
for (let j = 0; j < primaryMedicineDatas.length; j++) {
if (primaryMedicineDatas[j].hospitalMedicine.indexOf(medicines[i].name) > -1) {
medicines[i].occursHospital++;
primaryMedicineDatas[j].allCountH++;
let minimalDays = Math.min(medicines[i].minimalCourse, primaryMedicineDatas[j].daysMax);
let maximalDays = Math.min(medicines[i].maximumCourse, primaryMedicineDatas[j].daysMax);
let minimalCost = minimalDays * medicines[i].minimalDosage * medicines[i].costByMg;
let maximalCost = maximalDays * medicines[i].maximumDosage * medicines[i].costByMg;
primaryMedicineDatas[j].allCostMinH = primaryMedicineDatas[j].allCostMinH + minimalCost;
primaryMedicineDatas[j].allCostMaxNumber = primaryMedicineDatas[j].allCostMaxNumber + maximalCost;
//region groups
if (medicines[i].isPEP) {
primaryMedicineDatas[j].pepCountH++;
primaryMedicineDatas[j].pepCostMinH = primaryMedicineDatas[j].pepCostMinH + minimalCost;
primaryMedicineDatas[j].pepCostMaxNumber =
primaryMedicineDatas[j].pepCostMaxNumber + maximalCost;
}
if (medicines[i].isPsych) {
primaryMedicineDatas[j].psychCountH++;
primaryMedicineDatas[j].psychCostMinH = primaryMedicineDatas[j].psychCostMinH + minimalCost;
primaryMedicineDatas[j].psychCostMaxNumber =
primaryMedicineDatas[j].psychCostMaxNumber + maximalCost;
}
if (medicines[i].isSymptomatical) {
primaryMedicineDatas[j].symptCountH++;
primaryMedicineDatas[j].symptCostMinH = primaryMedicineDatas[j].symptCostMinH + minimalCost;
primaryMedicineDatas[j].symptCostMaxNumber =
primaryMedicineDatas[j].symptCostMaxNumber + maximalCost;
}
if (medicines[i].isProfilactic) {
primaryMedicineDatas[j].profCountH++;
primaryMedicineDatas[j].profCostMinH = primaryMedicineDatas[j].profCostMinH + minimalCost;
primaryMedicineDatas[j].profCostMaxNumber =
primaryMedicineDatas[j].profCostMaxNumber + maximalCost;
}
if (medicines[i].isComorbid) {
primaryMedicineDatas[j].comorbidCountH++;
primaryMedicineDatas[j].comorbidCostMinH =
primaryMedicineDatas[j].comorbidCostMinH + minimalCost;
primaryMedicineDatas[j].comorbidCostMaxNumber =
primaryMedicineDatas[j].comorbidCostMaxNumber + maximalCost;
}
//endregion
//region PEPs
if (medicines[i].etosuksemid) {
primaryMedicineDatas[j].pepCountH++;
primaryMedicineDatas[j].pepCostMinH = primaryMedicineDatas[j].pepCostMinH + minimalCost;
primaryMedicineDatas[j].pepCostMaxNumber =
primaryMedicineDatas[j].pepCostMaxNumber + maximalCost;
}
if (medicines[i].karbamazepin) {
primaryMedicineDatas[j].karbamazepinCountH++;
primaryMedicineDatas[j].karbamazepinCostMinH =
primaryMedicineDatas[j].karbamazepinCostMinH + minimalCost;
primaryMedicineDatas[j].karbamazepinCostMaxNumber =
primaryMedicineDatas[j].karbamazepinCostMaxNumber + maximalCost;
}
if (medicines[i].valproate) {
primaryMedicineDatas[j].valproateCountH++;
primaryMedicineDatas[j].valproateCostMinH =
primaryMedicineDatas[j].valproateCostMinH + minimalCost;
primaryMedicineDatas[j].valproateCostMaxNumber =
primaryMedicineDatas[j].valproateCostMaxNumber + maximalCost;
}
if (medicines[i].benzoPheno) {
primaryMedicineDatas[j].benzoPhenoCountH++;
primaryMedicineDatas[j].benzoPhenoCostMinH =
primaryMedicineDatas[j].benzoPhenoCostMinH + minimalCost;
primaryMedicineDatas[j].benzoPhenoCostMaxNumber =
primaryMedicineDatas[j].benzoPhenoCostMaxNumber + maximalCost;
}
if (medicines[i].fenitoin) {
primaryMedicineDatas[j].fenitoinCountH++;
primaryMedicineDatas[j].fenitoinCostMinH =
primaryMedicineDatas[j].fenitoinCostMinH + minimalCost;
primaryMedicineDatas[j].fenitoinCostMaxNumber =
primaryMedicineDatas[j].fenitoinCostMaxNumber + maximalCost;
}
if (medicines[i].lamotrigin) {
primaryMedicineDatas[j].lamotriginCountH++;
primaryMedicineDatas[j].lamotriginCostMinH =
primaryMedicineDatas[j].lamotriginCostMinH + minimalCost;
primaryMedicineDatas[j].lamotriginCostMaxNumber =
primaryMedicineDatas[j].lamotriginCostMaxNumber + maximalCost;
}
if (medicines[i].clonazepin) {
primaryMedicineDatas[j].clonazepinCountH++;
primaryMedicineDatas[j].clonazepinCostMinH =
primaryMedicineDatas[j].clonazepinCostMinH + minimalCost;
primaryMedicineDatas[j].clonazepinCostMaxNumber =
primaryMedicineDatas[j].clonazepinCostMaxNumber + maximalCost;
}
if (medicines[i].topiramat) {
primaryMedicineDatas[j].topiramatCountH++;
primaryMedicineDatas[j].topiramatCostMinH =
primaryMedicineDatas[j].topiramatCostMinH + minimalCost;
primaryMedicineDatas[j].topiramatCostMaxNumber =
primaryMedicineDatas[j].topiramatCostMaxNumber + maximalCost;
}
if (medicines[i].levetiracetam) {
primaryMedicineDatas[j].levetiracetamCountH++;
primaryMedicineDatas[j].levetiracetamCostMinH =
primaryMedicineDatas[j].levetiracetamCostMinH + minimalCost;
primaryMedicineDatas[j].levetiracetamCostMaxNumber =
primaryMedicineDatas[j].levetiracetamCostMaxNumber + maximalCost;
}
if (medicines[i].oskarbazin) {
primaryMedicineDatas[j].oskarbazinCountH++;
primaryMedicineDatas[j].oskarbazinCostMinH =
primaryMedicineDatas[j].oskarbazinCostMinH + minimalCost;
primaryMedicineDatas[j].oskarbazinCostMaxNumber =
primaryMedicineDatas[j].oskarbazinCostMaxNumber + maximalCost;
}
if (medicines[i].prebabalin) {
primaryMedicineDatas[j].prebabalinCountH++;
primaryMedicineDatas[j].prebabalinCostMinH =
primaryMedicineDatas[j].prebabalinCostMinH + minimalCost;
primaryMedicineDatas[j].prebabalinCostMaxNumber =
primaryMedicineDatas[j].prebabalinCostMaxNumber + maximalCost;
}
//endregion
}
if (primaryMedicineDatas[j].recomendatioMedicine.indexOf(medicines[i].name) > -1) {
medicines[i].occuresRecomendations++;
primaryMedicineDatas[j].allCountR++;
let dosage =
primaryMedicineDatas[j].recomendationalDosage[
primaryMedicineDatas[j].recomendatioMedicine.indexOf(medicines[i].name)
] * 30;
let cost = dosage * medicines[i].costByMg;
medicines[i].meanDosageRecomendations = medicines[i].meanDosageRecomendations + dosage;
primaryMedicineDatas[j].allCostR = primaryMedicineDatas[j].allCostR + cost;
//region groups
if (medicines[i].isPEP) {
primaryMedicineDatas[j].pepCountR++;
primaryMedicineDatas[j].pepCostR = primaryMedicineDatas[j].pepCostR + cost;
}
if (medicines[i].isPsych) {
primaryMedicineDatas[j].psychCountR++;
primaryMedicineDatas[j].psychCostR = primaryMedicineDatas[j].psychCostR + cost;
}
if (medicines[i].isSymptomatical) {
primaryMedicineDatas[j].symptCountR++;
primaryMedicineDatas[j].symptCostR = primaryMedicineDatas[j].symptCostR + cost;
}
if (medicines[i].isProfilactic) {
primaryMedicineDatas[j].profCountR++;
primaryMedicineDatas[j].profCostR = primaryMedicineDatas[j].profCostR + cost;
}
if (medicines[i].isComorbid) {
primaryMedicineDatas[j].comorbidCountR++;
primaryMedicineDatas[j].comorbidCostR = primaryMedicineDatas[j].comorbidCostR + cost;
}
//endregion
//region PEPs
if (medicines[i].etosuksemid) {
primaryMedicineDatas[j].etosuksemidCountR++;
primaryMedicineDatas[j].etosuksemidCostR = primaryMedicineDatas[j].etosuksemidCostR + cost;
}
if (medicines[i].karbamazepin) {
primaryMedicineDatas[j].karbamazepinCountR++;
primaryMedicineDatas[j].karbamazepinCostR = primaryMedicineDatas[j].karbamazepinCostR + cost;
}
if (medicines[i].valproate) {
primaryMedicineDatas[j].valproateCountR++;
primaryMedicineDatas[j].valproateCostR = primaryMedicineDatas[j].valproateCostR + cost;
}
if (medicines[i].benzoPheno) {
primaryMedicineDatas[j].benzoPhenoCountR++;
primaryMedicineDatas[j].benzoPhenoCostR = primaryMedicineDatas[j].benzoPhenoCostR + cost;
}
if (medicines[i].fenitoin) {
primaryMedicineDatas[j].fenitoinCountR++;
primaryMedicineDatas[j].fenitoinCostR = primaryMedicineDatas[j].fenitoinCostR + cost;
}
if (medicines[i].lamotrigin) {
primaryMedicineDatas[j].lamotriginCountR++;
primaryMedicineDatas[j].lamotriginCostR = primaryMedicineDatas[j].lamotriginCostR + cost;
}
if (medicines[i].clonazepin) {
primaryMedicineDatas[j].clonazepinCountR++;
primaryMedicineDatas[j].clonazepinCostR = primaryMedicineDatas[j].clonazepinCostR + cost;
}
if (medicines[i].topiramat) {
primaryMedicineDatas[j].topiramatCountR++;
primaryMedicineDatas[j].topiramatCostR = primaryMedicineDatas[j].topiramatCostR + cost;
}
if (medicines[i].levetiracetam) {
primaryMedicineDatas[j].levetiracetamCountR++;
primaryMedicineDatas[j].levetiracetamCostR = primaryMedicineDatas[j].levetiracetamCostR + cost;
}
if (medicines[i].oskarbazin) {
primaryMedicineDatas[j].oskarbazinCountR++;
primaryMedicineDatas[j].oskarbazinCostR = primaryMedicineDatas[j].oskarbazinCostR + cost;
}
if (medicines[i].prebabalin) {
primaryMedicineDatas[j].prebabalinCountR++;
primaryMedicineDatas[j].prebabalinCostR = primaryMedicineDatas[j].oskarbazinCostR + cost;
}
//endregion
}
}
}
for (let i = 0; i < medicines.length; i++) {
medicines[i].meanDosageRecomendations =
medicines[i].meanDosageRecomendations / medicines[i].occuresRecomendations;
medicines[i].costedRecomendation = medicines[i].meanDosageRecomendations * medicines[i].costByMg;
}
}
async writeData(context) {
let transport: TransportWritter[] = [];
let row = this.row + 4;
transport.push(
new TransportWritter(
"MedicineData",
"B",
row,
null,
this.occursHospital.toString()));
transport.push(
new TransportWritter(
"MedicineData",
"C",
row,
null,
(this.minimalDosage * this.minimalCourse).toString()));
transport.push(
new TransportWritter(
"MedicineData",
"D",
row,
null,
(this.maximumDosage * this.maximumCourse).toString()));
transport.push(
new TransportWritter(
"MedicineData",
"E",
row,
null,
(this.minimalDosage * this.minimalCourse * this.costByMg).toString()));
transport.push(
new TransportWritter(
"MedicineData",
"F",
row,
null,
(this.maximumDosage * this.maximumCourse * this.costByMg).toString()));
transport.push(
new TransportWritter(
"MedicineData",
"G",
row,
null,
this.occuresRecomendations.toString()));
transport.push(
new TransportWritter(
"MedicineData",
"H",
row,
null,
this.meanDosageRecomendations.toString()));
transport.push(
new TransportWritter(
"MedicineData",
"I",
row,
null,
this.costedRecomendation.toString()));
let val = await writeCells(transport, context);
}
}
class PrimaryMedicineData {
id: number;
daysMax: number;
row: number;
hospitalMedicine: string[] = [];
recomendatioMedicine: string[] = [];
recomendationalDosage: number[] = [];
allCountH: number = 0;
allCostMinH: number = 0;
allCostMaxNumber: number = 0;
pepCountH: number = 0;
pepCostMinH: number = 0;
pepCostMaxNumber: number = 0;
psychCountH: number = 0;
psychCostMinH: number = 0;
psychCostMaxNumber: number = 0;
symptCountH: number = 0;
symptCostMinH: number = 0;
symptCostMaxNumber: number = 0;
profCountH: number = 0;
profCostMinH: number = 0;
profCostMaxNumber: number = 0;
comorbidCountH: number = 0;
comorbidCostMinH: number = 0;
comorbidCostMaxNumber: number = 0;
etosuksemidCountH: number = 0;
etosuksemidCostMinH: number = 0;
etosuksemidCostMaxNumber: number = 0;
karbamazepinCountH: number = 0;
karbamazepinCostMinH: number = 0;
karbamazepinCostMaxNumber: number = 0;
valproateCountH: number = 0;
valproateCostMinH: number = 0;
valproateCostMaxNumber: number = 0;
benzoPhenoCountH: number = 0;
benzoPhenoCostMinH: number = 0;
benzoPhenoCostMaxNumber: number = 0;
fenitoinCountH: number = 0;
fenitoinCostMinH: number = 0;
fenitoinCostMaxNumber: number = 0;
lamotriginCountH: number = 0;
lamotriginCostMinH: number = 0;
lamotriginCostMaxNumber: number = 0;
clonazepinCountH: number = 0;
clonazepinCostMinH: number = 0;
clonazepinCostMaxNumber: number = 0;
topiramatCountH: number = 0;
topiramatCostMinH: number = 0;
topiramatCostMaxNumber: number = 0;
levetiracetamCountH: number = 0;
levetiracetamCostMinH: number = 0;
levetiracetamCostMaxNumber: number = 0;
oskarbazinCountH: number = 0;
oskarbazinCostMinH: number = 0;
oskarbazinCostMaxNumber: number = 0;
prebabalinCountH: number = 0;
prebabalinCostMinH: number = 0;
prebabalinCostMaxNumber: number = 0;
allCountR: number = 0;
allCostR: number = 0;
pepCountR: number = 0;
pepCostR: number = 0;
psychCountR: number = 0;
psychCostR: number = 0;
symptCountR: number = 0;
symptCostR: number = 0;
profCountR: number = 0;
profCostR: number = 0;
comorbidCountR: number = 0;
comorbidCostR: number = 0;
etosuksemidCountR: number = 0;
etosuksemidCostR: number = 0;
karbamazepinCountR: number = 0;
karbamazepinCostR: number = 0;
valproateCountR: number = 0;
valproateCostR: number = 0;
benzoPhenoCountR: number = 0;
benzoPhenoCostR: number = 0;
fenitoinCountR: number = 0;
fenitoinCostR: number = 0;
lamotriginCountR: number = 0;
lamotriginCostR: number = 0;
clonazepinCountR: number = 0;
clonazepinCostR: number = 0;
topiramatCountR: number = 0;
topiramatCostR: number = 0;
levetiracetamCountR: number = 0;
levetiracetamCostR: number = 0;
oskarbazinCountR: number = 0;
oskarbazinCostR: number = 0;
prebabalinCountR: number = 0;
prebabalinCostR: number = 0;
constructor(id: number, row: number, daysMax: number) {
this.id = id;
this.row = row;
this.daysMax = daysMax;
}
async readData(context) {
let transport = [];
let transport2 = [];
let transport3 = [];
let hospital = [
"AY",
"AZ",
"BA",
"BB",
"BC",
"BD",
"BE",
"BF",
"BG",
"BH",
"BI",
"BJ",
"BK",
"BL",
"BM",
"BN",
"BO",
"BP"
];
let recomend = ["BQ", "BS", "BU", "BW", "BY", "CA", "CC"];
let recomendDoses = ["BR", "BT", "BV", "BX", "BZ", "CB", "CD"];
for (let i = 0; i < hospital.length; i++) {
transport.push(new TransportReader("Data", hospital[i], this.row + 4, null, ParseType.STRING));
}
for (let i = 0; i < recomend.length; i++) {
transport2.push(new TransportReader("Data", recomend[i], this.row + 4, null, ParseType.STRING));
transport3.push(new TransportReader("Data", recomendDoses[i], this.row + 4, null, ParseType.NUMBER));
}
let val = await readCells(transport, context);
let val2 = await readCells(transport2, context);
let val3 = await readCells(transport3, context);
for (let i = 0; i < hospital.length; i++) {
if (transport[i].value !== null) {
this.hospitalMedicine.push(transport[i].value);
}
}
for (let i = 0; i < recomend.length; i++) {
if (transport2[i].value !== null) {
this.recomendatioMedicine.push(transport2[i].value);
this.recomendationalDosage.push(transport3[i].value);
}
}
}
async writeData(context) {
let transport: TransportWritterRange[] = [];
let row = this.row + 4;
let rowValues: string[] = [];
//region groups
rowValues.push(this.allCountH.toString());
rowValues.push(this.allCostMinH.toString());
rowValues.push(this.allCostMaxNumber.toString());
rowValues.push(this.pepCountH.toString());
rowValues.push(this.pepCostMinH.toString());
rowValues.push(this.pepCostMaxNumber.toString());
rowValues.push(this.psychCountH.toString());
rowValues.push(this.psychCostMinH.toString());
rowValues.push(this.psychCostMaxNumber.toString());
rowValues.push(this.symptCountH.toString());
rowValues.push(this.symptCostMinH.toString());
rowValues.push(this.symptCostMaxNumber.toString());
rowValues.push(this.profCountH.toString());
rowValues.push(this.profCostMinH.toString());
rowValues.push(this.profCostMaxNumber.toString());
rowValues.push(this.comorbidCountH.toString());
rowValues.push(this.comorbidCostMinH.toString());
rowValues.push(this.comorbidCostMaxNumber.toString());
//endregion
//region PEPs
rowValues.push(this.etosuksemidCountH.toString());
rowValues.push(this.etosuksemidCostMinH.toString());
rowValues.push(this.etosuksemidCostMaxNumber.toString());
rowValues.push(this.karbamazepinCountH.toString());
rowValues.push(this.karbamazepinCostMinH.toString());
rowValues.push(this.karbamazepinCostMaxNumber.toString());
rowValues.push(this.valproateCountH.toString());
rowValues.push(this.valproateCostMinH.toString());
rowValues.push(this.valproateCostMaxNumber.toString());
rowValues.push(this.benzoPhenoCountH.toString());
rowValues.push(this.benzoPhenoCostMinH.toString());
rowValues.push(this.benzoPhenoCostMaxNumber.toString());
rowValues.push(this.fenitoinCountH.toString());
rowValues.push(this.fenitoinCostMinH.toString());
rowValues.push(this.fenitoinCostMaxNumber.toString());
rowValues.push(this.lamotriginCountH.toString());
rowValues.push(this.lamotriginCostMinH.toString());
rowValues.push(this.lamotriginCostMaxNumber.toString());
rowValues.push(this.clonazepinCountH.toString());
rowValues.push(this.clonazepinCostMinH.toString());
rowValues.push(this.clonazepinCostMaxNumber.toString());
rowValues.push(this.topiramatCountH.toString());
rowValues.push(this.topiramatCostMinH.toString());
rowValues.push(this.topiramatCostMaxNumber.toString());
rowValues.push(this.levetiracetamCountH.toString());
rowValues.push(this.levetiracetamCostMinH.toString());
rowValues.push(this.levetiracetamCostMaxNumber.toString());
rowValues.push(this.oskarbazinCountH.toString());
rowValues.push(this.oskarbazinCostMinH.toString());
rowValues.push(this.oskarbazinCostMaxNumber.toString());
rowValues.push(this.prebabalinCountH.toString());
rowValues.push(this.prebabalinCostMinH.toString());
rowValues.push(this.prebabalinCostMaxNumber.toString());
//endregion
let rowValuesArr: string[][] = [];
rowValuesArr.push(rowValues);
transport.push(new TransportWritterRange("CollectedData", "AV", "CT", row, row, null, null, rowValuesArr));
rowValues = [];
//region groups R
rowValues.push(this.allCountR.toString());
rowValues.push(this.allCostR.toString());
rowValues.push(this.pepCountR.toString());
rowValues.push(this.pepCostR.toString());
rowValues.push(this.psychCountR.toString());
rowValues.push(this.psychCostR.toString());
rowValues.push(this.symptCountR.toString());
rowValues.push(this.symptCostR.toString());
rowValues.push(this.profCountR.toString());
rowValues.push(this.profCostR.toString());
rowValues.push(this.comorbidCountR.toString());
rowValues.push(this.comorbidCostR.toString());
//endregion
//region PEPs R
rowValues.push(this.etosuksemidCountR.toString());
rowValues.push(this.etosuksemidCostR.toString());
rowValues.push(this.karbamazepinCountR.toString());
rowValues.push(this.karbamazepinCostR.toString());
rowValues.push(this.valproateCountR.toString());
rowValues.push(this.valproateCostR.toString());
rowValues.push(this.benzoPhenoCountR.toString());
rowValues.push(this.benzoPhenoCostR.toString());
rowValues.push(this.fenitoinCountR.toString());
rowValues.push(this.fenitoinCostR.toString());
rowValues.push(this.lamotriginCountR.toString());
rowValues.push(this.lamotriginCostR.toString());
rowValues.push(this.clonazepinCountR.toString());
rowValues.push(this.clonazepinCostR.toString());
rowValues.push(this.topiramatCountR.toString());
rowValues.push(this.topiramatCostR.toString());
rowValues.push(this.levetiracetamCountR.toString());
rowValues.push(this.levetiracetamCostR.toString());
rowValues.push(this.oskarbazinCountR.toString());
rowValues.push(this.oskarbazinCostR.toString());
rowValues.push(this.prebabalinCountR.toString());
rowValues.push(this.prebabalinCostR.toString());
//endregion
rowValuesArr = [];
rowValuesArr.push(rowValues);
transport.push(new TransportWritterRange("CollectedData", "CU", "EB", row, row, null, null, rowValuesArr));
let val = await writeRanges(transport, context);
}
}
function completeMedicines() {
return Excel.run(function(context) {
return context.sync().then(async function() {
let medicines: Medicine[] = [];
let primaryMedicineDatas: PrimaryMedicineData[] = [];
let transportMedicine: TransportReader[] = [];
let transportPrimaryData: TransportReader[] = [];
for (let i = 0; i < 166; i++) {
transportPrimaryData.push(new TransportReader("Data", "A", i + 4, null, ParseType.NUMBER));
transportPrimaryData.push(new TransportReader("CollectedData", "D", i + 4, null, ParseType.NUMBER));
}
for (let i = 0; i < 183; i++) {
transportMedicine.push(new TransportReader("MedicineData", "A", i + 4, null, ParseType.STRING));
}
let val = await readCells(transportMedicine, context);
let val2 = await readCells(transportPrimaryData, context);
for (let i = 0; i < 183; i++) {
let medicine: Medicine = new Medicine(transportMedicine[i].value, i);
await medicine.readData(context);
medicines.push(medicine);
}
console.log("readed medicines");
for (let i = 0; i < transportPrimaryData.length; i = i + 2) {
let primaryMedicineData: PrimaryMedicineData = new PrimaryMedicineData(
transportPrimaryData[i].value,
i / 2,
transportPrimaryData[i + 1].value
);
await primaryMedicineData.readData(context);
primaryMedicineDatas.push(primaryMedicineData);
}
console.log("readed patients");
Medicine.checkUpMedicine(medicines, primaryMedicineDatas);
console.log("counted!");
for (let i = 0; i < medicines.length; i++) {
await medicines[i].writeData(context);
}
console.log("writed medicines");
for (let i = 0; i < primaryMedicineDatas.length; i++) {
await primaryMedicineDatas[i].writeData(context);
}
console.log("writed patients");
});
});
}
function sortByRow() {
return Excel.run(function(context) {
return context.sync().then(async function() {
let sheet: Excel.Worksheet = await getSheetByName(context);
let name: string = $("#sheetName")
.val()
.toString();
let source: Excel.Worksheet = context.workbook.worksheets.getItem("CollectedData");
let nativeVariants: string[] = [];
let transportReader: TransportReader[] = [];
let column = $("#columnName")
.val()
.toString();
for (let i = 0; i < 166; i++) {
transportReader.push(new TransportReader("CollectedData", column, i + 4, null, ParseType.STRING));
}
let val = await readCells(transportReader, context);
for (let i = 0; i < transportReader.length; i++) {
nativeVariants.push(transportReader[i].value);
}
let uniqueArray: string[] = nativeVariants.filter(function(item, pos) {
return nativeVariants.indexOf(item) == pos;
});
console.log("uniqu array ready");
let row: number = uniqueArray.length * 10 + 4;
if (column != "A"){
for (let i = 0; i < uniqueArray.length; i++) {
if (uniqueArray[i] != null) {
row++;
let transportWritterRange: TransportWritterRange[] = [];
let valuesToAnalize: string[][] = [];
let startRow = row;
for (let j = 0; j < nativeVariants.length; j++) {
if (nativeVariants[j] === uniqueArray[i]) {
let transportReaderRange: TransportReaderRange[] = [];
transportReaderRange.push(
new TransportReaderRange("CollectedData", "A", "GA", j + 4, j + 4, null, null)
);
let val = await readRanges(transportReaderRange, context);
transportWritterRange.push(
new TransportWritterRange(
name,
"A",
"GA",
row,
row,
null,
null,
transportReaderRange[0].value
)
);
valuesToAnalize.push(transportReaderRange[0].value[0]);
row++;
}
}
let val2 = await writeRanges(transportWritterRange, context);
let val3 = await groupAnalize(valuesToAnalize, startRow, i * 10 + 4, name, context);
let val4 = await writeRanges(val3, context);
}
}
}
else{
row++;
let transportWritterRange: TransportWritterRange[] = [];
let valuesToAnalize: string[][] = [];
let startRow = row;
for (let j = 0; j < nativeVariants.length; j++) {
let transportReaderRange: TransportReaderRange[] = [];
transportReaderRange.push(
new TransportReaderRange("CollectedData", "A", "GA", j + 4, j + 4, null, null)
);
let val = await readRanges(transportReaderRange, context);
transportWritterRange.push(
new TransportWritterRange(
name,
"A",
"GA",
row,
row,
null,
null,
transportReaderRange[0].value
)
);
valuesToAnalize.push(transportReaderRange[0].value[0]);
row++;
}
let val2 = await writeRanges(transportWritterRange, context);
let val3 = await groupAnalize(valuesToAnalize, startRow, 0 * 10 + 4, name, context);
let val4 = await writeRanges(val3, context);
}
});
});
}
function getSheetByName(context): Promise<Excel.Worksheet> {
return Excel.run(function(context) {
let name: string = $("#sheetName")
.val()
.toString();
console.log("seek for " + name);
let sheet: Excel.Worksheet = context.workbook.worksheets.getItem(name);
sheet.getRange("A4:GA169").delete(Excel.DeleteShiftDirection.up);
return context.sync().then(function() {
return sheet;
});
}).catch(function() {
return Excel.run(function(context) {
let name: string = $("#sheetName")
.val()
.toString();
return context.sync().then(async function() {
console.log("not found " + name);
let sourceSheet = context.workbook.worksheets.getItem("CollectedData");
let copied = sourceSheet.copy(Excel.WorksheetPositionType.end);
await context.sync();
copied.name = name;
await context.sync;
copied.getRange("A4:GA169").delete(Excel.DeleteShiftDirection.up);
await context.sync;
let sheet: Excel.Worksheet = context.workbook.worksheets.getItem(name);
return sheet;
});
});
});
}
async function groupAnalize(
values: string[][],
dataStarts: number,
charsStarts: number,
worksheet: string,
context
): Promise<TransportWritterRange[]> {
let transportWritterRange: TransportWritterRange[] = [];
let categorial: string[] = [
"B",
"F",
"H",
"K",
"AD",
"AI",
"AM",
"AT",
"AU",
"T",
"U",
"V",
"W",
"X",
"Y",
"AJ",
"AK",
"AL"
];
let normalized: string[] = ["C", "D", "L", "M", "N", "O", "P", "Q", "R", "S"];
let valuesToWrite: any[][] = [];
let maxCount = 165 + 18;
let allNormalized: number = 49;
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
valuesToWrite.push(new Array<string>(maxCount));
for (let i = 0; i < maxCount; i++) {
if (categorial.indexOf(intToColumn(i)) > -1) {
for (let group = 0; group < valuesToWrite.length; group++) {
let buffer: number = 0;
for (let entry = 0; entry < values.length; entry++) {
if (values[entry][i] == group.toString()) {
buffer++;
}
}
valuesToWrite[group][i] = (buffer / values.length).toString();
}
} else if (intToColumn(i) == "E"){
let antroTypes: string[] = ["Атланто-балтійський", "Беломоро - балтійський", "Динарський", "Средземноморський", "Палеоевропейський", "Арменоїдний", "Уралоїдний", "Альпійський","Індо - афганський"];
for (let group = 0; group < valuesToWrite.length; group++) {
let buffer: number = 0;
let all: number = 0;
for (let entry = 0; entry < values.length; entry++) {
if (values[entry][i] == antroTypes[group]) {
buffer++;
all++;
}
else if (antroTypes.indexOf(values[entry][i]) > -1){
all++;
}
}
valuesToWrite[group][i] = (buffer / all).toString();
}
} else if (normalized.indexOf(intToColumn(i)) > -1 || i >= allNormalized) {
let range = context.workbook.worksheets
.getItem(worksheet)
.getRange(
intToColumn(i) +
dataStarts.toString() +
":" +
intToColumn(i) +
(dataStarts + values.length).toString()
);
let mean = context.workbook.functions.average(range);
let dispersia = context.workbook.functions.var_S(range);
let sdtDer = context.workbook.functions.stDev_S(range);
let confidence = context.workbook.functions.confidence_T(
0.05,
context.workbook.functions.stDev_S(range),
values.length
);
let skew = context.workbook.functions.skew(range);
let excess = context.workbook.functions.kurt(range);
mean.load("value");
dispersia.load("value");
sdtDer.load("value");
confidence.load("value");
skew.load("value");
excess.load("value");
await context.sync();
valuesToWrite[0][i] = mean.value;
valuesToWrite[1][i] = dispersia.value;
valuesToWrite[2][i] = sdtDer.value;
valuesToWrite[3][i] = sdtDer.value / Math.sqrt(values.length);
valuesToWrite[4][i] = confidence.value;
valuesToWrite[5][i] = skew.value;
valuesToWrite[6][i] = excess.value;
}
}
transportWritterRange.push(
new TransportWritterRange(
worksheet,
"A",
intToColumn(maxCount - 1),
charsStarts,
charsStarts + 8,
null,
null,
valuesToWrite
)
);
console.log(transportWritterRange[0].rangeAddress);
return transportWritterRange;
}
function intToColumn(y: number): string {
var ordA = "a".charCodeAt(0);
var ordZ = "z".charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while (y >= 0) {
s = String.fromCharCode((y % len) + ordA) + s;
y = Math.floor(y / len) - 1;
}
return s.toUpperCase();
}
function tryF() {
return Excel.run(function(context) {
return context.sync().then(function() {
let transport = [];
transport.push(new TransportReader("Data", "A1", null, null, ParseType.NUMBER));
transport.push(new TransportReader("Data", "A2", null, null, ParseType.BOOLEAN));
transport.push(new TransportReader("Data", "A", 3, null, ParseType.BOOLEAN));
transport.push(new TransportReader("Data", "A", 4, null, ParseType.STRING));
transport.push(new TransportReader("Data", null, 5, 0, ParseType.NUMBER));
transport.push(new TransportReader("Data", "A6", null, null, ParseType.STRING));
let val = readCells(transport, context);
val.then(function(result) {
let objectO = {};
console.log(JSON.stringify(result));
let wr = [];
wr.push(new TransportWritter("Data", "A11", null, null, JSON.stringify(objectO)));
let write = writeCells(wr, context);
write.then(function(result) {
console.log(result);
});
});
});
});
}
language: typescript
template:
content: "<section class=\"samples ms-font-m\">\n\t<h3>Epilepsy farmakoeconomy</h3>\n\t<p class=\"ms-font-m\" style=\"justify-content:space-between;\">Рассчет препаратови идет по всем препаратам с листа\n\t\tПрепаратыИнформация и первичных данных. Рассчитывается количество раз назначения, средняя доза и цена препарата;\n\t\tцена принимаемых препаратов и появления их по группам для каждого пациента.</p>\n\t<button id=\"makeObject\" class=\"ms-Button\">\n\t <span class=\"ms-Button-label\">Count medicine</span>\n\t</button>\n\t<p class=\"ms-font-m\">Enter sheet name</p>\n\t<input type=\"text\" id=\"sheetName\"/>\n\t<p class=\"ms-font-m\">Enter column letters</p>\n\t<input type=\"text\" id=\"columnName\"/>\n\t<button id=\"addSheet\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Make sheet</span>\n\t</button>\n</section>"
language: html
style:
content: |-
section.samples {
justify-content: space-between;
margin-top: 20px;
}
p {
justify-content: space-between;
}
section.samples .ms-Button, section.setup .ms-Button {
display: block;
margin-bottom: 5px;
margin-left: 20px;
min-width: 80px;
}
language: css
libraries: |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js
@types/office-js
[email protected]/dist/css/fabric.min.css
[email protected]/dist/css/fabric.components.min.css
[email protected]/client/core.min.js
@types/core-js
[email protected]
@types/[email protected]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment