Spaces:
Running
Running
| 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 setting | |
| SHEET_ID = os.getenv('SHEET_ID') # Replace with your Google Sheet ID | |
| log = 'cevauto' | |
| password = os.getenv('PASSWORD') | |
| 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', '观察员'], | |
| ['7', 'cevcaigou888', '采购经理'], | |
| ['8', 'cevcaigou005', '采购'], | |
| ['9', 'cevchuna001', '出纳'] # Added new user | |
| ] | |
| 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 '已批准' | |
| 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 not in ['采购经理', '总经理']) 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, 'order_id': 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_order1( | |
| username,付款抬头, | |
| 运编号,车辆信息,销售合同价, 采购价, | |
| 费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息, | |
| 在途状态, 开票类型, | |
| 收款状态, 退款说明, 备注 | |
| ): | |
| try: | |
| global profit | |
| login(username) | |
| if session_state['role'] not in ['采购', '采购经理']: | |
| return "只有采购角色可以提交订单" | |
| orders = sheet.worksheet(PURCHASE_ORDERS_SHEET) | |
| # Generate purchase order name based on username | |
| prefix = 'DING00000' | |
| if session_state['username'] == 'cevcaigou159': | |
| prefix = 'SHEN00000' | |
| elif session_state['username'] == 'cevcaigou888': | |
| prefix = 'NIU00000' | |
| elif session_state['username'] == 'cevcaigou005': | |
| prefix = 'HAN00000' | |
| all_records = orders.get_all_records() | |
| existing_records = [record for record in all_records if record['采购编号'].startswith(prefix)] | |
| n = len(existing_records) | |
| i = n + 1 | |
| order_name = f"{prefix}{i}" | |
| existing_records1 = [record for record in all_records if record['运编号'] == 运编号] | |
| existing_record = existing_records1[0] | |
| profit = float(销售合同价) * 7 - float(采购价) + float(采购价) * 0.115 | |
| new_order = [ | |
| 付款抬头, existing_record['合同号'], 运编号, order_name, 车辆信息, 销售合同价, 采购价, | |
| 费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息, | |
| existing_record['业务员'], existing_record['客户名称'], existing_record['客户国家'], 在途状态, 开票类型, | |
| 收款状态, '待单证审批', 退款说明, 备注, | |
| 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 submit_order2( | |
| username, | |
| 付款抬头, 合同号, 运编号, 车辆信息, 销售合同价, 采购价, | |
| 费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息, | |
| 业务员, 客户名称, 客户国家, 在途状态, 开票类型, | |
| 收款状态, 退款说明, 备注 | |
| ): | |
| try: | |
| global profit | |
| login(username) | |
| if session_state['role'] not in ['采购', '采购经理']: | |
| return "只有采购角色可以提交订单" | |
| orders = sheet.worksheet(PURCHASE_ORDERS_SHEET) | |
| # Generate purchase order name based on username | |
| prefix = 'DING00000' | |
| if session_state['username'] == 'cevcaigou159': | |
| prefix = 'SHEN00000' | |
| elif session_state['username'] == 'cevcaigou888': | |
| prefix = 'NIU00000' | |
| elif session_state['username'] == 'cevcaigou005': | |
| prefix = 'HAN00000' | |
| all_records = orders.get_all_records() | |
| existing_records = [record for record in all_records if record['采购编号'].startswith(prefix)] | |
| n = len(existing_records) | |
| i = n + 1 | |
| order_name = f"{prefix}{i}" | |
| profit = float(销售合同价) * 7.25 - float(采购价) + float(采购价) * 0.115 | |
| new_order = [ | |
| 付款抬头, 合同号, 运编号, order_name, 车辆信息, 销售合同价, 采购价, | |
| 费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息, | |
| 业务员, 客户名称, 客户国家, 在途状态, 开票类型, | |
| 收款状态, '待单证审批', 退款说明, 备注, | |
| 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 = [ | |
| '采购', '单证', '财务', | |
| '总经理', '观察员', '采购经理', '出纳' # Added '出纳' to 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 = ['单证', '财务', '总经理', '采购经理', '出纳'] # Added '出纳' to 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="销售合同价(FOB美元)") | |
| 采购价 = gr.Textbox(label="采购价(人民币)") | |
| 费用名称 = gr.Dropdown( | |
| choices=[ | |
| '采车定金', '采车定金（抵车款）', '采车定金（全额退回）', '采车定金（部分退回）', | |
| '采车尾款（含背户费）', '采车尾款', '背户手续费', '系统刷机费', '充电桩费', | |
| '保险费（短期交强）', '保险费（长期交强）', '保险费（全险）', '国内运费（市内）', '国内运费', | |
| '购置税费', '仓储费', '检测报告费', '车辆用品费', '车辆改装费', '港杂费', '海运费', '海运费+港杂费', | |
| '其他费用' | |
| ], | |
| 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_order1, inputs=[ | |
| username_submit, 付款抬头,运编号,车辆信息, 销售合同价, 采购价, | |
| 费用名称, 费用金额, 采购款收款抬头, 采购款收款账号信息, | |
| 在途状态, 开票类型, | |
| 收款状态, 退款说明, 备注 | |
| ], outputs=submit_output) | |
| with gr.TabItem("提交订单(新运编号)"): | |
| username_submit = gr.Textbox(label="用户名") | |
| 运编号_submit = 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_order2, inputs=[ | |
| username_submit, 付款抬头, 合同号, 运编号_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(auth=(log, password)) |