public List <int> SetMailboxesProcessed(int timeoutInMinutes)
{
var query = new SqlQuery(MailboxTable.TABLE_NAME)
.Select(MailboxTable.Columns.Id)
.Where(MailboxTable.Columns.IsProcessed, true)
.Where(!Exp.Eq(MailboxTable.Columns.DateChecked, null))
.Where(string.Format(SET_PROCESS_EXPIRES, timeoutInMinutes));
var ids = Db.ExecuteList(query)
.ConvertAll(r => Convert.ToInt32(r[0]));
if (!ids.Any())
{
return(ids);
}
var update = new SqlUpdate(MailboxTable.TABLE_NAME)
.Set(MailboxTable.Columns.IsProcessed, false)
.Where(Exp.In(MailboxTable.Columns.Id, ids.ToArray()));
Db.ExecuteNonQuery(update);
return(ids);
}
private void CheckSecurity(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (checkAccess)
{
query.Where(Exp.Eq("p.private", false));
return;
}
if (isAdmin)
{
return;
}
if (!filter.MyProjects && !filter.MyMilestones)
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = !Exp.Eq("ppp.security", null) & Exp.Eq("ppp.removed", false);
var canReadMilestones = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Eq("t.responsible_id", CurrentUserID);
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadMilestones));
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var q = new SqlQuery("bookmarking_bookmark b")
.Select("b.id", "b.url", "b.name", "b.description", "b.usercreatorid", "b.date")
.LeftOuterJoin("bookmarking_comment c",
Exp.EqColumns("b.id", "c.bookmarkId") &
Exp.Eq("c.inactive", 0) &
Exp.Eq("c.tenant", filter.Tenant)
)
.Select("c.id", "c.content", "c.userId", "c.datetime")
.Where("b.tenant", filter.Tenant)
.Where(Exp.Between("b.date", filter.Time.From, filter.Time.To) |
Exp.Between("c.datetime", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(q).ConvertAll(ToComment);
var groupedBookmarks = comments.GroupBy(c => c.Bookmark.ID);
return(groupedBookmarks
.Select(b => new Tuple <Bookmark, IEnumerable <Comment> >(b.First().Bookmark, b))
.Select(ToFeed));
}
}
public XDocument BuildSalesByManagerReport(DateTime fromDate, DateTime toDate)
{
var sqlQuery = new SqlQuery("crm_deal tbl_deal")
.Select("tbl_deal.responsible_id",
"tbl_deal.bid_currency",
@"sum(CASE tbl_deal.bid_type
WHEN 0 THEN
(tbl_deal.bid_value)
ELSE
(tbl_deal.bid_value * tbl_deal.per_period_value)
END) AS bid_value",
"count(tbl_deal.id) as count")
.LeftOuterJoin("crm_deal_milestone tbl_list", Exp.EqColumns("tbl_deal.deal_milestone_id", "tbl_list.id"))
.Where(Exp.Eq("tbl_list.tenant_id", TenantID) & Exp.Eq("tbl_deal.tenant_id", TenantID) &
Exp.Between("tbl_deal.actual_close_date", fromDate, toDate) &
!Exp.Eq("tbl_deal.bid_value", 0) &
Exp.Eq("tbl_list.status", (int)DealMilestoneStatus.ClosedAndWon))
.GroupBy("tbl_deal.responsible_id", "tbl_deal.bid_currency");
using (var db = GetDb())
{
var sqlResult = db.ExecuteList(sqlQuery);
if (sqlResult.Count == 0)
return new XDocument();
sqlResult.ForEach(row => row[0] = ASC.Core.CoreContext.UserManager.GetUsers(new Guid(row[0].ToString())).DisplayUserName());
var xDocument = BuildSalesReport(sqlResult, Guid.Empty, fromDate, toDate);
xDocument.Root.XPathSelectElement("content/columns/column[@name='title']").Value = CRMDealResource.ResponsibleDeal;
xDocument.Root.XPathSelectElement("metadata/description").Value = "";
return xDocument;
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var q1 = new SqlQuery("files_folder f")
.Select(FolderColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("parent_id"))
.Select("null, null, null")
.Where(
Exp.Eq("f.tenant_id", filter.Tenant) &
Exp.Eq("f.folder_type", 0) &
Exp.Between("f.create_on", filter.Time.From, filter.Time.To)
);
var q2 = new SqlQuery("files_folder f")
.LeftOuterJoin("files_security s",
Exp.EqColumns("s.entry_id", "f.id") &
Exp.Eq("s.tenant_id", filter.Tenant) &
Exp.Eq("s.entry_type", (int)FileEntryType.Folder)
)
.Select(FolderColumns().Select(f => "f." + f).ToArray())
.Select(DocumentsDbHelper.GetRootFolderType("parent_id"))
.Select("s.timestamp, s.owner, s.subject")
.Where(
Exp.Eq("f.tenant_id", filter.Tenant) &
Exp.Eq("f.folder_type", 0) &
Exp.Lt("s.security", 3) &
Exp.Between("s.timestamp", filter.Time.From, filter.Time.To)
);
using (var db = new DbManager(DbId))
{
var folders = db.ExecuteList(q1.UnionAll(q2)).ConvertAll(ToFolder);
return(folders
.Where(f => f.Item1.RootFolderType != FolderType.TRASH && f.Item1.RootFolderType != FolderType.BUNCH)
.Select(f => new Tuple <Feed, object>(ToFeed(f), f)));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query = new SqlQuery("blogs_posts p")
.Select(BlogColumns().Select(p => "p." + p).ToArray())
.LeftOuterJoin("blogs_comments c",
Exp.EqColumns("c.post_id", "p.id") &
Exp.Eq("c.inactive", 0) &
Exp.Eq("c.tenant", filter.Tenant)
)
.Select(BlogCommentColumns().Select(c => "c." + c).ToArray())
.Where("p.tenant", filter.Tenant)
.Where(Exp.Between("p.created_when", filter.Time.From, filter.Time.To) |
Exp.Between("c.created_when", filter.Time.From, filter.Time.To));
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query).ConvertAll(ToComment);
var groupedBlogs = comments.GroupBy(c => c.Post.ID);
return(groupedBlogs
.Select(b => new Tuple <Post, IEnumerable <Comment> >(b.First().Post, b))
.Select(ToFeed));
}
}
private void DeleteBatchCasesExecute(List <Cases> caseses)
{
var casesID = caseses.Select(x => x.ID).ToArray();
using (var tagdao = FilesIntegration.GetTagDao())
{
var tagNames = Db.ExecuteList(Query("crm_relationship_event").Select("id")
.Where(Exp.Eq("have_files", true) & Exp.In("entity_id", casesID) & Exp.Eq("entity_type", (int)EntityType.Case)))
.Select(row => String.Format("RelationshipEvent_{0}", row[0])).ToArray();
var filesIDs = tagdao.GetTags(tagNames, TagType.System).Where(t => t.EntryType == FileEntryType.File).Select(t => t.EntryId).ToArray();
using (var tx = Db.BeginTransaction(true))
{
Db.ExecuteNonQuery(Delete("crm_field_value").Where(Exp.In("entity_id", casesID) & Exp.Eq("entity_type", (int)EntityType.Case)));
Db.ExecuteNonQuery(Delete("crm_relationship_event").Where(Exp.In("entity_id", casesID) & Exp.Eq("entity_type", (int)EntityType.Case)));
Db.ExecuteNonQuery(Delete("crm_task").Where(Exp.In("entity_id", casesID) & Exp.Eq("entity_type", (int)EntityType.Case)));
Db.ExecuteNonQuery(new SqlDelete("crm_entity_tag").Where(Exp.In("entity_id", casesID) & Exp.Eq("entity_type", (int)EntityType.Case)));
Db.ExecuteNonQuery(Delete("crm_case").Where(Exp.In("id", casesID)));
tx.Commit();
}
caseses.ForEach(item => CoreContext.AuthorizationManager.RemoveAllAces(item));
if (0 < tagNames.Length)
{
using (var filedao = FilesIntegration.GetFileDao())
{
foreach (var filesID in filesIDs)
{
filedao.DeleteFile(filesID);
}
}
}
}
}
private void SetFieldValue(EntityType entityType, int entityID, int fieldID, String fieldValue, DbManager db)
{
if (!_supportedEntityType.Contains(entityType))
{
throw new ArgumentException();
}
fieldValue = fieldValue.Trim();
db.ExecuteNonQuery(Delete("crm_field_value").Where(Exp.Eq("entity_id", entityID) & Exp.Eq("entity_type", (int)entityType) & Exp.Eq("field_id", fieldID)));
if (!String.IsNullOrEmpty(fieldValue))
{
db.ExecuteNonQuery(
Insert("crm_field_value")
.InColumnValue("entity_id", entityID)
.InColumnValue("value", fieldValue)
.InColumnValue("field_id", fieldID)
.InColumnValue("entity_type", (int)entityType)
.InColumnValue("last_modifed_on", TenantUtil.DateTimeToUtc(TenantUtil.DateTimeNow()))
.InColumnValue("last_modifed_by", ASC.Core.SecurityContext.CurrentAccount.ID)
);
}
}
public void DeleteMessageAttachments(int tenant, string user, int messageId, List <int> attachmentIds)
{
var ids = attachmentIds.ToArray();
long usedQuota;
using (var db = GetDb())
{
using (var tx = db.BeginTransaction(IsolationLevel.ReadUncommitted))
{
usedQuota = MarkAttachmetsNeedRemove(db, tenant,
Exp.And(Exp.Eq(AttachmentTable.Columns.MailId, messageId),
Exp.In(AttachmentTable.Columns.Id, ids)));
ReCountAttachments(db, messageId);
UpdateMessageChainAttachmentsFlag(db, tenant, user, messageId);
tx.Commit();
}
}
if (usedQuota > 0)
{
QuotaUsedDelete(tenant, usedQuota);
}
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.TagId != 0)
{
if (filter.TagId == -1)
{
query.LeftOuterJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "p.id"));
query.Where("ptag.tag_id", null);
}
else
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "p.id"));
query.Where("ptag.tag_id", filter.TagId);
}
}
if (filter.HasUserId || (filter.ParticipantId.HasValue && !filter.ParticipantId.Equals(Guid.Empty)))
{
var existParticipant = new SqlQuery(ParticipantTable + " ppp").Select("ppp.participant_id").Where(Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.tenant", Tenant));
if (filter.DepartmentId != Guid.Empty)
{
existParticipant.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ppp.participant_id") & Exp.EqColumns("cug.tenant", "ppp.tenant"));
existParticipant.Where("cug.groupid", filter.DepartmentId);
}
if (filter.ParticipantId.HasValue && !filter.ParticipantId.Equals(Guid.Empty))
{
existParticipant.Where(Exp.Eq("ppp.participant_id", filter.ParticipantId.ToString()));
}
query.Where(Exp.Exists(existParticipant));
}
if (filter.UserId != Guid.Empty)
{
query.Where("responsible_id", filter.UserId);
}
if (filter.Follow)
{
query.InnerJoin(FollowingProjectTable + " pfpp", Exp.EqColumns("p.id", "pfpp.project_id"));
query.Where(Exp.Eq("pfpp.participant_id", CurrentUserID));
}
if (filter.ProjectStatuses.Any())
{
query.Where(Exp.In("p.status", filter.ProjectStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
List <int> projIds;
if (FactoryIndexer <ProjectsWrapper> .TrySelectIds(s => s.MatchAll(filter.SearchText), out projIds))
{
query.Where(Exp.In("p.id", projIds));
}
else
{
query.Where(Exp.Like("p.title", filter.SearchText, SqlLike.AnyWhere));
}
}
query.GroupBy("p.id");
if (checkAccess)
{
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
var isInTeam = new SqlQuery(ParticipantTable).Select("security").Where(Exp.EqColumns("p.id", "project_id") & Exp.Eq("removed", false) & Exp.Eq("participant_id", CurrentUserID) & Exp.EqColumns("tenant", "p.tenant_id"));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return(query);
}
public List <Project> GetByParticipiant(Guid participantId, ProjectStatus status)
{
var query = Query(ProjectsTable)
.Select(ProjectColumns)
.InnerJoin(ParticipantTable, Exp.EqColumns("id", "project_id") & Exp.Eq("removed", false) & Exp.EqColumns("tenant_id", "tenant"))
.Where("status", status)
.Where("participant_id", participantId.ToString())
.OrderBy("title", true);
return(Db.ExecuteList(query).ConvertAll(converter));
}
public virtual Exp GetExpression()
{
var filterExp = Exp.Eq(MailTable.Columns.Folder.Prefix(MM_ALIAS), (int)Filter.PrimaryFolder);
if (Filter.Unread.HasValue)
{
filterExp &= Exp.Eq(MailTable.Columns.Unread.Prefix(MM_ALIAS), Filter.Unread);
}
if (Filter.Attachments.HasValue)
{
filterExp &= Exp.Gt(MailTable.Columns.AttachCount.Prefix(MM_ALIAS), 0);
}
if (Filter.PeriodFrom.HasValue && Filter.PeriodTo.HasValue)
{
var fromTs = TimeSpan.FromMilliseconds(Filter.PeriodFrom.Value);
var from = Defines.BaseJsDateTime.Add(fromTs);
var toTs = TimeSpan.FromMilliseconds(Filter.PeriodTo.Value);
var to = Defines.BaseJsDateTime.Add(toTs);
filterExp &= Exp.Between(MailTable.Columns.DateSent.Prefix(MM_ALIAS), from, to);
}
if (Filter.Important.HasValue)
{
filterExp &= Exp.Eq(MailTable.Columns.Importance.Prefix(MM_ALIAS), true);
}
if (Filter.WithCalendar.HasValue)
{
filterExp &= !Exp.Eq(MailTable.Columns.CalendarUid.Prefix(MM_ALIAS), null);
}
if (!string.IsNullOrEmpty(Filter.FromAddress) && !FactoryIndexer <MailWrapper> .Support)
{
if (Filter.PrimaryFolder == FolderType.Sent || Filter.PrimaryFolder == FolderType.Draft)
{
filterExp &= Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), Filter.FromAddress, SqlLike.AnyWhere);
}
else
{
filterExp &= Exp.Like(MailTable.Columns.From.Prefix(MM_ALIAS), Filter.FromAddress,
SqlLike.AnyWhere);
}
}
if (!string.IsNullOrEmpty(Filter.ToAddress) && !FactoryIndexer <MailWrapper> .Support)
{
if (Filter.PrimaryFolder == FolderType.Sent || Filter.PrimaryFolder == FolderType.Draft)
{
filterExp &= Exp.Like(MailTable.Columns.From.Prefix(MM_ALIAS), Filter.ToAddress, SqlLike.AnyWhere);
}
else
{
filterExp &= Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), Filter.ToAddress,
SqlLike.AnyWhere);
}
}
if (Filter.MailboxId.HasValue)
{
filterExp &= Exp.Eq(MailTable.Columns.MailboxId.Prefix(MM_ALIAS), Filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(Filter.SearchText) && !FactoryIndexer <MailWrapper> .Support)
{
filterExp &=
Exp.Or(Exp.Like(MailTable.Columns.From.Prefix(MM_ALIAS), Filter.SearchText, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), Filter.SearchText, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Cc.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Bcc.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Subject.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere),
Exp.Like(MailTable.Columns.Introduction.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere)
)
)
)
)
);
}
if (Ids != null && Ids.Any())
{
filterExp &= Exp.In(MailTable.Columns.Id.Prefix(MM_ALIAS), Ids);
}
var exp = Exp.Eq(MailTable.Columns.Tenant.Prefix(MM_ALIAS), Tenant) &
Exp.Eq(MailTable.Columns.User.Prefix(MM_ALIAS), User) &
Exp.Eq(MailTable.Columns.IsRemoved.Prefix(MM_ALIAS), false);
exp &= filterExp;
return(exp);
}
public static IEnumerable <ResWord> GetListResWords(ResCurrent current, string search)
{
using (var dbManager = new DbManager(Dbid))
{
var exist = new SqlQuery(ResDataTable + " rd3")
.Select("rd3.title")
.Where("rd3.fileid = rd1.fileid")
.Where("rd3.title = concat('del_', rd1.title)")
.Where("rd3.cultureTitle = rd1.cultureTitle");
var sql = new SqlQuery(ResDataTable + " rd1")
.Select("rd1.title", "rd1.fileid", "rd1.textValue", "rd1.description", "rd1.flag", "rd1.link", "rf.resName", "rd2.id", "rd2.flag", "rd2.textValue")
.LeftOuterJoin(ResDataTable + " rd2", Exp.EqColumns("rd1.fileid", "rd2.fileid") & Exp.EqColumns("rd1.title", "rd2.title") & Exp.Eq("rd2.cultureTitle", current.Language.Title))
.InnerJoin(ResFilesTable + " rf", Exp.EqColumns("rf.ID", "rd1.fileID"))
.Where("rf.moduleName", current.Module.Name)
.Where("rf.projectName", current.Project.Name)
.Where("rd1.cultureTitle", "Neutral")
.Where("rd1.flag != 4")
.Where("rd1.resourceType", "text")
.Where(!Exp.Like("rd1.title", @"del\_", SqlLike.StartWith) & !Exp.Exists(exist))
.OrderBy("rd1.id", true);
if (!String.IsNullOrEmpty(search))
{
sql.Where(Exp.Like("rd1.textvalue", search));
}
return(dbManager.ExecuteList(sql).ConvertAll(r =>
{
var word = GetWord(r);
word.ResFile.FileName = Convert.ToString(r[6]);
if (r[7] != null)
{
word.Status = (int)r[8] == 3 ? WordStatusEnum.Changed : WordStatusEnum.Translated;
word.ValueTo = Convert.ToString(r[9]);
}
else
{
word.Status = WordStatusEnum.Untranslated;
}
return word;
}).OrderBy(r => r.ValueFrom));
}
}
public Dictionary <Guid, Typle <int, int> > GetCommentsStatistic(ICollection <Guid> posts, Guid forUser)
{
var q = new SqlQuery("blogs_comments t1")
.LeftOuterJoin("blogs_reviewposts t2", Exp.EqColumns("t1.post_id", "t2.post_id") & Exp.Sql("t1.created_when>t2.timestamp") & Exp.Eq("t2.reviewed_by", forUser.ToString()) & Exp.Eq("t2.Tenant", Tenant))
.Select("t1.post_id")
.Select("count(*) as all_cnt")
.Select("count(t2.post_id) as notseen_cnt ")
.Where("t1.tenant", Tenant)
.Where("t1.inactive", 0)
.Where(Exp.In("t1.post_id", posts.Select(g => g.ToString()).ToArray()))
.GroupBy("t1.post_id");
var rows = Db.ExecuteList(q);
var result = new Dictionary <Guid, Typle <int, int> >(rows.Count);
foreach (var row in rows)
{
var postId = new Guid(row[0].ToString());
var stat = new Typle <int, int>();
stat.Value1 = Convert.ToInt32(row[1]);
stat.Value2 = Convert.ToInt32(row[2]);
result.Add(postId, stat);
}
return(result);
}
public SqlDelete Where(string column, object value)
{
return(Where(Exp.Eq(column, value)));
}
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", invoice.CreateOn == DateTime.MinValue ? DateTime.UtcNow : invoice.CreateOn)
.InColumnValue("create_by", SecurityContext.CurrentAccount.ID)
.InColumnValue("last_modifed_on", invoice.CreateOn == DateTime.MinValue ? DateTime.UtcNow : invoice.CreateOn)
.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);
}
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.CRMInvoicesModule))
{
var ids = FullTextSearch.Search(FullTextSearch.CRMInvoicesModule.Match(searchText));
conditions.Add(Exp.In(tblAliasPrefix + "id", ids));
}
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 List <Task> GetByResponsible(Guid responsibleId, IEnumerable <TaskStatus> statuses)
{
var q = CreateQuery()
.LeftOuterJoin("projects_subtasks pst", Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("t.id", "pst.task_id"))
.Where((Exp.Eq("pst.responsible_id", responsibleId) & !Exp.Eq("Coalesce(pst.status,-1)", TaskStatus.Closed)) | Exp.Eq("ptr.responsible_id", responsibleId))
.OrderBy("t.sort_order", false)
.OrderBy("t.status", true)
.OrderBy("t.priority", true)
.OrderBy("t.create_on", true);
if (statuses != null && statuses.Any())
{
var status = statuses.First();
if (status == TaskStatus.Open)
{
q.Where(!Exp.Eq("t.status", TaskStatus.Closed));
}
else
{
q.Where("t.status", TaskStatus.Closed);
}
}
return(DbManager.ExecuteList(q).ConvertAll(r => ToTask(r)));
}
public IEnumerable <Tag> GetNewTags(Guid subject, Folder parentFolder, bool deepSearch)
{
var folderId = DropboxDaoSelector.ConvertId(parentFolder.ID);
var fakeFolderId = parentFolder.ID.ToString();
using (var db = GetDb())
{
var entryIDs = db.ExecuteList(Query("files_thirdparty_id_mapping")
.Select("hash_id")
.Where(Exp.Like("id", fakeFolderId, SqlLike.StartWith)))
.ConvertAll(x => x[0]);
if (!entryIDs.Any())
{
return(new List <Tag>());
}
var sqlQuery = new SqlQuery("files_tag ft")
.Select("ft.name",
"ft.flag",
"ft.owner",
"ftl.entry_id",
"ftl.entry_type",
"ftl.tag_count",
"ft.id")
.Distinct()
.LeftOuterJoin("files_tag_link ftl",
Exp.EqColumns("ft.tenant_id", "ftl.tenant_id") &
Exp.EqColumns("ft.id", "ftl.tag_id"))
.Where(Exp.Eq("ft.tenant_id", TenantID) &
Exp.Eq("ftl.tenant_id", TenantID) &
Exp.Eq("ft.flag", (int)TagType.New) &
Exp.In("ftl.entry_id", entryIDs));
if (subject != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("ft.owner", subject));
}
var tags = db.ExecuteList(sqlQuery).ConvertAll(r => new Tag
{
TagName = Convert.ToString(r[0]),
TagType = (TagType)r[1],
Owner = new Guid(r[2].ToString()),
EntryId = MappingID(r[3]),
EntryType = (FileEntryType)r[4],
Count = Convert.ToInt32(r[5]),
Id = Convert.ToInt32(r[6])
});
if (deepSearch)
{
return(tags);
}
var folderFileIds = new[] { fakeFolderId }
.Concat(GetChildren(folderId));
return(tags.Where(tag => folderFileIds.Contains(tag.EntryId.ToString())));
}
}
public List <Task> GetByProject(int projectId, TaskStatus?status, Guid participant)
{
var query = CreateQuery()
.LeftOuterJoin("projects_milestones m", Exp.EqColumns("m.id", "t.milestone_id") & Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where("t.project_id", projectId)
.OrderBy("t.sort_order", false)
.OrderBy("m.status", true)
.OrderBy("m.deadLine", true)
.OrderBy("m.id", true)
.OrderBy("t.status", true)
.OrderBy("t.priority", true)
.OrderBy("t.create_on", true);
if (status != null)
{
if (status == TaskStatus.Open)
{
query.Where(!Exp.Eq("t.status", TaskStatus.Closed));
}
else
{
query.Where("t.status", TaskStatus.Closed);
}
}
if (participant != Guid.Empty)
{
var existSubtask = new SqlQuery("projects_subtasks pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("t.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open));
var existResponsible = new SqlQuery("projects_tasks_responsible ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("t.id", "ptr1.task_id"));
existSubtask.Where(Exp.Eq("pst.responsible_id", participant.ToString()));
existResponsible.Where(Exp.Eq("ptr1.responsible_id", participant.ToString()));
query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible));
}
return(DbManager.ExecuteList(query).ConvertAll(r => ToTask(r)));
}
public IEnumerable <Comment> GetCommentsByDate(DateTime @from, DateTime to, Guid forUser)
{
var query = new SqlQuery("blogs_comments t1")
.Select("t1.id", "t1.post_id", "t1.parent_id", "t1.content", "t1.created_by", "t1.created_when", "t1.inactive", "t2.reviewed_by")
.LeftOuterJoin("blogs_reviewposts t2", Exp.EqColumns("t1.post_id", "t2.post_id") & Exp.Sql("t1.created_when>t2.timestamp") & Exp.Eq("t2.reviewed_by", forUser.ToString()) & Exp.Eq("t2.Tenant", Tenant))
.Where(Exp.Between("t1.created_when", from, to))
.OrderBy("t1.created_when", true);
return(Db.ExecuteList(query, (x) =>
{
var comment = RowMappers.ToComment(x);
comment.IsReaded = x.Get <string>("reviewed_by") == forUser.ToString();
return comment;
}));
}
public List <File> GetFiles(object parentId, OrderBy orderBy, FilterType filterType, Guid subjectID, string searchText, bool withSubfolders = false)
{
if (filterType == FilterType.FoldersOnly)
{
return(new List <File>());
}
if (orderBy == null)
{
orderBy = new OrderBy(SortedByType.DateAndTime, false);
}
var q = GetFileQuery(Exp.Eq("current_version", true) & Exp.Eq("folder_id", parentId));
if (withSubfolders)
{
q = GetFileQuery(Exp.Eq("current_version", true) & Exp.Eq("fft.parent_id", parentId))
.InnerJoin("files_folder_tree fft", Exp.EqColumns("fft.folder_id", "f.folder_id"));
}
if (!string.IsNullOrEmpty(searchText))
{
q.Where(Exp.Like("lower(title)", searchText.ToLower().Trim()));
}
switch (orderBy.SortedBy)
{
case SortedByType.Author:
q.OrderBy("create_by", orderBy.IsAsc);
break;
case SortedByType.Size:
q.OrderBy("content_length", orderBy.IsAsc);
break;
case SortedByType.AZ:
q.OrderBy("title", orderBy.IsAsc);
break;
case SortedByType.DateAndTime:
q.OrderBy("create_on", orderBy.IsAsc);
break;
default:
q.OrderBy("title", true);
break;
}
if (!string.IsNullOrEmpty(searchText))
{
q.Where(Exp.Like("lower(title)", searchText.ToLower().Trim()));
}
switch (filterType)
{
case FilterType.DocumentsOnly:
case FilterType.ImagesOnly:
case FilterType.PresentationsOnly:
case FilterType.SpreadsheetsOnly:
case FilterType.ArchiveOnly:
q.Where("category", (int)filterType);
break;
case FilterType.ByUser:
q.Where("create_by", subjectID.ToString());
break;
case FilterType.ByDepartment:
var users = CoreContext.UserManager.GetUsersByGroup(subjectID).Select(u => u.ID.ToString()).ToArray();
q.Where(Exp.In("create_by", users));
break;
case FilterType.ByExtension:
if (!string.IsNullOrEmpty(searchText))
{
q.Where(Exp.Like("lower(title)", searchText.ToLower().Trim(), SqlLike.EndWith));
}
break;
}
using (var dbManager = GetDb())
{
return(dbManager
.ExecuteList(q)
.ConvertAll(ToFile));
}
}
public static void AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin, bool updateIfExist = true)
{
using (var db = new DbManager(Dbid))
{
var resData = db.ExecuteScalar <string>(GetQuery(ResDataTable, cultureTitle, word));
var resReserve = db.ExecuteScalar <string>(GetQuery(ResReserveTable, cultureTitle, word));
//нет ключа
if (string.IsNullOrEmpty(resData))
{
//добавляем в основную таблицу
db.ExecuteNonQuery(Insert(ResDataTable, cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
//добавляем в резервную таблицу
if (isConsole)
{
db.ExecuteNonQuery(Insert(ResReserveTable, cultureTitle, word));
}
}
else
{
if (cultureTitle == "Neutral" && isConsole)
{
updateIfExist = db.ExecuteScalar <int>(new SqlQuery(ResDataTable)
.SelectCount()
.Where("fileID", word.ResFile.FileID)
.Where(!Exp.Eq("cultureTitle", cultureTitle))
.Where("title", word.Title)) == 0;
}
var isChangeResData = resData != word.ValueFrom;
var isChangeResReserve = resReserve != word.ValueFrom;
if (!updateIfExist)
{
return;
}
//при работе с консолью изменилось по сравнению с res_data и res_reserve, либо при работе с сайтом изменилось по сравнению с res_reserve
if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
{
// изменилась нейтральная культура - выставлен флаг у всех ключей из выбранного файла с выбранным title
if (cultureTitle == "Neutral")
{
var update = new SqlUpdate(ResDataTable)
.Set("flag", 3)
.Where("fileID", word.ResFile.FileID)
.Where("title", word.Title);
db.ExecuteNonQuery(update);
}
// изменилась не нейтральная культура
db.ExecuteNonQuery(Insert(ResDataTable, cultureTitle, word)
.InColumnValue("resourceType", resType)
.InColumnValue("timechanges", date)
.InColumnValue("flag", 2)
.InColumnValue("authorLogin", authorLogin));
if (isConsole)
{
db.ExecuteNonQuery(Update(ResReserveTable, cultureTitle, word));
}
}
else if (isChangeResData)
{
db.ExecuteNonQuery(Update(ResReserveTable, cultureTitle, word));
}
}
}
}
public File SaveFile(File file, Stream fileStream)
{
if (file == null)
{
throw new ArgumentNullException("file");
}
if (SetupInfo.MaxChunkedUploadSize < file.ContentLength)
{
throw FileSizeComment.GetFileSizeException(SetupInfo.MaxChunkedUploadSize);
}
var isNew = false;
lock (syncRoot)
{
using (var db = GetDb())
using (var tx = db.BeginTransaction())
{
if (file.ID == null)
{
file.ID = db.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();
}
db.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);
db.ExecuteNonQuery(sql);
tx.Commit();
file.PureTitle = file.Title;
var parentFoldersIds = db.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)
{
db.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(db, 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));
}
public List <Project> GetOpenProjectsWithTasks(Guid participantId)
{
var query = new SqlQuery(ProjectsTable + " p")
.Select(ProjectColumns.Select(c => "p." + c).ToArray())
.InnerJoin(TasksTable + " t", Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id"))
.Where("p.tenant_id", Tenant)
.Where("p.status", ProjectStatus.Open)
.OrderBy("p.title", true)
.GroupBy("p.id");
if (!participantId.Equals(Guid.Empty))
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("ppp.tenant", "p.tenant_id") & Exp.EqColumns("ppp.project_id", "p.id") & Exp.Eq("ppp.removed", false))
.Where("ppp.participant_id", participantId);
}
return(Db.ExecuteList(query).ConvertAll(converter));
}
public File GetFile(object parentId, String title)
{
if (String.IsNullOrEmpty(title))
{
throw new ArgumentNullException(title);
}
using (var dbManager = GetDb())
{
var sqlQueryResult = dbManager
.ExecuteList(GetFileQuery(Exp.Eq("title", title) & Exp.Eq("current_version", true) & Exp.Eq("folder_id", parentId)))
.ConvertAll(ToFile);
return(sqlQueryResult.Count > 0 ? sqlQueryResult[0] : null);
}
}
public List <Project> GetByFilter(TaskFilter filter, bool isAdmin, bool checkAccess)
{
var teamQuery = new SqlQuery(ParticipantTable + " pp")
.SelectCount()
.InnerJoin("core_user cup", Exp.EqColumns("cup.tenant", "pp.tenant") & Exp.EqColumns("cup.id", "pp.participant_id"))
.Where(Exp.EqColumns("pp.tenant", "p.tenant_id"))
.Where(Exp.EqColumns("pp.project_id", "p.id"))
.Where("pp.removed", false)
.Where("cup.status", EmployeeStatus.Active);
var query = new SqlQuery(ProjectsTable + " p")
.Select(ProjectColumns.Select(c => "p." + c).ToArray())
.Select(new SqlQuery(MilestonesTable + " m").SelectCount().Where(Exp.EqColumns("m.tenant_id", "p.tenant_id") & Exp.EqColumns("m.project_id", "p.id")).Where(Exp.Eq("m.status", MilestoneStatus.Open)))
.Select(new SqlQuery(TasksTable + " t").SelectCount().Where(Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id")).Where(!Exp.Eq("t.status", TaskStatus.Closed)))
.Select(new SqlQuery(TasksTable + " t").SelectCount().Where(Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id")))
.Select(teamQuery)
.Select("p.private")
.Where("p.tenant_id", Tenant);
if (filter.Max > 0 && filter.Max < 150000)
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
query.OrderBy("(case p.status when 2 then 1 when 1 then 2 else 0 end)", true);
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["Project"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("p." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("p." + sort, sortColumns[sort]);
}
}
query = CreateQueryFilter(query, filter, isAdmin, checkAccess);
return(Db.ExecuteList(query).ConvertAll(ToProjectFull));
}
public virtual Invoice GetByID(int id, DbManager db)
{
var invoices = db.ExecuteList(GetInvoiceSqlQuery(Exp.Eq("id", id), null)).ConvertAll(ToInvoice);
return(invoices.Count > 0 ? invoices[0] : null);
}
public List <int> GetTaskCount(List <int> projectId, TaskStatus?taskStatus, bool isAdmin)
{
var query = new SqlQuery(TasksTable + " t")
.Select("t.project_id").SelectCount()
.Where(Exp.In("t.project_id", projectId))
.Where("t.tenant_id", Tenant)
.GroupBy("t.project_id");
if (taskStatus.HasValue)
{
if (taskStatus.Value == TaskStatus.Open)
{
query.Where(!Exp.Eq("t.status", TaskStatus.Closed));
}
else
{
query.Where("t.status", TaskStatus.Closed);
}
}
if (!isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id"))
.LeftOuterJoin(TasksResponsibleTable + " ptr", Exp.EqColumns("t.tenant_id", "ptr.tenant_id") & Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.responsible_id", CurrentUserID))
.LeftOuterJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.EqColumns("p.tenant_id", "ppp.tenant") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.participant_id", CurrentUserID))
.Where(Exp.Eq("p.private", false) | !Exp.Eq("ptr.responsible_id", null) | (Exp.Eq("p.private", true) & !Exp.Eq("ppp.security", null) & !Exp.Eq("ppp.security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks)));
}
var result = Db.ExecuteList(query);
return(projectId.ConvertAll(
pid =>
{
var res = result.Find(r => Convert.ToInt32(r[0]) == pid);
return res == null ? 0 : Convert.ToInt32(res[1]);
}
));
}
public List <Invoice> GetEntityInvoices(EntityType entityType, int entityID)
{
var result = new List <Invoice>();
if (entityID <= 0)
{
return(result);
}
using (var db = GetDb())
{
if (entityType == EntityType.Opportunity)
{
return(db.ExecuteList(GetInvoiceSqlQuery(Exp.Eq("entity_id", entityID) & Exp.Eq("entity_type", (int)entityType), null))
.ConvertAll(ToInvoice)
.FindAll(CRMSecurity.CanAccessTo)
.ToList());
}
if (entityType == EntityType.Contact || entityType == EntityType.Person || entityType == EntityType.Company)
{
return(GetContactInvoices(entityID));
}
return(result);
}
}