/**************** Stored Procedure **************/

CREATE DEFINER=`root`@`localhost` PROCEDURE `getEmpOverallTargetReport`(IN _fromDate VARCHAR(50), IN _toDate VARCHAR(50), IN _empID TEXT)
BEGIN
	declare ic_count int;
    declare sd_count int;
    declare sdv_count int;
    declare fd_count int;
    declare fm_count int;
    declare av_count int;
    declare revisit_count int;
    declare dealer_count int;
    declare liquidation_count int;
    SELECT  (
		select count(farmerId) from tbl_farmer where empId in (_empID) and farmerRegDate between _fromDate and _toDate
	) INTO ic_count;
	SELECT  (
		select count(faId) from tbl_farmactivity where empId in (_empID) AND SD_Flag=1 and SD_date between _fromDate and _toDate
	) INTO sd_count;
    SELECT  (
		SELECT count(*) AS totalCount FROM tbl_farmactivity as tfa
		LEFT JOIN tbl_farmer as tf
		ON tfa.farmerId = tf.farmerId
		WHERE SDV_date BETWEEN _fromDate AND _toDate
		AND FIND_IN_SET(tf.empId, _empID)
	) INTO sdv_count;
    SELECT  (
		SELECT count(*) AS totalCount FROM tbl_farmactivityFD as tfa
		LEFT JOIN tbl_farmer as tf ON tfa.farmerId = tf.farmerId
		WHERE FD_date BETWEEN _fromDate AND _toDate
		AND FIND_IN_SET(tf.empId, _empID)
	) INTO fd_count;
    SELECT  (
		SELECT count(*) FROM tbl_farmermeeting WHERE empId IN (_empID) AND fmDate BETWEEN _fromDate AND _toDate
	) INTO fm_count;
	SELECT  (
		SELECT count(*) AS totalCount FROM tbl_agencyvisit WHERE empId IN (_empID) AND visitDate BETWEEN _fromDate AND _toDate
	) INTO av_count;
    SELECT  (
		SELECT count(*) AS totalCount FROM tbl_revisit WHERE empId IN (_empID) AND revisit_datetime BETWEEN _fromDate AND _toDate
	) INTO revisit_count;
	SELECT  (
		SELECT COUNT(*) FROM tbl_agency as ta
        LEFT JOIN tbl_agencydetail as tad ON ta.agencyId = tad.agencyId
        WHERE tad.empid IN (_empID)
        AND ta.agencyType = 1
        AND tad.date BETWEEN _fromDate AND _toDate
	) INTO dealer_count;
    SELECT  (
		SELECT count(visitId) FROM tbl_agencyvisit as tav
        WHERE liquidation = '1'
        AND visitDate BETWEEN _fromDate AND _toDate AND empId IN (_empID)
	) INTO liquidation_count;

    select ic_count, sd_count, sdv_count, fd_count, fm_count, av_count, revisit_count, dealer_count, liquidation_count;
END


/////////////////////  END  ///////////////////////


////////////////////  CODE START //////////////////

$regionID = $request->regionID;
$fromDate = $request->from_date;
$toDate = $request->to_date;
$categoryIDWithParent = $regionID.','.$this->getChildren($regionID);
$getAllRegionIDs = rtrim($categoryIDWithParent,',');
// dd($getAllRegionIDs);
$reportArray = [];
$getEmployee = DB::table('tbl_employee')->where('deleteFlag',0)->whereIn('region',explode(',',$getAllRegionIDs))->get()->toArray();
foreach ($getEmployee as $employee) {
  $empId = $employee->id;
  $data['employee_name'] = $employee->emp_name;
  // I am calling above Procedure from below line
  $allData = DB::select("call gavl.getEmpOverallTargetReport('$fromDate','$toDate','$empId')");
  $data['ic_count'] = $allData[0]->ic_count;
  $data['sd_count'] = $allData[0]->sd_count;
  $data['sdv_count'] = $allData[0]->sdv_count;
  $data['fd_count'] = $allData[0]->fd_count;
  $data['fm_count'] = $allData[0]->fm_count;
  $data['av_count'] = $allData[0]->av_count;
  $data['revisit_count'] = $allData[0]->revisit_count;
  $data['dealer_count'] = $allData[0]->dealer_count;
  $data['liquidation_count'] = $allData[0]->liquidation_count;
  array_push($reportArray, $data);
}
dd($reportArray);
return response()->json([
  'code' => 200,
  'getOverallTargetReport' => $reportArray,
]);