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);
}