protected void Page_Load(object sender, EventArgs e) { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); list_Eqrthquake.InnerText = appPro.GetApplicationString(BaseInfo.EarthquakeName); list_Bridge.InnerText = appPro.GetApplicationString(BaseInfo.BridgeName); list_Film.InnerText = appPro.GetApplicationString(BaseInfo.FilmName); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Session["LogIn"] == null || Session["LogIn"].ToString() != "Y") { Response.Redirect("Login.aspx"); } Session["IsFirstSubmit"] = "Y"; //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); lab_Title.InnerText = appPro.GetApplicationString(BaseInfo.EarthquakeName) + "報名表"; AddTeamCount(1); AddTeamCount(2); AddTeamCount(3); } List <string> keys = Request.Form.AllKeys.Where(key => key.Contains("input_Name")).ToList(); int i = 1; foreach (string key in keys) { this.AddTeamCount(i); i++; } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Session["Login"] != null && Session["Login"].ToString() == "Y") { InitLoad(); } else if (Session["ManageLogin"] != null && Session["ManageLogin"].ToString() == "Y") { InitLoad(); } else { Response.Redirect("Login.aspx"); } //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); lab_Title.InnerText = appPro.GetApplicationString(BaseInfo.BridgeName) + "報名資訊"; } List <string> keys = Request.Form.AllKeys.Where(key => key.Contains("input_Name")).ToList(); int i = 1; foreach (string key in keys) { this.AddTeamCount(i); i++; } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); lab_Title.InnerText = appPro.GetApplicationString(BaseInfo.BridgeName) + "報名名單"; LoadInitSelect(); LoadTeamListBySelected(); string strConn = ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString; SqlConnection conn = new SqlConnection(strConn); conn.Open(); SqlCommand command = new SqlCommand($"SELECT COUNT(*) AS Count FROM BridgeTeam WHERE CreateDate " + appPro.GetBetweenSignUpTime(), conn); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { lab_Count.InnerText = dr["Count"].ToString() + "隊"; } dr.Close(); command.Cancel(); conn.Close(); } //每次頁面重新彙整都會重新更新隊伍資訊 p_UpdateTime.InnerText = $"報名隊伍清單更新時間:{DateTime.Now.ToString("yyyy-MM-dd tt hh:mm:ss")}"; }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); lab_LoginTitle.InnerText = Convert.ToDateTime(DateTime.Now).ToString("yyyy") + $" " + appPro.GetApplicationString(BaseInfo.GameNumber) + $"抗震盃報名系統"; } }
protected void Page_Load(object sender, EventArgs e) { //讀抗震大作戰的隊伍資訊 if (!IsPostBack) { if (Session["Login"] != null && Session["Login"].ToString() == "Y") { LoadAccountInfo(); } else { Response.Redirect("Login.aspx"); } //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); DateTime startTime = Convert.ToDateTime(appPro.GetApplicationString(BaseInfo.StartSignUp)); DateTime endTime = Convert.ToDateTime(appPro.GetApplicationString(BaseInfo.EndSignUp)); if (!(DateTime.Now >= startTime && DateTime.Now <= endTime)) { btn_NewTeam.Enabled = false; to_update_Lunch.Attributes.Add("disabled", "true"); } a_Earthquake.InnerText = appPro.GetApplicationString(BaseInfo.EarthquakeName); a_Bridge.InnerText = appPro.GetApplicationString(BaseInfo.BridgeName); a_Film.InnerText = appPro.GetApplicationString(BaseInfo.FilmName); } if (a_Earthquake.Attributes["class"].Contains("active")) { LoadTeamByAccount(TeamType.Earthquake); } if (a_Bridge.Attributes["class"].Contains("active")) { LoadTeamByAccount(TeamType.Bridge); } if (a_Film.Attributes["class"].Contains("active")) { LoadTeamByAccount(TeamType.Film); } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Session["LogIn"] == null || Session["LogIn"].ToString() != "Y") { Response.Redirect("Login.aspx"); } Session["IsFirstSubmit"] = "Y"; //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); lab_Title.InnerText = appPro.GetApplicationString(BaseInfo.FilmName) + "報名表"; LoadInitTeam(); } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Session["Login"] != null && Session["Login"].ToString() == "Y") { InitLoad(); } else if (Session["ManageLogin"] != null && Session["ManageLogin"].ToString() == "Y") { InitLoad(); } else { Response.Redirect("Login.aspx"); } //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); lab_Title.InnerText = appPro.GetApplicationString(BaseInfo.FilmName) + "報名資訊"; } }
//確認資料後允許報名 public bool CheckRegistrationData() { if (Session["IsFirstSubmit"] == null || Session["IsFirstSubmit"].ToString() == "N") { return(true); } string errMes = ""; int mainCount = 1; //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); //隊名要填 if (input_TeamName.Value == "") { errMes += $"<p>{mainCount}. 請填寫隊名</p>"; mainCount++; } else { //有填要確定有沒有重複的隊名 SqlConnection connect = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); connect.Open(); SqlCommand comm = new SqlCommand($"SELECT Name FROM EarthquakeTeam WHERE Name = '{input_TeamName.Value}' " + $"AND CreateDate " + appPro.GetBetweenSignUpTime() + $";", connect); SqlDataReader theDr = comm.ExecuteReader(); if (theDr.HasRows) { errMes += $"<p>{mainCount}. 本年度比賽已存在相同隊名之隊伍,請更換隊名!</p>"; mainCount++; } else { theDr.Close(); comm.Cancel(); comm = new SqlCommand($"SELECT Name FROM EarthquakeTeam WHERE Name = '{input_TeamName.Value}' " + $"AND CreateDate " + appPro.GetBetweenSignUpTime() + $";", connect); theDr = comm.ExecuteReader(); if (theDr.HasRows) { errMes += $"<p>{mainCount}. 本年度比賽已存在相同隊名之隊伍,請更換隊名!</p>"; mainCount++; } } theDr.Close(); comm.Cancel(); connect.Close(); } int count = (Request.Form.AllKeys.Where(key => key.Contains("input_Name")).ToList()).Count; bool hasLeader = false; for (int i = 1; i <= count; i++) { HtmlInputRadioButton btn = Master.FindControl("MainContent").FindControl("radioBtn_" + i) as HtmlInputRadioButton; HtmlInputText nameControl = Master.FindControl("MainContent").FindControl("input_Name" + i) as HtmlInputText; HtmlInputText idControl = Master.FindControl("MainContent").FindControl("input_Id" + i) as HtmlInputText; HtmlInputText BirthdayControl = Master.FindControl("MainContent").FindControl("input_Birthday" + i) as HtmlInputText; //不是需要判別的控制項 if (btn.Checked) { hasLeader = true; } //判斷隊伍資訊 string memberError = $"<p>{mainCount}. 請確認隊員{count}資訊:</p>"; //判斷名字有沒有寫 if (nameControl.Value == "") { memberError += $"<p style=\"margin-left: 10px; margin-top: 0px;\">.姓名不可留空!</p>"; } //判斷身份證字號 if (!Regex.IsMatch(idControl.Value, @"([A-Z]|[a-z])\d{9}")) { memberError += $"<p style=\"margin-left: 10px; margin-top: 0px;\">.身分證字號不符合規定!</p>"; } //判斷生日 if (!Regex.IsMatch(BirthdayControl.Value, @"^[1-9]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$")) { memberError += $"<p style=\"margin-left: 10px; margin-top: 0px;\">.生日不符合規定!</p>"; } if (memberError != $"<p>{mainCount}. 請確認隊員{count}資訊:</p>") { errMes += memberError; mainCount++; } } //確認有沒有隊長 if (!hasLeader) { errMes += $"<p>{mainCount}. 請選擇一位隊長!</p>"; mainCount++; } //確認是否可以新增隊伍 //橋樑每個學校只能一隊 string accountSchoolId = ""; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand command = new SqlCommand($"SELECT SchoolID FROM Account WHERE Id = '{Session["LoginId"]}';", conn); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { accountSchoolId = dr["SchoolID"].ToString(); } dr.Close(); command.Cancel(); command = new SqlCommand($"SELECT Account.Name FROM BridgeTeam LEFT JOIN Account ON BridgeTeam.AccountID = Account.Id" + $" WHERE Account.SchoolID = {accountSchoolId}" + $"AND BridgeTeam.CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = command.ExecuteReader(); if (dr.HasRows) { //有隊伍就不能新增 errMes = $"不好意思,本帳號所屬學校已報名一隊" + appPro.GetApplicationString(BaseInfo.BridgeName) + $"隊伍,不得再進行本賽程報名!"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "closepup", "$('#Modal_ErrMsg').modal('show');", true); } if (errMes == "") { return(true); } else { Modal_Body.InnerHtml = errMes; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "closepup", "$('#Modal_ErrMsg').modal('show');", true); return(false); } }
public void LoadTeamListBySelected() { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); string strConn = ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString; SqlConnection conn = new SqlConnection(strConn); conn.Open(); SqlCommand command; SqlDataReader dr; //確認符合的老師帳號 string queryCommand = $"SELECT Account.Id AS AccountId, Account.Name AS TeacherName, School.Name AS SchoolName FROM Account LEFT JOIN School on Account.SchoolID = School.Id "; if (select_Teacher.Items[select_Teacher.SelectedIndex].Text != "All") { queryCommand += $"WHERE Account.Name = '{select_Teacher.Items[select_Teacher.SelectedIndex].Text}'"; } command = new SqlCommand(queryCommand + ";", conn); dr = command.ExecuteReader(); //紀錄帳戶相關資訊 Dictionary <string, string> TeacherIdToSchool = new Dictionary <string, string>(); Dictionary <string, string> TeacherIdToName = new Dictionary <string, string>(); while (dr.Read()) { IDataRecord data = (IDataRecord)dr; TeacherIdToSchool.Add(data["AccountId"].ToString(), data["SchoolName"].ToString()); TeacherIdToName.Add(data["AccountId"].ToString(), data["TeacherName"].ToString()); } dr.Close(); command.Cancel(); div_TeamCard.InnerHtml = ""; //讀隊伍, 然後把隊伍加進去Card foreach (string teacherId in TeacherIdToName.Keys) { command = new SqlCommand($"SELECT Name, TeamType FROM FilmInfo WHERE AccountID = {teacherId} AND CreateDate " + appPro.GetBetweenSignUpTime() + $";", conn); dr = command.ExecuteReader(); while (dr.Read()) { IDataRecord data = (IDataRecord)dr; string name = data["Name"].ToString(); string teamType = data["TeamType"].ToString(); string innerHtmltext = "<div class=\"card\" style=\"margin-bottom: 2%; display: inline-block;\">" + "<div class=\"card-body text-left\">" + "<h5 class=\"card-title\">" + name + "</h5>" + "<h6 class=\"card-subtitle mb-2 text-muted\">" + TeacherIdToSchool[teacherId] + "</h6>" + "<p class=\"card-subtitle mb-2 text-muted\">指導老師:" + TeacherIdToName[teacherId] + "</p>" + "<a href=\"#\" class=\"card-link\">比賽組別:"; if (teamType == "Earthquake") { innerHtmltext += appPro.GetApplicationString(BaseInfo.EarthquakeName); } else { innerHtmltext += appPro.GetApplicationString(BaseInfo.BridgeName); } innerHtmltext += "</a></div></div>"; div_TeamCard.InnerHtml += innerHtmltext; } dr.Close(); command.Cancel(); } conn.Close(); }
public void LoadInitTeam() { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); List <string> earList = new List <string>(); List <string> briList = new List <string>(); //先抓抗震的 SqlCommand command = new SqlCommand($"SELECT Name FROM EarthquakeTeam WHERE AccountID = {Session["LoginId"].ToString()}" + $" AND CreateDate " + appPro.GetBetweenSignUpTime(), conn); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { earList.Add(dr["Name"].ToString()); } dr.Close(); command.Cancel(); //抓橋梁的 command = new SqlCommand($"SELECT Name FROM BridgeTeam WHERE AccountID = {Session["LoginId"].ToString()}" + $" AND CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = command.ExecuteReader(); while (dr.Read()) { briList.Add(dr["Name"].ToString()); } dr.Close(); command.Cancel(); //確認微電影報了沒有 command = new SqlCommand($"SELECT Name, TeamType FROM FilmInfo WHERE AccountID = {Session["LoginId"].ToString()}" + $" AND CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = command.ExecuteReader(); while (dr.Read()) { if (dr["TeamType"].ToString() == "Earthquake") { earList.Remove(dr["Name"].ToString()); } else { briList.Remove(dr["Name"].ToString()); } } dr.Close(); command.Cancel(); conn.Close(); foreach (string team in earList) { select_Team.Items.Add(appPro.GetApplicationString(BaseInfo.EarthquakeName) + $"|{team}"); } foreach (string team in briList) { select_Team.Items.Add(appPro.GetApplicationString(BaseInfo.BridgeName) + $"|{team}"); } }
//確認資料後允許報名 public bool CheckRegistrationData() { if (Session["IsFirstSubmit"] == null || Session["IsFirstSubmit"].ToString() == "N") { return(true); } string errMes = ""; int mainCount = 1; //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); //隊名要選 if (select_Team.Items[select_Team.SelectedIndex].Text == "請選擇隊伍") { errMes += $"<p>{mainCount}. 請填寫隊名!</p>"; mainCount++; } else if (select_Team.Items[select_Team.SelectedIndex].Text == "") { errMes += $"<p>{mainCount}. 請填寫隊名!</p>"; mainCount++; } //設計理念要填 if (text_Design.Value == "") { errMes += $"<p>{mainCount}. 請填寫設計理念!</p>"; mainCount++; } //故事大綱要填 if (text_Design.Value == "") { errMes += $"<p>{mainCount}. 請填寫故事大綱!</p>"; mainCount++; } //確認選取的隊伍是不是被搶報了 string[] teamInfo = select_Team.Items[select_Team.SelectedIndex].Text.Split('|'); string teamName = ""; for (int i = 1; i < teamInfo.Count(); i++) { teamName += teamInfo[i]; } SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand command = new SqlCommand($"SELECT Name FROM FilmInfo WHERE " + $"Name = '{teamName}' AND CreateDate " + appPro.GetBetweenSignUpTime() + $";", conn); SqlDataReader dr = command.ExecuteReader(); if (dr.HasRows) { //該隊伍已經報名了 errMes = $"不好意思,該隊伍已報名" + appPro.GetApplicationString(BaseInfo.FilmName) + $"賽程!"; } dr.Close(); command.Cancel(); conn.Close(); if (errMes != "") { Modal_Body.InnerHtml = errMes; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "closepup", "$('#Modal_ErrMsg').modal('show');", true); return(false); } else { return(true); } }
protected void btn_Submit_ServerClick(object sender, EventArgs e) { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); if (!CheckRegistrationData()) { return; } if (Session["IsFirstSubmit"] == null || Session["IsFirstSubmit"].ToString() == "Y") { Session["IsFirstSubmit"] = "N"; } else { Session["IsFirstSubmit"] = null; Response.Redirect("Intro.aspx"); } string commandString = ""; string[] teamInfo = select_Team.Items[select_Team.SelectedIndex].Text.Split('|'); string teamName = ""; string teamType = teamInfo[0]; for (int i = 1; i < teamInfo.Count(); i++) { teamName += teamInfo[i]; } if (teamType == appPro.GetApplicationString(BaseInfo.EarthquakeName)) { teamType = "Earthquake"; } else { teamType = "Bridge"; } if (input_Link.Value == "") { commandString = $"INSERT INTO FilmInfo (Name, DesignConcept, Outline, AccountID, TeamType)" + $" VALUES('{teamName}'," + $"'{text_Design.InnerText}'," + $"'{text_Outline.InnerText}'," + $"'{Session["LoginId"]}'," + $"'{teamType}')"; } else { commandString = $"INSERT INTO FilmInfo (Name, DesignConcept, Outline, AccountID, TeamType, FileLink)" + $" VALUES('{select_Team.Items[select_Team.SelectedIndex].Text}'," + $"'{text_Design.InnerText}'," + $"'{text_Outline.InnerText}'," + $"'{Session["LoginId"]}'," + $"'{select_Team.Items[select_Team.SelectedIndex].Value}'," + $"'{input_Link.Value}')"; } SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand command = new SqlCommand(commandString, conn); command.ExecuteNonQuery(); command.Cancel(); conn.Close(); Session["IsFirstSubmit"] = null; Response.Redirect("Intro.aspx"); }
protected void btn_ExportExcel_Click(object sender, EventArgs e) { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand comm; SqlDataReader dr; //Create an instance of ExcelEngine using (ExcelEngine excelEngine = new ExcelEngine()) { //Set the default application version as Excel 2016 excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //Create a workbook with a worksheet IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); //抗震大作戰Sheet //Access first worksheet from the workbook instance IWorksheet earthquakeWorksheet = workbook.Worksheets[0]; earthquakeWorksheet.Name = appPro.GetApplicationString(BaseInfo.EarthquakeName) + "報名資訊"; //標題列 //Insert sample text into cell “A1” earthquakeWorksheet.Range[1, 1].Text = "隊伍編號"; earthquakeWorksheet.Range[1, 2].Text = "隊伍名稱"; earthquakeWorksheet.Range[1, 3].Text = "報名人數"; earthquakeWorksheet.Range[1, 4].Text = "隊長姓名"; earthquakeWorksheet.Range[1, 5].Text = "隊員一姓名"; earthquakeWorksheet.Range[1, 6].Text = "隊員二姓名"; earthquakeWorksheet.Range[1, 7].Text = "隊員三姓名"; earthquakeWorksheet.Range[1, 8].Text = "隊員四姓名"; earthquakeWorksheet.Range[1, 9].Text = "隊員五姓名"; earthquakeWorksheet.Range[1, 10].Text = "帶隊老師"; earthquakeWorksheet.Range[1, 11].Text = "帶隊老師電話"; earthquakeWorksheet.Range[1, 12].Text = "帶隊老師Email"; earthquakeWorksheet.Range[1, 13].Text = "共同指導老師"; earthquakeWorksheet.Range[1, 14].Text = "學校名稱"; earthquakeWorksheet.Range[1, 15].Text = "學校地址"; //匯出資料 comm = new SqlCommand($"SELECT * FROM EarthquakeTeam LEFT JOIN Account ON EarthquakeTeam.AccountID = Account.Id " + $"LEFT JOIN School ON Account.SchoolID = School.Id WHERE EarthquakeTeam.CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = comm.ExecuteReader(); int num = 2; while (dr.Read()) { //匯出每一筆資料 earthquakeWorksheet.Range[num, 1].Text = dr[0].ToString(); earthquakeWorksheet.Range[num, 2].Text = dr[1].ToString(); earthquakeWorksheet.Range[num, 3].Text = dr[2].ToString(); earthquakeWorksheet.Range[num, 4].Text = dr[3].ToString(); earthquakeWorksheet.Range[num, 5].Text = dr[4].ToString(); earthquakeWorksheet.Range[num, 6].Text = dr[5].ToString(); earthquakeWorksheet.Range[num, 7].Text = dr[6].ToString(); earthquakeWorksheet.Range[num, 8].Text = dr[7].ToString(); earthquakeWorksheet.Range[num, 9].Text = dr[8].ToString(); earthquakeWorksheet.Range[num, 10].Text = dr[15].ToString(); earthquakeWorksheet.Range[num, 11].Text = dr[16].ToString(); earthquakeWorksheet.Range[num, 12].Text = dr[17].ToString(); earthquakeWorksheet.Range[num, 13].Text = dr[11].ToString(); earthquakeWorksheet.Range[num, 14].Text = dr[21].ToString(); earthquakeWorksheet.Range[num, 15].Text = dr[22].ToString(); num++; } dr.Close(); comm.Cancel(); //橋梁變變變Sheet //Access 2nd worksheet from the workbook instance IWorksheet bridgeWorksheet = workbook.Worksheets.Create(appPro.GetApplicationString(BaseInfo.BridgeName) + "報名資訊"); //標題列 //Insert sample text into cell “A1” bridgeWorksheet.Range[1, 1].Text = "隊伍編號"; bridgeWorksheet.Range[1, 2].Text = "隊伍名稱"; bridgeWorksheet.Range[1, 3].Text = "報名人數"; bridgeWorksheet.Range[1, 4].Text = "隊長姓名"; bridgeWorksheet.Range[1, 5].Text = "隊長身分證字號"; bridgeWorksheet.Range[1, 6].Text = "隊長生日"; bridgeWorksheet.Range[1, 7].Text = "隊員一姓名"; bridgeWorksheet.Range[1, 8].Text = "隊員一身分證字號"; bridgeWorksheet.Range[1, 9].Text = "隊員一生日"; bridgeWorksheet.Range[1, 10].Text = "隊員二姓名"; bridgeWorksheet.Range[1, 11].Text = "隊員二身分證字號"; bridgeWorksheet.Range[1, 12].Text = "隊員二生日"; bridgeWorksheet.Range[1, 13].Text = "隊員三姓名"; bridgeWorksheet.Range[1, 14].Text = "隊員三身分證字號"; bridgeWorksheet.Range[1, 15].Text = "隊員三生日"; bridgeWorksheet.Range[1, 16].Text = "隊員四姓名"; bridgeWorksheet.Range[1, 17].Text = "隊員四身分證字號"; bridgeWorksheet.Range[1, 18].Text = "隊員四生日"; bridgeWorksheet.Range[1, 19].Text = "帶隊老師"; bridgeWorksheet.Range[1, 20].Text = "帶隊老師電話"; bridgeWorksheet.Range[1, 21].Text = "帶隊老師Email"; bridgeWorksheet.Range[1, 22].Text = "共同指導老師"; bridgeWorksheet.Range[1, 23].Text = "學校名稱"; bridgeWorksheet.Range[1, 24].Text = "學校地址"; //匯出資料 comm = new SqlCommand($"SELECT * FROM BridgeTeam LEFT JOIN Account ON BridgeTeam.AccountID = Account.Id " + $"LEFT JOIN School ON Account.SchoolID = School.Id WHERE BridgeTeam.CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = comm.ExecuteReader(); num = 2; while (dr.Read()) { //匯出每一筆資料 bridgeWorksheet.Range[num, 1].Text = dr[0].ToString(); bridgeWorksheet.Range[num, 2].Text = dr[1].ToString(); bridgeWorksheet.Range[num, 3].Text = dr[2].ToString(); bridgeWorksheet.Range[num, 4].Text = dr[3].ToString(); bridgeWorksheet.Range[num, 5].Text = dr[4].ToString(); bridgeWorksheet.Range[num, 6].Text = dr[5].ToString().Replace("上午 12:00:00", ""); bridgeWorksheet.Range[num, 7].Text = dr[6].ToString(); bridgeWorksheet.Range[num, 8].Text = dr[7].ToString(); bridgeWorksheet.Range[num, 9].Text = dr[8].ToString().Replace("上午 12:00:00", ""); bridgeWorksheet.Range[num, 10].Text = dr[9].ToString(); bridgeWorksheet.Range[num, 11].Text = dr[10].ToString(); bridgeWorksheet.Range[num, 12].Text = dr[11].ToString().Replace("上午 12:00:00", ""); bridgeWorksheet.Range[num, 13].Text = dr[12].ToString(); bridgeWorksheet.Range[num, 14].Text = dr[13].ToString(); bridgeWorksheet.Range[num, 15].Text = dr[14].ToString().Replace("上午 12:00:00", ""); bridgeWorksheet.Range[num, 16].Text = dr[15].ToString(); bridgeWorksheet.Range[num, 17].Text = dr[16].ToString(); bridgeWorksheet.Range[num, 18].Text = dr[17].ToString().Replace("上午 12:00:00", ""); bridgeWorksheet.Range[num, 19].Text = dr[24].ToString(); bridgeWorksheet.Range[num, 20].Text = dr[25].ToString(); bridgeWorksheet.Range[num, 21].Text = dr[26].ToString(); bridgeWorksheet.Range[num, 22].Text = dr[20].ToString(); bridgeWorksheet.Range[num, 23].Text = dr[30].ToString(); bridgeWorksheet.Range[num, 24].Text = dr[31].ToString(); num++; } dr.Close(); comm.Cancel(); //微電影Sheet //Access 3rd worksheet from the workbook instance IWorksheet filmWorksheet = workbook.Worksheets.Create(appPro.GetApplicationString(BaseInfo.FilmName) + "報名資訊"); //標題列 //Insert sample text into cell “A1” filmWorksheet.Range[1, 1].Text = "隊伍編號"; filmWorksheet.Range[1, 2].Text = "隊伍名稱"; filmWorksheet.Range[1, 3].Text = "設計理念"; filmWorksheet.Range[1, 4].Text = "故事大綱"; filmWorksheet.Range[1, 5].Text = "作品連結"; filmWorksheet.Range[1, 6].Text = "隊伍類型"; filmWorksheet.Range[1, 7].Text = "帶隊老師"; //匯出資料 comm = new SqlCommand($"SELECT * FROM FilmInfo LEFT JOIN Account ON FilmInfo.AccountID = Account.Id " + $"WHERE FilmInfo.CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = comm.ExecuteReader(); num = 2; while (dr.Read()) { //匯出每一筆資料 filmWorksheet.Range[num, 1].Text = dr[0].ToString(); filmWorksheet.Range[num, 2].Text = dr[1].ToString(); filmWorksheet.Range[num, 3].Text = dr[2].ToString(); filmWorksheet.Range[num, 4].Text = dr[3].ToString(); filmWorksheet.Range[num, 5].Text = dr[4].ToString(); filmWorksheet.Range[num, 6].Text = dr[7].ToString(); filmWorksheet.Range[num, 7].Text = dr[11].ToString(); num++; } dr.Close(); comm.Cancel(); IWorksheet eatWorksheet = workbook.Worksheets.Create("便當統計"); //標題列 //Insert sample text into cell “A1” eatWorksheet.Range[1, 1].Text = "總葷食便當"; eatWorksheet.Range[1, 2].Text = "總素食便當"; eatWorksheet.Range[1, 4].Text = "帶隊老師"; eatWorksheet.Range[1, 5].Text = "學校名稱"; eatWorksheet.Range[1, 6].Text = "葷食便當"; eatWorksheet.Range[1, 7].Text = "素食便當"; //匯出資料 comm = new SqlCommand($"SELECT * FROM LunchInfo LEFT JOIN Account ON LunchInfo.AccountID = Account.Id " + $"LEFT JOIN School ON Account.SchoolID = School.Id WHERE LunchInfo.CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = comm.ExecuteReader(); int VegLunch_Count = 0; int Lunch_Count = 0; num = 2; while (dr.Read()) { //匯出每一筆資料 eatWorksheet.Range[num, 4].Text = dr[8].ToString(); eatWorksheet.Range[num, 5].Text = dr[14].ToString(); eatWorksheet.Range[num, 6].Text = dr[1].ToString(); eatWorksheet.Range[num, 7].Text = dr[2].ToString(); VegLunch_Count += (int)dr[1]; Lunch_Count += (int)dr[2]; num++; } dr.Close(); comm.Cancel(); //值 eatWorksheet.Range[2, 1].Text = VegLunch_Count.ToString(); eatWorksheet.Range[2, 2].Text = Lunch_Count.ToString(); IWorksheet accountWorkSheet = workbook.Worksheets.Create("帳戶資訊"); //標題列 //Insert sample text into cell “A1” accountWorkSheet.Range[1, 1].Text = "老師名稱"; accountWorkSheet.Range[1, 2].Text = "連絡電話"; accountWorkSheet.Range[1, 3].Text = "Email"; accountWorkSheet.Range[1, 4].Text = "任職學校"; accountWorkSheet.Range[1, 5].Text = "學校地址"; //匯出資料 comm = new SqlCommand($"SELECT * FROM Account LEFT JOIN School ON Account.SchoolID = School.Id ;", conn); dr = comm.ExecuteReader(); num = 2; while (dr.Read()) { //匯出每一筆資料 accountWorkSheet.Range[num, 1].Text = dr[3].ToString(); accountWorkSheet.Range[num, 2].Text = dr[4].ToString(); accountWorkSheet.Range[num, 3].Text = dr[5].ToString(); accountWorkSheet.Range[num, 4].Text = dr[10].ToString(); accountWorkSheet.Range[num, 5].Text = dr[11].ToString(); num++; } dr.Close(); comm.Cancel(); comm.Cancel(); //Save the workbook to disk in xlsx format workbook.SaveAs("Output.xlsx", Response, ExcelDownloadType.Open, ExcelHttpContentType.Excel2016); } }
private void LoadTeamByAccount(TeamType type) { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); div_TeamInfo.InnerHtml = ""; //Loading Team Function //Connect to SQL DB SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand command = new SqlCommand(); switch (type) { case TeamType.Earthquake: command = new SqlCommand($"SELECT Id, Name FROM EarthquakeTeam WHERE AccountID = {Session["LoginId"]} AND (CreateDate " + appPro.GetBetweenSignUpTime() + ")", conn); break; case TeamType.Bridge: command = new SqlCommand($"SELECT Id, Name FROM BridgeTeam WHERE AccountID = {Session["LoginId"]} AND (CreateDate " + appPro.GetBetweenSignUpTime() + ")", conn); break; case TeamType.Film: command = new SqlCommand($"SELECT Id, Name, FileLink FROM FilmInfo WHERE AccountID = {Session["LoginId"]} AND (CreateDate " + appPro.GetBetweenSignUpTime() + $")", conn); break; } SqlDataReader dataReader = command.ExecuteReader(); string innerHtmlStr = ""; if (dataReader.HasRows) { while (dataReader.Read()) { IDataRecord record = (IDataRecord)dataReader; string teamName = record["Name"].ToString(); string teamId = record["Id"].ToString(); bool hasUpdate = DateTime.Now <= Convert.ToDateTime(appPro.GetApplicationString(BaseInfo.EndUpdateInfo)); bool hasUpdateLink = DateTime.Now <= Convert.ToDateTime(appPro.GetApplicationString(BaseInfo.EndFilmUpdate)); if (type == TeamType.Film) { if (record["FileLink"].ToString() == "") { AddFilmTeamCard(teamName, teamId, div_TeamInfo, false, hasUpdate, hasUpdateLink); } else { AddFilmTeamCard(teamName, teamId, div_TeamInfo, true, hasUpdate, hasUpdateLink); } } else { AddTeamCard(type, teamName, teamId, div_TeamInfo, hasUpdate); } } } else { innerHtmlStr += "<div class=\"card\">" + "<div class=\"card-body\" style=\"color: dimgray; text-align: center; \">" + "<div class=\"row\">" + "<div class=\"col-12\" style=\"margin-top: 5px; \">" + "<h6>尚未開始報名</h6>" + "</div></div></div></div>"; div_TeamInfo.InnerHtml = innerHtmlStr; } command.Cancel(); dataReader.Close(); conn.Close(); }
private void LoadAccountInfo() { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand command = new SqlCommand($"SELECT * FROM Account WHERE Id = {Session["LoginId"]};", conn); SqlDataReader reader = command.ExecuteReader(); int schoolid = 0; num_Lunch.Visible = false; num_VegLunch.Visible = false; id_Lunch.Visible = false; while (reader.Read()) { IDataRecord record = (IDataRecord)reader; user_Name.InnerText = record["Name"].ToString(); user_Phone.InnerText = record["Phone"].ToString(); updatePhone.Value = record["Phone"].ToString(); user_Email.InnerText = record["Email"].ToString(); updateEmail.Value = record["Email"].ToString(); schoolid = Convert.ToInt32(record["SchoolID"]); } reader.Close(); ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); DateTime startTime = Convert.ToDateTime(appPro.GetApplicationString(BaseInfo.StartSignUp)); DateTime endTime = Convert.ToDateTime(appPro.GetApplicationString(BaseInfo.EndSignUp)); command = new SqlCommand($"SELECT * FROM LunchInfo WHERE AccountID = {Session["LoginId"]}", conn); reader = command.ExecuteReader(); while (reader.Read()) { IDataRecord record = (IDataRecord)reader; DateTime dt = Convert.ToDateTime(record["CreateDate"]); if ((dt >= startTime) && (dt <= endTime)) { Lunch.InnerText = $"便當數量 (葷): " + record["Lunch"].ToString(); VegLunch.InnerText = $"便當數量 (素): " + record["VegLunch"].ToString(); text_uLunch.Value = record["Lunch"].ToString(); text_uVegLunch.Value = record["VegLunch"].ToString(); num_Lunch.InnerText = record["Lunch"].ToString(); num_VegLunch.InnerText = record["VegLunch"].ToString(); id_Lunch.InnerText = record["Id"].ToString(); } } reader.Close(); command = new SqlCommand($"SELECT Name FROM School WHERE Id = {schoolid}", conn); reader = command.ExecuteReader(); while (reader.Read()) { IDataRecord record = (IDataRecord)reader; user_School.InnerText = record["Name"].ToString(); } reader.Close(); command.Cancel(); conn.Close(); }
protected void btn_NewTeam_Click(object sender, EventArgs e) { //讀取Application Data ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); if (a_Earthquake.Attributes["class"].Contains("active")) { //確認是否可以新增隊伍 //抗震是一校六隊 string accountSchoolId = ""; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand command = new SqlCommand($"SELECT SchoolID FROM Account WHERE Id = '{Session["LoginId"]}';", conn); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { accountSchoolId = dr["SchoolID"].ToString(); } dr.Close(); command.Cancel(); command = new SqlCommand($"SELECT Count(*) AS Count FROM EarthquakeTeam LEFT JOIN Account ON EarthquakeTeam.AccountID = Account.Id" + $" WHERE Account.SchoolID = {accountSchoolId}" + $"AND EarthquakeTeam.CreateDate {appPro.GetBetweenSignUpTime()}", conn); dr = command.ExecuteReader(); while (dr.Read()) { if (Convert.ToInt32(dr["Count"]) == 6) { //有隊伍就不能新增 MsgBox_Data.InnerHtml = $"<p>帳號所屬學校已報名六隊" + appPro.GetApplicationString(BaseInfo.EarthquakeName) + $"隊伍,不得再進行本賽程報名!</p>"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "closepup", "$('#MsgBox').modal('show');", true); LoadTeamByAccount(TeamType.Bridge); } else { //去填抗震盃報名資料 Response.Redirect("EarthquakeRegistration.aspx"); } } command.Cancel(); dr.Close(); conn.Close(); } else if (a_Bridge.Attributes["class"].Contains("active")) { //確認是否可以新增隊伍 //橋樑每個學校只能一隊 string accountSchoolId = ""; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); SqlCommand command = new SqlCommand($"SELECT SchoolID FROM Account WHERE Id = '{Session["LoginId"]}';", conn); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { accountSchoolId = dr["SchoolID"].ToString(); } dr.Close(); command.Cancel(); command = new SqlCommand($"SELECT Account.Name FROM BridgeTeam LEFT JOIN Account ON BridgeTeam.AccountID = Account.Id" + $" WHERE Account.SchoolID = {accountSchoolId}" + $"AND BridgeTeam.CreateDate {appPro.GetBetweenSignUpTime()}", conn); dr = command.ExecuteReader(); if (dr.HasRows) { //有隊伍就不能新增 MsgBox_Data.InnerHtml = $"<p>帳號所屬學校已報名一隊" + appPro.GetApplicationString(BaseInfo.BridgeName) + $"隊伍,不得再進行本賽程報名!</p>"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "closepup", "$('#MsgBox').modal('show');", true); LoadTeamByAccount(TeamType.Bridge); } else { //去填橋梁變變變資料 Response.Redirect("BridgeRegistration.aspx"); } dr.Close(); command.Cancel(); conn.Close(); } else { //去填微電影資料 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString); conn.Open(); List <string> earList = new List <string>(); List <string> briList = new List <string>(); //先抓抗震的 SqlCommand command = new SqlCommand($"SELECT Name FROM EarthquakeTeam WHERE AccountID = {Session["LoginId"].ToString()}" + $" AND CreateDate " + appPro.GetBetweenSignUpTime(), conn); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { earList.Add(dr["Name"].ToString()); } dr.Close(); command.Cancel(); //抓橋梁的 command = new SqlCommand($"SELECT Name FROM BridgeTeam WHERE AccountID = {Session["LoginId"].ToString()}" + $" AND CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = command.ExecuteReader(); while (dr.Read()) { briList.Add(dr["Name"].ToString()); } dr.Close(); command.Cancel(); //確認微電影報了沒有 command = new SqlCommand($"SELECT Name, TeamType FROM FilmInfo WHERE AccountID = {Session["LoginId"].ToString()}" + $" AND CreateDate " + appPro.GetBetweenSignUpTime(), conn); dr = command.ExecuteReader(); while (dr.Read()) { if (dr["TeamType"].ToString() == "Eqrthquake") { earList.Remove(dr["Name"].ToString()); } else { briList.Remove(dr["Name"].ToString()); } } dr.Close(); command.Cancel(); if (earList.Count == 0 && briList.Count == 0) { MsgBox_Data.InnerHtml = $"<p>帳號中" + appPro.GetApplicationString(BaseInfo.EarthquakeName) + $"與" + appPro.GetApplicationString(BaseInfo.BridgeName) + $"皆已參加" + appPro.GetApplicationString(BaseInfo.FilmName) + $"賽程!</p>"; ScriptManager.RegisterStartupScript(Page, Page.GetType(), "closepup", "$('#MsgBox').modal('show');", true); LoadTeamByAccount(TeamType.Bridge); } else { Response.Redirect("FilmRegistration.aspx"); } conn.Close(); } }