private void SetReport <T>(IList <T> aLista, string NomeFonte)
{
Report.SetValue("Empresa", "LCorp S.A.");
Report.SetValue("PaginaXdeY", string.Empty);
Report.SetValue("DataSistema", DateTime.Now.ToShortDateString());
Report.AddTable(NomeFonte, aLista);
//Adiciona a função de BuscarContexto, para que possam ser traduzido os termos
Report.SetUserFunction("BuscarContexto", new BuscarContexto());
}
/// <summary>
/// This is the method that will be called by the ASP.NET front end. It returns an array of bytes
/// with the report data, so the ASP.NET application can stream it to the client.
/// </summary>
/// <returns>The generated file as a byte array.</returns>
public byte[] WebRun(Stream DataStream, string FileName)
{
XlsReport.SetValue("Date", DateTime.Now);
XlsReport.SetValue("FileName", FileName);
DataSet Data = new DataSet();
DataTable ValidateResult = Data.Tables.Add("ValidateResult");
ValidateResult.Columns.Add("FileName", typeof(string));
ValidateResult.Columns.Add("CellRef", typeof(string));
ValidateResult.Columns.Add("ErrorType", typeof(string));
ValidateResult.Columns.Add("FunctionName", typeof(string));
DataTable CompareResult = Data.Tables.Add("CompareResult");
CompareResult.Columns.Add("SheetName", typeof(string));
CompareResult.Columns.Add("CellRef", typeof(string));
CompareResult.Columns.Add("CalcResult", typeof(string));
CompareResult.Columns.Add("XlsResult", typeof(string));
CompareResult.Columns.Add("Diff", typeof(string));
CompareResult.Columns.Add("FormulaText", typeof(string));
XlsReport.AddTable(Data);
XlsFile xls1 = new XlsFile();
XlsFile xls2 = new XlsFile();
xls1.Open(DataStream);
DataStream.Position = 0;
xls2.Open(DataStream);
CompareXls(xls1, xls2, CompareResult);
ValidateXls(xls1, ValidateResult);
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
using (MemoryStream OutStream = new MemoryStream())
{
using (FileStream InStream = new FileStream(DataPath + "ValidateReport.xls", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XlsReport.Run(InStream, OutStream);
return(OutStream.ToArray());
}
}
}
public static void SetValueExportByString(ref FlexCel.Report.FlexCelReport flcReport, string _ParamName, object _value)
{
try
{
flcReport.SetValue(_ParamName, _value);
}
catch (Exception ex)
{
}
}
public static void SetValueExportByString(ref FlexCel.Report.FlexCelReport flcReport, string _ParamName, object _value)
{
try
{
flcReport.SetValue(_ParamName, _value);
}
catch (Exception ex)
{
CommonData.log.Error(ex.ToString());
}
}
public FileContentResult RacunReportCollectionAsList(string jqGridFilters)
{
DataAccessAdapterBase adapter = Helpers.Helper.GetDataAccessAdapter();
long firmaId = UserEntity.GetFirmaId(adapter, User.Identity.Name);
string reportPath = Server.MapPath(string.Format("~/ReportTemplates/{0}/RacunList.xls", firmaId));
FlexCel.XlsAdapter.XlsFile xls = new FlexCel.XlsAdapter.XlsFile();
xls.Open(reportPath);
short godina = ConfigEntity.GetInstance(adapter, firmaId).AktivnaGodina;
RelationPredicateBucket bucket = RacunGlavaPager.CreateBucket(godina, jqGridFilters);
bucket.PredicateExpression.Add(RacunGlavaFields.FirmaId == firmaId);
bucket.Relations.Add(RacunGlavaEntity.Relations.PartnerEntityUsingPartnerId);
IEnumerable <RacunReport> racunReportCollection = NinjaSoftware.EnioNg.CoolJ.DatabaseGeneric.BusinessLogic.RacunReport.GetRacunReportCollection(adapter, bucket, firmaId);
decimal ukupnoSum = racunReportCollection.Where(x => x.RacunGlava.JePdvRacun).Sum(x => x.Ukupno);
ukupnoSum += racunReportCollection.Where(x => !x.RacunGlava.JePdvRacun).Sum(x => x.UkupnoBezPdv);
FlexCel.Report.FlexCelReport report = new FlexCel.Report.FlexCelReport();
report.AddTable("Racun", racunReportCollection);
report.SetValue("UkupnoSum", ukupnoSum);
report.Run(xls);
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
FlexCel.Render.FlexCelPdfExport pdfExport = new FlexCel.Render.FlexCelPdfExport(xls);
pdfExport.BeginExport(ms);
pdfExport.ExportAllVisibleSheets(false, "");
pdfExport.EndExport();
//xls.Save(ms);
ms.Position = 0;
return(File(ms.ToArray(), "application/pdf"));
}
}
private void GenerateReportButton_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
if (!Directory.Exists(Directory.GetCurrentDirectory() + "\\Output\\"))
{
Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\\Output\\");
}
try
{
conn.ConnectionString = ConnectionStringTextBox.Text;
conn.Open();
cmd.Connection = conn;
if (UnknownSummaryMRIActualsRadio.Checked)
{
#region UnknownSummaryMRIActuals
cmd.CommandTimeout = 0;
cmd.CommandText = "stp_S_UnknownSummaryMRIActuals";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("BudgetYear", Convert.ToInt32(BudgetYearParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("BudgetQuater", BudgetQuaterParameterTextBox.Text));
cmd.Parameters.Add(new SqlParameter("DataPriorToDate", Convert.ToDateTime(DataPriorToDateParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("StartPeriod", Convert.ToInt32(StartPeriodParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("EndPeriod", Convert.ToInt32(EndPeriodParameterTextBox.Text)));
//@BudgetYear int,
//@BudgetQuater Varchar(2),
//@DataPriorToDate DateTime,
//@StartPeriod int,
//@EndPeriod int
DataSet result = GetUnknownSummaryMRIActualsDataSet();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataRow newRow = result.Tables[0].NewRow();
//newRow[""] = reader[""];
newRow["SourceCode"] = reader["SourceCode"];
newRow["ProfitabilityActualKey"] = reader["ProfitabilityActualKey"];
newRow["ReferenceCode"] = reader["ReferenceCode"];
newRow["HasActivityTypeUnknown"] = reader["HasActivityTypeUnknown"];
newRow["HasAllocationRegionUnknown"] = reader["HasAllocationRegionUnknown"];
newRow["HasFunctionalDepartmentUnknown"] = reader["HasFunctionalDepartmentUnknown"];
newRow["HasGlAccountUnknown"] = reader["HasGlAccountUnknown"];
newRow["HasGlAccountCategoryUnknown"] = reader["HasGlAccountCategoryUnknown"];
newRow["HasOriginatingRegionUnknown"] = reader["HasOriginatingRegionUnknown"];
newRow["HasPropertyFundUnknown"] = reader["HasPropertyFundUnknown"];
newRow["InValidOriginatingRegionAndFunctionalDepartment"] = reader["InValidOriginatingRegionAndFunctionalDepartment"];
newRow["InValidActivityTypeAndEntity"] = reader["InValidActivityTypeAndEntity"];
newRow["ResolvedBy"] = reader["ResolvedBy"];
newRow["Gr MajorCategoryName"] = reader["Gr MajorCategoryName"];
newRow["Gr MinorCategoryName"] = reader["Gr MinorCategoryName"];
newRow["Gr AccountSubTypeName"] = reader["Gr AccountSubTypeName"];
newRow["Gr FeeOrExpense"] = reader["Gr FeeOrExpense"];
newRow["Gr ReportingEntityName"] = reader["Gr ReportingEntityName"];
newRow["Gr ActivityTypeName"] = reader["Gr ActivityTypeName"];
newRow["Gr OriginatingRegionName"] = reader["Gr OriginatingRegionName"];
newRow["Gr OriginatingSubRegionName"] = reader["Gr OriginatingSubRegionName"];
newRow["Gr AllocationRegionName"] = reader["Gr AllocationRegionName"];
newRow["Gr AllocationSubRegionName"] = reader["Gr AllocationSubRegionName"];
newRow["Gr OverheadName"] = reader["Gr OverheadName"];
newRow["Gr GlAccountCode"] = reader["Gr GlAccountCode"];
newRow["Gr GlAccountName"] = reader["Gr GlAccountName"];
newRow["Gr FunctionalDepartmentName"] = reader["Gr FunctionalDepartmentName"];
newRow["Period"] = reader["Period"];
newRow["Ref"] = reader["Ref"];
newRow["SiteID"] = reader["SiteID"];
newRow["EntityID"] = reader["EntityID"];
newRow["EntityName"] = reader["EntityName"];
newRow["GlAccountCode"] = reader["GlAccountCode"];
newRow["GlAccountName"] = reader["GlAccountName"];
newRow["Department"] = reader["Department"];
newRow["DepartmentDescription"] = reader["DepartmentDescription"];
newRow["JobCode"] = reader["JobCode"];
newRow["JobCodeDescription"] = reader["JobCodeDescription"];
newRow["Amount"] = reader["Amount"];
newRow["Description"] = reader["Description"];
newRow["EnterDate"] = reader["EnterDate"];
newRow["Reversal"] = reader["Reversal"];
newRow["Status"] = reader["Status"];
newRow["Basis"] = reader["Basis"];
newRow["UserId"] = reader["UserId"];
newRow["CorporateDepartmentCode"] = reader["CorporateDepartmentCode"];
newRow["Source"] = reader["Source"];
result.Tables[0].Rows.Add(newRow);
}
//Mapp DataSet to Flexcel
FlexCel.Report.FlexCelReport unknownSummaryMRIActuals = new FlexCel.Report.FlexCelReport();
unknownSummaryMRIActuals.ClearUserFunctions();
unknownSummaryMRIActuals.AddTable(result);
unknownSummaryMRIActuals.SetValue("Date", DateTime.Now);
MemoryStream OutStream = new MemoryStream();
try
{
FileStream InStream = new FileStream(Directory.GetCurrentDirectory() + "\\UnknownSummaryMRIActualsTemplate.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
try
{
unknownSummaryMRIActuals.Run(InStream, OutStream);
}
finally
{
InStream.Close();
}
string filePath = Directory.GetCurrentDirectory() + "\\Output\\" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + " UnknownSummaryMRIActuals.xlsx";
FileStream streamWriter = new FileStream(filePath, FileMode.CreateNew);
BinaryWriter writeBinay = new BinaryWriter(streamWriter);
writeBinay.Write(OutStream.ToArray());
writeBinay.Close();
}
finally
{
OutStream.Close();
}
#endregion
}
else if (UnknownSummaryAllocatedOverheadRadio.Checked)
{
#region UnknownSummaryAllocatedOverhead
cmd.CommandTimeout = 0;
cmd.CommandText = "stp_S_UnknownSummaryAllocatedOverhead";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("BudgetYear", Convert.ToInt32(BudgetYearParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("BudgetQuater", BudgetQuaterParameterTextBox.Text));
cmd.Parameters.Add(new SqlParameter("DataPriorToDate", Convert.ToDateTime(DataPriorToDateParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("StartPeriod", Convert.ToInt32(StartPeriodParameterTextBox.Text)));
cmd.Parameters.Add(new SqlParameter("EndPeriod", Convert.ToInt32(EndPeriodParameterTextBox.Text)));
//@BudgetYear int,
//@BudgetQuater Varchar(2),
//@DataPriorToDate DateTime,
//@StartPeriod int,
//@EndPeriod int
DataSet result = GetUnknownSummaryAllocatedOverheadDataSet();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DataRow newRow = result.Tables[0].NewRow();
//newRow[""] = reader[""];
newRow["SourceCode"] = reader["SourceCode"];
newRow["ProfitabilityActualKey"] = reader["ProfitabilityActualKey"];
newRow["ReferenceCode"] = reader["ReferenceCode"];
newRow["HasActivityTypeUnknown"] = reader["HasActivityTypeUnknown"];
newRow["HasAllocationRegionUnknown"] = reader["HasAllocationRegionUnknown"];
newRow["HasFunctionalDepartmentUnknown"] = reader["HasFunctionalDepartmentUnknown"];
newRow["HasGlAccountUnknown"] = reader["HasGlAccountUnknown"];
newRow["HasGlAccountCategoryUnknown"] = reader["HasGlAccountCategoryUnknown"];
newRow["HasOriginatingRegionUnknown"] = reader["HasOriginatingRegionUnknown"];
newRow["HasPropertyFundUnknown"] = reader["HasPropertyFundUnknown"];
newRow["InValidRegionAndFunctionalDepartment"] = reader["InValidRegionAndFunctionalDepartment"];
newRow["InValidActivityTypeEntity"] = reader["InValidActivityTypeEntity"];
newRow["ResolvedBy"] = reader["ResolvedBy"];
newRow["ExpensePeriod"] = reader["ExpensePeriod"];
newRow["AllocationRegionCode"] = reader["AllocationRegionCode"];
newRow["AllocationRegionName"] = reader["AllocationRegionName"];
newRow["OriginatingRegionCode"] = reader["OriginatingRegionCode"];
newRow["OriginatingRegionSourceCode"] = reader["OriginatingRegionSourceCode"];
newRow["PropertyFundName"] = reader["PropertyFundName"];
newRow["FunctionalDepartmentCode"] = reader["FunctionalDepartmentCode"];
newRow["ActivityTypeCode"] = reader["ActivityTypeCode"];
newRow["ForeignCurrency"] = reader["ForeignCurrency"];
newRow["ForeignActual"] = reader["ForeignActual"];
newRow["GlAccountCode"] = reader["GlAccountCode"];
newRow["EmployeeDisplayName"] = reader["EmployeeDisplayName"];
result.Tables[0].Rows.Add(newRow);
}
//Mapp DataSet to Flexcel
FlexCel.Report.FlexCelReport unknownSummaryAllocatedOverhead = new FlexCel.Report.FlexCelReport();
unknownSummaryAllocatedOverhead.ClearUserFunctions();
unknownSummaryAllocatedOverhead.AddTable(result);
unknownSummaryAllocatedOverhead.SetValue("Date", DateTime.Now);
MemoryStream OutStream = new MemoryStream();
try
{
FileStream InStream = new FileStream(Directory.GetCurrentDirectory() + "\\UnknownSummaryAllocatedOverheadTemplate.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
try
{
unknownSummaryAllocatedOverhead.Run(InStream, OutStream);
}
finally
{
InStream.Close();
}
string filePath = Directory.GetCurrentDirectory() + "\\Output\\" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + " UnknownSummaryAllocatedOverhead.xlsx";
FileStream streamWriter = new FileStream(filePath, FileMode.CreateNew);
BinaryWriter writeBinay = new BinaryWriter(streamWriter);
writeBinay.Write(OutStream.ToArray());
writeBinay.Close();
}
finally
{
OutStream.Close();
}
#endregion
}
else
{
throw new ApplicationException("Invalid Radio Selection");
}
MessageBox.Show("Export Complete", "Exporter", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}