function vatreport_download_xls($fields, $month, $year, $startdate, $enddate) { global $CFG, $DB, $USER; require_once("$CFG->libdir/excellib.class.php"); //require_once($CFG->dirroot.'/user/profile/lib.php'); $hierarchy = new hierarchy(); $tax = tax::getInstance(); $filename = clean_filename(get_string('vatreport', 'local_onlinepayment')); $workbook = new MoodleExcelWorkbook('-'); $workbook->send($filename); $worksheet = array(); $worksheet[0] = $workbook->add_worksheet(''); $col = 0; foreach ($fields as $fieldname) { $worksheet[0]->write(0, $col, $fieldname); $col++; } $mysql = "SELECT item.id, item.itemtype, item.moduleid, item.online_courseid, item.item_amount AS amount, tra.orderid, tra.transactionid, tra.timecreated, user.id AS userid, CONCAT(user.firstname, ' ', user.lastname) AS studentname, user.country FROM {local_item} AS item JOIN {local_payment_transaction} AS tra ON tra.orderid = item.orderid JOIN {user} AS user ON user.id = tra.userid WHERE tra.status = 'Success' "; if ($month) { $mysql .= " AND FROM_UNIXTIME(tra.timecreated, '%m') = '{$month}' "; } if ($year) { $mysql .= " AND FROM_UNIXTIME(tra.timecreated, '%Y') = '{$year}' "; } if ($startdate) { $start = date('Y-m-d', $startdate); $mysql .= " AND FROM_UNIXTIME(tra.timecreated, '%Y-%m-%d') >= '{$start}' "; } if ($enddate) { $end = date('Y-m-d', $enddate); $mysql .= " AND FROM_UNIXTIME(tra.timecreated, '%Y-%m-%d') <= '{$end}' "; } $mysql .= " ORDER BY tra.timecreated DESC "; $reports = $DB->get_records_sql($mysql); //$data = array(); $ctrs = array(); foreach ($reports as $report) { $ctrs[$report->id] = $report->country; } $countries = get_string_manager()->get_list_of_countries(false); $euCountries = $tax->get_eu_countries(); $grandtotal = 0; /* ---Group the UK, EU Countries and non EU Countries--- */ //Group UK countries--------------- $i = 1; $sheetrow = 1; $subtotal = 0; $satisfy1 = false; foreach ($ctrs as $key => $ctr) { if ($ctr == 'GB') { $satisfy1 = true; $line = array(); $line[] = $reports[$key]->studentname; //Student Name $line[] = $reports[$key]->amount; $line[] = date('d M, Y', $reports[$key]->timecreated); if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) { $line[] = $DB->get_field('local_clclasses', 'fullname', array('id' => $reports[$key]->moduleid)); } else if ($reports[$key]->itemtype == 'mooctype' && $reports[$key]->online_courseid) { $line[] = $DB->get_field('course', 'fullname', array('id' => $reports[$key]->online_courseid)); } // $items = $DB->get_records('local_item', array('orderid'=>$reports[$key]->orderid)); // $modules = array(); // foreach($items as $item){ // if($item->itemtype=='classtype' && $item->moduleid){ // $onlinecourseid = $DB->get_field('local_clclasses', 'onlinecourseid', array('id'=>$item->moduleid));; // } // if($item->itemtype=='mooctype' && $item->online_courseid){ // $onlinecourseid = $item->online_courseid; // } // $course = $DB->get_record('course', array('id'=>$onlinecourseid)); // $modules[] = $course->fullname; // } // $line[] = implode(', ', $modules); $line[] = $countries[$reports[$key]->country]; //Student country $no_vat = false; if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) { $local_user = $DB->get_record('local_users', array('userid' => $reports[$key]->userid)); $user_class = $DB->get_record('local_user_clclasses', array('userid' => $reports[$key]->userid, 'classid' => $reports[$key]->moduleid)); $exist = $local_user && $user_class; if ($exist && $local_user->fundsbygovt && !$user_class->fundbyuk) { $no_vat = true; } } if ($no_vat) { $line[] = 'No VAT applied'; $line[] = '0'; $subtotal += '0'; } else if ($tax = $DB->get_record_select('local_tax_rate', "country = '" . $reports[$key]->country . "' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) { $line[] = $tax->rate . ' %'; $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount; $line[] = $vatamount; $subtotal += $vatamount; } else if ($tax = $DB->get_record_select('local_tax_rate', "country = 'all' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) { $line[] = $tax->rate . ' %'; $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount; $line[] = $vatamount; $subtotal += $vatamount; } else { $line[] = '0 %'; $line[] = '0'; $subtotal += '0'; } $col = 0; foreach ($fields as $k => $v) { $worksheet[0]->write($sheetrow, $col, $line[$k]); $col++; } $sheetrow++; $i++; unset($ctrs[$key]); } } if ($satisfy1) { $line = array('', '', '', '', '', 'Sub-Total', $subtotal); $col = 0; foreach ($fields as $k => $v) { $worksheet[0]->write($sheetrow, $col, $line[$k]); $col++; } $sheetrow++; $i++; } $grandtotal += $subtotal; //Group all EU countries--------------- $subtotal = 0; $satisfy2 = false; foreach ($ctrs as $key => $ctr) { if (in_array($ctr, array_keys($euCountries))) { $satisfy2 = true; $line = array(); $line[] = $reports[$key]->studentname; //Student Name $line[] = $reports[$key]->amount; $line[] = date('d M, Y', $reports[$key]->timecreated); if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) { $line[] = $DB->get_field('local_clclasses', 'fullname', array('id' => $reports[$key]->moduleid)); } else if ($reports[$key]->itemtype == 'mooctype' && $reports[$key]->online_courseid) { $line[] = $DB->get_field('course', 'fullname', array('id' => $reports[$key]->online_courseid)); } // $items = $DB->get_records('local_item', array('orderid'=>$reports[$key]->orderid)); // $modules = array(); // foreach($items as $item){ // if($item->itemtype=='classtype' && $item->moduleid){ // $onlinecourseid = $DB->get_field('local_clclasses', 'onlinecourseid', array('id'=>$item->moduleid));; // } // if($item->itemtype=='mooctype' && $item->online_courseid){ // $onlinecourseid = $item->online_courseid; // } // $course = $DB->get_record('course', array('id'=>$onlinecourseid)); // $modules[] = $course->fullname; // } // $line[] = implode(', ', $modules); $line[] = $countries[$reports[$key]->country]; //Student country $no_vat = false; if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) { $local_user = $DB->get_record('local_users', array('userid' => $reports[$key]->userid)); $user_class = $DB->get_record('local_user_clclasses', array('userid' => $reports[$key]->userid, 'classid' => $reports[$key]->moduleid)); $exist = $local_user && $user_class; if ($exist && $local_user->fundsbygovt && !$user_class->fundbyuk) { $no_vat = true; } } if ($no_vat) { $line[] = 'No VAT applied'; $line[] = '0'; $subtotal += '0'; } else if ($tax = $DB->get_record_select('local_tax_rate', "country = '" . $reports[$key]->country . "' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) { $line[] = $tax->rate . ' %'; $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount; $line[] = $vatamount; $subtotal += $vatamount; } else if ($tax = $DB->get_record_select('local_tax_rate', "country = 'all' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) { $line[] = $tax->rate . ' %'; $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount; $line[] = $vatamount; $subtotal += $vatamount; } else { $line[] = '0 %'; $line[] = '0'; $subtotal += '0'; } $col = 0; foreach ($fields as $k => $v) { $worksheet[0]->write($sheetrow, $col, $line[$k]); $col++; } $sheetrow++; $i++; unset($ctrs[$key]); } } if ($satisfy2) { $line = array('', '', '', '', '', 'Sub-Total', $subtotal); $col = 0; foreach ($fields as $k => $v) { $worksheet[0]->write($sheetrow, $col, $line[$k]); $col++; } $sheetrow++; $i++; } $grandtotal += $subtotal; //Group all Non-EU countries--------------- $subtotal = 0; $satisfy3 = false; foreach ($ctrs as $key => $ctr) { $satisfy3 = true; $line = array(); $line[] = $reports[$key]->studentname; //Student Name $line[] = $reports[$key]->amount; $line[] = date('d M, Y', $reports[$key]->timecreated); if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) { $line[] = $DB->get_field('local_clclasses', 'fullname', array('id' => $reports[$key]->moduleid)); } else if ($reports[$key]->itemtype == 'mooctype' && $reports[$key]->online_courseid) { $line[] = $DB->get_field('course', 'fullname', array('id' => $reports[$key]->online_courseid)); } //$items = $DB->get_records('local_item', array('orderid'=>$reports[$key]->orderid)); //$modules = array(); //foreach($items as $item){ // if($item->itemtype=='classtype' && $item->moduleid){ // $onlinecourseid = $DB->get_field('local_clclasses', 'onlinecourseid', array('id'=>$item->moduleid));; // } // if($item->itemtype=='mooctype' && $item->online_courseid){ // $onlinecourseid = $item->online_courseid; // } // $course = $DB->get_record('course', array('id'=>$onlinecourseid)); // $modules[] = $course->fullname; //} //$line[] = implode(', ', $modules); $line[] = $countries[$reports[$key]->country]; //Student country $no_vat = false; if ($reports[$key]->itemtype == 'classtype' && $reports[$key]->moduleid) { $local_user = $DB->get_record('local_users', array('userid' => $reports[$key]->userid)); $user_class = $DB->get_record('local_user_clclasses', array('userid' => $reports[$key]->userid, 'classid' => $reports[$key]->moduleid)); $exist = $local_user && $user_class; if ($exist && $local_user->fundsbygovt && !$user_class->fundbyuk) { $no_vat = true; } } if ($no_vat) { $line[] = 'No VAT applied'; $line[] = '0'; $subtotal += '0'; } else if ($tax = $DB->get_record_select('local_tax_rate', "country = '" . $reports[$key]->country . "' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) { $line[] = $tax->rate . ' %'; $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount; $line[] = $vatamount; $subtotal += $vatamount; } else if ($tax = $DB->get_record_select('local_tax_rate', "country = 'all' AND typeid = 1 AND '" . date('d-m-Y', $reports[$key]->timecreated) . "' BETWEEN FROM_UNIXTIME(startdate, '%d-%m-%Y') AND FROM_UNIXTIME(enddate, '%d-%m-%Y')")) { $line[] = $tax->rate . ' %'; $vatamount = ( $tax->rate / 100 ) * $reports[$key]->amount; $line[] = $vatamount; $subtotal += $vatamount; } else { $line[] = '0 %'; $line[] = '0'; $subtotal += '0'; } $col = 0; foreach ($fields as $k => $v) { $worksheet[0]->write($sheetrow, $col, $line[$k]); $col++; } $sheetrow++; $i++; unset($ctrs[$key]); } if ($satisfy3) { $line = array('', '', '', '', '', 'Sub-Total', $subtotal); $col = 0; foreach ($fields as $k => $v) { $worksheet[0]->write($sheetrow, $col, $line[$k]); $col++; } $sheetrow++; $i++; } $grandtotal += $subtotal; if ($satisfy1 || $satisfy2 || $satisfy3) { $line = array('', '', '', '', '', 'Grand-Total', $grandtotal); $col = 0; foreach ($fields as $k => $v) { $worksheet[0]->write($sheetrow, $col, $line[$k]); $col++; } $sheetrow++; $i++; } $workbook->close(); die; }
//} $PAGE->set_url('/local/onlinepayment/paymentstatus.php'); $PAGE->set_title(get_string('paymentdetails', 'local_onlinepayment')); //Header and the navigation bar $PAGE->set_heading(get_string('paymentdetails', 'local_onlinepayment')); $PAGE->requires->css('/local/onlinepayment/css/style.css'); //$PAGE->navbar->add(get_string('pluginname', 'local_onlinepayment'), new moodle_url('/local/onlinepayment/index.php')); $PAGE->navbar->add(get_string('paymentdetails', 'local_onlinepayment')); echo $OUTPUT->header(); //Heading of the page //echo $OUTPUT->heading(get_string('paymentdetails', 'local_onlinepayment')); $trans = onlinepay_transaction::getInstance(); $tax = tax::getInstance(); $schoollist = $hierarchy->get_assignedschools(); if (is_siteadmin()) { $schoollist = $hierarchy->get_school_items(); } $schoollist = $hierarchy->get_school_parent($schoollist, $selected = array(), $inctop = false, $all = false); //$filter = new status_filter_form(); //$filter->display(); //$records = $tax->get_payment_status($schoollist, $fullname); //print_object($records); $data = array(); if ($orderid) { echo $OUTPUT->heading(get_string('paymentdetails', 'local_onlinepayment')); $record = $tax->get_payment_status($schoollist, $orderid); $semester = $DB->get_record('local_semester', array('id' => $record->semesterid));
function definition() { global $USER, $CFG, $DB, $PAGE; $hierarchy = new hierarchy(); $tax = tax::getInstance(); $mform = $this->_form; $months = $tax->get_months(); $years = $tax->get_years(); $mform->addElement('header', 'settingsheader', get_string('filters', 'local_onlinepayment')); $montharray = array(); $montharray[] = & $mform->createElement('select', 'month', '', $months); $montharray[] = & $mform->createElement('select', 'year', '', $years); $montharray[] = & $mform->createElement('checkbox', 'checkforenable', '', 'Enable'); $mform->addGroup($montharray, 'montharray', get_string('selectmonth', 'local_onlinepayment'), array(' '), false); $mform->disabledIf('month', 'checkforenable'); $mform->disabledIf('year', 'checkforenable'); $mform->addElement('static', 'or', '', '(OR)'); $mform->addElement('date_selector', 'startdate', get_string('startdate', 'local_academiccalendar'), array('optional' => true)); $mform->addElement('date_selector', 'enddate', get_string('enddate', 'local_academiccalendar'), array('optional' => true)); $mform->addElement('hidden', 'page'); $mform->setType('page', PARAM_INT); $mform->addElement('hidden', 'perpage'); $mform->setType('perpage', PARAM_RAW); $mform->addElement('submit', 'submitbutton', 'Search'); }