excel_file = open_workbook(file, encoding_override='utf-8')

flush('Opening excel file...')

def prepare_mapping():

s = excel_file.sheet_by_name("Mapping")

for x in range(1, s.nrows):

db.session.add(

db.Lead(account=s.cell(x, 0).value, account_name=s.cell(x, 1).value, lead_code=s.cell(x, 2).value,

name=s.cell(x, 3).value))

flush('Parsing mappings...')

db.session.commit()

def define_variables():

flush('Defining Company names and audit periods...')

sheet = excel_file.sheet_by_name('Instruction')

db.tanimlar['company'] = sheet.cell_value(6, 2)

try:

db.tanimlar['optional'] = sheet.cell_value(7, 2) or "NO"

except IndexError:

flush('There is no optional column...')

for sheet in excel_file.sheets()[2:]:

db.periodss.append(sheet.name)

# donus = dict()

define_variables()

db.Hesaplar = db.make_hesaplar()

db.create_db()

db.len_periods = len(db.periodss)

prepare_mapping()

def find_seperator():

from collections import defaultdict

pointer = defaultdict(int)

stop = 0

for item in excel_file.sheet_by_index(excel_file.nsheets - 1).col(0):

if stop == 5:

return max(pointer.keys(), key=lambda q: pointer[q])

if not str(item.value).isalnum():

for char in str(item.value):

if not char.isalnum():

pointer[char] += 1

stop += 1

for i in range(2, excel_file.nsheets):

sheet = excel_file.sheet_by_index(i)

key = sheet.name

flush('Parsing %s...' % key)

headers = [col.value.strip() for col in sheet.row(0)]

desc_col = 0

bal_col = 0

opt_col = 999

for y in range(len(headers)):

if headers[y].lower() in bakiye:

bal_col = y

if headers[y].lower() in hesap_adi:

desc_col = y

if headers[y] == db.tanimlar['optional']:

opt_col = y

for row in range(1, sheet.nrows):

temp = {}

# todo float diye gorulen kalemlerde seperator farkedilip aslinda str'ye cevrilip seperator replace edilebilir.

for col in range(sheet.ncols):

if col is 0:

temp["number"] = str(sheet.cell_value(row, col)) # burasi duzeltilecek bug var.

elif col == bal_col:

temp[key] = sheet.cell_value(row, col)

elif col == opt_col:

temp['optional'] = sheet.cell_value(row, col)

elif col == desc_col:

temp['name'] = sheet.cell_value(row, col)

param = 'optional' if not db.tanimlar['optional'] == 'NO' else 'NO'

if param is 'NO':

gecici = db.session.query(db.Hesaplar).filter_by(number=temp['number']).first()

else:

hink = db.session.query(db.Hesaplar).filter_by(number=temp['number'],

**{param: temp[param]}).first()

gecici = None

if hink is not None:

if int(getattr(hink, key)) is 0:

gecici = hink

ana_hesap = temp['number'][:3]

lead_cod = None

if db.session.query(db.Lead).filter_by(account=ana_hesap).first() is not None:

lead_cod = db.session.query(db.Lead).filter_by(account=ana_hesap).first().lead_code

if gecici is not None:

setattr(gecici, key, temp[key])

flush(temp['number'] + ' updated...', wait=0.0001, code='blue')

else:

db.session.add(db.Hesaplar(**temp, ana_hesap=ana_hesap, lead_code=lead_cod, len=len(temp['number'])))

flush(temp['number'] + ' added...', wait=0.0001, code='blue')

db.session.commit()

flush('Determining breakdowns...')

accounts = db.session.query(db.Hesaplar).all()

for k in accounts:

if db.tanimlar['optional'] != 'NO' and sum_var is False:

if len(k.number) > 3:

k.bd = True

elif db.tanimlar['optional'] != 'NO' and sum_var is True:

if len(db.session.query(db.Hesaplar).filter(and_(db.Hesaplar.number.startswith(k.number),

db.Hesaplar.optional == k.optional)).all()) <= 1 \

and len(k.number) > 3:

k.bd = True

else:

if len(db.session.query(db.Hesaplar).filter(db.Hesaplar.number.startswith(k.number)).all()) <= 1:

k.bd = True

db.session.commit()

if db.session.query(db.Hesaplar).filter_by(len=3).first() is not None:

flush('Data has main accounts.')

sum_var = True

return True

flush('Creating main accounts...')

query = db.session.query(db.Hesaplar.ana_hesap,

db.Hesaplar.lead_code,

*[func.sum(getattr(db.Hesaplar, '{}'.format(period))).label('{}'.format(period)) for period

in db.periodss]).group_by('ana_hesap').all()

for k in query:

unmapped = None

if k.lead_code == 'Unmapped':

unmapped = db.session.query(db.Hesaplar).filter_by(len=3,

ana_hesap=k.ana_hesap).first() or db.session.query(

db.Hesaplar).filter_by(ana_hesap=k.ana_hesap).first()

source = db.session.query(db.Lead).filter_by(account=k.ana_hesap).first()

main_source = unmapped or source

name = main_source.name

t = db.Hesaplar(number=k.ana_hesap, ana_hesap=k.ana_hesap, name=name, lead_code=main_source.lead_code, len=3)

for q in db.periodss:

[setattr(t, q, getattr(k, q))]

db.session.add(t)

flush('Changing the names of main accounts...')

hesaplar = db.session.query(db.Hesaplar).filter_by(len=3)

for k in hesaplar.all():

item = db.session.query(db.Lead).filter_by(account=k.ana_hesap).first()

if item:

k.name = item.account_name

if exceptions is None:

exceptions = ['900']

flush('Deleting accounts with zero balances in all periods...')

query = db.session.query(db.Hesaplar).filter_by(**{k: 0 for k in db.periodss}).all()

for item in query:

if item.number[:3] in exceptions:

continue

db.session.delete(item)

if summary_check():

return True

else: