// CurrentJob = job;
//}
public override void Update()
{
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
string jobCommand = string.Format(SQLCommands.InsertJob, CurrentJob.JobTypeId, CurrentJob.CustomerId, CurrentJob.JobDescription.Replace("'", "''"), CurrentJob.DocketNo, CurrentJob.LotNo, CurrentJob.LodgementDate, CurrentJob.CampaignManagerId, CurrentJob.ProgrammerId, CurrentJob.DocketReceivedDate,
CurrentJob.NoOfRecords, CurrentJob.NoOfFiles, CurrentJob.IsSOARequired, CurrentJob.IsPresortRequired, CurrentJob.IsAddressCleansingRequired, CurrentJob.IsTitleCasingRequired, CurrentJob.IsEDM, CurrentJob.IsLabel, CurrentJob.JobStatusId, CurrentJob.JobNotes.Replace("'", "''"));
try
{
// Add Job Log
int lastInsert = dbc.ExecuteCommand(jobCommand);
string activity = "Job Created - " + DateTime.Now.ToString();
dbc.ExecuteCommand(string.Format(SQLCommands.InsertJobLog, lastInsert, activity));
// Insert Job Tasks
if (CurrentJob.JobTasks != null)
{
foreach (DataRow dr in CurrentJob.JobTasks.Rows)
{
string insertJobTaskSQL = string.Format(SQLCommands.InsertJobTask, lastInsert, dr["TaskId"], dr["HoursSpent"], dr["Comments"].ToString().Replace("'", "''"));
dbc.ExecuteCommand(insertJobTaskSQL);
}
}
dbc.CloseConnection();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public override void Update()
{
try
{
if (!CheckIfJobTypeIsNewSetupAndQATaskIsEntered())
{
throw new Exception("You haven't entered a QA tasks for this job, sorry you cannot change job status to Proof");
}
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
string jobSQL = string.Empty;
string jobCommand = string.Empty;
jobCommand = string.Format(SQLCommands.UpdateJob, CurrentJob.CustomerId, CurrentJob.JobDescription.Replace("'", "''"), CurrentJob.DocketNo, CurrentJob.LotNo, CurrentJob.LodgementDate, CurrentJob.CampaignManagerId,
CurrentJob.ProgrammerId, CurrentJob.DocketReceivedDate, CurrentJob.NoOfRecords, CurrentJob.NoOfFiles, CurrentJob.IsSOARequired, CurrentJob.IsPresortRequired, CurrentJob.IsAddressCleansingRequired,
CurrentJob.IsTitleCasingRequired, 5, CurrentJob.JobTypeId, CurrentJob.IsEDM, CurrentJob.IsLabel, CurrentJob.JobNotes.Replace("'", "''"), CurrentJob.JobId);
dbc.ExecuteCommand(jobCommand);
// Add Job Log
string activity = "Job Updated to PROOF - " + DateTime.Now.ToString();
dbc.ExecuteCommand(string.Format(SQLCommands.InsertJobLog, CurrentJob.JobId, activity));
dbc.CloseConnection();
dbc.CloseConnection();
JobTaskData jobTaskdata = new JobTaskData();
jobTaskdata.UpdateJobTasks(CurrentJob.JobTasks);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void UpdateJobTasks(DataTable Tasks)
{
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
string strSQL = string.Empty;
foreach (DataRow dr in Tasks.Rows)
{
if (dr.RowState == DataRowState.Modified)
{
strSQL = "UPDATE JobTask SET TaskId={0}, HoursSpent={1}, Comments='{2}' WHERE JobTaskId = {3}";
strSQL = string.Format(strSQL, (int)dr["TaskId"], dr["HoursSpent"], dr["Comments"].ToString().Replace("'", "''"), (int)dr["JobTaskId"]);
dbc.ExecuteCommand(strSQL);
}
if (dr.RowState == DataRowState.Added)
{
strSQL = "INSERT INTO JobTask (JobId,TaskId,HoursSpent,Comments) Values ({0},{1},{2},'{3}')";
strSQL = string.Format(strSQL, (int)dr["JobId"], (int)dr["TaskId"], dr["HoursSpent"], dr["Comments"].ToString().Replace("'", "''"));
dbc.ExecuteCommand(strSQL);
}
if (dr.RowState == DataRowState.Deleted)
{
int dataTaskId = (int)dr["JobTaskId", DataRowVersion.Original];
strSQL = "DELETE * FROM JobTask WHERE JobTaskId = {0}";
strSQL = string.Format(strSQL, dataTaskId);
dbc.ExecuteCommand(strSQL);
}
//Debug.WriteLine(dr.RowState);
}
dbc.CloseConnection();
Tasks.AcceptChanges();
}
public static void UpdateUserPassword(SystemUser user)
{
string sql = string.Format(SQLCommands.UpdateUser, user.UserPassword, user.UserId);
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
dbc.ExecuteCommand(sql);
dbc.CloseConnection();
}
public static SystemUser GetSystemUser(string UserName, string Password)
{
DataTable dt;
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
dt = dbc.GetDataTable(string.Format(SQLCommands.User, UserName, Password));
dbc.CloseConnection();
return(CreateUser(dt));
}
public virtual DataTable GetTasks()
{
string sql = "SELECT * FROM vw_Task";
DataTable dt;
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
dt = dbc.GetDataTable(sql);
dbc.CloseConnection();
return(dt);
}
public DataTable GetJobTaskDT(int JobId)
{
string strSQL = string.Format("SELECT * FROM vw_JobTask WHERE JobId = {0}", JobId);
List <JobTask> jobTasks = new List <JobTask>();
DataTable dt;
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
dt = dbc.GetDataTable(strSQL);
dbc.CloseConnection();
return(dt);
}
public static DataTable GetAllJobs(string sql)
{
try
{
DataTable dt;
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
dt = dbc.GetDataTable(sql);
dbc.CloseConnection();
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static void AddNewCustomer(DataTable Customers)
{
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
string sqlCustomer = string.Empty;
foreach (DataRow dr in Customers.Rows)
{
try
{
sqlCustomer = string.Format("INSERT INTO Customer (CustomerName) VALUES ('{0}')", dr["CustomerName"].ToString());
dbc.ExecuteCommand(sqlCustomer);
}
catch (Exception ex) { }
}
dbc.CloseConnection();
}
public static void CreateUser(SystemUser user)
{
// Create Person
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
string sql = string.Format(SQLCommands.CreatePerson, user.PersonFirstName, user.PersonLastName);
int lastPersonId = dbc.ExecuteCommand(sql);
sql = string.Format(SQLCommands.CreateUser, user.UserName, user.UserPassword, lastPersonId);
int lastUserId = dbc.ExecuteCommand(sql);
foreach (string role in user.Roles)
{
sql = string.Format(SQLCommands.CreateRole, lastUserId, role);
dbc.ExecuteCommand(sql);
}
dbc.CloseConnection();
}
public override void Update()
{
try
{
if (!CheckIfAnyUndeletedTasksExists())
{
throw new Exception("You haven't entered any tasks, sorry you cannot close this job");
}
if (!CheckIfJobTypeIsNewSetupAndQATaskIsEntered())
{
throw new Exception("For 'DM-New setup' jobs You need to enter a QA entry, sorry you cannot close this job");
}
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
// string jobSQL = string.Empty;
string jobCommand = string.Empty;
//jobSQL = "UPDATE Job SET CustomerId={0},JobDescription='{1}',DocketNo='{2}',LotNo='{3}',LodgementDate='{4}',CampaignManagerId={5},ProgrammerId={6}," +
//"DocketReceivedDate='{7}',NoOfRecords={8},NoOfFiles={9},IsSOARequired={10},IsPresortRequired={11},IsAddressCleansingRequired={12},IsTitleCasingRequired={13}," +
//"JobStatusId={14}, JobTypeId={15}, JobCompletedDate='{16}',IsEDM={17},IsLabel={18},JobNotes='{19}' WHERE JobId = {20}";
jobCommand = string.Format(SQLCommands.CompleteJob, CurrentJob.CustomerId, CurrentJob.JobDescription.Replace("'", "''"), CurrentJob.DocketNo, CurrentJob.LotNo, CurrentJob.LodgementDate, CurrentJob.CampaignManagerId,
CurrentJob.ProgrammerId, CurrentJob.DocketReceivedDate, CurrentJob.NoOfRecords, CurrentJob.NoOfFiles, CurrentJob.IsSOARequired, CurrentJob.IsPresortRequired, CurrentJob.IsAddressCleansingRequired,
CurrentJob.IsTitleCasingRequired, CurrentJob.JobStatusId, CurrentJob.JobTypeId, DateTime.Now, CurrentJob.IsEDM, CurrentJob.IsLabel, CurrentJob.JobNotes.Replace("'", "''"), CurrentJob.JobId);
dbc.ExecuteCommand(jobCommand);
// Add Job Log
string activity = "Job Completed - " + DateTime.Now.ToString();
dbc.ExecuteCommand(string.Format(SQLCommands.InsertJobLog, CurrentJob.JobId, activity));
dbc.CloseConnection();
dbc.CloseConnection();
JobTaskData jobTaskdata = new JobTaskData();
jobTaskdata.UpdateJobTasks(CurrentJob.JobTasks);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public Job GetOneJob(int JobId)
{
string strSQL = string.Format(SQLCommands.SingleJob, JobId);
DataTable dt;
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
dt = dbc.GetDataTable(strSQL);
dbc.CloseConnection();
DataRow dr = dt.Rows[0];
Job job = new Job();
job.JobId = (int)dr["JobId"];
job.JobTypeId = (int)dr["JobTypeId"];
job.DocketNo = dr["DocketNo"].ToString();
job.LotNo = dr["LotNo"].ToString();
job.JobDescription = dr["JobDescription"].ToString();
job.JobNotes = dr["JobNotes"].ToString();
job.LodgementDate = (DateTime)dr["LodgementDate"];
job.DocketReceivedDate = (DateTime)dr["DocketReceivedDate"];
job.CustomerId = (int)dr["CustomerId"];
job.JobCreatedDate = (DateTime)dr["JobCreatedDate"];
job.NoOfRecords = (int)dr["NoOfrecords"];
job.NoOfFiles = (int)dr["NoOfFiles"];
job.CampaignManagerId = (int)dr["CampaignManagerId"];
job.ProgrammerId = (int)dr["ProgrammerId"];
job.JobStatusId = (int)dr["JobStatusId"];
job.IsAddressCleansingRequired = (bool)dr["IsAddressCleansingRequired"];
job.IsPresortRequired = (bool)dr["IsPresortRequired"];
job.IsSOARequired = (bool)dr["IsSOARequired"];
job.IsTitleCasingRequired = (bool)dr["IsTitleCasingRequired"];
job.IsEDM = (bool)dr["IsEDM"];
job.IsLabel = (bool)dr["IsLabel"];
dbc.CloseConnection();
JobTaskData jtd = new JobTaskData();
job.JobTasks = jtd.GetJobTaskDT(JobId);
return(job);
}
// CurrentJob = job;
//}
public override void Update()
{
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
string jobSQL = string.Empty;
string jobCommand = string.Empty;
jobCommand = string.Format(SQLCommands.UpdateJob, CurrentJob.CustomerId, CurrentJob.JobDescription.Replace("'", "''"), CurrentJob.DocketNo, CurrentJob.LotNo, CurrentJob.LodgementDate, CurrentJob.CampaignManagerId,
CurrentJob.ProgrammerId, CurrentJob.DocketReceivedDate, CurrentJob.NoOfRecords, CurrentJob.NoOfFiles, CurrentJob.IsSOARequired, CurrentJob.IsPresortRequired, CurrentJob.IsAddressCleansingRequired,
CurrentJob.IsTitleCasingRequired, 1, CurrentJob.JobTypeId, CurrentJob.IsEDM, CurrentJob.IsLabel, CurrentJob.JobNotes.Replace("'", "''"), CurrentJob.JobId);
dbc.ExecuteCommand(jobCommand);
// Add Job Log
string activity = "Job Updated to IN PROGRESS - " + DateTime.Now.ToString();
dbc.ExecuteCommand(string.Format(SQLCommands.InsertJobLog, CurrentJob.JobId, activity));
dbc.CloseConnection();
JobTaskData jobTaskdata = new JobTaskData();
jobTaskdata.UpdateJobTasks(CurrentJob.JobTasks);
}
public List <JobTask> GetJobTasks(int JobId)
{
string strSQL = string.Format("SELECT * FROM vw_JobTask WHERE JobId = {0}", JobId);
List <JobTask> jobTasks = new List <JobTask>();
Job Job = new Job();
DataTable dt;
SQLDBCommand dbc = new SQLDBCommand(SQLDBCommand.TransactionType.WithoutTransaction);
dt = dbc.GetDataTable(strSQL);
foreach (DataRow dr in dt.Rows)
{
JobTask jobTask = new JobTask();
jobTask.JobTaskId = (int)dr["JobTaskId"];
jobTask.JobId = (int)dr["JobId"];
jobTask.TaskId = (int)dr["TaskId"];
jobTask.HoursSpent = Convert.ToDecimal(dr["HoursSpent"]);
jobTask.Comments = dr["Comments"].ToString();
jobTasks.Add(jobTask);
}
dbc.CloseConnection();
return(jobTasks);
}
