
<?php
class SubdealerClass {

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 getSubdealerList($search = null, $searchValState = null, $searchDockey= null) {
	$searchCnd	= " WHERE  " . ((($search!='')) ? " A.Descript like '". $search . "%'" : "1") .((($searchValState!='')) ? " And A.StateCd like '". $searchValState . "%'" : "").((($searchDockey!='')) ? " And C.LicDocKey = '". $searchDockey . "'" : "");
	$sql		= "SELECT A.*,IFNULL(B.Descript,A.StateCd) AS State,C.MagazineLicNo,C.LicDocKey FROM so_subdealer_m A LEFT OUTER JOIN so_state_m B ON A.StateCd=B.StateCd LEFT JOIN so_subdealer_magazine_m C ON A.IdSubdealer = C.IdSubdealer "	.$searchCnd . " ORDER BY A.IsActive DESC, B.DispOrder, A.Descript";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;	
}
*/

public function getSubdealerList($search = null, $searchValState = null, $searchDockey= null) {
	$searchCnd	= " WHERE  " . ((($search!='')) ? " A.Descript like '". $search . "%'" : "1") .((($searchValState!='')) ? " And A.StateCd like '". $searchValState . "%'" : "").((($searchDockey!='')) ? " And C.LicDocKey = '". $searchDockey . "'" : "");
	$sql		= "SELECT A.*,IFNULL(D.District,A.City) AS City,IFNULL(B.Descript,A.StateCd) AS State,C.MagazineType,C.MagazineLicNo,C.LicDocKey FROM so_subdealer_m A LEFT OUTER JOIN so_state_m B ON A.StateCd=B.StateCd LEFT JOIN so_subdealer_magazine_m C ON A.IdSubdealer = C.IdSubdealer LEFT JOIN so_ai_district_m D ON A.City = D.District_id "  	.$searchCnd . " ORDER BY A.IsActive DESC, B.DispOrder, A.Descript";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;	
}

public function getSubdealer($IdSubdealer){
	$sql = "SELECT  A.* FROM so_subdealer_m A WHERE A.IdSubdealer ='".$IdSubdealer."'";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;	
}

public function getSubdealerMagazineData($IdSubdealer){
	$sql = "SELECT  A.* FROM so_subdealer_magazine_m A WHERE A.IdSubdealer ='".$IdSubdealer."'";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;	
}

public function getSubdealerDealerItem() {
	$sql		= "SELECT * FROM so_target_item_group";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;
}


public function getMagazineCapacityData($MagazineCd){  /// remove this name
	$sql1 		= "SELECT * FROM so_subdealer_magazine_m WHERE MagazineCd = '".$MagazineCd."'";
	$returnData1	= $this->classBusiLogic->execSelectQuery($sql1);	
	$sql2		= "SELECT * FROM  so_subdealer_magazine_capacity WHERE MagazineCd = '".$MagazineCd."'";
	$returnData2	= $this->classBusiLogic->execSelectQuery($sql2);
	$returnData = array("MagazineDetailData"=>$returnData1)+array("MagacineCapacityData"=>$returnData2);
	return $returnData;
}

public function getMagazineCapacity($IdSubdealer){  /// remove this name
	$sql = "SELECT * FROM  so_subdealer_magazine_capacity WHERE IdSubdealer = '".$IdSubdealer."'";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;
}

public function checkMagazineDockey($dockey){
	$sql = " SELECT COUNT(*) AS Count FROM so_subdealer_magazine_m WHERE LicDocKey='".$dockey."'";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);
	if($returnData[0]['Count']>0){
		return 1;
	}else{
		return 0;
	}
}

public function RemoveSubdealerPenalty($IdSubdealer){
	$curMonth	= date('n');
	$curYear	= date('Y');
	$curLogYear	= $curYear . '-' . ($curYear+1);		// eg 2011-2012
	$curLogMonth= (($curMonth+8) % 12) + 1;				
	$dateFrom   = date("Y-m-01");
	$dateTo     =  date("Y-m-t");

	$sql 		= " UPDATE so_subdealer_m SET IsPenalty = '0' WHERE IdSubdealer = '".$IdSubdealer."' ";
	$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
	if($this->objDBQuery->objDBConnect->affected_rows > 0){
		$sqlinsert = "INSERT INTO so_subdealer_m_penaltylog (IdSubdealer, Month, Year, DateFrom, DateTo, CreatedBy, CreatedDateTime) VALUES ('".$IdSubdealer."','".$curLogMonth."','".$curLogYear."','".$dateFrom."','".$dateTo."','".$_SESSION['UserName']."','".date('Y-m-d H:i:s')."')";
		$returnData	= $this->classBusiLogic->execUpdateQuery($sqlinsert);
		return 1;
	}else{
		return 0;
	}	
}

public function insertSubdealer($data) {
	$stmt = " INSERT INTO so_subdealer_m (SubdealerCd,VenderCd, SubdealerCtgCd, Descript, Station,  City, StateCd, OperationType, WorkType, RunnerName, RunnerRelation, TelNo, MobileNo, EMailId, PAN, IsLoyality, IsActive, FirmType, AllowTrgt, StatusR_N, NotificationProp, NotificationRunner, NotificationManager)
	VALUES ('%s','%s', '%s', '%s', '%s', '%s', '%s', '%s',  '%s',  '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s', '%s', '%s', '%s','%s');";
	$inputArr   = array($data['SubdealerCd'], $data['VenderCd'],$data['SubdealerCtgCd'], addslashes(trim(ucfirst($data['Descript']))), addslashes(trim(ucfirst($data['Station']))), trim(ucfirst($data['City'])), $data['StateCd'], $data['OperationType'], $data['WorkType'], ucfirst($data['RunnerName']), $data['RunnerRelation'], trim($data['TelNo1']), trim($data['TelNo2']), trim($data['EMailId']), $data['PAN'], (int)$data['IsLoyality'], (int)$data['IsActive'],  $data['FirmType'], (int) $data['AllowTrgt'], $data['StatusR_N'], $data['NotificationProp'], $data['NotificationRunner'], $data['NotificationManager']);
	$sql        = vsprintf($stmt,$inputArr);
	$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
	return $returnData;
}

public function updateSubdealer($data){
	$stmt = " UPDATE so_subdealer_m SET SubdealerCtgCd = '%s', Descript = '%s', Station = '%s',City = '%s',StateCd = '%s',OperationType = '%s', WorkType = '%s',RunnerName = '%s',RunnerRelation = '%s',TelNo = '%s',MobileNo = '%s',EMailId = '%s',PAN = '%s',IsLoyality = '%s',IsActive = '%s', FirmType= '%s', AllowTrgt = '%s',StatusR_N = '%s', NotificationProp='%s', NotificationRunner='%s', NotificationManager='%s' WHERE IdSubdealer = '%s'";
	$inputArr   = array($data['SubdealerCtgCd'], addslashes(trim(ucfirst($data['Descript']))), addslashes(trim(ucfirst($data['Station']))), trim(ucfirst($data['City']))	, $data['StateCd'], $data['OperationType'], $data['WorkType'], ucfirst($data['RunnerName']), $data['RunnerRelation'],  trim($data['TelNo1']), trim($data['TelNo2']), trim($data['EMailId']), $data['PAN'], (int)$data['IsLoyality'], (int)$data['IsActive'], $data['FirmType'], (int) $data['AllowTrgt'], $data['StatusR_N'],$data['NotificationProp'], $data['NotificationRunner'], $data['NotificationManager'], $data['IdSubdealer']);
	$sql        = vsprintf($stmt,$inputArr);
	$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
	return $returnData;
}
// INSERT SUBDEALER MAGAZINE


public function getDistrictMDisp() {
	$sql		= "SELECT * FROM so_ai_district_m order by District";
	$returnData = $this->classBusiLogic->execSelectQuery($sql);
	return $returnData;	
}

public function insertSubdealerMagazineQuery(){
	$sql = "INSERT INTO so_subdealer_magazine_m (IdSubdealer, MagazineLicNo, LicDocKey,  LicExpiryDate, MagazineType, IsActive)VALUES ('%s', '%s', '%s', '%s', '%s',  '%s');";
	return $sql;
}

public function updateSubdealerMagazineQuery(){
	$sql = "UPDATE so_subdealer_magazine_m  SET MagazineLicNo = '%s', LicDocKey  = '%s',   LicExpiryDate  = '%s', MagazineType = '%s', IsActive = '%s' WHERE MagazineCd = '%s' ;";
	return $sql;
}

public function insertSubdealerMagazine($data) {
	$queryInsert = $this->insertSubdealerMagazineQuery();
	$IdSubdealer = $data['IdSubdealer'];
	for($i=1;$i<= $data['lastMagazineRow'];$i++){
		$MagazineLicNo 	= addslashes(trim($data['MagazineLicNo1_'.$i]));
		$LicDocKey 		= trim($data['MagazineLicDocKey1_'.$i]);
		$LicExpiryDate 	= $this->classBusiLogic->convertDate($data['MagazineLicExpiryDate1_'.$i], True, 1);
		$MagazineType = trim($data['MagazineType1_'.$i]);
		$IsActive 		= trim($data['MagazineIsActive1_'.$i]);
		$inputArr    	= array($IdSubdealer,$MagazineLicNo, $LicDocKey, $LicExpiryDate, $MagazineType, $IsActive );
		$sql        	= vsprintf($queryInsert,$inputArr);
		$returnData		= $this->classBusiLogic->execUpdateQuery($sql);
		if($returnData) return $returnData;
	}
	return $returnData;
}

public function updateSubdealerMagazine($data) {
	$queryInsert = $this->insertSubdealerMagazineQuery();
	$queryUpdate = $this->updateSubdealerMagazineQuery();
	$IdSubdealer = $data['IdSubdealer'];
	$returnData='';
	for($i=1;$i<= $data['lastMagazineRow'];$i++){
		$MagazineCd 	= trim($data['MagazineCd1_'.$i]);
		$MagazineLicNo 	= addslashes(trim($data['MagazineLicNo1_'.$i]));
		$LicDocKey 		= trim($data['MagazineLicDocKey1_'.$i]);
		$LicExpiryDate 	= $this->classBusiLogic->convertDate($data['MagazineLicExpiryDate1_'.$i], True, 1);
		$MagazineType = trim($data['MagazineType1_'.$i]);
		$IsActive 		= trim($data['MagazineIsActive1_'.$i]);
		if($MagazineCd >0){
			$inputArr    	= array($MagazineLicNo, $LicDocKey, $LicExpiryDate, $MagazineType, $IsActive,$MagazineCd);
			 $sql        	= vsprintf($queryUpdate,$inputArr);
			$returnData		= $this->classBusiLogic->execUpdateQuery($sql);
			if($returnData) return $returnData;
		}else{
			$inputArr    	= array($IdSubdealer,$MagazineLicNo, $LicDocKey, $LicExpiryDate, $MagazineType, $IsActive );
			$sql        	= vsprintf($queryInsert,$inputArr);
			$returnData		= $this->classBusiLogic->execUpdateQuery($sql);
			if($returnData) return $returnData;
		}
	}
	return $returnData;
}


public function addSubdealerMagazineDetails($data){
	$stmt 		=	 " UPDATE so_subdealer_magazine_m SET MagazineDetail='%s',  Place = '%s', Descript = '%s', OccupierName = '%s', Taluka = '%s', District = '%s', StateCd = '%s', Pincode='%s' WHERE MagazineCd = '%s'  ";
	$inputArr  	= array(ucfirst($data['MagazineDetail']),ucfirst($data['Place']),'',ucfirst($data['OccupierName']),ucfirst($data['Taluka']),$data['District'],$data['StateCd'], $data['Pincode'],$data['MagazineCd']);
	 $sql		= vsprintf($stmt,$inputArr);
	$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
	if(!$returnData){
		$this->addsubdealerMagazineCapacity($data);
	}
	
	//return $returnData;
}

public function insertsubdealerMagazineCapacityQuery(){
	$sql = " INSERT INTO  so_subdealer_magazine_capacity (IdSubdealer,MagazineCd,ItemCtgCd,Capacity,RotationCapacity) VALUES('%s','%s','%s','%s','%s')";
	return $sql;
}

public function updatesubdealerMagazineCapacityQuery(){
	$sql = " UPDATE  so_subdealer_magazine_capacity SET MagazineCd='%s',ItemCtgCd='%s',Capacity='%s',RotationCapacity='%s' WHERE IdSubdealerMagazine='%s' AND IdSubdealer='%s'";
	return $sql;
}


public function addsubdealerMagazineCapacity($data){
	$returnData 		= array();
	$queryInsert		= $this->insertsubdealerMagazineCapacityQuery();
	$queryUpdate		= $this->updatesubdealerMagazineCapacityQuery();
	$MagazineCd 		= $data['MagazineCd'];
	$IdSubdealer 		= $data['IdSubdealer'];
	for($curRowNo=1; $curRowNo<=$data['lastSubdealerMagazineCapacityRow']; $curRowNo++) {
		$ItemCtgCd		= $data['ItemCtgCd1_'.$curRowNo];	
		$Capacity   	= $data['Capacity1_'.$curRowNo];	
		$RotationCapacity = $data['RotationCapacity1_'.$curRowNo];	
		$UniqueId		= $data['UniqueId1_'.$curRowNo];		
		if($Capacity == ''){
			continue;
		}
		if($UniqueId>0){
			$inputArr 	= array( $MagazineCd,$ItemCtgCd, $Capacity,$RotationCapacity,$UniqueId, $IdSubdealer);
			$sql		= vsprintf($queryUpdate,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		}else{
			$inputArr 	= array($IdSubdealer, $MagazineCd,$ItemCtgCd, $Capacity,$RotationCapacity);
			$sql		= vsprintf($queryInsert,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		}
	}
}

/********************* Subdealer Dealer *********************************/
public function getSubdealerDealerInsertQuery() {
	$sql	= "INSERT INTO so_subdealer_m_dealer (IdSubdealer, CustomerCd, RegnDate, SignoutDate, Status, Relation, Preference)  VALUES (%s, '%s', '%s','%s', '%s', '%s', '%s');";
	return $sql ;
}
public function getSubdealerDealerUpdateQuery() {
	$sql	= "UPDATE so_subdealer_m_dealer SET IdSubdealer=%s, CustomerCd='%s', RegnDate='%s', SignoutDate='%s', Status='%s', Relation='%s', Preference='%s', PercentageOfWork='%s'
	, PercentageStatus='%s', CreditNoOfDays='%s', CreditLimitAmount='%s', SubdealerMagazineDistance='%s' WHERE UniqueId=%s";
	return $sql ;
}

public function getSubdealerDealerDeleteQuery() {
	$sql	= "DELETE FROM so_subdealer_m_dealer WHERE UniqueId=%s";
	return $sql ;
}

public function insertSubdealerDealer($data){
	$returnData 	= array();
	$queryInsert	= $this->getSubdealerDealerInsertQuery();
	$queryUpdate	= $this->getSubdealerDealerUpdateQuery();
	for($curRowNo=1; $curRowNo<=$data['lastSubdealerDealerRow']; $curRowNo++) {
		if (!isset($data['CustomerCd1_'.$curRowNo]) || trim($data['CustomerCd1_'.$curRowNo])==''){
			continue;
		}
		$UniqueId = $data['UniqueId1_'.$curRowNo];
		if($UniqueId >0){			
			$sqlQuery = "SELECT * FROM so_subdealer_m_dealer WHERE UniqueId = '".$UniqueId."'";
			$resultSelect = $this->classBusiLogic->execSelectQuery($sqlQuery);
			$RegnDate1 = $this->classBusiLogic->convertDate($data['RegnDate1_'.$curRowNo], TRUE, 1);
			$OldDealerCd= $data['OldCustomerCd1_'.$curRowNo];
			$CustomerCd = $data['CustomerCd1_'.$curRowNo];
			if($OldDealerCd == $CustomerCd){
				$SignoutDate = $this->classBusiLogic->convertDate($data['SignoutDate1_'.$curRowNo], TRUE, 1);
				$status 	 = $data['Status_'.$curRowNo];
				$Relation 	 = $data['Relation_'.$curRowNo];
				$Preference  = $data['Preference_'.$curRowNo];
				$PercentageOfWork  = $data['PercentageOfWork1_'.$curRowNo];
				$PercentageStatus  = $data['PercentageStatus_'.$curRowNo];
				$CreditNoOfDays  = $data['CreditNoOfDays1_'.$curRowNo];
				$CreditLimitAmount  = $data['CreditLimitAmount1_'.$curRowNo];
				$SubdealerMagazineDistance  = $data['SubdealerMagazineDistance1_'.$curRowNo];
			}else{
				$SignoutDate='';
				$status 	 = '';
				$Relation 	 = '';
				$Preference  = '';
				$PercentageOfWork  = '';
				$PercentageStatus  = '';
				$CreditNoOfDays  = '';
				$CreditLimitAmount  = '';
				$SubdealerMagazineDistance  = '';
			}
			$inputArr	= array((int)$data['IdSubdealer'], $data['CustomerCd1_'.$curRowNo],$RegnDate1, $SignoutDate,$Status, $Relation
			,$Preference, $PercentageOfWork, $PercentageStatus, $CreditNoOfDays, $CreditLimitAmount, $SubdealerMagazineDistance, $UniqueId);
			$sql		= vsprintf($queryUpdate,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			$rowAffected =  $this->objDBQuery->objDBConnect->affected_rows;
			if($rowAffected>0){
				//$this->subdealerDealerChangeLog($resultSelect,$data['CustomerCd1_'.$curRowNo],$UniqueId);
			}
			if ($returnData) return $returnData;
		}else{
			$inputArr	= array((int)$data['IdSubdealer'], $data['CustomerCd1_'.$curRowNo], $this->classBusiLogic->convertDate($data['RegnDate1_'.$curRowNo], TRUE, 1), $this->classBusiLogic->convertDate($data['SignoutDate1_'.$curRowNo], TRUE, 1),$data['Status_'.$curRowNo], $data['Relation_'.$curRowNo], $data['Preference_'.$curRowNo]);
			$sql		= vsprintf($queryInsert,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			if ($returnData) return $returnData;
		}
	}
	if($returnData == null){
		return 1;				
	}else{
		return 0;
	}	
}


public function subdealerDealerChangeLog($result,$CustomerCd,$UniqueId){
	if(($result[0]['CustomerCd'] != $CustomerCd) || ($result[0]['PercentageOfWork'] != $CustomerCd)){
		$sql = "INSERT INTO `so_subdealer_m_dealer_log`( `TransLogId`, `IdSubdealer`, `SubdealerCd`, `Preference`, `CustomerCd`, `RegnDate`, `SignoutDate`, `Status`, `Relation`, `PercentageOfWork`, `TransUserName`, `TransDateTime`, `CreditNoOfDays`, `CreditLimitAmount`, `SubdealerMagazineDistance`, `DealerApprovedUserName`, `DealerApprovedDateTime`, `ApprovedUserName`, `ApprovedDateTime`) VALUES ('".$UniqueId."','".$result[0]['IdSubdealer']."','".$result[0]['SubdealerCd']."','".$result[0]['Preference']."','".$result[0]['CustomerCd']."','".$result[0]['RegnDate']."','".$result[0]['SignoutDate']."', '".$result[0]['Status']."', '".$result[0]['Relation']."','".$result[0]['PercentageOfWork']."','".$result[0]['TransUserName']."','".$result[0]['TransDateTime']."','".$result[0]['CreditNoOfDays']."','".$result[0]['CreditLimitAmount']."','".$result[0]['SubdealerMagazineDistance']."','".$result[0]['DealerApprovedUserName']."','".$result[0]['DealerApprovedDateTime']."','".$result[0]['ApprovedUserName']."','".$result[0]['ApprovedDateTime']."')";
		$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
	}
}
	
//==================================Subdealer Dealer Percentage ========================================
	
	public function insertSubdealerDealerPercenatgeQuery(){
	$sql = "INSERT INTO so_subdealer_m_dealer(PercentageOfWork, PercentageStatus, TransUserName, TransDateTime ) VALUES ('%s','%s','%s', '%s')";
	return $sql;
	}

	
	public function UpdateSubdealerDealerPercenatgeQuery(){
		$sql = " UPDATE so_subdealer_m_dealer SET PercentageOfWork='%s',PercentageStatus='%s', TransUserName = '%s',TransDateTime = '%s' WHERE CustomerCd = '%s' AND IdSubdealer = '%s' and UniqueId='%s'";
		return $sql;
	}
	
	public function insertSubdealerDealerPercenatge($postData){
		parse_str($postData, $data);
		$sql		 	= '';
		$returnData 	= '0';
		$queryInsert 	=  $this->insertSubdealerDealerPercenatgeQuery();
		$queryUpdate 	=  $this->UpdateSubdealerDealerPercenatgeQuery();
		$IdSubdealer	= $data['IdSubdealer'];
		
		for($curRowNo = 1; $curRowNo<=$data['LastRow'];$curRowNo++){
			$UniqueId			= $data['UniqueId'.$curRowNo];
			$CustomerCd	 		= $data['Dealer'.$curRowNo];	
			//$Preferance			= $data['Preferance_'.$curRowNo];
			$Percentage			= $data['Percentage'.$curRowNo];
			$TransUserName		= $_SESSION['UserName'];
			$TransDateTime		= date('Y-m-d H:i:s');
			$IsApproved			= isset($data['CheckedRow_'.$curRowNo]) ? 1:0;
			
			if($CustomerCd =='' && $Percentage=='' ){
				continue;
			}
			
			if($UniqueId>0){
				$sqlQuery = "SELECT * FROM so_subdealer_m_dealer WHERE UniqueId = '".$UniqueId."'";
				$resultSelect = $this->classBusiLogic->execSelectQuery($sqlQuery);				
				$inputArr	= array($Percentage,0, $TransUserName,$TransDateTime, $CustomerCd, $IdSubdealer, $UniqueId);
				$sql		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				$rowAffected =  $this->objDBQuery->objDBConnect->affected_rows;
				if ($returnData) return $returnData;
			}
			else{
				$inputArr	= array($Percentage,0, $TransUserName,$TransDateTime);
				$sql		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}											
		}
		if($returnData ==''){
			return 1; 
		}
		else{
		return 0;
		}
		
	}

	public function getDealerSubdealerChangeLog($IdSubdealer){
		$sql = " SELECT CreditLimitAmount, CreditNoOfDays, SubdealerMagazineDistance, DATE_FORMAT(RegnDate,'%d-%m-%Y') As RegDate, DATE_FORMAT(SignoutDate,'%d-%m-%Y') As SignoutDate,DATE_FORMAT(A.TransDateTime,'%d-%m-%Y') As TimeStamp,A.PercentageOfWork, IFNULL(B.Descript,A.CustomerCd) AS Customer, ApprovedUserName, ApprovedDateTime FROM so_subdealer_m_dealer_log A INNER JOIN so_customer_m B ON A.CustomerCd = B.CustomerCd WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

/********************* End Subdealer Dealer *********************************/

//===========================SUbdealer Item Master================================================

	public function getSubdealerItemM($IdSubdealer){  /// remove this name
		$sql = "SELECT * FROM  so_subdealer_item WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}

	public function insertItemSubdealerQuery(){
		$sql = "INSERT INTO so_subdealer_item (IdSubdealer, ItemMStatus,  ItemCd, Priority, SaleStatus,MaxLotSize, FromDate, ToDate, Authority, CreatedBy) VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
		return $sql ;
	}
	
	public function updateItemSubdealerQuery(){
		$sql = "Update so_subdealer_item SET ItemMStatus='%s',  Priority='%s', SaleStatus='%s', MaxLotSize='%s', FromDate='%s', ToDate='%s', Authority='%s', CreatedBy='%s' where IdSubdealer='%s' AND ItemCd='%s'";
		return $sql ;
	}
	
	public function insertItemSubdealer($postData){
		parse_str($postData, $data);
			$ItemCount 		= $data['ItemCount'];
		for($i=1; $i<=$ItemCount; $i++){
			$queryInsert    =  $this->insertItemSubdealerQuery();
			$queryUpdate    =  $this->updateItemSubdealerQuery();
			
			$IdSubdealer 	= $data['IdSubdealer'];
			$ItemMStatus 	= $data['ItemMStatus'];
			$UniqueId	 	= $data['UniqueId_'.$i];
			$ItemCd		 	= $data['ItemCd_'.$i];
			$ItemPriority 	= $data['ItemPriority_'.$i];
			$ItemStatus 	= $data['ItemStatus_'.$i];
			$FromDate 		= $data['FromDate_'.$i];
			$ToDate 		= $data['ToDate_'.$i];
			$Authority 		= $data['Authority_'.$i];
			$MaxLotSize 	= $data['MaxLotSize_'.$i];
			$CreatedBy 		= $_SESSION['UserName'];
			
			
			if($UniqueId>0){
				$inputArr	= array( $ItemMStatus, $ItemPriority, $ItemStatus, $MaxLotSize, $this->classBusiLogic->convertDate($FromDate, TRUE, 1), $this->classBusiLogic->convertDate($ToDate, TRUE, 1), $Authority, $CreatedBy, $IdSubdealer, $ItemCd);
				 $sql		= vsprintf($queryUpdate,$inputArr);					
			}else{
				$inputArr	= array($IdSubdealer, $ItemMStatus, $ItemCd, $ItemPriority, $ItemStatus, $MaxLotSize, $this->classBusiLogic->convertDate($FromDate, TRUE, 1), $this->classBusiLogic->convertDate($ToDate, TRUE, 1), $Authority, $CreatedBy);
				 $sql		= vsprintf($queryInsert,$inputArr);					
			}
			if($ItemPriority!='' && $ItemStatus!='' ){
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;				
			}

		}
		if($returnData == null){
					return 1;				
				}else{
					return 0;
				}
	}

	
/********************** Subdealer partner Data ****************************/

	public function getSubdealerPartnerData($IdSubdealer){
		$sql = " SELECT *, DATE_FORMAT(DOB,'%d-%m-%Y') AS DOB FROM so_subdealer_m_partner_details WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	public function insertBusinessPartnerQuery(){
		$sql = " INSERT INTO `so_subdealer_m_partner_details` ( `IdSubdealer`, `StackHolderType`, `BuisnessPercentage`, `PartnerFirstName`, `PartnerMiddleName`, `PartnerLastName`, `Gender`, `DOB`, `IdType`, `IdNumber`,  `Education`, `Specialization`, `ITLiteracy`, `NickName`, `Nature`, `LanguageLiteracy`, `OtherLanguage`, `StateCd`, `District`, `Tahshil`, `AtPost`, `City`, `PinCode`, `MobileNo1`, `MobileNo2`, `EmergencyNo`, `EmailId`, `WhatsAppNo`) VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ";
		return $sql ;
	}
	
	public function updateBusinessPartnerQuery(){
		$sql = " UPDATE  so_subdealer_m_partner_details SET StackHolderType = '%s', BuisnessPercentage = '%s', PartnerFirstName = '%s', PartnerMiddleName = '%s', PartnerLastName = '%s', Gender = '%s', DOB = '%s', IdType = '%s', IdNumber = '%s',  Education = '%s', Specialization = '%s', ITLiteracy= '%s', NickName= '%s', Nature= '%s', LanguageLiteracy= '%s', OtherLanguage= '%s', StateCd= '%s', District= '%s', Tahshil= '%s', AtPost= '%s', City= '%s', PinCode= '%s', MobileNo1= '%s', MobileNo2= '%s', EmergencyNo= '%s', EmailId= '%s', WhatsAppNo= '%s' WHERE SubdealerPartnetId = '%s' ";
		return $sql ;
	}
	public function insertBusinessPartner($postData){
		parse_str($postData, $data);
		$queryInsert    =  	$this->insertBusinessPartnerQuery();
		$queryUpdate    =  	$this->updateBusinessPartnerQuery();
		$IdSubdealer 	= 	$data['IdSubdealer'];
		$LastPartnerRow = 	$data['LastPartnerRow'];
		for($curRowNo=1;$curRowNo <= $LastPartnerRow;$curRowNo++ ){
			$UniqueId 			= $data['PartnerUniqueId_'.$curRowNo];
			$Gender 			= isset($data['Gender_'.$curRowNo])? $data['Gender_'.$curRowNo]:'';
			$Education 			= isset($data['Education_'.$curRowNo])? $data['Education_'.$curRowNo]:'';
			$ITLiteracy 		= isset($data['ITLiteracy_'.$curRowNo])? $data['ITLiteracy_'.$curRowNo]:'';
			$LanguageLiterecy 	= isset($data['LanguageLiterecy_'.$curRowNo])? $data['LanguageLiterecy_'.$curRowNo]:'';$WhatsAppNo   	   = isset($data['WhatsAppNo_'.$curRowNo])? $data['WhatsAppNo_'.$curRowNo]:'';
			$StackHolderType   = ucfirst($data['StackHolderType_'.$curRowNo]);
			$BuisnessPercentage   = ucfirst($data['BuisnessPercentage_'.$curRowNo]);
			$PartnerFirstName   = ucfirst($data['PartnerFirstName_'.$curRowNo]);
			$PartnerMiddleName  = ucfirst($data['PartnerMiddleName_'.$curRowNo]);
			$PartnerLastName    = ucfirst($data['PartnerLastName_'.$curRowNo]);
			$DOB 				= $this->classBusiLogic->convertDate($data['DOB_'.$curRowNo], True, 1);
			$IdType 			= $data['IdType_'.$curRowNo];
			$IdNumber 			= $data['IdNumber_'.$curRowNo];
			$Specialization 	= $data['Specialization_'.$curRowNo];
			$NickName 			= $data['NickName_'.$curRowNo];
			$Nature 			= $data['Nature_'.$curRowNo];
			$OtherLanguage      = $data['OtherLanguage_'.$curRowNo];
			$StateCd 			= $data['StateCd_'.$curRowNo];
			$District 			= ucfirst($data['District_'.$curRowNo]);
			$Tahshil 			= ucfirst($data['Tahshil_'.$curRowNo]);
			$AtPost 			= ucfirst($data['AtPost_'.$curRowNo]);
			$City 				= ucfirst($data['City_'.$curRowNo]);
			$PinCode 			= $data['PinCode_'.$curRowNo];
			$MobileNo1 			= $data['MobileNo1_'.$curRowNo];
			$MobileNo2 			= $data['MobileNo2_'.$curRowNo];
			$EmergencyNo 		= $data['EmergencyNo_'.$curRowNo];
			$EmailId 			= $data['EmailId_'.$curRowNo];
			if($UniqueId>0){
				$inputArr = array($StackHolderType, $BuisnessPercentage, $PartnerFirstName,$PartnerMiddleName,$PartnerLastName,$Gender,$DOB,$IdType,$IdNumber,$Education,$Specialization,$ITLiteracy,$NickName,$Nature,$LanguageLiterecy,$OtherLanguage,$StateCd,$District,$Tahshil,$AtPost,$City ,$PinCode,$MobileNo1,$MobileNo2,$EmergencyNo,$EmailId,$WhatsAppNo,$UniqueId);
				$sql		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}else{
				$inputArr = array($IdSubdealer,$StackHolderType, $BuisnessPercentage, $PartnerFirstName,$PartnerMiddleName,$PartnerLastName,$Gender,$DOB,$IdType,$IdNumber,$Education,$Specialization,$ITLiteracy,$NickName,$Nature,$LanguageLiterecy,$OtherLanguage,$StateCd,$District,$Tahshil,$AtPost,$City ,$PinCode,$MobileNo1,$MobileNo2,$EmergencyNo,$EmailId,$WhatsAppNo);
				$sql		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;	
			}			
		}
		if($returnData == null){
		return 1;				
		}else{
			return 0;
		}
	}
	
	/********************** end partner *******************************/
	
	/************************ office*******************************/
	public function getSubdealerOfficeDetailsData($IdSubdealer){
		$sql = " SELECT  * FROM so_subdealer_m_officeconsultancy_details WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	public function getSubdealerStaffDetailsData($IdSubdealer){
		$sql = " SELECT  * FROM so_subdealer_m_staff_details WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	public function insertSubdealerOfficeDetailsQuery(){
		$sql = " INSERT INTO so_subdealer_m_officeconsultancy_details (IdSubdealer, Address, StateCd, District, Tahsil, AtPost, City, Pincode, MobileNo, LandLine, EmailId, ConsultancyName, ContactPersonName, ConsultantMobileNo, Remark) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ";
		return $sql;
	}

	public function updateSubdealerOfficeDetailsQuery(){
		$sql = " UPDATE so_subdealer_m_officeconsultancy_details SET Address = '%s', StateCd= '%s', District= '%s', Tahsil= '%s', AtPost= '%s', City= '%s', Pincode= '%s', MobileNo= '%s', LandLine= '%s', EmailId= '%s', ConsultancyName= '%s', ContactPersonName= '%s', ConsultantMobileNo= '%s', Remark= '%s'WHERE Id= '%s' AND IdSubdealer = '%s' ";
		return $sql;
	}
	

	public function insertSubdealerOfficeDetails($postData){
		parse_str($postData, $data);
		$queryInsert = $this->insertSubdealerOfficeDetailsQuery();
		$queryUpdate = $this->updateSubdealerOfficeDetailsQuery();
		$IdSubdealer = $data['IdSubdealer'];
		$Address 	= $data['OfficeAddress'];
		$StateCd 	= $data['StateCd'];
		$District 	= $data['District'];
		$Tahsil  	= $data['Tehsil'];
		$AtPost 	= $data['AtPost'];
		$City 		= $data['City'];
		$Pincode 	= $data['Pincode'];
		$MobileNo 	= $data['MobileNo'];
		$LandLine 	= $data['LandLine'];
		$EmailId 	= $data['EmailId'];
		$ConsultancyName 	= $data['ConsultancyName'];
		$ContactPersonName 	= $data['ContactPersonName'];
		$ConsultantMobileNo = $data['ConsultantMobileNo'];
		$Remark 	= $data['Remark'];
	
		$UniqueIdOfficeData 	= $data['UniqueIdOfficeData'];
		if($UniqueIdOfficeData >0){
			$inputArr 	= array($Address,$StateCd,$District,$Tahsil,$AtPost,$City,$Pincode,$MobileNo,$LandLine,$EmailId,$ConsultancyName,$ContactPersonName,$ConsultantMobileNo,$Remark,$UniqueIdOfficeData,$IdSubdealer);
			$sql  		= vsprintf($queryUpdate,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			
			$this->insertSubdealerStaffDetails($data);
			
		}else{
			$inputArr 	= array($IdSubdealer,$Address,$StateCd,$District,$Tahsil,$AtPost,$City,$Pincode,$MobileNo,$LandLine,$EmailId,$ConsultancyName,$ContactPersonName,$ConsultantMobileNo,$Remark);
			$sql  		= vsprintf($queryInsert,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			$this->insertSubdealerStaffDetails($data);
		}
		if($returnData == null){
			return 1;				
		}else{
			return 0;
		}
	}
	
		public function insertSubdealerStaffDetailsQuery(){
		$sql = " INSERT INTO so_subdealer_m_staff_details  (IdSubdealer, EmployeeType, ManagerType, FirstName, MiddleName, LastName, MobileNo1, MobileNo2, DateofJoining, Relation, Qualification, ItLiteracy, PreviousFirmName, WorkExperience)  VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ";
		return $sql;
	}
	
	public function updateSubdealerStaffDetailsQuery(){
		$sql = " UPDATE  so_subdealer_m_staff_details  SET EmployeeType='%s', ManagerType='%s', FirstName= '%s', MiddleName= '%s', LastName= '%s', MobileNo1= '%s', MobileNo2= '%s', DateofJoining= '%s', Relation= '%s', Qualification= '%s', ItLiteracy= '%s', PreviousFirmName= '%s', WorkExperience= '%s' WHERE SubdealerStaffId ='%s' AND IdSubdealer = '%s' ";
		return $sql;
	}
	

	public function insertSubdealerStaffDetails($data){
		$queryInsert 		= $this->insertSubdealerStaffDetailsQuery();
		$queryUpdate		= $this->updateSubdealerStaffDetailsQuery();
		$IdSubdealer 		= $data['IdSubdealer'];
		$lastOfficestaffRow = $data['lastOfficestaffRow'];
		for($curRow = 1;$curRow<=$lastOfficestaffRow;$curRow++){
			$EmployeeType			= $data['EmployeeType_'.$curRow];
			$ManagerType			= $data['ManagerType_'.$curRow];
			$FirstName				= $data['FirstName_'.$curRow];
			$MiddleName				= $data['MiddleName_'.$curRow];
			$LastName				= $data['LastName_'.$curRow];
			$MobileNo1				= $data['MobileNo1_'.$curRow];
			$MobileNo2				= $data['MobileNo2_'.$curRow];
			$DateofJoining			= $this->classBusiLogic->convertDate($data['DateofJoining_'.$curRow], True, 1);
			$Relation				= $data['Relation_'.$curRow];
			$Qualification			= $data['Qualification_'.$curRow];
			$ItLiteracy				= $data['ItLiteracy_'.$curRow];
			$PreviousFirmName		= $data['PreviousFirmName_'.$curRow];
			$WorkExperience			= $data['WorkExperience_'.$curRow];
			
			
			$UniqueStaffId		= $data['UniqueStaffId_'.$curRow];
			if($UniqueStaffId>0){
				$inputArr  = array($EmployeeType, $ManagerType, $FirstName,$MiddleName,$LastName,$MobileNo1,$MobileNo2,$DateofJoining,$Relation,$Qualification,$ItLiteracy,$PreviousFirmName,$WorkExperience, $UniqueStaffId,$IdSubdealer);
				$sql  		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;	
			}else{
				$inputArr  = array($IdSubdealer,$EmployeeType,$ManagerType, $FirstName,$MiddleName,$LastName,$MobileNo1,$MobileNo2,$DateofJoining,$Relation,$Qualification,$ItLiteracy,$PreviousFirmName,$WorkExperience);
				$sql  		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;	
			}			 
		}
		return $returnData;
	}

/********************************* end  office *******************************/

/*********************** Business Scenario ************************/
	public function getBusinessScenarioData($IdSubdealer){
		$sql = " SELECT  * , DATE_FORMAT(BusinessHolder_DOB,'%d-%m-%Y') as DOB FROM so_subdealer_m_businessscenario WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	public function getBusinessOperationData($IdSubdealer){
		$sql = " SELECT  * FROM so_subdealer_m_businessoperation_details WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	public function getBusinessManufacturerDealerData($IdSubdealer){
		$sql = " SELECT  * FROM so_subdealer_m_manufacturer_dealer_details WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}



	public function insertBusinessScenarioQuery(){
		$sql = " INSERT INTO   `so_subdealer_m_businessscenario`  (IdSubdealer, `BusinessHolder_FirstName`, `BusinessHolder_MiddleName`, `BusinessHolder_LastName`, `BusinessHolder_DOB`, `BusinessHolder_City`, `BusinessHolder_District`, `BusinessHolder_StateCd`, `BusinessHolder_Qualification`, `BusinessHolder_BuisnessNature`, `BusinessHolder_PersonalNature`, `BusinessHolder_NoOfFamilyNo`) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') ";
		return $sql;
	}
	
	public function insertBusinessOperationQuery(){
		$sql = "  INSERT INTO  so_subdealer_m_businessoperation_details (IdSubdealer,`BusinessOperation_Type`, `FirmName`, `City`, `PercentageOfHolding`, `BusinessOperation`, `BusinessName`, `YearlyTurnOver`, `Status`, `RelationWithFirm`, `Remark` ) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
		return $sql;
	}
	public function insertBusinessManufacturerDealerQuery(){
		$sql = " INSERT INTO so_subdealer_m_manufacturer_dealer_details ( IdSubdealer, ManuDealerType, ExplosivesType, FirstName, MiddleName, LastName, Relationship, RelationshipStatus, RelationshipStartDate, RelationshipEndDate, BreakReason, Remark, LatestMarketUpdate) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
		return $sql;
	}
	
	public function updateBusinessScenarioQuery(){
		$sql = " UPDATE`so_subdealer_m_businessscenario`  SET  `BusinessHolder_FirstName` ='%s' , `BusinessHolder_MiddleName` ='%s' , `BusinessHolder_LastName` ='%s' , `BusinessHolder_DOB` ='%s' , `BusinessHolder_City` ='%s' , `BusinessHolder_District` ='%s' , `BusinessHolder_StateCd` ='%s' , `BusinessHolder_Qualification` ='%s' , `BusinessHolder_BuisnessNature` ='%s' , `BusinessHolder_PersonalNature` ='%s' , `BusinessHolder_NoOfFamilyNo` ='%s'  WHERE IdSubdealer = '%s' AND BusinessScenarioId ='%s' ";
		return $sql;
	}
	
	public function updateBusinessOperationQuery(){
		$sql = "  UPDATE so_subdealer_m_businessoperation_details SET BusinessOperation_Type = '%s', FirmName= '%s', City= '%s', PercentageOfHolding= '%s', `BusinessOperation`= '%s', `BusinessName`= '%s', `YearlyTurnOver`= '%s', `Status`= '%s', `RelationWithFirm`= '%s', `Remark`= '%s' WHERE IdSubdealer ='%s' AND BusinessOPerationId = '%s'";
		return $sql;
	}
	
	public function updateBusinessManufacturerDealerQuery(){
		$sql = " UPDATE so_subdealer_m_manufacturer_dealer_details SET   ManuDealerType ='%s' , ExplosivesType ='%s' , FirstName ='%s' , MiddleName ='%s' , LastName ='%s' , Relationship ='%s' , RelationshipStatus ='%s' , RelationshipStartDate ='%s' , RelationshipEndDate ='%s' , BreakReason ='%s' , Remark ='%s' , LatestMarketUpdate ='%s'  WHERE IdSubdealer ='%s' AND SubdealerManuDealerId  ='%s' ";
		return $sql;
	}
	public function insertBusinessScenario($postData){
		parse_str($postData, $data);
		$queryInsert 	= $this->insertBusinessScenarioQuery();
		$queryUpdate 	= $this->updateBusinessScenarioQuery();
		$IdSubdealer 					= $data['IdSubdealer'];
		//$NoOfBusiness 					= $data['NoOfBusiness'];
		//$FirmType						= isset($data['FirmType'])?$data['FirmType']:'';;
		$BusinessHolder_FirstName 		= $data['BusinessHolder_FirstName'];
		$BusinessHolder_MiddleName 		= $data['BusinessHolder_MiddleName'];
		$BusinessHolder_LastName 		= $data['BusinessHolder_LastName'];
		$BusinessHolder_DOB 			= $this->classBusiLogic->convertDate($data['BusinesHolder_DOB'], True, 1);
		$BusinessHolder_City 			= $data['BusinessHolder_City'];
		//$BusinessHolder_District 		= $data['BusinessHolder_District'];
		$BusinessHolder_StateCd 		= $data['BusinessHolder_StateCd'];
		$BusinessHolder_Qualification 	= $data['BusinessHolder_Qualification'];
		$BusinessHolder_BusinessNature 	= $data['BusinessHolder_BusinessNature'];
		$BusinessHolder_PersonalNature 	= $data['BusinessHolder_PersonalNature'];
		$BusinessHolder_NoOfFamilyNo 	= $data['BusinessHolder_NoOfFamilyNo'];
		
		
		$BusinessScenarioId 	= $data['BusinessScenarioId'];
		
		if($BusinessScenarioId){
			$inputArr  = array($BusinessHolder_FirstName,$BusinessHolder_MiddleName,$BusinessHolder_LastName,$BusinessHolder_DOB,$BusinessHolder_City,'',$BusinessHolder_StateCd,$BusinessHolder_Qualification,$BusinessHolder_BusinessNature,$BusinessHolder_PersonalNature,$BusinessHolder_NoOfFamilyNo,$IdSubdealer,$BusinessScenarioId);
			$sql  		= vsprintf($queryUpdate,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			$this->insertBusinessOperation($data);
			$this->insertBusinessManufacturerDealer($data);
			if ($returnData) return $returnData;
		}else{
			$inputArr  = array($IdSubdealer,$BusinessHolder_FirstName,$BusinessHolder_MiddleName,$BusinessHolder_LastName,$BusinessHolder_DOB,$BusinessHolder_City,'',$BusinessHolder_StateCd,$BusinessHolder_Qualification,$BusinessHolder_BusinessNature,$BusinessHolder_PersonalNature,$BusinessHolder_NoOfFamilyNo);
			$sql  		= vsprintf($queryInsert,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			$this->insertBusinessOperation($data);
			$this->insertBusinessManufacturerDealer($data);
			if ($returnData) return $returnData;
		}
		
		if($returnData == null){
			return 1;				
		}else{
			return 0;
		}
	}
	
	public function insertBusinessOperation($data){
	//	parse_str($postData, $data);
		$queryInsert 				= $this->insertBusinessOperationQuery();
		$queryUpdate 				= $this->updateBusinessOperationQuery();
		$IdSubdealer 				= $data['IdSubdealer'];
		$LastBusinessOperationRow 	= $data['LastBusinessOperationRow'];
		
		for($curRow = 1;$curRow<=$LastBusinessOperationRow;$curRow++){
			$BusinessOperation_Type = $data['BusinessOperation_Type_'.$curRow];
			$FirmName 				= $data['FirmName_'.$curRow];
			$City 					= $data['City_'.$curRow];
			$PercentageOfHolding 	= $data['PercentageOfHolding_'.$curRow];
			$BusinessOperation 		= $data['BusinessOperation_'.$curRow];
			$BusinessName 			= $data['BusinessName_'.$curRow];
			$YearlyTurnOver  		= $data['YearlyTurnOver_'.$curRow];
			$Status 				= $data['Status_'.$curRow];
			$RelationWithFirm 		= $data['RelationWithFirm_'.$curRow];
			$Remark 				= $data['Remark_'.$curRow];
			
			$OperationUniqueId 				= $data['OperationUniqueId_'.$curRow];
			if($OperationUniqueId >0){
				$inputArr  = array($BusinessOperation_Type,$FirmName,$City,$PercentageOfHolding,$BusinessOperation,$BusinessName,$YearlyTurnOver,$Status,$RelationWithFirm,$Remark,$IdSubdealer,$OperationUniqueId);
				$sql  		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}else{
				$inputArr  = array($IdSubdealer,$BusinessOperation_Type,$FirmName,$City,$PercentageOfHolding,$BusinessOperation,$BusinessName,$YearlyTurnOver,$Status,$RelationWithFirm,$Remark);
				$sql  		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}			
		}
		return $returnData;			
	}
	
	public function insertBusinessManufacturerDealer($data){
		$queryInsert 	= $this->insertBusinessManufacturerDealerQuery();
		$queryUpdate 	= $this->updateBusinessManufacturerDealerQuery();
		$IdSubdealer 	= $data['IdSubdealer'];
		$LastManufacturerRow 	= $data['LastManufacturerRow'];		
		for($curRow = 1;$curRow<=$LastManufacturerRow;$curRow++){
			$ManuDealerType 		= $data['ManuDealerType_'.$curRow];
			
			$ExplosivesType 		= $data['Type_'.$curRow]; //isset($data['ExplosivesType_'.$curRow])?$data['ExplosivesType_'.$curRow]:'';
			$FirstName 				= $data['FirstName_'.$curRow];
			$MiddleName 			= $data['MiddleName_'.$curRow];
			$LastName 				= $data['LastName_'.$curRow];
			$Relationship 			= $data['Relationship_'.$curRow];
			$RelationshipStatus 	= isset($data['RelationshipStatus_'.$curRow])?$data['RelationshipStatus_'.$curRow]:'';
			$RelationshipStartDate 	= $this->classBusiLogic->convertDate($data['RelationshipStartDate_'.$curRow], True, 1);
			$RelationshipEndDate 	= $this->classBusiLogic->convertDate($data['RelationshipEndDate_'.$curRow], True, 1);
			$BreakReason 			= $data['BreakReason_'.$curRow];
			$Remark 				= $data['SpeacialRemark_'.$curRow];
			$LatestMarketUpdate 	= $data['LatestMarketUpdate_'.$curRow];			
			
			$UniqueManuDealerId 	= $data['UniqueManuDealerId_'.$curRow];			
			if($UniqueManuDealerId>0){
				$inputArr  = array($ManuDealerType,$ExplosivesType,$FirstName,$MiddleName,$LastName,$Relationship,$RelationshipStatus,$RelationshipStartDate,$RelationshipEndDate,$BreakReason,$Remark,$LatestMarketUpdate,$IdSubdealer,$UniqueManuDealerId);
				$sql  		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}else{				
				$inputArr  = array($IdSubdealer,$ManuDealerType,$ExplosivesType,$FirstName,$MiddleName,$LastName,$Relationship,$RelationshipStatus,$RelationshipStartDate,$RelationshipEndDate,$BreakReason,$Remark,$LatestMarketUpdate);
				$sql  		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}
		}
		return $returnData;			
	}
	
	
	
	public function getcurrentProductAvailability($CustomerCd) {
	$searchCnd	= " WHERE B.CustomerCd='$CustomerCd' ";
	$sql 		= " SELECT ProductId,ProductName FROM so_target_item_group ";
	$returnData	= $this->classBusiLogic->execSelectQuery($sql);

	$sql1 = " SELECT C.IdSubdealer,C.Descript AS Subdealer ,IFNULL(E.District,C.City) AS City,";
	for($i=1;$i<=sizeof($returnData);$i++){
		$sql1 	.= "max(case when A.ItemCd  ='".$i."' then CONCAT(A.Priority,'~',A.MaxLotSize,'~',A.SaleStatus) end) as '".$i."',  ";
	}
	$sql1 .=  " IFNULL(D.Descript,C.SubdealerCtgCd) AS Tier FROM  so_subdealer_m_dealer B LEFT OUTER JOIN so_subdealer_m C ON B.IdSubdealer = C.IdSubdealer LEFT OUTER JOIN so_subdealer_item A ON A.IdSubdealer = C.IdSubdealer LEFT OUTER JOIN so_subdealer_ctg_m D ON C.SubdealerCtgCd = D.SubdealerCtgCd  INNER JOIN so_ai_district_m E ON C.City = E.District_Id ".$searchCnd." GROUP BY IdSubdealer ";

	$returnData	= $this->classBusiLogic->execSelectQuery($sql1);
	return $returnData;	
}
	
	//========================end Business Scenario ************************/
	//=======================Start Bank & Other Details==================================//
	
	public function getBankNTaxDetails($IdSubdealer){
		$sql = " SELECT * FROM so_subdealer_bank_tax_m   WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;
	}
	
	public function insertBankNTaxDetailsQuery(){
		$sql = " INSERT INTO  so_subdealer_bank_tax_m (IdSubdealer, SubdealerCd, BankName, BankAcountNo, BankIFSC, BankAddress,  PANType, PANNo, CardHolderName, VATNo, VATNomination, CSTNo, CSTNomination, GSTNo, GSTNomination) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
		return $sql;
	}
	
	public function updateBankNTaxDetailsQuery(){
		$sql = " UPDATE so_subdealer_bank_tax_m SET  BankName = '%s', BankAcountNo = '%s', BankIFSC = '%s', BankAddress = '%s',  PANType = '%s', PANNo = '%s', CardHolderName = '%s', VATNo = '%s', VATNomination = '%s',  CSTNo = '%s', CSTNomination = '%s',  GSTNo = '%s', GSTNomination = '%s' WHERE IdSubdealer = '%s' AND Id ='%s'" ;
		return $sql;
	}
	
	public function insertBankNTaxDetails($postData){
		parse_str($postData, $data);
		$queryInsert 	= $this->insertBankNTaxDetailsQuery();
		$queryUpdate	= $this->updateBankNTaxDetailsQuery();		
		$IdSubdealer 	= $data['IdSubdealer'];
		$BankName 		= ucfirst($data['BankName']);
		$BankAcNo		= $data['BankAcNo'];
		$BankIFSC		= $data['BankIFSC'];
		$BankAddress	= ucfirst($data['BankAddress']);
		//$UploadCheque   = $data['BankCheque'];
		$PAN			= $data['PANCardNO'];
		$TypeOfPAN		= isset($data['TypeOfPAN'])?$data['TypeOfPAN']:'';
		$PANCardHolderName = ucfirst($data['PANCardHolderName']);
		$VATNo			= $data['VATNo'];
		$VATNomination	= ucfirst($data['VATNomination']);
		//$VATCertificate = $data['VATCertificate'];
		$CSTNo          = $data['CSTNo'];
		$CSTNomination	= ucfirst($data['CSTNomination']);
		//$CSTCertificate = $data['CSTCertificate'];
		$GSTNo			= $data['GSTNo'];
		$GSTNomination	= ucfirst($data['GSTNomination']);
		//$GSTCertificate = $data['GSTCertificate'];
		
		$UniqueId 		= $data['UniqueId'];
		$queryCheck = " SELECT COUNT(*) AS Total FROM so_subdealer_bank_tax_m WHERE IdSubdealer ='".$IdSubdealer."'";
		$result 	= $this->classBusiLogic->execSelectQuery($queryCheck);		
		if($result[0]['Total'] != 0){
			$inputArr = array($BankName,$BankAcNo,$BankIFSC,$BankAddress,$TypeOfPAN,$PAN,$PANCardHolderName,$VATNo,$VATNomination,$CSTNo,$CSTNomination, $GSTNo, $GSTNomination, $IdSubdealer,$UniqueId );	
			$sql  		= vsprintf($queryUpdate,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		}else{
			$inputArr = array($IdSubdealer,'',$BankName,$BankAcNo,$BankIFSC,$BankAddress,$TypeOfPAN,$PAN,$PANCardHolderName,$VATNo,$VATNomination,$CSTNo,$CSTNomination,$GSTNo, $GSTNomination);	
			$sql  		= vsprintf($queryInsert,$inputArr);
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		}		
		if($returnData == null){
			return 1;				
		}else{
			return 0;
		}
	}
	
	
	public function uploadBankNTAXFile($IdSubdealer,$fileName,$Name){
		$returnData = '';
		$queryCheck = " SELECT COUNT(*) AS Total FROM so_subdealer_bank_tax_m WHERE IdSubdealer ='".$IdSubdealer."'";
		$result 	= $this->classBusiLogic->execSelectQuery($queryCheck);
		if($result[0]['Total'] <= 0){
			$sql = " INSERT INTO so_subdealer_bank_tax_m (IdSubdealer, SubdealerCd,".$Name.") VALUES ('".$IdSubdealer."','','".$fileName."')";
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			$lastInsertTranNo	= $this->classBusiLogic->getInsertId();
			return $lastInsertTranNo;	
		}else{
			$sql = " UPDATE so_subdealer_bank_tax_m  SET ".$Name." = '".$fileName."' WHERE IdSubdealer = '".$IdSubdealer."'";
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			return $returnData;	
		}
	}

/*******************************************************************/

/****************** Associated Dealer Cd*************************/
	public function getAssociateDealer($IdSubdealer){
		$sql 		= " SELECT * FROM so_subdealer_associate_dealer_m WHERE IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);
		//print_r($returnData);
		return $returnData;			
	}
	
	public function insertAssociateddealerQuery(){
		$sql = " INSERT INTO so_subdealer_associate_dealer_m ( IdSubdealer, CustomerCd, Remark) VALUES ('%s','%s','%s')";
		return $sql;
	}

	public function updateAssociateddealerQuery(){
		$sql = " UPDATE so_subdealer_associate_dealer_m  SET  CustomerCd='%s', Remark = '%s' WHERE IdSubdealer = '%s' AND Id='%s'";
		return $sql;
	}
	public function insertAssociateddealer($postData){
		parse_str($postData, $data);
		$queryInsert 	= $this->insertAssociateddealerQuery();
		$queryUpdate	= $this->updateAssociateddealerQuery();		
		$IdSubdealer 	= $data['IdSubdealer'];
		$lastAssociateDealerRow 	= $data['lastAssociateDealerRow'];		
		for($curRow = 1;$curRow<=$lastAssociateDealerRow;$curRow++){
			$AssociatedRemark 		= $data['AssociatedRemark_'.$curRow];
			$AssociatedDealer 		= $data['AssociatedDealer_'.$curRow]; 		
			$UniqueId 	= $data['UniqueId1_'.$curRow];		
			if($AssociatedDealer ==''){
				continue;
			}
			if($UniqueId>0){
				$inputArr  = array($AssociatedDealer,$AssociatedRemark,$IdSubdealer,$UniqueId);
				$sql  		= vsprintf($queryUpdate,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}else{				
				$inputArr  = array($IdSubdealer,$AssociatedDealer,$AssociatedRemark);
				$sql  		= vsprintf($queryInsert,$inputArr);
				$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
				if ($returnData) return $returnData;
			}
		}
		if($returnData == null){
			return 1;				
		}else{
			return 0;
		}	
	}
	
/*	public function uploadIDFile($IdSubdealer ,$IdNumber,$fileName){
		$sql = " UPDATE so_subdealer_m_partner_details SET UploadIdFile = '".$fileName."' WHERE IdNumber = '".$IdNumber."' AND IdSubdealer = '".$IdSubdealer."'";
		$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
		return $returnData;
	}
*/
	public function uploadIDFile($IdSubdealer ,$IdNumber,$fileName){
		$returnData = '';
		$queryCheck = " SELECT COUNT(*) AS Total FROM so_subdealer_m_partner_details WHERE  IdNumber = '".$IdNumber."' AND  IdSubdealer ='".$IdSubdealer."'";
		$result 	= $this->classBusiLogic->execSelectQuery($queryCheck);
		if($result[0]['Total'] <= 0){
			$sql = " INSERT INTO so_subdealer_m_partner_details (IdSubdealer,UploadIdFile) VALUES ('".$IdSubdealer."','".$fileName."')";
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			$lastInsertTranNo	= $this->classBusiLogic->getInsertId();
			return $lastInsertTranNo;	
		}else{
			$sql = " UPDATE so_subdealer_m_partner_details SET UploadIdFile = '".$fileName."' WHERE IdNumber = '".$IdNumber."' AND IdSubdealer = '".$IdSubdealer."'";
			$returnData	= $this->classBusiLogic->execUpdateQuery($sql);
			return $returnData;
		}
	}
		
	
public function removePDFIDPartner($IdSubdealer, $IdNumber){
	$target_path = '/public_html/B2B/template/subdealer/upload/PartnerId/';
	$filename= $IdSubdealer.'_'.$IdNumber.'.pdf';
	$target_path = $target_path.$filename; 
	//unlink($target_path);
	$sql = "update so_subdealer_m_partner_details SET UploadIdFile = '' where IdNumber ='".$IdNumber."' AND IdSubdealer=".$IdSubdealer;
	$result = $this->classBusiLogic->execUpdateQuery($sql);
	return $result;
}
	
	
	public function removeBankPDFOtherDetails($IdSubdealer, $FileName, $Field, $Folder){
	$target_path = '/public_html/B2B/template/subdealer/upload/'.$Folder.'/';
	$target_path = $target_path.$FileName; 
	//unlink($target_path);
	$sql = "update so_subdealer_bank_tax_m SET ".$Field." = '' where IdSubdealer=".$IdSubdealer;
	$result = $this->classBusiLogic->execUpdateQuery($sql);
	return $result;
}
	


	public function getDistrict($StateCd){
		$sql = " SELECT District_id,District FROM so_ai_district_m WHERE StateCd = '".$StateCd."'";
		$returnData	= $this->classBusiLogic->execSelectQuery($sql);		
		return $returnData;	
	}
	
	public function DealerSubdealerRelationMDisp(){
		$sql		= "SELECT RelationId,Descript  FROM  so_firm_relation_m ";
		$returnData = $this->classBusiLogic->execSelectQuery($sql);
		return $returnData;	
	}




}
