app.py

import gradio as gr
import calendar
from datetime import datetime, timedelta
import tempfile
from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string

# 1) Define Indonesian month names
MONTHS_ID = [
    "Januari", "Februari", "Maret", "April", "Mei", "Juni",
    "Juli", "Agustus", "September", "Oktober", "November", "Desember"
]

# Core report-generation function
def generate_report(month, year, prev_file_path, template_file_path):
    if not prev_file_path or not template_file_path:
        raise gr.Error("Please upload both the previous report and the template.")

    # Determine month numbers
    month_num = MONTHS_ID.index(month) + 1
    prev_month_num = 12 if month_num == 1 else month_num - 1
    prev_year = year - 1 if month_num == 1 else year

    # Generate 31-day sequences for current and previous month
    start_current = datetime(year, month_num, 1)
    date_list_current = [start_current + timedelta(days=i) for i in range(31)]
    start_prev = datetime(prev_year, prev_month_num, 1)
    date_list_prev = [start_prev + timedelta(days=i) for i in range(31)]

    # Load template workbook and get TOTAL sheet
    wb_template = load_workbook(template_file_path)
    if 'TOTAL' not in wb_template.sheetnames:
        raise gr.Error("Template workbook must contain a 'TOTAL' sheet.")
    sheet = wb_template['TOTAL']
    sheet_target = wb_template['Target']
    sheet_piutang = wb_template['PIUTANG']


    # Set report title
    sheet.merge_cells('T1:AP1')
    title_cell = sheet['T1']
    title_cell.value = f"Laporan Keuangan {month} {year}"
    title_cell.alignment = title_cell.alignment.copy(horizontal='center', vertical='center')

    # Fill dates for current month in specified ranges
    current_ranges = [
        ('O', 9, 39),
        ('T', 9, 39),
        ('T', 48, 78),
        ('T', 131, 161)
    ]
    for col_letter, start_row, end_row in current_ranges:
        for idx, row in enumerate(range(start_row, end_row + 1)):
            cell = sheet[f"{col_letter}{row}"]
            cell.value = date_list_current[idx]
            cell.number_format = 'DD/MM/YYYY'

    # Fill dates for previous month only in T88:T118
    for idx, row in enumerate(range(88, 119)):
        cell = sheet[f"T{row}"]
        cell.value = date_list_prev[idx]
        cell.number_format = 'DD/MM/YYYY'

        # Copy block U88:AN161 from previous TOTAL sheet into template at same rows (U88:AN161)
    wb_prev = load_workbook(prev_file_path, data_only=True)
    if 'TOTAL' not in wb_prev.sheetnames:
        raise gr.Error("Previous workbook must contain a 'TOTAL' sheet.")
    sheet_prev = wb_prev['TOTAL']
    sheet_prev_target = wb_prev['Target']
    sheet_prev_piutang = wb_prev['PIUTANG']

    sheet_setting = wb_template['Setting']  # Access the 'Setting' sheet

    # Set the dropdown value in cell E6 under 'Setting' tab
    sheet_setting['E6'].value = month  # Set the value of E6 to the selected month



    src_start_col = column_index_from_string('U')
    src_end_col = column_index_from_string('AN')
    src_start_row = 131
    src_end_row = 162  # rows 88 through 161
    dest_start_row = 88  # same starting row in template
    row_offset = dest_start_row - src_start_row  # should be 0

    for col in range(src_start_col, src_end_col + 1):
        for row in range(src_start_row, src_end_row + 1):
            val = sheet_prev.cell(row=row, column=col).value
            # Write into same row in template
            dest_cell = sheet.cell(row=row + row_offset, column=col)
            dest_cell.value = val


    # 0) Copy C3:I8 -> C22:I27 on Target sheet (values only)
    c_start, c_end = column_index_from_string('C'), column_index_from_string('I')
    offset_t1 = 22 - 3
    for col in range(c_start, c_end + 1):
        for row in range(3, 9):
            val = sheet_prev_target.cell(row=row, column=col).value
            sheet_target.cell(row=row + offset_t1, column=col).value = val

    # 1) Copy C11:G15 -> C30:G34 on Target sheet (values only)
    c_start2, c_end2 = column_index_from_string('C'), column_index_from_string('G')
    offset_t2 = 30 - 11
    for col in range(c_start2, c_end2 + 1):
        for row in range(11, 16):
            val = sheet_prev_target.cell(row=row, column=col).value
            sheet_target.cell(row=row + offset_t2, column=col).value = val

    # 2) Copy C11:G15 -> C30:G34 (from previous Target sheet)
    sc2, ec2 = column_index_from_string('C'), column_index_from_string('G')
    offset2 = 30 - 11
    for col in range(sc2, ec2+1):
        for row in range(11,16):
            val = sheet_prev_target.cell(row=row, column=col).value
            sheet_target.cell(row=row+offset2, column=col).value = val

    # 3) Copy AK38:AQ39 -> B39:H40 (from previous TOTAL sheet)
    ak_columns = ['AK','AL','AM','AN','AO','AP','AQ']
    for i, src_row in enumerate([38,39]):
        for j, col_letter in enumerate(ak_columns):
            col_idx = column_index_from_string(col_letter)
            val = sheet_prev.cell(row=src_row, column=col_idx).value
            sheet_target.cell(row=39+i, column=2+j).value = val

    # 4) PIUTANG: entries on last calendar day of prev month -> B49:D?
    last_day = calendar.monthrange(prev_year, prev_month_num)[1]
    rows = []
    for r in range(2, sheet_prev_piutang.max_row+1):
        d = sheet_prev_piutang.cell(row=r, column=1).value
        if isinstance(d, datetime) and d.month==prev_month_num and d.year==prev_year and d.day==last_day:
            rows.append(r)
    for idx, r in enumerate(rows):
        sheet_target.cell(row=49+idx, column=2).value = sheet_prev_piutang.cell(row=r, column=1).value
        sheet_target.cell(row=49+idx, column=3).value = sheet_prev_piutang.cell(row=r, column=2).value
        sheet_target.cell(row=49+idx, column=4).value = sheet_prev_piutang.cell(row=r, column=3).value

    
    for col in ['A', 'B', 'C', 'E', 'F', 'G']:
        for row in range(3, 501):  # rows 3 to 100 inclusive
            sheet_piutang[f"{col}{row}"].value = None


    # Save and return temporary file path
    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
    wb_template.save(tmp.name)
    tmp.close()
    return tmp.name
    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
    wb_template.save(tmp.name)
    tmp.close()
    return tmp.name

# Gradio UI components
dropdown_month = gr.Dropdown(
    choices=MONTHS_ID,
    label="Pilih Bulan",
    value=MONTHS_ID[datetime.now().month - 1]
)
number_year = gr.Number(
    label="Tahun",
    value=datetime.now().year,
    precision=0
)
file_prev = gr.File(
    label="Laporan Bulan Sebelumnya (.xlsx)",
    file_count="single",
    type="filepath"
)
file_template = gr.File(
    label="Template Laporan (.xlsx)",
    file_count="single",
    type="filepath"
)

iface = gr.Interface(
    fn=generate_report,
    inputs=[dropdown_month, number_year, file_prev, file_template],
    outputs=[gr.File(label="Unduh Laporan", type="filepath")],
    title="Pembuatan Laporan J&T Otomatis",
    allow_flagging='never'
)


if __name__ == '__main__':
  iface.launch(share=True, server_name="0.0.0.0", server_port=7860)