VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "CUnBook"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

Private m_dUnBook As Date

Private m_Wks     As Workspace
Private m_DB      As UfDatabase
Private m_Rs      As UfRecordset

Public Event FlashMessage(sMsg1 As String, sMsg2 As String)
Public Event DataValidated(sErrorMsg() As String)

Public Property Get UnBookDate() As Date
   UnBookDate = m_dUnBook
End Property

Public Property Let UnBookDate(ByVal vNewValue As Date)
   m_dUnBook = vNewValue
End Property

Public Sub Init(oWks As Workspace, oDB As UfDatabase, Optional vUnBook As Variant)
   Set m_Wks = oWks
   Set m_DB = oDB
   If Not IsMissing(vUnBook) Then m_dUnBook = CDate(vUnBook)
End Sub

Public Function Start() As Boolean
   Dim sqlExec As String
   Dim con As New ADODB.Connection
   Dim rec As New ADODB.Recordset
   
   On Error GoTo lblOut
   
   m_DB.BeginTrans
   
   RaiseEvent FlashMessage("貸款單", "")
   'sqlExec = "Update FD_Cred Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='41' or substring(transactions_id,1,2)='45')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("還款單", "")
   'sqlExec = "Update FD_Return Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='42' or substring(transactions_id,1,2)='46')"
   m_DB.Execute sqlExec, dbFailOnError
      
   RaiseEvent FlashMessage("還息單", "")
   'sqlExec = "Update FD_CreAcrRcp Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='52' or substring(transactions_id,1,2)='55')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("存款單", "")
   'sqlExec = "Update FD_Sav Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='21' or substring(transactions_id,1,2)='26' or substring(transactions_id,1,2)='31' or substring(transactions_id,1,2)='33')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("取款單", "")
   'sqlExec = "Update FD_Fetch Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='22' or substring(transactions_id,1,2)='27' or substring(transactions_id,1,2)='32' or substring(transactions_id,1,2)='34')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("內部拆借單", "")
   'sqlExec = "Update FD_UnwDeb Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='43')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("內部拆借還款單", "")
   'sqlExec = "Update FD_UnwRet Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='44')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("內部拆借還息單", "")
   'sqlExec = "Update FD_UnwAcrRcp Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='53')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("結算單", "")
   'sqlExec = "Update FD_SettAcc Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='23' or substring(transactions_id,1,2)='24' or substring(transactions_id,1,2)='25')"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("利息單", "")
'   sqlExec = "Delete From FD_CadAcr Where dTo >= '" & FormatDate(m_dUnBook) & "'"                       'cuidong 2002.03.13
   'sqlExec = "Delete From FD_CadAcr Where dTo >= '" & FormatDate(m_dUnBook) & "' And (Not iDanType = 1)" 'cuidong 2002.03.13
   sqlExec = "Delete From fd_transactions Where to_date >= '" & FormatDate(m_dUnBook) & "' And (Not vouchertype_flag = 1) and (substring(transactions_id,1,2)='51' or substring(transactions_id,1,2)='52' or substring(transactions_id,1,2)='53' or substring(transactions_id,1,2)='54' or substring(transactions_id,1,2)='55')"
   m_DB.Execute sqlExec, dbFailOnError
   
   '--------------------------------
   RaiseEvent FlashMessage("利息單", "")
   sqlExec = "Update FD_CadAcr Set cBookCode = Null Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   sqlExec = "Update fd_transactions Set book_name = Null Where bill_date >= '" & FormatDate(m_dUnBook) & "' and (substring(transactions_id,1,2)='51' or substring(transactions_id,1,2)='52' or substring(transactions_id,1,2)='53' or substring(transactions_id,1,2)='54' or substring(transactions_id,1,2)='55')"
   m_DB.Execute sqlExec, dbFailOnError
   '--------------------------------
   
   If TestYtCadAcrTable Then
      RaiseEvent FlashMessage("預提利息單", "")
      sqlExec = "Delete From FD_YtCadAcr Where dTo >= '" & FormatDate(m_dUnBook) & "'"
      m_DB.Execute sqlExec, dbFailOnError
   End If
   '--------------------------------
   
   RaiseEvent FlashMessage("賬戶余額及積數處理", "")
   sqlExec = "Delete From FD_AccSum Where dbill_date >= '" & FormatDate(m_dUnBook) & "'"
   m_DB.Execute sqlExec, dbFailOnError
   
   RaiseEvent FlashMessage("自動還款單", "")
   sqlExec = "Delete From fd_transactions Where loan_flag = 1 and to_date >= '" & FormatDate(m_dUnBook) & "'"
   m_DB.Execute sqlExec, dbFailOnError
   
   m_DB.CommitTrans
   Start = True
   
   Exit Function
   
lblOut:
   On Error Resume Next
   m_DB.Rollback
   Start = False
   
End Function

Private Function TestYtCadAcrTable() As Boolean
    On Error GoTo Err_Test
    TestYtCadAcrTable = False
    
    m_DB.OpenRecordset "Select * From FD_YtCadAcr Where 1 = 0"
    TestYtCadAcrTable = True
    
Err_Test:
End Function

Public Function ValidateData(dUnBook As Date) As Boolean
   Dim bRaise     As Boolean
   Dim sqlValid   As String
   Dim i          As Long
   Dim sMsg()     As String
   
   m_dUnBook = dUnBook
   sqlValid = "Select cCraID As cBill From FD_CreAcrRcp " & _
      "Where dbill_date > '" & FormatDate(m_dUnBook) & "' " & _
      "Union Select cUnaID From FD_UnwAcrRcp " & _
      "Where dbill_date > '" & FormatDate(m_dUnBook) & "' " & _
      "Union Select cCarID From FD_CadAcr " & _
      "Where dbill_date >= '" & FormatDate(m_dUnBook) & "' And cCarID In (Select cBus_id From FD_Vouch)"
   Set m_Rs = m_DB.OpenRecordset(sqlValid, dbOpenSnapshot, dbOpenForwardOnly)
   
   If Not m_Rs.EOF Then
      bRaise = True
      i = 1
   End If
   
   While Not m_Rs.EOF
      ReDim Preserve sMsg(i)
      sMsg(i) = m_Rs!cBill
      m_Rs.MoveNext
      i = i + 1
   Wend
   
   m_Rs.oClose
   Set m_Rs = Nothing
   
   If bRaise Then RaiseEvent DataValidated(sMsg)
   
   ValidateData = Not bRaise
End Function

Public Property Get BookedDate() As Variant
   
   Set m_Rs = m_DB.OpenRecordset("Select Max(dbill_date) As dMax From FD_AccSum", dbOpenSnapshot)
   If Not IsNull(m_Rs!dMax) Then BookedDate = m_Rs!dMax
   
   m_Rs.oClose
   Set m_Rs = Nothing
End Property