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