<?php
class SpecialClass {

const POStatusDespatch	= 'S';			// Accessory Manually Completed
const DTUserM = 'b2b_solar_sc.so_user_m'; // DT:DatabaseTable. Some Masters are always to be used (even for Non-TradeSector) from b2b_solar_sc
const DTUserTypeM = 'b2b_solar_sc.so_user_type_m';
const DTSectorM = 'b2b_solar_sc.so_sector_m';

private 	$objDBQuery;
private		$dbName;
private		$classBusiLogic;
	
public function __construct($objDBQuery, $dbName, $classBusiLogic) {
	$this->objDBQuery			= $objDBQuery;
	$this->dbName				= $dbName;
	$this->classBusiLogic		= $classBusiLogic;
	date_default_timezone_set('Asia/Kolkata');
}


public function StockExist($CompanyCd, $SectorCd= null, $DTS){
		$where_str = '';
		if(isset($SectorCd) && $SectorCd !=''){
			$where_str = "AND A.SectorCd IN ('".$SectorCd."')";
		}
		
		$sql = "select A.*, B.Descript AS SectorName FROM scm_stock_sales AS A, scm_sector_m AS B where A.SectorCd = B.SectorCd AND CompanyCd = '".$CompanyCd."' ".$where_str." AND A.DateAdded LIKE '%".$DTS."%' ";
	
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getStockDetails($StockSalesID){
		$sql = "select A.ItemCd, B.Descript, A.Stock, A.Trail, A.Hold, A.Dispatch FROM scm_stock_sales_item AS A, scm_item_m AS B where A.ItemCd = B.ItemCd AND A.StockSalesID = '".$StockSalesID."' ORDER By B.DispOrder ";
	
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

//header message display 
public function getHeaderMessageData() {
	$where = ($_SESSION['IsSubDealer']) ? " AND ForSubdealer=1" : "";
	$sql		= "SELECT Message FROM so_login_message WHERE MessageType='H' AND IsActive = 1 ".$where." order by IdMessage desc";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	$message_data = array();
	if(is_array($returnData)){
		foreach($returnData as $index => $value){
			$color = $this->RandomColor();
			$message_data['Message'][$index] = '<span style="color:'.$color.';">'.$returnData[$index]['Message'].'</span>';
		}

		$returnData = $message_data;

	}else{
		return $returnData;
	}
	
	return $returnData;
} 

function RandomColor() {
	$str = '#';
	for($i = 0 ; $i < 6 ; $i++) {
		$randNum = rand(0 , 15);
		switch ($randNum) {
			case 10: $randNum = 'A'; break;
			case 11: $randNum = 'B'; break;
			case 12: $randNum = 'C'; break;
			case 13: $randNum = 'D'; break;
			case 14: $randNum = 'E'; break;
			case 15: $randNum = 'F'; break;
		}
		$str .= $randNum;
	}
	return $str;
}

public function createDateRangeArray($strDateFrom,$strDateTo){
    // takes two dates formatted as YYYY-MM-DD and creates an
    // inclusive array of the dates between the from and to dates.

    // could test validity of dates here but I'm already doing
    // that in the main script


    $aryRange=array();

    $iDateFrom=mktime(1,0,0,substr($strDateFrom,5,2),     substr($strDateFrom,8,2),substr($strDateFrom,0,4));
    $iDateTo=mktime(1,0,0,substr($strDateTo,5,2),     substr($strDateTo,8,2),substr($strDateTo,0,4));
	

    if ($iDateTo>=$iDateFrom)
    {
        array_push($aryRange,date('Y-m-d',$iDateFrom)); // first entry
        while ($iDateFrom<$iDateTo)
        {
            $iDateFrom+=86400; // add 24 hours
            array_push($aryRange,date('Y-m-d',$iDateFrom));
        }
    }
	
	return $aryRange;
}

//Get company for export plant
public function getExportCompanyMDisp($CompanyCd=NULL) {
	$sql = "SELECT * FROM so_company_m" . ((isset($CompanyCd)) ? " WHERE CompanyCd IN ($CompanyCd)" : "");
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;	
}

public function getItem($CompanyCd = '')
    {
        // Always send UOM
		$where_str = '';
		if($CompanyCd !=''){
			$where_str .= " Where CompanyCd ='".$CompanyCd."' ";
		}
        $sql = "SELECT ItemCd,Descript,UOM FROM so_item_m ".$where_str." ORDER BY Descript";

        $returnData = $this->classBusiLogic->execSelectQuery($sql);
        return $returnData;
    }
	
public function getItemExp($CompanyCd = '')
    {
        // Always send UOM
		$where_str = '';
		if($CompanyCd !=''){
			$where_str .= " Where CompanyCd ='".$CompanyCd."' ";
		}
        $sql = "SELECT ItemId, ItemCd,Descript,UOM FROM  exb2b_solar_sc.so_re11_item_m ".$where_str." ORDER BY Descript";

        $returnData = $this->classBusiLogic->execSelectQuery($sql);
        return $returnData;
    }
//report display
public function reportMonthlyAccessorySaleDealer($DTS, $DTE, $CompanyCd, $CustomerCd = 0)
    {
        $where_str        = '';
        $DTS              = $this->classBusiLogic->convertDate($DTS, TRUE, 1);
        $DTE              = $this->classBusiLogic->convertDate($DTE, TRUE, 1);
        $POStatusDespatch = self::POStatusDespatch;
        if (isset($CustomerCd) && $CustomerCd != 0) {
            $where_str .= "AND socm.CustomerCd='" . $CustomerCd . "' ";
        }
              
        $sql = "SELECT socm.CustomerCd AS Customer, soa.IdAccessory As PO_No, socm.Descript AS Dealer, socm.City AS Station, soacm.Descript AS ItemName,
		soacm.HasLength, soa.TotAmt AS Total_Amount, soa.TotBox AS Total_Box, sum(soai.DespatchedQty * soam.Length) AS Total_Mtr,
		sum(soai.DespatchedQty) AS Total_Qty, DATE_FORMAT(soa.SAPInvDate,'%d-%m-%Y') as SAP_Inv_Date, soa.SAPInvNo AS SAP_Inv_No,
		DATE_FORMAT(soa.Date,'%d-%m-%Y %H:%i:%s') AS PO_Date FROM so_accessory AS soa LEFT OUTER JOIN (so_accessory_ctg_m AS soacm,
		so_accessory_m AS soam, so_customer_m AS socm, so_accessory_item AS soai) ON (soa.AccessoryCtgCd = soacm.AccessoryCtgCd AND 
		soa.AccessoryCtgCd = soam.AccessoryCtgCd AND soa.CustomerCd = socm.CustomerCd AND soa.IdAccessory = soai.IdAccessory AND 
		soai.AccessoryCd = soam.AccessoryCd) WHERE soacm.CompanyCd='" . (int) $CompanyCd . "' AND soa.StatusCd = '" . $POStatusDespatch . "'
		AND soa.SAPInvDate <> ''AND soa.SAPInvDate >='" . $DTS . "' AND soa.SAPInvDate <='" . $DTE . "' " . $where_str . " 
		GROUP BY soa.IdAccessory ORDER BY socm.CustomerCd, soa.Date";
        
		
		      
        $returnData = $this->classBusiLogic->execSelectQuery($sql);
        If (count($returnData) == 0) {
            echo "<script>alert('No record found for the dealer!!!.')</script>";
            return;
        } else {
            return $returnData;
        }
    }

	 public function shortreportDailyStock($SectorCd, $DTSDays, $DTE, $IsAED)
    {
        
		$DTEAnsi    = $this->classBusiLogic->convertDate($DTE, TRUE, 1);
        $DTS        = $this->classBusiLogic->addDate($DTEAnsi, '-' . $DTSDays . ' day', TRUE);
        $inputArr   = array(
            $SectorCd,
            $DTSDays,
            $DTEAnsi,
            $IsAED
        );
        $paraString = vsprintf("'%s', %s, '%s', %s", $inputArr);
        $sql        = "call sp_shortrepDailyStockExpl(" . $paraString . ")"; // Returns 4 Recordsets 0:@ColumnStru,... (to ignore) 1:DailyStockDetails 2:GrandTotal 3:NoDailyStockCustomerList
		
        $preResults = '';
        $returnData = $this->classBusiLogic->execSelectQuery($sql, 2, $preResults);
	    return $preResults;
    }

	// Subdealer Traget Monthly ---------------------------------------------------------------------------------------------------------------------------------------

	public function getSubDealerItemMDisp($SubdealerCd=''){

		//$sql = "SELECT Descript, ShortDescript from so_item_m WHERE IsforSubDealer = 1 and IsActive = 1";
		$sql = "SELECT C.ItemCd, D.Descript,  D.ShortDescript, D.UOM, D.UOMType, D.GroupDesc, A.SectorCd FROM so_customer_m AS A, so_subdealer_m_dealer AS B, so_customer_m_item_rate_type AS C, so_item_m AS D WHERE B.IdSubdealer = '".$SubdealerCd."' AND A.CustomerCd = B.CustomerCd AND C.CustomerCd = B.CustomerCd AND D.ItemCd = C.ItemCd AND D.GroupDesc <> '' AND D.IsforSubDealer=1 AND D.IsActive = 1 Group By C.ItemCd ORDER BY D.DispOrder";
		//$sql = "SELECT C.ItemCd, D.Descript,  D.ShortDescript, D.UOM, D.UOMType, D.GroupDesc, A.SectorCd FROM so_customer_m AS A, so_subdealer_m_dealer AS B, so_customer_m_item_rate_type AS C, so_item_m AS D WHERE B.IdSubdealer = '".$SubdealerCd."' AND A.CustomerCd = B.CustomerCd AND C.CustomerCd = B.CustomerCd AND D.ItemCd = C.ItemCd AND D.GroupDesc <> '' AND D.IsforSubDealer=1 AND D.IsActive = 1 Group By C.ItemCd ORDER BY D.DispOrder";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;	

	}

	public function getSubDealerCustomerMDisp($SubdealerCd='', $CustomerCd='',$filterCond='') {
	// flagColumns: CustomerCd,Descript,SectorCd,HasSubdealer,HasDailyStock always. 1:IsInState,TaxType,IsRE11MustInPO, 2:MaxMonthlyTargetDate,MaxDailyStockDateTime
	// Preference. $CustomerCd, $filterCond, HasCustomer
		$searchCnd = '';
		$searchCnd	.= ($CustomerCd) ? " AND A.CustomerCd='" .$CustomerCd. "' AND A.IsActive=1" : "";

		$sql = "SELECT A.CustomerCd,A.Descript AS Customer, A.City AS Station, A.SectorCd FROM so_customer_m AS A, so_subdealer_m_dealer AS B, so_subdealer_m AS C WHERE  B.IdSubdealer = '".$SubdealerCd."' AND C.IdSubdealer = B.IdSubdealer AND C.IsActive=1 ".$searchCnd." AND A.CustomerCd = B.CustomerCd ORDER BY A.Descript";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;	
	}

	public function getSubDealerTargetMonthList($search, $TargetForMY) {
		$DTSAnsi		= preg_replace('/(\d{2})[-\/](\d{4})/', "$2-$1", $TargetForMY) . '-01';
		$Year			= $this->getSubDealerTargetYear($DTSAnsi, $curMonth, $curLogMonth);
		$searchCnd	= " WHERE A.Year='" . $Year . "' AND A.Month=" . $curLogMonth;
		$searchCnd	.= (isset($search)) ? " AND B.Descript like '". $search . "%' AND A.YMFlag=1" : " AND A.YMFlag=1";
		$searchCnd	.= ($_SESSION['IsSubDealer']) ? " AND A.SubdealerCd='" .$_SESSION['SubdealerCd']. "'" : "";
		$searchCnd	.= ($_SESSION['IsCustomer']) ? " AND A.CustomerCd='" .$_SESSION['CustomerCd']. "'" : "";

		switch ($_SESSION['StatusFilter']) {
		case 0:
			break;
		case 2:
			$searchCnd	.= " AND A.StatusCd='A'";
			break;
		default:		// 1
			$searchCnd	.= " AND A.StatusCd='U'";
			break;
		}
		$sql = "SELECT A.SubdealerCd, A.CustomerCd,A.Year, A.Month, DATE_FORMAT(CONCAT('2012-',(A.Month +2)%12 +1,'-01'),'%b') AS MonthName,IFNULL(B.Descript,A.CustomerCd) AS Customer,IFNULL(E.Descript,A.SubdealerCd) AS Subdealer, IFNULL(C.Descript,A.StatusCd) AS Status, E.AllowTrgt FROM so_subdealer_target A LEFT OUTER JOIN so_subdealer_m E ON A.SubdealerCd=E.IdSubdealer LEFT OUTER JOIN so_customer_m B ON A.CustomerCd=B.CustomerCd LEFT OUTER JOIN so_status_m C ON A.StatusCd=C.StatusCd LEFT OUTER JOIN so_state_m D ON B.StateCd=D.StateCd LEFT OUTER JOIN so_subdealer_target_item F ON (A.SubdealerCd = F.SubdealerCd AND A.CustomerCd = F.CustomerCd AND A.Year = F.Year AND A.Month =F.Month) " .$searchCnd. " GROUP BY A.CustomerCd,A.SubdealerCd ORDER BY A.Year DESC, A.Month DESC, B.IsActive DESC, E.Descript, D.DispOrder, B.CustomerCtgCd, B.Descript";



		$returnData = $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getSubDealerTargetMonthItemStock($SubdealerCd, $CustomerCd='', $Year, $Month) {

	// Return complete details of Item Stock fill by Subdealer. Optionally return Items of Default Subdealer also. This along with specified Month Stock details
		$searchCnd	= '';
		$col_list = '';
		$table_name = '';
		if($CustomerCd){
			$col_list .= ", C.OwnQuantity, C.ApprovedQty ";
			$table_name .= ", so_subdealer_target_item AS C";
			$searchCnd	.= ($CustomerCd) ? " AND C.CustomerCd='" .$CustomerCd. "'" : "";
			$searchCnd	.= " AND C.SubdealerCd = A.SubdealerCd AND C.Year = A.Year AND C.Month = A.Month AND C.ItemCd = B.ItemCd";
		}

		//$sql = "SELECT Descript, ShortDescript from so_item_m WHERE IsforSubDealer = 1 and IsActive = 1";
		$sql = "SELECT A.ItemCd, A.SubdealerCd, B.Descript, B.ShortDescript, B.UOM, B.UOMType, B.GroupDesc, A.MonthStock AS Stock ".$col_list." FROM so_subdealer_target_item_stock A, so_item_m AS B ".$table_name." WHERE A.SubdealerCd = '".$SubdealerCd."' ".$searchCnd." AND A.Year = '".$Year."' AND A.Month = '".$Month."' AND B.ItemCd = A.ItemCd ORDER BY B.Descript";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getSubDealerTargetMonthCustomer($SubdealerCd, $CustomerCd = '', $Year, $Month, $StatusCd = '') {
		// Return complete details of Customer + Company. Optionally return Items of Default Customer also. This along with specified Month details
		$searchCnd = '';
		$searchCnd	.= ($CustomerCd) ? " AND A.CustomerCd='" .$CustomerCd. "'" : "";
		$sql = "SELECT A.CustomerCd, A.SubdealerCd, IFNULL(B.Descript,A.CustomerCd) AS Customer, B.City AS Station, B.SectorCd FROM so_subdealer_target A, so_customer_m AS B WHERE A.SubdealerCd = '".$SubdealerCd."' ".$searchCnd." AND A.Year = '".$Year."' AND A.Month = '".$Month."' AND  B.CustomerCd = A.CustomerCd";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getSubDealerTargetMonthHeader($SubdealerCd = '', $CustomerCd = '', $Month, $Year) {
		$searchCnd='';
		$searchCnd	.= ($SubdealerCd !='') ? " AND SubdealerCd='" .$SubdealerCd. "'" : "";
		$searchCnd	.= ($CustomerCd !='') ? " AND CustomerCd='" .$CustomerCd. "'" : "";
		$sql		= "SELECT * FROM so_subdealer_target WHERE YMFlag=1 AND Year='".$Year."' AND Month=".(int)$Month ." ".$searchCnd;
	
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function isExistSubDealerTargetMonth($SubdealerCd, $Month, $Year) {
		$sql			= "SELECT * FROM so_subdealer_target WHERE SubdealerCd='".$SubdealerCd."' AND YMFlag=1 AND Year='".$Year."' AND Month=".(int)$Month;
		$queryResult	= $this->classBusiLogic->execSelectQuery($sql);
		$rowCount		= count($queryResult);
		
		return $rowCount;
	}

	public function insertSubDealerTargetMonth($data) {
		foreach($data['CustomerCd'] as $index => $value){
			$Status = (isset($data['StatusCd']) ? $data['StatusCd'] : 'U');
			$stmt	= "INSERT INTO so_subdealer_target (SubdealerCd,CustomerCd, Year, YMFlag, Month, Date, StatusCd, TransUserName, TransDateTime)". " VALUES ('%s','%s', '%s', %s, %s, '%s', '%s', '%s', '%s');";
			$inputArr	= array($_SESSION['SubdealerCd'],$data['CustomerCd'][$index], $data['Year'], 1, (int)$data['Month'], $this->classBusiLogic->convertDate($data['Date'],TRUE), $Status, $_SESSION['UserName'], date("Y-m-d h:i:s"));
			$sql		= vsprintf($stmt,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		}
		return $returnData;
	}

	public function insertSubDealerTargetMonthItem($data) {
		foreach($data['CustomerCd'] as $index => $value){
			$queryInsert	= "INSERT INTO so_subdealer_target_item(SubdealerCd,CustomerCd, Year, YMFlag, Month, ItemCd, OwnQuantity)". " VALUES ('%s','%s', '%s', %s, %s, '%s', %s);";
			$returnData		= '';
			
			for ($curRowNo=0; $curRowNo<sizeof($data['ItemCd']); $curRowNo++) {
				$ItemCd		= $data['ItemCd'][$curRowNo];
				$OwnQuantity	= (float)$data['Target_'.$index.'_'.$curRowNo];			
				$inputArr	= array();
				$inputArr	= array($data['SubdealerCd'], $data['CustomerCd'][$index], $data['Year'], 1, (int)$data['Month'], $ItemCd, $OwnQuantity);
				$sql		= vsprintf($queryInsert, $inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}
		}
	return $returnData;
	}

	public function insertSubDealerTargetMonthItemStock($data) {
		
		$queryInsert	= "INSERT INTO so_subdealer_target_item_stock(Year, SubdealerCd, ItemCd, YMFlag, Month, MonthStock)". " VALUES ('%s', '%s', '%s', %s, %s, %s);";
		$returnData		= '';
			
		for ($curRowNo=0; $curRowNo<sizeof($data['ItemCd']); $curRowNo++) {
			$ItemCd		= $data['ItemCd'][$curRowNo];
			$MonthStock	= (float)$data['Stock'][$curRowNo];			
			$inputArr	= array();
			$inputArr	= array( $data['Year'], $data['SubdealerCd'], $ItemCd, 1, (int)$data['Month'], $MonthStock);
			$sql		= vsprintf($queryInsert, $inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			if ($returnData) return $returnData;
		}
	return $returnData;
	}

	public function updateSubDealerTargetMonth($data) {
		$Status = (isset($data['StatusCd']) ? $data['StatusCd'] : 'U');
		$Status = (isset($data['StatusCd']) ? $data['StatusCd'] : 'U');
		
		$stmt	= "UPDATE so_subdealer_target SET StatusCd='%s', TransUserName='%s', TransDateTime='%s'". " WHERE CustomerCd='%s' AND SubdealerCd = '%s' AND Year='%s' AND YMFlag=1 AND Month=%s";
		$inputArr	= array($Status, $_SESSION['UserName'], date("Y-m-d H:i:s"), $data['oldCustomerCd'], $data['oldSubDealerCd'], $data['oldYear'], $data['oldMonth']);
		$sql = vsprintf($stmt,$inputArr);

		$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		return $returnData;
	}

	public function updateSubDealerTargetMonthItem($data) {

		foreach($data['CustomerCd'] as $index => $value){
			$stmt	= "UPDATE so_subdealer_target_item SET ApprovedQty='%s' WHERE CustomerCd='%s' AND SubdealerCd = '%s' AND ItemCd = '%s' AND Year='%s' AND YMFlag=1 AND Month=%s";
			for ($curRowNo=0; $curRowNo<sizeof($data['ItemCd']); $curRowNo++) {

				$ItemCd		= $data['ItemCd'][$curRowNo];
				$ApproveTarget	= $data['ApproveTarget_'.$index.'_'.$curRowNo];				
				$inputArr	= array($ApproveTarget, $data['oldCustomerCd'], $data['oldSubDealerCd'] ,$ItemCd, $data['oldYear'], $data['oldMonth']);
				$sql = vsprintf($stmt,$inputArr);

				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			}
		}
		
		return $returnData;
	}


	public function approveSubDealerTargetMonth($condTarget, $isApproved) {

		$stmt		= "UPDATE so_subdealer_target SET StatusCd='%s', ApprovedByUserName='%s', ApprovalDateTime='%s' WHERE ".$condTarget;
		if ($isApproved)
			$inputArr 	= array('A', $_SESSION['UserName'], date('Y-m-d H:i:s'));
		else
			$inputArr 	= array('U', '', '');
		$sql		= vsprintf($stmt,$inputArr);
		$returnData = $this->classBusiLogic->execUpdateQuery($sql);
		return $returnData;
	}

	public function getSubDealerTargetYear($DateANSI=NULL, &$curMonth=NULL, &$curLogMonth=NULL, &$curYear=NULL) {
		$DateANSI		= (is_null($DateANSI)) ? 'now' : $DateANSI;
		$DateANSI		= new DateTime($DateANSI);
		$curMonth		= $DateANSI->format('n');
		$curLogMonth	= (($curMonth+8) % 12) + 1;				// Logical Month: April-1, May-2, ..., Dec-9, Jan-10, Feb-11, Mar-12
		$curYear		= $DateANSI->format('Y');
		$targetYear	= $curYear - ($curMonth<=3 ? 1 : 0);
		$targetYear	= $targetYear . '-' . ($targetYear+1);			// eg 2011-2012
		return $targetYear;
	}

	 public function getAchivement($curSubDCd,$curCustDCd,$ItemCd, $date){
		$Achivement = 0;
		$sql = "SELECT IFNULL(SUM(SaleQty),0) AS Achievement FROM so_daily_sale_item WHERE CustomerCd = '".$curCustDCd."' AND IdSubdealer = ".$curSubDCd." AND ItemCd = '".$ItemCd."' AND Date LIKE '%".$date."%'";

		$AchivementData = $this->classBusiLogic->execSelectQuery($sql);
		if(isset($AchivementData[0]['Achievement'])){
			return $Achivement = $AchivementData[0]['Achievement'];
		}else{
			return $Achivement;
		}

	}

	 public function reportSubDealerTargetMonthly($data)
    {
        $DTSAnsi     = $this->classBusiLogic->MY2AnsiDate($data['MYS']);
        $Year        = $this->getSubDealerTargetYear($DTSAnsi, $curMonth, $curLogMonth);
        $displayYear = substr($Year, ($curLogMonth <= 9) ? 0 : 5, 4);
        
        $MonthName     = $this->classBusiLogic->monthName($curLogMonth, FALSE);
        $inputArr      = array(
            $Year,
            $curLogMonth
        );

		$where_str = '';
		$groupby = '';
		$orderby = "ORDER BY Subdealer";

		if(isset($data['IdSubdealer']) && $data['IdSubdealer'] !=''){
			$where_str .= " AND A.SubdealerCd='".$data['IdSubdealer']."'";
			$groupby .= " ,A.CustomerCd, A.SubdealerCd ";
			$orderby = " ORDER BY Customer ";		
		}else if(isset($data['CustomerCd']) && $data['CustomerCd'] !=''){
			$where_str .= " AND A.CustomerCd='".$data['CustomerCd']."'";
			$groupby .= " , A.SubdealerCd ";
		}else if(isset($data['repType']) && $data['repType'] == 1){
			$groupby .= " ,A.CustomerCd, A.SubdealerCd ";
		}else{
			$groupby .= " , A.SubdealerCd ";			
		}
		
		$date = date('Y-m',strtotime($DTSAnsi));	

       
        $sql = "SELECT A.SubdealerCd AS IdSubdealer, F.Descript AS Subdealer, F.City, A.CustomerCd, E.Descript AS Customer, E.City AS Station, A.Year, A.Month, A.YMFlag, B.ItemCd, D.ShortDescript AS Item, SUM(B.OwnQuantity) AS Quantity, SUM(B.ApprovedQty) AS ApprovedQty, SUM(C.MonthStock) AS Stock FROM so_subdealer_target AS A LEFT OUTER JOIN (so_subdealer_target_item AS B, so_subdealer_target_item_stock AS C, so_item_m AS D, so_customer_m AS E, so_subdealer_m AS F) ON (A.YMFlag = B.YMFlag AND A.Year = B.Year AND A.Month = B.Month AND A.SubdealerCd = B.SubdealerCd AND A.CustomerCd = B.CustomerCd AND A.YMFlag = B.YMFlag AND B.Year = C.Year AND B.Month = C.Month AND B.SubdealerCd = C.SubdealerCd AND B.ItemCd = C.ItemCd AND C.ItemCd = D.ItemCd AND A.CustomerCd = E.CustomerCd AND A.SubdealerCd = F.IdSubdealer) WHERE A.YMFlag=1 AND A.Year='".$Year."' AND A.Month=".$curLogMonth." AND D.ShortDescript<>'' ".$where_str." GROUP BY B.ItemCd ".$groupby." ".$orderby;

        $pivotColSupAr = array();
        $flatDataAr    = $this->classBusiLogic->execSelectQuery($sql, 0, $pivotColSupAr);
	
        $pivotColAr    = $pivotColSupAr[0];
        If (count($flatDataAr) == 0) {
            echo '<script>alert("No data Exist for this Month!!.")</script>';
            return;
        }
        $pivotDataAr   = array();
        $flatDataArCnt = count($flatDataAr);
		$TotalStock = 0;
		$TotalQty = 0;
		$TotalMonthStock = 0;
		$TotalDifference = 0;
		$TotalAchievement = 0;
		$TotalVariation = 0;
		$TotalVariationPer = 0;
        for ($i = 0; $i < $flatDataArCnt; $i++) {
			$VariationpPer = 0;
            $curSubDCd    = $flatDataAr[$i]['IdSubdealer'];
            $curCustDCd   = $flatDataAr[$i]['CustomerCd'];
            $curItem      = $flatDataAr[$i]['Item'];
            $ItemCd      = $flatDataAr[$i]['ItemCd'];
			$Achievement  = $this->getAchivement($curSubDCd,$curCustDCd,$ItemCd,$date);
            
			$pivotDataAr[$curSubDCd]['Subdealer']            = $flatDataAr[$i]['Subdealer'];
            $pivotDataAr[$curSubDCd]['Customer'][$curCustDCd]= $flatDataAr[$i]['Customer'];
            $pivotDataAr[$curSubDCd]['City'][$curCustDCd] = $flatDataAr[$i]['City'];
            $pivotDataAr[$curSubDCd]['Station'][$curCustDCd] = $flatDataAr[$i]['Station'];
            $pivotDataAr['Item'][]							 = $curItem;
            $pivotDataAr[$curSubDCd][$curItem]['Stock'][$curCustDCd]	 = $flatDataAr[$i]['Stock'];
            $pivotDataAr[$curSubDCd][$curItem]['Quantity'][$curCustDCd]  = $flatDataAr[$i]['Quantity'];
            $pivotDataAr[$curSubDCd][$curItem]['MonthStock'][$curCustDCd]= $flatDataAr[$i]['ApprovedQty'];
            $Difference  = ($flatDataAr[$i]['ApprovedQty'] - $flatDataAr[$i]['Quantity']);
         	$Variation  = ($Achievement - $flatDataAr[$i]['ApprovedQty']);
            $pivotDataAr[$curSubDCd][$curItem]['Difference'][$curCustDCd] = $Difference;			
            $pivotDataAr[$curSubDCd][$curItem]['Achievement'][$curCustDCd]= $Achievement;			
            $pivotDataAr[$curSubDCd][$curItem]['Variation'][$curCustDCd]  = $Variation;
			if($Achievement != 0 && $flatDataAr[$i]['ApprovedQty'] > 0){
				$VariationpPer = (($Achievement * 100) / $flatDataAr[$i]['ApprovedQty']);
				$pivotDataAr[$curSubDCd][$curItem]['VariationpPer'][$curCustDCd]  = $VariationpPer;
			}else{				
				$pivotDataAr[$curSubDCd][$curItem]['VariationpPer'][$curCustDCd]  = $VariationpPer;
			}

			if(isset($pivotDataAr['Total']['TotalStock'][$curItem])){
				$pivotDataAr['Total']['TotalStock'][$curItem] = $pivotDataAr['Total']['TotalStock'][$curItem] + $flatDataAr[$i]['Stock'];
			}else{
				$pivotDataAr['Total']['TotalStock'][$curItem] = $flatDataAr[$i]['Stock'];
			}
			if(isset($pivotDataAr['Total']['TotalQty'][$curItem])){
				$pivotDataAr['Total']['TotalQty'][$curItem] = $pivotDataAr['Total']['TotalQty'][$curItem] + $flatDataAr[$i]['Quantity'];
			}else{
				$pivotDataAr['Total']['TotalQty'][$curItem] = $flatDataAr[$i]['Quantity'];
			}
			if(isset($pivotDataAr['Total']['TotalMonthStock'][$curItem])){
				$pivotDataAr['Total']['TotalMonthStock'][$curItem] = $pivotDataAr['Total']['TotalMonthStock'][$curItem] + $flatDataAr[$i]['ApprovedQty'];
			}else{
				$pivotDataAr['Total']['TotalMonthStock'][$curItem] = $flatDataAr[$i]['ApprovedQty'];
			}
			if(isset($pivotDataAr['Total']['TotalDifference'][$curItem])){
				$pivotDataAr['Total']['TotalDifference'][$curItem] = $pivotDataAr['Total']['TotalDifference'][$curItem] + $Difference;
			}else{
				$pivotDataAr['Total']['TotalDifference'][$curItem] = $Difference;
			}
			if(isset($pivotDataAr['Total']['TotalAchievement'][$curItem])){
				$pivotDataAr['Total']['TotalAchievement'][$curItem] = $pivotDataAr['Total']['TotalAchievement'][$curItem] + $Achievement;
			}else{
				$pivotDataAr['Total']['TotalAchievement'][$curItem] = $Achievement;
			}
			if(isset($pivotDataAr['Total']['TotalVariation'][$curItem])){
				$pivotDataAr['Total']['TotalVariation'][$curItem] = $pivotDataAr['Total']['TotalVariation'][$curItem] + $Variation;
			}else{
				$pivotDataAr['Total']['TotalVariation'][$curItem] = $Variation;
			}
			if(isset($pivotDataAr['Total']['TotalVariationPer'][$curItem])){
				$pivotDataAr['Total']['TotalVariationPer'][$curItem] = $pivotDataAr['Total']['TotalVariationPer'][$curItem] + $VariationpPer;
			}else{
				$pivotDataAr['Total']['TotalVariationPer'][$curItem] = $VariationpPer;
			}
		}




		return $pivotDataAr;
     
    }
///===========================Subdealer Traget  Month Trade new==============================

public function getSubDealerTradeTargetMonthList($search, $TargetForMY) {
		$DTSAnsi		= preg_replace('/(\d{2})[-\/](\d{4})/', "$2-$1", $TargetForMY) . '-01';
		$Year			= $this->getSubDealerTargetYear($DTSAnsi, $curMonth, $curLogMonth);
		$searchCnd		= " WHERE A.Year='" . $Year . "' AND A.Month=" . $curLogMonth;
		$searchCnd		.= (isset($search)) ? " AND B.Descript like '". $search . "%' AND A.YMFlag=1" : " AND A.YMFlag=1";
		$searchCnd		.= ($_SESSION['IsSubDealer']) ? " AND A.SubdealerCd='" .$_SESSION['SubdealerCd']. "'" : "";
		$searchCnd		.= ($_SESSION['IsCustomer']) ? " AND A.CustomerCd='" .$_SESSION['CustomerCd']. "'" : "";

		switch ($_SESSION['StatusFilter']) {
		case 0:
			break;
		case 2:
			$searchCnd	.= " AND A.StatusCd='A'";
			break;
		default:		// 1
			$searchCnd	.= " AND A.StatusCd='U'";
			break;
		}
		$sql = "SELECT A.SubdealerCd, A.CustomerCd,A.Year, A.Month, DATE_FORMAT(CONCAT('2012-',(A.Month +2)%12 +1,'-01'),'%b') AS MonthName,IFNULL(B.Descript,A.CustomerCd) AS Customer,IFNULL(E.Descript,A.SubdealerCd) AS Subdealer, IFNULL(C.Descript,A.StatusCd) AS Status, E.AllowTrgt FROM so_subdealer_target_trade A LEFT OUTER JOIN so_subdealer_m E ON A.SubdealerCd=E.IdSubdealer LEFT OUTER JOIN so_customer_m B ON A.CustomerCd=B.CustomerCd LEFT OUTER JOIN so_status_m C ON A.StatusCd=C.StatusCd LEFT OUTER JOIN so_state_m D ON B.StateCd=D.StateCd LEFT OUTER JOIN so_subdealer_target_item_trade F ON (A.SubdealerCd = F.SubdealerCd AND A.CustomerCd = F.CustomerCd AND A.Year = F.Year AND A.Month =F.Month) " .$searchCnd. " GROUP BY A.CustomerCd,A.SubdealerCd ORDER BY A.Year DESC, A.Month DESC, B.IsActive DESC, E.Descript, D.DispOrder, B.CustomerCtgCd, B.Descript";
		$returnData = $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	public function getSubDealerTradeTargetMonthItemStock($SubdealerCd, $CustomerCd='', $Year, $Month, $ProductId) {
		$sql = "SELECT A.ItemCd, A.SubdealerCd, B.ProductId, B.ProductName, B.UOM,  A.MonthStock AS Stock  FROM so_subdealer_target_item_stock_trade A Right JOIN  so_target_item_group AS B ON A.ItemCd=B.ProductId  WHERE A.SubdealerCd = '".$SubdealerCd."'  AND A.Year = '".$Year."' AND A.Month = '".$Month."' AND B.ProductId ='".$ProductId."' ORDER BY B.ProductId";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getSubDealerTradeTargetMonthCustomer($SubdealerCd, $CustomerCd = '', $Year, $Month, $StatusCd = '', $ProductId) {
		$sql = "SELECT A.ItemCd, A.SubdealerCd, A.CustomerCd, B.ProductId, B.ProductName, B.UOM,  A.`OwnQuantity` AS Target,
		A.ApprovedQty AS Appoved,A.VariationQty AS Variation FROM so_subdealer_target_item_trade A Right JOIN  so_target_item_group AS B ON A.ItemCd=B.ProductId  WHERE A.SubdealerCd ='".$SubdealerCd."' AND A.CustomerCd='".$CustomerCd."'  AND A.Year = '".$Year."' AND A.Month = '".$Month."' AND B.ProductId ='".$ProductId."' ORDER BY B.ProductId";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
public function getSubDealerTradeTotalTargetMonthCustomer($SubdealerCd, $CustomerCd = '', $Year, $Month, $StatusCd = '', $ProductId) {
		$sql = "SELECT  SUM(A.`OwnQuantity`) AS TotalTarget  FROM so_subdealer_target_item_trade A Right JOIN  so_target_item_group AS B ON A.ItemCd=B.ProductId  WHERE A.SubdealerCd ='".$SubdealerCd."'  AND A.Year = '".$Year."' AND A.Month = '".$Month."' AND B.ProductId ='".$ProductId."' ORDER BY B.ProductId";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getSubDealerTradeTargetMonthHeader($SubdealerCd = '', $CustomerCd = '', $Month, $Year) {
		$searchCnd='';
		$searchCnd	.= ($SubdealerCd !='') ? " AND SubdealerCd='" .$SubdealerCd. "'" : "";
		$searchCnd	.= ($CustomerCd !='') ? " AND CustomerCd='" .$CustomerCd. "'" : "";
		$sql		= "SELECT * FROM so_subdealer_target_trade WHERE YMFlag=1 AND Year='".$Year."' AND Month=".(int)$Month ." ".$searchCnd;
	
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}


public function getSubDealerTradeItemMDisp($SubdealerCd=''){	
	$sql = " SELECT ProductId,UOM,ProductName,ItemType FROM so_target_item_group  ORDER BY ItemType  DESC";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;	
}

public function isSubDealerPreferancesApproved($IdSubdealer) {
	$sql			= "SELECT * FROM so_subdealer_dealer_preference WHERE IdSubdealer='".$IdSubdealer."'" ;
	$queryResult	= $this->classBusiLogic->execSelectQuery($sql);
	$rowCount		= count($queryResult);
	
	return $queryResult;
}


public function isExistSubDealerTradeTargetMonth($SubdealerCd, $Month, $Year) {
	 $sql			= "SELECT * FROM so_subdealer_target_trade WHERE SubdealerCd='".$SubdealerCd."' AND YMFlag=1 AND Year='".$Year."' AND Month=".(int)$Month;
	$queryResult	= $this->classBusiLogic->execSelectQuery($sql);
	$rowCount		= count($queryResult);
	
	return $rowCount;
}

public function insertSubDealerTradeTargetMonth($data) {
		foreach($data['CustomerCd'] as $index => $value){
			$Status = (isset($data['StatusCd']) ? $data['StatusCd'] : 'U');
			$stmt	= "INSERT INTO so_subdealer_target_trade(SubdealerCd,CustomerCd, Year, YMFlag, Month, Date, TimesofDeliveryExplo, TimesofDeliveryAED, StatusCd, TransUserName, TransDateTime)". " VALUES ('%s','%s', '%s', %s, %s, '%s', '%s', '%s', '%s', '%s', '%s');";
			$OwnQuantity=0;
			$returnData = 'Target Qty cant be null Data is not Inserted';
			for ($curRowNo=0; $curRowNo<sizeof($data['ProductId']); $curRowNo++) {
				$OwnQuantity += $data['Target_'.($curRowNo+1).'_'.($index+1)];	
			}
			if($OwnQuantity > '00'){
		$inputArr	= array($_SESSION['SubdealerCd'],$data['CustomerCd'][$index], $data['Year'], 1, (int)$data['Month'], $this->classBusiLogic->convertDate($data['Date'],TRUE),$data['TimeOfDeliveryExp_'.($index+1)], $data['TimeOfDeliveryAED_'.($index+1)], $Status, $_SESSION['UserName'], date("Y-m-d h:i:s"));
			echo $sql		= vsprintf($stmt,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			}
		}
		
		return $returnData;
}

public function insertSubDealerTradeTargetMonthItem($data) {
		foreach($data['CustomerCd'] as $index => $value){
			$queryInsert	= "INSERT INTO so_subdealer_target_item_trade(SubdealerCd,CustomerCd, Year, YMFlag, Month, ItemCd,  OwnQuantity)". " VALUES ('%s','%s', '%s', %s, %s, '%s', %s);";
			$returnData		= '';
			//print_r($data['ProductId']);
			for ($curRowNo=0; $curRowNo<sizeof($data['ProductId']); $curRowNo++) {
				$ProductId		= $data['ProductId'][$curRowNo];
				//$TotalTargetQty	= (float)$data['TotalTarget_'.($curRowNo+1)];			
				$OwnQuantity	= (float)$data['Target_'.($curRowNo+1).'_'.($index+1)];			
				$inputArr	= array();
				if( $OwnQuantity!=0 || $OwnQuantity!='' ){
				$inputArr	= array($data['SubdealerCd'], $data['CustomerCd'][$index], $data['Year'], 1, (int)$data['Month'], $ProductId,  $OwnQuantity);
				$sql		= vsprintf($queryInsert, $inputArr);
				
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				}
				if ($returnData) return $returnData;
			}
		}
	return $returnData;
}

public function insertSubDealerTradeTargetMonthItemStock($data) {
	
	$queryInsert	= "INSERT INTO so_subdealer_target_item_stock_trade(Year, SubdealerCd, ItemCd, YMFlag, Month, MonthStock)". " VALUES ('%s', '%s', '%s', %s, %s, %s);";
	$returnData		= '';
			
	for ($curRowNo=0; $curRowNo<sizeof($data['ProductId']); $curRowNo++) {
		$ProductId		= $data['ProductId'][$curRowNo];
		$MonthStock	= (float)$data['MagazineStock_'.($curRowNo+1)];			
		$inputArr	= array();
		if($MonthStock!='0'){
		$inputArr	= array( $data['Year'], $data['SubdealerCd'], $ProductId, 1, (int)$data['Month'], $MonthStock);
		$sql		= vsprintf($queryInsert, $inputArr);
		$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		if ($returnData) return $returnData;
		}
	}
	return $returnData;
}

public function updateSubDealerTradeTargetMonthItemSub($data) {
	foreach($data['CustomerCd'] as $index => $value){
		$stmt0	= " Select count(*) as count from so_subdealer_target_item_trade WHERE CustomerCd='%s' AND SubdealerCd = '%s' AND ItemCd = '%s' AND Year='%s' AND YMFlag='1' AND Month='%s'";
		 
		$queryInsert	= "INSERT INTO so_subdealer_target_item_trade(SubdealerCd,CustomerCd, Year, YMFlag, Month, ItemCd,  OwnQuantity)". " VALUES ('%s','%s', '%s', %s, %s, '%s', %s);";
		
		$stmt2	= " UPDATE so_subdealer_target_item_trade SET OwnQuantity='%s' WHERE CustomerCd='%s' AND SubdealerCd = '%s' AND ItemCd = '%s' AND Year='%s' AND YMFlag='1' AND Month='%s'";
		
		for ($curRowNo=0; $curRowNo<sizeof($data['ProductId']); $curRowNo++) {
			//sizeof($data['ProductId'])
			$ProductId		= $data['ProductId'][$curRowNo];
			$OwnQuantity	= (isset($data['Target_'.($curRowNo+1).'_'.($index+1)]) ? $data['Target_'.($curRowNo+1).'_'.($index+1)] : 0);	
			
			$inputArr	= array($data['CustomerCd'][$index], $data['oldSubDealerCd'] ,$ProductId, $data['oldYear'], $data['oldMonth']);
			$sql0 = vsprintf($stmt0,$inputArr);
			$returnData0	= $this->classBusiLogic->execSelectQuery($sql0);
			
			if($returnData0[0]['count'] > '0'){
				$inputArr	= array($OwnQuantity, $data['CustomerCd'][$index], $data['oldSubDealerCd'] ,$ProductId, $data['oldYear'], $data['oldMonth']);
				$sql2 = vsprintf($stmt2,$inputArr);
				$returnData2	= $this->classBusiLogic->execUpdateQuery($sql2);
			}
			else{
			$inputArr	= array($data['SubdealerCd'], $data['CustomerCd'][$index], $data['Year'], 1, (int)$data['Month'], $ProductId,  $OwnQuantity);
			$sql		= vsprintf($queryInsert, $inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			}
		}	
	}
	return $returnData;
}


public function updateSubDealerTradeTargetMonthStockSub($data) {
	foreach($data['CustomerCd'] as $index => $value){
		$querySelect	= " Select count(*) as count from so_subdealer_target_item_stock_trade WHERE  Year='%s' AND SubdealerCd = '%s' AND ItemCd = '%s' AND  YMFlag='1' AND Month='%s'";
		 
		$queryInsert	= "INSERT INTO so_subdealer_target_item_stock_trade(Year, SubdealerCd, ItemCd, YMFlag, Month,   MonthStock)". " VALUES ('%s','%s', '%s', %s, '%s', '%s');";
		
		$QueryUpdate	= " UPDATE so_subdealer_target_item_stock_trade SET MonthStock='%s' WHERE Year='%s' AND SubdealerCd = '%s' AND ItemCd = '%s' AND  YMFlag='1' AND Month='%s'";
		
		for ($curRowNo=0; $curRowNo<sizeof($data['ProductId']); $curRowNo++) {
			//sizeof($data['ProductId'])
			$ProductId		= $data['ProductId'][$curRowNo];
			$MonthStock	= (float)$data['MagazineStock_'.($curRowNo+1)];	
			
			$inputArr	= array($data['oldYear'], $data['oldSubDealerCd'] ,$ProductId, $data['oldMonth']);
			$sql0 = vsprintf($querySelect,$inputArr);
			$returnData0	= $this->classBusiLogic->execSelectQuery($sql0);
			
			if($returnData0[0]['count'] > '0'){
				$inputArr	= array($MonthStock, $data['oldYear'], $data['oldSubDealerCd'], $ProductId, $data['oldMonth']);
				$sql2 = vsprintf($QueryUpdate,$inputArr);
				$returnData2	= $this->classBusiLogic->execUpdateQuery($sql2);
			}
			else{
			$inputArr	= array($data['Year'], $data['SubdealerCd'], $ProductId, 1, (int)$data['Month'], $MonthStock);
			$sql		= vsprintf($queryInsert, $inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			}
		}	
	}
	return $returnData;
}

	
public function updateSubDealerTradeTargetMonth($data) {
	$Status = (isset($data['StatusCd']) ? $data['StatusCd'] : 'U');
	
	$stmt	= "UPDATE so_subdealer_target_trade SET StatusCd='%s', TransUserName='%s', TransDateTime='%s'". " WHERE CustomerCd='%s' AND SubdealerCd = '%s' AND Year='%s' AND YMFlag=1 AND Month=%s";
	$inputArr	= array($Status, $_SESSION['UserName'], date("Y-m-d H:i:s"), $data['oldCustomerCd'], $data['oldSubDealerCd'], $data['oldYear'], $data['oldMonth']);
	$sql = vsprintf($stmt,$inputArr);

	$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
	return $returnData;
}

public function updateSubDealerTradeTargetMonthItem($data) {

	foreach($data['CustomerCd'] as $index => $value){
		$stmt	= "UPDATE so_subdealer_target_item_trade SET ApprovedQty='%s' WHERE CustomerCd='%s' AND SubdealerCd = '%s' AND ItemCd = '%s' AND Year='%s' AND YMFlag=1 AND Month=%s";
		for ($curRowNo=0; $curRowNo<sizeof($data['ProductId']); $curRowNo++) {
			$ProductId		= $data['ProductId'][$curRowNo];
			$ApproveTarget	= $data['ApproveTarget_'.$index.'_'.$curRowNo];				
			$inputArr	= array($ApproveTarget, $data['oldCustomerCd'], $data['oldSubDealerCd'] ,$ProductId, $data['oldYear'], $data['oldMonth']);
			$sql = vsprintf($stmt,$inputArr);

			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		}
	}
	return $returnData;
}

 public function getDealerSubdealerPercentage($SubDealerCd){
	$sql 	= "SELECT CustomerCd,PercentageOfWork FROM so_subdealer_m_dealer WHERE IdSubdealer = '".$SubDealerCd."'";
	$returnData = $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;
}



	public function subdealerTargetPercentageDetails($SubDealerCd,$CustomerCd){
		$sql 	= "SELECT A.PercentageOfWork,IFNULL(B.Descript,A.IdSubdealer) AS SubdealerName,B.StateCd FROM so_subdealer_m_dealer A INNER JOIN so_subdealer_m B ON A.IdSubdealer = B.IdSubdealer WHERE A.IdSubdealer = '".$SubDealerCd."' AND CustomerCd = '".$CustomerCd ."' ";
		$returnData = $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	
	public function approveItemSubDealerTradeTargetMonthItem($Month,$Year,$SubDealerCd,$CustomerCd){
		$sql = "SELECT A.ProductId,A.ProductName,A.UOM,B.OwnQuantity,B.ApprovedQty,B.VariationQty FROM so_target_item_group A INNER JOIN  so_subdealer_target_item_trade B ON A.ProductId = B.ItemCd WHERE B.Month = '".$Month."' AND B.Year = '".$Year."' AND B.SubDealerCd = '".$SubDealerCd."' AND B.CustomerCd ='".$CustomerCd."' ";
		$returnData = $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	
	public function insertApproveItemSubDealerTradeTargetMonth($data){
		$returnData	 = '';
		$sqlUpdate = "UPDATE so_subdealer_target_item_trade SET ApprovedQty = '%s', VariationQty='%s' WHERE Year = '%s' AND Month ='%s' AND SubdealerCd = '%s' AND CustomerCd = '%s' AND ItemCd ='%s' ";
		$CustomerCd 	= $data['CustomerCd'];
		$SubDealerCd 	= $data['SubdealerCd'];
		$Year			= $data['Year'];
		$Month 			= $data['Month'];
		for($curRow = 1;$curRow<=$data['LastRowNo'];$curRow++){
			$ApprovedQty  = $data['ApprovedQty_'.$curRow];
			$VariationQty = $data['VariationQty_'.$curRow];
			$ItemCd		  = $data['ProductId_'.$curRow];
			if($ApprovedQty == ''){
				continue;
			}
			$inputArr	  = array($ApprovedQty,$VariationQty,$Year,$Month,$SubDealerCd,$CustomerCd,$ItemCd);
			$sql	  = vsprintf($sqlUpdate, $inputArr);
			$returnData	  = $this->classBusiLogic->execUpdateQuery($sql);
			//if ($returnData) return $returnData;
		}
		
		$queryUpdate 	  = "UPDATE so_subdealer_target_trade SET StatusCd = 'A'  WHERE Year = '%s' AND Month ='%s' AND SubdealerCd = '%s' AND CustomerCd = '%s' ";
		$inputArr	  = array($Year,$Month,$SubDealerCd,$CustomerCd);
		$sql	 	  = vsprintf($queryUpdate, $inputArr);
		$returnData	  = $this->classBusiLogic->execUpdateQuery($sql);
	}
	
	
	public function statusToggleByAdmin($SubdealerCd,$CustomerCd,$Month,$Status,$Year){
		$sqlUpdate 	  = "UPDATE so_subdealer_target_trade SET StatusCd = '%s'  WHERE Year = '%s' AND Month ='%s' AND SubdealerCd = '%s' AND CustomerCd = '%s' ";
		$inputArr	  = array($Status,$Year,$Month,$SubdealerCd,$CustomerCd);
		$sql	 	  = vsprintf($sqlUpdate, $inputArr);
		$returnData	  = $this->classBusiLogic->execUpdateQuery($sql);
	}

//===================================================
//Phone call record and report functionality

	public function SubdealerDetails($search = null, $CustomerCd = null)
    {
        $searchCnd  = (isset($search)) ? " WHERE A.IdSubdealer ='".$search ."'" : "";
        $sql        = "SELECT A.IdSubdealer, A.SubdealerCd, A.Descript AS SubdealerName, A.City, A.StateCd, B.Descript As state FROM so_subdealer_m A LEFT OUTER JOIN so_state_m B ON A.StateCd=B.StateCd " . $searchCnd . " ORDER BY A.IsActive DESC, B.DispOrder, A.Descript";
        $SubdealerData = $this->classBusiLogic->execSelectQuery($sql);

		if($search != null || $CustomerCd != null){
			if($search != null){
				$sql        = "select fcpm.contact_person_list from so_firm_cp_m AS fcpm where fcpm.firm_id = '".$search."'";
				$SubDealerList = $this->classBusiLogic->execSelectQuery($sql);		
				if(!empty($SubDealerList)){
				$contact_person['person'] = explode(',',$SubDealerList[0]['contact_person_list']);
				}
			}

			if($CustomerCd != null){
				$sql        = "select fcpm.contact_person_list from so_firm_cp_m AS fcpm where fcpm.firm_id = '".$CustomerCd."'";
				$DealerList = $this->classBusiLogic->execSelectQuery($sql);
				if(!empty($DealerList)){
				$contact_person['contactby'] = explode(',',$DealerList[0]['contact_person_list']);
				}
			}
		}

		if(isset($contact_person) && !empty($contact_person)){
			$returnData = array_merge($SubdealerData[0], $contact_person);
		}else{
			$returnData = $SubdealerData[0];
		}

		return $returnData;
    }
	
	public function getCallType($CallTypeCd = '') {
		$where_str = '';	  
		if(!is_numeric($CallTypeCd) && $CallTypeCd != ''){
			$where_str .= "WHERE call_type_cd = '".$CallTypeCd."'";
		}
		
		$sql = "select call_type_cd, call_type_name from so_call_type_status ".$where_str." order by call_type_id";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);		
		return $returnData;
	}

	public function getPhoneCallList($date, $CustomerCd = '', $searchVal = ''){
		$where_str = '';
		$date = $this->classBusiLogic->convertDate($date, TRUE, 1);
		if($searchVal !=''){
			$where_str .= "AND (pc.SubDealerName like '%" . $searchVal . "%' || pc.call_type_cd like '%" . $searchVal . "%' || socm.Descript like '%" . $searchVal . "%')";
		}

		if($CustomerCd !=''){
			$where_str .= " AND pc.CustomerCd = '".$CustomerCd."' ";
		}


		$sql = "select pc.id, pc.SubDealerCd, pc.SubDealerName, pc.call_type_cd, cts.call_type_name, pc.Station, pc.StateCd, pc.call_time, pc.Person
		, pc.contact_medium, socm.CustomerCd, socm.Descript AS CustomerName, pc.message, pc.ContactBy, pc.priority_flag, pc.date_added , pc.PhoneStatus
		from so_phone_commu AS pc, so_call_type_status AS cts, so_customer_m AS socm 
		where pc.CustomerCd = socm.CustomerCd AND cts.call_type_cd = pc.call_type_cd  AND 
		pc.date_added LIKE '%".$date."%' ".$where_str." 
		ORDER BY socm.Descript,  DATE_FORMAT(pc.date_added, '%Y-%m-%d') DESC , pc.call_time ASC";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;

	}

	public function getHighlightPhoneCallList($date, $CustomerCd = '', $searchVal = ''){
		$where_str = '';
		$date = $this->classBusiLogic->convertDate($date, TRUE, 1);
		if($searchVal !=''){
			$where_str .= "AND (pc.SubDealerName like '%" . $searchVal . "%' || pc.call_type_cd like '%" . $searchVal . "%' || socm.Descript like '%" . $searchVal . "%')";
		}

		if($CustomerCd !=''){
			$where_str .= " AND pc.CustomerCd = '".$CustomerCd."' ";
		}


		$sql = "select pc.id, pc.SubDealerCd, pc.SubDealerName, pc.call_type_cd, cts.call_type_name, pc.Station, pc.StateCd, pc.call_time, pc.Person, pc.contact_medium, socm.CustomerCd, socm.Descript AS CustomerName, pc.message, pc.ContactBy, pc.priority_flag, pc.date_added from so_phone_commu AS pc, so_call_type_status AS cts, so_customer_m AS socm 
		where pc.HighlightedRecord='1' and pc.CustomerCd = socm.CustomerCd  AND cts.call_type_cd = pc.call_type_cd  AND pc.date_added LIKE '%".$date."%' ".$where_str." ORDER BY DATE_FORMAT(pc.date_added, '%Y-%m-%d') DESC , pc.call_time ASC";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;

	}
	
	
function approvePhoneCall($id,$CustomerCd,$date){
		$sql = "update so_phone_commu SET PhoneStatus ='1' WHERE id IN($id)";	
		$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);	
		
		$CustomerCd = explode(",",$CustomerCd);
		$date = explode(",",$date);
		//print_r($CustomerCd);
		$count = 0;
		foreach($CustomerCd as $list){
			echo $sql = "SELECT * FROM so_phone_commu WHERE PhoneStatus ='0' AND CustomerCd = $list AND date_added LIKE '%".$date[$count]."%'";	
			$unApprovePhoneList  = $this->classBusiLogic->execSelectQuery($sql);	
			if(empty($unApprovePhoneList)){
				echo $sql = "update so_report_not_submitted SET PhoneStatus ='1' WHERE CustomerCd = $list AND date_added LIKE '%".$date[$count]."%'";	
				$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);	
			}
			$count++;
		}
	}
	

	public function getPhoneCall($call_id){
		
		
		$sql = "select pc.id, pc.SubDealerCd, pc.SubDealerName, pc.call_type_cd, cts.call_type_name, pc.Station, pc.StateCd, pc.call_time, pc.Person, pc.contact_medium, socm.CustomerCd, socm.Descript AS CustomerName, pc.message,  pc.priority_flag, pc.ContactBy, pc.date_added from so_phone_commu AS pc, so_call_type_status AS cts, so_customer_m AS socm where pc.id = '".$call_id."' AND pc.CustomerCd = socm.CustomerCd AND cts.call_type_cd = pc.call_type_cd ";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;

	}

	public function insertPhoneCall($data){

		if (isset($data['call_type_cd'])) $call_type_cd = $data['call_type_cd'];
		$count_entry = 0;
		$CustomerCd = $data['CustomerCd'];
		$CheckPhoneReport = $data['CheckPhoneReport'];
		if($CheckPhoneReport){
			$PhoneStatus = 0;
		}
		else{
			$PhoneStatus = 1;
		}
		$date = date('Y-m-d');
		foreach($call_type_cd AS $calltype_index => $calltype_value){
			$flag = 0;
			if($calltype_value == 'H-IC' || $calltype_value == 'H-OG') $flag = 1;
			for($i = 0; $i < sizeof($data[$calltype_value]['SubdealerName']); $i++) {
				$SubdealerName = $data[$calltype_value]['SubdealerName'][$i];
				if(isset($SubdealerName) && $SubdealerName != '') {
					$count_entry++;
					$SubdealerCd = $data[$calltype_value]['SubdealerCd'][$i];
					$Station = $data[$calltype_value]['station_name'][$i];						
					$State = $data[$calltype_value]['StateCd'][$i];
					$contact_medium = $data[$calltype_value]['contact_medium'][$i];
					$contact_person = $data[$calltype_value]['Person'][$i];
					$call_time = $data[$calltype_value]['time'][$i];
					$ContactBy = $data[$calltype_value]['ContactBy'][$i];
					$message = $data[$calltype_value]['message'][$i];
					
					$sql = "insert into so_phone_commu (SubDealerCd,SubDealerName,call_type_cd,Station,StateCd,call_time, Person,contact_medium,message,priority_flag, CustomerCd, PhoneStatus, ContactBy,date_added,last_modified) values('".$SubdealerCd."','".$SubdealerName."','".$calltype_value."','".$Station."','".$State."','".$call_time."','".$contact_person."','".$contact_medium."','".$message."','".$flag."','".$CustomerCd."', '".$PhoneStatus."', '".$ContactBy."', now(),now())";			
					
					$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);

				}
			}
		}

		$reportsql = "select * from so_report_not_submitted where CustomerCd ='".$CustomerCd."' and date_added LIKE '%".$date."%' ";

		$report_exit = $this->classBusiLogic->execSelectQuery($reportsql);
		if(empty($report_exit)){
			$reportsql = "insert into so_report_not_submitted(CustomerCd,status,reason, PhoneStatus, date_added,last_modified) values('".$CustomerCd."','Received','Report Submitted', '".$PhoneStatus."', now(),now())"; 
			$report_exit = $this->classBusiLogic->execUpdateQuery($reportsql);
		}

		return $failMsg;
	}

	public function updatePhoneCall($data){
		if (isset($data['call_type_cd'])) $call_type_cd = $data['call_type_cd'];
		$count_entry = 0;
		$id = $data['id'];
		$CustomerCd = $data['CustomerCd'];
		foreach($call_type_cd AS $calltype_index => $calltype_value){
			$flag = 0;
			if($calltype_value == 'H-IC' || $calltype_value == 'H-OG') $flag = 1;
			for($i = 0; $i < sizeof($data[$calltype_value]['SubdealerName']); $i++) {
				$SubdealerName = $data[$calltype_value]['SubdealerName'][$i];
				if(isset($SubdealerName) && $SubdealerName != '') {
					$count_entry++;
					$SubdealerCd = $data[$calltype_value]['SubdealerCd'][$i];
					$Station = $data[$calltype_value]['station_name'][$i];						
					$State = $data[$calltype_value]['StateCd'][$i];
					$contact_medium = $data[$calltype_value]['contact_medium'][$i];
					$contact_person = $data[$calltype_value]['Person'][$i];
					$call_time = $data[$calltype_value]['time'][$i];
					$ContactBy = $data[$calltype_value]['ContactBy'][$i];
					$message = $data[$calltype_value]['message'][$i];
				
					$sql = "update so_phone_commu SET SubDealerCd ='".$SubdealerCd."', SubDealerName='".$SubdealerName."', call_type_cd='".$calltype_value."', Station='".$Station."', StateCd='".$State."', call_time='".$call_time."', Person='".$contact_person."', contact_medium='".$contact_medium."', message='".$message."', priority_flag='".$flag."', CustomerCd='".$CustomerCd."', ContactBy='".$ContactBy."',last_modified=now() where id=".$id;		
					
					$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
				}
			}
		}

		return $failMsg;
	}

	public function deletePhoneCall($id){

		if($id != ''){
			$sql = "delete FROM so_phone_commu WHERE id IN (" . $id . ")";
			$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
			return $failMsg;
		}
	}

	public function highlightPhoneCall($id){
	if($id != ''){
		$sql = "update so_phone_commu SET HighlightedRecord ='1' WHERE id IN (" . $id . ")";		
		$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
		return $failMsg;
		}
	}


	public function reportPhoneCall($DTS, $DTE, $CallType ,$where_str = ''){
		$DTS = $this->classBusiLogic->convertDate($DTS, TRUE, 1);
		$DTE = $this->classBusiLogic->convertDate($DTE, TRUE, 1);
	
		$sql = "Select pc.id, pc.SubDealerCd, pc.SubDealerName, pc.call_type_cd, cts.call_type_name, pc.Station, pc.StateCd, pc.call_time, pc.Person, pc.contact_medium, socm.CustomerCd, socm.Descript AS CustomerName, pc.message,  pc.priority_flag, pc.ContactBy, pc.date_added from so_phone_commu AS pc, so_call_type_status AS cts, so_customer_m AS socm where pc.call_type_cd IN ('".$CallType."')  AND pc.CustomerCd = socm.CustomerCd AND pc.call_type_cd = cts.call_type_cd AND pc.date_added >= '".$DTS." 00:00:00' AND pc.date_added <= '".$DTE." 59:59:59' ".$where_str." order by socm.Descript, cts.call_type_id, DATE_FORMAT(pc.date_added,'%d-%m-%Y') desc, pc.call_time asc ";		

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;

	}

	public function pdfreportPhoneCall($DTS, $DTE, $CallType , $CustomerCd ,$where_str = ''){
		$DTS = $this->classBusiLogic->convertDate($DTS, TRUE, 1);
		$DTE = $this->classBusiLogic->convertDate($DTE, TRUE, 1);
	
		$sql = "Select pc.id, pc.SubDealerCd, pc.SubDealerName, pc.call_type_cd, cts.call_type_name, pc.Station, pc.StateCd, pc.call_time, pc.Person, pc.contact_medium, socm.CustomerCd, socm.Descript AS CustomerName, pc.message,  pc.priority_flag, pc.ContactBy, pc.date_added from so_phone_commu AS pc, so_call_type_status AS cts, so_customer_m AS socm where pc.call_type_cd IN ('".$CallType."')  AND pc.CustomerCd = socm.CustomerCd AND pc.call_type_cd = cts.call_type_cd AND pc.date_added >= '".$DTS." 00:00:00' AND pc.date_added <= '".$DTE." 59:59:59' ".$CustomerCd." ".$where_str." order by socm.Descript, cts.call_type_id, DATE_FORMAT(pc.date_added,'%d-%m-%Y') desc, pc.call_time asc ";		

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;

	}
	
		public function pdfreportHighlightPhoneCall($DTS, $DTE, $CallType , $CustomerCd ,$where_str = ''){
		$DTS = $this->classBusiLogic->convertDate($DTS, TRUE, 1);
		$DTE = $this->classBusiLogic->convertDate($DTE, TRUE, 1);
	
		$sql = "Select pc.id, pc.SubDealerCd, pc.SubDealerName, pc.call_type_cd, cts.call_type_name, pc.Station, pc.StateCd, pc.call_time, pc.Person, pc.contact_medium, socm.CustomerCd, socm.Descript AS CustomerName, pc.message,  pc.priority_flag, pc.ContactBy, pc.date_added from so_phone_commu AS pc, so_call_type_status AS cts, so_customer_m AS socm where pc.call_type_cd IN ('".$CallType."')  AND pc.CustomerCd = socm.CustomerCd AND pc.HighlightedRecord='1' AND pc.call_type_cd = cts.call_type_cd AND pc.date_added >= '".$DTS." 00:00:00' AND pc.date_added <= '".$DTE." 59:59:59' ".$CustomerCd." ".$where_str." order by socm.Descript, cts.call_type_id, DATE_FORMAT(pc.date_added,'%d-%m-%Y') desc, pc.call_time asc ";		

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;

	}

	public function getPhoneUserList($searchVal)
    {
        $searchCnd = (isset($searchVal) && $searchVal !='') ? " AND A.CustomerCd = '" . $searchVal . "' " : "";

	    $sql = "SELECT A.CustomerCd, A.UserName, A.Descript, IFNULL(C.Descript,A.UserType) AS UserType,A.ActiveFlag,A.PhoneFlag,IFNULL(B.Descript,A.CustomerCd) AS Customer FROM b2b_solar_sc.so_user_m A LEFT OUTER JOIN so_customer_m B ON A.CustomerCd=B.CustomerCd  LEFT OUTER JOIN " . self::DTUserTypeM . " C ON A.UserType=C.UserType WHERE A.CustomerCd !='' AND A.ActiveFlag!=3 AND A.PhoneFlag!=1 AND A.SectorCd = 'T' $searchCnd GROUP BY A.UserName ORDER BY B.DispPhoneReport, A.UserType, B.Descript, A.UserName ASC";

        $returnData = $this->classBusiLogic->execSelectQuery($sql);
        return $returnData;
    }

	public function getSummaryPhCall($DTS, $CustomerCd){
		$DTS = $this->classBusiLogic->convertDate($DTS, TRUE, 1);
		
		$sql = "SELECT rns.id, rns.CustomerCd, rns.status, rns.reason, IFNULL((select pc.date_added from so_phone_commu pc where pc.CustomerCd = '".$CustomerCd."' AND pc.date_added  like '%".$DTS."%'  order by pc.date_added desc limit 0,1), rns.date_added) AS date_added from so_report_not_submitted rns where rns.CustomerCd = '".$CustomerCd."' AND rns.date_added LIKE '%".$DTS."%' order by date_added desc limit 0,1 ";

		$returnData = $this->classBusiLogic->execSelectQuery($sql);
        return $returnData;
	}

	public function ChkSummaryReport($prev_date, $CustomerCd, $status = ''){

		$where_str = '';
		if($status !=''){
			$where_str .= "And rns.status='".$status."'";
		}

		$sql = "SELECT rns.CustomerCd from so_report_not_submitted rns where rns.CustomerCd = '".$CustomerCd."' AND rns.PhoneStatus='1' AND rns.date_added LIKE '%".$prev_date."%' ".$where_str;
		
		$returnData = $this->classBusiLogic->execSelectQuery($sql);
        return $returnData;
	}

	public function insertFillPhReason($data){
		$status = "Received";
		if($data['NilFlag'] == 'on'){
			$status = "Nil Record";
		}

		$sql= "insert into so_report_not_submitted(CustomerCd,status,reason,PhoneStatus,date_added,last_modified) values('".$data['CustomerCd']."','".$status."' 
		,'".$data['reason']."', '1', '".$data['DTS']."',now())";

		$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);

		return $failMsg;
	}

	public function getLicDocKeyOfCustomer($data)   {
        $stmt       = "SELECT LicDocKey FROM so_magazine_m WHERE CustomerCd='%s' AND IsActive = 1 AND DATEDIFF(LicExpiryDate, '%s')>2"; // assuming min 2 days from PO to Load
        $inputArr   = array(
            $data["CustomerCd"],
            date('Y-m-d')
        );
        $sql        = vsprintf($stmt, $inputArr);
        $returnData = $this->classBusiLogic->execSelectQuery($sql);
        return $returnData;
    }

	public function getRE11Details($RE11No = '', $RE11Date = ''){
		$RE11Date = $this->classBusiLogic->convertDate($RE11Date, TRUE, 1);
		$sql = "SELECT Count(A.IdExpDet) AS Total, A.IdExpDet AS PONo, B.StatusCd from so_expdet_magazine AS A LEFT OUTER JOIN so_expdet AS B ON A.IdExpDet = B.IdExpDet WHERE RE11No = '".$RE11No."' AND RE11Date = '".$RE11Date."' ";
		$returnData  = $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getRE11List($CustomerCd = '', $CompanyCd = '',  $searchVal = ''){
		$where_str = '';
		$str = array();
		
		if($searchVal !=''){
			$str[] = "(B.Descript like '%" . $searchVal . "%' || C.CompanyCd like '%" . $searchVal . "%' )";
		}

		if($CustomerCd !=''){
			$str[] = "A.CustomerCd = '".$CustomerCd."' ";
		}

		if($CompanyCd !=''){
			$str[] = " A.CompanyCd = '".$CompanyCd."' ";
		}
		
		if(is_array($str) && !empty($str)){
			$where_str = implode(' AND ', $str);
			$where_str = " WHERE A.ExpireDate >= '".date('Y-m-d')."' AND ".$where_str;
		}else{
			$where_str = " WHERE A.ExpireDate >= '".date('Y-m-d')."' ";
		}

		$sql = "Select A.Re11Id AS Id, A.CompanyCd, C.ShortDesc AS Company, A.CustomerCd,  B.Descript As Customer, A.LicDocKey, A.INDETNo, A.INDETDate, A.ReadyDate, A.ExpireDate, A.CourierName, A.DocketId, A.ReceivedDate, A.Status, A.DateAdded FROM b2b_solar_sc.so_re11_m AS A LEFT OUTER JOIN so_customer_m AS B ON A.CustomerCd = B.CustomerCd LEFT OUTER JOIN so_company_m AS C ON A.CompanyCd = C.CompanyCd  ".$where_str." order by B.Descript, A.DateAdded Desc";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getRE11($Id){
		
		$sql = "Select A.Re11Id AS Id, A.CompanyCd, C.ShortDesc AS Company, A.CustomerCd, B.Descript As Customer, A.LicDocKey, B.EMailId, A.INDETNo, A.INDETDate, E.ItemCd, D.Descript AS Item, D.UOM, E.Quantity, A.ReadyDate, A.ExpireDate, A.CourierName, A.DocketId, A.ReceivedDate, A.Status, A.DateAdded FROM b2b_solar_sc.so_re11_m AS A LEFt OUTER JOIN b2b_solar_sc.so_re11_item AS E ON  E.Re11Id = A.Re11Id LEFT OUTER JOIN so_customer_m AS B ON A.CustomerCd = B.CustomerCd LEFT OUTER JOIN so_company_m AS C ON A.CompanyCd = C.CompanyCd LEFT OUTER JOIN so_item_m AS D ON E.ItemCd = D.ItemCd Where A.Re11Id ='".$Id."' ";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function insertRE11($data){
	
		$failMsg = '';
		$ReceivedDate = '';
		$ExpireDate = date('d-m-Y',strtotime($data['ExpireDate']));
		$INDETDate = $this->classBusiLogic->convertDate($data['INDETDate'], TRUE, 1);
		$ReadyDate = $this->classBusiLogic->convertDate($data['ReadyDate'], TRUE, 1);
		$ExpireDate = $this->classBusiLogic->convertDate($ExpireDate, TRUE, 1);
		if(!empty($data['ReceivedDate'])){
			$ReceivedDate = $this->classBusiLogic->convertDate($data['ReceivedDate'], TRUE, 1);
					
		}

		$count = ((isset($data['ItemCd']) && !empty($data['ItemCd'])) ? sizeof($data['ItemCd']) : 0);

		$sql = "INSERT INTO b2b_solar_sc.so_re11_m (CompanyCd, CustomerCd, LicDocKey, INDETNo, INDETDate, ReadyDate, ExpireDate, CourierName, DocketId, ReceivedDate, DateAdded) VALUES ('".$data['CompanyCd']."','".$data['CustomerCd']."','".$data['LicDocKey']."','".$data['INDETNo']."','".$INDETDate."','".$ReadyDate."','".$ExpireDate."','".$data['CourierName']."','".$data['DocketId']."','".$ReceivedDate."', now())";

		$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
		$Re11Id  = $this->classBusiLogic->getInsertId();

		if($count > 0){

			foreach($data['ItemCd'] AS $index => $value){
				$ItemCd = $data['ItemCd'][$index];
				$Quantity = $data['Quantity'][$index];

				$sql = "INSERT INTO b2b_solar_sc.so_re11_item (Re11Id, ItemCd, Quantity) VALUES ('".$Re11Id."','".$ItemCd."','".$Quantity."')";
				$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
			}
			
			
		}
		return $failMsg;
	}

	public function updateRE11($data) {

		if(isset($data['ReceivedDate']) && $data['ReceivedDate'] != ''){

			$ReceivedDate = $this->classBusiLogic->convertDate($data['ReceivedDate'], TRUE, 1);
			$Id = $data['oldId'];
			$sql = "Update b2b_solar_sc.so_re11_m SET ReceivedDate = '".$ReceivedDate."' Where Re11Id ='".$Id."' ";

			$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);		
			
			return $failMsg;
		}
	}

	
	public function deleteRE11List($id){

		if($id != ''){
			$sql = "delete FROM b2b_solar_sc.so_re11_m WHERE Re11Id IN (" . $id . ")";
			$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
			$sql = "delete FROM b2b_solar_sc.so_re11_item WHERE Re11Id IN (" . $id . ")";
			$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
			return $failMsg;
		}
	}

	public function reportRE11($DTS, $DTE, $CompanyCd, $CustomerCd = ''){
		$where_str = '';
		if($CustomerCd != ''){
			$where_str = " AND A.CustomerCd ='".$CustomerCd."'";
		}
		
		$sql = "Select A.Re11Id AS Id, A.CompanyCd, C.ShortDesc AS Company, A.CustomerCd, B.Descript As Customer, A.LicDocKey, B.EMailId, A.INDETNo, A.INDETDate, E.ItemCd, D.Descript AS Item, D.UOM, E.Quantity, A.ReadyDate, A.ExpireDate, A.CourierName, A.DocketId, A.ReceivedDate, A.Status, A.DateAdded FROM b2b_solar_sc.so_re11_m AS A LEFt OUTER JOIN b2b_solar_sc.so_re11_item AS E ON  E.Re11Id = A.Re11Id LEFT OUTER JOIN so_customer_m AS B ON A.CustomerCd = B.CustomerCd LEFT OUTER JOIN so_company_m AS C ON A.CompanyCd = C.CompanyCd LEFT OUTER JOIN so_item_m AS D ON E.ItemCd = D.ItemCd Where A.CompanyCd ='".$CompanyCd."' AND A.DateAdded >= '".$DTS." 00:00:00' AND A.DateAdded <= '".$DTE." 59:59:59' ".$where_str." ORDER By B.Descript, A.Re11Id";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function sendRE11Email($Id){
	
		$eMailBody = '';
		$Status = '';
		$Srno = '';
		$Subject = 'RE11 Details';
		$RE11Details = $this->getRE11($Id);
		$IsExist = $this->getRE11Details($RE11Details[0]['INDETNo'],$RE11Details[0]['INDETDate']);
		$Status = (($IsExist[0]['PONo'] !='') ? 'Use In Po: '.$IsExist[0]['PONo'] : '-');
		$eMailBody .='<table border="0" width="100%" cellspacing="1" cellpadding="2" class="dynamic" id="vtblBreak">';
		$eMailBody .='<tr><td>Dear '.ucfirst($RE11Details[0]['Customer']).',</td></tr>';
		$eMailBody .='<tr><td>Following are the whole details of RE11: </td></tr>';
		$eMailBody .='<tr><td><table border="1" width="100%" cellspacing="1" cellpadding="2" class="dynamic" id="vtblBreak"><td width="%">LicDocKey</td></tr><tr><td width="%">'.$RE11Details[0]['Company'].'</td><td width="%">'.$RE11Details[0]['LicDocKey'].'</td></tr></table></td></tr>';
		$eMailBody .='<tr><td>Following are the Item details of RE11: ,</td><tr>';
		$eMailBody .='<tr><td><table border="1" width="50%" cellspacing="1" cellpadding="2" class="dynamic" id="vtblBreak"><tr><th align="center" width="2%">SrNo</th><th align="center" width="%">Item</th><th align="center" width="%">Quantity</th></tr>';
		foreach($RE11Details AS $Index => $value){
			$Srno = $Index + 1;
		$eMailBody .='<tr><td width="2%">'.$Srno.'</td><td width="%">'.$RE11Details[$Index]['Item'].'</td><td width="%">'.$RE11Details[$Index]['Quantity'].'</td></tr>';
		}
		$eMailBody .='</table></td></tr>';
		$eMailBody .= '<tr><td>Please Send the to Solapur Office</td></tr>';
		$eMailBody .= '<tr><td>&nbsp;</td></tr>';
		$eMailBody .= '<tr><td>Thank You.</td></tr>';
		$eMailBody .= '<tr><td>Customer Service Department</td></tr></table>';


		// To send HTML mail, the Content-type header must be set
		$headers  = 'MIME-Version: 1.0' . "\r\n";
		$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
		
		// Additional headers
		$headers .= 'From: Solar Group <b2b.solarexplosives@gmail.com>' . "\r\n";
			
		// Mail it
		//mail('websupport@solarexplosives.com', $Subject, $eMailBody, $headers);
		//mail('m.bhalerao@solargroup.com', $Subject, $eMailBody, $headers);
		
	}
	
	////////////////////////////////------------------------RE11 EXP Form--------------------------------///
	
	public function getRE11ExpDetails($RE11No = '', $RE11Date = ''){
		$RE11Date = $this->classBusiLogic->convertDate($RE11Date, TRUE, 1);
		$sql = "SELECT Count(A.IdExpDet) AS Total, A.IdExpDet AS PONo, B.StatusCd from so_expdet_magazine AS A LEFT OUTER JOIN so_expdet AS B ON A.IdExpDet = B.IdExpDet WHERE RE11No = '".$RE11No."' AND RE11Date = '".$RE11Date."' ";
		$returnData  = $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getRE11ExpList($CustomerCd = '', $CompanyCd = '',  $searchVal = ''){
		$where_str = '';
		$str = array();
		
		if($searchVal !=''){
			$str[] = "(B.Descript like '%" . $searchVal . "%' || C.CompanyCd like '%" . $searchVal . "%' )";
		}

		if($CustomerCd !=''){
			$str[] = "A.CustomerCd = '".$CustomerCd."' ";
		}

		if($CompanyCd !=''){
			$str[] = " A.CompanyCd = '".$CompanyCd."' ";
		}
		
		if(is_array($str) && !empty($str)){
			$where_str = implode(' AND ', $str);
			$where_str = " WHERE  A.DeletedFlag='0' and A.ExpireDate >= '".date('Y-m-d')."' AND ".$where_str;
		}else{
			$where_str = " WHERE A.DeletedFlag='0'  and A.ExpireDate >= '".date('Y-m-d')."' ";
		}

		$sql = "Select A.Re11Id AS Id, A.CompanyCd, A.PlaceofMagazine, C.ShortDesc AS Company, A.CustomerCd,  B.Descript As Customer,  A.INDETNo, A.INDETDate,  A.ExpireDate, A.LicenceNo, A.PlaceofMagazine, A.ReadytoReceiveDate, A.DateAdded FROM exb2b_solar_sc.so_re11_m AS A LEFT OUTER JOIN exb2b_solar_sc.so_customer_m AS B ON A.CustomerCd = B.CustomerCd LEFT OUTER JOIN exb2b_solar_sc.so_company_m AS C ON A.CompanyCd = C.CompanyCd  ".$where_str." order by B.Descript, A.DateAdded  Desc";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function getRE11Exp($Id){
		
		$sql = "Select A.Re11Id AS Id, A.CompanyCd, A.ConsigneeName, A.Country, A.PlaceofMagazine, C.ShortDesc AS Company, A.CustomerCd, B.Descript As Customer
		, B.EMailId, A.INDETNo, A.INDETDate, E.ItemCd, D.Descript AS Item, D.ItemCtgCd, D.UOM, E.Quantity, A.ReadytoReceiveDate, A.ExpireDate, A.LicenceNo
		, A.DateAdded, F.Address, F.Country as magazinecountry FROM exb2b_solar_sc.so_re11_m AS A LEFT OUTER JOIN exb2b_solar_sc.so_re11_item AS E ON  E.Re11Id = A.Re11Id LEFT OUTER JOIN
		exb2b_solar_sc.so_customer_m AS B ON A.CustomerCd = B.CustomerCd LEFT OUTER JOIN 
		exb2b_solar_sc.so_company_m AS C ON A.CompanyCd = C.CompanyCd LEFT OUTER JOIN 
		exb2b_solar_sc.so_re11_item_m AS D ON E.ItemId = D.ItemId LEFT OUTER JOIN
		exb2b_solar_sc.placeofmagazine_m AS F ON F.PomId = A.PlaceofMagazine Where A.Re11Id ='".$Id."' ";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function insertRE11Exp($data){
	
		$failMsg = '';
		$INDETDate = $this->classBusiLogic->convertDate($data['INDETDate'], TRUE, 1);
		$ReadytoReceiveDate = $this->classBusiLogic->convertDate($data['ReadytoReceiveDate'], TRUE, 1);
		$ExpireDate = $this->classBusiLogic->convertDate($data['ExpireDate'], TRUE, 1);
		$sql1 = "Select MAX(IndentId) as IndentId from so_re11_m where Year =".date('Y')."";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql1);
		if($returnData[0]['IndentId']==0){
			$IndentId= '1';
		}
		else{
			$IndentId= $returnData[0]['IndentId']+1;
		}
		$IndentId= str_pad($IndentId, 4, "0", STR_PAD_LEFT);  // produces "0001"
		$IndentNo= "RE-11/".date('Y')."/".$IndentId."";
		
		$count = ((isset($data['ItemCd']) && !empty($data['ItemCd'])) ? sizeof($data['ItemCd']) : 0);

		$sql = "INSERT INTO exb2b_solar_sc.so_re11_m (IndentId, Year, CompanyCd, PlaceofMagazine, CustomerCd, INDETNo, INDETDate, ReadytoReceiveDate, ExpireDate, LicenceNo, ConsigneeName, Country, DateAdded) 
		VALUES ('".$IndentId."', '".date('Y')."', '".$data['CompanyCd']."', '".addslashes($data['PlaceofMagazine'])."', '".$data['CustomerCd']."','".addslashes($IndentNo)."','".$INDETDate."'
		,'".$ReadytoReceiveDate."','".$ExpireDate."','".addslashes($data['LicenceNo'])."', '".addslashes($data['ConsigneeName'])."', '".addslashes($data['Country'])."',  '".date('Y-m-d H:i:s')."' )";

		$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
		$Re11Id  = $this->classBusiLogic->getInsertId();

		if($count > 0){

			foreach($data['ItemCd'] AS $index => $value){
				$ItemId = $data['ItemCd'][$index];
				$Quantity = $data['Quantity'][$index];
				$sql1 = "Select * from exb2b_solar_sc.so_re11_item_m where ItemId =".$ItemId."";
				$returnData1	= $this->classBusiLogic->execSelectQuery($sql1);
				$ItemCd= $returnData1[0]['ItemCd'];
				
				$sql = "INSERT INTO exb2b_solar_sc.so_re11_item (Re11Id, RE11IndentNo, ItemId, ItemCd, Quantity) VALUES ('".$Re11Id."', '".$IndentNo."', '".$ItemId."', '".$ItemCd."', '".$Quantity."')";
				$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
			}
			
			
		}
		if(!$failMsg){
		return $Re11Id;
		}
		else{
		 return  0;
		}
	}

	public function updateRE11Exp($data) {

		if(isset($data['ReadytoReceiveDate']) && $data['ReadytoReceiveDate'] != ''){

			$ReadytoReceiveDate = $this->classBusiLogic->convertDate($data['ReadytoReceiveDate'], TRUE, 1);
			$ExpireDate = $this->classBusiLogic->convertDate($data['ExpireDate'], TRUE, 1);
			//$PlaceofMagazine = $data['PlaceofMagazine'];
			$Id = $data['oldId'];
			$sql = "Update exb2b_solar_sc.so_re11_m SET ReadytoReceiveDate = '".$ReadytoReceiveDate."', ExpireDate='".$ExpireDate."', Country='".$data['Country']."' Where Re11Id ='".$Id."' ";
			$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);		
			
			return $failMsg;
		}
	}

	
	public function deleteRE11ExpList($id){

		if($id != ''){
			$sql = "Update exb2b_solar_sc.so_re11_m SET DeletedFlag = '1' Where Re11Id IN (" . $id . ")";
			$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);	
			return $failMsg;
		}
	}

	public function reportRE11Exp($DTS, $DTE, $CompanyCd, $CustomerCd = ''){
		$where_str = '';
		if($CustomerCd != ''){
			$where_str = " AND A.CustomerCd ='".$CustomerCd."'";
		}
		
		$sql = "Select A.Re11Id AS Id, A.CompanyCd, C.ShortDesc AS Company, A.CustomerCd, B.Descript As Customer, A.LicDocKey, B.EMailId, A.INDETNo, A.INDETDate, E.ItemCd, D.Descript AS Item, D.UOM, E.Quantity, A.ReadyDate, A.ExpireDate, A.CourierName, A.DocketId, A.ReceivedDate, A.Status, A.DateAdded FROM b2b_solar_sc.so_re11_m AS A LEFt OUTER JOIN b2b_solar_sc.so_re11_item AS E ON  E.Re11Id = A.Re11Id LEFT OUTER JOIN so_customer_m AS B ON A.CustomerCd = B.CustomerCd LEFT OUTER JOIN so_company_m AS C ON A.CompanyCd = C.CompanyCd LEFT OUTER JOIN so_item_m AS D ON E.ItemCd = D.ItemCd Where A.CompanyCd ='".$CompanyCd."' AND A.DateAdded >= '".$DTS." 00:00:00' AND A.DateAdded <= '".$DTE." 59:59:59' ".$where_str." ORDER By B.Descript, A.Re11Id";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function sendRE11EmailExp($Id){
	
		$eMailBody = '';
		$Status = '';
		$Srno = '';
		$Subject = 'RE11 Details';
		$RE11Details = $this->getRE11($Id);
		$IsExist = $this->getRE11Details($RE11Details[0]['INDETNo'],$RE11Details[0]['INDETDate']);
		$Status = (($IsExist[0]['PONo'] !='') ? 'Use In Po: '.$IsExist[0]['PONo'] : '-');
		$eMailBody .='<table border="0" width="100%" cellspacing="1" cellpadding="2" class="dynamic" id="vtblBreak">';
		$eMailBody .='<tr><td>Dear '.ucfirst($RE11Details[0]['Customer']).',</td></tr>';
		$eMailBody .='<tr><td>Following are the whole details of RE11: </td></tr>';
		$eMailBody .='<tr><td><table border="1" width="100%" cellspacing="1" cellpadding="2" class="dynamic" id="vtblBreak"><td width="%">LicDocKey</td></tr><tr><td width="%">'.$RE11Details[0]['Company'].'</td><td width="%">'.$RE11Details[0]['LicDocKey'].'</td></tr></table></td></tr>';
		$eMailBody .='<tr><td>Following are the Item details of RE11: ,</td><tr>';
		$eMailBody .='<tr><td><table border="1" width="50%" cellspacing="1" cellpadding="2" class="dynamic" id="vtblBreak"><tr><th align="center" width="2%">SrNo</th><th align="center" width="%">Item</th><th align="center" width="%">Quantity</th></tr>';
		foreach($RE11Details AS $Index => $value){
			$Srno = $Index + 1;
		$eMailBody .='<tr><td width="2%">'.$Srno.'</td><td width="%">'.$RE11Details[$Index]['Item'].'</td><td width="%">'.$RE11Details[$Index]['Quantity'].'</td></tr>';
		}
		$eMailBody .='</table></td></tr>';
		$eMailBody .= '<tr><td>Please Send the to Solapur Office</td></tr>';
		$eMailBody .= '<tr><td>&nbsp;</td></tr>';
		$eMailBody .= '<tr><td>Thank You.</td></tr>';
		$eMailBody .= '<tr><td>Customer Service Department</td></tr></table>';


		// To send HTML mail, the Content-type header must be set
		$headers  = 'MIME-Version: 1.0' . "\r\n";
		$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
		
		// Additional headers
		$headers .= 'From: Solar Group <b2b.solarexplosives@gmail.com>' . "\r\n";
			
		// Mail it
		//mail('websupport@solarexplosives.com', $Subject, $eMailBody, $headers);
		//mail('m.bhalerao@solargroup.com', $Subject, $eMailBody, $headers);
		
	}
/////////////////////////////////=====================================================//////////////////	
	
	


	// Sub Dealer Stock ---------------------------------------------------------------------------------------------------------------------------------------

	public function getSubDealerStock($StockForMY, $searchVal = '',$IdSubdealer = '', $CustomerCd = ''){

		$where = '';
		$groupby = '';

		if(isset($IdSubdealer) && $IdSubdealer !=''){
			$where .= " AND C.IdSubdealer='".$IdSubdealer."'";
			$groupby .= "B.CustomerCd, A.IdSubdealer ";
		}

		if(isset($CustomerCd) && $CustomerCd !=''){
			$where .= " AND B.CustomerCd='".$CustomerCd."'";
		}

		if(isset($searchVal) && $searchVal !=''){
			$where .= $searchVal;
		}
		
		$StockForMY = date('Y-m',strtotime('01-'.$StockForMY));
		       
        $sql = "SELECT A.Id, B.ItemCd, A.IdSubdealer, C.Descript AS Subdealer, C.City, D.ShortDescript AS Item, B.CustomerCd, E.Descript AS Customer, E.City AS Station, B.RE12NO, B.RE12Date, B.BatchNo, B.Status, IFNULL(B.Stock,0) AS Stock, A.DateAdded FROM so_subdealer_stock AS A LEFT OUTER JOIN so_subdealer_stock_item AS B ON A.Id = B.Id LEFT OUTER JOIN so_subdealer_m AS C ON A.IdSubdealer = C.IdSubdealer LEFT OUTER JOIN so_item_m AS D ON B.ItemCd = D.ItemCd LEFT OUTER JOIN so_customer_m AS E ON B.CustomerCd = E.CustomerCd WHERE DateAdded LIKE '%".$StockForMY."%'  ".$where." Group By A.Id, E.Descript ORDER BY A.DateAdded desc, Subdealer, Customer ";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;	
	}

	public function getSubDealerStockDetails($searchVal = '',$IdSubdealer = '', $CustomerCd = ''){

		$where = '';
		$str = array();
		$groupby = '';

		if(isset($IdSubdealer) && $IdSubdealer !=''){
			$str[] = " C.IdSubdealer='".$IdSubdealer."'";
			$groupby .= "B.CustomerCd, A.IdSubdealer ";
		}

		if(isset($CustomerCd) && $CustomerCd !=''){
			$str[] = " B.CustomerCd='".$CustomerCd."'";
		}

		if(isset($searchVal) && $searchVal !=''){
			$str[] = $searchVal;
		}

		if(is_array($str) && !empty($str)){
			$where = implode(' AND ', $str);
			$where = " WHERE ".$where;
		}
		       
        $sql = "SELECT A.Id, B.ItemCd, A.IdSubdealer, C.Descript AS Subdealer, C.City, D.ShortDescript AS Item, B.CustomerCd, E.Descript AS Customer, E.City AS Station, B.RE12NO, B.RE12Date, B.BatchNo, B.Status, IFNULL(B.Stock,0) AS Stock, A.DateAdded FROM so_subdealer_stock AS A LEFT OUTER JOIN so_subdealer_stock_item AS B ON A.Id = B.Id LEFT OUTER JOIN so_subdealer_m AS C ON A.IdSubdealer = C.IdSubdealer LEFT OUTER JOIN so_item_m AS D ON B.ItemCd = D.ItemCd LEFT OUTER JOIN so_customer_m AS E ON B.CustomerCd = E.CustomerCd ".$where." ORDER BY A.DateAdded, Customer ";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;	
	}

	public function insertSubDealerStock($data){
		$CustomerArr = '';

		if(isset($data['IdSubdealer']) && $data['IdSubdealer'] !=''){
			$IdSubealer = $data['IdSubdealer'];
			$CustomerArr = $data['CustomerCd'];
			
			$sql = "insert into so_subdealer_stock (IdSubdealer,IsAgree,TransUserName,TransUserTime,DateAdded) values('".$IdSubealer."',1,'".$_SESSION['UserName']."',now(),now())";

			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			$id = $this->classBusiLogic->getInsertId();

			foreach($CustomerArr AS $index => $value){
				$CustomerCd = $CustomerArr[$index];
				$ItemCd = $data['ItemCd'][$index];
				$RE12NO = $data['RE12NO'][$index];
				$RE12Date = $data['RE12Date'][$index];
				$Stock = $data['Stock'][$index];
				$BatchNoDate = $data['BatchNo&Date'][$index];			
				$Status = 'U';
				$RE12Date = $this->classBusiLogic->convertDate($RE12Date, TRUE, 1);
			
				
				$sql = "insert into so_subdealer_stock_item (id,CustomerCd,ItemCd,RE12NO,RE12Date,Stock,BatchNo,Status) values(".$id.",'".$CustomerCd."','".$ItemCd."','".$RE12NO."','".$RE12Date."',".$Stock.",'".$BatchNoDate."','".$Status."')";				
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			}
			
			return $returnData;
			
		}
	}
	public function updateSubDealerStock($data){
		$returnData = '';
	
		if(isset($_SESSION['CustomerCd']) && $_SESSION['CustomerCd'] !=''){
			$CustomerCd = $_SESSION['CustomerCd'];
			$id = $data['id'];
			if(is_array($data['IdSubdealer']) && !empty($data['IdSubdealer'])){
				foreach($data['IdSubdealer'] AS $index => $value) {
					$IdSubdealer = $data['IdSubdealer'][$index];
					if(isset($data['Status'][$IdSubdealer])) {
						$Status = 'A';
						$sql = "UPDATE so_subdealer_stock_item SET Status='".$Status."' where id = ".$id." AND CustomerCd ='".$CustomerCd."'";
						$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
					}
				}
				return $returnData;
			}
		}
	}

	public function approveSubDealerStock($condTarget, $isApproved) {

		$stmt		= "UPDATE so_subdealer_stock_item SET Status='%s' WHERE ".$condTarget;
		if ($isApproved)
			$inputArr 	= array('A');
		else
			$inputArr 	= array('U');
		$sql		= vsprintf($stmt,$inputArr);

		$returnData = $this->classBusiLogic->execUpdateQuery($sql);
		return $returnData;
	}

	public function deleteSubDealerStock($data) {
		$dataArr = array();
		$dataArr['id'] = explode(',', $data['id']);
		$dataArr['CustomerCd'] = explode(',', $data['CustomerCd']);
		
		foreach($dataArr['id'] AS $index => $value){
			$StockId = trim($dataArr['id'][$index]);		
			$CustomerCd = trim($dataArr['CustomerCd'][$index]);
	
			if($CustomerCd != '' && $CustomerCd != 0){
				$sql = "delete FROM so_subdealer_stock_item WHERE id =".$StockId." AND CustomerCd ='".$CustomerCd."'" ;
				$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
			}
			$CountStock = $this->checkSubDealerStockExist($StockId);
			if($CountStock == 0){
				$sql = "delete FROM so_subdealer_stock WHERE id =".$StockId ;
				$failMsg  = $this->classBusiLogic->execUpdateQuery($sql);
				return $failMsg;				
			}			
		}
	}

	public function checkSubDealerStockExist($StockId){

		$sql = "Select count(id) as Total FROM so_subdealer_stock_item WHERE id =".$StockId." group by CustomerCd ";
	
		$returnData  = $this->classBusiLogic->execSelectQuery($sql);
		if(count($returnData) >= 1){
			return count($returnData);
		}else{
			return 0;
		}
	}

	public function reportSubDealerStock($searchVal = '',$IdSubdealer = '', $CustomerCd = ''){

		$where = '';
		$str = array();
		$groupby = '';

		if(isset($IdSubdealer) && $IdSubdealer !=''){
			$str[] = " C.IdSubdealer='".$IdSubdealer."'";
			$groupby .= "B.CustomerCd, A.IdSubdealer ";
		}

		if(isset($CustomerCd) && $CustomerCd !=''){
			$str[] = " B.CustomerCd='".$CustomerCd."'";
		}

		if(isset($searchVal) && $searchVal !=''){
			$str[] = $searchVal;
		}

		if(is_array($str) && !empty($str)){
			$where = implode(' AND ', $str);
			$where = " WHERE ".$where;
		}
		       
        $sql = "SELECT A.Id, B.ItemCd, A.IdSubdealer, C.Descript AS Subdealer, C.City, D.ShortDescript AS Item, B.CustomerCd, E.Descript AS Customer, E.City AS Station, B.RE12NO, B.RE12Date, B.BatchNo, B.Status, IFNULL(B.Stock,0) AS Stock, A.DateAdded FROM so_subdealer_stock AS A LEFT OUTER JOIN so_subdealer_stock_item AS B ON A.Id = B.Id LEFT OUTER JOIN so_subdealer_m AS C ON A.IdSubdealer = C.IdSubdealer LEFT OUTER JOIN so_item_m AS D ON B.ItemCd = D.ItemCd LEFT OUTER JOIN so_customer_m AS E ON B.CustomerCd = E.CustomerCd ".$where." ORDER BY A.DateAdded, Customer ";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;	
	}

	public function getCustomerDetails() {
		$searchCnd = " WHERE SectorCd='{$_SESSION['SectorCd']}' ";
		$sql		= "SELECT * FROM so_customer_m A " . $searchCnd . " ORDER BY A.IsActive DESC, A.Descript";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql,2);
		return $returnData;
		
	}
	
	public function getStatewiseCustList($StateCd) {
	if($StateCd=='0'){ $StateCscond="1"; } else{ $StateCscond="A.StateCd='$StateCd'"; }
	$searchCnd = " WHERE ". $StateCscond ." and A.IsActive ='1' and  A.SectorCd='{$_SESSION['SectorCd']}'";
	$sql		= "SELECT A.*,IFNULL(B.Descript,A.StateCd) AS State FROM so_customer_m A LEFT OUTER JOIN so_state_m B ON A.StateCd=B.StateCd ".$searchCnd. " ORDER BY A.IsActive DESC, B.DispOrder, A.CustomerCtgCd, A.Descript";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql, 2);
	return $returnData;	
	}
	
	public function getStatewiseSubdealerList($StateCd) {
	if($StateCd=='0'){ $StateCscond="1"; } else{ $StateCscond="A.StateCd='$StateCd'"; }
	$searchCnd = " WHERE ". $StateCscond ." and A.IsActive ='1'";
	$sql		= "SELECT A.*,IFNULL(B.Descript,A.StateCd) AS State FROM so_subdealer_m A LEFT OUTER JOIN so_state_m B ON A.StateCd=B.StateCd " . $searchCnd . " ORDER BY A.IsActive DESC, B.DispOrder, A.Descript";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql, 2);
	return $returnData;	
	}

	public function getSubdealerDetails() {
		$sql		= "SELECT A.*,IFNULL(B.Descript,A.StateCd) AS State FROM so_subdealer_m A LEFT OUTER JOIN so_state_m B ON A.StateCd=B.StateCd ORDER BY A.IsActive DESC, B.DispOrder, A.Descript";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql,2);
		return $returnData;	
	}
	///////==================== Advance Intimation ==========================
	
	
	public function getPODetails($IdExpDet) {
		$searchCnd = " WHERE A.IdExpDet IN ($IdExpDet) ";
		$sql		= "SELECT A.*, B.Descript FROM so_expdet A JOIN so_customer_m B ON A.CustomerCd= B.CustomerCd " . $searchCnd . "";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql,2);
		return $returnData;
	}
	public function getPOMagazineDetails($IdExpDet) {
		$searchCnd = " WHERE IdExpDet ='$IdExpDet' ";
		$sql		= "SELECT A.*, B.Descript  FROM so_expdet_magazine A JOIN so_magazine_m B ON A.MagazineCd= B.MagazineCd " . $searchCnd . "";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql,2);
		return $returnData;
	}
	public function getPOItemDetails($IdExpDet, $MagazineCd) {
		$searchCnd = " WHERE A.IdExpDet = '$IdExpDet' and A.MagazineCd='$MagazineCd' ";
		$sql		= "SELECT A.*, B.Descript, B.UOM, B.ItemCtgCd, B.ShortDescript  FROM   so_expdet_magazine_item A JOIN so_item_m B  ON A.ItemCd= B.ItemCd" . $searchCnd . "";

		$returnData	= $this->classBusiLogic->execSelectQuery($sql,2);
		return $returnData;
	}
	
	//================================
	
	
}
?>
