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 []