@php
$totalOpeningBalnace = 0;
$totalPurchase = 0;
$totalAdjustmentIn = 0;
$totalTransferIn = 0;
$totalSale = 0;
$totalAdjustmentOut = 0;
$totalTransferOut = 0;
$totalClosing = 0;
$totalDepOpeningBalance = 0;
$totalCurrentDep = 0;
$totalDisposalAmount = 0;
$totalDepTransferIn = 0;
$totalDepTransferOut = 0;
$totalDepAdjustment = 0;
$totalDepClosingBalance = 0;
$totalWrittenDown = 0;
@endphp
@foreach ($allGroups as $group)
@php
$gOpeningBalance = 0;
$gPurchase = 0;
$gAdjustmentIn = 0;
$gTransferIn = 0;
$gSale = 0;
$gAdjustmentOut = 0;
$gTransferOut = 0;
$gClosingBalance = 0;
$gDepOpeningBalance = 0;
$gCurrentDep = 0;
$gDepDisposalAmount = 0;
$gDepTransferIn = 0;
$gDepTransferOut = 0;
$gDepAdjustment = 0;
$gDepClosingBalance = 0;
$gWrittenDownValue = 0;
@endphp
|
{{ $group->name }} |
@php
$tCategories = DB::table('fams_product_category')
->where('productGroupId', $group->id)
->get();
@endphp
@foreach ($tCategories as $tCategory)
@php
$cOpeningBalance = 0;
$cPurchase = 0;
$cAdjustmentIn = 0;
$cTransferIn = 0;
$cSale = 0;
$cAdjustmentOut = 0;
$cTransferOut = 0;
$cClosingBalance = 0;
$cDepOpeningBalance = 0;
$cCurrentDep = 0;
$cDepDisposalAmount = 0;
$cDepTransferIn = 0;
$cDepTransferOut = 0;
$cDepAdjustment = 0;
$cDepClosingBalance = 0;
$cWrittenDownValue = 0;
$cDepPercentage = 0;
@endphp
@foreach ($branchIds as $branchId)
@php
$transferedAllProduct = DB::table('fams_tra_transfer as t1')
->join('fams_product as t2', 't1.productId', '=', 't2.id')
->where(function ($query) use ($branchId)
{
$query->where('branchIdFrom', $branchId)
->orWhere('branchIdTo', $branchId);
})
// ->where('t1.branchIdFrom', $branchId)
// ->where('purchaseDate', '>=', $startDate)
->where('purchaseDate', '<=', $endDate)
->where('t2.categoryId', $tCategory->id)
->whereIn('t2.projectId', $projectId)
->whereIn('t2.projectTypeId', $projectTypeId)
->pluck('productId');
$productsOfthisCategory = DB::table('fams_product')
// ->where('id', 4711)
->where('categoryId', $tCategory->id)
->where('branchId', $branchId)
->where('purchaseDate', '<=', $endDate)
->whereIn('projectId', $projectId)
->whereIn('projectTypeId', $projectTypeId)
->orWhereIn('id', $transferedAllProduct)
->orderBy('purchaseDate', 'asc')
->get();
@endphp
@foreach ($productsOfthisCategory as $product)
@php
$transferedOutProduct = DB::table('fams_tra_transfer')
->where('productId', $product->id)
->where('branchIdFrom', $branchId)
->where('transferDate', '>', $startDate)
->get();
$transferedInProduct = DB::table('fams_tra_transfer')
->where('productId', $product->id)
->where('branchIdTo', $branchId)
->where('transferDate', '>', $endDate)
->get();
$depDisposalamount = 0;
$saleAmount = 0;
$adjustmentIn = 0;
$adjustmentOut = 0;
$transferInAmount = 0;
$transferOutAmount = 0;
$openingDisposalamount = 0;
$openingDepTransferInAmount = 0;
$openingDepTransferOutAmount = 0;
$openingDepAdjustment = 0;
@endphp
@if ($branchId == $product->branchId || sizeof($transferedOutProduct) > 0)
@if (sizeof($transferedInProduct) == 0)
@php
$branchName = DB::table('gnr_branch')
->where('id', $branchId)
->value('name');
$purchaseDate = date('Y-m-d', strtotime($product->purchaseDate));
//Get the Additional Charge After Start Date
$postAddCharge = (float) DB::table('fams_additional_charge')
->where('branchId', $branchId)
->where('productId', $product->id)
->where('purchaseDate', '>=', $startDate)
->where('purchaseDate', '<=', $endDate)
->sum('amount');
if ($purchaseDate < $startDate) {
$additionalCharge = (float) DB::table('fams_additional_charge')
->where('branchId', $branchId)
->where('productId', $product->id)
->where('purchaseDate', '<', $startDate)
->sum('amount');
$soldOut = DB::table('fams_sale')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('createdDate', '<', $startDate)
->first();
if ($soldOut) {
$saleAmount = $soldOut->amount;
$adjustmentIn = $soldOut->profitAmount;
$adjustmentOut = round(
(float) $soldOut->lossAmount +
(float) DB::table('fams_depreciation_details')
->where('depTo', '<', $startDate)
->where('productId', $product->id)
->sum('amount') +
(float) DB::table('fams_product')
->where('id', $product->id)
->value('depreciationOpeningBalance'),
2
);
}
//If product is write offed (for opening balance)
$writeOffed = DB::table('fams_write_off')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('createdDate', '<', $startDate)
->first();
if ($writeOffed) {
$adjustmentOut = round(
(float) $additionalCharge +
DB::table('fams_product')
->where('id', $product->id)
->value('totalCost'),
2
);
$openingDisposalamount = round((float) $writeOffed->amount, 2);
$openingDepAdjustment =
(float) $product->totalCost +
(float) DB::table('fams_additional_charge')
->where('productId', $product->id)
->where('purchaseDate', '<', $startDate)
->sum('amount');
}
//If product Transfered In Then get Tranfer Amount (for opening balance)
$isTransferdInProduct = DB::table('fams_tra_transfer')
->where('productId', $product->id)
->where('branchIdTo', $branchId)
->where('transferDate', '<', $startDate)
->orderBy('id', 'desc')
->first();
if ($isTransferdInProduct) {
$transferInAmount =
(float) $product->totalCost +
(float) DB::table('fams_additional_charge')
->where('productId', $product->id)
->where('purchaseDate', '<', $isTransferdInProduct->transferDate)
->sum('amount');
$openingDepTransferInAmount = $isTransferdInProduct->pastDep;
}
$isTransferdOutProduct = DB::table('fams_tra_transfer')
->where('productId', $product->id)
->where('branchIdFrom', $branchId)
->where('transferDate', '<', $startDate)
->orderBy('id', 'desc')
->first();
if ($isTransferdOutProduct) {
$transferOutAmount =
(float) $product->totalCost +
(float) DB::table('fams_additional_charge')
->where('productId', $product->id)
->where('purchaseDate', '<', $isTransferdOutProduct->transferDate)
->sum('amount');
$openingDepTransferOutAmount = round(
(float) DB::table('fams_depreciation_details')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('depTo', '<', $startDate)
->sum('amount'),
2
);
}
if ($isTransferdInProduct || $isTransferdOutProduct) {
$openingBalance = $adjustmentIn - $saleAmount - $adjustmentOut + $transferInAmount - $transferOutAmount;
}
else{
$openingBalance = (float) $product->totalCost + $additionalCharge + $adjustmentIn - $saleAmount - $adjustmentOut + $transferInAmount - $transferOutAmount;
}
$purchase = $postAddCharge;
}
// end for opening balance
else {
$openingBalance = 0;
$additionalCharge = (float) DB::table('fams_additional_charge')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('purchaseDate', '>=', $startDate)
->where('purchaseDate', '<=', $endDate)
->sum('amount');
$purchase = (float) $product->totalCost + $additionalCharge;
}
//If product is sold out
$soldOut = DB::table('fams_sale')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('createdDate', '>=', $startDate)
->where('createdDate', '<=', $endDate)
->first();
if ($soldOut) {
$saleAmount = $soldOut->amount;
$adjustmentIn = $soldOut->profitAmount;
//$adjustmentOut = Dep. Generated + Loss amount
$adjustmentOut = round(
(float) $soldOut->lossAmount +
(float) DB::table('fams_depreciation_details')
->where('productId', $product->id)
->where('depTo', '<', $endDate)
->sum('amount') +
(float) DB::table('fams_product')
->where('id', $product->id)
->value('depreciationOpeningBalance'),
2
);
//$depAdjustment = round(DB::table('fams_depreciation_details')->where('productId',$product->id)->where('depTo','>=',$startDate)->where('depTo','<=',$endDate)->sum('amount'),2);
$depDisposalamount = 0;
} else {
$saleAmount = 0;
$adjustmentIn = 0;
$adjustmentOut = 0;
//If product is write offed
$writeOffed = DB::table('fams_write_off')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('createdDate', '>=', $startDate)
->where('createdDate', '<=', $endDate)
->first();
if ($writeOffed) {
//$adjustmentOut = $writeOffed->lossAmount;
$adjustmentOut = round(
(float) $additionalCharge +
DB::table('fams_product')
->where('id', $product->id)
->value('totalCost'),
2
);
/*$depDisposalamount = round((float) DB::table('fams_write_off')->where('productId',$product->id)->where('createdDate','>=',$startDate)->where('createdDate','<=',$endDate)->value('amount'),2);*/
$depDisposalamount = round((float) $writeOffed->amount, 2);
//$depAdjustment = round((float) $additionalCharge + DB::table('fams_product')->where('id',$product->id)->value('totalCost'),2);
} else {
$adjustmentOut = 0;
$depDisposalamount = 0;
//$depAdjustment = 20;
}
}
//If Transfered Out Then get old product Code and Tranfer Amount
$isTransferdOutProduct = DB::table('fams_tra_transfer')
->where('productId', $product->id)
->where('branchIdFrom', $branchId)
->where('transferDate', '>=', $startDate)
->where('transferDate', '<=', $endDate)
->orderBy('id', 'desc')
->first();
if ($isTransferdOutProduct) {
$productCode = $isTransferdOutProduct->oldProductCode;
$transferOutAmount =
(float) $product->totalCost +
(float) DB::table('fams_additional_charge')
->where('productId', $product->id)
->where('purchaseDate', '<', $isTransferdOutProduct->transferDate)
->sum('amount');
} else {
$productCode = $product->productCode;
$transferOutAmount = 0;
}
//If product Transfered In Then get Tranfer Amount
$isTransferdInProduct = DB::table('fams_tra_transfer')
->where('productId', $product->id)
->where('branchIdTo', $branchId)
->where('transferDate', '>=', $startDate)
->where('transferDate', '<=', $endDate)
->orderBy('id', 'desc')
->first();
if ($isTransferdInProduct) {
$transferInAmount =
(float) $product->totalCost +
(float) DB::table('fams_additional_charge')
->where('productId', $product->id)
->where('purchaseDate', '<', $isTransferdInProduct->transferDate)
->sum('amount');
$openingBalance = 0;
$purchase = 0;
} else {
$transferInAmount = 0;
}
$closingBalance = round($openingBalance + $purchase + $adjustmentIn + $transferInAmount - $saleAmount - $adjustmentOut - $transferOutAmount, 2);
/////// Dep ///////
$openingAccDep = round(
$product->depreciationOpeningBalance +
(float) DB::table('fams_depreciation_details')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('depTo', '<', $startDate)
->sum('amount'),
2
);
$openingAccDep += $openingDisposalamount + $openingDepTransferInAmount - $openingDepAdjustment;
$currentAccDep = round(
(float) DB::table('fams_depreciation_details')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('depTo', '>=', $startDate)
->where('depTo', '<=', $endDate)
->sum('amount'),
2
);
// dd($branchId);
//If product is sold out
$soldOut = DB::table('fams_sale')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('createdDate', '>=', $startDate)
->where('createdDate', '<=', $endDate)
->first();
if ($soldOut) {
$depAdjustment = $openingAccDep + $currentAccDep;
} else {
//If product is write offed
$writeOffed = DB::table('fams_write_off')
->where('productId', $product->id)
->where('branchId', $branchId)
->where('createdDate', '>=', $startDate)
->where('createdDate', '<=', $endDate)
->first();
if ($writeOffed) {
$depAdjustment =
(float) $product->totalCost +
(float) DB::table('fams_additional_charge')
->where('productId', $product->id)
->sum('amount');
} else {
$depAdjustment = 0;
}
}
//If product is Transfered In
if ($isTransferdInProduct) {
$openingAccDep = 0;
$depTransferInAmount = (float) DB::table('fams_tra_transfer')
->where('branchIdTo', $branchId)
->where('productId', $product->id)
->where('transferDate', '>=', $startDate)
->where('transferDate', '<=', $endDate)
->value('pastDep');
} else {
$depTransferInAmount = 0;
}
//If product Is transfered Out
if ($isTransferdOutProduct) {
$depTransferOutAmount = $openingAccDep + $currentAccDep;
} else {
$depTransferOutAmount = 0;
}
$depTransferAmount = $depTransferOutAmount + $depTransferInAmount;
$depClosingBalance = round($openingAccDep + $currentAccDep + $depDisposalamount + $depTransferInAmount - $depAdjustment - $depTransferOutAmount, 2);
@endphp
@php
$cOpeningBalance = $cOpeningBalance + $openingBalance;
$cPurchase = $cPurchase + $purchase;
$cAdjustmentIn = $cAdjustmentIn + $adjustmentIn;
$cTransferIn = $cTransferIn + $transferInAmount;
$cSale = $cSale + $saleAmount;
$cAdjustmentOut = $cAdjustmentOut + $adjustmentOut;
$cTransferOut = $cTransferOut + $transferOutAmount;
$cClosingBalance = $cClosingBalance + $closingBalance;
$cDepOpeningBalance = $cDepOpeningBalance + $openingAccDep;
$cCurrentDep = $cCurrentDep + $currentAccDep;
$cDepDisposalAmount = $cDepDisposalAmount + $depDisposalamount;
$cDepTransferIn = $cDepTransferIn + $depTransferInAmount;
$cDepTransferOut = $cDepTransferOut + $depTransferOutAmount;
$cDepAdjustment = $cDepAdjustment + $depAdjustment;
$cDepClosingBalance = $cDepClosingBalance + $depClosingBalance;
$cWrittenDownValue = $cWrittenDownValue + $closingBalance - $depClosingBalance;
$cDepPercentage = $product->depreciationPercentage;
@endphp
@endif
@endif
@endforeach {{-- End Product Foreach --}}
@endforeach {{-- For Branch --}}
|
{{ $tCategory->name }} |
{{ dotPrint($cOpeningBalance) }} |
{{ dotPrint($cPurchase) }} |
{{ dotPrint($cAdjustmentIn) }} |
{{ dotPrint($cTransferIn) }} |
{{ dotPrint($cSale) }} |
{{ dotPrint($cAdjustmentOut) }} |
{{ dotPrint($cTransferOut) }} |
{{ dotPrint($cClosingBalance) }} |
@if ($cDepPercentage > 0)
{{ $cDepPercentage }}@else{{ '-' }}@endif
|
{{ dotPrint($cDepOpeningBalance) }} |
{{ dotPrint($cCurrentDep) }} |
{{ dotPrint($cDepDisposalAmount) }} |
{{ dotPrint($cDepTransferIn) }} |
{{ dotPrint($cDepTransferOut) }} |
{{ dotPrint($cDepAdjustment) }} |
{{ dotPrint($cDepClosingBalance) }} |
{{ dotPrint($cWrittenDownValue) }} |
@php
$gOpeningBalance = $gOpeningBalance + $cOpeningBalance;
$gPurchase = $gPurchase + $cPurchase;
$gAdjustmentIn = $gAdjustmentIn + $cAdjustmentIn;
$gTransferIn = $gTransferIn + $cTransferIn;
$gSale = $gSale + $cSale;
$gAdjustmentOut = $gAdjustmentOut + $cAdjustmentOut;
$gTransferOut = $gTransferOut + $cTransferOut;
$gClosingBalance = $gClosingBalance + $cClosingBalance;
$gDepOpeningBalance = $gDepOpeningBalance + $cDepOpeningBalance;
$gCurrentDep = $gCurrentDep + $cCurrentDep;
$gDepDisposalAmount = $gDepDisposalAmount + $cDepDisposalAmount;
$gDepTransferIn = $gDepTransferIn + $cDepTransferIn;
$gDepTransferOut = $gDepTransferOut + $cDepTransferOut;
$gDepAdjustment = $gDepAdjustment + $cDepAdjustment;
$gDepClosingBalance = $gDepClosingBalance + $cDepClosingBalance;
$gWrittenDownValue = $gWrittenDownValue + $cWrittenDownValue;
@endphp
@endforeach {{-- End Category Foreach --}}
| {{ 'Sub Total' }}
|
{{ dotPrint($gOpeningBalance) }} |
{{ dotPrint($gPurchase) }} |
{{ dotPrint($gAdjustmentIn) }} |
{{ dotPrint($gTransferIn) }} |
{{ dotPrint($gSale) }} |
{{ dotPrint($gAdjustmentOut) }} |
{{ dotPrint($gTransferOut) }} |
{{ dotPrint($gClosingBalance) }} |
|
{{ dotPrint($gDepOpeningBalance) }} |
{{ dotPrint($gCurrentDep) }} |
{{ dotPrint($gDepDisposalAmount) }} |
{{ dotPrint($gDepTransferIn) }} |
{{ dotPrint($gDepTransferOut) }} |
{{ dotPrint($gDepAdjustment) }} |
{{ dotPrint($gDepClosingBalance) }} |
{{ dotPrint($gWrittenDownValue) }} |
@php
$totalOpeningBalnace = $totalOpeningBalnace + $gOpeningBalance;
$totalPurchase = $totalPurchase + $gPurchase;
$totalAdjustmentIn = $totalAdjustmentIn + $gAdjustmentIn;
$totalTransferIn = $totalTransferIn + $gTransferIn;
$totalSale = $totalSale + $gSale;
$totalAdjustmentOut = $totalAdjustmentOut + $gAdjustmentOut;
$totalTransferOut = $totalTransferOut + $gTransferOut;
$totalClosing = $totalClosing + $gClosingBalance;
$totalDepOpeningBalance = $totalDepOpeningBalance + $gDepOpeningBalance;
$totalCurrentDep = $totalCurrentDep + $gCurrentDep;
$totalDisposalAmount = $totalDisposalAmount + $gDepDisposalAmount;
$totalDepTransferIn = $totalDepTransferIn + $gDepTransferIn;
$totalDepTransferOut = $totalDepTransferOut + $gDepTransferOut;
$totalDepAdjustment = $totalDepAdjustment + $gDepAdjustment;
$totalDepClosingBalance = $totalDepClosingBalance + $gDepClosingBalance;
$totalWrittenDown = $totalWrittenDown + $gWrittenDownValue;
@endphp
@endforeach {{-- End Group Foreach --}}
| {{ 'Grand Total' }} |
{{ dotPrint($totalOpeningBalnace) }} |
{{ dotPrint($totalPurchase) }} |
{{ dotPrint($totalAdjustmentIn) }} |
{{ dotPrint($totalTransferIn) }} |
{{ dotPrint($totalSale) }} |
{{ dotPrint($totalAdjustmentOut) }} |
{{ dotPrint($totalTransferOut) }} |
{{ dotPrint($totalClosing) }} |
|
{{ dotPrint($totalDepOpeningBalance) }} |
{{ dotPrint($totalCurrentDep) }} |
{{ dotPrint($totalDisposalAmount) }} |
{{ dotPrint($totalDepTransferIn) }} |
{{ dotPrint($totalDepTransferOut) }} |
{{ dotPrint($totalDepAdjustment) }} |
{{ dotPrint($totalDepClosingBalance) }} |
{{ dotPrint($totalWrittenDown) }} |