000

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Session;
use DB;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\DashboardReportExport;
use App\Exports\DashboardPOReportExport;
use DateTime;
use App\Exports\DashboardGraphReportExport;

class DashboardController extends Controller
{
	public function index(){
		$data = array();
		$data['companies'] = \App\Company::where('is_active', 1)->get();

$year = date('Y');
	 	$month = date('m');
	 
	 	if($month > 3){
	    $session = substr($year, 2).(substr($year, 2)+1);
	 	} else{
	    $session = (substr($year, 2)-1).substr($year, 2);
	 	}
			
    //PR which are approved but PO not generated  (final approved (directior approval final) PR count)  
		$pr_data = \App\PurchaseRequestItem::join('purchase_requests as pr', 'pr.id', '=', 'purchase_request_items.pr_id')
	    ->where('pr.session', $session)
			->where('pr.status', 'LIKE', 'Approved')
	    ->groupBy('purchase_request_items.pr_id')
	    ->havingRaw('SUM(rqty) > 0')
	    ->get();

$data['open_pr_count'] = count($pr_data);

//approved PR jinka PO process start ho gya hai  //  all approved
		$data['total_pr_count'] = \App\PurchaseRequest::where('session', $session)->where('status', 'LIKE', 'Approved')->count();
		
        //total of approved(director approval) PR amount ( PR basic amount) exclding tax
		$data['total_pr_amount'] = $this->convertToLacks(round( \App\PurchaseRequest::join('purchase_request_items as pri', 'pri.pr_id', 'purchase_requests.id')->where('session', $session)->where('purchase_requests.status', 'approved')->sum(DB::raw('pri.rate*pri.qty'))));
		
		//unapproved pr count (status requested)
		$data['total_un_app_pr_count'] = \App\PurchaseRequest::where('status', 'requested')->where('session', $session)->count();

//po approved mrn partially done or not done
		$po_data = \App\PurchaseOrderItem::join('purchase_orders as po', 'po.id', '=', 'purchase_order_items.po_id')
		->where('po.session', $session)
		->where('po.status', 'LIKE', 'Approved')
		->groupBy('purchase_order_items.po_id')
		->havingRaw('SUM(qty) > SUM(rqty)')
		->get();

//po not approved (status requested)
		$data['total_un_app_po_count'] = \App\PurchaseOrder::where('status', 'requested')->where('session', $session)->count();
		
		//Total of approved(director approval) PO amount ( PO basic amount) excluding tax
		$data['total_po_amount'] = $this->convertToLacks(round( \App\PurchaseOrder::join('purchase_order_items as poi', 'poi.po_id', 'purchase_orders.id')->where('purchase_orders.status', 'Approved')->where('session', $session)->sum(DB::raw('poi.rate*poi.qty'))));				
		
		$data['total_mrn_count'] = \App\Irn::where('current_session', $session)->count();

// $data['total_mrn_amount'] = $this->convertToLacks(round( \App\Irn::join('irn_items', 'irn_items.irn_id', 'irns.id')->where('session', $session)->sum(DB::raw('irn_items.rate*irn_items.qty'))));

// $data['total_mrn_amount'] = $this->convertToLacks(round( \App\Irn::join('irn_items', 'irn_items.irn_id', 'irns.id')->join('purchase_orders', 'purchase_orders.id', 'irn_items.po_id')->where('irns.current_session', $session)->sum(DB::raw('irn_items.rate*irn_items.qty'))));

$data['total_mrn_amount'] = $this->convertToLacks(round( \App\Irn::join('irn_items', 'irn_items.irn_id', 'irns.id')->where('irns.current_session', $session)->sum(DB::raw('irn_items.rate*irn_items.qty'))));

$data['total_invoice_count'] = \App\IrnPayment::count();
		
		// $data['total_supliers'] = \App\Vendor::where('is_active',1)->count();
		$data['total_supliers'] = \App\PurchaseOrder::join('vendors', 'vendors.id', 'purchase_orders.vendor_id')->where('vendors.is_active',1)->where('purchase_orders.session', $session)->distinct('purchase_orders.vendor_id')->count('purchase_orders.vendor_id');
		$data['sessions'] = \App\Session::where('is_active',1)->orderBy('id','desc')->get();

if (!empty($data['total_po_count']) && !empty($data['total_mrn_count'])) {
         $data['mrn_by_po_count'] = round(($data['total_po_count'] * 100) / $data['total_mrn_count'], 2);
        } else {
           $data['mrn_by_po_count'] = 0;
        }

return view('dashboard.index',$data);
		
	}

public function getDashboardSessionData(Request $request) {
		$data = array();
		
		if($request->type == 'session') {
			$session = substr($request->session, 2, 2);
			$year = '20'.$session;
			$session = substr($request->session, 2,2).substr($request->session, 7,2);

//PR which are approved but PO id partially done or not done  (final approved (directior approval final) PR count)  											
			$pr_data = \App\PurchaseRequestItem::join('purchase_requests as pr', 'pr.id', '=', 'purchase_request_items.pr_id')
	    ->where('pr.session', $session)
			->where('pr.status', 'LIKE', 'Approved')
	    ->groupBy('purchase_request_items.pr_id')
	    ->havingRaw('SUM(rqty) > 0')
	    ->get();

$data['open_pr_count'] = count($pr_data);
			
			//approved PR jinka PO process start ho gya hai   // all approved
			$data['total_pr_count'] = \App\PurchaseRequest::where('session', $session)->where('status', 'LIKE', 'Approved')->count();
			
					//total of approved(director approval) PR amount ( PR basic amount) exclding tax
			$data['total_pr_amount'] = $this->convertToLacks(round( \App\PurchaseRequest::join('purchase_request_items as pri', 'pri.pr_id', 'purchase_requests.id')->where('session', $session)->where('purchase_requests.status', 'LIKE','Approved')->sum(DB::raw('pri.rate*pri.qty'))));
			
			//unapproved pr count (status requested)
			$data['total_un_app_pr_count'] = \App\PurchaseRequest::where('status', 'requested')->where('session', $session)->count();			
			
			//po approved mrn partially done or not done
			$po_data = \App\PurchaseOrderItem::join('purchase_orders as po', 'po.id', '=', 'purchase_order_items.po_id')
	    ->where('po.session', $session)
			->where('po.status', 'LIKE', 'Approved')
	    ->groupBy('purchase_order_items.po_id')
	    ->havingRaw('SUM(qty) > SUM(rqty)')
	    ->get();

$data['open_po_count'] = count($po_data);			
			
			//total approval po count (final director approval ) 
			$data['total_po_count'] = \App\PurchaseOrder::where('status', 'approved')->where('session', $session)->count();
			
			//Total of approved(director approval) PO amount ( PO basic amount) excluding tax
			$data['total_po_amount'] = $this->convertToLacks(round( \App\PurchaseOrder::leftjoin('purchase_order_items as poi', 'poi.po_id', 'purchase_orders.id')->where('purchase_orders.status', 'Approved')->where('session', $session)->sum(DB::raw('poi.rate*poi.qty'))));
			
			//po not approved (status requested)
			$data['total_un_app_po_count'] = \App\PurchaseOrder::where('status', 'requested')->where('session', $session)->count();
			
			$data['total_mrn_count'] = \App\Irn::where('current_session', $session)->count();

// $data['total_mrn_amount'] = $this->convertToLacks(round( \App\Irn::join('irn_items', 'irn_items.irn_id', 'irns.id')->join('purchase_orders', 'purchase_orders.id', 'irn_items.po_id')->where('purchase_orders.session', $session)->sum(DB::raw('irn_items.rate*irn_items.qty')), 2));

$data['total_mrn_amount'] = $this->convertToLacks(round( \App\Irn::join('irn_items', 'irn_items.irn_id', 'irns.id')->join('purchase_orders', 'purchase_orders.id', 'irn_items.po_id')->where('irns.current_session', $session)->sum(DB::raw('irn_items.rate*irn_items.qty'))));

$data['total_invoice_count'] = \App\IrnPayment::count();
			
			$data['total_supliers'] = \App\PurchaseOrder::join('vendors', 'vendors.id', 'purchase_orders.vendor_id')->where('vendors.is_active',1)->where('purchase_orders.session', $session)->distinct('purchase_orders.vendor_id')->count('purchase_orders.vendor_id');

//dd(\DB::getQueryLog($data['total_supliers']));
			$data['sessions'] = \App\Session::where('is_active',1)->orderBy('id','desc')->get();

if($data['total_po_count'] != 0) {
				$data['mrn_by_po_count'] = round(($data['total_po_count']*100)/$data['total_mrn_count'], 2);
			} else {
					$data['mrn_by_po_count'] = 0;
			}
			
			return response()->json($data);
		}
	}

public function getDashboardQuarterData(Request $request) {
		$data = array();
		
		if($request->type == 'quarter') {
				$session = substr($request->session, 2, 2);
				$year = '20'.$session;
				$session = substr($request->session, 2,2).substr($request->session, 7,2);
			 // $session = $request->session;

if($request->quarter == 1) {
				$quarter_start = $year.'-04-01';
				$quarter_end = $year.'-06-30';
			} elseif($request->quarter == 2) {
				$quarter_start = $year.'-07-01';
				$quarter_end = $year.'-09-30';
			} elseif($request->quarter == 3) {
				$quarter_start = $year.'-10-01';
				$quarter_end = $year.'-12-31';
			} else {
				$quarter_start = ($year+1).'-01-01';
				$quarter_end = ($year+1).'-03-31';
			}
		
			//PR which are approved but PO not generated  (final approved (directior approval final) PR count)  			
			$query = \App\PurchaseRequestItem::query();
			$query->join('purchase_requests as pr', 'pr.id', '=', 'purchase_request_items.pr_id');

if(isset($request->quarter) && $request->quarter != '') {
				$query->whereBetween(DB::raw('date(pr.pr_date)'),[$quarter_start,$quarter_end]);
			}
			$pr_data = $query->select('pr_number')->where('pr.session', $session)
			->where('pr.status', 'LIKE', 'Approved')
	    ->groupBy('purchase_request_items.pr_id')
	    ->havingRaw('SUM(rqty) > 0')
	    ->get();			
			
			$data['open_pr_count'] = count($pr_data);		
			
			//approved PR jinka PO process start ho gya hai // all approved
			$data['total_pr_count'] = \App\PurchaseRequest::where('session', $session)->whereBetween(DB::raw('date(purchase_requests.pr_date)'),[$quarter_start,$quarter_end])->where('status', 'LIKE', 'Approved')->count();
			
					//total of approved(director approval) PR amount ( PR basic amount) exclding tax
			$data['total_pr_amount'] = $this->convertToLacks(round( \App\PurchaseRequest::join('purchase_request_items as pri', 'pri.pr_id', 'purchase_requests.id')->where('session', $session)->whereBetween(DB::raw('date(purchase_requests.pr_date)'),[$quarter_start,$quarter_end])->where('purchase_requests.status', 'LIKE', 'Approved')->sum(DB::raw('pri.rate*pri.qty'))));
			
			//unapproved pr count (status requested)
			$data['total_un_app_pr_count'] = \App\PurchaseRequest::where('status', 'requested')->where('session', $session)->whereBetween(DB::raw('date(purchase_requests.pr_date)'),[$quarter_start,$quarter_end])->count();			
						
			//po approved mrn partially done or not done
			$po_data = \App\PurchaseOrderItem::join('purchase_orders as po', 'po.id', '=', 'purchase_order_items.po_id')
			->where('po.session', $session)
			->where('po.status', 'LIKE', 'Approved')
			->whereBetween(DB::raw('date(po.po_date)'),[$quarter_start,$quarter_end])
			->groupBy('purchase_order_items.po_id')
			->havingRaw('SUM(qty) > SUM(rqty)')
			->get();

$data['open_po_count'] = count($po_data);

//total approval po count (final director approval ) 
			$data['total_po_count'] = \App\PurchaseOrder::where('status', 'approved')->where('session', $session)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$quarter_start,$quarter_end])->count();
			
			//Total of approved(director approval) PO amount ( PO basic amount) excluding tax
			$data['total_po_amount'] = $this->convertToLacks(round( \App\PurchaseOrder::leftjoin('purchase_order_items as poi', 'poi.po_id', 'purchase_orders.id')->where('purchase_orders.status', 'Approved')->where('session', $session)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$quarter_start,$quarter_end])->sum(DB::raw('poi.rate*poi.qty'))));
			
			//po not approved (status requested)
			$data['total_un_app_po_count'] = \App\PurchaseOrder::where('status', 'requested')->where('session', $session)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$quarter_start,$quarter_end])->count();
			
			$data['total_mrn_count'] = \App\Irn::where('current_session', $session)->whereBetween(DB::raw('date(irns.created_at)'),[$quarter_start,$quarter_end])->count();

$data['total_invoice_count'] = \App\IrnPayment::count();

//\DB::enableQueryLog();
			$data['total_supliers'] = \App\PurchaseOrder::join('vendors', 'vendors.id', 'purchase_orders.vendor_id')->where('vendors.is_active',1)->where('purchase_orders.session', $session)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$quarter_start,$quarter_end])->distinct('purchase_orders.vendor_id')->count('purchase_orders.vendor_id');

//dd(\DB::getQueryLog($data['total_supliers']));
			$data['sessions'] = \App\Session::where('is_active',1)->orderBy('id','desc')->get();

if($data['total_po_count'] != 0 && $data['total_mrn_count'] != 0) {
				$data['mrn_by_po_count'] = round(($data['total_po_count']*100)/$data['total_mrn_count'], 2);
			} else {
					$data['mrn_by_po_count'] = 0;
			}
			
			return response()->json($data);
		}
	}

public function openPrCount(Request $request) {
		return Excel::download(new DashboardReportExport('open_pr_count', $request->all()), 'open-pr-count.xlsx');
	}

public function totalPrCount(Request $request) {
		return Excel::download(new DashboardReportExport('total_pr_count', $request->all()), 'total-pr-count.xlsx');
	}

public function totalUnAppPrCount(Request $request) {
		return Excel::download(new DashboardReportExport('total_un_app_pr_count', $request->all()), 'total-un-app-pr-count.xlsx');
	}
	
	public function totalPrAmount(Request $request) {
		return Excel::download(new DashboardReportExport('total_pr_amount', $request->all()), 'total-pr-amount.xlsx');
	}

public function totalMRNPOCount(Request $request) {
		return Excel::download(new DashboardPOReportExport('mrn_po_count', $request->all()), 'mrn-po-count.xlsx');
	}

public function openPoCount(Request $request) {
		return Excel::download(new DashboardPOReportExport('open_po_count', $request->all()), 'open-po-count.xlsx');
	}

public function totalPoCount(Request $request) {
		return Excel::download(new DashboardPOReportExport('total_po_count', $request->all()), 'total-po-count.xlsx');
	}

public function totalUnAppPoCount(Request $request) {		
		return Excel::download(new DashboardPOReportExport('total_un_app_po_count', $request->all()), 'total-un-app-po-count.xlsx');
	}

public function totalPoAmount(Request $request) {		
		return Excel::download(new DashboardPOReportExport('total_po_amount', $request->all()), 'total-po-amount.xlsx');
	}

public function totalMRNAmount(Request $request) {		
		return Excel::download(new DashboardPOReportExport('total_mrn_amount', $request->all()), 'total-mrn-amount.xlsx');
	}

public function totalSuppliers(Request $request) {		
		return Excel::download(new DashboardPOReportExport('total_suppliers', $request->all()), 'total-suppliers.xlsx');
	}

//graph excel download code start
	public function totalPurchaseItemCategory(Request $request) {		
		return Excel::download(new DashboardGraphReportExport('total_purchase_item_category', $request->all()), 'total-purchase-item-category.xlsx');
	}

public function totalPurchaseDepartment(Request $request) {
		return Excel::download(new DashboardGraphReportExport('total_purchase_department', $request->all()), 'total-purchase-department.xlsx');
	}
	
	public function totalPurchaseCircle(Request $request) {
		return Excel::download(new DashboardGraphReportExport('total_purchase_circle', $request->all()), 'total-purchase-circle.xlsx');
	}

public function topFiveSuppliers(Request $request) {
		return Excel::download(new DashboardGraphReportExport('top_five_supplier', $request->all()), 'top-five-supplier.xlsx');
	}

public function topFiveItems(Request $request) {
		return Excel::download(new DashboardGraphReportExport('top_five_items', $request->all()), 'top-five-items.xlsx');
	}

public function po_categoryWise(Request $request) {

if($request->type == 'po_categoryWise') {
			
      if($request->item_cat_week!=null){
		
				$now = new DateTime();
				$weekStart = $now->modify($request->item_cat_week)->format('Y-m-d');
				$weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
				//dd($weekStart,$weekEnd);
				$fy = $request->item_cat_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('category.id','category.name as category_name', DB::raw('sum(purchase_order_items.qty) as category__qty_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->orderBy('po_amount', 'desc')->get()->toArray();
				// usort($data, function($a, $b) {
				// 	return $b['category_count'] - $a['category_count'];
				// });
	
			}else if($request->item_cat_month!=null){
		
				$month = $request->item_cat_month;
			
				$fy = $request->item_cat_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('category.id','category.name as category_name', \DB::raw('sum(purchase_order_items.qty) as category__qty_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->orderBy('po_amount', 'desc')->get()->toArray();
				// usort($data, function($a, $b) {
				// 	return $b['category_count'] - $a['category_count'];
				// });
	
			}else if($request->item_cat_quarterly!=null){
		
				$quarter = $request->item_cat_quarterly;
				$fyear = $request->item_cat_fy;
				if($quarter==1){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
				}else if($quarter==2){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
				}else if($quarter==3){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
				}else if($quarter==4){
					$start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
					$end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
				}
				
				$fy = $request->item_cat_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('category.id','category.name as category_name', DB::raw('sum(purchase_order_items.qty) as category__qty_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->orderBy('po_amount', 'desc')->get()->toArray();
				// usort($data, function($a, $b) {
				// 	return $b['category_count'] - $a['category_count'];
				// });
	
			}else{
	
				$fy = $request->item_cat_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('category.id','category.name as category_name', DB::raw('sum(purchase_order_items.qty) as category__qty_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('items', 'items.code', 'purchase_order_items.item_code')->join('category', 'category.id', 'items.category')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->groupBy('items.category')->where('purchase_orders.session',$fy)->orderBy('po_amount', 'desc')->get()->toArray();

// /	dd($data);
				// usort($data, function($a, $b) {
				// 	return $b['category_count'] - $a['category_count'];
				// });
			}

return response()->json($data);
                 
    }	  
	}
	public function po_departmentWise(Request $request) {
		
    if($request->type == 'po_departmentWise') {
			
      if($request->department_week!=null){
				
				$now = new DateTime();
				$weekStart = $now->modify($request->department_week)->format('Y-m-d');
				$weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
			
				$fy = $request->department_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('purchase_orders.id', 'purchase_order_items.po_id')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->orderBy('po_amount', 'DESC')->get()->toArray();

// usort($data, function($a, $b) {
				// 	return $b['department_count'] - $a['department_count'];
				// });
			
			}else if($request->department_month!=null){
				
				$month = $request->department_month;
			
				$fy = $request->department_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];

$data = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->orderBy('po_amount', 'DESC')->get()->toArray();

// usort($data, function($a, $b) {
				// 	return $b['department_count'] - $a['department_count'];
				// });
			
			}else if($request->department_quarterly!=null){
				
				$quarter = $request->department_quarterly;
				$fyear = $request->department_fy;
				if($quarter==1){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
				}else if($quarter==2){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
				}else if($quarter==3){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
				}else if($quarter==4){
					$start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
					$end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
				}
				
				$fy = $request->department_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->orderBy('po_amount', 'DESC')->get()->toArray();

// usort($data, function($a, $b) {
				// 	return $b['department_count'] - $a['department_count'];
				// });
			
			}else{
			
				$fy = $request->department_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('department.id as department_id','department.name as department_name', DB::raw('sum(purchase_order_items.qty) as department_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_requests','purchase_requests.id','purchase_order_items.pr_id')->join('purchase_orders', 'purchase_orders.id', 'purchase_order_items.po_id')->join('department', 'department.id', 'purchase_requests.department')->groupBy('purchase_requests.department')->where('purchase_requests.session',$fy)->orderBy('po_amount', 'DESC')->get()->toArray();
				
				// usort($data, function($a, $b) {
				// 	return $b['department_count'] - $a['department_count'];
				// });
			}
		
			return response()->json($data);
        
    }	  
	}
	public function po_circleWise(Request $request) {
	
    if($request->type == 'po_circleWise') {
			
      if($request->circle_week!=null){
				
				$now = new DateTime();
				$weekStart = $now->modify($request->circle_week)->format('Y-m-d');
				$weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
			
				$fy = $request->circle_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('circles.id as circle_id','circles.name as circle_name', DB::raw('sum(purchase_order_items.qty) as circle_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('circles', 'circles.id', 'purchase_orders.circle')->groupBy('purchase_orders.circle')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->get()->toArray();

usort($data, function($a, $b) {
					return $b['po_amount'] - $a['po_amount'];
				});
			
			}else if($request->circle_month!=null){
				
				$month = $request->circle_month;
			
				$fy = $request->circle_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];

$data = \App\PurchaseOrderItem::select('circles.id as circle_id','circles.name as circle_name', DB::raw('sum(purchase_order_items.qty) as circle_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('circles', 'circles.id', 'purchase_orders.circle')->groupBy('purchase_orders.circle')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->get()->toArray();

usort($data, function($a, $b) {
					return $b['po_amount'] - $a['po_amount'];
				});
			
			}else if($request->circle_quarterly!=null){
				
				$quarter = $request->circle_quarterly;
				$fyear = $request->circle_fy;
				if($quarter==1){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
				}else if($quarter==2){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
				}else if($quarter==3){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
				}else if($quarter==4){
					$start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
					$end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
				}
				
				$fy = $request->circle_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('circles.id as circle_id','circles.name as circle_name', DB::raw('sum(purchase_order_items.qty) as circle_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('circles', 'circles.id', 'purchase_orders.circle')->groupBy('purchase_orders.circle')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->get()->toArray();

usort($data, function($a, $b) {
					return $b['po_amount'] - $a['po_amount'];
				});
			
			}else{
			
				$fy = $request->circle_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data = \App\PurchaseOrderItem::select('circles.id as circle_id','circles.name as circle_name', DB::raw('sum(purchase_order_items.qty) as circle_count'), \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as po_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('circles', 'circles.id', 'purchase_orders.circle')->groupBy('purchase_orders.circle')->where('purchase_orders.session',$fy)->get()->toArray();
				usort($data, function($a, $b) {
					return $b['po_amount'] - $a['po_amount'];
				});
				
			}
		
			return response()->json($data);
        
    }	  
	}

public function po_supplierWise(Request $request) {
	
    if($request->type == 'po_supplierWise') {
			
      if($request->supplier_week!=null){
				
				$now = new DateTime();
				$weekStart = $now->modify($request->supplier_week)->format('Y-m-d');
				$weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
			
				$fy = $request->supplier_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data=[];
				$vendors = \App\PurchaseOrderItem::select('purchase_orders.vendor_id as vendor_id','vendors.vendor_name as vendor_name')->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('vendors','vendors.id','purchase_orders.vendor_id')->groupBy('purchase_orders.vendor_id')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->get()->toArray();
				
				foreach($vendors as $key=>$vendor){
					$po_amount = \App\PurchaseOrder::where('session',$fy)->where('vendor_id',$vendor['vendor_id'])->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->sum('po_amount');
					$temArr['vendor_name']= $vendor['vendor_name'];
					$temArr['po_amount']= $po_amount;
					$data[$key]= $temArr;
				}
			
				usort($data, function($a, $b) {
					return $b['po_amount'] - $a['po_amount'];
				});

}else if($request->supplier_month!=null){
				
				$month = $request->supplier_month;
			
				$fy = $request->supplier_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];

$data=[];
				$vendors = \App\PurchaseOrderItem::select('purchase_orders.vendor_id as vendor_id','vendors.vendor_name as vendor_name', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as total_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('vendors','vendors.id','purchase_orders.vendor_id')->groupBy('purchase_orders.vendor_id')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->orderBy('total_amount', 'desc')->skip(0)->take(5)->get()->toArray();
				
				foreach($vendors as $key=>$vendor){
					$po_amount = \App\PurchaseOrder::where('session',$fy)->where('vendor_id',$vendor['vendor_id'])->whereMonth('purchase_orders.po_date','=',$month)->sum('po_amount');
					$temArr['vendor_name']= $vendor['vendor_name'];
					$temArr['po_amount']= $po_amount;
					$data[$key]= $temArr;
				}
			
				// usort($data, function($a, $b) {
				// 	return $b['po_amount'] - $a['po_amount'];
				// });

}else if($request->supplier_quarterly!=null){
			
				$quarter = $request->supplier_quarterly;
				$fyear = $request->supplier_fy;
				if($quarter==1){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
				}else if($quarter==2){					
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";					
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";					
				}else if($quarter==3){
					$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
					$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
				}else if($quarter==4){
					$start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
					$end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
				}
				
				$fy = $request->supplier_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				
				$data=[];
				$vendors = \App\PurchaseOrderItem::select('purchase_orders.vendor_id as vendor_id','vendors.vendor_name as vendor_name', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as total_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('vendors','vendors.id','purchase_orders.vendor_id')->groupBy('purchase_orders.vendor_id')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->orderBy('total_amount', 'desc')->skip(0)->take(5)->get()->toArray();
								
				foreach($vendors as $key=>$vendor){
					$po_amount = \App\PurchaseOrder::where('session',$fy)->where('vendor_id',$vendor['vendor_id'])->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->sum('po_amount');
					$temArr['vendor_name']= $vendor['vendor_name'];
					$temArr['po_amount']= $po_amount;
					$data[$key]= $temArr;
				}
			
				// usort($data, function($a, $b) {
				// 	return $b['po_amount'] - $a['po_amount'];
				// });

}else{
			
				$fy = $request->supplier_fy;
				$fy = $fy[2].$fy[3].$fy[7].$fy[8];
				$data=[];
				$vendors = \App\PurchaseOrderItem::select('purchase_orders.vendor_id as vendor_id','vendors.vendor_name as vendor_name', \DB::raw('sum(purchase_order_items.qty*purchase_order_items.rate) as total_amount'))->join('purchase_orders','purchase_orders.id','purchase_order_items.po_id')->join('vendors','vendors.id','purchase_orders.vendor_id')->groupBy('purchase_orders.vendor_id')->where('purchase_orders.session',$fy)->orderBy('total_amount', 'desc')->skip(0)->take(5)->get()->toArray();
				
				foreach($vendors as $key=>$vendor){
					$po_amount = \App\PurchaseOrder::where('session',$fy)->where('vendor_id',$vendor['vendor_id'])->sum('po_amount');
					$temArr['vendor_name']= $vendor['vendor_name'];
					$temArr['po_amount']= $po_amount;
					$data[$key]= $temArr;
				}
			
				// usort($data, function($a, $b) {
				// 	return $b['po_amount'] - $a['po_amount'];
				// });
				
			}
		
			return response()->json($data);
        
    }	  
	}

public function po_itemWise(Request $request) {
	
		if($request->type == 'po_itemWise') {
	
			if($request->item_week!=null){
		
			$now = new DateTime();
			$weekStart = $now->modify($request->item_week)->format('Y-m-d');
			$weekEnd = $now->modify('this week +6 days')->format('Y-m-d');
		
			$fy = $request->item_fy;
			$fy = $fy[2].$fy[3].$fy[7].$fy[8];
			
			$data=[];
			$items_data = \App\PurchaseOrderItem::select('purchase_order_items.item_code as item_code', \DB::raw('sum(purchase_order_items.qty) as total_item_qty'), 'items.description as item_name')->join('purchase_orders','purchase_orders.id', 'purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$weekStart,$weekEnd])->groupBy('purchase_order_items.item_code')->get()->toArray();
			
			foreach($items_data as $key=>$vendor){
					$temArr['vendor_name']= $vendor['item_name'];
				$temArr['po_amount']= $vendor['total_item_qty'];
				$data[$key]= $temArr;
			}
		
			usort($data, function($a, $b) {
				return $b['po_amount'] - $a['po_amount'];
			});

}else if($request->item_month!=null){
			
			$month = $request->item_month;
		
			$fy = $request->item_fy;
			$fy = $fy[2].$fy[3].$fy[7].$fy[8];
			
			$data=[];
			
			$items_data = \App\PurchaseOrderItem::select('purchase_order_items.item_code as item_code', \DB::raw('sum(purchase_order_items.qty) as total_item_qty'), 'items.description as item_name')->join('purchase_orders','purchase_orders.id', 'purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->where('purchase_orders.session',$fy)->whereMonth('purchase_orders.po_date','=',$month)->groupBy('purchase_order_items.item_code')->get()->toArray();
		
			foreach($items_data as $key=>$vendor){
				$temArr['vendor_name']= $vendor['item_name'];
				$temArr['po_amount']= $vendor['total_item_qty'];
				$data[$key]= $temArr;
			}
		
			usort($data, function($a, $b) {
				return $b['po_amount'] - $a['po_amount'];
			});

}else if($request->item_quarterly!=null){
			
			$quarter = $request->item_quarterly;
			$fyear = $request->item_fy;
			if($quarter==1){
				$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."04"."-"."01";
				$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."06"."-"."31";
			}else if($quarter==2){
				$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."07"."-"."01";
				$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."09"."-"."30";
			}else if($quarter==3){
				$start_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."10"."-"."01";
				$end_date = $fyear[0].$fyear[1].$fyear[2].$fyear[3]."-"."12"."-"."31";
			}else if($quarter==4){
				$start_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."01"."-"."01";
				$end_date = $fyear[5].$fyear[6].$fyear[7].$fyear[8]."-"."03"."-"."31";
			}
			
			$fy = $request->item_fy;
			$fy = $fy[2].$fy[3].$fy[7].$fy[8];
			
			$data=[];
			
			$items_data = \App\PurchaseOrderItem::select('purchase_order_items.item_code as item_code', \DB::raw('sum(purchase_order_items.qty) as total_item_qty'), 'items.description as item_name')->join('purchase_orders','purchase_orders.id', 'purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->where('purchase_orders.session',$fy)->whereBetween(DB::raw('date(purchase_orders.po_date)'),[$start_date,$end_date])->groupBy('purchase_order_items.item_code')->get()->toArray();
			
			foreach($items_data as $key=>$vendor){
				$temArr['vendor_name']= $vendor['item_name'];
				$temArr['po_amount']= $vendor['total_item_qty'];
				$data[$key]= $temArr;
			}
		
			usort($data, function($a, $b) {
				return $b['po_amount'] - $a['po_amount'];
			});

}else{
		
			$fy = $request->item_fy;
			$fy = $fy[2].$fy[3].$fy[7].$fy[8];
			$data=[];
			$items_data = \App\PurchaseOrderItem::select('purchase_order_items.item_code as item_code', \DB::raw('sum(purchase_order_items.qty) as total_item_qty'), 'items.description as item_name')->join('purchase_orders','purchase_orders.id', 'purchase_order_items.po_id')->join('items', 'items.code', 'purchase_order_items.item_code')->where('purchase_orders.session',$fy)->groupBy('purchase_order_items.item_code')->get()->toArray();
			
			foreach($items_data as $key=>$vendor){
					$temArr['vendor_name']= $vendor['item_name'];
				$temArr['po_amount']= $vendor['total_item_qty'];
				$data[$key]= $temArr;
			}
			
			// print_r($data);
			// dd($items_data);
		
			usort($data, function($a, $b) {
				return $b['po_amount'] - $a['po_amount'];
			});
			
		}

return response()->json($data);
			
			}	  
	}

public function purchase_analysis(Request $request) {
	
		if($request->type == 'purchase_analysis') {
			
		$year = explode('-', $request->purchase_analysis_fy);
		
		$data=[];
		$data['year1'] = $year[0];
		$data['year2'] = $year[1];
		$data['year_wise_po1'] = \App\PurchaseOrder::select(\DB::raw('year(po_date) as year'), \DB::raw('month(po_date) as month'), \DB::raw('count(id) as po_count'))->where('purchase_orders.status', 'Approved')->whereYear('purchase_orders.po_date', $year[0])->groupBy(\DB::raw('year(po_date)'))->groupBy(\DB::raw('month(po_date)'))->get()->toArray();
		
		$data['year_wise_po2'] = \App\PurchaseOrder::select(\DB::raw('year(po_date) as year'), \DB::raw('month(po_date) as month'), \DB::raw('count(id) as po_count'))->where('purchase_orders.status', 'Approved')->whereYear('purchase_orders.po_date', $year[1])->groupBy(\DB::raw('year(po_date)'))->groupBy(\DB::raw('month(po_date)'))->get()->toArray();
		
		
		return response()->json($data);
			
			}	  
	}

public function getPartyStock($party){
		$data = array();
		$party_array = explode('-',$party);
		if($party_array[1] == "1"){
			$type = "dealer_id";
		}else{
			$type = "product_company_id";
		}
		$cement_product_ids = \App\Product::where('product_category_id',2)->pluck('id');
		$data['cement_stock'] = \App\Inventory::where($type,$party_array[0])->whereIn('product_id',$cement_product_ids)->sum('quantity');
		$data['dap_stock'] = \App\Inventory::where($type,$party_array[0])->where('product_id',4)->sum('quantity');
		$data['npk_stock'] = \App\Inventory::where($type,$party_array[0])->whereIn('product_id',array(9,10))->sum('quantity');
		$data['urea_stock'] = \App\Inventory::where($type,$party_array[0])->where('product_id',19)->sum('quantity');
		$data['mop_stock'] = \App\Inventory::where($type,$party_array[0])->where('product_id',7)->sum('quantity');
		return view('dashboard.get-party-stock',$data);
		
	}

function latestSaleActivities(){
		$data = array();
		$activities = "";
		$cement_product_ids = \App\Product::where('product_category_id',2)->pluck('id');
		$cement_sale_activity = \App\LoadingSlipInvoice::whereIn('product_id',$cement_product_ids)->orderBy('id','desc')->first();
		if(!is_null($cement_sale_activity)){
			$activities .= "Sale of ".$cement_sale_activity->product." with Invoice ".$cement_sale_activity->invoice_number."(Rs $cement_sale_activity->total) to $cement_sale_activity->retailer_name from Account of ".getModelById('Dealer',$cement_sale_activity->dealer_id)->name."(".getModelById('Dealer',$cement_sale_activity->dealer_id)->address1.") ".calculateTimeSpan($cement_sale_activity->created_at).". ";
		}
		$dap_sale_activity = \App\LoadingSlipInvoice::where('product_id',4)->orderBy('id','desc')->first();
		if(!is_null($dap_sale_activity)){
			$activities .= "Sale of ".$dap_sale_activity->product." with Invoice ".$dap_sale_activity->invoice_number."(Rs $dap_sale_activity->total) to $dap_sale_activity->retailer_name from Account of ".getModelById('Dealer',$dap_sale_activity->dealer_id)->name."(".getModelById('Dealer',$dap_sale_activity->dealer_id)->address1.") ".calculateTimeSpan($dap_sale_activity->created_at).". ";
		}

$npk_sale_activity = \App\LoadingSlipInvoice::whereIn('product_id',array(9,10))->orderBy('id','desc')->first();
		if(!is_null($npk_sale_activity)){
			$activities .= "Sale of ".$npk_sale_activity->product." with Invoice ".$npk_sale_activity->invoice_number."(Rs $npk_sale_activity->total) to $npk_sale_activity->retailer_name from Account of ".getModelById('Dealer',$npk_sale_activity->dealer_id)->name."(".getModelById('Dealer',$npk_sale_activity->dealer_id)->address1.") ".calculateTimeSpan($npk_sale_activity->created_at).". ";
		}
		$urea_sale_activity = \App\LoadingSlipInvoice::where('product_id',19)->orderBy('id','desc')->first();
		if(!is_null($urea_sale_activity)){
			$activities .= "Sale of ".$urea_sale_activity->product." with Invoice ".$urea_sale_activity->invoice_number."(Rs $urea_sale_activity->total) to $urea_sale_activity->retailer_name from Account of ".getModelById('Dealer',$urea_sale_activity->dealer_id)->name."(".getModelById('Dealer',$urea_sale_activity->dealer_id)->address1.") ".calculateTimeSpan($urea_sale_activity->created_at).". ";
		}
		$mop_sale_activity = \App\LoadingSlipInvoice::where('product_id',7)->orderBy('id','desc')->first();
		if(!is_null($mop_sale_activity)){
			$activities .= "Sale of ".$mop_sale_activity->product." with Invoice ".$mop_sale_activity->invoice_number."(Rs $mop_sale_activity->total) to $mop_sale_activity->retailer_name from Account of ".getModelById('Dealer',$mop_sale_activity->dealer_id)->name."(".getModelById('Dealer',$mop_sale_activity->dealer_id)->address1.") ".calculateTimeSpan($mop_sale_activity->created_at).". ";
		}
		$data['activities'] = $activities;
		return view('dashboard.latest-sale-activities',$data);
	}

public function getPartyStockSaleGraph($party,$month,$year){
		$data = array();
		$cement_product_ids = \App\Product::where('product_category_id',2)->pluck('id');
		$data['cement_sale'] = \App\LoadingSlipInvoice::where('dealer_id',$party)->whereIn('product_id',$cement_product_ids)->whereMonth('created_at','=',$month)->whereYear('created_at','=',$year)->sum('total');

$data['dap_sale'] = \App\LoadingSlipInvoice::where('dealer_id',$party)->where('product_id',4)->whereMonth('created_at','=',$month)->whereYear('created_at','=',$year)->sum('total');
		$data['npk_sale'] = \App\LoadingSlipInvoice::where('dealer_id',$party)->whereIn('product_id',array(9,10))->whereMonth('created_at','=',$month)->whereYear('created_at','=',$year)->sum('total');
		$data['urea_sale'] = \App\LoadingSlipInvoice::where('dealer_id',$party)->where('product_id',19)->whereMonth('created_at','=',$month)->whereYear('created_at','=',$year)->sum('total');
		$data['mop_sale'] = \App\LoadingSlipInvoice::where('dealer_id',$party)->where('product_id',7)->whereMonth('created_at','=',$month)->whereYear('created_at','=',$year)->sum('total');
		return view('dashboard.get-party-stock-sale-graph',$data);
	}

public function getSalesAndPurchase($date){
		$data = array();
		$sales = array();
		$unique_sale_parties = \App\LoadingSlipInvoice::select('dealer_id')->whereDate('created_at','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->distinct()->get();

if(!is_null($unique_sale_parties)){
			foreach ($unique_sale_parties as $unique_sale_party) {
				$unique_products = \App\LoadingSlipInvoice::select('product_id')->where('dealer_id',$unique_sale_party->dealer_id)->whereDate('created_at','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->distinct()->get();
				foreach ($unique_products as $unique_product) {
					$tempArr = array();
					$tempArr['party'] = getModelById('Dealer',$unique_sale_party->dealer_id)->name;  
					$tempArr['product'] = getModelById('Product',$unique_product->product_id)->name;  
					$tempArr['total_sale_quantity'] = \App\LoadingSlipInvoice::where('product_id',$unique_product->product_id)->where('dealer_id',$unique_sale_party->dealer_id)->whereDate('created_at','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->sum('quantity');
					$tempArr['total_sale_amount'] = \App\LoadingSlipInvoice::where('product_id',$unique_product->product_id)->where('dealer_id',$unique_sale_party->dealer_id)->whereDate('created_at','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->sum('total');
					array_push($sales, $tempArr);
				}
			}
		}

$purchase = array();
		$unique_purchase_c_companies = \App\CompanyDi::select('product_company_id')->whereDate('invoice_date','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->distinct()->get();

$unique_purchase_w_companies = \App\WarehouseDi::select('product_company_id')->where('transfer_type',1)->whereDate('invoice_date','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->distinct()->get();
		$unique_purchase_w_parties = \App\WarehouseDi::select('from_dealer_id')->where('transfer_type',2)->whereDate('invoice_date','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->distinct()->get();

if(!is_null($unique_purchase_c_companies)){
			foreach ($unique_purchase_c_companies as $unique_purchase_c_company) {
				$unique_products = \App\CompanyDi::select('product_id')->where('product_company_id',$unique_purchase_c_company->product_company_id)->whereDate('invoice_date','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->distinct()->get();
				foreach ($unique_products as $unique_product) {
					$tempArr = array();
					$tempArr['party'] = getModelById('ProductCompany',$unique_purchase_c_company->product_company_id)->name;  
					$tempArr['product'] = getModelById('Product',$unique_product->product_id)->name;  
					$tempArr['total_purchase_quantity'] = \App\CompanyDi::where('product_id',$unique_product->product_id)->where('product_company_id',$unique_purchase_c_company->product_company_id)->whereDate('invoice_date','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->sum('quantity');
					$tempArr['total_purchase_amount'] = \App\CompanyDi::where('product_id',$unique_product->product_id)->where('product_company_id',$unique_purchase_c_company->product_company_id)->whereDate('invoice_date','=',date('Y-m-d',strtotime($date)))->orderBy('id','desc')->sum('total');
					array_push($purchase, $tempArr);
				}
			}
		}

if(!is_null($unique_purchase_w_companies)){
			foreach ($unique_purchase_w_companies as $unique_company) {
				$unique_products = \App\WarehouseDi::select('product_id')->where('product_company_id',$unique_company->product_company_id)->whereDate('invoice_date','=',date('Y-m-d'))->orderBy('id','desc')->distinct()->get();
				foreach ($unique_products as $unique_product) {
					$tempArr = array();
					$tempArr['party'] = getModelById('ProductCompany',$unique_company->product_company_id)->name;  
					$tempArr['product'] = getModelById('Product',$unique_product->product_id)->name;  
					$tempArr['total_purchase_quantity'] = \App\WarehouseDi::where('product_id',$unique_product->product_id)->where('product_company_id',$unique_company->product_company_id)->whereDate('invoice_date','=',date('Y-m-d'))->orderBy('id','desc')->sum('quantity');
					$tempArr['total_purchase_amount'] = \App\WarehouseDi::where('product_id',$unique_product->product_id)->where('product_company_id',$unique_company->product_company_id)->whereDate('invoice_date','=',date('Y-m-d'))->orderBy('id','desc')->sum('total');
					array_push($purchase, $tempArr);
				}
			}
		}

if(!is_null($unique_purchase_w_parties)){
			foreach ($unique_purchase_w_parties as $unique_purchase_party) {
				$unique_products = \App\WarehouseDi::select('product_id')->where('from_dealer_id',$unique_purchase_party->from_dealer_id)->whereDate('invoice_date','=',date('Y-m-d'))->orderBy('id','desc')->distinct()->get();
				foreach ($unique_products as $unique_product) {
					$tempArr = array();
					$tempArr['party'] = getModelById('Dealer',$unique_purchase_party->from_dealer_id)->name;  
					$tempArr['product'] = getModelById('Product',$unique_product->product_id)->name;  
					$tempArr['total_purchase_quantity'] = \App\WarehouseDi::where('product_id',$unique_product->product_id)->where('from_dealer_id',$unique_purchase_party->from_dealer_id)->whereDate('invoice_date','=',date('Y-m-d'))->orderBy('id','desc')->sum('quantity');
					$tempArr['total_purchase_amount'] = \App\WarehouseDi::where('product_id',$unique_product->product_id)->where('from_dealer_id',$unique_purchase_party->from_dealer_id)->whereDate('invoice_date','=',date('Y-m-d'))->orderBy('id','desc')->sum('total');
					array_push($purchase, $tempArr);
				}
			}
		}

$data['today_sales'] = $sales;
		$data['today_purchase'] = $purchase;
		// dd($data);

$data['date'] = date('m/d/Y',strtotime($date));
		return view('dashboard.get-sales-and-purchase',$data);
		
	}
	public function getRakeGraph($id){
		$data = array();
		$data['master_rakes'] = \App\MasterRake::where('is_active',1)->orderBy('id','desc')->get();
		
		$data['master_rake'] = \App\MasterRake::where('id',$id)->first();
		return view('dashboard.get-rake-graph',$data);
		
	}
	public function getMonthlyAverage($month,$year){
		$data = array();
		$data['month'] = $month;
		$data['year'] = $year;
		return view('dashboard.get-monthly-average',$data);
		
	}

public function setCompany(Request $request){
		$response = array();
		$validator = \Validator::make($request->all(),
			array(
				'act_as_company' =>'required',
			)
		);

if($validator->fails())
		{
			$response['flag'] = false;
			$response['errors'] = $validator->getMessageBag();
		}else{
			Session::put('acting_company',$request->act_as_company);
			$response['flag'] = true;
			$response['message'] = "Now Your are acting as ".getModelById('Company', $request->act_as_company)->name;
		}
		return response()->json($response);
	}

public function changeLanguage(Request $request){
		$response = array();
		$validator = \Validator::make($request->all(),
			array(
				'language' =>'required',
			)
		);

if($validator->fails())
		{
			$response['flag'] = false;
			$response['errors'] = $validator->getMessageBag();
		}else{
			Session::put('language',$request->language);
			\App::setLocale($request->language);
			$response['flag'] = true;
			$response['message'] = "Language Changed Successfully";
		}
		return response()->json($response);
	}
	public function blank()
	{
		return view('dashboard.blank');
	}

function convertToLacks($num) {
  
		// //function call
		// $num = "789";
		$ext="lac";//thousand,lac, crore
		$number_of_digits = $this->count_digit($num); //this is call :)
		if($number_of_digits>3)
		{
			if($number_of_digits%2!=0)
					$divider=$this->divider($number_of_digits-1);
			else
				 $divider=$this->divider($number_of_digits);
		}
		else
		    $divider=1;
	
		$fraction=$num/$divider;
		$fraction=number_format($fraction,2);
		if($number_of_digits==4 ||$number_of_digits==5)
				$ext="k";
		if($number_of_digits >= 6	|| $number_of_digits==7)
				$ext="Lac";
		if($number_of_digits==8 ||$number_of_digits==9)
				$ext="Cr";

return $fraction." ".$ext;

}

function count_digit($number) {
		return strlen($number);
	}

function divider($number_of_digits) {
		$tens="1";

if($number_of_digits>8)
			return 10000000;
       
		while(($number_of_digits-1)>0)
		{
			$tens.="0";
			$number_of_digits--;
		}
		return $tens;
	}

}