private static void LibraryEdmDemo(IEdmManager edmManager)
{
Console.WriteLine("\nEDM Demo\n");
string rootDirectory = ConfigurationHelper.AppSettings <string>("EDM.FileSystemDirectory");
Console.WriteLine("IMPORTANT: Create \"" + rootDirectory + "\" directory !");
Console.Write("\nPress <KEY> to continue... ");
Console.ReadKey();
Console.WriteLine("\n");
string txtFilePath = LibraryHelper.AddDirectorySeparator(rootDirectory) + "txt.txt";
using (StreamWriter writer = new StreamWriter(txtFilePath))
{
writer.Write("EasyLOB");
}
byte[] file;
int key;
string entityName = "Entity", exportPath;
ZFileTypes fileType;
// 1
Console.WriteLine("1");
key = 1;
fileType = ZFileTypes.ftTXT;
exportPath = LibraryHelper.AddDirectorySeparator(rootDirectory) + "txt.1.txt";
Console.WriteLine(" Write TXT");
edmManager.WriteFile(entityName, key, fileType, txtFilePath);
Console.WriteLine(" Read TXT");
file = edmManager.ReadFile(entityName, key, fileType);
File.WriteAllBytes(exportPath, file);
//Console.WriteLine(" Delete TXT");
//edmManager.EdmEngine.DeleteFile(entityName, key, fileType);
// 101
Console.WriteLine("101");
key = 101;
fileType = ZFileTypes.ftTXT;
exportPath = LibraryHelper.AddDirectorySeparator(rootDirectory) + "txt.101.txt";
Console.WriteLine(" Write TXT");
edmManager.WriteFile(entityName, key, fileType, txtFilePath);
Console.WriteLine(" Read TXT");
file = edmManager.ReadFile(entityName, key, fileType);
File.WriteAllBytes(exportPath, file);
//Console.WriteLine(" Delete TXT");
//edmManager.edmEngine.DeleteFile(entityName, key, fileType);
}
public string GetFilePath(string entityName, int key, ZFileTypes fileType, bool create)
{
string filePath = "";
string extension = LibraryHelper.GetFileExtension(fileType);
string workingDirectory = "";
try
{
workingDirectory = ftpClient.GetWorkingDirectory();
entityName = (entityName == null) ? "" : entityName;
string entityKey = String.Format("{0:000000000}", (key / 100) * 100);
string directoryPath = LibraryHelper.AddDirectorySeparator(RootDirectory) +
((entityName == "") ? entityName : entityName + "/") +
entityKey;
FTPCreateDirectoryPath(directoryPath);
/*
* ftpClient.SetWorkingDirectory("/");
* if (!FTPDirectoryExists(path) && create) // /root/entityName/entityTree
* {
* if (FTPCreateDirectory(RootDirectory))
* {
* ftpClient.SetWorkingDirectory(RootDirectory);
* if (FTPCreateDirectory(entityName))
* {
* ftpClient.SetWorkingDirectory(entityName);
* FTPCreateDirectory(entityTree);
* }
* }
* }
*/
ftpClient.SetWorkingDirectory("/");
if (FTPDirectoryExists(directoryPath))
{
filePath = directoryPath + "/" + String.Format("{0:000000000}", key) + extension;
}
}
finally
{
if (!String.IsNullOrEmpty(workingDirectory))
{
ftpClient.SetWorkingDirectory(workingDirectory);
}
}
return(filePath);
}
private static void LibraryEdmFilePathDemo(IEdmManager edmManager)
{
Console.WriteLine("\nEDM Demo\n");
string rootDirectory = LibraryHelper.AppSettings <string>("EDM.FileSystemDirectory");
Console.WriteLine("IMPORTANT: Create \"" + rootDirectory + "\" directory !");
Console.Write("\nPress <KEY> to continue... ");
Console.ReadKey();
Console.WriteLine("\n");
string txtFilePath = LibraryHelper.AddDirectorySeparator(rootDirectory) + "txt.txt";
using (StreamWriter writer = new StreamWriter(txtFilePath))
{
writer.Write("EasyLOB");
}
byte[] file;
string edmFilePath, exportPath;
// 1
Console.WriteLine("A/B/C");
edmFilePath = "A/B/C/txt.txt";
exportPath = LibraryHelper.AddDirectorySeparator(rootDirectory) + "txt.1.txt";
Console.WriteLine(" Write TXT");
edmManager.WriteFile(edmFilePath, txtFilePath);
Console.WriteLine(" Read TXT");
file = edmManager.ReadFile(edmFilePath);
File.WriteAllBytes(exportPath, file);
//Console.WriteLine(" Delete TXT");
//edmManager.EdmEngine.DeleteFile(edmFilePath);
// 101
Console.WriteLine("101");
edmFilePath = "A/D/E/txt.txt";
exportPath = LibraryHelper.AddDirectorySeparator(rootDirectory) + "txt.101.txt";
Console.WriteLine(" Write TXT");
edmManager.WriteFile(edmFilePath, txtFilePath);
Console.WriteLine(" Read TXT");
file = edmManager.ReadFile(edmFilePath);
File.WriteAllBytes(exportPath, file);
//Console.WriteLine(" Delete TXT");
//edmManager.EdmEngine.DeleteFile(edmFilePath);
}
public string GetFilePath(string edmFilePath, bool create)
{
string filePath = "";
string directoryPath = Path.GetDirectoryName(LibraryHelper.AddDirectorySeparator(RootDirectory) + edmFilePath);
if (!Directory.Exists(directoryPath) && create)
{
Directory.CreateDirectory(directoryPath);
}
if (Directory.Exists(directoryPath))
{
filePath = LibraryHelper.AddDirectorySeparator(RootDirectory) + edmFilePath;
}
return(filePath);
}
public string GetFilePath(string entityName, int key, ZFileTypes fileType, bool create)
{
string filePath = "";
string extension = LibraryHelper.GetFileExtension(fileType);
entityName = (entityName == null) ? "" : entityName;
string entityKey = String.Format("{0:000000000}", (key / 100) * 100);
string directoryPath = LibraryHelper.AddDirectorySeparator(RootDirectory) +
((entityName == "") ? entityName : entityName + "/") +
entityKey;
if (!Directory.Exists(directoryPath) && create)
{
Directory.CreateDirectory(directoryPath);
}
if (Directory.Exists(directoryPath))
{
filePath = directoryPath + "/" + String.Format("{0:000000000}", key) + extension;
}
return(filePath);
}
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 bool ExportAlbumByArtistXLSX(ZOperationResult operationResult, string templateDirectory, string fileDirectory,
out string filePath)
{
string template = "AlbumByArtist";
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;
string connectionName = "Chinook";
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"
SELECT
Artist.ArtistId
,Artist.Name ArtistName
,Album.AlbumId
,Album.Title AlbumTitle
FROM
Artist
LEFT JOIN Album ON
Album.ArtistId = Artist.ArtistId
ORDER BY
Artist.Name
,Album.Title
";
//parameter = command.CreateParameter();
//parameter.DbType = DbType.DateTime;
//parameter.ParameterName = "@Data";
//parameter.Value = viewModel.XDateTime;
//command.Parameters.Add(parameter);
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;
// Sintético
worksheet = workbook.Worksheets[0];
worksheet.Range[1, 4].Value2 = String.Format("{0:dd/MM/yyyy}", DateTime.Today);
// Analítico
worksheet = workbook.Worksheets[2];
reader = command.ExecuteReader();
int row = 2, column;
while (reader.Read())
{
column = 1;
worksheet.Range[row, column++].Value2 = reader.ToInt32("ArtistId");
worksheet.Range[row, column++].Value2 = reader.ToString("ArtistName");
worksheet.Range[row, column++].Value2 = reader.ToInt32("AlbumId");
worksheet.Range[row, column++].Value2 = reader.ToString("AlbumTitle");
row++;
}
reader.Close();
worksheet.AutoAlign(1, 4);
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 virtual ActionResult ImportGenreAjax()
{
ZOperationResult operationResult = new ZOperationResult();
string path = "";
bool isUploaded = false;
try
{
if (!IsAuthorized("ImportGenre", operationResult))
{
return(View("OperationResult", new OperationResultModel(operationResult)));
}
else
{
HttpPostedFileBase upload = Request.Files["Upload"];
if (upload != null && upload.ContentLength != 0)
{
// Save file
string file = Path.GetFileName(upload.FileName);
path = Server.MapPath(LibraryHelper.AddDirectorySeparator(LibraryHelper.AppSettings <string>("DirectoryImport")) + file);
upload.SaveAs(path);
isUploaded = true;
// Read file and Create Genre
// Application
//IChinookGenericApplication<Genre> genreApplication =
// DependencyResolver.Current.GetService<IChinookGenericApplication<Genre>>();
//Application.ImportGenreTXTApplication(operationResult, path, genreApplication);
// OR
// Persistence
//IChinookUnitOfWork unitOfWork =
// DependencyResolver.Current.GetService<IChinookUnitOfWork>();
//Application.ImportGenreTXTPersistence(operationResult, path, unitOfWork);
}
}
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (!String.IsNullOrEmpty(path))
{
System.IO.File.Delete(path);
}
}
if (operationResult.Ok)
{
return(JsonResultSuccess(new { IsUploaded = isUploaded, OperationResult = operationResult }));
}
else
{
return(JsonResultFailure(new { OperationResult = operationResult }));
}
}