Inserting and Updating Discounts in tblpricelist

This document provides a step-by-step guide for inserting and updating discounts in the tblpricelist table using bulk data from a CSV file.

Insert Discounts in tblpricelist

Step 1: Prepare Your Data

1. CSV File Structure:

   • Create a .csv file with the following columns:

     • ProductCode: The unique product code (should match the productcode in tblproduct).
     • Discount: The discount value to be applied.
     • BuyerId: The UUID of the buyer (should match the dealerid in tbldealermaster).
     • SellerId: The UUID of the seller (should match the dealerid in tbldealermaster).

   • Ensure that the data in these columns is accurate and correctly formatted.

2. Convert Excel to CSV (if needed):

   • If the data is in Excel format, convert it to CSV.

How to Easily Create the .csv File?

To quickly create the .csv file, follow these steps:

1. Go to Fleeto EV Price List and navigate to the Pricelist section.
2. Filter the price list by any dealer you wish, and then export the filtered price list.
3. In the exported file, you will find the following columns:
   • productcode
   • discount
   • sellername
   • buyername
   • buyercode
   • retailprice
   • sellingprice
4. Now, add two additional columns: buyerid and sellerid.
5. To fill in these columns:
   • Copy the buyername and run the following query to find the buyerid in tbldealermaster:

     SELECT dealerid  
     FROM tbldealermaster 
     WHERE dealername = @buyername;
   • Place the resulting buyerid in the corresponding buyerid column.
   • Similarly, find the sellerid by copying the sellername and running a similar query.
6. Populate both buyerid and sellerid in your .csv file.
7. You can delete the unnecessary columns, and you're ready for the next steps.

Step 2: Create a Temporary Table

To process the data before inserting it into the tblpricelist table, create a temporary table. Use the following SQL command:

CREATE TABLE temp_pricelist_insert (
    productcode text,
    discount numeric,
    buyerid uuid,
    sellerid uuid
);

Step 3: Import CSV File into the Temporary Table

Follow these steps to import the .csv file:

1. Right-click on the temp_pricelist_insert table.
2. Select the Import option.
3. Choose and upload the .csv file, then click OK.

warning

Before proceeding with the next step to execute the insert query, please ensure that the following select query fetches the exact rows you want to insert. This step is crucial to avoid inserting incorrect data.

SELECT
    uuid_generate_v4() AS pricelistid,         -- This will generate a new UUID for each pricelist entry
    tpi.sellerid,
    p.productid,
    tpi.discount,
    tpi.buyerid,
    'script' AS logusername,            
    NOW() AS logdts                
FROM temp_pricelist_insert tpi
JOIN public.tblproduct p ON p.productcode = tpi.productcode
JOIN public.tbldealermaster b1 ON b1.dealerid = tpi.sellerid
JOIN public.tbldealermaster b2 ON b2.dealerid = tpi.buyerid;

And also make sure that the number of rows returned by this select query matches the number of rows you expect to insert into the tblpricelist.

Step 4: Insert Data into tblpricelist

Now insert the data from the temporary table into tblpricelist:

INSERT INTO public.tblpricelist (pricelistid, sellerid, productid, discount, buyerid, logusername, logdts)
SELECT
    uuid_generate_v4(),         -- Generate a new UUID for each pricelist entry
    tpi.sellerid,
    p.productid,
    tpi.discount,
    tpi.buyerid,
    'script',            
    NOW()                
FROM temp_pricelist_insert tpi
JOIN public.tblproduct p ON p.productcode = tpi.productcode
JOIN public.tbldealermaster b1 ON b1.dealerid = tpi.sellerid
JOIN public.tbldealermaster b2 ON b2.dealerid = tpi.buyerid;

Step 5: Clean Up

After the insert, drop the temporary table to clean up:

DROP TABLE temp_pricelist_insert;

Update Discounts in tblpricelist

Step 1: Prepare Your Data

1. CSV File Structure:

   • Create a .csv file with the following columns:

     • SellerName: The name of the seller.
     • SellerId: The discount value to be applied.
     • BuyerName: The name of the buyer.
     • BuyerId: The UUID of the buyer (should match the dealerid in tbldealermaster).
     • ProductCode: The unique product code (should match the productcode in tblproduct).
     • ProductName: The name of the product (for reference).
     • NewDiscount: The new discount value to be updated in the tblpricelist.

   • Ensure that the data in these columns is accurate and correctly formatted.

2. Convert Excel to CSV (if needed):

   • If the data is in Excel format, convert it to CSV.

Step 2: Create a Temporary Table

Create a temporary table to hold the data from the Excel file for easier processing. This allows you to join with other tables as needed. Run the following SQL command:

CREATE TABLE temp_pricelist_update (
    sellername text,
    buyername text,
    productcode text,
    productname text,
    newdiscount numeric,
    buyerid uuid,
    sellerid uuid
);

Step 3: Import CSV File into the Temporary Table

Follow these steps to import the .csv file:

1. Right-click on the temp_pricelist_update table.
2. Select the Import option.
3. Choose and upload the .csv file, then click OK.

Step 4: Validate Data Before Update

Before performing the update, verify the rows to be updated:

SELECT 
    pl.pricelistid,
    pl.sellerid,
    tpu.sellerid,
    pl.buyerid,
    tpu.buyerid,
    pl.discount AS current_discount,
    tpu.newdiscount AS new_discount
FROM 
    public.tblpricelist pl
JOIN 
    temp_pricelist_update tpu ON pl.sellerid = tpu.sellerid 
    AND pl.buyerid = tpu.buyerid 
    AND pl.productid = (SELECT productid FROM public.tblproduct WHERE productcode = tpu.productcode)
WHERE 
    pl.discount <> tpu.newdiscount;

Step 5: Update the tblpricelist Table

Run the update query, but only for rows where the discount has changed:

UPDATE public.tblpricelist pl
SET discount = tpu.newdiscount, 
    logusername = 'script',   
    logdts = NOW()        
FROM temp_pricelist_update tpu
WHERE pl.sellerid = tpu.sellerid 
  AND pl.buyerid = tpu.buyerid 
  AND pl.productid = (SELECT productid FROM public.tblproduct WHERE productcode = tpu.productcode)
  AND pl.discount <> tpu.newdiscount;

Step 5: Clean Up

After the update, drop the temporary table to clean up:

DROP TABLE temp_pricelist_update;