';
if (isset($_GET['r']) && !empty($_GET['r'])) {
$report=$_GET['r'];
switch ($report){
case 'last':
echo 'LAST';
break;
case 'eoy':
echo 'EOY';
break;
case 'l14':
#sql Query
$sqlCmd="
select fidId, DayName(fidCurDate) as Day, Date(fidCurDate) as Date ,
CONCAT('$', FORMAT(fidTot, 2 )) as Total,
CONCAT('$', FORMAT(nTot, 2 )) as nTot,
CONCAT('$', FORMAT(mTot, 2 )) as mTot,
CONCAT('$', FORMAT(oTot, 2 )) as oTot
from fidelity order by fidCurDate DESC limit 14;";
showDataTable($sqlCmd,$pdo,'Last 14 Records');
break;
case 'jan':
$sqlCmd="select fidId, dayName(fidCurdate) as Day, date(fidCurDate) as Date, CONCAT('$', FORMAT(fidTot, 2 )) as Total ,CONCAT('$', FORMAT(nTot, 2 )) as Self, CONCAT('$', FORMAT(mTot, 2 )) as Mng, CONCAT('$', FORMAT(oTot, 2 )) as OtherTot from fidelity where month(fidCurDate)=month(now())-7 and year(fidCurDate) = Year(now())order by fidCurDate ASC;";
showDataTable($sqlCmd,$pdo,'Month: January');
break;
case 'prevMonth':
$sqlCmd="select fidId, dayName(fidCurdate) as Day, date(fidCurDate) as Date, CONCAT('$', FORMAT(fidTot, 2 )) as Total from fidelity where month(fidCurDate)=month(now())-1 and year(fidCurDate) = Year(now())order by fidCurDate DESC;";
showDataTable($sqlCmd,$pdo,'Previous Month');
break;
case 'self':
$sqlCmd="select fidId,date(fidCurDate) as Date, CONCAT('$', FORMAT(nTot, 2 )) as 'Self', CONCAT('$', FORMAT(mTot, 2 )) as 'Mng', CONCAT('$', FORMAT((nTot +mTot), 2 )) as Total, CONCAT('$', FORMAT(fidTot, 2 )) as FidTotal, CONCAT('$', FORMAT((fidTot-(nTot+mTot)), 2 )) as Other from fidelity where fidId > 1600 order by fidId Desc Limit 5;";
showDataTable($sqlCmd,$pdo,'Self vs Managed');
break;
case 'eoy':
$sqlCmd="SELECT Year(fidCurDate), DATE(fidCurDate), CONCAT('$', FORMAT(fidTot, 2)) AS formatted_fidTot FROM fidelity where fidId in (SELECT max(fidId) FROM fidelity GROUP BY Year(fidCurDate) ) order by Year(fidCurDate) asc";
showDataTable($sqlCmd,$pdo,'End of Year Totals ');
$sqlcmd="SELECT first_record.fidCurDate AS first_date, first_record.fidTot AS first_fidTot, last_record.fidCurDate AS last_date, last_record.fidTot AS last_fidTot, last_record.fidTot - first_record.fidTot AS difference FROM (SELECT fidCurDate, fidTot FROM fidelity WHERE fidCurDate >= '2024-01-01' ORDER BY fidCurDate LIMIT 1) AS first_record, (SELECT fidCurDate, fidTot FROM fidelity WHERE fidCurDate >= '2024-01-01' ORDER BY fidCurDate DESC LIMIT 1) AS last_record;";
showDataTable($sqlCmd,$pdo,'Current Year Change ');
} else if (isset($_GET['o']) && !empty($_GET['ddiff'])) {
$sqlCmd="SELECT DAYNAME(fidCurDate) as 'Day', date(fidCurDate) as 'Date', CONCAT('$', FORMAT(fidTot, 2 )) AS Total, concat('$ ',format((fidTot - LAG(fidTot, 1) OVER (ORDER BY fidCurDate)) ,2) )AS PrevDayDiff FROM fidelity ORDER BY fidCurDate Desc limit 5;";
showDataTable($sqlCmd,$pdo,'Show Daily Difference ');
break;
case 'd5':
#sqlCmd="SELECT fidId as 'Id', DAYNAME(fidCurDate) as 'Day',date(fidCurDate) as 'Date', fidTot as 'Total', ar091, ai163,at420,mi866,mi543,mt592,as551 from fidelity limit 2;";
#echo "
$sqlCmd=$sqlCmd
";
#echo "
OTHER $sqlCmd
";
$sqlCmd=" SELECT fidId as 'Id', DAYNAME(fidCurDate) as 'Day',date(fidCurDate) as 'Date',CONCAT('$', FORMAT(fidTot, 2)) as 'Total', CONCAT('$', FORMAT((ar091+ai163+at420),2)) as 'Non-Mng' , CONCAT('$',FORMAT((mi866+mi543+mt592+as551),2)) as 'Mng', CONCAT('$', CONCAT('$', FORMAT(fidTot, 2 )) as oTot FROM `fidelity` order by fidCurDate desc limit 6;";
showDataTable($sqlCmd,$pdo,'Default: Compare Managed ');
break;
case 'd5':
$sqlCmd="select * from fidelity order by fidCurDate DESC LIMIT 4";
$sqlCmd="SELECT fidId, date(fidCurDate) as 'Date',DAYNAME(fidCurDate) as 'Day',fidTot as 'Total', (ar091+ai163+at420) as 'Non-Mng' , (mi866+mi543+mt592+as551) as 'Mng', CONCAT('$', FORMAT(oTot, 2 )) as oTot FROM `fidelity` WHERE year(fidCurDate) = '2024' order by fidCurDate DESC LIMIT 6;";
showDataTable($sqlCmd,$pdo,'Last 5 Days1');
#sqlCmd="select Date(fidCurDate), DAYNAME(fidCUrDate), Concat('$ ', FORMAT(fidTot, 2)) from fidelity WHERE YEARWEEK(fidCurDate) = YEARWEEK(NOW());";
break;
case 'week1':
$sqlCmd="select fidId, yearweek(fidCurDate) as 'Yr/Wk', Date(fidCurDate) as 'Date', DAYNAME(fidCUrDate) as 'Day', Concat('$ ', FORMAT(fidTot, 2)) as 'Total' from fidelity WHERE YEARWEEK(fidCurDate) = 202401;";
showDataTable($sqlCmd,$pdo,'Week: 1 2024');
break;
case 'lastWeek':
$sqlCmd = 'junk';
showDataTable($sqlCmd,$pdo,'Last Week');
break;
case 'curMonth':
$sqlCmd="select fidCurDate ,DAYNAME (fidCurDate) as 'Date', concat('$',format(fidTot,2)) as 'Balance' from fidelity where month(fidCurDate) = '07' and Year(fidCurDate) = year(now()) order by date(fidCurDate) LIMIT 31;";
showDataTable($sqlCmd,$pdo,'Current Month ');
break;
case 'yearly':
$sqlCmd="-- select fidId, fidTot, fidCurDate,fidComment from fidelity order by fidCurDate desc LIMIT 21
-- select id,tags from tips;
SELECT
f1.Year,
CONCAT('$', FORMAT(f3.fidTot, 2)) as Last_fidTot,
CONCAT('$', FORMAT(f2.fidTot, 2)) as First_fidTot,
CONCAT('$', FORMAT((f3.fidTot - f2.fidTot), 2)) as TotDiff,
f1.MinDate as Date,,
f3.fidId as fidId,
f1.MaxDate as Date
FROM
(SELECT YEAR(fidCurDate) as Year, MIN(fidCurDate) as MinDate, MAX(fidCurDate) as MaxDate FROM fidelity GROUP BY YEAR(fidCurDate)) f1
JOIN
fidelity f2 ON f1.MinDate = f2.fidCurDate
JOIN
fidelity f3 ON f1.MaxDate = f3.fidCurDate
ORDER BY f1.Year DESC;";
showDataTable($sqlCmd,$pdo,'Yearly Results ');
break;
case 'dup':
$sqlCmd="select fidId, fidCurDate, dayname(fidCurDate) as 'DayOfWeek', fidTot ,
'!' as '!',ar091,ai163,at420,mi866,mi543,mt592,as551
from fidelity order by fidId DESC LIMIT 6;";
showDataTable($sqlCmd,$pdo,'Last 5 Results2 ');
} else if (isset($_GET['o']) && !empty($_GET['dup2'])) {
$sqlCmd="SELECT a.fidCurDate as Date, DAYNAME(a.fidCurDate) AS Day, a.fidId as fidId, CONCAT('$', FORMAT(a.fidTot, 2 )) as Total
FROM fidelity a
INNER JOIN fidelity b ON DATE(a.fidCurDate) = DATE(b.fidCurDate)
WHERE a.fidId <> b.fidId;";
showDataTable($sqlCmd,$pdo,'Duplicate Records ');
break;
case '50r':
$sqlCmd="select * from fidelity order by fidCurDate DESC LIMIT 50";
showDataTable($sqlCmd,$pdo,'50 Records ');
break;
} #end Switch
} #end of if rpt
$idNum=0;
?>