KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Really Strange Bug, it is not showing an amount in the column in one situation, but it shows in the other same query #2721

Open paulo opened this topic on on Jun 10 - 4 comments

paulo commented on Jun 10

Hi there, I have a report with a complex query that tracks a commission distribution. I have the info in the database, returning properly. One staff can see the info in the report, the other can't. Please help me with this.

MySQL Query:

 select  T.id as tourid, concat_ws('',T.Prefix,' ' ,T.number,' ' ,T.name) as tourname,
                    T.startDate,
                    @PlannerID := T.Planner as PlannerID,
                    @QuoterID := T.Quoter as QuoterID,
                    Sassign.name as AssignTo,
                    TC.id as TCID, TC.tour_id,
                    estimated_sale, TC.vacation_agent as agent,

					(select concat_ws(' ',updated_at,Subject) as activity from activities where Status='Completed' and activatable_type='App\\\Tour' and activatable_id=T.id  order by updated_at desc limit 1 ) as LastUpdate,
                     onedrive_weburl as Sharepoint,
                     @Comm := IFNULL(comm,0) as comm,
                    margin,
                    @Claimed := IFNULL(TC.percent_claimed_current_year,100) as claimed,
                    TC.percent_claimed_current_year,  TC.amount_claimed_current_year,
                    tacct.date_closed as Acct_Date_Closed,
                    @acctCommAdj := ifnull(tacct.commission_adjstment,0) as Acctcomm_adj,

                    @acctReceipts := ifnull(tacct.receipts,0) as Acctreceipts,
					@acctExpenses := ifnull(tacct.expenses,0) as Acctexpenses,
					@acctTourDif := ifnull(tacct.tour_difference,0) as Accttour_difference,



                    @TotalEarnedCommThisYear := ((@Comm * @Claimed / 100) - @acctCommAdj) as TotalEarnedCommThisYear,


                    @acctNetprofit := ifnull(tacct.net_profit,0) as Acctnet_profit,
                    Format(IFNULL(tacct.net_gross_profit,tacct.net_profit*100/@acctReceipts),2) as Acctnet_Perc_profit ,
                    (@acctNetprofit - @acctCommAdj) as AcctEarnedCommThisYear,
                    TC.profile_sharing as ProfitSharingFlag,

                   @PaidPrevYears := ifnull(CASE  WHEN TC.profile_sharing=1  THEN
						(select paid_in_previous_year FROM tour_costing_staff where tour_costing_id=TCID AND staff_id=135)
                     ELSE
						@acctCommAdj
                     END,0) as PaidPrevYears,

                    @StaffAmount := ifnull((select staff_amount FROM tour_costing_staff where tour_costing_id=TCID AND staff_id=135),0) as StaffAmount,

                     @EarnedCommThisYear := ifnull(CASE  WHEN TC.profile_sharing=1 or NOT T.Quoter <=> T.Planner  THEN
						@StaffAmount
                     ELSE
						@TotalEarnedCommThisYear
                     END,0) as EarnedCommThisYear,

					@EarnedCommThisYear + @acctTourDif as Balance,
                    @EarnedCommThisYear  as BalanceQuoting,

                     ifnull(CASE  WHEN (TC.profile_sharing=1 or NOT T.Quoter <=> T.Planner) AND @StaffAmount=0   THEN
							   @TotalEarnedCommThisYear*80/100
                     END,0) as EstSharedComm,

                     ifnull(CASE  WHEN (TC.profile_sharing=1 or NOT T.Quoter <=> T.Planner) AND @StaffAmount=0   THEN
							   @TotalEarnedCommThisYear*20/100
                     END,0) as EstSharedCommQuoting,

                    S.id,
                    countryname, interest,
                    ST.id, ST.name as status

                    from tours as T
                    left join tour_costing AS TC on TC.tour_id = T.id
					left join tour_accts AS tacct on tacct.tour_id = T.id
                    LEFT join staff as S on S.id = T.Planner
                    left join staff as Sassign on Sassign.id = T.AssignTo

                    join statuses as ST on ST.id = T.status_id

                    LEFT join (SELECT tour_id, GROUP_CONCAT(name SEPARATOR ', ') as interest
							FROM tour_selected_category as catTour
						left join tour_interests AS cat on cat.id = catTour.category_id
                        GROUP BY  tour_id) as interests on interests.tour_id = T.id

					LEFT join (SELECT tour_id, GROUP_CONCAT(name SEPARATOR ', ') as countryname  FROM country_tour
						left join countries AS country on country.id = country_tour.country_id
						GROUP BY  tour_id) as countries on countries.tour_id = T.id
                        
                        WHERE T.tour_status !='0' AND ST.ClosedWMoney is null AND tacct.date_closed is null 
                        AND T.Planner=135 AND ST.noGo is null AND ST.Category='Potential'
                        AND T.id=2335
                    ORDER BY  T.startDate ASC

Result:

Query staff ID=184 results:

    <?php
        DataTables::create(array(
            "dataSource" => $this->dataStore("queryToursPotentialQuoting"),
            "attributes" => [
                "td" => function ($row, $colKey, $colMeta) {
                    if ($colKey === "LastUpdate") return [
                        "title" => $this->LastUpdate
                    ];
                    if ($colKey === "comm") return [
                        "title" => $this->GrossCommission
                    ];
                    if ($colKey === "Acctcomm_adj") return [
                        "title" => $this->TotalPaidPreviousyears
                    ];
                    if ($colKey === "TotalEarnedCommThisYear") return [
                        "title" => $this->TotalEarnedCommthisyear
                    ];
                    if ($colKey === "percent_claimed_current_year") return [
                        "title" => $this->ClaimedCurrentYear
                    ];
                    if ($colKey === "Accttour_difference") return [
                        "title" => $this->AcctTourDifference
                    ];
                    if ($colKey === "PaidPrevYears") return [
                        "title" => $this->PaidPreviousyears
                    ];
                    if ($colKey === "EarnedCommThisYear") return [
                        "title" => $this->Earnedcommthisyear
                    ];
                    if ($colKey === "Balance") return [
                        "title" => $this->Balance
                    ];
                    if ($colKey === "LastUpdate") return [
                        "title" => $this->LastUpdate
                    ];
                    if ($colKey === "EstSharedComm") return [
                        "title" => $this->EstSharedComm
                    ];


                    if ($colKey === "Acctnet_profit") return [
                        "title" => $this->AcctNet_profit
                    ];
                    if ($colKey === "Acctreceipts") return [
                        "title" => $this->AcctReceiptsexpenses
                    ];
                    if ($colKey === "AcctEarnedCommThisYear") return [
                        "title" => $this->acctearnedcommthisyear
                    ];
                    if ($colKey === "tourname") return [
                        "title" => $this->AcctCloseddate
                    ];

                }
            ],
            "options" => array(
                "fixedHeader" => true,
                "searching" => true,
                "colReorder" => true,
                "Responsive" => true,
                "order" => array(
                    array(1, "desc"), //Sort by first column desc
                    array(2, "asc"), //Sort by second column asc
                ),
            ),
            "showFooter" => "bottom",


            "columns" => array(
                "tourname" => array(
                    "label" => "Tour name",
                    "footerText" => "<b>Grand Totals</b>",
                    "formatValue" => function ($value, $row) {
                        // $tourID = $value; // or = $row["tourid"];
                        $tourID = $row["tourid"];
                        $tourName = $row["tourname"];
                        $acct_closed = $row["Acct_Date_Closed"];

                        $ProfitSharingFlag = $row["ProfitSharingFlag"] == 1 ? '<br/>sharing' : '';
                        if ($acct_closed) {
                            return "<a href='/tour/tours/$tourID'  target='_blank'>$tourName</a> <br/>Acct Closed: $acct_closed $ProfitSharingFlag";
                        } else {
                            return "<a href='/tour/tours/$tourID'  target='_blank'>$tourName</a> $ProfitSharingFlag";
                        }
                    },
                ),
                "status" => array(
                    "label" => "Status",
                ),
                "startDate" => array(
                    "label" => "Dept Date",
                ),


                "countryname" => array(
                    "label" => "Interest / Destination",
                    "formatValue" => function ($value, $row) {
                        // $tourID = $value; // or = $row["tourid"];
                        $tourID = $row["countryname"];
                        $interest = $row["interest"];
                        return "$interest<br/>$tourID";
                    },
                ),

                "AssignTo" => array(
                    "label" => "AssignTo",
                ),


                "comm" => array(
                    "label" => "Gross Commission",
                    "prefix" => "$",
                    "cssStyle" => "text-align:right",
                    "type" => "number",
                    "decimals" => 2,

                ),
                "margin" => array(
                    "label" => "Margin",
                    "type" => "number",
                    "decimals" => 2,
                    "suffix" => "%",
                    "formatValue"=> function($value,$row) {
                        // $tourID = $value; // or = $row["tourid"];
                        $tourID =  $row["tourid"];
                        $TCID = $row["TCID"];
                        $estSale = $row["margin"];
                        $mylink = $TCID?"tour/tour-costing/$TCID/edit":"tour/financial/$tourID";

                        return "<a href='/$mylink' target='_blank'>".number_format($estSale,2)."</a>";
                    },
                ),

                "Acctcomm_adj" => array(
                    "label" => "Total Paid Previous years",
                    "prefix" => "$",
                    "cssStyle" => "text-align:right",
                    "type" => "number",
                    "decimals" => 2,

                    "footer" => "sum",

                ),
                "percent_claimed_current_year" => array(
                    "label" => "% Claimed Current Year",
                    "type" => "number",
                    "cssStyle" => "text-align:right",
                    "decimals" => 2,
                    "suffix" => "%"
                ),
                "TotalEarnedCommThisYear" => array(
                    "label" => "Total Earned Comm this year",
                    "prefix" => "$",
                    "cssStyle" => "text-align:right",
                    "type" => "number",
                    "decimals" => 2,
                    "footer" => "sum",
                ),

                "PaidPrevYears" => array(
                    "label" => "Paid Previous years",
                    "prefix" => "$",
                    "cssStyle" => "text-align:right",
                    "type" => "number",
                    "decimals" => 2,
                    "footer" => "sum",
                ),
                "EarnedCommThisYear" => array(
                    "label" => "Earned Comm This Year",
                    "prefix" => "$",
                    "cssStyle" => "text-align:right",
                    "type" => "number",
                    "decimals" => 2,
                    "footer" => "sum",
                ),
                "EstSharedCommQuoting" => array(
                    "label" => "Est Shared Comm",
                    "prefix" => "$",
                    "cssStyle" => "text-align:right",
                    "type" => "number",
                    "decimals" => 2,
                    "footer" => "sum",
                ),
                "LastUpdate" => array(
                    "label" => "Sharepoint / LastUpdate",
                    "formatValue" => function ($value, $row) {
                        // $tourID = $value; // or = $row["tourid"];
                        $lastUpdate = $row["LastUpdate"];
                        $sharepoint = $row["Sharepoint"];
                        $mylink = $sharepoint ? "<a href='$sharepoint' target='_blank'>Sharepoint</a><br/>" : "";
                        return " $mylink $lastUpdate";
                    },
                ),


            ),
            "cssClass" => array(
                "table" => "table-bordered table-striped table-hover cell-border compact",
                'tf' => "text-right",
                "td" => function ($row, $colName) {
                    if (in_array($colName, array("Balance", "comm", "margin", "Acctcomm_adj", "EstSharedComm", "PaidPrevYears", "percent_claimed_current_year", "EarnedCommThisYear", "TotalEarnedCommThisYear", "Acctreceipts", "Accttour_difference", "Acctnet_profit", "AcctEarnedCommThisYear"))) {
                        return "text-right";
                    }
                },
            )
        ));
        ?>

thank you

paulo commented on Jun 10
$queryToursPotentialQuoting=$masterQuery."
                      WHERE T.tour_status !='0' AND T.Quoter=:plannerNumber AND ST.noGo is null AND ST.Category='Potential'  AND NOT T.Quoter <=> T.Planner
                    ORDER BY  T.startDate ASC
        ";

        Log::debug("MYEarnedCommissionV2 queryToursPotentialQuoting=" . $queryToursPotentialQuoting);
        $this->src('mysql')
            ->query($queryToursPotentialQuoting)
            ->params(array(
                ":plannerNumber"=>$this->params["plannerNumber"],
                ":plannerNumber2"=>$this->params["plannerNumber"],
                ":plannerNumber3"=>$this->params["plannerNumber"],
            ))
            ->pipe($this->dataStore("queryToursPotentialQuoting"));
Sebastian Morales commented on Jun 13

In your report view, pls add this command to check the datastore's data:

\koolreport\core\Utility::prettyPrint($this->dataStore("queryToursPotentialQuoting")->data());
...

Let us know if the data is what you expected or not. Tks,

paulo commented on Jun 22

sorry for the delay, I was away. This must be something with MySQL . After checking your code, it is a very strange error. If I put in the SQL where filtering by the tour id where T.id=2335 AND .. the test, the EarnedCommThisYear shows the amount properly. If I remove the filter by ID which shows all tours, it returns Zero. I know this is outside of the report, by would you have any ide why MySql would treat this differently ? with a list of tours this would be zero, but filtering by one tour the amount is correct? thanks

Sebastian Morales commented on Jun 22

I guess your amount comes from this expression:


                     @EarnedCommThisYear := ifnull(CASE  WHEN TC.profile_sharing=1 or NOT T.Quoter <=> T.Planner  THEN
						@StaffAmount
                     ELSE
						@TotalEarnedCommThisYear
                     END,0) as EarnedCommThisYear.

                    @TotalEarnedCommThisYear := ((@Comm * @Claimed / 100) - @acctCommAdj) as TotalEarnedCommThisYear

With filtered T.id and non-filtred T.ids @TotalEarnedCommThisYear changes, thus @EarnedCommThisYear changes, too.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
bug

Laravel