1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
// requirements
////////////////////////////////////////////////////////////////////////////////
var mysql = require('mysql');
const fs = require('fs');
// ready to get main data to preccess
////////////////////////////////////////////////////////////////////////////////
// db connection parametres
// -----------------------------------------------------------------------------
var con = mysql.createConnection({
host: "127.0.0.1",
user: "pythia_db_user_dev",
password: "VnEP0eysjiXDHcfM",
database: "dev_pythia_db"
});
// products' (order-)frequecy array
// -----------------------------------------------------------------------------
var freq_ = []; //////////////////////////////// MAIN OUTPUT OF THE SCRIPT
var prods_ = [];
// connect;
// get records to proccess;
// call main proccess function;
// save dictionary;
// end script;
// -----------------------------------------------------------------------------
con.connect(function(err) {
// connect;
if (err) throw err;
console.log("Connected!");
// SQL to get needed data
var sql = "SELECT count(pl.eys_code) as FREQuency,\
pl.product_id as product_id,\
pb.brand_name,\
pl.barcode, pl.skl_code, pl.eys_code,\
IF( pd.description IS NOT NULL , pd.description , pl.product_description ) AS product_description,\
pl.bpcs_code,\
pd.image_path\
FROM product_list as pl\
LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code\
LEFT JOIN delivery_orders AS do ON dop.order_id = do.id\
LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code\
LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code\
LEFT JOIN product_brands pb ON pl.brand_id = pb.id\
WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%'\
GROUP BY pl.product_id\
ORDER BY FREQuency DESC\
LIMIT 60000";
// query sql
con.query(sql, function (err, result) {
if (err) throw err;
console.log('Records from database received!')
do_proccess(result); // proccess
console.log('Keywords proccesed!')
save_keywords(); // save
console.log('Results saved! Exiting.')
// echo memory stats
const used = process.memoryUsage();
for (let key in used) {
console.log(`${key} ${Math.round(used[key] / 1024 / 1024 * 100) / 100} MB`);
}
process.exit(1); // exit
});
});
// main proccess
// -----------------------------------------------------------------------------
function do_proccess(obj) {
// console.log(JSON.stringify(obj, null, 2));
var freq = [];
obj.forEach( rec => {
var description = rec.product_description;
var fq = rec.FREQuency;
var pid = rec.eys_code;
prods_.push({
id: pid,
w: description
});
freq_.push({
fq: fq,
id: pid
});
}); // main proccessing finished;
}
// save proccess
// -----------------------------------------------------------------------------
function save_keywords() {
let fqStr = JSON.stringify(freq_);
fs.writeFileSync("../results/freq.json", fqStr, 'utf8', (err) => {
if (err) {
console.log("An error occured while writing keywords.json");
return console.log(err);
}
console.log("JSON file has been saved.");
});
let prodStr = JSON.stringify(prods_);
fs.writeFileSync("../results/prods.json", prodStr, 'utf8', (err) => {
if (err) {
console.log("An error occured while writing keywords.json");
return console.log(err);
}
console.log("JSON file has been saved.");
});
}
|
