----------------------------------------------- Vatrak Query ---------------------------------

1)
CREATE TABLE `product_stock_updated` (
  `product_name` varchar(60) NOT NULL,
  `current_stock_qty` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2)
import csv sheet
3)
ALTER TABLE `product_stock_updated` ADD `product_id` INT(10) NULL AFTER `current_stock_qty`;
4)
UPDATE `product_stock_updated` AS psu JOIN product_master AS pm ON pm.product_name = psu.product_name SET psu.`product_id` = pm.product_id;
5)
CREATE TABLE `product_stock_updated_new` (
  `product_id` int(10) NOT NULL,
  `current_stock_qty` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
6)
INSERT INTO `product_stock_updated_new`(`product_id`, `current_stock_qty`) SELECT product_id,SUM(current_stock_qty) FROM product_stock_updated WHERE product_id IS NOT NULL GROUP BY product_id;
7)
UPDATE `product_transaction_batch` AS ptb JOIN product_stock_updated_new AS psun ON psun.product_id = ptb.product_id SET ptb.`quantity_suom_received` = psun.current_stock_qty;
8)
UPDATE `product_transaction_batch` AS ptb JOIN product_master AS pm ON pm.product_id = ptb.product_id SET ptb.`quantity_received` = ptb.`quantity_suom_received`/pm.conversion_factor; 

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



CREATE TABLE `product_master_updated` (
  `product_name` varchar(60) NOT NULL COMMENT 'Medicine/Item Name',
  `conversion_factor` tinyint(4) DEFAULT NULL COMMENT 'conversion factor OR packing size',
  `product_gst` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


import csv

ALTER TABLE `product_master_updated` ADD `product_id` INT(10) NULL AFTER `product_name`;




UPDATE product_stock1 SET `batch_exp`=TRIM(LEADING "'" FROM `batch_exp`)



INSERT
INTO
  `product_stock2`(
    `product_id`,
    `batch_no`,
    `batch_exp`,
    `mrp_per_unit`,
    `current_stock_qty`,
    `price_per_unit`,
    `gst_percentage`
  )
SELECT `product_id`,
    `batch_no`,
    `batch_exp`,
    `mrp_per_unit`,
    SUM(`current_stock_qty`),
    `price_per_unit`,
    `gst_percentage`
    FROM product_stock1 GROUP BY product_id



DELETE FROM `product_stock4` WHERE `product_id` NOT IN (1,236,485,497,554,564,653,734,744,787,965,982,1116,1159,1210,1237,1277,1281,1302,1374,1682,1743,1749,1800,1840,1993)



SELECT pm.`product_name`,tpm.PRODUCT_NAME FROM `product_master` as pm ,temp_product_master as tpm WHERE pm.`product_name` = tpm.PRODUCT_NAME

ALTER TABLE `temp_product_master` ADD `d_id` INT(20) NOT NULL AFTER `GENERIC_NAME`;
ALTER TABLE `temp_product_master` ADD `m_id` INT(20) NOT NULL AFTER `MANUFACTURER`;

UPDATE temp_product_master as t JOIN generic_drug_master as g ON t.GENERIC_NAME = g.generic_drug_name SET t.d_id = g.generic_drug_id
UPDATE temp_product_master as t JOIN generic_drug_master as g ON t.GENERIC_NAME = g.generic_drug_name SET t.GENERIC_NAME = g.generic_drug_id

UPDATE temp_product_master as t JOIN manufacturer_master as m ON t.MANUFACTURER = m.manufacturer_name SET t.m_id = m.manufacturer_id
UPDATE temp_product_master as t JOIN manufacturer_master as m ON t.MANUFACTURER = m.manufacturer_name SET t.MANUFACTURER = m.manufacturer_id
---------------------------------------------------------------------------------------------

UPDATE
  product_master AS pm
JOIN
  temp_product_master AS tpm ON pm.product_name = tpm.PRODUCT_NAME
LEFT JOIN
  generic_drug_master AS gdm ON gdm.generic_drug_id = tpm.d_id
SET
  pm.generic_drug_id = gdm.generic_drug_id
----------------------------------------------------------------

UPDATE
    product_master AS pm
JOIN temp_product_master AS tpm
ON
    pm.product_name = tpm.PRODUCT_NAME
LEFT JOIN manufacturer_master AS m
ON
    m.manufacturer_id = tpm.m_id
SET
    pm.`manufacturer_id` = m.manufacturer_id

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

UPDATE product_master as pm JOIN temp_product_master as tpm ON pm.product_name = tpm.PRODUCT_NAME SET pm.conversion_factor = tpm.CONVERSION   ----261


2019-12-28
====================================
ALTER TABLE `temp_product_master` ADD `product_id` INT(10) NOT NULL AFTER `SrNo`;
UPDATE `temp_product_master` as t JOIN product_master as p ON t.`PRODUCT_NAME` = p.product_name SET t.`product_id` = p.product_id


INSERT
INTO
  product_stock(
   `product_id`,
    `batch_no`
  )
SELECT
pm.`product_id`,
   pm.BATCHNO
FROM
  temp_product_master as pm
-----------------------------------------------------

not usable--// UPDATE product_stock as ps JOIN temp_product_master as tpm ON ps.product_id = tpm.`product_id` AND ps.batch_no = tpm.BATCHNO SET ps.`mrp`= tpm.MRP,ps.`gst_percentage` = tpm.GST


UPDATE
  product_stock ps
JOIN
  temp_product_master pm ON pm.product_id = ps.product_id AND pm.BATCHNO = ps.batch_no
SET
  ps.`fyear` = 1920,
  ps.`store_code` = 1,
  ps.batch_exp = pm.EXP_DATE,
  ps.`price` = pm.PUR_RATE,
  ps.`price_per_unit` = ROUND(pm.PUR_RATE / pm.CONVERSION,
  2),
  ps.mrp = ROUND(pm.CURRENT_STOCK * pm.MRP,
  2),
  ps.mrp_per_unit = ROUND(
    pm.SALE_RATE / pm.CONVERSION,
    2
  ),
  ps.gst_percentage = pm.GST,
  ps.current_stock_qty = pm.CURRENT_STOCK,
  ps.current_stock_amt = ROUND(
    pm.CURRENT_STOCK * ROUND(pm.PUR_RATE / pm.CONVERSION,
    2),
    2
  )
WHERE
  pm.product_id = ps.product_id AND pm.BATCHNO = ps.batch_no


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

SELECT `PRODUCT_NAME`, `BATCHNO`, COUNT(*) FROM temp_product_master GROUP BY PRODUCT_NAME, BATCHNO HAVING COUNT(*) > 1;

UPDATE `temp_product_master` SET `EXP_DATE`=STR_TO_DATE(`DATE`, "%d/%m/%Y");

UPDATE product_stock as ps JOIN temp_product_master as tpm ON ps.product_id = tpm.`product_id` AND ps.batch_no = tpm.BATCHNO SET ps.`batch_exp`= tpm.`EXP_DATE`


2019-12-30

UPDATE `product_master` SET `category_id` = '6' WHERE `product_name` LIKE '%INJ%';


UPDATE `temp_product_master` SET `EXP_DATE`=TRIM(LEADING "'" FROM `EXP_DATE`)
UPDATE `temp_product_master` SET `EXP_DATE`=CONCAT("01/",`EXP_DATE`)

UPDATE `medicine_master` m left JOIN product_master pm ON pm.product_id = m.mapping_id SET `unit`=pm.category_id