fzygl.frm

为个人用户开发的车险秘书系统

                    c.SubItems(10) = rs("学历") & ""
                    c.SubItems(11) = rs("专业") & ""
                    c.SubItems(12) = rs("入司前职业") & ""
                    c.SubItems(13) = rs("入司时间") & ""
                    c.SubItems(14) = rs("特长") & ""
                    c.SubItems(15) = rs("住址") & ""
                rs.MoveNext
                Loop
                rs.Close
                Set rs = Nothing
End Sub

Private Sub LoadData()
    Dim vdb As Boolean
    Dim rs As ADODB.Recordset
    Dim i, vcou As Integer
    Dim sql, sql1, vsh As String
    Dim arrdata() As Integer
    
    McScore.ShowLegend = True
    sql = "select 姓名,业绩 from member order by 业绩 desc"
    sql1 = "select count(*) from member"
    vdb = ExcSql
    If vdb = True Then
        vcou = conn.Execute(sql1)(0)
        
        ReDim arrdata(1 To vcou, 1 To 2)
        Set rs = conn.Execute(sql)
        If Not rs.EOF Then
            i = 1
           Do While Not rs.EOF
                    MsgBox rs("姓名") & i
                    arrdata(1, 1) = rs("姓名")
                    arrdata(1, 2) = rs("业绩")
                i = i + 1
                rs.MoveNext
            Loop
            McScore.ChartData = arrdata
    
        End If
        rs.Close
    End If
End Sub

Private Sub isz_Click()
    Dim icount, maxyj, icol, irow As Integer
    Dim rs As Recordset
    Dim sname  As String
   ' On Error GoTo serr
    
    icount = conn.Execute("select count(代号) from member")(0)
    maxyj = conn.Execute("select max(业绩) from member")(0)
    
    If icount > 0 Then
        With McScore
            .ChartType = VtChChartType2dBar
            .RowCount = icount
            .AllowSeriesSelection = True
            .AllowSelections = True
            .Column = 1
            .ColumnLabel = "业绩"
            .Plot.SeriesCollection(1).DataPoints(-1).EdgePen.VtColor.Set 0, 200, 0
            .Plot.SeriesCollection(1).DataPoints(-1).Brush.FillColor.Set 0, 200, 0
            .Plot.Axis(VtChAxisIdY).ValueScale.Auto = False
            .Plot.Axis(VtChAxisIdY).ValueScale.Minimum = 0
            .Plot.Axis(VtChAxisIdY).ValueScale.Maximum = maxyj
            .Plot.Axis(VtChAxisIdX).ValueScale.Auto = False
            .Plot.xGap = 2
            .Plot.Axis(VtChAxisIdY2).AxisScale.Hide = True
            .Plot.Axis(VtChAxisIdX).AxisGrid.MajorPen.Style = VtPenStyleNull
            .Plot.Axis(VtChAxisIdY).AxisGrid.MajorPen.Style = VtPenStyleNull
            .Title.Text = "组员业绩查看"
        End With
        
        Set rs = conn.Execute("select 代号 from member")
        With McScore
            For icol = 1 To .ColumnCount
                For irow = 1 To icount
                    .Column = icol
                    .Row = irow
                   ' MsgBox rs("代号")
                    .Data = conn.Execute("select 业绩 from member where 代号='" & rs("代号") & "'")(0)
                   .RowLabel = conn.Execute("select 姓名 from member where 代号='" & rs("代号") & "'")(0)
                    rs.MoveNext
                Next irow
            Next icol
        End With
        
    End If
    Exit Sub

serr:
    MsgBox "serr"
    
End Sub

Private Sub ListView1_Click()
    Dim sql As String
    Dim vdb As Boolean
    Dim rs As Recordset
    
    If ListView1.ListItems.Count > 0 Then
    Tadd.Text = "change"
    Tzy(0).Enabled = False
    
    
    Tzy(0).Text = ListView1.SelectedItem.Text
    Tzy(1).Text = ListView1.SelectedItem.ListSubItems(2).Text
    Tzy(2).Text = ListView1.SelectedItem.ListSubItems(4).Text
    Tzy(3).Text = ListView1.SelectedItem.ListSubItems(9).Text
    Tzy(4).Text = ListView1.SelectedItem.ListSubItems(10).Text
    Tzy(5).Text = ListView1.SelectedItem.ListSubItems(11).Text
    Tzy(6).Text = ListView1.SelectedItem.ListSubItems(14).Text
    If ListView1.SelectedItem.ListSubItems(5).Text = "" Then
        Tzy(7).Text = ListView1.SelectedItem.ListSubItems(5).Text
    Else
        Tzy(7).Text = Mid$(ListView1.SelectedItem.ListSubItems(5).Text, 1, Len(ListView1.SelectedItem.ListSubItems(4).Text) - 1)
    End If
    Tzy(8).Text = ListView1.SelectedItem.ListSubItems(14).Text
    Tzy(10).Text = ListView1.SelectedItem.ListSubItems(7).Text
    Tzy(11).Text = ListView1.SelectedItem.ListSubItems(12).Text
    Osex.Text = ListView1.SelectedItem.ListSubItems(3).Text
    Ohun.Text = ListView1.SelectedItem.ListSubItems(8).Text
    Osr.Text = ListView1.SelectedItem.ListSubItems(13).Text
    Osb.Text = ListView1.SelectedItem.ListSubItems(6).Text
    
    sql = "select EMAIL,备注 from member where 代号= '" & Trim$(ListView1.SelectedItem.Text) & "'"
    vdb = ExcSql
    If vdb = True Then
        Set rs = conn.Execute(sql)
        If Not rs.EOF Then
            Tzy(12).Text = rs("EMAIL") & ""
            Tmark.Text = rs("备注") & ""
        End If
        rs.Close
        Set rs = Nothing
    End If
    End If
End Sub

Private Sub Tzy_GotFocus(Index As Integer)
    Tzy(Index).BackColor = &HFFC0C0
    
End Sub

Private Sub Tzy_LostFocus(Index As Integer)
    Tzy(Index).BackColor = &HFFFFFF
End Sub


Private Sub isButton3_Click()
    Dim i As Integer
    
    For i = 0 To 12
        If i = 9 Then
        Else
            Tzy(i).Text = ""
        End If
    Next i
    
    Tmark.Text = ""
    Tzy(0).Enabled = True
    Tadd.Text = "add"
End Sub

Private Sub isButton4_Click()
    Call sqldel("member", "代号='" & ListView1.SelectedItem.Text & "' and 姓名='" & ListView1.SelectedItem.ListSubItems.Item(2).Text & "'")
    
    Call sqlsel
End Sub

Private Sub isButton5_Click()
    If Trim$(Tzy(0).Text) = "" Or Trim$(Tzy(1).Text) = "" Then
        MsgBox "信息填写不完整！"
        Exit Sub
    End If
    If Tadd.Text = "add" Then
        Call sqlinsert
    Else
        Call sqlupdate
    End If
    Call sqlsel
    Tadd.Text = ""
End Sub

Private Sub sqlsel()
    Dim sql As String
    Dim vdb As Boolean
    Dim rs As Recordset
    Dim c As ListItem
    
    sql = "select * from member order by 入司时间 desc"
    vdb = ExcSql
    If vdb = True Then
        Set rs = conn.Execute(sql)
        If Not rs.EOF Then
            ListView1.ListItems.Clear
            Do While Not rs.EOF
                Set c = ListView1.ListItems.Add(, , rs("代号") & "")
                    c.SubItems(1) = rs("代号") & ""
                    c.SubItems(2) = rs("姓名") & ""
                    c.SubItems(3) = rs("性别") & ""
                    c.SubItems(4) = rs("年龄") & ""
                    c.SubItems(5) = rs("业绩") & "万"
                    c.SubItems(6) = rs("生日") & ""
                    c.SubItems(7) = rs("手机号码") & ""
                    c.SubItems(8) = rs("婚姻状况") & ""
                    c.SubItems(9) = rs("籍贯") & ""
                    c.SubItems(10) = rs("学历") & ""
                    c.SubItems(11) = rs("专业") & ""
                    c.SubItems(12) = rs("入司前职业") & ""
                    c.SubItems(13) = rs("入司时间") & ""
                    c.SubItems(14) = rs("特长") & ""
                    c.SubItems(15) = rs("住址") & ""
            rs.MoveNext
            Loop
        End If
        rs.Close
        Set rs = Nothing
    End If
End Sub

Private Sub sqlinsert()
    Dim sql As String
    Dim vdb As Boolean
    Dim vnum As Integer
     Dim age As Integer
    If Trim$(Tzy(2).Text) = "" Then
        age = 0
    Else
        age = Trim$(Tzy(2).Text)
    End If
    
    vdb = ExcSql
    If vdb = True Then
        sql = "select count(*) from member where 代号='" & Tzy(0).Text & "'"
        vnum = conn.Execute(sql)(0)
    End If
    If vnum = 0 Then
    sql = "insert into member values('" & Trim$(Tzy(0).Text) & "','" & Trim$(Tzy(1).Text) & "','" & Trim$(Tzy(10).Text) & "'," & _
    " '" & Trim$(Osex.Text) & "'," & age & ",'" & Trim$(Tzy(3).Text) & "','" & Trim$(Ohun.Text) & "'," & _
    "'" & Trim$(Tzy(4).Text) & " ','" & Trim$(Tzy(5).Text) & "','" & Trim$(Tzy(11).Text) & "','" & Trim$(Osr.Text) & "'," & _
    "'" & Trim$(Osb.Text) & "','" & Trim$(Tzy(8).Text) & "','" & Trim$(Tzy(6).Text) & "','" & Trim$(Tzy(12).Text) & "','" & Trim$(Tmark.Text) & "','" & Trim$(Tzy(7).Text) & "')"
    
    Call exsql(sql)
    Call isButton3_Click
    Else
        MsgBox Tzy(0).Text & "组员代号已经存在"
    End If
End Sub

Private Sub sqlupdate()
    Dim sql As String
    Dim age As Integer
    If Trim$(Tzy(2).Text) = "" Then
        age = 0
    Else
        age = Trim$(Tzy(2).Text)
    End If
    sql = "update member set 姓名='" & Trim$(Tzy(1).Text) & "',手机号码='" & Trim$(Tzy(10).Text) & "',性别='" & Trim$(Osex.Text) & "',年龄=" & age & _
    ",籍贯='" & Trim$(Tzy(3).Text) & "',婚姻状况='" & Trim$(Ohun.Text) & "',学历='" & Trim$(Tzy(4).Text) & " ',专业='" & Trim$(Tzy(5).Text) & "'" & _
    ",入司前职业='" & Trim$(Tzy(11).Text) & "',入司时间=#" & Trim$(Osr.Text) & "#,生日=#" & Trim$(Osb.Text) & "#,住址='" & Trim$(Tzy(8).Text) & "'" & _
    ",特长='" & Trim$(Tzy(6).Text) & "',EMAIL='" & Trim$(Tzy(12).Text) & "',备注='" & Trim$(Tmark.Text) & "',业绩='" & Trim$(Tzy(7).Text) & "' where 代号='" & Trim$(Tzy(0).Text) & "'"

    Call exsql(sql)
    Call isButton3_Click
    Tzy(0).Enabled = True
    Tadd.Text = ""
End Sub

Private Sub sqldel(ByVal tbname As String, ByVal zdfr As String)
    Dim sql As String
    
    sql = "delete from " & tbname & " where " & zdfr
    Call exsql(sql)
End Sub

Private Sub isButton6_Click()
   Dim allnum, icol, ilevel As Integer
   Dim iarray(2) As Integer
   Dim dblarray(2) As Double
   Dim strarray(2) As String
   Dim strcoursename As String
   Dim rs As ADODB.Recordset
   Dim icount
   
   ilevel = 0
  ' On Error GoTo err
  ilevel = conn.Execute("select count(num) from allto where syear='" & Year(Date) & "'")(0)
  If ilevel > 0 Then
   allnum = conn.Execute("select num from allto where syear='" & Year(Date) & "'")(0)
    icount = conn.Execute("select sum(业绩) from member")(0)

    iarray(0) = icount
    iarray(1) = allnum - icount
    dblarray(0) = icount / allnum
    dblarray(1) = 1 - icount / allnum
    MsgBox dblarray(1)
    With McScore
        .ChartType = VtChChartType2dPie
        '.RowCount = 2
        .ColumnCount = 1
        .ColumnLabelCount = .ColumnCount
         .Plot.SeriesCollection(1).DataPoints(-1).EdgePen.VtColor.Set 255, 0, 0
        .Plot.SeriesCollection(1).DataPoints(-1).Brush.FillColor.Set 255, 0, 0
        .ShowLegend = True
        .Title.Text = Year(Date) & "年统计图表"
        .Title.TextLayout.HorzAlignment = VtHorizontalAlignmentCenter
    End With
    
    Set rs = conn.Execute("select 代号 from member")
    With McScore
        .Row = 1
        For icol = 1 To .ColumnCount
            .Column = icol
            .Data = dblarray(icol - 1)
            .ColumnLabel = strarray(icol - 1) & "总计：" & iarray(icol - 1)
            .RowLabel = conn.Execute("select 姓名 from member where 代号='" & rs("代号") & "'")(0)
        Next icol
        .RowLabel = "aaa"
    End With
    End If
    Exit Sub
err:
    MsgBox "err"
End Sub