销售系统搭建（二）

2023-08-31
作者 John DD
~2.31K 字

最后一个章节就是调用所有的数据，这个部分很简单了，直接上代码

一、初始化数组

'初始化数组
Sub InitTableFrom()
    ReDim tableFrom(1 To 22) As Variant
    
    tableFrom(1) = "数据总表.id"
    tableFrom(2) = "数据总表.省份"
    tableFrom(3) = "数据总表.市"
    tableFrom(4) = "数据总表.[区/县]"
    tableFrom(5) = "数据总表.地址信息"
    tableFrom(6) = "数据总表.学校名称"
    tableFrom(7) = "数据总表.联系方式"
    tableFrom(8) = "数据总表.学校级别"
    tableFrom(9) = "数据总表.学校性质"
    tableFrom(10) = "数据总表.分配时间"
    tableFrom(11) = "cint(T.回访数据总数)"
    tableFrom(12) = "R.回访时间"
    tableFrom(13) = "R.触达结果"
    tableFrom(14) = "R.接通部门"
    tableFrom(15) = "R.情况说明"
    tableFrom(16) = "R.意向等级"
    tableFrom(17) = "R.是否与校长建联"
    tableFrom(18) = "跟进结果.加微信"
    tableFrom(19) = "跟进结果.加公众号"
    tableFrom(20) = "跟进结果.是否应约峰会"
    tableFrom(21) = "跟进结果.签约"
    tableFrom(22) = "销售.姓名"
End Sub

'获取所有数据
Sub 客户跟进数据()
    Dim sql As String
    Dim columsTitle As String
    Dim salesName As String
    
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.AutoFilterMode = False
    End If
    
    
    salesName = asycSalerName
    
    'salesName = "孙阔"
    
    Call 连接数据库
    
    
    InitTableFrom ' 初始化数组
    
    columsTitle = Join(tableFrom, ", ") ' 构建新的列名部分
    

    sql = "SELECT " & columsTitle & " " _
    & "FROM ((((数据总表 " _
    & "LEFT JOIN " _
    & "(SELECT 回访.数据总表id, COUNT(回访.数据总表id) AS 回访数据总数, Max(回访.id) AS 最后回访数据 " _
    & "FROM 回访 " _
    & "GROUP BY 回访.数据总表id) AS T ON 数据总表.id = T.数据总表id) " _
    & "LEFT JOIN 回访 AS R ON T.最后回访数据 = R.id) " _
    & "LEFT JOIN 销售 ON 数据总表.跟进人id = 销售.id) " _
    & "LEFT JOIN 跟进结果 ON 数据总表.id = 跟进结果.学校名称id) " _
    & "WHERE 销售.姓名 = '" & salesName & "'"


    
    Set rs = New ADODB.Recordset
    rs.Open sql, cnn, adOpenKeyset, adLockOptimistic
    
    Sheets("客户跟进表").Range("c15").CopyFromRecordset rs
    
    '释放对象变量空间
    rs.Close: Set rs = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

'获取销售的名字
Function asycSalerName() As Variant

    Dim ws As Worksheet
    Dim dataValues As Variant
    
    ' 设置工作表对象
    Set ws = ThisWorkbook.Sheets("客户跟进表")
    
    ' 定义要获取数据的区域范围
    dataValues = ws.Range("c8").value

    asycSalerName = dataValues

    Set ws = Nothing
    
End Function