VERSION 5.00
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "comdlg32.ocx"
Begin VB.UserControl ONSV_baselines 
   ClientHeight    =   7965
   ClientLeft      =   0
   ClientTop       =   0
   ClientWidth     =   16095
   ScaleHeight     =   7965
   ScaleWidth      =   16095
   Begin MSComDlg.CommonDialog dlg_fileopen 
      Left            =   15600
      Top             =   7440
      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
   Begin VB.Frame frm_detail 
      Caption         =   "Values"
      Height          =   1965
      Left            =   60
      TabIndex        =   13
      Top             =   5490
      Width           =   15690
      Begin VB.TextBox txt_recordID 
         BorderStyle     =   0  'None
         BeginProperty Font 
            Name            =   "MS Sans Serif"
            Size            =   9.75
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   285
         Left            =   14160
         TabIndex        =   58
         Top             =   270
         Width           =   1425
      End
      Begin VB.TextBox txt_dataVersion 
         Height          =   300
         Left            =   14160
         TabIndex        =   49
         Tag             =   "data_version"
         Text            =   "Text1"
         Top             =   1575
         Width           =   1425
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   11
         Left            =   6390
         TabIndex        =   37
         Tag             =   "Month_Value12"
         Top             =   1425
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   10
         Left            =   5175
         TabIndex        =   36
         Tag             =   "Month_Value11"
         Top             =   1425
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   9
         Left            =   3960
         TabIndex        =   35
         Tag             =   "Month_Value10"
         Top             =   1425
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   8
         Left            =   2505
         TabIndex        =   34
         Tag             =   "Month_Value9"
         Top             =   1425
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   7
         Left            =   1305
         TabIndex        =   33
         Tag             =   "Month_Value8"
         Top             =   1425
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   6
         Left            =   90
         TabIndex        =   32
         Tag             =   "Month_Value7"
         Top             =   1425
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   5
         Left            =   6390
         TabIndex        =   31
         Tag             =   "Month_Value6"
         Top             =   675
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   4
         Left            =   5175
         TabIndex        =   30
         Tag             =   "Month_Value5"
         Top             =   675
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   3
         Left            =   3960
         TabIndex        =   29
         Tag             =   "Month_Value4"
         Top             =   675
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   2
         Left            =   2505
         TabIndex        =   28
         Tag             =   "Month_Value3"
         Top             =   675
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   1
         Left            =   1305
         TabIndex        =   27
         Tag             =   "Month_Value2"
         Top             =   675
         Width           =   1170
      End
      Begin VB.TextBox txt_values 
         Height          =   285
         Index           =   0
         Left            =   90
         TabIndex        =   26
         Tag             =   "Month_Value1"
         Top             =   675
         Width           =   1170
      End
      Begin Project1.ArmCombobox cbo_dValueType 
         Height          =   345
         Left            =   12705
         TabIndex        =   47
         Tag             =   "Value_type"
         Top             =   195
         Width           =   885
         _ExtentX        =   1561
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_dDataType 
         Height          =   345
         Left            =   10695
         TabIndex        =   48
         Tag             =   "Data_Type"
         Top             =   195
         Width           =   990
         _ExtentX        =   1746
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_dDataQuarter 
         Height          =   345
         Left            =   14160
         TabIndex        =   50
         Tag             =   "data_quarter"
         Top             =   1095
         Width           =   1425
         _ExtentX        =   2514
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_dDataMonth 
         Height          =   345
         Left            =   14160
         TabIndex        =   51
         Tag             =   "Data_Month"
         Top             =   645
         Width           =   1425
         _ExtentX        =   2514
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_dDataYear 
         Height          =   345
         Left            =   8535
         TabIndex        =   52
         Tag             =   "Data_Year"
         Top             =   195
         Width           =   1080
         _ExtentX        =   1905
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_dMG_Code 
         Height          =   345
         Left            =   9060
         TabIndex        =   53
         Tag             =   "MG_code"
         Top             =   690
         Width           =   3495
         _ExtentX        =   6165
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_dBUG_Code 
         Height          =   345
         Left            =   9060
         TabIndex        =   54
         Tag             =   "BUG_code"
         Top             =   1110
         Width           =   3495
         _ExtentX        =   6165
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_dBMK_Code 
         Height          =   345
         Left            =   9060
         TabIndex        =   55
         Tag             =   "BMK_code"
         Top             =   1545
         Width           =   3495
         _ExtentX        =   6165
         _ExtentY        =   609
      End
      Begin VB.Label lbl_labels 
         Caption         =   "ID"
         Height          =   255
         Index           =   27
         Left            =   13845
         TabIndex        =   59
         Top             =   285
         Width           =   330
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Market"
         Height          =   270
         Index           =   25
         Left            =   8295
         TabIndex        =   46
         Top             =   1590
         Width           =   675
      End
      Begin VB.Label lbl_labels 
         Caption         =   "BU group"
         Height          =   270
         Index           =   24
         Left            =   8115
         TabIndex        =   45
         Top             =   1110
         Width           =   855
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Material group"
         Height          =   270
         Index           =   23
         Left            =   7755
         TabIndex        =   44
         Top             =   720
         Width           =   1215
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Value type"
         Height          =   270
         Index           =   22
         Left            =   11835
         TabIndex        =   43
         Top             =   270
         Width           =   825
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Data version"
         Height          =   270
         Index           =   21
         Left            =   13155
         TabIndex        =   42
         Top             =   1590
         Width           =   1020
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Data Quarter"
         Height          =   270
         Index           =   20
         Left            =   13155
         TabIndex        =   41
         Top             =   1155
         Width           =   1020
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Data Month"
         Height          =   270
         Index           =   19
         Left            =   13245
         TabIndex        =   40
         Top             =   705
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Data Year"
         Height          =   270
         Index           =   18
         Left            =   7710
         TabIndex        =   39
         Top             =   270
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Data type"
         Height          =   270
         Index           =   17
         Left            =   9885
         TabIndex        =   38
         Top             =   270
         Width           =   795
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "December"
         Height          =   270
         Index           =   16
         Left            =   6510
         TabIndex        =   25
         Top             =   1140
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "November"
         Height          =   270
         Index           =   15
         Left            =   5295
         TabIndex        =   24
         Top             =   1140
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "October"
         Height          =   270
         Index           =   14
         Left            =   4080
         TabIndex        =   23
         Top             =   1140
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "September"
         Height          =   270
         Index           =   13
         Left            =   2625
         TabIndex        =   22
         Top             =   1110
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "August"
         Height          =   270
         Index           =   12
         Left            =   1425
         TabIndex        =   21
         Top             =   1110
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "July"
         Height          =   285
         Index           =   11
         Left            =   210
         TabIndex        =   20
         Top             =   1095
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "June"
         Height          =   270
         Index           =   10
         Left            =   6510
         TabIndex        =   19
         Top             =   360
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "May"
         Height          =   270
         Index           =   9
         Left            =   5295
         TabIndex        =   18
         Top             =   360
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "April"
         Height          =   270
         Index           =   8
         Left            =   4080
         TabIndex        =   17
         Top             =   360
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "March"
         Height          =   270
         Index           =   7
         Left            =   2625
         TabIndex        =   16
         Top             =   330
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "February"
         Height          =   270
         Index           =   6
         Left            =   1425
         TabIndex        =   15
         Top             =   330
         Width           =   930
      End
      Begin VB.Label lbl_labels 
         Alignment       =   2  'Center
         Caption         =   "January"
         Height          =   270
         Index           =   5
         Left            =   210
         TabIndex        =   14
         Top             =   330
         Width           =   930
      End
   End
   Begin VB.Frame frm_filters 
      Height          =   600
      Left            =   270
      TabIndex        =   2
      Top             =   825
      Width           =   15480
      Begin Project1.ArmCombobox cbo_dataType 
         Height          =   345
         Left            =   2790
         TabIndex        =   4
         Top             =   180
         Width           =   1065
         _ExtentX        =   1879
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_valueType 
         Height          =   345
         Left            =   5100
         TabIndex        =   6
         Top             =   180
         Width           =   960
         _ExtentX        =   1693
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_MG_Code 
         Height          =   345
         Left            =   7500
         TabIndex        =   8
         Top             =   180
         Width           =   1695
         _ExtentX        =   2990
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_BUG_Code 
         Height          =   345
         Left            =   10470
         TabIndex        =   10
         Top             =   180
         Width           =   1695
         _ExtentX        =   2990
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_BMK_Code 
         Height          =   345
         Left            =   13095
         TabIndex        =   12
         Top             =   180
         Width           =   2295
         _ExtentX        =   4048
         _ExtentY        =   609
      End
      Begin Project1.ArmCombobox cbo_Year 
         Height          =   345
         Left            =   615
         TabIndex        =   57
         Top             =   180
         Width           =   1065
         _ExtentX        =   1879
         _ExtentY        =   609
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Year"
         Height          =   270
         Index           =   26
         Left            =   120
         TabIndex        =   56
         Top             =   240
         Width           =   600
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Market"
         Height          =   270
         Index           =   4
         Left            =   12375
         TabIndex        =   11
         Top             =   240
         Width           =   675
      End
      Begin VB.Label lbl_labels 
         Caption         =   "BU group"
         Height          =   270
         Index           =   3
         Left            =   9555
         TabIndex        =   9
         Top             =   240
         Width           =   855
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Material group"
         Height          =   270
         Index           =   2
         Left            =   6345
         TabIndex        =   7
         Top             =   240
         Width           =   1110
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Value type"
         Height          =   270
         Index           =   1
         Left            =   4155
         TabIndex        =   5
         Top             =   240
         Width           =   870
      End
      Begin VB.Label lbl_labels 
         Caption         =   "Data type"
         Height          =   270
         Index           =   0
         Left            =   1890
         TabIndex        =   3
         Top             =   240
         Width           =   855
      End
   End
   Begin Project1.ArmGrid grd_lst 
      Height          =   3510
      Left            =   195
      TabIndex        =   1
      Top             =   1590
      Width           =   14730
      _ExtentX        =   25982
      _ExtentY        =   6191
   End
   Begin Project1.ToolbarControl tlb_main 
      Height          =   690
      Left            =   255
      TabIndex        =   0
      Top             =   120
      Width           =   14835
      _ExtentX        =   26167
      _ExtentY        =   1217
   End
End
Attribute VB_Name = "ONSV_baselines"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

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

Private Const SCREEN_NAME As String = "ONSVBaselines"
Private Const SEP = "®©"
Private Const SEP1 As String = "®"
Private Const SEP2 As String = "©"

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 Const CL_COLOR_ENABLED As Long = &H80000005
Private Const CL_COLOR_DISABLED As Long = &H8000000F

Private mb_EventRunning As Boolean
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal _
    hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
    lParam As Any) As Long
Const EM_LINEFROMCHAR = &HC9

'Const C_ID_COL As Long = 1
Const C_BMK_CODE_COL As Long = 1
'Const C_BUG_CODE_COL As Long = 2
Const C_DATA_YEAR_COL As Long = 1
Const C_DATA_VERSION_COL As Long = 1
Const C_DATA_TYPE_COL As Long = 2
Const C_VALUE_TYPE_COL As Long = 2
Const C_MG_CODE_COL As Long = 2
Const C_MONTH_JAN_COL As Long = 3               ' Start of month section


Private mb_Initialized As Boolean
Private ms_inTranName As String
Private mo_Db As ArmDb
Private ms_Language_Code As String
Dim mu_ActiveMode As ArmScreenMode

Private ml_U_Code As Long

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


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 ' Repaint immediately
        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


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 Load_A_Com()
    
On Error GoTo ErrHandler

    If mb_Initialized Then Exit Sub
    
    mb_Initialized = True
    
    mb_EventRunning = True
    
    ms_inTranName = ""
    
    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
    
    dlg_fileopen.Filter = "2005 Excell files (*.xlsx)|*.xlsx|Excell files (*.xls)|*.xls"

    ' 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 = "Media Blob Config folders"
    grd_lst.MultiSelect = False
    
    
    If Not grd_lst.SetColumns(Array( _
                Join(Array("Record_ID", 0, 1, "Record_ID", "#"), SEP) _
                , Join(Array("Data_Year", 700, 0, "Data_Year", "Year"), SEP) _
                , Join(Array("BU_Code", 0, 0, "BU_Code", "Name"), SEP) _
                , Join(Array("BU_Desc", 2200, 0, "BU_Desc", "BU"), SEP) _
                , Join(Array("Data_Type", 700, 0, "Data_Type", "Data Type"), SEP) _
                , Join(Array("Value_Type", 700, 0, "Value_Type", "Value Type", "String"), SEP) _
                , Join(Array("MG_Code", 0, 0, "MG_Code", "MG_Code", "String"), SEP) _
                , Join(Array("MG_Desc", 2200, 0, "MG_Desc", "Material Group", "String"), SEP) _
                , Join(Array("BUG_Code", 0, 0, "BUG_Code", "BUG_Code", "String"), SEP) _
                , Join(Array("BUG_Desc", 2200, 0, "BUG_Desc", "BU Geography", "String"), SEP) _
                , Join(Array("BMK_Code", 0, 0, "BMK_Code", "BMK_Code", "String"), SEP) _
                , Join(Array("BMK_Desc", 3200, 0, "BMK_Desc", "Market", "String"), SEP) _
                , Join(Array("Data_quarter", 600, 0, "Data_quarter", "Quarter", "String"), SEP) _
                , Join(Array("Data_Month", 700, 0, "Data_Month", "Month", "String"), SEP) _
                , Join(Array("Data_version", 400, 0, "Data_version", "Ver.", "String"), SEP) _
                , Join(Array("BMK_Code_NextLevel", 600, 0, "BMK_Code_NextLevel", "BMK_Code_NextLevel", "String"), SEP) _
                )) Then
        MsgBox ("Grid not initialized!")
    End If
    
    cbo_year.FirstBlankItem = False
    cbo_year.Request = "SELECT DISTINCT Data_Year, Data_Year FROM ONSV_Baselines ORDER BY 1"
    cbo_year.Load
    Set cbo_year.SelectedItem = cbo_year.ComboItems(cbo_year.Count)
    
    cbo_dataType.FirstBlankItem = True
    cbo_dataType.AddItem (Array("BDG", "BDG"))
    cbo_dataType.AddItem (Array("FCT", "FCT"))
    cbo_dataType.AddItem (Array("EST", "EST"))
    
    cbo_dDataType.FirstBlankItem = False
    cbo_dDataType.AddItem (Array("BDG", "BDG"))
    cbo_dDataType.AddItem (Array("FCT", "FCT"))
    cbo_dDataType.AddItem (Array("EST", "EST"))
    
    cbo_valueType.FirstBlankItem = True
    cbo_valueType.AddItem (Array("QTY", "QTY"))
    cbo_valueType.AddItem (Array("NSV", "NSV"))
    cbo_valueType.AddItem (Array("GSV", "GSV"))
    
    cbo_dValueType.FirstBlankItem = False
    cbo_dValueType.AddItem (Array("QTY", "QTY"))
    cbo_dValueType.AddItem (Array("NSV", "NSV"))
    cbo_dValueType.AddItem (Array("GSV", "GSV"))
    
    cbo_dDataYear.FirstBlankItem = False
    cbo_dDataYear.Request = "SELECT DISTINCT YY AS Data_Year, YY FROM [dbo].[Calendar] ORDER BY 1"
    
    cbo_dDataMonth.FirstBlankItem = False
    cbo_dDataMonth.AddItem (Array("NA", "Not asigned"))
    cbo_dDataMonth.AddItem (Array("01", "January"))
    cbo_dDataMonth.AddItem (Array("02", "February"))
    cbo_dDataMonth.AddItem (Array("03", "March"))
    cbo_dDataMonth.AddItem (Array("04", "April"))
    cbo_dDataMonth.AddItem (Array("05", "May"))
    cbo_dDataMonth.AddItem (Array("06", "June"))
    cbo_dDataMonth.AddItem (Array("07", "July"))
    cbo_dDataMonth.AddItem (Array("08", "August"))
    cbo_dDataMonth.AddItem (Array("09", "September"))
    cbo_dDataMonth.AddItem (Array("10", "October"))
    cbo_dDataMonth.AddItem (Array("11", "November"))
    cbo_dDataMonth.AddItem (Array("12", "December"))
    
    cbo_dDataQuarter.FirstBlankItem = False
    cbo_dDataQuarter.AddItem (Array("NA", "Not asigned"))
    cbo_dDataQuarter.AddItem (Array("01", "First quarter"))
    cbo_dDataQuarter.AddItem (Array("02", "Second quarter"))
    cbo_dDataQuarter.AddItem (Array("03", "Third quarter"))
    cbo_dDataQuarter.AddItem (Array("04", "Fourth quarter"))
    
    
    cbo_MG_Code.FirstBlankItem = True
    cbo_MG_Code.Request = "select MG_Code, MG_Desc from [dbo].[ONSV_Material_Group] WHERE Language_code='E' ORDER BY Desc_Order"
    
    cbo_dMG_Code.FirstBlankItem = False
    cbo_dMG_Code.Request = "select MG_Code, MG_Desc from [dbo].[ONSV_Material_Group] WHERE Language_code='E' ORDER BY Desc_Order"
    
    cbo_BMK_Code.FirstBlankItem = True
    
    cbo_dBMK_Code.FirstBlankItem = False
    cbo_dBMK_Code.Request = "EXEC Business_Market2_cbo"
    
    cbo_BUG_Code.FirstBlankItem = True
    cbo_BUG_Code.Request = "select BUG_Code, BUG_Desc from [dbo].[ONSV_BU_Geography] WHERE Language_code='E' ORDER BY bug_Order"
    
    cbo_dBUG_Code.FirstBlankItem = False
    cbo_dBUG_Code.Request = "select BUG_Code, BUG_Desc from [dbo].[ONSV_BU_Geography] WHERE Language_code='E' ORDER BY bug_Order"
    
    Set cbo_BUG_Code.SelectedItem = cbo_BUG_Code.ComboItems(1)
    
    Call cbo_BUG_Code_ComboItemSelected
    
    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(txt_recordID, False)
            Call EnableControl(cbo_dDataYear, False)
            Call EnableControl(cbo_dDataType, False)
            Call EnableControl(cbo_dValueType, False)
            Call EnableControl(cbo_dMG_Code, False)
            Call EnableControl(cbo_dBUG_Code, False)
            Call EnableControl(cbo_dBMK_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 EnableControl(txt_recordID, False)
            
            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


' 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

' 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

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(Extender.Name & ".GetDbError()")
End Function


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

Public Sub Resize()
    Call InitCtrlSize
End Sub

Private Sub InitCtrlSize()
On Error GoTo ErrHandler
    If UserControl.Extender.Width < 13300 Then
        Exit Sub
    End If
    
    If UserControl.Extender.Height < 5500 Then
        Exit Sub
    End If
    
    Const SPACE As Long = 40
    Call tlb_Main.Move(SPACE, 0, UserControl.Extender.Width - 2 * SPACE - 100, 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 - 2 * SPACE - frm_detail.Height - 450
    
    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

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(Extender.Name & ".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(Extender.Name & ".ExecuteSQLSafe")
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(Extender.Name & ".SetEnabled()")
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_baselines 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

' 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 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


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 "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 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 "BU_Code"
        Case "BU_Desc"
'            GetDataSrcForGrid = txt_WMB_Type.Text
        Case "Data_Type"
        Case "Data_Year"
        Case "Data_Month"
        Case "Data_version"
        Case "Data_quarter"
        Case "Value_Type"
        Case "MG_Code"
        Case "MG_Desc"
        Case "BUG_Code"
        Case "BUG_Desc"
        Case "BMK_Code"
        Case "BMK_Desc"
        Case "BMK_Code_NextLevel"

    End Select
    
    Exit Function
ErrHandler:
     Call ErrorHandler("GetDataSrcForGrid()")
End Function



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

Private Sub cbo_BUG_Code_ComboItemSelected()
On Error GoTo ErrHandler
    If mb_EventRunning Then Exit Sub
    Set cbo_BMK_Code.SelectedItem = Nothing
    cbo_BMK_Code.Request = Replace("SELECT BM.BMK_Code, BM.BMK_Desc FROM Business_Market2 BM INNER JOIN ONSV_BU_Geography_To_BusinessMarkets2 L ON L.BMK_Code=BM.BMK_Code WHERE (L.BUG_Code=$BUG_CODE$ OR $BUG_CODE$ IS NULL) AND (BM.Language_code = 'E') ORDER BY 2", "$BUG_CODE$", SqlStr(GetCboKey(cbo_BUG_Code), 4, True))
    
    Call RefreshGrid
    Exit Sub
ErrHandler:
     Call ErrorMessage("cbo_BUG_Code_ComboItemSelected()")
End Sub

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 Sub RefreshGrid()
On Error GoTo ErrHandler
Const REQ As String = "EXEC ONSV_Baselines_lst $DATA_TYPE$, $DATA_YEAR$, $VALUE_TYPE$, $MG_CODE$, $BUG_CODE$, $BMK_CODE$"
    Dim ls_Req As String
    
    ls_Req = Replace(REQ, "$DATA_TYPE$", SqlStr(GetCboKey(cbo_dataType), 5, True), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$DATA_YEAR$", cbo_year.Text, , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$VALUE_TYPE$", SqlStr(GetCboKey(cbo_valueType), 3, True), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$MG_CODE$", SqlStr(GetCboKey(cbo_MG_Code), 4, True), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$BUG_CODE$", SqlStr(GetCboKey(cbo_BUG_Code), 4, True), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$BMK_CODE$", SqlStr(GetCboKey(cbo_BMK_Code), 4, True), , , vbTextCompare)
    
    If Not grd_lst.Load(ls_Req, True) Then
        MsgBox ("Grid not loaded (ONSV_Baselines_lst)!")
    End If
    
    Call Item_Clear
    
    Exit Sub
ErrHandler:
     Call ErrorHandler("RefreshGrid()")
End Sub

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

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

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


Private Sub cbo_Year_ComboItemSelected()
On Error GoTo ErrHandler
    If mb_EventRunning Then Exit Sub
    Call RefreshGrid
    Exit Sub
ErrHandler:
     Call ErrorMessage("cbo_Year_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_InitView(grd_lst.SelectedKey(0)(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_InitAdd
    Case "B", "I"       ' Update, refresh detail
        If mu_ActiveMode = smAdd Then
            Call Item_InitAdd
        ElseIf (mu_ActiveMode = smUpdate) Or (mu_ActiveMode = smMain) Then
            If grd_lst.SelectedCount > 0 Then
                Call Item_InitUpdate(grd_lst.SelectedKey(0)(0))
            Else
                Call MsgBox("Select a row please!")
            End If
        End If
    Case "C"
        If grd_lst.SelectedCount > 0 Then
            Call Item_InitDelete(grd_lst.SelectedKey(0)(0))
        Else
            Call MsgBox("Select a row please!")
        End If
    Case "L"        ' refresh grid
        cbo_year.Clear
        cbo_year.Load
        Set cbo_year.SelectedItem = cbo_year.ComboItems(cbo_year.Count)
        
        Call RefreshGrid
    Case "H"
        If mu_ActiveMode = smAdd Then
            If Item_Add Then
                UpdateUI (smMain)
            End If
        ElseIf mu_ActiveMode = smUpdate Then
            If Item_Update Then
                UpdateUI (smMain)
            End If
        ElseIf mu_ActiveMode = smDelete Then
            If Item_Delete Then
                UpdateUI (smMain)
            End If
        End If
    Case "T"
        If mu_ActiveMode = smMain Then
            RaiseEvent quit
        Else
            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 Sub Item_InitAdd()
On Error GoTo ErrHandler
    Call Item_Clear
    Call UpdateUI(smAdd)
    Call Item_LoadDefaultValues
    Exit Sub
ErrHandler:
    Call ErrorMessage("Item_InitAdd")
End Sub

Private Sub Item_InitDelete(ByVal al_recordID As Long)
On Error GoTo ErrHandler
    Call Item_Clear
    Call UpdateUI(smDelete)
    Dim ll_Cursor As Long
    ll_Cursor = Item_LoadDB(al_recordID)
    Call Item_Load(ll_Cursor)
    Call mo_Db.Close(ll_Cursor)
    ll_Cursor = 0
    Exit Sub
ErrHandler:
    If ll_Cursor <> 0 Then
        Call mo_Db.Close(ll_Cursor)
        ll_Cursor = 0
    End If
    Call ErrorMessage("Item_InitDelete")
End Sub

Private Sub Item_InitView(ByVal al_recordID As Long)
On Error GoTo ErrHandler
    Call Item_Clear
    Call UpdateUI(smMain)
    Dim ll_Cursor As Long
    ll_Cursor = Item_LoadDB(al_recordID)
    Call Item_Load(ll_Cursor)
    Call mo_Db.Close(ll_Cursor)
    ll_Cursor = 0
    Exit Sub
ErrHandler:
    If ll_Cursor <> 0 Then
        Call mo_Db.Close(ll_Cursor)
        ll_Cursor = 0
    End If
    Call ErrorMessage("Item_InitView")
End Sub

Private Sub Item_InitUpdate(ByVal al_recordID As Long)
On Error GoTo ErrHandler
    Call Item_Clear
    Call UpdateUI(smUpdate)
    Dim ll_Cursor As Long
    ll_Cursor = Item_LoadDB(al_recordID)
    Call Item_Load(ll_Cursor)
    Call mo_Db.Close(ll_Cursor)
    ll_Cursor = 0
    Exit Sub
ErrHandler:
    If ll_Cursor <> 0 Then
        Call mo_Db.Close(ll_Cursor)
        ll_Cursor = 0
    End If
    Call ErrorMessage("Item_InitUpdate")
End Sub

Private Function Item_Update() As Boolean
On Error GoTo ErrHandler
    Item_Update = False
    If Not Item_Check() Then
        Exit Function
    End If
    
    Call Item_UpdateDB(CLng(txt_recordID.Text))
    
    Call grd_lst.Refresh
'    Call UpdateGridAfterAction(grd_lst, "Upd", CLng(txt_WMB_Type.Text))
    
    Item_Update = True
    Exit Function
ErrHandler:
    Call ErrorMessage("Item_Update")
End Function

Private Function Item_Delete() As Boolean
On Error GoTo ErrHandler
    Item_Delete = False
    If MsgBox("Do you want to delete the record?", vbYesNo) <> vbYes Then
        Exit Function
    End If
    
    Call Item_DeleteDB(CLng(txt_recordID.Text))
    
    Call grd_lst.Refresh
    
    Item_Delete = True
    Exit Function
ErrHandler:
    Call ErrorMessage("Item_Delete")
End Function

Private Function Item_Add() As Boolean
On Error GoTo ErrHandler
    Item_Add = False
    If Not Item_Check() Then
        Exit Function
    End If
    
    Call Item_AddDB(CLng(GetNextRecordID()))
    
    Call grd_lst.Refresh
'    Call UpdateGridAfterAction(grd_lst, "Upd", CLng(txt_WMB_Type.Text))
    
    Item_Add = True
    Exit Function
ErrHandler:
    Call ErrorMessage("Item_Add")
End Function

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

    If (Trim(txt_values(0).Text) <> "") And Not isNumeric(txt_values(0).Text) Then
        Call MsgBox("Wrong January number!")
        Call txt_values(0).SetFocus
        Exit Function
    End If
    
    If (Trim(txt_values(1).Text) <> "") And Not isNumeric(txt_values(1).Text) Then
        Call MsgBox("Wrong February number!")
        Call txt_values(1).SetFocus
        Exit Function
    End If
    
    If (Trim(txt_values(2).Text) <> "") And Not isNumeric(txt_values(2).Text) Then
        Call MsgBox("Wrong March number!")
        Call txt_values(2).SetFocus
        Exit Function
    End If

    If (Trim(txt_values(3).Text) <> "") And Not isNumeric(txt_values(3).Text) Then
        Call MsgBox("Wrong April number!")
        Call txt_values(3).SetFocus
        Exit Function
    End If

    If (Trim(txt_values(4).Text) <> "") And Not isNumeric(txt_values(4).Text) Then
        Call MsgBox("Wrong May number!")
        Call txt_values(4).SetFocus
        Exit Function
    End If
    
    If (Trim(txt_values(5).Text) <> "") And Not isNumeric(txt_values(5).Text) Then
        Call MsgBox("Wrong June number!")
        Call txt_values(5).SetFocus
        Exit Function
    End If
    
    If (Trim(txt_values(6).Text) <> "") And Not isNumeric(txt_values(6).Text) Then
        Call MsgBox("Wrong July number!")
        Call txt_values(6).SetFocus
        Exit Function
    End If

    If (Trim(txt_values(7).Text) <> "") And Not isNumeric(txt_values(7).Text) Then
        Call MsgBox("Wrong August number!")
        Call txt_values(7).SetFocus
        Exit Function
    End If

    If (Trim(txt_values(8).Text) <> "") And Not isNumeric(txt_values(8).Text) Then
        Call MsgBox("Wrong Semptember number!")
        Call txt_values(8).SetFocus
        Exit Function
    End If

    If (Trim(txt_values(9).Text) <> "") And Not isNumeric(txt_values(9).Text) Then
        Call MsgBox("Wrong October number!")
        Call txt_values(9).SetFocus
        Exit Function
    End If

    If (Trim(txt_values(10).Text) <> "") And Not isNumeric(txt_values(10).Text) Then
        Call MsgBox("Wrong November number!")
        Call txt_values(10).SetFocus
        Exit Function
    End If

    If Not isNumeric(txt_dataVersion.Text) Then
        Call MsgBox("Wrong Period!")
        Call txt_dataVersion.SetFocus
        Exit Function
    End If
    
    If (Trim(txt_values(11).Text) <> "") And Not isNumeric(txt_values(11).Text) Then
        Call MsgBox("Wrong December number!")
        Call txt_values(11).SetFocus
        Exit Function
    End If

    
    If GetCboKey(cbo_dDataYear) = "" Then
        Call MsgBox("Missing Year!")
        Call cbo_dDataYear.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_dDataType) = "" Then
        Call MsgBox("Missing Data Type!")
        Call cbo_dDataType.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_dValueType) = "" Then
        Call MsgBox("Missing Value Type!")
        Call cbo_dValueType.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_dMG_Code) = "" Then
        Call MsgBox("Missing Material Group!")
        Call cbo_dMG_Code.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_dDataMonth) = "" Then
        Call MsgBox("Missing Data Month!")
        Call cbo_dDataMonth.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_dBUG_Code) = "" Then
        Call MsgBox("Missing BU Geography!")
        Call cbo_dBUG_Code.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_dDataQuarter) = "" Then
        Call MsgBox("Missing Data Quarter!")
        Call cbo_dDataQuarter.SetFocus
        Exit Function
    End If
    
    If GetCboKey(cbo_dBMK_Code) = "" Then
        Call MsgBox("Missing Market!")
        Call cbo_dBMK_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_Load()")
End Sub

Private Sub Item_LoadDefaultValues()
On Error GoTo ErrHandler

    txt_recordID.Text = "NEW"
    Call cbo_dDataYear.SearchItem(GetCboKey(cbo_year))
    Call cbo_dDataType.SearchItem(GetCboKey(cbo_dataType))
    Call cbo_dValueType.SearchItem(GetCboKey(cbo_valueType))
    Call cbo_dMG_Code.SearchItem(GetCboKey(cbo_MG_Code))
    Call cbo_dDataMonth.SearchItem("NA")
    Call cbo_dDataQuarter.SearchItem("NA")
    Call cbo_dBUG_Code.SearchItem(GetCboKey(cbo_BUG_Code))
    Call cbo_dBMK_Code.SearchItem(GetCboKey(cbo_BMK_Code))
    txt_dataVersion.Text = "1"
    
    Exit Sub
ErrHandler:
    Call ErrorHandler("Item_LoadDefaultValues()")
End Sub

Private Function Item_LoadDB(ByVal al_record_ID As Long) As Long
On Error GoTo ErrHandler

Const C_REQ = "EXEC ONSV_Baselines_Values_sel $RECORD_ID$"
    Dim ll_retVal As Long
    ll_retVal = OpenSQLSafe(mo_Db, Replace(C_REQ, "$RECORD_ID$", al_record_ID))
    txt_recordID.Text = al_record_ID
    Item_LoadDB = ll_retVal
    Exit Function
ErrHandler:
    Call ErrorHandler("Item_LoadDB()")
End Function


Private Sub Item_UpdateDB(ByVal al_record_ID As Long)
On Error GoTo ErrHandler
    Dim ls_Req As String

Const CREQ = "EXEC ONSV_Baselines_upd $Record_ID$, $Data_Year$, $Data_Type$, $Value_type$, $MG_code$, $BUG_code$, $BMK_code$, $BMK_code_NextLevel$, $Data_Month$, $data_quarter$, $Data_version$"
    
    ' common placeholders
    ls_Req = Replace(CREQ, "$Record_ID$", al_record_ID, , , vbTextCompare)
    ls_Req = Item_ReplacePlaceholders(ls_Req)
    
    Call BeginTran("ONSV_BASELINES")
    
    Call ExecuteSQLSafe(mo_Db, ls_Req, 1)
    
    ' update values
    Call Item_UpdateValueDB(al_record_ID, 0)
    Call Item_UpdateValueDB(al_record_ID, 1)
    Call Item_UpdateValueDB(al_record_ID, 2)
    Call Item_UpdateValueDB(al_record_ID, 3)
    Call Item_UpdateValueDB(al_record_ID, 4)
    Call Item_UpdateValueDB(al_record_ID, 5)
    Call Item_UpdateValueDB(al_record_ID, 6)
    Call Item_UpdateValueDB(al_record_ID, 7)
    Call Item_UpdateValueDB(al_record_ID, 8)
    Call Item_UpdateValueDB(al_record_ID, 9)
    Call Item_UpdateValueDB(al_record_ID, 10)
    Call Item_UpdateValueDB(al_record_ID, 11)
    
    Call CommitTran("ONSV_BASELINES")
    
    Exit Sub
ErrHandler:
    Call RollbackTran("ONSV_BASELINES")
    Call ErrorHandler(Extender.Name & ".Item_UpdateDb")
End Sub

Private Sub Item_DeleteDB(ByVal al_record_ID As Long)
On Error GoTo ErrHandler
    Dim ls_Req As String
    Dim ls_Req_v As String

Const CREQ = "EXEC ONSV_Baselines_del $Record_ID$"
Const CREQ_v = "EXEC ONSV_Baselines_Values_del $Record_ID$, NULL"
    
    ' common placeholders
    ls_Req = Replace(CREQ, "$Record_ID$", al_record_ID, , , vbTextCompare)
    ls_Req_v = Replace(CREQ_v, "$Record_ID$", al_record_ID, , , vbTextCompare)
    
    Call BeginTran("ONSV_BASELINES")
    
    Call ExecuteSQLSafe(mo_Db, ls_Req_v)
    Call ExecuteSQLSafe(mo_Db, ls_Req, 1)
    
    Call CommitTran("ONSV_BASELINES")
    
    Exit Sub
ErrHandler:
    Call RollbackTran("ONSV_BASELINES")
    Call ErrorHandler(Extender.Name & ".Item_DeleteDB")
End Sub

Private Sub Item_AddDB(ByVal al_record_ID As Long)
On Error GoTo ErrHandler
    Dim ls_Req As String

Const CREQ = "EXEC ONSV_Baselines_ins $Record_ID$, 'B001', $Data_Year$, $Data_Type$, $Value_type$, $MG_code$, $BUG_code$, $BMK_code$, $BMK_code_NextLevel$, $Data_Month$, $data_quarter$, $Data_version$"
    
    ' common placeholders
    ls_Req = Replace(CREQ, "$Record_ID$", al_record_ID, , , vbTextCompare)
    ls_Req = Item_ReplacePlaceholders(ls_Req)
    
    Call BeginTran("ONSV_BASELINES")
    
    Call ExecuteSQLSafe(mo_Db, ls_Req, 1)
    
    ' update values
    Call Item_UpdateValueDB(al_record_ID, 0)
    Call Item_UpdateValueDB(al_record_ID, 1)
    Call Item_UpdateValueDB(al_record_ID, 2)
    Call Item_UpdateValueDB(al_record_ID, 3)
    Call Item_UpdateValueDB(al_record_ID, 4)
    Call Item_UpdateValueDB(al_record_ID, 5)
    Call Item_UpdateValueDB(al_record_ID, 6)
    Call Item_UpdateValueDB(al_record_ID, 7)
    Call Item_UpdateValueDB(al_record_ID, 8)
    Call Item_UpdateValueDB(al_record_ID, 9)
    Call Item_UpdateValueDB(al_record_ID, 10)
    Call Item_UpdateValueDB(al_record_ID, 11)
    
    Call CommitTran("ONSV_BASELINES")
    
    Exit Sub
ErrHandler:
    Call RollbackTran("ONSV_BASELINES")
    Call ErrorHandler(Extender.Name & ".Item_AddDB")
End Sub

Private Sub Item_UpdateValueDB(ByVal al_record_ID As Long, ByVal al_valueIndex As Long)
On Error GoTo ErrHandler
    Dim ls_Req As String

Const CREQ_AV = "EXEC ONSV_Baselines_Values_ins $Record_ID$, $Month$, $Value$"
Const CREQ_DV = "EXEC ONSV_Baselines_Values_del $Record_ID$, $Month$"
    
    ls_Req = Replace(CREQ_DV, "$Record_ID$", al_record_ID, , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$Month$", SqlStr(Format(al_valueIndex + 1, "0#")), , , vbTextCompare)
    ls_Req = Item_ReplacePlaceholders(ls_Req)
    Call ExecuteSQLSafe(mo_Db, ls_Req, -1)
    
    If Trim(txt_values(al_valueIndex).Text) <> "" Then
        ls_Req = Replace(CREQ_AV, "$Record_ID$", al_record_ID, , , vbTextCompare)
        ls_Req = Replace(ls_Req, "$Month$", SqlStr(Format(al_valueIndex + 1, "0#")), , , vbTextCompare)
        ls_Req = Replace(ls_Req, "$Value$", SQLNum(txt_values(al_valueIndex).Text), , , vbTextCompare)
        Call ExecuteSQLSafe(mo_Db, ls_Req, 1)
    End If
    
    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_UpdateValueDB")
End Sub

Private Function Item_ReplacePlaceholders(ls_retVal As String) As String
On Error GoTo ErrHandler
    ls_retVal = Replace(ls_retVal, "$Data_Year$", GetCboKey(cbo_dDataYear), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$Data_Type$", SqlStr(GetCboKey(cbo_dDataType)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$Value_type$", SqlStr(GetCboKey(cbo_dValueType)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$MG_code$", SqlStr(GetCboKey(cbo_dMG_Code)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$BUG_code$", SqlStr(GetCboKey(cbo_dBUG_Code)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$BMK_code$", SqlStr(GetCboKey(cbo_dBMK_Code)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$BMK_code_NextLevel$", SqlStr(GetCboKey(cbo_dBMK_Code)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$Data_Month$", SqlStr(GetCboKey(cbo_dDataMonth)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$data_quarter$", SqlStr(GetCboKey(cbo_dDataQuarter)), , , vbTextCompare)
    ls_retVal = Replace(ls_retVal, "$Data_version$", SQLNum(txt_dataVersion.Text), , , vbTextCompare)
    
    Item_ReplacePlaceholders = ls_retVal
    Exit Function
ErrHandler:
    Call ErrorHandler(Extender.Name & ".Item_ReplacePlaceholders")
End Function

Private Sub Item_Load(ByVal al_Cursor As Long)
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"
                If Not lo_control.SearchItem(mo_Db.GetFields(al_Cursor, lo_control.Tag)) Then
                    Call lo_control.Load
                    lo_control.SearchItem (mo_Db.GetFields(al_Cursor, lo_control.Tag))
                End If
            Case "ARMPICKER"
            Case "TOOLBARCONTROL"
            Case "ARMGRID"
            Case "ARMTREEVIEW"
            Case "ARMCHECKVIEW"
            Case "A_CALOCX"
            Case "TOOLBR"
            Case "TEXTBOX"
                lo_control.Text = mo_Db.GetFields(al_Cursor, lo_control.Tag)
            Case "CHECKBOX"
                lo_control.Value = IIf(mo_Db.GetFields(al_Cursor, lo_control.Tag) = "", vbUnchecked, vbChecked)
            End Select
        End If
    Next
    
    ' CUSTOM UPDATES to supress 0 value when record do not exists
    For Each lo_control In txt_values
        If mo_Db.GetFields(al_Cursor, Replace(lo_control.Tag, "Value", "ID")) = "" Then
            Debug.Assert (lo_control.Text = "0")
            lo_control.Text = ""
        End If
    Next
    Exit Sub
ErrHandler:
    Call ErrorHandler("Item_Load()")
End Sub

Private Sub Item_Load_E(ByRef ao_workSheet As Object, ByVal al_Row As Long, ByVal al_recordID As Long, ByVal al_dataYear As Long, ByVal as_DataType As String, ByVal as_MG_Code As String, ByVal as_value_type As String, ByVal as_Data_Version As String, ByVal as_BUG_Code As String)
On Error GoTo ErrHandler
    
    If Not SetComboItemValue(cbo_dDataYear, al_dataYear) Then
        Call MsgBox("Invalid value for year:" & al_dataYear)
    End If
    
    If Not SetComboItemValue(cbo_dDataType, as_DataType) Then
        Call MsgBox("Invalid value for data type:" & as_DataType)
    End If
    
    If Not SetComboItemValue(cbo_dDataMonth, Format(Month(Now), "0#")) Then
        Call MsgBox("Invalid value for data month:" & Format(Month(Now), "0#"))
    End If
    
    If Not SetComboItemValue(cbo_dDataQuarter, Format(CLng((Month(Now) / 4)) + 1, "0#")) Then
        Call MsgBox("Invalid value for data quarter:" & Format(CLng((Month(Now) / 4)) + 1, "0#"))
    End If
    
    txt_dataVersion.Text = as_Data_Version
    
    If Not SetComboItemValue(cbo_dMG_Code, as_MG_Code) Then
        Call MsgBox("Invalid value for Material group:" & as_MG_Code)
    End If
    
    If Not SetComboItemValue(cbo_dBUG_Code, as_BUG_Code) Then
        Call MsgBox("Invalid value for BUG_Code:" & as_BUG_Code)
    End If
    
    If Not SetComboItemValue(cbo_dBMK_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
    
    If Not SetComboItemValue(cbo_dValueType, as_value_type) Then
        Call MsgBox("Invalid value for Value type:" & as_value_type)
    End If
    
    txt_recordID.Text = al_recordID
    
    Dim lo_control As TextBox
    For Each lo_control In txt_values
        lo_control.Text = Round(ao_workSheet.cells(al_Row, C_MONTH_JAN_COL - 1 + Replace(lo_control.Tag, "Month_Value", "")), 0) * 1000
    Next
    Exit Sub
ErrHandler:
    Call ErrorHandler("Item_Load_E()")
End Sub

Private Function BeginTran(as_Tran As String) As Boolean

On Error GoTo ErrHandler
    BeginTran = False
    
    If ms_inTranName <> "" Then
        MsgBox ("Already in transaction!!!!! " & ms_inTranName)
        Exit Function
    End If
    
    ExecuteSQLSafe mo_Db, "BEGIN TRANSACTION " & as_Tran
    
    ms_inTranName = as_Tran

    BeginTran = True
    Exit Function
    
ErrHandler:
    'try to log error
'    Call LogMessage("BeginTran: " & as_Tran)
    Call mo_Db.Disconnect
    Set mo_Db = Nothing
    MsgBox "A Fatal error occured in " & SCREEN_NAME & ".BeginTran, your application will be close. Please contact your IT support", , App.Title & " " & App.Major & "." & App.Minor & "." & App.Revision
    End
End Function

Private Function CommitTran(as_Tran As String) As Boolean

On Error GoTo ErrHandler
    CommitTran = False
    
    If ms_inTranName <> as_Tran Then
        Call MsgBox("Warning!!! Commit transaction name does not match!!! " & as_Tran & "<>" & ms_inTranName, vbCritical)
    End If
    
    ExecuteSQLSafe mo_Db, "COMMIT TRANSACTION " & as_Tran

    ms_inTranName = ""
    CommitTran = True
    Exit Function
    
ErrHandler:
    'try to log error
'    Call LogMessage("CommitTran: " & as_Tran)
    Call mo_Db.Disconnect
    Set mo_Db = Nothing
    MsgBox "A Fatal error occured in " & SCREEN_NAME & ".CommitTran, your application will be close. Please contact your IT support", , App.Title & " " & App.Major & "." & App.Minor & "." & App.Revision
    End

End Function

Private Function RollbackTran(as_Tran As String) As Boolean
    
    Dim ll_errNumber As Long, ls_ErrSource As String, ls_ErrDesc As String
    ll_errNumber = Err.Number
    ls_ErrSource = Err.Source
    ls_ErrDesc = Err.Description

On Error GoTo ErrHandler
    RollbackTran = False
    
    If ms_inTranName <> as_Tran Then
        Call MsgBox("Warning!!! Rollback transaction name does not match!!! " & as_Tran & "<>" & ms_inTranName, vbCritical)
    End If
    
    ExecuteSQLSafe mo_Db, "ROLLBACK TRANSACTION " & as_Tran
    ms_inTranName = ""


    Err.Number = ll_errNumber
    Err.Source = ls_ErrSource
    Err.Description = ls_ErrDesc

    RollbackTran = True
    Exit Function
    
ErrHandler:
    'try to log error
'    Call LogMessage("RollbackTran: " & as_Tran)
    Call mo_Db.Disconnect
    Set mo_Db = Nothing
    MsgBox "A Fatal error occured in " & SCREEN_NAME & ".RollbackTran, your application will be close. Please contact your IT support", , App.Title & " " & App.Major & "." & App.Minor & "." & App.Revision
    End
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_ItemPicker
    
    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_Version As String
    Dim ls_DataType As String
    lb_sheetFound = False
    
    For Each lo_WorkSheet In lo_WorkBook.Worksheets
        ll_firstRow = GetFirstDataRow(lo_WorkSheet, C_BMK_CODE_COL, 3)
        If ll_firstRow <> 0 Then
            Set lo_lvItem = lo_dlg.AddItem(lo_WorkSheet.Name, lo_WorkSheet.Name)
            ls_year = lo_WorkSheet.cells(1, C_DATA_YEAR_COL)
            If Not isNumeric(ls_year) Then ls_year = ""
            
            ls_Version = lo_WorkSheet.cells(2, C_DATA_VERSION_COL)
            If Not isNumeric(ls_Version) Then ls_Version = ""
            
            ls_DataType = lo_WorkSheet.cells(1, C_DATA_TYPE_COL)
            If InStr(1, ls_DataType, "Forecast", vbTextCompare) > 0 Then
                ls_DataType = "FCT"
            ElseIf InStr(1, ls_DataType, "Estimate", vbTextCompare) > 0 Then
                ls_DataType = "EST"
            ElseIf InStr(1, ls_DataType, "BUDGET", vbTextCompare) > 0 Then
                ls_DataType = "BDG"
            End If
            
            lo_lvItem.Tag = Join(Array(ls_year, ls_DataType, ll_firstRow, ls_Version), 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(1, C_DATA_YEAR_COL) Then
                lo_WorkSheet.cells(1, C_DATA_YEAR_COL) = lo_dlg.Data_Year
                lb_sheetUpdated = True
            End If
            
            If lo_dlg.Data_version <> lo_WorkSheet.cells(2, C_DATA_VERSION_COL) Then
                lo_WorkSheet.cells(2, C_DATA_VERSION_COL) = lo_dlg.Data_version
                lb_sheetUpdated = True
            End If
            
            Dim ls_MG_Code As String
            Dim ls_value_type As String
            Dim ll_BUG_cursor As Long
            
            ll_BUG_cursor = OpenSQLSafe(mo_Db, "select BUG_Code, BMK_Code from onsv_bu_geography_to_businessmarkets2 where language_code='E'")
            
            While Not IsLastExcellRow(lo_WorkSheet, ll_Row)
            
                ll_Row = NextExcellRow(lo_WorkSheet, ll_Row)
                
                Call FindMG_Code_AND_Value_type(lo_WorkSheet, ll_prevRow, ll_Row, ls_MG_Code, ls_value_type)
                ll_prevRow = ll_Row
                
                If lo_WorkSheet.cells(ll_Row, C_BMK_CODE_COL) <> "" Then
                    Dim ll_recordID As Long
                    
                    If mo_Db.Find(ll_BUG_cursor, "BMK_Code", lo_WorkSheet.cells(ll_Row, C_BMK_CODE_COL)) < 0 Then
                        Call MsgBox("Cannot fing BU_Geography value for BMK_Code:" & lo_WorkSheet.cells(ll_Row, C_BMK_CODE_COL) & " (check in onsv_bu_geography_to_businessmarkets2)")
                    Else
                    
                        ll_recordID = GetRecordID(lo_dlg.BU_code, lo_dlg.Data_Type, lo_dlg.Data_Year, lo_dlg.Data_version, ls_value_type, ls_MG_Code, lo_WorkSheet.cells(ll_Row, C_BMK_CODE_COL), mo_Db.GetFields(ll_BUG_cursor, "BUG_Code"))
                        
                        If ll_recordID <> -1 Then
                            Call Item_Load_E(lo_WorkSheet, ll_Row, ll_recordID, lo_dlg.Data_Year, lo_dlg.Data_Type, ls_MG_Code, ls_value_type, lo_dlg.Data_version, mo_Db.GetFields(ll_BUG_cursor, "BUG_Code"))
                            Call frm_detail.Refresh
                            DoEvents
'                            Call Item_audit(ll_recordID, "U")
                            Call Item_UpdateDB(ll_recordID)
                        Else
                            ' generate new record into ONSV_Baselines
                            ll_recordID = GetNextRecordID()
                            Call Item_Load_E(lo_WorkSheet, ll_Row, ll_recordID, lo_dlg.Data_Year, lo_dlg.Data_Type, ls_MG_Code, ls_value_type, lo_dlg.Data_version, mo_Db.GetFields(ll_BUG_cursor, "BUG_Code"))
                            Call frm_detail.Refresh
                            DoEvents
'                            Call Item_audit(ll_recordID, "I")
                            Call Item_AddDB(ll_recordID)
                        End If
                    End If
                End If
                ll_Row = ll_Row + 1
            Wend
            
            Call mo_Db.Close(ll_BUG_cursor)
            ll_BUG_cursor = 0
            
            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:
    If ll_BUG_cursor <> 0 Then
        Call mo_Db.Close(ll_BUG_cursor)
        ll_BUG_cursor = 0
    End If
    Call ErrorHandler(Extender.Name & ".ImportFromXLS")
End Sub



Private Sub FindMG_Code_AND_Value_type(ByRef ao_workSheet As Object, ByVal al_Row As Long, ByVal al_MaxRow As Long, ByRef as_MG_Code As String, ByRef as_value_type As String)
On Error GoTo ErrHandler
    While al_Row <= al_MaxRow
        Select Case UCase(Trim(ao_workSheet.cells(al_Row, C_MG_CODE_COL)))
            Case "MINERAL FIBRE"
                as_MG_Code = "M001"
            Case "METAL"
                as_MG_Code = "M002"
            Case "GRID"
                as_MG_Code = "M003"
            Case "WOOL"
                as_MG_Code = "M004"
        End Select
        
        Select Case Left(UCase(Trim(ao_workSheet.cells(al_Row, C_VALUE_TYPE_COL))), 3)
            Case "VOL"
                as_value_type = "QTY"
            Case "GSV"
                as_value_type = "GSV"
            Case "NSV"
                as_value_type = "NSV"
        End Select
        
        al_Row = al_Row + 1
    Wend

    Exit Sub
ErrHandler:
    Call ErrorHandler(Extender.Name & ".FindMG_Code_AND_Value_type")
End Sub


Private Function GetRecordID(ByVal as_BU_Code As String, ByVal as_Data_type As String, ByVal as_data_year As String, ByVal as_Data_Version As String, ByVal as_value_type As String, ByVal as_MG_Code As String, ByVal as_bmk_code As String, ByVal as_BUG_Code As String) As Long
On Error GoTo ErrHandler
    GetRecordID = -1
    Const C_REQ As String = "SELECT Record_ID, BUG_Code " & _
                            "FROM [ONSV_baselines] " & _
                            "WHERE bu_code=$BU_CODE$ and Data_type=$DATA_TYPE$ and Data_year=$DATA_YEAR$ and Data_version=$DATA_VERSION$ and Value_type=$VALUE_TYPE$ and mg_code=$MG_CODE$ and BMK_code=$BMK_CODE$"
    Dim ll_Cursor As Long
    Dim ls_Req As String
    
    ls_Req = Replace(C_REQ, "$BU_CODE$", SqlStr(as_BU_Code, 4), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$DATA_TYPE$", SqlStr(as_Data_type, 5), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$DATA_YEAR$", SqlStr(as_data_year, 4), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$DATA_VERSION$", SQLNum(as_Data_Version), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$VALUE_TYPE$", SqlStr(as_value_type, 3), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$MG_CODE$", SqlStr(as_MG_Code, 4), , , vbTextCompare)
    ls_Req = Replace(ls_Req, "$BMK_CODE$", SqlStr(as_bmk_code, 4), , , vbTextCompare)
    
    ll_Cursor = OpenSQLSafe(mo_Db, ls_Req)
    
    If mo_Db.RowCount(ll_Cursor) >= 1 Then
        GetRecordID = mo_Db.GetFields(ll_Cursor, "Record_ID")
        Debug.Assert (mo_Db.GetFields(ll_Cursor, "BUG_Code") = as_BUG_Code)
    End If
    
    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 & ".GetRecordID")
End Function

Private Function GetNextRecordID() As Long
On Error GoTo ErrHandler
    Dim ll_Cursor As Long
    
    ll_Cursor = OpenSQLSafe(mo_Db, "SELECT MAX(Record_ID) AS Record_ID FROM ONSV_Baselines")
    
    GetNextRecordID = mo_Db.GetFields(ll_Cursor, "Record_ID") + 1
    
    mo_Db.Close (ll_Cursor)
    Exit Function
ErrHandler:
    Call ErrorHandler(Extender.Name & ".GetNextRecordID")
End Function

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

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


' 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

