public void setLogonInfo()
{
try
{
TableLogOnInfo logOnInfo = new TableLogOnInfo();
TableLogOnInfos infos = new TableLogOnInfos();
ConnectionInfo connectionInfo = new ConnectionInfo();
ReportDocument reportDocument = new ReportDocument();
logOnInfo.ConnectionInfo.ServerName = new Connection().ServerName;
logOnInfo.ConnectionInfo.DatabaseName = new Connection().DatabaseName;
logOnInfo.ConnectionInfo.UserID = new Connection().UserId;
logOnInfo.ConnectionInfo.Password = new Connection().Password;
logOnInfo.TableName = "LogonInfoTable";
infos.Add(logOnInfo);
reportDocument = (ReportDocument)Session[SessionKeys.s_reportDocument];
reportDocument.Database.Tables[0].ApplyLogOnInfo(logOnInfo);
CrystalReportViewer1.LogOnInfo = infos;
}
catch (Exception exception)
{
lbl_message.Text = exception.Message;
}
}
public Report(ReportDocument rep)
{
InitializeComponent();
TextReader textReader = new StreamReader("c:\\Program files\\SM\\dblogon.txt");
string uid = textReader.ReadLine();
string pwd = textReader.ReadLine();
string server = textReader.ReadLine();
string db = textReader.ReadLine();
textReader.Close();
ConnectionInfo crConnectionInfo = new ConnectionInfo();
crConnectionInfo.ServerName = server;
crConnectionInfo.DatabaseName = db;
crConnectionInfo.UserID = uid;
crConnectionInfo.Password = pwd;
TableLogOnInfos crTableLogonInfos = new TableLogOnInfos();
foreach (CrystalDecisions.CrystalReports.Engine.Table table in rep.Database.Tables)
{
TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
crTableLogonInfo.TableName = table.Name;
crTableLogonInfo.ConnectionInfo = crConnectionInfo;
crTableLogonInfos.Add(crTableLogonInfo);
table.ApplyLogOnInfo(crTableLogonInfo);
}
crystalReportViewer1.LogOnInfo = crTableLogonInfos;
crystalReportViewer1.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None;
crystalReportViewer1.ReportSource = rep;
}
private void Retive_Rep_loan_collection()
{
try
{
string strPath = "~/reports/" + ViewState["report_code"].ToString() + ".rpt";
oRpt.Load(Server.MapPath(strPath));
TableLogOnInfo logOnInfo = new TableLogOnInfo();
TableLogOnInfos tableLogOnInfos = new TableLogOnInfos();
string strUsername = Session["username"].ToString();
string strCompanyname = ((DataSet)Application["xmlconfig"]).Tables["default"].Rows[0]["companyname"].ToString();
string strServername = System.Configuration.ConfigurationSettings.AppSettings["servername"];
string strDbname = System.Configuration.ConfigurationSettings.AppSettings["dbname"];
string strDbuser = System.Configuration.ConfigurationSettings.AppSettings["dbuser"];
string strDbpassword = System.Configuration.ConfigurationSettings.AppSettings["dbpassword"];
logOnInfo.ConnectionInfo.ServerName = strServername;
logOnInfo.ConnectionInfo.DatabaseName = strDbname;
logOnInfo.ConnectionInfo.UserID = strDbuser;
logOnInfo.ConnectionInfo.Password = strDbpassword;
tableLogOnInfos.Add(logOnInfo);
oRpt.SetParameterValue("@vc_criteria", Session["criteria"].ToString());
oRpt.SetParameterValue("@vc_criteria2", "");
oRpt.SetParameterValue("date_print", Session["date_print"].ToString());
//oRpt.SetParameterValue("UserName", strUsername);
//oRpt.SetParameterValue("CompanyName", strCompanyname);
//oRpt.SetParameterValue("Condition", Session["Condition"].ToString());
CrystalReportViewer1.LogOnInfo = tableLogOnInfos;
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
private void Retive_Rep_loan01()
{
try
{
string strPath = "~/reports/" + ViewState["report_code"] + ".rpt";
oRpt.Load(Server.MapPath(strPath));
TableLogOnInfo logOnInfo = new TableLogOnInfo();
TableLogOnInfos tableLogOnInfos = new TableLogOnInfos();
string strUsername = Session["username"].ToString();
string strCompanyname = ViewState["companyname"].ToString();
string strServername = System.Configuration.ConfigurationSettings.AppSettings["servername"];
string strDbname = System.Configuration.ConfigurationSettings.AppSettings["dbname"];
string strDbuser = System.Configuration.ConfigurationSettings.AppSettings["dbuser"];
string strDbpassword = System.Configuration.ConfigurationSettings.AppSettings["dbpassword"];
this.Title = ViewState["report_title"].ToString();
logOnInfo.ConnectionInfo.ServerName = strServername;
logOnInfo.ConnectionInfo.DatabaseName = strDbname;
logOnInfo.ConnectionInfo.UserID = strDbuser;
logOnInfo.ConnectionInfo.Password = strDbpassword;
tableLogOnInfos.Add(logOnInfo);
oRpt.SetParameterValue("@vc_criteria", ViewState["criteria"].ToString());
oRpt.SetParameterValue("@vc_criteria2", ViewState["criteria2"].ToString());
CrystalReportViewer1.LogOnInfo = tableLogOnInfos;
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
public static TableLogOnInfos ConectaCrystal()
{
TableLogOnInfos Infos = new TableLogOnInfos();
TableLogOnInfo Info = new TableLogOnInfo();
ConnectionInfo cn = new ConnectionInfo();
cn.ServerName = "CCLSQL";
cn.DatabaseName = "materia_prima";
cn.UserID = "matprima";
cn.Password = "******";
cn.Type = ConnectionInfoType.SQL;
cn.IntegratedSecurity = false;
Info.ConnectionInfo = cn;
Infos.Add(Info);
return Infos;
}
public static TableLogOnInfos ConectaCrystal()
{
TableLogOnInfos Infos = new TableLogOnInfos();
TableLogOnInfo Info = new TableLogOnInfo();
ConnectionInfo cn = new ConnectionInfo();
cn.ServerName = "CCLSQL";
cn.DatabaseName = "materia_prima";
cn.UserID = "matprima";
cn.Password = "******";
cn.Type = ConnectionInfoType.SQL;
cn.IntegratedSecurity = false;
Info.ConnectionInfo = cn;
Infos.Add(Info);
return(Infos);
}
/// <summary>
/// Actualiza la conexión a la base de datos
/// </summary>
/// <param name="rpt">
/// Objeto del reporte
/// </param>
/// <param name="loginfo">
/// Datos de conexión
/// </param>
private void Actualiza(ref ReportDocument rpt, ConnectionInfo loginfo)
{
try
{
CrystalDecisions.Shared.TableLogOnInfo TableLogOnInfo = new TableLogOnInfo();
CrystalDecisions.Shared.TableLogOnInfos tableLogOnInfos = new TableLogOnInfos();
TableLogOnInfo.ConnectionInfo = loginfo;
tableLogOnInfos.Add(TableLogOnInfo);
ActualizarConexionReporte(rpt, TableLogOnInfo);
}
catch (Exception ex)
{
throw new Exception("Error al actualizar reporte: " + ex.Message);
}
}
private void btnLoc_Click(object sender, EventArgs e)
{
SqlConnection con = dal.GetConnect();
con.Open();
string sql = "select *from SinhVien where MaLop=@MaLop";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.SelectCommand.Parameters.AddWithValue("@MaLop", cmbMaLop.SelectedValue.ToString());
DataTable dt = new DataTable();
da.Fill(dt);
crBaoCao cr = new crBaoCao();
ConnectionInfo crConnectionInfo = new ConnectionInfo();
crConnectionInfo.ServerName = "Ty-PC";
crConnectionInfo.DatabaseName = "CNPM_1";
crConnectionInfo.UserID = "sa";
crConnectionInfo.Password = "******";
TableLogOnInfos crTableLogonInfos = new TableLogOnInfos();
foreach (CrystalDecisions.CrystalReports.Engine.Table table in cr.Database.Tables)
{
TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
crTableLogonInfo.TableName = table.Name;
crTableLogonInfo.ConnectionInfo = crConnectionInfo;
crTableLogonInfos.Add(crTableLogonInfo);
table.ApplyLogOnInfo(crTableLogonInfo);
}
crvHocKyHocPhan.LogOnInfo = crTableLogonInfos;
cr.SetDataSource(dt);
ParameterValues a = new ParameterValues();
ParameterDiscreteValue b = new ParameterDiscreteValue();
b.Value = cmbMaLop.SelectedValue.ToString();
a.Add(b);
cr.DataDefinition.ParameterFields["txtMaLop"].ApplyCurrentValues(a);
crvHocKyHocPhan.ReportSource = cr;
crvHocKyHocPhan.Show();
}
private void GetReport(string pos, string cpoint)
{
//resultListEmp.RefreshReport();
//string sql = "SELECT p.profix_name as profix, ep.emp_name as name, ep.emp_lname as lname, IF( cp.cpoint_id LIKE '60%', 'ฝ่ายฯ ', cp.cpoint_name ) AS cpoint_name, pos.pos_name, ep.emp_id, '' AS note, NOW() AS dateNote, CASE WHEN pos.pos_id = '1' THEN CONCAT( SUBSTRING_INDEX(pos.pos_name, ' ', 1), ' (รองฯ)' ) WHEN pos.pos_id = '2' THEN CONCAT( SUBSTRING_INDEX(pos.pos_name, ' ', 1), ' (คุมระบบฯ)' ) ELSE pos.pos_name END AS pos_row, REPLACE( REPLACE( REPLACE( REPLACE( cp.cpoint_name, 'ฝ่ายฯ ', '' ), '(', '' ), ')', '' ), 'ด่านฯ ', '' ) AS cpoint_row FROM tbl_emp_profile ep JOIN tbl_profix p ON p.profix_id = ep.emp_profix_id JOIN tbl_cpoint cp ON ep.emp_cpoint_id = cp.cpoint_id JOIN tbl_pos pos ON pos.pos_id = ep.emp_pos_id WHERE ep.emp_staus_working = '1' AND pos.pos_id = '" + pos + "' AND cp.cpoint_id LIKE '" + cpoint + "%' ORDER BY name,lname";
ReportProfile reportProfile = new ReportProfile();
//CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
//rpt.Load(Server.MapPath("FinalReport.rpt"));
ConnectionInfo crConnectionInfo = new ConnectionInfo();
crConnectionInfo.ServerName = "MySql DSN HR"; //Database server or ODBC
crConnectionInfo.DatabaseName = "hrsystem"; // Database name
crConnectionInfo.UserID = "adminhrs"; // username
crConnectionInfo.Password = "******"; // password
TableLogOnInfos crTableLogonInfos = new TableLogOnInfos();
TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
foreach (CrystalDecisions.CrystalReports.Engine.Table table in reportProfile.Database.Tables)
{
crTableLogonInfo.TableName = table.Name;
crTableLogonInfo.ConnectionInfo = crConnectionInfo;
crTableLogonInfos.Add(crTableLogonInfo);
table.ApplyLogOnInfo(crTableLogonInfo);
}
resultListEmp.LogOnInfo = crTableLogonInfos;
//reportProfile.SetParameterValue("cpoint",cpoint_id.SelectedValue);
//reportProfile.SetParameterValue("pos",pos_id.SelectedValue);
//resultListEmp.ReportSource = reportProfile;
//rpt.SetParameterValue("LAB_ID_SQL", 7);
resultListEmp.ReportSource = reportProfile;
}
private void SetConnection(ReportDocument rpt)
{
try
{
ReportObjects crReportObjects;
SubreportObject crSubreportObject;
ReportDocument crSubreportDocument;
Database crDatabase;
Tables crTables;
TableLogOnInfo crTableLogOnInfo;
ConnectionInfo crConnectionInfo = new ConnectionInfo();
//pass the necessary parameters to the connectionInfo object
crConnectionInfo.ServerName = OISDataInfo.DatabaseInfomation.DATASOURCE;
crConnectionInfo.UserID = OISDataInfo.DatabaseInfomation.USERNAME;
crConnectionInfo.Password = OISDataInfo.DatabaseInfomation.PASSWORD;
crConnectionInfo.DatabaseName = OISDataInfo.DatabaseInfomation.DATABASENAME;
//set up the database and tables objects to refer to the current report
crDatabase = rpt.Database;
crTables = crDatabase.Tables;
//loop through all the tables and pass in the connection info
foreach (Table crTable in crTables)
{
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);
}
//set the crSections object to the current report's sections
Sections crSections = rpt.ReportDefinition.Sections;
//loop through all the sections to find all the report objects
foreach (Section crSection in crSections)
{
crReportObjects = crSection.ReportObjects;
//loop through all the report objects to find all the subreports
foreach (ReportObject crReportObject in crReportObjects)
{
if (crReportObject.Kind == ReportObjectKind.SubreportObject)
{
//you will need to typecast the reportobject to a subreport
//object once you find it
crSubreportObject = (SubreportObject)crReportObject;
//open the subreport object
crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);
//set the database and tables objects to work with the subreport
crDatabase = crSubreportDocument.Database;
crTables = crDatabase.Tables;
//loop through all the tables in the subreport and
//set up the connection info and apply it to the tables
foreach (Table crTable in crTables)
{
crConnectionInfo.ServerName = OISDataInfo.DatabaseInfomation.DATASOURCE;
crConnectionInfo.UserID = OISDataInfo.DatabaseInfomation.USERNAME;
crConnectionInfo.Password = OISDataInfo.DatabaseInfomation.PASSWORD;
crConnectionInfo.DatabaseName = OISDataInfo.DatabaseInfomation.DATABASENAME;
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);
}
}
}
}
TableLogOnInfos crTableLogonInfos = new TableLogOnInfos();
TableLogOnInfo crTableLogonInfo = new TableLogOnInfo();
foreach (Table table in rpt.Database.Tables)
{
crTableLogonInfo.TableName = table.Name;
crTableLogonInfo.ConnectionInfo = crConnectionInfo;
crTableLogonInfos.Add(crTableLogonInfo);
table.ApplyLogOnInfo(crTableLogonInfo);
}
crystalReportViewer1.LogOnInfo = crTableLogonInfos;
}
catch (Exception ex)
{
rMessageBox.ShowException(null, ex);
}
}
private void btnExecute_Click(object sender, EventArgs e)
{
ReportDocument report = new ReportDocument();
report.Load(this.filePath);
report.SetDatabaseLogon(userName, password, server, db);
ParameterFieldDefinitions crParameterFieldDefinitions;
ParameterFieldDefinition crParameterFieldDefinition;
ParameterValues crParameterValues = new ParameterValues();
ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();
//order no
crParameterDiscreteValue.Value = txtFilter.Text;
crParameterFieldDefinitions = report.DataDefinition.ParameterFields;
crParameterFieldDefinition = crParameterFieldDefinitions["orderNo"];
crParameterValues = crParameterFieldDefinition.CurrentValues;
crParameterValues.Clear();
crParameterValues.Add(crParameterDiscreteValue);
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);
//start date
crParameterDiscreteValue.Value = dtpStartDate.Value;
crParameterFieldDefinitions = report.DataDefinition.ParameterFields;
crParameterFieldDefinition = crParameterFieldDefinitions["startDate"];
crParameterValues = crParameterFieldDefinition.CurrentValues;
crParameterValues.Clear();
crParameterValues.Add(crParameterDiscreteValue);
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);
//end date
crParameterDiscreteValue.Value = dtpEndDate.Value;
crParameterFieldDefinitions = report.DataDefinition.ParameterFields;
crParameterFieldDefinition = crParameterFieldDefinitions["endDate"];
crParameterValues = crParameterFieldDefinition.CurrentValues;
crParameterValues.Clear();
crParameterValues.Add(crParameterDiscreteValue);
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);
ConnectionInfo crConnectionInfo = new ConnectionInfo();
crConnectionInfo.ServerName = server;
crConnectionInfo.DatabaseName = db;
crConnectionInfo.UserID = userName;
crConnectionInfo.Password = password;
TableLogOnInfo logOnInfo = new TableLogOnInfo();
logOnInfo.ConnectionInfo = crConnectionInfo;
TableLogOnInfos logOnInfos = new TableLogOnInfos();
logOnInfos.Add(logOnInfo);
crystalReportViewer.LogOnInfo = logOnInfos;
crystalReportViewer.ReportSource = report;
crystalReportViewer.Refresh();
}
