public static int UpdateSinglePrefixCode(int ID, string[] FieldValues)
{
string sSqlText = "";
if (ID > 0)
{
sSqlText = "begin update SysPrefixCode_Info set CodeName='" + FieldValues.GetValue(0)
+ "',TableName='" + FieldValues[1] + ""
+ "',PrefixCode='" + FieldValues[2] + ""
+ "',Description='" + FieldValues[3] + "'"
+ " WHERE ID =" + ID + ";";
string sLogText = "更新 系统管理>单号前缀管理:单号前缀列表：" + FieldValues.GetValue(0) + "记录。";
sSqlText += SysLogs.GetOperatorLogSQL(sLogText) + " end;";
}
else
{
sSqlText = "begin Insert Into SysPrefixCode_Info(SystemID, CodeName, TableName, PrefixCode, Description)Values("
+ SysParams.GetPurviewSystemID().ToString() + ",'"
+ FieldValues.GetValue(0) + "','" + FieldValues.GetValue(1) + "','" + FieldValues.GetValue(2)
+ "','" + FieldValues.GetValue(3) + "')" + ";";
string sLogText = "新增 系统管理>单号前缀管理:单号前缀列表：" + FieldValues.GetValue(0) + "记录。";
sSqlText = sSqlText + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
}
return(DataCommon.QueryData(sSqlText));
}
public static SqlDataReader GetRoleChildPurviewLst(int PPurviewID)
{
string sSQL = "Select * from SysPurviewTempate_Info Where Status=0 "
+ " And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " And IsNull(PPurviewID,0)=" + PPurviewID.ToString() + " Order By SortID";
return(DataCommon.GetDataByReader(sSQL));
}
public static SqlDataReader GetRoleChildPurvieLstByReader(int PPurviewID, string Purview)
{
string sSQL = "Select *,'" + Purview + "' as Purview from SysPurviewTempate_Info Where Status=0 "
+ " And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " And IsNull(PPurviewID,0)=" + PPurviewID.ToString();
return(DataCommon.GetDataByReader(sSQL));
}
public static int DeleteUserByRole(int _UserID, int _RoleID)
{
string sSQL = "Update User_Info Set UserRoles=Replace(','+UserRoles+',','," + _RoleID.ToString() + ",','') where IsNull(SystemID,0)="
+ SysParams.GetPurviewSystemID().ToString() + " Status =0 and ID=" + _UserID.ToString() + ";";
sSQL = sSQL + "";
return(DataCommon.QueryData(sSQL));
}
public static DataSet GetSysOpCodeLstByRole(int RoleID, string SearchText)
{
string sSQL = "SELECT a.*,b.OrganName FROM SysUser_Info a "
+ " left join SysOrgan_Info b on a.OrganID=b.ID and b.Status=0 And IsNull(b.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " WHERE a.Status=0";
if (SearchText.Length > 0)
{
sSQL = sSQL + " And (a.OpCode Like '%" + SearchText + "%' Or a.OpName Like '%" + SearchText + "%')";
}
sSQL = sSQL + " And charindex('," + RoleID.ToString() + ",',','+IsNull(UserRoles,'')+',')>0 And IsNull(a.SystemID,0)="
+ SysParams.GetPurviewSystemID().ToString() + " Order By a.SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
public static DataSet GetSysUserByDataSet(int OrganID, string SearchText)
{
string sSQL = "SELECT a.*,b.OrganName, dbo.RoleNameLstByUserID(" + SysParams.GetPurviewSystemID().ToString() + ", a.ID) As RoleName FROM SysUser_Info a "
+ " left join SysOrgan_Info b on a.OrganID=b.ID and b.Status=0 And IsNull(b.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " WHERE a.Status=0";
if (OrganID > 0)
{
sSQL = sSQL + " And IsNull(a.OrganID,0)=" + OrganID.ToString();
}
if (SearchText.Length > 0)
{
sSQL = sSQL + " And (a.OpCode Like '%" + SearchText + "%' Or a.OpName Like '%" + SearchText + "%')";
}
sSQL = sSQL + " And IsNull(a.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " Order By a.SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
public static DataSet GetPrefixCodeUserByDataSet(int PrefixCodeID, string SearchText)
{
string sSQL = "SELECT a.*,b.OrganName FROM User_Info a "
+ " left join Organ_Info b on a.OrganID=b.ID and b.Status=0 And IsNull(b.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " WHERE a.Status=0";
if (PrefixCodeID > 0)
{
sSQL = sSQL + " And a.ID in (Select UserID From SysPrefixCode_Info where Status=0 And IsNull(SystemID,0)=" + SysClass.SysParams.GetPurviewSystemID().ToString() + " And PrefixCodeID=" + PrefixCodeID.ToString() + ")";
}
if (SearchText.Length > 0)
{
sSQL = sSQL + " And (a.OpCode Like '%" + SearchText + "%' Or a.OpName Like '%" + SearchText + "%')";
}
sSQL = sSQL + " And IsNull(a.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " Order By a.SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
public static int UpdateSingleUser(int ID, string[] FieldValues)
{
string sSqlText = "begin ";
if (ID > 0)
{
sSqlText = sSqlText + "begin UPDATE SysUser_Info SET OpCode = '" + FieldValues.GetValue(0)
+ "',OpName='" + FieldValues.GetValue(1)
+ "',OrganID='" + FieldValues.GetValue(2)
+ "',Position='" + FieldValues.GetValue(3)
+ "',Place='" + FieldValues.GetValue(4)
+ "',Sex='" + FieldValues.GetValue(5)
+ "',Phone='" + FieldValues.GetValue(6)
+ "',TelNo='" + FieldValues.GetValue(7)
+ "',Address='" + FieldValues.GetValue(8)
+ "',UserDesc='" + FieldValues.GetValue(9)
+ "',IDNumber='" + FieldValues.GetValue(10)
+ "',ZipCode='" + FieldValues.GetValue(12)
+ "',IsCanLogin='******'";
sSqlText = sSqlText + " WHERE ID =" + ID + ";";
string sLogText = "更新 系统管理>人员管理 ：" + FieldValues.GetValue(0) + "|" + FieldValues.GetValue(1) + " 记录。";
sSqlText = sSqlText + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
}
else
{
sSqlText = sSqlText + "begin Insert Into SysUser_Info(SystemID, Guid, OpCode, OpName,"
+ " OrganID,Position,Place,Sex,Phone,TelNo,Address,UserDesc,IDNumber,Password,ZipCode, IsCanLogin, Status) "
+ " Values(" + SysParams.GetPurviewSystemID().ToString() + ",'" + FieldValues.GetValue(13) + "','"
+ FieldValues.GetValue(0) + "','" + FieldValues.GetValue(1) + "','"
+ FieldValues.GetValue(2) + "','" + FieldValues.GetValue(3) + "','"
+ FieldValues.GetValue(4) + "','" + FieldValues.GetValue(5) + "','"
+ FieldValues.GetValue(6) + "','" + FieldValues.GetValue(7) + "','"
+ FieldValues.GetValue(8) + "','" + FieldValues.GetValue(9) + "','"
+ FieldValues.GetValue(10) + "','" + FieldValues.GetValue(11) + "','"
+ FieldValues.GetValue(12) + "'," + FieldValues.GetValue(14);
sSqlText = sSqlText + ",0 ); ";
string sLogText = "新增 系统管理>人员管理 ： " + FieldValues.GetValue(0) + "|" + FieldValues.GetValue(1) + " 记录。";
sSqlText = sSqlText + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
}
sSqlText = sSqlText + " end";
return(DataCommon.QueryData(sSqlText));
}
public static DataSet GetChildrenUserByDataSet(string CategoryNo, int OrganID, string SearchText)
{
string sSQL = "SELECT a.*,b.OrganName FROM SysUser_Info a "
+ " left join SysOrgan_Info b on a.OrganID=b.ID and b.Status=0 And IsNull(b.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " WHERE a.Status=0";
if (CategoryNo.Length > 0)
{
sSQL = sSQL + " And a.CategoryNo='" + CategoryNo.ToString() + "'";
}
if (OrganID > 0)
{
sSQL = sSQL + " And (IsNull(a.OrganID,0) in (Select ID From dbo.GetOrganChildren(" + OrganID.ToString() + ", 1)))";
}
if (SearchText.Length > 0)
{
sSQL = sSQL + " And (a.OpCode Like '%" + SearchText + "%' Or a.OpName Like '%" + SearchText + "%')";
}
sSQL = sSQL + " And IsNull(a.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " Order By a.SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
//更新用户信息
public static int UpdateCurrentUser(int UserID, string[] FieldValues)
{
string sSqlText = "";
if (UserID > 0)
{
sSqlText = "UPDATE SysUser_Info SET OpName='"
+ FieldValues.GetValue(0) + "',Comment='"
+ FieldValues[1] + "' WHERE ID=" + UserID;
}
else
{
sSqlText = "Insert Into SysUser_Info (SystemID, OpName,Comment,Status, Password) Values(" + SysParams.GetPurviewSystemID().ToString() + ",'"
+ FieldValues.GetValue(0) + "','" + FieldValues.GetValue(1) + "',"
+ "0,'111111' )";
}
return(DataCommon.QueryData(sSqlText));
}
////更新用户信息
//public static int UpdateSingleUser(int UserID, string[] FieldValues)
//{
// string sSqlText = "";
// if (UserID > 0)
// {
// sSqlText = "UPDATE SysUser_Info SET OpName='"
// + FieldValues.GetValue(0) + "',OpCode='"
// + FieldValues.GetValue(1) + "',RoleID="
// + FieldValues.GetValue(2) + ",Comment='"
// + FieldValues[3] + "' WHERE ID=" + UserID;
// }
// else
// {
// sSqlText = "Insert Into SysUser_Info (OpName,OpCode,RoleID,Comment,Status, Password) Values('"
// + FieldValues.GetValue(0) + "','" + FieldValues.GetValue(1) + "','"
// + FieldValues.GetValue(2) + "','" + FieldValues.GetValue(3) + "',"
// + "0,'111111' )";
// }
// return DataCommon.QueryData(sSqlText);
//}
//更新用户信息
public static int UpdateSingleUserPurview(int UserID, string UserPurview)
{
string sSqlText = "";
sSqlText = "begin ";
sSqlText = sSqlText + " if not Exists(Select 1 From User_Purview Where Status=0 And UserID=" + UserID.ToString() + ")";
sSqlText = sSqlText + " Insert into User_Purview (SystemID, UserID, Purview, SortID, Status) Values(" + SysParams.GetPurviewSystemID().ToString() + "," + UserID.ToString() + ",'" + UserPurview + "',0,0" + ");";
sSqlText = sSqlText + " else ";
sSqlText = sSqlText + " Update User_Purview Set Purview='" + UserPurview + "' Where Status=0 and UserID=" + UserID.ToString() + "; ";
string sLogText = " 更新 系统管理>权限管理： " + UserID.ToString() + " 记录。";
sSqlText = sSqlText + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
return(DataCommon.QueryData(sSqlText));
}
//更新车间信息
public static int UpdateSingleOrgan(int _ID, int _OrganType, string[] FieldValues)
{
string sSqlText = "";
if (_ID > 0)
{
sSqlText = "begin UPDATE SysOrgan_Info SET OrganType=" + _OrganType.ToString() + ",POrganID=" + FieldValues.GetValue(0) + ", OrganNo='" + FieldValues.GetValue(1)
+ "',OrganName='" + FieldValues.GetValue(2) + "',Description='" + FieldValues.GetValue(3) + "'";
sSqlText = sSqlText + ",SortID=" + FieldValues.GetValue(4) + " WHERE ID=" + _ID + "" + ";";
string sLogText = "更新 人员管理>机构部门：" + FieldValues.GetValue(2) + "记录。";
sSqlText += SysLogs.GetOperatorLogSQL(sLogText) + " end;";
}
else
{
sSqlText = "begin Insert Into SysOrgan_Info (SystemID, OrganType, POrganID, OrganNo, OrganName, Description, SortID) Values(" + SysParams.GetPurviewSystemID().ToString() + ","
+ _OrganType.ToString() + "," + FieldValues.GetValue(0) + ",'" + FieldValues.GetValue(1) + "','"
+ FieldValues.GetValue(2) + "','" + FieldValues.GetValue(3) + "'," + FieldValues.GetValue(4) + ")" + ";";
string sLogText = "新增 人员管理>机构部门：" + FieldValues.GetValue(2) + "记录。";
sSqlText = sSqlText + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
}
//Del by lk 20151214 start
//sSqlText += "Insert Into ToolStock_Info (OrganID, ToolID)"
// + " select "
// + " a.ID As OrganID, b.ID As ToolID from SysOrgan_Info a, Tool_Info b "
// + " Where a.OrganType in (0, 1) and a.OrganNo='" + FieldValues.GetValue(1) + "' and a.OrganType in (" + OrganType_Value.ToString() + "," + WorkshopType_Value.ToString() + ")"
// + " And Convert(varchar(10),a.ID) + '-' + Convert(varchar(10),b.ID) not in ("
// + " Select Convert(varchar(10),a.OrganID) + '-' + Convert(varchar(10),a.ToolID) From ToolStock_Info a, SysOrgan_Info b "
// + " where a.OrganID=b.ID and b.OrganNo='" + FieldValues.GetValue(1) + "')";
//Del by lk 20151214 end
return(DataCommon.QueryData(sSqlText));
}
//获取单号前缀是否重复
public static bool CheckPrefixCodeNameExists(int ID, string PrefixCodeName)
{
string sSqlText = "select 1 from SysPrefixCode_Info where IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " And PrefixCodeName ='" + PrefixCodeName.ToString() + "' and ID<>" + ID.ToString();
return(SysGlobal.GetExecSqlIsExist(sSqlText));
}
public static DataSet GetPrefixCodeLstByDataSet(string WhereSQL)
{
string sSQL = "Select * from SysPrefixCode_Info Where Status=0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString();
sSQL = sSQL + WhereSQL;
sSQL = sSQL + " Order By SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
//获取车站列表至控件
public static void FullToRoleLst(DropDownCheckList ddlList)
{
ddlList.Items.Clear();
string sSQL = "Select * from SysRole_Info Where Status=0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " Order By SortID";
SqlDataReader sdr = DataCommon.GetDataByReader(sSQL);
while (sdr.Read())
{
ListItem liItem = new ListItem();
liItem.Value = sdr["ID"].ToString();
liItem.Text = sdr["RoleName"].ToString();
ddlList.Items.Add(liItem);
}
sdr.Close();
}
//判断预案编号已被使用
public static bool CheckPrefixCodeIsUse(int PrefixCodeID)
{
string sSqlText = "Select top 1 1 From User_Info Where Status=0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " And ','+UserPrefixCodes+',' Like '%," + PrefixCodeID.ToString() + ",%'";
return(SysGlobal.GetExecSqlIsExist(sSqlText));
}
public static int DeleteSinglePrefixCode(int _ID)
{
string sSQL = "begin delete from SysPrefixCode_Info where Status =0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " and ID=" + _ID.ToString() + "; ";
string sLogText = "删除 系统管理>单号前缀管理：单号前缀列表:" + _ID.ToString() + "的记录。";
sSQL = sSQL + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
return(DataCommon.QueryData(sSQL));
}
public static SqlDataReader GetPrefixCodeLstByReader()
{
string sSQL = "select * from SysPrefixCode_Info where Status=0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString();
return(DataCommon.GetDataByReader(sSQL));
}
public static SqlDataReader GetSinglePrefixCodeByReader(int ID)
{
string sSQL = "select * from SysPrefixCode_Info where Status=0 and IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " and ID=" + ID.ToString();
return(DataCommon.GetDataByReader(sSQL));
}
public static SqlDataReader GetSysUserByReader(int OrganID)
{
string sSQL = "SELECT a.*,b.OrganName"
+ ",(Case IsNull(a.WorkStatus,0) When 0 then '在岗' when 1 then '出差' when 2 then '请假' when 3 then '临时外出' end) as WorkStatusName"
+ " FROM SysUser_Info a "
+ " left join SysOrgan_Info b on a.OrganID=b.ID and b.Status=0 And IsNull(b.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " WHERE a.Status=0";
sSQL = sSQL + " And a.OrganID=" + OrganID.ToString();
sSQL = sSQL + //" And IsNull(a.SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() +
" Order By a.SortID";
return(DataCommon.GetDataByReader(sSQL));
}
//获取地图列表首个ID
public static int GetTopPrefixCodeID()
{
int _TopMapID = 0;
SqlDataReader sdr = DataCommon.GetDataByReader("Select Top 1 * From SysPrefixCode_Info Where Status=0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " Order By SortID");
if (sdr.Read())
{
_TopMapID = int.Parse(sdr["ID"].ToString());
}
sdr.Close();
return(_TopMapID);
}
public static DataSet GetRoleLstByDataSet(string SearchText)
{
string sSQL = "Select * from SysRole_Info Where Status=0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString();
if (SearchText.Length > 0)
{
sSQL = sSQL + " And RoleName Like '%" + SearchText + "%'";
}
sSQL = sSQL + " Order By SortID";
return(DataCommon.GetDataByDataSet(sSQL));
}
public static SqlDataReader GetChildPurviewByRole(int PPurviewID, int RoleID)
{
string sSQL = "exec sp_RolePurview " + SysParams.GetPurviewSystemID().ToString() + "," + PPurviewID.ToString() + "," + RoleID.ToString();
return(DataCommon.GetDataByReader(sSQL));
}
public static SqlDataReader GetUserRolePurviewByReader(int UserID)
{
string sSQL = "select * from SysRole_Info where Status=0 and IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " and ID In (Select RoleID From SysUserRoles_Info Where Status=0 "// IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString()
+ " And UserID=" + UserID.ToString() + ")";
return(DataCommon.GetDataByReader(sSQL));
}
public static SqlDataReader GetUserChildPurviewByRole(int PPurviewID, int UserID, string UserRolePurview)
{
string sSQL = "exec sp_UserRolePurview " + SysParams.GetPurviewSystemID().ToString() + "," + PPurviewID.ToString() + "," + UserID.ToString() + ",'" + UserRolePurview.ToString() + "'";
return(DataCommon.GetDataByReader(sSQL));
}
public static int UpdateSingleRole(int ID, string[] FieldValues)
{
string sSqlText = "";
if (ID > 0)
{
sSqlText = "begin update SysRole_Info set RoleName='" + FieldValues.GetValue(0)
+ "',Description='" + FieldValues[1] + ""
+ "',Purview='" + FieldValues[2] + "'"
+ " WHERE ID =" + ID + ";";
string sLogText = "更新 系统管理>角色管理:角色列表：" + FieldValues.GetValue(0) + "记录。";
sSqlText += SysLogs.GetOperatorLogSQL(sLogText) + " end;";
}
else
{
sSqlText = "begin Insert Into SysRole_Info(SystemID, RoleName,Description, Purview,Status) Values(" + SysParams.GetPurviewSystemID().ToString() + ",'"
+ FieldValues.GetValue(0) + "','" + FieldValues.GetValue(1) + "','" + FieldValues.GetValue(2) + "',0 )" + ";";
string sLogText = "新增 系统管理>角色管理:角色列表：" + FieldValues.GetValue(0) + "记录。";
sSqlText = sSqlText + SysLogs.GetOperatorLogSQL(sLogText) + " End;";
}
return(DataCommon.QueryData(sSqlText));
}
//获取车站列表至控件
public static void FullToUserPrefixCodeLst(DropDownList ddlList, bool HasAll)
{
ddlList.Items.Clear();
if (HasAll)
{
ListItem liItem = new ListItem();
liItem.Value = "";
liItem.Text = "请选择";
ddlList.Items.Add(liItem);
}
string sSQL = "Select * from SysPrefixCode_Info Where Status=0 And IsNull(SystemID,0)=" + SysParams.GetPurviewSystemID().ToString() + " Order By SortID";
SqlDataReader sdr = DataCommon.GetDataByReader(sSQL);
while (sdr.Read())
{
ListItem liItem = new ListItem();
liItem.Value = sdr["ID"].ToString();
liItem.Text = sdr["PrefixCodeName"].ToString();
ddlList.Items.Add(liItem);
}
sdr.Close();
}