frminquireincondition.frm

VB礼品店销售管理系统 包括ACCESS数据库访问职工管理 账单管理

                    "where saledate>=cdate('" & startdate & "') and saledate<cdate('" & _
                     enddate & "') group by productid order by productid "

        Set cn = New ADODB.Connection
        cn.Provider = "Microsoft.Jet.OLEDB.4.0"
        strconn = "Data Source=" & dbFile & ";Persist Security Info=False"
        cn.Open ConnectionString:=strconn
        Set rs = New ADODB.Recordset
        rs.ActiveConnection = cn
        rs.CursorType = adOpenStatic
        Set rs1 = New ADODB.Recordset
        rs1.ActiveConnection = cn
        rs1.CursorType = adOpenStatic
        
        sql_del = "delete from tabStatic"
        cn.Execute sql_del
        
        rs.Open sqlstring
        If Not rs.BOF Then
            rs.MoveFirst
            i = 1
            tNumber = 0
            tLiRun = 0
            While Not rs.EOF
                tNumber = tNumber + rs.Fields(1) '销售总量
                tLiRun = tLiRun + rs.Fields(2)   '总利润
                sql_sel = "select productname,Number,remainNumber,TreeSort from product where ProductID='" & Trim(rs.Fields("productid")) & "'"
                rs1.Open sql_sel
                sql_ins = "insert into tabStatic(id,productid,productname,InNumber,RemainNumber,TreeSort,salesnumber,lirun) " & _
                           "  values(" & i & ",'" & rs.Fields("productid") & "','" & _
                           rs1.Fields("productname") & "'," & rs1.Fields("Number") & "," & rs1.Fields("remainNumber") & _
                           ",'" & rs1.Fields("treesort") & "'," & rs.Fields(1) & "," & rs.Fields(2) & ")"
                cn.Execute sql_ins
                rs1.Close
                rs.MoveNext
                i = i + 1
            Wend
        End If
        rs.Close
        cn.Close
        Set rs1 = Nothing
        Set rs = Nothing
        Set cn = Nothing
        
        frmSalesinfo.Adodc3.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile & ";Persist Security Info=False"
        sqlstring = "select * from tabStatic order by id"
        frmSalesinfo.Adodc3.CommandType = adCmdText
        frmSalesinfo.Adodc3.RecordSource = sqlstring
        Set frmSalesinfo.DataGrid3.DataSource = frmSalesinfo.Adodc3
        frmSalesinfo.Adodc3.Refresh
               
        frmSalesinfo.txtNumber = tNumber
        frmSalesinfo.txtLirun = "￥" & Format(Str(tLiRun), "###,###,###.00")
        Unload Me
        frmSalesinfo.Show
    ElseIf mode = 3 Then
        If cmbPersonId = "" Then
            MsgBox "请选择相应的员工编号！", 48, "信息提示"
            Exit Sub
        End If
        Set cn = New ADODB.Connection
        cn.Provider = "Microsoft.Jet.OLEDB.4.0"
        strconn = "Data Source=" & dbFile & ";Persist Security Info=False"
        cn.Open ConnectionString:=strconn
        Set rs = New ADODB.Recordset
        rs.ActiveConnection = cn
        rs.CursorType = adOpenStatic
        
        sqlstring = "select * from usemanage where userid='" & cmbPersonId & "'"
        rs.Open sqlstring
        If Not rs.BOF Then
            frmstaff_salary.txtId = rs.Fields("userid")
            frmstaff_salary.txtName = rs.Fields("RealName")
            frmstaff_salary.txtIndentity = rs.Fields("Idefication")
            frmstaff_salary.txtBasic = rs.Fields("BasicDeduct")
            frmstaff_salary.txtOver = rs.Fields("OverDeduct")
            frmstaff_salary.txtOther = rs.Fields("OtherDeduct")
            frmstaff_salary.txtAddress = rs.Fields("Address")
            frmstaff_salary.txtPhone = rs.Fields("Tel")
            frmstaff_salary.txtEmail = rs.Fields("Email")
            t1 = CSng(rs.Fields("BasicDeduct"))
            t2 = CSng(rs.Fields("OverDeduct"))
            t3 = CSng(rs.Fields("OtherDeduct"))
            rs.Close
            
            sqlstring = "select a.productid,a.ProductName,b.treesort,a.SalesPrice,b.lowestprice," & _
                        " a.Shuliang,a.saledate,a.salespersonId from tabsales a,product b " & _
                        " where (a.saledate>=cdate('" & startdate & "') and a.saledate<cdate('" & _
                         enddate & "')) and a.salespersonId='" & cmbPersonId & "' and a.productid=b.productid order by a.saledate desc"
            rs.Open sqlstring
            totalSalary = 0
            
            sql_del = "delete from personSalary"
            cn.Execute sql_del
            
            i = 1
            If Not rs.BOF Then
                rs.MoveFirst
                While Not rs.EOF
                    lprice = CSng(rs.Fields("LowestPrice"))
                    If CSng(rs.Fields("salesprice")) <= lprice Then
                        lirun = CSng(rs.Fields("salesprice")) * t1
                    Else
                        lirun = (lprice * t1 + (CSng(rs.Fields("salesprice")) - lprice) * t2 + _
                                 CSng(rs.Fields("salesprice")) * t3) * CLng(rs.Fields("shuliang"))
                    End If
                    totalSalary = totalSalary + lirun
                    sql_ins = "insert into personSalary(sequenceId,ProductId,ProductName,TreeSort," & _
                              "salesPrice,lowestPrice,[number],salesdate,salesPersonId,ticheng) values(" & _
                              i & ",'" & rs.Fields("productid") & "','" & rs.Fields("ProductName") & _
                              "','" & rs.Fields("treesort") & "'," & rs.Fields("SalesPrice") & "," & _
                              rs.Fields("lowestprice") & "," & rs.Fields("shuliang") & ",'" & _
                              rs.Fields("saledate") & "','" & rs.Fields("salespersonId") & "'," & lirun & ")"
                    cn.Execute sql_ins
                    i = i + 1
                    rs.MoveNext
                Wend
            End If
            rs.Close
            Set rs = Nothing
            Set cn = Nothing
            frmstaff_salary.Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile & ";Persist Security Info=False"
            sqlstring = "select * from personSalary order by id"
            frmstaff_salary.Adodc1.CommandType = adCmdTable
            frmstaff_salary.Adodc1.RecordSource = "personSalary"
            frmstaff_salary.Adodc1.Refresh
            Set frmstaff_salary.DataGrid1.DataSource = frmstaff_salary.Adodc1
            frmstaff_salary.txtSalary = "￥" & Format(Str(totalSalary), "###,###,###.00")
        Else
            rs.Close
            Set rs = Nothing
            Set cn = Nothing
            MsgBox "没有此员工的相应信息，请重新查询！", 48, "信息提示"
            Exit Sub
        End If
        Unload Me
        frmstaff_salary.Show
    End If
    Unload Me
    Exit Sub
errhandle:
        MsgBox "有错误发生，错误编号为：" & Err.Number, 48, "信息提示"
End Sub

Private Sub Form_Load()
On Error GoTo errhandle
    DTPstart.Value = Date
    DTPend.Value = Date
    
    If mode = 3 Then
        Set cn = New ADODB.Connection
        cn.Provider = "Microsoft.Jet.OLEDB.4.0"
        strconn = "Data Source=" & dbFile & ";Persist Security Info=False"
        cn.Open ConnectionString:=strconn
        Set rs = New ADODB.Recordset
        rs.ActiveConnection = cn
        rs.CursorType = adOpenStatic
    
        sqlstring = "select userid from Usemanage where role='1' order by userid"
        rs.Open sqlstring
        rs.MoveFirst
        While Not rs.EOF
            cmbPersonId.AddItem (rs.Fields("userid"))
            rs.MoveNext
        Wend
        rs.Close
        Set rs = Nothing
        Set cn = Nothing
    End If
    Exit Sub
errhandle:
        MsgBox "有错误发生，错误编号为：" & Err.Number, 48, "信息提示"
End Sub