1*b1cdbd2cSJim Jagielski<?xml version="1.0" encoding="UTF-8"?> 2*b1cdbd2cSJim Jagielski<!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd"> 3*b1cdbd2cSJim Jagielski<!--*********************************************************** 4*b1cdbd2cSJim Jagielski * 5*b1cdbd2cSJim Jagielski * Licensed to the Apache Software Foundation (ASF) under one 6*b1cdbd2cSJim Jagielski * or more contributor license agreements. See the NOTICE file 7*b1cdbd2cSJim Jagielski * distributed with this work for additional information 8*b1cdbd2cSJim Jagielski * regarding copyright ownership. The ASF licenses this file 9*b1cdbd2cSJim Jagielski * to you under the Apache License, Version 2.0 (the 10*b1cdbd2cSJim Jagielski * "License"); you may not use this file except in compliance 11*b1cdbd2cSJim Jagielski * with the License. You may obtain a copy of the License at 12*b1cdbd2cSJim Jagielski * 13*b1cdbd2cSJim Jagielski * http://www.apache.org/licenses/LICENSE-2.0 14*b1cdbd2cSJim Jagielski * 15*b1cdbd2cSJim Jagielski * Unless required by applicable law or agreed to in writing, 16*b1cdbd2cSJim Jagielski * software distributed under the License is distributed on an 17*b1cdbd2cSJim Jagielski * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 18*b1cdbd2cSJim Jagielski * KIND, either express or implied. See the License for the 19*b1cdbd2cSJim Jagielski * specific language governing permissions and limitations 20*b1cdbd2cSJim Jagielski * under the License. 21*b1cdbd2cSJim Jagielski * 22*b1cdbd2cSJim Jagielski ***********************************************************--> 23*b1cdbd2cSJim Jagielski<script:module xmlns:script="http://openoffice.org/2000/script" script:name="tools" script:language="StarBasic">REM ***** BASIC ***** 24*b1cdbd2cSJim JagielskiOption Explicit 25*b1cdbd2cSJim Jagielski 26*b1cdbd2cSJim JagielskiSub RemoveSheet() 27*b1cdbd2cSJim Jagielski If oSheets.HasbyName("Link") then 28*b1cdbd2cSJim Jagielski oSheets.RemovebyName("Link") 29*b1cdbd2cSJim Jagielski End If 30*b1cdbd2cSJim JagielskiEnd Sub 31*b1cdbd2cSJim Jagielski 32*b1cdbd2cSJim Jagielski 33*b1cdbd2cSJim JagielskiSub InitializeStatusLine(StatusText as String, MaxValue as Integer, FirstValue as Integer) 34*b1cdbd2cSJim Jagielski oStatusline = oDocument.GetCurrentController.GetFrame.CreateStatusIndicator() 35*b1cdbd2cSJim Jagielski oStatusLine.Start(StatusText, MaxValue) 36*b1cdbd2cSJim Jagielski oStatusline.SetValue(FirstValue) 37*b1cdbd2cSJim JagielskiEnd Sub 38*b1cdbd2cSJim Jagielski 39*b1cdbd2cSJim Jagielski 40*b1cdbd2cSJim JagielskiSub MakeRangeVisible(oSheet as Object, RangeName as String, BIsVisible as Boolean) 41*b1cdbd2cSJim JagielskiDim oRangeAddress, oColumns as Object 42*b1cdbd2cSJim JagielskiDim i, iStartColumn, iEndColumn as Integer 43*b1cdbd2cSJim Jagielski oRangeAddress = oSheet.GetCellRangeByName(RangeName).RangeAddress 44*b1cdbd2cSJim Jagielski iStartColumn = oRangeAddress.StartColumn 45*b1cdbd2cSJim Jagielski iEndColumn = oRangeAddress.EndColumn 46*b1cdbd2cSJim Jagielski oColumns = oSheet.Columns 47*b1cdbd2cSJim Jagielski For i = iStartColumn To iEndColumn 48*b1cdbd2cSJim Jagielski oSheet.Columns(i).IsVisible = bIsVisible 49*b1cdbd2cSJim Jagielski Next i 50*b1cdbd2cSJim JagielskiEnd Sub 51*b1cdbd2cSJim Jagielski 52*b1cdbd2cSJim Jagielski 53*b1cdbd2cSJim JagielskiFunction GetRowIndex(oSheet as Object, RowName as String) 54*b1cdbd2cSJim JagielskiDim oRange as Object 55*b1cdbd2cSJim Jagielski oRange = oSheet.GetCellRangeByName(RowName) 56*b1cdbd2cSJim Jagielski GetRowIndex = oRange.RangeAddress.StartRow 57*b1cdbd2cSJim JagielskiEnd Function 58*b1cdbd2cSJim Jagielski 59*b1cdbd2cSJim Jagielski 60*b1cdbd2cSJim JagielskiFunction GetTransactionCount(iStartRow as Integer) 61*b1cdbd2cSJim JagielskiDim iEndRow as Integer 62*b1cdbd2cSJim Jagielski iStartRow = GetRowIndex(oMovementSheet, "ColumnsToHide") 63*b1cdbd2cSJim Jagielski iEndRow = GetRowIndex(oMovementSheet, "HiddenRow3" ) 64*b1cdbd2cSJim Jagielski GetTransactionCount = iEndRow -iStartRow - 2 65*b1cdbd2cSJim JagielskiEnd Function 66*b1cdbd2cSJim Jagielski 67*b1cdbd2cSJim Jagielski 68*b1cdbd2cSJim JagielskiFunction GetStocksCount(iStartRow as Integer) 69*b1cdbd2cSJim JagielskiDim iEndRow as Integer 70*b1cdbd2cSJim Jagielski iStartRow = GetRowIndex(oFirstSheet, "HiddenRow1") 71*b1cdbd2cSJim Jagielski iEndRow = GetRowIndex(oFirstSheet, "HiddenRow2") 72*b1cdbd2cSJim Jagielski GetStocksCount = iEndRow -iStartRow - 1 73*b1cdbd2cSJim JagielskiEnd Function 74*b1cdbd2cSJim Jagielski 75*b1cdbd2cSJim Jagielski 76*b1cdbd2cSJim JagielskiFunction FillListbox(ListboxControl as Object, MsgTitle as String, bShowMessage) as Boolean 77*b1cdbd2cSJim JagielskiDim i, StocksCount as Integer 78*b1cdbd2cSJim JagielskiDim iStartRow as Integer 79*b1cdbd2cSJim JagielskiDim oCell as Object 80*b1cdbd2cSJim Jagielski ' Add stock names to empty list box 81*b1cdbd2cSJim Jagielski StocksCount = GetStocksCount(iStartRow) 82*b1cdbd2cSJim Jagielski If StocksCount > 0 Then 83*b1cdbd2cSJim Jagielski ListboxControl.Model.StringItemList() = NullList() 84*b1cdbd2cSJim Jagielski For i = 1 To StocksCount 85*b1cdbd2cSJim Jagielski oCell = oFirstSheet.GetCellByPosition(SBCOLUMNNAME1,iStartRow + i) 86*b1cdbd2cSJim Jagielski ListboxControl.AddItem(oCell.String, i-1) 87*b1cdbd2cSJim Jagielski Next 88*b1cdbd2cSJim Jagielski FillListbox() = True 89*b1cdbd2cSJim Jagielski Else 90*b1cdbd2cSJim Jagielski If bShowMessage Then 91*b1cdbd2cSJim Jagielski Msgbox(sInsertStockName, 16, MsgTitle) 92*b1cdbd2cSJim Jagielski FillListbox() = False 93*b1cdbd2cSJim Jagielski End If 94*b1cdbd2cSJim Jagielski End If 95*b1cdbd2cSJim JagielskiEnd Function 96*b1cdbd2cSJim Jagielski 97*b1cdbd2cSJim Jagielski 98*b1cdbd2cSJim JagielskiSub CellValuetoControl(oSheet, oControl as Object, CellName as String) 99*b1cdbd2cSJim JagielskiDim oCell as Object 100*b1cdbd2cSJim JagielskiDim StringValue 101*b1cdbd2cSJim Jagielski oCell = GetCellByName(oSheet, CellName) 102*b1cdbd2cSJim Jagielski If oControl.PropertySetInfo.HasPropertyByName("EffectiveValue") Then 103*b1cdbd2cSJim Jagielski oControl.EffectiveValue = oCell.Value 104*b1cdbd2cSJim Jagielski Else 105*b1cdbd2cSJim Jagielski oControl.Value = oCell.Value 106*b1cdbd2cSJim Jagielski End If 107*b1cdbd2cSJim Jagielski' If oCell.FormulaResultType = 1 Then 108*b1cdbd2cSJim Jagielski' StringValue = oNumberFormatter.GetInputString(oCell.NumberFormat, oCell.Value) 109*b1cdbd2cSJim Jagielski' oControl.Text = DeleteStr(StringValue, "%") 110*b1cdbd2cSJim Jagielski' Else 111*b1cdbd2cSJim Jagielski' oControl.Text = oCell.String 112*b1cdbd2cSJim Jagielski' End If 113*b1cdbd2cSJim JagielskiEnd Sub 114*b1cdbd2cSJim Jagielski 115*b1cdbd2cSJim Jagielski 116*b1cdbd2cSJim JagielskiSub RemoveStockRows(oSheet as Object, iStartRow, RowCount as Integer) 117*b1cdbd2cSJim Jagielski If RowCount > 0 Then 118*b1cdbd2cSJim Jagielski oSheet.Rows.RemoveByIndex(iStartRow, RowCount) 119*b1cdbd2cSJim Jagielski End If 120*b1cdbd2cSJim JagielskiEnd Sub 121*b1cdbd2cSJim Jagielski 122*b1cdbd2cSJim Jagielski 123*b1cdbd2cSJim JagielskiSub AddValueToCellContent(iCellCol, iCellRow as Integer, AddValue) 124*b1cdbd2cSJim JagielskiDim oCell as Object 125*b1cdbd2cSJim JagielskiDim OldValue 126*b1cdbd2cSJim Jagielski oCell = oMovementSheet.GetCellByPosition(iCellCol, iCellRow) 127*b1cdbd2cSJim Jagielski OldValue = oCell.Value 128*b1cdbd2cSJim Jagielski oCell.Value = OldValue + AddValue 129*b1cdbd2cSJim JagielskiEnd Sub 130*b1cdbd2cSJim Jagielski 131*b1cdbd2cSJim Jagielski 132*b1cdbd2cSJim JagielskiSub CheckInputDate(aEvent as Object) 133*b1cdbd2cSJim JagielskiDim oRefDialog as Object 134*b1cdbd2cSJim JagielskiDim oRefModel as Object 135*b1cdbd2cSJim JagielskiDim oDateModel as Object 136*b1cdbd2cSJim Jagielski oDateModel = aEvent.Source.Model 137*b1cdbd2cSJim Jagielski oRefModel = DlgReference.GetControl("cmdGoOn").Model 138*b1cdbd2cSJim Jagielski oRefModel.Enabled = oDateModel.Date <> 0 139*b1cdbd2cSJim JagielskiEnd Sub 140*b1cdbd2cSJim Jagielski 141*b1cdbd2cSJim Jagielski 142*b1cdbd2cSJim Jagielski 143*b1cdbd2cSJim Jagielski' Updates the cell with the CurrentValue after checking if the 144*b1cdbd2cSJim Jagielski' Newdate is later than the one that is refered to in the annotation 145*b1cdbd2cSJim Jagielski' of the cell 146*b1cdbd2cSJim JagielskiSub InsertCurrentValue(CurValue as Double, iRow as Integer, Newdate as Date) 147*b1cdbd2cSJim JagielskiDim oCell as Object 148*b1cdbd2cSJim JagielskiDim OldDate as Date 149*b1cdbd2cSJim Jagielski oCell = oFirstSheet.GetCellByPosition(SBCOLUMNRATE1, iRow) 150*b1cdbd2cSJim Jagielski OldDate = CDate(oCell.Annotation.Text.String) 151*b1cdbd2cSJim Jagielski If NewDate >= OldDate Then 152*b1cdbd2cSJim Jagielski oCell.SetValue(CurValue) 153*b1cdbd2cSJim Jagielski oCell.Annotation.Text.SetString(CStr(NewDate)) 154*b1cdbd2cSJim Jagielski End If 155*b1cdbd2cSJim JagielskiEnd Sub 156*b1cdbd2cSJim Jagielski 157*b1cdbd2cSJim Jagielski 158*b1cdbd2cSJim JagielskiSub SplitCellValue(oSheet, FirstNumber, SecondNumber, iCol, iRow, NoteText) 159*b1cdbd2cSJim JagielskiDim oCell as Object 160*b1cdbd2cSJim JagielskiDim OldValue 161*b1cdbd2cSJim Jagielski oCell = oSheet.GetCellByPosition(iCol, iRow) 162*b1cdbd2cSJim Jagielski OldValue = oCell.Value 163*b1cdbd2cSJim Jagielski oCell.Value = OldValue * FirstNumber / SecondNumber 164*b1cdbd2cSJim Jagielski If NoteText <> "" Then 165*b1cdbd2cSJim Jagielski oCell.Annotation.SetString(NoteText) 166*b1cdbd2cSJim Jagielski End If 167*b1cdbd2cSJim JagielskiEnd Sub 168*b1cdbd2cSJim Jagielski 169*b1cdbd2cSJim Jagielski 170*b1cdbd2cSJim JagielskiFunction GetStockRowIndex(ByVal Stockname) as Integer 171*b1cdbd2cSJim JagielskiDim i, StocksCount as Integer 172*b1cdbd2cSJim JagielskiDim iStartRow as Integer 173*b1cdbd2cSJim JagielskiDim oCell as Object 174*b1cdbd2cSJim Jagielski StocksCount = GetStocksCount(iStartRow) 175*b1cdbd2cSJim Jagielski For i = 1 To StocksCount 176*b1cdbd2cSJim Jagielski oCell = oFirstSheet.GetCellByPosition(SBCOLUMNNAME1,iStartRow + i) 177*b1cdbd2cSJim Jagielski If oCell.String = Stockname Then 178*b1cdbd2cSJim Jagielski GetStockRowIndex = iStartRow + i 179*b1cdbd2cSJim Jagielski Exit Function 180*b1cdbd2cSJim Jagielski End If 181*b1cdbd2cSJim Jagielski Next 182*b1cdbd2cSJim Jagielski GetStockRowIndex = -1 183*b1cdbd2cSJim JagielskiEnd Function 184*b1cdbd2cSJim Jagielski 185*b1cdbd2cSJim Jagielski 186*b1cdbd2cSJim JagielskiFunction GetStockID(StockName as String, Optional iFirstRow as Integer) as String 187*b1cdbd2cSJim JagielskiDim CellStockName as String 188*b1cdbd2cSJim JagielskiDim i as Integer 189*b1cdbd2cSJim JagielskiDim iCount as Integer 190*b1cdbd2cSJim JagielskiDim iLastRow as Integer 191*b1cdbd2cSJim Jagielski If IsMissing(iFirstRow) Then 192*b1cdbd2cSJim Jagielski iFirstRow = GetRowIndex(oFirstSheet, "HiddenRow1") 193*b1cdbd2cSJim Jagielski End If 194*b1cdbd2cSJim Jagielski iCount = GetStocksCount(iFirstRow) 195*b1cdbd2cSJim Jagielski iLastRow = iFirstRow + iCount 196*b1cdbd2cSJim Jagielski For i = iFirstRow To iLastRow 197*b1cdbd2cSJim Jagielski CellStockName = oFirstSheet.GetCellByPosition(SBCOLUMNNAME1, i).String 198*b1cdbd2cSJim Jagielski If CellStockname = StockName Then 199*b1cdbd2cSJim Jagielski Exit For 200*b1cdbd2cSJim Jagielski End If 201*b1cdbd2cSJim Jagielski Next i 202*b1cdbd2cSJim Jagielski If i > iLastRow Then 203*b1cdbd2cSJim Jagielski GetStockID() = "" 204*b1cdbd2cSJim Jagielski Else 205*b1cdbd2cSJim Jagielski If Not IsMissing(iFirstRow) Then 206*b1cdbd2cSJim Jagielski iFirstRow = i 207*b1cdbd2cSJim Jagielski End If 208*b1cdbd2cSJim Jagielski GetStockID() = oFirstSheet.GetCellByPosition(SBCOLUMNID1, i).String 209*b1cdbd2cSJim Jagielski End If 210*b1cdbd2cSJim JagielskiEnd Function 211*b1cdbd2cSJim Jagielski 212*b1cdbd2cSJim Jagielski 213*b1cdbd2cSJim JagielskiFunction CheckDocLocale(LocLanguage as String, LocCountry as String) 214*b1cdbd2cSJim JagielskiDim bIsDocLanguage as Boolean 215*b1cdbd2cSJim JagielskiDim bIsDocCountry as Boolean 216*b1cdbd2cSJim Jagielski bIsDocLanguage = Instr(1, LocLanguage, sDocLanguage, SBBINARY) <> 0 217*b1cdbd2cSJim Jagielski bIsDocCountry = Instr(1, LocCountry, sDocCountry, SBBINARY) <> 0 OR SDocCountry = "" 218*b1cdbd2cSJim Jagielski CheckDocLocale = (bIsDocLanguage And bIsDocCountry) 219*b1cdbd2cSJim JagielskiEnd Function 220*b1cdbd2cSJim Jagielski</script:module> 221