GenLCLExportDailyStats

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `GenLCLExportDailyStats` $$
CREATE PROCEDURE `GenLCLExportDailyStats`( IN pSailDate INT(11) )
MODIFIES SQL DATA
MAIN: BEGIN
    -- Server SQL Logging declarations.
    DECLARE mSLS VARCHAR(64) DEFAULT "GenLCLExportDailyStats";
    DECLARE mSLH VARCHAR(256) DEFAULT "";
    DECLARE mSLT VARCHAR(128) DEFAULT UUID();
    DECLARE mDEBUG BOOL DEFAULT FALSE;

    DECLARE mSailDate DATE DEFAULT NULL;

    DECLARE mTotalCBM DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalKGS DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalAPCost DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalBiblesCost DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalInvoice DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalGenExp DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalUnitVY DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalChgs DECIMAL(10,2) DEFAULT 0.00;
    DECLARE mTotalCns DECIMAL(10,2) DEFAULT 0.00;

    DECLARE mBLTerm VARCHAR(2) DEFAULT "";
    DECLARE mPort VARCHAR(3) DEFAULT "";
    DECLARE mDRTerm VARCHAR(2) DEFAULT "";
    DECLARE mDRNum VARCHAR(6) DEFAULT "";
    DECLARE mGLTerm VARCHAR(3) DEFAULT "";
    DECLARE mCntVoy VARCHAR(4) DEFAULT "";
    DECLARE mBLFdes VARCHAR(3) DEFAULT "";

    DECLARE mTRecs INT(11) DEFAULT 0;

    -- SQL Error Handler Variables
    -- These must appear BEFORE cursor declarations.
    DECLARE mRecordNotFound BOOL DEFAULT FALSE;
    DECLARE mCursorEOL BOOL DEFAULT FALSE;

    -- SQL CURSOR declarations begin here.

    DECLARE mCursorA CURSOR FOR
        SELECT
            blterm, port, drterm, drnum, glterm, cntvoy, blfdes,
            (
                IF ( ChgsChgCdeInGLNumb( chg01, "3440" ), amt01, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg02, "3440" ), amt02, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg03, "3440" ), amt03, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg04, "3440" ), amt04, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg05, "3440" ), amt05, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg06, "3440" ), amt06, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg07, "3440" ), amt07, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg08, "3440" ), amt08, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg09, "3440" ), amt09, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg10, "3440" ), amt10, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg11, "3440" ), amt11, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( chg12, "3440" ), amt12, 0.00 )
            ) AS 'AMT',
            IF( engmet = 'M', ( msr01 + msr02 + msr03 + msr04 + msr05 + msr06 ) / 100, ( msr01 + msr02 + msr03 + msr04 + msr05 + msr06 ) / 35.314 ) AS 'CBM',
            IF( engmet = 'M', ( wgt01 + wgt02 + wgt03 + wgt04 + wgt05 + wgt06 ) / 100, ( wgt01 + wgt02 + wgt03 + wgt04 + wgt05 + wgt06 ) / 2.2046 ) AS 'KGS'
        FROM histry
        WHERE saildt = pSailDate
        AND airocn='O';

    DECLARE mCursorB CURSOR FOR
        SELECT DISTINCT port, glterm, cntvoy, blfdes
        FROM histry
        WHERE saildt = pSailDate
        AND airocn='O';

    DECLARE mCursorC CURSOR FOR
        SELECT DISTINCT glterm, PORT, blfdes, cntvoy
        FROM histry
        WHERE saildt = pSailDate
        AND airocn='O';

    -- SQL CURSOR declarations end here.

    -- SQL Error Handlers
    -- These must appear AFTER cursor declarations.
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET mRecordNotFound = TRUE;
    DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET mCursorEOL = TRUE;

    -- ****************************************************************
    -- VALIDATION

    -- Set the SQL Server Log Header.
    SET mSLH = CONCAT( ": pSailDate (", pSailDate, "): " );

    IF ISNULL( pSailDate ) OR pSailDate < 1 THEN
        CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "VALIDATION", mSLH, "Stopped; pSailDate invalid." ) );
        LEAVE MAIN;
    END IF;

    SET mSailDate = DATE_FORMAT( pSailDate, "%Y%m%d" );

    IF ISNULL( mSailDate ) THEN
        CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "VALIDATION", mSLH, "Stopped; pSailDate invalid." ) );
        LEAVE MAIN;
    END IF;

    -- VALIDATION
    -- ****************************************************************

    -- Debug Entry
    IF mDEBUG = TRUE THEN
        CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG", mSLH, "Begins." ) );
    END IF;

    -- Make sure the table is empty for the selected date
  -- because this procedure regens all the data for entire date!
    START TRANSACTION;
    DELETE FROM ExportDailyStats WHERE SailDate = mSailDate;
  COMMIT;

    -- *******************************************************
    -- Loop A
    -- Charges and Costs by BL

    START TRANSACTION;

    OPEN mCursorA;

    -- Debug Entry
    IF mDEBUG = TRUE THEN
        CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG: Cursor opened, starting loop A." ) );
    END IF;

    LoopA: LOOP

        -- Reset
        SET mTotalCBM = 0.00;
        SET mTotalKGS = 0.00;
        SET mTotalAPCost = 0.00;
        SET mTotalInvoice = 0.00;
        SET mTotalBiblesCost = 0.00;
        SET mTotalChgs = 0.00;
        SET mTotalCns = 0.00;

        SET mBLTerm = "";
        SET mPort = "";
        SET mDRTerm = "";
        SET mDRNum = "";
        SET mGLTerm = "";
        SET mCntVoy = "";
        SET mBLFdes = "";

        -- Get next record from the table.
        SET mCursorEOL = FALSE;
        FETCH mCursorA INTO  mBLTerm, mPort, mDRTerm, mDRNum, mGLTerm, mCntVoy, mBLFdes, mTotalChgs, mTotalCBM, mTotalKGS;

        IF mCursorEOL = TRUE THEN
            LEAVE LoopA;
        END IF;

        -- Get the total Correction Notices associated with this BL.
        -- Only get the LATEST entry (may be more than one entry but the latest is the final).
        SELECT
            IF ( ChgsChgCdeInGLNumb( ccd01, "3440" ), cna01, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd02, "3440" ), cna02, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd03, "3440" ), cna03, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd04, "3440" ), cna04, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd05, "3440" ), cna05, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd06, "3440" ), cna06, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd07, "3440" ), cna07, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd08, "3440" ), cna08, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd09, "3440" ), cna09, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd10, "3440" ), cna10, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd11, "3440" ), cna11, 0.00 ) +
            IF ( ChgsChgCdeInGLNumb( ccd12, "3440" ), cna12, 0.00 )
        INTO mTotalCns
        FROM cnoths
        WHERE blkey = CONCAT( mBLTerm, mPort, mDRTerm, mDRNum )
        ORDER BY cndate DESC
        LIMIT 1;

        -- Get the Type 4 COSTS associated with this BL.
        SELECT SUM( apcost.amount ) INTO mTotalAPCost
        FROM bldr, apcost
        WHERE bldr.blterm = mBLTerm
        AND bldr.PORT = mPort
        AND bldr.drterm = mDRTerm
        AND bldr.drnum = mDRNum
        AND apcost.type = '4'
        AND apcost.askfld = CONCAT( bldr.dr1, bldr.dr2 )
        AND apcost.gltrml = mGLTerm
        AND ( FclCodFclCodInGLNumb( apcost.cstcde, "3403" ) = TRUE
        OR FclCodFclCodInGLNumb( apcost.cstcde, "3492" ) = TRUE
        OR FclCodFclCodInGLNumb( apcost.cstcde, "4009" ) = TRUE
        OR FclCodFclCodInGLNumb( apcost.cstcde, "4010" ) = TRUE
        OR FclCodFclCodInGLNumb( apcost.cstcde, "4030" ) = TRUE
        OR FclCodFclCodInGLNumb( apcost.cstcde, "4041" ) = TRUE
        OR FclCodFclCodInGLNumb( apcost.cstcde, "4130" ) = TRUE );

        -- Cleanup Results
        IF ISNULL( mTotalCBM ) = TRUE THEN
            SET mTotalCBM = 0.00;
        END IF;

        IF ISNULL( mTotalKGS ) = TRUE THEN
            SET mTotalKGS = 0.00;
        END IF;

        IF ISNULL( mTotalAPCost ) = TRUE THEN
            SET mTotalAPCost = 0.00;
        END IF;

        IF ISNULL( mTotalInvoice ) = TRUE THEN
            SET mTotalInvoice = 0.00;
        END IF;

        IF ISNULL( mTotalBiblesCost ) = TRUE THEN
            SET mTotalBiblesCost = 0.00;
        END IF;

        IF ISNULL( mTotalChgs ) = TRUE THEN
            SET mTotalChgs = 0.00;
        END IF;

        IF ISNULL( mTotalCns ) = TRUE THEN
            SET mTotalCns = 0.00;
        END IF;

        -- Update temporary table.
        INSERT INTO ExportDailyStats SET
      SailDate = mSailDate,
            LoadTerm = mGLTerm,
            UnLoadTerm = mPort,
            DestTerm = mBLFdes,
            TotalCBM = mTotalCBM,
            TotalKGS = mTotalKGS,
            TotalAPCost4 = mTotalAPCost,
            TotalChgs = mTotalChgs,
            TotalCns = mTotalCns,
            TotalBLs = 1,
      RecLastUpdated = NULL
        ON DUPLICATE KEY UPDATE
            TotalCBM = TotalCBM + mTotalCBM,
            TotalKGS = TotalKGS + mTotalKGS,
            TotalAPCost4 = TotalAPCost4 + mTotalAPCost,
            TotalChgs = TotalChgs + mTotalChgs,
            TotalCns = TotalCns + mTotalCns,
            TotalBLs = TotalBLs + 1,
      RecLastUpdated = NULL;

    END LOOP LoopA;

    -- Cleanup
    CLOSE mCursorA;

    COMMIT;

    -- *******************************************************
    -- Loop B
    -- Costs by VOYAGE (Voyages consists of multiple BLs!)

    START TRANSACTION;

    OPEN mCursorB;

    -- Debug Entry
    IF mDEBUG = TRUE THEN
        CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG: Cursor opened, starting loop B." ) );
    END IF;

    LoopB: LOOP

        -- Reset
        SET mTotalCBM = 0.00;
        SET mTotalKGS = 0.00;
        SET mTotalAPCost = 0.00;
        SET mTotalBiblesCost = 0.00;
        SET mTotalChgs = 0.00;
        SET mTotalCns = 0.00;
        SET mTotalInvoice = 0.00;
        SET mTotalUnitVY = 0.00;

        SET mBLTerm = "";
        SET mPort = "";
        SET mDRTerm = "";
        SET mDRNum = "";
        SET mGLTerm = "";
        SET mCntVoy = "";
        SET mBLFdes = "";

        SET mTRecs = 0;

        -- Get next record from the table.
        SET mCursorEOL = FALSE;
        FETCH mCursorB INTO  mPort, mGLTerm, mCntVoy, mBLFdes;

        IF mCursorEOL = TRUE THEN
            LEAVE LoopB;
        END IF;

        -- Get the Type 5 COSTS associated with this Voyage.
        SELECT SUM( amount ) INTO mTotalAPCost
        FROM apcost
        WHERE type = '5'
        AND askfld LIKE CONCAT( mGLTerm, mPort, mCntVoy, "%" )
        AND gltrml = mGLTerm
        AND ( FclCodFclCodInGLNumb( cstcde, "3403" ) = TRUE
        OR  FclCodFclCodInGLNumb( cstcde, "3492" ) = TRUE
        OR FclCodFclCodInGLNumb( cstcde, "4009" ) = TRUE
        OR FclCodFclCodInGLNumb( cstcde, "4010" ) = TRUE
        OR FclCodFclCodInGLNumb( cstcde, "4030" ) = TRUE
        OR FclCodFclCodInGLNumb( cstcde, "4041" ) = TRUE
        OR FclCodFclCodInGLNumb( cstcde, "4130" ) = TRUE );

        IF ISNULL( mTotalAPCost ) = TRUE THEN
            SET mTotalAPCost = 0.00;
        END IF;

        SELECT IF( SUM( trlcbm ) != 0.00, SUM( trlcbm ), SUM( trlcft ) / 35.314 )
    INTO mTotalUnitVY
        FROM unitvy
        WHERE voynum = CONCAT( mGLTerm, mPort, mCntVoy );

        IF ISNULL( mTotalUnitVY ) THEN
            SET mTotalUnitVY = 0.00;
        END IF;

        -- Get the Bibles associated with this Voyage.
        SELECT amnt01 + amnt02 + amnt03 + amnt04 + amnt05 + amnt06 + amnt07 + amnt08 + amnt09 + amnt10 + amnt11 + amnt12
        INTO mTotalBiblesCost
        FROM bibles
        WHERE trmnum =  mGLTerm
        AND prtnum = mPort
        AND voynum = mCntVoy;

        IF ISNULL( mTotalBiblesCost ) = TRUE THEN
            SET mTotalBiblesCost = 0.00;
        END IF;

        -- Update temporary table - with TotalUnitVY.
        UPDATE ExportDailyStats SET
            TotalAPCost5 = TotalAPCost5 + mTotalAPCost,
            TotalUnitVY = TotalUnitVY + mTotalUnitVY,
      TotalBiblesCost = TotalBiblesCost + mTotalBiblesCost,
      RecLastUpdated = NULL
        WHERE SailDate = mSailDate
    AND LoadTerm = mGLTerm
        AND UnloadTerm = mPort
        AND DestTerm = mBLFdes;

    END LOOP LoopB;

    -- Cleanup
    CLOSE mCursorB;

    -- *******************************************************
    -- Loop C
    -- Invoices by Voyage Destination

    OPEN mCursorC;

    -- Debug Entry
    IF mDEBUG = TRUE THEN
        CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG: Cursor opened, starting loop C." ) );
    END IF;

    LoopC: LOOP

        -- Reset
        SET mTotalInvoice = 0.00;

        SET mBLTerm = "";
        SET mPort = "";
        SET mDRTerm = "";
        SET mDRNum = "";
        SET mGLTerm = "";
        SET mCntVoy = "";
        SET mBLFdes = "";

        SET mTRecs = 0;

        -- Get next record from the table.
        SET mCursorEOL = FALSE;
        FETCH mCursorC INTO mGLTerm, mPort, mBLFdes, mCntVoy;

        IF mCursorEOL = TRUE THEN
            LEAVE LoopC;
        END IF;

        -- Get the INVOICE charges associated with this BL.
        SELECT SUM( `InvAmts` ) INTO mTotalInvoice
        FROM
        (
            SELECT DISTINCT invoic.invnum,
            (
                IF ( ChgsChgCdeInGLNumb( invoic.chg01, "3492" ), invoic.amt01, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg01, "4030" ), invoic.amt01, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg02, "3492" ), invoic.amt02, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg02, "4030" ), invoic.amt02, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg03, "3492" ), invoic.amt03, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg03, "4030" ), invoic.amt03, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg04, "3492" ), invoic.amt04, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg04, "4030" ), invoic.amt04, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg05, "3492" ), invoic.amt05, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg05, "4030" ), invoic.amt05, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg06, "3492" ), invoic.amt06, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg06, "4030" ), invoic.amt06, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg07, "3492" ), invoic.amt07, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg07, "4030" ), invoic.amt07, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg08, "3492" ), invoic.amt08, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg08, "4030" ), invoic.amt08, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg09, "3492" ), invoic.amt09, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg09, "4030" ), invoic.amt09, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg10, "3492" ), invoic.amt10, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg10, "4030" ), invoic.amt10, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg11, "3492" ), invoic.amt11, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg11, "4030" ), invoic.amt11, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg12, "3492" ), invoic.amt12, 0.00 ) +
                IF ( ChgsChgCdeInGLNumb( invoic.chg12, "4030" ), invoic.amt12, 0.00 )
            ) AS `InvAmts`
            FROM histry, bldr, invdrs, invoic
            WHERE histry.saildt = pSailDate
            AND histry.port = mPort
            AND histry.glterm = mGLTerm
            AND histry.airocn = 'O'
            AND histry.cntvoy = mCntVoy
            AND bldr.blterm = histry.`blterm`
            AND bldr.PORT = histry.`port`
            AND bldr.drterm = histry.`drterm`
            AND bldr.drnum = histry.`drnum`
            AND invdrs.drcnum = CONCAT( bldr.dr1, bldr.dr2 )
            AND invoic.cmpnum = invdrs.cmpnum
            AND invoic.trmnum = invdrs.trmnum
            AND invoic.invnum = invdrs.invnum
            AND invoic.cntnum = invdrs.cntnum
        ) AS `SumAllInvoices`;

        IF ISNULL( mTotalInvoice ) = FALSE OR mTotalInvoice != 0.00 THEN
            -- Update temporary table - with TotalUnitVY.
            UPDATE ExportDailyStats SET
                TotalInvoice = TotalInvoice + mTotalInvoice,
              RecLastUpdated = NULL
      WHERE SailDate = mSailDate
      AND LoadTerm = mGLTerm
      AND UnloadTerm = mPort
      AND DestTerm = mBLFdes;
        END IF;

    END LOOP LoopC;

    -- Cleanup
    CLOSE mCursorC;


    -- *******************************************************
    -- General Expenses
    -- NOT CORRECT
    UPDATE ExportDailyStats, genexp SET
        TotalGenExp = TotalGenExp +
            ( amnt01 + amnt02 + amnt03 + amnt04 + amnt05 + amnt06 +
            amnt07 + amnt08 + amnt09 + amnt10 + amnt11 + amnt12 ),
    RecLastUpdated = NULL
    WHERE SailDate = mSailDate
  AND LoadTerm = genexp.trmnum
    AND UnloadTerm = genexp.prtnum
    AND genexp.geyear = "0000"
    AND genexp.gemnth = "00";

    COMMIT;

    -- Debug Entry
    IF mDEBUG = TRUE THEN
        CALL LogSQLEntry( 1, mSLS, mSLT, "DEBUG: Ends normally." );
    END IF;

END MAIN $$