SET @fakturjurnal='saldoawa121224';
SET @tglsaldoawal='2024-12-12';
SET @usercreate='admin';
SELECT id INTO @division FROM dbv8.division ORDER BY id LIMIT 1;

CREATE TABLE dbv4.journaltrans2 AS
SELECT a.division, a.accountid, SUM(a.debit) AS sumdebet, SUM(a.credit) AS sumcredit
FROM dbv4.journaltrans AS a
WHERE DATE(a.jtdate) <= @tglsaldoawal
GROUP BY a.division, a.accountid
ORDER BY a.division, a.accountid;

START TRANSACTION;
INSERT INTO dbv8.journaltrans (jtid, jtdate, memo, accountid, accountname, debit, credit, division, reftransaction, approved, usercreate, useredit)
SELECT @fakturjurnal as jtid, CONCAT(@tglsaldoawal, ' 08:00:00') as jtdate, 'Saldo awal' as memo, a.accountid, b.name AS accountdbv8name, if(a.sumdebet>a.sumcredit, a.sumdebet-a.sumcredit, 0) AS debit,
if(a.sumcredit>a.sumdebet, a.sumcredit-a.sumdebet, 0) AS credit, a.division, @fakturjurnal AS reftransaction, 1 AS approved, @usercreate AS usercreate, '' AS useredit
FROM dbv4.journaltrans2 AS a
LEFT JOIN dbv8.account AS b ON b.id=a.accountid
WHERE a.sumdebet-a.sumcredit<>0;
COMMIT;


SET @fakturjurnal='saldoawa1212124';
SET @tglsaldoawal='2024-12-12';
SET @usercreate='admin';

START TRANSACTION;
INSERT INTO dbv8.inventory (transid, transdate, division, departement, productid, snproduct, expireddate, invin, invout, transtype, memo, usercreate, useredit)
SELECT @fakturjurnal as transid,
CONCAT(@tglsaldoawal, ' 08:00:00') as transdate,
division,
departement,
productid,
snproduct,
expireddate,
sum(invin-invout) as invin,
0 AS invout,
99 AS transtype,
'Saldo awal' as memo,
@usercreate AS usercreate,
'' as useredit
FROM dbv4.inventory
WHERE DATE(transdate) <= @tglsaldoawal
GROUP BY division, departement, productid, snproduct, expireddate
ORDER BY division, departement, productid ASC;

INSERT INTO dbv8.cogs (transid, transdate, division, productid, snproduct, expireddate, qty, cost, cogs, usercreate, useredit)
SELECT @fakturjurnal as transid,
CONCAT(@tglsaldoawal, ' 08:00:00') as transdate,
a.division,a.productid,
a.snproduct,a.expireddate,
sum(a.invin-a.invout) as qty,
(sum((a.invin-a.invout)*a.invvalue)/sum(a.invin-a.invout)) as cost,
(sum((a.invin-a.invout)*a.invvalue)/sum(a.invin-a.invout)) as cogs,
@usercreate AS usercreate,
'' as useredit
FROM dbv4.inventory AS a
WHERE DATE(a.transdate) <= @tglsaldoawal
GROUP BY a.division, a.productid, a.snproduct, a.expireddate
HAVING qty >0;
COMMIT;

SET @salesidpref='saldoawal-';
SET @shipmentid=0;

CREATE TABLE dbv8.salespoint2 AS
SELECT CONCAT(@tglsaldoawal, ' 08:00:00') as transdate, CONCAT(@salesidpref, customer) as salesid,
CONCAT(@salesidpref, customer) as salesidref, customer, SUM(qtyin-qtyout) AS qtyin, 0 as qtyout, @usercreate AS usercreate, '' AS useredit FROM dbv4.salespoint
WHERE DATE(transdate) <= @tglsaldoawal
GROUP BY customer;

START TRANSACTION;
INSERT INTO dbv8.sales (salesid, salesidref, division, kind, salesdate, salestime, customerid, customername, billto, billtoname, salesmanid, salesmanname, shipment, usercreate)
SELECT a.salesid, a.salesidref, @division AS division, 0 as kind, @tglsaldoawal as salesdate, '08:00:00' as salestime, a.customer as customerid, b.name as customername,
a.customer as billto, b.name as billtoname, b.defsalesmanid as salesmanid, '' as salesmanname, @shipmentid AS shipment, @usercreate as usercreate
FROM dbv8.salespoint2 AS a LEFT JOIN dbv8.customer AS b ON b.id=a.customer;

INSERT INTO dbv8.salespoint (transdate, salesid, salesidref, customer, qtyin, qtyout, usercreate, useredit)
SELECT transdate, salesid, salesidref, customer, qtyin, qtyout, usercreate, useredit FROM dbv8.salespoint2;
COMMIT;
