<?php

namespace App\Http\Controllers\backend;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use App\Models\Bank;
use App\Models\Transaction;
use App\Models\Purchase;
use App\Models\Supplier;
use App\Models\Customer;
use App\Models\CustomerAccount;
use App\Models\SupplierAccount;
use App\Models\Sale;
use App\Models\SaleDetail;
use App\Models\PurchaseDetail;
use App\Models\SaleReturnDetail;
use App\Models\PurchaseReturnDetail;
use App\Models\Product;
use App\Models\CashClosing;
use App\Models\Expense;
use DB;


class LedgerController extends Controller
{
    /**
    * Role and permission implement by ****** 
    * >>>>>> Md Azharul Islam
    * >>>>>> azharul787@gmail.com
    *>>>>>>> Software Developer
    */
   /* function __construct()
    {
        $this->middleware('permission:customer-ledger', ['only' => ['cledgeri','cledgers']]);
        $this->middleware('permission:supplier-ledger',['only'=>['sledgeri','sledgers']]);
        $this->middleware('permission:bank-balance',['only'=>['bankbalance']]);
        $this->middleware('permission:profit-balance',['only'=>['ipri','iprs','ppri','pprs']]);
        $this->middleware('permission:income-expense', ['only' => ['income_expense','income_expenses']]);
        $this->middleware('permission:daily-summary', ['only' => ['daily_summary']]);

    }*/
    /*-----------daily ledger report section----------*/
    public function ledger(){
        $today = date('Y-m-d');

        // $cpayments = DB::select("SELECT c.name,ca.customer_id,ca.sale_id,
        //                             SUM(IF(s.sale_date = '$today', s.grand_total_price, 0)) AS total_sale,
        //                             SUM(IF(s.sale_date = '$today', s.payment, 0)) AS payment,
        //                             SUM(IF(s.sale_date = '$today', s.due_amount, 0)) AS due,
        //                             SUM(IF(ca.payment_date = '$today', ca.amount, 0)) AS total_payment,
        //                             SUM(IF(ca.payment_date = '$today', ca.amount, 0)) - SUM(IF(s.sale_date = '$today', s.payment, 0)) AS pdp
        //                             FROM customers c,customer_accounts ca, sales s
        //                             WHERE ca.sale_id = s.id 
        //                             AND ca.customer_id = c.id AND ca.payment_date = '$today'
        //                         GROUP BY ca.sale_id");
        $spayment = SupplierAccount::where('payment_date',date('Y-m-d'))->sum('amount');
        $cpayment = CustomerAccount::where('payment_date',date('Y-m-d'))->sum('amount');
        
        $sales = Sale::with('customer')->where('sale_date',$today)->latest()->get();

        //$deposits = Transaction::with('bank')->where('transaction_date',$today)->where('transaction_status','Deposit')->get();
        $transactions = Transaction::with('bank')->where('transaction_date',$today)->get();
        $expenses = Expense::with('expensetype')->where('expense_date',$today)->get();
        $lastday_cash = CashClosing::whereDate('closing_date', '<=', $today)->latest('closing_date')->first();
        
        return view('backend.ledger.ledger',compact('sales','cpayment','transactions','expenses','spayment','lastday_cash'));
    }
    /*--------------customer ledger index section---------------*/
    public function cledgeri(){
        $transactions = '';
        return view('backend.admin.ledger.customer_ledger',compact('transactions'));
    }
    /*--------------customer ledger search section---------------*/
    public function cledgers(Request $request){
        $from_date =  date('Y-m-d',strtotime($request->from_date));
        $to_date =  date('Y-m-d',strtotime($request->to_date)) ;
        $customer_id = $request->customer_id; 

        
        $customer = Customer::find($customer_id);
        $transactions = CustomerAccount::where('customer_id',$customer_id)
                                        ->whereDate('ca_date', '>=', $from_date)
                                        ->whereDate('ca_date', '<=', $to_date)
                                        ->where('amount', '>',0)
                                        ->get();

        return view('backend.ledger.customer_ledger',compact('customer','transactions','from_date','to_date')); 
    }
     /*--------------Supplier ledger index section---------------*/
    public function sledgeri(){
        $transactions = '';
        return view('backend.ledger.supplier_ledger',compact('transactions'));
    }
    /*-------------- Supplier ledger search section---------------*/
    public function sledgers(Request $request){
        $from_date =  date('Y-m-d',strtotime($request->from_date));
        $to_date =  date('Y-m-d',strtotime($request->to_date)) ;
        $supplier_id = $request->supplier_id; 

        
        $supplier = Supplier::find($supplier_id);
        $transactions = SupplierAccount::where('supplier_id',$supplier_id)
                                        ->whereDate('pay_date', '>=', $from_date)
                                        ->whereDate('pay_date', '<=', $to_date)
                                        ->where('amount', '>',0)
                                        ->get();

        return view('backend.ledger.supplier_ledger',compact('supplier','transactions','from_date','to_date')); 
    }
    
    /*-----------total bank balance section-------------------*/
    public function bankbalance(Request $request){
        
        $cbalances = DB::select("SELECT b.*, tr.bank_id, 
                            SUM(IF(tr.transaction_status = 'Deposit', tr.transaction_amount, 0)) AS total_deposit,
                            SUM(IF(tr.transaction_status = 'Withdraw', tr.transaction_amount, 0)) AS total_withdraw, 
                            SUM(IF(tr.transaction_status = 'Loan', tr.transaction_amount, 0)) AS total_loan 
                        FROM banks b,transactions tr
                        WHERE tr.bank_id = b.id
                        GROUP BY tr.bank_id
                    ");
        return view('backend.ledger.bank_balance',compact('cbalances'));

    }
    /*-------------Invoice wise profit report-----------------*/
    public function ipri(){
        $invoices = DB::select("SELECT DISTINCT sales.id, sales.invoice_no FROM `sales` ");
        
        $from_date =  date('Y-m-01') ;
        $to_date = date('Y-m-d');
        $ty = 'All' ;

        $sales = Sale::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
        //return $sales;
        return view('backend.ledger.profit.invoice_report',compact('invoices','sales','ty','from_date','to_date'));
        
    }
    /*--------------------Invoice wise profit resport search-------------------------*/
    public function iprs(Request $request){
        $invoices = DB::select("SELECT DISTINCT sales.id, sales.invoice_no FROM `sales` ");

        $from_date =  date('Y-m-d',strtotime($request->from_date)) ;
        $to_date =  date('Y-m-d',strtotime($request->to_date)) ;

        if($request->invoice_no === "All"){
            $ty = $request->invoice_no ;
              $sales = Sale::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
           }
           else{
               $ty =  $request->invoice_no;

              $sales = Sale::where('invoice_no',$request->invoice_no)
                         // ->whereDate('sale_date', '>=', $from_date)
                        //  ->whereDate('sale_date', '<=', $to_date)
                          ->get();
              }
        return view('backend.ledger.profit.invoice_report',compact('invoices','sales','ty','from_date','to_date'));
        
    }

    /*-------------product wise profit report-----------------*/
    public function ppri(){
        $products = DB::select("SELECT DISTINCT sale_details.product_id, products.product_name,products.code FROM products,`sale_details` WHERE products.id = sale_details.product_id ");
        $from_date =  date('Y-m-01') ;
        $to_date = date('Y-m-d');
        $ty = 'All' ;

        $sales = SaleDetail::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
          
        return view('backend.ledger.profit.product_report',compact('products','sales','ty','from_date','to_date'));

    }
    /*--------------------product wise profit resport search-------------------------*/
    public function pprs(Request $request){
        $products = DB::select("SELECT DISTINCT sale_details.product_id, products.product_name,products.code FROM products,`sale_details` WHERE products.id = sale_details.product_id ");
        $from_date =  date('Y-m-d',strtotime($request->from_date));
        $to_date =  date('Y-m-d',strtotime($request->to_date));

        if($request->product_id === "All"){
            $ty = $request->product_id ;
              $sales = SaleDetail::whereDate('sale_date', '>=', $from_date)->whereDate('sale_date', '<=', $to_date)->get();
           }
           else{
               $pro = Product::find($request->product_id);
               $ty =  $pro->product_name;

              $sales = SaleDetail::where('product_id',$request->product_id)
                          ->whereDate('sale_date', '>=', $from_date)
                          ->whereDate('sale_date', '<=', $to_date)
                          ->get();
              }
        return view('backend.ledger.profit.product_report',compact('products','sales','ty','from_date','to_date'));
    }
    /*---------------income expense report section----------------*/
    public function income_expense(){
        $today = date('Y-m-d');
        $from =  date('Y-m-d');
        $to = date('Y-m-d');
        $user_id = Auth::id();
        $type = 'Details';
        
        //return $user_id;
        //DELETE FROM income_expenses WHERE user_id = $user_id
        $deleted = DB::delete('delete from income_expenses where user_id = '.$user_id);
        
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, income_amount ) SELECT $user_id, ca.payment_date, c.name, ca.amount FROM customer_accounts AS ca, customers AS c WHERE c.id = ca.customer_id AND ca.payment_date = '$today' AND ca.amount != '0.00';");
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, expense_amount ) SELECT $user_id, e.expense_date, t.type_name, e.expense_amount FROM expenses as e, expensetypes as t WHERE t.id = e.expensetype_id AND e.expense_date = '$today';");
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, expense_amount ) SELECT $user_id, sa.payment_date, s.name, sa.amount FROM suppliers AS s, supplier_accounts AS sa WHERE s.id = sa.supplier_id AND sa.payment_date = '$today' AND sa.amount != '0.00';");
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, expense_amount ) SELECT $user_id, lo.transaction_date, ln.loaner_name, lo.debit FROM loaners as ln, loans as lo WHERE ln.id = lo.loaner_id AND lo.transaction_date = '$today' AND debit != '0.00';");
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, income_amount ) SELECT $user_id, lo.transaction_date, ln.loaner_name, lo.credit FROM loaners as ln, loans as lo WHERE ln.id = lo.loaner_id AND lo.transaction_date = '$today' AND credit != '0.00';");
                    
        $data = DB::select("SELECT * FROM income_expenses WHERE user_id = $user_id");

        return view('backend.ledger.income_expense',compact('data','type','from','to'));
    }
    public function income_expenses(Request $request){
        $from =  date('Y-m-d',strtotime($request->from_date));
        $to =  date('Y-m-d',strtotime($request->to_date));
        $type = $request->type;
        $user_id = Auth::id();
       // DELETE FROM income_expenses WHERE user_id = $user_id;
        $deleted = DB::delete('delete from income_expenses where user_id = '.$user_id);
        
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, income_amount ) SELECT $user_id, ca.payment_date, c.name, ca.amount FROM customer_accounts AS ca, customers AS c WHERE c.id = ca.customer_id AND ca.payment_date >=  '$from'  AND ca.payment_date <= '$to' AND ca.amount != '0.00';");
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, expense_amount ) SELECT $user_id,  e.expense_date, t.type_name, e.expense_amount FROM expenses as e, expensetypes as t WHERE t.id = e.expensetype_id AND e.expense_date >= '$from' AND e.expense_date <= '$to';");
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, expense_amount ) SELECT $user_id, sa.payment_date, s.name, sa.amount FROM suppliers AS s, supplier_accounts AS sa WHERE s.id = sa.supplier_id AND sa.payment_date >= '$from' AND sa.payment_date <= '$to' AND sa.amount != '0.00';"); 
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, expense_amount ) SELECT $user_id, lo.transaction_date, ln.loaner_name, lo.debit FROM loaners as ln, loans as lo WHERE ln.id = lo.loaner_id AND lo.transaction_date >= '$from' AND lo.transaction_date <= '$to' AND debit != '0.00';");
        DB::insert("INSERT INTO income_expenses( user_id, transaction_date, description, income_amount ) SELECT $user_id, lo.transaction_date, ln.loaner_name, lo.credit FROM loaners as ln, loans as lo WHERE ln.id = lo.loaner_id AND lo.transaction_date >= '$from' AND lo.transaction_date <= '$to' AND credit != '0.00';");
                    
        if($request->type == 'Details' ){
           $data = DB::select("SELECT * FROM income_expenses WHERE user_id = $user_id ORDER BY transaction_date ");
        }
        if($request->type == 'Daily' ){
            $data = DB::select("SELECT transaction_date,description, SUM(income_amount) AS income_amount ,SUM(expense_amount) AS expense_amount FROM income_expenses WHERE user_id = $user_id GROUP BY DATE(transaction_date)");
        }
        if($request->type == 'Monthly' ){
            $data = DB::select("SELECT transaction_date,description, SUM(income_amount) AS income_amount ,SUM(expense_amount) AS expense_amount FROM income_expenses WHERE user_id = $user_id GROUP BY MONTH(transaction_date)");
        }
        if($request->type == 'Yearly' ){
            $data = DB::select("SELECT transaction_date,description, SUM(income_amount) AS income_amount,SUM(expense_amount) AS expense_amount FROM income_expenses WHERE user_id = $user_id GROUP BY YEAR(transaction_date)");
        }
       
        return view('backend.ledger.income_expense',compact('data','type','from','to'));

    }
    /*----------------daily summary-----------------------*/
    public function daily_summary(Request $request){
        /*---------delete 0 amount---------------*/
        SupplierAccount::where('amount',0)->delete();
        CustomerAccount::where('amount',0)->delete();
        if($request->summary_date != ''){
            $today = date('Y-m-d',strtotime($request->summary_date));
        }else{
            $today = date('Y-m-d');
        }
        
        
        $customerac =   DB::select("SELECT SUM(ca.amount) AS c_amount, COUNT(ca.amount) AS c_a FROM customer_accounts AS ca WHERE ca.payment_date = '$today'");
        $sum = array();
        foreach($customerac as $ca){
            $sum[] = ['description' => 'Customer Payment', 'transaction' => $ca->c_a, 'income' => $ca->c_amount, 'expense' => 0 ];
        }
        $sa =   DB::select("SELECT SUM(sa.amount) AS s_amount, COUNT(sa.amount) AS s_a FROM supplier_accounts AS sa WHERE sa.payment_date = '$today'");
        foreach($sa as $s){
            $sum[] = ['description' => 'Supplier Payment', 'transaction' => $s->s_a, 'income' => 0, 'expense' => $s->s_amount ];
        }
        $exp = DB::select("SELECT  SUM(ex.expense_amount) AS ex_amount, count(ex.expense_amount) AS ex_a FROM expenses AS ex WHERE ex.expense_date = '$today'");
        foreach($exp as $ex){
            $sum[] = ['description' => 'Expense', 'transaction' => $ex->ex_a, 'income' => 0, 'expense' => $ex->ex_amount ];
        }
        /*$loans =  DB::select("SELECT SUM(lo.debit) AS l_debit, COUNT(lo.id) AS l_d, SUM(lo.credit) AS l_credit FROM loans AS lo WHERE lo.transaction_date = '$today'");
        foreach($loans as $lo){
            $sum[] = ['description' => 'Loan', 'transaction' => $lo->l_d, 'income' => $lo->l_credit, 'expense' => $lo->l_debit ];
        }*/
        $banks =  DB::select("SELECT SUM(IF(transaction_status = 'Deposit', transaction_amount, 0)) AS b_debit,  SUM(IF(transaction_status = 'Withdraw', transaction_amount, 0)) AS b_credit ,COUNT(bt.id) AS btc FROM transactions AS bt WHERE bt.transaction_date = '$today'");
        foreach($banks as $bn){
            $sum[] = ['description' => 'Bank Transaction', 'transaction' => $bn->btc, 'income' => $bn->b_credit, 'expense' => $bn->b_debit ];
        }
        //return $sum;
        return view('backend.ledger.summary',compact('sum','today'));
    }
    /*----------daily summary details request from ajax-------------*/
    public function summaryDetails(Request $request){
        $table = $request->table;
        $today = date('Y-m-d',strtotime($request->sdate));
        
        if($table == 'Customer Payment'){
            $data =   DB::select("SELECT c.name as name,ca.amount AS credit FROM customers c,customer_accounts AS ca WHERE c.id = ca.customer_id and ca.payment_date = '$today'");
        }
        if($table == 'Supplier Payment'){
            $data =   DB::select("SELECT s.name as name,sa.amount AS debit FROM suppliers s,supplier_accounts AS sa WHERE s.id = sa.supplier_id and sa.payment_date = '$today'");
        }
        if($table == 'Expense'){
            $data =   DB::select("SELECT t.type_name as name,ex.expense_amount AS debit FROM expensetypes AS t,expenses AS ex WHERE t.id = ex.expensetype_id and ex.expense_date = '$today'");
        }
        if($table == 'Loan'){
            $data =   DB::select("SELECT loa.loaner_name as name,lo.debit AS debit, lo.credit as credit FROM loaners as loa, loans as lo  WHERE loa.id = lo.loaner_id and lo.transaction_date = '$today'");
        }
        if($table == 'Bank Transaction'){
            $data =   DB::select("SELECT b.bank_name as name, IF(bt.transaction_status = 'Deposit', bt.transaction_amount, 0) AS debit, IF(bt.transaction_status = 'Withdraw', bt.transaction_amount, 0) AS credit FROM banks b, transactions bt WHERE b.id = bt.bank_id and bt.transaction_date = '$today'");
        }
        return response()->json($data);
    }
    /*----------cash flow section------------------*/
    public function cash_flow(Request $request){
        if($request->summary_date != ''){
            $today = date('Y-m-d',strtotime($request->summary_date));
        }else{
            $today = date('Y-m-d');
        }
        $sum = array();
        $suppliers = DB::select("SELECT  SUM(s.amount) AS amount, count(s.amount) AS s_a FROM supplier_accounts AS s WHERE  s.payment_date = '$today'");
        foreach($suppliers as $sp){
            $sum[] = ['description' => 'Supplier Payment', 'total' => $sp->amount, 'income' => 0, 'expense' => $sp->amount ];
        }
        $customers = DB::select("SELECT  SUM(c.amount) AS amount, count(c.amount) AS c_a FROM customer_accounts AS c WHERE  c.payment_date = '$today'");
        foreach($customers as $ca){
            $sum[] = ['description' => 'Customer Payment', 'total' => $ca->amount, 'income' => $ca->amount, 'expense' => 0 ];
        }
        $exp = DB::select("SELECT  SUM(ex.expense_amount) AS ex_amount, count(ex.expense_amount) AS ex_a FROM expenses AS ex WHERE  ex.expense_date = '$today'");
        foreach($exp as $ex){
            $sum[] = ['description' => 'Expense', 'total' => $ex->ex_amount, 'income' => 0, 'expense' => $ex->ex_amount ];
        }
       /* $loans =  DB::select("SELECT SUM(lo.debit) AS l_debit, COUNT(lo.id) AS l_d, SUM(lo.credit) AS l_credit FROM loans AS lo WHERE lo.transaction_date = '$today'");
        foreach($loans as $lo){
            $sum[] = ['description' => 'Borrows', 'total' => ($lo->l_credit + $lo->l_debit) , 'income' => $lo->l_credit, 'expense' => $lo->l_debit ];
        }*/
        $banks =  DB::select("SELECT SUM(IF(transaction_status = 'Deposit', transaction_amount, 0)) AS b_debit,  SUM(IF(transaction_status = 'Withdraw', transaction_amount, 0)) AS b_credit FROM transactions AS bt WHERE  bt.transaction_date = '$today'");
        foreach($banks as $bn){
            $sum[] = ['description' => 'Bank Transaction', 'total' => (($bn->b_credit) + ($bn->b_debit)), 'income' => $bn->b_credit, 'expense' => $bn->b_debit ];
        }

        /*-----lastday cash-----------*/
        $lastday_cash = CashClosing::whereDate('closing_date', '<=', date('Y-m-d',strtotime("- 1 day".$today)))->latest('closing_date')->first();

        $new = array() ;

        foreach ($sum as $traffic){
            $key = $traffic['description'] ;
            if (isset($new[$key])){
                if ($new[$key]['description'] === $traffic['description']){
                $new[$key]['total'] += $traffic['total'];
                $new[$key]['income'] += $traffic['income'];
                $new[$key]['expense'] += $traffic['expense'];
                }
            } else {
                $new[$key] = $traffic ;
            }
        }

        return view('backend.ledger.cash_flow',compact('new','lastday_cash','today'));
    }
    public function profit_loss(Request $request){
        if($request->from_date != '' && $request->to_date != ''){
            $from =  date('Y-m-d',strtotime($request->from_date));
            $to = date('Y-m-d',strtotime($request->to_date));
        }else{
            $from =  date('Y-m-d');
            $to = date('Y-m-d');
        }
        $sum = array();

        $expenses =  DB::select("SELECT t.type_name,SUM(e.expense_amount) as amount FROM expenses e,expensetypes t WHERE e.expensetype_id = t.id AND e.expense_date >= '$from' AND e.expense_date <= '$to' GROUP BY e.expensetype_id");
        foreach($expenses as $ex){
            $sum[] = ['income_description' => '', 'income' => 0,'expense_description' => $ex->type_name, 'expense' => $ex->amount ];
        }
        /*$purchases =  DB::select("SELECT c.name,SUM(e.expense_amount) as amount FROM expenses e,expensetypes t WHERE e.expensetype_id = t.id AND e.expense_date >= '$from' AND e.expense_date <= '$to' GROUP BY e.expensetype_id");
        foreach($expenses as $purchase){
            $sum[] = ['income_description' => '', 'income' => 0,'expense_description' => $purchase->type_name, 'expense' => $purchase->amount ];
        }
        $sales =  DB::select("SELECT t.type_name,SUM(e.expense_amount) as amount FROM expenses e,expensetypes t WHERE e.expensetype_id = t.id AND e.expense_date >= '$from' AND e.expense_date <= '$to' GROUP BY e.expensetype_id");
        foreach($sales as $sale){
            $sum[] = ['income_description' => '', 'income' => 0,'expense_description' => $sale->type_name, 'expense' => $sale->amount ];
        }*/
        /*-----lastday cash-----------*/
       // $lastday_cash = CashClosing::whereDate('closing_date', '<=', date('Y-m-d',strtotime("- 1 day".$today)))->latest('closing_date')->first();

        $new = array() ;

        foreach ($sum as $traffic){
            $key = $traffic['expense_description'] ;
            if (isset($new[$key])){
                if ($new[$key]['expense_description'] === $traffic['expense_description']){
                //$new[$key]['total'] += $traffic['total'];
                $new[$key]['income'] += $traffic['income'];
                $new[$key]['expense'] += $traffic['expense'];
                }
            } else {
                $new[$key] = $traffic ;
            }
        }

        return view('backend.ledger.profit_loss',compact('new','from','to'));
    }
}
