('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:45','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:46','Update setting level user (level=01)','link','154.26.130.93',''),('2023-06-19','11:38:54','Login','link - link','154.26.130.93',''),('2023-06-19','11:39:48','Login','link - link','154.26.130.93',''),('2023-06-23','10:07:34','Login','link - link','154.26.130.93',''),('2023-08-28','17:05:02','Login','admin - admin','154.26.130.93',''),('2023-08-28','17:06:22','Login','admin - admin','154.26.130.93',''),('2023-08-28','17:15:42','Login','admin - admin','154.26.130.93',''),('2023-12-11','13:49:41','Login','link - link','154.26.130.93',''),('2023-12-11','14:02:14','Login','link - link','154.26.130.93',''),('2023-12-11','14:03:38','Login','link - link','154.26.130.93',''),('2023-12-11','14:14:14','Login','link - link','154.26.130.93',''),('2023-12-11','14:15:25','Login','link - link','154.26.130.93',''),('2024-04-15','15:44:10','Login','link - link','154.26.130.93',''),('2024-04-15','15:46:10','Login','link - link','154.26.130.93',''),('2024-04-15','15:52:07','Login','link - link','154.26.130.93',''),('2024-04-15','15:56:30','Login','link - link','154.26.130.93',''),('2024-04-15','15:59:25','Login','link - link','154.26.130.93',''),('2024-04-15','16:03:49','Login','link - link','154.26.130.93',''),('2024-04-15','16:10:10','Login','link - link','154.26.130.93',''),('2024-11-05','09:05:08','Login','link - link','154.26.136.5','');

--
-- Table structure for table `v_stockbrg`
--

CREATE TABLE `v_stockbrg` (
  `KodeBrg` varchar(50) default NULL,
  `Namabrg` varchar(200) default NULL,
  `StockAkhir` decimal(10,0) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `v_stockbrg`
--


--
-- Dumping routines for database 'dbcvdanish'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `BuatNo` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`client`@`%`*/ /*!50003 FUNCTION `BuatNo`(sUser varchar(1),Nama Varchar(20),Codes varchar(2),Tgl Varchar(10),Trans varchar(2),NoBaru varchar(11)) RETURNS char(11) CHARSET latin1
BEGIN
  DECLARE nUser Varchar(1) DEFAULT 0;
  DECLARE yy Varchar(2) DEFAULT 0;
  DECLARE mm Varchar(2) DEFAULT 0;
  DECLARE dd Varchar(2) DEFAULT 0;
  DECLARE NoInt Integer DEFAULT 0;
  DECLARE NoStr Varchar(11) DEFAULT 0;
  DECLARE Nomer Varchar(11) DEFAULT 0;
  DECLARE thn Varchar(6) DEFAULT 0;
  DECLARE TipeNo Integer DEFAULT 0;
  set nUser = Substring(sUser,1,1); 
  Set yy=Substring(tgl,3,2);
  Set mm=Substring(tgl,6,2);
  Set dd=Substring(tgl,9,2);
  Select ifnull(nilainum,1) into TipeNo from tm_setupall where groups='Nomor' and subgroups=Codes;
  If (TipeNo=0) or (TipeNo=1) then
     select if(ifnull(substring(nilaichar,3,6),0)='',0,ifnull(substring(nilaichar,3,6),0)),ifnull(nilaichar,'00') into NoInt,Nomer from tm_setupall where groups='Nomor' and subgroups=Codes;
     set thn=substring(nomer,1,2);
     if thn=yy then
        set NoInt=NoInt+1;
        set NoStr=NoInt;
        set NoBaru=concat(yy,REPEAT('0',6-length(NoStr)),NoStr);
     else
        case upper(Nama)
             when upper('Tr_Beli') then select ifnull(max(substring(noBeli,3,6)),0) into NoInt from TR_Beli_m where left(NoBeli,2)=yy and transBeli=Trans;
             when upper('Tr_Jual') then select ifnull(max(substring(noJual,3,6)),0) into NoInt from TR_Jual_m where left(NoJual,2)=yy and TransJual=Trans;
             when upper('Tr_JualR') then select ifnull(max(substring(noJualR,3,6)),0) into NoInt from TR_JualR_m where left(NoJualR,2)=yy and TransRetail=Trans;
             when upper('Tr_JualRakitan') then select ifnull(max(substring(noJual,3,6)),0) into NoInt from TR_JualRakitan_m where left(NoJual,2)=yy and TransJual=Trans;
             when upper('Tr_BayarBeli') then select ifnull(max(substring(noBayarB,3,6)),0) into NoInt from TR_BayarBeli_m where left(NoBayarB,2)=yy;
             when upper('Tr_BayarJual') then select ifnull(max(substring(noBayarJ,3,6)),0) into NoInt from TR_BayarJual_m where left(NoBayarJ,2)=yy;
             when upper('Tr_BayarJualRakitan') then select ifnull(max(substring(noBayarJ,3,6)),0) into NoInt from TR_BayarJualRakitan_m where left(NoBayarJ,2)=yy;
             when upper('Tr_RetBeli') then select ifnull(max(substring(noRetB,3,6)),0) into NoInt from TR_RetBeli_m where left(NoRetB,2)=yy and TransRetB=Trans;
             when upper('Tr_RetJual') then select ifnull(max(substring(noRetJ,3,6)),0) into NoInt from TR_RetJual_m where left(NoRetJ,2)=yy and TransRetJ=Trans;
             when upper('Tr_RetJualR') then select ifnull(max(substring(noRetJ,3,6)),0) into NoInt from TR_RetJualR_m where left(NoRetJ,2)=yy and TransRetJ=Trans;
             when upper('Tr_RetJualRakitan') then select ifnull(max(substring(noRetJ,3,6)),0) into NoInt from TR_RetJualRakitan_m where left(NoRetJ,2)=yy and TransRetJ=Trans;
             when upper('Tr_PoJual') then select ifnull(max(substring(nopo,3,6)),0) into NoInt from TR_PoJual_m where left(NoPo,2)=yy;
             when upper('Tr_PoJualRakitan') then select ifnull(max(substring(nopo,3,6)),0) into NoInt from TR_PoJualRakitan_m where left(NoPo,2)=yy;
             when upper('Tr_PIndahGd') then select ifnull(max(substring(noPindahGd,3,6)),0) into NoInt from Tr_PindahGd_M where left(noPindahGd,2)=yy;
             when upper('Tr_BuangGd') then select ifnull(max(substring(noBuangGd,3,6)),0) into NoInt from Tr_BuangGd_M where left(noBuangGd,2)=yy;
             when upper('Close') then select ifnull(max(substring(noClose,3,6)),0) into NoInt from TR_JualR_m where left(NoJualR,2)=yy and TransRetail=Trans;
        end case;
        if NoInt=0 then
           set NoBaru = concat(yy,'000001');
        else
           set noInt = noInt + 1;
           set noStr = noInt;
           set NoBaru= concat(yy,repeat('0',6-length(NoStr)),NoStr);
        end if;
     end if;
  end if;
  If (TipeNo=2) then
     select if(ifnull(substring(nilaichar,5,4),0)='',0,ifnull(substring(nilaichar,5,4),0)),ifnull(nilaichar,'00') into NoInt,Nomer from tm_setupall where groups='Nomor' and subgroups=Codes;
     set thn=substring(nomer,1,4);
     if thn=concat(yy,mm) then
        set NoInt=NoInt+1;
        set NoStr=NoInt;
        set NoBaru=concat(yy,mm,REPEAT('0',4-length(NoStr)),NoStr);
     else
        case upper(Nama)
             when upper('Tr_Beli') then select ifnull(max(substring(noBeli,5,4)),0) into NoInt from TR_Beli_m where left(NoBeli,4)=concat(yy,mm) and transBeli=Trans;
             when upper('Tr_Jual') then select ifnull(max(substring(noJual,5,4)),0) into NoInt from TR_Jual_m where left(NoJual,4)=concat(yy,mm) and TransJual=Trans;
             when upper('Tr_JualR') then select ifnull(max(substring(noJualR,5,4)),0) into NoInt from TR_JualR_m where left(NoJualR,4)=concat(yy,mm) and TransRetail=Trans;
             when upper('Tr_JualRakitan') then select ifnull(max(substring(noJual,5,4)),0) into NoInt from TR_JualRakitan_m where left(NoJual,4)=concat(yy,mm) and TransJual=Trans;
             when upper('Tr_BayarBeli') then select ifnull(max(substring(noBayarB,5,4)),0) into NoInt from TR_BayarBeli_m where left(NoBayarB,4)=concat(yy,mm);
             when upper('Tr_BayarJual') then select ifnull(max(substring(noBayarJ,5,4)),0) into NoInt from TR_BayarJual_m where left(NoBayarJ,4)=concat(yy,mm);
             when upper('Tr_BayarJualRakitan') then select ifnull(max(substring(noBayarJ,5,4)),0) into NoInt from TR_BayarJualRakitan_m where left(NoBayarJ,4)=concat(yy,mm);
             when upper('Tr_RetBeli') then select ifnull(max(substring(noRetB,5,4)),0) into NoInt from TR_RetBeli_m where left(NoRetB,4)=concat(yy,mm) and TransRetB=Trans;
             when upper('Tr_RetJual') then select ifnull(max(substring(noRetJ,5,4)),0) into NoInt from TR_RetJual_m where left(NoRetJ,4)=concat(yy,mm) and TransRetJ=Trans;
             when upper('Tr_RetJualR') then select ifnull(max(substring(noRetJ,5,4)),0) into NoInt from TR_RetJualR_m where left(NoRetJ,4)=concat(yy,mm) and TransRetJ=Trans;
             when upper('Tr_RetJualRakitan') then select ifnull(max(substring(noRetJ,5,4)),0) into NoInt from TR_RetJualRakitan_m where left(NoRetJ,4)=concat(yy,mm) and TransRetJ=Trans;
             when upper('Tr_PoJual') then select ifnull(max(substring(nopo,5,4)),0) into NoInt from TR_PoJual_m where left(NoPo,4)=concat(yy,mm);
             when upper('Tr_PoJualRakitan') then select ifnull(max(substring(nopo,5,4)),0) into NoInt from TR_PoJualRakitan_m where left(NoPo,4)=concat(yy,mm);
             when upper('Tr_PIndahGd') then select ifnull(max(substring(noPindahGd,5,4)),0) into NoInt from Tr_PindahGd_M where left(noPindahGd,4)=concat(yy,mm);
             when upper('Tr_BuangGd') then select ifnull(max(substring(noBuangGd,5,4)),0) into NoInt from Tr_BuangGd_M where left(noBuangGd,4)=concat(yy,mm);
             when upper('Close') then select ifnull(max(substring(noClose,5,4)),0) into NoInt from TR_JualR_m where left(NoJualR,4)=concat(yy,mm) and TransRetail=Trans;
       end case;
        if NoInt=0 then
           set NoBaru = concat(yy,mm,'0001');
        else
           set noInt = noInt + 1;
           set noStr = noInt;
           set NoBaru= concat(yy,mm,repeat('0',4-length(NoStr)),NoStr);
        end if;
     end if;
  end if;
 If (TipeNo=3) then
     select if(ifnull(substring(nilaichar,8,3),0)='',0,ifnull(substring(nilaichar,8,3),0)),ifnull(nilaichar,'00') into NoInt,Nomer from tm_setupall where groups='Nomor' and subgroups=Codes;
     set thn=substring(nomer,2,6);
     if thn=concat(yy,mm,dd) then
        set NoInt=NoInt+1;
        set NoStr=NoInt;
        set NoBaru=concat(nUser,yy,mm,dd,REPEAT('0',3-length(NoStr)),NoStr);
     else
        case upper(Nama)
             when upper('Tr_Beli') then select ifnull(max(substring(noBeli,7,3)),0) into NoInt from TR_Beli_m where left(NoBeli,6)=concat(yy,mm,dd) and transBeli=Trans;
             when upper('Tr_Jual') then select ifnull(max(substring(noJual,8,3)),0) into NoInt from TR_Jual_m where substring(NoJual,2,6)=concat(yy,mm,dd) and TransJual=Trans;
             when upper('Tr_JualR') then select ifnull(max(substring(noJualR,8,3)),0) into NoInt from TR_JualR_m where substring(NoJualR,2,6)=concat(yy,mm,dd) and TransRetail=Trans;
             when upper('Tr_JualRakitan') then select ifnull(max(substring(noJual,7,3)),0) into NoInt from TR_JualRakitan_m where left(NoJual,6)=concat(yy,mm,dd) and TransJual=Trans;
             when upper('Tr_BayarBeli') then select ifnull(max(substring(noBayarB,7,3)),0) into NoInt from TR_BayarBeli_m where left(NoBayarB,6)=concat(yy,mm,dd);
             when upper('Tr_BayarJual') then select ifnull(max(substring(noBayarJ,7,3)),0) into NoInt from TR_BayarJual_m where left(NoBayarJ,6)=concat(yy,mm,dd);
             when upper('Tr_BayarJualRakitan') then select ifnull(max(substring(noBayarJ,7,3)),0) into NoInt from TR_BayarJualRakitan_m where left(NoBayarJ,6)=concat(yy,mm,dd);
             when upper('Tr_RetBeli') then select ifnull(max(substring(noRetB,7,3)),0) into NoInt from TR_RetBeli_m where left(NoRetB,6)=concat(yy,mm,dd) and TransRetB=Trans;
             when upper('Tr_RetJual') then select ifnull(max(substring(noRetJ,8,3)),0) into NoInt from TR_RetJual_m where substring(NoRetJ,2,6)=concat(yy,mm,dd) and TransRetJ=Trans;
             when upper('Tr_RetJualR') then select ifnull(max(substring(noRetJ,7,3)),0) into NoInt from TR_RetJualR_m where left(NoRetJ,6)=concat(yy,mm,dd) and TransRetJ=Trans;
             when upper('Tr_RetJualRakitan') then select ifnull(max(substring(noRetJ,7,3)),0) into NoInt from TR_RetJualRakitan_m where left(NoRetJ,6)=concat(yy,mm,dd) and TransRetJ=Trans;
             when upper('Tr_PoJual') then select ifnull(max(substring(nopo,7,3)),0) into NoInt from TR_PoJual_m where left(NoPo,6)=concat(yy,mm,dd);
             when upper('Tr_PoJualRakitan') then select ifnull(max(substring(nopo,7,3)),0) into NoInt from TR_PoJualRakitan_m where left(NoPo,6)=concat(yy,mm,dd);
             when upper('Tr_PIndahGd') then select ifnull(max(substring(noPindahGd,7,3)),0) into NoInt from Tr_PindahGd_M where left(noPindahGd,6)=concat(yy,mm,dd);
             when upper('Tr_BuangGd') then select ifnull(max(substring(noBuangGd,8,3)),0) into NoInt from Tr_BuangGd_M where SUBSTRING(noBuangGd,2,6)=concat(yy,mm,dd);
             when upper('Close') then select ifnull(max(substring(noClose,7,3)),0) into NoInt from TR_JualR_m where left(NoJualR,6)=concat(yy,mm,dd) and TransRetail=Trans;
      end case;
        if NoInt=0 then
           set NoBaru = concat(nUser,yy,mm,dd,'001');
        else
           set noInt = noInt + 1;
           set noStr = noInt;
           set NoBaru= concat(nUser,yy,mm,dd,repeat('0',3-length(NoStr)),NoStr);
        end if;
     end if;
  end if;
  update tm_setupall set nilaichar=NoBaru where groups='Nomor' and subgroups=codes;
  Return NoBaru;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `CekHppRetJual` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `CekHppRetJual`(sNoRetJ VARCHAR(13)) RETURNS decimal(15,2)
BEGIN
  DECLARE sTotHPP, sHPP, sJml, sKonv DECIMAL(15,2);
	DECLARE sKodeBrg,sKodeCust VARCHAR(30);
	DECLARE finished INTEGER DEFAULT 0;
	DEClARE curBrg
		CURSOR FOR 
			SELECT x.* FROM (SELECT KodeBrg,Jml,Konv FROM tr_retjual_d WHERE NoRetJ = sNoRetJ) x;
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
	SET sTotHPP = 0;
	SELECT KodeCust INTO sKodeCust FROM tr_retjual_m WHERE NoRetJ=sNoRetJ;
	OPEN curBrg;
	getBrg: LOOP
		FETCH curBrg INTO sKodeBrg, sJml, sKonv;
		IF finished = 1 THEN 
			LEAVE getBrg;
		END IF;
		
		SELECT d.HargaAvg/d.Konv INTO sHPP FROM tr_jual_d d JOIN tr_jual_m m ON (d.NoJual=m.NoJual)
			WHERE m.KodeCust=sKodeCust AND d.KodeBrg=sKodeBrg ORDER BY m.TglJual DESC,m.NoJual DESC LIMIT 0,1;
		SET sTotHPP = sTotHPP + (sHpp * sJml * sKonv);
	END LOOP getBrg;
	CLOSE curBrg;
	RETURN sTotHPP;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `GenAnggota_id_tm_anggota` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `GenAnggota_id_tm_anggota`() RETURNS char(5) CHARSET latin1
BEGIN
  DECLARE nomor INTEGER DEFAULT 0;
  DECLARE VPK char(5) DEFAULT 0;
  SELECT IFNULL(MAX(right(Anggota_id,5)), 0)+1 INTO nomor FROM tm_anggota;
  SET vpk=INSERT('00000',6-LENGTH(nomor), length(nomor), nomor);
  RETURN concat(vPK);
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `GenKodeBrg` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`link`@`%`*/ /*!50003 FUNCTION `GenKodeBrg`(NoBk char(10)) RETURNS char(13) CHARSET latin1
BEGIN
  DECLARE nomor INTEGER DEFAULT 0;
  DECLARE VPK char(10) DEFAULT 0;
  SELECT IFNULL(MAX(right(kodebrg,1)), 0)+1 INTO nomor FROM tm_barang where left(kodebrg,length(nobk))=NoBk;
  SET vpk=INSERT('00000000',9-LENGTH(nomor), length(nomor), nomor);
  RETURN upper(concat(NoBk,vPK));
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `GenKodeCust` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `GenKodeCust`(NoBk char(10)) RETURNS char(13) CHARSET latin1
BEGIN
  DECLARE nomor INTEGER DEFAULT 0;
  DECLARE VPK char(10) DEFAULT 0;
  SELECT IFNULL(MAX(right(kodecust,1)), 0)+1 INTO nomor FROM tm_customer where left(kodecust,length(nobk))=NoBk;
  SET vpk=INSERT('0000000',8-LENGTH(nomor), length(nomor), nomor);
  RETURN upper( concat(NoBk,vPK));
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `GenKodeSupp` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `GenKodeSupp`(NoBk char(10)) RETURNS char(13) CHARSET latin1
BEGIN
  DECLARE nomor INTEGER DEFAULT 0;
  DECLARE VPK char(10) DEFAULT 0;
  SELECT IFNULL(MAX(right(kodesupp,1)), 0)+1 INTO nomor FROM tm_supplier where left(kodesupp,length(nobk))=NoBk;
  SET vpk=INSERT('0000000',8-LENGTH(nomor), length(nomor), nomor);
  RETURN upper( concat(NoBk,vPK));
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `GenNoBuktiJurnal` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `GenNoBuktiJurnal`(NoBk char(10)) RETURNS char(13) CHARSET latin1
BEGIN
  DECLARE nomor INTEGER DEFAULT 0;
  DECLARE VPK char(3) DEFAULT 0;
  SELECT IFNULL(MAX(right(no_bukti,3)), 0)+1 INTO nomor FROM tJurnal where left(no_bukti,length(nobk))=NoBk;
  SET vpk=INSERT('000',4-LENGTH(nomor), length(nomor), nomor);
  RETURN concat(NoBk,vPK);
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `CekPenjualanPerGolongan` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`link`@`%`*/ /*!50003 PROCEDURE `CekPenjualanPerGolongan`(sTglStart DATE, sTglEnd DATE, sFilterKodeGol VARCHAR(100), sFilterKodeBrg VARCHAR(20), sFilterKodeCust VARCHAR(20), sFilterNoJual VARCHAR(20))
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE sKodeGol,sNamaGol VARCHAR(50);
	DEClARE curGol
		CURSOR FOR 
			SELECT x.* FROM (SELECT DISTINCT(b.KodeGol),g.NamaGol FROM tr_jual_d d
				JOIN tm_barang b ON d.KodeBrg=b.KodeBrg JOIN tm_golongan g ON b.KodeGol=g.KodeGol JOIN tr_jual_m m ON d.NoJual=m.NoJual 
				WHERE m.TglJual >= sTglStart AND m.TglJual <= sTglEnd AND sFilterKodeGol AND sFilterKodeBrg AND sFilterKodeCust AND sFilterNoJual) x;
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
	TRUNCATE LapJualPerGolongan;
	IF (sFilterKodeGol <> "TRUE") OR (sFilterKodeBrg <> "TRUE") OR (sFilterKodeCust <> "TRUE") OR (sFilterNoJual <> "TRUE") THEN
		SELECT "Total All Penjualan" Golongan,m.NoJual, m.TglJual,m.KodeCust,m.NamaCust,d.*, ((d.Harga - d.Disc) * d.Jml) Nilai FROM tr_jual_d d
			JOIN tr_jual_m m ON d.NoJual=m.NoJual JOIN tm_barang b ON d.KodeBrg=b.KodeBrg
			WHERE m.TglJual >= sTglStart AND m.TglJual <= sTglEnd;
	END IF;
	OPEN curGol;
	getGol: LOOP
		FETCH curGol INTO sKodeGol,sNamaGol;
		IF finished = 1 THEN 
			LEAVE getGol;
		END IF;
		
	  SELECT CONCAT("Total ",sNamaGol) Golongan,m.NoJual, m.TglJual,m.KodeCust,m.NamaCust,d.*, ((d.Harga - d.Disc) * d.Jml) Nilai FROM tr_jual_d d
			JOIN tr_jual_m m ON d.NoJual=m.NoJual JOIN tm_barang b ON d.KodeBrg=b.KodeBrg 
			WHERE m.TglJual >= sTglStart AND m.TglJual <= sTglEnd and b.KodeGol=sKodeGol;
		
	END LOOP getGol;
	CLOSE curGol;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `CheckJurnalUlang_ReturGrosir` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `CheckJurnalUlang_ReturGrosir`(sTglStart DATE)
BEGIN
  DECLARE finished INTEGER DEFAULT 0;
	DECLARE sNoTrans VARCHAR(20);
	DEClARE curTrans
		CURSOR FOR 
			SELECT x.* FROM (SELECT NoRetJ FROM tr_retjual_m WHERE TglRetJ >= sTglStart) x;
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
	OPEN curTrans;
	getTrans: LOOP
		FETCH curTrans INTO sNoTrans;
		IF finished = 1 THEN 
			LEAVE getTrans;
		END IF;
		
		CALL SimpanJurnal_ReturGrosir(sNoTrans);
		
	END LOOP getTrans;
	CLOSE curTrans;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteJurnal_BayarBeli` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `DeleteJurnal_BayarBeli`(sNoBayar VARCHAR(15))
BEGIN
	DECLARE NoBukti,sNoBukti VARCHAR(30);
	
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="YB" and Nobukti_Rtl=sNoBayar LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	SET NoBukti = CONCAT("YB",sNoBayar);
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteJurnal_BayarJual` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `DeleteJurnal_BayarJual`(sNoBayar VARCHAR(15))
BEGIN
	DECLARE NoBukti,sNoBukti VARCHAR(30); 
	
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="YJ" and Nobukti_Rtl=sNoBayar LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	SET NoBukti = CONCAT("YJ",sNoBayar);
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteJurnal_JualGrosir` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `DeleteJurnal_JualGrosir`(sNoJual VARCHAR(15))
BEGIN
  DECLARE sNoBukti, NoBukti VARCHAR(30);
	select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="JT" and Nobukti_Rtl=sNoJual LIMIT 0,1;
  delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	SET NoBukti = CONCAT("JT",sNoJual);
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteJurnal_Pembelian` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `DeleteJurnal_Pembelian`(sNoBeli VARCHAR(15))
BEGIN
  DECLARE sNoBukti, NoBukti VARCHAR(30); 
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="BT" and Nobukti_Rtl=sNoBeli LIMIT 0,1;  
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	select CONCAT("BT",sNoBeli) INTO NoBukti;
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti; 
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteJurnal_ReturBeli` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `DeleteJurnal_ReturBeli`(sNoRetur VARCHAR(15))
BEGIN
  DECLARE sNoBukti, NoBukti VARCHAR(30);
	Select No_bukti INTO sNoBukti from tjurnal_m
		where Jns_jurnal="JK" and Sandi_Trans_Id="RB" and Nobukti_Rtl=sNoRetur LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	select CONCAT("RB",sNoRetur) INTO NoBukti;
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `DeleteJurnal_ReturGrosir` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `DeleteJurnal_ReturGrosir`(sNoRetur VARCHAR(15))
BEGIN
  DECLARE sNoBukti, NoBukti VARCHAR(30); 
	Select No_bukti INTO sNoBukti from tjurnal_m
		where Jns_jurnal="JK" and Sandi_Trans_Id="RJ" and Nobukti_Rtl=sNoRetur LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	select CONCAT("RJ",sNoRetur) INTO NoBukti;
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `HitungPosisiStock` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `HitungPosisiStock`(sKodeBrg VARCHAR(50),sKodeGD VARCHAR(10))
BEGIN
	DECLARE sStock DECIMAL(15,2); 
	DECLARE sTemp INTEGER;
	DECLARE sNamaBrg VARCHAR(150);
	SET sStock = (select 
			(IFNULL(sum(a.saldoawal),0) +
			IFNULL((select sum(IFNULL(b.jml,0) * IFNULL(b.Konv,1)) from tr_beli_d b where b.kodebrg=sKodeBrg AND b.KodeGD=sKodeGD),0) -
			IFNULL((select sum(IFNULL(c.jml,0) * IFNULL(c.Konv,1)) from tr_retbeli_d c where c.kodebrg=sKodeBrg AND c.KodeGD=sKodeGD),0) -
			IFNULL((select sum(IFNULL(d.jml,0) * IFNULL(d.Konv,1)) from tr_jualr_d d where d.kodebrg=sKodeBrg AND d.KodeGD=sKodeGD),0) +
			IFNULL((select sum(IFNULL(e.jml,0) * IFNULL(e.Konv,1)) from tr_retjualr_d e where e.kodebrg=sKodeBrg AND e.KodeGD=sKodeGD),0) -
			IFNULL((select sum(IFNULL(f.jml,0) * IFNULL(f.Konv,1)) from tr_jual_d f where f.kodebrg=sKodeBrg AND f.KodeGD=sKodeGD),0) +
			IFNULL((select sum(IFNULL(g.jml,0) * IFNULL(g.Konv,1)) from tr_retjual_d g where g.KodeBrg=sKodeBrg AND g.KodeGD=sKodeGD),0) -
			IFNULL((select sum(l.SelisihKurang) from tr_koreksistc_d l where l.kodebrg=sKodeBrg AND l.KodeGD=sKodeGD),0) +
			IFNULL((select sum(m.SelisihLebih) from tr_koreksistc_d m where m.kodebrg=sKodeBrg AND m.KodeGD=sKodeGD),0) 
			)
		from tb_saldoawal a where a.kodebrg=sKodeBrg AND a.KodeGD=sKodeGD);
	SELECT COUNT(*) INTO sTemp FROM tm_stockbrg WHERE KodeBrg=sKodeBrg AND KodeGD=sKodeGD;
	IF (sTemp IS NULL) OR (sTemp = 0) THEN
		SELECT NamaBrg INTO sNamaBrg FROM tm_barang WHERE KodeBrg=sKodeBrg;
		INSERT INTO tm_stockbrg (KodeBrg,NamaBrg,KodeGd,StockAwal,StockAkhir) VALUES (sKodeBrg,sNamaBrg,sKodeGD,0,sStock);
	ELSE
		UPDATE tm_stockbrg SET StockAkhir=sStock WHERE KodeBrg=sKodeBrg AND KodeGD=sKodeGD;
	END IF;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `LapBufferStock` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `LapBufferStock`(sTglStart DATE, sTglEnd DATE)
BEGIN 
	DECLARE finished, sIdx, sRow INTEGER DEFAULT 0;
	DECLARE sKodeBrg,sNamaBrg,sNamaGol,sSatuan,sSatuan1,sSatuan2,sSatuan3,sSatuanKarton,sKodeSupp,sNamaSupp VARCHAR(100);
	DECLARE sStock,sStockKarton,sJmlJual,sJmlKarton,sStockBuffer,sStockRata2Hari,sRumusBuffer,sKonv1,sKonv2,sKonv3,sHargaAVG,sAvgKarton,sJmlPoSblmnya,sHargaPO DECIMAL(15,2);
	DEClARE curBrg
		CURSOR FOR 
			SELECT x.* FROM (SELECT b.KodeBrg,REPLACE(b.NamaBrg,'"','`'),IFNULL(b.Konv1,0),IFNULL(b.Konv2,0),IFNULL(b.Konv3,0),g.NamaGol,s.StockAkhir,ROUND(HargaAVG,2),Satuan,Satuan1,Satuan2,Satuan3
				FROM tm_barang b JOIN tm_golongan g ON b.KodeGol=g.KodeGol JOIN tm_stockbrg s ON b.KodeBrg=s.KodeBrg WHERE b.Aktif="Y" ORDER BY b.NamaBrg) x;
	DECLARE CONTINUE HANDLER 
		FOR NOT FOUND SET finished = 1;
	TRUNCATE tr_lapbufferstock;
	SET sHargaAVG =0;
	SET sRumusBuffer=0;
	SELECT nilainum INTO sRumusBuffer FROM tm_setupall WHERE groups="Rumus" AND subgroups="BufferStock";
	SELECT COUNT(KodeBrg) INTO sRow FROM tm_barang WHERE Aktif="Y";
	OPEN curBrg;
	getBrg: LOOP
		FETCH curBrg INTO sKodeBrg,sNamaBrg,sKonv1,sKonv2,sKonv3,sNamaGol,sStock,sHargaAVG,sSatuan,sSatuan1,sSatuan2,sSatuan3;
			SET sIdx = sIdx + 1;
			IF sIdx > sRow THEN 
				LEAVE getBrg;
			END IF;
			
			
			
			SET sJmlKarton = 0;
			SET sJmlJual = 0;
			SET sAVGKarton = 0;
			SET sSatuanKarton = "";
			SET sStockKarton = 0;
			SELECT SUM(d.Jml *d.Konv) INTO sJmlJual FROM tr_jual_d d JOIN tr_jual_m m ON d.NoJual=m.NoJual WHERE m.TglJual >= sTglStart AND m.TglJual <= sTglEnd 
				AND d.KodeBrg=sKodeBrg GROUP BY d.KodeBrg;
			IF sJmlJual IS NULL THEN SET sJmlJual=0; END IF;
	
			IF (sKonv3 > 0) AND (sJmlJual > 0) THEN
				SET sStockKarton = ROUND(sStock / sKonv3,2);
				SET sSatuanKarton = sSatuan3;
				SET sAVGKarton = ROUND(sHargaAVG * sKonv3,2);
				SET sJmlKarton = ROUND(sJmlJual / sKonv3,2);
			ELSEIF (sKonv2 > 0) AND (sJmlJual > 0) THEN
				SET sStockKarton = ROUND(sStock / sKonv2,2);
				SET sSatuanKarton = sSatuan2;
				SET sAVGKarton = ROUND(sHargaAVG * sKonv2,2);
				SET sJmlKarton = ROUND(sJmlJual / sKonv2,2);
			ELSEIF (sKonv1 > 0) AND (sJmlJual > 0) THEN
				SET sStockKarton = ROUND(sStock / sKonv1,2);
				SET sSatuanKarton = sSatuan1;
				SET sAVGKarton = ROUND(sHargaAVG * sKonv1,2);
				SET sJmlKarton = ROUND(sJmlJual / sKonv1,2);
			ELSE
				SET sSatuanKarton = sSatuan;
				SET sAVGKarton = ROUND(sHargaAVG,2);
				SET sStockKarton = sStock;
				SET sJmlKarton = sJmlJual;
			END IF;
			SET sStockBuffer = 0;
			IF sJmlKarton > 0 THEN
				SET sStockBuffer = ROUND((sJmlKarton * sRumusBuffer / 100),2);
			END IF;
			IF sStockBuffer > sStockKarton THEN
				SELECT m.KodeSupp,s.NamaSupp,d.Jml * d.Konv, IFNULL(d.Harga,0) 
					INTO sKodeSupp,sNamaSupp,sJmlPoSblmnya,sHargaPO FROM tr_pobeli_d d 
					JOIN tr_pobeli_m m ON d.NoPO=m.NoPO JOIN tm_supplier s ON m.KodeSupp=s.KodeSupp
					WHERE d.KodeBrg=sKodebrg GROUP BY d.NoPO ORDER BY m.TglPO DESC LIMIT 0,1;
				IF sKodeSupp IS NULL THEN
					SET sKodeSupp = ""; SET sNamaSupp = ""; SET sJmlPoSblmnya = 0; SET sHargaPO = 0;
				END IF;
				INSERT INTO tr_LapBufferStock (KodeBrg,NamaBrg,NamaGol,Satuan,StockSkrg,JmlPenjualan,JmlBuffer,HargaAVG,JmlPoSblmnya,HargaPO,KodeSupp,NamaSupp) VALUES (
					sKodeBrg,sNamaBrg,sNamaGol,sSatuanKarton,sStockKarton,sJmlKarton,sStockBuffer,sAVGKarton,sJmlPoSblmnya,sHargaPO,sKodeSupp,sNamaSupp);
			END IF;
	END LOOP getBrg;
	
	CLOSE curBrg;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `SimpanJurnal_BayarBeli` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `SimpanJurnal_BayarBeli`(sNoBayar VARCHAR(15))
BEGIN
	DECLARE NoBukti,sNoBukti VARCHAR(30);
  DECLARE sKodePerk VARCHAR(30);
  DECLARE HPP DECIMAL(15,2);
	DECLARE sTgl DATE;
	DECLARE sNamaSupp VARCHAR(100);
  DECLARE sNilai DECIMAL(15,2);
	DECLARE sJmlRow,sIndex INTEGER(4);
	
	SELECT m.TglBayarB,s.NamaSupp INTO sTgl,sNamaSupp FROM tr_bayarbeli_m m JOIN tm_supplier s ON m.KodeSupp=s.KodeSupp WHERE NoBayarB=sNoBayar;
	
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="YB" and Nobukti_Rtl=sNoBayar LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	SET NoBukti = CONCAT("YB",sNoBayar);
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
	insert into tjurnal_m (tanggal,no_bukti,keterangan,Sandi_Trans_Id,Status_Val,Nobukti_Rtl,pst,jns_jurnal,tgl_validasi,tgl_edit,user) 	values 
		("0000-00-00",NoBukti,CONCAT("Pembayaran Pembelian No: YJ-",sNoBayar," a.n ", sNamaSupp),"YB",0,sNoBayar,0,"JK",DATE(sTgl),now(),"ADMIN");
	
	SELECT COUNT(*) INTO sJmlRow FROM tr_bayarbeli_d WHERE NoBayarB=sNoBayar;
	
	
	SET sIndex=1;
	WHILE sIndex <= sJmlRow DO
		SELECT IFNULL(Bank,"111001"),Nilai INTO sKodePerk,sNilai FROM tr_bayarbeli_d WHERE NoBayarB=sNoBayar AND ItemDBayarB=sIndex;
		IF sKodePerk = '' THEN SET sKodePerk="111001"; END IF; 
		insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
				(YEAR(sTgl),DATE(sTgl),NoBukti,(sIndex * 2)-1,sKodePerk,0,sNilai,"JK");
		insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
				(YEAR(sTgl),DATE(sTgl),NoBukti,(sIndex * 2),"211001",sNilai,0,"JK");
		SET sIndex= sIndex + 1; 
	END WHILE;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `SimpanJurnal_BayarJual` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `SimpanJurnal_BayarJual`(sNoBayar VARCHAR(15))
BEGIN
	DECLARE NoBukti,sNoBukti VARCHAR(30); 
  DECLARE sKodePerk VARCHAR(30);
  DECLARE HPP DECIMAL(15,2);
	DECLARE sTgl DATE;
	DECLARE sNamaCust VARCHAR(100);
  DECLARE sNilai DECIMAL(15,2);
	DECLARE sJmlRow,sIndex INTEGER(4);
	
	SELECT m.TglBayarJ,c.NamaCust INTO sTgl,sNamaCust FROM tr_bayarjual_m m JOIN tm_customer c ON m.KodeCust=c.KodeCust WHERE NoBayarJ=sNoBayar;
	
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="YJ" and Nobukti_Rtl=sNoBayar LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	SET NoBukti = CONCAT("YJ",sNoBayar);
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
	insert into tjurnal_m (tanggal,no_bukti,keterangan,Sandi_Trans_Id,Status_Val,Nobukti_Rtl,pst,jns_jurnal,tgl_validasi,tgl_edit,user) 	values 
		("0000-00-00",NoBukti,CONCAT("Pembayaran Penjualan No: YJ-",sNoBayar," a.n ", sNamaCust),"YJ",0,sNoBayar,0,"JK",DATE(sTgl),now(),"ADMIN");
	
	SELECT COUNT(*) INTO sJmlRow FROM tr_bayarjual_d WHERE NoBayarJ=sNoBayar;
	
	
	SET sIndex=1;
	WHILE sIndex <= sJmlRow DO
		SELECT IFNULL(Bank,"111001"),Nilai INTO sKodePerk,sNilai FROM tr_bayarjual_d WHERE NoBayarJ=sNoBayar AND ItemDBayarJ=sIndex;
		IF sKodePerk = '' THEN SET sKodePerk="111001"; END IF; 
		insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
				(YEAR(sTgl),DATE(sTgl),NoBukti,(sIndex * 2)-1,sKodePerk,sNilai,0,"JK");
		insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
				(YEAR(sTgl),DATE(sTgl),NoBukti,(sIndex * 2),"112001",0,sNilai,"JK");
		SET sIndex= sIndex + 1; 
	END WHILE;
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `SimpanJurnal_JualGrosir` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `SimpanJurnal_JualGrosir`(sNoJual VARCHAR(15))
BEGIN
	DECLARE NoBukti VARCHAR(30);
  DECLARE sNoBukti VARCHAR(30);
  DECLARE HPP DECIMAL(15,2);
	DECLARE sTgl DATE;
	DECLARE sNamaCust VARCHAR(100);
  DECLARE sNilai DECIMAL(15,2);
	SELECT TglJual,NamaCust, Nilai INTO sTgl,sNamaCust, sNilai FROM tr_jual_m WHERE NoJual=sNoJual;
	SELECT SUM(d.HargaAVG * d.Jml) INTO HPP FROM tr_jual_d d WHERE d.NoJual=sNoJual; 
	
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="JT" and Nobukti_Rtl=sNoJual LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	SET NoBukti = CONCAT("JT",sNoJual);
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
	insert into tjurnal_m (tanggal,no_bukti,keterangan,Sandi_Trans_Id,Status_Val,Nobukti_Rtl,pst,jns_jurnal,tgl_validasi,tgl_edit,user) 	values 
		("0000-00-00",NoBukti,CONCAT("Penjualan Grosir No: JT-",sNoJual," a.n ", sNamaCust),"JT",0,sNoJual,0,"JK",DATE(sTgl),now(),"NK");
		
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,1,"511001",HPP,0,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,2,"112001",sNilai,0,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,3,"113001",0,HPP,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,4,"411001",0,sNilai,"JK");
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `SimpanJurnal_Pembelian` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `SimpanJurnal_Pembelian`(sNoBeli  VARCHAR(15))
BEGIN
	DECLARE NoBukti VARCHAR(30); 
  DECLARE sNoBukti VARCHAR(30);
  DECLARE HPP DECIMAL(15,2); 
	DECLARE sTgl DATE;
	DECLARE sNamaSupp VARCHAR(100);
  DECLARE sNilai DECIMAL(15,2);
	DECLARE sKodePerkD1,sKodePerkD2,sKodePerkD3 CHAR(6);
	DECLARE Persediaan1, Persediaan2, Persediaan3 DECIMAL(15,2);
	DECLARE sIndex,sJmlRow INTEGER;
	SELECT TglBeli,NamaSupp, Nilai  INTO sTgl,sNamaSupp, sNilai FROM tr_beli_m WHERE NoBeli=sNoBeli;
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="BT" and Nobukti_Rtl=sNoBeli LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	select CONCAT("BT",sNoBeli) INTO NoBukti;
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
	insert into tjurnal_m (tanggal,no_bukti,keterangan,Sandi_Trans_Id,Status_Val,Nobukti_Rtl,pst,jns_jurnal,tgl_validasi,tgl_edit,user)	values 
		("0000-00-00",NoBukti,CONCAT("Pembelian BT-",sNoBeli," , a.n ", sNamaSupp),"BT",0,sNoBeli,0,"JK",DATE(sTgl),now(),"ADMIN");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
			(YEAR(sTgl),DATE(sTgl),NoBukti,1,113001,sNilai,0,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
			(YEAR(sTgl),DATE(sTgl),NoBukti,2,211001,0,sNilai,"JK");
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `SimpanJurnal_ReturBeli` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `SimpanJurnal_ReturBeli`(sNoRetB  VARCHAR(15))
BEGIN
	DECLARE NoBukti VARCHAR(30); 
  DECLARE sNoBukti VARCHAR(30);
  DECLARE HPP DECIMAL(15,2); 
	DECLARE sTgl DATE;
	DECLARE sNamaSupp VARCHAR(100);
  DECLARE sNilai DECIMAL(15,2);
	DECLARE sKodePerkD1,sKodePerkD2,sKodePerkD3 CHAR(6);
	DECLARE Persediaan1, Persediaan2, Persediaan3 DECIMAL(15,2);
	DECLARE sIndex,sJmlRow INTEGER;
	SELECT m.TglRetB,(SELECT NamaSupp FROM tm_supplier where KodeSupp=m.KodeSupp), m.Nilai  INTO sTgl,sNamaSupp, sNilai FROM tr_retbeli_m m WHERE m.NoRetB=sNoRetB;
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="RB" and Nobukti_Rtl=sNoRetB LIMIT 0,1;
   
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	select CONCAT("RB",sNoRetB) INTO NoBukti;
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
	insert into tjurnal_m (tanggal,no_bukti,keterangan,Sandi_Trans_Id,Status_Val,Nobukti_Rtl,pst,jns_jurnal,tgl_validasi,tgl_edit,user)	values 
		("0000-00-00",NoBukti,CONCAT("Retur Pembelian RB-",sNoRetB," , a.n ", sNamaSupp),"RB",0,sNoRetB,0,"JK",DATE(sTgl),now(),"ADMIN");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
			(YEAR(sTgl),DATE(sTgl),NoBukti,1,113001,0,sNilai,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values 
			(YEAR(sTgl),DATE(sTgl),NoBukti,2,211001,sNilai,0,"JK");
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `SimpanJurnal_ReturGrosir` */;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER"*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `SimpanJurnal_ReturGrosir`(sNoRetJ VARCHAR(15))
BEGIN
	DECLARE NoBukti VARCHAR(30);
  DECLARE sNoBukti VARCHAR(30);
  DECLARE sHPP DECIMAL(15,2);
	DECLARE sTgl DATE;
	DECLARE sNamaCust VARCHAR(100);
  DECLARE sNilai DECIMAL(15,2);
	SELECT r.TglRetJ,(SELECT NamaCust FROM tm_customer WHERE KodeCust=r.KodeCust LIMIT 0,1), r.Nilai INTO sTgl,sNamaCust, sNilai 
		FROM tr_retjual_m r WHERE r.NoRetJ=sNoRetJ;
	SELECT CekHppRetJual(sNoRetJ) INTO sHPP;
	
	Select No_bukti INTO sNoBukti from tjurnal_m where Jns_jurnal="JK" and Sandi_Trans_Id="RJ" and Nobukti_Rtl=sNoRetJ LIMIT 0,1;
    
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=sNoBukti;
	SET NoBukti = CONCAT("RJ",sNoRetJ);
	delete from tJurnal_d where Jns_Jurnal="JK" and No_bukti=NoBukti;
	delete from tJurnal_m where Jns_Jurnal="JK" and No_bukti=NoBukti;
	insert into tjurnal_m (tanggal,no_bukti,keterangan,Sandi_Trans_Id,Status_Val,Nobukti_Rtl,pst,jns_jurnal,tgl_validasi,tgl_edit,user) 	values 
		("0000-00-00",NoBukti,CONCAT("Retur Penjualan Grosir No: RJ-",sNoRetJ," a.n ", sNamaCust),"RJ",0,sNoRetJ,0,"JK",DATE(sTgl),now(),"ADMIN");
		
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,1,"511001",0,sHPP,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,2,"112001",0,sNilai,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,3,"113001",sHPP,0,"JK");
	insert into tjurnal_d(tahun,tanggal,no_bukti,row_id,kode_perk,debet,kredit,jns_jurnal) values (YEAR(sTgl),DATE(sTgl),NoBukti,4,"411001",sNilai,0,"JK");
END */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;


