private string getQueryString(Boolean bListMode, Int32?nTop, Int32?nSkip, Int32 hid, Boolean sentbox, String usrName) { String strSQL = ""; if (bListMode) { if (sentbox) { strSQL += @"SELECT count(*) FROM [dbo].[t_homemsg] WHERE [HID] = " + hid.ToString() + " AND [USERFROM] = N'" + usrName + "'; "; } else { strSQL += @"SELECT count(*) FROM [dbo].[t_homemsg] WHERE [HID] = " + hid.ToString() + " AND [USERTO] = N'" + usrName + "'; "; } } strSQL += HIHDBUtility.getHomeMsgQueryString(usrName, hid, sentbox); if (bListMode && nTop.HasValue && nSkip.HasValue) { strSQL += @" ORDER BY (SELECT NULL) OFFSET " + nSkip.Value.ToString() + " ROWS FETCH NEXT " + nTop.Value.ToString() + " ROWS ONLY;"; } else if (!bListMode && !String.IsNullOrEmpty(usrName)) { strSQL += @" AND [t_homemsg].[USERTO] = " + usrName; } #if DEBUG System.Diagnostics.Debug.WriteLine("HomeMsgController, SQL generated: " + strSQL); #endif return(strSQL); }
private string getSQLString(Boolean bListMode, Int32?nTop, Int32?nSkip, String userFilter, Int32?hid) { String strSQL = ""; if (bListMode) { strSQL += @"SELECT count(*) FROM [dbo].[t_learn_hist] WHERE [hid] = " + hid.Value.ToString(); if (!String.IsNullOrEmpty(userFilter)) { strSQL += " AND [USERID] = N'" + userFilter + "'"; } strSQL += ";"; } strSQL += HIHDBUtility.getLearnHistoryQueryString(hid, userFilter); if (bListMode && nTop.HasValue && nSkip.HasValue) { strSQL += @" ORDER BY (SELECT NULL) OFFSET " + nSkip.Value.ToString() + " ROWS FETCH NEXT " + nTop.Value.ToString() + " ROWS ONLY;"; } else if (!bListMode) { strSQL += @" WHERE [t_learn_hist].[HID] = @HID AND [t_learn_hist].[USERID] = @USERID AND [t_learn_hist].[OBJECTID] = @OBJECTID AND [t_learn_hist].[LEARNDATE] = @LEARNDATE "; } #if DEBUG System.Diagnostics.Debug.WriteLine("LearnHistoryController, SQL generated: " + strSQL); #endif return(strSQL); }
private string getQueryString(Boolean bListMode, Int32?nTop, Int32?nSkip, Int32?nSearchID, Int32?hid) { String strSQL = ""; if (bListMode) { strSQL += @"SELECT count(*) FROM [dbo].[t_learn_ctgy] WHERE [HID] IS NULL "; if (hid.HasValue && hid.Value != 0) { strSQL += " OR [HID] = " + hid.Value.ToString() + ";"; } } strSQL += HIHDBUtility.getLearnCategoryQueryString(); if (bListMode && nTop.HasValue && nSkip.HasValue) { strSQL += " WHERE [HID] IS NULL "; if (hid.HasValue && hid.Value != 0) { strSQL += " OR [HID] = " + hid.Value.ToString(); } strSQL += @" ORDER BY (SELECT NULL) OFFSET " + nSkip.Value.ToString() + " ROWS FETCH NEXT " + nTop.Value.ToString() + " ROWS ONLY;"; } else if (!bListMode && nSearchID.HasValue) { strSQL += @" AND [t_learn_ctgy].[ID] = " + nSearchID.Value.ToString(); } #if DEBUG System.Diagnostics.Debug.WriteLine("LearnCategoryController, SQL generated: " + strSQL); #endif return(strSQL); }
public async Task <IActionResult> Get(int id, [FromQuery] Int32 hid = 0) { if (hid <= 0) { return(BadRequest("HID is missing")); } if (id <= 0) { return(BadRequest("Invalid ID")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } RecurUIEventViewModel vm = new RecurUIEventViewModel(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } queryString = HIHDBUtility.Event_GetRecurEventQueryString(false, usrName, hid, null, null, id); cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { HIHDBUtility.Event_RecurDB2VM(reader, vm, false); } } reader.Close(); cmd.Dispose(); cmd = null; queryString = HIHDBUtility.Event_GetNormalEventByRecurIDString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.Event_BindNormalEventForRecurDeletionParameters(cmd, hid, id); reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { while (reader.Read()) { var vmevent = new EventViewModel(); HIHDBUtility.Event_DB2VM(reader, vmevent, true); vm.EventList.Add(vmevent); } } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Post([FromBody] EventHabitCheckInViewModel vm) { if (vm == null) { return(BadRequest("No data is inputted")); } if (!ModelState.IsValid) { return(BadRequest("Model status is invalid")); } if (vm.HabitID <= 0 || vm.HID <= 0) { return(BadRequest("Invalid data: home ID, habit ID")); } Boolean unitMode = Startup.UnitTestMode; SqlConnection conn = null; String queryString = ""; Int32 nNewID = -1; String strErrMsg = ""; String usrName = String.Empty; HttpStatusCode errorCode = HttpStatusCode.OK; if (unitMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usr = User.FindFirst(c => c.Type == "sub"); if (usr != null) { usrName = usr.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User is not recognized")); } } SqlCommand cmd = null; SqlTransaction tran = null; try { queryString = @"SELECT [ID] FROM [dbo].[t_event_habit_checkin] WHERE [TranDate] = @trandate AND [HabitID] = @habitid"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; // Re-throw } cmd = new SqlCommand(queryString, conn); cmd.Parameters.AddWithValue("@trandate", vm.TranDate); cmd.Parameters.AddWithValue("@habitid", vm.HabitID); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { Int32 nDuplicatedID = -1; while (reader.Read()) { nDuplicatedID = reader.GetInt32(0); break; } errorCode = HttpStatusCode.BadRequest; strErrMsg = "Event has been checked in at same date: " + nDuplicatedID.ToString(); throw new Exception(strErrMsg); } else { reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Now go ahead for the creating queryString = HIHDBUtility.Event_GetEventHabitCheckInInsertString(); tran = conn.BeginTransaction(); cmd = new SqlCommand(queryString, conn); cmd.Transaction = tran; HIHDBUtility.Event_BindEventHabitCheckInInsertParameters(cmd, vm, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewID = (Int32)idparam.Value; tran.Commit(); } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif if (tran != null) { tran.Rollback(); } strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (cmd != null) { cmd.Dispose(); cmd = null; } if (tran != null) { tran.Dispose(); tran = null; } if (conn != null) { conn.Close(); 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } vm.ID = nNewID; var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Get([FromQuery] Int32 hid, Boolean sentbox = false, Int32 top = 100, Int32 skip = 0) { BaseListViewModel <HomeMsgViewModel> listVm = new BaseListViewModel <HomeMsgViewModel>(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; HttpStatusCode errorCode = HttpStatusCode.OK; String strErrMsg = ""; try { var usrObj = HIHAPIUtility.GetUserClaim(this); if (usrObj == null) { return(BadRequest()); } var usrName = usrObj.Value; if (String.IsNullOrEmpty(usrName)) { return(BadRequest()); } queryString = this.getQueryString(true, top, skip, hid, sentbox, usrName); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { listVm.TotalCount = reader.GetInt32(0); break; } } reader.NextResult(); if (reader.HasRows) { while (reader.Read()) { HomeMsgViewModel vm = new HomeMsgViewModel(); HIHDBUtility.HomeMsg_DB2VM(reader, vm); listVm.Add(vm); } } } } 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.Close(); conn.Dispose(); } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listVm, setting)); }
public async Task <IActionResult> Get([FromQuery] Int32 hid = 0, Int32 top = 100, Int32 skip = 0) { String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } List <LearnCategoryViewModel> listVm = null; SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { var cacheKey = String.Format(CacheKeys.LearnCtgyList, hid); if (_cache.TryGetValue <List <LearnCategoryViewModel> >(cacheKey, out listVm)) { // Do nothing } else { listVm = new List <LearnCategoryViewModel>(); queryString = HIHDBUtility.getLearnCategoryQueryString() + " WHERE [HID] IS NULL OR [HID] = " + hid.ToString(); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user if (hid > 0) { try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { LearnCategoryViewModel vm = new LearnCategoryViewModel(); HIHDBUtility.LearnCategory_DB2VM(reader, vm); listVm.Add(vm); } } } _cache.Set <List <LearnCategoryViewModel> >(cacheKey, listVm, TimeSpan.FromMinutes(20)); } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listVm, setting)); }
public async Task <IActionResult> Delete(int id, [FromQuery] Int32 hid = 0) { if (hid <= 0) { return(BadRequest("HID is missing")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } EventHabitViewModel vm = new EventHabitViewModel(); SqlConnection conn = null; SqlCommand cmd = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } queryString = HIHDBUtility.Event_GetEventHabitDeleteString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.Event_BindEventHabitDeleteParameters(cmd, id, hid); await cmd.ExecuteNonQueryAsync(); } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } return(Ok()); }
public async Task <IActionResult> Get([FromQuery] Int32 hid, Int32 top = 100, Int32 skip = 0) { if (hid <= 0) { return(BadRequest("HID is missing")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } BaseListViewModel <EventHabitViewModel> listVm = new BaseListViewModel <EventHabitViewModel>(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } queryString = HIHDBUtility.Event_GetEventHabitQueryString(true, usrName, hid, skip, top); cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { listVm.TotalCount = reader.GetInt32(0); break; } } reader.NextResult(); if (reader.HasRows) { Dictionary <Int32, EventHabitViewModel> dictVM = new Dictionary <int, EventHabitViewModel>(); while (reader.Read()) { EventHabitViewModel vm = new EventHabitViewModel(); EventHabitDetail detail = new EventHabitDetail(); HIHDBUtility.Event_HabitDB2VM(reader, vm, detail, true); if (dictVM.ContainsKey(vm.ID)) { dictVM[vm.ID].Details.Add(detail); } else { vm.Details.Add(detail); listVm.Add(vm); dictVM.Add(vm.ID, vm); } } } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listVm, setting)); }
public async Task <IActionResult> Put(int id, [FromBody] EventViewModel vm) { if (vm == null || id <= 0 || id != vm.ID) { return(BadRequest("No data is inputted")); } if (vm.HID <= 0) { return(BadRequest("Home not defined")); } if (vm.Name != null) { vm.Name = vm.Name.Trim(); } if (String.IsNullOrEmpty(vm.Name)) { return(BadRequest("Name is a must!")); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } try { queryString = @"SELECT [ID] FROM [dbo].[t_event] WHERE [ID] = " + vm.ID.ToString(); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; // Re-throw } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (!reader.HasRows) { errorCode = HttpStatusCode.BadRequest; strErrMsg = "Object with ID doesnot exist: " + id.ToString(); throw new Exception(strErrMsg); } else { reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Now go ahead for the creating queryString = HIHDBUtility.Event_GetNormalEventUpdateString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.Event_BindNormalEventUpdateParameters(cmd, vm, usrName); Int32 nRst = await cmd.ExecuteNonQueryAsync(); } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Get([FromRoute] int id, [FromQuery] Int32 hid = 0) { if (hid <= 0) { return(BadRequest("Not HID inputted")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } FinanceLoanDocumentUIViewModel vm = new FinanceLoanDocumentUIViewModel(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { queryString = HIHDBUtility.GetFinanceDocLoanQueryString(id, hid); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (!reader.HasRows) { errorCode = HttpStatusCode.NotFound; throw new Exception(); } // Header while (reader.Read()) { HIHDBUtility.FinDocHeader_DB2VM(reader, vm); } reader.NextResult(); // Items while (reader.Read()) { FinanceDocumentItemUIViewModel itemvm = new FinanceDocumentItemUIViewModel(); HIHDBUtility.FinDocItem_DB2VM(reader, itemvm); vm.Items.Add(itemvm); } reader.NextResult(); // Account while (reader.Read()) { FinanceAccountUIViewModel vmAccount = new FinanceAccountUIViewModel(); Int32 aidx = 0; aidx = HIHDBUtility.FinAccountHeader_DB2VM(reader, vmAccount, aidx); vmAccount.ExtraInfo_Loan = new FinanceAccountExtLoanViewModel(); HIHDBUtility.FinAccountLoan_DB2VM(reader, vmAccount.ExtraInfo_Loan, aidx); vm.AccountVM = vmAccount; } reader.NextResult(); // Tmp docs while (reader.Read()) { FinanceTmpDocLoanViewModel loanvm = new FinanceTmpDocLoanViewModel(); HIHDBUtility.FinTmpDocLoan_DB2VM(reader, loanvm); vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs.Add(loanvm); } reader.NextResult(); // Tag if (reader.HasRows) { while (reader.Read()) { Int32 itemID = reader.GetInt32(0); String sterm = reader.GetString(1); foreach (var vitem in vm.Items) { if (vitem.ItemID == itemID) { vitem.TagTerms.Add(sterm); } } } } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Post([FromBody] FinanceLoanDocumentUIViewModel vm) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } if (vm == null || (vm.DocType != FinanceDocTypeViewModel.DocType_BorrowFrom && vm.DocType != FinanceDocTypeViewModel.DocType_LendTo)) { return(BadRequest("No data is inputted")); } if (vm.HID <= 0) { return(BadRequest("Not HID inputted")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } // Check the items if (vm.Items.Count != 1) { return(BadRequest("Only one item doc is supported by far")); } if (vm.AccountVM == null || vm.AccountVM.ExtraInfo_Loan == null) { return(BadRequest("No account info!")); } if (vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs.Count <= 0) { return(BadRequest("No template docs defined!")); } else { foreach (var tdoc in vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs) { if (!tdoc.ControlCenterID.HasValue && !tdoc.OrderID.HasValue) { return(BadRequest("Either control center or order shall be specified in Loan Template doc")); } if (tdoc.TranAmount <= 0) { return(BadRequest("Amount is zero!")); } } } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; SqlTransaction tran = null; String queryString = ""; Int32 nNewDocID = -1; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } tran = conn.BeginTransaction(); // First, create the doc header => nNewDocID queryString = HIHDBUtility.GetFinDocHeaderInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vm, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewDocID = (Int32)idparam.Value; cmd.Dispose(); cmd = null; // Then, creating the items foreach (FinanceDocumentItemUIViewModel ivm in vm.Items) { if (vm.DocType == FinanceDocTypeViewModel.DocType_BorrowFrom) { ivm.TranType = FinanceTranTypeViewModel.TranType_BorrowFrom; } else if (vm.DocType == FinanceDocTypeViewModel.DocType_LendTo) { ivm.TranType = FinanceTranTypeViewModel.TranType_LendTo; } queryString = HIHDBUtility.GetFinDocItemInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // Tags if (ivm.TagTerms.Count > 0) { // Create tags foreach (var term in ivm.TagTerms) { queryString = HIHDBUtility.GetTagInsertString(); cmd = new SqlCommand(queryString, conn, tran); HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } } } // Third, go to the account creation => nNewAccountID queryString = HIHDBUtility.GetFinanceAccountHeaderInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinAccountInsertParameter(cmd, vm.AccountVM, usrName); SqlParameter idparam2 = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam2.Direction = ParameterDirection.Output; nRst = await cmd.ExecuteNonQueryAsync(); Int32 nNewAccountID = (Int32)idparam2.Value; cmd.Dispose(); cmd = null; // 3a. Create another item to loan document var nMaxItemID = vm.Items.Max(item => item.ItemID); foreach (FinanceDocumentItemUIViewModel ivm in vm.Items) { ivm.ItemID = ++nMaxItemID; ivm.AccountID = nNewAccountID; if (vm.DocType == FinanceDocTypeViewModel.DocType_BorrowFrom) { ivm.TranType = FinanceTranTypeViewModel.TranType_OpeningLiability; } else if (vm.DocType == FinanceDocTypeViewModel.DocType_LendTo) { ivm.TranType = FinanceTranTypeViewModel.TranType_OpeningAsset; } queryString = HIHDBUtility.GetFinDocItemInsertString(); SqlCommand cmd2 = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocItemInsertParameter(cmd2, ivm, nNewDocID); await cmd2.ExecuteNonQueryAsync(); cmd2.Dispose(); cmd2 = null; } // Fourth, creat the Loan part queryString = HIHDBUtility.GetFinanceAccountLoanInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinAccountLoanInsertParameter(cmd, vm.AccountVM.ExtraInfo_Loan, nNewDocID, nNewAccountID, usrName); nRst = await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // Fifth, create template docs foreach (FinanceTmpDocLoanViewModel avm in vm.AccountVM.ExtraInfo_Loan.LoanTmpDocs) { queryString = HIHDBUtility.GetFinanceTmpDocLoanInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinTmpDocLoanParameter(cmd, avm, nNewAccountID, usrName); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } tran.Commit(); // Update the buffer // Account list var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null); this._cache.Remove(cacheKey); } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif if (tran != null) { tran.Rollback(); } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } vm.ID = nNewDocID; var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Put(int id, [FromBody] LearnQuestionBankViewModel vm) { String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } if (vm == null) { return(BadRequest("No data is inputted")); } if (vm.HID <= 0) { return(BadRequest("No Home Inputted")); } // Check if (vm.ID != id) { return(BadRequest("Invalid data")); } if (vm.Question != null) { vm.Question = vm.Question.Trim(); } if (String.IsNullOrEmpty(vm.Question)) { return(BadRequest("Question is a must!")); } if (vm.QuestionType == (Byte)HIHQuestionBankType.EssayQuestion || vm.QuestionType == (Byte)HIHQuestionBankType.MultipleChoice) { } else { // Non supported type return(BadRequest("Non-supported type")); } // Update the database SqlConnection conn = null; SqlTransaction tran = null; SqlCommand cmd = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } tran = conn.BeginTransaction(); // Question bank queryString = @"UPDATE [dbo].[t_learn_qtn_bank] SET [Type] = @Type ,[Question] = @Question ,[BriefAnswer] = @BriefAnswer ,[UPDATEDBY] = @UPDATEDBY ,[UPDATEDAT] = @UPDATEDAT WHERE [HID] = @HID AND [ID] = @ID"; cmd = new SqlCommand(queryString, conn) { Transaction = tran }; cmd.Parameters.AddWithValue("@HID", vm.HID); cmd.Parameters.AddWithValue("@ID", vm.ID); cmd.Parameters.AddWithValue("@Type", vm.QuestionType); cmd.Parameters.AddWithValue("@Question", vm.Question); if (!String.IsNullOrEmpty(vm.BriefAnswer)) { cmd.Parameters.AddWithValue("@BriefAnswer", vm.BriefAnswer); } else { cmd.Parameters.AddWithValue("@BriefAnswer", DBNull.Value); } cmd.Parameters.AddWithValue("@UPDATEDBY", usrName); cmd.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // Question bank sub item queryString = @"DELETE FROM [dbo].[t_learn_qtn_bank_sub] WHERE [QTNID] = " + id.ToString(); cmd = new SqlCommand(queryString, conn, tran); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; foreach (var si in vm.SubItemList) { queryString = @"INSERT INTO [dbo].[t_learn_qtn_bank_sub] ([QTNID] ,[SUBITEM] ,[DETAIL] ,[OTHERS]) VALUES (@QTNID ,@SUBITEM ,@DETAIL ,@OTHERS)"; cmd = new SqlCommand(queryString, conn, tran); cmd.Parameters.AddWithValue("@QTNID", id); cmd.Parameters.AddWithValue("@SUBITEM", si.SubItem); cmd.Parameters.AddWithValue("@DETAIL", si.Detail); if (!String.IsNullOrEmpty(si.Others)) { cmd.Parameters.AddWithValue("@OTHERS", si.Others); } else { cmd.Parameters.AddWithValue("@OTHERS", DBNull.Value); } await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } // Tag queryString = HIHDBUtility.GetTagDeleteString(); cmd = new SqlCommand(queryString, conn, tran); HIHDBUtility.BindTagDeleteParameter(cmd, vm.HID, HIHTagTypeEnum.LearnQuestionBank, id); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; foreach (var tag in vm.TagTerms) { queryString = HIHDBUtility.GetTagInsertString(); cmd = new SqlCommand(queryString, conn, tran); HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.LearnQuestionBank, id, tag); await cmd.ExecuteNonQueryAsync(); } tran.Commit(); } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif if (tran != null) { tran.Rollback(); } strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (tran != null) { tran.Dispose(); tran = 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Post([FromBody] FinanceAssetSoldoutDocViewModel vm) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } // Perform checks if (vm.HID <= 0) { return(BadRequest("Not HID inputted")); } if (vm.AssetAccountID <= 0) { return(BadRequest("Asset Account is invalid")); } if (vm.TranAmount <= 0) { return(BadRequest("Amount is less than zero")); } if (vm.Items.Count <= 0) { return(BadRequest("No items inputted")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } // Construct the Doc. var vmFIDoc = new FinanceDocumentUIViewModel(); vmFIDoc.DocType = FinanceDocTypeViewModel.DocType_AssetSoldOut; vmFIDoc.Desp = vm.Desp; vmFIDoc.TranDate = vm.TranDate; vmFIDoc.HID = vm.HID; vmFIDoc.TranCurr = vm.TranCurr; Decimal totalAmt = 0; var maxItemID = 0; foreach (var di in vm.Items) { if (di.ItemID <= 0 || di.TranAmount == 0 || di.AccountID <= 0 || di.TranType != FinanceTranTypeViewModel.TranType_AssetSoldoutIncome || (di.ControlCenterID <= 0 && di.OrderID <= 0)) { return(BadRequest("Invalid input data in items!")); } totalAmt += di.TranAmount; vmFIDoc.Items.Add(di); if (maxItemID < di.ItemID) { maxItemID = di.ItemID; } } if (Decimal.Compare(totalAmt, vm.TranAmount) != 0) { return(BadRequest("Amount is not even")); } var nitem = new FinanceDocumentItemUIViewModel(); nitem.ItemID = ++maxItemID; nitem.AccountID = vm.AssetAccountID; nitem.TranAmount = vm.TranAmount; nitem.Desp = vmFIDoc.Desp; nitem.TranType = FinanceTranTypeViewModel.TranType_AssetSoldout; if (vm.ControlCenterID.HasValue) { nitem.ControlCenterID = vm.ControlCenterID.Value; } if (vm.OrderID.HasValue) { nitem.OrderID = vm.OrderID.Value; } vmFIDoc.Items.Add(nitem); // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; SqlTransaction tran = null; String queryString = ""; Int32 nNewDocID = -1; String strErrMsg = ""; Decimal dCurrBalance = 0; HttpStatusCode errorCode = HttpStatusCode.OK; try { // Basic check again FinanceDocumentController.FinanceDocumentBasicCheck(vmFIDoc); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } // Perfrom the doc. validation await FinanceDocumentController.FinanceDocumentBasicValidationAsync(vmFIDoc, conn); // Additional checks SqlCommand cmdAddCheck = null; SqlDataReader readerAddCheck = null; try { // Check 1: check account is a valid asset? String strsqls = @"SELECT [t_fin_account].[STATUS] ,[t_fin_account_ext_as].[REFDOC_SOLD] AS [ASREFDOC_SOLD] FROM [dbo].[t_fin_account] INNER JOIN [dbo].[t_fin_account_ext_as] ON [t_fin_account].[ID] = [t_fin_account_ext_as].[ACCOUNTID] WHERE [t_fin_account].[ID] = " + vm.AssetAccountID.ToString() + " AND [t_fin_account].[HID] = " + vm.HID.ToString(); cmdAddCheck = new SqlCommand(strsqls, conn); readerAddCheck = await cmdAddCheck.ExecuteReaderAsync(); if (readerAddCheck.HasRows) { while (readerAddCheck.Read()) { if (!readerAddCheck.IsDBNull(0)) { var acntStatus = (FinanceAccountStatus)readerAddCheck.GetByte(0); if (acntStatus != FinanceAccountStatus.Normal) { throw new Exception("Account status is not normal"); } } else { // Status is NULL stands for Active Status // throw new Exception("Account status is not normal"); } if (!readerAddCheck.IsDBNull(1)) { throw new Exception("Account has soldout doc already"); } break; } } readerAddCheck.Close(); readerAddCheck = null; cmdAddCheck.Dispose(); cmdAddCheck = null; // Check 2: check the inputted date is valid > must be the later than all existing transactions; strsqls = @"SELECT MAX(t_fin_document.TRANDATE) FROM [dbo].[t_fin_document_item] INNER JOIN [dbo].[t_fin_document] ON [dbo].[t_fin_document_item].[DOCID] = [dbo].[t_fin_document].[ID] WHERE [dbo].[t_fin_document_item].[ACCOUNTID] = " + vm.AssetAccountID.ToString(); cmdAddCheck = new SqlCommand(strsqls, conn); readerAddCheck = await cmdAddCheck.ExecuteReaderAsync(); if (readerAddCheck.HasRows) { while (readerAddCheck.Read()) { var latestdate = readerAddCheck.GetDateTime(0); if (vm.TranDate.Date < latestdate.Date) { throw new Exception("Invalid date"); } break; } } else { throw new Exception("Invalid account - no doc items"); } readerAddCheck.Close(); readerAddCheck = null; cmdAddCheck.Dispose(); cmdAddCheck = null; // Check 3. Fetch current balance strsqls = @"SELECT [balance] FROM [dbo].[v_fin_report_bs] WHERE [accountid] = " + vm.AssetAccountID.ToString(); cmdAddCheck = new SqlCommand(strsqls, conn); readerAddCheck = await cmdAddCheck.ExecuteReaderAsync(); if (readerAddCheck.HasRows) { while (readerAddCheck.Read()) { dCurrBalance = readerAddCheck.GetDecimal(0); if (dCurrBalance <= 0) { throw new Exception("Balance is zero"); } break; } } else { throw new Exception("Invalid account - no doc items"); } readerAddCheck.Close(); readerAddCheck = null; cmdAddCheck.Dispose(); cmdAddCheck = null; var ncmprst = Decimal.Compare(dCurrBalance, vm.TranAmount); if (ncmprst > 0) { var nitem2 = new FinanceDocumentItemUIViewModel(); nitem2.ItemID = ++maxItemID; nitem2.AccountID = vm.AssetAccountID; nitem2.TranAmount = Decimal.Subtract(dCurrBalance, vm.TranAmount); nitem2.Desp = vmFIDoc.Desp; nitem2.TranType = FinanceTranTypeViewModel.TranType_AssetValueDecrease; if (vm.ControlCenterID.HasValue) { nitem2.ControlCenterID = vm.ControlCenterID.Value; } if (vm.OrderID.HasValue) { nitem2.OrderID = vm.OrderID.Value; } vmFIDoc.Items.Add(nitem2); } else if (ncmprst < 0) { var nitem2 = new FinanceDocumentItemUIViewModel(); nitem2.ItemID = ++maxItemID; nitem2.AccountID = vm.AssetAccountID; nitem2.TranAmount = Decimal.Subtract(vm.TranAmount, dCurrBalance); nitem2.Desp = vmFIDoc.Desp; nitem2.TranType = FinanceTranTypeViewModel.TranType_AssetValueIncrease; if (vm.ControlCenterID.HasValue) { nitem2.ControlCenterID = vm.ControlCenterID.Value; } if (vm.OrderID.HasValue) { nitem2.OrderID = vm.OrderID.Value; } vmFIDoc.Items.Add(nitem2); } } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } finally { if (readerAddCheck != null) { readerAddCheck.Close(); readerAddCheck = null; } if (cmdAddCheck != null) { cmdAddCheck.Dispose(); cmdAddCheck = null; } } // Begin the modification tran = conn.BeginTransaction(); // First, craete the doc header => nNewDocID queryString = HIHDBUtility.GetFinDocHeaderInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDoc, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewDocID = (Int32)idparam.Value; cmd.Dispose(); cmd = null; // Then, creating the items foreach (FinanceDocumentItemUIViewModel ivm in vmFIDoc.Items) { queryString = HIHDBUtility.GetFinDocItemInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // Tags if (ivm.TagTerms.Count > 0) { // Create tags foreach (var term in ivm.TagTerms) { queryString = HIHDBUtility.GetTagInsertString(); cmd = new SqlCommand(queryString, conn, tran); HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } } } // Third, update the Account's status queryString = HIHDBUtility.GetFinanceAccountStatusUpdateString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; // Close this account HIHDBUtility.BindFinAccountStatusUpdateParameter(cmd, FinanceAccountStatus.Closed, vm.AssetAccountID, vm.HID, usrName); nRst = await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // Fourth, Update the Asset account part for sold doc queryString = HIHDBUtility.GetFinanceAccountAssetUpdateSoldDocString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinAccountAssetUpdateSoldDocParameter(cmd, nNewDocID, vm.AssetAccountID); nRst = await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // Do the commit tran.Commit(); // Update the buffer // Account List try { var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null); this._cache.Remove(cacheKey); } catch (Exception) { // Do nothing here. } // Account itself try { var cacheKey = String.Format(CacheKeys.FinAccount, vm.HID, vm.AssetAccountID); this._cache.Remove(cacheKey); } catch (Exception) { // Do nothing here. } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } if (tran != null) { tran.Rollback(); } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } // Return nothing return(Ok(nNewDocID)); }
public async Task <IActionResult> Get([FromQuery] Int32 hid = 0, Int32 nfrqtype = 1) { if (hid <= 0) { return(BadRequest("HID is missing")); } FinanceDocCreatedFrequencyType frqtype = FinanceDocCreatedFrequencyType.Weekly; DateTime dtBgn = DateTime.Today; if (nfrqtype == 1) { frqtype = FinanceDocCreatedFrequencyType.Weekly; dtBgn = dtBgn.AddMonths(-1); } else if (nfrqtype == 2) { frqtype = FinanceDocCreatedFrequencyType.Monthly; dtBgn = dtBgn.AddYears(-1); } else { return(BadRequest("Frequence type is not supported")); } String usrName = ""; String scopeFilter = String.Empty; try { if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } } catch { return(BadRequest("Not valid HTTP HEAD: User and Scope Failed!")); } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("No user found")); } List <FinanceDocCreatedFrequenciesByUserViewModel> listVm = new List <FinanceDocCreatedFrequenciesByUserViewModel>(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { var cacheKey = String.Format(CacheKeys.FinDocCrtFrqByUser, hid); if (_cache.TryGetValue <List <FinanceDocCreatedFrequenciesByUserViewModel> >(cacheKey, out listVm)) { // Do nothing } else { listVm = new List <FinanceDocCreatedFrequenciesByUserViewModel>(); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } queryString = HIHDBUtility.getFinDocCreatedFrequenciesByUserQueryString(frqtype, hid, dtBgn, DateTime.Today); #if DEBUG System.Diagnostics.Debug.WriteLine("FinanceDocCreatedFrequenciesByUserController, SQL generated: " + queryString); #endif cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { FinanceDocCreatedFrequenciesByUserViewModel vm = new FinanceDocCreatedFrequenciesByUserViewModel(); HIHDBUtility.FinDocCreatedFrequenciesByUser_DB2VM(frqtype, reader, vm); listVm.Add(vm); } } _cache.Set <List <FinanceDocCreatedFrequenciesByUserViewModel> >(cacheKey, listVm, TimeSpan.FromMinutes(20)); } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listVm, setting)); }
public async Task <IActionResult> Post([FromBody] RecurEventViewModel vm) { if (vm == null) { return(BadRequest("No data is inputted")); } if (vm.HID <= 0) { return(BadRequest("Home not defined")); } if (vm.Name != null) { vm.Name = vm.Name.Trim(); } if (String.IsNullOrEmpty(vm.Name)) { return(BadRequest("Name is a must!")); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; Int32 nNewID = -1; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } // Get the simulate items EventGenerationInputViewModel datInput = new EventGenerationInputViewModel(); datInput.Name = vm.Name; datInput.RptType = vm.RptType; datInput.StartTimePoint = vm.StartTimePoint; datInput.EndTimePoint = vm.EndTimePoint; List <EventGenerationResultViewModel> listRsts = EventUtility.GenerateEvents(datInput); if (listRsts.Count <= 0) { return(BadRequest("Failed to generate recur items")); } SqlTransaction tran = null; try { queryString = @"SELECT [ID] FROM [dbo].[t_event_recur] WHERE [Name] = N'" + vm.Name + "'"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { Int32 nDuplicatedID = -1; while (reader.Read()) { nDuplicatedID = reader.GetInt32(0); break; } strErrMsg = "Object with name already exists: " + nDuplicatedID.ToString(); errorCode = HttpStatusCode.BadRequest; throw new Exception(strErrMsg); } else { reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; tran = conn.BeginTransaction(); // Now go ahead for the creating queryString = HIHDBUtility.Event_GetRecurEventInsertString(); cmd = new SqlCommand(queryString, conn); cmd.Transaction = tran; HIHDBUtility.Event_BindRecurEventInsertParameters(cmd, vm, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewID = (Int32)idparam.Value; cmd.Dispose(); cmd = null; // Go for the recur item creation foreach (var gitem in listRsts) { queryString = HIHDBUtility.Event_GetNormalEventInsertString(false); cmd = new SqlCommand(queryString, conn); cmd.Transaction = tran; var vmEvent = new EventViewModel(); vmEvent.EndTimePoint = gitem.EndTimePoint; vmEvent.StartTimePoint = gitem.StartTimePoint; vmEvent.RefRecurrID = nNewID; vmEvent.IsPublic = vm.IsPublic; vmEvent.Name = gitem.Name; vmEvent.HID = vm.HID; vmEvent.Content = vm.Content; vmEvent.Assignee = vm.Assignee; HIHDBUtility.Event_BindNormalEventInsertParameters(cmd, vmEvent, usrName); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } tran.Commit(); } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (tran != null) { tran.Rollback(); } 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.Close(); conn.Dispose(); } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } vm.ID = nNewID; var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Get(int id, [FromQuery] Int32 hid = 0) { if (hid <= 0) { return(BadRequest("HID is missing")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } EventHabitViewModel vm = new EventHabitViewModel(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } queryString = HIHDBUtility.Event_GetEventHabitQueryString(false, usrName, hid, null, null, id); cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); // Detail while (reader.Read()) { EventHabitDetail detail = new EventHabitDetail(); HIHDBUtility.Event_HabitDB2VM(reader, vm, detail, false); vm.Details.Add(detail); } reader.NextResult(); // Checkin while (reader.Read()) { EventHabitCheckInViewModel civm = new EventHabitCheckInViewModel(); civm.ID = reader.GetInt32(0); civm.TranDate = reader.GetDateTime(1); if (!reader.IsDBNull(2)) { civm.Score = reader.GetInt32(2); } if (!reader.IsDBNull(3)) { civm.Comment = reader.GetString(3); } if (!reader.IsDBNull(4)) { civm.CreatedBy = reader.GetString(4); } if (!reader.IsDBNull(5)) { civm.CreatedAt = reader.GetDateTime(5); } if (!reader.IsDBNull(6)) { civm.UpdatedBy = reader.GetString(6); } if (!reader.IsDBNull(7)) { civm.UpdatedAt = reader.GetDateTime(7); } vm.CheckInLogs.Add(civm); } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Patch(int id, [FromQuery] int hid, [FromBody] JsonPatchDocument <EventViewModel> patch) { if (patch == null || id <= 0) { return(BadRequest("No data is inputted")); } if (hid <= 0) { return(BadRequest("No home is inputted")); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } EventViewModel vm = new EventViewModel(); try { queryString = HIHDBUtility.Event_GetNormalEventQueryString(false, usrName, null, null, null, id); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; // Re-throw } // Optimization logic for Mark as complete if (patch.Operations.Count == 1 && patch.Operations[0].path == "/completeTimePoint") { // Only update the complete time queryString = HIHDBUtility.Event_GetNormalEventMarkAsCompleteString(); SqlCommand cmdupdate = new SqlCommand(queryString, conn); HIHDBUtility.Event_BindNormalEventMarkAsCompleteParameters(cmdupdate, DateTime.Parse((string)patch.Operations[0].value), usrName, id); await cmdupdate.ExecuteNonQueryAsync(); } else { cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (!reader.HasRows) { errorCode = HttpStatusCode.BadRequest; strErrMsg = "Object with ID doesnot exist: " + id.ToString(); throw new Exception(strErrMsg); } else { while (reader.Read()) { HIHDBUtility.Event_DB2VM(reader, vm, false); } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Now go ahead for the update //var patched = vm.Copy(); patch.ApplyTo(vm, ModelState); if (!ModelState.IsValid) { return(new BadRequestObjectResult(ModelState)); } queryString = HIHDBUtility.Event_GetNormalEventUpdateString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.Event_BindNormalEventUpdateParameters(cmd, vm, usrName); Int32 nRst = await cmd.ExecuteNonQueryAsync(); } } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Post([FromBody] EventHabitViewModel vm, [FromQuery] Boolean geneMode = false) { if (vm == null) { return(BadRequest("No data is inputted")); } if (vm.Name != null) { vm.Name = vm.Name.Trim(); } if (String.IsNullOrEmpty(vm.Name)) { return(BadRequest("Name is a must!")); } // Check the details' generation EventGenerationInputViewModel datInput = new EventGenerationInputViewModel(); datInput.StartTimePoint = vm.StartDate; datInput.EndTimePoint = vm.EndDate; datInput.Name = vm.Name; datInput.RptType = vm.RptType; List <EventGenerationResultViewModel> listDetails = null; try { listDetails = EventUtility.GenerateHabitDetails(datInput); } catch (Exception exp) { return(BadRequest(exp.Message)); } if (listDetails.Count <= 0) { return(BadRequest("Failed to generate the details")); } // For generation mode, just return the results if (geneMode) { return(Json(listDetails)); } // For non-generation mode, go ahead for creating if (!ModelState.IsValid) { return(BadRequest("Model status is invalid")); } if (vm.Count <= 0) { return(BadRequest("Count is must")); } Boolean unitMode = Startup.UnitTestMode; SqlConnection conn = null; String queryString = ""; Int32 nNewID = -1; String strErrMsg = ""; String usrName = String.Empty; if (unitMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usr = User.FindFirst(c => c.Type == "sub"); if (usr != null) { usrName = usr.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User is not recognized")); } } SqlCommand cmd = null; SqlTransaction tran = null; HttpStatusCode errorCode = HttpStatusCode.OK; try { queryString = @"SELECT [ID] FROM [dbo].[t_event_habit] WHERE [Name] = N'" + vm.Name + "'"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; // Re-throw } cmd = new SqlCommand(queryString, conn); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { Int32 nDuplicatedID = -1; while (reader.Read()) { nDuplicatedID = reader.GetInt32(0); break; } strErrMsg = "Object with name already exists: " + nDuplicatedID.ToString(); errorCode = HttpStatusCode.BadRequest; throw new Exception(strErrMsg); } else { reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Now go ahead for the creating queryString = HIHDBUtility.Event_GetEventHabitInsertString(); tran = conn.BeginTransaction(); cmd = new SqlCommand(queryString, conn); cmd.Transaction = tran; HIHDBUtility.Event_BindEventHabitInsertParameters(cmd, vm, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewID = (Int32)idparam.Value; // Then go to the details. foreach (var detail in listDetails) { EventHabitDetail detailVM = new EventHabitDetail(); detailVM.HabitID = nNewID; detailVM.StartDate = detail.StartTimePoint; detailVM.EndDate = detail.EndTimePoint; queryString = HIHDBUtility.Event_GetEventHabitDetailInsertString(); cmd.Dispose(); cmd = null; cmd = new SqlCommand(queryString, conn, tran); HIHDBUtility.Event_BindEventHabitDetailInsertParameter(cmd, detailVM); await cmd.ExecuteNonQueryAsync(); vm.Details.Add(detailVM); } tran.Commit(); } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif if (tran != null) { tran.Rollback(); } strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (cmd != null) { cmd.Dispose(); cmd = null; } if (tran != null) { tran.Dispose(); tran = 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } vm.ID = nNewID; var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Post([FromQuery] Int32 hid, Int32 docid) { // The post here is: // 1. Post a normal document with the content from this template doc // 2. Update the template doc with REFDOCID // Basic check if (hid <= 0 || docid <= 0) { return(BadRequest("No data inputted!")); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; SqlTransaction tran = null; String queryString = String.Empty; String strErrMsg = String.Empty; FinanceTmpDocDPViewModel vmTmpDoc = new FinanceTmpDocDPViewModel(); HomeDefViewModel vmHome = new HomeDefViewModel(); FinanceDocumentUIViewModel vmFIDOC = new FinanceDocumentUIViewModel(); HttpStatusCode errorCode = HttpStatusCode.OK; String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check: HID, it requires more info than just check, so it implemented it if (hid != 0) { String strHIDCheck = HIHDBUtility.getHomeDefQueryString() + " WHERE [ID]= @hid AND [USER] = @user"; cmd = new SqlCommand(strHIDCheck, conn); cmd.Parameters.AddWithValue("@hid", hid); cmd.Parameters.AddWithValue("@user", usrName); reader = await cmd.ExecuteReaderAsync(); if (!reader.HasRows) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Not home found!"); } else { while (reader.Read()) { HIHDBUtility.HomeDef_DB2VM(reader, vmHome); // It shall be only one entry if found! break; } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; } if (vmHome == null || String.IsNullOrEmpty(vmHome.BaseCurrency) || vmHome.ID != hid) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Home Definition is invalid"); } // Check: DocID String checkString = HIHDBUtility.getFinanceDocADPListQueryString() + " WHERE [DOCID] = " + docid.ToString() + " AND [HID] = " + hid.ToString(); cmd = new SqlCommand(checkString, conn); reader = cmd.ExecuteReader(); if (!reader.HasRows) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Invalid Doc ID inputted: " + docid.ToString()); } else { while (reader.Read()) { HIHDBUtility.FinTmpDocADP_DB2VM(reader, vmTmpDoc); // It shall be only one entry if found! break; } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Check: Tmp doc has posted or not? if (vmTmpDoc == null || (vmTmpDoc.RefDocID.HasValue && vmTmpDoc.RefDocID.Value > 0)) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Tmp Doc not existed yet or has been posted"); } if (!vmTmpDoc.ControlCenterID.HasValue && !vmTmpDoc.OrderID.HasValue) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Tmp doc lack of control center or order"); } if (vmTmpDoc.TranAmount == 0) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Tmp doc lack of amount"); } // Now go ahead for the creating tran = conn.BeginTransaction(); cmd = null; Int32 nNewDocID = 0; vmFIDOC.Desp = vmTmpDoc.Desp; vmFIDOC.DocType = FinanceDocTypeViewModel.DocType_Normal; vmFIDOC.HID = hid; //vmFIDOC.TranAmount = vmTmpDoc.TranAmount; vmFIDOC.TranCurr = vmHome.BaseCurrency; vmFIDOC.TranDate = vmTmpDoc.TranDate; vmFIDOC.CreatedAt = DateTime.Now; FinanceDocumentItemUIViewModel vmItem = new FinanceDocumentItemUIViewModel { AccountID = vmTmpDoc.AccountID }; if (vmTmpDoc.ControlCenterID.HasValue) { vmItem.ControlCenterID = vmTmpDoc.ControlCenterID.Value; } if (vmTmpDoc.OrderID.HasValue) { vmItem.OrderID = vmTmpDoc.OrderID.Value; } vmItem.Desp = vmTmpDoc.Desp; vmItem.ItemID = 1; vmItem.TranAmount = vmTmpDoc.TranAmount; vmItem.TranType = vmTmpDoc.TranType; vmFIDOC.Items.Add(vmItem); // Now go ahead for the creating queryString = HIHDBUtility.GetFinDocHeaderInsertString(); // Header cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDOC, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewDocID = (Int32)idparam.Value; vmFIDOC.ID = nNewDocID; cmd.Dispose(); cmd = null; // Then, creating the items foreach (FinanceDocumentItemUIViewModel ivm in vmFIDOC.Items) { queryString = HIHDBUtility.GetFinDocItemInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } // Then, update the template doc queryString = @"UPDATE [dbo].[t_fin_tmpdoc_dp] SET [REFDOCID] = @REFDOCID ,[UPDATEDBY] = @UPDATEDBY ,[UPDATEDAT] = @UPDATEDAT WHERE [HID] = @HID AND [DOCID] = @DOCID"; cmd = new SqlCommand(queryString, conn) { Transaction = tran }; cmd.Parameters.AddWithValue("@REFDOCID", nNewDocID); cmd.Parameters.AddWithValue("@UPDATEDBY", usrName); cmd.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now); cmd.Parameters.AddWithValue("@HID", hid); cmd.Parameters.AddWithValue("@DOCID", docid); await cmd.ExecuteNonQueryAsync(); tran.Commit(); // Update the buffer of the relevant Account! var cacheAccountKey = String.Format(CacheKeys.FinAccount, hid, vmTmpDoc.AccountID); this._cache.Remove(cacheAccountKey); } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif if (tran != null) { tran.Rollback(); } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vmFIDOC, setting)); }
public async Task <IActionResult> Post([FromBody] LearnCategoryViewModel vm) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } if (vm.Name != null) { vm.Name = vm.Name.Trim(); } if (String.IsNullOrEmpty(vm.Name)) { return(BadRequest("Name is a must!")); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; Int32 nNewID = -1; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { queryString = @"SELECT [ID] FROM [dbo].[t_learn_ctgy] WHERE [Name] = N'" + vm.Name + "'"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user if (vm.HID.HasValue && vm.HID.Value > 0) { try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID.Value, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { Int32 nDuplicatedID = -1; while (reader.Read()) { nDuplicatedID = reader.GetInt32(0); break; } errorCode = HttpStatusCode.BadRequest; throw new Exception("Object with name already exists: " + nDuplicatedID.ToString()); } else { reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Now go ahead for the creating queryString = HIHDBUtility.getLearnCategoryInsertString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.bindLearnCategoryInsertParameter(cmd, vm, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewID = (Int32)idparam.Value; } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } vm.ID = nNewID; var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Post([FromQuery] Int32 hid, Int32 loanAccountID, Int32?tmpdocid, [FromBody] FinanceDocumentUIViewModel repaydoc) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } // The post here is: // 1. Post a repayment document with the content from this template doc // 2. Update the template doc with REFDOCID // 3. If the account balance is zero, close the account; // Basic check if (hid <= 0 || (tmpdocid.HasValue && tmpdocid.Value <= 0) || loanAccountID <= 0 || repaydoc == null || repaydoc.HID != hid || repaydoc.DocType != FinanceDocTypeViewModel.DocType_Repay) { return(BadRequest("No data inputted!")); } SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; SqlTransaction tran = null; String queryString = String.Empty; String strErrMsg = String.Empty; HttpStatusCode errorCode = HttpStatusCode.OK; Decimal acntBalance = 0M; String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } // Update the database FinanceTmpDocLoanViewModel vmTmpDoc = new FinanceTmpDocLoanViewModel(); HomeDefViewModel vmHome = new HomeDefViewModel(); FinanceAccountUIViewModel vmAccount = new FinanceAccountUIViewModel(); try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check: HID, it requires more info than just check, so it implemented it try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } // Check: DocID String checkString = ""; if (tmpdocid.HasValue) { checkString = HIHDBUtility.GetFinanceDocLoanListQueryString() + " WHERE [DOCID] = " + tmpdocid.Value.ToString() + " AND [HID] = " + hid.ToString(); cmd = new SqlCommand(checkString, conn); reader = cmd.ExecuteReader(); if (!reader.HasRows) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Invalid Doc ID inputted: " + tmpdocid.Value.ToString()); } else { while (reader.Read()) { HIHDBUtility.FinTmpDocLoan_DB2VM(reader, vmTmpDoc); // It shall be only one entry if found! break; } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; } // Check: Tmp doc has posted or not? if (vmTmpDoc == null || (vmTmpDoc.RefDocID.HasValue && vmTmpDoc.RefDocID.Value > 0) || vmTmpDoc.AccountID != loanAccountID) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Tmp Doc not existed yet or has been posted"); } // Check: Loan account checkString = HIHDBUtility.GetFinanceLoanAccountQueryString(hid, loanAccountID); cmd = new SqlCommand(checkString, conn); reader = cmd.ExecuteReader(); if (!reader.HasRows) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Loan account read failed based on Doc ID inputted: " + tmpdocid.ToString()); } else { if (reader.HasRows) { while (reader.Read()) { HIHDBUtility.FinAccountHeader_DB2VM(reader, vmAccount, 0); break; } } reader.NextResult(); vmAccount.ExtraInfo_Loan = new FinanceAccountExtLoanViewModel(); if (reader.HasRows) { while (reader.Read()) { HIHDBUtility.FinAccountLoan_DB2VM(reader, vmAccount.ExtraInfo_Loan, 0); break; } } reader.NextResult(); if (reader.HasRows) { while (reader.Read()) { if (!reader.IsDBNull(0)) { acntBalance = reader.GetDecimal(0); } break; } } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Data validation - basic try { await FinanceDocumentController.FinanceDocumentBasicValidationAsync(repaydoc, conn); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } // Data validation - loan specific try { int ninvaliditems = 0; // Only four tran. types are allowed if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_BorrowFrom) { ninvaliditems = repaydoc.Items.Where(item => item.TranType != FinanceTranTypeViewModel.TranType_InterestOut && item.TranType != FinanceTranTypeViewModel.TranType_RepaymentOut && item.TranType != FinanceTranTypeViewModel.TranType_RepaymentIn) .Count(); } else if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_LendTo) { ninvaliditems = repaydoc.Items.Where(item => item.TranType != FinanceTranTypeViewModel.TranType_InterestIn && item.TranType != FinanceTranTypeViewModel.TranType_RepaymentOut && item.TranType != FinanceTranTypeViewModel.TranType_RepaymentIn) .Count(); } if (ninvaliditems > 0) { throw new Exception("Items with invalid tran type"); } // Check the amount decimal totalOut = repaydoc.Items.Where(item => item.TranType == FinanceTranTypeViewModel.TranType_RepaymentOut).Sum(item2 => item2.TranAmount); decimal totalIn = repaydoc.Items.Where(item => item.TranType == FinanceTranTypeViewModel.TranType_RepaymentIn).Sum(item2 => item2.TranAmount); //decimal totalintOut = repaydoc.Items.Where(item => (item.TranType == FinanceTranTypeViewModel.TranType_InterestOut)).Sum(item2 => item2.TranAmount); // New account balance if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_BorrowFrom) { acntBalance += totalOut; } else if (vmAccount.CtgyID == FinanceAccountCtgyViewModel.AccountCategory_LendTo) { acntBalance -= totalIn; } if (totalOut != totalIn) { throw new Exception("Amount is not equal!"); } } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } // Now go ahead for the creating tran = conn.BeginTransaction(); Int32 nNewDocID = 0; // Now go ahead for creating queryString = HIHDBUtility.GetFinDocHeaderInsertString(); // Header cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, repaydoc, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewDocID = (Int32)idparam.Value; repaydoc.ID = nNewDocID; cmd.Dispose(); cmd = null; // Then, creating the items foreach (FinanceDocumentItemUIViewModel ivm in repaydoc.Items) { queryString = HIHDBUtility.GetFinDocItemInsertString(); SqlCommand cmd2 = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocItemInsertParameter(cmd2, ivm, nNewDocID); await cmd2.ExecuteNonQueryAsync(); cmd2.Dispose(); cmd2 = null; } // Then, update the template doc queryString = @"UPDATE [dbo].[t_fin_tmpdoc_loan] SET [REFDOCID] = @REFDOCID ,[UPDATEDBY] = @UPDATEDBY ,[UPDATEDAT] = @UPDATEDAT WHERE [HID] = @HID AND [DOCID] = @DOCID"; SqlCommand cmdTmpDoc = new SqlCommand(queryString, conn) { Transaction = tran }; cmdTmpDoc.Parameters.AddWithValue("@REFDOCID", nNewDocID); cmdTmpDoc.Parameters.AddWithValue("@UPDATEDBY", usrName); cmdTmpDoc.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now); cmdTmpDoc.Parameters.AddWithValue("@HID", hid); cmdTmpDoc.Parameters.AddWithValue("@DOCID", tmpdocid); await cmdTmpDoc.ExecuteNonQueryAsync(); cmdTmpDoc.Dispose(); cmdTmpDoc = null; // Incase balance is zero, update the account status if (Decimal.Compare(acntBalance, 0) == 0) { queryString = HIHDBUtility.GetFinanceAccountStatusUpdateString(); SqlCommand cmdAccount = new SqlCommand(queryString, conn, tran); HIHDBUtility.BindFinAccountStatusUpdateParameter(cmdAccount, FinanceAccountStatus.Closed, loanAccountID, hid, usrName); await cmdAccount.ExecuteNonQueryAsync(); cmdAccount.Dispose(); cmdAccount = null; } tran.Commit(); // Update the buffer of the relevant Account! // Account List try { var cacheKey = String.Format(CacheKeys.FinAccountList, hid, null); this._cache.Remove(cacheKey); cacheKey = String.Format(CacheKeys.FinAccount, hid, loanAccountID); this._cache.Remove(cacheKey); } catch (Exception) { // Do nothing here. } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif if (tran != null) { tran.Rollback(); } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(repaydoc, setting)); }
public async Task <IActionResult> Get([FromRoute] int id, [FromQuery] Int32 hid = 0) { if (hid <= 0 || id <= 0) { return(BadRequest("Invalid ID or HID inputted")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } FinancePlanViewModel vm = new FinancePlanViewModel(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { queryString = HIHDBUtility.GetFinPlanSelectionString() + " WHERE [ID] = " + id.ToString() + " AND [HID] = " + hid.ToString(); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } cmd = new SqlCommand(queryString, conn); reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { while (reader.Read()) { HIHDBUtility.FinPlan_DB2VM(reader, vm); break; // Should only one result!!! } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Post([FromBody] FinancePlanViewModel vm) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } // Perform the checks if (vm.HID <= 0) { return(BadRequest("No HID inputted!")); } if (vm == null || vm.StartDate > vm.TargetDate || (vm.PlanType == FinancePlanTypeEnum.Account && (!vm.AccountID.HasValue || vm.AccountID.Value <= 0)) || (vm.PlanType == FinancePlanTypeEnum.AccountCategory && (!vm.AccountCategoryID.HasValue || vm.AccountCategoryID.Value <= 0)) || (vm.PlanType == FinancePlanTypeEnum.ControlCenter && (!vm.ControlCenterID.HasValue || vm.ControlCenterID.Value <= 0)) || (vm.PlanType == FinancePlanTypeEnum.TranType && (!vm.TranTypeID.HasValue || vm.TranTypeID.Value <= 0)) ) { return(BadRequest("Invalid data to create")); } String usrName = ""; try { if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } } catch { return(BadRequest("Not valid HTTP HEAD: User and Scope Failed!")); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; SqlTransaction tran = null; String queryString = ""; Int32 nNewPlanID = -1; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check HID assignment try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } if (vm.PlanType == FinancePlanTypeEnum.Account) { // Check the account queryString = @"SELECT [ID], [Status] FROM [dbo].[t_fin_account] WHERE [ID] = " + vm.AccountID.Value.ToString() + " AND [HID] = " + vm.HID.ToString(); cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (!reader.HasRows) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Account doesnot exist: " + vm.AccountID.Value.ToString()); } else { // Check the status await reader.ReadAsync(); if (!reader.IsDBNull(1)) { FinanceAccountStatus nAccountStatus = (FinanceAccountStatus)reader.GetByte(1); if (nAccountStatus == FinanceAccountStatus.Frozen || nAccountStatus == FinanceAccountStatus.Closed) { errorCode = HttpStatusCode.BadRequest; throw new Exception("Account status is invalid: " + vm.AccountID.Value.ToString()); } } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; // Now create the DB entry // Begin the transaction tran = conn.BeginTransaction(); // Now go ahead for the creating queryString = HIHDBUtility.GetFinPlanInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; vm.CreatedBy = usrName; vm.CreatedAt = DateTime.Now; HIHDBUtility.BindFinPlanInsertParameter(cmd, vm); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewPlanID = (Int32)idparam.Value; // Now commit it! tran.Commit(); // Update the buffer try { var cacheKey = String.Format(CacheKeys.FinPlanList, vm.HID); this._cache.Remove(cacheKey); } catch (Exception) { // Do nothing here. } } else if (vm.PlanType == FinancePlanTypeEnum.AccountCategory) { } else if (vm.PlanType == FinancePlanTypeEnum.ControlCenter) { } else if (vm.PlanType == FinancePlanTypeEnum.TranType) { } // Update the buffer // Account List try { var cacheKey = String.Format(CacheKeys.FinPlanList, vm.HID, null); this._cache.Remove(cacheKey); } catch (Exception) { // Do nothing here. } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif if (tran != null) { tran.Rollback(); } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } vm.ID = nNewPlanID; var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Patch(int id, [FromQuery] int hid, [FromBody] JsonPatchDocument <HomeMsgViewModel> patch) { if (patch == null || id <= 0) { return(BadRequest("No data is inputted")); } if (hid <= 0) { return(BadRequest("No home is inputted")); } // Update the database SqlConnection conn = null; SqlCommand cmd = null; String queryString = ""; HttpStatusCode errorCode = HttpStatusCode.OK; String strErrMsg = ""; var usr = User.FindFirst(c => c.Type == "sub"); String usrName = String.Empty; if (usr != null) { usrName = usr.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User is not recognized")); } HomeMsgViewModel vm = new HomeMsgViewModel(); try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; // Re-throw } // Optimization logic for Mark as complete if (patch.Operations.Count == 1 && patch.Operations[0].path == "/readFlag") { // Only update the complete time queryString = HIHDBUtility.HomeMsg_GetMarkAsReadUpdateString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.HomeMsg_BindMarkAsReadUpdateParameters(cmd, (Boolean)patch.Operations[0].value, id, hid); await cmd.ExecuteNonQueryAsync(); } else if (patch.Operations.Count == 1 && patch.Operations[0].path == "/receiverDeletion") { // Only update the complete time queryString = HIHDBUtility.HomeMsg_GetReceiverDeletionUpdateString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.HomeMsg_BindReceiverDeletionUpdateParameters(cmd, (Boolean)patch.Operations[0].value, id, hid); await cmd.ExecuteNonQueryAsync(); } else if (patch.Operations.Count == 1 && patch.Operations[0].path == "/senderDeletion") { // Only update the complete time queryString = HIHDBUtility.HomeMsg_GetSenderDeletionUpdateString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.HomeMsg_BindSenderDeletioUpdateParameters(cmd, (Boolean)patch.Operations[0].value, id, hid); await cmd.ExecuteNonQueryAsync(); } else { errorCode = HttpStatusCode.BadRequest; throw new Exception("Non support patch mode!"); } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Close(); conn.Dispose(); } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(vm, setting)); }
public async Task <IActionResult> Get([FromQuery] Int32 hid, Boolean skipPosted = true, DateTime?dtbgn = null, DateTime?dtend = null) { if (hid <= 0) { return(BadRequest("No HID inputted")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } List <FinanceTmpDocLoanViewModel> listVm = new List <FinanceTmpDocLoanViewModel>(); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { queryString = HIHDBUtility.GetFinanceDocLoanListQueryString() + " WHERE [HID] = @hid "; if (skipPosted) { queryString += " AND [REFDOCID] IS NULL "; } if (dtbgn.HasValue) { queryString += " AND [TRANDATE] >= @dtbgn "; } if (dtend.HasValue) { queryString += " AND [TRANDATE] <= @dtend "; } queryString += " ORDER BY [TRANDATE] DESC"; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } cmd = new SqlCommand(queryString, conn); cmd.Parameters.AddWithValue("@hid", hid); if (dtbgn.HasValue) { cmd.Parameters.AddWithValue("@dtbgn", dtbgn.Value); } if (dtbgn.HasValue) { cmd.Parameters.AddWithValue("@dtend", dtend.Value); } reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { FinanceTmpDocLoanViewModel dpvm = new FinanceTmpDocLoanViewModel(); HIHDBUtility.FinTmpDocLoan_DB2VM(reader, dpvm); listVm.Add(dpvm); } } } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listVm, setting)); }
public async Task <IActionResult> Get([FromQuery] Int32 hid, DateTime dtbgn, DateTime dtend) { if (hid <= 0) { return(BadRequest("HID is missing")); } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } List <EventHabitDetailWithCheckInViewModel> listVm = new List <EventHabitDetailWithCheckInViewModel>(); SqlConnection conn = null; SqlCommand cmd = null; String queryString = ""; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, hid, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } queryString = HIHDBUtility.Event_GetHabitDetailWithCheckInSearchString(); cmd = new SqlCommand(queryString, conn); HIHDBUtility.Event_BindHabitDetailWithCheckInSearchParameter(cmd, hid, dtbgn, dtend); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { EventHabitDetailWithCheckInViewModel vm = new EventHabitDetailWithCheckInViewModel(); HIHDBUtility.Event_HabitDetailWithCheckInDB2VM(reader, vm); listVm.Add(vm); } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } var setting = new Newtonsoft.Json.JsonSerializerSettings { DateFormatString = HIHAPIConstants.DateFormatPattern, ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() }; return(new JsonResult(listVm, setting)); }
public async Task <IActionResult> Post([FromBody] FinanceAssetBuyinDocViewModel vm) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } if (vm == null || vm.accountAsset == null) { return(BadRequest("No data is inputted")); } if (vm.HID <= 0) { return(BadRequest("Not HID inputted")); } // Do basic checks if (String.IsNullOrEmpty(vm.TranCurr) || String.IsNullOrEmpty(vm.accountAsset.Name) || (vm.IsLegacy.HasValue && vm.IsLegacy.Value && vm.Items.Count > 0) || ((!vm.IsLegacy.HasValue || (vm.IsLegacy.HasValue && !vm.IsLegacy.Value)) && vm.Items.Count <= 0) ) { return(BadRequest("Invalid input data")); } foreach (var di in vm.Items) { if (di.TranAmount == 0 || di.AccountID <= 0 || di.TranType <= 0 || (di.ControlCenterID <= 0 && di.OrderID <= 0)) { return(BadRequest("Invalid input data in items!")); } } String usrName = String.Empty; if (Startup.UnitTestMode) { usrName = UnitTestUtility.UnitTestUser; } else { var usrObj = HIHAPIUtility.GetUserClaim(this); usrName = usrObj.Value; } if (String.IsNullOrEmpty(usrName)) { return(BadRequest("User cannot recognize")); } // Construct the Account var vmAccount = new FinanceAccountViewModel(); vmAccount.HID = vm.HID; vmAccount.Name = vm.accountAsset.Name; vmAccount.Status = FinanceAccountStatus.Normal; vmAccount.CtgyID = FinanceAccountCtgyViewModel.AccountCategory_Asset; vmAccount.ExtraInfo_AS = new FinanceAccountExtASViewModel(); vmAccount.Owner = vm.AccountOwner; vmAccount.Comment = vm.accountAsset.Name; vmAccount.ExtraInfo_AS.Name = vm.accountAsset.Name; vmAccount.ExtraInfo_AS.Comment = vm.accountAsset.Comment; vmAccount.ExtraInfo_AS.CategoryID = vm.accountAsset.CategoryID; // Construct the Doc. var vmFIDoc = new FinanceDocumentUIViewModel(); vmFIDoc.DocType = FinanceDocTypeViewModel.DocType_AssetBuyIn; vmFIDoc.Desp = vm.Desp; vmFIDoc.TranDate = vm.TranDate; vmFIDoc.HID = vm.HID; vmFIDoc.TranCurr = vm.TranCurr; var maxItemID = 0; if (vm.IsLegacy.HasValue && vm.IsLegacy.Value) { // Legacy account... } else { Decimal totalAmt = 0; foreach (var di in vm.Items) { if (di.ItemID <= 0 || di.TranAmount == 0 || di.AccountID <= 0 || (di.ControlCenterID <= 0 && di.OrderID <= 0)) { return(BadRequest("Invalid input data in items!")); } // Todo: new check the tran. type is an expense! totalAmt += di.TranAmount; vmFIDoc.Items.Add(di); if (maxItemID < di.ItemID) { maxItemID = di.ItemID; } } if (totalAmt != vm.TranAmount) { return(BadRequest("Amount is not even")); } } var nitem = new FinanceDocumentItemUIViewModel(); nitem.ItemID = ++maxItemID; nitem.AccountID = -1; nitem.TranAmount = vm.TranAmount; nitem.Desp = vmFIDoc.Desp; nitem.TranType = FinanceTranTypeViewModel.TranType_OpeningAsset; if (vm.ControlCenterID.HasValue) { nitem.ControlCenterID = vm.ControlCenterID.Value; } if (vm.OrderID.HasValue) { nitem.OrderID = vm.OrderID.Value; } vmFIDoc.Items.Add(nitem); // Update the database SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; SqlTransaction tran = null; String queryString = ""; Int32 nNewDocID = -1; String strErrMsg = ""; HttpStatusCode errorCode = HttpStatusCode.OK; try { // Basic check again - document level FinanceDocumentController.FinanceDocumentBasicCheck(vmFIDoc); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); // Check Home assignment with current user try { HIHAPIUtility.CheckHIDAssignment(conn, vm.HID, usrName); } catch (Exception) { errorCode = HttpStatusCode.BadRequest; throw; } // Perfrom the doc. validation await FinanceDocumentController.FinanceDocumentBasicValidationAsync(vmFIDoc, conn, -1); // 0) Start the trasnaction for modifications tran = conn.BeginTransaction(); // 1) craete the doc header => nNewDocID queryString = HIHDBUtility.GetFinDocHeaderInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocHeaderInsertParameter(cmd, vmFIDoc, usrName); SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam.Direction = ParameterDirection.Output; Int32 nRst = await cmd.ExecuteNonQueryAsync(); nNewDocID = (Int32)idparam.Value; vmFIDoc.ID = nNewDocID; cmd.Dispose(); cmd = null; // 2), create the new account => nNewAccountID queryString = HIHDBUtility.GetFinanceAccountHeaderInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinAccountInsertParameter(cmd, vmAccount, usrName); SqlParameter idparam2 = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int); idparam2.Direction = ParameterDirection.Output; nRst = await cmd.ExecuteNonQueryAsync(); vmAccount.ID = (Int32)idparam2.Value; cmd.Dispose(); cmd = null; // 3) create the Asset part of account vmAccount.ExtraInfo_AS.AccountID = vmAccount.ID; vmAccount.ExtraInfo_AS.RefDocForBuy = nNewDocID; queryString = HIHDBUtility.GetFinanceAccountAssetInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinAccountAssetInsertParameter(cmd, vmAccount.ExtraInfo_AS); nRst = await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // 4) create the doc items foreach (FinanceDocumentItemUIViewModel ivm in vmFIDoc.Items) { if (ivm.AccountID == -1) { ivm.AccountID = vmAccount.ID; } queryString = HIHDBUtility.GetFinDocItemInsertString(); cmd = new SqlCommand(queryString, conn) { Transaction = tran }; HIHDBUtility.BindFinDocItemInsertParameter(cmd, ivm, nNewDocID); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; // Tags if (ivm.TagTerms.Count > 0) { // Create tags foreach (var term in ivm.TagTerms) { queryString = HIHDBUtility.GetTagInsertString(); cmd = new SqlCommand(queryString, conn, tran); HIHDBUtility.BindTagInsertParameter(cmd, vm.HID, HIHTagTypeEnum.FinanceDocumentItem, nNewDocID, term, ivm.ItemID); await cmd.ExecuteNonQueryAsync(); cmd.Dispose(); cmd = null; } } } // 5) Do the commit tran.Commit(); // Update the buffer // Account List try { var cacheKey = String.Format(CacheKeys.FinAccountList, vm.HID, null); this._cache.Remove(cacheKey); } catch (Exception) { // Do nothing here. } // B.S. try { var cacheKey = String.Format(CacheKeys.FinReportBS, vm.HID); this._cache.Remove(cacheKey); } catch (Exception) { // Do nothing here. } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } if (tran != null) { tran.Rollback(); } } 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(strErrMsg)); default: return(StatusCode(500, strErrMsg)); } } return(Ok(nNewDocID)); }