mdlzz.bas

用友u8财务源码,用visual basic开发

   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_UnwDeb WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_UnwRet WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_UnwRet WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_UnwAcrRcp WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_UnwAcrRcp WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mmoney else -mmoney end) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mmoney else -mmoney end) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_CadAcr WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_CadAcr WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_Sav WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_Fetch WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
Else
   sqlYe = "SELECT Sum(mmoney) AS Mye FROM FD_Cred WHERE cAccID='" & _
         strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_Return WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_CreAcrRcp WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_UnwDeb WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_UnwDeb WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_UnwRet WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_UnwRet WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_UnwAcrRcp WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_UnwAcrRcp WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mmoney else -mmoney end) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mmoney else -mmoney end) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_CadAcr WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_CadAcr WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mmoney) FROM FD_Sav WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mmoney) FROM FD_Fetch WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
End If

   Set rsYe = dbsZJ.OpenRecordset(sqlYe, dbOpenSnapshot)
   While Not rsYe.EOF
'      GetZhYe = GetZhYe + IIf(IsNull(rsYe!mYe), 0, rsYe!mYe)                        'cuidong DZD 2001.11.02
      GetZhYe = GetZhYe + IIf(IsNull(rsYe.Fields(0).Value), 0, rsYe.Fields(0).Value) 'cuidong DZD 2001.11.02
      rsYe.MoveNext
   Wend
   
   Set oOption = Nothing
End Function

'cuidong DZD 2001.11.02
'--------------------------------------------------------------------
'函数说明: 取出账户余额(BenWeiBi)
'参    数: strZhID         账户号
'          dDate           日期
'返回值  : 账户BenWeiBi余额
Public Function GetZhYe_F(strZhID As String, dDate As Date, Optional IncludeUnCheck As Boolean = False) As Double
   Dim sqlYe      As String
   Dim rsYe       As New UfRecordset
   Dim strDate    As String
   Dim strBdate   As String
   Dim oOption    As COption
   Dim strStartDate As String
   
   Set oOption = New COption
   oOption.Init dbsZJ
   strDate = FormatDate(dDate)
   strBdate = FormatDate(oOption.Option1)
   strStartDate = FormatDate(ZjAccInfo.zjStartdate)
If IncludeUnCheck Then
   sqlYe = "SELECT Sum(mMoney_F) AS Mye FROM FD_Cred WHERE cAccID='" & _
         strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_Return WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_CreAcrRcp WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_UnwDeb WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_UnwDeb WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_UnwRet WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_UnwRet WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_UnwAcrRcp WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_UnwAcrRcp WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mMoney_F else -mMoney_F end) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mMoney_F else -mMoney_F end) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_CadAcr WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_CadAcr WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_Sav WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_Fetch WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<='" & strDate & "' And dbill_date>='" & strStartDate & "' "
Else
   sqlYe = "SELECT Sum(mMoney_F) AS Mye FROM FD_Cred WHERE cAccID='" & _
         strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_Return WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_CreAcrRcp WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_UnwDeb WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_UnwDeb WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_UnwRet WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_UnwRet WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_UnwAcrRcp WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_UnwAcrRcp WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='15' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mMoney_F else -mMoney_F end) FROM FD_SettAcc WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(case when igp_flag=0 then mMoney_F else -mMoney_F end) FROM FD_SettAcc WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' AND Left(cSetID,2)='14' And dbill_date>='" & strStartDate & "' "
   
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_CadAcr WHERE " & _
         "cGAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_CadAcr WHERE " & _
         "cPAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' " & _
         "AND iDanType=0 And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(mMoney_F) FROM FD_Sav WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
   sqlYe = sqlYe & "UNION ALL SELECT Sum(-mMoney_F) FROM FD_Fetch WHERE " & _
         "cAccID='" & strZhID & "' AND dbill_date<'" & strDate & "' And dbill_date>='" & strStartDate & "' "
End If
   Set rsYe = dbsZJ.OpenRecordset(sqlYe, dbOpenSnapshot)
   While Not rsYe.EOF
      GetZhYe_F = GetZhYe_F + IIf(IsNull(rsYe.Fields(0).Value), 0, rsYe.Fields(0).Value)
      rsYe.MoveNext
   Wend
   
   Set oOption = Nothing
End Function