REM  *****  BASIC  *****

Function BadTiming(ByVal plan As cell, ByVal real As cell) As double
	Dim cnt As Integer
	
	BadTiming = 0
	For cnt = LBound(plan) To UBound(plan)
		If real(cnt,1) > plan(cnt,1) Then
			BadTiming = BadTiming + 1
		End If
	Next cnt
	If cnt = 1 Then
		cnt = 2
	End If
	BadTiming = BadTiming*100/(cnt-1)
End Function

Function ErrorTypes(ByVal taskname As cell, ByVal percent As cell, ByVal eventdate As cell, ByVal errortype As cell, ByVal startdate As date, ByVal enddate As date) As String
	Dim cnt As Integer
	Dim inner_cnt As Integer
	Dim overnum As Integer
	Dim completenum As Integer
	Dim waste_time
	
	Dim oDiagSheet, oSpravSheet
	Dim oErrors, oCell
	Dim Flags
	Dim tmpVal
	
	On Error Goto ErrorHandler
	waste_time = "Пустая трата"
	oDiagSheet = ThisComponent.Sheets.getByName("ДиаИнфо")
	oErrors = oDiagSheet.getCellRangeByName("A2:A30") rem Очистка 20 ячеек в первом столбце
	Flags = com.sun.star.sheet.CellFlags.VALUE
	oErrors.clearContents(Flags)
	oSpravSheet = ThisComponent.Sheets.getByName("Справочники")
	oErrors = oSpravSheet.getCellRangeByName("B2:B14") rem изменить
	tmpVal = oErrors.DataArray
	
	overnum = 0
	completenum = 0
	
	For cnt = LBound(percent) To UBound(percent)
	If Len(taskname(cnt,1)) > 1 Then
	If (CDate(eventdate(cnt,1))>=CDate(startdate)) and (CDate(eventdate(cnt,1))<=CDate(enddate)) Then Rem Check for the date
		REM Get Number of all errors with comments
		If (percent(cnt,1) < 100) And (Len(errortype(cnt,1)) > 1) Then
			overnum = overnum + 1
		End If
		REM Get Number of exact errors
		If (Len(errortype(cnt,1)) > 1) Then
			For inner_cnt = LBound(tmpVal) To UBound(tmpVal)
				If StrComp(errortype(cnt,1),oErrors.getCellByPosition(0,inner_cnt).getString()) = 0 Then
					oCell = oDiagSheet.getCellByPosition(0,1+inner_cnt)
					oCell.setValue(oCell.getValue() + 1)
				End If
			Next inner_cnt
		End If
		REM Get Number of complete works
		If (percent(cnt,1) = 100) and (StrComp(errortype(cnt,1),waste_time) <> 0) Then
			completenum = completenum + 1
		End If
	End If
	Else
		Exit For
	End If
	Next cnt
	
	oCell = oDiagSheet.getCellByPosition(0,UBound(tmpVal)+2)
	oCell.setValue(completenum*100/(overnum+completenum))
	
	For inner_cnt = LBound(tmpVal) To UBound(tmpVal)
		oCell = oDiagSheet.getCellByPosition(0,1+inner_cnt)
		oCell.setValue(oCell.getValue()*100/(overnum+completenum))
	Next inner_cnt
	
	ErrorTypes = "ErrorTypes"
	Exit Function
ErrorHandler:
    ErrorTypes = "Exception"
End Function 

REM For count of work completance by week
Function GoodVsBad(ByVal week As cell, ByVal percent As cell, ByVal rating, ByVal errorcode) As String
	Dim cnt As Integer
	Dim current As Date
	Dim pos As Integer
	Dim buff As Integer
	Dim effect As Double rem For new diagram
	Dim inn_cnt As Integer
	Dim errortypex As String
	
	Dim oDiagSheet
	Dim oChart
	Dim oDataArray()
	Dim oStringInfo()
	Dim oEffectArray() rem For new diagram
	Dim oCell
	
	errortypex = "Пустая трата"
	current = week(1,1)
	pos = 1
	buff = 0
	effect = 0 rem For new diagram
	inn_cnt = 0
	Redim oDataArray(100,0)
	Redim oStringInfo(100)
	Redim oEffectArray(100,0) rem For new diagram
	For cnt = LBound(week) To UBound(week)
	If Not IsNull(week(cnt,1)) Then
		If current = week(cnt,1) Then
			inn_cnt = inn_cnt + 1
			If StrComp(errorcode(cnt,1),errortypex) <> 0 Then
				buff = buff + percent(cnt,1)
				If rating(cnt,1) > 0 Then
					effect = effect + percent(cnt,1)/rating(cnt,1) rem For new diagram
				End If
			End If
		Else
			rem Write to the diagram array
			oDataArray(pos-1,0) = buff/inn_cnt
			oStringInfo(pos-1) = cStr(current)
			oEffectArray(pos-1,0) = effect/inn_cnt rem For new diagram
			rem Set counters
			current = week(cnt,1)
			pos = pos + 1
			If StrComp(errorcode(cnt,1),errortypex) <> 0 Then
				buff = percent(cnt,1)
				If rating(cnt,1) > 0 Then
					effect = percent(cnt,1)/rating(cnt,1) rem For new diagram
				Else
					effect = 0
				End If
			End If
			inn_cnt = 1
		End If
	Else
		Exit For
	End If
	Next cnt
	
	Redim Preserve oDataArray(pos-2,0)
	Redim Preserve oStringInfo(pos-2)
	Redim Preserve oEffectArray(pos-2,0)
	On Error Goto ErrorHandler
	oDiagSheet = ThisComponent.Sheets.getByName("Отчёты")
	oChart = oDiagSheet.Charts.getByName("Object 3").EmbeddedObject rem ОБЪЕКТ: поставьте правильное имя в функции getByName (Например, Object 1)
	If StrComp(oChart.Title.String,"Процент выполнения задач") = 0 Then
		oChart.diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
		oChart.diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
		oChart.Data.setData(oDataArray())
		oChart.Data.setRowDescriptions(oStringInfo())
		GoodVsBad = "GoodVsBad"
	Else
		GoodVsBad = "Ошибка: укажите другое имя в поле ОБЪЕКТ"
	End If
	
	rem For new Diagram
	oChart = oDiagSheet.Charts.getByName("Object 4").EmbeddedObject
	If StrComp(oChart.Title.String,"Эффективность по неделям") = 0 Then
		oChart.diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
		oChart.diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
		oChart.Data.setData(oEffectArray())
		oChart.Data.setRowDescriptions(oStringInfo())
		GoodVsBad = GoodVsBad + "OK"
	Else
		GoodVsBad = GoodVsBad + "NameEr"
	End If
	Exit Function
ErrorHandler:
	rem GoodVsBad = "Exception"
End Function

REM For count of work completance by time
Function ChartWorksByTime(ByVal week As cell, ByVal percent As cell, ByVal rating, ByVal errorcode, ByVal timex As cell) As String
	Dim cnt As Integer
	Dim current As Date
	Dim pos As Integer
	Dim buff As Integer
	Dim effect As Double rem For new diagram
	Dim inn_cnt As Integer
	Dim errortypex As String
	
	Dim oDiagSheet
	Dim oChart
	Dim oDataArray()
	Dim oStringInfo()
	Dim oEffectArray() rem For new diagram
	Dim oCell
	
	errortypex = "Пустая трата"
	current = week(1,1)
	pos = 1
	buff = 0
	effect = 0 rem For new diagram
	inn_cnt = 0
	Redim oDataArray(100,0)
	Redim oStringInfo(100)
	For cnt = LBound(week) To UBound(week)
	If Not IsNull(week(cnt,1)) Then
		If current = week(cnt,1) Then
			inn_cnt = inn_cnt + 1
			If StrComp(errorcode(cnt,1),errortypex) <> 0 Then
				If rating(cnt,1) > 0 Then
					buff = buff + percent(cnt,1) * timex(cnt,1) / rating(cnt,1)
				Else
					buff = buff + percent(cnt,1) * timex(cnt,1) / 3
				End If
			End If
		Else
			rem Write to the diagram array
			oDataArray(pos-1,0) = buff/inn_cnt
			oStringInfo(pos-1) = cStr(current)
			rem Set counters
			current = week(cnt,1)
			pos = pos + 1
			If StrComp(errorcode(cnt,1),errortypex) <> 0 Then
				If rating(cnt,1) > 0 Then
					buff = percent(cnt,1)/rating(cnt,1)
				Else
					buff = 0
				End If
			End If
			inn_cnt = 1
		End If
	Else
		Exit For
	End If
	Next cnt
	
	Redim Preserve oDataArray(pos-2,0)
	Redim Preserve oStringInfo(pos-2)
	On Error Goto ErrorHandlerTimex
	oDiagSheet = ThisComponent.Sheets.getByName("Отчёты")
	oChart = oDiagSheet.Charts.getByName("Object 7").EmbeddedObject rem ОБЪЕКТ: поставьте правильное имя в функции getByName (Например, Object 1)
	If StrComp(oChart.Title.String,"По времени и приоритету") = 0 Then
		oChart.diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS
		oChart.diagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
		oChart.Data.setData(oDataArray())
		oChart.Data.setRowDescriptions(oStringInfo())
		ChartWorksByTime = "ChartWorksByTime"
	Else
		ChartWorksByTime = "Ошибка: укажите другое имя в поле ОБЪЕКТ"
	End If
	Exit Function
ErrorHandlerTimex:
	rem ChartWorksByTime = "Exception"
End Function

Function FindTasks(ByVal tasks As cell, ByVal str As String) As Integer
	Dim cnt As Integer
	FindTasks = 0
	For cnt = LBound(tasks) To UBound(tasks)
		If InStr(lcase(tasks(cnt,1)),lcase(str)) > 0 Then
			FindTasks = FindTasks + 1
		End If
	Next cnt
End Function

Function FindTasksPercent(ByVal tasks As cell, ByVal percent As cell, ByVal str As String) As Double
	Dim cnt As Integer
	Dim count As Integer
	FindTasksPercent = 0
	count = 0
	For cnt = LBound(tasks) To UBound(tasks)
		If InStr(lcase(tasks(cnt,1)),lcase(str)) > 0 Then
			count = count + 1
			FindTasksPercent = FindTasksPercent + percent(cnt,1)
		End If
	Next cnt
	If count = 0 Then
		count = 1
	End If
	FindTasksPercent = FindTasksPercent / count
End Function

Function FindTasksTime(ByVal tasks As cell, ByVal needtime As cell, ByVal str As String) As Integer
	Dim cnt As Integer
	FindTasksTime = 0
	count = 0
	For cnt = LBound(tasks) To UBound(tasks)
		If InStr(lcase(tasks(cnt,1)),lcase(str)) > 0 Then
			FindTasksTime = FindTasksTime + needtime(cnt,1)
		End If
	Next cnt
End Function

Function DifferentTypes(ByVal evt_date As cell, ByVal priority As cell, ByVal errorcode As cell, ByVal startx As Date, ByVal endx As Date) As String
	Dim overnum, cnt, completenum As Integer
	Dim prior_array(3)
	Dim waste_time As String
	
	Dim oDiagSheet
	Dim oErrors, oCell
	Dim Flags
	Dim tmpVal
	
	waste_time = "Пустая трата"
	overnum = 0
	completenum = 0
	prior_array(0)=0
	prior_array(1)=0
	prior_array(2)=0
	
	On Error Goto ErrorHandlerDT
	oDiagSheet = ThisComponent.Sheets.getByName("ДиаИнфо")
	oErrors = oDiagSheet.getCellRangeByName("C2:C4") rem Очистка 3 ячеек в третьем столбце
	Flags = com.sun.star.sheet.CellFlags.VALUE
	oErrors.clearContents(Flags)
	
	For cnt = LBound(priority) To UBound(priority)
	If (priority(cnt,1) > 0) Then
		If (StrComp(errorcode(cnt,1),waste_time) <> 0) Then
			If (CDate(evt_date(cnt,1))>=CDate(startx)) and (CDate(evt_date(cnt,1))<=CDate(endx)) Then
				rem overnum = overnum + 1
				rem Check for the priority
				prior_array(priority(cnt,1)-1) = prior_array(priority(cnt,1)-1) + 1
			End If
		End If
	Else
		Exit For
	End If
	Next cnt
	
	oCell = oDiagSheet.getCellByPosition(2,1)
	oCell.setValue(prior_array(0))
	oCell = oDiagSheet.getCellByPosition(2,2)
	oCell.setValue(prior_array(1))
	oCell = oDiagSheet.getCellByPosition(2,3)
	oCell.setValue(prior_array(2))
	
	DifferentTypes = cnt
	Exit Function
ErrorHandlerDT:
    DifferentTypes = "Error:"+cnt
End Function

Function NextTask(ByVal evt_name As cell, ByVal percent As cell, ByVal timing As cell, ByVal priority As cell) As String
	Dim cnt As Integer
	Dim max_val As Double
	max_val = 0
	For cnt = LBound(priority) To UBound(priority)
		If (priority(cnt,1)>max_val) and (percent(cnt,1)<100) and (timing(cnt,1)>0) Then
			max_val = priority(cnt,1)
			NextTask = evt_name(cnt,1)
		End If
	Next cnt
End Function

Function GetSum(ByVal trata As Double, ByVal rezerv As Double) As Double
	GetSum = rezerv - trata
	If GetSum < 0 Then
	 	GetSum = 0
	End If
End Function

Function GetSumPercent(ByVal trata As Double, ByVal rezerv As Double, ByVal plan_rashod As Double) As Double
	GetSumPercent = rezerv - trata
	If GetSumPercent < 0 Then
		GetSumPercent = (plan_rashod - trata + rezerv)*100/plan_rashod
	Else
		GetSumPercent = 100
	End If
End Function

rem Budget
Function GetMaxByCategory(ByVal cat As cell, ByVal trata As cell, ByVal compare_cat As String) As Double
	Dim cnt As Integer
	Dim max_val As Double
	max_val = 0
	For cnt = LBound(cat) To UBound(cat)
		If (StrComp(cat(cnt,1), compare_cat) = 0) Then
			If (trata(cnt,1) > max_val) Then
				max_val = trata(cnt,1)
			End If
		End If
	Next cnt
	GetMaxByCategory = max_val
End Function

Function GetCategoryForMax(ByVal cat As cell, ByVal trata As cell, ByVal descr As cell, ByVal compare_cat As String) As String
	Dim cnt As Integer
	Dim max_val As Double
	Dim des As String
	max_val = 0
	For cnt = LBound(cat) To UBound(cat)
		If (StrComp(cat(cnt,1), compare_cat) = 0) Then
			If (trata(cnt,1) > max_val) Then
				max_val = trata(cnt,1)
				des = descr(cnt,1)
			End If
		End If
	Next cnt
	GetCategoryForMax = des
End Function

Function CountFailedTasks(ByVal cat As cell, ByVal cat_templ As String, ByVal percent As cell) As Integer
	Dim cnt As Integer
	CountFailedTasks = 0
	For cnt = LBound(cat) To UBound(cat)
		If (StrComp(cat(cnt,1), cat_templ) = 0) Then
			If percent(cnt,1) < 100 Then
				CountFailedTasks = CountFailedTasks + 1
			End If
		End If
	Next cnt
End Function