­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ set_charset("utf8"); if (!$link) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } ?> query($codequery) === TRUE) { //return '1'; }else{ //return 'error'; } date_default_timezone_set('Asia/Riyadh'); $date = date("Y-m-d H:i:s"); $codequery = "update xx_file_import set approve_by = '$userid', approve_date = '$date', status = 'Approve', active = 'Y' where id = '$data2' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { //return '1'; }else{ //return 'error'; } } function send_email_api ($from,$fromn,$to,$username,$password,$cc,$bcc,$subject,$body,$attachment){ $ch = curl_init(); curl_setopt($ch, CURLOPT_URL,"http://136.144.45.200/khurram/wwwroot/wwwroot/vocalcom/CRMAPP/newreports/hondaemailapi.php"); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_POSTFIELDS, "from=".$from."&fromn=".$fromn."&to=".$to."&username=".$username."&password=".$password."&cc=".$cc."&bcc=".$bcc."&subject=".$subject."&body=".$body."&attachment=".$attachment); // In real life you should use something like: // curl_setopt($ch, CURLOPT_POSTFIELDS, // http_build_query(array('postvar1' => 'value1'))); // Receive server response ... curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $server_output = curl_exec($ch); //print_r($server_output); curl_close($ch); // Further processing ... //if ($server_output == "OK") { ... } else { ... } } function send_email($to,$cc,$bcc,$subject,$body,$attachment){ $codequery = "select * from xx_email_configuration"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } print_r($id); send_email_api($id['from'],$id['from_name'],$to,$id['username'],$id['password'],$cc,$bcc,$subject,$body,$attachment); } function checkauth(){ if($_SESSION['auth'] == 1){ }else{ $redirect = "https://tajeercrm.core3.agency".$_SERVER['REQUEST_URI']; header('Location: https://tajeercrm.core3.agency/admin/app/login.php?redirect='.$redirect); } } function load_excel_file($file){ require_once('./../plugins/phpoffice/Classes/PHPExcel/IOFactory.php'); try { // load uploaded file $objPHPExcel = PHPExcel_IOFactory::load($file); } catch (Exception $e) { die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage()); } // Specify the excel sheet index $sheet = $objPHPExcel->getSheet(0); $total_rows = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // loop over the rows for ($row = 1; $row <= $total_rows; ++ $row) { for ($col = 0; $col < $highestColumnIndex; ++ $col) { $cell = $sheet->getCellByColumnAndRow($col, $row); $val = $cell->getValue(); $records[$row][$col] = $val; } } return $records; } function load_excel_file_in_db($file){ require_once('./../plugins/phpoffice/Classes/PHPExcel/IOFactory.php'); try { // load uploaded file $objPHPExcel = PHPExcel_IOFactory::load($file); } catch (Exception $e) { die('Error loading file "' . pathinfo($file, PATHINFO_BASENAME). '": ' . $e->getMessage()); } // Specify the excel sheet index $sheet = $objPHPExcel->getSheet(0); $total_rows = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // loop over the rows for ($row = 1; $row <= $total_rows; ++ $row) { for ($col = 0; $col < $highestColumnIndex; ++ $col) { $cell = $sheet->getCellByColumnAndRow($col, $row); $val = $cell->getValue(); $records[$row][$col] = $val; } } //return $records; $codequery = "delete from xx_price_list"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { //return '1'; }else{ //return 'error'; } foreach ($records as $records_v){ date_default_timezone_set('Asia/Riyadh'); $date = date("Y-m-d H:i:s"); $codequery = "insert into xx_price_list (item_code, description_en, description_ar, price_list, on_hand_qty, mad, on_order_qty, eta, upload_file, update_date ) value ('$records_v[0]', '$records_v[1]', '$records_v[2]', '$records_v[3]', '$records_v[4]', '$records_v[5]', '$records_v[6]', '$records_v[7]', '$file', '$date' )"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { //return '1'; }else{ //return 'error'; } } } function import_file_upload($filename){ date_default_timezone_set('Asia/Riyadh'); $date = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "insert into xx_file_import (upload_file, uploaded_by, upload_date, status ) value ('$filename', '$user_id', '$date', 'Initiated' )"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { $last_id = $GLOBALS["link"]->insert_id; return $last_id; }else{ return 'error'; } } function import_file_upload_service($filename){ date_default_timezone_set('Asia/Riyadh'); $date = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "insert into xx_service_file_import (upload_file, uploaded_by, upload_date, status ) value ('$filename', '$user_id', '$date', 'Initiated' )"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { $last_id = $GLOBALS["link"]->insert_id; return $last_id; }else{ return 'error'; } } function update_user($data2){ $userid = $data2['user_id']; $username = $data2['username']; $email_address = $data2['email_address']; $password = $data2['password']; $role_id = $data2['role_id']; $active = $data2['active']; $codequery = "update xx_users set username = '$username', email_address = '$email_address', password = '$password', role_id = '$role_id', active = '$active' where id = '$userid' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function update_account_v($acc_id,$full_name,$fname,$lname,$mobile1,$mobile2,$mobile3,$salary_category_id,$income_type_id,$bank_id,$nationality_id,$gender_id,$work_sector_id){ date_default_timezone_set("Asia/Riyadh"); $mdate = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "update xx_accounts set full_name = '$full_name', first_name = '$fname', last_name = '$lname', mobile1 = '$mobile1', mobile2 = '$mobile2', mobile3 = '$mobile3', salary_category_id = '$salary_category_id', income_type_id = '$income_type_id', bank_id = '$bank_id', nationality_id = '$nationality_id', gender_id = '$gender_id', work_sector_id = '$work_sector_id', modified_by_id = '$user_id', modified_date = '$mdate' where acc_id = '$acc_id' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function update_lead_vehicle_v($lead_id,$model_id,$brand_id){ date_default_timezone_set("Asia/Riyadh"); $mdate = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "update xx_leads set model_id = '$model_id', brand_id = '$brand_id', is_voi_updated = 'Y', modified_date = '$mdate', modified_by_id = '$user_id' where lead_id = '$lead_id' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function update_lead_owner($lead_id,$owner_id){ date_default_timezone_set("Asia/Riyadh"); $mdate = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "update xx_leads set sales_agent_id = '$owner_id', modified_date = '$mdate', modified_by_id = '$user_id' where lead_id in ($lead_id) "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function update_opp($lead_id,$opp_status_id,$opp_stage_id,$note,$close_note_id){ date_default_timezone_set("Asia/Riyadh"); $mdate = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "update xx_leads set opp_status_id = '$opp_status_id', opp_stage_id = '$opp_stage_id', modified_date = '$mdate', modified_by_id = '$user_id', note = '$note', last_note_date = '$mdate', close_note_id = '$close_note_id', close_note_date = '$mdate' where lead_id = '$lead_id' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function update_confirmation_code($lead_id){ date_default_timezone_set("Asia/Riyadh"); $mdate = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "update xx_leads set is_contacted = 'Y', contact_date = '$mdate' where lead_id = '$lead_id' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function insert_activity($lead_id,$note,$opp_stage_id,$opp_status_id,$opp_note_id){ date_default_timezone_set('Asia/Riyadh'); $date = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "insert into xx_lead_notes (lead_id, note, opp_stage_id, opp_status_id, creation_date, created_by_id, opp_note_id ) value ( '$lead_id', '$note', '$opp_stage_id', '$opp_status_id', '$date', '$user_id', '$opp_note_id' )"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { $last_id = $GLOBALS["link"]->insert_id; return $last_id; }else{ return 'error'; } } function unsucessful_contact_attempt($lead_id,$reason_id){ date_default_timezone_set('Asia/Riyadh'); $date = date("Y-m-d H:i:s"); $user_id = $_SESSION['user_id']; $codequery = "insert into xx_contact_attempts (attempt_id, lead_id, reason_id, creation_date, created_by_id ) value ( '$attempt_id', '$lead_id', '$reason_id', '$date', '$user_id' )"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { $last_id = $GLOBALS["link"]->insert_id; return $last_id; }else{ return 'error'; } } function update_email_configuration($data2){ $username = $data2['username']; $email_address = $data2['email_address']; $password = $data2['password']; $protocol = $data2['protocol']; $from_name = $data2['from']; $from = $data2['fromemail']; $port = $data2['port']; $codequery = "update xx_email_configuration set username = '$username', email_address = '$email_address', password = '$password', protocol = '$protocol', from_name = '$from_name', `from` = '$from', port = '$port' where id = '1' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function auth($data1,$data2){ $codequery = "select * from xx_users where email_address = '$data1'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } //print_r($id); //return $id; if($id['password'] == $data2){ $auth = '1'; }else{ $auth = '0'; } $data = array('auth' => $auth, 'role_id' => $id['role_id'], 'username' => $id['username'], 'email_address' => $id['email_address'], 'user_id' => $id['id'], 'branch_code' => $id['branch_code'],'report_source' => $id['report_source']); return $data; } function get_user_detail($data){ $codequery = "select * from xx_users where id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_city_lookup($data){ $codequery = "select * from xx_cities where lookup_value = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_bank_lookup($data){ $codequery = "select * from xx_banks where lookup_value = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_branch_lookup($data){ $codequery = "select * from xx_branches where lookup_value = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_expected_purchase_lookup($data){ $codequery = "select * from xx_expected_purchase_time where lookup_value = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_pref_time_lookup($data){ $codequery = "select * from xx_preferred_call_time where lookup_value = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_gender_lookup($data){ $codequery = "select * from xx_gender where lookup_value = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_random_number($data){ $digits = $data; return rand(pow(10, $digits-1), pow(10, $digits)-1); } function get_total_leads_branch($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads_vu where branch_code = '$data' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_leads_agent($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads where sales_agent_id = '$data' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_leads($data){ $codequery = "select count(lead_id)cnt from xx_leads where date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_leads_motorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_leads_nonmotorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_contacted_leads_branch($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads_vu where branch_code = '$data' and is_contacted = 'Y' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_contacted_leads_agent($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads where sales_agent_id = '$data' and is_contacted = 'Y' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_contacted_leads($data){ $codequery = "select count(lead_id)cnt from xx_leads where is_contacted = 'Y' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_contacted_leads_motorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where is_contacted = 'Y' and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_contacted_leads_nonmotorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where is_contacted = 'Y' and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_pending_leads_branch($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads_vu where branch_code = '$data' and is_contacted is null and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_pending_leads_agent($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads where sales_agent_id = '$data' and is_contacted is null and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_pending_leads($data){ $codequery = "select count(lead_id)cnt from xx_leads where is_contacted is null and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_pending_leads_motorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where is_contacted is null and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_pending_leads_nonmotorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where is_contacted is null and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_lost_leads_branch($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads_vu where branch_code = '$data' and opp_stage_id = '5' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_lost_leads_agent($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads where sales_agent_id = '$data' and opp_stage_id = '5' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_lost_leads($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '5' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_lost_leads_motorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '5' and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_lost_leads_nonmotorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '5' and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_won_leads_branch($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads_vu where branch_code = '$data' and opp_stage_id = '4' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_won_leads_agent($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads where sales_agent_id = '$data' and opp_stage_id = '4' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_won_leads($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '4' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_won_leads_motorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '4' and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_won_leads_nonmotorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '4' and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_nego_leads_branch($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads_vu where branch_code = '$data' and opp_stage_id = '3' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_nego_leads_agent($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads where sales_agent_id = '$data' and opp_stage_id = '3' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_nego_leads($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '3' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_nego_leads_motorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '3' and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_nego_leads_nonmotorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '3' and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_prospect_leads_branch($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads_vu where branch_code = '$data' and opp_stage_id = '1' and is_contacted = 'Y' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_prospect_leads_agent($data,$data2){ $codequery = "select count(lead_id)cnt from xx_leads where sales_agent_id = '$data' and opp_stage_id = '1' and is_contacted = 'Y' and date_format(creation_date,'%M-%Y') = '$data2'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_prospect_leads($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '1' and is_contacted = 'Y' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_prospect_leads_motorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '1' and is_contacted = 'Y' and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_total_prospect_leads_nonmotorsouq($data){ $codequery = "select count(lead_id)cnt from xx_leads where opp_stage_id = '1' and is_contacted = 'Y' and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_leads_aging_by_agent_branch($data,$data2){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 0 and 24 then lead_id end) as '24hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 25 and 48 then lead_id end) as '48hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 49 and 72 then lead_id end) as '72hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 73 and 96 then lead_id end) as '96hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 97 and 120 then lead_id end) as '120hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) > 120 then lead_id end) as '121hrs' from xx_leads_vu where branch_code = '$data' and is_contacted is null and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY sales_agent "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_aging_by_agent_salesagent($data,$data2){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 0 and 24 then lead_id end) as '24hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 25 and 48 then lead_id end) as '48hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 49 and 72 then lead_id end) as '72hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 73 and 96 then lead_id end) as '96hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 97 and 120 then lead_id end) as '120hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) > 120 then lead_id end) as '121hrs' from xx_leads_vu where sales_agent_id = '$data' and is_contacted is null and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY sales_agent "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_aging_by_agent($data){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 0 and 24 then lead_id end) as '24hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 25 and 48 then lead_id end) as '48hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 49 and 72 then lead_id end) as '72hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 73 and 96 then lead_id end) as '96hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 97 and 120 then lead_id end) as '120hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) > 120 then lead_id end) as '121hrs' from xx_leads_vu where is_contacted is null and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_aging_by_agent_motorsouq($data){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 0 and 24 then lead_id end) as '24hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 25 and 48 then lead_id end) as '48hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 49 and 72 then lead_id end) as '72hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 73 and 96 then lead_id end) as '96hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 97 and 120 then lead_id end) as '120hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) > 120 then lead_id end) as '121hrs' from xx_leads_vu where is_contacted is null and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_aging_by_agent_nonmotorsouq($data){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 0 and 24 then lead_id end) as '24hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 25 and 48 then lead_id end) as '48hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 49 and 72 then lead_id end) as '72hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 73 and 96 then lead_id end) as '96hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) between 97 and 120 then lead_id end) as '120hrs', count(case when TIMESTAMPDIFF(HOUR, creation_date, now()) > 120 then lead_id end) as '121hrs' from xx_leads_vu where is_contacted is null and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_stages_by_agent_branch($data,$data2){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when opp_stage_id = '1' then lead_id end) as 'stage1', count(case when opp_stage_id = '2' then lead_id end) as 'stage2', count(case when opp_stage_id = '3' then lead_id end) as 'stage3', count(case when opp_stage_id = '4' then lead_id end) as 'stage4', count(case when opp_stage_id = '5' then lead_id end) as 'stage5' from xx_leads_vu where is_contacted = 'Y' and branch_code = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_stages_by_agent_agent($data,$data2){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when opp_stage_id = '1' then lead_id end) as 'stage1', count(case when opp_stage_id = '2' then lead_id end) as 'stage2', count(case when opp_stage_id = '3' then lead_id end) as 'stage3', count(case when opp_stage_id = '4' then lead_id end) as 'stage4', count(case when opp_stage_id = '5' then lead_id end) as 'stage5' from xx_leads_vu where is_contacted = 'Y' and sales_agent_id = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_stages_by_agent($data){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when opp_stage_id = '1' then lead_id end) as 'stage1', count(case when opp_stage_id = '2' then lead_id end) as 'stage2', count(case when opp_stage_id = '3' then lead_id end) as 'stage3', count(case when opp_stage_id = '4' then lead_id end) as 'stage4', count(case when opp_stage_id = '5' then lead_id end) as 'stage5' from xx_leads_vu where is_contacted = 'Y' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_stages_by_agent_motorsouq($data){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when opp_stage_id = '1' then lead_id end) as 'stage1', count(case when opp_stage_id = '2' then lead_id end) as 'stage2', count(case when opp_stage_id = '3' then lead_id end) as 'stage3', count(case when opp_stage_id = '4' then lead_id end) as 'stage4', count(case when opp_stage_id = '5' then lead_id end) as 'stage5' from xx_leads_vu where is_contacted = 'Y' and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_stages_by_agent_nonmotorsouq($data){ $codequery = "select sales_agent,count(lead_id)cnt, count(case when opp_stage_id = '1' then lead_id end) as 'stage1', count(case when opp_stage_id = '2' then lead_id end) as 'stage2', count(case when opp_stage_id = '3' then lead_id end) as 'stage3', count(case when opp_stage_id = '4' then lead_id end) as 'stage4', count(case when opp_stage_id = '5' then lead_id end) as 'stage5' from xx_leads_vu where is_contacted = 'Y' and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_lostduetouncontact_branch($data,$data2){ $codequery = "select sales_agent,count(lead_id)cnt from xx_leads_vu where opp_status_id = '20' and branch_code = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_lostduetouncontact_agent($data,$data2){ $codequery = "select sales_agent,count(lead_id)cnt from xx_leads_vu where opp_status_id = '20' and sales_agent_id = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_lostduetouncontact($data){ $codequery = "select sales_agent,count(lead_id)cnt from xx_leads_vu where opp_status_id = '20' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_lostduetouncontact_motorsouq($data){ $codequery = "select sales_agent,count(lead_id)cnt from xx_leads_vu where opp_status_id = '20' and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_leads_lostduetouncontact_nonmotorsouq($data){ $codequery = "select sales_agent,count(lead_id)cnt from xx_leads_vu where opp_status_id = '20' and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY sales_agent"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_7_days_leads_branch($data,$data2){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and date_format(now(),'%Y-%m-%d') and branch_code = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_7_days_leads_agent($data,$data2){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and date_format(now(),'%Y-%m-%d') and sales_agent_id = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_7_days_leads($data){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and date_format(now(),'%Y-%m-%d') and date_format(creation_date,'%M-%Y') = '$data' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_7_days_leads_motorsouq($data){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and date_format(now(),'%Y-%m-%d') and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_7_days_leads_nonmotorsouq($data){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and date_format(now(),'%Y-%m-%d') and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_14_days_leads_branch($data,$data2){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 14 DAY),'%Y-%m-%d') and date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and branch_code = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_14_days_leads_agent($data,$data2){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 14 DAY),'%Y-%m-%d') and date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and sales_agent_id = '$data' and date_format(creation_date,'%M-%Y') = '$data2' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_14_days_leads($data){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 14 DAY),'%Y-%m-%d') and date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and date_format(creation_date,'%M-%Y') = '$data' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_14_days_leads_motorsouq($data){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 14 DAY),'%Y-%m-%d') and date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and landing_page_source = 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_14_days_leads_nonmotorsouq($data){ $codequery = "select DATE_FORMAT(creation_date,'%d')dte,count(lead_id)cnt from xx_leads_vu where date_format(creation_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 14 DAY),'%Y-%m-%d') and date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and landing_page_source <> 'MOTORSOUQ' and date_format(creation_date,'%M-%Y') = '$data' GROUP BY DATE_FORMAT(creation_date,'%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_branch_users($data){ $codequery = "select * from xx_users where branch_code = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_branch_users_business($data){ $codequery = "select * from xx_users where business_unit = '4'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_international_users($data){ $codequery = "select * from xx_users where business_unit in('3','2')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_lead_detail($data){ $codequery = "select * from xx_leads where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_leadv_detail($data){ $codequery = "select * from xx_leads_vu where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_leadv_ar_detail($data){ $codequery = "select * from xx_leads_vu_ar where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_account_detail($data){ $codequery = "select * from xx_accounts where acc_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_accountv_detail($data){ $codequery = "select * from xx_accounts_vu where acc_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_accountv_ar_detail($data){ $codequery = "select * from xx_accounts_vu_ar where acc_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_sale_stages($data){ $codequery = "select * from xx_opp_stages where active = 'Y'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_salary_category($data){ $codequery = "select * from xx_salary_cat"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_income_type($data){ $codequery = "select * from xx_income_type"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_banks($data){ $codequery = "select * from xx_banks"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_nationalities($data){ $codequery = "select * from xx_nationality"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_gender($data){ $codequery = "select * from xx_gender"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_work_sector($data){ $codequery = "select * from xx_work_sector"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_brands($data){ $codequery = "select * from xx_brands"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_brands_v($data){ $codequery = "select * from xx_brands where id = '$data' "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_models($data){ $codequery = "select * from xx_models where brand_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_sale_stage_name($data){ $codequery = "select * from xx_opp_stages where id='$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_sale_notes($data){ $codequery = "select * from xx_opp_notes"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_uncontact_reasons($data){ $codequery = "select * from xx_uncontact_reasons"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_contact_attempts($data){ $codequery = "select * from xx_contact_attempts_vu where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_contact_attempts_ar($data){ $codequery = "select * from xx_contact_attempts_vu_ar where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_date_by_id($data){ $codequery = "select * from xx_fl_date where id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_mdate_by_id($data){ $codequery = "select * from xx_fl_mdate where id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_contact_attempts_cnt($data){ $codequery = "select count(distinct(date_format(creation_date,'%Y-%m-%d'))) cnt from xx_contact_attempts_vu where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_opp_activities($data){ $codequery = "select * from xx_activities_vu where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_opp_activities_ar($data){ $codequery = "select * from xx_activities_vu_ar where lead_id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_sale_stages_d($data){ $codequery = "select * from xx_opp_stages where id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_opp_status($data){ $codequery = "select * from xx_opp_status where stage_id = $data"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_current_week_searches($data){ $codequery = "select selected_date,DATE_FORMAT(selected_date,'%d')dte,get_logs_by_date(selected_date) as cnt from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where date_format(selected_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d') and date_format(now(),'%Y-%m-%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_last_week_searches($data){ $codequery = "select selected_date,DATE_FORMAT(selected_date,'%d')dte,get_logs_by_date(selected_date) as cnt from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where date_format(selected_date,'%Y-%m-%d') between date_format(DATE(NOW() - INTERVAL 14 DAY),'%Y-%m-%d') and date_format(DATE(NOW() - INTERVAL 7 DAY),'%Y-%m-%d')"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_vistors_count($data){ if($data == '1'){ $codequery = "SELECT count(id)cnt FROM xx_visitors_logs WHERE date_format(log_date,'%Y-%m-%d') = date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d')"; } if($data == '2'){ $codequery = "SELECT count(id)cnt FROM xx_visitors_logs WHERE YEARWEEK(log_date, 0) = YEARWEEK(date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d'), 0)"; } if($data == '3'){ $codequery = "SELECT count(id)cnt FROM xx_visitors_logs WHERE date_format(log_date,'%Y%m') = date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y%m')"; } if($data == '4'){ $codequery = "SELECT count(id)cnt FROM xx_visitors_logs WHERE date_format(log_date,'%Y') = date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y')"; } //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_top_5_search_partnumber_30days(){ $codequery = "select * from (select partnumber,count(id)cnt from xx_search_logs where product_category <> '' and log_date BETWEEN date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d') - INTERVAL 30 DAY AND date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d') group by partnumber) as z order by z.cnt desc limit 0,5 "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_top_5_search_city_30days(){ $codequery = "select * from (select city,count(id)cnt from xx_search_logs where product_category <> '' and city <> '' and log_date BETWEEN date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d') - INTERVAL 30 DAY AND date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d') group by city) as z order by z.cnt desc limit 0,5 "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_top_5_search_prd_category_30days(){ $codequery = "select * from (select product_category,count(id)cnt from xx_search_logs where product_category <> '' and log_date BETWEEN date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d') - INTERVAL 30 DAY AND date_format(CONVERT_TZ(now(), '+00:00','+10:00'),'%Y-%m-%d') group by product_category) as z order by z.cnt desc limit 0,5 "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_user_details(){ $codequery = "select * from xx_users_vu"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_email_configuration(){ $codequery = "select * from xx_email_configuration"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_file_imports(){ $codequery = "select * from xx_file_import"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_file_imports_d(){ $codequery = "select * from xx_file_import_vu"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_service_file_imports_d(){ $codequery = "select * from xx_service_file_import_vu"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function get_file_imports_detail($data){ $codequery = "select * from xx_file_import_vu where id = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row; } return $id; } function get_menu($data){ $codequery = "select * from xx_access_vu where role_id = '$data' order by page_order asc "; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } return $id; } function register_user($data){ $name = $data['name']; $mbl = $data['mbl']; $empid = $data['empid']; $codequery = "insert into xx_hr_national_day_draw (name, empid, mbl, register_date ) value ('$name', '$empid', '$mbl', now() )"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function is_existing_user($data){ $codequery = "select count(id) cnt from xx_hr_national_day_draw where empid = '$data'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id = $row['cnt']; } return $id; } function get_draw_users($data){ $codequery = "select * from xx_hr_national_day_draw where is_win = 'N'"; //echo $codequery; $result = $GLOBALS["link"]->query($codequery); while($row = $result->fetch_assoc()) { $id[] = $row; } foreach($id as $idv) { $drawusn[] = $idv['name']."|".$idv['empid']; } //$drawusn[] = '123'; //$arr = array('Hello','World!','Beautiful','Day!'); return '"'.implode('","',$drawusn).'"'; //return $id; } function add_winner($name,$empid,$mobile){ $codequery = "insert into xx_winners (name, emp_id, mobile, winning_date ) value ('$name', '$empid', '$mobile', now() )"; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } function remove_winner_from_draw($data){ $codequery = "update xx_hr_national_day_draw set is_win = 'Y' where empid = '$data' "; //echo $codequery; if ($GLOBALS["link"]->query($codequery) === TRUE) { return '1'; }else{ return 'error'; } } ?>