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> GetPhotos([FromQuery] String albumid = null, [FromQuery] String accessCode = null, [FromQuery] Int32 top = 100, [FromQuery] Int32 skip = 0) { BaseListViewModel <PhotoViewModel> rstFiles = new BaseListViewModel <PhotoViewModel>(); String strErrMsg = ""; SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; HttpStatusCode errorCode = HttpStatusCode.OK; try { var usrObj = User.FindFirst(c => c.Type == "sub"); String queryString = String.Empty; String strAlbumAC = String.Empty; String strCreatedBy = String.Empty; Boolean bIsPublic = false; UserOperatorAuthEnum?authRead = null; using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); if (usrObj != null) { String cmdText = @"SELECT [AlbumRead] FROM [dbo].[UserDetail] WHERE [UserID] = N'" + usrObj.Value + "'"; cmd = new SqlCommand(cmdText, conn); reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { reader.Read(); if (!reader.IsDBNull(0)) { authRead = (UserOperatorAuthEnum)reader.GetByte(0); } } reader.Close(); reader = null; cmd.Dispose(); cmd = null; } if (String.IsNullOrEmpty(albumid)) { if (usrObj == null) { // Anonymous user queryString = @"SELECT count(*) FROM [dbo].[Photo] WHERE [IsPublic] = 1; " + GetPhotoViewSql() + @"WHERE [IsPublic] = 1 ORDER BY (SELECT NULL) OFFSET " + skip.ToString() + " ROWS FETCH NEXT " + top.ToString() + " ROWS ONLY; "; } else { // Signed-in user queryString = @"SELECT count(*) FROM [dbo].[Photo] WHERE [IsPublic] = 1 OR [UploadedBy] = N'" + usrObj.Value + "'; " + GetPhotoViewSql() + @" WHERE [IsPublic] = 1 OR [UploadedBy] = N'" + usrObj.Value + "' ORDER BY (SELECT NULL) OFFSET " + skip.ToString() + " ROWS FETCH NEXT " + top.ToString() + " ROWS ONLY; "; } } else { String queryString2 = @"SELECT [AlbumID] ,[CreatedBy] ,[IsPublic] ,[AccessCode] FROM [dbo].[Album] WHERE [AlbumID] = " + albumid.ToString(); cmd = new SqlCommand(queryString2, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); // Only one record! if (!reader.IsDBNull(1)) { strCreatedBy = reader.GetString(1); } if (!reader.IsDBNull(2)) { bIsPublic = reader.GetBoolean(2); } if (!reader.IsDBNull(3)) { strAlbumAC = reader.GetString(3); } } reader.Dispose(); reader = null; cmd.Dispose(); cmd = null; if (usrObj == null) { // Anonymous user if (!bIsPublic) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } if (!String.IsNullOrEmpty(strAlbumAC)) { if (String.IsNullOrEmpty(accessCode)) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } else { if (String.CompareOrdinal(strAlbumAC, accessCode) != 0) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } } } } else { // Signed-in user if (authRead.HasValue && authRead.Value == UserOperatorAuthEnum.OnlyOwner) { if (String.CompareOrdinal(strCreatedBy, usrObj.Value) != 0) { // Not the album creator then needs the access code if (bIsPublic) { if (!String.IsNullOrEmpty(strAlbumAC)) { if (String.IsNullOrEmpty(accessCode)) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } else { if (String.CompareOrdinal(strAlbumAC, accessCode) != 0) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } else { // Access code accepted, do nothing } } } } else { // Non public album, current user has no authority to view it. errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } } else { // Creator of album, no need to access code at all } } else if (authRead.HasValue && authRead.Value == UserOperatorAuthEnum.All) { // Do nothing~ } else { // Though logged in, but without any rights, it is the same as unlogged in user if (!bIsPublic) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } if (!String.IsNullOrEmpty(strAlbumAC)) { if (String.IsNullOrEmpty(accessCode)) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } else { if (String.CompareOrdinal(strAlbumAC, accessCode) != 0) { errorCode = HttpStatusCode.Unauthorized; throw new Exception(); } } } } } queryString = @"SELECT count(*) FROM [dbo].[AlbumPhoto] AS taba LEFT OUTER JOIN [dbo].[Photo] AS tabb ON taba.[PhotoID] = tabb.[PhotoID] WHERE taba.[AlbumID] = N'" + albumid + "'; " + @"SELECT tabb.[PhotoID] ,tabb.[Title] ,tabb.[Desp] ,tabb.[Width] ,tabb.[Height] ,tabb.[ThumbWidth] ,tabb.[ThumbHeight] ,tabb.[UploadedAt] ,tabb.[UploadedBy] ,tabb.[OrgFileName] ,tabb.[PhotoUrl] ,tabb.[PhotoThumbUrl] ,tabb.[IsOrgThumb] ,tabb.[ThumbCreatedBy] ,tabb.[CameraMaker] ,tabb.[CameraModel] ,tabb.[LensModel] ,tabb.[AVNumber] ,tabb.[ShutterSpeed] ,tabb.[ISONumber] ,tabb.[IsPublic] ,tabb.[EXIFInfo] ,tabb.[Rating] ,tabb.[Tags] FROM [dbo].[AlbumPhoto] AS taba LEFT OUTER JOIN [dbo].[View_Photo] AS tabb ON taba.[PhotoID] = tabb.[PhotoID] WHERE taba.[AlbumID] = N'" + albumid + "' ORDER BY (SELECT NULL) OFFSET " + skip.ToString() + " ROWS FETCH NEXT " + top.ToString() + " ROWS ONLY; "; } cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { rstFiles.TotalCount = reader.GetInt32(0); break; } } reader.NextResult(); if (reader.HasRows) { while (reader.Read()) { PhotoViewModel rst = new PhotoViewModel(); DataRowToPhoto(reader, rst); rstFiles.Add(rst); } } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } return(new ObjectResult(rstFiles)); }
public async Task <IActionResult> Get([FromBody] PhotoSearchFilterViewModel filters, [FromQuery] Int32 top = 100, Int32 skip = 0) { BaseListViewModel <PhotoViewModel> rstFiles = new BaseListViewModel <PhotoViewModel>(); String strErrMsg = ""; SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader reader = null; HttpStatusCode errorCode = HttpStatusCode.OK; try { var usrObj = User.FindFirst(c => c.Type == "sub"); String queryString = String.Empty; String subqueries = filters.GetFullWhereClause(); StringBuilder sb = new StringBuilder(); if (usrObj == null) { // Anonymous user sb.Append(@"SELECT count(*) FROM [dbo].[View_Photo] WHERE [IsPublic] = 1 "); if (String.IsNullOrEmpty(subqueries)) { sb.Append("; "); } else { sb.Append(" AND " + subqueries + "; "); } sb.Append(PhotoController.GetPhotoViewSql() + @" WHERE [IsPublic] = 1"); if (String.IsNullOrEmpty(subqueries)) { } else { sb.Append(" AND " + subqueries); } sb.Append(@" ORDER BY (SELECT NULL) OFFSET " + skip.ToString() + " ROWS FETCH NEXT " + top.ToString() + " ROWS ONLY; "); } else { // Signed-in user sb.Append(@"SELECT count(*) FROM [dbo].[View_Photo] WHERE ([IsPublic] = 1 OR [UploadedBy] = N'" + usrObj.Value + "')"); if (String.IsNullOrEmpty(subqueries)) { sb.Append("; "); } else { sb.Append(" AND " + subqueries + "; "); } sb.Append(PhotoController.GetPhotoViewSql() + @"WHERE ([IsPublic] = 1 OR [UploadedBy] = N'" + usrObj.Value + "')"); if (String.IsNullOrEmpty(subqueries)) { } else { sb.Append(" AND " + subqueries); } sb.Append(@" ORDER BY (SELECT NULL) OFFSET " + skip.ToString() + " ROWS FETCH NEXT " + top.ToString() + " ROWS ONLY; "); } queryString = sb.ToString(); using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); cmd = new SqlCommand(queryString, conn); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { rstFiles.TotalCount = reader.GetInt32(0); break; } } reader.NextResult(); if (reader.HasRows) { while (reader.Read()) { PhotoViewModel rst = new PhotoViewModel(); PhotoController.DataRowToPhoto(reader, rst); rstFiles.Add(rst); } } } } catch (Exception exp) { System.Diagnostics.Debug.WriteLine(exp.Message); strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } return(new ObjectResult(rstFiles)); }
public async Task <IActionResult> Get([FromQuery] String photoid = null, [FromQuery] Int32 top = 100, [FromQuery] Int32 skip = 0) { BaseListViewModel <AlbumViewModel> listVm = new BaseListViewModel <AlbumViewModel>(); SqlConnection conn = null; String queryString = ""; String strErrMsg = ""; SqlCommand cmd = null; SqlDataReader reader = null; HttpStatusCode errorCode = HttpStatusCode.OK; try { var usrObj = User.FindFirst(c => c.Type == "sub"); if (usrObj == null) { // Anonymous user if (String.IsNullOrEmpty(photoid)) { queryString = @"WITH albumfirstphoto as (SELECT tabb.AlbumID, COUNT(tabb.PhotoID) as PhotoCount, MIN(tabc.PhotoThumbUrl) as ThumbUrl FROM dbo.AlbumPhoto as tabb JOIN dbo.Photo as tabc ON tabb.PhotoID = tabc.PhotoID GROUP BY tabb.AlbumID) SELECT COUNT(*) FROM dbo.Album as taba LEFT OUTER JOIN albumfirstphoto as tabb ON taba.AlbumID = tabb.AlbumID WHERE taba.IsPublic = 1; WITH albumfirstphoto as (SELECT tabb.AlbumID, COUNT(tabb.PhotoID) as PhotoCount, MIN(tabc.PhotoThumbUrl) as ThumbUrl FROM dbo.AlbumPhoto as tabb JOIN dbo.Photo as tabc ON tabb.PhotoID = tabc.PhotoID GROUP BY tabb.AlbumID) SELECT taba.AlbumID, taba.Title, taba.Desp, taba.IsPublic, taba.AccessCodeHint, taba.AccessCode, taba.CreateAt, taba.CreatedBy, tabb.PhotoCount, tabb.ThumbUrl FROM dbo.Album as taba LEFT OUTER JOIN albumfirstphoto as tabb ON taba.AlbumID = tabb.AlbumID WHERE taba.IsPublic = 1 ORDER BY (SELECT NULL) OFFSET " + skip.ToString() + " ROWS FETCH NEXT " + top.ToString() + " ROWS ONLY;"; } else { // In case the photo id is specified, won't care about the top and skip queryString = @"SELECT 0; WITH albumfirstphoto as ( SELECT tabb.AlbumID, count(tabb.PhotoID) as PhotoCount, min(tabc.PhotoThumbUrl) as ThumbUrl from dbo.AlbumPhoto as tabb INNER JOIN dbo.Photo as tabc ON tabb.PhotoID = tabc.PhotoID GROUP BY tabb.AlbumID) SELECT taba.AlbumID, taba.Title, taba.Desp, taba.IsPublic, taba.AccessCodeHint, taba.AccessCode, taba.CreateAt, taba.CreatedBy, tabb.PhotoCount, tabb.ThumbUrl FROM dbo.AlbumPhoto as tabc INNER JOIN dbo.Album as taba ON tabc.AlbumID = taba.AlbumID AND taba.IsPublic = 1 LEFT OUTER JOIN albumfirstphoto as tabb ON taba.AlbumID = tabb.AlbumID WHERE tabc.PhotoID = N'"; queryString += photoid; queryString += @"'"; } } else { // Signed in user if (String.IsNullOrEmpty(photoid)) { queryString = @"WITH albumfirstphoto as (select tabb.AlbumID, count(tabb.PhotoID) as PhotoCount, min(tabc.PhotoThumbUrl) as ThumbUrl FROM dbo.AlbumPhoto as tabb JOIN dbo.Photo as tabc ON tabb.PhotoID = tabc.PhotoID GROUP BY tabb.AlbumID) SELECT count(*) FROM dbo.Album as taba LEFT OUTER JOIN albumfirstphoto as tabb ON taba.AlbumID = tabb.AlbumID WHERE taba.IsPublic = 1 OR (taba.IsPublic = 0 and taba.CreatedBy = N'" + usrObj.Value + "'); " + @"WITH albumfirstphoto as (SELECT tabb.AlbumID, COUNT(tabb.PhotoID) as PhotoCount, MIN(tabc.PhotoThumbUrl) as ThumbUrl FROM dbo.AlbumPhoto as tabb JOIN dbo.Photo as tabc ON tabb.PhotoID = tabc.PhotoID GROUP BY tabb.AlbumID) SELECT taba.AlbumID, taba.Title, taba.Desp, taba.IsPublic, taba.AccessCodeHint, taba.AccessCode, taba.CreateAt, taba.CreatedBy, tabb.PhotoCount, tabb.ThumbUrl FROM dbo.Album as taba LEFT OUTER JOIN albumfirstphoto as tabb on taba.AlbumID = tabb.AlbumID WHERE taba.IsPublic = 1 or (taba.IsPublic = 0 and taba.CreatedBy = N'" + usrObj.Value + @"') ORDER BY (SELECT NULL) OFFSET " + skip.ToString() + " ROWS FETCH NEXT " + top.ToString() + " ROWS ONLY; ";; } else { queryString = @"SELECT 0; WITH albumfirstphoto AS ( SELECT tabb.AlbumID, count(tabb.PhotoID) as PhotoCount, min(tabc.PhotoThumbUrl) as ThumbUrl from dbo.AlbumPhoto as tabb JOIN dbo.Photo as tabc ON tabb.PhotoID = tabc.PhotoID GROUP BY tabb.AlbumID) SELECT taba.AlbumID, taba.Title, taba.Desp, taba.IsPublic, taba.AccessCodeHint, taba.AccessCode, taba.CreateAt, taba.CreatedBy, tabb.PhotoCount, tabb.ThumbUrl FROM dbo.AlbumPhoto as tabc INNER JOIN dbo.Album as taba ON tabc.AlbumID = taba.AlbumID AND taba.IsPublic = 1 OR (taba.IsPublic = 0 and taba.CreatedBy = N'" + usrObj.Value + "') " + @" LEFT OUTER JOIN albumfirstphoto as tabb ON taba.AlbumID = tabb.AlbumID WHERE tabc.PhotoID = N'"; queryString += photoid; queryString += @"'"; } } using (conn = new SqlConnection(Startup.DBConnectionString)) { await conn.OpenAsync(); 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()) { AlbumViewModel avm = new AlbumViewModel(); Int32 idx = 0; avm.Id = reader.GetInt32(idx++); avm.Title = reader.GetString(idx++); if (!reader.IsDBNull(idx)) { avm.Desp = reader.GetString(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { avm.IsPublic = reader.GetBoolean(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { avm.AccessCodeHint = reader.GetString(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { if (!String.IsNullOrEmpty(reader.GetString(idx))) { avm.AccessCodeRequired = true; } else { avm.AccessCodeRequired = false; } ++idx; } else { ++idx; } if (!reader.IsDBNull(idx)) { avm.CreatedAt = reader.GetDateTime(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { avm.CreatedBy = reader.GetString(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { avm.PhotoCount = (Int32)reader.GetInt32(idx++); } else { ++idx; } if (!reader.IsDBNull(idx)) { avm.FirstPhotoThumnailUrl = reader.GetString(idx++); if (avm.AccessCodeRequired) { avm.FirstPhotoThumnailUrl = String.Empty; } } else { ++idx; } listVm.Add(avm); } if (!String.IsNullOrEmpty(photoid)) { // Need update the total count listVm.TotalCount = listVm.ContentList.Count; } } } } catch (Exception exp) { #if DEBUG System.Diagnostics.Debug.WriteLine(exp.Message); #endif strErrMsg = exp.Message; if (errorCode == HttpStatusCode.OK) { errorCode = HttpStatusCode.InternalServerError; } } finally { if (reader != null) { reader.Dispose(); reader = null; } if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { conn.Dispose(); conn = null; } } if (errorCode != HttpStatusCode.OK) { switch (errorCode) { case HttpStatusCode.Unauthorized: return(Unauthorized()); case HttpStatusCode.NotFound: return(NotFound()); case HttpStatusCode.BadRequest: return(BadRequest()); default: return(StatusCode(500, strErrMsg)); } } return(new ObjectResult(listVm)); }