Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
Dear Team, i have successfully run report using following widgets in laravel.blade.
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google\ColumnChart;
My issues is that how can i export in PDF and EXCEL could not found any widget that directly export on laravel.blade
kind regards
Oky please find this is my controller public function that i call
public function sale_by_customer($s_d,$s_f,$d_from,$d_to,$customer,$s_r) {
$mainuser = User::where('id', auth()->user()->id)->first();
$company_id = $mainuser->company_id;
// dd($s_f);
$c_f = 1;
$c_t = Mod_gl_customers::where('company_id','=',$company_id)->max('id');
$sale_from = 0;
$sale_to = Mod_employeecoa::where('company_id','=',$company_id)->max('id');
if($s_d == 1){
$d_f = new Carbon(now());
$d_t = new Carbon(now());
}elseif($s_d == 2){
$d_f1 = new Carbon(now());
$d_f = $d_f1->startOfWeek();
$d_t1 = new Carbon(now());
$d_t = $d_t1->endOfWeek();
}elseif($s_d == 3){
$d_f1 = new Carbon(now());
$d_f = $d_f1->startOfMonth();
$d_t1 = new Carbon(now());
$d_t = $d_t1->endOfMonth();
}elseif($s_d == 4){
$d_f1 = new Carbon(now());
$d_f = $d_f1->startOfQuarter();
$d_t1 = new Carbon(now());
$d_t = $d_t1->endOfQuarter();
}elseif($s_d == 5){
$d_f1 = new Carbon(now());
$d_f = $d_f1->startOfYear();
$d_t1 = new Carbon(now());
$d_t = $d_t1->endOfYear();
}elseif($s_d == 6){
$d_f1 = new Carbon(now());
$d_f = $d_f1->subDay()->startOfDay();
$d_t1 = new Carbon(now());
$d_t = $d_t1->subDay()->endOfDay();
}elseif($s_d == 7){
$d_f1 = new Carbon(now());
$d_f = $d_f1->subDays($d_f1->dayOfWeek)->subWeek()->endOfDay();
$d_t1 = new Carbon(now());
$d_t = $d_t1->subDays($d_t1->dayOfWeek + 1)->startOfDay();
}elseif($s_d == 8){
$d_f1 = new Carbon(now());
$d_f = $d_f1->subMonth()->startOfMonth();
$d_t1 = new Carbon(now());
$d_t = $d_t1->subMonth()->endOfMonth();
}elseif($s_d == 9){
$d_f1 = new Carbon(now());
$d_f = $d_f1->startOfQuarter()->subQuarter(1)->startOfQuarter();
$d_t1 = new Carbon(now());
$d_t = $d_t1->startOfQuarter()->subQuarter(1)->endOfQuarter();
}elseif($s_d == 10){
$d_f1 = new Carbon(now());
$d_f = $d_f1->subYear()->startOfYear();
$d_t1 = new Carbon(now());
$d_t = $d_t1->subYear()->endOfYear();
}elseif($s_d == 11){
$d_f1 = new Carbon($d_from);
$d_f = $d_f1->startOfDay();
$d_t1 = new Carbon($d_to);
$d_t = $d_t1->endOfDay();
if($customer > 0){
$c_f = $customer;
$c_t = $customer;
}else{
$c_f = 1;
$c_t = Mod_gl_customers::where('company_id','=',$company_id)->max('id');
}
if($s_r > 0){
$sale_from = $s_r;
$sale_to = $s_r;
}else{
$sale_from = 0;
$sale_to = Mod_employeecoa::where('company_id','=',$company_id)->max('id');
}
}
$dropTempTables = DB::unprepared(DB::raw("DROP TABLE IF EXISTS temp_invoice_ap_datas ;"));
DB::select(' CREATE TEMPORARY TABLE temp_invoice_ap_datas
SELECT
ar_bill_invoice.id,date_time,item_code,customer_id,Invoice_number,voucher_no,voucher_id,invoice_id,
currency,qty,unit_price,discount,sales_tax,ar_vat_sales.rate as sales_tax_rate,
(qty * unit_price) -((qty * unit_price)* inv_discount_pattern.inv_discount/100) as invoice_amount,
(((qty * unit_price) -((qty * unit_price)* inv_discount_pattern.inv_discount/100) )* ar_vat_sales.rate/100) as sales_tax_amount
FROM ar_bill_invoice
LEFT OUTER JOIN inv_discount_pattern On inv_discount_pattern.inv_discount_id = ar_bill_invoice.discount AND inv_discount_pattern.company_id = "' . $company_id . '"
LEFT OUTER JOIN ar_vat_sales On ar_vat_sales.sales_tax_id = ar_bill_invoice.sales_tax AND ar_vat_sales.company_id = "' . $company_id . '"
WHERE ar_bill_invoice.company_id = "' . $company_id . '" AND ar_bill_invoice.deleted_at is NULL
AND customer_id IN (Select gl_customer.id FROM gl_customer where gl_customer.company_id = "'.$company_id.'" AND gl_customer.id BETWEEN "' . $c_f . '" AND "' . $c_t. '") AND sale_represntative BETWEEN "' . $sale_from . '" AND "' . $sale_to. '"
AND ar_bill_invoice.date_time between "' . $d_f . '" AND "' . $d_t . '"
');
$dropTempTables = DB::unprepared(DB::raw("DROP TABLE IF EXISTS temp_debit_note_data ;"));
DB::select(' CREATE TEMPORARY TABLE temp_debit_note_data
SELECT
debit_note_amount,sale_tax_id_add,invoice_id,customer_id,note_date
,(debit_note_amount + (debit_note_amount * ar_vat_sales.rate /100 )) as debit_note
FROM credit_note_sale
LEFT OUTER JOIN ar_vat_sales On ar_vat_sales.sales_tax_id = credit_note_sale.sale_tax_id_add AND ar_vat_sales.company_id = "' . $company_id . '"
where credit_note_sale.company_id = "'.$company_id.'" AND credit_note_sale.deleted_at is null AND customer_id between "' . $c_f . '" AND "' . $c_t . '"
AND credit_note_sale.note_date between "' . $d_f . '" AND "' . $d_t . '"
');
// $abc= DB::table('temp_debit_note_data')->get();
//
// dd($abc);
//purchase return impact
$date_now = new Carbon(now());
$dropTempTables = DB::unprepared(DB::raw("DROP TABLE IF EXISTS temp_purchase_return_data ;"));
DB::select(' CREATE TEMPORARY TABLE temp_purchase_return_data
SELECT
ar_returns_main.id,return_date,item_code,customer_id,invoice_no,voucher_no,voucher_id,inv_master_id,
currency,qty,unit_price,discount,sales_tax,ar_vat_sales.rate as sales_tax_rate,
(qty * unit_price) -((qty * unit_price)* inv_discount_pattern.inv_discount/100) +(((qty * unit_price) -((qty * unit_price)* inv_discount_pattern.inv_discount/100) )* ar_vat_sales.rate/100) as tot_return_amount
FROM ar_returns_main
LEFT OUTER JOIN inv_discount_pattern On inv_discount_pattern.inv_discount_id = ar_returns_main.discount AND inv_discount_pattern.company_id = "' . $company_id . '"
LEFT OUTER JOIN ar_vat_sales On ar_vat_sales.sales_tax_id = ar_returns_main.sales_tax AND ar_vat_sales.company_id = "' . $company_id . '"
WHERE ar_returns_main.company_id = "' . $company_id . '" AND ar_returns_main.deleted_at is NULL AND ar_returns_main.customer_id between "' . $c_f . '" AND "' . $c_t . '"
AND ar_returns_main.return_date between "' . $d_f . '" AND "' . $d_t . '"
');
$dropTempTables = DB::unprepared(DB::raw("DROP TABLE IF EXISTS temp_invoice_ap_datas_final ;"));
DB::select(' CREATE TEMPORARY TABLE temp_invoice_ap_datas_final
SELECT id,
customer_id,Invoice_number,invoice_id,date_time,
Invoice_number as invoice_number_new
,round(sum(invoice_amount),3) as invoice_amount,round(sum(sales_tax_amount),3) as sales_tax_amount
,round((sum(invoice_amount) + sum(sales_tax_amount)),2) as total_amount
,(SELECT if(SUM(invoice_payment)>0,SUM(invoice_payment),0) FROM ar_invoice_new_payment WHERE company_id = "'.$company_id.'" AND deleted_at is NULL AND invoice_id =temp_invoice_ap_datas.invoice_id and customer_id = temp_invoice_ap_datas.customer_id ) as partial_paid
,round(sum(invoice_amount),3) as payable
,(SELECT if(sum(debit_note)>0,sum(debit_note),0) FROM temp_debit_note_data where invoice_id = temp_invoice_ap_datas.invoice_id) as debit_note_amount
,(SELECT if(sum(tot_return_amount)>0,sum(tot_return_amount),0) fROM temp_purchase_return_data where inv_master_id =temp_invoice_ap_datas.invoice_id) as return_amount
FROM temp_invoice_ap_datas
group by invoice_id
');
$dropTempTables = DB::unprepared(DB::raw("DROP TABLE IF EXISTS temp_invoice_ap_datas_final_one ;"));
DB::select(' CREATE TEMPORARY TABLE temp_invoice_ap_datas_final_one
SELECT
id, customer_id,Invoice_number,invoice_id,invoice_amount,date_time,
invoice_number_new
,sum(sales_tax_amount) as sales_tax_amount
,round(sum(payable -debit_note_amount-return_amount),3) as final_payable
,count(invoice_id) as invoice_count
FROM temp_invoice_ap_datas_final
group by customer_id
');
$dropTempTables = DB::unprepared(DB::raw("DROP TABLE IF EXISTS temp_invoice_ap_datas_finals ;"));
DB::select(' CREATE TEMPORARY TABLE temp_invoice_ap_datas_finals
SELECT
customer_id
,gl_customer.account_name as customer_name
,sales_tax_amount
,final_payable
,invoice_count
FROM temp_invoice_ap_datas_final_one
LEFT OUTER JOIN gl_customer on gl_customer.id = temp_invoice_ap_datas_final_one.customer_id AND gl_customer.company_id = "'.$company_id.'"
where final_payable > 0
');
$cust_inv_data = DB::table('temp_invoice_ap_datas_finals')->where('final_payable','>',0)->get();
$sum_final_pay = DB::table('temp_invoice_ap_datas_finals')->where('final_payable','>',0)->sum('final_payable');
$sales_tax_amount = DB::table('temp_invoice_ap_datas_finals')->where('final_payable','>',0)->sum('sales_tax_amount');
$dropTempTables = DB::unprepared(DB::raw("DROP TABLE IF EXISTS temp_invoice_ap_datas_finals_one ;"));
DB::select(' CREATE TEMPORARY TABLE temp_invoice_ap_datas_finals_one
SELECT
customer_name
, CONVERT(final_payable, UNSIGNED ) as final_payable
FROM temp_invoice_ap_datas_finals
');
$daata = DB::table('temp_invoice_ap_datas_finals_one')->where('final_payable','>',0)->get();
$data = $daata->toArray();
//------------------------------------------------
return view ('admin.expo_erp.expo_reports.customer_rpt.sales_by_customer',[
'cust_inv_data' => $cust_inv_data,
'sum_final_pay' => $sum_final_pay,
'sales_tax_amount' => $sales_tax_amount,
'data' => $data,
]);
}
AND this is view
!--extends('admin.layouts.master')
section('style')-->
<link href="{{asset('assets/admin/global/plugins/datatables/datatables.min.css')}}" rel="stylesheet" type="text/css" />
<link href="{{asset('assets/admin/global/plugins/datatables/plugins/bootstrap/datatables.bootstrap.css')}}" rel="stylesheet" type="text/css" />
<link href="{{asset('assets/admin/global/css/components-rounded.min.css')}}" rel="stylesheet" id="style_components" type="text/css" />
<link href="{{asset('assets/admin/global/css/plugins.min.css')}}" rel="stylesheet" type="text/css" />
<link href="{{asset('assets/admin/global/plugins/bootstrap-modal/css/bootstrap-modal-bs3patch.css')}}" rel="stylesheet" type="text/css" />
<link href="{{asset('assets/admin/global/plugins/bootstrap-modal/css/bootstrap-modal.css')}}" rel="stylesheet" type="text/css" />
<style>
td a {color: #000000;}
</style>
<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google\ColumnChart;
use \koolreport\export\Exportable;
use \koolreport\excel\ExcelExportable;
//$data = array(
// array("category"=>"Books","sale"=>32000,"cost"=>20000,"profit"=>12000),
// array("category"=>"Accessories","sale"=>43000,"cost"=>36000,"profit"=>7000),
// array("category"=>"Phones","sale"=>54000,"cost"=>39000,"profit"=>15000),
// array("category"=>"Movies","sale"=>23000,"cost"=>18000,"profit"=>5000),
// array("category"=>"Others","sale"=>12000,"cost"=>6000,"profit"=>6000)
//);
//?>
<!--endsection
section('content')-->
<div class="page-content-inner" style="margin-top: -40px">
<div class="row">
<div class="col-md-12">
<div class="text-center">
<h1>Cash In Report</h1>
<p class="lead">This example show how to export report to PDF</p>
<form>
<button type="submit" class="btn btn-primary" formaction="export.php">Download Excel</button>
</form>
</div>
<div class="portlet light">
<div class="portlet-title">
<?php
Table::create(array(
"dataSource"=>$data
));
?>
<?php
ColumnChart::create(array(
"dataSource"=>$data,
"columns"=>array("customer_name","final_payable")
));
?>
</div>
<div class="portlet-body">
<!-- <div class="table-toolbar">
<div class="row">
<div class="col-md-9">
<div class="btn-group">
<?php ?>
<button onclick="assignaccount()" class="btn sbold green"> New Employee Group <i class="fa fa-plus"></i> </button>
<?php ?>
</div>
</div>
</div>
</div>
<table class="table table-striped table-bordered table-hover table-checkable " id="example">-->
</div>
</div>
</div>
</div>
</div>
<div class="modal container- fade draggable-modal" id="hr_status" style="position: absolute; top:35%;" data-backdrop="static" tabindex="-1" aria-hidden="true" data-width="650">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true"></button>
<h4 class="modal-title">Employee Type</h4>
</div>
<div class="modal-body"></div>
</div>
<div class="modal container- fade draggable-modal" id="filer_type" style="position: absolute; top:35%;" data-backdrop="static" tabindex="-1" aria-hidden="true" data-width="650">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true"></button>
<h4 class="modal-title">Filer Type</h4>
</div>
<div class="modal-body"></div>
</div>
<div class="modal container- fade draggable-modal" id="techer_info" style="position: absolute; top:35%;" data-backdrop="static" tabindex="-1" aria-hidden="true" data-width="1050">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true"></button>
<h4 class="modal-title">Teacher Basic Info</h4>
</div>
<div class="modal-body"></div>
</div>
<div class="modal container- fade draggable-modal" id="popupalert" style="position: absolute; top:35%;" data-backdrop="static" tabindex="-1" aria-hidden="true" data-width="1050">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true"></button>
<h4 class="modal-title">Are you sure?</h4>
</div>
<div class="modal-body"></div>
</div>
<script src="{{asset('assets/admin/global/scripts/datatable.js')}}" type="text/javascript"></script>
<script src="{{asset('assets/admin/global/plugins/datatables/datatables.min.js')}}" type="text/javascript"></script>
<script src="{{asset('assets/admin/global/plugins/datatables/plugins/bootstrap/datatables.bootstrap.js')}}" type="text/javascript"></script>
<!--<script src="{{asset('assets/admin/pages/scripts/table-datatables-managed.min.js')}}" type="text/javascript"></script>-->
<script src="{{asset('assets/admin/pages/scripts/table-datatables-buttons.min.js')}}" type="text/javascript"></script>
<script src="{{asset('assets/admin/global/plugins/bootstrap-modal/js/bootstrap-modalmanager.js')}}" type="text/javascript"></script>
<script src="{{asset('assets/admin/global/plugins/bootstrap-modal/js/bootstrap-modal.js')}}" type="text/javascript"></script>
<script src="{{asset('assets/admin/pages/scripts/ui-extended-modals.min.js')}}" type="text/javascript"></script>
This what i got when i call show report
i could not find the way to get export in excel and pdf what function is missing kindly guide
Hi,
I see that you only use our widgets by passing your data directly to the view. For our pdf and excel exports to work I'm afraid you have to use our koolreport class to setup your report and view.
In order to use koolreport in Laravel please check this free package:
https://www.koolreport.com/packages/laravel
In order to export to pdf and excel please see theses docs:
https://www.koolreport.com/docs/export/get_started/
https://www.koolreport.com/docs/excel/export_to_excel/
Don't hesitate to let us know if you have any question regarding these. Thanks!
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo