示例#1
0
        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();
        }
示例#2
0
        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);
            }
        }