format('N'); // 1=Lunes ... 5=Viernes ... 7=Domingo
// 2) Offsets de días según regla
// Viernes -> 0,1,2,3 (vie,sab,dom,lun). Resto -> 0,1 (hoy,mañana)
$dayOffsets = ($dow === 5) ? [0, 1, 2, 3] : [0, 1];
// 3) Construimos la lista de fechas (Y-m-d) para las últimas 4 semanas
$dates = [];
If ($mesDias) {
$semana1=1;
$semana2=5;
} else {
$semana1=1;
$semana2=2;
}
for ($w = $semana1; $w < $semana2; $w++) {
foreach ($dayOffsets as $off) {
$d = clone $today;
// “Últimas 4 semanas” tomando bloques de 7 días hacia atrás
// y luego el offset del día dentro del bloque
$d->modify("-{$w} week");
if ($off > 0) {
$d->modify("+{$off} day");
}
$dates[] = $d->format('Y-m-d');
}
}
// Evitar duplicados por seguridad
$dates = array_values(array_unique($dates));
// 4) Preparar IN (...) seguro
$escapedDates = array_map(function ($d) {
return "'" . $d . "'";
}, $dates);
if (empty($escapedDates)) {
return 0;
}
$inDates = implode(',', $escapedDates);
$idProduct = (int)$idProduct;
$prefix = 'ps_';
// 5) SQL:
// - Parte A: ventas directas del producto (order_detail.product_id = idProduct)
// - Parte B: ventas del producto como componente de un pack (pack.id_product_item = idProduct)
// multiplicando por la cantidad del componente en el pack.
$sql = "
SELECT COALESCE(SUM(x.qty), 0) AS total_qty
FROM (
-- A) Ventas directas del producto
SELECT SUM(od.product_quantity) AS qty
FROM {$prefix}orders o
INNER JOIN {$prefix}order_detail od ON od.id_order = o.id_order
WHERE o.current_state IN (2,3,4,5,14,16)
AND od.product_id = {$idProduct}
AND DATE(o.date_add) IN ({$inDates})
UNION ALL
-- B) Ventas del producto como parte de packs
SELECT SUM(od.product_quantity * p.quantity) AS qty
FROM {$prefix}orders o
INNER JOIN {$prefix}order_detail od ON od.id_order = o.id_order
INNER JOIN {$prefix}pack p ON p.id_product_pack = od.product_id
WHERE o.current_state IN (2,3,4,5,14,16)
AND p.id_product_item = {$idProduct}
AND DATE(o.date_add) IN ({$inDates})
) x
";
//echo $sql;
$status_query0 = tep_db_fetch_array(tep_db_query($sql));
$total = (int) $status_query0['total_qty'];
/* Dependiendo de si se está ejecutando para las últimas 4 semanas o sólo para la última, divido por 3*/
if ($mesDias) {
$total=$total/4;
}
return round($total);
}
/*
$idProduct = 1196; // tu product_id
$total1 = mediaUltMes($idProduct,true);
$total2 = mediaUltMes($idProduct,false);
echo '******';
echo $total1/4;
echo '******';
echo $total2;
echo '******';
*/
$texto_query0 = "select products_id,round(sum(cantidad)) cantidad,max(fecActualizacion) fecActualizacion ".
"from ps_stock_web_foto ".
"where fechaFoto = curdate() ".
"group by 1 ";
$status_query0 = tep_db_query($texto_query0);
$stock = array();
$fechaActualizacion= date('2000-01-01');
while ($status_values0 = tep_db_fetch_array($status_query0)) {
$stock[$status_values0['products_id']] = $status_values0['cantidad'];
//Con lo siguiente, consigo la mayor fecha de actualización ya que como está no la daba
if ($fechaActualizacion < $status_values0['fecActualizacion']) {
$fechaActualizacion = $status_values0['fecActualizacion'];
}
}
echo 'Basado en el stock del dia: '.$fechaActualizacion.'
';
//Cargo en un array las salidas de cada producto para las fechas indicadas
if (($_POST['fecIni'])==0 or strlen($_POST['fecFin'])==0) {
/* if (date("G")>=11) {
$_POST['fecIni'] = date('Y-m-d',strtotime('now +2 DAY'));
$_POST['fecFin'] = date('Y-m-d',strtotime('now +8 DAY'));
} else {
$_POST['fecIni'] = date('Y-m-d',strtotime('now +1 DAY'));
$_POST['fecFin'] = date('Y-m-d',strtotime('now +7 DAY'));
}
*/
$_POST['fecIni'] = date('Y-m-d',strtotime('now +1 DAY'));
$_POST['fecFin'] = date('Y-m-d',strtotime('now +1 DAY'));
}
if (strlen($_POST['fecIniBarcelona'])==0 or strlen($_POST['fecFinBarcelona'])==0) {
if (date('N')==1) {
$_POST['fecIniBarcelona'] = date('Y-m-d',strtotime('now +2 DAY'));
$_POST['fecFinBarcelona'] = date('Y-m-d',strtotime('now +2 DAY'));
} else if (date('N')==2) {
$_POST['fecIniBarcelona'] = '2000-01-01';
$_POST['fecFinBarcelona'] = '2000-01-01';
} else if (date('N')==3) { //para el viernes y s?bado
$_POST['fecIniBarcelona'] = '2000-01-01';
$_POST['fecFinBarcelona'] = '2000-01-01';
} else if (date('N')==4) {
$_POST['fecIniBarcelona'] = date('Y-m-d',strtotime('now +2 DAY'));
$_POST['fecFinBarcelona'] = date('Y-m-d',strtotime('now +4 DAY'));
} else if (date('N')==5) { //ser?n los del lunes y martes
$_POST['fecIniBarcelona'] = '2000-01-01';
$_POST['fecFinBarcelona'] = '2000-01-01';
} else if (date('N')==6) {
$_POST['fecIniBarcelona'] = '2000-01-01';
$_POST['fecFinBarcelona'] = '2000-01-01';
} else if (date('N')==7) {
$_POST['fecIniBarcelona'] = '2000-01-01';
$_POST['fecFinBarcelona'] = '2000-01-01';
}
}
$sqlVentas = "select op.product_id products_id,sum(product_quantity) products_quantity " .
"from ps_order_detail op join ps_orders o on op.id_order=o.id_order " .
"join ps_cart d on (d.id_cart=o.id_cart) ".
"join ps_product p on (p.id_product=op.product_id) ".
"where tramo_date>date_add(now(),INTERVAL -90 DAY) ".
"and p.active=1 ".
"group by 1";
$reg_sqlVentas = tep_db_query($sqlVentas);
$ventas = array();
while ($status_values_ventas = tep_db_fetch_array($reg_sqlVentas)) {
//Averiguo si el producto actual es un menú
$texto_query1 = "select p.id_product_item products_id,p.quantity products_quantity " .
"from ps_pack p ".
"where p.id_product_pack=".$status_values_ventas['products_id'];
$status_query1 = tep_db_query($texto_query1);
if (tep_db_num_rows($status_query1)>0) {
while ($status_values1 = tep_db_fetch_array($status_query1)) {
//$salidas[$status_values1['products_id']] = array('cantidad'=>$salidas[$status_values1['products_id']]['cantidad']+$status_values1['products_quantity']*$status_values['products_quantity']);
$ventas[$status_values1['products_id']] = $ventas[$status_values1['products_id']]+$status_values1['products_quantity']*$status_values_ventas['products_quantity'];
}
} else {
//$salidas[$status_values['products_id']] = array('cantidad'=>$salidas[$status_values['products_id']]['cantidad']+$status_values['products_quantity']);
$ventas[$status_values_ventas['products_id']] = $ventas[$status_values_ventas['products_id']]+$status_values_ventas['products_quantity'];
}
}
/* *************************/
$sqlVentasUltimaSemana = "select op.product_id products_id,sum(product_quantity) products_quantity " .
"from ps_order_detail op join ps_orders o on op.id_order=o.id_order " .
"join ps_cart d on (d.id_cart=o.id_cart) ".
"join ps_product p on (p.id_product=op.product_id) ".
// "where date_delivery>date_add(now(),INTERVAL -7 DAY) ".
"where tramo_date between date(date_add(now(),INTERVAL -7 DAY)) and date(now()) ".
"and p.active=1 ".
"group by 1";
$reg_sqlVentasUltimaSemana = tep_db_query($sqlVentasUltimaSemana);
$ventasUltimaSemana = array();
while ($status_values_ventasUltimaSemana = tep_db_fetch_array($reg_sqlVentasUltimaSemana)) {
//Averiguo si el producto actual es un menú
$texto_query1 = "select p.id_product_item products_id,p.quantity products_quantity " .
"from ps_pack p ".
"where p.id_product_pack=".$status_values_ventasUltimaSemana['products_id'];
$status_query1 = tep_db_query($texto_query1);
if (tep_db_num_rows($status_query1)>0) {
while ($status_values1 = tep_db_fetch_array($status_query1)) {
//$salidas[$status_values1['products_id']] = array('cantidad'=>$salidas[$status_values1['products_id']]['cantidad']+$status_values1['products_quantity']*$status_values['products_quantity']);
$ventasUltimaSemana[$status_values1['products_id']] = $ventasUltimaSemana[$status_values1['products_id']]+$status_values1['products_quantity']*$status_values_ventasUltimaSemana['products_quantity'];
}
} else {
//$salidas[$status_values['products_id']] = array('cantidad'=>$salidas[$status_values['products_id']]['cantidad']+$status_values['products_quantity']);
$ventasUltimaSemana[$status_values_ventasUltimaSemana['products_id']] = $ventasUltimaSemana[$status_values_ventasUltimaSemana['products_id']]+$status_values_ventasUltimaSemana['products_quantity'];
}
}
/* *************************/
/* AAAAAAAAAAAAAAAAAAAAAAAA */
$tq5 = "select op.product_id products_id,sum(product_quantity) products_quantity " .
"from ps_order_detail op join ps_orders o on op.id_order=o.id_order " .
"join ps_cart dd on (dd.id_cart=o.id_cart) " .
"join ps_address ad on o.id_address_delivery=ad.id_address ".
"join ps_carrier ca on ca.id_carrier=o.id_carrier " .
"where ((dd.tramo_date between '" . $_POST['fecIni'] . "' and '" . $_POST['fecFin'] . "' and id_reference<>49) or (dd.tramo_date between '" . $_POST['fecIniBarcelona'] . "' and '" . $_POST['fecFinBarcelona'] . "' and id_reference=49)) ".
"and o.current_state in (2,3,4,5,14,16) ".
"group by 1";
//echo $tq5;
$reg_query5 = tep_db_query($tq5);
$salidas = array();
while ($status_values = tep_db_fetch_array($reg_query5)) {
//Averiguo si el producto actual es un menú
$texto_query1 = "select p.id_product_item products_id,p.quantity products_quantity " .
"from ps_pack p ".
"where p.id_product_pack=".$status_values['products_id'];
$status_query1 = tep_db_query($texto_query1);
if (tep_db_num_rows($status_query1)>0) {
while ($status_values1 = tep_db_fetch_array($status_query1)) {
//$salidas[$status_values1['products_id']] = array('cantidad'=>$salidas[$status_values1['products_id']]['cantidad']+$status_values1['products_quantity']*$status_values['products_quantity']);
$salidas[$status_values1['products_id']] = $salidas[$status_values1['products_id']]+$status_values1['products_quantity']*$status_values['products_quantity'];
}
} else {
//$salidas[$status_values['products_id']] = array('cantidad'=>$salidas[$status_values['products_id']]['cantidad']+$status_values['products_quantity']);
$salidas[$status_values['products_id']] = $salidas[$status_values['products_id']]+$status_values['products_quantity'];
}
}
//print_r($salidas);
$texto_query = "SELECT p.id_product products_id,pl.name products_name,sa.quantity disponibles " .
"FROM ps_product_lang pl join ps_product p on (p.id_product=pl.id_product) ".
"join ps_stock_available sa on sa.id_product=pl.id_product " .
"where p.active=1 ".
"and pl.name not like '%monedero%' ".
"and pl.name not like '%pack%' ".
"and pl.name not like '%Guia%' ".
"and pl.name not like '%Libro%' ".
"order by 2 ";
$status_query = tep_db_query($texto_query);
//echo $texto_query;
?>