public void Can_Generate_Json_Table()
{
var config = JsonConfigReader<SqlReportConfig>.GetConfig(@"C:\Projects\amoon\SiteAdmin\OneTechnologies.SiteAdministration\OneTechnologies.SiteAdmin.Portal\json\sqlReportConfig.json");
SqlReport report = new SqlReport(config);
var jsonReport = report.GetJsonTable();
Assert.IsTrue(jsonReport.Length>0);
}
public void CompareReports_ForDuplicatedSp_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2,
sp3
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\|sp defined 2 times"));
}
private void btnProcess_ServerClick(object sender, EventArgs e)
{
Response.Clear();
Response.ContentType = "text/html";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("content-disposition", String.Format("attachment; filename=\"CustomSqlReport_{0}.html\"", DateTime.Now.ToString("yyyy-MM-dd")));
Response.Write(SqlReport.ExecuteScript(txtSQLRequest.Text));
Response.End();
}
public void CompareReports_ForNewNonDefaultInNewSp_DoesNotReturnError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2,
sp3
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
public void CompareReports_ForMultipleSelectsWithNewValuesAtEnd_ReturnsNoErrors()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.SomeAmountExt", 2, false, ""),
new ReturnSqlReportEntry("C.NameExt", 2, false, ""),
new ReturnSqlReportEntry("C.IdExt", 2, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.SomethingElse", 1, false, ""),
new ReturnSqlReportEntry("C.SomeAmountExt", 2, false, ""),
new ReturnSqlReportEntry("C.NameExt", 2, false, ""),
new ReturnSqlReportEntry("C.IdExt", 2, false, ""),
new ReturnSqlReportEntry("C.SomethingElseExt", 2, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
/// <summary>
/// PreCreate
/// </summary>
/// <param name="context">The context.</param>
protected override void PreCreate(BusinessContext context)
{
ReportEntity report = context.Request.Target as ReportEntity;
if (report != null && !string.IsNullOrEmpty(report.RdlText))
{
SqlReport.DemandCustomSqlReportAccess();
}
base.PreCreate(context);
}
private async Task <IActionResult> RenderReport(UserInfo userInfo, string entityName, int id, string reportName, ExportType exportType, ReportGenerator gen)
{
byte[] repData = await this.RenderReportData(userInfo, entityName, id, reportName, exportType, gen);
if (repData != null && repData.Length > 0)
{
SqlReport sqlReport = new SqlReport(userInfo, this.auditService);
string reportNameReal = sqlReport.GetReportName(entityName, id);
return(File(repData, ReportHelper.GetMimeType(exportType), reportNameReal + "." + ReportHelper.GetExtension(exportType)));
}
return(NotFound());
}
private void CheckForDuplicate(SqlReport newReport, List <string> errors)
{
var duplicates =
newReport.StoredProcedures.GroupBy(sp => sp.SpUniqueName)
.Where(spGroup => spGroup.Count() > 1)
.Select(dupeSps => new { UniqueName = dupeSps.Key, Count = dupeSps.Count() })
.ToList();
foreach (var duplicate in duplicates)
{
errors.Add($"{duplicate.UniqueName}\\|sp defined {duplicate.Count} times");
}
}
private void CheckForRename(SqlReport masterReport,
SqlReport newReport, List <string> errors)
{
var spsNotInMaster =
newReport.StoredProcedures.Where(
x => masterReport.StoredProcedures.All(y => x.SpUniqueName != y.SpUniqueName));
errors.AddRange(from sp in spsNotInMaster
let differentCaseSp = masterReport.StoredProcedures.Where(y => sp.SpUniqueName.Equals(y.SpUniqueName, StringComparison.InvariantCultureIgnoreCase))
.ToList()
where differentCaseSp.Any()
select $"{differentCaseSp[0].SpUniqueName}:{sp.SpName}\\|sp was renamed with different case");
}
public void CompareReports_ForChangeFieldCase_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.Phone", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.PhonE", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\StoredProcedure1\\C.Phone|existing return value is missing from new code"));
}
public void CompareReports_ForRenameStringLiteral_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello' AS TheName", 1, true, "TheName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello' AS DifferentName", 1, true, "DifferentName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\StoredProcedure1\\'Hello' AS TheName|existing return value is missing from new code"));
}
public void CompareReports_ForNoLongerDefaultedParam_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", false)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@data|parameter is defaulted in master but not in new code"));
}
public void CompareReports_ForBigIntToTinyInt_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "bigint", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "TINYINT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@id|existing BIGINT parameter was changed to TINYINT"));
}
public void CompareReports_ForChangeStringLiteralWithName_ReturnsNoError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello' AS TheName", 1, true, "TheName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello 123' AS TheName", 1, true, "TheName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
public void WriteMasterReport(string dataFilePath, SqlReport masterReport)
{
using (var writer = _xmlWriterWrapperFactory.CreateXmlWriter(dataFilePath))
{
writer.WriteStartElement("SqlReport");
writer.WriteElementString("TimeStamp", $"{DateTime.Now}");
foreach (var sp in masterReport.StoredProcedures)
{
writer.SerializeSqlReportElement(sp);
}
writer.WriteEndElement();
writer.Flush();
}
}
public void WriteMasterReport_ForValidReport_WritesDataToFile()
{
// Setup
var xmlFactory = Substitute.For <IXmlStreamWrapperFactory>();
var fileWrapper = Substitute.For <IFileWrapper>();
var streamWriter = Substitute.For <IXmlStreamWriterWrapper>();
xmlFactory.CreateXmlWriter("c:\\output\\fakeReport1.xml")
.Returns(streamWriter);
var storedProcedureReport = new StoredProcedureReport()
{
Db = "MyDB",
Schema = "Schema1",
SpName = "SomeSp1",
Parameters = new List <ParamSqlReportEntry>()
{
new ParamSqlReportEntry("@Id", "INT", "N/A", false)
},
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("P.Name", 1, false, ""),
}
};
var sqlReport = new SqlReport
{
TimeStamp = "6/21/2019 6:45:00 PM",
StoredProcedures = new List <StoredProcedureReport>()
{
storedProcedureReport
}
};
var writer = new SqlReportWriter(xmlFactory, fileWrapper);
// Act
writer.WriteMasterReport("c:\\output\\fakeReport1.xml",
sqlReport);
// Assert
streamWriter.Received(1).WriteStartElement("SqlReport");
streamWriter.Received(1).WriteElementString("TimeStamp", Arg.Any <string>());
streamWriter.Received(1).SerializeSqlReportElement(storedProcedureReport);
streamWriter.Received(1).WriteEndElement();
streamWriter.Received(1).Flush();
}
public void CompareReports_StarReplacesWithParamNames_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("*", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("Param1", 1, false, ""),
new ReturnSqlReportEntry("Param2", 1, false, ""),
new ReturnSqlReportEntry("Param3", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("Database1\\dbo\\StoredProcedure1\\*|existing return value is missing from new code"));
}
public void CompareReports_ForBigIntRemoved_ReturnsCorrectError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "BIGINT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@id|existing parameter is missing from new code"));
}
public void CompareReports(SqlReport masterReport, SqlReport newReport, List <string> errors)
{
Parallel.ForEach(masterReport.StoredProcedures, (masterSp) =>
{
var newSp =
newReport.StoredProcedures.FirstOrDefault(
x => x.SpUniqueName == masterSp.SpUniqueName);
if (newSp == null)
{
return;
}
CheckForMissingReturnValues(masterSp, newSp, errors);
CheckReturnValueOrder(masterSp, newSp, errors);
});
}
public void CompareReports_SelectStatementRemoved_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("A.Name1", 1, false, "Name1"),
new ReturnSqlReportEntry("A.Name2", 1, false, "Name2")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(2));
Assert.That(errors[0], Is.EqualTo("Database1\\dbo\\StoredProcedure1\\A.Name1|existing return value is missing from new code"));
Assert.That(errors[1], Is.EqualTo("Database1\\dbo\\StoredProcedure1\\A.Name2|existing return value is missing from new code"));
}
internal SqlFileScanner(IFileWrapper fileWrapper,
IXmlStreamWrapperFactory xmlWriterWrapperFactory,
IDirectoryWrapperFactory directoryWrapperFactory,
IHtmlReportGenerator htmlReportGenerator,
IParamReportComparer paramReportComparer,
IReturnReportComparer returnReportComparer)
{
_fileWrapper = fileWrapper;
_sqlParameterReportWriter =
new SqlReportWriter(xmlWriterWrapperFactory, _fileWrapper);
_directoryFactory = directoryWrapperFactory;
_htmlReportGenerator = htmlReportGenerator;
_paramReportComparer = paramReportComparer;
_returnReportComparer = returnReportComparer;
_sqlReport = new SqlReport();
}
public void CompareReports(SqlReport masterReport, SqlReport newReport, List <string> errors)
{
Parallel.ForEach(masterReport.StoredProcedures, (masterSp) =>
{
var newSp =
newReport.StoredProcedures.FirstOrDefault(
x => x.SpUniqueName == masterSp.SpUniqueName);
if (newSp == null)
{
return;
}
CheckForRemovedParameters(masterSp, newSp, errors);
CheckForNewNonDefaultedParameters(masterSp, newSp, errors);
CheckParameterOrder(masterSp, newSp, errors);
CheckParameterType(masterSp, newSp, errors);
});
CheckForRename(masterReport, newReport, errors);
CheckForDuplicate(newReport, errors);
}
public void CompareReports_ForSpCaseChanged_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@info", "VARCHAR", "200", false)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2,
sp3
}
};
var sp4 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp5 = new StoredProcedureReport("DB1", "dbo", "loadsomethinG2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false)
}
};
var sp6 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@info", "VARCHAR", "200", false)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp4,
sp5,
sp6
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0],
Is.EqualTo("DB1\\dbo\\LoadSomething2:loadsomethinG2\\|sp was renamed with different case"));
}
public void CompareReports_ForArityChange_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false),
new ParamSqlReportEntry("@address", "VARCHAR", "200", true),
new ParamSqlReportEntry("@phone", "VARCHAR", "200", false)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", true),
new ParamSqlReportEntry("@style", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2,
sp3
}
};
var sp4 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp5 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false),
new ParamSqlReportEntry("@phone", "VARCHAR", "200", false),
new ParamSqlReportEntry("@address", "VARCHAR", "200", true)
}
};
var sp6 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", true),
new ParamSqlReportEntry("@style", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp4,
sp5,
sp6
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(2));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething2\\@address|existing parameter is out of order"));
Assert.That(errors[1], Is.EqualTo("DB1\\dbo\\LoadSomething2\\@phone|existing parameter is out of order"));
}
public void CompareReports_ForSameSpNameInDifferentDb_ReturnsNoErrors()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp2 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var sp3 = new StoredProcedureReport("Database3", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2,
sp3
}
};
var sp4 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp5 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var sp6 = new StoredProcedureReport("Database3", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp4,
sp5,
sp6
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
public bool IsEnable(object Sender, object Element)
{
return(SqlReport.AskCustomSqlReportAccess());
}
public void CompareReports_ForNewValueInMiddle_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp2 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2
}
};
var sp3 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp4 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.NewValue", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.Phone", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp3,
sp4
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("Database2\\schema1\\StoredProcedure2\\C.Id|existing return value is out of order"));
}
public void CompareReports_ForDuplicatedSpWithDifferentValues_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp4 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp3,
sp4
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(3));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@id|new parameter has no default"));
Assert.That(errors[1], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@data|existing parameter is out of order"));
Assert.That(errors[2], Is.EqualTo("DB1\\dbo\\LoadSomething1\\|sp defined 2 times"));
}
public async Task <IActionResult> GetReport(
int id,
ReportMode reportMode,
ExportType exportType,
string rdlReportName,
string entityName,
string ids,
string reportJsonData,
string userToken)
{
if (string.IsNullOrEmpty(userToken))
{
return(null);
}
UserInfo userInfo = GetUserInfoFromUserToken(userToken);
string reportJsonData1 = string.Empty;
if (!string.IsNullOrEmpty(reportJsonData))
{
byte[] encodedDataAsBytes = System.Convert.FromBase64String(reportJsonData);
string dataEncoded = System.Text.ASCIIEncoding.ASCII.GetString(encodedDataAsBytes);
reportJsonData1 = HttpUtility.UrlDecode(dataEncoded);
}
try
{
if (userInfo != null)
{
ReportGenerator gen = new ReportGenerator(
Settings.GetInstance.SystemSettings.ReportServerUrl + "/ReportExecution2005.asmx",
Settings.GetInstance.SystemSettings.ReportServerUserName,
Settings.GetInstance.SystemSettings.ReportServerPassword
);
string reportPath = Settings.GetInstance.SystemSettings.ReportServerFormsPath;
if (!reportPath.EndsWith("/"))
{
reportPath += "/";
}
if (reportMode == ReportMode.MultipleByMultipleIds)
{
byte[] encodedDataAsBytes = System.Convert.FromBase64String(ids);
string idsEncoded = System.Text.ASCIIEncoding.ASCII.GetString(encodedDataAsBytes);
string idsUrlEncoded = HttpUtility.UrlDecode(idsEncoded).Replace("[", "").Replace("]", "");
string[] idsUrlEncodedSplited = idsUrlEncoded.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
if (idsUrlEncodedSplited != null && idsUrlEncodedSplited.Length == 1)
{
int recordId = int.Parse(idsUrlEncodedSplited[0]);
return(await this.RenderReport(userInfo, entityName, recordId, reportPath + rdlReportName, exportType, gen));;
}
else
{
using (MemoryStream compressedFileStream = new MemoryStream())
{
using (ZipArchive zipArchive = new ZipArchive(compressedFileStream, ZipArchiveMode.Update, false))
{
foreach (string recordIdStr in idsUrlEncodedSplited)
{
int recordId = int.Parse(recordIdStr);
byte[] repData = await this.RenderReportData(userInfo, entityName, recordId, reportPath + rdlReportName, exportType, gen);
SqlReport sqlReport = new SqlReport(userInfo, this.auditService);
string reportNameReal = sqlReport.GetReportName(entityName, id);
ZipArchiveEntry zipEntry = zipArchive.CreateEntry(reportNameReal);
using (MemoryStream originalFileStream = new MemoryStream(repData))
{
using (Stream zipEntryStream = zipEntry.Open())
{
originalFileStream.CopyTo(zipEntryStream);
}
}
}
}
return(new FileContentResult(compressedFileStream.ToArray(), "application/zip")
{
FileDownloadName = "Reports.zip"
});
}
}
}
//**else if (reportMode == ReportMode.OneByMultipleIds)
//**{
//** byte[] encodedDataAsBytes = System.Convert.FromBase64String(ids);
//** string idsEncoded = System.Text.ASCIIEncoding.ASCII.GetString(encodedDataAsBytes);
//** string idsUrlEncoded = HttpUtility.UrlDecode(idsEncoded);
//**
//** Vistos3Api.Code.Reports.ReportSettings reportSettings = new Vistos3Api.Code.Reports.ReportSettings();
//** reportSettings.ReportPath = Settings.GetInstance.SystemSettings.ReportServerFormsPath;
//** reportSettings.ReportServerUserName = Settings.GetInstance.SystemSettings.ReportServerUserName;
//** reportSettings.ReportServerPassword = Settings.GetInstance.SystemSettings.ReportServerPassword;
//** reportSettings.ReportServerUrl = Settings.GetInstance.SystemSettings.ReportServerUrl;
//** if (!reportSettings.ReportPath.EndsWith("/")) reportSettings.ReportPath += "/";
//** reportSettings.ReportPath += rdlReportName;
//** reportSettings.ReportParameters.Add(new Microsoft.Reporting.WebForms.ReportParameter("ids", idsUrlEncoded));
//** if (!string.IsNullOrEmpty(reportJsonData1))
//** {
//** reportSettings.ReportParameters.Add(new Microsoft.Reporting.WebForms.ReportParameter("jsonData", reportJsonData1));
//** }
//**
//** string reportNameReal = reportSettings.GetReportName(entityName, id, userInfo);
//** ReportResult rr = new ReportResult(reportNameReal,
//** exportType,
//** reportSettings,
//** entityName,
//** userInfo,
//** entityName,
//** id,
//** false);
//**
//** return rr;
//**}
//**else
else if (reportMode == ReportMode.OneByOneId)
{
return(await this.RenderReport(userInfo, entityName, id, reportPath + rdlReportName, exportType, gen));
}
}
}
catch (Exception ex)
{
Logger.SaveLogError(LogLevel.Error, "Execute", ex, null, userInfo);
}
return(NotFound());
}