sample

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
use App\BillSubDetail;
use App\SDDetail;
use App\BillDetail;
use App\WhTaxType;
use App\VatRate;
use App\BillHeader;
use App\AppDetail;
use App\AppHeader;

class AppDetail extends Model
{
    static function bill($id){
    	return BillHeader::where('id',$id)->first();
    }

    static function trapEwt($bill_id){
    	$billings = BillSubDetail::where('bill_sub_details.bill_id',$bill_id)
				->groupBy('bill_sub_details.dr_id')->get();
    	$trap = 0;
    	foreach ($billings as $billing) {
    		
    			$trap++;
    	
    	} 
    	return $trap;
    } 

    static function order($bill_id){
    	$billing = BillSubDetail::where('bill_sub_details.bill_id',$bill_id)->get();

    	foreach ($billing as $billings){
    		if($billings->dr_id){
    			$salesAcct = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->select('items.sales_acct as sales_acct')
								->first();
				if($salesAcct->sales_acct == 0 || !$salesAcct->sales_acct){
					$salesAcctSub = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
								->select('item_subcategories.sales_acct as sales_acct')
								->first();
					if($salesAcctSub->sales_acct == 0 || !$salesAcctSub->sales_acct){
						$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->leftJoin('item_categories','items.category_id','=','item_categories.id')
							->select('sd_details.item_id as item_id','item_categories.sales_acct as sales_acct',
									'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
							->get();
					}
					else{
						$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
							->select('sd_details.item_id as item_id','item_subcategories.sales_acct as sales_acct',
									'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
							->get();
					}
				}
				else{
					$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->select('sd_details.item_id as item_id','items.sales_acct as sales_acct',
									'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
							->get();
				}

    		}
    	}

    	$whtxrate = 0;
		$total_whtxrate = 0;
		$ewt = 0;
		$rate = 0;
		$total_ewt = 0;
		$total_ewt2 = 0;
		foreach($bill as $bills){
			$whtaxtype = WhTaxType::where('effective_date','<=',Carbon::now()->toDateString())
					->where('sales_acct',$bills->sales_acct)
					->orderBy('effective_date','desc')
					->first();

			$vat = VatRate::where('effective_date','<=',Carbon::now()->toDateString())->where('account',50)->orderBy('effective_date','desc')->first();
			if(!$whtaxtype){
				$whtxrate = 0;
			} 
			else{
				$whtxrate = $whtaxtype->rate;
			}

			if($bills->tax_type_id == 4){
				
			}
			else{
				if($bills->tax_type_id == 1){
					$vat_output = $vat->rate / 100;
    				$vat_x = 1 + $vat_output;
    				$vatsales = $bills->total_amt / $vat_x;
    				$wtx = $whtxrate / 100;
    				$rate = $vatsales * $wtx; 
					$ewt = $rate;
				}
				else{
					$wtx = $whtxrate / 100;
    				$rate = $bills->total_amt * $wtx; 
					$ewt = $rate;
				}
			}

			$total_ewt += $ewt;
		}
		return $total_ewt2 = $total_ewt;
    }

    static function project($bill_id){
    	$billings = BillSubDetail::where('bill_sub_details.bill_id',$bill_id)->get();
    	if(!$billings){
    		$salesAcct = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
							->where('bill_details.bill_id',$bill_id)
							->leftJoin('items','project_details.item_id','=','items.id')
							->select('items.sales_acct as sales_acct')
							->first();

			if($salesAcct->sales_acct <= 0){
				$salesAcctSub = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
								->where('bill_details.bill_id',$bill_id)
								->leftJoin('items','project_details.item_id','=','items.id')
								->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
								->select('item_subcategories.sales_acct as sales_acct')
								->first();
				if($salesAcctSub->sales_acct <= 0){
					$bill = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
								->where('bill_details.bill_id',$bill_id)
								->leftJoin('items','project_details.item_id','=','items.id')
								->leftJoin('item_categories','items.category_id','=','item_categories.id')
								->select('project_details.item_id as item_id','item_categories.sales_acct as sales_acct','bill_details.bill_id as bill_id',
									'project_details.tax_type_id as tax_type_id','project_details.total_amt as total_amt')->get();
				}
				else{
					$bill = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
								->where('bill_details.bill_id',$bill_id)
								->leftJoin('items','project_details.item_id','=','items.id')
								->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
								->select('project_details.item_id as item_id','item_subcategories.sales_acct as sales_acct','bill_details.bill_id as bill_id',
									'project_details.tax_type_id as tax_type_id','project_details.total_amt as total_amt')->get();
				}
			}
			else{
				$bill = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
						->where('bill_details.bill_id',$bill_id)
						->leftJoin('items','project_details.item_id','=','items.id')
						->select('project_details.item_id as item_id','items.sales_acct as sales_acct','bill_details.bill_id as bill_id',
							'project_details.tax_type_id as tax_type_id','project_details.total_amt as total_amt')->get();
			}
    	} 
    	else{
    		foreach ($billings as $billing) {
    			$salesAcct = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->select('items.sales_acct as sales_acct')
								->first();
				if($salesAcct->sales_acct <= 0){
						$salesAcctSub = SDDetail::where('sd_details.dr_id',$billings->dr_id)
									->leftJoin('items','sd_details.item_id','=','items.id')
									->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
									->select('item_subcategories.sales_acct as sales_acct')
									->first();
						if($salesAcctSub <= 0){
							$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->leftJoin('item_categories','items.category_id','=','item_categories.id')
								->select('sd_details.item_id as item_id','item_categories.sales_acct as sales_acct',
										'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
								->get();
						}
						else{
							$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
								->select('sd_details.item_id as item_id','item_subcategories.sales_acct as sales_acct',
										'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
								->get();
						}
					}
					else{
						$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->select('sd_details.item_id as item_id','items.sales_acct as sales_acct',
										'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
								->get();
					} 

    		}
    	}

    	$whtxrate = 0;
		$total_whtxrate = 0;
		$ewt = 0;
		$rate = 0;
		$total_ewt = 0;
		$total_ewt2 = 0;
		foreach($bill as $bills){
			$whtaxtype = WhTaxType::where('effective_date','<=',Carbon::now()->toDateString())
					->where('sales_acct',$bills->sales_acct)
					->orderBy('effective_date','desc')
					->first();

			$vat = VatRate::where('effective_date','<=',Carbon::now()->toDateString())->where('account',50)->orderBy('effective_date','desc')->first();
			if(!$whtaxtype){
				$whtxrate = 0;
			} 
			else{
				$whtxrate = $whtaxtype->rate;
			}

			if($bills->tax_type_id == 4){
				
			}
			else{
				if($bills->tax_type_id == 1){
					$vat_output = $vat->rate / 100;
    				$vat_x = 1 + $vat_output;
    				$vatsales = $bills->total_amt / $vat_x;
    				$wtx = $whtxrate / 100;
    				$rate = $vatsales * $wtx; 
					$ewt = $rate;
				}
				else{
					$wtx = $whtxrate / 100;
    				$rate = $bills->total_amt * $wtx; 
					$ewt = $rate;
				}
			}

			$total_ewt += $ewt;
		}
		return $total_ewt2 = $total_ewt;
    }

    static function ewt($bill_id,$bill_type){
    	$billing = BillSubDetail::where('bill_sub_details.bill_id',$bill_id)
				->groupBy('bill_sub_details.dr_id')->get();
		
		if($bill_type == "Order"){
			foreach($billing as $billings){
				$salesAcct = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->select('items.sales_acct as sales_acct')
							->first();
				if($salesAcct->sales_acct <= 0){
					$salesAcctSub = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
							->select('item_subcategories.sales_acct as sales_acct')
							->first();

					if($salesAcctSub->sales_acct <= 0){
						$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->leftJoin('item_categories','items.category_id','=','item_categories.id')
							->select('sd_details.item_id as item_id','item_categories.sales_acct as sales_acct',
									'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
							->get();
					}
					else{ 
						$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
							->select('sd_details.item_id as item_id','item_subcategories.sales_acct as sales_acct',
									'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
							->get();
					}
				}
				else{
					$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
							->leftJoin('items','sd_details.item_id','=','items.id')
							->select('sd_details.item_id as item_id','items.sales_acct as sales_acct',
									'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
							->get();
				}
			}
		}
		else{
			$billings = BillSubDetail::leftJoin('sd_details','bill_sub_details.dr_id','=','sd_details.dr_id')
				->where('bill_sub_details.bill_id',$bill_id)
				->leftJoin('items','sd_details.item_id','=','items.id')
				->select('sd_details.item_id as item_id','items.sales_acct as sales_acct','bill_sub_details.bill_id as bill_id',
						'sd_details.tax_type_id as tax_type_id')
				->groupBy('bill_sub_details.bill_id')->first();

			if(!$billings){
				$salesAcct = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
							->where('bill_details.bill_id',$bill_id)
							->leftJoin('items','project_details.item_id','=','items.id')
							->select('items.sales_acct as sales_acct')
							->first();
				if($salesAcct->sales_acct <= 0){
					$salesAcctSub = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
								->where('bill_details.bill_id',$bill_id)
								->leftJoin('items','project_details.item_id','=','items.id')
								->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
								->select('item_subcategories.sales_acct as sales_acct')
								->first();
					if($salesAcctSub->sales_acct <= 0){

						$bill = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
								->where('bill_details.bill_id',$bill_id)
								->leftJoin('items','project_details.item_id','=','items.id')
								->leftJoin('item_categories','items.category_id','=','item_categories.id')
								->select('project_details.item_id as item_id','item_categories.sales_acct as sales_acct','bill_details.bill_id as bill_id',
									'project_details.tax_type_id as tax_type_id','project_details.total_amt as total_amt')->get();
					}
					else{
						$bill = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
								->where('bill_details.bill_id',$bill_id)
								->leftJoin('items','project_details.item_id','=','items.id')
								->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
								->select('project_details.item_id as item_id','item_subcategories.sales_acct as sales_acct','bill_details.bill_id as bill_id',
									'project_details.tax_type_id as tax_type_id','project_details.total_amt as total_amt')->get();
					}
				}
				else{
					$bill = BillDetail::leftJoin('project_details','bill_details.ref_no','=','project_details.project_no')
						->where('bill_details.bill_id',$bill_id)
						->leftJoin('items','project_details.item_id','=','items.id')
						->select('project_details.item_id as item_id','items.sales_acct as sales_acct','bill_details.bill_id as bill_id',
							'project_details.tax_type_id as tax_type_id','project_details.total_amt as total_amt')->get();
				}
			}
			else{
				foreach ($billing as $billings){
					$salesAcct = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->select('items.sales_acct as sales_acct')
								->first();

					if($salesAcct->sales_acct <= 0){
						$salesAcctSub = SDDetail::where('sd_details.dr_id',$billings->dr_id)
									->leftJoin('items','sd_details.item_id','=','items.id')
									->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
									->select('item_subcategories.sales_acct as sales_acct')
									->first();
						if($salesAcctSub <= 0){
							$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->leftJoin('item_categories','items.category_id','=','item_categories.id')
								->select('sd_details.item_id as item_id','item_categories.sales_acct as sales_acct',
										'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
								->get();
						}
						else{
							$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->leftJoin('item_subcategories','items.subcategory_id','=','item_subcategories.id')
								->select('sd_details.item_id as item_id','item_subcategories.sales_acct as sales_acct',
										'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
								->get();
						}
					}
					else{
						$bill = SDDetail::where('sd_details.dr_id',$billings->dr_id)
								->leftJoin('items','sd_details.item_id','=','items.id')
								->select('sd_details.item_id as item_id','items.sales_acct as sales_acct',
										'sd_details.tax_type_id as tax_type_id','sd_details.total_amt as total_amt')
								->get();
					} 
				}
			}
		}

		$whtxrate = 0;
		$total_whtxrate = 0;
		$ewt = 0;
		$rate = 0;
		$total_ewt = 0;
		$total_ewt2 = 0;
		foreach($bill as $bills){
			$whtaxtype = WhTaxType::where('effective_date','<=',Carbon::now()->toDateString())
					->where('sales_acct',$bills->sales_acct)
					->orderBy('effective_date','desc')
					->first();

			$vat = VatRate::where('effective_date','<=',Carbon::now()->toDateString())->where('account',50)->orderBy('effective_date','desc')->first();
			if(!$whtaxtype){
				$whtxrate = 0;
			} 
			else{
				$whtxrate = $whtaxtype->rate;
			}

			if($bills->tax_type_id == 4){
				
			}
			else{
				if($bills->tax_type_id == 1){
					$vat_output = $vat->rate / 100;
    				$vat_x = 1 + $vat_output;
    				$vatsales = $bills->total_amt / $vat_x;
    				$wtx = $whtxrate / 100;
    				$rate = $vatsales * $wtx; 
					$ewt = $rate;
				}
				else{
					$wtx = $whtxrate / 100;
    				$rate = $bills->total_amt * $wtx; 
					$ewt = $rate;
				}
			}

			$total_ewt += $ewt;
		}
		return $total_ewt2 = $total_ewt;

    }

    static function prev_amt($app_id,$bill_id){
    	return AppDetail::join('app_headers','app_details.header_id','=','app_headers.id')
    					->where('app_details.bill_id',$bill_id)
    					->where('app_details.header_id','<',$app_id)
    					->whereIn('app_headers.status', ['Approved','Printed'])
    					->sum('app_details.amount');
    }
}