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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
/** freq.js
*
* this script extracts products' order-frequency
* -----------------------------------------------------------------------------
*
* Contents:
* #1 Requirements
* #2 Personalized constants and parametres
* #3 Supporting functions
* #4 Output functions
* #5 Entry-point function main()
*/
// #1
// REQUIREMENTS
////////////////////////////////////////////////////////////////////////////////
var mysql = require('mysql');
const fs = require('fs');
const os = require('os');
const {Storage} = require('@google-cloud/storage'); // import Google Cloud client library
// #2
// SETUP PERSONALIZED CONSTANTS AND PARAMETRES
////////////////////////////////////////////////////////////////////////////////
// google-storage parametres
// --- -- -- - - -
const projectId = 'pythia-251711';
// const keyFilename = '../auth/pythia-251711-047e3d5e6608.json';
// temp file of frequencies (json)
const temp_file = os.tmpdir() + '/freq.json';
// db connection parametres
// (keep the one that suits your environment; comment out the other)
// -----------------------------------------------------------------------------
// while on Google Cloud Functions
var con = mysql.createConnection({ // ** check
socketPath: "/cloudsql/pythia-251711:europe-west4:pythia-db-eu",
user: "pythia_services",
password: process.env.DB_PASSWORD,
database: "pythia_db"
});
// MAIN OUTPUT OF THE SCRIPT
// arrays that need to be constructed and filled with data
// -----------------------------------------------------------------------------
/** array of product frequencies
* array of obj: { id: , fq: }
* @var id (int): product's eys code
* @var fq (int): (order) frerquency
*/
var freq_ = [];
// #3
// SUPPORTING FUNCTIONS
////////////////////////////////////////////////////////////////////////////////
/** extract frequencies
*
* @param obj (array): array of products
*/
function extract_freq(obj) {
obj.forEach( rec => {
var description = rec.product_description;
var fq = rec.FREQuency;
var pid = rec.eys_code;
freq_.push({
fq: fq,
id: pid
});
});
}
// #4
// OUTPUT FUNCTIONS
////////////////////////////////////////////////////////////////////////////////
// Save Local
// -----------------------------------------------------------------------------
function save_local(jsonArray, fileName) {
let fileStr = JSON.stringify(jsonArray); // convert json to string
// write string to file
fs.writeFileSync(fileName, fileStr, 'utf8', (err) => {
if (err) {
console.log("An error occured while writing keywords.json");
return console.log(err);
}
console.log("JSON file has been saved.");
});
return true;
}
// Save to Google Cloud Storage
// -----------------------------------------------------------------------------
async function upload_file( bucketName, srcFilePath, trgFilePath ) {
// Creates a client
const storage = new Storage({projectId, keyFilename});
try {
await storage.bucket(bucketName).upload(srcFilePath, {
destination: trgFilePath,
gzip: true, // serve compressed
metadata: { // cache for 8 hours
cacheControl: 'public, max-age=60' // production set: 28800
}
});
console.log(`${srcFilePath} uploaded to ${bucketName}`);
}
catch(err) {
console.error('ERROR:', err);
}
}
// #5
// MAIN function (exposed function to run the whole proccess)
////////////////////////////////////////////////////////////////////////////////
/** main()
*
* nodejs's exported function
* used as entry-point function (in case of Google Cloud function)
*/
exports.main = () => { // google cloud function entry point
// connect;
// get records to proccess;
// call main proccess function;
// save dictionary;
// end script;
// -----------------------------------------------------------------------------
// 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!')
extract_freq(result); // proccess
console.log('Keywords proccesed!')
save_local(freq_, temp_file); // save frequencies
upload_file('pythia-files', temp_file, 'uploads/json/freq.json')
.then( () => {
console.log('Results saved! Exiting...');
process.exit(1);
});
/// // 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
});
}
|
