private static List <string> GetInspurID(string[] users)
{
string qu = string.Empty;
List <string> list = new List <string>();
for (int i = 0; i < users.Length; i++)
{
qu += string.Format("'{0}',", users[i]);
}
qu = qu.TrimEnd(',');
string sql = "select inspur_id from users where inspur_id<>'' and id in (" + qu + ")";
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql))
{
while (reader.Read())
{
list.Add(reader.GetString(0));
}
}
return(list);
}
public SysUser GetUserByID(string id)
{
string sql = @"select a.id, a.inspur_id, a.code, a.full_name, a.name_global, a.mobile, a.email,
c.id org_id, c.name org_name, c.full_name org_full_name, c.full_path,c.parent_id,a.serial_number
from users a inner join r_org_user b on a.id = b.user_id inner JOIN organizations c on b.org_id = c.id where a.id ={0}";
SysUser user = default(SysUser);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, id))
{
if (reader.Read())
{
user = new SysUser();
user.ID = reader.GetString(0);
user.InspurID = reader.GetString(1);
user.Code = reader.GetString(2);
user.Name = reader.GetString(3);
user.GlobalName = reader.GetString(4);
user.Phone = reader.GetString(5);
user.Mail = reader.GetString(6);
user.SerialNumber = reader.GetString(12);
Organization org = new Organization()
{
ID = reader.GetString(7),
Name = reader.GetString(8),
FullName = reader.GetString(9),
FullPath = reader.GetString(10),
ParentID = reader.GetString(11)
};
}
}
return(user);
}
/// <summary>
/// 获取身份证号
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static string GetUserIdCardNmb(string userID)
{
string sql = @"select id_nmb from users where id={0}";
string nmb = string.Empty;
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, userID))
{
if (reader.Read())
{
nmb = reader.GetString(0);
}
}
return(nmb);
}
public List <string> GetUserIDsByOrgID(string orgID)
{
List <string> userIDList = new List <string>();
string sql = @"select user_id from r_org_user where org_id ={0}";
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, orgID))
{
while (reader.Read())
{
userIDList.Add(reader.GetString(0));
}
}
return(userIDList);
}
public static Stream GetPassportPic(string userId)
{
string sql = @"select picture from user_passport where user_id={0}";
Stream sm = default(Stream);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, userId))
{
if (reader.Read())
{
object obj = reader.GetValue(0);
sm = ToStream(obj);
}
}
return(sm);
}
/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public List <SysUser> QueryByUserName(string userName, string tenant_id)
{
List <SysUser> list = new List <SysUser>();
if (userName.Length < 2) //至少要输入两个字符。
{
return(list);
}
string sql = @"select a.id, a.inspur_id, a.code, a.full_name, a.name_global, a.mobile, a.email,
c.id org_id, c.name org_name, c.full_name org_full_name, c.full_path,c.parent_id,a.serial_number
from users a inner join r_org_user b on a.id = b.user_id inner JOIN organizations c on b.org_id = c.id where a.full_name like {0} and b.tenant_id={1}";
SysUser user = default(SysUser);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, string.Format("%{0}%", userName), tenant_id))
{
while (reader.Read())
{
user = new SysUser();
user.ID = Convert.ToString(reader["id"]);
user.InspurID = Convert.ToString(reader["inspur_id"]);
user.Code = Convert.ToString(reader["code"]);
user.Name = Convert.ToString(reader["full_name"]);
user.GlobalName = Convert.ToString(reader["name_global"]);
user.Phone = Convert.ToString(reader["mobile"]);
user.Mail = Convert.ToString(reader["email"]);
user.SerialNumber = Convert.ToString(reader["serial_number"]);
Organization org = new Organization()
{
ID = Convert.ToString(reader["org_id"]),
Name = Convert.ToString(reader["org_name"]),
FullName = Convert.ToString(reader["org_full_name"]),
FullPath = Convert.ToString(reader["full_path"]),
ParentID = Convert.ToString(reader["parent_id"])
};
user.org = org;
list.Add(user);
}
}
return(list);
}
/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public List <SysUser> QueryByUserName(string userName, string tenant_id, int page, int total)
{
List <SysUser> list = new List <SysUser>();
if (string.IsNullOrEmpty(userName)) //至少要输入两个字符。
{
throw new Exception("名称不能为空。");
}
string sql = @"select a.id, a.inspur_id, a.code, a.full_name, a.name_global, a.mobile, a.email,
c.id org_id, c.name org_name, c.full_name org_full_name, c.full_path,c.parent_id,a.serial_number
from users a inner join r_org_user b on a.id = b.user_id inner JOIN organizations c on b.org_id = c.id where a.full_name like {0} and b.tenant_id={1}";
SysUser user = default(SysUser);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, string.Format("%{0}%", userName), tenant_id))
{
while (reader.Read())
{
user = new SysUser();
user.ID = reader.GetString(0);
user.InspurID = reader.GetString(1);
user.Code = reader.GetString(2);
user.Name = reader.GetString(3);
user.GlobalName = reader.GetString(4);
user.Phone = reader.GetString(5);
user.Mail = reader.GetString(6);
user.SerialNumber = reader.GetString(12);
Organization org = new Organization()
{
ID = reader.GetString(7),
Name = reader.GetString(8),
FullName = reader.GetString(9),
FullPath = reader.GetString(10),
ParentID = reader.GetString(11)
};
list.Add(user);
}
}
return(list);
}
private static Organization GetOrg(string sql, params object[] objParams)
{
Organization org = default(Organization);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, objParams))
{
if (reader.Read())
{
org = new Organization()
{
ID = reader.GetString(0),
Name = reader.GetString(1),
FullName = reader.GetString(2),
FullPath = reader.GetString(3),
ParentID = reader.GetString(4)
};
}
}
return(org);
}
private static List <Organization> GetOrgs(string sql, params object[] objParams)
{
List <Organization> orgs = new List <Organization>();
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (var reader = db.ExcuteDataReader(sql, objParams))
{
while (reader.Read())
{
orgs.Add(new Organization()
{
ID = Convert.ToString(reader["id"]),
Name = Convert.ToString(reader["name"]),
FullName = Convert.ToString(reader["full_name"]),
FullPath = Convert.ToString(reader["full_path"]),
ParentID = Convert.ToString(reader["parent_id"])
});
}
}
return(orgs);
}
private static Organization GetOrg(string sql, params object[] objParams)
{
Organization org = default(Organization);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, objParams))
{
if (reader.Read())
{
org = new Organization()
{
ID = Convert.ToString(reader["id"]),
Name = Convert.ToString(reader["name"]),
FullName = Convert.ToString(reader["full_name"]),
FullPath = Convert.ToString(reader["full_path"]),
ParentID = Convert.ToString(reader["parent_id"])
};
}
}
return(org);
}
public List <string> GetInspurIdByUserId(List <string> users, string tenant_id)
{
if (users == null || users.Count < 1)
{
throw new Exception("用户标识列表为空");
}
string id = string.Join("','", users.ToArray());
string sql = $@"select a.inspur_id from users a inner join r_org_user b on a.id = b.user_id where b.tenant_id='{tenant_id}' and a.id in('{id}')";
PGDatabase db = PGDatabase.GetDatabase("sysdb");
List <string> list = new List <string>();
using (IDataReader reader = db.ExcuteDataReader(sql))
{
while (reader.Read())
{
string inspurID = reader.GetString(0);
list.Add(inspurID);
}
}
return(list);
}
/// <summary>
/// 根据用户ID获取护照信息
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static Passport GetPassport(string userId)
{
string sql = @"select id,user_id, number, picture, effect_date, type from user_passport where user_id={0}";
Passport ppt = default(Passport);
PGDatabase db = PGDatabase.GetDatabase("sysdb");
using (IDataReader reader = db.ExcuteDataReader(sql, userId))
{
if (reader.Read())
{
ppt = new Passport()
{
ID = reader.GetString(0),
UserID = reader.GetString(1),
Number = reader.GetString(2),
Picture = ToStream(reader.GetValue(3)),
EffectDate = reader.GetDateTime(4),
Type = reader.GetInt32(5)
};
}
}
return(ppt);
}