public ErpUser GetUser(Guid userId)
{
var result = new EqlCommand("SELECT *, $user_role.* FROM user WHERE id = @id",
new List<EqlParameter> { new EqlParameter("id", userId) }).Execute();
if (result.Count != 1)
return null;
return result[0].MapTo<ErpUser>();
}
public void Delete(Guid recordId)
{
//Validate - only authors can start to delete their posts and comments. Moderation will be later added if needed
{
var eqlCommand = "SELECT id,created_by FROM comment WHERE id = @commentId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("commentId", recordId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (!eqlResult.Any())
{
throw new Exception("RecordId not found");
}
if ((Guid)eqlResult[0]["created_by"] != SecurityContext.CurrentUser.Id)
{
throw new Exception("Only the author can delete its comment");
}
}
var commentIdListForDeletion = new List <Guid>();
//Add requested
commentIdListForDeletion.Add(recordId);
//Find and add all the child comments
//TODO currently only on level if comment nesting is implemented. If it is increased this method should be changed
{
var eqlCommand = "SELECT id FROM comment WHERE parent_id = @commentId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("commentId", recordId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
foreach (var childComment in eqlResult)
{
commentIdListForDeletion.Add((Guid)childComment["id"]);
}
}
//Create transaction
//Trigger delete
foreach (var commentId in commentIdListForDeletion)
{
//Remove case relations
//TODO
var deleteResponse = new RecordManager().DeleteRecord("comment", commentId);
if (!deleteResponse.Success)
{
throw new Exception(deleteResponse.Message);
}
}
}
public override object Execute(Dictionary <string, object> arguments)
{
var currentUser = SecurityContext.CurrentUser;
if (currentUser == null)
{
return(null);
}
var userOpenTasks = new List <EntityRecord>();
var searchQuery = (string)arguments["search_query"];
{
var eqlCommand = "SELECT * from task WHERE owner_id = @userId AND (start_date >= @currentDate OR x_billable_minutes > 0 OR x_nonbillable_minutes > 0) ";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("userId", currentUser.Id)
};
eqlParams.Add(new EqlParameter("currentDate", new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0)));
if (!String.IsNullOrWhiteSpace(eqlCommand))
{
eqlCommand += " AND x_search CONTAINS @search";
eqlParams.Add(new EqlParameter("search", searchQuery));
}
userOpenTasks = new EqlCommand(eqlCommand, eqlParams).Execute();
}
var userTimelogs = new List <EntityRecord>();
{
var eqlCommand = "SELECT * from timelog WHERE created_by = @userId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("userId", currentUser.Id)
};
userTimelogs = new EqlCommand(eqlCommand, eqlParams).Execute();
}
foreach (var task in userOpenTasks)
{
task.Properties.Add("logged_minutes", (int)0);
task.Properties.Add("last_logged_on", (DateTime)DateTime.MinValue);
var taskTimelogs = userTimelogs.FindAll(x => (x["l_related_records"] == null ? "" : (string)x["l_related_records"]).Contains(task["id"].ToString())).ToList();
if (taskTimelogs.Count > 0)
{
task["logged_minutes"] = (int)(taskTimelogs.Select(x => (decimal)x["minutes"]).Aggregate(func: (aggrResult, x) => aggrResult + x));
var lastLog = taskTimelogs.OrderByDescending(x => (DateTime)x["created_on"]).FirstOrDefault();
if (lastLog != null)
{
task["last_logged_on"] = (DateTime)lastLog["created_on"];
}
}
}
return(userOpenTasks.OrderByDescending(x => (DateTime?)x["timelog_started_on"]).ThenByDescending(x => (DateTime)x["last_logged_on"]).ToList());
}
internal SmtpService GetSmtpServiceInternal(Guid id)
{
var result = new EqlCommand("SELECT * FROM smtp_service WHERE id = @id", new EqlParameter("id", id)).Execute();
if (result.Count == 0)
{
throw new Exception($"SmtpService with id = '{id}' not found.");
}
return(result[0].MapTo <SmtpService>());
}
public void OnGet()
{
Init();
#region << InitPage >>
int pager = 0;
string sortBy = "";
QuerySortType sortOrder = QuerySortType.Ascending;
PageUtils.GetListQueryParams(PageContext.HttpContext, out pager, out sortBy, out sortOrder);
Pager = pager;
SortBy = sortBy;
SortOrder = sortOrder;
//var appServ = new AppService();
//var apps = appServ.GetAllApplications();
//TotalCount = apps.Count;
//ReturnUrlEncoded = HttpUtility.UrlEncode(PageUtils.GetCurrentUrl(PageContext.HttpContext));
#endregion
#region << Create Columns >>
Columns = new List <GridColumn>()
{
new GridColumn()
{
Name = "action",
Width = "1%"
},
new GridColumn()
{
Label = "name",
Name = "name",
Width = "200px"
},
new GridColumn()
{
Label = "description",
Name = "description"
}
};
#endregion
#region << Records >>
var eql = " SELECT * FROM role ";
List <EqlParameter> eqlParams = new List <EqlParameter>();
Records = new EqlCommand(eql, eqlParams).Execute();
TotalCount = Records.TotalCount;
#endregion
BeforeRender();
}
internal Email GetEmail(Guid id)
{
var result = new EqlCommand("SELECT * FROM email WHERE id = @id", new EqlParameter("id", id)).Execute();
if (result.Count == 1)
{
return(result[0].MapTo <Email>());
}
return(null);
}
public EntityRecordList GetProjectTimelogs(Guid projectId)
{
var projectRecord = new EntityRecord();
var eqlCommand = "SELECT * from timelog WHERE l_related_records CONTAINS @projectId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("projectId", projectId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
return(eqlResult);
}
public void PostUpdateApiHookLogic(string entityName, EntityRecord record)
{
//Update key and search fields
Guid projectId = Guid.Empty;
Guid? projectOwnerId = null;
string taskSubject = "";
var patchRecord = new TaskService().SetCalculationFields((Guid)record["id"], subject: out taskSubject, projectId: out projectId, projectOwnerId: out projectOwnerId);
var updateResponse = new RecordManager(executeHooks: false).UpdateRecord("task", patchRecord);
if (!updateResponse.Success)
{
throw new Exception(updateResponse.Message);
}
//Check if owner is in watchers list. If not create relation
if (record.Properties.ContainsKey("owner_id") && record["owner_id"] != null)
{
var watchers = new List <Guid>();
var eqlCommand = "SELECT id, $user_nn_task_watchers.id FROM task WHERE id = @taskId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("taskId", (Guid)record["id"])
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
foreach (var relRecord in eqlResult)
{
if (relRecord.Properties.ContainsKey("$user_nn_task_watchers") && relRecord["$user_nn_task_watchers"] is List <EntityRecord> )
{
var currentWatchers = (List <EntityRecord>)relRecord["$user_nn_task_watchers"];
foreach (var watchRecord in currentWatchers)
{
watchers.Add((Guid)watchRecord["id"]);
}
}
}
if (!watchers.Contains((Guid)record["owner_id"]))
{
var watchRelation = new EntityRelationManager().Read("user_nn_task_watchers").Object;
if (watchRelation == null)
{
throw new Exception("Watch relation not found");
}
var createRelResponse = new RecordManager().CreateRelationManyToManyRecord(watchRelation.Id, (Guid)record["owner_id"], (Guid)record["id"]);
if (!createRelResponse.Success)
{
throw new Exception(createRelResponse.Message);
}
}
}
}
public EntityRecord GetPageHookLogic(BaseErpPageModel pageModel, EntityRecord record)
{
if (record == null)
{
record = new EntityRecord();
}
//Preselect owner
ErpUser currentUser = (ErpUser)pageModel.DataModel.GetProperty("CurrentUser");
if (currentUser != null)
{
record["owner_id"] = currentUser.Id;
}
//$project_nn_task.id
//Preselect project
if (pageModel.HttpContext.Request.Query.ContainsKey("projectId"))
{
var projectQueryId = pageModel.HttpContext.Request.Query["projectId"].ToString();
if (Guid.TryParse(projectQueryId, out Guid outGuid))
{
var projectIdList = new List <Guid>();
projectIdList.Add(outGuid);
record["$project_nn_task.id"] = projectIdList;
}
}
else
{
var eqlCommand = "SELECT created_on,type_id,$project_nn_task.id FROM task WHERE created_by = @currentUserId ORDER BY created_on DESC PAGE 1 PAGESIZE 1";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("currentUserId", currentUser.Id)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (eqlResult != null && eqlResult is EntityRecordList && eqlResult.Count > 0)
{
var relatedProjects = (List <EntityRecord>)eqlResult[0]["$project_nn_task"];
if (relatedProjects.Count > 0)
{
var projectIdList = new List <Guid>();
projectIdList.Add((Guid)relatedProjects[0]["id"]);
record["$project_nn_task.id"] = projectIdList;
}
record["type_id"] = (Guid?)eqlResult[0]["type_id"];
}
}
//Preset start date
record["start_time"] = DateTime.Now.Date.ClearKind();
record["end_time"] = DateTime.Now.Date.ClearKind().AddDays(1);
return(record);
}
public void ProcessSmtpQueue()
{
lock (lockObject)
{
if (queueProcessingInProgress)
{
return;
}
queueProcessingInProgress = true;
}
try
{
List <Email> pendingEmails = new List <Email>();
do
{
EmailServiceManager serviceManager = new EmailServiceManager();
pendingEmails = new EqlCommand("SELECT * FROM email WHERE status = @status AND scheduled_on <> NULL" +
" AND scheduled_on < @scheduled_on ORDER BY priority DESC, scheduled_on ASC PAGE 1 PAGESIZE 10",
new EqlParameter("status", ((int)EmailStatus.Pending).ToString()),
new EqlParameter("scheduled_on", DateTime.UtcNow)).Execute().MapTo <Email>();
foreach (var email in pendingEmails)
{
var service = serviceManager.GetSmtpService(email.ServiceId);
if (service == null)
{
email.Status = EmailStatus.Aborted;
email.ServerError = "SMTP service not found.";
email.ScheduledOn = null;
SaveEmail(email);
continue;
}
else
{
SendEmail(email, service);
}
}
}while (pendingEmails.Count > 0);
}
finally
{
lock (lockObject)
{
queueProcessingInProgress = false;
}
}
}
public ErpUser GetUserByUsername(string username)
{
var result = new EqlCommand("SELECT *, $user_role.* FROM user WHERE username = @username",
new List <EqlParameter> {
new EqlParameter("username", username)
}).Execute();
if (result.Count != 1)
{
return(null);
}
return(result[0].MapTo <ErpUser>());
}
public ErpUser GetUser(string email)
{
var result = new EqlCommand("SELECT *, $user_role.* FROM user WHERE email = @email",
new List <EqlParameter> {
new EqlParameter("email", email)
}).Execute();
if (result.Count != 1)
{
return(null);
}
return(result[0].MapTo <ErpUser>());
}
public override object Execute(Dictionary <string, object> arguments)
{
var currentUser = SecurityContext.CurrentUser;
if (currentUser == null)
{
return(null);
}
var userOpenTasks = new TaskService().GetTaskQueue(null, currentUser.Id, Model.TasksDueType.StartTimeDue, includeProjectData: true);
var userTimelogs = new List <EntityRecord>();
{
var eqlCommand = "SELECT * from timelog WHERE created_by = @userId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("userId", currentUser.Id)
};
userTimelogs = new EqlCommand(eqlCommand, eqlParams).Execute();
}
foreach (var task in userOpenTasks)
{
task.Properties.Add("logged_minutes", (int)0);
task.Properties.Add("last_logged_on", (DateTime)DateTime.MinValue);
var taskTimelogs = userTimelogs.FindAll(x => (x["l_related_records"] == null ? "" : (string)x["l_related_records"]).Contains(task["id"].ToString())).ToList();
if (taskTimelogs.Count > 0)
{
task["logged_minutes"] = (int)(taskTimelogs.Select(x => (decimal)x["minutes"]).Aggregate(func: (aggrResult, x) => aggrResult + x));
var lastLog = taskTimelogs.OrderByDescending(x => (DateTime)x["created_on"]).FirstOrDefault();
if (lastLog != null)
{
task["last_logged_on"] = (DateTime)lastLog["created_on"];
}
}
task["is_billable"] = true;
if (task.Properties.ContainsKey("$project_nn_task") && task["$project_nn_task"] != null && task["$project_nn_task"] is List <EntityRecord> &&
((List <EntityRecord>)task["$project_nn_task"]).Any())
{
var firstRecord = ((List <EntityRecord>)task["$project_nn_task"])[0];
if (firstRecord.Properties.ContainsKey("is_billable") && firstRecord["is_billable"] != null && firstRecord["is_billable"] is Boolean)
{
task["is_billable"] = (bool)firstRecord["is_billable"];
}
}
}
return(userOpenTasks.OrderByDescending(x => (DateTime?)x["created_on"]).ToList());
}
public EntityRecordList GetTaskStatuses()
{
var projectRecord = new EntityRecord();
var eqlCommand = "SELECT * from task_status";
var eqlParams = new List <EqlParameter>();
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (!eqlResult.Any())
{
throw new Exception("Error: No task statuses found");
}
return(eqlResult);
}
public ErpUser GetUser(string email, string password)
{
var encryptedPassword = PasswordUtil.GetMd5Hash(password);
var result = new EqlCommand("SELECT *, $user_role.* FROM user WHERE email = @email AND password = @password",
new List <EqlParameter> {
new EqlParameter("email", email), new EqlParameter("password", encryptedPassword)
}).Execute();
if (result.Count != 1)
{
return(null);
}
return(result[0].MapTo <ErpUser>());
}
private void InitPage()
{
if (RecordId != null)
{
var resultRecords = new EqlCommand($"select * from role where id = '{RecordId}'").Execute();
if (resultRecords.Any())
{
RoleRecord = resultRecords.First();
}
}
if (string.IsNullOrWhiteSpace(ReturnUrl))
{
ReturnUrl = "/sdk/access/role/l/list";
}
}
public EntityRecord Get(Guid userId)
{
var projectRecord = new EntityRecord();
var eqlCommand = "SELECT * from user WHERE id = @userId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("userId", userId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (!eqlResult.Any())
{
return(null);
}
return(eqlResult[0]);
}
public ErpUser GetUser(string email, string password)
{
if (string.IsNullOrWhiteSpace(email))
return null;
var encryptedPassword = PasswordUtil.GetMd5Hash(password);
var result = new EqlCommand("SELECT *, $user_role.* FROM user WHERE email ~* @email AND password = @password",
new List<EqlParameter> { new EqlParameter("email", email), new EqlParameter("password", encryptedPassword) }).Execute();
foreach(var rec in result)
{
if (((string)rec["email"]).ToLowerInvariant() == email.ToLowerInvariant())
return rec.MapTo<ErpUser>();
}
return null;
}
public EntityRecord Get(Guid projectId)
{
var projectRecord = new EntityRecord();
var eqlCommand = "SELECT * from project WHERE id = @projectId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("projectId", projectId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (!eqlResult.Any())
{
throw new Exception("Error: No project was found for this ProjectId");
}
return(eqlResult.First());
}
public EntityRecord GetTask(Guid taskId)
{
var projectRecord = new EntityRecord();
var eqlCommand = " SELECT * from task WHERE id = @taskId";
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("taskId", taskId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (!eqlResult.Any())
{
return(null);
}
else
{
return(eqlResult[0]);
}
}
private static void FixSenderAndRecipients(RecordManager recMan)
{
var emails = new EqlCommand("SELECT * FROM email").Execute();
foreach (var email in emails)
{
email["sender"] = JsonConvert.SerializeObject(new EmailAddress {
Name = (string)email["sender_name"], Address = (string)email["sender_email"]
});
email["recipients"] = JsonConvert.SerializeObject(new List <EmailAddress> {
new EmailAddress {
Name = (string)email["recipient_name"], Address = (string)email["recipient_email"]
}
});
var response = recMan.UpdateRecord("email", email);
if (!response.Success)
{
throw new Exception(response.Message);
}
}
}
private void InitPage()
{
var roles = new EqlCommand("select * from role").Execute();
foreach (var role in roles)
{
if ((string)role["name"] != "guest")
{
RoleOptions.Add(new SelectOption()
{
Value = role["id"].ToString(), Label = role["name"].ToString()
});
}
}
RoleOptions = RoleOptions.OrderBy(x => x.Label).ToList();
if (string.IsNullOrWhiteSpace(ReturnUrl))
{
ReturnUrl = "/sdk/access/user/l/list";
}
}
public ActionResult CreateTimelog([FromBody] EntityRecord record)
{
var response = new ResponseModel();
#region << Init >>
var recordId = Guid.NewGuid();
Guid relatedRecordId = Guid.Empty;
if (!record.Properties.ContainsKey("relatedRecordId") || record["relatedRecordId"] == null)
{
throw new Exception("relatedRecordId is required");
}
if (Guid.TryParse((string)record["relatedRecordId"], out Guid outGuid))
{
relatedRecordId = outGuid;
}
else
{
throw new Exception("relatedRecordId is invalid Guid");
}
var scope = new List <string>()
{
"projects"
};
var relatedRecords = new List <Guid>()
{
relatedRecordId
};
if (record.Properties.ContainsKey("relatedRecords") && record["relatedRecords"] != null)
{
relatedRecords = JsonConvert.DeserializeObject <List <Guid> >((string)record["relatedRecords"]);
}
var body = "";
if (record.Properties.ContainsKey("body") && record["body"] != null)
{
body = (string)record["body"];
}
Guid currentUserId = SystemIds.FirstUserId; //This is for webcomponent development to allow guest submission
if (SecurityContext.CurrentUser != null)
{
currentUserId = SecurityContext.CurrentUser.Id;
}
var minutes = 0;
if (record.Properties.ContainsKey("minutes") && record["minutes"] != null)
{
if (Int32.TryParse(record["minutes"].ToString(), out Int32 outInt32))
{
minutes = outInt32;
}
}
var isBillable = false;
if (record.Properties.ContainsKey("isBillable") && record["isBillable"] != null)
{
if (Boolean.TryParse(record["isBillable"].ToString(), out bool outBool))
{
isBillable = outBool;
}
}
var loggedOn = new DateTime();
if (record.Properties.ContainsKey("loggedOn") && record["loggedOn"] != null)
{
if (DateTime.TryParse(record["loggedOn"].ToString(), out DateTime outDateTime))
{
loggedOn = outDateTime;
}
}
#endregion
try
{
new TimeLogService().Create(recordId, currentUserId, DateTime.Now, loggedOn, minutes, isBillable, body, scope, relatedRecords);
}
catch (Exception ex)
{
throw ex;
}
response.Success = true;
response.Message = "Timelog successfully created";
var eqlCommand = @"SELECT *,$user_1n_timelog.image,$user_1n_timelog.username
FROM timelog
WHERE id = @recordId" ;
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("recordId", recordId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (eqlResult.Any())
{
response.Object = eqlResult.First();
}
return(Json(response));
}
public void PostPreUpdateApiHookLogic(string entityName, EntityRecord record, List <ErrorModel> errors)
{
var eqlResult = new EqlCommand("SELECT id,number, $project_nn_task.id, $project_nn_task.abbr, $user_nn_task_watchers.id FROM task WHERE id = @taskId", new EqlParameter("taskId", (Guid)record["id"])).Execute();
if (eqlResult.Count > 0)
{
var oldRecord = eqlResult[0];
Guid?oldProjectId = null;
Guid?newProjectId = null;
var taskWatcherIdList = new List <Guid>();
var projectAbbr = "";
if (oldRecord.Properties.ContainsKey("$project_nn_task") && oldRecord["$project_nn_task"] is List <EntityRecord> )
{
var projectRecords = (List <EntityRecord>)oldRecord["$project_nn_task"];
if (projectRecords.Any())
{
if (projectRecords[0].Properties.ContainsKey("id"))
{
oldProjectId = (Guid)projectRecords[0]["id"];
}
if (projectRecords[0].Properties.ContainsKey("abbr"))
{
projectAbbr = (string)projectRecords[0]["abbr"];
}
}
}
if (record.Properties.ContainsKey("$project_nn_task.id") && record["$project_nn_task.id"] != null)
{
if (record["$project_nn_task.id"] is Guid)
{
newProjectId = (Guid)record["$project_nn_task.id"];
}
if (record["$project_nn_task.id"] is string)
{
newProjectId = new Guid(record["$project_nn_task.id"].ToString());
}
}
if (oldRecord.Properties.ContainsKey("$user_nn_task_watchers") && oldRecord["$user_nn_task_watchers"] is List <EntityRecord> )
{
var watcherRecords = (List <EntityRecord>)oldRecord["$user_nn_task_watchers"];
foreach (var watcherRecord in watcherRecords)
{
taskWatcherIdList.Add((Guid)watcherRecord["id"]);
}
}
if (oldProjectId != null && newProjectId != null && oldProjectId != newProjectId)
{
var recMan = new RecordManager();
var relations = new EntityRelationManager().Read().Object;
var projectTaskRel = relations.First(x => x.Name == "project_nn_task");
if (projectTaskRel == null)
{
throw new Exception("project_nn_task relation not found");
}
//Remove all NN relation
var removeRelResponse = recMan.RemoveRelationManyToManyRecord(projectTaskRel.Id, oldProjectId, (Guid)record["id"]);
if (!removeRelResponse.Success)
{
throw new Exception(removeRelResponse.Message);
}
//Create new NN relation
var addRelResponse = recMan.RemoveRelationManyToManyRecord(projectTaskRel.Id, newProjectId, (Guid)record["id"]);
if (!addRelResponse.Success)
{
throw new Exception(addRelResponse.Message);
}
//change key
record["key"] = projectAbbr + "-" + ((decimal)oldRecord["number"]).ToString("N0");
var projectEqlResult = new EqlCommand("SELECT id,owner_id FROM project WHERE id = @projectId",
new EqlParameter("projectId", newProjectId)).Execute();
Guid?projectOwnerId = null;
if (projectEqlResult != null && ((List <EntityRecord>)projectEqlResult).Any())
{
var newProjectRecord = ((List <EntityRecord>)projectEqlResult).First();
if (newProjectRecord.Properties.ContainsKey("owner_id") && newProjectRecord["owner_id"] != null)
{
if (newProjectRecord["owner_id"] is Guid)
{
projectOwnerId = (Guid)newProjectRecord["owner_id"];
}
if (newProjectRecord["owner_id"] is string)
{
projectOwnerId = new Guid(newProjectRecord["owner_id"].ToString());
}
}
}
//check if the new project owner is in the watcher list and add it if not
if (projectOwnerId != null && !taskWatcherIdList.Contains(projectOwnerId.Value))
{
var watcherTaskRel = relations.First(x => x.Name == "user_nn_task_watchers");
if (watcherTaskRel == null)
{
throw new Exception("user_nn_task_watchers relation not found");
}
//Create new NN relation
var addWatcherRelResponse = recMan.RemoveRelationManyToManyRecord(watcherTaskRel.Id, projectOwnerId, (Guid)record["id"]);
if (!addWatcherRelResponse.Success)
{
throw new Exception(addWatcherRelResponse.Message);
}
}
}
}
}
public EntityRecordList GetTaskQueue(Guid?projectId, Guid?userId, TasksDueType type = TasksDueType.All, int?limit = null, bool includeProjectData = false)
{
var selectedFields = "*";
if (includeProjectData)
{
selectedFields += ",$project_nn_task.is_billable";
}
var eqlCommand = $"SELECT {selectedFields} from task ";
var eqlParams = new List <EqlParameter>();
eqlParams.Add(new EqlParameter("currentDateStart", DateTime.Now.Date));
eqlParams.Add(new EqlParameter("currentDateEnd", DateTime.Now.Date.AddDays(1)));
var whereFilters = new List <string>();
// Start time
if (type == TasksDueType.StartTimeDue)
{
whereFilters.Add("(start_time < @currentDateEnd OR start_time = null)");
}
if (type == TasksDueType.StartTimeNotDue)
{
whereFilters.Add("start_time > @currentDateEnd");
}
// End time
if (type == TasksDueType.EndTimeOverdue)
{
whereFilters.Add("end_time < @currentDateStart");
}
if (type == TasksDueType.EndTimeDueToday)
{
whereFilters.Add("(end_time >= @currentDateStart AND end_time < @currentDateEnd)");
}
if (type == TasksDueType.EndTimeNotDue)
{
whereFilters.Add("(end_time >= @currentDateEnd OR end_time = null)");
}
// Project and user
if (projectId != null && userId != null)
{
whereFilters.Add("$project_nn_task.id = @projectId AND owner_id = @userId");
eqlParams.Add(new EqlParameter("projectId", projectId));
eqlParams.Add(new EqlParameter("userId", userId));
}
else if (projectId != null)
{
whereFilters.Add("$project_nn_task.id = @projectId");
eqlParams.Add(new EqlParameter("projectId", projectId));
}
else if (userId != null)
{
whereFilters.Add("owner_id = @userId");
eqlParams.Add(new EqlParameter("userId", userId));
}
//Status open
if (type != TasksDueType.All)
{
var taskStatuses = new TaskService().GetTaskStatuses();
var closedStatusHashset = new HashSet <Guid>();
foreach (var taskStatus in taskStatuses)
{
if ((bool)taskStatus["is_closed"])
{
closedStatusHashset.Add((Guid)taskStatus["id"]);
}
}
var index = 1;
foreach (var key in closedStatusHashset)
{
var paramName = "status" + index;
whereFilters.Add($"status_id <> @{paramName}");
eqlParams.Add(new EqlParameter(paramName, key));
index++;
}
}
if (whereFilters.Count > 0)
{
eqlCommand += " WHERE " + string.Join(" AND ", whereFilters);
}
//Order
switch (type)
{
case TasksDueType.All:
// No sort for optimization purposes
break;
case TasksDueType.EndTimeOverdue:
eqlCommand += $" ORDER BY end_time ASC, priority DESC";
break;
case TasksDueType.EndTimeDueToday:
eqlCommand += $" ORDER BY priority DESC";
break;
case TasksDueType.EndTimeNotDue:
eqlCommand += $" ORDER BY end_time ASC, priority DESC";
break;
case TasksDueType.StartTimeDue:
eqlCommand += $" ORDER BY end_time ASC, priority DESC";
break;
case TasksDueType.StartTimeNotDue:
eqlCommand += $" ORDER BY end_time ASC, priority DESC";
break;
default:
throw new Exception("Unknown TasksDueType");
}
//Limit
if (limit != null)
{
eqlCommand += $" PAGE 1 PAGESIZE {limit} ";
}
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
return(eqlResult);
}
private static void RecordHookSample()
{
//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
{
connection.BeginTransaction();
RecordManager recMan = new RecordManager();
//list all records from role entity
var existingRoles = new EqlCommand("SELECT * FROM role").Execute();
Console.WriteLine();
Console.WriteLine($"=== existing roles ===");
foreach (var rec in existingRoles)
{
Console.WriteLine($"name:{rec["name"]}");
}
//create new role record to triger record hook
EntityRecord newRec = new EntityRecord();
newRec["id"] = Guid.NewGuid();
newRec["name"] = "New Role";
var result = recMan.CreateRecord("role", newRec);
if (!result.Success)
{
throw new Exception(result.Message);
}
Console.WriteLine($"=== roles after create ===");
existingRoles = new EqlCommand("SELECT * FROM role").Execute();
foreach (var rec in existingRoles)
{
Console.WriteLine($"name:{rec["name"]}");
}
newRec["name"] = "New changed Role";
result = recMan.UpdateRecord("role", newRec);
if (!result.Success)
{
throw new Exception(result.Message);
}
Console.WriteLine($"=== roles after update ===");
existingRoles = new EqlCommand("SELECT * FROM role").Execute();
foreach (var rec in existingRoles)
{
Console.WriteLine($"name:{rec["name"]}");
}
result = recMan.DeleteRecord("role", (Guid)newRec["id"]);
if (!result.Success)
{
throw new Exception(result.Message);
}
Console.WriteLine($"=== roles after delete ===");
existingRoles = new EqlCommand("SELECT * FROM role").Execute();
foreach (var rec in existingRoles)
{
Console.WriteLine($"name:{rec["name"]}");
}
}
finally
{
//we allways rollback transaction - this method is only for presentation how hooks are triggered from console app
connection.RollbackTransaction();
}
}
}
}
}
public IActionResult TestSmtpServiceOnPost(RecordDetailsPageModel pageModel)
{
SmtpService smtpService = null;
string recipientEmail = string.Empty;
string subject = string.Empty;
string content = string.Empty;
ValidationException valEx = new ValidationException();
if (pageModel.HttpContext.Request.Form == null)
{
valEx.AddError("form", "Smtp service test page missing form tag");
valEx.CheckAndThrow();
}
if (!pageModel.HttpContext.Request.Form.ContainsKey("recipient_email"))
{
valEx.AddError("recipient_email", "Recipient email is not specified.");
}
else
{
recipientEmail = pageModel.HttpContext.Request.Form["recipient_email"];
if (string.IsNullOrWhiteSpace(recipientEmail))
{
valEx.AddError("recipient_email", "Recipient email is not specified");
}
else if (!recipientEmail.IsEmail())
{
valEx.AddError("recipient_email", "Recipient email is not a valid email address");
}
}
if (!pageModel.HttpContext.Request.Form.ContainsKey("subject"))
{
valEx.AddError("subject", "Subject is not specified");
}
else
{
subject = pageModel.HttpContext.Request.Form["subject"];
if (string.IsNullOrWhiteSpace(subject))
{
valEx.AddError("subject", "Subject is required");
}
}
if (!pageModel.HttpContext.Request.Form.ContainsKey("content"))
{
valEx.AddError("content", "Content is not specified");
}
else
{
content = pageModel.HttpContext.Request.Form["content"];
if (string.IsNullOrWhiteSpace(content))
{
valEx.AddError("content", "Content is required");
}
}
var smtpServiceId = pageModel.DataModel.GetProperty("Record.id") as Guid?;
if (smtpServiceId == null)
{
valEx.AddError("serviceId", "Invalid smtp service id");
}
else
{
smtpService = new EmailServiceManager().GetSmtpService(smtpServiceId.Value);
if (smtpService == null)
{
valEx.AddError("serviceId", "Smtp service with specified id does not exist");
}
}
List <string> attachments = new List <string>();
if (pageModel.HttpContext.Request.Form.ContainsKey("attachments"))
{
var ids = pageModel.HttpContext.Request.Form["attachments"].ToString().Split(",", StringSplitOptions.RemoveEmptyEntries).Select(x => new Guid(x));
foreach (var id in ids)
{
var fileRecord = new EqlCommand("SELECT name,path FROM user_file WHERE id = @id", new EqlParameter("id", id)).Execute().FirstOrDefault();
if (fileRecord != null)
{
attachments.Add((string)fileRecord["path"]);
}
}
}
//we set current record to store properties which don't exist in current entity
EntityRecord currentRecord = pageModel.DataModel.GetProperty("Record") as EntityRecord;
currentRecord["recipient_email"] = recipientEmail;
currentRecord["subject"] = subject;
currentRecord["content"] = content;
pageModel.DataModel.SetRecord(currentRecord);
valEx.CheckAndThrow();
try
{
EmailAddress recipient = new EmailAddress(recipientEmail);
smtpService.SendEmail(recipient, subject, string.Empty, content, attachments: attachments);
pageModel.TempData.Put("ScreenMessage", new ScreenMessage()
{
Message = "Email was successfully sent", Type = ScreenMessageType.Success, Title = "Success"
});
var returnUrl = pageModel.HttpContext.Request.Query["returnUrl"];
return(new RedirectResult($"/mail/services/smtp/r/{smtpService.Id}/details?returnUrl={returnUrl}"));
}
catch (Exception ex)
{
valEx.AddError("", ex.Message);
valEx.CheckAndThrow();
return(null);
}
}
public void ValidatePreCreateRecord(EntityRecord rec, List <ErrorModel> errors)
{
foreach (var prop in rec.Properties)
{
switch (prop.Key)
{
case "name":
{
var result = new EqlCommand("SELECT * FROM smtp_service WHERE name = @name", new EqlParameter("name", rec["name"])).Execute();
if (result.Count > 0)
{
errors.Add(new ErrorModel
{
Key = "name",
Value = (string)rec["name"],
Message = "There is already existing service with that name. Name must be unique"
});
}
}
break;
case "port":
{
if (!Int32.TryParse(rec["port"]?.ToString(), out int port))
{
errors.Add(new ErrorModel
{
Key = "port",
Value = rec["port"]?.ToString(),
Message = $"Port must be an integer value between 1 and 65025"
});
}
else
{
if (port <= 0 || port > 65025)
{
errors.Add(new ErrorModel
{
Key = "port",
Value = rec["port"]?.ToString(),
Message = $"Port must be an integer value between 1 and 65025"
});
}
}
}
break;
case "default_from_email":
{
if (!((string)rec["default_from_email"]).IsEmail())
{
errors.Add(new ErrorModel
{
Key = "default_from_email",
Value = (string)rec["default_from_email"],
Message = $"Default from email address is invalid"
});
}
}
break;
case "default_reply_to_email":
{
if (string.IsNullOrWhiteSpace((string)rec["default_reply_to_email"]))
{
continue;
}
if (!((string)rec["default_reply_to_email"]).IsEmail())
{
errors.Add(new ErrorModel
{
Key = "default_reply_to_email",
Value = (string)rec["default_reply_to_email"],
Message = $"Default reply to email address is invalid"
});
}
}
break;
case "max_retries_count":
{
if (!Int32.TryParse(rec["max_retries_count"]?.ToString(), out int count))
{
errors.Add(new ErrorModel
{
Key = "max_retries_count",
Value = rec["max_retries_count"]?.ToString(),
Message = $"Number of retries on error must be an integer value between 1 and 10"
});
}
else
{
if (count < 1 || count > 10)
{
errors.Add(new ErrorModel
{
Key = "max_retries_count",
Value = rec["max_retries_count"]?.ToString(),
Message = $"Number of retries on error must be an integer value between 1 and 10"
});
}
}
}
break;
case "retry_wait_minutes":
{
if (!Int32.TryParse(rec["retry_wait_minutes"]?.ToString(), out int minutes))
{
errors.Add(new ErrorModel
{
Key = "retry_wait_minutes",
Value = rec["retry_wait_minutes"]?.ToString(),
Message = $"Wait period between retries must be an integer value between 1 and 1440 minutes"
});
}
else
{
if (minutes < 1 || minutes > 1440)
{
errors.Add(new ErrorModel
{
Key = "retry_wait_minutes",
Value = rec["retry_wait_minutes"]?.ToString(),
Message = $"Wait period between retries must be an integer value between 1 and 1440 minutes"
});
}
}
}
break;
case "connection_security":
{
if (!Int32.TryParse(rec["connection_security"] as string, out int connectionSecurityNumber))
{
errors.Add(new ErrorModel
{
Key = "connection_security",
Value = (string)rec["connection_security"],
Message = $"Invalid connection security setting selected."
});
continue;
}
try
{
var secOptions = (MailKit.Security.SecureSocketOptions)connectionSecurityNumber;
}
catch
{
errors.Add(new ErrorModel
{
Key = "connection_security",
Value = (string)rec["connection_security"],
Message = $"Invalid connection security setting selected."
});
}
}
break;
}
}
}
public void OnGet()
{
Init();
#region << InitPage >>
int pager = 0;
string sortBy = "";
QuerySortType sortOrder = QuerySortType.Ascending;
PageUtils.GetListQueryParams(PageContext.HttpContext, out pager, out sortBy, out sortOrder);
Pager = pager;
SortBy = sortBy;
SortOrder = sortOrder;
#endregion
#region << Create Columns >>
Columns = new List <GridColumn>()
{
new GridColumn()
{
Name = "action",
Width = "1%"
},
new GridColumn()
{
Label = "email",
Name = "email",
Sortable = true,
Width = "120px"
},
new GridColumn()
{
Label = "username",
Name = "username",
Sortable = true
},
new GridColumn()
{
Label = "role",
Name = "role",
Sortable = false
}
};
#endregion
#region << Records >>
var eql = " SELECT id,email,username,$user_role.name FROM user ";
List <EqlParameter> eqlParams = new List <EqlParameter>();
//Apply sort
if (!String.IsNullOrWhiteSpace(SortBy) && (new List <string>()
{
"email", "username"
}).Contains(SortBy))
{
eqlParams.Add(new EqlParameter("@sortBy", SortBy));
if (SortOrder == QuerySortType.Descending)
{
eqlParams.Add(new EqlParameter("@sortOrder", "Desc"));
}
else
{
eqlParams.Add(new EqlParameter("@sortOrder", "Asc"));
}
eql += " ORDER BY @sortBy @sortOrder ";
}
else
{
eql += " ORDER BY email Asc ";
}
eql += " PAGE @page ";
eql += " PAGESIZE @pageSize ";
eqlParams.Add(new EqlParameter("@page", Pager));
eqlParams.Add(new EqlParameter("@pageSize", PagerSize));
Records = new EqlCommand(eql, eqlParams).Execute();
TotalCount = Records.TotalCount;
#endregion
BeforeRender();
}
public ActionResult CreateNewPcPostListItem([FromBody] EntityRecord record)
{
var response = new ResponseModel();
#region << Init >>
var recordId = Guid.NewGuid();
Guid relatedRecordId = Guid.Empty;
if (!record.Properties.ContainsKey("relatedRecordId") || record["relatedRecordId"] == null)
{
throw new Exception("relatedRecordId is required");
}
if (Guid.TryParse((string)record["relatedRecordId"], out Guid outGuid))
{
relatedRecordId = outGuid;
}
else
{
throw new Exception("relatedRecordId is invalid Guid");
}
Guid?parentId = null;
if (record.Properties.ContainsKey("parentId") && record["parentId"] != null)
{
if (Guid.TryParse((string)record["parentId"], out Guid outGuid2))
{
parentId = outGuid2;
}
}
var scope = new List <string>()
{
"projects"
};
var relatedRecords = new List <Guid>();
if (record.Properties.ContainsKey("relatedRecords") && record["relatedRecords"] != null)
{
relatedRecords = JsonConvert.DeserializeObject <List <Guid> >((string)record["relatedRecords"]);
}
var subject = "";
if (record.Properties.ContainsKey("subject") && record["subject"] != null)
{
subject = (string)record["subject"];
}
var body = "";
if (record.Properties.ContainsKey("body") && record["body"] != null)
{
body = (string)record["body"];
}
Guid currentUserId = SystemIds.FirstUserId; //This is for webcomponent development to allow guest submission
if (SecurityContext.CurrentUser != null)
{
currentUserId = SecurityContext.CurrentUser.Id;
}
#endregion
try
{
new CommentService().Create(recordId, currentUserId, DateTime.Now, body, parentId, scope, relatedRecords);
}
catch (Exception ex)
{
throw ex;
}
response.Success = true;
response.Message = "Comment successfully created";
var eqlCommand = @"SELECT *,$user_1n_comment.image,$user_1n_comment.username
FROM comment
WHERE id = @recordId" ;
var eqlParams = new List <EqlParameter>()
{
new EqlParameter("recordId", recordId)
};
var eqlResult = new EqlCommand(eqlCommand, eqlParams).Execute();
if (eqlResult.Any())
{
response.Object = eqlResult.First();
}
return(Json(response));
}
