BeWoPlaner/Queries/Einträge ohne Monatsunterschrift des Klienten - auch mehrere MA.txt

SELECT 
p.LastName as 'Nachname',
p.Firstname as 'Vorname',
c2s.ApprovedStartDate as 'Bewilligt von',
c2s.ApprovedEndDate as 'Bewilligt bis',
scat.Name as 'Kategorie',
Date_Format(sr.StartDate, '%d.%m.%Y') as 'Datum',
IF (Date_Format(sr.StartDate, '%s') <> '00', '', Date_Format(sr.StartDate, '%H:%i')) as 'von',
IF (Date_Format(sr.StartDate, '%s') <> '00', '', Date_Format(Date_Add(sr.StartDate, INTERVAL sr.Roundedduration MINUTE), '%H:%i')) as 'bis',
Round(sr.Roundedduration, 0) as 'Minuten',
sr.InsUser as 'Angelegt von',
DATE_FORMAT(sr.InsTs, '%d.%m.%Y %H:%i') as 'Angelegt am',
(select CONCAT(emp1p.`FirstName`, ' ', emp1p.`LastName`) from employee emp1 join `person` emp1p on emp1p.`Oid` = emp1.`PersonOid` where emp1.`Oid` in (select employeeoid from `employee2customer` e2c1 join `valuelistentry2object` vl2o on e2c1.`Oid` = vl2o.`ObjectOid` join `valuelistentry` vle on vl2o.`ValueListEntryOid` = vle.`Oid` where e2c1.`CustomerOid` = c.`Oid` and vle.`SystemEntryID` = 3) LIMIT 1) as 'Hauptbetreuung',
(select t.`Name` from team t where t.`Oid` in (select teamoid from `team2customer` t2c where t2c.`CustomerOid` = c.`Oid`) LIMIT 1) as 'Team'
FROM servicerecord sr
join costbearer2supportconcept c2s on sr.costbearer2supportconceptoid = c2s.oid
join supportconcept sc on c2s.supportconceptoid = sc.oid
join customer c on sc.customeroid = c.oid
join person p on c.personoid = p.oid
join servicedescription sd on sd.oid = sr.servicedescriptionoid
join servicecategory scat on scat.oid = sd.servicecategoryoid
LEFT JOIN (
    -- direkt unterschriebene SRs
    SELECT crs2s.servicerecordoid as signed_oid
    FROM confirmationreceiptsignature2servicerecord crs2s
    JOIN confirmationreceiptsignature crs ON crs2s.confirmationreceiptsignatureoid = crs.oid
    WHERE crs.employeeoid IS NULL AND crs.customeroid IS NOT NULL
    UNION
    -- SRs, deren Gruppen-Partner unterschrieben ist
    SELECT sr_other.oid as signed_oid
    FROM servicerecord sr_other
    JOIN servicerecord sr_signed ON sr_signed.GroupOid = sr_other.GroupOid
        AND sr_other.GroupOid IS NOT NULL
    JOIN confirmationreceiptsignature2servicerecord crs2s ON sr_signed.oid = crs2s.servicerecordoid
    JOIN confirmationreceiptsignature crs ON crs2s.confirmationreceiptsignatureoid = crs.oid
    WHERE crs.employeeoid IS NULL AND crs.customeroid IS NOT NULL
) signed_records ON signed_records.signed_oid = sr.oid
WHERE signed_records.signed_oid IS NULL
AND scat.billable = true
AND sr.startdate >= ':Von' AND sr.startdate < ':bis'
ORDER BY p.Lastname, sr.startdate




INSERT INTO `query` (`Oid`,`Tid`,`Type`,`Title`,`SQL`,`Notice`,`InsTs`,`InsUser`,`UdpUser`,`Version`,`IsActive`,`SystemEntryID`,`UserGroupOids`,`ReportTypeName`,`FileName`,`ExportTitle`) VALUES (4712,24,0,'Einträge ohne Monatsunterschrift des Klienten','SELECT 
p.LastName as 'Nachname',
p.Firstname as 'Vorname',
c2s.ApprovedStartDate as 'Bewilligt von',
c2s.ApprovedEndDate as 'Bewilligt bis',
scat.Name as 'Kategorie',
Date_Format(sr.StartDate, '%d.%m.%Y') as 'Datum',
IF (Date_Format(sr.StartDate, '%s') <> '00', '', Date_Format(sr.StartDate, '%H:%i')) as 'von',
IF (Date_Format(sr.StartDate, '%s') <> '00', '', Date_Format(Date_Add(sr.StartDate, INTERVAL sr.Roundedduration MINUTE), '%H:%i')) as 'bis',
Round(sr.Roundedduration, 0) as 'Minuten',
sr.InsUser as 'Angelegt von',
DATE_FORMAT(sr.InsTs, '%d.%m.%Y %H:%i') as 'Angelegt am',
(select CONCAT(emp1p.`FirstName`, ' ', emp1p.`LastName`) from employee emp1 join `person` emp1p on emp1p.`Oid` = emp1.`PersonOid` where emp1.`Oid` in (select employeeoid from `employee2customer` e2c1 join `valuelistentry2object` vl2o on e2c1.`Oid` = vl2o.`ObjectOid` join `valuelistentry` vle on vl2o.`ValueListEntryOid` = vle.`Oid` where e2c1.`CustomerOid` = c.`Oid` and vle.`SystemEntryID` = 3) LIMIT 1) as 'Hauptbetreuung',
(select t.`Name` from team t where t.`Oid` in (select teamoid from `team2customer` t2c where t2c.`CustomerOid` = c.`Oid`) LIMIT 1) as 'Team'
FROM servicerecord sr
join costbearer2supportconcept c2s on sr.costbearer2supportconceptoid = c2s.oid
join supportconcept sc on c2s.supportconceptoid = sc.oid
join customer c on sc.customeroid = c.oid
join person p on c.personoid = p.oid
join servicedescription sd on sd.oid = sr.servicedescriptionoid
join servicecategory scat on scat.oid = sd.servicecategoryoid
LEFT JOIN (
    -- direkt unterschriebene SRs
    SELECT crs2s.servicerecordoid as signed_oid
    FROM confirmationreceiptsignature2servicerecord crs2s
    JOIN confirmationreceiptsignature crs ON crs2s.confirmationreceiptsignatureoid = crs.oid
    WHERE crs.employeeoid IS NULL AND crs.customeroid IS NOT NULL
    UNION
    -- SRs, deren Gruppen-Partner unterschrieben ist
    SELECT sr_other.oid as signed_oid
    FROM servicerecord sr_other
    JOIN servicerecord sr_signed ON sr_signed.GroupOid = sr_other.GroupOid
        AND sr_other.GroupOid IS NOT NULL
    JOIN confirmationreceiptsignature2servicerecord crs2s ON sr_signed.oid = crs2s.servicerecordoid
    JOIN confirmationreceiptsignature crs ON crs2s.confirmationreceiptsignatureoid = crs.oid
    WHERE crs.employeeoid IS NULL AND crs.customeroid IS NOT NULL
) signed_records ON signed_records.signed_oid = sr.oid
WHERE signed_records.signed_oid IS NULL
AND scat.billable = true
AND sr.startdate >= ':Von' AND sr.startdate < ':bis'
ORDER BY p.Lastname, sr.startdate',NULL,NULL,NULL,NULL,1,1,NULL,'1',NULL,NULL,NULL);
INSERT INTO `parameter` (`QueryOid`,`Tid`,`Name`,`Type`,`DbType`,`InsTs`,`InsUser`,`UdpUser`,`Version`,`IsActive`,`SystemEntryID`,`DefaultValue`) VALUES (4712,25,'Von_bis',5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'aktueller Monat');