📄 frmphclerkmx.frm
字号:
If result.RowCount <> 0 Then
i = 1
Do While Not result.EOF()
MSFlex1.TextArray(i * MSFlex1.Cols) = Trim(result("负责人"))
MSFlex1.TextArray(i * MSFlex1.Cols + 1) = Trim(datel)
result.MoveNext
i = i + 1
Loop
End If
''''''''计算平米数和画面数 ''''''''
For i = 1 To MSFlex1.Rows - 2
sql = "select * from JTSEtable where 日期='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols + 1)) & "' and 负责人='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols)) & "'"
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
result.MoveLast
MSFlex1.TextArray(i * MSFlex1.Cols + 3) = result.RowCount
result.MoveFirst
End If
RecordHJ = 0
If result.RowCount <> 0 Then
Do While Not result.EOF()
RecordHJ = RecordHJ + Mid(result("画面规格"), 1, InStr(result("画面规格"), "*") - 1) * Mid(result("画面规格"), (InStr(result("画面规格"), "*") + 1), Len(result("画面规格")) - InStr(result("画面规格"), "*"))
result.MoveNext
Loop
End If
MSFlex1.TextArray(i * MSFlex1.Cols + 4) = RecordHJ
Next i
''''查询JTSEtable表,得到记录个数
If Trim(Combo2.Text) = "所有" Then
sql = "select * from JTSEtable where 日期='" & Trim(datel) & "'"
Else
sql = "select * from JTSEtable where 日期='" & Trim(datel) & "' and 负责人='" & Trim(Combo2.Text) & "'"
End If
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
result.MoveLast
Records = result.RowCount
result.MoveFirst
End If
''''''存储记录到数组中''''
ReDim RecordDate(Records) As String
ReDim RecordHThao(Records) As String
ReDim RecordDesigner(Records) As String
If result.RowCount <> 0 Then
i = 0
Do While Not result.EOF()
RecordDate(i) = Trim(result("日期"))
RecordHThao(i) = Mid(Trim(result("文件号")), 1, 5)
RecordDesigner(i) = Trim(result("负责人"))
result.MoveNext
i = i + 1
Loop
End If
'''''删除临时表CouHTnumbers''''
sql = "delete from CouHTnumbers"
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
For i = 1 To MSFlex1.Rows - 2
sql = "insert into CouHTnumbers(日期,设计员,合同号) values('" & Trim(RecordDate(i - 1)) & "','" & Trim(RecordDesigner(i - 1)) & "','" & Trim(RecordHThao(i - 1)) & "')"
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
Next i
''''''查询合同数''''''
For i = 1 To MSFlex1.Rows - 2
sql = "select distinct 合同号 from CouHTnumbers where 日期='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols + 1)) & "' and 设计员='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols)) & "'"
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
MSFlex1.TextArray(i * MSFlex1.Cols + 2) = result.RowCount
End If
Next i
'''''''计算合计''''
For i = 0 To 3
HJ(i) = 0
Next i
For i = 1 To MSFlex1.Rows - 2
HJ(0) = HJ(0) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 2))
HJ(1) = HJ(1) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 3))
HJ(2) = HJ(2) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 4))
Next i
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols) = "合计:"
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 2) = HJ(0)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 3) = HJ(1)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 4) = HJ(2)
End If
'''布置电子表格'''''''查询条件2
If Trim(Combo1.Text) = "操作员" Then
MSFlex1.Cols = 8
MSFlex1.FormatString = "^ 操作员 |^ 日 期 |^ 喷绘序列 |^ 总面积 |^ 幅宽 |^ 原有长度 |^实际用布长度|^ 剩余长度 "
If Trim(Combo2.Text) <> "所有" Then
sql = "select distinct 操作员 from JTPRtable where 日期='" & Trim(datel) & "' and 操作员='" & Trim(Combo2.Text) & "'"
Else
sql = "select distinct 操作员 from JTPRtable where 日期='" & Trim(datel) & "'"
End If
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
result.MoveLast
MSFlex1.Rows = result.RowCount + 2
result.MoveFirst
End If
'''''显示操作员名称'''''''''''
If result.RowCount <> 0 Then
i = 1
Do While Not result.EOF()
MSFlex1.TextArray(i * MSFlex1.Cols) = Trim(result("操作员"))
MSFlex1.TextArray(i * MSFlex1.Cols + 1) = Trim(datel)
result.MoveNext
i = i + 1
Loop
End If
'''''''执行查询条件'''''''''
For i = 1 To MSFlex1.Rows - 2
sql = "select * from JTPRtable where 日期='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols + 1)) & "' and 操作员='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols)) & "'"
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
'Do While Not result.EOF()
MSFlex1.TextArray(i * MSFlex1.Cols + 2) = Trim(result("喷绘序列"))
MSFlex1.TextArray(i * MSFlex1.Cols + 3) = Trim(result("总面积"))
MSFlex1.TextArray(i * MSFlex1.Cols + 4) = Trim(result("幅宽"))
MSFlex1.TextArray(i * MSFlex1.Cols + 5) = Trim(result("原有长度"))
MSFlex1.TextArray(i * MSFlex1.Cols + 6) = Trim(result("实际用布长度"))
MSFlex1.TextArray(i * MSFlex1.Cols + 7) = Trim(result("剩余长度"))
' result.MoveNext
' i = i + 1
'Loop
End If
Next i
''''''''计算合计''''''
For i = 0 To 9
HJ(i) = 0
Next i
For i = 1 To MSFlex1.Rows - 2
HJ(0) = HJ(0) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 3))
HJ(1) = HJ(1) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 5))
HJ(2) = HJ(2) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 6))
HJ(3) = HJ(3) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 7))
Next i
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols) = "合计:"
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 3) = HJ(0)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 5) = HJ(1)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 6) = HJ(2)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 7) = HJ(3)
End If
''''''''查询条件3
If Trim(Combo1.Text) = "业务员" Then
MSFlex1.Clear
MSFlex1.Cols = 8
MSFlex1.FormatString = "^ 业务员 |^ 日 期 |^ 合同号 |^ 客户名称 |^ 总面积 |^应收款额|^已收款额|^未收款额 "
If Trim(Combo2.Text) <> "所有" Then
sql = "select distinct 业务员 from YWPRtable where 日期='" & Trim(datel) & "' and 业务员='" & Trim(Combo2.Text) & "'"
Else
sql = "select distinct 业务员 from YWPRtable where 日期='" & Trim(datel) & "'"
End If
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
result.MoveLast
MSFlex1.Rows = result.RowCount + 2
result.MoveFirst
End If
'''''显示业务员名称'''''''''''
If result.RowCount <> 0 Then
i = 1
Do While Not result.EOF()
MSFlex1.TextArray(i * MSFlex1.Cols) = Trim(result("业务员"))
MSFlex1.TextArray(i * MSFlex1.Cols + 1) = Trim(datel)
result.MoveNext
i = i + 1
Loop
End If
'''''''执行查询条件'''''''''
For i = 1 To MSFlex1.Rows - 2
sql = "select * from YWPRtable where 日期='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols + 1)) & "' and 业务员='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols)) & "'"
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
'Do While Not result.EOF()
MSFlex1.TextArray(i * MSFlex1.Cols + 2) = Trim(result("合同号"))
MSFlex1.TextArray(i * MSFlex1.Cols + 3) = Trim(result("客户名称"))
MSFlex1.TextArray(i * MSFlex1.Cols + 4) = Trim(result("总面积"))
MSFlex1.TextArray(i * MSFlex1.Cols + 5) = Trim(result("应收款额"))
MSFlex1.TextArray(i * MSFlex1.Cols + 6) = Trim(result("已收款额"))
MSFlex1.TextArray(i * MSFlex1.Cols + 7) = Trim(result("未收款额"))
' result.MoveNext
' i = i + 1
'Loop
End If
Next i
'''''''计算合计'''''''
For i = 0 To 9
HJ(i) = 0
Next i
For i = 1 To MSFlex1.Rows - 2
HJ(0) = HJ(0) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 5))
HJ(1) = HJ(1) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 6))
HJ(2) = HJ(2) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 7))
HJ(3) = HJ(3) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 8))
Next i
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols) = "合计:"
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 5) = HJ(0)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 6) = HJ(1)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 7) = HJ(2)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 8) = HJ(3)
End If
''''''''查询条件4
If Trim(Combo1.Text) = "签单人" Then
MSFlex1.Clear
MSFlex1.Cols = 8
MSFlex1.FormatString = "^ 签单人 |^ 日 期 |^ 合同号 |^ 客户名称 |^ 总面积 |^应收款额|^已收款额|^未收款额 "
If Trim(Combo2.Text) <> "所有" Then
sql = "select distinct 签单人 from YWPRtable where 日期='" & Trim(datel) & "' and 签单人='" & Trim(Combo2.Text) & "'"
Else
sql = "select distinct 签单人 from YWPRtable where 日期='" & Trim(datel) & "'"
End If
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
result.MoveLast
MSFlex1.Rows = result.RowCount + 2
result.MoveFirst
End If
'''''显示签单人名称'''''''''''
If result.RowCount <> 0 Then
i = 1
Do While Not result.EOF()
MSFlex1.TextArray(i * MSFlex1.Cols) = Trim(result("签单人"))
MSFlex1.TextArray(i * MSFlex1.Cols + 1) = Trim(datel)
result.MoveNext
i = i + 1
Loop
End If
'''''''执行查询条件'''''''''
For i = 1 To MSFlex1.Rows - 2
sql = "select * from YWPRtable where 日期='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols + 1)) & "' and 签单人='" & Trim(MSFlex1.TextArray(i * MSFlex1.Cols)) & "'"
Set result = cn.OpenResultset(sql, rdOpenDynamic, rdConcurRowVer)
If result.RowCount <> 0 Then
'Do While Not result.EOF()
MSFlex1.TextArray(i * MSFlex1.Cols + 2) = Trim(result("合同号"))
MSFlex1.TextArray(i * MSFlex1.Cols + 3) = Trim(result("客户名称"))
MSFlex1.TextArray(i * MSFlex1.Cols + 4) = Trim(result("总面积"))
MSFlex1.TextArray(i * MSFlex1.Cols + 5) = Trim(result("应收款额"))
MSFlex1.TextArray(i * MSFlex1.Cols + 6) = Trim(result("已收款额"))
MSFlex1.TextArray(i * MSFlex1.Cols + 7) = Trim(result("未收款额"))
' result.MoveNext
' i = i + 1
'Loop
End If
Next i
'''''''计算合计'''''''
For i = 0 To 9
HJ(i) = 0
Next i
For i = 1 To MSFlex1.Rows - 2
HJ(0) = HJ(0) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 5))
HJ(1) = HJ(1) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 6))
HJ(2) = HJ(2) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 7))
HJ(3) = HJ(3) + Val(MSFlex1.TextArray(i * MSFlex1.Cols + 8))
Next i
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols) = "合计:"
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 5) = HJ(0)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 6) = HJ(1)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 7) = HJ(2)
MSFlex1.TextArray((MSFlex1.Rows - 1) * MSFlex1.Cols + 8) = HJ(3)
End If
End If
errmsg:
If Err.Number <> 0 Then
If Mask1.Text <> "" And Trim(Combo1.Text) <> "" And Trim(Combo2.Text) <> "" Then
If Trim(Combo1.Text) = "设计员" Then
MSFlex1.Cols = 5
MSFlex1.FormatString = "^ 设计员 |^ 日 期 |^ 合同数 |^ 画面数 |^ 平米数 "
MSFlex1.Rows = 6
i = 1
MSFlex1.TextArray(i * MSFlex1.Cols) = "李明"
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -