public NewsData_Info GetBySN(long NewsId)
{
using (var db = new DBExecutor().GetDatabase())
{
var SQLStr = Rest.Core.PetaPoco.Sql.Builder
.Append("SELECT * FROM db_NewsData")
.Append("WHERE NewsId=@0", NewsId);
var result = db.SingleOrDefault <NewsData_Info>(SQLStr);
return(result);
}
}
private List <ScPriceTypeEntity> GetRecords(UserConnection userConnection, ScPriceTypeEntity us)
{
var records = new List <ScPriceTypeEntity>();
// SQL-запрос.
var select = (Select) new Select(userConnection)
.Column("Id")
.Column("AccountId") // контрагент
.Column("StartDate") // дата начала
.Column("EndDate") // дата окончания
.Column("vsDeliveryLocationId") // место досатвки
.Column("vsLogisticTypeId") // тип логистики
.From("ScPriceType") // таблица "Коммерческие условия"
.Where()
.OpenBlock("AccountId").IsEqual(Column.Parameter(us.AccountId)) // контрагент
.And("Id").IsNotEqual(Column.Parameter(us.Id)) // исключить саму запись
.CloseBlock();
// место доставки
var deliveryCondition = new QueryCondition()
{
ConditionType = us.DeliveryLocationId == Guid.Empty ? QueryConditionType.IsNull : QueryConditionType.Equal,
LeftExpression = new QueryColumnExpression(Column.SourceColumn("vsDeliveryLocationId")),
RightExpressions = { Column.Parameter(us.DeliveryLocationId) }
};
select.AddCondition(deliveryCondition, LogicalOperation.And);
// тип логистики
var logisticTypeCondition = new QueryCondition()
{
ConditionType = us.LogisticTypeId == Guid.Empty ? QueryConditionType.IsNull : QueryConditionType.Equal,
LeftExpression = new QueryColumnExpression(Column.SourceColumn("vsLogisticTypeId")),
RightExpressions = { Column.Parameter(us.LogisticTypeId) }
};
select.AddCondition(logisticTypeCondition, LogicalOperation.And);
// Использование экземпляра DBExecutor в основном потоке.
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
{
using (IDataReader dataReader = select.ExecuteReader(dbExecutor))
{
while (dataReader.Read())
{
records.Add(new ScPriceTypeEntity(
dataReader.GetColumnValue <Guid>("Id"),
dataReader.GetColumnValue <Guid>("AccountId"),
dataReader.GetColumnValue <DateTime>("StartDate"),
dataReader.GetColumnValue <DateTime>("EndDate"),
dataReader.GetColumnValue <Guid>("vsDeliveryLocationId"),
dataReader.GetColumnValue <Guid>("vsLogisticTypeId")));
}
}
}
return(records);
}
public CostUnit_Info GetBySN(long CostUnitId)
{
using (var db = new DBExecutor().GetDatabase())
{
var SQLStr = Rest.Core.PetaPoco.Sql.Builder
.Append("SELECT * FROM db_CostUnit")
.Append("WHERE CostUnitId=@0", CostUnitId);
var result = db.SingleOrDefault <CostUnit_Info>(SQLStr);
return(result);
}
}
public AboutCategory_Info GetBySN(long AboutCategoryId)
{
using (var db = new DBExecutor().GetDatabase())
{
var SQLStr = Rest.Core.PetaPoco.Sql.Builder
.Append("SELECT * FROM db_AboutCategory")
.Append("WHERE AboutCategoryId=@0", AboutCategoryId);
var result = db.SingleOrDefault <AboutCategory_Info>(SQLStr);
return(result);
}
}
public TeamIntroduce_Info GetBySN(long TeamIntroduceId)
{
using (var db = new DBExecutor().GetDatabase())
{
var SQLStr = Rest.Core.PetaPoco.Sql.Builder
.Append("SELECT * FROM db_TeamIntroduce")
.Append("WHERE TeamIntroduceId=@0", TeamIntroduceId);
var result = db.SingleOrDefault <TeamIntroduce_Info>(SQLStr);
return(result);
}
}
/// <summary>
/// Fill static group by dynamic group filter
/// </summary>
/// <returns>Result response</returns>
public void Convert(Guid newFolderId, Guid parentFolderId, string entitySchemaName)
{
var selectFromEsq = CreateEntityDataSelect(entitySchemaName, parentFolderId, newFolderId);
selectFromEsq.SpecifyNoLockHints();
var entityFolderInsertSelect = GetInsertSelectForEntityFolder(selectFromEsq, entitySchemaName);
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
dbExecutor.CommandTimeout = ConvertingTimeout;
entityFolderInsertSelect.Execute(dbExecutor);
}
}
public long Insert(Question_Info data)
{
using (var db = new DBExecutor().GetDatabase())
{
long NewID = 0;
var result = db.Insert(data);
if (result != null)
{
long.TryParse(result.ToString(), out NewID);
}
return(NewID);
}
}
public bool TryHandleWebHook(DBExecutor dbExecutor, UserConnection userConnection)
{
try {
dbExecutor.StartTransaction();
HandleWebHook(userConnection);
dbExecutor.CommitTransaction();
return(true);
} catch (Exception e) {
dbExecutor.RollbackTransaction();
MailingUtilities.WebHookLog.ErrorFormat("[WebHookHandler.ProcessWebHooks.TryHandleWebHook]", e);
}
return(false);
}
public override IEnumerable <TModel> Execute()
{
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
{
using (IDataReader dataReader = Query.ExecuteReader(dbExecutor))
{
while (dataReader.Read())
{
yield return(Mapper.Convert(dataReader));
}
}
}
}
public int Insert(Doc_Info data)
{
using (var db = new DBExecutor().GetDatabase())
{
int NewID = 0;
var result = db.Insert(data);
if (result != null)
{
int.TryParse(result.ToString(), out NewID);
}
return(NewID);
}
}
/// <summary>
/// ############ ######## WebHookEvent.
/// </summary>
/// <param name="webHookEvents">######### ######### #### WebHookEvent</param>
public void ProcessRawMandrillEvents()
{
var countReadRow =
(Int32)Terrasoft.Core.Configuration.SysSettings.GetValue(UserConnection, "MandrillWebHookProcessingPackageCount");
if (countReadRow < 1)
{
countReadRow = _defaultCountReadRow;
}
int batchLimit = countReadRow * _defMultiplier;
int tc = System.Environment.TickCount;
int insertedRows = 1;
while (insertedRows > 0)
{
if (System.Environment.TickCount - tc > _maxTimelimit)
{
break;
}
insertedRows = 0;
var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "RawMandrillEvent");
esq.JoinRightState = Terrasoft.Core.DB.QueryJoinRightLevel.Disabled;
string idColumnName = esq.AddColumn(esq.RootSchema.GetPrimaryColumnName()).Name;
string JsonDataColumnName = esq.AddColumn("JsonData").Name;
esq.RowCount = countReadRow;
EntityCollection rawEvents = esq.GetEntityCollection(UserConnection);
if (rawEvents.Any())
{
foreach (Entity rawEvent in rawEvents)
{
Guid id = rawEvent.GetTypedColumnValue <Guid>(idColumnName);
using (MemoryStream jsonDataCompressed = rawEvent.GetStreamValue(JsonDataColumnName)) {
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
dbExecutor.StartTransaction();
try {
List <Mandrill.WebHookEvent> responses = CompressedJsonToEmailTargetStatuses(jsonDataCompressed);
insertedRows = ProcessWebHooks(responses, dbExecutor);
Delete delete = new Delete(UserConnection).From("RawMandrillEvent")
.Where("Id").IsEqual(Column.Parameter(id)) as Delete;
delete.Execute(dbExecutor);
dbExecutor.CommitTransaction();
} catch (Exception e) {
dbExecutor.RollbackTransaction();
_log.ErrorFormat(string.Format("[HandleWebHooks] Error while processing record: {0}", id), e);
}
}
}
}
}
}
}
private Dictionary <Guid, Guid> GetListenersData()
{
if (_entitySchemaColumns.FindByName("EntityId") != null &&
_entitySchemaColumns.FindByName("EntitySchemaUId") != null)
{
if (_entity.GetTypedColumnValue <Guid>("EntitySchemaUId") == Guid.Empty &&
_entity.GetTypedColumnValue <Guid>("EntityId") == Guid.Empty &&
_entity.SchemaName == "SocialMessage")
{
return(GetSocialMessageListenersData());
}
return(new Dictionary <Guid, Guid> {
{ _entity.GetTypedColumnValue <Guid>("EntitySchemaUId"), _entity.GetTypedColumnValue <Guid>("EntityId") }
});
}
var listenersData = new Dictionary <Guid, Guid>();
var connectionColumnsSelect = new Select(_userConnection)
.Column("NotifierConnectionColumn")
.From("ListenerByNotifier")
.Where("MessageNotifierId").IsEqual(new QueryParameter(LocalMessageNotifierId)) as Select;
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
using (IDataReader dataReader = connectionColumnsSelect.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
var notifierConnectionColumnName = dataReader.GetColumnValue <string>("NotifierConnectionColumn");
if (!string.IsNullOrEmpty(notifierConnectionColumnName))
{
var entitySchemaColumn = _entitySchemaColumns.FindByName(notifierConnectionColumnName);
if (entitySchemaColumn != null)
{
var connectionColumnValue = _entity.GetTypedColumnValue <Guid>(entitySchemaColumn.ColumnValueName);
if (connectionColumnValue == Guid.Empty)
{
connectionColumnValue = _entity.GetTypedOldColumnValue <Guid>(entitySchemaColumn.ColumnValueName);
}
if (connectionColumnValue != Guid.Empty)
{
listenersData.Add(entitySchemaColumn.ReferenceSchemaUId, connectionColumnValue);
}
}
else if (notifierConnectionColumnName == _entity.SchemaName)
{
listenersData.Add(_entity.Schema.UId, _entity.PrimaryColumnValue);
}
}
}
}
}
return(listenersData);
}
virtual protected bool IsSchemaSegmentsStatusEquals(string schemaName, Guid recordId, Guid statusId)
{
Select checkStatusQuery = (Select) new Select(UserConnection)
.Column("Id")
.From(schemaName)
.Where("Id").IsEqual(Column.Parameter(recordId))
.And("SegmentsStatusId").IsEqual(Column.Parameter(statusId));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = checkStatusQuery.ExecuteReader(dbExecutor)) {
return(reader.Read());
}
}
}
/// <summary>
/// Called at the end of the authorization process.
/// </summary>
/// <param name="userLogin">OAuth application user login.</param>
/// <param name="tokenStorageId">Created token storage identifier.</param>
protected override void PostprocessAuthentication(string userLogin, Guid tokenStorageId)
{
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
dbExecutor.StartTransaction();
try {
CreateNewMailboxSyncSettings(userLogin, tokenStorageId);
CreateNewContactCommunications(userLogin);
dbExecutor.CommitTransaction();
} catch {
dbExecutor.RollbackTransaction();
throw;
}
}
}
private HashSet <Guid> LoadPhoneTypes()
{
var select = (Select) new Select(_userConnection)
.Column("CommunicationTypeId")
.From("ComTypebyCommunication").InnerJoin("Communication")
.On("CommunicationId").IsEqual("Communication", "Id")
.Where("Code").IsEqual(Column.Const("Phone"));
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
using (IDataReader reader = select.ExecuteReader(dbExecutor)) {
return(new HashSet <Guid>(reader.GetAllColumnValues <Guid>("CommunicationTypeId")));
}
}
}
private void DownloadDevExpressReportAssemblies(UserConnection userConnection,
string directoryPath, IEnumerable <string> assemblyNames)
{
var refAssemblyDataSelect = GetRefAssemblyDataSelect(userConnection, assemblyNames);
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
using (IDataReader dataReader = refAssemblyDataSelect.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
DownloadAssemblyToFileSystem(dataReader, directoryPath);
}
}
}
}
/// <summary>
/// Executes action in transaction.
/// <param name="action">Action.</param>
/// </summary>
public void Transaction(Action action)
{
using (_dbExecutor = UserConnection.EnsureDBConnection()) {
_dbExecutor.StartTransaction();
try {
action();
_dbExecutor.CommitTransaction();
} catch (Exception e) {
_dbExecutor.RollbackTransaction();
TranslationLogger.Error(e);
throw;
}
}
}
public virtual bool CheckIsProcessRestartRequired()
{
Select checkStatusQuery = (Select) new Select(UserConnection)
.Column("Id")
.From(RootSchemaName)
.Where("Id").IsEqual(Column.Parameter(RootSchemaRecordId))
.And("SegmentsStatusId").IsEqual(Column.Parameter(MarketingConsts.SegmentStatusRequiresUpdatingId));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = checkStatusQuery.ExecuteReader(dbExecutor)) {
return(reader.Read());
}
}
}
/// <summary>
/// Selects sections data using <paramref name="select"/> and creates <see cref="Section"/> collection.
/// </summary>
/// <param name="select"><see cref="Select"/> instance.</param>
/// <returns><see cref="Section"/> collection.</returns>
protected List <Section> GetSectionsFromDb(Select select)
{
List <Section> sections = new List <Section>();
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
sections.AddIfNotExists(CreateSectionInstance(dataReader));
}
}
}
return(sections);
}
/// <summary>
/// Checks ability execute of operation.
/// </summary>
/// <param name="operationId">Deduplicat operation id.</param>
/// <param name="schemaName">Schema name.</param>
/// <returns>Access to execution of operations.</returns>
public bool CanExecute(Guid operationId, string schemaName)
{
var select = new Select(_userConnection)
.Column("Id").From("DeduplicateExecLocker")
.Where("EntitySchemaName").IsEqual(Column.Parameter(schemaName))
.And("OperationId").IsEqual(Column.Parameter(operationId))
.And("IsInProgress").IsEqual(Column.Parameter(true)) as Select;
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
using (IDataReader reader = select.ExecuteReader(dbExecutor)) {
return(!reader.Read());
}
}
}
private IEnumerable <Guid> GetUsersSspAccounts(List <Guid> userIds)
{
Select select = new Select(UserConnection).Distinct().Column("PortalAccountId").From("SysAdminUnit")
.Where("Id").In(Column.Parameters(userIds)) as Select;
using (DBExecutor executor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(executor)) {
while (dataReader.Read())
{
yield return(dataReader.GetColumnValue <Guid>("PortalAccountId"));
}
}
}
}
public bool SaveEventTrackingData(Tracking tracking)
{
if (UserConnection == null)
{
_log.ErrorFormat("[EventTrackingService_SaveEventData. UserConnection = null]");
return(false);
}
if (tracking.ListTracking == null)
{
_log.ErrorFormat("[EventTrackingService_SaveEventData. ListTracking = null]");
return(false);
}
if (tracking.ListTracking.Count == 0)
{
_log.ErrorFormat("[EventTrackingService_SaveEventData. ListTracking.Count = 0]");
return(false);
}
var eventTrackingApiKey = SysSettings.GetValue(UserConnection,
"EventTrackingApiKey").ToString();
if (tracking.ApiKey != eventTrackingApiKey)
{
_log.ErrorFormat("[EventTrackingService_SaveEventData. tracking.ApiKey != eventTrackingApiKey]");
return(false);
}
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
dbExecutor.StartTransaction();
try {
List <Guid> eventsId = tracking.ListTracking.Select(trackingItem => trackingItem.Id).ToList();
List <Guid> existsEventsId = GetExistsSiteEventsIds(eventsId);
List <TrackingParameters> newEvents = tracking.ListTracking
.Where(item => !existsEventsId.Contains(item.Id)).ToList();
newEvents.ForEach(item => {
var query = new Insert(UserConnection).Into("SiteEvent")
.Set("Id", Column.Parameter(item.Id))
.Set("EventDate", Column.Parameter(Convert.ToDateTime(item.EventTime)))
.Set("Source", Column.Parameter(item.Url))
.Set("SiteEventTypeId", Column.Parameter(item.EventTypeId))
.Set("BpmSessionId", Column.Parameter(item.SessionId));
query.Execute(dbExecutor);
});
dbExecutor.CommitTransaction();
} catch (Exception e) {
dbExecutor.RollbackTransaction();
_log.ErrorFormat("[EventTrackingService_SaveEventData. Insert data failed.]", e);
return(false);
}
}
return(true);
}
/// <summary>
/// Executes select contact query and returns found contact ids list.
/// </summary>
/// <param name="select">Select contact query</param>
/// <returns>List of found contact ids</returns>
private List <Guid> GetContactIdsFromQuery(Select select)
{
var contactIds = new List <Guid>();
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dr = select.ExecuteReader(dbExecutor)) {
while (dr.Read())
{
contactIds.Add(dr.GetColumnValue <Guid>("ContactId"));
}
}
return(contactIds);
}
}
public bool CheckLic(string apiKey)
{
using (DBExecutor dbExec = _userConnection.EnsureDBConnection()) {
using (IDataReader reader = dbExec.ExecuteReader(GetCheckLicQuery(apiKey).GetSqlText())) {
if (reader.Read())
{
AddInfo(apiKey);
var count = reader.GetColumnValue <int>("Count");
return(count < MaxCallCount);
}
}
}
return(false);
}
/// <summary>
/// Selects workplaces data using <paramref name="select"/> and creates <see cref="Section"/> collection.
/// </summary>
/// <param name="select"><see cref="Select"/> instance.</param>
/// <returns><see cref="Workplace"/> collection.</returns>
private List <Workplace> GetWorkplacesFromDb(Select select)
{
var workplaces = new List <Workplace>();
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
workplaces.AddIfNotExists(CreateWorkplaceInstance(dataReader));
}
}
}
return(workplaces);
}
private void AddIdentification(Guid entityId, IEnumerable <Guid> identifiedRecordIds,
IdentificationType identificationType, string foundEntityTableName, string entityColumnName)
{
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
foreach (Guid identifiedRecordId in identifiedRecordIds)
{
Insert insert = new Insert(_userConnection).Into(foundEntityTableName);
insert.Set("EnrchTextEntityId", Column.Parameter(entityId));
insert.Set("IdentificationType", Column.Parameter(identificationType.ToString()));
insert.Set(entityColumnName, Column.Parameter(identifiedRecordId));
insert.Execute(dbExecutor);
}
}
}
/// <summary>
/// Selects pages data and creates <see cref="PageEntity"/> collection.
/// </summary>
/// <returns><see cref="PageEntity"/> collection.</returns>
protected List <PageEntity> GetPagesFromDb()
{
var pages = new List <PageEntity>();
var pagesSelect = GetPagesSelect();
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = pagesSelect.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
pages.AddIfNotExists(CreatePageInstance(dataReader));
}
}
}
return(pages);
}
/// <summary>
/// Reads single record and applies transformation on it.
/// </summary>
/// <typeparam name="T">Type of the output item.</typeparam>
/// <param name="select">Source <see cref="Select"/> to read data.</param>
/// <param name="readMethod">Record transformation method.</param>
/// <param name="item">Read and transformed item, or default <see cref="T"/> if nothing was read.</param>
/// <returns>Returns <c>true</c> if record was read, <c>false</c> otherwise.</returns>
/// <example>
/// This sample shows how to call the <see cref="ExecuteSingleRecord{T}"/> method.
/// <code>
/// select.ExecuteSingleRecord(reader => new {
/// Id = reader.GetColumnValue<Guid>("Id"),
/// Name = reader.GetColumnValue<string>("Name")
/// }, out var item);
/// </code>
/// </example>
public static bool ExecuteSingleRecord <T>(this Select select,
ExecuteReaderTypedReadMethod <T> readMethod, out T item)
{
using (DBExecutor dbExecutor = EnsureSpecialDbExecutor(select.UserConnection, select.QueryKind)) {
using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
if (dataReader.Read())
{
item = readMethod(dataReader);
return(true);
}
item = default(T);
return(false);
}
}
}
/// <summary>
/// Determines whether the current process element is executed.
/// </summary>
/// <returns></returns>
public virtual bool GetIsExecuted()
{
CoreEntitySchema entitySchema = UserConnection.EntitySchemaManager.GetInstanceByUId(ObjectSchemaId);
var select =
(Select) new Select(UserConnection)
.Column("Id")
.From(entitySchema.Name)
.Where("Id").IsEqual(Column.Parameter(RecordId));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
return(dataReader.Read());
}
}
}
private IEnumerable <ParticipantsAnalyticsItem> GetParticipantsAnalyticsItems(Select analyticsSelect)
{
var analyticsItems = new Dictionary <Guid, ParticipantsAnalyticsItem>();
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
analyticsSelect.ExecuteReader((IDataReader reader) => {
var itemId = reader.GetColumnValue <Guid>("CampaignItemId");
var statusId = reader.GetColumnValue <Guid>("statusId");
var isStepCompleted = reader.GetColumnValue <bool>("StepCompleted");
var count = reader.GetColumnValue <int>("Count");
SetParticipantsCountValue(ref analyticsItems, itemId, statusId, isStepCompleted, count);
});
}
return(analyticsItems.Values);
}
