public string GetLayoutIDUser(string ViewCode)
{
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@ViewCode", SqlDbType.VarChar, 200, ViewCode);
p.AddParameter("@Account", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.Account);
return(dal.DBHelper.ExecuteScalarSP <string>("usp_GridViewLayout_IDOfUser", p));
}
public DataTable GetCommonSearchUser(string SearchCode, string Account)
{
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@SearchCode", SqlDbType.VarChar, 50, SearchCode);
p.AddParameter("@Account", SqlDbType.VarChar, 20, Account);
return(dal.DBHelper.GetTableSP("usp_CommonSearchUser_Search", sys_CommonSearchUser._TableName, p));
}
public DataSet GetViewLayoutLayoutID(string ViewCode, string LayoutID)
{
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@ViewCode", SqlDbType.VarChar, 200, ViewCode);
p.AddParameter("@LayoutID", SqlDbType.VarChar, 50, LayoutID);
DataSet ds = dal.DBHelper.GetDataSetSP("usp_GetViewLayoutLayoutID", p);
ds.Tables[0].TableName = sys_GridViewLayout._TableName;
ds.Tables[1].TableName = sys_GridViewLayoutDetail._TableName;
return(ds);
}
public DataSet GetViewLayoutUser(string ViewCode)
{
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@ViewCode", SqlDbType.VarChar, 200, ViewCode);
p.AddParameter("@Account", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.Account);
DataSet ds = dal.DBHelper.GetDataSetSP("usp_GetViewLayoutUser", p);
ds.Tables[0].TableName = sys_GridViewLayout._TableName;
ds.Tables[1].TableName = sys_GridViewLayoutDetail._TableName;
return(ds);
}
public bool VerifyPwdEx(string User, string Pwd)
{
Pwd = PwdEncrypt(Pwd);
const string sql = "SELECT COUNT(0) FROM dt_MyUser WHERE Account=@Account AND [Password]=@Pwd";
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@Account", SqlDbType.VarChar, 20, User);
p.AddParameter("@Pwd", SqlDbType.VarChar, 50, Pwd);
long v = DataBaseFactoryEx.CreateDataBase(Loginer.CurrentLoginer.SystemDBCode).ExecuteScalar <int>(sql, p);
return(v > 0);
}
/// <summary>
/// 验证用户名密码
/// </summary>
/// <param name="User"></param>
/// <param name="Pwd"></param>
/// <param name="dr"></param>
/// <returns></returns>
public Loginer VerifyPwd(string User, string Pwd, string LoginDBCode)
{
Pwd = PwdEncrypt(Pwd);
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@Account", SqlDbType.VarChar, User);
p.AddParameter("@Pwd", SqlDbType.VarChar, Pwd);
p.AddParameter("@LoginDBCode", SqlDbType.VarChar, LoginDBCode);
DataTable dt = DataBaseFactoryEx.CreateDataBase(Loginer.CurrentLoginer.SystemDBCode).GetTableSP("usp_UserLogin", "tmp", p);
if (dt.Rows.Count == 0)
{
return(null);
}
else
{
DataRow row = dt.Rows[0];
Loginer user = new Loginer();
user.Account = (string)row[dt_MyUser.Account];
user.UserName = (string)row[dt_MyUser.UserName];
user.IsSysAdmin = Object.Equals(row[dt_MyUser.IsSysAdmain], "Y");
user.IsSysLock = Object.Equals(row[dt_MyUser.IsSysLock], "Y");
user.LoginDBCode = ConvertLib.ToString(row[dt_MyUserDBs.DBCode]);
user.IsDBAdmin = Object.Equals(row[dt_MyUserDBs.IsDBAdmin], "Y");
user.IsDBLock = Object.Equals(row[dt_MyUserDBs.IsDBLock], "Y");
if (user.IsSysAdmin && String.IsNullOrEmpty(user.LoginDBCode))
{
user.LoginDBCode = LoginDBCode;
}
return(user);
}
//var v = DatabaseFactory.CreateDataBaseEx(Loginer.CurrentLoginer.SystemDBCode).ExecuteDataReader(sql, new { Account = User, Pwd = Pwd }, row =>
//{
// return new Loginer()
// {
// Account = (string)row[dt_MyUser.Account],
// UserName = (string)row[dt_MyUser.UserName],
// IsDBAdmin = Object.Equals(row[dt_MyUser.IsAdmain], "Y")
// };
//});
//if (v.Count() > 0)
// return v.First();
//else
// return null;
}
public DataTable GetTreeModule_User(string Account)
{
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@Account", SqlDbType.VarChar, 20, Account);
return(dal.DBHelper.GetTableSP("usp_sys_GetModules_CurrentUser", "tmp", p));
}
public System.Data.DataTable GetDBAuthority(string DBCode)
{
string sql = "SELECT * FROM dbo.sys_DataBaseListAuthority WHERE DBCode=@DBCode";
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@DBCode", SqlDbType.VarChar, 20, DBCode);
return(dal.DBHelper.GetTable(sql, sys_DataBaseListAuthority._TableName, p));
}
/// <summary>
/// 获得用户权限内的账套列表
/// </summary>
/// <param name="Account"></param>
/// <returns></returns>
public System.Data.DataTable GetUserDBList(string Account)
{
//@Account
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@Account", SqlDbType.VarChar, 20, Account);
return(dal.DBHelper.GetTableSP("usp_GetUserDBList", sys_DataBaseList._TableName, p));
}
public DataTable GetViewLayoutItems(string ViewCode)
{
string sql = " SELECT LayoutID,LayoutName FROM sys_GridViewLayout WHERE ViewCode=@ViewCode ";
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@ViewCode", SqlDbType.VarChar, 200, ViewCode);
return(dal.DBHelper.GetTable(sql, sys_GridViewLayout._TableName, p));
}
public DataTable GetDetail(string DataType)
{
string sql = "SELECT * FROM dt_CommonDicData WHERE DataType=@DataType";
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@DataType", SqlDbType.VarChar, 50, DataType);
return(dal.DBHelper.GetTable(sql, dt_CommonDicData._TableName, p));
}
public DataTable getCommonDicData(EnumCommonDicData E, string TableName)
{
var sql = "SELECT DataCode,DataName FROM dt_CommonDicData WHERE DataType=@DataType ORDER BY SortIndex";
//var dbH = DataBaseFactoryEx.CreateDataBase(Loginer.CurrentLoginer.LoginDBCode);
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@DataType", SqlDbType.VarChar, 50, E.ToString());
return(DBServices.LoginDB.GetTable(sql, TableName, p));
}
public bool DeleteModule(string Modules)
{
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@ModuleIDs", SqlDbType.VarChar, 500, Modules);
int query = dal.DBHelper.ExecuteNonQuerySP("usp_DeleteModule", p);
return(query > 0);
}
public bool DBCodeExists(string DBCode)
{
string sql = "SELECT COUNT(*) FROM dbo.sys_DataBaseList WHERE DBCode=@DBCode";
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@DBCode", SqlDbType.VarChar, 20, DBCode);
int count = dal.DBHelper.ExecuteScalar <int>(sql, p);
return(count > 0);
}
public DataTable GetDBList()
{
string sql = "";
SqlParameterProvider p = null;
if (!String.IsNullOrEmpty(Loginer.CurrentLoginer.LoginDBCode))
{
sql = "SELECT * FROM dbo.sys_DataBaseList WHERE DBCode=@DBCode";
p = new SqlParameterProvider();
p.AddParameter("@DBCode", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.LoginDBCode);
}
else
{
sql = "SELECT * FROM dbo.sys_DataBaseList";
}
return(dal.DBHelper.GetTable(sql, sys_DataBaseList._TableName, p));
}
public System.Data.DataSet GetUserModules(string Account)
{
SqlParameterProvider p1 = new GZFrameworkDemo.Business.SqlParameterProvider();
p1.AddParameter("@Account", SqlDbType.VarChar, 20, Account);
DataSet ds = null;
DBServices.DB.ExecuteTransaction(db =>
{
string sql = "SELECT IsSysAdmain FROM dt_MyUser WHERE Account=@Account";
string isAdmin = db.ExecuteScalar <string>(sql, p1);
string sqlAllModuleFunction = "SELECT * FROM sys_Modules ORDER BY Sort;" +
"SELECT *,0 AS UserAuthority FROM sys_ModulesFunction ORDER BY Sort ";
ds = dal.DBHelper.GetDataSet(sqlAllModuleFunction, null);
if ("Y".Equals(isAdmin))
{
foreach (DataRow dr in ds.Tables[1].Rows)
{
dr["UserAuthority"] = 1073741823;
}
return;
}
else
{
DataTable dtAuthority = null;
string sqlDBAdmin = "SELECT IsDbAdmin FROM dbo.dt_MyUserDBs WHERE Account=@Account AND DBCode=@DBCode";
SqlParameterProvider pDBAdmin = new SqlParameterProvider();
pDBAdmin.AddParameter("@Account", SqlDbType.VarChar, 20, Account);
pDBAdmin.AddParameter("@DBCode", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.LoginDBCode);
string isDBAdmin = db.ExecuteScalar <string>(sqlDBAdmin, pDBAdmin);
if ("Y".Equals(isDBAdmin))
{
string sql2 = "SELECT FunctionID,Authority FROM dbo.sys_DataBaseListAuthority where DBCode=@DBCode";
SqlParameterProvider p3 = new SqlParameterProvider();
p3.AddParameter("@DBCode", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.LoginDBCode);
dtAuthority = db.GetTable(sql2, "tmp", p3);
}
else
{
string sql2 = @"SELECT B.FunctionID,
A.Authority & B.Authority AS Authority
FROM
(
SELECT FunctionID,
Authority
FROM dt_MyRoleAuthority
WHERE RoleID IN (
SELECT RoleID FROM dt_MyUserRole WHERE Account = @Account
)
) AS A
INNER JOIN
(
SELECT FunctionID,
Authority
FROM dbo.sys_DataBaseListAuthority
WHERE DBCode = @DBCode
) AS B
ON A.FunctionID = B.FunctionID;";
SqlParameterProvider p3 = new SqlParameterProvider();
p3.AddParameter("@Account", SqlDbType.VarChar, 20, Account);
p3.AddParameter("@DBCode", SqlDbType.VarChar, 20, Loginer.CurrentLoginer.LoginDBCode);
dtAuthority = db.GetTable(sql2, "tmp", p3);
}
//获得总权限，
//DataTable dtAuthority = ds.Tables[2];
var query = (from t in dtAuthority.AsEnumerable()
group t by new { FunctionID = t.Field <string>("FunctionID") } into m
select new
{
FunctionID = m.Key.FunctionID,
Authority = m.Aggregate(0, (d, n) =>
{
return(d | Common.ConvertLib.ToInt(n["Authority"]));
})
}).ToList();
query.ToList().ForEach(p =>
{
ds.Tables[1].Select($"FunctionID='{p.FunctionID}'").ToList().ForEach(row =>
{
row["UserAuthority"] = p.Authority;
});
});
ds.Tables[1].Select("UserAuthority=0").ToList().ForEach(row =>
{
ds.Tables[1].Rows.Remove(row);
});
var ModuleNo = from module in ds.Tables[0].AsEnumerable()
where !ds.Tables[1].AsEnumerable().Any(y =>
y.Field <string>(sys_ModulesFunction.ModuleID) == module.Field <string>(sys_Modules.ModuleID))
select new
{
row = module,
ModuleID = module.Field <string>(sys_Modules.ModuleID)
};
ModuleNo.ToList().ForEach(r =>
{
ds.Tables[0].Rows.Remove(r.row);
});
//ds.Tables.RemoveAt(2);
ds.AcceptChanges();
}
});
ds.Tables[0].TableName = sys_Modules._TableName;
ds.Tables[1].TableName = sys_ModulesFunction._TableName;
return(ds);
}