private static Exp Junction(Exp exp1, Exp exp2, bool and) { if (exp1 == null && exp2 == null) return null; if (exp1 == null) return exp2; if (exp2 == null) return exp1; return new JunctionExp(exp1, exp2, and); }
private List<File> GetFiles(Exp where) { var q = Query("wiki_files") .Select("filename", "uploadfilename", "version", "userid", "date", "filelocation", "filesize") .Where(where) .OrderBy("filename", true); return db .ExecuteList(q) .ConvertAll(r => ToFile(r)) .GroupBy(f => f.FileName) .Select(g => g.OrderByDescending(f => f.Version).First()) .ToList(); }
private SqlQuery GetAccountQuery(Exp where) { SqlQuery sqlQuery = Query("sm_facebookaccounts") .Select( "access_token", "user_id", "associated_id", "user_name" ); if (where != null) sqlQuery.Where(where); return sqlQuery; }
private SqlQuery GetSqlQuery(Exp where) { var sqlQuery = new SqlQuery("crm_currency_info") .Select("resource_key", "abbreviation", "symbol", "culture_name", "is_convertable", "is_basic"); if (where != null) sqlQuery.Where(where); return sqlQuery; }
private SqlQuery GetAccountQuery(Exp where) { SqlQuery sqlQuery = Query("sm_twitteraccounts") .Select( "access_token", "access_token_secret", "screen_name", "user_id", "associated_id", "user_name" ); if (where != null) sqlQuery.Where(where); return sqlQuery; }
private IEnumerable<TenantQuota> GetTenantQuotas(Exp where) { var q = new SqlQuery(tenants_quota) .Select("tenant", "name", "description", "max_file_size", "max_total_size", "active_users", "features", "price", "avangate_id") .Where(where); return ExecList(q) .ConvertAll(r => new TenantQuota(Convert.ToInt32(r[0])) { Name = (string)r[1], Desc = (string)r[2], MaxFileSize = GetInBytes(Convert.ToInt64(r[3])), MaxTotalSize = GetInBytes(Convert.ToInt64(r[4])), ActiveUsers = Convert.ToInt32(r[5]) != 0 ? Convert.ToInt32(r[5]) : int.MaxValue, Features = (string)r[6], Price = Convert.ToDecimal(r[7]), AvangateId = (string)r[8], }); }
private List<MailTag> GetMailTags(int id_tenant, string id_user, Exp exp) { using (var db = GetDb()) { return GetMailTags(db, id_tenant, id_user, exp); } }
private SqlQuery GetListItemSqlQuery(Exp where) { var result = Query("crm_list_item") .Select( "id", "title", "description", "color", "sort_order", "additional_params", "list_type" ); if (where != null) result.Where(where); return result; }
private SqlQuery GetTaskQuery(Exp where) { return GetTaskQuery(where, String.Empty); }
private SqlQuery GetInvoiceSqlQuery(Exp where, String alias) { var sqlQuery = Query("crm_invoice"); if (!String.IsNullOrEmpty(alias)) { sqlQuery = new SqlQuery(String.Concat("crm_invoice ", alias)) .Where(Exp.Eq(alias + ".tenant_id", TenantID)); sqlQuery.Select(GetInvoiceColumnsTable(alias)); } else { sqlQuery.Select(GetInvoiceColumnsTable(String.Empty)); } if (where != null) sqlQuery.Where(where); return sqlQuery; }
private SqlQuery GetRelationshipEventQuery(Exp where) { SqlQuery sqlQuery = Query("crm_relationship_event") .Select("id", "contact_id", "content", "entity_id", "entity_type", "create_on", "create_by", "category_id" ); if (where != null) sqlQuery.Where(where); return sqlQuery; }
public static Exp Or(Exp exp1, Exp exp2) { return Junction(exp1, exp2, false); }
public SqlUpdate Where(Exp where) { this.where = this.where & where; return this; }
private SqlQuery GetContactSqlQuery(Exp where) { return GetContactSqlQuery(where, String.Empty); }
public SqlDelete Where(Exp where) { this.where = this.where & where; return this; }
private SqlQuery GetFieldDescriptionSqlQuery(Exp where) { var sqlQuery = Query("crm_field_description") .Select("id", "-1", "label", "' '", "type", "sort_order", "mask", "entity_type") .OrderBy("sort_order", true); if (where != null) sqlQuery.Where(where); return sqlQuery; }
protected SqlQuery GetFolderQuery(Exp where) { return Query("files_folder f") .Select("f.id") .Select("f.parent_id") .Select("f.title") .Select("f.create_on") .Select("f.create_by") .Select("f.modified_on") .Select("f.modified_by") .Select("f.folder_type") .Select("f.foldersCount") .Select("f.filesCount") .Select(GetRootFolderType("parent_id")) .Select(Exp.Exists(GetSharedQuery(FileEntryType.Folder))) .Where(@where); }
private SqlQuery GetCasesSqlQuery(Exp where) { var sqlQuery = Query("crm_case") .Select("id", "title", "create_by", "create_on", "is_closed"); if (where != null) { sqlQuery.Where(where); } return sqlQuery; }
private SqlQuery GetFeedCommentQuery(Exp where) { return Query("events_comment").Select("Id", "Feed", "Comment", "Parent", "Date", "Creator", "Inactive").Where(where); }
private SqlQuery GetInvoiceLineSqlQuery(Exp where) { var sqlQuery = Query("crm_invoice_line") .Select( "id", "invoice_id", "invoice_item_id", "invoice_tax1_id", "invoice_tax2_id", "sort_order", "description", "quantity", "price", "discount"); if (where != null) { sqlQuery.Where(where); } return sqlQuery; }
private List<MailTag> GetMailTags(IDbManager db, int id_tenant, string id_user, Exp exp) { return db.ExecuteList( new SqlQuery(MAIL_TAG) .Select(TagFields.id, TagFields.name, TagFields.style, TagFields.addresses) .Where(GetUserWhere(id_user, id_tenant)) .Where(exp)) .ConvertAll(r => new MailTag(Convert.ToInt32(r[0]), (string)r[1], r[3] == null ? null : r[3].ToString().Split(';'), ConvertToString(r[2]), 0)); }
private SqlQuery GetDealSqlQuery(Exp where) { SqlQuery sqlQuery = Query("crm_deal tblDeal") .Select( "tblDeal.id", "tblDeal.title", "tblDeal.description", "tblDeal.responsible_id", "tblDeal.contact_id", "tblDeal.bid_currency", "tblDeal.bid_value", "tblDeal.bid_type", "tblDeal.deal_milestone_id", "tblDeal.expected_close_date", "tblDeal.per_period_value", "tblDeal.deal_milestone_probability", "tblDeal.create_on", "tblDeal.create_by", "tblDeal.actual_close_date" ) .Select(@"case tblDM.status when 0 then tblDeal.expected_close_date else tblDeal.actual_close_date end as close_date") .LeftOuterJoin("crm_deal_milestone tblDM", Exp.EqColumns("tblDeal.deal_milestone_id", "tblDM.id")); if (where != null) sqlQuery.Where(where); return sqlQuery; }
public static Exp And(Exp exp1, Exp exp2) { return Junction(exp1, exp2, true); }
private SqlQuery GetInvoiceItemSqlQuery(Exp where) { var sqlQuery = Query("crm_invoice_item") .Select( "id", "title", "description", "stock_keeping_unit", "price", "quantity", "stock_quantity", "track_inventory", "invoice_tax1_id", "invoice_tax2_id", "currency", "create_on", "create_by", "last_modifed_on", "last_modifed_by"); if (where != null) { sqlQuery.Where(where); } return sqlQuery; }
private SqlQuery GetSqlQuery(Exp where) { var sqlQuery = Query("crm_currency_rate") .Select("id", "from_currency", "to_currency", "rate"); if (where != null) sqlQuery.Where(where); return sqlQuery; }
public static Exp Parse(Expression expression, Exp exp) { Exp expOut = null; var binary = expression as BinaryExpression; var unary = expression as UnaryExpression; var member = expression as MemberExpression; if (binary != null) { if (IsInBinaryType(binary.NodeType)) { var memberExp = binary.Left as MemberExpression; var constExp = binary.Right as ConstantExpression; if (constExp == null && binary.Right is MemberExpression) { var lambda = Expression.Lambda(binary.Right); var fn = lambda.Compile(); constExp = Expression.Constant(fn.DynamicInvoke(null), binary.Right.Type); } if (memberExp != null && constExp != null) { object[] dbprops = memberExp.Member.GetCustomAttributes(typeof (DbColumnAttribute), true); if (dbprops.Length > 0) { string column = ((DbColumnAttribute) dbprops[0]).Column; switch (binary.NodeType) { case ExpressionType.Equal: expOut = Exp.Eq(column, constExp.Value); break; case ExpressionType.GreaterThan: expOut = Exp.Gt(column, constExp.Value); break; case ExpressionType.GreaterThanOrEqual: expOut = Exp.Ge(column, constExp.Value); break; case ExpressionType.LessThan: expOut = Exp.Lt(column, constExp.Value); break; case ExpressionType.LessThanOrEqual: expOut = Exp.Le(column, constExp.Value); break; case ExpressionType.NotEqual: expOut = !Exp.Eq(column, constExp.Value); break; } } } } else { switch (binary.NodeType) { case ExpressionType.Or: expOut = (Parse(binary.Left, exp) | Parse(binary.Right, exp)); break; case ExpressionType.OrElse: expOut = exp | (Parse(binary.Left, exp) | Parse(binary.Right, exp)); break; case ExpressionType.And: expOut = (Parse(binary.Left, exp) & Parse(binary.Right, exp)); break; case ExpressionType.AndAlso: expOut = exp & (Parse(binary.Left, exp) & Parse(binary.Right, exp)); break; } } } else if (unary != null) { switch (unary.NodeType) { case ExpressionType.Not: expOut = !Parse(unary.Operand, exp); break; } } else if (member != null) { object[] dbprops = member.Member.GetCustomAttributes(typeof (DbColumnAttribute), true); if (dbprops.Length > 0) { string column = ((DbColumnAttribute) dbprops[0]).Column; if (member.Type == typeof (bool)) { expOut = exp | Exp.Eq(column, true); } } if (expOut == null) { expOut = Exp.True; } } return expOut; }
internal List<Project> GetProjects(Exp where) { using (var db = new DbManager(DatabaseId)) { return db.ExecuteList(Query(ProjectsTable + " p").Select(ProjectColumns).Where(where)).ConvertAll(ToProject); } }
private SqlQuery GetSqlQuery(Exp where) { var sqlQuery = Query("crm_contact_info") .Select("id", "category", "data", "type", "is_primary", "contact_id"); if (where != null) sqlQuery.Where(where); return sqlQuery; }
internal List<Milestone> GetMilestones(Exp where) { using (var db = new DbManager(DatabaseId)) { return db.ExecuteList(CreateQuery().Where(where)).ConvertAll(ToMilestone); } }
private SqlQuery PageQuery(Exp where) { return Query("wiki_pages p") .InnerJoin("wiki_pages_history h", Exp.EqColumns("p.tenant", "h.tenant") & Exp.EqColumns("p.pagename", "h.pagename") & Exp.Eq("h.version", 1)) .Select("p.pagename", "p.version", "p.modified_by", "p.modified_on", "h.create_by") .Where(where); }