'-------------------------------------------------------------------
    '判斷
    '3 判斷當前金額有沒有超過預算金額
    '-------------------------------------------------------------------
    Dim lAmt As Currency  '借款金額
    Dim lbudget As Currency '預算金額
    Dim lUseAmt As Currency '已用預處金額
    Dim lUseAmt1 As Currency
    Dim lUseAmt2 As Currency
    Dim lUseAmt3 As Currency
    Dim lbudgetID As Long  '預算方案ＩＤ
    Dim lDepId As Long  '部門ＩＤ
    Dim lDetailId As Long
    Dim lAcctId As Long '會計科目
    Dim lBudgetAccId As Long  '預算科目
    Dim sAccIdDepId As String '預算科目對應(yīng)會計科目和部門　和字符串
    Dim lBudgetCon As Long '預算控制　０本期預算　１累計預算 ２本年預算 ３方案預算  ４季度預算 ５半年預算
    Dim sConBound As String '預算控制范圍的字符串
    Dim sConBound1 As String
    Dim rs As New ADODB.Recordset
    Dim sSql As String
    Dim iYear As Integer
    Dim iPeriod As Integer
    Dim i As Integer, j As Integer
    Dim sFid_src As String '源單編號
    Dim sErr As String
    
    
    On Error GoTo ERR
    With m_BillInterface
         '取對應(yīng)預算方案
        lbudgetID = lBudgetProjectID
        
        '循環(huán)分錄中的所有列
        i = 1
beg:        While i <= .Data("Page2").Size
                '對應(yīng)部門，
                lDepId = .Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FDivideDep"))("FFLD")
                
                '取借款金額的值
                If iType = 1 Then
                    lAmt = 0
                Else
                    lAmt = .Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FloanAmt"))("FFLD")
                End If
    
                '會計科目--報銷科目＝借款科目（如果有借款存在）
                lAcctId = .Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FWipeItem"))("FFLD")

    
                '2根據(jù)財務(wù)科目內(nèi)碼取預算科目的內(nèi)碼 lBudgetAccId
                sSql = "select FBudgetItem from t_EP_ER_AccToMgAccEntry1 t1  " & _
                                " inner join t_EP_ER_AccToMgAcc t2 on t1.fid=t2.fid " & _
                                " where FAcctID = '" & lAcctId & "' and FDepID  ='" & lDepId & "'"
                 Set rs = .K3Lib.GetData(sSql)
                If rs.State = adStateOpen And rs.RecordCount > 0 Then
                    lBudgetAccId = rs("FBudgetItem")
                Else
                    sErr = sErr & "您錄入第" & CStr(i) & "列中的　“會計科目”：" & _
                       CStr(.Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FWipeItem"))("FFND")) & "-" & CStr(.Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FWipeItem"))("FDSP")) & _
                        " 和 “部門”" & .Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FDivideDep"))("FFND") & "-" & .Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FDivideDep"))("FDSP") & _
                        "　沒有對應(yīng)預算科目！"
                    compareNum = False
                    sRet = sErr
                    Exit Function
                End If
            
                If rs.State = adStateOpen Then rs.Close
                            
                '取預算范圍，是月還是季還是年,沒有的話，默認為季度預算
                Set rs = .K3Lib.GetData("select FValue from T_SystemProfile where FCategory='mg' and FKey='BudGet_Con'")
                If rs.State = adStateOpen And rs.RecordCount > 0 Then
                    lBudgetCon = rs("FValue")
                Else
                    lBudgetCon = 4
                End If
                
                If rs.State = adStateOpen Then rs.Close
                       
                '3 根據(jù)預算類型進行條件拼寫
                iYear = Left(.K3Lib.GetData("select  convert(varchar(19),getdate(),21)  as date")("Date"), 4)
                iPeriod = Mid(.K3Lib.GetData("select  convert(varchar(19),getdate(),21) as date")("Date"), 6, 2)
                Select Case lBudgetCon '０本期預算　１累計預算 ２本年預算 ３方案預算  ４季度預算 ５半年預算
                    Case 0
                        sConBound = "FYear= " & iYear & " and FPeriod <=" & iPeriod
                        sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)='" & CStr(iYear) & "' and substring(convert(varchar(7),FBillDate,21),6,2)<='" & CStr(iPeriod) & "'"
                    Case 1
                    Case 2
                        sConBound = "FYear= " & iYear
                        sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)= '" & CStr(iYear) & "'"
                    Case 3
                    Case 4
                        If iPeriod <= 3 Then
                            sConBound = "FYear= " & iYear & " and FPeriod<=3 " ' in (1,2,3)"
                            sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)='" & CStr(iYear) & "' and substring(convert(varchar(7),FBillDate,21),6,2)<='03'"
                        ElseIf iPeriod >= 4 And iPeriod <= 6 Then
                            sConBound = "FYear= " & iYear & " and FPeriod <=6 " 'in (4,5,6)"
                            sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)='" & CStr(iYear) & "' and substring(convert(varchar(7),FBillDate,21),6,2)<='06'"
                        ElseIf iPeriod >= 7 And iPeriod <= 9 Then
                            sConBound = "FYear= " & iYear & " and FPeriod <=9 " 'in (7,8,9)"
                            sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)='" & CStr(iYear) & "' and substring(convert(varchar(7),FBillDate,21),6,2)<='09'"
                        ElseIf iPeriod >= 10 And iPeriod <= 12 Then
                            sConBound = "FYear= " & iYear & " and FPeriod <=12 " 'in (10,11,12)"
                            sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)='" & CStr(iYear) & "' and substring(convert(varchar(7),FBillDate,21),6,2)<='12'"
                        End If
                        
                    Case 5
                        If iPeriod < 7 Then
                            sConBound = "FYear= " & iYear & " and FPeriod <=6" 'in (1,2,3,4,5,6)"
                            sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)='" & CStr(iYear) & "' and substring(convert(varchar(7),FBillDate,21),6,2)<='06'"
                        ElseIf iPeriod >= 7 Then
                            sConBound = "FYear= " & iYear & " and FPeriod <=12" ' in (7,8,9,10,11,12)"
                            sConBound1 = " substring(convert(varchar(7),FBillDate,21),1,4)='" & CStr(iYear) & "' and substring(convert(varchar(7),FBillDate,21),6,2)<='12'"
                        End If
                End Select
                '本年的到目前為止的預算
                sSql = "SELECT sum(case when Acct.FDC=1 then isnull(Budd.FDebitMoney,0) else IsNull(Budd.FCreditMoney,0) end) as FBudMoney" & _
                        " FROM t_MgBudGetDetail As BudD" & _
                        " Inner join t_MgBudGet as Bud on Bud.FBudGetID= Budd.FBudgetID" & _
                        " Inner join t_MgBudgetSet As BudSet On Bud.FProjectID=BudSet.FProjectID" & _
                        " Inner Join t_MgAcct as Acct ON  Acct.FMgAcctID=Bud.FMgAcctID" & _
                        " Where BudSet.FExec = 1 And Bud.FItemID = 0 And Acct.FDelete = 0 And Bud.FCyID = 1" & _
                        " And Acct.FMgAcctID = '" & lBudgetAccId & "'"
                sSql = sSql & " and " & sConBound
                Set rs = .K3Lib.GetData(sSql)
                
                If rs.State = adStateOpen And rs.RecordCount > 0 Then
                    lbudget = CNulls(rs("FBudMoney"), 0)
                Else
                    lbudget = 0
                End If
                If rs.State = adStateOpen Then rs.Close
                '計算的到目前為止的本年調(diào)整金額
                sSql = "SELECT isnull(sum(case when Acct.FDC=1 then isnull(BudModD.FDebitMoney,0) else IsNull(BudModD.FCreditMoney,0) end),0)  as FBudMoney " & _
                        " FROM t_MgBudModifyDetail As BudModD" & _
                        " Inner join t_MgBudGetModify as BudMod on BudMod.FModifyid= BudModD.FModifyid" & _
                        " inner join T_MgBudGet as Bud on Bud.FBudGetID =BudMod.FBudGetID" & _
                        " Inner join t_MgBudgetSet As BudSet On Bud.FProjectID=BudSet.FProjectID" & _
                        " Inner Join t_MgAcct as Acct ON  Acct.FMgAcctID=Bud.FMgAcctID" & _
                        " Where BudSet.FExec = 1 And Bud.FItemID = 0 And Acct.FDelete = 0 And Bud.FCyID = 1" & _
                        " And  Acct.FMgAcctID = '" & lBudgetAccId & "'"
                sSql = sSql & " and " & sConBound
                Set rs = .K3Lib.GetData(sSql)
                If rs.State = adStateOpen And rs.RecordCount > 0 Then
                    lbudget = lbudget + CCur(CNulls(rs("FBudMoney"), 0))
                Else
                    lbudget = 0
                End If
                If rs.State = adStateOpen Then rs.Close
    
                '計算預算科目的已經(jīng)用金額－本年
                '取對應(yīng)預算科目所對應(yīng)的全部 --會計科目
                sSql = "select * from t_EP_ER_AccToMgAccEntry1 t1  " & _
                                " inner join t_EP_ER_AccToMgAcc t2 on t1.fid=t2.fid " & _
                                " where FBudgetItem ='" & lBudgetAccId & "'"
                 Set rs = .K3Lib.GetData(sSql)
                If rs.State = adStateOpen And rs.RecordCount > 0 Then
                    j = 1
                    sAccIdDepId = ""
                    rs.MoveFirst
                    While j <= rs.RecordCount
                        If sAccIdDepId = "" Then
                            sAccIdDepId = sAccIdDepId & " (FReqDept= '" & CStr(rs("FDepID")) & "' and FLoanItem='" & CStr(rs("FAcctID")) & "')"
                        Else
                            sAccIdDepId = sAccIdDepId & " or " & " (FReqDept= '" & CStr(rs("FDepID")) & "' and FLoanItem='" & CStr(rs("FAcctID")) & "')"
                        End If
                        j = j + 1
                        rs.MoveNext
                    Wend
                Else
            
                End If
                '1. 借款申請的金額，只取沒有與報銷單勾銷的
                sFid_src = ""
                If Trim(.Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FID_SRC"))("FFLD")) <> "" Then
                    sFid_src = " AND t1.fid= " & .Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FID_SRC"))("FFLD")
                End If
                 sSql = " select isnull(sum(FCtlAmt),0)   as FCtlAmt from t_EP_ER_Loan t1 " & _
                        " LEFT JOIN T_ITEM T2 ON T2.FItemClassid=2 and t1.FReqDept=t2.fitemid  " & _
                        " where Fclose=0  " & _
                        " and  " & sConBound1 & sFid_src
                If sAccIdDepId <> "" Then sSql = sSql & " AND (" & sAccIdDepId & ")"
                Set rs = .K3Lib.GetData(sSql)
                If rs.State = adStateOpen And rs.RecordCount > 0 Then
                    lUseAmt1 = CNulls(rs("FCtlAmt"), 0)
                Else
                    lUseAmt1 = 0
                End If
                If rs.State = adStateOpen Then rs.Close
                '2.　報銷的金額
                sSql = "select ISNULL(sum(t1.FWipeAmt),0) as FWipeAmt from t_BOSWipeOffEntry1 t1 " & _
                        " INNER JOIN t_BOSWipeOff t2 on t1.Fid=t2.Fid " & _
                        " AND " & sConBound1 & " AND T2.FID <> " & .CurBillID
                If sAccIdDepId <> "" Then
                    sAccIdDepId = Replace(sAccIdDepId, "FReqDept", "FDivideDep")
                    sAccIdDepId = Replace(sAccIdDepId, "FLoanItem", "FWipeItem")
                    sSql = sSql & " AND (" & sAccIdDepId & ")"
                End If
                Set rs = .K3Lib.GetData(sSql)
                If rs.State = adStateOpen And rs.RecordCount > 0 Then
                    lUseAmt2 = CNulls(rs("FWipeAmt"), 0)
                Else
                    lUseAmt2 = 0
                End If
                If rs.State = adStateOpen Then rs.Close
                
                '3計算本次的該項目的報銷金額　.Data("Page2").Item(i)(.DataSrv.TableInfo("map")("FWipeAmt"))("FFLD")
                lUseAmt3 = 0
                For j = 1 To .Data("page2").Size
                
                    If .Data("Page2").Item(j)(.DataSrv.TableInfo("map")("FWipeITEM"))("FFLD") = lAcctId _
                        And .Data("Page2").Item(j)(.DataSrv.TableInfo("map")("FDivideDep"))("FFLD") = lDepId Then
                        lUseAmt3 = lUseAmt3 + .Data("Page2").Item(j)(.DataSrv.TableInfo("map")("FWipeAmt"))("FFLD")
                    End If
                Next j
                '借款金額　加上報銷金額　加上本次報銷金額
                If iType <> 1 Then
                    lUseAmt = lUseAmt1 + lUseAmt2 + lUseAmt3
                Else
                    lUseAmt = lUseAmt1 + lUseAmt2
                End If
                '判斷余額是否大于借款金額
                If lbudget < lUseAmt Then
                    '計算余額
                    lBalanceAmt = lbudget - lUseAmt1 - lUseAmt2
                    sErr = sErr & "報銷項目： '" & .GetFieldValue("FWipeItem", i, Enu_ValueType_FFND) & "-" & .GetFieldValue("FWipeItem", i, Enu_ValueType_FDSP) & "',預算余額：" & lBalanceAmt & "，當前報銷金額：" & lUseAmt3 & "已經(jīng)超過預算金額！" & vbCrLf
                End If
                i = i + 1
       Wend
        If sErr <> "" Then
            compareNum = False
            sRet = sErr
        Else
            compareNum = True
        End If
       
    End With
    Set rs = Nothing
    Exit Function
ERR:
    Set rs = Nothing
    compareNum = False
    lBalanceAmt = 0
    sErr = ERR.Number & ERR.Description
    End Function