Option Compare Database
Option Explicit
Public stroka As String
Public stroka1
Public twFilter
Public twFiltercur
Public twFindepList
Public twCuratorList
Dim WithEvents TV As TreeView

Private Sub Form_Load()
Echo False
DoCmd.Maximize
Set TV = Me.Treeview1.Object
Dim nodx As Node
Dim RS As New ADODB.Recordset
    Treeview1.Nodes.Clear
    Set nodx = Treeview1.Nodes.Add(, , ,  "Вся Компания" ,  1 )

RS.Open  "SELECT    ... " , CurrentProject.Connection, adOpenKeyset, adLockReadOnly

Do Until RS.EOF
    Set nodx = Treeview1.Nodes.Add (1 , tvwChild,  "id_fg"  & RS( "findepid" ), RS( "findepname" ),  2 )

     nodx.EnsureVisible
 RS.MoveNext
Loop

RS.Close
RS.Open  "dbo.all_curators_by_findep" , CurrentProject.Connection, adOpenKeyset, adLockReadOnly

Do Until RS.EOF
 If IsNull(RS( "findepid" )) Then
    Set nodx = Treeview1.Nodes.Add (1 , tvwChild,  "id_cunodep"  & RS( "curator" ), RS( "fio" ))
 Else
    Set nodx = Treeview1.Nodes.Add( "id_fg"  & RS( "findepid" ), tvwChild,  "id_cu"  & RS( "curator" ), RS( "fio" ))
 End If
     nodx.EnsureVisible

RS.MoveNext
Loop

Treeview1.Nodes (1 ).Selected = True
RS.Close
Set RS = Nothing
Echo True
End Sub



Private Sub setfilters_Click()
'Фильтр '
stroka =  " and " 
'Заголовок отчета - Фильтр '
stroka1 =  ""

If twFindepList <> "  " Then

    stroka = stroka & "     dbo.get_sotr_findepid_absolute(pr.curator, pr.OpenDate) in ( " & twFindepList & " ) and  "
    stroka1 = stroka1 + twFilter
End If

If twCuratorList <> "  " Then
stroka = stroka & "     pr.curator in ( " & twCuratorList & " ) and  "
stroka1 = stroka1 + twFiltercur

End If



If Not IsNull(Me.fopendatafrom) Then stroka = stroka + " OpenDate >=  " & sqldata(Me.fopendatafrom) & "  AND  "
If Not IsNull(Me.fopendatafrom) Then stroka1 = stroka1 + " открытые после  " & Me.fopendatafrom & " ;  "

If Not IsNull(Me.fopendatato) Then stroka = stroka + " OpenDate <=  " & sqldata(Me.fopendatato) & "  AND  "
If Not IsNull(Me.fopendatato) Then stroka1 = stroka1 + " открытые до  " & (Me.fopendatato) & " ;  "

If Me.onlyopen Then
stroka = stroka + " [Close4 Date] IS NULL AND  "
stroka1 = stroka1 + "  открытые;  "

Else
    If Not IsNull(Me.fclosedatafrom) Then stroka = stroka + " [Close4 Date] >=  " & sqldata(Me.fclosedatafrom) & "  AND  "
    If Not IsNull(Me.fclosedatafrom) Then stroka1 = stroka1 + " закрытые после  " & (Me.fclosedatafrom) & " ;  "

    If Not IsNull(Me.fclosedatato) Then stroka = stroka + " [Close4 Date] <=  " & sqldata(Me.fclosedatato) & "  AND  "
    If Not IsNull(Me.fclosedatato) Then stroka1 = stroka1 + " закрытые до  " & (Me.fclosedatato) & " ;  "

End If

If Me.farchive = 2  Then stroka = stroka + "archive <>  0   AND  "
If Me.farchive = 2  Then stroka1 = stroka1 + "Архивные  "

If Me.farchive = 3  Then stroka = stroka + "archive =  0  AND  "
If Me.farchive = 3  Then stroka1 = stroka1 + "неархивные  "

If Me.sovmest Then stroka = stroka + " [countfil] >  1  AND  "
If Me.sovmest Then stroka1 = stroka1 + "  Только совместные  "



If Len(stroka) > 5  Then
stroka = Mid(stroka,  1 , Len(stroka) -  5 )
Me.pfilter = stroka & "  " & stroka1
Else
stroka = "  "
stroka1 = " Все  "
Me.pfilter = stroka & "   " & stroka1
End If
SQLReportFilterName = stroka1
SQLReportFilter = stroka

End Sub
Private Sub Open_filtred(Rptname As String)

DoCmd.OpenReport Rptname, acPreview
Reports(Rptname).FilterOn = False

End Sub



Private Sub TV_NodeCheck(ByVal Node As MSComctlLib.Node)
Dim N As MSComctlLib.Node
twFilter = "  "
twFiltercur = "  "
twFindepList = "  "
twCuratorList = "  "

If Node.Index = 1  Then
    For Each N In Me.Treeview1.Nodes
        N.Checked = False
    Next N
    TV.Nodes (1 ).Checked = True
Else
    TV.Nodes (1 ).Checked = False

    For Each N In Me.Treeview1.Nodes
        If N.Index =  1  Then GoTo m1
        If N.Parent.Index = Node.Index Or Node.Parent.Index = N.Index Then N.Checked = False
m1:
    Next N
End If


For Each N In Me.Treeview1.Nodes
If N.Checked Then

If InStr (1 , N.Key, "id_fg ", vbTextCompare) Then twFindepList = twFindepList & Mid(N.Key, 6 ,  5 ) & ", ": twFilter = twFilter + "   " + N.Text + " ;  "
If InStr(1 , N.Key, "id_cu ", vbTextCompare) Then twCuratorList = twCuratorList & Mid(N.Key, 6 ,  5 ) & ", ": twFiltercur = twFiltercur + "   " + N.Text + " ;  "

End If
Next N
If Len(twFindepList) > 1  Then twFindepList = Left(twFindepList, Len(twFindepList) -  1 )
If Len(twCuratorList) >  1  Then twCuratorList = Left(twCuratorList, Len(twCuratorList) -  1 )

Debug.Print twFilter
End Sub

Private Sub TV_NodeClick(ByVal Node As MSComctlLib.Node)
Node.Checked = Not Node.Checked
Call TV_NodeCheck(Node)
End Sub

Private Sub Отрытие_Отчета_Click()
On Error Resume Next
Call setfilters_Click

'если хочется в последний момент еще чего - нибудь'
If Me.custom_where Then
    SQLReportFilterName = InputBox("Название фильтра ", , SQLReportFilterName)
    SQLReportFilter = InputBox(" SQL ADD  ", , SQLReportFilter)
     
End If

Call Open_filtred(" форма6-1_dynamical")
End Sub

Public Sub Refresh_filter()
Dim sel As String
Dim rs As String
Dim where As String
Dim where1 As String

Dim order As String


Me.fresort.Requery
If IsNull(Me.fresort.ItemData (1 )) Then Me.fresort =  0 


sel =  " select * " 
rs =  " from search_all_oi_price_period where  1  =  1  "
If Me.fresort <>  0  Then where =  " and id_resort = "  & Me.fresort
If Me.fresort <>  0  Then where1 =  " and id_resort = "  & Me.fresort
If Me.fcountry <>  0  Then where1 =  " and id_country = "  & Me.fcountry

If Not IsNull(Me.fhotel) Then where1 = where1 &  " and idhotel = "  & Me.fhotel
If Not IsNull(Me.fnights) Then where1 = where1 &  " and nights = "  & Me.fnights
If Not IsNull(Me.fnumber_type) Then where1 = where1 &  " and idNumberType = "  & Me.fnumber_type


Me.p1.Form.RecordSource = sel & rs & where1
Me.fhotel.RowSource =  "SELECT distinct search_all_oi_price_period.idHotel, search_all_oi_price_period.hotel_name, Resort_Name "  & rs & where
End Sub