select * from acctinf where grcode in ('I', 'J');


--*************** Fresh Beginning
USE `pos_purabi2223`;
delete from acctdet;
delete from acctinf where grcode in ('I', 'J', 'G', 'K');
delete from custpmtdetails;
delete from itemsinf;
delete from journal;
delete from openingstock;
delete from partytran;
delete from purchase;
delete from purchasedet;
delete from purchasetmp;
delete from sales;
delete from salesdet;
delete from salesdetbar;
delete from stock;
delete from stockbar;
delete from stocktransfer;
delete from suppmtdetails;
update util set PREF1='', PREF2='', SUFF1='', SUFF2='' where Id=1;

UPDATE acctinf set OPBALDR = 0, OPBALCR=0, OPBALDR2=0, OPBALCR2=0, TRANDR=0, TRANCR=0, TRANDR2=0, TRANCR2=0;

INSERT INTO `acctinf` SET `ACCTCODE`='K00000', `ACCTNAME`='CASH***', `GRCODE`='K', `GRNAME`='SUNDRY DEBTORS';



USE `pos_purabi2223`;
INSERT INTO `pos_purabi2223`.`acctinf` SET `ACCTCODE`='I00001', `ACCTNAME`='CASH DR', `GRCODE`='I', `GRNAME`='CASH ACCOUNT', `LOCID`=1;
INSERT INTO `pos_purabi2223`.`acctinf` SET `ACCTCODE`='I00002', `ACCTNAME`='CASH OH', `GRCODE`='I', `GRNAME`='CASH ACCOUNT', `LOCID`=2;

--=========konar
INSERT INTO `acctinf` SET `ACCTCODE`='I00001', `ACCTNAME`='CASH 1', `GRCODE`='I', `GRNAME`='CASH ACCOUNT', `LOCID`=1;
INSERT INTO `acctinf` SET `ACCTCODE`='I00002', `ACCTNAME`='CASH 2', `GRCODE`='I', `GRNAME`='CASH ACCOUNT', `LOCID`=2;
INSERT INTO `acctinf` SET `ACCTCODE`='I00003', `ACCTNAME`='CASH 3', `GRCODE`='I', `GRNAME`='CASH ACCOUNT', `LOCID`=3;




--WORKING WITH STOCK in Old Purobi DB
ALTER TABLE `purobi2223`.`stock`
  ADD COLUMN `NEWLOTNO` varchar(50) NULL DEFAULT '-';

ALTER TABLE `purobi2223`.`stockbar`
  ADD COLUMN `NEWLOTNO` varchar(50) NULL DEFAULT '-';


--****CREATING LOT NO.
update stock set NEWLOTNO = CONCAT('G1P',IF(LENGTH(ITEMCODE) < 5, LPAD(ITEMCODE, 5, "0"), ITEMCODE),'R',IF(LENGTH(FLOOR(MRP)) < 5, LPAD(FLOOR(MRP), 5, "0"), FLOOR(MRP)));

UPDATE stockbar stb 
inner join (select LOTNO,NEWLOTNO from stock) st 
on st.LOTNO = stb.LOTNO
              set stb.NEWLOTNO = st.NEWLOTNO;



delete * from itemsinf where category like 'CRÈME%'



-- ****************** data transfer from Purobi
INSERT INTO pos_purabi2223.acctinf (ACCTCODE, ACCTNAME, GRCODE, GRNAME, LOCID, OPBALDR, OPBALCR) 
SELECT ACCTCODE, ACCTNAME, GRCODE, GRNAME, 1, OpBalDr, OpBalCr from purobi2223.acctinf WHERE GRCODE in ('G', 'K');


INSERT INTO pos_purabi2223.acctdet (ACCTCODE, ACCTNAME, ADDRESS1, ADDRESS2, MOB1, MOB2, EMAIL, CONTPERSON, GSTIN, CREDITLIMIT) 
SELECT ACCTCODE, ACCTNAME, ADDRESS1, ADDRESS2, MOB1, MOB2, EMAIL, CONTPERSON, GSTIN, CREDITLIMIT from purobi2223.extended;


INSERT INTO pos_purabi2223.itemsinf (ITEMCODE, ITEMNAME, HSN, GSTP, PPRICE, MRP, DISCP, CATEGORY, BRAND, MODEL, SPEC, UNIT) 
SELECT ITEMCODE, ITEMNAME, HSN, GSTP, PPRICE, MRP, DISC, CATEGORY, BRAND, MODEL, SPEC, UNIT from purobi2223.itemsinf;



INSERT INTO pos_purabi2223.stock (ITEMCODE, MRP, QTY, LOTNO, WHID, BLTYPE) 
SELECT ITEMCODE, MRP, QTY, NEWLOTNO, 1, 1 from purobi2223.stock;


INSERT INTO pos_purabi2223.stockbar (BARCODE, LOTNO) 
SELECT BARCODE, NEWLOTNO from purobi2223.stockbar;


--====== updating opening stock qty
UPDATE pos_purabi2223.itemsinf set OPQTY=0, OPBALANCE=0;

UPDATE pos_purabi2223.itemsinf itm 
inner join (select itemcode,sum(qty) as opqty from purobi2223.stock group by itemcode) stk 
on stk.itemcode = itm.itemcode
              set itm.opqty = stk.opqty;