/// <summary>
/// update a job
/// </summary>
/// <param name="strsql">sql sentence</param>
/// <returns>0 or 1</returns>
public static int ModifyJob(JobInfo job)
{
//JobInfo origin= GetJobById(job.id);
const string DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
StringBuilder sb = new StringBuilder();
sb.Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[0], (job.closedate == null) ? "" : job.closedate.ToString(DATE_FORMAT)));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[1], job.createdate.ToString(DATE_FORMAT)));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[2], job.carNo));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[3], (job.desk == null) ? "" : "" + job.desk.UserId));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[4], (job.manager == null) ? "" : "" + job.manager.UserId));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[5], (job.worker == null) ? "" : "" + job.worker.UserId));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[6], (job.jobDescription == null) ? "" : job.jobDescription));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[7], (int)job.resolve));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[8], (int)job.priority));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[9], (job.comment == null) ? "" : job.comment));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[10], (job.outlet == null) ? "" : "" + job.outlet.Id));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[11], (job.customer == null) ? "" : "" + job.customer.UserId));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[12], (int)job.jobStatus));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[13], (job.opendate == null) ? "" : job.assigndate.ToString(DATE_FORMAT)));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[14], (job.assigndate == null) ? "" : job.assigndate.ToString(DATE_FORMAT)));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[15], ((int)job.jobType)));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[16], (job.jobHistory == null) ? "" : job.jobHistory));
string strsql = string.Format("UPDATE jobs SET {0} WHERE id = {1}", sb.ToString(), job.id);
return(SqliteHelper.ExecuteNoneQuery(strsql));
}
/// <summary>
/// confirm a job
/// </summary>
/// <param name="strsql">sql sentence</param>
/// <returns>0 or 1</returns>
public static int confirmById(JobInfo job)
{
string strsql = string.Format("UPDATE `jobs` SET `desk`=0,`manager`=0 WHERE id = 2", job.desk, job.manager);
return(SqliteHelper.ExecuteNoneQuery(strsql));
}
/// <summary>
/// add a job
/// </summary>
/// <param name="strsql">sql sentence</param>
/// <returns>0 or 1</returns>
public static int AddJob(JobInfo job)
{
const string DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
StringBuilder sb = new StringBuilder();
sb.Append("'").Append((job.closedate == null) ? "" : job.closedate.ToString(DATE_FORMAT)).Append("',");
sb.Append("'").Append(job.createdate.ToString(DATE_FORMAT)).Append("',");
sb.Append("'").Append(job.carNo).Append("',");
sb.Append("'").Append((job.desk == null)?"":"" + job.desk.UserId).Append("',");
sb.Append("'").Append((job.manager == null)?"":"" + job.manager.UserId).Append("',");
sb.Append("'").Append((job.worker == null)?"":"" + job.worker.UserId).Append("',");
sb.Append("'").Append((job.jobDescription == null) ? "" : job.jobDescription).Append("',");
sb.Append("'").Append((int)job.resolve).Append("',");
sb.Append("'").Append((int)job.priority).Append("',");
sb.Append("'").Append((job.comment == null) ? "" : job.comment).Append("',");
sb.Append("'").Append((job.outlet == null) ? "" : "" + job.outlet.Id).Append("',");
sb.Append("'").Append((job.customer == null)?"":"" + job.customer.UserId).Append("',");
sb.Append("'").Append((int)job.jobStatus).Append("',");
sb.Append("'").Append((job.opendate == null) ? "" : job.assigndate.ToString(DATE_FORMAT)).Append("',");
sb.Append("'").Append((job.assigndate == null)?"":job.assigndate.ToString(DATE_FORMAT)).Append("',");
sb.Append("'").Append((int)job.jobType).Append("',");
sb.Append("'").Append((job.jobHistory == null) ? "" : job.jobHistory).Append("'");
//JobStatus foo = (JobStatus)Enum.ToObject(typeof(JobStatus) , yourInt);
string strsql = string.Format("INSERT INTO jobs ({0}) VALUES ({1})", string.Join(",", JobInfo.COLUMNS_NO_ID), sb.ToString());
return(SqliteHelper.ExecuteNoneQuery(strsql));
}
/// <summary>
/// delete a job
/// </summary>
/// <param name="strsql">sql sentence</param>
/// <returns>0 or 1</returns>
public static int DelJob(int id)
{
string strsql = "delete from jobs where id=" + id.ToString();
string strsql1 = "delete from job_history where jobid=" + id.ToString();
SqliteHelper.ExecuteNoneQuery(strsql);
return(SqliteHelper.ExecuteNoneQuery(strsql1));
}
public static int closeByWorker(JobInfo job)
{
const string DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
StringBuilder sb = new StringBuilder();
sb.Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[0], (job.closedate == null) ? "" : job.closedate.ToString(DATE_FORMAT)));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[7], (int)job.resolve));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[9], (job.comment == null) ? "" : job.comment));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[12], (int)job.jobStatus));
string strsql = string.Format("UPDATE jobs SET {0} WHERE id = {1}", sb.ToString(), job.id);
return(SqliteHelper.ExecuteNoneQuery(strsql));
}
/// <summary>
/// assign a job
/// </summary>
/// <param name="strsql">sql sentence</param>
/// <returns>0 or 1</returns>
public static int assign(JobInfo job)
{
const string DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
StringBuilder sb = new StringBuilder();
sb.Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[5], (job.worker == null) ? "" : "" + job.worker.UserId));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[8], (int)job.priority));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[12], (int)job.jobStatus));
sb.Append(", ").Append(string.Format(SQL_COLUMN_VALUE_FMT, JobInfo.COLUMNS_NO_ID[14], (job.assigndate == null) ? "" : job.assigndate.ToString(DATE_FORMAT)));
string strsql = string.Format("UPDATE jobs SET {0} WHERE id = {1}", sb.ToString(), job.id);
return(SqliteHelper.ExecuteNoneQuery(strsql));
}
/// <summary>
/// set a job's priority
/// </summary>
/// <param name="strsql">sql sentence</param>
/// <returns>0 or 1</returns>
public static int setPriority(JobInfo job)
{
string strsql = string.Format("UPDATE `jobs` SET `priority`=0, WHERE id = 2", job.priority);
return(SqliteHelper.ExecuteNoneQuery(strsql));
}
/// <summary>
/// close a job by Desk
/// </summary>
/// <param name="strsql">sql sentence</param>
/// <returns>0 or 1</returns>
public static int closeByDesk(JobInfo job)
{
string strsql = string.Format("UPDATE `jobs` SET `desk`=0 WHERE id = 2", job.desk.UserId);
return(SqliteHelper.ExecuteNoneQuery(strsql));
}
