public function actionDonations($id) { $visit_sql = "SELECT d.user_id,d.visit_id,s.first_name,s.last_name,v.visit_code, CASE WHEN v.status = 1 THEN 'Yes' ELSE 'No' END AS visit_active, v.start_date,v.end_date, SUM( d.amount ) AS amount FROM `user_donation` d LEFT JOIN visits v ON d.visit_id = v.id LEFT JOIN solicitor s ON d.solicitor_id = s.id WHERE s.id = '{$id}' GROUP BY d.visit_id ORDER BY d.date_entered DESC"; $visits = BaseModel::executeSimpleQuery($visit_sql); $c_sql = "SELECT SUM(amount) AS amount FROM solicitor_credit WHERE solicitor_id = '{$id}'"; $c_result = BaseModel::executeSimpleQueryFirstRow($c_sql); if ($c_result === null) { $amount = 0; } else { $amount = $c_result['amount']; } $d_sql = "SELECT SUM(amount) AS amount FROM user_donation WHERE solicitor_id = '{$id}'"; $d_result = BaseModel::executeSimpleQueryFirstRow($d_sql); if ($d_result === null) { $damount = 0; } else { $damount = $d_result['amount']; } $solicitor = Solicitor::model()->findByPk($id); $donation = new Donation('users'); $donation->unsetAttributes(); $payments = new SolicitorCredit('solicitor'); $payments->unsetAttributes(); $this->render('donations', array('visits' => $visits, 'donation' => $donation, 'solicitor' => $solicitor, 'amount' => $amount, 'damount' => $damount, 'payments' => $payments)); }
protected function gridDataColumn($data, $row) { $sql = "SELECT id, COALESCE(SUM(credit),0) - COALESCE(SUM(debit),0) AS balance from user_trans where user_id = '" . $data->id . "'"; $result = BaseModel::executeSimpleQueryFirstRow($sql); if(!empty($result['balance'])) { return "$".$result['balance']; } else { return 0; } }
public function getUserBalance($user_id) { $sql = "SELECT id, COALESCE(SUM(credit),0) - COALESCE(SUM(debit),0) AS balance from user_trans where user_id = '" .$user_id. "'"; $result = BaseModel::executeSimpleQueryFirstRow($sql); if(!empty($result['balance'])) { return $result['balance']; } else { return 0; } }