flightLog.lib.php ➔ sqlToArray() - Code Metrics - Inspection of "improvement of instructor" - laudeco/bbc-dolibarr-flightlog - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 6abb53...f63f4b )

by Laurent

created 2017-09-19 08:54 UTC

flightLog.lib.php ➔ sqlToArray() C

↳ Parent: Project

Complexity

Conditions	15
Paths	27

Size

Total Lines	61
Code Lines	40

Duplication

Lines	30
Ratio	49.18 %

Importance

Changes

Metric	Value
cc	15
eloc	40
nc	27
nop	4
dl	30
loc	61
rs	6.2274
c	0
b	0
f	0

How to fix Long Method Complexity

<?php
/**
 * @param DoliDb $db
 * @param string $sql
 * @param bool   $total
 * @param string $year
 *
 * @return array
 */
function sqlToArray(DoliDb $db, $sql, $total = true, $year = '')
{
    $resql = $db->query($sql);
    $array = array();
    if ($resql) {
        $num = $db->num_rows($resql);
        $i = 0;
        if ($num) {
            while ($i < $num) {
                $obj = $db->fetch_object($resql); //vol
                if ($obj) {
                    $array[$obj->pilot][$obj->type]['time'] = $obj->time;
                    $array[$obj->pilot][$obj->type]['count'] = $obj->nbr;
                    $array[$obj->pilot]['name'] = $obj->prenom . ' ' . $obj->nom;
                    $array[$obj->pilot]['id'] = $obj->pilot;
                }
                $i++;
            }
        }
    }

    //total orga
    $sql = 'SELECT llx_user.lastname as name , llx_user.firstname,llx_user.rowid, count(idBBC_vols) as total FROM llx_bbc_vols LEFT JOIN llx_user ON rowid = fk_organisateur WHERE YEAR(date) = \'' . $year . '\' AND fk_type IN (1,2) GROUP BY fk_organisateur';
    $resql = $db->query($sql);
    if ($resql && $total) {

        $num = $db->num_rows($resql);
        $i = 0;
        if ($num) {
            while ($i < $num) {
                $obj = $db->fetch_object($resql); //vol

                if ($obj) {
                    $array[$obj->rowid]['name'] = $obj->firstname . ' ' . $obj->name;
                    $array[$obj->rowid]['orga']['count'] = $obj->total;
                }
                $i++;
            }
        }
    }

    //total orga T6 - instructeur
    $sql = 'SELECT llx_user.lastname as name , llx_user.firstname,llx_user.rowid, count(idBBC_vols) as total FROM llx_bbc_vols LEFT JOIN llx_user ON rowid = fk_organisateur WHERE YEAR(date) = \'' . $year . '\' AND fk_type = 6 GROUP BY fk_organisateur';
    $resql = $db->query($sql);
    if ($resql && $total) {

        $num = $db->num_rows($resql);
        $i = 0;
        if ($num) {
            while ($i < $num) {
                $obj = $db->fetch_object($resql); //vol

                if ($obj) {
                    $array[$obj->rowid]['name'] = $obj->firstname . ' ' . $obj->name;
                    $array[$obj->rowid]['orga_T6']['count'] = $obj->total;
                }
                $i++;
            }
        }
    }

    return $array;
}

/**
 * @param int $active
 *
 * @return BbctypesLine[]
 */
function fetchBbcFlightTypes($active = 1)
{
    global $db;

    $bbcTypes = new Bbctypes($db);

    $bbcTypes->fetchAll('', '', 0, 0, [
        "active" => $active
    ]);

    return $bbcTypes->lines;
}

/**
 * Return list of flight type
 *
 * @param   mixed $selected  Preselected type
 * @param   mixed $htmlname  Name of field in form
 * @param   mixed $showempty Add an empty field
 */
function select_flight_type($selected = '1', $htmlname = 'type', $showempty = false)
{

    global $langs;
    $langs->load("trips");

    $types = fetchBbcFlightTypes();

    print '<select class="flat" name="' . $htmlname . '">';

    if ($showempty) {
        print sprintf('<option selected="%s" value=""></option>', (($selected == "" || $selected = 0 || $selected == -1) ? "selected" : ""));
    }

    foreach ($types as $flightType) {
        print '<option value="' . $flightType->id . '"';
        if ($flightType->numero == $selected) {
            print ' selected="selected"';
        }
        print '>';
        echo "T" . $flightType->numero . '-' . $flightType->nom;
        print "</option>";
    }

    print '</select>';
}

/**
 * @param string $selected
 * @param string $htmlname
 * @param int    $showimmat
 * @param int    $showDeclasse
 */
function select_balloons($selected = '', $htmlname = 'ballon', $showimmat = 0, $showDeclasse = 1)
{

    global $db, $langs, $user;
    $langs->load("trips");
    print '<!-- select_balloons in form class -->';
    print '<select class="flat" name="' . $htmlname . '">';

    print '<option value=""';
    if ($selected == -1 || $selected == '' || $selected == 0) {
        print ' selected="selected"';
    }
    print '>&nbsp;</option>';

    if (!$showDeclasse) {
        $resql = $db->query("SELECT B.immat,B.rowid FROM llx_bbc_ballons as B WHERE is_disable = false ");
    } else {
        $resql = $db->query("SELECT B.immat,B.rowid FROM llx_bbc_ballons as B");
    }

    if ($resql) {
        $num = $db->num_rows($resql);
        $i = 0;
        if ($num) {
            while ($i < $num) {
                $obj = $db->fetch_object($resql);
                if ($obj) {
                    if ($showimmat) {
                        print '<option value="' . $obj->immat . '"';
                    } else {
                        print '<option value="' . $obj->rowid . '"';
                    }
                    if ($obj->rowid == $selected) {
                        print ' selected="selected"';
                    }
                    print '>';
                    echo strtoupper($obj->immat);
                    print "</option>";
                }
                $i++;
            }
        }
    }

    print '</select>';
}

/**
 * @param null $year
 * @param null $pilotId
 * @param null $quarter
 * @param bool $groupBy
 *
 * @return string
 */
function generateQuarterQuery($year = null, $pilotId = null, $quarter = null, $groupBy = true)
{

    global $db;

    $sql = "SELECT USR.rowid, USR.lastname, USR.firstname, QUARTER(VOL.date) as quartil ";

    if ($groupBy) {
        $sql .= " , SUM(VOL.kilometers) as SUM";
        $sql .= " , COUNT(VOL.idBBC_vols) as nbrFlight";
    } else {
        $sql .= " , VOL.*";
    }

    $sql .= " FROM llx_bbc_vols as VOL";
    $sql .= " LEFT OUTER JOIN llx_user AS USR ON VOL.fk_pilot = USR.rowid";
    $sql .= " WHERE ";
    $sql .= " 1 = 1 ";
    $sql .= " AND YEAR(VOL.date) = " . ($year ?: 'YEAR(NOW())');
    $sql .= " AND ( VOL.fk_type = 1 OR VOL.fk_type = 2 ) ";

    if ($pilotId !== null) {
        $sql .= " AND USR.rowid = " . $pilotId;
    }

    if ($quarter !== null) {
        $sql .= " AND QUARTER(VOL.date) = " . $quarter;
    }

    if ($groupBy) {
        $sql .= " GROUP BY QUARTER(VOL.date), VOL.fk_pilot";
    }
    $sql .= " ORDER BY QUARTER(VOL.date), VOL.fk_pilot";

    return $db->escape($sql);
}

/**
 * @param int $pilotId
 * @param int $year
 * @param int $quarter
 *
 * @return array
 */
function findFlightByPilotAndQuarter($pilotId, $year, $quarter)
{
    global $db;

    $sql = generateQuarterQuery($year, $pilotId, $quarter, false);
    $flights = [];
    $resql = $db->query($sql);
    if ($resql) {
        $num = $db->num_rows($resql);
        $i = 0;
        if ($num) {
            while ($i < $num) {
                $flight = $db->fetch_object($resql);
                if ($flight) {
                    $flights[] = $flight;
                }
                $i++;
            }
        }
    }


    return $flights;
}

/**
 * @param int $year
 *
 * @return array
 */
function bbcKilometersByQuartil($year)
{
    global $db;

    $sql = generateQuarterQuery($year);
    $resql = $db->query($sql);

    $kmByQuartil = array();
    if ($resql) {
        $num = $db->num_rows($resql);
        $i = 0;
        if ($num) {
            while ($i < $num) {
                $obj = $db->fetch_object($resql); //vol
                if ($obj) {

                    $rowId = $obj->rowid;
                    $name = $obj->lastname;
                    $firstname = $obj->firstname;
                    $sum = $obj->SUM;
                    $quartil = $obj->quartil;

                    $kmByQuartil[$rowId]["name"] = $name;
                    $kmByQuartil[$rowId]["name"] = $name;
                    $kmByQuartil[$rowId]["name"] = $name;
                    $kmByQuartil[$rowId]["firstname"] = $firstname;

                    $kmByQuartil[$rowId]["quartil"][$quartil]["km"] = $sum;
                    $kmByQuartil[$rowId]["quartil"][$quartil]["flight"] = $obj->nbrFlight;


                }
                $i++;
            }
        }
    }

    return $kmByQuartil;
}

/**
 * @param $kmByQuartil
 * @param $tauxRemb
 */
function printBbcKilometersByQuartil($kmByQuartil, $tauxRemb, $unitPriceMission)
{
    global $user;

    print '<table class="border" width="100%">';

    print '<tr>';
    print '<td></td>';
    print '<td></td>';

    print '<td class="liste_titre" colspan="5">Trimestre 1 (Jan - Mars)</td>';
    print '<td class="liste_titre" colspan="5">Trimestre 2 (Avr - Juin)</td>';
    print '<td class="liste_titre" colspan="5">Trimestre 3 (Juil - Sept)</td>';
    print '<td class="liste_titre" colspan="5">Trimestre 4 (Oct - Dec)</td>';
    print '<td class="liste_titre" >Total</td>';

    print '</tr>';

    print '<tr class="liste_titre">';
    print '<td class="liste_titre" > Nom </td>';
    print '<td class="liste_titre" > Prenom </td>';


    print '<td class="liste_titre" > # T1 & T2</td>';
    print '<td class="liste_titre" > Forfaits pil </td>';
    print '<td class="liste_titre" > Total des KM </td>';
    print '<td class="liste_titre" > Remb km €</td>';
    print '<td class="liste_titre" > Total € </td>';

    print '<td class="liste_titre" > # T1 & T2</td>';
    print '<td class="liste_titre" > Forfaits pil </td>';
    print '<td class="liste_titre" > Total des KM </td>';
    print '<td class="liste_titre" > Remb km €</td>';
    print '<td class="liste_titre" > Total € </td>';

    print '<td class="liste_titre" > # T1 & T2</td>';
    print '<td class="liste_titre" > Forfaits pil </td>';
    print '<td class="liste_titre" > Total des KM </td>';
    print '<td class="liste_titre" > Remb km €</td>';
    print '<td class="liste_titre" > Total € </td>';

    print '<td class="liste_titre" > # T1 & T2</td>';
    print '<td class="liste_titre" > Forfaits pil </td>';
    print '<td class="liste_titre" > Total des KM </td>';
    print '<td class="liste_titre" > Remb km €</td>';
    print '<td class="liste_titre" > Total € </td>';

    print '<td class="liste_titre" > Total € </td>';
    print '</tr>';

    $totalQ1 = 0;
    $totalQ2 = 0;
    $totalQ3 = 0;
    $totalQ4 = 0;

    $curMonth = date("m", time());
    $curQuarter = ceil($curMonth/3);
    $disableColor = 'style="background-color: lightyellow;" title="N/A" data-toggle="tooltip"';

    foreach ($kmByQuartil as $id => $rembKm) {
        $name = $rembKm["name"];
        $firstname = $rembKm["firstname"];
        $sumQ1 = isset($rembKm["quartil"]["1"]["km"]) ? $rembKm["quartil"]["1"]["km"] : 0;
        $sumQ2 = isset($rembKm["quartil"]["2"]["km"]) ? $rembKm["quartil"]["2"]["km"] : 0;
        $sumQ3 = isset($rembKm["quartil"]["3"]["km"]) ? $rembKm["quartil"]["3"]["km"] : 0;
        $sumQ4 = isset($rembKm["quartil"]["4"]["km"]) ? $rembKm["quartil"]["4"]["km"] : 0;

        $flightsQ1 = isset($rembKm["quartil"]["1"]["flight"]) ? $rembKm["quartil"]["1"]["flight"] : 0;
        $flightsQ2 = isset($rembKm["quartil"]["2"]["flight"]) ? $rembKm["quartil"]["2"]["flight"] : 0;
        $flightsQ3 = isset($rembKm["quartil"]["3"]["flight"]) ? $rembKm["quartil"]["3"]["flight"] : 0;
        $flightsQ4 = isset($rembKm["quartil"]["4"]["flight"]) ? $rembKm["quartil"]["4"]["flight"] : 0;

        $amoutQ1 = ($sumQ1 * $tauxRemb) + ($flightsQ1 * $unitPriceMission);
        $amoutQ2 = ($sumQ2 * $tauxRemb) + ($flightsQ2 * $unitPriceMission);
        $amoutQ3 = ($sumQ3 * $tauxRemb) + ($flightsQ3 * $unitPriceMission);
        $amoutQ4 = ($sumQ4 * $tauxRemb) + ($flightsQ4 * $unitPriceMission);

        $totalQ1 += $amoutQ1;
        $totalQ2 += $amoutQ2;
        $totalQ3 += $amoutQ3;
        $totalQ4 += $amoutQ4;

        $sumKm = ($sumQ1 + $sumQ2 + $sumQ3 + $sumQ4);
        $sumFlights = ($flightsQ1 + $flightsQ2 + $flightsQ3 + $flightsQ4);

        print '<tr>';

        print '<td>' . $name . '</td>';
        print '<td>' . $firstname . '</td>';

        print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . ($flightsQ1) . '</td>';
        print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . ($flightsQ1 * $unitPriceMission) . '€</td>';
        print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . $sumQ1 . '</td>';
        print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . ($sumQ1 * $tauxRemb) . '</td>';
        print '<td'.($curQuarter < 1 ? $disableColor: '').'><b>' . $amoutQ1 . '€</b></td>';

        print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . ($flightsQ2) . '</td>';
        print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . ($flightsQ2 * $unitPriceMission) . '€</td>';
        print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . $sumQ2 . '</td>';
        print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . ($sumQ2 * $tauxRemb) . '</td>';
        print '<td '.($curQuarter < 2 ? $disableColor: '').'><b>' . $amoutQ2 . '€</b></td>';

        print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . ($flightsQ3) . '</td>';
        print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . ($flightsQ3 * $unitPriceMission) . '€</td>';
        print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . $sumQ3 . '</td>';
        print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . ($sumQ3 * $tauxRemb) . '</td>';
        print '<td '.($curQuarter < 3 ? $disableColor: '').'><b>' . $amoutQ3 . '€</b></td>';

        print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . ($flightsQ4) . '</td>';
        print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . ($flightsQ4 * $unitPriceMission) . '€</td>';
        print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . $sumQ4 . '</td>';
        print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . ($sumQ4 * $tauxRemb) . '</td>';
        print '<td '.($curQuarter < 4 ? $disableColor: '').'><b>' . $amoutQ4 . '€</b></td>';

        print '<td>' . (($sumFlights * $unitPriceMission) + ($sumKm * $tauxRemb)) . '€</td>';

        print '</tr>';
    }

    print "<td colspan='6'></td>";
    print "<td>".price($totalQ1)."€</td>";
    print "<td colspan='4'></td>";
    print "<td>".price($totalQ2)."€</td>";
    print "<td colspan='4'></td>";
    print "<td>".price($totalQ3)."€</td>";
    print "<td colspan='4'></td>";
    print "<td>".price($totalQ4)."€</td>";
    print "<td></td>";

    print '</table>';
}

/**
 * @return int[]
 */
function getFlightYears()
{
    global $db;

    $results = [];

    $sqlYear = "SELECT DISTINCT(YEAR(llx_bbc_vols.date)) as annee FROM llx_bbc_vols ";
    $resql_years = $db->query($sqlYear);

    $num = $db->num_rows($resql_years);
    $i = 0;
    if ($num) {
        while ($i < $num) {
            $obj = $db->fetch_object($resql_years);

            if ($obj->annee) {
                $results[] = $obj->annee;
            }

            $i++;
        }
    }

    return $results;
}

/**
 * @param array $results
 * @param int   $year
 * @param int   $type
 * @param int   $val
 *
 * @return array
 * @throws Exception
 */
function addValueForYear($results, $year, $type, $val)
{
    if (!is_array($results)) {
        return $results;
    }

    $countResults = count($results);

    for ($i = 0; $i < $countResults; $i++) {
        $resultLine = $results[$i];
        if (!is_array($resultLine)) {
            throw new \Exception("not an array ");
        }

        if (in_array($year, $resultLine)) {
            $results[$i][$type] = $val;
            return $results;
        }
    }

    //not found add a new entry
    $results[] = [
        $year,
        $type => $val
    ];

    return $results;

}

/**
 * @param GraphicalData $graphData
 *
 * @return GraphicalData
 */
function fetchGraphByTypeAndYearData(GraphicalData $graphData)
{
    global $db;

    $sql = "SELECT YEAR(date) as year, fk_type as type,COUNT(idBBC_vols) as val FROM llx_bbc_vols GROUP BY YEAR(date), fk_type ORDER BY year,fk_type";
    $resql = $db->query($sql);

    $num = $db->num_rows($resql);
    $i = 0;
    if ($num) {
        while ($i < $num) {
            $obj = $db->fetch_object($resql);

            if ($obj->year) {
                $graphData->addValue($obj->year, new GraphicalValue($obj->val, $obj->year, $obj->type));
            }

            $i++;
        }
    }

    return $graphData;
}

/**
 * @return GraphicalData
 */
function getGraphByTypeAndYearData()
{

    $flightTypes = fetchBbcFlightTypes();

    $graphData = new GraphicalData();

    foreach (getFlightYears() as $flightYear) {
        $pieceData = new YearGraphicalData($flightYear);

        foreach ($flightTypes as $flightType) {
            $pieceData->addType(new GraphicalType($flightType->id, $flightType->nom));
        }

        $graphData->addData($pieceData);
    }

    return fetchGraphByTypeAndYearData($graphData);
}

1		<?php
2		/**
3		* @param DoliDb $db
4		* @param string $sql
5		* @param bool $total
6		* @param string $year
7		*
8		* @return array
9		*/
10		function sqlToArray(DoliDb $db, $sql, $total = true, $year = '')
11		{
12		$resql = $db->query($sql);
13		$array = array();
14		if ($resql) {
15		$num = $db->num_rows($resql);
16		$i = 0;
17		if ($num) {
18		while ($i < $num) {
19		$obj = $db->fetch_object($resql); //vol
20		if ($obj) {
21		$array[$obj->pilot][$obj->type]['time'] = $obj->time;
22		$array[$obj->pilot][$obj->type]['count'] = $obj->nbr;
23		$array[$obj->pilot]['name'] = $obj->prenom . ' ' . $obj->nom;
24		$array[$obj->pilot]['id'] = $obj->pilot;
25		}
26		$i++;
27		}
28		}
29		}
30
31		//total orga
32		$sql = 'SELECT llx_user.lastname as name , llx_user.firstname,llx_user.rowid, count(idBBC_vols) as total FROM llx_bbc_vols LEFT JOIN llx_user ON rowid = fk_organisateur WHERE YEAR(date) = \'' . $year . '\' AND fk_type IN (1,2) GROUP BY fk_organisateur';
33		$resql = $db->query($sql);
34	View Code Duplication	if ($resql && $total) {
		0 ignored issues – show Duplication introduced 2017-09-19 08:56 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
35		$num = $db->num_rows($resql);
36		$i = 0;
37		if ($num) {
38		while ($i < $num) {
39		$obj = $db->fetch_object($resql); //vol
40
41		if ($obj) {
42		$array[$obj->rowid]['name'] = $obj->firstname . ' ' . $obj->name;
43		$array[$obj->rowid]['orga']['count'] = $obj->total;
44		}
45		$i++;
46		}
47		}
48		}
49
50		//total orga T6 - instructeur
51		$sql = 'SELECT llx_user.lastname as name , llx_user.firstname,llx_user.rowid, count(idBBC_vols) as total FROM llx_bbc_vols LEFT JOIN llx_user ON rowid = fk_organisateur WHERE YEAR(date) = \'' . $year . '\' AND fk_type = 6 GROUP BY fk_organisateur';
52		$resql = $db->query($sql);
53	View Code Duplication	if ($resql && $total) {
		0 ignored issues – show Duplication introduced 2017-09-19 08:56 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
54		$num = $db->num_rows($resql);
55		$i = 0;
56		if ($num) {
57		while ($i < $num) {
58		$obj = $db->fetch_object($resql); //vol
59
60		if ($obj) {
61		$array[$obj->rowid]['name'] = $obj->firstname . ' ' . $obj->name;
62		$array[$obj->rowid]['orga_T6']['count'] = $obj->total;
63		}
64		$i++;
65		}
66		}
67		}
68
69		return $array;
70		}
71
72		/**
73		* @param int $active
74		*
75		* @return BbctypesLine[]
76		*/
77		function fetchBbcFlightTypes($active = 1)
78		{
79		global $db;
80
81		$bbcTypes = new Bbctypes($db);
82
83		$bbcTypes->fetchAll('', '', 0, 0, [
84		"active" => $active
85		]);
86
87		return $bbcTypes->lines;
88		}
89
90		/**
91		* Return list of flight type
92		*
93		* @param mixed $selected Preselected type
94		* @param mixed $htmlname Name of field in form
95		* @param mixed $showempty Add an empty field
96		*/
97		function select_flight_type($selected = '1', $htmlname = 'type', $showempty = false)
98		{
99
100		global $langs;
101		$langs->load("trips");
102
103		$types = fetchBbcFlightTypes();
104
105		print '<select class="flat" name="' . $htmlname . '">';
106
107		if ($showempty) {
108		print sprintf('<option selected="%s" value=""></option>', (($selected == "" \|\| $selected = 0 \|\| $selected == -1) ? "selected" : ""));
109		}
110
111		foreach ($types as $flightType) {
112		print '<option value="' . $flightType->id . '"';
113		if ($flightType->numero == $selected) {
114		print ' selected="selected"';
115		}
116		print '>';
117		echo "T" . $flightType->numero . '-' . $flightType->nom;
118		print "</option>";
119		}
120
121		print '</select>';
122		}
123
124		/**
125		* @param string $selected
126		* @param string $htmlname
127		* @param int $showimmat
128		* @param int $showDeclasse
129		*/
130		function select_balloons($selected = '', $htmlname = 'ballon', $showimmat = 0, $showDeclasse = 1)
131		{
132
133		global $db, $langs, $user;
134		$langs->load("trips");
135		print '<!-- select_balloons in form class -->';
136		print '<select class="flat" name="' . $htmlname . '">';
137
138		print '<option value=""';
139		if ($selected == -1 \|\| $selected == '' \|\| $selected == 0) {
140		print ' selected="selected"';
141		}
142		print '> </option>';
143
144		if (!$showDeclasse) {
145		$resql = $db->query("SELECT B.immat,B.rowid FROM llx_bbc_ballons as B WHERE is_disable = false ");
146		} else {
147		$resql = $db->query("SELECT B.immat,B.rowid FROM llx_bbc_ballons as B");
148		}
149
150		if ($resql) {
151		$num = $db->num_rows($resql);
152		$i = 0;
153		if ($num) {
154		while ($i < $num) {
155		$obj = $db->fetch_object($resql);
156		if ($obj) {
157		if ($showimmat) {
158		print '<option value="' . $obj->immat . '"';
159		} else {
160		print '<option value="' . $obj->rowid . '"';
161		}
162		if ($obj->rowid == $selected) {
163		print ' selected="selected"';
164		}
165		print '>';
166		echo strtoupper($obj->immat);
167		print "</option>";
168		}
169		$i++;
170		}
171		}
172		}
173
174		print '</select>';
175		}
176
177		/**
178		* @param null $year
179		* @param null $pilotId
180		* @param null $quarter
181		* @param bool $groupBy
182		*
183		* @return string
184		*/
185		function generateQuarterQuery($year = null, $pilotId = null, $quarter = null, $groupBy = true)
186		{
187
188		global $db;
189
190		$sql = "SELECT USR.rowid, USR.lastname, USR.firstname, QUARTER(VOL.date) as quartil ";
191
192		if ($groupBy) {
193		$sql .= " , SUM(VOL.kilometers) as SUM";
194		$sql .= " , COUNT(VOL.idBBC_vols) as nbrFlight";
195		} else {
196		$sql .= " , VOL.*";
197		}
198
199		$sql .= " FROM llx_bbc_vols as VOL";
200		$sql .= " LEFT OUTER JOIN llx_user AS USR ON VOL.fk_pilot = USR.rowid";
201		$sql .= " WHERE ";
202		$sql .= " 1 = 1 ";
203		$sql .= " AND YEAR(VOL.date) = " . ($year ?: 'YEAR(NOW())');
204		$sql .= " AND ( VOL.fk_type = 1 OR VOL.fk_type = 2 ) ";
205
206		if ($pilotId !== null) {
207		$sql .= " AND USR.rowid = " . $pilotId;
208		}
209
210		if ($quarter !== null) {
211		$sql .= " AND QUARTER(VOL.date) = " . $quarter;
212		}
213
214		if ($groupBy) {
215		$sql .= " GROUP BY QUARTER(VOL.date), VOL.fk_pilot";
216		}
217		$sql .= " ORDER BY QUARTER(VOL.date), VOL.fk_pilot";
218
219		return $db->escape($sql);
220		}
221
222		/**
223		* @param int $pilotId
224		* @param int $year
225		* @param int $quarter
226		*
227		* @return array
228		*/
229		function findFlightByPilotAndQuarter($pilotId, $year, $quarter)
230		{
231		global $db;
232
233		$sql = generateQuarterQuery($year, $pilotId, $quarter, false);
234		$flights = [];
235		$resql = $db->query($sql);
236		if ($resql) {
237		$num = $db->num_rows($resql);
238		$i = 0;
239		if ($num) {
240		while ($i < $num) {
241		$flight = $db->fetch_object($resql);
242		if ($flight) {
243		$flights[] = $flight;
244		}
245		$i++;
246		}
247		}
248		}
249
250
251		return $flights;
252		}
253
254		/**
255		* @param int $year
256		*
257		* @return array
258		*/
259		function bbcKilometersByQuartil($year)
260		{
261		global $db;
262
263		$sql = generateQuarterQuery($year);
264		$resql = $db->query($sql);
265
266		$kmByQuartil = array();
267		if ($resql) {
268		$num = $db->num_rows($resql);
269		$i = 0;
270		if ($num) {
271		while ($i < $num) {
272		$obj = $db->fetch_object($resql); //vol
273		if ($obj) {
274
275		$rowId = $obj->rowid;
276		$name = $obj->lastname;
277		$firstname = $obj->firstname;
278		$sum = $obj->SUM;
279		$quartil = $obj->quartil;
280
281		$kmByQuartil[$rowId]["name"] = $name;
282		$kmByQuartil[$rowId]["name"] = $name;
283		$kmByQuartil[$rowId]["name"] = $name;
284		$kmByQuartil[$rowId]["firstname"] = $firstname;
285
286		$kmByQuartil[$rowId]["quartil"][$quartil]["km"] = $sum;
287		$kmByQuartil[$rowId]["quartil"][$quartil]["flight"] = $obj->nbrFlight;
288
289
290		}
291		$i++;
292		}
293		}
294		}
295
296		return $kmByQuartil;
297		}
298
299		/**
300		* @param $kmByQuartil
301		* @param $tauxRemb
302		*/
303		function printBbcKilometersByQuartil($kmByQuartil, $tauxRemb, $unitPriceMission)
304		{
305		global $user;
306
307		print '<table class="border" width="100%">';
308
309		print '<tr>';
310		print '<td></td>';
311		print '<td></td>';
312
313		print '<td class="liste_titre" colspan="5">Trimestre 1 (Jan - Mars)</td>';
314		print '<td class="liste_titre" colspan="5">Trimestre 2 (Avr - Juin)</td>';
315		print '<td class="liste_titre" colspan="5">Trimestre 3 (Juil - Sept)</td>';
316		print '<td class="liste_titre" colspan="5">Trimestre 4 (Oct - Dec)</td>';
317		print '<td class="liste_titre" >Total</td>';
318
319		print '</tr>';
320
321		print '<tr class="liste_titre">';
322		print '<td class="liste_titre" > Nom </td>';
323		print '<td class="liste_titre" > Prenom </td>';
324
325
326		print '<td class="liste_titre" > # T1 & T2</td>';
327		print '<td class="liste_titre" > Forfaits pil </td>';
328		print '<td class="liste_titre" > Total des KM </td>';
329		print '<td class="liste_titre" > Remb km €</td>';
330		print '<td class="liste_titre" > Total € </td>';
331
332		print '<td class="liste_titre" > # T1 & T2</td>';
333		print '<td class="liste_titre" > Forfaits pil </td>';
334		print '<td class="liste_titre" > Total des KM </td>';
335		print '<td class="liste_titre" > Remb km €</td>';
336		print '<td class="liste_titre" > Total € </td>';
337
338		print '<td class="liste_titre" > # T1 & T2</td>';
339		print '<td class="liste_titre" > Forfaits pil </td>';
340		print '<td class="liste_titre" > Total des KM </td>';
341		print '<td class="liste_titre" > Remb km €</td>';
342		print '<td class="liste_titre" > Total € </td>';
343
344		print '<td class="liste_titre" > # T1 & T2</td>';
345		print '<td class="liste_titre" > Forfaits pil </td>';
346		print '<td class="liste_titre" > Total des KM </td>';
347		print '<td class="liste_titre" > Remb km €</td>';
348		print '<td class="liste_titre" > Total € </td>';
349
350		print '<td class="liste_titre" > Total € </td>';
351		print '</tr>';
352
353		$totalQ1 = 0;
354		$totalQ2 = 0;
355		$totalQ3 = 0;
356		$totalQ4 = 0;
357
358		$curMonth = date("m", time());
359		$curQuarter = ceil($curMonth/3);
360		$disableColor = 'style="background-color: lightyellow;" title="N/A" data-toggle="tooltip"';
361
362		foreach ($kmByQuartil as $id => $rembKm) {
363		$name = $rembKm["name"];
364		$firstname = $rembKm["firstname"];
365		$sumQ1 = isset($rembKm["quartil"]["1"]["km"]) ? $rembKm["quartil"]["1"]["km"] : 0;
366		$sumQ2 = isset($rembKm["quartil"]["2"]["km"]) ? $rembKm["quartil"]["2"]["km"] : 0;
367		$sumQ3 = isset($rembKm["quartil"]["3"]["km"]) ? $rembKm["quartil"]["3"]["km"] : 0;
368		$sumQ4 = isset($rembKm["quartil"]["4"]["km"]) ? $rembKm["quartil"]["4"]["km"] : 0;
369
370		$flightsQ1 = isset($rembKm["quartil"]["1"]["flight"]) ? $rembKm["quartil"]["1"]["flight"] : 0;
371		$flightsQ2 = isset($rembKm["quartil"]["2"]["flight"]) ? $rembKm["quartil"]["2"]["flight"] : 0;
372		$flightsQ3 = isset($rembKm["quartil"]["3"]["flight"]) ? $rembKm["quartil"]["3"]["flight"] : 0;
373		$flightsQ4 = isset($rembKm["quartil"]["4"]["flight"]) ? $rembKm["quartil"]["4"]["flight"] : 0;
374
375		$amoutQ1 = ($sumQ1 * $tauxRemb) + ($flightsQ1 * $unitPriceMission);
376		$amoutQ2 = ($sumQ2 * $tauxRemb) + ($flightsQ2 * $unitPriceMission);
377		$amoutQ3 = ($sumQ3 * $tauxRemb) + ($flightsQ3 * $unitPriceMission);
378		$amoutQ4 = ($sumQ4 * $tauxRemb) + ($flightsQ4 * $unitPriceMission);
379
380		$totalQ1 += $amoutQ1;
381		$totalQ2 += $amoutQ2;
382		$totalQ3 += $amoutQ3;
383		$totalQ4 += $amoutQ4;
384
385		$sumKm = ($sumQ1 + $sumQ2 + $sumQ3 + $sumQ4);
386		$sumFlights = ($flightsQ1 + $flightsQ2 + $flightsQ3 + $flightsQ4);
387
388		print '<tr>';
389
390		print '<td>' . $name . '</td>';
391		print '<td>' . $firstname . '</td>';
392
393		print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . ($flightsQ1) . '</td>';
394		print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . ($flightsQ1 * $unitPriceMission) . '€</td>';
395		print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . $sumQ1 . '</td>';
396		print '<td'.($curQuarter < 1 ? $disableColor: '').'>' . ($sumQ1 * $tauxRemb) . '</td>';
397		print '<td'.($curQuarter < 1 ? $disableColor: '').'><b>' . $amoutQ1 . '€</b></td>';
398
399		print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . ($flightsQ2) . '</td>';
400		print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . ($flightsQ2 * $unitPriceMission) . '€</td>';
401		print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . $sumQ2 . '</td>';
402		print '<td '.($curQuarter < 2 ? $disableColor: '').'>' . ($sumQ2 * $tauxRemb) . '</td>';
403		print '<td '.($curQuarter < 2 ? $disableColor: '').'><b>' . $amoutQ2 . '€</b></td>';
404
405		print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . ($flightsQ3) . '</td>';
406		print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . ($flightsQ3 * $unitPriceMission) . '€</td>';
407		print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . $sumQ3 . '</td>';
408		print '<td '.($curQuarter < 3 ? $disableColor: '').'>' . ($sumQ3 * $tauxRemb) . '</td>';
409		print '<td '.($curQuarter < 3 ? $disableColor: '').'><b>' . $amoutQ3 . '€</b></td>';
410
411		print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . ($flightsQ4) . '</td>';
412		print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . ($flightsQ4 * $unitPriceMission) . '€</td>';
413		print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . $sumQ4 . '</td>';
414		print '<td '.($curQuarter < 4 ? $disableColor: '').'>' . ($sumQ4 * $tauxRemb) . '</td>';
415		print '<td '.($curQuarter < 4 ? $disableColor: '').'><b>' . $amoutQ4 . '€</b></td>';
416
417		print '<td>' . (($sumFlights * $unitPriceMission) + ($sumKm * $tauxRemb)) . '€</td>';
418
419		print '</tr>';
420		}
421
422		print "<td colspan='6'></td>";
423		print "<td>".price($totalQ1)."€</td>";
424		print "<td colspan='4'></td>";
425		print "<td>".price($totalQ2)."€</td>";
426		print "<td colspan='4'></td>";
427		print "<td>".price($totalQ3)."€</td>";
428		print "<td colspan='4'></td>";
429		print "<td>".price($totalQ4)."€</td>";
430		print "<td></td>";
431
432		print '</table>';
433		}
434
435		/**
436		* @return int[]
437		*/
438		function getFlightYears()
439		{
440		global $db;
441
442		$results = [];
443
444		$sqlYear = "SELECT DISTINCT(YEAR(llx_bbc_vols.date)) as annee FROM llx_bbc_vols ";
445		$resql_years = $db->query($sqlYear);
446
447		$num = $db->num_rows($resql_years);
448		$i = 0;
449		if ($num) {
450		while ($i < $num) {
451		$obj = $db->fetch_object($resql_years);
452
453		if ($obj->annee) {
454		$results[] = $obj->annee;
455		}
456
457		$i++;
458		}
459		}
460
461		return $results;
462		}
463
464		/**
465		* @param array $results
466		* @param int $year
467		* @param int $type
468		* @param int $val
469		*
470		* @return array
471		* @throws Exception
472		*/
473		function addValueForYear($results, $year, $type, $val)
474		{
475		if (!is_array($results)) {
476		return $results;
477		}
478
479		$countResults = count($results);
480
481		for ($i = 0; $i < $countResults; $i++) {
482		$resultLine = $results[$i];
483		if (!is_array($resultLine)) {
484		throw new \Exception("not an array ");
485		}
486
487		if (in_array($year, $resultLine)) {
488		$results[$i][$type] = $val;
489		return $results;
490		}
491		}
492
493		//not found add a new entry
494		$results[] = [
495		$year,
496		$type => $val
497		];
498
499		return $results;
500
501		}
502
503		/**
504		* @param GraphicalData $graphData
505		*
506		* @return GraphicalData
507		*/
508		function fetchGraphByTypeAndYearData(GraphicalData $graphData)
509		{
510		global $db;
511
512		$sql = "SELECT YEAR(date) as year, fk_type as type,COUNT(idBBC_vols) as val FROM llx_bbc_vols GROUP BY YEAR(date), fk_type ORDER BY year,fk_type";
513		$resql = $db->query($sql);
514
515		$num = $db->num_rows($resql);
516		$i = 0;
517		if ($num) {
518		while ($i < $num) {
519		$obj = $db->fetch_object($resql);
520
521		if ($obj->year) {
522		$graphData->addValue($obj->year, new GraphicalValue($obj->val, $obj->year, $obj->type));
523		}
524
525		$i++;
526		}
527		}
528
529		return $graphData;
530		}
531
532		/**
533		* @return GraphicalData
534		*/
535		function getGraphByTypeAndYearData()
536		{
537
538		$flightTypes = fetchBbcFlightTypes();
539
540		$graphData = new GraphicalData();
541
542		foreach (getFlightYears() as $flightYear) {
543		$pieceData = new YearGraphicalData($flightYear);
544
545		foreach ($flightTypes as $flightType) {
546		$pieceData->addType(new GraphicalType($flightType->id, $flightType->nom));
547		}
548
549		$graphData->addData($pieceData);
550		}
551
552		return fetchGraphByTypeAndYearData($graphData);
553		}

laudeco / bbc-dolibarr-flightlog

Push — master ( 6abb53...f63f4b )

flightLog.lib.php ➔ sqlToArray() C

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like