INSERT into loc SET LOCID = 3, CASHACCT='I00003', LOCNAME='thrid loc', ADDRESS1='bwn', ADDRESS2='wb', MOB1='1234567890', MOB2='1234567890', EMAIL='no mail', LOCPRINT='third loc print'
INSERT into acctinf SET ACCTCODE='I00003', ACCTNAME='third cash', GRCODE='I', GRNAME='CASH ACCOUNT', LOCID=3

select A.ACCTCODE, A.ACCTNAME, B.GSTIN, B.MOB1 from acctinf A 
Left Join (select ACCTCODE, GSTIN, MOB1 FROM acctdet) B on A.ACCTCODE = B.ACCTCODE where A.GRCODE = 'G' order by A.ACCTNAME


select * from acctdet where acctcode = 'G00003'
delete from acctinf where grcode = 'G'


USE `pos2122`;
CREATE TABLE `pos2122`.`transferdet` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Id`)
);
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `ITEMCODE` int(11) DEFAULT '0';
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `MRP` decimal(10,2) DEFAULT '0.00';
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `QTY` int(10) DEFAULT '0';
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `LOTNO` varchar(50) DEFAULT '-';
ALTER TABLE `pos2122`.`transferdet`
  CHANGE COLUMN `Id` `TDID` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `CHALLANNO` varchar(30) NULL DEFAULT '-' AFTER `TDID`;
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `CHALLANDATE` date NULL DEFAULT '2000-01-01' AFTER `CHALLANNO`;
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `LOCFROM` int(3) NULL DEFAULT 0;
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `LOCTO` int(3) NULL DEFAULT 0;
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `STATUS` int(3) NULL DEFAULT 0;
ALTER TABLE `pos2122`.`transferdet`
  ADD COLUMN `STKID` int(11) NULL DEFAULT 0;
ALTER TABLE `pos2122`.`salesdet`
  ADD COLUMN `WHID` int(3) NULL DEFAULT 0;



ALTER TABLE `pos2122`.`stock`
  CHANGE COLUMN `LOCID` `WHID` int(3) NULL DEFAULT 0;




USE `pos2122`;
ALTER TABLE `pos2122`.`acctinf`
  ADD COLUMN `LOCID` int(3) NULL DEFAULT 0 AFTER `ISPRIMARY`;


ALTER TABLE `pos2122`.`acctinf`
  ADD COLUMN `OBFIXED` int(3) NULL DEFAULT 0 AFTER `LOCID`;
ALTER TABLE `pos2122`.`acctinf`
  ADD COLUMN `OB2FIXED` int(3) NULL DEFAULT 0 AFTER `OBFIXED`;

ALTER TABLE `pos2122`.`purchase`
  ADD COLUMN `WHID` int(3) NULL DEFAULT 0 AFTER `LOCID`;


CREATE TABLE `pos2122`.`partytran` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `TRANDATE` date NULL DEFAULT '2000-01-01',
  `ENTRYTYPE` varchar(30) NULL DEFAULT '-',
  `DEBITAMT` double(10,2) NULL DEFAULT 0,
  `CREDITAMT` double(10,2) NULL DEFAULT 0,
  `PARTYCODE` varchar(10) NULL DEFAULT '-',
  `REFNO` varchar(10) NULL DEFAULT '-',
  `PMTMODE` varchar(30) NULL DEFAULT '-',
  `BLTYPE` int(3) NULL DEFAULT 1,
  `LOCID` int(3) NULL DEFAULT 0,
  PRIMARY KEY (`Id`)
);



--MAIN DB
USE `pos`;
ALTER TABLE `pos`.`loc`
  ADD COLUMN `CASHACCT` varchar(10) NULL DEFAULT '-';
ALTER TABLE `pos`.`loc`
  ADD COLUMN `LOCPRINT` varchar(30) NULL DEFAULT '-';

CREATE TABLE `pos`.`wh` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `WHID` int(3) NULL DEFAULT 0,
  `WHNAME` varchar(30) NULL DEFAULT '-',
  `ISACTIVE` int(3) NULL DEFAULT 1,
  PRIMARY KEY (`Id`)
);

CREATE TABLE `pos`.`whmap` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `WHID` int(3) NULL DEFAULT 0,
  `LOCID` int(3) NULL DEFAULT 0,
  PRIMARY KEY (`Id`)
);

USE `pos_purabi`;
ALTER TABLE `pos_purabi`.`loc`
  ADD COLUMN `LOCCODE` varchar(2) NULL DEFAULT '-' AFTER `LOCID`;



ALTER TABLE `pos_purabi`.`login`
  ADD COLUMN `ISSALESPERSON` int(3) NULL DEFAULT 0 AFTER `USERROLE`;

--14.11.2022



-- account DB
ALTER TABLE `pos_purabi2223`.`partytran`
  ADD COLUMN `PAYMENTCODE` varchar(15) NULL DEFAULT '-' AFTER `PARTYCODE`;

ALTER TABLE `pos_purabi2223`.`partytran`
  ADD UNIQUE INDEX `REFNO` (`REFNO`(10)),

ALTER TABLE `pos_purabi2223`.`stockbar`
  CHANGE COLUMN `LOTNO` `LOTNO` varchar(30) CHARACTER SET latin1 NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`salesdet`
  CHANGE COLUMN `BILLNO` `BILLNO` varchar(15) CHARACTER SET latin1 NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`groupinf`
  CHANGE COLUMN `GrCode` `GRCODE` char(1) CHARACTER SET latin1 NULL DEFAULT NULL;

ALTER TABLE `pos_purabi2223`.`groupinf`
  CHANGE COLUMN `GrName` `GRNAME` varchar(30) CHARACTER SET latin1 NULL DEFAULT NULL;

ALTER TABLE `pos_purabi2223`.`stocktransfer`
  CHANGE COLUMN `LOC1` `WH1` int(3) NULL DEFAULT 0;

ALTER TABLE `pos_purabi2223`.`stocktransfer`
  CHANGE COLUMN `LOC2` `WH2` int(3) NULL DEFAULT 0;

ALTER TABLE `pos_purabi2223`.`partytran`
  CHANGE COLUMN `PARTYCODE` `DRACCTCODE` varchar(10) CHARACTER SET latin1 NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`partytran`
  CHANGE COLUMN `PAYMENTCODE` `CRACCTCODE` varchar(15) CHARACTER SET latin1 NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `CUSTCODE` varchar(8) NULL DEFAULT '-' AFTER `BILLDATE`;

ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `CHALLANNO` varchar(30) NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `EWAYBILLNO` varchar(30) NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`partytran`
  DROP INDEX `REFNO`;

ALTER TABLE `pos_purabi2223`.`purchase`
  CHANGE COLUMN `BILLNO` `BILLNO` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`purchasedet`
  CHANGE COLUMN `BILLNO` `BILLNO` varchar(15) CHARACTER SET latin1 NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`partytran`
  ADD COLUMN `EVENTTYPE` varchar(20) NULL DEFAULT '-' AFTER `ENTRYTYPE`;

ALTER TABLE `pos_purabi2223`.`partytran`
  ADD COLUMN `NARRATION` varchar(255) NULL DEFAULT '-' AFTER `PMTMODE`;


CREATE TABLE `pos_purabi2223`.`suppmtdetails` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Id`)
);

ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `VRNO` varchar(15) NULL DEFAULT '-';
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `VRDATE` date NULL DEFAULT '2000-01-01';
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `PBNO` varchar(15) NULL DEFAULT '-';
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `PBAMOUNT` double(10,2) NULL DEFAULT 0;
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `DUEAMOUNT` double(10,2) NULL DEFAULT 0;
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `PAIDAMOUNT` double(10,2) NULL DEFAULT 0;
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `BLTYPE` int(3) NULL DEFAULT 0;
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `LOCID` int(3) NULL DEFAULT 0;
ALTER TABLE `pos_purabi2223`.`suppmtdetails`
  ADD COLUMN `SUPCODE` varchar(15) NULL DEFAULT '-' AFTER `Id`;

ALTER TABLE `pos_purabi2223`.`partytran`
  CHANGE COLUMN `EVENTTYPE` `EVENTTYPE` varchar(50) CHARACTER SET latin1 NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`salesdet`
  ADD COLUMN `LOTNO` varchar(50) DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `SPCODE` int(3) NULL DEFAULT 0;

CREATE TABLE `pos_purabi2223`.`custpmtdetails` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `CUSTCODE` varchar(15) DEFAULT '-',
  `VRNO` varchar(15) DEFAULT '-',
  `VRDATE` date DEFAULT '2000-01-01',
  `SBNO` varchar(15) DEFAULT '-',
  `SBAMOUNT` double(10,2) DEFAULT '0.00',
  `DUEAMOUNT` double(10,2) DEFAULT '0.00',
  `PAIDAMOUNT` double(10,2) DEFAULT '0.00',
  `BLTYPE` int(3) DEFAULT '0',
  `LOCID` int(3) DEFAULT '0',
  PRIMARY KEY (`Id`) 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ;

ALTER TABLE `pos_purabi`.`login`
  ADD COLUMN `ISSALESPERSON` int(11) NULL DEFAULT 0 AFTER `ISACTIVE`;

CREATE TABLE `pos_purabi2223`.`openingstock` (
  `OSID` int(11) NOT NULL AUTO_INCREMENT,
  `INVOICENO` varchar(30) DEFAULT '-',
  `ITEMCODE` int(11) DEFAULT '0',
  `QTY` decimal(10,2) DEFAULT '0.00',
  `MRP` decimal(10,2) DEFAULT '0.00',
  `GSTP` decimal(10,2) DEFAULT '0.00',
  `LOTNO` varchar(50) DEFAULT '-',
  `BLTYPE` int(3) DEFAULT '0',
  PRIMARY KEY (`OSID`)
)
--14.11.2022
ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `UID` int(3) NULL DEFAULT 0;


ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `COMPNAME` varchar(10) NULL DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`purchase`
  ADD COLUMN `UID` int(3) NULL DEFAULT 0;

ALTER TABLE `pos_purabi2223`.`purchase`
  ADD COLUMN `COMPNAME` varchar(10) DEFAULT '-';

ALTER TABLE `pos_purabi2223`.`partytran`
  ADD COLUMN `UID` int(3) NULL DEFAULT 0;

ALTER TABLE `pos_purabi2223`.`partytran`
  ADD COLUMN `COMPNAME` varchar(10) DEFAULT '-';



-- 28.01.2023
ALTER TABLE `pos_purabi2223`.`partytran`
  ADD COLUMN `TRANTIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP;

USE `pos_purabi`;
ALTER TABLE `pos_purabi`.`login`
  ADD COLUMN `SMSOTP` varchar(10) NULL DEFAULT '-' AFTER `USERPWD`;

ALTER TABLE `pos_purabi`.`login`
  ADD COLUMN `EMAIL` varchar(30) NULL DEFAULT '-' AFTER `SMSOTP`;
ALTER TABLE `pos_purabi`.`login`
  ADD COLUMN `SMSDATE` date NULL DEFAULT '2001-01-01' AFTER `SMSOTP`;
ALTER TABLE `pos_purabi`.`login`
  ADD COLUMN `MOBILE` varchar(15) NULL DEFAULT '-' AFTER `EMAIL`;


USE `pos_purabi2223`;
ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `EMAIL` varchar(30) NULL DEFAULT '-' AFTER `MOBILE`;

ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `BID` varchar(100) NULL DEFAULT '-';

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


ALTER TABLE `pos_purabi`.`login`
  CHANGE COLUMN `USERPWD` `USERPWD` varchar(250) CHARACTER SET latin1 NULL DEFAULT '-';

--23042023
ALTER TABLE `stock`
  ADD COLUMN `ITEMNAME` varchar(140) NULL DEFAULT '-' AFTER `ITEMCODE`;

UPDATE stock stk
inner join (select ITEMCODE, ITEMNAME from itemsinf) itm 
on stk.itemcode = itm.itemcode
              set stk.ITEMNAME = itm.ITEMNAME;


--27.06.2023
ALTER TABLE `salesdet`
  ADD COLUMN `BARCODE` varchar(50) NOT NULL DEFAULT '';
  
ALTER TABLE `pos_purabi2223`.`sales`
  ADD COLUMN `CUSTADD` varchar(255) NULL DEFAULT 'xxxxx';


--APRIL 2024
ALTER TABLE `salesdet`
  ADD COLUMN `RETQTY` decimal(10,2) NULL DEFAULT 0;

ALTER TABLE `salesreturn`
  ADD COLUMN `ADJBILLNO` varchar(10) NULL DEFAULT '-';

ALTER TABLE `salesreturn`
  ADD COLUMN `ADJBILLDATE` date NULL DEFAULT '2000-01-01';

ALTER TABLE `sales`
  ADD COLUMN `CNAMT` decimal(10,2) NULL DEFAULT 0;

ALTER TABLE `sales`
  ADD COLUMN `CNNO` varchar(10) NULL DEFAULT '-';


ALTER TABLE `taskdet`
  ADD COLUMN `UID` int(3) NULL DEFAULT 0;