public T Load(IEnumerable <EntityKeyMember> keyMembers)
{
ObjectParameter[] parameters;
string query = QueryUtil.BuildKeyQuery(keyMembers, out parameters);
return(ObjectQuery.Where(query, parameters).FirstOrDefault());
}
public void DynamicParamsComplex()
{
var criteria = new { Name = "Adam", Address = "21 Brandt" };
var result = QueryUtil.GetDynamicParameters(criteria, "SELECT * FROM whatever WHERE Name LIKE @name AND Address LIKE @address");
Assert.IsTrue(result.ParameterNames.Count() == 2);
}
private ICriterion GetCollectionContainsCriteria(CollectionAccessExpression arg, Expression containsExpression)
{
EntityExpression rootEntity = EntityExpressionVisitor.FirstEntity(arg);
var rootEntityType = rootEntity.Type;
if (rootEntity.MetaData.HasProxy)
{
rootEntityType = rootEntity.MetaData.GetMappedClass(EntityMode.Poco);
}
DetachedCriteria query = DetachedCriteria.For(rootEntityType)
.SetProjection(Projections.Id());
var visitor = new MemberNameVisitor(query.Adapt(session), true);
visitor.Visit(arg);
//TODO: this won't work for collections of values
var containedEntity = QueryUtil.GetExpressionValue(containsExpression);
var collectionIdPropertyName = visitor.MemberName + "." + arg.ElementExpression.MetaData.IdentifierPropertyName;
var idValue = arg.ElementExpression.MetaData.GetIdentifier(containedEntity, EntityMode.Poco);
query.Add(Restrictions.Eq(collectionIdPropertyName, idValue));
string identifierName = rootEntity.MetaData.IdentifierPropertyName;
return(Subqueries.PropertyIn(identifierName, query));
}
public async Task UpsertCharactersInVideos(List <VideoCharacterActorExportDto> characters, Dictionary <string, long> ids)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
using (var txn = db.BeginTransaction()) {
foreach (var c in characters)
{
if (c.CharacterId == null || c.VideoId == null)
{
continue;
}
var charId = ids[c.CharacterId];
var videoId = ids[c.VideoId];
var actorId = c.ActorId == null ? DatabaseConstants.DEFAULT_ID : ids[c.ActorId];
var command = new SqliteCommand($"UPDATE actor_for_video_character SET creator_id = @ActorId WHERE character_id = {charId} AND video_id = {videoId}", db, txn);
command.Parameters.AddWithValue("@ActorId", QueryUtil.GetNullableIdForStorage(actorId));
command.ExecuteNonQuery();
command = new SqliteCommand($"INSERT OR IGNORE INTO actor_for_video_character(character_id, video_id, creator_id) VALUES({charId}, {videoId}, @ActorId)", db, txn);
command.Parameters.AddWithValue("@ActorId", QueryUtil.GetNullableIdForStorage(actorId));
command.ExecuteNonQuery();
}
txn.Commit();
}
}
}
private SqliteCommand GetCreateVideoCommand(SqliteConnection db, CreateVideoDto dto, string guid, bool ignoreDuplicates)
{
var ignoreClause = ignoreDuplicates ? "OR IGNORE" : "";
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $"INSERT {ignoreClause} INTO video(title, duration_in_seconds, external_rating, user_rating, description, notes, source_url, site_url, series_id, watch_status, publisher_id, library_id, deleted, deletion_due_to_cascade, times_watched, release_date, timeline_date, cover_image, unique_id) VALUES(@Title, @Duration, @ExternalRating, @UserRating, @Description, @Notes, @SourceURL, @SiteURL, @SeriesId, @WatchStatus, @PublisherId, @LibraryId, false, false, @TimesWatched, @ReleaseDate, @TimelineDate, @CoverImage, @UniqueId)";
command.Parameters.AddWithValue("@Title", dto.Title);
command.Parameters.AddWithValue("@Duration", QueryUtil.GetNullableValueForStorage(dto.DurationInSeconds));
command.Parameters.AddWithValue("@ExternalRating", dto.ExternalRating);
command.Parameters.AddWithValue("@UserRating", dto.UserRating);
command.Parameters.AddWithValue("@Description", dto.Description);
command.Parameters.AddWithValue("@Notes", dto.Notes);
command.Parameters.AddWithValue("@SourceURL", dto.SourceURL);
command.Parameters.AddWithValue("@SiteURL", dto.SiteURL);
command.Parameters.AddWithValue("@SeriesId", QueryUtil.GetNullableIdForStorage(dto.SeriesId));
command.Parameters.AddWithValue("@WatchStatus", dto.WatchStatus);
command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(dto.PublisherId));
command.Parameters.AddWithValue("@LibraryId", dto.LibraryId);
command.Parameters.AddWithValue("@ReleaseDate", QueryUtil.GetNullableValueForStorage(dto.ReleaseDate));
command.Parameters.AddWithValue("@TimelineDate", QueryUtil.GetNullableValueForStorage(dto.TimelineDate));
command.Parameters.AddWithValue("@CoverImage", QueryUtil.GetNullableIdForStorage(dto.CoverFileId));
command.Parameters.AddWithValue("@UniqueId", guid);
command.Parameters.AddWithValue("@TimesWatched", dto.TimesWatched);
return(command);
}
private SqliteCommand GetUpdateVideoCommand(SqliteConnection db, Video video, string idColumn, object idValue)
{
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = $@"
UPDATE video SET title = @Title, times_watched = @TimesWatched, last_watch_date = @LastWatchDate,
duration_in_seconds = @Duration, external_rating = @ExternalRating, user_rating = @UserRating,
description = @Description, notes = @Notes, source_url = @SourceURL, site_url = @SiteURL,
series_id = @SeriesId, watch_status = @WatchStatus, publisher_id = @PublisherId, library_id = @LibraryId,
release_date = @ReleaseDate, timeline_date = @TimelineDate, cover_image = @CoverImage
WHERE {idColumn} = @VideoId";
command.Parameters.AddWithValue("@Title", video.Title);
command.Parameters.AddWithValue("@TimesWatched", video.TimesWatched);
command.Parameters.AddWithValue("@LastWatchDate", QueryUtil.GetNullableValueForStorage(video.LastWatchDate));
command.Parameters.AddWithValue("@Duration", QueryUtil.GetNullableValueForStorage(video.DurationInSeconds));
command.Parameters.AddWithValue("@ExternalRating", video.ExternalRating);
command.Parameters.AddWithValue("@UserRating", video.UserRating);
command.Parameters.AddWithValue("@Description", video.Description);
command.Parameters.AddWithValue("@Notes", video.Notes);
command.Parameters.AddWithValue("@SourceURL", video.SourceURL);
command.Parameters.AddWithValue("@SiteURL", video.SiteURL);
command.Parameters.AddWithValue("@SeriesId", QueryUtil.GetNullableIdForStorage(video.SeriesId));
command.Parameters.AddWithValue("@WatchStatus", video.WatchStatus);
command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(video.PublisherId));
command.Parameters.AddWithValue("@LibraryId", video.LibraryId);
command.Parameters.AddWithValue("@VideoId", idValue);
command.Parameters.AddWithValue("@ReleaseDate", QueryUtil.GetNullableValueForStorage(video.ReleaseDate));
command.Parameters.AddWithValue("@TimelineDate", QueryUtil.GetNullableValueForStorage(video.TimelineDate));
command.Parameters.AddWithValue("@CoverImage", QueryUtil.GetNullableIdForStorage(video.CoverFileId));
return(command);
}
private List <CreatorOfVideoFullDto> GetVideosCharacterIsInWithoutActors(long characterId, SqliteConnection db, string characterIdColumn)
{
var firstQuery = $"SELECT {SELECT_BASE_COLUMNS_STRING}, c.creator_id, 'Actor' as role FROM video v, actor_for_video_character c WHERE c.{characterIdColumn} = {characterId} AND c.video_id = v.video_id AND v.deleted = false";
var secondQuery = "";
if (characterIdColumn == "creator_id")
{
secondQuery = $"UNION SELECT {SELECT_BASE_COLUMNS_STRING}, vc.creator_id, vc.role FROM video v, video_creator vc WHERE v.deleted = false AND vc.video_id = v.video_id AND vc.creator_id = {characterId}";
}
var command = new SqliteCommand($"{firstQuery} {secondQuery}", db);
var query = command.ExecuteReader();
var list = new List <CreatorOfVideoFullDto>();
var characterIds = new Dictionary <long, CreatorOfVideoFullDto>();
while (query.Read())
{
var role = query.GetString(22);
var dto = new CreatorOfVideoFullDto(ParseVideo(query), QueryUtil.GetNullableId(query, 21), "", role);
var key = dto.Video.VideoId;
if (characterIds.ContainsKey(key))
{
var existingDto = characterIds[key];
existingDto.Role += $", {dto.Role}";
}
else
{
list.Add(dto);
characterIds[key] = dto;
}
}
return(list);
}
public async Task Triage(string buildQuery)
{
foreach (var build in await QueryUtil.ListBuildsAsync(buildQuery))
{
await Triage(build).ConfigureAwait(false);
}
}
public void ResolveSqlNoCriteria()
{
string query = "SELECT * FROM [Whatever] {where} ORDER BY [Nothing]";
string result = QueryUtil.ResolveSql(query);
Assert.IsTrue(result.Equals("SELECT * FROM [Whatever] ORDER BY [Nothing]"));
}
public void ResolveSqlNoParams()
{
var query = new ActiveEmployees();
string sql = QueryUtil.ResolveSql(query.Sql, query);
Assert.IsTrue(sql.Equals("SELECT * FROM [Employee] WHERE [IsActive]=@isActive ORDER BY [Nothing]"));
}
protected long CreateVideoSequence(CreateVideoSequenceDto dto, SqliteConnection db, SqliteTransaction txn)
{
var sequenceId = CreateVideoSequence(db, txn, dto);
var columnNames = new List <string>()
{
"sequence_id", GetParentColumnName()
};
columnNames.AddRange(GetChildTableAdditionalColumns());
var columnValues = new List <string>()
{
sequenceId.ToString(), "@ParentColumnValue"
};
columnValues.AddRange(GetChildTableAdditionalValues(dto));
var command = new SqliteCommand($"INSERT INTO {GetChildTableName()}({string.Join(",", columnNames)}) VALUES ({string.Join(",", columnValues)})", db, txn);
command.Parameters.AddWithValue("@ParentColumnValue", QueryUtil.GetNullableIdForStorage(GetParentColumnValue(dto)));
command.ExecuteNonQuery();
return(sequenceId);
}
public ServiceResult <object> AutocompleteViewParent(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.ComboVal.ComboVal>();
var queryString =
$"SELECT * FROM(SELECT e1.EntityId AS entityId,e1.Name AS name,e1.Value AS value,e1.code AS code,e2.Name AS parentName,e2.code AS parentCode,e1.Deleted,e1.FullTitle AS fullTitle,e1.AccessKey,e1.CreateBy FROM {tableName} e1 LEFT JOIN {tableName} e2 on e1.ParentId = e2.EntityId where e1.Deleted = 0 and e2.Deleted = 0) e" +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
using (var unitOfWork = new UnitOfWork())
{
var comboList = unitOfWork.ComboVal.CreateNativeQuery(queryString, x => new ComboValDto
{
EntityId = Convert.ToInt64(x[0].ToString()),
Name = x[1]?.ToString(),
Value = x[2]?.ToString(),
Code = x[3]?.ToString()
});
return(comboList.Count == 0
? new ServiceResult <object>(Enumerator.ErrorCode.NotFound, "رکوردی یافت نشد")
: new ServiceResult <object>(comboList, comboList.Count));
}
}
catch (Exception ex)
{
return(ExceptionUtil.ExceptionHandler(ex, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ActionResult Show(string sitename, int querySetId, int revisionId, string slug)
{
Site site;
if (!TryGetSite(sitename, out site))
{
return(site == null ? (ActionResult)PageNotFound() : RedirectPermanent(string.Format("/{0}/revision/{1}/{2}{3}{4}",
site.TinyName.ToLower(), querySetId, revisionId, slug.HasValue() ? "/" + slug : "", Request.Url.Query
)));
}
Site = site;
var querySet = QueryUtil.GetFullQuerySet(querySetId);
if (querySet == null)
{
return(PageNotFound());
}
var revision = querySet.Revisions.FirstOrDefault(r => r.Id == revisionId);
if (revision == null)
{
return(PageNotFound());
}
return(ShowCommon(revision, slug, false));
}
public long CreateLibrary(CreateLibraryDto library, string guid = null)
{
if (guid == null)
{
guid = UniqueIdUtil.GenerateUniqueId();
}
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
long createdLibraryId = -1;
using (var txn = db.BeginTransaction()) {
var command = new SqliteCommand();
command.Connection = db;
command.CommandText = "INSERT INTO library VALUES(NULL, @Name, @BackgroundId, false, @UniqueId)";
command.Parameters.AddWithValue("@Name", library.Name);
command.Parameters.AddWithValue("@BackgroundId", QueryUtil.GetNullableIdForStorage(library.BackgroundImageId));
command.Parameters.AddWithValue("@UniqueId", guid);
command.Transaction = txn;
command.ExecuteNonQuery();
createdLibraryId = QueryUtil.GetLastInsertedPrimaryKey(db, txn);
foreach (var setting in LibrarySettings.DEFAULT_SETTINGS)
{
var createSettingsCommand = new SqliteCommand($"INSERT INTO library_setting VALUES({createdLibraryId}, '{setting.Key}', '{setting.Value}')", db, txn);
createSettingsCommand.ExecuteNonQuery();
}
txn.Commit();
}
return(createdLibraryId);
}
}
public ServiceResult <object> AutocompleteView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.UseCase.UseCase>();
var queryString =
$"select * from (select EntityId as entityId,UseCaseName as useCaseName,Clazz as clazz,TableName as tableName,FullTitle as fullTitle,CreateBy as createBy,AccessKey as accessKey,Deleted as deleted from {tableName}) e" +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
using (var unitOfWork = new UnitOfWork())
{
var useCaseList = unitOfWork.UseCase.CreateNativeQuery(queryString, x => new UseCaseDto
{
EntityId = Convert.ToInt64(x[0].ToString()),
UseCaseName = x[1]?.ToString(),
Clazz = x[2]?.ToString(),
TableName = x[3]?.ToString(),
FullTitle = x[4]?.ToString()
});
return(useCaseList.Count == 0
? new ServiceResult <object>(Enumerator.ErrorCode.NotFound, "رکوردی یافت نشد")
: new ServiceResult <object>(useCaseList, useCaseList.Count));
}
}
catch (Exception ex)
{
return(ExceptionUtil.ExceptionHandler(ex, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
/// <summary>
/// createdUserIdが作成したRefListを返します。
/// </summary>
public async Task <PagedRefLists> GetRefListsAsync(GetRefListsRequest req)
{
var query = _refsContext.RefLists.Include("Author").Include("TagUses.Tag").Include("Statistics").AsNoTracking();
query = AppendQueryForAuthorId(query, req.AuthorId);
query = AppendQueryForAuthorUserName(query, req.AuthorUserName);
query = AppendQueryForTagUseId(query, req.TagUseId);
query = AppendQueryForTagName(query, req.TagName, req.TagUseId);
query = AppendQueryForFromDate(query, req.FromDate);
query = AppendQueryForSearchText(query, req.TitleSearch);
query = AppendQueryForPublishingStatus(query, req.PublishingStatusCondition);
var storedRefListCount = await query.CountAsync();
query = QueryUtil.AppendQueryForRefListSort(query, req.Sort);
query = query.Skip(req.PageIndex * req.PageSize);
query = query.Take(req.PageSize);
var storedLists = await query.ToArrayAsync();
var ret = new PagedRefLists()
{
PageIndex = req.PageIndex,
PageCount = IndexUtil.GetPageCount(storedRefListCount, req.PageSize),
AllRefListCount = storedRefListCount,
RefLists = storedLists,
};
return(ret);
}
public ActionResult ShowPlan(string sitename, int revisionId)
{
Query query = QueryUtil.GetQueryForRevision(revisionId);
if (query == null)
{
return(PageNotFound());
}
var parsedQuery = new ParsedQuery(query.QueryBody, Request.Params);
if (!parsedQuery.IsExecutionReady)
{
return(PageBadRequest());
}
CachedResult cache = QueryUtil.GetCachedResults(
parsedQuery,
Site.Id
);
if (cache == null || cache.ExecutionPlan == null)
{
return(PageNotFound());
}
return(new QueryPlanResult(cache.ExecutionPlan));
}
/// <summary>
/// Private routine to import a sheet of contentType into a workspace
/// </summary>
/// <param name="workspaceId"></param>
/// <param name="file"></param>
/// <param name="sheetName"></param>
/// <param name="headerRowIndex"></param>
/// <param name="primaryColumnIndex"></param>
/// <param name="contentType"></param>
/// <returns> the created sheet </returns>
private Sheet ImportSheet(long workspaceId, string file, string sheetName, int?headerRowIndex, int?primaryColumnIndex, string contentType)
{
Utility.Utility.ThrowIfNull(file);
IDictionary <string, string> parameters = new Dictionary <string, string>();
if (sheetName == null)
{
FileInfo fi = new FileInfo(file);
parameters.Add("sheetName", fi.Name);
}
else
{
parameters.Add("sheetName", sheetName);
}
if (headerRowIndex != null)
{
parameters.Add("headerRowIndex", headerRowIndex.ToString());
}
if (primaryColumnIndex != null)
{
parameters.Add("primaryColumnIndex", primaryColumnIndex.ToString());
}
string path = "workspaces/" + workspaceId + "/sheets/import" + QueryUtil.GenerateUrl(null, parameters);
return(this.ImportFile <Sheet>(path, file, contentType));
}
public ServiceResult <object> AutocompleteView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.Person.Person>();
var queryString =
$"select * from (select EntityId as entityId,FirstName as firstName,LastName as lastName,FullTitle as fullTitle,Deleted as deleted,AccessKey as accessKey,CreateBy as createBy from {tableName}) e" +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, true, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
using (var unitOfWork = new UnitOfWork())
{
var personList = unitOfWork.Person.CreateNativeQuery(queryString, x =>
new Dictionary <string, object>
{
{ "entityId", Convert.ToInt64(x[0].ToString()) },
{ "fullName", $"{x[1]} {x[2]}" },
{ "firstName", $"{x[1]}" },
{ "lastName", $"{x[2]}" },
{ "fullTitle", x[3]?.ToString() }
});
return(personList.Count == 0
? new ServiceResult <object>(Enumerator.ErrorCode.NotFound, "رکوردی یافت نشد")
: new ServiceResult <object>(personList, personList.Count));
}
}
catch (Exception ex)
{
return(ExceptionUtil.ExceptionHandler(ex, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ActionResult Edit(string sitename, int querySetId)
{
bool foundSite = SetCommonQueryViewData(sitename);
if (!foundSite)
{
return(PageNotFound());
}
SetHeader("Editing Query");
QuerySet querySet = QueryUtil.GetFullQuerySet(querySetId);
if (querySet == null)
{
return(PageNotFound());
}
ViewData["query_action"] = "save/" + Site.Id + "/" + querySetId;
ViewData["HelperTables"] = HelperTableCache.GetCacheAsJson(Site);
return(View("Editor", new ViewModel.QuerySetViewModel
{
Site = Site,
Revisions = querySet.Revisions,
CurrentRevision = querySet.CurrentRevision,
QuerySet = querySet
}));
}
public ServiceResult <object> AutocompleteView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.AccessGroup.AccessGroup>();
var queryString =
"select * from (select EntityId,Name,FullTitle,code,CreateBy,AccessKey,Deleted from " +
tableName + " where code <> 'ADMIN') e" +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false, true)) +
QueryUtil.GetOrderByClause(bp.Clause);
using (var unitOfWork = new UnitOfWork())
{
var accessGroupList = unitOfWork.AccessGroup.CreateNativeQuery(queryString, x =>
new Dictionary <string, object>
{
{ "entityId", Convert.ToInt64(x[0].ToString()) },
{ "name", Convert.ToInt64(x[1].ToString()) },
{ "fullTitle", Convert.ToInt64(x[2].ToString()) }
});
return(accessGroupList.Count == 0
? new ServiceResult <object>(Enumerator.ErrorCode.NotFound, "رکوردی یافت نشد")
: new ServiceResult <object>(accessGroupList, accessGroupList.Count));
}
}
catch (Exception ex)
{
return(ExceptionUtil.ExceptionHandler(ex, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
public ActionResult Edit(string sitename, string operation, int querySetId, string slug)
{
Site site;
if (!TryGetSite(sitename, out site))
{
return(site == null ? (ActionResult)PageNotFound() : RedirectPermanent(string.Format("/{0}/query/{1}/{2}{3}",
site.TinyName.ToLower(), operation, querySetId, slug.HasValue() ? "/" + slug : ""
)));
}
SetCommonQueryViewData(site, "Editing Query");
QuerySet querySet = QueryUtil.GetFullQuerySet(querySetId);
if (querySet == null)
{
return(PageNotFound());
}
ViewData["query_action"] = "save/" + Site.Id + "/" + querySetId;
ViewData["HelperTables"] = HelperTableCache.GetCacheAsJson(Site);
return(View("Editor", new ViewModel.QuerySetViewModel
{
Site = Site,
Revisions = querySet.Revisions,
CurrentRevision = querySet.CurrentRevision,
QuerySet = querySet
}));
}
/// <summary>
/// <para>Get a sheet.</para>
///
/// <para>It mirrors To the following Smartsheet REST API method: GET /sheets/{sheetId}</para>
/// </summary>
/// <param name="sheetId"> the Id of the sheet </param>
/// <param name="includes"> used To specify the optional objects To include. </param>
/// <param name="excludes"> used To specify the optional objects To include. </param>
/// <param name="rowIds"> used To specify the optional objects To include. </param>
/// <param name="rowNumbers"> used To specify the optional objects To include. </param>
/// <param name="columnIds"> used To specify the optional objects To include. </param>
/// <param name="pageSize"> used To specify the optional objects To include. </param>
/// <param name="page"> used To specify the optional objects To include. </param>
/// <returns> the sheet resource (note that if there is no such resource, this method will throw
/// ResourceNotFoundException rather than returning null). </returns>
/// <exception cref="System.InvalidOperationException"> if any argument is null or empty string </exception>
/// <exception cref="InvalidRequestException"> if there is any problem with the REST API request </exception>
/// <exception cref="AuthorizationException"> if there is any problem with the REST API authorization (access token) </exception>
/// <exception cref="ResourceNotFoundException"> if the resource cannot be found </exception>
/// <exception cref="ServiceUnavailableException"> if the REST API service is not available (possibly due To rate limiting) </exception>
/// <exception cref="SmartsheetException"> if there is any other error during the operation </exception>
public virtual Sheet GetSheet(long sheetId, IEnumerable <SheetLevelInclusion> includes, IEnumerable <SheetLevelExclusion> excludes, IEnumerable <long> rowIds, IEnumerable <int> rowNumbers, IEnumerable <long> columnIds, long?pageSize, long?page)
{
IDictionary <string, string> parameters = new Dictionary <string, string>();
if (includes != null)
{
parameters.Add("include", QueryUtil.GenerateCommaSeparatedList(includes));
}
if (excludes != null)
{
parameters.Add("exclude", QueryUtil.GenerateCommaSeparatedList(excludes));
}
if (rowIds != null)
{
parameters.Add("rowIds", QueryUtil.GenerateCommaSeparatedList(rowIds));
}
if (rowNumbers != null)
{
parameters.Add("rowNumbers", QueryUtil.GenerateCommaSeparatedList(rowNumbers));
}
if (columnIds != null)
{
parameters.Add("columnIds", QueryUtil.GenerateCommaSeparatedList(columnIds));
}
if (pageSize != null)
{
parameters.Add("pageSize", pageSize.ToString());
}
if (page != null)
{
parameters.Add("page", page.ToString());
}
return(this.GetResource <Sheet>("sheets/" + sheetId + QueryUtil.GenerateUrl(null, parameters), typeof(Sheet)));
}
public virtual bool OpenTableFromSql(DataTable table, string sql, string paramvalue, string pageinfo, string dsname, string alias, bool autotran)
{
if (QueryUtil.OpenTableFromSql(table, sql, paramvalue, pageinfo, dsname, alias))
{
//table.TableNewRow -= new DataTableNewRowEventHandler(DoTableNewRow);
//table.TableNewRow += new DataTableNewRowEventHandler(DoTableNewRow);
//table.ColumnChanged -= new DataColumnChangeEventHandler(DoColumnChanged);
//table.ColumnChanged += new DataColumnChangeEventHandler(DoColumnChanged);
PageManager pm = TableUtil.GetPageManager(table);
if (pm != null)
{
pm.OnPageChangedEvent -= new PageChangedEventHandle(OnPageChanged);
pm.OnPageChangedEvent += new PageChangedEventHandle(OnPageChanged);
}
BindingSource bs = DataUIUtil.GetBindingSource(table);
if (bs != null)
{
bs.ListChanged -= new ListChangedEventHandler(TableListChanged);
bs.ListChanged += new ListChangedEventHandler(TableListChanged);
}
if (autotran)
{
AddTable(table);
}
return(true);
}
return(false);
}
public ServiceResult <object> GridView(BusinessParam bp)
{
var methodName = $".{new StackTrace().GetFrame(1).GetMethod().Name}";
try
{
var tableName = Util.GetSqlServerTableName <DataLayer.Model.Core.File.File>();
var queryString = $"select * from (SELECT * FROM {tableName}) e" +
QueryUtil.GetWhereClause(bp.Clause,
QueryUtil.GetConstraintForNativeQuery(bp, ClassDetails[0].Clazz, false, false,
true)) +
QueryUtil.GetOrderByClause(bp.Clause);
using (var content = new ParsiContext())
{
var files = content.File.FromSqlRaw(queryString).OrderBy(x => x.Extension)
.ThenByDescending(x => x.Created).IgnoreQueryFilters().ToList();
var lstData = files.Select(file => Copier.GetDto(file)).ToList();
return(lstData.Count <= 0
? new ServiceResult <object>(new List <FileDto>(), 0)
: new ServiceResult <object>(lstData, lstData.Count));
}
}
catch (Exception e)
{
return(ExceptionUtil.ExceptionHandler(e, ClassDetails[0].Facade + methodName, bp.UserInfo));
}
}
internal async Task PrintBuildResults(IEnumerable <string> args)
{
int count = 5;
var optionSet = new OptionSet()
{
{ "c|count=", "count of builds to return", (int c) => count = c }
};
ParseAll(optionSet, args);
var data = DotNetUtil.BuildDefinitions
.AsParallel()
.AsOrdered()
.Select(async t => (t.BuildName, t.DefinitionId, await QueryUtil.ListBuildsAsync(count, t.Project, new[] { t.DefinitionId })));
foreach (var task in data)
{
var(name, definitionId, builds) = await task;
Console.Write($"{name,-20}");
var percent = (builds.Count(x => x.Result == BuildResult.Succeeded) / (double)count) * 100;
Console.Write($"{percent,4:G3}% ");
foreach (var build in builds)
{
var c = build.Result == BuildResult.Succeeded ? 'Y' : 'N';
Console.Write(c);
}
Console.WriteLine();
}
}
private long CreateVideoSequence(SqliteConnection db, SqliteTransaction txn, CreateVideoSequenceDto dto)
{
var command = GetCreateVideoSequenceCommand(db, dto);
command.Transaction = txn;
command.ExecuteNonQuery();
return(QueryUtil.GetLastInsertedPrimaryKey(db, txn));
}
public void UpdateCharacterInVideo(CreatorOfVideoFullDto dto, long characterId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var clearCommand = new SqliteCommand($"UPDATE actor_for_video_character SET video_id = {dto.Video.VideoId}, character_id = {characterId}, creator_id = @ActorId WHERE video_id = {dto.Video.VideoId} AND character_id = {characterId}", db);
clearCommand.Parameters.AddWithValue("@ActorId", QueryUtil.GetNullableIdForStorage(dto.ActorId));
clearCommand.ExecuteNonQuery();
}
}
public async Task <IEnumerable <Tag> > GetAllTagsAsync(TagSortKind sort)
{
var query = _refsContext.Tags.Include("Statistics").AsNoTracking();
query = QueryUtil.AppendQueryForTagSort(query, sort);
var storedTags = await query.ToArrayAsync();
return(storedTags);
}
/// <summary>
/// 获取本人的站内信列表
/// </summary>
/// <param name="name">标题</param>
/// <param name="detail">详情</param>
/// <param name="unread">只显示未读</param>
/// <param name="type">类型</param>
/// <param name="createStart">创建起始时间</param>
/// <param name="createEnd">创建结束时间</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页个数</param>
/// <returns></returns>
public async Task <PagedData <PushMessageDTO> > GetMyMessageAsync(
string name,
string detail,
bool unread = false,
EnumMessageType?type = null,
DateTime?createStart = null,
DateTime?createEnd = null,
int pageIndex = 1,
int pageSize = 10)
{
var user = await _uidClient.GetLoginUser().ConfigureAwait(false);
Expression <Func <Message, bool> > exp = o => o.ToUser == user.Account;
if (unread)
{
exp = exp.And(o => o.ReadTime == null);
}
if (!string.IsNullOrEmpty(name))
{
exp = exp.And(o => o.Name.Contains(name));
}
if (!string.IsNullOrEmpty(detail))
{
exp = exp.And(o => o.Detail.Contains(detail));
}
if (type != null)
{
exp = exp.And(o => o.Type == type.Value.ToString());
}
if (createStart != null)
{
exp.And(o => o.CreateTime >= createStart.Value);
}
if (createEnd != null)
{
exp.And(o => o.CreateTime < createEnd.Value);
}
return(await QueryUtil.FindPageObjectList(
exp,
o => o.Id,
true,
pageIndex,
pageSize,
o => new PushMessageDTO
{
Id = o.Id,
CreateTime = o.CreateTime,
Detail = o.Detail,
Name = o.Name,
ToUser = o.ToUser,
Type = o.Type,
ReadTime = o.ReadTime
}
));
}
