-- Active: 1750974463121@@127.0.0.1@3306@gnbrepuestos_panel
#corregir cuenta corriente de proveedor.

#HELPER
SELECT * FROM orden_pago_proveedor ORDER BY id DESC
SELECT * FROM remitos WHERE id_proveedor = 1 ORDER BY codremito DESC
SELECT * FROM proveedor WHERE id = 1

SELECT * FROM `debe_haber_proveedor` WHERE id_proveedor = 1 AND f=0 ORDER BY id DESC

SELECT 
orden_pago_proveedor.id as id_orden_pago_proveedor,
debe_haber_proveedor.id as id_debe_haber_proveedor,
debe_haber_proveedor.*
 FROM orden_pago_proveedor 
left JOIN debe_haber_proveedor ON
orden_pago_proveedor.debe_haber_proveedor_id = debe_haber_proveedor.id
order by orden_pago_proveedor.id desc;


#PAGOS PROVEEDOR
  SELECT
      dhp.id,
      r.codremito AS remito,
      dhp.nom_proveedor AS proveedor,
      dhp.debe AS total,
      COALESCE(dhp.pagado, 0) AS pagado,
      (dhp.debe - COALESCE(dhp.pagado, 0)) AS pendiente,
      CASE
          WHEN dhp.debe - COALESCE(dhp.pagado, 0) <= 0 THEN '✓ PAGADO'
          WHEN dhp.pagado > 0 THEN '⚠ PARCIAL'
          ELSE '✗ PENDIENTE'
      END AS estado,
      dhp.f AS facturado,
      -- Historial de pagos con fechas e importes
      GROUP_CONCAT(
          DISTINCT CONCAT(
              DATE_FORMAT(dhp_pago.fecha, '%d/%m/%Y'),
              ' - $',
              FORMAT(opp2.monto, 2),
              ' (OP#', opp2.id, ')'
          )
          ORDER BY dhp_pago.fecha, opp2.id
          SEPARATOR ' | '
      ) AS historial_pagos,
      -- IDs de órdenes de pago donde aparece este remito
      GROUP_CONCAT(DISTINCT opp2.id ORDER BY opp2.id) AS ops
  FROM debe_haber_proveedor dhp
  LEFT JOIN remitos r ON dhp.id_remito = r.codremito
  -- Buscar órdenes de pago que contengan este remito en orden_pago_proveedor_ids
  LEFT JOIN orden_pago_proveedor opp2 ON (
      opp2.orden_pago_proveedor_ids LIKE CONCAT('%', dhp.id, '%')
      OR opp2.orden_pago_proveedor_ids = dhp.id
      OR opp2.orden_pago_proveedor_ids LIKE CONCAT(dhp.id, ',%')
      OR opp2.orden_pago_proveedor_ids LIKE CONCAT('%,', dhp.id, ',%')
      OR opp2.orden_pago_proveedor_ids LIKE CONCAT('%,', dhp.id)
  )
  LEFT JOIN debe_haber_proveedor dhp_pago ON dhp_pago.id = opp2.debe_haber_proveedor_id
  WHERE dhp.deleted_at IS NULL
    AND dhp.borrado != 1
    AND dhp.id_remito IS NOT NULL
    AND dhp.id_proveedor = 1
    AND r.f = 0
    and r.codremito = 393
  GROUP BY dhp.id
  ORDER BY r.codremito DESC;

SELECT * FROM remitos WHERE codremito = 85 OR codremito = 98
SELECT * FROM `debe_haber_proveedor` WHERE referencia_de_id = 96

#BORRAR ORDENES DE PAGO
    ALTER TABLE `orden_pago_proveedor` DROP FOREIGN KEY `FK_E6F4B263ACD1C6AF`; 
    ALTER TABLE `orden_pago_proveedor` ADD CONSTRAINT `FK_E6F4B263ACD1C6AF` FOREIGN KEY (`debe_haber_registro_id`) REFERENCES `debe_haber_proveedor`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
    ALTER TABLE `debe_haber_proveedor` DROP FOREIGN KEY `FK_447EF9E17C2AB5B`; 
    ALTER TABLE `debe_haber_proveedor` ADD CONSTRAINT `FK_447EF9E17C2AB5B` FOREIGN KEY (`referencia_de_id`) REFERENCES `orden_pago_proveedor`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 
    ALTER TABLE `historico_debe_haber_proveedor` DROP FOREIGN KEY `FK_1650A09DACD1C6AF`; 
    ALTER TABLE `historico_debe_haber_proveedor` ADD CONSTRAINT `FK_1650A09DACD1C6AF` FOREIGN KEY (`debe_haber_registro_id`) REFERENCES `debe_haber_proveedor`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE; 


    DELETE FROM debe_haber_proveedor WHERE id_proveedor = 1 AND f = 0 AND id_remito IS NULL;
    UPDATE debe_haber_proveedor SET pagado=NULL WHERE id_proveedor = 1 AND f = 0;


    ALTER TABLE `orden_pago_proveedor` DROP FOREIGN KEY `FK_E6F4B263ACD1C6AF`; 
    ALTER TABLE `orden_pago_proveedor` ADD CONSTRAINT `FK_E6F4B263ACD1C6AF` FOREIGN KEY (`debe_haber_registro_id`) REFERENCES `debe_haber_proveedor`(`id`) ON UPDATE RESTRICT ON DELETE RESTRICT; 
    ALTER TABLE `debe_haber_proveedor` DROP FOREIGN KEY `FK_447EF9E17C2AB5B`; 
    ALTER TABLE `debe_haber_proveedor` ADD CONSTRAINT `FK_447EF9E17C2AB5B` FOREIGN KEY (`referencia_de_id`) REFERENCES `orden_pago_proveedor`(`id`) ON UPDATE RESTRICT ON DELETE RESTRICT; 
    ALTER TABLE `historico_debe_haber_proveedor` DROP FOREIGN KEY `FK_1650A09DACD1C6AF`; 
    ALTER TABLE `historico_debe_haber_proveedor` ADD CONSTRAINT `FK_1650A09DACD1C6AF` FOREIGN KEY (`debe_haber_registro_id`) REFERENCES `debe_haber_proveedor`(`id`) ON UPDATE RESTRICT ON DELETE RESTRICT;


#Problemas de sincronizacion de imágenes 08-07-2024
######################################################################MAL######################################################################
select articulos_imagenes.*,articulo.id from `gnbrepuestos_panel`.`articulos_imagenes`
left join articulo on articulo.`codigo` = articulos_imagenes.`articulo`
WHERE articulos_imagenes.`articulo` = "BX15040022"


#JGO RETENES MOTOR S2 – RX 150CC BX15040022 (88)
select * from `gnbrepuestos_final`.wp_postmeta where `meta_key` LIKE 'reference_id' AND `meta_value` like "%articulo_88%"
SELECT * FROM `gnbrepuestos_final`.wp_postmeta WHERE post_id = 4392 #BUSCAO _thumbnail_id
SELECT * FROM `gnbrepuestos_final`.wp_postmeta WHERE post_id = 4495




SELECT p.*
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'attachment'
  AND p.post_status IN ('publish', 'future', 'draft', 'pending', 'private', 'inherit', 'trash')
  AND pm.meta_key = 'reference_id'
  AND pm.meta_value LIKE '%imagen_1%'
LIMIT 1;


######################################################################BIEN######################################################################
select articulos_imagenes.*,articulo.id from `gnbrepuestos_panel`.`articulos_imagenes`
left join articulo on articulo.`codigo` = articulos_imagenes.`articulo`
WHERE articulos_imagenes.`articulo` = "bx-mulwa"


#MULETA HONDA WAVE bx-mulwa (58)
select * from `gnbrepuestos_final`.wp_postmeta where `meta_key` LIKE 'reference_id' AND `meta_value` like "%articulo_58%"
SELECT * FROM `gnbrepuestos_final`.wp_postmeta WHERE post_id = 4464 #BUSCAO _thumbnail_id
SELECT * FROM `gnbrepuestos_final`.wp_postmeta WHERE post_id = 4327


SELECT p.*
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'attachment'
  AND p.post_status IN ('publish', 'future', 'draft', 'pending', 'private', 'inherit', 'trash')
  AND pm.meta_key = 'reference_id'
  AND pm.meta_value LIKE '%imagen_1%'
LIMIT 1;




#----------------------------------


select * from `gnbrepuestos_final`.wp_postmeta where post_id = 3367 #type=product


SELECT * FROM `gnbrepuestos_final`.wp_posts WHERE id = 3367 #null
SELECT * FROM `gnbrepuestos_final`.wp_posts WHERE id = 4435 #type=product


sELECT ID FROM wp_posts WHERE post_type IN ('product', 'product_variation')
--------------------------
select * from `gnbrepuestos_final`.wp_posts where id = 4367 #type=attachment
select * from `gnbrepuestos_final`.wp_postmeta where post_id = 4367 #type=attachment


select * from `articulo` where id = 24






#########
DELETE FROM wp_postmeta
WHERE post_id NOT IN (SELECT ID FROM wp_posts);


codigo = bx-kitskua
codigo = bx-estsm6
