[eluser]Nica[/eluser]
Hi,
Just want to ask how can I extract the files as csv in a code igniter way..?
[eluser]CroNiX[/eluser]
That's too broad of a question to really answer. It depends on what the "file" is. Do you mean you have a csv file that you want CI to load and parse?
[eluser]Nica[/eluser]
This is my sample of php query for extracting the file, the problem is how can I convert it Code Igniter way..? I'm using MVC, should I do it to the Controller..?
Code: <?php
$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." 00:00:00' AND '".$dto." 23:59:59'
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;
?>
[eluser]Nica[/eluser]
I already convert my code to Code Igniter.
And this is my Controller
Code: <?php
class Sales extends Controller {
function sales()
{
parent::Controller();
}
function get_all()
{
$this->load->model('sales_model');
$data['query'] = $this->sales_model->restaurant_master_getall();
$this->load->view('sales_viewall', $data);
}
function Get() {
$this->load->dbutil();
$dfrom = $this->input->post('try_dfrom');
$dto = $this->input->post('try_dto');
$file = 'report';
$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 '".$dfrom." 00:00:00' AND '".$dto." 23:59:59'
GROUP BY restaurant_master.code, restaurant_master.name");
if($query->num_rows() > 0)
foreach($query->result() as $row) {
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->fetch_assoc($query)) {
$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;
$i++;
}
}
echo $this->dbutil->csv_from_result($query);
}
}
?>
But when I try to extract the file this is displayed in a browser
Code: Sales Per Sales Frequency Bracket, Date select from '2011-12-01' to '2011-12-02', Restaurant Code,Restaurant Name,100 & Below,101-200,201-300,301-400,401-500,501-600,601-700,701-800,801-900,901-1000,1001 & Above,Total Gross Sales,
Actually it should not be displayed in a browser in should be save as csv file.
[eluser]LifeSteala[/eluser]
Hello,
You still need the following code to tell the browser to send what is being echoed as a csv file. Add this to your view, and pass the result from csv_from_result() to the view.
Controller:
Code: // echo $this->dbutil->csv_from_result($query); # Change to
$data['csv_result'] = $this->dbutil->csv_from_result($query);
$this->load->view('export_csv', $data);
View:
Code: <?php
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
echo $csv_result;
?>
That should work for you.
[eluser]CroNiX[/eluser]
There is also the download helper to force a download.
[eluser]Nica[/eluser]
@LifeStealaa and @ CroNiX thank you for your response, it works! 
Just one more thing again.,I need to link my function.
Example:
In home page there's a four report and when I click any of them it will go to another page for their function. Also there's a back page which is for the home page.
How can I do this..?
Thanks again and have a great day ahead! 
|
