public static IEnumerable<ResCulture> GetCultures()
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlQuery("res_cultures");
sql.Select(new[] {"title", "value", "available"})
.OrderBy("title", true);
return dbManager.ExecuteList(sql).ConvertAll(r => GetCultureFromDB(r));
}
}
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;
}
public static void GetValueByKey(ResWord word, string to)
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlQuery("res_data");
sql.Select(new[] {"textvalue"})
.Where("fileID", word.ResFile.FileID)
.Where("cultureTitle", to)
.Where("title", word.Title);
word.ValueTo = dbManager.ExecuteScalar<string>(sql) ?? "";
}
}
public static void AddLink(string resource, string fileName, string page)
{
using (var dbManager = new DbManager("tmresource"))
{
var query = new SqlQuery("res_data");
query.Select("res_data.id")
.InnerJoin("res_files", Exp.EqColumns("res_files.id", "res_data.fileid"))
.Where("res_data.title", resource).Where("res_files.resName", fileName).Where("res_data.cultureTitle", "Neutral");
var key = dbManager.ExecuteScalar<int>(query);
var update = new SqlUpdate("res_data");
update.Set("link", page).Where("id", key);
dbManager.ExecuteNonQuery(update);
}
}
public static IEnumerable<ResProject> GetResProjects()
{
using (var dbManager = new DbManager("tmresource"))
{
var sql = new SqlQuery("res_files");
sql.Select("projectName").Distinct();
var projects = dbManager.ExecuteList(sql).Select(r => new ResProject {Name = (string)r[0]}).ToList();
foreach (var resProject in projects)
{
sql = new SqlQuery("res_files")
.Select("moduleName", "islock")
.Where("projectName", resProject.Name)
.Distinct();
resProject.Modules = dbManager.ExecuteList(sql).Select(r => new ResModule {Name = (string)r[0], IsLock = (bool)r[1]}).ToList();
}
return projects;
}
}
protected int[] GetRelativeToEntity(int? contactID, EntityType entityType, int? entityID, DbManager db)
{
var sqlQuery = new SqlQuery("crm_entity_contact");
if (contactID.HasValue && !entityID.HasValue)
sqlQuery.Select("entity_id").Where(Exp.Eq("entity_type", entityType) & Exp.Eq("contact_id", contactID.Value));
else if (!contactID.HasValue && entityID.HasValue)
sqlQuery.Select("contact_id").Where(Exp.Eq("entity_type", entityType) & Exp.Eq("entity_id", entityID.Value));
return db.ExecuteList(sqlQuery).Select(row => Convert.ToInt32(row[0])).ToArray();
}
protected Dictionary<int, int[]> GetRelativeToEntity(int[] contactID, EntityType entityType, int[] entityID)
{
var sqlQuery = new SqlQuery("crm_entity_contact");
if (contactID != null && contactID.Length > 0 && (entityID == null || entityID.Length == 0))
sqlQuery.Select("entity_id", "contact_id").Where(Exp.Eq("entity_type", entityType) & Exp.In("contact_id", contactID));
else if (entityID != null && entityID.Length > 0 && (contactID == null || contactID.Length == 0))
sqlQuery.Select("entity_id", "contact_id").Where(Exp.Eq("entity_type", entityType) & Exp.In("entity_id", entityID));
using (var db = GetDb())
{
var sqlResult = db.ExecuteList(sqlQuery);
return sqlResult.GroupBy(item => item[0])
.ToDictionary(item => Convert.ToInt32(item.Key),
item => item.Select(x => Convert.ToInt32(x[1])).ToArray());
}
}
private SqlQuery GetInvoiceSqlQuery(Exp where, String alias)
{
var q = Query("crm_invoice");
if (!string.IsNullOrEmpty(alias))
{
q = new SqlQuery("crm_invoice " + alias)
.Where(alias + ".tenant_id", TenantID);
q.Select(GetInvoiceColumnsTable(alias));
}
else
{
q.Select(GetInvoiceColumnsTable(String.Empty));
}
if (where != null)
q.Where(where);
return q;
}
