private static EntityRecordList SampleGetAllErpUsers()
{
EntityRecordList result = null;
//you need to create manually database context
using (var dbCtx = DbContext.CreateContext(ErpSettings.ConnectionString))
{
//create connection
using (var connection = dbCtx.CreateConnection())
{
//create security context - in this sample we use OpenSystemScope method,
//which used system user with all privileges and rights to erp data
using (var scope = SecurityContext.OpenSystemScope())
{
try
{
//use transaction if needed
connection.BeginTransaction();
result = new EqlCommand("SELECT * FROM user").Execute();
connection.CommitTransaction();
}
catch
{
connection.RollbackTransaction();
throw;
}
}
}
}
return(result);
}
public EntityRecordList GetLogs(int page, int pageSize, string querySource = null, string queryMessage = null)
{
int offset = (page - 1) * pageSize;
int limit = pageSize;
using (var connection = DbContext.Current.CreateConnection())
{
var sql = $@"SELECT *, COUNT(*) OVER() AS ___total_count___ FROM system_log WHERE (source ILIKE @querySource OR @querySource is null) AND (message ILIKE @queryMessage OR @queryMessage is null ) ORDER BY created_on DESC LIMIT {limit} OFFSET {offset} ";
var cmd = connection.CreateCommand(sql);
cmd.Parameters.Add(new NpgsqlParameter("@querySource", string.IsNullOrWhiteSpace(querySource) ? (object)DBNull.Value : $"%{querySource}%"));
cmd.Parameters.Add(new NpgsqlParameter("@queryMessage", string.IsNullOrWhiteSpace(queryMessage) ? (object)DBNull.Value : $"%{queryMessage}%"));
DataTable dt = new DataTable();
new NpgsqlDataAdapter(cmd).Fill(dt);
EntityRecordList result = new EntityRecordList();
foreach (DataRow dr in dt.Rows)
{
result.TotalCount = (int)((long)dr["___total_count___"]);
EntityRecord record = new EntityRecord();
record["id"] = dr["id"];
record["created_on"] = dr["created_on"];
record["type"] = dr["type"];
record["notification_status"] = dr["notification_status"];
record["source"] = dr["source"];
record["message"] = dr["message"];
if (dr["details"] == DBNull.Value)
{
record["details"] = null;
}
else
{
record["details"] = dr["details"];
}
result.Add(record);
}
return(result);
}
}
/// <summary>
/// Executes the command to database
/// </summary>
/// <returns></returns>
public EntityRecordList Execute()
{
EqlBuilder eqlBuilder = new EqlBuilder(Text);
var eqlBuildResult = eqlBuilder.Build(Parameters);
if (eqlBuildResult.Errors.Count > 0)
{
throw new EqlException(eqlBuildResult.Errors);
}
if (DbContext.Current == null)
{
throw new EqlException("DbContext need to be created.");
}
EntityRecordList result = new EntityRecordList();
DataTable dt = new DataTable();
var npgsParameters = eqlBuildResult.Parameters.Select(x => x.ToNpgsqlParameter()).ToList();
NpgsqlCommand command = null;
if (Connection != null)
{
command = Connection.CreateCommand(eqlBuildResult.Sql, parameters: npgsParameters);
}
else if (NpgConnection != null)
{
if (NpgTransaction != null)
{
command = new NpgsqlCommand(eqlBuildResult.Sql, NpgConnection, NpgTransaction);
}
else
{
command = new NpgsqlCommand(eqlBuildResult.Sql, NpgConnection);
}
command.Parameters.AddRange(npgsParameters.ToArray());
}
else
{
if (DbContext.Current == null)
{
throw new EqlException("DbContext needs to be initialized before using EqlCommand without supplying connection.");
}
using (var connection = DbContext.Current.CreateConnection())
{
command = connection.CreateCommand(eqlBuildResult.Sql, parameters: npgsParameters);
command.CommandTimeout = 600;
new NpgsqlDataAdapter(command).Fill(dt);
foreach (DataRow dr in dt.Rows)
{
var jObj = JObject.Parse((string)dr[0]);
if (result.TotalCount == 0 && jObj.ContainsKey("___total_count___"))
{
result.TotalCount = int.Parse(((JValue)jObj["___total_count___"]).ToString());
}
result.Add(ConvertJObjectToEntityRecord(jObj, eqlBuildResult.Meta));
}
return(result);
}
}
command.CommandTimeout = 600;
new NpgsqlDataAdapter(command).Fill(dt);
foreach (DataRow dr in dt.Rows)
{
var jObj = JObject.Parse((string)dr[0]);
if (result.TotalCount == 0 && jObj.ContainsKey("___total_count___"))
{
result.TotalCount = int.Parse(((JValue)jObj["___total_count___"]).ToString());
}
result.Add(ConvertJObjectToEntityRecord(jObj, eqlBuildResult.Meta));
}
return(result);
}
public void PreDeleteApiHookLogic(string entityName, EntityRecord record, List <ErrorModel> errors)
{
if (!record.Properties.ContainsKey("id"))
{
throw new Exception("Hook exception: timelog id field not found in record");
}
Guid recordId = (Guid)record["id"];
var isProjectTimeLog = false;
var timelogRecord = new EntityRecord();
var relatedTaskRecords = new EntityRecordList();
//Get timelog
var eqlCommand = "SELECT * from timelog WHERE id = @recordId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("recordId", recordId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (!eqlResult.Any())
{
throw new Exception("Hook exception: timelog with this id was not found");
}
timelogRecord = eqlResult[0];
if (timelogRecord["l_scope"] != null && ((string)timelogRecord["l_scope"]).Contains("projects"))
{
isProjectTimeLog = true;
}
if (isProjectTimeLog)
{
if (timelogRecord["l_related_records"] != null && (string)timelogRecord["l_related_records"] != "")
{
try
{
var relatedRecordGuid = JsonConvert.DeserializeObject <List <Guid> >((string)timelogRecord["l_related_records"]);
var taskEqlCommand = "SELECT *,$project_nn_task.id from task WHERE ";
var filterStringList = new List <string>();
var taskEqlParams = new List <EqlParameter>();
var index = 1;
foreach (var taskGuid in relatedRecordGuid)
{
var paramName = "taskId" + index;
filterStringList.Add($" id = @{paramName} ");
taskEqlParams.Add(new EqlParameter(paramName, taskGuid));
index++;
}
taskEqlCommand += String.Join(" OR ", filterStringList);
relatedTaskRecords = new EqlCommand(taskEqlCommand, taskEqlParams).Execute();
}
catch
{
//Do nothing
}
}
var taskRecord = relatedTaskRecords[0]; //Currently should be related only to 1 task in projects
var patchRecord = new EntityRecord();
patchRecord["id"] = (Guid)taskRecord["id"];
if ((bool)timelogRecord["is_billable"])
{
var result = Math.Round((decimal)taskRecord["x_billable_minutes"] - (decimal)timelogRecord["minutes"]);
if (result > 0)
{
patchRecord["x_billable_minutes"] = result;
}
else
{
patchRecord["x_billable_minutes"] = 0;
}
}
else
{
var result = Math.Round((decimal)taskRecord["x_nonbillable_minutes"] - (decimal)timelogRecord["minutes"]);
if (result > 0)
{
patchRecord["x_nonbillable_minutes"] = result;
}
else
{
patchRecord["x_nonbillable_minutes"] = 0;
}
}
var updateResponse = new RecordManager(executeHooks: false).UpdateRecord("task", patchRecord);
if (!updateResponse.Success)
{
throw new Exception(updateResponse.Message);
}
//Delete feeds that related to this timelog
var feedEqlCommand = "SELECT id FROM feed_item WHERE l_related_records CONTAINS @recordId";
var feedEqlParams = new List <EqlParameter>()
{
new EqlParameter("recordId", recordId)
};
var feedEqlResult = new EqlCommand(feedEqlCommand, feedEqlParams).Execute();
foreach (var feedId in feedEqlResult)
{
var deleteResponse = new RecordManager(executeHooks: false).DeleteRecord("feed_item", (Guid)feedId["id"]);
if (!deleteResponse.Success)
{
throw new Exception(deleteResponse.Message);
}
}
}
}
public void PreCreateApiHookLogic(string entityName, EntityRecord record, List <ErrorModel> errors)
{
if (!record.Properties.ContainsKey("id"))
{
throw new Exception("Hook exception: timelog id field not found in record");
}
Guid recordId = (Guid)record["id"];
var isProjectTimeLog = false;
var relatedTaskRecords = new EntityRecordList();
//Get timelog
if (record["l_scope"] != null && ((string)record["l_scope"]).Contains("projects"))
{
isProjectTimeLog = true;
}
if (isProjectTimeLog)
{
if (record["l_related_records"] != null && (string)record["l_related_records"] != "")
{
try
{
var relatedRecordGuid = JsonConvert.DeserializeObject <List <Guid> >((string)record["l_related_records"]);
var taskEqlCommand = "SELECT *,$project_nn_task.id, $user_nn_task_watchers.id FROM task WHERE ";
var filterStringList = new List <string>();
var taskEqlParams = new List <EqlParameter>();
var index = 1;
foreach (var taskGuid in relatedRecordGuid)
{
var paramName = "taskId" + index;
filterStringList.Add($" id = @{paramName} ");
taskEqlParams.Add(new EqlParameter(paramName, taskGuid));
index++;
}
taskEqlCommand += String.Join(" OR ", filterStringList);
relatedTaskRecords = new EqlCommand(taskEqlCommand, taskEqlParams).Execute();
}
catch (Exception ex)
{
throw ex;
}
}
if (!relatedTaskRecords.Any())
{
throw new Exception("Hook exception: This timelog does not have an existing taskId");
}
var taskRecord = relatedTaskRecords[0]; //Currently should be related only to 1 task in projects
var patchRecord = new EntityRecord();
patchRecord["id"] = (Guid)taskRecord["id"];
var loggedTypeString = "billable";
if ((bool)record["is_billable"])
{
patchRecord["x_billable_minutes"] = (decimal)taskRecord["x_billable_minutes"] + (int)record["minutes"];
}
else
{
patchRecord["x_nonbillable_minutes"] = (decimal)taskRecord["x_nonbillable_minutes"] + (int)record["minutes"];
loggedTypeString = "non-billable";
}
//Null timelog_started_on
patchRecord["timelog_started_on"] = null;
var updateResponse = new RecordManager(executeHooks: false).UpdateRecord("task", patchRecord);
if (!updateResponse.Success)
{
throw new Exception(updateResponse.Message);
}
//Add feed record - include all taskIds and related Project ids in the field
Guid?projectId = null;
if (((List <EntityRecord>)taskRecord["$project_nn_task"]).Any())
{
var projectRecord = ((List <EntityRecord>)taskRecord["$project_nn_task"]).First();
if (projectRecord != null)
{
projectId = (Guid)projectRecord["id"];
}
}
var taskWatchersList = new List <string>();
if (((List <EntityRecord>)taskRecord["$user_nn_task_watchers"]).Any())
{
taskWatchersList = ((List <EntityRecord>)taskRecord["$user_nn_task_watchers"]).Select(x => ((Guid)x["id"]).ToString()).ToList();
}
//Add activity log
var subject = $"logged {((int)record["minutes"]).ToString("N0")} {loggedTypeString} minutes on <a href=\"/projects/tasks/tasks/r/{taskRecord["id"]}/details\">[{taskRecord["key"]}] {taskRecord["subject"]}</a>";
var relatedRecords = new List <string>()
{
taskRecord["id"].ToString(), record["id"].ToString()
};
if (projectId != null)
{
relatedRecords.Add(projectId.ToString());
}
relatedRecords.AddRange(taskWatchersList);
var scope = new List <string>()
{
"projects"
};
var logSnippet = new Web.Services.RenderService().GetSnippetFromHtml((string)record["body"]);
new FeedItemService().Create(id: Guid.NewGuid(), createdBy: SecurityContext.CurrentUser.Id, subject: subject,
body: logSnippet, relatedRecords: relatedRecords, scope: scope, type: "timelog");
}
}
public void PreCreateApiHookLogic(string entityName, EntityRecord record, List <ErrorModel> errors)
{
var isProjectComment = false;
var relatedTaskRecords = new EntityRecordList();
//Get timelog
if (record.Properties.ContainsKey("l_scope") && record["l_scope"] != null && ((string)record["l_scope"]).Contains("projects"))
{
isProjectComment = true;
}
if (isProjectComment)
{
//Get related tasks from related records field
if (record.Properties.ContainsKey("l_related_records") && record["l_related_records"] != null && (string)record["l_related_records"] != "")
{
try
{
var relatedRecordGuid = JsonConvert.DeserializeObject <List <Guid> >((string)record["l_related_records"]);
var taskEqlCommand = "SELECT *,$project_nn_task.id, $user_nn_task_watchers.id FROM task WHERE ";
var filterStringList = new List <string>();
var taskEqlParams = new List <EqlParameter>();
var index = 1;
foreach (var taskGuid in relatedRecordGuid)
{
var paramName = "taskId" + index;
filterStringList.Add($" id = @{paramName} ");
taskEqlParams.Add(new EqlParameter(paramName, taskGuid));
index++;
}
taskEqlCommand += String.Join(" OR ", filterStringList);
relatedTaskRecords = new EqlCommand(taskEqlCommand, taskEqlParams).Execute();
}
catch (Exception ex)
{
throw ex;
}
}
if (!relatedTaskRecords.Any())
{
throw new Exception("Hook exception: This comment is a project comment but does not have an existing taskId related");
}
var taskRecord = relatedTaskRecords[0]; //Currently should be related only to 1 task in projects
//Get Project Id
Guid?projectId = null;
if (((List <EntityRecord>)taskRecord["$project_nn_task"]).Any())
{
var projectRecord = ((List <EntityRecord>)taskRecord["$project_nn_task"]).First();
if (projectRecord != null)
{
projectId = (Guid)projectRecord["id"];
}
}
var taskWatchersList = new List <string>();
if (((List <EntityRecord>)taskRecord["$user_nn_task_watchers"]).Any())
{
taskWatchersList = ((List <EntityRecord>)taskRecord["$user_nn_task_watchers"]).Select(x => ((Guid)x["id"]).ToString()).ToList();
}
//Add activity log
var subject = $"commented on <a href=\"/projects/tasks/tasks/r/{taskRecord["id"]}/details\">[{taskRecord["key"]}] {taskRecord["subject"]}</a>";
var relatedRecords = new List <string>()
{
taskRecord["id"].ToString(), record["id"].ToString()
};
if (projectId != null)
{
relatedRecords.Add(projectId.ToString());
}
relatedRecords.AddRange(taskWatchersList);
var body = new Web.Services.RenderService().GetSnippetFromHtml((string)record["body"]);
var scope = new List <string>()
{
"projects"
};
new FeedItemService().Create(id: Guid.NewGuid(), createdBy: SecurityContext.CurrentUser.Id, subject: subject,
body: body, relatedRecords: relatedRecords, scope: scope, type: "comment");
}
}
public List <EntityRecord> GetTimelogData(int year, int month, Guid?accountId)
{
ValidationException valEx = new ValidationException();
if (month > 12 || month <= 0)
{
valEx.AddError("month", "Invalid month.");
}
if (year <= 0)
{
valEx.AddError("year", "Invalid year.");
}
List <EqlParameter> eqlParams;
if (accountId.HasValue)
{
eqlParams = new List <EqlParameter>()
{
new EqlParameter("id", accountId.Value)
};
var eqlResult = new EqlCommand("SELECT * FROM account WHERE id = @id ", eqlParams).Execute();
if (!eqlResult.Any())
{
valEx.AddError("accountId", $"Account with ID:{accountId} not found.");
}
}
valEx.CheckAndThrow();
/// load timelog records from database
DateTime fromDate = new DateTime(year, month, 1);
DateTime toDate = new DateTime(year, month, DateTime.DaysInMonth(year, month));
eqlParams = new List <EqlParameter>()
{
new EqlParameter("from_date", fromDate),
new EqlParameter("to_date", toDate),
new EqlParameter("scope", "projects"),
};
string eql = @"SELECT id,is_billable,l_related_records,minutes FROM timelog
WHERE logged_on >= @from_date AND
logged_on <= @to_date AND
l_scope CONTAINS @scope" ;
var timelogRecords = new EqlCommand(eql, eqlParams).Execute();
HashSet <Guid> setOfTasksWithTimelog = new HashSet <Guid>();
foreach (var timelog in timelogRecords)
{
List <Guid> ids = JsonConvert.DeserializeObject <List <Guid> >((string)timelog["l_related_records"]);
Guid taskId = ids[0];
timelog["task_id"] = taskId;
if (!setOfTasksWithTimelog.Contains(taskId))
{
setOfTasksWithTimelog.Add(taskId);
}
}
//load all tasks
eqlParams = new List <EqlParameter>();
eql = @"SELECT id,subject, $project_nn_task.id, $project_nn_task.name,$project_nn_task.account_id, $task_type_1n_task.label FROM task";
var tasks = new EqlCommand(eql, eqlParams).Execute();
//process tasks - split records for projects - filter by account - filter by timelog
EntityRecordList processedTasks = new EntityRecordList();
foreach (var task in tasks)
{
//skip task that has no timelog record
if (!setOfTasksWithTimelog.Contains((Guid)task["id"]))
{
continue;
}
List <EntityRecord> taskProjects = (List <EntityRecord>)task["$project_nn_task"];
//skip tasks with no project
if (taskProjects.Count == 0)
{
continue;
}
//split tasks to projects if more than one project is related to task
foreach (var project in taskProjects)
{
if (accountId != null)
{
if ((Guid)project["account_id"] == accountId)
{
task["project"] = project;
processedTasks.Add(task);
}
}
else
{
task["project"] = project;
processedTasks.Add(task);
}
}
}
tasks = processedTasks;
tasks.TotalCount = processedTasks.Count;
List <EntityRecord> result = new List <EntityRecord>();
foreach (var task in tasks)
{
EntityRecord rec = new EntityRecord();
rec["task_id"] = task["id"];
rec["project_id"] = ((EntityRecord)task["project"])["id"];
rec["task_subject"] = task["subject"];
rec["project_name"] = ((EntityRecord)task["project"])["name"];
rec["task_type"] = ((List <EntityRecord>)task["$task_type_1n_task"])[0]["label"];
rec["billable_minutes"] = (decimal)0;
rec["non_billable_minutes"] = (decimal)0;
//during development
//rec["task"] = task;
//rec["project"] = (EntityRecord)task["project"];
foreach (var timelog in timelogRecords)
{
if ((Guid)timelog["task_id"] != (Guid)task["id"])
{
continue;
}
if ((bool)timelog["is_billable"])
{
rec["billable_minutes"] = (decimal)rec["billable_minutes"] + (decimal)timelog["minutes"];
}
else
{
rec["non_billable_minutes"] = (decimal)rec["non_billable_minutes"] + (decimal)timelog["minutes"];
}
}
result.Add(rec);
}
return(result);
}
public void OnPreCreateRecord(string entityName, EntityRecord record, List <ErrorModel> errors)
{
var isProjectTimeLog = false;
var relatedTaskRecords = new EntityRecordList();
//Get timelog
if (record["l_scope"] != null && ((string)record["l_scope"]).Contains("projects"))
{
isProjectTimeLog = true;
}
if (isProjectTimeLog)
{
//Get related tasks from related records field
if (record["l_related_records"] != null && (string)record["l_related_records"] != "")
{
try
{
var relatedRecordGuid = JsonConvert.DeserializeObject <List <Guid> >((string)record["l_related_records"]);
var taskEqlCommand = "SELECT *,$project_nn_task.id from task WHERE ";
var filterStringList = new List <string>();
var taskEqlParams = new List <EqlParameter>();
var index = 1;
foreach (var taskGuid in relatedRecordGuid)
{
var paramName = "taskId" + index;
filterStringList.Add($" id = @{paramName} ");
taskEqlParams.Add(new EqlParameter(paramName, taskGuid));
index++;
}
taskEqlCommand += String.Join(" OR ", filterStringList);
relatedTaskRecords = new EqlCommand(taskEqlCommand, taskEqlParams).Execute();
}
catch
{
//Do nothing
}
}
if (!relatedTaskRecords.Any())
{
throw new Exception("Hook exception: This comment is a project comment but does not have an existing taskId related");
}
var taskRecord = relatedTaskRecords[0]; //Currently should be related only to 1 task in projects
//Get Project Id
Guid?projectId = null;
if (((List <EntityRecord>)taskRecord["$project_nn_task"]).Any())
{
var projectRecord = ((List <EntityRecord>)taskRecord["$project_nn_task"]).First();
if (projectRecord != null)
{
projectId = (Guid)projectRecord["id"];
}
}
//Add activity log
var subject = $"commented on <a href=\"/projects/tasks/tasks/r/{taskRecord["id"]}/details\">[{taskRecord["key"]}] {taskRecord["subject"]}</a>";
var relatedRecords = new List <string>()
{
taskRecord["id"].ToString(), record["id"].ToString()
};
if (projectId != null)
{
relatedRecords.Add(projectId.ToString());
}
var body = "";
if (!String.IsNullOrWhiteSpace((string)record["body"]))
{
HtmlDocument doc = new HtmlDocument();
doc.LoadHtml((string)record["body"]);
var root = doc.DocumentNode;
var sb = new StringBuilder();
foreach (var node in root.DescendantsAndSelf())
{
if (!node.HasChildNodes)
{
string text = node.InnerText;
if (!string.IsNullOrEmpty(text))
{
sb.AppendLine(text.Trim());
}
}
}
var commentText = sb.ToString();
if (commentText.Length > 150)
{
body = sb.ToString().Substring(0, 150);
if (commentText.Length > 150)
{
body += "...";
}
}
}
var scope = new List <string>()
{
"projects"
};
new FeedItemService().Create(id: Guid.NewGuid(), createdBy: SecurityContext.CurrentUser.Id, subject: subject,
body: body, relatedRecords: relatedRecords, scope: scope, type: "comment");
}
}