private void VerifyReport()
{
int _selectReportIndex = this.gridView1.FocusedRowHandle;
if (_selectReportIndex >= 0)
{
MD_ReportItem _ritem = this.gridView1.GetRow(_selectReportIndex) as MD_ReportItem;
Dialog_VerifyReport _dialog = new Dialog_VerifyReport(_ritem, this.reportType);
if (_dialog.ShowDialog() == DialogResult.OK)
{
using (SinoSZClientBase.ReportService.ReportServiceClient _rsc = new SinoSZClientBase.ReportService.ReportServiceClient())
{
bool _createResult = _rsc.VerifyReport(_ritem, _dialog.VerifyDate, this.reportType);
if (_createResult)
{
XtraMessageBox.Show("审核报表成功!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
QueryReport();
}
else
{
XtraMessageBox.Show("审核报表失败!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
else
{
XtraMessageBox.Show("请选择要审核的报表", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void BrowseReport()
{
int _selectReportIndex = this.gridView1.FocusedRowHandle;
if (_selectReportIndex >= 0)
{
MD_ReportItem _ritem = this.gridView1.GetRow(_selectReportIndex) as MD_ReportItem;
switch (this.reportType)
{
case "RS报表":
frmShowHTMLReport _htmlForm = new frmShowHTMLReport(_ritem, reportType);
_application.AddForm(Guid.NewGuid().ToString(), _htmlForm);
break;
case "自定义报表":
default:
ShowExcelReport excelReport = new ShowExcelReport(_ritem, reportType);
_application.AddForm(Guid.NewGuid().ToString(), excelReport);
break;
}
}
else
{
XtraMessageBox.Show("请选择要浏览的报表", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private bool VerifyRsReport(MD_ReportItem _reportItem, DateTime _verifyDate)
{
string _CommandStr = "update BB_BBJLFJXXB SET TBR =:TBR,TBRQ =:TBRQ,TBDW =:TBDW where TJDW =:TJDW AND NF=:NF AND YF=:YF AND BBMC=:BBMC ";
OracleParameter[] _param =
{
new OracleParameter(":TBR", OracleDbType.Varchar2),
new OracleParameter(":TBRQ", OracleDbType.Date),
new OracleParameter(":TBDW", OracleDbType.Varchar2),
new OracleParameter(":TJDW", OracleDbType.Varchar2),
new OracleParameter(":NF", OracleDbType.Varchar2),
new OracleParameter(":YF", OracleDbType.Varchar2),
new OracleParameter(":BBMC", OracleDbType.Varchar2)
};
_param[0].Value = SinoUserCtx.CurUser.UserName;
_param[1].Value = _verifyDate;
_param[2].Value = SinoUserCtx.CurUser.CurrentPost.PostDWMC;
_param[3].Value = _reportItem.ReportDWID;
_param[4].Value = _reportItem.StartDate.Year.ToString();
_param[5].Value = _reportItem.StartDate.Month.ToString("D2");
_param[6].Value = _reportItem.ReportName.ReportName;
OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringProfile, CommandType.Text, _CommandStr, _param);
return(true);
}
public frmShowHTMLReport(MD_ReportItem _report, string _type)
{
InitializeComponent();
this.SelectedReport = _report;
this.reportType = _type;
this.Text = string.Format("浏览报表[{0}]", _report.ReportTitle);
}
/// <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);
}
private MD_ReportVerifyInfo GetRsReportVerifyInof(MD_ReportItem _ritem)
{
MD_ReportVerifyInfo _ret = null;
string _sql = "select TBR,TBRQ,TBDW from BB_BBJLFJXXB where ";
_sql += " TJDW =:TJDW AND NF=:NF AND YF=:YF AND BBMC=:BBMC ";
OracleParameter[] _param = { new OracleParameter(":TJDW", OracleDbType.Varchar2),
new OracleParameter(":NF", OracleDbType.Varchar2),
new OracleParameter(":YF", OracleDbType.Varchar2),
new OracleParameter(":BBMC", OracleDbType.Varchar2) };
_param[0].Value = _ritem.ReportDWID;
_param[1].Value = _ritem.StartDate.Year.ToString();
_param[2].Value = _ritem.StartDate.Month.ToString("D2");
_param[3].Value = _ritem.ReportName.ReportName;
OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.Text,
_sql, _param);
while (dr.Read())
{
_ret = new MD_ReportVerifyInfo(dr.IsDBNull(2) ? "" : dr.GetString(2),
dr.IsDBNull(0) ? "" : dr.GetString(0),
dr.IsDBNull(1) ? DateTime.MinValue : dr.GetDateTime(1));
}
dr.Close();
return(_ret);
}
private byte[] GetSinoSZDefineReport_Excel(MD_ReportItem _reportItem)
{
byte[] cbuffer = null;
using (OracleConnection cn = OracleHelper.OpenConnection())
{
long actual = 0;
OracleCommand _cmd = new OracleCommand();
_cmd.CommandText = SQL_GetSinoSZDefineReport_Excel;
_cmd.CommandType = CommandType.Text;
_cmd.Connection = cn;
_cmd.Parameters.Add(":BBMC", _reportItem.ReportName.ReportName);
_cmd.Parameters.Add(":TJDW", _reportItem.ReportDWID);
_cmd.Parameters.Add(":KSRQ", _reportItem.StartDate);
_cmd.Parameters.Add(":JZRQ", _reportItem.EndDate);
OracleDataReader myOracleDataReader = _cmd.ExecuteReader();
bool _readflag = myOracleDataReader.Read();
OracleBlob myOracleClob = myOracleDataReader.GetOracleBlob(0);
long lobLength = myOracleClob.Length;
cbuffer = new byte[lobLength];
actual = myOracleClob.Read(cbuffer, 0, cbuffer.Length);
myOracleDataReader.Close();
return(cbuffer);
}
}
/// <summary>
/// 获取报表数据
/// </summary>
/// <param name="selectedReport">查询报表所需信息</param>
/// <param name="reportType">报表类型</param>
/// <returns></returns>
private byte[] GetReportData(MD_ReportItem selectedReport, string reportType)
{
using (SinoSZClientBase.ReportService.ReportServiceClient rsc = new SinoSZClientBase.ReportService.ReportServiceClient())
{
byte[] reportBytes = rsc.GetReport(selectedReport, "EXCEL", reportType);
return(reportBytes);
}
}
private void ShowVerifyData(MD_ReportItem _ritem)
{
using (SinoSZClientBase.ReportService.ReportServiceClient _rsc = new SinoSZClientBase.ReportService.ReportServiceClient())
{
MD_ReportVerifyInfo _info = _rsc.GetReportVerifyInfo(_ritem, reportType);
Dialog_VerifyReport _dialog = new Dialog_VerifyReport(_info);
_dialog.ShowDialog();
}
}
private byte[] GetRsReport_OLD(MD_ReportItem _reportItem, string Format)
{
ReportingService rs = new ReportingService();
rs.Credentials = new System.Net.NetworkCredential(RsConfig.Name, RsConfig.Pass);
//rs.Credentials = new System.Net.NetworkCredential(Config.Rs_UserName, Config.Rs_UserPass);
// Assign the URL of the Web service
rs.Url = RsConfig.RsURL;
string ReportPath = "/海关总署缉私局/" + _reportItem.ReportName.ReportName;
// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
ParameterValue[] param = new ParameterValue[3];
param[0] = new ParameterValue();
param[0].Name = "Report_Parameter_Year";
param[0].Value = _reportItem.StartDate.Year.ToString();
param[1] = new ParameterValue();
param[1].Name = "Report_Parameter_month";
param[1].Value = _reportItem.StartDate.Month.ToString("D2");
param[2] = new ParameterValue();
param[2].Name = "Report_Parameter_Dw";
param[2].Value = _reportItem.ReportDWID;
string showHide = null;
DataSourceCredentials[] credentials = null;
Byte[] results;
string encoding;
string mimeType;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
//Exectute the report and save it into a file.
try
{
results = rs.Render(ReportPath, Format, historyID, deviceInfo, param,
credentials, showHide, out encoding, out mimeType,
out reportHistoryParameters, out warnings, out streamIDs);
return(results);
}
catch (Exception e)
{
Exception e1 = e;
string _eStr = string.Format(" 系统在生成报表时发出错误！\n Report Name:{0} \n Report Date:{1} \n Report Dw:{2} \n Report Error:{3}",
_reportItem.ReportName, _reportItem.StartDate.Year.ToString() + _reportItem.StartDate.Month.ToString("D2"),
string.Format("{0}[{1}]", _reportItem.ReportDWID, _reportItem.ReportDWName), e.Message);
// Write an informational entry to the event log.
OralceLogWriter.WriteSystemLog(_eStr, "ERROR");
return(null);
}
}
private byte[] GetSinoSZDefineReport(MD_ReportItem _reportItem, string Format)
{
switch (Format)
{
case "MHTML":
return(GetSinoSZDefineReport_HTML(_reportItem));
case "EXCEL":
return(GetSinoSZDefineReport_Excel(_reportItem));
}
return(null);
}
public byte[] GetReport(MD_ReportItem _reportItem, string Format, string reportType)
{
switch (reportType)
{
case "RS报表":
return(GetRsReport(_reportItem, Format));
default:
return(GetSinoSZDefineReport(_reportItem, Format));
}
return(null);
}
public bool ReBuildReport(MD_ReportItem _ritem, string _reportType)
{
switch (_reportType)
{
case "RS报表":
return(ReBuildRsReport(_ritem));
default:
return(ReBuildSinoSZDefineReport(_ritem));
}
return(false);
}
public MD_ReportVerifyInfo GetReportVerifyInof(MD_ReportItem _ritem, string reportType)
{
switch (reportType)
{
case "RS报表":
return(GetRsReportVerifyInof(_ritem));
default:
return(GetSinoSZDefineReportVerifyInfo(_ritem));
}
return(null);
}
public bool VerifyReport(MD_ReportItem _reportItem, DateTime _verifyDate, string _reportType)
{
switch (_reportType)
{
case "RS报表":
return(VerifyRsReport(_reportItem, _verifyDate));
default:
return(VerifySinoSZDefineReport(_reportItem, _verifyDate));
}
return(false);
}
private List <MD_ReportItem> GetSinoSZDefineReports(DateTime _startDate, DateTime _endDate, List <MD_ReportName> _selectReportNames, SinoOrganize _Org)
{
List <MD_ReportItem> _ret = new List <MD_ReportItem>();
string _rnString = "";
string _fg = "";
foreach (MD_ReportName _rn in _selectReportNames)
{
_rnString += string.Format("{0}{1}", _fg, _rn.ReportName);
_fg = ",";
}
using (OracleConnection cn = OracleHelper.OpenConnection())
{
OracleCommand _cmd = new OracleCommand();
_cmd.CommandText = "zhtj_zdybb.getbbmc";
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.Connection = cn;
OracleParameter _p1 = _cmd.Parameters.Add("strtjdw", OracleDbType.Varchar2);
_p1.Value = _Org.DWDM;
OracleParameter _p2 = _cmd.Parameters.Add("dtbegin_p", OracleDbType.Varchar2);
_p2.Value = (_startDate == DateTime.MinValue) ? "" : _startDate.ToString("yyyyMMdd");
OracleParameter _p3 = _cmd.Parameters.Add("dtend_p", OracleDbType.Varchar2);
_p3.Value = (_endDate == DateTime.MaxValue) ? "" : _endDate.ToString("yyyyMMdd");
OracleParameter _p4 = _cmd.Parameters.Add("strbbmc", OracleDbType.Varchar2);
_p4.Value = _rnString;
_cmd.Parameters.Add("recret", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
OracleDataReader _dr = _cmd.ExecuteReader();
while (_dr.Read())
{
bool _isauth = _dr.IsDBNull(6) ? false : (_dr.GetString(6) == "已审核");
MD_ReportItem _item = new MD_ReportItem(
_dr.IsDBNull(3) ? DateTime.MinValue : _dr.GetDateTime(3),
_dr.IsDBNull(4) ? DateTime.MaxValue : _dr.GetDateTime(4),
new MD_ReportName(_dr.IsDBNull(0) ? "" : _dr.GetString(0), _dr.IsDBNull(5) ? "" : _dr.GetString(5), SinoSZReportType.SinoSZDefineReport),
_dr.IsDBNull(1) ? "" : _dr.GetString(1),
_dr.IsDBNull(2) ? "" : _dr.GetString(2),
_isauth);
_ret.Add(_item);
}
_dr.Close();
cn.Close();
}
return(_ret);
}
/// <summary>
/// 通过ReportingService取报表内容
/// </summary>
/// <param name="_reportItem"></param>
/// <param name="Format"></param>
/// <returns></returns>
private byte[] GetRsReport(MD_ReportItem _reportItem, string Format)
{
switch (RsConfig.InterfaceType)
{
case "NEW":
return(GetRsReport_New(_reportItem, Format));
case "OLD":
default:
return(GetRsReport_OLD(_reportItem, Format));
}
}
/// <summary>
/// 主程序
/// </summary>
/// <param name="report">查询报表所需信息</param>
/// <param name="type">报表类型</param>
public ShowExcelReport(MD_ReportItem report, string type)
{
InitializeComponent();
_selectedReport = report;
_reportType = type;
Text = string.Format("浏览报表[{0}]", report.ReportTitle);
IWorkbook workbook = spreadsheetControl1.Document;
byte[] reportBytes = GetReportData(_selectedReport, _reportType);
workbook.LoadDocument(reportBytes, DocumentFormat.Xlsx);
SetStyle(workbook);
}
private void ReBuildReport()
{
int _selectReportIndex = this.gridView1.FocusedRowHandle;
if (_selectReportIndex >= 0)
{
_ToBeRebuild_Report = this.gridView1.GetRow(_selectReportIndex) as MD_ReportItem;
this.panelWait.Visible = true;
SetInputItem(false);
this.BW_Rebuild.RunWorkerAsync();
}
else
{
XtraMessageBox.Show("请选择要重新计算的报表", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private bool ReBuildRsReport(MD_ReportItem _ritem)
{
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 = _ritem.StartDate;
_param[1].Value = _ritem.ReportDWID;
_param[2].Value = _ritem.ReportName.ReportName;
OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringProfile, CommandType.StoredProcedure, _sql, _param);
return(true);
}
private bool VerifySinoSZDefineReport(MD_ReportItem _reportItem, DateTime _verifyDate)
{
using (OracleConnection cn = OracleHelper.OpenConnection())
{
OracleCommand _cmd = new OracleCommand();
_cmd.CommandText = SQL_VerifySinoSZDefineReport;
_cmd.Parameters.Add(":BBMC", _reportItem.ReportName.ReportName);
_cmd.Parameters.Add(":TJDW", _reportItem.ReportDWID);
_cmd.Parameters.Add(":KSRQ", _reportItem.StartDate);
_cmd.Parameters.Add(":JZRQ", _reportItem.EndDate);
_cmd.CommandType = CommandType.Text;
_cmd.Connection = cn;
_cmd.ExecuteNonQuery();
}
return(true);
}
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);
}
/// <summary>
/// 取HTML格式的报表
/// </summary>
/// <param name="_reportItem"></param>
/// <returns></returns>
private byte[] GetSinoSZDefineReport_HTML(MD_ReportItem _reportItem)
{
int actual = 0;
string _resStr = "";
using (OracleConnection cn = OracleHelper.OpenConnection())
{
OracleCommand _cmd = new OracleCommand();
_cmd.CommandText = "select BBJG_C from TJ_ZDYBBFJXXB where BBMC = :BBMC AND TJDW=:TJDW AND KSRQ=:KSRQ AND JZRQ=:JZRQ ";
_cmd.CommandType = CommandType.Text;
_cmd.Connection = cn;
_cmd.Parameters.Add(":BBMC", _reportItem.ReportName.ReportName);
_cmd.Parameters.Add(":TJDW", _reportItem.ReportDWID);
_cmd.Parameters.Add(":KSRQ", _reportItem.StartDate);
_cmd.Parameters.Add(":JZRQ", _reportItem.EndDate);
using (OracleDataReader myOracleDataReader = _cmd.ExecuteReader())
{
myOracleDataReader.Read();
OracleClob myOracleClob = myOracleDataReader.IsDBNull(0) ? null : myOracleDataReader.GetOracleClob(0);
if (myOracleClob == null)
{
return(null);
}
StreamReader streamreader = new StreamReader(myOracleClob, System.Text.Encoding.Unicode);
// step 3: get the CLOB data using the Read() method
char[] cbuffer = new char[100];
while ((actual = streamreader.Read(cbuffer, 0 /*buffer offset*/, cbuffer.Length /*count*/)) > 0)
{
_resStr += new string(cbuffer, 0, actual);
}
myOracleDataReader.Close();
}
cn.Close();
System.Text.UnicodeEncoding converter = new System.Text.UnicodeEncoding();
byte[] returnBytes = converter.GetBytes(_resStr);
return(returnBytes);
}
}
private void gridView1_DoubleClick(object sender, EventArgs e)
{
int _selectReportIndex = this.gridView1.FocusedRowHandle;
if (_selectReportIndex >= 0)
{
GridColumn _gc = this.gridView1.FocusedColumn;
MD_ReportItem _ritem = this.gridView1.GetRow(_selectReportIndex) as MD_ReportItem;
if (_gc.FieldName == "Audited")
{
if (_ritem.Audited)
{
ShowVerifyData(_ritem);
}
}
else
{
BrowseReport();
}
}
}
public Dialog_VerifyReport(MD_ReportItem _ritem, string _reportType)
{
InitializeComponent();
InitForm();
}
public bool VerifyReport(MD_ReportItem ReportItem, DateTime VerifyDate, string ReportType)
{
OraReportFactroy _of = new OraReportFactroy();
return(_of.VerifyReport(ReportItem, VerifyDate, ReportType));
}
public bool ReBuildReport(MD_ReportItem ReportItem, string ReportType)
{
OraReportFactroy _of = new OraReportFactroy();
return(_of.ReBuildReport(ReportItem, ReportType));
}
public MD_ReportVerifyInfo GetReportVerifyInfo(MD_ReportItem ReportItem, string ReportType)
{
OraReportFactroy _of = new OraReportFactroy();
return(_of.GetReportVerifyInof(ReportItem, ReportType));
}
public byte[] GetReport(MD_ReportItem ReportItem, string Format, string ReportType)
{
OraReportFactroy _of = new OraReportFactroy();
return(_of.GetReport(ReportItem, Format, ReportType));
}
private bool ReBuildSinoSZDefineReport(MD_ReportItem _ritem)
{
return(CreateSinoSZDefineReport(_ritem.ReportName, _ritem.StartDate, _ritem.EndDate));
}
