public async Task <IActionResult> Post([FromBody] Quiz qz) { if (qz == null) { return(BadRequest()); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; Int32 nNewID = -1; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; // Get user name var usr = User.FindFirst(c => c.Type == "sub"); String usrName = String.Empty; if (usr != null) { usrName = usr.Value; } else { return(BadRequest()); } List <AwardPlan> listAPlans = new List <AwardPlan>(); QuizCreateResult qcr = new QuizCreateResult(); SqlTransaction tran = null; try { queryString = @"SELECT [planid],[tgtuser],[createdby],[validfrom],[validto],[quiztype],[quizcontrol], [minscore],[minavgtime],[award] FROM [dbo].[awardplan] WHERE [tgtuser] = @tgtuser AND [quiztype] = @qtype AND @qdate >= [validfrom] AND @qdate <= [validto] "; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Read the award plan as the first setp cmd = new SqlCommand(queryString, conn); cmd.Parameters.AddWithValue("@tgtuser", usrName); cmd.Parameters.AddWithValue("@qtype", qz.QuizType); cmd.Parameters.AddWithValue("@qdate", qz.SubmitDate); reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { while (reader.Read()) { AwardPlan ap = new AwardPlan { PlanID = reader.GetInt32(0), TargetUser = reader.GetString(1) }; if (!reader.IsDBNull(2)) { ap.CreatedBy = reader.GetString(2); } else { ap.CreatedBy = String.Empty; } ap.ValidFrom = reader.GetDateTime(3); ap.ValidTo = reader.GetDateTime(4); ap.QuizType = (QuizTypeEnum)reader.GetInt16(5); if (!reader.IsDBNull(6)) { ap.QuizControl = reader.GetString(6); } if (!reader.IsDBNull(7)) { ap.MinQuizScore = reader.GetInt32(7); } if (!reader.IsDBNull(8)) { ap.MaxQuizAvgTime = reader.GetInt32(8); } ap.Award = reader.GetInt32(9); listAPlans.Add(ap); } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; tran = conn.BeginTransaction(); queryString = @"INSERT INTO [dbo].[quiz] ([quiztype],[basicinfo],[attenduser],[submitdate]) VALUES (@quiztype, @basicinfo, @attenduser, @submitdate); SELECT @Identity = SCOPE_IDENTITY();"; cmd = new SqlCommand(queryString, conn) { Transaction = tran }; cmd.Parameters.AddWithValue("@quiztype", qz.QuizType); cmd.Parameters.AddWithValue("@basicinfo", qz.BasicInfo); cmd.Parameters.AddWithValue("@attenduser", usrName); cmd.Parameters.AddWithValue("@submitdate", qz.SubmitDate); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewID = (Int32)idparam.Value; qcr.QuizID = nNewID; cmd.Dispose(); cmd = null; // Section foreach (QuizSection sect in qz.Sections) { queryString = @"INSERT INTO [dbo].[quizsection]([quizid],[section],[timespent],[totalitems],[faileditems]) VALUES(@quizid, @section, @timespent,@totalitems,@faileditems);"; cmd = new SqlCommand(queryString, conn) { Transaction = tran }; cmd.Parameters.AddWithValue("@quizid", nNewID); cmd.Parameters.AddWithValue("@section", sect.SectionID); cmd.Parameters.AddWithValue("@timespent", sect.TimeSpent); cmd.Parameters.AddWithValue("@totalitems", sect.TotalItems); cmd.Parameters.AddWithValue("@faileditems", sect.FailedItems); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } // Failed log foreach (QuizFailLog fl in qz.FailLogs) { queryString = @"INSERT INTO [dbo].[quizfaillog]([quizid],[failidx],[expected],[inputted]) VALUES(@quizid,@failidx,@expected,@inputted);"; cmd = new SqlCommand(queryString, conn) { Transaction = tran }; cmd.Parameters.AddWithValue("@quizid", nNewID); cmd.Parameters.AddWithValue("@failidx", fl.QuizFailIndex); cmd.Parameters.AddWithValue("@expected", fl.Expected); cmd.Parameters.AddWithValue("@inputted", fl.Inputted); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } // Now, work for the award foreach (AwardPlan ap in listAPlans) { if (!String.IsNullOrEmpty(ap.QuizControl)) { if (String.IsNullOrEmpty(qz.BasicInfo) || String.CompareOrdinal(qz.BasicInfo, ap.QuizControl) != 0) { continue; } } if (ap.MinQuizScore.HasValue) { if (qz.TotalScore < ap.MinQuizScore.Value) { continue; } } if (ap.MaxQuizAvgTime.HasValue) { if (qz.TotalAverageTime > ap.MaxQuizAvgTime.Value) { continue; } } queryString = @"INSERT INTO [dbo].[useraward] ([userid],[adate],[award],[planid],[qid],[used]) VALUES(@userid,@adate,@award,@planid,@qid, @used); SELECT @Identity = SCOPE_IDENTITY();"; cmd = new SqlCommand(queryString, conn) { Transaction = tran }; cmd.Parameters.AddWithValue("@userid", usrName); cmd.Parameters.AddWithValue("@adate", qz.SubmitDate); cmd.Parameters.AddWithValue("@award", ap.Award); cmd.Parameters.AddWithValue("@planid", ap.PlanID); cmd.Parameters.AddWithValue("@qid", nNewID); cmd.Parameters.AddWithValue("@used", DBNull.Value); SqlParameter idparam2 = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam2.Direction = ParameterDirection.Output; qcr.TotalAwardPoint += ap.Award; nRst = await cmd.ExecuteNonQueryAsync(); qcr.AwardIDList.Add((Int32)idparam2.Value); cmd.Dispose(); cmd = null; } // No errors! tran.Commit(); } } catch (Exception exp) { if (tran != null) { tran.Rollback(); } System.Diagnostics.Debug.WriteLine(exp.Message); strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (tran != null) { tran.Dispose(); tran = null; } if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } qz.QuizID = nNewID; var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = "yyyy-MM-dd", ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(qcr, setting)); }
public async Task <IActionResult> Put(int id, [FromBody] AwardPlan ap) { if (ap == null || ap.PlanID != id) { return(BadRequest()); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; // Get user name var usr = User.FindFirst(c => c.Type == "sub"); String usrName = String.Empty; if (usr != null) { usrName = usr.Value; } else { return(BadRequest("No user info found")); } try { Boolean bAllow = false; queryString = @"SELECT COUNT(*) AS COUNT FROM [quizuser] WHERE [userid] = N'" + usrName + "' AND [awardplan] LIKE '%U%'"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check the authority cmd = new SqlCommand(queryString, conn); reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { while (reader.Read()) { if (reader.GetInt32(0) > 0) { bAllow = true; break; } } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; if (!bAllow) { errorCode = HttpStatusCode.BadRequest; throw new Exception("No authority to create plan"); } queryString = @"UPDATE [dbo].[awardplan] SET [tgtuser] = @tgtuser ,[createdby] = @createdby ,[validfrom] = @validfrom ,[validto] = @validto ,[quiztype] = @quiztype ,[quizcontrol] = @quizcontrol ,[minscore] = @minscore ,[minavgtime] = @minavgtime ,[award] = @award WHERE [planid] = @planid;"; cmd = new SqlCommand(queryString, conn); cmd.Parameters.AddWithValue("@tgtuser", ap.TargetUser); if (String.IsNullOrEmpty(ap.CreatedBy)) { cmd.Parameters.AddWithValue("@createdby", DBNull.Value); } else { cmd.Parameters.AddWithValue("@createdby", ap.CreatedBy); } cmd.Parameters.AddWithValue("@validfrom", ap.ValidFrom); cmd.Parameters.AddWithValue("@validto", ap.ValidTo); cmd.Parameters.AddWithValue("@quiztype", ap.QuizType); if (!String.IsNullOrEmpty(ap.QuizControl)) { cmd.Parameters.AddWithValue("@quizcontrol", ap.QuizControl); } else { cmd.Parameters.AddWithValue("@quizcontrol", DBNull.Value); } if (ap.MinQuizScore.HasValue) { cmd.Parameters.AddWithValue("@minscore", ap.MinQuizScore.Value); } else { cmd.Parameters.AddWithValue("@minscore", DBNull.Value); } if (ap.MaxQuizAvgTime.HasValue) { cmd.Parameters.AddWithValue("@minavgtime", ap.MaxQuizAvgTime.Value); } else { cmd.Parameters.AddWithValue("@minavgtime", DBNull.Value); } cmd.Parameters.AddWithValue("@award", ap.Award); cmd.Parameters.AddWithValue("@planid", id); Int32 nRst = await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } } catch (Exception exp) { System.Diagnostics.Debug.WriteLine(exp.Message); strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } return(new JsonResult(ap)); }
public async Task <IActionResult> Get([FromQuery] String tgtuser = null, [FromQuery] String crtedby = null, [FromQuery] Boolean incInvalid = false) { List <AwardPlan> listRst = new List <AwardPlan>(); String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; try { String queryString = @"SELECT [planid],[tgtuser],[createdby],[validfrom],[validto],[quiztype],[quizcontrol],[minscore],[minavgtime],[award] FROM [dbo].[awardplan] "; if (!String.IsNullOrEmpty(crtedby) && String.IsNullOrEmpty(tgtuser)) { queryString += " WHERE [createdby] = N'" + crtedby + "'"; } else if (String.IsNullOrEmpty(crtedby) && !String.IsNullOrEmpty(tgtuser)) { queryString += " WHERE [tgtuser] = N'" + tgtuser + "'"; } else if (!String.IsNullOrEmpty(crtedby) && !String.IsNullOrEmpty(tgtuser)) { queryString += " WHERE [tgtuser] = N'" + tgtuser + "' AND [createdby] = N'" + crtedby + "'"; } if (!incInvalid) { if (!String.IsNullOrEmpty(crtedby) || !String.IsNullOrEmpty(tgtuser)) { queryString += " AND "; } else { queryString += " WHERE "; } queryString += " [validfrom] <= GETDATE() AND [validto] >= GETDATE() "; } using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { AwardPlan ap = new AwardPlan { PlanID = reader.GetInt32(0), TargetUser = reader.GetString(1) }; if (!reader.IsDBNull(2)) { ap.CreatedBy = reader.GetString(2); } else { ap.CreatedBy = String.Empty; } ap.ValidFrom = reader.GetDateTime(3); ap.ValidTo = reader.GetDateTime(4); ap.QuizType = (QuizTypeEnum)reader.GetInt16(5); if (!reader.IsDBNull(6)) { ap.QuizControl = reader.GetString(6); } if (!reader.IsDBNull(7)) { ap.MinQuizScore = reader.GetInt32(7); } if (!reader.IsDBNull(8)) { ap.MaxQuizAvgTime = reader.GetInt32(8); } ap.Award = reader.GetInt32(9); listRst.Add(ap); } } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = "yyyy-MM-dd", ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listRst, setting)); }
public async Task <IActionResult> Post([FromBody] AwardPlan ap) { if (ap == null) { return(BadRequest()); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; Boolean bError = false; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; // Get user name var usr = User.FindFirst(c => c.Type == "sub"); String usrName = String.Empty; if (usr != null) { usrName = usr.Value; } else { return(BadRequest()); } #if DEBUG // Just skip this check in debug mode #else if (String.IsNullOrEmpty(ap.CreatedBy)) { if (String.CompareOrdinal(usrName, ap.TargetUser) == 0) { return(BadRequest("Cannot create an plan for yourself")); } } else { if (String.CompareOrdinal(ap.CreatedBy, ap.TargetUser) == 0) { return(BadRequest("Cannot create an plan for yourself")); } } #endif try { queryString = @"SELECT COUNT(*) AS COUNT FROM [quizuser] WHERE [userid] = N'" + usrName + "' AND [awardplan] LIKE '%C%'"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check the authority Boolean bAllow = false; cmd = new SqlCommand(queryString, conn); reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { while (reader.Read()) { if (reader.GetInt32(0) > 0) { bAllow = true; break; } } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; if (!bAllow) { return(BadRequest("No authority to create plan")); } queryString = @"INSERT INTO [dbo].[awardplan] ([tgtuser],[createdby],[validfrom],[validto],[quiztype],[quizcontrol],[minscore],[minavgtime],[award]) VALUES(@tgtuser, @createdby, @validfrom, @validto, @quiztype, @quizcontrol, @minscore, @minavgtime, @award); SELECT @Identity = SCOPE_IDENTITY();"; cmd = new SqlCommand(queryString, conn); cmd.Parameters.AddWithValue("@tgtuser", ap.TargetUser); if (String.IsNullOrEmpty(ap.CreatedBy)) { cmd.Parameters.AddWithValue("@createdby", usrName); } else { cmd.Parameters.AddWithValue("@createdby", ap.CreatedBy); } cmd.Parameters.AddWithValue("@validfrom", ap.ValidFrom); cmd.Parameters.AddWithValue("@validto", ap.ValidTo); cmd.Parameters.AddWithValue("@quiztype", ap.QuizType); if (!String.IsNullOrEmpty(ap.QuizControl)) { cmd.Parameters.AddWithValue("@quizcontrol", ap.QuizControl); } else { cmd.Parameters.AddWithValue("@quizcontrol", DBNull.Value); } if (ap.MinQuizScore.HasValue) { cmd.Parameters.AddWithValue("@minscore", ap.MinQuizScore.Value); } else { cmd.Parameters.AddWithValue("@minscore", DBNull.Value); } if (ap.MaxQuizAvgTime.HasValue) { cmd.Parameters.AddWithValue("@minavgtime", ap.MaxQuizAvgTime.Value); } else { cmd.Parameters.AddWithValue("@minavgtime", DBNull.Value); } cmd.Parameters.AddWithValue("@award", ap.Award); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); ap.PlanID = (Int32)idparam.Value; cmd.Dispose(); cmd = null; } } catch (Exception exp) { System.Diagnostics.Debug.WriteLine(exp.Message); bError = true; strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (bError) { return(StatusCode(500, strErrMsg)); } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } return(new JsonResult(ap)); }
public async Task <IActionResult> Get(int id) { AwardPlan objRst = new AwardPlan(); String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; try { String queryString = @"SELECT [planid],[tgtuser],[createdby],[validfrom],[validto],[quiztype],[quizcontrol],[minscore],[minavgtime],[award] FROM [dbo].[awardplan] WHERE [planid] = @pid;"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); cmd = new SqlCommand(queryString, conn); cmd.Parameters.AddWithValue("@pid", id); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { objRst.PlanID = reader.GetInt32(0); objRst.TargetUser = reader.GetString(1); if (!reader.IsDBNull(2)) { objRst.CreatedBy = reader.GetString(2); } else { objRst.CreatedBy = String.Empty; } objRst.ValidFrom = reader.GetDateTime(3); objRst.ValidTo = reader.GetDateTime(4); objRst.QuizType = (QuizTypeEnum)reader.GetInt16(5); if (!reader.IsDBNull(6)) { objRst.QuizControl = reader.GetString(6); } if (!reader.IsDBNull(7)) { objRst.MinQuizScore = reader.GetInt32(7); } if (!reader.IsDBNull(8)) { objRst.MaxQuizAvgTime = reader.GetInt32(8); } objRst.Award = reader.GetInt32(9); break; } } else { errorCode = HttpStatusCode.NotFound; throw new Exception(); } } } catch (Exception exp) { System.Diagnostics.Debug.WriteLine(exp.Message); strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = "yyyy-MM-dd", ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(objRst, setting)); }