-- 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', '');