def __init__(self, project_details_bp, project_details_our_company, month):
self.project_details_bp = project_details_bp
self.project_details_our_company = project_details_our_company
self.month = month
self.excel = Excel("project_list.xlsx")
self.current_row = 4
self.current_col = 1
self.ws = self.excel.active
def __init__(self, project_month):
super(DeliveryReport, self).__init__(project_month)
self.excel = Excel("delivery.xlsx")
self.ws = self.excel.active
class DeliveryReport(BillingBaseReport):
project_billing_repository = ProjectBillingRepository()
def __init__(self, project_month):
super(DeliveryReport, self).__init__(project_month)
self.excel = Excel("delivery.xlsx")
self.ws = self.excel.active
def delivery_report_download(self):
self._create_excel()
return self.excel.download()
def _create_excel(self):
super(DeliveryReport, self)._create_excel()
# エクセルを一時フォルダに保存
self.excel.save('05_納品書（{}）_{}.xlsx'.format(self.project_month.client_billing_no,
datetime.today().strftime("%Y%m%d")))
def create_billing_details(self):
project_billings = self.project_billing_repository.find_billings_at_a_month(self.project_month.project_id,
self.project_month.project_month)
# 請求明細----------------------------------------------------------------------------------
for n, project_billing in enumerate(project_billings):
# 書式設定
self.create_billing_detail()
# 値代入
self.ws['A' + str(self.current_row)].value = (n + 1)
self.ws['E' + str(self.current_row)].value = project_billing.billing_content
self.ws['I' + str(self.current_row)].value = project_billing.billing_amount
self.ws['M' + str(self.current_row)].value = project_billing.remarks
# 次の行へ移動
self.current_row += 1
self.current_row += 1
# 行調整
if self.current_row < 25:
self.current_row = 25
# 小計--------------------------------------------------------------------------------------
# 値代入
self.create_subtotal_list('課税 小計', "")
if self.project_month.project.client_company.billing_tax != Tax.zero:
self.create_subtotal_list('消費税', "")
self.create_subtotal_list('交通費等 非課税 小計', "")
self.current_row += 1
# 行調整
if self.current_row < 39:
self.current_row = 39
# 累計--------------------------------------------------------------------------------------
# 書式設定
self.ws['I' + str(self.current_row)].font = Font(name="HGS明朝")
self.ws['K' + str(self.current_row)].font = Font(name="Century")
# 罫線
for row in self.ws['A' + str(self.current_row) + ":Q" + str(self.current_row)]:
for cell in row:
cell.border = Border(top=Side(style='thin'))
self.current_row += 2
def __init__(self, project):
self.project = project
self.excel = Excel("client_order.xlsx")
self.ws = self.excel.active
class ClientOrderReport(object):
def __init__(self, project):
self.project = project
self.excel = Excel("client_order.xlsx")
self.ws = self.excel.active
def download(self):
self._create_excel()
return self.excel.download()
def _create_excel(self):
self.write_client_order().write_style()
# エクセルを一時フォルダに保存
self.excel.save('04_注文請書（{}）_{}.xlsx'.format(
self.project.client_order_no or "",
datetime.today().strftime("%Y%m%d")))
def write_client_order(self):
# 値を代入
self.ws[self.excel.get_defined_name_range(
"printed_date")].value = datetime.today().date()
if self.project.client_company:
self.ws[self.excel.get_defined_name_range("client_company_name")].value = \
self.project.client_company.company_name
self.ws[self.excel.get_defined_name_range(
"client_order_no")].value = self.project.client_order_no
self.ws[self.excel.get_defined_name_range(
"estimation_no")].value = self.project.estimation_no
self.ws[self.excel.get_defined_name_range(
"project_name")].value = self.project.project_name
if self.project.contract_form:
self.ws[self.excel.get_defined_name_range(
"contract_form")].value = self.project.contract_form.name
self.ws[self.excel.get_defined_name_range("estimated_total_amount")].value = \
self.project.estimated_total_amount or 0
self.ws[self.excel.get_defined_name_range(
"contents")].value = self.project.contents
self.ws[self.excel.get_defined_name_range(
"start_date")].value = self.project.start_date
self.ws[self.excel.get_defined_name_range(
"end_date")].value = self.project.end_date
self.ws[self.excel.get_defined_name_range(
"responsible_person")].value = self.project.responsible_person
self.ws[self.excel.get_defined_name_range(
"quality_control")].value = self.project.quality_control
self.ws[self.excel.get_defined_name_range(
"subcontractor")].value = self.project.subcontractor
self.ws[self.excel.get_defined_name_range(
"working_place")].value = self.project.working_place
self.ws[self.excel.get_defined_name_range(
"delivery_place")].value = self.project.delivery_place
self.ws[self.excel.get_defined_name_range(
"deliverables")].value = self.project.deliverables
if self.project.billing_timing == BillingTiming.billing_by_month:
self.ws[self.excel.get_defined_name_range(
"inspection_date")].value = '毎月月末'
else:
self.ws[self.excel.get_defined_name_range(
"inspection_date")].value = self.project.inspection_date
if self.project.billing_timing:
self.ws[self.excel.get_defined_name_range("billing_timing")].value = \
self.project.billing_timing.name_for_report
self.ws[self.excel.get_defined_name_range(
"remarks")].value = self.remarks()
return self
def write_style(self):
# フォント設定
self.ws[self.excel.get_defined_name_range("client_company_name")].font = \
Font(name="ＭＳ ゴシック", size=14, underline="single")
# 表示形式
self.ws[self.excel.get_defined_name_range(
"printed_date")].number_format = 'yyyy年m月d日'
self.ws[self.excel.get_defined_name_range(
"start_date")].number_format = 'yyyy年m月d日'
self.ws[self.excel.get_defined_name_range(
"end_date")].number_format = 'yyyy年m月d日'
if self.project.billing_timing == BillingTiming.billing_at_last:
self.ws[self.excel.get_defined_name_range(
"inspection_date")].number_format = 'yyyy"年"m"月"d"日"'
# 罫線
self.write_border_to_merged_cell(row=14)
self.write_border_to_merged_cell(row=16)
self.write_border_to_cell(row=18)
self.write_border_to_cell(row=19)
self.write_border_to_cell(row=20)
self.write_border_to_merged_cell(row=21)
self.write_border_to_cell(row=23)
self.write_border_to_cell(row=24)
self.write_border_to_cell(row=25)
self.write_border_to_cell(row=26)
self.write_border_to_cell(row=27)
self.write_border_to_cell(row=28)
self.write_border_to_cell(row=29)
self.write_border_to_cell(row=30)
self.write_border_to_merged_cell(row=31)
self.ws['B32'].border = Border(left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
# セル結合されていない1行の項目に罫線を引く
def write_border_to_cell(self, row):
for column_num in ['D', 'E', 'F', 'G']:
self.ws[column_num +
str(row)].border = Border(bottom=Side(style='thin'))
self.ws['H' + str(row)].border = Border(bottom=Side(style='thin'),
right=Side(style='thin'))
# セルが結合されて2行になっている項目に罫線を引く
def write_border_to_merged_cell(self, row):
self.ws['H' + str(row)].border = Border(top=Side(style='thin'),
right=Side(style='thin'))
for column_num in ['D', 'E', 'F', 'G']:
self.ws[column_num +
str(row + 1)].border = Border(bottom=Side(style='thin'))
self.ws['H' + str(row + 1)].border = Border(bottom=Side(style='thin'),
right=Side(style='thin'))
def remarks(self):
text = '上記以外の条件は当社見積書(見積書No.{})の通りとします。'\
.format(self.project.estimation_no or " ")
return text
class ProjectList(object):
def __init__(self, project_details_bp, project_details_our_company, month):
self.project_details_bp = project_details_bp
self.project_details_our_company = project_details_our_company
self.month = month
self.excel = Excel("project_list.xlsx")
self.current_row = 4
self.current_col = 1
self.ws = self.excel.active
def download(self):
self._create_excel_bp()
self._create_excel_our_company()
return self.excel.download()
def _create_excel_bp(self):
self.ws = self.excel.workbook['案件一覧(BP)']
self._create_excel()
def _create_excel_our_company(self):
self.ws = self.excel.workbook['案件一覧(プロパー)']
self._create_excel()
def _create_excel(self):
to = ""
client_flag = ""
if self.ws.title == '案件一覧(BP)':
to = self.project_details_bp
client_flag = 'BP'
elif self.ws.title == '案件一覧(プロパー)':
to = self.project_details_our_company
client_flag = 'プロパー'
# 結合しているセルの罫線が消えるので再度引き直す
if client_flag == 'BP':
for column_num in ['P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC']:
self.ws[column_num + '2'].border = Border(top=Side(style='thin'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
else:
for column_num in ['P', 'Q', 'R', 'S', 'T', 'U']:
self.ws[column_num + '2'].border = Border(top=Side(style='thin'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
# セルにデータを書き込み
i = 0
for project_detail in to:
if project_detail.billing_start_day <= self.month <= project_detail.billing_end_day:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = ""
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = ""
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = ""
if project_detail.project.recorded_department:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail.project\
.recorded_department.department_name
else:
self.increment_col()
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail.project\
.sales_person
if project_detail.project.client_company:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.project.client_company.company_name
else:
self.increment_col()
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.project.project_name
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.engineer_name
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.engineer_name_kana
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_per_month
if project_detail.project.client_company and project_detail.project.client_company.billing_site:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail.project\
.client_company.billing_site.name
else:
self.increment_col()
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.company.company_name
if project_detail.engineer.engineer_histories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = \
project_detail.engineer.get_histories_by_date(self.month).payment_per_month
else:
self.increment_col()
if client_flag == 'BP':
if project_detail.engineer.company and project_detail.engineer.company.payment_site:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.company.payment_site.name
else:
self.increment_col()
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = "=J"\
+ str(i + self.current_row) + "-M" + str(i + self.current_row)
if project_detail.billing_rule == Rule.fixed:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_rule.name
else:
if project_detail.billing_bottom_base_hour:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] =\
str(project_detail.billing_bottom_base_hour) + "～"\
+ str(project_detail.billing_top_base_hour)
else:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_free_base_hour
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_per_hour
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_per_bottom_hour
if client_flag == 'BP':
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_per_top_hour
if project_detail.engineer.engineer_histories:
if project_detail.engineer.get_histories_by_date(self.month).payment_rule == Rule.fixed:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] =\
project_detail.engineer.get_histories_by_date(self.month).payment_rule.name
else:
if project_detail.engineer.get_histories_by_date(self.month).payment_bottom_base_hour:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] =\
str(project_detail.engineer.get_histories_by_date(self.month).
payment_bottom_base_hour) + "～" \
+ str(project_detail.engineer.get_histories_by_date(self.month).
payment_top_base_hour)
else:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = \
project_detail.engineer.get_histories_by_date(self.month).payment_free_base_hour
else:
self.increment_col()
if project_detail.engineer.engineer_histories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.get_histories_by_date(self.month).payment_per_hour
else:
self.increment_col()
if project_detail.engineer.engineer_histories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.get_histories_by_date(self.month).payment_per_bottom_hour
else:
self.increment_col()
if project_detail.engineer.engineer_histories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.get_histories_by_date(self.month).payment_per_top_hour
else:
self.increment_col()
if project_detail.engineer.engineer_histories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_start_day
else:
self.increment_col()
if project_detail.engineer.engineer_histories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.billing_end_day
else:
self.increment_col()
if project_detail.engineer.engineer_histories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.bp_order_no
else:
self.increment_col()
else:
self.increment_col()
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.project.start_date
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.project.end_date
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.project.estimation_no
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = ""
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = ""
if project_detail.project.end_user_company:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.project.end_user_company.company_name
else:
self.increment_col()
if project_detail.engineer.engineer_business_categories:
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = \
project_detail.engineer.engineer_business_categories[0].business_category.business_category_name
else:
self.increment_col()
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.engineer.get_age()
skill = ''
if project_detail.engineer.engineer_skills:
skills = [engineer_skill.skill.skill_name for engineer_skill in
project_detail.engineer.engineer_skills]
skill = ','.join(skills)
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = skill
self.ws[Point(i + self.current_row, self.increment_col()).get_cell_name()] = project_detail\
.project.project_name_for_bp
if client_flag == 'BP':
# 罫線の設定
for col in ['D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC',
'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ']:
self.ws[col + str(i + self.current_row)].border = Border(outline=True,
top=Side(style='dotted'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='dotted'))
# 日付の設定
for col in ['X', 'Y', 'AA', 'AB']:
self.ws[col + str(i + self.current_row)].number_format = 'yyyy/mm/dd'
# 金額の設定
for col in ['J', 'M', 'O']:
self.ws[col + str(i + self.current_row)].number_format = '¥#,###0'
# セルの設定
for col in ['K', 'N', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'Z', 'AC']:
self.ws[col + str(i + self.current_row)].alignment = Alignment(horizontal='center')
else:
# 罫線の設定
for col in ['D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB']:
self.ws[col + str(i + self.current_row)].border = Border(outline=True,
top=Side(style='dotted'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='dotted'))
# 日付の設定
for col in ['S', 'T']:
self.ws[col + str(i + self.current_row)].number_format = 'yyyy/mm/dd'
# 金額の設定
for col in ['J', 'M', 'N']:
self.ws[col + str(i + self.current_row)].number_format = '¥#,###0'
# セルの設定
for col in ['K', 'O', 'P', 'Q', 'R', 'S', 'T', 'U']:
self.ws[col + str(i + self.current_row)].alignment = Alignment(horizontal='center')
self.current_col = 1
i += 1
# 最終行用にiを1減らす
i -= 1
# 最終行の罫線の設定
if client_flag == 'BP':
for col in ['D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC',
'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ']:
self.ws[col + str(i + self.current_row)].border = Border(outline=True,
top=Side(style='dotted'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
else:
for col in ['D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB']:
self.ws[col + str(i + self.current_row)].border = Border(outline=True,
top=Side(style='dotted'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
self.ws.title = self.ws.title + '{}月'.format(self.month.month)
# エクセルを一時フォルダに保存
self.excel.save('案件一覧_{}.xlsx'.format(datetime.today().strftime("%Y%m%d")))
def increment_col(self):
col = self.current_col
self.current_col += 1
return col
def __init__(self, project_month):
super(BillingReport, self).__init__(project_month)
self.excel = Excel("billing.xlsx")
self.ws = self.excel.active
def __init__(self, project):
self.project = project
self.excel = Excel("estimation.xlsx")
self.current_row = 22
self.ws = self.excel.active
class BillingReport(BillingBaseReport):
project_billing_repository = ProjectBillingRepository()
def __init__(self, project_month):
super(BillingReport, self).__init__(project_month)
self.excel = Excel("billing.xlsx")
self.ws = self.excel.active
def billing_report_download(self):
self._create_excel()
return self.excel.download()
def _create_excel(self):
super(BillingReport, self)._create_excel()
# 宛名シート作成
self.address_sheet.create_address_sheet()
# エクセルを一時フォルダに保存
self.excel.save('06_請求書（{}）_{}.xlsx'.format(self.project_month.client_billing_no,
datetime.today().strftime("%Y%m%d")))
def write_top_part(self):
super(BillingReport, self).write_top_part()
# フォントを調整
self.ws[self.excel.get_defined_name_range("total_money_title")].font = \
Font(name="HGP明朝", size=14, underline="single")
self.ws[self.excel.get_defined_name_range("total_money")].font = \
Font(name="Century", size=14, underline="single")
def create_billing_details(self):
project_billings = self.project_billing_repository.find_billings_at_a_month(self.project_month.project_id,
self.project_month.project_month)
# 請求明細----------------------------------------------------------------------------------
for n, project_billing in enumerate(project_billings):
# 書式設定
self.create_billing_detail()
# 値代入
self.ws['A' + str(self.current_row)].value = (n + 1)
self.ws['E' + str(self.current_row)].value = project_billing.billing_content
self.ws['I' + str(self.current_row)].value = project_billing.billing_amount
self.ws['K' + str(self.current_row)].value = project_billing.billing_confirmation_money
self.ws['M' + str(self.current_row)].value = project_billing.remarks
# 次の行へ移動
self.current_row += 1
self.current_row += 1
# 行調整
if self.current_row < 25:
self.current_row = 25
# 小計--------------------------------------------------------------------------------------
# 値代入
self.create_subtotal_list('課税 小計', self.project_month.billing_confirmation_money)
if self.project_month.project.client_company.billing_tax != Tax.zero:
self.create_subtotal_list('消費税', self.project_month.tax_of_billing_confirmation_money())
self.create_subtotal_list('交通費等 非課税 小計', self.project_month.billing_transportation)
self.current_row += 1
# 行調整
if self.current_row < 39:
self.current_row = 39
# 累計--------------------------------------------------------------------------------------
# 値代入
self.ws['I' + str(self.current_row)].value = '計'
self.ws['K' + str(self.current_row)].value = (self.project_month.billing_confirmation_money or 0)\
+ self.project_month.tax_of_billing_confirmation_money()\
+ (self.project_month.billing_transportation or 0)
self.ws['K14'].value = '=K' + str(self.current_row)
# 書式設定
self.ws['I' + str(self.current_row)].font = Font(name="HGS明朝")
self.ws['K' + str(self.current_row)].font = Font(name="Century")
# 罫線
for row in self.ws['A' + str(self.current_row) + ":Q" + str(self.current_row)]:
for cell in row:
cell.border = Border(top=Side(style='thin'))
# セルの色変更
for column_num in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q']:
self.ws[column_num + str(self.current_row)].fill = PatternFill(patternType='solid', fgColor='E8F0F8')
self.current_row += 2
def create_bottom_part(self):
super(BillingReport, self).create_bottom_part()
# お支払先記入
# 値代入
self.ws['B' + str(self.current_row)].value = self.project_month.project.client_company.bank.text_for_document
class EstimatedReport(object):
def __init__(self, project):
self.project = project
self.excel = Excel("estimation.xlsx")
self.current_row = 22
self.ws = self.excel.active
def download(self):
self._create_excel()
return self.excel.download()
def _create_excel(self):
# 上部部分記載
self.write_estimated_content_rows()
# 作業詳細記載
self.create_project_detail_rows()
# 作業内容～備考
self.create_contract_content_rows()
# ロゴ画像貼り付け
img = Image('excel/templates/tp_logo.png')
img.width = img.width * 0.63
img.height = img.height * 0.63
self.ws.add_image(img, 'I10')
# 余白調整
self.ws.page_margins.top = 0.7
self.ws.page_margins.left = 0.3
self.ws.page_margins.right = 0.26
self.ws.page_margins.bottom = 0.2
self.ws.page_margins.header = 0.7
self.ws.page_margins.footer = 0.22
# 倍率の調整
self.ws.page_setup.scale = 86
# エクセルを一時フォルダに保存
self.excel.save('01_見積書（{}）_{}.xlsx'.format(
self.project.estimation_no,
datetime.today().strftime("%Y%m%d")))
def write_estimated_content_rows(self):
# 結合しているセルの罫線が消えるため、罫線を引き直す。
for rows in self.ws[self.excel.get_defined_name_range(
"estimated_content")]:
for cell in rows:
cell.border = Border(outline=True, bottom=Side(style='dashed'))
# 値を代入
self.ws[self.excel.get_defined_name_range(
"estimation_no")].value = self.project.estimation_no
self.ws[self.excel.get_defined_name_range(
"printed_date")].value = datetime.today().date()
if self.project.client_company:
self.ws[self.excel.get_defined_name_range("client_company_name")].value = \
self.project.client_company.company_name
self.ws[self.excel.get_defined_name_range(
"project_name")].value = self.project.project_name
# start_date、end_dateをdatetime型に変更
start_date = datetime(self.project.start_date.year,
self.project.start_date.month,
self.project.start_date.day)
end_date = datetime(self.project.end_date.year,
self.project.end_date.month,
self.project.end_date.day)
locale.setlocale(locale.LC_ALL, '')
self.ws[self.excel.get_defined_name_range(
"start_date")].value = strjpftime(start_date, ' %O%E年%m月%d日')
self.ws[self.excel.get_defined_name_range(
"end_date")].value = strjpftime(end_date, ' %O%E年%m月%d日')
if self.project.billing_timing:
self.ws[self.excel.get_defined_name_range("billing_timing")].value = \
self.project.billing_timing.name_for_report
if self.project.contract_form:
self.ws[self.excel.get_defined_name_range(
"contract_form")].value = self.project.contract_form.name
# 表示形式
self.ws[self.excel.get_defined_name_range(
"printed_date")].number_format = 'yyyy年m月d日'
# 請負ではない場合、「瑕疵担保期間」を非表示にする。
if self.project.contract_form != Contract.blanket:
self.ws.row_dimensions[19].hidden = True
# 書式が崩れるので修正
self.ws['J18'].border = Border(bottom=Side(style='medium'))
self.ws['L18'].border = Border(bottom=Side(style='medium'))
def create_project_detail_rows(self):
# 作業内容タイトル-------------------------------------------------------------------------
for column_num in range(2, 14):
self.ws.cell(
row=self.current_row,
column=column_num).border = Border(bottom=Side(style='medium'))
self.current_row += 1
self.create_project_detail_line(bottom_line_style='double')
self.current_row += 1
# 作業内容--------------------------------------------------------------------------------
for n, project_detail in enumerate(self.project.project_details):
# 書式作成
self.create_project_detail_style()
# 値代入
self.ws['B' + str(self.current_row)].value = (n + 1)
if project_detail.engineer_id:
self.ws['C' +
str(self.current_row
)].value = project_detail.engineer.engineer_name
else:
self.ws['C' +
str(self.current_row)].value = project_detail.work_name
self.ws['C' + str(self.current_row)].font = Font(name="ＭＳ ゴシック",
size=9,
bold=False)
self.ws['G' +
str(self.current_row)].value = project_detail.billing_money
self.ws['H' + str(self.current_row)].value = project_detail.remarks
# 表示形式
self.ws['G' + str(self.current_row)].number_format = '¥#,###.-'
self.current_row += 1
# 消費税----------------------------------------------------------------------------------
# 書式作成
self.create_project_detail_style()
# 値代入
if self.project.billing_tax:
tax = str(self.project.client_company.billing_tax.name)
else:
tax = ''
self.ws['C' + str(self.current_row)].value = '消費税（' + tax + '）'
self.ws['G' +
str(self.current_row
)].value = self.project.tax_of_estimated_total_amount()
# 表示形式
self.ws['C' + str(self.current_row)].number_format = '" "@'
self.ws['G' + str(self.current_row)].number_format = '¥#,###.-'
self.current_row += 1
# 合計金額--------------------------------------------------------------------------------
# 書式設定
self.create_project_detail_style()
# 値代入
self.ws['C' + str(self.current_row)].value = '合計'
self.ws['G' + str(self.current_row)].value = (self.project.estimated_total_amount or 0)\
+ self.project.tax_of_estimated_total_amount()
# 上部に存在する「金額」は、合計金額を参照するようにする。
self.ws['E14'].value = '=G' + str(self.current_row)
# 表示形式
self.ws['C' + str(self.current_row)].number_format = '" "@'
self.ws['G' + str(self.current_row)].number_format = '¥#,###.-'
self.current_row += 1
# その他調整------------------------------------------------------------------------------
# 空白行を作成
if len(self.project.project_details) < 3:
for n in range(3 - len(self.project.project_details)):
# 書式作成
self.create_project_detail_style()
self.current_row += 1
def create_project_detail_style(self):
# セルの結合
self.ws.merge_cells('C' + str(self.current_row) + ':F' +
str(self.current_row))
self.ws.merge_cells('H' + str(self.current_row) + ':M' +
str(self.current_row))
# フォント設定
self.ws['B' + str(self.current_row)].font = Font(name="ＭＳ ゴシック",
bold=True)
self.ws['C' + str(self.current_row)].font = Font(name="ＭＳ ゴシック",
bold=False)
self.ws['G' + str(self.current_row)].font = Font(name="ＭＳ ゴシック",
bold=False)
self.ws['H' + str(self.current_row)].font = Font(name="ＭＳ ゴシック",
bold=False)
# 書式設定
self.ws['B' + str(self.current_row)].alignment = Alignment(
horizontal='center')
self.ws['C' +
str(self.current_row)].alignment = Alignment(wrap_text=True)
self.ws['G' + str(self.current_row)].alignment = Alignment(
horizontal='right')
self.ws['H' +
str(self.current_row)].alignment = Alignment(wrap_text=True)
# 罫線
self.create_project_detail_line(bottom_line_style='thin')
def create_project_detail_line(self, bottom_line_style):
self.ws['B' + str(self.current_row)].border = Border(
left=Side(style='medium'),
right=Side(style='thin'),
bottom=Side(style=bottom_line_style))
for column_num in ['C', 'D', 'E', 'F']:
self.ws[column_num + str(self.current_row)].border = Border(
bottom=Side(style=bottom_line_style))
self.ws['G' + str(self.current_row)].border = Border(
left=Side(style='thin'),
right=Side(style='double'),
bottom=Side(style=bottom_line_style))
for column_num in ['H', 'I', 'J', 'K', 'L']:
self.ws[column_num + str(self.current_row)].border = Border(
bottom=Side(style=bottom_line_style))
self.ws['M' + str(self.current_row)].border = Border(
right=Side(style='medium'), bottom=Side(style=bottom_line_style))
def create_contract_content_rows(self):
self.create_contract_contents_row('作業内容',
self.project.contents,
top_line_style='double')
self.create_contract_contents_row('納品物', self.project.deliverables)
self.create_contract_contents_row('作業場所', self.project.working_place)
if self.project.billing_timing == BillingTiming.billing_by_month:
self.create_contract_contents_row('検査完了日', '毎月月末')
else:
self.create_contract_contents_row('検査完了日',
self.project.inspection_date)
self.create_contract_contents_row('作業責任者',
self.project.responsible_person)
self.create_contract_contents_row('品質管理担当者',
self.project.quality_control)
self.create_contract_contents_row('再委託先', self.project.subcontractor)
self.create_contract_contents_row('備考', self.project.remarks)
def create_contract_contents_row(self, title, value, top_line_style=None):
# セルの結合
self.ws.merge_cells('B' + str(self.current_row) + ':C' +
str(self.current_row))
self.ws.merge_cells('E' + str(self.current_row) + ':M' +
str(self.current_row))
# フォント設定
self.ws['B' + str(self.current_row)].font = Font(name="ＭＳ ゴシック",
bold=True)
self.ws['E' + str(self.current_row)].font = Font(name="ＭＳ ゴシック",
bold=False)
# 書式設定
self.ws['B' + str(self.current_row)].alignment = Alignment(
horizontal='distributed',
vertical='center',
wrap_text=True,
justifyLastLine=1)
self.ws['E' + str(self.current_row)].alignment = Alignment(
horizontal='left', vertical='center', wrap_text=True)
# 罫線
self.ws['B' + str(self.current_row)].border = Border(
top=Side(style=top_line_style),
left=Side(style='medium'),
bottom=Side(style='thin'))
self.ws['C' + str(self.current_row)].border = Border(
top=Side(style=top_line_style), bottom=Side(style='thin'))
self.ws['D' + str(self.current_row)].border = Border(
top=Side(style=top_line_style),
left=Side(style='thin'),
bottom=Side(style='thin'))
for column_num in ['E', 'F', 'G', 'H', 'I', 'J', 'K', 'L']:
self.ws[column_num + str(self.current_row)].border = Border(
top=Side(style=top_line_style), bottom=Side(style='thin'))
self.ws['M' + str(self.current_row)].border = Border(
top=Side(style=top_line_style),
right=Side(style='medium'),
bottom=Side(style='thin'))
# 値を代入
self.ws['B' + str(self.current_row)].value = title
self.ws['E' + str(self.current_row)].value = value
# 表示形式
if title == '検査完了日':
self.ws['E' + str(self.current_row)].number_format = 'yyyy年m月d日'
self.current_row += 1
class BillingDepartmentReport(object):
DEPARTMENT = 'department'
DEPOSIT = 'deposit'
RAW_DATA = 'raw_data'
def __init__(self, project_months, month):
self.project_months = project_months
self.month = month
self.excel = Excel("billing_department.xlsx")
self.current_row = 3
def download(self):
self._create_excel_department()
self._create_excel_deposit()
self._create_excel_raw_data()
return self.excel.download()
def _create_excel_department(self):
self.ws = self.excel.workbook['Sheet1']
group_name = ""
department_name = ""
company_name = ""
# "顧客"単位の各合計金額の宣言
transportation_total_company = 0
tax_transportation_total_company = 0
confirmation_total_company = 0
tax_total_company = 0
amount_total_company = 0
# "部"単位の各合計金額の宣言
transportation_total_department = 0
tax_transportation_total_department = 0
confirmation_total_department = 0
tax_total_department = 0
amount_total_department = 0
# "本部"単位の各合計金額の宣言
transportation_total_group = 0
tax_transportation_total_group = 0
confirmation_total_group = 0
tax_total_group = 0
amount_total_group = 0
# 全合計金額の宣言
transportation_total_all = 0
tax_transportation_total_all = 0
confirmation_total_all = 0
tax_total_all = 0
amount_total_all = 0
i = 0
for i, project_month in enumerate(self.project_months):
if i != 0:
# １つ上の"顧客"の値を比較
if company_name != project_month.project.client_company.company_name:
self.amount_total(i, 'company', company_name,
transportation_total_company,
tax_transportation_total_company,
confirmation_total_company,
tax_total_company, amount_total_company)
transportation_total_company = 0
tax_transportation_total_company = 0
confirmation_total_company = 0
tax_total_company = 0
amount_total_company = 0
# １つ上の"部"の値を比較
if department_name != project_month.project.recorded_department.department_name:
self.amount_total(i, 'department', department_name,
transportation_total_department,
tax_transportation_total_department,
confirmation_total_department,
tax_total_department,
amount_total_department)
transportation_total_department = 0
tax_transportation_total_department = 0
confirmation_total_department = 0
tax_total_department = 0
amount_total_department = 0
# １つ上の"本部"の値を比較
if group_name != project_month.project.recorded_department.group_name:
self.amount_total(i, 'group', group_name,
transportation_total_group,
tax_transportation_total_group,
confirmation_total_group,
tax_total_group, amount_total_group)
transportation_total_group = 0
tax_transportation_total_group = 0
confirmation_total_group = 0
tax_total_group = 0
amount_total_group = 0
# 値を代入
self.ws['B' + str(
i + self.current_row
)].value = project_month.project.recorded_department.group_name
self.ws['C' + str(
i + self.current_row
)].value = project_month.project.recorded_department.department_name
self.ws['D' + str(
i + self.current_row
)].value = project_month.project.client_company.company_name
self.ws['E' + str(
i + self.current_row)].value = project_month.client_billing_no
self.ws['F' + str(i + self.current_row
)].value = project_month.billing_printed_date
self.ws['G' + str(i + self.current_row
)].value = project_month.project.project_name
self.ws['H' +
str(i + self.current_row
)].value = project_month.billing_transportation or 0
self.ws['I' + str(
i + self.current_row
)].value = project_month.get_tax_of_billing_transportation()
self.ws['J' +
str(i + self.current_row
)].value = project_month.billing_confirmation_money
self.ws['K' + str(
i + self.current_row
)].value = project_month.tax_of_billing_confirmation_money()
self.ws['L' + str(i + self.current_row)].value = (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
self.ws['M' +
str(i +
self.current_row)].value = project_month.deposit_date
self.ws['N' + str(
i + self.current_row
)].value = project_month.project.client_company.bank.bank_name
# 金額セルの書式設定
self.money_format(i, 'department')
# 日付セルの書式設定
self.day_format(i, 'department')
# フォントの変更
for column_num in [
'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
'N'
]:
self.ws[column_num + str(i + self.current_row)].font = Font(
name='Meiryo UI')
# "顧客"単位の各合計金額
transportation_total_company += project_month.billing_transportation or 0
tax_transportation_total_company += project_month.get_tax_of_billing_transportation(
)
confirmation_total_company += project_month.billing_confirmation_money
tax_total_company += project_month.tax_of_billing_confirmation_money(
)
amount_total_company += (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
# "部"単位の各合計金額
transportation_total_department += project_month.billing_transportation or 0
tax_transportation_total_department += project_month.get_tax_of_billing_transportation(
)
confirmation_total_department += project_month.billing_confirmation_money
tax_total_department += project_month.tax_of_billing_confirmation_money(
)
amount_total_department += (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
# "本部"単位の各合計金額
transportation_total_group += project_month.billing_transportation or 0
tax_transportation_total_group += project_month.get_tax_of_billing_transportation(
)
confirmation_total_group += project_month.billing_confirmation_money
tax_total_group += project_month.tax_of_billing_confirmation_money(
)
amount_total_group += (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
# 全合計金額
transportation_total_all += project_month.billing_transportation or 0
tax_transportation_total_all += project_month.get_tax_of_billing_transportation(
)
confirmation_total_all += project_month.billing_confirmation_money
tax_total_all += project_month.tax_of_billing_confirmation_money()
amount_total_all += (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
group_name = project_month.project.recorded_department.group_name
department_name = project_month.project.recorded_department.department_name
company_name = project_month.project.client_company.company_name
self.create_outline(i, 'department')
# 最終合計行の追加
self.ws['B' +
str(i + self.current_row + 1)].value = company_name + " 合計"
self.ws['B' +
str(i + self.current_row + 2)].value = department_name + " 合計"
self.ws['B' + str(i + self.current_row + 3)].value = group_name + " 合計"
self.ws['B' + str(i + self.current_row + 4)].value = "総合計"
# 値を代入
self.ws['H' + str(i + self.current_row +
1)].value = transportation_total_company
self.ws['I' + str(i + self.current_row +
1)].value = tax_transportation_total_company
self.ws['J' + str(i + self.current_row +
1)].value = confirmation_total_company
self.ws['K' + str(i + self.current_row + 1)].value = tax_total_company
self.ws['L' +
str(i + self.current_row + 1)].value = amount_total_company
self.ws['H' + str(i + self.current_row +
2)].value = transportation_total_department
self.ws['I' + str(i + self.current_row +
2)].value = tax_transportation_total_department
self.ws['J' + str(i + self.current_row +
2)].value = confirmation_total_department
self.ws['K' +
str(i + self.current_row + 2)].value = tax_total_department
self.ws['L' +
str(i + self.current_row + 2)].value = amount_total_department
self.ws['H' + str(i + self.current_row +
3)].value = transportation_total_group
self.ws['I' + str(i + self.current_row +
3)].value = tax_transportation_total_group
self.ws['J' +
str(i + self.current_row + 3)].value = confirmation_total_group
self.ws['K' + str(i + self.current_row + 3)].value = tax_total_group
self.ws['L' + str(i + self.current_row + 3)].value = amount_total_group
self.ws['H' +
str(i + self.current_row + 4)].value = transportation_total_all
self.ws['I' + str(i + self.current_row +
4)].value = tax_transportation_total_all
self.ws['J' +
str(i + self.current_row + 4)].value = confirmation_total_all
self.ws['K' + str(i + self.current_row + 4)].value = tax_total_all
self.ws['L' + str(i + self.current_row + 4)].value = amount_total_all
# セルの結合
self.ws.merge_cells('B' + str(i + self.current_row + 1) + ':G' +
str(i + self.current_row + 1))
self.ws.merge_cells('B' + str(i + self.current_row + 2) + ':G' +
str(i + self.current_row + 2))
self.ws.merge_cells('B' + str(i + self.current_row + 3) + ':G' +
str(i + self.current_row + 3))
self.ws.merge_cells('B' + str(i + self.current_row + 4) + ':G' +
str(i + self.current_row + 4))
# セルの書式設定
self.ws['B' + str(i + self.current_row + 1)].alignment = Alignment(
horizontal='right')
self.ws['B' + str(i + self.current_row + 2)].alignment = Alignment(
horizontal='right')
self.ws['B' + str(i + self.current_row + 3)].alignment = Alignment(
horizontal='right')
self.ws['B' + str(i + self.current_row + 4)].alignment = Alignment(
horizontal='right')
for column_num in ['B', 'H', 'I', 'J', 'K', 'L', 'M', 'N']:
# セルの色変更
self.ws[column_num + str(i + self.current_row + 1)].fill = \
PatternFill(patternType='solid', fgColor='B7D6A3')
self.ws[column_num + str(i + self.current_row + 2)].fill = \
PatternFill(patternType='solid', fgColor='94C175')
self.ws[column_num + str(i + self.current_row + 3)].fill = \
PatternFill(patternType='solid', fgColor='70AD47')
self.ws[column_num + str(i + self.current_row + 4)].fill = \
PatternFill(patternType='solid', fgColor='5A8A39')
# フォントサイズの変更
self.ws[column_num + str(i + self.current_row + 1)].font = Font(
name='Meiryo UI', size=14, bold=True)
self.ws[column_num + str(i + self.current_row + 2)].font = Font(
name='Meiryo UI', size=14, bold=True)
self.ws[column_num + str(i + self.current_row + 3)].font = Font(
name='Meiryo UI', size=14, bold=True)
self.ws[column_num + str(i + self.current_row + 4)].font = Font(
name='Meiryo UI', size=14, bold=True)
for j in range(5):
self.money_format(i + j, 'department')
self.create_outline(i + j, 'department')
self.ws.title = '請求一覧（部）{}月'.format(self.month.month)
def _create_excel_deposit(self):
self.ws = self.excel.workbook['Sheet2']
company_name = ""
deposit_date = ""
bank_name = ""
# "部"単位の各合計金額の宣言
transportation_total_department = 0
tax_transportation_total_department = 0
confirmation_total_department = 0
tax_total_department = 0
amount_total_department = 0
# "全合計金額の宣言
transportation_total_all = 0
tax_transportation_total_all = 0
confirmation_total_all = 0
tax_total_all = 0
amount_total_all = 0
self.current_row = 3
i = 0
# 入金日、会社名順にソート
self.project_months.sort(key=lambda x: (x.deposit_date, x.project.
client_company.company_name))
for i, project_month in enumerate(self.project_months):
if i != 0:
if company_name != project_month.project.client_company.company_name:
self.ws['B' +
str(i +
self.current_row)].value = company_name + " 集計"
# 値を代入
self.ws['C' +
str(i + self.current_row
)].value = transportation_total_department
self.ws['D' +
str(i + self.current_row
)].value = tax_transportation_total_department
self.ws['E' + str(i + self.current_row
)].value = confirmation_total_department
self.ws['F' +
str(i +
self.current_row)].value = tax_total_department
self.ws['G' + str(
i + self.current_row)].value = amount_total_department
self.ws['H' +
str(i + self.current_row)].value = deposit_date
self.ws['I' + str(i + self.current_row)].value = bank_name
# 金額セルの書式設定
self.create_outline(i, 'deposit')
# セルの書式設定
self.money_format(i, 'deposit')
# 日付セルの書式設定
self.day_format(i, 'deposit')
# フォントの変更
for column_num in ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
self.ws[column_num +
str(i + self.current_row)].font = Font(
name='Meiryo UI')
# 合計金額の初期化
transportation_total_department = 0
tax_transportation_total_department = 0
confirmation_total_department = 0
tax_total_department = 0
amount_total_department = 0
self.current_row += 1
# 値を代入
self.ws['B' + str(i + self.current_row
)].value = project_month.project.project_name
self.ws['C' +
str(i + self.current_row
)].value = project_month.billing_transportation or 0
self.ws['D' + str(
i + self.current_row
)].value = project_month.get_tax_of_billing_transportation()
self.ws['E' + str(i + self.current_row)].value = (
project_month.billing_confirmation_money or 0)
self.ws['F' + str(
i + self.current_row
)].value = project_month.tax_of_billing_confirmation_money()
self.ws['G' + str(i + self.current_row)].value = (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
self.ws['H' +
str(i +
self.current_row)].value = project_month.deposit_date
self.ws['I' + str(
i + self.current_row
)].value = project_month.project.client_company.bank.bank_name
# セルの書式設定
self.create_outline(i, 'deposit')
self.ws.row_dimensions[i + self.current_row].hidden = True
# 金額セルの書式設定
self.money_format(i, 'deposit')
# 日付セルの書式設定
self.day_format(i, 'deposit')
# フォントの変更
for column_num in ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
self.ws[column_num + str(i + self.current_row)].font = Font(
name='Meiryo UI')
# "部"単位の各合計金額
transportation_total_department += project_month.billing_transportation or 0
tax_transportation_total_department += project_month.get_tax_of_billing_transportation(
)
confirmation_total_department += project_month.billing_confirmation_money or 0
tax_total_department += project_month.tax_of_billing_confirmation_money(
)
amount_total_department += (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
# "全合計金額"の各合計金額
transportation_total_all += project_month.billing_transportation or 0
tax_transportation_total_all += project_month.get_tax_of_billing_transportation(
)
confirmation_total_all += project_month.billing_confirmation_money or 0
tax_total_all += project_month.tax_of_billing_confirmation_money()
amount_total_all += (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
company_name = project_month.project.client_company.company_name
deposit_date = project_month.deposit_date
bank_name = project_month.project.client_company.bank.bank_name
# 最終合計行の追加
self.ws['B' +
str(i + self.current_row + 1)].value = company_name + " 集計"
self.ws['B' + str(i + self.current_row + 2)].value = "総計"
# 値を代入
self.ws['C' + str(i + self.current_row +
1)].value = transportation_total_department
self.ws['D' + str(i + self.current_row +
1)].value = tax_transportation_total_department
self.ws['E' + str(i + self.current_row +
1)].value = confirmation_total_department
self.ws['F' +
str(i + self.current_row + 1)].value = tax_total_department
self.ws['G' +
str(i + self.current_row + 1)].value = amount_total_department
self.ws['H' + str(i + self.current_row + 1)].value = deposit_date
self.ws['I' + str(i + self.current_row + 1)].value = bank_name
self.ws['C' +
str(i + self.current_row + 2)].value = transportation_total_all
self.ws['D' + str(i + self.current_row +
2)].value = tax_transportation_total_all
self.ws['E' +
str(i + self.current_row + 2)].value = confirmation_total_all
self.ws['F' + str(i + self.current_row + 2)].value = tax_total_all
self.ws['G' + str(i + self.current_row + 2)].value = amount_total_all
for j in range(3):
# セルの書式設定
self.create_outline(i + j, 'deposit')
self.money_format(i + j, 'deposit')
self.day_format(i + j, 'deposit')
# フォントの変更
for column_num in ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
self.ws[column_num + str(i + self.current_row + 1)].font = Font(
name='Meiryo UI')
self.ws[column_num + str(i + self.current_row + 2)].font = Font(
name='Meiryo UI')
self.ws.title = '請求一覧（入金日）{}月'.format(self.month.month)
def _create_excel_raw_data(self):
self.ws = self.excel.workbook['Sheet3']
self.current_row = 3
for i, project_month in enumerate(self.project_months):
# 値を代入
self.ws['B' + str(
i + self.current_row
)].value = project_month.project.recorded_department.group_name
self.ws['C' + str(
i + self.current_row
)].value = project_month.project.recorded_department.department_name
self.ws['D' + str(
i + self.current_row
)].value = project_month.project.client_company.company_name
self.ws['E' + str(
i + self.current_row)].value = project_month.client_billing_no
self.ws['F' + str(i + self.current_row
)].value = project_month.billing_printed_date
self.ws['G' + str(i + self.current_row
)].value = project_month.project.project_name
self.ws['H' +
str(i + self.current_row
)].value = project_month.billing_transportation or 0
self.ws['I' + str(
i + self.current_row
)].value = project_month.get_tax_of_billing_transportation()
self.ws[
'J' +
str(i + self.current_row
)].value = project_month.billing_confirmation_money or 0
self.ws['K' + str(
i + self.current_row
)].value = project_month.tax_of_billing_confirmation_money()
self.ws['L' + str(i + self.current_row)].value = (project_month.billing_confirmation_money or 0) \
+ project_month.tax_of_billing_confirmation_money() + (project_month.billing_transportation or 0)
self.ws['M' +
str(i +
self.current_row)].value = project_month.deposit_date
self.ws['N' + str(
i + self.current_row
)].value = project_month.project.client_company.bank.bank_name
# 金額セルの書式設定
self.money_format(i, 'raw_data')
# 日付セルの書式設定
self.day_format(i, 'raw_data')
# フォントの変更
for column_num in [
'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
'N'
]:
self.ws[column_num + str(i + self.current_row)].font = Font(
name='Meiryo UI')
self.create_outline(i, 'raw_data')
self.ws.title = '請求一覧（元データ）{}月'.format(self.month.month)
# エクセルを一時フォルダに保存
self.excel.save('請求一覧_{}.xlsx'.format(
datetime.today().strftime("%Y%m%d")))
def create_outline(self, index, kind):
if kind == self.DEPARTMENT or kind == self.RAW_DATA:
for col in [
'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
'N'
]:
self.ws[col + str(index + self.current_row)].border = Border(
outline=True,
top=Side(style='thin'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
elif kind == self.DEPOSIT:
for col in ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
self.ws[col + str(index + self.current_row)].border = Border(
outline=True,
top=Side(style='thin'),
left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
def day_format(self, index, kind):
if kind == self.DEPARTMENT or kind == self.RAW_DATA:
for col in ['F', 'M']:
self.ws[col +
str(index +
self.current_row)].number_format = 'yyyy/mm/dd'
elif kind == self.DEPOSIT:
self.ws['H' +
str(index + self.current_row)].number_format = 'yyyy/mm/dd'
def money_format(self, index, kind):
if kind == self.DEPARTMENT or kind == self.RAW_DATA:
for col in ['H', 'I', 'J', 'K', 'L']:
self.ws[col + str(index +
self.current_row)].number_format = '¥#,###0'
elif kind == self.DEPOSIT:
for col in ['C', 'D', 'E', 'F', 'G']:
self.ws[col + str(index +
self.current_row)].number_format = '¥#,###0'
def amount_total(self, index, col, name, transportation,
tax_transportation, confirmation, tax_total,
amount_total):
self.ws['B' + str(index + self.current_row)].value = name + " 合計"
# 値を代入
self.ws['H' + str(index + self.current_row)].value = transportation
self.ws['I' + str(index + self.current_row)].value = tax_transportation
self.ws['J' + str(index + self.current_row)].value = confirmation
self.ws['K' + str(index + self.current_row)].value = tax_total
self.ws['L' + str(index + self.current_row)].value = amount_total
# 金額セルの書式設定
self.create_outline(index, 'department')
# セルの結合
self.ws.merge_cells('B' + str(index + self.current_row) + ':G' +
str(index + self.current_row))
# セルの書式設定
self.ws['B' + str(index + self.current_row)].alignment = Alignment(
horizontal='right')
# セルの色変更
for column_num in ['B', 'H', 'I', 'J', 'K', 'L', 'M', 'N']:
if col == 'company':
self.ws[column_num + str(index + self.current_row)].fill = \
PatternFill(patternType='solid', fgColor='B7D6A3')
elif col == 'department':
self.ws[column_num + str(index + self.current_row)].fill = \
PatternFill(patternType='solid', fgColor='94C175')
else:
self.ws[column_num + str(index + self.current_row)].fill = \
PatternFill(patternType='solid', fgColor='70AD47')
self.create_outline(index, 'department')
self.money_format(index, 'department')
# フォントサイズの変更
for column_num in ['B', 'H', 'I', 'J', 'K', 'L', 'M', 'N']:
self.ws[column_num + str(index + self.current_row)].font = Font(
name='Meiryo UI', size=14, bold=True)
self.current_row += 1
def __init__(self, project_months, month):
self.project_months = project_months
self.month = month
self.excel = Excel("billing_department.xlsx")
self.current_row = 3
def __init__(self, project_detail):
self.project_detail = project_detail
self.excel = Excel("bp_order_report.xlsx")
self.address_sheet = AddressSheet(self)
class BpOrderReport(object):
engineer_history_repository = EngineerHistoryRepository()
def __init__(self, project_detail):
self.project_detail = project_detail
self.excel = Excel("bp_order_report.xlsx")
self.address_sheet = AddressSheet(self)
def download(self):
self._create_excel()
return self.excel.download()
def _create_excel(self):
self.ws = self.excel.workbook['注文書']
self.write_bp_order_original().create_bp_order_style()
self.ws = self.excel.workbook['注文書_副']
self.create_bp_order_style()
self.ws = self.excel.workbook['注文請書']
self.write_bp_order_confirmation().create_bp_order_style()
# 宛名を作成
self.address_sheet.create_address_sheet()
# エクセルを一時フォルダに保存
self.excel.save('02_注文書（{}_{}）_{}.xlsx'.format(
self.project_detail.engineer.engineer_name,
self.project_detail.bp_order_no,
datetime.today().strftime("%Y%m%d")))
def write_bp_order_original(self):
# 値を代入
engineer_history = self.engineer_history_repository.get_history_by_date(
self.project_detail.engineer.id,
self.project_detail.billing_start_day)
self.ws[self.excel.get_defined_name_range(
"bp_order_no")].value = self.project_detail.bp_order_no
self.ws[self.excel.get_defined_name_range(
"printed_date")].value = self.project_detail.billing_start_day
self.ws[self.excel.get_defined_name_range("bp_company_name")].value = \
self.project_detail.engineer.company.company_name
if self.project_detail.engineer.company.contract_date:
self.ws[self.excel.get_defined_name_range("contract_date")].value = \
strjpftime(datetime(
self.project_detail.engineer.company.contract_date.year,
self.project_detail.engineer.company.contract_date.month,
self.project_detail.engineer.company.contract_date.day
), '%O%E年%m月%d日')
self.ws[self.excel.get_defined_name_range("project_name_for_bp")].value = \
self.project_detail.project.project_name_for_bp
self.ws[self.excel.get_defined_name_range(
"start_date")].value = self.project_detail.billing_start_day
self.ws[self.excel.get_defined_name_range(
"end_date")].value = self.project_detail.billing_end_day
if engineer_history:
self.ws[self.excel.get_defined_name_range(
"payment_per_month"
)].value = engineer_history.payment_per_month
self.ws[self.excel.get_defined_name_range("payment_detail")].value = \
self.get_payment_detail_text(engineer_history)
self.ws[self.excel.get_defined_name_range(
"payment_condition"
)].value = engineer_history.payment_condition
self.ws[self.excel.get_defined_name_range(
"remarks")].value = engineer_history.remarks
return self
def write_bp_order_confirmation(self):
# 印紙の罫線を修正
self.ws['B3'].border = Border(left=Side(style='hair'),
right=Side(style='hair'))
self.ws['B4'].border = Border(left=Side(style='hair'),
right=Side(style='hair'),
bottom=Side(style='hair'))
return self
def create_bp_order_style(self):
bp_company_name = self.ws['B9']
contract_date = self.ws['B18']
printed_date = self.ws['J4']
start_date = self.ws['D26']
end_date = self.ws['D27']
payment_per_month = self.ws['D31']
# フォント
if self.ws.title == '注文書' or self.ws.title == '注文書_副':
bp_company_name.font = Font(name='ＭＳ 明朝',
size='10.5',
underline='single')
# 書式設定
contract_date.alignment = Alignment(horizontal='right')
payment_per_month.alignment = Alignment(horizontal='left')
# 表示形式
printed_date.number_format = 'yyyy"年"m"月"d"日"'
contract_date.number_format = 'yyyy"年"m"月"d"日"'
start_date.number_format = 'yyyy"年"m"月"d"日"'
end_date.number_format = 'yyyy"年"m"月"d"日"'
payment_per_month.number_format = '¥#,##0.-"/月額"'
# 罫線
for column_num in ['C', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L']:
self.ws[column_num +
str(37)].border = Border(bottom=Side(style='medium'))
# 倍率の調整
self.ws.page_setup.scale = 91
return self
def get_payment_detail_text(self, engineer_history):
text = self.project_detail.engineer.engineer_name + " 氏 "\
+ "¥{:,d}.-/月額".format(engineer_history.payment_per_month)
if engineer_history.payment_rule == Rule.variable:
text += "\n "\
+ "超過単価：" + "¥{:,d}.-/H".format(engineer_history.payment_per_top_hour) + " "\
+ "欠業単価：" + "¥{:,d}.-/H".format(engineer_history.payment_per_bottom_hour)
else:
text += "（固定）"
return text
def get_print_name(self):
return self.project_detail.engineer.company.print_name