private void ImportMembers(string ChapterID)
{
DataSet ds = new DataSet();
SqlDataAdapter da;
DataRow[] MemberRows;
string NationalChapterID;
string NationalMemberID, FirstName, LastName;
bool MemberExists, NeedsUpdate;
int GradYear;
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfDB"].ToString());
cnn.Open();
// The import will mark all the members from Nationals as paid, so start by setting everyone to not paid
//SqlCommand cmd = new SqlCommand("UPDATE NationalMembers SET isPaid=0 WHERE ChapterID=" + ChapterID, cnn);
//cmd.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("", cnn);
#region Fill a table with this Chapter's info and determine if an import is needed
da = new SqlDataAdapter(
"SELECT *, isImported=CASE WHEN ImportedDate=CONVERT(date,GETDATE()) THEN 'Y' ELSE 'N' END FROM Chapters WHERE ChapterID=" + ChapterID, cnn);
da.Fill(ds, "Chapters");
NationalChapterID = ds.Tables["Chapters"].Rows[0]["NationalChapterID"].ToString();
// Only import the National members once per day
if (ds.Tables["Chapters"].Rows[0]["isImported"].ToString() == "Y")
{
return;
}
#endregion
#region Import data from Nationals web page using XML
HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(
new Uri("http://members.fbla-pbl.org/members_online/members/chapter_export_national.asp?AuthCode=Secret_Password&ChapterID=" + NationalChapterID));
myRequest.Method = "GET";
myRequest.Accept = "text/xml";
myRequest.AllowAutoRedirect = true;
int NationalMembers = 1; // This is the index in the DataSet for the table that holds the data from Nationals
try {
HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse();
using (Stream responseStream = myResponse.GetResponseStream()) {
//StreamReader reader = new StreamReader(responseStream);
//string text = reader.ReadToEnd();
ds.ReadXml(responseStream, XmlReadMode.InferSchema);
if (ds.Tables.Count != 2 || ds.Tables[NationalMembers].Rows.Count == 0)
{
return;
}
}
} catch (Exception ex) {
// Something went wrong when trying to read from Nationals
return;
}
#endregion
#region Fill a table with Member info for students who haven't already graduated
string strSQL =
"select * from NationalMembers where" +
// ** " ChapterID=" + ChapterID + " AND" + // ** Include all members so we can tell if a member switched to a different chapter
" GraduatingClass >= " + ((DateTime.Now.Year) + ((DateTime.Now.Month > 7) ? 1 : 0));
da = new SqlDataAdapter(strSQL, cnn);
da.Fill(ds, "Members");
#endregion
// Loop through the National members for this chapter and update the system
foreach (DataRow ImportRow in ds.Tables[NationalMembers].Rows)
{
NationalMemberID = ImportRow["IndId"].ToString();
FirstName = ImportRow["FirstName"].ToString().Replace("'", "''").Trim();
LastName = ImportRow["LastName"].ToString().Replace("'", "''").Trim();
MemberExists = false; // Assume the member does not yet exist
NeedsUpdate = false; // Assume there are no updates needed
#region Attempt to import student data
try {
// If it's after July, we're in the first half of a new school year; otherwise, we're in the second half of the school year
GradYear = System.DateTime.Now.Year + 12 - Int32.Parse(ImportRow["FBLAYear"].ToString()) + ((System.DateTime.Now.Month > 7) ? 1 : 0);
// Attempt to locate student by National ID
MemberRows = ds.Tables["Members"].Select("NationalMemberID=" + NationalMemberID);
#region Found student by National ID
if (MemberRows.Length != 0)
{
// Verify student hasn't been transferred to a different chapter
if (MemberRows[0]["ChapterID"].ToString() == ChapterID)
{
MemberExists = true;
// See if anything needs to be updated
NeedsUpdate = MemberRows[0]["isPaid"].ToString() != "1";
NeedsUpdate |= MemberRows[0]["isInactive"].ToString() != "0";
NeedsUpdate |= MemberRows[0]["GraduatingClass"].ToString() != GradYear.ToString();
NeedsUpdate |= MemberRows[0]["FirstName"].ToString() != FirstName;
NeedsUpdate |= MemberRows[0]["LastName"].ToString() != LastName;
if (NeedsUpdate)
{
cmd.CommandText =
"UPDATE NationalMembers SET " +
"isPaid=1, isInactive=0, GraduatingClass=" + GradYear + ", FirstName='" + FirstName + "', LastName='" + LastName + "' " +
"WHERE NationalMemberID=" + NationalMemberID;
cmd.ExecuteNonQuery();
}
}
else
{
// Student was transferred to a different chapter
// Clear the NationalMemberID and paid flag so a new record can be created and set to that number
cmd.CommandText = "UPDATE NationalMembers SET NationalMemberID=NULL, isPaid=0, isInactive=1 WHERE NationalMemberID=" + NationalMemberID;
cmd.ExecuteNonQuery();
}
}
#endregion
#region Did not find student by National Student ID
if (!MemberExists)
{
// Attempt to locate student by chapter & name
MemberRows = ds.Tables["Members"].Select(
"FirstName='" + FirstName + "' AND " +
"LastName='" + LastName + "' AND " +
"ChapterID=" + ChapterID);
#region Found student by chapter & name
if (MemberRows.Length != 0)
{
// Update student by chapter & name
cmd.CommandText =
"UPDATE NationalMembers SET isPaid=1, isInactive=0," +
" GraduatingClass=" + GradYear + "," +
" NationalMemberID=" + NationalMemberID +
" FROM NationalMembers M INNER JOIN Chapters C ON M.ChapterID=C.ChapterID WHERE" +
" C.NationalChapterID=" + NationalChapterID + " AND M.FirstName='" + FirstName + "' AND M.LastName='" + LastName + "'";
cmd.ExecuteNonQuery();
}
#endregion
#region Student not found in database
else
{
// Add new student record
cmd.CommandText =
"INSERT INTO NationalMembers (NationalMemberID, ChapterID, isPaid, FirstName, LastName, GraduatingClass) VALUES (" +
NationalMemberID + "," +
ChapterID + ",1," +
"'" + FirstName + "'," +
"'" + LastName + "'," +
GradYear + ")";
cmd.ExecuteNonQuery();
}
#endregion
}
#endregion
}
catch {
// Each student must have a valid graduating class before being marked as paid
}
#endregion
}
#region Clean up
// Remove spurious members who are not National members and are not signed up for any conference events
cmd.CommandText =
"DELETE FROM NationalMembers WHERE MemberID IN" +
"(SELECT DISTINCT M.MemberID" +
" FROM NationalMembers M LEFT JOIN ConferenceMemberEvents CME ON M.MemberID=CME.MemberID " +
"WHERE ISNULL(M.NationalMemberID,0)=0 AND CME.ConferenceID IS NULL)";
// This query is timing out so I'm just going to skip this cleanup task -- Oct 18, 2014
//cmd.ExecuteNonQuery();
// Clear the paid flag for any member without a National ID
cmd.CommandText = "UPDATE NationalMembers SET isPaid=0 WHERE NationalMemberID IS NULL";
cmd.ExecuteNonQuery();
// Update this chapter as having been imported today
cmd.CommandText = "UPDATE Chapters SET ImportedDate=CONVERT(date,GETDATE()) WHERE ChapterID=" + ChapterID;
cmd.ExecuteNonQuery();
#endregion
cnn.Close();
}
protected void btnImport_Click(object sender, EventArgs e)
{
if (uplFile.HasFile)
{
lstResults.Items.Clear(); // empty the results list
string f = Environment.GetEnvironmentVariable("TEMP") + "\\" + uplFile.FileName;
if (File.Exists(f))
{
File.Delete(f);
}
uplFile.SaveAs(f);
string[] lines = File.ReadAllLines(f);
// used to perform any needed update queries
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfDB"].ToString());
cnn.Open();
SqlCommand cmd = new SqlCommand("", cnn);
// Fill a table with Chapter info
SqlDataAdapter da = new SqlDataAdapter("select * from Chapters", cnn);
DataTable Chapters = new DataTable();
da.Fill(Chapters);
DataRow[] ChapterRows;
// Fill a table with Member info for students who haven't already graduated
da = new SqlDataAdapter(
"select * from NationalMembers where GraduatingClass >= " + ((DateTime.Now.Year) + ((DateTime.Now.Month > 7) ? 1 : 0)), cnn);
DataTable Members = new DataTable();
da.Fill(Members);
DataRow[] MemberRows;
string[] ImportRow;
string NationalChapterID, NationalMemberID, FirstName, LastName;
bool MemberExists, NeedsUpdate;
int GradYear;
// ind_id, ind_first_name, ind_middle_initial, ind_last_name, school_name, school_name2, fbla_oid, member_type, state, fbla_year, fbla_office, last_active_year, date_paid
// 0 = NationalMemberID
// 1 = FirstName
// 3 = LastName
// 6 = NationalChapterID
// 9 = student's grade: 7, 8, 9, 10, 11, or 12
for (int i = 1; i < lines.Length; i++)
{
ImportRow = lines[i].Split('\t');
NationalMemberID = ImportRow[0];
FirstName = ImportRow[1].Replace("'", "''").Trim();
LastName = ImportRow[3].Replace("'", "''").Trim();
NationalChapterID = ImportRow[6];
MemberExists = false; // Assume the member does not yet exist
NeedsUpdate = false; // Assume there are no updated needed
#region Attempt to import/update student data
try {
// If it's after July, we're in the first half of a new school year; otherwise, we're in the second half of the school year
GradYear = DateTime.Now.Year + 12 - Int32.Parse(ImportRow[9]) + ((DateTime.Now.Month > 7) ? 1 : 0);
// Attempt to locate chapter by NationalID
ChapterRows = Chapters.Select("NationalChapterID=" + NationalChapterID);
if (ChapterRows.Length != 0)
{
// Attempt to locate student by National ID
MemberRows = Members.Select("NationalMemberID=" + NationalMemberID);
#region Found student by National ID
if (MemberRows.Length != 0)
{
// Verify student hasn't been transferred to a different chapter
if (MemberRows[0]["ChapterID"].ToString() == ChapterRows[0]["ChapterID"].ToString())
{
MemberExists = true;
// See if anything needs to be updated
NeedsUpdate = MemberRows[0]["isPaid"].ToString() != "1";
NeedsUpdate |= MemberRows[0]["isInactive"].ToString() != "0";
NeedsUpdate |= MemberRows[0]["GraduatingClass"].ToString() != GradYear.ToString();
NeedsUpdate |= MemberRows[0]["FirstName"].ToString() != FirstName;
NeedsUpdate |= MemberRows[0]["LastName"].ToString() != LastName;
if (NeedsUpdate)
{
cmd.CommandText =
"UPDATE NationalMembers SET " +
"isPaid=1, isInactive=0, GraduatingClass=" + GradYear + ", FirstName='" + FirstName + "', LastName='" + LastName + "' " +
"WHERE NationalMemberID=" + NationalMemberID;
cmd.ExecuteNonQuery();
}
}
else
{
// Student was transferred to a different chapter
// Clear the NationalMemberID and paid flag so a new record can be created and set to that number
cmd.CommandText =
"UPDATE NationalMembers SET NationalMemberID=NULL, isPaid=0, isInactive=1 " +
"WHERE NationalMemberID=" + NationalMemberID;
cmd.ExecuteNonQuery();
}
}
#endregion
#region Did not find student by National Student ID
if (!MemberExists)
{
// Attempt to locate student by chapter & name
MemberRows = Members.Select(
"FirstName='" + FirstName + "' AND " +
"LastName='" + LastName + "' AND " +
"ChapterID=" + ChapterRows[0]["ChapterID"].ToString());
#region Found student by chapter & name
if (MemberRows.Length != 0)
{
// Update student by chapter & name
cmd.CommandText =
"UPDATE NationalMembers SET isPaid=1, isInactive=0," +
" GraduatingClass=" + GradYear + "," +
" NationalMemberID=" + NationalMemberID +
" WHERE MemberID=" + MemberRows[0]["MemberID"].ToString();
cmd.ExecuteNonQuery();
}
#endregion
#region Student not found in database
else
{
// Add new student record
cmd.CommandText =
"INSERT INTO NationalMembers (NationalMemberID, ChapterID, isPaid, FirstName, LastName, GraduatingClass) VALUES (" +
NationalMemberID + "," +
ChapterRows[0]["ChapterID"].ToString() + ",1," +
"'" + FirstName + "'," +
"'" + LastName + "'," +
GradYear + ")";
cmd.ExecuteNonQuery();
}
#endregion
}
#endregion
}
else
{
lstResults.Items.Add(
"Cannot update " + FirstName + " " + LastName + ", chapter not found: NationalChapterID=" + NationalChapterID + ", School=" + ImportRow[4]);
}
}
catch {
// Each student must have a valid graduating class before being marked as paid
lstResults.Items.Add(
"Cannot update " + FirstName + " " + LastName + ", graduating class not specified, School=" + ImportRow[4]);
}
#endregion
}
lstResults.Items.Add("** Import complete");
// Remove spurious members who are not National members and are not signed up for any conference events
cmd.CommandText =
"DELETE FROM NationalMembers WHERE MemberID IN" +
"(SELECT DISTINCT M.MemberID" +
" FROM NationalMembers M LEFT JOIN ConferenceMemberEvents CME ON M.MemberID=CME.MemberID " +
"WHERE ISNULL(M.NationalMemberID,0)=0 AND CME.ConferenceID IS NULL)";
// There are too many CME records now, so this takes too long
// I might work on structuring the query to be more efficient at some point...
//cmd.ExecuteNonQuery();
cnn.Close();
//File.Delete(f);
}
}