<?php
// ***************************************************************
// ***************************************************************
// ***************************************************************
// Memeber Mail Functions


// ***************************************************************
// ***************************************************************
// Member Mail Send
// Sends a member an email, saves he email to the database
// all member mailing should go through here

function member_mail_send( $from_member_id, $from_network_id, $subject, $message, $type = 'mail', $reply_id = 0, $to_array = null )
{
    global $link;
    global $memberinfo;

    //save the mail record to the internal email
    $query='INSERT INTO tool_member_mail (
             mail_from_member_id,
             mail_from_network_id,
             mail_reply_id,
             mail_subject,
             mail_message,
             mail_type,
             mail_date_added
        ) VALUES (
            "'.$from_member_id.'",
            "'.$from_network_id.'",
            "'.$reply_id.'",
            "'.mysql_real_escape_string( $subject ).'",
            "'.mysql_real_escape_string( $message ).'",
            "'.$type.'",
            "'.time().'"
        )';
    mysql_query( $query, $link ) or mysql_error_handler( $query );

    // Add to records
    if( $reply_id == 0 )
    {
        // Get message id
        $message_id = mysql_insert_id();

        // Add from member to array
        $query = 'INSERT INTO tool_member_mail_to (
                    to_mail_id,
                    to_member_id,
                    to_network_id,
                    to_read,
                    to_delete_mail_id
                ) VALUES (
                    "'.$message_id.'",
                    "'.$from_member_id.'",
                    "'.$from_network_id.'",
                    "Yes",
                    NULL
                )';

        // Loop throut the $to array and add to records
        foreach( $to_array as $to )
        {
            $query .= ',(
                    "'.$message_id.'",
                    "'.$to['member'].'",
                    "'.$to['network'].'",
                    "No",
                    NULL
                )';
        }

        // Run query
        mysql_query( $query, $link ) or mysql_error_handler( $query );
    }

    // Clear to records for existing messages
    else
    {
        $query = 'UPDATE tool_member_mail_to SET
            to_read = "No",
            to_delete_mail_id = NULL
            WHERE to_mail_id = "'.$reply_id.'"';
        mysql_query( $query, $link ) or mysql_error_handler( $query );
        $message_id = $reply_id;
    }

    // From
    $from = 'Who\'s In Your City <no-reply@whosinyourcity.com>';

    // Subject
    $query='SELECT member_firstname, member_lastname
        FROM tool_member
        WHERE member_id = "'.$from_member_id.'"
        LIMIT 1';
    $result_name = mysql_query( $query, $link ) or mysql_error_handler( $query );
    if( mysql_num_rows( $result_name ) )
    {
        $record_name = mysql_fetch_assoc( $result_name );
        $subject = $record_name['member_firstname'].' '.$record_name['member_lastname'].' Sent you a message on Who\'s In Your City';
    }
    else
    {
        $subject = 'New mail from Who\'s In Your City';
    }

    // Select all members but the sending member
    $query = 'SELECT *
        FROM tool_member
        INNER JOIN tool_member_mail_to ON to_member_id = member_id
        WHERE to_mail_id = "'.$message_id.'"
        AND member_id <> "'.$from_member_id.'"';
    $result_member = mysql_query( $query, $link ) or mysql_error_handler( $query );

    if( mysql_num_rows( $result_member ) )
    {
        while( $record_member = mysql_fetch_assoc( $result_member ) )
        {
            $to = $record_member['member_firstname'].' '.$record_member['member_lastname'].' <'.$record_member['member_email'].'>';
            $msg = array();
            //$msg['html'] = '';
            $msg['html'] = str_replace( array( "\n\r", "\n", "\r", "\r\n", chr(13) ), '<br/>', $message );
            send_mail( $to, $from, $subject, $msg, null, true, 'default-mail' );
        }
    }

    return $message_id;
}


// ***************************************************************
// ***************************************************************
// Mark email as read

function member_mail_mark_read( $member_id, $mail_id )
{
    global $link;

    $query = 'UPDATE  tool_member_mail_to SET
        to_read = "Yes"
        WHERE to_member_id = "'.$member_id.'"
        AND to_mail_id = "'.$mail_id.'"
        LIMIT 1';
    mysql_query( $query, $link ) or mysql_error_handler( $query );

    return true;
}

// ***************************************************************
// ***************************************************************
// Get Mail Count

function member_mail_unread_count( $member_id, $from_network=null )
{
    global $link;

    $query='SELECT a.*,b.to_id, b.to_read,d.*,
            (
                SELECT to_network_id
                FROM tool_member_mail_to
                WHERE to_mail_id = a.mail_id
                AND to_member_id = "'.$member_id.'"
                LIMIT 1
            ) AS to_network_id
            FROM tool_member_mail AS a
            INNER JOIN tool_member_mail_to AS b ON to_mail_id = a.mail_id ';

    if( isset( $from_network ) && !empty( $from_network ) )
        $query .= ' AND to_network_id = "'.$from_network.'" ';

    $query.='INNER JOIN tool_network AS d
                ON to_network_id = network_id
             INNER JOIN tool_member
                ON member_id = mail_from_member_id

            -- Get main MAIL messages only
            WHERE a.mail_reply_id = 0
            AND mail_type="Mail"

            -- Mail is to current member and not deleted and not read
            AND to_delete_mail_id IS NULL
            AND to_read = "No"
            AND to_member_id = "'.$member_id.'"
            GROUP BY a.mail_id  ';

    $result_mail = mysql_query( $query, $link ) or mysql_error_handler( $query );
    $rows = mysql_num_rows( $result_mail );

    mysql_free_result( $result_mail );
    return $rows;

}

?>