public static bool UpdateRoleLink(string strDelete, string strRoleId)
{
//构建查询条件
Parameter parameter1 = new Parameter();
parameter1.SqlString = " DELETE FROM PT_RoleLink WHERE RoleId = " + strRoleId + " ";
//构建多个查询条件
Parameter parameter2 = new Parameter();
parameter2.SqlString = " INSERT PT_RoleLink VALUES('" + strRoleId + "','01') ";
List <Parameter> parmList = new List <Parameter>();
parmList.Add(parameter1);
parmList.Add(parameter2);
string[] lstr = strDelete.Split(',');
foreach (string str in lstr)
{
//角色关联功能
Parameter parameter = new Parameter();
parameter.SqlString = " INSERT PT_RoleLink VALUES('" + strRoleId + "','" + str + "') ";
parmList.Add(parameter);
}
CommonToolsBLL.ExcuteSqlWithTransaction(parmList);
return(true);
}
/// <summary>
/// add by chr
/// 通过用户名将用户的所有信息进行删除
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static void DeleteUser(string userName)
{
//构建查询条件
Parameter parameter1 = new Parameter();
parameter1.SqlString = " DELETE FROM PT_UserLink WHERE UserName = @UserName";
SqlParameter[] parameters1 =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 50)
{
Value = userName
},
};
parameter1.Parameters = parameters1;
Parameter parameter2 = new Parameter();
parameter2.SqlString = " DELETE FROM PT_USER WHERE UserName= @UserName";
SqlParameter[] parameters2 =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 50)
{
Value = userName
},
};
parameter2.Parameters = parameters2;
List <Parameter> parmList = new List <Parameter>();
parmList.Add(parameter1);
parmList.Add(parameter2);
CommonToolsBLL.ExcuteSqlWithTransaction(parmList);
}
/// <summary>
/// add by chr
/// 根据用户名获取本餐厅可用的餐桌负责人列表
/// </summary>
/// <returns></returns>
public static DataTable GetTableDutyUserListByOrgId(string strOrgId)
{
int iOrgId = Convert.ToInt32(strOrgId);
Parameter parameter = new Parameter();
parameter.SqlString = "SELECT [YWPerson].[UserName],[PT_User].[Nickname],[YWPerson].[OrgId],[PT_ORG].[OrgName] "
+ "FROM [YWPerson] "
+ "LEFT JOIN [PT_ORG] ON [YWPerson].[OrgId]=[PT_ORG].[Id] "
+ "LEFT JOIN [PT_User] ON [YWPerson].[UserName]=[PT_User].[UserName] "
+ "INNER JOIN [PT_UserLink] ON [YWPerson].[UserName]=[PT_UserLink].[UserName] "
+ "WHERE [YWPerson].[OrgId]=@OrgId AND [PT_User].[StateId]=1 AND [PT_UserLink].[RoleId]=@RoleId";
SqlParameter[] parameters =
{
new SqlParameter("@OrgId", SqlDbType.Int, 50)
{
Value = iOrgId
},
new SqlParameter("@RoleId", SqlDbType.Int, 50)
{
Value = CodeDictionary.UserRole["一般用户（初始默认）"]
}
};
parameter.Parameters = parameters;
return(CommonToolsBLL.GetAllListByParams(parameter));
}
/// <summary>
/// add by chr
/// 根据餐厅组织机构Id获取获取餐厅所有用户列表
/// </summary>
/// <returns></returns>
public static DataTable GetRestaurantUserListByOrgId(int iOrgId)
{
string strAllSubOrgId = PTOrgBLL.GetAllSubOrgIdString(iOrgId.ToString());
Parameter parameter = new Parameter();
parameter.SqlString = "SELECT [PT_User].[UserName],[PT_User].[Nickname],[PT_User].[OrgId],[PT_ORG].[OrgName] "
+ "FROM [PT_User] "
+ "LEFT JOIN [PT_ORG] ON [PT_User].[OrgId]=[PT_ORG].[Id] "
+ "WHERE [PT_User].[OrgId] IN (" + strAllSubOrgId + (strAllSubOrgId.Trim() == "" ? "" : ",") + iOrgId.ToString() + ") AND [PT_User].[StateId]=1";
return(CommonToolsBLL.GetAllListByParams(parameter));
}
/// <summary>
/// add by chr
/// 根据用户名strUserName来获取该用户所有的角色
/// </summary>
/// <param name="strUserName"></param>
/// <returns></returns>
public static DataTable GetRolesByUserName(string strUserName)
{
Parameter parameter = new Parameter();
parameter.SqlString = "SELECT [PT_UserLink].[Id] AS [LinkId], [PT_ROLE].[Id] AS [RoleId],[UserName],[RoleName] ,[StateId] ";
parameter.SqlString += " FROM [PT_UserLink] LEFT JOIN [PT_ROLE] ON [PT_ROLE].[Id] = [PT_UserLink].[RoleId] WHERE [UserName] = @UserName ";
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 50)
{
Value = strUserName
},
};
parameter.Parameters = parameters;
return(CommonToolsBLL.GetAllListByParams(parameter));
}
/// <summary>
/// add by chr
/// 根据角色类型获取角色数据列表
/// </summary>
/// <param name="strType">0-系统角色 1-业务角色</param>
/// <returns></returns>
public static DataTable GetRolesByType(string strType)
{
Parameter parameter = new Parameter();
parameter.SqlString = "SELECT [Id],[RoleName],[GroupId], [StateId],[Alternate1],[Alternate2],[Alternate3],[Alternate4],[Alternate5] ";
parameter.SqlString += " FROM [PT_ROLE] WHERE [GroupId] = @GroupId ";
SqlParameter[] parameters =
{
new SqlParameter("@GroupId", SqlDbType.NVarChar, 50)
{
Value = strType
},
};
parameter.Parameters = parameters;
return(CommonToolsBLL.GetAllListByParams(parameter));
}
/// <summary>
/// add by chr
/// </summary>
/// <param name="strUserName"></param>
/// <returns></returns>
public static DataTable GetUserByTel(string strTel)
{
//构建查询条件
Parameter parameter = new Parameter();
parameter.SqlString = " SELECT [OrgId] FROM [PT_USER] WHERE [Alternate1]=@Alternate1 ";
SqlParameter[] parameters =
{
new SqlParameter("@Alternate1", SqlDbType.NVarChar, 50)
{
Value = strTel
},
};
parameter.Parameters = parameters;
return(CommonToolsBLL.GetAllListByParams(parameter));
}
/// <summary>
/// add by chr
/// </summary>
/// <param name="strUserName"></param>
/// <returns></returns>
public static int GetUserOrgId(string strUserName)
{
//构建查询条件
Parameter parameter = new Parameter();
parameter.SqlString = " SELECT [OrgId] FROM [PT_USER] WHERE [UserName]=@UserName ";
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 50)
{
Value = strUserName
},
};
parameter.Parameters = parameters;
return(Convert.ToInt32(CommonToolsBLL.ExcuteSqlReturnSingleObject(parameter)));
}
/// <summary>
/// add by chr
/// </summary>
/// <param name="OrgId">当前机构</param>
/// <param name="OrgName">要查找机构名称(1-厨房 2-仓库)</param>
/// <returns></returns>
public static int GetFatherIdByOrg(string OrgId, string OrgName)
{
//构建查询条件
Parameter parameter = new Parameter();
parameter.SqlString = " SELECT [Id] FROM [PT_ORG] WHERE [FatherId]=@OrgId AND [OrgName]=@OrgName ";
SqlParameter[] parameters =
{
new SqlParameter("@OrgId", SqlDbType.NVarChar, 50)
{
Value = OrgId
},
new SqlParameter("@OrgName", SqlDbType.NVarChar, 50)
{
Value = OrgName
}
};
parameter.Parameters = parameters;
return(Convert.ToInt32(CommonToolsBLL.ExcuteSqlReturnSingleObject(parameter)));
}
/// <summary>
/// add by chr
/// 根据功能id和组织机构id，获取相应的用户列表
/// </summary>
/// <param name="strFunctionId"></param>
/// <param name="iOrgId"></param>
/// <returns></returns>
public static DataTable GetUserNameByFunctionIdAndOrgId(string strFunctionId, int iOrgId)
{
Parameter parameter = new Parameter();
parameter.SqlString = "SELECT DISTINCT [PT_User].[UserName] FROM [PT_User],[PT_UserLink],[PT_RoleLink] ";
parameter.SqlString += "WHERE [PT_RoleLink].[FunctionId]=@FunctionId AND [PT_User].[OrgId]=@OrgId AND [PT_UserLink].[RoleId] = [PT_RoleLink].[RoleId] AND [PT_User].[UserName] = [PT_UserLink].[UserName] ";
SqlParameter[] parameters =
{
new SqlParameter("@FunctionId", SqlDbType.NVarChar, 50)
{
Value = strFunctionId
},
new SqlParameter("@OrgId", SqlDbType.Int, 50)
{
Value = iOrgId
},
};
parameter.Parameters = parameters;
return(CommonToolsBLL.GetAllListByParams(parameter));
}
/// <summary>
/// 公共方法：获取某一功能节点所有下级子功能的列表（递归），返回功能树格式的JSON，筛选用户权限，用于系统维护-功能管理
/// </summary>
public static string GetAllSubFunctonByRole(string strFunctionId, string strTypeIds, string roleId)
{
DataTable dtSubFunction = GetSubFunctionByRole(strFunctionId, strTypeIds, roleId);
string strResult = @"[";
for (int i = 0; i < dtSubFunction.Rows.Count; i++)
{
if (i > 0)
{
strResult += @",";
}
strResult += @"{";
strResult += @"""item"":" + CommonToolsBLL.DataRowToJson(dtSubFunction.Rows[i]) + @",";
strResult += @"""children"":" + GetAllSubFunctonByRole(dtSubFunction.Rows[i]["Id"].ToString(), strTypeIds, roleId);
strResult += @"}";
}
strResult += @"]";
return(strResult);
}
//add by chr
//获取系统所有仓库信息
public static PageData GetSystemStorehouseList(int iPageSize, int iCurPage, string sOrgId, string sName)
{
//构建查询条件
Parameter parameter = new Parameter();
parameter.SqlString = "SELECT [PT_ORG].[Id],[PT_ORG2].[FatherId] AS [OrgId],[PT_ORG].[LevelId],[PT_ORG].[OrgName], [PT_ORG3].[OrgName] AS [FOrgName] ,CONVERT(BIT,[PT_ORG].[StateId]) AS [StateId] "
+ "FROM [PT_ORG] "
+ "LEFT JOIN [PT_ORG] AS [PT_ORG2] ON [PT_ORG2].Id = [PT_ORG].[FatherId] "
+ "LEFT JOIN [PT_ORG] AS [PT_ORG3] ON [PT_ORG3].Id = [PT_ORG2].[FatherId] "
+ "WHERE [PT_ORG].[FatherId] IN "
+ "(SELECT [Id] FROM [PT_ORG] WHERE [OrgName]='仓库')";
if (sOrgId.Trim() != "")
{
parameter.SqlString += " AND [PT_ORG3].[Id] = " + sOrgId + " ";
}
if (sName.Trim() != "")
{
parameter.SqlString += " AND [PT_ORG].[OrgName] LIKE '%" + sName + "%' ";
}
return(CommonToolsBLL.GetPageListByParams(iPageSize, iCurPage, parameter));
}
//﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉
// 说明：以下区域的代码为设计开发人员所编写，主要为扩展该业务逻辑类的功能，而定义的变量、属性及相关业务逻辑处理方法。
// 注意：用户界面层应当只需调用本层便可完成所有操作，本类对关联的数据访问类调用，应当只通过类中的DataAccess属性实现。
//﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍
/// <summary>
/// 根据基本用户信息（PTUsers）的主键“用户名（UserName）”从数据库中获取基本用户信息（PTUsers）的实例。
/// 成功从数据库中取得记录返回新基本用户信息（PTUsers）的实例“，没有取到记录返回null值。
/// </summary>
/// <param name="userName">基本用户信息（PTUsers）的主键“用户名（UserName）”</param>
public static DataTable GetDataTableByUserName(string userName)
{
// return DataAccess.GetDataByUserName(userName);
//构建查询条件
Parameter parameter = new Parameter();
parameter.SqlString = " SELECT [PT_USER].[Id],[PT_USER].[UserName],[PT_USER].[Password],[PT_USER].[OrgId],[PT_ORG].[OrgName], "
+ " [PT_USER].[Nickname],[PT_USER].[StateId],[PT_USER].[Alternate1],[PT_USER].[Alternate2], "
+ " [PT_USER].[Alternate3],[PT_USER].[Alternate4],[PT_USER].[Alternate5] "
+ " FROM [PT_USER] LEFT JOIN [PT_ORG] ON [PT_USER].[OrgId] = [PT_ORG].[Id] "
+ " WHERE [PT_USER].[UserName]=@UserName ";
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 50)
{
Value = userName
},
};
parameter.Parameters = parameters;
return(CommonToolsBLL.GetAllListByParams(parameter));
}
//﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉﹉
// 说明：以下区域的代码为设计开发人员所编写，主要为扩展该业务逻辑类的功能，而定义的变量、属性及相关业务逻辑处理方法。
// 注意：用户界面层应当只需调用本层便可完成所有操作，本类对关联的数据访问类调用，应当只通过类中的DataAccess属性实现。
//﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍﹍
/// <summary>
/// 通过传入的角色id组成的字符串 来删除角色
/// </summary>
/// <param name="strDelete"></param>
/// <returns></returns>
public static bool DeleteRolesByStr(string strDelete)
{
//构建查询条件
Parameter parameter1 = new Parameter();
parameter1.SqlString = " SELECT * FROM PT_UserLink WHERE RoleId IN(" + strDelete + ") ";
//查询数据
DataTable dt = BLL.CommonToolsBLL.GetAllListByParams(parameter1);
if (dt.Rows.Count > 0)
{
return(false);
}
//构建多个查询条件
Parameter parameter2 = new Parameter();
parameter2.SqlString = " DELETE FROM PT_ROLE WHERE Id IN(" + strDelete + ") ";
//角色关联功能
Parameter parameter3 = new Parameter();
parameter3.SqlString = " DELETE FROM PT_RoleLink WHERE RoleId IN(" + strDelete + ") ";
//角色关联用户
Parameter parameter4 = new Parameter();
parameter4.SqlString = " DELETE FROM PT_UserLink WHERE RoleId IN(" + strDelete + ") ";
List <Parameter> parmList = new List <Parameter>();
parmList.Add(parameter2);
parmList.Add(parameter3);
parmList.Add(parameter4);
CommonToolsBLL.ExcuteSqlWithTransaction(parmList);
return(true);
}
//add by chr
//根据餐厅组织机构Id获取餐厅的所有仓库信息
public static PageData GetRestaurantStorehouseListByOrgId(int iOrgId, int iPageSize, int iCurPage, string sName)
{
//构建查询条件
Parameter parameter = new Parameter();
parameter.SqlString = "SELECT [PT_ORG].[Id],[PT_ORG].[FatherId],[PT_ORG].[LevelId],[PT_ORG].[OrgName],CONVERT(BIT,[PT_ORG].[StateId]) AS [StateId] "
+ "FROM [PT_ORG] "
+ "WHERE [PT_ORG].[FatherId] IN "
+ "(SELECT [Id] FROM [PT_ORG] WHERE [FatherId]=@OrgId AND [OrgName]='仓库')";
if (sName.Trim() != "")
{
parameter.SqlString += " AND [PT_ORG].[OrgName] LIKE '%" + sName + "%' ";
}
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@OrgId", SqlDbType.Int, 50)
{
Value = iOrgId
}
};
parameter.Parameters = parameters;
return(CommonToolsBLL.GetPageListByParams(iPageSize, iCurPage, parameter));
}
/// <summary>
/// add by chr
/// 公共方法: 通过机构的主键Id 删除相关的机构信息(包括其子机构) 将被删除机构下的所有用户一并删除
/// </summary>
/// <param name="strId"></param>
public static bool DeleteOrgById(string strId)
{
//构建查询条件
Parameter parameter1 = new Parameter();
parameter1.SqlString = " SELECT Id FROM PT_ORG WHERE Id = @Id OR FatherId= @Id ";
SqlParameter[] parameters1 =
{
new SqlParameter("@Id", SqlDbType.Int, 4)
{
Value = strId
},
};
parameter1.Parameters = parameters1;
//查询数据
DataTable dt = BLL.CommonToolsBLL.GetAllListByParams(parameter1);
string DelStr = "";
foreach (DataRow dr in dt.Rows)
{
DelStr += dr["Id"] + ",";
}
//被删除的机构字符串
DelStr = DelStr.Substring(0, DelStr.Length - 1);
//构建多个查询条件
Parameter parameter4 = new Parameter();
parameter4.SqlString = " SELECT * FROM PT_USER WHERE OrgId IN (" + DelStr + ") ";
DataTable dt_user = BLL.CommonToolsBLL.GetAllListByParams(parameter4);
if (dt_user.Rows.Count > 0)
{
return(false);
}
//构建多个查询条件
Parameter parameter2 = new Parameter();
parameter2.SqlString = " DELETE FROM PT_USER WHERE OrgId IN (" + DelStr + ") ";
Parameter parameter3 = new Parameter();
parameter3.SqlString = " DELETE FROM PT_ORG WHERE Id = @Id OR FatherId= @Id ";
SqlParameter[] parameters3 =
{
new SqlParameter("@Id", SqlDbType.Int, 4)
{
Value = strId
},
};
parameter3.Parameters = parameters3;
List <Parameter> parmList = new List <Parameter>();
parmList.Add(parameter2);
parmList.Add(parameter3);
CommonToolsBLL.ExcuteSqlWithTransaction(parmList);
return(true);
}
