select
1 AS `agenturid`,
`e`.`jobnummer` AS `jobnr`,
`pr`.`titel` AS `jobname`,
`pt`.`kreditorennummer` AS `lcode`,
`vk`.`id` AS `datensatzid`,
'Verkauf' AS `name herkunfts-db`,
`e`.`datum` AS `datum`,
`pp`.`id` AS `lanr`,
`p`.`name` AS `latext`,
`vk`.`menge` AS `menge`,
(`ppp`.`provision` * `ppp`.`abschlagszahlung`) AS `betrag`,
0 AS `typ`,
`vk`.`id` AS `belegkey`,
date_format(`e`.`datum`, '%Y-%m-%d') AS `bemerkung`,
`vs`.`id_verkauf` AS `id_verkauf`,
`auftr`.`nummer` AS `Auftragsnummer`
from `verkauf` `vk` USE INDEX (`vk_einsatzeditmengepid`)
left join `auftragsnummer` `auftr` on   `auftr`.`id_produkt` = `vk`.`id_produkt`  and `auftr`.`id_einsatz` = `vk`.`id_einsatz`
join `einsatz` `e` on     `vk`.`editierbar` = 1    and `vk`.`menge` > 0    and `e`.`freigegeben_proacc` = 1    and `e`.`id` = `vk`.`id_einsatz`
join `produkt` `p` on `p`.`id` = `vk`.`id_produkt` join `promotion` `pr` on `pr`.`id` = `e`.`id_promotion` 
join `promoter` `pt` on `pt`.`id` = `e`.`id_promoter`
join `promotionprodukt` `pp` on `pp`.`id_promotion` = `pr`.`id` and `pp`.`id_produkt` = `p`.`id`     and `pp`.`proacc` = 1 and `pp`.`needs_proof` = 0
join `promotionproduktprovision` `ppp` on `ppp`.`id_promotionprodukt` = `pp`.`id`  and `e`.`datum` between `ppp`.`datum_von` and `ppp`.`datum_bis`
left join `verkauf_sperre` `vs` on `vs`.`id_verkauf` = `vk`.`id`
where `pt`.`kreditorennummer` <> ''
and (`pp`.`id` <> '')
and (`e`.`jobnummer` <> '')
and (`vk`.`menge` <> 0)
and (`e`.`datum` > '2011-03-31')
and (`ppp`.`provision` <> 0)
having isnull(`id_verkauf`)
union
select
1 AS `agenturid`,
`e`.`jobnummer` AS `jobnr`,
`pr`.`titel` AS `jobname`,
`pt`.`kreditorennummer` AS `lcode`,
`vk`.`id` AS `datensatzid`,
'Verkauf' AS `name herkunfts-db`,
`e`.`datum` AS `datum`,
`pp`.`id` AS `lanr`,
`p`.`name` AS `latext2`,
`vk`.`menge` AS `menge`,
(`ppp`.`provision` * `ppp`.`abschlagszahlung`) AS `betrag`,
0 AS `typ`,
`vk`.`id` AS `belegkey`,
date_format(`e`.`datum`, '%Y-%m-%d') AS `bemerkung`,
`vs`.`id_verkauf` AS `id_verkauf`,
`auftr`.`nummer` AS `Auftragsnummer`
from
`doit_utf8`.`verkauf` `vk`
left join `doit_utf8`.`auftragsnummer` `auftr` on    `auftr`.`id_produkt` = `vk`.`id_produkt`        and `auftr`.`id_einsatz` = `vk`.`id_einsatz`
join `doit_utf8`.`proacc_abrechnungsdaten_feedback` `paf` on    `paf`.`DatensatzID` = `vk`.`id`        and `paf`.`editierbar` = 1            and `vk`.`menge` = 0
join `doit_utf8`.`einsatz` `e` on    `e`.`id` = `vk`.`id_einsatz`        and `e`.`freigegeben_proacc` = 1
join `doit_utf8`.`produkt` `p` on    `p`.`id` = `vk`.`id_produkt`
join `doit_utf8`.`promotion` `pr` on    `pr`.`id` = `e`.`id_promotion`
join `doit_utf8`.`promoter` `pt` on    `pt`.`id` = `e`.`id_promoter`
join `doit_utf8`.`promotionprodukt` `pp` on    `pp`.`id_promotion` = `pr`.`id`        and `pp`.`id_produkt` = `p`.`id`            and `pp`.`proacc` = 1
join `doit_utf8`.`promotionproduktprovision` `ppp` on    `ppp`.`id_promotionprodukt` = `pp`.`id`        and `e`.`datum` between `ppp`.`datum_von` and `ppp`.`datum_bis`
left join `doit_utf8`.`verkauf_sperre` `vs` on    `vs`.`id_verkauf` = `vk`.`id`
where
(`pt`.`kreditorennummer` <> '')
and (`pp`.`id` <> '')
and (`e`.`jobnummer` <> '')
and (`vk`.`menge` <> 0)
and (`e`.`datum` > '2011-03-31')
and (`ppp`.`provision` <> 0)
having isnull(`id_verkauf`)
union
select
1 AS `agenturid`,
`e`.`jobnummer` AS `jobnr`,
`pr`.`titel` AS `jobname`,
`pt`.`kreditorennummer` AS `lcode`,
`a`.`id` AS `datensatzid`,
'Auslage' AS `name herkunfts-db`,
`e`.`datum` AS `datum`,
1 AS `lanr`,
`a`.`name` AS `latext`,
1 AS `menge`,
`a`.`summe` AS `betrag`,
(case
`a`.`mwst` when 0 then 1
when 7 then 2
when 5 then 4
when 16 then 5
else 3
end) AS `typ`,
`a`.`id` AS `belegkey`,
concat('Was: ', `a`.`name`, ', Bei wem:', `a`.`ort`, ', Einsatzdatum: ', convert(date_format(`e`.`datum`, '%Y-%m-%d') using utf8)) AS `bemerkung`,
'' AS `id_verkauf`,
`a`.`auftragsnummer` AS `Auftragsnummer`
from `doit_utf8`.`auslage` `a`
left join `doit_utf8`.`proacc_abrechnungsdaten_feedback` `paf` on `paf`.`Name Herkunfts-DB` = 'Auslage' and `paf`.`DatensatzID` = `a`.`id`
join `doit_utf8`.`einsatz` `e` on    `e`.`id` = `a`.`id_einsatz` and `e`.`freigegeben_proacc` = 1
join `doit_utf8`.`promotion` `pr` on `pr`.`id` = `e`.`id_promotion` 
join `doit_utf8`.`promoter` `pt` on `pt`.`id` = `e`.`id_promoter`
where
(`e`.`datum` > '2011-03-31'
and `paf`.`editierbar` = 1)
or isnull(`paf`.`editierbar`)
union
select
1 AS `agenturid`,
`e`.`jobnummer` AS `jobnr`,
`pr`.`titel` AS `jobname`,
`pt`.`kreditorennummer` AS `lcode`,
`a`.`id` AS `datensatzid`,
'Anummer' AS `name herkunfts-db`,
`e`.`datum` AS `datum`,
1 AS `lanr`,
`p`.`name` AS `latext`,
1 AS `menge`,
(`ppp`.`provision` * `ppp`.`abschlagszahlung`) AS `betrag`,
0 AS `typ`,
`a`.`id` AS `belegkey`,
'bemerkung'  AS `bemerkung`,
'' AS `id_verkauf`,
`a`.`nummer` AS `Auftragsnummer`
from `doit_utf8`.`auftragsnummer` `a`
left join `doit_utf8`.`proacc_abrechnungsdaten_feedback` `paf` on `paf`.`Name Herkunfts-DB` = 'Anummer' and `paf`.`DatensatzID` = `a`.`id`
join `doit_utf8`.`einsatz` `e` on    `e`.`id` = `a`.`id_einsatz` and `e`.`freigegeben_proacc` = 1 and e.datum > "2020-01-01"
join `doit_utf8`.`promotion` `pr` on `pr`.`id` = `e`.`id_promotion` 
join `doit_utf8`.`promoter` `pt` on `pt`.`id` = `e`.`id_promoter`
join produkt p on p.id=a.id_produkt
join promotionprodukt pp on pp.id_promotion=e.id_promotion and pp.id_produkt=p.id and pp.needs_proof =1 
join promotionproduktprovision ppp on ppp.id_promotionprodukt=pp.id and ppp.datum_von  <= e.datum and ppp.datum_bis >= e.datum
where
(a.checked_rl=1 or a.checked_isp=1)
and
`e`.`datum` > '2020-03-31'
and (`paf`.`editierbar` = 1
or isnull(`paf`.`editierbar`))


