/// <summary>
/// Validar la conexion
/// </summary>
private bool validacionBD()
{
bool succes = true;
try
{
this.addText("Comprobando la conectividad con la base de datos\n");
Consultas consulta = new Consultas();
consulta.getNMTGroup();
}
catch (Exception ex)
{
//Nombre de la BD
string vg_BDConexion = ConfigurationManager.AppSettings["NombreBD"];
if (ex.Message.Contains("error: 26"))
{
this.addText("Error relacionado con la red o específico de la instancia mientras se establecía una conexión con el servidor SQL Server. No se encontró el servidor o éste no estaba accesible. Compruebe que el nombre de la instancia es correcto y que SQL Server está configurado para admitir conexiones remotas.\n");
MessageBox.Show("Error relacionado con la red o específico de la instancia mientras se establecía una conexión con el servidor SQL Server. No se encontró el servidor o éste no estaba accesible. Compruebe que el nombre de la instancia es correcto y que SQL Server está configurado para admitir conexiones remotas - " + ex.Message);
Debug.Write(ex.Message);
succes = false;
}
else if (ex.Message.Contains(vg_BDConexion))
{
try
{
this.addText("No se detectó la base de datos, procediendo a la creación.\n");
ConexionBD cb = new ConexionBD();
FileInfo file = new FileInfo("Script\\Script.sql");
string script = file.OpenText().ReadToEnd();
cb.ejecutarScript("CREATE DATABASE " + vg_BDConexion + "; ");
cb.ejecutarScript(script.Replace("[NMTBASE]", vg_BDConexion));
System.Threading.Thread.Sleep(20000);
file = new FileInfo("Script\\ScriptSP.sql");
script = file.OpenText().ReadToEnd();
string[] creates = script.Split('|');
foreach (string sql in creates)
{
try
{
cb.ejecutarScriptBase(sql);
}
catch (Exception e)
{
MessageBox.Show("Error tratando de crear el stored procedure " + sql + " - " + e.Message);
Debug.Write(e.Message);
}
}
this.addText("Creación de base de datos completa.\n");
string delay = System.Configuration.ConfigurationManager.AppSettings["SegsTiempoComprobacionConexion"];
try
{
int delayNew = Convert.ToInt32(delay);
delayNew = delayNew + 5;
System.Threading.Thread.Sleep(delayNew * 1000);
}
catch (Exception)
{
}
this.reinitPrincipal();
if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.PrincipalWindow")
{
((PrincipalWindow)parentWin).Dispatcher.Invoke(
System.Windows.Threading.DispatcherPriority.Normal,
new Action(
delegate()
{
territorio = ((PrincipalWindow)parentWin).tbTerrirotio.Text;
}
));
}
else if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.IndicadorWindow")
{
((IndicadorWindow)parentWin).Dispatcher.Invoke(
System.Windows.Threading.DispatcherPriority.Normal,
new Action(
delegate()
{
territorio = ((IndicadorWindow)parentWin).tbTerrirotio.Text;
}
));
}
}
catch (FileNotFoundException ex1)
{
MessageBox.Show("No se encontró el script " + ex1.Message);
Debug.Write(ex1.Message);
succes = false;
}
catch (Exception ex2)
{
MessageBox.Show("Error relacionado con la red o específico de la instancia mientras se establecía una conexión con el servidor SQL Server. No se encontró el servidor o éste no estaba accesible. Compruebe que el nombre de la instancia es correcto y que SQL Server está configurado para admitir conexiones remotas - " + ex2.Message);
Debug.Write(ex2.Message);
succes = false;
}
}
}
return succes;
}
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_DistibucionPDVGrupo_Flt_Segmentacion().Tables[0];
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro1, "Segmentacion", "Segmentacion", "Segmentacion", true);
filtros.Add(filterIndustryLevel);
return filtros;
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_EfectividadMDZ_Flt_ProductSubfamilyCode().Tables[0];
Filtro filterProductSubfamilyCode = Herramientas.Instance.obtenerFiltro(filtro1, "Product Subfamily Code", "ProductSubfamilyCode", "ProductSubfamilyCode", true);
DataTable filtro2 = consulta.SelectKPI_EfectividadMDZ_Flt_ProductSubfamilyDescription().Tables[0];
Filtro filterProductSubfamilyDescription = Herramientas.Instance.obtenerFiltro(filtro2, "Marca", "ProductSubfamilyDescription", "ProductSubfamilyDescription", true);//Subfamilia
DataTable filtro3 = consulta.SelectKPI_EfectividadMDZ_Flt_IndustryLevel().Tables[0];
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro3, "Potencial", "IndustryLevel", "IndustryLevel", false);
filtros.Add(filterProductSubfamilyCode);
filtros.Add(filterProductSubfamilyDescription);
filtros.Add(filterIndustryLevel);
return filtros;
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_Clientes_ITO_UX_Flt_IndustryLevel().Tables[0];
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro1, "Potencial", "IndustryLevel", "IndustryLevel", false);
filtros.Add(filterIndustryLevel);
return filtros;
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_SwitchSellingSKU_Flt_OriginalBrandDescription().Tables[0];
Filtro filterOriginalBrandDescription = Herramientas.Instance.obtenerFiltro(filtro1, "Marca Original", "OriginalBrandDescription", "OriginalBrandDescription", false);
DataTable filtro2 = consulta.SelectKPI_SwitchSellingSKU_Flt_TimeId().Tables[0];
Filtro filterTimeId = Herramientas.Instance.obtenerFiltro(filtro2, "Ciclo", "TimeId", "TimeId", false);
filtros.Add(filterOriginalBrandDescription);
filtros.Add(filterTimeId);
return filtros;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_Agotamiento_Flt_IndustryLevel().Tables[0];
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro1, "Potencial", "IndustryLevel", "IndustryLevel",false);
DataTable filtro2 = consulta.SelectKPI_Agotamiento_Flt_BrandDescription().Tables[0];
Filtro filterSBrandDescription = Herramientas.Instance.obtenerFiltro(filtro2, "Subfamilia", "BrandDescription", "BrandDescription", true);//Subfamilia
DataTable filtro3 = consulta.SelectKPI_Agotamiento_Flt_BrandCode().Tables[0];
Filtro filterSBrandCode = Herramientas.Instance.obtenerFiltro(filtro3, "Brand Code", "BrandCode", "BrandCode", true);
filtros.Add(filterIndustryLevel);
filtros.Add(filterSBrandCode);
filtros.Add(filterSBrandDescription);
return filtros;
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_Volumen_Flt_Chain().Tables[0];
Filtro filterChain = Herramientas.Instance.obtenerFiltro(filtro1, "Cadena", "Chain", "Chain", false);
filtros.Add(filterChain);
return filtros;
}
/// <summary>
/// Homologa el nombre del catalogo entregado por su correspondiente tabla
/// </summary>
/// <param name="catalogo"></param>
/// <returns></returns>
private string homologueCatalogo(string catalogo)
{
Consultas c = new Consultas();
string retorno = "";
DataTable catCurrent = new DataTable();
catCurrent = c.SelectHomologacion_Catalogo(catalogo).Tables[0];
foreach (DataRow g in catCurrent.Rows)
{
retorno = (string)g["strNombreTabla"];
}
return retorno;
}
/// <summary>
/// Lanzamiento del update
/// </summary>
private void lanzarUpdate()
{
try
{
actualProces = 0;
actualProces++;
// Create a new instance of our ProgressBar Delegate that points
// to the ProgressBar's SetValue method.
updatePbDelegate =
new UpdateProgressBarDelegate(pBar.SetValue);
Dispatcher.Invoke(updatePbDelegate,
System.Windows.Threading.DispatcherPriority.Background, new object[] { ProgressBar.ValueProperty, Convert.ToDouble(actualProces) });
if (this.validacionBD() )
if( this.checkSMS() != 0)
{
this.addText("Comprobando la conectividad de la red\n");
if (NetworkInterface.GetIsNetworkAvailable())
{
this.addText("Conectividad de la red comprobada\n");
int numIntentos = 1;
int numIntentosMax = 1;
int tiempoEspera = 1 ;
//Obteniendo num maximo de intentos
try
{
numIntentosMax = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["NumReintentos"]);
}
catch(Exception ex)
{
this.addText("Formato de número de intentos invalido - " + ex.Message );
numIntentos = 1;
}
//Obteniendo tiempo de espera entre intentos
try
{
tiempoEspera = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SegsTiempoReintento"]);
}
catch(Exception ex2)
{
this.addText("Formato de tiempo de espera invalido - " + ex2.Message);
numIntentos = 1;
}
//Intentos de conexion
while (numIntentos <= numIntentosMax)
{
this.addText("Comprobando la conectividad de la carpeta de datos - intento "+numIntentos+" / "+numIntentosMax+"\n");
if (this.getStatusConn())
{
this.addText("Conectividad de la carpeta de datos comprobada\n");
this.addText("Iniciando sincronización de KPIs\n");
//--------- INI BZG - Elimina Carpeta temporal -------------
EliminaArchivosTemporales();
//--------- FIN BZG - Elimina Carpeta temporal -------------
//Sincronizando
//-------------- INI BZG ----------------
territorio = this.ValidaCambioTerritorio();
//-------------- FIN BZG ----------------
this.sincroniceKPIs();
this.sincroniceCatalogos();
DateTime stopTime = DateTime.Now;
double totalSeconds = (double)stopTime.Ticks / TimeSpan.TicksPerSecond;
string dateFormat = stopTime.ToString("yyyyMMddHHmmssffff");
long fecha_sin_int = Convert.ToInt64(dateFormat);
int duracion = 0;
Consultas c = new Consultas();
c.InsertVitacora(usuario, WindowsIdentity.GetCurrent().Name, DateTime.Now, versionCliente, duracion, null, territorio, fecha_sin_int, true);
if (this.getText().ToLower().Contains("error"))
{
MessageBox.Show("El proceso de sincronización terminó con al menos un error. Favor de contactar a Help Desk.");
//MessageBox.Show("El proceso de actualización terminó, pero se registró un problema, verifique el detalle de la ejecución.");
}
else
MessageBox.Show("El proceso de sincronización de KPIs terminó exitosamente.");
//MessageBox.Show("El proceso de actualización terminó, por favor verifique el log para constatar la ejecución del proceso");
string delay = System.Configuration.ConfigurationManager.AppSettings["SegsTiempoComprobacionConexion"];
try
{
int delayNew = Convert.ToInt32(delay);
delayNew = delayNew + 5;
System.Threading.Thread.Sleep(delayNew * 1000);
}
catch (Exception)
{
}
this.reinitPrincipal();
break;
}
else
{
this.addText("No se encontró conexión con la carpeta de datos de aplicación " + System.Configuration.ConfigurationManager.AppSettings["CarpetaCompartida"] + "\n");
numIntentos++;
System.Threading.Thread.Sleep(tiempoEspera);
}
}
//Comprobando maximo de intentos
if (numIntentos > numIntentosMax)
{
this.addText("Los intentos de conectividad superaron el máximo de intentos "+numIntentos+"/"+numIntentosMax+"\n");
}
}
else
{
MessageBox.Show("No se encontró conexión de red disponible");
}
}
actualProces++;
Dispatcher.Invoke(updatePbDelegate,
System.Windows.Threading.DispatcherPriority.Background, new object[] { ProgressBar.ValueProperty, Convert.ToDouble(actualProces) });
this.btnEjecutarEnabled(true);
}
catch (Exception ex3)
{
this.btnEjecutarEnabled(true);
this.addText("Error en la ejecución de la actualización : " + ex3.Message);
MessageBox.Show("Ocurrió un error en la ejecución de la actualización - " + ex3.Message);
}
}
/// <summary>
/// Autor : Belizario Zapien Garcia
/// Funcion que elimina los archivos de la carpeta temporal
/// </summary>
private void EliminaArchivosTemporales()
{
Consultas c = new Consultas();
DataTable kpis = new DataTable();
kpis = c.SelectNMT_KPI_Temporal().Tables[0];
//if (kpis.Rows.Count == 0)
// this.addText("No se detectaron archivos a borrar \n");
string appData = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
string tmpFile = appData + System.Configuration.ConfigurationManager.AppSettings["CarpetaTemporal"];
foreach (DataRow g in kpis.Rows)
{
try
{
string kpi = (string)g["FILENAME_KPI"];
this.eliminarArchivos(tmpFile, "*" + kpi + "*");
}
catch { }
}
}
/// <summary>
/// Homologa el nombre del archivo entregado por su correspondiente tabla
/// </summary>
/// <param name="nombreTabla"></param>
/// <returns></returns>
private string homologue(string nombreTabla)
{
Consultas c = new Consultas();
string retorno = "";
DataTable kpisCurrent = new DataTable();
kpisCurrent = c.SelectHomologacion_FILES(nombreTabla).Tables[0];
foreach (DataRow g in kpisCurrent.Rows)
{
retorno = (string)g["strNombreTabla"];
}
return retorno;
}
/// <summary>
/// Comprueba la conexión a la basa de datos SMS
/// </summary>
private int checkSMS()
{
string nombreBD = System.Configuration.ConfigurationManager.AppSettings["NombreBDiSMS"];
int estado;
DataTable valores;
try
{
estado = 0;
valores = new DataTable();
Consultas consulta = new Consultas();
valores = consulta.getAutenticacion(nombreBD).Tables[0];
consulta = null;
if (valores.Rows.Count == 0)
{
return estado;
}
else
{
return estado = 1;
}
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
}
/// <summary>
/// Carga la informacion en la tabla NMT_CURRENT_FILES
/// </summary>
/// <param name="file"></param>
/// <param name="kpi"></param>
/// <param name="territorio"></param>
/// <param name="dateLong"></param>
private void cargaCurrentFiles(string file,string kpi, string territorio, long dateLong)
{
FileInfo finf = new FileInfo(file);
string appData = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
string tmpFile = appData + System.Configuration.ConfigurationManager.AppSettings["CarpetaTemporal"] + "\\"+finf.Name;
if (this.copiarArchivo(file, tmpFile))
{
if (comprobacionDescargaArchivo(file,tmpFile))
{
Consultas c = new Consultas();
c.InsertNMT_CURRENT_FILES(tmpFile, kpi, territorio, dateLong);
c.DeleteNMT_TMP_FILES(file);
}
}
}
/// <summary>
/// Metodo para asignacion de estudios
/// </summary>
/// <param name="id"></param>
public void changeStyle(string id)
{
Consultas cons = new Consultas();
cons.UpdateSkins(Convert.ToInt32(id));
if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.PrincipalWindow")
{
((PrincipalWindow)parentWin).changeStyle(id);
}
else if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.IndicadorWindow")
{
((IndicadorWindow)parentWin).changeStyle(id);
}
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_MDZ_GENERAL_DROP_SIZE_Flt_IndustryLevel().Tables[0];
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro1, "Potencial", "IndustryLevel", "IndustryLevel", true);
filtros.Add(filterIndustryLevel);
return filtros;
}
/// <summary>
/// Obtiene la version de la actializacion
/// </summary>
private void obtenerVersiones()
{
Consultas consulta = new Consultas();
territorio = "";
DataTable version;
if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.PrincipalWindow")
{
territorio = ((PrincipalWindow)parentWin).tbTerrirotio.Text;
usuario = ((PrincipalWindow)parentWin).tbUsuario.Text;
versionCliente = ((PrincipalWindow)parentWin).tbVersion.Text;
}
else if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.IndicadorWindow")
{
territorio = ((IndicadorWindow)parentWin).tbTerrirotio.Text;
usuario = ((IndicadorWindow)parentWin).tbUsuario.Text;
versionCliente = ((IndicadorWindow)parentWin).tbVersion.Text;
}
lblFecha.Content = "N/A";
lblVersion.Content = "N/A";
DataSet vDS =consulta.getVersionBitacora(territorio);
if (vDS!=null)
{
version = vDS.Tables[0];
foreach (DataRow g in version.Rows)
{
string ver = (String)g["VERSION_CLIENTE"];
DateTime fecha = (DateTime)g["FECHA_SINCRONIZACION"];
lblFecha.Content = fecha.ToString("dd/MM/yyyy");
lblVersion.Content = ver;
}
}
//Configurando informacion de aplicacion
try
{
string strVersion = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();
string fechaAp = File.GetCreationTime(System.Reflection.Assembly.GetExecutingAssembly().Location).ToString("dd/MM/yyyy");
lblFechaAplicacion.Content = fechaAp;
lblVersionAplicacion.Content = strVersion;
}
catch (Exception)
{
lblFechaAplicacion.Content = "N/A";
lblVersionAplicacion.Content = "N/A";
}
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_Share_Segment_Flt_IndustryLevel().Tables[0];
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro1, "Potencial", "IndustryLevel", "IndustryLevel", false);
//DataTable filtro2 = consulta.SelectKPI_Share_Segment_Flt_SegmentPrice().Tables[0];
//Filtro filterSegmentPrice = Herramientas.Instance.obtenerFiltro(filtro2, "Segment Price", "SegmentPrice", "SegmentPrice", false);
filtros.Add(filterIndustryLevel);
//filtros.Add(filterSegmentPrice);
return filtros;
}
/// <summary>
/// Proceso de sincronizacion catalogos
/// </summary>
private void sincroniceCatalogos()
{
bool local = true;
this.addText("***************** INICIANDO PROCESO DE SINCRONIZACIÓN DE CATÁLOGOS *****************\n");
string compartida = System.Configuration.ConfigurationManager.AppSettings["CarpetaCompartidaCatalogos"];
this.addText("Obteniendo archivos de la carpeta remota para los catálogos \n");
string[] archivosTerritorio = this.obtenerArchivos(compartida, "*.zip");
if(archivosTerritorio.Length==0)
this.addText("No se detectaron archivos en la carpeta remota para los catálogos \n");
setMaxBar(archivosTerritorio.Length);
Consultas c = new Consultas();
foreach (string file in archivosTerritorio)
{
try
{
FileInfo fkpi = new FileInfo(file);
string catalogo = fkpi.Name.Substring(0, (fkpi.Name.IndexOf("_")));
string date = file.Substring(file.LastIndexOf("_") + 1, (file.Length - 5 - file.LastIndexOf("_")));
long dateLong = -1;
try
{
dateLong = Convert.ToInt64(date);
}
catch{ }
this.addText("Insertando registro en NMT_TMP_CAT_FILES para el catálogo " + catalogo + "\n");
c.InsertNMT_TMP_CAT_FILES(file, catalogo, dateLong);
}
catch (Exception ex)
{
this.addText("Error insertando registro en NMT_TMP_CAT_FILES para el catálogo en el archivo" + file + "\n" + ex.Message);
}
actualProces++;
Dispatcher.Invoke(updatePbDelegate,
System.Windows.Threading.DispatcherPriority.Background, new object[] { ProgressBar.ValueProperty, Convert.ToDouble(actualProces) });
}
this.addText("Seleccionando archivos a cargar\n");
c.CompareNMT_TMP_CAT_FILES();
DataTable cata = new DataTable();
cata = c.SelectNMT_TMP_CAT_FILES().Tables[0];
if(cata.Rows.Count==0)
this.addText("No se detectaron archivos a cargar \n");
foreach (DataRow g in cata.Rows)
{
string file = (string)g["FULL_FILENAME"];
string catalog = (string)g["CATALOG_NAME"];
long dateLong = (long)g["FILENAME_DATE"];
this.cargaCurrentCatalogos(file, catalog, dateLong);
}
this.addText("Seleccionando archivos con bit para carga en CURRENT CAT\n");
DataTable catCurrent = new DataTable();
catCurrent = c.SelectNMT_CURRENT_CAT_FILES().Tables[0];
setMaxBar(catCurrent.Rows.Count);
if (catCurrent.Rows.Count==0)
this.addText("No se detectaron archivos a cargar con bit en 1\n");
foreach (DataRow g in catCurrent.Rows)
{
string file = (string)g["FULL_FILENAME"];
string catalog = (string)g["CATALOG_NAME"];
long dateLong = (long)g["FILENAME_DATE"];
try
{
this.addText("Realizando la extracción del .zip\n");
List<string> files = this.descomprimir(file);
this.addText("Homologando " + catalog + " \n");
try
{
string tabla = this.homologueCatalogo(catalog);
DataTable datosTabla = new DataTable();
datosTabla = c.CreateTableCatalogoTemp(tabla,local).Tables[0];
this.addText("Iniciando lectura de archivo\n");
StreamReader reader = null;
bool exitoLectura = true;
try
{
reader = new StreamReader(files[0], Encoding.Default);
string line = string.Empty;
while ((line = reader.ReadLine()) != null)
{
try
{
string[] valores = line.Split('|');
int index = 0;
string columns = "";
string inserts = "";
foreach (DataRow gdata in datosTabla.Rows)
{
string column = (string)gdata["COLUMN_NAME"];
string tipo = (string)gdata["TYPE_NAME"];
columns += column + ",";
string val = "";
if (tipo.ToLower().Contains("char")) val += "{\"}";
if (index < valores.Length) val += valores[index];
if (tipo.ToLower().Contains("char")) val += "{\"}";
inserts += val + ",";
index++;
}
try
{
c.ExecuteSQLNMT_CAT_TMP_FILES(tabla, columns.Remove(columns.Length - 1, 1), inserts.Remove(inserts.Length - 1, 1), local);
}
catch (Exception ex2)
{
exitoLectura = exitoLectura && false;
this.addText("Error insertando los datos en la tabla temporal\n" + ex2.Message);
}
}
catch (Exception ex3)
{
exitoLectura = exitoLectura && false;
this.addText("Error mapeando columnas de tabla con columnas del archivo\n" + ex3.Message);
}
}
reader.Close();
}
catch (Exception ex4)
{
this.addText("Error en la lectura del archivo\n" + ex4.Message);
if (reader != null)
reader.Close();
exitoLectura = exitoLectura && false;
}
if (exitoLectura)
{
this.addText("Tabla temporal " + tabla + "_TMP creada y cargada\n");
this.addText("Truncando la tabla " + tabla + "\n");
try
{
//AQUI TRUNCA LA TABLA DE KPI NORMAL
c.Truncate_NMT_Table(tabla, local);
//c.Drop_NMT_Table(tabla, local);
this.addText("Tabla " + tabla + " truncada\n");
this.addText("Cargando los datos desde la tabla temporal " + tabla + "_TMP\n");
try
{
c.Merge_NMT_Table(tabla + "_TMP", tabla, local);
this.addText("Carga de datos desde la tabla temporal " + tabla + "_TMP exitosa\n");
// ----- INI - BZG - 20120227 -----------
c.SetLoadedNMT_CURRENT_CAT_FILES(catalog);
// ----- FIN - BZG - 20120227 -----------
this.addText("Eliminando tabla temporal " + tabla + "_TMP \n");
try
{
c.Drop_NMT_Table(tabla + "_TMP", local);
}
catch (Exception ex5)
{
this.addText("Error eliminando la tabla temporal " + tabla + "_TMP\n"+ ex5.Message);
}
}
catch (Exception ex6)
{
this.addText("Error al intentar cargar los datos desde la tabla temporal " + tabla + "_TMP\n"+ex6.Message);
}
}
catch (Exception ex7)
{
this.addText("Error al intentar truncar la tabla\n" + ex7.Message);
}
}
}
catch (Exception ex8)
{
this.addText("Ocurrió un error tratando de crear la tabla temporal\n" + ex8.Message);
}
}
catch (Exception ex9)
{
this.addText("Ocurrió un error en la extracción del .zip\n" + ex9.Message);
}
this.addText("\nEl proceso ha finalizado.\n");
actualProces++;
Dispatcher.Invoke(updatePbDelegate,
System.Windows.Threading.DispatcherPriority.Background, new object[] { ProgressBar.ValueProperty, Convert.ToDouble(actualProces) });
}
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_Share_SKU_Flt_ProductSubfamilyCode().Tables[0];
Filtro filterProductSubfamilyCode = Herramientas.Instance.obtenerFiltro(filtro1, "Product Subfamily Code", "ProductSubfamilyCode", "ProductSubfamilyCode", true);
DataTable filtro2 = consulta.SelectKPI_Share_SKU_Flt_ProductSubfamilyDescription().Tables[0];
Filtro filterProductSubfamilyDescription = Herramientas.Instance.obtenerFiltro(filtro2, "Marca", "ProductSubfamilyDescription", "ProductSubfamilyDescription", true);//Subfamilia
DataTable filtro3 = consulta.SelectKPI_Share_SKU_Flt_SegmentPrice().Tables[0];
Filtro filterSegmentPrice = Herramientas.Instance.obtenerFiltro(filtro3, "Segmento de Precio", "SegmentPrice", "SegmentPrice", false);
filtros.Add(filterProductSubfamilyCode);
filtros.Add(filterProductSubfamilyDescription);
filtros.Add(filterSegmentPrice);
return filtros;
}
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_Facturado_Flt_Cliente().Tables[0];
if(filtro1.Rows.Count>0)
filtro1.Rows.Add("TODOS");
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro1, "Cliente", "Cliente", "Cliente", false);
filtros.Add(filterIndustryLevel);
return filtros;
}
/// <summary>
/// Metodo de graficación.
/// </summary>
/// <returns></returns>
public Chart Draw(params string[] filtros)
{
// Create a new instance of Chart
Chart chart = new Chart();
Dictionary<String, decimal?> Pdvs = new Dictionary<string, decimal?>();
chart.AnimationEnabled = true;
// Create a new instance of Title
Title title = new Title();
// Set title property
title.Text = "Volumen";
// Add title to Titles collection
chart.Titles.Add(title);
// Create a new instance of DataSeries
DataSeries dataSeries = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
// Create a new instance of DataSeries secundary
DataSeries dataSeriesSecundary = new DataSeries();
// Set DataSeries property
dataSeriesSecundary.AxisYType = AxisTypes.Secondary;
dataSeriesSecundary.Name = "PDVs DE CADENA";
dataSeriesSecundary.Padding = new Thickness(30);
dataSeriesSecundary.RenderAs = RenderAs.Line;
dataSeriesSecundary.MarkerType = Visifire.Commons.MarkerTypes.Cross;
dataSeriesSecundary.SelectionEnabled = true;
dataSeriesSecundary.LineThickness = 3;
//dataSeriesSecundary.ZIndex = 4;
#region configuracion eje X
// Creating AxisX
Axis axisX = new Axis();
// Date time standard format
axisX.ValueFormatString = "000000";
axisX.AxisOffset = 0.02;
//axisX.IntervalType = IntervalTypes.Months;
// To avoid auto skip
//
chart.AxesX.Add(axisX);
#endregion
// Create a DataPoint
DataPoint dataPoint;
#region consulta
DataTable kpis;
//Adicionando 2do eje
//Orientation axisYOrientation = Orientation.Vertical;
//Axis axisY = new Axis();
//axisY._isAutoGenerated = true;
//axisY.Chart = chart;
//axisY.AxisOrientation = axisYOrientation;
//axisY.AxisType = AxisTypes.Secondary;
//axisY.PlotDetails = this;
////axisY.AxisRepresentation = AxisRepresentations.AxisY;
//chart.InternalAxesY.Add(axisY);
//chart.AxesY.Add(axisY);
string ser = "-1111";
try
{
kpis = new DataTable();
Consultas consulta = new Consultas();
kpis = consulta.SelectKPI_Volumen(filtros).Tables[0];
consulta = null;
string serAnt = "-5555";
foreach (DataRow g in kpis.Rows)
{
Decimal? y = g["QtySellOut"]!=DBNull.Value?(Decimal?)g["QtySellOut"]:null;
Decimal? y2 = g["QtyCPW"]!=DBNull.Value?(Decimal?)g["QtyCPW"]:null;
long x = (long)g["TimeId"];
ser = (string)g["ManufacturerCode"];
if (serAnt != ser)
{
if (serAnt != "-5555" )
{
chart.Series.Add(dataSeries);
}
// Create a new instance of DataSeries
dataSeries = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Circle;
dataSeries.SelectionEnabled = true;
dataSeries.LineThickness = 3;
dataSeries.Name = "CPW "+ser;
/*
// Create a new instance of DataSeries
//dataSeriesSecundary = new DataSeries();
// Set DataSeries property
dataSeriesSecundary.RenderAs = RenderAs.Line;
dataSeriesSecundary.Name = ser;
dataSeriesSecundary.MarkerType = Visifire.Commons.MarkerTypes.Circle;
dataSeriesSecundary.SelectionEnabled = true;
dataSeriesSecundary.LineThickness = 3;
*/
}
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
// Add dataPoint to DataPoints collection.
dataSeries.DataPoints.Add(dataPoint);
//Almacenando los maximos de QtyCPW
if (!Pdvs.ContainsKey(x.ToString()))
{
Pdvs.Add(x.ToString(), y2);
}
else
{
decimal? value = Pdvs[x.ToString()];
if (y2 > value)
{
Pdvs.Remove(x.ToString());
Pdvs.Add(x.ToString(),y2);
}
}
/*
if (ser.Contains("PDV"))
{
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
//dataPoint.XValue = x;
dataPoint.YValue = System.Convert.ToDouble(y);
// Add dataPoint to DataPoints collection.
dataSeriesSecundary.DataPoints.Add(dataPoint);
}
*/
serAnt = ser;
}
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
#endregion
// Add dataSeries to Series collection.
chart.Series.Add(dataSeries);
//Llenando eje secundario
foreach (string cicloTMP in ciclos)
{
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
dataPoint.AxisXLabel = cicloTMP;
//dataPoint.XValue = x;
decimal? value = -111111111;
if (Pdvs.ContainsKey(cicloTMP))
{
value = Pdvs[cicloTMP];
dataPoint.YValue = System.Convert.ToDouble(value);
}
// Add dataPoint to DataPoints collection.
dataSeriesSecundary.DataPoints.Add(dataPoint);
}
chart.Series.Add(dataSeriesSecundary);
return chart;
}
public Chart Draw(string[] filtros)
{
Chart chart = new Chart();
if (filtros.Length > 1)
{
if (filtros[1] == "Detallado")
{
// Create a new instance of Chart
chart.AnimationEnabled = true;
// Create a new instance of Title
Title title = new Title();
// Set title property
title.Text = "FACTURADO";
// Add title to Titles collection
chart.Titles.Add(title);
// Create a new instance of DataSeries
DataSeries dataSeries = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Column;
dataSeries.SelectionEnabled = true;
dataSeries.LineThickness = 3;
// Create a new instance of DataSeries2
DataSeries dataSeries2 = new DataSeries();
// Set DataSeries property
dataSeries2.RenderAs = RenderAs.Column;
dataSeries2.SelectionEnabled = true;
dataSeries2.LineThickness = 3;
// Create a new instance of DataSeries3
DataSeries dataSeries3 = new DataSeries();
// Set DataSeries property
dataSeries3.RenderAs = RenderAs.Column;
dataSeries3.SelectionEnabled = true;
dataSeries3.LineThickness = 3;
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Column;
#region configuracion eje X
// Creating AxisX
Axis axisX = new Axis();
// Date time standard format
axisX.ValueFormatString = "000000";
// To avoid auto skip
chart.AxesX.Add(axisX);
#endregion
// Create a DataPoint
DataPoint dataPoint;
#region consulta
DataTable kpis;
try
{
kpis = new DataTable();
Consultas consulta = new Consultas();
kpis = consulta.SelectKPI_Facturado(filtros).Tables[0];
consulta = null;
Boolean band = false;
foreach (DataRow g in kpis.Rows)
{
Decimal? y = g["VolumenActual"] != DBNull.Value ? (Decimal?)g["VolumenActual"] : null;
Decimal? y2 = g["VolumenAnterior"] != DBNull.Value ? (Decimal?)g["VolumenAnterior"] : null;
Decimal? y3 = g["Variacion"] != DBNull.Value ? (Decimal?)g["Variacion"] : null;
int x = (int)g["Ciclo"];
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y2);
dataPoint.ToolTipText = (int.Parse(x.ToString().Substring(0, 4)) - 1).ToString()+" "+y2;
// Add dataPoint to DataPoints collection.
dataSeries.DataPoints.Add(dataPoint);
dataSeries.Name = "ANTERIOR";
// Set YValue for a DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
dataPoint.ToolTipText = (int.Parse(x.ToString().Substring(0, 4))).ToString() + " " + y;
// Add dataPoint to DataPoints collection.
dataSeries2.DataPoints.Add(dataPoint);
dataSeries2.Name = "ACTUAL";
// Set YValue for a DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
dataPoint.ToolTipText = x.ToString().Substring(0, 4);
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y3);
dataPoint.ToolTipText = (int.Parse(x.ToString().Substring(0, 4))).ToString() + " " + y3;
// Add dataPoint to DataPoints collection.
dataSeries3.DataPoints.Add(dataPoint);
dataSeries3.Name = "VARIACION";
band = true;
}
// Add dataSeries to Series collection.
if (band != false)
{
chart.Series.Add(dataSeries);
chart.Series.Add(dataSeries2);
chart.Series.Add(dataSeries3);
}
return chart;
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
#endregion
}
else
{
// Create a new instance of Chart
chart.AnimationEnabled = true;
// Create a new instance of Title
Title title = new Title();
// Set title property
title.Text = "FACTURADO";
// Add title to Titles collection
chart.Titles.Add(title);
// Create a new instance of DataSeries
DataSeries dataSeries = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Column;
dataSeries.SelectionEnabled = true;
dataSeries.LineThickness = 3;
DataSeries dataSeries2 = new DataSeries();
// Set DataSeries property
dataSeries2.RenderAs = RenderAs.Column;
dataSeries2.SelectionEnabled = true;
dataSeries2.LineThickness = 3;
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Column;
#region configuracion eje X
// Creating AxisX
Axis axisX = new Axis();
// Date time standard format
axisX.ValueFormatString = "000000";
// To avoid auto skip
chart.AxesX.Add(axisX);
#endregion
// Create a DataPoint
DataPoint dataPoint;
#region consulta
DataTable kpis;
try
{
kpis = new DataTable();
Consultas consulta = new Consultas();
kpis = consulta.SelectKPI_Facturado(filtros).Tables[0];
consulta = null;
Boolean band = false;
foreach (DataRow g in kpis.Rows)
{
Decimal? y = g["VolumenActual"] != DBNull.Value ? (Decimal?)g["VolumenActual"] : null;
Decimal? y2 = g["VolumenAnterior"] != DBNull.Value ? (Decimal?)g["VolumenAnterior"] : null;
int x = 0; //(int)g["Ciclo"];
string año = (string)g["ano"];
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y2);
dataPoint.ToolTipText = (int.Parse(año.Substring(0, 4))-1).ToString()+" "+y2;
// Add dataPoint to DataPoints collection.
dataSeries.DataPoints.Add(dataPoint);
dataSeries.Name = "ANTERIOR";
// Set YValue for a DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
dataPoint.ToolTipText = año+" "+y;
// Add dataPoint to DataPoints collection.
dataSeries2.DataPoints.Add(dataPoint);
dataSeries2.Name = "ACTUAL";
band = true;
}
// Add dataSeries to Series collection.
if (band != false)
{
chart.Series.Add(dataSeries);
chart.Series.Add(dataSeries2);
}
return chart;
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
#endregion
}
}
else
return chart;
}
/// <summary>
/// Metodo de graficación.
/// </summary>
/// <returns></returns>
public Chart Draw(params string[] filtros)
{
// Create a new instance of Chart
Chart chart = new Chart();
chart.AnimationEnabled = true;
// Create a new instance of Title
Title title = new Title();
// Set title property
title.Text = "Clientes ITO Vs Clientes UX";
// Add title to Titles collection
chart.Titles.Add(title);
// Create a new instance of DataSeries
DataSeries dataSeries = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Circle;
dataSeries.SelectionEnabled = true;
dataSeries.LineThickness = 3;
// Create a new instance of DataSeries2
DataSeries dataSeries2 = new DataSeries();
// Set DataSeries property
dataSeries2.RenderAs = RenderAs.Line;
dataSeries2.MarkerType = Visifire.Commons.MarkerTypes.Circle;
dataSeries2.SelectionEnabled = true;
dataSeries2.LineThickness = 3;
#region configuracion eje X
// Creating AxisX
Axis axisX = new Axis();
// Date time standard format
axisX.ValueFormatString = "000000";
// To avoid auto skip
chart.AxesX.Add(axisX);
#endregion
// Create a DataPoint
DataPoint dataPoint;
#region consulta
DataTable kpis;
try
{
kpis = new DataTable();
Consultas consulta = new Consultas();
kpis = consulta.SelectKPI_Clientes_ITO_UX(filtros).Tables[0];
consulta = null;
foreach (DataRow g in kpis.Rows)
{
long? y = g["NbrCustUX"]!=DBNull.Value?(long?)g["NbrCustUX"]:null;
long? y2 = g["NbrCustITO"]!=DBNull.Value?(long?)g["NbrCustITO"]:null;
long x = (long)g["TimeId"];
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
// Add dataPoint to DataPoints collection.
dataSeries.DataPoints.Add(dataPoint);
dataSeries.Name = "Universo";
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y2);
// Add dataPoint to DataPoints collection.
dataSeries2.DataPoints.Add(dataPoint);
dataSeries2.Name = "Itinerario";
}
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
#endregion
// Add dataSeries to Series collection.
chart.Series.Add(dataSeries);
chart.Series.Add(dataSeries2);
return chart;
}
public void generarExcelClientes(string path, string filtros)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Workbook xlWorkBook;
Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
int fila = 4;
int columna = 1;
try
{
Range chartRange;
xlWorkSheet.get_Range("a1", "k1").Merge(false);
string titulo;
if (filtros != "Todos")
titulo = filtros;
else
titulo = "";
chartRange = xlWorkSheet.get_Range("a1", "k1");
chartRange.FormulaR1C1 = "DETALLE SOBRE CLIENTES "+titulo;
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
chartRange.Font.Bold = true;
//Consulta a la BD
System.Data.DataTable kpis;
kpis = new System.Data.DataTable();
Consultas consulta = new Consultas();
kpis = consulta.Select_ClientesDetallado(filtros).Tables[0];
consulta = null;
if (kpis.Rows.Count > 0)
//Encabezado
foreach (DataColumn col in kpis.Rows[0].Table.Columns)
{
string valor = col.ColumnName;
xlWorkSheet.Cells[3, columna] = valor;
xlWorkSheet.Cells[3, columna].Style.Font.Name = "Calibri";
xlWorkSheet.Cells[3, columna].Style.Font.Bold = true;
xlWorkSheet.Cells[3, columna].BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, XlColorIndex.xlColorIndexAutomatic);
columna++;
}
chartRange = xlWorkSheet.get_Range("a3", "u3");
chartRange.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, XlColorIndex.xlColorIndexAutomatic);
chartRange.Interior.ColorIndex = 15;
//chartRange.AutoFit();
//Detalle
foreach (DataRow g in kpis.Rows)
{
columna = 1;
foreach (DataColumn col in g.Table.Columns)
{
string valor = "";
if (g[col.ColumnName] != null)
{
valor = g[col.ColumnName].ToString();
}
else valor = "";
xlWorkSheet.Cells[fila, columna] = valor;
xlWorkSheet.Cells[fila, columna].Style.Font.Name = "Calibri";
xlWorkSheet.Cells[fila, columna].BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, XlColorIndex.xlColorIndexAutomatic);
xlWorkSheet.Cells[fila, columna].Style.Font.Bold = false;
columna++;
}
fila++;
}
chartRange = xlWorkSheet.get_Range("a3", "k" + (fila - 1));
chartRange.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, XlColorIndex.xlColorIndexAutomatic);
//Autoajustando tamanio
try
{
Range xlEntireColumn = null;
Range xlRange = null;
for (int i = 1; i < 12; i++)
{
xlRange = xlWorkSheet.Cells[1, i];
xlEntireColumn = xlRange.EntireColumn;
xlEntireColumn.AutoFit();
}
}
catch (Exception)
{
//Excepted error
}
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
xlWorkBook.SaveAs(path, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
xlApp = null;
//Abrir excel
Application excel = new Application();
Workbook wb = excel.Workbooks.Open(path, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
excel.Visible = true;
//excel.Quit();
//releaseObject(excel);
//excel.Quit();
}
/// <summary>
/// Metodo de graficación.
/// </summary>
/// <returns></returns>
public Chart Draw(params string[] filtros)
{
// Create a new instance of Chart
Chart chart = new Chart();
chart.AnimationEnabled = true;
// Create a new instance of Title
Title title = new Title();
// Set title property
title.Text = "Switch Selling Por SKU";
// Add title to Titles collection
chart.Titles.Add(title);
// Create a new instance of DataSeries
DataSeries dataSeries = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Column;
// Create a DataPoint
DataPoint dataPoint;
#region consulta
DataTable kpis;
try
{
kpis = new DataTable();
Consultas consulta = new Consultas();
kpis = consulta.SelectKPI_SwitchSellingSKU(filtros).Tables[0];
consulta = null;
string serAnt = "-5555";
string ser = "-1111";
foreach (DataRow g in kpis.Rows)
{
string x = (string)g["ProductSubfamilyDescription"];
long? y = g["NbrSwitchSelling"]!=DBNull.Value?(long?)g["NbrSwitchSelling"]:null;
ser = (string)g["IndustryLevel"];
//Creando las series
if (serAnt != ser)
{
if (serAnt != "-5555")
{
chart.Series.Add(dataSeries);
}
// Create a new instance of DataSeries
dataSeries = new DataSeries();
dataSeries.Name = ser;
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Circle;
dataSeries.SelectionEnabled = true;
dataSeries.LineThickness = 3;
}
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
// Add dataPoint to DataPoints collection.
dataSeries.DataPoints.Add(dataPoint);
serAnt = ser;
}
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
#endregion
// Add dataSeries to Series collection.
chart.Series.Add(dataSeries);
return chart;
}
public List<Filtro> ObtenerFiltrosDescripcion()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_MDZ_VisitasPlaneadas_MDZDescription().Tables[0];
Filtro filterIndustryLevel = Herramientas.Instance.obtenerFiltro(filtro1, "MDZ Description", "MDZ Description", "MDZ Description", false);
filtros.Add(filterIndustryLevel);
return filtros;
}
public Chart Draw(params string[] filtros)
{
// Create a new instance of Chart
Chart chart = new Chart();
chart.AnimationEnabled = true;
// Create a new instance of Title
Title title = new Title();
// Set title property
title.Text = "DISTRIBUCION PDV GRUPO";
// Add title to Titles collection
chart.Titles.Add(title);
// Create a new instance of DataSeries
DataSeries dataSeries = new DataSeries();
DataSeries dataSeriesSecundary = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
#region configuracion eje X
// Creating AxisX
Axis axisX = new Axis();
// Date time standard format
axisX.ValueFormatString = "000000";
// To avoid auto skip
chart.AxesX.Add(axisX);
#endregion
// Create a DataPoint
DataPoint dataPoint;
#region consulta
DataTable kpis;
try
{
kpis = new DataTable();
Consultas consulta = new Consultas();
kpis = consulta.SelectKPI_DistibucionPDVGrupo(filtros).Tables[0];
consulta = null;
bool band1 = true;
bool band2 = true;
string serAnt = "-5555";
string ser = "-1111";
foreach (DataRow g in kpis.Rows)
{
int? y = g["TotalClientes"] != DBNull.Value ? (int?)g["TotalClientes"] : null;
int x = (int)g["Ciclo"];
ser = (string)g["Segmentacion"];
ser = ser.ToUpper();
//--Condicion para el hueco se vaya a cero y no se corte la linea.
//if (y == null)
// y = 0;
if (ser != "TOTAL MAYORISTAS")
{ //Creando las series
if (serAnt != ser)
{
if (serAnt != "-5555")
{
chart.Series.Add(dataSeries);
}
// Create a new instance of DataSeries
dataSeries = new DataSeries();
dataSeries.Name = ser;
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
if (ser == "TIGRE")
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Diamond;
if (ser == "LOBO")
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Triangle;
if (ser == "AGUILA")
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Square;
if (ser == "COBRA")
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Cross;
if (ser == "PEZ")
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Line;
dataSeries.SelectionEnabled = true;
dataSeries.LineThickness = 3;
band1 = false;
}
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null && y != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
// Add dataPoint to DataPoints collection.
dataSeries.DataPoints.Add(dataPoint);
serAnt = ser;
}
else
{
if (serAnt != ser)
{
//////////////dataseries secundario para segundo eje y
// Create a new instance of DataSeries secundary
// Set DataSeries property
dataSeriesSecundary.AxisYType = AxisTypes.Secondary;
dataSeriesSecundary.Name = "TOTAL MAYORISTAS";
dataSeriesSecundary.Padding = new Thickness(30);
dataSeriesSecundary.RenderAs = RenderAs.Line;
dataSeriesSecundary.MarkerType = Visifire.Commons.MarkerTypes.Circle;
dataSeriesSecundary.SelectionEnabled = true;
dataSeriesSecundary.LineThickness = 3;
//////////////////////////////////////////////
band2 = false;
}
dataPoint = new DataPoint();
if (x != null && y != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
dataSeriesSecundary.DataPoints.Add(dataPoint);
serAnt = ser;
}
}
// Add dataSeries to Series collection.
if(band1!=true)
chart.Series.Add(dataSeries);
if (band2 != true)
chart.Series.Add(dataSeriesSecundary);
return chart;
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
#endregion
}
/// <summary>
///
/// </summary>
public List<Filtro> ObtenerFiltros()
{
filtros = new List<Filtro>();
Consultas consulta = new Consultas();
DataTable filtro1 = consulta.SelectKPI_SELL_OUT_KA_SEGMENT_CONTRIBUTION_Flt_ChainCode().Tables[0];
Filtro filterChainCode = Herramientas.Instance.obtenerFiltro(filtro1, "Cadena", "ChainCode", "ChainCode", false);
filtros.Add(filterChainCode);
return filtros;
}
/// <summary>
/// Metodo de graficación.
/// </summary>
/// <returns></returns>
public Chart Draw(params string[] filtros)
{
// Create a new instance of Chart
Chart chart = new Chart();
chart.AnimationEnabled = true;
// Create a new instance of Title
Title title = new Title();
// Set title property
title.Text = "Efectividad MDZ";
// Add title to Titles collection
chart.Titles.Add(title);
// Create a new instance of DataSeries
DataSeries dataSeries = new DataSeries();
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
#region configuracion eje X
// Creating AxisX
Axis axisX = new Axis();
// Date time standard format
axisX.ValueFormatString = "000000";
// To avoid auto skip
chart.AxesX.Add(axisX);
#endregion
// Create a DataPoint
DataPoint dataPoint;
#region consulta
DataTable kpis;
try
{
kpis = new DataTable();
Consultas consulta = new Consultas();
kpis = consulta.SelectKPI_EfectividadMDZ(filtros).Tables[0];
consulta = null;
string serAnt = "-5555";
string ser = "-1111";
foreach (DataRow g in kpis.Rows)
{
Decimal? y = g["EfectivenessByBrand"]!=DBNull.Value?(Decimal?)g["EfectivenessByBrand"]:null;
long x = (long)g["TimeId"];
ser = (string)g["ProductSubfamilyDescription"];
//Creando las series
if (serAnt != ser)
{
if (serAnt != "-5555")
{
chart.Series.Add(dataSeries);
}
// Create a new instance of DataSeries
dataSeries = new DataSeries();
dataSeries.Name = ser;
// Set DataSeries property
dataSeries.RenderAs = RenderAs.Line;
dataSeries.MarkerType = Visifire.Commons.MarkerTypes.Circle;
dataSeries.SelectionEnabled = true;
dataSeries.LineThickness = 3;
}
// Create a new instance of DataPoint
dataPoint = new DataPoint();
// Set YValue for a DataPoint
if (x != null) dataPoint.AxisXLabel = x.ToString();
if (!ciclos.Contains(x.ToString())) ciclos.Add(x.ToString());
dataPoint.YValue = System.Convert.ToDouble(y);
// Add dataPoint to DataPoints collection.
dataSeries.DataPoints.Add(dataPoint);
serAnt = ser;
}
}
catch (Exception Error)
{
throw (new Exception(Error.ToString()));
}
#endregion
// Add dataSeries to Series collection.
chart.Series.Add(dataSeries);
return chart;
}
private string ValidaCambioTerritorio()
{
Consultas cons = new Consultas();
//cons.DeleteNMT_CURRENT_FILES(territorio);
string nombreBD = System.Configuration.ConfigurationManager.AppSettings["NombreBDiSMS"];
string territorio = cons.DeleteNMT_CURRENT_FILES().Tables[0].Rows[0][0].ToString();
//Establece etiquetas de territorio
if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.PrincipalWindow")
{
((PrincipalWindow)parentWin).Dispatcher.Invoke(
System.Windows.Threading.DispatcherPriority.Normal,
new Action(
delegate()
{
((PrincipalWindow)parentWin).tbTerrirotio.Text = territorio;
}
));
}
if (parentWin.GetType().FullName == "IMC.Wpf.CoverFlow.NMT.IndicadorWindow")
{
((IndicadorWindow)parentWin).Dispatcher.Invoke(
System.Windows.Threading.DispatcherPriority.Normal,
new Action(
delegate()
{
((IndicadorWindow)parentWin).tbTerrirotio.Text = territorio;
}
));
}
return territorio;
}
