-- 02-JAN-2020 Author : Avanish --
ALTER TABLE `cycle_count` ADD `unit_price_wo_gst` FLOAT(9,2) NOT NULL DEFAULT '0.00' AFTER `unit_price`;
UPDATE `cycle_count` cc INNER JOIN product_stock ps ON ps.product_id = cc.product_id AND ps.batch_no <=> cc.batch_no SET cc.`unit_price_wo_gst` = ROUND((cc.unit_price /(1 +(ps.gst_percentage / 100))),2);

-- 03-JAN-2020 Author : Avanish --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_BARCODE_STICKER', '2', 'if value equals to below: 2 - 38mm*16mm sticker with 2 sticker per page and no gap between sticker. 4 - 25mm*14mm sticker with 4 sticker per page and no gap between sticker.');

-- 06-JAN-2020 Author : Avanish --
ALTER TABLE `job_title_master` CHANGE `job_notice_period` `job_notice_period` INT(11) NOT NULL DEFAULT '30' COMMENT 'notice period in day';
UPDATE `job_title_master` SET `job_notice_period`=30 WHERE job_notice_period = 1
UPDATE `job_title_master` SET `job_notice_period`=60 WHERE job_notice_period = 2

-- 07 jan 2020 Author : zalak --
update medicine_transaction set product_id = item_cd where trn_type = 'IP';
ALTER TABLE `product_master` ADD `is_narcotic` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `is_barcode_applicable`;

-- 10 jan 2020 Author : jayraj --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Pharmacy Earning Summary', '', 'far fa-circle', 'Item/medicalStoreCollectionReportInput', NULL, 'Y', 'thims', '2019-12-16 18:18:00', '', '0000-00-00 00:00:00');

-- 15 jan 2020 Author : zalak --
ALTER TABLE `conversionfactor_update_request` ADD `last_purchase_voucher` INT(11) NULL DEFAULT NULL AFTER `revised_conversion_factor`;

-- 18-JAN-2020 Author : Avanish --
ALTER TABLE `product_master` ADD `barcode_sticker_pack_size` INT NOT NULL DEFAULT '1' AFTER `is_barcode_applicable`;
UPDATE `product_master` SET `barcode_sticker_pack_size` = `conversion_factor`;

-- 23-JAN-2020 Author : Avanish Composer Command Just for backup purpose --
-- composer require picqer/php-barcode-generator

-- 25 jan 2020 Author : zalak --
ALTER TABLE `product_stock` ADD `latest_purchase_price_wo_gst` FLOAT(11,2) NOT NULL DEFAULT '0' AFTER `batch_exp`;

ALTER TABLE `product_stock` CHANGE `latest_purchase_price_per_unit` `latest_purchase_price_per_unit_wo_gst` FLOAT(9,2) NOT NULL DEFAULT '0.00';

-- Change latest purchase price per unit to without gst.
update product_stock set latest_purchase_price_per_unit_wo_gst = round(latest_purchase_price_per_unit_wo_gst / (1 + ((gst_percentage)/100)),2) where latest_purchase_price_per_unit_wo_gst != 0;

-- 28-JAN-2020 Author : Avanish --
ALTER TABLE `product_transaction_batch` ADD `barcode_source` ENUM('P','IR') NOT NULL DEFAULT 'P' AFTER `is_barcode_printed`;

-- 29-JAN-2020 Author : Avanish --
-- ALTER TABLE `cycle_count` ADD `product_tracking_sr_no` TEXT NULL DEFAULT NULL AFTER `batch_no`;
-- ALTER TABLE `cycle_count` ADD `physical_stock_barcode` INT NULL DEFAULT NULL AFTER `system_stock`;

--30 jan-2020 :zalak --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Pharmacy Stock Inquiry', '', 'far fa-
circle', 'Item/StockInquiry', NULL, 'Y', 'thims', '2020-01-30 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_SALES_DISCOUNT_EDITABLE',
'Y', 'Pharmacy Sales Time discount editable or not.');
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_BARCODE_GENERATE_THROUGH_CYCLE_COUNT', 'N', 'if Y then on submit of cycle count barcode will be generated for current stock.');

-- 31-JAN-2020 Author : Avanish --
ALTER TABLE `product_transaction_batch` ADD `physical_quantity` FLOAT NOT NULL DEFAULT '0' AFTER `quantity_suom_sold`;
ALTER TABLE `product_transaction_batch` CHANGE `product_tracking_sr_no` `product_tracking_sr_no` VARCHAR(25) NOT NULL;
ALTER TABLE `product_transaction` CHANGE `product_tracking_sr_no` `product_tracking_sr_no` VARCHAR(25) NULL DEFAULT NULL;
ALTER TABLE `product_transaction_amendment` ADD `product_tracking_sr_no` VARCHAR(25) NULL DEFAULT NULL AFTER `batch_no`;

-- 07-FEB-2020 Author : Avanish --
CREATE TABLE `sales_return_barcode_reprint` (
  `trn_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return ',
  `trn_id` int(11) NOT NULL,
  `product_tracking_sr_no` varchar(25) NOT NULL,
  `barcode_source` enum('IR','CC') NOT NULL DEFAULT 'IR',
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 10-FEB-2020 Author : Avanish --
ALTER TABLE `sales_return_barcode_reprint` ADD `barcode_quantity` INT NOT NULL DEFAULT '0' AFTER `product_tracking_sr_no`;

-- 11-FEB-2020 Author : Avanish --
ALTER TABLE `product_transaction_batch` CHANGE `barcode_source` `barcode_source` ENUM('P','IR','CC') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'P';
ALTER TABLE `product_master` CHANGE `is_barcode_applicable` `is_barcode_applicable` ENUM('Y','N') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N' COMMENT 'Y - for individual Product Tracking, N - for Product Tracking';

-- 18-FEB-2020 Author : Avanish --
CREATE TABLE `leave_configuration` (
  `leave_configuration_id` int(11) NOT NULL,
  `leave_type_id` tinyint(2) NOT NULL,
  `leave_entitlement` int(11) NOT NULL,
  `leave_effective_from` date NOT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `leave_configuration` ADD PRIMARY KEY (`leave_configuration_id`);
ALTER TABLE `leave_configuration` MODIFY `leave_configuration_id` int(11) NOT NULL AUTO_INCREMENT;

-- 25-FEB-2020 Author : Avanish --
ALTER TABLE `product_master` CHANGE `is_barcode_applicable` `is_barcode_applicable` ENUM('Y','N','NB') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N' COMMENT 'Y - for individual Product Tracking, N - for Product Tracking, NB - for No Barcode';

-- 02-MARCH-2020 Author : Zalak --
update product_stock set price_per_unit = ABS(price_per_unit),price_per_unit_wo_gst = ABS(price_per_unit_wo_gst),price = ABS(price),mrp = ABS(mrp),mrp_per_unit = ABS(mrp_per_unit);

-- 07-March-2020 Author : Avanish --
ALTER TABLE `business_entity_master` CHANGE `remarks` `remarks` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

-- 07-May-2020 Author : Avanish --
ALTER TABLE `product_transaction` ADD `return_quantity` FLOAT NULL DEFAULT '0' AFTER `quantity_per_unit_suom`;
ALTER TABLE `product_transaction_amendment` ADD `return_quantity` FLOAT NULL DEFAULT '0' AFTER `quantity_per_unit_suom`;

-- 29-May-2020 Author : Avanish --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_DISPLAY_MRP_DETAILS', 'Y', 'if value is Yes, display MRP in Material Issue and Material Issue Return. If value is set to No, do not display MRP details.');
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_DISPLAY_PTR_DETAILS', 'Y', 'if value is Yes, display PTR in Material Issue and Material Issue Return. If value is set to No, do not display PTR details.');

-- 05-May-2020 Author : Avanish (PO related changes)--
ALTER TABLE `purchase_order_header` ADD `igst_amt` FLOAT NULL DEFAULT '0' AFTER `cgst_amt`;
ALTER TABLE `purchase_order_header` ADD `payment_terms` INT NOT NULL DEFAULT '0' AFTER `total`;
ALTER TABLE `purchase_order_detail` ADD `additional_product_description` VARCHAR(100) NULL DEFAULT NULL AFTER `product_id`;
ALTER TABLE `purchase_order_detail` DROP `expacted_delivery_date`;
ALTER TABLE `purchase_order_header` DROP `remarks`;

CREATE TABLE `purchase_order_delivery_schedule` (
  `po_fyear` smallint(4) NOT NULL COMMENT 'Transaction Financial Year',
  `po_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return ',
  `po_id` int(11) NOT NULL,
  `po_sr_no` smallint(3) NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL COMMENT 'product_master - product_id',
  `po_quantity` float NOT NULL DEFAULT '0',
  `expected_delivery_date` date NOT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `purchase_order_delivery_schedule`
  ADD UNIQUE KEY `UK_TransactionNo` (`po_fyear`,`po_type`,`po_id`,`po_sr_no`,`expected_delivery_date`),
  ADD KEY `FK_product_master_id` (`product_id`);

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_PURCHASE_ORDER_STORES', '18', '');

-- 08-May-2020 Author : Avanish (MR related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_MATERIAL_REQUEST_STORES', '2,17', 'Stores from which internal material issue will be allowed');
INSERT INTO `last_transaction_number` (`trn_type`, `trn_fyear`, `trn_id`) VALUES ('MR', '2021', '0');

CREATE TABLE `material_request_detail` (
  `mr_fyear` smallint(4) NOT NULL,
  `mr_id` int(11) NOT NULL,
  `mr_sr_no` smallint(3) NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `stock_uom` tinyint(3) UNSIGNED NOT NULL,
  `requested_quantity` int(11) NOT NULL DEFAULT '0',
  `approved_quantity` int(11) NOT NULL DEFAULT '0',
  `issued_quantity` int(11) NOT NULL DEFAULT '0',
  `created_by` varchar(10) CHARACTER SET latin1 NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `material_request_header` (
  `mr_fyear` smallint(4) NOT NULL,
  `store_code` varchar(6) CHARACTER SET latin1 NOT NULL,
  `mr_id` int(11) NOT NULL,
  `mr_date` date NOT NULL,
  `mr_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'P - Partially Saved, U - Un approved, T - Cancelled, A - Approved',
  `department_id` tinyint(5) UNSIGNED NOT NULL,
  `remarks` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `approved_by` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `approved_date` datetime DEFAULT NULL,
  `created_by` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `material_request_detail`
  ADD UNIQUE KEY `mr_fyear` (`mr_fyear`,`mr_id`,`mr_sr_no`),
  ADD KEY `FK_mr_detail_productid` (`product_id`),
  ADD KEY `FK_mr_detail_suom` (`stock_uom`),
  ADD KEY `FK_mr_detail_createdby` (`created_by`),
  ADD KEY `FK_mr_detail_lastmodifiedby` (`lastmodified_by`);

ALTER TABLE `material_request_header`
  ADD UNIQUE KEY `mr_fyear` (`mr_fyear`,`store_code`,`mr_id`),
  ADD KEY `FK_mr_header_deptid` (`department_id`);

ALTER TABLE `material_request_detail`
  ADD CONSTRAINT `FK_mr_detail_createdby` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `FK_mr_detail_lastmodifiedby` FOREIGN KEY (`lastmodified_by`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `FK_mr_detail_productid` FOREIGN KEY (`product_id`) REFERENCES `product_master` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `FK_mr_detail_suom` FOREIGN KEY (`stock_uom`) REFERENCES `unit_master` (`unit_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `material_request_header`
  ADD CONSTRAINT `FK_mr_header_deptid` FOREIGN KEY (`department_id`) REFERENCES `department_master` (`department_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Material Request', '', 'far fa-
circle', 'StoreMrDashboard/User', NULL, 'Y', 'thims', '2020-06-20 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Approve Material Request', '', 'far fa-
circle', 'StoreMrDashboard/SuperUser', NULL, 'Y', 'thims', '2020-06-20 00:00:36', '', '0000-00-00 00:00:00');

-- 24-JUN-2020 Author : Avanish (Payment Collection settle bill without receipt related changes)--
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_WRITEOFF_BILL_AMOUNT', '0', 'Here, We allow that much positive and negative amount to settle patients pharmacy bills.');

CREATE TABLE IF NOT EXISTS `pharmacy_bill_amt_writeoff_detail` (
  `bill_writeoff_id` int(7) NOT NULL AUTO_INCREMENT,
  `patient_id` int(7) NULL DEFAULT NULL COMMENT 'Medicine Issue & Issue Return Transaction has patient information if Pharmacy module is integrated',
  `ph_cust_id` INT(7) NULL DEFAULT NULL,
  `amount_received` float NOT NULL DEFAULT '0',
  `balance_type` enum('C','D') NOT NULL,
  `remarks` varchar(50) DEFAULT NULL,
  `created_by` varchar(15) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`bill_writeoff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Sales Write Off Report', '', 'far fa-
circle', 'Item/SalesWriteOffInput', NULL, 'Y', 'thims', '2020-06-25 00:00:36', '', '0000-00-00 00:00:00');

-- Release 3.01.11 Done --

-- 02-JULY-2020 Author : Avanish --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Material Issue Note With MR', '', 'far fa-
circle', 'StoreMrDashboard/ApprovedMaterialRequestList', NULL, 'Y', 'thims', '2020-07-02 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_INDENT_NOTIFICATION_UPTO_NO_OF_DAYS', '365', 'indent notification will be displayed based on this parameter value.');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'IP Medicine Issue Lead Time Report', '', 'far fa-
circle', 'Item/ipMedicineIssueLeadTimeReportInput', NULL, 'Y', 'thims', '2020-07-04 00:00:36', '', '0000-00-00 00:00:00');

-- 06-JULY-2020 Author : Avanish --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase Detail - Supp / Item Wise', '', 'far fa-
circle', 'Item/PurchaseDetailReportInput', NULL, 'Y', 'thims', '2020-07-06 00:00:36', '', '0000-00-00 00:00:00');

-- 15-JULY-2020 Author : Avanish (External Doctor related changes) --
ALTER TABLE `doctor_master` ADD `mapping_id` INT(10) NOT NULL COMMENT 'Mapping external_doctor_master.external_doctor_id' AFTER `queue_status`;

DROP TABLE IF EXISTS `external_doctor_master`;
CREATE TABLE IF NOT EXISTS `external_doctor_master` (
  `external_doctor_id` int(10) NOT NULL AUTO_INCREMENT,
  `doctor_name` varchar(25) NOT NULL,
  `doctor_contact_no` varchar(10) DEFAULT NULL,
  `is_active` enum('Y','N') NOT NULL DEFAULT 'Y',
  `mapping_id` int(10) NOT NULL COMMENT 'Mapping doctor_master.doctor_id',
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`external_doctor_id`) USING BTREE,
  KEY `FK_externaldoctormaster_createdBy` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `external_doctor_master`
  ADD CONSTRAINT `FK_externaldoctormaster_createdBy` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE TRIGGER `before_doctor_master_insert` BEFORE INSERT ON `doctor_master`
 FOR EACH ROW BEGIN
    SET
        @database_name =(
    SELECT
        DATABASE()) ;
    SET
        @external_doctor_id =(
        SELECT AUTO_INCREMENT
    FROM
        information_schema.tables
    WHERE TABLE_NAME
        = 'external_doctor_master' AND table_schema = @database_name
    ) ;
SET NEW
    .mapping_id = @external_doctor_id ;
END

CREATE TRIGGER `after_doctor_master_insert` AFTER INSERT ON `doctor_master`
 FOR EACH ROW BEGIN
INSERT INTO external_doctor_master
SET
    external_doctor_master.doctor_name = CONCAT(NEW.doctor_fname," ",NEW.doctor_mname," ",NEW.doctor_lname),
    external_doctor_master.doctor_contact_no = NEW.doctor_contact,
    external_doctor_master.is_active = 'Y',
    external_doctor_master.mapping_id = NEW.doctor_id,
    external_doctor_master.created_by = NEW.CreatedBy,
    external_doctor_master.created_date = NEW.CreatedDate;
END

CREATE TRIGGER `after_doctor_master_update` AFTER UPDATE ON `doctor_master`
 FOR EACH ROW BEGIN
	IF NEW.doctor_fname <> OLD.doctor_fname OR NEW.doctor_mname <> OLD.doctor_mname OR NEW.doctor_lname <> OLD.doctor_lname OR NEW.doctor_contact <> OLD.doctor_contact THEN
UPDATE
    external_doctor_master
SET
	external_doctor_master.doctor_name = CONCAT(NEW.doctor_fname," ",NEW.doctor_mname," ",NEW.doctor_lname),
    external_doctor_master.doctor_contact_no = NEW.doctor_contact
WHERE
    external_doctor_master.external_doctor_id = NEW.mapping_id ;
    END IF ;
END

ALTER TABLE `product_transaction_header` ADD `doctor_id` INT(10) NULL DEFAULT NULL COMMENT 'Referring external_doctor_master' AFTER `department_id`;
ALTER TABLE `product_transaction_header_amendment` ADD `doctor_id` INT(10) NULL DEFAULT NULL COMMENT 'Referring external_doctor_master' AFTER `department_id`;

-- 17-JULY-2020 Author : Avanish (Doctor Wise Sales Report related) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Doctor Wise Sales Report', '', 'far fa-
circle', 'Item/DoctorWiseSalesReportInput', NULL, 'Y', 'thims', '2020-07-17 00:00:36', '', '0000-00-00 00:00:00');

-- 21-JULY-2020 Author : Avanish (Selling Price Rule related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_SELLING_PRICE_BUSINESS_LOGIC_APPLICABLE', 'N', 'if yes then ph_sell_price_rule used to derive selling price');

ALTER TABLE `product_transaction` ADD `original_mrp` FLOAT(9,2) NULL DEFAULT '0.00' AFTER `sch_amt`;
ALTER TABLE `product_transaction_amendment` ADD `original_mrp` FLOAT(9,2) NULL DEFAULT '0.00' AFTER `sch_amt`;

DROP TABLE IF EXISTS `ph_sell_price_rule`;
CREATE TABLE IF NOT EXISTS `ph_sell_price_rule` (
  `rule_id` int(11) NOT NULL AUTO_INCREMENT,
  `start_range` float(9,2) DEFAULT '0.00',
  `end_range` float(9,2) DEFAULT '0.00',
  `margin_percentage` float(6,2) DEFAULT '0.00',
  `rule_description` text,
  PRIMARY KEY (`rule_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

UPDATE `product_transaction` SET original_mrp = mrp WHERE `trn_type` IN ('P','PR');
UPDATE `product_transaction_amendment` SET original_mrp = mrp WHERE `trn_type` IN ('P','PR');

-- Selling Price Rule for Mavjat Hospital --
INSERT INTO `ph_sell_price_rule` (`rule_id`, `start_range`, `end_range`, `margin_percentage`, `rule_description`) VALUES (NULL, '0', '20', '400', 'Item PR/Costing Less than or equal to RS.20 then margin should be 400% or MRP whichever is lesser.');
INSERT INTO `ph_sell_price_rule` (`rule_id`, `start_range`, `end_range`, `margin_percentage`, `rule_description`) VALUES (NULL, '20', '250', '180', 'Item PR/Costing > RS.20 & <= RS.250 then margin should be 180% or MRP whichever is lesser.');
INSERT INTO `ph_sell_price_rule` (`rule_id`, `start_range`, `end_range`, `margin_percentage`, `rule_description`) VALUES (NULL, '250', '500', '125', 'Item PR/Costing > RS.250 & <= RS.500 then margin should be 125% or MRP whichever is lesser.');
INSERT INTO `ph_sell_price_rule` (`rule_id`, `start_range`, `end_range`, `margin_percentage`, `rule_description`) VALUES (NULL, '500', '4999', '70', 'Item PR/Costing > RS.500 & <= RS.4999 then margin should be 70% or MRP whichever is lesser.');
INSERT INTO `ph_sell_price_rule` (`rule_id`, `start_range`, `end_range`, `margin_percentage`, `rule_description`) VALUES (NULL, '5000', NULL, '15', 'Item PR/Costing More than or equal to RS.5000 and above then margin should be 15% or MRP whichever is lesser.');

-- 30-JULY-2020 Author : Avanish (Stores Report related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase Detail - Supp / Item Wise', '', 'far fa-
circle', 'StoreItem/PurchaseDetailReportInput', NULL, 'Y', 'thims', '2020-07-30 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Product Expiry Report', '', 'far fa-
circle', 'StoreItem/productExpiryInput', NULL, 'Y', 'thims', '2020-07-30 00:00:36', '', '0000-00-00 00:00:00');

-- Release 3.01.12 Done --

-- 06-AUGUST-2020 Author : Avanish (Product Master conversion factor related changes) --
ALTER TABLE `product_master` CHANGE `conversion_factor` `conversion_factor` INT NULL DEFAULT NULL COMMENT 'conversion factor OR packing size';

-- 08-AUGUST-2020 Author : Avanish (Purchase GST Percentage related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_PURCHASE_ADJUSTMENT_AMOUNT', '1', 'if value is 1 then purchase bill submit time amount difference = 1 is allowed.');
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_PRODUCT_GST_PERCENTAGE', '0,5,12,18,28', 'only this percentages are allowed in pharmacy.');

-- 10-AUGUST-2020 Author : Avanish (Sales Collection float issue related changes) --
ALTER TABLE `ph_temp_cash_collection` CHANGE `issue_bill_amount` `issue_bill_amount` FLOAT(10,2) NOT NULL;
ALTER TABLE `ph_temp_cash_collection` CHANGE `issue_return_bill_amount` `issue_return_bill_amount` FLOAT(10,2) NOT NULL;
ALTER TABLE `ph_temp_cash_collection` CHANGE `received_amount` `received_amount` FLOAT(10,2) NOT NULL;
ALTER TABLE `ph_temp_cash_collection` CHANGE `refund_amount` `refund_amount` FLOAT(10,2) NOT NULL;

-- 11-AUGUST-2020 Author : Avanish (Indent related changes) --
ALTER TABLE `product_transaction` ADD `indent_id` INT NULL DEFAULT NULL AFTER `bill_amendment`;
ALTER TABLE `product_transaction_amendment` ADD `indent_id` INT NULL DEFAULT NULL AFTER `bill_amendment`;

-- 20-AUGUST-2020 Author : Avanish (Sales Bill File name related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_SALEBILL_FILENAME', 'medicine_bill', 'Parameter for which file name is used to print pharmacy sales/sales return bill.');

-- 21-AUGUST-2020 Author : Avanish (indent square off logic parameter) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_INDENT_SOFF_GENERIC_NAME', 'N', 'if Y then indent medicine square off with same generic name medicine.');

-- 24-AUGUST-2020 Author : Avanish (PO related changes) --
ALTER TABLE `purchase_order_header` CHANGE `payment_terms` `payment_terms_after` INT(11) NOT NULL DEFAULT '0';
ALTER TABLE `purchase_order_header` ADD `payment_terms_after_unit` ENUM('days','weeks','months') NOT NULL DEFAULT 'days' AFTER `payment_terms_after`;

-- 26-AUGUST-2020 Author : Avanish --
ALTER TABLE `product_stock` ADD `original_mrp` FLOAT(9,2) NULL DEFAULT '0.00' AFTER `mrp`;
UPDATE `product_stock` ps JOIN product_master pm ON pm.product_id = ps.product_id SET ps.`original_mrp` = ROUND(ps.`mrp_per_unit` * pm.conversion_factor, 2);

-- 27-AUGUST-2020 Author : Avanish (Delivery Challan related changes) --
INSERT INTO `last_transaction_number` (`trn_type`, `trn_fyear`, `trn_id`) VALUES ('DC', '2021', '0');
INSERT INTO `last_transaction_number` (`trn_type`, `trn_fyear`, `trn_id`) VALUES ('DCR', '2021', '0');

ALTER TABLE `business_entity_master` CHANGE `code` `code` CHAR(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT 'H- Hospital, P - Pharmacy, L - Laboratory, R - Radiology, IP - IP Pharmacy';

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Delivery Challan', '', 'far fa-
circle', 'Item/deliveryChallan', NULL, 'Y', 'thims', '2020-08-27 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Delivery Challan Return', '', 'far fa-
circle', 'Item/deliveryChallanReturn', NULL, 'Y', 'thims', '2020-08-28 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Delivery Challan List', '', 'far fa-
circle', 'TransactionDashboard/DeliveryChallan', NULL, 'Y', 'thims', '2020-08-28 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Delivery Challan Return List', '', 'far fa-
circle', 'TransactionDashboard/DeliveryChallanReturn', NULL, 'Y', 'thims', '2020-08-28 00:00:36', '', '0000-00-00 00:00:00');

ALTER TABLE `product_transaction_batch` CHANGE `barcode_source` `barcode_source` ENUM('P','IR','CC','DCR') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'P';
ALTER TABLE `sales_return_barcode_reprint` CHANGE `barcode_source` `barcode_source` ENUM('IR','CC','DCR') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'IR';

ALTER TABLE `purchase_order_header` ADD `approved_by` VARCHAR(10) NULL DEFAULT NULL AFTER `terms_conditions`;
ALTER TABLE `purchase_order_header` ADD `approved_date` DATE NULL DEFAULT NULL AFTER `approved_by`;

ALTER TABLE `email_setup` CHANGE `email_code` `email_code` VARCHAR(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
INSERT INTO `email_setup` (`id`, `email_code`, `subject`, `content`, `footer`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'POAPPROVED', 'Purchase Order', 'Purchase Order Status : Approved <br/> Please see attached order for supplies. <br/> If you have any questions, please contact us. <br/> Thank you for your service.', '', 'thims', '2020-09-03 00:00:00', '', '0000-00-00 00:00:00');
INSERT INTO `email_setup` (`id`, `email_code`, `subject`, `content`, `footer`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'POCLOSED', 'Purchase Order', 'Purchase Order Status : Closed <br/> Please see attached order for supplies. <br/> If you have any questions, please contact us. <br/> Thank you for your service.', '', 'thims', '2020-09-03 00:00:00', '', '0000-00-00 00:00:00');
INSERT INTO `email_setup` (`id`, `email_code`, `subject`, `content`, `footer`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'POCANCELLED', 'Purchase Order', 'Purchase Order Status : Cancelled <br/> Please see attached order for supplies. <br/> If you have any questions, please contact us. <br/> Thank you for your service.', '', 'thims', '2020-09-03 00:00:00', '', '0000-00-00 00:00:00');

-- 04-SEPTEMBER-2020 Author : Avanish (PO Category related changes) --
ALTER TABLE `purchase_order_header` ADD `po_category` ENUM('G','P') NOT NULL DEFAULT 'G' COMMENT 'G - General PO, P - Pharmacy PO' AFTER `po_status`;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase Order', '', 'far fa-
circle', 'StorePoDashboard/PharmacyUser', NULL, 'Y', 'thims', '2020-09-04 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Approve Purchase Order', '', 'far fa-
circle', 'StorePoDashboard/PharmacySuperUser', NULL, 'Y', 'thims', '2020-09-04 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'PO Reprint', '', 'far fa-
circle', 'StorePo/PharmacyReprint', NULL, 'Y', 'thims', '2020-09-04 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_STORE_PRODUCT_GST_PERCENTAGE', '0,5,12,18,28', 'only this percentages are allowed in store.');

ALTER TABLE `product_transaction_header` ADD `shipping_other_charges` FLOAT(10,2) NOT NULL DEFAULT '0.00' AFTER `is_ptr_applicable`;

-- Release 3.01.13 Done --

-- 18-SEPTEMBER-2020 Author : Avanish (Update IP ID for existing receipt and bill record) --
UPDATE `product_transaction_header` SET ipd_reg_id = 0 WHERE `trn_type` IN ('I','IR') AND visit_id = 0 and ipd_reg_id IS NULL;
UPDATE `product_transaction_header` ph LEFT JOIN visit v ON v.visit_id = ph.visit_id SET ph.ipd_reg_id = v.ipd_reg_id WHERE ph.visit_id > 0;

UPDATE `product_receipt_transaction` SET `ipd_reg_id` = 0 WHERE ipd_reg_id IS NULL AND visit_id = 0;
UPDATE `product_receipt_transaction` pr LEFT JOIN visit v ON v.visit_id = pr.visit_id SET pr.ipd_reg_id = v.ipd_reg_id WHERE pr.visit_id > 0;

ALTER TABLE `ph_temp_payment_collection` ADD `ipd_reg_id` INT NOT NULL AFTER `patient_id`;

ALTER TABLE `pharmacy_bill_amt_writeoff_detail` ADD `ipd_reg_id` INT(10) NULL DEFAULT NULL AFTER `patient_id`;

-- 22-SEPTEMBER-2020 Author : Avanish (MRP and Expiry Update Transaction related changes) --
DROP TABLE IF EXISTS `product_stock_update`;
CREATE TABLE IF NOT EXISTS `product_stock_update` (
  `update_id` int(11) NOT NULL AUTO_INCREMENT,
  `update_type` enum('M','E') NOT NULL COMMENT 'M - MRP Update, E - Expiry Update',
  `store_code` varchar(6) NOT NULL DEFAULT '1',
  `product_id` int(10) UNSIGNED NOT NULL COMMENT 'product_master - product_id',
  `batch_no` varchar(15) DEFAULT NULL,
  `batch_exp` date DEFAULT NULL,
  `revised_batch_exp` date DEFAULT NULL,
  `mrp_per_unit` float(9,2) DEFAULT NULL,
  `revised_mrp_per_unit` float(9,2) DEFAULT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`update_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Expiry Update Transaction', '', 'far fa-
circle', 'Item/ExpiryUpdateTransaction', NULL, 'Y', 'thims', '2020-09-22 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'MRP Update Transaction', '', 'far fa-
circle', 'Item/MrpUpdateTransaction', NULL, 'Y', 'thims', '2020-09-23 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Expiry Update Transaction', '', 'far fa-
circle', 'Item/StoreExpiryUpdateTransaction', NULL, 'Y', 'thims', '2020-09-22 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'MRP Update Transaction', '', 'far fa-
circle', 'Item/StoreMrpUpdateTransaction', NULL, 'Y', 'thims', '2020-09-23 00:00:36', '', '0000-00-00 00:00:00');

-- 24-SEPTEMBER-2020 Author : Avanish (Opening Stock Update Transaction related changes) --
DROP TABLE IF EXISTS `product_stock_opening_audit_trial`;
CREATE TABLE IF NOT EXISTS `product_stock_opening_audit_trial` (
  `opening_audit_trial_id` int(11) NOT NULL AUTO_INCREMENT,
  `fyear` smallint(4) NOT NULL COMMENT 'Product Stock Financial Year',
  `store_code` varchar(6) NOT NULL DEFAULT '1',
  `product_id` int(10) UNSIGNED NOT NULL COMMENT 'product_master - product_id',
  `batch_no` varchar(15) DEFAULT NULL,
  `batch_exp` date DEFAULT NULL COMMENT 'To store batch expiry month and year (it will store date as 1st of that given month)',
  `price` float(10,2) NOT NULL,
  `price_per_unit` float(9,2) NOT NULL,
  `price_per_unit_wo_gst` float(9,2) NOT NULL DEFAULT '0.00',
  `original_mrp` float(9,2) DEFAULT '0.00',
  `mrp_per_unit` float(9,2) NOT NULL,
  `gst_percentage` float(9,2) NOT NULL DEFAULT '0.00',
  `current_stock_qty` float NOT NULL COMMENT 'Current Stock Quantity',
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`opening_audit_trial_id`),
  UNIQUE KEY `store_code` (`store_code`,`product_id`,`batch_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Opening Stock Update Transaction', '', 'far fa-
circle', 'Item/OpeningStockUpdateTransaction', NULL, 'Y', 'thims', '2020-09-24 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Opening Stock Update Transaction', '', 'far fa-
circle', 'Item/StoreOpeningStockUpdateTransaction', NULL, 'Y', 'thims', '2020-09-26 00:00:36', '', '0000-00-00 00:00:00');

-- Release 3.01.14 Done --

-- 29-SEPTEMBER-2020 Author : Avanish (without barcode sales allowed or not parameterized) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_SALES_WO_BARCODE_TOBE_ALLOWED', 'Y', 'if value is Y, we will give warning while selling without barcode. If value is set to N, we will give error and not allow to sale without barcode and if value set to NA, we will not do anything.');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Cycle Count Adjustment - Item Wise', '', 'far fa-
circle', 'Item/stockCycleReportItemWise', NULL, 'Y', 'thims', '2020-09-29 00:00:36', '', '0000-00-00 00:00:00');

-- 03-OCTOBER-2020 Author : Avanish (TCS Amount related changes) --
ALTER TABLE `product_transaction_header_amendment` ADD `shipping_other_charges` FLOAT(10,2) NOT NULL DEFAULT '0.00' AFTER `is_ptr_applicable`;

ALTER TABLE `product_transaction_header` ADD `tcs_amount` FLOAT(9,2) NOT NULL DEFAULT '0.00' AFTER `sch_amount`;
ALTER TABLE `product_transaction_header_amendment` ADD `tcs_amount` FLOAT(9,2) NOT NULL DEFAULT '0.00' AFTER `sch_amount`;

-- 06-OCTOBER-2020 Author : Avanish (Purchase Order related changes) --
ALTER TABLE `purchase_order_delivery_schedule` ADD `received_quantity` FLOAT NOT NULL DEFAULT '0' AFTER `po_quantity`;
ALTER TABLE `purchase_order_delivery_schedule` ADD `pending_quantity` FLOAT NOT NULL DEFAULT '0' AFTER `received_quantity`;
UPDATE `purchase_order_delivery_schedule` SET `pending_quantity`=`po_quantity`;

-- Release 3.01.15 Done --

-- 08-OCTOBER-2020 Author : Avanish (Purchase With PO related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase With PO', '', 'far fa-
circle', 'Item/purchaseWithPo', NULL, 'Y', 'thims', '2020-10-08 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_PO_BUFFER_DELIVERY_DAYS', '0', 'if value is 1 then 1 day before delivery date qty allowed.');

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_PO_BUFFER_QTY_PERCENTAGE', '0', 'if value is 10 then allowed qty = pending qty + 10 % of pending qty.');

ALTER TABLE `purchase_order_detail` CHANGE `discount_type` `discount_type` ENUM('A','P') CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'P';
UPDATE `purchase_order_detail` SET `discount_type` = "P" WHERE `discount_type` IS NULL;

-- 20-OCTOBER-2020 Author : Avanish (Store code added in barcode table primary key related changes) --
ALTER TABLE `product_transaction_batch` DROP INDEX `UK_TransactionNo`, ADD UNIQUE `UK_TransactionNo` (`trn_fyear`, `trn_type`, `trn_id`, `trn_sr_no`, `trn_batch_sr_no`, `store_code`) USING BTREE;

-- 20-OCTOBER-2020 Author : Avanish (Entity Wise PO Serial Number related changes) --
ALTER TABLE `last_transaction_number` ADD `business_entity_code` CHAR(6) NOT NULL AFTER `trn_type`;
ALTER TABLE `last_transaction_number` DROP PRIMARY KEY, ADD PRIMARY KEY (`trn_type`, `business_entity_code`, `trn_fyear`) USING BTREE;
INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('PO', 'P', '2021', '0');

UPDATE `general_master` SET gm_short_code = "H" WHERE `master_cd` LIKE 'STORE';

ALTER TABLE `purchase_order_header` ADD `po_entity_code` CHAR(6) NOT NULL AFTER `po_type`;
ALTER TABLE `purchase_order_header` DROP PRIMARY KEY, ADD PRIMARY KEY (`po_fyear`, `po_type`, `po_entity_code`, `po_id`) USING BTREE;
UPDATE `purchase_order_header` SET `po_entity_code` = "H";

ALTER TABLE `purchase_order_header` ADD `store_code` VARCHAR(6) NOT NULL AFTER `po_fyear`;
UPDATE `purchase_order_header` SET store_code = 1 WHERE `po_category` = 'P';
UPDATE `purchase_order_header` SET store_code = 2 WHERE `po_category` = 'G';

ALTER TABLE `purchase_order_header` DROP PRIMARY KEY, ADD PRIMARY KEY (`po_fyear`, `store_code`, `po_type`, `po_entity_code`, `po_id`) USING BTREE;

ALTER TABLE `purchase_order_detail` ADD `po_entity_code` CHAR(6) NOT NULL AFTER `po_type`;
ALTER TABLE `purchase_order_detail` DROP INDEX `UK_TransactionNo`, ADD UNIQUE `UK_TransactionNo` (`po_fyear`, `po_type`, `po_entity_code`, `po_id`, `po_sr_no`) USING BTREE;
UPDATE `purchase_order_detail` SET `po_entity_code` = "H";

ALTER TABLE `purchase_order_delivery_schedule` ADD `po_entity_code` CHAR(6) NOT NULL AFTER `po_type`;
ALTER TABLE `purchase_order_delivery_schedule` DROP INDEX `UK_TransactionNo`, ADD UNIQUE `UK_TransactionNo` (`po_fyear`, `po_type`, `po_entity_code`, `po_id`, `po_sr_no`, `expected_delivery_date`) USING BTREE;
UPDATE `purchase_order_delivery_schedule` SET `po_entity_code` = "H";

ALTER TABLE `product_transaction_header` ADD `purchase_order_entity_code` CHAR(6) NULL DEFAULT NULL AFTER `purchase_order_no`;
UPDATE `product_transaction_header` SET `purchase_order_entity_code` = "H" WHERE `purchase_order_no` > 0;

-- 28-OCTOBER-2020 Author : Avanish (PO Amendment related changes) --
ALTER TABLE `purchase_order_header` ADD `po_amendment` SMALLINT(3) NOT NULL DEFAULT '0' AFTER `po_id`;
ALTER TABLE `purchase_order_detail` ADD `po_amendment` SMALLINT(3) NOT NULL DEFAULT '0' AFTER `po_sr_no`;
ALTER TABLE `purchase_order_delivery_schedule` ADD `po_amendment` SMALLINT(3) NOT NULL DEFAULT '0' AFTER `po_sr_no`;

DROP TABLE IF EXISTS `purchase_order_header_amendment`;
CREATE TABLE IF NOT EXISTS `purchase_order_header_amendment` (
  `po_fyear` smallint(4) NOT NULL COMMENT 'Transaction Financial Year',
  `store_code` varchar(6) NOT NULL,
  `po_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return',
  `po_entity_code` char(6) NOT NULL,
  `po_id` int(11) NOT NULL,
  `po_amendment` smallint(3) NOT NULL,
  `po_date` date NOT NULL COMMENT 'Transaction Date',
  `po_status` char(1) NOT NULL COMMENT 'Partially saved - P Unapproved - U (Pending approval) Cancelled - T, Approved - A',
  `po_category` enum('G','P') NOT NULL DEFAULT 'G' COMMENT 'G - General PO, P - Pharmacy PO',
  `is_closed` enum('Y','N') NOT NULL DEFAULT 'N',
  `supplier_id` smallint(5) UNSIGNED DEFAULT NULL,
  `gross_total` float NOT NULL COMMENT 'Roll up from child items stored in transaction table',
  `discount_type` enum('P','A') DEFAULT NULL,
  `discount` float DEFAULT NULL,
  `discount_amt` float NOT NULL,
  `net_taxable_amt` float DEFAULT '0',
  `cgst_amt` float DEFAULT '0',
  `igst_amt` float DEFAULT '0',
  `sgst_amt` float DEFAULT '0',
  `shipping_charges` float NOT NULL DEFAULT '0',
  `other_charges` float NOT NULL DEFAULT '0',
  `invoice_amt` float DEFAULT '0',
  `round_off` float NOT NULL,
  `total` float NOT NULL COMMENT 'Net Total',
  `payment_terms_after` int(11) NOT NULL DEFAULT '0',
  `payment_terms_after_unit` enum('days','weeks','months') NOT NULL DEFAULT 'days',
  `terms_conditions` text,
  `approved_by` varchar(10) DEFAULT NULL,
  `approved_date` date DEFAULT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`po_fyear`,`store_code`,`po_type`,`po_entity_code`,`po_id`) USING BTREE,
  KEY `FK_suppliermaster` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `purchase_order_detail_amendment`;
CREATE TABLE IF NOT EXISTS `purchase_order_detail_amendment` (
  `po_fyear` smallint(4) NOT NULL COMMENT 'Transaction Financial Year',
  `po_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return ',
  `po_entity_code` char(6) NOT NULL,
  `po_id` int(11) NOT NULL,
  `po_sr_no` smallint(3) NOT NULL,
  `po_amendment` smallint(3) NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL COMMENT 'product_master - product_id',
  `additional_product_description` varchar(100) DEFAULT NULL,
  `purchase_uom` tinyint(3) UNSIGNED NOT NULL,
  `po_quantity` float NOT NULL DEFAULT '0',
  `received_quantity` float NOT NULL DEFAULT '0',
  `pending_quantity` float NOT NULL DEFAULT '0',
  `price` float DEFAULT '0' COMMENT 'For trn_type=''P'' its price for PUOM and for trn_type=''I'' it will be kept 0 ',
  `price_per_unit_suom` float NOT NULL DEFAULT '0' COMMENT 'Price Per Unit based on stock unit of measure',
  `discount_type` enum('A','P') DEFAULT 'P',
  `discount` float DEFAULT '0',
  `discount_amt` float DEFAULT '0',
  `taxable_amt` float DEFAULT '0',
  `net_rate` float DEFAULT '0',
  `sgst_percentage` float NOT NULL DEFAULT '0',
  `sgst_value` float NOT NULL,
  `cgst_percentage` float NOT NULL DEFAULT '0',
  `cgst_value` float NOT NULL,
  `igst_percentage` float DEFAULT '0',
  `igst_value` float NOT NULL,
  `total` float NOT NULL DEFAULT '0' COMMENT 'For trn_type = ''P'' => total = price * quantity For trn_type = ''I'' => (price * quantity) - discount',
  `landing_rate` float DEFAULT '0',
  `landing_cost` float DEFAULT '0',
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  UNIQUE KEY `UK_TransactionNo` (`po_fyear`,`po_type`,`po_entity_code`,`po_id`,`po_sr_no`) USING BTREE,
  KEY `FK_product_master_id` (`product_id`),
  KEY `FK_unitmaster_popuom` (`purchase_uom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `purchase_order_delivery_schedule_amendment`;
CREATE TABLE IF NOT EXISTS `purchase_order_delivery_schedule_amendment` (
  `po_fyear` smallint(4) NOT NULL COMMENT 'Transaction Financial Year',
  `po_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return ',
  `po_entity_code` char(6) NOT NULL,
  `po_id` int(11) NOT NULL,
  `po_sr_no` smallint(3) NOT NULL,
  `po_amendment` smallint(3) NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL COMMENT 'product_master - product_id',
  `po_quantity` float NOT NULL DEFAULT '0',
  `received_quantity` float NOT NULL DEFAULT '0',
  `pending_quantity` float NOT NULL DEFAULT '0',
  `expected_delivery_date` date NOT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  UNIQUE KEY `UK_TransactionNo` (`po_fyear`,`po_type`,`po_entity_code`,`po_id`,`po_sr_no`,`expected_delivery_date`) USING BTREE,
  KEY `FK_product_master_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 28-OCTOBER-2020 Author : Avanish (ref_trn_id added in product_transaction) --
ALTER TABLE `product_transaction` ADD `ref_trn_id` INT(11) NOT NULL AFTER `bill_amendment`;

-- 02-NOVEMBER-2020 Author : Avanish (net_rate and landing_rate updation for existing purchase transaction having free_quantity) --
UPDATE `product_transaction` SET net_rate = ROUND(taxable_amt / (quantity + free_quantity), 2) WHERE `trn_type` = 'P' AND `free_quantity` > 0;
UPDATE `product_transaction` SET landing_rate = ROUND(landing_cost / (quantity + free_quantity), 2) WHERE `trn_type` = 'P' AND `free_quantity` > 0;

-- Release Done --

-- 06-NOVEMBER-2020 Author : Avanish (Material Issue Return Barcode related changes) --
ALTER TABLE `sales_return_barcode_reprint` CHANGE `barcode_source` `barcode_source` ENUM('IR','CC','DCR','GINR') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'IR';
ALTER TABLE `product_transaction_batch` CHANGE `barcode_source` `barcode_source` ENUM('P','IR','CC','DCR','GINR') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'P';

-- 07-NOVEMBER-2020 Author : Avanish (Existing Purchase Transaction update for Purchase cash related changes) --
UPDATE `product_transaction_header` SET is_fully_paid = "N" WHERE `trn_type` IN ('P','PR');
UPDATE `product_transaction_header` SET is_credit_bill = "Y" WHERE `trn_type` IN ('P','PR');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase - Cash', '', 'far fa-
circle', 'Item/purchaseCash', NULL, 'Y', 'thims', '2020-11-07 00:00:36', '', '0000-00-00 00:00:00');

-- Release Done --

-- 19-NOVEMBER-2020 Author : Avanish (Barcode Based Product Inquiry related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Barcode Based Product Inquiry', '', 'far fa-
circle', 'Item/BarcodeBasedProductInquiry', NULL, 'Y', 'thims', '2020-11-19 00:00:36', '', '0000-00-00 00:00:00');

-- Release Done --

-- 20-NOVEMBER-2020 Author : Avanish (Sales Discount Report related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Sales Discount Report', '', 'far fa-
circle', 'Item/SalesDiscountReportInput', NULL, 'Y', 'thims', '2020-11-20 00:00:36', '', '0000-00-00 00:00:00');

-- 21-NOVEMBER-2020 Author : Avanish (Reorder Request from Sales related changes) --
DROP TABLE IF EXISTS `product_reorder_request`;
CREATE TABLE IF NOT EXISTS `product_reorder_request` (
  `store_code` varchar(6) NOT NULL,
  `product_id` int(10) NOT NULL,
  `reorder_request_date` date NOT NULL,
  `product_reorder_level` int(4) NULL,
  `current_stock_qty` float NOT NULL,
  `is_order_done` enum('Y','N') NOT NULL DEFAULT 'N',
  `created_by` varchar(15) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`store_code`,`product_id`,`reorder_request_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 25-NOVEMBER-2020 Author : Avanish (Reorder Request table and Report related changes) --
ALTER TABLE `product_reorder_request` ADD `ref_trn_id` VARCHAR(15) NULL AFTER `is_order_done`;
ALTER TABLE `product_reorder_request` ADD `ref_trn_date` DATE NULL AFTER `ref_trn_id`;
ALTER TABLE `product_reorder_request` ADD `lastmodified_by` VARCHAR(15) NULL AFTER `created_date`;
ALTER TABLE `product_reorder_request` ADD `lastmodified_date` DATETIME NULL AFTER `lastmodified_by`;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Reorder Request Report', '', 'far fa-
circle', 'Item/ReorderRequestReportInput', NULL, 'Y', 'thims', '2020-11-25 00:00:36', '', '0000-00-00 00:00:00');

-- 27-NOVEMBER-2020 Author : Avanish (Sales Discount Amendment related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Sale Discount Edit', '', 'far fa-
circle', 'Item/issueBillDiscountAmendment', NULL, 'Y', 'thims', '2020-11-27 00:00:36', '', '0000-00-00 00:00:00');

-- Release Done --

-- 30-NOVEMBER-2020 Author : Avanish (Sales Discount Edit time Margin Percentage validation related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PH_ADDITIONAL_DISCOUNT_UPTO_MARGIN_PERCENT', '20', 'if margin percentage is above this value then discount is editable.');

-- Release Done --

-- 02-DECEMBER-2020 Author : Avanish (Non Moving Product Report related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Non / Slow Moving Product Report', '', 'far fa-
circle', 'Item/deadStockInput', NULL, 'Y', 'thims', '2020-12-02 00:00:36', '', '0000-00-00 00:00:00');

ALTER TABLE `product_transaction` ADD INDEX( `trn_type`, `trn_id`);
ALTER TABLE `product_transaction_header` ADD INDEX(`trn_date`);
ALTER TABLE `product_transaction_header` ADD INDEX( `trn_type`, `trn_id`);

CREATE TABLE IF NOT EXISTS `temp_nonmoving_products` (
  `product_id` int(10) NOT NULL,
  `product_name` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `puom` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `suom` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `current_stock` float NOT NULL DEFAULT '0',
  `rate` float(9,2) NOT NULL DEFAULT '0.00',
  `amount` float(9,2) NOT NULL DEFAULT '0.00',
  `last_purchase_date` date DEFAULT NULL,
  `last_purchase_voucher_no` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_purchase_duration` int(11) DEFAULT NULL,
  `last_sale_date` date DEFAULT NULL,
  `last_sale_voucher_no` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_sale_duration` int(11) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DELIMITER $$
CREATE PROCEDURE `proc_getNonMovingProductList`(IN `days` INT, IN `categoryId` TINYINT(3), IN `storeCode` VARCHAR(6), IN `rateType` VARCHAR(2), OUT `success` INT)
    NO SQL
BEGIN
    DECLARE EXIT
HANDLER FOR SQLEXCEPTION
BEGIN
        	SHOW ERRORS;
    SELECT         '----ERROR IN PERFORMING OPERATION (SQLEXCEPTION)';
    ROLLBACK
        ;
SET AUTOCOMMIT=1;
GET DIAGNOSTICS CONDITION 1 @ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT;

SELECT CONCAT('MySQL ERROR: ', @ERRNO, ': ', @MESSAGE_TEXT);

    END ; DECLARE EXIT
HANDLER FOR SQLWARNING
BEGIN
          SHOW WARNINGS;
     SELECT
        '----ERROR IN PERFORMING OPERATION (SQLWARNING)';
    ROLLBACK
        ;
        SET AUTOCOMMIT=1;
GET DIAGNOSTICS CONDITION 1 @ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT;

SELECT CONCAT('MySQL ERROR: ', @ERRNO, ': ', @MESSAGE_TEXT);

    END ;

SET success = 0;
SET AUTOCOMMIT=0;
START TRANSACTION
    ;

TRUNCATE TABLE `temp_nonmoving_products`;

SELECT @transaction_endDate := DATE(now());
SELECT @transaction_startDate := DATE_SUB(@transaction_endDate, INTERVAL days DAY);


CREATE TEMPORARY TABLE tempSoldProductList AS(
    SELECT DISTINCT
        (pt.product_id)
    FROM
        product_transaction pt
    INNER JOIN product_transaction_header ph ON
        pt.trn_type = ph.trn_type AND pt.trn_id = ph.trn_id
    INNER JOIN product_master pm on pt.product_id = pm.product_id
    WHERE
        ph.store_code = storeCode AND ph.trn_date BETWEEN @transaction_startDate AND @transaction_endDate AND pt.trn_type IN ('I', 'DC', 'GIN') and IF(categoryId = 0, true, pm.category_id = categoryId)

);

INSERT INTO temp_nonmoving_products(product_id, product_name, puom, suom) SELECT
    pm.product_id,
    product_name,
    pum.unit_abbrev,
    sum.unit_abbrev
FROM
    product_master pm
    inner join unit_master pum on pm.purchase_uom = pum.unit_id
    inner join unit_master sum on pm.stock_uom = sum.unit_id
    WHERE
    pm.product_id NOT IN(
    SELECT
        product_id
    FROM
        tempSoldProductList
) and IF(categoryId = 0, true, pm.category_id = categoryId);

UPDATE
    temp_nonmoving_products tnp
INNER JOIN(
    SELECT
        ps.store_code,
        ps.product_id,
        SUM(
            ps.current_stock_qty
        ) AS current_stock
    FROM
        product_stock ps where ps.store_code = storeCode
    GROUP BY
        ps.product_id
) AS t
ON
    tnp.product_id = t.product_id AND t.store_code = storeCode
SET
    tnp.current_stock = t.current_stock;

DELETE from temp_nonmoving_products where current_stock = 0;

UPDATE
    temp_nonmoving_products tnp
INNER JOIN(
    SELECT
        ph.store_code,
        ph.trn_type,
        ph.trn_id,
        pt.product_id,
        (CASE WHEN rateType = 'W' THEN pt.price_per_unit_suom WHEN rateType = 'WO' THEN pt.price_per_unit_suom_wo_gst ELSE pt.mrp END) as rate,
        MAX(
            ph.trn_date
        ) AS last_purchase_date
    FROM
        product_transaction_header ph
    inner join product_transaction pt ON pt.trn_type = ph.trn_type and pt.trn_id = ph.trn_id where ph.trn_type in ('P', 'GRN') and ph.trn_date < @transaction_startDate group by pt.product_id
) AS t
ON
    tnp.product_id = t.product_id
SET
    tnp.rate = t.rate,
    tnp.amount = ROUND((tnp.current_stock * t.rate), 2),
    tnp.last_purchase_date = t.last_purchase_date,
    tnp.last_purchase_voucher_no = CONCAT(t.trn_type, t.trn_id)  ,
    tnp.last_purchase_duration = DATEDIFF(DATE(now()), t.last_purchase_date);


UPDATE
    temp_nonmoving_products tnp
INNER JOIN(
    SELECT
        ph.store_code,
        ph.trn_type,
        ph.trn_id,
        pt.product_id,
        MAX(
            ph.trn_date
        ) AS last_sale_date
    FROM
        product_transaction_header ph
    inner join  product_transaction pt ON pt.trn_type = ph.trn_type and pt.trn_id = ph.trn_id where ph.trn_type IN ( 'I', 'DC', 'GIN') and ph.store_code = storeCode and ph.trn_date < @transaction_startDate group by pt.product_id
) AS t
ON
    tnp.product_id = t.product_id
SET
    tnp.last_sale_date = t.last_sale_date,
    tnp.last_sale_voucher_no = CONCAT(t.trn_type, t.trn_id)  ,
    tnp.last_sale_duration = DATEDIFF(DATE(now()), t.last_sale_date);

COMMIT;

SET AUTOCOMMIT=1;
SET success = 1;

END$$
DELIMITER ;

-- 12-DECEMBER-2020 Author : Avanish (Cash Collection Report related changes) --
ALTER TABLE `ph_temp_cash_collection` ADD `no_of_exp_voucher` INT(5) NOT NULL AFTER `refund_amount`;
ALTER TABLE `ph_temp_cash_collection` ADD `expense_amount` FLOAT(10,2) NOT NULL AFTER `no_of_exp_voucher`;

-- 15-DECEMBER-2020 Author : Avanish (Purchase Scheme Amount report related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase Scheme Amount Report', '', 'far fa-
circle', 'Item/PurchaseSchemeAmountReportInput', NULL, 'Y', 'thims', '2020-12-15 00:00:36', '', '0000-00-00 00:00:00');

-- 16-DECEMBER-2020 Author : Avanish (PO Amendment related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase Order Amendment', '', 'far fa-
circle', 'StorePo/PurchaseOrderAmendment', NULL, 'Y', 'thims', '2020-12-16 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase Order Amendment', '', 'far fa-
circle', 'StorePo/StorePurchaseOrderAmendment', NULL, 'Y', 'thims', '2020-12-16 00:00:36', '', '0000-00-00 00:00:00');

-- Release Done --

-- 28-DECEMBER-2020 Author : Avanish (PO Amendment related changes) --
ALTER TABLE `purchase_order_header_amendment` DROP PRIMARY KEY, ADD PRIMARY KEY (`po_fyear`, `store_code`, `po_type`, `po_entity_code`, `po_id`, `po_amendment`) USING BTREE;
ALTER TABLE `purchase_order_detail_amendment` DROP INDEX `UK_TransactionNo`, ADD UNIQUE `UK_TransactionNo` (`po_fyear`, `po_type`, `po_entity_code`, `po_id`, `po_sr_no`, `po_amendment`) USING BTREE;
ALTER TABLE `purchase_order_delivery_schedule_amendment` DROP INDEX `UK_TransactionNo`, ADD UNIQUE `UK_TransactionNo` (`po_fyear`, `po_type`, `po_entity_code`, `po_id`, `po_sr_no`, `expected_delivery_date`, `po_amendment`) USING BTREE;

-- 04-JANUARY-2021 Author : Avanish (Patient Wise Profit Report related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Patient Wise Profit Report', '', 'far fa-
circle', 'Item/PatientWiseProfitReportInput', NULL, 'Y', 'thims', '2021-01-04 00:00:36', '', '0000-00-00 00:00:00');

-- 05-JANUARY-2021 Author : Avanish (Expiry Validation in Purchase related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_EXP_ALERT_DAYS', '60', 'if value is 60 then alert message display at time of purchase when difference between current date and entered expiry date will be less than 60.', 'N', '', '');

-- Release Done --

-- 09-JANUARY-2021 Author : Avanish (Indent Notification parameterized related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_INDENT_NOTIFICATION_ALLOWED_STORES', '17', 'indent notification will be displayed based on this parameter value.', 'N', '', '');

-- 19-JANUARY-2021 Author : Avanish (Store Wise Product related changes) --
DROP TABLE IF EXISTS `product_store_master`;
CREATE TABLE IF NOT EXISTS `product_store_master` (
  `store_code` varchar(6) NOT NULL DEFAULT '1',
  `product_id` int(10) NOT NULL,
  `product_bin_location` varchar(10) DEFAULT NULL COMMENT 'Physical location OR Rack No',
  `product_reorder_level` int(4) DEFAULT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  UNIQUE KEY `store_code` (`store_code`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Store Wise Product Details', '', 'far fa-
circle', 'Item/StoreWiseProductDetails', NULL, 'Y', 'thims', '2021-01-19 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `product_store_master`(
    `store_code`,
    `product_id`,
    `product_bin_location`,
    `product_reorder_level`,
    `created_by`,
    `created_date`
)
SELECT
    1,
    product_id,
    product_bin_location,
    product_reorder_level,
    'thims',
    '2021-01-22 00:00:00'
FROM
    product_master
WHERE
    product_is_active = "Y" AND product_type IN ('M', 'I');

-- Release Done --

-- 28-JANUARY-2021 Author : Avanish (Store Purchase Transaction related changes) --
ALTER TABLE `product_stock` CHANGE `latest_purchase_price_wo_gst` `latest_purchase_price_wo_gst` FLOAT(11,4) NOT NULL DEFAULT '0.0000';
ALTER TABLE `product_transaction` CHANGE `price` `price` FLOAT(9,4) NULL DEFAULT '0.0000' COMMENT 'For trn_type=\'P\' its price for PUOM and for trn_type=\'I\' it will be kept 0 ', CHANGE `price_per_unit_suom` `price_per_unit_suom` FLOAT(9,4) NOT NULL COMMENT 'Price Per Unit based on stock unit of measure', CHANGE `price_per_unit_suom_wo_gst` `price_per_unit_suom_wo_gst` FLOAT(9,4) NOT NULL DEFAULT '0.0000';
ALTER TABLE `product_stock` CHANGE `price` `price` FLOAT(10,4) NOT NULL, CHANGE `price_per_unit` `price_per_unit` FLOAT(9,4) NOT NULL, CHANGE `price_per_unit_wo_gst` `price_per_unit_wo_gst` FLOAT(9,4) NOT NULL DEFAULT '0.0000', CHANGE `latest_purchase_price_per_unit_wo_gst` `latest_purchase_price_per_unit_wo_gst` FLOAT(9,4) NOT NULL DEFAULT '0.0000';

-- Release Done --

-- 05-FEBRUARY-2021 Author : Avanish (Reorder Request related changes) --
ALTER TABLE `product_reorder_request` ADD `product_reorder_qty` FLOAT NOT NULL AFTER `current_stock_qty`;
ALTER TABLE `product_reorder_request` ADD `product_ordered_qty` FLOAT NOT NULL AFTER `product_reorder_qty`;

UPDATE `product_reorder_request` SET `product_reorder_qty` = 0 WHERE `product_reorder_level` = 0 OR product_reorder_level is null;
UPDATE `product_reorder_request` SET `product_reorder_qty` = `product_reorder_level` WHERE `product_reorder_level` > 0;

-- 08-FEBRUARY-2021 Author : Avanish (Supplier Product Relationship related changes) --
DROP TABLE IF EXISTS `supplier_product_relationship`;
CREATE TABLE IF NOT EXISTS `supplier_product_relationship` (
  `supplier_id` int(10) NOT NULL,
  `product_id` int(10) NOT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  UNIQUE KEY `supplier_id` (`supplier_id`,`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO supplier_product_relationship SELECT ph.supplier_id, pt.product_id, "thims", "2021-02-08 00:00:00" FROM `product_transaction` pt JOIN product_transaction_header ph ON ph.trn_type = pt.trn_type AND ph.trn_id = pt.trn_id WHERE pt.trn_type IN ("P", "GRN") GROUP BY pt.product_id, ph.supplier_id;

-- 09-FEBRUARY-2021 Author : Avanish (Standard PO Delivery Date related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_PO_STD_DELIVERY_DAYS', '15', 'if value is 15 then 15 days after current date will be considered as standard delivery date.', 'N', '', '');

-- Release Done --

-- 05-MARCH-2021 Author : Avanish (Bill - Receipt Unsettlement related changes) --
ALTER TABLE `product_receipt_transaction` CHANGE `modified_remark` `modified_remark` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Bill - Receipt Unsettlement', '', 'far fa-
circle', 'Item/BillReceiptUnsettleInput', NULL, 'Y', 'thims', '2021-03-05 00:00:36', '', '0000-00-00 00:00:00');

-- 12-MARCH-2021 Author : Jayraj (Employee salary calculation add remark) --
ALTER TABLE `employee_monthly_salary` ADD `remark` VARCHAR(100) NULL AFTER `net_payable`;

-- 24-MARCH-2021 Author : Avanish (Consignment transaction related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_CONSIGNMENT_STORE', '1', 'Parameter for consignment purchase activity.', 'N', '', '');

INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('CSN', '', '2021', '0');
INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('CSNR', '', '2021', '0');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment Purchase', '', 'far fa-
circle', 'Item/ConsignmentPurchase', NULL, 'Y', 'thims', '2021-03-24 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment Purchase Return', '', 'far fa-
circle', 'Item/ConsignmentPurchaseReturn', NULL, 'Y', 'thims', '2021-03-24 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment Purchase List', '', 'far fa-
circle', 'TransactionDashboard/ConsignmentPurchase', NULL, 'Y', 'thims', '2021-03-24 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment Purchase Return List', '', 'far fa-
circle', 'TransactionDashboard/ConsignmentPurchaseReturn', NULL, 'Y', 'thims', '2021-03-24 00:00:36', '', '0000-00-00 00:00:00');

-- 26-MARCH-2021 Author : Avanish (Consignment Register Report related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment Transaction Register', '', 'far fa-
circle', 'Item/ConsignmentRegisterInput', NULL, 'Y', 'thims', '2021-03-26 00:00:36', '', '0000-00-00 00:00:00');

-- 27-MARCH-2021 Author : Avanish (Consignment Delivery Challan related changes) --
ALTER TABLE `product_transaction_header` ADD `is_consignment_dc` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `is_ptr_applicable`;
ALTER TABLE `product_transaction_header_amendment` ADD `is_consignment_dc` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `is_ptr_applicable`;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment DC', '', 'far fa-
circle', 'Item/ConsignmentDeliveryChallan', NULL, 'Y', 'thims', '2021-03-27 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment DC Return', '', 'far fa-
circle', 'Item/ConsignmentDeliveryChallanReturn', NULL, 'Y', 'thims', '2021-03-27 00:00:36', '', '0000-00-00 00:00:00');

-- 02-APRIL-2021 Author : Avanish (Consignment Purchase Order related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'PO With Consignment DC', '', 'far fa-
circle', 'StorePo/ConsignmentPurchaseOrder', NULL, 'Y', 'thims', '2021-04-02 00:00:36', '', '0000-00-00 00:00:00');

-- 12-APRIL-2021 Author : Avanish (Payment Collection related changes) --
ALTER TABLE `ipd` ADD `is_pharmacy_ffs_completed` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `is_ffs_discharge_completed`;

-- 13-APRIL-2021 Author : Avanish (Payment Collection related changes) --
ALTER TABLE `ph_temp_payment_collection` ADD `admit_date` DATETIME NULL DEFAULT NULL AFTER `amount_received`;
ALTER TABLE `ph_temp_payment_collection` ADD `discharge_date` DATETIME NULL DEFAULT NULL AFTER `admit_date`;

-- 19-APRIL-2021 Author : Avanish (Batch No size increase from 15 to 40 related changes) --
ALTER TABLE `cycle_count` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `product_stock` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `product_stock_opening_audit_trial` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `product_stock_update` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `product_transaction` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `product_transaction_amendment` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `product_transaction_batch` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `temp_stock_ledger` CHANGE `batch_no` `batch_no` VARCHAR(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '-';

-- 22-APRIL-2021 Author : Avanish (Release Version related changes) --
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.17', '2021-04-22', 'SP48', '0', '');

-- Release Done on care4life and Mavjat --

-- 09-MAY-2021 Author : Avanish (Supplier Supply Type related changes) --
ALTER TABLE `suppliermaster` ADD `supplier_supply_type` ENUM('intra_state','inter_state') NOT NULL DEFAULT 'intra_state' AFTER `supplier_gstin_no`;

-- 09-MAY-2021 Author : Avanish (IGST related changes) --
ALTER TABLE `product_transaction` CHANGE `sgst_percentage` `sgst_percentage` FLOAT(6,2) NOT NULL DEFAULT '0.00';
ALTER TABLE `product_transaction` CHANGE `sgst_value` `sgst_value` FLOAT(9,2) NOT NULL DEFAULT '0.00' COMMENT 'sgst_value = taxable_amt * (gst_percentage / 100)';
ALTER TABLE `product_transaction` CHANGE `cgst_percentage` `cgst_percentage` FLOAT(6,2) NOT NULL DEFAULT '0.00';
ALTER TABLE `product_transaction` CHANGE `cgst_value` `cgst_value` FLOAT(9,2) NOT NULL DEFAULT '0.00' COMMENT 'cgst_value = taxable_amt * (gst_percentage / 100)';
ALTER TABLE `product_transaction` CHANGE `igst_percentage` `igst_percentage` FLOAT(6,2) NOT NULL DEFAULT '0.00';
ALTER TABLE `product_transaction` CHANGE `igst_value` `igst_value` FLOAT(9,2) NOT NULL DEFAULT '0.00' COMMENT 'igst_value = taxable_amt * (gst_percentage / 100)';

ALTER TABLE `product_transaction_amendment` CHANGE `sgst_percentage` `sgst_percentage` FLOAT(6,2) NOT NULL DEFAULT '0.00';
ALTER TABLE `product_transaction_amendment` CHANGE `sgst_value` `sgst_value` FLOAT(9,2) NOT NULL DEFAULT '0.00' COMMENT 'sgst_value = taxable_amt * (gst_percentage / 100)';
ALTER TABLE `product_transaction_amendment` CHANGE `cgst_percentage` `cgst_percentage` FLOAT(6,2) NOT NULL DEFAULT '0.00';
ALTER TABLE `product_transaction_amendment` CHANGE `cgst_value` `cgst_value` FLOAT(9,2) NOT NULL DEFAULT '0.00' COMMENT 'cgst_value = taxable_amt * (gst_percentage / 100)';
ALTER TABLE `product_transaction_amendment` CHANGE `igst_percentage` `igst_percentage` FLOAT(6,2) NOT NULL DEFAULT '0.00';
ALTER TABLE `product_transaction_amendment` CHANGE `igst_value` `igst_value` FLOAT(9,2) NOT NULL DEFAULT '0.00' COMMENT 'igst_value = taxable_amt * (gst_percentage / 100)';

ALTER TABLE `product_transaction_header` ADD `igst_amt` FLOAT(10,2) NULL DEFAULT '0.00' AFTER `sgst_amt`;
ALTER TABLE `product_transaction_header_amendment` ADD `igst_amt` FLOAT(10,2) NULL DEFAULT '0.00' AFTER `sgst_amt`;

ALTER TABLE `product_transaction_amendment` ADD `ref_trn_id` INT NOT NULL AFTER `bill_amendment`;
ALTER TABLE `product_transaction_header_amendment` ADD `purchase_order_entity_code` CHAR(6) NULL DEFAULT NULL AFTER `purchase_order_no`;

-- 14-MAY-2021 Author : Avanish (IGST related changes in PO) --
ALTER TABLE `purchase_order_detail` CHANGE `igst_percentage` `igst_percentage` FLOAT NOT NULL DEFAULT '0';
ALTER TABLE `purchase_order_detail_amendment` CHANGE `igst_percentage` `igst_percentage` FLOAT NOT NULL DEFAULT '0';

-- Release Done on care4life and Mavjat --
-- Also release done on SMVS and Kadi hospital --
-- mavajt and care4life release F4 functionality disable in salse, return and amendmend --

-- 08-JULY-2021 Author : Avanish (GST Exempt related changes in Sale and Sale Return) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_SALES_GST_EXEMPT', 'N', 'if value is Y, we will not Consider GST Percentage. If value is set to N, we will Consider GST.', 'N', '', '');

-- 11-JULY-2021 Author : Avanish (Purchase Detail display in Sale Screen related changes) --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_PURCHASE_DETAILS_DISPLAY_SALES', 'Y', 'if value is Y, we will display purchase details in popup using F4 Key. If value is set to N, we will disable this feature.', 'N', '', '');

-- Release done in all hospital no - 23 --

-- 17-JULY-2021 Author : Avanish (Single IP Pharmacy Bill related changes) --
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Single IP Pharmacy Bill', '', 'far fa-
circle', 'Item/SingleIpPharmacyBill', NULL, 'Y', 'thims', '2021-07-17 00:00:36', '', '0000-00-00 00:00:00');

ALTER TABLE `product_transaction` ADD `ref_trn_type` VARCHAR NULL DEFAULT NULL AFTER `bill_amendment`;
ALTER TABLE `product_transaction_amendment` ADD `ref_trn_type` VARCHAR(5) NULL DEFAULT NULL AFTER `bill_amendment`;

ALTER TABLE `product_transaction` ADD `ref_trn_date` DATE NULL DEFAULT NULL AFTER `ref_trn_id`;
ALTER TABLE `product_transaction_amendment` ADD `ref_trn_date` DATE NULL DEFAULT NULL AFTER `ref_trn_id`;

-- gautam db changes in release
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_HSN_CODE_SALES_BILL', 'Y', 'if value is Y, we will display HSN code details with name in sales bill. If value is set to N, we will disable th\r\nis feature.', 'N', '', '');
    ALTER TABLE `product_master` ADD `is_lasa` ENUM('Y','N') NULL DEFAULT 'N' AFTER `is_narcotic`;
    ALTER TABLE `product_master` ADD `is_highrisk` ENUM('Y','N') NULL DEFAULT 'N' AFTER `is_lasa`;

-- Release done in all hospital no 26
-- mavjat and smvs manually upload and other hospital git  through upload..
    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.26', '2021-09-08', 'SP55', '0', '');

-- 11-Sep-2021 Jayraj : barcode table add index due to performance issue --
ALTER TABLE `product_transaction_batch` ADD INDEX(`product_tracking_sr_no`);

-- 24-Sep-2021 Gautam :  rate master details add column for new discount feature on patient categeory--
ALTER TABLE `rate_master_detail` ADD `is_dis_fro_pro_mas` ENUM('Y','N') NOT NULL DEFAULT 'Y' AFTER `is_ph_ptr_applicable`;
ALTER TABLE `rate_master_detail` ADD `dis_for_ph_prod` INT NOT NULL DEFAULT '0' AFTER `is_dis_fro_pro_mas`;

-- 24-Sep-2021 Gautam :  suppliermaster table index add for performance--
ALTER TABLE `suppliermaster` ADD INDEX(`supplier_name`);

-- 28-Sep-2021 Gautam :  parameter table variable add--
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'ph_is_credit_for_only_cashless', 'N', 'if set to Y considered credit bill only available for insurance patient', 'N', '', '')

-- 28-Sep-2021 Gautam :  parameter table variable update--
UPDATE `parameter` SET `name` = 'PH_IS_CREDIT_BILL_FOR_ONLY_CASHLESS' WHERE `parameter`.`name` = 'ph_is_credit_bill_for_only_cashless';

-- 27-Sep-2021 Jayraj : release done
    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.27', '2021-09-27', 'SP56', '0', '');

-- 04-10-2021 Gautam : navgiven job categeory issue --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'HRMS', 'HR_EMP_ID_ALLOCATION', 'OVERALL', 'If value is overall then employeeID allocation done as per employee master and if value is categorywise then employeeID allocation done as per job_category_master.', 'N', 'O', 'OVERALL|CATEGORYWISE');

-- 04-10-2021 Gautam : summary table add --
summary table add

-- 04-10-2021 Gautam : employee_salary_head_master updated --

ALTER TABLE `employee_salary_head_master` ADD `head_code` VARCHAR(5) NOT NULL AFTER `head_id`;
ALTER TABLE `employee_salary_head_master` ADD `is_input_head` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `head_type`;
ALTER TABLE `employee_salary_head_master` ADD `recalculate` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `is_input_head`;
ALTER TABLE `employee_salary_head_master` ADD `is_standard_head` ENUM('Y','N') NOT NULL DEFAULT 'Y' AFTER `recalculate`;


-- 04-10-2021 Gautam :parameter max value readched problem  --
ALTER TABLE `parameter` CHANGE `id` `id` INT UNSIGNED NOT NULL AUTO_INCREMENT;


-- 05-10-2021 Gautam :gst percentage edit allowed on not in purchase return --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES ('256', 'Pharmacy', 'PH_IS_GST_BILL_EDIT_ALLOWED_IN_RETURN', 'N', 'if set to Y GST bill esit allowed at the time of product return', 'N', '', 'Y|N')


-- 2021-10-11 Jayraj : release note
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.29', '2021-10-11', 'SP58', '0', '');

-- 12-10-2021 Gautam :room detills print on pharmacy bill parameter --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'IS_ROOM_DETAIL_IN_PHA_BILL', 'Y', 'If Y then room no and other ip details of patient will be printed in pharmacy bill and if N then it will not be printed.', 'N', '', 'Y|N');

-- 21-10-2021 Gautam :job category error in hrms-payroll --
ALTER TABLE `job_category_master` ADD `emp_id_start` INT(11) NOT NULL AFTER `job_category_name`, ADD `emp_id_end` INT(11) NOT NULL AFTER `emp_id_start`, ADD `last_emp_id` INT(11) NOT NULL AFTER `emp_id_end`;

-- 24-10-2021 Gautam :leave rules table creation --
CREATE TABLE `leave_rules` (
 `rule_id` int NOT NULL AUTO_INCREMENT,
 `leave_type_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `leave_short_code` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `category` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `gender` enum('M','F','A') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `matirial_status` enum('S','M','A') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `department` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `count_per_year` int NOT NULL,
 `accrual_flag` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `accrue_from` enum('DOJ','DOC') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `accrue_frequency` enum('M','Y') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `accrre_when` enum('S','E') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `accrue_days` int NOT NULL,
 `accrue_frequency_year1` enum('M','Y') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `accrre_when_year1` enum('S','E') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `accrue_days_year1` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `carry_flag` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `carry_limit` int NOT NULL,
 `encash_limit` int NOT NULL,
 `allowed_in_probation` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `allowed_month` int NOT NULL,
 `week_days_between_leaves` enum('I','E') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `holidays_between_leaves` enum('I','E') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `maximum_in_month` int NOT NULL,
 `cont_days_allowed_Minimum` int NOT NULL,
 `cont_days_allowed_Maximum` int NOT NULL,
 `advance_allowed` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `backdated_allowed` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `backdated_upto_days` int NOT NULL,
 `partial_days_allowed` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `CreatedBy` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `CreatedDate` datetime NOT NULL,
 `LastModifiedBy` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `LastModifiedDate` datetime NOT NULL,
 PRIMARY KEY (`rule_id`),
 UNIQUE KEY `category` (`category`,`gender`,`matirial_status`,`department`)
) ENGINE=MyISAM

-- 24-10-2021 Gautam :`leave_entitlement_monthly` table creation --
CREATE TABLE `leave_entitlement_monthly` (
 `leave_entitlement_monthly_id` int NOT NULL AUTO_INCREMENT,
 `employee_id` int NOT NULL,
 `leave_month` date NOT NULL,
 `leave_type_id` tinyint(1) NOT NULL,
 `op_balance` tinyint NOT NULL,
 `new_leaves` tinyint NOT NULL,
 `leave_taken` smallint NOT NULL,
 `cl_balance` smallint NOT NULL,
 PRIMARY KEY (`leave_entitlement_monthly_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
asr backup and navigation add error

-- 24-10-2021 Gautam :`alter table `employee_leave_entitlement` --
ALTER TABLE `employee_leave_entitlement` ADD `month_up_to` DATE NOT NULL AFTER `leave_period_to`;

-- 24-10-2021 Gautam :` Added `supplier_payment_details` table --
CREATE TABLE `supplier_payment_details` (
 `Payment_ID` int NOT NULL AUTO_INCREMENT,
 `Trn_Type` tinytext NOT NULL,
 `Voucher_No` int NOT NULL,
 `Sr_No` int NOT NULL,
 `Bill_Amount_Paid` int NOT NULL,
 `Bill_Amount_Date` date NOT NULL,
 `Payment_Remark` varchar(100) NOT NULL,
 `trn_status` enum('C','T') NOT NULL,
 PRIMARY KEY (`Payment_ID`)
) ENGINE=MyISAM

-- 30-11-2021 Gautam :` added parameter `special_instruction_in_pharmacy_bill` --

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy Bill', 'special_instruction_in_pharmacy_bill', 'hello', 'special_instruction_in_pharmacy_bill', 'Y', '', 'Y|N');

-- 30-11-2021 Gautam :` added parameter `IP patient validity` --
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PHA_CREDIT_SALES_FOR_IP_PATIENT_VALIDITY', 'B', 'B for final bill and D for discharge to off credit bill for ip patient', 'Y', '', 'B|D');


-- 30-11-2021 Gautam :` chaged fill fo assets --
ALTER TABLE `repair_transaction` CHANGE `Repair_type` `Repair_type` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `repair_transaction` ADD `Ass_type` VARCHAR(15) NOT NULL AFTER `supplier_id`;

18-02-2022: on package release all leave related query
ALTER TABLE `leave_rules` DROP INDEX `category`;
ALTER TABLE `leave_rules` DROP COLUMN `department`;
ALTER TABLE `leave_rules` DROP COLUMN `backdated_allowed`;
ALTER TABLE `leave_rules` DROP COLUMN `carry_flag`
ALTER TABLE `leave_rules` CHANGE `allowed_month` `Allowed_ater_months` INT NOT NULL;
ALTER TABLE `leave_rules` ADD UNIQUE( `leave_type_id`, `category`, `gender`, `matirial_status`);
ALTER TABLE `leave_type_master` ADD `Code` VARCHAR(4) NOT NULL AFTER `leave_type_name`;
ALTER TABLE `leave_entitlement_monthly` CHANGE `op_balance` `op_balance` FLOAT NOT NULL, CHANGE `new_leaves` `new_leaves` FLOAT NOT NULL, CHANGE `leave_taken` `leave_taken` FLOAT NOT NULL, CHANGE `cl_balance` `cl_balance` FLOAT NOT NULL;

18-02-2022: parametr added for barcode base sale return or direct
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_SALES_RETURN_WITH_BARCODE', 'N', 'if Y then sale return only work on barcode and will not allow to enter medicine manually.', 'N', '', 'Y|N');

18-02-2022: supplier format for auto grn bill upload
ALTER TABLE `suppliermaster` ADD `supplier_bill_format` VARCHAR(50) NOT NULL AFTER `supplier_supply_type`;


-- jayraj
ALTER TABLE `productgroup_master` CHANGE `productgroup_name` `productgroup_name` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `product_master` CHANGE `subcategory` `subcategory` VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;


INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.38', '2022-02-21', 'SP66', '0', '');


INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.40', '2022-03-15', 'SP69', '0', '');

-- Prince Patel
CREATE TABLE `cycle_count_header` ( `cc_trn_no` INT NOT NULL , `str_code` VARCHAR(6) NOT NULL , `product_wise` ENUM('Y','N') NULL , `product_id` INT NULL , `category_wise` ENUM('Y','N') NULL , `product_category` INT NULL , `ptr_from` FLOAT NULL , `ptr_to` FLOAT NULL , `product_range_from` VARCHAR(6) NULL , `product_range_to` VARCHAR(6) NULL , `product_with_zero_qty` ENUM('Y','N') NOT NULL , `system_stock_amt` FLOAT NOT NULL , `physical_stock_amt` FLOAT NULL , `total_product_count` INT NOT NULL , `created_by` VARCHAR(10) NOT NULL , `created_date` DATETIME NOT NULL , `lastmodified_by` VARCHAR(10) NULL , `lastmodified_date` DATETIME NULL , PRIMARY KEY (`cc_trn_no`)) ENGINE = MyISAM;

INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'PH_CYCLE_BATCH_FOR_MONTHS', '24', 'For cycle count, all product batches older than mo...', 'N', '', '');

ALTER TABLE `employee_master` CHANGE `salary_type` `salary_type` ENUM('ctc','per_hour','per_day','per_visit','mg','stipend') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'ctc';

ALTER TABLE `employee_salary_details` ADD `salary_type` ENUM('ctc','per_hour','per_day','per_visit','mg','stipend') CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL DEFAULT 'ctc' AFTER `job_title_id`;

ALTER TABLE `employee_master` ADD `comp_off` ENUM('Yes','No') NOT NULL DEFAULT 'No' COMMENT 'Compensatory Off or Overtime Allowed' AFTER `ctc`;

ALTER TABLE `employee_daily_attendance` ADD `shift_id` VARCHAR(5) NOT NULL AFTER `attendance_date`;

ALTER TABLE `employee_daily_attendance` CHANGE `created_date` `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `lastmodified_date` `lastmodified_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `employee_daily_attendance` CHANGE `attendance_flag` `attendance_flag` VARCHAR(50) NOT NULL;

ALTER TABLE `employee_daily_time_correction` ADD `ot_hours` TIME NOT NULL AFTER `approved_by`, ADD `ot_hours_approved` TIME NOT NULL AFTER `ot_hours`, ADD `compensation_status` ENUM('R','A') NOT NULL AFTER `ot_hours_approved`;

DROP TABLE IF EXISTS `employee_holiday_eligibility`;
CREATE TABLE IF NOT EXISTS `employee_holiday_eligibility` (
  `year` int NOT NULL,
  `total_holidays` tinyint NOT NULL,
  `fixed_holidays` tinyint NOT NULL,
  `optional_holidays` tinyint NOT NULL,
  `CreatedBy` varchar(10) NOT NULL,
  `CreatedDate` datetime NOT NULL,
  `LastModifiedBy` varchar(10) NOT NULL,
  `LastModifiedDate` datetime NOT NULL
);

DROP TABLE IF EXISTS `employee_optional_leaves`;
CREATE TABLE IF NOT EXISTS `employee_optional_leaves` (
  `employee_id` mediumint NOT NULL,
  `optional_leave_date` date NOT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`employee_id`,`optional_leave_date`)
);

ALTER TABLE `employee_timestamps` ADD `shift_date` DATE NOT NULL AFTER `time`, ADD `in_out_flag` ENUM('i','o') NOT NULL AFTER `shift_date`;

ALTER TABLE `employee_timestamps` CHANGE `modified_time` `modified_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';

ALTER TABLE `employee_master` ADD `ABRY` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `comp_off`;



-- jayraj script for update net rate
SELECT * FROM `product_transaction_header` WHERE `trn_type` LIKE 'P' AND `with_po` = 'Y' ORDER BY `trn_id` DESC

if found record in select qry then update record

UPDATE `product_transaction` SET net_rate = (price * quantity)/(quantity+free_quantity) WHERE `trn_type` = 'P' AND free_quantity > 0 AND price <= net_rate;
SELECT * FROM `product_transaction` WHERE `trn_type` = 'P' AND free_quantity > 0 AND price <= net_rate;


-- 2022-04-19 jayraj : cycle report, list add in navigation

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Cycle Report', '', 'far fa-circle', 'Item/cycleReportInput', NULL, 'Y', 'thims', '2019-03-08 15:13:57', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Cycle List', '', 'far fa-circle', 'Item/cycleList', NULL, 'Y', 'thims', '2019-03-08 15:13:57', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Cycle Cancel', '', 'far fa-circle', 'Item/cycleCancellationInput', NULL, 'Y', 'thims', '2019-03-08 15:13:57', '', '0000-00-00 00:00:00');

-- 28-4-22 Prince Patel
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`) VALUES (NULL, 'Pharmacy', 'PHA_IND_QTY_CONTROL', 'Y', 'Quantity not higher than Indent Quantity flag in Pharmacy');


-- 02-05-22 Prince Patel
ALTER TABLE `employee_salary_head_rules` CHANGE `value_formula_global` `value_formula_global` VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL;

-- 18-5-22 Prince Patel
ALTER TABLE `employee_monthly_salary_summary` ADD `is_hours_updated` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_head_updated`;

-- 20-5-22 Prince Patel
ALTER TABLE `employee_monthly_salary_header` ADD `calculated_overtime_hours` FLOAT(9,2) NOT NULL DEFAULT '0.00' AFTER `overtime_hours`;

-- Prince Patel 25-5-22
INSERT INTO `parameter`(`module`, `name`, `value`, `remark`, `customer_access`) VALUES ('Super Admin','Hours_For_Compoff','8','Hour calculation for Compensatory Off','N')


-- Prince Patel 30-5-22 MRD Module
INSERT INTO `general_code_master` (`master_cd`, `master_description`, `active`, `short_code`, `language`, `calculation`, `gm_order`, `gm_shortcode`, `gm_custom_field`) VALUES ('MRDOCS', 'MR Document Master', 'yes', 'MRDOC', 'N', 'N', 'Y', 'N', 'N');

UPDATE `general_code_master` SET `gm_order` = 'Y' WHERE `general_code_master`.`master_cd` = 'MRDOCS';

CREATE TABLE IF NOT EXISTS `mrd_details` (
  `Fin_Year` int NOT NULL AUTO_INCREMENT,
  `IPID` int NOT NULL,
  `document_id` varchar(6) NOT NULL COMMENT 'Reference of general_master table',
  `no_of_page` smallint NOT NULL,
  `prepared_by` varchar(11) NOT NULL,
  `verified` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `verified_by` varchar(11) NOT NULL,
  `created_by` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `lastmodified_date` datetime NOT NULL,
  PRIMARY KEY (`Fin_Year`,`IPID`),
  KEY `gm_code` (`document_id`)
);

-- Prince Patel 7-6-22
ALTER TABLE `suppliermaster` ADD `drug_licence_no` VARCHAR(30) NOT NULL AFTER `supplier_supply_type`;


-- release no : 47
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.47', '2022-06-17', 'SP76', '0', '');

ALTER TABLE `ph_temp_payment_collection` ADD `patient_type` VARCHAR(50) NOT NULL AFTER `patient_name`;

-- release no : 48
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.48', '2022-07-14', 'SP77', '0', '');

-- Prince Patel 5-8-22
INSERT INTO `parameter` (`module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES ('Pharmacy', 'PH_PURCHASE_NET_AMT_INPUT_SKIP', 'N', 'If value is Yes then purchase transition can be submitted without entering  net amount.', 'N', '', '');


-- 2022-09-06 : Jayraj
ALTER TABLE `ph_temp_payment_collection` ADD `mediclaim` VARCHAR(5) NOT NULL AFTER `patient_name`;
ALTER TABLE `ph_temp_payment_collection` ADD `cashless` VARCHAR(10) NOT NULL AFTER `mediclaim`;


-- 2022-08-30 : Prince
INSERT INTO `parameter` (`id`, `module`, `name`, `value`, `remark`, `customer_access`, `input_type`, `options`) VALUES(318, 'Pharmacy', 'special_instruction_in_pharmacy_bill2', 'Medicines once sold can be returned within one month from the date of purchase with the original bill only. No cut strip or used strip will be returned.', 'Display Medicines Return notices on all pharmacy bill.', 'N', '', '');

-- 2022-09-08 : jayraj
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.50', '2022-09-08', 'SP78', '0', '');

-- 2022-09-15 : Prince
ALTER TABLE `purchase_order_header` ADD `purchase_order_type` ENUM('NPO','RPO') NOT NULL AFTER `po_date`, ADD `po_valid_upto` DATE NOT NULL AFTER `purchase_order_type`;

-- 2022-09-20 : Prince
ALTER TABLE `employee_salary_head_master` ADD `is_acitve` ENUM('Y','N') NOT NULL DEFAULT 'Y' AFTER `print_sequence`;


-- 23-11-22 : Prince
ALTER TABLE `employee_master` ADD `business_entity` CHAR(2) NOT NULL DEFAULT 'H' AFTER `employee_department_id`, ADD INDEX (`business_entity`);

INSERT INTO `parameter` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `value`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Super Admin', '', '', 'Multiple_Hospital_Entity', 'N', 'Value Y then we will allow multiple Hospital  Entity For PayRoll.', '', '', 'N', '', '');

-- 24-11-22 : Prince

ALTER TABLE `job_category_master` ADD `business_entity_code` CHAR(2) NOT NULL DEFAULT 'H' AFTER `job_category_id`, ADD INDEX (`business_entity_code`);

-- 28-11-22 : Prince
ALTER TABLE `job_category_master` DROP INDEX `job_category_name`, ADD UNIQUE `job_category_name` (`job_category_name`, `business_entity_code`) USING BTREE;
ALTER TABLE `business_entity_master` ADD `emp_id_start` INT NULL AFTER `remarks`, ADD `emp_id_end` INT NULL AFTER `emp_id_start`, ADD `last_emp_id` INT NULL AFTER `emp_id_end`;
ALTER TABLE `business_entity_master` ADD `payroll_applicable` ENUM('Y','N') NOT NULL AFTER `code`;
ALTER TABLE `business_entity_master` CHANGE `payroll_applicable` `payroll_applicable` ENUM('Y','N') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N';

-- 8-2-23: Prince
ALTER TABLE `product_master` ADD `is_schedule_h1` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `lastmodified_date`;

-- 10-2-23: Prince
ALTER TABLE `product_master` CHANGE `is_schedule_h1` `is_schedule` VARCHAR(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

-- 20-2-23: Prince
INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('GCR', '', '2223', '0');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'GST Credit Note', '', 'far fa-circle', 'Item/GSTCreditnote', NULL, 'Y', 'thims', '2023-03-06 18:18:00', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'GST Credit Note Report', '', 'far fa-circle', 'Item/GSTCreditNoteReportInput', NULL, 'Y', 'thims', '2023-03-06 18:18:00', '', '0000-00-00 00:00:00');

-- 24-3-23: Prince

ALTER TABLE `leave_rules` ADD `accrue_frequency_when` ENUM('LP','J') NOT NULL DEFAULT 'LP' AFTER `accrue_frequency`;
ALTER TABLE `leave_rules` CHANGE `accrue_frequency_when` `accrue_frequency_when` ENUM('LP','J') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'LP';


-- 11-4-23: Prince
ALTER TABLE `leave_rules` ADD `carry_forward` ENUM('M','Y') NOT NULL DEFAULT 'Y' AFTER `carry_limit`;
ALTER TABLE `leave_entitlement_monthly` ADD `entitlement_laps` FLOAT NOT NULL AFTER `cl_balance`;

INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.58', '2023-04-17', 'SP87', '0', '');


-- 2023-05-24 : Jayraj 

    --  ----- run in provider Database

    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'GSTR Report', 'Place Of Supply For Pharmacy Sales', 'PLACE_OF_SUPPLY_FOR_PHARMACY_SALES', 'Add state name with code', '', '', 'N', 'T', '');

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

    INSERT INTO `parameter` (`id`,  `name`, `value`) VALUES (NULL, 'PLACE_OF_SUPPLY_FOR_PHARMACY_SALES', '24-Gujarat');


-- 2023-05-26 : Jayraj , release no 59 

    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.59', '2023-05-24', 'SP88', '0', '');


-- 2023-06-26 : Jayraj 

ALTER TABLE `purchase_order_header` ADD `approval_remark` TEXT NOT NULL AFTER `terms_conditions`;
ALTER TABLE `purchase_order_header` CHANGE `approval_remark` `approval_remark` TEXT NULL;

-- ----------------- add in provider database
INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'Sale', 'Hospital Internal Sale Margin On PTR', 'PH_MRGRT_ON_PTR_FOR_HOSP_INTERNAL_SALE', 'Add Margin in Percentage', '', '', 'N', 'T', '');

-- ------------------------
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'Pharmacy', 'PH_MRGRT_ON_PTR_FOR_HOSP_INTERNAL_SALE', '0');


    CREATE TABLE `template_inventory` (
    `inv_template_id` int NOT NULL AUTO_INCREMENT,
    `inv_template_type` varchar(10) NOT NULL,
    `inv_template_name` varchar(25) NOT NULL,
    `inv_template_content` text NOT NULL,
    `is_active` enum('N','Y') NOT NULL DEFAULT 'Y',
    `created_by` varchar(15) NOT NULL,
    `created_date` datetime NOT NULL,
    `lastmodified_by` varchar(15) NOT NULL,
    `lastmodified_date` datetime NOT NULL,
    PRIMARY KEY (`inv_template_id`)
   ) ENGINE=MyISAM ;


-- 2023-07-10 : Jayraj : Release done : No 60
    
    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.60', '2023-07-10', 'SP90', '0', '');


-- 2023-07-20 : Jayraj
INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('DT', '', '2324', '0');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Discard Stock', '', 'far fa-circle', 'Item/stockDiscard', NULL, 'Y', 'thims', '2023-03-06 18:18:00', '', '0000-00-00 00:00:00');


-- 2023-07-27 : Jayraj
ALTER TABLE `purchase_order_header` ADD `is_fully_executed` ENUM('Y','N','P') NOT NULL DEFAULT 'N' COMMENT 'Y- Fully executed, N-Not excecute, P - Partial execute' AFTER `net_taxable_amt`;

ALTER TABLE `product_master` ADD `product_requested_by` VARCHAR(20) NOT NULL AFTER `is_consumable`;
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Stock Statement - Requested By', '', 'far fa-circle', 'StoreItem/storeStockStatementReqByInput', NULL, 'Y', 'thims', '2023-03-06 18:18:00', '', '0000-00-00 00:00:00');


-- 2023-08-01 : Jayraj

INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.61', '2023-08-01', 'SP91', '0', '');

-- 2023-08-01 : Jayraj

    ALTER TABLE `temp_nonmoving_products` ADD `product_requested_by` VARCHAR(20) NOT NULL AFTER `last_sale_duration`;

    -- add stored proc for change in non moving report > add requested by column

-- 2023-08-21 : JAYRAJ

    ALTER TABLE `suppliermaster` ADD `supplier_type` ENUM('GEN','PHA') NOT NULL COMMENT 'PHA - Pharmacy, GEN - General Store' AFTER `drug_licence_no`;
    ALTER TABLE `manufacturer_master` ADD `manufacturer_type` ENUM('GEN','PHA') NOT NULL COMMENT 'PHA - Pharmacy, GEN - General Store' AFTER `manufacturer_website_url`;


-- 2023-08-28 : Jayraj 

INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.62', '2023-08-28', 'SP92', '0', '');

-- 2023-09-15 : Jayraj

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Sales Return Free Hand Entry', '', 'far fa-circle', 'Item/issueReturnFreeHand', NULL, 'Y', 'thims', '2023-03-06 18:18:00', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Sales Return Amendment', '', 'far fa-circle', 'Item/issueReturnBillAmendment', NULL, 'Y', 'thims', '2023-03-06 18:18:00', '', '0000-00-00 00:00:00');


-- 2023-09-19 : Nandini

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Amendment Report', '', 'far fa-circle', 'Item/SalesAmendmentView', NULL, 'Y', 'thims', '2023-03-06 18:18:00', '', '0000-00-00 00:00:00');

--2023-10-06 : Prince
ALTER TABLE `leave_entitlement_monthly` ADD INDEX(`leave_month`);
ALTER TABLE `employee_leave_entitlement` ADD INDEX(`employee_id`);
ALTER TABLE `employee_leave_entitlement` DROP INDEX `employee_id`, ADD INDEX `employee_id` (`employee_id`, `leave_type_id`, `leave_period_from`, `leave_period_to`) USING BTREE;
ALTER TABLE `leave_entitlement_monthly` ADD INDEX(`employee_id`);
ALTER TABLE `leave_entitlement_monthly` DROP INDEX `employee_id`, ADD INDEX `employee_id` (`employee_id`, `leave_type_id`, `leave_month`) USING BTREE;
ALTER TABLE `leave_entitlement_monthly` CHANGE `entitlement_laps` `entitlement_laps` FLOAT NOT NULL COMMENT 'The opening balance is calculated after reducing these laps days. So, it is for information. ';
ALTER TABLE `employee_leave_entitlement` ADD `entitlement_laps` FLOAT NOT NULL DEFAULT '0' COMMENT 'The entitlements is calculated after reducing these laps days. So, it is for information.' AFTER `leave_taken`;
ALTER TABLE `leave_rules` CHANGE `category` `category` VARCHAR(15) NULL DEFAULT NULL COMMENT 'We are not using this column. (7-9-23)';
ALTER TABLE `employee_daily_time_correction` CHANGE `compensation_status` `compensation_status` ENUM('R','A','C','N') NOT NULL DEFAULT 'N' COMMENT 'R- Request,\r\nA- Approved,\r\nC- Rejected,\r\nN- No Request';



-- 2023-10-05 : Jayraj, Pharmacy Supplier master supplier type update script

    UPDATE `suppliermaster` SET supplier_type = 'PHA' WHERE `supplier_id` IN (SELECT supplier_id FROM `product_transaction_header` WHERE `trn_type` LIKE 'P' GROUP BY supplier_id);


-- 2023-10-05 : Jayraj, Release Done No 63

    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.63', '2023-10-06', 'SP93', '0', '');

-- 2023-10-10 : Nandini, Parameter setup fyear wise Voucher_No
    
    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'PHA Voucher Order No Fyear Wise', 'PHA_VOUCHER_ORDER_NO_FYEAR_WISE', 'If value is yes then Voucher No will be generated Fyear wise', '', '', 'N', 'D', 'Y,N');
    
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'PHA_VOUCHER_ORDER_NO_FYEAR_WISE', 'N');

-- 2023-10-30 : Nandini, Parameter setup for show Discount in Sales Bill
    
    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'Sales Bill Discount Column Visible', 'SALES_BILL_DISCOUNT_COL_VISIBLE', 'If value is yes then Discount Rate and Value will be shown upon printing Sales Bill', '', '', 'N', 'D', 'Y,N');
    
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'SALES_BILL_DISCOUNT_COL_VISIBLE', 'Y');

-- 2023-11-02 : Nandini, Added created, modified details columns to supplier_payment_details table
    
    ALTER TABLE `supplier_payment_details` ADD `created_by` VARCHAR(10) NOT NULL AFTER `trn_status`, ADD `created_date` DATETIME NOT NULL AFTER `created_by`, ADD `lastmodified_by` VARCHAR(10) NOT NULL AFTER `created_date`, ADD `lastmodified_date` DATETIME NOT NULL AFTER `lastmodified_by`;

-- 2023-11-21 : Jayraj , supplier master add common supplier type

    ALTER TABLE `suppliermaster` CHANGE `supplier_type` `supplier_type` ENUM('GEN','PHA','CMN') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT 'PHA - Pharmacy, GEN - General Store, CMN - Common';

    UPDATE `product_master` SET generic_drug_id = NULL WHERE generic_drug_id = 0;

    ALTER TABLE `product_reorder_request` DROP PRIMARY KEY;
    ALTER TABLE `product_reorder_request` ADD INDEX(`store_code`, `product_id`, `reorder_request_date`);

-- 2023-11-28

    ALTER TABLE `product_transaction` CHANGE `price_per_unit_suom` `price_per_unit_suom` FLOAT(11,4) NOT NULL COMMENT 'Price Per Unit based on stock unit of measure';
    ALTER TABLE `product_transaction` CHANGE `price_per_unit_suom_wo_gst` `price_per_unit_suom_wo_gst` FLOAT(11,4) NOT NULL DEFAULT '0.0000';
    ALTER TABLE `product_transaction` CHANGE `price` `price` FLOAT(11,4) NULL DEFAULT '0.0000' COMMENT 'For trn_type=\'P\' its price for PUOM and for trn_type=\'I\' it will be kept 0 ';

-- 2023-11-30 : Nandini, Increased limit of generic_drug_name to 250 in generic_drug_master

    ALTER TABLE `generic_drug_master` CHANGE `generic_drug_name` `generic_drug_name` VARCHAR(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

-- 2023-12-01 : Jayraj Release Done No 65

    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.65', '2023-12-01', 'SP95', '0', '');

-- 2-11-23: Prince
    
    ALTER TABLE `leave_rules` ADD `accrue_days_apply_year1` ENUM('F','P','M') NOT NULL DEFAULT 'M' COMMENT 'This column is used to apply accrue days for first year F means Full P means Provisioning and M means Manually' AFTER `accrue_days_year1`;

    ALTER TABLE `leave_rules` CHANGE `accrue_days_apply_year1` `accrue_days_apply_year1` ENUM('F','P','M') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'M' COMMENT 'This column is used to apply accrue days for first year and applicable only when the accrual is yearly. F means Full P means proportionately and M means Manually';

-- 2023-11-30 : Nandini, Added Trn_Fyear column in supplier_payment_details

    ALTER TABLE `supplier_payment_details` ADD `Trn_Fyear` SMALLINT NOT NULL AFTER `Payment_ID`;


-- 2023-12-18 : Jayraj, add PAN No column in business entity master

    ALTER TABLE `business_entity_master` ADD `pan_no` VARCHAR(10) NOT NULL AFTER `CIN_NO`;
    ALTER TABLE `business_entity_master` CHANGE `name` `name` VARCHAR(100) NOT NULL;
    ALTER TABLE `business_entity_master` ADD `tan_no` VARCHAR(10) NOT NULL AFTER `pan_no`;

-- 2023-12-12 : Nandini, Added ref_trn_type and ref_trn_fyear in Product Transaction tables and Product Transaction Amendment tables
    
    ALTER TABLE `product_transaction_header` ADD `ref_trn_type` VARCHAR(10) NOT NULL AFTER `bill_amendment`, ADD `ref_trn_fyear` SMALLINT NOT NULL AFTER `ref_trn_type`;

    ALTER TABLE `product_transaction` ADD `ref_trn_fyear` SMALLINT NOT NULL AFTER `ref_trn_type`;

    ALTER TABLE `product_transaction_header_amendment` ADD `ref_trn_type` VARCHAR(10) NOT NULL AFTER `bill_amendment`, ADD `ref_trn_fyear` SMALLINT NOT NULL AFTER `ref_trn_type`;

    ALTER TABLE `product_transaction_amendment` ADD `ref_trn_fyear` SMALLINT NOT NULL AFTER `ref_trn_type`;

-- 19-12-23: Prince Patel

    ALTER TABLE employee_daily_time_correction ADD leave_date VARCHAR(30) NOT NULL AFTER ot_hours_approved, ADD ot_days_pending_for_comp_off VARCHAR(5) NOT NULL AFTER leave_date;

    ALTER TABLE employee_master ADD Hours_For_Compoff VARCHAR(5) NOT NULL DEFAULT '8' AFTER comp_off;

    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`,`CreatedBy`,`CreatedDate`) VALUES (NULL, 'HRMS', 'HRMS', 'comp_off_within_months', 'comp_off_within_months', ' ', '', '', 'N', 'T', '','thims','2023-01-03');

    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'HRMS', 'comp_off_within_months', '8');

-- 2024-01-08 : Jayraj, Release Done No : 66

	INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.66', '2023-01-08', 'SP96', '0', '');


INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Manufacturer Wise - Purchase Detail', '', 'far fa-
circle', 'manufactur/productWiseReportInput', NULL, 'Y', 'thims', '2020-08-28 00:00:36', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Doctor And Patient Wise - Sales Report', '', 'far fa-
circle', 'doctorWise/patientSalesReportInput', NULL, 'Y', 'thims', '2020-08-28 00:00:36', '', '0000-00-00 00:00:00');

-- 2024-01-23 : Nandini, Added emergency column in product_master

    ALTER TABLE `product_master` ADD `is_emergency` ENUM('Y', 'N') NOT NULL DEFAULT 'N' AFTER `is_highrisk`;

-- 2024-01-22 : Nandini, Added General Store Supplier Payment

    INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,
    `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'General Store Supplier Payment', '', 'far fa-
    circle', 'StoreItem/GeneralStoreSupplierPayment', NULL, 'Y', 'thims', '2020-08-28 00:00:36', '', '0000-00-00 00:00:00');

-- 2024-01-30 : Prince

    ALTER TABLE `employee_master` CHANGE `employee_father_name` `employee_father_name` VARCHAR(35)  NULL DEFAULT NULL;

    ALTER TABLE `product_ebill_format_master` ADD `datarow` INT NOT NULL AFTER `column_header`;
    ALTER TABLE `product_ebill_format_master` ADD `footer_present` ENUM('Y','N') NOT NULL AFTER `column_header`;
    ALTER TABLE `product_ebill_format_master` ADD `headerrow` INT NOT NULL AFTER `footer_present`;
    ALTER TABLE `product_ebill_format_master` ADD `Identifiers` VARCHAR(6) NOT NULL AFTER `saperator`;

    CREATE TABLE IF NOT EXISTS `product_ebill_transaction_header_tmp` (
    `trn_fyear` smallint NOT NULL COMMENT 'Transaction Financial Year',
    `store_code` varchar(6) NOT NULL,
    `to_store_code` varchar(6) DEFAULT NULL,
    `trn_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return',
    `trn_id` int NOT NULL,
    `bill_amendment` smallint NOT NULL DEFAULT '0',
    `ref_trn_id` int NOT NULL,
    `receipt_id` int DEFAULT NULL,
    `trn_date` date NOT NULL COMMENT 'Transaction Date',
    `trn_status` char(1) NOT NULL COMMENT 'P - Partial, C - Completed, T - Turned Down (Cancelled)',
    `with_po` enum('Y','N') NOT NULL DEFAULT 'N',
    `purchase_bill_no` varchar(15) DEFAULT NULL COMMENT 'Purchase Bill Number',
    `purchase_order_no` varchar(15) DEFAULT NULL,
    `purchase_order_entity_code` char(6) DEFAULT NULL,
    `purchase_bill_date` date DEFAULT NULL COMMENT 'Purchase Bill Date',
    `supplier_id` smallint UNSIGNED DEFAULT NULL,
    `department_id` tinyint DEFAULT NULL,
    `doctor_id` int DEFAULT NULL COMMENT 'Referring external_doctor_master',
    `patient_id` int DEFAULT NULL COMMENT 'Medicine Issue & Issue Return Transaction has patient information if Pharmacy module is integrated',
    `opd_reg_id` int DEFAULT NULL COMMENT 'Medicine Issue & Issue Return Transaction has patient information if Pharmacy module is integrated',
    `ipd_reg_id` int DEFAULT NULL COMMENT 'Medicine Issue & Issue Return Transaction has patient information if Pharmacy module is integrated',
    `visit_id` int DEFAULT NULL,
    `ph_cust_id` int DEFAULT NULL,
    `gross_total` float(11,2) NOT NULL COMMENT 'Roll up from child items stored in transaction table',
    `discount_type` enum('A','P') DEFAULT NULL COMMENT 'P - Percentage, A - Amount',
    `discount` float(10,2) DEFAULT NULL COMMENT 'discount applied',
    `discount_amt` float(10,2) NOT NULL COMMENT 'Stores calculated discount amount',
    `round_off` float(5,2) NOT NULL,
    `amount_received` float(10,2) NOT NULL DEFAULT '0.00',
    `is_fully_paid` enum('Y','N') NOT NULL DEFAULT 'Y',
    `is_credit_bill` enum('Y','N') DEFAULT 'N',
    `is_ptr_applicable` enum('N','Y') NOT NULL DEFAULT 'N',
    `is_consignment_dc` enum('Y','N') NOT NULL DEFAULT 'N',
    `is_final_bill_prepared` enum('Y','N') NOT NULL DEFAULT 'N',
    `shipping_other_charges` float(10,2) NOT NULL DEFAULT '0.00',
    `sch_amount` float(9,2) NOT NULL DEFAULT '0.00',
    `tcs_amount` float(9,2) NOT NULL DEFAULT '0.00',
    `total` float(10,2) NOT NULL COMMENT 'Net Total',
    `cgst_amt` float(10,2) DEFAULT '0.00',
    `sgst_amt` float(10,2) DEFAULT '0.00',
    `igst_amt` float(10,2) DEFAULT '0.00',
    `invoice_amt` float(11,2) DEFAULT '0.00',
    `remarks` varchar(50) NOT NULL COMMENT 'Remarks, if any. Mandatory if return transaction type',
    `net_taxable_amt` float(11,2) DEFAULT '0.00',
    `is_exported_to_xml` enum('Y','N') NOT NULL DEFAULT 'N',
    `payment_mode` varchar(10) NOT NULL DEFAULT 'CSH',
    `payment_agency` varchar(30) DEFAULT NULL,
    `payment_number` varchar(30) DEFAULT NULL,
    `created_by` varchar(10) NOT NULL,
    `created_date` datetime NOT NULL,
    `lastmodified_by` varchar(10) DEFAULT NULL,
    `lastmodified_date` datetime DEFAULT NULL,
    PRIMARY KEY (`trn_fyear`,`store_code`,`trn_type`,`trn_id`),
    KEY `FK_suppliermaster` (`supplier_id`),
    KEY `trn_date` (`trn_date`),
    KEY `trn_type` (`trn_type`,`trn_id`)
    );

    CREATE TABLE IF NOT EXISTS `product_ebill_transaction_tmp` (
    `trn_fyear` smallint NOT NULL COMMENT 'Transaction Financial Year',
    `trn_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return ',
    `trn_id` int NOT NULL,
    `trn_sr_no` smallint NOT NULL,
    `bill_amendment` smallint NOT NULL DEFAULT '0',
    `ref_trn_type` varchar(5) NOT NULL,
    `ref_trn_id` int NOT NULL,
    `ref_trn_date` date NOT NULL,
    `indent_id` int DEFAULT NULL,
    `product_id` int UNSIGNED NOT NULL COMMENT 'product_master - product_id',
    `batch_no` varchar(40) DEFAULT NULL,
    `product_tracking_sr_no` varchar(25) DEFAULT NULL,
    `batch_exp` date DEFAULT NULL COMMENT 'To store batch expiry month and year (it will store date as 1st of that given month)',
    `purchase_uom` tinyint UNSIGNED NOT NULL DEFAULT '1',
    `quantity` float NOT NULL,
    `free_quantity` float DEFAULT '0',
    `quantity_per_unit_suom` float NOT NULL,
    `return_quantity` float DEFAULT '0',
    `sch_amt` float(9,2) DEFAULT '0.00' COMMENT 'Scheme Amount entered by user',
    `original_mrp` float(9,2) DEFAULT '0.00',
    `mrp` float(9,2) DEFAULT '0.00' COMMENT 'For trn_typ=''P'' its mrp entered by user, for trn_type=''I'' its mrp_per_unit',
    `price` float(9,4) DEFAULT '0.0000' COMMENT 'For trn_type=''P'' its price for PUOM and for trn_type=''I'' it will be kept 0 ',
    `price_per_unit_suom` float(9,4) NOT NULL COMMENT 'Price Per Unit based on stock unit of measure',
    `price_per_unit_suom_wo_gst` float(9,4) NOT NULL DEFAULT '0.0000',
    `is_reorder_required` enum('Y','N') NOT NULL DEFAULT 'N',
    `ledger_type` enum('C','D') NOT NULL,
    `discount_type` enum('A','P') DEFAULT NULL COMMENT 'P - Percentage, A - Amount',
    `discount` float(9,2) DEFAULT NULL,
    `discount_amt` float(9,2) NOT NULL COMMENT 'IF discount_type = P     discount_amt = net_rate * (Discount%/100) If discount_type = A     discount_amt = Discount',
    `taxable_amt` float(9,2) DEFAULT '0.00' COMMENT '((price * qty) - sch_amt) - discount where discount is to be calculated based on discount type',
    `net_rate` float(9,2) DEFAULT '0.00' COMMENT 'net_rate = taxable_amt/quantity',
    `sgst_percentage` float(6,2) NOT NULL DEFAULT '0.00',
    `sgst_value` float(9,2) NOT NULL DEFAULT '0.00' COMMENT 'sgst_value = taxable_amt * (gst_percentage / 100)',
    `cgst_percentage` float(6,2) NOT NULL DEFAULT '0.00',
    `cgst_value` float(9,2) NOT NULL DEFAULT '0.00' COMMENT 'cgst_value = taxable_amt * (gst_percentage / 100)',
    `igst_percentage` float(6,2) NOT NULL DEFAULT '0.00',
    `igst_value` float(9,2) NOT NULL DEFAULT '0.00' COMMENT 'igst_value = taxable_amt * (gst_percentage / 100)',
    `total` float(10,2) NOT NULL DEFAULT '0.00' COMMENT 'For trn_type = ''P'' => total = price * quantity For trn_type = ''I'' => (price * quantity) - discount',
    `landing_rate` float(9,2) DEFAULT '0.00' COMMENT 'For trn_type=''P=> landing_cost/quantity, For trn_type=''I'' 0',
    `landing_cost` float(10,2) DEFAULT '0.00' COMMENT 'For trn_type=''P'' => taxable_amt + GST, For trn_type = ''I'' 0',
    `created_by` varchar(10) NOT NULL,
    `created_date` datetime NOT NULL,
    `lastmodified_by` varchar(10) DEFAULT NULL,
    `lastmodified_date` datetime DEFAULT NULL,
    UNIQUE KEY `UK_TransactionNo` (`trn_fyear`,`trn_type`,`trn_id`,`trn_sr_no`),
    KEY `FK_product_master_id` (`product_id`),
    KEY `FK_unitmaster_trnpom` (`purchase_uom`),
    KEY `trn_type` (`trn_type`,`trn_id`)
    );

    ALTER TABLE `product_ebill_format_master` DROP `column_header`;

    ALTER TABLE `product_ebill_product_mapping` ADD `auto_mapped` ENUM('Y','N') NULL AFTER `thims_product_id`;

    ALTER TABLE `product_ebill_format_master` CHANGE `format_type` `format_type` ENUM('csv','excel_identifier','excel_direct','excel_normal') NOT NULL;
    ALTER TABLE `product_ebill_format_master` CHANGE `format_type` `format_type` ENUM('csv','excel_identifier','excel_direct','excel_normal') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'CSV: values are available in single column separated with some character. Direct: no separate header record, supplier bill number repeated along with item details. Identifier: details appear with identifier such as HTF. Normal: Supplier bill number appears in header row but identifiers are not defined. Provide header row number and data row number';


    ALTER TABLE `product_ebill_product_mapping` CHANGE `supplier_product_pack` `supplier_product_pack` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL;
    ALTER TABLE `product_ebill_transaction_tmp` CHANGE `product_id` `product_id` VARCHAR(15) NOT NULL COMMENT 'product_master - product_id';
    ALTER TABLE `product_ebill_product_mapping` CHANGE `supplier_product_id` `supplier_product_id` VARCHAR(15) NOT NULL;

    ALTER TABLE `leave_rules` CHANGE `accrue_days_apply_year1` `accrue_days_apply_year1` ENUM('F','Q','M','H','E') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'M' COMMENT 'This column is used to apply accrue days for first year and applicable only when the accrual is yearly. F means Full Q means quarterly H means Half Year M means Monthly and E means Manual Entry';

-- 2024-01-31 : Nandini, Added gst_number column in ph_customer master table
    ALTER TABLE `ph_customer_master` ADD `gst_number` VARCHAR(15) NOT NULL AFTER `hospital_internal`;

-- 2024-02-02 : Rahil
ALTER TABLE `product_transaction_header` ADD `payment_card_number` VARCHAR(20) NOT NULL AFTER `payment_agency`;
ALTER TABLE `product_transaction_header` CHANGE `payment_card_number` `payment_card_number` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `product_transaction_header_amendment` ADD `payment_card_number` VARCHAR(20) NULL DEFAULT NULL AFTER `payment_agency`;

ALTER TABLE `product_transaction_header` ADD `approval_code` VARCHAR(30) NULL DEFAULT NULL AFTER `payment_number`;
ALTER TABLE `product_transaction_header_amendment` ADD `approval_code` VARCHAR(30) NULL DEFAULT NULL AFTER `payment_number`;

-- 2024-02-05 : Nandini, Added new menu for Supplier Payment Report

    INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Supplier Payment Report', '', 'far fa-circle', 'Item/SupplierPaymentReportInput', NULL, 'Y', 'thims', '2020-01-30 00:00:36', '', '0000-00-00 00:00:00');

-- 2024-02-15 : Nandini, increased product description limit to 200 in Pharmacy & store PO

    ALTER TABLE `purchase_order_detail` CHANGE `additional_product_description` `additional_product_description` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
    ALTER TABLE `purchase_order_detail_amendment` CHANGE `additional_product_description` `additional_product_description` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

-- 2024-02-15 : Nandini, Added approver_id column 
    ALTER TABLE `department_master` ADD `approver_id` VARCHAR(50) NOT NULL AFTER `department_is_active`;


-- 2024-02-28: Prince
ALTER TABLE `employee_leave` ADD `apply_for` ENUM('1','2') NULL AFTER `is_half_day`;

ALTER TABLE `employee_holiday_eligibility` ADD PRIMARY KEY(`year`);

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Approve Leave By HR', '', 'far fa-circle', 'HRApproveLeave', NULL, 'Y', 'thims', '2024-02-19', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Supervisor Report', '', 'far fa-circle', 'supervisorsreport', NULL, 'Y', 'thims', '2024-02-19', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Apply Optional Holiday', '', 'far fa-circle', './../../pages/attendance/employee_optional_leave.php?optionalby=R', NULL, 'Y', 'thims', '2024-02-19', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Approve Optional Holiday', '', 'far fa-circle', './../../pages/attendance/employee_optional_leave.php?optionalby=H', NULL, 'Y', 'thims', '2024-02-19', '', '0000-00-00 00:00:00');

ALTER TABLE `employee_salary_head_master` ADD INDEX(`print_sequence`);
ALTER TABLE `thims1_demo108`.`employee_salary_head_master` DROP INDEX `print_sequence`, ADD INDEX `print_sequence` (`print_sequence`, `head_type`) USING BTREE;
ALTER TABLE `employee_monthly_salary_header` ADD INDEX(`salary_month`);


-- 2024-02-28 : Nandini, Added parameter for setting limit to add products while pharmacy purchase
    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'Pharmacy Purchase Transaction Max Limit', 'PHA_PUR_TRAN_MAX_LIMIT', '0: No Limit, 100: Max lines should be 100..', '', '', 'N', 'T', '');
    
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'PHA_PUR_TRAN_MAX_LIMIT', '0');

-- 2024-03-05 : Prince

ALTER TABLE `product_ebill_format_master` ADD `bill_date_format` VARCHAR(20) NOT NULL AFTER `expiry_date_format`;

ALTER TABLE `product_ebill_format_master` ADD `formate_type` ENUM('general','date') NOT NULL COMMENT 'General when type is general or text and date when typw is date in excel.' AFTER `Identifiers`;

-- 2024-03-12 : Rahil


ALTER TABLE `payment_gateway_status` CHANGE `trn_type` `trn_type` ENUM('O','I','PS','HA','PHA','PC','PHPC','PHS') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'O=op bill, I=ip bill, PS=pharmacy bill, HA=hospital advance, PHA=pharmacy advance,PC=payment collection,PHPC=pharmacy payment collection, PHS=pharmacy sale';


ALTER TABLE `product_receipt_transaction` CHANGE `payment_card_number` `payment_card_number` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;


-- 2024-03-13 : Jayraj

ALTER TABLE `product_master` CHANGE `product_name` `product_name` VARCHAR(100) NOT NULL;
	-- PO ITEM discreption data lenght change


-- 2024-03-13 : Jayraj Release Done No 68
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.68', '2023-03-13', 'SP98', '0', '');

-- 2024-03-14 : Nandini, increased product description limit to 500 in Pharmacy & store PO

    ALTER TABLE `purchase_order_detail` CHANGE `additional_product_description` `additional_product_description` VARCHAR(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
    ALTER TABLE `purchase_order_detail_amendment` CHANGE `additional_product_description` `additional_product_description` VARCHAR(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

-- 15-3-24: Prince Patel
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Finalize Salary', '', 'far fa-circle', 'FinalSalaryInput', NULL, 'Y', 'thims', '2024-02-19', '', '0000-00-00 00:00:00');

ALTER TABLE `employee_monthly_salary_header` ADD `finalize_datetime` DATETIME NULL AFTER `is_calculated`;


-- 15-3-24: Prince Patel
ALTER TABLE `requests` ADD `request_department_id` INT NOT NULL AFTER `requested_by`;


05-04-24 :Prince Patel
ALTER TABLE `employee_monthly_salary_header` ADD `calculated_lop` VARCHAR(50) NULL AFTER `days_payable`;

ALTER TABLE `job_category_master` ADD `probation_duration` TINYINT NULL AFTER `job_category_name`;

ALTER TABLE `job_category_master` ADD `notice_period` TINYINT NULL AFTER `probation_duration`;

ALTER TABLE `employee_languages` CHANGE `comments` `comments` VARCHAR(50) NULL;

-- 16-4-24: Nandini, temporary table for Pharmacy bills reduction and Menu 

    INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Pharmacy Discount Adjustment', '', 'far fa-circle', 'Item/ReducePhaBillInput', NULL, 'Y', 'thims', '2020-01-30 00:00:36', '', '0000-00-00 00:00:00');

CREATE TABLE `temp_pha_bills_reduction` (
 `Sr` int NOT NULL AUTO_INCREMENT,
 `trn_fyear` int NOT NULL,
 `trn_type` varchar(2) NOT NULL,
 `trn_id` int NOT NULL,
 PRIMARY KEY (`Sr`)
) ENGINE=InnoDB AUTO_INCREMENT=2


-- 17-4-24: Nandini, increased size of password 
ALTER TABLE `tp_software_interface_configuration` CHANGE `lis_password` `lis_password` VARCHAR(30);

-- 17-4-24: Nandini, Added parameter for Sale/Sale Return rounding of invoiceAmount
    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'Pharmacy Sale/Sale Return Bill Rounding Setup', 'pha_s_sr_bill_rounding_setup', 'If value = 5 then rounding upto 5Rs is allowed', '', '', 'N', 'T', '');
    
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'pha_s_sr_bill_rounding_setup', '1');


-- 2024-04-19 Jayraj : Release no 69

INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.69', '2023-04-19', 'SP99', '0', '');

-- 2024-04-25 Nandini : temporary table for storing tax details
CREATE TABLE `temp_pha_bills_reduction_tax_details` (
 `status` text NOT NULL,
 `net_taxable` float(11,2) NOT NULL,
 `cgst_amt` float(10,2) NOT NULL,
 `sgst_amt` float(10,2) NOT NULL,
 `total` float(10,2) NOT NULL
) ENGINE=InnoDB


-- 2024-05-27 Prince Patel
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'View Shift', '', 'far fa-circle', 'FinalSalaryInput', NULL, 'Y', 'thims', '2024-02-19', '', '0000-00-00 00:00:00');

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`,`CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'View Salary Report', '', 'far fa-circle', 'SalaryReport_emp', NULL, 'Y', 'thims', '2024-05-04', '', '0000-00-00 00:00:00');

ALTER TABLE `employee_salary_head_master` CHANGE `head_code` `head_code` VARCHAR(10) NOT NULL COMMENT 'Head codes for some heads like Stipend, CTC, Gross, Net should be standard';

ALTER TABLE `employee_master` ADD `employee_bank_address` VARCHAR(250) NOT NULL AFTER `employee_bank_ifsc_code`, ADD `employee_bank_account_type` VARCHAR(10) NOT NULL AFTER `employee_bank_address`;

ALTER TABLE `employee_monthly_salary_header` ADD `payslip_release` ENUM('Yes','No') NOT NULL DEFAULT 'No' AFTER `finalize_datetime`;

ALTER TABLE `department_master` ADD `Head_of_Department` INT NOT NULL AFTER `department_is_active`;

-- 2024-06-08: Nandini
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Day Wise GST Sales Summary', '', 'far fa-circle', 'Item/GstSalesSummaryInput', NULL, 'Y', 'thims', '', '', '');

-- 2024-06-10: Nandini
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consignment Purchase Amendment', '', 'far fa-circle', 'Item/ConsignmentPurchaseAmendment', NULL, 'Y', 'thims', '', '', '');

-- 2024-06-20: Nandini
ALTER TABLE `rate_master_detail` ADD `sales_ptr_margin` FLOAT(5,2) NOT NULL AFTER `is_ph_ptr_applicable`;

-- 2024-06-21: Nandini
CREATE TABLE `temp_pha_bills_redu_receipt_details` (
 `rcpt_fyear` smallint NOT NULL,
 `rcpt_type` char(6) NOT NULL,
 `rcpt_no` int NOT NULL,
 `amount_received` float NOT NULL
) ENGINE=InnoDB


-- 2024-07-24 : query to be given for release (transferring values from parameter to rate master)
UPDATE `rate_master_detail` SET sales_ptr_margin = (SELECT `value` from parameter WHERE `name` = 'PH_MRGRT_ON_PTR')  WHERE `is_ph_ptr_applicable` = 'Y';
-- guide support team to change path for store supplier master
UPDATE `navigation` SET File_Name = 'Suppliers/list/PHA' WHERE `File_Name` = 'Suppliers/list';


-- 2024-07-26 : Jayraj, Release Done, No : 71
    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.71', '2023-07-26', 'SP101', '0', '');


-- 2024-08-01 : jayraj
    ALTER TABLE `department_master` CHANGE `Head_of_Department` `Head_of_Department` INT NULL DEFAULT NULL;
    
-- 2024-07-26 : Nandini, additional fields added to supplier payment
ALTER TABLE `supplier_payment_details` ADD `payment_mode` VARCHAR(10) NOT NULL AFTER `Bill_Amount_Date`, ADD `bank` VARCHAR(10) NOT NULL AFTER `payment_mode`, ADD `cheque_ref_no` VARCHAR(20) NOT NULL AFTER `bank`;

ALTER TABLE `supplier_payment_details` ADD `Payment_Vou_Fyear` SMALLINT NOT NULL AFTER `Voucher_No`, ADD `Payment_Vou_No` INT NOT NULL AFTER `Payment_Vou_Fyear`;

INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('PPV', '', '0', '0');
INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('GPV', '', '0', '0');

-- 2024-08-10: Nandini, increased product name character limit
ALTER TABLE `medicine_master` CHANGE `product_name` `product_name` VARCHAR(100) NOT NULL;
ALTER TABLE `product_master` CHANGE `product_name` `product_name` VARCHAR(100) NOT NULL COMMENT 'Medicine/Item Name';
ALTER TABLE `product_master` CHANGE `product_alias_name` `product_alias_name` VARCHAR(100)  NULL DEFAULT '';

-- 2024-08-14 : Nandini , Menu for IP Bill Wise DC/DCR report
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'IP Bill Wise DC/DCR Report', '', 'far fa-circle', 'Item/IPBillWiseDcDcrReportInput', NULL, 'Y', 'thims', '', '', '');

-- 2024-08-27 : JAYRAJ, Payroll related changes
      ALTER TABLE `employee_master` CHANGE `comp_off` `comp_off` ENUM('Yes','No','OT','BOTH') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'No' COMMENT 'Compensatory Off or Overtime Allowed, OR BOTH';

      ALTER TABLE `employee_daily_time_correction` ADD `ot_hours2` TIME NOT NULL COMMENT 'Over time hours' AFTER `compensation_status`;
      ALTER TABLE `employee_daily_time_correction` CHANGE `ot_hours` `ot_hours` TIME NOT NULL COMMENT 'Comp. off hours';

-- 2024-09-05 : Nandini, Seperated Payment Collection WriteOff
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Payment WriteOff', '', 'far fa-circle', 'Item/PaymentWriteOff', NULL, 'Y', 'thims', '', '', '');

-- 2024-09-11 : Nandini, Menu for Payment Collection with search option
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Payment Collection With Search', '', 'far fa-circle', 'Item/PaymentCollectionSearch', NULL, 'Y', 'thims', '', '', '');

-- 2024-09-12 : Nandini, Menu for WriteOff with search option
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Payment Write Off With Search', '', 'far fa-circle', 'Item/PaymentWriteOffSearch', NULL, 'Y', 'thims', '', '', '');

-- 2024-08-03 : Nandini , Initial record for RFQ
INSERT INTO `last_transaction_number` (`trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('RFQ', 'P', '2425', '0');

-- 2024-09-09 : Nandini, Menu for Request for Quotation of PO
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Request For Quotation', '', 'far fa-circle', 'StorePo/RequestForQuotation', NULL, 'Y', 'thims', '', '', '');

-- 2024-09-09 : Nandini, added RFQ in purchase_order_type
ALTER TABLE `purchase_order_header` CHANGE `purchase_order_type` `purchase_order_type` ENUM('NPO','RPO','RFQ') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT 'RPO- Rate Purchase OrderNPO- Normal Purchase OrderRFQ-Request For Quotation';


-- 2024-10-17 Release Package Complete 72 No.

INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.72', '2024-10-17', 'SP102', '0', '');


-- 2024-10-28 : Jayraj

 INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'Purchase', 'IS_PURCHASE_WITH_PO_DETAIL_EDITABLE', 'IS_PURCHASE_WITH_PO_DETAIL_EDITABLE', 'Value Y,N; if Y then in purchase with po option all detail like rate, disc, gst editable.','','','N', 'D', 'Y,N');
 INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'Pharmacy', 'IS_PURCHASE_WITH_PO_DETAIL_EDITABLE', 'N');


-- 2024-11-14 : 
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Trn Wise HSN Missing Report', '', 'far fa-circle', 'item/ProductWithBlankHsnInput', NULL, 'Y', 'thims', '', '', '');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Upload Missing HSN', '', 'far fa-circle', 'item/uploadBlankHsnProductInput', NULL, 'Y', 'thims', '', '', '');


ALTER TABLE `material_request_header` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

ALTER TABLE `material_request_detail` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `material_request_detail` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;


ALTER TABLE `purchase_order_header` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `purchase_order_header_amendment` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `purchase_order_detail` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `purchase_order_detail_amendment` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.73', '2025-01-22', 'SP103', '0', '');



ALTER TABLE `temp_stock_ledger` CHANGE `product_name` `product_name` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT 'Medicine/Item Name';
ALTER TABLE `temp_stock_ledger_balance` CHANGE `product_name` `product_name` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT 'Medicine/Item Name';
ALTER TABLE `temp_nonmoving_products` CHANGE `product_name` `product_name` VARCHAR(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL;



-- 2025-02-13 : Rahil

CREATE TABLE `product_receipt_breakup` (
  `id` int NOT NULL AUTO_INCREMENT,
  `trn_fyear` smallint NOT NULL COMMENT 'Transaction Financial Year',
  `store_code` varchar(6) NOT NULL,
  `trn_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return',
  `trn_id` int NOT NULL,
  `total` float(10,2) NOT NULL COMMENT 'Net Total',
  `invoice_amt` float(11,2) DEFAULT '0.00',
  `remarks` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL COMMENT 'Remarks, if any. Mandatory if return transaction type',
  `amount_received` float(10,2) NOT NULL,
  `payment_mode` varchar(10) NOT NULL DEFAULT 'CSH',
  `payment_agency` varchar(30) DEFAULT NULL,
  `payment_card_number` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `payment_number` varchar(30) DEFAULT NULL,
  `approval_code` varchar(30) DEFAULT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);


-- 2025-03-03 : Jayraj, Release Package Created
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.74', '2025-03-03', 'SP104', '0', '');
 
-- jayraj
ALTER TABLE `product_store_master` ADD `reorder_max_quantity` INT NOT NULL AFTER `product_reorder_level`;


-- 2025-04-09 : Jayraj, add new parameter for purchase partial allow or not

    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Pharmacy', 'Purchase', 'IS_PARTIAL_PURCHASE_ALLOW', 'IS_PARTIAL_PURCHASE_ALLOW', 'Value Y,N; if Y then in purchase Save functionality Enabled, if N then Purchase entry not save.','','','N', 'D', 'Y,N');
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'Pharmacy', 'IS_PARTIAL_PURCHASE_ALLOW', 'Y');

    INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Store', 'Purchase', 'IS_PARTIAL_GRN_ALLOW', 'IS_PARTIAL_GRN_ALLOW', 'Value Y,N; if Y then in GRN Save functionality Enabled, if N then GRN entry time save button disabled.','','','N', 'D', 'Y,N');
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'Store', 'IS_PARTIAL_GRN_ALLOW', 'Y');

-- 2025-04-09 : Rahil

    ALTER TABLE `product_transaction_header` ADD `business_entity` INT NULL AFTER `trn_id`;
    ALTER TABLE `product_transaction_header_amendment` ADD `business_entity` INT NULL AFTER `trn_id`;


INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'IP Sale - Arpan', '', 'far fa-circle', 'Item/issue/Arpan', NULL, 'Y', 'thims', '', '', '');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Transaction Register - Arpan', '', 'far fa-circle', 'Item/RegisterInput/Arpan', NULL, 'Y', 'thims', '', '', '');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Patient Wise Profit Report - Arpan', '', 'far fa-circle', 'Item/PatientWiseProfitReportEntityInput', NULL, 'Y', 'thims', '', '', '');


-- 2025-05-07 : Jayraj


INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'PTR Comparison Report', '', 'far fa-circle', 'report/priceCompairInput', NULL, 'Y', 'thims', '', '', '');

INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Store', 'Purchase', 'P_PR_VOUCHER_DATE_EDITABLE', 'P_PR_VOUCHER_DATE_EDITABLE', 'Value Y,N; if Y then in Voucher date editable for current fyear, if N then date not editable.','','','N', 'D', 'Y,N');
    INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'Store', 'P_PR_VOUCHER_DATE_EDITABLE', 'Y');

-- rahil 
CREATE TABLE `product_receipt_breakup_amendment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `trn_fyear` smallint NOT NULL COMMENT 'Transaction Financial Year',
  `store_code` varchar(6) NOT NULL,
  `trn_type` char(6) NOT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return',
  `trn_id` int NOT NULL,
  `total` float(10,2) NOT NULL COMMENT 'Net Total',
  `invoice_amt` float(11,2) DEFAULT '0.00',
  `remarks` varchar(50) DEFAULT NULL COMMENT 'Remarks, if any. Mandatory if return transaction type',
  `amount_received` float(10,2) NOT NULL,
  `payment_mode` varchar(10) NOT NULL DEFAULT 'CSH',
  `payment_agency` varchar(30) DEFAULT NULL,
  `payment_card_number` varchar(20)  DEFAULT NULL,
  `payment_number` varchar(30) DEFAULT NULL,
  `approval_code` varchar(30) DEFAULT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

ALTER TABLE `product_receipt_breakup` ADD `bill_amendment` SMALLINT NOT NULL DEFAULT '0' AFTER `trn_id`;

ALTER TABLE `product_receipt_breakup_amendment` ADD `bill_amendment` SMALLINT NOT NULL DEFAULT '0' AFTER `trn_id`;

ALTER TABLE `product_master` ADD `is_dpco` ENUM('Y','N') NOT NULL DEFAULT 'N' AFTER `is_consumable`;


--
ALTER TABLE `product_transaction_header` CHANGE `purchase_bill_no` `purchase_bill_no` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL COMMENT 'Purchase Bill Number';

-- rahil
	
CREATE TABLE `stock_adjustment` (
  `adjustment_trn_no` int NOT NULL,
  `fyear` int NOT NULL,
  `sr_no` int NOT NULL,
  `adjustment_txn_date` timestamp NOT NULL,
  `store_code` varchar(6) NOT NULL DEFAULT '1',
  `product_id` int NOT NULL,
  `batch_no` varchar(40) DEFAULT NULL,
  `batch_exp` date DEFAULT NULL,
  `unit_price` float(9,2) NOT NULL,
  `unit_price_wo_gst` float(9,2) NOT NULL DEFAULT '0.00',
  `system_stock` int NOT NULL,
  `physical_stock` int DEFAULT NULL,
  `diff_stock` int DEFAULT NULL,
  `created_by` varchar(20) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(20) NOT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`adjustment_trn_no`,`fyear`,`sr_no`)
)

ALTER TABLE `product_stock` ADD `last_stock_adjusted_by` VARCHAR(20) NOT NULL AFTER `is_product_locked_for_cc`;
ALTER TABLE `product_stock` ADD `last_stock_adjusted_date` DATETIME NULL AFTER `last_stock_adjusted_by`;

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Stock Adjustment Report', '', 'far fa-circle', 'Item/stockAdjustmentInput', NULL, 'Y', 'thims', '', '', '');

-- 2025-07-04 : Jayraj

ALTER TABLE product_stock CHANGE price_per_unit price_per_unit FLOAT(15,4) NOT NULL;
ALTER TABLE product_stock CHANGE price_per_unit_wo_gst price_per_unit_wo_gst FLOAT(15,4) NOT NULL DEFAULT '0.0000';
ALTER TABLE product_stock CHANGE latest_purchase_price_per_unit_wo_gst latest_purchase_price_per_unit_wo_gst FLOAT(15,4) NOT NULL DEFAULT '0.0000';


-- rahil
INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'Store', 'Purchase', 'PURCHASE_AGAINST_CSN_GRN', 'PURCHASE_AGAINST_CSN_GRN', 'Stores from which Purchase Against Consignment GRN will be allowed','','','N', 'T', '');
INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'Pharmacy', 'PURCHASE_AGAINST_CSN_GRN', '');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Purchase Against Consignment GRN', '', 'far fa-circle', 'Item/purchaseViaCSN', NULL, 'Y', 'thims', '', '', '');

-- 2025-08-06 : Jayraj Release Complete, company,branch and internationalization related changes not include in this package
    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.77', '2025-08-06', 'SP107', '0', '');


-- rahil Multi company Multi Branch changes 
ALTER TABLE `product_transaction` ADD `store_code` VARCHAR(6) NOT NULL AFTER `trn_fyear`;

ALTER TABLE `product_transaction_header` DROP PRIMARY KEY;
ALTER TABLE `product_transaction_header` ADD `pth_id` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`pth_id`);


ALTER TABLE `product_transaction_header` ADD UNIQUE(`company_id`, `branch_id`, `trn_fyear`, `store_code`, `trn_type`, `trn_id`);


ALTER TABLE `product_transaction` ADD UNIQUE(`company_id`, `branch_id`, `trn_fyear`, `store_code`, `trn_type`, `trn_id`, `trn_sr_no`);
ALTER TABLE `product_transaction` DROP PRIMARY KEY;
ALTER TABLE `product_transaction` ADD `pth_id` INT UNSIGNED NOT NULL FIRST;

UPDATE `product_transaction`  as pt inner JOIN product_transaction_header as pth 
on (pt.trn_fyear=pth.trn_fyear and pt.trn_type=pth.trn_type and pt.trn_id=pth.trn_id)
SET pt.pth_id = pth.pth_id;


ALTER TABLE `product_transaction` ADD PRIMARY KEY(`pth_id`, `trn_sr_no`);

ALTER TABLE `product_transaction_header_amendment` ADD `company_id` TINYINT NOT NULL FIRST;
ALTER TABLE `product_transaction_header_amendment` ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `product_transaction_header_amendment` ADD `pth_id` INT UNSIGNED NOT NULL FIRST;

UPDATE `product_transaction_header_amendment`  as ptha inner JOIN product_transaction_header as pth 
on (ptha.trn_fyear=pth.trn_fyear and ptha.trn_type=pth.trn_type and ptha.trn_id=pth.trn_id)
SET ptha.pth_id = pth.pth_id;

ALTER TABLE `product_transaction_header_amendment` ADD UNIQUE(`company_id`, `branch_id`, `trn_fyear`, `store_code`, `trn_type`, `trn_id`, `bill_amendment`);

ALTER TABLE `product_transaction_header_amendment` DROP PRIMARY KEY;
ALTER TABLE `product_transaction_header_amendment` ADD PRIMARY KEY(`pth_id`, `bill_amendment`);


ALTER TABLE `product_transaction_amendment` ADD `company_id` TINYINT NOT NULL FIRST;
ALTER TABLE `product_transaction_amendment` ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `product_transaction_amendment` ADD `pth_id` INT UNSIGNED NOT NULL FIRST;
ALTER TABLE `product_transaction_amendment` ADD `store_code` VARCHAR(6) NOT NULL AFTER `trn_fyear`;


UPDATE `product_transaction_header_amendment`  as ptha inner JOIN product_transaction_amendment as pta 
on (ptha.trn_fyear=pta.trn_fyear and ptha.trn_type=pta.trn_type and ptha.trn_id=pta.trn_id and ptha.bill_amendment = pta.bill_amendment)
SET pta.pth_id = ptha.pth_id;

ALTER TABLE `product_transaction_amendment` ADD PRIMARY KEY(`pth_id`, `trn_sr_no`, `bill_amendment`);
ALTER TABLE `product_transaction_amendment` DROP INDEX `trn_fyear`, ADD UNIQUE `trn_fyear` (`company_id`, `branch_id`, `trn_fyear`, `store_code`, `trn_type`, `trn_id`, `trn_sr_no`, `bill_amendment`) USING BTREE;



ALTER TABLE `product_transaction_batch` ADD UNIQUE(`company_id`, `branch_id`, `trn_fyear`, `store_code`, `trn_type`, `trn_id`, `trn_sr_no`, `trn_batch_sr_no`);
ALTER TABLE `product_transaction_batch` ADD `pth_id` INT UNSIGNED NOT NULL FIRST;

UPDATE `product_transaction_batch` as ptb inner JOIN product_transaction as pt on (ptb.trn_fyear=pt.trn_fyear and ptb.trn_type=pt.trn_type and ptb.trn_id=pt.trn_id and ptb.trn_sr_no = pt.trn_sr_no) SET ptb.pth_id = pt.pth_id;
ALTER TABLE `product_transaction_batch` ADD PRIMARY KEY(`pth_id`, `trn_sr_no`, `trn_batch_sr_no`);


ALTER TABLE `product_receipt_transaction` DROP INDEX `unique_key`; // need to vefiy

ALTER TABLE `product_receipt_breakup` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `product_receipt_breakup` ADD UNIQUE(`company_id`, `branch_id`, `trn_fyear`, `store_code`, `trn_type`, `trn_id`, `payment_mode`);

ALTER TABLE `suppliermaster` ADD `company_id` TINYINT NOT NULL AFTER `supplier_id`;
ALTER TABLE `suppliermaster` DROP INDEX `supplier_name`, ADD INDEX `supplier_name` (`supplier_name`, `company_id`) USING BTREE;

ALTER TABLE `generic_drug_master` ADD `company_id` TINYINT NOT NULL FIRST;
ALTER TABLE `product_reorder_request` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `product_store_master` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;

ALTER TABLE `product_reorder_request` DROP INDEX `store_code`, ADD INDEX `store_code` (`company_id`, `branch_id`, `store_code`, `product_id`, `reorder_request_date`) USING BTREE;

ALTER TABLE `category_master` ADD `company_id` TINYINT NOT NULL FIRST;
ALTER TABLE `unit_master` ADD `company_id` TINYINT NOT NULL FIRST;
ALTER TABLE `manufacturer_master` ADD `company_id` TINYINT NOT NULL FIRST;
ALTER TABLE `pharmacy_store_users_link` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `pharmacy_store_users_link` DROP PRIMARY KEY, ADD PRIMARY KEY (`company_id`, `branch_id`, `user_id`, `store_code`, `trn_type`) USING BTREE;


ALTER TABLE `ph_customer_master` ADD `company_id` TINYINT NOT NULL FIRST;
ALTER TABLE `ph_customer_master` DROP PRIMARY KEY, ADD PRIMARY KEY (`ph_cust_id`, `company_id`) USING BTREE;

ALTER TABLE `external_doctor_master` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `external_doctor_master` DROP PRIMARY KEY, ADD PRIMARY KEY (`external_doctor_id`, `company_id`, `branch_id`) USING BTREE;
ALTER TABLE `discount_master` ADD `company_id` TINYINT NOT NULL FIRST;

ALTER TABLE `discount_master` DROP PRIMARY KEY, ADD PRIMARY KEY (`discount_id`, `company_id`) USING BTREE;
ALTER TABLE `product_receipt_breakup_amendment` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;

ALTER TABLE `supplier_product_relationship` ADD `company_id` INT NOT NULL FIRST;
ALTER TABLE `supplier_product_relationship` DROP INDEX `supplier_id`, ADD UNIQUE `supplier_id` (`company_id`, `supplier_id`, `product_id`) USING BTREE;

01-09-2025:Harsh
ALTER TABLE `productgroup_master` ADD `company_id` TINYINT NOT NULL AFTER `productgroup_id`;

-- rahil 

ALTER TABLE `product_receipt_transaction` ADD `store_code` VARCHAR(6) NOT NULL AFTER `trn_fyear`;
ALTER TABLE `product_receipt_transaction` DROP INDEX `company_id`, ADD UNIQUE `company_id` (`company_id`, `branch_id`, `trn_fyear`, `store_code`, `trn_type`, `trn_id`, `trn_receipt_no`) USING BTREE;

ALTER TABLE `ph_temp_payment_collection` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `pharmacy_bill_amt_writeoff_detail` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;


ALTER TABLE `stock_adjustment` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `stock_adjustment` DROP PRIMARY KEY, ADD PRIMARY KEY (`adjustment_trn_no`, `fyear`, `sr_no`, `store_code`) USING BTREE;
ALTER TABLE `stock_adjustment` ADD UNIQUE(`company_id`, `branch_id`, `adjustment_trn_no`, `fyear`, `sr_no`, `store_code`);

ALTER TABLE `product_stock_opening_audit_trial` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `product_stock_opening_audit_trial` DROP INDEX `store_code`, ADD UNIQUE `store_code` (`store_code`, `product_id`, `batch_no`, `company_id`, `branch_id`) USING BTREE;

ALTER TABLE `supplier_payment_details` ADD `store_code` VARCHAR(6) NOT NULL AFTER `Trn_Fyear`;

ALTER TABLE `cycle_count` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `cycle_count`
  DROP PRIMARY KEY,
   ADD PRIMARY KEY(
     `company_id`,
     `branch_id`,
     `cc_trn_no`,
     `sr_no`
   );

   ALTER TABLE `cycle_count_header` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `cycle_count_header`
  DROP PRIMARY KEY,
   ADD PRIMARY KEY(
     `company_id`,
     `branch_id`,
     `cc_trn_no`
   );

 09-09-2025: Harsh
 ALTER TABLE `product_stock_update` ADD `company_id` TINYINT NOT NULL AFTER `update_id`, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`; 
 ALTER TABLE `ph_exp_voucher_header` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
 ALTER TABLE `ph_exp_voucher_header` ADD UNIQUE(`company_id`, `branch_id`, `fyear`, `voucher_number`);
 ALTER TABLE `ph_exp_voucher_transaction` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
 ALTER TABLE `ph_exp_voucher_transaction` ADD UNIQUE(`company_id`, `branch_id`, `fyear`, `voucher_number`, `voucher_sr_no`);
 ALTER TABLE `ph_temp_cash_collection` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`; 

    ALTER TABLE `conversionfactor_update_request` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;

27-0-2025: Harsh
ALTER TABLE `temp_stock_ledger_balance` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `temp_stock_ledger_balance` DROP PRIMARY KEY, ADD PRIMARY KEY(`company_id`, `branch_id`, `product_id`);
ALTER TABLE `temp_stock_ledger_avgrate` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `temp_stock_ledger_avgrate` DROP PRIMARY KEY, ADD PRIMARY KEY(`company_id`, `branch_id`, `product_id`);
ALTER TABLE `temp_stock_ledger` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `temp_stock_ledger` DROP PRIMARY KEY, ADD PRIMARY KEY(`company_id`, `branch_id`, `trn_fyear`, `trn_type`, `trn_id`, `trn_sr_no`);
ALTER TABLE `temp_stock_ledger_summary` ADD `company_id` TINYINT NOT NULL AFTER `summary_id`, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `temp_nonmoving_products` ADD `company_id` TINYINT NOT NULL AFTER `product_id`, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `sales_return_barcode_reprint` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;

-- 2025-11-07 - rahil

 INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'Pharmacy purchase Bill Rounding Setup', 'pha_purchase_rounding_setup', 'If value = 5 then rounding upto 5 is allowed', '', '', 'N', 'T', '');
    
 INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'pha_purchase_rounding_setup', '0');


-- 2025-12-10 - jayraj, Release Done no 79.

INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.79', '2025-12-09', 'SP109', '0', '');

--2025-12-12 -Harsh
 ALTER TABLE product_master MODIFY sl_brand_cd VARCHAR(2) NULL, MODIFY sl_grp_cd VARCHAR(2) NULL, MODIFY sl_fmly_cd VARCHAR(2) NULL;

 -- rahil 2025-14-12
  INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'Pharmacy Voucher Number independent Entity wise', 'VOUCHER_NUMBER_ENTITY_WISE', 'If value Y then voucher number will be independent, but need to setup like PO', '', '', 'N', 'D', 'Y,N');
    
 INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'VOUCHER_NUMBER_ENTITY_WISE', 'N');

update product_transaction as pt INNER join product_transaction_header as pth on pt.pth_id=pth.pth_id set pt.store_code=pth.store_code;

update product_transaction as pt INNER join product_transaction_header as pth on pt.pth_id=pth.pth_id set pt.store_code=pth.store_code where pt.store_code = '';

UPDATE product_transaction  as pt inner JOIN product_transaction_header as pth 
on (pt.trn_fyear=pth.trn_fyear and pt.trn_type=pth.trn_type and pt.trn_id=pth.trn_id)
SET pt.pth_id = pth.pth_id WHERE pt.pth_id = 0;


SELECT
    pt.product_id,pm.product_name
FROM
    `visit` AS v
INNER JOIN product_transaction_header AS pth
ON
    v.visit_id = pth.visit_id
INNER JOIN product_transaction AS pt
ON
    pth.trn_fyear = pt.trn_fyear AND pth.trn_id = pt.trn_id AND pth.trn_type = pt.trn_type
    INNER JOIN product_master as pm on pm.product_id = pt.product_id
WHERE
   v.visit_date >= '2025-08-01' and v.business_entity_code = 'H1' group by pm.product_id order by pm.product_name;


CREATE TABLE `btob_customer_master` (
  `customer_id` smallint unsigned NOT NULL,
  `company_id` tinyint NOT NULL,
  `customer_code` varchar(10) DEFAULT NULL,
  `customer_name` varchar(50) DEFAULT NULL,
  `customer_phone_no` varchar(25) DEFAULT NULL,
  `customer_address1` varchar(100) DEFAULT NULL,
  `customer_address2` varchar(100) DEFAULT NULL,
  `customer_city` varchar(30) DEFAULT NULL,
  `customer_state` varchar(30) DEFAULT NULL,
  `customer_country_code` char(2) DEFAULT NULL,
  `customer_pincode` mediumint DEFAULT NULL,
  `customer_email` varchar(100) DEFAULT NULL,
  `customer_gstin_no` varchar(20) DEFAULT NULL,
  `customer_supply_type` enum('intra_state','inter_state') DEFAULT NULL,
  `drug_licence_no` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `customer_type` enum('GEN','PHA','CMN') DEFAULT NULL,
  `customer_bill_format` varchar(50) DEFAULT NULL,
  `customer_is_active` enum('Y','N') DEFAULT NULL,
  `created_by` varchar(10) NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`customer_id`),
  KEY `supplier_name` (`customer_name`,`company_id`) USING BTREE
);


CREATE TABLE `btob_supplier_contact` (
  `customer_contact_id` smallint unsigned NOT NULL,
  `customer_id` smallint unsigned DEFAULT NULL,
  `customer_contact_name` varchar(50) DEFAULT NULL,
  `customer_contact_no` varchar(15) DEFAULT NULL,
  `created_by` varchar(10) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `lastmodified_by` varchar(10) DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`customer_contact_id`),
  KEY `FK_suppliercontactmaster` (`customer_id`)
) 

-- foreign key removed 
ALTER TABLE product_transaction_header DROP FOREIGN KEY FK_suppliermaster;

-- bill_status for reducing pha bill program
ALTER TABLE `product_transaction_header` ADD `bill_status` TINYINT NOT NULL DEFAULT '0' AFTER `created_by`;

-- supplier_payment_details
ALTER TABLE `supplier_payment_details` CHANGE `Bill_Amount_Paid` `Bill_Amount_Paid` FLOAT(10,2) NOT NULL;

ALTER TABLE `supplier_payment_details` CHANGE `Trn_Type` `Trn_Type` CHAR(6) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL;
ALTER TABLE supplier_payment_details ADD INDEX idx_spd_update_join ( company_id, branch_id, Trn_Fyear, Trn_Type, Voucher_No );

-- for updating store code in supplier_payment_detail
UPDATE supplier_payment_details AS spd INNER JOIN product_transaction_header AS pth ON spd.company_id = pth.company_id AND spd.branch_id = pth.branch_id AND spd.Trn_Fyear = pth.trn_fyear AND spd.Trn_Type = pth.trn_type AND spd.Voucher_No = pth.trn_id SET spd.store_code = pth.store_code;

--DCR Discount Edit menu 
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'DCR Discount Edit', '', 'far fa-circle', 'Item/DcrDiscountEdit', NULL, 'Y', 'thims', '', '', '');



-- 2026-01-05 : Jayraj 

ALTER TABLE `material_request_detail` ADD `received_quantity` FLOAT(15,4) NOT NULL AFTER `issued_quantity`, ADD `received_by` VARCHAR(20) NOT NULL AFTER `received_quantity`, ADD `received_datetime` DATETIME NOT NULL AFTER `received_by`;


INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Material Receive', '', 'far fa-circle', 'StoreMrDashboard/ReceiverUser', NULL, 'Y', 'thims', '', '', '');

-- rahil menu b2b
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'B2B Sell', '', 'far fa-circle', 'Item/sellbtob', NULL, 'Y', 'thims', '', '', '');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'B2B Sell Return', '', 'far fa-circle', 'Item/sellreturnbtob', NULL, 'Y', 'thims', '', '', '');

CREATE INDEX idx_pt_update_discount ON product_transaction ( company_id, branch_id, trn_fyear, trn_type, trn_id, product_id, batch_no );


-- 2026-01-22 : Jayraj
ALTER TABLE `purchase_order_detail` ADD `netrate_or_grossrate` ENUM('','N','G') NOT NULL AFTER `purchase_uom`;
ALTER TABLE `purchase_order_detail` ADD `free_qty` FLOAT(15,4) NOT NULL AFTER `pending_quantity`, ADD `sch_amt` FLOAT(15,4) NOT NULL AFTER `free_qty`;
ALTER TABLE `purchase_order_detail` CHANGE `free_qty` `free_qty` INT NOT NULL, CHANGE `sch_amt` `sch_amt` FLOAT(12,3) NOT NULL COMMENT 'store schem percentage';

INSERT INTO `general_code_master` (`master_cd`, `master_description`, `active`, `short_code`, `language`, `calculation`, `gm_order`, `gm_shortcode`, `gm_custom_field`) VALUES ('FREEQTY', 'Pharmacy Free Quantity', 'Yes', '', '', '', 'N', 'N', 'N');
INSERT INTO `general_master` (`master_cd`, `company_id`, `branch_id`, `gm_code`, `gm_code_sr`, `gm_description`, `gm_description_hindi`, `gm_description_guj`, `gm_custom_field`, `auto_calculation`, `gm_short_code`, `gm_short_code2`, `gm_active`, `is_auto_created`, `is_auto_updated`, `mapping_id`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES ('FREEQTY', '', '', '1', '1', '10 is to 1', '', '', '', '', '10', '1', 'Y', '0', '0', NULL, '', '2026-01-27 12:32:56.000000', '', '2026-01-27 12:32:56.000000'),('FREEQTY', '0', '0', '2', '2', '15 is to 1', '', '', '', '', '15', '1', 'Y', '0', '0', NULL, '', '2026-01-27 12:32:56', '', '2026-01-27 12:32:56'),('FREEQTY', '', '', '3', '3', '20 is to 1', '', '', '', '', '20', '1', 'Y', '0', '0', NULL, '', '2026-01-27 12:32:56.000000', '', '2026-01-27 12:32:56.000000');


-- 2026-01-28 : jayraj, DB change for sales bill customer name search performance issue.
    CREATE INDEX idx_edm_join
    ON external_doctor_master
    (company_id, branch_id, external_doctor_id, doctor_name);

    CREATE INDEX idx_pth_last_doctor
    ON product_transaction_header
    (company_id, branch_id, ph_cust_id, trn_id DESC, doctor_id);

    

-- rahil sale b2b parameter for store
 INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'Sale B2B Store', 'sale_b2b_store', 'store code for sale b2b trn', '', '', 'N', 'T', '');
    
 INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'sale_b2b_store', '');


-- jayraj : purchase order RC related changes add.
ALTER TABLE `purchase_order_detail` ADD `is_product_from_rc` ENUM('N','Y') NOT NULL DEFAULT 'N' COMMENT 'in purchase order product select from rate contract.' AFTER `landing_cost`;

INSERT INTO `last_transaction_number` (`company_id`, `branch_id`, `trn_type`, `business_entity_code`, `trn_fyear`, `trn_id`) VALUES ('0', '0', 'RC', 'P', '2526', '0'), ('0', '0', 'RC', 'H', '2526', '0');

--2026-06-02 Harsh 
ALTER TABLE `material_request_detail` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `material_request_detail` DROP INDEX `mr_fyear`, ADD UNIQUE `mr_fyear` (`mr_fyear`, `mr_id`, `mr_sr_no`, `company_id`, `branch_id`) USING BTREE;

-- pth_id zero problem
SELECT ptha.trn_id,pth.trn_id,ptha.pth_id,pth.pth_id,ptha.trn_fyear FROM product_transaction_header_amendment ptha, product_transaction_header pth WHERE ptha.trn_type = pth.trn_type AND ptha.trn_id = pth.trn_id AND ptha.supplier_id = pth.supplier_id AND pth.purchase_bill_no = pth.purchase_bill_no AND ptha.store_code = pth.store_code AND ptha.trn_id =109 and ptha.trn_type='P';

SELECT 
pta.pth_id,ptha.pth_id, pta.trn_id,ptha.trn_id, pta.trn_fyear,ptha.trn_fyear FROM `product_transaction_amendment` as pta inner join product_transaction_header_amendment as ptha on pta.trn_fyear=ptha.trn_fyear and pta.trn_type=ptha.trn_type and pta.trn_id=ptha.trn_id and pta.bill_amendment=ptha.bill_amendment WHERE pta.`pth_id` = 0 ;

UPDATE `product_transaction_amendment` AS pta INNER JOIN product_transaction_header_amendment AS ptha ON pta.trn_fyear = ptha.trn_fyear AND pta.trn_type = ptha.trn_type AND pta.trn_id = ptha.trn_id AND pta.bill_amendment = ptha.bill_amendment SET pta.pth_id = ptha.pth_id WHERE pta.`pth_id` = 0;

UPDATE product_transaction_header_amendment ptha, product_transaction_header pth SET ptha.pth_id = pth.pth_id WHERE ptha.trn_type = pth.trn_type AND ptha.trn_id = pth.trn_id AND ptha.supplier_id = pth.supplier_id AND pth.purchase_bill_no = pth.purchase_bill_no AND ptha.store_code = pth.store_code AND ptha.pth_id = 0 AND ptha.trn_type = 'P';

SELECT ptha.trn_id,pth.trn_id,ptha.pth_id,pth.pth_id,ptha.trn_fyear FROM product_transaction_header_amendment ptha, product_transaction_header pth WHERE ptha.trn_type = pth.trn_type AND ptha.trn_id = pth.trn_id AND ptha.supplier_id = pth.supplier_id AND pth.purchase_bill_no = pth.purchase_bill_no AND ptha.store_code = pth.store_code AND ptha.trn_id =109 and ptha.trn_type='P';

SELECT COUNT(*),trn_id,trn_fyear,trn_type FROM `product_transaction_amendment` WHERE pth_id = 0 GROUP by trn_fyear,trn_type,trn_id;

UPDATE `product_transaction_header_amendment` as ptha inner JOIN product_transaction_amendment as pta on (ptha.trn_fyear=pta.trn_fyear and ptha.trn_type=pta.trn_type and ptha.trn_id=pta.trn_id and ptha.bill_amendment = pta.bill_amendment) SET pta.store_code = ptha.store_code where ptha.trn_fyear='2526';

-- dc return line item takes time
ALTER TABLE `product_master` ADD INDEX(`company_id`, `branch_id`, `product_type`, `product_name`);
ALTER TABLE `product_transaction_header` ADD INDEX(`company_id`, `branch_id`, `trn_id`, `doctor_id`, `ph_cust_id`);
ALTER TABLE `product_transaction` ADD INDEX(`pth_id`, `product_id`);

09-02-2026 Harsh
ALTER TABLE `material_request_header` ADD `company_id` TINYINT NOT NULL FIRST, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `material_request_header` DROP INDEX `mr_fyear`;
ALTER TABLE `material_request_header` ADD UNIQUE(`company_id`, `branch_id`, `mr_fyear`, `store_code`, `mr_id`);
ALTER TABLE `material_request_detail` DROP INDEX `FK_mr_detail_productid`;
ALTER TABLE material_request_detail ADD UNIQUE KEY uq_mr_product (mr_fyear, mr_id, product_id, company_id, branch_id);
ALTER TABLE `material_request_header` CHANGE `approved_by` `approved_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `material_request_header` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL;
ALTER TABLE `material_request_header` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NULL DEFAULT NULL;




-- index add for medicine indent performance issue solve.

ALTER TABLE `product_stock` ADD INDEX(`product_id`, `batch_no`);
ALTER TABLE `product_master` ADD INDEX(`company_id`, `branch_id`, `product_type`, `product_name`);


-- 2026-02-11 : Jayraj (Material transfer request program list query)

INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Material Transfer Request', '', 'far fa-circle', 'MaterialTrfReq/User', NULL, 'Y', 'thims', '', '', '');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Approve Material Transfer Request', '', 'far fa-circle', 'MaterialTrfReq/SuperUser', NULL, 'Y', 'thims', '', '', '');
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Material Transfer With Req', '', 'far fa-circle', 'MaterialTrfReq/trfApprovedList', NULL, 'Y', 'thims', '', '', '');

CREATE TABLE `material_trf_request_detail` (
  `company_id` tinyint NOT NULL,
  `branch_id` tinyint NOT NULL,
  `trf_req_fyear` smallint NOT NULL,
  `trf_req_id` int NOT NULL,
  `trf_req_sr_no` smallint NOT NULL,
  `product_id` int unsigned NOT NULL,
  `stock_uom` tinyint unsigned NOT NULL,
  `requested_quantity` float(15,4) NOT NULL DEFAULT '0.0000',
  `approved_quantity` float(15,4) NOT NULL DEFAULT '0.0000',
  `issued_quantity` float(15,4) NOT NULL DEFAULT '0.0000',
  `received_quantity` float(15,4) NOT NULL,
  `received_by` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `received_datetime` datetime NOT NULL,
  `created_by` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  UNIQUE KEY `company_id` (`company_id`,`branch_id`,`trf_req_fyear`,`trf_req_id`,`trf_req_sr_no`),
  KEY `FK_mr_detail_productid` (`product_id`),
  KEY `FK_mr_detail_suom` (`stock_uom`),
  KEY `FK_mr_detail_createdby` (`created_by`),
  KEY `FK_mr_detail_lastmodifiedby` (`lastmodified_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE `material_trf_request_header` (
  `company_id` tinyint NOT NULL,
  `branch_id` tinyint NOT NULL,
  `trf_req_fyear` smallint NOT NULL,
  `from_store_code` varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `to_store_code` varchar(6) COLLATE utf8mb3_unicode_ci NOT NULL,
  `trf_req_id` int NOT NULL,
  `trf_req_date` date NOT NULL,
  `trf_req_status` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT 'P - Partially Saved, U - Un approved, T - Cancelled, A - Approved',
  `remarks` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `approved_by` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `approved_date` datetime DEFAULT NULL,
  `created_by` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `lastmodified_by` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `lastmodified_date` datetime DEFAULT NULL,
  UNIQUE KEY `company_id` (`company_id`,`branch_id`,`trf_req_fyear`,`from_store_code`,`trf_req_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- 2026-02-16 : Jayraj barcode print performance issue fixed.
ALTER TABLE `product_transaction_batch` ADD INDEX(`is_barcode_printed`);


-- 2026-02-18 : Jayraj (add new consumption transaction)
INSERT INTO `program_list` (`id`, `program_name`, `program_type`, `icon_class`, `file_name`, `short_code`, `active_YN`, `CreatedBy`, `CreatedDate`, `LastModifiedBy`, `LastModifiedDate`) VALUES (NULL, 'Consumption', '', 'far fa-circle', 'Item/Consumption', NULL, 'Y', 'thims', '', '', '');



-- 2026-02-24 : Jayraj

 INSERT INTO `parameter_setup` (`id`, `module`, `sub_module`, `parameter_name`, `name`, `remark`, `warning_msg`, `more_info`, `customer_access`, `input_type`, `options`) VALUES (NULL, 'PHARMACY', 'PHARMACY', 'PH_DC_STORE', 'PH_DC_STORE', 'Store code for delivery challan transaction', '', '', 'N', 'T', '');
 INSERT INTO `parameter` (`id`, `module`, `name`, `value`) VALUES (NULL, 'PHARMACY', 'PH_DC_STORE', '');

ALTER TABLE `product_receipt_transaction` ADD `btob_customer_id` INT NOT NULL COMMENT 'store for sb2b and srb2b transaction' AFTER `ph_cust_id`;
ALTER TABLE `product_receipt_transaction` CHANGE `btob_customer_id` `btob_customer_id` INT NULL COMMENT 'store for sb2b and srb2b transaction';
ALTER TABLE `product_receipt_transaction` CHANGE `trn_type` `trn_type` CHAR(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL COMMENT 'Transaction Type P-Purchase I-Issue PR-Purchase Return IR-Issue Return';

UPDATE `parameter` p1
JOIN `parameter` p2 
    ON p2.name = 'PH_IP_STORE'
SET 
    p1.module = 'Pharmacy',
    p1.value = p2.value
WHERE 
    p1.name = 'PH_DC_STORE';

-- 2026-03-03 : Jayraj (Release Package Created... NO : 80)

    INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.80', '2026-03-03', 'SP110', '0', '');

14-03-2026: Harsh 
ALTER TABLE `job_title_master` ADD `company_id` TINYINT NOT NULL AFTER `job_title_id`;
ALTER TABLE `job_title_master` DROP INDEX `job_title_name`;
ALTER TABLE `job_title_master` ADD UNIQUE(`company_id`, `job_title_name`);
ALTER TABLE `job_title_master` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `job_title_master` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `employment_status_master` ADD `company_id` TINYINT NOT NULL AFTER `employment_status_id`;
ALTER TABLE `employment_status_master` DROP INDEX `employment_status_name`;
ALTER TABLE `employment_status_master` ADD UNIQUE(`company_id`, `employment_status_name`);
ALTER TABLE `employment_status_master` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `employment_status_master` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `job_category_master` ADD `company_id` TINYINT NOT NULL AFTER `job_category_id`;
ALTER TABLE `job_category_master` DROP INDEX `job_category_name`;
ALTER TABLE `job_category_master` ADD UNIQUE(`company_id`, `business_entity_code`, `job_category_name`);
ALTER TABLE `job_category_master` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `job_category_master` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `sub_department_master` ADD `company_id` TINYINT NOT NULL AFTER `sub_department_id`, ADD `branch_id` TINYINT NOT NULL AFTER `company_id`;
ALTER TABLE `sub_department_master` DROP INDEX `sub_department_name`;
ALTER TABLE `sub_department_master` ADD UNIQUE(`company_id`, `branch_id`, `sub_department_name`);
ALTER TABLE `sub_department_master` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `sub_department_master` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `skill_master` ADD `company_id` TINYINT NOT NULL AFTER `skill_id`;
ALTER TABLE `skill_master` DROP INDEX `skill_name`;
ALTER TABLE `skill_master` ADD UNIQUE(`company_id`, `skill_name`);
ALTER TABLE `skill_master` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `skill_master` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `education_master` ADD `company_id` TINYINT NOT NULL AFTER `education_id`;
ALTER TABLE `education_master` DROP INDEX `education_level`;
ALTER TABLE `education_master` ADD UNIQUE(`company_id`, `education_level`);
ALTER TABLE `education_master` CHANGE `created_by` `created_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `education_master` CHANGE `lastmodified_by` `lastmodified_by` VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;



INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.81', '2026-04-14', 'SP111', '0', '');
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.82', '2026-05-08', 'SP112', '0', '');
INSERT INTO `release_note` (`ver_no`, `release_date`, `module`, `line_no`, `release_note`) VALUES ('3.01.13.83', '2026-06-17', 'SP113', '0', '');