// Import data to [Collect2000].[ERCTasks].[Tasks] and reference table, [Collect2000].[ERCTasks].[TaskUsers], [Collect2000].[ERCTasks].[Users]
// Involved table for data migration [Collect2000].[dbo].[erc_ff_FileProcessing],
public static void ImportDataDailyRecurrenceTask()
{
//[Collect2000].[dbo].erc_ff_FileProcessingData.Frequency should map with [Collect2000].[ERCTasks].[Tasks].RecurrencePattern
//[Collect2000].[dbo].erc_ff_FileProcessingData.Recurrance should map with [Collect2000].[ERCTasks].[Tasks].RecurrencePattern.RecurrenceBusinessDayStep
//Last 4 characters of [Collect2000].[dbo].erc_ff_FileProcessingData.ProcessorSLA should map with [Collect2000].[ERCTasks].[Tasks].TaskDueMinutes
//Left characters of (Total length of [Collect2000].[dbo].erc_ff_FileProcessingData.ProcessorSLA - 4) should map with [Collect2000].[ERCTasks].[Tasks].TaskDueHour
//[Collect2000].[ERCTasks].[Tasks].TaskDueDate should be null in case of daily recurrence.
//We think the [Collect2000].[ERCTasks].[Tasks].TaskName should come from [Collect2000].[dbo].erc_ff_FpProcessorType on behalf of [Collect2000].[dbo].[erc_ff_FileProcessing].ProcessorType.
// We think the [Collect2000].[ERCTasks].[Tasks].TaskDesc will remain blank because we don't get the matched column in [Collect2000].[dbo].[erc_ff_FileProcessing].
//Create User [Collect2000].[ERCTasks].[Users]. We assume that user is [Collect2000].[dbo].[erc_ff_FileProcessing].Processor.
//We verify user using UserManager.GetUserDetails
//For task assignment, UserId & TaskId from above steps to be inserted into [Collect2000].[ERCTasks].[TaskUsers].
// [Collect2000].[dbo].erc_ff_FileProcessingData is the child table of [Collect2000].[dbo].[erc_ff_FileProcessing],
//We don't think data require from this [Collect2000].[dbo].erc_ff_FileProcessingData.
//Following condition should meet
// TaskDueDate = NULL, TaskDueHour > 0, TaskDueMinutes > 0(conditional) and belong to login user
//Temporary using TaskDesc to store the username
//We assume that need to assign the task to admin by default, if it does not require then remove this part
var Dailytasks = new List <Task>();
int userId = 0;//to store recent inserted userId, TaskDueHour > 0
using (var db = new SqlConnection(ConnectionString))
{
var q = @"SELECT DISTINCT top 2
ff.Processor TaskName,
NULL TaskDueDate,
CASE WHEN LEN(tt.ProcessorSLA) > 4 THEN
LEFT(tt.ProcessorSLA, LEN(tt.ProcessorSLA) - 4)
ELSE NULL END TaskDueHour,
CASE WHEN LEN(tt.ProcessorSLA) > 4 THEN
LEFT(RIGHT(tt.ProcessorSLA,4),2)
ELSE NULL END TaskDueMinutes,
tt.Frequency RecurrencePattern,
tt.Recurrance RecurrenceBusinessDayStep,
tt.Processor TaskDesc,
tt.Customer
FROM [Collect2000].[dbo].[erc_ff_FileProcessing] tt
JOIN [Collect2000].[dbo].erc_ff_FpProcessorType ff WITH(NOLOCK) ON ff.pid = tt.ProcessorType
WHERE tt.Frequency = 'Daily'" ;
Dailytasks.AddRange(db.Query <Task>(q));
foreach (var dr in Dailytasks)
{
userId = 0;
var q1 = @"INSERT INTO [collect2000].[ERCTasks].[Tasks]
(TaskDesc, TaskDueDate, TaskDueHour, TaskDueMinutes, RecurrencePattern, RecurrenceBusinessDayStep, TaskName, Customer)
VALUES (@TaskName, @duedate, @hour, @minutes, @pattern, @daystep, @TaskName, @Customer);SELECT SCOPE_IDENTITY();";
var Tid = db.Query <int>(q1, new
{
duedate = dr.TaskDueDate,
hour = dr.TaskDueHour,
minutes = dr.TaskDueMinutes,
pattern = dr.RecurrencePattern,
daystep = dr.RecurrenceBusinessDayStep,
TaskName = dr.TaskName,
Customer = dr.Customer
}).FirstOrDefault <int>();
User usr = UserManager.GetUserDetails(dr.TaskDesc); //dr.TaskDesc = [Collect2000].[dbo].[erc_ff_FileProcessing].Processor. We don't have variable for username in Task class so utilized the un-used TaskDesc temporary.
//create user by [Collect2000].[dbo].[erc_ff_FileProcessing].Processor
if (usr.UserName != null && usr.Email != null)
{
userId = db.ExecuteScalar <int>(
"IF NOT EXISTS(SELECT * FROM [collect2000].[ERCTasks].[Users] WHERE UserName = @userName) BEGIN INSERT INTO [collect2000].[ERCTasks].[Users] (UserName, DisplayName, Email) VALUES (@userName, @dispName, @email); SELECT SCOPE_IDENTITY(); END IF EXISTS(SELECT * FROM [collect2000].[ERCTasks].[Users] WHERE UserName = @userName) SELECT UserId FROM [collect2000].[ERCTasks].[Users] WHERE UserName = @userName",
new { userName = usr.UserName, dispName = usr.DisplayName, email = usr.Email });
}
//assignment of current created task
//in above query we pay attention on if user exist then return the exist userId otherwise new inserted userId will return
//if UserName and Email is null thenin this case UserId will zero because code will not execute.
//We have provided the below codition to avoid assignment if UseId is zero.
if (userId > 0)
{
AssignedToUser(userId, Tid);
}
//default assign to admin
//commented the auto assign to admin for task "Modify ImportDataDailyRecurrenceTask #15"
//in future if we need auto assign to admin then simply uncomment the code
// userId = db.ExecuteScalar<int>(
// "SELECT TOP 1 UserId FROM [collect2000].[ERCTasks].[Users] WHERE UserRole = 'admin'",
// new { userName = usr.UserName, dispName = usr.DisplayName, email = usr.Email });
// AssignedToUser(userId, Tid);
}
}
}
