Spaces:
Sleeping
Sleeping
| from flask import Flask, request, jsonify, render_template_string | |
| from google.oauth2.service_account import Credentials | |
| from googleapiclient.discovery import build | |
| from googleapiclient.http import MediaIoBaseUpload | |
| import os | |
| import io | |
| import datetime | |
| import json | |
| app = Flask(__name__) | |
| SPREADSHEET_ID = '1rcIJflbC1VIc70F6dnASLFvGQ90TXHhCx0iyxsXR7Ww' | |
| FOLDER_ID = '1brmLNqOMCvRS0TDY6ECHvIBmlRx8pcPz' | |
| # Create service account credentials from environment variable | |
| CREDS_JSON = os.environ.get('GOOGLE_CREDENTIALS') | |
| if CREDS_JSON: | |
| CREDS_INFO = json.loads(CREDS_JSON) | |
| CREDENTIALS = Credentials.from_service_account_info( | |
| CREDS_INFO, | |
| scopes=['https://www.googleapis.com/auth/spreadsheets', | |
| 'https://www.googleapis.com/auth/drive.file'] | |
| ) | |
| def get_sheet_names(): | |
| try: | |
| service = build('sheets', 'v4', credentials=CREDENTIALS) | |
| spreadsheet = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute() | |
| return [sheet['properties']['title'] for sheet in spreadsheet['sheets']] | |
| except Exception as e: | |
| print(f"Error getting sheet names: {str(e)}") | |
| return ['Sheet1'] | |
| def ensure_headers(sheet_name): | |
| try: | |
| sheets_service = build('sheets', 'v4', credentials=CREDENTIALS) | |
| headers = [ | |
| ['項目 Item', '總金額 Total Cost', '發票檔案 Receipt File', '時間 Timestamp', 'IP位址 IP', '付款明細 Payment Details'] | |
| ] | |
| sheets_service.spreadsheets().values().update( | |
| spreadsheetId=SPREADSHEET_ID, | |
| range=f'{sheet_name}!A1:F1', | |
| valueInputOption='RAW', | |
| body={'values': headers} | |
| ).execute() | |
| requests = [{ | |
| 'repeatCell': { | |
| 'range': { | |
| 'sheetId': 0, | |
| 'startRowIndex': 0, | |
| 'endRowIndex': 1 | |
| }, | |
| 'cell': { | |
| 'userEnteredFormat': { | |
| 'horizontalAlignment': 'CENTER', | |
| 'textFormat': { | |
| 'bold': True | |
| } | |
| } | |
| }, | |
| 'fields': 'userEnteredFormat(horizontalAlignment,textFormat)' | |
| } | |
| }] | |
| sheets_service.spreadsheets().batchUpdate( | |
| spreadsheetId=SPREADSHEET_ID, | |
| body={'requests': requests} | |
| ).execute() | |
| except Exception as e: | |
| print(f"Error setting headers: {str(e)}") | |
| HTML_TEMPLATE = ''' | |
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <title>Purchase Record Form</title> | |
| <meta charset="UTF-8"> | |
| <style> | |
| body { | |
| font-family: Arial, sans-serif; | |
| max-width: 800px; | |
| margin: 20px auto; | |
| padding: 20px; | |
| } | |
| .form-group { | |
| margin-bottom: 20px; | |
| } | |
| label { | |
| display: block; | |
| margin-bottom: 8px; | |
| font-weight: bold; | |
| } | |
| input, select { | |
| width: 100%; | |
| padding: 10px; | |
| border: 1px solid #ddd; | |
| border-radius: 4px; | |
| font-size: 16px; | |
| margin-bottom: 10px; | |
| } | |
| button { | |
| background-color: #4CAF50; | |
| color: white; | |
| padding: 12px 24px; | |
| border: none; | |
| border-radius: 4px; | |
| cursor: pointer; | |
| font-size: 16px; | |
| } | |
| button:hover { | |
| background-color: #45a049; | |
| } | |
| button:disabled { | |
| background-color: #cccccc; | |
| cursor: not-allowed; | |
| } | |
| #status { | |
| margin-top: 20px; | |
| padding: 15px; | |
| border-radius: 4px; | |
| display: none; | |
| } | |
| .success { | |
| background-color: #dff0d8; | |
| color: #3c763d; | |
| } | |
| .error { | |
| background-color: #f2dede; | |
| color: #a94442; | |
| } | |
| .person-entry { | |
| border: 1px solid #ddd; | |
| padding: 15px; | |
| margin-bottom: 15px; | |
| border-radius: 4px; | |
| } | |
| .remove-person { | |
| background-color: #dc3545; | |
| margin-top: 10px; | |
| width: auto; | |
| } | |
| #add-person { | |
| margin-bottom: 20px; | |
| background-color: #17a2b8; | |
| width: auto; | |
| } | |
| .total-cost { | |
| font-size: 1.2em; | |
| font-weight: bold; | |
| margin: 20px 0; | |
| } | |
| </style> | |
| </head> | |
| <body> | |
| <h2>購買記錄表單 Purchase Record Form</h2> | |
| <form id="purchaseForm"> | |
| <div class="form-group"> | |
| <label for="sheetSelect">選擇工作表 Select Sheet:</label> | |
| <select id="sheetSelect" name="sheetName" required> | |
| {% for sheet in sheet_names %} | |
| <option value="{{ sheet }}">{{ sheet }}</option> | |
| {% endfor %} | |
| </select> | |
| </div> | |
| <div class="form-group"> | |
| <label for="product">購買項目 Item:</label> | |
| <input type="text" id="product" name="product" required> | |
| </div> | |
| <div class="form-group"> | |
| <label for="receipt">發票 Receipt:</label> | |
| <input type="file" id="receipt" name="receipt" accept="image/*,.pdf" required> | |
| </div> | |
| <div id="people-container"> | |
| <!-- Person entries will be added here --> | |
| </div> | |
| <button type="button" id="add-person">新增付款人 Add Person</button> | |
| <div class="total-cost"> | |
| 總金額 Total Cost: <span id="total">0</span> | |
| </div> | |
| <button type="submit" id="submitBtn">提交 Submit</button> | |
| </form> | |
| <div id="status"></div> | |
| <script> | |
| let personCounter = 0; | |
| const maxPersons = 10; // Maximum number of persons allowed | |
| function createPersonEntry() { | |
| const container = document.createElement('div'); | |
| container.className = 'person-entry'; | |
| container.innerHTML = ` | |
| <div class="form-group"> | |
| <label>姓名 Name:</label> | |
| <input type="text" name="names[]" required> | |
| </div> | |
| <div class="form-group"> | |
| <label>學號 Student ID:</label> | |
| <input type="text" name="studentIds[]" required> | |
| </div> | |
| <div class="form-group"> | |
| <label>金額 Cost:</label> | |
| <input type="number" name="costs[]" required onchange="updateTotal()"> | |
| </div> | |
| <button type="button" class="remove-person" onclick="removePerson(this)">移除 Remove</button> | |
| `; | |
| return container; | |
| } | |
| function updateTotal() { | |
| const costs = Array.from(document.getElementsByName('costs[]')) | |
| .map(input => Number(input.value) || 0); | |
| const total = costs.reduce((sum, cost) => sum + cost, 0); | |
| document.getElementById('total').textContent = total; | |
| } | |
| function removePerson(button) { | |
| button.parentElement.remove(); | |
| updateTotal(); | |
| document.getElementById('add-person').disabled = document.getElementsByClassName('person-entry').length >= maxPersons; | |
| } | |
| document.getElementById('add-person').addEventListener('click', () => { | |
| const container = document.getElementById('people-container'); | |
| if (container.children.length < maxPersons) { | |
| container.appendChild(createPersonEntry()); | |
| personCounter++; | |
| document.getElementById('add-person').disabled = container.children.length >= maxPersons; | |
| } | |
| }); | |
| // Add initial person entry | |
| document.getElementById('add-person').click(); | |
| document.getElementById('purchaseForm').addEventListener('submit', async (e) => { | |
| e.preventDefault(); | |
| const submitBtn = document.getElementById('submitBtn'); | |
| if (submitBtn.disabled) return; | |
| if (document.getElementsByName('names[]').length === 0) { | |
| alert('請至少新增一個付款人 Please add at least one person'); | |
| return; | |
| } | |
| submitBtn.disabled = true; | |
| const status = document.getElementById('status'); | |
| status.style.display = 'block'; | |
| status.textContent = '提交中... Submitting...'; | |
| status.className = ''; | |
| const formData = new FormData(e.target); | |
| try { | |
| const response = await fetch('/submit', { | |
| method: 'POST', | |
| body: formData | |
| }); | |
| const result = await response.json(); | |
| if (result.success) { | |
| status.className = 'success'; | |
| status.textContent = '提交成功！ ' + result.message; | |
| e.target.reset(); | |
| document.getElementById('people-container').innerHTML = ''; | |
| document.getElementById('add-person').click(); | |
| document.getElementById('total').textContent = '0'; | |
| } else { | |
| status.className = 'error'; | |
| status.textContent = '錯誤：' + result.message; | |
| } | |
| } catch (error) { | |
| status.className = 'error'; | |
| status.textContent = '提交錯誤 Error submitting form'; | |
| } finally { | |
| submitBtn.disabled = false; | |
| } | |
| }); | |
| </script> | |
| </body> | |
| </html> | |
| ''' | |
| def index(): | |
| sheet_names = get_sheet_names() | |
| return render_template_string(HTML_TEMPLATE, sheet_names=sheet_names) | |
| def submit(): | |
| try: | |
| sheet_name = request.form['sheetName'] | |
| product = request.form['product'] | |
| file = request.files['receipt'] | |
| names = request.form.getlist('names[]') | |
| student_ids = request.form.getlist('studentIds[]') | |
| costs = request.form.getlist('costs[]') | |
| # Calculate total | |
| total_cost = sum(float(cost) for cost in costs) | |
| # Create payment details string | |
| payment_details = [] | |
| for name, student_id, cost in zip(names, student_ids, costs): | |
| payment_details.append(f"{name}({student_id}): ${cost}") | |
| payment_details_str = " | ".join(payment_details) | |
| # Initialize Drive service | |
| drive_service = build('drive', 'v3', credentials=CREDENTIALS) | |
| # Check if subfolder exists, if not create it | |
| subfolder_name = sheet_name | |
| subfolder_query = f"name = '{subfolder_name}' and '{FOLDER_ID}' in parents and mimeType = 'application/vnd.google-apps.folder' and trashed = false" | |
| results = drive_service.files().list(q=subfolder_query).execute() | |
| items = results.get('files', []) | |
| if not items: | |
| # Create new subfolder | |
| folder_metadata = { | |
| 'name': subfolder_name, | |
| 'mimeType': 'application/vnd.google-apps.folder', | |
| 'parents': [FOLDER_ID] | |
| } | |
| subfolder = drive_service.files().create( | |
| body=folder_metadata, | |
| fields='id' | |
| ).execute() | |
| subfolder_id = subfolder.get('id') | |
| else: | |
| subfolder_id = items[0]['id'] | |
| # Upload file to the subfolder | |
| file_metadata = { | |
| 'name': f"{datetime.datetime.now().strftime('%Y%m%d_%H%M%S')}_{file.filename}", | |
| 'parents': [subfolder_id] # Use subfolder ID instead of main folder ID | |
| } | |
| media = MediaIoBaseUpload( | |
| io.BytesIO(file.read()), | |
| mimetype=file.content_type, | |
| resumable=True | |
| ) | |
| uploaded_file = drive_service.files().create( | |
| body=file_metadata, | |
| media_body=media, | |
| fields='id,webViewLink' | |
| ).execute() | |
| file_url = uploaded_file.get('webViewLink', '') | |
| # Ensure headers exist | |
| ensure_headers(sheet_name) | |
| # Update Google Sheet | |
| sheets_service = build('sheets', 'v4', credentials=CREDENTIALS) | |
| timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') | |
| ip_address = request.remote_addr | |
| # Create single row entry | |
| values = [[ | |
| product, | |
| total_cost, | |
| file_url, | |
| timestamp, | |
| ip_address, | |
| payment_details_str | |
| ]] | |
| body = {'values': values} | |
| sheets_service.spreadsheets().values().append( | |
| spreadsheetId=SPREADSHEET_ID, | |
| range=f'{sheet_name}!A:F', | |
| valueInputOption='USER_ENTERED', | |
| body=body | |
| ).execute() | |
| return jsonify({'success': True, 'message': 'Data submitted successfully!'}) | |
| except Exception as e: | |
| return jsonify({'success': False, 'message': f'Error: {str(e)}'}) | |
| if __name__ == '__main__': | |
| app.run(host='0.0.0.0', port=7860) |