[eluser]akien[/eluser]
Hi,
I have a code where I will convert the result of my query that I get into a CSV file. My code is already displaying the result, now my problem is I can't export it into CSV file. I make a report_model_export but instead of displaying a download prompt it display the main page.
This is my code for the extraction:
Code: <?php
class report_model_export extends Model
{
function report_model_export()
{
parent :: Model();
}
function getcsv()
{
$this->load->helper('file');
$this->load->dbutil();
$delimiter = ",";
$newline = "\r\n";
$query = $this->db->query("SELECT restaurant_master.code, restaurant_master.name,
SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID
WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59'
GROUP BY restaurant_master.code, restaurant_master.name");
$csv = $this->dbutil->csv_from_result($query,$delimiter,$newline);
header('Content-Type: "application/octet-stream"');
header('Content-Disposition: attachment; filename="sales_frequency_bracket.csv"');
header('Pragma: no-cache');
readfile("/home/mdscsi/Desktop/sales_frequency_bracket.csv");
}
}
?>
This is my code for the controller:
Code: <?php
class Report extends Controller
{
function report()
{
parent :: Controller();
}
public function GetAll()
{
$this->load->model('report_model');
$data['query'] = $this->report_model->restaurant_master_getall();
$this->load->view('report_view', $data);
}
public function Get()
{
$this->load->model('report_model_export');
$data['query'] = $this->report_model_export->getcsv();
}
}
?>
This is my code for the model:
Code: <?php
class Report_model extends Model
{
function report_model()
{
parent :: Model();
}
function restaurant_master_getall()
{
$this->load->database();
$date_from = $this->input->post('dfrom');
$date_to = $this->input->post('dto');
$query = $this->db->query("SELECT restaurant_master.code, restaurant_master.name,
SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID
WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59'
GROUP BY restaurant_master.code, restaurant_master.name");
return $query;
}
}
?>
Thanks.
[eluser]akien[/eluser]
I already did this in the simple php file and I make it like this.
Code: <?php
$host = '172.16.8.32';
$user = 'mds_reports';
$pass = 'password';
$db = 'mds_reports';
$file = 'report';
$dfrom = $_POST['try_dfrom'];
$dto = $_POST['try_dto'];
$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");
$sql = "SELECT restaurant_master.code, restaurant_master.name,
SUM(CASE WHEN mds_orders.GrossTotal < '100' THEN mds_orders.GrossTotal END) AS 'less100',
SUM(CASE WHEN mds_orders.GrossTotal >= '100' AND mds_orders.GrossTotal < '200' THEN mds_orders.GrossTotal END) AS 'less200',
SUM(CASE WHEN mds_orders.GrossTotal >= '200' AND mds_orders.GrossTotal < '300' THEN mds_orders.GrossTotal END) AS 'less300',
SUM(CASE WHEN mds_orders.GrossTotal >= '300' AND mds_orders.GrossTotal < '400' THEN mds_orders.GrossTotal END) AS 'less400',
SUM(CASE WHEN mds_orders.GrossTotal >= '400' AND mds_orders.GrossTotal < '500' THEN mds_orders.GrossTotal END) AS 'less500',
SUM(CASE WHEN mds_orders.GrossTotal >= '500' AND mds_orders.GrossTotal < '600' THEN mds_orders.GrossTotal END) AS 'less600',
SUM(CASE WHEN mds_orders.GrossTotal >= '600' AND mds_orders.GrossTotal < '700' THEN mds_orders.GrossTotal END) AS 'less700',
SUM(CASE WHEN mds_orders.GrossTotal >= '700' AND mds_orders.GrossTotal < '800' THEN mds_orders.GrossTotal END) AS 'less800',
SUM(CASE WHEN mds_orders.GrossTotal >= '800' AND mds_orders.GrossTotal < '900' THEN mds_orders.GrossTotal END) AS 'less900',
SUM(CASE WHEN mds_orders.GrossTotal >= '900' AND mds_orders.GrossTotal < '1000' THEN mds_orders.GrossTotal END) AS 'less1k',
SUM(CASE WHEN mds_orders.GrossTotal >= '1000' THEN mds_orders.GrossTotal END) AS 'gtr1k',
SUM(mds_orders.GrossTotal) AS 'total'
FROM mds_orders
JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.PKID
WHERE mds_orders.OrderDate BETWEEN '$dfrom' AND '$dto'
GROUP BY restaurant_master.code, restaurant_master.name";
$result = mysql_query($sql);
$i = 0;
if (mysql_num_rows($result) > 0) {
echo 'Sales Per Sales Frequency Bracket'.","."\n"."\n";
echo "Date select from '$dfrom' to '$dto'".","."\n"."\n";
echo 'Restaurant Code'.",";
echo 'Restaurant Name'.",";
echo '100 & Below'.",";
echo '101-200'.",";
echo '201-300'.",";
echo '301-400'.",";
echo '401-500'.",";
echo '501-600'.",";
echo '601-700'.",";
echo '701-800'.",";
echo '801-900'.",";
echo '901-1000'.",";
echo '1001 & Above'.",";
echo 'Total Gross Sales'.","."\n";
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['code'].",".
$row['name'].",".
$row['less100'].",".
$row['less200'].",".
$row['less300'].",".
$row['less400'].",".
$row['less500'].",".
$row['less600'].",".
$row['less700'].",".
$row['less800'].",".
$row['less900'].",".
$row['less1k'].",".
$row['gtr1k'].",".
$row['total'].","."\n";
$i++;
}
}
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>
Now I'm confused how I can convert it to Code Igniter or into MVC pattern.
Thanks in advance. Best Regards.
|
