public ParametrosMaestrosDAO()
{
list_parametromaestro = new List <ParametrosMaestros>();
entidad = null;
reader = null;
clientResponse = new ClientResponse();
clientResponse.Status = "OK";
}
public ClientResponse getLstParametroMaestro(string skey)
{
try
{
using (conexion = new SqlConnection(ConexionDAO.cnx))
{
using (comando = new SqlCommand("usp_sel_parametros_maestros", conexion))
{
comando.Parameters.AddWithValue("@skey", skey);
comando.CommandType = CommandType.StoredProcedure;
conexion.Open();
using (reader = comando.ExecuteReader())
{
while (reader.Read())
{
entidad = new ParametrosMaestros();
entidad.valor = Convert.ToString(reader["valor"] == DBNull.Value ? "" : reader["valor"]);
entidad.descripcion = Convert.ToString(reader["descripcion"] == DBNull.Value ? "" : reader["descripcion"]);
list_parametromaestro.Add(entidad);
}
}
clientResponse.DataJson = JsonConvert.SerializeObject(list_parametromaestro).ToString();
}
}
}
catch (Exception ex)
{
clientResponse.Mensaje = ex.Message;
clientResponse.Status = "ERROR";
}
finally
{
conexion.Close();
conexion.Dispose();
comando.Dispose();
reader.Dispose();
}
return(clientResponse);
}
public static ClientResponse getExportarExcel(int id_cbo_origenfil, int id_cbo_grupo_consultar)
{
ClientResponse response;
try
{
string hora = DateTime.Now.ToString("yyyyMMddhhmmss");
ClientResponse responserutaexcel;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responserutaexcel = dbParametrosMaestro.getObjParametroMaestro("RUTA_DESCARGA_EXCEL");
}
ParametrosMaestros rutaexcel = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responserutaexcel.DataJson);
ClientResponse responserutaexcelsite;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responserutaexcelsite = dbParametrosMaestro.getObjParametroMaestro("RUTA_SITE_DESCARGA_EXCEL");
}
ParametrosMaestros rutaexcelsite = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responserutaexcelsite.DataJson);
if (Directory.Exists(rutaexcel.valor))
{
Directory.Delete(rutaexcel.valor, true);
}
string savepath = rutaexcel.valor + hora;
using (GestionCorreoDAO dbGestionCorreo = new GestionCorreoDAO())
{
response = dbGestionCorreo.getExportarCorreos(id_cbo_origenfil, id_cbo_grupo_consultar);
}
List <GestionCorreo> listCorreos = Newtonsoft.Json.JsonConvert.DeserializeObject <List <GestionCorreo> >(response.DataJson);
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
try
{
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
workSheet.Cells[1, "A"] = "Grupo";
workSheet.Cells[1, "B"] = "Nombre 1";
workSheet.Cells[1, "C"] = "Nombre 2";
workSheet.Cells[1, "D"] = "Paterno";
workSheet.Cells[1, "E"] = "Materno";
workSheet.Cells[1, "F"] = "Email";
workSheet.Cells[1, "G"] = "Estado";
workSheet.Cells[1, "H"] = "Fecha Baja";
// ------------------------------------------------
// Populate sheet with some real data from "cars" list
// ------------------------------------------------
int row = 2; // start row (in row 1 are header cells)
foreach (GestionCorreo car in listCorreos)
{
workSheet.Cells[row, "A"] = car.grupocorreo.descripcion;
workSheet.Cells[row, "B"] = car.Nombre1;
workSheet.Cells[row, "C"] = car.Nombre2;
workSheet.Cells[row, "D"] = car.ApePaterno;
workSheet.Cells[row, "E"] = car.ApeMaterno;
workSheet.Cells[row, "F"] = car.Email;
workSheet.Cells[row, "G"] = car.descestado;
workSheet.Cells[row, "H"] = car.fechabaja;
row++;
}
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
if (!Directory.Exists(savepath))
{
Directory.CreateDirectory(savepath);
}
// Define filename
string fileNamea = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
string fileName = savepath + "\\" + "ExcelData.xlsx";
// Save this data as a file
workSheet.SaveAs(fileName);
// Display SUCCESS messager
response.Mensaje = "Ok";
response.DataJson = rutaexcelsite.valor + hora + "/" + "ExcelData.xlsx";
}
catch (Exception exception)
{
response.Mensaje = "Error";
response.DataJson = exception.Message;
}
finally
{
// Quit Excel application
excel.Quit();
// Release COM objects (very important!)
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
catch (Exception exception)
{
throw exception;
}
return(response);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Expires = -1;
//string ruta_hota_name = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority);
try
{
string hora = DateTime.Now.ToString("yyyyMMddhhmmss");
string tempPath = "";
string txt_descripcion = context.Request["txt_descripcion"];
int txt_codigo = Convert.ToInt32(context.Request["txt_codigo"]);
string FiledataHTMLName = context.Request["FiledataHTMLName"];
HttpFileCollection uploadFiles = context.Request.Files;
plantilla.id = txt_codigo;
plantilla.descripcion = txt_descripcion;
//string FolderPath = System.Configuration.ConfigurationManager.AppSettings["FolderPath"];
ClientResponse responserutahtml;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responserutahtml = dbParametrosMaestro.getObjParametroMaestro("RUTA_CARGAHTMLPLANTILLACORREOS");
}
ParametrosMaestros rutahtml = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responserutahtml.DataJson);
ClientResponse responserutaimage;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responserutaimage = dbParametrosMaestro.getObjParametroMaestro("RUTA_CARGAIMAGENPLANTILLACORREOS");
}
ParametrosMaestros rutaimage = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responserutaimage.DataJson);
ClientResponse responserutasiteimage;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responserutasiteimage = dbParametrosMaestro.getObjParametroMaestro("RUTA_SITE_CARGAIMAGENPLANTILLACORREOS");
}
ParametrosMaestros rutasiteimage = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responserutasiteimage.DataJson);
for (int i = 0; i < uploadFiles.Count; i++)
{
HttpPostedFile postedFile = uploadFiles[i];
if (postedFile.FileName.Equals(FiledataHTMLName))
{
tempPath = rutahtml.valor + hora;
string savepath = "";
// savepath = context.Server.MapPath(tempPath);
savepath = tempPath;// context.Server.MapPath(tempPath);
if (!Directory.Exists(savepath))
{
Directory.CreateDirectory(savepath);
}
string filename = postedFile.FileName;
string files = savepath + @"/" + filename;
postedFile.SaveAs(files);
plantilla.NombreArchivoHtml = filename;
plantilla.id_estado = 1;
plantilla.ruta_plantilla_html = files;
}
else
{
plantilla_detalle = new Plantilla_Detalle();
tempPath = rutaimage.valor + hora;
string savepath = "";
//savepath = context.Server.MapPath(tempPath);
savepath = tempPath;// context.Server.MapPath(tempPath);
if (!Directory.Exists(savepath))
{
Directory.CreateDirectory(savepath);
}
string filename = postedFile.FileName;
string files = savepath + @"/" + filename;
postedFile.SaveAs(files);
plantilla_detalle.NombreArchivoImagen = filename;
plantilla_detalle.ruta_imagen = files;
plantilla_detalle.id_estado = 1;
// plantilla_detalle.ruta_site_imagen = ruta_hota_name + "/" + FolderPath + rutaimage.valor + hora + "/" + filename;
plantilla_detalle.ruta_site_imagen = rutasiteimage.valor + hora + "/" + filename;
list_plantilla_detalle.Add(plantilla_detalle);
}
}
plantilla.list_plantilla_detalle = list_plantilla_detalle;
//Replace SRC plantilla html
HtmlDocument document = new HtmlDocument();
if (plantilla.ruta_plantilla_html != null)
{
document.Load(plantilla.ruta_plantilla_html);
document.DocumentNode.Descendants("img")
.Where(e =>
{
string src = e.GetAttributeValue("src", null) ?? "";
return(!string.IsNullOrEmpty(src)); // && src.StartsWith("data:image");
})
.ToList()
.ForEach(x =>
{
string currentSrcValue = string.Empty;
currentSrcValue = x.GetAttributeValue("src", null);
Plantilla_Detalle objeto = plantilla.list_plantilla_detalle.Where(i => i.NombreArchivoImagen.ToUpper().Equals(currentSrcValue.ToUpper())).FirstOrDefault();
if (objeto != null)
{
x.SetAttributeValue("src", objeto.ruta_site_imagen);
}
});
document.Save(plantilla.ruta_plantilla_html);
document.DocumentNode.Descendants("a")
.Where(e =>
{
string src = e.GetAttributeValue("href", null) ?? "";
return(!string.IsNullOrEmpty(src)); // && src.StartsWith("data:image");
})
.ToList()
.ForEach(x =>
{
string currentSrcValue = string.Empty;
currentSrcValue = x.GetAttributeValue("href", null);
if (currentSrcValue != "{linkdardebaja}" && !currentSrcValue.Contains("http"))
{
Plantilla_Detalle objeto = plantilla.list_plantilla_detalle.Where(i => i.NombreArchivoImagen.ToUpper().Equals(currentSrcValue.ToUpper())).FirstOrDefault();
if (objeto != null)
{
x.SetAttributeValue("href", objeto.ruta_site_imagen);
}
}
});
document.Save(plantilla.ruta_plantilla_html);
HtmlDocument document1 = new HtmlDocument();
document1.Load(plantilla.ruta_plantilla_html);
plantilla.fl_parrafo = 0;
try
{
//List<HtmlNode> list = document1.DocumentNode.SelectNodes("p").ToList();
//plantilla.fl_parrafo = list.Count > 0 ? 1 : 0;
document.DocumentNode.Descendants("p")
.Where(e =>
{
string src = e.GetAttributeValue("type", null) ?? "";
return(!string.IsNullOrEmpty(src));
})
.ToList()
.ForEach(x =>
{
string currentSrcValue = string.Empty;
currentSrcValue = x.GetAttributeValue("type", null);
if (currentSrcValue == "por ingresar")
{
plantilla.fl_parrafo = 1;
}
});
}
catch (Exception)
{
plantilla.fl_parrafo = 0;
}
}
ClientResponse response;
using (PlantillaDAO dbPlanilla = new PlantillaDAO())
{
if (txt_codigo == 0)
{
response = dbPlanilla.InsertPantilla(plantilla);
}
else
{
if (plantilla.ruta_plantilla_html != null) //si adjunto html se elminar el existente
{
ClientResponse responseplantilla = dbPlanilla.getPlantillaXId(plantilla.id);
Plantilla objetoplantilla = Newtonsoft.Json.JsonConvert.DeserializeObject <Plantilla>(responseplantilla.DataJson);
FileInfo fi = new FileInfo(objetoplantilla.ruta_plantilla_html);
fi.Delete();
}
response = dbPlanilla.UpdatePantilla(plantilla);
}
}
var result = new
{
Result = response.Status,
Mensaje = response.Mensaje
};
context.Response.Write(JsonConvert.SerializeObject(result));
}
catch (Exception ex)
{
var result = new
{
Result = "Error",
Mensaje = ex.Message
};
context.Response.Write(JsonConvert.SerializeObject(result));
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Expires = -1;
try
{
string hora = DateTime.Now.ToString("yyyyMMddhhmmss");
string tempPath = "";
string savepath = "";
int cbo_origen = 0;
int cbo_plantilla = 0;
int.TryParse(context.Request["cbo_origen"], out cbo_origen);
int.TryParse(context.Request["cbo_plantilla"], out cbo_plantilla);
string txt_prompt = context.Request["txt_prompt"];
string txt_asunto = context.Request["txt_asunto"];
string id_grupo_correo = context.Request.Form.Get("list");
string usuarioSession = context.Request.Form.Get("txt_UserSession");
HttpFileCollection uploadFiles = context.Request.Files;
List <string> listrutas = new List <string>();
List <LogPromoDet> lstLogPromoDet = new List <LogPromoDet>();
ClientResponse responseruta;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responseruta = dbParametrosMaestro.getObjParametroMaestro("RUTA_ADJUNTO_ENVIO_CORREOS");
}
ParametrosMaestros rutaadjunto = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responseruta.DataJson);
for (int i = 0; i < uploadFiles.Count; i++)
{
HttpPostedFile postedFile = uploadFiles[i];
//tempPath = System.Configuration.ConfigurationManager.AppSettings["FolderPath"] + rutaadjunto.valor + hora;
tempPath = rutaadjunto.valor + hora;
savepath = tempPath;
if (!Directory.Exists(savepath))
{
Directory.CreateDirectory(savepath);
}
string filename = postedFile.FileName;
string files = savepath + @"\" + filename;
postedFile.SaveAs(files);
listrutas.Add(files);
}
ClientResponse response;
string usuario = cUtil.ObtenerValorParametro("CORREOPROMO", "USER");
string clave = cUtil.ObtenerValorParametro("CORREOPROMO", "CLAVE");
string smtp = cUtil.ObtenerValorParametro("CORREOPROMO", "SMTP");
int puerto = Convert.ToInt32(cUtil.ObtenerValorParametro("CORREOPROMO", "PUERTO"));
ClientResponse responsedesafiliacion;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responsedesafiliacion = dbParametrosMaestro.getObjParametroMaestro("DESAFILACIONPROMOCIONES");
}
ParametrosMaestros desafiliacion = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responsedesafiliacion.DataJson);
ClientResponse responseplantilla;
using (PlantillaDAO dbPlanilla = new PlantillaDAO())
{
responseplantilla = dbPlanilla.getPlantillaXId(cbo_plantilla);
}
Plantilla objetoplantilla = Newtonsoft.Json.JsonConvert.DeserializeObject <Plantilla>(responseplantilla.DataJson);
string body = string.Empty;
using (StreamReader reader = new StreamReader(objetoplantilla.ruta_plantilla_html))
{
body = reader.ReadToEnd();
}
body = "<div style='padding: 0px 2px; '>" + body + "</div>";
using (GestionCorreoDAO dbGestionCorreoDAO = new GestionCorreoDAO())
{
response = dbGestionCorreoDAO.getLstGestionCorreoXGrupo(id_grupo_correo);
}
List <GestionCorreo> liscorreos = Newtonsoft.Json.JsonConvert.DeserializeObject <List <GestionCorreo> >(response.DataJson);
string bodyaux = string.Empty;
LogPromo oLogPromo = new LogPromo();
oLogPromo.remitente = usuarioSession;
oLogPromo.asunto = txt_asunto;
oLogPromo.PlantillaID = objetoplantilla.id;
foreach (GestionCorreo item in liscorreos)
{
bodyaux = body;
if (objetoplantilla.fl_parrafo == 1)
{
bodyaux = bodyaux.Replace("{parrafo}", txt_prompt);
}
bodyaux = bodyaux.Replace("{NombreUsuario}", item.Nombre1 + " " + item.Nombre2);
bodyaux = bodyaux.Replace("{Codigo}", item.codigo.Trim());
bodyaux = bodyaux.Replace("{linkdardebaja}", desafiliacion.valor + "?tokens=" + item.Tokens);
bodyaux = HttpUtility.HtmlDecode(bodyaux);
cUtil.EnvioMailSegundo(txt_asunto, item.Email, bodyaux, listrutas, usuario, clave, smtp, puerto);
LogPromoDet oLogPromoDet = new LogPromoDet();
oLogPromoDet.id_grupo_correo = item.grupocorreo.id;// Convert.ToInt32(id_grupo_correo);
oLogPromoDet.destinatario = item.Email.ToString();
lstLogPromoDet.Add(oLogPromoDet);
}
oLogPromo.LogPromoDet = lstLogPromoDet;
ClientResponse responseLogPromo;
using (LogPromoDAO dbLogPromo = new LogPromoDAO())
{
responseLogPromo = dbLogPromo.InsertLogPromo(oLogPromo);
}
//Directory.Delete(savepath, true);
//plantilla.list_plantilla_detalle = list_plantilla_detalle;
//ClientResponse response;
//using (PlantillaDAO dbPlanilla = new PlantillaDAO())
//{
// response = dbPlanilla.InsertPantilla(plantilla);
//}
//if ((System.IO.File.Exists(files)))
//{
// System.IO.File.Delete(files);
//}
var result = new
{
Result = "Ok",
Mensaje = "Se envio correo"
};
context.Response.Write(JsonConvert.SerializeObject(result));
}
catch (Exception ex)
{
var result = new
{
Result = "Error",
Mensaje = ex.Message
};
context.Response.Write(JsonConvert.SerializeObject(result));
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
context.Response.Expires = -1;
try
{
string hora = DateTime.Now.ToString("yyyyMMddhhmmss");
ClientResponse responseruta;
using (ParametrosMaestrosDAO dbParametrosMaestro = new ParametrosMaestrosDAO())
{
responseruta = dbParametrosMaestro.getObjParametroMaestro("RUTA_CARGAEXCELCORREOS");
}
ParametrosMaestros rutaexcel = Newtonsoft.Json.JsonConvert.DeserializeObject <ParametrosMaestros>(responseruta.DataJson);
HttpPostedFile postedFile = context.Request.Files["Filedata"];
listgestioncorreo = new List <GestionCorreo>();
string savepath = "";
string tempPath = "";
tempPath = rutaexcel.valor + hora;
savepath = tempPath;
string filename = postedFile.FileName;
if (!Directory.Exists(savepath))
{
Directory.CreateDirectory(savepath);
}
string files = savepath + "/" + filename;
postedFile.SaveAs(files);
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(files, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
rw = range.Rows.Count;
cl = 6;// range.Columns.Count; Leemos solo 6 columnas de cada fila
ClientResponse response;
using (GrupoCorreoDAO dbGrupoCorreo = new GrupoCorreoDAO())
{
response = dbGrupoCorreo.getGrupoCorreoCombo(2);
}
List <GrupoCorreo> list = JsonConvert.DeserializeObject <List <GrupoCorreo> >(response.DataJson);
for (rCnt = 1; rCnt <= rw; rCnt++)
{
if (rCnt > 1)
{
entidad = new GestionCorreo();
for (cCnt = 1; cCnt <= cl; cCnt++)
{
if (cCnt == 1)
{
GrupoCorreo grupocorreo = new GrupoCorreo();
grupocorreo.descripcion = str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
entidad.grupocorreo = grupocorreo;
//Validamos si el grupo de correo esta registrado en la base de datos
grupocorreo.origen = 2;//Carga de Correo externo
grupocorreo.estado = 1;
GrupoCorreo objeto = list.Where(i => i.descripcion.ToUpper() == grupocorreo.descripcion.ToUpper()).FirstOrDefault();
if (objeto != null)
{
grupocorreo.id = objeto.id;
}
}
if (cCnt == 2)
{
entidad.Nombre1 = str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
}
if (cCnt == 3)
{
entidad.Nombre2 = str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
}
if (cCnt == 4)
{
entidad.ApePaterno = str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
}
if (cCnt == 5)
{
entidad.ApeMaterno = str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
}
if (cCnt == 6)
{
entidad.Email = str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
}
}
entidad.id_estado = 1;
listgestioncorreo.Add(entidad);
}
}
xlWorkBook.Close(true, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
if ((System.IO.File.Exists(files)))
{
System.IO.File.Delete(files);
}
if (listgestioncorreo.Count() > 0)
{
ClientResponse response1;
using (GestionCorreoDAO dbGestionCorreo = new GestionCorreoDAO())
{
response1 = dbGestionCorreo.InsertGestionCorreoAutomatico(listgestioncorreo);
}
}
var wapper = new
{
Result = "Ok",
Mensaje = "Se cargaron correctaente la trama."
};
context.Response.Write(JsonConvert.SerializeObject(wapper));
}
catch (Exception ex)
{
var wapper = new
{
Result = "Error",
Mensaje = ex.Message
};
context.Response.Write(JsonConvert.SerializeObject(wapper));
}
}