############################## Ion Auth Table Start ############################### DROP TABLE IF EXISTS `groups`; # # Table structure for table 'groups' # CREATE TABLE `groups` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `description` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table 'groups' # INSERT INTO `groups` (`id`, `name`, `description`) VALUES (1,'admin','Administrator'), (2,'members','General User'); DROP TABLE IF EXISTS `users`; # # Table structure for table 'users' # CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `ip_address` varchar(45) NOT NULL, `username` varchar(100) NULL, `password` varchar(255) NOT NULL, `email` varchar(254) NOT NULL, `activation_selector` varchar(255) DEFAULT NULL, `activation_code` varchar(255) DEFAULT NULL, `forgotten_password_selector` varchar(255) DEFAULT NULL, `forgotten_password_code` varchar(255) DEFAULT NULL, `forgotten_password_time` int(11) unsigned DEFAULT NULL, `remember_selector` varchar(255) DEFAULT NULL, `remember_code` varchar(255) DEFAULT NULL, `created_on` int(11) unsigned NOT NULL, `last_login` int(11) unsigned DEFAULT NULL, `active` tinyint(1) unsigned DEFAULT NULL, `first_name` varchar(50) DEFAULT NULL, `last_name` varchar(50) DEFAULT NULL, `company` varchar(100) DEFAULT NULL, `phone` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `uc_email` UNIQUE (`email`), CONSTRAINT `uc_activation_selector` UNIQUE (`activation_selector`), CONSTRAINT `uc_forgotten_password_selector` UNIQUE (`forgotten_password_selector`), CONSTRAINT `uc_remember_selector` UNIQUE (`remember_selector`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Dumping data for table 'users' # INSERT INTO `users` (`id`, `ip_address`, `username`, `password`, `email`, `activation_code`, `forgotten_password_code`, `created_on`, `last_login`, `active`, `first_name`, `last_name`, `company`, `phone`) VALUES ('1','127.0.0.1','administrator','$2y$08$200Z6ZZbp3RAEXoaWcMA6uJOFicwNZaqk4oDhqTUiFXFe63MG.Daa','admin@admin.com','',NULL,'1268889823','1268889823','1', 'Admin','istrator','ADMIN','0'); DROP TABLE IF EXISTS `users_groups`; # # Table structure for table 'users_groups' # CREATE TABLE `users_groups` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `group_id` mediumint(8) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `fk_users_groups_users1_idx` (`user_id`), KEY `fk_users_groups_groups1_idx` (`group_id`), CONSTRAINT `uc_users_groups` UNIQUE (`user_id`, `group_id`), CONSTRAINT `fk_users_groups_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_users_groups_groups1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `users_groups` (`id`, `user_id`, `group_id`) VALUES (1,1,1), (2,1,2); DROP TABLE IF EXISTS `login_attempts`; # # Table structure for table 'login_attempts' # CREATE TABLE `login_attempts` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `ip_address` varchar(45) NOT NULL, `login` varchar(100) NOT NULL, `time` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ############################## Ion Auth Table End ############################### ############################# Store Table Start #################################### CREATE TABLE `store` ( `PKStoreID` int(10) NOT NULL AUTO_INCREMENT, `FKCmpID` int(10) NOT NULL, `FKParentID` int(10) NOT NULL, `FKEmpID` int(10) NOT NULL, `StoreCode` char(10) NOT NULL, `StoreName` varchar(50) NOT NULL, `StoreLevel` int(15) NOT NULL, `Consumption` char(10) NOT NULL, `ModuleName` char(10) NOT NULL, `Location` varchar(200) NOT NULL, `StorePhone` varchar(50) NOT NULL, `StoreFax` varchar(50) NOT NULL, `StoreEmail` varchar(50) NOT NULL, `SiteMap` varchar(50) NOT NULL, `Description` varchar(100) NOT NULL, `TypeFlag` char(2) NOT NULL, `DelFlag` char(1) NOT NULL, `RepFlag` char(1) NOT NULL, `CreatedBy` int(10) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(10) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################################Store Table End #################################### ########################################### Item Table Start########################################### CREATE TABLE `metacorp`.`item` ( `PKItemID` INT(11) NOT NULL AUTO_INCREMENT , `FKCmpID` INT(11) NOT NULL , `FKGroupID` INT(11) NOT NULL , `FKStoreID` INT(11) NOT NULL , `FKUnitID` INT(11) NOT NULL , `FKSupplierID` INT(11) NOT NULL , `ItemCode` CHAR(100) NOT NULL , `HSNCode` VARCHAR(50) NOT NULL , `ItemName` VARCHAR(150) NOT NULL , `Class` CHAR(15) NOT NULL , `Qty` INT(18) NOT NULL , `Price` INT(18) NOT NULL , `Lcost` INT(18) NOT NULL , `ReOrderLevel` INT(18) NOT NULL , `MaxLevel` INT(18) NOT NULL , `TaxPer` INT(18) NOT NULL , `DelFlag` INT(1) NOT NULL , `RepFlag` INT(1) NOT NULL , `CreatedBy` INT(10) NOT NULL , `CreatedOn` DATETIME NOT NULL , `ModifiedBy` INT(10) NOT NULL , `ModifiedOn` DATETIME NOT NULL , PRIMARY KEY (`PKItemID`)) ENGINE = InnoDB; ALTER TABLE `item` CHANGE `FKSupplierID` `FKManufactureID` INT(11) NOT NULL; ###################### Item Table End ################################################################## ######################## Item Price Table Start ############################################################### CREATE TABLE `metacorp`.`item_price` ( `PKItemPriceID` INT(11) NOT NULL , `FKItemID` INT(11) NOT NULL , `Price` INT(15) NOT NULL , `CreatedOn` TIMESTAMP NOT NULL ) ENGINE = InnoDB; ###################### Item Price Table End ############################################################ ###################### Employee Table start ############################################################### CREATE TABLE `metacorp`.`employee` ( `PKEmployeeID` INT(11) NOT NULL AUTO_INCREMENT , `EmployeeName` VARCHAR(50) NOT NULL , `Designation` VARCHAR(50) NOT NULL , `Address` VARCHAR(100) NOT NULL , `Phone` INT(11) NOT NULL , `Email` VARCHAR(20) NOT NULL , `DOB` DATE NOT NULL , `CreatedOn` DATE NOT NULL , PRIMARY KEY (`PKEmployeeID`)) ENGINE = InnoDB ################################## Employee TABLE End ########################## ################################## Manufacture Table Start ##################### CREATE TABLE `manufacturer` ( `PKManufactureID` int(11) NOT NULL, `ManufactureName` varchar(50) NOT NULL, `licenceNo` varchar(20) NOT NULL, `Category` varchar(50) NOT NULL, `Address` varchar(100) NOT NULL, `Country` varchar(50) NOT NULL, `Email` varchar(20) NOT NULL, `Phone` varchar(15) NOT NULL, `ContactPerson` varchar(30) NOT NULL, `DelFlag` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################## Manufacture Table End ##################################### ################################## Vat table Stand ######################################## CREATE TABLE `metacorp`.`vat` ( `PKVatID` INT(11) NOT NULL , `vat_name` VARCHAR(20) NOT NULL , `FKCountryID` INT(10) NOT NULL , `vat_rate` VARCHAR(20) NOT NULL , `valid_from` DATETIME NOT NULL , `valid_to` DATETIME NOT NULL , `CreatedBy` INT(10) NOT NULL , `CreatedOn` DATETIME NOT NULL , `ModifiedBy` INT(10) NOT NULL , `ModifiedOn` DATETIME NOT NULL ) ENGINE = InnoDB; ###################################### Vat Table End ###################################### ################################## Bank Details Table Start######################################## CREATE TABLE `bank_details` ( `PKID` int(11) NOT NULL, `BankName` varchar(50) NOT NULL, `BankBranch` varchar(50) NOT NULL, `BankCode` varchar(30) NOT NULL, `AccountName` varchar(50) NOT NULL, `AccountNumber` int(30) NOT NULL, `Is_Primary_Accnt` tinyint(4) NOT NULL, `VendorType` tinyint(4) NOT NULL, `FKID` int(11) NOT NULL, `DelFlag` int(11) NOT NULL, `RepFlag` int(11) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, PRIMARY KEY (`PKID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ################################## Bank Details Table End ######################################## # Group Table is named as category_groups in database! ################################## Group Table Start ######################################## CREATE TABLE `category_groups` ( `PKGroupID` int(11) NOT NULL, `FKCmpID` int(11) DEFAULT NULL, `GroupCode` char(10) DEFAULT NULL, `GroupName` varchar(50) DEFAULT NULL, `DelFlag` char(1) DEFAULT NULL, `RepFlag` char(1) DEFAULT NULL, `CreatedBy` int(11) DEFAULT NULL, `CreatedOn` datetime DEFAULT NULL, `ModifiedBy` int(11) DEFAULT NULL, `ModifiedOn` datetime DEFAULT NULL, PRIMARY KEY (`PKGroupID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ################################## Group Table End ######################################## ################################## Contact Details Table Start ######################################## CREATE TABLE `contact_details` ( `PKID` int(11) NOT NULL, `Phone` varchar(50) NOT NULL, `Fax` varchar(50) NOT NULL, `Email` varchar(30) NOT NULL, `Is_Primary_Contact` tinyint(4) NOT NULL, `VendorType` tinyint(4) NOT NULL, `FKID` int(11) NOT NULL, `DelFlag` int(11) NOT NULL, `RepFlag` int(11) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, PRIMARY KEY (`PKID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ################################## Contact Details Table End ######################################## ################################## Retail Table Start ######################################## CREATE TABLE `retailer` ( `PKCmpID` int(11) NOT NULL, `FKCountryID` int(10) NOT NULL, `RetailCode` char(10) NOT NULL, `RetailName` varchar(100) NOT NULL, `RetailVat` varchar(100) NOT NULL, `RetailBusiness` varchar(100) NOT NULL, `RetailAddress1` varchar(100) NOT NULL, `RetailAddress2` varchar(100) NOT NULL, `RetailAddress3` varchar(100) NOT NULL, `RetailCountry` varchar(20) NOT NULL, `RetailURL` varchar(50) NOT NULL, `RetailPhone` varchar(50) NOT NULL, `RetailPinCode` varchar(50) NOT NULL, `RetailFax` varchar(50) NOT NULL, `RetailEmail` varchar(50) NOT NULL, `RetailCorpComp` int(10) NOT NULL, `RetailTin` varchar(50) NOT NULL, `RetailNo1` varchar(50) NOT NULL, `RetailOwner` varchar(50) NOT NULL, `Logo` varchar(50) NOT NULL, `RetailDesig` varchar(50) NOT NULL, `RetailStatus` varchar(50) NOT NULL, `RetailFlag` int(15) NOT NULL, `RetailDefault` int(15) NOT NULL, `DisplayTheme` varchar(10) NOT NULL, `DelFlag` varchar(1) NOT NULL, `RepFlag` varchar(1) NOT NULL, `CreatedBy` int(10) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBY` int(10) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ################################## Company Table End ######################################## CREATE TABLE `metacorp`.`orders` (`id` INT NOT NULL AUTO_INCREMENT , `order_number` VARCHAR(100) NULL , `store_id` INT NULL , `customer_id` INT NULL , `total_amount` FLOAT NULL , `shipping_cost` FLOAT NULL , `vat` FLOAT NULL , `shipping_address` INT NULL , `billing_address` INT NULL , `payment_status` INT NOT NULL DEFAULT '0' , `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`)) ENGINE = InnoDB; ################################# Manufacture Category Table Start ################################ CREATE TABLE IF NOT EXISTS `manufacture_category` ( `PKCategoryID` int NOT NULL AUTO_INCREMENT, `CategoryName` varchar(100) NOT NULL, `Description` varchar(200) NOT NULL, `Status` int NOT NULL, `DelFlag` int NOT NULL, `CreatedOn` datetime NOT NULL, `CreatedBy` int NOT NULL, `ModifiedBy` int NOT NULL, `ModifiedOn` datetime NOT NULL, PRIMARY KEY (`PKCategoryID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ############################ Manufacture Category Table End ######################################## ############################ User Master Table start ########################################### CREATE TABLE IF NOT EXISTS `user_master` ( `PKUserID` int NOT NULL AUTO_INCREMENT, `FKStoreID` int NOT NULL, `Name` varchar(50) NOT NULL, `Email` varchar(50) NOT NULL, `Phone` varchar(50) NOT NULL, `DelFlag` int NOT NULL, `CreatedBy` int NOT NULL, `CreatedOn` datetime(5) NOT NULL, `ModifiedBy` int NOT NULL, `ModifiedOn` datetime NOT NULL, PRIMARY KEY (`PKUserID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ######################## User Master End ######################################### ###################### Item Store Start ############################################## CREATE TABLE IF NOT EXISTS `item_store` ( `PKStrItemID` int NOT NULL AUTO_INCREMENT, `FKItemID` int NOT NULL, `FKCmpID` int NOT NULL, `FKStoreID` int NOT NULL, `FKUnitID` int NOT NULL, `FKSupplierID` int NOT NULL, `Class` char(15) NOT NULL, `Qty` int NOT NULL, `Price` int NOT NULL, `Lcost` int NOT NULL, `ReOrderLevel` int NOT NULL, `MaxLevel` int NOT NULL, `TaxPer` int NOT NULL, `DelFlag` int NOT NULL, `CreatedBy` int NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int NOT NULL, `ModifiedOn` datetime NOT NULL, PRIMARY KEY (`PKStrItemID`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ALTER TABLE `item_store` CHANGE `FKSupplierID` `FKManufactureID` INT(11) NOT NULL; ################################## Item Store End #################################### ################################### Item Price Start ################################ CREATE TABLE IF NOT EXISTS `item_price` ( `PKItemPriceID` int NOT NULL, `FKItemID` int NOT NULL, `Price` int NOT NULL, `CreatedOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ############################# Item Price End ########################################### ############################# Orders start ########################################### CREATE TABLE `orders` (`id` INT NOT NULL AUTO_INCREMENT , `order_number` VARCHAR(100) NULL , `store_id` INT NULL , `customer_id` INT NULL , `total_amount` FLOAT NULL , `shipping_cost` FLOAT NULL , `vat` FLOAT NULL , `shipping_address` INT NULL , `billing_address` INT NULL , `payment_status` INT NOT NULL DEFAULT '0' , `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`)) ENGINE = InnoDB; ############################# Orders end ########################################### ############################# Account Master start########################################### CREATE TABLE `accounts_master` ( `PKAccID` int(11) NOT NULL AUTO_INCREMENT, `FKCmpID` int(11) DEFAULT NULL, `FKRouteID` int(11) DEFAULT NULL, `PrintOrder` varchar(50) DEFAULT NULL, `AccLevel` int(11) DEFAULT NULL, `ParentAccID` int(11) DEFAULT NULL, `AccCode` varchar(10) DEFAULT NULL, `AccName` varchar(100) DEFAULT NULL, `AccGroup` varchar(10) DEFAULT NULL, `Schedule` varchar(50) DEFAULT NULL, `DefTransType` varchar(10) DEFAULT NULL, `ControlAccount` varchar(10) DEFAULT NULL, `CostCenter` varchar(30) DEFAULT NULL, `Project` varchar(30) DEFAULT NULL, `Status` varchar(10) DEFAULT NULL, `AccType` varchar(10) DEFAULT NULL, `AccSubType` varchar(10) DEFAULT NULL, `ContactPerson` varchar(50) DEFAULT NULL, `Address1` varchar(50) DEFAULT NULL, `Address2` varchar(50) DEFAULT NULL, `Address3` varchar(50) DEFAULT NULL, `Phone` varchar(50) DEFAULT NULL, `Email` varchar(50) DEFAULT NULL, `URL` varchar(50) DEFAULT NULL, `VatNo` varchar(50) DEFAULT NULL, `LicenceNo` varchar(50) DEFAULT NULL, `OrderBy` float DEFAULT NULL, `DelFlag` varchar(1) DEFAULT NULL, `RepFlag` varchar(1) DEFAULT NULL, `CreatedBy` int(11) DEFAULT NULL, `CreatedOn` datetime DEFAULT NULL, `ModifiedBy` int(11) DEFAULT NULL, `ModifiedOn` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Account Master end########################################### ############################# Country start ########################################### CREATE TABLE `country` ( `PKCountryID` int(11) NOT NULL AUTO_INCREMENT, `CountryCode` varchar(10) NOT NULL, `CountryName` varchar(30) NOT NULL, `Currency` varchar(20) NOT NULL, `DelFlag` int(11) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Country end ########################################### ############################# Damage Items start ########################################### CREATE TABLE `damage_items` ( `PKDamageID` int(11) NOT NULL AUTO_INCREMENT, `FKItemID` int(11) NOT NULL, `quantity` int(11) NOT NULL, `description` varchar(100) NOT NULL, `date` datetime NOT NULL, `DelFlag` int(11) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Damage Items end ########################################### ############################# Employee Designation start########################################### CREATE TABLE `employee_designation` ( `PKdesigID` int(11) NOT NULL AUTO_INCREMENT, `Designation` varchar(100) NOT NULL, `Dcode` varchar(10) NOT NULL, `DelFlag` char(1) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Employee Designation end########################################### ############################# Employee Type start ########################################### CREATE TABLE `employee_type` ( `PKtypeID` int(11) NOT NULL AUTO_INCREMENT, `EmpType` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Employee Type end ########################################### ############################# Financial Year start ########################################### CREATE TABLE `financial_year` ( `PKFinYrID` int(11) NOT NULL AUTO_INCREMENT, `FKCmpID` int(11) NOT NULL, `FinYrCode` varchar(20) NOT NULL, `FinYrStartDate` datetime NOT NULL, `FinYrEndDate` datetime NOT NULL, `FinYrStatus` char(10) NOT NULL, `PresentYear` int(11) NOT NULL, `ClosedUpto` datetime NOT NULL, `DelFlag` char(1) NOT NULL, `RepFlag` char(1) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Financial Year end ########################################### ############################# Item Manufacture start ########################################### CREATE TABLE `item_manufacture` ( `PKManItemID` int(11) NOT NULL AUTO_INCREMENT, `FKItemID` int(11) NOT NULL, `FKManufactureID` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `CreatedBy` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Item Manufacture end ########################################### ############################# Opening Stock Child start ########################################### CREATE TABLE `opening_stock_child` ( `PKOpStkChildID` int(11) NOT NULL AUTO_INCREMENT, `FKOPStkID` int(11) NOT NULL, `FKItemID` int(11) NOT NULL, `FKUnitID` int(11) NOT NULL, `OPQty` float(18,3) NOT NULL, `OPLcost` float NOT NULL, `DelFlag` char(1) NOT NULL, `RepFlag` char(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Opening Stock Child end ########################################### ############################# Opening Stock Header start ########################################### CREATE TABLE `opening_stock_header` ( `PKOPStkID` int(11) NOT NULL AUTO_INCREMENT, `FKCmpID` int(11) NOT NULL, `FKFinYrID` int(11) NOT NULL, `FKStoreID` int(11) NOT NULL, `DateofEntry` datetime NOT NULL, `Remarks` varchar(100) NOT NULL, `DelFlag` char(1) NOT NULL, `RepFlag` char(1) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Opening Stock Header end ########################################### ############################# Purchase Child start ########################################### CREATE TABLE `purchase_child` ( `PKTransID` int(11) NOT NULL AUTO_INCREMENT, `FKPurchID` int(11) NOT NULL, `FKItemID` int(11) NOT NULL, `FKUnitID` int(11) NOT NULL, `Qty` float NOT NULL, `Price` float NOT NULL, `MRP` float NOT NULL, `Lcost` float NOT NULL, `DiscPer` float NOT NULL, `DiscAmt` float NOT NULL, `CashDisc` float NOT NULL, `Taxable` float NOT NULL, `TaxPer` float NOT NULL, `TaxAmt` float NOT NULL, `NetAmt` float NOT NULL, `CreatedBy` varchar(100) NOT NULL, `CreatedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; ############################# Purchase Child end ########################################### ############################# Purchase Header start ########################################### CREATE TABLE `purchase_header` ( `PKpurchID` int(11) NOT NULL AUTO_INCREMENT, `FKcmpID` int(11) NOT NULL, `FKFinYrID` int(11) NOT NULL, `FKCurrID` int(11) NOT NULL, `FKBPID` int(11) NOT NULL, `FKStoreID` int(11) NOT NULL, `FKSeriesID` int(11) NOT NULL, `FKOrderID` int(11) NOT NULL, `PurchNo` int(11) NOT NULL, `PurchDate` datetime NOT NULL, `InvNo` varchar(100) NOT NULL, `InvDate` date NOT NULL, `PurchType` varchar(10) NOT NULL, `RoundOff` float NOT NULL, `InvAmount` float NOT NULL, `VATPer` float NOT NULL, `VATAmt` float NOT NULL, `LessAmt` float NOT NULL, `AddAmt` float NOT NULL, `InvNetAmt` float NOT NULL, `Settled` int(11) NOT NULL, `Remarks` varchar(100) NOT NULL, `DelFlag` smallint(6) NOT NULL, `RepFlag` smallint(6) NOT NULL, `Posted` int(11) NOT NULL, `createdBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; ############################# Purchase Header end ########################################### ############################# Purchase return Child start ########################################### CREATE TABLE `purchase_return_child` ( `PkTransID` int(11) NOT NULL AUTO_INCREMENT, `FKPrID` int(11) NOT NULL, `FKItemID` int(11) NOT NULL, `FKUnitID` int(11) NOT NULL, `Qty` float NOT NULL, `Price` float NOT NULL, `MRP` float NOT NULL, `Lcost` float NOT NULL, `DiscPer` float NOT NULL, `DiscAmt` float NOT NULL, `CashDisc` float NOT NULL, `Taxable` float NOT NULL, `TaxPer` float NOT NULL, `TaxAmt` float NOT NULL, `CessPer` float NOT NULL, `CessAmt` float NOT NULL, `NetAmt` float NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; ############################# Purchase return Child end ########################################### ############################# Purchase return Header start ########################################### CREATE TABLE `purchase_return_header` ( `PKprId` int(11) NOT NULL AUTO_INCREMENT, `FKPurchID` int(11) NOT NULL, `FKCmpID` int(11) NOT NULL, `FKFinYrID` int(11) NOT NULL, `FKBPID` int(11) NOT NULL, `FKStoreID` int(11) NOT NULL, `FKSeriesID` int(11) NOT NULL, `PReturnNo` int(11) NOT NULL, `PReturnDate` datetime NOT NULL, `InvNo` varchar(50) NOT NULL, `InvDate` datetime NOT NULL, `PurchType` varchar(10) NOT NULL, `InvAmount` float NOT NULL, `Posted` int(11) NOT NULL, `DelFlag` smallint(6) NOT NULL, `RepFlag` smallint(6) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; ############################# Purchase return Header end ########################################### ############################# Requisition start ########################################### CREATE TABLE `requisition` ( `PKID` int(11) NOT NULL AUTO_INCREMENT, `VendorID` int(11) NOT NULL, `RequisitionID` int(11) NOT NULL, `FKItemID` int(11) NOT NULL, `Qty` int(11) NOT NULL, `VendorType` int(11) NOT NULL, `RequestedOn` datetime NOT NULL, `FKManufactureID` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Requisition end ########################################### ############################# Stock Transfer Master start ########################################### CREATE TABLE `stock_transfer_master` ( `PKTransferID` int(11) NOT NULL AUTO_INCREMENT, `FKCmpID` int(11) NOT NULL, `FKFinYrID` int(11) NOT NULL, `FKSeriesID` int(11) NOT NULL, `FKStoreIDFrom` int(11) NOT NULL, `FKStoreIDTo` int(11) NOT NULL, `TransferNo` int(11) NOT NULL, `DateOfTransfer` datetime NOT NULL, `RequestBy` varchar(50) NOT NULL, `IssuedBy` varchar(50) NOT NULL, `Remarks` varchar(50) NOT NULL, `DelFlag` char(1) NOT NULL, `RepFlag` char(1) NOT NULL, `ModuleName` char(10) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ############################# Stock Transfer Master end ########################################### ############################# Transfer Child start ########################################### CREATE TABLE `transfer_child` ( `PKTraChildID` int(11) NOT NULL AUTO_INCREMENT, `FKTransferID` int(11) NOT NULL, `FKItemID` int(11) NOT NULL, `FKUnitID` int(11) NOT NULL, `Qty` float NOT NULL, `Rate` float NOT NULL, `RepFlag` char(1) NOT NULL, `DelFlag` char(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ################### unit Table Start ############################################## CREATE TABLE IF NOT EXISTS `unit` ( `PKUnitID` int NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `code` varchar(10) NOT NULL, `DelFlag` int NOT NULL, `CreatedBy` int NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int NOT NULL, `ModifiedOn` datetime NOT NULL, PRIMARY KEY (`PKUnitID`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ################### Purchase Order Header Table Start ############################################## CREATE TABLE `purchase_order_header` ( `PKID` int(11) NOT NULL, `FKcmpID` int(11) NOT NULL, `FKFinYrID` int(11) NOT NULL, `FKCurrID` int(11) NOT NULL, `FKBPID` int(11) NOT NULL, `FKStoreID` int(11) NOT NULL, `FKSeriesID` int(11) NOT NULL, `OrderNo` int(11) NOT NULL, `OrderDate` datetime NOT NULL, `PurchType` varchar(10) NOT NULL, `InvAmount` float NOT NULL, `VATPer` float NOT NULL, `VATAmt` float NOT NULL, `LessAmt` float NOT NULL, `AddAmt` float NOT NULL, `InvNetAmt` float NOT NULL, `Settled` int(11) NOT NULL, `Remarks` varchar(100) NOT NULL, `DelFlag` smallint(6) NOT NULL, `RepFlag` smallint(6) NOT NULL, `Posted` int(11) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; ################### Purchase Order Child Table Start ############################################## CREATE TABLE `purchase_order_child` ( `PKTransID` int(11) NOT NULL, `FKOrderID` int(11) NOT NULL, `FKItemID` int(11) NOT NULL, `FKUnitID` int(11) NOT NULL, `Qty` float NOT NULL, `Price` float NOT NULL, `MRP` float NOT NULL, `Lcost` float NOT NULL, `DiscPer` float NOT NULL, `DiscAmt` float NOT NULL, `CashDisc` float NOT NULL, `Taxable` float NOT NULL, `TaxPer` float NOT NULL, `TaxAmt` float NOT NULL, `NetAmt` float NOT NULL, `DelFlag` tinyint(4) NOT NULL DEFAULT 0, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL DEFAULT current_timestamp(), `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; ################### Purchase Order Child Table End ############################################## ############################# Transfer Child end ########################################### ############################# material_receipt_master Start ########################################### CREATE TABLE `material_receipt_master` ( `PKGRNID` int(11) NOT NULL, `FKCmpID` int(11) NOT NULL, `FkFinYrID` int(11) NOT NULL, `FKStoreID` int(11) NOT NULL, `FKSeriesID` int(11) NOT NULL, `FKManufactureID` int(11) NOT NULL, `FKPOID` int(11) NOT NULL, `FKPurchID` int(11) NOT NULL, `GRNNo` varchar(50) NOT NULL, `ReceiptDate` datetime NOT NULL, `Remarks` varchar(25) NOT NULL, `DelFlag` char(1) NOT NULL, `RepFlag` char(1) NOT NULL, `CreatedBy` int(11) NOT NULL, `CreatedOn` datetime NOT NULL, `ModifiedBy` int(11) NOT NULL, `ModifiedOn` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; ############################# material_receipt_master End ########################################### ############################# material_receipt_child Start ########################################### CREATE TABLE `material_receipt_child` ( `PKTransID` int(11) NOT NULL, `FKGRNID` int(11) NOT NULL, `FKItemID` int(11) NOT NULL, `FKUnitID` int(11) NOT NULL, `Qty` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; ############################# material_receipt_child End ########################################### CREATE procedure [dbo].[GS_CurrentStock] @FinYrID AS integer, @AsonDate as DateTime, @StoreID as integer, @ItemID as Integer AS SELECT s.PKItemID, isnull((SELECT sum(a.Qty) FROM GSPurchChild a inner join GSPurchParent b ON a.FKpurchID = b.PKpurchID WHERE a.FKItemID = @ItemID and b.FKStoreID = @StoreID and b.PurchDate <= @AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' GROUP BY a.FKItemID),0) AS Purch, isnull((SELECT sum(a.Qty) FROM GSPurchReturnChild a inner join GSPurchReturnParent b ON a.FKprID = b.PKprID WHERE a.FKItemID = @ItemID and b.FKStoreID = @StoreID and b.PReturnDate <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' GROUP BY a.FKItemID),0) AS PRET, isnull((SELECT sum(a.Qty) FROM GSTransferChild a inner join GSTransferParent b ON a.FKTransferID = b.PKTransferID WHERE a.FKItemID = @ItemID and b.FKStoreIDto = @StoreID and b.DateOfTransfer <= @AsonDate and b.FKFinYrID = @FinYrID and a.DelFlag<>'D' and b.DelFlag<>'D' GROUP BY a.FKItemID),0) AS TRIN, isnull((SELECT sum(a.Qty) FROM GSTransferChild a inner join GSTransferParent b ON a.FKTransferID = b.PKTransferID WHERE a.FKItemID = @ItemID and b.FKStoreIDFrom = @StoreID and b.DateOfTransfer <=@AsonDate and b.FKFinYrID = @FinYrID and a.DelFlag<>'D' and b.DelFlag<>'D' GROUP BY a.FKItemID),0) AS TROUT, isnull((SELECT sum(a.Qty) FROM GSSalesChild a inner join GSSalesParent b ON a.FKSalesID = b.PKSalesID WHERE a.FKItemID = @ItemID and b.FKStoreID = @StoreID and b.BillDate <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' GROUP BY a.FKItemID),0) AS SALES, isnull((SELECT sum(a.Qty) FROM GSSalesReturnChild a inner join GSSalesReturnParent b ON a.FKSRID = b.PKSRID WHERE a.FKItemID = @ItemID and b.FKStoreID = @StoreID and b.BillDate <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' GROUP BY a.FKItemID),0) AS SRET, isnull((SELECT sum(a.opQty) as Qty FROM GSOPStockChild a inner join GSOPStock b ON a.FKOPstkID = b.PKOPstkID WHERE a.FKItemID = @ItemID and b.FKStoreID = @StoreID and b.FKFinYrID = @FinYrID and a.DelFlag<>'D' and b.DelFlag<>'D' GROUP BY a.FKItemID),0) AS OPSTK FROM GSMItem s WHERE s.PKItemID =@ItemID GO DELIMITER $$ CREATE PROCEDURE GS_CurrentStock_Allitems( IN CmpID INT, IN FinYrID INT, IN AsonDate DATETIME, IN StoreID INT ) BEGIN -- Drop temporary table if it exists DROP TEMPORARY TABLE IF EXISTS Table1; -- Select opening stock CREATE TEMPORARY TABLE Table1 SELECT a.FKItemID, IFNULL(SUM(a.opQty), 0) AS qty FROM opening_stock_child a INNER JOIN opening_stock_header b ON a.FKOPStkID = b.PKOPStkID WHERE b.FKStoreID = StoreID AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag <> 'D' AND b.DelFlag <> 'D' GROUP BY a.FKItemID; -- Union all with purchases INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_child a INNER JOIN purchase_header b ON a.FKPurchID = b.PKPurchID WHERE b.FKStoreID = StoreID AND b.PurchDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag <> 'D' GROUP BY a.FKItemID; -- Union all with purchase returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_return_child a INNER JOIN purchase_return_header b ON a.FKReturnID = b.PKPrID WHERE b.FKStoreID = StoreID AND b.PReturnDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag <> 'D' GROUP BY a.FKItemID; -- Union all with sales INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM sales_child a INNER JOIN sales_parent b ON a.FKSalesID = b.PKSalesID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag <> 'D' GROUP BY a.FKItemID; -- Union all with sales returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM sales_return_child a INNER JOIN sales_return_parent b ON a.FKSRID = b.PKSRID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag <> 'D' GROUP BY a.FKItemID; -- Union all with transfers (from store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDFrom = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag <> 'D' AND b.DelFlag <> 'D' GROUP BY a.FKItemID; -- Union all with transfers (to store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDTo = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag <> 'D' AND b.DelFlag <> 'D' GROUP BY a.FKItemID; -- Update gsmitem stock quantities UPDATE item_store g SET g.qty = (SELECT SUM(t.qty) FROM Table1 t WHERE g.PKItemID = t.FKItemID GROUP BY t.FKItemID); -- Final select statement SELECT FKItemID, IFNULL(SUM(qty), 0) AS qty FROM Table1 GROUP BY FKItemID; -- Drop the temporary table DROP TEMPORARY TABLE IF EXISTS Table1; END $$ DELIMITER ; CREATE procedure [dbo].[GS_CurrentStock_Allitems] @CmpID AS integer, @FinYrID AS integer, @AsonDate as DateTime, @StoreID as integer AS SELECT a.FKItemID, isnull(sum(a.opQty),0) as qty into #Table1 FROM opening_stock_child a inner join opening_stock_header b ON a.FKOPStkID = b.PKOPStkID WHERE b.FKStoreID = @StoreID and b.FKFinYrID = @FinYrID and b.FKCmpID=@CmpID and a.DelFlag<>'D' and b.DelFlag<>'D' GROUP BY a.FKItemID union all SELECT a.FKItemID, isnull(sum(a.Qty),0) as qty FROM purchase_child a inner join purchase_header b ON a.FKPurchID = b.PKPurchID WHERE b.FKStoreID = @StoreID and b.PurchDate <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' and b.FKCmpID=@CmpID GROUP BY a.FKItemID union all SELECT a.FKItemID, -1*isnull(sum(a.Qty),0) as qty FROM purchase_return_child a inner join purchase_return_header b ON a.FKReturnID = b.PKPrID WHERE b.FKStoreID = @StoreID and b.PReturnDate <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' and b.FKCmpID=@CmpID GROUP BY a.FKItemID union all SELECT a.FKItemID, -1*isnull(sum(a.Qty),0) as qty FROM sales_child a inner join sales_parent b ON a.FKSalesID = b.PKSalesID WHERE b.FKStoreID = @StoreID and b.BillDate <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' and b.FKCmpID=@CmpID GROUP BY a.FKItemID union all SELECT a.FKItemID, isnull(sum(a.Qty),0) as qty FROM sales_return_child a inner join sales_return_parent b ON a.FKSRID = b.PKSRID WHERE b.FKStoreID = @StoreID and b.BillDate <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' and b.FKCmpID=@CmpID GROUP BY a.FKItemID union all SELECT a.FKItemID, -1*isnull(sum(a.Qty),0) as qty FROM transfer_child a inner join stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDFrom = @StoreID and b.DateOfTransfer <= @AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' and b.FKCmpID=@CmpID and a.DelFlag<>'D' and b.DelFlag<>'D' GROUP BY a.FKItemID union all SELECT a.FKItemID, isnull(sum(a.Qty),0) as qty FROM transfer_child a inner join stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDTo = @StoreID and b.DateOfTransfer <=@AsonDate and b.FKFinYrID = @FinYrID and b.DelFlag<>'D' and b.FKCmpID=@CmpID and a.DelFlag<>'D' and b.DelFlag<>'D' GROUP BY a.FKItemID update gsmitem set gsmitem.qty = (select SUM(qty) AS Qty from #TABLE1 b where gsmitem.pkitemid=b.fkitemid GROUP BY PKITEMID) SELECT FKItemID, isnull(sum(qty),0) as qty FROM #TABLE1 GROUP BY FKItemID drop table #Table1 GO ***************************** DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `CURRENT_STOCK`(IN `CmpID` INT, IN `FinYrID` INT, IN `AsonDate` DATE, IN `StoreID` INT) BEGIN -- Drop temporary table if it exists DROP TEMPORARY TABLE IF EXISTS Table1; -- Select opening stock CREATE TEMPORARY TABLE Table1 SELECT a.FKItemID, IFNULL(SUM(a.opQty), 0) AS qty FROM opening_stock_child a INNER JOIN opening_stock_header b ON a.FKOPStkID = b.PKOPStkID WHERE b.FKStoreID = StoreID AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with purchases INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_child a INNER JOIN purchase_header b ON a.FKPurchID = b.PKPurchID WHERE b.FKStoreID = StoreID AND b.PurchDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with purchase returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_return_child a INNER JOIN purchase_return_header b ON a.FKReturnID = b.PKPrID WHERE b.FKStoreID = StoreID AND b.PReturnDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with sales INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM sales_child a INNER JOIN sales_header b ON a.FKSalesID = b.PKSalesID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with sales returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM sales_return_child a INNER JOIN sales_return_parent b ON a.FKSRID = b.PKSRID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with transfers (from store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDFrom = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with transfers (to store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDTo = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; UPDATE item_store g SET g.qty = (SELECT SUM(t.qty) FROM Table1 t WHERE g.FKItemID = t.FKItemID AND g.FKStoreID = StoreID GROUP BY t.FKItemID); -- Drop the temporary table DROP TEMPORARY TABLE IF EXISTS Table1; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GS_CurrentStock_Allitems`(IN `CmpID` INT, IN `FinYrID` INT, IN `AsonDate` DATE, IN `StoreID` INT) BEGIN -- Drop temporary table if it exists DROP TEMPORARY TABLE IF EXISTS Table1; -- Select opening stock CREATE TEMPORARY TABLE Table1 SELECT a.FKItemID, IFNULL(SUM(a.opQty), 0) AS qty FROM opening_stock_child a INNER JOIN opening_stock_header b ON a.FKOPStkID = b.PKOPStkID WHERE b.FKStoreID = StoreID AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with purchases INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_child a INNER JOIN purchase_header b ON a.FKPurchID = b.PKPurchID WHERE b.FKStoreID = StoreID AND b.PurchDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with purchase returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_return_child a INNER JOIN purchase_return_header b ON a.FKReturnID = b.PKPrID WHERE b.FKStoreID = StoreID AND b.PReturnDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with sales INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM sales_child a INNER JOIN sales_header b ON a.FKSalesID = b.PKSalesID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with sales returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM sales_return_child a INNER JOIN sales_return_parent b ON a.FKSRID = b.PKSRID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with transfers (from store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDFrom = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with transfers (to store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDTo = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Update gsmitem stock quantities UPDATE item_store g SET g.qty = (SELECT SUM(t.qty) FROM Table1 t WHERE g.FKItemID = t.FKItemID AND g.FKStoreID = StoreID GROUP BY t.FKItemID); -- Final select statement SELECT FKItemID, IFNULL(SUM(qty), 0) AS qty FROM Table1 GROUP BY FKItemID; -- Drop the temporary table DROP TEMPORARY TABLE IF EXISTS Table1; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GS_CurrentStock_Allitems_report`(IN `CmpID` INT, IN `FinYrID` INT, IN `AsonDate` DATE, IN `StoreID` INT) BEGIN -- Drop temporary table if it exists DROP TEMPORARY TABLE IF EXISTS Table1; -- Select opening stock CREATE TEMPORARY TABLE Table1 SELECT a.FKItemID, IFNULL(SUM(a.opQty), 0) AS qty FROM opening_stock_child a INNER JOIN opening_stock_header b ON a.FKOPStkID = b.PKOPStkID WHERE b.FKStoreID = StoreID AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with purchases INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_child a INNER JOIN purchase_header b ON a.FKPurchID = b.PKPurchID WHERE b.FKStoreID = StoreID AND b.PurchDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with purchase returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM purchase_return_child a INNER JOIN purchase_return_header b ON a.FKReturnID = b.PKPrID WHERE b.FKStoreID = StoreID AND b.PReturnDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with sales INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM sales_child a INNER JOIN sales_header b ON a.FKSalesID = b.PKSalesID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with sales returns INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM sales_return_child a INNER JOIN sales_return_parent b ON a.FKSRID = b.PKSRID WHERE b.FKStoreID = StoreID AND b.BillDate <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with transfers (from store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, -1 * IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDFrom = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Union all with transfers (to store) INSERT INTO Table1 (FKItemID, qty) SELECT a.FKItemID, IFNULL(SUM(a.Qty), 0) AS qty FROM transfer_child a INNER JOIN stock_transfer_master b ON a.FKTransferID = b.PKTransferID WHERE b.FKStoreIDTo = StoreID AND b.DateOfTransfer <= AsonDate AND b.FKFinYrID = FinYrID AND b.FKCmpID = CmpID AND a.DelFlag = 0 AND b.DelFlag = 0 GROUP BY a.FKItemID; -- Final select statement -- Final select statement with ItemName SELECT i.ItemName, -- Selecting ItemName from the item table t.FKItemID, IFNULL(SUM(t.qty), 0) AS qty FROM Table1 t INNER JOIN item i ON i.PKItemID = t.FKItemID -- Joining with item table GROUP BY t.FKItemID, i.ItemName; -- Group by FKItemID and ItemName -- Drop the temporary table DROP TEMPORARY TABLE IF EXISTS Table1; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPurchaseReport`( IN StoreID INT, IN FinYrID INT, IN StartDate DATE, IN EndDate DATE ) BEGIN SELECT a.FKItemID, b.ItemName, SUM(a.Qty) AS PurchasedQuantity, SUM(a.Price * a.Qty) AS TotalPurchaseAmount FROM purchase_child a INNER JOIN purchase_header c ON a.FKPurchID = c.PKPurchID INNER JOIN item b ON a.FKItemID = b.PKItemID WHERE c.FKStoreID = StoreID AND c.PurchDate BETWEEN StartDate AND EndDate AND c.FKFinYrID = FinYrID AND c.DelFlag = 0 GROUP BY a.FKItemID, b.ItemName; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPurchaseReturnReport`( IN StoreID INT, IN FinYrID INT, IN StartDate DATE, IN EndDate DATE ) BEGIN SELECT a.FKItemID, b.ItemName, SUM(a.Qty) AS ReturnedQuantity, SUM(a.Price * a.Qty) AS TotalReturnAmount FROM purchase_return_child a INNER JOIN purchase_return_header c ON a.FKReturnID = c.PKPrID INNER JOIN item b ON a.FKItemID = b.PKItemID WHERE c.FKStoreID = StoreID AND c.PReturnDate BETWEEN StartDate AND EndDate AND c.FKFinYrID = FinYrID AND c.DelFlag = 0 GROUP BY a.FKItemID, b.ItemName; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSalesReport`( IN StoreID INT, IN FinYrID INT, IN StartDate DATE, IN EndDate DATE ) BEGIN SELECT a.FKItemID, b.ItemName, SUM(a.Qty) AS TotalQuantity, SUM(a.Price * a.Qty) AS TotalAmount FROM sales_child a INNER JOIN sales_header c ON a.FKSalesID = c.PKSalesID INNER JOIN item b ON a.FKItemID = b.PKItemID WHERE c.FKStoreID = StoreID AND c.BillDate BETWEEN StartDate AND EndDate AND c.FKFinYrID = FinYrID AND c.DelFlag = 0 GROUP BY a.FKItemID, b.ItemName; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSalesReturnReport`( IN StoreID INT, IN FinYrID INT, IN StartDate DATE, IN EndDate DATE ) BEGIN SELECT a.FKItemID, b.ItemName, SUM(a.Qty) AS ReturnedQuantity, SUM(a.Price * a.Qty) AS TotalReturnAmount FROM sales_return_child a INNER JOIN sales_return_parent c ON a.FKSRID = c.PKSRID INNER JOIN item b ON a.FKItemID = b.PKItemID WHERE c.FKStoreID = StoreID AND c.BillDate BETWEEN StartDate AND EndDate AND c.FKFinYrID = FinYrID AND c.DelFlag = 0 GROUP BY a.FKItemID, b.ItemName; END$$ DELIMITER ; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GetStockTransferReport`( IN StoreID INT, IN FinYrID INT, IN StartDate DATE, IN EndDate DATE ) BEGIN -- Transfers From Store SELECT a.FKItemID, b.ItemName, SUM(a.Qty) AS TransferredQuantity FROM transfer_child a INNER JOIN stock_transfer_master c ON a.FKTransferID = c.PKTransferID INNER JOIN item b ON a.FKItemID = b.PKItemID WHERE c.FKStoreIDFrom = StoreID AND c.DateOfTransfer BETWEEN StartDate AND EndDate AND c.FKFinYrID = FinYrID AND c.DelFlag = 0 GROUP BY a.FKItemID, b.ItemName; -- Transfers To Store SELECT a.FKItemID, b.ItemName, SUM(a.Qty) AS ReceivedQuantity FROM transfer_child a INNER JOIN stock_transfer_master c ON a.FKTransferID = c.PKTransferID INNER JOIN item b ON a.FKItemID = b.PKItemID WHERE c.FKStoreIDTo = StoreID AND c.DateOfTransfer BETWEEN StartDate AND EndDate AND c.FKFinYrID = FinYrID AND c.DelFlag = 0 GROUP BY a.FKItemID, b.ItemName; END$$ DELIMITER ; *********************************************