def monthlyData(self, year, excelCellInfoPerDate=None): #find columns in this sheet which represent months allMonths = sorted([k for k in self.__dict__ if dates.datify(k, year)], key=(lambda dateColumn: dates.datify(dateColumn))) #build monthly expected payments table monthlyExpectedPaymentOf = {} #if there are different expected payment throughtout this sheet, then monthlyColumns gets updated #ar the c'tor of the excel record if len(self.monthlyColumns): currExpectedPayment = self.payment for m in allMonths: if int(m) in self.monthlyColumns: currExpectedPayment = utils.Intify( excel_utils.ExtractCellValueByColumn( self, 'ת.חודשי_%d' % int(m))) monthlyExpectedPaymentOf[m] = currExpectedPayment else: for m in allMonths: monthlyExpectedPaymentOf[m] = utils.Intify(self.payment) for month in allMonths: cell_info = excelCellInfoPerDate[dates.datify(month, year)] expected_payment = monthlyExpectedPaymentOf[month] actual_payment = utils.Intify(self.monthlyPayment(month)) cell_info.payment_details.actual_payment = actual_payment cell_info.payment_details.expected_payment = expected_payment
def ResolveDynamicFieldValue(record, field_name, field_type): #print field_name field_value = ExtractCellValueByColumn(record, field_name.encode('utf-8')) if field_type == 1: return field_value return utils.Intify(field_value)
def monthlyData(self, year, excelCellInfoPerDate=None): #find columns in this sheet which represent months allMonths = sorted([k for k in self.__dict__ if dates.datify(k, year)], key=(lambda dateColumn: dates.datify(dateColumn))) #build monthly expected payments table monthlyExpectedPaymentOf = {} #if there are different expected payment throughtout this sheet, then monthlyColumns gets updated #ar the c'tor of the excel record if len(self.monthlyColumns): currExpectedPayment = self.payment for m in allMonths: if m in self.monthlyColumns: currExpectedPayment = int( getattr(self, 'תשלום חודשי_%d' % m)) monthlyExpectedPaymentOf[m] = currExpectedPayment else: for m in allMonths: monthlyExpectedPaymentOf[m] = utils.Intify(self.payment) #calculate how much was paid during the whole period total_paid = sum( utils.Intify(self.monthlyPayment(month)) for month in allMonths if self.monthlyPayment(month) is not None) for month in allMonths: cell_info = excelCellInfoPerDate[dates.datify(month, year)] expected_payment = monthlyExpectedPaymentOf[month] if not total_paid: actual_payment = 0 else: if total_paid >= expected_payment: actual_payment = expected_payment else: actual_payment = total_paid total_paid -= actual_payment cell_info.payment_details.actual_payment = actual_payment cell_info.payment_details.expected_payment = expected_payment
def ParseTenantsPaymentData(excel, buildingOf, dbBuildingName): book = excel_helper.OpenExcelFile(excel) if not book: return building_name = os.path.splitext(os.path.basename(excel).replace(utils.config.excelPaymentFilePrefix, '').strip())[0].strip() building = buildingOf[building_name] building.based_on_files.add(excel) building.building_name = building_name #dict year->sheet sheetsOf = GetPaymentSheets(book) if not sheetsOf: return for (year, sheet) in sheetsOf.items(): global indexAppartnment indexAppartnment = -1 apartment_number = "" #go over all excel rows, each represents an apartment for excelRecord in excel_helper.ExtractApartments(sheet, 2): indexAppartnment += 1 appStart = ( indexAppartnment%4 == 0) if appStart and indexAppartnment: #time to insert apt_num = utils.Intify(apartment_number) if utils.Intify(apartment_number) else apartment_number app = building.apartmentOf[apt_num] app.apartment_number = apt_num recordToKeep.monthlyData(year, app.excelCellInfoPerDate) if appStart: apartment_number = excelRecord.appartment recordToKeep = excelRecord #insert last appartment if (indexAppartnment + 1) % 4 == 0: #time to insert apt_num = utils.Intify(apartment_number) if utils.Intify(apartment_number) else apartment_number app = building.apartmentOf[apt_num] app.apartment_number = apt_num recordToKeep.monthlyData(year, app.excelCellInfoPerDate)
def special_debt(self): # if payment does not exist, treat as 0 as it is defined integer in the data base return utils.Intify( excel_utils.ExtractCellValueByColumn(self, 'יתרה לתשלום'))
def building_code(self): return utils.Intify( excel_utils.ExtractCellValueByColumn(self, 'קוד בניין'))
def debt(self): return utils.Intify(excel_utils.ExtractCellValueByColumn(self, 'יתרה'))
def ParseTenantsPaymentData(excel, buildingOf, dbBuildingName): # get a dict of building code -> building name codesOf = GetBuildingCodeDict() book = excel_helper.OpenExcelFile(excel) if not book: return sheet = GetPaymentSheet(book) if not sheet: return # first building starts here start_row = 14 while True: skip_building = False # column 10 = K, thats where the building code supposed to be building_code_cell = sheet.cell_value(start_row, 10) # no more buiuldings, finish everything if not len(building_code_cell): break # extract building code building_code = utils.Intify( building_code_cell.split( 'חובות דיירים'.decode('utf-8'))[-1].strip()) if building_code: try: # take building name from the dict extracted from the general tenants sheet building_name = codesOf[building_code] print building_code, building_name # retrieve building object building = buildingOf[building_name] building.based_on_files.add(excel) building.building_name = building_name except KeyError: skip_building = True # if for some reason there is no code, iterate the rows but don't update building tenants else: skip_building = True # tenants data starts 2 rows after the header start_row += 2 for num_tenants, excelRecord in enumerate( excel_helper.ExtractApartments(sheet, start_row + 2, 2, start_row)): if skip_building: continue apartment_number = excelRecord.appartment app = building.apartmentOf[apartment_number] excelRecord.monthlyData(app.excelCellInfoPerDate) # set pointer to the last tenant in the table start_row += (num_tenants + 1) * 2 # set pointer to the next building start_row += 5
def payment(self): #if payment does not exist, treat as 0 as it is defined integer in the data base return utils.Intify( excel_utils.ExtractCellValueByColumn(self, 'ת.חודשי'))
def ParseTenantsGeneralData(excel, buildingOf, dbBuildingName): print excel book = excel_helper.OpenExcelFile(excel) if not book: return building_name = os.path.splitext(os.path.basename(excel).replace(utils.config.excelGeneralFilePrefix, '').strip())[0].strip() print building_name building = buildingOf[building_name] building.based_on_files.add(excel) building.building_name = building_name sheet = GetGeneralSheet(book) if not sheet: return global indexAppartnment indexAppartnment = -1 renter_mails = [] owner_mails = [] renter_phones = [] owner_phones = [] renter_name = "" owner_name = "" payment = 0 apartment_number = "" #go over all excel rows, each represents an apartment for i, excelRecord in enumerate(excel_helper.ExtractApartments(sheet, 2)): indexAppartnment += 1 appStart = ( indexAppartnment%4 == 0) if appStart and indexAppartnment: #time to insert apt_num = utils.Intify(apartment_number) if utils.Intify(apartment_number) else apartment_number app = building.apartmentOf[apt_num] app.apartment_number = apt_num app.recent_payment = payment if type(renter_name) == int or len(renter_name): app.renter = elements.Person(renter_name, renter_mails, renter_phones ) else: app.renter = None if type(owner_name) == int or len(owner_name): app.owner = elements.Person(owner_name, owner_mails, owner_phones ) else: app.owner = None if app.renter: app.renter.defacto = True else: app.owner.defacto = True #initialize renter_mails = [] owner_mails = [] renter_phones = [] owner_phones = [] renter_name = "" owner_name = "" payment = 0 apartment_number = "" if indexAppartnment % 4 == 0: apartment_number = excelRecord.appartment renter_name = excelRecord.renter owner_name = excelRecord.owner payment = excelRecord.payment if len(excelRecord.renterMails): renter_mails.append(','.join(excelRecord.renterMails)) if len(excelRecord.ownerMails): owner_mails.append(','.join(excelRecord.ownerMails)) if len(excelRecord.renterPhones): renter_phones.append(','.join(excelRecord.renterPhones)) if len(excelRecord.ownerPhones): owner_phones.append(','.join(excelRecord.ownerPhones)) #app.apartment_number = apartment_number #app.recent_payment = excelRecord.payment #app.renter = GetRenter(excelRecord) #app.owner = GetOwner(excelRecord) #could be that no renter or owner details exist #if app.renter or app.owner: #if app.renter: #app.renter.defacto = True #else: #app.owner.defacto = True ##if no details for renter and owner store an empty person as owner #else: #app.owner = elements.Person() #app.owner.defacto = True #insert last appartment if (indexAppartnment + 1) % 4 == 0: #time to insert apt_num = utils.Intify(apartment_number) if utils.Intify(apartment_number) else apartment_number app = building.apartmentOf[apt_num] app.apartment_number = apt_num app.recent_payment = payment if type(renter_name) == int or len(renter_name): app.renter = elements.Person(renter_name, renter_mails, renter_phones ) else: app.renter = None if type(owner_name) == int or len(owner_name): app.owner = elements.Person(owner_name, owner_mails, owner_phones ) else: app.owner = None if app.renter: app.renter.defacto = True else: app.owner.defacto = True