Algorithm Work

Please look at the attached spreadsheet What I need is for somebody to build a website tool where I upload 2 CSV files (sheet 1 and sheet 5 on the spreadsheet). The programme then runs and produces 3 output CSV files (sheets 2, 3 and 4 on the spreadsheet). According to the following instructions The business ID is determined by an input, in this case it is 106648. looking at the outputs, it needs to take one row at a time from the input. productoption the first column is a consecutive number, starting at 2000 the next column is the productID, so it needs to look at the input row where the productId is 1004362 the name is column B from the input, in this case Body Colour it then performs the operation on the next sheet, optionvalue the first column is a consecutive number, starting at 5000 the next column is the optionId which is taken from productoption (the ID there), in this case 2000 the name is then taken from column C of the input table. Now this column also contains "Please Specify" which needs to be ignored. So the name is either "Grey" or "White" We do one at a time, so this time it is Grey But then because there is another option it needs to create a new row 5001, 2000, 106648, White Once it has finished (which it now has because there are only 2) it then moves onto the third output, attribute attribute the first column is a consecutive number, starting at 7000 the next is the productId, 1004362 The next bit is the tricky part. What the system needs to do is put all of the available variants of a product in here, accounting for all the possible permutations e.g. Product Price = £100.00 Colour: Blue (+£10), Green (+£20) (option ID 1) (value ID 1, 2) Size: Small (+£10), Large (+£20 (option ID 2) (value ID 3, 4) Material: Plastic (+£10), Wood (+£20). (option ID 3) (value ID 5, 6) If all options are available then there are 8 permutations in total. Blue, Small, Plastic £130 (1, 3, 5) Blue, Small, Wood £140 (1, 3, 6) Blue, Large, Plastic £140 (1, 4, 5) Blue, Large, Wood £150 (1, 4, 6) Green, Small, Plastic £140 (2, 3, 5) Green, Small, Wood £150 (2, 3, 6) Green, Large, Plastic £150 (2, 4, 5) Green, Large, Wood £160 (2, 4, 6) Each permutation has a separate row in the table, e.g. id productId optionIds valueIds price 1 1 1, 2, 3 1, 3, 5 130 2 1 1, 2, 3 1, 3, 6 140 3 1 1, 2, 3 1, 4, 5 140 4 1 1, 2, 3 1, 4, 6 150 5 1 1, 2, 3 2, 3, 5 140 6 1 1, 2, 3 2, 3, 6 150 7 1 1, 2, 3 2, 4, 5 150 8 1 1, 2, 3 2, 4, 6 160 So the system you build needs first to create the available permutations, e.g. If an item has 2 options: Option 1 choices: Required, Not Required Option 2 choices: Required, Not Required Then there are 4 possible outcomes Required, Not Required Required, Required Not Required, Not Required Not Required, Required Each one has to have a separate row in the attribute output. Each one has a unique id, and the corresponding productId Then it needs to enter the option Ids (as taken from the product option output) and then the value IDs, as taken from the option value output. I've done an example for product 1004353 The final thing is the price column (you can ignore all of the remaining columns in this and just leave them empty). So, it needs to look up the current price from the "productId and price" input. Column D from the input table shows what the additional price for the option is. What the system needs to work out is the total cost for the product with all the additional option prices taken into consideration. The example for 1004353 is in the table. (for 1004362) there is no additional cost for either option so the price is the same as the main product price.