app.py

# '1ckM1GgD3hG_eTsa_7CGCB7qf8AyKKDII_yIOWwKaWvA'    sheet
import pandas as pd
import gradio as gr
from datetime import datetime
import gspread
from google.oauth2.service_account import Credentials
import os

# Google Sheets settings
SHEET_ID = os.getenv('SHEET_ID')  # Replace with your Google Sheet ID
USERS_SHEET = 'Users'
PURCHASE_ORDERS_SHEET = 'PurchaseOrders'

# Service account credentials file path
CREDENTIALS_FILE = 'credentials.json'  # Replace with the path to your service account credentials file

# Initialize Google Sheets client
SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
creds = Credentials.from_service_account_file(CREDENTIALS_FILE, scopes=SCOPES)
gc = gspread.authorize(creds)
sheet = gc.open_by_key(SHEET_ID)

# Initialize Google Sheets
def init_sheets():
    if USERS_SHEET not in [s.title for s in sheet.worksheets()]:
        sheet.add_worksheet(title=USERS_SHEET, rows="100", cols="20")
        users = sheet.worksheet(USERS_SHEET)
        users.append_row(['id', 'username', 'role'])
        new_users = [
            ['1', 'cevcaigou159', '采购'],
            ['2', 'cevdanzheng789', '单证'],
            ['3', 'cevcaiwu369', '财务'],
            ['4', 'cevboss888', '总经理'],
            ['5', 'cevcaigou357', '采购'],
            ['6', 'investor888', '观察员']
        ]
        for user in new_users:
            users.append_row(user)

    if PURCHASE_ORDERS_SHEET not in [s.title for s in sheet.worksheets()]:
        sheet.add_worksheet(title=PURCHASE_ORDERS_SHEET, rows="100", cols="20")
        orders = sheet.worksheet(PURCHASE_ORDERS_SHEET)
        orders.append_row([
            '付款抬头', '合同号', '运编号', '采购编号','车辆信息', '销售合同价', '采购价',
            '费用名称', '费用金额', '采购款收款抬头', '采购款收款账号信息',
            '业务员', '客户名称', '客户国家', '在途状态', '开票类型',
            '收款状态', '审批状态', '退款说明', '备注', 'timestamp', '毛利'
        ])

init_sheets()

# Approval process control
def get_next_approval_status(current_status):
    try:
        if current_status == '待单证审批':
            return '待财务审批'
        elif current_status == '待财务审批':
            return '待总经理审批'
        elif current_status == '待总经理审批':
            return '已批准'
        return current_status
    except Exception as e:
        return "操作有误: {}".format(e)

def approve_order(order_id, approver_role):
    try:
        orders = sheet.worksheet(PURCHASE_ORDERS_SHEET)
        order_records = orders.get_all_records()

        order_index = next((index for (index, d) in enumerate(order_records) if d["采购编号"] == order_id), None)

        if order_index is None:
            return '订单未找到'

        current_status = order_records[order_index]['审批状态']

        if (current_status == '待单证审批' and approver_role != '单证') or \
                (current_status == '待财务审批' and approver_role != '财务') or \
                (current_status == '待总经理审批' and approver_role != '总经理'):
            return f'只有{current_status}角色可以批准当前订单'

        next_status = get_next_approval_status(current_status)
        headers = list(order_records[0].keys())
        orders.update_cell(order_index + 2, headers.index('审批状态') + 1, next_status)  # Update approval status
        orders.update_cell(order_index + 2, headers.index('timestamp') + 1,
                           datetime.now().strftime('%Y-%m-%d'))  # Update timestamp

        return '订单已批准'
    except Exception as e:
        return "操作有误: {}".format(e)

# Global session state
session_state = {'user_id': None, 'username': None, 'role': None}

def login(username):
    try:
        users = sheet.worksheet(USERS_SHEET).get_all_records()
        user = next((u for u in users if u['username'] == username), None)
        if user:
            session_state['user_id'] = user['id']
            session_state['username'] = user['username']
            session_state['role'] = user['role']
            return "登录成功，用户：{}".format(username)
        else:
            return "用户未找到"
    except Exception as e:
        return "操作有误: {}".format(e)

def submit_order(
    username,
    付款抬头, 合同号, 运编号, 采购编号, 车辆信息, 销售合同价, 采购价,
    费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息,
    业务员, 客户名称, 客户国家, 在途状态, 开票类型,
    收款状态, 退款说明, 备注
):
    try:
        global profit
        login(username)

        if session_state['role'] != '采购':
            return "只有采购角色可以提交订单"

        orders = sheet.worksheet(PURCHASE_ORDERS_SHEET)
        profit = float(销售合同价)*7.25 - float(采购价) + float(采购价)*0.115

        new_order = [
            付款抬头, 合同号, 运编号, 采购编号, 车辆信息, 销售合同价, 采购价,
            费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息,
            业务员, 客户名称, 客户国家, 在途状态, 开票类型,
            收款状态, '待单证审批', 退款说明, 备注,
            datetime.now().strftime('%Y-%m-%d'), profit
        ]
        orders.append_row(new_order)
        session_state.clear()
        return "订单已提交"
        session_state.clear()
    except Exception as e:
        return "操作有误: {}".format(e)

def format_order(order):
    try:
        return f"""
            <table border="1">
                <tr><th>审批状态</b></th><td><b>{order['审批状态']}</b></td></tr>
                <tr><th>付款抬头</th><td>{order['付款抬头']}</td></tr>
                <tr><th>合同号</th><td>{order['合同号']}</td></tr>
                <tr><th>运编号</th><td>{order['运编号']}</td></tr>
                <tr><th>采购编号</th><td>{order['采购编号']}</td></tr>
                <tr><th>车辆信息</th><td>{order['车辆信息']}</td></tr>
                <tr><th>销售合同价(FOB美元)</th><td>{order['销售合同价']}</td></tr>
                <tr><th>采购价(人民币)</th><td>{order['采购价']}</td></tr>
                <tr><th>费用名称</th><td>{order['费用名称']}</td></tr>
                <tr><th>费用金额</th><td>{order['费用金额']}</td></tr>
                <tr><th>采购款收款抬头</th><td>{order['采购款收款抬头']}</td></tr>
                <tr><th>采购款收款账号信息</th><td>{order['采购款收款账号信息']}</td></tr>
                <tr><th>业务员</th><td>{order['业务员']}</td></tr>
                <tr><th>客户名称</th><td>{order['客户名称']}</td></tr>
                <tr><th>客户国家</th><td>{order['客户国家']}</td></tr>
                <tr><th>在途状态</th><td>{order['在途状态']}</td></tr>
                <tr><th>开票类型</th><td>{order['开票类型']}</td></tr>
                <tr><th>收款状态</th><td>{order['收款状态']}</td></tr>
                <tr><th>退款说明</th><td>{order['退款说明']}</td></tr>
                <tr><th>备注</th><td>{order['备注']}</td></tr>
                <tr><th>时间</th><td>{order['timestamp']}</td></tr>
                <tr><th>毛利</th><td>{order['毛利']}</td></tr>
                <tr><th>当前身份</th><td>{session_state['username']}</td></tr>
            </table>
            """
    except Exception as e:
        return "操作有误: {}".format(e)

def display_orders(username):
    try:

        login(username)
        allowed_roles = [
            '采购', '单证', '财务',
            '总经理', '观察员'
        ]

        if session_state['role'] not in allowed_roles:
            return ["请登录后查看订单"]

        orders = sheet.worksheet(PURCHASE_ORDERS_SHEET).get_all_records()
        orders_df = pd.DataFrame(orders)
        orders_df['timestamp'] = pd.to_datetime(orders_df['timestamp'], errors='coerce')
        orders_df['index'] = range(len(orders_df))
        orders_df = orders_df.sort_values(by=['timestamp', 'index'], ascending=[False, False]).reset_index(drop=True)
        orders_df = orders_df.drop(columns=['index'])

        order_list = [format_order(order) for _, order in orders_df.iterrows()]
        session_state.clear()
        return order_list
        session_state.clear()
    except Exception as e:
        return "操作有误: {}".format(e)

def approve_order_interface(username, order_id):
    try:
        login(username)

        allowed_roles = ['单证', '财务', '总经理']

        if session_state['role'] not in allowed_roles:
            return "您没有批准订单的权限"

        role1 = session_state['role']
        result = approve_order(order_id, role1)
        session_state.clear()
        return result
        session_state.clear()
    except Exception as e:
        return "操作有误: {}".format(e)

with gr.Blocks() as app:
    with gr.TabItem("提交订单"):
        username_submit = gr.Textbox(label="用户名")
        付款抬头 = gr.Textbox(label="付款抬头")
        合同号 = gr.Textbox(label="合同号")
        运编号 = gr.Textbox(label="运编号")
        采购编号 = gr.Textbox(label="采购编号")
        车辆信息 = gr.Textbox(label="车辆信息")
        销售合同价 = gr.Textbox(label="销售合同价(FOB美元)")
        采购价 = gr.Textbox(label="采购价(人民币)")
        费用名称 = gr.Dropdown(
            choices = [
              '采车定金（抵车款）','采车定金（全额退回）','采车定金（部分退回）',
                '采车尾款（含背户费）','采车尾款','背户手续费','系统刷机费','充电桩费',
                '保险费（短期交强）','保险费（长期交强）','保险费（全险）' ,'国内运费（市内）','国内运费',
                '购置税费','仓储费','检测报告费','车辆用品费','车辆改装费','港杂费','海运费','其他费用' 
            ],
            label="费用名称")
        费用金额 = gr.Textbox(label="费用金额")
        采购款收款抬头 = gr.Textbox(label="采购款收款抬头")
        采购款收款账号信息 = gr.Textbox(label="采购款收款账号信息")
        业务员 = gr.Textbox(label="业务员")
        客户名称 = gr.Textbox(label="客户名称")
        客户国家 = gr.Textbox(label="客户国家")
        在途状态 = gr.Dropdown(
            choices = [
              '工厂','4S店','车管所',
                '改装店','运输中','物流站点','货代仓库','装箱',
                '发运','到达目的港'
            ],
            label="在途状态")
        开票类型 = gr.Dropdown(
            choices = [
              '增票','普票','低开','无票'
            ],
            label="开票类型")
        收款状态 = gr.Textbox(label="收款状态")
        退款说明 = gr.Textbox(label="退款说明")
        备注 = gr.Textbox(label="备注")
        submit_button = gr.Button("提交订单")
        submit_output = gr.HTML(label="输出")
        submit_button.click(fn=submit_order, inputs=[
            username_submit, 付款抬头, 合同号, 运编号, 采购编号,车辆信息, 销售合同价, 采购价,
            费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息,
            业务员, 客户名称, 客户国家, 在途状态, 开票类型,
            收款状态, 退款说明, 备注
        ], outputs=submit_output)

    with gr.TabItem("所有订单"):
        username_display = gr.Textbox(label="用户名")
        display_button = gr.Button("显示所有订单")
        orders_output = gr.HTML(label="订单")
        display_button.click(fn=display_orders, inputs=username_display, outputs=orders_output)

    with gr.TabItem("批准订单"):
        username_approve = gr.Textbox(label="用户名")
        order_id = gr.Textbox(label="采购编号")
        approve_order_button = gr.Button("批准订单")
        approve_order_output = gr.HTML(label="输出")
        approve_order_button.click(fn=approve_order_interface, inputs=[username_approve, order_id], outputs=approve_order_output)

app.launch()