CREATE DATABASE  IF NOT EXISTS `ygpharmacy` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `ygpharmacy`;
--
-- Host: localhost    Database: ygpharmacy
-- ------------------------------------------------------


--
-- Table structure for table `tbl_barcode_details`
--

CREATE TABLE `tbl_barcode_details` (
  `a_brcd_id` int(11) NOT NULL AUTO_INCREMENT,
  `Category_Id` int(11) DEFAULT NULL,
  `itemId` int(11) DEFAULT NULL,
  `depoId` int(11) DEFAULT NULL,
  `depoGRNId` int(11) DEFAULT NULL,
  `issuedToBranch` tinyint(4) DEFAULT NULL,
  `branchId` int(11) DEFAULT NULL,
  `branchGRNId` int(11) DEFAULT NULL,
  `barcodeVal` varchar(45) DEFAULT NULL,
  `itemSold` int(11) DEFAULT NULL,
  `isAcceptedByBranch` tinyint(4) DEFAULT '0',
  `isRemovedByStockCorrection` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`a_brcd_id`),
  KEY `idx_pk_barcode_1` (`barcodeVal`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_barcode_details`
--


--
-- Table structure for table `tbl_branchtransfer_details`
--

CREATE TABLE `tbl_branchtransfer_details` (
  `abrnchtanid` int(11) NOT NULL AUTO_INCREMENT,
  `po_raised_id` int(11) DEFAULT NULL,
  `reqRaisedDate` date DEFAULT NULL,
  `totalQty` int(11) DEFAULT NULL,
  `totalItems` int(11) DEFAULT NULL,
  `fromBranchId` int(11) DEFAULT '0',
  `fromBranchName` varchar(250) DEFAULT NULL,
  `toBranchId` int(11) DEFAULT '0',
  `toBranchName` varchar(250) DEFAULT NULL,
  `reqRaisedUserId` int(11) DEFAULT NULL,
  `reqRaisedUserName` varchar(150) DEFAULT NULL,
  `fromBranchComment` text,
  `expctDeliveryDate` date DEFAULT NULL,
  `toBranchComment` text,
  `actualDeliveryDate` date DEFAULT NULL,
  `depoComment` text,
  `brnchTrans_statu_id` tinyint(4) DEFAULT '0',
  `brnchTrans_statu_text` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`abrnchtanid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_branchtransfer_details`
--


--
-- Table structure for table `tbl_company_depot_details`
--

CREATE TABLE `tbl_company_depot_details` (
  `adepotId` int(11) NOT NULL AUTO_INCREMENT,
  `depotName` varchar(1000) DEFAULT NULL,
  `depotOwnerName` varchar(250) DEFAULT NULL,
  `depotAddress` varchar(1000) DEFAULT NULL,
  `depotCity` varchar(100) DEFAULT NULL,
  `depotState` varchar(100) DEFAULT NULL,
  `depotCountry` varchar(100) DEFAULT NULL,
  `dPincode` varchar(50) DEFAULT NULL,
  `depotPhNo` varchar(250) DEFAULT NULL,
  `depotEmergencyNo` varchar(250) DEFAULT NULL,
  `depotActive` int(11) DEFAULT NULL,
  `nComId` int(11) DEFAULT NULL,
  `dEmailId` varchar(250) DEFAULT NULL,
  `depotcode` varchar(250) DEFAULT NULL,
  `regNo` varchar(45) DEFAULT NULL,
  `PANNo` varchar(45) DEFAULT NULL,
  `distCode` varchar(45) DEFAULT NULL,
  `depotDistrict` varchar(50) DEFAULT NULL,
  `dGSTINno` varchar(45) DEFAULT NULL,
  `st_number` varchar(50) DEFAULT NULL,
  `cin_number` varchar(50) DEFAULT NULL,
  `rohini_id` varchar(50) DEFAULT NULL,
  `depot_DeliveryAddress` text,
  `depot_BillingAddress` text,
  `depot_LocationId` int(11) DEFAULT NULL,
  `po_raise_format` varchar(45) DEFAULT NULL,
  `grnShortCode` varchar(10) DEFAULT NULL,
  `faxNumber` varchar(45) DEFAULT NULL,
  `contact_Num` varchar(45) DEFAULT NULL,
  `contact_Person` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`adepotId`),
  KEY `depot_LocationId_idx` (`depot_LocationId`),
  KEY `nComId_idx` (`nComId`),
  CONSTRAINT `depot_LocationId` FOREIGN KEY (`depot_LocationId`) REFERENCES `tbl_mst_locations` (`aLocationId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_company_depot_details_1` FOREIGN KEY (`nComId`) REFERENCES `tbl_company_details` (`aComId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_company_depot_details`
--

INSERT INTO `tbl_company_depot_details` VALUES (1,'YaraGoDepo','','','1','1','1','400057','1','1',1,1,'','1000','1','AAECI6389M','1','1','27AAECI6389M1ZT ','1',' U74999MH2017PTC300119','1','','',1,'1000','1000',NULL,NULL,NULL);

--
-- Table structure for table `tbl_company_details`
--

CREATE TABLE `tbl_company_details` (
  `aComId` int(11) NOT NULL AUTO_INCREMENT,
  `CompanyName` varchar(500) DEFAULT NULL,
  `CompanyOwnerName` varchar(250) DEFAULT NULL,
  `CompanyAddress` varchar(1000) DEFAULT NULL,
  `CompanyCity` varchar(100) DEFAULT NULL,
  `CompanyState` varchar(100) DEFAULT NULL,
  `CompanyCountry` varchar(100) DEFAULT NULL,
  `Pincode` varchar(50) DEFAULT NULL,
  `CompanyPhNo` varchar(250) DEFAULT NULL,
  `CompanyEmergencyNo` varchar(250) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  `CompanyEmail` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`aComId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_company_details`
--

INSERT INTO `tbl_company_details` VALUES (1,'Main Depo','1','1','1','1','1','1','1','1',1,'1');

--
-- Table structure for table `tbl_courier_dctnumber`
--

CREATE TABLE `tbl_courier_dctnumber` (
  `dctnumber_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `phone_number` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`dctnumber_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_courier_dctnumber`
--

INSERT INTO `tbl_courier_dctnumber` VALUES (1,'dct1','banglore','111','2019-02-13 15:55:35','2019-02-13 15:55:35',1,0),(2,'dct2','baglkot','222','2019-02-13 15:56:19','2019-02-13 15:56:19',1,0);

--
-- Table structure for table `tbl_customer_details`
--

CREATE TABLE `tbl_customer_details` (
  `acustomerId` int(11) NOT NULL AUTO_INCREMENT,
  `customerName` varchar(500) DEFAULT NULL,
  `nComId` int(11) DEFAULT NULL,
  `customerOwnerName` varchar(250) DEFAULT NULL,
  `customerAddress` varchar(1000) DEFAULT NULL,
  `customerCity` varchar(100) DEFAULT NULL,
  `customerState` varchar(100) DEFAULT NULL,
  `customerCountry` varchar(100) DEFAULT NULL,
  `Pincode` varchar(50) DEFAULT NULL,
  `customerPhNo` varchar(250) DEFAULT NULL,
  `customerEmergencyNo` varchar(250) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  `customerEmail` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`acustomerId`),
  KEY `nComId_idx` (`nComId`),
  CONSTRAINT `nComId` FOREIGN KEY (`nComId`) REFERENCES `tbl_company_details` (`aComId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_customer_details`
--

INSERT INTO `tbl_customer_details` VALUES (1,'test',1,'1','1','1','1','1','1','1','1',1,'1');

--
-- Table structure for table `tbl_frametype_discount_branchmap`
--

CREATE TABLE `tbl_frametype_discount_branchmap` (
  `aId` int(11) NOT NULL AUTO_INCREMENT,
  `nframeTypeId` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `discountPercentage` float DEFAULT NULL,
  `isActive` int(11) DEFAULT '1',
  `isDeleted` int(11) DEFAULT '0',
  `insertedDateTime` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDateTime` datetime DEFAULT CURRENT_TIMESTAMP,
  `nUserId` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`aId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_frametype_discount_branchmap`
--


--
-- Table structure for table `tbl_goods_return_each_item_details`
--

CREATE TABLE `tbl_goods_return_each_item_details` (
  `aItemRetId` int(11) NOT NULL AUTO_INCREMENT,
  `mainReturnId` varchar(45) DEFAULT NULL,
  `categoryId` int(11) DEFAULT NULL,
  `is_customer_or_cunsumer_retn_by` int(11) DEFAULT NULL,
  `customer_or_cunsumer_id_retn_by` int(11) DEFAULT NULL,
  `is_customer_or_supplier_retn_to` int(11) DEFAULT NULL,
  `customer_or_supplier_id_retn_to` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `grnID` int(11) DEFAULT NULL,
  `grn_number` varchar(150) DEFAULT NULL,
  `batchNo` varchar(150) DEFAULT NULL,
  `expiryDate` datetime DEFAULT NULL,
  `itemratemasterID` int(11) DEFAULT NULL,
  `returnedItems` int(11) DEFAULT NULL,
  `returnedQty` int(11) DEFAULT NULL,
  `isApproved` int(11) DEFAULT '0',
  `approvedBy` int(11) DEFAULT NULL,
  `isDepoAccepted` int(11) DEFAULT '0',
  `depoAcceptedBy` int(11) DEFAULT NULL,
  `retReasonId` int(11) DEFAULT NULL,
  `retReason` text,
  `scannedBarcodeNo` text,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `grnInvoiceNumber` varchar(250) DEFAULT NULL,
  `isconsumedinpo` int(11) DEFAULT '0',
  `returntype` int(11) DEFAULT '0',
  `returnActive` int(11) DEFAULT '0',
  PRIMARY KEY (`aItemRetId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_goods_return_each_item_details`
--


--
-- Table structure for table `tbl_goods_return_main_details`
--

CREATE TABLE `tbl_goods_return_main_details` (
  `aRetID` int(11) NOT NULL AUTO_INCREMENT,
  `categoryId` int(11) DEFAULT NULL,
  `is_customer_or_cunsumer_retn_by` int(11) DEFAULT NULL,
  `customer_or_cunsumer_id_retn_by` int(11) DEFAULT NULL,
  `is_customer_or_supplier_retn_to` int(11) DEFAULT NULL,
  `customer_or_supplier_id_retn_to` int(11) DEFAULT NULL,
  `returnedItems_total` int(11) DEFAULT NULL,
  `returnedQty_total` int(11) DEFAULT NULL,
  `generatedUUID_PerTran` varchar(500) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `isReturnreplacement` int(11) DEFAULT '0',
  `returntype` int(11) DEFAULT '0',
  `returnActive` int(11) DEFAULT '0',
  PRIMARY KEY (`aRetID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_goods_return_main_details`
--


--
-- Table structure for table `tbl_grn_for_po_each_item_orders`
--

CREATE TABLE `tbl_grn_for_po_each_item_orders` (
  `aGrnId_each_pk` int(11) NOT NULL AUTO_INCREMENT,
  `nGrnId_fk` int(11) DEFAULT NULL,
  `categoryid` int(11) DEFAULT NULL,
  `nPoIdCentral_fk` int(11) DEFAULT NULL,
  `nPoEachId` int(11) DEFAULT NULL,
  `nPoEachIdBranch_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_Central_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_Centra_Each_fk` int(11) DEFAULT NULL,
  `sendToBranch` int(11) DEFAULT NULL,
  `nGrnIdCentral_fk` int(11) DEFAULT NULL,
  `nPoType_fk` int(11) DEFAULT NULL,
  `nSupplierId_fk` int(11) DEFAULT NULL,
  `nGrnDepo_fk` int(11) DEFAULT NULL,
  `nGrnUserId_fk` int(11) DEFAULT NULL,
  `nItemId_fk` int(11) DEFAULT NULL,
  `grnItemStatusId` int(11) DEFAULT NULL,
  `grnItemStatusText` text,
  `balanceQty` int(11) DEFAULT NULL,
  `receivedQty` int(11) DEFAULT NULL,
  `freeQtys` int(11) DEFAULT '0',
  `issuedqty_fromdepo` int(11) DEFAULT NULL,
  `batchNo` varchar(150) DEFAULT NULL,
  `expiryDate` datetime DEFAULT NULL,
  `serialNumber` varchar(100) DEFAULT NULL,
  `itemAmount` float DEFAULT NULL,
  `gstType` int(11) DEFAULT NULL,
  `cgstPercent` float DEFAULT NULL,
  `sgstPercent` float DEFAULT NULL,
  `igstPercent` float DEFAULT NULL,
  `gstPercent` float DEFAULT NULL,
  `cgstAmount` float DEFAULT NULL,
  `sgstAmount` float DEFAULT NULL,
  `igstAmount` float DEFAULT NULL,
  `gstAmount` float DEFAULT NULL,
  `grossAmount` float DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `ngrnBranchId_fk` int(11) DEFAULT NULL,
  `size` varchar(25) DEFAULT NULL,
  `diapter` varchar(25) DEFAULT NULL,
  `strength` varchar(25) DEFAULT NULL,
  `volume` varchar(25) DEFAULT NULL,
  `issuedToBranch` int(11) DEFAULT '0',
  `nSupplierItemMapid` int(11) DEFAULT NULL,
  `nGrnTypeId` int(11) DEFAULT '1',
  `nEachTransferId` int(11) DEFAULT NULL,
  `nMainTransferId` int(11) DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `po_from_depo_fk` int(11) DEFAULT NULL,
  `n_poid_from_branch_fk` int(11) DEFAULT '0',
  `grn_from_depo_fk` int(11) DEFAULT NULL,
  `grn_from_branch_fk` int(11) DEFAULT '0',
  `disPercentage` float DEFAULT '0',
  `disAmt` float DEFAULT '0',
  `itemTotalAmt` float DEFAULT '0',
  `taxableAmount` float DEFAULT '0',
  `taxForTaxableAmount` float DEFAULT '0',
  `cgstForTaxbleAmt` float DEFAULT '0',
  `sgstForTaxableAmt` float DEFAULT '0',
  `taxableAmtForOneQty` float DEFAULT '0',
  `taxForOneQty` float DEFAULT '0',
  PRIMARY KEY (`aGrnId_each_pk`),
  UNIQUE KEY `nEachTransferId_UNIQUE` (`nEachTransferId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_grn_for_po_each_item_orders`
--


--
-- Table structure for table `tbl_grn_for_po_each_item_orders_temp_mq`
--

CREATE TABLE `tbl_grn_for_po_each_item_orders_temp_mq` (
  `aGrnId_each_pk` int(11) NOT NULL AUTO_INCREMENT,
  `nGrnId_fk` int(11) DEFAULT NULL,
  `categoryid` int(11) DEFAULT NULL,
  `nPoIdCentral_fk` int(11) DEFAULT NULL,
  `nPoEachId` int(11) DEFAULT NULL,
  `nPoEachIdBranch_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_Central_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_Centra_Each_fk` int(11) DEFAULT NULL,
  `sendToBranch` int(11) DEFAULT NULL,
  `nGrnIdCentral_fk` int(11) DEFAULT NULL,
  `nPoType_fk` int(11) DEFAULT NULL,
  `nSupplierId_fk` int(11) DEFAULT NULL,
  `nGrnDepo_fk` int(11) DEFAULT NULL,
  `nGrnUserId_fk` int(11) DEFAULT NULL,
  `nItemId_fk` int(11) DEFAULT NULL,
  `grnItemStatusId` int(11) DEFAULT NULL,
  `grnItemStatusText` text,
  `balanceQty` int(11) DEFAULT NULL,
  `receivedQty` int(11) DEFAULT NULL,
  `issuedqty_fromdepo` int(11) DEFAULT NULL,
  `batchNo` varchar(10) DEFAULT NULL,
  `expiryDate` datetime DEFAULT NULL,
  `serialNumber` varchar(100) DEFAULT NULL,
  `itemAmount` float DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `ngrnBranchId_fk` int(11) DEFAULT NULL,
  `size` varchar(25) DEFAULT NULL,
  `diapter` varchar(25) DEFAULT NULL,
  `strength` varchar(25) DEFAULT NULL,
  `volume` varchar(25) DEFAULT NULL,
  `issuedToBranch` int(11) DEFAULT '0',
  `nSupplierItemMapid` int(11) DEFAULT NULL,
  `nGrnTypeId` int(11) DEFAULT '1',
  `nEachTransferId` int(11) DEFAULT NULL,
  `nMainTransferId` int(11) DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `po_from_depo_fk` int(11) DEFAULT NULL,
  `n_poid_from_branch_fk` int(11) DEFAULT '0',
  `grn_from_depo_fk` int(11) DEFAULT NULL,
  `grn_from_branch_fk` int(11) DEFAULT '0',
  PRIMARY KEY (`aGrnId_each_pk`),
  UNIQUE KEY `nEachTransferId_UNIQUE` (`nEachTransferId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_grn_for_po_each_item_orders_temp_mq`
--


--
-- Table structure for table `tbl_grn_for_po_orders`
--

CREATE TABLE `tbl_grn_for_po_orders` (
  `aGrnId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `grnNumber` varchar(50) DEFAULT NULL,
  `categoryid` int(11) DEFAULT NULL,
  `nPoIdCentral_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_Central_fk` int(11) DEFAULT NULL,
  `nGrnIdCentral_fk` int(11) DEFAULT NULL,
  `sendToBranch` int(11) DEFAULT '0',
  `nPoType_fk` int(11) DEFAULT NULL,
  `nSupplierId_fk` int(11) DEFAULT NULL,
  `nGrnDepo_fk` int(11) DEFAULT NULL,
  `nGrnUserId_fk` int(11) DEFAULT NULL,
  `grnDate` datetime DEFAULT NULL,
  `dcNumber` varchar(25) DEFAULT NULL,
  `dcDate` datetime DEFAULT NULL,
  `invoiceNumber` varchar(50) DEFAULT NULL,
  `invoiceAmount` float DEFAULT NULL,
  `invoiceTaxAmt` float DEFAULT '0',
  `frightCharges` float DEFAULT '0',
  `invoicePhotoUploadName` varchar(25) DEFAULT NULL,
  `grnRemarks` text,
  `grnTotalItems` int(11) DEFAULT NULL,
  `grnTotalItemsQty` int(11) DEFAULT NULL,
  `issuedqty_fromdepo` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `ngrnBranchId_fk` int(11) DEFAULT NULL,
  `issuedToBranch` int(11) DEFAULT '0',
  `centralGRNNum` varchar(25) DEFAULT NULL,
  `centralGrnId` int(11) DEFAULT '0',
  `invoiceDate` datetime DEFAULT NULL,
  `nCommertialStatus` int(11) DEFAULT '0',
  `commercialStatusUpdatedDate` datetime DEFAULT NULL,
  `commercialStatusUpdatedBy` int(11) DEFAULT NULL,
  `statusRemarks` text,
  `centralMainTransferId` int(11) DEFAULT NULL,
  `centralMainGrnId` int(11) DEFAULT '0',
  `accountsStatusId` int(11) DEFAULT NULL,
  `accountsRemarks` text,
  `accountsUserId` int(11) DEFAULT NULL,
  `accountsDate` datetime DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `po_from_depo_fk` int(11) DEFAULT NULL,
  `n_poid_from_branch_fk` int(11) DEFAULT '0',
  `grn_from_depo_fk` int(11) DEFAULT NULL,
  `grn_from_branch_fk` int(11) DEFAULT '0',
  `sgst` float DEFAULT '0',
  `cgst` float DEFAULT '0',
  `igst` float DEFAULT '0',
  `dctnumer_id_fk` int(11) DEFAULT NULL,
  `dct_name` varchar(45) DEFAULT NULL,
  `courier_dct_number` varchar(45) DEFAULT NULL,
  `courier_phone` varchar(45) DEFAULT NULL,
  `isDepoOrBranchGeneratingInvoice` int(11) DEFAULT NULL,
  `invoiceGenerateDepoOrBranchId` int(11) DEFAULT NULL,
  `grnFrightCharges` float DEFAULT '0',
  `grnAmtWithFright` float DEFAULT '0',
  `grnDisTotAmt` float DEFAULT '0',
  `isPaidToSupplier` int(11) DEFAULT '0',
  PRIMARY KEY (`aGrnId_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_grn_for_po_orders`
--


--
-- Table structure for table `tbl_grn_for_po_orders_temp_mq`
--

CREATE TABLE `tbl_grn_for_po_orders_temp_mq` (
  `aGrnId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `grnNumber` varchar(50) DEFAULT NULL,
  `categoryid` int(11) DEFAULT NULL,
  `nPoIdCentral_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_fk` int(11) DEFAULT NULL,
  `nPoIdBranch_Central_fk` int(11) DEFAULT NULL,
  `nGrnIdCentral_fk` int(11) DEFAULT NULL,
  `sendToBranch` int(11) DEFAULT '0',
  `nPoType_fk` int(11) DEFAULT NULL,
  `nSupplierId_fk` int(11) DEFAULT NULL,
  `nGrnDepo_fk` int(11) DEFAULT NULL,
  `nGrnUserId_fk` int(11) DEFAULT NULL,
  `grnDate` datetime DEFAULT NULL,
  `dcNumber` varchar(25) DEFAULT NULL,
  `dcDate` datetime DEFAULT NULL,
  `invoiceNumber` varchar(50) DEFAULT NULL,
  `invoiceAmount` float DEFAULT NULL,
  `invoiceTaxAmt` float DEFAULT '0',
  `frightCharges` float DEFAULT NULL,
  `invoicePhotoUploadName` varchar(25) DEFAULT NULL,
  `grnRemarks` text,
  `grnTotalItems` int(11) DEFAULT NULL,
  `grnTotalItemsQty` int(11) DEFAULT NULL,
  `issuedqty_fromdepo` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `ngrnBranchId_fk` int(11) DEFAULT NULL,
  `issuedToBranch` int(11) DEFAULT '0',
  `centralGRNNum` varchar(25) DEFAULT NULL,
  `centralGrnId` int(11) DEFAULT '0',
  `invoiceDate` datetime DEFAULT NULL,
  `nCommertialStatus` int(11) DEFAULT '0',
  `commercialStatusUpdatedDate` datetime DEFAULT NULL,
  `commercialStatusUpdatedBy` int(11) DEFAULT NULL,
  `statusRemarks` text,
  `centralMainTransferId` int(11) DEFAULT NULL,
  `centralMainGrnId` int(11) DEFAULT '0',
  `accountsStatusId` int(11) DEFAULT NULL,
  `accountsRemarks` text,
  `accountsUserId` int(11) DEFAULT NULL,
  `accountsDate` datetime DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `po_from_depo_fk` int(11) DEFAULT NULL,
  `n_poid_from_branch_fk` int(11) DEFAULT '0',
  `grn_from_depo_fk` int(11) DEFAULT NULL,
  `grn_from_branch_fk` int(11) DEFAULT '0',
  PRIMARY KEY (`aGrnId_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_grn_for_po_orders_temp_mq`
--


--
-- Table structure for table `tbl_item_availability`
--

CREATE TABLE `tbl_item_availability` (
  `itemAvailId` int(11) NOT NULL AUTO_INCREMENT,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `itemratemasterID` int(11) DEFAULT NULL,
  `available_qty` int(11) DEFAULT NULL,
  `previous_avail_qty` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemAvailId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_item_availability`
--


--
-- Table structure for table `tbl_item_availability_stock_corrn_history`
--

CREATE TABLE `tbl_item_availability_stock_corrn_history` (
  `itemAvailId_st_crn` int(11) NOT NULL AUTO_INCREMENT,
  `nitemAvailId` int(11) NOT NULL,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `itemratemasterID` int(11) DEFAULT NULL,
  `available_qty` int(11) DEFAULT NULL,
  `previous_avail_qty` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemAvailId_st_crn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_item_availability_stock_corrn_history`
--


--
-- Table structure for table `tbl_item_grn_availability`
--

CREATE TABLE `tbl_item_grn_availability` (
  `itemAvailId` int(11) NOT NULL AUTO_INCREMENT,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `grnID` int(11) DEFAULT NULL,
  `grn_number` varchar(150) DEFAULT NULL,
  `itemratemasterID` int(11) DEFAULT NULL,
  `available_qty` int(11) DEFAULT NULL,
  `previous_avail_qty` int(11) DEFAULT NULL,
  `discountPercentIfRemainUnsold` float DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemAvailId`),
  KEY `fr_key_1_idx` (`itemID`),
  CONSTRAINT `fr_key_1` FOREIGN KEY (`itemID`) REFERENCES `tbl_mst_items_details` (`aItemId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_item_grn_availability`
--


--
-- Table structure for table `tbl_item_grn_availability_stock_corrn_history`
--

CREATE TABLE `tbl_item_grn_availability_stock_corrn_history` (
  `itemAvailId_st_crn` int(11) NOT NULL AUTO_INCREMENT,
  `nitemAvailId` int(11) NOT NULL,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `grnID` int(11) DEFAULT NULL,
  `grn_number` varchar(150) DEFAULT NULL,
  `itemratemasterID` int(11) DEFAULT NULL,
  `available_qty` int(11) DEFAULT NULL,
  `previous_avail_qty` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemAvailId_st_crn`),
  KEY `fr_key_1_idx` (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_item_grn_availability_stock_corrn_history`
--


--
-- Table structure for table `tbl_item_grn_module_expire_availability`
--

CREATE TABLE `tbl_item_grn_module_expire_availability` (
  `itemAvail_expire_Id` int(11) NOT NULL AUTO_INCREMENT,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `grnID` int(11) DEFAULT NULL,
  `grn_number` varchar(150) DEFAULT NULL,
  `batchNo` varchar(45) DEFAULT NULL,
  `expiryDate` datetime DEFAULT NULL,
  `itemratemasterID` int(11) DEFAULT NULL,
  `available_qty` int(11) DEFAULT NULL,
  `previous_avail_qty` int(11) DEFAULT NULL,
  `discountPercentIfRemainUnsold` float DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemAvail_expire_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_item_grn_module_expire_availability`
--


--
-- Table structure for table `tbl_item_grn_module_expire_availability_stock_corrn_history`
--

CREATE TABLE `tbl_item_grn_module_expire_availability_stock_corrn_history` (
  `itemAvail_expire_Id_st_crn` int(11) NOT NULL AUTO_INCREMENT,
  `nitemAvail_expire_Id` int(11) NOT NULL,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `grnID` int(11) DEFAULT NULL,
  `grn_number` varchar(150) DEFAULT NULL,
  `batchNo` varchar(45) DEFAULT NULL,
  `expiryDate` datetime DEFAULT NULL,
  `itemratemasterID` int(11) DEFAULT NULL,
  `available_qty` int(11) DEFAULT NULL,
  `previous_avail_qty` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemAvail_expire_Id_st_crn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_item_grn_module_expire_availability_stock_corrn_history`
--


--
-- Table structure for table `tbl_item_rate_master`
--

CREATE TABLE `tbl_item_rate_master` (
  `aitemrateId` int(11) NOT NULL AUTO_INCREMENT,
  `nSupplierId_fk` int(11) NOT NULL,
  `nItemId_fk` int(11) NOT NULL,
  `iscustomerorconsumer` int(11) NOT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `ndepotId` int(11) DEFAULT NULL,
  `iscompanyorsupplier` int(11) DEFAULT '0',
  `companyassupplierId` int(11) DEFAULT '0',
  `branchassupplierId` int(11) DEFAULT '0',
  `purchasePrice` float DEFAULT NULL,
  `nhsnId_fk` int(11) DEFAULT NULL,
  `sellingPrice` float DEFAULT NULL,
  `MRP` float DEFAULT NULL,
  `freeQty` float DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aitemrateId`),
  KEY `fk_tbl_item_rate_master_1_idx` (`nSupplierId_fk`),
  KEY `fk_tbl_item_rate_master_2_idx` (`nItemId_fk`),
  KEY `fk_tbl_item_rate_master_3_idx` (`ndepotId`),
  KEY `fk_tbl_item_rate_master_4_idx` (`nBranchId`),
  KEY `fk_tbl_item_rate_master_5_idx` (`nUserId_fk`),
  KEY `fk_tbl_item_rate_master_6_idx` (`nhsnId_fk`),
  CONSTRAINT `fk_tbl_item_rate_master_2` FOREIGN KEY (`nItemId_fk`) REFERENCES `tbl_mst_items_details` (`aItemId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_item_rate_master_5` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_item_rate_master_6` FOREIGN KEY (`nhsnId_fk`) REFERENCES `tbl_mst_hsn_codes` (`aHsnId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_item_rate_master`
--


--
-- Table structure for table `tbl_medicine_template_each_details`
--

CREATE TABLE `tbl_medicine_template_each_details` (
  `templateEachId` int(11) NOT NULL AUTO_INCREMENT,
  `templateId` int(11) NOT NULL,
  `medicineId` int(11) NOT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`templateEachId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_medicine_template_each_details`
--


--
-- Table structure for table `tbl_medicine_template_main_details`
--

CREATE TABLE `tbl_medicine_template_main_details` (
  `templateId` int(11) NOT NULL AUTO_INCREMENT,
  `templateName` varchar(250) NOT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`templateId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_medicine_template_main_details`
--


--
-- Table structure for table `tbl_modules`
--

CREATE TABLE `tbl_modules` (
  `aModuleId` int(11) NOT NULL AUTO_INCREMENT,
  `priority` float NOT NULL,
  `moduleName` varchar(100) DEFAULT NULL,
  `moduleNameForMobile` varchar(100) DEFAULT NULL,
  `isHavingSubModule` int(11) DEFAULT NULL,
  `isMobileOrWeb` int(11) DEFAULT NULL,
  `moduleId` int(11) DEFAULT NULL COMMENT 'Module Id refers to aModuleId of same table',
  `fnOnClick` varchar(250) NOT NULL COMMENT 'This is the function name that will be triggered (in js) on click of a link in jsp',
  `moduleIcon` varchar(45) DEFAULT NULL,
  `fnOnClickForMobile` varchar(250) DEFAULT NULL,
  `moduleIconForMobile` varchar(100) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isDeleted` int(11) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  `iscompanyorcustomerUser` int(11) DEFAULT NULL,
  PRIMARY KEY (`aModuleId`),
  UNIQUE KEY `moduleName_UNIQUE` (`moduleName`),
  KEY `fk_tblModules_1_idx` (`nUserId`),
  CONSTRAINT `fk_tblModules_1` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbl_modules`
--

INSERT INTO `tbl_modules` VALUES (25,1,'Masters',NULL,1,1,1,'dsf','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(26,2,'Lists',NULL,1,1,1,'dsf','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(27,3,'Lists2',NULL,0,1,1,'dsadf','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(28,2,'Purchase Order',NULL,1,1,1,' ','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(29,3,'Indent',NULL,1,1,1,'','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(30,4,'Sales',NULL,1,1,1,'fa fa-tasks','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(31,5,'Reports',NULL,1,1,1,'dsadf','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(32,6,'Inventory',NULL,1,1,1,'fa fa-tasks','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(33,7,'Goods Return',NULL,1,1,1,'fa fa-tasks','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(34,8,'Audit',NULL,1,1,1,'fa fa-tasks','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,0),(35,9,'Supplier Payment',NULL,1,1,1,'fa fa-tasks','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,0);

--
-- Table structure for table `tbl_mst_bank`
--

CREATE TABLE `tbl_mst_bank` (
  `aBankId_pk` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aBankId_pk`),
  KEY `fk_tbl_mst_bank_1_idx` (`nUserId_fk`),
  CONSTRAINT `fk_tbl_mst_bank_1` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_bank`
--

INSERT INTO `tbl_mst_bank` VALUES (0,'SBI','BankDesc','2018-10-30 19:38:00','2018-10-30 19:38:00',1,0,70);

--
-- Table structure for table `tbl_mst_barcode_config_details`
--

CREATE TABLE `tbl_mst_barcode_config_details` (
  `aBarCodeId` int(11) NOT NULL AUTO_INCREMENT,
  `depoId` int(11) DEFAULT NULL,
  `isconsumerorcustomer` int(11) DEFAULT NULL,
  `consumerorcustomerid` int(11) DEFAULT NULL,
  `nCategoryIdFk` int(11) DEFAULT NULL,
  `categoryIdShortCode` varchar(45) DEFAULT NULL,
  `barcodeCurrentValue` varchar(45) DEFAULT NULL,
  `barcodePreviseValue` varchar(45) DEFAULT NULL,
  `barcodeLength` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`aBarCodeId`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_barcode_config_details`
--

INSERT INTO `tbl_mst_barcode_config_details` VALUES (1,1,NULL,NULL,1,'T1','342','337','9','2019-01-23 16:32:44','2019-01-23 16:32:44',1,0),(2,1,NULL,NULL,3,'FR','61','56','10','2019-01-23 18:04:57','2019-01-23 18:04:57',1,0),(3,2,NULL,NULL,1,'T2','20','10','9','2019-01-23 16:32:44','2019-01-23 16:32:44',1,0),(4,1,NULL,NULL,4,'SND1','3012','2512','9','2019-01-23 16:32:44','2019-01-23 16:32:44',1,0),(5,2,NULL,NULL,4,'SND2','10','0','9','2019-01-23 16:32:44','2019-01-23 16:32:44',1,0),(6,1,NULL,NULL,2,'L1','1871','1869','9','2019-01-23 16:32:44','2019-01-23 16:32:44',1,0),(7,2,NULL,NULL,2,'L2','0','0','9','2019-01-23 16:32:44','2019-01-23 16:32:44',1,0);

--
-- Table structure for table `tbl_mst_brands`
--

CREATE TABLE `tbl_mst_brands` (
  `aBrandTypeId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `brandName` varchar(100) DEFAULT NULL,
  `nCategoryId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aBrandTypeId_pk`),
  KEY `fk_tbl_mst_brands_1_idx` (`nCategoryId_fk`),
  CONSTRAINT `fk_tbl_mst_brands_1` FOREIGN KEY (`nCategoryId_fk`) REFERENCES `tbl_mst_category` (`aCategoryId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_brands`
--


--
-- Table structure for table `tbl_mst_business_type`
--

CREATE TABLE `tbl_mst_business_type` (
  `aBusinessId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(1) DEFAULT '1',
  `isDeleted` tinyint(1) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aBusinessId_pk`),
  KEY `fk_tbl_mst_business_type_1_idx` (`nUserId_fk`),
  CONSTRAINT `fk_tbl_mst_business_type_1` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_business_type`
--

INSERT INTO `tbl_mst_business_type` VALUES (1,'Business Name','Business Desc','2018-10-30 19:41:20','2018-10-30 19:41:20',1,0,70);

--
-- Table structure for table `tbl_mst_categories`
--

CREATE TABLE `tbl_mst_categories` (
  `aPharmaCatId` int(11) NOT NULL AUTO_INCREMENT,
  `categoryName` varchar(100) DEFAULT NULL,
  `categoryTypeId` tinyint(4) DEFAULT '2',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aPharmaCatId`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_categories`
--

INSERT INTO `tbl_mst_categories` VALUES (7,'Tablet',2,'2018-12-22 14:47:50','2018-12-22 14:47:50',1,0,1),(8,'Eye Drop',2,'2018-12-26 11:39:28','2018-12-26 11:39:28',1,0,1),(9,'Ointment',2,'2018-12-26 11:39:28','2018-12-26 11:39:28',1,0,1),(10,'Injection',2,'2018-12-26 11:39:28','2018-12-26 11:39:28',1,0,1);

--
-- Table structure for table `tbl_mst_category`
--

CREATE TABLE `tbl_mst_category` (
  `aCategoryId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `CategoryCode` varchar(100) DEFAULT NULL,
  `CategoryName` varchar(100) DEFAULT NULL,
  `isBatchNum_Expiey_Category` tinyint(4) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aCategoryId_pk`),
  KEY `fk_tbl_mst_category_1_idx` (`nUserId_fk`),
  CONSTRAINT `fk_tbl_mst_category_1` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_category`
--

INSERT INTO `tbl_mst_category` VALUES (1,'TAB','Tablets',1,'2018-12-22 14:47:50','2018-12-22 14:47:50',1,0,75),(2,'ED','Eye Drops',1,'2018-12-26 11:39:28','2018-12-26 11:39:28',1,0,75),(3,'OINT','Ointments',1,'2018-12-26 11:39:28','2018-12-26 11:39:28',1,0,75),(4,'INJ','Injection',1,'2018-12-26 11:39:28','2018-12-26 11:39:28',1,0,75),(5,'OTH','Others',1,'2018-12-26 11:39:28','2018-12-26 11:39:28',1,0,75),(6,'SYP','Syrup',1,'2019-03-19 17:36:42','2019-03-19 17:36:42',1,0,75),(7,'CAP','Capsule',1,'2019-03-19 17:36:42','2019-03-19 17:36:42',1,0,75);

--
-- Table structure for table `tbl_mst_category_item_fields`
--

CREATE TABLE `tbl_mst_category_item_fields` (
  `aCatItemId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `nCategoryId_fk` int(11) NOT NULL,
  `Fieldname` varchar(100) NOT NULL,
  `Fielddescription` varchar(255) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(1) DEFAULT '1',
  `isDeleted` tinyint(1) DEFAULT '0',
  `isDefaultSourceForNaming` int(11) DEFAULT '1',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aCatItemId_pk`),
  KEY `fk_tbl_mst_category_item_fields_1_idx` (`nCategoryId_fk`),
  KEY `fk_tbl_mst_category_item_fields_2_idx` (`nUserId_fk`),
  CONSTRAINT `fk_tbl_mst_category_item_fields_1` FOREIGN KEY (`nCategoryId_fk`) REFERENCES `tbl_mst_category` (`aCategoryId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_category_item_fields_2` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_category_item_fields`
--

INSERT INTO `tbl_mst_category_item_fields` VALUES (1,1,'Name','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(2,1,'Strength ','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(3,1,'Volume','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(4,2,'Name','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(5,2,'Strength ','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(6,2,'Volume','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(7,3,'Name','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(8,3,'Strength ','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(9,3,'Volume','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(10,4,'Name','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(11,4,'Strength ','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(12,4,'Volume','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(13,5,'Name','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(14,5,'Strength ','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(15,5,'Volume','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(16,6,'Name','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(17,6,'Strength ','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(18,6,'Volume','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(19,7,'Name','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(20,7,'Strength ','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1),(21,7,'Volume','desc','2019-03-06 13:41:14','2019-03-06 13:41:14',1,0,1,1);

--
-- Table structure for table `tbl_mst_countries`
--

CREATE TABLE `tbl_mst_countries` (
  `aCountryId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `countryCode` varchar(10) DEFAULT NULL,
  `countryShortCode` varchar(10) DEFAULT NULL,
  `countryName` varchar(100) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aCountryId_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_countries`
--

INSERT INTO `tbl_mst_countries` VALUES (1,'1','A1','India','2018-10-30 16:17:03','2018-10-30 16:17:03',1,0,1),(2,'CCode','CSCode','CName','2018-10-30 19:52:16','2018-10-30 19:52:16',1,0,70);

--
-- Table structure for table `tbl_mst_creditcompanies`
--

CREATE TABLE `tbl_mst_creditcompanies` (
  `aCompanyId` int(11) NOT NULL AUTO_INCREMENT,
  `companyName` text,
  `companyAddress` varchar(1000) DEFAULT NULL,
  `companyPhNo` varchar(45) DEFAULT NULL,
  `companyTINNo` varchar(250) DEFAULT NULL,
  `companyRegistrationNo` varchar(250) DEFAULT NULL,
  `companyEstDate` datetime DEFAULT NULL,
  `InsertedDate` datetime DEFAULT NULL,
  `LastModifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(1) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aCompanyId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_creditcompanies`
--

INSERT INTO `tbl_mst_creditcompanies` VALUES (1,'CGHS',NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL),(2,'EGSH',NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL),(3,'AirIndia',NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL);

--
-- Table structure for table `tbl_mst_global_configurations`
--

CREATE TABLE `tbl_mst_global_configurations` (
  `aConfig_fk` int(11) NOT NULL AUTO_INCREMENT,
  `configKey` varchar(250) DEFAULT NULL,
  `configValue` varchar(250) DEFAULT NULL,
  `configDescription` varchar(250) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isDeleted` int(11) DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `branchId` int(11) DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aConfig_fk`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_global_configurations`
--

INSERT INTO `tbl_mst_global_configurations` VALUES (1,'isBarcodeMandatory_ForAll_Functions','1','1 => Barcode is mandatpry for sale, issue and return functionalities, 0 => Not mandatory',NULL,NULL,1,0,NULL,1,NULL),(2,'BarcodeMandatory_Categories','Frames, Sunglasses','Comma seperated value of barcode mandatory categories',NULL,NULL,1,0,NULL,1,NULL),(3,'Maximum_Fitting_Charge','500','Maximum fitting charge allowed',NULL,NULL,1,0,2,1,NULL),(4,'showCostPriceInOpticalPendingOrderReport','1','1 => show cost price , 0 = > dont show',NULL,NULL,1,0,2,1,NULL),(5,'categoriesWhichPatientCategoryDiscountApplied','1,2,3','Comma seperated values of categories for optical sale each item discount',NULL,NULL,1,0,2,1,NULL),(6,'mst_permisable_level_config','1','1 => Percentage, 2 => Qty, 3 => Amount Value',NULL,NULL,1,0,2,1,NULL),(7,'mst_permisable_level','20','configuration value',NULL,NULL,1,0,2,1,NULL),(8,'audit_without_approval_show_for_reconcile','1','1 => show audit in reconsile without approval 0 => dont show',NULL,NULL,1,0,2,1,NULL);

--
-- Table structure for table `tbl_mst_goods_purchase_return_reasons_details`
--

CREATE TABLE `tbl_mst_goods_purchase_return_reasons_details` (
  `areasonId` int(11) NOT NULL AUTO_INCREMENT,
  `reason` text,
  `isCustomerOrConsumer` int(11) DEFAULT NULL,
  `customerOrConsumerId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`areasonId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_goods_purchase_return_reasons_details`
--

INSERT INTO `tbl_mst_goods_purchase_return_reasons_details` VALUES (1,'reason1',NULL,NULL,'2019-02-28 11:29:15','2019-02-28 11:29:15',1,0,70),(2,'reason2',NULL,NULL,'2019-02-28 11:29:15','2019-02-28 11:29:15',1,0,70),(3,'reason3',NULL,NULL,'2019-02-28 11:29:15','2019-02-28 11:29:15',1,0,70);

--
-- Table structure for table `tbl_mst_hsn_codes`
--

CREATE TABLE `tbl_mst_hsn_codes` (
  `aHsnId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `hsnCode` varchar(15) DEFAULT NULL,
  `gstPercentage` double DEFAULT NULL,
  `sgstPercentage` double DEFAULT NULL,
  `cgstPercentage` double DEFAULT NULL,
  `igstPercentage` double DEFAULT NULL,
  `nCountryId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` int(11) DEFAULT '1',
  `isDeleted` int(11) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aHsnId_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_hsn_codes`
--

INSERT INTO `tbl_mst_hsn_codes` VALUES (1,'2804',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',1,0,NULL),(2,'3002',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(3,'3004',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(4,'3006',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(5,'3926',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(6,'4801',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(7,'4817',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(8,'8415',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(9,'8443',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(10,'8471',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(11,'8501',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(12,'8502',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(13,'8504',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(14,'8517',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(15,'8521',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(16,'8532',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(17,'8536',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(18,'8543',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(19,'8713',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(20,'9002',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(21,'9003',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(22,'9004',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(23,'9018',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(24,'9020',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',1,0,NULL),(25,'9021',12,6,6,12,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,NULL),(26,'3040',5,2.5,2.5,5,1,'2018-07-03 14:43:53','2018-07-03 14:43:53',0,0,0),(27,'2160',18,9,9,18,1,'2019-03-18 11:23:58','2019-03-18 11:23:58',0,0,NULL),(28,'2106',18,9,9,18,1,'2019-07-09 12:34:10','2019-07-09 12:34:10',0,0,NULL),(29,'900015000',12,6,6,12,1,'2019-03-20 10:45:35','2019-03-20 10:45:35',0,0,NULL);

--
-- Table structure for table `tbl_mst_items_details`
--

CREATE TABLE `tbl_mst_items_details` (
  `aItemId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `nCategoryId_fk` int(11) DEFAULT NULL,
  `itemCode` varchar(45) DEFAULT NULL,
  `itemShortCode` varchar(30) DEFAULT NULL,
  `itemName` varchar(250) DEFAULT NULL,
  `customer_desc` varchar(150) DEFAULT NULL,
  `nBrandTypeId_fk` int(11) DEFAULT NULL,
  `itemDescription` varchar(500) DEFAULT NULL,
  `nItemManufacturerId_fk` int(11) DEFAULT NULL,
  `taxability` int(11) DEFAULT NULL,
  `genericName` text,
  `nHsnCodeId_fk` int(11) DEFAULT NULL,
  `ROL` int(11) DEFAULT '0',
  `ROQ` int(11) DEFAULT '0',
  `UNIT` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `isSingleOrMultiUser_Product` int(11) DEFAULT '0',
  `max_times_multiproduct_to_use` int(11) DEFAULT '0',
  `freeQtyAvail` int(11) DEFAULT NULL,
  `remarks` tinytext,
  `nTypeId_fk` int(11) DEFAULT '0',
  `productComposition` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`aItemId_pk`),
  KEY `fk_tbl_mst_items_details_1_idx` (`nCategoryId_fk`),
  KEY `fk_tbl_mst_items_details_2_idx` (`nItemManufacturerId_fk`),
  KEY `fk_tbl_mst_items_details_3_idx` (`nUserId_fk`),
  KEY `fk_tbl_mst_items_details_4_idx` (`nHsnCodeId_fk`),
  CONSTRAINT `fk_tbl_mst_items_details_1` FOREIGN KEY (`nCategoryId_fk`) REFERENCES `tbl_mst_category` (`aCategoryId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_items_details_2` FOREIGN KEY (`nItemManufacturerId_fk`) REFERENCES `tbl_mst_manufacturers` (`aManufacturerId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_items_details_3` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_items_details_4` FOREIGN KEY (`nHsnCodeId_fk`) REFERENCES `tbl_mst_hsn_codes` (`aHsnId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_items_details`
--


--
-- Table structure for table `tbl_mst_items_details_fields`
--

CREATE TABLE `tbl_mst_items_details_fields` (
  `aItemfieldId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `nItemId_fk` int(11) DEFAULT NULL,
  `nCatItemId_fk` int(11) DEFAULT NULL,
  `CatFieldName` varchar(250) DEFAULT NULL,
  `CatFieldValue` varchar(500) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `isSingleOrMultiUser_Product` int(11) DEFAULT '0',
  `max_times_multiproduct_to_use` int(11) DEFAULT '0',
  PRIMARY KEY (`aItemfieldId_pk`),
  KEY `fk_tbl_mst_items_details_fields_1_idx` (`nItemId_fk`),
  KEY `fk_tbl_mst_items_details_fields_2_idx` (`nCatItemId_fk`),
  KEY `fk_tbl_mst_items_details_fields_3_idx` (`nUserId_fk`),
  CONSTRAINT `fk_tbl_mst_items_details_fields_1` FOREIGN KEY (`nItemId_fk`) REFERENCES `tbl_mst_items_details` (`aItemId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_items_details_fields_2` FOREIGN KEY (`nCatItemId_fk`) REFERENCES `tbl_mst_category_item_fields` (`aCatItemId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_items_details_fields_3` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_items_details_fields`
--


--
-- Table structure for table `tbl_mst_lens_fitters_details`
--

CREATE TABLE `tbl_mst_lens_fitters_details` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `fitter_name` varchar(500) DEFAULT NULL,
  `isCustomerOrConsumer` int(11) DEFAULT NULL,
  `customerOrConsumerId` int(11) DEFAULT NULL,
  `isDefault_Fitter_ForBranch` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_lens_fitters_details`
--


--
-- Table structure for table `tbl_mst_lestypes`
--

CREATE TABLE `tbl_mst_lestypes` (
  `aLensType_id` int(11) NOT NULL AUTO_INCREMENT,
  `lensTypeName` varchar(250) DEFAULT NULL,
  `lensTypeCode` varchar(250) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aLensType_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_lestypes`
--


--
-- Table structure for table `tbl_mst_locations`
--

CREATE TABLE `tbl_mst_locations` (
  `aLocationId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `locnCode` varchar(10) DEFAULT NULL,
  `locnShortCode` varchar(10) DEFAULT NULL,
  `locnName` varchar(100) DEFAULT NULL,
  `nCountryId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aLocationId_pk`),
  KEY `fk_tbl_mst_locations_1_idx` (`nCountryId_fk`),
  CONSTRAINT `fk_tbl_mst_locations_1` FOREIGN KEY (`nCountryId_fk`) REFERENCES `tbl_mst_countries` (`aCountryId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_locations`
--

INSERT INTO `tbl_mst_locations` VALUES (1,'MH','MH','Maharastra',1,'2018-10-30 19:52:38','2018-10-30 19:52:38',1,0,70),(2,'GJ','GJ','Gujrat',1,'2018-10-30 19:52:38','2018-10-30 19:52:38',1,0,70);

--
-- Table structure for table `tbl_mst_manufacturers`
--

CREATE TABLE `tbl_mst_manufacturers` (
  `aManufacturerId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `manufacturerCode` varchar(10) DEFAULT NULL,
  `manufacturerShortCode` varchar(10) DEFAULT NULL,
  `manufacturerName` varchar(250) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aManufacturerId_pk`),
  KEY `fk_tbl_mst_manufacturers_1_idx` (`nUserId_fk`),
  CONSTRAINT `fk_tbl_mst_manufacturers_1` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_manufacturers`
--


--
-- Table structure for table `tbl_mst_module_features`
--

CREATE TABLE `tbl_mst_module_features` (
  `aMstModFeatureId` int(11) NOT NULL AUTO_INCREMENT,
  `nModuleId` int(11) DEFAULT NULL,
  `nSubModuleId` int(11) DEFAULT NULL,
  `featureName` varchar(100) DEFAULT NULL,
  `isDeleted` tinyint(4) DEFAULT '0',
  `isActive` tinyint(4) DEFAULT '1',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aMstModFeatureId`),
  UNIQUE KEY `featureName` (`featureName`),
  KEY `fk_tbl_mst_module_features_1_idx` (`nModuleId`),
  KEY `fk_tbl_mst_module_features_2_idx` (`nSubModuleId`),
  CONSTRAINT `fk_tbl_mst_module_features_1` FOREIGN KEY (`nModuleId`) REFERENCES `tbl_modules` (`aModuleId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_module_features_2` FOREIGN KEY (`nSubModuleId`) REFERENCES `tbl_sub_modules` (`aSubModuleId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_module_features`
--


--
-- Table structure for table `tbl_mst_opticalsale_main_status`
--

CREATE TABLE `tbl_mst_opticalsale_main_status` (
  `aStatusId` int(11) NOT NULL AUTO_INCREMENT,
  `Status_Name` varchar(250) DEFAULT NULL,
  `InsertedDate` datetime DEFAULT NULL,
  `LastModifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(1) DEFAULT NULL,
  `isDelete` tinyint(1) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aStatusId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_opticalsale_main_status`
--

INSERT INTO `tbl_mst_opticalsale_main_status` VALUES (1,'InProgress','2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(2,'Ready for Delivery','2018-12-17 10:51:12','2018-12-17 10:51:12',1,0,1),(3,'Delivered','2018-12-17 10:51:12','2018-12-17 10:51:12',1,0,1),(4,'Canceled','2018-12-17 10:51:12','2018-12-17 10:51:12',0,0,1);

--
-- Table structure for table `tbl_mst_opticalsale_payment_mode`
--

CREATE TABLE `tbl_mst_opticalsale_payment_mode` (
  `aPaymentId` int(11) NOT NULL AUTO_INCREMENT,
  `PaymentModeName` varchar(250) DEFAULT NULL,
  `isDefault` tinyint(1) DEFAULT NULL,
  `InsertedDate` datetime DEFAULT NULL,
  `LastModifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(1) DEFAULT NULL,
  `isDelete` tinyint(1) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aPaymentId`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_opticalsale_payment_mode`
--

INSERT INTO `tbl_mst_opticalsale_payment_mode` VALUES (1,'Cash',1,'2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(2,'Card',1,'2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(3,'Cheque',0,'2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(4,'NEFT',0,NULL,NULL,1,0,1),(5,'PayTm',0,NULL,NULL,1,0,1);

--
-- Table structure for table `tbl_mst_opticalsale_payment_type`
--

CREATE TABLE `tbl_mst_opticalsale_payment_type` (
  `aPaymentTypeId` int(11) NOT NULL AUTO_INCREMENT,
  `PaymentTypeName` varchar(250) DEFAULT NULL,
  `isDefault` tinyint(1) DEFAULT NULL,
  `InsertedDate` datetime DEFAULT NULL,
  `LastModifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(1) DEFAULT NULL,
  `isDelete` tinyint(1) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aPaymentTypeId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_opticalsale_payment_type`
--

INSERT INTO `tbl_mst_opticalsale_payment_type` VALUES (1,'Advance',0,'2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(2,'Second Advance',0,'2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(3,'Amt On Delivery',0,'2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(4,'Cancelled',0,'2018-11-29 13:05:52','2018-11-29 13:05:52',0,0,1);

--
-- Table structure for table `tbl_mst_opticalsale_sub_status`
--

CREATE TABLE `tbl_mst_opticalsale_sub_status` (
  `aSubStatusId` int(11) NOT NULL AUTO_INCREMENT,
  `nStatusId` int(10) DEFAULT NULL,
  `SubStatus_Name` varchar(250) DEFAULT NULL,
  `InsertedDate` datetime DEFAULT NULL,
  `LastModifiedDate` datetime DEFAULT NULL,
  `isActive` tinyint(1) DEFAULT NULL,
  `isDelete` tinyint(1) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aSubStatusId`),
  KEY `nStatusId` (`nStatusId`),
  CONSTRAINT `tbl_mst_opticalsale_sub_status_ibfk_1` FOREIGN KEY (`nStatusId`) REFERENCES `tbl_mst_opticalsale_main_status` (`aStatusId`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_opticalsale_sub_status`
--

INSERT INTO `tbl_mst_opticalsale_sub_status` VALUES (1,1,'Open','2018-11-29 13:05:52','2018-11-29 13:05:52',1,0,1),(2,1,'Send to Supplier','2018-12-17 10:52:31','2018-12-17 10:52:31',1,0,1),(3,1,'Received from Supplier','2018-12-17 10:52:31','2018-12-17 10:52:31',1,0,1),(4,1,'Fitting','2018-12-17 10:52:31','2018-12-17 10:52:31',1,0,1),(5,1,'Quality Check','2018-12-17 10:52:31','2018-12-17 10:52:31',1,0,1),(6,1,'Rejected','2018-12-17 10:52:31','2018-12-17 10:52:31',1,0,1);

--
-- Table structure for table `tbl_mst_paymodes`
--

CREATE TABLE `tbl_mst_paymodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payModeName` varchar(250) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_paymodes`
--

INSERT INTO `tbl_mst_paymodes` VALUES (1,'CASH','2019-12-11 11:30:17','2019-12-11 11:30:17',1,0),(2,'NEFT','2019-12-11 11:30:17','2019-12-11 11:30:17',1,0),(3,'IMPS','2019-12-11 11:30:17','2019-12-11 11:30:17',1,0),(4,'REFT','2019-12-11 16:48:22','2019-12-11 16:48:22',1,0),(5,'PhonePy','2019-12-11 18:09:26','2019-12-11 18:09:26',1,0);

--
-- Table structure for table `tbl_mst_pharmacredit_company_details`
--

CREATE TABLE `tbl_mst_pharmacredit_company_details` (
  `aCompanyId` int(11) NOT NULL AUTO_INCREMENT,
  `companyName` text,
  `companyAddress` varchar(1000) DEFAULT NULL,
  `companyPhNo` varchar(45) DEFAULT NULL,
  `companyTINNo` varchar(250) DEFAULT NULL,
  `companyRegistrationNo` varchar(250) DEFAULT NULL,
  `companyEstDate` datetime DEFAULT NULL,
  `InsertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(1) DEFAULT '1',
  `nUserId` int(11) DEFAULT '0',
  PRIMARY KEY (`aCompanyId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_pharmacredit_company_details`
--


--
-- Table structure for table `tbl_mst_registration_type`
--

CREATE TABLE `tbl_mst_registration_type` (
  `aRegTypeId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `regTypeShortCode` varchar(10) DEFAULT NULL,
  `regTypeName` varchar(100) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aRegTypeId_pk`),
  KEY `fk_tbl_mst_registration_type_1_idx` (`nUserId_fk`),
  CONSTRAINT `fk_tbl_mst_registration_type_1` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_registration_type`
--

INSERT INTO `tbl_mst_registration_type` VALUES (1,'RegCode','RegName','2018-10-30 19:59:46','2018-10-30 19:59:46',1,0,70);

--
-- Table structure for table `tbl_mst_supplier`
--

CREATE TABLE `tbl_mst_supplier` (
  `aSupplierId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `nToDepoId` int(11) DEFAULT NULL,
  `nToBranchId` varchar(150) DEFAULT NULL,
  `code` varchar(20) NOT NULL,
  `Supplier_name` varchar(100) NOT NULL,
  `supplyLeadTime` int(11) DEFAULT '0',
  `nBusiness_typeId_fk` int(11) DEFAULT NULL,
  `registration_type` int(11) DEFAULT NULL,
  `GSTIN` varchar(200) DEFAULT NULL,
  `IGSTN` varchar(200) DEFAULT NULL,
  `emailId` varchar(255) NOT NULL,
  `po_emailId` varchar(255) NOT NULL,
  `pan_no` varchar(30) NOT NULL,
  `drugl_no` varchar(200) NOT NULL,
  `nLocationId_fk` int(11) DEFAULT NULL,
  `credit_period` int(11) DEFAULT NULL,
  `com_address` varchar(255) DEFAULT NULL,
  `po_address` varchar(255) DEFAULT NULL,
  `contact_person` varchar(50) NOT NULL,
  `designation` varchar(50) DEFAULT NULL,
  `mobile_no` varchar(10) DEFAULT NULL,
  `phone_no` varchar(15) DEFAULT NULL,
  `alternative_no` varchar(15) DEFAULT NULL,
  `nBankId_fk` int(11) DEFAULT NULL,
  `beneficiary_name` varchar(50) DEFAULT NULL,
  `ac_no` varchar(20) DEFAULT NULL,
  `ifsc` varchar(20) DEFAULT NULL,
  `chequeFavourName` varchar(100) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(1) DEFAULT '1',
  `isDeleted` tinyint(1) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `cin_Number` varchar(45) DEFAULT NULL,
  `fax_Number` varchar(45) DEFAULT NULL,
  `agreementExpiryDate` datetime DEFAULT NULL,
  `gstnRegistered` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`aSupplierId_pk`),
  KEY `fk_tbl_mst_supplier_1_idx` (`nBusiness_typeId_fk`),
  KEY `fk_tbl_mst_supplier_2_idx` (`registration_type`),
  KEY `fk_tbl_mst_supplier_3_idx` (`nLocationId_fk`),
  KEY `fk_tbl_mst_supplier_5_idx` (`nUserId_fk`),
  KEY `fk_tbl_mst_supplier_4` (`nBankId_fk`),
  CONSTRAINT `fk_tbl_mst_supplier_1` FOREIGN KEY (`nBusiness_typeId_fk`) REFERENCES `tbl_mst_business_type` (`aBusinessId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_supplier_2` FOREIGN KEY (`registration_type`) REFERENCES `tbl_mst_registration_type` (`aRegTypeId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_supplier_3` FOREIGN KEY (`nLocationId_fk`) REFERENCES `tbl_mst_locations` (`aLocationId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_supplier_4` FOREIGN KEY (`nBankId_fk`) REFERENCES `tbl_mst_bank` (`aBankId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_mst_supplier_5` FOREIGN KEY (`nUserId_fk`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_supplier`
--


--
-- Table structure for table `tbl_mst_supplier_frametypewise_rol`
--

CREATE TABLE `tbl_mst_supplier_frametypewise_rol` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `nBranchId` int(11) DEFAULT NULL,
  `nSupplierId` int(11) DEFAULT NULL,
  `nFrameTypeId` int(11) DEFAULT NULL,
  `rol_qty` int(11) DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_supplier_frametypewise_rol`
--


--
-- Table structure for table `tbl_mst_taxablility`
--

CREATE TABLE `tbl_mst_taxablility` (
  `ataxableId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` int(11) DEFAULT '1',
  `isDeleted` int(11) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`ataxableId_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_taxablility`
--

INSERT INTO `tbl_mst_taxablility` VALUES (1,'Taxable','2018-10-30 16:03:18','2018-10-30 16:03:18',1,0,1),(2,'Nil Rated','2018-10-30 16:03:18','2018-10-30 16:03:18',1,0,1),(3,'Exempt','2018-10-30 16:03:18','2018-10-30 16:03:18',1,0,1),(4,'Non GST','2018-10-30 16:03:18','2018-10-30 16:03:18',1,0,1);

--
-- Table structure for table `tbl_mst_types`
--

CREATE TABLE `tbl_mst_types` (
  `aTypeId_pk` int(11) NOT NULL AUTO_INCREMENT,
  `typeName` varchar(100) DEFAULT NULL,
  `nCategoryId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `discount_percentage` float DEFAULT '0',
  PRIMARY KEY (`aTypeId_pk`),
  KEY `fk_tbl_mst_types_1_idx` (`nCategoryId_fk`),
  CONSTRAINT `fk_tbl_mst_types_1` FOREIGN KEY (`nCategoryId_fk`) REFERENCES `tbl_mst_category` (`aCategoryId_pk`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_types`
--


--
-- Table structure for table `tbl_mst_visioninst`
--

CREATE TABLE `tbl_mst_visioninst` (
  `aRow_ID` int(11) NOT NULL AUTO_INCREMENT,
  `tInst_Name` varchar(500) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedTime` datetime DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`aRow_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_visioninst`
--

INSERT INTO `tbl_mst_visioninst` VALUES (1,'Distance only ','2017-01-10 16:27:52','2017-01-10 16:27:52',1),(2,'NV only','2017-01-10 16:27:57','2017-01-10 16:27:57',1),(3,'Constant wear','2017-01-10 16:28:14','2017-01-10 16:28:14',1),(4,'Vocation Use','2017-01-10 16:28:46','2017-01-10 16:28:46',1);

--
-- Table structure for table `tbl_mst_visionmaterial`
--

CREATE TABLE `tbl_mst_visionmaterial` (
  `aRow_ID` int(11) NOT NULL AUTO_INCREMENT,
  `tMaterial_Name` varchar(500) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedTime` datetime DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`aRow_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_visionmaterial`
--

INSERT INTO `tbl_mst_visionmaterial` VALUES (1,'White','2017-01-06 18:51:47','2017-01-06 18:51:47',1),(2,'Tint','2017-01-06 18:52:04','2017-01-06 18:52:04',1),(3,'Photochromic','2017-01-06 18:52:11','2017-01-06 18:52:11',1),(4,'AR Coat','2017-03-24 19:21:47','2017-03-24 19:21:47',1);

--
-- Table structure for table `tbl_mst_visiontype`
--

CREATE TABLE `tbl_mst_visiontype` (
  `aRow_ID` int(11) NOT NULL AUTO_INCREMENT,
  `tType_Name` varchar(500) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedTime` datetime DEFAULT NULL,
  `is_active` int(11) DEFAULT NULL,
  PRIMARY KEY (`aRow_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_mst_visiontype`
--

INSERT INTO `tbl_mst_visiontype` VALUES (1,'SV','2016-04-07 11:40:28','2016-04-07 11:40:28',0),(2,'CR','2016-04-07 11:40:42','2016-04-07 11:40:42',0),(3,'Kyrptok','2016-04-07 11:41:02','2016-04-07 11:41:02',1),(4,'ARC','2016-04-07 11:41:15','2016-04-07 11:41:15',0),(5,'Executive','2016-04-07 11:41:23','2016-04-07 11:41:23',1),(6,'Photo Chromic','2016-04-07 11:41:38','2016-04-07 11:41:38',0),(7,'D-Type','2017-01-06 18:54:21','2017-01-06 18:54:21',1),(8,'Progressive','2017-01-06 18:54:52','2017-01-06 18:54:52',1),(9,'Monofocal','2017-01-06 18:55:14','2017-01-06 18:55:14',1);

--
-- Table structure for table `tbl_mst_waivers_category`
--

CREATE TABLE `tbl_mst_waivers_category` (
  `a_waivers_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `categorycol_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`a_waivers_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_waivers_category`
--


--
-- Table structure for table `tbl_mst_waivers_sub_category`
--

CREATE TABLE `tbl_mst_waivers_sub_category` (
  `a_sub_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `sub_category_name` varchar(45) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `opti_waiver_percentage` float DEFAULT NULL,
  `is_active` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`a_sub_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_mst_waivers_sub_category`
--


--
-- Table structure for table `tbl_optical_sale_each_returns`
--

CREATE TABLE `tbl_optical_sale_each_returns` (
  `aOpticalSaleEachReturnId` int(11) NOT NULL AUTO_INCREMENT,
  `nOpticalSaleMainReturnId` int(11) DEFAULT NULL,
  `nOrderId` int(11) DEFAULT NULL,
  `nOrderDetailsId` int(11) DEFAULT NULL,
  `nGrnId` int(11) DEFAULT '0',
  `nCategoryId` int(11) DEFAULT '0',
  `nBranchId` int(11) DEFAULT '0',
  `nItemId` int(11) DEFAULT NULL,
  `itemName` text,
  `batchNo` varchar(150) DEFAULT NULL,
  `returnQty` int(11) DEFAULT NULL,
  `inStockQty` int(11) DEFAULT NULL,
  `isToIntock` tinyint(4) DEFAULT NULL,
  `returnDate` datetime DEFAULT NULL,
  `returnAmount` float DEFAULT NULL,
  `scannedBarcodeId` int(11) DEFAULT NULL,
  `scannedBarcodeNo` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aOpticalSaleEachReturnId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_optical_sale_each_returns`
--


--
-- Table structure for table `tbl_optical_sale_main_returns`
--

CREATE TABLE `tbl_optical_sale_main_returns` (
  `aOpticalSaleReturnId` int(11) NOT NULL AUTO_INCREMENT,
  `returnNumber` varchar(100) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `nOrderId` int(11) DEFAULT NULL,
  `nPayMainId` int(11) DEFAULT '0',
  `returnDate` datetime DEFAULT NULL,
  `returnAmount` float DEFAULT NULL,
  `returnMode` tinyint(4) DEFAULT NULL,
  `patientDetails` text,
  `saleOrderNumber` varchar(100) DEFAULT NULL,
  `billNumber` varchar(100) DEFAULT NULL,
  `returnRemarks` text,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  `nPatient_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`aOpticalSaleReturnId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_optical_sale_main_returns`
--


--
-- Table structure for table `tbl_opticalsale_main_payment`
--

CREATE TABLE `tbl_opticalsale_main_payment` (
  `PayId` int(11) NOT NULL AUTO_INCREMENT,
  `OrderId` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `nPatient_Id` int(11) DEFAULT NULL,
  `TotalQty` int(45) DEFAULT '0',
  `Dis_Perc` int(45) DEFAULT '0',
  `Dis_Amount` float DEFAULT '0',
  `mustPayAdvance_Amount` float DEFAULT '0',
  `asOfPaidAmt` float DEFAULT '0',
  `returnedAmt` float DEFAULT '0',
  `billNumber` varchar(100) DEFAULT NULL,
  `PaymentTypeId` int(11) DEFAULT NULL,
  `PaymentTypeName` varchar(150) DEFAULT NULL,
  `TotalAmt` float DEFAULT '0',
  `grandTotal` float DEFAULT '0',
  `balanceAmt` float DEFAULT '0',
  `is_Credit_Company_Transction` int(10) DEFAULT NULL,
  `Credit_Company_Id` int(10) DEFAULT NULL,
  `Credit_Company_Amt` float DEFAULT NULL,
  `Credit_Company_Status` int(45) DEFAULT NULL,
  `billerId` int(11) DEFAULT '0',
  `Roundoff_Value` varchar(10) DEFAULT '0',
  `Fitting_Charge` float DEFAULT NULL,
  `Cust_GSTIN` varchar(45) DEFAULT NULL,
  `Fitting_by` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` int(11) DEFAULT '1',
  `isDelete` tinyint(1) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`PayId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_opticalsale_main_payment`
--


--
-- Table structure for table `tbl_opticalsale_order_status_transcation`
--

CREATE TABLE `tbl_opticalsale_order_status_transcation` (
  `aOrderTransId` int(11) NOT NULL AUTO_INCREMENT,
  `OrderId` int(10) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `nPatientId` int(10) DEFAULT NULL,
  `MainStatusId` int(10) DEFAULT NULL,
  `SubStatusId` int(10) DEFAULT NULL,
  `MainStatusName` varchar(150) DEFAULT NULL,
  `SubStatusName` varchar(150) DEFAULT NULL,
  `Remarks` text,
  `isCurrentActive` int(10) DEFAULT NULL,
  `Status_Date` date DEFAULT NULL,
  `InsertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(1) DEFAULT '1',
  `isDelete` tinyint(1) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aOrderTransId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_opticalsale_order_status_transcation`
--


--
-- Table structure for table `tbl_opticalsale_orderdetails`
--

CREATE TABLE `tbl_opticalsale_orderdetails` (
  `ODId` int(11) NOT NULL AUTO_INCREMENT,
  `nOrderId` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `OrderCode` varchar(100) DEFAULT NULL,
  `nCategoryId` int(11) DEFAULT NULL,
  `lensTypeId` int(11) DEFAULT NULL,
  `lensTypeName` varchar(250) DEFAULT NULL,
  `ItemCode` varchar(45) DEFAULT NULL,
  `ItemName` varchar(500) DEFAULT NULL,
  `ItemId` int(11) DEFAULT NULL,
  `saleType_GRN_Or_Open` int(11) DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `grn_id` int(11) DEFAULT NULL,
  `expiryDate` varchar(100) DEFAULT NULL,
  `batchNo` varchar(250) DEFAULT NULL,
  `expDateDBForm` datetime DEFAULT NULL,
  `is_Company_Or_Supplier` int(10) DEFAULT NULL,
  `company_As_Supplier_Id` int(10) DEFAULT NULL,
  `nSuppId` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `nQty` int(11) DEFAULT NULL,
  `returnedQty` int(11) DEFAULT '0',
  `non_in_stock_issuing_qty` int(11) DEFAULT '0',
  `unitSellingPrice` float DEFAULT NULL,
  `cancelledQty` int(11) DEFAULT '0',
  `discountPercent` float DEFAULT NULL,
  `discountAmount` float DEFAULT NULL,
  `item_gst_percent` float DEFAULT NULL,
  `item_gst_rs` float DEFAULT NULL,
  `item_sgst_percent` float DEFAULT NULL,
  `item_sgst_rs` float DEFAULT NULL,
  `item_cgst_percent` float DEFAULT NULL,
  `item_cgst_rs` float DEFAULT NULL,
  `item_igst_percent` float DEFAULT NULL,
  `item_igst_rs` float DEFAULT NULL,
  `item_total_amt_without_gst` float DEFAULT NULL,
  `item_total_amt_with_gst` float DEFAULT NULL,
  `totAmount` float DEFAULT NULL,
  `hsnCode` varchar(250) DEFAULT NULL,
  `isPoRaised` int(11) DEFAULT NULL,
  `poMainId` int(11) DEFAULT NULL,
  `poNumber` varchar(150) DEFAULT NULL,
  `poMailSent_ToSupplier` int(11) DEFAULT NULL,
  `numOfTimesMailSent` int(11) DEFAULT NULL,
  `scannedBarcodeId` int(11) DEFAULT NULL,
  `scannedBarcodeNo` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `nUserId` int(11) DEFAULT NULL,
  `isActive` int(11) DEFAULT '1',
  `isDelete` tinyint(1) DEFAULT '0',
  `customerItemDecName` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ODId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_opticalsale_orderdetails`
--


--
-- Table structure for table `tbl_opticalsale_orders`
--

CREATE TABLE `tbl_opticalsale_orders` (
  `OrderId` int(11) NOT NULL AUTO_INCREMENT,
  `nBranchId` int(11) DEFAULT NULL,
  `nOrderAutoId` varchar(150) DEFAULT NULL,
  `returnIds` varchar(150) DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `is_Depo_Id` int(11) DEFAULT NULL,
  `is_Branch_Id` int(11) DEFAULT NULL,
  `saleType_GRN_Or_Open` int(11) DEFAULT NULL,
  `PatientType_EMR_Or_NEMR` int(11) DEFAULT NULL,
  `nPatient_Id` int(10) DEFAULT NULL,
  `nAppoint_Id` int(11) DEFAULT NULL,
  `orderDate` datetime DEFAULT NULL,
  `actDeliveredDate` datetime DEFAULT NULL,
  `expDeliveryDate` datetime DEFAULT NULL,
  `iS_Credit_Company_Transction` int(10) DEFAULT NULL,
  `Credit_Company_Id` int(10) DEFAULT NULL,
  `medicineRxType` int(11) DEFAULT NULL,
  `Current_Main_Status_Id` int(10) DEFAULT NULL,
  `Current_Sub_Statusa_Id` int(10) DEFAULT NULL,
  `isPatientFrame` int(11) DEFAULT NULL,
  `billerId` int(11) DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` int(11) DEFAULT '1',
  `isDelete` tinyint(1) DEFAULT '0',
  `userId` int(11) DEFAULT NULL,
  PRIMARY KEY (`OrderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_opticalsale_orders`
--


--
-- Table structure for table `tbl_opticalsale_payment_each_tran`
--

CREATE TABLE `tbl_opticalsale_payment_each_tran` (
  `PayId_each_tran` int(11) NOT NULL AUTO_INCREMENT,
  `bill_num` varchar(150) DEFAULT NULL,
  `OrderId` int(11) DEFAULT NULL,
  `OrderPaymentId` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `nPatient_Id` int(11) DEFAULT NULL,
  `PaymentDate` date DEFAULT NULL,
  `PaymentTypeId` int(11) DEFAULT NULL,
  `PaymentTypeName` varchar(150) DEFAULT NULL,
  `TotalAmt_paid` float DEFAULT NULL,
  `billerId` int(11) DEFAULT '0',
  `Roundoff_Value` varchar(10) DEFAULT '0',
  `Fitting_Charge` float DEFAULT NULL,
  `Cust_GSTIN` varchar(45) DEFAULT NULL,
  `Fitting_by` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` int(11) DEFAULT '1',
  `isDelete` tinyint(1) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`PayId_each_tran`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_opticalsale_payment_each_tran`
--


--
-- Table structure for table `tbl_pharma_patients_each_sales`
--

CREATE TABLE `tbl_pharma_patients_each_sales` (
  `aSalesEachId` int(11) NOT NULL AUTO_INCREMENT,
  `nMainSalesId` int(11) DEFAULT NULL,
  `discountAmount` float DEFAULT NULL,
  `nItemExpiryAvailId` int(11) DEFAULT NULL,
  `nItemId` int(11) DEFAULT NULL,
  `nItemRateMstId` int(11) DEFAULT NULL,
  `soldQty` int(11) DEFAULT NULL,
  `waiverAmt` float DEFAULT NULL,
  `taxAmount` float DEFAULT NULL,
  `amtWithoutTax` float DEFAULT NULL,
  `amtWithTax` float DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `purchasePrice` float DEFAULT '0',
  PRIMARY KEY (`aSalesEachId`),
  KEY `nItemExpiryAvailId` (`nItemExpiryAvailId`),
  KEY `nItemId` (`nItemId`),
  KEY `nItemRateMstId` (`nItemRateMstId`),
  KEY `nMainSalesId` (`nMainSalesId`),
  KEY `nBranchId` (`nBranchId`),
  CONSTRAINT `tbl_pharma_patients_each_sales_ibfk_1` FOREIGN KEY (`nItemExpiryAvailId`) REFERENCES `tbl_item_grn_module_expire_availability` (`itemAvail_expire_Id`),
  CONSTRAINT `tbl_pharma_patients_each_sales_ibfk_2` FOREIGN KEY (`nItemId`) REFERENCES `tbl_mst_items_details` (`aItemId_pk`),
  CONSTRAINT `tbl_pharma_patients_each_sales_ibfk_3` FOREIGN KEY (`nItemRateMstId`) REFERENCES `tbl_item_rate_master` (`aitemrateId`),
  CONSTRAINT `tbl_pharma_patients_each_sales_ibfk_4` FOREIGN KEY (`nMainSalesId`) REFERENCES `tbl_pharma_patients_main_sales` (`aSalesId`),
  CONSTRAINT `tbl_pharma_patients_each_sales_ibfk_5` FOREIGN KEY (`nBranchId`) REFERENCES `tbl_customer_branch_details` (`abranchId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_pharma_patients_each_sales`
--


--
-- Table structure for table `tbl_pharma_patients_main_sales`
--

CREATE TABLE `tbl_pharma_patients_main_sales` (
  `aSalesId` int(11) NOT NULL AUTO_INCREMENT,
  `saleOrderNumber` varchar(100) DEFAULT NULL,
  `nPatientTypeId` int(11) DEFAULT '1',
  `nPharmaPatientId` int(11) DEFAULT NULL,
  `nPatientId` int(11) DEFAULT NULL,
  `nAppointId` int(11) DEFAULT '0',
  `totalDiscountAmount` float DEFAULT NULL,
  `totalWaiverAmt` float DEFAULT NULL,
  `totalTaxAmount` float DEFAULT NULL,
  `totalAmtWithoutTax` float DEFAULT NULL,
  `totalAmtWithTax` float DEFAULT NULL,
  `totalcashAmt` float DEFAULT '0',
  `totalcardAmt` float DEFAULT '0',
  `otherPayType` int(11) DEFAULT '0',
  `otherPayAmt` float DEFAULT '0',
  `totalroundOff` float DEFAULT '0',
  `totalPaidAmt` float DEFAULT '0',
  `otherPayAmtRefNumber` varchar(100) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `creditCompanyAmt` float DEFAULT '0',
  `creditCompanyId` int(11) DEFAULT NULL,
  `billingDoneBy` varchar(250) DEFAULT NULL,
  `verifiedDoneBy` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`aSalesId`),
  KEY `nBranchId` (`nBranchId`),
  KEY `tbl_pharma_patients_main_sales_ibfk_2_idx` (`nPharmaPatientId`),
  CONSTRAINT `tbl_pharma_patients_main_sales_ibfk_1` FOREIGN KEY (`nBranchId`) REFERENCES `tbl_customer_branch_details` (`abranchId`),
  CONSTRAINT `tblpharmapatient` FOREIGN KEY (`nPharmaPatientId`) REFERENCES `tbl_pharma_patientsregistration` (`aPatientPharmaId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_pharma_patients_main_sales`
--


--
-- Table structure for table `tbl_pharma_patientsregistration`
--

CREATE TABLE `tbl_pharma_patientsregistration` (
  `aPatientPharmaId` int(11) NOT NULL AUTO_INCREMENT,
  `patienttype` int(11) DEFAULT '1',
  `nEmrPatientId` int(11) DEFAULT '0',
  `nEmrApptId` int(11) DEFAULT NULL,
  `emrPatientCode` varchar(100) DEFAULT NULL,
  `patientName` varchar(100) DEFAULT NULL,
  `patientCode` varchar(100) DEFAULT NULL,
  `patientAge` tinyint(4) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `phoneNumber` varchar(10) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `address` text,
  `nConsultantId` int(11) DEFAULT NULL,
  `consultantName` varchar(100) DEFAULT NULL,
  `nCategoryId` int(11) DEFAULT NULL,
  `nSubCategoryId` int(11) DEFAULT NULL,
  `nSourceId` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `isSold` tinyint(4) DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `constultant_RegNo` varchar(100) DEFAULT NULL,
  `medicinGenRemarks` varchar(250) DEFAULT NULL,
  `advices` text,
  PRIMARY KEY (`aPatientPharmaId`),
  KEY `nBranchId` (`nBranchId`),
  CONSTRAINT `tbl_pharma_patientsregistration_ibfk_1` FOREIGN KEY (`nBranchId`) REFERENCES `tbl_customer_branch_details` (`abranchId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_pharma_patientsregistration`
--


--
-- Table structure for table `tbl_pharma_sales_each_returns`
--

CREATE TABLE `tbl_pharma_sales_each_returns` (
  `aSaleEachReturnId` int(11) NOT NULL AUTO_INCREMENT,
  `nMainReturnId` int(11) DEFAULT NULL,
  `nItemId` int(11) DEFAULT NULL,
  `nItemExpireAvailId` int(11) DEFAULT NULL,
  `returnedQty` int(11) DEFAULT NULL,
  `returnedAmt` double DEFAULT NULL,
  `inStock` tinyint(4) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aSaleEachReturnId`),
  KEY `nMainReturnId` (`nMainReturnId`),
  KEY `nUserId` (`nUserId`),
  CONSTRAINT `tbl_pharma_sales_each_returns_ibfk_1` FOREIGN KEY (`nMainReturnId`) REFERENCES `tbl_pharma_sales_main_returns` (`aSaleReturnId`),
  CONSTRAINT `tbl_pharma_sales_each_returns_ibfk_2` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`auserId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_pharma_sales_each_returns`
--


--
-- Table structure for table `tbl_pharma_sales_main_returns`
--

CREATE TABLE `tbl_pharma_sales_main_returns` (
  `aSaleReturnId` int(11) NOT NULL AUTO_INCREMENT,
  `saleReturnNumber` varchar(50) DEFAULT NULL,
  `nMainSaleId` int(11) DEFAULT NULL,
  `mainSaleNumber` varchar(50) DEFAULT NULL,
  `nReturnReasonId` int(11) DEFAULT '0',
  `returnReasonsText` text,
  `saleDate` datetime DEFAULT NULL,
  `returnDate` datetime DEFAULT NULL,
  `returnAmount` double DEFAULT NULL,
  `nReturnedToPatientId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aSaleReturnId`),
  KEY `nMainSaleId` (`nMainSaleId`),
  KEY `nReturnedToPatientId` (`nReturnedToPatientId`),
  KEY `nUserId` (`nUserId`),
  CONSTRAINT `tbl_pharma_sales_main_returns_ibfk_1` FOREIGN KEY (`nMainSaleId`) REFERENCES `tbl_pharma_patients_main_sales` (`aSalesId`),
  CONSTRAINT `tbl_pharma_sales_main_returns_ibfk_2` FOREIGN KEY (`nReturnedToPatientId`) REFERENCES `tbl_pharma_patientsregistration` (`aPatientPharmaId`),
  CONSTRAINT `tbl_pharma_sales_main_returns_ibfk_3` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`auserId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_pharma_sales_main_returns`
--


--
-- Table structure for table `tbl_po_each_item_details`
--

CREATE TABLE `tbl_po_each_item_details` (
  `apoeachid` int(11) NOT NULL AUTO_INCREMENT,
  `npoid_fk` int(11) DEFAULT NULL,
  `typeOfRequest` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `po_from_depo_fk` int(11) DEFAULT NULL,
  `n_poeachid_from_branch_fk` int(11) DEFAULT NULL,
  `is_company_or_supplier` int(11) DEFAULT NULL,
  `po_to_company_or_supplier_id_fk` int(11) DEFAULT NULL,
  `po_to_supplier_id_fk` int(11) DEFAULT NULL,
  `item_id_fk` int(11) DEFAULT NULL,
  `item_code` varchar(50) DEFAULT NULL,
  `item_name` tinytext,
  `categoryid_fk` int(11) DEFAULT NULL,
  `item_hsn_code` varchar(45) DEFAULT NULL,
  `item_free_qty` int(11) DEFAULT NULL,
  `item_order_qty` int(11) DEFAULT NULL,
  `item_unit_price` float DEFAULT NULL,
  `item_discount_percent` float DEFAULT NULL,
  `item_discount_rs` float DEFAULT NULL,
  `item_gst_percent` float DEFAULT NULL,
  `item_gst_rs` float DEFAULT NULL,
  `item_sgst_percent` float DEFAULT NULL,
  `item_sgst_rs` float DEFAULT NULL,
  `item_cgst_percent` float DEFAULT NULL,
  `item_cgst_rs` float DEFAULT NULL,
  `item_igst_percent` float DEFAULT NULL,
  `item_igst_rs` float DEFAULT NULL,
  `item_total_amt_without_gst` float DEFAULT NULL,
  `item_total_amt_with_gst` float DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `poItemStatusId` tinyint(4) DEFAULT NULL,
  `nSupplierItemMapid` int(11) DEFAULT NULL,
  `poItemStatusText` text,
  `po_auth_reason` text,
  `po_authorized_by_fk` int(11) DEFAULT NULL,
  `po_authorized_date` datetime DEFAULT NULL,
  `po_rejected_reason` text,
  `po_rejected_by_fk` int(11) DEFAULT NULL,
  `po_rejected_date` datetime DEFAULT NULL,
  `po_forceclose_reason` text,
  `po_forceclose_by_fk` int(11) DEFAULT NULL,
  `po_forceclose_date` datetime DEFAULT NULL,
  `po_item_brchtrans_status_id` tinyint(4) DEFAULT '0',
  `po_item_brchtrans_status_text` varchar(45) DEFAULT NULL,
  `po_item_brchtrans_reqqty` int(11) DEFAULT NULL,
  `po_item_brchtrans_req_depo_auth_qty` int(11) DEFAULT NULL,
  PRIMARY KEY (`apoeachid`),
  KEY `fk_tbl_ygo_po_each_item_details_1_idx` (`npoid_fk`),
  KEY `fk_tbl_ygo_po_each_item_details_2_idx` (`isconsumer_or_iscustomer`),
  KEY `fk_tbl_ygo_po_each_item_details_3_idx` (`po_from_depo_fk`),
  KEY `fk_tbl_ygo_po_each_item_details_4_idx` (`is_company_or_supplier`),
  KEY `fk_tbl_ygo_po_each_item_details_5_idx` (`po_to_company_or_supplier_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_po_each_item_details`
--


--
-- Table structure for table `tbl_po_main_details`
--

CREATE TABLE `tbl_po_main_details` (
  `apoid` int(11) NOT NULL AUTO_INCREMENT,
  `typeOfRequest` int(11) DEFAULT '1',
  `po_number` varchar(30) DEFAULT NULL,
  `categoryid_fk` int(11) DEFAULT NULL,
  `po_type_fk` int(11) DEFAULT NULL,
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `po_from_depo_fk` int(11) DEFAULT NULL,
  `n_poid_from_branch_fk` int(11) DEFAULT '0',
  `po_date` datetime DEFAULT NULL,
  `po_raisedby_fk` int(11) DEFAULT NULL,
  `po_billingaddress` text,
  `po_deliveryaddress` text,
  `po_del_addr_iscustomerorconsumer` int(11) DEFAULT NULL,
  `po_del_addr_customerorconsumerid` int(11) DEFAULT NULL,
  `is_company_or_supplier` int(11) DEFAULT NULL,
  `po_to_company_or_supplier_id_fk` int(11) DEFAULT NULL,
  `po_to_supplier_id_fk` int(11) DEFAULT NULL,
  `gstType` int(11) DEFAULT '0',
  `po_expexted_delivery_date` datetime DEFAULT NULL,
  `po_total_items` int(11) DEFAULT NULL,
  `po_total_items_qty` int(11) DEFAULT NULL,
  `po_total_amount` float DEFAULT NULL,
  `po_total_tax_amount` float DEFAULT NULL,
  `po_total_po_aoumt_withtax` float DEFAULT NULL,
  `po_status` int(11) DEFAULT NULL,
  `po_status_text` varchar(45) DEFAULT NULL,
  `reasonForAuthOrRej` text,
  `po_authorized_by_fk` int(11) DEFAULT NULL,
  `po_authorized_date` datetime DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  `poRemarkss` text,
  `po_auth_reason` text,
  `po_rejected_reason` text,
  `po_rejected_by_fk` int(11) DEFAULT NULL,
  `po_rejected_date` datetime DEFAULT NULL,
  `po_forceclose_reason` text,
  `po_forceclose_by_fk` int(11) DEFAULT NULL,
  `po_forceclose_date` datetime DEFAULT NULL,
  `payment_type_id` int(11) DEFAULT NULL COMMENT '										nu		nu',
  `payment_type_name` varchar(45) DEFAULT NULL,
  `payment_days` int(11) DEFAULT NULL,
  PRIMARY KEY (`apoid`),
  KEY `fk_tbl_ygo_po_main-details_1_idx` (`categoryid_fk`),
  KEY `fk_tbl_ygo_po_main-details_2_idx` (`isconsumer_or_iscustomer`),
  KEY `fk_tbl_ygo_po_main-details_3_idx` (`po_from_depo_fk`),
  KEY `fk_tbl_ygo_po_main-details_4_idx` (`is_company_or_supplier`),
  KEY `fk_tbl_ygo_po_main-details_5_idx` (`po_to_company_or_supplier_id_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_po_main_details`
--


--
-- Table structure for table `tbl_stock_audit_creation_details`
--

CREATE TABLE `tbl_stock_audit_creation_details` (
  `audit_id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) DEFAULT NULL,
  `branch_name` varchar(250) DEFAULT NULL,
  `audit_name` varchar(250) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `audity_id` int(11) DEFAULT NULL,
  `audity_name` varchar(250) DEFAULT NULL,
  `auditor_id` int(11) DEFAULT NULL,
  `auditor_name` varchar(250) DEFAULT NULL,
  `category_Id` int(11) DEFAULT NULL,
  `remarks` text,
  `audit_status_id` int(11) DEFAULT NULL,
  `audit_status_text` varchar(100) DEFAULT NULL,
  `audit_stock_corr_appr_status_id` int(11) DEFAULT NULL,
  `audit_stock_corr_appr_status_text` varchar(100) DEFAULT NULL,
  `audit_stock_corr_appoved_by_id` int(11) DEFAULT NULL,
  `audit_stock_corr_appoved_by_name` varchar(250) DEFAULT NULL,
  `is_within_permisable_level` float(100,0) DEFAULT NULL,
  `mst_permisable_level_config` int(11) DEFAULT NULL,
  `mst_permisable_level_percentage` float(100,0) DEFAULT NULL,
  `mst_permisable_level_qty` int(11) DEFAULT NULL,
  `mst_permisable_level_amount` float(100,0) DEFAULT NULL,
  `deviation_qty` int(11) DEFAULT NULL,
  `deviation_value` float(100,0) DEFAULT NULL,
  `deviation_percentage` float(100,0) DEFAULT NULL,
  `deviation_qty_more_than_inventory` int(11) DEFAULT NULL,
  `deviation_value_more_than_inventory` float DEFAULT NULL,
  `audit_stock_total_qty` int(11) DEFAULT NULL,
  `audit_stock_total_value` float(100,0) DEFAULT NULL,
  `audit_stock_inventory_total_qty` int(11) DEFAULT NULL,
  `audit_stock_inventory_total_value` float(100,0) DEFAULT NULL,
  `audit_inventory_corrn_status_id` int(11) DEFAULT NULL,
  `audit_inventory_corrn_status_text` varchar(100) DEFAULT NULL,
  `remarks_complete` text,
  `nUserId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`audit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_audit_creation_details`
--


--
-- Table structure for table `tbl_stock_audit_inventory_corrn_creation_details`
--

CREATE TABLE `tbl_stock_audit_inventory_corrn_creation_details` (
  `audit_id_st_corrn` int(11) NOT NULL AUTO_INCREMENT,
  `naudit_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `branch_name` varchar(250) DEFAULT NULL,
  `category_Id` int(11) DEFAULT NULL,
  `remarks` text,
  `deviation_qty` int(11) DEFAULT NULL,
  `deviation_value` float(100,0) DEFAULT NULL,
  `deviation_percentage` float(100,0) DEFAULT NULL,
  `deviation_qty_more_than_inventory` int(11) DEFAULT NULL,
  `deviation_value_more_than_inventory` float DEFAULT NULL,
  `audit_stock_total_qty` int(11) DEFAULT NULL,
  `audit_stock_total_value` float(100,0) DEFAULT NULL,
  `audit_stock_inventory_total_qty` int(11) DEFAULT NULL,
  `audit_stock_inventory_total_value` float(100,0) DEFAULT NULL,
  `nUserId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`audit_id_st_corrn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_audit_inventory_corrn_creation_details`
--


--
-- Table structure for table `tbl_stock_audit_inventory_corrn_item_barcode_details`
--

CREATE TABLE `tbl_stock_audit_inventory_corrn_item_barcode_details` (
  `a_stk_aud_barcode_id_st_corrn` int(11) NOT NULL AUTO_INCREMENT,
  `n_audit_id` int(11) DEFAULT NULL,
  `n_stock_corrn_id` int(11) DEFAULT NULL,
  `isconsumerorcustomer` int(11) DEFAULT NULL,
  `consumerorcustomerid` int(11) DEFAULT NULL,
  `n_barcode_det_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `grn_id` int(11) DEFAULT NULL,
  `barcode_number` varchar(150) DEFAULT NULL,
  `nUserId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`a_stk_aud_barcode_id_st_corrn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_audit_inventory_corrn_item_barcode_details`
--


--
-- Table structure for table `tbl_stock_audit_inventory_corrn_item_grn_and_expiry_all_details`
--

CREATE TABLE `tbl_stock_audit_inventory_corrn_item_grn_and_expiry_all_details` (
  `a_stk_aud_item_id_st_corrn` int(11) NOT NULL AUTO_INCREMENT,
  `n_audit_id` int(11) DEFAULT NULL,
  `n_stock_corrn_id` int(11) DEFAULT NULL,
  `isconsumerorcustomer` int(11) DEFAULT NULL,
  `consumerorcustomerid` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `item_name` varchar(500) DEFAULT NULL,
  `item_code` varchar(500) DEFAULT NULL,
  `grn_id` int(11) DEFAULT NULL,
  `grn_number` varchar(150) DEFAULT NULL,
  `category_Id` int(11) DEFAULT NULL,
  `is_Batch_Num_Exp_Category` tinyint(4) DEFAULT NULL,
  `item_avail_or_avail_batch_exp_id` int(11) DEFAULT NULL,
  `item_inventory_avail_qty` int(11) DEFAULT NULL,
  `item_inventory_audit_actual_qty` int(11) DEFAULT NULL,
  `item_qty_difference` int(11) DEFAULT NULL,
  `nUserId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`a_stk_aud_item_id_st_corrn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_audit_inventory_corrn_item_grn_and_expiry_all_details`
--


--
-- Table structure for table `tbl_stock_audit_item_barcode_details`
--

CREATE TABLE `tbl_stock_audit_item_barcode_details` (
  `a_stk_aud_barcode_id` int(11) NOT NULL AUTO_INCREMENT,
  `n_audit_id` int(11) DEFAULT NULL,
  `isconsumerorcustomer` int(11) DEFAULT NULL,
  `consumerorcustomerid` int(11) DEFAULT NULL,
  `is_barcode_audit_done` int(11) DEFAULT NULL,
  `n_barcode_det_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `grn_id` int(11) DEFAULT NULL,
  `is_barcode_audit_valid` int(11) DEFAULT NULL,
  `barcode_number` varchar(150) DEFAULT NULL,
  `nUserId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`a_stk_aud_barcode_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_audit_item_barcode_details`
--


--
-- Table structure for table `tbl_stock_audit_item_grn_and_expiry_all_details`
--

CREATE TABLE `tbl_stock_audit_item_grn_and_expiry_all_details` (
  `a_stk_aud_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `n_audit_id` int(11) DEFAULT NULL,
  `isconsumerorcustomer` int(11) DEFAULT NULL,
  `consumerorcustomerid` int(11) DEFAULT NULL,
  `is_item_audit_done` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `item_name` varchar(500) DEFAULT NULL,
  `item_code` varchar(500) DEFAULT NULL,
  `grn_id` int(11) DEFAULT NULL,
  `grn_number` varchar(150) DEFAULT NULL,
  `category_Id` int(11) DEFAULT NULL,
  `is_Batch_Num_Exp_Category` tinyint(4) DEFAULT NULL,
  `item_avail_or_avail_batch_exp_id` int(11) DEFAULT NULL,
  `item_inventory_avail_qty` int(11) DEFAULT NULL,
  `item_inventory_audit_actual_qty` int(11) DEFAULT NULL,
  `item_qty_difference` int(11) DEFAULT NULL,
  `is_item_audit_valid` int(11) DEFAULT NULL,
  `nUserId_fk` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`a_stk_aud_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_audit_item_grn_and_expiry_all_details`
--


--
-- Table structure for table `tbl_stock_details_daily`
--

CREATE TABLE `tbl_stock_details_daily` (
  `adly_stk_id` int(11) NOT NULL AUTO_INCREMENT,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `depo_Id` int(11) DEFAULT NULL,
  `branch_Id` int(11) DEFAULT NULL,
  `stock_date` date DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `itemName` text,
  `itemCode` varchar(500) DEFAULT NULL,
  `isCompanyOrSupplier` int(11) DEFAULT NULL,
  `vendor_Id` int(11) DEFAULT NULL,
  `vendorName` text,
  `batchNumber` varchar(250) DEFAULT NULL,
  `openingStock` int(11) DEFAULT '0',
  `closingStock` int(11) DEFAULT '0',
  `soldStock` int(11) DEFAULT '0',
  `purchaseStock` int(11) DEFAULT '0',
  `purchaseReturnStock` int(11) DEFAULT '0',
  `salesReturnStock` int(11) DEFAULT '0',
  `purchaseReturnAcceptStock` int(11) DEFAULT '0',
  `saleReturnAcceptStock` int(11) DEFAULT '0',
  `inventory_Corrn_Stock` int(11) DEFAULT '0',
  `openingStock_amt` float DEFAULT '0',
  `closingStock_amt` float DEFAULT '0',
  `soldStock_amt` float DEFAULT '0',
  `purchaseStock_amt` float DEFAULT '0',
  `purchaseReturnStock_amt` float DEFAULT '0',
  `salesReturnStock_amt` float DEFAULT '0',
  `purchaseReturnAcceptStock_amt` float DEFAULT '0',
  `saleReturnAcceptStock_ant` float DEFAULT '0',
  `inventory_Corrn_Stock_amt` float DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`adly_stk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_details_daily`
--


--
-- Table structure for table `tbl_stock_details_daily_stock_corrn_history`
--

CREATE TABLE `tbl_stock_details_daily_stock_corrn_history` (
  `adly_stk_id_st_corrn` int(11) NOT NULL AUTO_INCREMENT,
  `nadly_stk_id` int(11) NOT NULL,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `depo_Id` int(11) DEFAULT NULL,
  `branch_Id` int(11) DEFAULT NULL,
  `stock_date` date DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `itemName` text,
  `itemCode` varchar(500) DEFAULT NULL,
  `isCompanyOrSupplier` int(11) DEFAULT NULL,
  `vendor_Id` int(11) DEFAULT NULL,
  `vendorName` text,
  `batchNumber` varchar(250) DEFAULT NULL,
  `openingStock` int(11) DEFAULT '0',
  `closingStock` int(11) DEFAULT '0',
  `soldStock` int(11) DEFAULT '0',
  `purchaseStock` int(11) DEFAULT '0',
  `purchaseReturnStock` int(11) DEFAULT '0',
  `salesReturnStock` int(11) DEFAULT '0',
  `purchaseReturnAcceptStock` int(11) DEFAULT '0',
  `saleReturnAcceptStock` int(11) DEFAULT '0',
  `openingStock_amt` float DEFAULT '0',
  `closingStock_amt` float DEFAULT '0',
  `soldStock_amt` float DEFAULT '0',
  `purchaseStock_amt` float DEFAULT '0',
  `purchaseReturnStock_amt` float DEFAULT '0',
  `salesReturnStock_amt` float DEFAULT '0',
  `purchaseReturnAcceptStock_amt` float DEFAULT '0',
  `saleReturnAcceptStock_ant` float DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`adly_stk_id_st_corrn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_details_daily_stock_corrn_history`
--


--
-- Table structure for table `tbl_stock_details_monthly`
--

CREATE TABLE `tbl_stock_details_monthly` (
  `amo_stk_id` int(11) NOT NULL AUTO_INCREMENT,
  `Category_Id` int(11) DEFAULT '1',
  `isconsumer_or_iscustomer` int(11) DEFAULT NULL,
  `depo_Id` int(11) DEFAULT NULL,
  `branch_Id` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `itemID` int(11) DEFAULT NULL,
  `openingStock` int(11) DEFAULT '0',
  `closingStock` int(11) DEFAULT '0',
  `soldStock` int(11) DEFAULT '0',
  `purchaseStock` int(11) DEFAULT '0',
  `purchaseReturnStock` int(11) DEFAULT '0',
  `salesReturnStock` int(11) DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`amo_stk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_details_monthly`
--


--
-- Table structure for table `tbl_stock_issue_or_inv_gen_tempsave_details`
--

CREATE TABLE `tbl_stock_issue_or_inv_gen_tempsave_details` (
  `a_si_tempid_pk` int(11) NOT NULL AUTO_INCREMENT,
  `depoId` int(11) DEFAULT NULL,
  `temp_save_date` datetime DEFAULT NULL,
  `branches` varchar(150) DEFAULT NULL,
  `tempData` longtext,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`a_si_tempid_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_stock_issue_or_inv_gen_tempsave_details`
--


--
-- Table structure for table `tbl_sub_module_pemissions`
--

CREATE TABLE `tbl_sub_module_pemissions` (
  `aPermissionId` int(11) NOT NULL AUTO_INCREMENT,
  `permissionName` varchar(500) DEFAULT NULL,
  `displayName` varchar(500) DEFAULT NULL,
  `discription` text,
  `displayOrder` int(11) DEFAULT NULL,
  `nMainModuleId` int(11) DEFAULT NULL,
  `nSubModuleId` int(11) DEFAULT NULL,
  `isActive` int(11) DEFAULT '1',
  `isDeleted` int(11) DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModified` datetime DEFAULT CURRENT_TIMESTAMP,
  `nUserId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aPermissionId`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_sub_module_pemissions`
--

INSERT INTO `tbl_sub_module_pemissions` VALUES (1,'Authorize','Authorize',NULL,NULL,28,31,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(2,'Reject','Reject',NULL,NULL,28,31,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(3,'ForceClose','Force Close',NULL,NULL,28,31,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(4,'otherBranch_DepoStock','OtherBranch DepoStock',NULL,NULL,32,41,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(5,'BranchDashBoard','Branch Dash Board',NULL,NULL,31,39,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(6,'BranchCombinedReport','Branch Combined Report',NULL,NULL,31,40,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(7,'Authorize','Authorize',NULL,NULL,29,35,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(8,'Reject','Reject',NULL,NULL,29,35,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(9,'ForceClose','Force Close',NULL,NULL,29,35,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(10,'UpdatePOMRP','Update PP',NULL,NULL,28,31,1,0,'2019-01-21 13:55:29','2019-01-21 13:55:29',NULL),(11,'isCompletedApprovalPermit','isCompletedApprovalPermit',NULL,NULL,34,71,1,0,'2019-08-01 11:45:53','2019-08-01 11:45:53',NULL),(12,'branchWiseOutStand','Branch Wise OutStand',NULL,NULL,35,77,1,0,'2019-08-01 11:45:53','2019-08-01 11:45:53',NULL),(13,'vendorPayment','Vendor Payment',NULL,NULL,35,77,1,0,'2019-08-01 11:45:53','2019-08-01 11:45:53',NULL),(14,'SupplierWiseOutStand','Supplier Wise OutStand',NULL,NULL,35,77,1,0,'2019-08-01 11:45:53','2019-08-01 11:45:53',NULL),(15,'Edit_Payment_Mode','Edit Payment Mode',NULL,NULL,30,47,1,0,'2019-08-01 11:39:14','2019-08-01 11:39:14',NULL),(16,'Credit Bill','Credit Bill',NULL,NULL,30,47,1,0,'2019-08-14 21:23:40','2019-08-14 21:23:40',NULL),(17,'SalesDiscountEdit','SalesDiscountEdit',NULL,NULL,30,47,1,0,'2022-03-18 12:19:07','2022-03-18 12:19:07',NULL),(18,'ProductMarginReport','ProductMarginReport',NULL,NULL,31,53,1,0,'2022-03-18 12:19:20','2022-03-18 12:19:20',NULL),(19,'Purchase GST Percentage Report','Purchase GST Percentage Report',NULL,NULL,31,87,1,0,'2023-06-06 11:51:18','2023-06-06 11:51:18',NULL);

--
-- Table structure for table `tbl_sub_modules`
--

CREATE TABLE `tbl_sub_modules` (
  `aSubModuleId` int(11) NOT NULL AUTO_INCREMENT,
  `nModuleId` int(11) DEFAULT NULL,
  `priority` float NOT NULL,
  `submoduleName` varchar(100) DEFAULT NULL,
  `submoduleNameForMobile` varchar(100) DEFAULT NULL,
  `isMobileOrWeb` int(11) DEFAULT NULL,
  `fnOnClick` varchar(250) NOT NULL COMMENT 'This is the function name that will be triggered (in js) on click of a link in jsp',
  `submoduleIcon` varchar(45) DEFAULT NULL,
  `fnOnClickForMobile` varchar(250) DEFAULT NULL,
  `submoduleIconForMobile` varchar(100) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isDeleted` int(11) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  `iscompanyorcustomerUser` int(11) DEFAULT NULL,
  PRIMARY KEY (`aSubModuleId`),
  UNIQUE KEY `moduleName_UNIQUE` (`submoduleName`),
  KEY `fk_tblModules_1_idx` (`nUserId`),
  KEY `fk_tblModules_2` (`nModuleId`),
  CONSTRAINT `fk_tblsubModules_1` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tblsubModules_2` FOREIGN KEY (`nModuleId`) REFERENCES `tbl_modules` (`aModuleId`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbl_sub_modules`
--

INSERT INTO `tbl_sub_modules` VALUES (25,25,1.1,'UserCreation','UserCreation',1,'users','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(26,25,1.2,'Category Field Master','Category Field Master',1,'category_field_master','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,0),(28,26,1.2,'UserLists','UserList',NULL,'users','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(29,25,1.3,'Item Registration','Item Registration',1,'itemregistration','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(30,25,1.4,'Item Rate Master','Item Rate Master',1,'itemratemaster','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(31,28,2.1,'Raise PO','Raise PO',1,'raisepurchaseorder','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(32,28,2.2,'GRN','GRN',1,'po_grn','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(33,28,2.3,'Stock Issue','Stock Issue',1,'po_generate_invoice','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(34,28,2.4,'GRN / Accept Invoice','GRN / Accept Invoice',1,'po_grn_accept_invoice','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(35,29,3.1,'Raise Indent','Raise Indent',NULL,'raise_indent','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(37,29,3.2,'Stock Accept','Stock Accept',NULL,'indent_grn_accept_invoice','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(38,30,4.1,'Patient Sale','Patient Sale',NULL,'optical_sale','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,31,1),(39,31,5.1,'Dashboard','Dashboard',NULL,'report_dashboard','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,31,1),(40,31,5.2,'Optical Sale Report','Optical Sale Report',NULL,'report_combinedreport','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,31,1),(41,32,6.1,'Items In Stock','Items In Stock',NULL,'invenorty_itemstock','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(44,33,7.1,'Return Items','Return Items',1,'return_items','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(45,33,7.2,'Accept Return Items','Accept Return Items',1,'accept_return_items','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(46,33,7.3,'Sales Return','Sales Return',1,'salesReturns','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,31,1),(47,30,4.2,'Pharmacy Sale','Pharmacy Sale',NULL,'pharmacysales','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(48,31,5,'Sales Summery Reports','Sales Reports',NULL,'salesreport','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(49,33,7,'Pharma Sales Returns','Pharma Sales Returns',NULL,'salesreturns','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,31,1),(50,NULL,5.4,'Optical Conversion report','Optical Conversion report',NULL,'optical_conversion_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,1,1),(51,NULL,5.5,'Pharmacy Conversion report','Pharmacy Conversion report',NULL,'pharmacy_conversion_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,1,1),(52,31,5.5,'GRN Report','GRN Report',NULL,'grn_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(53,31,5.6,'Sale Report','Pharma Item Sale Report',NULL,'pharmacyitemwisesale_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(54,31,5.7,'Drug  Report','Pharma Drug Details Report',NULL,'pharma_drug_details_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(55,NULL,5.8,'Optical Credit Billing','Optical Credit Billing',NULL,'optical_credit_billing','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(56,31,5.8,'Lens Report','Lense Not In Stock Report',NULL,'lens_not_in_stock_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(57,31,5.9,'Lens Order Report','Item Return Report',NULL,'item_return_report','fa fa-tasks',NULL,NULL,'2019-05-21 20:55:47','2019-05-21 20:55:47',0,0,NULL,1),(58,31,6,'Goods Return Report','Item Return Report',NULL,'branch_return_report','fa fa-tasks',NULL,NULL,'2019-05-21 20:55:47','2019-05-21 20:55:47',1,0,NULL,1),(59,31,6.1,'Stock Issue Report','Stock Issue Report',NULL,'stock_issue_report','fa fa-tasks',NULL,NULL,'2019-05-21 20:55:47','2019-05-21 20:55:47',1,0,NULL,1),(60,25,1.5,'PO Masters','PO Masters',NULL,'po_masters','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(61,31,5.1,'Barcode Inventory Report','Barcode Inventory Report',1,'barcode_inventory_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(62,31,5.12,'Optical Pending Order Report','Optical Pending Order Report',1,'optical_pending_order_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(63,31,5.13,'Product Profit Margin Report','Product Profit Margin Report',1,'product_profit_margin_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(64,31,5.14,'Salesman Productivity Report','Salesman Productivity Report',1,'salesman_productivity_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(65,31,5.15,'Optical Flash Dashboard','Optical Flash Dashboard',NULL,'optical_flash_dashboard','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(66,31,5.16,'Optical Sale GST Report','Optical Sale GST Report',1,'optical_sale_gst_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(67,28,2.5,'Branch Transfers','Branch Transfers',1,'branch_transfer_depo','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(68,31,5.17,'Fast Moving Frames','Fast Moving Frames',1,'fast_moving_frames','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(70,34,1,'Create Audit','Create Audit',NULL,'create_audit','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(71,34,2,'Stock Entry','Stock Entry',NULL,'stock_entry','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(72,34,3,'Stock Reconcile','Stock Reconcile',NULL,'stock_reconcile','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(74,31,5.17,'Audit Report','Audit Report',NULL,'audit_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(75,31,5.18,'Fitters Order Report','Fitters Order Report',NULL,'fitters_order_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(76,31,5.19,'Optical Gross Margin Report','Optical Gross Margin Report',NULL,'optical_gross_margin_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,1),(77,35,5.1,'Supplier Payment','Vendor Payment',NULL,'barcode_age_report','	fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(78,31,5.2,'Supplier Payment Report',NULL,NULL,'vendor_payment','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(79,31,5.18,'Pharmacy Sale GST Report','Pharmacy Sale GST Report',NULL,'pharmacy_sale_gst_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,NULL,NULL,NULL),(85,31,5.3,'Consumption Report','Consumption Report',NULL,'consumption_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(86,31,5.21,'Pharmacy Sale GSTR1 Report','Pharmacy Sale GSTR1 Report',NULL,'pharmacy_sale_gstr1_report','fa fa-tasks',NULL,NULL,NULL,NULL,0,0,NULL,NULL),(87,31,5.21,'Pharmacy GST Reports','Pharmacy GST Reports',NULL,'pharmacy_sale_gstr1_report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,NULL),(88,28,2.3,'Credit Note','Credit Note',NULL,'credit_Note','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,1),(89,31,5.23,'Credit Note Report','Credit Note Report',NULL,'credit_Note_Report','fa fa-tasks',NULL,NULL,NULL,NULL,1,0,NULL,NULL);

--
-- Table structure for table `tbl_trans_log`
--

CREATE TABLE `tbl_trans_log` (
  `atransId` int(11) NOT NULL AUTO_INCREMENT,
  `iscompanyorcustomer` int(11) DEFAULT NULL,
  `iscompanyorsupplier` int(11) DEFAULT NULL,
  `ndepotId` int(11) DEFAULT NULL,
  `ncusBranchId` int(11) DEFAULT NULL,
  `nsupplierId` int(11) DEFAULT NULL,
  `transModule` varchar(45) DEFAULT NULL,
  `transItems` text,
  `logMsg` text,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `nUserId_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`atransId`),
  UNIQUE KEY `atransId_UNIQUE` (`atransId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_trans_log`
--


--
-- Table structure for table `tbl_user_branches_map`
--

CREATE TABLE `tbl_user_branches_map` (
  `aUserBrachesId` int(11) NOT NULL AUTO_INCREMENT,
  `nUserId` int(11) DEFAULT NULL,
  `iscompanyorcustomerUser` int(11) DEFAULT NULL,
  `nBranchId` int(11) DEFAULT NULL,
  `nDepotId` int(11) DEFAULT NULL,
  `nUserRoleId` varchar(45) DEFAULT NULL,
  `isDeleted` tinyint(4) DEFAULT '0',
  `isActive` tinyint(4) DEFAULT '1',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `nPermission` text,
  `nfeaturepermission` text,
  PRIMARY KEY (`aUserBrachesId`),
  KEY `fk_tbl_user_branches_map_1_idx` (`nUserId`),
  KEY `fk_tbl_user_branches_map_2_idx` (`nBranchId`),
  KEY `fk_tbl_user_branches_map_2_idx1` (`nDepotId`),
  CONSTRAINT `fk_tbl_user_branches_map_1` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_user_branches_map`
--

INSERT INTO `tbl_user_branches_map` VALUES (1,1,1,0,1,NULL,0,1,'2019-03-16 16:06:49','2019-03-16 16:06:49','{\"permissions\":[{\"label\":\"MAIN NAVIGATION\",\"separator\": \"true\"},{\"label\":\"Masters\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserCreation\", \"route\": \"users\"},{\"label\": \"Category Field Master\", \"route\": \"category_field_master\"},{\"label\": \"Item Registration\", \"route\": \"itemregistration\"},{\"label\": \"Item Rate Master\", \"route\": \"itemratemaster\"}]},{\"label\":\"Lists\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserLists\", \"route\": \"users\"}]},{\"label\":\"Lists2\",\"route\":\"dsadf\",\"iconClasses\":\"fa fa-tasks\"},{\"label\":\"Purchase Order\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise PO\", \"route\": \"raisepurchaseorder\"},{\"label\": \"GRN\", \"route\": \"po_grn\"},{\"label\": \"Stock Issue\", \"route\": \"po_generate_invoice\"}]},{\"label\":\"Reports\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Dashboard\", \"route\": \"report_dashboard\"},{\"label\": \"Sales Reports\", \"route\": \"salesreport\"},{\"label\": \"Consumption Report\", \"route\": \"consumption_report\"}]},{\"label\":\"Inventory\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Items In Stock\", \"route\": \"invenorty_itemstock\"}]},{\"label\":\"Goods Return\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Return Items\", \"route\": \"return_items\"},{\"label\": \"Accept Return Items\", \"route\": \"accept_return_items\"}]}]}','{\"permissionsArr\":[{\"subModuleName\":\"Raise PO\",\"permission\":[{\"pemissionName\":\"Authorize\"},{\"pemissionName\":\"Reject\"},{\"pemissionName\":\"ForceClose\"}]},{\"subModuleName\":\"Dashboard\",\"permission\":[{\"pemissionName\":\"BranchDashBoard\"}]},{\"subModuleName\":\"Items In Stock\",\"permission\":[{\"pemissionName\":\"otherBranch_DepoStock\"}]}]}'),(2,2,2,4,0,NULL,0,1,'2019-03-16 16:08:18','2019-03-16 16:08:18','{\"permissions\":[{\"label\":\"MAIN NAVIGATION\",\"separator\": \"true\"},{\"label\":\"Masters\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserCreation\", \"route\": \"users\"},{\"label\": \"Category Field Master\", \"route\": \"category_field_master\"},{\"label\": \"Item Registration\", \"route\": \"itemregistration\"},{\"label\": \"Item Rate Master\", \"route\": \"itemratemaster\"}]},{\"label\":\"Lists\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserLists\", \"route\": \"users\"}]},{\"label\":\"Purchase Order\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise PO\", \"route\": \"raisepurchaseorder\"},{\"label\": \"GRN\", \"route\": \"po_grn\"}]},{\"label\":\"Indent\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise Indent\", \"route\": \"raise_indent\"},{\"label\": \"Stock Accept\", \"route\": \"indent_grn_accept_invoice\"}]},{\"label\":\"Sales\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Pharmacy Sale\", \"route\": \"pharmacysales\"}]},{\"label\":\"Reports\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Dashboard\", \"route\": \"report_dashboard\"},{\"label\": \"Combined Report\", \"route\": \"report_combinedreport\"},{\"label\": \"Sales Reports\", \"route\": \"salesreport\"},{\"label\": \"Consumption Report\", \"route\": \"consumption_report\"}]},{\"label\":\"Inventory\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Items In Stock\", \"route\": \"invenorty_itemstock\"}]},{\"label\":\"Goods Return\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Return Items\", \"route\": \"return_items\"},{\"label\": \"Pharma Sales Returns\", \"route\": \"salesreturns\"}]}]}','{\"permissionsArr\":[{\"subModuleName\":\"Raise PO\",\"permission\":[{\"pemissionName\":\"Authorize\"},{\"pemissionName\":\"Reject\"},{\"pemissionName\":\"ForceClose\"}]},{\"subModuleName\":\"Raise Indent\",\"permission\":[{\"pemissionName\":\"ForceClose\"},{\"pemissionName\":\"Authorize\"}]},{\"subModuleName\":\"Dashboard\",\"permission\":[{\"pemissionName\":\"BranchDashBoard\"}]},{\"subModuleName\":\"Combined Report\",\"permission\":[{\"pemissionName\":\"BranchCombinedReport\"}]},{\"subModuleName\":\"Items In Stock\",\"permission\":[{\"pemissionName\":\"otherBranch_DepoStock\"}]}]}'),(3,2,2,1,0,NULL,0,1,'2019-03-16 16:08:18','2019-03-16 16:08:18','{\"permissions\":[{\"label\":\"MAIN NAVIGATION\",\"separator\": \"true\"},{\"label\":\"Masters\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserCreation\", \"route\": \"users\"},{\"label\": \"Category Field Master\", \"route\": \"category_field_master\"},{\"label\": \"Item Registration\", \"route\": \"itemregistration\"},{\"label\": \"Item Rate Master\", \"route\": \"itemratemaster\"}]},{\"label\":\"Lists\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserLists\", \"route\": \"users\"}]},{\"label\":\"Purchase Order\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise PO\", \"route\": \"raisepurchaseorder\"},{\"label\": \"GRN\", \"route\": \"po_grn\"}]},{\"label\":\"Indent\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise Indent\", \"route\": \"raise_indent\"},{\"label\": \"Stock Accept\", \"route\": \"indent_grn_accept_invoice\"}]},{\"label\":\"Sales\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Pharmacy Sale\", \"route\": \"pharmacysales\"}]},{\"label\":\"Reports\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Dashboard\", \"route\": \"report_dashboard\"},{\"label\": \"Combined Report\", \"route\": \"report_combinedreport\"},{\"label\": \"Sales Reports\", \"route\": \"salesreport\"},{\"label\": \"Consumption Report\", \"route\": \"consumption_report\"}]},{\"label\":\"Inventory\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Items In Stock\", \"route\": \"invenorty_itemstock\"}]},{\"label\":\"Goods Return\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Return Items\", \"route\": \"return_items\"},{\"label\": \"Pharma Sales Returns\", \"route\": \"salesreturns\"}]}]}','{\"permissionsArr\":[{\"subModuleName\":\"Raise PO\",\"permission\":[{\"pemissionName\":\"Authorize\"},{\"pemissionName\":\"Reject\"},{\"pemissionName\":\"ForceClose\"}]},{\"subModuleName\":\"Raise Indent\",\"permission\":[{\"pemissionName\":\"ForceClose\"},{\"pemissionName\":\"Authorize\"}]},{\"subModuleName\":\"Dashboard\",\"permission\":[{\"pemissionName\":\"BranchDashBoard\"}]},{\"subModuleName\":\"Combined Report\",\"permission\":[{\"pemissionName\":\"BranchCombinedReport\"}]},{\"subModuleName\":\"Items In Stock\",\"permission\":[{\"pemissionName\":\"otherBranch_DepoStock\"}]}]}'),(4,2,2,3,0,NULL,0,1,'2019-03-16 16:08:18','2019-03-16 16:08:18','{\"permissions\":[{\"label\":\"MAIN NAVIGATION\",\"separator\": \"true\"},{\"label\":\"Masters\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserCreation\", \"route\": \"users\"},{\"label\": \"Category Field Master\", \"route\": \"category_field_master\"},{\"label\": \"Item Registration\", \"route\": \"itemregistration\"},{\"label\": \"Item Rate Master\", \"route\": \"itemratemaster\"}]},{\"label\":\"Lists\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserLists\", \"route\": \"users\"}]},{\"label\":\"Purchase Order\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise PO\", \"route\": \"raisepurchaseorder\"},{\"label\": \"GRN\", \"route\": \"po_grn\"}]},{\"label\":\"Indent\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise Indent\", \"route\": \"raise_indent\"},{\"label\": \"Stock Accept\", \"route\": \"indent_grn_accept_invoice\"}]},{\"label\":\"Sales\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Pharmacy Sale\", \"route\": \"pharmacysales\"}]},{\"label\":\"Reports\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Dashboard\", \"route\": \"report_dashboard\"},{\"label\": \"Combined Report\", \"route\": \"report_combinedreport\"},{\"label\": \"Sales Reports\", \"route\": \"salesreport\"},{\"label\": \"Consumption Report\", \"route\": \"consumption_report\"}]},{\"label\":\"Inventory\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Items In Stock\", \"route\": \"invenorty_itemstock\"}]},{\"label\":\"Goods Return\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Return Items\", \"route\": \"return_items\"},{\"label\": \"Pharma Sales Returns\", \"route\": \"salesreturns\"}]}]}','{\"permissionsArr\":[{\"subModuleName\":\"Raise PO\",\"permission\":[{\"pemissionName\":\"Authorize\"},{\"pemissionName\":\"Reject\"},{\"pemissionName\":\"ForceClose\"}]},{\"subModuleName\":\"Raise Indent\",\"permission\":[{\"pemissionName\":\"ForceClose\"},{\"pemissionName\":\"Authorize\"}]},{\"subModuleName\":\"Dashboard\",\"permission\":[{\"pemissionName\":\"BranchDashBoard\"}]},{\"subModuleName\":\"Combined Report\",\"permission\":[{\"pemissionName\":\"BranchCombinedReport\"}]},{\"subModuleName\":\"Items In Stock\",\"permission\":[{\"pemissionName\":\"otherBranch_DepoStock\"}]}]}'),(5,2,2,2,0,NULL,0,1,'2019-03-16 16:08:18','2019-03-16 16:08:18','{\"permissions\":[{\"label\":\"MAIN NAVIGATION\",\"separator\": \"true\"},{\"label\":\"Masters\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserCreation\", \"route\": \"users\"},{\"label\": \"Category Field Master\", \"route\": \"category_field_master\"},{\"label\": \"Item Registration\", \"route\": \"itemregistration\"},{\"label\": \"Item Rate Master\", \"route\": \"itemratemaster\"}]},{\"label\":\"Lists\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"UserLists\", \"route\": \"users\"}]},{\"label\":\"Purchase Order\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise PO\", \"route\": \"raisepurchaseorder\"},{\"label\": \"GRN\", \"route\": \"po_grn\"}]},{\"label\":\"Indent\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Raise Indent\", \"route\": \"raise_indent\"},{\"label\": \"Stock Accept\", \"route\": \"indent_grn_accept_invoice\"}]},{\"label\":\"Sales\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Pharmacy Sale\", \"route\": \"pharmacysales\"}]},{\"label\":\"Reports\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Dashboard\", \"route\": \"report_dashboard\"},{\"label\": \"Combined Report\", \"route\": \"report_combinedreport\"},{\"label\": \"Sales Reports\", \"route\": \"salesreport\"},{\"label\": \"Consumption Report\", \"route\": \"consumption_report\"}]},{\"label\":\"Inventory\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Items In Stock\", \"route\": \"invenorty_itemstock\"}]},{\"label\":\"Goods Return\",\"iconClasses\":\"fa fa-tasks\",\"children\":[{\"label\": \"Return Items\", \"route\": \"return_items\"},{\"label\": \"Pharma Sales Returns\", \"route\": \"salesreturns\"}]}]}','{\"permissionsArr\":[{\"subModuleName\":\"Raise PO\",\"permission\":[{\"pemissionName\":\"Authorize\"},{\"pemissionName\":\"Reject\"},{\"pemissionName\":\"ForceClose\"}]},{\"subModuleName\":\"Raise Indent\",\"permission\":[{\"pemissionName\":\"ForceClose\"},{\"pemissionName\":\"Authorize\"}]},{\"subModuleName\":\"Dashboard\",\"permission\":[{\"pemissionName\":\"BranchDashBoard\"}]},{\"subModuleName\":\"Combined Report\",\"permission\":[{\"pemissionName\":\"BranchCombinedReport\"}]},{\"subModuleName\":\"Items In Stock\",\"permission\":[{\"pemissionName\":\"otherBranch_DepoStock\"}]}]}');

--
-- Table structure for table `tbl_user_module_map`
--

CREATE TABLE `tbl_user_module_map` (
  `aModuleMapId` int(11) NOT NULL AUTO_INCREMENT,
  `nUserId` int(11) DEFAULT NULL,
  `nModuleId` int(11) DEFAULT NULL,
  `iscompanyorcustomerUser` int(11) DEFAULT NULL,
  `ndepotId` varchar(45) DEFAULT NULL,
  `nBranchId` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isDeleted` int(11) DEFAULT NULL,
  `nLoggedInUserId` int(11) DEFAULT NULL,
  `nPermission` text,
  `nfeaturepermission` text,
  PRIMARY KEY (`aModuleMapId`),
  KEY `fk_tbl_user_module_map_1_idx` (`nModuleId`),
  KEY `fk_tbl_user_module_map_2_idx` (`nUserId`),
  CONSTRAINT `fk_tbl_user_module_map_1` FOREIGN KEY (`nModuleId`) REFERENCES `tbl_modules` (`aModuleId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tbl_user_module_map_2` FOREIGN KEY (`nUserId`) REFERENCES `tbl_user` (`auserId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbl_user_module_map`
--


--
-- Table structure for table `tbl_vender_outstand_payment_each_transaction`
--

CREATE TABLE `tbl_vender_outstand_payment_each_transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mainTrancId` int(11) DEFAULT '0',
  `vendorId` int(11) DEFAULT '0',
  `branchId` int(11) DEFAULT '0',
  `depoId` int(11) DEFAULT '0',
  `isCustOrConsum` int(11) DEFAULT '0',
  `paidAmount` float DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `userId` int(11) DEFAULT NULL,
  `outStandAmount` float DEFAULT '0',
  `paymentDate` date DEFAULT NULL,
  `paymentModeId` int(11) DEFAULT NULL,
  `paymentRemarks` varchar(250) DEFAULT NULL,
  `isCreditOrDebit` int(11) DEFAULT '0',
  `debitTransactionMainTableId` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_vender_outstand_payment_each_transaction`
--


--
-- Table structure for table `tbl_vender_outstand_payment_main_transaction`
--

CREATE TABLE `tbl_vender_outstand_payment_main_transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendorId` int(11) DEFAULT '0',
  `branchId` int(11) DEFAULT '0',
  `depoId` int(11) DEFAULT '0',
  `isCustOrConsum` int(11) DEFAULT '0',
  `totalCreditAmount` float DEFAULT '0',
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_vender_outstand_payment_main_transaction`
--


--
-- Table structure for table `tbl_vendor_payment_each_details`
--

CREATE TABLE `tbl_vendor_payment_each_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mainTransId` int(11) DEFAULT NULL,
  `grnId` int(11) DEFAULT NULL,
  `invoiceNo` varchar(250) DEFAULT NULL,
  `invoiceDate` date DEFAULT NULL,
  `invoiceAmount` varchar(45) DEFAULT NULL,
  `venderId` varchar(45) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `branchId` int(11) DEFAULT NULL,
  `depoId` int(11) DEFAULT '0',
  `isCustOrConsum` int(11) DEFAULT '0',
  `userId` int(11) DEFAULT NULL,
  `grnNumber` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_vendor_payment_each_details`
--


--
-- Table structure for table `tbl_vendor_payment_main_details`
--

CREATE TABLE `tbl_vendor_payment_main_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `venderId` int(11) DEFAULT '0',
  `totalInvoiceCount` int(11) DEFAULT '0',
  `crediPeriodInvCount` int(11) DEFAULT '0',
  `totalInvAmt` float DEFAULT '0',
  `crediPeriodInvAmt` float DEFAULT '0',
  `paidAmount` float DEFAULT '0',
  `creditAmount` float DEFAULT '0',
  `paymentDate` date DEFAULT NULL,
  `paymentMode` varchar(250) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `branchId` int(11) DEFAULT NULL,
  `depoId` int(11) DEFAULT '0',
  `isCustOrConsum` int(11) DEFAULT '0',
  `userId` int(11) DEFAULT NULL,
  `remarks` varchar(250) DEFAULT NULL,
  `payModeId` int(11) DEFAULT '0',
  `advanceAmtConsumed` float DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_vendor_payment_main_details`
--


--
-- Table structure for table `tblcreditnotedetails`
--

CREATE TABLE `tblcreditnotedetails` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `supplierID` int(11) DEFAULT NULL,
  `creditNoteNumber` varchar(250) DEFAULT NULL,
  `creditNoteDate` date DEFAULT NULL,
  `remarks` varchar(250) DEFAULT NULL,
  `creditNoteAmount` float DEFAULT NULL,
  `creditNoteTaxAmount` float DEFAULT NULL,
  `creditTotalValue` float DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `isConsumerOrIsCustomer` int(11) DEFAULT NULL,
  `customerOrConsumerId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblcreditnotedetails`
--


--
-- Table structure for table `tblcreditnoteitemdetails`
--

CREATE TABLE `tblcreditnoteitemdetails` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `mainCreditId` int(11) DEFAULT NULL,
  `hsnId` int(11) DEFAULT NULL,
  `itemId` int(11) DEFAULT NULL,
  `batchNo` varchar(250) DEFAULT NULL,
  `expDate` date DEFAULT NULL,
  `returnedQty` int(11) DEFAULT NULL,
  `purchasePrice` float DEFAULT NULL,
  `discount` float DEFAULT NULL,
  `gstAmount` float DEFAULT NULL,
  `taxableAmount` float DEFAULT NULL,
  `creditQty` int(11) DEFAULT NULL,
  `totalValue` float DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `itemRateMasterId` int(11) DEFAULT NULL,
  `mainReturnId` int(11) DEFAULT NULL,
  `eachReturnId` int(11) DEFAULT NULL,
  `supplierId` int(11) DEFAULT NULL,
  `categoryId` int(11) DEFAULT NULL,
  `isConsumerOrIsCustomer` int(11) DEFAULT NULL,
  `customerOrConsumerId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblcreditnoteitemdetails`
--


--
-- Table structure for table `tbldepartment`
--

CREATE TABLE `tbldepartment` (
  `deptid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbldepartment`
--


--
-- Table structure for table `tbldepartmenttransferitemdetails`
--

CREATE TABLE `tbldepartmenttransferitemdetails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptid` int(11) NOT NULL,
  `itemname` varchar(250) DEFAULT NULL,
  `batchno` varchar(100) DEFAULT NULL,
  `expirydate` date DEFAULT NULL,
  `issuedqty` int(11) DEFAULT NULL,
  `issuedTo` varchar(250) DEFAULT NULL,
  `is_customer_or_cunsumer` int(11) DEFAULT NULL,
  `customer_or_cunsumer_id` int(11) DEFAULT NULL,
  `itemratemasterID` int(11) NOT NULL,
  `itemID` int(11) NOT NULL,
  `grnID` int(11) DEFAULT NULL,
  `scannedBarcodeNo` varchar(100) DEFAULT NULL,
  `categoryId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` tinyint(4) DEFAULT '1',
  `isDeleted` tinyint(4) DEFAULT '0',
  `reason` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbldepartmenttransferitemdetails`
--


--
-- Table structure for table `tblhospotalwiseconfigurations`
--

CREATE TABLE `tblhospotalwiseconfigurations` (
  `aConfig_fk` int(11) NOT NULL AUTO_INCREMENT,
  `configKey` varchar(250) DEFAULT NULL,
  `configValue` varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  `configDescription` varchar(250) DEFAULT NULL,
  `insertedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `lastModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `isActive` int(11) DEFAULT '1',
  `isDeleted` int(11) DEFAULT '0',
  PRIMARY KEY (`aConfig_fk`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblhospotalwiseconfigurations`
--

INSERT INTO `tblhospotalwiseconfigurations` VALUES (1,'defaultBillHeaderCheck_Pharmacy','1','if is active 1 and config value 1  to show 0 means not to show ','2022-08-11 12:14:15','2022-08-11 12:14:15',1,0),(2,'gstColumsDisplay','1','if is active 1 and config value 1  to show 0 means not to show ','2022-08-11 12:14:15','2022-08-11 12:14:15',1,0),(3,'IsBill_and_Paid_amount_must_equals','1','if is active 1 and config value 1  to show 0 means not to show ','2022-08-11 12:14:15','2022-08-11 12:14:15',1,0),(4,'isManufacturerShowInBillPrint','1','if is active 1 and config value 1  to show 0 means not to show ','2022-08-11 12:14:15','2022-08-11 12:14:15',1,0),(5,'showBillHeaderName','BILL OF SUPPLY','Give Text To Display in Bill Header','2022-08-11 12:14:15','2022-08-11 12:14:15',1,0),(6,'diagnosisDisplay','0','if is active 1 and config value 1  to show 0 means not to show ','2022-08-11 12:24:46','2022-08-11 12:24:46',1,0),(7,'DisplayRxinBillPrint','1','if is active 1 and config value 1  to show 0 means not to show ','2022-08-11 12:24:46','2022-08-11 12:24:46',1,0),(8,'pharmacySalesPrintTerms','',' ','2022-08-11 12:25:41','2022-08-11 12:25:41',1,0),(9,'PlacepfsupplyinGSTReport','sasds','if active 1 anad config value is there show in GSTPercentage Report','2023-05-18 18:16:18','2023-05-18 18:16:18',1,0),(10,'PurchaseGSTPercentageReport','1','if active 1 anad config value is there show in GSTPercentage Report','2023-06-20 11:08:01','2023-06-20 11:08:01',1,0),(11,'PharmacySalesGSTReport','1','if active 1 anad config value is there show in GSTPercentage Report','2023-06-20 11:08:01','2023-06-20 11:08:01',1,0),(12,'DisplayEMRAdviseinprint','1','1 to display advices 0 to not display','2023-08-22 12:56:03','2023-08-22 12:56:03',1,0),(13,'IsReturnreplacement','1','1 to display Replacement CheckBox 0 to not display','2023-09-07 15:34:03','2023-09-07 15:34:03',1,0),(15,'DisplayDaignosisinprint','1','1 to display diagnosis and 0 to not display diagnosis','2023-12-04 17:31:44','2023-12-04 17:31:44',0,0),(16,'DeptTansferinGoodsRetutrn','1','1 to work department tranfer 0 to not work','2023-12-04 17:31:44','2023-12-04 17:31:44',0,0),(17,'ViewItemPriceInInventory','1','if 1 to display 0 not to not display','2024-02-10 17:00:44','2024-02-10 17:00:44',1,0),(18,'RxPrintinTableFormat','1','1 to show 0 to show old print','2023-03-31 13:48:22','2023-03-31 13:48:22',1,0),(19,'Terms and Condition',' This is Software generated prescription and no signature required','if active 1 anad config value is there show in print','2023-03-31 13:48:22','2023-03-31 13:48:22',1,0),(20,'DaystodisablePharmacySalesReturn','30','config value should be days ','2023-06-21 16:31:07','2023-06-21 16:31:07',1,0),(21,'Displaycompositioninprint','0','1 to display composion 0 to not display','2023-08-05 20:17:03','2023-08-05 20:17:03',0,0),(22,'DisplayDaignosisinRxprintfor12','0','1 to display diagnosis 0 to not display','2024-05-30 16:38:03','2024-05-30 16:38:03',0,0),(23,'IsBiilingandVerifybymandatory','0','1 to mandataory 0 to non mandatatory','2024-05-30 16:38:03','2024-05-30 16:38:03',0,0),(24,'BillPrintHeading','Pharmacy Prescription','add Bill print Header ','2024-07-13 09:45:18','2024-07-13 09:45:18',1,0);

--
-- Table structure for table `tblmedicineprescriptionforpharma`
--

CREATE TABLE `tblmedicineprescriptionforpharma` (
  `aId` int(11) NOT NULL AUTO_INCREMENT,
  `npatientid` int(11) DEFAULT NULL,
  `napptid` int(11) DEFAULT NULL,
  `catid` int(11) DEFAULT NULL,
  `nprodid` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `branchid` int(11) DEFAULT NULL,
  `inserteddate` datetime DEFAULT NULL,
  `lastmodifieddate` datetime DEFAULT NULL,
  `isactive` int(11) DEFAULT '1',
  `nuserid` int(11) DEFAULT NULL,
  `nstartDate` varchar(250) DEFAULT NULL,
  `nendDate` varchar(250) DEFAULT NULL,
  `tinstruction` text,
  `tremarks` text,
  `medicineRxType` int(11) DEFAULT NULL,
  `frommodal_id` int(11) DEFAULT '1',
  `eyeType` varchar(45) DEFAULT NULL,
  `medType` varchar(45) DEFAULT NULL,
  `noOfDays` varchar(45) DEFAULT NULL,
  `medicinecomposition` varchar(250) DEFAULT NULL,
  `productName` varchar(250) DEFAULT NULL,
  `eachDaysOrDates` int(11) DEFAULT '1',
  PRIMARY KEY (`aId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tblmedicineprescriptionforpharma`
--


--
-- Table structure for table `tblopticalpatientrefractiondetails`
--

CREATE TABLE `tblopticalpatientrefractiondetails` (
  `aOpt_ID` int(11) NOT NULL AUTO_INCREMENT,
  `nPat_Id` int(11) DEFAULT NULL,
  `nAppoint_Id` int(11) DEFAULT NULL,
  `nbranchId` int(11) DEFAULT NULL,
  `opticalStatus` int(11) DEFAULT NULL,
  `Patient_Type` int(11) DEFAULT NULL,
  `nConsultantId` int(11) DEFAULT NULL,
  `nOpticianId` int(11) DEFAULT NULL,
  `nsph_R_Id` varchar(250) DEFAULT NULL,
  `nsph_L_Id` varchar(250) DEFAULT NULL,
  `ncyl_R_Id` varchar(250) DEFAULT NULL,
  `ncyl_L_Id` varchar(250) DEFAULT NULL,
  `naxis_R_Id` varchar(250) DEFAULT NULL,
  `naxis_L_Id` varchar(250) DEFAULT NULL,
  `nadd_R_Id` varchar(250) DEFAULT NULL,
  `nadd_L_Id` varchar(250) DEFAULT NULL,
  `nNVA_R_Id` varchar(250) DEFAULT NULL,
  `nNVA_L_Id` varchar(250) DEFAULT NULL,
  `nDVA_R_Id` varchar(250) DEFAULT NULL,
  `nDVA_L_Id` varchar(250) DEFAULT NULL,
  `nsph_B_Id` varchar(250) DEFAULT NULL,
  `ncyl_B_Id` varchar(250) DEFAULT NULL,
  `naxis_B_Id` varchar(250) DEFAULT NULL,
  `nadd_B_Id` varchar(250) DEFAULT NULL,
  `nNVA_B_Id` varchar(250) DEFAULT NULL,
  `nDVA_B_Id` varchar(250) DEFAULT NULL,
  `openComments` varchar(250) DEFAULT NULL,
  `inprogressComments` varchar(250) DEFAULT NULL,
  `closedComments` varchar(250) DEFAULT NULL,
  `cancelledComments` varchar(250) DEFAULT NULL,
  `deliveredComments` varchar(250) DEFAULT NULL,
  `lensType` varchar(500) DEFAULT NULL,
  `lensMaterial` varchar(45) DEFAULT NULL,
  `lensInst` varchar(45) DEFAULT NULL,
  `lensIPD` varchar(45) DEFAULT NULL,
  `consultantComments` varchar(250) DEFAULT NULL,
  `nVADV_R_Id` varchar(250) DEFAULT NULL,
  `nVANV_R_Id` varchar(250) DEFAULT NULL,
  `nVADV_L_Id` varchar(250) DEFAULT NULL,
  `nVANV_L_Id` varchar(250) DEFAULT NULL,
  `addsubstitute_R` varchar(250) DEFAULT NULL,
  `addsubstitute_L` varchar(250) DEFAULT NULL,
  `lensPD` varchar(100) DEFAULT NULL,
  `conLens_sph_R` varchar(100) DEFAULT NULL,
  `conLens_sph_L` varchar(100) DEFAULT NULL,
  `conLens_cyl_R` varchar(100) DEFAULT NULL,
  `conLens_cyl_L` varchar(100) DEFAULT NULL,
  `conLens_axis_R` varchar(100) DEFAULT NULL,
  `conLens_axis_L` varchar(100) DEFAULT NULL,
  `conLens_dva_R` varchar(100) DEFAULT NULL,
  `conLens_dva_L` varchar(100) DEFAULT NULL,
  `conLens_basecurve_R` varchar(100) DEFAULT NULL,
  `conLens_basecurve_L` varchar(100) DEFAULT NULL,
  `conLens_diameter_R` varchar(100) DEFAULT NULL,
  `conLens_diameter_L` varchar(100) DEFAULT NULL,
  `conLens_type` varchar(100) DEFAULT NULL,
  `conLens_company` varchar(100) DEFAULT NULL,
  `conLens_usage1` varchar(100) DEFAULT NULL,
  `conLens_usage2` varchar(100) DEFAULT NULL,
  `conLens_txtcomment` mediumtext,
  `consultantName` varchar(250) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  `insertedDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `Category` int(11) DEFAULT NULL,
  `SubCategory` int(11) DEFAULT NULL,
  `Referred_ById` int(11) DEFAULT NULL,
  `Referred_By` varchar(100) DEFAULT NULL,
  `SourceId` int(11) DEFAULT NULL,
  `SourceName` varchar(200) DEFAULT NULL,
  `opticalStatusText` varchar(100) DEFAULT NULL,
  `discount_percentage` float DEFAULT NULL,
  `category_name` varchar(100) DEFAULT NULL,
  `sub_category_name` varchar(100) DEFAULT NULL,
  `constultant_RegNo` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`aOpt_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblopticalpatientrefractiondetails`
--


--
-- Table structure for table `tblopticalsalepatientregistration`
--

CREATE TABLE `tblopticalsalepatientregistration` (
  `aPatientId` int(11) NOT NULL AUTO_INCREMENT,
  `nbranchId` int(11) DEFAULT NULL,
  `PatientName` varchar(250) DEFAULT NULL,
  `PateintGender` varchar(150) DEFAULT NULL,
  `PatientAge` int(11) DEFAULT NULL,
  `emrPatientMRDNum` varchar(150) DEFAULT NULL,
  `Patient_Type` int(11) DEFAULT NULL,
  `tDOB` date DEFAULT NULL,
  `MobileNumber` varchar(45) DEFAULT NULL,
  `Address` text,
  `Category` int(11) DEFAULT NULL,
  `SubCategory` int(11) DEFAULT NULL,
  `Referred_By` varchar(45) DEFAULT NULL,
  `Constultant_Name` varchar(45) DEFAULT NULL,
  `Source` varchar(45) DEFAULT NULL,
  `opticalStatusId` int(11) DEFAULT NULL,
  `opticalStatusText` varchar(45) DEFAULT NULL,
  `LastModifiedDate` datetime DEFAULT NULL,
  `InsertedDate` datetime DEFAULT NULL,
  `isActive` tinyint(1) DEFAULT NULL,
  `isDelete` tinyint(1) DEFAULT NULL,
  `nUserId` int(11) DEFAULT NULL,
  `discount_percentage` float DEFAULT NULL,
  `category_name` varchar(45) DEFAULT NULL,
  `sub_category_name` varchar(45) DEFAULT NULL,
  `patEmailId` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`aPatientId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblopticalsalepatientregistration`
--


--
-- Dumping events for database 'ygpharmacy'
--

--
-- Dumping routines for database 'ygpharmacy'
--
/*!50003 DROP PROCEDURE IF EXISTS `sproc_combined_payment_report_main` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_combined_payment_report_main`(
in fromDate varchar(50),
in toDate varchar(50),
in branchIds tinytext 
)
BEGIN
declare rtnStatus int;
set rtnStatus = 4;
  


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_combined_payment_report_sub` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_combined_payment_report_sub`(
in fromDate varchar(50),
in toDate varchar(50),
in branchIds tinytext
)
BEGIN
DECLARE payModeId_ int;
DECLARE payModeTypeId_ int; 
DECLARE balanceAmt_ float; 

set payModeId_ = 1;
set payModeTypeId_ = 4;  
set balanceAmt_ = 0.0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_EMR_Optical_AmountCollectionontheDayForDashboard` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_EMR_Optical_AmountCollectionontheDayForDashboard`(
in fromDate text,
in toDate text,
in branchId text
)
BEGIN

declare advAmt float;
declare retAmt float; 
declare discountAmt float;
declare cashAmt float;
declare cardAmt float;
declare chequeAmt float;
declare otherAmt float;
        

SELECT 
    IFNULL(advAmt, 0) advAmt,
    IFNULL(retAmt, 0) retAmt,
    IFNULL(discountAmt, 0) discountAmt,
    IFNULL(cashAmt, 0) cashAmt,
    IFNULL(cardAmt, 0) cardAmt,
    IFNULL(chequeAmt, 0) chequeAmt,
    IFNULL(otherAmt, 0) otherAmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_emr_optical_consultant_contibutionreport` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_emr_optical_consultant_contibutionreport`(
in fromDate text,
in toDate text,
in branchId text,
in consltId text
)
BEGIN
declare prescibedCount int;
declare convertedCount int;
declare revenue float;
        
select prescibedCount, convertedCount, revenue;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_emr_optical_eachduration_amt` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_emr_optical_eachduration_amt`(
in fromDate text,
in toDate text,
in branchId text
)
BEGIN
  
declare advAmt float;
declare retAmt float; 
declare discountAmt float;
declare cashAmt float;
declare cardAmt float;
declare chequeAmt float;
declare otherAmt float;
declare totalAountPaid float;

declare advColle float;
declare advAdju float;

declare creditAmt float;
        
SET group_concat_max_len := 1000000;
        
SELECT 
    IFNULL(totalAountPaid, 0) TotalAmt_paid,
    IFNULL(advAmt, 0) advAmt,
    IFNULL(retAmt, 0) retAmt,
    IFNULL(discountAmt, 0) discountAmt,
    IFNULL(cashAmt, 0) cashAmt,
    IFNULL(cardAmt, 0) cardAmt,
    IFNULL(chequeAmt, 0) chequeAmt,
    IFNULL(otherAmt, 0) otherAmt,
    IFNULL(advColle, 0) advColle,
    IFNULL(advAdju, 0) advAdju,
    IFNULL(creditAmt, 0) creditAmt; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_emr_optical_source_wise_report` */;
DELIMITER ;;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_EMR_Pharma_AmountCollectionontheDayForDashboard` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_EMR_Pharma_AmountCollectionontheDayForDashboard`(
in fromDate text,
in toDate text,
in branchId text
)
BEGIN

declare advAmt float;
declare retAmt float;
declare discountAmt float;
declare cashAmt float;
declare cardAmt float;
declare chequeAmt float;
declare otherAmt float;

SELECT 
    0 advAmt,
    IFNULL(retAmt, 0) retAmt,
    IFNULL(discountAmt, 0) discountAmt,
    IFNULL(cashAmt, 0) cashAmt,
    IFNULL(cardAmt, 0) cardAmt,
    0 chequeAmt,
    IFNULL(otherAmt, 0) otherAmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_emr_pharma_consultant_contibutionreport` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_emr_pharma_consultant_contibutionreport`(
in fromDate text,
in toDate text,
in branchId text,
in consltId text
)
BEGIN
declare prescibedCount int;
declare convertedCount int;
declare revenue float;
        
select prescibedCount, convertedCount, revenue;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_emr_pharma_eachduration_amt` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_emr_pharma_eachduration_amt`(
in fromDate text,
in toDate text,
in branchId text
)
BEGIN

declare paidAmt float;
/*declare retAmt float; */


declare advAmt float;
declare retAmt float;
declare discountAmt float;
declare cashAmt float;
declare cardAmt float;
declare chequeAmt float;
declare otherAmt float;

/* set retAmt = (SELECT 
    SUM(returnAmount) retAmt 
FROM
    tbl_pharma_sales_main_returns t1
        JOIN
    tbl_pharma_patients_main_sales t2 ON t2.aSalesId = t1.nMainSaleId
        AND (DATE(t1.returnDate) BETWEEN DATE(fromDate) AND DATE(toDate))
        -- AND t2.nBranchId IN (branchId));
        AND if(branchId <> '-1', t2.nBranchId IN (branchId), 1=1)); */
        
SELECT 
    ROUND(IFNULL(paidAmt, 0) - IFNULL(retAmt, 0),2) AS TotalAmt_paid,
    0.00 advAmt,
    ROUND(IFNULL(retAmt, 0),2) retAmt,
    ROUND(IFNULL(discountAmt, 0),2) discountAmt,
    ROUND(IFNULL(cashAmt, 0) ,2) cashAmt, -- - IFNULL(retAmt, 0)
    ROUND(IFNULL(cardAmt, 0),2) cardAmt,
    0.00 chequeAmt,
    ROUND(IFNULL(otherAmt, 0),2) otherAmt;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_EMR_Pharma_gstMasters` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_EMR_Pharma_gstMasters`()
BEGIN
SELECT 
    IFNULL(gstPercentage,0) AS gstPercentage,
    IFNULL(sgstPercentage,0) AS sgstPercentage,
    IFNULL(cgstPercentage,0) AS cgstPercentage,
    IFNULL(igstPercentage,0) AS igstPercentage    
 FROM tbl_mst_hsn_codes
 WHERE isActive = 1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_EMR_Pharma_gstWiseAmount` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_EMR_Pharma_gstWiseAmount`(
in fromDate date,
in toDate date,
in branchId int
)
BEGIN

SELECT 
    returnAmt,
    Amt,
    (Amt - returnAmt) AS taxAmount,
    gstPercentage,
    sgstPercentage,
    cgstPercentage,
    igstPercentage
FROM
    (SELECT 
        0 returnAmt, 
            IFNULL(TPPES.amtWithTax, 0) Amt,
            (IFNULL(TPPES.amtWithTax, 0)) AS taxAmount,
            TMHC.gstPercentage AS gstPercentage,
            TMHC.sgstPercentage AS sgstPercentage,
            TMHC.cgstPercentage AS cgstPercentage,
            TMHC.igstPercentage AS igstPercentage
    FROM
        tbl_pharma_patients_each_sales TPPES
    JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.nItemId
        AND IF(branchId <> - 1, TPPES.nBranchId = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
    WHERE
        DATE(TPPES.insertedDate) BETWEEN DATE(fromDate) AND DATE(toDate)
 
 UNION ALL 
 
 SELECT  
        IFNULL(TBLERT.returnedAmt, 0) returnAmt,
            0 Amt,
            0 AS taxAmount,
             TMHC.gstPercentage AS gstPercentage,
            TMHC.sgstPercentage AS sgstPercentage,
            TMHC.cgstPercentage AS cgstPercentage,
            TMHC.igstPercentage AS igstPercentage
    FROM
        tbl_pharma_patients_each_sales TPPES
         JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.nItemId
        AND IF(branchId <> - 1, TPPES.nBranchId = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
    JOIN tbl_pharma_sales_main_returns TBLMRT ON TBLMRT.nMainSaleId = TPPES.nMainSalesId
    JOIN tbl_pharma_sales_each_returns TBLERT ON TBLERT.nMainReturnId = TBLMRT.aSaleReturnId
        AND TPPES.nItemId = TBLERT.nItemId
        AND TPPES.nItemExpiryAvailId = TBLERT.nItemExpireAvailId
        AND DATE(TBLMRT.returnDate) BETWEEN DATE(fromDate) AND DATE(toDate)) TBLU
ORDER BY TBLU.gstPercentage;

/*SELECT 
        -- TPPES.nItemId AS itemId,
        -- TMID.itemName AS itemName,
        (IFNULL(TPPES.amtWithTax, 0) - IFNULL(TBLERT.returnedAmt, 0)) as taxAmount,
        TMHC.gstPercentage AS gstPercentage,
        TMHC.sgstPercentage AS sgstPercentage,
        TMHC.cgstPercentage AS cgstPercentage,
        TMHC.igstPercentage AS igstPercentage
             
    FROM tbl_pharma_patients_each_sales TPPES
    LEFT JOIN tbl_pharma_sales_main_returns TBLMRT ON TBLMRT.nMainSaleId = TPPES.nMainSalesId
    LEFT JOIN tbl_pharma_sales_each_returns TBLERT ON TBLERT.nMainReturnId = TBLMRT.aSaleReturnId  
        AND TPPES.nItemId =  TBLERT.nItemId 
        AND TPPES.nItemExpiryAvailId = TBLERT.nItemExpireAvailId
    JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.nItemId
        AND IF(branchId <> -1, TPPES.nBranchId = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
    WHERE DATE(TPPES.insertedDate) BETWEEN DATE(fromDate) AND DATE(toDate)
    
   -- GROUP BY TMHC.gstPercentage
    ORDER BY TMHC.gstPercentage;*/
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMedicineQtyFor_NewExam` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getMedicineQtyFor_NewExam`(
in medicienProducId int,
in medicineCatId varchar(50),
in medicineBranchId int 
)
BEGIN


select  sum(ifnull(T1.available_qty,0)) as availableQty
    from
        tbl_item_grn_module_expire_availability T1 
     join
        tbl_mst_items_details T2 
            on T1.itemID=T2.aItemId_pk 
            and (
                (  
                    T1.itemID = medicienProducId 
                 --   and T2.nCategoryId_fk=medicineCatId 
                    and T1.is_Branch_Id=medicineBranchId 
                    and T1.available_qty > 0  AND DATE(T1.expiryDate) >= DATE(NOW())
                ) 
            )
           
           group by T1.itemID,T1.is_Branch_Id
            ;
            
            
        
        

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_main_data_order_dashboard` */;
DELIMITER ;;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_po_items_from_branch_inv_gen_from_depo` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_po_items_from_branch_inv_gen_from_depo`(
in branchPoId int,
in isExpiryBatchItem boolean,
in depoId int
)
BEGIN

declare itemBatchCats varchar(150);
declare isPoORBrchTran int;
declare val int;
-- set val = 0;
set itemBatchCats = (select group_concat(aCategoryId_pk) from tbl_mst_category where isBatchNum_Expiey_Category = 1);
-- select itemBatchCats;

set isPoORBrchTran = (select typeOfRequest from tbl_po_main_details where apoid = branchPoId);

SELECT 
    t1.apoeachid,
    t1.npoid_fk,
    t1.item_id_fk,
    t1.item_code,
    t1.item_name,
    t1.item_order_qty,
    t2.nPoEachId,
    IFNULL(t2.receivedQty, 0) receivedQty,  
    CASE 
    -- when INSTR(itemBatchCats, t3.Category_Id) < 0
    when SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null
    then 
     IFNULL(t3.available_qty, 0)
    else 
     IFNULL(t4.available_qty, 0)
    end as available_qty,
    -- IFNULL(t3.available_qty, 0) available_qty,
    CASE 
    -- when isExpiryBatchItem is false
    -- when INSTR(itemBatchCats, t3.Category_Id) < 0
    when SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null
    then 
     IFNULL(t3.itemID, 0)
    else 
     IFNULL(t4.itemID, 0)
    end as itemID,
    -- t3.itemID,
    CASE 
    -- when isExpiryBatchItem is false
    -- when INSTR(itemBatchCats, t3.Category_Id) < 0
    when SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null
    then 
     IFNULL(t3.grnID, 0)
    else 
     IFNULL(t4.grnID, 0)
    end as grnID,
    -- t3.grnID,
    CASE 
    -- when isExpiryBatchItem is false
    -- when INSTR(itemBatchCats, t3.Category_Id) < 0
    when SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null
    then 
     IFNULL(t3.grn_number, '')
    else 
     IFNULL(t4.grn_number, '')
    end as grn_number,
    -- t3.grn_number,
    CASE 
    -- when isExpiryBatchItem is false
    -- when INSTR(itemBatchCats, t3.Category_Id) < 0
    when SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null
    then 
     IFNULL(t3.itemAvailId, 0) 
    else 
     IFNULL(t4.itemAvail_expire_Id, 0)
    end as itemAvailId,
    -- t3.itemAvailId
    CASE 
    -- when isExpiryBatchItem is false
    -- when INSTR(itemBatchCats, t3.Category_Id) < 0
    when SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null
    then 
     ''
    else 
     IFNULL(t4.batchNo, '')
    end as batchNo,
    CASE 
    -- when isExpiryBatchItem is false
    -- when INSTR(itemBatchCats, t3.Category_Id) < 0
    when SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null
    then 
     date('1900-01-01')
    else 
     IFNULL(date(t4.expiryDate), date('1900-01-01'))
    end as expiryDate,
    t1.nSupplierItemMapid,
    t1.categoryid_fk,
    CAST(CONV(ifnull(t1.po_item_brchtrans_status_id, 0), 16, 19) AS UNSIGNED INTEGER) as  po_item_brchtrans_status_id 
FROM
    tbl_po_each_item_details t1  
        LEFT JOIN
    (SELECT 
        nPoEachIdBranch_fk nPoEachId, SUM(ifnull(issuedqty_fromdepo,0)) receivedQty
    FROM
        tbl_grn_for_po_each_item_orders
    WHERE
        nPoIdBranch_fk = branchPoId
    GROUP BY nPoEachIdBranch_fk) t2 ON t1.apoeachid = t2.nPoEachId
        LEFT JOIN
    (SELECT 
        itemAvailId, itemID, grnID, grn_number, available_qty, Category_Id
    FROM
        tbl_item_grn_availability
    WHERE
        if(isPoORBrchTran!=4, is_Depo_Id = depoId, is_Branch_Id=depoId) and available_qty > 0) t3 on t3.itemID = t1.item_id_fk
		LEFT JOIN
    (SELECT 
        itemAvail_expire_Id, itemID, grnID, grn_number, available_qty, batchNo, expiryDate, Category_Id
    FROM
        tbl_item_grn_module_expire_availability
    WHERE
        if(isPoORBrchTran!=4, is_Depo_Id = depoId, is_Branch_Id=depoId) and available_qty > 0) t4 on t4.itemID = t1.item_id_fk
WHERE
t1.npoid_fk = branchPoId and t1.poItemStatusId in (2,4,7) and 
if(SUBSTRING_INDEX(SUBSTRING_INDEX(itemBatchCats, ',', t3.Category_Id), ',', -1) is not null,
    IFNULL(t3.available_qty, 0) > 0, 
     IFNULL(t4.available_qty, 0) > 0)
     and t1.po_item_brchtrans_status_id < 2
    order by t1.item_id_fk
    ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_sub_data_order_dashboard` */;
DELIMITER ;;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_optical_flash_dashboard_daily_coll_report` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_optical_flash_dashboard_daily_coll_report`(
in currDateFrom text,
in currDateTo text,
in financialYearFrom text,
in financialYearTo text,
in branchId text
)
BEGIN

declare newOrderCount int;
declare deliveredCount int;
declare advAmt float;
declare settlementAmt float;
declare cashAmt float;
declare cardAmt float;
declare chequeAmt float;
declare creditAmt float;
declare netRevenue_FinancialYear float;
        
SELECT 
    newOrderCount,
    deliveredCount,
    advAmt,
    settlementAmt,
    cashAmt,
    cardAmt,
    chequeAmt,
    creditAmt,
    netRevenue_FinancialYear;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_optical_flash_dashboard_month_report` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_optical_flash_dashboard_month_report`(
in fromDate text,
in toDate text,
in branchId text
)
BEGIN

declare orders int;
declare orderAmount float;
declare cgstAmount float;
declare sgstAmount float;
declare openOrders int;
declare openOrdersAmount float;
declare cashAmt float;
declare cardAmt float;
declare chequeAmt float;
declare creditAmt float;
        
SELECT orders, orderAmount, openOrders, cgstAmount, sgstAmount, openOrdersAmount, cashAmt, cardAmt, chequeAmt, creditAmt; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_optical_flash_dashboard_pending_order_report` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_optical_flash_dashboard_pending_order_report`(
in fromDate_past text,
in toDate_past text,
in branchId text)
BEGIN

declare pendingOrderCount int;
declare pendingOrderValue float;
declare pendingOrderCount_notMin int;
declare pendingOrderValue_notMin float;
declare pendingOrderCount_collectedMin int;
declare pendingOrderValue_collectedMin float;

SELECT 
    pendingOrderCount,
    pendingOrderValue,
    pendingOrderCount_collectedMin,
    IFNULL(pendingOrderValue_collectedMin, 0) pendingOrderValue_collectedMin,
    pendingOrderCount_notMin,
    IFNULL(pendingOrderValue_notMin, 0) pendingOrderValue_notMin;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Pharma_gstReturnWiseAmount` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_Pharma_gstReturnWiseAmount`(
in fromDate date,
in toDate date,
in branchId int
)
BEGIN


SELECT  
        IFNULL(TBLERT.returnedAmt, 0) returnAmt,
       IFNULL(TPPES.amtWithTax, 0) Amt,
            (IFNULL(TPPES.amtWithTax, 0)) AS taxAmount,
             TMHC.gstPercentage AS gstPercentage,
            TMHC.sgstPercentage AS sgstPercentage,
            TMHC.cgstPercentage AS cgstPercentage ,
          --  TMHC.igstPercentage AS igstPercentage ,
			TPPES.amtWithoutTax AS taxableAmount  
         
    FROM
        tbl_pharma_patients_each_sales TPPES 
         JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.nItemId
        AND IF(branchId <> - 1, TPPES.nBranchId = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
    JOIN tbl_pharma_sales_main_returns TBLMRT ON TBLMRT.nMainSaleId = TPPES.nMainSalesId
    JOIN tbl_pharma_sales_each_returns TBLERT ON TBLERT.nMainReturnId = TBLMRT.aSaleReturnId
       AND TPPES.nItemId = TBLERT.nItemId 
        AND TPPES.nItemExpiryAvailId = TBLERT.nItemExpireAvailId
        AND DATE(TBLMRT.returnDate) BETWEEN DATE(fromDate) AND DATE(toDate) 
     --   ORDER BY TBLU.gstPercentage
        ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Pharma_gstWiseAmount` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_Pharma_gstWiseAmount`(
in fromDate date,
in toDate date,
in branchId int
)
BEGIN

SELECT 
    returnAmt,
    Amt,
    Amt  AS taxAmount,
    gstPercentage,
    sgstPercentage,
    cgstPercentage,
    taxableAmt  AS taxableAmount
  -- , igstPercentageas igstPercentage
FROM
    (SELECT 
        0 returnAmt,  
            IFNULL(TPPES.amtWithTax, 0) Amt,
            (IFNULL(TPPES.amtWithTax, 0)) AS taxAmount,
            TMHC.gstPercentage AS gstPercentage,
            TMHC.sgstPercentage AS sgstPercentage,
            TMHC.cgstPercentage AS cgstPercentage,
            TMHC.igstPercentage AS igstPercentage,
            TPPES.amtWithoutTax AS taxableAmt
          ,  0 as returnTaxableAmt
    FROM 
        tbl_pharma_patients_each_sales TPPES 
    JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.nItemId
        AND IF(branchId <> - 1, TPPES.nBranchId = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
   -- LEFT JOIN tbl_pharma_sales_main_returns TBLMRT ON TBLMRT.nMainSaleId = TPPES.nMainSalesId

    WHERE
  --  isnull(TBLMRT.nMainSaleId) and 
        DATE(TPPES.insertedDate) BETWEEN DATE(fromDate) AND DATE(toDate)) AS TBLD ;
 
  
 /*
 UNION ALL 
 
 SELECT  
        IFNULL(TBLERT.returnedAmt, 0) returnAmt,
            0 Amt,
            0 AS taxAmount,
             TMHC.gstPercentage AS gstPercentage,
            TMHC.sgstPercentage AS sgstPercentage,
            TMHC.cgstPercentage AS cgstPercentage
          ,  TMHC.igstPercentage AS igstPercentage, 
			0 AS taxableAmt
         ,   TPPES.amtWithoutTax as retuenTaxableAmount

    FROM
        tbl_pharma_patients_each_sales TPPES 
         JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.nItemId
        AND IF(branchId <> - 1, TPPES.nBranchId = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
    JOIN tbl_pharma_sales_main_returns TBLMRT ON TBLMRT.nMainSaleId = TPPES.nMainSalesId
    JOIN tbl_pharma_sales_each_returns TBLERT ON TBLERT.nMainReturnId = TBLMRT.aSaleReturnId
        AND TPPES.nItemId = TBLERT.nItemId
        AND TPPES.nItemExpiryAvailId = TBLERT.nItemExpireAvailId
        AND DATE(TBLMRT.returnDate) BETWEEN DATE(fromDate) AND DATE(toDate)) TBLU
ORDER BY TBLU.gstPercentage; */

/*SELECT 
        -- TPPES.nItemId AS itemId,
        -- TMID.itemName AS itemName,
        (IFNULL(TPPES.amtWithTax, 0) - IFNULL(TBLERT.returnedAmt, 0)) as taxAmount,
        TMHC.gstPercentage AS gstPercentage,
        TMHC.sgstPercentage AS sgstPercentage,
        TMHC.cgstPercentage AS cgstPercentage,
        TMHC.igstPercentage AS igstPercentage
             
    FROM tbl_pharma_patients_each_sales TPPES
    LEFT JOIN tbl_pharma_sales_main_returns TBLMRT ON TBLMRT.nMainSaleId = TPPES.nMainSalesId
    LEFT JOIN tbl_pharma_sales_each_returns TBLERT ON TBLERT.nMainReturnId = TBLMRT.aSaleReturnId  
        AND TPPES.nItemId =  TBLERT.nItemId 
        AND TPPES.nItemExpiryAvailId = TBLERT.nItemExpireAvailId
    JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.nItemId
        AND IF(branchId <> -1, TPPES.nBranchId = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
    WHERE DATE(TPPES.insertedDate) BETWEEN DATE(fromDate) AND DATE(toDate)
    
   -- GROUP BY TMHC.gstPercentage
    ORDER BY TMHC.gstPercentage;*/
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Pharma_purchase_gstWiseAmount` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_Pharma_purchase_gstWiseAmount`(
in fromDate date,
in toDate date,
in branchId int
)
BEGIN

SELECT 
            IFNULL(TPPES.item_total_amt_with_gst, 0) Amt,
            (IFNULL(TPPES.item_total_amt_with_gst, 0)) AS taxAmount,
            TMHC.gstPercentage AS gstPercentage,
            TMHC.sgstPercentage AS sgstPercentage,
            TMHC.cgstPercentage AS cgstPercentage,
            TMHC.igstPercentage AS igstPercentage
    FROM
        tbl_po_each_item_details TPPES
    JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TPPES.item_id_fk
        AND IF(branchId <> - 1, TPPES.n_poeachid_from_branch_fk = branchId, TRUE)
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnId_pk = TMID.nHsnCodeId_fk
    WHERE 
        DATE(TPPES.insertedDate) BETWEEN DATE(fromDate) AND DATE(toDate);


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_POs_For_GRN` */;
DELIMITER ;;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatePatientDetailsFromEMR` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updatePatientDetailsFromEMR`(
in in_patientID varchar(20),
in in_patientName varchar(250),
in in_patientAge int,
in in_phoneNo varchar(20),
in in_address text,
in in_gender varchar(20)
)
BEGIN

declare updateResults int;
set updateResults = 0;
if exists(select * from tbl_pharma_patientsregistration where nEmrPatientId = in_patientID)
then

update tbl_pharma_patientsregistration
set 
patientName = in_patientName,
patientAge = in_patientAge,
gender = in_gender,
phoneNumber = in_phoneNo,
address = in_address

where nEmrPatientId = in_patientID AND aPatientPharmaId <> 0; 

set updateResults = 1;

end if; 

select updateResults ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test_byname_gentest` */;
DELIMITER ;;
END ;;
DELIMITER ;


