Skip to content

Instantly share code, notes, and snippets.

@worstn8mare

worstn8mare/gist:bf3cf536b55ef4486cb6fea73844c2db

Created October 28, 2017 03:23
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save worstn8mare/bf3cf536b55ef4486cb6fea73844c2db to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save worstn8mare/bf3cf536b55ef4486cb6fea73844c2db to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
<?php
namespace App\Http\Controllers\Accounting;
use App\Http\Controllers\Controller;
use Response;
use Session;
use Redirect;
use TCPDF;
use Input;
use DB;
use App\CompanyBranch;
use App\Partner;
use App\User;
use App\BillHeader;
use App\ReceiptHeader;
use App\CustomerType;
use App\RightHeader;
use App\PartnerBranch;
use App\Posting;
use App\GlAccount;
use Illuminate\Support\Collection;
class MYPDF extends TCPDF {
public function Header(){
$logo = CompanyBranch::where('id',1)->first();
$address = Partner::where('id',$logo->partner_id)->with('barangays','citys')->first();
$date = date("Y-m-d");
if (!$address->barangays) {
$barangays = "";
}
else {
$barangays = $address->barangays->provDesc;
}
if (!$address->citys) {
$city = "";
}
else {
$city = $address->citys->provDesc;
}
$html = '<style type="text/css">
.circle {
font-size: 8pt;
text-align: left;
}
.circle1 {
font-size: 8pt;
}
.circle2 {
text-align: right;
font-size: 8pt;
}
</style>
<table>
<tbody>
<tr>
<td colspan="2" style="width:65%;">
<div class="circle1">
</div>
</td>
<td style="width:80px;">
<div class="circle2">
</div>
</td>
<td style="width: 3px;"></td>
<td style="width:140px;">
<div class="circle2">
</div>
</td>
</tr>
</tbody>
<tbody>
<tr>
<td colspan="2" style="width:65%;">
<div class="circle1">
<b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$address->name.'</b><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$address->home.'<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$address->street.'<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$barangays.'
,'.$city.'<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tel: ('.$address->tel_countrycode.''.$address->tel_areacode.') '.$address->tel_lineno.' &nbsp; Fax: ('.$address->fax_countrycode.''.$address->fax_areacode.') '.$address->fax_lineno.'<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Email: '.$address->email.'
</div>
</td>
<td style="width:80px;">
<div class="circle2">
<label style="text-align:right;">Printed on :</label><br>
<label style="text-align:right;">Page :</label>
</div>
</td>
<td style="width: 3px;"></td>
<td style="width:140px;">
<div class="circle2">
<label style="text-align:left;">'.date('M-d-Y', strtotime($date)).'</label><br>
<label style="text-align:left;">'.$this->getAliasNumPage().' of '.$this->getAliasNbPages().'</label>
</div>
</td>
</tr>
</tbody>
</table>';
$this->SetMargins('', PDF_MARGIN_BOTTOM+20, '');
$this->writeHTMLCell($w = 0, $h = 0, $x = '', $y = '', $html, $border = 0, $ln = 1, $fill = 0, $reseth = true, $align = 'top', $autopadding = true);
}
}
class ReportsController extends Controller {
public function viewreports(){
$user_id = Session::get('user');
$customertype = CustomerType::where('status','Active')->get();
$user = User::where('partner_id',$user_id)->first();
$rights = RightHeader::where('user_id',$user_id)->where('tab_id',8)->where('sub_tab_id',0)->where('module_id',3)->where('type',1)->first();
$tabbing = ['tab' => 'Reports', 'subtab' => ''];
return view('/accounting/reports/reports',compact('customertype','rights','user','tabbing'));
}
public function ALLprint(){
if (Input::get('SA_formprint1002')) {
ini_set('max_execution_time', 10000);
// ini_set('memory_limit', '-1');
require_once('../vendor/tecnickcom/tcpdf/tcpdf.php');
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetTitle('Statement of Account');
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,0,0), array(0,0,0));
$pdf->setFooterData(array(0,64,0), array(0,64,128));
$pdf->SetPrintFooter(false);
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 14, '', true);
$pdf->SetMargins(7, 0, 10, true);
$resolution= array(215.9, 279.4);
$pdf->AddPage('P', $resolution);
$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$logo = CompanyBranch::where('id',1)->first();
$address = Partner::where('id',$logo->partner_id)->with('barangays','citys')->first();
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
$ctype = Input::get('customer_type');
$partner_id = Input::get('partner_id');
$sa_date = date('Y-m-d', strtotime(Input::get('sa_date')));
if (!$partner_id) {
// $billhead = BillHeader::where('status','!=','Void')->where('balance','!=',0)->groupBy('partner_id')->with('partner','customers')->get();
$currcounter = BillHeader::leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->groupBy('bill_headers.currency_id')
->select('bill_headers.*','deskpadcurrencies.curr_code as curr_code')
->get();
if ($ctype == 999) {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
}
else {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('customers.customer_types_id',$ctype)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
// $receipt = ReceiptHeader::where('unapplied_amt','!=',0)
// ->groupBy('partner_id')
// ->select('partner_id', DB::raw('SUM(unapplied_amt) as unapplied_amt'))
// ->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('customers.customer_types_id',$ctype)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
//$aa = count($billhead);
}
}
else {
// $billhead = BillHeader::where('partner_id',$partner_id)->where('status','!=','Void')->where('balance','!=',0)->groupBy('partner_id')->with('partner','customers')->get();
$currcounter = BillHeader::leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->groupBy('bill_headers.currency_id')
->select('bill_headers.*','deskpadcurrencies.curr_code as curr_code')
->get();
if ($ctype == 999) {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
}
else {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('customers.customer_types_id',$ctype)
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('customers.customer_types_id',$ctype)
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
}
}
// return Response::json($billhead, 200, array(), JSON_PRETTY_PRINT);
$pdf->writeHTML(view('printing.statementofaccountprint',compact('user','logo','address','billhead','ctype','partner_id','sa_date','currcounter','billheadnogroup','receipt'))->render());
ob_end_clean();
$pdf->Output('Statement of Account.pdf','I');
}
else if(Input::get('SA_excelform')){
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
$ctype = Input::get('customer_type');
$partner_id = Input::get('partner_id');
$sa_date = date('Y-m-d', strtotime(Input::get('sa_date')));
$currcounter = BillHeader::leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->groupBy('bill_headers.currency_id')
->select('bill_headers.currency_id','deskpadcurrencies.curr_code as curr_code');
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type as type');
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('partners.name','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code');
if(!$ctype){
}
else{
$billhead->where('customers.customer_types_id',$ctype);
$billheadnogroup->where('customers.customer_types_id',$ctype);
}
if(!$partner_id){
}
else{
$billhead->where('bill_headers.partner_id',$partner_id);
$billheadnogroup->where('bill_headers.partner_id',$partner_id);
}
$billhead = $billhead->get();
$billheadnogroup = $billheadnogroup->get();
$currcounter = $currcounter->get();
$data1 = [];
$totalsum = 0;
foreach ($currcounter as $currency) {
foreach ($billhead as $billheads) {
$sum = 0;
$totalsub = 0;
if($currency->currency_id == $billheads->currency_id){
$data1[] = array($billheads->partner_name);
foreach ($billheadnogroup as $billheadnogroups) {
if($billheads->partner_id == $billheadnogroups->partner_id){
$date1=date_create($billheadnogroups->bill_date);
$date2=date_create($sa_date);
$diff = date_diff($date1,$date2);
$data1[] = array('',
date('M-d-Y', strtotime($billheadnogroups->bill_date)),
sprintf("%08d", $billheadnogroups->id),
$diff->format("%a"),
number_format($billheadnogroups->total_amt,2,'.',','),
number_format($billheadnogroups->balance,2,'.',',')
);
$sum += number_format($billheadnogroups->balance,2,'.','');
}
}
if($billheads->type == 'Payment'){
if(ReceiptHeader::unappamountpayment($billheads->partner_id) == 0){
$totalsub = $sum - $billheads->unapplied_amt;
$data1[] = array('','','','','Subtotal:',number_format($totalsub,2,'.',','));
}
else{
$data1[] = array('','','','','Unapplied Receipts:',number_format(ReceiptHeader::unappamountpayment($billheads->partner_id),2,'.',','));
$totalsub = $sum - ReceiptHeader::unappamountpayment($billheads->partner_id);
$data1[] = array('','','','','Subtotal:',number_format($totalsub,2,'.',','));
}
}
else{
$totalsub = $sum - ReceiptHeader::unappamountpayment($billheads->partner_id);
$data1[] = array('','','','','Subtotal:',number_format($totalsub,2,'.',','));
}
$totalsum += $totalsub;
}
}
}
$data1[] = array('');
$data1[] = array('','','','','Total:',number_format($totalsum,2,'.',','));
ob_end_clean();
ob_start();
\Excel::create('Statements of Accounts', function($excel) use($data1) {
$excel->sheet('Sheet1', function($sheet) use($data1) {
$sheet->mergeCells('A1:E1');
$sheet->getStyle('A1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A2')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->fromArray($data1,null,'A2',true);
$sheet->row(1,array('Statements of Accounts'));
$sheet->row(2,array('Partner Name','Date','Billing No.','Aging','Billing Total','Balance'));
});
})->export('xls');
}
else{
ini_set('max_execution_time', 10000);
// ini_set('memory_limit', '-1');
require_once('../vendor/tecnickcom/tcpdf/tcpdf.php');
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetTitle('Statement of Account');
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,0,0), array(0,0,0));
$pdf->setFooterData(array(0,64,0), array(0,64,128));
$pdf->SetPrintFooter(false);
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 14, '', true);
$pdf->SetMargins(7, 0, 10, true);
$resolution= array(215.9, 279.4);
$pdf->AddPage('P', $resolution);
$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$logo = CompanyBranch::where('id',1)->first();
$address = Partner::where('id',$logo->partner_id)->with('barangays','citys')->first();
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
$ctype = Input::get('customer_type');
$partner_id = Input::get('partner_id');
$sa_date = date('Y-m-d', strtotime(Input::get('sa_date')));
if (!$partner_id) {
// $billhead = BillHeader::where('status','>','Void')->where('balance','>',0)->groupBy('partner_id')->with('partner','customers')->get();
$currcounter = BillHeader::leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->groupBy('bill_headers.currency_id')
->select('bill_headers.*','deskpadcurrencies.curr_code as curr_code')
->get();
if ($ctype == 999) {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
}
else {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('customers.customer_types_id',$ctype)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('customers.customer_types_id',$ctype)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
//$aa = count($billheadnogroup);
}
}
else {
// $billhead = BillHeader::where('partner_id',$partner_id)->where('status','>','Void')->where('balance','>',0)->groupBy('partner_id')->with('partner','customers')->get();
$currcounter = BillHeader::leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->groupBy('bill_headers.currency_id')
->select('bill_headers.*','deskpadcurrencies.curr_code as curr_code')
->get();
if ($ctype == 999) {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
}
else {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('customers.customer_types_id',$ctype)
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
// ->where('receipt_headers.receipt_type','Payment')
->groupBy('bill_headers.partner_id')
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('customers.customer_types_id',$ctype)
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.balance','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code')
->get();
}
}
$pdf->writeHTML(view('printing.statementofaccountprint',compact('user','logo','address','billhead','ctype','partner_id','sa_date','currcounter','billheadnogroup'))->render());
ob_end_clean();
$pdf->Output('Statement of Account.pdf','D');
}
}
public function OWTprint(){
if (Input::get('OWT_formprint1002')) {
ini_set('max_execution_time', 10000);
// ini_set('memory_limit', '-1');
require_once('../vendor/tecnickcom/tcpdf/tcpdf.php');
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetTitle('Outstanding Withholding Tax');
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,0,0), array(0,0,0));
$pdf->setFooterData(array(0,64,0), array(0,64,128));
$pdf->SetPrintFooter(false);
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 14, '', true);
$pdf->SetMargins(7, 0, 10, true);
$resolution= array(215.9, 279.4);
$pdf->AddPage('P', $resolution);
$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$logo = CompanyBranch::where('id',1)->first();
$address = Partner::where('id',$logo->partner_id)->with('barangays','citys')->first();
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
$ctype = Input::get('customer_type');
$partner_id = Input::get('partner_id');
$sa_date = date('Y-m-d', strtotime(Input::get('sa_date')));
$headers = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->select('bill_headers.currency_id as currency_id','deskpadcurrencies.curr_code as curr_code')
->groupBy('bill_headers.currency_id');
$details = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->select('bill_headers.currency_id as currency_id','partners.name as partner_name','bill_headers.partner_id as partner_id')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC');
$subDetails = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->select('bill_headers.*','partners.name as partner_name')
->orderBy('partners.name','ASC');
$unapplied_amt = DB::select("SELECT partner_id, sum(unapplied_amt) as unapplied_amt from receipt_headers
where status in ('Approved','Printed')
and unapplied_amt > 0 and receipt_type = 'Tax'
GROUP BY partner_id");
if($ctype == 999){
if(!$partner_id){
}
else{
$headers->where('bill_headers.partner_id',$partner_id);
$details->where('bill_headers.partner_id',$partner_id);
$subDetails->where('bill_headers.partner_id',$partner_id);
}
}
else{
if(!$partner_id){
}
else{
$headers->where('bill_headers.partner_id',$partner_id)->where('customers.customer_types_id',$ctype);
$details->where('bill_headers.partner_id',$partner_id)->where('customers.customer_types_id',$ctype);
$subDetails->where('bill_headers.partner_id',$partner_id)->where('customers.customer_types_id',$ctype);
}
}
$details = $details->get();
$headers = $headers->get();
$subDetails = $subDetails->get();
$pdf->writeHTML(view('printing.outstandingwithholdingtaxprint',compact('unapplied_amt','subDetails','user','logo','address','details','ctype','partner_id','sa_date','headers','receipt'))->render());
ob_end_clean();
$pdf->Output('Outstanding Withholding Tax.pdf','I');
}
else if (Input::get('OWT_formprint1001')){
ini_set('max_execution_time', 10000);
// ini_set('memory_limit', '-1');
require_once('../vendor/tecnickcom/tcpdf/tcpdf.php');
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetTitle('Outstanding Withholding Tax');
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,0,0), array(0,0,0));
$pdf->setFooterData(array(0,64,0), array(0,64,128));
$pdf->SetPrintFooter(false);
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 14, '', true);
$pdf->SetMargins(7, 0, 10, true);
$resolution= array(215.9, 279.4);
$pdf->AddPage('P', $resolution);
$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$logo = CompanyBranch::where('id',1)->first();
$address = Partner::where('id',$logo->partner_id)->with('barangays','citys')->first();
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
$ctype = Input::get('customer_type');
$partner_id = Input::get('partner_id');
$sa_date = date('Y-m-d', strtotime(Input::get('sa_date')));
if (!$partner_id) {
$currcounter = BillHeader::leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
->groupBy('bill_headers.currency_id')
->select('bill_headers.*','deskpadcurrencies.curr_code as curr_code')
->get();
if ($ctype == 999) {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
//->where('receipt_headers.receipt_type','Tax')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('app_details','bill_headers.id','=','app_details.bill_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','app_details.header_id as APPHEAD','app_details.bill_id as APPBILLID','app_details.ewt as APPEWT')
->get();
}
else {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('customers.customer_types_id',$ctype)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
//->where('receipt_headers.receipt_type','Tax')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
// $receipt = ReceiptHeader::where('unapplied_amt','!=',0)
// ->groupBy('partner_id')
// ->select('partner_id', DB::raw('SUM(unapplied_amt) as unapplied_amt'))
// ->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('app_details','bill_headers.id','=','app_details.bill_id')
->where('customers.customer_types_id',$ctype)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','app_details.header_id as APPHEAD','app_details.bill_id as APPBILLID','app_details.ewt as APPEWT')
->get();
//$aa = count($billhead);
}
}
else {
// $billhead = BillHeader::where('partner_id',$partner_id)->where('status','!=','Void')->where('balance','!=',0)->groupBy('partner_id')->with('partner','customers')->get();
$currcounter = BillHeader::leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
->groupBy('bill_headers.currency_id')
->select('bill_headers.*','deskpadcurrencies.curr_code as curr_code')
->get();
if ($ctype == 999) {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
//->where('receipt_headers.receipt_type','Tax')
->groupBy('bill_headers.partner_id')
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('app_details','bill_headers.id','=','app_details.bill_id')
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','app_details.header_id as APPHEAD','app_details.bill_id as APPBILLID','app_details.ewt as APPEWT')
->get();
}
else {
$billhead = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('receipt_headers','bill_headers.partner_id','=','receipt_headers.partner_id')
->where('customers.customer_types_id',$ctype)
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
//->where('receipt_headers.receipt_type','Tax')
->groupBy('bill_headers.partner_id')
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.partner_id','bill_headers.currency_id','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','receipt_headers.receipt_type')
->get();
$billheadnogroup = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->leftJoin('app_details','bill_headers.id','=','app_details.bill_id')
->where('customers.customer_types_id',$ctype)
->where('bill_headers.partner_id',$partner_id)
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','!=',0)
->where('bill_headers.bill_date','<=',$sa_date)
->orderBy('bill_headers.bill_date','ASC')
->select('bill_headers.*','partners.name as partner_name','customers.customer_types_id','deskpadcurrencies.curr_code as curr_code','app_details.header_id as APPHEAD','app_details.bill_id as APPBILLID','app_details.ewt as APPEWT')
->get();
}
}
//return Response::json($billhead, 200, array(), JSON_PRETTY_PRINT);
$pdf->writeHTML(view('printing.outstandingwithholdingtaxprint',compact('user','logo','address','billhead','ctype','partner_id','sa_date','currcounter','billheadnogroup','receipt'))->render());
ob_end_clean();
$pdf->Output('Outstanding Withholding Tax.pdf','D');
}
else if(Input::get('OWT_excelform')){
$ctype = Input::get('customer_type');
$partner_id = Input::get('partner_id');
$sa_date = date('Y-m-d', strtotime(Input::get('sa_date')));
$headers = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->leftJoin('deskpadcurrencies','bill_headers.currency_id','=','deskpadcurrencies.id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->select('bill_headers.currency_id as currency_id','deskpadcurrencies.curr_code as curr_code')
->groupBy('bill_headers.currency_id');
$details = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->select('bill_headers.currency_id as currency_id','partners.name as partner_name','bill_headers.partner_id as partner_id')
->groupBy('bill_headers.partner_id')
->orderBy('partners.name','ASC');
$subDetails = BillHeader::leftJoin('partners','bill_headers.partner_id','=','partners.id')
->leftJoin('customers','bill_headers.partner_id','=','customers.partner_id')
->whereIn('bill_headers.status',['Approved','Printed'])
->where('bill_headers.ewt_bal','>',0)
->where('bill_headers.bill_date','<=',$sa_date)
->select('bill_headers.*','partners.name as partner_name')
->orderBy('partners.name','ASC');
$unapplied_amt = DB::select("SELECT partner_id, sum(unapplied_amt) as unapplied_amt from receipt_headers
where status in ('Approved','Printed')
and unapplied_amt > 0 and receipt_type = 'Tax'
GROUP BY partner_id");
if($ctype == 999){
if(!$partner_id){
}
else{
$headers->where('bill_headers.partner_id',$partner_id);
$details->where('bill_headers.partner_id',$partner_id);
$subDetails->where('bill_headers.partner_id',$partner_id);
}
}
else{
if(!$partner_id){
}
else{
$headers->where('bill_headers.partner_id',$partner_id)->where('customers.customer_types_id',$ctype);
$details->where('bill_headers.partner_id',$partner_id)->where('customers.customer_types_id',$ctype);
$subDetails->where('bill_headers.partner_id',$partner_id)->where('customers.customer_types_id',$ctype);
}
}
$details = $details->get();
$headers = $headers->get();
$subDetails = $subDetails->get();
$data = [];
foreach ($headers as $header) {
$grand_total = 0.00;
foreach($details as $detail){
if($header->currency_id == $detail->currency_id){
$data[] = array($detail->partner_name);
foreach($subDetails as $subDetail){
if($subDetail->partner_id == $detail->partner_id){
$prev_applied = $subDetail->ewt - $subDetail->ewt_bal;
$data[] = array('',date('M-d-Y', strtotime($subDetail->bill_date)),sprintf("%08d", $subDetail->id),number_format($subDetail->ewt,2,'.',','),number_format($prev_applied,2,'.',','),number_format($subDetail->ewt_bal,2,'.',','));
}
}
foreach($unapplied_amt as $unapplied_amts){
if($unapplied_amts->partner_id == $detail->partner_id){
$unapplied_amts = number_format($unapplied_amts->unapplied_amt,2,'.',',');
$data[] = array('','','','','Unapplied EWT:',$unapplied_amts);
}
}
$total_bal = 0;
$total_unapp = 0;
foreach($subDetails as $subDetail){
if($subDetail->partner_id == $detail->partner_id){
$total_bal += $subDetail->ewt_bal;
}
}
foreach($unapplied_amt as $unapplied_amts){
if($unapplied_amts->partner_id == $detail->partner_id){
$total_unapp += $unapplied_amts->unapplied_amt;
}
else{
$total_unapp += 0;
}
}
$sub = number_format($total_bal - $total_unapp,2,'.',',');
$total = number_format($total_bal - $total_unapp,2,'.','');
$data[] = array('','','','','Subtotal:',$sub);
}
$grand_total += $total;
}
$data[] = array('');
$data[] = array('','','','','Total:',$grand_total);
// return Response::json($sub, 200, array(), JSON_PRETTY_PRINT);
}
ob_end_clean();
ob_start();
\Excel::create('Outstanding Withholding Tax', function($excel) use($data) {
$excel->sheet('Sheet1', function($sheet) use($data) {
$sheet->mergeCells('A1:E1');
$sheet->getStyle('A1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A2')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->fromArray($data,null,'A2',true);
$sheet->row(1,array('Outstanding Withholding Tax'));
$sheet->row(2,array('Partner Name','Date','Billing No.','EWT Total','Previously Applied','EWT Balance'));
});
})->export('xls');
}
}
public function RECEIPTprint(){
if (Input::get('REC_formprint1002')) {
ini_set('max_execution_time', 10000);
require_once('../vendor/tecnickcom/tcpdf/tcpdf.php');
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetTitle('Receipts Report');
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,0,0), array(0,0,0));
$pdf->setFooterData(array(0,64,0), array(0,64,128));
$pdf->SetPrintFooter(false);
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 14, '', true);
$pdf->SetMargins(7, 0, 10, true);
$resolution= array(215.9, 279.4);
$pdf->AddPage('P', $resolution);
$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$logo = CompanyBranch::where('id',1)->first();
$address = Partner::where('id',$logo->partner_id)->with('barangays','citys')->first();
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
if (!Input::get('recfromdate')) {
$recfromdate = '';
}
else {
$recfromdate = date('Y-m-d', strtotime(Input::get('recfromdate')));
}
if (!Input::get('rectodate')) {
$rectodate = '';
}
else {
$rectodate = date('Y-m-d', strtotime(Input::get('rectodate')));
}
$fromrecno = Input::get('fromrecno');
$torecno = Input::get('torecno');
if (!$recfromdate && !$rectodate) {
$forcurr = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin, deskpadcurrencies.curr_code
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
LEFT JOIN deskpadcurrencies ON receipt_headers.deskpadcurrency_id = deskpadcurrencies.id
WHERE receipt_headers.id >= $fromrecno AND receipt_headers.id <= $torecno
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id AND receipt_headers.deskpadcurrency_id
ORDER BY receipt_headers.id
");
$receipt = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
WHERE receipt_headers.id >= $fromrecno AND receipt_headers.id <= $torecno
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id
ORDER BY receipt_headers.id
");
}
else if (!$fromrecno && !$torecno) {
$forcurr = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin, deskpadcurrencies.curr_code
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
LEFT JOIN deskpadcurrencies ON receipt_headers.deskpadcurrency_id = deskpadcurrencies.id
WHERE receipt_headers.receipt_date >= '$recfromdate' AND receipt_headers.receipt_date <= '$rectodate'
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id AND receipt_headers.deskpadcurrency_id
ORDER BY receipt_headers.id
");
$receipt = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
WHERE receipt_headers.receipt_date >= '$recfromdate' AND receipt_headers.receipt_date <= '$rectodate'
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id
ORDER BY receipt_headers.id
");
}
else {
$forcurr = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin, deskpadcurrencies.curr_code
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
LEFT JOIN deskpadcurrencies ON receipt_headers.deskpadcurrency_id = deskpadcurrencies.id
WHERE receipt_headers.id >= $fromrecno AND receipt_headers.id <= $torecno
AND receipt_headers.receipt_date >= '$recfromdate' AND receipt_headers.receipt_date <= '$rectodate'
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id AND receipt_headers.deskpadcurrency_id
ORDER BY receipt_headers.id
");
$receipt = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
WHERE receipt_headers.id >= $fromrecno AND receipt_headers.id <= $torecno
AND receipt_headers.receipt_date >= '$recfromdate' AND receipt_headers.receipt_date <= '$rectodate'
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id
ORDER BY receipt_headers.id
");
}
$pdf->writeHTML(view('printing.receiptsreportprint',compact('user','logo','address','receipt','forcurr'))->render());
ob_end_clean();
$pdf->Output('Receipts Report.pdf','I');
}
else if (Input::get('REC_formprint1001')) {
ini_set('max_execution_time', 10000);
require_once('../vendor/tecnickcom/tcpdf/tcpdf.php');
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetTitle('Receipts Report');
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,0,0), array(0,0,0));
$pdf->setFooterData(array(0,64,0), array(0,64,128));
$pdf->SetPrintFooter(false);
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 14, '', true);
$pdf->SetMargins(7, 0, 10, true);
$resolution= array(215.9, 279.4);
$pdf->AddPage('P', $resolution);
$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$logo = CompanyBranch::where('id',1)->first();
$address = Partner::where('id',$logo->partner_id)->with('barangays','citys')->first();
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
if (!Input::get('recfromdate')) {
$recfromdate = '';
}
else {
$recfromdate = date('Y-m-d', strtotime(Input::get('recfromdate')));
}
if (!Input::get('rectodate')) {
$rectodate = '';
}
else {
$rectodate = date('Y-m-d', strtotime(Input::get('rectodate')));
}
$fromrecno = Input::get('fromrecno');
$torecno = Input::get('torecno');
if (!$recfromdate && !$rectodate) {
$receipt = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
WHERE receipt_headers.id >= $fromrecno AND receipt_headers.id <= $torecno
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id
ORDER BY receipt_headers.id
");
}
else if (!$fromrecno && !$torecno) {
$receipt = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
WHERE receipt_headers.receipt_date >= '$recfromdate' AND receipt_headers.receipt_date <= '$rectodate'
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id
ORDER BY receipt_headers.id
");
}
else {
$receipt = DB::select("
SELECT receipt_headers.*, SUM(app_details.ewt) as EWT, partners.name, partners.tin
FROM receipt_headers
LEFT JOIN app_details ON receipt_headers.id = app_details.receipt_id
LEFT JOIN partners ON receipt_headers.partner_id = partners.id
WHERE receipt_headers.id >= $fromrecno AND receipt_headers.id <= $torecno
AND receipt_headers.receipt_date >= '$recfromdate' AND receipt_headers.receipt_date <= '$rectodate'
AND receipt_headers.status IN ('Approved','Printed')
GROUP BY receipt_headers.id
ORDER BY receipt_headers.id
");
}
$pdf->writeHTML(view('printing.receiptsreportprint',compact('user','logo','address','receipt'))->render());
ob_end_clean();
$pdf->Output('Receipts Report.pdf','D');
}
else if(Input::get('REC_excelform')){
if (!Input::get('recfromdate')) {
$recfromdate = '';
}
else {
$recfromdate = date('Y-m-d', strtotime(Input::get('recfromdate')));
}
if (!Input::get('rectodate')) {
$rectodate = '';
}
else {
$rectodate = date('Y-m-d', strtotime(Input::get('rectodate')));
}
$fromrecno = Input::get('fromrecno');
$torecno = Input::get('torecno');
$receipt = ReceiptHeader::leftJoin('partners','receipt_headers.partner_id','=','partners.id')
->where('receipt_headers.status','!=','Void')
->select('receipt_headers.id as id','receipt_headers.receipt_date as receipt_date',
'receipt_headers.doc_no as doc_no','partners.name as name',
'partners.tin as tin','receipt_headers.total as total',
'receipt_headers.status as Status','receipt_headers.receipt_type as receipt_type','receipt_headers.unapplied_amt as unapplied_amt')
->groupBy('receipt_headers.id')
->orderBy('receipt_headers.id');
if (!$recfromdate && !$rectodate) {
$receipt->where('receipt_headers.id','>=',$fromrecno);
$receipt->where('receipt_headers.id','<=',$torecno);
}
else if (!$fromrecno && !$torecno) {
$receipt->where('receipt_headers.receipt_date','>=',$recfromdate);
$receipt->where('receipt_headers.receipt_date','<=',$rectodate);
}
else {
$receipt->where('receipt_headers.id','>=',$fromrecno);
$receipt->where('receipt_headers.id','<=',$torecno);
$receipt->where('receipt_headers.receipt_date','>=',$recfromdate);
$receipt->where('receipt_headers.receipt_date','<=',$rectodate);
}
$receipt = $receipt->get();
$data1 = [];
$data2 = [];
foreach ($receipt as $receipts) {
$x = '';
if ($receipts->Status == "New") {
$x = 'New';
}
elseif ($receipts->Status == "Finalized") {
$x = 'Finalized';
}
elseif ($receipts->Status == "Approved" || $receipts->Status == "Printed") {
if($receipts->unapplied_amt < $receipts->total && $receipts->unapplied_amt > 0){
$x = 'Partial';
}
elseif($receipts->unapplied_amt == $receipts->total){
$x = 'Unapplied';
}
else{
$x = 'Applied';
}
}
$getdate = date('Y,m,d', strtotime($receipts->receipt_date));
$app_headers = DB::select("select sum(app_details.amount) as amount, sum(app_details.ewt) as ewt from app_details
inner join app_headers on app_details.header_id = app_headers.id
where app_details.receipt_id = ?
and app_headers.status in ('Approved','Printed')",[$receipts->id]);
$data1['receipt_type'] = $receipts->receipt_type;
$data1['id'] = sprintf("%08d",$receipts->id);
$data1['date'] = '=date('.$getdate.')';
$data1['doc_no'] = $receipts->doc_no;
$data1['name'] = $receipts->name;
$data1['tin'] = $receipts->tin;
$data1['total'] = number_format($receipts->total,2,'.',',');
$data1['applied'] = number_format($app_headers[0]->amount,2,'.',',');
$data1['ewt'] = number_format($app_headers[0]->ewt,2,'.',',');
$data1['partial'] = $x;
array_push($data2,$data1);
}
ob_end_clean();
ob_start();
\Excel::create('Receipt Report', function($excel) use($data2) {
$excel->sheet('Sheet1', function($sheet) use($data2) {
$sheet->setColumnFormat(array(
'C' => 'mmm-dd-yyyy',
));
$sheet->fromArray($data2);
$sheet->row(1,array('Receipt Type','No.','Date','Doc. No.','Partner Name','TIN','Total','Applied','EWT Total','Status'));
});
})->export('xls');
}
}
public function SAWTprint(){
if(Input::get('excel_print')) {
$year = Input::get('year');
$quarter = Input::get('quarter');
$branch = Input::get('branch');
$detail = ReceiptHeader::leftJoin('receipt_details','receipt_details.receipt_id','=','receipt_headers.id')
->leftJoin('partners','partners.id','=','receipt_headers.partner_id')
->leftJoin('actng_atc_codes','actng_atc_codes.id','=','receipt_details.actng_atc_code_id')
->where('receipt_type','=','Tax')
->select('receipt_headers.id as id','partners.tin as tin','partners.name as name','actng_atc_codes.name as atc','receipt_details.percent as percent',
DB::raw('SUM(receipt_details.income_pay) as income_pay, SUM(receipt_details.amount) as amount_with_tax'))
->groupBy('receipt_headers.partner_id','receipt_details.actng_atc_code_id')
->orderBy('name','ASC');
if(Input::get('branch') == 999 || Input::get('branch') == ""){
}
else{
$detail->where('cb',Input::get('branch'));
}
if(!$year){
if($quarter == 1){
$detail->whereMonth('receipt_headers.receipt_date','>=',1);
$detail->whereMonth('receipt_headers.receipt_date','<=',3);
}
else if($quarter == 2){
$detail->whereMonth('receipt_headers.receipt_date','>=',4);
$detail->whereMonth('receipt_headers.receipt_date','<=',6);
}
else if($quarter == 3){
$detail->whereMonth('receipt_headers.receipt_date','>=',7);
$detail->whereMonth('receipt_headers.receipt_date','<=',9);
}
else if($quarter == 4){
$detail->whereMonth('receipt_headers.receipt_date','>=',10);
$detail->whereMonth('receipt_headers.receipt_date','<=',12);
}
}
else{
$detail->whereYear('receipt_headers.receipt_date','=',$year);
if($quarter == 1){
$detail->whereMonth('receipt_headers.receipt_date','>=',1);
$detail->whereMonth('receipt_headers.receipt_date','<=',3);
}
else if($quarter == 2){
$detail->whereMonth('receipt_headers.receipt_date','>=',4);
$detail->whereMonth('receipt_headers.receipt_date','<=',6);
}
else if($quarter == 3){
$detail->whereMonth('receipt_headers.receipt_date','>=',7);
$detail->whereMonth('receipt_headers.receipt_date','<=',9);
}
else if($quarter == 4){
$detail->whereMonth('receipt_headers.receipt_date','>=',10);
$detail->whereMonth('receipt_headers.receipt_date','<=',12);
}
}
$detail = $detail->get();
$data = [];
$x = 1;
$total = 0;
foreach ($detail as $details) {
$data[] = array($x++,$details->tin,$details->name,$details->atc,$details->income_pay,number_format($details->percent).'%',number_format($details->amount_with_tax,2,'.',','));
$total += number_format($details->amount_with_tax,2,'.','');
}
$data[] = array('');
$data[] = array('','','','','','Total:',number_format($total,2,'.',','));
ob_end_clean();
ob_start();
\Excel::create('Summary List of Withholding', function($excel) use($data){
$excel->sheet('Quarter', function($sheet) use($data){
$sheet->fromArray($data, null, 'A4',true);
$sheet->mergeCells('A1:G1');
$sheet->mergeCells('A2:G2');
$sheet->mergeCells('A3:G3');
$sheet->getStyle('A1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A2')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A3')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->row(1,array('MTO Technotrends'));
$sheet->row(2,array('Summary Alphalist of Withholding Taxes(SAWT)'));
$quarter = Input::get('quarter');
$year = Input::get('year');
if($quarter == 1){
$sheet->row(3,array('Period : January to March '.$year));
}
elseif ($quarter == 2) {
$sheet->row(3,array('Period : April to June '.$year));
}
elseif ($quarter == 3) {
$sheet->row(3,array('Period : July to September '.$year));
}
elseif ($quarter == 4) {
$sheet->row(3,array('Period : October to December '.$year));
}
$sheet->row(4,array('SEQ. NO.','TAXPAYER IDENTIFICATION NUMBER(TIN)','REGISTERED NAME','ATC','AMOUNT TAX BASE','TAX RATE','AMOUNT OF TAX WITHHELD'));
});
})->export('xls');
}
elseif(Input::get('pdf_print')){
ini_set('max_execution_time', 10000);
require_once('../vendor/tecnickcom/tcpdf/tcpdf.php');
$pdf = new MYPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetTitle('Summary Alphalist of Withholding Taxes');
$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 001', PDF_HEADER_STRING, array(0,0,0), array(0,0,0));
$pdf->setFooterData(array(0,64,0), array(0,64,128));
$pdf->SetPrintFooter(false);
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
$pdf->setFontSubsetting(true);
$pdf->SetFont('dejavusans', '', 14, '', true);
$pdf->SetMargins(7, 0, 10, true);
$resolution= array(215.9, 279.4);
$pdf->AddPage('P', $resolution);
$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
$year = Input::get('year');
$quarter = Input::get('quarter');
$branch = Input::get('branch');
$user = User::where('partner_id',Session::get('user'))->with('partner')->first();
$detail = ReceiptHeader::leftJoin('receipt_details','receipt_details.receipt_id','=','receipt_headers.id')
->leftJoin('partners','partners.id','=','receipt_headers.partner_id')
->leftJoin('actng_atc_codes','actng_atc_codes.id','=','receipt_details.actng_atc_code_id')
->where('receipt_type','=','Tax')
->select('receipt_headers.id as id','partners.tin as tin','partners.name as name','actng_atc_codes.name as atc','receipt_details.percent as percent',
DB::raw('SUM(receipt_details.income_pay) as income_pay, SUM(receipt_details.amount) as amount_with_tax'))
->groupBy('receipt_headers.partner_id','receipt_details.actng_atc_code_id')
->orderBy('name','ASC');
if(Input::get('branch') == 999 || Input::get('branch') == ""){
}
else{
$detail->where('cb',Input::get('branch'));
}
if(!$year){
if($quarter == 1){
$detail->whereMonth('receipt_headers.receipt_date','>=',1);
$detail->whereMonth('receipt_headers.receipt_date','<=',3);
}
else if($quarter == 2){
$detail->whereMonth('receipt_headers.receipt_date','>=',4);
$detail->whereMonth('receipt_headers.receipt_date','<=',6);
}
else if($quarter == 3){
$detail->whereMonth('receipt_headers.receipt_date','>=',7);
$detail->whereMonth('receipt_headers.receipt_date','<=',9);
}
else if($quarter == 4){
$detail->whereMonth('receipt_headers.receipt_date','>=',10);
$detail->whereMonth('receipt_headers.receipt_date','<=',12);
}
}
else{
$detail->whereYear('receipt_headers.receipt_date','=',$year);
if($quarter == 1){
$detail->whereMonth('receipt_headers.receipt_date','>=',1);
$detail->whereMonth('receipt_headers.receipt_date','<=',3);
}
else if($quarter == 2){
$detail->whereMonth('receipt_headers.receipt_date','>=',4);
$detail->whereMonth('receipt_headers.receipt_date','<=',6);
}
else if($quarter == 3){
$detail->whereMonth('receipt_headers.receipt_date','>=',7);
$detail->whereMonth('receipt_headers.receipt_date','<=',9);
}
else if($quarter == 4){
$detail->whereMonth('receipt_headers.receipt_date','>=',10);
$detail->whereMonth('receipt_headers.receipt_date','<=',12);
}
}
$detail = $detail->get();
$pdf->writeHTML(view('printing.summarylistwithholdingtaxes',compact('user','detail','quarter','year'))->render());
ob_end_clean();
$pdf->Output('Summary Alphalist of Withholding Taxes.pdf','I');
}
}
public function purchase_report(){
if(Input::get('pr_excel')){
$pr_month = Input::get('pr_month');
$pr_year = Input::get('pr_year');
if(Input::get('branch') == 0){
$branches = CompanyBranch::where('id', 1)->first();
}
else{
$branches = PartnerBranch::where('id', Input::get('branch'))->first();
}
$header = Posting::join('partners','postings.partner_id','=','partners.id')
->leftJoin('cities','partners.city','=','cities.citymunCode')
->select(DB::raw("partners.tin as tin,partners.tin2 as tin2,partners.tin3 as tin3, partners.name as registered_name,CONCAT(partners.home,' ',partners.street) as address1,CONCAT(cities.provDesc,' ') as address2, postings.post_date as post_date,postings.id as id"))
->where('postings.status','Posted')
->whereIn('postings.doc_type',['RR','CV'])
->orderBy('partners.name','asc');
if(!Input::get('branch')){
if(Input::get('pr_year')){
$header->whereYear('postings.post_date', '=', $pr_year);
}
}
else{
$header->where('postings.cb', Input::get('branch'));
}
$headers = $header->get();
// return Response::json($headers, 200, array(), JSON_PRETTY_PRINT);
$data = [
'1' => [], '2' => [], '3' => [],
'4' => [], '5' => [], '6' => [],
'7' => [], '8' => [], '9' => [],
'10' => [], '11' => [], '12' => []
];
$dataTotal = [
'1' => [], '2' => [], '3' => [],
'4' => [], '5' => [], '6' => [],
'7' => [], '8' => [], '9' => [],
'10' => [], '11' => [], '12' => []
];
$existStorage = [
'1' => [], '2' => [], '3' => [],
'4' => [], '5' => [], '6' => [],
'7' => [], '8' => [], '9' => [],
'10' => [], '11' => [], '12' => []
];
foreach ($headers as $header) {
$registered_name_replace = str_replace(',', '', $header->registered_name);
$address1_replace = str_replace(',', '', $header->address1);
$address2_replace = str_replace(',', '', $header->addres2);
$ex_tin = explode('-', $header->tin);
foreach ([1,2,3,4,5,6,7,8,9,10,11,12] as $i => $v) {
if(date('n', strtotime($header->post_date)) == $v){
$refData = $header->partner_id . '-' . $v;
if(in_array($refData, $existStorage[$v])){
$keyx = array_keys($existStorage[$v],$refData);
$m = $data[$v][$keyx[0]];
$ar = $this->detail_purchase($header,$m);
$data[$v][$keyx[0]] = $ar;
$dataTotal[$v][$keyx[0]] = ['nv' => $ar[4], 'zr' => $ar[5], 'srv' => $ar[6], 'cg' => $ar[7], 'ocg' => $ar[8], 'vat' => $ar[9]];
}
else{
$m = array((strlen($header->tin) > 3) ? $ex_tin[0].$ex_tin[1].$ex_tin[2] : $header->tin.$header->tin2.$header->tin3,$registered_name_replace,$address1_replace,$address2_replace,'','','','','','');
$ar = $this->detail_purchase($header,$m);
if($ar[4] > 0 || $ar[5] > 0 || $ar[4] > 0 || $ar[6] > 0 || $ar[7] > 0 || $ar[8] > 0 || $ar[9] > 0){
$data[$v][] = $ar;
$existStorage[$v][] = $refData;
$dataTotal[$v][] = ['nv' => $ar[4], 'zr' => $ar[5], 'srv' => $ar[6], 'cg' => $ar[7], 'ocg' => $ar[8], 'vat' => $ar[9]];
}
}
break;
}
}
}
// return Response::json($dataTotal, 200, array(), JSON_PRETTY_PRINT);
////total excel code ////
$data1 = [];
$month1 = ['January','February','March','April','May','June','July','August','September','October','November','December'];
foreach($month1 as $k1 => $value1){
$details_t = array($value1,'','','','','','');
foreach ([1,2,3,4,5,6,7,8,9,10,11,12] as $i => $y) {
if(($k1 + 1) == $y){
$ndata2 = collect($dataTotal);
$onlyData2 = $ndata2->only($y);
$sumData = collect($onlyData2[$y]);
$totalnv = $sumData->sum('nv');
$totalzr = $sumData->sum('zr');
$totalsrv = $sumData->sum('srv');
$totalcg = $sumData->sum('cg');
$totalocg = $sumData->sum('ocg');
$totalvat = $sumData->sum('vat');
$details_t = array($value1,($totalnv > 0) ? $totalnv : '-',
($totalzr > 0) ? $totalzr: '-',
($totalsrv > 0) ? $totalsrv : '-',
($totalcg > 0) ? $totalcg : '-',
($totalocg > 0) ? $totalocg : '-',
($totalvat > 0) ? $totalvat : '-');
}
}
$data1[] = $details_t;
}
$data1[] = array('');
$data1[] = array('TOTAL:');
/// end excel code /////
ob_end_clean();
ob_start();
\Excel::create('Purchase Report', function($excel) use($data, $dataTotal, $branches, $pr_month, $pr_year, $data1) {
$month = ['January','February','March','April','May','June','July','August','September','October','November','December'];
foreach ($month as $k => $value) {
$excel->sheet($value, function($sheet) use($data, $dataTotal, $branches, $pr_month, $k, $value, $pr_year) {
$sheet->mergeCells('A1:J1');
$sheet->row(1, function($row) {
$row->setFontWeight('bold')->setFontSize(18);
});
$sheet->row(6, function($row) {
$row->setFontWeight('bold');
});
$sheet->row(7, function($row) {
$row->setFontWeight('bold');
});
$sheet->mergeCells('A2:J2');
$sheet->mergeCells('A3:J3');
$sheet->mergeCells('A4:J4');
$sheet->mergeCells('A5:J5');
$sheet->getStyle('A1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A2')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A3')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('I6')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A7:J7')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$index = ($k + 1);
$ndata = collect($data);
$ndata2 = collect($dataTotal);
$onlyData = $ndata->only($index);
$onlyData2 = $ndata2->only($index);
$sumData = collect($onlyData2[$index]);
$totalnv = $sumData->sum('nv');
$totalzr = $sumData->sum('zr');
$totalsrv =$sumData->sum('srv');
$totalcg = $sumData->sum('cg');
$totalocg = $sumData->sum('ocg');
$totalvat = $sumData->sum('vat');
$datax = ['','','TOTAL PURCHASES','', ($totalnv > 0) ? number_format($totalnv,2,'.',',') : '-', ($totalzr > 0) ? number_format($totalzr,2,'.',',') : '-', ($totalsrv > 0) ? number_format($totalsrv,2,'.',',') : '-', ($totalcg > 0) ? number_format($totalcg,2,'.',',') : '-', ($totalocg > 0) ? number_format($totalocg,2,'.',',') : '-', ($totalvat > 0) ? number_format($totalvat,2 ,'.',','): '-'];
$tdk = $onlyData[$index];
$tdk[] = $datax;
$sheet->fromArray($tdk,null,'A7',true);
$sheet->row(1,array($branches->name));
$sheet->row(2,array('PURCHASES REPORT'));
$sheet->row(3,array('FOR THE MONTH OF'.' '.$value.'-'.$pr_year));
$sheet->row(6,array('','','','','','','','','OTHER THAN',''));
$sheet->row(7,array('TIN','REGISTERED NAME','ADDRESS 1','ADDRESS 2','EXEMPT','ZERO-RATED','SERVICES','CAPITAL GOODS','CAPITAL GOODS','VAT'));
});
}
$excel->sheet('Total', function($sheet) use($data1) {
$sheet->getStyle('A1:G1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A2:G2')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A16:G16')->getAlignment()->applyFromArray(
array('horizontal' => 'right'));
$sheet->row(1, function($row) {
$row->setFontWeight('bold');
});
$sheet->row(2, function($row) {
$row->setFontWeight('bold');
});
$sheet->row(16, function($row) {
$row->setFontWeight('bold');
});
$sheet->fromArray($data1,null,'A2',true);
$sheet->row(1,array('','','','','','OTHER THAN'));
$sheet->row(2,array('DATE','EXEMPT','ZERO-RATED','SERVICES','CAPITAL GOODS','CAPITAL GOODS','VAT'));
$sheet->setColumnFormat(array(
'B3:B16' => '0.00',
'C3:C16' => '0.00',
'D3:D16' => '0.00',
'E3:E16' => '0.00',
'F3:F16' => '0.00',
'G3:G16' => '0.00'
));
$sheet->setCellValue('B16','=SUM(B3:B14)');
$sheet->setCellValue('C16','=SUM(C3:C14)');
$sheet->setCellValue('D16','=SUM(D3:D14)');
$sheet->setCellValue('E16','=SUM(E3:E14)');
$sheet->setCellValue('F16','=SUM(F3:F14)');
$sheet->setCellValue('G16','=SUM(G3:G14)');
});
$excel->setActiveSheetIndex($pr_month -1);
})->export('xls');
}
}
public function monthTotal($m,$e){
$z = 0;
if($m == '-' || $m == ''){
$z = 0;
}
else{
$z = $m;
}
return $m = $z + $e;
}
public function detail_purchase($header,$month){
$vat_total = 0;
foreach($header->posting_purchase as $details){
$expense = ($details->expense > 0) ? $details->expense : '-';
$vat = ($details->vat > 0) ? $details->vat : 0;
$vat_total += $vat;
if($details->purchase_type == 'nv'){
$month[4] = $this->monthTotal($month[4],$expense);
}
if($details->purchase_type == 'zr'){
$month[5] = $this->monthTotal($month[5],$expense);
}
if($details->purchase_type == 'srv'){
$month[6] =$this->monthTotal($month[6],$expense);
}
if($details->purchase_type == 'cg'){
$month[7] = $this->monthTotal($month[7],$expense);
}
if($details->purchase_type == 'ocg'){
$month[8] = $this->monthTotal($month[8],$expense);
}
}
$month[9] = $this->monthTotal($month[9],$vat_total);
return $month;
}
public function sales_report(){
if(Input::get('sr_excel')){
$sr_month = Input::get('sr_month');
$sr_year = Input::get('sr_year');
if(Input::get('branch') == 0){
$branches = CompanyBranch::where('id', 1)->first();
}
else{
$branches = PartnerBranch::where('id', Input::get('branch'))->first();
}
$header = Posting::join('partners','postings.partner_id','=','partners.id')
->leftJoin('cities','partners.city','=','cities.citymunCode')
->leftJoin('provinces','partners.province','=','provinces.provCode')
->select(DB::raw("partners.tin as tin, partners.tin2 as tin2, partners.tin3 as tin3, partners.name as registered_name, CONCAT(partners.home,' ',partners.street) as address1, CONCAT(cities.provDesc,' ',provinces.provCode) as address2,postings.post_date as post_date, postings.id as id"))
->whereIn('postings.doc_type',['Bill','Receipt'])
->where('postings.status','Posted')
->groupBy('partners.name')->orderBy('partners.name','asc')->with('posting_sale');
if(!Input::get('branch')){
if(Input::get('sr_month')){
$header->whereMonth('postings.post_date', '=', $sr_month);
}
if(Input::get('sr_year')){
$header->whereYear('postings.post_date', '=', $sr_year);
}
}
else{
$header->where('postings.cb', Input::get('branch'));
}
$headers = $header->get();
// return Response::json($headers, 200, array(), JSON_PRETTY_PRINT);
$data = [];
$totalnv = 0;
$totalzr = 0;
$totalvat = 0;
foreach ($headers as $header) {
$registered_name_replace = str_replace(',', '', $header->registered_name);
$address1_replace = str_replace(',', '', $header->address1);
$address2_replace = str_replace(',', '', $header->addres2);
$ex_tin = explode('-', $header->tin);
$tdk = array((strlen($header->tin) > 3) ? $ex_tin[0].$ex_tin[1].$ex_tin[2] : $header->tin.$header->tin2.$header->tin3,$registered_name_replace,$address1_replace,$address2_replace,'','','');
$vat_total = 0;
foreach($header->posting_sale as $details){
$income = ($details->income > 0) ? $details->income : '-';
$vat = ($details->vat > 0) ? $details->vat : 0;
$vat_total += $vat;
if($details->sales_type == 'nv'){
$tdk[4] = $income;
$totalnv += $income;
}
if($details->sales_type == 'zr'){
$tdk[5] = $income;
$totalzr += $income;
}
}
$tdk[6] = $vat_total;
$totalvat += $vat_total;
if($vat_total > 0){
$data[] = $tdk;
}
}
$data[] = array('');
$data[] = array('','','TOTAL PURCHASES','', ($totalnv > 0) ? number_format($totalnv,2,'.',',') : '-', ($totalzr > 0) ? number_format($totalzr,2,'.',',') : '-', ($totalvat > 0) ? number_format($totalvat,2 ,'.',','): '-');
////// start excel total for sales report code /////
$data1 = [];
$month1 = ['January','February','March','April','May','June','July','August','September','October','November','December'];
foreach($month1 as $k1 => $value1){
$details_t = array($value1,'','','');
if(($k1 + 1) == $sr_month){
$details_t = array($value1, $totalnv, $totalzr, $totalvat);
}
$data1[] = $details_t;
}
$data1[] = array('');
$data1[] = array('TOTAL:', ($totalnv > 0) ? number_format($totalnv,2,'.',',') : '-', ($totalzr > 0) ? number_format($totalzr,2,'.',',') : '-', ($totalvat > 0) ? number_format($totalvat,2,'.',',') : '-');
////// end excel total for sales report code /////
ob_end_clean();
ob_start();
\Excel::create('Sales Report', function($excel) use($data, $branches, $sr_month, $sr_year, $data1) {
$month = ['January','February','March','April','May','June','July','August','September','October','November','December'];
foreach ($month as $k => $value) {
$excel->sheet($value, function($sheet) use($data, $branches, $k, $sr_month, $sr_year, $value) {
$sheet->mergeCells('A1:G1');
$sheet->row(1, function($row) {
$row->setFontWeight('bold')->setFontSize(18);
});
$sheet->row(6, function($row) {
$row->setFontWeight('bold');
});
$sheet->mergeCells('A2:G2');
$sheet->mergeCells('A3:G3');
$sheet->mergeCells('A4:G4');
$sheet->mergeCells('A5:G5');
$sheet->getStyle('A1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A2')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A3')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A6:G6')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
if(($k + 1) == $sr_month){
$sheet->fromArray($data,null,'A6',true);
}
$sheet->row(1,array($branches->name));
$sheet->row(2,array('SALES REPORT'));
$sheet->row(3,array('FOR THE MONTH OF'.' '.$value.'-'.$sr_year));
$sheet->row(6,array('TIN','REGISTERED NAME','ADDRESS 1','ADDRESS 2','EXEMPT','ZERO-RATED','VAT'));
});
}
$excel->sheet('Total', function($sheet) use($data1, $branches) {
$sheet->getStyle('A1:D1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A15:D15')->getAlignment()->applyFromArray(
array('horizontal' => 'right'));
$sheet->row(1, function($row) {
$row->setFontWeight('bold');
});
$sheet->row(15, function($row) {
$row->setFontWeight('bold');
});
$sheet->fromArray($data1,null,'A1',true);
$sheet->row(1,array('DATE','EXEMPT','ZERO-RATED','VAT'));
$sheet->setColumnFormat(array(
'B3:B15' => '0.00',
'C3:C15' => '0.00',
'D3:D15' => '0.00'
));
$sheet->setCellValue('B15','=SUM(B3:B13)');
$sheet->setCellValue('C15','=SUM(C3:C13)');
$sheet->setCellValue('D15','=SUM(D3:D13)');
});
$excel->setActiveSheetIndex($sr_month -1);
})->export('xls');
}
}
public function detailed_report(){
if(Input::get('dr_excel')){
$drdatefrom = date('m/d/Y', strtotime(Input::get('dr_date_from')));
$drdateto = date('m/d/Y', strtotime(Input::get('dr_date_to')));
$drdatefrom1 = date('Y-m-d', strtotime(Input::get('dr_date_from')));
$drdateto1 = date('Y-m-d', strtotime(Input::get('dr_date_to')));
if(Input::get('branch') == 0){
$branches = CompanyBranch::where('id', 1)->first();
}
else{
$branches = PartnerBranch::where('id', Input::get('branch'))->first();
}
$title = GlAccount::where('id', Input::get('title'))->first();
$sumdebit = Posting::join('posting_gls','postings.id','=','posting_gls.posting_id')
->select(DB::raw('sum(posting_gls.amount) as amount'))
->where([
['postings.post_date','<',$drdatefrom1],
['posting_gls.gl_account_id', Input::get('title')],
['posting_gls.amount','>',0]
])
->first();
$headers = Posting::join('partners','postings.partner_id','=','partners.id')
->join('posting_gls','postings.id','=','posting_gls.posting_id')
->join('gl_accounts','posting_gls.gl_account_id','=','gl_accounts.id')
->select('postings.doc_type as doc_type','postings.doc_no as doc_no','postings.post_date as post_date','partners.name as partner_name','postings.id as id','gl_accounts.title as title','posting_gls.amount as amount')
->where('posting_gls.amount','!=', 0)
->orderBy('postings.post_date','asc');
if(!Input::get('branch')){
if(Input::get('title')){
$headers->where('gl_accounts.id', Input::get('title'));
}
if(Input::get('entry_type')){
$headers->where('posting_gls.entry_type', Input::get('entry_type'));
}
if(Input::get('dr_date_from')){
$headers->where('postings.post_date','>=',$drdatefrom1);
}
if(Input::get('dr_date_to')){
$headers->where('postings.post_date','<=',$drdateto1);
}
}
else{
$headers->where('postings.cb', Input::get('branch'));
}
$header = $headers->get();
// return Response::json($header, 200, array(), JSON_PRETTY_PRINT);
$data = [];
$sum = ($sumdebit->amount > 0) ? $sumdebit->amount : 0;
$data[] = array('',sprintf('%08d', date('Y')),$drdatefrom,'',$title->title,($sumdebit->amount > 0) ? number_format($sumdebit->amount,2,'.',',') : 0,'',($sumdebit->amount > 0) ? number_format($sumdebit->amount,2,'.',',') : 0);
foreach($header as $headers){
$details = array($headers->doc_type,sprintf('%08d', $headers->doc_no),date('m/d/Y', strtotime($headers->post_date)),$headers->partner_name,$headers->title,'','','');
if($headers->amount > 0){
$details[5] = number_format($headers->amount,2,'.',',');
$details[6] = '-';
$sum += $headers->amount;
$details[7] = number_format($sum,2,'.',',');
}
else{
$details[5] = '-';
$details[6] = number_format($headers->amount * -1,2,'.',',');
$sum -= $headers->amount * -1;
$details[7] = number_format($sum,2,'.',',');
}
$data[] = $details;
}
ob_end_clean();
ob_start();
\Excel::create('Detailed Report per Account', function($excel) use($data,$branches, $drdatefrom, $drdateto, $title) {
$excel->sheet('Sheet1', function($sheet) use($data, $branches, $drdatefrom, $drdateto, $title) {
$sheet->mergeCells('A1:H1');
$sheet->row(1, function($row) {
$row->setFontWeight('bold')->setFontSize(18);
});
$sheet->row(2, function($row) {
$row->setFontWeight('bold');
});
$sheet->row(3, function($row) {
$row->setFontWeight('bold');
});
$sheet->row(5, function($row) {
$row->setFontWeight('bold');
});
$sheet->mergeCells('A2:H2');
$sheet->mergeCells('A3:H3');
$sheet->mergeCells('A4:H4');
$sheet->getStyle('A1')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A2')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A3')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->getStyle('A5:H5')->getAlignment()->applyFromArray(
array('horizontal' => 'center'));
$sheet->fromArray($data,null,'A5',true);
$sheet->row(1,array($branches->name));
$sheet->row(2,array('Detailed Report for'.' '.$title->title));
$sheet->row(3,array($drdatefrom.' '.'to'.' '.$drdateto));
$sheet->row(5,array('Doc Type','Doc No.','Date','Partner Name','Account Name','Debit','Credit','Balance'));
});
})->export('xls');
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment