protected void Page_Load(object sender, EventArgs e)
{
string sql = "select h.empid, pshr.get_fullname(h.empid),pshr.get_categtxt(soccategory),p.dob,pshr.get_dojp(h.empid), pshr.get_desg(p.cdesgcode), p.cloccode, " +
"pshr.get_org(p.cloccode) as posting,h.pcloccode,pshr.get_org(h.pcloccode) as paychrg,pshr.get_desg(h.sancdesg) as sancdesg,h.sancindx, h.oonum, " +
" h.odate,cadre.get_hecode(p.cdesgcode) as hecode,p.branchcode,pshr.get_branch(p.empid) " +
"from pshr.empperso p, emphistory h where p.empid = h.empid and " +
"h.empid = 102430 and " +
"h.rowno = (select max(rowno) from pshr.emphistory where empid = p.empid and oonum is not null) order by hecode,p.empid ";
System.Data.DataSet ds = OraDBConnection.GetData(sql);
CrystalReportSource CrystalReportSource1 = new CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("rptpplist.rpt");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
string pdfPath = Server.MapPath("a.pdf");
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
System.IO.FileInfo objFi = new System.IO.FileInfo(pdfPath);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + objFi.Name);
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.AddHeader("Content-Length", objFi.Length.ToString());
HttpContext.Current.Response.ContentType = "application/pdf";
HttpContext.Current.Response.WriteFile(objFi.FullName);
HttpContext.Current.Response.End();
}
///<summary>
///功能：拉模式提取水晶报表
///个人主页：http://www.dzend.com/
///</summary>
///<param name="sender"></param>
///<param name="e"></param>
///
public int view_report(CrystalReportViewer CrystalReportViewer1, string file_name, string report_sql)
{
// CrystalReport.rpt是水晶报表文件的名称；CrystalReportSource1是从工具箱加到页面上的水晶报表数据源对像。
ExcuteDataTable(dt_report, report_sql, CommandType.Text);
try
{
CrystalReportSource cs = new CrystalReportSource();
cs.ReportDocument.Load(file_name);
cs.ReportDocument.SetDataSource(dt_report);
cs.DataBind();
for (int i = 0; i < cs.ReportDocument.ParameterFields.Count; i++)
{
string ls_part = cs.ReportDocument.ParameterFields[i].Name;
}
CrystalReportViewer1.ReportSource = cs;
CrystalReportViewer1.DataBind();
return(1);
}
catch (Exception e)
{
return(-1);
}
}
private void MakeReport(bool save = false)
{
string oonum = "-";
string oodate = "-";
string propno = this.propno.ToString();
string type;
string pdfPath;
if (save)
{
oonum = txtoonum.Text;
oodate = txtoodate.Text;
}
//string sql = "select * from cadre.ret_list where propno = " + propno + " order by empid";
string sql = "select rownum as \"#\", '" + oonum + "' as onum, '" + oodate + "' as odate, a.* from " +
"(SELECT e.empid, " +
"firstname||' '|| decode(middlename, NULL, '', middlename||' ') || lastname AS name, " +
"to_char(e.dob,'dd/MM/YYYY') AS DOB, md.desgtext as DESG, ml.locname as LOC, " +
"to_char(rl.dor,'dd/MM/YYYY') as DOR, " +
"rp.type as reptype " +
"FROM cadre.ret_list rl, pshr.mast_loc ml, pshr.mast_desg md, pshr.empperso e, cadre.ret_proposals rp " +
"WHERE rl.empid = e.empid " +
"AND e.cloccode = ml.loccode " +
"AND e.cdesgcode = md.desgcode " +
"AND rl.propno = " + propno +
" AND rp.pno = rl.propno" +
" order by md.hecode,e.empid) a";
OraDBConnection oraCn = new OraDBConnection();
System.Data.DataSet ds = OraDBConnection.GetData(sql);
//select report according to type (voluntary or supperannuation)
type = ds.Tables[0].Rows[0]["reptype"].ToString();
if (save)
{
pdfPath = Server.MapPath("office_orders\\" + oonum + "-BEG-3" + oodate + ".pdf");
}
else
{
pdfPath = Server.MapPath("office_orders\\preview_ret-" + propno + "-" + DateTime.Now.ToString("yyyyMMdd-HHmmssfff") + ".pdf");
}
CrystalReportSource CrystalReportSource1 = new CrystalReportSource();
CrystalReportSource1.Report.FileName = Server.MapPath("Reports\\rpt_ret_vol.rpt");
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables[0]);
CrystalReportSource1.DataBind();
CrystalReportSource1.ReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfPath);
CrystalReportSource1.Dispose();
Utils.DownloadFile(pdfPath);
}
public MemoryStream imprimirConstancia(int idPaciente)
{
SysPaciente p = new SysPaciente(idPaciente);
CrystalReportSource oCr = new CrystalReportSource();
string informe = "../Paciente/Reportes/certificadoSumar.rpt";
DataTable dt = SPs.PnGetCertificadoSumar(p.NumeroDocumento.ToString()).GetDataSet().Tables[0];
oCr.Report.FileName = informe;
oCr.ReportDocument.SetDataSource(dt);
oCr.DataBind();
MemoryStream oStream;
return(oStream = (MemoryStream)oCr.ReportDocument.ExportToStream(ExportFormatType.PortableDocFormat));
}
public string get_report_condition(CrystalReportViewer CrystalReportViewer1, string file_name, System.Web.UI.HtmlControls.HtmlTable lb_table)
{
// CrystalReport.rpt是水晶报表文件的名称；CrystalReportSource1是从工具箱加到页面上的水晶报表数据源对像。
string ls_conditon = "";
try
{
CrystalReportSource cs = new CrystalReportSource();
cs.ReportDocument.Load(file_name);
cs.ReportDocument.SetDataSource(dt_report);
cs.DataBind();
for (int i = 0; i < cs.ReportDocument.ParameterFields.Count; i++)
{
//string ls_part .Name;
ParameterField param = cs.ReportDocument.ParameterFields[i];
HtmlTableRow tr = new HtmlTableRow();
HtmlTableCell td1 = new HtmlTableCell();
td1.InnerHtml = param.PromptText;
tr.Cells.Add(td1);
HtmlTableCell td2 = new HtmlTableCell();
td2.InnerHtml = "<span> =</span>";
tr.Cells.Add(td2);
HtmlTableCell td3 = new HtmlTableCell();
td3.InnerHtml = "<input type=\"text\" >";
tr.Cells.Add(td3);
lb_table.Rows.Add(tr);
}
return("1");
}
catch (Exception e)
{
return("-1");
}
return("0");
}
public ReportDocument GerarRelatorioPgtoProdutores(Pagamento pgto, String strPathreport)
{
SqlConnection cn = new SqlConnection(Conexao.SQL);
DataSet ds = new DataSet();
CrystalReportSource crpt = new CrystalReportSource();
ReportDocument rpt = new ReportDocument();
SqlCommand cmd = new SqlCommand("PR_REL_PGTO_PRODUTORES", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID_REGIAO", pgto.Id_regiao);
cmd.Parameters.AddWithValue("@ID_COOPERATIVA", pgto.Id_cooperativa);
cmd.Parameters.AddWithValue("@ID_PROPRIED", pgto.Id_propriedade);
cmd.Parameters.AddWithValue("@DT_INICIO", pgto.Dt_inicio);
cmd.Parameters.AddWithValue("@DT_FIM", pgto.Dt_fim);
cmd.Parameters.AddWithValue("@TP_RELATORIO", pgto.Tp_relatorio);
try
{
cn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataTable dataTable = ds.Tables[0];
rpt.Load(strPathreport);
rpt.SetDataSource(dataTable);
}
else
{
throw new Exception("Não há coleta cadastrada para o período informado!");
}
}
catch (SqlException ex)
{
throw new Exception("Erro no servidor nro." + ex.Number);
}
finally
{
cn.Close();
}
return rpt;
}
public ReportDocument GerarRelatorioPgtoProdutores(Pagamento pgto, String strPathreport)
{
SqlConnection cn = new SqlConnection(Conexao.SQL);
DataSet ds = new DataSet();
CrystalReportSource crpt = new CrystalReportSource();
ReportDocument rpt = new ReportDocument();
SqlCommand cmd = new SqlCommand("PR_REL_PGTO_PRODUTORES", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID_REGIAO", pgto.Id_regiao);
cmd.Parameters.AddWithValue("@ID_COOPERATIVA", pgto.Id_cooperativa);
cmd.Parameters.AddWithValue("@ID_PROPRIED", pgto.Id_propriedade);
cmd.Parameters.AddWithValue("@DT_INICIO", pgto.Dt_inicio);
cmd.Parameters.AddWithValue("@DT_FIM", pgto.Dt_fim);
cmd.Parameters.AddWithValue("@TP_RELATORIO", pgto.Tp_relatorio);
try
{
cn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataTable dataTable = ds.Tables[0];
rpt.Load(strPathreport);
rpt.SetDataSource(dataTable);
}
else
{
throw new Exception("Não há coleta cadastrada para o período informado!");
}
}
catch (SqlException ex)
{
throw new Exception("Erro no servidor nro." + ex.Number);
}
finally
{
cn.Close();
}
return(rpt);
}
public static CrystalReportSource GetReportDD(ref CrystalReportSource crystalReportSource, ref WebDataSource webDataSource)
{
DataSet dsDD = new DataSet();
string strModuleName = webDataSource.RemoteName.Substring(0, webDataSource.RemoteName.IndexOf('.'));
int flag = 0;
int k, l, i, j;
if (webDataSource.InnerDataSet.Relations.Count != 0)
{
string strRelationTableName = webDataSource.InnerDataSet.Relations[0].ChildTable.TableName;
string relationTabName = CliUtils.GetTableName(strModuleName, strRelationTableName, fCurrentProject);
string strSqlRelation = "select * from COLDEF where TABLE_NAME = '" + relationTabName + "'";
dsDD = CliUtils.ExecuteSql(strModuleName, strRelationTableName, strSqlRelation, true, fCurrentProject);
k = webDataSource.InnerDataSet.Relations[0].ChildTable.Columns.Count;
j = crystalReportSource.ReportDocument.ReportDefinition.Sections["Section2"].ReportObjects.Count;
if (dsDD.Tables[0].Rows.Count != 0)
{
for (i = 0; i < j; i++)
{
for (l = 0; l < k; l++)
{
string DD;
DD = dsDD.Tables[0].Rows[l]["FIELD_NAME"].ToString().ToLower();
TextObject textObject = crystalReportSource.ReportDocument.ReportDefinition.Sections["Section2"].ReportObjects[i] as TextObject;
if (textObject != null)
{
if (DD == textObject.Text.ToString().ToLower())
{
textObject.Text = dsDD.Tables[0].Rows[l]["CAPTION"].ToString();
}
}
}
}
flag = flag + 1;
}
}
string strTableName = webDataSource.RemoteName.Substring(webDataSource.RemoteName.IndexOf('.') + 1);
string tabName = CliUtils.GetTableName(strModuleName, strTableName, fCurrentProject);
string strSql = "select * from COLDEF where TABLE_NAME = '" + tabName + "'";
dsDD = CliUtils.ExecuteSql(strModuleName, strTableName, strSql, true, fCurrentProject);
k = webDataSource.InnerDataSet.Tables[0].Columns.Count;
j = crystalReportSource.ReportDocument.ReportDefinition.Sections["Section2"].ReportObjects.Count;
if (dsDD.Tables[0].Rows.Count != 0)
{
for (i = 0; i < j; i++)
{
for (l = 0; l < k; l++)
{
string DD;
DD = dsDD.Tables[0].Rows[l]["FIELD_NAME"].ToString().ToLower();
TextObject textObject = crystalReportSource.ReportDocument.ReportDefinition.Sections["Section2"].ReportObjects[i] as TextObject;
if (textObject != null)
{
if (DD == textObject.Text.ToString().ToLower())
{
textObject.Text = dsDD.Tables[0].Rows[l]["CAPTION"].ToString();
}
}
}
}
flag = flag + 1;
}
if (flag == 0)
{
MessageBox.Show("DD has not been set up!");
}
return crystalReportSource;
}
