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');
}
