frmload.frm

来自「通用书店管理系统」· FRM 代码 · 共 194 行

VERSION 5.00
Begin VB.Form frmLoad 
   Caption         =   "数据导入"
   ClientHeight    =   3195
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   4680
   Icon            =   "frmLoad.frx":0000
   LinkTopic       =   "Form1"
   ScaleHeight     =   3195
   ScaleWidth      =   4680
   StartUpPosition =   3  '窗口缺省
   Begin VB.CommandButton Command1 
      Caption         =   "图书库存导入"
      Height          =   420
      Index           =   3
      Left            =   450
      TabIndex        =   3
      Top             =   2175
      Width           =   1380
   End
   Begin VB.CommandButton Command1 
      Caption         =   "图书资料导入"
      Height          =   420
      Index           =   2
      Left            =   420
      TabIndex        =   2
      Top             =   1515
      Width           =   1380
   End
   Begin VB.CommandButton Command1 
      Caption         =   "图书分类导入"
      Height          =   420
      Index           =   1
      Left            =   420
      TabIndex        =   1
      Top             =   840
      Width           =   1395
   End
   Begin VB.CommandButton Command1 
      Caption         =   "出版社导入"
      Height          =   420
      Index           =   0
      Left            =   405
      TabIndex        =   0
      Top             =   195
      Width           =   1380
   End
End
Attribute VB_Name = "frmLoad"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click(Index As Integer)
  Dim i As Integer
  Dim decAgio As Double
  Dim sqlstring As String
  Dim rstmp As New ADODB.Recordset
  Dim rsNewTmp As New ADODB.Recordset
  
  On Error GoTo Err
  
  Select Case Index
    Case 0 '出版社
        sqlstring = "select * from tmp_出版社 order by 出版社"
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        
        sqlstring = "delete from PublishingCompanyData"
        cN.Execute sqlstring
        
        i = 1
        cN.BeginTrans
        Do While Not rstmp.EOF
           sqlstring = "Insert into PublishingCompanyData (chrCompanyNo,ChrCompanyName,ChrBenelux) values " & _
                     "('" & i & "','" & rstmp.Fields("出版社").Value & "','" & rstmp.Fields("出版社").Value & "')"
           cN.Execute sqlstring
           rstmp.MoveNext
           i = i + 1
        Loop
        cN.CommitTrans
        MsgBox "出版社导入成功"
    Case 1 '图书分类
        sqlstring = "select * from tmp_书类 order by 书类"
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        
        sqlstring = "delete from BookType"
        cN.Execute sqlstring
        
        i = 1
        cN.BeginTrans
        Do While Not rstmp.EOF
           sqlstring = "Insert into BookType (chrBookTypeNo,ChrBookType) values " & _
                     "('" & i & "','" & rstmp.Fields("书类").Value & "')"
           cN.Execute sqlstring
           rstmp.MoveNext
           i = i + 1
        Loop
        cN.CommitTrans
        MsgBox "图书分类导入成功"
    Case 2 '图书资料
        sqlstring = "select * from tmp_库存数据"
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        
        sqlstring = "delete from BookData"
        cN.Execute sqlstring
        
        i = 1
        cN.BeginTrans
        Do While Not rstmp.EOF
           If IsVacancy(rstmp.Fields("折扣")) Then
             decAgio = 1
           Else
             decAgio = rstmp.Fields("折扣") / 100
           End If
           If IsVacancy(rstmp.Fields("出版日期")) Then
              sqlstring = "Insert into BookData (chrBookNo,chrBookName,ChrProduceType,ChrBookType," & _
                     "ChrAuthoer,Chrbookconcern,DecAgio,DecPrice) values " & _
                     "('" & rstmp.Fields("书号") & "','" & rstmp.Fields("书名").Value & _
                     "','图书','" & rstmp.Fields("类别").Value & _
                     "','" & rstmp.Fields("作者") & "','" & rstmp.Fields("出版社").Value & _
                     "'," & decAgio & "," & IIf(IsNull(rstmp.Fields("单价")), 0, rstmp.Fields("单价")) & ")"
           Else
              sqlstring = "Insert into BookData (chrBookNo,chrBookName,ChrProduceType,ChrBookType," & _
                     "ChrAuthoer,Chrbookconcern,DatPublishDate,DecAgio,DecPrice) values " & _
                     "('" & rstmp.Fields("书号") & "','" & rstmp.Fields("书名").Value & _
                     "','图书','" & rstmp.Fields("类别").Value & _
                     "','" & rstmp.Fields("作者") & "','" & rstmp.Fields("出版社").Value & _
                     "',#" & rstmp.Fields("出版日期") & "#," & decAgio & _
                     "," & IIf(IsNull(rstmp.Fields("单价")), 0, rstmp.Fields("单价")) & ")"
           End If
           
           cN.Execute sqlstring
           rstmp.MoveNext
           i = i + 1
        Loop
        cN.CommitTrans
        MsgBox "图书资料导入成功"
    Case 3 '图书库存
        sqlstring = "select * from tmp_库存数据"
        Set rstmp = New ADODB.Recordset
        rstmp.Open sqlstring, cN, adOpenKeyset, adLockReadOnly
        
        sqlstring = "delete from BookStorage "
        cN.Execute sqlstring
        
        '保存盘点控制表内容
        sqlstring = "select * from PDControl"
        Set rsNewTmp = New ADODB.Recordset
        rsNewTmp.Open sqlstring, cN, adOpenKeyset, adLockBatchOptimistic
        
        If rsNewTmp.EOF Then
           rsNewTmp.AddNew
        End If
        
        rsNewTmp.Fields("DatLast").Value = Format(Date, "yyyy-mm-dd")
        rsNewTmp.UpdateBatch adAffectAllChapters
        
        
        sqlstring = "delete from PDResult "
        cN.Execute sqlstring
        i = 1
        cN.BeginTrans
        Do While Not rstmp.EOF
           '保存库存
           sqlstring = "Insert into BookStorage (chrBookNo,chrBookName,chrStorageNo,IntAmount," & _
                     "IntKCLimit,IntYXKC) values " & _
                     "('" & rstmp.Fields("书号") & "','" & rstmp.Fields("书名").Value & _
                     "','1'," & IIf(IsNull(rstmp.Fields("数量")), 0, rstmp.Fields("数量")) & ",0,0)"
          
           cN.Execute sqlstring
           '保存盘点结果表，将其作为本月盘点结果
           sqlstring = "insert into PDResult(ChrPDDate,chrBookNo,chrBookName,chrStorageNo,intAmount,intFactAmount," & _
                    "intMonthAdd,intMonthAmount) values ('" & Mid(Format(Date, "yyyy-mm-dd"), 1, 7) & _
                    "','" & rstmp.Fields("书号") & "','" & rstmp.Fields("书名") & "','1'" & _
                     "," & IIf(IsNull(rstmp.Fields("数量")), 0, rstmp.Fields("数量")) & _
                     "," & IIf(IsNull(rstmp.Fields("数量")), 0, rstmp.Fields("数量")) & ",0,0) "
                     
           cN.Execute sqlstring
           rstmp.MoveNext
        Loop
        cN.CommitTrans
        MsgBox "图书库存导入成功"
   
  End Select
  Exit Sub
Err:
  cN.RollbackTrans
  MsgBox Err.Description, vbInformation
End Sub