5jrdZddlZddlZddlZddlZddlZddlZddlmZmZm Z ddl m Z ddl m Z  ddlZddlZddlZe ej+j,Ze ej0j3ded z Ze ej0j3d ed z Zed z Zed z Zedz ZdZdZ dddZ!dBde de"fdZ#dBde$de ddfdZ%de"de&fdZ'de"de&dede(fdZ)de$fdZ*d ed!e+fd"Z,de$d ede"d!e+de(f d#Z-de de"fd$Z.de"fd%Z/de$de"fd&Z0de$de"fd'Z1de$de"fd(Z2d)e&de&fd*Z3d+ed,ede&fd-Z4de$d+ed,ede&fd.Z5de&fd/Z6d0e&de&fd1Z7dddd2de$d3e$d+ed,ede&f d4Z8d5Z9dd6d7de$d ed8e(d9ejtd:e&d;e(de$fd<Z;d=e"d ed8e(d9ejtde$f d>Ze?dAk(re>yy#e$r!Zej&dedYdZ[dZ[wwxYw)Du scada_report_scheduler.py — Scheduled SCADA report execution + email delivery (DVI-486, SCADA View Reporting ST4). Part of SCADA View Reporting (DVI-474). A standalone, systemd-timer driven CLI that runs once per invocation, scans the saved report schedules (``scada_report_schedules.json``, authored by ST3 / DVI-484), generates the XLSX for every schedule that is *due*, persists it under ``reports/``, and — when the schedule opts in — emails it as a base64 file attachment via Microsoft Graph. Modeled on ``notification_scheduler.py``: it imports the small self-contained engine modules (``scada_report`` for report/XLSX generation, ``notifications`` for the Graph send) and never imports app.py, which starts background loops at import time and is unsuitable for a one-shot job. Due-date / cadence model ------------------------ A schedule's ``cadence`` drives BOTH the report's data window and how often it fires. The window always ends on *yesterday* (the last complete log day): cadence interval data window (run date = today) last_week 7 days [today-7, today-1] last_month 30 days [today-30, today-1] custom N days [today-N, today-1] (N = schedule.custom_days) There is no separate firing anchor stored on the schedule, so the run log (``scada_report_log.json``) doubles as the last-run tracker: a schedule is due when it has never run, or when its most recent successful run is at least ``interval`` days old. First invocation after a schedule is created runs it immediately. Idempotency ----------- File-based, mirroring the ``notification_log.json`` pattern but in its own ``scada_report_log.json`` (the report run shape differs from notification sends). The idempotency key is ``(schedule_id, run_date)`` — a schedule runs at most once per calendar day, so a second invocation the same day is a no-op. A run is recorded unless the email was attempted and failed transiently (so a failed send is retried on the next daily pass); permission/config issues (no recipients, oversized attachment) are recorded to avoid daily regeneration. Attachment size guard --------------------- Graph's simple sendMail caps total message size near 4 MB. If a generated XLSX exceeds ``MAX_ATTACHMENT_BYTES`` (3 MB) the report is still persisted to ``reports/`` but the email is skipped with a warning (never silently truncated). Recipient model (DVI-487 — final shape) --------------------------------------- The Settings → Notifications pane drives two global lists plus per-schedule toggles. Recipients are OR-merged and de-duplicated by lowercased email from: 1. ``schedule["recipients"]`` — per-schedule list, included when ``schedule["notify"]`` (User notifications) is on 2. ``scada_enforced_recipients.json`` — global enforced list, always included (every report) 3. ``scada_external_recipients.json`` — global external list, included only when ``schedule["external"]`` is on Each global file may be a flat JSON list ``["a@b.com", ...]`` or an object ``{"recipients": ["a@b.com", ...]}``. Missing files resolve to no recipients. A report is emailed when ``notify`` OR ``external`` is set; if neither is on the report is persisted only. When sending but no recipients resolve, the email is skipped with a warning. Invocation contract (for the systemd timer in services/) -------------------------------------------------------- * Command: /bin/python /var/www/html/togen/scada_report_scheduler.py * Working directory: /var/www/html/togen (so ``import scada_report`` / ``import notifications`` resolve and the JSON state files are found alongside them) * Required env vars: AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, AZURE_TENANT_ID (Graph fetch + send), AZURE_MAIL_FROM (send-from address) * Optional env vars: SCADA_MAILBOX, SCADA_REPORT_SCHEDULES, SCADA_REPORTS_DIR * Cadence: run daily; the per-schedule cadence decides what is due. * Exit code: 0 on a clean scan (including "nothing due"); non-zero only on an unexpected error (e.g. unreadable schedules file). Usage: python scada_report_scheduler.py [--dry-run] [--date YYYY-MM-DD] [--schedules PATH] [--log-level LEVEL] --dry-run Generate + persist reports and resolve recipients, but do not send email or write the run log. --date Override "today" (for testing); defaults to the system date. --schedules Override the schedules JSON path. N)datedatetime timedelta)Path)quotez!Cannot import a required module: z Run this script from the togen app directory (the one containing scada_report.py and notifications.py), e.g. WorkingDirectory=/var/www/html/togen.SCADA_REPORT_SCHEDULESzscada_report_schedules.jsonSCADA_REPORTS_DIRreportszscada_report_log.jsonzscada_enforced_recipients.jsonzscada_external_recipients.jsoni0zAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet) last_week last_monthpathreturnc|xst}|jsgS tj|j }t |t r|SgS#tjtf$rgcYSwxYw)zFLoad the report run log (list of run records). [] when absent/invalid.) RUN_LOG_FILEis_filejsonloads read_text isinstancelistJSONDecodeErrorOSError)rdatas -/var/www/html/togen/scada_report_scheduler.py load_run_logrsg   zz$..*+!$-t525  ' * s4AAA32A3recordc|xst}t|}|j| |jt j |dy#t $r(tjdjd|YywxYw)N)indentscada_report_schedulerzFailed to persist %s) rrappend write_textrdumpsrlogging getLogger exception)rrlogs r_append_run_logr*sl  QZF  M I&   s%A&&A87A8r/cP|jtfd|DS)zTrue if a *scheduled* run for this schedule already landed on ``run_date``. Manual runs are ignored (see ``_last_run_date``).c3K|]D}|jdk(xr*|jdk(xr|jddk7Fyw)r+r/r-r.N)r1).0eisor+s r z_ran_on..sVuu]#{2/quuZ7HC7O/55?h./sA A ) isoformatany)r)r+r/r=s ` @r_ran_onrAs-    C  schedulec|jdxsdj}|tvr t|S|dk(r$ t|jd}|dk\r|SdSy#tt f$rYywxYw)z}Firing interval / window length in days for a schedule, or None if the cadence is unrecognized or custom_days is invalid.cadencer0custom custom_daysN)r1stripCADENCE_INTERVAL_DAYSintr4r3)rCrEns r_interval_daysrMs||I&,"335G''$W--( HLL/0AFq$$ :&  sAA10A1todayintervalcL|tdz }|t|dz z }||fS)z zz$..*+$xx b) dD !  @!z!S1aggiA @@  ' *  As##B/B>B>B>B;:B;cgt}}|D]i}t|ts|jj }|s5||vs:|j ||j |jk|SN)setrr]rIloweraddr#)addrsorderedseenaddrnorms r_deduperiskTG)$$ zz|!!# D$ HHTN NN4::< ( ) NrBcg}|jdr2|jd}t|tr|j||jt t t |S)zInternal audience emailed at generation time (no approval): the per-schedule list when ``notify`` is on + the global enforced list.notifyr[)r1rrextendr^ENFORCED_RECIPIENTS_FILEri)rCrd per_schedules rresolve_internal_recipientsrosV E||H||L1 lD ) LL & LL%&>?@ 5>rBcX|jdsgStttS)uExternal audience — the global external list, included only when the schedule's ``external`` toggle is on. Gated behind approval (DVI-1102 F4).external)r1rir^EXTERNAL_RECIPIENTS_FILErCs rresolve_external_recipientsrts' << # '(@A BBrBcBtt|t|zS)zFull OR-merged recipient list (internal + external). Retained for callers that want the combined audience; the scheduler itself sends the two groups separately so external delivery can be held for approval.)rirortrss rresolve_recipientsrvs& .x80:; <.fmt-s+''!AEE7!AJJt$4#566rBz - )rSrRrs r_friendly_ranger,s!7)n SW //rBcHd|jdddt||dS)NzKiln Metrics Report: rwReportz ())r1r)rCrSrRs r_email_subjectr2s4#HLL$B#CD 734A 78rBcftjjdxsdjdS)z?Public Togen base URL for building the in-app viewer deep link.TOGEN_BASE_URLzhttps://togen.icastinc.com/)osenvironr1rstriprrBr _base_urlr7s( JJNN+ , ,+VVC[9rBout_namechtdt|}djt|S)uA "View report online" button linking into the in-app Report Viewer. Deep-links to ``?open_scada_report=`` which the frontend handles by opening the SCADA tool and the persisted-report viewer modal. The route is SSO-gated, so this is for the internal audience only (DVI-1165 D2) — the scheduler never adds it to the external send (external gets the attachment only, via the approvals path). z/?open_scada_report=aM
View report online

Opens the report in Togen (sign-in required).

)link)rrformat_esc)rrs r_viewer_cta_htmlr=s9k].uX.? @D B  f$t*frBcriteria_summaryroptsrzc J|xstj}|jdxsg}|rdj|nd}d|jddfd|j d|j fd |fd t |jd d fg} djd | D} tj |||} d| zdz| z} d} |jdr |r t|} d}tjtt|||| d| |S)zInternal report email body: intro + detail table + (opt) Attention summary block + (opt) viewer link CTA. The summary/link are gated by the ``report_email`` settings in ``opts`` (DVI-1165).sensors, z All sensorsSchedulerwr0z Date rangeu – SensorsRows row_countrc3lK|],\}}djt|t|.yw)z{lbl}{val})lblvalN)rr)r;rrs rr>z_email_html.._s>K:Bc 49$s)4 5Ks24z

Your scheduled Kiln Metrics report is attached as an Excel workbook (one tab per day).

z
include_viewer_linkz

You are receiving this because you are a recipient of this scheduled Kiln Metrics report.

zKiln Metrics Report)title body_htmlsubtitlecta_html footer_html) scada_reportreport_email_settingsr1joinr?r]build_report_email_summaryr notificationsrender_email_shellrr)rCrzrSrRrrrr sensors_txtrowsrow_html summary_htmlrrrs r _email_htmlrQs^  7<557Dll9%+G(/$))G$]K X\\&"-. )--/0g6G6G6I5JKL K VZZ Q/01  D wwKFJ KKH  :: $(L &)1  14>  >   H xx%&8#H- 2  + +>(Iw?@&;{ 44rBcxt|jddjddjddS)N&z&z>)r]replace)values rrr{s1 J WS' "773#7V8LNrBT)r- send_emaildry_runloggerr-rcx |jd}|jdd}t|}t||\} } |||j| j| jddggd|t j jdd } t j|jd} | j| | |jd xsd } | d r1|jd || d d t| d z| d<d| d<| S| d| d<| j| }| j| |}|j}tjddt!|d| jd| jd}t|z } |j#||| d<|j%d||| dt'| t j,|| |||| j.| j| jt j j||d|s|j%d|d| d<| St3|}t5|}|| d<|| d<|jd s%|jd!s|j%d"|| S|s|s|j1d#|d$| d<| St'|t6kDr(|j1d%|t'|t6d&| d<| St j8}t;|| | }t=|| | | |||'}t?| | d}|r|g}|r&|jAd(|d)d*jC||r#|jAd+d*jC||j%d,|d-jC|xsd.d/| d<| Sd}|r]tEjF|||||tH0}|r|j%d1||t'|n|jd2|d3| d<d| d<| S|s |rd4nd$| d<| StKjL|| j.| r| jnd | r| jnd | d||||d5|d67 }|d8d| d9<|d:r4|j%d;|t'|t'|d<|rd=nd>| d<| S|j1d?||rd@ndA| d<| S#t($r!|j+d|d| d<d| d<| cYSwxYw#t($r|j1d|YwxYw)Bu~Generate + persist a schedule's report and (optionally) email it. Returns a run record dict (also the shape appended to the run log). The ``recorded`` key tells the caller whether to persist it: a transiently failed send is not recorded so it retries on the next pass. ``source`` tags the run in the log — ``"schedule"`` for a timer-driven pass, ``"manual"`` for a Test / run-now (DVI-1164). ``send_email`` gates all email: a manual Test run (D3) sets it False so the report is generated, persisted and recorded (openable in the viewer) but nothing is emailed and no external approval is enqueued. rVrwr0rskipped_notify_offT) r+ schedule_namer/rSrRr email_statusr[external_recipients report_filer- recorded_atrecorded report_typerNerrorz$schedule %r: report build failed: %serror:rFrr)parentsexist_ok_z.xlsxrz)schedule %r: wrote %s (%d rows, %d bytes)z%schedule %r: failed to persist reportpersist_failed)r+rrrSrR generated_atr-r)metazCschedule %r: failed to persist view-model JSON (report still saved)uFschedule %r: manual run — report persisted + recorded, no email (D3)manual_no_emailr[rrkrqu=schedule %r: notifications off — report persisted, no emailuJschedule %r: notifications on but no recipients resolved — email skippedskipped_no_recipientsu[schedule %r: XLSX %d bytes exceeds %d-byte limit — email skipped (report still persisted)skipped_oversizedrzemail z to internal rzenqueue external approval for z[DRY-RUN] schedule %r: would %sz; z do nothingr)filenamecontent content_typez3schedule %r: emailed %s to %d internal recipient(s)u@schedule %r: internal email send FAILED — will retry next pass send_failedsentrC scheduler) report_namerrSrRrr attach_namesubjectr[r-r+ requested_byrequest approval_idnotifiedzWschedule %r: external send held for approval (%d recipient(s)); notified %d approver(s) approverszsent+pending_approvalpending_approvaluschedule %r: external send held for approval but NO approver is configured — report will NOT reach external recipients until an approver is set in Kiln Metrics → Report Notificationsz!sent+pending_approval_no_approverpending_approval_no_approver)'r1rMrTr?rnowrget_report_typebuildrr]rworkbookgetvalue REPORTS_DIRmkdirr~ write_bytesinfolenrr(write_report_view_modelrVwarningrortMAX_ATTACHMENT_BYTESrrrrr#rrsend_email_with_attachmentXLSX_CONTENT_TYPEscada_report_approvalsenqueue_external_send)rCrNrrr-rrWrwrOrSrRrrtyperzsummarybufrrout_pathinternalrq email_optsrhtmlrparts internal_okresults r run_schedulers ,,t C << #Dh'H$UH5IwOO%((*$$&,!||~//1F&  ( (m)D EE [[GX\\)-D-L MF g ;T6'?S!)Cw,@!@~"z  -F; $$V,G .. )C <<>DdT2+a 3 3 56a8I8I8K7LERHX%H T" (} ?(F;$7T D 5,, fg!$t!&(224$..0"*,,.":":"<"8 = >  $%) +!2~ *84H*84H#F<$,F ! LL "hll:&> S  H+,0 2!8~  4y''@SY(< >"5~ 335JXy':G xG(/(& (D%Y89?K  LL6+mDIIh> gt $=NP   KKMk3x= : LL%&* ,%2F> "!&F: M +39P~ # 9 9ehh+4)%%'$'.!!#D%8Xs FF#9-d3F= j A#h-VK-@)A C>F"9(: ~ M .04 5JR"E(F ~ M} @$G!1~"z $ 5./3 55s%$7S-A(T-'TTT98T9 schedulesct}t|dddddd}|D])}t|tr|j ds|dxxdz cc<3t |}|/|j d|j d|dxxdz cc<ot||||s|dxxdz cc<|d xxdz cc<t|||| }|j d d jd s |dxxdz cc<|j d dk(r |dxxdz cc<|jdds |rt||j|,|S)z.Run every due schedule. Returns summary stats.r)rdue generatedemailedskipped_not_dueinvalidrVrrHu(schedule %r: invalid cadence — skippedrwrrrrrr0)rpersistrrrrT) rrrr\r1rMrrYr startswithpopr*r#) rrNrrr)statsrCrOrs rscanr GsQ .Ci.aA ?E(D)d1C )  !  !(+   NNE#<</ 1 )  !  hsH5 # $ ) $  e  hwvNzz."-889NO + ! #  ::n % / )  !  ::j$ ' F # JJv /2 LrBc|jsgStj|j}t |t st d||Dcgc]}t |ts|c}Scc}w)Nz#schedules file is not a JSON list: )existsrrrrrr3r\)rrr}s rload_schedulesrjs_ ;;= ::dnn& 'D dD !>tfEFF 3!z!T2A 33 3s A5.A5c tjdtj}|jdt t d|jddd|jdd d |jd d gdd|j }tj|jddtjtjd}|jr! tj|j}n"t%j&j}t)|j*} t-|}|j5d|j7|j8rdndt;||j8|}|j5d|d|d|d |d!|d"|d#y#t$r4|j!d|jtj"dYwxYw#t.tt0j2f$r2}|j!d||tj"dYd}~d}~wwxYw)$Nz9Run due SCADA report schedules and email the XLSX results) descriptionformatter_classz --schedulesz#Path to scada_report_schedules.json)defaulthelpz--datez1Override today's date as YYYY-MM-DD (for testing)z --dry-run store_truezEGenerate + persist reports but do not send email or write the run log)actionrz --log-levelINFO)DEBUGrWARNINGERRORzLogging verbosity)rchoicesrz'%(asctime)s %(levelname)-8s %(message)sz%Y-%m-%dT%H:%M:%S)levelrdatefmtstreamr"z'Invalid --date %r (expected YYYY-MM-DD)r zCould not read schedules %s: %srHzScanning %s (today=%s%s)z , DRY-RUNr0rz_Scan complete: %d schedule(s) | due=%d generated=%d emailed=%d | skipped: not_due=%d invalid=%drrrrrr)argparseArgumentParserArgumentDefaultsHelpFormatter add_argumentr]DEFAULT_SCHEDULES_PATH parse_argsr& basicConfig log_levelsysstdoutr'rr2r3rexitrrrrrrrrrr?rr )parserargsrrNschedules_pathrexcr s rmainr.ss  $ $O >>F  s3I/JBD $PR  L45  v E02    D nn8#zz    7 8F yy &&tyy1E  ##%$..)N">2  KK*NEOON  % "2 4!  LLBDII N HHQK  Z!5!5 6 6L  s*)F? G??:G<;G<?I(I  I__main__r`)rN)@__doc__rrr&rr{r'rrrpathlibr urllib.parserrrr ImportErrorr-r)__file__resolveparent _BASE_DIRrr1r#rrrmrrrrrJrrr\r*r]r8boolrArMrKrTrYr^rirortrvr~rrrrrrLoggerrr rr.__name__rrBrr;sjYv  .. ! N " " $ + + JJNN+Y9V-VWY2::>>"5y97LMN 22 $'GG$'GG'H'(r: t t *D***3,C4D T ts +T +$ +T +S +T +$ At A A d  $ 4 C$C4C<<$<$$$ 0t0d0s0 8T8d8T8c8 939 ss*%)4d'4$'4'4'4'4GJ'4TN8B$(@4@@$@ @14@!@-1@F D  4  $( F44$404f zFw CHH +C510 0s F>>G$GG$