/// <summary>
/// 取中科富星自定义报表名称列表
/// </summary>
/// <param name="_rNames"></param>
/// <returns></returns>
private List <MD_ReportName> GetSinoSZDefineReportNames(string[] _rNames)
{
List <MD_ReportName> _ret = new List <MD_ReportName>();
string _fg = "";
string zjbbstr = "select BBMC,XSBBMC from tj_zdybbmcb where BBMC in (";
foreach (string _rs in _rNames)
{
zjbbstr += _fg;
zjbbstr += string.Format("'{0}'", _rs);
_fg = ",";
}
zjbbstr += ") ORDER BY BBMC";
SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.Text, zjbbstr, null);
while (dr.Read())
{
MD_ReportName _mitem = new MD_ReportName(dr.IsDBNull(0) ? "" : dr.GetString(0),
dr.IsDBNull(1) ? "" : dr.GetString(1),
SinoSZReportType.SinoSZDefineReport);
_ret.Add(_mitem);
}
dr.Close();
return(_ret);
}
private List <MD_ReportItem> GetSinoSZDefineReports(MD_ReportName _ReportName, SinoOrganize _Org)
{
List <MD_ReportName> _rps = new List <MD_ReportName>();
_rps.Add(_ReportName);
return(GetSinoSZDefineReports(DateTime.MinValue, DateTime.MaxValue, _rps, _Org));
}
public bool CreateReport(MD_ReportName ReportName, DateTime StartDate, DateTime EndDate, string ReportType)
{
OraReportFactroy _of = new OraReportFactroy();
bool result = _of.CreateReport(ReportName, StartDate, EndDate, ReportType);
return(result);
}
/// <summary>
/// 取ReportingService格式的报表名称列表
/// </summary>
/// <param name="_rNames"></param>
/// <returns></returns>
private List <MD_ReportName> GetRSReportNames(string[] _rNames)
{
string _fg = "";
string zjbbstr = "select BBMC,BBJC from BB_BBMCB where BBMC in (";
foreach (string _rs in _rNames)
{
zjbbstr += _fg;
zjbbstr += string.Format("'{0}'", _rs);
_fg = ",";
}
zjbbstr += ") ORDER BY BBMC";
List <MD_ReportName> _ret = new List <MD_ReportName>();
using (OracleConnection cn = OracleHelper.OpenConnection())
{
OracleDataReader dr = OracleHelper.ExecuteReader(cn, CommandType.Text, zjbbstr, null);
while (dr.Read())
{
MD_ReportName _mitem = new MD_ReportName(dr.IsDBNull(0) ? "" : dr.GetString(0),
dr.IsDBNull(1) ? "" : dr.GetString(1),
SinoSZReportType.ReportingService);
_ret.Add(_mitem);
}
dr.Close();
}
return(_ret);
}
/// <summary>
/// 取ReporintService格式的报表列表
/// </summary>
/// <param name="_ReportName"></param>
/// <returns></returns>
private List <MD_ReportItem> GetRSReports(MD_ReportName _ReportName)
{
string _sql = "select t.BBMC,t.BBJC,t.TJDW,t.JGMC,t.NF,t.YF, ";
_sql += " (select count(a2.bbmc) from bb_bbjlfjxxb a2 where a2.bbmc = t.bbmc AND a2.tjdw = t.TJDW ";
_sql += " and a2.nf = t.nf and a2.yf = t.yf and a2.tbrq is not null ) ysh ";
_sql += " from BB_BBJLXXST t where t.bbmc=:BBMC and T.TJDW = :TJDW";
OracleParameter[] _param = { new OracleParameter(":BBMC", OracleDbType.Varchar2, 50),
new OracleParameter(":TJDW", OracleDbType.Varchar2, 12) };
_param[0].Value = _ReportName.ReportName;
_param[1].Value = SinoUserCtx.CurUser.CurrentPost.PostDWDM;
List <MD_ReportItem> _ret = new List <MD_ReportItem>();
OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.Text, _sql, _param);
while (dr.Read())
{
MD_ReportName _mitem = new MD_ReportName(dr.IsDBNull(0) ? "" : dr.GetString(0),
dr.IsDBNull(1) ? "" : dr.GetString(1),
SinoSZReportType.ReportingService);
int _year = int.Parse(dr.IsDBNull(4) ? "2000" : dr.GetString(4));
int _month = int.Parse(dr.IsDBNull(5) ? "1" : dr.GetString(5));
DateTime _startDate = new DateTime(_year, _month, 1);
DateTime _endDate = _startDate.AddMonths(1).AddSeconds(-1);
bool _isauth = dr.IsDBNull(6) ? false : (dr.GetDecimal(6) > 0);
MD_ReportItem _ritem = new MD_ReportItem(_startDate, _endDate, _mitem, dr.IsDBNull(2) ? "" : dr.GetString(2), dr.IsDBNull(3) ? "" : dr.GetString(3), _isauth);
_ret.Add(_ritem);
}
dr.Close();
return(_ret);
}
public Dialog_CreateReport(MD_ReportName _selectReport, DateTime _sDate, DateTime _eDate, List <MD_ReportName> _reports, string _type)
{
InitializeComponent();
_reportName = _selectReport;
_startDate = _sDate;
_endDate = _eDate;
_inputType = _type;
_reportItems = _reports;
ReDrawForm();
}
public bool CreateReport(MD_ReportName _reportName, DateTime _startDate, DateTime _endDate, string _reportType)
{
switch (_reportType)
{
case "RS报表":
return(CreateRsReport(_reportName, _startDate, _endDate));
default:
return(CreateSinoSZDefineReport(_reportName, _startDate, _endDate));
}
return(false);
}
public List <MD_ReportItem> GetReports(MD_ReportName _ReportName, SinoOrganize _Org, string reportType)
{
switch (reportType)
{
case "RS报表":
return(GetRsReports(_ReportName, _Org));
default:
return(GetSinoSZDefineReports(_ReportName, _Org));
}
return(new List <MD_ReportItem>());
}
private void BuildReport()
{
Dialog_CreateReport _dialog = new Dialog_CreateReport(this.CurrentInput.ReportName, this.CurrentInput.StartDate, this.CurrentInput.EndDate, this.reportItems, this.inputType);
if (_dialog.ShowDialog() == DialogResult.OK)
{
//生成报表
this.panelWait.Visible = true;
SetInputItem(false);
_ToBeCreate_ReportName = _dialog.SelectedReport;
_ToBeCreate_StartDate = _dialog.StartDate;
_ToBeCreate_EndDate = _dialog.EndDate;
_ToBeCreate_InputType = reportType;
this.BW_Create.RunWorkerAsync();
}
}
private List <MD_ReportItem> GetRsReports(DateTime _startDate, DateTime _endDate, List <MD_ReportName> _selectReportNames, SinoOrganize _Org)
{
string _yearstr = _startDate.Year.ToString();
string _monthstr = _startDate.Month.ToString("D2");
string _sql = "select t.BBMC,t.BBJC,t.TJDW,t.JGMC,t.NF,t.YF, ";
_sql += " (select count(a2.bbmc) from bb_bbjlfjxxb a2 where a2.bbmc = t.bbmc AND a2.tjdw = t.TJDW ";
_sql += " and a2.nf = t.nf and a2.yf = t.yf and a2.tbrq is not null ) ysh ";
_sql += " from BB_BBJLXXST t where T.TJDW = :TJDW";
_sql += " and t.nf = :NF and t.yf = :YF and t.bbmc in (";
string _fg = "";
foreach (MD_ReportName _rn in _selectReportNames)
{
_sql += string.Format(" {0}'{1}'", _fg, _rn.ReportName);
_fg = ",";
}
_sql += ")";
OracleParameter[] _param = { new OracleParameter(":TJDW", OracleDbType.Varchar2, 12),
new OracleParameter(":NF", OracleDbType.Char, 4),
new OracleParameter(":YF", OracleDbType.Char, 2) };
_param[0].Value = _Org.DWDM;
_param[1].Value = _yearstr;
_param[2].Value = _monthstr;
List <MD_ReportItem> _ret = new List <MD_ReportItem>();
OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.Text, _sql, _param);
while (dr.Read())
{
MD_ReportName _mitem = new MD_ReportName(dr.IsDBNull(0) ? "" : dr.GetString(0),
dr.IsDBNull(1) ? "" : dr.GetString(1),
SinoSZReportType.ReportingService);
int _year = int.Parse(dr.IsDBNull(4) ? "2000" : dr.GetString(4));
int _month = int.Parse(dr.IsDBNull(5) ? "1" : dr.GetString(5));
DateTime _sDate = new DateTime(_year, _month, 1);
DateTime _eDate = _sDate.AddMonths(1).AddSeconds(-1);
bool _isauth = dr.IsDBNull(6) ? false : (dr.GetDecimal(6) > 0);
MD_ReportItem _ritem = new MD_ReportItem(_sDate, _eDate, _mitem, dr.IsDBNull(2) ? "" : dr.GetString(2), dr.IsDBNull(3) ? "" : dr.GetString(3), _isauth);
_ret.Add(_ritem);
}
dr.Close();
return(_ret);
}
private bool CreateRsReport(MD_ReportName _reportItem, DateTime _startDate, DateTime _endDate)
{
string _sql = "HGZHTJ.bb";
OracleParameter[] _param =
{
new OracleParameter("dtTJRQ", OracleDbType.Date),
new OracleParameter("strTJDW", OracleDbType.Varchar2),
new OracleParameter("strBBMC_JS", OracleDbType.Varchar2),
};
_param[0].Value = _startDate;
_param[1].Value = SinoUserCtx.CurUser.CurrentPost.PostDWDM;
_param[2].Value = _reportItem.ReportName;
OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringProfile, CommandType.StoredProcedure, _sql, _param);
return(true);
}
public List <MD_ReportGuideLineItem> GetReportGuideLines(MD_ReportName _reportName)
{
List <MD_ReportGuideLineItem> _ret = new List <MD_ReportGuideLineItem>();
using (OracleConnection cn = OracleHelper.OpenConnection())
{
try
{
OracleCommand _cmd = new OracleCommand(SQL_GetReportGuideLines, cn);
_cmd.Parameters.Add(":ZTMC", _reportName.ReportName);
OracleDataReader _dr = _cmd.ExecuteReader();
while (_dr.Read())
{
MD_ReportGuideLineItem _item = new MD_ReportGuideLineItem(
"1",
"",
_dr.IsDBNull(2) ? "" : _dr.GetString(2),
_dr.IsDBNull(0) ? "" : _dr.GetString(0),
Enum_ReportGuideLineItemType.Report
);
_item.Children = GetChildGuideLine(_item, cn);
_ret.Add(_item);
}
_dr.Close();
cn.Close();
}
catch (Exception ex)
{
string _err = string.Format("取报表指标项时出错，错误信息:{0} SQL语句：{1}", ex.Message, SQL_GetReportGuideLines);
OralceLogWriter.WriteSystemLog(_err, "ERROR");
throw ex;
}
}
return(_ret);
}
public List <MD_ReportGuideLineItem> GetReportGuideLines(MD_ReportName mD_ReportName)
{
throw new Exception("The method or operation is not implemented.");
}
private bool CreateSinoSZDefineReport(MD_ReportName _reportItem, DateTime _startDate, DateTime _endDate)
{
return(SinoSZExcelReport.CreateReport(_reportItem, _startDate, _endDate, SinoUserCtx.CurUser.CurrentPost.PostDWDM));
}
public List <MD_ReportItem> GetReports(MD_ReportName _ReportName, string reportType)
{
return(ReportFactroy.GetReports(_ReportName, reportType));
}
public List <MD_ReportItem> GetReports(MD_ReportName _ReportName, SinoSZJS.Base.Authorize.SinoOrganize _Org, string reportType)
{
throw new Exception("The method or operation is not implemented.");
}
public List <MD_ReportItem> GetReportsOfOrg(MD_ReportName ReportName, Base.Authorize.SinoOrganize Org, string ReportType)
{
OraReportFactroy _of = new OraReportFactroy();
return(_of.GetReports(ReportName, Org, ReportType));
}
public bool CreateReport(MD_ReportName _reportName, DateTime _startDate, DateTime _endDate, string _reportType)
{
throw new Exception("The method or operation is not implemented.");
}
public List <MD_ReportGuideLineItem> GetReportGuideLines(MD_ReportName mD_ReportName)
{
return(ReportFactroy.GetReportGuideLines(mD_ReportName));
}
public bool CreateReport(MD_ReportName _reportName, DateTime _startDate, DateTime _endDate, string _reportType)
{
return(ReportFactroy.CreateReport(_reportName, _startDate, _endDate, _reportType));
}
public List <MD_ReportItem> GetReports(MD_ReportName _ReportName, SinoOrganize _Org, string reportType)
{
return(ReportFactroy.GetReports(_ReportName, _Org, reportType));
}
/// <summary>
/// 生成报表
/// </summary>
/// <param name="_reportItem"></param>
/// <param name="_sDate"></param>
/// <param name="_eDate"></param>
/// <returns></returns>
public static bool CreateReport(MD_ReportName _reportItem, DateTime _sDate, DateTime _eDate, string _dwdm)
{
DataTable _rData = null, _mxData = new DataTable(), _cellData = null;
DataSet _hmcData;
#region 生成报表统计数据
if (!CreateReportData(_reportItem.ReportName, _sDate, _eDate, _dwdm))
{
OralceLogWriter.WriteSystemLog(string.Format("生成报表发生错误({0},{1},{2},{3})", _reportItem.ReportName, _dwdm, _sDate, _eDate), "ERROR");
return(false);
}
#endregion
#region 取报表统计结果数据
if (!GetReportData(_reportItem.ReportName, _sDate, _eDate, _dwdm, out _rData))
{
OralceLogWriter.WriteSystemLog(string.Format("取报表数据时发生错误({0},{1},{2},{3})", _reportItem.ReportName, _dwdm, _sDate, _eDate), "ERROR");
return(false);
}
#endregion
#region 取报表特殊行/列的结果数据
if (!GetReportSpecialData(_reportItem.ReportName, _sDate, _eDate, _dwdm, out _cellData))
{
OralceLogWriter.WriteSystemLog(string.Format("取报表数据特殊行列数据时发生错误({0},{1},{2},{3})", _reportItem.ReportName, _dwdm, _sDate, _eDate), "ERROR");
return(false);
}
#endregion
#region 取报表定义
if (!GetReportDefine(_reportItem.ReportName, _sDate, _eDate, _dwdm, out _hmcData))
{
OralceLogWriter.WriteSystemLog(string.Format("取报表定义数据时发生错误({0},{1},{2},{3})", _reportItem.ReportName, _dwdm, _sDate, _eDate), "ERROR");
return(false);
}
#endregion
#region 取报表明细数据
if (!GetReportMXData(_reportItem.ReportName, _sDate, _eDate, _dwdm, out _mxData))
{
OralceLogWriter.WriteSystemLog(string.Format("取报表明细数据时发生错误({0},{1},{2},{3})", _reportItem.ReportName, _dwdm, _sDate, _eDate), "ERROR");
return(false);
}
#endregion
using (OracleConnection cn = OracleHelper.OpenConnection())
{
OracleTransaction txn = cn.BeginTransaction();
try
{
//开始生成报表，并存入ORALCE CLOB字段
ProcessExcelReport oReport = new ProcessExcelReport();
oReport.ReportTemplate = Utils.ExeDir + string.Format(@"ReportTemp\{0}.xls", _reportItem.ReportName);
oReport.DataTemplate = "\\TestData.xml"; //暂时不用
oReport.ReportIndex = 1;
oReport.ExportFormat = "HTML";
oReport.OutputCache = Utils.ExeDir + @"OutputCache";
string excelReportFile, htmReportFile, sMessage;
string _bbid = GetReportID(_reportItem.ReportName, _sDate, _eDate, _dwdm);
if (!oReport.CreateReportFiles(_bbid, _rData, _cellData, _mxData, _hmcData, out excelReportFile, out htmReportFile, out sMessage))
{
//ShowError(sMessage,true);
}
else
{
//将HTML文件存入CLOB字段
//将EXCEL写入BLOG字段
StreamReader sr = new StreamReader(htmReportFile, System.Text.Encoding.Default);
string strHtml = sr.ReadToEnd();
sr.Close();
FileStream fs = new FileStream(excelReportFile, FileMode.OpenOrCreate);
byte[] blob = new byte[fs.Length];
fs.Read(blob, 0, blob.Length);
fs.Close();
OracleCommand _cmd = new OracleCommand();
_cmd.CommandText = @"update TJ_ZDYBBFJXXB set TBR = :TBR,TBRQ = sysdate,TBDW=:TBDW,BBZT='未审核',BBJG_C=:HTMFILE,BBJG_B=:EXEFILE where
BBMC = :BBMC AND TJDW=:TJDW AND KSRQ=:KSRQ AND JZRQ=:JZRQ";
_cmd.CommandType = CommandType.Text;
_cmd.Parameters.Add(":TBR", SinoUserCtx.CurUser.UserName);
_cmd.Parameters.Add(":TBDW", SinoUserCtx.CurUser.QxszDWMC);
_cmd.Parameters.Add(new OracleParameter(":HTMFILE", OracleDbType.Clob, strHtml.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, strHtml));
_cmd.Parameters.Add(new OracleParameter(":EXEFILE", OracleDbType.Blob, blob.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, blob));
_cmd.Parameters.Add(":BBMC", _reportItem.ReportName);
_cmd.Parameters.Add(":TJDW", _dwdm);
_cmd.Parameters.Add(":KSRQ", _sDate);
_cmd.Parameters.Add(":JZRQ", _eDate);
_cmd.Connection = cn;
int _updateflag = _cmd.ExecuteNonQuery();
txn.Commit();
#if !DEBUG
FileInfo oFInfo = new FileInfo(excelReportFile);
if (oFInfo.Exists)
{
File.Delete(excelReportFile);
}
oFInfo = new FileInfo(htmReportFile);
if (oFInfo.Exists)
{
File.Delete(htmReportFile);
}
#endif
return(true);
}
}
catch (Exception e)
{
txn.Rollback();
string _eStr = string.Format(" 系统生成报表数据时发生错误！\n Error Message:{0}", e.Message);
OralceLogWriter.WriteSystemLog(_eStr, "ERROR");
return(false);
}
}
return(true);
}
public List <MD_ReportItem> GetReportsByName(MD_ReportName ReportName, string ReportType)
{
OraReportFactroy _of = new OraReportFactroy();
return(_of.GetReports(ReportName, ReportType));
}
public List <MD_ReportItem> GetReports(MD_ReportName _ReportName, string reportType)
{
throw new Exception("The method or operation is not implemented.");
}
public List <MD_ReportGuideLineItem> GetReportGuideLines(MD_ReportName ReportName)
{
OraReportFactroy _of = new OraReportFactroy();
return(_of.GetReportGuideLines(ReportName));
}
List <MD_ReportGuideLineItem> IReportFactroy.GetReportGuideLines(MD_ReportName mD_ReportName)
{
throw new NotImplementedException();
}
