FileMgr

✏️ /home/mien4214/public_html/pos/application/models/promo.php

✕ Tutup

<?php 
DELIMITER $$

USE `dbplb`$$

DROP PROCEDURE IF EXISTS `sp_select_pdpro`$$

CREATE DEFINER=`netsys`@`%` PROCEDURE `sp_select_pdpro`(_cabang CHAR(5),_nobukti CHAR(20),_supp CHAR(20),_flag CHAR(1))
BEGIN
DECLARE _IDPro CHAR(30);
DECLARE _titem CHAR(1);
DECLARE _tdskn CHAR(1);
DECLARE _mix TINYINT(1);
DECLARE _minjumlahitem TINYINT(2);
DECLARE _minjumlahitemtrans TINYINT(2);
DECLARE _disc1 DOUBLE(5,2);
DECLARE _disc2 DOUBLE(5,2);
DECLARE _disc3 DOUBLE(5,2);
DECLARE _nilai DOUBLE(17,2);
DECLARE _nilai2 DOUBLE(17,2);
DECLARE _bruto DOUBLE(17,2);
DECLARE _ftop INTEGER DEFAULT -1;
DECLARE _ttop INTEGER DEFAULT -1;
DECLARE _custterm INTEGER DEFAULT -1;
DECLARE _level INTEGER;
DECLARE _qtybns INTEGER;
DECLARE _perqty INTEGER;
DECLARE _perqty1 INTEGER DEFAULT 0;
DECLARE _perqty2 INTEGER DEFAULT 0;
DECLARE _kdbrgbns CHAR(20);
DECLARE _IsMinJumlahOK TINYINT(1);
DECLARE _SEQ INTEGER;
DECLARE _Ada INTEGER;
DECLARE _NU INTEGER;
DECLARE _AdaDist INTEGER DEFAULT 0;
DECLARE _AdaDist1 INTEGER DEFAULT 0;
DECLARE done BOOL DEFAULT FALSE;
DECLARE curs1 CURSOR FOR 
SELECT SQL_CALC_FOUND_ROWS DISTINCT h.nobukti 
FROM pdpro_header h
LEFT JOIN pdpro_customer dp ON h.nobukti=dp.nobukti
INNER JOIN soheader sp ON sp.noso=_nobukti
AND 
(
  CASE h.typecust
	WHEN "3" THEN   
       ( SELECT ref.CustNo FROM customer ref WHERE ref.custno=sp.CustNo) IN  
       ( SELECT ref.custno FROM pdpro_customer ref WHERE ref.custno=sp.CustNo)  
       WHEN "2" THEN   
       ( SELECT ref.GroupCust FROM customer ref WHERE ref.custno=sp.CustNo) IN  
       ( SELECT pdf.groupcust FROM pdpro_groupcust pdf WHERE pdf.nobukti=h.nobukti)
       WHEN "4" THEN   
       ( SELECT ref.TypeCust FROM customersupptype_detail ref WHERE ref.custno=sp.CustNo AND kdsupplier=_supp LIMIT 1) IN  
       ( SELECT pdf.TypeCust FROM pdpro_typecust pdf WHERE pdf.nobukti=h.nobukti)    
       WHEN "1" THEN  
       ( SELECT ref.custno  FROM customer ref  WHERE (ref.custno=sp.CustNo AND ref.cabang=sp.cabang)) IN
       ( SELECT ref.custno  FROM customer ref  WHERE (ref.custno=sp.CustNo AND ref.cabang=sp.cabang))  
       
   END    
)
LEFT JOIN sodetail dsp ON dsp.noso=sp.noso  
INNER JOIN pdpro_detail dd ON dd.nobukti=h.nobukti
WHERE h.stat="6"  
AND sp.cabang=_cabang
AND ((h.fromdate<=sp.Tgl AND h.todate>=sp.tgl) OR (h.eternity="Y"))
AND (
  CASE h.typebarang  
	WHEN "3" THEN   
       ( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=dsp.kdbrg LIMIT 1) IN
       ( SELECT ref.kdbrg FROM pdpro_barang ref WHERE ref.nobukti=h.nobukti )  
       WHEN "4" THEN   
       ( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=dsp.kdbrg) IN  
       ( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=h.nobukti)  
       WHEN "2" THEN   
       ( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=dsp.kdbrg) IN  
       ( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=h.nobukti)  
       WHEN "1" THEN  
       ( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=dsp.kdbrg LIMIT 1) IN
       ( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=dsp.kdbrg)   
       
       
       
   END    
)
 AND 
h.kdsupplier=_supp
ORDER BY h.nobukti DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN curs1;
read_loop: LOOP
FETCH curs1 INTO _IDPro;
IF done THEN
 LEAVE read_loop;
END IF;
  
  -- 1:cari idpromosi yang msh berlaku 
  IF (_IDPro IS NOT NULL) THEN
        -- 2:find variasi kategori disc	
	SELECT typebarang,typediskon,mix,minjumlahitem INTO _titem,_tdskn,_mix,_minjumlahitem 
	FROM pdpro_header WHERE nobukti=_IDPro;
	SELECT tc.hari INTO _custterm FROM soheader sp,termcustomer tc 
	WHERE sp.kdterm=tc.kdterm AND sp.noso=_nobukti AND sp.cabang=_cabang LIMIT 1;
        SET _SEQ=1;
        SET _IsMinJumlahOK=0;
        IF ((_minjumlahitem<=_minjumlahitemtrans) OR (_minjumlahitem=0)) THEN
          SET _IsMinJumlahOK=1;        
        END IF;
        SET _disc1=0;
        SET _disc2=0;
        SET _disc3=0;
        SET _nilai=0;
        SET _qtybns=0;
        SET _perqty=0;
        SET _kdbrgbns='';
        SET _level=0;
        SET _Ada=0;SET _AdaDist=0;
        SET _AdaDist1=0;
        
	IF ((_mix=1) AND (_IsMinJumlahOK=1)) THEN
	   SELECT COUNT(kdbrg) INTO _AdaDist FROM pdpro_barangdist WHERE nobukti=_IDPro; 
      -- find level
           CALL sp_select_geturut(_nobukti,_IDPro,_titem,_tdskn,_cabang,
           @disc1,@disc2,@disc3,@nilai,@qtybns,@perqty,@kdbrgbns,@level,@ftop,@ttop,@nilai2,@perqty1,@perqty2);
           SELECT @disc1,@disc2,@disc3,@nilai,@qtybns,@perqty,@kdbrgbns,@level,@ftop,@ttop,@nilai2,@perqty1,@perqty2 
           INTO _disc1,_disc2,_disc3,_nilai,_qtybns,_perqty,_kdbrgbns,_level,_ftop,_ttop,_nilai2,_perqty1,_perqty2;
	   IF (_AdaDist=0) THEN
		UPDATE fakturjualdetail a
		SET a.Disc2=IFNULL(_disc1,0),
		a.NDisc2=IFNULL(IF(_disc1>0,(((a.qty*a.hs)-a.NDisc1) * (_disc1/100)),
		IF(_perqty1>0,(ROUND(a.qty/_perqty1)*_nilai),_nilai)),0),
		a.Disc2a=IFNULL(_disc2,0),
		a.NDisc2a=IFNULL(IF(_disc2>0,(((a.qty*a.hs)-a.NDisc1-a.NDisc2) * (_disc2/100)),
		IF(_perqty2>0,(ROUND(a.qty/_perqty2)*_nilai2),_nilai2)),0),
		idDisc2=IF((_level>0),CONCAT(_IDPro,' L',_level),'')
		WHERE a.nobukti=_nobukti	  
		AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
			       AND (
				CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
 					( EXISTS(SELECT ref1.kdbrg FROM pdpro_barang ref1 
 					         WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro))  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				  ));
	   END IF;			  
           
           IF (_AdaDist>0) THEN
           SET _AdaDist=0;
           SELECT COUNT(kdbrg) INTO _AdaDist FROM fakturjualdetail WHERE nobukti=_nobukti
           AND kdbrg IN(SELECT ref.kdbrg FROM pdpro_barangdist ref WHERE ref.nobukti=_IDPro);
		IF (_AdaDist>0) THEN
			UPDATE fakturjualdetail a
			SET a.Disc2 =IFNULL(_disc1,0),
			a.NDisc2 =IFNULL(IF(_disc1>0,(((a.qty*a.hs)-a.NDisc1) * (_disc1/100)),
			IF(_perqty1>0,(ROUND(a.qty/_perqty1)*_nilai),_nilai)),0),
			a.Disc2a=IFNULL(_disc2,0),
			a.NDisc2a=IFNULL(IF(_disc2>0,(((a.qty*a.hs)-a.NDisc1-a.NDisc2) * (_disc2/100)),
			IF(_perqty2>0,(ROUND(a.qty/_perqty2)*_nilai2),_nilai2)),0),
			idDisc2=IF((_level>0),CONCAT(_IDPro,' L',_level),'')
			WHERE a.nobukti=_nobukti	  
			AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
				AND ((
				 CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
					( EXISTS
					( SELECT ref1.kdbrg FROM pdpro_barang ref1 WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro))  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				)
			       OR (SELECT CASE _AdaDist 
			         WHEN '1' THEN EXISTS(SELECT ref1.kdbrg FROM pdpro_barangdist ref1 
					              WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro)  
                                 END ))
                                 );
		ELSEIF (_AdaDist=0) THEN
			UPDATE fakturjualdetail a
			SET a.Disc2 =IFNULL(_disc1,0),
			a.NDisc2 =IFNULL(IF(_disc1>0,(((a.qty*a.hs)-a.NDisc1) * (_disc1/100)),
			IF(_perqty1>0,(ROUND(a.qty/_perqty1)*_nilai),_nilai)),0),
			a.Disc2a=IFNULL(_disc2,0),
			a.NDisc2a=IFNULL(IF(_disc2>0,(((a.qty*a.hs)-a.NDisc1-a.NDisc2) * (_disc2/100)),
			IF(_perqty2>0,(ROUND(a.qty/_perqty2)*_nilai2),_nilai2)),0),
			idDisc2=IF((_level>0),CONCAT(_IDPro,' L',_level),'')
			WHERE a.nobukti=_nobukti	  
			AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
				AND (
				 CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
					( EXISTS
					( SELECT ref1.kdbrg FROM pdpro_barang ref1 WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro))  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				))
                                 ;
		END IF;
           END IF;
           
           -- bonus
           IF ((_kdbrgbns<>'') AND (_qtybns>0)) THEN
		SET _NU=0;
		SET @iddisc2=CONCAT(_IDPro,'%');
		IF (_flag<>'M') THEN
		SELECT MAX(nourut) INTO _NU FROM fakturjualdetail WHERE nobukti=_NoBukti;
		INSERT INTO fakturjualdetail(nourut,kdbrg,kdbrg1,Model,NoBukti,Cabang,KdGudang,KdSlm,Hs,Hs1,Qty,
		Disc1,Disc1a,NDisc1,NDisc1a,Disc2,Disc2a,NDisc2,NDisc2a,Disc4,NDisc4,IDDisc1,IDDisc2,IDBonus)
		SELECT _NU,_kdbrgbns,_kdbrgbns,_kdbrgbns,_NoBukti,_Cabang,a.KdGudang,a.KdSlm,0 Hs,0 Hs1,
		IF(_perqty>0,(ROUND(SUM(a.qty)/_perqty)*_qtybns),_qtybns) AS qty,
		0 Disc1,0 Disc1a,0 NDisc1,0 NDisc1a,0 Disc2,0 Disc2a,0 NDisc2,0 NDisc2a,0 Disc4,0 NDisc4,
		'' IDDisc1,'' IDDisc2,CONCAT(_IDPro,' L',_level) AS IDBonus 
		FROM fakturjualdetail a WHERE a.nobukti=_NoBukti AND IDDisc2 LIKE @iddisc2 AND cabang=_cabang;
		END IF;
           END IF;
           
	END IF;
	IF ((_mix=2)) THEN
	   SELECT COUNT(kdbrg) INTO _AdaDist1 FROM pdpro_barangdist WHERE nobukti=_IDPro; 
      -- find level
           
           CALL sp_select_geturut1(_nobukti,_IDPro,_titem,_tdskn,_cabang,
           @disc1,@disc2,@disc3,@nilai,@qtybns,@perqty,@kdbrgbns,@level,@ftop,@ttop,@nilai2,@perqty1,@perqty2);
           SELECT @disc1,@disc2,@disc3,@nilai,@qtybns,@perqty,@kdbrgbns,@level,@ftop,@ttop,@nilai2,@perqty1,@perqty2 
           INTO _disc1,_disc2,_disc3,_nilai,_qtybns,_perqty,_kdbrgbns,_level,_ftop,_ttop,_nilai2,_perqty1,_perqty2;
	   IF (_AdaDist1=0) THEN
		UPDATE fakturjualdetail a
		SET a.Disc1=IFNULL(_disc1,0),
		a.NDisc1=IFNULL(IF(_disc1>0,(((a.qty*a.hs)) * (_disc1/100)),
		IF(_perqty1>0,((a.qty/_perqty1)*_nilai),_nilai)),0),
		a.Disc1a=IFNULL(_disc2,0),
		a.NDisc1a=IFNULL(IF(_disc2>0,(((a.qty*a.hs)-a.NDisc1) * (_disc2/100)),
		IF(_perqty2>0,((a.qty/_perqty2)*_nilai2),_nilai2)),0),
		idDisc1=IF((_level>0),CONCAT(_IDPro,' L',_level),'')
		WHERE a.nobukti=_nobukti	  
		AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
			       AND (
				CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
 					( EXISTS(SELECT ref1.kdbrg FROM pdpro_barang ref1 
 					         WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro))  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				  ));
	   END IF;			  
           
           IF (_AdaDist1>0) THEN
           SET _AdaDist1=0;
           SELECT COUNT(kdbrg) INTO _AdaDist1 FROM fakturjualdetail WHERE nobukti=_nobukti
           AND kdbrg IN(SELECT ref.kdbrg FROM pdpro_barangdist ref WHERE ref.nobukti=_IDPro);
		IF (_AdaDist1>0) THEN
			UPDATE fakturjualdetail a
			SET a.Disc1 =IFNULL(_disc1,0),
			a.NDisc1 =IFNULL(IF(_disc1>0,(((a.qty*a.hs)) * (_disc1/100)),
			IF(_perqty1>0,((a.qty/_perqty1)*_nilai),_nilai)),0),
			a.Disc1a=IFNULL(_disc2,0),
			a.NDisc1a=IFNULL(IF(_disc2>0,(((a.qty*a.hs)-a.NDisc1) * (_disc2/100)),
			IF(_perqty2>0,((a.qty/_perqty2)*_nilai2),_nilai2)),0),
			idDisc1=IF((_level>0),CONCAT(_IDPro,' L',_level),'')
			WHERE a.nobukti=_nobukti	  
			AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
				AND ((
				 CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
					( EXISTS
					( SELECT ref1.kdbrg FROM pdpro_barang ref1 WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro))  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				)
			       OR (SELECT CASE _AdaDist1 
			         WHEN '1' THEN EXISTS(SELECT ref1.kdbrg FROM pdpro_barangdist ref1 
					              WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro)  
                                 END ))
                                 );
		ELSEIF (_AdaDist1=0) THEN
			UPDATE fakturjualdetail a
			SET a.Disc1 =IFNULL(_disc1,0),
			a.NDisc1 =IFNULL(IF(_disc1>0,(((a.qty*a.hs)) * (_disc1/100)),
			IF(_perqty1>0,((a.qty/_perqty1)*_nilai),_nilai)),0),
			a.Disc1a=IFNULL(_disc2,0),
			a.NDisc1a=IFNULL(IF(_disc2>0,(((a.qty*a.hs)-a.NDisc1) * (_disc2/100)),
			IF(_perqty2>0,((a.qty/_perqty2)*_nilai2),_nilai2)),0),
			idDisc1=IF((_level>0),CONCAT(_IDPro,' L',_level),'')
			WHERE a.nobukti=_nobukti	  
			AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
				AND (
				 CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
					( EXISTS
					( SELECT ref1.kdbrg FROM pdpro_barang ref1 WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro))  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				))
                                 ;
		END IF;
           END IF;
                      
	END IF;
	
	IF ((_mix=0) AND (_IsMinJumlahOK=1)) THEN
	   IF (_tdskn='0') THEN
 	   UPDATE fakturjualdetail a
		SET a.Disc2  =IFNULL((SELECT Disc1 FROM pdpro_detail WHERE nobukti=_IDPro
			   AND ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) 
			   -- or   ((a.qty*a.hs)>=fromnilai and  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			   AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0), 
			   
		    a.NDisc2 =IF(a.Disc2>0,IFNULL((((a.qty*a.hs)-a.ndisc1) * (a.Disc2/100)),0),
		
		IF(IFNULL((SELECT perqty1 FROM pdpro_detail WHERE nobukti=_IDPro
		
			AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			   -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0) 
			
		    >0,ROUND(a.qty/IFNULL((SELECT perqty1 FROM pdpro_detail WHERE nobukti=_IDPro
			AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			  -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
		        )*
		        IFNULL((SELECT nilai FROM pdpro_detail WHERE nobukti=_IDPro
			AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			   -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			,
			IFNULL((SELECT nilai FROM pdpro_detail WHERE nobukti=_IDPro
			AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			  -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			)),
		a.Disc2a  =IFNULL((SELECT disc2 FROM pdpro_detail WHERE nobukti=_IDPro
			   AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			  -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			   AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0), 
		a.NDisc2a =IF(a.Disc2a>0,IFNULL((((a.qty*a.hs)-a.ndisc1-a.nDisc2) * (a.Disc2a/100)),0),
		IF(IFNULL((SELECT perqty2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			   -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0) 
		    >0,ROUND(a.qty/IFNULL((SELECT perqty2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			   -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
		        )*
		        IFNULL((SELECT nilai2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			   -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			,
			IFNULL((SELECT nilai2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			   -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			)),
		a.idDisc2= IFNULL((SELECT CONCAT(nobukti,' L',nourut) FROM pdpro_detail WHERE nobukti=_IDPro
			   AND  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0)) -- OR 
			  -- ((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			   AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),'') 
	   WHERE a.nobukti=_nobukti	  
	   AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
			       AND (
				CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1 ) IN
					( SELECT ref.kdbrg FROM pdpro_barang ref WHERE ref.nobukti=_IDPro )  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				  ));
	   	
	   ELSEIF (_tdskn='1') THEN
  	   UPDATE fakturjualdetail a
		SET a.Disc2  =IFNULL((SELECT Disc1 FROM pdpro_detail WHERE nobukti=_IDPro
			   AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			   AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0), 
		a.NDisc2 =IF(a.Disc2>0,IFNULL((((a.qty*a.hs)-a.NDisc1) * (a.Disc2/100)),0),
		IF(IFNULL((SELECT perqty1 FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0) 
		    >0,ROUND(a.qty/IFNULL((SELECT perqty1 FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
		        )*
		        IFNULL((SELECT nilai FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			,
			IFNULL((SELECT nilai FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			)),
		a.Disc2a  =IFNULL((SELECT disc2 FROM pdpro_detail WHERE nobukti=_IDPro
			   AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			   AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0), 
		a.NDisc2a =IF(a.Disc2a>0,IFNULL((((a.qty*a.hs)-a.NDisc1-a.NDisc2) * (a.Disc2a/100)),0),
		IF(IFNULL((SELECT perqty2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0) 
		    >0,ROUND(a.qty/IFNULL((SELECT perqty2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
		        )*
		        IFNULL((SELECT nilai2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			,
			IFNULL((SELECT nilai2 FROM pdpro_detail WHERE nobukti=_IDPro
			AND -- ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),0)
			)),
		a.idDisc2=IFNULL((SELECT CONCAT(nobukti,' L',nourut) FROM pdpro_detail WHERE nobukti=_IDPro
			   AND --  ((a.qty>=fromqty AND a.qty<=toqty) OR (a.qty>=fromqty AND toqty=0) OR 
			   (((a.qty*a.hs)>=fromnilai AND  (a.qty*a.hs)<=tonilai) OR ((a.qty*a.hs)>=fromnilai AND tonilai=0)) 
			   AND ((fromtop<=_custterm AND totop>=_custterm) OR (fromtop<=_custterm AND totop=-1) OR (fromtop<=-1 AND totop=-1)) LIMIT 1),'') 
	   WHERE a.nobukti=_nobukti	  
	   AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
			       AND (
				CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1 ) IN
					( SELECT ref.kdbrg FROM pdpro_barang ref WHERE ref.nobukti=_IDPro )  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				  ));
	   ELSEIF (_tdskn='2') THEN
		-- find level
		CALL sp_select_geturut2(_nobukti,_IDPro,_titem,_tdskn,_cabang,
		@disc1,@disc2,@disc3,@nilai,@qtybns,@perqty,@kdbrgbns,@level,@ftop,@ttop,@nilai2,@perqty1,@perqty2);
		SELECT @disc1,@disc2,@disc3,@nilai,@qtybns,@perqty,@kdbrgbns,@level,@ftop,@ttop,@nilai2,@perqty1,@perqty2 
		INTO _disc1,_disc2,_disc3,_nilai,_qtybns,_perqty,_kdbrgbns,_level,_ftop,_ttop,_nilai2,_perqty1,_perqty2;
	   
		UPDATE fakturjualdetail a
		SET a.Disc2=IFNULL(_disc1,0),
		a.NDisc2=IFNULL(IF(_disc1>0,(((a.qty*a.hs)-a.NDisc1) * (_disc1/100)),
		IF(_perqty1>0,(ROUND(a.qty/_perqty1)*_nilai),_nilai)),0),
		a.Disc2a=IFNULL(_disc2,0),
		a.NDisc2a=IFNULL(IF(_disc2>0,(((a.qty*a.hs)-a.NDisc1-a.NDisc2) * (_disc2/100)),
		IF(_perqty2>0,(ROUND(a.qty/_perqty2)*_nilai2),_nilai2)),0),
		idDisc2=IF((_level>0),CONCAT(_IDPro,' L',_level),'')
		WHERE a.nobukti=_nobukti	  
		AND a.kdbrg IN(SELECT b.kdbrg FROM barang b WHERE b.kdsupplier=_supp
			       AND (
				CASE _titem  
					WHEN "1" THEN  
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg LIMIT 1) IN
					( SELECT ref.kdbrg  FROM barang ref  WHERE ref.kdbrg=b.kdbrg)   
					WHEN "2" THEN   
					( SELECT ref.GroupBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.GroupBrg FROM pdpro_groupbarang pdf WHERE pdf.nobukti=_IDPro )  
					WHEN "3" THEN   
 					( EXISTS(SELECT ref1.kdbrg FROM pdpro_barang ref1 
 					         WHERE ref1.kdbrg=b.kdbrg AND ref1.nobukti=_IDPro))  
					WHEN "4" THEN   
					( SELECT ref.JenisBrg FROM barang ref WHERE ref.kdbrg=b.kdbrg ) IN  
					( SELECT pdf.JenisBrg FROM pdpro_jenisbarang pdf WHERE pdf.nobukti=_IDPro )  
				END    
				  ));	   
	   END IF;	   		  
           -- bonus
           IF (_flag<>'M') THEN
           SET _NU=0;
           SET @iddisc2=CONCAT(_IDPro,'%') ;
           SELECT MAX(nourut) INTO _NU FROM fakturjualdetail WHERE nobukti=_NoBukti;
           INSERT INTO fakturjualdetail(NoUrut,kdbrg,kdbrg1,Model,NoBukti,Cabang,KdGudang,KdSlm,Hs,Hs1,Qty,
           Disc1,Disc1a,NDisc1,NDisc1a,Disc2,Disc2a,NDisc2,NDisc2a,Disc4,NDisc4,IDDisc1,IDDisc2,IDBonus)
           SELECT _NU,pro.kdbrg,pro.kdbrg,pro.kdbrg,_NoBukti,_Cabang,a.KdGudang,a.KdSlm,0 Hs,0 Hs1,
           IF(pro.perqty>0,(ROUND(a.qty/pro.perqty)*pro.qtybns),pro.qtybns) AS qty,
           0 Disc1,0 Disc1a,0 NDisc1,0 NDisc1a,0 Disc2,0 Disc2a,0 NDisc2,0 NDisc2a,0 Disc4,0 NDisc4,
           '' IDDisc1,'' IDDisc2,CONCAT(_IDPro,' L',pro.nourut) AS IDBonus 
           FROM fakturjualdetail a
           LEFT JOIN pdpro_detail pro ON pro.nobukti=_IDPro
           WHERE a.nobukti=_NoBukti AND cabang=_cabang AND IDDisc2 LIKE @iddisc2
	   AND ((a.qty>=pro.fromqty AND a.qty<=pro.toqty) OR (a.qty>=pro.fromqty AND pro.toqty=0)) 			  
           AND pro.qtybns>0 LIMIT 1;
           END IF;
	END IF;	
  END IF;
END LOOP;
CLOSE curs1;  
END$$

DELIMITER ;

Batal

⬅ application 19 item

Nama	Tipe	Ukuran	Diubah	Perm	Aksi
🐘 Dashboard_model.php	php	344 B	08/08/22 11:10	0644	✏️ ⬇
🐘 Grafik_model.php	php	2.9 KB	08/08/22 11:10	0644	✏️ ⬇
🐘 Gudang_model.php	php	28.3 KB	19/11/23 13:47	0644	✏️ ⬇
🐘 Kasir_model.php	php	116.7 KB	10/09/25 09:38	0644	✏️ ⬇
📄 Kasir_model.php.bak	bak	8.6 KB	08/08/22 11:10	0644	✏️ ⬇
🐘 Kasir_model1408.php	php	68.2 KB	14/08/23 09:48	0644	✏️ ⬇
🐘 Kasir_model2005.php	php	45.4 KB	08/08/22 11:10	0644	✏️ ⬇
🐘 Kasir_model2501.php	php	60.6 KB	24/01/23 10:42	0644	✏️ ⬇
🐘 Kasir_model2707.php	php	55.1 KB	08/08/22 11:10	0644	✏️ ⬇
🐘 Kasir_model28.php	php	20.5 KB	08/08/22 11:10	0644	✏️ ⬇
🐘 Kasir_model_130323.php	php	65.8 KB	13/03/23 08:25	0644	✏️ ⬇
🐘 Laporan_model.php	php	72.3 KB	14/01/26 08:01	0644	✏️ ⬇
🐘 Login_model.php	php	649 B	01/02/23 08:00	0644	✏️ ⬇
🐘 Manajer_model.php	php	3.5 KB	28/02/24 08:32	0644	✏️ ⬇
📄 Manajer_model.php.bak	bak	1.9 KB	08/08/22 11:10	0644	✏️ ⬇
🌐 index.html	html	131 B	08/08/22 11:10	0644	✏️ ⬇
🐘 kasir_modelold.php	php	97.9 KB	29/07/25 08:29	0644	✏️ ⬇
🐘 promo.php	php	28.7 KB	08/08/22 11:10	0644	✏️ ⬇
📄 promo.php.bak	bak	28.7 KB	08/08/22 11:10	0644	✏️ ⬇

✏️ Rename

🔑 Ubah Permission