tsdbxg.frm

出版社图书出货管理系统,包括图书的出库,入库,结果输出

Dim rs As Recordset

Dim paperh As Double
Dim paperw As Double
Dim library As String
Dim faxizhan As String
Dim xiaoding As String
Dim riqi As String
Dim company As String
On Error Resume Next
If Data2.Recordset.RecordCount = 0 Then
  MsgBox "没有记录！"
  Exit Sub
End If
paperh = 29.7
paperw = 21
 
 Set db = Workspaces(0).OpenDatabase("d:\cbssys\bookcgk.mdb")
 Set rs = db.OpenRecordset("select * from 输出参数 where 名称='" & Combo4.Text & "'")
 If rs.RecordCount = 0 Then
   rs.Close
   db.Close
   MsgBox "请设置打印参数！"
   Exit Sub
 End If
 rs.MoveFirst
If Combo4.Text <> "不打印表头和表尾" Then
 cs_a = rs("收书单位").Value
 cs_b = rs("单位地址").Value
 cs_c = Text12.Text
 cs_d = rs("发货单位").Value
 cs_e = rs("收货电话").Value
  
End If
  If IsNull(rs("纸张宽度").Value) Then
    paperw = 21
   Else
    paperw = Val(rs("纸张宽度").Value)
   End If
   
  If IsNull(rs("纸张长度").Value) Then
    paperh = 29.7
   Else
    paperh = Val(rs("纸张长度").Value)
   End If
  
  If paperh <= 0 Or paperw <= 0 Then
    paperh = 29.7
    paperw = 21
  End If
rs.Close
db.Close
'读参数结束

suhao = 0
zongsum = 0
cesum = 0
jesum = 0
t_zongsum = 0
t_cesum = 0
t_jesum = 0
cury = 2
curx = 2
aab = MsgBox("是否输出上面表格中打包数据！", vbYesNo)
If aab = 7 Then
  Exit Sub
  End If


On Error GoTo bbb

Command4.Enabled = False
Data2.Recordset.MoveFirst

If Data2.Recordset.RecordCount = 0 Then
 MsgBox "没有记录输出！"
 Exit Sub
 End If
 
 '设置尺寸单位为cm
    Printer.ScaleMode = vbCentimeters
    '设置打印区域大小
    Printer.Width = paperw
    Printer.Height = paperh
    '开始打印
    
   '打印表头
    Printer.Font.Name = "黑体"
    Printer.Font.Size = 12
    
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print cs_a

    
    cury = cury + 0.8
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "单位地址：" & cs_b
    cury = cury + 0.8
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "提书单号：" & cs_c
   
    cury = cury + 0.7
    Printer.Line (curx - 1, cury)-(curx + 16, cury)
    cury = cury + 0.2
   '打印表头结束
   '打印标题开始
    Printer.Font.Name = "宋体"
    Printer.Font.Size = 11
    
    Printer.CurrentX = curx - 1
    Printer.CurrentY = cury
    Printer.Print "序号"
    
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "ISBN"
    
    Printer.CurrentX = curx + 2.2
    Printer.CurrentY = cury
    Printer.Print "书名"
    
    Printer.CurrentX = curx + 11
    Printer.CurrentY = cury
    Printer.Print "单价"
    
    Printer.CurrentX = curx + 13
    Printer.CurrentY = cury
    Printer.Print "数量"
       
    Printer.CurrentX = curx + 15
    Printer.CurrentY = cury
    Printer.Print "包"
    Printer.CurrentX = curx
    Printer.CurrentY = cury + 0.5
    Printer.Line (curx - 1, cury + 0.5)-(curx + 16, cury + 0.5)
    
    '打印标题结束，出序号,ISBN、书名、价格、数量、包号
    cury = cury + 0.6
    If cury >= paperh - 3.7 Then
     Printer.NewPage
     cury = 2
      Printer.Line (curx - 1, cury - 0.1)-(curx + 16, cury - 0.1)
     End If
    baohaobak = Data2.Recordset("baohao").Value
    
 Do While Not Data2.Recordset.EOF
    baohaonew = Data2.Recordset("baohao").Value
  If baohaobak = baohaonew Then
    suhao = suhao + 1
    
    Printer.CurrentX = curx - 1
    Printer.CurrentY = cury
    Printer.Print suhao
    
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print Data2.Recordset("isbn").Value
    
    Printer.CurrentX = curx + 2.2
    Printer.CurrentY = cury
    Printer.Print Trim(Left(Data2.Recordset("bookname").Value, 25))
    
    Printer.CurrentX = curx + 11
    Printer.CurrentY = cury
    Printer.Print Data2.Recordset("jg").Value
    
    Printer.CurrentX = curx + 13
    Printer.CurrentY = cury
    Printer.Print Data2.Recordset("dgs").Value
    
       
    Printer.CurrentX = curx + 15
    Printer.CurrentY = cury
    Printer.Print Data2.Recordset("baohao").Value
    Printer.Line (curx - 1, cury + 0.5)-(curx + 16, cury + 0.5)
    
    zongsum = zongsum + 1
    cesum = cesum + Data2.Recordset("dgs").Value
    jesum = jesum + Data2.Recordset("dgs").Value * Val(Data2.Recordset("jg").Value)
    'jesum_discount = jesum_discount + Data3.Recordset("dgs").Value * Val(Data3.Recordset("jg").Value) * Data3.Recordset("discount").Value
    baohaobak = baohaonew
   Else
    '打印种册金额
    '打印标题
    Printer.Line (curx - 1, cury - 0.1)-(curx + 16, cury - 0.1)

    Printer.Font.Name = "黑体"
    Printer.Font.Size = 11
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "发货单位： " & cs_d
      cury = cury + 0.5
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "收货人电话：" & cs_e
    
     t_zongsum = t_zongsum + zongsum
     t_cesum = t_cesum + cesum
     t_jesum = t_jesum + jesum
     't_jesum_dis = t_jesum_dis + jesum_discount
     cury = cury + 2
     If cury >= paperh - 3.7 Then
     Printer.NewPage
     cury = 2
     Printer.Line (curx - 1, cury - 0.1)-(curx + 16, cury - 0.1)
     End If
     
      '打印表头
    Printer.Font.Name = "黑体"
    Printer.Font.Size = 12
    
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print cs_a
    
    cury = cury + 0.8
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "单位地址：" & cs_b
    cury = cury + 0.8
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "提书单号：" & cs_c
   
    cury = cury + 0.7
    Printer.Line (curx - 1, cury)-(curx + 16, cury)
    cury = cury + 0.2
   '打印表头结束
   '打印标题开始
    Printer.Font.Name = "宋体"
    Printer.Font.Size = 11
     
    Printer.CurrentX = curx - 1
    Printer.CurrentY = cury
    Printer.Print "序号"
     
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "ISBN"
    
    Printer.CurrentX = curx + 2.2
    Printer.CurrentY = cury
    Printer.Print "书名"
    
    Printer.CurrentX = curx + 11
    Printer.CurrentY = cury
    Printer.Print "单价"
    
    Printer.CurrentX = curx + 13
    Printer.CurrentY = cury
    Printer.Print "数量"
    
        
    Printer.CurrentX = curx + 15
    Printer.CurrentY = cury
    Printer.Print "包"
    Printer.CurrentX = curx
    Printer.CurrentY = cury + 0.5
    Printer.Line (curx - 1, cury + 0.5)-(curx + 16, cury + 0.5)
    zongsum = 0
    cesum = 0
    jesum = 0
   ' jesum_discount = 0
    suhao = 0
    baohaobak = baohaonew
    Data2.Recordset.MovePrevious
   End If
   cury = cury + 0.6
   If cury >= paperh - 3.7 Then
     Printer.NewPage
     cury = 2
     Printer.Line (curx - 1, cury - 0.1)-(curx + 16, cury - 0.1)
     End If
   Data2.Recordset.MoveNext
 Loop
     Printer.Line (curx - 1, cury - 0.1)-(curx + 16, cury - 0.1)
    Printer.Font.Name = "黑体"
    Printer.Font.Size = 11
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "发货单位： " & cs_d
      cury = cury + 0.5
    Printer.CurrentX = curx
    Printer.CurrentY = cury
    Printer.Print "收货人电话：" & cs_e
     
     t_zongsum = t_zongsum + zongsum
     t_cesum = t_cesum + cesum
     t_jesum = t_jesum + jesum
    ' t_jesum_dis = t_jesum_dis + jesum_discount
    '打印总的种、册、金额和尾

     '结束
    Printer.EndDoc
    MsgBox "打印完成"
 Command4.Enabled = True
 Exit Sub
bbb:
  MsgBox Err.Description




End Sub

Private Sub Command5_Click()
On Error Resume Next
startbao = Val(Text3.Text)
endbao = Val(Text4.Text)
dwcode = Combo4.Text
dwdh = Text12.Text
If startbao <= 0 Then
  startbao = 1
 
End If
If endbao <= 0 Then
 endbao = 1

End If
Text3.Text = Trim(Str(startbao))
Text4.Text = Trim(Str(endbao))


Data2.RecordSource = "select * from 本馆数据 where baohao>=" & Text3.Text & " and baohao<=" & Text4.Text & " and 名称='" & dwcode & "' and  单号='" & dwdh & "' order by baohao,val(jg)"
  Data2.Refresh
 
  Dim db As Database
  Dim rs As Recordset
 
  On Error GoTo aa:
  baohao1 = Val(Text3.Text)
  Set db = Workspaces(0).OpenDatabase("d:\cbssys\bookcgk.mdb")
  Set rs = db.OpenRecordset("select count(*) as zongsum,sum(dgs)as csum,sum(dgs*val(jg)) as je from 本馆数据 where baohao>=" & Text3.Text & " and baohao<=" & Text4.Text & " and 名称='" & dwcode & "' and  单号='" & dwdh & "'")
  Text5.Text = rs.Fields("zongsum").Value
  Text6.Text = rs.Fields("csum").Value
  Text7.Text = rs.Fields("je").Value
  rs.Close
  db.Close
  Exit Sub
aa:
  Text5.Text = 0
  Text6.Text = 0
  Text7.Text = 0
End Sub

Private Sub Command6_Click()
'输出打包数据
   Dim myExcel As Excel.Application
    Dim myBook As Excel.Workbook
    Dim mySheet As Excel.Worksheet
    Dim rownum As Long
    Dim curline As Long
    Dim zhongsum As Long
    Dim cesum As Long
    Dim jesum As Double
    Dim discount_jesum As Double
    Dim c_zhongsum As Long
    Dim c_cesum As Long
    Dim c_jesum As Double
    Dim discount_c_jesum As Double
    Dim discountx As Double
    discountx = 1
    c_zhongsum = 0
    c_cesum = 0
    c_jesum = 0
    zhongsum = 0
    cesum = 0
    jesum = 0
    discount_jesum = 0
    discount_c_jesum = 0
 '   On Error Resume Next
    curline = 0
    filenam = ""
 Set db = Workspaces(0).OpenDatabase("d:\cbssys\bookcgk.mdb")
 Set rs = db.OpenRecordset("select * from 输出参数 where 名称='" & Combo4.Text & "'")
 If rs.RecordCount = 0 Then
   rs.Close
   db.Close
   MsgBox "请设置打印参数！"
   Exit Sub
 End If
 rs.MoveFirst
 cs_a = rs("收书单位").Value
 cs_b = rs("单位地址").Value
 cs_c = Text12.Text
 cs_d = rs("发货单位").Value
 cs_e = rs("收货电话").Value
 
  rs.Close
  db.Close
  
    
filenam = InputBox("请输入Excel文件名(不包含路径,文件位于d:\cbssys\temp\*.xls下)：", , "NO" & Format(Now(), "YYYYMMDDHHMMSS") & ".xls")
If filenam = "" Then
 ' MsgBox "文件名未输入!"
  Exit Sub
  End If
filenam1 = "d:\cbssys\temp\" + filenam
If Dir(filenam1) = filenam Then
 
 Kill (filenam1)
End If
    
    Data2.Recordset.MoveLast
    rownum = Data2.Recordset.RecordCount
    colnum = 9
    Data2.Recordset.MoveFirst
    If rownum < 1 Then
      MsgBox "没有打包数据转出!"
      Exit Sub
      End If
  Me.Caption = "EXCEL格式打包数据正在输出......."
      Data2.Recordset.MoveFirst
    Set myExcel = CreateObject("Excel.Application")
    Set myBook = myExcel.Workbooks().Add
    Set mySheet = myBook.Worksheets("sheet1")
    curline = 1
     mySheet.Cells(curline, 1).Value = cs_a
     curline = curline + 1
     mySheet.Cells(curline, 1).Value = "单位地址：" & cs_b
      curline = curline + 1
     mySheet.Cells(curline, 1).Value = "提书单号" & cs_c
      curline = curline + 1
     mySheet.Cells(curline, 1).Value = "发货单位" & cs_d
     curline = curline + 1
     mySheet.Cells(curline, 1).Value = "收货电话" & cs_e
     curline = curline + 2
     
       mySheet.Cells(curline, 1).Value = "序号"
       mySheet.Cells(curline, 2).Value = "ISBN号"
       mySheet.Cells(curline, 3).Value = "书名"
       mySheet.Cells(curline, 4).Value = "作者"
       mySheet.Cells(curline, 5).Value = "出版社"
       mySheet.Cells(curline, 6).Value = "出版年"
       mySheet.Cells(curline, 7).Value = "价格"
      ' mySheet.Cells(curline, 8).Value = "折扣"
       mySheet.Cells(curline, 8).Value = "打包册数"
      ' mySheet.Cells(curline, 10).Value = "库存数量"
       mySheet.Cells(curline, 9).Value = "码洋"
      ' mySheet.Cells(curline, 10).Value = "实洋"
       mySheet.Cells(curline, 10).Value = "包号"
  
    backbh = Data2.Recordset.Fields("baohao").Value
    curbh = Data2.Recordset.Fields("baohao").Value
    For r = 1 To rownum
      curbh = Data2.Recordset.Fields("baohao").Value
     If curbh = backbh Then
      zhongsum = zhongsum + 1
      cesum = cesum + Data2.Recordset.Fields("dgs").Value
      jesum = jesum + Data2.Recordset.Fields("dgs").Value * Val(Data2.Recordset.Fields("jg").Value)
      'discount_jesum = discount_jesum + Data2.Recordset.Fields("dgs").Value * Val(Data2.Recordset.Fields("jg").Value) * Data2.Recordset.Fields("discount").Value
      'discount_jesum = discount_jesum + Data3.Recordset.Fields("dgs").Value * Val(Data3.Recordset.Fields("jg").Value) * discountx
      curline = curline + 1
      mySheet.Cells(curline, 1).Value = r
      mySheet.Cells(curline, 2).Value = Data2.Recordset.Fields("isbn").Value
      mySheet.Cells(curline, 3).Value = Data2.Recordset.Fields("bookname").Value
      mySheet.Cells(curline, 4).Value = Data2.Recordset.Fields("author").Value
      mySheet.Cells(curline, 5).Value = Data2.Recordset.Fields("bms").Value
      mySheet.Cells(curline, 6).Value = Data2.Recordset.Fields("bmn").Value
      mySheet.Cells(curline, 7).Value = "￥" & Format(Val(Data2.Recordset.Fields("jg").Value), "#0.00")
     ' mySheet.Cells(curline, 8).Value = "'" & Format(Data3.Recordset.Fields("discount").Value, "#0.00")
     ' mySheet.Cells(curline, 8).Value = "'" & Str(discountx)
      mySheet.Cells(curline, 8).Value = Data2.Recordset.Fields("dgs").Value
     ' mySheet.Cells(curline, 10).Value = Data3.Recordset.Fields("fbl").Value
      mySheet.Cells(curline, 9).Value = "￥" & Format(Data2.Recordset.Fields("dgs").Value * Data2.Recordset.Fields("jg").Value, "#0.00")
     ' mySheet.Cells(curline, 12).Value = "￥" & Format(Data3.Recordset.Fields("dgs").Value * Data3.Recordset.Fields("jg").Value * Data3.Recordset.Fields("discount").Value, "#0.00")
     ' mySheet.Cells(curline, 12).Value = "￥" & Format(Data3.Recordset.Fields("dgs").Value * Data3.Recordset.Fields("jg").Value * discountx, "#0.00")