VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} AUTO_ALG 
   Caption         =   "Автоматический ввод условий отбора"
   ClientHeight    =   3930
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   6825
   OleObjectBlob   =   "AUTO_ALG.frx":0000
   StartUpPosition =   1  'CenterOwner
End
Attribute VB_Name = "AUTO_ALG"
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False











Option Explicit
Public Grafa
Private Sub ComboBox1_Change()
Dim i
    For i = ComboBox2.ListCount - 1 To 0 Step -1
        ComboBox2.RemoveItem (i)
    Next
    With Sheets("Service")
        If .Range("B23") <> 1 Then Sheets(ComboBox1.Text).Activate
        .Range(.Cells(KRIT_GRAF_ROW + 1, KRIT_GRAF_COLUMN), .Cells(KRIT_GRAF_ROW + 1, KRIT_GRAF_COLUMN + KRIT_GRAF_COLUMNCOUNT - 2)) = ""
        
        For i = 0 To .Range("A10")
            .Cells(KRIT_GRAF_ROW + 1, KRIT_GRAF_COLUMN) = i
            ComboBox2.AddItem (.Cells(KRIT_GRAF_ROW + 2, KRIT_GRAF_COLUMN))
        Next
        .Cells(KRIT_GRAF_ROW + 1, KRIT_GRAF_COLUMN) = ""
    End With
End Sub

Private Sub ComboBox2_Change()
Dim shablon_Column, shablon_Row
    With Sheets("Service")
        shablon_Column = .Range("shablon").column
        shablon_Row = .Range("shablon").row
        .Cells(KRIT_GRAF_ROW + 1, KRIT_GRAF_COLUMN + 1) = ComboBox2.Text
        Grafa = ComboBox2.ListIndex
        'Grafa = .Cells(KRIT_GRAF_ROW + 2, KRIT_GRAF_COLUMN + 1)
        While (.Cells(shablon_Row, shablon_Column) <> .Range("B20")) And (.Cells(shablon_Row, shablon_Column) <> "")
            shablon_Row = shablon_Row + 1
        Wend
        TextBox1.Text = .Cells(shablon_Row, shablon_Column + 1)
        TextBox2.Text = .Cells(shablon_Row, shablon_Column + 2)
    End With
End Sub

Private Sub CommandButton1_Click()
Dim lcExcelTextFormula2, lcExcelTextFormula1, i, linkGraf, linkStr, save1, save2, bbb, j, lnRes
' Вытащить  с TABLE_ALG для строки , если есть
linkGraf = Grafa
Call init_data
save1 = Me.TextBox1.Text
save2 = Me.TextBox2.Text
For i = Sheets("Service").Range("C5") To Sheets("Service").Range("A9")
    With Sheets("ALG")
        ' Сброс критерия поиска
        .Range(.Cells(KRIT_ALG_ROW + 1, KRIT_ALG_COLUMN), .Cells(KRIT_ALG_ROW + 1, KRIT_ALG_COLUMN + KRIT_ALG_COLUMNCOUNT - 2)) = ""
        
        linkStr = ActiveSheet.Cells(i, 1)
        .Cells(KRIT_ALG_ROW + 1, KRIT_ALG_COLUMN) = linkStr
        .Cells(KRIT_ALG_ROW + 1, KRIT_ALG_COLUMN + 1) = 0 'тип алгоритма
        
        '.Cells(lnRow + 1, lnColumn + 2) = "="
        
        ActiveWindow.SmallScroll Down:=1
        ' Проверим , а есть ли такой алгоритм расчета
        If (.Cells(KRIT_ALG_ROW + 2, KRIT_ALG_COLUMN + 1) = 0 And .Cells(KRIT_ALG_ROW + 2, KRIT_ALG_COLUMN + 2) = 0) Or (linkStr = 0) Then ' Запись есть
        Else
            .Cells(KRIT_ALG_ROW + 1, KRIT_ALG_COLUMN) = linkStr
            .Cells(KRIT_ALG_ROW + 1, KRIT_ALG_COLUMN + 1) = linkGraf 'тип алгоритма
            If (.Cells(KRIT_ALG_ROW + 2, KRIT_ALG_COLUMN + 1) = 0 And .Cells(KRIT_ALG_ROW + 2, KRIT_ALG_COLUMN + 2) = 0) Or (linkStr = 0) Then ' Запись есть
            Else
                Me.TextBox1.Text = Replace_in_Str(Me.TextBox1.Text, "%0", TRUNCATE_00(getCode(i)))
                Me.TextBox2.Text = Replace_in_Str(Me.TextBox2.Text, "%0", TRUNCATE_00(getCode(i)))
                
                If (TextBox3.Text <> "") Then
                    If VBA.Trim(TRUNCATE_00(ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1"))))) <> "" Then
                        Me.TextBox1.Text = Me.TextBox1.Text + " " + Replace_in_Str(Me.TextBox3.Text, "%1", TRUNCATE_00(ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")))))
                    End If
                End If
                If (TextBox5.Text <> "") Then
                    If VBA.Trim((ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + 1))) <> "000" Then
                        Me.TextBox1.Text = Me.TextBox1.Text + " " + Replace_in_Str(Me.TextBox5.Text, "%2", (ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + 1)))
                    End If
                End If
                If (TextBox7.Text <> "") Then
                    If VBA.Trim((ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + 2))) <> "000" Then
                        Me.TextBox1.Text = Me.TextBox1.Text + " " + Replace_in_Str(Me.TextBox7.Text, "%3", (ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + 2)))
                    End If
                End If
                If (TextBox9.Text <> "") Then
                    If VBA.Trim((ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + 3))) <> "000" Then
                        Me.TextBox1.Text = Me.TextBox1.Text + " " + Replace_in_Str(Me.TextBox9.Text, "%4", (ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + 3)))
                    End If
                End If
                
                Me.TextBox1.Text = Replace_in_Str(Me.TextBox1.Text, "%0", TRUNCATE_00(getCode(i)))
                Me.TextBox2.Text = Replace_in_Str(Me.TextBox2.Text, "%0", TRUNCATE_00(getCode(i)))
                
                bbb = MsgBox("Добавить условие для строки с кодом " + getCode(i) + " ?", vbQuestion + vbYesNoCancel, "Внимание !")
                If (bbb = vbYes) And (linkStr <> 0) Then
                    'Me.TextBox1.Text = Replace_in_Str(Me.TextBox1.Text, "%0", TRUNCATE_00(Val(Right(ActiveSheet.Cells(i, 3), 5))))
                    'Me.TextBox2.Text = Replace_in_Str(Me.TextBox2.Text, "%0", TRUNCATE_00(Val(Right(ActiveSheet.Cells(i, 3), 5))))
                    
                    'For j = 1 To Sheets("Service").Range("E4")
                    '    Me.TextBox1.Text = Replace_in_Str(Me.TextBox1.Text, "%" + VBA.Trim(VBA.str(j)), TRUNCATE_00(ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + j - 1)))
                    '    Me.TextBox2.Text = Replace_in_Str(Me.TextBox2.Text, "%" + VBA.Trim(VBA.str(j)), TRUNCATE_00(ActiveSheet.Cells(i, Sheets("Service").Range("C6") - Sheets("Service").Range(Sheets("Service").Range("E1")) + j - 1)))
                    'Next
                    
                    ' Необходимо проверить условия  и перевести их в форма Excel
                    lcExcelTextFormula1 = ALG_EDIT.TEST_COND_NEW(Me.TextBox1.Text)
                    If Me.TextBox2.Text <> "" Then
                        lcExcelTextFormula2 = ALG_EDIT.TEST_COND_NEW(Me.TextBox2.Text)
                    End If
                    
                    If (lcExcelTextFormula1 <> "") And ((lcExcelTextFormula2 <> "") Or (Me.TextBox2.Text = "")) Then ' Условие правильное
                        Call WORK_TABLE_ALG(linkStr, linkGraf, 0, "", 1, 1) ' Insert в TABLE_ALG
                        lnRes = WORK_TABLE_STR_COND(linkStr, linkGraf, 0, 1, lcExcelTextFormula1, lcExcelTextFormula2, IIf(Sheets("Service").Range("B23") = 1, "1", Sheets("Service").Range("B20").Text), Me.TextBox1.Text, Me.TextBox2.Text) ' Вставка в TABLE_STR_COND
            '            Call WORK_TABLE_PART_COND(lnRes, 1, Me.LstOtbor) ' Вставка в TABLE_PART_COND
                    End If
                End If
                If bbb = vbCancel Then
                    Unload Me
                    Exit Sub
                End If
                Me.TextBox1.Text = save1
                Me.TextBox2.Text = save2
            End If
        End If
    End With
Next

End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim S_RAZDEL_ROW, S_RAZDEL_COLUMN, S_RAZDEL_COLUMNCOUNT, i
    Call init_data
    With Sheets("Service")
        S_RAZDEL_ROW = .Range("S_RAZDEL").row
        S_RAZDEL_COLUMN = .Range("S_RAZDEL").column
        S_RAZDEL_COLUMNCOUNT = .Range("S_RAZDEL").Columns.count
        '.Range(.Cells(KRIT_RAZDEL_ROW + 1, KRIT_RAZDEL_COLUMN), .Cells(KRIT_RAZDEL_ROW + 1, KRIT_RAZDEL_COLUMN + KRIT_RAZDEL_COLUMNCOUNT - 1)) = ""
        i = S_RAZDEL_ROW + 1
        While .Cells(i, S_RAZDEL_COLUMN) <> ""
            ComboBox1.AddItem (.Cells(i, S_RAZDEL_COLUMN + 1))
            i = i + 1
        Wend
    End With
End Sub

