static async Task Main(string[] args)
{
var queryWorkflow = new QueryWorkflow();
var displayResults = new DisplayResults();
var connectionString = UserInteractions.AskUserForAuthenticationInformation();
var sqlCommand = UserInteractions.PromptUserForSqlCommand();
var results = await queryWorkflow.ExecuteQuery(connectionString, sqlCommand);
displayResults.WriteSelectResultsToConsole(results);
}
static public void init()
{
using (ApplicationDbContext db = new ApplicationDbContext())
{
if (db.QueryWorkflows.Count() == 0)
{
string path = Path.Combine(HttpContext.Current.Server.MapPath("~"), "InitData");
string fileName = Path.Combine(path, "QueryWorkflow.csv");
if (File.Exists(fileName))
{
var lines = File.ReadAllLines(fileName);
int count = 1;
var usernameWiseIds = db.Users.GroupBy(u => u.UserName).ToDictionary(d => d.Key, d => d.ToList().First().Id);
foreach (var line in lines)
{
QueryWorkflow queryWorkflow = new QueryWorkflow();
queryWorkflow.Name = $"Workflow - {count++}";
var users = line.Split(',');
for (int i = 1; i <= users.Length; i++)
{
string userName = users[i - 1];
if (usernameWiseIds.ContainsKey(userName))
{
queryWorkflow.WorkflowUsers.Add(new QueryWorkflowUser
{
Role = (QueryRole)i,
UserId = usernameWiseIds[userName],
Workflow = queryWorkflow
});
}
}
db.QueryWorkflows.Add(queryWorkflow);
}
db.SaveChanges();
}
else
{
throw new FileNotFoundException($"Default Query Workflows Not Found In The Database. Please Create {fileName}.");
}
}
}
}
public object SaveWorkflows(List <QueryWorkflowUserDTO> dtos)
{
using (TransactionScope scope = new TransactionScope())
using (ApplicationDbContext db = new ApplicationDbContext())
{
var queryWorkflowUsers = db.QueryWorkflowUsers.Include(nameof(QueryWorkflowUser.User)).ToList();
var userWiseWorkflowIds = queryWorkflowUsers
.Where(qwu => qwu.Role == QueryRole.L1)
.GroupBy(qwu => qwu.User.UserName)
.ToDictionary(d => d.Key, d => d.First().WorkflowId);
var usernameWiseIds = db.Users.GroupBy(u => u.UserName).ToDictionary(d => d.Key, d => d.First().Id);
foreach (var dto in dtos)
{
//If workflow exists previously
if (userWiseWorkflowIds.ContainsKey(dto.L1User))
{
var workflowId = userWiseWorkflowIds[dto.L1User];
if (!String.IsNullOrEmpty(dto.L1User))
{
var L1user = queryWorkflowUsers.Where(qwu => qwu.WorkflowId == workflowId && qwu.Role == QueryRole.L1).FirstOrDefault();
if (L1user != null)
{
L1user.UserId = usernameWiseIds[dto.L1User];
}
else
{
db.QueryWorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L1, UserId = usernameWiseIds[dto.L1User], WorkflowId = workflowId
});
}
}
if (!String.IsNullOrEmpty(dto.L2User))
{
var L2user = queryWorkflowUsers.Where(qwu => qwu.WorkflowId == workflowId && qwu.Role == QueryRole.L2).FirstOrDefault();
if (L2user != null)
{
L2user.UserId = usernameWiseIds[dto.L2User];
}
else
{
db.QueryWorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L2, UserId = usernameWiseIds[dto.L2User], WorkflowId = workflowId
});
}
}
if (!String.IsNullOrEmpty(dto.L3User))
{
var L3user = queryWorkflowUsers.Where(qwu => qwu.WorkflowId == workflowId && qwu.Role == QueryRole.L3).FirstOrDefault();
if (L3user != null)
{
L3user.UserId = usernameWiseIds[dto.L3User];
}
else
{
db.QueryWorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L3, UserId = usernameWiseIds[dto.L3User], WorkflowId = workflowId
});
}
}
if (!String.IsNullOrEmpty(dto.L4User))
{
var L4user = queryWorkflowUsers.Where(qwu => qwu.WorkflowId == workflowId && qwu.Role == QueryRole.L4).FirstOrDefault();
if (L4user != null)
{
L4user.UserId = usernameWiseIds[dto.L4User];
}
else
{
db.QueryWorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L4, UserId = usernameWiseIds[dto.L4User], WorkflowId = workflowId
});
}
}
if (!String.IsNullOrEmpty(dto.L5User))
{
var L5user = queryWorkflowUsers.Where(qwu => qwu.WorkflowId == workflowId && qwu.Role == QueryRole.L5).FirstOrDefault();
if (L5user != null)
{
L5user.UserId = usernameWiseIds[dto.L5User];
}
else
{
db.QueryWorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L5, UserId = usernameWiseIds[dto.L5User], WorkflowId = workflowId
});
}
}
}
//Create new workflow
else if (dto.HasValidData)
{
QueryWorkflow workflow = new QueryWorkflow();
workflow.Name = dto.L1User;
workflow.WorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L1, UserId = usernameWiseIds[dto.L1User], Workflow = workflow
});
if (!String.IsNullOrEmpty(dto.L2User))
{
workflow.WorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L2, UserId = usernameWiseIds[dto.L2User], Workflow = workflow
});
}
if (!String.IsNullOrEmpty(dto.L3User))
{
workflow.WorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L3, UserId = usernameWiseIds[dto.L3User], Workflow = workflow
});
}
if (!String.IsNullOrEmpty(dto.L4User))
{
workflow.WorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L4, UserId = usernameWiseIds[dto.L4User], Workflow = workflow
});
}
if (!String.IsNullOrEmpty(dto.L5User))
{
workflow.WorkflowUsers.Add(new QueryWorkflowUser {
Role = QueryRole.L5, UserId = usernameWiseIds[dto.L5User], Workflow = workflow
});
}
db.QueryWorkflows.Add(workflow);
}
}
db.SaveChanges();
scope.Complete();
}
return("Workflows Saved Successfully");
}