<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Maintenance { 

    private $CI = null;

	public $error = '';
	public $mainbid = '';
	public $fyStart = '';
	public function __construct()
	{
		$this->CI = &get_instance();
		$this->error = '';
	}

    public function trigger($job)
	{
		
		if (!$job)
		{
			return;
		}

		switch ($job['actCode']) {
			case 'DMT'://Daily Maintenance Cron
				$this->dailyMaintenance();
			break;
			case 'WMT': //Weekly Maintenance Cron
				$this->weeklyMaintenance();
			break;
			case 'MMT': //Monthly Maintenance Cron
				$this->monthlyMaintenance();
			break;
			default:
				# code...
			break;
		}
		
		
	}
	public function monthlyMaintenance() {

		$cur_date = gmdate('Y-m-d H:i:s');
		$logDelete = date('Y-m-d H:i:s', strtotime('- 3 years', strtotime($cur_date)));
		$useraccessDelete = date('Y-m-d H:i:s', strtotime('- 3 months', strtotime($cur_date)));
		$devicelocDelete = date('Y-m-d H:i:s', strtotime('- 1 years', strtotime($cur_date)));
		
		// delete three years before logs
		$this->CI->db->delete('trans_log',array('aDate < ' => $logDelete));
		// delete three month before user access data
		$this->CI->db->delete('useraccess',array('updatedOn < ' => $useraccessDelete));
		// delete one year before device log data
		$this->CI->db->delete('devices_loc',array('accessTime < ' => $useraccessDelete));
		return true;
	}

	public function weeklyMaintenance() {
		$cur_date = strtotime(gmdate('Y-m-d H:i:s'));
		$backup_check_date = date('Y-m-d H:i:s', strtotime('-14 day'));
		$messageNotificationDelete = date('Y-m-d H:i:s', strtotime('-15 day'));
		$this->CI->db->select("count(*) AS cnt",false);
		$this->CI->db->from("trans_log")->where(array("aEvent" => 'BL', 'aDate > ' => $backup_check_date));
		$this->CI->db->order_by('id','DESC');
		$data = $this->CI->db->get()->row_array();
		if(empty($data) || empty($data['cnt'])) {
			$this->insertbackUpMessage();
		}
		
		//delete Notifications older than 15 days. (Weekly)
		$this->CI->db->delete('messages',array('mDate < ' => $messageNotificationDelete));
		
		// Obo Auto stock Level update once in a week
		$this->updateStklevel();

		// Obo Notify Expiry Stock update once in a week
		$this->notifyExpiryStock();

		$this->updateTransCount();

        return true;
	}
	public function notifyExpiryStock() {
		$this->CI->db->select("id,mainbid,fyStart");
		$this->CI->db->from("companies");
		$this->CI->db->where("id",4);
		$companyList = $this->CI->db->get()->result_array();
		if(!empty($companyList)) {
			for ($i=0; $i < count($companyList); $i++) { 
				$cId = $companyList[$i]['id'];
				$this->mainbid = $companyList[$i]['mainbid'];
				$this->fyStart = $companyList[$i]['fyStart'];				
				$this->notifyOBOExpiryStock($cId);
				$this->CI->db->select("id");
				$this->CI->db->from("contacts");
				$this->CI->db->where('cId',$cId);
				$this->CI->db->where_in('csType',array('B','F'));
				$branchList = $this->CI->db->get()->result_array();
				if(count($branchList)) {
					for ($j=0; $j < count($branchList); $j++) {
						$this->notifyOBOExpiryStock($cId,$branchList[$j]['id']);
					}
				}
			}
		}
	}

	private function updateTransCount()
	{
		$this->CI->db->select("COUNT(*) AS cnt")->from("trans AS t");
		financial_year_where();
		$transCnt = $this->CI->db->get()->row_array();

		$this->CI->db->replace('compconfig', array(
			"cId" => 0,
			"code" => "transCnt",
			"value" => $transCnt["cnt"]
		));
	}

	public function notifyOBOExpiryStock($cId,$bId = 0) {
		if($bId == $this->mainbid)
		{
			return;
		}
		$expiryBefore = 30;
		$this->CI->db->select("obja.*,obo.*,ojs.startDate,ojs.lastExecOn")->from("oboactions as obo");
		$this->CI->db->join('obojobact as obja','obja.actId = obo.id');
		$this->CI->db->join('obojobs as obj','obj.id = obja.jobId');
		$this->CI->db->join("obojobscheduler AS ojs", "ojs.jobId = obj.id",'left');
		$this->CI->db->where("obo.actCode", "STR");
		$this->CI->db->where(array(
			"obj.cId" => $cId,
			"obj.bId" => $this->mainbid,
		));
		if(empty($bId)) {
			$bId = $this->mainbid;
		}
		$result =  $this->CI->db->get()->row_array();
		if(!empty($result)) {
			if(!empty($result["actConfig"])) {
				$result["actConfig"] = json_decode($result["actConfig"],true);
				$expiryBefore = !empty($result["actConfig"]['expiryBefore']) ? $result["actConfig"]['expiryBefore'] : 30;
			}
		}
		$cur_date = gmdate('Y-m-d');
		$expDateFilter = date('Y-m-d',strtotime($cur_date. ' +'.$expiryBefore.' days'));
		$post_data = array("to_date" => $expDateFilter,"branch" => $bId,'bId' =>$bId,"rpType"=>"STE","page" => 1,"expDateFilter"=> $expDateFilter,"isDet" => 
		false,"fromObo" => true,"cId"=>$cId,"stk_type" => 'current');
		$fin_start_date = $fin_end_date = $from_date = $post_data['from_date'] = "";
		$to_date = $post_data['to_date'];
		get_financial_dates($from_date, $fin_start_date, $fin_end_date,$this->fyStart);
		$from_date = $fin_start_date;
		// $this->CI->load->model("ReportModel","reportModel");
		// $this->CI->load->model("report/StockModel", "stockModel");
		// // $this->CI->reportModel->cId = $cId;
		// // $this->CI->reportModel->bId = $bId;
		// $result = $this->CI->stockModel->alert($post_data);
		// print_r($result);
		$expiryQuery = "select itms.* ,IFNULL(ita.atList,'') as atList  from ( select itemId,uom,ROUND(sum(p_qty),3) as p_qty,ROUND(sum(s_qty),3) as s_qty,itemName,SKU ,expDate,batchName,saleRate,max(if(itemAttrId>0,mrp,0)) as mrp,ROUND(sum(pi_rate)/sum(ifnull(pur_qty,p_qty)),2) AS pi_rate,-1 as itemAttrId from (SELECT ti.itemAttrId, iar.saleRate, iar.mrp, it.stkLevel, sum(if(t.tType IN ('PI', 'PR', 'EI', 'PE', 'PD', 'GR', 'SC'), if(ti.itc=1, ti.taxableAmount, ti.itemTotal) + ti.chargesAmount, 0) * if (t.tType!='SC', 1, 0)) AS pi_rate, it.uom, sum(IF(t.ttype NOT IN ('PI', 'PR', 'EI', 'PE', 'PD', 'GR', 'SC'), (ti.qty+ti.freeQty), 0) * if(itr.convFactor, itr.convFactor, 1)) as s_qty, sum(IF(t.ttype IN ('PI', 'PR', 'EI', 'PE', 'PD', 'GR', 'SC'), (ti.qty+ti.freeQty), 0) * if(itr.convFactor, itr.convFactor, 1)) as p_qty, sum(IF(t.ttype IN ('PI', 'PR', 'EI', 'PE', 'PD', 'GR', 'SC'), (ti.qty+ti.freeQty), 0) * if(itr.convFactor, itr.convFactor, 1) * if (t.tType!='SC', 1, 0)) as pur_qty, it.itemId, if(it.name!='', it.name, ti.itemName) as itemName, it.SKU, att.name as expDate, attBatch.name as batchName
		FROM ". $this->CI->db->dbprefix('trans') ." AS t
		JOIN ". $this->CI->db->dbprefix('trans_items') ." AS ti ON ti.transId=t.id
		JOIN ". $this->CI->db->dbprefix('items') ." AS it ON it.itemId=ti.itemId
		LEFT JOIN ". $this->CI->db->dbprefix('item_attr') ." AS iar ON iar.itemAttrId=ti.itemAttrId and iar.itemId=ti.itemId
		LEFT JOIN ". $this->CI->db->dbprefix('itemrelunits') ." AS itr ON itr.itemId=ti.itemId and itr.relUnitName=ti.unit
		JOIN ". $this->CI->db->dbprefix('item_attr_link') ." AS ial ON ial.itemAttrId=ti.itemAttrId
		JOIN ". $this->CI->db->dbprefix('attributes') ." AS att ON att.attrId=ial.attrId
		JOIN ". $this->CI->db->dbprefix('attributes_group') ." AS atg ON atg.attrGrpId=att.attrGrpId and atg.grpCode='ED' and atg.cId=t.cid
		LEFT JOIN (Select ialB.itemAttrId, attB.name from ". $this->CI->db->dbprefix('item_attr_link') ." AS ialB
				JOIN ". $this->CI->db->dbprefix('attributes') ." AS attB ON attB.attrId=ialB.attrId
				JOIN ". $this->CI->db->dbprefix('attributes_group') ." atgB on attB.attrGrpId=atgB.attrGrpId and atgB.grpCode ='BA' and atgB.cId=4) AS attBatch ON attBatch.itemAttrId=ti.itemAttrId
		WHERE it.type IN('G', 'T', 'S', 'Y', 'R')
		AND t.cId = '".$cId."'
		AND t.tDate >= '".$from_date."'
		AND t.tDate <= '".$to_date."'
		AND it.isInventory = 1
		AND t.status NOT IN('D', 'X', 'V')
		AND   (
		ti.locId IN('".$bId."')
		 )
		AND t.tType IN('SI', 'SR', 'SC', 'SD', 'SH', 'PI', 'PR', 'EI', 'PE', 'PC', 'PD', 'GR')
		AND   (
		t.reftType NOT IN('SI', 'SR', 'SC', 'SD', 'SH', 'PI', 'PR', 'EI', 'PE', 'PC', 'PD', 'GR')
		OR    (
		t.tType IN('SC', 'SD', 'PC', 'PD')
		AND t.reftType IN('SI', 'SR', 'PI', 'PR', 'EI', 'PE', 'SC', 'PD', 'SD', 'PC')
		  )
		OR    (
		t.tType IN('GR', 'SH')
		AND     (
		t.reftType IN('GR', 'SH')
		   )
		  )
		 )
		AND (cast(att.name as Date) > '".$cur_date."' AND cast(att.name as Date) <= '".$expDateFilter."')
		GROUP BY it.itemId UNION ALL SELECT si.itemAttrId, iar.saleRate, iar.mrp, it.stkLevel, it.uom, (si.rate*si.qty * IF(si.qty < 0, -1, 0)) AS pi_rate, sum(si.qty * IF(si.qty < 0, -1, 0) * if(itr.convFactor, itr.convFactor, 1)) as s_qty, sum(IF(si.qty > 0, si.qty, 0) * if(itr.convFactor, itr.convFactor, 1)) as p_qty, null as pur_qty, it.itemId, it.name AS itemName, it.SKU, att.name as expDate, attBatch.name as batchName
		FROM ". $this->CI->db->dbprefix('stock_entries') ." AS se
		JOIN ". $this->CI->db->dbprefix('stock_items') ." AS si ON si.sId=se.id
		JOIN ". $this->CI->db->dbprefix('items') ." AS it ON it.itemId=si.itemId
		LEFT JOIN ". $this->CI->db->dbprefix('itemrelunits') ." AS itr ON itr.itemId=si.itemId and itr.relUnitName=si.unit
		LEFT JOIN ". $this->CI->db->dbprefix('item_attr') ." AS iar ON iar.itemAttrId=si.itemAttrId and iar.itemId=si.itemId
		JOIN ". $this->CI->db->dbprefix('item_attr_link') ." AS ial ON ial.itemAttrId=si.itemAttrId
		JOIN ". $this->CI->db->dbprefix('attributes') ." AS att ON att.attrId=ial.attrId
		JOIN ". $this->CI->db->dbprefix('attributes_group') ." AS atg ON atg.attrGrpId=att.attrGrpId and atg.grpCode='ED' and atg.cId=se.cid
		LEFT JOIN (Select ialB.itemAttrId, attB.name from ". $this->CI->db->dbprefix('item_attr_link') ." AS ialB
				JOIN ". $this->CI->db->dbprefix('attributes') ." AS attB ON attB.attrId=ialB.attrId
				JOIN ". $this->CI->db->dbprefix('attributes_group') ." atgB on attB.attrGrpId=atgB.attrGrpId and atgB.grpCode ='BA' and atgB.cId=4) AS attBatch ON attBatch.itemAttrId=si.itemAttrId
		WHERE it.type IN('G', 'T', 'S', 'Y', 'R')
		AND se.cId = '".$cId."'
		AND se.sDate >= '".$from_date."'
		AND se.sDate <= '".$to_date."'
		AND it.isInventory = 1
		AND si.fromlocId IN('".$bId."')
		AND (cast(att.name as Date) > '".$cur_date."' AND cast(att.name as Date) <= '".$expDateFilter."')
		GROUP BY it.itemId UNION ALL SELECT si.itemAttrId, iar.saleRate, iar.mrp, it.stkLevel, it.uom, (si.rate*IF(si.qty > 0, si.qty, 0)) AS pi_rate, sum(IF(si.qty > 0, si.qty, 0) * if(itr.convFactor, itr.convFactor, 1)) as s_qty, sum(IF(si.qty < 0, si.qty * -1, 0) * if(itr.convFactor, itr.convFactor, 1)) as p_qty, null as pur_qty, it.itemId, it.name AS itemName, it.SKU, att.name as expDate, attBatch.name as batchName
		FROM ". $this->CI->db->dbprefix('stock_entries') ." AS se
		JOIN ". $this->CI->db->dbprefix('stock_items') ." AS si ON si.sId=se.id
		JOIN ". $this->CI->db->dbprefix('items') ." AS it ON it.itemId=si.itemId
		LEFT JOIN ". $this->CI->db->dbprefix('itemrelunits') ." AS itr ON itr.itemId=si.itemId and itr.relUnitName=si.unit
		LEFT JOIN ". $this->CI->db->dbprefix('item_attr') ." AS iar ON iar.itemAttrId=si.itemAttrId and iar.itemId=si.itemId
		JOIN ". $this->CI->db->dbprefix('item_attr_link') ." AS ial ON ial.itemAttrId=si.itemAttrId
		JOIN ". $this->CI->db->dbprefix('attributes') ." AS att ON att.attrId=ial.attrId
		JOIN ". $this->CI->db->dbprefix('attributes_group') ." AS atg ON atg.attrGrpId=att.attrGrpId and atg.grpCode='ED' and atg.cId=se.cid
		LEFT JOIN (Select ialB.itemAttrId, attB.name from ". $this->CI->db->dbprefix('item_attr_link') ." AS ialB
				JOIN ". $this->CI->db->dbprefix('attributes') ." AS attB ON attB.attrId=ialB.attrId
				JOIN ". $this->CI->db->dbprefix('attributes_group') ." atgB on attB.attrGrpId=atgB.attrGrpId and atgB.grpCode ='BA' and atgB.cId=4) AS attBatch ON attBatch.itemAttrId=si.itemAttrId
		WHERE it.type IN('G', 'T', 'S', 'Y', 'R')
		AND se.cId = '".$cId."'
		AND se.sDate >= '".$from_date."'
		AND se.sDate <= '".$to_date."'
		AND it.isInventory = 1
		AND se.sType = 'TL'
		AND si.tolocId IN('".$bId."')
		AND (cast(att.name as Date) > '".$cur_date."' AND cast(att.name as Date) <= '".$expDateFilter."')
		GROUP BY it.itemId ) uniontable  group by itemId HAVING (p_qty-s_qty) <> 0 limit 0,40 ) itms  LEFT JOIN (SELECT ita.itemAttrId as itaAttrId, GROUP_CONCAT(If (ag.fldType!='E', CONCAT(ag.Name, '	', if(ag.fldType = 'D', (CASE
										WHEN ag.format = 'd-m-Y' THEN DATE_FORMAT(a.name, '%d/%m/%Y')
										WHEN ag.format = 'm-d-Y' THEN DATE_FORMAT(a.name, '%m-%d-%Y')
										WHEN ag.format = 'MY' THEN DATE_FORMAT(a.name, '%b %Y')
										 WHEN ag.format = 'FY' THEN DATE_FORMAT(a.name, '%M %Y')
											ELSE DATE_FORMAT(a.name, '%d/%m/%Y')
									END ), a.name)), '')  ORDER BY ag.sortNo SEPARATOR '
		') as atList
		FROM ". $this->CI->db->dbprefix('item_attr') ." as ita
		JOIN ". $this->CI->db->dbprefix('item_attr_link') ." as ial ON ial.itemAttrId=ita.itemAttrId
		JOIN ". $this->CI->db->dbprefix('attributes') ." as a ON a.attrId=ial.attrId
		JOIN ". $this->CI->db->dbprefix('attributes_group') ." as ag ON ag.attrGrpId=a.attrGrpId
		WHERE ita.cId = '".$cId."'
		GROUP BY ita.itemAttrId) ita on ita.itaAttrId=itms.itemAttrId LEFT JOIN (SELECT tias.itemAttrId as tiasItemAttrId, GROUP_CONCAT(CONCAT(ag.name, '\t', if (iasc.curStk>1 || iasc.curStk<0, CONCAT(tias.serialNo, ' (', iasc.curStk, ') '), CONCAT(tias.serialNo))) ORDER BY iasc.curStk SEPARATOR '\n') as slList
		FROM ". $this->CI->db->dbprefix('item_attr_serial') ." as tias
		JOIN ". $this->CI->db->dbprefix('item_attr_link') ." as ial ON ial.itemAttrId=tias.itemAttrId
		JOIN ". $this->CI->db->dbprefix('item_attr_serial_cur') ." as iasc ON iasc.asId=tias.id
		JOIN ". $this->CI->db->dbprefix('attributes') ." as a ON a.attrId=ial.attrId
		JOIN ". $this->CI->db->dbprefix('attributes_group') ." as ag ON ag.attrGrpId=a.attrGrpId AND ag.fldType='E'
		WHERE tias.cId = '".$cId."'
		AND   (
		iasc.bId IN('".$bId."')
		 )
		AND iasc.curStk !=0
		GROUP BY tias.itemAttrId) ias on ias.tiasitemAttrId=itms.itemAttrId  order by itemId,itemAttrId asc";			
		$query_list =	$this->CI->db->query($expiryQuery);
		$result = $query_list->result_array();
		$itemQty = 0;
		if(!empty($result)) {
			for ($j=0; $j < count($result); $j++) {
				$result[$j]['p_qty'] = (isset($result[$j]['p_qty'])) ? (float)$result[$j]['p_qty'] : 0;
				$result[$j]['s_qty'] = (isset($result[$j]['s_qty'])) ? (float)$result[$j]['s_qty'] : 0;
				$result[$j]['b_qty'] = $result[$j]['p_qty'] - $result[$j]['s_qty'];
				$itemQty = $itemQty + $result[$j]['b_qty'];
			}
			$params = array("cId" => $cId,"bId" => $bId,"itemCount" => count($result),"itemQty" => $itemQty);
			$this->insertexpiryMessage($params);
		}
		// print_r($result);
		
	}

	public function insertexpiryMessage($params = array()) {
		$this->CI->load->model('oboMessageModel');
		$user_id =  0;
		$message = array();
		$message['bId'] = $params['bId'];
		$message['isTask'] = 1;
		$message['itemCount'] = $params['itemCount'];
		$message['itemQty'] = $params['itemQty'];
		$messageParams =array('mType' =>'EA', 'cId' =>  $params['cId'],'toUid' => $user_id ,'toRoles'=>'A', 'message'=>json_encode($message),'priority'=>'H','isActive'=>'Y',"isFrocecId"=> 1);
		$this->CI->oboMessageModel->notificationAlert($messageParams);
	}
	public function updateStklevel() {
		$this->CI->db->select("id,mainbid");
		$this->CI->db->from("companies");
		$companyList = $this->CI->db->get()->result_array();
		if(!empty($companyList)) {
			for ($i=0; $i < count($companyList); $i++) { 
				$cId = $companyList[$i]['id'];
				$this->mainbid = $companyList[$i]['mainbid'];
				$this->updateOBOStockLevel($cId);
				$this->CI->db->select("id");
				$this->CI->db->from("contacts");
				$this->CI->db->where('cId',$cId);
				$this->CI->db->where_in('csType',array('B','F'));
				$branchList = $this->CI->db->get()->result_array();	
				if(count($branchList)) {
					$this->updateOBOStockLevel($cId,count($branchList));
				}
			}
		}
	}

	public function updateOBOStockLevel($cId,$bId = 0) {
		$range_cnt 	= 3;
		// $oboDays = !empty($config['oboDays'])?$config['oboDays']:14;
		$oboDays = 14;
		$this->CI->db->select("obja.*,obo.*,ojs.startDate,ojs.lastExecOn")->from("oboactions as obo");
		$this->CI->db->join('obojobact as obja','obja.actId = obo.id');
		$this->CI->db->join('obojobs as obj','obj.id = obja.jobId');
		$this->CI->db->join("obojobscheduler AS ojs", "ojs.jobId = obj.id",'left');
		$this->CI->db->where("obo.actCode", "STR");
		$this->CI->db->where(array(
			"obj.cId" => $cId,
			"obj.bId" => $this->mainbid,
		));
		if(empty($bId)) {
			$bId = $this->mainbid;
		}
		$result =  $this->CI->db->get()->row_array();
		if(!empty($result)) {
			if(!empty($result["actConfig"])) {
				$result["actConfig"] = json_decode($result["actConfig"],true);
				$oboDays = $result["actConfig"]['stklevel'];
			}
		}
		$start_date = $end_date = $cur_date = gmdate('Y-m-d');
		$ranges 	= array();
		$end_date 	= $cur_date;
		$start_date = 	date('Y-m-d',strtotime($cur_date. ' -'.max($oboDays*$range_cnt,90).' days'));
		$params = array('cId' => $cId,'bId'=>$bId,'fromDate'=>$start_date,'toDate'=>$cur_date);
		// $salesQuery = $this->salesQuery($params);
		// $manufacturingJournalQuery = $this->manufacturingJournal($params);
		// // y1,y2 ...
		// $select = 'SELECT itemId,CEILING(sum(qty) * ('.$oboDays.'/90)) AS avgExpsal';

		// for($i=$range_cnt;$i>0;$i--) {
		// 	$col = 'y'.$i;
		// 	$from_date = date('Y-m-d',strtotime($end_date. ' -'.$oboDays.' days'));
		// 	$select .= ",sum(if(tdate > '".$from_date."' and tdate <= '".$end_date."', qty,0)) AS ".$col;
		// 	$end_date = $from_date;
		// }

		// $innerQurry = $select;
		// $innerQurry .= ((!empty($bId) && $bId != $this->mainbid) ? ',sout.bId,sout.cId ' : ''  ).' FROM (/* Sales */'.$salesQuery .' UNION ALL /* Manufacturing Journal */ '.$manufacturingJournalQuery .') AS sout GROUP BY ' .((!empty($bId) && $bId != $this->mainbid) ? "cId,bId," :"").' itemId';
		// $innerJoinchildQuery = 'SELECT *, ((-1 * (y1-((y3+y2+y1)/3)))  + (1 * (y3-((y3+y2+y1)/3))))/2 AS slope, ((y3+y2+y1)/3) - (2 *  ((-1 * (y1-((y3+y2+y1)/3)))  + (1 * (y3-((y3+y2+y1)/3))))/2) AS interc FROM ('.$innerQurry.') AS in_t';
		// $innerJoinQuery = 'SELECT t.itemId,' . ((!empty($bId) && $bId != $this->mainbid) ? ' t.bId,t.cId, '  : '' ) .  ' CEILING(if((slope * 4) + interc > 0 AND (y1>0 AND y2>0 AND y3>0),(slope * 4) + interc, avgExpsal)) AS oboStkLevel 
		// FROM ('.$innerJoinchildQuery.') AS t';

		// $resetQuery = "";
		// if(!empty($bId) && $bId != $this->mainbid) {
		// 	$resetQuery = "UPDATE " .$this->CI->db->dbprefix('items_cur') ." AS icur INNER JOIN " .$this->CI->db->dbprefix('items') ." AS i ON i.itemid=icur.itemid SET icur.stkLevel = 0 WHERE i.isOboLevel= 'Y' AND i.cId = ".$cId;
		// } else {
		// 	$resetQuery = "UPDATE " .$this->CI->db->dbprefix('items') ." AS i SET i.stkLevel = 0 WHERE i.isOboLevel = 'Y' AND i.cId = ".$cId;
		// }

		// // $updatefinalQuery = $resetQuery . " GO " . 
		// $updatefinalQuery = "UPDATE " .((!empty($bId) && $bId != $this->mainbid) ? $this->CI->db->dbprefix('items_cur') : $this->CI->db->dbprefix('items') )."  AS i INNER JOIN (".$innerJoinQuery.") AS istk ON istk.itemId=i.itemId ".((!empty($bId) && $bId != $this->mainbid) ? 'AND istk.cId=i.cId AND istk.bId = i.bId' :'')." 
		// SET i.stkLevel = istk.oboStkLevel";

		// $this->CI->db->query($resetQuery);
		// $this->CI->db->query($updatefinalQuery);
		
		// echo $updatefinalQuery . "<br><br><br>";
		// exit;
		$table_to_update = (!empty($bId) && $bId != $this->mainbid) ? 'ob_items_cur' : 'ob_items';
		$sql = "
		UPDATE {$table_to_update} i
		JOIN (
		SELECT s.itemId,
				ROUND(SUM(IF(s.tDate >= CURDATE() - INTERVAL 84 DAY, s.qty, 0)) / 12, 2) AS calcAvgWeek,
				SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 407 DAY AND CURDATE() - INTERVAL 365 DAY, s.qty, 0)) / 42 AS seasonalDaily,
				SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 365 DAY AND CURDATE(), s.qty, 0)) / 365 AS yearDaily,
				ROUND(
				IF(SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 365 DAY AND CURDATE(), s.qty, 0)) / 365 > 0,
					SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 407 DAY AND CURDATE() - INTERVAL 365 DAY, s.qty, 0)) / 42 /
					(SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 365 DAY AND CURDATE(), s.qty, 0)) / 365),
					0
				), 2
				) AS seasonalityScore,
				SUM(IF(s.tDate >= CURDATE() - INTERVAL 14 DAY, s.qty, 0)) AS y3,
				SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 28 DAY AND CURDATE() - INTERVAL 14 DAY, s.qty, 0)) AS y2,
				SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 42 DAY AND CURDATE() - INTERVAL 28 DAY, s.qty, 0)) AS y1,
				SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 407 DAY AND CURDATE() - INTERVAL 393 DAY, s.qty, 0)) AS y1_last,
				SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 393 DAY AND CURDATE() - INTERVAL 379 DAY, s.qty, 0)) AS y2_last,
				SUM(IF(s.tDate BETWEEN CURDATE() - INTERVAL 379 DAY AND CURDATE() - INTERVAL 365 DAY, s.qty, 0)) AS y3_last,
				DATEDIFF(CURDATE(), MIN(s.tDate)) AS daysActive
		FROM (
			SELECT rr_ti.itemId,
				(rr_ti.qty + rr_ti.freeQty) * IFNULL(rr_itr.convFactor, 1) AS qty,
				rr_t.tDate
			FROM ob_trans rr_t
			JOIN ob_trans_items rr_ti ON rr_t.id = rr_ti.transid
			JOIN ob_items rr_it ON rr_ti.itemId = rr_it.itemid
			LEFT JOIN ob_itemrelunits rr_itr ON rr_itr.itemId = rr_ti.itemId AND rr_itr.relUnitName = rr_ti.unit
			WHERE rr_t.status NOT IN ('D', 'X')
			AND rr_t.tType IN ('SI', 'SR', 'SD')
			AND rr_t.tDate >= CURDATE() - INTERVAL 407 DAY
			AND rr_t.cId = {$cId}
			AND rr_t.bId = {$bId}
			AND rr_it.type = 'G'
			AND rr_it.isInventory = 1
			AND rr_it.isOboLevel = 'Y'
			UNION ALL
			SELECT si.itemId,
				-si.qty * IFNULL(itr.convFactor, 1) AS qty,
				se.sDate AS tDate
			FROM ob_stock_entries se
			JOIN ob_stock_items si ON si.sId = se.id
			LEFT JOIN ob_itemrelunits itr ON itr.itemId = si.itemId AND itr.relUnitName = si.unit
			WHERE se.sType = 'MJ'
			AND se.sDate >= CURDATE() - INTERVAL 407 DAY
			AND si.qty < 0
			AND se.cId = {$cId}
			AND se.locid = {$bId}
		) s
		GROUP BY s.itemId
		) salstats ON salstats.itemId = i.itemId
		LEFT JOIN (
		SELECT poi.itemId,
				ROUND(AVG(DATEDIFF(gr.tDate, po.tDate))) AS avgLeadDays
		FROM ob_trans po
		JOIN ob_trans_items poi ON po.id = poi.transId
		JOIN ob_trans_ref tref ON tref.reftransId = po.id
		JOIN ob_trans gr ON gr.Id = tref.transId
							AND gr.tdate > po.tdate
							AND gr.tType IN ('PI', 'PR', 'GR')
							AND gr.reftType = 'PO'
		JOIN ob_trans_items gri ON gr.id = gri.transId
								AND gri.itemId = poi.itemId
								AND gri.refTitemId = poi.id
		WHERE po.tType = 'PO'
			AND po.status NOT IN ('D', 'X', 'V')
			AND gr.status NOT IN ('D', 'X', 'V')
			AND po.tDate >= CURDATE() - INTERVAL 180 DAY
			AND gr.tDate >= CURDATE() - INTERVAL 180 DAY
			AND DATEDIFF(gr.tDate, po.tDate) BETWEEN 1 AND 90
		GROUP BY poi.itemId
		) leadpo ON leadpo.itemId = i.itemId
		SET i.stkLevel = CEIL(
		CASE
			WHEN salstats.daysActive < 21 THEN
			IF(salstats.y3 > 0, (salstats.y3 / {$oboDays}) * (IFNULL(leadpo.avgLeadDays, 7) + {$oboDays}), 0)
			WHEN salstats.calcAvgWeek >= 10 AND salstats.seasonalityScore >= 1.5 THEN
			(
				0.7 * ((salstats.y3 - salstats.y1)/2 * 4 + ((salstats.y1 + salstats.y2 + salstats.y3)/3 - (salstats.y3 - salstats.y1)/2)) +
				0.3 * ((salstats.y3_last - salstats.y1_last)/2 * 4 + ((salstats.y1_last + salstats.y2_last + salstats.y3_last)/3 - (salstats.y3_last - salstats.y1_last)/2))
			) / {$oboDays} * (IFNULL(leadpo.avgLeadDays, 7) + {$oboDays})
			WHEN salstats.calcAvgWeek >= 10 THEN
			((salstats.y3 - salstats.y1)/2 * 4 + ((salstats.y1 + salstats.y2 + salstats.y3)/3 - (salstats.y3 - salstats.y1)/2)) / {$oboDays} * (IFNULL(leadpo.avgLeadDays, 7) + {$oboDays})
			WHEN salstats.calcAvgWeek > 0 THEN
			(salstats.calcAvgWeek / 7) * (IFNULL(leadpo.avgLeadDays, 7) + {$oboDays})
			ELSE 0
		END
		)";
		$this->CI->db->query($sql);
	}

	public function salesQuery($data) {
		$select = 'rr_ti.itemId AS itemId,' .($data['bId'] == $this->mainbid ? "" : " rr_t.cid, rr_t.bId,"). ' (rr_ti.qty+rr_ti.freeQty) * if(rr_itr.convFactor, rr_itr.convFactor, 1) AS qty, rr_t.tdate AS tdate';
		$outwardType= array('SI','SR','SD');
		$this->CI->db->select($select,false);
		$this->CI->db->from('trans AS rr_t');
		$this->CI->db->join('trans_items AS rr_ti','rr_t.id = rr_ti.transId');
		$this->CI->db->join('items AS rr_it','rr_ti.itemId = rr_it.itemId AND rr_it.isOboLevel ="Y"');
		if($data['bId'] != $this->mainbid) {
			$this->CI->db->join('items_cur AS rr_icur','rr_icur.itemId = rr_ti.itemId AND rr_t.cId = rr_t.cId AND rr_icur.bId=rr_t.bId');	
		}
		$this->CI->db->join('itemrelunits AS rr_itr','rr_itr.itemId = rr_ti.itemId AND rr_itr.relUnitName = rr_ti.unit','left');
		
		$where = array('rr_t.cId'=>$data["cId"],
		// 'rr_it.type' => 'G',
		'rr_it.isInventory' => 1,'rr_it.isOboLevel'=>'Y');
		$this->CI->db->where($where);
		if($data['bId'] == $this->mainbid) { 
			$this->CI->db->where('rr_t.bId',$data['bId']);
		}
		$this->CI->db->where_not_in('rr_t.status',array('D','X'));
			
		if($data['bId'] == $this->mainbid) {  
			$this->CI->db->where_in('rr_t.tType',$outwardType);	
		} else {
			$this->CI->db->group_start(); // Open bracket
				$this->CI->db->where_in('rr_t.tType',$outwardType);	
				$this->CI->db->or_group_start();
					$this->CI->db->where('rr_t.tType',"SH");
					$this->CI->db->where('rr_t.categoryCode',"TL");
					$this->CI->db->where('rr_t.conId != ',"rr_icur.supConId",false);
				$this->CI->db->group_end();	
			$this->CI->db->group_end(); //close bracket
		}

		$this->CI->db->where('rr_t.tDate >=' , $data['fromDate']);
		$this->CI->db->where('rr_t.tDate <=' , $data['toDate']);

		return $this->CI->db->get_compiled_select();

	}

	public function  manufacturingJournal($data) {

		$select = 'si.itemId AS itemId,' .($data['bId'] == $this->mainbid ? "" : " se.id AS cId, se.locId AS bId,"). '-(si.qty) * IF(itr.convFactor, itr.convFactor, 1) AS qty, se.sDate AS tDate';
		$this->CI->db->select($select,false);
		$this->CI->db->from('stock_entries AS se');
		$this->CI->db->join('stock_items AS si','si.sId = se.id');
		$this->CI->db->join('items AS rr_it','si.itemId = rr_it.itemId AND rr_it.isOboLevel ="Y"');
		if($data['bId'] == $this->mainbid) {
			$this->CI->db->join('items_cur AS rr_icur','rr_icur.itemId = si.itemId AND rr_icur.cId = si.cId AND rr_icur.bId = se.locId');	
		}
		$this->CI->db->join('itemrelunits AS itr','itr.itemId=si.itemId AND itr.relUnitName=si.unit','left');
		$this->CI->db->where('se.cId',$data["cId"]);
		if($data['bId'] == $this->mainbid) { 
			$this->CI->db->where('se.locid',$data['bId'] );
		}
		$this->CI->db->where('se.sType','MJ');
		$this->CI->db->where('se.sDate >=' , $data['fromDate']);
		$this->CI->db->where('se.sDate <=' , $data['toDate']);
		$this->CI->db->where('si.qty <',0);
		return $this->CI->db->get_compiled_select();
	}

	public function insertbackUpMessage() {
		$this->CI->load->model('oboMessageModel');
		$user_id =  0;
		$message = array();
		$message['bId'] = 0;
		$message['isTask'] = 1;		
		$messageParams =array('mType' =>'DB', 'cId' => 0,'toUid' => $user_id ,'toRoles'=>'A', 'message'=>json_encode($message),'priority'=>'H','isActive'=>'Y');
		$this->CI->oboMessageModel->notificationAlert($messageParams);
	}
	public function dailyMaintenance() {
		// echo $this->CI->db->last_query();exit;
		$start_date = $end_date = $cur_date = gmdate('Y-m-d');
		
		get_financial_dates($cur_date, $start_date, $end_date);
		
		$last_fi_start_date = date('Y-m-d', strtotime('-1 year', strtotime($start_date)));
		$last_fi_end_date = date('Y-m-d', strtotime('-1 year', strtotime($end_date)));
		// echo $last_fi_start_date;
		// echo $last_fi_end_date;
		// exit;
		
		$tmp = $this->CI->db->select('mDate')->from('messages')->where('mType','AY')->order_by('mDate','desc')->get()->row_array();
		$mDate = !empty($tmp) ? $tmp['mDate'] : '';

		$this->CI->db->select('t.cId,max(t.updatedOn) AS mud');
		$this->CI->db->from('trans AS t');
		$this->CI->db->where(array('t.tDate >= ' => $last_fi_start_date,'t.tDate <= ' => $last_fi_end_date,'t.tType != '=>"AO"));
		
		$this->CI->db->group_by('t.cId');
		if(!empty($mDate)) {
			$this->CI->db->having('mud >', '"'.$mDate.'"');
		} else {
			$this->CI->db->having('mud >', '"'.$start_date.'"');
			// $this->CI->db->where('mud >' ,$start_date);
		}
		
		// echo $this->CI->db->get_compiled_select();exit;
		$nonImportRecords = $this->CI->db->get()->result_array();
		// echo $this->CI->db->last_query();exit;
		if(!empty($nonImportRecords)) {
			foreach ($nonImportRecords as $key => $nonIR) {
				$this->insertNonImportMesageMessage($nonIR);
			}	
		}
		$this->alertStockLevelItems();
        return true;
	}

	public function insertNonImportMesageMessage($nonIR) {
		$this->CI->load->model('oboMessageModel');
		$user_id =  0;
		$this->CI->db->select("mainbId");
		$this->CI->db->from("companies");
		$this->CI->db->where('id',$nonIR["cId"]);
		$company = $this->CI->db->get()->row_array();
		$message = array('modifiedDate' => $nonIR['mud'],'bId' => $company['mainbId']);
		$message['isTask'] = 1;
		$messageParams =array('mType' =>'AY', 'cId' => $nonIR["cId"],'toUid' => $user_id ,'toRoles'=>'A', 'message'=>json_encode($message),'priority'=>'N','isActive'=>'Y');
		$this->CI->oboMessageModel->notificationAlert($messageParams);
	}

	public function alertStockLevelItems() {
		$this->CI->db->select("id,mainbid");
		$this->CI->db->from("companies");
		$companyList = $this->CI->db->get()->result_array();
		if(!empty($companyList)) {
			for ($i=0; $i < count($companyList); $i++) { 
				$cId = $companyList[$i]['id'];
				$this->mainbid = $companyList[$i]['mainbid'];
				$this->CI->db->select("id");
				$this->CI->db->from("contacts");
				$this->CI->db->where('cId',$cId);
				$this->CI->db->where_in('csType',array('B','F'));
				$branchList = $this->CI->db->get()->result_array();	
				if(count($branchList)) {
					$this->new_reorder($cId,count($branchList));
				} else {
					$this->new_reorder($cId);
				}
			}
		}
	}

	public function new_reorder($cId,$branchId = 0) {
		$orderListRange = '6';
		$cur_date 	= gmdate('Y-m-d');
		$page_limit = $this->CI->config->item('page_limit');
		if($branchId == $this->mainbid ) {
			$branchId = $this->mainbid;
		}
		$max_tDate = date('Y-m-d', strtotime('- '.$orderListRange.' months', strtotime(gmdate('Y-m-d'))));
		$salesOrderQuery =$this->salesOrderQuery($cId,$max_tDate,$branchId);
		$purchaseOrderQuery =$this->purchaseOrderQuery($cId,$max_tDate,$branchId);
		$sItemsStkQuery =$this->sItemsStockQuery(array("cId"=>$cId,"bId"=>$branchId));

		if($this->CI->db->conn_id->server_version >= 50800) {
			$bomInnerQuery = "SELECT itms.bId,bmi.ItemId as mItemId,/* Times of Prod Needed Qty SO-Stk-PO / BOM Prod Qty */
			sum((abs(bmi.qty) * IF(itbmi.convFactor, itbmi.convFactor, 1)) * (((pSO.soQty - GREATEST(IFNULL(itms.curStk, 0), 0) - IFNULL(pPO.poQty, 0)) DIV (bmp.qty * IF(itbmp.convFactor, itbmp.convFactor, 1))) + if((pSO.soQty - GREATEST(IFNULL(itms.curStk, 0), 0) - IFNULL(pPO.poQty, 0)) MOD (bmp.qty * IF(itbmp.convFactor, itbmp.convFactor, 1)) > 0,1,0))) AS mQty 
			FROM " .$this->CI->db->dbprefix('bom') ." AS bom 
			JOIN " .$this->CI->db->dbprefix('bom_items') ." AS bmi ON bmi.bomid = bom.id AND bmi.pType = 'C'
			JOIN " .$this->CI->db->dbprefix('bom_items') ." AS bmp ON bmp.bomid = bom.id AND bmp.pType = 'P'
			JOIN sItemsStk AS itms ON itms.itemId = bmp.itemId
			JOIN sSO AS pSO ON pSO.itemId = bom.itemId /* JOIN as SO is required. Not LEFT JOIN */
			LEFT JOIN ".$this->CI->db->dbprefix('itemrelunits')." AS itbmi ON itbmi.itemId = bmi.itemId AND itbmi.relUnitName = bmi.unit
			LEFT JOIN ".$this->CI->db->dbprefix('itemrelunits')." AS itbmp ON itbmp.itemId = bmp.itemId AND itbmp.relUnitName = bmp.unit
			LEFT JOIN sPO AS pPO ON pPO.itemId = bmp.itemId	where bom.bomType = 'P'	GROUP BY " .($branchId == $this->mainbid ? ''  : ' itms.bId,') ." bmi.itemId";

			$childQuery = "SELECT i.bId,i.itemId, i.itemName, i.sku,
			i.curStk AS curStk,i.stkLevel AS stkLevel,IFNULL(po.poqty, 0) AS poQty,
			IFNULL(so.soqty, 0) AS soQty, IFNULL(prdSO.mQty, 0) AS psoQty,
			IFNULL(i.curStk, 0) + IFNULL(po.poqty, 0) - IFNULL(i.stkLevel, 0) - IFNULL(so.soqty, 0) - IFNULL(prdSO.mQty, 0) AS result,
			IFNULL(i.stkLevel, 0) + IFNULL(po.poqty, 0) + IFNULL(i.curStk, 0) + IFNULL(so.soqty, 0) + IFNULL(prdSO.mQty, 0) AS totalVal /* To display or hide */ 
			FROM sItemsStk AS i
			LEFT JOIN sPO AS po ON po.itemId = i.itemid 
			LEFT JOIN sSO AS so ON so.itemId = i.itemid /* IF BOM get SO of Production Item Qty */
			LEFT JOIN (" . $bomInnerQuery .") AS prdSO ON prdSO.mItemId = i.itemId";
			$where = ' WHERE ';
			$whereMerge = '';
			
			$stockAlertQuery = "(SELECT 1 FROM ".$this->CI->db->dbprefix('trans')." AS t 
			JOIN ".$this->CI->db->dbprefix('trans_items')." AS ti ON t.id=ti.transid
			WHERE t.status NOT IN('D', 'X') AND
			t.tType IN('SI', 'SR', 'SD','SO','JO') AND
			t.cid = ". $cId ." AND t.tDate >= DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND
			ti.itemId=i.itemId UNION ALL SELECT 1
			FROM ".$this->CI->db->dbprefix('stock_entries')." AS se
			JOIN  ".$this->CI->db->dbprefix('stock_items')." AS si ON si.sId=se.id
			WHERE se.cid = ". $cId ." AND se.sDate >=  DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND 
			si.qty < 0 AND si.itemId=i.itemId)";
			$where .= $whereMerge. ' EXISTS '.$stockAlertQuery;


			$orderby = ' ORDER BY i.itemName ASC';
			$having = "";
			$havingJoin = "";
			if(empty($having)) {
				$having = " HAVING";
			}
			$having .= $havingJoin." result < 0";
			$havingJoin = ' AND'; 
			$final_SO_query = "WITH sPO AS ( /* PO */". $purchaseOrderQuery ."), 
			sSO AS ( /* SO */ ".$salesOrderQuery."), sItemsStk AS (".$sItemsStkQuery.") 
			SELECT t.bId, count(t.itemId) as itemCnt FROM (".$childQuery.$where ." GROUP BY " . ($branchId == $this->mainbid ? '' : 'i.bId,') ."i.itemId "  .$having.") AS t ".($branchId == $this->mainbid ? ';' :  'GROUP BY t.bId;') ;
		} else {
			$bomInnerQuery = "SELECT bmi.ItemId as mItemId,/* Times of Prod Needed Qty SO-Stk-PO / BOM Prod Qty */
			sum((abs(bmi.qty) * IF(itbmi.convFactor, itbmi.convFactor, 1)) * (((pSO.soQty - GREATEST(i.curStk + IFNULL(icur.curStk, 0), 0) - IFNULL(pPO.poQty, 0)) DIV (bmp.qty * IF(itbmp.convFactor, itbmp.convFactor, 1))) + if((pSO.soQty - GREATEST(i.curStk + IFNULL(icur.curStk, 0), 0) - IFNULL(pPO.poQty, 0)) MOD (bmp.qty * IF(itbmp.convFactor, itbmp.convFactor, 1)) > 0,1,0))) AS mQty 
			FROM " .$this->CI->db->dbprefix('bom') ." AS bom 
			JOIN " .$this->CI->db->dbprefix('bom_items') ." AS bmi ON bmi.bomid = bom.id AND bmi.pType = 'C'
			JOIN " .$this->CI->db->dbprefix('bom_items') ." AS bmp ON bmp.bomid = bom.id AND bmp.pType = 'P'
			JOIN " .$this->CI->db->dbprefix('items') ." AS i ON i.itemId = bmp.itemId
			LEFT JOIN (SELECT  ic.itemId, ic.bId, sum(ic.curstk) AS curstk FROM ".$this->CI->db->dbprefix('items_cur') . " AS ic JOIN ".$this->CI->db->dbprefix('contacts') ." AS c ON c.id = ic.bId
			and c.csType = 'L' AND c.bId = ic.bId GROUP BY ic.itemId,ic.bId) AS icur ON icur.itemId = i.itemId
			JOIN ( /* SO */ ".$salesOrderQuery."  /* SO END */) AS pSO ON pSO.itemId = bom.itemId
			LEFT JOIN ".$this->CI->db->dbprefix('itemrelunits')." AS itbmi ON itbmi.itemId = bmi.itemId AND itbmi.relUnitName = bmi.unit
			LEFT JOIN ".$this->CI->db->dbprefix('itemrelunits')." AS itbmp ON itbmp.itemId = bmp.itemId AND itbmp.relUnitName = bmp.unit
			LEFT JOIN ( /* PO */". $purchaseOrderQuery ."/* PO END */) AS pPO ON pPO.itemId = bmp.itemId WHERE bom.bomType = 'P' GROUP BY bmi.itemId";

			$childQuery = "SELECT i.itemId, i.name AS itemName , i.sku, i.uom AS unit,i.purchaseRate,i.isOboLevel,i.minOrdQty,
			i.curStk + IFNULL(icur.curStk, 0) as curStk,IFNULL(icur.bId, 0) as bId,IFNULL(i.stkLevel, 0) AS stkLevel,IFNULL(po.poqty, 0) AS poQty,
			IFNULL(so.soqty, 0) AS soQty, IFNULL(prdSO.mQty, 0) AS psoQty,
			IFNULL(i.curStk, 0) + IFNULL(po.poqty, 0) - IFNULL(i.stkLevel, 0) - IFNULL(so.soqty, 0) - IFNULL(prdSO.mQty, 0) AS result,
			IFNULL(i.stkLevel, 0) + IFNULL(po.poqty, 0) + IFNULL(i.curStk, 0) + IFNULL(so.soqty, 0) + IFNULL(prdSO.mQty, 0) AS totalVal /* To display or hide */ 
			FROM " .$this->CI->db->dbprefix('items') ." AS i 
			LEFT JOIN (SELECT  ic.itemId, ic.bId, sum(ic.curstk) AS curstk FROM ".$this->CI->db->dbprefix('items_cur') . " AS ic JOIN ".$this->CI->db->dbprefix('contacts') ." AS c ON c.id = ic.bId
			and c.csType = 'L' AND c.bId = ic.bId GROUP BY ic.itemId,ic.bId) AS icur ON icur.itemId = i.itemId
			LEFT JOIN ( /* PO */". $purchaseOrderQuery ."/* PO END*/) AS po ON po.itemId = i.itemid 
			LEFT JOIN ( /* SO */ ".$salesOrderQuery." /* SO END*/) AS so ON so.itemId = i.itemid /* IF BOM get SO of Production Item Qty */
			LEFT JOIN (" . $bomInnerQuery .") AS prdSO ON prdSO.mItemId = i.itemId";
			$where = ' WHERE i.isActive > 0 AND i.cId = '. $cId .' AND i.isInventory = 1 AND i.type = "G"';
			$whereMerge = " AND ";
			if(empty($where)) {
				$where = ' WHERE ';
			}

			$stockAlertQuery = "(SELECT 1 FROM ".$this->CI->db->dbprefix('trans')." AS t 
			JOIN ".$this->CI->db->dbprefix('trans_items')." AS ti ON t.id=ti.transid
			WHERE t.status NOT IN('D', 'X') AND
			t.tType IN('SI', 'SR', 'SD','SO','JO') AND
			t.cid = ". $cId ." AND t.tDate >= DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND
			ti.itemId=i.itemId UNION ALL SELECT 1
			FROM ".$this->CI->db->dbprefix('stock_entries')." AS se
			JOIN  ".$this->CI->db->dbprefix('stock_items')." AS si ON si.sId=se.id
			WHERE se.cid = ". $cId ." AND se.sDate >=  DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND 
			si.qty < 0 AND si.itemId=i.itemId)";
			$where .= $whereMerge. ' EXISTS '.$stockAlertQuery;


			$orderby = ' ORDER BY i.name ASC';
			$having = "";
			$havingJoin = "";
			if(empty($having)) {
				$having = " HAVING";
			}
			$having .= $havingJoin." result < 0";
			$havingJoin = ' AND';
			$final_SO_query = "SELECT IFNULL(t.bId,0)  as bId, count(t.itemId) as itemCnt FROM (".$childQuery.$where." GROUP BY " . ($branchId == $this->mainbid ? '' : 'bId,') ."itemId "  .$having.") AS t ".($branchId == $this->mainbid ? ';' :  'GROUP BY t.bId;') ;

		}
	
		
		// echo "<br><br><br>";
		// print_r($final_SO_query);
		$query_list =	$this->CI->db->query($final_SO_query);
		$result = $query_list->result_array();

		if(!empty($result) && count($result) > 0) {
			for ($i=0; $i < count($result); $i++) {
				if($result[$i]['itemCnt']) {
					$result[$i]['bId'] = $result[$i]['bId'] ? (int) $result[$i]['bId'] : $result[$i]['bId'];
					$this->insertStockAlertMessage($cId,$result[$i]);
				}
			}
		}	
	}

	public function insertStockAlertMessage($cId,$params) {
		$this->CI->load->model('oboMessageModel');
		$user_id =  0;
		$message = array('itemCnt' => $params['itemCnt'],'bId' => $params["bId"] ? $params["bId"] : 0);
		$messageParams =array('mType' =>'SA', 'cId' => $cId,'toUid' => $user_id ,'toRoles'=>'A', 'message'=>json_encode($message),'priority'=>'N','isActive'=>'Y');
		$this->CI->oboMessageModel->notificationAlert($messageParams);
	}

	public  function salesOrderQuery($cId,$tdate,$bId = 0) {
        $salesOrderdeliveredQuery = $this->salesOrderdeliveredQuery();
		$groupBy = "";
		if(empty($bId)){
			$bId = $this->mainbid;
		}
		
		if((!empty($bId) && $bId != $this->mainbid)) {
			$groupBy .= "p_t.bId,";
		}
		$groupBy .= "p_ti.itemId";
        $this->CI->db->select("p_ti.itemId, sum(((IFNULL(p_ti.qty,0)+IFNULL(p_ti.freeQty, 0))  * IF(itr.convFactor, itr.convFactor, 1))  - IFNULL(dt.recivQty,0)) AS soQty");
        $this->CI->db->from('trans AS p_t');
        $this->CI->db->join('trans_items AS p_ti','p_ti.transId=p_t.id');
        $this->CI->db->join('itemrelunits AS itr','itr.itemId=p_ti.itemId AND itr.relUnitName=p_ti.unit','left');
        $this->CI->db->join('(/* SO DELIVERED */'. $salesOrderdeliveredQuery .' ) AS dt','dt.refTransId=p_t.id and dt.refTitemId=p_ti.id','left');
        $this->CI->db->where(array('p_t.tType'=>'SO','p_t.cid'=>$cId,'p_t.tDate >'=>$tdate));
        $this->CI->db->where_in('p_t.status', array('S','R'));
        $this->CI->db->group_by($groupBy);
        return $this->CI->db->get_compiled_select();
    }

	public function salesOrderdeliveredQuery() {
        $outwardtypes = array("SH", "SI", "SR", "SD");
        $this->CI->db->select("d_trf.refTransId, d_ti.refTitemId,sum((IFNULL(d_ti.qty,0)+IFNULL(d_ti.freeQty, 0)) * IF(itr.convFactor, itr.convFactor, 1)) AS recivQty");
        $this->CI->db->from('trans AS d_t');
        $this->CI->db->join('trans_items AS d_ti','d_ti.transId=d_t.id');
        $this->CI->db->join('trans_ref AS d_trf','d_trf.transId=d_t.id');
        $this->CI->db->join('itemrelunits AS itr','itr.itemId=d_ti.itemId and itr.relUnitName=d_ti.unit','left');
        $this->CI->db->where_in('d_t.tType',$outwardtypes);
        $this->CI->db->group_by('d_ti.refTitemId');
        return $this->CI->db->get_compiled_select();
    }

	public  function purchaseOrderQuery($cId,$tdate,$bId = 0) {
        $purchaseOrderConsumedQuery = $this->purchaseOrderConsumedQuery();
		$groupBy = "";
		if(empty($bId)){
			$bId = $this->mainbid;
		}
		if((!empty($bId) && $bId != $this->mainbid)) {
			$groupBy .= "p_t.bId,";
		}
		$groupBy .= "p_ti.itemId";
        $this->CI->db->select("p_ti.itemId, sum(((IFNULL(p_ti.qty,0)+IFNULL(p_ti.freeQty, 0))  * IF(itr.convFactor, itr.convFactor, 1))  - IFNULL(dt.recivQty,0)) AS poQty");
        $this->CI->db->from('trans AS p_t');
        $this->CI->db->join('trans_items AS p_ti','p_ti.transId=p_t.id');
        $this->CI->db->join('itemrelunits AS itr','itr.itemId=p_ti.itemId AND itr.relUnitName=p_ti.unit','left');
        $this->CI->db->join('(/* PO CONSUMED*/'. $purchaseOrderConsumedQuery .' ) AS dt','dt.refTransId=p_t.id and dt.refTitemId=p_ti.id','left');
        $this->CI->db->where(array('p_t.tType'=>'PO','p_t.cid'=>$cId,'p_t.tDate >'=>$tdate));
        $this->CI->db->where_in('p_t.status', array('S','R'));
        $this->CI->db->group_by($groupBy);
        return $this->CI->db->get_compiled_select();
    }

	public function purchaseOrderConsumedQuery() {
        $inwardypes = array("GR","PI","PE","PD");
        $this->CI->db->select("d_trf.refTransId, d_ti.refTitemId,sum((IFNULL(d_ti.qty,0)+IFNULL(d_ti.freeQty, 0)) * IF(itr.convFactor, itr.convFactor, 1)) AS recivQty");
        $this->CI->db->from('trans AS d_t');
        $this->CI->db->join('trans_items AS d_ti','d_ti.transId=d_t.id');
        $this->CI->db->join('trans_ref AS d_trf','d_trf.transId=d_t.id');
        $this->CI->db->join('itemrelunits AS itr','itr.itemId=d_ti.itemId and itr.relUnitName=d_ti.unit','left');
        $this->CI->db->where_in('d_t.tType',$inwardypes);
        $this->CI->db->group_by('d_ti.refTitemId');
        return $this->CI->db->get_compiled_select();
    }


	public function sItemsStockQuery($params) {
		
		$this->CI->db->select('i.itemId, i.name AS itemName,i.SKU,i.cId,i.isActive,i.isInventory,i.type,i.curStk ,i.stkLevel,'.$this->mainbid.' as bId');
		$this->CI->db->from('items AS i');
		$this->CI->db->where(array('i.isActive >'=>0,'i.cId'=>$params["cId"],'i.isInventory'=>1
				//, 'i.type'=>'G'
	));
		$mainBranchItemQuery = $this->CI->db->get_compiled_select();
		$finalSitemsQuery = "";
		if(!empty($params["bId"]) && $params["bId"] != $this->mainbid) {
			$select = 'i.itemId, i.name AS itemName,i.SKU,i.cId,i.isActive,i.isInventory,i.type,IF(c.csType = "L", c.bId, icur.bId) AS bId,
			IFNULL(icur.curStk, 0) AS curstk,IFNULL(icur.stkLevel, 0) AS stkLevel';
			$this->CI->db->select($select);
			$this->CI->db->from('items AS i');
			$this->CI->db->join('items_cur AS icur','icur.itemId = i.itemId');
			$this->CI->db->join('contacts AS c','c.id = icur.bId ');
			$this->CI->db->where(array('i.isActive >'=> 0,'i.cId'=>$params["cId"],'i.isInventory'=>1
			// ,'i.type'=>'G'
		));
			$otherBranchItemQuery = $this->CI->db->get_compiled_select();
			$finalSitemsQuery = "SELECT itemId,itemName,SKU,cId,isActive,isInventory,type,bid,sum(curstk) as curStk,stkLevel FROM 
			(". $mainBranchItemQuery." UNION ALL " . $otherBranchItemQuery . ") AS it GROUP BY bId,itemId";
		
		} else {
			$finalSitemsQuery = $mainBranchItemQuery;
		}
		return $finalSitemsQuery;
		
	}

	private function error_log($log) {
		$this->CI->load->helper('file');
        $file_path = 'temp'.DS.'cron'.DS.'Maintenance'.DS.$log['cId'];

        if(!is_dir($file_path))
        {
            mkdir($file_path, 0755, TRUE);
		}
		
		$output ="=====================".base_url(uri_string())."=====================\n";
		$output.=$log['error']."\n";
		if ( ! write_file(FCPATH  . $file_path."/log.txt", $output, 'a+')){
            log_message('debug','Unable to write query the file');
        }
	}
}
