using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;

public partial class rl_admins_pg_ClassEdit : System.Web.UI.Page
{

    //*********************************************************
    SqlConnection con;
    SqlCommand cmd;
    SqlTransaction tr;
    static DataTable dt;
    static string lastCoursesQuery = "", lastStudentQuery = "";
    //*********************************************************

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!Page.IsPostBack)
            {
                C_myClass.MenueItemsCreate(User, panelMenues);
                Label lblTitle = (Label)this.Master.FindControl("lblTitle");
                lblTitle.Text = "ویرایش کلاس";
                lastCoursesQuery = "SELECT * FROM [t_Courses] ORDER BY [courseId]";
                int classId = Int32.Parse(Request.QueryString["classId"].ToString());
                string classTitle = "", courseName = "", teacherName = "", status = "";
                int courseId = 0, teacherId = 0;
                List<int> students = new List<int>();
                string conString = RamanConnectionString.LocalConnectionString();
                con = new SqlConnection(conString);
                cmd = con.CreateCommand();
                try
                {
                    con.Open();
                    cmd.CommandText = "select courseId, teacherId, classTitle, status FROM t_Classes Where classId = " + classId;
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            courseId = (rdr.GetValue(0) != DBNull.Value ? rdr.GetInt32(0) : 0);
                            teacherId = (rdr.GetValue(1) != DBNull.Value ? rdr.GetInt32(1) : 0);
                            classTitle = (rdr.GetValue(2) != DBNull.Value ? rdr.GetString(2) : "");
                            status = (rdr.GetValue(3) != DBNull.Value ? rdr.GetString(3) : "");
                        }
                    }
                    rdr.Close();
                    //////////////////////////// students :
                    cmd.CommandText = "select studentId from t_ClassStudents Where classId = " + classId;
                    SqlDataReader rdr2 = cmd.ExecuteReader();
                    if (rdr2.HasRows)
                    {
                        while (rdr2.Read())
                        {
                            int studentId = (rdr2.GetValue(0) != DBNull.Value ? rdr2.GetInt32(0) : 0);
                            students.Add(studentId);
                        }
                    }
                    rdr2.Close();
                    //////////////////////////// fill text boxes : 
                    courseName = _UseFull.C_Usefull.FindStringWithIntegerShart(con, cmd, "t_Courses", "courseName", "courseId", courseId);
                    teacherName = _UseFull.C_Usefull.FindStringWithIntegerShart(con, cmd, "t_Teachers", "fullName", "teacherId", teacherId);
                    tbClassTitle.Text = classTitle;
                    tbCourseId.Text = courseId.ToString();
                    tbCourseName.Text = courseName;
                    tbTeacherId.Text = teacherId.ToString();
                    tbTeacherName.Text = teacherName;
                    checkStatus.Checked = ( status == "جاری" ? true : false);
                    ///////////////////////////////// fill GridView : 
                    dt = new DataTable();
                    dt.Columns.Add("studentId");
                    dt.Columns.Add("fullName");
                    dt.Columns.Add("tahsilat");
                    dt.Columns.Add("dateOfBirth");
                    dt.Columns.Add("userName");
                    for (int i = 0; i < students.Count; i++)
                    {
                        Hashtable info = FindStudentInfo(students[i]);
                        DataRow dr = dt.NewRow();
                        dr["studentId"] = info["studentId"].ToString();
                        dr["fullName"] = info["fullName"].ToString();
                        dr["tahsilat"] = info["tahsilat"].ToString();
                        dr["dateOfBirth"] = info["dateOfBirth"].ToString();
                        dr["userName"] = info["userName"].ToString();
                        dt.Rows.Add(dr);
                    }
                    GridView2.DataSource = dt;
                    GridView2.DataBind();
                    ////////////////////////////////
                    lastStudentQuery = "SELECT t_Students.studentId, t_Students.fullName, t_Students.gender, t_Students.tahsilat, t_Students.dateOfBirth, t_Students.melliCode, t_Students.studentPhotoUrl, t_Students.userName, t_Students.userCreater, t_Registers.courseId FROM t_Students INNER JOIN t_Registers ON t_Students.studentId = t_Registers.studentId WHERE (t_Registers.courseId = " + courseId + " AND t_Registers.regStatus = N'تأیید'" + ")";
                }
                catch (Exception ex)
                {
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = ex.Message;
                }
                finally
                {
                    if (con.State != ConnectionState.Closed)
                        con.Close();
                }
            }
        }
        catch
        {
        }
    }

    private Hashtable FindStudentInfo(int StudentId)
    {
        Hashtable info = new Hashtable();
        string conStrin = RamanConnectionString.LocalConnectionString();
        con = new SqlConnection(conStrin);
        SqlCommand cmd = con.CreateCommand();
        cmd.CommandText = "select studentId, fullName, tahsilat, dateOfBirth, userName FROM t_Students Where studentId = " + StudentId;
        try
        {
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    int studentId = (rdr.GetValue(0) != DBNull.Value ? rdr.GetInt32(0) : 0);
                    string fullName = (rdr.GetValue(1) != DBNull.Value ? rdr.GetString(1) : "");
                    string tahsilat = (rdr.GetValue(2) != DBNull.Value ? rdr.GetString(2) : "");
                    string dateOfBirth = (rdr.GetValue(3) != DBNull.Value ? rdr.GetString(3) : "");
                    string userName = (rdr.GetValue(4) != DBNull.Value ? rdr.GetString(4) : "");
                    info["studentId"] = studentId;
                    info["fullName"] = fullName;
                    info["tahsilat"] = tahsilat;
                    info["dateOfBirth"] = dateOfBirth;
                    info["userName"] = userName;
                }
            }
            rdr.Close();
        }
        catch
        {
        }
        finally
        {
            if (con.State != ConnectionState.Closed)
                con.Close();
        }
        return info;
    }

    protected void btnBack_Click(object sender, EventArgs e)
    {
        try
        {
            string returnUrl = Request.QueryString["returnUrl"].ToString();
            Response.Redirect(returnUrl);
        }
        catch
        {
        }
    }

    protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            GridView2.PageIndex = e.NewPageIndex;
            GridView2.DataSource = dt;
            GridView2.DataBind();
        }
        catch
        {
        }
    }

    protected void btnOk_Click(object sender, EventArgs e)
    {
        try
        {
            if ((tbClassTitle.Text.Length > 0) && (tbCourseId.Text.Length > 0) && (tbTeacherId.Text.Length > 0))
            {
                int courseId = Int32.Parse(tbCourseId.Text);
                int teacherId = Int32.Parse(tbTeacherId.Text);
                string classTitle = tbClassTitle.Text;
                string status = (checkStatus.Checked ? "جاری" : "گذشته");
                List<int> students = FindGridStudents();
                //////////////////////////
                
                string conString = RamanConnectionString.LocalConnectionString();
                con = new SqlConnection(conString);
                cmd = con.CreateCommand();
                try
                {
                    con.Open();
                    tr = con.BeginTransaction();
                    cmd.Transaction = tr;
                    int classId = Int32.Parse(Request.QueryString["classId"].ToString());
                    lblMessage.Text = classId.ToString();
                    
                    cmd.CommandText = "update t_Classes SET " +
                        "courseId = " + courseId + " , " +
                        "teacherId = " + teacherId + " , " +
                        "classTitle = N'" + classTitle + "' , " +
                        "status = N'" + status + "' Where classId = " + classId;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "delete from t_ClassStudents Where classId = " + classId;
                    cmd.ExecuteNonQuery();
                    for (int i = 0; i < students.Count; i++)
                    {
                        int studentId = students[i];
                        cmd.CommandText = "insert into t_ClassStudents (classId, studentId) values (" +
                            classId + " , " +
                            studentId + ")";
                        cmd.ExecuteNonQuery();
                    }
                    tr.Commit();
                    lblMessage.ForeColor = System.Drawing.Color.Green;
                    lblMessage.Text = "با موفقیت ذخیره شد.";
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = ex.Message;
                }
                finally
                {
                    if (con.State != ConnectionState.Closed)
                        con.Close();
                }
                
            }
            else
            {
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Text = "عنوان کلاس ، دوره و مدرس الزامی می باشند.";
            }
        }
        catch(Exception exc)
        {
            lblMessage.ForeColor = System.Drawing.Color.Red;
            lblMessage.Text = exc.Message;
        }
    }

    private List<int> FindGridStudents()
    {
        List<int> students = new List<int>();
        for (int i = 0; i < GridView2.Rows.Count; i++)
        {
            DropDownList cmbOp = (DropDownList)GridView2.Rows[i].FindControl("cmbOp");
            string op = cmbOp.SelectedItem.ToString();
            if (op == "ثبت")
            {
                int studentId = Int32.Parse(GridView2.Rows[i].Cells[1].Text);
                students.Add(studentId);
            }
        }
        return students;
    }

    protected void btnAddCourse_Click(object sender, EventArgs e)
    {
        try
        {
            lastCoursesQuery = "SELECT * FROM [t_Courses] ORDER BY [courseId]";
            SqlDataSource1.SelectCommand = lastCoursesQuery;
            GridCourses.DataBind();
            mpeCourses.Show();
        }
        catch
        {
        }
    }

    protected void btnAllShow_Click(object sender, EventArgs e)
    {
        try
        {
            lastCoursesQuery = "SELECT * FROM [t_Courses] ORDER BY [courseId]";
            SqlDataSource1.SelectCommand = lastCoursesQuery;
            GridCourses.DataBind();
            tbSearch.Text = "";
            mpeCourses.Show();
        }
        catch
        {
        }
    }

    protected void btnTeacherSelect_Click(object sender, EventArgs e)
    {
        try
        {
            GridTeachers.DataBind();
            mpeTeachers.Show();
        }
        catch
        {

        }
    }

    protected void btnAddStudent_Click(object sender, EventArgs e)
    {
        try
        {
            SqlDataSource2.SelectCommand = (lastStudentQuery.Length > 0 ? lastStudentQuery : SqlDataSource2.SelectCommand);
            GridStudents.DataBind();
            mpeStudents.Show();
        }
        catch
        {
        }
    }

    protected void GridCourses_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        try
        {
            if (e.CommandName == "SelectPress")
            {
                int courseId = Int32.Parse(e.CommandArgument.ToString());
                string conSt = RamanConnectionString.LocalConnectionString();
                con = new SqlConnection(conSt);
                SqlCommand cmd = con.CreateCommand();
                try
                {
                    con.Open();
                    string courseName = _UseFull.C_Usefull.FindStringWithIntegerShart(con, cmd, "t_Courses", "courseName", "courseId", courseId);
                    tbCourseId.Text = courseId.ToString();
                    tbCourseName.Text = courseName;
                    lastStudentQuery = "SELECT t_Students.studentId, t_Students.fullName, t_Students.gender, t_Students.tahsilat, t_Students.dateOfBirth, t_Students.melliCode, t_Students.studentPhotoUrl, t_Students.userName, t_Students.userCreater, t_Registers.courseId FROM t_Students INNER JOIN t_Registers ON t_Students.studentId = t_Registers.studentId WHERE (t_Registers.courseId = " + courseId + " AND t_Registers.regStatus = N'تأیید'" + ")";
                    mpeCourses.Hide();
                }
                catch
                {
                }
                finally
                {
                    if (con.State != ConnectionState.Closed)
                        con.Close();
                }
            }
        }
        catch
        {
        }
    }

    protected void GridCourses_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            GridCourses.PageIndex = e.NewPageIndex;
            SqlDataSource1.SelectCommand = lastCoursesQuery;
            GridCourses.DataBind();
            tbSearch.Text = "";
            mpeCourses.Show();
        }
        catch
        {
        }
    }

    protected void GridTeachers_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        try
        {
            if (e.CommandName == "SelectTeacher")
            {
                int teacherId = Int32.Parse(e.CommandArgument.ToString());
                string conSt = RamanConnectionString.LocalConnectionString();
                con = new SqlConnection(conSt);
                SqlCommand cmd = con.CreateCommand();
                try
                {
                    con.Open();
                    string fullName = _UseFull.C_Usefull.FindStringWithIntegerShart(con, cmd, "t_Teachers", "fullName", "teacherId", teacherId);
                    tbTeacherId.Text = teacherId.ToString();
                    tbTeacherName.Text = fullName;
                    mpeTeachers.Hide();
                }
                catch
                {
                }
                finally
                {
                    if (con.State != ConnectionState.Closed)
                        con.Close();
                }
            }
        }
        catch
        {
        }
    }

    protected void GridTeachers_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            GridTeachers.PageIndex = e.NewPageIndex;
            GridTeachers.DataBind();
        }
        catch
        {
        }
    }

    protected void GridStudents_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        try
        {
            if (e.CommandName == "SelectStudentPress")
            {
                int studentId = Int32.Parse(e.CommandArgument.ToString());
                lblstuerror.ForeColor = System.Drawing.Color.White;
                lblstuerror.Text = studentId.ToString();
                if (!IsInList(studentId))
                {
                    Hashtable studentInfo = FindStudentInfo(studentId);
                    DataRow dr = dt.NewRow();
                    dr["studentId"] = studentInfo["studentId"].ToString();
                    dr["fullName"] = studentInfo["fullName"].ToString();
                    dr["tahsilat"] = studentInfo["tahsilat"].ToString();
                    dr["dateOfBirth"] = studentInfo["dateOfBirth"].ToString();
                    dr["userName"] = studentInfo["userName"].ToString();
                    dt.Rows.Add(dr);
                    GridView2.DataSource = dt;
                    int rowCount = dt.Rows.Count;
                    if (rowCount > 10)
                    {
                        int mod = rowCount % 10;
                        if (mod == 0)
                        {
                            GridView2.PageIndex = rowCount / 10;
                        }
                        else if (mod > 0)
                        {
                            GridView2.PageIndex = (rowCount / 10) + 1;
                        }
                    }
                    GridView2.DataBind();
                    UpdatePanel2.Update();
                }
                mpeStudents.Hide();
            }
        }
        catch(Exception ex)
        {
            lblstuerror.ForeColor = System.Drawing.Color.White;
            lblstuerror.Text = ex.Message;
        }
    }

    private bool IsInList(int StudentId)
    {
        bool res = false;
        try
        {
            for (int i = 0; i < GridView2.Rows.Count; i++)
            {
                int stuId = Int32.Parse(GridView2.Rows[i].Cells[1].Text);
                if (StudentId == stuId)
                    res = true;
            }
        }
        catch
        {
        }
        return res;
    }

    protected void GridStudents_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            GridStudents.PageIndex = e.NewPageIndex;
            SqlDataSource2.SelectCommand = lastStudentQuery;
            GridStudents.DataBind();
        }
        catch
        {
        }
    }
    protected void btnStudentsCancel_Click(object sender, EventArgs e)
    {
        try
        {
            mpeStudents.Hide();
        }
        catch
        {
        }
    }
}
