/// <summary> /// This function is search in database by user acount number /// check user name and password is correct, retrun is object /// </summary> /// <param name="loginName">Lgoin account</param> /// <returns>UserInfo object</returns> //fuction for check login success public UserInfor IsLoginByLoginName(string loginName) { string sql = "select * from user where user_name=@user_name"; //string sql = "select * from user"; //return a data table DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@user_name", loginName)); UserInfor user = null; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { user = RowToUserInfo(dr); } } return(user); }
/// <summary> /// get all the users /// </summary> /// <param name="strsql"></param> /// <returns></returns> private static IList <UserInfor> GetUsersBySQL(string strsql) { IList <UserInfor> users = new List <UserInfor>(); DataTable dt = SqliteHelper.ExecuteTable(strsql); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { UserInfor user = new UserInfor(); user.UserId = int.Parse(dt.Rows[i]["userId"].ToString()); user.UserName = dt.Rows[i]["user_name"].ToString(); users.Add(user); } } return(users); }
public List <ReportInfo> GetUserReports(int userid) { string sql = "SELECT * FROM jobs LEFT JOIN user ON jobs.customer = user.userId WHERE userId = @userId"; DataTable dt = SqliteHelper.ExecuteTable(sql, new SQLiteParameter("@userId", userid)); List <ReportInfo> list = new List <ReportInfo>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { ReportInfo userepor = RowToReport(dr); list.Add(userepor); } } return(list); }
/// <summary> /// Get outlet talbe data and change it to object /// </summary> /// <param name="outletsql">pass the sql query in fucntion</param> /// <returns></returns> public static IList <Outlet> GetOutlets(string outletsql) { IList <Outlet> outlets = new List <Outlet>(); DataTable dt = SqliteHelper.ExecuteTable(outletsql); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { Outlet outlet = new Outlet(); outlet.Id = Convert.ToInt32(dt.Rows[i]["otID"]); outlet.Name = dt.Rows[i]["otName"].ToString(); outlet.Address = dt.Rows[i]["otAddress"].ToString(); outlets.Add(outlet); } } return(outlets); }
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); }