public async Task <IActionResult> Delete(int id, [FromQuery] Int32 hid = 0)
{
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"));
}
// 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, hid, usrName);
}
catch (Exception)
{
errorCode = HttpStatusCode.BadRequest;
throw;
}
tran = conn.BeginTransaction();
// Question bank
queryString = @"DELETE FROM [dbo].[t_learn_qtn_bank] WHERE [ID] = @ID AND [HID] = @HID";
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@HID", hid);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Question bank sub
queryString = @"DELETE FROM [dbo].[t_learn_qtn_bank_sub] WHERE [QTNID] = @QTNID";
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
cmd.Parameters.AddWithValue("@QTNID", id);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
// Tags
queryString = @"DELETE FROM [dbo].[t_tag] WHERE [TagType] = @tagtype AND [TagID] = @tagid AND [HID] = @HID";
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
cmd.Parameters.AddWithValue("@tagtype", HIHTagTypeEnum.LearnQuestionBank);
cmd.Parameters.AddWithValue("@tagid", id);
cmd.Parameters.AddWithValue("@HID", hid);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
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));
}
}
return(Ok());
}
public async Task <IActionResult> Get([FromQuery] Int32 hid, 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 <LearnReportCtgyDateViewModel> listVm = new List <LearnReportCtgyDateViewModel>();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
queryString = @"SELECT [HID]
,[CATEGORY]
,[LEARNDATE]
,[LEARNCOUNT]
FROM [dbo].[v_lrn_ctgylrndate] WHERE [HID] = @hid ";
if (dtbgn.HasValue)
{
queryString += " AND [LEARNDATE] >= @dtbgn";
}
if (dtend.HasValue)
{
queryString += " AND [LEARNDATE] <= @dtend";
}
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())
{
LearnReportCtgyDateViewModel avm = new LearnReportCtgyDateViewModel
{
HID = reader.GetInt32(0),
Category = reader.GetInt32(1),
LearnDate = reader.GetDateTime(2),
LearnCount = reader.GetInt32(3)
};
listVm.Add(avm);
}
}
}
}
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> Post([FromBody] LibLocationViewModel vm)
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
var 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"));
}
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_lib_location] WHERE [Name] = N'" + vm.Name + "' AND [HID] = " + vm.HID.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;
}
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("Location with name already exists: " + nDuplicatedID.ToString());
}
else
{
reader.Dispose();
reader = null;
cmd.Dispose();
cmd = null;
// Now go ahead for the creating
queryString = @"INSERT INTO [dbo].[t_lib_location]
([HID]
,[Name]
,[IsDevice]
,[Desp]
,[CREATEDBY]
,[CREATEDAT])
VALUES
(@HID
,@Name
,@IsDevice
,@Desp
,@CREATEDBY
,@CREATEDAT); SELECT @Identity = SCOPE_IDENTITY();";
cmd = new SqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("@HID", vm.HID);
cmd.Parameters.AddWithValue("@Name", vm.Name);
cmd.Parameters.AddWithValue("@IsDevice", vm.IsDevice);
if (String.IsNullOrEmpty(vm.Desp))
{
cmd.Parameters.AddWithValue("@Desp", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Desp", vm.Desp);
}
cmd.Parameters.AddWithValue("@CREATEDBY", usrName);
cmd.Parameters.AddWithValue("@CREATEDAT", vm.CreatedAt);
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([FromBody] LearnEnSentenceViewModel vm)
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
var 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"));
}
if (vm.Sentence != null)
{
vm.Sentence = vm.Sentence.Trim();
}
if (String.IsNullOrEmpty(vm.Sentence))
{
return(BadRequest("Sentence is a must!"));
}
if (vm.Explains == null || vm.Explains.Count <= 0)
{
return(BadRequest("Explain is a must"));
}
// Update the database
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = "";
Int32 nNewID = -1;
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
queryString = @"SELECT [ID] FROM [dbo].[t_learn_ensent] WHERE [Word] = N'" + vm.Sentence + "' AND [HID] = " + vm.HID.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;
}
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 = @"INSERT INTO [dbo].[t_learn_ensent]
([HID]
,[Sentence]
,[CREATEDBY]
,[CREATEDAT])
VALUES (@HID
,@Sentence
,@CREATEDBY
,@CREATEDAT); SELECT @Identity = SCOPE_IDENTITY();";
tran = conn.BeginTransaction();
// Header
cmd = new SqlCommand(queryString, conn, tran);
cmd.Parameters.AddWithValue("@HID", vm.HID);
cmd.Parameters.AddWithValue("@Sentence", vm.Sentence);
cmd.Parameters.AddWithValue("@CREATEDBY", usrName);
cmd.Parameters.AddWithValue("@CREATEDAT", vm.CreatedAt);
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
Int32 nRst = await cmd.ExecuteNonQueryAsync();
nNewID = (Int32)idparam.Value;
vm.ID = nNewID;
cmd.Dispose();
cmd = null;
// Explains
foreach (var exp in vm.Explains)
{
queryString = @"INSERT INTO [dbo].[t_learn_ensentexp]
([SentID]
,[ExpID]
,[LangKey]
,[ExpDetail])
VALUES (@SentID
,@ExpID
,@LangKey
,@ExpDetail)";
cmd = new SqlCommand(queryString, conn, tran);
cmd.Parameters.AddWithValue("@SentID", vm.ID);
cmd.Parameters.AddWithValue("@ExpID", exp.ExpID);
cmd.Parameters.AddWithValue("@LangKey", exp.LanguageKey);
cmd.Parameters.AddWithValue("@ExpDetail", exp.Detail);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
// Related words
foreach (var rwid in vm.RelatedWordIDs)
{
queryString = @"INSERT INTO [dbo].[t_learn_ensent_word] ([SentID],[WordID]) VALUES (@SentID, @WordID)";
cmd = new SqlCommand(queryString, conn, tran);
cmd.Parameters.AddWithValue("@SentID", vm.ID);
cmd.Parameters.AddWithValue("@WordID", rwid);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
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 (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> Put(int id, [FromBody] LearnObjectViewModel 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"));
}
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;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
queryString = @"UPDATE [dbo].[t_learn_obj]
SET [CATEGORY] = @CTGY
,[NAME] = @NAME
,[CONTENT] = @CONTENT
,[UPDATEDBY] = @UPDATEDBY
,[UPDATEDAT] = @UPDATEDAT
WHERE [ID] = @OBJID";
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);
cmd.Parameters.AddWithValue("@CTGY", vm.CategoryID);
cmd.Parameters.AddWithValue("@NAME", vm.Name);
cmd.Parameters.AddWithValue("@CONTENT", vm.Content);
cmd.Parameters.AddWithValue("@UPDATEDBY", usrName);
cmd.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now);
cmd.Parameters.AddWithValue("@OBJID", vm.ID);
Int32 nRst = await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception exp)
{
System.Diagnostics.Debug.WriteLine(exp.Message);
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(vm, setting));
}
public async Task <IActionResult> Get([FromQuery] Int32 hid)
{
if (hid <= 0)
{
return(BadRequest("No home inputted"));
}
HomeKeyFigure figure = new HomeKeyFigure();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
String queryString = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
String strErrMsg = "";
String usrName = "";
try
{
var usrObj = HIHAPIUtility.GetUserClaim(this);
usrName = usrObj.Value;
}
catch
{
return(BadRequest("Not valid HTTP HEAD: User and Scope Failed!"));
}
try
{
queryString = this.getQueryString(hid, usrName);
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
cmd = new SqlCommand(queryString, conn);
reader = cmd.ExecuteReader();
// 1. Total assets and liability
if (reader.HasRows)
{
while (reader.Read())
{
if (reader.GetBoolean(0))
{
figure.TotalLiability = reader.GetDecimal(1);
}
else
{
figure.TotalAsset = reader.GetDecimal(1);
}
}
}
await reader.NextResultAsync();
// 2. Total assets and liability
if (reader.HasRows)
{
while (reader.Read())
{
if (reader.GetBoolean(0))
{
figure.TotalLiabilityUnderMyName = reader.GetDecimal(1);
}
else
{
figure.TotalAssetUnderMyName = reader.GetDecimal(1);
}
}
}
await reader.NextResultAsync();
// 3. Total unread message
if (reader.HasRows)
{
while (reader.Read())
{
figure.TotalUnreadMessage = reader.GetInt32(0);
}
}
await reader.NextResultAsync();
// 4. My uncomplated event
if (reader.HasRows)
{
while (reader.Read())
{
figure.MyUnCompletedEvents = reader.GetInt32(0);
}
}
await reader.NextResultAsync();
// 5. My completed events
if (reader.HasRows)
{
while (reader.Read())
{
figure.MyCompletedEvents = reader.GetInt32(0);
}
}
await reader.NextResultAsync();
}
}
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(figure, 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> 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([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> 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> 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> 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> Get([FromQuery] Int32 hid, String tgtcurr)
{
if (hid <= 0)
{
return(BadRequest("No Home 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 <FinanceDocPlanExgRateViewModel> listVMs = new List <FinanceDocPlanExgRateViewModel>();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
queryString = @"SELECT [ID]
,[HID]
,[DOCTYPE]
,[TRANDATE]
,[TRANCURR]
,[DESP]
,[EXGRATE]
,[EXGRATE_PLAN]
,[EXGRATE_PLAN2]
,[TRANCURR2]
,[EXGRATE2]
FROM [dbo].[t_fin_document]
WHERE [HID] = @hid
AND ( ( [EXGRATE_PLAN] = 1 AND [TRANCURR] = @curr )
OR ( [EXGRATE_PLAN2] = 1 AND [TRANCURR2] = @curr ) )" ;
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);
cmd.Parameters.AddWithValue("@curr", tgtcurr);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
FinanceDocPlanExgRateViewModel avm = new FinanceDocPlanExgRateViewModel();
Int32 idx = 0;
avm.DocID = reader.GetInt32(idx++);
avm.HID = reader.GetInt32(idx++);
avm.DocType = reader.GetInt16(idx++);
avm.TranDate = reader.GetDateTime(idx++);
avm.TranCurr = reader.GetString(idx++);
avm.Desp = reader.GetString(idx++);
if (reader.IsDBNull(idx))
{
++idx;
}
else
{
avm.ExgRate = reader.GetDecimal(idx++);
}
if (reader.IsDBNull(idx))
{
++idx;
}
else
{
avm.ExgRate_Plan = reader.GetBoolean(idx++);
}
if (reader.IsDBNull(idx))
{
++idx;
}
else
{
avm.ExgRate_Plan2 = reader.GetBoolean(idx++);
}
if (reader.IsDBNull(idx))
{
++idx;
}
else
{
avm.TranCurr2 = reader.GetString(idx++);
}
if (reader.IsDBNull(idx))
{
++idx;
}
else
{
avm.ExgRate2 = reader.GetDecimal(idx++);
}
listVMs.Add(avm);
}
}
}
}
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(listVMs, setting));
}
public async Task <IActionResult> Post([FromBody] FinanceDocPlanExgRateForUpdViewModel vm)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (vm.HID <= 0)
{
return(BadRequest("No Home 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 <FinanceDocPlanExgRateViewModel> listVMs = new List <FinanceDocPlanExgRateViewModel>();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
// Basic check
if (String.IsNullOrEmpty(vm.TargetCurrency))
{
return(BadRequest());
}
if (vm.ExchangeRate <= 0)
{
return(BadRequest());
}
if (vm.DocIDs.Count <= 0)
{
return(BadRequest());
}
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();
foreach (var did in vm.DocIDs)
{
queryString = @"UPDATE [dbo].[t_fin_document]
SET [EXGRATE] = @EXGRATE
,[EXGRATE_PLAN] = @EXGRATE_PLAN
,[UPDATEDBY] = @UPDATEDBY
,[UPDATEDAT] = @UPDATEDAT
WHERE [ID] = @id
AND [TRANCURR] = @tcurr
AND [EXGRATE_PLAN] = @isplan";
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
cmd.Parameters.AddWithValue("@EXGRATE", vm.ExchangeRate);
cmd.Parameters.AddWithValue("@EXGRATE_PLAN", false);
cmd.Parameters.AddWithValue("@UPDATEDBY", usrName);
cmd.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now);
cmd.Parameters.AddWithValue("@id", did);
cmd.Parameters.AddWithValue("@tcurr", vm.TargetCurrency);
cmd.Parameters.AddWithValue("@isplan", true);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
queryString = @"UPDATE [dbo].[t_fin_document]
SET [EXGRATE2] = @EXGRATE
,[EXGRATE_PLAN2] = @EXGRATE_PLAN
,[UPDATEDBY] = @UPDATEDBY
,[UPDATEDAT] = @UPDATEDAT
WHERE [ID] = @id
AND [TRANCURR2] = @tcurr
AND [EXGRATE_PLAN2] = @isplan";
cmd = new SqlCommand(queryString, conn)
{
Transaction = tran
};
cmd.Parameters.AddWithValue("@EXGRATE", vm.ExchangeRate);
cmd.Parameters.AddWithValue("@EXGRATE_PLAN", false);
cmd.Parameters.AddWithValue("@UPDATEDBY", usrName);
cmd.Parameters.AddWithValue("@UPDATEDAT", DateTime.Now);
cmd.Parameters.AddWithValue("@id", did);
cmd.Parameters.AddWithValue("@tcurr", vm.TargetCurrency);
cmd.Parameters.AddWithValue("@isplan", true);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
cmd = null;
}
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 (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());
}
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> 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> 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([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] 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([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> Get(int id, [FromQuery] Int32 hid = 0)
{
if (hid <= 0)
{
return(BadRequest("No Home Inputted"));
}
var usrObj = HIHAPIUtility.GetUserClaim(this);
var usrName = usrObj.Value;
if (String.IsNullOrEmpty(usrName))
{
return(BadRequest("User cannot recognize"));
}
LearnEnSentenceViewModel vm = new LearnEnSentenceViewModel();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
queryString = this.getQueryString(false, null, null, 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();
// Header
if (reader.HasRows)
{
while (reader.Read())
{
OnSentenceHeader2VM(reader, vm);
break; // Should only one result!!!
}
}
else
{
errorCode = HttpStatusCode.NotFound;
throw new Exception();
}
await reader.NextResultAsync();
// Explains
if (reader.HasRows)
{
while (reader.Read())
{
// Explain
var vmExp = new LearnEnSentenceExpViewModel();
OnSentenceExplain2VM(reader, vmExp);
vm.Explains.Add(vmExp);
}
}
await reader.NextResultAsync();
if (reader.HasRows)
{
while (reader.Read())
{
// Word ID
vm.RelatedWordIDs.Add(reader.GetInt32(0));
}
}
}
}
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> Get([FromQuery] Int32 hid)
{
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 <FinanceReportOrderViewModel> 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.FinReportOrder, hid);
if (_cache.TryGetValue <List <FinanceReportOrderViewModel> >(cacheKey, out listVm))
{
// Do nothing
}
else
{
listVm = new List <FinanceReportOrderViewModel>();
queryString = @"SELECT [ORDERID]
,[ORDERNAME]
,[ORDERVALID_FROM]
,[ORDERVALID_TO]
,[debit_balance]
,[credit_balance]
,[balance]
FROM [dbo].[v_fin_report_order] WHERE [HID] = " + hid.ToString();
//if (!incInv.HasValue || !incInv.Value)
// queryString += " AND [ORDERVALID_FROM] <= GETDATE() AND [ORDERVALID_TO] >= GETDATE()";
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())
{
FinanceReportOrderViewModel avm = new FinanceReportOrderViewModel
{
OrderID = reader.GetInt32(0),
OrderName = reader.GetString(1),
ValidFrom = reader.GetDateTime(2),
ValidTo = reader.GetDateTime(3)
};
Int32 clnidx = 4;
if (reader.IsDBNull(clnidx))
{
avm.DebitBalance = 0;
}
else
{
avm.DebitBalance = Math.Round(reader.GetDecimal(clnidx), 2);
}
clnidx++;
if (reader.IsDBNull(clnidx))
{
avm.CreditBalance = 0;
}
else
{
avm.CreditBalance = Math.Round(reader.GetDecimal(clnidx), 2);
}
clnidx++;
if (reader.IsDBNull(clnidx))
{
avm.Balance = 0;
}
else
{
avm.Balance = Math.Round(reader.GetDecimal(clnidx), 2);
}
listVm.Add(avm);
}
}
}
_cache.Set <List <FinanceReportOrderViewModel> >(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> Post([FromBody] HomeMsgViewModel vm)
{
if (vm == null)
{
return(BadRequest());
}
if (String.IsNullOrEmpty(vm.Title))
{
return(BadRequest("Title is a must"));
}
if (String.IsNullOrEmpty(vm.UserTo))
{
return(BadRequest("Who shall be send to"));
}
if (String.IsNullOrEmpty(vm.Content))
{
return(BadRequest("Content is a must"));
}
SqlConnection conn = null;
SqlCommand cmd = null;
String queryString = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
String strErrMsg = "";
var usrObj = HIHAPIUtility.GetUserClaim(this);
if (usrObj == null)
{
return(BadRequest());
}
var usrName = usrObj.Value;
if (String.IsNullOrEmpty(usrName))
{
return(BadRequest());
}
if (String.CompareOrdinal(usrName, vm.UserFrom) != 0)
{
return(BadRequest("Cannot send message for others"));
}
try
{
queryString = GetInsertString();
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
cmd = new SqlCommand(queryString, conn);
BindInsertParameters(cmd, vm, usrName);
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
await cmd.ExecuteNonQueryAsync();
vm.ID = (Int32)idparam.Value;
}
}
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> Delete(int id, [FromQuery] Int32 hid = 0)
{
if (hid <= 0)
{
return(BadRequest("No Home 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"));
}
// Update the database
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
String queryString = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
String strErrMsg = "";
try
{
queryString = @"SELECT COUNT( * )
FROM [dbo].[t_learn_hist] WHERE [OBJECTID] = " + 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 = cmd.ExecuteReader();
if (reader.HasRows)
{
Int32 usageAmount = 0;
while (reader.Read())
{
usageAmount = reader.GetInt32(0);
break;
}
errorCode = HttpStatusCode.BadRequest;
throw new Exception("Object still in use: " + usageAmount.ToString());
}
reader.Dispose();
reader = null;
cmd.Dispose();
cmd = null;
// Now go ahead for the creating
queryString = @"DELETE FROM [t_learn_obj] WHERE [ID] = " + id.ToString();
cmd = new SqlCommand(queryString, conn);
Int32 nRst = await cmd.ExecuteNonQueryAsync();
}
}
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));
}
}
return(Ok());
}
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> Get([FromQuery] Int32 hid = 0, Int32 top = 100, Int32 skip = 0)
{
if (hid <= 0)
{
return(BadRequest("No Home 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"));
}
BaseListViewModel <LibLocationViewModel> listVm = new BaseListViewModel <LibLocationViewModel>();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
try
{
queryString = this.GetQueryString(true, top, skip, null, 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)
{
while (reader.Read())
{
listVm.TotalCount = reader.GetInt32(0);
break;
}
}
reader.NextResult();
if (reader.HasRows)
{
while (reader.Read())
{
LibLocationViewModel vm = new LibLocationViewModel();
OnDB2VM(reader, vm);
listVm.Add(vm);
}
}
}
}
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> 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([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> 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));
}