private void TeamView(object sender, EventArgs e)
{
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
string teamName = "";
HtmlAnchor control = (HtmlAnchor)sender;
string[] sendInfo = (control.ID).Split('|');
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
conn.Open();
SqlCommand command;
SqlDataReader dr;
command = new SqlCommand($"SELECT Name FROM EarthquakeTeam WHERE Id = '{sendInfo[2]}' AND EarthquakeTeam.CreateDate " +
appPro.GetBetweenSignUpTime(), conn);
dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
teamName = dr["Name"].ToString();
}
}
dr.Close();
command.Cancel();
command = new SqlCommand($"DELETE FROM FilmInfo WHERE AccountId = '{teamName}' AND CreateDate " +
appPro.GetBetweenSignUpTime() + $" AND TeamType = 'Earthquake';", conn);
command.ExecuteNonQuery();
command.Cancel();
command = new SqlCommand($"DELETE FROM EarthquakeTeam WHERE Id= '{sendInfo[2]}'", conn);
command.ExecuteNonQuery();
command.Cancel();
conn.Close();
}
private void LoadTeamByAccount()
{
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
conn.Open();
SqlCommand command;
SqlDataReader dr;
string queryCommand = $"SELECT BridgeTeam.Name AS teamName,BridgeTeam.Id AS teamid,School.Name AS SchoolName FROM BridgeTeam LEFT JOIN Account ON BridgeTeam.AccountID = Account.Id " +
$"LEFT JOIN School ON Account.SchoolID=School.Id ";
if (Select_School.Items[Select_School.SelectedIndex].Text != "All")
{
queryCommand += $"WHERE School.Name = '{Select_School.Items[Select_School.SelectedIndex].Text}' AND BridgeTeam.CreateDate " +
appPro.GetBetweenSignUpTime();
}
command = new SqlCommand(queryCommand + ";", conn);
dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string teamName = dr["teamName"].ToString();
string SchoolName = dr["SchoolName"].ToString();
string teamID = dr["teamid"].ToString();
AddTeamCard(teamName, SchoolName, teamID, div1);
}
}
dr.Close();
command.Cancel();
conn.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//讀取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.GameDate));
if (DateTime.Now > startTime && DateTime.Now < endTime)
{
LoadInfoAboutTeam();
}
//加入每屆的資料
lab_TitleName.InnerText = $"國立高雄科技大學 {appPro.GetApplicationString(BaseInfo.GameNumber)} 抗震大作戰";
lab_TitleGameList.InnerText = $"{appPro.GetApplicationString(BaseInfo.EarthquakeName)}X" +
$"{appPro.GetApplicationString(BaseInfo.BridgeName)}X" +
$"{appPro.GetApplicationString(BaseInfo.FilmName)}";
lab_SignTime.InnerText = $"報名開放時間：" +
$"{appPro.GetDateFormat(BaseInfo.StartSignUp, "yyyy/MM/dd")} ~ " +
$"{appPro.GetDateFormat(BaseInfo.EndSignUp, "yyyy/MM/dd")}";
lab_UpdateTime.InnerText = $"隊伍資訊更正開放時間：" +
$"{appPro.GetDateFormat(BaseInfo.StartSignUp, "yyyy/MM/dd")} ~ " +
$"{appPro.GetDateFormat(BaseInfo.EndUpdateInfo, "yyyy/MM/dd")}";
lab_FilmUpdateTime.InnerText = $"{appPro.GetApplicationString(BaseInfo.FilmName)}作品繳交開放時間：" +
$"{appPro.GetDateFormat(BaseInfo.StartSignUp, "yyyy/MM/dd")} ~ " +
$"{appPro.GetDateFormat(BaseInfo.EndFilmUpdate, "yyyy/MM/dd")}";
lab_Game1Name.InnerText = appPro.GetApplicationString(BaseInfo.EarthquakeName);
lab_Game2Name.InnerText = appPro.GetApplicationString(BaseInfo.BridgeName);
lab_Game3Name.InnerText = appPro.GetApplicationString(BaseInfo.FilmName);
}
}
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)
{
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 btn_AddFilmLink_ServerClick(object sender, EventArgs e)
{
//空的連結就不處理了 -.-
if (FilmLink.Value == "")
{
return;
}
//讀取Application Data
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
//有連結就更新資料
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
conn.Open();
SqlCommand command = new SqlCommand($"UPDATE FilmInfo SET FileLink = '{FilmLink.Value}' WHERE Id = '{Session["AddLink"].ToString()}'" +
$" AND CreateDate " +
appPro.GetBetweenSignUpTime(), conn);
command.ExecuteNonQuery();
command.Cancel();
conn.Close();
//Init something
FilmLink.Value = "";
Session["AddLink"] = null;
LoadTeamByAccount(TeamType.Film);
}
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);
}
public void LoadTeacherSelectData(string focusArea, string facusSchool)
{
select_Teacher.Items.Clear();
select_Teacher.Items.Add("All");
//讀取Application Data
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
string strConn = ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString;
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
//取得今年的已報名隊伍的帳號ID
SqlCommand command = new SqlCommand($"SELECT AccountId FROM BridgeTeam WHERE CreateDate " +
appPro.GetBetweenSignUpTime() +
$" GROUP BY AccountID", conn);
SqlDataReader dr = command.ExecuteReader();
List <string> Ids = new List <string>();
while (dr.Read())
{
Ids.Add(dr["AccountId"].ToString());
}
dr.Close();
command.Cancel();
//取得學校名稱跟區域
if (facusSchool == "All")
{
LoadSchoolSelectData(focusArea);
}
else
{
List <string> SchoolName = new List <string>();
foreach (string id in Ids)
{
command = new SqlCommand($"SELECT Account.Name AS Name, School.Name AS SchoolName, School.Area AS Area " +
$"FROM Account LEFT JOIN School ON Account.SchoolID = School.Id WHERE Account.Id = '{id}' AND School.Name = '{facusSchool}';", conn);
dr = command.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
//Add teacher Name
select_Teacher.Items.Add(dr["Name"].ToString());
}
}
dr.Close();
command.Cancel();
}
}
conn.Close();
}
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) + $"抗震盃報名系統";
}
}
public void LoadInitSelect()
{
//讀取Application Data
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
string strConn = ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString;
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
//取得今年的已報名隊伍的帳號ID
SqlCommand command = new SqlCommand($"SELECT AccountId FROM BridgeTeam WHERE CreateDate " +
appPro.GetBetweenSignUpTime() +
$" GROUP BY AccountID", conn);
SqlDataReader dr = command.ExecuteReader();
List <string> Ids = new List <string>();
while (dr.Read())
{
Ids.Add(dr["AccountId"].ToString());
}
dr.Close();
command.Cancel();
//取得學校名稱跟區域
List <string> SchoolName = new List <string>();
List <string> Areas = new List <string>();
foreach (string id in Ids)
{
command = new SqlCommand($"SELECT Account.Name AS Name, School.Name AS SchoolName, School.Area AS Area FROM Account LEFT JOIN School ON Account.SchoolID = School.Id WHERE Account.Id = '{id}';", conn);
dr = command.ExecuteReader();
while (dr.Read())
{
//Add teacher Name
select_Teacher.Items.Add(dr["Name"].ToString());
//取得學校名稱跟區域, 不同的才儲存
if (!SchoolName.Contains(dr["SchoolName"].ToString()))
{
SchoolName.Add(dr["SchoolName"].ToString());
select_School.Items.Add(dr["SchoolName"].ToString());
}
if (!Areas.Contains(dr["Area"].ToString()))
{
Areas.Add(dr["Area"].ToString());
select_Area.Items.Add(dr["Area"].ToString());
}
}
dr.Close();
command.Cancel();
}
conn.Close();
}
private void InitLoad()
{
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
conn.Open();
SqlCommand command;
SqlDataReader dr;
string queryCommand = $"SELECT Account.Name, Account.Username, Account.Password, " +
$"Account.Phone, Account.Email, Account.Id, SchoolID FROM Account WHERE Id = '{Session["UpdateId"].ToString()}'";
command = new SqlCommand(queryCommand + ";", conn);
dr = command.ExecuteReader();
string accountSchoolId = "";
while (dr.Read())
{
input_Username.Value = dr["Username"].ToString();
Text1.Value = dr["Password"].ToString();
input_Name.Value = dr["Name"].ToString();
Text2.Value = dr["Phone"].ToString();
Text3.Value = dr["Email"].ToString();
accountSchoolId = dr["SchoolID"].ToString();
Text5.Value = dr["Id"].ToString();
}
dr.Close();
command.Cancel();
command = new SqlCommand($"SELECT Id, Name FROM School;", conn);
dr = command.ExecuteReader();
int accountIndex = 0;
while (dr.Read())
{
Select_School.Items.Add(dr["Name"].ToString());
if (dr["Id"].ToString() == accountSchoolId)
{
accountIndex = Select_School.Items.Count - 1;
}
}
dr.Close();
command.Cancel();
conn.Close();
//設定選擇的SchoolName
Select_School.SelectedIndex = accountIndex;
}
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();
}
}
private void TeamView(object sender, EventArgs e)
{
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
HtmlAnchor control = (HtmlAnchor)sender;
string[] sendInfo = (control.ID).Split('|');
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
conn.Open();
SqlCommand command;
SqlDataReader dr;
command = new SqlCommand($"DELETE FROM FilmInfo WHERE Id= '{sendInfo[2]}' AND FilmInfo.CreateDate " +
appPro.GetBetweenSignUpTime(), conn);
dr = command.ExecuteReader();
dr.Close();
command.Cancel();
conn.Close();
}
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);
}
}
private void LoadSchoolSelectData()
{
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
Select_School.Items.Clear();
string strConn = ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString;
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand da = new SqlCommand("SELECT Name FROM School ", conn);
SqlDataReader dr = da.ExecuteReader();
while (dr.Read())
{
Select_School.Items.Add(dr["Name"].ToString());
}
dr.Close();
da.Cancel();
conn.Close();
}
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) + "報名資訊";
}
}
private void TeamView(object sender, EventArgs e)
{
ApplicationProcessing appPro = new ApplicationProcessing(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
HtmlAnchor control = (HtmlAnchor)sender;
string[] sendInfo = (control.ID).Split('|');
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlDB"].ConnectionString);
conn.Open();
SqlCommand command;
SqlDataReader dr;
command = new SqlCommand($"SELECT * FROM Account WHERE Id = '{sendInfo[2]}';", conn);
dr = command.ExecuteReader();
while (dr.Read())
{
ViewAccountInfo(dr);
}
dr.Close();
command.Cancel();
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "closepup", "$('#TeamView').modal('show');", true);
conn.Close();
}
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();
}
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, Count FROM BridgeTeam WHERE AccountID = {teacherId} AND CreateDate " +
appPro.GetBetweenSignUpTime() +
$";", conn);
dr = command.ExecuteReader();
while (dr.Read())
{
IDataRecord data = (IDataRecord)dr;
string name = data["Name"].ToString();
string count = data["Count"].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\">隊伍人數：" + count + "人</a>"
+ "</div></div>";
div_TeamCard.InnerHtml += innerHtmltext;
}
dr.Close();
command.Cancel();
}
conn.Close();
}
//確認資料後允許報名
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);
}
}
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);
}
}
public void LoadInfoAboutTeam()
{
//讀取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;
//Earthquake
command = new SqlCommand($"SELECT School.Name AS SchoolName, EarthquakeTeam.Name AS Name 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 = command.ExecuteReader();
int count = 0;
List <string> school = new List <string>();
while (dr.Read())
{
count++;
if (!school.Contains(dr["SchoolName"].ToString()))
{
school.Add(dr["SchoolName"].ToString());
}
}
p_Earthquake_Count.InnerText = count.ToString() + " 隊";
p_Earthquake_SchoolCount.InnerText = school.Count().ToString() + " 校";
dr.Close();
command.Cancel();
//Bridge
command = new SqlCommand($"SELECT School.Name AS SchoolName, BridgeTeam.Name AS Name 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 = command.ExecuteReader();
count = 0;
school = new List <string>();
while (dr.Read())
{
count++;
if (!school.Contains(dr["SchoolName"].ToString()))
{
school.Add(dr["SchoolName"].ToString());
}
}
p_Bridge_Count.InnerText = count.ToString() + " 隊";
p_Birdge_SchoolCount.InnerText = school.Count().ToString() + " 校";
dr.Close();
command.Cancel();
//Film
school = new List <string>();
command = new SqlCommand($"SELECT Name, TeamType FROM FilmInfo WHERE FilmInfo.CreateDate " +
appPro.GetBetweenSignUpTime() +
$";", conn);
dr = command.ExecuteReader();
count = 0;
List <string> earthquakeTeam = new List <string>();
List <string> bridgeTeam = new List <string>();
while (dr.Read())
{
count++;
if (dr["TeamType"].ToString() == "Earthquake")
{
earthquakeTeam.Add(dr["Name"].ToString());
}
else
{
bridgeTeam.Add(dr["Name"].ToString());
}
}
dr.Close();
command.Cancel();
foreach (string ear in earthquakeTeam)
{
command = new SqlCommand($"SELECT School.Name AS SchoolName FROM EarthquakeTeam LEFT JOIN Account ON EarthquakeTeam.AccountID = Account.Id " +
$"LEFT JOIN School ON Account.SchoolID = School.Id WHERE EarthquakeTeam.CreateDate " +
appPro.GetBetweenSignUpTime() +
$" AND EarthquakeTeam.Name = '{ear}';", conn);
dr = command.ExecuteReader();
while (dr.Read())
{
if (!school.Contains(dr["SchoolName"].ToString()))
{
school.Add(dr["SchoolName"].ToString());
}
}
dr.Close();
command.Cancel();
}
foreach (string bri in bridgeTeam)
{
command = new SqlCommand($"SELECT School.Name AS SchoolName FROM BridgeTeam LEFT JOIN Account ON BridgeTeam.AccountID = Account.Id " +
$"LEFT JOIN School ON Account.SchoolID = School.Id WHERE BridgeTeam.CreateDate " +
appPro.GetBetweenSignUpTime() +
$" AND BridgeTeam.Name = '{bri}';", conn);
dr = command.ExecuteReader();
while (dr.Read())
{
if (!school.Contains(dr["SchoolName"].ToString()))
{
school.Add(dr["SchoolName"].ToString());
}
}
dr.Close();
command.Cancel();
}
p_Film_Count.InnerText = count.ToString() + " 隊";
p_Film_SchoolCount.InnerText = school.Count().ToString() + " 校";
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);
}
}
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_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_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();
}
}
