using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Net.Mail;

public partial class updates : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
			// Get the last time the updates was run.
			string strSql = "SELECT LastLogin FROM UserData WHERE UserID = 'updates'";
			DateTime lastUpdate, weekAgo;
			string eMsg, strScore;
			int fld;

			weekAgo = DateTime.Now.AddDays(-10);

			// Create the connection using the connection string in web.config.
			OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
			OdbcCommand cmd = new OdbcCommand(strSql, conn);
			OdbcDataReader rdr;

			Response.Write("Processing updates <br />");

			conn.Open();
			rdr = cmd.ExecuteReader();

			if (rdr.HasRows)
			{
				lastUpdate = rdr.GetDateTime(0);
				rdr.Close();
				//Response.Write(lastUpdate.ToShortDateString());
				// Last update occurred 7 or more days ago
				if (DateTime.Now >= lastUpdate.AddDays(7))
				{
					strSql = "SELECT * FROM UserData WHERE (SendReport = True AND LastLogin > #" + weekAgo.ToShortDateString() + "#)";
					cmd.CommandText = strSql;
					rdr = cmd.ExecuteReader();
					if (rdr.HasRows)
					{
						// Build each e-mail to go out.
						MailMessage msg = new MailMessage();
						msg.From = (new MailAddress("flashcards@tomusn.com"));
						SmtpClient client = new SmtpClient("smh01.opentransfer.com");

						while (rdr.Read())
						{
							Response.Write(rdr[0] + "<br />");
							Response.Write(rdr[4] + "<br />");


							// start construction of the e-mail body.
							eMsg = "This e-mail is a progress report on the multiplication and division " +
								"flash cards program your child is using.  Your child can retake a test as many times as they wish to " +
								"achieve a better score.\n\nYou will only receive these e-mails as your child is actively using the " +
								"program.  You may also opt-out of receiving these progress e-mails by going to the settings page at " +
								"http://tomusn.com/flashcards and turning off the e-mail progress reports option.\n\n\n" +
								"* MULTIPLICATION CARDS *\n\n Factor   Current Score\n\n";

							// get the times columns 0-9 (single digits).
							for (fld = 5; fld <= 14; fld++)
							{
								strScore = rdr[fld].ToString();
								if (strScore.Length == 0)
									strScore = "not tested";
								eMsg = eMsg + "    " + (fld - 5) + "     " + strScore + "\n";
							}
							// get the times columns 10-12.
							for (fld = 15; fld <= 17; fld++)
							{
								strScore = rdr[fld].ToString();
								if (strScore.Length == 0)
									strScore = "not tested";
								eMsg = eMsg + "   " + (fld - 5) + "     " + strScore + "\n";
							}
							// get the All score
							strScore = rdr[18].ToString();
							if (strScore.Length == 0)
								strScore = "not tested";
							eMsg = eMsg + "  ALL     " + strScore + "\n\n";

							// get division scores.
							eMsg = eMsg + "  * DIVISION CARDS *\n\n Divisor  Current Score\n\n";
							for (fld = 19; fld <= 27; fld++)
							{
								strScore = rdr[fld].ToString();
								if (strScore.Length == 0)
									strScore = "not tested";
								eMsg = eMsg + "    " + (fld - 18) + "     " + strScore + "\n";
							}
							// get the div columns 10-12.
							for (fld = 28; fld <= 30; fld++)
							{
								strScore = rdr[fld].ToString();
								if (strScore.Length == 0)
									strScore = "not tested";
								eMsg = eMsg + "   " + (fld - 18) + "     " + strScore + "\n";
							}
							// get the All score
							strScore = rdr[31].ToString();
							if (strScore.Length == 0)
								strScore = "not tested";
							eMsg = eMsg + "  ALL     " + strScore + "\n";

							// msg body constructed at this point.
							msg.Body = eMsg;
							msg.Subject = "Flash Cards Progress";
							msg.To.Clear();
							msg.To.Add(rdr["ParentEMail"].ToString());
							client.Send(msg);
						} 
					}

					// write new update date.
					rdr.Close();
					strSql = "UPDATE UserData SET LastLogin='" + DateTime.Now + "' WHERE UserID = 'updates'";
					cmd.CommandText = strSql;
					cmd.ExecuteNonQuery(); 
				}
			}


			rdr.Close();
			conn.Close();

			Response.Write("Processing complete");
    } 
}