#
# By Gilbert Le Blanc
# http://www.python-cgi-programming.com/
# webmaster@python-cgi-programming.com
#
# Released under GNU Public License
#
# Copyright (C) 

# This program is free software; you can redistribute it and/or modify it under the terms of the 
# GNU General Public License as published by the Free Software Foundation; either version 2 of the 
# License, or (at your option) any later version.

# This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without 
# even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 
# General Public License for more details.

# http://www.opensource.org/licenses/gpl-license.html

# This General Public License does not permit incorporating your program into proprietary programs. 

#
# September 15, 2007 - Version 1.02
#   Update referer title every time, to pick up web page name changes.
#
# November 20, 2005 - Version 1.01
#   Add where clause to SelectReferrer query to test for a Referrer Title.
#   Correct update referrer test for UpdateReferrer.
#
# August 18, 2005  -  Version 1.00
#

import MySQLdb

DATABASE_1 = "referrers"
HOST       = "localhost"
USER       = ""
PASSWORD   = ""

def SelectReferrer(domain, minimum, expires, limit):
    """Select the referrer information from all three tables"""

    seconds            = 86400L * long(expires)
    
    select_sql         = "Select r.Referrer_Title, r.Referrer_URL, rc.Referrer_Count"
    select_sql        += "  from domain d, referrer r, referrer_count rc"
    select_sql        += " where d.Domain_Name      = " + '"' + domain + '"'
    select_sql        += "   and rc.Domain_ID       = d.Domain_ID"
    select_sql        += "   and rc.Referrer_ID     = r.Referrer_ID"
    select_sql        += "   and r.Referrer_Title  >  ' '"
    select_sql        += "   and rc.Referrer_Count >= " + str(minimum)
    select_sql        += "   and DATE_ADD(rc.Referrer_Timestamp, INTERVAL "
    select_sql        +=     str(seconds) + " SECOND) >= Now()"
    select_sql        += " order by rc.Referrer_Count desc, r.Referrer_Title asc"

    if (int(limit) > 0):
        select_sql    += " limit 0, " + str(limit)
    #print select_sql
        
    db                 = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
    c                  = db.cursor()

    c.execute(select_sql)
    result             = c.fetchall()

    return result


def UpdateReferrer(domain, referrer, title):
    """Update the referrer count, inserting rows as needed"""
    
    commit                   = "Commit"
    
    insert_domain            = "Insert into domain ("
    insert_domain           += "       Domain_ID"
    insert_domain           += "      ,Domain_Name"
    insert_domain           += ") "
    insert_domain           += 'values (Null, '
    insert_domain           += '"' + domain   + '"'
    insert_domain           += ")"

    insert_referrer          = "Insert into referrer ("
    insert_referrer         += "       Referrer_ID"
    insert_referrer         += "      ,Referrer_Timestamp"
    insert_referrer         += "      ,Referrer_URL"
    insert_referrer         += "      ,Referrer_Title"
    insert_referrer         += ") "
    insert_referrer         += 'values (Null, Now(), '
    insert_referrer         += '"' + referrer + '", '
    insert_referrer         += '"' + title[1] + '"'
    insert_referrer         += ")"

    select_domain            = "Select Domain_ID from domain"
    select_domain           += " where Domain_Name = " + '"' + domain + '"'
    
    select_insert_id         = "Select LAST_INSERT_ID()"

    select_referrer          = "Select Referrer_ID"
    select_referrer         += "      ,Referrer_Title"
    select_referrer         += "  from referrer"
    select_referrer         += " where Referrer_URL = " + '"' + referrer + '"'
    
    update_referrer          = "Update referrer "
    update_referrer         += "Set Referrer_Title        = " + '"' + title[1] + '"' 
    update_referrer         += "   ,Referrer_Timestamp    = Now()"  
    update_referrer         += " where Referrer_URL       = " + '"' + referrer + '"'

    db                 = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
    c                  = db.cursor()

    c.execute(select_domain)
    result             = c.fetchone()
    
    if (result == None):
        c.execute(insert_domain)
        c.execute(select_insert_id)
        domain_id      = int(c.fetchone()[0])
    else:
        domain_id      = int(result[0])  

    c.execute(select_referrer)
    result             = c.fetchone()
    
    if (result == None):
        c.execute(insert_referrer)
        c.execute(select_insert_id)
        referrer_id    = int(c.fetchone()[0])
    else:
##        if (title[0] == "T") or (str(result[1]) == ' '):
        c.execute(update_referrer)
        referrer_id    = int(result[0])

    insert_referrer_count    = "Insert into referrer_count ("
    insert_referrer_count   += "       Domain_ID"
    insert_referrer_count   += "      ,Referrer_ID"
    insert_referrer_count   += "      ,Referrer_Count"
    insert_referrer_count   += "      ,Referrer_Timestamp"
    insert_referrer_count   += ") "
    insert_referrer_count   += 'values ('
    insert_referrer_count   +=       str(domain_id)   + ', '
    insert_referrer_count   +=       str(referrer_id) + ', '
    insert_referrer_count   +=      "1, Now()"
    insert_referrer_count   += ")"

    select_referrer_count    = "Select Referrer_Count from referrer_count"
    select_referrer_count   += " where Domain_ID          = " + str(domain_id)
    select_referrer_count   += " and   Referrer_ID        = " + str(referrer_id)

    update_referrer_count    = "Update referrer_count "
    update_referrer_count   += "Set Referrer_Count        = Referrer_Count + 1" 
    update_referrer_count   += "   ,Referrer_Timestamp    = Now()"  
    update_referrer_count   += " where Domain_ID          = " + str(domain_id)
    update_referrer_count   += " and   Referrer_ID        = " + str(referrer_id)

    c.execute(select_referrer_count)
    result             = c.fetchone()

    if (result == None):
        c.execute(insert_referrer_count)
    else:
        c.execute(update_referrer_count)

    c.execute(commit)
    
    c.close()

    return