CREATE view `proacc_abrechnungsdaten`
AS
  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))))
  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      ((((((((`verkauf` `vk`
  left join auftragsnummer auftr on auftr.id_produkt = vk.id_produkt and auftr.id_einsatz=vk.id_einsatz
  JOIN      `proacc_abrechnungsdaten_feedback` `paf`
  ON       (((
                                          `paf`.`datensatzid` = `vk`.`id`)
                      AND       (
                                          `paf`.`editierbar` = 1)
                      AND       (
                                          `vk`.`menge` = 0))))
  JOIN      `einsatz` `e`
  ON       (((
                                          `e`.`id` = `vk`.`id_einsatz`)
                      AND       (
                                          `e`.`freigegeben_proacc` = 1))))
  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))))
  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`,
            `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
                      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      ((((`auslage` `a`
  LEFT JOIN `proacc_abrechnungsdaten_feedback` `paf`
  ON       (((
                                          `paf`.`name herkunfts-db` = 'Auslage')
                      AND       (
                                          `paf`.`datensatzid` = `a`.`id`))))
  JOIN      `einsatz` `e`
  ON       ((
                                `e`.`id` = `a`.`id_einsatz` and `e`.`freigegeben_proacc` = 1
                                )))
  JOIN      `promotion` `pr`
  ON       ((
                                `pr`.`id` = `e`.`id_promotion`)))
  JOIN      `promoter` `pt`
  ON       ((
                                `pt`.`id` = `e`.`id_promoter`)))
  WHERE     ( ( `e`.`datum` > '2011-03-31') AND (
                                `paf`.`editierbar` = 1)
            OR        isnull(`paf`.`editierbar`));







            CREATE VIEW `proacc_abrechnungsdaten`
AS 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)))) 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 (((((((((`verkauf` `vk` left join `auftragsnummer` `auftr` on(((`auftr`.`id_produkt` = `vk`.`id_produkt`) and (`auftr`.`id_einsatz` = `vk`.`id_einsatz`)))) join `proacc_abrechnungsdaten_feedback` `paf` on(((`paf`.`DatensatzID` = `vk`.`id`) and (`paf`.`editierbar` = 1) and (`vk`.`menge` = 0)))) join `einsatz` `e` on(((`e`.`id` = `vk`.`id_einsatz`) and (`e`.`freigegeben_proacc` = 1)))) 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)))) 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`,`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 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 ((((`auslage` `a` left join `proacc_abrechnungsdaten_feedback` `paf` on(((`paf`.`Name Herkunfts-DB` = 'Auslage') and (`paf`.`DatensatzID` = `a`.`id`)))) join `einsatz` `e` on((`e`.`id` = `a`.`id_einsatz` and `e`.`freigegeben_proacc` = 1))) join `promotion` `pr` on((`pr`.`id` = `e`.`id_promotion`))) join `promoter` `pt` on((`pt`.`id` = `e`.`id_promoter`))) where (((`e`.`datum` > '2011-03-31') and (`paf`.`editierbar` = 1)) or isnull(`paf`.`editierbar`));






# Alte Version
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `proacc_leistungsarten`
AS SELECT
   `pj`.`jobnummer` AS `JobNr`,
   `pp`.`id` AS `LaNr`,
   `p`.`name` AS `LAText`,
   `pp`.`provision` AS `Betrag`,0 AS `Honorar`
FROM (((`promotionprodukt` `pp` join `produkt` `p` on(((`p`.`id` = `pp`.`id_produkt`) and (`pp`.`proacc` = 1)))) join `promotion` `pr` on((`pr`.`id` = `pp`.`id_promotion`))) join `promotion_jobnummer` `pj` on((`pr`.`id` = `pj`.`id_promotion`))) union select `pj`.`jobnummer` AS `JobNr`,1 AS `LaNr`,_utf8'Auslage' AS `LAText`,1 AS `Betrag`,1 AS `Honorar` from `promotion_jobnummer` `pj`;


#Neue Version
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `proacc_leistungsarten`
AS SELECT
   `pj`.`jobnummer` AS `JobNr`,
   `pp`.`id` AS `LaNr`,
   `p`.`name` AS `LAText`,
   `pp`.`provision` AS `Betrag`,0 AS `Honorar`
FROM (((`promotionprodukt` `pp`
join `produkt` `p` on(((`p`.`id` = `pp`.`id_produkt`) and (`pp`.`proacc` = 1))))
join `promotion` `pr` on((`pr`.`id` = `pp`.`id_promotion` and pr.archiv=0) )))
join `promotion_jobnummer` `pj` on((`pr`.`id` = `pj`.`id_promotion` and pj.archiv=0)))

union select `pj`.`jobnummer` AS `JobNr`,1 AS `LaNr`,_utf8'Auslage' AS `LAText`,1 AS `Betrag`,1 AS `Honorar` 
   from `promotion_jobnummer` `pj`;




#Promoter alt

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `doit_utf8`.`proacc_promoter` AS
select
    `pj`.`jobnummer` AS `JobNr`,
    `p`.`id` AS `PromoterID`,
    1 AS `AgenturId`,
    `p`.`kreditorennummer` AS `kreditorennummer`,
    (case		
        `p`.`geschlecht`
        when _latin1'1' then _latin1'1'
        when _latin1'0' then _latin1'2' end) AS `Anrede`,
    `p`.`nachname` AS `nachname`,
    `p`.`vorname` AS `vorname`,
    date_format(`p`.`geburtstag`, _latin1'%d.%m.%Y') AS `geburtstag`,
    `p`.`strasse` AS `strasse`,
    `p`.`plz` AS `plz`,
    `p`.`ort` AS `ort`,
    _latin1'' AS `firma`,
    `p`.`land` AS `tel`,
    `p`.`fax` AS `fax`,
    `p`.`mobil` AS `mobil`,
    `p`.`email` AS `email`,
    `p`.`bemerkung` AS `bemerkung`
from
    (((`doit_utf8`.`promotionpromoter` `pp`
join `doit_utf8`.`promoter` `p` on
    ((`p`.`id` = `pp`.`id_promoter`)))
join `doit_utf8`.`promotion_jobnummer` `pj` on
    ((`pp`.`id_promotion` = `pj`.`id_promotion`)))
join `doit_utf8`.`promotion` `pr` on
    ((`pr`.`id` = `pp`.`id_promotion`)))

#NEU (WHERE pj.archiv=0)
CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `doit_utf8`.`proacc_promoter` AS
select
    `pj`.`jobnummer` AS `JobNr`,
    `p`.`id` AS `PromoterID`,
    1 AS `AgenturId`,
    `p`.`kreditorennummer` AS `kreditorennummer`,
    (case		
        `p`.`geschlecht`
        when _latin1'1' then _latin1'1'
        when _latin1'0' then _latin1'2' end) AS `Anrede`,
    `p`.`nachname` AS `nachname`,
    `p`.`vorname` AS `vorname`,
    date_format(`p`.`geburtstag`, _latin1'%d.%m.%Y') AS `geburtstag`,
    `p`.`strasse` AS `strasse`,
    `p`.`plz` AS `plz`,
    `p`.`ort` AS `ort`,
    _latin1'' AS `firma`,
    `p`.`land` AS `tel`,
    `p`.`fax` AS `fax`,
    `p`.`mobil` AS `mobil`,
    `p`.`email` AS `email`,
    `p`.`bemerkung` AS `bemerkung`
from
    (((`doit_utf8`.`promotionpromoter` `pp`
join `doit_utf8`.`promoter` `p` on
    ((`p`.`id` = `pp`.`id_promoter`)))
join `doit_utf8`.`promotion_jobnummer` `pj` on
    ((`pp`.`id_promotion` = `pj`.`id_promotion`)))
join `doit_utf8`.`promotion` `pr` on
    ((`pr`.`id` = `pp`.`id_promotion`)))
    WHERE pj.archiv=0s
