def new_big_picture(clasfctn,statement,budgetstatement,budget, bigpict_cahsflow_checkpoints, period):
monthlydataset=ClassificationDataset(clasfctn,Period.Month, statement)
budgetmonthlydataset=ClassificationDataset(clasfctn,Period.Month, budgetstatement)
table=Table(u"Годовой план")
table.define_style("redmoney", foreground_color=Color.Red,formatting_style= Style.Money)
table.define_style("greenmoney", foreground_color=Color.Green,formatting_style= Style.Money)
table.define_style("blackmoney", foreground_color=Color.Black,formatting_style= Style.Money)
table.write_cells_vert(2,0,["family_in","family_out","EBIDTA","cashflow"])
table[1,2]="test"
coli=1
cummulative=0
dtnow=datetime.now()
bt_row=0
for p in monthlydataset.periods:
if not (p._end<dtnow):
break
if p._start<period.start or p._end>period.end:
continue
cummulative=big_pict_period(table,coli,p,clasfctn,monthlydataset,cummulative,bigpict_cahsflow_checkpoints)
bt_row=show_buying_targets(bt_row,p,budget,coli,table, ispast=True)
coli+=1
for p in budgetmonthlydataset.periods:
if p._end<dtnow:
continue
else:
table[0,coli]="Plan Y"+str(p._end.year-2000)
if p._start<period.start or p._end>period.end:
continue
cummulative=big_pict_period(table,coli,p,clasfctn,budgetmonthlydataset,cummulative,bigpict_cahsflow_checkpoints)
bt_row=show_buying_targets(bt_row,p,budget,coli,table, ispast=False)
coli+=1
return table
def relationshipwithcompany(statement,wb,debts):
checkpoints=[]
checkpoints.append([datetime(2012,11,24,17),117097, False])
checkpoints.append([datetime(2013,04,10,16),-35427, False])
table=Table("CM and Max")
table.define_style("redmoney", foreground_color=Color.Red, formatting_style=Style.Money)
table[0,0]=u"Отношения с компанией"
rowi=0
rbase=3
mydebt=0
table[rbase-1,1]=u"Мой долг компании"
table[rbase-1,8]=u"Дала мне компания"
table[rbase-1,7]=u"Я потратил на нужды компании или отдал долг"
for row in statement.Rows:
if row.type!=RowType.Tx:
continue
relation=False
#print row.classification.title
relation=weeklyplanner.check_classification(row.classification,"company_txs_in")
if not relation:
relation=weeklyplanner.check_classification(row.classification,"company_txs")
if not relation:
continue
for cp in checkpoints:
if row.date>=cp[0] and cp[2]==False:
rowi+=1
mydebt=cp[1]
print_checkpoint(table,rbase+rowi,cp)
debts.define_debt_balance("CM",cp[0],cp[1])
rowi+=1
break
table[rbase+rowi,0]=row.date, Style.Day
table[rbase+rowi,2]=row.classification.title
table[rbase+rowi,4]=row.description
v=row.amount.as_float()
if row.tx.direction==1:
mydebt+=v
coli=8
else:
mydebt-=v
coli=7
table[rbase+rowi,1]=mydebt, Style.Money
table[rbase+rowi,coli]=v, Style.Money
table[rbase+rowi,15]=TagTools.TagsToStr(row.tags)
debts.define_debt_balance("CM",row.date,mydebt)
rowi+=1
for cp in checkpoints:
if cp[2]==False:
rowi+=1
mydebt=cp[1]
print_checkpoint(table,rbase+rowi,cp)
debts.define_debt_balance("CM",cp[0],cp[1])
rowi+=1
break
DestinationXls(table,wb)
def budget_weekly_planner(caption,period, plan, clasfctn2, fact,budget):
mtitle="{0} {1}".format(period.start.strftime("%B"),period.start.year)
table=Table(mtitle)
#self._normal_magn=0
table[0,0]=mtitle #название месяца для которого делается отчет
table._normal_magn=100
table.define_style("totals", foreground_color=Color.Red)
table.define_style("weekcaptions", bold=True, font_size=10)
table.define_style("categoryline", bold=True, background_color=Color.LightGreen)
table.define_style("categoryline_totals", italic=True,background_color=Color.LightGreen, formatting_style=Style.Money)
table.define_style("item_plan",background_color=Color.LightGray, formatting_style=Style.Money)
table.define_style("item_plan_overdue",background_color=Color.Red,foreground_color=Color.White, formatting_style=Style.Money)
table.define_style("percent_green",foreground_color=Color.Green, formatting_style=Style.Percent)
table.define_style("percent_red",foreground_color=Color.Red, formatting_style=Style.Percent)
table.define_style("accum",bold=True, formatting_style=Style.Money)
table.define_style("item_fact", formatting_style=Style.Money)
clasfctn=copy.deepcopy(clasfctn2)
t=period.start
weeks=[]
w=WeekDef(t)
weeks.append(w)
#находим первый и последний день недели
while t<period.end:
w.lastday=t
if t.weekday()==6:
#это воскресенье
#ечли это последний день месяца, то не создаем новую неделю
firstDayOfNextWeek=t+timedelta(days=1)
if firstDayOfNextWeek<period.end:
i=w.windex
w=WeekDef(firstDayOfNextWeek)
w.windex=i+1
weeks.append(w)
t+=timedelta(days=1)
#заголовки колонок недель
rowi=1
coli=3
now=datetime.now()
for w in weeks:
w.coli=coli
spast="[p]"
if w.startday<now: spast=""
if now>=w.startday and now<=w.lastday:spast="[c]"
sdays="{0}-{1}".format(w.startday.day,w.lastday.day)
table[rowi,coli]=u"{0}{1}".format(sdays,spast), "weekcaptions"
coli+=2
budget_weekly_planner_preprocessrows(plan,clasfctn,period,weeks,False)
budget_weekly_planner_preprocessrows(fact,clasfctn,period,weeks, True)
now=datetime.now()
duedate=now
if now>period.end:
duedate=period.end
if now<period.start:
duedate=period.start
#вывод массива данных
overspendingReport={}
lastrowi,outputedrecords=budget_weekly_planner_cat(table,clasfctn._root,7, period,plan,weeks,budget,duedate,overspendingReport)
plan_total=0
fact_total=0
prediction_total=0
for w in weeks:
table[3,w.coli]=u"План"
table[3,w.coli+1]=w.plan_total, Style.Money
table[4,w.coli]=u"Факт"
table[4,w.coli+1]=w.fact_total, Style.Money
plan_total+=w.plan_total
fact_total+=w.fact_total
prediction_total+=w.predict_total
table.set_column_width(w.coli, 10)
table.set_column_width(w.coli+1, 5)
table.set_column_width(w.coli+2, 10)
table.set_column_width(w.coli+3, 5)
coli=0
table[3,coli]=u"План"
table[3,coli+1]=u"Факт"
table[3,coli+2]=u"Предикт"
table[4,coli]=plan_total, Style.Money
table[4,coli+1]=fact_total, Style.Money
table[4,coli+2]=prediction_total, Style.Money
over=prediction_total/plan_total
if over>1:
table[5,coli+2]=over,"percent_red"
else:
table[5,coli+2]=over, "percent_green"
table.set_column_width(0, 6)
table.set_column_width(1, 6)
table.set_column_width(2, 6)
todorow=lastrowi+0-5
table[todorow,0]=u"Просроченные бюджетные цели","weekcaptions"
bt_row=1
sum=0
for budget_item in budget.get_buying_targets():
is_overdue, is_executed, is_todo=budget.check_item_execution(budget_item,duedate)
if is_overdue:
table[todorow+bt_row,1]="", Style.Month
descr=budget_item.description
if hasattr(budget_item,"_description"):
descr= budget_item._description
table[todorow+bt_row,3]=u"{0}".format(descr)
table[todorow+bt_row,6]=budget_item.debit, Style.Money
sum+=budget_item.debit
bt_row+=1
table[todorow,6]=sum, Style.Money
lastrowi=todorow
bt_row=1
sum=0
table[lastrowi,7]=u"Бюджетные цели в ближайшие 30 дней","weekcaptions"
for budget_item in budget.get_buying_targets():
is_overdue, is_executed, is_todo=budget.check_item_execution(budget_item,duedate)
if is_todo:
if budget_item.exactdate<period.end:
table[lastrowi+bt_row,8]=budget_item.exactdate, Style.Month
table[lastrowi+bt_row,9]=u"{0}".format(budget_item.description)
table[lastrowi+bt_row,12]=budget_item.debit, Style.Money
sum+=budget_item.debit
bt_row+=1
#table[lastrowi,7]=u"Бюджетные цели ({0})".format(sum)
table[lastrowi,12]=sum, Style.Money
bt_row=lastrowi+3
table[bt_row,0]=u"Отчет по перерасходу","weekcaptions"
overspending_sum=0
#overspendingReport=OrderedDict(overspendingReport)
overspendingReport=OrderedDict(sorted(overspendingReport.items(), key=lambda t: t[1], reverse=True))
for k, v in overspendingReport.items():
if v>0:
bt_row+=1
table[bt_row,3]=k
table[bt_row,6]=v, Style.Money
overspending_sum+=v
table[bt_row+2,3]=u"Итого:"
table[bt_row+2,6]=overspending_sum, Style.Money
return table
