public async Task <IActionResult> GetAccountsListAsync(AccountFilter filter, ListOptions options,
[FromServices] IAccountsListQuery query)
{
var response = await query.RunAsync(filter, options);
return(Ok(response));
}
public async Task <List <Order_AccountDTO> > SingleListAccount([FromBody] Order_AccountFilterDTO Order_AccountFilterDTO)
{
AccountFilter AccountFilter = new AccountFilter();
AccountFilter.Skip = 0;
AccountFilter.Take = 20;
AccountFilter.OrderBy = AccountOrder.Id;
AccountFilter.OrderType = OrderType.ASC;
AccountFilter.Selects = AccountSelect.ALL;
AccountFilter.Id = Order_AccountFilterDTO.Id;
AccountFilter.DisplayName = Order_AccountFilterDTO.DisplayName;
AccountFilter.Email = Order_AccountFilterDTO.Email;
AccountFilter.Phone = Order_AccountFilterDTO.Phone;
AccountFilter.Password = Order_AccountFilterDTO.Password;
AccountFilter.Salt = Order_AccountFilterDTO.Salt;
AccountFilter.PasswordRecoveryCode = Order_AccountFilterDTO.PasswordRecoveryCode;
AccountFilter.ExpiredTimeCode = Order_AccountFilterDTO.ExpiredTimeCode;
AccountFilter.Address = Order_AccountFilterDTO.Address;
AccountFilter.Dob = Order_AccountFilterDTO.Dob;
AccountFilter.Avatar = Order_AccountFilterDTO.Avatar;
AccountFilter.RoleId = Order_AccountFilterDTO.RoleId;
List <Account> Accounts = await AccountService.List(AccountFilter);
List <Order_AccountDTO> Order_AccountDTOs = Accounts
.Select(x => new Order_AccountDTO(x)).ToList();
return(Order_AccountDTOs);
}
public async Task <IActionResult> GetAccount(AccountFilter filter)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var result = new QueryResult <Account>();
var query = _context.Account.Include(c => c.Users).AsQueryable();
if (filter.Id.HasValue)
{
query = query.Include(c => c.Users).Where(c => c.Id == filter.Id.Value);
}
result.PagingData.Page = filter.Page;
result.PagingData.PageSize = filter.PageSize;
result.PagingData.TotalItems = await query.CountAsync();
result.TotalItems = await query.CountAsync();
if (filter.getAll != true)
{
query = query.ApplyPaging(filter);
}
result.PagingData.PageItems = await query.CountAsync();
result.Items = await query.ToListAsync();
return(Ok(result));
}
public async Task <List <MdAccount> > Filter(AccountFilter filterModel)
{
var filterBuilder = Builders <MdAccount> .Filter;
var criterias = new List <FilterDefinition <MdAccount> >();
if (!string.IsNullOrWhiteSpace(filterModel.Email))
{
criterias.Add(Builders <MdAccount> .Filter.Eq("Email", filterModel.Email));
}
if (!string.IsNullOrWhiteSpace(filterModel.PhoneNumber))
{
criterias.Add(Builders <MdAccount> .Filter.Eq("PhoneNumber", filterModel.PhoneNumber));
}
if (!string.IsNullOrWhiteSpace(filterModel.UserName))
{
criterias.Add(Builders <MdAccount> .Filter.Eq("UserName", filterModel.UserName));
}
var filter = filterBuilder.And(criterias);
var filterResult = await(await Collection.FindAsync <MdAccount>(filter)).ToListAsync();
return(filterResult);
}
/// <summary>
///
/// </summary>
/// <param name="client">The Media Services client.</param>
/// <param name="resourceGroupName">The name of the resource group within the Azure subscription.</param>
/// <param name="accountName">The Media Services account name.</param>
/// <param name="accountFilterName">The AccountFilter name</param>
/// <returns></returns>
private async static Task <AccountFilter> CreateAccountFilterAsync(IAzureMediaServicesClient client, string resourceGroupName,
string accountName, string accountFilterName)
{
var audioConditions = new List <FilterTrackPropertyCondition>()
{
new FilterTrackPropertyCondition(FilterTrackPropertyType.Type, "Audio", FilterTrackPropertyCompareOperation.Equal),
new FilterTrackPropertyCondition(FilterTrackPropertyType.FourCC, "EC-3", FilterTrackPropertyCompareOperation.Equal)
};
var videoConditions = new List <FilterTrackPropertyCondition>()
{
new FilterTrackPropertyCondition(FilterTrackPropertyType.Type, "Video", FilterTrackPropertyCompareOperation.Equal),
new FilterTrackPropertyCondition(FilterTrackPropertyType.Bitrate, "0-1000000", FilterTrackPropertyCompareOperation.Equal)
};
List <FilterTrackSelection> includedTracks = new List <FilterTrackSelection>()
{
new FilterTrackSelection(audioConditions),
new FilterTrackSelection(videoConditions)
};
AccountFilter accountFilter = await client.AccountFilters.CreateOrUpdateAsync(
resourceGroupName,
accountName,
accountFilterName,
new AccountFilter(tracks : includedTracks));
return(accountFilter);
}
private IQueryable <AccountDAO> OrFilter(IQueryable <AccountDAO> query, AccountFilter filter)
{
if (filter.OrFilter == null || filter.OrFilter.Count == 0)
{
return(query);
}
IQueryable <AccountDAO> initQuery = query.Where(q => false);
foreach (AccountFilter AccountFilter in filter.OrFilter)
{
IQueryable <AccountDAO> queryable = query;
if (filter.Id != null)
{
queryable = queryable.Where(q => q.Id, filter.Id);
}
if (filter.DisplayName != null)
{
queryable = queryable.Where(q => q.DisplayName, filter.DisplayName);
}
if (filter.Email != null)
{
queryable = queryable.Where(q => q.Email, filter.Email);
}
if (filter.Phone != null)
{
queryable = queryable.Where(q => q.Phone, filter.Phone);
}
if (filter.Password != null)
{
queryable = queryable.Where(q => q.Password, filter.Password);
}
if (filter.Salt != null)
{
queryable = queryable.Where(q => q.Salt, filter.Salt);
}
if (filter.PasswordRecoveryCode != null)
{
queryable = queryable.Where(q => q.PasswordRecoveryCode, filter.PasswordRecoveryCode);
}
if (filter.ExpiredTimeCode != null)
{
queryable = queryable.Where(q => q.ExpiredTimeCode, filter.ExpiredTimeCode);
}
if (filter.Address != null)
{
queryable = queryable.Where(q => q.Address, filter.Address);
}
if (filter.Dob != null)
{
queryable = queryable.Where(q => q.Dob, filter.Dob);
}
if (filter.Avatar != null)
{
queryable = queryable.Where(q => q.Avatar, filter.Avatar);
}
initQuery = initQuery.Union(queryable);
}
return(initQuery);
}
public AccountFilter CreateFilter(string filterName, FilterTrackPropertyType trackType, string trackValue)
{
AccountFilter accountFilter = new AccountFilter()
{
Tracks = GetTrackSelections(trackType, trackValue)
};
return(_media.AccountFilters.CreateOrUpdate(MediaAccount.ResourceGroupName, MediaAccount.Name, filterName, accountFilter));
}
public AccountFilter CreateFilter(string filterName, int bitrate)
{
AccountFilter accountFilter = new AccountFilter()
{
FirstQuality = new FirstQuality(bitrate)
};
return(_media.AccountFilters.CreateOrUpdate(MediaAccount.ResourceGroupName, MediaAccount.Name, filterName, accountFilter));
}
private IQueryable <AccountDAO> DynamicFilter(IQueryable <AccountDAO> query, AccountFilter filter)
{
if (filter == null)
{
return(query.Where(q => false));
}
query = query.Where(q => !q.DeletedAt.HasValue);
if (filter.Id != null)
{
query = query.Where(q => q.Id, filter.Id);
}
if (filter.DisplayName != null)
{
query = query.Where(q => q.DisplayName, filter.DisplayName);
}
if (filter.Email != null)
{
query = query.Where(q => q.Email, filter.Email);
}
if (filter.Phone != null)
{
query = query.Where(q => q.Phone, filter.Phone);
}
if (filter.Password != null)
{
query = query.Where(q => q.Password, filter.Password);
}
if (filter.Salt != null)
{
query = query.Where(q => q.Salt, filter.Salt);
}
if (filter.PasswordRecoveryCode != null)
{
query = query.Where(q => q.PasswordRecoveryCode, filter.PasswordRecoveryCode);
}
if (filter.ExpiredTimeCode != null)
{
query = query.Where(q => q.ExpiredTimeCode, filter.ExpiredTimeCode);
}
if (filter.Address != null)
{
query = query.Where(q => q.Address, filter.Address);
}
if (filter.Dob != null)
{
query = query.Where(q => q.Dob, filter.Dob);
}
if (filter.Avatar != null)
{
query = query.Where(q => q.Avatar, filter.Avatar);
}
query = OrFilter(query, filter);
return(query);
}
public PagingResult <VAccount> GetVAccounts(AccountFilter filter, int page = 1, int pageSize = 20)
{
var paging = new Paging
{
PageSize = pageSize,
CurrentPage = page,
};
var data = Dao.GetVAccounts(filter, paging);
return(new PagingResult <VAccount>(paging, data));
}
// GET: Admin/User
//[HasCredential(RoleID = "VIEW_USER")]
public ActionResult Index(string searchString, int page = 1, int pageSize = 20)
{
var dao = new UserDao();
AccountFilter filter = new AccountFilter();
filter.CatalogueId = SiteConfiguration.CatalogueId;
filter.SearchString = searchString;
var model = dao.ListAllPaging(filter, page, pageSize);
ViewBag.SearchString = searchString;
return(View(model));
}
public async Task <IActionResult> GetBalanceByAccountAsync(int id, [FromQuery] PagedFilter filter)
{
if (GetRole() == Roles.User && !await _accountService.IsOwnerAccountAsync(GetUser(), id))
{
return(Forbid());
}
var userId = GetRole() == Roles.User ? GetUser() : (int?)null;
var accountFilter = new AccountFilter {
AccountId = id, Page = filter.Page, Take = filter.Take, UserId = userId
};
var result = await _accountTransactionService.GetTransactionByAccountAsync(accountFilter);
return(result.Items.Any() ? (IActionResult)Ok(result) : NotFound());
}
public void Find_accounting_by_user()
{
userAccount.Accounted();
session.SaveOrUpdate(client);
Flush();
var accounts = new AccountFilter {
SearchBy = AccountingSearchBy.ByUser, SearchText = client.Users[0].Id.ToString()
}
.Find(session, new Pager());
Assert.That(accounts.Count, Is.EqualTo(1));
Assert.That(accounts.Single().Id, Is.EqualTo(client.Users[0].Accounting.Id));
}
public async Task <List <Account> > List(AccountFilter filter)
{
if (filter == null)
{
return(new List <Account>());
}
IQueryable <AccountDAO> AccountDAOs = DataContext.Account;
AccountDAOs = DynamicFilter(AccountDAOs, filter);
AccountDAOs = DynamicOrder(AccountDAOs, filter);
List <Account> Accounts = await DynamicSelect(AccountDAOs, filter);
return(Accounts);
}
public override Task <Empty> Delete(AccountFilter request, ServerCallContext context)
{
using var db = new AppDbContext();
var account = db.Accounts.FirstOrDefault(a => a.ID == Guid.Parse(request.Id));
if (account == null)
{
throw new RpcException(new Status(StatusCode.NotFound, "Account not found"));
}
db.Accounts.Remove(account);
db.SaveChanges();
return(Task.FromResult(new Empty()));
}
public void Sample(string clientCode)
{
// Create query and add event handler
query = new AccountQuery(null);
query.QueryResponse += query_QueryResponse;
// Create filter and add criterion to filter by client code
AccountFilter filter = new AccountFilter();
filter.AddFilterCriteria(AccountFilter.FilterBy.Client, clientCode);
// Start query to start receiving data
query.Start(filter);
}
public AccountFilter CreateFilter(string filterName, long timescale, long startTimestamp, long endTimestamp)
{
AccountFilter accountFilter = new AccountFilter()
{
PresentationTimeRange = new PresentationTimeRange()
{
Timescale = timescale,
StartTimestamp = startTimestamp,
EndTimestamp = endTimestamp
}
};
return(_media.AccountFilters.CreateOrUpdate(MediaAccount.ResourceGroupName, MediaAccount.Name, filterName, accountFilter));
}
public void GetByFilterTest()
{
using (var context = new OctoContext())
{
var repo = new AccountRepository(context);
var filter = new AccountFilter
{
Id = 1
};
var items = repo.GetByFilterAsync(filter).GetAwaiter().GetResult();
}
}
public async Task <ActionResult> Post([FromBody] AccountFilter filter)
{
OperationResult <IList <AccountDTO> > items = null;
try
{
items = _accountService.Read(filter);
return(Ok(items));
}
catch (Exception e)
{
return(BadRequest(e));
}
}
public AccountFilter CreateFilter(string filterName, long?dvrWindowSeconds, long?liveBackoffSeconds, bool?forceEndTimestamp)
{
AccountFilter accountFilter = new AccountFilter()
{
PresentationTimeRange = new PresentationTimeRange()
{
Timescale = Constant.Media.Filter.Timescale,
PresentationWindowDuration = dvrWindowSeconds,
LiveBackoffDuration = liveBackoffSeconds,
ForceEndTimestamp = forceEndTimestamp
}
};
return(_media.AccountFilters.CreateOrUpdate(MediaAccount.ResourceGroupName, MediaAccount.Name, filterName, accountFilter));
}
public IEnumerable <v_WebAccount> ListAllPaging(AccountFilter filter, int page, int pageSize = 20)
{
IQueryable <v_WebAccount> model = db.v_WebAccount;
if (filter.CatalogueId > 0)
{
model = model.Where(w => w.CatalogueId == filter.CatalogueId);
}
if (!string.IsNullOrEmpty(filter.SearchString))
{
model = model.Where(x => x.UserName.Contains(filter.SearchString) || x.Name.Contains(filter.SearchString));
}
return(model.OrderByDescending(x => x.CreatedDate).ToPagedList(page, pageSize));
}
public void AccountFilterTest()
{
var accountFilterOutgoing = new AccountFilter(10, OperationType.Outgoing, Account1.Object);
Assert.Null(accountFilterOutgoing.VisitOperation(Operations1[10]));
Assert.NotNull(accountFilterOutgoing.VisitOperation(Operations2[10]));
var accountFilterAllowAll = new AccountFilter(10, OperationType.All, Account1.Object);
Assert.NotNull(accountFilterAllowAll.VisitOperation(Operations1[10]));
Assert.NotNull(accountFilterAllowAll.VisitOperation(Operations2[10]));
var accountFilterIncoming = new AccountFilter(100, OperationType.Incoming, Account2.Object);
Assert.Null(accountFilterOutgoing.VisitOperation(Operations1[10]));
Assert.NotNull(accountFilterOutgoing.VisitOperation(Operations2[10]));
}
public virtual XElement GenerateQueryRq()
{
XElement xElement = new XElement(typeof(T).Name + "QueryRq");
xElement.Add(TxnID?.ToQBXML(nameof(TxnID)));
xElement.Add(RefNumber?.ToQBXML(nameof(RefNumber)));
xElement.Add(RefNumberCaseSensitive?.ToQBXML(nameof(RefNumberCaseSensitive)));
xElement.Add(MaxReturned?.ToQBXML(nameof(MaxReturned)));
xElement.Add(ModifiedDateRangeFilter?.ToQBXML(nameof(ModifiedDateRangeFilter)));
xElement.Add(TxnDateRangeFilter?.ToQBXML(nameof(TxnDateRangeFilter)));
xElement.Add(EntityFilter?.ToQBXML(nameof(EntityFilter)));
xElement.Add(AccountFilter?.ToQBXML(nameof(AccountFilter)));
xElement.Add(MatchFilter?.ToQBXML(nameof(MatchFilter)));
xElement.Add(MatchRangeFilter?.ToQBXML(nameof(MatchRangeFilter)));
xElement.Add(CurrencyFilter?.ToQBXML(nameof(CurrencyFilter)));
xElement.Add(OwnerID?.ToQBXML(nameof(OwnerID)));
return(xElement);
}
public IActionResult Get([FromQuery] AccountFilter filter)
{
try
{
var accountList = _service.Get(filter);
if (accountList == null || accountList.Count() == 0)
{
return(NotFound());
}
return(Ok(accountList));
}
catch (System.Exception e)
{
try { _logService.SendLogError(e); } catch (System.Exception ex) { return(StatusCode(503, ex.Message)); }
return(StatusCode(503, e));
}
}
public List <Account> GetAccounts(AccountFilter filter, Paging page = null)
{
using (var db = GetDataContext())
{
var query = db.Account.AsQueryable();
if (filter.Deleted.HasValue)
{
var deletedValue = (short)(filter.Deleted.Value ? 1 : 0);
query = query.Where(e => e.Deleted == deletedValue);
}
if (filter.Enabled.HasValue)
{
var enabledValue = (short)(filter.Enabled.Value ? Status.Enabled : Status.Disabled);
query = query.Where(e => e.Status == enabledValue);
}
if (!string.IsNullOrEmpty(filter.SearchKey))
{
query = query.Where(e => e.TrueName.Contains(filter.SearchKey) || e.Username.Contains(filter.SearchKey));
}
if (filter.BeginTime.HasValue)
{
query = query.Where(e => e.CreateTime > filter.BeginTime.Value);
}
if (filter.EndTime.HasValue)
{
query = query.Where(e => e.CreateTime <= filter.EndTime.Value);
}
if (filter.Usernames != null)
{
query = query.Where(e => filter.Usernames.Contains(e.Username));
}
if (filter.AccountIds != null)
{
query = query.Where(e => filter.AccountIds.Contains(e.AccountId));
}
return(query.OrderByDescending(e => e.AccountId).SetPage(page).ToList());
}
}
internal static void ValidateAccountFilter(AccountFilter accountFilter, FirstQuality expectedFirstQuality, string expectedName, PresentationTimeRange expectedPresentationTimeRange, IList <FilterTrackSelection> expectedTracks)
{
Assert.Equal(expectedFirstQuality?.Bitrate, accountFilter.FirstQuality?.Bitrate);
Assert.Equal(expectedName, accountFilter.Name);
if (expectedPresentationTimeRange != null)
{
Assert.Equal(expectedPresentationTimeRange.StartTimestamp, accountFilter.PresentationTimeRange.StartTimestamp);
Assert.Equal(expectedPresentationTimeRange.EndTimestamp, accountFilter.PresentationTimeRange.EndTimestamp);
Assert.Equal(expectedPresentationTimeRange.PresentationWindowDuration, accountFilter.PresentationTimeRange.PresentationWindowDuration);
Assert.Equal(expectedPresentationTimeRange.LiveBackoffDuration, accountFilter.PresentationTimeRange.LiveBackoffDuration);
Assert.Equal(expectedPresentationTimeRange.Timescale, accountFilter.PresentationTimeRange.Timescale);
Assert.Equal(expectedPresentationTimeRange.ForceEndTimestamp, accountFilter.PresentationTimeRange.ForceEndTimestamp);
}
else
{
Assert.Null(accountFilter.PresentationTimeRange);
}
if (expectedTracks != null)
{
// A filter can include multiple track definitions
Assert.Equal(expectedTracks.Count, accountFilter.Tracks.Count);
for (int x = 0; x < expectedTracks.Count; x++)
{
// For each track included in the filter, there can be multiple selection criteria. The criteria have an AND relationship
Assert.Equal(expectedTracks[x].TrackSelections.Count, accountFilter.Tracks[x].TrackSelections.Count);
for (int y = 0; y < expectedTracks[x].TrackSelections.Count; y++)
{
Assert.Equal(expectedTracks[x].TrackSelections[y].Property, accountFilter.Tracks[x].TrackSelections[y].Property);
Assert.Equal(expectedTracks[x].TrackSelections[y].Operation, accountFilter.Tracks[x].TrackSelections[y].Operation);
Assert.Equal(expectedTracks[x].TrackSelections[y].Value, accountFilter.Tracks[x].TrackSelections[y].Value);
}
}
}
else
{
Assert.True(accountFilter.Tracks == null || accountFilter.Tracks.Count == 0);
}
}
public override Task <AccountResponse> GetByID(AccountFilter request, ServerCallContext context)
{
using var db = new AppDbContext();
if (db.Sessions.FirstOrDefault(s => s.ID == Guid.Parse(request.SessionId)) == null)
{
throw new RpcException(new Status(StatusCode.PermissionDenied, "Session is invalid"));
}
var data = db.Accounts.FirstOrDefault(a => a.ID == Guid.Parse(request.Id));
if (data == null)
{
throw new RpcException(new Status(StatusCode.NotFound, "User not found"));
}
return(Task.FromResult(new AccountResponse {
Account = AccountModel.ConvertAccount(data)
}));
}
public async Task <Account> ForgotPassword(Account Account)
{
try
{
AccountFilter AccountFilter = new AccountFilter
{
Skip = 0,
Take = 1,
Email = new StringFilter {
Equal = Account.Email.ToLower()
},
Selects = AccountSelect.ALL
};
Account = (await UOW.AccountRepository.List(AccountFilter)).FirstOrDefault();
Account.PasswordRecoveryCode = GenerateCode();
Account.ExpiredTimeCode = (DateTime.Now).AddSeconds(1800);
await UOW.Begin();
await UOW.AccountRepository.Update(Account);
await UOW.Commit();
var obj = await UOW.AccountRepository.Get(Account.Id);
return(obj);
}
catch (Exception ex)
{
await UOW.Rollback();
if (ex.InnerException == null)
{
throw new MessageException(ex);
}
else
{
throw new MessageException(ex.InnerException);
}
}
}
public async Task <bool> ValidateId(Account Account)
{
AccountFilter AccountFilter = new AccountFilter
{
Skip = 0,
Take = 10,
Id = new IdFilter {
Equal = Account.Id
},
Selects = AccountSelect.Id
};
int count = await UOW.AccountRepository.Count(AccountFilter);
if (count == 0)
{
Account.AddError(nameof(AccountValidator), nameof(Account.Id), ErrorCode.IdNotExisted);
}
return(count == 1);
}
public async Task <AccountModel> Register([FromBody] AddAccountModel model)
{
ValidateInputModel();
var accountFilter = new AccountFilter()
{
UserName = model.UserName
};
var accounts = await accountDao.Filter(accountFilter);
if (accounts.Count > 0)
{
throw new DomainValidateException(new List <string>()
{
AccountErrorMessages.REGISTER_EXIST_USERNAME
});
}
var passwordSalt = "-" + model.UserName + "-AuctionHub-" + model.UserName;
string passwordHash = StringUtils.ComputeSha256Hash(model.Password + passwordSalt);
var accountDto = new MdAccount()
{
Id = ObjectId.GenerateNewId(),
UserName = model.UserName,
PasswordSalt = passwordSalt,
PasswordHash = passwordHash,
Email = model.Email,
PhoneNumber = model.PhoneNumber,
CreatedAt = DateTime.UtcNow,
ModifiedAt = DateTime.UtcNow
};
await accountDao.AddAsync(accountDto);
var userAccountResponse = GetFromAccount(accountDto);
return(userAccountResponse);
}
/// <summary>
/// Find Accounts using the Filter.
/// </summary>
/// <param name="filter"></param>
/// <returns>Search result of <c>Accounts</c>.</returns>
public SearchResult<Account> FindAccounts(
AccountFilter filter)
{
/* select *
* from member_account mac
* //get account based on username
* join member_account_username mau
* on mac.account_id = mau.account_id
*
* //get account based on attributes
* join member_account_attribute maat
* on mac.account_id = maat.account_id
*
* //get account based on contacts
* join member_account_contact macc
* on mac.account_id = macc.account_id
* join member_contact mc
* on macc.contact_id = mc.contact_id
*
* //get account based on role - this needs to be added if needed
* join member_account_role mar
* on mac.account_id = mar.account_id
* join member_role mr
* on mar.role_id = mr.role_id
*
* //get account based on addresses - this needs to be added if needed
* join member_account_address maad
* on mac.account_id = maad.account_id
* join member_address mad
* on maad.address_id = mad.address_id
*/
//reasoning for a global try catch:
//if the function fails at any moment we want to exit and get back to the callee
SearchResult<Account> searchResult = null;
SQLiteConnection conn = null;
SQLiteCommand cmd = null;
SQLiteDataReader dr = null;
List<Account> accountList = new List<Account>();
int totalMatches = 0;
int numReturned = 0;
try {
List<string> fields = new List<string>();
List<string> joinTables = new List<string>();
List<string> where = new List<string>();
string sortby = "";
conn = (SQLiteConnection)this.GetConnection();
cmd = new SQLiteCommand();
fields.AddRange(new string[] { "mac.account_id", "mac.password", "mac.account_status_id, mac.create_date" });
if (filter.AccountIds.Count > 0) {
StringBuilder accounts = new StringBuilder();
accounts.Append("mac.account_id in (");
for(int i = 0; i < filter.AccountIds.Count; i++){
string accIdParamName = "@accountId" + i;
accounts.Append(accIdParamName + ",");
cmd.Parameters.AddWithValue(accIdParamName, filter.AccountIds[i].ToString());
}
//remove the trailing ,
accounts.Length--;
//append the end )
accounts.Append(")");
where.Add(accounts.ToString());
}
if (filter.StatusId != null) {
//get the accounts by status id
StringBuilder status = new StringBuilder();
status.Append("mac.account_status_id = @statusId");
cmd.Parameters.AddWithValue("@statusId", filter.StatusId);
where.Add(status.ToString());
}
if (filter.UserNames.Count > 0) {
//get the accounts by username
joinTables.Add("join member_account_username mau on mac.account_id = mau.account_id");
StringBuilder usernames = new StringBuilder();
if ( filter.UserNames.Count == 1 )
{
where.Add(DbUtil.buildStringCondition("%" + filter.UserNames[0] + "%", "mau.username", cmd, ""));
}
else
{
usernames.Append("mau.username in (");
for(int i = 0; i < filter.UserNames.Count; i++){
string userNameParamName = "@username" + i;
usernames.Append(userNameParamName + ",");
cmd.Parameters.AddWithValue(userNameParamName, filter.UserNames[i]);
}
//remove the trailing ,
usernames.Length--;
usernames.Append(")");
where.Add(usernames.ToString());
}
}
if (filter.AttributeInfo.Count > 0) {
//get the accounts by attribute
joinTables.Add("join member_account_attribute maat on mac.account_id = maat.account_id");
StringBuilder attributes = new StringBuilder();
for(int i = 0; i < filter.AttributeInfo.Count; i++){
if (filter.AttributeInfo[i].TypeId == null) {
filter.AttributeInfo[i].TypeId = SingletonBase<AccountAttributeType>.GetInstance()[filter.AttributeInfo[i].TypeCode].Id;
}
string attributeIdParamName = "@attributeId" + i;
string attributeValueParamName = "@attributeValue" + i;
attributes.Append("maat.account_attribute_type_id = " + attributeIdParamName);
attributes.Append(" and maat.account_attribute_value = " + attributeValueParamName);
cmd.Parameters.AddWithValue(attributeIdParamName, filter.AttributeInfo[i].TypeId);
cmd.Parameters.AddWithValue(attributeValueParamName, filter.AttributeInfo[i].Value);
}
where.Add(attributes.ToString());
}
if (filter.ContactInfo.Count > 0) {
//get the accounts by contact
joinTables.Add("join member_account_contact macc on mac.account_id = macc.account_id");
joinTables.Add("join member_contact mc on macc.contact_id = mc.contact_id");
StringBuilder contacts = new StringBuilder();
for(int i = 0; i < filter.ContactInfo.Count; i++){
if (filter.ContactInfo[i].TypeId == null) {
filter.ContactInfo[i].TypeId = SingletonBase<ContactType>.GetInstance()[filter.ContactInfo[i].TypeCode].Id;
}
string contactIdParamName = "@contactId" + i;
string contactValueParamName = "@contactValue" + i;
contacts.Append("mc.contact_type_id = " + contactIdParamName);
contacts.Append(" and mc.contact_value = " + contactValueParamName);
cmd.Parameters.AddWithValue(contactIdParamName, filter.ContactInfo[i].TypeId);
cmd.Parameters.AddWithValue(contactValueParamName, filter.ContactInfo[i].Value);
}
where.Add(contacts.ToString());
}
cmd.Connection = conn;
//cmd.CommandText = "select " + BuildFields(fields) +
// " from member_account mac " +
// BuildJoins(joinTables) +
// BuildWhere(where);
string topFilter = string.Empty;
if (filter.Page >= 0)
{
topFilter = String.Format(" limit {0} ", (filter.Page + 1) * filter.PageSize);
}
if (filter.Sort != "")
{
if (filter.SortAttributeId > 0)
{
if (!where.Contains("join member_account_attribute mats on mac.account_id = mats.account_id"))
{
joinTables.Add("join member_account_attribute mats on mac.account_id = mats.account_id");
where.Add(DbUtil.buildLongCondition(filter.SortAttributeId, "mats.account_attribute_type_id", cmd, ""));
sortby = string.Format("{0} {1}", "order by mats." + filter.Sort, filter.SortOrder == web.model.dao.DAOUtil.DBSortDirection.DESC ? " DESC" : "ASC");
}
}
}
cmd.CommandText = "select " + BuildFields(fields) +
" from member_account mac " +
BuildJoins(joinTables) +
BuildWhere(where) +
sortby +
topFilter;
cmd.Connection.Open();
dr = cmd.ExecuteReader();
while (dr.Read()) {
Account account = new Account(new Guid(DbUtil.getGuidValue(dr, "account_id")), DbUtil.getStringValue(dr, "password"), DbUtil.getIntValue(dr, "account_status_id"),DbUtil.getDateValue(dr,"create_date"));
accountList.Add(account);
numReturned++;
}
dr.Close();
cmd.CommandText = "select count ( mac." + MembershipConstants.FIELD_ACCOUNT_ID + ") as " + MembershipConstants.FIELD_ACCOUNT_ID +
" from member_account mac " +
BuildJoins(joinTables) +
BuildWhere(where);
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalMatches = DbUtil.getIntValue(dr, MembershipConstants.FIELD_ACCOUNT_ID);
}
if (filter.Page >= 0)
{
accountList.RemoveRange(0, filter.Page * filter.PageSize);
}
} catch (Exception e) {
logger.Error("SQLiteAccountDAO:FindAccounts: ", e);
}
finally {
DbUtil.close((cmd == null) ? null : cmd.Connection, cmd, dr);
}
//searchResult = new SearchResult<Account>(
// accountList,
// filter,
// accountList.Count,
// accountList.Count,
// 0,
// 0,
// accountList.Count,
// 1,
// accountList.Count,
// 1);
searchResult = new SearchResult<Account>(
accountList,
filter,
totalMatches,
numReturned,
filter.Page,
filter.PageSize,
null);
return searchResult;
}
