· 8 years ago · Jun 29, 2018, 10:30 PM
1import json
2import sqlite3
3import inspect
4import re, os, shutil
5import pandas as pd
6
7def defaultconfig():
8 #create default config.json file
9 with open('config.json', 'w') as config:
10 config_data = {
11 "path_to_database": "FUDB/FOLLOWUP.DB",
12 "path_to_frontend": "FUDB/",
13 "path_to_excels_exported_from_database": "excels exported/",
14 "path_to_excels_to_be_imported_in_database": "excels to be imported/",
15 "path_to_batches_unassigned": "DC BATCHES IN WORK/1 UNASSIGNED/",
16 "path_to_batches_assigned": "DC BATCHES IN WORK/2 ASSIGNED/",
17 "path_to_batches_tobechecked": "DC BATCHES IN WORK/3 TO BE CHECKED/",
18 "path_to_batches_tbimported": "DC BATCHES IN WORK/4 TO BE IMPORTED/",
19 "path_to_batches_finnished": "DC BATCHES IN WORK/5 FINISHED/",
20 "path_to_batches_rejected": "DC BATCHES IN WORK/6 REJECTED/",
21 "path_to_batches_instandby": "DC BATCHES IN WORK/7 IN STANDBY/",
22 "path_to_batches_unrecordable": "DC BATCHES IN WORK/8 UNRECORDABLE/",
23 "batch_status_options_responsible": "TO BE CHECKED, ONGOING, FILE PREPARATION, SPLIT FILE, FE MACRO PROCESSING, ISAIM IMPORTATION",
24 "batch_status_options_proofreader": "TO BE IMPORTED, FINISHED, REJECTED, STANDBY, UNRECORDABLE, WAITING FOR RESPONSIBLE, CHECKING BY PROOFREADER",
25 "batch_status_options_overall": "ONGOING, STANDBY, FINISHED",
26 "aircraft": "A300, A300-600, A310, A320, A330, A340, A350, A380",
27 "split_batch_factor": "2, 3, 4, 5, 6, 7, 8, 9",
28 "generateBigID": "NO",
29 "generateCustomID": "YES",
30 "customIDlentgh": "6"
31 }
32 #Write to file
33 json.dump(config_data, config)
34
35
36def get_jsonfilespath():
37 #create a session json file on each call and a config.json file if not found
38 try:
39 __location__ = os.path.realpath(os.path.join(os.getcwd(), os.path.dirname(__file__)))
40 configfilepath = open(os.path.join(__location__, 'config.json'))
41 with open('session.json', 'w') as sessionfile:
42 sessionfilepath = open(os.path.join(__location__, sessionfile.name))
43 return configfilepath.name, sessionfilepath.name
44 except:
45 try:
46 defaultconfig() #create default config.json file if not found
47 time.sleep(2) #wait for the file to be created
48 configfilepath = open(os.path.join(__location__, 'config.json'))
49 with open('session.json', 'w') as sessionfile:
50 sessionfilepath = open(os.path.join(__location__, sessionfile.name))
51 return configfilepath.name, sessionfilepath.name
52 except:
53 return False, False
54
55
56def readjson(filepath):
57 #Return a dict form a json file
58 with open(filepath) as j:
59 adict = json.load(j)
60 return adict
61
62
63def configInfo():
64 #Get db path from dbpath.json file
65 configfilepath, sessionfilepath = 'config.json', 'session.json'
66 try:
67 return readjson(configfilepath)
68 except:
69 try:
70 configfilepath, sessionfilepath = get_jsonfilespath()
71 return readjson(configfilepath)
72 except Exception as e:
73 print("config.json file not found! creating config default Got: ",e)
74 time.sleep(3)
75 return configInfo()
76 return False
77
78
79
80
81def user_session(user_working, user_password, user_rights):
82 #Write current user data session
83 configfilepath, sessionfilepath = 'config.json', 'session.json'
84 try:
85 with open(sessionfilepath, "w") as session:
86 current_user_working = user_working
87 current_user_password = user_password
88 current_user_rights = user_rights
89 user_session_data = {"current_user_working": current_user_working,
90 "current_user_password": current_user_password,
91 "current_user_rights": current_user_rights}
92 #Write to file
93 json.dump(user_session_data, session)
94 except:
95 False
96
97
98def sessionInfo():
99 #Get curent user info from session.json file
100 configfilepath, sessionfilepath = 'config.json', 'session.json'
101 try:
102 return readjson(sessionfilepath)
103 except:
104 configfilepath, sessionfilepath = get_jsonfilespath()
105 return readjson(sessionfilepath)
106
107
108def appSettings():
109 configfilepath, sessionfilepath = 'config.json', 'session.json'
110 try:
111 return readjson(configfilepath)
112 except:
113 configfilepath, sessionfilepath = get_jsonfilespath()
114 return readjson(configfilepath)
115
116
117config = configInfo()
118session = sessionInfo()
119
120def connection():
121 #Connect to a db and if it not exists creates one with the name given
122 dbNamePath = config["path_to_database"]
123 try:
124 connection = sqlite3.connect(dbNamePath)
125 #cursor = connection.cursor()
126 return connection
127 except:
128 return False
129
130
131def execute_query(query, keepConn=False):
132 #Execute query, commit and close query
133 try:#execute query in database
134 conn = connection()
135 if conn == False:
136 print("Connection to db failed")
137 return False #if conn fails
138 else:
139 with conn:
140 conn.execute(query)
141 if keepConn:
142 return True
143 else:
144 conn.close()
145 return True
146 except Exception as e:#query was not executed, Try again
147 print("Got error: ",e)
148 return False
149
150
151#Query for creating the followup table in db
152sql_create_table_followup = """CREATE TABLE IF NOT EXISTS `followup` (`BatchID` TEXT,
153 `Aircraft` TEXT,
154 `Operator` TEXT,
155 `OriginalFilesName` TEXT,
156 `OriginalFilesPath` TEXT,
157 `FilesID` TEXT,
158 `AddedDate` TEXT,
159 `Responsible` TEXT,
160 `Proofreader` TEXT,
161 `ResponsibleStatus` TEXT,
162 `ProofreaderStatus` TEXT,
163 `ResponsibleComment` TEXT,
164 `ProofreaderComment` TEXT,
165 `OverallStatus` TEXT,
166 `TotalRowsNbr` TEXT,
167 `MPDTaskRowsNbr` TEXT,
168 `OperatorRowsNbr` TEXT,
169 `FindingsRowsNbr` TEXT,
170 `ChangesLog` TEXT,
171 `ImportedDateISAIM` TEXT);
172 """
173#used for creation of user table
174sql_create_table_users = """CREATE TABLE IF NOT EXISTS `users` (`UserEmail` TEXT, `UserPassword` TEXT, `UserRights` TEXT, `DefaultProofreader` TEXT, PRIMARY KEY(`UserEmail`));"""
175sql_user_first_use = """INSERT INTO `users`(`UserEmail`, `UserPassword`, `UserRights`, `DefaultProofreader`) VALUES ('{}','{}','{}','{}');""".format('admin@admin.admin', 'admin', 'admin', 'admin@admin.admin')
176sql_delete_default_admin = """ DELETE FROM users WHERE UserEmail='admin@admin.admin' """
177
178#Query for inserting an user
179sql_insert_user = """INSERT INTO `users`(`UserEmail`, `UserPassword`, `UserRights`, `DefaultProofreader`) VALUES ('{}','{}','{}','{}');"""
180
181
182
183def generateCustomID(lencustomID):
184 #Generate a random series of chars upper/lower + numbers
185 import string, random
186 upper = list(string.ascii_uppercase)
187 lower = list(string.ascii_lowercase)
188 numbers = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9']
189 allcharli = upper + lower + numbers
190 customIDli = []
191 for char in range(lencustomID):
192 customIDli.append(random.choice(allcharli))
193 customID = ''.join(customIDli)
194 return customID
195
196
197def generateID():
198 import uuid
199 from datetime import datetime
200 genbigID = config["generateBigID"].strip().upper()
201 gencustomID = config["generateCustomID"].strip().upper()
202 lencustomID = int(config["customIDlentgh"].strip())
203 #print(genbigID, gencustomID, lencustomID)
204 if genbigID == "YES":
205 bigID = datetime.now().strftime('%Y-%m-%d-%H-%M') +'-'+ str(uuid.uuid4())
206 return bigID
207 elif gencustomID == "YES":
208 return generateCustomID(lencustomID)
209 else:
210 return generateCustomID(6)
211
212
213def listifyString(astring, delimiter=','):
214 #get a list from a string
215 strListifyed = astring.split(delimiter)
216 astringListifyed = [s.strip() for s in strListifyed]
217 return astringListifyed
218
219
220
221def getfilespath_from(root_path):
222 #Walk thru a start path and return a list of paths to files
223 import os
224 allfiles = []
225 for root, dirs, files in os.walk(root_path):
226 for file in files:
227 path_tofile = os.path.join(root, file)
228 allfiles.append(path_tofile)
229 return allfiles
230
231
232def current_date():
233 #Get current date in day-month-year format
234 from datetime import datetime
235 date = datetime.now().strftime('%d-%m-%Y')
236 return date
237
238
239def copytree(src, dst, symlinks=False, ignore=None):
240 #Copy dirs and it's items from src to dst
241 import os, shutil
242 if not os.path.exists(dst):
243 os.makedirs(dst)
244 for item in os.listdir(src):
245 s = os.path.join(src, item)
246 d = os.path.join(dst, item)
247 if os.path.isdir(s):
248 copytree(s, d, symlinks, ignore)
249 else:
250 if not os.path.exists(d) or os.stat(s).st_mtime - os.stat(d).st_mtime > 1:
251 shutil.copy2(s, d)
252
253
254
255def prepcell(cell, tolist=False):
256 #Remove whitespaces/new line from the string and put words to list if needed
257 cell = str(cell).strip().replace('\n', ' ') # using replace to avoid POST\n156424 > POST156424
258 cellli = ''.join(cell).split(' ')
259 cellli = [c.strip() for c in cellli if len(c) != 0]
260
261 if tolist:
262 return cellli
263 else:
264 cellstr = ' '.join(cellli)
265 return cellstr
266
267
268def stringifyDF(df):
269 #Clean cells and make all columns astype string
270 columnsli = df.columns.tolist()
271 for col in columnsli:
272 df[col] = df[col].apply(lambda cell: prepcell(cell))
273 return df
274
275
276def validate(args, paramdict):
277 #check if not empty return a dict {idx:val}
278 validateddict = {}
279 for arg in args:
280 if len(paramdict[arg]) != 0:
281 validateddict[arg] = paramdict[arg]
282
283 return validateddict
284
285
286
287def dcsinfo(dcspath):
288 from lxml import etree
289 file = open(dcspath)
290 tree = etree.parse(file)
291
292 sumAll = tree.xpath('//sum')
293 totalRows = sum([int(s.text) for s in sumAll])
294
295 sumMpd = tree.xpath('//mpdTask//sum')
296 mpdtask = sum([int(s.text) for s in sumMpd])
297
298 sumOp = tree.xpath('//opeTask//sum')
299 optask = sum([int(s.text) for s in sumOp])
300
301 sumFindings = tree.xpath("//finding[@activated='true']//sum")
302 findings = sum([int(s.text) for s in sumFindings])
303
304 infodcs = {"totalrows": totalRows,
305 "mpdrows": mpdtask,
306 "operatorrows": optask,
307 "findingsrows": findings
308 }
309
310 return infodcs
311
312
313
314
315
316def create_table_followup():
317 return execute_query(sql_create_table_followup)
318
319
320
321def get_dftable(table_name):
322 #gets the table from the db
323 conn = connection()
324 query = "SELECT * FROM {}".format(table_name)
325 df = pd.read_sql_query(query, conn)
326 return df
327
328
329def create_table_users():
330 try:
331 execute_query(sql_create_table_users)
332 user_df = get_dftable('users')
333 rows, cols = user_df.shape
334 if rows == 0:
335 return execute_query(sql_user_first_use)
336 elif rows > 1:
337 return execute_query(sql_delete_default_admin)
338 return True
339 except:
340 return False
341
342
343def get_usersdict(listallusers=False):
344 user_dict = get_dftable('users').to_dict('list')
345 if listallusers == True:
346 return user_dict['UserEmail']
347 else:
348 return user_dict
349
350
351def check_user(username, password):
352 usersdict = get_usersdict()
353
354 for i, email in enumerate(usersdict['UserEmail']):
355 try:
356 user = email.split('@')[0]
357 except:
358 pass
359 if username == user or username == email:
360 password_db = usersdict['UserPassword'][i]
361 if password_db == password:
362 rights = usersdict['UserRights'][i]
363 user_session(email, password_db, rights)
364 return True
365 else:
366 user_session('NoEmail', 'NoPass', 'NoRights')
367 return False
368 else:
369 user_session('NoEmail', 'NoPass', 'NoRights')
370 return False
371
372
373def users_data():
374 #Get from the users table all the proofreaders
375 usersdf = get_dftable('users')
376
377 usersProofs = usersdf[usersdf['UserRights'] == 'proofreader']
378 proofslist = usersProofs['UserEmail'].tolist()
379 context = {'proofreaderList': proofslist,
380 'UserEmail': usersdf['UserEmail'].tolist(),
381 'UserPassword': usersdf['UserPassword'].tolist(),
382 'UserRights': usersdf['UserRights'].tolist(),
383 'DefaultProofreader': usersdf['DefaultProofreader'].tolist()
384 }
385 return context
386
387
388
389def add_user(useremail, userpassword, user_right, defaultProofreader):
390 if len(defaultProofreader) == 0:
391 defaultProofreader = "UNASSIGNED"
392 insert_user = sql_insert_user.format(useremail, userpassword, user_right, defaultProofreader)
393 return execute_query(insert_user)
394
395
396def update_tbcell(table_name, coltoUpdate, colValtoUpdate, colID, rowID):
397
398 update_batch = """UPDATE "{}" SET "{}"="{}" WHERE "{}"="{}";""".format(table_name, coltoUpdate, colValtoUpdate, colID, rowID)
399 if execute_query(update_batch) == True:
400 return True
401 else:
402 return False
403
404
405def update_user(userEmail, coltoUpdate, colValtoUpdate):
406 return update_tbcell('users', coltoUpdate, colValtoUpdate, 'UserEmail', userEmail)
407
408
409def modify_user(UserEmail, UserPassword, UserRights, DefaultProofreader):
410 frame = inspect.currentframe()
411 args, _, _, paramdict = inspect.getargvalues(frame)
412 validated = validate(args, paramdict)
413 for col, val in validated.items():
414 if col == 'UserEmail': continue
415 if update_user(validated['UserEmail'], col, val):
416 pass
417 else:
418 return False
419
420 return True
421
422
423def remove_user(UserEmail):
424 delete_user = """DELETE FROM users WHERE UserEmail='{}' """.format(UserEmail)
425 return execute_query(delete_user)
426
427
428def viewBatches():
429 df_dict = get_dftable('followup').to_dict('list')
430 return df_dict
431
432
433
434def updateBatchOptions():
435 #get batch update options depending on the user type (user/responsible, admin or proofreader)
436 update_options_responsible = listifyString(config['batch_status_options_responsible'])
437 update_options_proofreader = listifyString(config['batch_status_options_proofreader'])
438 update_options_overall = listifyString(config['batch_status_options_overall'])
439 aircraft = listifyString(config['aircraft'])
440 split_batch_factor = listifyString(config['split_batch_factor'])
441 allusers = get_usersdict(True)
442
443 current_user_rights = session['current_user_rights']
444
445 update_batch_dict = {"responsibleStatus": update_options_responsible,
446 "proofreaderStatus": update_options_proofreader,
447 "overallStatus": update_options_overall,
448 "aircraft": aircraft,
449 "splitBatch": split_batch_factor,
450 "allusers": allusers,
451 "disableCommentResponsible": '',
452 "disableCommentProofreader": '',
453 "disableCheckbox": ''
454 }
455
456 if current_user_rights == 'user':
457 update_batch_dict["proofreaderStatus"] = ['You cannot change this']
458 update_batch_dict["allusers"] = ['You cannot change this']
459 update_batch_dict["overallStatus"] = ['You cannot change this']
460 update_batch_dict["disableCommentProofreader"] = "disabled"
461 update_batch_dict["disableCheckbox"] = "disabled"
462 return update_batch_dict
463 elif current_user_rights == 'admin' or current_user_rights == 'proofreader':
464 update_batch_dict["disableCommentResponsible"] = "disabled"
465 return update_batch_dict
466
467
468
469
470def tb_cols_placeholder(tableName):
471 #Get the column names and make placeholders for them
472 df = get_dftable(tableName)
473 colsname = tuple(df.columns.tolist())
474 phli = []
475 for n in range(len(colsname)):
476 phli.append('{}')
477
478 emptyplaceholders = tuple(phli)
479 colsphdict = {"columns": colsname, "placeholders": emptyplaceholders}
480
481 return colsphdict
482
483
484
485
486def sql_insertDict(tableName, infoaddDict):
487 columns = tuple(infoaddDict.keys())
488 values = tuple(infoaddDict.values())
489
490 sql_insert = """INSERT INTO {} {} VALUES {};"""
491 insert = sql_insert.format(tableName, columns, values)
492
493 return execute_query(insert)
494
495
496
497def addBatch(infoaddDict):
498 infoaddDict['Responsible'] = 'UNASSIGNED'
499 infoaddDict['Proofreader'] = 'UNASSIGNED'
500 infoaddDict['ResponsibleStatus'] = 'UNASSIGNED'
501 infoaddDict['ProofreaderStatus'] = 'UNASSIGNED'
502 infoaddDict['OverallStatus'] = 'UNASSIGNED'
503 return sql_insertDict('followup', infoaddDict)
504
505
506def getChangesLog(batchID):
507 #get change log for batch ID as a list
508 df = get_dftable('followup')
509 df_batch = df[df['BatchID'] == batchID]
510 return df_batch['ChangesLog'].tolist()
511
512
513
514def getUnassignedBatch(batchID, usertype):
515 #Select an UNASSIGNED batch from the followup by batchID if found or
516 #return first met UNASSIGNED batch
517 df = get_dftable('followup')
518 df_unassigned = df[df[usertype] == 'UNASSIGNED']
519 df_batch = df_unassigned[df_unassigned['BatchID'] == batchID]
520 try:
521 if df_batch.shape[0] == 0:
522 df_batchUnassigned = df[df[usertype] == 'UNASSIGNED'].head(1)
523 selected_Unassigned = df_batchUnassigned['BatchID'].tolist()[0]
524 return selected_Unassigned
525 else:
526 selected_BatchID = df_batch['BatchID'].tolist()[0]
527 return selected_BatchID
528 except Exception as e:
529 #print("\n\ngetUnassignedBatch error: {}, \n df {}\n\n".format(e, df_batch.shape[0]))
530 return False
531
532
533
534def checkAssignStatus():
535 df = get_dftable('followup')
536
537 df_assignedUser = df[df['ResponsibleStatus'] == 'ASSIGNED']
538 df_assignedProof = df[df['ProofreaderStatus'] == 'ASSIGNED']
539 assignedBatchesUser = df_assignedUser['BatchID'].tolist()
540 assignedBatchesProof = df_assignedProof['BatchID'].tolist()
541 communliBatch = list(set(assignedBatchesUser).intersection(assignedBatchesProof))
542 return communliBatch
543
544
545
546def checkOverallStatus():
547 #if responsible and proofreader status are ASSIGNED set OverallStatus to ASSIGNED
548 setOverallBatchToAssignedli = checkAssignStatus()
549 update_followup_overallstatus = """UPDATE followup SET OverallStatus="{}" WHERE BatchID="{}";"""
550 if len(setOverallBatchToAssignedli) != 0:
551 for batch in setOverallBatchToAssignedli:
552 if execute_query(update_followup_overallstatus.format("ASSIGNED", batch)) == True:
553 pass
554 else:
555 return False
556 return True
557 else:
558 return True
559
560
561
562def createAssignedDirFiles(unassignedBatch):
563 user = session['current_user_working']
564 if re.search('@', user):
565 user = user.split('@')[0]
566
567 dir_unassigned = config['path_to_batches_unassigned']
568 dir_assigned = config['path_to_batches_assigned']
569 dir_frontend = config['path_to_frontend']
570 dir_feli = os.listdir(dir_frontend)
571 dir_feli = [f for f in dir_feli if re.search('.xlsm', f)]
572 dir_feFile = [f for f in dir_feli if not re.search('BETA', f.upper())]
573 dir_bidli = os.listdir(dir_unassigned)
574
575 try:
576 for biddir in dir_bidli:
577 if re.search(unassignedBatch, biddir):
578 #Get the unassigned and assigned folders paths
579 opfile_dirunassigned = os.path.join(dir_unassigned, biddir)
580 opfile_dirassigned = os.path.join(dir_assigned, str(user+'-'+biddir))
581 #Make a new directory in the Assigned folder
582 os.mkdir(opfile_dirassigned)
583 #Copy the FE macro to the folder created
584 fepathfile = os.path.join(dir_frontend, dir_feFile[0])
585 shutil.copy2(fepathfile, opfile_dirassigned)
586 #Create also here the OP FILE folder and copy here the files from unassigned
587 opfilepath = os.path.join(opfile_dirassigned, 'OP FILE')
588 os.mkdir(opfilepath)
589 org_filesli = getfilespath_from(opfile_dirunassigned)
590 org_files = [f for f in org_filesli if not re.search('Thumbs.db', f)]
591 for file in org_files:
592 shutil.copy2(file, opfilepath)
593
594 #Rename the FE macro
595 filesinassigned = os.listdir(opfile_dirassigned)
596 fenameold = [f for f in filesinassigned if re.search('.xlsm', f)][0]
597 fefileold = os.path.join(opfile_dirassigned, fenameold)
598 fenamenew = unassignedBatch+'-'+fenameold
599 fefilenew = os.path.join(opfile_dirassigned, fenamenew)
600 os.rename(fefileold, fefilenew)
601
602 return True
603
604 except Exception as e:
605 print("GOT: ", e)
606 return False
607
608
609
610def updatedict_sq(tableName, updatedict, colIDName):
611 colsvals = []
612 for col, val in updatedict.items():
613 if col != colIDName:
614 sqval = col + '=' + "'{}'".format(val)
615 colsvals.append(sqval)
616 else:
617 whereCol_value = col + '=' + "'{}'".format(val)
618
619 colstoUpdate = ', '.join(colsvals)
620 q = str("UPDATE " + tableName + " SET " + colstoUpdate + " WHERE " + whereCol_value + ";")
621
622 return execute_query(q)
623
624
625
626
627def assignBatchtoUser(batchID, assignedtoProofreader):
628
629 if checkOverallStatus() == True:
630
631 date = current_date()
632 userinfo = sessionInfo()
633 responsible_user = userinfo["current_user_working"]
634
635 tableName = 'followup'
636
637 updatedict = {"BatchID": batchID,
638 "Responsible": responsible_user,
639 "ResponsibleStatus": "ASSIGNED",
640 "Proofreader": responsible_user,
641 "ProofreaderStatus": "ASSIGNED"
642 }
643
644 colIDName = "BatchID"
645
646 if assignedtoProofreader == True:
647 unassignedBatch = getUnassignedBatch(batchID, 'ProofreaderStatus')
648 updatedict.pop("Responsible", None)
649 updatedict.pop("ResponsibleStatus", None)
650 changeLoglist = getChangesLog(unassignedBatch)
651 loginfo = "ASSIGNED to {} on {}".format(responsible_user, date)
652 updatedict["ChangesLog"] = loginfo
653 if updatedict_sq(tableName, updatedict, colIDName) == True:
654 print('TRUE updatedict_sq')
655 checkOverallStatus()
656 createAssignedDirFiles(unassignedBatch)
657 return True
658
659
660 elif assignedtoProofreader == False:
661 unassignedBatch = getUnassignedBatch(batchID, 'ResponsibleStatus')
662 updatedict.pop("Proofreader", None)
663 updatedict.pop("ProofreaderStatus", None)
664 changeLoglist = getChangesLog(unassignedBatch)
665 loginfo = "ASSIGNED to {} on {}".format(responsible_user, date)
666 updatedict["ChangesLog"] = loginfo
667 if updatedict_sq(tableName, updatedict, colIDName) == True:
668 print('FALSE updatedict_sq')
669 checkOverallStatus()
670 createAssignedDirFiles(unassignedBatch)
671 return True
672 else:
673 return False