/// <summary>
/// 保存参数
/// </summary>
/// <param name="cfg"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int SaveCfg(Syscfg cfg, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
int i;
dal.BeginTran();
dal.Execute("UPDATE dbo.tSysCfg SET OptionValue=@OptionValue WHERE OptionName=@OptionName", out i,
dal.CreateParameter("@OptionValue", cfg.Company),
dal.CreateParameter("@OptionName", "Company"));
dal.Execute("UPDATE dbo.tSysCfg SET OptionValue=@OptionValue WHERE OptionName=@OptionName", out i,
dal.CreateParameter("@OptionValue", cfg.License),
dal.CreateParameter("@OptionName", "License"));
dal.CommitTran();
msg = "success";
return(1);
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 设置可用状态
/// </summary>
/// <param name="id"></param>
/// <param name="enabled"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int SetEnabled(decimal id, bool enabled, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(128);
sql.Append("UPDATE tReport SET Enabled=@Enabled WHERE ID=@ID");
int i;
dal.BeginTran();
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@Enabled", enabled),
dal.CreateParameter("@ID", id)
);
if (i == 1)
{
dal.CommitTran();
msg = "success";
return(1);
}
else
{
dal.RollBackTran();
msg = "error";
return(0);
}
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 读取参数
/// </summary>
/// <param name="cfg"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static Syscfg LoadCfg(out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
dal.OpenReader("Select Section,OptionName,OptionValue From tSyscfg");
Syscfg cfg = new Syscfg();
while (dal.DataReader.Read())
{
switch (Convert.ToString(dal.DataReader["OptionName"]).TrimEnd())
{
case "Company":
cfg.Company = Convert.ToString(dal.DataReader["OptionValue"]).TrimEnd();
break;
case "License":
cfg.License = Convert.ToString(dal.DataReader["OptionValue"]).TrimEnd();
break;
}
}
cfg.SerialNo = LicenseClass.GetMacByNetworkInterface();
if (string.IsNullOrEmpty(cfg.SerialNo))
{
msg = "获取序列号失败";
return(null);
}
if (cfg.License != null && cfg.License.Length == 48)
{
LicenseClass.AnalyzeLisense(cfg.License, cfg.SerialNo, out cfg.ExpDate, out cfg.ReportNumber);
if (cfg.ExpDate == "99999999")
{
cfg.ExpDate = "永久";
}
else
{
StringBuilder temp = new StringBuilder(32);
temp.AppendFormat("{0}-{1}-{2}", cfg.ExpDate.Substring(0, 4), cfg.ExpDate.Substring(4, 2), cfg.ExpDate.Substring(6, 2));
cfg.ExpDate = temp.ToString();
}
if (cfg.ReportNumber == "999")
{
cfg.ReportNumber = "无限";
}
}
else
{
cfg.ExpDate = "无效";
cfg.ReportNumber = "无效";
}
msg = "success";
return(cfg);
}
}
catch (Exception ex)
{
msg = ex.Message;
return(null);
}
}
/// <summary>
/// 删除数据库
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static int DeleteDatabase(decimal ID, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
dal.BeginTran();
int i;
dal.Execute("DELETE FROM tDatabase WHERE ID=@ID", out i,
dal.CreateParameter("@ID", ID));
if (i == 1)
{
dal.CommitTran();
msg = "success";
return(1);
}
else
{
dal.RollBackTran();
msg = "error";
return(0);
}
}
}
catch (Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 获取指定数据源明细
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static Database GetDatabase(decimal ID)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("SELECT * FROM dbo.tDatabase WHERE ID=@ID");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@ID", ID)
);
Database db = null;
if (dal.DataReader.Read())
{
db = new Database()
{
ID = Convert.ToDecimal(dal.DataReader["ID"]),
DbCode = Convert.ToString(dal.DataReader["DbCode"]).TrimEnd(),
DbType = Convert.ToInt16(dal.DataReader["DbType"]),
DataSource = Convert.ToString(dal.DataReader["DataSource"]).TrimEnd(),
DbName = Convert.ToString(dal.DataReader["DbName"]).TrimEnd(),
UserID = Convert.ToString(dal.DataReader["UserID"]).TrimEnd(),
Remark = Convert.ToString(dal.DataReader["Remark"]).TrimEnd()
};
}
return(db);
}
}
catch
{
return(null);
}
}
/// <summary>
/// 删除用户
/// </summary>
/// <param name="ID"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int DeleteUser(decimal ID, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("Delete from tUser where ID=@ID");
dal.BeginTran();
int i;
dal.Execute(sql.ToString(), out i, dal.CreateParameter("@ID", ID));
if (i == 1)
{
dal.CommitTran();
msg = "success";
return(i);
}
else
{
dal.RollBackTran();
msg = "error";
return(0);
}
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 获取系统可用报表数量
/// </summary>
/// <returns></returns>
public static int ReportCount()
{
int rptCount = -1;
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(128);
sql.Append("SELECT COUNT(1) FROM tReport WHERE Enabled=1");
dal.OpenReader(sql.ToString());
if (dal.DataReader.Read())
{
rptCount = Convert.ToInt32(dal.DataReader[0]);
}
else
{
throw new Exception("报表数获取失败");
}
return(rptCount);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="ID"></param>
/// <param name="password"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int ChangePwd(decimal ID, string oldPwd, string newPwd, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
dal.BeginTran();
StringBuilder sql = new StringBuilder(256);
sql.Append("SELECT UPassword ,UserCode FROM tUser WHERE ID=@ID ");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@ID", ID)
);
string oldDbPwd, UserCode;
if (dal.DataReader.Read())
{
oldDbPwd = Convert.ToString(dal.DataReader["UPassword"]).TrimEnd();
UserCode = Convert.ToString(dal.DataReader["UserCode"]).TrimEnd();
}
else
{
msg = "未找到该用户";
return(0);
}
dal.DataReader.Close();
if (!oldDbPwd.Equals(Des.EncryStrHex(oldPwd, UserCode)))
{
//密码验证失败
msg = "密码验证失败";
return(0);
}
sql.Clear();
sql.Append("UPDATE tUser SET UPassword=@UPassword WHERE ID=@ID");
int i;
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@UPassword", Des.EncryStrHex(newPwd, UserCode)),
dal.CreateParameter("@ID", ID)
);
if (i == 1)
{
dal.CommitTran();
msg = "success";
return(1);
}
else
{
dal.RollBackTran();
msg = "error";
return(0);
}
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 保存数据库
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public static int InsertDatabase(ref Database db, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append(" INSERT INTO tDatabase ( DBCode ,DBType ,DataSource ,DbName ,UserID ,Password ,Remark) ");
sql.Append("VALUES (");
sql.Append("@DbCode,@DbType,@DataSource,@DbName,@UserID,@Password,@Remark )");
dal.BeginTran();
int i;
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@DbCode", db.DbCode),
dal.CreateParameter("@DbType", db.DbType),
dal.CreateParameter("@DataSource", db.DataSource),
dal.CreateParameter("@DbName", db.DbName),
dal.CreateParameter("@UserID", db.UserID),
dal.CreateParameter("@Password", Des.EncryStrHex(db.Password, db.UserID)),
dal.CreateParameter("@Remark", db.Remark)
);
if (i == 1)
{
sql.Clear();
sql.Append("SELECT IDENT_CURRENT('tDatabase') ");
dal.OpenReader(sql.ToString());
if (dal.DataReader.Read())
{
db.ID = Convert.ToDecimal(dal.DataReader[0]);
dal.DataReader.Close();
}
else
{
dal.DataReader.Close();
dal.RollBackTran();
throw new Exception("获取报表ID失败");
}
dal.CommitTran();
msg = "success";
return(1);
}
else
{
dal.RollBackTran();
msg = "error";
return(0);
}
}
}
catch (Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 增加用户
/// </summary>
/// <returns></returns>
public static int AddUser(ref User user, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("insert into tUser(UserCode,UserName,UPassword,IsAdmin,Enabled) ");
sql.Append("Values (");
sql.AppendFormat("@UserCode,@UserName,@UPassword,@IsAdmin,@Enabled");
sql.Append(")");
dal.BeginTran();
int i;
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@UserCode", user.UserCode),
dal.CreateParameter("@UserName", user.UserName),
dal.CreateParameter("@UPassword", Des.EncryStrHex(user.UserCode, user.UserCode)),
dal.CreateParameter("@IsAdmin", user.IsAdmin ? 1 : 0),
dal.CreateParameter("@Enabled", user.Enabled)
);
if (i == 1)
{
sql.Clear();
sql.Append("SELECT IDENT_CURRENT('tUser') ");
dal.OpenReader(sql.ToString());
if (dal.DataReader.Read())
{
user.ID = Convert.ToInt32(dal.DataReader[0]);
dal.DataReader.Close();
}
else
{
dal.DataReader.Close();
dal.RollBackTran();
throw new Exception("获取ID失败");
}
dal.CommitTran();
msg = "success";
return(i);
}
else
{
msg = "error";
dal.RollBackTran();
return(0);
}
}
}
catch (Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <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);
}
}
/// <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;
}
}
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>
/// <param name="id"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int DeleteReport(decimal id, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
int i;
dal.BeginTran();
//清除Column
sql.Append("DELETE FROM tReportColumn WHERE ReportID=@ReportID");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id)
);
//清除ParamsItem
sql.Clear();
sql.Append("DELETE FROM tReportParamItem WHERE ReportID=@ReportID");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id)
);
//清除Params
sql.Clear();
sql.Append("DELETE FROM tReportParam WHERE ReportID=@ReportID");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id)
);
//清除Report
sql.Clear();
sql.Append("DELETE FROM tReport WHERE ID=@ID");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ID", id)
);
dal.CommitTran();
msg = "success";
return(1);
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 保存用户报表
/// </summary>
/// <param name="userID"></param>
/// <param name="userReports"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int SaveUserReport(decimal userID, UserReport[] userReports, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("DELETE FROM tUserReport WHERE UserID=@UserID");
dal.BeginTran();
int i;
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@UserID", userID)
);
sql.Clear();
sql.Append("INSERT INTO tUserReport( UserID, ReportID ) VALUES ( @UserID, @ReportID )");
foreach (UserReport ur in userReports)
{
if (ur.Enabled)
{
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@UserID", ur.UserID),
dal.CreateParameter("@ReportID", ur.ReportID)
);
if (i == 0)
{
dal.RollBackTran();
msg = "保存失败";
return(0);
}
}
}
dal.CommitTran();
msg = "success";
return(1);
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
public static int TestDatabase(Database db, out string msg)
{
string connectionString;
connectionString = db.DbType == 0 ? MSSQL_CONNECTIONSTRING : ORACLE_CONNECTIONSTRING;
connectionString = connectionString.Replace("@DataSource", db.DataSource).Replace("@DbName", db.DbName).Replace("@UserID", db.UserID).Replace("@Password", db.Password);
try
{
using (IDAL dal = DALBuilder.CreateDAL(connectionString, db.DbType))
{
msg = "连接成功";
return(1);
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="id"></param>
/// <param name="db"></param>
/// <returns></returns>
public static int UpdateDatabase(decimal ID, Database db, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append(" UPDATE tDatabase SET DbCode=@DbCode,DbType=@DbType,DataSource=@DataSource,DbName=@DbName,UserID=@UserID,Password=@Password,Remark=@Remark ");
sql.Append(" Where ID=@ID");
dal.BeginTran();
int i;
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@DbCode", db.DbCode),
dal.CreateParameter("@DbType", db.DbType),
dal.CreateParameter("@DataSource", db.DataSource),
dal.CreateParameter("@DbName", db.DbName),
dal.CreateParameter("@UserID", db.UserID),
dal.CreateParameter("@Password", Des.EncryStrHex(db.Password, db.UserID)),
dal.CreateParameter("@Remark", db.Remark),
dal.CreateParameter("@ID", db.ID)
);
if (i == 1)
{
dal.CommitTran();
msg = "success";
return(1);
}
else
{
dal.RollBackTran();
msg = "error";
return(0);
}
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <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>
/// <param name="userCode"></param>
/// <param name="uPassword"></param>
/// <param name="user"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int UserSignin(string userCode, string uPassword, out User user, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("SELECT * FROM dbo.tUser WHERE UserCode=@UserCode AND UPassword=@UPassword AND Enabled=1");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@UserCode", userCode),
dal.CreateParameter("@UPassword", Des.EncryStrHex(uPassword, userCode))
);
if (dal.DataReader.Read())
{
//登录成功
user = new User()
{
ID = Convert.ToDecimal(dal.DataReader["ID"]),
UserCode = Convert.ToString(dal.DataReader["UserCode"]).TrimEnd(),
UserName = Convert.ToString(dal.DataReader["UserName"]).TrimEnd(),
IsAdmin = Convert.ToDecimal(dal.DataReader["IsAdmin"]) == 1 ? true : false
};
msg = "登录成功";
}
else
{
msg = "用户名或密码错误";
user = null;
//登录失败
}
}
return(user == null ? 0 : 1);
}
catch (Exception ex)
{
msg = ex.Message;
user = null;
return(-1);
}
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public static int GetConnectionString(decimal ID, out string connectionString, out short dbType, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
string DataSource, DbName, UserID, Password;
dal.OpenReader("SELECT DbType, DataSource,DbName,UserID,Password FROM dbo.tDatabase WHERE ID=@ID",
dal.CreateParameter("@ID", ID));
if (dal.DataReader.Read())
{
dbType = Convert.ToInt16(dal.DataReader["DbType"]);
DataSource = Convert.ToString(dal.DataReader["DataSource"]).TrimEnd();
DbName = Convert.ToString(dal.DataReader["DbName"]).TrimEnd();
UserID = Convert.ToString(dal.DataReader["UserID"]).TrimEnd();
Password = Convert.ToString(dal.DataReader["Password"]).TrimEnd();
Password = Des.DecryStrHex(Password, UserID);
connectionString = dbType == 0 ?MSSQL_CONNECTIONSTRING :ORACLE_CONNECTIONSTRING;
connectionString = connectionString.Replace("@DataSource", DataSource).Replace("@DbName", DbName).Replace("@UserID", UserID).Replace("@Password", Password);
msg = "success";
return(1);
}
else
{
connectionString = null;
msg = "error";
dbType = -1;
return(0);
}
}
}
catch (System.Exception ex)
{
msg = ex.Message;
connectionString = null;
dbType = -1;
return(-1);
}
}
/// <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="user"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int UpdateUser(User user, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("update tUser set UserName=@UserName,IsAdmin=@IsAdmin ,Enabled=@Enabled where ID=@ID");
dal.BeginTran();
int i;
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@UserName", user.UserName),
dal.CreateParameter("@IsAdmin", user.IsAdmin?1:0),
dal.CreateParameter("@Enabled", user.Enabled),
dal.CreateParameter("@ID", user.ID)
);
if (i == 1)
{
dal.CommitTran();
msg = "success";
return(i);
}
else
{
dal.RollBackTran();
msg = "error";
return(0);
}
}
}
catch (Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 添加报表
/// </summary>
/// <param name="report"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int InsertReport(ref Report report, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
int i;
dal.BeginTran();
StringBuilder sql = new StringBuilder(256);
sql.Append(" INSERT INTO tReport( DBID ,ReportName ,Enabled ,Remark,SqlCommand,AllSumabled ,PageSumabled ,Pagingabled ,PageSize)");
sql.Append(" VALUES( ");
sql.Append(" @DBID ,@ReportName ,@Enabled ,@Remark,@SqlCommand,@AllSumabled ,@PageSumabled ,@Pagingabled ,@PageSize");
sql.Append(" ) ");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@DBID", report.DBID),
dal.CreateParameter("@ReportName", report.ReportName),
dal.CreateParameter("@Enabled", report.Enabled),
dal.CreateParameter("@Remark", report.Remark == null?"":report.Remark),
dal.CreateParameter("@SqlCommand", report.SqlCommand),
dal.CreateParameter("@AllSumabled", report.AllSumabled),
dal.CreateParameter("@PageSumabled", report.PageSumabled),
dal.CreateParameter("@Pagingabled", report.Pagingabled),
dal.CreateParameter("@PageSize", report.PageSize)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("插入报表头失败");
}
else
{
sql.Clear();
sql.Append("SELECT IDENT_CURRENT('tReport')");
dal.OpenReader(sql.ToString());
if (dal.DataReader.Read())
{
report.ID = Convert.ToDecimal(dal.DataReader[0]);//读取最新ReportID
dal.DataReader.Close();
}
else
{
dal.DataReader.Close();
dal.RollBackTran();
throw new Exception("获取报表ID失败");
}
}
//保存字段
foreach (ReportColumn c in report.Columns)
{
sql.Clear();
sql.Append("INSERT INTO tReportColumn( ReportID , ColumnCode , ColumnName ,ColumnType,Sumabled ,Sortabled,OrderIndex) ");
sql.Append("VALUES( ");
sql.Append(" @ReportID ,@ColumnCode ,@ColumnName ,@ColumnType,@Sumabled ,@Sortabled ,@OrderIndex");
sql.Append(" ) ");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", report.ID),
dal.CreateParameter("@ColumnCode", c.ColumnCode),
dal.CreateParameter("@ColumnName", c.ColumnName == null?"":c.ColumnName),
dal.CreateParameter("@ColumnType", c.ColumnType),
dal.CreateParameter("@Sumabled", c.Sumabled),
dal.CreateParameter("@Sortabled", c.Sortabled),
dal.CreateParameter("@OrderIndex", c.OrderIndex)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("字段" + c.ColumnCode + "插入失败");
}
}
//保存参数
foreach (ReportParam p in report.Params)
{
sql.Clear();
sql.Append(" INSERT INTO tReportParam( ReportID ,ParamCode ,ParamName ,ParamType ,ParamInputType) ");
sql.Append(" VAlues (");
sql.Append(" @ReportID , @ParamCode ,@ParamName ,@ParamType ,@ParamInputType ");
sql.Append(" ) ");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", report.ID),
dal.CreateParameter("@ParamCode", p.ParamCode),
dal.CreateParameter("@ParamName", p.ParamName == null?"":p.ParamName),
dal.CreateParameter("@ParamType", p.ParamType),
dal.CreateParameter("@ParamInputType", p.ParamInputType)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("参数" + p.ParamCode + "保存失败");
}
foreach (ReportParamItem op in p.ParamItems)
{
sql.Clear();
sql.Append(" INSERT INTO tReportParamItem( ReportID ,ParamCode ,OptionName , OptionValue) ");
sql.Append(" VALUES ( ");
sql.Append(" @ReportID ,@ParamCode ,@OptionName , @OptionValue");
sql.Append(" ) ");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", report.ID),
dal.CreateParameter("@ParamCode", op.ParamCode),
dal.CreateParameter("@OptionName", op.OptionName == null?"":op.OptionName),
dal.CreateParameter("@OptionValue", op.OptionValue)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("参数列表选项" + op.OptionName + "插入失败");
}
}
}
dal.CommitTran();
msg = "success";
return(1);
}
}
catch (Exception ex)
{
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 管理员登录
/// </summary>
/// <param name="userCode"></param>
/// <param name="uPassword"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int AdminSignin(string userCode, string uPassword, out User user, out string msg)
{
try
{
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
sql.Append("select count(*) userCount from tUser where IsAdmin=1 And Enabled=1");
dal.OpenReader(sql.ToString());
int i = -1;
if (dal.DataReader.Read())
{
i = Convert.ToInt32(dal.DataReader["userCount"]);
}
dal.DataReader.Close();
if (i == 0)
{
//梅伊欧用户
if (userCode == "admin" && uPassword == "753951")
{
msg = "默认用户登录成功，请尽快添加管理员账户";
//登录成功
user = new User()
{
ID = 0,
UserCode = "admin",
UserName = "******",
IsAdmin = true
};
return(1);
}
else
{
msg = "用户名或密码错误";
user = null;
return(0);
}
}
else
{
sql.Clear();
sql.Append("SELECT * FROM tUser WHERE UserCode=@UserCode AND UPassword=@UPassword AND IsAdmin='1' AND Enabled=1 ");
dal.OpenReader(sql.ToString(),
dal.CreateParameter("@UserCode", userCode),
dal.CreateParameter("@UPassword", Des.EncryStrHex(uPassword, userCode))
);
if (dal.DataReader.Read())
{
//登录成功
msg = "登录成功";
//登录成功
user = new User()
{
ID = Convert.ToDecimal(dal.DataReader["ID"]),
UserCode = Convert.ToString(dal.DataReader["UserCode"]).TrimEnd(),
UserName = Convert.ToString(dal.DataReader["UserName"]).TrimEnd(),
IsAdmin = Convert.ToDecimal(dal.DataReader["IsAdmin"]) == 1 ? true : false
};
return(1);
}
else
{
msg = "用户名或密码错误";
user = null;
return(0);
}
}
}
}
catch (Exception ex)
{
msg = ex.Message;
user = null;
return(-1);
}
}
/// <summary>
/// 更新报表
/// </summary>
/// <param name="report"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int UpdateReport(decimal id, Report report, out string msg)
{
try
{
if (report.Columns == null && report.Params == null)
{
//无字段、参数集合，确定为更改状态
return(SetEnabled(id, report.Enabled, out msg));
}
using (IDAL dal = DALBuilder.CreateDAL(ConfigurationManager.ConnectionStrings["SYSDB"].ConnectionString, 0))
{
StringBuilder sql = new StringBuilder(256);
dal.BeginTran();
sql.Append(" UPDATE tReport SET DBID=@DBID, ReportName=@ReportName, Enabled=@Enabled, Remark =@Remark,SqlCommand=@SqlCommand, AllSumabled=@AllSumabled ,PageSumabled=@PageSumabled ,Pagingabled=@Pagingabled ,PageSize=@PageSize WHERE ID=@ID");
int i;
//更新主表
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@DBID", report.DBID),
dal.CreateParameter("@ReportName", report.ReportName),
dal.CreateParameter("@Enabled", report.Enabled),
dal.CreateParameter("@Remark", report.Remark == null?"":report.Remark),
dal.CreateParameter("@ID", id),
dal.CreateParameter("@SqlCommand", report.SqlCommand == null?"":report.SqlCommand),
dal.CreateParameter("@AllSumabled", report.AllSumabled),
dal.CreateParameter("@PageSumabled", report.PageSumabled),
dal.CreateParameter("@Pagingabled", report.Pagingabled),
dal.CreateParameter("@PageSize", report.PageSize)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("更新报表头错误");
}
sql.Clear();
//清除Column
sql.Append("DELETE FROM tReportColumn WHERE ReportID=@ReportID");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id)
);
//清除ParamsItem
sql.Clear();
sql.Append("DELETE FROM tReportParamItem WHERE ReportID=@ReportID");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id)
);
//清除Params
sql.Clear();
sql.Append("DELETE FROM tReportParam WHERE ReportID=@ReportID");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id)
);
//保存字段
foreach (ReportColumn c in report.Columns)
{
sql.Clear();
sql.Append("INSERT INTO tReportColumn( ReportID , ColumnCode , ColumnName ,ColumnType,Sumabled ,Sortabled,OrderIndex) ");
sql.Append("VALUES( ");
sql.Append(" @ReportID ,@ColumnCode ,@ColumnName ,@ColumnType,@Sumabled ,@Sortabled,@OrderIndex ");
sql.Append(" ) ");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id),
dal.CreateParameter("@ColumnCode", c.ColumnCode),
dal.CreateParameter("@ColumnName", c.ColumnName == null?"":c.ColumnName),
dal.CreateParameter("@ColumnType", c.ColumnType),
dal.CreateParameter("@Sumabled", c.Sumabled),
dal.CreateParameter("@Sortabled", c.Sortabled),
dal.CreateParameter("@OrderIndex", c.OrderIndex)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("字段" + c.ColumnCode + "插入失败");
}
}
//保存参数
foreach (ReportParam p in report.Params)
{
sql.Clear();
sql.Append(" INSERT INTO tReportParam( ReportID ,ParamCode ,ParamName ,ParamType ,ParamInputType) ");
sql.Append(" VAlues (");
sql.Append(" @ReportID , @ParamCode ,@ParamName ,@ParamType ,@ParamInputType ");
sql.Append(" ) ");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id),
dal.CreateParameter("@ParamCode", p.ParamCode),
dal.CreateParameter("@ParamName", p.ParamName == null?"":p.ParamName),
dal.CreateParameter("@ParamType", p.ParamType),
dal.CreateParameter("@ParamInputType", p.ParamInputType)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("参数" + p.ParamCode + "保存失败");
}
foreach (ReportParamItem op in p.ParamItems)
{
sql.Clear();
sql.Append(" INSERT INTO tReportParamItem( ReportID ,ParamCode ,OptionName , OptionValue) ");
sql.Append(" VALUES ( ");
sql.Append(" @ReportID ,@ParamCode ,@OptionName , @OptionValue");
sql.Append(" ) ");
dal.Execute(sql.ToString(), out i,
dal.CreateParameter("@ReportID", id),
dal.CreateParameter("@ParamCode", op.ParamCode),
dal.CreateParameter("@OptionName", op.OptionName == null?"":op.OptionName),
dal.CreateParameter("@OptionValue", op.OptionValue)
);
if (i != 1)
{
dal.RollBackTran();
throw new Exception("参数列表选项" + op.OptionName + "插入失败");
}
}
}
dal.CommitTran();
msg = "success";
return(1);
}
}
catch (System.Exception ex)
{
msg = ex.Message;
return(-1);
}
}
const string PARAM_BETWEEN_BOTH_PARAM_NULL = @"\b\w+\s+between\s+null\s+and\s+null\b"; //匹配between 两侧侧参数为null
/// <summary>
/// 执行报表
/// </summary>
/// <param name="id">报表ID</param>
/// <param name="request">请求信息</param>
/// <returns></returns>
public static int QueryReport(ReportRequest request, out ReportResult result, out string msg)
{
try
{
#region 授权检测
Syscfg cfg = SyscfgBLL.LoadCfg(out msg);
if (cfg == null)
{
msg = "系统参数错误，请联系管理员";
result = null;
return(-99);
}
if (cfg.ExpDate == "无效" || (cfg.ExpDate != "永久" && DateTime.Now.CompareTo(DateTime.Parse(cfg.ExpDate)) > 0))
{
//过期
msg = "授权已过期";
result = null;
return(-99);
}
int rptCount;
if (cfg.ReportNumber == "无效" || !int.TryParse(cfg.ReportNumber, out rptCount))
{
throw new Exception("报表授权数获取失败");
}
if (rptCount < ReportCount())
{
//超过报表数
msg = "报表数已超过授权数量";
result = null;
return(-99);
}
#endregion
Report rpt = GetReport(request.ReportID);//获取报表
result = new ReportResult()
{
TotalCount = 0,
CurPage = request.Page,
PageSize = rpt.PageSize
};
string connectionString;
short dbType;
int i = -1;
i = DatabaseBLL.GetConnectionString(rpt.DBID, out connectionString, out dbType, out msg);
if (i != 1)
{
//获取数据库连接参数失败
result = null;
return(i);
}
//开始组装sql
StringBuilder sql = new StringBuilder(256);
sql.Append(rpt.SqlCommand);
var orderBy = new StringBuilder();
if (!rpt.CommandHasOrderby && !string.IsNullOrEmpty(request.SortColumn))
{
//排序请求
orderBy.AppendFormat(" Order By {0} {1}",
request.SortColumn,
request.Desc?"Desc":string.Empty
);
}
string finalSql = sql.ToString();
DataTable rstTable;
using (IDAL dal = DALBuilder.CreateDAL(connectionString, dbType))
{
//组成parameter
List <IDbDataParameter> pList = new List <IDbDataParameter>();
if (request.Params != null)
{
//替换所有的
for (int index = 0; index < request.Params.Length; index++)
{
if (request.Params[index].ParamValue == null)
{
//将所有值为null的参数 替换为null
finalSql = Regex.Replace(finalSql, PARAM_REGEX.Replace("参数名", request.Params[index].ParamCode), " null ");
}
}
finalSql = Regex.Replace(finalSql, PARAM_SIG_OPERATOR, " 1=1 "); //将所有 参数【单操作符】null 的项 替换为1=1
finalSql = Regex.Replace(finalSql, PARAM_MUL_OPERATOR, " 1=1 "); //将所有参数【多操作符】null 的项替换为1=1
finalSql = Regex.Replace(finalSql, PARAM_LIKE, " 1=1 "); //将所有like null的项替换为1=1
finalSql = Regex.Replace(finalSql, PARAM_BETWEEN_BOTH_PARAM_NULL, "1=1"); //将所有between null and null 的项替换为1=1
MatchCollection mc = Regex.Matches(finalSql, PARAM_BETWEEN_LEFT_PARAM_NULL); //匹配between null and 参数 的项
if (mc.Count > 0)
{
foreach (Match m in mc)
{
string tmp = " <= " + (dbType == 0 ? "@" : ":") + Regex.Match(m.Value, @"(?<=@)\w+\b").Value + " ";
finalSql = finalSql.Replace(m.Value, tmp);
}
}
mc = Regex.Matches(finalSql, PARAM_BETWEEN_RIGHT_PARAM_NULL);//匹配between 参数 and null
if (mc.Count > 0)
{
foreach (Match m in mc)
{
string tmp = " >=" + (dbType == 0 ? "@" : ":") + Regex.Match(m.Value, @"(?<=@)\w+\b").Value + " ";
finalSql = finalSql.Replace(m.Value, tmp);
}
}
foreach (ReportParam p in request.Params)
{
IDbDataParameter dbp = null;
if (p.ParamValue == null)
{
continue;//值为null的参数不加入参数列表
}
switch (p.ParamType)
{
case 0:
dbp = dal.CreateParameter(p.ParamCode, DbType.String);
dbp.Value = string.IsNullOrEmpty(p.ParamValue) ? string.Empty : p.ParamValue;
break;
case 1:
decimal v;
dbp = dal.CreateParameter(p.ParamCode, DbType.Decimal);
if (decimal.TryParse(p.ParamValue, out v))
{
dbp.Value = v;
}
else
{
dbp.Value = null;
}
break;
case 2:
DateTime d;
dbp = dal.CreateParameter(p.ParamCode, DbType.DateTime);
if (DateTime.TryParse(p.ParamValue, out d))
{
dbp.Value = d;
}
else
{
dbp.Value = null;
}
break;
}
if (dbp == null)
{
throw new Exception("参数错误");
}
pList.Add(dbp);
}
}
if (rpt.Pagingabled)
{
//分页请求
rstTable = dal.Select(finalSql.Replace('\r', ' ').Replace('\n', ' ') + orderBy.ToString(), rpt.PageSize * (request.Page - 1), rpt.PageSize, out i, pList.ToArray());
}
else
{
rstTable = dal.Select(finalSql.Replace('\r', ' ').Replace('\n', ' ') + orderBy.ToString(), out i, pList.ToArray());
}
if (rpt.PageSumabled)
{
//页合计请求
DataRow row = rstTable.NewRow();
foreach (ReportColumn c in rpt.Columns)
{
if (!c.Sumabled)
{
continue;
}
row[c.ColumnCode] = rstTable.Compute("sum(" + c.ColumnCode + ")", "");
}
rstTable.Rows.InsertAt(row, rstTable.Rows.Count);
}
sql.Clear();
sql.Append(" Select ");
if (rpt.AllSumabled)
{
//总合计请求
foreach (ReportColumn c in rpt.Columns)
{
if (c.Sumabled)
{
sql.AppendFormat(" Sum({0}) AS {0}, ",
c.ColumnCode
);
}
else
{
sql.AppendFormat(" null AS {0}, ",
c.ColumnCode
);
}
}
}
sql.Append(" Count(*) AS TotalCount ");
sql.AppendFormat(" From {0} ", Regex.Match(finalSql.Replace('\r', ' ').Replace('\n', ' '), ALLSUM_FROM_REGEX, RegexOptions.IgnoreCase).Value);
IDbDataParameter[] pList2 = new IDbDataParameter[pList.Count];
for (int j = 0; j < pList.Count; j++)
{
pList2[j] = dal.CloneParameter(pList[j]);
}
dal.OpenReader(sql.ToString(), pList2);
if (dal.DataReader.Read() && rpt.AllSumabled)
{
DataRow newrow = rstTable.NewRow();
foreach (ReportColumn c in rpt.Columns)
{
if (!c.Sumabled)
{
continue;
}
newrow[c.ColumnCode] = dal.DataReader[c.ColumnCode];
}
rstTable.Rows.Add(newrow);
}
result.TotalCount = Convert.ToInt32(dal.DataReader["TotalCount"]);
dal.DataReader.Close();
}
result.ReportData = JsonHelper.DatatableToJson(rstTable);
return(1);
}
catch (Exception ex)
{
result = null;
msg = ex.Message;
return(-1);
}
}
/// <summary>
/// 重建报表结构
/// </summary>
/// <param name="id"></param>
/// <param name="SQL"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static Report RebuildReport(decimal id, decimal dbID, string SQL, out string msg)
{
Report report, reportdb;
report = new Report();
report.Columns = new List <ReportColumn>();
report.Params = new List <ReportParam>();
report.SqlCommand = SQL;
string tmpSql = Regex.Replace(SQL, REGEX_PARAMS_2, "NULL");//将所有参数设置为null，获取表结构
try
{
string tarConn;
short dbType;
if (DatabaseBLL.GetConnectionString(dbID, out tarConn, out dbType, out msg) != 1)
{
throw new Exception(msg);
}
using (IDAL dal = DALBuilder.CreateDAL(tarConn, dbType))
{
//获取SQL语句中的Column
DataSet ds = dal.Select(tmpSql.Replace('\r', ' ').Replace('\n', ' '));//替换掉回车、换行符
if (ds.Tables.Count != 1)
{
throw new Exception("错误：查询结果必须只有一个结果表");
}
else
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
ReportColumn column = new ReportColumn()
{
ReportID = id,
ColumnCode = ds.Tables[0].Columns[i].ColumnName,
OrderIndex = i + 1
};
switch (ds.Tables[0].Columns[i].DataType.Name)
{
case "Int16":
case "Int32":
case "Int64":
case "Decimal":
case "Float":
column.ColumnType = 1;
break;
case "String":
column.ColumnType = 0;
break;
case "Datetime":
column.ColumnType = 2;
break;
default:
column.ColumnType = 0;
break;
}
report.Columns.Add(column);
}
}
}
//获取SQL语句中的Param
MatchCollection pms = Regex.Matches(SQL, REGEX_PARAMS);
foreach (Match m in pms)
{
//获取SQL语句中的参数
report.Params.Add(new ReportParam()
{
ReportID = id,
ParamCode = m.Value
});
}
if (id != 0)
{
//获取到数据库report
report.ID = id;
reportdb = GetReport(id);
report.Enabled = reportdb.Enabled;
//两个report进行比较
//比较params
foreach (ReportParam rp in report.Params)
{
IEnumerable <ReportParam> dbrps = reportdb.Params.Where(p => p.ParamCode == rp.ParamCode);
if (dbrps.Count() == 0)
{
continue;
}
ReportParam dbrp = dbrps.First();
rp.ParamInputType = dbrp.ParamInputType;
rp.ParamItems = dbrp.ParamItems;
rp.ParamName = dbrp.ParamName;
rp.ParamType = dbrp.ParamType;
}
//比较columns
foreach (ReportColumn rc in report.Columns)
{
IEnumerable <ReportColumn> dbclms = reportdb.Columns.Where(c => c.ColumnCode == rc.ColumnCode);
if (dbclms.Count() == 0)
{
//数据库中原本不存在
continue;
}
ReportColumn dbclm = dbclms.First();
rc.ColumnName = dbclm.ColumnName;
rc.Sortabled = report.CommandHasOrderby?false:dbclm.Sortabled;//如果含有OrderBy 则不允许排序
rc.Sumabled = dbclm.Sumabled;
}
}
msg = "success";
return(report);
}
catch (Exception ex)
{
msg = ex.Message;
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);
}
}