CylinderAutomation.xba - lobasic-macros

1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
40 
41 
42 
43 
44 
45 
46 
47 
48 
49 
50 
51 
52 
53 
54 
55 
56 
57 
58 
59 
60 
61 
62 
63 
64 
65 
66 
67 
68 
69 
70 
71 
72 
73 
74 
75 
76 
77 
78 
79 
80 
81 
82 
83 
84 
85 
86 
87 
88 
89 
90 
91 
92 
93 
94 
95 
96 
97 
98 
99 
100 
101 
102 
103 
104 
105 
106 
107 
108 
109 
110 
111 
112 
113 
114 
115 
116 
117 
118 
119 
120 
121 
122 
123 
124 
125 
126 
127 
128 
129 
130 
131 
132 
133 
134 
135 
136 
137 
138 
139 
140 
141 
142 
143 
144 
145 
146 
147 
148 
149 
150 
151 
152 
153 
154 
155 
156 
157 
158 
159 
160 
161 
162 
163 
164 
165 
166 
167 
168 
169 
170 
171 
172 
173 
174 
175 
176 
177 
178 
179 
180 
181 
182 
183 
184 
185 
186 
187 
188 
189 
190 
191 
192 
193 
194 
195 
196 
197 
198 
199 
200 
201 
202 
203 
204 
205 
206 
207 
208 
209 
210 
211 
212 
213 
214 
215 
216 
217 
218 
219 
220 
221 
222 
223 
224 
225 
226 
227 
228 
229 
230 
231 
232 
233 
234 
235 
236 
237 
238 
239 
240 
241 
242 
243 
244 
245 
246 
247 
248 
249 
250 
251 
252 
253 
254 
255 
256 
257 
258 
259 
260 
261 
262 
263 
264 
265 
266 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd">
<script:module xmlns:script="http://openoffice.org/2000/script" script:name="CylinderAutomation" script:language="StarBasic">REM  *****  BASIC  *****
&apos;Option VBASupport 1
Option Explicit

Global Const rowsToSkip = 1
Global Const roughHeaderMatch = True
Global Const highlightBasedOn = &quot;Payment Option&quot; &apos; A yellow background is drawn that matches this column and &apos;highlightSearchString&apos;
Global Const highlightSearchString = &quot;Online&quot; &apos; Does partial match
Global Const highlightRemoveColumn = True &apos; True if you want remove the column once the highlight is done
Global Const reportType = &quot;PENDING&quot; &apos; Allowed values are &quot;PENDING&quot;, &quot;SAFETY&quot; and &quot;SBC&quot;. The number of columns that are copied and the column size will be affected
Global Const shouldExportPDF = True &apos; If True this will export all the created sheets with Area specific data individually TODO change name to easily understand
Global Const badDayThreshold = 4 &apos; The days below &apos;the maximum date in report&apos; to show as bad
Global Const shouldSegregateAreaWise = True&apos; True if you want to create sheets with Area specific data
Global Const exportFolderPrefix = &quot;C:\Users\bhara\Sync\&quot;

&apos; Values are loaded from the document
Private tillDate as Date
Private tillDateStr as String
Private sheetNameSuffix as String
Private exportFolder as String

&apos;Private shouldExportSummaryPDF as Boolean&apos; If True the first page will be exported as PDF

Sub Main
	Dim oDoc : oDoc = ThisComponent
	Dim s as Object
	Dim cursor as Object
	Dim c as Integer
	Dim destSheet as Variant
	Dim sheetName$, d$, a$
	Dim areaColumn%, areaNames as New Collection
	Dim startColumn%, startRow%, endColumn%, endRow%
	Dim rowsToRemove%, iArea%
	Dim areaRange, idRange, bookRange
	Dim dColumns as Object
	Dim headerRange as Object
	Dim cellRangeToCopy as Object
	Dim pageStyle as Object
	Dim oCellStyles
	Dim oConFormat
	Dim oCondition(2) as new com.sun.star.beans.PropertyValue
	Dim T4Style

	&apos; Initialize Globals
	&apos; Casting to Long removes the time component
	tillDate = CLng(Now())

	s = oDoc.Sheets(0)
	cursor = UsedRangeCursor(s)

	&apos; NaiveLastTable gives us the last non-blank table&apos;s
	rowsToRemove = NaiveLastTable(s)
	If rowsToRemove &lt;&gt; 0 Then
		s.getRows().removeByIndex(0, rowsToRemove + 1)
	End If

	startRow = 1
	startColumn = 0
	endRow = cursor.RangeAddress.EndRow
	endColumn = cursor.RangeAddress.EndColumn

	&apos; TODO move this to knowledge base
	oCellStyles = ThisComponent.StyleFamilies(&quot;CellStyles&quot;)
	If Not oCellStyles.hasByName(&quot;T4&quot;) Then
		T4Style = oDoc.createInstance(&quot;com.sun.star.style.CellStyle&quot;)
		oCellStyles.insertByName(&quot;T4&quot;, T4Style)
		oCellStyles.getByName(&quot;T4&quot;).CellBackColor = RGB(255, 0, 0)
	End If

	If ThisComponent.CurrentController.hasFrozenPanes() Then
		Call UnFreezeSelection
	End If
	
	&apos; TODO pass the column name/index as an argument
	&apos; Transformations to be applied to the Main sheet before splitting the sheet by Area Name
	cursor = UsedRangeCursor(s)
	
	Call CleanColumnHeaders(s, endColumn)
	
	areaColumn = GetHeaderPosition(s, endColumn, &quot;Area&quot;)
	Call ShortenDirections(s, areaColumn)

	If Left(reportType, 1) = &quot;P&quot; Then
		Call ApplyTheme &quot;millennium.ots&quot;
		endColumn = RemoveColumnsExcept(s, endColumn, Array( _
			&quot;Area&quot;, _
			&quot;ID&quot;, _
			&quot;Name&quot;, _
			&quot;Mobile&quot;, _
			&quot;Book&quot;, _
			&quot;Payment Option&quot; _
		))
		
		endColumn = HighlightRowWithColumn(s, endColumn, endRow, _
			&quot;Payment Option&quot;, &quot;Online Payment&quot;, True, _
			RGB(255, 255, 0))

		&apos; Justify Leftmost cells to left and Rightmost cells to right

		bookRange = GetColumnRange(s, endColumn, endRow, &quot;Book&quot;)
		bookRange.setPropertyValue(&quot;HoriJustify&quot;, com.sun.star.table.CellHoriJustify.RIGHT)

		FormatRangeAsNumber(s, new com.sun.star.lang.Locale, oDoc.getNumberFormats(), bookRange, &quot;DD/MM&quot;)
		
		tillDate = FindHighestDateAsString(s, &quot;Book&quot;, endColumn, endRow)

		With oCondition(0) : .Name = &quot;Operator&quot; : .Value = com.sun.star.sheet.ConditionOperator.LESS_EQUAL : End With
		oCondition(1).Name = &quot;Formula1&quot; : oCondition(1).Value = CLng(tillDate - badDayThreshold)
	    With oCondition(2) : .Name = &quot;StyleName&quot; : .Value = &quot;T4&quot; : End With
		oConFormat = bookRange.ConditionalFormat
		oConFormat.clear() : oConFormat.addNew(oCondition)

		SetWidths(s, endColumn, Array( _
				Array(&quot;Area&quot;, 0), _
				Array(&quot;ID&quot;, 0), _
				Array(&quot;Name&quot;, 7000), _
				Array(&quot;Mobile&quot;, 0), _
				Array(&quot;Book&quot;, 0) _
		))
	ElseIf reportType = &quot;SBC&quot; Then
		Call ApplyTheme &quot;pumpkin.ots&quot;
		endColumn = RemoveColumnsExcept(s, endColumn, Array( _
			&quot;ID&quot;, _
			&quot;Name&quot;, _
			&quot;Address&quot;, _
			&quot;Mobile&quot;, _
			&quot;Area&quot;, _
			&quot;No Of Cylinder&quot; _
			))
			SetWidths(s, endColumn, Array( _
				Array(&quot;ID&quot;, 0), _
				Array(&quot;Name&quot;, 3000), _
				Array(&quot;Address&quot;, 8000), _
				Array(&quot;Mobile&quot;, 0), _
				Array(&quot;Area&quot;, 0) _
			))
	End If
		
	idRange = GetColumnRange(s	, endColumn, endRow, &quot;ID&quot;) _
		.setPropertyValue(&quot;HoriJustify&quot;, com.sun.star.table.CellHoriJustify.LEFT)

	Call SortColumn(s, endColumn, endRow, &quot;Area&quot;)

	&apos; Setup exportFolder and sheetName
	tillDateStr = Replace(CDate(tillDate), &quot;/&quot;, &quot;-&quot;)
	tillDateStr = Trim(tillDateStr)
	sheetNameSuffix = &quot; - &quot; &amp; reportType &amp; &quot; &quot; &amp; tillDateStr
	exportFolder = exportFolderPrefix &amp; &quot;/&quot; &amp; reportType &amp; &quot; &quot; &amp; tillDateStr

	headerRange = s.getCellRangeByPosition(0, 0, endColumn, 0)
	headerRange.CellStyle = &quot;Heading 1&quot;
		
	pageStyle = ThisComponent.StyleFamilies.getByName(&quot;PageStyles&quot;).getByName(&quot;Default&quot;)
	pageStyle.setPropertyValue(&quot;PrintGrid&quot;, True)
	pageStyle.setPropertyValue(&quot;CenterHorizontally&quot;, True)
	&apos; Make the margins 0.2&quot; thick	
	
	pageStyle.setPropertyValue(&quot;LeftMargin&quot;, 0.2 * 2540)
	pageStyle.setPropertyValue(&quot;RightMargin&quot;, 0.2 * 2540)

	&apos; Change original sheet name
	s.Name = &quot;SUMMARY&quot; &amp; sheetNameSuffix

	&apos; Export summary
	If shouldExportPDF Then
		ExportPDF(s, exportFolder)
	End If

	If NOT shouldSegregateAreaWise Then Exit Sub
	
	Dim statusBar
	statusBar = oDoc.CurrentController.StatusIndicator
	statusBar.start(&quot;Creating Area Wise&quot;, 10)

	areaRange = s.getCellRangeByPosition(areaColumn, startRow, areaColumn, endRow)
	a = areaRange.getCellByPosition(0, startRow).getString()
	&apos;On Error Goto ErrorHandler
	For iArea = startRow To endRow - 1
		d = areaRange.getCellByPosition(0, iArea).getString()
		If  d = &quot;&quot; Then
			Exit For
		End If

		If a &lt;&gt; d Or  iArea = (endRow - 1) Then
			 statusBar.setValue((iArea / endRow) * 100)
			&apos; FIXME Get the columns automatically from the sheet
			&apos; FIXME Covert the end column from endColumn above

			Goto Con:
			Dim cName
			s.Rows.insertByIndex(startRow, 1)
			cName = s.getCellByPosition(startColumn, startRow)
			cName.setString(d)
			startRow = startRow + 1
			endRow = endRow + 1
			
			Con:
			&apos; TODO maybe there is an elegant way than
			&apos; writing this two times
			sheetName = a &amp; sheetNameSuffix

			&apos; TODO subtotals might solve this
			&apos;s.group(areaRange.RangeAddress, 1)

			&apos; Prepare Destination sheet
			destSheet = NewSheet(sheetName)
			
			
			&apos; Copy the Headers from the Main Document
			If areaColumn &gt; 0 And areaColumn &lt; endColumn Then
				MsgBox &quot;Can&apos;t have Area column in the middle&quot;, 16
				Exit Sub
			End If

			&apos; start                     end
			&apos;1 :                           endColumn          0 4
			&apos;areaColumn + 1 : endColumn           2  4
			&apos;0 :                           endColumn - 1     4  4
							
			&apos; Copy Header
			s.copyRange(destSheet.getCellRangeByName(&quot;A1&quot;).CellAddress, _
				s.getCellRangeByPosition( _
				(endColumn - areaColumn)/ endColumn, _
				0, endcolumn - CInt(areaColumn/endColumn), 0).RangeAddress)

			&apos; Copy Contents
			s.copyRange(destSheet.getCellRangeByName(&quot;A2&quot;).CellAddress, _
				s.getCellRangeByPosition(_
				(endColumn - areaColumn)/ endColumn, _
				startRow, _
				endcolumn - CInt(areaColumn/endColumn), _
				iArea).RangeAddress)
			
		    &apos; Customize the Destination Sheet&apos;s Columns
			If  Left(reportType, 1) = &quot;P&quot; Then
					SetWidths(destSheet, endColumn, Array( _
						Array(&quot;ID&quot;, 0), _
						Array(&quot;Name&quot;, 7000), _
						Array(&quot;Mobile&quot;, 0), _
						Array(&quot;Book&quot;, 0) _
					))
			ElseIf reportType = &quot;SBC&quot; Then
						SetWidths(destSheet, endColumn, Array( _
							Array(&quot;ID&quot;, 0), _
							Array(&quot;Name&quot;, 4000), _
							Array(&quot;Address&quot;, 11000), _
							Array(&quot;Mobile&quot;, 0) _
						))
			End If
	
			If shouldExportPDF Then
				ExportPDF(destSheet, exportFolder)
			End If
			Continue:
			startRow = (iArea + 1)
			a = d
		End If
	Next iArea

	ErrorHandler:
		statusBar.end()
	Reset
End Sub
</script:module>