Module BestFunctions.bas

 Attribute VB_Name = "BestFunctions"

 FUNCTION ДТС0(Счет As Variant, Optional ДСтарт As DATE = EMPTY, Optional Свернуть As BOOLEAN = FALSE) As Currency
   IF YEAR(ДСтарт) < 1990 Then
     ДСтарт = Range("Дата0").Value
     End IF
   SET oConnection = CreateObject("ADODB.Connection")
   oConnection.Open GetConSTR()
   Rem Запрос
   cSQL1 = "select main.DT_schet as schet, main.dt_code as code, sum(main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "dt_", Счет) + _
   " and main.dataoper < {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select main.kT_schet as schet, main.kt_code as code,sum(-main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "kt_", Счет) + _
   " and main.dataoper < {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code,sum(analit0.dt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code, sum(-analit0.kt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2" + _
   " union all "
   cSQL2 = "select plan0.schet, space(6) as code, sum(plan0.dt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " union all " + _
   "select plan0.schet, space(6) as code,sum(-plan0.kt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " into cursor crCur1;" + _
   " select schet, code, sum(summa) as summa " + _
   "from crCur1 " + _
   " group by 1,2 " + _
   " into cursor crCur2; " + _
   " select sum(summa) as summa " + _
   "from crCur2 " + _
   IIF(Свернуть, "", " where summa > 0")
   SET rs = oConnection.Execute(cSQL1 + cSQL2)
   SET rs = rs.NextRecordSet()
   SET rs = rs.NextRecordSet()
   ДТС0 = 0
   IF NOT (rs.EOF AND rs.BOF) Then
     IF rs!Summa >= 0 Then
       ДТС0 = rs!Summa
       End IF
     End IF
   rs.CLOSE
   oConnection.CLOSE
   End FUNCTION

 FUNCTION ДТС(Счет As Variant, Optional ДСтарт As DATE = EMPTY, Optional Свернуть As BOOLEAN = FALSE) As Currency
   IF YEAR(ДСтарт) < 1990 Then
     ДСтарт = Range("Дата").Value
     End IF
   SET oConnection = CreateObject("ADODB.Connection")
   oConnection.Open GetConSTR()
   Rem Запрос
   cSQL1 = "select main.DT_schet as schet, main.dt_code as code, sum(main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "dt_", Счет) + _
   " and main.dataoper <= {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select main.kT_schet as schet, main.kt_code as code,sum(-main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "kt_", Счет) + _
   " and main.dataoper <= {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code,sum(analit0.dt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code, sum(-analit0.kt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2" + _
   " union all "
   cSQL2 = "select plan0.schet, space(6) as code, sum(plan0.dt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " union all " + _
   "select plan0.schet, space(6) as code,sum(-plan0.kt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " into cursor crCur1;" + _
   " select schet, code, sum(summa) as summa " + _
   "from crCur1 " + _
   " group by 1,2 " + _
   " into cursor crCur2; " + _
   " select sum(summa) as summa " + _
   "from crCur2 " + _
   IIF(Свернуть, "", " where summa > 0")
   SET rs = oConnection.Execute(cSQL1 + cSQL2)
   SET rs = rs.NextRecordSet()
   SET rs = rs.NextRecordSet()
   ДТС = 0
   IF NOT (rs.EOF AND rs.BOF) Then
     IF rs!Summa >= 0 Then
       ДТС = rs!Summa
       End IF
     End IF
   rs.CLOSE
   oConnection.CLOSE
   End FUNCTION

 FUNCTION КТС0(Счет As Variant, Optional ДСтарт As DATE = EMPTY, Optional Свернуть As BOOLEAN = FALSE) As Currency
   IF YEAR(ДСтарт) < 1990 Then
     ДСтарт = Range("Дата0").Value
     End IF
   SET oConnection = CreateObject("ADODB.Connection")
   oConnection.Open GetConSTR()
   Rem Запрос
   cSQL1 = "select main.DT_schet as schet, main.dt_code as code, sum(-main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "dt_", Счет) + _
   " and main.dataoper < {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select main.kT_schet as schet, main.kt_code as code,sum(main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "kt_", Счет) + _
   " and main.dataoper < {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code,sum(-analit0.dt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code, sum(analit0.kt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2" + _
   " union all "
   cSQL2 = "select plan0.schet, space(6) as code, sum(-plan0.dt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " union all " + _
   "select plan0.schet, space(6) as code,sum(plan0.kt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " into cursor crCur1;" + _
   " select schet, code, sum(summa) as summa " + _
   "from crCur1 " + _
   " group by 1,2 " + _
   " into cursor crCur2; " + _
   " select sum(summa) as summa " + _
   "from crCur2 " + _
   IIF(Свернуть, "", " where summa > 0")
   SET rs = oConnection.Execute(cSQL1 + cSQL2)
   SET rs = rs.NextRecordSet()
   SET rs = rs.NextRecordSet()
   КТС0 = 0
   IF NOT (rs.EOF AND rs.BOF) Then
     IF rs!Summa >= 0 Then
       КТС0 = rs!Summa
       End IF
     End IF
   rs.CLOSE
   oConnection.CLOSE
   End FUNCTION

 FUNCTION КТС(Счет As Variant, Optional ДСтарт As DATE = EMPTY, Optional Свернуть As BOOLEAN = FALSE) As Currency
   IF YEAR(ДСтарт) < 1990 Then
     ДСтарт = Range("Дата").Value
     End IF
   SET oConnection = CreateObject("ADODB.Connection")
   oConnection.Open GetConSTR()
   Rem Запрос
   cSQL1 = "select main.DT_schet as schet, main.dt_code as code, sum(-main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "dt_", Счет) + _
   " and main.dataoper <= {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select main.kT_schet as schet, main.kt_code as code,sum(main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "kt_", Счет) + _
   " and main.dataoper <= {" + FORMAT(ДСтарт, "^YYYY-MM-DD") + "}" + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code,sum(-analit0.dt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2 " + _
   " union all " + _
   "select analit0.schet, analit0.code, sum(analit0.kt_begin) as summa " + _
   " from analit0 " + _
   " where " + GetFilterExp("analit0", "", Счет) + _
   " and analit0.valuta=[РУБ] " + _
   " group by 1,2" + _
   " union all "
   cSQL2 = "select plan0.schet, space(6) as code, sum(-plan0.dt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " union all " + _
   "select plan0.schet, space(6) as code,sum(plan0.kt_begin) as summa " + _
   " from plan0 join plan_sch on plan0.schet==plan_sch.schet " + _
   " where " + GetFilterExp("plan0", "", Счет, FALSE) + _
   " and plan0.date={" + FORMAT(GlobalPer(oConnection), "^YYYY-MM-DD") + "}" + _
   " and !plan_sch.analit_y_n and plan0.valuta=[РУБ]" + _
   " group by 1,2 " + _
   " into cursor crCur1;" + _
   " select schet, code, sum(summa) as summa " + _
   "from crCur1 " + _
   " group by 1,2 " + _
   " into cursor crCur2; " + _
   " select sum(summa) as summa " + _
   "from crCur2 " + _
   IIF(Свернуть, "", " where summa > 0")
   SET rs = oConnection.Execute(cSQL1 + cSQL2)
   SET rs = rs.NextRecordSet()
   SET rs = rs.NextRecordSet()
   КТС = 0
   IF NOT (rs.EOF AND rs.BOF) Then
     IF rs!Summa >= 0 Then
       КТС = rs!Summa
       End IF
     End IF
   rs.CLOSE
   oConnection.CLOSE
   End FUNCTION

 FUNCTION ДТКТ(Дебет As Variant, Кредит As Variant, Optional ДСтарт As DATE = EMPTY, Optional ДКонец As DATE = EMPTY) As Currency
   'Функция расчета оборота между двумя счетами
  If Year(ДСтарт) < 1990 Then
   ДСтарт = Range("Дата0").Value
   End If
   If Year(ДКонец) < 1990 Then
   ДКонец = Range("Дата").Value
   End If
   Set oConnection = CreateObject("ADODB.Connection")
   oConnection.Open GetConSTR()
   cSQL = "select sum(main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "dt_", Дебет) + _
   " and " + _
   GetFilterExp("main", "kt_", Кредит) + _
   " and main.dataoper between {" + Format(ДСтарт, "^YYYY-MM-DD") + "} and {" + Format(ДКонец, "^YYYY-MM-DD") + "}"
   Set rs = oConnection.Execute(cSQL)
   If rs.Bof And rs.EOF Then
   ДТКТ = 0
   Else
   ДТКТ = rs.fields("summa").Value
   End If
   rs.Close
   oConnection.Close
   End Function

 Function ДТ(Счет As Variant, Optional ДСтарт As Date = Empty, Optional ДКонец As Date = Empty) As Currency
   If Year(ДСтарт) < 1990 Then
   ДСтарт = Range("Дата0").Value
   End If
   If Year(ДКонец) < 1990 Then
   ДКонец = Range("Дата").Value
   End If
   Set oConnection = CreateObject("ADODB.Connection")
   oConnection.Open GetConSTR()
   Rem Запрос
   cSQL = "select sum(main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "dt_", Счет) + _
   " and main.dataoper between {" + Format(ДСтарт, "^YYYY-MM-DD") + "} and {" + Format(ДКонец, "^YYYY-MM-DD") + "}"
   Set rs = oConnection.Execute(cSQL)
   If rs.Bof And rs.EOF Then
   ДТ = 0
   Else
   ДТ = rs.fields("summa").Value
   End If
   rs.Close
   oConnection.Close
   End Function

 Function КТ(Счет As Variant, Optional ДСтарт As Date = Empty, Optional ДКонец As Date = Empty) As Currency
   If Year(ДСтарт) < 1990 Then
   ДСтарт = Range("Дата0").Value
   End If
   If Year(ДКонец) < 1990 Then
   ДКонец = Range("Дата").Value
   End If
   Set oConnection = CreateObject("ADODB.Connection")
   oConnection.Open GetConSTR()
   Rem Запрос
   cSQL = "select sum(main.summa) as summa " + _
   " from main " + _
   " where " + GetFilterExp("main", "kt_", Счет) + _
   " and main.dataoper between {" + Format(ДСтарт, "^YYYY-MM-DD") + "} and {" + Format(ДКонец, "^YYYY-MM-DD") + "}"
   Set rs = oConnection.Execute(cSQL)
   If rs.Bof And rs.EOF Then
   КТ = 0
   Else
   КТ = rs.fields("summa").Value
   End If
   rs.Close
   oConnection.Close
   End Function