Further to my last post, I have in each of the lab products added an identity along the lines of code='unique lab code eg HIS1-3', description='LabCode=VPG'. I only use one external lab but could have many if the description was different for each eg 'LabCode=IDEXX,GRIBBLES...'

Because I am a coward I take a backup of the server database and do all of the following on my workstation.

All of the below instructions are based on my work station being linux. Windows users would have to adjust things. They may actually not need to do some of the file moves as in linux mysql has very restricted file permissions and can only read and write to certain places.

In mysql workbench import the backup of the production server database. Move the product file from the lab to:

'/var/lib/mysql-files/VPGPriceList2021.csv'

Export the prices of interest from openVPMS by using the search function (ie 'Lab-VPG-). Save this file and move to something like:

'/var/lib/mysql-files/products-2022-02-18.csv'

Create a stored proc along the lines of:

CREATE DEFINER=`chris`@`localhost` PROCEDURE `updateLabPrices`(IN labName VARCHAR (255)

                    )

BEGIN

DECLARE whereSortName varchar(255);

Select concat('LabCode-', TRIM(labName)) into whereSortName;

    DROP TABLE IF exists temp_price_tbl;
    
    CREATE TABLE IF NOT EXISTS temp_price_tbl as (
    select e.entity_id as pid,
       e.name as product,
       ei.description,
       ei.identity as reorderCode,
       p.product_price_id as 'Unit Price Id',
       p.price as 'Unit Price',
       ppd.cost_price,  markup
from   entities e
left join entity_links r
       on e.entity_id = r.source_id and r.arch_short_name = "entityLink.productType"
left join entities pt
       on r.target_id = pt.entity_id
left join entity_identities ei on ei.entity_id = e.entity_id
left join product_prices p on p.product_id = e.entity_id
left join
                (select product_price_details.product_price_id, product_price_details.name, product_price_details.value as cost_price from product_price_details

                inner join product_prices on product_price_details.product_price_id = product_prices.product_price_id where product_price_details.name="cost") ppd

                on ppd.product_price_id=p.product_price_id

            left join

                (select product_price_details.product_price_id, product_price_details.name, product_price_details.value as markup from product_price_details

                inner join product_prices on product_price_details.product_price_id = product_prices.product_price_id where product_price_details.name="markup") ppdm

                on ppdm.product_price_id=p.product_price_id

    
where e.arch_short_name='product.service'
and e.name like '%vpg%'
and e.active=true);

DROP TABLE IF EXISTS temp_wholesaler_prices;
                    

   
    CREATE TABLE IF NOT EXISTS temp_wholesaler_prices (

        wholesaler_code VARCHAR(255) NOT NULL,

        wholesaler_name VARCHAR(255),

        wholesaler_list_price DECIMAL (10,4),

        primary key (wholesaler_code)

    );

    

    

    

END

Execute the following in a query window:

call openvpmstest.updateLabPrices('VPG');

LOAD DATA INFILE '/var/lib/mysql-files/VPGPriceList2021.csv'
        INTO TABLE temp_wholesaler_prices
        FIELDS TERMINATED BY '\t' ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE 1 ROWS;
        
LOAD DATA INFILE '/var/lib/mysql-files/products-2022-02-18.csv'
        INTO TABLE temp_openvpms_prices
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE 1 ROWS
        (`Product Id`,
        `Product Name`,
    `Product Printed Name`,
    @FixedPriceId,
    @FixedPrice,
    @FixedCost,
    @FixedPriceMaxDiscount,
    @FixedPriceStartDate,    
    @FixedPriceEndDate,
    @DefaultFixedPrice,
    `Fixed Price Groups`,
    @UnitPriceId,    
    @UnitPrice,
    @UnitCost,
    @UnitPriceMaxDiscount,
    @UnitPriceStartDate,
    @UnitPriceEndDate,
    `Unit Price Groups`,
    `Tax Rate`,
    `Notes` )
    SET `Fixed Price Id` = nullif(@FixedPriceId,''),
    `Fixed Price`= nullif(@FixedPrice,''),
    `Fixed Cost` = nullif(@FixedCost,''),
    `Fixed Price Max Discount` = nullif(@FixedPriceMaxDiscount,''),
    `Fixed Price Start Date` = nullif(@FixedPriceStartDate,''),
    `Fixed Price End Date` = nullif(@FixedPriceEndDate,''),
    `Default Fixed Price` = nullif(@DefaultFixedPrice,''),
    `Unit Price Id` = nullif(@UnitPriceId,''),
    `Unit Price End Date` = nullif(@UnitPriceEndDate,''),
    `Unit Price` = nullif(@UnitPrice,''),
    `Unit Cost` = nullif(@UnitCost,''),
    `Unit Price Max Discount` = nullif(@UnitPriceMaxDiscount,''),
    `Unit Price Start Date` = nullif(@UnitPriceStartDate,'')
    ;
    
alter table temp_price_tbl add column new_cost_price decimal (10,3);

-- select * from temp_price_tbl inner join temp_wholesaler_prices
-- on temp_price_tbl.reorderCode = temp_wholesaler_prices.wholesaler_code
-- where
-- temp_price_tbl.cost_price < temp_wholesaler_prices.wholesaler_list_price;

update temp_price_tbl inner join temp_wholesaler_prices
on temp_price_tbl.reorderCode = temp_wholesaler_prices.wholesaler_code
set temp_price_tbl.new_cost_price = temp_wholesaler_prices.wholesaler_list_price
where
temp_price_tbl.cost_price < temp_wholesaler_prices.wholesaler_list_price;

delete temp_openvpms_prices from temp_openvpms_prices inner join temp_price_tbl on temp_price_tbl.pid = temp_openvpms_prices.`Product ID`
where temp_price_tbl.new_cost_price is null;

update temp_openvpms_prices inner join temp_price_tbl on temp_openvpms_prices.`Product ID` = temp_price_tbl.pid
set `Unit Price Start Date` = DATE(now()),
    temp_openvpms_prices.`Unit Price Id` = null,
    temp_openvpms_prices.`Unit Cost`=temp_price_tbl.new_cost_price,
    temp_openvpms_prices.`Unit Price` = round(temp_price_tbl.new_cost_price*(1+temp_price_tbl.markup/100),3);
    
select `Product Id`,
    IFNULL(`Product Name`,'')as `Product Name`,
    IFNULL(`Product Printed Name`,'')as `Product Printed Name`,
    IFNULL(`Fixed Price Id`,'') as `Fixed Price Id`,
    IFNULL(`Fixed Price`,'')as `Fixed Price`,
    IFNULL(`Fixed Cost`,'')as `Fixed Cost`,
    IFNULL(`Fixed Price Max Discount`,'')as `Fixed Price Max Discount`,
    IFNULL(`Fixed Price Start Date`,'')as `Fixed Price Start Date`,
    IFNULL(`Fixed Price End Date`,'')as `Fixed Price End Date`,
    IFNULL(`Default Fixed Price`,'')as `Default Fixed Price`,
    IFNULL(`Fixed Price Groups`,'')as `Fixed Price Groups`,
    IFNULL(`Unit Price Id`,'')as `Unit Price Id`,
    IFNULL(`Unit Price`,'')as `Unit Price`,
    IFNULL(`Unit Cost`,'')as `Unit Cost`,
    IFNULL(`Unit Price Max Discount`,'')as `Unit Price Max Discount`,
    IFNULL(`Unit Price Start Date`,'')as `Unit Price Start Date`,
    IFNULL(`Unit Price End Date`,'')as `Unit Price End Date`,    
    IFNULL(`Unit Price Groups`,'')as `Unit Price Groups`,    
    IFNULL(`Tax Rate`,'')as `Tax Rate`,    
    IFNULL(`Notes`,'')as `Notes`
    
    from temp_openvpms_prices where `Unit Price Id` is null;
    
drop table if exists temp_openvpms_prices;
drop table if exists temp_wholesaler_prices;
drop table if exists temp_price_tbl;

In the results window of mysql workbench export the results of the final select statement. Save this as a csv. Import this file using openVPMS import prices. This gives you a visual sanity check on what you are about to do. Whilst it would have been far less trouble to have the scripts directly update the prices in openVPMS I really like the visual confirmation of what is going to change in the production database.

All of the above is as rough as guts and very much tailored to my implementation. I'm a vet not a programmer so I leave it to others to improve it and make it generic if they choose.

Chris