OpenNIT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

-- Migration 247: Leistungsnachweise aus der Ticket-Zeiterfassung
--
-- Ein Leistungsnachweis fasst die auf Tickets erfasste Arbeitszeit zu einem
-- Dokument zusammen: entweder zu einem einzelnen Ticket (beim Abschluss) oder
-- als Monatssammlung je Standort. Er kann rein zur Information versendet,
-- auf dem Signatur-Tablet oder über einen Link am Rechner unterschrieben
-- werden.

-- Empfänger je Standort. Leer bedeutet: Adresse des übergeordneten Standorts
-- verwenden (die Auflösung steigt die Hierarchie hinauf).
ALTER TABLE `ticket_locations`
    ADD COLUMN `report_email` VARCHAR(255) NULL DEFAULT NULL
    COMMENT 'Empfaenger der Leistungsnachweise; leer = Adresse des uebergeordneten Standorts';

ALTER TABLE `ticket_locations`
    ADD COLUMN `report_monthly` TINYINT(1) NOT NULL DEFAULT 1
    COMMENT 'Nimmt am monatlichen Sammelnachweis teil';

CREATE TABLE IF NOT EXISTS `service_reports` (
    `id`                     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `number`                 VARCHAR(40)  NOT NULL COMMENT 'Fortlaufende Nummer, z. B. LN-2026-0042',
    `kind`                   ENUM('ticket','monthly') NOT NULL DEFAULT 'ticket',
    `location_id`            INT UNSIGNED NULL,
    `ticket_id`              INT UNSIGNED NULL,
    `period_start`           DATE NULL,
    `period_end`             DATE NULL,
    `recipient_email`        VARCHAR(255) NULL,
    `recipient_name`         VARCHAR(255) NULL,
    `mode`                   ENUM('info','signature_link','signature_tablet') NOT NULL DEFAULT 'info',
    `status`                 ENUM('draft','awaiting_signature','signed','sent','failed','cancelled')
                             NOT NULL DEFAULT 'draft',
    `total_minutes`          INT UNSIGNED NOT NULL DEFAULT 0,
    `entries_json`           LONGTEXT NULL COMMENT 'Positionen zum Zeitpunkt der Erstellung (revisionssicher)',
    `document_html`          LONGTEXT NULL COMMENT 'Ausgefuellte Vorlage; ab dem Versand unveraenderlich',
    `signature_token`        CHAR(64) NULL,
    `signature_requested_at` DATETIME NULL,
    `signature_expires_at`   DATETIME NULL,
    `signature_data`         LONGTEXT NULL COMMENT 'PNG als data-URL',
    `signature_name`         VARCHAR(255) NULL,
    `signature_obtained_at`  DATETIME NULL,
    `pdf_path`               VARCHAR(500) NULL,
    `sent_at`                DATETIME NULL,
    `last_error`             VARCHAR(500) NULL,
    `created_by`             BIGINT UNSIGNED NULL,
    `created_at`             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_sr_number` (`number`),
    UNIQUE KEY `uq_sr_token`  (`signature_token`),
    KEY `idx_sr_ticket`   (`ticket_id`),
    KEY `idx_sr_location` (`location_id`, `period_start`),
    KEY `idx_sr_status`   (`status`, `created_at`),
    CONSTRAINT `fk_sr_ticket` FOREIGN KEY (`ticket_id`)
        REFERENCES `tickets`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_sr_location` FOREIGN KEY (`location_id`)
        REFERENCES `ticket_locations`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Leistungsnachweise aus der Ticket-Zeiterfassung';

-- Gestaltbare Dokumentvorlagen (wie die PDF-Vorlagen der Geräteübergabe).
CREATE TABLE IF NOT EXISTS `service_report_templates` (
    `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `template_key` VARCHAR(50)  NOT NULL UNIQUE COMMENT 'ticket oder monthly',
    `name`         VARCHAR(255) NOT NULL,
    `body_html`    LONGTEXT     NOT NULL COMMENT 'HTML mit {{Platzhaltern}}',
    `updated_by`   BIGINT UNSIGNED NULL,
    `updated_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Vorlagen der Leistungsnachweise';

INSERT IGNORE INTO `service_report_templates` (`template_key`, `name`, `body_html`) VALUES
('ticket', 'Leistungsnachweis – einzelnes Ticket',
 '<div style="text-align:right;margin-bottom:6px;">{{logo}}</div>
<h2 style="text-align:center;color:#1e3a5f;border-bottom:2px solid #1e3a5f;padding-bottom:8px;">Leistungsnachweis {{number}}</h2>
<table style="width:100%;font-size:10pt;margin:14px 0;">
  <tr><td style="width:28%;color:#6b7280;">Standort</td><td><strong>{{location}}</strong></td></tr>
  <tr><td style="color:#6b7280;">Vorgang</td><td>#{{ticket_id}} – {{ticket_subject}}</td></tr>
  <tr><td style="color:#6b7280;">Zeitraum</td><td>{{period}}</td></tr>
  <tr><td style="color:#6b7280;">Gesamtaufwand</td><td><strong>{{total_time}}</strong></td></tr>
</table>
{{entries_table}}
<p style="margin-top:18px;font-size:10pt;">Die aufgeführten Leistungen wurden erbracht.</p>
{{signature_block}}'),
('monthly', 'Leistungsnachweis – Monatssammlung',
 '<div style="text-align:right;margin-bottom:6px;">{{logo}}</div>
<h2 style="text-align:center;color:#1e3a5f;border-bottom:2px solid #1e3a5f;padding-bottom:8px;">Leistungsnachweis {{number}}</h2>
<table style="width:100%;font-size:10pt;margin:14px 0;">
  <tr><td style="width:28%;color:#6b7280;">Standort</td><td><strong>{{location}}</strong></td></tr>
  <tr><td style="color:#6b7280;">Zeitraum</td><td>{{period}}</td></tr>
  <tr><td style="color:#6b7280;">Vorgänge</td><td>{{ticket_count}}</td></tr>
  <tr><td style="color:#6b7280;">Gesamtaufwand</td><td><strong>{{total_time}}</strong></td></tr>
</table>
{{entries_table}}
<p style="margin-top:18px;font-size:10pt;">Die aufgeführten Leistungen wurden im genannten Zeitraum erbracht.</p>
{{signature_block}}');

-- E-Mail-Vorlagen: reine Information und Bitte um Unterschrift.
INSERT IGNORE INTO `email_templates` (`template_key`, `subject`, `body_html`, `description`) VALUES
('service_report_info',
 'Leistungsnachweis {{number}} – {{location}}',
 '<p>Guten Tag,</p>
<p>anbei erhalten Sie den Leistungsnachweis <strong>{{number}}</strong> für <strong>{{location}}</strong> ({{period}}).</p>
<p>Erfasster Aufwand: <strong>{{total_time}}</strong>.</p>
<p>Der Nachweis liegt dieser E-Mail als PDF bei. Eine Rückmeldung ist nicht erforderlich.</p>
<p>Freundliche Grüße<br>{{app_name}}</p>',
 'Leistungsnachweis zur Information (mit PDF im Anhang)'),
('service_report_signature',
 'Bitte um Bestätigung: Leistungsnachweis {{number}} – {{location}}',
 '<p>Guten Tag,</p>
<p>für <strong>{{location}}</strong> liegt der Leistungsnachweis <strong>{{number}}</strong> ({{period}}) vor – erfasster Aufwand: <strong>{{total_time}}</strong>.</p>
<p>Bitte bestätigen Sie die Leistungen über den folgenden Link:</p>
<p><a href="{{signature_url}}" style="background:#2563eb;color:#ffffff;padding:10px 18px;border-radius:6px;text-decoration:none;display:inline-block;">Leistungsnachweis ansehen und unterschreiben</a></p>
<p style="font-size:12px;color:#6b7280;">Der Link ist bis zum {{expires_at}} gültig.</p>
<p>Freundliche Grüße<br>{{app_name}}</p>',
 'Leistungsnachweis mit Link zur Unterschrift am Rechner');

-- Monatslauf als Scheduler-Task. Läuft stündlich an und prüft selbst, ob der
-- eingestellte Stichtag erreicht ist; so genügt eine Zeile in der Crontab.
INSERT IGNORE INTO `cron_tasks`
    (`name`, `label`, `description`, `handler_class`, `frequency_minutes`, `enabled`, `run_in_background`, `max_runtime_seconds`)
VALUES
    ('service_report_monthly', 'Leistungsnachweise (Monat)',
     'Erstellt zu Monatsbeginn je Standort den Leistungsnachweis des Vormonats und versendet ihn gemaess Einstellung (Information oder Unterschrift per Link). Standorte ohne erfasste Zeit werden uebersprungen.',
     'OpenNIT\\Cron\\Handlers\\ServiceReportMonthlyHandler', 60, 1, 1, 900);