frmquerylog.frm

水电费收费管理系统

                .FormatString = "< 楼     号 |<房   主|<银行帐号|>上月表数|>本月表数|" & _
                            ">电 量|>单 价|>金   额|>上月表数|>本月表数|" & _
                            ">水量|>单价|>金  额|>合计金额|>上月时间|>本月时间|"
                            
                .Rows = 1
                .Cols = 16
                MySQL = "select * from user1 where rtuid=" & cmbRTU.Text
                If cboPrecinct.ListIndex <> -1 Then
                    MySQL = MySQL & " and precinct in (select elementid from bmpowerelement where  memo='" & cboPrecinct.ItemData(cboPrecinct.ListIndex) & "')"
                End If
                MySQL = MySQL & " order by precinct,userid"
                Set mySET = Mydb.OpenRecordset(MySQL, 2)
                Do Until mySET.EOF
                    .Rows = .Rows + 1
                    .Row = .Rows - 1
                    sTotalFee = 0
                    MySQL = "select * from powerfee where meterid=" & _
                            Val(mySET("AmmeterAddr")) & _
                            " and year=" & dtpStart.Year & " and month=" & _
                            dtpStart.Month
                    Set MyFeeset = Mydb.OpenRecordset(MySQL, 2)
                    If MyFeeset.EOF Then
                        'MsgBox "本月电费数据不存在，请运行电费转换程序！"
                        Exit Do
                    End If
                    MySQL = "select * from powerfee where meterid=" & _
                            Val(mySET("AmmeterAddr")) & _
                            " and  year=" & _
                            IIf(dtpStart.Month = 1, dtpStart.Year - 1, dtpStart.Year) & _
                            " and month=" & dtpStart.Month - 1
                    Set MyFeeSet1 = Mydb.OpenRecordset(MySQL, 2)
                    If MyFeeSet1.EOF Then
                        'MsgBox "上月电费数据不存在，请运行电费转换程序！"
                        Exit Do
                    End If
                    .Col = 0
                    .Text = mySET("Address") & mySET("usernumber")
                    .Col = 1
                    .Text = mySET("username")
                    .Col = 2
                    MySQL = "select * FROM bmPowerelement WHERE ElementId=" & mySET("Precinct")
                    Set MySet2 = Mydb.OpenRecordset(MySQL, 2)
                    .Text = mySET("ZIPCODE") 'MySet2("ElementName")
                    
                    .Col = 3
                    .Text = MyFeeSet1("reading")
                    .Col = 4
                    .Text = MyFeeset("reading")
                   ' .Col = 5
'                    .Text = 1
'                    .Col = 7
                    '.Text = MyFeeSet1("changevalue")
                    .Col = 5
                    .Text = MyFeeset("reading") - MyFeeSet1("reading") + MyFeeSet1("changevalue")
                    sTotalValue_D = sTotalValue_D + .Text
                    .Col = 6
                    .Text = Format(mySET("Price"), "0.000")
                    .Col = 7
                    .Text = Format((MyFeeset("reading") - MyFeeSet1("reading") + _
                            MyFeeSet1("changevalue")) * mySET("Price"), "0.0")
                    sTotalFee = Val(.Text)
                    sTotalFee_D = sTotalFee_D + Val(.Text)
                    MySQL = "select * from powerfee where meterid=" & _
                            Val(mySET("WaterAddr")) & _
                            " and year=" & dtpStart.Year & " and month=" & _
                            dtpStart.Month
                    Set MyFeeset = Mydb.OpenRecordset(MySQL, 2)
                    If MyFeeset.EOF Then
                        'MsgBox "本月电费数据不存在，请运行电费转换程序！"
                        Exit Do
                    End If
                    MySQL = "select * from powerfee where meterid=" & _
                            Val(mySET("WaterAddr")) & _
                            " and  year=" & _
                            IIf(dtpStart.Month = 1, dtpStart.Year - 1, dtpStart.Year) & _
                            " and month=" & dtpStart.Month - 1
                    Set MyFeeSet1 = Mydb.OpenRecordset(MySQL, 2)
                    If MyFeeSet1.EOF Then
                        'MsgBox "上月电费数据不存在，请运行电费转换程序！"
                        Exit Do
                    End If
                    .Col = 8
                    .Text = MyFeeSet1("reading")
                    .Col = 9
                    .Text = MyFeeset("reading")
'                    .Col = 13
'                    .Text = mySET("WaterConstant")
                    '.Col = 11
                    '.Text = MyFeeSet1("changevalue")
                    .Col = 10
                    .Text = MyFeeset("reading") - MyFeeSet1("reading") + MyFeeSet1("changevalue")
                    sTotalValue_Water = sTotalValue_Water + .Text
                    .Col = 11
                    .Text = mySET("WaterPrice")
                    .Col = 12
                    .Text = Format((MyFeeset("reading") - MyFeeSet1("reading") + _
                            MyFeeSet1("changevalue")) * mySET("waterPrice"), "0.0")
                    sTotalFee = sTotalFee + Val(.Text)
                    sTotalFee_Water = sTotalFee_Water + Val(.Text)
                    .Col = 13
                    .Text = Format(sTotalFee, "0.0")
        
                    'Dim MyFeeSet2 As Recordset
                    'MySQL = "select * from fee where usernumber='" & mySET("userID") & _
                    '        "' and rtuid=" & mySET("Rtuid")
                    'Set MyFeeSet2 = Mydb.OpenRecordset(MySQL, 2)
                    .Col = 14
                   ' .Text = Format(MyFeeSet2("fee") - sTotalFee, "0.00")
                    
                    .Text = MyFeeSet1("colltime")
                    .Col = 15
                    .Text = MyFeeset("colltime")
                    mySET.MoveNext
                    
                Loop
'                For i = 0 To 9
'                    .ColWidth(i) = 1000
'                Next
                .Rows = .Rows + 1
                .Row = .Rows - 1
                .Col = 0: .Text = "合计"
                .Col = 5: .Text = Format(sTotalValue_D, , "0")
                .Col = 7: .Text = Format(sTotalFee_D, "0.0")
                .Col = 10: .Text = Format(sTotalValue_Water, "0")
                .Col = 12: .Text = Format(sTotalFee_Water, "0.0")
                .Col = 13: .Text = Format(sTotalFee_D + sTotalFee_Water, "0.0")
                .ColWidth(2) = 2100
               ' .ColWidth(3) = 850
                
                .ColWidth(14) = 0
                .ColWidth(15) = 0
            End With
        Case 1  '打印
              On Error Resume Next
            'On Error GoTo ErrHandler
            dlgCommonDialog.CancelError = True
            dlgCommonDialog.ShowPrinter
            If Err.Number = 32755 Then Exit Sub
            Printer.ScaleMode = vbMillimeters           '设置度量单位为毫米
            Printer.ColorMode = vbPRCMColor       '设置打印机为单色打印输出
            Print_H = Printer.Height / 56.7
            Print_W = Printer.Width / 56.7
'            Printer.Orientation = vbPRORPortrait  '设置横向打印
'            Printer.PrintQuality = vbPRPQHigh     '设置打印质量为低分辨率
            Printer.DrawWidth = 1
            dlgCommonDialog.Copies = 1
'            dlgCommonDialog.Min = 1
'            dlgCommonDialog.Max = 1
'            dlgCommonDialog.FromPage = 1
'            dlgCommonDialog.ToPage = 1
            'CY = 30
            With mfgFee
                If MsgBox("打印电费收据请按“是”,打印电费明细请按“否”", vbYesNo) = vbYes Then
                     
                      .Row = 0
                    For u = 1 To .Rows
                        For i = 0 To 11
                            CY = i * 24 - 20
                            Printer.FontSize = 10
                            Printer.CurrentX = 60
                            Printer.CurrentY = 20 + CY
                            .Row = .Row + 1
                            Printer.FontSize = 10
                            Dim a As String, b As String
                            .Col = 14
                            a = .Text
                            .Col = 15
                            b = .Text
                           .Row = .Row - 1
                            Printer.Print a & "至" & b & "水电费用银行转帐凭证"
                           
                            Printer.DrawStyle = 0
                            Printer.Line (2, 26 + CY)-(197, 38 + CY), 0, B
                            Printer.Line (2, 32 + CY)-(195, 32 + CY), 0, B
                           
                            
                            Printer.Line (21, 26 + CY)-(21, 38 + CY), 0, B
                            Printer.Line (36, 26 + CY)-(36, 38 + CY), 0, B
                            'Printer.Line (85, 26 + CY)-(85, 38 + CY), 0, B
                            Printer.Line (53, 26 + CY)-(53, 38 + CY), 0, B
                            Printer.Line (71, 26 + CY)-(71, 38 + CY), 0, B
                            'Printer.Line (134, 26 + CY)-(134, 38 + CY), 0, B
                            Printer.Line (82, 26 + CY)-(82, 38 + CY), 0, B
                            Printer.Line (94, 26 + CY)-(94, 38 + CY), 0, B
                            Printer.Line (110, 26 + CY)-(110, 38 + CY), 0, B
                            Printer.Line (127, 26 + CY)-(127, 38 + CY), 0, B

                            Printer.Line (144, 26 + CY)-(144, 38 + CY), 0, B
                            'Printer.Line (223, 26 + CY)-(223, 38 + CY), 0, B
                            Printer.Line (155, 26 + CY)-(155, 38 + CY), 0, B
                            Printer.Line (164, 26 + CY)-(164, 38 + CY), 0, B
                            Printer.Line (179, 26 + CY)-(179, 38 + CY), 0, B
                            
                            
                            
                            Printer.FontSize = 10
                            
                            Printer.CurrentX = 5
                            Printer.CurrentY = 27 + CY
                            Printer.Print "楼    号"
                            Printer.CurrentX = 23
                            Printer.CurrentY = 27 + CY
                            Printer.Print "房  主"
                            Printer.CurrentX = 38
                            Printer.CurrentY = 27 + CY
'                            Printer.Print "倍率"
                           ' Printer.Print "银行帐号"
                            'Printer.CurrentX = 88
                            'Printer.CurrentY = 27 + CY
                            Printer.Print "上月表数"
                            Printer.CurrentX = 55
                            Printer.CurrentY = 27 + CY
                            Printer.Print "本月表数"
                            'Printer.CurrentX = 123
                            'Printer.CurrentY = 27 + CY
                            'Printer.Print "换表差"
                            Printer.CurrentX = 72
                            Printer.CurrentY = 27 + CY
                            Printer.Print "电 量"
                            Printer.CurrentX = 84
                            Printer.CurrentY = 27 + CY
                            Printer.Print "单 价"
                            Printer.CurrentX = 96
                            Printer.CurrentY = 27 + CY
                            Printer.Print "金  额"
                            Printer.CurrentX = 112
                            Printer.CurrentY = 27 + CY
                            Printer.Print "上月表数"
                            Printer.CurrentX = 128
                            Printer.CurrentY = 27 + CY
                            Printer.Print "本月表数"
                            'Printer.CurrentX = 212
                            'Printer.CurrentY = 27 + CY
                            'Printer.Print "换表差"
                            Printer.CurrentX = 147
                            Printer.CurrentY = 27 + CY
                            Printer.Print "水量"
                            Printer.CurrentX = 156
                            Printer.CurrentY = 27 + CY
                            Printer.Print "单价"
                            Printer.CurrentX = 166
                            Printer.CurrentY = 27 + CY
                            Printer.Print "金  额"
                            Printer.CurrentX = 180
                            Printer.CurrentY = 27 + CY
                            Printer.Print "合计金额"
                            
                            
                            .Row = .Row + 1
                            .Col = 0
                            Printer.CurrentX = 4
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                            
                            .Col = 1
                            Printer.CurrentX = 23
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                             .Col = 3
                            Printer.CurrentX = 39
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                            .Col = 4
                            Printer.CurrentX = 55
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                           
                            .Col = 5
                            Printer.CurrentX = 72
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                            
                            .Col = 6
                            Printer.CurrentX = 83
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                            
                            '电量
                            .Col = 7
                            Printer.CurrentX = 95
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                            
                            .Col = 8
                            Printer.CurrentX = 111
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text
                            
                            .Col = 9
                            Printer.CurrentX = 129
                            Printer.CurrentY = 33 + CY
                            Printer.Print .Text