cooperatestat.frm

即时通讯

End Sub
'**************************************
'*    功 能 描 述 :查找生产外协单
'*    输 入 参 数 :无
'*    输 出 能 数 :无
'**************************************
Private Function QueryCooperate()
    Dim ret As Boolean
    Dim currentrow As Integer                 ' 当前插入的行

    QueryFom.m_currentQuery = QUERY_COOPERATE
    QueryFom.show vbModal
    
    ret = m_dao.FindCooperateById(m_recordset, MainForm.g_application.m_queryResultId)
    
    HFG_cooperateList.clear
    HFG_cooperateList.Rows = 2

    RefreshListTitle
    If ret Then
        With m_recordset
            currentrow = 1
            Do While Not .EOF
                If currentrow > HFG_cooperateList.Rows - 1 Then
                    HFG_cooperateList.AddItem ""
                End If
                
                '[>>自定义填充内容
                HFG_cooperateList.TextMatrix(currentrow, 0) = Trim(.Fields("psName"))
                HFG_cooperateList.TextMatrix(currentrow, 1) = Trim(.Fields("bomName"))
                HFG_cooperateList.TextMatrix(currentrow, 2) = Trim(.Fields("Supplier"))
                HFG_cooperateList.TextMatrix(currentrow, 3) = Trim(.Fields("quantity"))
                HFG_cooperateList.TextMatrix(currentrow, 4) = Trim(.Fields("quote"))
                HFG_cooperateList.TextMatrix(currentrow, 5) = Trim(.Fields("totalMoney"))
                HFG_cooperateList.TextMatrix(currentrow, 6) = Trim(.Fields("planBeginDate"))
                HFG_cooperateList.TextMatrix(currentrow, 7) = Trim(.Fields("planFinishDate"))
                HFG_cooperateList.TextMatrix(currentrow, 8) = Trim(.Fields("finishDate"))
                HFG_cooperateList.TextMatrix(currentrow, 9) = Trim(.Fields("employeeName"))
                HFG_cooperateList.TextMatrix(currentrow, 10) = Trim(.Fields("state"))
                HFG_cooperateList.TextMatrix(currentrow, 11) = Trim(.Fields("cooperateId"))
                HFG_cooperateList.TextMatrix(currentrow, 12) = Trim(.Fields("finishNum"))
                '<<]
                '设置数据行高度(Fixed)
                HFG_cooperateList.RowHeight(currentrow) = 200
         
                '动态集指针加1,同时将计数器加1(Fixed)
                .MoveNext
                currentrow = currentrow + 1
            Loop
        End With
    End If
    m_recordset.Close
End Function

Private Sub T_setToNum_KeyPress(KeyAscii As Integer)
    InputFieldLimit T_setToNum, 5, KeyAscii
End Sub
'**************************************
'*    功 能 描 述 :生产外协单统计处理
'*    输 入 参 数 :无
'*    输 出 能 数 :无
'**************************************
Public Function CooperateStat()
    Dim ret As Boolean
    Dim currentrow As Integer
    
    Dim suppliername As String      ' 查询到的计录是供应商的名子
    Dim sumFee As Double            ' 汇总的加工费
    Dim sumCompensate As Double     ' 汇总的赔偿费
    
    Dim lossNum As Double            ' 汇总的加工费
    Dim counterclaim As Double       ' 索赔数量
    Dim backNum As Double            ' 汇总的赔偿费
    Dim finishNum As Double          ' 完成数量
    ' 构造查询语句
    ConstructSentence
    
    HFG_cooperateList.clear
    HFG_cooperateList.Rows = 2

    RefreshListTitle
    
    ' 执行查询操作
    If Trim(m_sql) <> "" Then
        ret = m_queryDAO.ExecuteQuery(m_recordset, m_sql)
        ' Set MSHFlexGrid1.DataSource = m_recordset
        If ret Then
            With m_recordset
                currentrow = 1
                suppliername = ""
                sumFee = 0
                sumCompensate = 0
                Do While Not .EOF
                    If currentrow > HFG_cooperateList.Rows - 1 Then
                        HFG_cooperateList.AddItem ""
                    End If
                    
                    If Trim(.Fields("lossNum") & " ") = "" Then
                        lossNum = 0
                    Else
                        lossNum = CDbl(.Fields("lossNum"))
                    End If
                    
                    If Trim(.Fields("counterclaim") & " ") = "" Then
                        counterclaim = 0
                    Else
                        counterclaim = CDbl(.Fields("counterclaim"))
                    End If
                    
                    If Trim(.Fields("backNum") & " ") = "" Then
                        backNum = 0
                    Else
                        backNum = CDbl(.Fields("backNum"))
                    End If
                    
                    If Trim(.Fields("finishNum") & " ") = "" Then
                        finishNum = 0
                    Else
                        finishNum = CDbl(.Fields("finishNum"))
                    End If
                    
                    If suppliername = "" Then      ' 显示第一行明细行
                        '[>>自定义填充内容
                        HFG_cooperateList.TextMatrix(currentrow, 1) = Trim(.Fields("Supplier") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 2) = Trim(.Fields("bomName") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 3) = Trim(.Fields("processName") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 4) = Trim(.Fields("quantity") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 5) = lossNum
                        HFG_cooperateList.TextMatrix(currentrow, 6) = backNum
                        HFG_cooperateList.TextMatrix(currentrow, 7) = finishNum
                        HFG_cooperateList.TextMatrix(currentrow, 8) = CDbl(.Fields("fee") & " ") * finishNum
                        HFG_cooperateList.TextMatrix(currentrow, 9) = CDbl(.Fields("bomCost")) * counterclaim
                        '<<]
                        
                        suppliername = Trim(.Fields("Supplier") & " ")
                        sumFee = CDbl(.Fields("fee")) * finishNum
                        sumCompensate = CDbl(.Fields("bomCost")) * counterclaim
                    ElseIf suppliername = Trim(.Fields("Supplier")) Then                     ' 显示非第一行明细行
                        '[>>自定义填充内容
                        HFG_cooperateList.TextMatrix(currentrow, 2) = Trim(.Fields("bomName") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 3) = Trim(.Fields("processName") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 4) = Trim(.Fields("quantity") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 5) = lossNum
                        HFG_cooperateList.TextMatrix(currentrow, 6) = backNum
                        HFG_cooperateList.TextMatrix(currentrow, 7) = finishNum
                        HFG_cooperateList.TextMatrix(currentrow, 8) = CDbl(.Fields("fee")) * finishNum
                        HFG_cooperateList.TextMatrix(currentrow, 9) = CDbl(.Fields("bomCost")) * counterclaim
                        '<<]
                        
                        suppliername = Trim(.Fields("Supplier") & " ")
                        sumFee = sumFee + CDbl(.Fields("fee")) * finishNum
                        sumCompensate = sumCompensate + CDbl(.Fields("bomCost")) * counterclaim
                    ElseIf suppliername <> Trim(.Fields("Supplier")) And currentrow <> 1 Then ' 显示汇总 并把下一行显示出来
                        '[>>自定义填充内容
                        HFG_cooperateList.TextMatrix(currentrow, 1) = "汇  总"
                        HFG_cooperateList.TextMatrix(currentrow, 8) = sumFee
                        HFG_cooperateList.TextMatrix(currentrow, 9) = sumCompensate
                        SetRowColor HFG_cooperateList, currentrow, &HFFFF00
                        '<<]
                    
                        suppliername = ""
                        sumFee = 0
                        sumCompensate = 0
                       
                        ' 显示下一行的数据
                        currentrow = currentrow + 1
                        If currentrow > HFG_cooperateList.Rows - 1 Then
                            HFG_cooperateList.AddItem ""
                        End If
                        '[>>自定义填充内容
                        HFG_cooperateList.TextMatrix(currentrow, 1) = Trim(.Fields("Supplier") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 2) = Trim(.Fields("bomName") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 3) = Trim(.Fields("processName") & " ")
                        HFG_cooperateList.TextMatrix(currentrow, 4) = Trim(.Fields("quantity"))
                        HFG_cooperateList.TextMatrix(currentrow, 5) = lossNum
                        HFG_cooperateList.TextMatrix(currentrow, 6) = backNum
                        HFG_cooperateList.TextMatrix(currentrow, 7) = finishNum
                        HFG_cooperateList.TextMatrix(currentrow, 8) = CDbl(.Fields("fee")) * finishNum
                        HFG_cooperateList.TextMatrix(currentrow, 9) = CDbl(.Fields("bomCost")) * counterclaim
                        '<<]
                        
                        suppliername = Trim(.Fields("Supplier") & " ")
                        sumFee = CDbl(.Fields("fee")) * finishNum
                        sumCompensate = CDbl(.Fields("bomCost")) * counterclaim
                    End If

                    '设置数据行高度(Fixed)
                    HFG_cooperateList.RowHeight(currentrow) = 200
             
                    '动态集指针加1,同时将计数器加1(Fixed)
                    .MoveNext
                    currentrow = currentrow + 1
                Loop
                
                ' 显示最后一行汇总数据
                If currentrow > HFG_cooperateList.Rows - 1 Then
                    HFG_cooperateList.AddItem ""
                End If
                '[>>自定义填充内容
                HFG_cooperateList.TextMatrix(currentrow, 1) = "汇  总"
                HFG_cooperateList.TextMatrix(currentrow, 8) = sumFee
                HFG_cooperateList.TextMatrix(currentrow, 9) = sumCompensate
                SetRowColor HFG_cooperateList, currentrow, &HFFFF00
                '<<]
            
                suppliername = ""
                sumFee = 0
                sumCompensate = 0
            End With
        End If
    
        m_recordset.Close
    End If
End Function
'**************************************
'*    功 能 描 述 :生产外协单统计处理
'*    输 入 参 数 :无
'*    输 出 能 数 :无
'**************************************
Public Function ConstructSentence()
'查询语句
'SELECT supplierId, SUM(finishNum) AS finishNum, SUM(backNum) AS backNum,
'      SUM(lossNum) AS lossNum, MAX(cooperateId) AS cooperateId, MAX(pocessNo)
'      AS pocessNo, MAX(Supplier) AS Supplier, MAX(finishDate) AS finishDate,
'      name As bomName, MAX(fee) As fee, MAX(bomCost) As bomCost, MAX(processName)
'      AS processName, SUM(quantity) AS quantity, MAX(psName) AS psName, MAX(billNo)
'      AS billNo, MAX(psId) AS psId
'From VIEW_COOPERATE_STAT
'GROUP BY name, supplierId
    
Dim condition As String

    If Trim(T_beginDate.text) <> "" Then
        condition = "finishdate >=  '" + Trim(T_beginDate.text) + "'  and  "
    End If

    If Trim(T_finishDate.text) <> "" Then
        condition = condition + "finishdate <  '" + Trim(T_finishDate.text) + "'  and  "
    End If

    condition = condition + "psId like '%' + '" + T_productScheme.Tag + "'  "

m_sql = "SELECT supplierId, SUM(finishNum) AS finishNum,SUM(counterclaimNum) AS counterclaim, SUM(backNum) AS backNum, " + _
      "SUM(lossNum) AS lossNum, MAX(cooperateId) AS cooperateId, MAX(pocessNo) " + _
      "AS pocessNo, MAX(Supplier) AS Supplier, MAX(finishDate) AS finishDate, " + _
      "name As bomName, MAX(fee) As fee, MAX(bomCost) As bomCost, MAX(processName) " + _
      "AS processName, SUM(quantity) AS quantity, MAX(psName) AS psName, MAX(billNo) " + _
      "AS billNo, MAX(psId) AS psId " + _
"From VIEW_COOPERATE_STAT " + _
"where " + condition + _
"GROUP BY name, supplierId "



'    Dim s As String
'    s = 1000
'
'    s = "SELECT supplierId, SUM(finishNum) AS finishNum, SUM(backNum) AS backNum, "
'    s = s + "SUM(lossNum) AS lossNum, MAX(cooperateId) AS cooperateId, MAX(pocessNo) "
'    s = s + "AS pocessNo, MAX(Supplier) AS Supplier, MAX(finishDate) AS finishDate, "
'    s = s + "name As bomName, MAX(fee) As fee, MAX(bomCost) As bomCost, MAX(processName) "
'    s = s + "AS processName, SUM(quantity) AS quantity, MAX(psName) AS psName, MAX(billNo) "
'    s = s + "AS billNo, MAX(psId) AS psId  "
'    s = s + "From VIEW_COOPERATE_STAT    "
    
      
'    m_sql = m_sql + "WHERE    "
'
'    If Trim(T_beginDate.text) <> "" Then
'        m_sql = m_sql + "'" + Trim(T_beginDate.text) + "' " + "< finishdate  and  "
'    End If
'
'    If Trim(T_finishDate.text) <> "" Then
'        m_sql = m_sql + "'" + Trim(T_finishDate.text) + "' " + "> finishdate  and  "
'    End If
'
'    m_sql = m_sql + "psId lik '%' + '" + T_productScheme.Tag + "'  "

'    m_sql = m_sql + "group by name, supplierId "

'    m_sql = m_sql + "GROUP BY name, supplierId"
End Function