public function actionIntegrityCheck() { $unassigned = CheckIncome::model()->findAllBySql("select check_income.*\nfrom check_income\nleft join (select sum(income.amount) as total, \n income.check_id as check_id\n from income\n group by income.check_id) as assigned\non assigned.check_id = check_income.id\nwhere coalesce(assigned.total,0) != coalesce(check_income.amount,0)\nand (check_income.returned is null or check_income.returned < '2000-01-01')\nand check_income.session_id = :sid\n", array('sid' => ClassSession::savedSessionId())); $instructorbalance = ClassInfo::model()->findAllBySql("select class_info.*\nfrom class_info\nleft join (select sum(instructor_assignment.percentage) as total, \n instructor_assignment.class_id as class_id\n from instructor_assignment\n group by instructor_assignment.class_id) as assigned\non assigned.class_id = class_info.id\nwhere coalesce(assigned.total,0) != 100\nand class_info.session_id = :sid\n", array('sid' => ClassSession::savedSessionId())); $this->render('integrity_check', array('unassigned' => $unassigned, 'instructorbalance' => $instructorbalance)); }
public static function underAssignedChecks($session = null, $company = null) { if (!isset($session)) { $session = ClassSession::savedSessionId(); } $q = "select check_income.*\n from check_income\n left join\n (select income.check_id as check_id,\n sum(income.amount) as assigned\n from income \n group by income.check_id) as assignments\n on (assignments.check_id = check_income.id)\n where assignments.assigned < check_income.amount\n and check_income.session_id = :session"; if (isset($company)) { ///XXX hACK!, just use querybuilder will ya? $q .= " and check_income.payee_id = {$company}"; } return CheckIncome::model()->findAllBySql($q, array('session' => $session)); }
public function actionCheckNumAC() { if (isset($_GET['term'])) { $c = CheckIncome::model()->findAllBySQL("select check_income.* from check_income where\n(check_num like :text \nor payer like :text)\nand session_id = :sid\nand cash < 1\nand (deposit_id is null or deposit_id < 1)\nand (delivered is null or delivered < '2000-01-01')\nand (returned is null or returned < '2000-01-01')\norder by abs(check_num)\n", array('text' => '%' . $_GET['term'] . '%', 'sid' => ClassSession::savedSessionId())); echo CJSON::encode(array_map(function ($r) { return array('other' => $r->amount, 'label' => $r->summary, 'value' => $r->id); }, $c)); Yii::app()->end(); } //TODO error out, 404? }
public function populate($type = 'check') { $cash_comparison = $type == 'check' ? "check_income.cash < 1" : " check_income.cash > 0"; /* XXX TODO, the rest of the criteria here! - enrolled count > minimum - income count > 1 */ $r = CheckIncome::model()->findAllBySql("\nselect check_income.*\nfrom check_income\nleft join (select sum(income.amount) as total,\n income.check_id as check_id\n from income\n group by income.check_id\n ) as income_summary\non income_summary.check_id = check_income.id\nleft join (select income.check_id as check_id,\n count(signup.class_id) as unenrolled_count\n from income\n left join signup\n on signup.class_id = income.class_id\n and signup.student_id = income.student_id\n left join (\n select count(signup.student_id) as total_enrolled,\n class_info.min_students as min,\n class_info.status as status,\n signup.class_id as class_id \n from class_info\n left join signup\n on class_info.id = signup.class_id\n where signup.status = 'Enrolled'\n group by class_id\n ) as class_summary\n on class_summary.class_id = signup.class_id\n where signup.status != 'Enrolled'\n or class_summary.total_enrolled < class_summary.min\n or class_summary.status = 'Cancelled'\n group by income.check_id\n ) as signup_status\non signup_status.check_id = check_income.id\nwhere income_summary.total = check_income.amount\n and signup_status.unenrolled_count is null\n and check_income.payee_id = :osspto\n and (check_income.deposit_id is null or check_income.deposit_id < 1)\n and ( {$cash_comparison} )\n and check_income.session_id = :sid\n and (check_income.returned is null or check_income.returned < '2000-01-01')\n", array('osspto' => Company::OSSPTO_COMPANY, 'sid' => ClassSession::savedSessionId())); foreach ($r as $i) { $i->deposit_id = $this->id; $i->save(); } }