<?php
//home/vhost/www/apps.prospectin.com/_common/_uti/BstFunctions.inc
/*****************************************************************************************/
/*****************************************************************************************/
/******************************** Start - Person Functions *********************************/
//
require_once("../../../_common/_uti/BstFunctions.inc");
//
////////////////////////////////////////////////////////////////////////////////
// get all names for a given Deal ID (or Worksheet ID)
//
function GetNames($var_ID)
{
    global $dblink, $UsePerson;
    
    $cnt_names = 0;
    $var_theNames = "";
    if ($UsePerson)
    {
        $sqlp1 = "SELECT tb_Person.col_PersonID,
                        tb_Person.col_Name
                FROM    tb_Person
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Person.col_Type
                AND     tb_Lists.col_VariableName = 'PersonType'
                WHERE   tb_Person.col_ID = '$var_ID'
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Lists.col_ValueOrder, tb_Person.col_Type";

        //echo '<br>'.$sqlp1;
        $resultp1 = mysqli_query($dblink,$sqlp1);
        while ($myrowp1 = mysqli_fetch_array($resultp1))
        {
            $var_Name = $myrowp1['col_Name'];
            if ($var_Name)
            {
                if (!$cnt_names)
                    $var_theNames = $var_Name;
                elseif ($cnt_names==1)
                    $var_theNames = $var_Name." & ".$var_theNames;
                else
                    $var_theNames = $var_Name.", ".$var_theNames;
                    
                $cnt_names++;
            }
        }
    }
    return $var_theNames; 
}

////////////////////////////////////////////////////////////////////////////////
// get list of names, comma seperated, for a given Deal ID (or Worksheet ID)
//
function GetNameList($var_ID)
{
    global $dblink, $UsePerson;
    
    $var_BR = "";
    $var_theNames = "";
    if ($UsePerson)
    {
        $sqlp1 = "SELECT tb_Person.col_PersonID,
                        tb_Person.col_Name
                FROM    tb_Person
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Person.col_Type
                AND     tb_Lists.col_VariableName = 'PersonType'
                WHERE   tb_Person.col_ID = '$var_ID'
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Lists.col_ValueOrder, tb_Person.col_Type";

        //echo '<br>'.$sqlp1;
        $resultp1 = mysqli_query($dblink,$sqlp1);
        while ($myrowp1 = mysqli_fetch_array($resultp1))
        {
            $var_Name = $myrowp1['col_Name'];
            if ($var_Name)
            {
                $var_theNames .= $var_BR.$var_Name;
                $var_BR = ',';
            }
        }
    }
    return $var_theNames; 
}

////////////////////////////////////////////////////////////////////////////////
// get first address for persons for a given Deal ID (or Worksheet ID)
//
function GetFirstAddress($var_ID)
{
    global $dblink, $UsePerson;
    
    $var_Address = "";
    if ($UsePerson)
    {
        $sqlp1 = "SELECT tb_Person.col_PersonID,
                        tb_Person.col_Address
                FROM    tb_Person
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Person.col_Type
                AND     tb_Lists.col_VariableName = 'PersonType'
                WHERE   tb_Person.col_ID = '$var_ID'
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Lists.col_ValueOrder, tb_Person.col_Type";

        //echo '<br>'.$sqlp1;
        $resultp1 = mysqli_query($dblink,$sqlp1);
        while ($myrowp1 = mysqli_fetch_array($resultp1))
        {
            if (!$var_Address)
                $var_Address = $myrowp1['col_Address'];
            
            if ($var_Address) break;
        }
    }
    return $var_Address; 
}

////////////////////////////////////////////////////////////////////////////////
// get PersonIDs for all persons for a given Deal ID (or Worksheet ID)
//
function GetNameIDs($var_ID)
{
    global $dblink, $UsePerson;
    
    $var_BR = "";
    $var_IDs = "";
    if ($UsePerson)
    {
        $sqlp1 = "SELECT tb_Person.col_PersonID
                FROM    tb_Person
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Person.col_Type
                AND     tb_Lists.col_VariableName = 'PersonType'
                WHERE   tb_Person.col_ID = '$var_I'D
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Lists.col_ValueOrder, tb_Person.col_Type";

        //echo '<br>'.$sqlp1;
        $resultp1 = mysqli_query($dblink,$sqlp1);
        while ($myrowp1 = mysqli_fetch_array($resultp1))
        {
            $var_PersonID = $myrowp1['col_PersonID'];
            $var_IDs = $var_BR.$var_PersonID;
            $var_BR = ',';
        }
    }
    return $var_IDs; 
}

////////////////////////////////////////////////////////////////////////////////
// get Name and Phones for given Person ID
//
function GetNamePhoneInfo($var_PersonID)
{
    global $dblink, $UsePerson;
    
    $var_Name = "";
    $var_TxtPhones = "";
    $var_TxtPhonesBR = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 
                        tb_Phone.col_PhoneID,
                        tb_Phone.col_PhoneType,
                        tb_Phone.col_PhoneNumber,
                        tb_Phone.col_PhoneExtension,
                        tb_Phone.col_Instruction,
                        tb_Phone.col_DefaultPhone
                FROM    tb_Person
                LEFT JOIN tb_Phone ON tb_Person.col_PersonID = tb_Phone.col_PersonID
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Phone.col_PhoneType
                AND     tb_Lists.col_VariableName = 'PhoneType'
                WHERE   tb_Phone.col_PersonID = '$var_PersonID'
                ORDER BY tb_Phone.col_DefaultPhone DESC, tb_Lists.col_ValueOrder";

        //echo '<br>'.$sqlp2;
        $resultp2 = mysqli_query($dblink,$sqlp2);
        while ($myrowp2 = mysqli_fetch_array($resultp2))
        {
            //print_r($myrowp2);
            //echo "<br>";

            $var_Name = $myrowp2['col_Name'];
            $var_PhoneID = $myrowp2['col_PhoneID'];
            $var_PhoneType = $myrowp2['col_PhoneType'];
            $var_PhoneNumber = $myrowp2['col_PhoneNumber'];
            $var_PhoneExtension = $myrowp2['col_PhoneExtension'];
            $var_Instruction = $myrowp2['col_Instruction'];
            $var_DefaultPhone = $myrowp2['col_DefaultPhone'];
            //echo "<br>def".$var_DefaultPhone;

            $var_PhoneNumber = format_phone($var_PhoneNumber);
            $var_Def = ($var_DefaultPhone=="Yes")?'*':'';
            $var_Ext = ($var_PhoneExtension)?' (x'.$var_PhoneExtension.')':'';
            $var_Ins = ($var_Instruction)?' '.$var_Instruction:'';
            $var_TxtPhone = $var_Def.$var_PhoneNumber.$var_Ext.$var_Ins;

            $var_TxtPhones .= $var_TxtPhonesBR.$var_PhoneType.": ".$var_TxtPhone;
            $var_TxtPhonesBR = " ";
        }
    }
    return $var_Name." ".$var_TxtPhones; 
}

////////////////////////////////////////////////////////////////////////////////
// get Phones for given Person ID
//
function GetPhones($var_PersonID)
{
    global $dblink, $UsePerson;
    
    $var_TxtPhones = "";
    $var_TxtPhonesBR = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 
                        tb_Phone.col_PhoneID,
                        tb_Phone.col_PhoneType,
                        tb_Phone.col_PhoneNumber,
                        tb_Phone.col_PhoneExtension,
                        tb_Phone.col_Instruction,
                        tb_Phone.col_DefaultPhone
                FROM    tb_Person
                LEFT JOIN tb_Phone ON tb_Person.col_PersonID = tb_Phone.col_PersonID
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Phone.col_PhoneType
                AND     tb_Lists.col_VariableName = 'PhoneType'
                WHERE   tb_Phone.col_PersonID = '$var_PersonID'
                ORDER BY tb_Phone.col_DefaultPhone DESC, tb_Lists.col_ValueOrder";

        //echo '<br>'.$sqlp2;
        $resultp2 = mysqli_query($dblink,$sqlp2);
        while ($myrowp2 = mysqli_fetch_array($resultp2))
        {
            //print_r($myrowp2);
            //echo "<br>";

            $var_Name = $myrowp2['col_Name'];
            $var_PhoneID = $myrowp2['col_PhoneID'];
            $var_PhoneType = $myrowp2['col_PhoneType'];
            $var_PhoneNumber = $myrowp2['col_PhoneNumber'];
            $var_PhoneExtension = $myrowp2['col_PhoneExtension'];
            $var_Instruction = $myrowp2['col_Instruction'];
            $var_DefaultPhone = $myrowp2['col_DefaultPhone'];
            //echo "<br>def".$var_DefaultPhone;

            $var_PhoneNumber = format_phone($var_PhoneNumber);
            $var_Def = ($var_DefaultPhone=="Yes")?'*':'';
            $var_Ext = ($var_PhoneExtension)?' (x'.$var_PhoneExtension.')':'';
            $var_Ins = ($var_Instruction)?' '.$var_Instruction:'';
            $var_TxtPhone = $var_Def.$var_PhoneNumber.$var_Ext.$var_Ins;

            $var_TxtPhones .= $var_TxtPhonesBR.$var_PhoneType.": ".$var_TxtPhone;
            $var_TxtPhonesBR = " ";
        }
    }
    return $var_TxtPhones; 
}

////////////////////////////////////////////////////////////////////////////////
// get Default Phone Information for given Person ID
//
function GetDefaultPhoneInfo($var_PersonID)
{
    global $dblink, $UsePerson;
    
    $var_TxtPhone = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 
                        tb_Phone.col_PhoneID,
                        tb_Phone.col_PhoneType,
                        tb_Phone.col_PhoneNumber,
                        tb_Phone.col_PhoneExtension,
                        tb_Phone.col_Instruction,
                        tb_Phone.col_DefaultPhone
                FROM    tb_Person
                LEFT JOIN tb_Phone ON tb_Person.col_PersonID = tb_Phone.col_PersonID
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Phone.col_PhoneType
                AND     tb_Lists.col_VariableName = 'PhoneType'
                WHERE   tb_Phone.col_PersonID = '$var_PersonID'
                ORDER BY tb_Phone.col_DefaultPhone DESC, tb_Lists.col_ValueOrder";

        //echo '<br>'.$sqlp2;
        $resultp2 = mysqli_query($dblink,$sqlp2);
        if ($myrowp2 = mysqli_fetch_array($resultp2))
        {
            //print_r($myrowp2);
            //echo "<br>";

            $var_Name = $myrowp2['col_Name'];
            $var_PhoneID = $myrowp2['col_PhoneID'];
            $var_PhoneType = $myrowp2['col_PhoneType'];
            $var_PhoneNumber = $myrowp2['col_PhoneNumber'];
            $var_PhoneExtension = $myrowp2['col_PhoneExtension'];
            $var_Instruction = $myrowp2['col_Instruction'];
            $var_DefaultPhone = $myrowp2['col_DefaultPhone'];
            //echo "<br>def".$var_DefaultPhone;

            $var_PhoneNumber = format_phone($var_PhoneNumber);
            $var_Def = ($var_DefaultPhone=="Yes")?'*':'';
            $var_Ext = ($var_PhoneExtension)?' (x'.$var_PhoneExtension.')':'';
            $var_Ins = ($var_Instruction)?' '.$var_Instruction:'';
            $var_TxtPhone = $var_PhoneType.": ".$var_Def.$var_PhoneNumber.$var_Ext.$var_Ins;
        }
    }
    return $var_TxtPhone; 
}

////////////////////////////////////////////////////////////////////////////////
// get Default Phone Number for given Person ID
//
function GetDefaultPhoneNumber($var_PersonID)
{
    global $dblink, $UsePerson;
    
    $var_PhoneNumber = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 
                        tb_Phone.col_PhoneID,
                        tb_Phone.col_PhoneType,
                        tb_Phone.col_PhoneNumber,
                        tb_Phone.col_PhoneExtension,
                        tb_Phone.col_Instruction,
                        tb_Phone.col_DefaultPhone
                FROM    tb_Person
                LEFT JOIN tb_Phone ON tb_Person.col_PersonID = tb_Phone.col_PersonID
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Phone.col_PhoneType
                AND     tb_Lists.col_VariableName = 'PhoneType'
                WHERE   tb_Phone.col_PersonID = '$var_PersonID'
                ORDER BY tb_Phone.col_DefaultPhone DESC, tb_Lists.col_ValueOrder";

        //echo '<br>'.$sqlp2;
        $resultp2 = mysqli_query($dblink,$sqlp2);
        if ($myrowp2 = mysqli_fetch_array($resultp2))
        {
            $var_PhoneNumber = $myrowp2['col_PhoneNumber'];
            $var_PhoneNumber = format_phone($var_PhoneNumber);
        }
    }
    return $var_PhoneNumber; 
}

////////////////////////////////////////////////////////////////////////////////
// get Phone Information for given Person ID and Phone Type
//
function GetPhoneInfo($var_PersonID,$var_PhoneType)
{
    global $dblink, $UsePerson;
    
    $var_TxtPhone = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 
                        tb_Phone.col_PhoneID,
                        tb_Phone.col_PhoneType,
                        tb_Phone.col_PhoneNumber,
                        tb_Phone.col_PhoneExtension,
                        tb_Phone.col_Instruction,
                        tb_Phone.col_DefaultPhone
                FROM    tb_Person
                LEFT JOIN tb_Phone ON tb_Person.col_PersonID = tb_Phone.col_PersonID
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Phone.col_PhoneType
                AND     tb_Lists.col_VariableName = 'PhoneType'
                WHERE   tb_Phone.col_PersonID = '$var_PersonID'
                AND     tb_Phone.col_PhoneType = '$var_PhoneType'
                ORDER BY tb_Phone.col_DefaultPhone DESC, tb_Lists.col_ValueOrder";

        //echo '<br>'.$sqlp2;
        $resultp2 = mysqli_query($dblink,$sqlp2);
        if ($myrowp2 = mysqli_fetch_array($resultp2))
        {
            //print_r($myrowp2);
            //echo "<br>";

            $var_Name = $myrowp2['col_Name'];
            $var_PhoneID = $myrowp2['col_PhoneID'];
            $var_PhoneType = $myrowp2['col_PhoneType'];
            $var_PhoneNumber = $myrowp2['col_PhoneNumber'];
            $var_PhoneExtension = $myrowp2['col_PhoneExtension'];
            $var_Instruction = $myrowp2['col_Instruction'];
            $var_DefaultPhone = $myrowp2['col_DefaultPhone'];
            //echo "<br>def".$var_DefaultPhone;

            $var_PhoneNumber = format_phone($var_PhoneNumber);
            $var_Def = ($var_DefaultPhone=="Yes")?'*':'';
            $var_Ext = ($var_PhoneExtension)?' (x'.$var_PhoneExtension.')':'';
            $var_Ins = ($var_Instruction)?' '.$var_Instruction:'';
            $var_TxtPhone = $var_PhoneType.": ".$var_Def.$var_PhoneNumber.$var_Ext.$var_Ins;
        }
    }
    return $var_TxtPhone; 
}

////////////////////////////////////////////////////////////////////////////////
// get Phone Number for given Person ID and Phone Type
//
function GetPhoneNumber($var_PersonID,$var_PhoneType)
{
    global $dblink, $UsePerson;
    
    $var_PhoneNumber = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 
                        tb_Phone.col_PhoneID,
                        tb_Phone.col_PhoneType,
                        tb_Phone.col_PhoneNumber,
                        tb_Phone.col_PhoneExtension,
                        tb_Phone.col_Instruction,
                        tb_Phone.col_DefaultPhone
                FROM    tb_Person
                LEFT JOIN tb_Phone ON tb_Person.col_PersonID = tb_Phone.col_PersonID
                LEFT JOIN tb_Lists ON tb_Lists.col_Value = tb_Phone.col_PhoneType
                AND     tb_Lists.col_VariableName = 'PhoneType'
                WHERE   tb_Phone.col_PersonID = '$var_PersonID'
                AND     tb_Phone.col_PhoneType = '$var_PhoneType'
                ORDER BY tb_Phone.col_DefaultPhone DESC, tb_Lists.col_ValueOrder";

        //echo '<br>'.$sqlp2;
        $resultp2 = mysqli_query($dblink,$sqlp2);
        if ($myrowp2 = mysqli_fetch_array($resultp2))
        {
            $var_PhoneNumber = $myrowp2['col_PhoneNumber'];
            $var_PhoneNumber = format_phone($var_PhoneNumber);
        }
    }
    return $var_PhoneNumber; 
}

/******************************** End - Person Functions *********************************/
?>