*Tb Saldo Awal Point
CREATE TABLE `tb_saldo_awal_point` (
`Tanggal`  date NOT NULL ,
`KodeCust`  varchar(25) NOT NULL ,
`SaldoAwal`  decimal(15,2) NULL ,
PRIMARY KEY (`Tanggal`, `KodeCust`)
);

ALTER TABLE `tb_saldo_awal_point` ADD CONSTRAINT `fk_kode_cust` FOREIGN KEY (`KodeCust`) REFERENCES `tm_customer` (`KodeCust`) ON DELETE RESTRICT ON UPDATE RESTRICT;

*Tm Barang
ALTER TABLE `tm_barang`
ADD COLUMN `TipeBrg`  varchar(8) NULL AFTER `Pabrik`;

*TMenu
INSERT INTO `tmenu` (`menu_id`, `menu`, `keterangan`, `Status_aktive`) VALUES ('090400', 'point1', 'Point', '1');
INSERT INTO `tmenu` (`menu_id`, `menu`, `keterangan`, `Status_aktive`) VALUES ('090401', 'saldoawalpoint1', 'Saldo Awal Point', '1');
INSERT INTO `tmenu` (`menu_id`, `menu`, `keterangan`, `Status_aktive`) VALUES ('090402', 'ambilpoint1', 'Ambil Point', '1');
INSERT INTO `tmenu` (`menu_id`, `menu`, `keterangan`, `Status_aktive`) VALUES ('090403', 'kartupoint1', 'Kartu Point', '1');
INSERT INTO `tuserlevel` (`userlevel_id`, `menu`, `status`, `Ket_level`) VALUES ('01', '090400', '1', 'Admin');
INSERT INTO `tuserlevel` (`userlevel_id`, `menu`, `status`, `Ket_level`) VALUES ('01', '090401', '1', 'Admin');
INSERT INTO `tuserlevel` (`userlevel_id`, `menu`, `status`, `Ket_level`) VALUES ('01', '090402', '1', 'Admin');
INSERT INTO `tuserlevel` (`userlevel_id`, `menu`, `status`, `Ket_level`) VALUES ('01', '090403', '1', 'Admin');

*Setup All
INSERT INTO `tm_setupall` (`groups`, `subgroups`, `nilainum`, `nilaichar`) VALUES ('Point', 'MinTrans', '10000', 'Minimum Transaksi Baru Dapat Point');
INSERT INTO `tm_setupall` (`groups`, `subgroups`, `nilainum`, `nilaichar`) VALUES ('Point', 'GetPoint', '50', 'Jumlah Point Yang Di Dapat');

*TrJual_M
ALTER TABLE `tr_jual_m`
ADD COLUMN `Point`  decimal(15,2) NULL AFTER `BankPerkiraan`;

ALTER TABLE `tr_jual_m`
ADD COLUMN `AmbilPoint`  decimal(15,2) NULL AFTER `Point`;

ALTER TABLE `tr_jual_m`
ADD INDEX `fk_tr_jual_tgljt` USING BTREE (`TglJT`);
 
+ Trigger

DROP TRIGGER `TrJualAdd`;

DROP TRIGGER `TrJualUpdate`;

DROP TRIGGER `TrJualDelete`;

CREATE DEFINER=`root`@`localhost` TRIGGER `TrJualAdd` AFTER INSERT ON `tr_jual_m`
FOR EACH ROW BEGIN
  Update tm_Customer set Piutang =  ifnull(piutang,0) + ((new.nilai-new.bayartunai-new.bayarcard-new.bayarvoucher) * new.exrate)
  where kodecust = new.kodecust;

  IF new.Point > 0 THEN
     Update tm_Customer set SaldoPoint = SaldoPoint + new.Point
     where kodecust = new.kodecust;
  END IF;

 IF new.AmbilPoint > 0 THEN
     Update tm_Customer set SaldoPoint = SaldoPoint - new.AmbilPoint
     where kodecust = new.kodecust;
  END IF;
end;

CREATE DEFINER=`root`@`localhost` TRIGGER `TrJualUpdate` AFTER UPDATE ON `tr_jual_m`
FOR EACH ROW BEGIN
  Update tm_Customer set Piutang =  ifnull(piutang,0) + ((new.nilai-new.bayartunai-new.bayarcard-new.bayarvoucher) * new.exrate)
  where kodecust = new.kodecust;
  Update tm_Customer set Piutang =  ifnull(piutang,0) - ((old.nilai-old.bayartunai-old.bayarcard-old.bayarvoucher) * Old.exrate)
  where kodecust = old.kodecust;

   Update tm_Customer set SaldoPoint = SaldoPoint + new.Point
   where kodecust = new.kodecust;

  Update tm_Customer set SaldoPoint = SaldoPoint - old.Point
   where kodecust = old.kodecust;

Update tm_Customer set SaldoPoint = SaldoPoint - new.AmbilPoint
   where kodecust = new.kodecust;

  Update tm_Customer set SaldoPoint = SaldoPoint + old.AmbilPoint
   where kodecust = old.kodecust;
end;

CREATE DEFINER=`root`@`localhost` TRIGGER `TrJualDelete` AFTER DELETE ON `tr_jual_m`
FOR EACH ROW BEGIN
  Update tm_Customer set Piutang =  ifnull(piutang,0) - ((old.nilai-old.bayartunai-old.bayarcard-old.bayarvoucher) * Old.exrate) 
  where kodecust = Old.kodecust;

Update tm_Customer set SaldoPoint = SaldoPoint - old.Point
   where kodecust = old.kodecust;

Update tm_Customer set SaldoPoint = SaldoPoint + old.AmbilPoint
   where kodecust = old.kodecust;
end;


*TrJualRM
ALTER TABLE `tr_jualr_m`
ADD COLUMN `Point`  decimal(15,2) NULL AFTER `Shift`;

ALTER TABLE `tr_jualr_m`
ADD COLUMN `AmbilPoint`  decimal(15,2) NULL AFTER `Point`;

 
+ Trigger
DROP TRIGGER `TrJualRAdd`;

CREATE TRIGGER `TrJualRAdd` AFTER INSERT ON `tr_jualr_m`
FOR EACH ROW BEGIN
  IF new.Point > 0 THEN
     Update tm_Customer set SaldoPoint = SaldoPoint + new.Point
     where kodecust = new.kodecust;
  END IF;
  
  IF new.AmbilPoint > 0 THEN
     Update tm_Customer set SaldoPoint = SaldoPoint - new.AmbilPoint
     where kodecust = new.kodecust;
  END IF;
end;

DROP TRIGGER `TrJualRUpdate`;

CREATE TRIGGER `TrJualRUpdate` AFTER UPDATE ON `tr_jualr_m`
FOR EACH ROW BEGIN
   Update tm_Customer set SaldoPoint = SaldoPoint + new.Point
   where kodecust = new.kodecust;

  Update tm_Customer set SaldoPoint = SaldoPoint - old.Point
   where kodecust = old.kodecust;

  Update tm_Customer set SaldoPoint = SaldoPoint - new.AmbilPoint
   where kodecust = new.kodecust;

  Update tm_Customer set SaldoPoint = SaldoPoint + old.AmbilPoint
   where kodecust = old.kodecust;
end;

DROP TRIGGER `TrJualRDelete`;

CREATE TRIGGER `TrJualRDelete` AFTER DELETE ON `tr_jualr_m`
FOR EACH ROW BEGIN
 Update tm_Customer set SaldoPoint = SaldoPoint + old.AmbilPoint
   where kodecust = old.kodecust;
END;



*mKonfigurasi
ALTER TABLE `mkonfigurasi`
ADD COLUMN `TotalPoint`  decimal(15,2) NULL DEFAULT 0 AFTER `shift`,
ADD COLUMN `AmbilPoint`  decimal(15,2) NULL DEFAULT 0 AFTER `TotalPoint`,
ADD COLUMN `SisaPoint`  decimal(15,2) NULL DEFAULT 0 AFTER `AmbilPoint`;

*TrAmbilPoint
ALTER TABLE `tr_ambil_point`
ADD COLUMN `KodeAmbil`  varchar(10) NOT NULL AFTER `NoAmbil`;

CREATE TRIGGER `TrAmbilPointAdd` AFTER INSERT ON `tr_ambil_point`
FOR EACH ROW BEGIN
 IF (new.TakePoint > 0) and (new.KodeAmbil='AP') THEN
     Update tm_Customer set SaldoPoint = SaldoPoint - new.TakePoint
     where kodecust = new.kodecust;
  END IF;
END;