*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     
*
*  vygeneruje z aktualnich dat pocty dokladu a radku na nich po mesicich za jednotlive pracovniky
*
* 2022-05-10
*
*     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     *     

#DEFINE POCET_MESICU 6

IF m.issql
    lsql = "SELECT c_dokl, c_karty, mnozstvi FROM food600.obraty WHERE c_data=?m.cdata "
    sql_select(lsql,'obraty',"c_dokl I, c_karty I, mnozstvi Y")

    lsql = "SELECT c_dokl, operace, provedl, kdy FROM food600.doklady WHERE c_data=?m.cdata "
    sql_select(lsql,'doklady',"c_dokl I, operace C(2), provedl C(3), kdy T")
ELSE
    USE (m.fn_doklady) AGAIN IN 0 NOUPDATE ALIAS doklady
    USE (m.fn_obraty)  AGAIN IN 0 NOUPDATE ALIAS obraty
ENDIF 

SELECT c_dokl, CNT(*) as radku ;
       GROUP BY 1 FROM obraty WHERE c_karty # 0 INTO TABLE (m.gcpri_path + "oooo")
INDEX on c_dokl TAG c_dokl

SELECT c_dokl, operace, provedl, LEFT(TTOC(kdy, 3), 7) as mesic, 0000 as radku, ;
       0000 as pocet_ve, 000000 as radku_ve, ;
       0000 as pocet_vv, 000000 as radku_vv, ;
       0000 as pocet_pe, 000000 as radku_pe, ;
       0000 as pocet_pb, 000000 as radku_pb  ;
       GROUP BY 1 WHERE INLIST(operace, "VE", "VV", "PE", "PB") ;
       FROM doklady INTO TABLE (m.gcpri_path + "ooo")

DELETE FOR mesic < LEFT(TTOC(GOMONTH(DATE(), -POCET_MESICU), 3), 7)

SET RELATION TO c_dokl INTO oooo
REPLACE ALL radku WITH oooo.radku
DELETE FOR radku = 0

REPLACE FOR operace = "VE" pocet_ve WITH 1, radku_ve WITH radku
REPLACE FOR operace = "VV" pocet_vv WITH 1, radku_vv WITH radku
REPLACE FOR operace = "PE" pocet_pe WITH 1, radku_pe WITH radku
REPLACE FOR operace = "PB" pocet_pb WITH 1, radku_pb WITH radku

SELECT mesic, provedl, ;
       SUM(pocet_pe) as pocet_pe  , SUM(radku_pe) as radku_pe,  ;
       SUM(pocet_vv) as pocet_vv  , SUM(radku_vv) as radku_vv,  ;
       SUM(pocet_ve) as pocet_ve  , SUM(radku_ve) as radku_ve,  ;
       SUM(pocet_pb) as pocet_bour, SUM(radku_pb) as radku_bour ;
       GROUP BY 1, 2 FROM ooo INTO TABLE (m.gcexportpath + "pocty_dokladu")
ALTER table (m.gcexportpath + "pocty_dokladu") alter pocet_pe   N(6)
ALTER table (m.gcexportpath + "pocty_dokladu") alter pocet_ve   N(6)
ALTER table (m.gcexportpath + "pocty_dokladu") alter pocet_vv   N(6)
ALTER table (m.gcexportpath + "pocty_dokladu") alter pocet_bour N(6)

ALTER table (m.gcexportpath + "pocty_dokladu") alter radku_pe   N(7)
ALTER table (m.gcexportpath + "pocty_dokladu") alter radku_ve   N(7)
ALTER table (m.gcexportpath + "pocty_dokladu") alter radku_vv   N(7)
ALTER table (m.gcexportpath + "pocty_dokladu") alter radku_bour N(7)

dbf2csv()

DO close_database
