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); }