/// <summary>
/// 删除
/// </summary>
/// <param name="lstOID"></param>
/// <returns></returns>
public bool DeleteFunction(List<string> lstOID)
{
string strSQL = @" DELETE FROM TBLFUNCTION WHERE oid=@OID ";
string[] paramNames = new string[1];
object[] paramValues = new object[1];
paramNames[0] = "OID";
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
foreach (string oid in lstOID)
{
paramValues[0] = oid;
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
}
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
return true;
}
public PersonEntity GetPersonInfo(string userName, string passWord)
{
PersonEntity pe = new PersonEntity();
string strSql = @" SELECT *
FROM TBLPERSON where personaccount=@UserName and personpassword=@PassWord";
string[] paramName=new string[2];
object[] paramValue = new object[2];
paramName[0]="UserName";
paramName[1] = "PassWord";
paramValue[0] = userName;
paramValue[1] = passWord;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql,CommandType.Text,paramName,paramValue);
broker.Close();
if (dst != null && dst.Tables[0] != null && dst.Tables[0].Rows.Count > 0)
{
return this.DataRow2Person(dst.Tables[0].Rows[0]);
}
else
{
return null;
}
}
public DataSet GetAllFunctions()
{
string strSql = " SELECT * FROM TBLFUNCTION ORDER BY functionorder ";
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql);
broker.Close();
return dst;
}
/// <summary>
/// 保存功能组菜单权限
/// </summary>
/// <param name="entitys"></param>
/// <returns></returns>
public bool SaveGroupFunction(List<GroupFunctionMapEntity> entitys)
{
if (entitys.Count > 0)
{
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
string strDelSQL = " DELETE FROM dbo.TBLGroupFunctionMap WHERE groupID=@GroupID ";
string[] paramNames1 = new string[1];
object[] paramValues1 = new object[1];
paramNames1[0] = "GroupID";
paramValues1[0] = entitys[0].GroupID;
broker.ExecuteNonQuery(strDelSQL, CommandType.Text, paramNames1, paramValues1);
string strSQL = @" INSERT INTO dbo.TBLGroupFunctionMap
( oid , groupID , functionID , cuser , cdate , muser , mdate , addition1 , addition2 )
VALUES ( @OID,@GroupID ,@FunctionID,@Cuser,getdate(),@Muser ,getdate() ,NULL,NULL) ";
string[] paramNames = new string[5];
object[] paramValues = new object[5];
foreach (GroupFunctionMapEntity en in entitys)
{
paramNames[0] = "OID";
paramNames[1] = "GroupID";
paramNames[2] = "FunctionID";
paramNames[3] = "Cuser";
paramNames[4] = "Muser";
paramValues[0] = en.OID;
paramValues[1] = en.GroupID;
paramValues[2] = en.FunctionID;
paramValues[3] = en.CUSER;
paramValues[4] = en.MUSER;
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
}
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
public string GetChildMaxOrder(string oid)
{
string strSQL = @" SELECT COUNT(1)+1 FROM TBLFUNCTION WHERE functionparentid=@OID ";
string[] paramNames = new string[1];
object[] paramValues = new object[1];
paramNames[0] = "OID";
paramValues[0] = oid;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
return broker.ExecuteScalar(strSQL, CommandType.Text, paramNames, paramValues);
}
/// <summary>
/// 根据群组查询权限
/// </summary>
/// <param name="groupID"></param>
/// <returns></returns>
public DataTable GetFuncByGroup(string groupID)
{
string strSQL = @" SELECT * FROM dbo.TBLGroupFunctionMap WHERE groupID=@GroupID ";
string[] paramNames = new string[1];
object[] paramValues = new object[1];
paramNames[0] = "GroupID";
paramValues[0] = groupID;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSQL, CommandType.Text, paramNames, paramValues);
broker.Close();
return dst.Tables[0];
}
public FunctionEntity GetFunc(string oid)
{
string strSQL = @" SELECT * FROM TBLFUNCTION WHERE oid=@OID ";
string[] paramNames = new string[1];
object[] paramValues = new object[1];
paramNames[0] = "OID";
paramValues[0] = oid;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSQL,CommandType.Text,paramNames,paramValues);
broker.Close();
if (dst != null && dst.Tables[0] != null && dst.Tables[0].Rows.Count > 0)
{
return Datarow2Entity(dst.Tables[0].Rows[0]);
}
else
{ return null; }
}
public PermissionGroupEntity GetGroupInfo(string personID)
{
PermissionGroupEntity pe = new PermissionGroupEntity();
string strSql = @" SELECT * FROM TBLPermissionGroup WHERE oid=@OID ";
string[] paramName = new string[1];
object[] paramValue = new object[1];
paramName[0] = "OID";
paramValue[0] = personID;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql, CommandType.Text, paramName, paramValue);
broker.Close();
if (dst != null && dst.Tables[0] != null && dst.Tables[0].Rows.Count > 0)
{
return this.DataRow2Group(dst.Tables[0].Rows[0]);
}
else
{
return null;
}
}
/// <summary>
/// 删除群组下的人
/// </summary>
/// <param name="personIDS"></param>
/// <param name="groupID"></param>
/// <returns></returns>
public bool DeleteGroupPersonByPersonID(List<string> personIDS,string groupID)
{
if (personIDS.Count > 0)
{
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
string strDelSQL = " DELETE FROM dbo.TBLGroupPersonMap WHERE PersonID=@PersonID AND groupID=@GroupID ";
string[] paramNames = new string[2];
object[] paramValues = new object[2];
paramNames[0] = "GroupID";
paramNames[1] = "PersonID";
paramValues[0] = groupID;
foreach (string personid in personIDS)
{
paramValues[1] = personid;
broker.ExecuteNonQuery(strDelSQL, CommandType.Text, paramNames, paramValues);
}
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
/// <summary>
/// 新增
/// </summary>
/// <param name="fun"></param>
/// <returns></returns>
public bool InsertFunction(FunctionEntity fun)
{
if (fun != null)
{
string strSQL = @" INSERT INTO TBLFUNCTION
( oid , functionkey , functionname , functionlevel , functionparentid , functionurl ,functionstatus ,
functionorder , functiontype , memo ,cuser ,cdate ,muser ,mdate ,addition1 ,addition2 )
VALUES ( @OID ,@functionkey ,@functionname ,@functionlevel ,@functionparentid ,@functionurl ,@functionstatus ,
@functionorder ,@functiontype , @memo ,@cuser ,GETDATE() , @muser ,GETDATE() ,NULL ,NULL) ";
string[] paramNames = new string[12];
object[] paramValues = new object[12];
paramNames[0] = "OID";
paramNames[1] = "functionkey";
paramNames[2] = "functionname";
paramNames[3] = "functionlevel";
paramNames[4] = "functionparentid";
paramNames[5] = "functionurl";
paramNames[6] = "functionstatus";
paramNames[7] = "functionorder";
paramNames[8] = "functiontype";
paramNames[9] = "memo";
paramNames[10] = "cuser";
paramNames[11] = "muser";
paramValues[0] = fun.OID;
paramValues[1] = fun.FUNCTIONKEY;
paramValues[2] = fun.FUNCTIONNAME;
paramValues[3] = fun.FUNCTIONLEVEL;
paramValues[4] = fun.FUNCTIONPARENTID;
paramValues[5] = fun.FUNCTIONURL;
paramValues[6] = fun.FUNCTIONSTATUS;
paramValues[7] = fun.FUNCTIONORDER;
paramValues[8] = fun.FUNCTIONTYPE;
paramValues[9] = fun.MEMO;
paramValues[10] = fun.CUSER;
paramValues[11] = fun.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
public bool InsertGroup(PermissionGroupEntity group)
{
if (group != null)
{
string strSQL = @" INSERT INTO dbo.TBLPermissionGroup( oid , groupcode , groupname , groupstatus ,memo,
cuser , cdate , muser , mdate , addition1 , addition2 )
VALUES ( @OID , @GroupCode , @GroupName , @GroupStatus,@Memo,
@Cuser , GETDATE(), @Muser,GETDATE(),NULL, NULL) ";
string[] paramNames = new string[7];
object[] paramValues = new object[7];
paramNames[0] = "OID";
paramNames[1] = "GroupCode";
paramNames[2] = "GroupName";
paramNames[3] = "GroupStatus";
paramNames[4] = "Memo";
paramNames[5] = "Cuser";
paramNames[6] = "Muser";
paramValues[0] = group.OID;
paramValues[1] = group.GroupCode;
paramValues[2] = group.GroupName;
paramValues[3] = group.GroupStatus;
paramValues[4] = group.MEMO;
paramValues[5] = group.CUSER;
paramValues[6] = group.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
/// <summary>
/// 更新
/// </summary>
/// <param name="fun"></param>
/// <returns></returns>
public bool UpdateFunction(FunctionEntity fun)
{
if (fun != null)
{
string strSQL = @" UPDATE TBLFUNCTION SET functionkey=@functionkey,functionname=@functionname, functionurl=@functionurl ,
functionstatus=@functionstatus ,functionorder=@functionorder,memo=@memo,muser=@muser,mdate=GETDATE()
WHERE oid=@OID ";
string[] paramNames = new string[8];
object[] paramValues = new object[8];
paramNames[0] = "OID";
paramNames[1] = "functionkey";
paramNames[2] = "functionname";
paramNames[3] = "functionurl";
paramNames[4] = "functionstatus";
paramNames[5] = "functionorder";
paramNames[6] = "memo";
paramNames[7] = "muser";
paramValues[0] = fun.OID;
paramValues[1] = fun.FUNCTIONKEY;
paramValues[2] = fun.FUNCTIONNAME;
paramValues[3] = fun.FUNCTIONURL;
paramValues[4] = fun.FUNCTIONSTATUS;
paramValues[5] = fun.FUNCTIONORDER;
paramValues[6] = fun.MEMO;
paramValues[7] = fun.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
/// <summary>
/// 查询功能列表
/// </summary>
/// <param name="funcCode"></param>
/// <param name="funcName"></param>
/// <returns></returns>
public DataSet QueryFunctions(string funcCode,string funcName)
{
string strSql = @" SELECT *
FROM dbo.TBLFUNCTION
WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(funcCode))
{
strSql += " and p.functionkey = FunCode ";
}
if (!string.IsNullOrEmpty(funcName))
{
strSql += " and p.functionname = FunName ";
}
strSql += " ORDER BY functionorder ";
string[] paramNames = new string[2];
object[] paramValues = new object[2];
paramNames[0] = "FunCode";
paramNames[1] = "FunName";
paramValues[0] = funcCode;
paramValues[1] = funcName;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.ExecuteDataset(strSql);
broker.Close();
return dst;
}
public bool UpdateGroup(PermissionGroupEntity group)
{
if (group != null)
{
string strSQL = @" UPDATE TBLPermissionGroup SET groupcode=@GroupCode,groupname=@GroupName,groupstatus=@GroupStatus,
memo=@Memo,muser=@Muser,mdate=GETDATE() WHERE oid=@OID ";
string[] paramNames = new string[6];
object[] paramValues = new object[6];
paramNames[0] = "OID";
paramNames[1] = "GroupCode";
paramNames[2] = "GroupName";
paramNames[3] = "GroupStatus";
paramNames[4] = "Memo";
paramNames[5] = "Muser";
paramValues[0] = group.OID;
paramValues[1] = group.GroupCode;
paramValues[2] = group.GroupName;
paramValues[3] = group.GroupStatus;
paramValues[4] = group.MEMO;
paramValues[5] = group.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
public bool InsertPerson(PersonEntity per)
{
if (per != null)
{
string strSQL = @" INSERT INTO TBLPERSON ( oid , personcode , personaccount , personpassword , personname , personsex ,
personstatus , persontype , personofficephone , personmobilephone , personemail ,
personmemo , cuser , cdate , muser , mdate , mpwdtime , personpasswordquestion ,
personpasswordanswer , addition1 , addition2 )
VALUES ( @oid, @personcode,@personaccount,@personpassword,@personname,@personsex,
@personstatus,@persontype,@personofficephone,@personmobilephone,@personemail,
@personmemo,@cuser, GETDATE(),@muser,GETDATE(),GETDATE(),' ' ,' ', NULL,NULL) ";
string[] paramNames = new string[14];
object[] paramValues = new object[14];
paramNames[0] = "OID";
paramNames[1] = "personcode";
paramNames[2] = "personaccount";
paramNames[3] = "personpassword";
paramNames[4] = "personname";
paramNames[5] = "personsex";
paramNames[6] = "personstatus";
paramNames[7] = "persontype";
paramNames[8] = "personofficephone";
paramNames[9] = "personmobilephone";
paramNames[10] = "personemail";
paramNames[11] = "personmemo";
paramNames[12] = "cuser";
paramNames[13] = "muser";
paramValues[0] = per.OID;
paramValues[1] = per.PERSONCODE;
paramValues[2] = per.PERSONACCOUNT;
paramValues[3] = per.PERSONPASSWORD??" ";
paramValues[4] = per.PERSONNAME;
paramValues[5] = per.PERSONSEX;
paramValues[6] = per.PERSONSTATUS;
paramValues[7] = per.PERSONTYPE;
paramValues[8] = per.PERSONOFFICEPHONE;
paramValues[9] = per.PERSONMOBILEPHONE;
paramValues[10] = per.PERSONEMAIL;
paramValues[11] = per.PERSONMEMO;
paramValues[12] = per.CUSER;
paramValues[13] = per.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
public bool UpdatePersonAdmin(PersonEntity per)
{
if (per != null)
{
string strSQL = @" UPDATE TBLPERSON SET personcode=@personcode,personaccount=@personaccount,personname=@personname,personsex=@personsex,
personstatus=@personstatus,persontype=@persontype,personofficephone=@personofficephone,
personmobilephone=@personmobilephone,personemail=@personemail,personmemo=@personmemo,muser=@muser,mdate=GETDATE()
WHERE oid=@oid ";
string[] paramNames = new string[12];
object[] paramValues = new object[12];
paramNames[0] = "OID";
paramNames[1] = "personcode";
paramNames[2] = "personaccount";
paramNames[3] = "personname";
paramNames[4] = "personsex";
paramNames[5] = "personstatus";
paramNames[6] = "persontype";
paramNames[7] = "personofficephone";
paramNames[8] = "personmobilephone";
paramNames[9] = "personemail";
paramNames[10] = "personmemo";
paramNames[11] = "muser";
paramValues[0] = per.OID;
paramValues[1] = per.PERSONCODE;
paramValues[2] = per.PERSONACCOUNT;
paramValues[3] = per.PERSONNAME;
paramValues[4] = per.PERSONSEX;
paramValues[5] = per.PERSONSTATUS;
paramValues[6] = per.PERSONTYPE;
paramValues[7] = per.PERSONOFFICEPHONE;
paramValues[8] = per.PERSONMOBILEPHONE;
paramValues[9] = per.PERSONEMAIL;
paramValues[10] = per.PERSONMEMO;
paramValues[11] = per.MUSER;
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
/// <summary>
/// 查询群组人员
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowCount"></param>
/// <param name="userAccount"></param>
/// <param name="userName"></param>
/// <param name="personType"></param>
/// <returns></returns>
public DataSet QueryPersons(int pageIndex, int pageSize, out int rowCount, string userAccount, string userName, string personType, string groupID)
{
string strSql = @" SELECT TBLPERSON.oid ,
TBLPERSON.personaccount ,
TBLPERSON.personname ,
TBLPERSON.personsex ,
TBLPERSON.personstatus ,
TBLPERSON.personofficephone ,
TBLPERSON.personmobilephone ,
TBLPERSON.personemail ,
TBLPERSON.personmemo ,
persontype
FROM TBLGroupPersonMap
INNER JOIN TBLPERSON ON TBLGroupPersonMap.PersonID = TBLPERSON.oid
WHERE 1 = 1 ";
if (!string.IsNullOrEmpty(userAccount))
{
strSql += " and personaccount like @PersonAccount ";
}
if (!string.IsNullOrEmpty(userName))
{
strSql += " and personname like @PersonName ";
}
if (!string.IsNullOrEmpty(personType))
{
strSql += " and persontype = @PersonType ";
}
if (!string.IsNullOrEmpty(groupID))
{
strSql += " AND TBLGroupPersonMap.groupID=@GroupID ";
}
string[] paramNames = new string[4];
object[] paramValues = new object[4];
paramNames[0] = "PersonAccount";
paramNames[1] = "PersonName";
paramNames[2] = "PersonType";
paramNames[3] = "GroupID";
paramValues[0] = "%" + userAccount + "%";
paramValues[1] = "%" + userName + "%";
paramValues[2] = personType;
paramValues[3] = groupID;
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.QueryPageFromSql(strSql, paramNames, paramValues, pageIndex, pageSize, out rowCount);
broker.Close();
return dst;
}
/// <summary>
/// 保存功能组人员
/// </summary>
/// <param name="entitys"></param>
/// <returns></returns>
public bool SaveGroupPerson(List<string> personIDS,string groupID,string editor)
{
if (personIDS.Count > 0)
{
SqlDBBroker broker = new SqlDBBroker();
try
{
broker.Open();
broker.BeginTrans();
string strDelSQL = " DELETE FROM dbo.TBLGroupPersonMap WHERE groupID=@GroupID ";
string[] paramNames1 = new string[1];
object[] paramValues1 = new object[1];
paramNames1[0] = "GroupID";
paramValues1[0] = groupID;
broker.ExecuteNonQuery(strDelSQL, CommandType.Text, paramNames1, paramValues1);
string strSQL = @" INSERT INTO dbo.TBLGroupPersonMap
( oid , groupID , personID , cuser , cdate , muser , mdate , addition1 , addition2 )
VALUES ( @OID,@GroupID ,@PersonID,@Cuser,getdate(),@Muser ,getdate() ,NULL,NULL) ";
string[] paramNames = new string[5];
object[] paramValues = new object[5];
foreach (string personid in personIDS)
{
paramNames[0] = "OID";
paramNames[1] = "GroupID";
paramNames[2] = "PersonID";
paramNames[3] = "Cuser";
paramNames[4] = "Muser";
paramValues[0] = Guid.NewGuid().ToString();
paramValues[1] = groupID;
paramValues[2] = personid;
paramValues[3] = editor;
paramValues[4] = editor;
broker.ExecuteNonQuery(strSQL, CommandType.Text, paramNames, paramValues);
}
broker.CommitTrans();
}
catch
{
broker.RollbackTrans();
return false;
}
finally
{
broker.Close();
}
}
return true;
}
public DataSet QueryPersons(int pageIndex,int pageSize,out int rowCount,string userAccount,string userName)
{
string strSql = @" SELECT oid ,
personaccount ,
personname ,
personsex ,
personofficephone ,
personmobilephone ,
personemail ,
personmemo
FROM TBLPERSON where 1=1 ";
if (!string.IsNullOrEmpty(userAccount))
{
strSql += " and personaccount like @PersonAccount ";
}
if (!string.IsNullOrEmpty(userName))
{
strSql += " and personname like @PersonName ";
}
string[] paramNames = new string[2];
object[] paramValues = new object[2];
paramNames[0] = "PersonAccount";
paramNames[1] = "PersonName";
paramValues[0] = "%" + userAccount + "%";
paramValues[1] = "%" + userName + "%";
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.QueryPageFromSql(strSql,paramNames,paramValues,pageIndex,pageSize,out rowCount);
broker.Close();
return dst;
}
public DataSet QueryGroupPermissions(int pageIndex, int pageSize, out int rowCount, string groupCode, string groupName)
{
string strSql = @" SELECT * FROM TBLPermissionGroup WHERE 1=1 ";
if (!string.IsNullOrEmpty(groupCode))
{
strSql += " and groupcode like @GroupCode ";
}
if (!string.IsNullOrEmpty(groupName))
{
strSql += " and groupname like @GroupName ";
}
string[] paramNames = new string[2];
object[] paramValues = new object[2];
paramNames[0] = "GroupCode";
paramNames[1] = "GroupName";
paramValues[0] = "%" + groupCode + "%";
paramValues[1] = "%" + groupName + "%";
SqlDBBroker broker = new SqlDBBroker();
broker.Open();
DataSet dst = broker.QueryPageFromSql(strSql, paramNames, paramValues, pageIndex, pageSize, out rowCount);
broker.Close();
return dst;
}