<?php

/* =======================================================\
  |                        FrontHrm                        |
  |--------------------------------------------------------|
  |   Creator: Phương                                      |
  |   Date :   09-Jul-2017                                  |
  |   Description: Frontaccounting Payroll & Hrm Module    |
  |   Free software under GNU GPL                          |
  |                                                        |
  \======================================================= */

function db_has_members() {
    return check_empty_result("SELECT COUNT(*) FROM " . TB_PREF . "person_per");
}

//--------------------------------------------------------------------------

function write_member(Member $member) {
    if (!$member->per_ID) {
        $sql = "INSERT INTO " . TB_PREF . "person_per (
`per_Title`,`per_FirstName`,`per_MiddleName`,`per_LastName`,`per_Address1`,`per_Zip`,`per_HomePhone`,
`per_CellPhone`,`per_Email`,`per_BirthMonth`,`per_BirthDay`,`per_BirthYear`,`per_MembershipDate`,`per_Gender`,
`per_DateLastEdited`,`per_DateEntered`,`per_EnteredBy`,`per_EditedBy`,`per_Status`) VALUES ("
                . db_escape($member->per_Title) . ", "
                . db_escape($member->per_FirstName) . ", "
                . db_escape($member->per_MiddleName) . ", "
                . db_escape($member->per_LastName) . ", "
                . db_escape($member->per_Address1) . ", "
                . db_escape($member->per_Zip) . ", "
                . db_escape($member->per_HomePhone) . ", "
                . db_escape($member->per_CellPhone) . ", "
                . db_escape($member->per_Email) . ", "
                . db_escape($member->per_BirthMonth) . ", "
                . db_escape($member->per_BirthDay) . ", "
                . db_escape($member->per_BirthYear) . ", '"
                . date2sql($member->per_MembershipDate) . "', "
                . db_escape($member->per_Gender) . ", '"
                . date2sql($member->per_DateLastEdited) . "', '"
                . date2sql($member->per_DateEntered) . "', "
                . db_escape($member->per_EnteredBy) . ", "
                . db_escape($member->per_EditedBy) . ", "
                . db_escape($member->per_Status)
                . ")";
    } else {
        $sql = "UPDATE " . TB_PREF . "person_per SET 
                `per_Title` = " . db_escape($member->per_Title) . ",
                `per_FirstName` = " . db_escape($member->per_FirstName) . ",
                `per_MiddleName` = " . db_escape($member->per_MiddleName) . ",
                `per_LastName` = " . db_escape($member->per_LastName) . ",
                `per_Address1` = " . db_escape($member->per_Address1) . ",
                `per_Zip` = " . db_escape($member->per_Zip) . ",
                `per_HomePhone` = " . db_escape($member->per_HomePhone) . ",
                `per_Email` = " . db_escape($member->per_Email) . ",
                `per_BirthMonth` = " . db_escape($member->per_BirthMonth) . ",
                `per_BirthDay` = " . db_escape($member->per_BirthDay) . ",
                `per_BirthYear` = " . db_escape($member->per_BirthYear) . ",
                `per_MembershipDate` = " . db_escape($member->per_MembershipDate) . ",
                `per_Gender` = " . db_escape($member->per_Gender) . ",
                `per_DateLastEdited` = " . db_escape($member->per_DateLastEdited) . ",
                `per_DateEntered` = " . db_escape($member->per_DateEntered) . ",
                `per_EnteredBy` = " . db_escape($member->per_EnteredBy) . ",
                `per_Status` = " . db_escape($member->per_Status) . ",
                `per_EditedBy` = " . db_escape($member->per_EditedBy) . "
                WHERE `per_ID` = " . db_escape($member->per_ID);
    }
    db_query($sql, _('Could not write Member data'));
    if (!$member->per_ID) {
        $member->per_ID = db_insert_id();
    }

    delete_member_grp_rltn($member->per_ID);
    if (is_array($member->per_Group)) {
        foreach ($member->per_Group as $group) {
            db_query("INSERT INTO " . TB_PREF . "group_rltn (`person`,`per_Group`) "
                    . "values(" . db_escape($member->per_ID) . "," . db_escape($group) . ") ", _('Could not write Member group data'));
        }
    }

    return $member->per_ID;
}

//--------------------------------------------------------------------------

function get_members($id = 0, $gender = '', $group = '', $name = '', $db_pager = FALSE) {


    $where = '';
    if (!empty($gender))
        $where .= " AND per_Gender=" . db_escape($gender);
    if ($id)
        $where .= " AND per_ID in (" . $id . ")";
    if ($name)
        $where .= " AND (per_FirstName like " . db_escape('%' . $name . '%') . " or per_LastName like " . db_escape('%' . $name . '%') . " )";

    if ($id && !$db_pager) {
        if (!empty($group))
            $where .= " AND per_ID in (select per_ID from  " . TB_PREF . "group_rltn where per_Group=" . db_escape($group) . " )";

        $sql = "SELECT * FROM " . TB_PREF . "person_per WHERE 1 ";
        $result = db_query($sql . $where, _('Could not get member data.'));
        $row = db_fetch($result);
        $member = new Member();
        $member->per_Address1 = $row['per_Address1'];
        $member->per_BirthDay = $row['per_BirthDay'];
        $member->per_BirthMonth = $row['per_BirthMonth'];
        $member->per_BirthYear = $row['per_BirthYear'];
        $member->per_CellPhone = $row['per_CellPhone'];
        $member->per_HomePhone = $row['per_HomePhone'];
        $member->per_Status = $row['per_Status'];
        $member->per_Email = $row['per_Email'];
        $member->per_FirstName = $row['per_FirstName'];
        $member->per_Gender = $row['per_Gender'];
        $member->per_LastName = $row['per_LastName'];
        $member->per_MembershipDate = $row['per_MembershipDate'];
        $member->per_MiddleName = $row['per_MiddleName'];
        $member->per_Title = $row['per_Title'];
        $member->per_Zip = $row['per_Zip'];
        $member->per_ID = $row['per_ID'];
        return $member;
    } else {
        $group_sql = " (SELECT GROUP_CONCAT(name SEPARATOR ', ') as group from " . TB_PREF . "group_rltn r"
                . " inner join   " . TB_PREF . "per_groups g on g.id=r.per_Group where r.person=p.per_ID group by r.person) as per_Gorup ";
        if (!empty($group))
            $where .= " AND g.id=" . db_escape($group);

        $sql = "SELECT per_ID, CONCAT(per_FirstName, ' ', per_LastName) AS name, per_Gender, per_Address1, "
                . " per_HomePhone, per_Email, GROUP_CONCAT(g.name SEPARATOR ', ') FROM " . TB_PREF . "person_per p"
                . " left join " . TB_PREF . "group_rltn r on p.per_ID=r.person "
                . " left join " . TB_PREF . "per_groups g on g.id=r.per_Group "
                . "  WHERE 1 " . $where . " group by p.per_ID";
        return $sql;
    }
}

function get_member_name($member_id)
{
	$sql = "SELECT CONCAT(per_FirstName, ' ', per_LastName) AS name "
                . " FROM ".TB_PREF."person_per WHERE per_ID=".db_escape($member_id);

	$result = db_query($sql, "could not get member");

	$row = db_fetch_row($result);

	return $row[0];
}

function select_members($id = 0, $gender = '', $group = '', $name = '', $strict = FALSE) {


    $where = '';
    if (!empty($gender))
        $where .= " AND per_Gender=" . db_escape($gender);
    if ($id)
        $where .= ($strict ?  " AND ":" OR ")."  per_ID in (" . $id . ")";
    if ($name)
        $where .= " AND (per_FirstName like " . db_escape('%' . $name . '%') . " or per_LastName like " . db_escape('%' . $name . '%') . " )";
    $group_sql = " (SELECT GROUP_CONCAT(name SEPARATOR ', ') as group from " . TB_PREF . "group_rltn r"
                . " inner join   " . TB_PREF . "per_groups g on g.id=r.per_Group where r.person=p.per_ID group by r.person) as per_Gorup ";
        if (!empty($group))
            $where .= " AND g.id=" . db_escape($group);

        $sql = "SELECT per_ID, CONCAT(per_FirstName, ' ', per_LastName) AS name, per_Gender,  "
                . " per_HomePhone, GROUP_CONCAT(g.name SEPARATOR ', ') FROM " . TB_PREF . "person_per p"
                . " left join " . TB_PREF . "group_rltn r on p.per_ID=r.person "
                . " left join " . TB_PREF . "per_groups g on g.id=r.per_Group "
                . "  WHERE 1 " . $where . " group by p.per_ID";
        return $sql;
}

function get_member_groups($member) {
    $sql = "SELECT g.id as id,g.name name,g.description as description FROM " . TB_PREF . "group_rltn r  "
            . " left join  " . TB_PREF . "per_groups g on r.per_Group=g.id "
            . " WHERE r.person = " . db_escape($member);
    $result = db_query($sql, _('Could not select member group data.'));
    //var_dump(db_fetch_assoc($result));
    return $result;
}

//--------------------------------------------------------------------------

function delete_member($id) {
    $sql = "DELETE FROM " . TB_PREF . "person_per WHERE per_ID = " . db_escape($id);
    db_query($sql, _('Could not delete member data.'));
}

function delete_member_grp_rltn($member) {
    $sql = "DELETE FROM " . TB_PREF . "group_rltn WHERE person = " . db_escape($member);
    db_query($sql, _('Could not delete member group data.'));
}

function get_member_by_phone($phone) {
    $sql = "select * FROM " . TB_PREF . "person_per WHERE RIGHT(per_HomePhone,9) = " . db_escape($phone) . " limit 1";
    $result = db_query($sql, _('Could not select member data.'));
    $row = db_fetch($result);
    return $row;
}


//--------------------------------------------------------------------------

function mmbr_img_name($emp_id) {

    $emp_id = strtr($emp_id, "><\\/:|*?", '________');
    return clean_file_name($emp_id);
}
