public ActivityLINQ2DB()
//: base("Activity")
: base(MultiTenantHelper.GetConnectionName("Activity")) // !?! Multi-Tenant
{
ActivityLINQ2DBMap.ActivityMap(MappingSchema);
ActivityLINQ2DBMap.ActivityRoleMap(MappingSchema);
}
public AuditTrailLINQ2DB()
//: base("AuditTrail")
: base(MultiTenantHelper.GetConnectionName("AuditTrail")) // !?! Multi-Tenant
{
AuditTrailLINQ2DBMap.AuditTrailConfigurationMap(MappingSchema);
AuditTrailLINQ2DBMap.AuditTrailLogMap(MappingSchema);
}
public static bool ExportRDLC(ZOperationResult operationResult, ref string exportPath, string exportFormat,
string rdlcDirectory, string reportDirectory, string reportName, IDictionary <string, string> reportParameters)
{
try
{
string reportPath = Path.Combine(rdlcDirectory,
MultiTenantHelper.Tenant.Name,
reportDirectory,
reportName + ".rdl");
ReportWriter reportWriter = new ReportWriter(reportPath);
reportWriter.ReportProcessingMode = ProcessingMode.Local;
// Parameter(s)
IList <ReportParameter> reportWriterParameters = new List <ReportParameter>(); // Syncfusion.Reports.EJ.ReportParameter
foreach (KeyValuePair <string, string> pair in reportParameters)
{
ReportParameter reportParameter = new ReportParameter();
reportParameter.Name = pair.Key;
reportParameter.Values.Add(pair.Value);
reportWriterParameters.Add(reportParameter);
}
//reportWriter.SetParameters(reportWriterParameters); // ???
// Data Source(s) & Data Set(s)
global::Syncfusion.RDL.DOM.ReportDefinition reportDefinition = DeSerializeReport(reportPath);
IList <string> dataSetNames = reportWriter.GetDataSetNames();
reportWriter.DataSources.Clear();
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = MultiTenantHelper.GetConnectionName(dataSet.First().Query.DataSourceName);
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
//foreach (var reportParameter in reportWriter.GetParameters()) // ???
foreach (var reportParameter in reportWriterParameters)
{
var defaultValue = reportParameter.Values.First() == null ? null : reportParameter.Values.First();
DbParameter parameter = command.CreateParameter();
//parameter.DbType = GetDbType(reportParameter.DataType); // ???
parameter.DbType = DbType.String;
parameter.ParameterName = "@" + reportParameter.Name;
if (defaultValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = defaultValue;
}
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
reader = command.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportWriter.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
if (operationResult.Ok)
{
// Export
string fileExtension = ".pdf";
WriterFormat writerFormat = WriterFormat.PDF;
switch (exportFormat.ToLower())
{
case "doc":
fileExtension = ".doc";
writerFormat = WriterFormat.Word;
break;
case "xls":
fileExtension = ".xls";
writerFormat = WriterFormat.Excel;
break;
}
//PageSettings pageSettings = new PageSettings();
//pageSettings.LeftMargin = 0.39; // 10mm
//pageSettings.RightMargin = 0.39; // 10mm
//pageSettings.TopMargin = 0.39; // 10mm
//pageSettings.BottomMargin = 0.39; // 10mm
//pageSettings.PageWidth = 8.27; // 210mm
//pageSettings.PageHeight = 11.69; // 297mm
//reportWriter.PageSettings = pageSettings;
exportPath = exportPath.Trim() + fileExtension;
FileStream fileStream = new FileStream(exportPath, FileMode.Create);
reportWriter.Save(fileStream, writerFormat);
fileStream.Close();
operationResult.StatusMessage = exportPath;
}
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
return(operationResult.Ok);
}
public AuditTrailDbContext()
//: base("Name=AuditTrail")
: base("Name=" + MultiTenantHelper.GetConnectionName("AuditTrail"))
{
Setup();
}
private bool ExportSecurity(ZOperationResult operationResult, string templateDirectory, string fileDirectory,
out string filePath)
{
string template = "Security";
string templatePath = LibraryHelper.AddDirectorySeparator(templateDirectory) + template + ".xlsx";
string file = template + "." + String.Format("{0:yyyyMMdd.HHmmssfff}", DateTime.Now) + ".xlsx";
filePath = Path.Combine(fileDirectory, file);
DbConnection connection = null;
ExcelEngine excelEngine = null;
try
{
DbProviderFactory provider;
DbCommand command;
DbDataReader reader;
string connectionName;
System.IO.File.Copy(templatePath, filePath);
excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open(filePath);
//workbook.Version = ExcelVersion.Excel2013;
IWorksheet worksheet;
int row, columns;
// Identity ////////////////////////////////////////////////////////////////////////
connectionName = MultiTenantHelper.GetConnectionName("Identity");
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Users
worksheet = workbook.Worksheets["Users"];
command.CommandText = @"
SELECT
UserName UserName,
Email EMail,
LockoutEnabled Lockout,
LockoutEndDateUtc LockoutEndDate
FROM
AspNetUsers
ORDER BY
UserName
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("UserName");
worksheet.Range[row, column++].Value2 = reader.ToString("EMail");
worksheet.Range[row, column++].Value2 = reader.ToBoolean("Lockout") ? EasyLOB.Resources.PresentationResources.Yes : EasyLOB.Resources.PresentationResources.No;
worksheet.Range[row, column++].ClearDateTime(reader.ToDateTimeNullable("LockoutEndDate"));
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles
worksheet = workbook.Worksheets["Roles"];
command.CommandText = @"
SELECT
Name RoleName
FROM
AspNetRoles
ORDER BY
RoleName
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles by User
worksheet = workbook.Worksheets["Roles by User"];
command.CommandText = @"
SELECT
AspNetUsers.UserName UserName,
AspNetRoles.Name RoleName
FROM
AspNetUserRoles
INNER JOIN AspNetUsers ON
AspNetUsers.Id = AspNetUserRoles.UserId
INNER JOIN AspNetRoles ON
AspNetRoles.Id = AspNetUserRoles.RoleId
ORDER BY
1,2
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("UserName");
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
connection.Close();
// Activity ////////////////////////////////////////////////////////////////////////
connectionName = MultiTenantHelper.GetConnectionName("Activity");
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Activities
worksheet = workbook.Worksheets["Activities"];
command.CommandText = @"
SELECT
Name ActivityName
FROM
EasyLOBActivity
ORDER BY
Name
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("ActivityName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles by Activity
worksheet = workbook.Worksheets["Roles by Activity"];
command.CommandText = @"
SELECT
EasyLOBActivity.Name ActivityName,
RoleName,
Operations
FROM
EasyLOBActivityRole
INNER JOIN EasyLOBActivity ON
EasyLOBActivity.Id = EasyLOBActivityRole.ActivityId
ORDER BY
Name
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("ActivityName");
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
worksheet.Range[row, column++].Value2 = reader.ToString("Operations");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
workbook.Save();
workbook.Close();
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (excelEngine != null)
{
excelEngine.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(operationResult.Ok);
}
public static ApplicationUserManager Create(IdentityFactoryOptions <ApplicationUserManager> options, IOwinContext context) // ???
{
var entities = new[]
{
typeof(ApplicationUser)
};
var configuration = Fluently.Configure()
.Database(MsSqlConfiguration
.MsSql2008
//.ConnectionString(x => x.FromConnectionStringWithKey("Identity"))
.ConnectionString(x => x.FromConnectionStringWithKey(MultiTenantHelper.GetConnectionName("Identity"))) // !?! Multi-Tenant
.Driver <SqlClientDriverEasyLOB>
)
.ExposeConfiguration(x =>
{
x.AddDeserializedMapping(MappingHelper.GetIdentityMappings(entities), null);
})
;
ISessionFactory factory = configuration.BuildSessionFactory();
ISession session = factory.OpenSession();
ApplicationUserManager manager = new ApplicationUserManager(new UserStore <ApplicationUser>(session));
manager.PasswordValidator = new CustomPasswordValidator
{
RequiredLength = 6,
RequireNonLetterOrDigit = false,
RequireDigit = false,
RequireLowercase = true,
RequireUppercase = true
};
manager.UserValidator = new CustomUserValidator(manager)
{
AllowOnlyAlphanumericUserNames = true,
RequireUniqueEmail = true
};
manager.EmailService = new EmailService();
manager.SmsService = new SmsService();
return(manager);
}
public static ApplicationRoleManager Create(IdentityFactoryOptions <ApplicationRoleManager> options, IOwinContext context) // ???
{
var entities = new[]
{
typeof(ApplicationRole)
};
var configuration = Fluently.Configure()
.Database(MsSqlConfiguration
.MsSql2008
//.ConnectionString(x => x.FromConnectionStringWithKey("Identity"))
.ConnectionString(x => x.FromConnectionStringWithKey(MultiTenantHelper.GetConnectionName("Identity"))) // !?! Multi-Tenant
.Driver <SqlClientDriverEasyLOB>
)
.ExposeConfiguration(x =>
{
x.AddDeserializedMapping(MappingHelper.GetIdentityMappings(entities), null);
})
;
ISessionFactory factory = configuration.BuildSessionFactory();
ISession session = factory.OpenSession();
return(new ApplicationRoleManager(new RoleStore <ApplicationRole>(session)));
}
public ActivityDbContext()
//: base("Name=Activity")
: base("Name=" + MultiTenantHelper.GetConnectionName("Activity"))
{
Setup();
}
public ApplicationDbContext()
//: base("Identity")
: base("Name=" + MultiTenantHelper.GetConnectionName("Identity"))
{
}
public IdentityDbContext()
//: base("Name=Identity")
: base("Name=" + MultiTenantHelper.GetConnectionName("Identity"))
{
Setup();
}
/// <summary>
/// Report loaded method that is triggered when report and sub report begins to be loaded.
/// </summary>
/// <param name="reportOptions">The ReportViewer options.</param>
public void OnReportLoaded(ReportViewerOptions reportOptions)
{
// User Parameters
if (HttpContext.Current.Items.Contains("userParameters"))
{
reportOptions.ReportModel.Parameters =
new JavaScriptSerializer().Deserialize <List <global::Syncfusion.Reports.EJ.ReportParameter> >(HttpContext.Current.Items["userParameters"].ToString());
HttpContext.Current.Items.Remove("userParameters");
}
// DataSet(s)
reportOptions.ReportModel.DataSources.Clear();
ReportDefinition reportDefinition = DeSerializeReport(reportOptions.ReportModel.ReportPath);
ReportParameters reportParameters = reportDefinition.ReportParameters;
IList <string> dataSetNames = ReportHelper.GetDataSetNames();
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = MultiTenantHelper.GetConnectionName(dataSet.First().Query.DataSourceName);
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
if (reportOptions.ReportModel.Parameters != null && reportOptions.ReportModel.Parameters.Count() > 0)
{
int index = 0;
foreach (var userParameter in reportOptions.ReportModel.Parameters)
{
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameters[index].DataType);
parameter.ParameterName = "@" + userParameter.Name;
parameter.Value = userParameter.Values.First();
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + userParameter.Name, userParameter.Values.First()); // DbType.String
index++;
}
}
else
{
foreach (var reportParameter in reportParameters)
{
var defaultValue = reportParameter.DefaultValue == null ? null : reportParameter.DefaultValue.Values.First();
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameter.DataType);
parameter.ParameterName = "@" + reportParameter.Name;
if (defaultValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = defaultValue;
}
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
}
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportOptions.ReportModel.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch // (Exception exception)
{
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}