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