使用wxPython开发订单管理系统-核对页面

统计页面可以自动核对客户对账信息
wx.FileDialog 文件对话框

import wx
import time
from xlrd import xldate_as_tuple
from datetime import datetime
import os
import order
from sqloperation import get_excel_row,get_excel_value,create_db,insert_db,update_db,select_from_db
#print(os.getcwd())

class MyCal(wx.Frame):
    
    def __init__(self,user,parent,id):
          self.user=user
          wx.Frame.__init__ (self,parent,id,title="输入文件界面_"+self.user,pos=(200,200),size=(500,500),name='frame') #如果是顶级窗口，这个值是None,id=-1自动生成 ,name框架内的文字
          self.panel=wx.Panel(self)
          icon = wx.Icon('smart.ico')
          self.SetIcon(icon) 
          self.bt_confirm=wx.Button(self.panel,label="确定对账")  #创建按钮
          
      
          self.bt_confirm.Bind(wx.EVT_BUTTON,self.OnclickSubmit)
          self.bt_back=wx.Button(self.panel,label="返回订单输入页面")
          self.bt_back.Bind(wx.EVT_BUTTON,self.OnclickRegister)
          
          self.title=wx.StaticText( self.panel,label="选择统计文件")#,style=0  #相当于tkinter中的Label
          
          self.label_my=wx.StaticText( self.panel,label="自己的文件:")
          self.text_my=wx.TextCtrl( self.panel,style=wx.TE_LEFT)  #相当于tkinter的Entry
          self.bt_choose_1=wx.Button(self.panel,label="选择自己文件")
          self.bt_choose_1.Bind(wx.EVT_BUTTON,self.Onchoose_1)

          
          self.label_customer=wx.StaticText(self.panel,label="客户的文件:")
          self.text_customer=wx.TextCtrl( self.panel,style=wx.TE_LEFT)
          self.bt_choose_2=wx.Button(self.panel,label="选择客户文件")
          self.bt_choose_2.Bind(wx.EVT_BUTTON,self.Onchoose_2)
                                   
          #控件横向排列
          hsizer_user=wx.BoxSizer(wx.HORIZONTAL)
          hsizer_user.Add(self.label_my,proportion=0,flag=wx.ALL,border=5)  #hsizer_user横向第一行
          hsizer_user.Add(self.text_my,proportion=1,flag=wx.ALL,border=5)  #proportion=0表示不变,proportion=1两倍宽度
          hsizer_user.Add(self.bt_choose_1,proportion=0,flag=wx.ALL,border=5) 
          
          hsizer_pwd=wx.BoxSizer(wx.HORIZONTAL)    #横向第二行
          hsizer_pwd.Add(self.label_customer,proportion=0,flag=wx.ALL,border=5)
          hsizer_pwd.Add(self.text_customer,proportion=1,flag=wx.ALL,border=5)
          hsizer_pwd.Add(self.bt_choose_2,proportion=0,flag=wx.ALL,border=5)                                     

                               
          #控件纵向排列
          vsizer_all=wx.BoxSizer(wx.VERTICAL)
          vsizer_all.Add(self.title,proportion=0,flag=wx.BOTTOM|wx.TOP|wx.ALIGN_CENTER,border=45)
          
          vsizer_all.Add(hsizer_user,proportion=0,flag=wx.EXPAND|wx.LEFT|wx.RIGHT,border=15)
          vsizer_all.Add(hsizer_pwd,proportion=0,flag=wx.EXPAND|wx.LEFT|wx.RIGHT,border=15)
          vsizer_all.Add(self.bt_confirm,proportion=0,flag=wx.EXPAND|wx.LEFT|wx.RIGHT,border=15)
          vsizer_all.AddSpacer(100)
          vsizer_all.Add(self.bt_back,proportion=0,flag=wx.ALIGN_LEFT|wx.LEFT|wx.RIGHT|wx.TOP,border=15)
          self.panel.SetSizer(vsizer_all)
          
                
          font=wx.Font(16,wx.DEFAULT,wx.FONTSTYLE_NORMAL,wx.NORMAL,underline=False)
          self.title.SetFont(font)
   
          
    def OnclickSubmit(self,event):
         my_file= self.text_my.GetValue()  # self.paths_my[0]    #
         file_name=self.text_customer.GetValue()  #self.paths_customer[0]  #
         print("file_name",file_name)
         if file_name:
            file_name_short=file_name.split()[0].split("\\")[-1]
         print(my_file,file_name)
         info_fields="ID INTEGER PRIMARY KEY AUTOINCREMENT, 日期 DATE,客户 TEXT,调度单号 TEXT, 线路 TEXT, 车型 TEXT ,书号 TEXT, 车辆信息 TEXT,\
                              驾驶员 TEXT,调度 TEXT,税前运费 REAL default 0, 含税运费 REAL default 0,税前其它 REAL default 0,含税其它费用 REAL default 0, \
                              运费客户计 REAL default 0 ,运费来源文件 TEXT ,其它费用客户计 REAL default 0,\
                              其它费用来源文件 TEXT, 费用差异 REAL ,是否结案 TEXT default False,关联 REAL default 0 ,\
                              进仓费 REAL default 0,停车费 REAL default 0,换托盘 REAL default 0,买标签 REAL default 0 ,送货费 REAL default 0 ,\
                              压车费 REAL default 0 ,垫付费用  REAL default 0,给司机费用  REAL default 0"
         create_db("费用明细",info_fields)
         
         if my_file:
             #s=time.time()
             rows=get_excel_row(my_file,0)
             for i in range(2,rows):
                   value=get_excel_value(i,1, 12,my_file)
                   charge=["{0:.1f}".format(value[-3]),"{0:.1f}".format(value[-2]),"{0:.1f}".format(value[-1])]
                   date=value[0]
                   keyindate=str(datetime(*xldate_as_tuple(date,0)).strftime("%Y-%m-%d"))
                   newvalue=str(tuple(([keyindate]+value[1:8]+charge)))
                   fields="日期,调度单号, 线路 , 车型  ,书号 , 车辆信息 ,驾驶员 ,调度,税前运费, 含税运费 ,含税其它费用 "
                   insert_db("费用明细",fields,newvalue)
             #e=time.time()
             #print("cost_time_no_thread",e-s)
             
         if  file_name:
             rows_customer=get_excel_row(file_name,0)
             count=0
             count_other=0    
             for i in range(3,rows_customer):
                   value=get_excel_value(i,1, 15,file_name)
                   MO=value[13]
                   freight=value[8]
                   other=value[9]
                   #print("MO",MO)

                   #mtime = os.stat(file_name).st_mtime
                   #file_modify_time = time.strftime('%Y-%m-%d ', time.localtime(mtime))
                   #print(file_modify_time)#.strftime("%Y-%m-%d"))
                   result=select_from_db("调度单号","费用明细","调度单号",MO)
                   print("result",result)
                   if result:
                         if freight:  #如果'运费'项有数据
                               #values=str(tuple([freight,file_name]))
                               update_db("费用明细","运费客户计",freight,"调度单号",MO)
                               update_db("费用明细","运费来源文件",file_name_short,"调度单号",MO)
                               check_result=select_from_db("(含税运费+含税其它费用 )- (运费客户计+其它费用客户计)","费用明细","调度单号",MO)[0]
                               update_db("费用明细","费用差异",check_result,"调度单号",MO)  #更新费用差异
                               count+=1
                         if other:  #如果'其它费用'项有数据
                               #values=str(tuple([other,file_name]))
                               update_db("费用明细","其它费用客户计",other,"调度单号",MO)
                               update_db("费用明细","其它费用来源文件",file_name_short,"调度单号",MO)
                               check_result=select_from_db("(含税运费+含税其它费用)- (运费客户计+其它费用客户计)","费用明细","调度单号",MO)[0]
                               update_db("费用明细","费用差异",check_result,"调度单号",MO)
                               count_other+=1
         wx.MessageBox("已输入完毕")            
            
    def OnclickRegister(self,event):
          self.panel.Parent.Hide()
          frame=order.MyFrame("usertest",parent=None,id=-1)
          frame.Show()

    def Onchoose_1(self,event):
            wildcard = "excel source (*.xls)|*.xlsx|"     \
           "All files (*.*)|*.*"
            dlg = wx.FileDialog(
                self, message="Choose a file选择文件",
                defaultDir=os.path.join(os.getcwd(),"我的备份"),
                defaultFile="",
                wildcard=wildcard,
                style=wx.FD_OPEN | wx.FD_MULTIPLE |
                      wx.FD_FILE_MUST_EXIST |
                      wx.FD_PREVIEW
                )

            if dlg.ShowModal() == wx.ID_OK:
                self.paths_my = dlg.GetPaths()
                print("self.paths_my ",self.paths_my )
                self.text_my.SetValue(self.paths_my [0])
                #print("os.getcwd()",os.getcwd())
                
    def Onchoose_2(self,event):
            wildcard = "excel source (*.xls)|*.xlsx|"     \
           "All files (*.*)|*.*"
            dlg = wx.FileDialog(
                self, message="Choose a file选择文件",
                defaultDir=os.path.join(os.getcwd(),"我的备份"),
                defaultFile="",
                wildcard=wildcard,
                style=wx.FD_OPEN | wx.FD_MULTIPLE |
                      wx.FD_FILE_MUST_EXIST |      #wx.FD_CHANGE_DIR | 
                      wx.FD_PREVIEW
                )

            if dlg.ShowModal() == wx.ID_OK:   #确认则选择
                self.paths_customer = dlg.GetPaths()  #返回值是列表
                print("self.paths_customer ",self.paths_customer )
                self.text_customer.SetValue(self.paths_customer [0])
                #print("os.getcwd()",os.getcwd())

       
if __name__=="__main__":
    app=wx.App()      
    frame=MyCal("usertst",parent=None,id=-1)
    frame.Show()
    app.MainLoop()