private static void GetSqlTest()
{
int jednostkaId = 3;
int osobaId = 1;
var rpt = new ReportDocument();
rpt.Load(@"Reports\ReportWithParameter.rpt");
rpt.SetParameterValue("Jednostka", jednostkaId);
rpt.SetParameterValue("Osoba", osobaId);
var temp = "";
var groupPath = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();
var sql = rpt.ReportClientDocument.RowsetController.GetSQLStatement(groupPath, out temp);
Console.WriteLine(sql);
rpt.Close();
rpt.Dispose();
}
private string GetSQLQuery(ReportDocument report)
{
//TODO
CrystalDecisions.ReportAppServer.DataDefModel.ISCRGroupPath gp = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();
var sql = report.ReportClientDocument.RowsetController.GetSQLStatement(gp, out var reserved);
return(sql);
}
private void btn_Run_Click(object sender, EventArgs e)
{
if (txtUsername.Text.Length > 1 && txtPassword.Text.Length > 1)
{
String username = txtUsername.Text;
String password = txtPassword.Text;
rTextBoxOutput.AppendText("Starting...\n\n");
rTextBoxOutput.ScrollToCaret();
String path = this.cboDirectoryList.Text;//"C:\\SVN\\CrystalReports\\OBS";
CreateDirectoryStructure();
foreach (string file in Directory.GetFiles(path, "*.rpt"))
{
Console.WriteLine(String.Format("Processing {0}...", file));
rTextBoxOutput.AppendText(String.Format("Processing {0}...\n", file));
rTextBoxOutput.ScrollToCaret();
// Declarations
CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;
CrystalDecisions.ReportAppServer.Controllers.RowsetController boRowsetController;
CrystalDecisions.ReportAppServer.DataDefModel.ISCRGroupPath boGroupPath;
string temp = "";
try
{
// Load the report from the application directory
boReportDocument.Load(file);
// Access the ReportClientDocument in the ReportDocument (EROM bridge)
// Note this is available without a dedicated RAS with SP2 for XI R2
boReportClientDocument = boReportDocument.ReportClientDocument;
boReportDocument.DataSourceConnections[0].SetLogon(username, password);
if (boReportDocument.Subreports.Count == 0)
{
CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions parameterList = boReportDocument.DataDefinition.ParameterFields;
for (int i = 0; i < parameterList.Count; i++)
{
Console.WriteLine("i = " + i + "... " + parameterList[i].ParameterFieldName.ToString());
if (parameterList[i].ParameterFieldName.ToString() == "ReportType")
{
boReportDocument.SetParameterValue(i, "D");
}
else if (parameterList[i].ParameterFieldName.ToString() == "@ControlID")
{
boReportDocument.SetParameterValue(i, 76461450);
}
else if (parameterList[i].ParameterFieldName.ToString() == "@RevisionCode")
{
boReportDocument.SetParameterValue(i, 0);
}
}
// use the RowsetController to get the SQL query
// Note: If a report has parameters they must be supplied before getting the
// SQL query.
boRowsetController = boReportClientDocument.RowsetController;
boGroupPath = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();
String s = boRowsetController.GetSQLStatement(boGroupPath, out temp);
s = u.RemoveQuotes(s);
bool ret = false;
if (u.HasSQL(s))
{
if (u.HasProduct(s))
{
if (du.DoesDirectoryExist(path + "\\HasEmbeddedSQL\\HasProductReference"))
{
ret = du.MoveFileToDirectory(file, path + "\\HasEmbeddedSQL\\HasProductReference\\" + Path.GetFileName(file));
temp = "Processed " + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\HasEmbeddedSQL\\HasProductReference\\";
}
}
else if (u.HasView(s))
{
if (du.DoesDirectoryExist(path + "\\HasEmbeddedSQL\\HasView"))
{
ret = du.MoveFileToDirectory(file, path + "\\HasEmbeddedSQL\\HasView\\" + Path.GetFileName(file));
temp = "Processed " + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\HasEmbeddedSQL\\HasView\\";
views.Add(boReportDocument.Database.Tables[0].Name);
}
}
else
{
if (du.DoesDirectoryExist(path + "\\HasEmbeddedSQL\\ProbablyOK"))
{
ret = du.MoveFileToDirectory(file, path + "\\HasEmbeddedSQL\\ProbablyOK\\" + Path.GetFileName(file));
temp = "Processed " + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\HasEmbeddedSQL\\ProbablyOK\\";
}
}
}
else if (u.UsesExec(s))
{
if (du.DoesDirectoryExist(path + "\\NoEmbeddedSQL"))
{
ret = du.MoveFileToDirectory(file, path + "\\NoEmbeddedSQL\\" + Path.GetFileName(file));
temp = "Processed " + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\NoEmbeddedSQL\\";
}
}
else
{
if (du.DoesDirectoryExist(path + "\\ShouldReview"))
{
ret = du.MoveFileToDirectory(file, path + "\\ShouldReview\\" + Path.GetFileName(file));
temp = "Processed " + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\ShouldReview\\";
}
}
rTextBoxOutput.AppendText(temp + "\n");
rTextBoxOutput.ScrollToCaret();
}
else
{
ConnectionInfo ci = new ConnectionInfo();
ci.UserID = username;
ci.Password = password;
ApplyLogOnInfoForSubreports(boReportDocument, ci);
if (du.DoesDirectoryExist(path + "\\HasSubReports"))
{
bool ret = du.MoveFileToDirectory(file, path + "\\HasSubReports\\" + Path.GetFileName(file));
temp = "HAS SUBREPORT(S)" + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\HasSubReports\\";
}
rTextBoxOutput.AppendText(temp + "\n");
rTextBoxOutput.ScrollToCaret();
}
}
catch (System.ArgumentOutOfRangeException aor)
{
if (du.DoesDirectoryExist(path + "\\ShouldReview"))
{
bool ret = du.MoveFileToDirectory(file, path + "\\ShouldReview\\" + Path.GetFileName(file));
temp = "Should REVIEW " + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\ShouldReview\\";
}
rTextBoxOutput.AppendText(temp + "\n");
rTextBoxOutput.AppendText(aor.Message + "\n");
rTextBoxOutput.ScrollToCaret();
}
catch (System.Runtime.InteropServices.COMException comex)
{
if (du.DoesDirectoryExist(path + "\\ShouldReview"))
{
bool ret = du.MoveFileToDirectory(file, path + "\\ShouldReview\\" + Path.GetFileName(file));
temp = "Should REVIEW " + Path.GetFileName(file) + ", moved file: " + ret + ", moved to " + path + "\\ShouldReview\\";
}
rTextBoxOutput.AppendText(temp + "\n");
rTextBoxOutput.AppendText(comex.Message + "\n");
rTextBoxOutput.ScrollToCaret();
}
finally
{
// Clean up by closing and disposing of the ReportDocument object
boReportDocument.Close();
boReportDocument.Dispose();
}
}
List <String> noDupes_procs = procedures.Distinct().ToList();
List <String> noDupes_sr_views = sr_views.Distinct().ToList();
List <String> noDupes_views = views.Distinct().ToList();
u.WriteToFile(path + "\\HasSubReports", "_subreportSPs.txt", noDupes_procs);
u.WriteToFile(path + "\\HasSubReports", "_subreportViews.txt", noDupes_sr_views);
u.WriteToFile(path + "\\HasEmbeddedSQL\\HasView", "_reportViews.txt", noDupes_views);
}
else
{
MessageBox.Show("Please enter a username and password!");
}
}
