#################################### 11-04-2024

ALTER TABLE `tblitems` ADD `barcode` VARCHAR(100) NULL DEFAULT NULL AFTER `itemcode`;

################################# 20-07-2024

ALTER TABLE `tblpurchase` ADD `termsid` INT(11) NULL DEFAULT '0' AFTER `carrierid`;

ALTER TABLE `tblpurchasereturn` ADD `termsid` INT(11) NULL DEFAULT '0' AFTER `carrierid`;

################################# 23-07-2024

ALTER TABLE `tblcustomerpayments` ADD `posted` TINYINT(4) NULL DEFAULT '0' AFTER `status`;

ALTER TABLE `tblsupplierpayments` ADD `posted` TINYINT(4) NULL DEFAULT '0' AFTER `status`;

######################################## 03-08-2024

ALTER TABLE `tbldashboard` ADD `inactive` TINYINT(4) NULL DEFAULT '0' AFTER `imageurl`;

######################################## 04-09-2024

ALTER TABLE `tbldashboard` ADD `component` VARCHAR(100) NULL DEFAULT NULL AFTER `label`;

INSERT INTO `config` (`id`, `created_at`, `updated_at`, `name`, `value`) VALUES (NULL, '2024-09-04 11:01:35', '2024-09-04 11:01:35', 'taxtitle', 'Vat'), (NULL, '2024-09-04 11:01:35', '2024-09-04 11:01:35', 'taxafterdiscount', '1');

ALTER TABLE `tblinvoicereturn_detail` ADD `barcode` VARCHAR(100) NULL DEFAULT NULL AFTER `details`;

ALTER TABLE `tblpurchasedetail` ADD `barcode` VARCHAR(100) NULL DEFAULT NULL AFTER `details`;

ALTER TABLE `tblpurchasereturn_detail` ADD `barcode` VARCHAR(100) NULL DEFAULT NULL AFTER `details`;

ALTER TABLE `tblinvoice` ADD `tax` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `expense`, ADD `taxamount` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `tax`;

ALTER TABLE `tblinvoicereturn` ADD `tax` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `expense`, ADD `taxamount` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `tax`;

######################################## 05-09-2024

ALTER TABLE `tblpurchasereturn` ADD `tax` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `expense`, ADD `taxamount` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `tax`;

ALTER TABLE `tblpurchase` ADD `tax` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `expense`, ADD `taxamount` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `tax`;

ALTER TABLE `tbldashboard` ADD `component` VARCHAR(100) NULL DEFAULT NULL AFTER `imageurl`;

ALTER TABLE `tbldashboard` ADD `category` VARCHAR(25) NULL DEFAULT NULL AFTER `component`, ADD `sequence` TINYINT(4) NOT NULL DEFAULT '0' AFTER `category`;


######################################## 07-09-2024

ALTER TABLE `tblinvoice` CHANGE `AdvancePayment` `cashreceived` DECIMAL(32,10) NULL DEFAULT '0.0000000000';

ALTER TABLE `tblgltransaction` ADD `qty` DECIMAL(18,6) NOT NULL DEFAULT '0' AFTER `path`, ADD `price` DECIMAL(32,10) NOT NULL DEFAULT '0' AFTER `qty`;


######################################## 09-09-2024

ALTER TABLE `tblinventorylocation` ADD `phone1code` INT(11) NOT NULL DEFAULT '0' AFTER `phone1`;

######################################## 10-09-2024

DELETE FROM `tbldashboard` WHERE `tbldashboard`.`id` = 9 LIMIT 1;
DELETE FROM `tbldashboard` WHERE `tbldashboard`.`id` = 10 LIMIT 1;
DELETE FROM `tbldashboard` WHERE `tbldashboard`.`id` = 11 LIMIT 1;

INSERT INTO `tbldashboard` (`id`, `label`, `imageurl`, `inactive`, `created_at`, `updated_at`) VALUES (NULL, 'Sales Table', '/dashboard/salestable.jpg', '0', '2024-09-10 11:10:13', '2024-09-10 11:10:13'), (NULL, 'Receipt Table', '/dashboard/receiptstable.jpg', '0', '2024-09-10 11:10:13', '2024-09-10 11:10:13'), (NULL, 'Purchases Table', '/dashboard/purchasetable.jpg', '0', '2024-09-10 11:10:13', '2024-09-10 11:10:13'), (NULL, 'Payments Table', '/dashboard/paymentstable.jpg', '0', '2024-09-10 11:10:13', '2024-09-10 11:10:13');


######################################## 14-09-2024

CREATE OR REPLACE VIEW view_account_balance_summary AS
            SELECT
                act.id AS accounttypeid,
                act.basicacctid AS basicacctid,
                ac.summaryaccountid AS groupacctid,
                ac.acctnumber AS acctnumber,
                ts.glaccountid AS glaccountid,
                tv.entrydate As entrydate,
                ts.credit AS credit,
                ts.debit AS debit,
                ts.branchid AS branchid,
                ts.posted AS posted
            FROM
                tblglaccount ac
            JOIN
                tblaccounttype act ON ac.accountypeid = act.id
            JOIN
                tblgltransaction ts ON ac.id = ts.glaccountid
            JOIN
                tblgltransvoucher tv ON ts.voucherno = tv.voucherno AND tv.branchid = ts.branchid



CREATE OR REPLACE VIEW view_trial_balance AS
            SELECT
                act.id AS accounttypeid,
                act.basicacctid AS basicacctid,
                ac.summaryaccountid AS groupacctid,
                ac.acctnumber AS acctnumber,
                ts.glaccountid AS glaccountid,
                tv.entrydate As entrydate,
                ts.credit AS credit,
                ts.debit AS debit,
                ts.branchid AS branchid,
                ts.posted AS posted
            FROM
                tblglaccount ac
            JOIN
                tblaccounttype act ON ac.accountypeid = act.id
            JOIN
                tblgltransaction ts ON ac.id = ts.glaccountid
            JOIN
                tblgltransvoucher tv ON ts.voucherno = tv.voucherno AND tv.branchid = ts.branchid


######################################## 16-09-2024

ALTER TABLE `tblposinvoice` ADD `bankid` INT(11) NULL DEFAULT '0' AFTER `cashcounterid`, ADD `bankreceived` DECIMAL(32,10) NULL DEFAULT '0' AFTER `bankid`, ADD `hold` TINYINT(4) NULL DEFAULT '0' AFTER `bankreceived`;

ALTER TABLE `tblposinvoicedet` ADD `hold` TINYINT(4) NULL DEFAULT '0' AFTER `netamount`;

######################################## 20-09-2024

ALTER TABLE `tblapcompany` ADD `tax` INT(11) NULL DEFAULT '0' AFTER `expense`;

INSERT INTO `config` (`id`, `created_at`, `updated_at`, `name`, `value`) VALUES (NULL, '2024-09-20 19:01:19', '2024-09-20 19:01:19', 'chequedigits', '8');

######################################## 21-09-2024

ALTER TABLE `tblgltransaction` CHANGE `price` `price` DECIMAL(32,10) NULL DEFAULT '0.0000000000';

ALTER TABLE `tblgltransaction` CHANGE `qty` `qty` DECIMAL(18,6) NULL DEFAULT '0.000000';

ALTER TABLE `tblitemtrans` ADD `companyid` INT(11) NULL DEFAULT '0' AFTER `ITDate`;

######################################## 23-09-2024

ALTER TABLE `tblapcompany` ADD `cheque` INT(11) NULL DEFAULT '0' AFTER `tax`;

######################################## 28-09-2024

ALTER TABLE `tbluser_rights` ADD `purchaseprice_rights` TINYINT(4) NULL DEFAULT '0' AFTER `otherdate_rights`, ADD `salesprice_rights` TINYINT(4) NULL DEFAULT '0' AFTER `purchaseprice_rights`;

######################################## 04-10-2024

CREATE TABLE `tbladmintranslog` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `LogDateTime` datetime NOT NULL,
  `EventName` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TransType` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TransID` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UserID` int(11) DEFAULT NULL,
  `yearid` int(11) DEFAULT NULL,
  `branchid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
COMMIT;

ALTER TABLE `tbladmintranslog` CHANGE `id` `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`id`);

######################################## 05-10-2024

INSERT INTO `tblmenutypes` (`id`, `type`, `created_at`, `updated_at`) VALUES ('7', 'Flowchart & Reports List', '2024-10-05 15:54:34', '2024-10-05 15:54:34');


######################################## 18-10-2024

ALTER TABLE `tblthemecustomization` ADD `shortcutbar` VARCHAR(50) NULL DEFAULT NULL AFTER `footerType`;

######################################## 26-10-2024

ALTER TABLE `tblglaccount` ADD `disable` TINYINT(4) NULL DEFAULT '0' AFTER `systemacct`;

######################################## 18-11-2024

ALTER TABLE tblsuppliers ADD phone1code INT(11) NULL DEFAULT '0' AFTER phone1;

ALTER TABLE tblsuppliers ADD cellphonecode INT(11) NULL DEFAULT '0' AFTER cellphone;

######################################## 12-12-2024

ALTER TABLE `tblposinvoice` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblposinvoicedet` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblinvoice` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblinvoicedetail` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblinvoicereturn` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblinvoicereturn_detail` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblpurchase` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblpurchasedetail` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblpurchasereturn` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblpurchasereturn_detail` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblsupplierpayments` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblcustomerpayments` ADD `userid` TINYINT(4) NOT NULL AFTER `yearid`;

ALTER TABLE `tblposinvoice` ADD `phonecode` INT(11) NULL DEFAULT '0' AFTER `phone`;

######################################## 12-12-2024

ALTER TABLE `tblbranch` DROP `poscustomerid`;

######################################## 17-12-2024

INSERT INTO `config` (`id`, `created_at`, `updated_at`, `name`, `value`) VALUES (NULL, '2024-12-17 11:18:29', '2024-12-17 11:18:29', 'defaultItems', '0'), (NULL, '2024-12-17 11:18:29', '2024-12-17 11:18:29', 'showTax', '1');

ALTER TABLE `tblitems` ADD `isservice` TINYINT(4) NOT NULL DEFAULT '0' AFTER `negativestock`;

INSERT INTO `tblsublinks` (`id`, `url`, `component`, `created_at`, `updated_at`) VALUES (NULL, 'services/editinvoice', 'EditServicesInvoice', '2024-12-17 12:52:45', '2024-12-17 12:52:45');


--
-- Table structure for table `tblservicesinvoice`
--

CREATE TABLE `tblservicesinvoice` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `invoicenumber` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entrydate` date NOT NULL,
  `customerid` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `salestype` int(11) NOT NULL DEFAULT 0,
  `amount` decimal(32,10) NOT NULL DEFAULT 0.0000000000,
  `tax` decimal(32,10) DEFAULT 0.0000000000,
  `taxamount` decimal(32,10) DEFAULT 0.0000000000,
  `netamount` decimal(32,10) NOT NULL DEFAULT 0.0000000000,
  `status` tinyint(4) DEFAULT 0,
  `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `discount` decimal(32,10) DEFAULT 0.0000000000,
  `cashreceived` decimal(32,10) DEFAULT 0.0000000000,
  `bankid` int(11) DEFAULT 0,
  `bankreceived` decimal(32,10) DEFAULT 0.0000000000,
  `branchid` tinyint(4) NOT NULL,
  `yearid` tinyint(4) NOT NULL,
  `userid` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblservicesinvoice`
--
ALTER TABLE `tblservicesinvoice`
  ADD PRIMARY KEY (`id`),
  ADD KEY `customerid` (`customerid`),
  ADD KEY `branchid` (`branchid`),
  ADD KEY `entrydate` (`entrydate`),
  ADD KEY `salestype` (`salestype`),
  ADD KEY `yearid` (`yearid`),
  ADD KEY `userid` (`userid`),
  ADD KEY `tblservicesinvoice_customerid_index` (`customerid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblservicesinvoice`
--
ALTER TABLE `tblservicesinvoice`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblservicesinvoice`
--
ALTER TABLE `tblservicesinvoice`
  ADD CONSTRAINT `tblservicesinvoice_customerid_foreign` FOREIGN KEY (`customerid`) REFERENCES `tblcustomers` (`id`);


-- --------------------------------------------------------

--
-- Table structure for table `tblservicesinvoicedetail`
--

CREATE TABLE `tblservicesinvoicedetail` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `invoiceid` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `serviceid` bigint(20) UNSIGNED NOT NULL,
  `details` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` decimal(32,10) NOT NULL DEFAULT 0.0000000000,
  `qty` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(32,10) NOT NULL DEFAULT 0.0000000000,
  `branchid` tinyint(4) NOT NULL,
  `yearid` tinyint(4) NOT NULL,
  `userid` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblservicesinvoicedetail`
--
ALTER TABLE `tblservicesinvoicedetail`
  ADD PRIMARY KEY (`id`),
  ADD KEY `serviceid` (`serviceid`),
  ADD KEY `branchid` (`branchid`),
  ADD KEY `yearid` (`yearid`),
  ADD KEY `userid` (`userid`),
  ADD KEY `tblservicesinvoicedetail_serviceid_index` (`serviceid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblservicesinvoicedetail`
--
ALTER TABLE `tblservicesinvoicedetail`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblservicesinvoicedetail`
--
ALTER TABLE `tblservicesinvoicedetail`
  ADD CONSTRAINT `tblservicesinvoicedetail_serviceid_foreign` FOREIGN KEY (`serviceid`) REFERENCES `tblitems` (`id`);


INSERT INTO `tblids` (`id`, `description`, `code`, `value`, `comments`, `branchid`, `codetype`, `created_at`, `updated_at`) VALUES (NULL, 'Services Invoice', 'SSI', '1', 'Services Invoice', '1', '0', '2024-12-17 13:05:47', '2024-12-17 13:05:47');

ALTER TABLE `tblarcompany` ADD `servicesDiscount` INT NULL DEFAULT '0' AFTER `tax`;


ALTER TABLE `tblarcompany` ADD `services` INT NULL DEFAULT '0' AFTER `servicesDiscount`;


ALTER TABLE `tblbranch` ADD `cashcustomerid` INT(11) NULL DEFAULT '0' AFTER `currencyid`;

ALTER TABLE `tblservicesinvoice` ADD `phonecode` INT(11) NULL DEFAULT '0' AFTER `phone`;


######################################## 24-12-2024

ALTER TABLE `tblservicesinvoice` ADD `cashcounterid` INT(11) NULL DEFAULT '0' AFTER `customerid`;

ALTER TABLE `tbluser_rights` ADD `userwise_entries` TINYINT(4) NULL DEFAULT '0' AFTER `salesprice_rights`;

INSERT INTO `admin_menus` (`id`, `parentid`, `sequence`, `label`, `icon`, `url`, `inactive`, `created_at`, `updated_at`) VALUES (NULL, '0', '10', 'Module Wise Config', 'fa-regular fa-circle', 'module-configuration-form', '0', '2024-12-24 15:46:53', '2024-12-24 15:46:53');

######################################## 26-12-2024

INSERT INTO `tblmoduleconfig` (`id`, `moduleid`, `name`, `value`, `created_at`, `updated_at`) VALUES (NULL, '438', 'showcashcounter', '1', '2024-12-26 15:18:34', '2024-12-26 15:18:34');

######################################## 06-02-2025

INSERT INTO `config` (`id`, `created_at`, `updated_at`, `name`, `value`) VALUES (NULL, '2025-02-06 11:22:41', '2025-02-06 11:22:41', 'showinvoices', '25'), (NULL, '2025-02-06 11:22:41', '2025-02-06 11:22:41', 'showreceipts', '25'), (NULL, '2025-02-06 11:22:41', '2025-02-06 11:22:41', 'showpurchases', '25'), (NULL, '2025-02-06 11:22:41', '2025-02-06 11:22:41', 'showpayments', '25');



######################################## 18-04-2025

-- phpMyAdmin SQL Dump
-- version 6.0.0-dev+20230606.a4940d79ec
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 18, 2025 at 10:37 AM
-- Server version: 10.4.24-MariaDB
-- PHP Version: 8.1.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `its_mobile24`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblposconfigurations`
--

DROP TABLE IF EXISTS `tblposconfigurations`;
CREATE TABLE `tblposconfigurations` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `customerid` bigint(20) UNSIGNED NOT NULL,
  `itemprice` tinyint(4) DEFAULT 0,
  `showitems` tinyint(4) DEFAULT 0,
  `showtax` tinyint(4) DEFAULT 0,
  `fullscreen` tinyint(4) DEFAULT 0,
  `admincashacctid` int(11) DEFAULT 0,
  `closingtime` time DEFAULT NULL,
  `branchid` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblposconfigurations`
--

INSERT INTO `tblposconfigurations` (`id`, `customerid`, `itemprice`, `showitems`, `showtax`, `fullscreen`, `admincashacctid`, `closingtime`, `branchid`, `created_at`, `updated_at`) VALUES
(2, 1, 1, 1, 1, 0, 11, '02:00:00', 1, '2024-12-20 07:35:20', '2025-03-20 05:50:41');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblposconfigurations`
--
ALTER TABLE `tblposconfigurations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tblposconfigurations_customerid_index` (`customerid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblposconfigurations`
--
ALTER TABLE `tblposconfigurations`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblposconfigurations`
--
ALTER TABLE `tblposconfigurations`
  ADD CONSTRAINT `tblposconfigurations_customerid_foreign` FOREIGN KEY (`customerid`) REFERENCES `tblcustomers` (`id`);
COMMIT;


######################################## 19-04-2025

ALTER TABLE `tblvehicleservices` ADD `isdeleted` TINYINT(4) NULL DEFAULT '0' AFTER `netamount`;

ALTER TABLE `tblvehicleservicesdet` ADD `isdeleted` TINYINT(4) NULL DEFAULT '0' AFTER `amount`;

ALTER TABLE `tblvehicleservicesitems` ADD `isdeleted` TINYINT(4) NULL DEFAULT '0' AFTER `amount`;

######################################## 23-05-2025

ALTER TABLE `tblvehicleservices` ADD `storeissue` TINYINT(4) NULL DEFAULT '0' AFTER `bankreceived`;

######################################## 24-05-2025

ALTER TABLE `tbluser_rights` ADD `allow_discount` TINYINT(4) NULL DEFAULT '0' AFTER `mobileshortcutbar`;

ALTER TABLE `tbluser_rights` ADD `prevdate_rights` TINYINT(4) NULL DEFAULT '0' AFTER `userwise_entries`, ADD `futuredate_rights` TINYINT(4) NULL DEFAULT '0' AFTER `prevdate_rights`;


INSERT INTO `tbluser_rights` (`id`, `userid`, `roleid`, `branch_rights`, `year_rights`, `salescat_rights`, `salesman_rights`, `salesman_disabled`, `itembrand_rights`, `itemcat_rights`, `location_rights`, `location_disabled`, `custgroup_rights`, `vendgroup_rights`, `accounts_rights`, `accounts_disabled`, `groupaccounts_rights`, `dashboard_rights`, `cashcounter_rights`, `otherdate_rights`, `purchaseprice_rights`, `salesprice_rights`, `userwise_entries`, `prevdate_rights`, `futuredate_rights`, `shortcutbar_rights`, `mobileshortcutbar`, `allow_discount`, `icontype`, `created_at`, `updated_at`) VALUES (NULL, '2', '1', '[{\"value\":1,\"label\":\"Main Branch\",\"select\":\"selected\"}]', '[{\"value\":1,\"label\":\"2024 - 2025\",\"status\":1,\"select\":\"selected\"}]', '[{\"value\":1,\"label\":\"Cash\"},{\"value\":2,\"label\":\"Credit\"}]', 'null', '1', '[{\"value\":1,\"label\":\"Local\"}]', '[{\"value\":1,\"label\":\"Category A\"},{\"value\":3,\"label\":\"Category B\"},{\"value\":4,\"label\":[...]

######################################## 05-06-2025

ALTER TABLE `tblvehicleservices` ADD `vehicledatano` VARCHAR(50) NULL DEFAULT NULL AFTER `customerid`;

ALTER TABLE `tblvehicleservices` ADD `suggested_days` INT(11) NULL DEFAULT '0' AFTER `suggested_limit`;

ALTER TABLE `tblvehicleservices` ADD `suggested_date` DATE NULL DEFAULT NULL AFTER `suggested_days`;

######################################## 25-06-2025

/// Currency Working

ALTER TABLE `tblgltransvoucher` CHANGE `currency_id` `currencyid` TINYINT(4) NULL DEFAULT '0', CHANGE `exc_rate` `exchangerate` DECIMAL(32,10) NULL DEFAULT '0.0000000000', CHANGE `amountincurr` `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0.0000000000';

ALTER TABLE `tblgltransaction` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `status`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblitemtransdet` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `reference`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangecost` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblpurchase` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `expense`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblpurchasedetail` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `netamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblpurchasereturn` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `taxamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblpurchasereturn_detail` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `netamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblinvoice` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `taxamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblinvoicedetail` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `netamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblinvoicereturn` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `taxamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblinvoicereturn_detail` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `netamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblposinvoice` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `taxamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblposinvoicedet` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `netamount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblsupplierpayments` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `reference`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblcustomerpayments` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `reference`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblservicesinvoice` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `cashreceived`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblservicesinvoicedetail` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `amount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblvehicleservices` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `cashreceived`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblvehicleservicesdet` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `amount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblvehicleservicesitems` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `amount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblvehicledata` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `cashreceived`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblvehicledatadet` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `amount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

ALTER TABLE `tblvehicleitemsdata` ADD `currencyid` TINYINT(4) NULL DEFAULT '0' AFTER `amount`, ADD `exchangerate` DECIMAL(32,10) NULL DEFAULT '0' AFTER `currencyid`, ADD `exchangeamount` DECIMAL(32,10) NULL DEFAULT '0' AFTER `exchangerate`;

DROP TABLE IF EXISTS `tblbarcodetypes`;
CREATE TABLE `tblbarcodetypes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `inactive` tinyint(4) DEFAULT 0,
  `isdefault` tinyint(4) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblbarcodetypes`
--

INSERT INTO `tblbarcodetypes` (`id`, `name`, `inactive`, `isdefault`, `created_at`, `updated_at`) VALUES
(1, 'Manual', 0, 0, '2025-05-02 11:21:42', '2025-05-02 11:21:51'),
(2, 'Same as Item Code', 0, 0, '2025-05-02 11:21:52', '2025-05-02 11:21:54'),
(3, 'Auto Code', 0, 0, '2025-05-02 11:21:55', '2025-05-02 11:21:57');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblbarcodetypes`
--
ALTER TABLE `tblbarcodetypes`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblbarcodetypes`
--
ALTER TABLE `tblbarcodetypes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;


ALTER TABLE `tblitems` ADD `barcodetype` TINYINT(4) NULL DEFAULT '0' AFTER `itemcode`;

######################################## 04-07-2025

ALTER TABLE `tblposconfigurations` ADD `showdiscount` TINYINT(4) NULL DEFAULT '0' AFTER `closingtime`, ADD `showbank` TINYINT(4) NULL DEFAULT '0' AFTER `showdiscount`, ADD `allowqtychange` TINYINT(4) NULL DEFAULT '0' AFTER `showbank`;

ALTER TABLE `tblposconfigurations` ADD `autocashbank` VARCHAR(25) NULL DEFAULT NULL AFTER `allowqtychange`;

######################################## 22-07-2025

ALTER TABLE `tblposconfigurations` ADD `allowamountchange` TINYINT(4) NULL DEFAULT '0' AFTER `autocashbank`;

########################################### 02-08-2025

ALTER TABLE `tblpurchase` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `returnstatus`;

ALTER TABLE `tblpurchasedetail` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblpurchasereturn` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `returnstatus`;

ALTER TABLE `tblpurchasereturn_detail` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblinvoice` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `returnstatus`;

ALTER TABLE `tblinvoicedetail` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblinvoicereturn` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblinvoicereturn_detail` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblposinvoice` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblposinvoicedet` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblposinvoicereturn` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblposinvoicereturn_detail` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblservicesinvoice` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblservicesinvoicedetail` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblcustomerpayments` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;

ALTER TABLE `tblsupplierpayments` ADD `isdeleted` TINYINT(1) NULL DEFAULT '0' AFTER `exchangeamount`;


########################################### 18-08-2025

/// Update Currency

UPDATE `tblpurchase` SET currencyid=1 and exchangerate=1 and exchangeamount=purchasetotal;

UPDATE `tblpurchasedetail` SET currencyid=1 and exchangerate=1 and exchangeamount=netamount;

UPDATE `tblpurchasereturn` SET currencyid=1 and exchangerate=1 and exchangeamount=purchasetotal;

UPDATE `tblpurchasereturn_detail` SET currencyid=1 and exchangerate=1 and exchangeamount=netamount;

UPDATE `tblposinvoice` SET currencyid=1 and exchangerate=1 and exchangeamount=invoicetotal;

UPDATE `tblposinvoicedet` SET currencyid=1 and exchangerate=1 and exchangeamount=netamount;

UPDATE `tblinvoice` SET currencyid=1 and exchangerate=1 and exchangeamount=invoicetotal;

UPDATE `tblinvoicedetail` SET currencyid=1 and exchangerate=1 and exchangeamount=netamount;

UPDATE `tblinvoicereturn` SET currencyid=1 and exchangerate=1 and exchangeamount=invoicetotal;

UPDATE `tblinvoicereturn_detail` SET currencyid=1 and exchangerate=1 and exchangeamount=netamount;

UPDATE `tblcustomerpayments` SET currencyid=1 and exchangerate=1 and exchangeamount=amount;

UPDATE `tblsupplierpayments` SET currencyid=1 and exchangerate=1 and exchangeamount=amount;

UPDATE `tblgltransvoucher` SET currencyid=1 and exchangerate=1 and exchangeamount=total_debit;

UPDATE tblgltransaction
SET 
    currencyid = 1,
    exchangerate = 1,
    exchangeamount = CASE 
        WHEN debit > 0 THEN debit
        WHEN credit > 0 THEN credit
        ELSE 0
    END;

UPDATE `tblitemtransdet` SET currencyid=1 and exchangerate=1 and exchangecost=ItemPriceEach;

UPDATE `tblservicesinvoice` SET currencyid=1 and exchangerate=1 and exchangeamount=netamount;

UPDATE `tblservicesinvoicedetail` SET currencyid=1 and exchangerate=1 and exchangeamount=amount;

UPDATE `tblvehicleservices` SET currencyid=1 and exchangerate=1 and exchangeamount=netamount;

UPDATE `tblvehicleservicesdet` SET currencyid=1 and exchangerate=1 and exchangeamount=amount;

UPDATE `tblvehicleservicesitems` SET currencyid=1 and exchangerate=1 and exchangeamount=amount;

---------------------- Stock Calculation View table

CREATE OR REPLACE VIEW view_stock_calculation AS
            SELECT
                ROW_NUMBER() OVER () AS id,
                t.itemid,
                t.locationid,
                t.unitid,
                t.entrydate,
                t.branchid,
                t.stockqty AS stock
            FROM (
                SELECT
                    itd.itemID AS itemid,
                    itd.locationid,
                    itd.packingUnit AS unitid, 
                    CURDATE() AS entrydate,
                    it.branchid,
                    SUM(
                        CASE WHEN itd.CalType = '+' 
                             THEN itd.Qty
                             ELSE 0
                        END
                    ) -
                    SUM(
                        CASE WHEN itd.CalType = '-' 
                             THEN itd.Qty
                             ELSE 0
                        END
                    ) AS stockqty
                FROM tblitemtransdet itd
                INNER JOIN tblitemtrans it 
                    ON it.voucherno = itd.voucherno 
                    AND it.branchid = itd.branchid
                WHERE it.posted = 1
                GROUP BY itd.itemID, itd.packingUnit, itd.locationid, it.branchid
            ) AS t;
        

########################################### 21-08-2025

ALTER TABLE `tblpurchase` ADD `totalqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalqty`;

ALTER TABLE `tblpurchasereturn` ADD `totalqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalqty`;

ALTER TABLE `tblinvoice` ADD `totalqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalqty`;

ALTER TABLE `tblinvoicereturn` ADD `totalqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalqty`;

ALTER TABLE `tblposinvoice` ADD `totalqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalqty`;

ALTER TABLE `tblposinvoicereturn` ADD `totalqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalqty`;

ALTER TABLE `tblservicesinvoice` ADD `totalqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalservices` INT(11) NULL DEFAULT '0' AFTER `totalqty`;

ALTER TABLE `tblvehicledata` ADD `totalitemsqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalservicesqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `totalitemsqty`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalservicesqty`, ADD `totalservices` INT(11) NULL DEFAULT '0' AFTER `totalitems`;

ALTER TABLE `tblvehicleservices` ADD `totalitemsqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `exchangeamount`, ADD `totalservicesqty` DECIMAL(18,6) NULL DEFAULT '0' AFTER `totalitemsqty`, ADD `totalitems` INT(11) NULL DEFAULT '0' AFTER `totalservicesqty`, ADD `totalservices` INT(11) NULL DEFAULT '0' AFTER `totalitems`;

########################################### 04-09-2025

ALTER TABLE `tblitems` ADD `branchid` TINYINT(4) NULL DEFAULT '0' AFTER `isservice`;

INSERT INTO `tblids` (`id`, `description`, `code`, `value`, `comments`, `branchid`, `codetype`, `created_at`, `updated_at`) VALUES (NULL, 'itemCode', 'COD', '1', 'manual', '2', '1', '2024-11-14 10:18:52', '2024-11-14 10:18:52');


######################################## 02-10-2025

ALTER TABLE `tblcompanyinfo` ADD `pagetitle` VARCHAR(100) NULL DEFAULT NULL AFTER `reportlogopath`;

######################################## 28-10-2025

INSERT INTO `menus` (`id`, `moduleid`, `parentid`, `sequence`, `label`, `icon`, `url`, `component`, `shortcuticon`, `color`, `abbreviation`, `menutype`, `modal`, `inactive`, `details`, `created_at`, `updated_at`) VALUES
(473, 10, 0, 3, 'POS Invoice Return', 'fas fa-reply', NULL, NULL, 'fas fa-reply', 'text-success', 'PIR', 1, NULL, 0, NULL, '2025-07-15 08:45:33', '2025-07-16 18:04:40'),
(474, 10, 473, 1, 'POS Invoice Return', 'mdi mdi-18px mdi-plus-thick', 'sales/addposreturn', 'AddPosReturn', 'mdi mdi-18px mdi-plus-thick', 'text-success', 'PIR', 1, NULL, 0, NULL, '2025-07-15 08:47:58', '2025-07-16 18:04:40'),
(475, 10, 473, 2, 'POS Invoice Return List', 'dripicons-list', 'sales/posreturnlist', 'PosReturnList', 'dripicons-list', 'text-success', 'PIRL', 2, NULL, 0, NULL, '2025-07-15 11:04:10', '2025-07-16 18:04:40');
COMMIT;

INSERT INTO `tblids` (`id`, `description`, `code`, `value`, `comments`, `branchid`, `codetype`, `created_at`, `updated_at`) VALUES (NULL, 'POS Invoice Return', 'POSR', '1', 'POS Invoice Return', '1', '0', NULL, NULL);

INSERT INTO `tblsublinks` (`id`, `url`, `component`, `created_at`, `updated_at`) VALUES (NULL, 'sales/editposreturn', 'EditPosReturn', '2025-07-15 16:06:57', '2025-07-15 16:06:57');

ALTER TABLE `tblposconfigurations` ADD `showpurchaseprice` TINYINT(4) NULL DEFAULT '0' AFTER `allowamountchange`;

INSERT INTO `menus` (`id`, `moduleid`, `parentid`, `sequence`, `label`, `icon`, `url`, `component`, `shortcuticon`, `color`, `abbreviation`, `menutype`, `modal`, `inactive`, `details`, `created_at`, `updated_at`) VALUES (NULL, '74', '75', '9', 'Manage PDF Formats', 'dripicons-scale', 'admin/pdfformats', 'ManagePdfFormats', 'dripicons-scale', NULL, 'MPF', '1', NULL, '0', NULL, '2024-01-28 20:29:57', '2025-10-25 12:05:29');


--
-- Table structure for table `tblpdf_formats`
--

CREATE TABLE `tblpdf_formats` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `component` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `listname` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `button` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branchid` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblpdf_formats`
--

INSERT INTO `tblpdf_formats` (`id`, `description`, `image`, `component`, `listname`, `button`, `branchid`, `created_at`, `updated_at`) VALUES
(1, 'English PDF (Single Logo)', '/attachments/pdf_formats/englishsales.jpg', 'salesList_PDF', 'sales', NULL, 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(2, 'Urdu PDF', '/attachments/pdf_formats/urdusales.jpg', 'SalesInvoice_Arabic_PDF', 'sales', NULL, 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(3, 'Small PDF (Single Logo)', '/attachments/pdf_formats/smallsales.jpg', 'Invoice-smPDF', 'sales', NULL, 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(4, 'English PDF', '/attachments/pdf_formats/smallpos.jpg', 'Invoice-smPDF', 'pos', 'smallpdf', 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(5, 'English PDF (Double Logo)', '/attachments/pdf_formats/doublesales.jpg', 'salesList2nd_PDF', 'sales', 'largepdf', 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(6, 'Small PDF (Double Logo)', '/attachments/pdf_formats/doublesmallsales.jpg', 'Invoice2nd-smPDF', 'sales', NULL, 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(7, 'Barcode PDF (143.6px X 90.7px)', '/attachments/pdf_formats/barcode.jpg', 'SingleBarcodeGenPDF', 'barcode', NULL, 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(8, 'Multiple Barcode PDF (143.6px X 90.7px)', '/attachments/pdf_formats/barcode.jpg', 'barcodeGenPDF', 'barcode', NULL, 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(9, 'Barcode PDF (182.4px X 90.7px)', '/attachments/pdf_formats/barcode.jpg', 'SingleBarcodeGenPDF2', 'barcode', 'singlepdf', 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57'),
(10, 'Multiple Barcode PDF (182.4px X 90.7px)', '/attachments/pdf_formats/barcode.jpg', 'barcodeGenPDF2', 'barcode', 'multiplepdf', 1, '2025-03-15 01:57:07', '2025-10-28 08:21:57');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblpdf_formats`
--
ALTER TABLE `tblpdf_formats`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblpdf_formats`
--
ALTER TABLE `tblpdf_formats`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
COMMIT;


######################################## 07-11-2025

ALTER TABLE `tblposinvoice` ADD `discountpercent` DECIMAL(32,10) NULL DEFAULT '0' AFTER `subtotal`;

INSERT INTO `tblpdf_formats` (`id`, `description`, `image`, `component`, `listname`, `button`, `branchid`, `created_at`, `updated_at`) VALUES (NULL, 'English PDF (With Notes)', '/attachments/pdf_formats/smallpos.jpg', 'Pos-smPDF', 'pos', 'smallpdf', '1', '2025-03-15 06:57:07', '2025-10-28 13:21:57');

ALTER TABLE `tblmoduleconfig` CHANGE `value` `value` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

######################################## 10-11-2025

ALTER TABLE `tblposinvoice` ADD `salesmanid` INT(11) NULL DEFAULT '0' AFTER `hold`;

ALTER TABLE `tblposconfigurations` ADD `showsalesperson` TINYINT(4) NULL DEFAULT '0' AFTER `showpurchaseprice`;


######################################## 17-11-2025

ALTER TABLE `tblbranch` ADD `otherlanguage` VARCHAR(50) NULL DEFAULT NULL AFTER `cashcustomerid`;

######################################## 05-01-2026

INSERT INTO `tblmoduleconfig` (`id`, `moduleid`, `name`, `value`, `created_at`, `updated_at`) VALUES (NULL, '10', 'salesbalance', '0', '2026-01-05 16:56:39', '2026-01-05 16:56:39'), (NULL, '52', 'purchasebalance', '0', '2026-01-05 16:56:39', '2026-01-05 16:56:39');

######################################## 06-01-2026

ALTER TABLE `tblpdf_formats` ADD `isdefault` TINYINT(4) NULL DEFAULT '0' AFTER `button`;

######################################## 07-01-2026

ALTER TABLE `tblposconfigurations` ADD `showitemdiscount` TINYINT(4) NULL DEFAULT '0' AFTER `showsalesperson`;

ALTER TABLE `tblposinvoicedet` ADD `discountby` VARCHAR(25) NULL DEFAULT NULL AFTER `exchangeamount`;

ALTER TABLE `tblposinvoicedet` ADD `discountedprice` DECIMAL(32,10) NULL DEFAULT '0' AFTER `discountby`;

ALTER TABLE `tblposconfigurations` ADD `discountby` VARCHAR(25) NULL DEFAULT NULL AFTER `showitemdiscount`;

######################################## 27-02-2026

--
-- Table structure for table `tblcolors`
--

CREATE TABLE `tblcolors` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `tblsizes`
--

CREATE TABLE `tblsizes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblcolors`
--
ALTER TABLE `tblcolors`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tblsizes`
--
ALTER TABLE `tblsizes`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblcolors`
--
ALTER TABLE `tblcolors`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tblsizes`
--
ALTER TABLE `tblsizes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;


ALTER TABLE `tblitems` ADD `sizewise` TINYINT(4) NULL DEFAULT '0' AFTER `isservice`, ADD `colorwise` TINYINT(4) NULL DEFAULT '0' AFTER `sizewise`;

INSERT INTO `tblmoduleconfig` (`id`, `moduleid`, `name`, `value`, `created_at`, `updated_at`) VALUES (NULL, '237', 'showcolor', '0', NULL, NULL), (NULL, '237', 'showsize', '0', NULL, NULL);

INSERT INTO `tblmoduleconfig` (`id`, `moduleid`, `name`, `value`, `created_at`, `updated_at`) VALUES (NULL, '237', 'colorwisestock', '0', NULL, NULL);

INSERT INTO `tblmoduleconfig` (`id`, `moduleid`, `name`, `value`, `created_at`, `updated_at`) VALUES (NULL, '237', 'sizewisestock', '0', NULL, NULL);

ALTER TABLE `tblpurchasedetail` ADD `sizeid` INT(11) NULL DEFAULT '0' AFTER `colorid`;

ALTER TABLE `tblitemtransdet` ADD `sizeid` INT(11) NULL DEFAULT '0' AFTER `colorid`;

ALTER TABLE `tblpurchasereturn_detail` ADD `colorid` INT(11) NULL DEFAULT '0' AFTER `locationid`, ADD `sizeid` INT(11) NULL DEFAULT '0' AFTER `colorid`;

ALTER TABLE `tblinvoicedetail` ADD `colorid` INT(11) NULL DEFAULT '0' AFTER `locationid`, ADD `sizeid` INT(11) NULL DEFAULT '0' AFTER `colorid`;

ALTER TABLE `tblinvoicereturn_detail`  ADD `colorid` INT(11) NULL DEFAULT '0'  AFTER `locationid`,  ADD `sizeid` INT(11) NULL DEFAULT '0'  AFTER `colorid`;

ALTER TABLE `tblposinvoicedet` ADD `sizeid` INT(11) NULL DEFAULT '0'  AFTER `colorid`;

ALTER TABLE `tblposinvoicereturn_detail` ADD `sizeid` INT(11) NULL DEFAULT '0'  AFTER `colorid`;

INSERT INTO `menus` (`id`, `moduleid`, `parentid`, `sequence`, `label`, `icon`, `url`, `component`, `shortcuticon`, `color`, `abbreviation`, `menutype`, `modal`, `inactive`, `details`, `created_at`, `updated_at`) VALUES (NULL, '32', '43', '7', 'Quality Wise Stock Report', 'fe-file-text', 'inventory-reports/qualitywisestock', 'QualityWiseStock', 'fe-file-text', 'text-success', 'QWS', '7', NULL, '0', NULL, '2026-01-27 10:39:44', '2026-02-17 11:44:50');

INSERT INTO `tblids` (`id`, `description`, `code`, `value`, `comments`, `branchid`, `codetype`, `created_at`, `updated_at`) VALUES (NULL, 'Item Opening Stock', 'IOS', '1', 'Item Opening Stock', '1', '0', NULL, NULL);

-- Above updated at DICS


######################################## 11-03-2026

ALTER TABLE `tbldayclose` ADD `time` TIME NULL DEFAULT NULL AFTER `close`, ADD `userid` INT(11) NULL DEFAULT '0' AFTER `time`;

ALTER TABLE `tbldayclose` ADD `type` VARCHAR(25) NULL DEFAULT NULL AFTER `userid`;

ALTER TABLE `tblwhatsappconfig` ADD `phone` VARCHAR(50) NULL DEFAULT NULL AFTER `active`;



######################################## 14-03-2026

ALTER TABLE `tblvehicleservices` ADD `color` VARCHAR(100) NULL DEFAULT NULL AFTER `isdeleted`;

######################################## 16-03-2026

ALTER TABLE `tblvehicleservices` ADD `image` TEXT NULL DEFAULT NULL AFTER `color`, ADD `path` TEXT NULL DEFAULT NULL AFTER `image`;

ALTER TABLE `tblvehicledata` ADD `color` VARCHAR(100) NULL DEFAULT NULL AFTER `alert`;

ALTER TABLE `tblvehicledata` ADD `image` TEXT NULL DEFAULT NULL AFTER `color`, ADD `path` TEXT NULL DEFAULT NULL AFTER `image`;

-- Above updated at DICS

######################################## 28-03-2026

INSERT INTO `tblsublinks` (`id`, `url`, `component`, `created_at`, `updated_at`) VALUES (NULL, 'stocks/editST_branchwise', 'EditStockTransferBranchWise', NULL, NULL);

INSERT INTO `tblids` (`id`, `description`, `code`, `value`, `comments`, `branchid`, `codetype`, `created_at`, `updated_at`) VALUES (NULL, 'Branch Wise Stock Transfer', 'BST', '1', 'Branch Wise Stock Transfer', '1', '0', NULL, NULL);

ALTER TABLE `tblitemtransdet` 
ADD `qtyin` DECIMAL(18,6) NULL DEFAULT '0.000000' , 
ADD `qtyout` DECIMAL(18,6) NULL DEFAULT '0.000000' , 
ADD `amount` DECIMAL(32,10) NULL DEFAULT '0.0000000000' ;

INSERT INTO tblmoduleconfig (id, moduleid, name, value, created_at, updated_at) VALUES (NULL, '10', 'branchwisecustomers', '1', '2026-03-28 11:05:22', '2026-03-28 11:05:22'), (NULL, '10', 'branchwisebalance', '1', '2026-03-28 11:05:22', '2026-03-28 11:05:22');

INSERT INTO `menus` (`id`, `moduleid`, `parentid`, `sequence`, `label`, `icon`, `url`, `component`, `shortcuticon`, `color`, `abbreviation`, `menutype`, `modal`, `inactive`, `details`, `created_at`, `updated_at`) VALUES (NULL, '32', '408', '3', 'Add Stock Transfer Branch Wise', 'mdi mdi-18px mdi-plus-thick', 'stocks/addST_branchwise', 'AddStockTransferBranchWise', 'mdi mdi-18px mdi-plus-thick', 'text-primary', 'STB', '1', NULL, '0', NULL, '2026-03-28 10:24:08', '2026-03-28 10:26:34');

INSERT INTO `menus` (`id`, `moduleid`, `parentid`, `sequence`, `label`, `icon`, `url`, `component`, `shortcuticon`, `color`, `abbreviation`, `menutype`, `modal`, `inactive`, `details`, `created_at`, `updated_at`) VALUES (NULL, '32', '408', '4', 'Stock Transfer Branch Wise List', 'dripicons-list', 'stocks/stlist_branchwise', 'StockTransferBranchWiseList', 'dripicons-list', 'text-primary', 'STBWL', '6', NULL, '0', NULL, '2026-03-28 10:25:43', '2026-03-28 10:26:34');

######################################## 30-03-2026

INSERT INTO `menus` (`id`, `moduleid`, `parentid`, `sequence`, `label`, `icon`, `url`, `component`, `shortcuticon`, `color`, `abbreviation`, `menutype`, `modal`, `inactive`, `details`, `created_at`, `updated_at`) VALUES (NULL, '10', '21', '6', 'Branch Wise Customer Ledger', 'fe-file-text', 'customers/branchwise_ledger', 'BranchWiseCustomerLedger', 'fe-file-text', 'text-primary', 'CLBW', '6', NULL, '0', NULL, '2026-03-30 12:56:00', '2026-03-30 12:56:20');


-- above updated in BOTG
-- Above updated at HTGP

######################################## 10-04-2026

ALTER TABLE `tblpdf_formats` ADD `active` TINYINT(4) NULL DEFAULT '0' AFTER `isdefault`;

ALTER TABLE `tblpdf_formats` ADD `sequence` TINYINT(4) NULL DEFAULT '0' AFTER `active`;

INSERT INTO `tblpdf_formats` (`id`, `description`, `image`, `component`, `listname`, `button`, `isdefault`, `active`, `sequence`, `branchid`, `created_at`, `updated_at`) VALUES 
(NULL, 'Sales Return Small PDF', '/attachments/pdf_formats/smallpdf.jpg', 'saleReturn-smPDF', 'salesreturn', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Sales Return PDF', '/attachments/pdf_formats/smallpdf.jpg', 'salesReturn_PDF', 'salesreturn', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Sales Return Summary PDF', '/attachments/pdf_formats/smallpdf.jpg', 'summary-PDF_Return', 'salesreturn', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Purchase List PDF', '/attachments/pdf_formats/smallpdf.jpg', 'PurchaseList_PDF', 'purchase', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Purchase Invoice Small PDF', '/attachments/pdf_formats/smallpdf.jpg', 'Invoice-smPDF', 'purchase', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Purchase Invoice Summary PDF', '/attachments/pdf_formats/smallpdf.jpg', 'SaleSummary-PDF', 'purchase', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Barcode PDF', '/attachments/pdf_formats/smallpdf.jpg', 'barcodeGenPDF', 'purchase', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Purchase Return PDF', '/attachments/pdf_formats/smallpdf.jpg', 'PurchaseReturn_PDF', 'purchasereturn', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Purchase Return Summary PDF', '/attachments/pdf_formats/smallpdf.jpg', 'PurcahseSummaryReturn_PDf', 'purchasereturn', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Purchase Return Small PDF', '/attachments/pdf_formats/smallpdf.jpg', 'PurchaseReturn-smPDF', 'purchasereturn', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Customer Ledger PDF', '/attachments/pdf_formats/smallpdf.jpg', 'Customer_pdf', 'customerledger', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Supplier Ledger PDF', '/attachments/pdf_formats/smallpdf.jpg', 'supplier_pdf', 'supplierledger', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Item Ledger PDF', '/attachments/pdf_formats/smallpdf.jpg', 'Item_pdf', 'itemledger', NULL, '0', '0', '0', '1', NULL, NULL),
(NULL, 'Account Ledger PDF', '/attachments/pdf_formats/smallpdf.jpg', 'AccountList_Pdf', 'accountledger', NULL, '0', '0', '0', '1', NULL, NULL);