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));
}