foxlibc-auth/sqlarg.groovy

void sqlto(String sql, Object... value)
{
    def builder                    = new StringBuilder();
    def ecpeidx          = []
    def charArray = sql.toCharArray()
    charArray.eachWithIndex { char ch, int i ->
        if (ch == '?')
            ecpeidx << i
    }

    if (ecpeidx.size() != value.length)
        throw new IndexOutOfBoundsException("SQL转义字符和参数个数不匹配。SQL转义字符共有: ${ecpeidx.size()}, 参数个数共有: ${value.length}")

    int argidx = 0;
    int lidx   = 0;
    for (int idx in ecpeidx) {
        builder.append(sql.substring(lidx, idx + 1))

        def argv = value[argidx];
        builder.append(argv instanceof String ? "'$argv'" : argv)

        ++argidx
        lidx = idx
    }

    builder.append(sql.substring(lidx))
    println(builder.toString().replace('?', ''))

}

sqlto("""
WITH A AS (
\tSELECT
\t\tfi.* 
\tFROM
\t\tflightinfo fi 
\tWHERE
\t\tfi.landingAirport = ? 
\t\tAND fi.flightDate BETWEEN ? 
\t\tAND ? 
\t\tAND landingAirport = ? 
\t\tAND flightDate BETWEEN ? 
\tAND ?) SELECT
\tli.FlightNO AS flightNO,
\tli.FlightDate AS flightDate,
\tli.departureAirport,
\t(
\tSELECT
\t\ta.flightState 
\tFROM
\t\tA AS a 
\tWHERE
\t\ta.flightNO = li.flightNO 
\t\tAND a.flightDate = li.flightDate 
\t\tAND a.departureAirport = li.departureAirport 
\t\tAND a.landingAirport = li.arriveAirport 
\t) AS flightStatus,
\t(
\tSELECT
\t\tifnull(
\t\t\ta.actualLandingTime,
\t\tifnull( a.estimatedLandingTime, a.planLandingTime )) 
\tFROM
\t\tA AS a 
\tWHERE
\t\ta.flightNO = li.flightNO 
\t\tAND a.flightDate = li.flightDate 
\t\tAND a.departureAirport = li.departureAirport 
\t\tAND a.landingAirport = li.arriveAirport 
\t) AS arrivalTime,
\t(
\tSELECT
\t\ta.departureTerminal 
\tFROM
\t\tA AS a 
\tWHERE
\t\ta.flightNO = li.flightNO 
\t\tAND a.flightDate = li.flightDate 
\t\tAND a.departureAirport = li.departureAirport 
\t\tAND a.landingAirport = li.arriveAirport 
\t) AS departureTerminal,
\t(
\tSELECT
\t\ta.landingTerminal 
\tFROM
\t\tA AS a 
\tWHERE
\t\ta.flightNO = li.flightNO 
\t\tAND a.flightDate = li.flightDate 
\t\tAND a.departureAirport = li.departureAirport 
\t\tAND a.landingAirport = li.arriveAirport 
\t) AS arrivalTerminal,
\t(
\tSELECT
\t\ta.extractionPlace 
\tFROM
\t\tA AS a 
\tWHERE
\t\ta.flightNO = li.flightNO 
\t\tAND a.flightDate = li.flightDate 
\t\tAND a.departureAirport = li.departureAirport 
\t\tAND a.landingAirport = li.arriveAirport 
\t\tLIMIT 1 
\t) AS luggageCarousel,
\t(
\tSELECT
\t\ta.landingStand 
\tFROM
\t\tA AS a 
\tWHERE
\t\ta.flightNO = li.flightNO 
\t\tAND a.flightDate = li.flightDate 
\t\tAND a.departureAirport = li.departureAirport 
\t\tAND a.landingAirport = li.arriveAirport 
\t\tLIMIT 1 
\t) AS parkingSpace,
\tsum(
\tNOT isnull( li.checkInNO )) AS checkIns,
\tsum(
\tIF
\t( li.canLoad = "Y", 1, 0 )) AS projectedLoad,
\tsum( li.loadID && li.abnormalState <> "OFF" ) AS loadedQuantity,
\tsum( li.arrivedID ) AS numberOfDestinationArrivals,
\tsum(
\tIF
\t( li.loadID <> 1, li.loadID - li.arrivedID, 0 )) AS endPointNotReached,
\tsum(
\tNOT isnull( li.specialType )) AS specialQuantity,
\tsum( li.compensatID ) AS numberOfClaims,
\tsum( li.unloadID ) AS uninstalled,
\tsum(
\tIF
\t( li.unloadID <> 1 AND li.boardID = 1, 1, 0 )) AS numberToBeUninstalled,
\tsum(
\tIF
\t( li.arrivedID = 1 AND isnull( li.transferFlightNO ), 1, 0 )) AS terminateArrivalQuantity,
\tsum(
\tIF
\t( li.arrivedID <> 1 AND isnull( li.transferFlightNO ), 1, 0 )) AS terminateUnreachedQuantity,
\tsum((
\t\tSELECT
\t\t\t1 
\t\tFROM
\t\t\tluggageinfo li2 
\t\tWHERE
\t\t\tli.luggageSN = li2.luggageSN 
\t\t\tAND li.flightDate = li2.inFlightDate 
\t\t\tAND li.flightNO = li2.inFlightNO 
\t\t\tAND ( li2.loadID = 1 OR li2.sorteID = 1 OR li2.boardID = 1 OR li2.arrivedID = 1 ) 
\t\t\tLIMIT 1 
\t\t)) AS quantityShipped,
\tsum(
\t\tisnull((
\t\t\tSELECT
\t\t\t\t1 
\t\t\tFROM
\t\t\t\tluggageinfo li2 
\t\t\tWHERE
\t\t\t\tli.luggageSN = li2.luggageSN 
\t\t\t\tAND li.flightDate = li2.inFlightDate 
\t\t\t\tAND li.flightNO = li2.inFlightNO 
\t\t\t\tAND ( li2.loadID = 1 OR li2.sorteID = 1 OR li2.boardID = 1 OR li2.arrivedID = 1 ) 
\t\t\t\tLIMIT 1 
\t\t\t))) AS undeliveredQuantity,
\tconcat( group_concat( DISTINCT li.containerID ), "(", sum( NOT isnull( li.containerID )), ")" ) AS numberOfContainers,
\tsum(
\tisnull( li.containerID )) AS numberOfBulk 
FROM
\tluggageinfo AS li 
WHERE
\tarriveAirport = ? 
\tAND flightDate BETWEEN ? 
\tAND ? 
GROUP BY
\tli.flightNO,
\tli.flightDate,
\tli.departureAirport,
\tli.arriveAirport
""", "2022-07-13","2022-07-13","CA1659","CA1659",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1,1,null,null,null,null,null,null,null,null)