Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form. 10

german12 · Apr 17, 2023

I have a form with a search-list (incremental search).
Works fine.
I only have to type 1 to 3 letters - and I'm already on the right line - and with one click a browse window opens and shows me detailed results from a table.

This is of course easier to represent than to a GRID on the form.
You can also navigate in a browse window without writing any code oder filling headers etc.

My question:
Can a reduced BROWSE window be shown on the form at the same time if there is still enough space on the form?
Or - can a BROWSE windows can only be displayed on their own - but this means that the BROWSE window must first be closed manually in order to get back to the search list on the form.

I hope this question is not too stupid.
Up to now I always tryed to avoid GRIDS - but may be that this "fear" is exaggerated

I am very interested in your opinion.

Klaus

Peace worldwide - it starts here...

Chris Miller · Apr 19, 2023

One thought I had is why you'd suddenly want that list of distinct values. If you previously used a browse and picked from that, then closed and took the currently selected record as what is picked, well, use a listbox for such a selection or a combobox. What kind of user interface is that, where you pop up window, pick a row, then need to close this window, if you can pick it with one click from a listbox instead? You could pt a combobox above column1 and use the selected item of it to filter your aktien2 workarea. So in this case, make use of a combobox as picking what to filter.

Chriss

mjcmkrsr · Apr 20, 2023

Hi Klaus,

Don't use DISTINCT. From VFP Hacker's Guide

The ALL and DISTINCT keywords determine whether the result set contains every record found or only a unique set of records. When you specify DISTINCT, every field is compared; records that exactly match another record in the set are eliminated, so that each unique combination appears only once. If this sounds slow, it's because it is slow. Since you rarely want to match up every single field, you're usually better off culling duplicates with GROUP BY.

Furthermore I hope your DBF is indexed on ak2begriff - if not please do so. If you then = INDEXSEEK(...) or = SEEK(...) or SEEK (the command) the record pointer jumps to the 1st record found and highlights it in the grid - the subsequent records are shown below. Please have a look at he code I already posted or the one below (a slightly pimped up version of the former one).

Code:

LOCAL loForm
 
loForm = CREATEOBJECT("clssearch")
loForm.Show()

Read EVENTS

CLOSE ALL

CLEAR ALL 

RETURN 

***********
 
DEFINE CLASS clssearch AS form
 
	Top = 12
	Left = 12
	Height = 474
	Width = 1050
	MinWidth = This.Width
	Caption = "Incremental Search"
	WindowState = 0
	AutoCenter = .T.
	Name = "clssearch"
	Themes = .F.
	ShowTips = .T.
	
	ADD OBJECT lblName as Label WITH ;
		Left = 12, Top = 12, Caption = "Item to search :", FontBold = .T.
	
	ADD OBJECT lblFound as Label WITH ;
		Left = 270, Top = 12, Autosize = .T., Caption = "Item highlighted :", FontBold = .T.
	
	ADD OBJECT lblItemName as Label WITH ;
		Left = 270, Top = 36, AutoSize = .T., Caption = "", FontBold = .T., FontItalic = .T., FontSize = 12

	ADD OBJECT txtBox as TextBox WITH ;
		Left = 12, Top = 36, Format = "!", ToolTipText = "Double-click to clear textbox"
		
		PROCEDURE txtBox.DblClick()
			This.Value = ""

		ENDPROC 
		
		PROCEDURE txtBox.InteractiveChange
			LOCAL lcSearch
			
			lcSearch = ALLTRIM(This.Value)
[highlight #73D216]			= INDEXSEEK(lcSearch, .T., "csrWords")[/highlight]
			
			WITH ThisForm
				.lblItemName.Caption = cWord
				.grdValues.Refresh()
			ENDWITH 
		ENDPROC 

	ADD OBJECT grdValues AS Grid WITH ;
		Left = 12, ;
		Top = 72, ;
		Height = 474 - 72 - 12, ;
		Width = 1050 - 24, ;
		Anchor = 15, ;
		BackColor = RGB(224, 224, 224), ;
		AllowRowSizing = .F., ;
		HeaderHeight = 21, ;
		AllowHeaderSizing = .F., ;
		DeleteMark = .F., ;
		HighLightStyle = 2, ;
		HighlightBackColor = RGB(0, 250, 250), ;
		HighlightForeColor = RGB(0, 0, 0), ;
		ColumnCount = -1, ;
		RowSourceType = 2, ;
		RowSource = "csrWords", ;
		ReadOnly = .T.
		
		PROCEDURE grdValues.Init()

			WITH This
				.SetAll("FontBold", .T., "Header")
				.SetAll("BackColor", RGB(0, 250, 250), "Header")
			ENDWITH 

			WITH This.Column1
				.Width = 246
				.Header1.Caption = "Name"
			ENDWITH

			WITH This.Column2
				.Width = 150
				.Header1.Caption = "Value One"
			ENDWITH
			
			WITH This.Column3
				.Width = 150
				.Header1.Caption = "Value Two"
			ENDWITH

			WITH This.Column4
				.Width = 150
				.Header1.Caption = "VO + VT"
			ENDWITH

			WITH This.Column5
				.Width = 150
				.Header1.Caption = "VO - VT"
			ENDWITH

			WITH This.Column6
				.Width = 150
				.Header1.Caption = "VO + (2 * VT)"
			ENDWITH	
		ENDPROC  
		
		PROCEDURE grdValues.AfterRowColChange()
			LPARAMETERS nColIndex

			WITH ThisForm
				.lblItemName.Caption = cWord
				.Refresh()
			ENDWITH 
		ENDPROC 

	PROCEDURE Load()
		LOCAL lnI, lnY, lnWordLength, lcItem, lnUpper, lnLower

		lnUpper = 90 &&ASCII (Z)
		lnLower = 65 &&ASCII (A)

		CREATE CURSOR csrWords ( cWord C(20), iValOne I, iValTwo I, iValThree I, iValFour I, iValFive I)
		INDEX on cWord TAG tagWord
		SET ORDER to tagWord
  
		FOR lnI = 1 to 1500
			lcItem = ""

			lnWordLength = INT(20 * RAND()) + 1
   
			FOR lnY = 1 TO lnWordLength
				lcItem = lcItem + CHR(INT((lnUpper - lnLower + 1) * RAND()) + lnLower)
			ENDFOR

			INSERT INTO csrWords VALUES (lcItem, RAND() * 5000, RAND() * 1250, 0, 0, 0)
			UPDATE csrWords SET iValThree = iValOne + iValTwo, iValFour = iValOne - iValTwo, iValFive = iValOne + (2 * iValTwo)

		ENDFOR 
	ENDPROC 
	
	PROCEDURE Destroy()
		ThisForm.Release()
		CLEAR Events
			
	ENDPROC              
ENDDEFINE

**********

hth

MarK

Chris Miller · Apr 20, 2023

Another thought I had is if you often need a list of DISTINCT values of one field of a table, that points out you should actually have a table with that list of values and your other data should be related to it with a foreign key. That thought connects back to what I said about how using BROWSE guides you into wrong data structuring without foreign keys.

Chriss

german12 · Apr 20, 2023

Hi Chriss,
once again ...thank you for the many new information and hints how to improve my primitive program.
My knowledge dates back to 23 years ago when I retired.
But now I'm happy to get to know the whole development of VFP and OOP after 2002.
In addition, I also have to remember what I knew better than 20 years ago.
Not so easy when you are already 81 years old.
But that's just by the way....I learn more here than I did all the time before 2002.
That's why I only use my current program "quick and dirty" - it started in 2014 when I noted all sales of securities with the spreadsheet LOTUS 1-2-3, but that was much more tedious than it can be done with a database table today.
The table aktien2.dbf-structure is as follows:

and your assumption is correct - it is not yet indexed. The basic index should be on ak2begriff which is the name of a share, which repeats often in the table aktien2.dbf
and the form aktien2 looks like this:

The file "aktien2" is longer, but I shortened it.
All objects on the form show me stock sales in various groups (I used DISTINCT(ak2term) with summation .
e.g
Sales with gain or loss (ak2ergebni)
in total
by name of the day of the week
by name of a single stock via click(List1)
by calendar years
Etc.
This also works - the (old) browse windows are always used
shown where I also have the grand totals at the top of the title
display each.
But it's still totally old-fashioned - not the style of professionals - and
not friendly either, because of course you have to close the browse window again and again to get back to the form.
But it helps me with purchasing decisions, and I can always see if I'm broke or if it's worth moving on.
BTW - if someone tells you they always win at the stock market - run away quick - it's a liar.

I hope to have explained a litte more detailed, what I want to change.
(of course by using your hints then).
Thank you

Klaus

Peace worldwide - it starts here...

mjcmkrsr · Apr 20, 2023

Hi Klaus,

Your post raises more questions than we probably can help to solve

What struck me first is that "Gesamtergebnis" is the sum of "Gewinn gesamt" + "Verluste" and not the difference.

Furthermore you have a field called "ak2datum" and then fields like "ak2monat", "ak2jahr"... Are you aware that VFP has date/datetime handling functions like

CDoW(), CMonth(), Day(), Month(), Year(), Quarter()

These functions return the character and numeric equivalents of the date or datetime supplied.

Usage
cRetVal = CDOW( dDate | tDateTime )
cRetVal = CMONTH( dDate | tDateTime )
nRetVal = DAY( dDate | tDateTime )
nRetVal = MONTH( dDate | tDateTime )
nRetVal = YEAR( dDate | tDateTime )
nRetVal = QUARTER( dDate | tDateTime [, nFirstMonth ] )

CDoW() and CMonth() display the current day ("Saturday") and month ("December"). The return values are always in Proper format: capitalized first letter, lowercase for the remainder. Use these functions for output only, and not for internal business logic, if your application will be used in multi-lingual settings where one man's Thursday can be another's Donnerstag.

Day(), Month() and Year() return the numeric equivalents of the day of the month, month and year, based on the date or datetime supplied. They are absolute—they are based on the Gregorian calendar, a generally agreed-upon standard. Year() always returns the full year, including century, regardless of the setting of CENTURY. Two other similar functions, DoW() and Week(), are discussed in their own section, as they depend on the system settings of the "first week of the year" and the "first day of the week."

Quarter(), new in VFP 7, returns the numeric quarter of the year for the specified date or datetime. The optional nFirstMonth parameter lets you specify the starting month for the year.

Be forewarned. The functions in this group that return numeric values return them as numbers, not integers. That means that if you SET FIXED ON, you're going to see decimal places in the day, month, year and quarter. Probably not what you expected.

Example
? CDOW(DATE()) && The equivalent of "What day is it?"
? CMONTH(DATETIME()) && and "What month is it?"
? DAY({^ 1995/06/15}) && Returns 15
? MONTH({^1995/06/15}) && Returns 6
? YEAR({^1995/06/15}) && Returns 1995
? QUARTER({^1995/06/15}) && Returns 2
? QUARTER({^1995/06/15}, 7) && Returns 4

and

DoW(), Week()

Returns the numeric values of the day of the week and week of the year, based on the date or datetime supplied. You can optionally specify a starting point as well.

Usage
nDayOfWeek = DOW( dDate | tDateTime [, nFirstDayofWeek ] )
nWeekOfYear = WEEK( dDate | tDateTime [, nFirstWeek ]
[, nFirstDayOfWeek ] )

Parameter
Value
Meaning

dDate | tDateTime
Date or DateTime
The date or datetime value from which to calculate the day of the week or week of the year.

nFirstDayofWeek
Omitted
Use Sunday as the first day. This is for compatibility with older versions of FoxPro.

0
Use the current setting of FDOW.

1 - 7
Use days Sunday - Saturday as the first day.

nFirstWeek
Omitted or 1
The first week includes January 1.

0
Use the current setting of FWEEK.

2
The first week of the new year has four or more days.

3
The first week of the new year falls entirely within the new year.

nDayOfWeek
1 - 7
Indicates which day of the week dDate | tDateTime is.

nWeekOfYear
1 - 53
Indicates which week of the year dDate | tDateTime is.

These functions return the day and week for the supplied date or datetime. The parameters nFirstDayOfWeek and nFirstWeek can be confusing if you haven't worked with them before, and can give less than intuitive results.

Let's try an example to see if your confusion can lead to total befuddlement, er, enlightenment. January 1, 1998 was a Thursday, and your client tells you that they never start a new workweek with two or fewer days, so Thursday and Friday count as the last week of 1997. The WEEK() function, with a parameter of 2, gives you a return value of 53, for the 53rd week of 1997, for January 1st and 2nd.

? WEEK({^1998-01-01},2) && returns 53, the last week of 1997
On the other hand, if your client tells you they always start the new year on a full seven-day week (a good idea if they track production per week), you could use the parameter of 3 so that January 1st and 2nd fall into the 52nd full week of 1997.

? WEEK({^1998-01-01},3) && returns 52, the 52nd and last full
&& 7-day week of 1997

You find them in VFP Hacker's Guide 7 - which you own and hopefully have read - see your thread184-1812467

Finally I think you need to restructure you table(s) and "normalize" it/them, e.g. You don't need a field like ak2ergebnis since this is the difference between ak2gewinn and ak2verlust ...

And last - we can help you (re)coding. But the conception work will be your job, the computed results seems to have strong influence on your decisions where and when to invest your money.

Hope I wasn't too harsh

MarK

mjcmkrsr · Apr 20, 2023

Hi Klaus,

...

I forgot the crucial question: how and where would you like us to help you?

MarK

german12 · Apr 20, 2023

Hi Mark,
Thank you for your inquiry and your valuable information.
it is by no means harsh - I even found them to be friendly.

In communication there was an overlap of questions by me and answers from you and others in the forum,
given.
(this is actually a good sign if everyone replies quickly),
but I didn't have the time to answer everything immediately.

Chriss had already suggested a solution that helps me,
from data condensed in a cursor in a grid.

answered my question to avoid switching between form and old Browse window.
Also all the other features of a Grid given by you and all others will help me to work with grids only in future.

But nevertheless your request/advice is still very useful for me.
This question is justified.
When I wrote it I had in mind 2 columns in the base file - one for wins and one for losses -
but of course it is automatically in the calculation and there are of course profits-losses = result.
May also could be a typo by me.

Thank you also for your hints on the evaluation of calendar dates in months, weeks, days.
I have also used these functions in the program, the base table receives an accounting date manually - and I run this directly into the columns of the base file for cmonth, cdow in the file via the program.
This makes coding easier, and given the size of the table, it takes time
maybe probably under a second.(replace-all command)

I can definitely use the other special features and considerations about calendar dates in your detailed information at some point.

Your last question
My Answer:
I intend to rewrite my programm from scratch and I intend to built in as much as what was suggested for improvement here.
However - when new questions arise, I know that the best help can be found here.

Stay healthy
Regards
Klaus

Peace worldwide - it starts here...

Chris Miller · Apr 21, 2023

The only thing I can directly get from this is that you previously used a BROWSE to display mycursor, the distinct list of ak2begriff values within aktien2.

Using a grid is one step towards having the display within the form itself, reusing the rid is questionable for that matter, I already said what else I'd do, but it's still not clear whether I'm right in my guess you used the browse for picking one of the distinct value. If you do, what is done with this selection is still open.

I think I conclude this way here: I see how to handle the grid helped you here, if you have further questions you'll make further posts.

For the current phase and changing from BROWSE windows to grids, it might be helpful to your form design to have a single grid in the form which only has the purpose of showing the data you otherwise would display by BROWSE. Or are there cases, in which you have two or more BROWSE open at the same time?

I also would conclude that having a grid instead of a browse is only a minor improvement of the overall UI, as it just fixes the position and size of the browse. As I said already I think you're misusing a browse for a listbox or combobox to pick a value, not necessarily as an editing tool. Your query Select DISTINCT(ak2begriff) from aktien2 into cursor mycursor doesn't create a readwrite cursor so BROWSE of mycursor will only display it and you can move the record pointer to one of the mycursor records. That's what a listbox also does on a form, or a grid. A listbox is more appropriate for single column data, though indeed you can also display readonly data with multiple columns in a listbox, too.

For a complete rewrite the first thing I'd look into is the data rerstructuring, not the form restructuring, though. Maybe because I'm used to work on the basis of the right data design first. This is a rewriting straategy you couldn't do in small improvement steps. A new data structuring would require to also do all forms from scratch and not get away with slight modifications towards a final goal.

Chriss

german12 · Apr 21, 2023

Chriss

That is correct.
It is the INIT-Method in List1 (the picklist) with this code where an array was established and filled with distinct(ak2begriff) -

**********************

Code:

*USED = aktien2 

[b]* List1 - Init[/b]

IF NOT DODEFAULT()
	RETURN .F.
ENDIF

IF NOT USED('aktien2')
USE aktien2 IN 0
ENDIF 

*This creates an array of the list values.
LOCAL ARRAY laCompanies[1]

SELECT DISTINCT(ak2begriff) AS company FROM aktien2 INTO array laCompanies
if _TALLY = 0
  THIS.iaData[1] = "(Keine Datensätze gefunden)" && or somesuch
endif 

*This creates an array property for the array data in the control.
THIS.AddProperty("iaData(1)")
*This makes the array property big enough for the data
DIMENSION THIS.iaData[alen(laCompanies,1),alen(laCompanies,2)]

*This copies the array data into the array property.
ACOPY(laCompanies,THIS.iaData,1,-1,1)

*Maybe we need the rowsourcetype. I seem not to have copied that.
THIS.RowSourceType=5
THIS.RowSource="THIS.iaData"

...and in the Click-Event of List1 (the picklist) I wanted to show max. 50 records of a selected share in a browse-window.

Code:

[b]*Click-event of List1[/b]
Public suchwert
Store This.Value To suchwert
Set Talk Off
Select ak2begriff, ak2aktie,ak2stueck,ak2stckprs,ak2ergebni,ak2datum,ak2divi From aktien2 Order By ak2datum Into Cursor ttt Where ak2begriff = suchwert
Wait Window Fullpath(Dbf())
Select ttt
Sum ak2ergebni To gesamtpropapier For ak2begriff = suchwert

**********************************************************************************************
*The following code should avoid that the Browse-Window shows more than 50 movements.
**********************************************************************************************
Store Reccount() To lastrec

Do Case
Case Between(lastrec,1,52)
      Browse Title "Ergebnis dieses Wertpapiers "+ Alltrim(Str(gesamtpropapier))+Fullpath(Dbf())
Case Reccount()>52
      Go Reccount()-52
      Store Recno() To abrecno
      Browse Title "Ergebnis dieses Wertpapiers "+ Alltrim(Str(gesamtpropapier)) For Recno()>abrecno
Endcase
***

As you can see, List1's main purpose is to show unique stock names and the results that go with them -
aktien2.dbf can contain e.g. 5 times the ak2begriff "TESLA" - therefore DISTINCT (in future this would better by using SQL ...GROUP BY)

The other commandbuttons are designed to provide summaries
have to show results by calendar year, or month, or day of the week. etc.

I simply made a practical way of entering new data records with (command-button - use aktien2.dbf - go bottom.)

It's definitely to do all this much more elegant and probably more efficient - and I would be happy to learn that.
It is never too late.

greeting
Klaus

Peace worldwide - it starts here...

mjcmkrsr · Apr 22, 2023

Hi Klaus,

Rainy day - had some spare time. Wrote something for you.

The sample code uses three cursors/tables: one for the shares, one for the buying history and one for the value history. The last two have a similar structure but their functions are totally different. Although the Buy/Sell commandbutton works with RAND() generated data there is no way (yet) to modify the saved data afterwords.

Maybe it gives you some hints. Enjoy.

Code:

LOCAL go_Form 

go_Form = CreateObject("frmForm")
go_Form.Visible = .T.
go_Form.Show

READ Events

CLOSE ALL
CLEAR ALL

*****

DEFINE CLASS frmForm As Form
	DIMENSION aItems[1], aValues[1]
	
	gc_ItemCode = REPLICATE("Z", 12) 
	gi_ActivePage = 1

	Height = 360
	Width = 690
	MaxWidth = This.Width
	MinHeight = This.Height
	MinWidth = This.Width
	MaxButton = .F.
	MinButton = .F.
	Caption = "Shares"
	BackColor = RGB(120, 120, 120)
	AutoCenter = .T.
	ShowTips = .T.
	Themes = .F.

*!*		 ADD OBJECT imgFImage AS image WITH ;
*!*		  	Left = 0, ;
*!*		  	Top = 0, ;
*!*		  	Height = ThisForm.Height, ;
*!*		  	Width = ThisForm.Width, ;
*!*		  	Stretch = 2, ;
*!*		  	Name = "imgFImage", ;
*!*		  	Visible = .T., ;
*!*		  	Picture = "Picture 098.jpg", ;
*!*		  	Anchor = 15

	ADD OBJECT lblItems AS Label WITH ;
		Top = 12, ;
		Left = 12, ;
		Autosize = .T., ;
		BackStyle = 0, ;
		ForeColor = RGB(254, 254, 254), ;
		FontBold = .T.
		
		PROCEDURE lblItems.Refresh()
			This.Caption = IIF(ThisForm.gi_ActivePage != 4, "Please choose Item", "Click on any Items page")
			
		ENDPROC 

	ADD OBJECT lblWinLoss AS Label WITH ;
		Top = 12, ;
		Left = 492, ;
		Autosize = .T., ;
		BackStyle = 0, ;
		FontBold = .T., ;
		FontItalic = .T., ;
		FontSize = 12, ;
		ForeColor = RGB(255, 255, 255), ;
		Caption = ""

	ADD OBJECT cmdBuy as CommandButton WITH ;
		Top = 36, ;
		Left = 378, ;
		Height = 24, ;
		Caption = "Buy/Sell", ;
		Enabled = .F.
		
		PROCEDURE cmdBuy.Refresh()
			IF ThisForm.gc_ItemCode = REPLICATE("Z", 12) 
				This.Enabled = .F.
				This.ToolTipText = "Please choose Item"
			ELSE
				This.Enabled = .T.
				This.ToolTipText = "Click to buy/sell"
			ENDIF
		ENDPROC 
		
		PROCEDURE cmdBuy.Click()
			LOCAL liAnswer as Integer, liQuantity as Integer, lcISIN as Character, lnValue as Number(7,2)
			LOCAL ARRAY laCheck[1]
			
			lcISIN = ThisForm.gc_ItemCode
			liQuantity = INT((RAND() - 0.5) * 25)
			liValue = RAND() * 200

			liAnswer = MESSAGEBOX("Do you want to buy/sell " + ALLTRIM(STR(liQuantity)) + " shares " + lcISIN + " for " + TRANSFORM(liValue, "999.99") + " € ?", 4 + 32, "Buy/Sell Shares")

			IF liAnswer = 6
				SELECT cISIN, dInDate ;
					FROM Invest_In ;
					WHERE cISIN = lcISIN AND TTOD(dInDate) = TTOD(DATETIME() - 86400) ;
					INTO ARRAY laCheck
					
				IF ALEN(laCheck) = 1 AND VARTYPE(laCheck[1]) = "L"
				
					INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQuantity, liValue)
					INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, 0, liValue)

				ELSE
				
					= MESSAGEBOX("You're allowed to buy/sell " + lcISIN + " only once a day!", 64, "Buy/Sell Shares", 5000)
					
				ENDIF 
				
				WITH ThisForm
					.pgfBuySell.ActivePage = 1
					.POneClick()
				ENDWITH 
			ENDIF 
		ENDPROC 
		
	ADD OBJECT cboItems as Combobox WITH ;
		Top = 36, ;
		Left = 12, ;
		Height = 24, ;
		Width = 354, ;
		Style = 2, ;
		ColumnCount = 2, ;
		ColumnWidths = "210, 120"
		
		PROCEDURE cboItems.Init()
			Select cShareHolder, cISIN  FROM Shareholder ;
				ORDER BY 1 ;
				INTO ARRAY ThisForm.aItems
			
			WITH This
				.RowSourceType = 5
				.RowSource = "ThisForm.aItems"
				.Requery()
				
			ENDWITH

			This.Value = ThisForm.aItems[1,1]
			
		ENDPROC 
		
		PROCEDURE cboItems.Click()
			WITH  ThisForm
				.gc_ItemCode = ALLTRIM(This.List[This.ListIndex,2])
				.cmdBuy.Refresh()
				.pgfBuySell.UnBold()
				.pgfBuySell.Pages(ThisForm.gi_ActivePage).Click()
			ENDWITH 
			
		ENDPROC 

	ADD OBJECT pgfBuySell as PageFrame WITH ;
		Top = 84, ;
		Left = 12, ;
		Width = ThisForm.Width - 24, ;
		Height = ThisForm.Height - 108, ;
		Anchor = 15, ;
		PageCount = 4
		
		PROCEDURE pgfBuySell.Init()
			LOCAL loPage as Object
			
			FOR i = 1 TO This.pageCount
				loPage = This.Pages(i)
				loPage.AddObject("grdBuySell","grdBase")
	
				IF i = 3
					loPage.RemoveObject("grdBuySell","grdBase")

					loPage.AddObject("lblISIN", "Label")

					WITH loPage.lblISIN
						.Visible = .T.
						.Top = 24
						.Left = 24
						.Caption = "ISIN"
					
					ENDWITH 
					
					loPage.AddObject("lblUpdate", "Label")

					WITH loPage.lblUpdate
						.Visible = .T.
						.Top = 24
						.Left = 150
						.AutoSize = .T.
						.Caption = "Last update"
					
					ENDWITH 

					loPage.AddObject("lblNewValue", "Label")

					WITH loPage.lblNewValue
						.Visible = .T.
						.Top = 24
						.Left = 306
						.AutoSize = .T.
						.Caption = "New Value"
					
					ENDWITH 

					loPage.AddObject("cboISIN", "ComboBox")

					WITH loPage.cboISIN
						.Visible = .T.
						.Top = 48
						.Left = 24
						.Width = 120

					ENDWITH 

					loPage.AddObject("txtDateTime", "TextBox")

					WITH loPage.txtDateTime
						.Visible = .T.
						.Top = 48
						.Left = 150
						.Width = 150
						.ReadOnly = .T.
					ENDWITH 

					loPage.AddObject("txtShareValue", "TextBox")

					WITH loPage.txtShareValue
						.Visible = .T.
						.Top = 48
						.Left = 306
						.Width = 90
						.ReadOnly = .F.
						.Value = 0
						.InputMask = "999,999.99"
					ENDWITH 

					loPage.AddObject("cmdAddValue", "CommandButton")

					WITH loPage.cmdAddValue
						.Visible = .T.
						.Top = 36
						.Left = 420
						.Width = 120
						.Height = 60
						.Caption = "Add Value"
						.BackColor = RGB(0, 250, 250)
					ENDWITH 
				ENDIF 
				
				loPage.Caption = ICASE(i = 1, "Shares bought/sold", i = 2, "Recent evolution", i = 3, "Update Values", "Portfolio")

			ENDFOR 
		ENDPROC 

		PROCEDURE pgfBuySell.UnBold()
			LOCAL lnI
			
			This.SetAll("FontBold", .F., "Page")

			FOR lnI = 1 TO This.PageCount
				IF lnI != 3
					This.Pages(lnI).grdBuySell.Visible = .F.
				ENDIF 
					
			ENDFOR 
		ENDPROC 
		
	PROCEDURE Init()

		BINDEVENT(This.pgfBuySell.Page1, "Click", This, "POneClick")
		BINDEVENT(This.pgfBuySell.Page2, "Click", This, "PTwoClick")
		BINDEVENT(This.pgfBuySell.Page3, "Click", This, "PThreeClick")
		BINDEVENT(This.pgfBuySell.Page3.cmdAddValue, "Click", This, "PThreeCmdClick")
		BINDEVENT(This.pgfBuySell.Page3.cboISIN, "Click", This, "PThreeCboClick")
		BINDEVENT(This.pgfBuySell.Page4, "Click", This, "PFourClick")

	ENDPROC 
	
	PROCEDURE PThreeCboClick()
		LOCAL loPage, lcISIN, ldDateTime
		
		loPage = ThisForm.pgfBuySell.Page3

		lcISIN = ALLTRIM(loPage.cboISIN.List[loPage.cboISIN.ListIndex,1])
		ldDateTime = ThisForm.aValues[ASCAN(ThisForm.aValues, lcISIN) + 1]

		WITH loPage
			.txtDateTime.Value = ldDateTime
			.Refresh()
		ENDWITH 
	ENDPROC 
	
	PROCEDURE PThreeCmdClick()
		LOCAL lcISIN, lnValue, ldDateTime, loPage, liAnswer
		
		loPage = ThisForm.pgfBuySell.Page3
		
		WITH loPage
			lcISIN = ALLTRIM(.cboISIN.List[.cboISIN.ListIndex,1])
			lnValue = .txtShareValue.Value
			ldDateTime = .txtDateTime.Value

		ENDWITH 
		
*!*			WAIT WINDOW + lcISIN +" - "+ TRANSFORM(ldDateTime) + " - " + TRANSFORM(lnValue)

		IF lnValue > 0 AND DATE() > ldDateTime
			
			liAnswer = MESSAGEBOX("Do you want to update the value of " + CHR(13) + lcISIN + " with " + TRANSFORM(lnValue) +" € ?", 4 + 32,"Update Value")

			IF liAnswer = 6
				INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME(), 0, lnValue)
				
				= MESSAGEBOX("The value of " + lcISIN + " has been updated to " + TRANSFORM(lnValue) +" €.", 64,"Update Value", 5000)

			ENDIF 
		ELSE
			= MESSAGEBOX("Value 0 is not accepted - Update can be done only once a day !", 64, "Add Value", 5000)
			
		ENDIF 
		
		WITH loPage
			.txtShareValue.Value = 0
			.Click()
		ENDWITH 
		
	ENDPROC 
	
	PROCEDURE POneClick()
		LOCAL ARRAY laInvest[1]
	
		WITH ThisForm
			.gi_ActivePage = 1
			.lblItems.Refresh()
			.cboItems.Enabled = .T.
		ENDWITH
		
		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 
		
			SELECT cISIN, dInDate, iQuantity * nValue as nPInvest, iQuantity * nValue as nTInvest ;
				FROM Invest_in ;
				WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 2 ASC  ;
				INTO ARRAY laInvest

			FOR i = 2 TO ALEN(laInvest, 1)
				laInvest[i, ALEN(laInvest, 2)] = laInvest[i, ALEN(laInvest, 2)] + laInvest[i - 1, ALEN(laInvest, 2)]

			ENDFOR 
				
			CREATE CURSOR csrTInvest(cISIN C(12), dInDate T, nInvest N(7,2), nTInvest N(7,2))

			APPEND FROM array laInvest

			SELECT ShareHolder.cISIN, ShareHolder.cShareHolder, Invest_In.dInDate, Invest_In.iQuantity, Invest_In.nValue, Invest_In.iQuantity * Invest_In.nValue as nInvest, csrTInvest.nTInvest  ;
				FROM ShareHolder ;
					JOIN Invest_In ON ShareHolder.cISIN = Invest_In.cISIN ;
					JOIN csrTInvest ON Invest_In.cISIN = csrTInvest.cISIN AND InVest_In.dInDate = csrTInvest.dInDate ;
				WHERE ShareHolder.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 3 DESC ;
				INTO CURSOR csrTemp
		ENDIF 
			
		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 

			LOCATE 
			
			WITH This.pgfBuySell
				.UnBold()
				.Page1.FontBold = .T.
			ENDWITH 
				
			WITH This.pgfBuySell.Page1.grdBuySell
				.ColumnCount = -1
				.RecordSource = "csrTemp"
				.Visible = .T.
				
				.Column1.Header1.Caption = "ISIN"
				.Column1.Width = 120
				.Column2.Header1.Caption = "Company"
				.Column2.Width = 78
				.Column3.Header1.Caption = "Date"
				.Column3.Width = 120
				.Column4.Header1.Caption = "Units"
				.Column4.Width = 54
				.Column5.Header1.Caption = "Value"
				.Column5.Width = 60
				.Column6.Header1.Caption = "Invest"
				.Column6.Width = 84
				.Column7.Header1.Caption = "Total Invest"
				.Column7.Width = 84
			ENDWITH 
		ELSE
			WITH This.pgfBuySell
				.UnBold()
			ENDWITH 
			
			= MessageBox("No Item chosen", 16, "Choose Item", 2000)
		ENDIF 

		This.Refresh()

	ENDPROC 
	
	PROCEDURE PTwoClick()
		LOCAL ARRAY laShares[1, 1]

		WITH ThisForm
			.gi_ActivePage = 2
			.lblItems.Refresh()
			.cboItems.Enabled = .T.
		ENDWITH 

		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 

			SELECT Invest_In.dInDate, Invest_In.iQuantity, Invest_In.iQuantity As iTotal  ;
				FROM Invest_In ;
				WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 1 ASC ;
				INTO ARRAY laShares
			
			FOR i = 2 TO ALEN(laShares, 1)
				laShares[i, ALEN(laShares, 2)] = laShares[i, ALEN(laShares, 2)] + laShares[i - 1, ALEN(laShares, 2)]

			ENDFOR 

			FOR i = 1 TO ALEN(laShares) STEP 3
				UPDATE Invest_Out SET iQuantity = laShares[i + 2] WHERE dInDate >= laShares[i] AND cISIN = ThisForm.gc_ItemCode 
						
			ENDFOR 

			SELECT ShareHolder.cISIN, ShareHolder.cShareHolder, INVEST_OUT.dInDate, INVEST_OUT.iQuantity, INVEST_OUT.nValue, INVEST_OUT.iQuantity * INVEST_OUT.nValue as nInvest  ;
				FROM ShareHolder ;
					JOIN Invest_Out ON ShareHolder.cISIN = Invest_Out.cISIN ;
				WHERE ShareHolder.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 3 DESC ;
				INTO CURSOR csrTemp READWRITE  
		ENDIF 
	
		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 
			
			LOCATE 

			WITH This.pgfBuySell
				.UnBold()
				.Page2.FontBold = .T.
			ENDWITH 
				
			WITH This.pgfBuySell.Page2.grdBuySell
				.ColumnCount = -1
				.RecordSource = "csrTemp"
				.Visible = .T.
				
				.Column1.Header1.Caption = "ISIN"
				.Column1.Width = 120
				.Column2.Header1.Caption = "Company"
				.Column2.Width = 78
				.Column3.Header1.Caption = "Date"
				.Column3.Width = 120
				.Column4.Header1.Caption = "Units"
				.Column4.Width = 54
				.Column5.Header1.Caption = "Value"
				.Column5.Width = 60
				.Column6.Header1.Caption = "T-Value"
				.Column6.Width = 84
			ENDWITH 
		ELSE
			WITH This.pgfBuySell
				.UnBold()
			ENDWITH 
			
			= MessageBox("No Item chosen", 16, "Choose Item", 2000)
		ENDIF 

		This.Refresh()
		
	ENDPROC 
	
	PROCEDURE PThreeClick()
		LOCAL liAmount as Integer, lcItemCode as Character, loPage as Object
		
		loPage = ThisForm.pgfBuySell.Page3
		
		WITH ThisForm
			.gi_ActivePage = 3
			.lblItems.Refresh()
			.cboItems.Enabled = .T.
		ENDWITH 

		WITH This.pgfBuySell
			.UnBold()
			.Page3.FontBold = .T.
		ENDWITH 
		
		SELECT IVO1.cISIN, IVO1.dInDate ;
			FROM Invest_Out IVO1 ;
				WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ;
			INTO ARRAY ThisForm.aValues
			
			WITH loPage
				.cboISIN.Style = 2
				.cboISIN.RowSourceType = 5
				.cboISIN.RowSource = "ThisForm.aValues"
				.cboISIN.Requery()
				.cboISIN.Value = ThisForm.aValues[1]
				.txtDateTime.Value = ThisForm.aValues[2]
				
			ENDWITH

		This.Refresh()
		
	ENDPROC 	
			
	PROCEDURE PFourClick()
		LOCAL ARRAY laWinLoss[1]
	
		WITH This
			.gi_ActivePage = 4
			.lblItems.Refresh()
			.cboItems.Enabled = .F.
		ENDWITH
		
		SELECT IVO1.cISIN, IVO1.dInDate, IVO1.nValue ;
			FROM Invest_Out IVO1 ;
				WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ;
			INTO CURSOR csrValues

		SELECT ShareHolder.cISIN, ShareHolder.cShareHolder, ;
				SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nInvest, ;
				SUM(Invest_In.iQuantity * csrValues.nValue) as nSValue, ;
				SUM(Invest_In.iQuantity * csrValues.nValue) - SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nLossWin, ;
				SUM(Invest_In.iQuantity * csrValues.nValue) / SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nPcLossWin ;
			FROM ShareHolder ;
				JOIN Invest_In ON ShareHolder.cISIN = Invest_In.cISIN ;
				JOIN csrValues ON ShareHolder.cISIN = csrValues.cISIN ;
			GROUP BY 1, 2 ;
			ORDER BY 2 ASC ;
			INTO CURSOR csrTemp
			
		SELECT SUM(nLossWin) ;
			FROM csrTemp ;
			INTO ARRAY laWinLoss
		
		ThisForm.lblWinLoss.Caption = IIF(laWinLoss[1] > 0, "Total Win : ", "Total Loss :") + TRANSFORM(laWinLoss[1], "999,999.99")

		WITH This.pgfBuySell
			.UnBold()
			.Page4.FontBold = .T.
		ENDWITH 
				
		WITH This.pgfBuySell.Page4.grdBuySell
			.ColumnCount = -1
			.RecordSource = "csrTemp"
			.SetAll("DynamicBackColor", "IIF(csrTemp.nPcLossWin < 1, RGB(225, 0, 0), RGB(0, 230, 230))","Column")
			.Visible = .T.
			
			.Column1.Header1.Caption = "ISIN"
			.Column1.Width = 120
			.Column2.Header1.Caption = "Company"
			.Column2.Width = 78
			.Column3.Header1.Caption = "Invest"
			.Column3.Width = 90
			.Column4.Header1.Caption = "Value"
			.Column4.Width = 90
			.Column5.Header1.Caption = "Value - Invest"
			.Column5.Width = 90
			.Column6.Header1.Caption = "Value / Invest"
			.Column6.Width = 90

		ENDWITH 

		This.Refresh()
		
	ENDPROC 
	
	PROCEDURE Load()
		CREATE CURSOR ShareHolder (cISIN C(12), cShareHolder C(30), cItemDesc c(30))

			INSERT INTO ShareHolder VALUES ("DE0008469008", "Dax", "")
			INSERT INTO ShareHolder VALUES ("FR0008469009", "Chanel", "")
			INSERT INTO ShareHolder VALUES ("US0008469010", "Dell", "")
			INSERT INTO ShareHolder VALUES ("LU0008469012", "Post", "")

		CREATE CURSOR INVEST_IN (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(7,2))

			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-01}, 72, 120)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-04}, 12, 125)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-07}, 38, 130)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("DE0008469008", {^2022-12-08}, -5, 130)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-04}, 10, 110)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("FR0008469009", {^2022-12-05}, 53, 120)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-03}, 45, 125)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-05}, -10, 125)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-10}, -5, 120)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-05}, 32 , 109)
			INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES ("LU0008469012", {^2022-12-04}, 23, 99)
			
		CREATE CURSOR INVEST_OUT (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(7,2))

			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("DE0008469008", {^2022-12-04}, 0, 120)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity ,nValue)  VALUES ("DE0008469008", {^2022-12-05}, 0, 121)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("DE0008469008", {^2022-12-06}, 0 ,122)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("DE0008469008", {^2022-12-07}, 0, 121)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("DE0008469008", {^2022-12-08}, 0, 123)   

			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("FR0008469009", {^2022-12-04}, 0, 110)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("FR0008469009", {^2022-12-05}, 0, 109)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("FR0008469009", {^2022-12-06}, 0, 111)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("FR0008469009", {^2022-12-07}, 0, 112)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("FR0008469009", {^2022-12-09}, 0, 110)
			
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("LU0008469012", {^2022-12-04}, 0, 99)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("LU0008469012", {^2022-12-05}, 0, 109)   
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("LU0008469012", {^2022-12-06}, 0, 108)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("LU0008469012", {^2022-12-07}, 0, 107)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue)  VALUES ("LU0008469012", {^2022-12-12}, 0, 110)

			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-03}, 0, 125)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-04}, 0, 126)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-05}, 0, 127)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-06}, 0, 126)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-07}, 0, 124)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-08}, 0, 122)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-09}, 0, 120)
			INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES ("US0008469010", {^2021-12-10}, 0, 120)


	ENDPROC 
		 
	PROCEDURE Destroy()
		CLEAR Events
		ThisForm.Release
		
	ENDPROC
	
ENDDEFINE

*****

DEFINE CLASS grdBase AS Grid
		Top = 12
		Left = 12
		Height = 360 - 108 - (4 * 12)
		Width = 690 - (4 * 12)
		BackColor = RGB(0, 230, 230)
		RowHeight = 21
		AllowRowSizing = .F.
		HeaderHeight = 21
		AllowHeaderSizing = .F.
		DeleteMark = .F.
		ReadOnly = .T.
		Anchor = 15
		Visible = .F.

ENDDEFINE 
*****

MarK

german12 · Apr 24, 2023

Hi Mark,
Thanks for the sample code which uses three cursors/tables.

It makes a very good professional impression - as soon as you call it up.
I think it can help me a lot to improve my own program code.
I don't necessarily need the routines for buying stocks.
I already get a statement from the bank that already shows the result of a transaction, namely:

a) Date of transaction
b) Order no.
c) Number of shares sold
d) Designation of the share
e) ISIN
f) Execution rate on sale
g) gain or loss

I enter this data manually into the VFP file (aktien2.dbf) - it's very quick - and you don't do that every day.
Then it just needs to be evaluated - and so I can see
what each sold title brought as a result,

a)with which title the most was won/lost,
b)on which day of the week the highest result (gain or loss) was achieved,
c)at what price it was last sold
d)what the result has been per year.
e)and of course also what the total result of all transactions was since recording.

Your program can give me useful information how to code this.
The file structure for my aktien2.dbf can now also be simplified.

I still have to invest a little more time to fully understand your program, but I have the time.

Thanks very much
Klaus

Peace worldwide - it starts here...

mjcmkrsr · Apr 27, 2023

Hi Klaus,

This is "No Country for Old Men" - hence we have to support each other.

I have been using my app for almost a week now feeding it with real world values. It showed that it had some quirks which I removed from the code (check below).
Now it works to my fullest satisfaction. As I said, it uses three tables, that are now created the first time you start it. A "Dummy" record is also created in order not to yield errors. This record is deleted asa you add a new record.

Code:

LOCAL go_Form 

go_Form = CreateObject("frmForm")
go_Form.Visible = .T.
go_Form.Show

READ Events

CLOSE ALL
CLEAR ALL

*****

DEFINE CLASS frmForm As Form
	DIMENSION gaItems[1], gaValues[1]
	
	gc_ItemCode = REPLICATE("Z", 12) 
	gi_ActivePage = 1

	Height = 444
	Width = 750
	MaxWidth = This.Width
	MinHeight = This.Height
	MinWidth = This.Width
	MaxButton = .F.
	MinButton = .F.
	Caption = "Shares"
	BackColor = RGB(120, 120, 120)
	AutoCenter = .T.
	ShowTips = .T.
	Themes = .F.

	 ADD OBJECT imgFImage AS image WITH ;
	  	Left = 0, ;
	  	Top = 0, ;
	  	Height = ThisForm.Height, ;
	  	Width = ThisForm.Width, ;
	  	Stretch = 2, ;
	  	Name = "imgFImage", ;
	  	Visible = .T., ;
	  	Picture = "Picture 098.jpg", ;
	  	Anchor = 15

	ADD OBJECT lblItems AS Label WITH ;
		Top = 12, ;
		Left = 12, ;
		Autosize = .T., ;
		BackStyle = 0, ;
		ForeColor = RGB(254, 254, 254), ;
		FontBold = .T.
		
		PROCEDURE lblItems.Refresh()
			This.Caption = IIF(INLIST(ThisForm.gi_ActivePage, 1, 2, 3), "Please choose Item", "Click on page 1, 2 or 3")
			
		ENDPROC 

	ADD OBJECT cmdBuy as CommandButton WITH ;
		Top = 36, ;
		Left = 378, ;
		Height = 24, ;
		Width = 180, ;
		Caption = "Buy/Sell coupons from portfolio ", ;
		Enabled = .F.
		
		PROCEDURE cmdBuy.Refresh()
			IF ThisForm.gc_ItemCode = REPLICATE("Z", 12) OR ThisForm.gc_ItemCode = "XX1234567890" OR INLIST(ThisForm.gi_ActivePage, 2, 3, 4, 5, 6)
				This.Enabled = .F.
				This.ToolTipText = "Please choose Item"
			ELSE
				This.Enabled = .T.
				This.ToolTipText = "Click to buy/sell"
			ENDIF
		ENDPROC 
		
		PROCEDURE cmdBuy.Click()
			LOCAL liAnswer as Integer, liQuantity as Integer, lcISIN as Character, lnValue as Number(10,2)
			LOCAL ARRAY laCheck[1]
			
			lcISIN = ThisForm.gc_ItemCode
			liQuantity = INT((RAND() - 0.5) * 25)
			liValue = RAND() * 200

			liAnswer = MESSAGEBOX("Do you want to buy/sell " + ALLTRIM(STR(liQuantity)) + " shares " + lcISIN + " for " + TRANSFORM(liValue, "999.99") + " € ?", 4 + 32, "Buy/Sell Shares")

			IF liAnswer = 6
				SELECT Invest_In.cISIN, Invest_In.dInDate as dINVIDate, Invest_Out.dInDate as dINOUDate ;
					FROM Invest_In ;
					JOIN Invest_Out ON Invest_In.cISIN = Invest_Out.cISIN ;
					WHERE TTOD(Invest_In.dInDate) = TTOD(DATETIME() - 86400) OR TTOD(Invest_Out.dInDate) = TTOD(DATETIME() - 86400) ;
					INTO ARRAY laCheck

				IF VARTYPE(laCheck[1]) = "L"
				
*!*						INSERT INTO INVEST_IN (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQuantity, liValue)
*!*						INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, 0, liValue)

					WAIT WINDOW + "Procedure suspended" TIMEOUT 3

				ELSE
				
					= MESSAGEBOX("You're allowed to buy/sell " + lcISIN + " only once a day!", 64, "Buy/Sell Shares", 5000)
					
				ENDIF 
				
				WITH ThisForm
					.pgfBuySell.ActivePage = 1
					.POneClick()
				ENDWITH 
			ENDIF 
		ENDPROC 
		
	ADD OBJECT cboItems as Combobox WITH ;
		Top = 36, ;
		Left = 12, ;
		Height = 24, ;
		Width = 354, ;
		Style = 2, ;
		ColumnCount = 2, ;
		ColumnWidths = "210, 120", ;
		IncrementalSearch = .T.
		
		PROCEDURE cboItems.Init()
			Select cShareItem, cISIN  FROM INVEST_BK ;
				ORDER BY 1 ;
				INTO ARRAY ThisForm.gaItems
			
			WITH This
				.RowSourceType = 5
				.RowSource = "ThisForm.gaItems"
				.Requery()
			ENDWITH

			This.Value = ThisForm.gaItems[1,1]
			
		ENDPROC 
		
		PROCEDURE cboItems.Click()
			WITH  ThisForm
				.gc_ItemCode = ALLTRIM(This.List[This.ListIndex,2])
				.cmdBuy.Refresh()
				.pgfBuySell.UnBold()
				.pgfBuySell.Pages(ThisForm.gi_ActivePage).Click()
			ENDWITH 
			
		ENDPROC 
		
	ADD OBJECT pgfBuySell as PageFrame WITH ;
		Top = 84, ;
		Left = 12, ;
		Width = ThisForm.Width - 24, ;
		Height = ThisForm.Height - 108, ;
		Anchor = 15, ;
		PageCount = 5, ;
		ToolTipText = "RightClick to show Investment - Values"

		PROCEDURE pgfBuySell.Init()
			LOCAL loPage as Object
			
			FOR i = 1 TO This.pageCount
				loPage = This.Pages(i)
				loPage.ToolTipText = "RightClick to show Investment - Values"
				loPage.AddObject("grdBuySell","grdBase")
	
				IF i = 3
					loPage.RemoveObject("grdBuySell","grdBase")

					loPage.AddObject("lblISIN", "Label")

					WITH loPage.lblISIN
						.Visible = .T.
						.Top = 24
						.Left = 24
						.Caption = "ISIN"
					ENDWITH 
					
					loPage.AddObject("lblUpdate", "Label")

					WITH loPage.lblUpdate
						.Visible = .T.
						.Top = 24
						.Left = 150
						.AutoSize = .T.
						.Caption = "Last update"
					ENDWITH 

					loPage.AddObject("lblNewValue", "Label")

					WITH loPage.lblNewValue
						.Visible = .T.
						.Top = 24
						.Left = 306
						.AutoSize = .T.
						.Caption = "New Value"
					ENDWITH 

					loPage.AddObject("txtISIN", "TextBox")

					WITH loPage.txtISIN
						.Visible = .T.
						.Top = 48
						.Left = 24
						.Width = 120
						.ReadOnly = .T.
					ENDWITH 

					loPage.AddObject("txtDateTime", "TextBox")

					WITH loPage.txtDateTime
						.Visible = .T.
						.Top = 48
						.Left = 150
						.Width = 150
						.ReadOnly = .T.
					ENDWITH 

					loPage.AddObject("txtShareValue", "TextBox")

					WITH loPage.txtShareValue
						.Visible = .T.
						.Top = 48
						.Left = 306
						.Width = 90
						.ReadOnly = .F.
						.Value = 0
						.InputMask = "999,999.99 "
					ENDWITH 

					loPage.AddObject("cmdAddValue", "CommandButton")

					WITH loPage.cmdAddValue
						.Visible = .T.
						.Top = 36
						.Left = 480
						.Width = 120
						.Height = 60
						.Caption = "Add Value"
						.BackColor = RGB(0, 250, 250)
					ENDWITH 
				ENDIF 
				
				IF i = 4
					loPage.RemoveObject("grdBuySell","grdBase")

					loPage.AddObject("lblISIN", "Label")

					WITH loPage.lblISIN
						.Visible = .T.
						.Top = 24
						.Left = 24
						.Caption = "ISIN"
					ENDWITH 
					
					loPage.AddObject("lblCompany", "Label")

					WITH loPage.lblCompany
						.Visible = .T.
						.Top = 24
						.Left = 150
						.AutoSize = .T.
						.Caption = "Company"
					ENDWITH 

					loPage.AddObject("lblQty", "Label")

					WITH loPage.lblQty
						.Visible = .T.
						.Top = 24
						.Left = 306
						.AutoSize = .T.
						.Caption = "Quantity"
					ENDWITH 

					loPage.AddObject("lblValue", "Label")

					WITH loPage.lblValue
						.Visible = .T.
						.Top = 24
						.Left = 372
						.AutoSize = .T.
						.Caption = "Value"
					ENDWITH 

					loPage.AddObject("txtISIN", "TextBox")

					WITH loPage.txtISIN
						.Visible = .T.
						.Top = 48
						.Left = 24
						.Width = 120
						.Format = "!"
						.InputMask = "AA9999999999"
					ENDWITH 

					loPage.AddObject("txtCompany", "TextBox")

					WITH loPage.txtCompany
						.Visible = .T.
						.Top = 48
						.Left = 150
						.Width = 150
						.InputMask = "AAAAXXXXXXXXXXXXXXXXXXXXXXXXXX"
					ENDWITH 

					loPage.AddObject("txtQty", "TextBox")

					WITH loPage.txtQty
						.Visible = .T.
						.Top = 48
						.Left = 306
						.Width = 60
						.ReadOnly = .F.
						.Value = 0
						.InputMask = "999 "
					ENDWITH 

					loPage.AddObject("txtShareValue", "TextBox")

					WITH loPage.txtShareValue
						.Visible = .T.
						.Top = 48
						.Left = 372
						.Width = 78
						.ReadOnly = .F.
						.Value = 0
						.InputMask = "999,999.99 "
					ENDWITH 
					
					loPage.AddObject("cmdNewShare", "CommandButton")

					WITH loPage.cmdNewShare
						.Visible = .T.
						.Top = 36
						.Left = 480
						.Width = 120
						.Height = 60
						.Caption = "Add new share"
						.BackColor = RGB(0, 250, 250)
					ENDWITH 
				ENDIF 

				loPage.Caption = ICASE(i = 1, "Shares bought/sold", i = 2, "Value evolution", i = 3, "Update value", i = 4, "New share", "Portfolio" )

			ENDFOR 
		ENDPROC 

		PROCEDURE pgfBuySell.UnBold()
			LOCAL lnI
			
			This.SetAll("FontBold", .F., "Page")

			FOR lnI = 1 TO This.PageCount
				IF INLIST(lnI, 1, 2, 5)
					This.Pages(lnI).grdBuySell.Visible = .F.
				ENDIF 
			ENDFOR 
		ENDPROC 
		
		PROCEDURE pgfBuySell.RightClick()
			LOCAL ARRAY laWinLoss[1]
				
			SELECT IVO1.cISIN, IVO1.dInDate, IVO1.nValue ;
				FROM Invest_Out IVO1 ;
					WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ;
				INTO CURSOR csrValues

			SELECT SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nInvest, ;
						SUM(Invest_In.iQuantity * csrValues.nValue) as nSValue, ;
						SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) - SUM(Invest_In.iQuantity * csrValues.nValue) as nDValue ;
				FROM Invest_In ;
					JOIN csrValues ON INVEST_IN.cISIN = csrValues.cISIN ;
				INTO ARRAY laWinLoss			
				
			= MESSAGEBOX("Total invest : " + TRANSFORM(laWinLoss[1], "999,999.99 ") + " €" + CHR(13) ;
						+ "Total value : " + TRANSFORM(laWinLoss[2], "999,999.99 ") +" €" + CHR(13) ;
						+ IIF(laWinLoss[3] < 0, "Total win : ", "Total loss :") + TRANSFORM(laWinLoss[3], "999,999.99 ") + " €" + CHR(13) ;
						+ "Value/Invest : " + TRANSFORM(laWinLoss[2]/laWinLoss[1]) , 64, "Invest - Value - Win - Loss", 25000)
			
		ENDPROC 

	PROCEDURE Init()

		BINDEVENT(This.pgfBuySell.Page1, "Click", This, "POneClick")
		BINDEVENT(This.pgfBuySell.Page2, "Click", This, "PTwoClick")
		BINDEVENT(This.pgfBuySell.Page3, "Click", This, "PThreeClick")
		BINDEVENT(This.pgfBuySell.Page3.cmdAddValue, "Click", This, "PThreeCmdClick")
		BINDEVENT(This.pgfBuySell.Page4, "Click", This, "PFourClick")
		BINDEVENT(This.pgfBuySell.Page4.cmdNewShare, "Click", This, "PFourNewCouponClick")
		BINDEVENT(This.pgfBuySell.Page5, "Click", This, "PFiveClick")

	ENDPROC 
	
	PROCEDURE POneClick()
		LOCAL ARRAY laInvest[1]
	
		WITH ThisForm
			.gi_ActivePage = 1
			.lblItems.Refresh()
			.cmdBuy.Refresh()
			.cboItems.Enabled = .T.
		ENDWITH
		
		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 
		
			SELECT cISIN, dInDate, iQuantity * nValue as nPInvest, iQuantity * nValue as nTInvest ;
				FROM Invest_in ;
				WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 2 ASC  ;
				INTO ARRAY laInvest

			FOR i = 2 TO ALEN(laInvest, 1)
				laInvest[i, ALEN(laInvest, 2)] = laInvest[i, ALEN(laInvest, 2)] + laInvest[i - 1, ALEN(laInvest, 2)]

			ENDFOR 
				
			CREATE CURSOR csrTInvest(cISIN C(12), dInDate T, nInvest N(10,2), nTInvest N(10,2))

			APPEND FROM array laInvest

			SELECT INVEST_BK.cISIN, INVEST_BK.cShareItem, Invest_In.dInDate, Invest_In.iQuantity, Invest_In.nValue, Invest_In.iQuantity * Invest_In.nValue as nInvest, csrTInvest.nTInvest  ;
				FROM INVEST_BK ;
					JOIN Invest_In ON INVEST_BK.cISIN = Invest_In.cISIN ;
					JOIN csrTInvest ON Invest_In.cISIN = csrTInvest.cISIN AND InVest_In.dInDate = csrTInvest.dInDate ;
				WHERE INVEST_BK.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 3 DESC ;
				INTO CURSOR csrTemp
				
		ENDIF 
			
		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 

			LOCATE 
			
			WITH This.pgfBuySell
				.UnBold()
				.Page1.FontBold = .T.
			ENDWITH 
				
			WITH This.pgfBuySell.Page1.grdBuySell
				.ColumnCount = -1
				.RecordSource = "csrTemp"
				.Visible = .T.
				
				.Column1.Header1.Caption = "ISIN"
				.Column1.Width = 120
				.Column2.Header1.Caption = "Company"
				.Column2.Width = 150
				.Column3.Header1.Caption = "Date"
				.Column3.Width = 120
				.Column4.Header1.Caption = "Units"
				.Column4.Width = 54
				.Column5.Header1.Caption = "Value"
				.Column5.Width = 60
				.Column6.Header1.Caption = "Invest"
				.Column6.Width = 84
				.Column6.Sparse = .F.
				.Column6.Text1.InputMask = "999,999.99 "
				.Column7.Header1.Caption = "Total Invest"
				.Column7.Width = 84
				.Column7.Sparse = .F.
				.Column7.Text1.InputMask = "999,999.99 "
			ENDWITH 
		ELSE
			WITH This.pgfBuySell
				.UnBold()
			ENDWITH 
			
			= MessageBox("No Item chosen", 16, "Choose Item", 2000)
		ENDIF 

		This.Refresh()

	ENDPROC 
	
	PROCEDURE PTwoClick()
		LOCAL ARRAY laShares[1, 1], laDailyLossWin[1]
		LOCAL lnDailyLossWin

		WITH ThisForm
			.gi_ActivePage = 2
			.lblItems.Refresh()
			.cmdBuy.Refresh()
			.cboItems.Enabled = .T.
		ENDWITH 

		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 
		
			SELECT Invest_In.dInDate, Invest_In.iQuantity, Invest_In.iQuantity As iTotal  ;
				FROM Invest_In ;
				WHERE Invest_In.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 1 ASC ;
				INTO ARRAY laShares
				
			FOR i = 2 TO ALEN(laShares, 1)
				laShares[i, ALEN(laShares, 2)] = laShares[i, ALEN(laShares, 2)] + laShares[i - 1, ALEN(laShares, 2)]

			ENDFOR 

			FOR i = 1 TO ALEN(laShares) STEP 3
				UPDATE Invest_Out SET iQuantity = laShares[i + 2] WHERE dInDate >= laShares[i] AND cISIN = ThisForm.gc_ItemCode 
						
			ENDFOR
			
			SELECT Invest_Out.cISIN, COUNT(Invest_Out.cISIN), SUM(Invest_Out.iQuantity * Invest_Out.nValue) ;
				FROM Invest_Out ;
				GROUP BY Invest_Out.cIsin ;
				WHERE INVEST_OUT.cISIN = ThisForm.gc_ItemCode ;
				INTO ARRAY laDailyLossWin
				
			lnDailyLossWin = laDailyLossWin[3] / laDailyLossWin[2]

			SELECT INVEST_BK.cISIN, INVEST_BK.cShareItem, INVEST_OUT.dInDate, INVEST_OUT.iQuantity, INVEST_OUT.nValue, ;
					INVEST_OUT.iQuantity * INVEST_OUT.nValue as nInvest, (INVEST_OUT.iQuantity * INVEST_OUT.nValue) / lnDailyLossWin as nDLW   ;
				FROM INVEST_BK ;
					JOIN Invest_Out ON INVEST_BK.cISIN = Invest_Out.cISIN ;
				WHERE INVEST_BK.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY 3 DESC ;
				INTO CURSOR csrTemp 
		ENDIF 
	
		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 
			
			LOCATE 

			WITH This.pgfBuySell
				.UnBold()
				.Page2.FontBold = .T.
			ENDWITH 
				
			WITH This.pgfBuySell.Page2.grdBuySell
				.ColumnCount = -1
				.RecordSource = "csrTemp"
				.SetAll("DynamicBackColor", "IIF(csrTemp.nDLW < 1, RGB(225, 255, 0), RGB(0, 230, 230))","Column")
				.Visible = .T.
				
				.Column1.Header1.Caption = "ISIN"
				.Column1.Width = 120
				.Column2.Header1.Caption = "Company"
				.Column2.Width = 150
				.Column3.Header1.Caption = "Date"
				.Column3.Width = 120
				.Column4.Header1.Caption = "Units"
				.Column4.Width = 54
				.Column5.Header1.Caption = "Value"
				.Column5.Width = 60
				.Column5.Sparse = .F.
				.Column5.Text1.InputMask = "999,999.99 "
				.Column6.Header1.Caption = "T-Value"
				.Column6.Width = 84
				.Column6.Sparse = .F.
				.Column6.Text1.InputMask = "999,999.99 "
				.Column7.Header1.Caption = "DLW %"
				.Column7.Width = 72
				.Column7.Sparse = .F.
				.Column7.Text1.InputMask = "999.9999 "
			ENDWITH 
		ELSE
			WITH This.pgfBuySell
				.UnBold()
			ENDWITH 
			
			= MessageBox("No Item chosen", 16, "Choose Item", 2000)
		ENDIF 

		This.Refresh()
		
	ENDPROC 
	
	PROCEDURE PThreeCmdClick()
		LOCAL lcISIN, lnValue, ldDateTime, loPage, liAnswer
		
		loPage = ThisForm.pgfBuySell.Page3
		
		WITH loPage
			lcISIN = .txtISIN.Value
			lnValue = .txtShareValue.Value
			ldDateTime = .txtDateTime.Value
		ENDWITH 
		
		IF lnValue > 0 AND DATE() - 1 > TTOD(ldDateTime)
			
			liAnswer = MESSAGEBOX("Do you want to update the value of " + CHR(13) + lcISIN + " with " + TRANSFORM(lnValue) +" € ?", 4 + 32,"Update Value")

			IF liAnswer = 6
				INSERT INTO INVEST_OUT (cISIN , dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, 0, lnValue)
				
				= MESSAGEBOX("The value of " + lcISIN + " has been updated to " + TRANSFORM(lnValue) +" €.", 64,"Update Value", 3000)

			ENDIF 
		ELSE
			= MESSAGEBOX("Value 0 is not accepted - Update allowed only once a day !", 64, "Add Value", 5000)
			
		ENDIF 
		
		WITH loPage
			.txtShareValue.Value = 0
			.Click()
		ENDWITH 
	ENDPROC 
	
	PROCEDURE PThreeClick()
		LOCAL liAmount as Integer, lcItemCode as Character, loPage as Object
		
		loPage = ThisForm.pgfBuySell.Page3
		
		WITH ThisForm
			.gi_ActivePage = 3
			.lblItems.Refresh()
			.cmdBuy.Refresh()
			.cboItems.Enabled = .T.
		ENDWITH 
		
		WITH This.pgfBuySell
			.UnBold()
			.Page3.FontBold = .T.
		ENDWITH 
		
		IF ThisForm.gc_ItemCode != REPLICATE("Z", 12) 

			FOR i = 1 TO loPage.ControlCount
				IF UPPER(loPage.Controls(i).BaseClass) = "TEXTBOX" ;
						OR UPPER(loPage.Controls(i).BaseClass) = "LABEL" ;
						OR UPPER(loPage.Controls(i).BaseClass) = "COMMANDBUTTON" 
						
					loPage.Controls(i).Visible = .T.
				ENDIF 
			ENDFOR 

			SELECT IVO1.cISIN, IVO1.dInDate ;
				FROM Invest_Out IVO1 ;
					WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ;
						AND IVO1.cISIN = ThisForm.gc_ItemCode ;
				ORDER BY IVO1.cISIN ;
				INTO ARRAY ThisForm.gaValues
				
			WITH loPage
				.txtISIN.Value = ThisForm.gaValues[1]
				.txtDateTime.Value = ThisForm.gaValues[2]
				
			ENDWITH
		ELSE

			FOR i = 1 TO loPage.ControlCount
				IF UPPER(loPage.Controls(i).BaseClass) = "TEXTBOX" ;
						OR UPPER(loPage.Controls(i).BaseClass) = "LABEL" ;
						OR UPPER(loPage.Controls(i).BaseClass) = "COMMANDBUTTON" 
						
					loPage.Controls(i).Visible = .F.
				ENDIF 
			ENDFOR 

			WITH This.pgfBuySell
				.UnBold()
			ENDWITH 
			
			= MessageBox("No Item chosen", 16, "Choose Item", 2000)
		ENDIF 
	
		This.Refresh()
		
	ENDPROC 

	PROCEDURE PFourClick()
		WITH This
			.gi_ActivePage = 4
			.lblItems.Refresh()
			.cmdBuy.Refresh()
			.cboItems.Enabled = .F.
		ENDWITH
		
		WITH This.pgfBuySell
			.UnBold()
			.Page4.FontBold = .T.
		ENDWITH 
	ENDPROC 

	PROCEDURE PFourNewCouponClick()
		LOCAL loPage, lcIsin, lcCompany, liQty, lnValue
		LOCAL ARRAY laISIN[1] 

		loPage = ThisForm.pgfBuySell.Page4
		
		SELECT cISIN FROM INVEST_BK ORDER BY cISIN INTO ARRAY laISIN
		
		lcISIN = ALLTRIM(loPage.txtISIN.Value)
		lcCompany = ALLTRIM(loPage.txtCompany.Value)
		liQty = loPage.txtQty.Value
		lnValue = loPage.txtShareValue.Value

		IF EMPTY(lcISIN) ;
			OR LEN(lcISIN) != 12 ;
			OR EMPTY(lcCompany) ;
			OR LEN(lcCompany) < 4 ;
			OR liQty= 0 ;
			OR lnValue = 0 ;
			OR ASCAN(laISIN, lcIsin) > 0
			
			WAIT WINDOW + "Noooooo good!" TIMEOUT 2
		
		ELSE 
		
			IF MESSAGEBOX("Do you want to buy this coupon?", 4 + 32 + 256, "Buying new coupons") = 6
				LOCAL ARRAY laDummy[1]
				
				SELECT cISIN FROM INVEST_BK ;
					WHERE cISIN = "XX1234567890" ;
					INTO ARRAY laDummy
					
				IF laDummy[1] = "XX1234567890" 
				
					SET SAFETY off

					DELETE FROM INVEST_BK WHERE cISIN = "XX1234567890"
					USE IN INVEST_BK
					PACK INVEST_BK
					
					DELETE FROM INVEST_IN WHERE cISIN = "XX1234567890"
					USE IN INVEST_IN
					PACK INVEST_IN

					DELETE FROM INVEST_OUT WHERE cISIN = "XX1234567890"
					USE IN INVEST_OUT
					PACK INVEST_OUT
					
					SET SAFETY ON 
		
					USE INVEST_BK IN 0
					USE INVEST_IN IN 0
					USE INVEST_OUT IN 0
				ENDIF 
					
				INSERT INTO INVEST_BK VALUES (lcISIN, lcCompany, "")
				INSERT INTO Invest_In (cISIN, dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQty, lnValue)
				INSERT INTO Invest_Out (cISIN, dInDate, iQuantity, nValue) VALUES (lcISIN, DATETIME() - 86400, liQty, lnValue)
					
				= Messagebox("Congratulations! You bought " + TRANSFORM(liQty) + " coupons of " + lcISIN, 64, "Buying new coupons", 3000)

				Select cShareItem, cISIN ;
					FROM INVEST_BK ;
					ORDER BY 1 ;
					INTO ARRAY ThisForm.gaItems
					
				ThisForm.cboItems.Requery()
					
			ELSE
			
				= Messagebox("Nothing bought!", 64, "Buying new coupons", 3000)
			
			ENDIF
		ENDIF
		
		WITH loPage
			.txtISIN.Value = ""
			.txtCompany.Value = ""
			.txtQty.Value = 0
			.txtShareValue.Value = 0
			.Refresh()
		ENDWITH 
	ENDPROC 

	PROCEDURE PFiveClick()
		LOCAL ARRAY laWinLoss[1]
	
		WITH This
			.gi_ActivePage = 5
			.lblItems.Refresh()
			.cmdBuy.Refresh()
			.cboItems.Enabled = .F.
		ENDWITH
		
		SELECT IVO1.cISIN, IVO1.dInDate, IVO1.nValue ;
			FROM Invest_Out IVO1 ;
				WHERE IVO1.cISIN + DTOS(IVO1.dInDate) IN (SELECT IVO2.cISIN + DTOS(MAX(IVO2.dInDate)) FROM Invest_Out IVO2 GROUP BY IVO2.cISIN) ;
			INTO CURSOR csrValues

		SELECT INVEST_BK.cISIN, INVEST_BK.cShareItem, ;
				SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nInvest, ;
				SUM(Invest_In.iQuantity * csrValues.nValue) as nSValue, ;
				SUM(Invest_In.iQuantity * csrValues.nValue) - SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nLossWin, ;
				SUM(Invest_In.iQuantity * csrValues.nValue) / SUM(INVEST_IN.iQuantity * INVEST_IN.nValue) as nPcLossWin ;
			FROM INVEST_BK ;
				JOIN Invest_In ON INVEST_BK.cISIN = Invest_In.cISIN ;
				JOIN csrValues ON INVEST_BK.cISIN = csrValues.cISIN ;
			GROUP BY 1, 2 ;
			ORDER BY 2 ASC ;
			INTO CURSOR csrTemp

		WITH This.pgfBuySell
			.UnBold()
			.Page5.FontBold = .T.
		ENDWITH 
				
		WITH This.pgfBuySell.Page5.grdBuySell
			.ColumnCount = -1
			.RecordSource = "csrTemp"
			.SetAll("DynamicBackColor", "IIF(csrTemp.nPcLossWin < 1, RGB(225, 255, 0), RGB(0, 230, 230))","Column")
			.Visible = .T.
			
			.Column1.Header1.Caption = "ISIN"
			.Column1.Width = 120

			.Column2.Header1.Caption = "Company"
			.Column2.Width = 150

			.Column3.Header1.Caption = "Invest"
			.Column3.Width = 90
			.Column3.Sparse = .F.
			.Column3.Text1.InputMask = "999,999.99 "
			
			.Column4.Header1.Caption = "Value"
			.Column4.Width = 90
			.Column4.Sparse = .F.
			.Column4.Text1.InputMask = "999,999.99 "
			
			.Column5.Header1.Caption = "Value - Invest"
			.Column5.Width = 90
			.Column5.Sparse = .F.
			.Column5.Text1.InputMask = "999,999.99 "
			
			.Column6.Header1.Caption = "Value / Invest"
			.Column6.Width = 90

		ENDWITH 

		This.Refresh()
		
	ENDPROC 

	PROCEDURE Load()
		IF NOT (FILE("INVEST_BK.dbf") AND FILE("Invest_In.dbf") AND FILE("Invest_Out.dbf"))
			CREATE TABLE INVEST_BK (cISIN C(12), cShareItem C(30), cItemDesc c(30))
			INSERT INTO INVEST_BK VALUES ("XX1234567890", "Dummy", "")

			CREATE TABLE INVEST_IN (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(10,2))
			INSERT INTO Invest_In (cISIN, dInDate, iQuantity, nValue) VALUES ("XX1234567890", DATETIME() - 86400, 1, 100)

			CREATE TABLE INVEST_OUT (iPKey I AUTOINC NEXTVALUE 1000, cISIN C(12), dInDate T, iQuantity I, nValue N(10,2))
			INSERT INTO Invest_Out (cISIN, dInDate, iQuantity, nValue) VALUES ("XX1234567890", DATETIME() - 86400, 1, 100)

		ELSE 
		
			USE INVEST_BK IN 0
			USE INVEST_IN IN 0
			USE INVEST_OUT IN 0
		ENDIF 
	ENDPROC 
		 
	PROCEDURE Destroy()
		CLEAR Events
		ThisForm.Release
		
	ENDPROC
ENDDEFINE

*****

DEFINE CLASS grdBase AS Grid
		Top = 12
		Left = 12
		Height = 444 - 108 - (4 * 12)
		Width = 750 - (4 * 12)
		BackColor = RGB(0, 230, 230)
		RowHeight = 21
		AllowRowSizing = .F.
		HeaderHeight = 21
		AllowHeaderSizing = .F.
		DeleteMark = .F.
		ReadOnly = .T.
		Anchor = 15
		Visible = .F.

ENDDEFINE 
*****

Enjoy.

MarK

Girish Solanki · Aug 30, 2023

New method incremental Search in vfp9
Please show full video

https://youtu.be/RGmrxxQNiDo?si=oNouUYfOEtVhpnW3

Screenshot_2023-08-30-19-46-21-258_com.google.android.youtube_fz52eo.jpg

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Advantage/Disadvantage between a BROWSE window or/and a building of a running GRID on a form. 10

german12

Programmer

Chris Miller

Programmer

mjcmkrsr

Technical User

Chris Miller

Programmer

german12

Programmer

mjcmkrsr

Technical User

mjcmkrsr

Technical User

german12

Programmer

Chris Miller

Programmer

german12

Programmer

mjcmkrsr

Technical User

german12

Programmer

mjcmkrsr

Technical User

Girish Solanki

Programmer

Similar threads

Part and Inventory Search

Sponsor