public static IList <JobStatistic> GetJobStatistic()
{
IList <JobStatistic> totailwork = new List <JobStatistic>();
string sql = "SELECT COUNT(id), worker FROM jobs WHERE jobStatus = 1 group by worker";
DataTable dt = SqliteHelper.ExecuteTable(sql);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
JobStatistic js = new JobStatistic();
string workerId = dt.Rows[i][1].ToString();
if (workerId != null && workerId.Length > 0)
{
js.Worker = Sys_roleService.GetUserByiId(int.Parse(workerId));
}
js.ClosedJob = Convert.ToInt32(dt.Rows[i][0]);
totailwork.Add(js);
}
}
return(totailwork);
}
public static IList <JobInfo> GetJobsBySQL(string strsql)
{
IList <JobInfo> jobs = new List <JobInfo>();
DataTable dt = SqliteHelper.ExecuteTable(strsql);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
//to do: some fiels have been seperated from jobs
JobInfo job = new JobInfo();
job.id = dt.Rows[i]["id"].ToString();
job.carNo = dt.Rows[i]["carNo"].ToString();
string customerId = dt.Rows[i]["customer"].ToString();
if (customerId != null && customerId.Length > 0)
{
job.customer = Sys_roleService.GetUserByiId(int.Parse(customerId));
}
job.jobDescription = dt.Rows[i]["jobDescription"].ToString();
try
{
job.createdate = DateTime.Parse(dt.Rows[i]["createdate"].ToString());
}
catch
{
job.createdate = DateTime.Now;
}
job.jobStatus = (JobStatus)int.Parse(dt.Rows[i]["jobStatus"].ToString());
job.priority = (Priority)int.Parse(dt.Rows[i]["priority"].ToString());
string type = dt.Rows[i]["jobType"].ToString();
if (type != null && type.Length > 0)
{
job.jobType = (JobType)int.Parse(type);
}
string resolve = dt.Rows[i]["resolve"].ToString();
if (resolve != null && resolve.Length > 0)
{
job.resolve = (ResolveStatus)int.Parse(resolve);
}
//outer key
string deskId = dt.Rows[i]["desk"].ToString();
if (deskId != null && deskId.Length > 0)
{
job.desk = Sys_roleService.GetUserByiId(int.Parse(deskId));
}
string managerId = dt.Rows[i]["manager"].ToString();
if (managerId != null && managerId.Length > 0)
{
job.manager = Sys_roleService.GetUserByiId(int.Parse(managerId));
}
string workerId = dt.Rows[i]["worker"].ToString();
if (workerId != null && workerId.Length > 0)
{
job.worker = Sys_roleService.GetUserByiId(int.Parse(workerId));
}
//add outlet key
string outletID = dt.Rows[i]["outlet"].ToString();
if (outletID != null && outletID.Length > 0)
{
job.outlet = sys_outletService.GetOutletbyotID(int.Parse(outletID));
}
try
{
job.closedate = DateTime.Parse(dt.Rows[i]["closedate"].ToString());
}
catch
{
job.closedate = DateTime.Now;
}
job.comment = dt.Rows[i]["comment"].ToString();
jobs.Add(job);
}
}
return(jobs);
}