KoolReport's Forum
Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
How to implement the "Download Excel" for the multiple queries in filename.view.php file #2123
Dear Sebastian Morales, As per your instruction i would like to change my code in order to pass the data into datastore rather than data datasource. So,could please help me in the following conditions.
1.How to store the below query into a String variable like in php script .
2.Using above String variable now how should i print the variable data.
3.Now how should i declare above String variable as a global variable so that i can pass it or call it in any report.
...
Please move the query to setup() and create dataStore in setup() function The dataStore created in setup() then can be use for both exporting to excel and in your html view.
The view should not contain SQL and logic, although sometime is inevitable but keep it as low as possible. In your case, I see that you can move all to setup().
Dear Sebastian Morales, As per your instruction I have changed my code to datastore but a blank page is getting displayed.
So, Cloud you help me please on how to correct the code please.
ppotreport.php
protected function setup()
{
$branchid=$_SESSION['branch'];
$this->src("". $_SESSION['c_db'] ."")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND (DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') >= STR_TO_DATE(':startDatePicker', '%d-%m-%Y') AND DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') <= STR_TO_DATE(':endDatePicker', '%d-%m-%Y')) AND YEAR(ce.custRegisteredDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custRegisteredDate) <= MONTH(current_date - INTERVAL 1 MONTH)
GROUP BY ce.custRegistrationId
ORDER BY ce.custRegisteredDate DESC")
->params(array(
":startDatePicker"=>$this->params["startDatePicker"],
":endDatePicker"=>$this->params["endDatePicker"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result"));
$this->src("cloudkli_demo")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND (DATE_FORMAT(fc.nextCallingDate, '%Y-%m-%d') >= STR_TO_DATE(':startDatePicker', '%d-%m-%Y') AND DATE_FORMAT(fc.nextCallingDate, '%Y-%m-%d') <= STR_TO_DATE(':endDatePicker', '%d-%m-%Y'))
AND YEAR(fc.nextCallingDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(fc.nextCallingDate) <= MONTH(current_date - INTERVAL 1 MONTH)
ORDER BY fc.nextCallingDate DESC
")
->params(array(
":startDatePicker"=>$this->params["startDatePicker"],
":endDatePicker"=>$this->params["endDatePicker"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result1"));
$this->src("cloudkli_demo")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND emp.employeeFirstName=':employeeFirstName' AND
(DATE_FORMAT(ce.custConsultedDate,'%Y-%m-%d') >= STR_TO_DATE(':startDatePicker', '%d-%m-%Y') AND
DATE_FORMAT(ce.custConsultedDate,'%Y-%m-%d') <= STR_TO_DATE(':endDatePicker', '%d-%m-%Y')) AND YEAR(ce.custConsultedDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custConsultedDate) <= MONTH(current_date - INTERVAL 1 MONTH)
ORDER BY ce.custConsultedDate DESC")
->params(array(
":startDatePicker"=>$this->params["startDatePicker"],
":endDatePicker"=>$this->params["endDatePicker"],
":employeeFirstName"=>$this->params["employeeFirstName"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result2"));
$this->src("cloudkli_demo")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND ce.custRegistrationId=':RegistrationID'")
->params(array(
":RegistrationID"=>$this->params["RegistrationID"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result3"));
Dear Team, here is the excel index and view code but I have not yet achieved the error free code of above query writing into business logic file from view file.
ppotreportExcel.php
<?php
include "ppotreport.php";
$report = new ppotreport;
$report->run();
$report->exportToExcel('ppotreportExcel')->toBrowser("ppotreport.xls");
ppotreportExcel.view.php
...
Pls replace your "ppotreportExcel.php" content with this and let us know the result when you click export to excel:
<?php
include "ppotreport.php";
$report = new ppotreport;
$report->run();
print_r($_POST); echo "<br>";
print_r($report->dataStore("result")->data());
Respected Sebastian Morales, Initially I have posted the code of my view file and I have implemented the my whole business logic code in the view file.
And then you have suggested me to implement the logical code into the business logic file from view file and then I have done it and provided you above. But I am unable to implement the code form view file to business logic file.
And I have stated this in the above but you have asked me to send the files of ppotreportExcel.php and its view file.
But please provide me a solution on how to implement the written query in view file to business logic file.
And as per you suggestion I have implemented the above code in to business logic file as below. But a plan page had got displayed.
pportreport.php
protected function setup()
{
$branchid=$_SESSION['branch'];
$this->src("". $_SESSION['c_db'] ."")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND (DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') >= STR_TO_DATE(':startDatePicker', '%d-%m-%Y') AND DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') <= STR_TO_DATE(':endDatePicker', '%d-%m-%Y')) AND YEAR(ce.custRegisteredDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custRegisteredDate) <= MONTH(current_date - INTERVAL 1 MONTH)
GROUP BY ce.custRegistrationId
ORDER BY ce.custRegisteredDate DESC")
->params(array(
":startDatePicker"=>$this->params["startDatePicker"],
":endDatePicker"=>$this->params["endDatePicker"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result"));
$this->src("cloudkli_demo")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND (DATE_FORMAT(fc.nextCallingDate, '%Y-%m-%d') >= STR_TO_DATE(':startDatePicker', '%d-%m-%Y') AND DATE_FORMAT(fc.nextCallingDate, '%Y-%m-%d') <= STR_TO_DATE(':endDatePicker', '%d-%m-%Y'))
AND YEAR(fc.nextCallingDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(fc.nextCallingDate) <= MONTH(current_date - INTERVAL 1 MONTH)
ORDER BY fc.nextCallingDate DESC
")
->params(array(
":startDatePicker"=>$this->params["startDatePicker"],
":endDatePicker"=>$this->params["endDatePicker"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result1"));
$this->src("cloudkli_demo")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND emp.employeeFirstName=':employeeFirstName' AND
(DATE_FORMAT(ce.custConsultedDate,'%Y-%m-%d') >= STR_TO_DATE(':startDatePicker', '%d-%m-%Y') AND
DATE_FORMAT(ce.custConsultedDate,'%Y-%m-%d') <= STR_TO_DATE(':endDatePicker', '%d-%m-%Y')) AND YEAR(ce.custConsultedDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custConsultedDate) <= MONTH(current_date - INTERVAL 1 MONTH)
ORDER BY ce.custConsultedDate DESC")
->params(array(
":startDatePicker"=>$this->params["startDatePicker"],
":endDatePicker"=>$this->params["endDatePicker"],
":employeeFirstName"=>$this->params["employeeFirstName"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result2"));
$this->src("cloudkli_demo")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND ce.custRegistrationId=':RegistrationID'")
->params(array(
":RegistrationID"=>$this->params["RegistrationID"],
":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"]
))
->pipe($this->dataStore("result3"));
Follow my guide:
1 . In your report's setup function, use sql query tot pipe data to datastores.
2 . Replace your "ppotreportExcel.php" content with this:
<?php
include "ppotreport.php";
$report = new ppotreport;
$report->run();
print_r($_POST); echo "<br>";
print_r($report->dataStore("result")->data());
3 . Click export to excel and let us know what's printed out.
Respected Sebastian Morales, As for your instructions I have used my old code file that is I have implemented my query/business logic code in view file it self.
And I have replaced the code of file ppotreportExcel.php.
And when I have clicked on download excel the output is below.
It looks good. Pls use your old export command and set this in your excel view file:
<?php
use \koolreport\excel\Table;
?>
<div sheet-name="sheet1">
<div>
<?php
Table::create(array(
"dataSource" => $this->dataStore('result'),
));
?>
</div>
</div>
Then click export and let us know the result or error message (with screenshot) if there is.
Dear Sebastian Morale, First of I am very much thankful to you for supporting me but when I have clicked on export excel button the sheet is getting downloaded but with not data(blank sheet is getting downloaded).
And I have used the datastore code to the query in my "ppotreport.view.php" file. As shown below.
ppotreport.view.php
...
A report's web view has no effect on its excel export so don't post your report's web view again.
Replace your excel view file content with this:
<?php
use \koolreport\excel\Table;
?>
<div sheet-name="sheet1">
<div>
Hello world
</div>
</div>
Then click export to excel and let us know the result with screenshot.
Respected Sebastian Morales, I have changed the code of excel view file with above code and I am sorry.
And The result is below.
But just listen to me once please
I have added the datastore code to query in my view file "ppotreport.view.php" and passed the value to the view file of the excel before but not data was present in the downloaded file. and the code is below.
ppotreport.view.php
<script>
<?php
if(isset($_POST['submit'])){
$startDatePicker=$_POST['startDatePicker'];
$endDatePicker=$_POST['endDatePicker'];
$startDatePicker1=$_POST['startDatePicker1'];
$endDatePicker1=$_POST['endDatePicker1'];
$selected_val = $_POST['radioList']; // Storing Selected Value In Variable
$startDatePickernew=date("d-m-Y", strtotime($startDatePicker) );
$endDatePickernew=date("d-m-Y", strtotime($endDatePicker) );
$startDatePickernew1=date("d-m-Y", strtotime($startDatePicker1) );
$endDatePickernew1=date("d-m-Y", strtotime($endDatePicker1) );
$employeeFirstName=$_POST['employeeFirstName'];
$branchid=$_SESSION['branch'];
$RegistrationID=$_POST['RegistrationID'];
?>
//<script>
//alert('ddd');
//</script>
<?php
//echo "You have selected :".$selected_val; // Displaying Selected Value
$countno = 1;
if($selected_val=="RegistrationDate")
{
/*echo "SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND (DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') >= STR_TO_DATE('$startDatePickernew', '%d-%m-%Y') AND DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') <= STR_TO_DATE('$endDatePickernew', '%d-%m-%Y')) AND YEAR(ce.custRegisteredDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custRegisteredDate) <= MONTH(current_date - INTERVAL 1 MONTH)
GROUP BY ce.custRegistrationId
ORDER BY ce.custRegisteredDate DESC";
*/
echo
DataTables::create([
"dataSource"=>$this->src("DB Name")->query("SELECT ce.custEnquiryId,ce.custName,ce.custRegistrationId,
DATE_FORMAT(ce.custAppointmentDate, '%d-%m-%Y')As custAppointmentDateIND,DATE_FORMAT(ce.custConsultedDate, '%d-%m-%Y')As custConsultedDateIND,
fc.addComments,emp.employeeFirstName AS Doctor,emp1.employeeFirstName AS calledby FROM tblcustomerenquiries ce
LEFT JOIN tblfeedbackcomments fc ON ce.custEnquiryId = fc.enquiryId
LEFT JOIN tblemployeedetails emp ON ce.custTreatedDoctor = emp.employeeId
LEFT JOIN tblemployeedetails emp1 ON fc.createdBy = emp1.employeeId
WHERE ce.dueAmount > 0 AND ce.custBranchId='$branchid' AND (DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') >= STR_TO_DATE('$startDatePickernew', '%d-%m-%Y') AND DATE_FORMAT(ce.custRegisteredDate, '%Y-%m-%d') <= STR_TO_DATE('$endDatePickernew', '%d-%m-%Y')) AND YEAR(ce.custRegisteredDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custRegisteredDate) <= MONTH(current_date - INTERVAL 1 MONTH)
GROUP BY ce.custRegistrationId
ORDER BY ce.custRegisteredDate DESC"),
"cssClass"=>array(
"table"=>"table table-bordered"
),
"columns"=>[
/*"#"=>array(
"label"=>"S.no",
"start"=>1,
),*/
"custName"=>array(
"label"=>"Patient Name",
"formatValue"=>function($value,$row){
$custEnq = $row['custEnquiryId'];
$encryptId1=encrypt_url($custEnq);
if($row)
{
return"<a href='prospectiveForm.php?id=$encryptId1' style='color:blue;' target='_blanck'>$value</a>";
}
return $value;
}
),
/*"custPlace"=>array(
"label"=>"City",
"formatValue"=>function($City)
{
if($City=='0')
{
return '';
}
return$City;
}
),*/
/*"custServiceFor"=>array(
"label"=>"Treatment",
),*/
"custRegistrationId"=>array(
"label"=>"Reg. ID",
),
/*"custRegisteredDateIND"=>array(
"label"=>"Registration Date",
"formatValue"=>function($custRegisteredDateIND)
{
if($custRegisteredDateIND=='0')
{
return '';
}
elseif($custRegisteredDateIND=='01-01-1970')
{
return '';
}
elseif($custRegisteredDateIND=='01-01-1970')
{
return '';
}
return $custRegisteredDateIND;
}
),*/
"custAppointmentDateIND"=>array(
"label"=>"Appt. Date",
"formatValue"=>function($custAppointmentDateIND)
{
if($custAppointmentDateIND=='0')
{
return '';
}
elseif($custAppointmentDateIND=='01-01-1970')
{
return '';
}
elseif($custAppointmentDateIND=='01-01-1970')
{
return '';
}
return $custAppointmentDateIND;
}
),
/*"custStatusDateIND"=>array(
"label"=>"Next Calling Date",
"formatValue"=>function($custStatusDateIND)
{
if($custStatusDateIND=='0')
{
return "";
}
if($custStatusDateIND=='01-01-1970')
{
return "";
}
if($custStatusDateIND=='00-00-0000')
{
return "";
}
return $custStatusDateIND;
}
),*/
"custConsultedDateIND"=>array(
"label"=>"Consult Date",
"formatValue"=>function($custConsultedDateIND)
{
if($custConsultedDateIND=='0')
{
return "";
}
return $custConsultedDateIND;
}
),
"addComments"=>array(
"label"=>"Comments",
"formatValue"=>function($addComments)
{
if($addComments=='0')
{
return "";
}
return $addComments;
}
),
"Doctor"=>array(
"label"=>"Doctor"
),
"calledby"=>array(
"label"=>"Called by",
"formatValue"=>function($calledby)
{
if($calledby=='0')
{
return "";
}
return $calledby;
}
),
/*"custRegistrationId"=>array(
"label"=>"Reg. ID"
),*/
/*"custRemarks"=>array(
"label"=>"Remarks",
"formatValue"=>function($custRemarks)
{
if($custRemarks=='0')
{
return "";
}
return $custRemarks;
}
),
"replyName"=>array(
"label"=>"Reply",
"formatValue"=>function($replyName)
{
if($replyName=='0')
{
return "";
}
return $replyName;
}
),
"statusName"=>array(
"label"=>"Status",
"formatValue"=>function($statusName)
{
if($statusName=='0')
{
return "";
}
return $statusName;
}
),*/
],
"options"=>array(
"searching"=>true,
"paging" => true,
),
"searchOnEnter" => true,
"searchMode" => "or",
"pageLength" => 20,
]);
$metadata = $this->dataStore('result1')->meta();
}
Great result! Now replace the excel view content with this:
<?php
use \koolreport\excel\Table;
?>
<div sheet-name="sheet1">
<div>
Hello world
</div>
<div>
<?php
Table::create(array(
"name" => "table1",
"dataSource" => $this->dataStore("result"),
));
?>
</div>
</div>
and this:
<?php
use \koolreport\excel\Table;
?>
<div sheet-name="sheet1">
<div>
Hello world
</div>
<div>
<?php
Table::create(array(
"name" => "table1",
"dataSource" => "result",
));
?>
</div>
</div>
Respected Sebastian Morales, I am maintaining the queries in business logic file and view file and when I am selecting the radio button and clicking on submit button I am getting the output of selected radio button value.
Coming to downloaded excel sheet I am getting the data of business logic file query output but I need to download the data of view file query output.
And the query present in the business logic file is not used and we don't want that query output.
Note:- I have implemented my full length code logic in view file but not implemented in business logic file.
output of above code
Don't use Table's datasource with query and params in your web view. Move the queries and params from the web view to your report's setup and pipe to datastores. Then in your web view use the datastores for your Tables.
Once you get data right for your web view the excel export will be correct as well.
Respected Sebastian Morales, In morning it self I have moved the whole code from view file to business logic file but it was an bad idea for me because.
I have implemented 3 radio buttons registration, next calling date, select doctor and registration id.
And when I am selecting the particular radio button the respective query will get executed and display's the data.
So, for that requirement I have implemented the logical code in PHP and in view file.
As I have tried to implement the code in business logic file from view it was not a possible thing for me to do.
If it is possible please help me with a particle example. And again I am providing my view file code below.
ppotreport.view.php
<?php
if(isset($_POST['submit'])){
$startDatePicker=$_POST['startDatePicker'];
$endDatePicker=$_POST['endDatePicker'];
$selected_val = $_POST['radioList']; // Storing Selected Value In Variable
$startDatePickernew=date("d-m-Y", strtotime($startDatePicker) );
$endDatePickernew=date("d-m-Y", strtotime($endDatePicker) );
$employeeFirstName=$_POST['employeeFirstName'];
$branchid=$_SESSION['branch'];
$RegistrationID=$_POST['RegistrationID'];
?>
<?php
$countno = 1;
if($selected_val=="RegistrationDate")
{
echo
DataTables::create([
"dataSource"=>$this->src("DB Name")->query("SELECT CN,CN, FROM TN ce
LEFT JOIN tblfeedbackcomments fc ON CN= CN
LEFT JOIN tblemployeedetails emp ON CN= CN
LEFT JOIN tblemployeedetails emp1 ON CN= CN
WHERE CN> 0 AND CN='$branchid' AND (DATE_FORMAT(CN, '%Y-%m-%d') >= STR_TO_DATE('$startDatePickernew', '%d-%m-%Y') AND DATE_FORMAT(CN, '%Y-%m-%d') <= STR_TO_DATE('$endDatePickernew', '%d-%m-%Y')) AND YEAR(ce.custRegisteredDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custRegisteredDate) <= MONTH(current_date - INTERVAL 1 MONTH)
GROUP BY CN
ORDER BY CN DESC"),
...
]);
$data = $this->dataStore('result1')->data();
}
elseif($selected_val=="NextCallingDate"){
echo
DataTables::create(array(
"dataSource"=>$this->src("DB Name")->query("SELECT CN,CN,CN FROM TN ce
LEFT JOIN TN fc ON CN= CN
LEFT JOIN TN emp ON CN= CN
LEFT JOIN TN emp1 ON CN= CN
WHERE CN > 0 AND CN='$branchid' AND (DATE_FORMAT(CN, '%Y-%m-%d') >= STR_TO_DATE('$startDatePickernew', '%d-%m-%Y') AND DATE_FORMAT(CN, '%Y-%m-%d') <= STR_TO_DATE('$endDatePickernew', '%d-%m-%Y'))
AND YEAR(CN) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(CN) <= MONTH(current_date - INTERVAL 1 MONTH)
ORDER BY CN DESC
"),
...
));
}
elseif($selected_val=="SelectDoctor"){
echo
DataTables::create(array(
"dataSource"=>$this->src("DB Name")->query("SELECT CN FROM tblcustomerenquiries ce
LEFT JOIN TN fc ON CN
LEFT JOIN TN emp ON CN
LEFT JOIN TN emp1 ON CN
WHERE CN > 0 AND CN ='$branchid' AND CN ='value' AND
(DATE_FORMAT(CN,'%Y-%m-%d') >= STR_TO_DATE('$startDatePickernew', '%d-%m-%Y') AND
DATE_FORMAT(CN,'%Y-%m-%d') <= STR_TO_DATE('$endDatePickernew', '%d-%m-%Y')) AND YEAR(CN) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(CN) <= MONTH(current_date - INTERVAL 1 MONTH)
ORDER BY CN DESC"),
...
));
}
else{
?>
<script>
alert('Please Select Above Option');
</script>
<?php
}
}
?>
</script>
Respected Sebastian Morales, Now I can't move the code to setup and still I have tried to implement the code in setup file but did not worked and a blank page had appeared.
So, please help me on how to achieve it from view file code. And I will try it again to implement the code in setup file.
I am really sorry but please help me
Pls try this in your report's setup:
function setup()
{
if(isset($_POST['submit'])){
$startDatePicker=$_POST['startDatePicker'];
$endDatePicker=$_POST['endDatePicker'];
$selected_val = $_POST['radioList']; // Storing Selected Value In Variable
$startDatePickernew=date("d-m-Y", strtotime($startDatePicker) );
$endDatePickernew=date("d-m-Y", strtotime($endDatePicker) );
$employeeFirstName=$_POST['employeeFirstName'];
$branchid=$_SESSION['branch'];
$RegistrationID=$_POST['RegistrationID'];
}
$this->src("DB Name")->query("SELECT CN,CN, FROM TN ce
LEFT JOIN tblfeedbackcomments fc ON CN= CN
LEFT JOIN tblemployeedetails emp ON CN= CN
LEFT JOIN tblemployeedetails emp1 ON CN= CN
WHERE CN> 0 AND CN='$branchid' AND (DATE_FORMAT(CN, '%Y-%m-%d') >= STR_TO_DATE('$startDatePickernew', '%d-%m-%Y') AND DATE_FORMAT(CN, '%Y-%m-%d') <= STR_TO_DATE('$endDatePickernew', '%d-%m-%Y')) AND YEAR(ce.custRegisteredDate) <= YEAR(current_date - INTERVAL 1 MONTH)
AND MONTH(ce.custRegisteredDate) <= MONTH(current_date - INTERVAL 1 MONTH)
GROUP BY CN
ORDER BY CN DESC")
->pipe($this->dataStore("ds1"));
Then use $this->dataStore("ds1") for DataTables in your report's view. Let us know if there's any issue. Tks,
Build Your Excellent Data Report
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo