private static List <UserActivity> GetUserActivities(int tenant, Guid?userOrDept, Guid product, IEnumerable <Guid> modules, int action, IEnumerable <string> containers, DateTime from, DateTime to, int offset, int count)
{
// HACK: optimize sql query for MySql Server 5.1
var table = "webstudio_useractivity" + (userOrDept.GetValueOrDefault() != default(Guid) && DbRegistry.GetSqlDialect(dbid).GetType().Name == "MySQLDialect" ? " use index (productid)" : string.Empty);
var q = new SqlQuery(table)
.Select("id", "tenantid", "productid", "moduleid", "userid", "contentid", "containerid")
.Select("actiontype", "actiontext", "businessvalue", "additionaldata", "activitydate")
.Select("url", "title", "partid", "imagefilename", "htmlpreview", "securityid")
.Where("tenantid", tenant);
//userid
if (userOrDept.GetValueOrDefault() != default(Guid))
{
if (CoreContext.UserManager.UserExists(userOrDept.Value))
{
q.Where("userid", userOrDept.Value.ToString());
}
else
{
q.Where(Exp.In("userid", CoreContext.UserManager.GetUsersByGroup(userOrDept.Value).Select(u => u.ID.ToString()).ToArray()));
}
}
//productId
if (product != default(Guid))
{
q.Where("productid", product.ToString());
}
//moduleIds
if (modules != null && modules.Any())
{
q.Where(Exp.In("moduleid", modules.Select(m => m.ToString()).ToArray()));
}
//actionType
if (action != UserActivityConstants.AllActionType)
{
q.Where("actiontype", action);
}
//containerIds
if (containers != null && containers.Any())
{
q.Where(Exp.In("containerid", containers.ToArray()));
}
//dates
if (from != DateTime.MinValue)
{
q.Where(Exp.Ge("activitydate", TenantUtil.DateTimeToUtc(from).Date));
}
if (to != DateTime.MaxValue)
{
q.Where(Exp.Le("activitydate", TenantUtil.DateTimeToUtc(to).Date.AddTicks(TimeSpan.TicksPerDay - 1)));
}
//limits
if (0 < offset)
{
q.SetFirstResult(offset);
}
if (0 < count)
{
q.SetMaxResults(count);
}
q.OrderBy("id", false);
var key = BuildKey(q);
var result = cache.Get(key) as List <UserActivity>;
if (result == null)
{
using (var db = GetDbManager())
{
result = GetActivities(db, q, count);
lock (cache)
{
var depkey = BuildDependencyKey(tenant, product);
if (cache.Get(depkey) == null)
{
cache.Insert(depkey, depkey);
}
cache.Insert(key, result, new CacheDependency(null, new[] { depkey }), DateTime.Now.Add(expiration), Cache.NoSlidingExpiration);
}
}
}
return(result);
}
public File SaveFile(File file, Stream fileStream)
{
if (file == null)
{
throw new ArgumentNullException("file");
}
if (SetupInfo.MaxChunkedUploadSize < file.ContentLength)
{
throw FileSizeComment.GetFileSizeException(SetupInfo.MaxChunkedUploadSize);
}
if (CoreContext.Configuration.Personal && SetupInfo.IsVisibleSettings("PersonalMaxSpace"))
{
if (CoreContext.Configuration.PersonalMaxSpace - Global.GetUserUsedSpace(file.ID == null ? SecurityContext.CurrentAccount.ID : file.CreateBy) < file.ContentLength)
{
throw FileSizeComment.GetPersonalFreeSpaceException(CoreContext.Configuration.PersonalMaxSpace);
}
}
var isNew = false;
lock (syncRoot)
{
using (var tx = dbManager.BeginTransaction())
{
if (file.ID == null)
{
file.ID = dbManager.ExecuteScalar <int>(new SqlQuery("files_file").SelectMax("id")) + 1;
file.Version = 1;
file.VersionGroup = 1;
isNew = true;
}
file.Title = Global.ReplaceInvalidCharsAndTruncate(file.Title);
//make lowerCase
file.Title = FileUtility.ReplaceFileExtension(file.Title, FileUtility.GetFileExtension(file.Title));
file.ModifiedBy = SecurityContext.CurrentAccount.ID;
file.ModifiedOn = TenantUtil.DateTimeNow();
if (file.CreateBy == default(Guid))
{
file.CreateBy = SecurityContext.CurrentAccount.ID;
}
if (file.CreateOn == default(DateTime))
{
file.CreateOn = TenantUtil.DateTimeNow();
}
dbManager.ExecuteNonQuery(
Update("files_file")
.Set("current_version", false)
.Where("id", file.ID)
.Where("current_version", true));
var sql = Insert("files_file")
.InColumnValue("id", file.ID)
.InColumnValue("version", file.Version)
.InColumnValue("version_group", file.VersionGroup)
.InColumnValue("current_version", true)
.InColumnValue("folder_id", file.FolderID)
.InColumnValue("title", file.Title)
.InColumnValue("content_length", file.ContentLength)
.InColumnValue("category", (int)file.FilterType)
.InColumnValue("create_by", file.CreateBy.ToString())
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(file.CreateOn))
.InColumnValue("modified_by", file.ModifiedBy.ToString())
.InColumnValue("modified_on", TenantUtil.DateTimeToUtc(file.ModifiedOn))
.InColumnValue("converted_type", file.ConvertedType)
.InColumnValue("comment", file.Comment);
dbManager.ExecuteNonQuery(sql);
tx.Commit();
file.PureTitle = file.Title;
var parentFoldersIds = dbManager.ExecuteList(
new SqlQuery("files_folder_tree")
.Select("parent_id")
.Where(Exp.Eq("folder_id", file.FolderID))
.OrderBy("level", false)
).ConvertAll(row => row[0]);
if (parentFoldersIds.Count > 0)
{
dbManager.ExecuteNonQuery(
Update("files_folder")
.Set("modified_on", TenantUtil.DateTimeToUtc(file.ModifiedOn))
.Set("modified_by", file.ModifiedBy.ToString())
.Where(Exp.In("id", parentFoldersIds)));
}
if (isNew)
{
RecalculateFilesCount(dbManager, file.FolderID);
}
}
}
if (fileStream != null)
{
try
{
SaveFileStream(file, fileStream);
}
catch
{
if (isNew)
{
var stored = Global.GetStore().IsDirectory(GetUniqFileDirectory(file.ID));
DeleteFile(file.ID, stored);
}
else if (!IsExistOnStorage(file))
{
DeleteVersion(file);
}
throw;
}
}
return(GetFile(file.ID));
}
private Task SaveOrUpdateTask(Task newTask, DbManager db)
{
if (String.IsNullOrEmpty(newTask.Title) || newTask.DeadLine == DateTime.MinValue ||
newTask.CategoryID <= 0)
{
throw new ArgumentException();
}
if (newTask.ID == 0 || db.ExecuteScalar <int>(Query("crm_task").SelectCount().Where(Exp.Eq("id", newTask.ID))) == 0)
{
newTask.CreateOn = DateTime.UtcNow;
newTask.CreateBy = ASC.Core.SecurityContext.CurrentAccount.ID;
newTask.LastModifedOn = DateTime.UtcNow;
newTask.LastModifedBy = ASC.Core.SecurityContext.CurrentAccount.ID;
newTask.ID = db.ExecuteScalar <int>(
Insert("crm_task")
.InColumnValue("id", 0)
.InColumnValue("title", newTask.Title)
.InColumnValue("description", newTask.Description)
.InColumnValue("deadline", TenantUtil.DateTimeToUtc(newTask.DeadLine))
.InColumnValue("responsible_id", newTask.ResponsibleID)
.InColumnValue("contact_id", newTask.ContactID)
.InColumnValue("entity_type", (int)newTask.EntityType)
.InColumnValue("entity_id", newTask.EntityID)
.InColumnValue("is_closed", newTask.IsClosed)
.InColumnValue("category_id", newTask.CategoryID)
.InColumnValue("create_on", newTask.CreateOn)
.InColumnValue("create_by", newTask.CreateBy)
.InColumnValue("last_modifed_on", newTask.LastModifedOn)
.InColumnValue("last_modifed_by", newTask.LastModifedBy)
.InColumnValue("alert_value", newTask.AlertValue)
.Identity(1, 0, true));
}
else
{
var oldTask = db.ExecuteList(GetTaskQuery(Exp.Eq("id", newTask.ID)))
.ConvertAll(row => ToTask(row))
.FirstOrDefault();
CRMSecurity.DemandEdit(oldTask);
newTask.CreateOn = oldTask.CreateOn;
newTask.CreateBy = oldTask.CreateBy;
newTask.LastModifedOn = DateTime.UtcNow;
newTask.LastModifedBy = ASC.Core.SecurityContext.CurrentAccount.ID;
newTask.IsClosed = oldTask.IsClosed;
db.ExecuteNonQuery(
Update("crm_task")
.Set("title", newTask.Title)
.Set("description", newTask.Description)
.Set("deadline", TenantUtil.DateTimeToUtc(newTask.DeadLine))
.Set("responsible_id", newTask.ResponsibleID)
.Set("contact_id", newTask.ContactID)
.Set("entity_type", (int)newTask.EntityType)
.Set("entity_id", newTask.EntityID)
.Set("category_id", newTask.CategoryID)
.Set("last_modifed_on", newTask.LastModifedOn)
.Set("last_modifed_by", newTask.LastModifedBy)
.Set("alert_value", (int)newTask.AlertValue)
.Set("exec_alert", 0)
.Where(Exp.Eq("id", newTask.ID)));
}
return(newTask);
}
public File ReplaceFileVersion(File file, Stream fileStream)
{
if (file == null)
{
throw new ArgumentNullException("file");
}
if (file.ID == null)
{
throw new ArgumentException("No file id or folder id toFolderId determine provider");
}
if (SetupInfo.MaxChunkedUploadSize < file.ContentLength)
{
throw FileSizeComment.GetFileSizeException(SetupInfo.MaxChunkedUploadSize);
}
if (CoreContext.Configuration.Personal && SetupInfo.IsVisibleSettings("PersonalMaxSpace"))
{
if (CoreContext.Configuration.PersonalMaxSpace - Global.GetUserUsedSpace(file.ID == null ? SecurityContext.CurrentAccount.ID : file.CreateBy) < file.ContentLength)
{
throw FileSizeComment.GetPersonalFreeSpaceException(CoreContext.Configuration.PersonalMaxSpace);
}
}
List <object> parentFoldersIds;
lock (syncRoot)
{
using (var tx = dbManager.BeginTransaction())
{
file.Title = Global.ReplaceInvalidCharsAndTruncate(file.Title);
//make lowerCase
file.Title = FileUtility.ReplaceFileExtension(file.Title, FileUtility.GetFileExtension(file.Title));
file.ModifiedBy = SecurityContext.CurrentAccount.ID;
file.ModifiedOn = TenantUtil.DateTimeNow();
if (file.CreateBy == default(Guid))
{
file.CreateBy = SecurityContext.CurrentAccount.ID;
}
if (file.CreateOn == default(DateTime))
{
file.CreateOn = TenantUtil.DateTimeNow();
}
var sql = Update("files_file")
.Set("version", file.Version)
.Set("version_group", file.VersionGroup)
.Set("folder_id", file.FolderID)
.Set("title", file.Title)
.Set("content_length", file.ContentLength)
.Set("category", (int)file.FilterType)
.Set("create_by", file.CreateBy.ToString())
.Set("create_on", TenantUtil.DateTimeToUtc(file.CreateOn))
.Set("modified_by", file.ModifiedBy.ToString())
.Set("modified_on", TenantUtil.DateTimeToUtc(file.ModifiedOn))
.Set("converted_type", file.ConvertedType)
.Set("comment", file.Comment)
.Set("encrypted", file.Encrypted)
.Set("forcesave", (int)file.Forcesave)
.Where("id", file.ID)
.Where("version", file.Version);
dbManager.ExecuteNonQuery(sql);
tx.Commit();
file.PureTitle = file.Title;
parentFoldersIds = dbManager.ExecuteList(
new SqlQuery("files_folder_tree")
.Select("parent_id")
.Where(Exp.Eq("folder_id", file.FolderID))
.OrderBy("level", false)
).ConvertAll(row => row[0]);
if (parentFoldersIds.Count > 0)
{
dbManager.ExecuteNonQuery(
Update("files_folder")
.Set("modified_on", TenantUtil.DateTimeToUtc(file.ModifiedOn))
.Set("modified_by", file.ModifiedBy.ToString())
.Where(Exp.In("id", parentFoldersIds)));
}
}
}
if (fileStream != null)
{
try
{
DeleteVersionStream(file);
SaveFileStream(file, fileStream);
}
catch
{
if (!IsExistOnStorage(file))
{
DeleteVersion(file);
}
throw;
}
}
FactoryIndexer <FilesWrapper> .IndexAsync(FilesWrapper.GetFilesWrapper(file, parentFoldersIds));
return(GetFile(file.ID));
}
public object SaveFolder(Folder folder)
{
if (folder == null)
{
throw new ArgumentNullException("folder");
}
folder.Title = Global.ReplaceInvalidCharsAndTruncate(folder.Title);
folder.ModifiedOn = TenantUtil.DateTimeNow();
folder.ModifiedBy = SecurityContext.CurrentAccount.ID;
if (folder.CreateOn == default(DateTime))
{
folder.CreateOn = TenantUtil.DateTimeNow();
}
if (folder.CreateBy == default(Guid))
{
folder.CreateBy = SecurityContext.CurrentAccount.ID;
}
var isnew = false;
using (var tx = dbManager.BeginTransaction(true))
{
if (folder.ID != null && IsExist(folder.ID))
{
dbManager.ExecuteNonQuery(
Update("files_folder")
.Set("title", folder.Title)
.Set("create_by", folder.CreateBy.ToString())
.Set("modified_on", TenantUtil.DateTimeToUtc(folder.ModifiedOn))
.Set("modified_by", folder.ModifiedBy.ToString())
.Where("id", folder.ID));
}
else
{
isnew = true;
folder.ID = dbManager.ExecuteScalar <int>(
Insert("files_folder")
.InColumnValue("id", 0)
.InColumnValue("parent_id", folder.ParentFolderID)
.InColumnValue("title", folder.Title)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(folder.CreateOn))
.InColumnValue("create_by", folder.CreateBy.ToString())
.InColumnValue("modified_on", TenantUtil.DateTimeToUtc(folder.ModifiedOn))
.InColumnValue("modified_by", folder.ModifiedBy.ToString())
.InColumnValue("folder_type", (int)folder.FolderType)
.Identity(1, 0, true));
//itself link
dbManager.ExecuteNonQuery(
new SqlInsert("files_folder_tree")
.InColumns("folder_id", "parent_id", "level")
.Values(folder.ID, folder.ID, 0));
//full path to root
dbManager.ExecuteNonQuery(
new SqlInsert("files_folder_tree")
.InColumns("folder_id", "parent_id", "level")
.Values(
new SqlQuery("files_folder_tree t")
.Select(folder.ID.ToString(), "t.parent_id", "t.level + 1")
.Where("t.folder_id", folder.ParentFolderID)));
}
tx.Commit();
}
if (!dbManager.InTransaction && isnew)
{
RecalculateFoldersCount(folder.ID);
}
FactoryIndexer <FoldersWrapper> .IndexAsync(folder);
return(folder.ID);
}
private Exp WhereConditional(String tblAlias,
ICollection <int> exceptIDs,
String searchText,
InvoiceStatus?status,
DateTime issueDateFrom,
DateTime issueDateTo,
DateTime dueDateFrom,
DateTime dueDateTo,
EntityType entityType,
int entityID,
String currency)
{
var tblAliasPrefix = !String.IsNullOrEmpty(tblAlias) ? tblAlias + "." : "";
var conditions = new List <Exp>();
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
case EntityType.Person:
case EntityType.Company:
conditions.Add(Exp.Eq(tblAliasPrefix + "contact_id", entityID));
break;
case EntityType.Case:
case EntityType.Opportunity:
conditions.Add(Exp.Eq(tblAliasPrefix + "entity_id", entityID) &
Exp.Eq(tblAliasPrefix + "entity_type", (int)entityType));
break;
}
}
if (status != null)
{
conditions.Add(Exp.Eq(tblAliasPrefix + "status", (int)status.Value));
}
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
if (keywords.Length > 0)
{
//if (FullTextSearch.SupportModule(FullTextSearch.CRMInvoiceModule))
//{
// ids = FullTextSearch.Search(searchText, FullTextSearch.CRMInvoiceModule)
// .GetIdentifiers()
// .Select(item => Convert.ToInt32(item.Split('_')[1])).Distinct().ToList();
// if (ids.Count == 0) return null;
//}
//else
conditions.Add(BuildLike(new[] { tblAliasPrefix + "number", tblAliasPrefix + "description" }, keywords));
}
}
if (exceptIDs.Count > 0)
{
conditions.Add(!Exp.In(tblAliasPrefix + "id", exceptIDs.ToArray()));
}
if (issueDateFrom != DateTime.MinValue && issueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Between(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom), TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1))));
}
else if (issueDateFrom != DateTime.MinValue)
{
conditions.Add(Exp.Ge(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom)));
}
else if (issueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Le(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1))));
}
if (dueDateFrom != DateTime.MinValue && dueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Between(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom), TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1))));
}
else if (dueDateFrom != DateTime.MinValue)
{
conditions.Add(Exp.Ge(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom)));
}
else if (dueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Le(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1))));
}
if (!String.IsNullOrEmpty(currency))
{
conditions.Add(Exp.Eq(tblAliasPrefix + "currency", currency));
}
if (conditions.Count == 0)
{
return(null);
}
return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j));
}
public int GetDealsCount(String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate)
{
var cacheKey = TenantID.ToString(CultureInfo.InvariantCulture) +
"deals" +
SecurityContext.CurrentAccount.ID.ToString() +
searchText +
responsibleID +
milestoneID +
contactID;
if (tags != null)
{
cacheKey += String.Join("", tags.ToArray());
}
if (stageType.HasValue)
{
cacheKey += stageType.Value;
}
if (contactAlsoIsParticipant.HasValue)
{
cacheKey += contactAlsoIsParticipant.Value;
}
if (fromDate != DateTime.MinValue)
{
cacheKey += fromDate.ToString();
}
if (toDate != DateTime.MinValue)
{
cacheKey += toDate.ToString();
}
var fromCache = _cache.Get <string>(cacheKey);
if (fromCache != null)
{
return(Convert.ToInt32(fromCache));
}
var withParams = !(String.IsNullOrEmpty(searchText) &&
responsibleID == Guid.Empty &&
milestoneID <= 0 &&
(tags == null || !tags.Any()) &&
contactID <= 0 &&
stageType == null &&
contactAlsoIsParticipant == null &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
ICollection <int> exceptIDs = CRMSecurity.GetPrivateItems(typeof(Deal)).ToList();
int result;
if (withParams)
{
var whereConditional = WhereConditional(exceptIDs, searchText, responsibleID, milestoneID, tags,
contactID, stageType, contactAlsoIsParticipant);
var sqlQuery = GetDealSqlQuery(whereConditional);
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Having(Exp.Between("close_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate)));
result = Db.ExecuteList(sqlQuery).Count;
}
else if (whereConditional == null)
{
result = 0;
}
else
{
result = Db.ExecuteList(sqlQuery).Count;
}
}
else
{
var countWithoutPrivate = Db.ExecuteScalar <int>(Query("crm_deal").SelectCount());
var privateCount = exceptIDs.Count;
if (privateCount > countWithoutPrivate)
{
_log.Error("Private deals count more than all deals");
privateCount = 0;
}
result = countWithoutPrivate - privateCount;
}
if (result > 0)
{
_cache.Insert(cacheKey, result, TimeSpan.FromSeconds(30));
}
return(result);
}
public IEnumerable <Tag> SaveTags(params Tag[] tags)
{
var result = new List <Tag>();
if (tags == null)
{
return(result);
}
tags = tags.Where(x => x != null && !x.EntryId.Equals(null) && !x.EntryId.Equals(0)).ToArray();
if (tags.Length == 0)
{
return(result);
}
lock (syncRoot)
{
using (var DbManager = GetDb())
using (var tx = DbManager.BeginTransaction())
{
var mustBeDeleted = DbManager.ExecuteList(Query("files_tag ft")
.Select("ftl.tag_id",
"ftl.entry_id",
"ftl.entry_type")
.Distinct()
.InnerJoin("files_tag_link ftl",
Exp.EqColumns("ft.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("ft.id", "ftl.tag_id"))
.Where(Exp.Eq("ft.flag", (int)TagType.New) &
Exp.Le("create_on", TenantUtil.DateTimeNow().AddMonths(-3))));
foreach (var row in mustBeDeleted)
{
DbManager.ExecuteNonQuery(Delete("files_tag_link")
.Where(Exp.Eq("tag_id", row[0]) &
Exp.Eq("entry_id", row[1]) &
Exp.Eq("entry_type", row[2])));
}
DbManager.ExecuteNonQuery(Delete("files_tag").Where(Exp.EqColumns("0", Query("files_tag_link l").SelectCount().Where(Exp.EqColumns("tag_id", "id")))));
var createOn = TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow());
var cacheTagId = new Dictionary <String, int>();
foreach (var t in tags)
{
int id;
var cacheTagIdKey = String.Join("/", new[] { TenantID.ToString(), t.Owner.ToString(), t.TagName, ((int)t.TagType).ToString(CultureInfo.InvariantCulture) });
if (!cacheTagId.TryGetValue(cacheTagIdKey, out id))
{
id = DbManager.ExecuteScalar <int>(Query("files_tag")
.Select("id")
.Where("owner", t.Owner.ToString())
.Where("name", t.TagName)
.Where("flag", (int)t.TagType));
if (id == 0)
{
var i1 = Insert("files_tag")
.InColumnValue("id", 0)
.InColumnValue("name", t.TagName)
.InColumnValue("owner", t.Owner.ToString())
.InColumnValue("flag", (int)t.TagType)
.Identity(1, 0, true);
id = DbManager.ExecuteScalar <int>(i1);
}
cacheTagId.Add(cacheTagIdKey, id);
}
t.Id = id;
result.Add(t);
var i2 = Insert("files_tag_link")
.InColumnValue("tag_id", id)
.InColumnValue("entry_id", MappingID(t.EntryId, true))
.InColumnValue("entry_type", (int)t.EntryType)
.InColumnValue("create_by", ASC.Core.SecurityContext.CurrentAccount.ID)
.InColumnValue("create_on", createOn)
.InColumnValue("tag_count", t.Count);
DbManager.ExecuteNonQuery(i2);
}
tx.Commit();
}
}
return(result);
}
public virtual int SaveProviderInfo(string providerKey, string customerTitle, AuthData authData, FolderType folderType)
{
var prKey = (ProviderTypes)Enum.Parse(typeof(ProviderTypes), providerKey, true);
authData = GetEncodedAccesToken(authData, prKey);
if (!CheckProviderInfo(ToProviderInfo(0, providerKey, customerTitle, authData, SecurityContext.CurrentAccount.ID.ToString(), folderType, TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))))
{
throw new UnauthorizedAccessException(string.Format(FilesCommonResource.ErrorMassage_SecurityException_Auth, providerKey));
}
var queryInsert = new SqlInsert(TableTitle, true)
.InColumnValue("id", 0)
.InColumnValue("tenant_id", TenantID)
.InColumnValue("provider", prKey.ToString())
.InColumnValue("customer_title", customerTitle)
.InColumnValue("user_name", authData.Login)
.InColumnValue("password", EncryptPassword(authData.Password))
.InColumnValue("folder_type", (int)folderType)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))
.InColumnValue("user_id", SecurityContext.CurrentAccount.ID.ToString())
.InColumnValue("token", authData.Token)
.InColumnValue("url", authData.Url)
.Identity(0, 0, true);
return(Int32.Parse(DbManager.ExecuteScalar <string>(queryInsert)));
}
public void ProcessRequest(HttpContext context)
{
//Process authorization
if (!ProcessAuthorization(context))
{
AccessDenied(context);
return;
}
var productId = Helpers.ParseGuid(context.Request[ProductParam]);
var moduleIds = Helpers.ParseGuids(context.Request[ModuleParam]);
var actionType = Helpers.ParseInt(context.Request[ActionTypeParam]);
var containerIds = Helpers.Tokenize(context.Request[ContainerParam]);
var title = context.Request[TitleParam];
if (string.IsNullOrEmpty(title))
{
title = Resources.Resource.RecentActivity;
}
var userIds = Helpers.ParseGuid(context.Request[UserParam]);
if (userIds.HasValue)
{
throw new NotImplementedException();
}
var userActivity = UserActivityManager.GetUserActivities(
TenantProvider.CurrentTenantID,
null,
productId.GetValueOrDefault(),
moduleIds,
actionType.GetValueOrDefault(),
containerIds,
0, 100);
var feedItems = userActivity.Select(x =>
{
var user = CoreContext.UserManager.GetUsers(x.UserID);
var veloContext = Formatter.PrepareContext(x, user);
var item = new SyndicationItem(
HttpUtility.HtmlDecode(Formatter.Format(context, "title.txt", veloContext)),
SyndicationContent.CreateHtmlContent(Formatter.Format(context, "pattern.html", veloContext)),
new Uri(CommonLinkUtility.GetFullAbsolutePath(x.URL)),
x.ID.ToString(),
new DateTimeOffset(TenantUtil.DateTimeToUtc(x.Date)))
{
PublishDate = x.Date,
Summary = SyndicationContent.CreatePlaintextContent(Formatter.Format(context, "pattern.txt", veloContext))
};
var person = new SyndicationPerson(user.Email)
{
Name = user.UserName
};
if (productId.HasValue)
{
person.Uri = CommonLinkUtility.GetUserProfile(user.ID, productId.Value);
}
item.Authors.Add(person);
return(item);
});
var lastUpdate = new DateTimeOffset(DateTime.UtcNow);
if (userActivity.Count > 0)
{
lastUpdate = new DateTimeOffset(userActivity.Max(x => x.Date));
}
var feed = new SyndicationFeed(
title,
Resources.Resource.RecentActivity,
new Uri(context.Request.GetUrlRewriter(), VirtualPathUtility.ToAbsolute(Studio.WhatsNew.PageUrl)),
TenantProvider.CurrentTenantID.ToString(),
lastUpdate, feedItems);
var rssFormatter = new Atom10FeedFormatter(feed);
//Set writer settings
var settings = new XmlWriterSettings();
settings.CheckCharacters = false;
settings.ConformanceLevel = ConformanceLevel.Document;
settings.Encoding = Encoding.UTF8;
settings.Indent = true;
using (var writer = XmlWriter.Create(context.Response.Output, settings))
{
rssFormatter.WriteTo(writer);
}
context.Response.Charset = Encoding.UTF8.WebName;
context.Response.ContentType = "application/atom+xml";
}
public VoipCall SaveOrUpdateCall(VoipCall call)
{
var voipCall = new DbVoipCall
{
TenantId = TenantID,
Id = call.Id,
NumberFrom = call.From,
NumberTo = call.To,
ContactId = call.ContactId
};
if (!string.IsNullOrEmpty(call.ParentID))
{
voipCall.ParentCallId = call.ParentID;
}
if (call.Status.HasValue)
{
voipCall.Status = (int)call.Status.Value;
}
if (!call.AnsweredBy.Equals(Guid.Empty))
{
voipCall.AnsweredBy = call.AnsweredBy;
}
if (call.DialDate == DateTime.MinValue)
{
call.DialDate = DateTime.UtcNow;
}
voipCall.DialDate = TenantUtil.DateTimeToUtc(call.DialDate);
if (call.DialDuration > 0)
{
voipCall.DialDuration = call.DialDuration;
}
if (call.Price > decimal.Zero)
{
voipCall.Price = call.Price;
}
if (call.VoipRecord != null)
{
if (!string.IsNullOrEmpty(call.VoipRecord.Id))
{
voipCall.RecordSid = call.VoipRecord.Id;
}
if (!string.IsNullOrEmpty(call.VoipRecord.Uri))
{
voipCall.RecordUrl = call.VoipRecord.Uri;
}
if (call.VoipRecord.Duration != 0)
{
voipCall.RecordDuration = call.VoipRecord.Duration;
}
if (call.VoipRecord.Price != default)
{
voipCall.RecordPrice = call.VoipRecord.Price;
}
}
VoipDbContext.VoipCalls.Add(voipCall);
VoipDbContext.SaveChanges();
return(call);
}
public RelationshipEvent CreateItem(RelationshipEvent item)
{
var htmlBody = String.Empty;
if (String.IsNullOrEmpty(item.Content) || item.CategoryID == 0 || (item.ContactID == 0 && item.EntityID == 0))
{
throw new ArgumentException();
}
if (item.EntityID > 0 && item.EntityType != EntityType.Opportunity && item.EntityType != EntityType.Case)
{
throw new ArgumentException();
}
if (item.CreateOn == DateTime.MinValue)
{
item.CreateOn = TenantUtil.DateTimeNow();
}
item.CreateBy = ASC.Core.SecurityContext.CurrentAccount.ID;
item.LastModifedBy = ASC.Core.SecurityContext.CurrentAccount.ID;
if (item.CategoryID == (int)HistoryCategorySystem.MailMessage)
{
var jsonObj = JObject.Parse(item.Content);
var messageId = jsonObj.Value <Int32>("message_id");
var apiServer = new Api.ApiServer();
var msg = apiServer.GetApiResponse(
String.Format("{0}mail/messages/{1}.json?id={1}&unblocked=true&is_need_to_sanitize_html=true", SetupInfo.WebApiBaseUrl, messageId), "GET");
if (msg == null)
{
throw new ArgumentException();
}
var msgResponseWrapper = JObject.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(msg)));
var msgRequestObj = msgResponseWrapper.Value <JObject>("response");
string messageUrl;
htmlBody = msgRequestObj.Value <String>("htmlBody");
using (var fileStream = new MemoryStream(Encoding.UTF8.GetBytes(htmlBody)))
{
var filePath = String.Format("folder_{0}/message_{1}.html", (messageId / 1000 + 1) * 1000, messageId);
Global.GetStore().Save("mail_messages", filePath, fileStream);
messageUrl = String.Format("{0}HttpHandlers/filehandler.ashx?action=mailmessage&message_id={1}", PathProvider.BaseAbsolutePath, messageId).ToLower();
}
var msg_date_created = msgRequestObj.Value <String>("date");
item.Content = JsonConvert.SerializeObject(new
{
@from = msgRequestObj.Value <String>("from"),
to = msgRequestObj.Value <String>("to"),
cc = msgRequestObj.Value <String>("cc"),
bcc = msgRequestObj.Value <String>("bcc"),
subject = msgRequestObj.Value <String>("subject"),
important = msgRequestObj.Value <Boolean>("important"),
chain_id = msgRequestObj.Value <String>("chainId"),
is_sended = msgRequestObj.Value <Int32>("folder") != 1,
date_created = msg_date_created,
introduction = msgRequestObj.Value <String>("introduction"),
message_id = msgRequestObj.Value <Int32>("id"),
message_url = messageUrl
});
item.CreateOn = DateTime.Parse(msg_date_created, CultureInfo.InvariantCulture);
}
using (var db = GetDb())
{
item.ID = db.ExecuteScalar <int>(
Insert("crm_relationship_event")
.InColumnValue("id", 0)
.InColumnValue("contact_id", item.ContactID)
.InColumnValue("content", item.Content)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(item.CreateOn))
.InColumnValue("create_by", item.CreateBy)
.InColumnValue("entity_type", (int)item.EntityType)
.InColumnValue("entity_id", item.EntityID)
.InColumnValue("category_id", item.CategoryID)
.InColumnValue("last_modifed_on", DateTime.UtcNow)
.InColumnValue("last_modifed_by", item.LastModifedBy)
.InColumnValue("have_files", false)
.Identity(1, 0, true));
if (item.CreateOn.Kind == DateTimeKind.Utc)
{
item.CreateOn = TenantUtil.DateTimeFromUtc(item.CreateOn);
}
return(item);
}
}
public object SaveFolder(Folder folder, DbManager dbManager)
{
bool ownsManager = false;
if (folder == null)
{
throw new ArgumentNullException("folder");
}
folder.ModifiedOn = TenantUtil.DateTimeNow();
folder.ModifiedBy = SecurityContext.CurrentAccount.ID;
if (folder.CreateOn == default(DateTime))
{
folder.CreateOn = TenantUtil.DateTimeNow();
}
if (folder.CreateBy == default(Guid))
{
folder.CreateBy = SecurityContext.CurrentAccount.ID;
}
try
{
if (dbManager == null)
{
ownsManager = true;
dbManager = GetDbManager();
}
using (var tx = dbManager.BeginTransaction(true))
{
if (folder.ID != null && IsExist(folder.ID))
{
dbManager.ExecuteNonQuery(
Update("files_folder")
.Set("title", folder.Title)
.Set("modified_on", TenantUtil.DateTimeToUtc(folder.ModifiedOn))
.Set("modified_by", folder.ModifiedBy.ToString())
.Where("id", folder.ID));
}
else
{
folder.ID = dbManager.ExecuteScalar <int>(
Insert("files_folder")
.InColumnValue("id", 0)
.InColumnValue("parent_id", folder.ParentFolderID)
.InColumnValue("title", folder.Title)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(folder.CreateOn))
.InColumnValue("create_by", folder.CreateBy.ToString())
.InColumnValue("modified_on", TenantUtil.DateTimeToUtc(folder.ModifiedOn))
.InColumnValue("modified_by", folder.ModifiedBy.ToString())
.InColumnValue("folder_type", (int)folder.FolderType)
.Identity(1, 0, true));
//itself link
dbManager.ExecuteNonQuery(
new SqlInsert("files_folder_tree")
.InColumns("folder_id", "parent_id", "level")
.Values(folder.ID, folder.ID, 0));
//full path to root
dbManager.ExecuteNonQuery(
new SqlInsert("files_folder_tree")
.InColumns("folder_id", "parent_id", "level")
.Values(
new SqlQuery("files_folder_tree t")
.Select(folder.ID.ToString(), "t.parent_id", "t.level + 1")
.Where("t.folder_id", folder.ParentFolderID)));
}
tx.Commit();
}
if (!dbManager.InTransaction)
{
RecalculateFoldersCount(dbManager, folder.ID);
}
}
finally
{
if (dbManager != null && ownsManager && !dbManager.Disposed)
{
//If it's our manager - dispose
dbManager.Dispose();
}
}
return(folder.ID);
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
Guid responsibleID,
int categoryID,
bool?isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
if (responsibleID != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
}
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
var isCompany = Convert.ToBoolean(DbManager.ExecuteScalar(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID))));
if (isCompany)
{
return(WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy));
}
else
{
return(WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy));
}
case EntityType.Person:
sqlQuery.Where(Exp.Eq("contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
{
sqlQuery.Where(Exp.Eq("contact_id", entityID));
}
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In("contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq("entity_id", entityID) &
Exp.Eq("entity_type", (int)entityType));
break;
}
}
if (isClosed.HasValue)
{
sqlQuery.Where("is_closed", isClosed);
}
if (categoryID > 0)
{
sqlQuery.Where(Exp.Eq("category_id", categoryID));
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Between("deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
else if (fromDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Ge("deadline", TenantUtil.DateTimeToUtc(fromDate)));
}
else if (toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Le("deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
sqlQuery.OrderBy("is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
{
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy("title", orderBy.IsAsc)
.OrderBy("deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy("deadline", orderBy.IsAsc)
.OrderBy("title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy("category_id", orderBy.IsAsc)
.OrderBy("deadline", true)
.OrderBy("title", true);
break;
}
}
else
{
sqlQuery.OrderBy("deadline", true)
.OrderBy("title", true);
}
return(sqlQuery);
}
public virtual int UpdateProviderInfo(int linkId, string customerTitle, AuthData newAuthData, FolderType folderType, Guid?userId = null)
{
var authData = new AuthData();
if (newAuthData != null && !newAuthData.IsEmpty())
{
var querySelect = new SqlQuery(TableTitle)
.Select("provider", "url", "user_name", "password")
.Where("tenant_id", TenantID)
.Where("id", linkId);
object[] input;
try
{
using (var db = GetDb())
{
input = db.ExecuteList(querySelect).Single();
}
}
catch (Exception e)
{
Global.Logger.Error(string.Format("UpdateProviderInfo: linkId = {0} , user = {1}", linkId, SecurityContext.CurrentAccount.ID), e);
throw;
}
var providerKey = (string)input[0];
ProviderTypes key;
if (!Enum.TryParse(providerKey, true, out key))
{
throw new ArgumentException("Unrecognize ProviderType");
}
authData = new AuthData(
!string.IsNullOrEmpty(newAuthData.Url) ? newAuthData.Url : (string)input[1],
(string)input[2],
!string.IsNullOrEmpty(newAuthData.Password) ? newAuthData.Password : DecryptPassword(input[3] as string),
newAuthData.Token);
if (!string.IsNullOrEmpty(newAuthData.Token))
{
authData = GetEncodedAccesToken(authData, key);
}
if (!CheckProviderInfo(ToProviderInfo(0, providerKey, customerTitle, authData, SecurityContext.CurrentAccount.ID.ToString(), folderType, TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))))
{
throw new UnauthorizedAccessException(string.Format(FilesCommonResource.ErrorMassage_SecurityException_Auth, providerKey));
}
}
var queryUpdate = new SqlUpdate(TableTitle)
.Where("id", linkId)
.Where("tenant_id", TenantID);
if (!string.IsNullOrEmpty(customerTitle))
{
queryUpdate
.Set("customer_title", customerTitle);
}
if (folderType != FolderType.DEFAULT)
{
queryUpdate
.Set("folder_type", (int)folderType);
}
if (userId.HasValue)
{
queryUpdate
.Set("user_id", userId.Value.ToString());
}
if (!authData.IsEmpty())
{
queryUpdate
.Set("user_name", authData.Login ?? "")
.Set("password", EncryptPassword(authData.Password))
.Set("token", EncryptPassword(authData.Token ?? ""))
.Set("url", authData.Url ?? "");
}
using (var db = GetDb())
{
return(db.ExecuteNonQuery(queryUpdate) == 1 ? linkId : default(int));
}
}
//public ReportWrapper BuildWorkLoadReport(Guid responsibleID)
//{
// var sqlSubQuery =
// Query("crm_task")
// .Select("responsible_id,category_id, is_closed, count(*) as total")
// .GroupBy(0, 1, 2)
// .OrderBy(0, true)
// .OrderBy(1, true);
// if (responsibleID != Guid.Empty)
// sqlSubQuery.Where(Exp.Eq("tbl_task.responsible_id", responsibleID));
// var sqlQuery = new SqlQuery()
// .Select("tbl_stat.*", "tbl_task_category.title")
// .From(sqlSubQuery, "tbl_stat")
// .LeftOuterJoin("crm_list_item tbl_task_category", Exp.EqColumns("tbl_stat.category_id", "tbl_task_category.id"));
// var sqlResult = DbManager.ExecuteList(sqlQuery);
// var report = new ReportWrapper
// {
// ReportTitle = CRMReportResource.Report_WorkLoad_Title,
// ReportDescription = CRMReportResource.Report_WorkLoad_Description
// };
// //var responsibleStr = String.Empty;
// //if (responsibleID != Guid.Empty)
// // responsibleStr = ASC.Core.CoreContext.UserManager.GetUsers(responsibleID).DisplayUserName();
// //var xDocument = new XDocument(
// // new XElement("report",
// // new XElement("metadata",
// // new XElement("filters", new XElement("filter", new XAttribute("name", "responsible"), responsibleStr, new XAttribute("title", CRMCommonResource.Responsible))),
// // new XElement("description", CRMReportResource.Report_WorkLoad_Description, new XAttribute("title", CRMReportResource.Report_WorkLoad_Title))
// // ),
// // new XElement("content",
// // new XElement("columns",
// // new XElement("column", new XAttribute("name", "user"), ""),
// // new XElement("column", new XAttribute("name", "category"), CRMDealResource.Stage),
// // new XElement("column", new XAttribute("name", "total"), CRMDealResource.DealAmount)
// // ),
// // new XElement("rows",
// // sqlResult.ConvertAll(row =>
// // new XElement("row",
// // new XElement("userID", row[0].ToString()),
// // new XElement("userDisplayName", ASC.Core.CoreContext.UserManager.GetUsers(new Guid(row[0].ToString()))),
// // new XElement("percent", row[4]))
// // ).ToArray()))
// // ));
// throw new NotImplementedException();
//}
public List<object[]> BuildTasksReport(
Guid responsibleID,
DateTime fromDate,
DateTime toDate,
bool? isClosed,
bool showWithoutResponsible)
{
var sqlQuery = Query("crm_task tbl_task");
if (responsibleID != Guid.Empty)
sqlQuery.Where(Exp.Eq("tbl_task.responsible_id", responsibleID));
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Between("tbl_task.deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddHours(-1))));
else if (fromDate != DateTime.MinValue)
sqlQuery.Where(Exp.Ge("tbl_task.deadline", TenantUtil.DateTimeToUtc(fromDate)));
else if (toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Le("tbl_task.deadline", TenantUtil.DateTimeToUtc(toDate)));
if (!showWithoutResponsible)
sqlQuery.Where(!Exp.Eq("tbl_task.responsible_id", Guid.Empty));
if (isClosed.HasValue)
sqlQuery.Where(Exp.Le("tbl_task.is_closed", isClosed.Value));
using (var db = GetDb())
{
return db.ExecuteList(sqlQuery);
}
}
private int SaveOrUpdateInvoice(Invoice invoice, DbManager db)
{
if (String.IsNullOrEmpty(invoice.Number) ||
invoice.IssueDate == DateTime.MinValue ||
invoice.ContactID <= 0 ||
invoice.DueDate == DateTime.MinValue ||
String.IsNullOrEmpty(invoice.Currency) ||
invoice.ExchangeRate <= 0 ||
String.IsNullOrEmpty(invoice.Terms))
{
throw new ArgumentException();
}
invoice.PurchaseOrderNumber = !String.IsNullOrEmpty(invoice.PurchaseOrderNumber) ? invoice.PurchaseOrderNumber : String.Empty;
if (!IsExist(invoice.ID, db))
{
if (IsExist(invoice.Number, db))
{
throw new ArgumentException();
}
invoice.ID = db.ExecuteScalar <int>(
Insert("crm_invoice")
.InColumnValue("id", 0)
.InColumnValue("status", (int)invoice.Status)
.InColumnValue("number", invoice.Number)
.InColumnValue("issue_date", TenantUtil.DateTimeToUtc(invoice.IssueDate))
.InColumnValue("template_type", invoice.TemplateType)
.InColumnValue("contact_id", invoice.ContactID)
.InColumnValue("consignee_id", invoice.ConsigneeID)
.InColumnValue("entity_type", (int)invoice.EntityType)
.InColumnValue("entity_id", invoice.EntityID)
.InColumnValue("due_date", TenantUtil.DateTimeToUtc(invoice.DueDate))
.InColumnValue("language", invoice.Language)
.InColumnValue("currency", invoice.Currency)
.InColumnValue("exchange_rate", invoice.ExchangeRate)
.InColumnValue("purchase_order_number", invoice.PurchaseOrderNumber)
.InColumnValue("terms", invoice.Terms)
.InColumnValue("description", invoice.Description)
.InColumnValue("json_data", null)
.InColumnValue("file_id", 0)
.InColumnValue("create_on", DateTime.UtcNow)
.InColumnValue("create_by", SecurityContext.CurrentAccount.ID)
.InColumnValue("last_modifed_on", DateTime.UtcNow)
.InColumnValue("last_modifed_by", SecurityContext.CurrentAccount.ID)
.Identity(1, 0, true));
}
else
{
var oldInvoice = db.ExecuteList(GetInvoiceSqlQuery(Exp.Eq("id", invoice.ID), null))
.ConvertAll(ToInvoice)
.FirstOrDefault();
CRMSecurity.DemandEdit(oldInvoice);
db.ExecuteNonQuery(
Update("crm_invoice")
.Set("status", (int)invoice.Status)
.Set("issue_date", TenantUtil.DateTimeToUtc(invoice.IssueDate))
.Set("template_type", invoice.TemplateType)
.Set("contact_id", invoice.ContactID)
.Set("consignee_id", invoice.ConsigneeID)
.Set("entity_type", (int)invoice.EntityType)
.Set("entity_id", invoice.EntityID)
.Set("due_date", TenantUtil.DateTimeToUtc(invoice.DueDate))
.Set("language", invoice.Language)
.Set("currency", invoice.Currency)
.Set("exchange_rate", invoice.ExchangeRate)
.Set("purchase_order_number", invoice.PurchaseOrderNumber)
.Set("terms", invoice.Terms)
.Set("description", invoice.Description)
.Set("json_data", null)
.Set("file_id", 0)
.Set("last_modifed_on", DateTime.UtcNow)
.Set("last_modifed_by", SecurityContext.CurrentAccount.ID)
.Where(Exp.Eq("id", invoice.ID)));
}
return(invoice.ID);
}
public List<Object[]> BuildContactPopulateReport(
DateTime fromDate,
DateTime toDate,
bool? isCompany,
String tagName,
int contactType
)
{
var sqlQuery = Query("crm_contact")
.Select("date(create_on) as cur_date")
.Select("count(*) as total")
.GroupBy("cur_date");
if (contactType != 0)
sqlQuery.Where(Exp.Eq("status_id", contactType));
using (var db = GetDb())
{
if (!String.IsNullOrEmpty(tagName))
{
var tagID = db.ExecuteScalar<int>(Query("crm_tag").Select("id")
.Where(Exp.Eq("lower(title)", tagName.ToLower()) & Exp.Eq("entity_type", (int)EntityType.Contact)));
var findedContacts = db.ExecuteList(new SqlQuery("tag_id")
.Select("entity_id")
.Where(Exp.Eq("tag_id", tagID) &
Exp.Eq("entity_type", (int)EntityType.Contact)
));
sqlQuery.Where(Exp.In("id", findedContacts));
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Between("cur_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddHours(-1))));
else if (fromDate != DateTime.MinValue)
sqlQuery.Where(Exp.Ge("cur_date", TenantUtil.DateTimeToUtc(fromDate)));
else if (toDate != DateTime.MinValue)
sqlQuery.Where(Exp.Le("cur_date", TenantUtil.DateTimeToUtc(toDate)));
if (isCompany.HasValue)
sqlQuery.Where("is_company", isCompany);
var sqlResult = db.ExecuteList(sqlQuery);
return sqlResult;
}
}
public File SaveFile(File file, Stream fileStream)
{
if (file == null)
{
throw new ArgumentNullException("file");
}
if (SetupInfo.MaxChunkedUploadSize < file.ContentLength)
{
throw FileSizeComment.FileSizeException;
}
var isNew = false;
lock (syncRoot)
{
using (var DbManager = GetDbManager())
{
using (var tx = DbManager.BeginTransaction())
{
if (file.ID == null)
{
file.ID = DbManager.ExecuteScalar <int>(new SqlQuery("files_file").SelectMax("id")) + 1;
file.Version = 1;
file.VersionGroup = 1;
isNew = true;
}
file.Title = Global.ReplaceInvalidCharsAndTruncate(file.Title);
file.ModifiedBy = SecurityContext.CurrentAccount.ID;
file.ModifiedOn = TenantUtil.DateTimeNow();
if (file.CreateBy == default(Guid))
{
file.CreateBy = SecurityContext.CurrentAccount.ID;
}
if (file.CreateOn == default(DateTime))
{
file.CreateOn = TenantUtil.DateTimeNow();
}
DbManager.ExecuteNonQuery(
Update("files_file")
.Set("current_version", false)
.Where("id", file.ID)
.Where("current_version", true));
DbManager.ExecuteNonQuery(
Insert("files_file")
.InColumnValue("id", file.ID)
.InColumnValue("version", file.Version)
.InColumnValue("version_group", file.VersionGroup)
.InColumnValue("title", file.Title)
.InColumnValue("folder_id", file.FolderID)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(file.CreateOn))
.InColumnValue("create_by", file.CreateBy.ToString())
.InColumnValue("content_length", file.ContentLength)
.InColumnValue("modified_on", TenantUtil.DateTimeToUtc(file.ModifiedOn))
.InColumnValue("modified_by", file.ModifiedBy.ToString())
.InColumnValue("category", (int)file.FilterType)
.InColumnValue("current_version", true)
.InColumnValue("converted_type", file.ConvertedType)
.InColumnValue("comment", file.Comment));
var parentFoldersIds = DbManager.ExecuteList(
new SqlQuery("files_folder_tree")
.Select("parent_id")
.Where(Exp.Eq("folder_id", file.FolderID))
.OrderBy("level", false)
).ConvertAll(row => row[0]);
if (parentFoldersIds.Count > 0)
{
DbManager.ExecuteNonQuery(
Update("files_folder")
.Set("modified_on", TenantUtil.DateTimeToUtc(file.ModifiedOn))
.Set("modified_by", file.ModifiedBy.ToString())
.Where(Exp.In("id", parentFoldersIds))
);
}
file.PureTitle = file.Title;
tx.Commit();
}
RecalculateFilesCount(DbManager, file.FolderID);
}
}
if (fileStream != null)
{
try
{
SaveFileStream(file, fileStream);
}
catch
{
if (isNew)
{
DeleteFile(file.ID);
}
else if (!IsExistOnStorage(file))
{
DeleteVersion(file);
}
throw;
}
}
return(GetFile(file.ID));
}
public void SavePost(Post post)
{
var isInsert = false;
if (post.ID == Guid.Empty)
{
post.ID = Guid.NewGuid();
isInsert = true;
}
using (var tx = Db.BeginTransaction())
{
var tagsForSave = new List <Tag>(post.TagList);
var tagsForDelete = new List <Tag>();
if (!isInsert)
{
var savedTags = SelectTags(post.ID);
tagsForDelete.AddRange(savedTags);
tagsForDelete.RemoveAll(_1 => tagsForSave.Exists(_2 => String.Equals(_1.Content, _2.Content, StringComparison.CurrentCultureIgnoreCase)));
tagsForSave.RemoveAll(_1 => savedTags.Exists(_2 => String.Equals(_1.Content, _2.Content, StringComparison.CurrentCultureIgnoreCase)));
}
if (tagsForDelete.Count > 0)
{
var deleteq = Delete("blogs_tags")
.Where("post_id", post.ID.ToString())
.Where(Exp.In("name", tagsForDelete.ConvertAll(_ => (object)_.Content)));
Db.ExecuteNonQuery(deleteq);
}
if (tagsForSave.Count > 0)
{
foreach (var tag in tagsForSave)
{
var insertq = Insert("blogs_tags")
.InColumns("name", "post_id")
.Values(tag.Content, post.ID.ToString());
Db.ExecuteNonQuery(insertq);
}
}
var queryi = Insert("blogs_posts")
.InColumns("id", "title", "content", "created_by", "created_when", "blog_id", "post_id")
.Values(post.ID.ToString(), post.Title, post.Content, post.UserID.ToString(), TenantUtil.DateTimeToUtc(post.Datetime), post.BlogId, post.AutoIncrementID);
Db.ExecuteNonQuery(queryi);
tx.Commit();
}
}
private List <Deal> GetCrudeDeals(
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetDealSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) &&
responsibleID == Guid.Empty &&
milestoneID <= 0 &&
(tags == null || !tags.Any()) &&
contactID <= 0 &&
stageType == null &&
contactAlsoIsParticipant == null &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
var whereConditional = WhereConditional(new List <int>(),
searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant);
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Having(Exp.Between("close_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate)));
}
else if (withParams && whereConditional == null)
{
return(new List <Deal>());
}
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
if (orderBy != null && Enum.IsDefined(typeof(DealSortedByType), orderBy.SortedBy))
{
switch ((DealSortedByType)orderBy.SortedBy)
{
case DealSortedByType.Title:
sqlQuery.OrderBy("tblDeal.title", orderBy.IsAsc);
break;
case DealSortedByType.BidValue:
sqlQuery.OrderBy("tblDeal.bid_value", orderBy.IsAsc);
break;
case DealSortedByType.Responsible:
sqlQuery.OrderBy("tblDeal.responsible_id", orderBy.IsAsc)
.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.Stage:
sqlQuery.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.DateAndTime:
sqlQuery.OrderBy("close_date", orderBy.IsAsc);
break;
default:
throw new ArgumentException();
}
}
else
{
sqlQuery.OrderBy("tblDM.sort_order", true)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.title", true);
}
return(Db.ExecuteList(sqlQuery).ConvertAll(ToDeal));
}
public void SaveComment(Comment comment)
{
var isInsert = (comment.ID == Guid.Empty);
if (isInsert)
{
comment.ID = Guid.NewGuid();
}
var query = Insert("blogs_comments")
.InColumns("id", "post_id", "parent_id", "content", "created_by", "created_when", "inactive")
.Values(comment.ID, comment.PostId, comment.ParentId, comment.Content, comment.UserID.ToString(), TenantUtil.DateTimeToUtc(comment.Datetime), comment.Inactive ? 1 : 0);
using (var tx = Db.BeginTransaction())
{
Db.ExecuteNonQuery(query);
if (isInsert)
{
var update = Update("blogs_posts")
.Set("LastCommentId", comment.ID.ToString())
.Where("id", comment.PostId.ToString());
Db.ExecuteNonQuery(update);
}
tx.Commit();
}
}
public VoipCall SaveOrUpdateCall(VoipCall call)
{
using (var db = GetDb())
{
var query = Insert("crm_voip_calls")
.InColumnValue("id", call.Id)
.InColumnValue("number_from", call.From)
.InColumnValue("number_to", call.To)
.InColumnValue("contact_id", call.ContactId);
if (!string.IsNullOrEmpty(call.ParentID))
{
query.InColumnValue("parent_call_id", call.ParentID);
}
if (call.Status.HasValue)
{
query.InColumnValue("status", call.Status.Value);
}
if (!call.AnsweredBy.Equals(Guid.Empty))
{
query.InColumnValue("answered_by", call.AnsweredBy);
}
if (call.DialDate == DateTime.MinValue)
{
call.DialDate = DateTime.UtcNow;
}
query.InColumnValue("dial_date", TenantUtil.DateTimeToUtc(call.DialDate));
if (call.DialDuration > 0)
{
query.InColumnValue("dial_duration", call.DialDuration);
}
if (call.Price > decimal.Zero)
{
query.InColumnValue("price", call.Price);
}
if (call.VoipRecord != null)
{
if (!string.IsNullOrEmpty(call.VoipRecord.Id))
{
query.InColumnValue("record_sid", call.VoipRecord.Id);
}
if (!string.IsNullOrEmpty(call.VoipRecord.Uri))
{
query.InColumnValue("record_url", call.VoipRecord.Uri);
}
if (call.VoipRecord.Duration != 0)
{
query.InColumnValue("record_duration", call.VoipRecord.Duration);
}
if (call.VoipRecord.Price != default)
{
query.InColumnValue("record_price", call.VoipRecord.Price);
}
}
db.ExecuteNonQuery(query);
}
return(call);
}
public RelationshipEvent CreateItem(RelationshipEvent item)
{
CRMSecurity.DemandCreateOrUpdate(item);
var htmlBody = String.Empty;
if (item.CreateOn == DateTime.MinValue)
{
item.CreateOn = TenantUtil.DateTimeNow();
}
item.CreateBy = ASC.Core.SecurityContext.CurrentAccount.ID;
item.LastModifedBy = ASC.Core.SecurityContext.CurrentAccount.ID;
if (item.CategoryID == (int)HistoryCategorySystem.MailMessage)
{
var jsonObj = JObject.Parse(item.Content);
var messageId = jsonObj.Value <Int32>("message_id");
var apiServer = new Api.ApiServer();
var msg = apiServer.GetApiResponse(
String.Format("{0}mail/messages/{1}.json?id={1}&loadImages=true&needSanitize=true", SetupInfo.WebApiBaseUrl, messageId), "GET");
if (msg == null)
{
throw new ArgumentException("Mail message cannot be found");
}
var msgResponseWrapper = JObject.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(msg)));
var msgRequestObj = msgResponseWrapper.Value <JObject>("response");
string messageUrl;
htmlBody = msgRequestObj.Value <String>("htmlBody");
using (var fileStream = new MemoryStream(Encoding.UTF8.GetBytes(htmlBody)))
{
var filePath = String.Format("folder_{0}/message_{1}.html", (messageId / 1000 + 1) * 1000, messageId);
Global.GetStore().Save("mail_messages", filePath, fileStream);
messageUrl = String.Format("{0}HttpHandlers/filehandler.ashx?action=mailmessage&message_id={1}", PathProvider.BaseAbsolutePath, messageId).ToLower();
}
var msg_date_created = msgRequestObj.Value <String>("date");
var message_id = msgRequestObj.Value <Int32>("id");
item.Content = JsonConvert.SerializeObject(new
{
@from = msgRequestObj.Value <String>("from"),
to = msgRequestObj.Value <String>("to"),
cc = msgRequestObj.Value <String>("cc"),
bcc = msgRequestObj.Value <String>("bcc"),
subject = msgRequestObj.Value <String>("subject"),
important = msgRequestObj.Value <Boolean>("important"),
chain_id = msgRequestObj.Value <String>("chainId"),
is_sended = msgRequestObj.Value <Int32>("folder") != 1,
date_created = msg_date_created,
introduction = msgRequestObj.Value <String>("introduction"),
message_id = message_id,
message_url = messageUrl
});
item.CreateOn = DateTime.Parse(msg_date_created, CultureInfo.InvariantCulture);
var sqlQueryFindMailsAlready = Query("crm_relationship_event")
.SelectCount()
.Where("contact_id", item.ContactID)
.Where(Exp.Like("content", string.Format("\"message_id\":{0},", message_id)))
.Where("entity_type", (int)item.EntityType)
.Where("entity_id", item.EntityID)
.Where("category_id", item.CategoryID);
if (Db.ExecuteScalar <int>(sqlQueryFindMailsAlready) > 0)
{
throw new Exception("Already exists");
}
}
item.ID = Db.ExecuteScalar <int>(
Insert("crm_relationship_event")
.InColumnValue("id", 0)
.InColumnValue("contact_id", item.ContactID)
.InColumnValue("content", item.Content)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(item.CreateOn))
.InColumnValue("create_by", item.CreateBy)
.InColumnValue("entity_type", (int)item.EntityType)
.InColumnValue("entity_id", item.EntityID)
.InColumnValue("category_id", item.CategoryID)
.InColumnValue("last_modifed_on", DateTime.UtcNow)
.InColumnValue("last_modifed_by", item.LastModifedBy)
.InColumnValue("have_files", false)
.Identity(1, 0, true));
if (item.CreateOn.Kind == DateTimeKind.Utc)
{
item.CreateOn = TenantUtil.DateTimeFromUtc(item.CreateOn);
}
FactoryIndexer <EventsWrapper> .IndexAsync(item);
return(item);
}
private void SaveFeedComment(FeedComment comment)
{
if (comment == null)
{
throw new ArgumentNullException("comment");
}
comment.Id = dbManager.ExecuteScalar <long>(
Insert("events_comment").InColumns("Id", "Feed", "Comment", "Parent", "Date", "Creator", "Inactive")
.Values(comment.Id, comment.FeedId, comment.Comment, comment.ParentId, TenantUtil.DateTimeToUtc(comment.Date), comment.Creator, comment.Inactive)
.Identity(1, 0L, true)
);
}
public List <RelationshipEvent> GetItems(
String searchText,
EntityType entityType,
int entityID,
Guid createBy,
int categoryID,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetRelationshipEventQuery(null);
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
var isCompany = false;
isCompany = Db.ExecuteScalar <bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));
if (isCompany)
{
return(GetItems(searchText, EntityType.Company, entityID, createBy, categoryID, fromDate, toDate, from, count, orderBy));
}
else
{
return(GetItems(searchText, EntityType.Person, entityID, createBy, categoryID, fromDate, toDate, from, count, orderBy));
}
case EntityType.Person:
sqlQuery.Where(Exp.Eq("contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
{
sqlQuery.Where(Exp.Eq("contact_id", entityID));
}
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In("contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq("entity_id", entityID) &
Exp.Eq("entity_type", (int)entityType));
break;
}
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Between("create_on", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
else if (fromDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Ge("create_on", TenantUtil.DateTimeToUtc(fromDate)));
}
else if (toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Le("create_on", TenantUtil.DateTimeToUtc(toDate).AddDays(1).AddMinutes(-1)));
}
if (createBy != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("create_by", createBy));
}
if (categoryID != 0)
{
sqlQuery.Where(Exp.Eq("category_id", categoryID));
}
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
List <int> eventsIds;
if (!FactoryIndexer <EventsWrapper> .TrySelectIds(s => s.MatchAll(searchText), out eventsIds))
{
if (keywords.Length > 0)
{
sqlQuery.Where(BuildLike(new[] { "content" }, keywords));
}
}
else
{
if (eventsIds.Count == 0)
{
return(new List <RelationshipEvent>());
}
sqlQuery.Where(Exp.In("id", eventsIds));
}
}
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
if (orderBy != null && Enum.IsDefined(typeof(RelationshipEventByType), orderBy.SortedBy))
{
switch ((RelationshipEventByType)orderBy.SortedBy)
{
case RelationshipEventByType.Category:
sqlQuery.OrderBy("category_id", orderBy.IsAsc);
break;
case RelationshipEventByType.Content:
sqlQuery.OrderBy("content", orderBy.IsAsc);
break;
case RelationshipEventByType.CreateBy:
sqlQuery.OrderBy("create_by", orderBy.IsAsc);
break;
case RelationshipEventByType.Created:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
}
}
else
{
sqlQuery.OrderBy("create_on", false);
}
return(Db.ExecuteList(sqlQuery)
.ConvertAll(row => ToRelationshipEvent(row)));
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
String alias,
Guid responsibleID,
int categoryID,
bool?isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
var aliasPrefix = !String.IsNullOrEmpty(alias) ? alias + "." : "";
if (responsibleID != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
}
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
var isCompany = true;
using (var db = GetDb())
{
isCompany = db.ExecuteScalar <bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));
}
if (isCompany)
{
return(WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy));
}
else
{
return(WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy));
}
case EntityType.Person:
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
{
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
}
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In(aliasPrefix + "contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq(aliasPrefix + "entity_id", entityID) &
Exp.Eq(aliasPrefix + "entity_type", (int)entityType));
break;
}
}
if (isClosed.HasValue)
{
sqlQuery.Where(aliasPrefix + "is_closed", isClosed);
}
if (categoryID > 0)
{
sqlQuery.Where(Exp.Eq(aliasPrefix + "category_id", categoryID));
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Between(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
else if (fromDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Ge(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate)));
}
else if (toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Le(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
sqlQuery.OrderBy(aliasPrefix + "is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
{
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy(aliasPrefix + "title", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy(aliasPrefix + "deadline", orderBy.IsAsc)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy(aliasPrefix + "category_id", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.ContactManager:
sqlQuery.LeftOuterJoin("core_user u", Exp.EqColumns(aliasPrefix + "responsible_id", "u.id"))
.OrderBy("case when u.lastname is null or u.lastname = '' then 1 else 0 end, u.lastname", orderBy.IsAsc)
.OrderBy("case when u.firstname is null or u.firstname = '' then 1 else 0 end, u.firstname", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Contact:
sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(aliasPrefix + "contact_id", "c_tbl.id"))
.OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
}
}
else
{
sqlQuery
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
}
return(sqlQuery);
}
public virtual int SaveProviderInfo(string providerKey, string customerTitle, AuthData authData, FolderType folderType)
{
ProviderTypes prKey;
try
{
prKey = (ProviderTypes)Enum.Parse(typeof(ProviderTypes), providerKey, true);
}
catch (Exception)
{
throw new ArgumentException("Unrecognize ProviderType");
}
authData = GetEncodedAccesToken(authData, prKey);
if (!CheckProviderInfo(ToProviderInfo(0, providerKey, customerTitle, authData, SecurityContext.CurrentAccount.ID.ToString(), folderType, TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))))
{
throw new UnauthorizedAccessException(string.Format(FilesCommonResource.ErrorMassage_SecurityException_Auth, providerKey));
}
var queryInsert = new SqlInsert(TableTitle, true)
.InColumnValue("id", 0)
.InColumnValue("tenant_id", TenantID)
.InColumnValue("provider", prKey.ToString())
.InColumnValue("customer_title", Global.ReplaceInvalidCharsAndTruncate(customerTitle))
.InColumnValue("user_name", authData.Login ?? "")
.InColumnValue("password", EncryptPassword(authData.Password))
.InColumnValue("folder_type", (int)folderType)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))
.InColumnValue("user_id", SecurityContext.CurrentAccount.ID.ToString())
.InColumnValue("token", EncryptPassword(authData.Token ?? ""))
.InColumnValue("url", authData.Url ?? "")
.Identity(0, 0, true);
using (var db = GetDb())
{
return(Int32.Parse(db.ExecuteScalar <string>(queryInsert)));
}
}
public void ProcessRequest(HttpContext context)
{
if (!ProcessAuthorization(context))
{
AccessDenied(context);
return;
}
var productId = ParseGuid(context.Request[ProductParam]);
var product = WebItemManager.Instance[productId.GetValueOrDefault()];
var products = WebItemManager.Instance.GetItemsAll <IProduct>().ToDictionary(p => p.GetSysName());
var lastModified = GetLastModified(context);
var feeds = FeedAggregateDataProvider.GetFeeds(new FeedApiFilter
{
Product = product != null ? product.GetSysName() : null,
From = lastModified ?? DateTime.UtcNow.AddDays(-14),
To = DateTime.UtcNow,
OnlyNew = true
})
.OrderByDescending(f => f.CreatedDate)
.Take(100)
.Select(f => f.ToFeedMin())
.ToList();
if (lastModified != null && feeds.Count == 0)
{
context.Response.StatusCode = (int)HttpStatusCode.NotModified;
context.Response.StatusDescription = "Not Modified";
}
var feedItems = feeds.Select(f =>
{
var item = new SyndicationItem(
HttpUtility.HtmlDecode((products.ContainsKey(f.Product) ? products[f.Product].Name + ": " : string.Empty) + f.Title),
string.Empty,
new Uri(CommonLinkUtility.GetFullAbsolutePath(f.ItemUrl)),
f.Id,
new DateTimeOffset(TenantUtil.DateTimeToUtc(f.CreatedDate)))
{
PublishDate = f.CreatedDate,
};
if (f.Author != null && f.Author.UserInfo != null)
{
var u = f.Author.UserInfo;
item.Authors.Add(new SyndicationPerson(u.Email, u.DisplayUserName(false), CommonLinkUtility.GetUserProfile(u.ID)));
}
return(item);
});
var lastUpdate = DateTime.UtcNow;
if (feeds.Count > 0)
{
lastUpdate = feeds.Max(x => x.ModifiedDate);
}
var feed = new SyndicationFeed(
CoreContext.TenantManager.GetCurrentTenant().Name,
string.Empty,
new Uri(context.Request.GetUrlRewriter(), VirtualPathUtility.ToAbsolute("~/Feed.aspx")),
TenantProvider.CurrentTenantID.ToString(),
new DateTimeOffset(lastUpdate),
feedItems);
var rssFormatter = new Atom10FeedFormatter(feed);
var settings = new XmlWriterSettings
{
CheckCharacters = false,
ConformanceLevel = ConformanceLevel.Document,
Encoding = Encoding.UTF8,
Indent = true,
};
using (var writer = XmlWriter.Create(context.Response.Output, settings))
{
rssFormatter.WriteTo(writer);
}
context.Response.Charset = Encoding.UTF8.WebName;
context.Response.ContentType = "application/atom+xml";
context.Response.AddHeader("ETag", DateTime.UtcNow.ToString("yyyyMMddHHmmss"));
context.Response.AddHeader("Last-Modified", DateTime.UtcNow.ToString("R"));
}
public virtual int SaveContact(Contact contact)
{
// Delete relative keys
_cache.Insert(_contactCacheKey, String.Empty);
String firstName;
String lastName;
bool isCompany;
String companyName;
String title;
int companyID;
var displayName = String.Empty;
if (contact is Company)
{
firstName = String.Empty;
lastName = String.Empty;
title = String.Empty;
companyName = ((Company)contact).CompanyName.Trim();
isCompany = true;
companyID = 0;
displayName = companyName;
if (String.IsNullOrEmpty(companyName))
{
throw new ArgumentException();
}
}
else
{
var people = (Person)contact;
firstName = people.FirstName.Trim();
lastName = people.LastName.Trim();
title = people.JobTitle;
companyName = String.Empty;
isCompany = false;
companyID = people.CompanyID;
displayName = String.Concat(firstName, _displayNameSeparator, lastName);
if (String.IsNullOrEmpty(firstName) || String.IsNullOrEmpty(lastName))
{
throw new ArgumentException();
}
}
if (!String.IsNullOrEmpty(title))
{
title = title.Trim();
}
if (!String.IsNullOrEmpty(contact.About))
{
contact.About = contact.About.Trim();
}
if (!String.IsNullOrEmpty(contact.Industry))
{
contact.Industry = contact.Industry.Trim();
}
var contactID = DbManager.ExecuteScalar <int>(
Insert("crm_contact")
.InColumnValue("id", 0)
.InColumnValue("first_name", firstName)
.InColumnValue("last_name", lastName)
.InColumnValue("company_name", companyName)
.InColumnValue("title", title)
.InColumnValue("notes", contact.About)
.InColumnValue("is_company", isCompany)
.InColumnValue("industry", contact.Industry)
.InColumnValue("status_id", contact.StatusID)
.InColumnValue("company_id", companyID)
.InColumnValue("create_by", ASC.Core.SecurityContext.CurrentAccount.ID)
.InColumnValue("create_on", TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))
.InColumnValue("last_modifed_on", TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))
.InColumnValue("last_modifed_by", ASC.Core.SecurityContext.CurrentAccount.ID)
.InColumnValue("display_name", displayName)
.Identity(1, 0, true));
contact.ID = contactID;
if (companyID > 0)
{
AddMember(contactID, companyID);
}
return(contactID);
}
