using
System;
using System.Data;
using System.Data.SqlClient;
using SEM.DataEntity;
using COM.Makinfo.DataAccess;
namespace SEM.DataAccess.SysManage.RightManage
{
/**//// <summary>
/// DASysUser 的摘要说明。
/// </summary>
public class DASysUser:DBAccess
{
private DataTable dataSysUser;
public DASysUser()
{
}
read#region read
public DataTable ReadAllData(string strIsUsed,string strusername,string strusercode,string strareacode)
{
this.dataSysUser = OsdSysUser.NewDataTable();
//Generate where condition string.
string strWhereCo = "";
if(null != strIsUsed && strIsUsed != "")
{
strWhereCo += " and UserIsUse = '" + strIsUsed +"'";
}
if(null != strusername && strusername != "")
{
strWhereCo += " and UserName like '%" + strusername +"%'";
}
if(null != strusercode && strusercode != "")
{
strWhereCo += " and UserCode like '%" + strusercode.ToLower() +"%'";
}
if(null != strareacode && strareacode != "")
{
strWhereCo += " and AreaCode = '" + strareacode +"'";
}
string sql = " SELECT * from SysUser ";
if(strWhereCo != "")
{
strWhereCo = strWhereCo.Substring(4);
sql = sql + " Where " + strWhereCo;
}
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 通过部门编码获取所有的用户,注意结构 必须是,aa,aaa,这种数据结构才行,否则数据会出错。
/// </summary>
/// <param name="depcode"></param>
/// <returns></returns>
//由于数据库设计的改变,这个方法有所改变,现在变成了从SysUserPost取数
public DataTable GetAllUserByDepCode(string depcode)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = " SELECT * from SysUser where usercode in (select distinct usercode from SysUserDept where Depguid = '" + depcode +"')";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 读取人口职位关联表中的数据。
/// </summary>
/// <param name="postcode"></param>
/// <param name="deptcode"></param>
/// <returns></returns>
public DataTable ReadDataByUserPost(string strAreaCode,string strDepGuid,string strPostGuid)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = " SELECT * from SysUser where usercode in "+
"(select distinct usercode from SysUserPost "+
" where areaCode='"+ strAreaCode+"' and DepGuid = '" + strDepGuid +"' and postGuid='" + strPostGuid + "')";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 读取人口角色关联表中的数据。
/// </summary>
/// <param name="strAreaCode"></param>
/// <param name="strRoleGuid"></param>
/// <returns></returns>
public DataTable ReadDataByUserRole(string strAreaCode,string strRoleGuid)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = " SELECT * from SysUser where usercode in "+
"(select distinct usercode from SysUserRole "+
" where areaCode='"+ strAreaCode+"' and RoleGuid = '" + strRoleGuid + "')";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
///
/// </summary>
/// <param name="aryIDs"></param>
/// <returns></returns>
public DataTable ReadDataByIDs(string[] aryIDs)
{
this.dataSysUser = OsdSysUser.NewDataTable();
//Generate where condition string.
string strWhereCo = "";
foreach(string strValue in aryIDs)
{
strWhereCo += " or UserCode = '" + strValue.ToLower() +"'";
}
strWhereCo = strWhereCo.Substring(4);
string sql = " SELECT * from SysUser "+
" Where " + strWhereCo;
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 根据GUID(数组)查询人员信息(Daizh)
/// </summary>
/// <param name="aryGUIDs"></param>
/// <returns></returns>
public DataTable ReadDataByGUIDs(string[] aryGUIDs)
{
this.dataSysUser = OsdSysUser.NewDataTable();
//Generate where condition string.
string strWhereCo = "";
foreach(string strValue in aryGUIDs)
{
strWhereCo += " or UserGuid = '" + strValue.ToLower() +"'";
}
strWhereCo = strWhereCo.Substring(4);
string sql = " SELECT * from SysUser "+
" Where " + strWhereCo;
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 根据GUID查询人员信息(Daizh)
/// </summary>
/// <param name="aryIDs"></param>
/// <returns></returns>
public DataTable ReadDataByGuid(string strGuid)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = "SELECT * from SysUser Where UserGuid = '"+ strGuid + "'";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 根据Code查询人员信息(Daizh)
/// </summary>
/// <param name="aryIDs"></param>
/// <returns></returns>
public DataTable ReadUserNameByCode(string strCode)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string strSQLCommand = "SELECT * FROM SysUser Where UserCode = '" + strCode + "'";
this.InitDataSetTableBySQL(ref dataSysUser,strSQLCommand);
return dataSysUser;
}
/**//// <summary>
/// 得取地区数组和部门数组的所有用户 (Fenglx)
/// </summary>
/// <param name="strDept">部门数组</param>
/// <param name="strDept">部门数组</param>
/// <returns>用户</returns>
public DataTable ReadDataArrayUserCode(string[] strArea,string[] strDept) {
this.dataSysUser = OsdSysUser.NewDataTable();
string strAreaCode = "",strDeptCode = "";
for (int i=0 ; i < strArea.Length ; i++) {
strAreaCode += ",'" + strArea[i] + "'";
}
for (int i=0 ; i < strDept.Length ; i++) {
strDeptCode += ",'" + strDept[i] + "'";
}
strAreaCode = strAreaCode.Substring(1);
strDeptCode = strDeptCode.Substring(1);
// string sql = " SELECT DISTINCT SysUser.UserName, SysUser.UserGuid" +
// " FROM SysUserDept INNER JOIN SysUser ON SysUserDept.AreaCode = SysUser.AreaCode AND SysUserDept.UserCode = SysUser.UserCode" +
// " WHERE SysUserDept.AreaCode IN (" + strAreaCode + ") AND SysUserDept.DepGuid IN (" + strDeptCode + ")";
string sql = " SELECT DISTINCT SysUser.UserName, SysUser.UserGuid" +
" FROM SysUserDept INNER JOIN SysUser ON SysUserDept.UserCode = SysUser.UserCode" +
" WHERE SysUserDept.AreaCode IN (" + strAreaCode + ") AND SysUserDept.DepGuid IN (" + strDeptCode + ")";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
#endregion read
create#region create
/**//// <summary>
/// Create new income type info.
/// </summary>
/// <param name="defineData"></param>
public void Create(DataTable dataSysUser)
{
IDbCommand[] insert = this.GetInsertCommand(dataSysUser);
//Excute command list.
this.ExcuteCmdList(insert);
}
#endregion create
update#region update
/**//// <summary>
/// Create income type info.
/// </summary>
/// <param name="dataSysUser"></param>
public void Update(DataTable dataSysUser)
{
string[] strUpdateColumns = OsdSysUser.Columns;
string[] strPKColumns = {OsdSysUser.Col_UserGuid};
IDbCommand[] update = this.GetUpdateCommand(dataSysUser,OsdSysUser.Columns,strPKColumns);
//Excute command list.
this.ExcuteCmdList(update);
}
/**//// <summary>
/// 修改密码
/// </summary>
/// <param name="dataSysUser"></param>
public void UpdatePass(DataTable dataSysUser)
{
string[] strUpdateColumns = {OsdSysUser.Col_UserCode,OsdSysUser.Col_UserPass};
string[] strPKColumns = {OsdSysUser.Col_UserCode};
IDbCommand[] update = this.GetUpdateCommand(dataSysUser,strUpdateColumns,strPKColumns);
//Excute command list.
this.ExcuteCmdList(update);
}
#endregion update
delete#region delete
/**//// <summary>
///
/// </summary>
/// <param name="aryIDs"></param>
public void DeleteDataByIDs(string[] aryIDs)
{
IDbCommand delete = InstanceComm;
//Generate where condition string.
string strWhereCo = "";
foreach(string strValue in aryIDs)
{
strWhereCo += " or UserCode ='" + strValue.ToLower() +"' ";
}
strWhereCo = strWhereCo.Substring(4);
if(strWhereCo == "")
return;
delete.CommandText =
" update SysUser set UserIsUse ='F' "+
" Where " + strWhereCo;
//Excute command list.
this.ExcuteCmdList(new IDbCommand[]{delete});
}
#endregion delete
}
}
using System.Data;
using System.Data.SqlClient;
using SEM.DataEntity;
using COM.Makinfo.DataAccess;
namespace SEM.DataAccess.SysManage.RightManage
{
/**//// <summary>
/// DASysUser 的摘要说明。
/// </summary>
public class DASysUser:DBAccess
{
private DataTable dataSysUser;
public DASysUser()
{
}
read#region read
public DataTable ReadAllData(string strIsUsed,string strusername,string strusercode,string strareacode)
{
this.dataSysUser = OsdSysUser.NewDataTable();
//Generate where condition string.
string strWhereCo = "";
if(null != strIsUsed && strIsUsed != "")
{
strWhereCo += " and UserIsUse = '" + strIsUsed +"'";
}
if(null != strusername && strusername != "")
{
strWhereCo += " and UserName like '%" + strusername +"%'";
}
if(null != strusercode && strusercode != "")
{
strWhereCo += " and UserCode like '%" + strusercode.ToLower() +"%'";
}
if(null != strareacode && strareacode != "")
{
strWhereCo += " and AreaCode = '" + strareacode +"'";
}
string sql = " SELECT * from SysUser ";
if(strWhereCo != "")
{
strWhereCo = strWhereCo.Substring(4);
sql = sql + " Where " + strWhereCo;
}
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 通过部门编码获取所有的用户,注意结构 必须是,aa,aaa,这种数据结构才行,否则数据会出错。
/// </summary>
/// <param name="depcode"></param>
/// <returns></returns>
//由于数据库设计的改变,这个方法有所改变,现在变成了从SysUserPost取数
public DataTable GetAllUserByDepCode(string depcode)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = " SELECT * from SysUser where usercode in (select distinct usercode from SysUserDept where Depguid = '" + depcode +"')";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 读取人口职位关联表中的数据。
/// </summary>
/// <param name="postcode"></param>
/// <param name="deptcode"></param>
/// <returns></returns>
public DataTable ReadDataByUserPost(string strAreaCode,string strDepGuid,string strPostGuid)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = " SELECT * from SysUser where usercode in "+
"(select distinct usercode from SysUserPost "+
" where areaCode='"+ strAreaCode+"' and DepGuid = '" + strDepGuid +"' and postGuid='" + strPostGuid + "')";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 读取人口角色关联表中的数据。
/// </summary>
/// <param name="strAreaCode"></param>
/// <param name="strRoleGuid"></param>
/// <returns></returns>
public DataTable ReadDataByUserRole(string strAreaCode,string strRoleGuid)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = " SELECT * from SysUser where usercode in "+
"(select distinct usercode from SysUserRole "+
" where areaCode='"+ strAreaCode+"' and RoleGuid = '" + strRoleGuid + "')";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
///
/// </summary>
/// <param name="aryIDs"></param>
/// <returns></returns>
public DataTable ReadDataByIDs(string[] aryIDs)
{
this.dataSysUser = OsdSysUser.NewDataTable();
//Generate where condition string.
string strWhereCo = "";
foreach(string strValue in aryIDs)
{
strWhereCo += " or UserCode = '" + strValue.ToLower() +"'";
}
strWhereCo = strWhereCo.Substring(4);
string sql = " SELECT * from SysUser "+
" Where " + strWhereCo;
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 根据GUID(数组)查询人员信息(Daizh)
/// </summary>
/// <param name="aryGUIDs"></param>
/// <returns></returns>
public DataTable ReadDataByGUIDs(string[] aryGUIDs)
{
this.dataSysUser = OsdSysUser.NewDataTable();
//Generate where condition string.
string strWhereCo = "";
foreach(string strValue in aryGUIDs)
{
strWhereCo += " or UserGuid = '" + strValue.ToLower() +"'";
}
strWhereCo = strWhereCo.Substring(4);
string sql = " SELECT * from SysUser "+
" Where " + strWhereCo;
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 根据GUID查询人员信息(Daizh)
/// </summary>
/// <param name="aryIDs"></param>
/// <returns></returns>
public DataTable ReadDataByGuid(string strGuid)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string sql = "SELECT * from SysUser Where UserGuid = '"+ strGuid + "'";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
/**//// <summary>
/// 根据Code查询人员信息(Daizh)
/// </summary>
/// <param name="aryIDs"></param>
/// <returns></returns>
public DataTable ReadUserNameByCode(string strCode)
{
this.dataSysUser = OsdSysUser.NewDataTable();
string strSQLCommand = "SELECT * FROM SysUser Where UserCode = '" + strCode + "'";
this.InitDataSetTableBySQL(ref dataSysUser,strSQLCommand);
return dataSysUser;
}
/**//// <summary>
/// 得取地区数组和部门数组的所有用户 (Fenglx)
/// </summary>
/// <param name="strDept">部门数组</param>
/// <param name="strDept">部门数组</param>
/// <returns>用户</returns>
public DataTable ReadDataArrayUserCode(string[] strArea,string[] strDept) {
this.dataSysUser = OsdSysUser.NewDataTable();
string strAreaCode = "",strDeptCode = "";
for (int i=0 ; i < strArea.Length ; i++) {
strAreaCode += ",'" + strArea[i] + "'";
}
for (int i=0 ; i < strDept.Length ; i++) {
strDeptCode += ",'" + strDept[i] + "'";
}
strAreaCode = strAreaCode.Substring(1);
strDeptCode = strDeptCode.Substring(1);
// string sql = " SELECT DISTINCT SysUser.UserName, SysUser.UserGuid" +
// " FROM SysUserDept INNER JOIN SysUser ON SysUserDept.AreaCode = SysUser.AreaCode AND SysUserDept.UserCode = SysUser.UserCode" +
// " WHERE SysUserDept.AreaCode IN (" + strAreaCode + ") AND SysUserDept.DepGuid IN (" + strDeptCode + ")";
string sql = " SELECT DISTINCT SysUser.UserName, SysUser.UserGuid" +
" FROM SysUserDept INNER JOIN SysUser ON SysUserDept.UserCode = SysUser.UserCode" +
" WHERE SysUserDept.AreaCode IN (" + strAreaCode + ") AND SysUserDept.DepGuid IN (" + strDeptCode + ")";
this.InitDataSetTableBySQL(ref dataSysUser,sql);
return dataSysUser;
}
#endregion read
create#region create
/**//// <summary>
/// Create new income type info.
/// </summary>
/// <param name="defineData"></param>
public void Create(DataTable dataSysUser)
{
IDbCommand[] insert = this.GetInsertCommand(dataSysUser);
//Excute command list.
this.ExcuteCmdList(insert);
}
#endregion create
update#region update
/**//// <summary>
/// Create income type info.
/// </summary>
/// <param name="dataSysUser"></param>
public void Update(DataTable dataSysUser)
{
string[] strUpdateColumns = OsdSysUser.Columns;
string[] strPKColumns = {OsdSysUser.Col_UserGuid};
IDbCommand[] update = this.GetUpdateCommand(dataSysUser,OsdSysUser.Columns,strPKColumns);
//Excute command list.
this.ExcuteCmdList(update);
}
/**//// <summary>
/// 修改密码
/// </summary>
/// <param name="dataSysUser"></param>
public void UpdatePass(DataTable dataSysUser)
{
string[] strUpdateColumns = {OsdSysUser.Col_UserCode,OsdSysUser.Col_UserPass};
string[] strPKColumns = {OsdSysUser.Col_UserCode};
IDbCommand[] update = this.GetUpdateCommand(dataSysUser,strUpdateColumns,strPKColumns);
//Excute command list.
this.ExcuteCmdList(update);
}
#endregion update
delete#region delete
/**//// <summary>
///
/// </summary>
/// <param name="aryIDs"></param>
public void DeleteDataByIDs(string[] aryIDs)
{
IDbCommand delete = InstanceComm;
//Generate where condition string.
string strWhereCo = "";
foreach(string strValue in aryIDs)
{
strWhereCo += " or UserCode ='" + strValue.ToLower() +"' ";
}
strWhereCo = strWhereCo.Substring(4);
if(strWhereCo == "")
return;
delete.CommandText =
" update SysUser set UserIsUse ='F' "+
" Where " + strWhereCo;
//Excute command list.
this.ExcuteCmdList(new IDbCommand[]{delete});
}
#endregion delete
}
}