# -*- coding: utf-8 -*- import re from datetime import * from hql import hql from ppms.constants import * from ppms import ppms from ppms import processes from ppms import project_rights from ppms import schedule from ppms import ppms_cu from ppms import text_constant from ppms.module_subclasses.schedule_copy import copy_hyperlink class Versionizer: """class - Versionizer: creates report data""" #Define attributes sql_source_pk = ppms.get_query("000309") sql_target_pk = ppms.get_query("000310") sql_target_pk_autonr = ppms.get_query("000311") sql_di_relation = ppms.get_query("000312") sql_find_date = ppms.get_query("000313") sql_find_user = ppms.get_query("000314") """ Constructor: Parameters: source_dt: String - Set source datatable target_dt: String - Set target datatable dict_datatable: String - Consist primary key values of source datatables start_id: String - Set report start id """ def __init__(self, source_dt, target_dt, dict_datatable, main_pr_id, new_report_id=None): #Define and set attributes self.dict_datatable = dict_datatable self.source_dt = source_dt self.target_dt = target_dt self.main_pr_id = main_pr_id self.new_report_id = new_report_id def start(self): """Execute versionizing""" #ppms.ui_message_box("source_dt: " + self.source_dt + ", sql_source_pk: " + self.sql_source_pk) #ppms.ui_message_box("target_dt: " + self.target_dt + ", sql_target_pk: " + self.sql_target_pk) #Get primary key list of source datatable self.list_source_pk = self.sql_statements(self.source_dt, self.sql_source_pk) #Get primary key list of target datatable self.list_target_pk = self.sql_statements(self.target_dt, self.sql_target_pk) #Get acutual date self.actual_date = self.get_ppms_date() #Get next autonumber for new target record self.next() #Insert new target record to target datatable return self.create_insert() def next(self): """Get next report ID""" #Create sql statement to count existing records at target datatable sql_check_target = "SELECT count(*) FROM " + self.target_dt + " WHERE " sql_check_target_where = "" #Find primary key relation source-pk --> target-pk for pk in self.list_source_pk: for pk_relation in self.list_target_pk: try: pk_target = pk_relation[pk_relation.index(pk[0]) - 1] except: pass try: #Get value of primary key pk_value = self.dict_datatable[self.source_dt][pk[0]] #Create where - clause for sql_check_target sql_check_target_where = sql_check_target_where + pk_target + "='" + pk_value + "' AND " except: pass #Delete last AND from where - clause try: sql_check_target_where = sql_check_target_where[0:len(sql_check_target_where) - 5] except: pass #Add where - clause to sql_check_target sql_check_target = sql_check_target + sql_check_target_where #Check if report exist at defined source ID self.data_count = ppms.db_select(sql_check_target)[0][0] #Get autonumber dataitem self.target_pk_autonr = self.sql_statements(self.target_dt, self.sql_target_pk_autonr)[0][0] #Get next report id if (self.new_report_id == None): if (self.data_count > 0): #Create select - statement to get highest value sql_new_report_id = """SELECT """ + self.target_pk_autonr + """ FROM (Select """ + self.target_pk_autonr + """ FROM """ + self.target_dt + """ WHERE """ + \ sql_check_target_where + """ ORDER BY """ + self.target_pk_autonr + """ desc) WHERE rownum = 1""" #Get highest value highest_id = ppms.db_select(sql_new_report_id)[0][0] if highest_id == "-": self.new_report_id = "000001" else: #Use regex to create leading zeros # self.new_report_id = str((int(re.sub("(^0*)", "", highest_id)) + 1)).zfill(len(highest_id)) else: #Create select - statement to get highest value sql_new_report_id = """SELECT """ + self.target_pk_autonr + """ FROM (Select """ + self.target_pk_autonr + """ FROM """ + self.target_dt + """ ORDER BY """ + self.target_pk_autonr + """ desc) WHERE rownum = 1""" #Get value value = ppms.db_select(sql_new_report_id)[0][0] #self.new_report_id = "{number:0>}".format(number=value) #Use binary operator '%' to create leading zeros format = "%0." + str(len(value)) + "d" #Get new report id self.new_report_id = format % 1 def sql_statements(self, datatable, sql_statement): """Create and execute sql statements""" #Check string #ppms.ui_message_box(sql_statement.format(str(datatable[2:5]))) #Replace wild-card through datatable #replace doesn't work: return ppms.db_select(sql_statement.replace("$dt$",datatable)) return ppms.db_select(sql_statement.format(str(datatable[2:5]))) def create_insert(self): """Create insert statement""" #ppms.ui_message_box("create_insert entered") #Get dataitem relation self.relation_list = self.sql_statements(self.target_dt, self.sql_di_relation) if (type(self.source_dt) != list): #Define insert-attribute string targetList = "" sourceList = "" #Add attributes to string for item in self.relation_list: targetList = targetList + item[0] + ", " sourceList = sourceList + item[1] + ", " #Delete last comma targetList = targetList[0:len(targetList) - 2] sourceList = sourceList[0:len(sourceList) - 2] #ppms.ui_message_box("targetList: " + targetList + "\n\n" + "sourceList: " + sourceList) # find di with 'DATE'-constant in it get_di_fields = self.sql_statements(self.target_dt, self.sql_find_date) set_time_insertStatement = "" for items in range(len(get_di_fields)): set_time_insertStatement = set_time_insertStatement + str(get_di_fields[items][0]) + "," set_time_insertStatement = set_time_insertStatement[0:(len(set_time_insertStatement) - 1)] date_insert = "" for items in range(len(get_di_fields)): # date = self.get_ppms_date() # Oracle if ppms.db_get_dbms_name() == "000002": date_insert = date_insert + "sysdate," # MS SQL if ppms.db_get_dbms_name() == "000004": date_insert = date_insert + "GETDATE()," date_insert = date_insert[0:(len(date_insert) - 1)] # # finds di's with the 'USER' constant in it and fill them with the actual user # get_di_fields = self.sql_statements(self.target_dt, self.sql_find_user) set_user_insertStatement = "" for items in range(len(get_di_fields)): set_user_insertStatement = set_user_insertStatement + str(get_di_fields[items][0]) + "," set_user_insertStatement = set_user_insertStatement[0:(len(set_user_insertStatement)) - 1] user_insert = "" user = ppms.uvar_get('@1') for items in range(len(get_di_fields)): user_insert = user_insert + "'" + str(user) + "'," user_insert = user_insert[0:(len(user_insert) - 1)] #ppms.ui_message_box(user_insert) #Create whole insert statement insertStatement = "INSERT INTO " + self.target_dt + " (" + self.target_pk_autonr + ", " + set_time_insertStatement + ", " + set_user_insertStatement + ", " + targetList + ") SELECT '" + self.new_report_id + "', " + str( date_insert) + ", " + str(user_insert) + ", " + sourceList + " FROM " + self.source_dt + " WHERE " #Loop datatable dictionary for dt, pk in self.dict_datatable.items(): #Loop primary key dictionary for di, value in pk.items(): insertStatement = insertStatement + di + " = '" + value + "' AND " #Delete last AND # insertStatement = insertStatement[0:len(insertStatement) - 5] #ppms.ui_message_box(insertStatement) # #Execute insert statement linesInserted = ppms.db_modify(insertStatement) #updating current report, setting cumulated values if self.target_dt == "DT827": aggregateStatement = ppms.get_query("000440").format("0", self.main_pr_id, self.new_report_id) #ppms.ui_message_box(aggregateStatement) if aggregateStatement: success = ppms.db_modify(aggregateStatement) return linesInserted #cumulation should be possible on different levels of aggregation #day #week #month #quarter #year #some construct here #linesInserted def get_ppms_date(self): """ gets actual date """ # # gets DIs and sets date into them # add actual date to insert statement ## today = datetime.today() today = today.timetuple() day = "{text:0>2}".format(text=str(today[2])) month = "{text:0>2}".format(text=str(today[1])) year = str(today[0]) year = year[2:] actual_date = day + "." + month + "." + year return actual_date class Versionizer_helper: """ class: Versionizer_helper: concentrates data for versionizer class parameters: source_dt:list target_dt:list data_items_pk:list copy_project:list """ def __init__(self, source_dt, target_dt, data_items_pk, copy_project, current_data=False, refresh_report_id=""): # # define & set attributes # self.source_dt = source_dt self.target_dt = target_dt self.data_items_pk = data_items_pk self.copy_project = copy_project self.source_dt_buffer = self.source_dt[0] self.current_data = current_data self.refresh_report_id = refresh_report_id self.main_pr_id = ppms.search_record(461, [self.copy_project], [1001, 1052, 1003, 23220, 264, 29448, 41317], True).main_pr_id.get_value() self.dict_source_target = self.create_source_target() self.list_dt_pk_value = self.create_dt_pk_value_list() self.dict_versionizer = self.create_versionizer_dict() self.report_id = self.call_versionizer() def create_source_target(self): """ create_source_target:dict generate the source - target relation dictionary """ dict_source_target_temp = {} for x in range(len(self.source_dt)): dict_source_target_temp.setdefault(self.source_dt[x], self.target_dt[x]) return dict_source_target_temp def create_dt_pk_value_list(self): """ create_dt_pk_value_list:list generates the relation between the datatable and the primary key """ list_dt_pk_value = [] for item in range(len(self.source_dt)): dict_pk_value = {} dict_value = {} dict_value[self.data_items_pk[item]] = self.copy_project dict_pk_value[self.source_dt[item]] = dict_value list_dt_pk_value.append(dict_pk_value) return list_dt_pk_value def create_versionizer_dict(self): """ create_versionizer_dict:dict generates the datastructure for the versionizer class """ dict_versionizer = {} for key_item in self.dict_source_target.keys(): for list_item in self.list_dt_pk_value: try: list_item[key_item] dict_versionizer[key_item] = [self.dict_source_target[key_item], list_item] except: pass return dict_versionizer def call_versionizer(self): """ call_versionizer:boolean method gives the prepared datastructure to the versionizer class """ # # check for main project # if self.refresh_report_id != "": main_rep_id = self.refresh_report_id else: max_nr = ppms.db_select(ppms.get_query("000315").format(self.main_pr_id)) if max_nr[0][0] == "": max_nr = 0 else: max_nr = max_nr[0][0] try: new_nr = int(max_nr) except: new_nr = 0 new_nr += 1 new_report_id = "{0:0>6}".format(new_nr) main_rep_id = str(new_report_id) if self.check_main_pr(self.copy_project) == False: ppms.ui_message_id("0532") return else: # # get children ## childs = self.check_for_childs(self.copy_project) main_pr = self.main_pr_id new_childs = [] for pr_nr in childs: new_childs.extend(self.check_for_childs(pr_nr)) new_childs = childs # # delete duplicates in new_childs list ## new_childs.sort() new_childs_buf = new_childs """ for item in childs: if item in new_childs: new_childs.remove(item) else: pass #childs = new_childs #childs.append(main_pr) """ childs.sort() for element in childs: self.copy_project = element self.list_dt_pk_value = self.create_dt_pk_value_list() self.dict_versionizer = self.create_versionizer_dict() # # insert data into structure ## for item in sorted(self.dict_versionizer.keys()): if item == self.source_dt_buffer: vz = Versionizer(item, self.dict_versionizer[item][0], self.dict_versionizer[item][1], self.main_pr_id, main_rep_id) inserted_rows = vz.start() new_report_id = vz.new_report_id # # check if report id is ok # self.report_id = self.check_report_nr(new_report_id, childs) else: vz = Versionizer(item, self.dict_versionizer[item][0], self.dict_versionizer[item][1], self.main_pr_id, main_rep_id) inserted_rows = vz.start() return self.report_id def check_main_pr(self, pr_nr): """ check_main_pr:boolean checks if given project number is a main project with no pedecessor """ rec461 = ppms.search_record(461, [str(pr_nr)], [1001, 1052, 1003], True) if not rec461: return False if rec461.parent_pr_id.get_value() not in ('', '-'): return False return True def check_for_childs(self, pr_nr): """ check_for_childs:list if pr_nr is a main project, if this is true, it returns all following project numbers in form of a list """ sql_get_childs = ppms.get_query("000317").format(str(self.main_pr_id)) queue = [] sql_select = ppms.db_select(sql_get_childs) for item in sql_select: queue.append(item[0]) return queue def check_report_nr(self, vz_report_id, pr_queue): """ check_report_nr:string if the generated report_nr is lower then the highest report_nr in the db this method correct this issue """ sql_check_reportNr = "SELECT COUNT(*) FROM DT823 WHERE (" for item in pr_queue: sql_check_reportNr = sql_check_reportNr + "DI008413='" + item + "' AND " sql_check_reportNr = sql_check_reportNr[0:len(sql_check_reportNr) - 4] sql_check_reportNr += " AND DI008413='" + self.copy_project + "' AND DI008416='" + vz_report_id + "' AND " sql_check_reportNr = sql_check_reportNr[:-5] sql_check_reportNr += ")" report_id_check = ppms.db_select(sql_check_reportNr) if len(report_id_check) == 0: vz_report_id = int(vz_report_id) vz_report_id += 1 vz_report_id = "{0:0>6}".format(vz_report_id) return str(vz_report_id) else: return vz_report_id def create_report(pr_id, report_type): """Create a report for a given project Possible report_type parameters are found in ppms.constants with the prefix REPORT_CREATION_* Returns either the report id or None """ rec461 = ppms.search_record(461, [pr_id], ['main_pr_id', 'actual_end']) main_pr_id = rec461.main_pr_id.get_value() actual_end = rec461.actual_end.get_value() hyperlink_mapping = (({'datatable': 'Opportunity', 'pr_id': 'pr_id', 'hyperlink': 'linked_file'}, {'datatable': 'ProjectOpportunityReport', 'pr_id': 'pr_id', 'report_id': 'report_id', 'hyperlink': 'linked_file'}), ({'datatable': 'Risk', 'pr_id': 'pr_id', 'hyperlink': 'linked_file'}, {'datatable': 'ProjectRiskReport', 'pr_id': 'pr_id', 'report_id': 'status_id', 'hyperlink': 'linked_file'}), ({'datatable': 'RiskMeasures', 'pr_id': 'pr_id', 'hyperlink': 'linked_file'}, {'datatable': 'RiskMeasuresReport', 'pr_id': 'pr_id', 'report_id': 'status_id', 'hyperlink': 'linked_file'}), ({'datatable': 'TodoItemNote', 'pr_id': 'pr_id', 'hyperlink': 'linked_file'}, {'datatable': 'TodoItemNoteVersion', 'pr_id': 'pr_id', 'report_id': 'status_id', 'hyperlink': 'linked_file'}), ({'datatable': 'TodoItems', 'pr_id': 'pr_id', 'hyperlink': 'linked_file'}, {'datatable': 'TodoItemsReport', 'pr_id': 'pr_id', 'report_id': 'status_id', 'hyperlink': 'linked_file'}), ({'datatable': 'OpportunityMeasures', 'pr_id': 'pr_id', 'hyperlink': 'linked_file'}, {'datatable': 'OpportunMeasuresReport', 'pr_id': 'pr_id', 'report_id': 'status_id', 'hyperlink': 'linked_file'})) rights = project_rights.get_project_rights(pr_id) if rights <= PROJECT_RIGHTS_VIEW: ppms.ui_message_id('0051') return None elif not project_rights.get_project_is_main_project(pr_id): ppms.ui_message_id('0532') return None # Check the type of report that should be created and ask follow up # questions or abort if the conditions are not quite right... # Regardless of report type a "are you sure" message is displayed and the # following code relies on that, because it checks if MSG_REPLY_YES is # in the stack if report_type == REPORT_CREATION_APPLICATION: project_application_exists = ppms.db_select(ppms.get_query("000261").format(pr_id))[0][0] if project_application_exists > 0: ppms.ui_message_id("0790") if ppms.msg_pop().get_reply() == MSG_REPLY_YES: application_report_id = ppms.db_select(ppms.get_query("000262").format(pr_id))[0][0] project_rights.call_project_report(pr_id, application_report_id) return None else: ppms.ui_message_id("0804") if report_type == REPORT_CREATION_REPORT: if actual_end == 0: ppms.ui_message_id("0430") else: report_type = REPORT_CREATION_FINAL_REPORT if report_type == REPORT_CREATION_FINAL_REPORT: if actual_end == 0: ppms.ui_message_id("0669") return None elif actual_end != 0: final_report_exists = ppms.db_select(ppms.get_query("000263").format(pr_id))[0][0] if final_report_exists > 0: ppms.ui_message_id("0679") return None else: ppms.ui_message_id("0494") if report_type == REPORT_CREATION_BASELINE: if actual_end == 0: count_baseline = ppms.db_select(ppms.get_query("000277").format(pr_id)) if len(count_baseline) > 0: ppms.ui_message_id("0629") if ppms.msg_pop().get_reply() == MSG_REPLY_YES: ppms.ui_message_id("0843") else: return None else: ppms.ui_message_id("0843") # Now check the response to the "Do you really want to proceed?" message if ppms.msg_pop().get_reply() != MSG_REPLY_YES: return None # Why report creation continues when the project can not be scheduled is beyond me... schedule.start_scheduling() project_rights.refresh_costs_details(pr_id) mapping_dict = project_rights.get_report_mapping_definition() vzh = Versionizer_helper(mapping_dict["source_dt"], mapping_dict["target_dt"], mapping_dict["data_items"], pr_id) processes.create_report_for_processes(processes.get_object_type_by_pr_id(pr_id), pr_id, vzh.report_id) if not report_type: raise ValueError('No report_type given - can\'t create report') if report_type == REPORT_CREATION_APPLICATION: ppms.db_modify(ppms.get_query("000265").format(main_pr_id, vzh.report_id)) project_rights.set_report_title(pr_id, vzh.report_id, text_constant.get_text_constant("001255")) elif report_type == REPORT_CREATION_FINAL_REPORT: ppms.db_modify(ppms.get_query("000264").format(main_pr_id, vzh.report_id)) project_rights.set_report_title(pr_id, vzh.report_id, text_constant.get_text_constant("001254")) elif report_type == REPORT_CREATION_REPORT: project_rights.set_report_title(pr_id, vzh.report_id) project_rights.fix_planing_status(type="last_pl", pr_id=pr_id) elif report_type == REPORT_CREATION_BASELINE: with ppms.messages_disabled(), ppms.autoreply_to_message('0472', MSG_REPLY_YES): project_rights.approve_report(main_pr_id, pr_id, vzh.report_id) project_rights.exec_define_baseline(main_pr_id, pr_id, vzh.report_id) baseline_title = text_constant.get_text_constant("001123") + " " + vzh.report_id project_rights.set_report_title(pr_id, vzh.report_id, baseline_title) report_id = vzh.report_id for (source_dict, target_dict) in hyperlink_mapping: source_rec_hql = "from {datatable} x where x.{pr_id_property} = '{pr_id}'".format(datatable=source_dict['datatable'], pr_id_property=source_dict['pr_id'], pr_id=pr_id) target_rec_hql = "from {datatable} x where x.{pr_id_property} = '{pr_id}' and x.{report_id_property} = '{report_id}'".format(datatable=target_dict['datatable'], pr_id_property=target_dict['pr_id'], pr_id=pr_id, report_id_property=target_dict['report_id'], report_id=report_id) source_records = hql.search_record(source_rec_hql) target_records = hql.search_record(target_rec_hql) for source_rec in source_records: for target_rec in target_records: copy_hyperlink(source_rec.get_di(source_dict['hyperlink']), target_rec.get_di(target_dict['hyperlink'])) return report_id def set_cost_trend_analysis(project_id, report_id, value): """Set the cost trend analysis parameter to the given value""" report_record = ppms.search_record(823, [project_id, report_id], ['cost_ta']) report_record.cost_ta.set_value(value) return report_record.save(('cost_ta',)) def set_milestone_trend_analysis(project_id, report_id, value): """Set the milestone trend analysis parameter to the given value""" report_record = ppms.search_record(823, [project_id, report_id], ['ta']) report_record.ta.set_value(value) return report_record.save(('ta',)) def revoke_report(project_id, report_id): """Revoke a report Returns True if the report was revoked or False if it was not """ report_record = ppms.search_record(823, [project_id, report_id], ['main_pr_id', 'active_sr', 'acitve_baseline']) main_pr_id = report_record.main_pr_id.get_value() active_sr = report_record.active_sr.get_value() active_baseline = report_record.acitve_baseline.get_value() if not active_baseline and active_sr: #initialize approval for current report ppms.db_modify(ppms.get_query("000278").format(main_pr_id, report_id)) #set former report to active report ppms.db_modify(ppms.get_query("000279").format(main_pr_id)) #calculate next former report by approval date approved_reports = ppms.db_select(ppms.get_query("000280").format(main_pr_id)) if len(approved_reports) > 0: new_former_report_id = approved_reports[0][0] ppms.db_modify(ppms.get_query("000281").format(main_pr_id, new_former_report_id)) #execute sql procedure project_rights.refresh_report_costs_details(project_id) #update earned value count_baseline = ppms.db_select(ppms.get_query("000277").format(project_id)) if len(count_baseline) == 0: project_rights.update_earned_value(main_pr_id, report_id) return True return False def approve_report(project_id, report_id): """Approve a report A report can only be approved if it belongs to a main project Returns either True or False depending on if the report could be approved or not """ report_record = ppms.search_record(823, [project_id, report_id], ['main_pr_id']) main_pr_id = report_record.main_pr_id.get_value() if not project_rights.get_project_is_main_project(project_id): ppms.ui_message_id("0532") return False executed_approval = project_rights.approve_report(main_pr_id, project_id, report_id) if executed_approval: return True return False