public async Task <IActionResult> List(BaseListViewModel model)
{
if (model == null || string.IsNullOrEmpty(model.SearchData))
{
return(await List());
}
else
{
List <Customer> customers = new List <Customer>();
if (model.SearchData.Contains("@"))
{
customers.Add(_customerRepository.GetCustomer(model.SearchData));
}
else
{
customers.Add(_customerRepository.GetCustomer(int.Parse(model.SearchData)));
}
CustomerListViewModel customerModel = new CustomerListViewModel(1, _pageSize, customers.Count);
foreach (var customer in customers)
{
customerModel.Customers.Add(await _customerRepository.GetCustomerFullData(customer));
}
return(View(customerModel));
}
}
public async Task <IActionResult> List(BaseListViewModel model)
{
if (model == null || string.IsNullOrEmpty(model.SearchData))
{
return(await List());
}
else
{
var avaibleRoles = _roleManager.Roles.Select(r => r.Name).ToList();
avaibleRoles.Add("Wszystkie");
UserListViewModel userModel = new UserListViewModel(avaibleRoles, 1, _pageSize, 1);
SportUser user = null;
if (userModel.SearchData.Contains("@"))
{
user = await _userManager.FindByEmailAsync(model.SearchData);
}
else
{
user = await _userManager.FindByNameAsync(model.SearchData);
}
userModel.UsersWithRoles.Add(user, await _userManager.GetRolesAsync(user));
return(View(userModel));
}
}
public IActionResult List(BaseListViewModel model)
{
if (model == null || string.IsNullOrEmpty(model.SearchData))
{
return(List());
}
else
{
List <Order> orders = new List <Order>();
int orderId = 0;
Order order = null;
if (int.TryParse(model.SearchData, out orderId))
{
order = _orderRepository.GetOrder(orderId);
}
else
{
order = _orderRepository.GetOrder(model.SearchData);
}
if (order != null)
{
orders.Add(order);
}
OrderListViewModel orderModel = new OrderListViewModel(orders, 1, _pageSize, orders.Count());
return(View(orderModel));
}
}
public async Task <IActionResult> Index()
{
var users = await _business.GetAllAsync();
BaseListViewModel <UserViewModel> vm = await GetListViewModelAsync(users);
return(View(vm));
}
private void OnItemSelected(object sender, SelectedItemChangedEventArgs e)
{
BaseListViewModel model = (BaseListViewModel)BindingContext;
if (model?.SelectItemCommand?.CanExecute(e.SelectedItem) ?? false)
{
model?.SelectItemCommand?.Execute(e.SelectedItem);
}
}
public BaseListViewModel <ZhaoPin> GetZhaoPinList(long userID, Paging page)
{
var model = new BaseListViewModel <ZhaoPin>();
DataSet ds = dal.GetZhaoPinList(userID, page.StartIndex, page.EndIndex);
model.Page = page;
model.Page.RecordCount = ds.Tables[0].Rows[0][0].ToString().ToInt32();
model.DataList = ModelConvertHelper <ZhaoPin> .ConvertToList(ds.Tables[1]);
return(model);
}
public async void CanGetCustomerById(BaseListViewModel inputModel)
{
//arrange
//act
ViewResult result = (ViewResult)await _target.List(inputModel);
CustomerListViewModel resultModel = (CustomerListViewModel)result.Model;
//assert
Assert.NotNull(resultModel);
Assert.Equal(1, resultModel.Customers.Count);
Assert.Equal(int.Parse(inputModel.SearchData), resultModel.Customers.First().Customer.CustomerId);
}
// GET: /<controller>/
public async Task <IActionResult> Index(LoginLogOption filter, int?page)
{
ViewBag.filter = filter;
var currentPageNum = page.HasValue ? page.Value : 1;
var result = await _loginlogApp.GetPageAsync(currentPageNum, DefaultPageSize, filter);
var model = new BaseListViewModel <LoginLogDtoext>();
model.list = result.Data;
model.Paging.CurrentPage = currentPageNum;
model.Paging.ItemsPerPage = DefaultPageSize;
model.Paging.TotalItems = result.ItemCount;
return(View(model));
}
public void CanGetSelectedOrderByOrderNumber(string orderNumber)
{
//arrange
BaseListViewModel model = new BaseListViewModel {
SearchData = orderNumber
};
//act
ViewResult result = (ViewResult)_target.List(model);
OrderListViewModel resultModel = (OrderListViewModel)result.Model;
//assert
Assert.Single(resultModel.Orders);
Assert.True(result.ViewData.ModelState.IsValid);
}
private async Task <BaseListViewModel <UserViewModel> > GetListViewModelAsync(List <IdentityUser> users)
{
var list = new List <UserViewModel>();
foreach (var user in users)
{
list.Add(await GetUserViewModelAsync(user));
}
BaseListViewModel <UserViewModel> vm = new BaseListViewModel <UserViewModel>()
{
Data = list
};
return(vm);
}
//
// GET: /<controller>/
public async Task <IActionResult> Index(RoleOption filter, int?page)
{
ViewBag.filter = filter;
var currentPageNum = page ?? 1;
var result = await RoleApp.GetPageAsync(currentPageNum, DefaultPageSize, filter);
var model = new BaseListViewModel <Role>
{
list = result.Data,
Paging =
{
CurrentPage = currentPageNum,
ItemsPerPage = DefaultPageSize,
TotalItems = result.ItemCount
}
};
return(View(model));
}
public async Task <IActionResult> Index()
{
List <Client> clients = _dbContext.Clients.AsNoTracking().ToList();
List <IdentityServer4.Models.Client> auxList = new List <IdentityServer4.Models.Client>();
foreach (var item in clients)
{
var client = await _clientStore.FindClientByIdAsync(item.ClientId);
auxList.Add(client);
}
BaseListViewModel <ClientViewModel> vm = new BaseListViewModel <ClientViewModel>()
{
Data = _mapper.Map <List <ClientViewModel> >(auxList)
};
return(View(vm));
}
public async Task <BaseListViewModel <CategorySummary> > GetAllAsync(string sortBy, int?page)
{
IEnumerable <CategorySummary> list = await _summaryGenerator.AllSummariesAsync();
var viewModel = new BaseListViewModel <CategorySummary>();
viewModel.SortOrder = new Dictionary <string, string>()
{
{ "name", "namedesc" }, { "budget", "budgetdesc" }
};
switch (sortBy)
{
case "namedesc":
list = list.OrderByDescending(x => x.Category.Name);
viewModel.SortOrder["name"] = "nameasc";
break;
case "nameasc":
list = list.OrderBy(x => x.Category.Name);
viewModel.SortOrder["name"] = "namedesc";
break;
case "budgetdesc":
list = list.OrderByDescending(x => x.ThisMonthSpentAmount);
viewModel.SortOrder["budget"] = "budgetasc";
break;
case "budgetasc":
list = list.OrderBy(x => x.ThisMonthSpentAmount);
viewModel.SortOrder["budget"] = "budgetdesc";
break;
default:
break;
}
viewModel.Items = PaginatedList <CategorySummary> .Create(list, page ?? 1);
return(viewModel);
}
// GET: /<controller>/
public async Task <IActionResult> Index(WebConfigOption filter, int?page)
{
ViewBag.filter = filter;
var currentPageNum = page ?? 1;
filter.RowsPrePage = DefaultPageSize;
filter.PageNumber = currentPageNum;
var result = await WebConfigApp.GetPageAsync(filter);
var model = new BaseListViewModel <WebConfigDto>
{
list = result.Data.Data,
Paging =
{
CurrentPage = currentPageNum,
ItemsPerPage = DefaultPageSize,
TotalItems = result.Data.ItemCount
}
};
return(View(model));
}
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] 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([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));
}