public Boolean Login(String userName, String password)
{
string sql = @"SELECT [user.account] ,[user.password] ,[user.category_id] ,[category.description] as role FROM [user] INNER JOIN [category] ON user.category_id=category.id" +
" WHERE account='" + userName + "' AND password='******'";
ContentProvider provider = ContentProvider.getInstance();
DataTable dt = provider.query(sql);
mCurrentUserInfo = GetCurrentUserInfo();
mCurrentUserInfo.IsLogin = false;
if (dt.Rows.Count > 0)
{
mCurrentUserInfo.IsLogin = true;
DataRow row = dt.Rows[0];
mCurrentUserInfo.Account = row["user.account"].ToString();
mCurrentUserInfo.Role = row["role"].ToString();
int level;
int.TryParse(row["user.category_id"].ToString(), out level);
mCurrentUserInfo.UserLevel = level;
String description = String.Format("用户 {0} 登陆", mCurrentUserInfo.Account);
Log.write(Log.CATEGOTY_LOGIN, description);
NotifyObservers(PageLogin.LOGIN);
}
return(mCurrentUserInfo.IsLogin);
}
public static List <Journal> getAllLogs(String filter)
{
String where = "";
if (!String.IsNullOrEmpty(filter))
{
where = " where " + filter;
}
string sql = @"SELECT [category.description] as category,[log.description] as description,[when] FROM [log] INNER JOIN [category] ON log.category_id=category.id"
+ where + " ORDER BY [when] desc";
ContentProvider provider = ContentProvider.getInstance();
DataTable dt = provider.query(sql);
List <Journal> logs = new List <Journal>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
Journal log = new Journal();
log.when = row["when"].ToString();
log.category = row["category"].ToString();
log.description = row["description"].ToString();
logs.Add(log);
}
return(logs);
}
public Boolean ResetPassword(String account, String password)
{
string sql = @"UPDATE [user] SET [password] = '" + password + "' WHERE [account]='" + account + "'";
ContentProvider provider = ContentProvider.getInstance();
int rowAffected = provider.update(sql);
return(rowAffected > 0);
}
public Boolean deleteUser(String account)
{
string sql = @"DELETE FROM [user] WHERE account='" + account + "'";
ContentProvider provider = ContentProvider.getInstance();
int rowAffected = provider.delete(sql);
return(rowAffected > 0);
}
public Boolean registerUser(String account, String password, int level)
{
string sql = @"INSERT INTO [user] ([account],[password],[category_id]) VALUES('" + account + "','" + password + "'," + level + ")";
ContentProvider provider = ContentProvider.getInstance();
int rowAffected = provider.insert(sql);
return(rowAffected > 0);
}
//public class Record
//{
// public int index{get;set;}
// public String category{get;set;}
// public string description { get; set; }
// public string code { get; set; }
// public DateTime whenhappened { get; set; }
// public DateTime whenresolved { get; set; }
//}
//record infomation runtime
public static int write(WarnningDataSource.ErrorInfo info)
{
ContentProvider provider = ContentProvider.getInstance();
string sql = String.Format(@"INSERT INTO [warnning] ([level],[category_id],[code],[description],[whenhappened] )
VALUES({0},{1},'{2}','{3}','{4}')", info.level, info.category_id, info.code, info.description, DateTime.Now.ToString());
int rowAffected = provider.insert(sql);
return(rowAffected);
}
//record infomation runtime
public static int write(int category, string description)
{
String time = DateTime.Now.ToString();
string sql = @"INSERT INTO [log] ([when],[category_id],[description]) VALUES('" + DateTime.Now.ToString() + "'," + category + ",'" + description + "')";
ContentProvider provider = ContentProvider.getInstance();
int rowAffected = provider.insert(sql);
return(rowAffected);
}
public static List <Category> GetCategories(String filter)
{
string sql = @"SELECT [id],[description] FROM [category] where table='" + filter + "'";
ContentProvider provider = ContentProvider.getInstance();
DataTable dt = provider.query(sql);
List <Category> cats = new List <Category>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
Category cat = new Category();
cat.id = int.Parse(row["id"].ToString());
cat.description = row["description"].ToString();
cats.Add(cat);
}
return(cats);
}
public List <Category> getUserRoleList()
{
string sql = @"SELECT id,description FROM [category] where table='user'";
ContentProvider provider = ContentProvider.getInstance();
DataTable dt = provider.query(sql);
List <Category> userroles = new List <Category>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
Category role = new Category();
role.id = int.Parse(row["id"].ToString());
role.description = row["description"].ToString();
userroles.Add(role);
}
return(userroles);
}
public static int resolve(WarnningDataSource.ErrorInfo info)
{
ContentProvider provider = ContentProvider.getInstance();
String sqlQuery = String.Format(@"SELECT [_id],[whenresolved]FROM [warnning] where level={0} ORDER BY [whenhappened] desc", info.level);
DataTable dt = provider.query(sqlQuery);
int rowAffected = 0;
foreach (DataRow row in dt.Rows)
{
string whenresolved = row["whenresolved"].ToString();
if (String.IsNullOrEmpty(whenresolved))
{
string id = row["_id"].ToString();
string sql = String.Format(@"UPDATE [warnning] SET [whenresolved]='{0}' where [_id]={1} ", DateTime.Now.ToString(), id);
rowAffected = provider.update(sql);
break;
}
}
return(rowAffected);
}
public static List <WarnningDataSource.ErrorInfo> getAllRecords(String filter)
{
String where = "";
if (!String.IsNullOrEmpty(filter))
{
where = " where " + filter;
}
string sql = @"SELECT [level] ,
[code] ,
[category.description] as category,
[warnning.description] as description,
[whenhappened] ,
[whenresolved]
FROM [warnning] INNER JOIN [category] ON warnning.category_id=category.id"
+ where + " ORDER BY [whenhappened] desc ";
ContentProvider provider = ContentProvider.getInstance();
DataTable dt = provider.query(sql);
List <WarnningDataSource.ErrorInfo> records = new List <WarnningDataSource.ErrorInfo>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
WarnningDataSource.ErrorInfo record = new WarnningDataSource.ErrorInfo();
record.level = int.Parse(row["level"].ToString());
record.category = row["category"].ToString();
record.description = row["description"].ToString();
record.whenhappened = row["whenhappened"].ToString();
record.whenresolved = row["whenresolved"].ToString();
record.code = row["code"].ToString();
records.Add(record);
}
return(records);
}
public List <Info> getUsersInfo()
{
// string sql = @"SELECT [category.id] as level, [category.description] as role ,[account],[password] FROM [user] INNER JOIN [category] ON user.category_id=category.id";
string sql = @"SELECT [user.account] ,[user.password] ,[user.category_id] ,[category.description] "
+ " FROM [user] INNER JOIN [category] ON user.category_id=category.id";
ContentProvider provider = ContentProvider.getInstance();
DataTable dt = provider.query(sql);
List <Info> userroles = new List <Info>();
foreach (DataRow row in dt.Rows)
{
Info info = new Info();
info.UserLevel = int.Parse(row["user.category_id"].ToString());
info.Role = row["category.description"].ToString();
info.Account = row["user.account"].ToString();
info.Password = row["user.password"].ToString();
userroles.Add(info);
}
return(userroles);
}