<?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)
    {
        //use descending order as it will be reversed below
        $sqlp1 = "SELECT tb_Person.col_PersonID,
                        tb_Person.col_Name
                FROM    tb_Person
                WHERE   tb_Person.col_ID = '$var_ID'
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Person.col_PersonID DESC";

        //echo '<br>'.$sqlp1;
        $resultp1 = mysqli_query($dblink,$sqlp1);
        if ($resultp1)
          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
                WHERE   tb_Person.col_ID = '$var_ID'
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Person.col_PersonID";

        //echo '<br>'.$sqlp1;
        $resultp1 = mysqli_query($dblink,$sqlp1);
        if ($resultp1)
          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,$var_Lines)
{
    global $dblink, $UsePerson;
    
    $var_Address = "";
    if ($UsePerson)
    {
        $sqlp1 = "SELECT tb_Person.col_PersonID,
                    tb_Person.col_Type,
                    tb_Person.col_salutation,
                    tb_Person.col_Name,
                    tb_Person.col_Apartment,
                    tb_Person.col_Address,
                    tb_Person.col_City,
                    tb_Person.col_Province,
                    tb_Person.col_Postal,
                    tb_Person.col_Country,
                    tb_Person.col_Email
                FROM    tb_Person
                WHERE   tb_Person.col_ID = '$var_ID'
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Person.col_PersonID";

        //echo '<br>'.$sqlp1;
        $resultp1 = mysqli_query($dblink,$sqlp1);
        if ($resultp1)
          while ($myrowp1 = mysqli_fetch_array($resultp1))
        {
            if (!$var_Address)
            {
                $var_Address = $myrowp1['col_Address'];
                $var_Apartment = $myrowp1['col_Apartment'];
                $var_City = $myrowp1['col_City'];
                $var_Province = $myrowp1['col_Province'];
                $var_Country = $myrowp1['col_Country'];
                $var_Postal = $myrowp1['col_Postal'];

                $var_HOApartment = ($var_Apartment)?" - Apt: $var_Apartment":"";
                $var_HOAddress1 = $var_Address.$var_HOApartment;
                
                //$var_HOAddress2 = $var_HOCity.', '.$var_HOProvince.' '.$var_HOPostalCode;
                $var_HOAddress2 = $var_City;
                $var_HOAddress2 .= ($var_Province)?', '.$var_Province :'';
                
                $var_HOAddress3 .= $var_Country;
                $var_HOAddress3 .= ($var_Country && $var_Postal)?' '.$var_Postal : $var_Postal;
                $var_TxtNamesBR = "<br/>";
            }
            
            if ($var_Address) break;
        }
    }
    
    if ($var_Lines==1)
    {
        $var_HOAddress = $var_HOAddress1;
    }
    elseif ($var_Lines==2)
    {
        $var_HOAddress  = $var_HOAddress1;
        $var_HOAddress .= ($var_HOAddress2)? $var_TxtNamesBR.$var_HOAddress2 : "";
        $var_HOAddress .= ($var_HOAddress3)? ", ".$var_HOAddress3 : "";
    }
    elseif ($var_Lines==3)
    {
        $var_HOAddress  = $var_HOAddress1;
        $var_HOAddress .= ($var_HOAddress2)? $var_TxtNamesBR.$var_HOAddress2 : "";
        $var_HOAddress .= ($var_HOAddress3)? $var_TxtNamesBR.$var_HOAddress3 : "";
    }
    
    return $var_HOAddress;
}

////////////////////////////////////////////////////////////////////////////////
// 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
                WHERE   tb_Person.col_ID = '$var_ID'
                AND     tb_Person.col_Type = 'Contact'
                ORDER BY tb_Person.col_PersonID";

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

////////////////////////////////////////////////////////////////////////////////
// get the person array structure for a given Deal ID (or Worksheet ID)
//
function GetPerson($var_PersonID)
{
    $sql = "SELECT  *
            FROM    tb_Person
            WHERE   tb_Person.col_PersonID = '$var_PersonID'";
    $result = mysqli_query($dblink,$sql);
    if ($result)
        if ($myrow = mysqli_fetch_assoc($result))
            return $myrow;
        else
            return array();
}

////////////////////////////////////////////////////////////////////////////////
// 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_Person.col_Phone1,
                        tb_Person.col_Phone1Type,
                        tb_Person.col_Phone1Desc,
         
                        tb_Person.col_Phone2,
                        tb_Person.col_Phone2Type,
                        tb_Person.col_Phone2Desc,

                        tb_Person.col_Phone3,
                        tb_Person.col_Phone3Type,
                        tb_Person.col_Phone3Desc

                FROM    tb_Person
                WHERE   tb_Person.col_PersonID = '$var_PersonID'";

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

            $var_Name = $myrowp2['col_Name'];

            $var_Phone1 = $myrowp2['col_Phone1'];
            $var_Phone1Type = $myrowp2['col_Phone1Type'];
            $var_Phone1Desc = $myrowp2['col_Phone1Desc'];

            $var_Phone2 = $myrowp2['col_Phone2'];
            $var_Phone2Type = $myrowp2['col_Phone2Type'];
            $var_Phone2Desc = $myrowp2['col_Phone2Desc'];

            $var_Phone3 = $myrowp2['col_Phone3'];
            $var_Phone3Type = $myrowp2['col_Phone3Type'];
            $var_Phone3Desc = $myrowp2['col_Phone3Desc'];

            if ($var_Phone1)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone1Type.": ";
                $var_TxtPhones .= format_phone($var_Phone1);
                $var_TxtPhones .= ($var_Phone1Desc)? " ".$var_Phone1Desc : "";
                $var_TxtPhonesBR = " ";
            }

            if ($var_Phone2)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone2Type.": ";
                $var_TxtPhones .= format_phone($var_Phone2);
                $var_TxtPhones .= ($var_Phone2Desc)? " ".$var_Phone2Desc : "";
                $var_TxtPhonesBR = " ";
            }

            if ($var_Phone3)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone3Type.": ";
                $var_TxtPhones .= format_phone($var_Phone3);
                $var_TxtPhones .= ($var_Phone3Desc)? " ".$var_Phone3Desc : "";
                $var_TxtPhonesBR = " ";
            }
        }
    }
    return $var_Name." - ".$var_TxtPhones; 
}

////////////////////////////////////////////////////////////////////////////////
// get Phones for given Person ID
//
function GetEmail($var_PersonID)
{
    global $dblink, $UsePerson;
    
    $var_TxtPhones = "";
    $var_TxtPhonesBR = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 
                        tb_Person.col_Email
                FROM    tb_Person
                WHERE   tb_Person.col_PersonID = '$var_PersonID'";

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

            $var_Name = $myrowp2['col_Name'];
            $var_Email = $myrowp2['col_Email'];
        }
    }
    return $var_Email; 
}

////////////////////////////////////////////////////////////////////////////////
// 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_Person.col_Phone1,
                        tb_Person.col_Phone1Type,
                        tb_Person.col_Phone1Desc,
         
                        tb_Person.col_Phone2,
                        tb_Person.col_Phone2Type,
                        tb_Person.col_Phone2Desc,

                        tb_Person.col_Phone3,
                        tb_Person.col_Phone3Type,
                        tb_Person.col_Phone3Desc

                FROM    tb_Person
                WHERE   tb_Person.col_PersonID = '$var_PersonID'";

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

            $var_Name = $myrowp2['col_Name'];

            $var_Phone1 = $myrowp2['col_Phone1'];
            $var_Phone1Type = $myrowp2['col_Phone1Type'];
            $var_Phone1Desc = $myrowp2['col_Phone1Desc'];

            $var_Phone2 = $myrowp2['col_Phone2'];
            $var_Phone2Type = $myrowp2['col_Phone2Type'];
            $var_Phone2Desc = $myrowp2['col_Phone2Desc'];

            $var_Phone3 = $myrowp2['col_Phone3'];
            $var_Phone3Type = $myrowp2['col_Phone3Type'];
            $var_Phone3Desc = $myrowp2['col_Phone3Desc'];

            if ($var_Phone1)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone1Type.": ";
                $var_TxtPhones .= format_phone($var_Phone1);
                $var_TxtPhones .= ($var_Phone1Desc)? " ".$var_Phone1Desc : "";
                $var_TxtPhonesBR = " ";
            }

            if ($var_Phone2)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone2Type.": ";
                $var_TxtPhones .= format_phone($var_Phone2);
                $var_TxtPhones .= ($var_Phone2Desc)? " ".$var_Phone2Desc : "";
                $var_TxtPhonesBR = " ";
            }

            if ($var_Phone3)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone3Type.": ";
                $var_TxtPhones .= format_phone($var_Phone3);
                $var_TxtPhones .= ($var_Phone3Desc)? " ".$var_Phone3Desc : "";
                $var_TxtPhonesBR = " ";
            }
        }
    }
    return $var_TxtPhones; 
}

////////////////////////////////////////////////////////////////////////////////
// get Default Phone Information for given Person ID
//
function GetPhoneInfoIndex($var_PersonID,$var_Index)
{
    global $dblink, $UsePerson;
    
    $var_TxtPhones = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 

                        tb_Person.col_Phone1,
                        tb_Person.col_Phone1Type,
                        tb_Person.col_Phone1Desc,
         
                        tb_Person.col_Phone2,
                        tb_Person.col_Phone2Type,
                        tb_Person.col_Phone2Desc,

                        tb_Person.col_Phone3,
                        tb_Person.col_Phone3Type,
                        tb_Person.col_Phone3Desc

                FROM    tb_Person
                WHERE   tb_Person.col_PersonID = '$var_PersonID'";

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

            $var_Name = $myrowp2['col_Name'];

            $var_Phone1 = $myrowp2['col_Phone1'];
            $var_Phone1Type = $myrowp2['col_Phone1Type'];
            $var_Phone1Desc = $myrowp2['col_Phone1Desc'];

            $var_Phone2 = $myrowp2['col_Phone2'];
            $var_Phone2Type = $myrowp2['col_Phone2Type'];
            $var_Phone2Desc = $myrowp2['col_Phone2Desc'];

            $var_Phone3 = $myrowp2['col_Phone3'];
            $var_Phone3Type = $myrowp2['col_Phone3Type'];
            $var_Phone3Desc = $myrowp2['col_Phone3Desc'];

            if ($var_Phone1 && $var_Index==1)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone1Type.": ";
                $var_TxtPhones .= format_phone($var_Phone1);
                $var_TxtPhones .= ($var_Phone1Desc)? " ".$var_Phone1Desc : "";
                //$var_TxtPhonesBR = " ";
            }

            if ($var_Phone2 && $var_Index==2)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone2Type.": ";
                $var_TxtPhones .= format_phone($var_Phone2);
                $var_TxtPhones .= ($var_Phone2Desc)? " ".$var_Phone2Desc : "";
                //$var_TxtPhonesBR = " ";
            }

            if ($var_Phone3  && $var_Index==3)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone3Type.": ";
                $var_TxtPhones .= format_phone($var_Phone3);
                $var_TxtPhones .= ($var_Phone3Desc)? " ".$var_Phone3Desc : "";
                //$var_TxtPhonesBR = " ";
            }
        }
    }
    return $var_TxtPhones; 
}

////////////////////////////////////////////////////////////////////////////////
// get Phone Information for given Person ID and Phone Type
//
function GetPhoneInfoType($var_PersonID,$var_PhoneType)
{
    global $dblink, $UsePerson;
    
    $var_TxtPhones = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 

                        tb_Person.col_Phone1,
                        tb_Person.col_Phone1Type,
                        tb_Person.col_Phone1Desc,
         
                        tb_Person.col_Phone2,
                        tb_Person.col_Phone2Type,
                        tb_Person.col_Phone2Desc,

                        tb_Person.col_Phone3,
                        tb_Person.col_Phone3Type,
                        tb_Person.col_Phone3Desc

                FROM    tb_Person
                WHERE   tb_Person.col_PersonID = '$var_PersonID'";

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

            $var_Name = $myrowp2['col_Name'];

            $var_Phone1 = $myrowp2['col_Phone1'];
            $var_Phone1Type = $myrowp2['col_Phone1Type'];
            $var_Phone1Desc = $myrowp2['col_Phone1Desc'];

            $var_Phone2 = $myrowp2['col_Phone2'];
            $var_Phone2Type = $myrowp2['col_Phone2Type'];
            $var_Phone2Desc = $myrowp2['col_Phone2Desc'];

            $var_Phone3 = $myrowp2['col_Phone3'];
            $var_Phone3Type = $myrowp2['col_Phone3Type'];
            $var_Phone3Desc = $myrowp2['col_Phone3Desc'];

            if ($var_Phone1 && $var_PhoneType==$var_Phone1Type)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone1Type.": ";
                $var_TxtPhones .= format_phone($var_Phone1);
                $var_TxtPhones .= ($var_Phone1Desc)? " ".$var_Phone1Desc : "";
                //$var_TxtPhonesBR = " ";
            }

            if ($var_Phone2 && $var_PhoneType==$var_Phone2Type)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone2Type.": ";
                $var_TxtPhones .= format_phone($var_Phone2);
                $var_TxtPhones .= ($var_Phone2Desc)? " ".$var_Phone2Desc : "";
                //$var_TxtPhonesBR = " ";
            }

            if ($var_Phone3 && $var_PhoneType==$var_Phone3Type)
            {
                $var_TxtPhones .= $var_TxtPhonesBR.$var_Phone3Type.": ";
                $var_TxtPhones .= format_phone($var_Phone3);
                $var_TxtPhones .= ($var_Phone3Desc)? " ".$var_Phone3Desc : "";
                //$var_TxtPhonesBR = " ";
            }
        }
    }
    return $var_TxtPhones; 
}

////////////////////////////////////////////////////////////////////////////////
// get Phone Number for given Person ID and Phone Type
//
function GetPhoneNumber($var_PersonID,$var_PhoneType)
{
    global $dblink, $UsePerson;
    
    $var_TxtPhones = "";
    if ($UsePerson)
    {
        $sqlp2 = "SELECT tb_Person.col_Name, 

                        tb_Person.col_Phone1,
                        tb_Person.col_Phone1Type,
                        tb_Person.col_Phone1Desc,
         
                        tb_Person.col_Phone2,
                        tb_Person.col_Phone2Type,
                        tb_Person.col_Phone2Desc,

                        tb_Person.col_Phone3,
                        tb_Person.col_Phone3Type,
                        tb_Person.col_Phone3Desc

                FROM    tb_Person
                WHERE   tb_Person.col_PersonID = '$var_PersonID'";

        //echo '<br>'.$sqlp2;
        $resultp2 = mysqli_query($dblink,$sqlp2);
        if ($resultp2 && ($myrowp2 = mysqli_fetch_array($resultp2)))
        {
            $var_Name = $myrowp2['col_Name'];

            $var_Phone1 = $myrowp2['col_Phone1'];
            $var_Phone1Type = $myrowp2['col_Phone1Type'];
            $var_Phone1Desc = $myrowp2['col_Phone1Desc'];

            $var_Phone2 = $myrowp2['col_Phone2'];
            $var_Phone2Type = $myrowp2['col_Phone2Type'];
            $var_Phone2Desc = $myrowp2['col_Phone2Desc'];

            $var_Phone3 = $myrowp2['col_Phone3'];
            $var_Phone3Type = $myrowp2['col_Phone3Type'];
            $var_Phone3Desc = $myrowp2['col_Phone3Desc'];

            if ($var_Phone1 && $var_PhoneType==$var_Phone1Type)
            {
                //$var_TxtPhones .= $var_TxtPhonesBR.$var_Phone1Type.": ";
                $var_TxtPhones .= format_phone($var_Phone1);
                //$var_TxtPhones .= ($var_Phone1Desc)? " ".$var_Phone1Desc : "";
                //$var_TxtPhonesBR = " ";
            }

            if ($var_Phone2 && $var_PhoneType==$var_Phone2Type)
            {
                //$var_TxtPhones .= $var_TxtPhonesBR.$var_Phone2Type.": ";
                $var_TxtPhones .= format_phone($var_Phone2);
                //$var_TxtPhones .= ($var_Phone2Desc)? " ".$var_Phone2Desc : "";
                //$var_TxtPhonesBR = " ";
            }

            if ($var_Phone3 && $var_PhoneType==$var_Phone3Type)
            {
                //$var_TxtPhones .= $var_TxtPhonesBR.$var_Phone3Type.": ";
                $var_TxtPhones .= format_phone($var_Phone3);
                //$var_TxtPhones .= ($var_Phone3Desc)? " ".$var_Phone3Desc : "";
                //$var_TxtPhonesBR = " ";
            }
        }
    }
    return $var_TxtPhones; 
}

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