public UserLogin Login3(out string str_error, string mobile, string password)
{
UserLogin result = new UserLogin();
str_error = "";
string sql = "", error = "", temp = "";
int intTemp = 0;
DataTable dt;
try
{
sql = string.Format(@"select u.user_id, user_name, user_mobile, user_age, user_sex, user_photo from sys_user u
left join sys_user_role_relationship t1 on u.user_id = t1.user_id and t1.delete_mark = 0
left join sys_role r on t1.role_id = r.role_id and r.delete_mark = 0
where u.delete_mark = 0 and user_mobile = '{0}' and user_password = '******' and r.role_level >= {2}", mobile, password, ConfCenter.LoginAdminRoleLevel);
dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
result.id = Convert.ToInt32(dr[0]);
result.name = dr[1].ToString();
result.mobile = dr[2].ToString();
temp = dr[3].ToString();
if (int.TryParse(temp, out intTemp))
{
result.age = Convert.ToInt32(intTemp);
}
result.sex = dr[4].ToString();
result.photo = dr[5].ToString();
CheckUserPermission(ref result, out error);
if (error != "")
{
throw new Exception(error);
}
}
else
{
throw new Exception("用户/密码错误");
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("登录失败_" + mobile, "1009", ex.Message, ex.StackTrace);
}
return(result);
}
private Menu[] CheckRoleMenu(int[] roleIds, out string str_error)
{
str_error = "";
List <Menu> menus = new List <Menu>();
string sql = "";
string error = "";
DataTable dt;
try
{
sql = string.Format(@"select distinct m.* from sys_menu m, sys_role_menu_relationship t, sys_role r
where m.menu_id = t.menu_id and m.delete_mark = 0 and t.role_id = r.role_id
and t.delete_mark = 0 and r.delete_mark = 0 and r.role_id in({0}) and m.menu_system = 'web'
order by menu_parent,menu_order", string.Join(",", roleIds));
dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
foreach (DataRow dr in dt.Rows)
{
int parent = Convert.ToInt32(dr[6]);
if (parent == 0)
{
Menu menu = new Menu();
menu.id = Convert.ToInt32(dr[0]);
menu.name = dr[1].ToString();
menu.code = dr[2].ToString();
menu.url = dr[3].ToString();
menu.icon = dr[4].ToString();
menu.type = dr[5].ToString();
menu.parent = parent;
menu.system = dr[7].ToString();
menu.order = Convert.ToInt32(dr[8]);
menu.config = dr[9].ToString();
menu.path = dr[10].ToString();
menu.title = dr[11].ToString();
if (menu.type == "root")
{
menu.children = GetChildrenMenu(menu.id, dt.Rows);
}
menus.Add(menu);
}
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = "验证用户菜单信息失败";
SystemLog.WriteErrorLog("验证用户菜单信息失败", "1006", ex.Message, ex.StackTrace);
}
return(menus.ToArray());
}
private StatisticResult[] StatRegionData(out string str_error, int id)
{
str_error = "";
List <StatisticResult> results = new List <StatisticResult>();
string sql = "exec StatSubRegionData " + id + "," + ConfCenter.ImportantUserRoleLevel + "," + ConfCenter.AdministratorUserRoleLevel + "," + ConfCenter.LoginAdminRoleLevel;
string error = "";
try
{
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
StatisticResult result = new StatisticResult();
string name = dr[0].ToString();
string alias = dr[1].ToString();
int dCount = Convert.ToInt32(dr[2]);
int uCount = Convert.ToInt32(dr[3]);
int uCountL1 = Convert.ToInt32(dr[4]);
int uCountL2 = Convert.ToInt32(dr[5]);
int uCountL3 = Convert.ToInt32(dr[6]);
result.name = alias == "" ? name : alias;
result.data.Add("社区数目", dCount);
result.data.Add("用户总数", uCount);
result.data.Add("戒毒人员", uCountL1);
result.data.Add("社工", uCountL2);
result.data.Add("民警", uCountL3);
if (uCountL1 + uCountL2 + uCountL3 < uCount)
{
result.data.Add("其他", uCount - uCountL1 - uCountL2 - uCountL3);
}
results.Add(result);
}
}
else
{
throw new Exception("没有查到该行政区的数据");
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("统计行政区基本信息失败", "1031", ex.Message, ex.StackTrace);
}
return(results.ToArray());
}
private bool UpdateUrinalysisRecord(out string str_error, int id, int user2, string result, string remark, string photo, string state)
{
string sql = "";
string error = "";
int count = 0;
string where = "";
str_error = "";
try
{
if (!string.IsNullOrWhiteSpace(result))
{
where += ",urinalysis_result = '" + result + "'";
}
if (user2 != 0)
{
where += string.Format(",user_id2 = {0}", user2);
}
if (!string.IsNullOrWhiteSpace(remark))
{
where += string.Format(",urinalysis_remark = '{0}'", remark);
}
if (!string.IsNullOrWhiteSpace(photo))
{
where += string.Format(",urinalysis_photo = '{0}'", photo);
}
if (!string.IsNullOrWhiteSpace(state))
{
where += ",urinalysis_state = '" + state + "'";
}
if (where == "" || id == 0)
{
throw new Exception("参数传入错误");
}
where = "update_time = getdate()" + where;
sql = string.Format("update urinalysis_records set {0} where delete_mark = 0 and urinalysis_id = {1}", where, id);
count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (error == "" && count > 0)
{
return(true);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("更新尿检结果失败", "2902", ex.Message, ex.StackTrace);
}
return(false);
}
/// <summary>
/// 用户手机绑定
/// </summary>
/// <param name="str_error"></param>
/// <param name="mobile"></param>
/// <param name="pin"></param>
/// <returns></returns>
private bool UpdateUserPin(out string str_error, string mobile, string pin)
{
str_error = "";
try
{
string where = "";
string error = "";
string sql = string.Format("select user_pin_update_time from sys_user where delete_mark = 0 and user_mobile = '{0}'", mobile);
string d = DataBaseHelper.ExecuteScalar(sql, out error).ToString();
if (string.IsNullOrEmpty(d))
{
where += string.Format("user_pin = '{0}',user_pin_update_time = getdate()", pin);
}
else
{
DateTime date = DateTime.Parse(d);
if ((DateTime.Now - date).TotalDays <= 30)
{
throw new Exception("绑定手机30天内无法重复修改");
}
else
{
where += string.Format("user_pin = '{0}',user_pin_update_time = getdate()", pin);
}
}
sql = string.Format("update sys_user set {0} where delete_mark = 0 and user_mobile = '{1}'", where, mobile);
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (error == "")
{
if (count == 0)
{
throw new Exception("该用户不存在");
}
else
{
return(true);
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("更新用户设备码失败", "1002", ex.Message, ex.StackTrace);
}
return(false);
}
//private User[] QuerySubUserById(out string str_error, int userId)
//{
// List<User> result = new List<User>();
// str_error = "";
// string sql = "";
// string error = "";
// string where = "";
// DataTable dt;
// try
// {
// int roleId = ConfCenter.ImportantUserRoleID;
// if (roleId == 0)
// {
// throw new Exception("请检查重点人员角色ID配置");
// }
// sql = string.Format("exec QuerySubUserByUserID {0}, {1}", userId, roleId);
// dt = DataBaseHelper.ExecuteTable(sql, out error);
// if(error == "")
// {
// foreach(DataRow dr in dt.Rows)
// {
// User info = new User();
// info.id = Convert.ToInt32(dr[0]);
// info.name = dr[1].ToString();
// info.mobile = dr[3].ToString();
// info.age = Convert.ToInt32(dr[6]);
// info.sex = dr[7].ToString();
// info.photo = dr[8].ToString();
// info.pin = dr[9].ToString();
// result.Add(info);
// }
// }
// else
// {
// throw new Exception(error);
// }
// }
// catch(Exception ex)
// {
// str_error = ex.Message;
// SystemLog.WriteErrorLog("查询下属人员失败", "1004", ex.Message, ex.StackTrace);
// }
// return result.ToArray();
//}
private User[] QuerySubImportantUserById(out string str_error, int id)
{
List <User> users = new List <User>();
str_error = "";
string error = "";
try
{
string sql = string.Format(@"select r.role_level from sys_user u
left join sys_user_role_relationship t1 on t1.delete_mark = 0 and u.user_id = t1.user_id
left join sys_role r on t1.role_id = r.role_id and r.delete_mark = 0
where u.delete_mark = 0 and u.user_id = {0}", id);
int level = Convert.ToInt32(DataBaseHelper.ExecuteScalar(sql, out error));
if (level == ConfCenter.AdministratorUserRoleLevel)
{
sql = "select * from sys_user where delete_mark = 0 and user_parent = " + id;
}
else
{
sql = string.Format("exec QuerySubUserByUserID {0}, {1}", id, ConfCenter.ImportantUserRoleLevel);
}
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
foreach (DataRow dr in dt.Rows)
{
User info = new User();
info.id = Convert.ToInt32(dr[0]);
info.name = dr[1].ToString();
info.mobile = dr[3].ToString();
info.age = Convert.ToInt32(dr[6]);
info.sex = dr[7].ToString();
info.photo = dr[8].ToString();
info.pin = dr[9].ToString();
users.Add(info);
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("查询用户信息失败", "1004", ex.Message, ex.StackTrace);
}
return(users.ToArray());
}
public UserLogin Login2(out string str_error, string mobile, string code)
{
UserLogin result = new UserLogin();
str_error = "";
string sql = "";
string error = "";
string authCode = "";
DataTable dt;
try
{
sql = string.Format("select user_id, user_name, user_age, user_sex, user_photo, user_auth_code from sys_user where delete_mark = 0 and user_mobile = '{0}'", mobile);
dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
DataRow dr = dt.Rows[0];
result.id = Convert.ToInt32(dr[0]);
result.name = dr[1].ToString();
result.mobile = mobile;
result.age = Convert.ToInt32(dr[2]);
result.sex = dr[3].ToString();
result.photo = dr[4].ToString();
authCode = dr[5].ToString();
//CheckUserPermission(ref result, out error);
//token = GetToken(result.id, result.name, result.photo);
//result.token = token;
if (CheckIdentity(code, authCode, mobile))
{
CheckUserPermission(ref result, out error);
if (error != "")
{
throw new Exception(error);
}
result.token = GetToken(result.id, result.name, result.photo);
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("登录失败_" + mobile, "1009", ex.Message, ex.StackTrace);
}
return(result);
}
private Menu[] QueryMenus(out string str_error)
{
List <Menu> menus = new List <Menu>();
str_error = "";
try
{
string sql = string.Format(@"select * from sys_menu order by menu_parent, menu_order");
string error = "";
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
foreach (DataRow dr in dt.Rows)
{
int parent = Convert.ToInt32(dr[6]);
if (parent == 0)
{
Menu menu = new Menu();
menu.id = Convert.ToInt32(dr[0]);
menu.name = dr[1].ToString();
menu.code = dr[2].ToString();
menu.url = dr[3].ToString();
menu.icon = dr[4].ToString();
menu.type = dr[5].ToString();
menu.parent = parent;
menu.system = dr[7].ToString();
menu.order = Convert.ToInt32(dr[8]);
menu.config = dr[9].ToString();
menu.path = dr[10].ToString();
menu.title = dr[11].ToString();
if (menu.type == "root")
{
menu.children = GetChildrenMenu(menu.id, dt.Rows);
}
menus.Add(menu);
}
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("查询菜单失败", "1001", ex.Message, ex.StackTrace);
}
return(menus.ToArray());
}
public async void SendMail(int id, string mobile)
{
try
{
int nonce = new Random().Next(9999);
string timestamp = ((DateTime.Now.Ticks - new DateTime(1970, 1, 1, 0, 0, 0, 0).Ticks) / 10000).ToString();
string signature = SHA1_Encrypt(ConfCenter.MailAppSecret + nonce + timestamp);
string uri = "http://api.sms.ronghub.com/sendCode.json";
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Add("App-Key", ConfCenter.MailAppKey);
client.DefaultRequestHeaders.Add("Nonce", nonce.ToString());
client.DefaultRequestHeaders.Add("Timestamp", timestamp);
client.DefaultRequestHeaders.Add("Signature", signature);
//client.DefaultRequestHeaders.Add("Content-Type", "application/x-www-form-urlencoded");
HttpContent content = new FormUrlEncodedContent(new Dictionary <string, string>()
{
{ "mobile", mobile },
{ "templateId", "0W3eX9zqABL9w7iFSM8yN_" },
{ "region", "86" }
});
var response = await client.PostAsync(uri, content);
string responseString = await response.Content.ReadAsStringAsync();
RYResult result = JsonConvert.DeserializeObject <RYResult>(responseString);
if (result.code == 200)
{
string error = "";
UpdateUser(out error, id, "", "", "", "", 0, "", "", "", 0, result.sessionId);
if (error != "")
{
throw new Exception(error + "_" + mobile);
}
}
else
{
throw new Exception(responseString + " __" + mobile);
}
}
catch (Exception ex)
{
SystemLog.WriteErrorLog("发送短信失败", "1009", ex.Message, ex.StackTrace);
}
}
public string GetToken(int id, string name, string photo)
{
string token = "";
int nonce = new Random().Next(9999);
string timestamp = ((DateTime.Now.Ticks - new DateTime(1970, 1, 1, 0, 0, 0, 0).Ticks) / 10000).ToString();
string signature = SHA1_Encrypt(ConfCenter.MailAppSecret + nonce + timestamp);
string uri = "http://api-cn.ronghub.com/user/getToken.json";
string str_content = "userId=" + id + " &name=" + name + "&portraitUri=" + photo;
byte[] content = Encoding.UTF8.GetBytes(str_content);
try
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri);
request.Method = "Post";
request.Host = "api-cn.ronghub.com";
request.Headers.Add("App-Key", ConfCenter.MailAppKey);
request.Headers.Add("Nonce", nonce.ToString());
request.Headers.Add("Timestamp", timestamp);
request.Headers.Add("Signature", signature);
request.ContentType = "application/x-www-form-urlencoded";
request.ContentLength = content.Length;
Stream stream;
stream = request.GetRequestStream();
stream.Write(content, 0, content.Length);
stream.Close();
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
string strRtnHtml = reader.ReadToEnd();
reader.Close();
RYResult result = JsonConvert.DeserializeObject <RYResult>(strRtnHtml);
if (result.code == 200)
{
token = result.token;
}
}
catch (Exception ex)
{
SystemLog.WriteErrorLog("获取token失败", "1009", ex.Message, ex.StackTrace);
}
return(token);
}
private bool CheckIdentity(string code, string sessionId, string mobile)
{
int nonce = new Random().Next(9999);
string timestamp = ((DateTime.Now.Ticks - new DateTime(1970, 1, 1, 0, 0, 0, 0).Ticks) / 10000).ToString();
string signature = SHA1_Encrypt(ConfCenter.MailAppSecret + nonce + timestamp);
string uri = "http://api.sms.ronghub.com/verifyCode.json";
try
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri);
request.Method = "Post";
request.Host = "api.sms.ronghub.com";
request.Headers.Add("App-Key", ConfCenter.MailAppKey);
request.Headers.Add("Nonce", nonce.ToString());
request.Headers.Add("Timestamp", timestamp);
request.Headers.Add("Signature", signature);
request.ContentType = "application/x-www-form-urlencoded";
Stream stream;
stream = request.GetRequestStream();
byte[] content = Encoding.UTF8.GetBytes("sessionId=" + sessionId + "&code=" + code);
stream.Write(content, 0, content.Length);
stream.Close();
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
string strRtnHtml = reader.ReadToEnd();
reader.Close();
RYResult result = JsonConvert.DeserializeObject <RYResult>(strRtnHtml);
if (result.code == 200 && result.success == true)
{
return(true);
}
else
{
throw new Exception(strRtnHtml);
}
}
catch (Exception ex)
{
SystemLog.WriteErrorLog("短信验证失败_" + mobile, "1009", ex.Message, ex.StackTrace);
//throw new Exception(ex.Message);
}
return(false);
}
private Department UpdateDepartment(out string str_error, int id, string name, string alias, string code)
{
string where = "";
str_error = "";
Department dep = new Department();
try
{
if (!string.IsNullOrWhiteSpace(name))
{
where += string.Format(",dep_name = '{0}'", name);
}
if (!string.IsNullOrWhiteSpace(alias))
{
where += string.Format(",dep_alias = '{0}'", alias);
}
if (!string.IsNullOrWhiteSpace(code))
{
where += string.Format(",dep_code = '{0}'", code);
}
if (where == "" || id == 0)
{
throw new Exception("修改部门传入参数不正确");
}
where = where.Substring(1);
string sql = string.Format("update sys_dep set {0} where delete_mark = 0 and dep_id = {1}", where, id);
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (count > 0 && error == "")
{
dep.id = id;
dep.name = name;
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("修改部门失败", "1022", ex.Message, ex.StackTrace);
}
return(dep);
}
private Department AddDepartment(out string str_error, string name, int order, int parent, int level, string alias, string code)
{
str_error = "";
Department dep = new Department();
try
{
string sql = string.Format("select count(*) from sys_dep where delete_mark = 0 and dep_name = '{0}' and dep_parent = {1}", name, parent);
string error = "";
int count = Convert.ToInt32(DataBaseHelper.ExecuteScalar(sql, out error));
if (error == "")
{
if (count > 0)
{
throw new Exception("不能在同一部门下增加相同名称的子级部门");
}
else
{
sql = string.Format("insert into sys_dep(dep_name, dep_order, dep_parent, dep_level, dep_alias, dep_code, create_time, delete_mark) values('{0}', {1}, {2}, {3}, '{4}', '{5}', getdate(), 0)select @@IDENTITY", name, order, parent, level, alias, code);
count = Convert.ToInt32(DataBaseHelper.ExecuteScalar(sql, out error));
if (count > 0 && error == "")
{
dep.id = count;
dep.name = name;
dep.parent = parent;
dep.order = order;
dep.level = level;
dep.alias = alias;
dep.code = code;
//写入操作日志
}
else
{
throw new Exception(error);
}
}
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("添加部门失败", "1021", ex.Message, ex.StackTrace);
}
return(dep);
}
private string CountSignByDep(out string str_error, string regionCode, string startTime, string endTime)
{
string result = "";
str_error = "";
try
{
if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime))
{
startTime = DateTime.Now.Year + "-" + DateTime.Now.Month + "-1 0:0:0";
endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
string sql = "", error = "";
sql = string.Format("select count(*) from sys_dep a, sys_dep b where a.dep_parent = b.dep_id and b.dep_code = '{0}' and a.delete_mark = 0 and b.delete_mark = 0", regionCode);
int childrenCount = Convert.ToInt32(DataBaseHelper.ExecuteScalar(sql, out error));
if(error == "")
{
if (childrenCount == 0)
{
result = CountSignCommunity(regionCode, startTime, endTime);
}
else if(childrenCount > 0)
{
result = CountSignParent(regionCode, startTime, endTime);
}
else
{
throw new Exception("部门数据有误，无法统计");
}
}
else
{
throw new Exception(error);
}
}
catch(Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("统计签到信息失败", "11001", ex.Message, ex.StackTrace);
}
return result;
}
private string CountActiveUser(out string str_error, string regionCode, string startTime, string endTime)
{
string result = "";
str_error = "";
try
{
if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime))
{
startTime = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss");
endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
string sql = "", error = "";
sql = string.Format("exec CountActiveUser '{0}', '{1}', '{2}', {3}", regionCode, startTime, endTime, ConfCenter.ImportantUserRoleLevel);
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
List<StatisticForActiveUser> list = new List<StatisticForActiveUser>();
foreach (DataRow dr in dt.Rows)
{
StatisticForActiveUser info = new StatisticForActiveUser();
info.name = dr[0].ToString();
info.code = dr[1].ToString();
info.communityCount = Convert.ToInt32(dr[2]);
info.totalUserCount = Convert.ToInt32(dr[3]);
info.activeUserCount = Convert.ToInt32(dr[4]);
list.Add(info);
}
result = JsonConvert.SerializeObject(list);
}
else
{
throw new Exception(error);
}
}
catch(Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("统计装机信息失败", "11003", ex.Message, ex.StackTrace);
}
return result;
}
private Department[] QuerySubRegionByDepID(out string str_error, int id)
{
List <Department> deps = new List <Department>();
str_error = "";
string sql = "exec QuerySubRegionByDepID " + id;
string error = "";
try
{
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
Department dep = new Department();
dep.name = dr[0].ToString();
dep.code = dr[1].ToString();
dep.alias = dr[2].ToString();
deps.Add(dep);
}
}
else
{
throw new Exception("已无下属行政区数据");
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("查询下属行政区划失败", "1025", ex.Message, ex.StackTrace);
}
return(deps.ToArray());
}
private Department[] CheckUserDep(int userId, out string str_error)
{
str_error = "";
List <Department> deps = new List <Department>();
string sql = "";
string error = "";
DataTable dt;
try
{
sql = string.Format(@"select d.dep_id, d.dep_name, d.dep_parent, d.dep_order, d.dep_level, d.dep_code
from sys_dep d
left join sys_user_dep_relationship t on t.dep_id = d.dep_id and t.delete_mark = 0
left join sys_user u on u.user_id = t.user_id and u.delete_mark = 0
where d.delete_mark = 0 and u.user_id = {0} order by dep_order", userId);
dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
foreach (DataRow dr in dt.Rows)
{
Department dep = new Department();
dep.id = Convert.ToInt32(dr[0]);
dep.name = dr[1].ToString();
dep.parent = Convert.ToInt32(dr[2]);
dep.order = Convert.ToInt32(dr[3]);
dep.level = Convert.ToInt32(dr[4]);
dep.code = dr[5].ToString();
deps.Add(dep);
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = "验证用户部门信息失败";
SystemLog.WriteErrorLog("验证用户部门信息失败", "1007", ex.Message, ex.StackTrace);
}
return(deps.ToArray());
}
public bool AddNews(out string str_error, string title, string content, int user, string type, string photo, int order, int top, int dep)
{
str_error = "";
string sql;
try
{
string temp = "";
for (int i = 0; i < content.Length && temp.Length < 50; i++)
{
if (content[i] == '<')
{
i = content.IndexOf('>', i);
}
else
{
temp += content[i];
}
}
sql = string.Format(@"insert into
activity_news(news_title, news_content, news_user, news_dep, news_type, news_photo, news_order, news_top, create_time, delete_mark, news_html)
values('{0}', '{1}', {2}, {7},'{3}', '{4}', {5}, {6}, getdate(), 0, '{8}')", title, temp, user, type, photo, order, top, dep, content);
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (count > 0 && error == "")
{
//写入操作日志
return(true);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("新增新闻信息失败", "1104", ex.Message, ex.StackTrace);
}
return(false);
}
private Role[] CheckUserRole(int userId, out string str_error)
{
str_error = "";
List <Role> roles = new List <Role>();
string sql = "";
string error = "";
DataTable dt;
try
{
sql = string.Format(@"select r.role_id, r.role_code, r.role_name, r.role_level, r.role_order
from sys_role r
left join sys_user_role_relationship t on r.role_id = t.role_id and t.delete_mark = 0
left join sys_user u on u.user_id = t.user_id and u.delete_mark = 0
where r.delete_mark = 0 and u.user_id = {0} order by role_order", userId);
dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
foreach (DataRow dr in dt.Rows)
{
Role role = new Role();
role.id = Convert.ToInt32(dr[0]);
role.code = dr[1].ToString();
role.name = dr[2].ToString();
role.level = Convert.ToInt32(dr[3]);
role.order = Convert.ToInt32(dr[4]);
roles.Add(role);
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = "验证用户角色信息失败";
SystemLog.WriteErrorLog("验证用户角色信息失败", "1008", ex.Message, ex.StackTrace);
}
return(roles.ToArray());
}
public bool UpdateFence(out string str_error, int id, string name, string extent)
{
str_error = "";
try
{
string where = "";
if (name != "")
{
where += string.Format(" and fence_name = '{0}'", name);
}
if (extent != "")
{
where += string.Format(" and fence_extent = '{1}'", extent);
}
string sql = string.Format("update electronic_fence set {1} where fence_id = {0}", id, where);
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (error == "")
{
if (count > 0)
{
return(true);
}
else
{
throw new Exception("语句执行成功但编辑失败");
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("围栏编辑失败", "1502", ex.Message, ex.StackTrace);
}
return(false);
}
private Department QueryRegionByDepID(out string str_error, int id)
{
Department dep = new Department();
str_error = "";
string sql = "exec QueryRegionByDepID " + id;
string error = "";
try
{
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
dep.id = Convert.ToInt32(dr[0]);
dep.code = dr[1].ToString();
dep.name = dr[2].ToString();
dep.parent = Convert.ToInt32(dr[3]);
}
else
{
throw new Exception("没有查询到相应行政区数据");
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("查询所属行政区划失败", "1024", ex.Message, ex.StackTrace);
}
return(dep);
}
private bool DeleteMenu()
{
try
{
string sql = string.Format("delete from sys_menu where menu_id = {0}");
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (count > 0 && error == "")
{
//写入操作日志
return(true);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
SystemLog.WriteErrorLog("删除菜单失败", "1001", ex.Message, ex.StackTrace);
}
return(false);
}
private bool AddMenu(string name, string config, int order)
{
try
{
string sql = string.Format("insert into sys_menu values()");
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (count > 0 && error == "")
{
//写入操作日志
return(true);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
SystemLog.WriteErrorLog("添加菜单失败", "1001", ex.Message, ex.StackTrace);
}
return(false);
}
private int Login1(out string str_error, string mobile, string pin)
{
str_error = "";
string sql = "";
string error = "";
int id = 0;
try
{
sql = string.Format("ValidUser '{0}', '{1}', {2}", mobile, pin, ConfCenter.ImportantUserRoleLevel);
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
DataRow dr = dt.Rows[0];
error = dr[0].ToString();
id = Convert.ToInt32(dr[1]);
if (error == "" && id != 0)
{
SendMail(id, mobile);
}
else
{
throw new Exception(error);
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("登录失败_" + str_error + "_" + mobile + "_" + pin, "1009", ex.Message, ex.StackTrace);
}
return(id);
}
private bool UpdateRole(int id, string name, int level)
{
try
{
string where = "";
if (!string.IsNullOrWhiteSpace(name))
{
where += ",role_name = '" + name + "'";
}
if (level != 0)
{
where += ",role_level = " + level;
}
if (where == "" || id == 0)
{
throw new Exception("修改角色参数传入不正确");
}
where = where.Substring(1);
string sql = string.Format("update sys_role set {0} where delete_mark = 0 and role_id = {1}", where, id);
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (count > 0 && error == "")
{
//写入操作日志
return(true);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
SystemLog.WriteErrorLog("编辑角色失败", "1012", ex.Message, ex.StackTrace);
}
return(false);
}
public ZTreeNode[] QueryRelatedDep(out string str_error, int id)
{
List <ZTreeNode> list = new List <ZTreeNode>();
str_error = "";
try
{
string sql = string.Format(@"select d.dep_id, d.dep_name, d.dep_parent, e.fence_id from
sys_dep d left join dep_fence_relationship t on d.dep_id = t.dep_id
left join electronic_fence e on t.fence_id = e.fence_id and e.fence_id = {0} where d.delete_mark = 0", id);
string error = "";
DataTable dt = DataBaseHelper.ExecuteTable(sql, out error);
if (error == "")
{
foreach (DataRow dr in dt.Rows)
{
ZTreeNode node = new ZTreeNode();
node.id = Convert.ToInt32(dr[0]);
node.name = dr[1].ToString();
node.parent = Convert.ToInt32(dr[2]);
node.isChecked = dr[3].ToString() == "" ? false : true;
list.Add(node);
}
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("围栏关联部分信息查询失败", "1505", ex.Message, ex.StackTrace);
}
return(list.ToArray());
}
public string[] QueryHomepagePicture(out string str_error, string type)
{
List <string> result = new List <string>();
str_error = "";
try
{
string dir_path = AppHome.Download + "homepage";
if (!string.IsNullOrEmpty(type))
{
dir_path += "-" + type;
}
if (Directory.Exists(dir_path))
{
string[] file_path = Directory.GetFileSystemEntries(dir_path);
for (int i = 0; i < file_path.Length; i++)
{
string t = file_path[i];
if (t.IndexOf(".jpg") >= 0 || t.IndexOf(".png") >= 0)
{
t = t.Substring(AppHome.BaseDirectory.Length - 1);
t = t.Replace("\\", "/");
result.Add(t);
}
}
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("查询轮播图失败", "2914", ex.Message, ex.StackTrace);
}
return(result.ToArray());
}
private bool DeleteVideo(out string str_error, int id)
{
str_error = "";
try
{
string sql = string.Format("update video_records set delete_mark = 1 where video_id = {0}", id);
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (error == "" && count > 0)
{
return(true);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("删除视频失败", "1043", ex.Message, ex.StackTrace);
}
return(false);
}
private bool AddVideo(out string str_error, string title, string uploader, string url, int dep = 0)
{
str_error = "";
try
{
string sql = string.Format("insert into video_records values('{0}', '{1}', {2}, '{3}', getdate(), 0)", title, uploader, dep, url);
string error = "";
int count = DataBaseHelper.ExecuteNonQuery(sql, out error);
if (error == "" && count > 0)
{
return(true);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
str_error = ex.Message;
SystemLog.WriteErrorLog("添加视频失败", "1041", ex.Message, ex.StackTrace);
}
return(false);
}
private int NoLogin(out string str_error, string mobile)
{
str_error = "";
string sql = "", error = "";
try
{
sql = string.Format("select user_id from sys_user where user_mobile = '{0}' and delete_mark = 0", mobile);
int id = Convert.ToInt32(DataBaseHelper.ExecuteScalar(sql, out error));
if (error == "")
{
return(id);
}
else
{
throw new Exception(error);
}
}
catch (Exception ex)
{
SystemLog.WriteErrorLog("获取用户id失败_" + mobile, "1010", ex.Message, ex.StackTrace);
}
return(0);
}
