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 getAccountSuii(year, month, accID):
if accID == 0:
return []
strDate = u.createCurrentYearMonthString(year, month)
br_direction = db.AccBot.objects.get(uid=accID).acc_mid_uid.acc_top_uid.is_br
# BSのみ前月末残高を計算
if db.AccBot.objects.get(uid=accID).acc_mid_uid.acc_top_uid.uid >= 4:
total = 0
else:
init_qs = db.Journal.objects.filter(date__lt=strDate)
init_br = u.getEmptyOrValueInt(init_qs.filter(br_acc_bot_uid=accID).aggregate(Sum('br_amount'))['br_amount__sum'])
init_cr = u.getEmptyOrValueInt(init_qs.filter(cr_acc_bot_uid=accID).aggregate(Sum('cr_amount'))['cr_amount__sum'])
total = u.diffBrCr(init_br, init_cr, br_direction)
print('total=' + str(total))
# summing day by day ...
dic = []
nextMonth = u.createNextYearMonthString(year, month)
qs = db.Journal.objects.filter(date__range=(strDate, nextMonth))
curr_br_qs = qs.filter(br_acc_bot_uid=accID)
curr_cr_qs = qs.filter(cr_acc_bot_uid=accID)
# TODO 2019年現在にyearが２０２０だった場合の考慮はしてないが不要？
isSyoribiYM = u.get_nowdt().year == year and u.get_nowdt().month == month
# 1日 ~ 31日まで借方と貸方の差分を取得する。2月31日とかも処理するけど文字列型なので問題なし
for i in range(1,32):
# 処理当日以降のは登録しない
if isSyoribiYM and i > u.get_nowdt().day:
dic.append(0)
else:
today_br_sum = u.getEmptyOrValueInt(curr_br_qs.filter(date=(strDate+'{:02}'.format(i))).aggregate(Sum('br_amount'))['br_amount__sum'])
today_cr_sum = u.getEmptyOrValueInt(curr_cr_qs.filter(date=(strDate+'{:02}'.format(i))).aggregate(Sum('cr_amount'))['cr_amount__sum'])
print(str(i) + ': add to ' + str(u.diffBrCr(today_br_sum, today_cr_sum, br_direction)))
total += u.diffBrCr(today_br_sum, today_cr_sum, br_direction)
dic.append(total)
#else:
# dic.append(0)
#print(str(dic))
return dic
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 []