import pandas as pd import gradio as gr from datetime import datetime import gspread from google.oauth2.service_account import Credentials import os import json # Google Sheets setting conf1 = os.getenv('CONF') # conf = json.dumps(conf1) dict = json.loads(conf1) 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_info(dict, 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 '已付款' 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 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'] == 'cevdanzheng789': prefix = 'CHEN00000' elif session_state['username'] == 'cevcaigou359': prefix = 'ZHUANG00000' 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'] == 'cevdanzheng789': prefix = 'CHEN00000' elif session_state['username'] == 'cevcaigou359': prefix = 'ZHUANG00000' 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"""
审批状态{order['审批状态']}
付款抬头{order['付款抬头']}
合同号{order['合同号']}
运编号{order['运编号']}
采购编号{order['采购编号']}
车辆信息{order['车辆信息']}
销售合同价(FOB美元){order['销售合同价']}
采购价(人民币){order['采购价']}
费用名称{order['费用名称']}
费用金额{order['费用金额']}
采购款收款抬头{order['采购款收款抬头']}
采购款收款账号信息{order['采购款收款账号信息']}
业务员{order['业务员']}
客户名称{order['客户名称']}
客户国家{order['客户国家']}
在途状态{order['在途状态']}
开票类型{order['开票类型']}
收款状态{order['收款状态']}
退款说明{order['退款说明']}
备注{order['备注']}
时间{order['timestamp']}
毛利{order['毛利']}
当前身份{session_state['username']}
""" 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 = ['财务', '总经理', '采购经理', '出纳'] # Removed '单证' from 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))