def generate_stat_history(setCheck, boolCheck): dataArray = [] if setCheck == 'Alpha' and boolCheck == 'Power': query = (f""" SELECT completed_product_nick, avg(completed_product_prices), min(completed_product_prices), max(completed_product_prices), count(completed_product_prices), CAST(sum(completed_product_end::date - completed_product_start::date) as double precision)/count(completed_product_end) as average_length, sum(completed_product_prices) FROM completed_products WHERE completed_product_nick IN ('{setCheck} Black Lotus', '{setCheck} Mox Sapphire', '{setCheck} Mox Jet', '{setCheck} Mox Pearl', '{setCheck} Mox Ruby', '{setCheck} Mox Emerald', '{setCheck} Timetwister', '{setCheck} Ancestral Recall', '{setCheck} Time Walk') AND completed_product_end::date > current_timestamp - interval '90' day GROUP BY completed_product_nick; """) data = fetch_data(query) dataArray.append(data.values) return dataArray elif setCheck != 'Alpha' and boolCheck == 'Power': query = (f""" SELECT completed_product_nick, avg(completed_product_prices), min(completed_product_prices), max(completed_product_prices), count(completed_product_prices), CAST(sum(completed_product_end::date - completed_product_start::date) as double precision)/count(completed_product_end) as average_length, sum(completed_product_prices) FROM completed_products WHERE completed_product_nick IN ('{setCheck} Black Lotus MTG', '{setCheck} Mox Sapphire', '{setCheck} Mox Jet', '{setCheck} Mox Pearl', '{setCheck} Mox Ruby', '{setCheck} Mox Emerald', '{setCheck} Timetwister', '{setCheck} Ancestral Recall', '{setCheck} Time Walk') AND completed_product_end::date > current_timestamp - interval '90' day GROUP BY completed_product_nick; """) data = fetch_data(query) dataArray.append(data.values) return dataArray elif boolCheck == 'Duals': query = (f""" SELECT completed_product_nick, avg(completed_product_prices), min(completed_product_prices), max(completed_product_prices), count(completed_product_prices), CAST(sum(completed_product_end::date - completed_product_start::date) as double precision)/count(completed_product_end) as average_length, sum(completed_product_prices) FROM completed_products WHERE completed_product_nick IN ('{setCheck} Tundra MTG', '{setCheck} Underground Sea MTG', '{setCheck} Badlands MTG', '{setCheck} Taiga MTG', '{setCheck} Savannah MTG', '{setCheck} Scrubland MTG', '{setCheck} Volcanic Island MTG', '{setCheck} Bayou MTG', '{setCheck} Plateau MTG', '{setCheck} Tropical Island MTG') AND completed_product_end::date > current_timestamp - interval '90' day GROUP BY completed_product_nick; """) data = fetch_data(query) dataArray.append(data.values) return dataArray
def generate_index_history(setCheck, setId, boolCheck): dataArray = [] if setCheck == 'Alpha' and boolCheck == 'Power': query = (f""" SELECT '{setCheck}', '{setId}', sum(stats.avger), sum(stats.miner), sum(stats.maxer), avg(stats.lengther), sum(stats.counter) ,sum(stats.sumer) FROM (SELECT active_product_nick, avg(active_product_prices) as avger, min(active_product_prices) as miner, max(active_product_prices) as maxer, count(active_product_prices) as counter, CAST(sum(current_timestamp::date - active_product_start::date) as double precision)/count(active_product_end) as lengther, sum(active_product_prices) as sumer FROM active_products WHERE active_product_nick IN ('{setCheck} Black Lotus', '{setCheck} Mox Sapphire', '{setCheck} Mox Jet', '{setCheck} Mox Pearl', '{setCheck} Mox Ruby', '{setCheck} Mox Emerald', '{setCheck} Timetwister', '{setCheck} Ancestral Recall', '{setCheck} Time Walk') GROUP BY active_product_nick) stats; """) data = fetch_data(query) dataArray.append(data.values) return dataArray elif setCheck != 'Alpha' and boolCheck == 'Power': query = (f""" SELECT '{setCheck}', '{setId}', sum(stats.avger), sum(stats.miner), sum(stats.maxer), avg(stats.lengther), sum(stats.counter) ,sum(stats.sumer) FROM (SELECT active_product_nick, avg(active_product_prices) as avger, min(active_product_prices) as miner, max(active_product_prices) as maxer, count(active_product_prices) as counter, CAST(sum(current_timestamp::date - active_product_start::date) as double precision)/count(active_product_end) as lengther, sum(active_product_prices) as sumer FROM active_products WHERE active_product_nick IN ('{setCheck} Black Lotus MTG', '{setCheck} Mox Sapphire', '{setCheck} Mox Jet', '{setCheck} Mox Pearl', '{setCheck} Mox Ruby', '{setCheck} Mox Emerald', '{setCheck} Timetwister', '{setCheck} Ancestral Recall', '{setCheck} Time Walk') GROUP BY active_product_nick) stats; """) data = fetch_data(query) dataArray.append(data.values) return dataArray elif boolCheck == 'Duals': query = (f""" SELECT '{setCheck}', '{setId}', sum(stats.avger), sum(stats.miner), sum(stats.maxer), avg(stats.lengther), sum(stats.counter) ,sum(stats.sumer) FROM (SELECT active_product_nick, avg(active_product_prices) as avger, min(active_product_prices) as miner, max(active_product_prices) as maxer, count(active_product_prices) as counter, CAST(sum(current_timestamp::date - active_product_start::date) as double precision)/count(active_product_end) as lengther, sum(active_product_prices) as sumer FROM active_products WHERE active_product_nick IN ('{setCheck} Tundra MTG', '{setCheck} Underground Sea MTG', '{setCheck} Badlands MTG', '{setCheck} Taiga MTG', '{setCheck} Savannah MTG', '{setCheck} Scrubland MTG', '{setCheck} Volcanic Island MTG', '{setCheck} Bayou MTG', '{setCheck} Plateau MTG', '{setCheck} Tropical Island MTG') GROUP BY active_product_nick) stats; """) data = fetch_data(query) dataArray.append(data.values) return dataArray