VERSION 5.00
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "comdlg32.ocx"
Begin VB.UserControl ONSV_GSVManager 
   ClientHeight    =   7605
   ClientLeft      =   0
   ClientTop       =   0
   ClientWidth     =   11880
   ScaleHeight     =   7605
   ScaleWidth      =   11880
   Begin VB.Frame frm_detail 
      Caption         =   "Detail"
      Height          =   1275
      Left            =   180
      TabIndex        =   7
      Top             =   6195
      Width           =   10605
      Begin VB.TextBox txt_wool 
         Height          =   285
         Left            =   6645
         TabIndex        =   20
         Tag             =   "Month_Value1"
         Top             =   765
         Width           =   810
      End
      Begin VB.TextBox txt_grid 
         Height          =   285
         Left            =   4650
         TabIndex        =   18
         Tag             =   "Month_Value1"
         Top             =   765
         Width           =   810
      End
      Begin VB.TextBox txt_metal 
         Height          =   285
         Left            =   2880
         TabIndex        =   16
         Tag             =   "Month_Value1"
         Top             =   765
         Width           =   810
      End
      Begin VB.TextBox txt_mineral 
         Height          =   285
         Left            =   1185
         TabIndex        =   14
         Tag             =   "Month_Value1"
         Top             =   735
         Width           =   810
      End
      Begin Project1.ArmCombobox cbo_BMK_Code 
         Height          =   345
         Left            =   7500
         TabIndex        =   8
         Tag             =   "BMKCODE"
         Top             =   270
         Width           =   2775
         _ExtentX        =   4895
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_month 
         Height          =   345
         Left            =   3450
         TabIndex        =   9
         Tag             =   "MONTH"
         Top             =   270
         Width           =   2325
         _ExtentX        =   4101
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_year 
         Height          =   345
         Left            =   930
         TabIndex        =   10
         Tag             =   "YEAR"
         Top             =   270
         Width           =   1125
         _ExtentX        =   1984
         _ExtentY        =   609
      End
      Begin VB.Label lbl_labels 
         Alignment       =   1  'Right Justify
         Caption         =   "Wool"
         Height          =   270
         Index           =   8
         Left            =   5625
         TabIndex        =   21
         Top             =   780
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   1  'Right Justify
         Caption         =   "Grid"
         Height          =   270
         Index           =   7
         Left            =   3870
         TabIndex        =   19
         Top             =   780
         Width           =   660
      End
      Begin VB.Label lbl_labels 
         Alignment       =   1  'Right Justify
         Caption         =   "Metal"
         Height          =   270
         Index           =   6
         Left            =   2070
         TabIndex        =   17
         Top             =   765
         Width           =   660
      End
      Begin VB.Label lbl_labels 
         Alignment       =   1  'Right Justify
         Caption         =   "Mineral/Soft"
         Height          =   270
         Index           =   5
         Left            =   150
         TabIndex        =   15
         Top             =   795
         Width           =   990
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Market"
         Height          =   240
         Index           =   3
         Left            =   6540
         TabIndex        =   13
         Top             =   375
         Width           =   840
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Month"
         Height          =   240
         Index           =   4
         Left            =   2505
         TabIndex        =   12
         Top             =   375
         Width           =   885
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Year"
         Height          =   240
         Index           =   2
         Left            =   180
         TabIndex        =   11
         Top             =   375
         Width           =   735
      End
   End
   Begin VB.Frame frm_filters 
      Caption         =   "Filters"
      Height          =   675
      Left            =   225
      TabIndex        =   1
      Top             =   840
      Width           =   10935
      Begin Project1.ArmCombobox cbo_fYear 
         Height          =   345
         Left            =   1380
         TabIndex        =   2
         Top             =   240
         Width           =   1530
         _ExtentX        =   2699
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_fMarket 
         Height          =   345
         Left            =   4545
         TabIndex        =   4
         Top             =   195
         Width           =   3600
         _ExtentX        =   6350
         _ExtentY        =   609
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Market"
         Height          =   315
         Index           =   1
         Left            =   3330
         TabIndex        =   5
         Top             =   225
         Width           =   1035
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Year"
         Height          =   315
         Index           =   0
         Left            =   165
         TabIndex        =   3
         Top             =   270
         Width           =   1035
      End
   End
   Begin Project1.ToolbarControl tlb_main 
      Height          =   690
      Left            =   0
      TabIndex        =   0
      Top             =   0
      Width           =   10560
      _ExtentX        =   18627
      _ExtentY        =   1217
   End
   Begin Project1.ArmGrid grd_lst 
      Height          =   4020
      Left            =   300
      TabIndex        =   6
      Top             =   1695
      Width           =   10515
      _ExtentX        =   18547
      _ExtentY        =   7091
   End
   Begin MSComDlg.CommonDialog dlg_fileopen 
      Left            =   11325
      Top             =   7080
      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
End
Attribute VB_Name = "ONSV_GSVManager"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

Private Const SCREEN_NAME As String = "ONSVGSVManager"

Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwnd As Long) As Long

Private Const SEP = "®©"
Private Const SEP1 As String = "®"
Private Const SEP2 As String = "©"
Private Const CL_COLOR_ENABLED As Long = &H80000005
Private Const CL_COLOR_DISABLED As Long = &H8000000F


Private Const C_TOOLBARFACE_ITEM_LST As String = "0"
Private Const C_TOOLBARFACE_ITEM_MTNC As String = "1"
Private Const C_TOOLBARFACE_ITEM_DELETE As String = "2"


Private mb_EventRunning As Boolean
Private mb_Initialized As Boolean
Private mo_Db As ArmDb
Private ms_Language_Code As String
Dim mu_ActiveMode As ArmScreenMode
Private ml_U_Code As Long

Const C_BMK_CODE_COL As Long = 3
Const C_DATA_YEAR_COL As Long = 2
Const C_DATA_MONTH_COL As Long = 3
Const C_VALUE_MS_COL As Long = 5               ' Start of values section

Public Event quit()
Private Enum ArmScreenMode
  smMain
  smAdd
  smUpdate
  smDelete
End Enum




Public Property Get Initialized() As Boolean
    Initialized = mb_Initialized
End Property


Public Property Let Visible(ByVal aVisible As Boolean)
    UserControl.Extender.Visible = aVisible
End Property
Public Property Get Visible() As Boolean
    Visible = UserControl.Extender.Visible
End Property

Public Sub Move(ByVal aLeft As Single, ByVal aTop As Single, ByVal aWidth As Single, ByVal aHeight As Single)
    Call UserControl.Extender.Move(aLeft, aTop, aWidth, aHeight - 2220)
    Call Resize
End Sub

Property Let Language_Code(AString As String)
On Error GoTo ErrHandler

  ms_Language_Code = AString
  Exit Property
ErrHandler:
  Call ErrorMessage("Language_Code.Let")
End Property

Property Let U_Code(ByVal al_U_Code As Long)
On Error GoTo ErrHandler

  ml_U_Code = al_U_Code
  Exit Property
ErrHandler:
  Call ErrorMessage("U_Code.Let")
End Property

Public Property Set ArmDb(ByRef lo_Db As Object)
On Error GoTo ErrHandler
  
  Set mo_Db = lo_Db
  Exit Property
ErrHandler:
  Call ErrorHandler("ArmDb.Set")
End Property


Public Sub Resize()
    Call InitCtrlSize
End Sub

Private Sub InitCtrlSize()
On Error GoTo ErrHandler
    If UserControl.Extender.Width < 11910 Then
        Exit Sub
    End If
    
    If UserControl.Extender.Height < 3500 Then
        Exit Sub
    End If
    
    Const SPACE As Long = 60
    Call tlb_main.Move(SPACE, 0, UserControl.Extender.Width - 2 * SPACE - 30, tlb_main.Height)
    
    Call frm_filters.Move(tlb_main.Left, tlb_main.Top + tlb_main.Height, tlb_main.Width)
    
    Call grd_lst.Move(tlb_main.Left, frm_filters.Top + frm_filters.Height + SPACE, tlb_main.Width)
    grd_lst.Height = UserControl.Extender.Height - frm_filters.Top - frm_filters.Height - 3 * SPACE - frm_detail.Height
    
    Call frm_detail.Move(grd_lst.Left, grd_lst.Top + grd_lst.Height + SPACE, grd_lst.Width)
    
'    Call grd_values.Move(SPACE, SPACE + 250, frm_detail.Width - 2 * SPACE, frm_detail.Height - 2 * SPACE - 250)
    
    Exit Sub
ErrHandler:
    Call ErrorHandler("InitCtrlSize")
End Sub

' Standard error handler
Private Sub ErrorHandler(ByVal as_Fct As String)
    Err.Raise Err.Number, UserControl.Name & "." & UserControl.Ambient.DisplayName & "::" & as_Fct & SEP1 & Err.Source, Err.Description
End Sub

Public Sub Load_A_Com()
    
On Error GoTo ErrHandler

    If mb_Initialized Then Exit Sub
    
    mb_Initialized = True
    
    mb_EventRunning = True
    
    Dim lo_control As Object
    Dim lo_ToolTip As Object
    
      For Each lo_control In Controls
        Select Case UCase(TypeName(lo_control))
        Case "ARMCOMBOBOX"
          Set lo_control.ArmDb = mo_Db
          Call lo_control.Load_A_Com
        Case "ARMPICKER"
          Set lo_control.ArmDb = mo_Db
          Call lo_control.Load_A_Com
        Case "TOOLBARCONTROL"
          lo_control.Language = ms_Language_Code
'          Set lo_Control.ArmDb = mo_Db
          Call lo_control.Load_A_Com
        Case "ARMGRID"
          Set lo_control.ArmDb = mo_Db
          Call lo_control.Load_A_Com
        Case "ARMTREEVIEW"
          Set lo_control.ArmDb = mo_Db
          lo_control.Language = ms_Language_Code
          Call lo_control.Load_A_Com
        Case "ARMCHECKVIEW"
          Set lo_control.ArmDb = mo_Db
          Call lo_control.Load_A_Com
        Case "A_CALOCX"
          lo_control.Language = ms_Language_Code
          Call lo_control.reinit_cal
        Case "TOOLBR"
          Set lo_control.ArmDb = mo_Db
          Call lo_control.Load_A_Com
        End Select
      Next
    
    ' init toolbar
'336EESFGIDRW®05858®QE/BAC®CVVDGGEFFJTT
    Call tlb_main.SetToolbarInfoStringParameters("001EESFGIDRW®011708®QE/BAC®AAABBBCCCHLLIGGTTT®111708®QE/BAC®AHHBIITTT®211708®QE/BAC®AHHTTT", "001")
    
    ' init grig
    grd_lst.AllowExcelExport = True
    grd_lst.Title = "NSV values"
    grd_lst.MultiSelect = False
    
    
    If Not grd_lst.SetColumns(Array( _
                Join(Array("YYMM", 0, 1, "YYMM", "#YYMM"), SEP) _
                , Join(Array("BMKcode", 0, 1, "BMKcode", "#BMKcode"), SEP) _
                , Join(Array("YY", 0, 0, "YY", "YearCode"), SEP) _
                , Join(Array("YYYY", 900, 0, "YYYY", "Year"), SEP) _
                , Join(Array("MM", 900, 0, "MM", "Month"), SEP) _
                , Join(Array("BMK_Desc", 2900, 0, "BMK_Desc", "Market"), SEP) _
                , Join(Array("MS", 700, 0, "MS", "Mineral", "real"), SEP) _
                , Join(Array("MT", 700, 0, "MT", "Metal", "real"), SEP) _
                , Join(Array("GRD", 700, 0, "GRD", "Grid", "real"), SEP) _
                , Join(Array("WL", 700, 0, "WL", "Wool", "real"), SEP) _
                )) Then
        MsgBox ("Grid not initialized!")
    End If
    
    cbo_fYear.FirstBlankItem = False
    cbo_fYear.Request = "SELECT DISTINCT left(YYMM,2) as YY, '20' + left(YYMM,2) as YYDesc FROM BMKNSV ORDER BY 1"
    cbo_fYear.Load
    
    Set cbo_fYear.SelectedItem = cbo_fYear.ComboItems(cbo_fYear.Count)
    
    cbo_year.FirstBlankItem = False
    cbo_year.Request = "SELECT DISTINCT substring(YY,3,2) AS YY, YY as YYDesc FROM Calendar ORDER BY 1"
    
    cbo_month.FirstBlankItem = False
    cbo_month.AddItem (Array("01", "January"))
    cbo_month.AddItem (Array("02", "February"))
    cbo_month.AddItem (Array("03", "March"))
    cbo_month.AddItem (Array("04", "April"))
    cbo_month.AddItem (Array("05", "May"))
    cbo_month.AddItem (Array("06", "June"))
    cbo_month.AddItem (Array("07", "July"))
    cbo_month.AddItem (Array("08", "August"))
    cbo_month.AddItem (Array("09", "September"))
    cbo_month.AddItem (Array("10", "October"))
    cbo_month.AddItem (Array("11", "November"))
    cbo_month.AddItem (Array("12", "December"))
    
    cbo_fMarket.FirstBlankItem = True
    cbo_fMarket.Request = "select BMK_Code, BMK_Desc from Business_Market2 where language_code='" & ms_Language_Code & "' order by 2"
    
    cbo_BMK_Code.FirstBlankItem = False
    cbo_BMK_Code.Request = "select BMK_Code, BMK_Desc from Business_Market2 where language_code='" & ms_Language_Code & "' order by 2"
    
    Call UpdateUI(ArmScreenMode.smMain)
    
    Call RefreshGrid
    
    mb_EventRunning = False
    
    Exit Sub
    
ErrHandler:
    Call ErrorHandler("Load_A_COM")
End Sub



Private Sub UpdateUI(ByVal au_Mode As ArmScreenMode)
On Error GoTo ErrHandler

    ' set active face
    mu_ActiveMode = au_Mode
    tlb_main.Redraw = False
    

    ' apply face
    Dim lo_ctrl As Object

    ' hide all frames
    frm_detail.Visible = True
    grd_lst.Visible = True
    frm_filters.Visible = True

    ' we have clean screen we can display proper controls
    Select Case mu_ActiveMode
        Case smMain
            Call EnableControl(grd_lst, True)
            Call SetEnabled(GetContainedControlsChain(frm_filters), True)
            Call SetEnabled(GetContainedControlsChain(frm_detail), False)
    
            Call tlb_main.DisplayFace(C_TOOLBARFACE_ITEM_LST)
        Case smUpdate
            ' we are in Update section
            Call EnableControl(grd_lst, False)
            Call SetEnabled(GetContainedControlsChain(frm_filters), False)
            Call SetEnabled(GetContainedControlsChain(frm_detail), True)
            Call EnableControl(cbo_year, False)
            Call EnableControl(cbo_month, False)
            Call EnableControl(cbo_BMK_Code, False)
            
            Call tlb_main.DisplayFace(C_TOOLBARFACE_ITEM_MTNC)
        
        Case smAdd
            ' we are in Update section
            Call EnableControl(grd_lst, False)
            Call SetEnabled(GetContainedControlsChain(frm_filters), False)
            Call SetEnabled(GetContainedControlsChain(frm_detail), True)
            
            Call tlb_main.DisplayFace(C_TOOLBARFACE_ITEM_MTNC)
'        Case smView
'            ' we are in PreView section
'            Call EnableControl(grd_lst, False)
            
        Case smDelete
            ' we are in PreView section
            Call EnableControl(grd_lst, False)
            Call SetEnabled(GetContainedControlsChain(frm_filters), False)
            Call SetEnabled(GetContainedControlsChain(frm_detail), False)
            
            Call tlb_main.DisplayFace(C_TOOLBARFACE_ITEM_DELETE)
        
        Case Else
            Debug.Assert (False)
    End Select
    
    ' to display face immidiatelly
    tlb_main.Redraw = True
    UserControl.Refresh
    Exit Sub
ErrHandler:
    Call ErrorHandler("UpdateUI()")
End Sub


Private Sub RefreshGrid()
On Error GoTo ErrHandler
    Const REQ As String = "EXEC BMKNSV_lst $YY$, $BMK_CODE$, $LANG$"
    Dim ls_Req As String
    
    ls_Req = Replace(REQ, "$YY$", SqlStr(GetCboKey(cbo_fYear), 2, True), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$BMK_CODE$", SqlStr(GetCboKey(cbo_fMarket), 4, True), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$LANG$", SqlStr(ms_Language_Code, 1), , , vbTextCompare)
    
    If Not grd_lst.Load(ls_Req, True) Then
        MsgBox ("Grid not loaded (BMKNSV_lst)!")
    End If
    
    Call Item_Clear
    
    Exit Sub
ErrHandler:
     Call ErrorHandler("RefreshGrid()")
End Sub


Private Function GetContainedControlsChain(ByVal ao_parent As Object) As Collection
On Error GoTo ErrHandler
    Dim lo_retCollection As New Collection
    Dim lo_control As Object
    
    For Each lo_control In Controls
        If Not TypeOf lo_control Is CommonDialog Then
            If Not TypeOf lo_control.Container Is ONSV_GSVManager Then
                If ao_parent.hwnd = lo_control.Container.hwnd Then
                    If TypeOf lo_control Is Frame Then
                        Dim lo_aux_collection As New Collection
                        Dim ll_i As Long
                        Set lo_aux_collection = GetContainedControlsChain(lo_control)
                        For ll_i = 1 To lo_aux_collection.Count
                            lo_retCollection.Add (lo_aux_collection.Item(ll_i))
                        Next
                    Else
                        Call lo_retCollection.Add(lo_control)
                    End If
                End If
            End If
        End If
    Next
    Set GetContainedControlsChain = lo_retCollection
    Exit Function
ErrHandler:
    Call ErrorHandler(Extender.Name & ".GetContainedControlsChain()")
End Function

Private Sub cbo_fMarket_ComboItemSelected()
    Call cbo_fYear_ComboItemSelected
End Sub

Private Sub cbo_fYear_ComboItemSelected()
On Error GoTo ErrHandler
    If mb_EventRunning Then Exit Sub
    Call RefreshGrid
    Exit Sub
ErrHandler:
     Call ErrorMessage("cbo_fYear_ComboItemSelected()")
End Sub

Private Sub grd_lst_SelChange()
On Error GoTo ErrHandler
    If grd_lst.SelectedCount = 0 Then Exit Sub
    mb_EventRunning = True
    
    Call LockScreen(True)
    
    Call Item_ViewInit(grd_lst.SelectedKey(0))
    
    Call LockScreen(False)
    mb_EventRunning = False
    Exit Sub
ErrHandler:
    Call LockScreen(False)
    mb_EventRunning = False
    Call ErrorMessage("grd_lst_SelChange")
End Sub

Private Sub tlb_main_action(ByVal as_Role As String, as_Language As String)
On Error GoTo ErrHandler
    Call LockScreen(True)
    mb_EventRunning = True

    Select Case as_Role
    Case "A"
        Call Item_AddInit
    Case "B"       ' Update, refresh detail
        If grd_lst.SelectedCount = 1 Then
            Call Item_UpdateInit(grd_lst.SelectedKey(0))
        Else
            Call MsgBox(MsgText(9214, ms_Language_Code, "#Please, select single row"))
        End If
    Case "C"
        If grd_lst.SelectedCount = 1 Then
            Call Item_DeleteInit(grd_lst.SelectedKey(0))
        Else
            Call MsgBox(MsgText(9214, ms_Language_Code, "#Please, select single row"))
        End If
    Case "I"              ' reset changes
        Select Case mu_ActiveMode
        Case smAdd
            Call Item_AddInit
        Case smUpdate
            Debug.Assert (grd_lst.SelectedCount = 1)
            Call Item_UpdateInit(grd_lst.SelectedKey(0))
        End Select
    Case "L"        ' refresh grid
        cbo_fYear.Clear
        cbo_fYear.Load
        Set cbo_fYear.SelectedItem = cbo_fYear.ComboItems(cbo_fYear.Count)

        Call RefreshGrid
    Case "H"
        Select Case mu_ActiveMode
        Case smAdd
            Call Item_Add
        Case smUpdate
            Call Item_Update
        Case smDelete
            Call Item_Delete
        End Select
    Case "T"              ' QUIT
        If mu_ActiveMode = smMain Then
            RaiseEvent quit
        Else
            Call UpdateUI(smMain)
        End If
    Case "G"
        Call LockScreen(False)
        dlg_fileopen.ShowOpen
        
        dlg_fileopen.DialogTitle = "Open a file to import."
        
        Call LockScreen(True)
        If dlg_fileopen.FileName <> "" Then
        
            ImportFromXLS (dlg_fileopen.FileName)
        
        End If
        
    End Select

    Call LockScreen(False)
    mb_EventRunning = False
    Exit Sub
ErrHandler:
    Call LockScreen(False)
    mb_EventRunning = False
    Call ErrorMessage("tlb_main_action")
End Sub

Private Function Item_Exists(ByVal as_yymm As String, ByVal as_bmk_code As String) As Boolean
On Error GoTo ErrHandler
Const C_REQ As String = "EXEC BMKNSV_chk $YYMM$, $BMK_CODE$"
    Item_Exists = False
    
    Dim ll_Cursor As Long
    
    Dim ls_Req As String
    
    ls_Req = Replace(C_REQ, "$YYMM$", SqlStr(as_yymm, 4), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$BMK_CODE$", SqlStr(as_bmk_code, 4), , , vbTextCompare)
    
    ll_Cursor = OpenSQLSafe(mo_Db, ls_Req)
    
    Item_Exists = (mo_Db.GetFields(ll_Cursor, "GRPCount") > 0)
    
    Call mo_Db.Close(ll_Cursor)
    ll_Cursor = 0
    
    
    Exit Function
ErrHandler:
    If ll_Cursor > 0 Then
        Call mo_Db.Close(ll_Cursor)
        ll_Cursor = 0
    End If
    Call ErrorHandler(Extender.Name & ".Item_Exists")
End Function

Private Sub Item_Add()
On Error GoTo ErrHandler
Const C_REQ As String = "EXEC BMKNSV_chk $YYMM$, $BMK_CODE$"
    If Not Item_Check Then
        Exit Sub
    End If

    Dim ll_Cursor As Long
    
    ll_Cursor = OpenSQLSafe(mo_Db, ReplacePlaceholders(C_REQ))
    
    If mo_Db.GetFields(ll_Cursor, "GRPCount") > 0 Then
        Call mo_Db.Close(ll_Cursor)
        ll_Cursor = 0
        Call MsgBox(MsgText(666, ms_Language_Code, "Record already exists in database."), , vbInformation)
        Exit Sub
    End If
    
    Call mo_Db.Close(ll_Cursor)
    ll_Cursor = 0
    
    Call Item_AddDB
    
    Call UpdateUI(smMain)
    
    Call UpdateGridAfterAction(grd_lst, "Add", Array(GetCboKey(cbo_year) & GetCboKey(cbo_month), GetCboKey(cbo_BMK_Code)))
    Exit Sub
ErrHandler:
    If ll_Cursor > 0 Then
        Call mo_Db.Close(ll_Cursor)
        ll_Cursor = 0
    End If
    Call ErrorHandler(Extender.Name & ".Item_Add")
End Sub

Private Sub Item_Update()
On Error GoTo ErrHandler
    If Not Item_Check Then
        Exit Sub
    End If
    
    Call Item_UpdateDB
    
    Call UpdateUI(smMain)
    
    Call UpdateGridAfterAction(grd_lst, "Upd", Array(GetCboKey(cbo_year) & GetCboKey(cbo_month), GetCboKey(cbo_BMK_Code)))
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_Update")
End Sub

Private Sub Item_Delete()
On Error GoTo ErrHandler
    If Not Item_Check Then
        Exit Sub
    End If
    
    Call Item_DeleteDB
    
    Call UpdateUI(smMain)
    
    Call UpdateGridAfterAction(grd_lst, "Del", Array(GetCboKey(cbo_year) & GetCboKey(cbo_month), GetCboKey(cbo_BMK_Code)))
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_Delete")
End Sub

Private Sub Item_AddDB()
On Error GoTo ErrHandler
    Const C_REQ As String = "EXEC BMKNSV_ins $YYMM$, $BMK_CODE$, $MS$, $MT$, $GRD$, $WL$"
    
    Call ExecuteSQLSafe(mo_Db, ReplacePlaceholders(C_REQ), 1)
        
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_AddDB")
End Sub

Private Sub Item_UpdateDB()
On Error GoTo ErrHandler
    Const C_REQ As String = "EXEC BMKNSV_upd $YYMM$, $BMK_CODE$, $MS$, $MT$, $GRD$, $WL$"
    
    Call ExecuteSQLSafe(mo_Db, ReplacePlaceholders(C_REQ), 1)
        
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_UpdateDB")
End Sub

Private Sub Item_DeleteDB()
On Error GoTo ErrHandler
    Const C_REQ As String = "EXEC BMKNSV_del $YYMM$, $BMK_CODE$"
    
    Call ExecuteSQLSafe(mo_Db, ReplacePlaceholders(C_REQ), 1)
        
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_DeleteDB")
End Sub

Private Function ReplacePlaceholders(ByVal as_req As String) As String
On Error GoTo ErrHandler

    as_req = Replace(as_req, "$YYMM$", SqlStr(GetCboKey(cbo_year) & GetCboKey(cbo_month), 4), , , vbTextCompare)
    as_req = Replace(as_req, "$BMK_CODE$", SqlStr(GetCboKey(cbo_BMK_Code), 4), , , vbTextCompare)
    as_req = Replace(as_req, "$MS$", SQLNum(txt_mineral.Text), , , vbTextCompare)
    as_req = Replace(as_req, "$MT$", SQLNum(txt_metal.Text), , , vbTextCompare)
    as_req = Replace(as_req, "$GRD$", SQLNum(txt_grid.Text), , , vbTextCompare)
    as_req = Replace(as_req, "$WL$", SQLNum(txt_wool.Text), , , vbTextCompare)

    ReplacePlaceholders = as_req
    Exit Function
ErrHandler:
    Call ErrorHandler(Extender.Name & ".ReplacePlaceholders")
End Function

' loads values from cursor into form. if cursor=0 then reset whole detail
Private Sub Item_LoadValues(ByVal av_Key As Variant)
On Error GoTo ErrHandler

    mb_EventRunning = True
    If Not IsEmpty(av_Key) Then
        Debug.Assert (IsArray(av_Key))
        If grd_lst.SelectedKey(0)(0) <> av_Key(0) Or grd_lst.SelectedKey(0)(1) <> av_Key(1) Then
            If Not grd_lst.SearchKey(True, av_Key) Then
                mb_EventRunning = False
                Exit Sub
            End If
        End If
        Call SetComboBoxText(cbo_year, grd_lst.SelectedLine(0, "YY"), grd_lst.SelectedLine(0, "YYYY"))
        Call SetComboBoxText(cbo_month, grd_lst.SelectedLine(0, "MM"), grd_lst.SelectedLine(0, "MM"))
        Call SetComboBoxText(cbo_BMK_Code, grd_lst.SelectedLine(0, "BMKcode"), grd_lst.SelectedLine(0, "BMK_Desc"))
        
        txt_mineral.Text = grd_lst.SelectedLine(0, "MS")
        txt_metal.Text = grd_lst.SelectedLine(0, "MT")
        txt_grid.Text = grd_lst.SelectedLine(0, "GRD")
        txt_wool.Text = grd_lst.SelectedLine(0, "WL")
        
    Else
        ' load default values
        txt_mineral.Text = "0.0"
        txt_metal.Text = "0.0"
        txt_grid.Text = "0.0"
        txt_wool.Text = "0.0"
        
    End If
    
    mb_EventRunning = False

    Exit Sub
ErrHandler:
    mb_EventRunning = False
    Call ErrorHandler(Extender.Name & ".Item_LoadValues")
End Sub

Private Sub Item_Load_E(ByRef ao_workSheet As Object, ByVal al_Row As Long, ByVal al_dataYear As Long, ByVal al_dataMonth As Long)
On Error GoTo ErrHandler
    
    If Not SetComboItemValue(cbo_year, Mid(al_dataYear, 3, 2)) Then
        Call MsgBox("Invalid value for year:" & al_dataYear)
    End If
    
    If Not SetComboItemValue(cbo_month, Format(al_dataMonth, "#00")) Then
        Call MsgBox("Invalid value for data month:" & al_dataYear)
    End If
    
    If Not SetComboItemValue(cbo_BMK_Code, ao_workSheet.cells(al_Row, C_BMK_CODE_COL)) Then
        Call MsgBox("Invalid value for Market:" & ao_workSheet.cells(al_Row, C_BMK_CODE_COL))
    End If
    
    txt_mineral.Text = Round(ao_workSheet.cells(al_Row, C_VALUE_MS_COL + 0), 2) * 100
    txt_metal.Text = Round(ao_workSheet.cells(al_Row, C_VALUE_MS_COL + 1), 2) * 100
    txt_grid.Text = Round(ao_workSheet.cells(al_Row, C_VALUE_MS_COL + 2), 2) * 100
    txt_wool.Text = Round(ao_workSheet.cells(al_Row, C_VALUE_MS_COL + 3), 2) * 100
    
    Exit Sub
ErrHandler:
    Call ErrorHandler("Item_Load_E()")
End Sub


Private Sub Item_AddInit()
On Error GoTo ErrHandler
    
    Call Item_Clear
    
    ' loading values
    Call Item_LoadValues(Empty)
    
    Call UpdateUI(ArmScreenMode.smAdd)
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_AddInit")
End Sub
Private Sub Item_DeleteInit(ByVal as_detailKey As Variant)
On Error GoTo ErrHandler
    
    Call Item_Clear
    
    ' loading values
    Call Item_LoadValues(as_detailKey)
    
    Call UpdateUI(ArmScreenMode.smDelete)
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_DeleteInit")
End Sub

Private Sub Item_ViewInit(ByVal as_detailKey As Variant)
On Error GoTo ErrHandler
    
    Call Item_Clear
    
    ' loading values
    Call Item_LoadValues(as_detailKey)
    
    Call UpdateUI(ArmScreenMode.smMain)
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_ViewInit")
End Sub

Private Sub Item_UpdateInit(ByVal as_detailKey As Variant)
On Error GoTo ErrHandler
    
    Call Item_Clear
    
    ' loading values
    Call Item_LoadValues(as_detailKey)
    
    Call UpdateUI(ArmScreenMode.smUpdate)
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_UpdateInit")
End Sub

Private Function Item_Check() As Boolean
On Error GoTo ErrHandler
    Item_Check = False

    If (Trim(txt_mineral.Text) <> "") And Not isNumeric(txt_mineral.Text) Then
        Call MsgBox("Wrong Mineral number!")
        Call txt_mineral.SetFocus
        Exit Function
    End If
    
    If (Trim(txt_metal.Text) <> "") And Not isNumeric(txt_metal.Text) Then
        Call MsgBox("Wrong Metal number!")
        Call txt_metal.SetFocus
        Exit Function
    End If
    
    If (Trim(txt_grid.Text) <> "") And Not isNumeric(txt_grid.Text) Then
        Call MsgBox("Wrong Grid number!")
        Call txt_grid.SetFocus
        Exit Function
    End If

    If (Trim(txt_wool.Text) <> "") And Not isNumeric(txt_wool.Text) Then
        Call MsgBox("Wrong Wool number!")
        Call txt_wool.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_year) = "" Then
        Call MsgBox("Missing Year!")
        Call cbo_year.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_month) = "" Then
        Call MsgBox("Missing Month!")
        Call cbo_month.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_BMK_Code) = "" Then
        Call MsgBox("Missing Market!")
        Call cbo_BMK_Code.SetFocus
        Exit Function
    End If
    
    Item_Check = True
    Exit Function
ErrHandler:
    Call ErrorMessage("Item_Check")
End Function


Private Sub Item_Clear()
On Error GoTo ErrHandler
    Dim lo_control As Control
    For Each lo_control In Controls
        If lo_control.Tag <> "" Then
            Select Case UCase(TypeName(lo_control))
            Case "ARMCOMBOBOX"
                Set lo_control.SelectedItem = Nothing
            Case "ARMPICKER"
            Case "TOOLBARCONTROL"
            Case "ARMGRID"
            Case "ARMTREEVIEW"
            Case "ARMCHECKVIEW"
            Case "A_CALOCX"
            Case "TOOLBR"
            Case "TEXTBOX"
                lo_control.Text = ""
            Case "CHECKBOX"
                lo_control.Value = vbUnchecked
            End Select
        End If
    Next
    Exit Sub
ErrHandler:
    Call ErrorHandler("Item_Clear()")
End Sub


Public Sub Unload_A_Com()
    
On Error GoTo ErrHandler
    mb_Initialized = False
    
    Dim lo_control As Object

    For Each lo_control In Controls
      Select Case UCase(TypeName(lo_control))
      Case "ARMCOMBOBOX"
        Call lo_control.Unload_A_Com
      Case "ARMPICKER"
        Call lo_control.Unload_A_Com
      Case "TOOLBARCONTROL"
        Call lo_control.Unload_A_Com
      Case "ARMGRID"
        Call lo_control.Unload_A_Com
      Case "ARMTREEVIEW"
        Call lo_control.Unload_A_Com
      Case "ARMCHECKVIEW"
        Call lo_control.Unload_A_Com
      Case "TOOLBR"
        Call lo_control.Unload_A_Com
      End Select
    Next
    Exit Sub
    
ErrHandler:
    
    Call ErrorHandler("UnLoad_A_Com")
    
End Sub

Private Sub UpdateGridAfterAction(ByVal ao_grid As ArmGrid, ByVal as_Action As String, ByVal av_Key As Variant)
On Error GoTo ErrHandler
    Dim ll_Index As Long
    Dim lo_Column As ArmColumn
    
    Select Case as_Action
    Case "Add"
        ' insert row at the end of grid
        Debug.Assert (ao_grid.Cols > 0)
        Dim lsa_newRow() As String
        'MS REDIMM
        Call SafeRedimPreserve(lsa_newRow, ao_grid.Cols - 1)
        Dim ll_KeyIndex As Long
        ll_KeyIndex = 0
        
        For ll_Index = 0 To ao_grid.Cols - 1
            Set lo_Column = ao_grid.Columns(ll_Index)
            If lo_Column.Key Then
                Debug.Assert (UBound(av_Key) >= ll_KeyIndex)
                lsa_newRow(ll_Index) = av_Key(ll_KeyIndex)
                ll_KeyIndex = ll_KeyIndex + 1
            Else
                lsa_newRow(ll_Index) = GetDataSrcForGrid(lo_Column)
            End If
        Next
        Call ao_grid.AddLine(lsa_newRow)
    Case "Upd"
        ' search and update row in the grid
        Debug.Assert (ao_grid.Cols > 0)
        
        If Not ao_grid.SearchKey(True, av_Key) Then
            Call Err.Raise(1, "ao_grid.SearchKey", "Cannot update grid.")
        End If
        
        For ll_Index = 0 To ao_grid.Cols - 1
            Set lo_Column = ao_grid.Columns(ll_Index)
            If Not lo_Column.Key Then
                If Not lo_Column.SetData(ao_grid.Row, GetDataSrcForGrid(lo_Column)) Then
                    Call Err.Raise(1, "lo_Column.SetData", "Cannot update grid.")
                End If
            End If
        Next
    Case "Del"
        ' remove row from grid
        If Not ao_grid.DeleteLine(av_Key) Then
            Call Err.Raise(1, "DeleteLine", "Cannot delete line.")
        End If
    Case Else
        Debug.Assert (False)
    End Select
    Exit Sub
ErrHandler:
     Call ErrorHandler("UpdateGridAfterAction()")
End Sub

' when updating main grid from detail
Private Function GetDataSrcForGrid(ByVal ao_Column As ArmColumn) As String
On Error GoTo ErrHandler
    Select Case ao_Column.FieldName
        Case "YYMM"
            GetDataSrcForGrid = GetCboKey(cbo_year) & GetCboKey(cbo_month)
        Case "BMKcode"
            GetDataSrcForGrid = GetCboKey(cbo_BMK_Code)
        Case "YY"
            GetDataSrcForGrid = GetCboKey(cbo_year)
        Case "YYYY"
            GetDataSrcForGrid = GetCboText(cbo_year)
        Case "MM"
            GetDataSrcForGrid = GetCboKey(cbo_month)
        Case "BMK_Desc"
            GetDataSrcForGrid = GetCboText(cbo_BMK_Code)
        Case "MS"
            GetDataSrcForGrid = txt_mineral.Text
        Case "MT"
            GetDataSrcForGrid = txt_metal.Text
        Case "GRD"
            GetDataSrcForGrid = txt_grid.Text
        Case "WL"
            GetDataSrcForGrid = txt_wool.Text
    End Select
    
    Exit Function
ErrHandler:
     Call ErrorHandler("GetDataSrcForGrid()")
End Function



Private Sub ImportFromXLS(ByVal as_fileName As String)
On Error GoTo ErrHandler

    Dim lo_ExcelApp As Object
    Dim lo_WorkBook As Object
    Dim lo_WorkSheet As Object
    
    Dim lo_found As Boolean

On Error GoTo Err_NotLoaded
        
    Set lo_ExcelApp = GetObject(, "Excel.Application")
    
    If lo_ExcelApp Is Nothing Then
        Set lo_ExcelApp = CreateObject("Excel.Application")
    End If
    
    If lo_ExcelApp Is Nothing Then GoTo ErrHandler
    
On Error GoTo ErrHandler

    Set lo_WorkBook = OpenExcelDocument(lo_ExcelApp, as_fileName, lo_found)
    lo_ExcelApp.ScreenUpdating = False
    Call lo_WorkBook.Activate
    
    ' collect the list of sheets to pick correct one
    Dim lo_dlg As New ONSV_ItemPickerNSV
    
    lo_dlg.Title = lo_WorkBook.Name & " - Select the sheet to import."
    Set lo_dlg.Db = mo_Db
    Call lo_dlg.Load_A_Com
    
    Dim ll_firstRow As Long
    Dim lb_sheetFound As Boolean
    Dim lo_lvItem As ListItem
    Dim ls_year As String
    Dim ls_month As String
    lb_sheetFound = False
    
    For Each lo_WorkSheet In lo_WorkBook.Worksheets
        ll_firstRow = GetFirstDataRow(lo_WorkSheet, C_BMK_CODE_COL, 4)
        If ll_firstRow <> 0 Then
            Set lo_lvItem = lo_dlg.AddItem(lo_WorkSheet.Name, lo_WorkSheet.Name)
            ls_year = lo_WorkSheet.cells(3, C_DATA_YEAR_COL)
            If Not isNumeric(ls_year) Then ls_year = ""
            
            ls_month = lo_WorkSheet.cells(3, C_DATA_MONTH_COL)
            If Not isNumeric(ls_month) Then ls_month = ""
            
            lo_lvItem.Tag = Join(Array(ls_year, ls_month, ll_firstRow), SEP)
            Call lo_lvItem.ListSubItems.Add(, , lo_WorkSheet.Name)
            lb_sheetFound = True
        End If
    Next
    Set lo_dlg.lv_items.SelectedItem = lo_dlg.lv_items.ListItems(1)
    Call lo_dlg.lv_items_ItemClick(lo_dlg.lv_items.SelectedItem)
    
    Dim lb_closeWorkBook As Boolean
    lb_closeWorkBook = True
    
    If lb_sheetFound Then
        ' select a sheet to import
        Call LockScreen(False)
        Call lo_dlg.show(vbModal)
        DoEvents
        Call LockScreen(True)
        If lo_dlg.Result Then
            Set lo_WorkSheet = lo_WorkBook.Worksheets(lo_dlg.XLSSheetName)
            Call lo_WorkSheet.Activate
            
            ' we can read the data
            Dim ll_Row As Long
            Dim ll_prevRow As Long
            Dim lb_sheetUpdated As Boolean
            lb_sheetUpdated = False
            ll_prevRow = 1
            ll_Row = lo_dlg.FirstRow
            
            If lo_dlg.Data_Year <> lo_WorkSheet.cells(3, C_DATA_YEAR_COL) Then
                lo_WorkSheet.cells(3, C_DATA_YEAR_COL) = lo_dlg.Data_Year
                lb_sheetUpdated = True
            End If
            
            If lo_dlg.Data_Month <> lo_WorkSheet.cells(3, C_DATA_MONTH_COL) Then
                lo_WorkSheet.cells(3, C_DATA_MONTH_COL) = lo_dlg.Data_Month
                lb_sheetUpdated = True
            End If
            
            While Not IsLastExcellRow(lo_WorkSheet, ll_Row)
            
                ll_Row = NextExcellRow(lo_WorkSheet, ll_Row)
                
                ll_prevRow = ll_Row
                
                If lo_WorkSheet.cells(ll_Row, C_BMK_CODE_COL) <> "" Then
                    
                    If Item_Exists(lo_dlg.Data_Year & lo_WorkSheet.cells(ll_Row, C_VALUE_MS_COL), lo_WorkSheet.cells(ll_Row, C_BMK_CODE_COL)) Then
                        Call Item_Load_E(lo_WorkSheet, ll_Row, lo_dlg.Data_Year, lo_dlg.Data_Month)
                        Call frm_detail.Refresh
                        DoEvents
'                            Call Item_audit(ll_recordID, "U")
                        Call Item_UpdateDB
                    Else
                        ' generate new record into ONSV_Baselines
                        Call Item_Load_E(lo_WorkSheet, ll_Row, lo_dlg.Data_Year, lo_dlg.Data_Month)
                        Call frm_detail.Refresh
                        DoEvents
'                            Call Item_audit(ll_recordID, "I")
                        Call Item_AddDB
                    End If
                End If
                ll_Row = ll_Row + 1
            Wend
            
            If lb_sheetUpdated Then
                Call lo_WorkBook.Save
                If Not lo_WorkBook.Saved Then
                    lb_closeWorkBook = False
                    lo_ExcelApp.ScreenUpdating = True
                    MsgBox ("File is locked by another instance. Please save it with different name and use as a souce for futher updates.")
                End If
            End If
        End If
    Else
        ' no valid sheet found
        Call MsgBox("No valid sheet found.")
    End If
    
    If lb_closeWorkBook Then
        Call lo_WorkBook.Close(True)
        lo_ExcelApp.ScreenUpdating = True
    End If
    
    Set lo_WorkSheet = Nothing
    Set lo_WorkBook = Nothing
    Set lo_ExcelApp = Nothing
    
    Exit Sub
    
Err_NotLoaded:
    If Err.Number = 429 Then
        Resume Next
    End If
ErrHandler:
    Call ErrorHandler(Extender.Name & ".ImportFromXLS")
End Sub



Private Function GetFirstDataRow(ByRef ao_workSheet As Object, ByVal al_columNb As Long, ByVal al_Row As Long) As Long
    GetFirstDataRow = 0
    While ao_workSheet.cells(al_Row, al_columNb) = ""
        al_Row = al_Row + 1
        If al_Row > 10 Then
            Exit Function
        End If
    Wend
    GetFirstDataRow = al_Row
End Function

Private Function OpenExcelDocument(ByRef ao_ExcelApp As Object, ByVal as_Name As String, ByRef ab_found As Boolean) As Object
On Error GoTo ErrorHandler
    
    Set OpenExcelDocument = Nothing
    
    Dim lo_WorkBook As Object
    
    ab_found = False
    For Each lo_WorkBook In ao_ExcelApp.Workbooks
      If StrComp(lo_WorkBook.FullName, as_Name, vbTextCompare) = 0 Then
        Set OpenExcelDocument = lo_WorkBook
        ab_found = True
        Exit Function
      End If
    Next
    
    Set OpenExcelDocument = ao_ExcelApp.Workbooks.Open(as_Name)
    Exit Function
    
ErrorHandler:
    Call ErrorHandler(Extender.Name & ".OpenExcelDocument")
End Function

' if there is 20 following lines emty we assume it is the last row
Private Function IsLastExcellRow(ByRef ao_workSheet As Object, ByVal al_Row As Long) As Boolean
On Error GoTo ErrorHandler
    IsLastExcellRow = True
    
    Dim ll_i As Long
    For ll_i = 0 To 20
        If ao_workSheet.cells(al_Row + ll_i, C_BMK_CODE_COL) <> "" Then
            IsLastExcellRow = False
            Exit Function
        End If
    
    Next

    Exit Function
ErrorHandler:
    Call ErrorHandler(Extender.Name & ".IsLastExcellRow")
End Function

Private Function NextExcellRow(ByRef ao_workSheet As Object, ByVal al_Row As Long) As Long
On Error GoTo ErrorHandler
    While ao_workSheet.cells(al_Row, C_BMK_CODE_COL) = ""
        al_Row = al_Row + 1
    Wend

    NextExcellRow = al_Row
    Exit Function
ErrorHandler:
    Call ErrorHandler(Extender.Name & ".NextExcellRow")
End Function

Private Function SetComboItemValue(ByRef ao_cbo As ArmCombobox, ByVal as_Key As String) As Boolean
On Error GoTo ErrorHandler
    SetComboItemValue = True
    If Not ao_cbo.SearchItem(as_Key) Then
        Call ao_cbo.Load
        SetComboItemValue = ao_cbo.SearchItem(as_Key)
    End If
    Exit Function
ErrorHandler:
    Call ErrorHandler(Extender.Name & ".SetComboItemValue")
End Function
'*********************************** support functions ******************************************
' display standard error message
Private Sub ErrorMessage(ByVal as_Fct As String)
    Dim ls_ErrSource As String
    Dim ls_errDescription As String
    Dim ls_Message As String
    
    ls_ErrSource = as_Fct & SEP1 & Err.Source
    ls_errDescription = Err.Description
    ls_Message = SCREEN_NAME & " exception. Nr:" & Err.Number & ",Desc: " & ls_errDescription & ",Src:" & ls_ErrSource & "@"
'    Call mo_Tools.LogMessage(mo_Db, ml_U_Code, SCREEN_NAME, ls_Message, "E")
    Call MsgBox("Error occured, please contact IT. Application will now shutdown." & vbCrLf & ls_ErrSource & vbCrLf & "Description: " & ls_errDescription, vbCritical, App.Title & " " & App.Major & "." & App.Minor & "." & App.Revision)
    End
End Sub

Private Sub EnableControl(ByVal ao_Control As Control, ByVal ab_Enabled As Boolean)
On Error GoTo ErrHandler

    Select Case UCase(TypeName(ao_Control))
        Case "FRAME", "LABEL", "MSFLEXGRID", "SHAPE", "ARMGRID", "ARMCHECKVIEW", "TABSTRIP"
            ao_Control.Enabled = ab_Enabled
            ' Do nothing !
        Case "LISTVIEW"
            ' Do nothing !
        Case "TEXTBOX"
            ao_Control.Locked = Not ab_Enabled
            ao_Control.BackColor = IIf(ab_Enabled, CL_COLOR_ENABLED, CL_COLOR_DISABLED)
            ao_Control.TabStop = ab_Enabled
        Case "OPTIONBUTTON"
            ao_Control.Enabled = ab_Enabled
        Case "ARMCOMBOBOX", "A_CALOCX", "OPTIONBUTTON", "ARMTREEVIEW", "LISTBOX", "PICTUREBOX", "CHECKBOX", "COMMANDBUTTON", "TOOLBARCONTROL"
            ao_Control.Enabled = ab_Enabled
            ao_Control.TabStop = ab_Enabled
        Case Else
          Debug.Print ao_Control.Name
    End Select
  Exit Sub
ErrHandler:
  Call ErrorHandler("EnableControl")
End Sub

Private Sub SetEnabled(ByVal ao_srcCtrl As Object, ByVal ab_Value As Boolean)
On Error GoTo ErrHandler
    Dim lo_ctrl As Object
    For Each lo_ctrl In ao_srcCtrl
        Call EnableControl(lo_ctrl, ab_Value)
    Next
    Exit Sub
ErrHandler:
     Call ErrorHandler("SetEnabled()")
End Sub


' translate string to sql format
' Params:
' as_Value (String)
' ab_EmptyNULL (Boolean = False)
Private Function SqlStr(ByVal as_Value As String, Optional ByVal al_MaxLen As Long = 8000, Optional ByVal ab_EmptyNULL As Boolean = False) As String
    If as_Value = "" And ab_EmptyNULL Then
        SqlStr = "NULL"
    Else
        SqlStr = "'" & Replace(Left(as_Value, IIf(Len(as_Value) <= al_MaxLen, Len(as_Value), al_MaxLen)), "'", "''") & "'"
    End If
End Function

Private Function SqlDate(ByVal av_Data As String) As String
On Error GoTo ErrHandler

    SqlDate = "NULL"
    If IsNull(av_Data) Then av_Data = ""
    If (Trim(CStr(av_Data)) <> "") And (CStr(av_Data) <> "0") Then
        SqlDate = "'" & Format(av_Data, "yyyy-mm-dd") & "'"
    End If
    Exit Function
ErrHandler:
End Function


Private Function SQLNum(ByVal as_str As String) As String
    SQLNum = Replace(as_str, ",", ".")
End Function

Private Function GetCboKey(ByRef ao_cbo As ArmCombobox) As String
On Error GoTo ErrHandler
    GetCboKey = ""
    If Not ao_cbo.SelectedItem Is Nothing Then
        GetCboKey = ao_cbo.SelectedItem.Key
    End If
    Exit Function
ErrHandler:
     Call ErrorHandler("GetCboKey()")
End Function

Private Function GetCboText(ByRef ao_cbo As ArmCombobox) As String
On Error GoTo ErrHandler
    GetCboText = ""
    If Not ao_cbo.SelectedItem Is Nothing Then
        GetCboText = ao_cbo.SelectedItem.DisplayText
    End If
    Exit Function
ErrHandler:
     Call ErrorHandler("GetCboText()")
End Function

' Sets combobox selected item
' Params:
' ao_ComboBox (ArmCombobox)
' as_Key (String)
' as_Desc (String)
Private Sub SetComboBoxText(ByRef ao_Combobox As ArmCombobox, ByVal as_Key As String, ByVal as_desc As String)
On Error GoTo ErrHandler
    If Not ao_Combobox.SearchItem(as_Key) Then
        ' key not found ... set value from parameter
        If as_Key = "" Or as_Key = "0" Then     ' zero or empty string is not valid key
            Set ao_Combobox.SelectedItem = Nothing
        Else
            Call ao_Combobox.AddItem(Array(as_Key, as_desc), True)
            ' to make vb raise event
            Call ao_Combobox.SearchItem(as_Key)
        End If
    End If
    Exit Sub
ErrHandler:
    Call ErrorHandler("SetComboBoxText")
End Sub

Private Sub LockScreen(ByVal ab_lock As Boolean)

    Dim ll_errNumber As Long, ls_ErrSrc As String, ls_ErrDesc As String
    ll_errNumber = Err.Number
    ls_ErrSrc = Err.Source
    ls_ErrDesc = Err.Description

On Error GoTo ErrHandler
    Static ll_Count As Long
    Static ll_Mousepointer As Long
    Static lb_Locked As Boolean
      
      
    ll_Count = ll_Count + IIf(ab_lock, 1, -1)
    Debug.Assert (ll_Count >= 0)
    
    ' First lock
    If Not lb_Locked And ab_lock Then
        ll_Mousepointer = Screen.MousePointer
        Screen.MousePointer = vbHourglass
        LockWindowUpdate UserControl.hwnd
        lb_Locked = True
    End If
    
    ' Unlock
    If ll_Count = 0 Then
        DoEvents ' Flush events
        LockWindowUpdate 0
        UserControl.Refresh
        Screen.MousePointer = ll_Mousepointer
        lb_Locked = False
    End If
    
    Err.Number = ll_errNumber
    Err.Source = ls_ErrSrc
    Err.Description = ls_ErrDesc
    
    Exit Sub
    
ErrHandler:
    Call ErrorHandler("LockScreen")
End Sub

Private Function OpenSQLSafe(ByVal ao_db As ARMSYSCOMLib.ArmDb, ByVal as_Request As String, Optional ByVal al_RowExpectedCount = -1) As Long
On Error GoTo ErrHandler
    Dim lc_Data As Long
    lc_Data = ao_db.OpenSQL(as_Request)
    If lc_Data = 0 Then
        Call Err.Raise(1, "ao_Db.OpenSQL - " & "SQL : " & as_Request, "SQL Error: " & GetDbError(ao_db))
    End If
    
    If al_RowExpectedCount <> -1 Then
        ' Then check the rowcount
        If ao_db.RowCount(lc_Data) <> al_RowExpectedCount Then
            Call Err.Raise(2, "SQL : " & as_Request, al_RowExpectedCount & "<>" & ao_db.RowCount(lc_Data))
        End If
    End If
    OpenSQLSafe = lc_Data
    Exit Function
ErrHandler:
    Call ErrorHandler("OpenSQLSafe")
End Function

Private Sub ExecuteSQLSafe(ByVal ao_db As ARMSYSCOMLib.ArmDb, ByVal as_Request As String, Optional ByVal al_RowAffectedCount = -1, Optional ab_DuplicityCheck As Boolean = False)
On Error GoTo ErrHandler

    ' First execute the request
    If Not ao_db.ExecuteSQL(as_Request) Then
        If GetArrayValue(ao_db.SQLErrorCodes, 0) = 547 Then
            Err.Raise 3, "SQL : " & as_Request, Join(ao_db.SQLErrorCodes, SEP2) & SEP1 & Join(ao_db.SQLErrorMessages, SEP2)
        End If
        Err.Raise 1, "SQL : " & as_Request, Join(ao_db.SQLErrorCodes, SEP2) & SEP1 & Join(ao_db.SQLErrorMessages, SEP2)
    End If

    If al_RowAffectedCount <> -1 Then
        ' Then check the rowcount
        If ao_db.SQLRowsAffected <> al_RowAffectedCount Then
            
            If ab_DuplicityCheck Then
                Err.Raise 4, "SQL : " & as_Request, al_RowAffectedCount & "<>" & ao_db.SQLRowsAffected
            Else
                Err.Raise 5, "SQL : " & as_Request, al_RowAffectedCount & "<>" & ao_db.SQLRowsAffected
            End If
        End If
    End If
    
    Exit Sub

ErrHandler:
    Call ErrorHandler("ExecuteSQLSafe")
End Sub

Private Function GetDbError(ByVal lo_Db As ARMSYSCOMLib.ArmDb) As String
On Error GoTo ErrHandler
    If IsArray(lo_Db.SQLErrorMessages) Then
        Debug.Assert (IsArray(lo_Db.SQLErrorCodes))
        ' Display errors msgBox
        GetDbError = Join(lo_Db.SQLErrorCodes, ",") & vbCrLf & Join(lo_Db.SQLErrorMessages, vbCrLf)
    Else
        ' ExecuteSQL failed but no error message?
        GetDbError = "Unknown error"
    End If
    Exit Function
ErrHandler:
    Call ErrorHandler("GetDbError()")
End Function

Private Function GetArrayValue(ByRef ao_variantArray As Variant, ByVal al_Index As Long) As Variant
    If IsArray(ao_variantArray) Then
        If UBound(ao_variantArray) <= al_Index Then
            GetArrayValue = ao_variantArray(al_Index)
        Else
            GetArrayValue = 0
        End If
    Else
        GetArrayValue = 0
    End If
End Function



