opensourcecode/Hyper-M_Admin_Portal/services/common.php

<?php
include "Connection.php";
$conn = new Connection();

if (isset($_REQUEST["funName"])) {
    $db = $conn->getConnection();
    $fun = $_REQUEST["funName"];
    switch ($fun) {
        case "role":
        case "zone":
        case "state":
        case "city":
        case "dealer":
        case "language":
        case "category":
        case "product":
        case "questiontype":
        case "questiontag":
        case "allquestiontag":
        case "questions":
        case "assessment":
        case "adduserlist":
        case "certificate_level":
        //case "updateproduct":
        //case "deleteproduct":
        case "zonelist":
        case "zmlist":
        case "rmlist":
        case "amlist":
        case "dealerlist":
        case "dselist":
        case "accesspage":
        case "accessmentusertype":
        case "contentrole":
        case "model":
        case "dealer_v2":
        case "trainer":
            $fun();
            break;
        default:
            $conn->message(false, "PAGE NOT FOUND");exit;
            break;
    }
} else {
    $conn->message(false, "PAGE NOT FOUND");exit;
}

// GET ZONE
function zone()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $query_str = "SELECT zone FROM " . DMS_DEALER . " WHERE status='1' AND is_deleted='0' AND zone!='' GROUP BY zone ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET STATE
function state()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            //ZONE FILTER

            if ((isset($_REQUEST["zone"]))) {
                $zone = (isset($_REQUEST["zone"])) ? escape_str($_REQUEST["zone"]) : '';
                if ($zone) {
                    $zone_arr = @explode(',', $zone);
                    $zone_quotes = array_map("quotes_array", $zone_arr);
                    $cond = " AND zone IN (" . @implode(',', $zone_quotes) . ")";
                } else {
                    $cond = " AND zone IN ('')";
                }
            }
            //$cond=($cateid)?" AND t.cateid='".$cateid."'":"";

            $query_str = "SELECT state FROM " . DMS_DEALER . " WHERE status='1' AND is_deleted='0' AND state!='' " . $cond . " GROUP BY state ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET CITY
function city()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            //STATE FILTER
            $cond = '';
            if (isset($_REQUEST["state"])) {
                $state = (isset($_REQUEST["state"])) ? escape_str($_REQUEST["state"]) : '';
                if ($state) {
                    $state_arr = @explode(',', $state);
                    $state_quotes = array_map("quotes_array", $state_arr);
                    $cond .= " AND state IN (" . @implode(',', $state_quotes) . ")";
                } else {
                    $cond .= " AND state IN ('')";
                }
            }

            if ((isset($_REQUEST["zone"]))) {
                $zone = (isset($_REQUEST["zone"])) ? escape_str($_REQUEST["zone"]) : '';
                if ($zone) {
                    $zone_arr = @explode(',', $zone);
                    $zone_quotes = array_map("quotes_array", $zone_arr);
                    $cond .= " AND zone IN (" . @implode(',', $zone_quotes) . ")";
                } else {
                    $cond .= " AND zone IN ('')";
                }
            }

            if (isset($_REQUEST["trainer_id"])) {
                $trainer_id = (isset($_REQUEST["trainer_id"])) ? escape_str($_REQUEST["trainer_id"]) : '';
                if ($trainer_id) {
                    //$state_arr=@explode(',',$state);
                    //$state_quotes = array_map("quotes_array", $state_arr);
                    //$cond=" AND state IN (".@implode(',',$state_quotes).")";
                    $query_str = "SELECT h.zone FROM " . DMS_HOUSER . " as h JOIN " . TABLE_USER . " as u ON h.emp_code=u.emp_code WHERE u.status='1' AND u.is_deleted='0' AND u.userid='" . $trainer_id . "' AND u.role='5' LIMIT 1";
                    $query = $conn->query($query_str);
                    $arr = $query->fetchAll(PDO::FETCH_OBJ);
                    if (count($arr)) {
                        $cond .= " AND zone='" . $arr[0]->zone . "'";
                    } else {
                        $cond .= " AND zone=''";
                    }
                } else {
                    //$cond=" AND state IN ('')";
                }
            }

            $query_str = "SELECT city FROM " . DMS_DEALER . " WHERE status='1' AND is_deleted='0' AND city!='' " . $cond . " GROUP BY city ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET DEALER
function dealer()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $cond = '';
            //ZONE FILTER
            if (isset($_REQUEST["city"])) {
                $city = (isset($_REQUEST["city"])) ? escape_str($_REQUEST["city"]) : '';
                if ($city) {
                    $city_arr = @explode(',', $city);
                    $city_quotes = array_map("quotes_array", $city_arr);
                    $cond .= " AND city IN (" . @implode(',', $city_quotes) . ")";
                } else {
                    $cond .= " AND city IN ('')";
                }
            }
            if (isset($_REQUEST["state"])) {
                $state = (isset($_REQUEST["state"])) ? escape_str($_REQUEST["state"]) : '';
                if ($state) {
                    $state_arr = @explode(',', $state);
                    $state_quotes = array_map("quotes_array", $state_arr);
                    $cond .= " AND state IN (" . @implode(',', $state_quotes) . ")";
                } else {
                    $cond .= " AND state IN ('')";
                }
            }

            if ((isset($_REQUEST["zone"]))) {
                $zone = (isset($_REQUEST["zone"])) ? escape_str($_REQUEST["zone"]) : '';
                if ($zone) {
                    $zone_arr = @explode(',', $zone);
                    $zone_quotes = array_map("quotes_array", $zone_arr);
                    $cond .= " AND zone IN (" . @implode(',', $zone_quotes) . ")";
                } else {
                    $cond .= " AND zone IN ('')";
                }
            }

            $query_str = "SELECT `dealer_code`, `dealer_name`,city,state,zone FROM " . DMS_DEALER . " WHERE status='1' AND is_deleted='0' " . $cond . " GROUP BY dealer_code ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET CATEGORY
function category()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $query_str = "SELECT `cateid`, `catename` FROM  " . TABLE_CATEGORY;
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET LANGUAGE
function language()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $query_str = "SELECT `language_id`, `language_code`, `language_title` FROM  " . TABLE_MASTER_LANGUAGE . " WHERE is_deleted='0'";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// get product
function product()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $cateid = escape_str($_REQUEST["cateid"]);
            $cond = ($cateid) ? " AND t.cateid='" . $cateid . "'" : "";

            $query_str = "SELECT t.`assettopicid`, t.`topicname`,c.catename,t.user_type FROM  " . TABLE_ASSET_TOPIC . " t  LEFT JOIN " . TABLE_CATEGORY . " c ON t.cateid=c.cateid WHERE t.is_deleted='0' " . $cond . " ORDER BY t.position,t.assettopicid ASC ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET QUESTION TYPE
function questiontype()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $type = escape_str($_REQUEST["type"]);
            $cond = '';
            if ($type) {
                switch ($type) {
                    case '1': //Assessment
                        $cond = " AND id IN (1,2)";
                        break;
                    case '2': //Survey
                        $cond = " AND id IN (3,4,5,6)";
                        break;
                }
            }

            $query_str = "SELECT `id`, `questiontype` FROM " . TABLE_QUESTIONTYPE . " WHERE is_deleted=0 " . $cond;
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET QUESTION TAG
function questiontag()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $questiontypeid = escape_str($_REQUEST["questiontypeid"]);
            $cond = ($questiontypeid) ? " AND questiontypeid='" . $questiontypeid . "'" : "";

            if (isset($_REQUEST["certified"]) && $_REQUEST["certified"] == '1') {
                $cond .= " and  cateid>'0'";
            } else {
                $cond .= " and  cateid='0'";
            }
            if (isset($_REQUEST["is_survey"]) && $_REQUEST["is_survey"] == '1') {
                $cond .= " and  questiontypeid IN(3,4,5,6)";
            } else {
                $cond .= " and  questiontypeid IN(1,2)";
            }

            $query_str = "SELECT tag FROM " . TABLE_QUESTION . " WHERE status=1 " . $cond . " GROUP BY tag ORDER BY tag,id ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET QUESTION TAG
function allquestiontag()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $query_str = "SELECT tag FROM " . TABLE_QUESTION . " WHERE status=1 " . $cond . " GROUP BY tag ORDER BY tag,id ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET QUESTION
function questions()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $questiontypeid = escape_str($_REQUEST["questiontypeid"]);
            $cond = ($questiontypeid) ? " AND questiontypeid='" . $questiontypeid . "'" : "";

            if (isset($_REQUEST["certified"]) && $_REQUEST["certified"] == '1') {
                $cond .= " and  cateid>'0'";
            } else {
                $cond .= " and  cateid='0'";
            }
            if (isset($_REQUEST["is_survey"]) && $_REQUEST["is_survey"] == '1') {
                $cond .= " and  questiontypeid IN(3,4,5,6)";
            } else {
                $cond .= " and  questiontypeid IN(1,2)";
            }

            if (isset($_REQUEST["tag"]) && $_REQUEST["tag"] != '') {
                $cond .= " and  tag='" . escape_str($_REQUEST["tag"]) . "'";
            } else {
                $cond .= "";
            }

            $query_str = "SELECT `id`, `questiontypeid`,CASE
    WHEN questiontypeid IN(1,2) THEN '1' WHEN questiontypeid IN(3,4,5,6) THEN '2' END as type,`questiontext` ,tag  FROM " . TABLE_QUESTION . " WHERE status=1 " . $cond . " ORDER BY tag,id ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET ASSESSMENT
function assessment()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $cond = '';
            $atype = escape_str($_REQUEST["atype"]);
            $cond .= ($atype) ? " AND atype='" . $atype . "'" : "";

            $user_type = escape_str($_REQUEST["user_type"]);
            $cond .= ($user_type) ? " AND user_type='" . $user_type . "'" : " AND user_type='0'";

            $query_str = "SELECT `assessmentid`,`atype`, `lvl_id`, `assessmentname`,user_type  FROM " . TABLE_ASSESSMENT . " WHERE status=1 AND assettypeid=1 " . $cond;
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET USER LIST
function adduserlist()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';
        if (isset($_SESSION["userid"])) {
            $assessmentid = escape_str($_REQUEST["assessmentid"]);
            if ($assessmentid) {
                $query_assessmentstr = "SELECT  *  FROM " . TABLE_ASSESSMENT . " WHERE is_deleted=0 ";
                $conn->getQueryValue($query_assessmentstr);
            }

            $query_str = "SELECT  `userid`, `smpin`, `fname`, `lname`,`role`  FROM " . TABLE_USER . " WHERE is_deleted=0 " . $cond;
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET ROLE
function role()
{

    global $conn;
    try {
        // $_SESSION["userid"]='1'; echo "exit"; exit;z
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';
        if (isset($_SESSION["userid"])) {
            $type = escape_str($_REQUEST["type"]);
            $cond = '';
            if ($type == 'dse') {
                $cond = " AND id>7 AND id<14";
            }
            $user_type = escape_str($_REQUEST["user_type"]);
            if ($user_type != '') {
                $cond .= ($user_type == 'normal') ? " AND id>7 AND id<14 " : " AND id=15 ";

            }

            $cond .= (isset($_SESSION["role"]) && $_SESSION["role"] != '1') ? " AND id>2" : " AND id>1";

            $query_str = "SELECT `id`, `rolename`  FROM " . TABLE_ROLE . " WHERE status=1 " . $cond;

            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            if (count($arr) > 0) {
                foreach ($arr as $key => $res) {
                    $file_url = '';
                    switch ($res->id) {
                        case '1':
                        case '2':
                            $file_url = SITE_URL . "hypermupload/sample/sample_admin.xlsx";
                            break;
                        case '3':
                        case '4':
                        case '6':
                        case '7':
                            $file_url = SITE_URL . "hypermupload/sample/sample_manager.xlsx";
                            break;
                        case '5':
                            $file_url = SITE_URL . "hypermupload/sample/sample_trainer.xlsx";
                            break;
                        case '8':
                        case '9':
                        case '10':
                        case '11':
                        case '12':
                        case '13':
                        case '15':
                            $file_url = SITE_URL . "hypermupload/sample/sample_dse.xlsx";
                            break;
                        case '14':
                            $file_url = SITE_URL . "hypermupload/sample/sample_dealer.xlsx";
                            break;
                        case '16':
                            $file_url = SITE_URL . "hypermupload/sample/sample_experience.xlsx";
                            break;
                    }
                    $arr[$key]->sample_file = $file_url;
                }

            }

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET CERTIFICATE LEVEL
function certificate_level()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $type = escape_str($_REQUEST["type"]);

            $query_str = "SELECT  `lvl_id`, `lvl_name` FROM " . TABLE_TRAINING_LEVEL . "   ORDER BY  lvl_id ";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            if ($type == '1') {
                $nobj = new stdClass();
                $nobj->lvl_id = '0';
                $nobj->lvl_name = 'Non Cerificate';
                $data[] = $nobj;
                foreach ($arr as $v) {
                    $data[] = $v;
                }
            } else {
                $data = $arr;
            }

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $data);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET ZONELIST
function zonelist()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';
        //$_SESSION["userid"] = 1;
        if (isset($_SESSION["userid"])) {

            $query_str = "SELECT zone as name  FROM " . DMS_DEALER . " WHERE zm_name!='' AND status='1' AND is_deleted='0' " . $query_cond . "  GROUP BY zone";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}
// GET ZMLIST
function zmlist()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $zone = escape_str($_REQUEST["zone"]);
            $query_cond = ($zone != '') ? " AND zone='" . $zone . "'" : '';

            $query_str = "SELECT zm_name as name,zm_email as email,zone FROM " . DMS_DEALER . " WHERE zm_name!='' AND status='1' AND is_deleted='0' " . $query_cond . "  GROUP BY zm_email ORDER BY zm_name ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET RMLIST
function rmlist()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $zone = isset($_REQUEST["zone"]) ? escape_str($_REQUEST["zone"]) : '';
            if ($zone) {
                $query_cond .= " AND zone='" . $zone . "'";
            }

            $zonemanager = isset($_REQUEST["zm_email"]) ? escape_str($_REQUEST["zm_email"]) : '';
            if ($zonemanager) {
                $query_cond .= " AND zm_email='" . $zonemanager . "'";
            }

            $query_str = "SELECT rm_name as name,rm_email as email FROM " . DMS_DEALER . " WHERE rm_name!=''  AND status='1' AND is_deleted='0' " . $query_cond . "  GROUP BY rm_email ORDER BY rm_name ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET AMLIST
function amlist()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $zone = isset($_REQUEST["zone"]) ? escape_str($_REQUEST["zone"]) : '';
            if ($zone) {
                $query_cond .= " AND zone='" . $zone . "'";
            }

            $zonemanager = isset($_REQUEST["zm_email"]) ? escape_str($_REQUEST["zm_email"]) : '';
            if ($zonemanager) {
                $query_cond .= " AND zm_email='" . $zonemanager . "'";
            }
            $regionmanager = isset($_REQUEST["rm_email"]) ? escape_str($_REQUEST["rm_email"]) : '';
            if ($regionmanager) {
                $query_cond .= " AND rm_email='" . $regionmanager . "'";
            }

            $zone = isset($_REQUEST["zone"]) ? escape_str($_REQUEST["zone"]) : '';
            if ($zone) {
                $query_cond .= " AND zone='" . $zone . "'";
            }

            $query_str = "SELECT am_name as name,am_email as email FROM " . DMS_DEALER . " WHERE am_name!=''  AND status='1' AND is_deleted='0' " . $query_cond . "  GROUP BY am_email ORDER BY am_name ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET DEALER LIST
function dealerlist()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $zone = isset($_REQUEST["zone"]) ? escape_str($_REQUEST["zone"]) : '';
            if ($zone) {
                $query_cond .= " AND zone='" . $zone . "'";
            }

            $zonemanager = isset($_REQUEST["zm_email"]) ? escape_str($_REQUEST["zm_email"]) : '';
            if ($zonemanager) {
                $query_cond .= " AND zm_email='" . $zonemanager . "'";
            }
            $regionmanager = isset($_REQUEST["rm_email"]) ? escape_str($_REQUEST["rm_email"]) : '';
            if ($regionmanager) {
                $query_cond .= " AND rm_email='" . $regionmanager . "'";
            }
            $areamanager = isset($_REQUEST["am_email"]) ? escape_str($_REQUEST["am_email"]) : '';
            if ($areamanager != '') {
                $query_cond .= " AND am_email='" . $areamanager . "'";
            }

            $query_str = "SELECT dealer_name as name,dealer_code  FROM " . DMS_DEALER . " WHERE status='1' AND is_deleted='0' " . $query_cond . "  ORDER BY dealer_name ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET DSE LIST
function dselist()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $dealer_code = isset($_REQUEST["dealer_code"]) ? escape_str($_REQUEST["dealer_code"]) : '';
            if ($dealer_code != '') {
                $query_cond .= " AND dealer_code='" . $dealer_code . "'";
            }

            $regtype = (escape_str($_REQUEST["regtype"]) == '1') ? "1" : "0";
            $query_cond .= ($regtype == 1) ? "  AND  is_current=1 " : "";
            $segment = escape_str($_REQUEST["segment"]);
            $query_cond .= ($segment == 0) ? "" : " AND  segment='" . $segment . "'";

            $query_str = "SELECT CONCAT(fname,lname) as name,smpin FROM " . DMS_EMPLOYEE . " e WHERE status='1'  " . $query_cond . "  ORDER BY fname ASC,lname ASC";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET accesspage
function accesspage()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $arr = array("Dashboard", "Learning", "Assessment", "Feedback");

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

function accessmentusertype()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';
        if (isset($_SESSION["userid"])) {

            $arr[] = array("id" => '0', 'title' => 'Age Group 7-10');
            $arr[] = array("id" => '1', 'title' => 'Age Group 10-14');
            $arr[] = array("id" => '3', 'title' => 'Age Group 14-18');
            $arr[] = array("id" => '4', 'title' => 'Parents/Guardians');
            $arr[] = array("id" => '5', 'title' => 'Others');

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET ROLE
function contentrole()
{

    global $conn;
    try {
        // $_SESSION["userid"]='1'; echo "exit"; exit;z
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';
        if (isset($_SESSION["userid"])) {

            $cond .= ' AND id>2';
            $query_str = "SELECT `id`, `rolename`  FROM " . TABLE_ROLE . " WHERE status=1 AND id NOT IN(3,14,16) " . $cond;
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET ROLE
function model()
{

    global $conn;
    try {
        // $_SESSION["userid"]='1'; echo "exit"; exit;
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';
        if (isset($_SESSION["userid"])) {

            //$cond.=' AND id>2';
            $query_str = "SELECT *  FROM " . TABLE_COMPARE_MODEL . " WHERE is_deleted=0 and is_other=0";
            $query = $conn->query($query_str);
            $arr = $query->fetchAll(PDO::FETCH_OBJ);

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

// GET DEALER
function dealer_v2()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {
            $cond = '';
            //ZONE FILTER
            if (isset($_REQUEST["city"])) {
                $city = (isset($_REQUEST["city"])) ? escape_str($_REQUEST["city"]) : '';
                if ($city) {
                    $city_arr = @explode(',', $city);
                    $city_quotes = array_map("quotes_array", $city_arr);
                    $cond .= " AND city IN (" . @implode(',', $city_quotes) . ")";
                } else {
                    $cond .= " AND city IN ('')";
                }
            }
            if (isset($_REQUEST["state"])) {
                $state = (isset($_REQUEST["state"])) ? escape_str($_REQUEST["state"]) : '';
                if ($state) {
                    $state_arr = @explode(',', $state);
                    $state_quotes = array_map("quotes_array", $state_arr);
                    $cond .= " AND state IN (" . @implode(',', $state_quotes) . ")";
                } else {
                    $cond .= " AND state IN ('')";
                }
            }

            if ((isset($_REQUEST["zone"]))) {
                $zone = (isset($_REQUEST["zone"])) ? escape_str($_REQUEST["zone"]) : '';
                if ($zone) {
                    $zone_arr = @explode(',', $zone);
                    $zone_quotes = array_map("quotes_array", $zone_arr);
                    $cond .= " AND zone IN (" . @implode(',', $zone_quotes) . ")";
                } else {
                    $cond .= " AND zone IN ('')";
                }
            }
            $arr = array();
            $query_str = "SELECT `dealer_code`, `dealer_name`,city,state,zone FROM " . DMS_DEALER . " WHERE status='1' AND is_deleted='0' " . $cond . " GROUP BY dealer_code ";
            $query = $conn->query($query_str);
            $dealerarr = $query->fetchAll(PDO::FETCH_OBJ);

            if (count($dealerarr) > 0) {
                $ret = new stdClass();
                $ret->dealer = $dealerarr;
                $ret->city = (isset($_REQUEST["city"])) ? $_REQUEST["city"] : '';
                $ret->zone = (isset($_REQUEST["zone"])) ? $_REQUEST["zone"] : '';
                $ret->state = (isset($_REQUEST["state"])) ? $_REQUEST["state"] : '';
                $arr[] = $ret;
            }
            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}

function trainer()
{
    global $conn;
    try {
        $lang = (isset($_REQUEST["lang"])) ? $_REQUEST["lang"] : 'en_us';

        if (isset($_SESSION["userid"])) {

            $conn->message(true, $conn->getMessage($lang, 'common', 'success1'), $arr);
        } else {
            $conn->message(false, $conn->getMessage($lang, 'common', 'error1'));
        }
    } catch (PDOException $e) {
        $conn->message(false, $conn->getMessage($lang, 'common', 'error4'));
    }
}