['50' => 10, '100' => 25, '200' => 60, '500' => 100, 'above' => 150], '360' => ['50' => 25, '100' => 60, '200' => 150, '500' => 200, 'above' => 250], '25' => ['50' => 10, '100' => 25, '200' => 60, '500' => 100, 'above' => 150], '50' => ['50' => 20, '100' => 50, '200' => 120, '500' => 170, 'above' => 200], '6' => ['50' => 10, '100' => 25, '200' => 60, '500' => 100, 'above' => 150], '12' => ['50' => 20, '100' => 50, '200' => 120, '500' => 170, 'above' => 200] ]; $commission = 0; // Loan account commission (0.5% of total amount) if ($accountType === 'Loan') { $commission = $totalAmount * 0.005; return $commission; } // Recurring account commission - ONLY for exact amounts in chart (50, 100, 200, 500, above 500) if ($accountType === 'Recurring') { $planKey = ''; if ($accountCycle === 'D') { $planKey = ($totalPayments <= 180) ? '180' : '360'; } elseif ($accountCycle === 'W') { $planKey = ($totalPayments <= 25) ? '25' : '50'; } elseif ($accountCycle === 'M') { $planKey = ($totalPayments <= 6) ? '6' : '12'; } if (isset($commissionChart[$planKey])) { $plan = $commissionChart[$planKey]; // ONLY give commission for exact amounts that are in the chart if ($installmentAmount == 50) { $commission = $plan['50']; } elseif ($installmentAmount == 100) { $commission = $plan['100']; } elseif ($installmentAmount == 200) { $commission = $plan['200']; } elseif ($installmentAmount == 500) { $commission = $plan['500']; } elseif ($installmentAmount > 500) { $commission = $plan['above']; } // For amounts like 20, 30, 40, 70, 150, 300 etc. - NO COMMISSION (commission remains 0) } } return $commission; } function calculateCollectionCommission($agentId, $dateFrom, $dateTo) { $conn = new mysqli($GLOBALS['host'], $GLOBALS['user'], $GLOBALS['pass'], $GLOBALS['db']); // Get agent's commission rate $agentSql = "SELECT comi_rate FROM `".$GLOBALS['arif_users']."` WHERE user_id = '$agentId'"; $agentResult = $conn->query($agentSql); $comiRate = 0; if ($agentResult && $agentResult->num_rows > 0) { $agentData = $agentResult->fetch_assoc(); $comiRate = $agentData['comi_rate']; } // Get total collection amount for this agent's accounts $collectionSql = "SELECT COALESCE(SUM(t.AT_AMOUNT),0) as total_collection FROM `".$GLOBALS['arif_tran']."` t JOIN `".$GLOBALS['arif_ac']."` a ON t.AT_ACID = a.AA_ACNO WHERE a.AA_AGENT = '$agentId' AND t.AT_TIMESTAMP BETWEEN '$dateFrom 00:00:00' AND '$dateTo 23:59:59'"; $collectionResult = $conn->query($collectionSql); $totalCollection = 0; if ($collectionResult && $collectionResult->num_rows > 0) { $collectionData = $collectionResult->fetch_assoc(); $totalCollection = $collectionData['total_collection']; } $conn->close(); // Calculate collection commission $collectionCommission = ($totalCollection * $comiRate) / 100; return [ 'total_collection' => $totalCollection, 'comi_rate' => $comiRate, 'collection_commission' => $collectionCommission ]; } function dual_commission_report($dateFrom, $dateTo) { echo '

Dual Commission Report: '.$dateFrom." → ".$dateTo.'

Showing both Account Opening Commission and Collection Commission

'; $conn = new mysqli($GLOBALS['host'], $GLOBALS['user'], $GLOBALS['pass'], $GLOBALS['db']); if ($conn->connect_error) die("Connection failed: " . $conn->connect_error); // ---- Login user type check ---- $loginType = $_SESSION['type'] ?? ''; $loginId = $_SESSION['user_id'] ?? ''; // ---- Get ALL agents first ---- $agentsSql = "SELECT user_id, user_name, comi_rate FROM `".$GLOBALS['arif_users']."` WHERE type = 'agent'"; // ---- If Agent then filter only themselves ---- if ($loginType === 'agent') { $agentsSql .= " AND user_id = '".$loginId."'"; } $agentsResult = $conn->query($agentsSql); $agentCommissions = []; if ($agentsResult && $agentsResult->num_rows > 0) { while($agent = $agentsResult->fetch_assoc()) { $agentId = $agent['user_id']; // Initialize agent data with collection commission $collectionData = calculateCollectionCommission($agentId, $dateFrom, $dateTo); $agentCommissions[$agentId] = [ 'agent_name' => $agent['user_name'], 'opening_commission' => 0, 'collection_commission' => $collectionData['collection_commission'], 'total_collection' => $collectionData['total_collection'], 'account_count' => 0, 'comi_rate' => $collectionData['comi_rate'] ]; } } // ---- Now get accounts created in the date range and add opening commission ---- $accountsSql = "SELECT * FROM `".$GLOBALS['arif_ac']."` WHERE `AA_TIMESTAMP` BETWEEN '".$dateFrom." 00:00:00' AND '".$dateTo." 23:59:59'"; // ---- if Agent then filter only their accounts ---- if ($loginType === 'agent') { $accountsSql .= " AND `refferTo` = '".$loginId."'"; } $accountsResult = $conn->query($accountsSql); if ($accountsResult && $accountsResult->num_rows > 0) { while($account = $accountsResult->fetch_assoc()) { // Calculate OPENING commission $openingCommission = calculateOpeningCommission( $account['AA_ACTYPE'], $account['AA_TYPE'], $account['AA_AMOUNT'] ?? 0, $account['AA_INSTALLMENT'], $account['refferTo'], $account['AA_NO_OF_PAYMENT'] ); $commissionAgent = $account['refferTo']; // If agent exists in our list, add opening commission if (isset($agentCommissions[$commissionAgent])) { $agentCommissions[$commissionAgent]['opening_commission'] += $openingCommission; $agentCommissions[$commissionAgent]['account_count']++; } } } echo '

'; $grandTotalOpening = 0; $grandTotalCollection = 0; $grandTotalCommission = 0; $grandTotalCollectionAmount = 0; if (!empty($agentCommissions)) { foreach($agentCommissions as $agentId => $agentData) { $totalCommission = $agentData['opening_commission'] + $agentData['collection_commission']; $grandTotalOpening += $agentData['opening_commission']; $grandTotalCollection += $agentData['collection_commission']; $grandTotalCommission += $totalCommission; $grandTotalCollectionAmount += $agentData['total_collection']; echo " "; } } else { echo ""; } echo '

Agent ID	Agent Name	Accounts	Collection Amount	Collection Rate	Acc Opening Commission	Collection Commission	Total Commission
".$agentId."	".$agentData['agent_name']."	".$agentData['account_count']."	".number_format($agentData['total_collection'], 2)."	".$agentData['comi_rate']."%	".number_format($agentData['opening_commission'], 2)."	".number_format($agentData['collection_commission'], 2)."	".number_format($totalCommission, 2)."
No commission data found

'; // ---- Show grand totals ---- if ($loginType !== 'agent' && !empty($agentCommissions)) { echo '

Opening Commission

'.number_format($grandTotalOpening, 2).'

Collection Commission

'.number_format($grandTotalCollection, 2).'

Total Commission

'.number_format($grandTotalCommission, 2).'

'; } elseif ($loginType === 'agent' && !empty($agentCommissions)) { // Show individual agent summary $agentData = reset($agentCommissions); // Get first (and only) agent data $totalCommission = $agentData['opening_commission'] + $agentData['collection_commission']; echo '

My Summary

Total Accounts: '.$agentData['account_count'].'

Total Collection: '.number_format($agentData['total_collection'], 2).'

My Total Commission

'.number_format($totalCommission, 2).'

'; } echo '

'; $conn->close(); } ?>

Dual Commission Report