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));
        }
Example #4
0
        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));
        }
Example #5
0
        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));
        }
Example #8
0
        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));
        }
Example #9
0
        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));
        }
Example #11
0
        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));
        }
Example #16
0
        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));
        }
Example #21
0
        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));
        }
Example #24
0
        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));
        }
Example #25
0
        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));
        }