<?php
include "../funciones.php";
include "../dbconection.php";

header("Content-Type: text/html; charset=iso-8859-1");

$Where = " WHERE 1=1 ";

$sXMLContenido = "";
$totalRows = 0;
$bEntraOrigen = false;

$selectedArticulo="";
$selectedOrigen="";

$dateStart = trim($_REQUEST['dat1']);
if(isset($_REQUEST['dat2'])){
	$dateEnd = trim($_REQUEST['dat2']);
	
	if($dateStart!=$dateEnd){
		$Periodo = $dateStart." - ".$dateEnd;
	}else{
		$Periodo = $dateStart;
	}
}else{
	$dateEnd = $dateStart;
	
	$Periodo = $dateStart;
}

if(isset($_REQUEST['minimo'])){
	if($_REQUEST['minimo']!=""){
		$minimo = $_REQUEST['minimo'];
	}else{
		$minimo = 0;
	}
}else{
	$minimo = 0;
}

if(isset($_REQUEST['strArticulo'])){
	$Articulo = $_REQUEST['strArticulo'];
}else{
	$Articulo = "";
}

if($_REQUEST['intMode']=='0'){

	if($_REQUEST['selectBy']=='articulo'){ // AGRUPADO POR ARTICULO
		
		/*$query = "SELECT t1.strArticulo as Articulo, COUNT(t1.intArticuloVisita) as Visitas FROM tblArticulosVisitas as t1 
		WHERE CAST(datFecha AS date)>='".$dateStart."' and CAST(datFecha AS date)<='".$dateEnd."'
		GROUP BY strArticulo HAVING COUNT(t1.intArticuloVisita)>".$minimo." ORDER BY COUNT(t1.strOrigen) desc";*/
		$strCampoGroup = "";
		$strCampo = "";
		
		if(isset($_REQUEST['intAgrupado'])){
			if(intval($_REQUEST['intAgrupado'])==1){
				$strCampoGroup = ", left(Replace(Replace(t1.strOrigen,'https://',''),'http://',''), CHARINDEX('/',Replace(Replace(t1.strOrigen,'https://',''),'http://',''))) ";
				$strCampo = $strCampoGroup ." as Origen";
			}
		}

		$query = "SELECT t1.strArticulo as Articulo ". $strCampo .", COUNT(t1.intArticuloVisita) as Visitas FROM tblArticulosVisitas as t1 
		WHERE CAST(datFecha AS date)>='".$dateStart."' and CAST(datFecha AS date)<='".$dateEnd."'";
		if(isset($_REQUEST['strArticulo'])){
			if($_REQUEST['strArticulo']!=""){
				$query .= " and t1.strArticulo like '%". $_REQUEST['strArticulo'] ."%'";
			}		
		}		
		$query .= " GROUP BY strArticulo ". $strCampoGroup ." HAVING COUNT(t1.intArticuloVisita)>".$minimo;
		
		if(isset($_REQUEST['intAgrupado'])){
			if(intval($_REQUEST['intAgrupado'])==1){
				$query .= " ORDER BY t1.strArticulo asc, COUNT(t1.intArticuloVisita) desc";
			}else{
				$query .= " ORDER BY COUNT(t1.intArticuloVisita) desc";
			}
		}else{
			$query .= " ORDER BY COUNT(t1.intArticuloVisita) desc";
		}
		
		//$query .= " ORDER BY t1.strArticulo asc";

		$selectedArticulo=" Selected";
		
	}else if($_REQUEST['selectBy']=='origen'){  // AGRUPADO POR ORIGEN
		
		$bEntraOrigen = true;
		$query = "SELECT left(Replace(Replace(t1.strOrigen,'https://',''),'http://',''), CHARINDEX('/',Replace(Replace(t1.strOrigen,'https://',''),'http://',''))) as Origen, 
				COUNT(t1.intArticuloVisita) as Visitas FROM tblArticulosVisitas as t1
				WHERE CAST(datFecha AS date)>='".$dateStart."' and CAST(datFecha AS date)<='".$dateEnd."'
				GROUP BY left(Replace(Replace(t1.strOrigen,'https://',''),'http://',''), CHARINDEX('/',Replace(Replace(t1.strOrigen,'https://',''),'http://',''))) 
				ORDER BY COUNT(t1.strOrigen) desc";
		$selectedOrigen=" Selected";
		// HAVING COUNT(t1.intArticuloVisita)>".$minimo."  no va por que vienen diferentes sources m.facebook, facebook.com y abajo hago sumatoria, entonces alli hago el having count
	}

	//print($query);
	
	$rs = $CON->execute($query);
	//$total_rows = $rs->RecordCount();
	//exit("Cuantos = ". $total_rows);
	
	$num_columns = $rs->Fields->Count();
	$totales = 0; 
	
	if(!$rs->EOF){
		$iLinea = 0;
		$sBgColorRow = "#FFF";
			
		while(!$rs->EOF){
			
			if($_REQUEST['selectBy']=='origen'){
				
				// Lleno un ARRAY para abajo pintarlo en HTML
				$strOrigen = $rs->Fields(0)->value;
				$intCuantos = $rs->Fields(1)->value;
				
				foreach ($arrOrigenes as $i => $value) {
					
					$pos = strrpos($strOrigen, $i);
					//exit("pos = ". $pos ." | Origen = ". $strOrigen ." | i = ". $i ." | $value = ". $value);
					
					if ($pos>0) {
						// encontrado...
						//print("array i = ". $i ." | value = ". $value ."\n");
						$arrOrigenes[$i] += $intCuantos;
						//print("array i = ". $i ." ". $arrOrigenes[$i] ." <br/>");
						//exit("final");
						break;
					}
				}
									
			}else if($_REQUEST['selectBy']=='articulo'){
				
				if(($iLinea % 2)==0){
					$sBgColorRow = "#E1E7F9";
				}else{
					$sBgColorRow = "#FFF";
				}
				
				$sXMLContenido .= "<tr id='trRENGLON_".$iLinea."' height='20' numRenglon='".$iLinea."' bgcolor='".$sBgColorRow."'>";

					$strCodigoCamp = $rs->Fields(0)->value;
					$strDetalleCamp = "";
					
					$sXMLContenido .= "<td id='tdChk_".$iLinea."' align='center' style='padding: 2px;'><input type='checkbox' id='chk".$iLinea."' onchange='checkChange(".$iLinea.")' checked/></td>";
					
					$sXMLContenido .= "<td id='tdRow_".$iLinea."' style='padding: 2px;'>".$iLinea."</td>";
					
					for ($i=0; $i < $num_columns; $i++) {
						$fieldName = $rs->Fields($i)->name;
						
						$value = $rs->Fields($i)->value;
						$value = str_replace(">","", str_replace("<","", $value));
						$style = "";
						
						if($fieldName=="Visitas"){
							$value  = number_format($value);
							$sXMLContenido .= "<td id='tdVisitas_".$iLinea."' style='padding: 5px; padding-right:10px; text-align:right;'>". $value . "</td>";
						}else{
							$sXMLContenido .= "<td id='tdArticulo_".$iLinea."' style='padding: 5px;'>". $value . "</td>";
						}
						
					}
					
				$sXMLContenido .= "</tr>";
				
				$totales += $value; 
				$totalRows = $totalRows + 1;
				$iLinea++;
				
			}
			
			$rs->MoveNext(); //move on to the next record
		}
	}
	
	if($_REQUEST['selectBy']=='origen'){  // LLENO HTML desde un ARRAY 

		foreach ($arrOrigenes as $i => $value) {
				
			$fieldName = $i;
			if($value != "" && $value != 0){
				
				if($value>$minimo){	
					if(($iLinea % 2)==0){
						$sBgColorRow = "#E1E7F9";
					}else{
						$sBgColorRow = "#FFF";
					}
					
					$sXMLContenido .= "<tr id='trRENGLON_".$iLinea."' height='20' numRenglon='".$iLinea."' bgcolor='".$sBgColorRow."'>";
						$sXMLContenido .= "<td id='tdChk_".$iLinea."' align='center' style='padding: 2px;'><input type='checkbox' id='chk".$iLinea."' onchange='checkChange(".$iLinea.")' checked/></td>";
						$sXMLContenido .= "<td id='tdRow_".$iLinea."' style='padding: 5px;'>".$iLinea."</td>";
						
						$sXMLContenido .= "<td id='tdArticulo_".$iLinea."' style='padding: 5px;'>". $fieldName . "</td>";
						$sXMLContenido .= "<td id='tdVisitas_".$iLinea."' style='padding: 5px; padding-right:10px; text-align:right;'>". $value . "</td>";
						
					$sXMLContenido .= "</tr>";
					
					$totales += $value; 
					$iLinea++;
					$totalRows++;
				}
				
			}
		}
	}

	$sHTML = '<br/>';
	$sHTML .= 'Por: <select id="selectBy">
					 <option value="articulo" '.$selectedArticulo.'>Articulo</option>
					 <option value="origen" '.$selectedOrigen.'>Origen</option>
					</select><br/><br/>';
	$sHTML .= 'Periodo:&nbsp;<input id="txtFecha" type="text" size="30" style="height:22px;" value="'.$Periodo.'"/>&nbsp;&nbsp;&nbsp;';
	$sHTML .= 'Articulo:&nbsp;<input id="txtArticulo" type="text" size="30" style="height:22px;" value="'.$Articulo.'"/>&nbsp;&nbsp;&nbsp;';
	$sHTML .= 'Minimo:&nbsp;&nbsp;<input id="txtMinimoCantidad" type="text" size="5" style="height:22px; text-align:right;" value="'.$minimo.'"/>&nbsp;&nbsp;&nbsp;';
	if(isset($_REQUEST['intAgrupado'])){
		if(intval($_REQUEST['intAgrupado'])==1){
			$sHTML .= 'Separado:&nbsp;&nbsp;<input id="chkAgrupado" type="checkbox" checked/>';
		}else{
			$sHTML .= 'Separado:&nbsp;&nbsp;<input id="chkAgrupado" type="checkbox"/>';
		}
	}
	$sHTML .= '<br/><br/><br/>';
	
	$sHTML .= '<a href="javascript: verDetalleEstArt();" class="btnes" >Buscar</a>';
	
	$sHTML .= '<div style="width:67%; text-align:right;"><a href="javascript: verGrafica(\'todos\');" class="btnes">Ver Grafica</a> </div><br/>';
	
	$sHTML .= "<table id='tblGridEstadistica' totalRows='".$totalRows."' border='1' bordercolor='#666666' cellspacing='1' cellpadding='5' style='border-collapse:collapse; color: #333; font-size: 13px;'>";
	$sHTML .= "<thead>";
	$sHTML .= "<tr style='background-color: #C6D3EC; font-weight: bold;'>";
	
		$sHTML .= "<th style='padding:6px; width:30px;'><input type='checkbox' id='chkAll' onclick='checkAll(".$iLinea.")' checked/></th>";
		$sHTML .= "<th style='padding:6px; width:30px;'>No.</th>";
		for ($i=0; $i < $num_columns; $i++) {
			$fieldName = $rs->Fields($i)->name;
			$sHTML .= "<th style='padding:6px;'>". $fieldName ."</th>";
		}
		
	$sHTML .= "</tr>";
	$sHTML .= "</thead>";
	$sHTML .= "<tbody id='tbodyresultados'>";
		$sHTML .= $sXMLContenido;
	$sHTML .= "</tbody>";
	$sHTML .= "<tfoot id='tbodyresultados'>";
		$sHTML .= "<tr style='background-color: #C6D3EC; font-weight:bold; color:#000;'><td id='tdTotal' colspan='".($num_columns+1)."' style='padding:4px;'>Total: ".$totalRows."</td><td>".$totales."</td></tr>";
	$sHTML .= "</tfoot>";
	$sHTML .="</table>";

}else{ //// DETALLE DE CAMPAŅA

	//AGREGA 
	$strCODIGOCAMP = $_REQUEST['strCodigoCamp'];
	$strDetalleCamp = $_REQUEST['strDetalleCamp'];
	
	$orderBy = $_REQUEST['campoOrderBy'];
	
	if($_REQUEST['dirOrderBy']=='1'){
		$orderBy2 = " desc";
		$intOrder = 0;
	}else{
		$orderBy2 = " asc";
		$intOrder = 1;
	}
	
	/////// TRAER DESCRIPCION DE QUERYCONSULTA
	$query = "SELECT strCodigoCamp, intPacienteMedico, strSeccion, strRespuesta, strObservaciones, datFecha FROM tblvisitasemailmarketing WHERE strCodigoCamp = '".$strCODIGOCAMP."' order by ".$orderBy.$orderBy2;			
	
	$rs = $CON->execute($query);
	$num_columns = $rs->Fields->Count();
	
	if(!$rs->EOF){
		$iLinea = 1;
		$sBgColorRow = "#FFF";
	
		while(!$rs->EOF){
			if(($iLinea % 2)==0){
				$sBgColorRow = "#E1E7F9";
			}else{
				$sBgColorRow = "#FFF";
			}
			$sXMLContenido .= "<tr id='trRENGLON_".$iLinea."' height='20' numRenglon='".$iLinea."' bgcolor='".$sBgColorRow."'>";
				$sXMLContenido .= "<td id='td".$iLinea."' style='padding: 2px;'>". $iLinea . "</td>";

				for ($i=0; $i < $num_columns; $i++) {
					$fieldName = $rs->Fields($i)->name;
					
					$value = $rs->Fields($i)->value;
					$value = str_replace(">","", str_replace("<","", $value));
					
					$sXMLContenido .= "<td id='td".$fieldName."_".$iLinea."' style='padding: 2px;'>". $value . "</td>";
				}
			$sXMLContenido .= "</tr>";
			$totalRows = $totalRows + 1;
			$iLinea++;
			
			$rs->MoveNext(); //move on to the next record
		}
		$sHTML = '';
		$sHTML .="<br><a href='javascript: showPage(\"tabMarketingEstadistica\");' style='font-size:14px;'>&lt;Atras</a><br>";
		$sHTML .="<p style='font-size:14px;'>Detalle Campaņa: ".$strDetalleCamp."</p><br>";
		$sHTML .="<table id='tblGridEstadistica' totalRows='".$totalRows."' width='100%' border='1' bordercolor='#666666' cellspacing='1' cellpadding='5' style='border-collapse:collapse; color: #333; font-size: 13px;'>";
		$sHTML .= "<thead>";
		$sHTML .= "<tr style='background-color: #C6D3EC; font-weight: bold;'>";
			$sHTML .= "<td style='padding:4px;'>#</td>";
			/*$sHTML .= "<td style='padding:4px;'>intVisita</td>";*/
			$sHTML .= "<td style='padding:4px;'>strCodigoCamp</td>";
			$sHTML .= "<td style='padding:4px;'>intPacienteMedico</td>";
			$sHTML .= "<td style='padding:4px;'><a href='javascript: verDetalleCampania(\"".$strCODIGOCAMP."\",\"strSeccion\",\"".$intOrder."\",\"".$strDetalleCamp."\");'>strSeccion</a></td>";
			$sHTML .= "<td style='padding:4px;'>strRespuesta</td>";
			$sHTML .= "<td style='padding:4px;'><a href='javascript: verDetalleCampania(\"".$strCODIGOCAMP."\",\"strObservaciones\",\"".$intOrder."\",\"".$strDetalleCamp."\");'>strObservaciones</a></td>";
			$sHTML .= "<td style='padding:4px;'><a href='javascript: verDetalleCampania(\"".$strCODIGOCAMP."\",\"datFecha\",\"".$intOrder."\",\"".$strDetalleCamp."\");'>datFecha</a></td>";
		//	$sHTML .= "<td style='padding:4px;'>Quejas</td>";
		$sHTML .= "</tr>";
		$sHTML .= "</thead>";
		$sHTML .= "<tbody id='tbodyresultados'>";
		$sHTML .= $sXMLContenido;
		$sHTML .= "</tbody>";
		$sHTML .= "<tfoot id='tbodyresultados'>";
		$sHTML .= "<tr><td colspan='9' style='padding:4px;background-color: #C6D3EC; font-weight: bold;'>Total: ".$totalRows."</td></tr>";
		$sHTML .= "</tfoot>";
		$sHTML .="</table>";

	}else{
		$sXMLContenido .= "<td id='tdData_".$iLinea."' style='padding: 2px;'></td>";
	}
	
}

//close the connection and recordset objects freeing up resources 
$rs->Close();
$CON->Close();

$rs = null;
$CON = null;

print($sHTML);

?>