public DataTable GetProcessNodeListWithDataAuthorization(string ProcessCode, int congID)
{
string SQL = string.Format(@"SELECT distinct
P.ID AS ProcessID,
P.ProcessCode,
P.ProcessName,
PN.ID AS NodeID,
PN.NodeName,
PN.NodeSeq,
PN.NodeType,
PN.RoleID,
R.Name AS RoleName,
UR.UserID,U.WD_UserID,
U.LoginName AS LoginName,
U.Name AS UserName
FROM [LWF_Process] P WITH(NOLOCK)
INNER JOIN LWF_ProcessNode PN
ON PN.ProcessID=P.ID
INNER JOIN PB_ROLEINFO R WITH(NOLOCK)
ON R.ID=PN.RoleID
LEFT JOIN PA_UserRole UR WITH(NOLOCK)
ON UR.RoleID=R.ID and UR.IsDeleted<1
LEFT JOIN PB_Userinfo U WITH(NOLOCK)
ON UR.UserID=U.ID and U.IsDeleted<1
WHERE ProcessCode='{0}' and R.ScopeID={1} and P.CongID={1} and P.IsDeleted<1 and PN.IsDeleted<1
ORDER BY PN.NodeSeq
", SqlTextHelper.SafeQuote(ProcessCode), congID);
return(ExecuteReturnTable(SQL));
}
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="keyword">用户名称</param>
/// <param name="deptName">部门名称</param>
/// <param name="jobName">岗位名称</param>
/// <returns></returns>
public PartlyCollection <WD_User> GetSearchOrgUsers(string keyword, string deptName, string jobName)
{
PartlyCollection <WD_User> result = new PartlyCollection <WD_User>();
string sqlCommand = string.Empty;
{
sqlCommand = string.Format(@"SELECT DISTINCT TOP(100) b.employeeCode as Wd_UserID,b.username as LoginName,b.employeeName as Name,
b.orgName as OrgName,b.orgID as Wd_OrgID,b.jobID as JobID,b.joinUnitDate as StartTime, NULL as EndTime,
b.jobName as JobTitle
FROM dbo.Wd_User b
WHERE (b.username LIKE '%{0}%' or b.employeeName LIKE '%{0}%')
and b.unitName LIKE '%{1}%'
and b.jobName LIKE '%{2}%' and b.employeeStatus='2'",
string.IsNullOrEmpty(keyword) == false ? SqlTextHelper.SafeQuote(keyword) : keyword,
string.IsNullOrEmpty(deptName) == false ? SqlTextHelper.SafeQuote(deptName) : deptName,
string.IsNullOrEmpty(jobName) == false ? SqlTextHelper.SafeQuote(jobName) : jobName);
DataSet ds = DbHelper.RunSqlReturnDS(sqlCommand, ConnectionName);
if (ds != null)
{
DataTable table = ds.Tables[0];
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
WD_User view = new WD_User();
ORMapping.DataRowToObject <WD_User>(row, view);
result.Add(view);
}
}
}
}
return(result);
}
/// <summary>
/// 获取用户详细信息根据用户名
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public BUserinfo GetWDUserInfoByUserName(string userName)
{
string sql = @"
SELECT
employeeName,
username,
unitName,
jobName,
gender,
mobile,
employeeStatus as Status ,
employeeCode
FROM dbo.WD_User
where username='******'";
DataTable dt = ExecuteReturnTable(sql);
BUserinfo model = null;
if (dt != null)
{
model = new BUserinfo();
model.Name = !string.IsNullOrEmpty(dt.Rows[0][0].ToString()) ? dt.Rows[0][0].ToString() : "";
model.LoginName = !string.IsNullOrEmpty(dt.Rows[0][1].ToString()) ? dt.Rows[0][1].ToString() : "";
model.DisplayName = !string.IsNullOrEmpty(dt.Rows[0][0].ToString()) ? dt.Rows[0][0].ToString() : "";
model.Department = !string.IsNullOrEmpty(dt.Rows[0][2].ToString()) ? dt.Rows[0][2].ToString() : "";
model.JobTitle = !string.IsNullOrEmpty(dt.Rows[0][3].ToString()) ? dt.Rows[0][3].ToString() : "";
//model.Gender = !string.IsNullOrEmpty(dt.Rows[0][4].ToString()) ? (dt.Rows[0][4].ToString() == "1" ? "男" : "女") : "";
model.Phone = !string.IsNullOrEmpty(dt.Rows[0][5].ToString()) ? dt.Rows[0][5].ToString() : "";
model.Status = GetEmployeeStatus(DataConverter.ChangeType <object, string>(dt.Rows[0][6]));
model.WD_UserID = DataConverter.ChangeType <object, int>(dt.Rows[0][7]);
}
return(model);
}
/// <summary>
/// 处理完成后更新状态
/// </summary>
/// <param name="message"></param>
public void UploadMessageStatus(Entities.TMS_Messages message)
{
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE TSM_Messages SET SendTime=@Now, Status=@Status, TryTimes=COALESCE(TryTimes,0)+1");
if (!string.IsNullOrEmpty(message.ErrorInfo))
{
sql.Append(", ErrorInfo=@ErrorInfo");
}
if (message.Status == (int)MessageStatus.Retry && message.TargetTime > DateTime.MinValue)
{
sql.Append(", TargetTime=@TargetTime");
}
sql.Append(" WHERE MessageID=@MessageID");
Database db = DatabaseFactory.Create(DbContext.GetContext(this.ConnectionName));
DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
db.AddInParameter(dbCommand, "@Now", DbType.DateTime, DateTime.Now);
db.AddInParameter(dbCommand, "@MessageID", DbType.Guid, message.ID);
db.AddInParameter(dbCommand, "@Status", DbType.Int32, message.Status);
if (!string.IsNullOrEmpty(message.ErrorInfo))
{
db.AddInParameter(dbCommand, "@ErrorInfo", DbType.String, SqlTextHelper.SafeQuote(message.ErrorInfo));
}
if (message.Status == (int)MessageStatus.Retry && message.TargetTime > DateTime.MinValue)
{
db.AddInParameter(dbCommand, "@TargetTime", DbType.DateTime, message.TargetTime);
}
db.ExecuteNonQuery(dbCommand);
}
/// <summary>
/// 获得指定权限项组下的权限项
/// </summary>
/// <param name="groupID"></param>
/// <returns></returns>
public List <BPrivilege> GetPrivilegesOfGroup(string groupID)
{
string sql = ORMapping.GetSelectSql <BPrivilege>(TSqlBuilder.Instance);
sql += " WHERE " + base.NotDeleted;
sql += string.Format(" AND groupID='{0}'", SqlTextHelper.SafeQuote(groupID));
return(ExecuteQuery(sql));
}
/// <summary>
/// 验证用户,密码暂时未使用
/// </summary>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword"></param>
/// <returns></returns>
internal BUserinfo ValidateUser(string strUserName, string strPassword)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(" SELECT * from {0} ", ORMapping.GetTableName(typeof(BUserinfo)));
sql.AppendFormat(" WHERE LoginName='{0}'", SqlTextHelper.SafeQuote(strUserName));
sql.Append(" AND IsDeleted<1");
List <BUserinfo> result = ExecuteQuery(sql.ToString());
return(result.FirstOrDefault());
}
/// <summary>
/// 返回重名但ID不重复的项
/// </summary>
/// <param name="name"></param>
/// <param name="id"></param>
/// <returns></returns>
public List <BPrivilege> GetExists(string name, Guid groupId, Guid id)
{
var list = this.Load(p =>
{
p.AppendItem("Name", SqlTextHelper.SafeQuote(name));
p.AppendItem("GroupId", groupId);
p.AppendItem("ID", id, "<>");
p.AppendItem("ISDELETED", "1", "<>");
});
return(list);
}
internal List <BPrivilege> GetPrivilegeByCode(string code, PrivilegeType privilegeType)
{
string sql = ORMapping.GetSelectSql <BPrivilege>(TSqlBuilder.Instance);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("isdeleted", 0);
where.AppendItem("PrivilegeType", privilegeType.ToString());
where.AppendItem("Code", SqlTextHelper.SafeQuote(code), "startwith");
sql += " where " + where.ToSqlString(TSqlBuilder.Instance);
return(ExecuteQuery(sql));
}
internal IList <BPrivilegeGroup> GetExists(string name, Guid id)
{
var list = this.Load(p =>
{
p.AppendItem("Name", SqlTextHelper.SafeQuote(name));
if (id != null && id != Guid.Empty)
{
p.AppendItem("ID", id, "<>");
}
p.AppendItem("ISDELETED", "1", "<>");
});
return(list);
}
//internal BUserinfo GetUserinfoByWdUid(int wdUserID)
//{
// string SQL = string.Format("SELECT * FROM {0} WHERE WD_UserID={1} AND {2}", ORMapping.GetTableName<BUserinfo>(), wdUserID, NotDeleted);
// List<BUserinfo> users = ExecuteQuery(SQL);
// return users.FirstOrDefault();
//} //代码功能重复
/// <summary>
/// 返回重名但ID不重复的项
/// </summary>
/// <param name="name"></param>
/// <param name="id"></param>
/// <returns></returns>
internal IList <BUserinfo> GetExists(string name, string jobTitle, Guid id)
{
var result = this.Load(p =>
{
p.AppendItem("Name", SqlTextHelper.SafeQuote(name));
p.AppendItem("JobTitle", SqlTextHelper.SafeQuote(jobTitle));
if (id != null && id != Guid.Empty)
{
p.AppendItem("ID", id, "<>");
}
p.AppendItem("ISDELETED", "1", "<>");
});
return(result);
}
/// <summary>
/// 返回重名但ID不重复的项
/// </summary>
/// <param name="name"></param>
/// <param name="id"></param>
/// <returns></returns>
internal IList <BRoleinfo> GetExists(string name, Guid id, Guid congId)
{
var list = this.Load(p =>
{
p.AppendItem("Name", SqlTextHelper.SafeQuote(name));
if (id != null && id != Guid.Empty)
{
p.AppendItem("ID", id, "<>");
}
p.AppendItem("ScopeID", congId);
p.AppendItem("ISDELETED", "1", "<>");
});
return(list);
}
/// <summary>
/// 获取用户操作权限
/// </summary>
/// <param name="userID">用户id</param>
/// <param name="privilegeType">权限类型</param>
/// <param name="privilegeType">权限类型</param>
/// <returns></returns>
public IList <VUserPrivilege> GetByUserIDAndType(string userID, string privilegeType)
{
string sql = ORMapping.GetSelectSql <VUserPrivilege>(TSqlBuilder.Instance);
WhereSqlClauseBuilder where = new WhereSqlClauseBuilder();
where.AppendItem("userid", SqlTextHelper.SafeQuote(userID));
if (null != privilegeType && privilegeType != "")
{
where.AppendItem("privilegeType", privilegeType);
}
sql += " where " + where.ToSqlString(TSqlBuilder.Instance);
return(ExecuteQuery(sql));
}
public IList <TemplateConfigInstancePlan> GetList(TemplateConfigInstancePlanFilter filter, out int totalCount)
{
var whereBuilder = filter.ConvertToWhereBuilder();
whereBuilder.AppendItem("IsDeleted", false);
QueryCondition qc = new QueryCondition(
filter.RowIndex,
filter.PageSize,
" * ",
ORMapping.GetTableName(typeof(TemplateConfigInstancePlan)),
SqlTextHelper.SafeQuote("CreatorTime asc"),
whereBuilder.ToSqlString(TSqlBuilder.Instance)
);
var result = GetPageSplitedCollection(qc);
totalCount = result.TotalCount;
return(result.SubCollection);
}
public WdOrg GetOrgByName(string orgName)
{
WdOrg result = null;
string sqlCommand = string.Empty;
sqlCommand = string.Format(@"SELECT parentUnitID ParentID,OrgID, OrgName,ShortName,[order] OrderID , FullPath
FROM dbo.wd_org WHERE [orgName] ='{0}' ORDER BY [order]",
SqlTextHelper.SafeQuote(orgName));
DataSet ds = DbHelper.RunSqlReturnDS(sqlCommand, ConnectionName);
if (ds != null)
{
DataTable table = ds.Tables[0];
if (table != null && table.Rows.Count > 0)
{
WdOrg view = new WdOrg();
ORMapping.DataRowToObject <WdOrg>(table.Rows[0], view);
result = view;
}
}
return(result);
}
/// <summary>
/// 按where 条件 且返回分页
/// </summary>
/// <param name="where"></param>
/// <param name="rowIndex">行序号， 基于0</param>
/// <param name="pageSize">分页大小</param>
/// <param name="sortField">排序字段</param>
/// <returns></returns>
public PartlyCollection <T> GetList(WhereSqlClauseBuilder where, int rowIndex, int pageSize, string sortField = "ID")
{
if (where == null)
{
throw new ArgumentNullException("where");
}
if (string.IsNullOrEmpty(sortField))
{
sortField = "ID";
}
QueryCondition qc = new QueryCondition(
rowIndex,
pageSize,
" * ",
ORMapping.GetTableName(typeof(T)),
SqlTextHelper.SafeQuote(sortField),
where.ToSqlString(TSqlBuilder.Instance)
);
PartlyCollection <T> result = GetPageSplitedCollection(qc);
return(result);
}
/// <summary>
/// 根据orgID获取用户信息
/// </summary>
/// <param name="orgid">wd_org.orgID</param>
/// <returns></returns>
public PartlyCollection <WD_User> GetSearchOrgUser(string orgid)
{
PartlyCollection <WD_User> result = new PartlyCollection <WD_User>();
string sqlCommand = string.Empty;
if (orgid != null)
{
sqlCommand = string.Format(@"
SELECT b.employeeCode as Wd_UserID,b.username as LoginName,b.employeeName as Name,
b.orgName as OrgName,b.orgID as Wd_OrgID,b.jobID as JobID,b.joinUnitDate as StartTime, NULL as EndTime,
b.jobName as JobTitle
FROM dbo.wd_user_org_rel a
INNER JOIN dbo.Wd_User b ON a.username = b.username
WHERE b.unitID ='{0}' AND a.username IS NOT NULL and b.employeeStatus='2'", SqlTextHelper.SafeQuote(orgid));
DataSet ds = DbHelper.RunSqlReturnDS(sqlCommand, ConnectionName);
if (ds != null)
{
DataTable table = ds.Tables[0];
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
WD_User view = new WD_User();
ORMapping.DataRowToObject <WD_User>(row, view);
result.Add(view);
}
}
}
}
return(result);
}
public static string CreateAddMethod(int action, string table_name, List <SqlColumnInfo> colList, string model_name, string db_name)
{
if ((action & (int)action_type.add) == (int)action_type.add)
{
StringBuilder keyContent = new StringBuilder("");
StringBuilder valueContent = new StringBuilder("");
var addList = Cache_VMData.GetVMList(table_name, VMType.Add, colList.ToNotMainIdList());
for (var i = 0; i < addList.Count; i++)
{
var item = addList[i];
if (i == 0)
{
keyContent.Append(item.Name);
valueContent.Append("@" + item.Name);
}
else
{
keyContent.Append(" ," + item.Name);
valueContent.Append(" ,@" + item.Name);
}
}
string addContent = string.Format(@"string insertSql = ""insert into {0}({1}) values ({2});"";", table_name, keyContent.ToString(), valueContent.ToString());
var checkList = Cache_VMData.GetAddCheckList(table_name, colList.ToNotMainIdList());
if (checkList.Count == 0)
{
string template = @"
public bool Add{3}({0} model)
{{
{1}
using (IDbConnection sqlcn = ConnectionFactory.{2})
{{
return sqlcn.Execute(insertSql, model) > 0;
}}
}}
";
return(string.Format(template,
table_name,
addContent.ToString(),
db_name,
table_name));
}
else
{
string select_text = SqlTextHelper.CreateSelectCountSql(table_name, checkList);
string template = @"
public bool Add{3}({0} model)
{{
{4}
{1}
using (IDbConnection sqlcn = ConnectionFactory.{2})
{{
int count = sqlcn.QuerySingleOrDefault<int>(selectSql, model);
if(count == 0)
{{
return sqlcn.Execute(insertSql, model) > 0;
}}
return false;
}}
}}
";
return(string.Format(template,
table_name,
addContent.ToString(),
db_name,
table_name,
select_text));
}
}
else
{
return(string.Empty);
}
}
public IList <VUserPrivilege> GetByUserID(string userID)
{
return(GetByUserID(SqlTextHelper.SafeQuote(userID), -1));
}
public static string CreateAddMethod(int action, string table_name, List <SqlColumnInfo> colList, string model_name, string db_name)
{
if ((action & (int)action_type.add) == (int)action_type.add)
{
StringBuilder addContent = new StringBuilder();
StringBuilder valueContent = new StringBuilder(") values (");
StringBuilder addparamsContent = new StringBuilder("List<MySqlParameter> listParams = new List<MySqlParameter>();\r\n");
addContent.AppendFormat("string insertSql = \"insert into {0}(", table_name);
int index = 0;
var addList = Cache_VMData.GetVMList(table_name, VMType.Add, colList.ToNotMainIdList());
foreach (var item in addList)
{
if (index == 0)
{
addContent.Append(item.Name);
valueContent.Append("@" + item.Name);
}
else
{
addContent.Append(" ," + item.Name);
valueContent.Append(" ,@" + item.Name);
}
addparamsContent.AppendFormat("\t\t\tlistParams.Add(new MySqlParameter(\"@{0}\", {1}) {{ Value = model.{0} }});\r\n", item.Name, item.DbType.ToMySqlDbType());
index++;
}
var checkList = Cache_VMData.GetAddCheckList(table_name, colList.ToNotMainIdList());
if (checkList.Count == 0)
{
addContent.Append(valueContent.ToString() + ")\";");
string template = @"
public bool Add{4}({0} model)
{{
{1}
{2}
using (MySqlConnection sqlcn = ConnectionFactory.{3})
{{
return MySqlHelper2.ExecuteNonQuery(sqlcn, CommandType.Text, insertSql, listParams.ToArray()) > 0;
}}
}}
";
return(string.Format(template,
model_name,
addContent.ToString(),
addparamsContent.ToString(),
db_name,
table_name));
}
else
{
string select_text = SqlTextHelper.CreateSelectCountSql(table_name, checkList);
addContent.Append(valueContent.ToString() + ")\";");
string template = @"
public bool Add{4}({0} model)
{{
{1}
{2}
{5}
using (MySqlConnection sqlcn = ConnectionFactory.{3})
{{
Object obj = MySqlHelper2.ExecuteScaler(sqlcn, CommandType.Text, selectSql, listParams.ToArray());
if(obj == null || obj == DBNull.Value)
{{
return false;
}}
int count = Convert.ToInt32(obj);
if(count == 0)
{{
return MySqlHelper2.ExecuteNonQuery(sqlcn, CommandType.Text, insertSql, listParams.ToArray()) > 0;
}}
else
{{
return false;
}}
}}
}}
";
return(string.Format(template,
model_name,
addContent.ToString(),
addparamsContent.ToString(),
db_name,
table_name,
select_text));
}
}
else
{
return(string.Empty);
}
}
public string CreateAddMethod()
{
StringBuilder keyContent = new StringBuilder();
StringBuilder valueContent = new StringBuilder();
int index = 0;
foreach (var item in list.ToNotMainIdList())
{
if (index == 0)
{
keyContent.Append(item.Name);
valueContent.Append("@" + item.Name);
}
else
{
keyContent.Append(" ," + item.Name);
valueContent.Append(" ,@" + item.Name);
}
index++;
}
string addContent = string.Format(@"string insertSql = ""insert into {0} ({1}) values ({2});"";", table_name, keyContent.ToString(), valueContent.ToString());
var checkList = Cache_VMData.GetAddCheckList(table_name, list.ToNotMainIdList());
if (checkList.Count == 0)
{
string template = @"
public bool Add{3}({0} model)
{{
{1}
using (IDbConnection sqlcn = ConnectionFactory.{2})
{{
return sqlcn.Execute(insertSql, model) > 0;
}}
}}
";
return(string.Format(template,
model_name,
addContent,
db_name,
table_name));
}
else
{
string select_text = SqlTextHelper.CreateSelectCountSql(table_name, checkList);
string template = @"
public bool Add{3}({0} model)
{{
{4}
{1}
using (IDbConnection sqlcn = ConnectionFactory.{2})
{{
sqlcn.Open();
int count = sqlcn.QuerySingleOrDefault<int>(selectSql, model);
if(count == 0)
{{
return sqlcn.Execute(insertSql, model) > 0;
}}
return false;
}}
}}
";
return(string.Format(template,
model_name,
addContent,
db_name,
table_name,
select_text));
}
}
