#!/usr/bin/env python3 """ export_dochazka.py Reads JSON from stdin: {"schedule": {...}, "month": N, "year": N} Directly manipulates dochazka_template.xlsx XML — styles.xml is never touched, so Excel never shows a "repaired styles" warning. """ import sys, json, io, zipfile, re import xml.etree.ElementTree as ET from datetime import date as Date, timedelta TEMPLATE_PATH = __file__.replace('export_dochazka.py', 'dochazka_template.xlsx') MONTH_NAMES = { 1:'Leden', 2:'Únor', 3:'Březen', 4:'Duben', 5:'Květen', 6:'Červen', 7:'Červenec', 8:'Srpen', 9:'Září', 10:'Říjen', 11:'Listopad', 12:'Prosinec', } OP_CODE = 'OP24146101755' NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' ET.register_namespace('', NS) # Financial constants (from sichtovnice.py) KC_KM = 4 # Kč per km STRAVNE_12H = 236 # meal allowance for a 12+h shift STRAVNE_8H = 155 # meal allowance for an 8h workday DAN = 0.15 # tax factor → 1.15 multiplier INTERNET = 500 # Janouš Petr monthly internet reimbursement # Template name list — rows 8-19 of dochazka_template.xlsx. # Only these people are exported, in this order. TEMPLATE_NAMES = [ 'Dittrich Vladimír', 'Teslík Hynek', 'Kohl David', 'Vörös Pavel', 'Janouš Petr', 'Dvořák Václav', 'Toman Milan', 'Hanzlík Marek', 'Vondrák Pavel', 'Čeleda Olda', 'Žemlička Miroslav', 'Franek Lukáš', ] # Per-person static data from lidijson.py (auto type, km per shift) PERSON_DATA = { 'Žemlička Miroslav': {'auto': 'AUS', 'km_tkb': 40, 'km_sat': 0}, 'Franek Lukáš': {'auto': 'AUS', 'km_tkb': 40, 'km_sat': 0}, 'Dvořák Václav': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Toman Milan': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Kohl David': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Milisavljevič Jovica':{'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Hanzlík Marek': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Pauzer Libor': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Chudoba Jan': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Jór Leoš': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Gschray Jiří': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Rozman František': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Vörös Pavel': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Janouš Petr': {'auto': 'AUS', 'km_tkb': 70, 'km_sat': 60}, 'Strach Jiří': {'auto': 'AUV', 'km_tkb': 30, 'km_sat': 0}, 'Dittrich Vladimír': {'auto': 'AUV', 'km_tkb': 30, 'km_sat': 0}, 'Schwarz Richard': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Glaser Ondřej': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Herbst David': {'auto': 'AUV', 'km_tkb': 76, 'km_sat': 66}, 'Ryba Ondřej': {'auto': 'AUV', 'km_tkb': 100, 'km_sat': 80}, 'Zábranský Petr': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Šůna Jiří': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Čeleda Olda': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Vondrák Pavel': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Teslík Hynek': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, 'Štefan Robert': {'auto': 'AUS', 'km_tkb': 0, 'km_sat': 0}, } # Template layout (1-based row numbers) TPL_FIRST = 8 # first data row TPL_LAST = 19 # last data row (12 people) TPL_CELKEM = 20 TPL_HIDDEN1 = 22 TPL_HIDDEN2 = 23 TPL_IT_HDR = 24 TPL_IT1 = 25 # first IT person # ────────────────────────────────────────────────────────────── def get_czech_holidays(year): fixed = { Date(year,1,1), Date(year,5,1), Date(year,5,8), Date(year,7,5), Date(year,7,6), Date(year,9,28), Date(year,10,28), Date(year,11,17), Date(year,12,24), Date(year,12,25), Date(year,12,26), } a=year%19; b=year//100; c=year%100; d=b//4; e=b%4 f=(b+8)//25; g=(b-f+1)//3; h=(19*a+b-d-g+15)%30 i=c//4; k=c%4; l=(32+2*e+2*i-h-k)%7; m=(a+11*h+22*l)//451 mo=(h+l-7*m+114)//31; dy=((h+l-7*m+114)%31)+1 easter = Date(year, mo, dy) fixed.add(easter - timedelta(days=2)) fixed.add(easter + timedelta(days=1)) return fixed def split_day(hours): r=o=0; z=hours while z>0: if z>=6: r+=6; z-=6 else: r+=z; z=0 if z>=6: o+=6; z-=6 else: o+=z; z=0 return r, o def split_night(hours): r=o=n=0; z=hours while z>0: if z>=2: o+=2; z-=2 else: o+=z; z=0 if z>=8: n+=8; z-=8 else: n+=z; z=0 if z>=2: r+=2; z-=2 else: r+=z; z=0 return r, o, n # ────────────────────────────────────────────────────────────── def compute(schedule, month, year): holidays = get_czech_holidays(year) day_index = schedule['dayIndex'] people = schedule['people'] month_days = [d for d in day_index if d['month']==month and d['year']==year] work_days = sum(1 for d in month_days if not d['weekend'] and Date(d['year'],d['month'],d['day']) not in holidays) fpd = work_days * 8 tkb_results = [] # Only include people that are in the template — in template order tkb_by_name = {p['name']: p for p in people if p.get('group')=='TKB'} tkb_people = [tkb_by_name[name] for name in TEMPLATE_NAMES if name in tkb_by_name] for person in tkb_people: r=o=n=0; dov=nem=otc=0; minus_fpd=0 sichet_12h = 0 # shifts with ≥12 h sichet_under_12h = 0 # shifts with <12 h uzavery = 0 # 'U' days (closures) for d in month_days: v = (person['data'].get(str(d['idx'])) or {}).get('v', '') is_w = (not d['weekend'] and Date(d['year'],d['month'],d['day']) not in holidays) if v=='A': dr,do=split_day(12); r+=dr; o+=do sichet_12h += 1 elif v=='B': nr,no,nn=split_night(12); r+=nr; o+=no; n+=nn sichet_12h += 1 elif v=='D': dov+=1; minus_fpd += 8 if is_w else 0 elif v=='N': nem+=1; minus_fpd += 8 if is_w else 0 elif v=='O': otc+=1; minus_fpd += 8 if is_w else 0 elif v=='U': uzavery += 1 elif v not in ('x','U','') and v: try: h=float(v) if h>0: dr,do=split_day(h); r+=dr; o+=do if h >= 12: sichet_12h += 1 else: sichet_under_12h += 1 except (ValueError,TypeError): pass # PMS stored at negative dayIdx = -month # PMS = práce mimo směnu: hours added to R/O but NOT counted as shifts pms = (person['data'].get(str(-month)) or {}).get('v','') if pms=='A': pr,po=split_day(12); r+=pr; o+=po elif pms=='B': pr,po,pn=split_night(12); r+=pr; o+=po; n+=pn elif pms: try: ph=float(pms) if ph>0: pr,po=split_day(ph); r+=pr; o+=po except (ValueError,TypeError): pass total = r+o+n o_navic = total - fpd + minus_fpd r_fpd, o_fpd, n_fpd = r, o, n if o_navic > 0: pom = o_navic if r_fpd >= pom: r_fpd -= pom; pom = 0 else: pom -= r_fpd; r_fpd = 0 o_fpd = max(0, o_fpd - pom) absence = ' '.join(filter(None, [ f'{dov}D' if dov else '', f'{nem}N' if nem else '', f'{otc}O' if otc else '', ])) + (' ' if (dov or nem or otc) else '') # --- Financial calculations (sichtovnice.py) --- # Per-person FPD days (adjusted for D/N/O on working days) person_fpd_dny = work_days - (minus_fpd / 8) # R (Jine1): meal allowance supplement # 1.15 × (sichet_12h × 236 + sichet_<12h × 155 − fpd_per_person × 155) stravne_doplatek = round( (1 + DAN) * ( sichet_12h * STRAVNE_12H + sichet_under_12h * STRAVNE_8H - person_fpd_dny * STRAVNE_8H ), 2) # S (Jine2): transport money — only AUV drivers pdata = PERSON_DATA.get(person['name'], {'auto':'AUS','km_tkb':0,'km_sat':0}) kc_doprava = 0 if pdata['auto'] == 'AUV': sichet_tkb = sichet_12h + sichet_under_12h # no SAT tracking kc_tkb = pdata['km_tkb'] * KC_KM * sichet_tkb kc_uzavera = uzavery * pdata['km_tkb'] * KC_KM kc_doprava = kc_tkb + kc_uzavera # P (Indiv1): closure count text (+ Internet reimbursement for Janouš Petr) # indiv1_type: 'str', 'num', or None (empty) if person['name'] == 'Janouš Petr': if uzavery: indiv1_value = f' {uzavery}U+{INTERNET}' indiv1_type = 'str' else: indiv1_value = INTERNET indiv1_type = 'num' elif uzavery: indiv1_value = f' {uzavery}U' indiv1_type = 'str' else: indiv1_value = None indiv1_type = None tkb_results.append({ 'name': person['name'], 'r': r_fpd, 'o': o_fpd, 'n': n_fpd, 'o_navic': o_navic, 'absence': absence.strip(), 'indiv1_value': indiv1_value, 'indiv1_type': indiv1_type, 'jine1': stravne_doplatek, 'jine2': kc_doprava, }) it_order = ['it-glaser','it-janous','it-stefan','it-voros'] it_map = {p['id']:p for p in people if p.get('group')=='IT'} it_results = [] for pid in it_order: person = it_map.get(pid) if not person: it_results.append({'name':pid.split('-')[1].capitalize(),'hours':0,'prace':0}) continue hours = 0 for d in month_days: v = (person['data'].get(str(d['idx'])) or {}).get('v','') if v in ('A','B'): hours += 12 elif v: try: h=float(v); hours += h if h>0 else 0 except (ValueError,TypeError): pass pms = (person['data'].get(str(-month)) or {}).get('v','') prace = 0 try: prace = float(pms) if pms else 0 except (ValueError,TypeError): pass it_results.append({'name':person['name'],'hours':hours,'prace':prace}) return {'month':month,'year':year,'work_days':work_days,'fpd':fpd, 'tkb':tkb_results,'it':it_results} # ────────────────────────────────────────────────────────────── # Shared strings helpers # ────────────────────────────────────────────────────────────── def parse_ss(xml_bytes): root = ET.fromstring(xml_bytes) strings = [] for si in root.findall(f'{{{NS}}}si'): t = si.find(f'.//{{{NS}}}t') strings.append(t.text or '' if t is not None else '') return root, strings def get_or_add(root, strings, text): if text in strings: return strings.index(text) strings.append(text) si = ET.SubElement(root, f'{{{NS}}}si') t = ET.SubElement(si, f'{{{NS}}}t') t.text = text root.set('count', str(len(strings))) root.set('uniqueCount', str(len(strings))) return len(strings) - 1 def serialize_ss(root): return b'\n' + ET.tostring(root, encoding='unicode').encode('utf-8') # ────────────────────────────────────────────────────────────── # Sheet XML helpers # ────────────────────────────────────────────────────────────── def col_letter(col_idx): # 1-based letters = '' while col_idx > 0: col_idx, rem = divmod(col_idx - 1, 26) letters = chr(65 + rem) + letters return letters def cell_ref(col, row): return f'{col_letter(col)}{row}' def set_cell_num(row_el, col, row, value): """Set a numeric cell value; creates or updates the cell.""" ref = cell_ref(col, row) for c in list(row_el): if c.get('r') == ref: c.set('t', 'n') v = c.find(f'{{{NS}}}v') if v is None: v = ET.SubElement(c, f'{{{NS}}}v') v.text = str(value) # Remove formula if any f = c.find(f'{{{NS}}}f') if f is not None: row_el.remove(f) return # Cell doesn't exist — shouldn't happen for template cells, but handle gracefully c = ET.SubElement(row_el, f'{{{NS}}}c') c.set('r', ref); c.set('t', 'n') v = ET.SubElement(c, f'{{{NS}}}v'); v.text = str(value) def set_cell_ss(row_el, col, row, ss_idx): """Set a shared-string cell.""" ref = cell_ref(col, row) for c in list(row_el): if c.get('r') == ref: c.set('t', 's') v = c.find(f'{{{NS}}}v') if v is None: v = ET.SubElement(c, f'{{{NS}}}v') v.text = str(ss_idx) f = c.find(f'{{{NS}}}f') if f is not None: row_el.remove(f) return c = ET.SubElement(row_el, f'{{{NS}}}c') c.set('r', ref); c.set('t', 's') v = ET.SubElement(c, f'{{{NS}}}v'); v.text = str(ss_idx) def set_cell_formula(row_el, col, row, formula): ref = cell_ref(col, row) for c in list(row_el): if c.get('r') == ref: c.attrib.pop('t', None) # Remove existing v v = c.find(f'{{{NS}}}v') if v is not None: c.remove(v) f = c.find(f'{{{NS}}}f') if f is None: f = ET.SubElement(c, f'{{{NS}}}f') f.text = formula return c = ET.SubElement(row_el, f'{{{NS}}}c') c.set('r', ref) f = ET.SubElement(c, f'{{{NS}}}f'); f.text = formula def clear_cell(row_el, col, row): """Remove value from a cell (keep style/element, clear v and t).""" ref = cell_ref(col, row) for c in list(row_el): if c.get('r') == ref: c.attrib.pop('t', None) v = c.find(f'{{{NS}}}v') if v is not None: c.remove(v) f = c.find(f'{{{NS}}}f') if f is not None: c.remove(f) return # ────────────────────────────────────────────────────────────── def generate_excel(data): with open(TEMPLATE_PATH, 'rb') as f: tpl_bytes = f.read() tpl_zip = io.BytesIO(tpl_bytes) with zipfile.ZipFile(tpl_zip) as zin: filenames = zin.namelist() ss_bytes = zin.read('xl/sharedStrings.xml') if 'xl/sharedStrings.xml' in filenames else None sheet_bytes = zin.read('xl/worksheets/sheet1.xml') wb_bytes = zin.read('xl/workbook.xml') all_bytes = {f: zin.read(f) for f in filenames} # ── Shared strings ── ss_root, ss_list = parse_ss(ss_bytes) if ss_bytes else (None, []) def ss(text): return get_or_add(ss_root, ss_list, text) month_name = MONTH_NAMES[data['month']] month_idx = ss(month_name) op_idx = ss(OP_CODE) # ── Sheet XML ── sheet_root = ET.fromstring(sheet_bytes) sd = sheet_root.find(f'{{{NS}}}sheetData') # Build row map rows = {int(r.get('r')): r for r in sd.findall(f'{{{NS}}}row')} n_people = len(data['tkb']) n_tmpl = TPL_LAST - TPL_FIRST + 1 # 12 # ── ROW 3: month metadata ── r3 = rows[3] set_cell_ss (r3, 4, 3, month_idx) # D3 = month name set_cell_num(r3, 6, 3, 8) # F3 = direction (always 8) set_cell_num(r3, 7, 3, data['work_days']) # G3 set_cell_num(r3, 8, 3, data['fpd']) # H3 # ── DATA ROWS 8–19 ── for i in range(n_tmpl): row_num = TPL_FIRST + i r = rows[row_num] if i < n_people: p = data['tkb'][i] # Clear all value-bearing cells first for col in range(2, 26): clear_cell(r, col, row_num) # Write our values set_cell_ss (r, 2, row_num, op_idx) set_cell_ss (r, 3, row_num, ss(p['name'])) set_cell_num(r, 4, row_num, p['r']) set_cell_num(r, 6, row_num, p['o']) set_cell_num(r, 8, row_num, p['n']) set_cell_num(r,10, row_num, p['o_navic']) # P (indiv1): closure text/internet — string OR numeric OR blank if p['indiv1_type'] == 'str': set_cell_ss(r, 16, row_num, ss(p['indiv1_value'])) elif p['indiv1_type'] == 'num': set_cell_num(r, 16, row_num, p['indiv1_value']) set_cell_num(r,18, row_num, p['jine1']) # R: stravné doplatek set_cell_num(r,19, row_num, p['jine2']) # S: transport (AUV) set_cell_num(r,23, row_num, 0) if p['absence']: set_cell_ss(r, 24, row_num, ss(p['absence'])) else: # Fewer people than template: clear the row for col in range(2, 26): clear_cell(r, col, row_num) # Mark as hidden r.set('hidden', '1') # n_people ≤ n_tmpl always (filtered to TEMPLATE_NAMES in compute()). # Unused template rows are cleared and hidden above. Celkem row stays at TPL_CELKEM. celkem_row = TPL_CELKEM it_first = TPL_IT1 # ── CELKEM row ── ck = rows[celkem_row] for col in range(4, 24): clear_cell(ck, col, celkem_row) for col in [4, 6, 8, 10]: cl = col_letter(col) set_cell_formula(ck, col, celkem_row, f'SUBTOTAL(9,{cl}{TPL_FIRST}:{cl}{TPL_LAST})') for col in [5, 7, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23]: set_cell_num(ck, col, celkem_row, 0) # ── IT section (hidden rows already shifted) ── # IT people (rows it_first to it_first+3) it_names = ['Glaser', 'Janouš', 'Štefan', 'Vörös'] for j, it in enumerate(data['it']): rn = it_first + j r = rows.get(rn) if r is None: continue set_cell_ss (r, 2, rn, ss(it_names[j] if j < len(it_names) else it['name'])) set_cell_num(r, 3, rn, it['hours']) if it.get('prace', 0): set_cell_num(r, 4, rn, it['prace']) else: clear_cell(r, 4, rn) # ── Rebuild sheetData in row order ── for old_row in list(sd): sd.remove(old_row) for rn in sorted(rows): sd.append(rows[rn]) # ── Update sheet name in workbook.xml ── new_sheet_name = f"{data['month']:02d}_{data['year']}" wb_text = wb_bytes.decode('utf-8') wb_text = re.sub(r'name="[^"]*"', f'name="{new_sheet_name}"', wb_text, count=1) # ── Serialize ── sheet_out = (b'\n' + ET.tostring(sheet_root, encoding='unicode').encode('utf-8')) ss_out = serialize_ss(ss_root) if ss_root is not None else ss_bytes # ── Repack zip ── out_buf = io.BytesIO() with zipfile.ZipFile(out_buf, 'w', zipfile.ZIP_DEFLATED) as zout: for fname, fbytes in all_bytes.items(): if fname == 'xl/worksheets/sheet1.xml': zout.writestr(fname, sheet_out) elif fname == 'xl/sharedStrings.xml': zout.writestr(fname, ss_out) elif fname == 'xl/workbook.xml': zout.writestr(fname, wb_text.encode('utf-8')) else: zout.writestr(fname, fbytes) out_buf.seek(0) return out_buf.read() # ────────────────────────────────────────────────────────────── if __name__ == '__main__': inp = json.loads(sys.stdin.buffer.read()) result = compute(inp['schedule'], inp['month'], inp['year']) sys.stdout.buffer.write(generate_excel(result))