#!/usr/bin/python
import sys
import smtplib
import os
import re
import MySQLdb
import time
# Random gift recipients for Christmas 2008

# SQL : connection to the database :

try:
        conn = MySQLdb.connect (host = "localhost", user = "######", passwd = "######", db = "noelfam")
except MySQLdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)

gifted_ids = ['z'] # Initialization of Recipient List for unicity.

# SQL : Retrieval of all members + Execution de la boucle principale
cursor = conn.cursor()

once = "false"

for i in range(1,6):
  if i == 1:
    current_fam_id = 3
  if i == 2:
    current_fam_id = 1
  if i == 3:
    current_fam_id = 2
  if i == 4:
    current_fam_id = 4
  if i == 5:
    current_fam_id = 5

  if once == "true":
    cursor.execute ("""SELECT * FROM membres WHERE id_membre <> '18' ORDER BY RAND() LIMIT 1""")
    print "once == true"
    print "ID : 18 : ODETTE:"

  else:
    cursor.execute ("""SELECT * FROM membres WHERE family = %s ORDER BY RAND()""",(current_fam_id))
    print "once == false"

  print "I :::::::::: ============= %d" % i

  while(1):
        row = cursor.fetchone()
        if row != None:
                print "_____________________________"
                id_membre = int(row[0])
                name = row[1]
                fam_id = int(row[2])
                mail = row[3]
                print "DONNEUR :: %s :: id %s :: fam: %s" % (name, id_membre, fam_id)
                cursor1 = conn.cursor() # A second cursor for imbricated SQL queries

                print "I :::::::::::::: %d" % i

                if once == "true":
                  print "once == true again"
                  once = "false"
                  print "%s" % once
                  try:
                        cursor1.execute ("""SELECT name, id_membre FROM membres WHERE id_membre = '18' """)
                  except MySQLdb.Error, e:
                        print "Error %d: %s" % (e.args[0], e.args[1])
                else:
                  print "once == false now..."
                  try:
                        cursor1.execute("""SELECT name, id_membre FROM membres WHERE family <> %s AND id_membre <> %s ORDER BY RAND() LIMIT 1""",(fam_id, id_membre))
                  except MySQLdb.Error, e:
                        print "Error %d: %s" % (e.args[0], e.args[1])


                gifted_row = cursor1.fetchone()

                if gifted_row[1] in gifted_ids:
                        while( gifted_row[1] in gifted_ids ):
#                                print "CHECKING FOR OTHER DESTINAID..."
                                try:
                                        cursor1.execute ("""SELECT name, id_membre FROM membres WHERE family <> %s AND id_membre <> %s ORDER BY RAND() LIMIT 1""", (fam_id, id_membre))
                                except MySQLdb.Error, e:
                                        print "Error %d: %s" % (e.args[0], e.args[1])
                                gifted_row = cursor1.fetchone()

                gifted_ids.append(gifted_row[1])
                print "Identifiant du RECEVEUR : %s" % gifted_row[1]
                print "%s offre un cadeau a %s" % (row[1], gifted_row[0])



                #MAIL SENDING (TESTING)

                # EMAIL Handling Part
                SMTPserver = 'smtp.scarlet.be'
                sender =     '#####@scarlet.be'
                USERNAME = "########"
                PASSWORD = "########"


                text_subtype = 'plain' # typical values for text_subtype are plain, html, xml

                #destination = ['###########@hotmail.com']
                destination = mail
                content="""\
                Bonjour/Bonsoir %s, Voici le courriel de Nowel 2009

                Alors, tu devras trouver un chouette petit cadeau pour %s
                Hohoho...Joyeux Noel. J'espere qu'il y aura de la neige :D. Y en avais pas l'annee derniere. Et il n'y a toujours pas d'accents dans mes mails. ho ho ho.

                Encore une fois, joyeux Noel.

                L'ordinateur de Francois.

                PS: Francois m'embete ! Il n'arrete pas de me surcharger d'informations et en plus il m'a overclocke. Et si je chauffe de trop, il s'en fout on dirait...
                Enfin, voila, ce message est de la part de l'ordinateur de Francois..
                """ % (name, gifted_row[0])

                subject = "Nowel 2009 pour %s. Pour les cadeaux..." % (name)

                #from smtplib import SMTP_SSL as SMTP       # this invokes the secure SMTP protocol (port 465, uses SSL)
                from smtplib import SMTP                  # use this for standard SMTP protocol   (port 25, no encryption)
                from email.MIMEText import MIMEText

                msg = MIMEText(content, text_subtype)
                msg['Subject']=       subject


                connsmtp = SMTP(SMTPserver)
                connsmtp.set_debuglevel(False)
                connsmtp.login(USERNAME, PASSWORD)

                connsmtp.sendmail(sender, destination, msg.as_string())
                time.sleep(5)
        else:
                break

# SQL : Closing MySQL connection.
conn.close()