frmsell.frm

Visual basic 数据库编程技术与实例源码 源码

                p_save = False
                p_isadding = False
                p_isediting = False
                datPrimaryRS.Recordset.MovePrevious
            Case "NEXT"
                p_save = False
                p_isadding = False
                p_isediting = False
                datPrimaryRS.Recordset.MoveNext
            Case "LAST"
                p_save = False
                p_isadding = False
                p_isediting = False
                datPrimaryRS.Recordset.MoveLast
            Case "DELETE"
                p_save = False
                p_isadding = False
                p_isediting = False
                Delete_Details
                With datPrimaryRS.Recordset
                    .Delete
                    .MoveNext
                    If .RecordCount > 0 And .EOF Then .MoveLast
                End With
    End Select
    Enable_Fields (Not p_save)
    Enable_Buttons
    txtTotal = dspTotal
    TerminalDescription = " 商品总额"
End Sub

Public Sub Enable_Buttons()
    On Error Resume Next
    Dim cur_rec, fst_rec, lst_rec, rec_cnt As Integer
    Dim mark_rec As Variant
    rec_cnt = datPrimaryRS.Recordset.RecordCount
    If rec_cnt > 0 Then
        If Not datPrimaryRS.Recordset.BOF Or Not datPrimaryRS.Recordset.EOF Then
            cur_rec = datPrimaryRS.Recordset.AbsolutePosition + 1
            mark_rec = datPrimaryRS.Recordset.Bookmark
        End If
        datPrimaryRS.Recordset.MoveFirst
        fst_rec = datPrimaryRS.Recordset.AbsolutePosition + 1
        datPrimaryRS.Recordset.MoveLast
        lst_rec = datPrimaryRS.Recordset.AbsolutePosition + 1
        If Not datPrimaryRS.Recordset.BOF Or Not datPrimaryRS.Recordset.EOF Then
            datPrimaryRS.Recordset.Bookmark = mark_rec
        End If
        If fst_rec = cur_rec Then
            p_top = False
            p_prev = False
            p_next = True
            p_last = True
        End If
        If lst_rec = cur_rec Then
            p_top = True
            p_prev = True
            p_next = False
            p_last = False
        End If
        If (rec_cnt >= 0 And rec_cnt <= 1) Then
            p_top = False
            p_prev = False
            p_next = False
            p_last = False
        End If
        If cur_rec <> fst_rec And cur_rec <> lst_rec Then
            p_top = True
            p_prev = True
            p_next = True
            p_last = True
        End If
    End If
        If rec_cnt = 0 Then 'And Not p_isadding Then
            p_add = True
            p_edit = False
            p_undo = False
            p_top = False
            p_prev = False
            p_next = False
            p_last = False
            p_del = False
        End If
        If rec_cnt > 0 And (Not p_isediting And Not p_isadding) Then
            p_add = True
            p_edit = True
            p_del = True
        End If
        If Not p_isediting And Not p_isadding Then
            p_save = False
            p_undo = False
        Else
            p_save = True
            p_undo = True
            p_add = False
            p_edit = False
            p_top = False
            p_prev = False
            p_next = False
            p_last = False
            p_del = False
        End If
    cmdAdd.Enabled = p_add
    cmdEdit.Enabled = p_edit
    cmdSave.Enabled = p_save
    cmdUndo.Enabled = p_undo
    cmdTop.Enabled = p_top
    cmdPrev.Enabled = p_prev
    cmdNext.Enabled = p_next
    cmdLast.Enabled = p_last
    cmdDelete.Enabled = p_del
    If p_del Then
        cmdFind.Enabled = IIf(rec_cnt > 1, True, False)
    Else
        cmdFind.Enabled = False
    End If
        cmdDetail.Enabled = p_add
End Sub

Private Sub titleBar_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Call DragForm(Me)
End Sub


Private Sub txtChange_Change()
    'TerminalCounter = txtChange
End Sub

Private Sub txtChange_GotFocus()
    TerminalDescription = " 找零"
    TerminalCounter = txtChange
End Sub

Private Sub Update_ORNumber()
    On Error Resume Next
    strs = "select * from CODE_FILE where CODE_NAME = 'ORNO'"
    
    Set dummy = New adodb.Recordset
    dummy.Open strs, myDB, 1, 3

    
    'Set dummy = frmLogin.db.OpenRecordset(strs)
    If Not dummy.BOF Then
        dummy.Edit
        dummy("CODE_VALUE") = CStr(CInt(dummy("CODE_VALUE")) + 1)
        dummy.Update
    End If
End Sub

Function Get_OrNo() As String
    On Error Resume Next
    strs = "select * from CODE_FILE where CODE_NAME = 'ORNO'"
    Set dummy = New adodb.Recordset
    dummy.Open strs, myDB, 1, 3
    
    
    'Set dummy = frmLogin.db.OpenRecordset(strs)
    If Not dummy.BOF Then
       Get_OrNo = Pad_Str(dummy("CODE_VALUE"), "0", 5, False)
    Else
       Call MessageBox("frmSelling", "Warning Or number not found in Code File!!", 0)
       frmMessageBox.SetFocus
    End If
End Function

Private Sub Delete_Details()
    On Error Resume Next
    strs = "select * from INVOICE_DETAIL where INVOICE_NOD = '" & txtField(0) & "'"
    Set dummy = New adodb.Recordset
    dummy.Open strs, myDB, 1, 3
    
    'Set dummy = frmLogin.db.OpenRecordset(strs)
    Do While Not dummy.EOF
        If dummy.AbsolutePosition <> -1 Then
            Call Update_Stocks(dummy("PRODCODE"), True, dummy("QTYSOLD"))
            dummy.Delete
        Else
             Exit Do
        End If
    Loop
End Sub

Function dspTotal() As String
    On Error Resume Next
    strs = "select sum(AMOUNT) from QRYINVOICE where INVOICE_NO = '" & IIf(txtField(0) = "", "0", txtField(0)) & "'"
    Set dummy = New adodb.Recordset
    dummy.Open strs, myDB, 1, 3
    
    'Set dummy = frmLogin.db.OpenRecordset(strs)
    dspTotal = Convert_Numeric(IIf(IsNull(dummy(0)), "0", dummy(0)), True)
    txtTotal.Text = dspTotal
    TerminalDescription = " Total Amount Due"
End Function

Function PrintInvoice()
    On Error Resume Next
    Today = Now
    datSecondaryRS.Recordset.MoveFirst
        Printer.FontName = "Courier New"
        Printer.FontSize = 10
        Printer.Print IIf(IsNull(datprimary2("COMPANY_NAME")), "", Left(datprimary2("COMPANY_NAME"), 28))
        Printer.FontSize = 8
        Printer.Print IIf(IsNull(datprimary2("COMPANY_ADDRESS")), "", datprimary2("COMPANY_ADDRESS"))
        Printer.Print IIf(IsNull(datprimary2("COMPANY_TELEPHONE")), "", datprimary2("COMPANY_TELEPHONE"))
        Printer.Print IIf(IsNull(datprimary2("SELLING_HEADER")), "", datprimary2("SELLING_HEADER"))
        Printer.Print Space(1)
        Printer.Print "              THIS IS AN OFFICIAL          "
        Printer.Print "                    RECEIPT                "
        Printer.Print Space(1)
        Printer.Print "Inv. #" + txtField(0)
        Printer.Print "OR #" + txtField(1)
        Printer.Print "==========================================="
        Printer.Print "DESCRIPTION                 QTY      AMOUNT"
        Printer.Print "==========================================="
    Do While Not datSecondaryRS.Recordset.EOF
        Printer.Print Pad_Str(grdDataGrid.Columns(0), " ", 27, True) + _
                      Space(1) + Space(5 - Len(Convert_Numeric(grdDataGrid.Columns(1), False))) + _
                      Convert_Numeric(grdDataGrid.Columns(1), False) + _
                      Space(1) + Space(9 - Len(Convert_Numeric(grdDataGrid.Columns(2), True))) + _
                      Convert_Numeric(grdDataGrid.Columns(2), True)
        datSecondaryRS.Recordset.MoveNext
    Loop
        Printer.Print "                                 ----------"
        Printer.Print Pad_Str("TOTAL", " ", 27, True) + Space(7) + Space(9 - Len(IIf(Convert_Numeric(txtTotal, True) = 0, "", Convert_Numeric(txtTotal, True)))) + _
                        IIf(Convert_Numeric(txtTotal, True) = 0, "", Convert_Numeric(txtTotal, True))
        Printer.Print Pad_Str("CASH", " ", 27, True) + Space(7) + Space(9 - Len(IIf(Convert_Numeric(txtPaid, True) = 0, "", Convert_Numeric(txtPaid, True)))) + _
                        IIf(Convert_Numeric(txtPaid, True) = 0, "", Convert_Numeric(txtPaid, True))
        Printer.Print Pad_Str("CHANGE", " ", 27, True) + Space(7) + Space(9 - Len(IIf(Convert_Numeric(txtChange, True) = 0, "", Convert_Numeric(txtChange, True)))) + _
                        IIf(Convert_Numeric(txtChange, True) = 0, "", Convert_Numeric(txtChange, True))
        Printer.Print "                                 =========="
        Printer.Print Format(Today, "dd " & "mmmm " & "yyyy ") & Space(1) & Format(Today, "hh:mm:ss ampm ")
        Printer.Print frmLogin.txtUserName
        Printer.Print IIf(IsNull(datprimary2("SELLING_FOOTER")), "", datprimary2("SELLING_FOOTER"))
        Printer.EndDoc
End Function

Private Sub txtPaid_Change()
    txtChange.Text = Convert_Numeric(txtPaid, True) - Convert_Numeric(txtTotal, True)
    TerminalCounter = txtPaid
End Sub

Private Sub txtPaid_GotFocus()
    TerminalDescription = " 实收"
    TerminalCounter = txtPaid
End Sub

Private Sub txtPaid_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        AddtoDB
        txtWord.SetFocus
    End If
End Sub

Private Sub txtTotal_Change()
    TerminalCounter = txtTotal
End Sub


Private Sub txtWord_Change()
    TerminalDescription = "商品总额"
    'txpchange清空
    txtChange.Text = ""
    'txtpaid清空
    txtPaid.Text = ""

End Sub

'Private Sub lstWords_Click()
'    On Error Resume Next
'    Call QueryDetail(lstWords.List(lstWords.ListIndex))
'End Sub


Private Sub txtWord_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
        If txtWord.Text = "" Then
            'txtPaid.SetFocus
            If ListView1.ListItems.Count > 0 Then
                txtPaid.SetFocus
                Label1.Caption = "": Label2.Caption = ""
            End If
            'txtWord.SetFocus
            'EnterNum = EnterNum + 1
            Exit Sub
        End If
        
        If QueryDetail(txtWord.Text) Then
            txtWord.Text = ""
            txtWord.SetFocus
        Else
            txtWord.Text = ""
            txtWord.SetFocus
            Call MessageBox("前台销售", "商品编号不存在", 0)
        End If
    End If
End Sub

Private Sub AddtoDB()
Dim i As Integer
Dim strs As String
    With ListView1
        strs = "insert into invoice (Invoice_No,DateSold) values ('" & txtField(0).Text & "','" & Format(DTPick.Value, "yyyy-mm-dd") & "')"
        myDB.Execute (strs)
        '循环将销售记录插入到数据库
        For i = 1 To .ListItems.Count
            strs = "insert into invoice_detail (Invoice_NoD,ProdCode,QtySold) values ('" & _
              txtField(0).Text & "','" & .ListItems(i).Text & "'," & .ListItems(i).SubItems(2) & ")"
            Debug.Print strs
            myDB.Execute (strs)
            strs = "update prod_stocks set Quan=Quan-" & .ListItems(i).SubItems(2)
            myDB.Execute (strs)
        Next i
        '清空
        .ListItems.Clear
    End With
    '销售清单编号增加
    InvoiceNum = InvoiceNum + 1
    txtField(0).Text = CStr(InvoiceNum)
    '商品总额清零
    TotalMoney = 0
    txtTotal.Text = ""
    '显示找零信息
    TerminalCounter.Caption = txtChange
    '显示找零金额
    TerminalDescription.Caption = "找零"

End Sub
' 通过SQL语句查询是否商品编码存在
Function QueryDetail(reqText As String) As Boolean
    On Error Resume Next
    Dim SQLtext As String, SQLText2 As String
    Dim unit_cost As Single
        SQLtext = "SELECT * FROM PROD_STOCKS WHERE ProdCode" & _
                   "='" & reqText & "';"
        'Set rst = frmLogin.db.OpenRecordset(SQLtext)
        Set rst = New adodb.Recordset
        rst.Open SQLtext, myDB, 1, 3
        'frmSelling.lstWords.Clear
        If rst.BOF Then
            'lstWords.AddItem "No item was found"
            txtProductCode.Text = ""
            txtUnitCost.Text = ""
            txtAvailableStock = ""
            txtTotalCost.Text = ""
            QueryDetail = False
            Exit Function
        End If

        With ListView1
            .ListItems.Add , , txtWord.Text, 1, 1
            Label1.Caption = IIf(IsNull(rst.Fields("GenericName")), "", rst.Fields("GenericName"))
            .ListItems(.ListItems.Count).SubItems(1) = Label1.Caption
            .ListItems(.ListItems.Count).SubItems(2) = txtSellNum.Text
            unit_cost = IIf(IsNull(rst.Fields("UNIT_COST")), 0, rst.Fields("UNIT_COST"))
            Label2.Caption = Convert_Numeric(IIf(IsNull(rst.Fields("UNIT_COST")), 0, rst.Fields("UNIT_COST")), True)
            .ListItems(.ListItems.Count).SubItems(3) = Label2.Caption
            '统计总价格
            TotalMoney = unit_cost * CInt(txtSellNum.Text) + TotalMoney
            '显示应付款
            TerminalCounter.Caption = Convert_Numeric(CStr(TotalMoney), True)
            '记录应付帐
            txtTotal.Text = TerminalCounter.Caption
        End With
       
        QueryDetail = True
End Function