<?php
// User: keresearch_KENI055Sales

// Database: keresearch_KENI055Sales

//wrw2~VsH#FXN


$db_name = 'keresearch_KENI055Sales';

$conn = mysqli_connect("localhost","keresearch_KENI055Sales","wrw2~VsH#FXN");

if(!$conn){
    die('Could not connect:'.mysqli_error());
}
mysqli_select_db($conn,$db_name) or die("database not connected");
 
 
 
 
 
 
 
 
 
$user_table="sales_user";

// if($conn->connect_error)
// {
//   // die("Connection failed". $conn->connect_error);
// }
// mysqli_select_db($conn,$db_name) or die("database not connected");

// $db_name = 'demodash';

// $conn = mysqli_connect("localhost","keuser","kn0wl3dg3B@NK");

// if(!$conn){
//     die('Couls not connect:'.mysqli_error());
// }
// mysqli_select_db($conn,$db_name) or die("database not connected");
// define('DB_SERVER', 'localhost');
// define('DB_USERNAME', 'surveyli_user3');
// define('DB_PASSWORD', 'sawtooth123');

// mysql_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD);
// mysql_select_db('surveyli_nielsen3');
// $dbhost = "localhost";
// $dbuser = "root";
// $dbpass = "";
// $dbname = "avi";

// $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

// if($conn->connect_error)
// {
//     die("Connection failed". $conn->connect_error);
// }
//=====================================connection closed=========================================
// 
// $tabs['Snapshot'] = array("snaptabs"=>"1");

$user_log="user_log";

$time = array("Q2'22"=>"1","Q3'22"=>"2","Q4'22"=>"3","Q1'23"=>"4","Q2'23"=>"5","Q3'23"=>"6","Q4'23"=>"7","Q1'24"=>"8","Q2'24"=>"9","Q3'24"=>"10","Q4'24"=>"11","Q1'25"=>"12","Q2'25"=>"13","Q3'25"=>"14","Q4'25"=>"15","Q1'26"=>"16");
// $tabs = array("Overall Performance","Factors","Factors Deep Dive","HEART","Dealer-wise Ranking","Top Trending Topics","Top Ratings","Benchmark","Customer Concerns","Action Planning","Recordings","Key Performance Indicators");
 $tabs2 = array("Digitization","Facility","Sales Consultant","Test Drive","Deal","Paperwork","Delivery");
 $tabs3 = array("Digitization","Facility Diagnostics","Sales Consultant","Deal Diagnostics","Paperwork Diagnostics","Delivery Process Diagnostics");

$zone_arrF = array("North","South","East","West","Central");

$tabs = array("Overall Performance","Factors","Digitization","Facility","Sales Consultant","Deal","Paperwork","Delivery","HEART","Dealer-wise Ranking","Top Trending Topics","Top Ratings","Benchmark","Customer Concerns","Action Planning","Recordings","User Log","Action Plan Log","Key Performance Indicators");

$overallsubtabs = array("Preference","Recommendation");
$circle = array("Circle 1"=>"Circle 1","Circle 2"=>"Circle 2","Circle 3"=>"Circle 3","Circle 4"=>"Circle 4");
$month = array("May"=>"1","June"=>"2");
$factor_arr = array("Digitization","Facility","Salesperson","Deal and Paperword","Vehical Delivery","HEART");
$attr_arr = array("Vehical conditions","Test drive experience","Ease of navigation on website","Anticipate","Expression","Sales person expertise and knowledge","Effectiveness of salesperson communication","Empathize","Respect","Transparency of features and benefits","Cleanliness of facility","Delivery ceremony","Transparency in deal and paperwork","Online inventory on websites","Explanation of mannual and controls","Delivery time","Design and appearance of websites","Use of technology by salesperson","Explanation of price breakup","Display of vehicles in showroom","Host");
$zonal_arr = array("West","North","Overall","South","East","Cental");
$regional_arr = array("North","South","West","Cental","East","Overall");
$sop_arr = array("Salesperson met in reasonable time","Staff marketing social distance and wearing mask","Promised features delivered","Updates on Deliver Status","Introduced to service","Tips on Fuel and AC","Deliver within 60 mins","Followup call","Digital devices in store","Car sanitized before test drive");
$sales_yesN = array("a.Shared brochure/price list etc with the customer","b.Informed about monthly consumer schemes","c.Explained benefits of buying VAS","d.Gave comprehensive explanation of car features/benefits of Vehicle","e.Explained about Hyundai’s edge over its competition","f.Asked respondent to sit on driver's seat while giving demo","g.Escorted you and thanked for visiting");
$sales_noN = array("a.Did not meet within reasonable time on customer arrival at dealership","b.Did not use any digital devices to introduce customer to the vehicle","c.Did not ask question to determine model, features and options important to customer");
$dealN = array("a.% respondents say they got difficulty in getting cost of the vehicle","b.% respondents say that Sales Consultant changed the promised price later","c.% respondents say that Sales Consultant informed them about the price rise during negotiation stage","d.% respondents say that the Sales Consultant pressurized them for vehicle purchase");
$paperwork_timeN = array("a.More than My Expectations","b.Same as My Expectations","c.Less than My Expectations");
$paperwork_processN = array("a.Explained with fully clarity","b.Explained somewhat clearly","c.Did not explain clearly at all");
$paperwork_attributeN = array("a.% respondents say that Sales Consultant pressurized them for purchase of additional products","b.% respondents say that staff at dealership did addition of non-agreed products","c.% respondents say that they have to Follow-up/Return at dealership for Paperwork Problem");
$delivery_conditionN = array("a.Vehicle was delivered with no issues","b.Vehicle was not clean","c.Vehicle was not fitted","d.Vehicle had dents/scratches");
$delivery_qualityN = array("a.Explained in Detail","b.Explained Somewhat","c.Poor Explanation");
$delivery_timeN = array("a.Time taken was faster than what i expected","b.Time taken was same as what i expected","c.Took longer than i expected");
$delivery_attrN = array("% respondents say dealership Agree for Home Delivery of vehicle when requested","% respondents say sales consultant Informed them of delivery status","% respondents say vehicle was delivered with all promised features","% respondents say the vehicle was delivery with a ceremony","% respondents got follow-up call after Delivery");


$custom_arr = array("Consultant Good Attention","Expain All Details","Vehicle delivered on Time","Professional Behaviour","Good Quality Service","Satiesfied Showroom Experience","Good Responde from Dealer","Quality of Amenities","Followup was good","Vehicle not delivered on time","Maintain Social Distance","No Respone from Dealer","Maintained Social Distance","No Respone from Dealer");




$concern=array("001"=>"Waiting time should be reduce","002"=>"There should be more showrooms","003"=>"Service should be good","004"=>"Response should be good","005"=>"Bad experience","006"=>"Speed should be good","007"=>"Dealership should not change the number","008"=>"Should deliver what they promise","009"=>"Aftersales call should come for feedback","010"=>"Service should be fast","011"=>"Appearance should be good","012"=>"Should explained well","013"=>"Doesn’t provide accessories","014"=>"Ground clearance should be good","015"=>"Quality of accessories should be good","016"=>"Delivery should be fast","017"=>"Service centre should be good","018"=>"Management should be good","019"=>"Automatic Vehicle should be available for test drive","020"=>"Price should be good","021"=>"Staff behaviour should be good","022"=>"Charges should be low","023"=>"Insurance policy should be better","024"=>"Body length should be good","025"=>"Cameras should be available","026"=>"AC should be available","027"=>"Mileage is not good","028"=>"Improve their built quality","029"=>"Should be more spacious","030"=>"Product can be improve","031"=>"Safety features can be improve","032"=>"Airbags should be there","033"=>"They should check car properly","034"=>"Should be available for test drive","035"=>"Other brands are better","036"=>"Should give more features","037"=>"Should be clean before delivery","038"=>"Facility should be good","039"=>"Staff should be helpful","040"=>"They should be well trained","041"=>"They impose more taxes","042"=>"Way of talking should be good","043"=>"Location should be good","044"=>"Should  provide RC on time","045"=>"Discount should be available","046"=>"Document process should be fast","047"=>"Should give updates properly","048"=>"Suspension should be good","049"=>"Water should be available","050"=>"Plugs are damaged","051"=>"Should provide cover","052"=>"Should have navigation system","053"=>"EMI payment option should be provided","054"=>"Features should be good","055"=>"Should provide all information","056"=>"Front door should be good","057"=>"Should improve sales process","058"=>"Should have wipers","059"=>"Horn is too loud","060"=>"Restricted some of the model","061"=>"Should give diamond cut alloys","062"=>"Should provide extra benefits","063"=>"Sales is too low","064"=>"Issue of marking in the CNG cylinder","065"=>"Seats should be good","066"=>"Not happy with number plate","067"=>"Interior is not good","068"=>"Dealer does not change my number","069"=>"Dealer does not give reminders","070"=>"Headlight should be good","071"=>"Power should be good","072"=>"There should have transparency","073"=>"Brakes should be good","074"=>"Gears should be good","075"=>"Speedometer is different","076"=>"They should provide a contact number to their customer","077"=>"Staff should be more","078"=>"Showroom should be big","079"=>"Should provide my gift","080"=>"Should resolved issue","081"=>"Diesel model cluster meter is normal","082"=>"Colour should be available in our choice","083"=>"Facing issue with AC","084"=>"Pickup should be good","085"=>"Lighting system should be updated","086"=>"Showroom should be good","087"=>"Need more improvements","088"=>"Should have 7 seater cars","089"=>"Parking area need to repair","090"=>"Features of the car should be mentioned","091"=>"Sales person should be knowledgeable","092"=>"Engine performance should be good","093"=>"Should make brand good","094"=>"Locking system is manual","095"=>"Spare parts rate should reduce","096"=>"Spare parts should be available at the service centre","097"=>"Staff should be good","098"=>"They should give attention to customers","099"=>"Paper work should be better","100"=>"Tyres quality should be good","101"=>"Rear look should be good","102"=>"Car should be more stylish","103"=>"Roof quality should be good","104"=>"Car weight is lighter than expected","105"=>"Dealership was forcing me to buy accessories","106"=>"Only 3 years engine warranty","107"=>"There are foldable mirrors in other brands" ,"108"=>"There is noise coming from car","109"=>"Didn't provide the Fastag","110"=>"Repair work should be done properly","111"=>"There is vibration in car","112"=>"There was a leakage of oil","113"=>"Showroom is not air conditioned","114"=>"Steering is very close to the driver's seat","115"=>"The waiting area should be improved","116"=>"Wireless charger is not compatible with every phone","117"=>"There are issues with the rear glass","118"=>"Should be comfortable","119"=>"Problem with the music system","120"=>"Lot of rush in the showroom","121"=>"They did not return my smart card","122"=>"Should decorate the car properly","123"=>"They didn't provide an SD card in the new model","124"=>"Fog light should be there","125"=>"Lift should be  available at their workshop","126"=>"They should apply anti-dust paint on the car","127"=>"They should offer tea and coffee","128"=>"They should improve their marketing strategies","129"=>"They should not involve any third party agency","130"=>"They should provide car pick up and drop facility","131"=>"Should provide decent boot space");
$concern2 = array("001"=>"Should take a proper follow up","002"=>"Should provide all information","003"=>"Behaviour of staff should be good","004"=>"Car should check properly","005"=>"Stock should be available","006"=>"Loan facility should be available","007"=>"Price should be less","008"=>"Communication is too bad/communication should be good","009"=>"Issues should resolved","010"=>"Staff should be knowledgeable","011"=>"They did not do the cake cutting ceremony","012"=>"There should have transparency","013"=>"Doesn’t provide accessories","014"=>"Should explained well","015"=>"Service should be good","016"=>"Service should be fast","017"=>"AC should be working","018"=>"Hospitality should be good","019"=>"They should offer tea and coffee","020"=>"Discount should be available","021"=>"Car should not have problems","022"=>"Rear camera should be good","023"=>"Delivery should be fast","024"=>"Number plate should be available","025"=>"Dealership is far from my place","026"=>"Parking should be good","027"=>"Presentation should be good","028"=>"Interior should be good","029"=>"Mileage should be good","030"=>"Billing process should be good","031"=>"Extended warranty should be good","032"=>"Should not take extra charges","033"=>"They were forcing me to buy accessories","034"=>"Quality of accessories should be good","035"=>"Work should be good","036"=>"Space should be good","037"=>"They should be well trained","038"=>"Exchange program should be good","039"=>"Process should be good","040"=>"Noise coming from car","041"=>"Waiting time should be reduce","042"=>"Improve their built quality","043"=>"Pickup should be good","044"=>"Rear door should be good","045"=>"Tyers quality should be good","046"=>"Should deliver what they promise","047"=>"Dealer does not give reminders","048"=>"They should reply when call","049"=>"Should give good response","050"=>"Washing should be proper","051"=>"Extra key should be provided","052"=>"Test drive should be provided","053"=>"Box of paper tissues should be provided","054"=>"Management should be  good","055"=>"Bad experience","056"=>"Customer satisfaction should be their","057"=>"Seats are not comfortable","058"=>"Should provide my gift","059"=>"Spare parts should be available","060"=>"Should improve their marketing process ","061"=>"Should change their policy.","062"=>"Staff should be good","063"=>"Sitting area should be improve","064"=>"Colour should be provided","065"=>"Documentation should be fast","066"=>"Should give attention to customer","067"=>"Should provide good facilities","068"=>"Should provide cover for car","069"=>"Technology should be improve","070"=>"Features should be good","071"=>"Safety features should be good");
$column = array("Q6a_codes_1","Q6a_codes_2","Q6a_codes_3","Q6a_codes_4","Q6a_codes_5","Q6a_codes_6","Q6a_codes_7");
$column2 = array("Q9a_codes_1","Q9a_codes_2","Q9a_codes_3","Q9a_codes_4","Q9a_codes_5","Q9a_codes_6");

function query_marge($time,$columnS){
$columnS = implode(",", $columnS);
$columnS = explode(",", $columnS);
$columnS = array_unique($columnS);

	$nq="";
	foreach($time as $key => $value){
	  if($key==0){
	  $nq=	$nq." select";
	  }
	  else{
	  $nq=	$nq." UNION select";
	  }
  	foreach($columnS as $key2 => $value2){
  	  if(trim($value2)!=""){
  	  if($key2==0){
	 	$nq=	$nq." ".$value2; 
  	  }
  	  else{
	 	$nq=	$nq.",".$value2; 
  	  }
	  }
  	}
	  $nq=	$nq." from Q".$value;
	}
	return $nq;
}


//$city = array("ABOHAR","ABU ROAD","AGARTALA","AGRA","AHMEDABAD","AJMER","ALIGARH","ALLAHABAD","ALWAR","AMBALA","AMBALA CITY","AMBIKAPUR","AMRITSAR","ANAND","ANANTNAG","ANGUL","AZAMGARH","BALASORE","BANGALORE","BANKURA","BANSWARA","BARASAT","BARDOLI","BAREILLY","BARODA","BATALA","BATHINDA","BEED","BEGUSARAI","BELGAUM","BELLARI","BHAGALPUR","BHARATPUR","BHARAUCH","BHAVNAGAR","BHILAI","BHILWARA","BHIMAVARAM","BHIWADI","BHIWANI","BHOPAL","BHUBANESHWAR","BHUBANESWAR","BHUJ","BIJAPUR","BIJNORE","BILASPUR","BOISAR","BOKAGHAT","BOKARO","BULANDSHAHR","CHANDIGARH","CHENNAI","CHHINDWARA","CHIKMAGALUR","CHITTOOR","CIDCO ROAD", NANDED","COCHIN","CUTTACK","DAHOD","DALTONGANJ","DEHRADUN","DELHI","DHANBAD","DIBRUGARH","DURGAPUR","ETAWAH","FAIZABAD","FARIDABAD","FARIDKOT","GANDHIDHAM","GANDHINAGAR","GANGTOK","GHAZIABAD","GOA","GORAKHPUR","GREATER NOIDA","GULBARGA","GURDASPUR","GURGAON","GUWAHATI","GWALIOR","HAJIPUR","HALDIA","HALDWANI","HAMIRPUR","HARIDWAR","HASSAN","HAZARIBAGH","HIMMATNAGAR","HYDERABAD","IMPHAL","INDORE","ITANAGAR","JABALPUR","JAGDALPUR","JAIPUR","JALLANDHAR","JAMMU","JAMNAGAR","JEYPORE","JHARSUGUDA","JODHPUR","KAITHAL","KANNUR","KANPUR","KARNAL","KHANNA","KORBA","LUCKNOW","LUDHIANA","MADURAI","MAHESANA","MALDA","MALEGAON","MANDI","MANGALORE","MEERUT","MOHALI","MUMBAI","MUZAFFARNAGAR","NADIAD","NAGAON","NAGPUR,"NARNAUL","NASIK","NAWANSHAHAR","NEW DELHI","NIRMAL","NIZAMABAD","NOIDA","NORTH LAKHIMPUR","ONGOLE","PALAKKAD","PALANPUR","PANCHKULA","PANIPAT","PATHANKOT","PATIALA","PATNA","RAIGARH","RAIPUR","RAJKOT","REWARI","ROPAR","ROURKELA","SAMBALPUR","SANGRUR","SHAHJAHANPUR","SILIGURI","SIVSAGAR","SOHNA","SOLAPUR","SONEBHADRA","SONIPAT","SRINAGAR","SULTANPUR","SURAT","TARN TARAN","TEZPUR","THANE","TINSUKIA","TUTICORIN","UDHAMPUR","UDUPI","UJJAIN","VADODARA","VAPI","VARANASI","ZIRAKPUR");
?>


