def asset_suii(request, year=0): year = u.cleanYear(year) bs = b() bslist = bs.getMiddleYearStatement(year, 0) bslist.pop('自己資本', 'no match') bslist.pop('他人資本', 'no match') bslist.pop('評価換算差額等', 'no match') bslist.pop('固定資産', 'no match') # yearList = ['2019', '2018',] yearList = u.getSelectableYearList() monthList = getMonthList() colors = ( (0,153,255,10), (0,204,204,10), (0.204,153,10), (0,0,102,10), (0,51,51,10), (51,0,51,10), (102,0,0,10), (102,102,0,10), (255,102,0,10), ) context = { 'label': monthList, 'bs_list': bslist, 'year_list': yearList, 'colors': colors, 'view_name': 'sdss 2.0 Asset Summary Suii', 'target_year': str(year), 'message': '', } return render(request, 'asset_suii.html', context)
def rigid_pl(request, year=0): year = u.cleanYear(year) r_pl = r_p() #売上総利益 netRevenue = r_pl.getNetRevenue(year) costOfSales = r_pl.getCostOfSales(year) grossMarginList = [] for i in range(12): grossMarginList.append(netRevenue['total'][i] - costOfSales['total'][i]) grossMargin = { '収益': netRevenue, '売上原価': costOfSales, '売上総利益': grossMarginList, } # # #限界利益 # taxAndFocedCost = r_pl.getTaxAndForcedCost(year) # lifeCost = r_pl.getLifeCost(year) # limitIncomeList = [] # for i in range(12): # limitIncomeList.append(grossMarginList[i] - taxAndFocedCost[i] - lifeCost[i]) # limitIncome = { # '一般費': taxAndFocedCost, # '生活費': lifeCost, # '限界利益':limitIncomeList, # } # # #その他の収益・費用等 # nonOperatingCost = r_pl.getNonOperatingCost(year) # nonOperatingIncome = r_pl.getNonOperatingIncome(year) # entertainmentCost = r_pl.getEntertainmentCost(year) # # netIncomeList = [] # for i in range(12): # netIncomeList.append( # limitIncomeList[i] # - nonOperatingCost[i] # + nonOperatingIncome[i] # - entertainmentCost[i] # ) # netIncome = { # '営業外収益': nonOperatingIncome, # '営業外費用': nonOperatingCost, # '娯楽費': entertainmentCost, # '当月純利益': netIncomeList, # } yearList = u.getSelectableYearList() context = { 'year_list': yearList, '売上総利益': grossMargin, # '限界利益': limitIncome, # '当月純利益': netIncome, # 'month_list': getMonthList(), # 'view_name': 'sdss 2.0 Rigid PL view', # 'target_year': str(year), # 'message': '', } return render(request, 'rigid_pl.html', context)
def getMiddleYearStatement(self, year): try: year = u.cleanYear(year) dic = {} accListQs = db.AccMid.objects.filter( acc_top_uid__union_bs1_pl2_cs3=u.ReportType['BS']).order_by( 'sort_order') current = 0 for acc in accListQs: brCrDirection = acc.acc_top_uid.is_br logger.info('AccMid.name = ' + acc.name + ' brCrDirection = ' + str(brCrDirection)) #前年度末までの残高を算出(初期値) init_qs = db.Journal.objects.filter(date__lt=str(year)) init_br = init_qs.filter( br_acc_bot_uid__acc_mid_uid=acc.uid).aggregate( Sum('br_amount'))['br_amount__sum'] init_cr = init_qs.filter( cr_acc_bot_uid__acc_mid_uid=acc.uid).aggregate( Sum('cr_amount'))['cr_amount__sum'] current = u.diffBrCr(init_br, init_cr, brCrDirection) d = [] for i in range(12): #各月の集計 currentYearMonth = u.createCurrentYearMonthString( year, i + 1) nextYearMonth = u.createNextYearMonthString(year, i + 1) if currentYearMonth <= u.createCurrentYearMonthString( datetime.now().year, datetime.now().month): qs = db.Journal.objects.filter( date__range=(currentYearMonth, nextYearMonth)) curr_br = qs.filter( br_acc_bot_uid__acc_mid_uid=acc.uid).aggregate( Sum('br_amount'))['br_amount__sum'] curr_cr = qs.filter( cr_acc_bot_uid__acc_mid_uid=acc.uid).aggregate( Sum('cr_amount'))['cr_amount__sum'] current += u.diffBrCr(curr_br, curr_cr, brCrDirection) d.append(current if current != 0 else '') else: d.append('') dic[acc.name] = d return dic except Exception as e: logger.error('exception in BSClass.getMiddleYearStatement, year=' + str(year)) return {}
def pl(request, year=0): year = u.cleanYear(year) pl = p() pllist = pl.getBottomYearStatement(year) # yearList = ['2019', '2018',] yearList = u.getSelectableYearList() context = { 'year_list': yearList, 'pl_list': pllist, #'month_list': getMonthList(), 'month_list': getMonthListAndTotal(), 'view_name': 'sdss 2.0 PL view', 'target_year': str(year), 'message': '', } return render(request, 'pl.html', context)
def detail(request, mid_class_uid=1, year=0): year = u.cleanYear(year) d_class = d() # selectable_year_list = ['2019', '2018',] selectable_year_list = u.getSelectableYearList() month_list = range(1,13) # =['1','2', .. '12'] mid_class_list = d_class.getTopMidAccList() acc_amount_list = d_class.getClassificationDetail(mid_class_uid, year) mid_class_name = d_class.getMidClassName(mid_class_uid) context = { 'year': year, 'month_list': month_list, 'selectable_year_list': selectable_year_list, 'mid_class_name': mid_class_name, 'mid_class_list': mid_class_list, 'acc_amount_list': acc_amount_list, } return render(request, 'detail.html', context)
def bs(request, year=0): # | | 10 | 11 | 12 | # | ICOCA | 2,100 | 5,900 | 9,200 | # | 現金 | 13,110 | 24,670 | ... | year = u.cleanYear(year) bs_class = b() bslist = bs_class.getBottomYearStatement(year) # yearList = ['2019', '2018',] yearList = u.getSelectableYearList() context = { 'bs_list': list, 'year_list': yearList, 'bs_mid_list': bslist, 'month_list': getMonthList(), 'view_name': 'sdss 2.0 BS view', 'target_year': str(year), 'message': '', } return render(request, 'bs.html', context)
def summary(request, year=0): year = u.cleanYear(year) bs = b() pl = p() bslist = bs.getMiddleYearStatement(year) pllist = pl.getMiddleYearStatement(year) # yearList = ['2019', '2018',] yearList = u.getSelectableYearList() context = { 'bs_list': bslist, 'pl_list': pllist, 'month_list': getMonthList(), 'month_list_total': getMonthListAndTotal(), 'year_list': yearList, 'view_name': 'sdss 2.0 PL BS summary view', 'target_year': str(year), 'message': '', } return render(request, 'summary.html', context)
def suii(request, year=0, month=0, accID1=GENKIN_ACC_BOT_UID, accID2=0, accID3=0, ): message = '' # ダミーが来たらとりあえず現金を表示しとく if int(accID1) == DUMMY_ACC_BOT_UID: accID1 = GENKIN_ACC_BOT_UID message += 'accID1にダミーIDが来たため現金を表示します。\r\n' year = u.cleanYear(year) month = u.cleanMonth(month) suiilist1 = getAccountSuii(year, month, accID1) suiilist2 = getAccountSuii(year, month, accID2) suiilist3 = getAccountSuii(year, month, accID3) suiilist = {db.AccBot.objects.get(uid=accID1).name: suiilist1,} if int(accID2) != 0 and int(accID2) != DUMMY_ACC_BOT_UID: suiilist[db.AccBot.objects.get(uid=accID2).name] = getAccountSuii(year, month, accID2) if int(accID3) != 0 and int(accID3) != DUMMY_ACC_BOT_UID: suiilist[db.AccBot.objects.get(uid=accID3).name] = getAccountSuii(year, month, accID3) labels = list(range(1,32)) accbot_listgroup = getAccountListByGroup() one_two_three = [1,2,3] print('are---') context = { 'title_jp': str(year) + '年' + str(month) + '月の各勘定合計金額変動/累積状況', 'o_t_t': one_two_three, 'listgroup': accbot_listgroup, 'ym_list': getGatherYearMonth(), 'suii_list': suiilist, 'label': labels, 'view_name': 'sdss 2.0 Account Transition view', 'target_year_month': str(year) + '/' + str(month), 'message': message, } return render(request, 'suii.html', context)
def journal_export(request, year=0): '''指定された年の1〜12月の仕訳を記載したエクセルファイルをダウンロードする''' yaer = u.cleanYear(year) filename = 'fs_' filename += year filename += '_' filename += u.getStrTimeStamp() filename += '.xlsx' wb = openpyxl.load_workbook('/home/share/template_f_s.xlsx') y_startDate = year + '0101' y_endDate = year + '1231' journal_qs = db.Journal.objects.filter(date__range=(y_startDate,y_endDate)).order_by('date') journal_list = getExportJournalList(journal_qs) currentSheetName = '01' sheet = wb[currentSheetName] current_col=1 current_row=5 for journal in journal_list: if currentSheetName != journal['month']: currentSheetName = journal['month'] sheet = wb[currentSheetName] current_row=5 sheet.cell(row=current_row, column=1, value=journal['month']) sheet.cell(row=current_row, column=2, value=journal['day']) sheet.cell(row=current_row, column=3, value=journal['br_name']) sheet.cell(row=current_row, column=4, value=journal['br_amount']) sheet.cell(row=current_row, column=5, value=journal['cr_name']) sheet.cell(row=current_row, column=6, value=journal['cr_amount']) sheet.cell(row=current_row, column=7, value=journal['note']) current_row += 1 response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=%s' % filename wb.save(response) return response
def getBottomYearStatement(self, year): try: print('sarani temae...' + str(year)) year = u.cleanYear(year) print('daibu temae...') #dic = {} ret = [] accListQs = db.AccBot.objects.filter( acc_mid_uid__acc_top_uid__union_bs1_pl2_cs3=u.ReportType['PL'] ).order_by('sort_order') print('sosite.//') profit = [0] * 12 loss = [0] * 12 proloss = [0] * 12 current = 0 print('kokomade ok!') ''' 20190401 仕様変更 変更前 { '支払家賃': [38000, 38000, ... 38000] } -> 変更後 [ 'acc_name': '支払家賃', 'budget': 50000, 'list': [38000, 38000, 38000, ... 38000] ] ''' loss_total = 0 proloss_total = 0 profit_total = 0 for acc in accListQs: brCrDirection = acc.acc_mid_uid.acc_top_uid.is_br log.info('acc.uid = ' + str(acc.uid) + ' brCrDirection = ' + str(brCrDirection)) d = [] # 12ヶ月いずれも出現しない勘定科目は登録しないのでその管理フラグ wasNotChangeInYear = True acc_total = 0 for i in range(12): #各月の集計 currentYearMonth = u.createCurrentYearMonthString( year, i + 1) nextYearMonth = u.createNextYearMonthString(year, i + 1) if currentYearMonth <= u.createCurrentYearMonthString( datetime.now().year, datetime.now().month): qs = db.Journal.objects.filter( date__range=(currentYearMonth, nextYearMonth)) curr_br = qs.filter(br_acc_bot_uid=acc.uid).aggregate( Sum('br_amount'))['br_amount__sum'] curr_cr = qs.filter(cr_acc_bot_uid=acc.uid).aggregate( Sum('cr_amount'))['cr_amount__sum'] current = u.diffBrCr(curr_br, curr_cr, brCrDirection) if brCrDirection > 0: loss[i] += current loss_total += current proloss[i] -= current proloss_total -= current else: profit[i] += current profit_total += current proloss[i] += current proloss_total += current if current == 0: d.append('') else: wasNotChangeInYear = False d.append(current) #d.append(current if current != 0 else '') acc_total += current else: d.append('') # 全部空欄なら追加しない if wasNotChangeInYear == False: d.append(acc_total) ret.append({ 'acc_name': acc.name, 'budget': bd.getBudget(acc.uid), 'list': d, }) #dic[acc.name] = d loss.append(loss_total) profit.append(profit_total) proloss.append(proloss_total) ret.append({ 'acc_name': '費用計', 'budget': '', 'list': loss, }) ret.append({ 'acc_name': '利益計', 'budget': '', 'list': profit, }) ret.append({ 'acc_name': '損益', 'budget': '', 'list': proloss, }) #dic['費用計'] = loss #dic['利益計'] = profit #dic['損益'] = proloss return ret except ValueError as value_e: print('PLClass.getBottomYearStatement 何らかの例外が発生しました errorType:' + type(value_e) + 'year=' + str(year)) return [] except TypeError as type_e: print('PLClass.getBottomYearStatement 何らかの例外が発生しました errorType:' + type(type_e) + 'year=' + str(year)) return []
def getMiddleYearStatement(self, year): try: year = u.cleanYear(year) dic = {} accListQs = db.AccMid.objects.filter( acc_top_uid__union_bs1_pl2_cs3=u.ReportType['PL']).order_by( 'sort_order') profit = [0] * 12 loss = [0] * 12 proloss = [0] * 12 loss_total = 0 proloss_total = 0 profit_total = 0 for acc in accListQs: brCrDirection = acc.acc_top_uid.is_br log.info('AccMid.name = ' + str(acc.name) + ' brCrDirection = ' + str(brCrDirection)) d = [] acc_total = 0 for i in range(12): #各月の集計 currentYearMonth = u.createCurrentYearMonthString( year, i + 1) nextYearMonth = u.createNextYearMonthString(year, i + 1) if currentYearMonth <= '{:04d}{:02d}'.format( datetime.now().year, datetime.now().month): qs = db.Journal.objects.filter( date__range=(currentYearMonth, nextYearMonth)) curr_br = qs.filter( br_acc_bot_uid__acc_mid_uid=acc.uid).aggregate( Sum('br_amount'))['br_amount__sum'] curr_cr = qs.filter( cr_acc_bot_uid__acc_mid_uid=acc.uid).aggregate( Sum('cr_amount'))['cr_amount__sum'] current = u.diffBrCr(curr_br, curr_cr, brCrDirection) acc_total += current if brCrDirection > 0: loss[i] += current loss_total += current proloss[i] -= current proloss_total -= current else: profit[i] += current profit_total += current proloss[i] += current proloss_total += current d.append(current if current != 0 else '') else: d.append('') if acc_total == 0: d.append('') else: d.append(acc_total) dic[acc.name] = d loss.append(loss_total) profit.append(profit_total) proloss.append(proloss_total) dic['費用計'] = loss dic['利益計'] = profit dic['損益'] = proloss return dic except Exception as e: print('PLClass.getMiddleYearStatement 何らかの例外が発生しました year=' + str(year)) return {}
def getCostOfSales(self, year): try: print('create cost of sales ' + str(year)) year = u.cleanYear(year) ret = [] accListIncome = db.AccBot.objects.filter(acc_mid_uid__name='売上原価') incomeTotal = [0] * 12 for acc in accListIncome: d = [] # 12ヶ月いずれも出現しない勘定科目は登録しないのでその管理フラグ wasNotChangeInYear = True acc_total = 0 for i in range(12): #各月の集計 currentYearMonth = u.createCurrentYearMonthString(year, i + 1) nextYearMonth = u.createNextYearMonthString(year, i + 1) if currentYearMonth <= u.createCurrentYearMonthString(datetime.now().year, datetime.now().month): qs = db.Journal.objects.filter(date__range=(currentYearMonth, nextYearMonth)) curr_br = qs.filter(br_acc_bot_uid=acc.uid).aggregate(Sum('br_amount'))['br_amount__sum'] curr_cr = qs.filter(cr_acc_bot_uid=acc.uid).aggregate(Sum('cr_amount'))['cr_amount__sum'] current = u.diffBrCr(curr_br, curr_cr, 1) incomeTotal[i] += current if current == 0: d.append('') else: wasNotChangeInYear = False d.append(current) acc_total += current else: d.append('') # 全部空欄なら追加しない if wasNotChangeInYear == False: d.append(acc_total) ret.append({ 'acc_name': acc.name, 'budget': bd.getBudget(acc.uid), 'list': d, }) #dic[acc.name] = d ret.append({ 'acc_name': '営業収益計', 'list': incomeTotal, }) # loss.append(loss_total) # profit.append(profit_total) # proloss.append(proloss_total) # ret.append({'acc_name': '費用計', 'budget': '', 'list': loss,}) # ret.append({'acc_name': '利益計', 'budget': '', 'list': profit,}) # ret.append({'acc_name': '損益', 'budget': '', 'list': proloss,}) #dic['費用計'] = loss #dic['利益計'] = profit #dic['損益'] = proloss return ret except ValueError as value_e: print('RigidPLClass.getBottomYearStatement 何らかの例外が発生しました errorType:' + type(value_e) + 'year=' + str(year)) return [] except TypeError as type_e: print('RigidPLClass.getBottomYearStatement 何らかの例外が発生しました errorType:' + type(type_e) + 'year=' + str(year)) return []
def getClassificationDetail(self, classificaiton, year): try: # TODO 上位でもクリーニングしてるが・・・ year = u.cleanYear(year) total = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ''' 20190401 仕様変更 変更前 { '支払家賃': [38000, 38000, ... 38000] } -> 変更後 [ 'acc_name': '支払家賃', 'budget': 50000, 'list': [38000, 38000, 38000, ... 38000] ] ''' ret = [] accListQs = db.AccBot.objects.filter( acc_mid_uid=classificaiton).order_by('sort_order') acc_top = 0 brCrDirection = 0 if accListQs is not None: acc_top = accListQs[0].acc_mid_uid.acc_top_uid.uid brCrDirection = accListQs[0].acc_mid_uid.acc_top_uid.is_br for acc in accListQs: #brCrDirection = acc.acc_mid_uid.acc_top_uid.is_br logger.info('acc.name = ' + acc.name + ' brCrDirection = ' + str(brCrDirection)) #前年度末までの残高を算出(初期値) init_br = 0 init_cr = 0 current = 0 #最上位の科目が”純資産”以下(=資産、負債、純資産のBS科目)であれば前年度末の金額を計算する if acc_top <= u.AccType['NetAsset']: init_qs = db.Journal.objects.filter(date__lt=str(year)) init_br = init_qs.filter(br_acc_bot_uid=acc.uid).aggregate( Sum('br_amount'))['br_amount__sum'] init_cr = init_qs.filter(cr_acc_bot_uid=acc.uid).aggregate( Sum('cr_amount'))['cr_amount__sum'] current = u.diffBrCr(init_br, init_cr, brCrDirection) d = [] for i in range(12): #各月の集計 currentYearMonth = u.createCurrentYearMonthString( year, i + 1) nextYearMonth = u.createNextYearMonthString(year, i + 1) if currentYearMonth <= u.createCurrentYearMonthString( datetime.now().year, datetime.now().month): qs = db.Journal.objects.filter( date__range=(currentYearMonth, nextYearMonth)) curr_br = qs.filter(br_acc_bot_uid=acc.uid).aggregate( Sum('br_amount'))['br_amount__sum'] curr_cr = qs.filter(cr_acc_bot_uid=acc.uid).aggregate( Sum('cr_amount'))['cr_amount__sum'] #最上位の科目が”純資産”以下(=資産、負債、純資産のBS科目)であれば前月末の金額を足す if acc_top <= u.AccType['NetAsset']: current += u.diffBrCr(curr_br, curr_cr, brCrDirection) else: current = u.diffBrCr(curr_br, curr_cr, brCrDirection) d.append(current if current != 0 else '') total[i] += current if current != 0 else 0 else: d.append('') total[i] = '' ret.append({ 'acc_name': acc.name, 'budget': bd.getBudget(acc.uid), 'list': d, }) #dic[acc.name] = d ret.append({ 'acc_name': '合計', 'budget': '', 'list': total, }) #dic['合計'] = total return ret except Exception as e: logger.error('exception in DetailClass.getClassificationDetail,' + ' classification=' + classificaiton + + ' year=' + str(year)) return []