download portfolio

    def download_portfolio(self):
        '''
        \r[GeoPap]
        \rIt sends the appropriate query to the database
        \rand fetches the answer as a file
        '''

        params = request.get_json()


        '''
        input example:
        {
            'regions': ['China'],
            'markets': ['Beijing', 'Changsha', 'Chengdu', 'Chongqing', 'Dongguan', 'Guangzhou', 'Hangzhou', 'Hong Kong', 'N/A', 'Nanjing', 'Qingdao', 'Shanghai', 'Shenyang', 'Shenzhen', 'Suzhou', 'Taipei', 'Tianjin', 'Wuhan', 'Wuxi', "Xi'an", 'Xiamen'],
            'gates': ['A', 'B', 'C', 'D'],
            'comparables': True
        }
        '''

        # remove invalid entries
        params['markets'].remove("N/A")


        columns=['score_order',
                'region',
                'stargate_market',
                'target_region',
                'target_market',
                'gate',
                'subject_property',
                'comparable_property',
                'price_1p',
                'avg_price_per_desk',
                'occupancy',
                'discount']




        # Note the problematic entries
        problematic=[]
        for mark in params['markets']:
            if "\'" in mark:
                problematic.append(mark)



        # print("These have invalid characters:\n%s"%(problematic))


        regions="target_region=\'"+"\' OR target_region=\'".join(params['regions']).lstrip("OR ")+"\'"
        markets="target_market=\'"+"\' OR target_market=\'".join(params['markets']).lstrip("OR ")+"\'"
        gates="gate=\'"+"\' OR gate=\'".join(params['gates']).lstrip("OR ")+"\'"
        comparables_bool=params['comparables']

        # Replace the problematic entries with the unproblematic ones
        for word in problematic:
            markets=markets.replace(word,word.replace("\'","\'\'"))


        # The basis of the query
        query='''SELECT * FROM portofolio_top5
                WHERE
                (%s) AND
                (%s) AND
                (%s)
                '''%(
                    regions,
                    markets,
                    gates)

        # if comparables_bool==True then all score_order's are selected
        # otherwise only score_order==0 are selected
        if comparables_bool:
            query+='''AND \n\t('''
            for i in range(7):
                query+='''score_order=%s OR '''%(i)
            query=query[:-4]
            query+=")"
        else:
            query+='''AND \n\t(score_order=0)'''


        # order first by subject_property and then by score_order
        query+='''\n\tORDER BY\n\t (subject_property,score_order)'''


        # print("-----------------------")
        # print("This is the query that is about to be sent:\n%s"%(query))



        with self.db_connector.get_db_cursor(cursor_factory=NamedTupleCursor) as cur:
            cur.execute(query)
            portfolio=pd.DataFrame(data=cur.fetchall())



        portfolio.columns=columns


        # round up the avg_price_per_desk for those that score_order=0
        rounded_prices=portfolio.loc[portfolio['score_order']==0,:].loc[:,'avg_price_per_desk'].round()

        columns.remove('avg_price_per_desk')

        portfolio.loc[portfolio['score_order']==0,:]=portfolio.loc[portfolio['score_order']==0,columns]


        portfolio.loc[portfolio['score_order']==0,'avg_price_per_desk']=rounded_prices



        # Make it pretty
        columns2delete=['score_order','target_region','target_market']
        portfolio.drop(labels=columns2delete, axis="columns", inplace=True)

        portfolio.rename(index=str, columns={
            # "score_order": "score_order",# This is deleted
            "region": "Region",
            "stargate_market": "Market",
            # "target_region": "target_region",# This is deleted
            # "target_market": "target_market",# This is deleted
            "gate": "Gate",
            "subject_property": "Subject Property",
            "comparable_property": "Comparable Property",
            "price_1p": "1P Market Price",
            "avg_price_per_desk": "Avg market price (USD)",
            "occupancy": "Occupancy (this mo)",
            "discount": "Discount (this mo)"}, inplace=True)

        print(portfolio)

        # Download
        output = BytesIO()
        writer = pd.ExcelWriter(output, engine='xlsxwriter')
        portfolio.to_excel(writer, sheet_name='PortfolioView')

        writer.save()
        output.seek(0)

        filename="PortfolioView.xlsx"

        return send_file(output, attachment_filename=filename,as_attachment=True)


        # avala = {'avala': 'avala_katravala_vrika_tripa_ta_vala'}

        # return json.dumps(avala)