public static IEnumerable <UserGroup> Get(int page, int maxCount)
{
var startIndex = (page - 1) * maxCount;
using (var dal = DalBuilder.CreateDal(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
int i;
var rstDt = dal.Select(
"select * from TUSERGROUP",
startIndex, maxCount, out i);
return(ObjectHelper.BuildObject <UserGroup>(rstDt));
}
}
/// <summary>
/// 列表用户
/// </summary>
/// <returns></returns>
public static ICollection <User> ListUser()
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
dal.OpenReader("SELECT ID,UserCode,UserName,IsAdmin,Enabled FROM tUser");
ICollection <User> users = ObjectHelper.BuildObject <User>(dal.DataReader);
return(users);
}
}
catch
{
throw;
}
}
/// <summary>
/// 列表数据源
/// </summary>
/// <returns></returns>
public static ICollection <Database> ListDatabase()
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
dal.OpenReader("SELECT ID,DbCode,DbName,DbType,Remark FROM tDatabase");
ICollection <Database> rst = ObjectHelper.BuildObject <Database>(dal.DataReader);
return(rst);
}
}
catch
{
return(null);
}
}
public static ICollection <UserReport> ListUserReport(decimal userID)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(128);
sql.Append(" SELECT B.ID as ReportID,ReportName,UserID,Enabled FROM tUserReport A,tReport B WHERE A.ReportID=B.ID AND UserID=@UserID AND Enabled=1");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@UserID", userID)
);
return(ObjectHelper.BuildObject <UserReport>(dal.DataReader));
}
}
catch
{
return(null);
}
}
/// <summary>
/// 列表报表
/// 仅报表头信息，不包含数据，字段等
/// </summary>
/// <returns></returns>
public static ICollection <Report> ListReport()
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
ICollection <Report> rst;
StringBuilder sql = new StringBuilder(256);
sql.Append(" SELECT A.ID,DBID,DBCode ,ReportName ,Enabled ,A.Remark ");
sql.Append(" FROM tReport A, tDatabase B ");
sql.Append(" WHERE A.DBID=B.ID ");
dal.OpenReader(sql.ToString());
rst = ObjectHelper.BuildObject <Report>(dal.DataReader);
return(rst);
}
}
catch
{
return(null);
}
}
/// <summary>
/// 获取用户权限报表
/// </summary>
/// <returns></returns>
public static ICollection <UserReport> ListAllUserReport(decimal userID)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(128);
sql.Append(" SELECT ID AS ReportID,@UserID AS UserID,CASE ISNULL(B.ReportID,0) WHEN 0 THEN 0 ELSE 1 END AS Enabled ,A.ReportName ");
sql.Append(" FROM ");
sql.Append(" (SELECT * FROM dbo.tReport WHERE Enabled=1) A LEFT JOIN ");
sql.Append(" (SELECT * FROM dbo.tUserReport WHERE UserID=@userid) B ON A.ID=B.ReportID ");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@UserID", userID)
);
return(ObjectHelper.BuildObject <UserReport>(dal.DataReader));
}
}
catch
{
return(null);
}
}
/// <summary>
/// 获取报表
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Report GetReport(decimal id)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("SELECT A.ID,DbCode, DBID ,ReportName ,Enabled ,SqlCommand,A.Remark,AllSumabled ,PageSumabled ,Pagingabled ,PageSize ");
sql.Append(" FROM tReport A, tDatabase B where A.DBID=B.ID AND A.ID=@ID ");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@ID", id)
);
Report rpt;
if (dal.DataReader.Read())
{
rpt = new Report()
{
ID = Convert.ToDecimal(dal.DataReader["ID"]),
DBID = Convert.ToDecimal(dal.DataReader["DBID"]),
DBCode = Convert.ToString(dal.DataReader["DbCode"]).TrimEnd(),
ReportName = Convert.ToString(dal.DataReader["ReportName"]).TrimEnd(),
Enabled = Convert.ToBoolean(dal.DataReader["Enabled"]),
Remark = Convert.ToString(dal.DataReader["Remark"]).TrimEnd(),
SqlCommand = Convert.ToString(dal.DataReader["SqlCommand"]).TrimEnd(),
AllSumabled = Convert.ToBoolean(dal.DataReader["AllSumabled"]),
PageSumabled = Convert.ToBoolean(dal.DataReader["PageSumabled"]),
Pagingabled = Convert.ToBoolean(dal.DataReader["Pagingabled"]),
PageSize = Convert.ToInt16(dal.DataReader["PageSize"]),
};
dal.DataReader.Close();
}
else
{
throw new Exception("未找到报表");
}
//读取字段集合
sql.Clear();
sql.Append("SELECT * FROM tReportColumn WHERE ReportID=@ReportID ORDER BY OrderIndex");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@ReportID", rpt.ID)
);
rpt.Columns = ObjectHelper.BuildObject <ReportColumn>(dal.DataReader);
dal.DataReader.Close();
//读取参数集合
sql.Clear();
sql.Append("SELECT * FROM tReportParam WHERE ReportID=@ReportID");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@ReportID", rpt.ID)
);
rpt.Params = ObjectHelper.BuildObject <ReportParam>(dal.DataReader);
dal.DataReader.Close();
//读取参数列表项集合
foreach (ReportParam rp in rpt.Params)
{
if (rp.ParamInputType == 0)
{
continue;
}
sql.Clear();
sql.Append("SELECT * FROM tReportParamItem WHERE ReportID=@ReportID AND ParamCode=@ParamCode");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@ReportID", rpt.ID),
dal.CreateParameter("@ParamCode", rp.ParamCode)
);
rp.ParamItems = ObjectHelper.BuildObject <ReportParamItem>(dal.DataReader);
dal.DataReader.Close();
}
return(rpt);
}
}
catch
{
return(null);
}
}