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