public ActionResult Export()
{
var masterData = new SMOScopeBLL().Todos_completos_action_plan().Where(smo => smo.O_C.ToUpper() == "O" && (smo.ActionPlan != string.Empty && smo.ActionPlan != null)).ToList();
var file = new ExcelCore().GenerarActionReport("LARCA Action Plan Report", masterData);
DownloadFile(file);
return(Content("<script language='javascript' type='text/javascript'>alert('Exported!');document.location = 'Index';</script>"));
}
public void TestClearToolSheet()
{
using (ExcelCore excelCore = new ExcelCore())
{
excelCore.OpenExcel(@"C:\Users\Administrator\Desktop\WorkFiles\Letter Automation\Control turnaround.xlsx", false);
excelCore.SelectSheet("Sheet1");
IList <Row> rows = excelCore.GetSheetByRow();
}
}
private void RunSqlQueryProc(object o)
{
try
{
using (new WaitCursor())
{
Task runQuery = new Task(() =>
{
using (ExcelCore excelIn = new ExcelCore(Model.BaseModel.ExcelFileName, AppDataCore.Settings.AceVersion, Model.BaseModel.HDR))
{
DataTable queryResult = new DataTable();
// Run query
excelIn.RunSql(Model.BaseModel.SqlQuery, ref queryResult);
// Populate result
Model.QueryResult = queryResult;
}
});
runQuery.Start();
runQuery.Wait();
}
WindowsUI.RunWindowDialog(() =>
{
MessageBox.Show(
StringsFunctions.ResourceString("resQueryCompleted"),
StringsFunctions.ResourceString("resInfo"),
MessageBoxButton.OK, MessageBoxImage.Information
);
}
);
}
catch (Exception ex)
{
WindowsUI.RunWindowDialog(() =>
{
MessageBox.Show(
StringsFunctions.ResourceString("resErrorDuringExecution") +
Environment.NewLine + Environment.NewLine + ex.InnerException.Message,
StringsFunctions.ResourceString("resError"),
MessageBoxButton.OK, MessageBoxImage.Hand
);
}
);
}
}
/// <summary>
/// Verbose output -- show result from datatable
/// </summary>
/// <param name="data"></param>
public void DisplayResult(DataTable data)
{
using (ExcelCore excel = new ExcelCore())
{
excel.IterateOverData(data,
delegate(string value, int x, int y)
{
if (x == 1)
{
Console.WriteLine();
}
Console.Write(value + "\t");
}
);
}
}
public void ExcelCoreTest()
{
using (ExcelCore excelCore = new ExcelCore())
{
excelCore.OpenExcel(@"C:\Users\Administrator\Desktop\TestExcel\Letter Automation V1.19.1.xlsm", false);
IList <string> sheetNames = excelCore.GetSheetNames();
excelCore.SelectSheet("Data");
//DeptComboBox
IList <Cell> language = excelCore.GetRangeByName("Language");
IList <Cell> departmentLevel = excelCore.GetRange(new Cell()
{
ColumnName = "K", RowIndex = 42
},
new Cell()
{
ColumnName = "K", RowIndex = 46
});
IList <Cell> clientLevel = excelCore.GetRange(new Cell()
{
ColumnName = "L", RowIndex = 40
},
new Cell()
{
ColumnName = "L", RowIndex = 41
});
IList <Cell> EELetterlevel = excelCore.GetRange(new Cell()
{
ColumnName = "M", RowIndex = 40
},
new Cell()
{
ColumnName = "M", RowIndex = 48
});
IList <Cell> EETransactionlevel = excelCore.GetRange(new Cell()
{
ColumnName = "N", RowIndex = 40
},
new Cell()
{
ColumnName = "N", RowIndex = 72
});
}
}
/// <summary>
/// Display common information about Excel worksheets
/// </summary>
/// <param name="excel"></param>
public void DisplayWorksheetInfo(ExcelCore excel)
{
List <string> worksheets = excel.GetWorksheets();
Console.WriteLine("List of available worksheets in file \"{0}\":", excel.FileName);
foreach (String name in worksheets)
{
Console.WriteLine("\t\"{0}\"", name);
ExcelObject.Worksheet worksheet = excel.GetWorksheet(name);
Console.WriteLine("\tLast row with data: {0}; Last column with data: {1}\n",
excel.GetCountOfRows(worksheet),
excel.GetCountOfCols(worksheet)
);
}
}
private void RunAnalyticsProc(object o)
{
try
{
using (new WaitCursor())
{
using (ExcelCore excelIn = new ExcelCore(Model.BaseModel.ExcelFileName, AppDataCore.Settings.AceVersion, Model.BaseModel.HDR))
{
List <string> worksheets = excelIn.GetWorksheets();
Model.WorksheetItems.Clear();
foreach (String name in worksheets)
{
WorksheetItemType worksheetItem = new WorksheetItemType();
worksheetItem.WorksheetName = name;
ExcelObject.Worksheet worksheet = excelIn.GetWorksheet(name);
worksheetItem.RowCount = excelIn.GetCountOfRows(worksheet);
worksheetItem.ColCount = excelIn.GetCountOfCols(worksheet);
Model.WorksheetItems.Add(worksheetItem);
}
}
}
}
catch (Exception ex)
{
WindowsUI.RunWindowDialog(() =>
{
MessageBox.Show(
String.Format(StringsFunctions.ResourceString("resErrorDuringOpening"), Model.BaseModel.ExcelFileName) +
Environment.NewLine + Environment.NewLine + ex.Message,
StringsFunctions.ResourceString("resError"),
MessageBoxButton.OK, MessageBoxImage.Hand
);
}
);
}
}
public ActionResult Upload(Larca2.Views.ViewModels.ManualUploadViewModel model)
{
if (Request.Files.Count > 0)
{
var file = Request.Files[0];
if (file != null && file.ContentLength > 0 && new MailingCore().GetXmlMimes().Exists(s => s == file.ContentType))
{
// Generamos el archivo fisico y lo guardamos en un directorio
var date = DateTime.Now.ToString("ddMMMyyy");
var random = new Random().Next(0, 1000);
string fileName = String.Format("Manual_{0}_MasterData_{1}.xlsx", date, random);
string filePath = Path.Combine(Server.MapPath("~/App_Data/"), fileName);
file.SaveAs(filePath);
ViewBag.Result = "File Uploaded and Processed!";
if (!model.Parcial)
{
var rows = new ExcelCore().ProcesarExcel(fileName, TipoProceso.Total);
ViewData["MasterRows"] = rows;
ViewBag.Result = rows.Count > 0 ? "Complete File Uploaded and Processed!" : "No Rows Processed. Check XLS Reading Parameters.";
}
else
{
var rows = new ExcelCore().ProcesarExcel(fileName, TipoProceso.Parcial);
ViewData["MasterRows"] = rows;
ViewBag.Result = rows.Count > 0 ? "Partial File Uploaded and Processed!" : "No Rows Processed. Check XLS Reading Parameters.";
}
}
else
{
ViewBag.Result = "Invalid File! Try again.";
}
}
if (ViewData["MasterRows"] == null)
{
ViewData["MasterRows"] = new List <MasterRow>();
}
return(View("Index"));
}
public ActionResult Export()
{
var model = new Larca2.Views.ViewModels.SMOScopeViewModel();
/*try
* {
* ViewData["MasterRows"] = new SMOScopeBLL().Todos().ToList();
* }
* catch
* {
* ViewData["MasterRows"] = new List<LARCA20_SmoScope>();
* }*/
//Declaro BLLs e inicializo viewModel
Larca2.Views.ViewModels.SMOScopeViewModel viewModel = new Larca2.Views.ViewModels.SMOScopeViewModel();
LARCA2.Business.Services.UsuariosBLL repoUsuarios = new LARCA2.Business.Services.UsuariosBLL();
LARCA2.Business.Services.UserOwnerBLL uobll = new LARCA2.Business.Services.UserOwnerBLL();
LARCA2.Business.Services.SMOScopeBLL ssbll = new LARCA2.Business.Services.SMOScopeBLL();
//Reviso el usuario logueado, sino como prueba traigo al de ID 2
LARCA2.Data.DatabaseModels.LARCA20_Users user = (LARCA2.Data.DatabaseModels.LARCA20_Users)Session["Usuario"];
if (user == null)
{
user = repoUsuarios.Traer(2);
}
//determino el rol del usuario para entender qué filtros y funcionalidades disponer
LARCA2.Business.Services.RolesBLL robll = new LARCA2.Business.Services.RolesBLL();
LARCA2.Business.Services.UsuariosRolesBLL urbll = new LARCA2.Business.Services.UsuariosRolesBLL();
viewModel.userRole = robll.Traer(urbll.Traer(user.Id).RefIdRoles).Description;
viewModel.idRole = robll.Traer(urbll.Traer(user.Id).RefIdRoles).Id;
//Obtengo los registros de User Owner con IdUser igual al del usuario logueado
List <LARCA2.Data.DatabaseModels.LARCA20_User_Owner> luo = uobll.TraerPorIdUsuario(user.Id);
// Quito de la lista de SMO y BU de los filtros aquellos no contemplados por un registro existente de UserOwner para el usuario logueado
// viewModel.SMOList = viewModel.SMOList.Where(x => luo.Exists(y => y.IdSmo.ToString() == x.Value) || x.Value == "0").ToList();
// viewModel.BUList = viewModel.BUList.Where(x => luo.Exists(y => y.IdBU.ToString() == x.Value) || x.Value == "0").ToList();
//Filtro los registros de la tabla SmoScope en función del rol y los permisos para cada uno
//Aquellos cuyos RefIdSMO, RefIdBU, y RefIdOwner coinciden con los de un registro de la tabla UserOwner para el usuario logueado, permanecen
//Con que algunos de los campos en cuestion difiera, el registro de SmoScope ya no será mostrado.
if (viewModel.idRole != 1 && viewModel.idRole != 3)
{
List <LARCA2.Data.DatabaseModels.LARCA20_SmoScope> smoscopeact;
viewModel.RegistrosSMO = new List <LARCA2.Data.DatabaseModels.LARCA20_SmoScope>();
foreach (LARCA2.Data.DatabaseModels.LARCA20_User_Owner actualLuo in luo)
{
smoscopeact = ssbll.Filtrar(actualLuo.IdBU.ToString(), actualLuo.IdSmo.ToString(), viewModel.idRole.ToString()).Where(x => x.RefIdOwner == actualLuo.IdOwner).ToList();
if (smoscopeact != null)
{
viewModel.RegistrosSMO.AddRange(smoscopeact);
}
}
}
else
{
viewModel.RegistrosSMO = ssbll.Filtrar("0", "0", viewModel.idRole.ToString()).ToList();
}
viewModel.RegistrosSMO = viewModel.RegistrosSMO.Distinct().ToList();
var masterData = viewModel.RegistrosSMO.ToList();
//var masterData = new SMOScopeBLL().Todos().ToList();
var file = new ExcelCore().GenerarExcelPendingAnalysis("LARCA Pending Analysis", masterData);
DownloadFile(file);
return(Content("<script language='javascript' type='text/javascript'>alert('Exported!');document.location = 'Index';</script>"));
}
static void Main(string[] args)
{
CoreFunctions coreFunctions = new CoreFunctions();
// Parse command line params
AppData appData = coreFunctions.ParseCommandLineParams(args);
appData = coreFunctions.ValidateCommandLineParams(appData);
if (appData.showHelp)
{
coreFunctions.ShowHelp();
}
else
{
// Open file and ...
try
{
using (ExcelCore excelIn = new ExcelCore(appData.inFile, appData.acever, appData.useHdr == "Y" ? true : false))
{
if (appData.showInfo)
{
coreFunctions.DisplayWorksheetInfo(excelIn);
}
else
{
DataTable queryResult = new DataTable();
CancellationTokenSource tokenSource = new CancellationTokenSource();
try
{
Console.WriteLine("\nSQL Query execution running at: {0}", DateTime.Now.ToString());
// Start progress...
coreFunctions.ShowProgressInConsole(tokenSource.Token);
// Start query
excelIn.RunSql(appData.query, ref queryResult);
// Cancel progress
coreFunctions.StopProgress(tokenSource);
tokenSource = null;
Console.WriteLine("\nSQL Query execution completed at: {0}", DateTime.Now.ToString());
if (appData.resultToFile)
{
excelIn.SaveResultToExcelFile(appData.outFile, queryResult);
}
else
{
coreFunctions.DisplayResult(queryResult);
}
}
catch (Exception ex)
{
// Cancel progress
if (tokenSource != null)
{
coreFunctions.StopProgress(tokenSource);
}
Console.WriteLine(
"\nThe an error has been occured during executing the SQL query: \nSQL Query: \"{0}\"\nFile: \"{1}\"\nError message: {2}",
appData.query,
appData.inFile,
ex.Message
);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("\nThe an error has been occured during accessing Excel file \"{0}\"\nError message: {1}", appData.inFile, ex.Message);
}
}
coreFunctions.ByeMessage();
}
public ActionResult LarcaNewsProcess(Larca2.Views.ViewModels.LAScopeViewModel model, string command, string txtType)
{
var smo = model.smo;
var rbu = model.rbu;
var type = txtType;
var masterRows = new List <ReportRow>();
switch (command)
{
case "Filter by BU":
masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty);
break;
case "Filter by SMO":
if (smo != "1" && smo != "2")
{
masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty);
}
else
{
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo);
}
break;
}
if (command.Equals("Export Excel"))
{
if (rbu != "0")
{
masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty);
}
else
{
if (smo != "1" && smo != "2")
{
masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty);
}
else
{
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo);
}
}
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
// Download
DownloadFile(file);
}
else
{
if (command.Equals("Send Email"))
{
string datafin = "";
if (rbu != "0")
{
masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty);
datafin = rbu;
}
else
{
if (smo != "1" && smo != "2")
{
masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty);
datafin = smo;
}
else
{
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo);
datafin = smo;
}
}
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file, datafin);
ViewBag.Result = "File Exported and sent!.";
}
else
{
if (command.Equals("Send All Reports"))
{
foreach (var item in model.SMOList)
{
if (item.Text == "LA")
{
item.Value = "1";
masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, item.Value);
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file, item.Text);
}
else
{
masterRows = new ReportsBLL().LARCANews(item.Value, string.Empty, string.Empty);
var file2 = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file2, item.Text);
}
}
foreach (var item in model.BUList)
{
masterRows = new ReportsBLL().LARCANews(string.Empty, item.Value, string.Empty);
var file = new ExcelCore().GenerarExcel("LARCA News", masterRows);
new MailingCore().EnviarReporte(file, item.Text);
}
ViewBag.Result = "Multiple Files Exported and sent!.";
}
}
}
ViewData["MasterRows"] = masterRows;
return(View("Index", model));
}
/// <summary>
/// Metodo que se encarga de leer todos los emails de la casilla suministrada por P&G, levantara solo los que pertenezcan a LARCA
/// </summary>
/// <returns></returns>
public List <MasterRow> ProcesarEmails()
{
var list = new List <MasterRow>();
const string subject = "XLS SAMPLE";
const int port = 110;
var attachments = new ApplicationDataBLL().TraerCantidadAdjuntos();
var emailsService = new EmailsBLL();
using (var client = new Pop3Client())
{
try
{
// Conexion al servidor de emails
client.Connect(ServerPop, port, false);
client.Authenticate(UserName, Password);
int messageCount = client.GetMessageCount();
int tope = messageCount - 1000;
if (tope < 0)
{
tope = 0;
}
for (int i = messageCount; i > tope; i--)
{
try
{
Message message = client.GetMessage(i);
// Si el asunto es EL indicado (Capitalizando todo)
if (message.Headers.Subject.ToUpper() == subject.ToUpper())
{
// Si el email no se encuentra ya procesado en nuestra base
if (!emailsService.EmailExiste(message.Headers.MessageId))
{
// Recorremos los adjuntos y los descargamos (en caso que sea la cantidad indicada)
var adjuntos = message.FindAllAttachments();
if (adjuntos.Count == attachments)
{
foreach (var adjunto in adjuntos)
{
// Si el adjunto contiene el MIME tipo Excel
if (GetXmlMimes().Exists(s => s == adjunto.ContentType.MediaType))
{
// Generamos el archivo fisico y lo guardamos en un directorio
var date = DateTime.Now.ToString("ddMMMyyy");
var random = new Random().Next(0, 1000);
string fileName = String.Format("{0}_MasterData_{1}.xlsx", date, random);
string filePath = Path.Combine(HttpContext.Current.Server.MapPath("~/App_Data/"), fileName);
var stream = new FileStream(filePath, FileMode.Create);
var binaryStream = new BinaryWriter(stream);
binaryStream.Write(adjunto.Body);
stream.Close();
binaryStream.Close();
// Detectamos que tipo de proceso hay que aplicarle al archivo en base a su dia de la semana
var email = new LARCA20_Emails();
if (DateTime.Now.DayOfWeek == DayOfWeek.Tuesday)
{
email.processtype = (int)TipoProceso.Parcial;
}
else
{
email.processtype = (int)TipoProceso.Total;
}
// Completamos la info del email para guardar en la base
email.MessageID = message.Headers.MessageId;
email.date = message.Headers.DateSent;
email.deleted = false;
email.filename = fileName;
// Enviamos a procesar el Excel descargado del email
try
{
list = new ExcelCore().ProcesarExcel(email.filename, (TipoProceso)email.processtype);
email.processed = true;
emailsService.Guardar(email);
}
catch
{
list = new List <MasterRow>();
email.processed = false;
emailsService.Guardar(email);
}
break;
}
}
}
else
{
EnviarAlertaAdjuntos_Admin(message);
// Enviar email que no esta la cantidad de adjuntos que corresponde
}
}
}
}
catch (Exception ex)
{
// Logggear Excepcion
}
}
}
catch (Exception ex)
{
// Logggear Excepcion
}
return(list);
}
}
