Do+While...EndDo (adding new row) 2

[Rock4J]

Hi,

I am editing a project. here are some of the code:

CODE 1: Table

Create Cursor cDenom ;
   ( m100 N(5),;
     m50 N(5),;
     m10 N(5),;
     m5 N(5),;
     m2 N(5),;
     m1 N(5),;
     m05 N(5),;
     m02 N(5),;
     m01 N(5),;
     m005 N(5),;
     m001 N(5),;
     dpay N(10,2),;
     mpay n(10,2),;
     mBank n(10,2),;
     mResigned n(10,2),;
     EBank n(10),;
     EResigned n(10),;
     EDaily n(10),;
     EMonthly n(10) )

CODE 2: Process

wait window "Denominating in progress..." nowait

if !goApp.LoadCfg()
   Return
endif
 
ThisForm.CreateTable

********************************
* calculation daily employee
********************************

Store 0 to c100, c50, c10, c5, c2
Store 0 to c1, c05, c02, c01, c005, c001
Store 0 to cdpay, cmpay
Store 0 to nResigned, nBank
Store 0 to nEResigned, nEBank
Store 0 to nEDaily, nEMonthly

Select Empno
go top

do whil !eof()
	
   if Empty(nPay)
      Skip
      Loop
   endif

   if DResigned >= goApp.mDbegin and DResigned <= goApp.mDEnd
      nResigned = nResigned + nPay
      nEResigned = nEResigned + 1
      Skip
      Loop
   endif
     
   if PayType = "D"
      cdpay = cdpay + nPay
      nEDaily = nEDaily + 1

   else
      cmpay = cmpay + npay
      nEMonthly = nEMonthly + 1
   endif

   cBal = nPay
*   c100 = c100 + INT(cBal/100)		&&Used-02May2008
*   cBal = MOD(cBal,100)				&&Used-02May2008
   c50 = c50 + INT(cBal/50)		&&Cancel-02May2008
   cBal = MOD(cBal,50)				&&Cancel-02May2008
   c10 = c10 + INT(cBal/10)
   cBal = MOD(cBal,10)
   c5 = c5 + INT(cBal/5)
   cBal = MOD(cBal,5)
*   c2 = c2 + INT(cBal/2)
*   cBal = MOD(cBal,2)
   c1 = c1 + INT(cBal/1)
   cBal = MOD(cBal,1)
   c05 = c05 + INT(cBal/0.5)
   cBal = MOD(cBal,0.5)
   c02 = c02 + INT(cBal/0.2)
   cBal = MOD(cBal,0.2)
   c01 = c01 + INT(cBal/0.1)
   cBal = MOD(cBal,0.1)
   c005 = c005 + INT(cBal/0.05)
   cBal = MOD(cBal,0.05)
   c001 = c001 + INT(cBal/0.01)

   Skip

Enddo

Select cDenom
Append Blank
Replace m100 with c100,;
        m50 with c50,;
        m10 with c10,;
        m5 with c5,;
        m2 with c2,;
        m1 with c1,;
        m05 with c05,;
        m02 with c02,;
        m01 with c01,;
        m005 with c005,;
        m001 with c001,;
        dpay with cdpay,;
        mpay with cmpay,;
        mBank with nBank,;
        mResigned with nResigned,;
        EResigned with nEResigned,;
        EBank with nEBank,;
        EDaily with nEDaily,;
        EMonthly with nEMonthly

Wait Clear

As per code above,

1. I want to add NAME & ID in the 'cDenom' table (which will be related to the "Select Empno").

2. I want to add code that will ADDING ROW (for each NAME & ID) into the 'cdenom' table, on the Do-while loop in side the code 2 (Process).


Please help me, and thanks!





Regards,
Rocky

 

[Mike Lewis]

Rocky,

To add the Name and ID fields to the cDemon cursor, just add them to list of fields in the CREATE CURSOR statement. The exact syntax will depend on their data types. For example, if Name is a 12-character field, and ID is an integer, you would use this code:

Create Cursor cDenom ;
   ( [b]Name C(12), ;
     ID I, ;[/b]
     m100 N(5),;
     m50 N(5),;
        etc. etc.

For your second question, you would need to know where the Name and ID are coming from, that is, how you determine their values. Once you know that, you can add the appropriate statements within the loop, and then include the Name and ID in the final REPLACE statement.

By the way, off-hand I can see at least half a dozen ways of improving this code - and no doubt other forum members can too. Let's focus on answering your questions, and then perhaps we can deal with the other issuese.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Rock4J]

Thanks Mike!

Actually the "EMPNO" that appeared in the code 2 already have the NAME & ID FIELD:

********************************
* calculation daily employee
********************************

Store 0 to c100, c50, c10, c5, c2
Store 0 to c1, c05, c02, c01, c005, c001
Store 0 to cdpay, cmpay
Store 0 to nResigned, nBank
Store 0 to nEResigned, nEBank
Store 0 to nEDaily, nEMonthly

Select Empno     && <<------ SEE HERE
go top

*...

I know how to do this in VB using ADO Recordset. But it's totally different in Foxpro.


Regards,
Rocky

 

[Olaf Doschke]

>But it's totally different in Foxpro.
Well, yes. But..

A cursor compares to a recordset, but it's not a recordset, it's a temp table, and so SQL syntax applies. CREATE CURSOR is absolutely identical to CREATE TABLE, and creating a table has that syntax. Also you don't add fields to a table the same way you can do to an ADO recordset. Either you have the field in the first place (by specifying it in CREATE) or you alter the table ALTER TABLE also can alter cursors, but in this case it's easier to change the cursro creation. I think you are used to use the Append method of the recordset, but what's easier than simply letting the cursor have the field you want to have in the first place?

You can also add fields while doing SQL Select: Select expression as newfield, table.* from table.

And if the table you select from already has that field, you don't need to add it to the cursor. You also don't need to create a cursor before you query data into it, you SELECT ... FROM tablex JOIN ...whatever INTO CUROSR curResult and that creates that cursor.

Instead of all your code you'd rather do

SELECT SUM(nPay/100) ,... FROM Empno WHERE DResigned Between goApp.mDbegin and goApp.mDEnd INTO CURSOR cDenom

You are inheriting very convoluted and overcomplicated code there.

Bye, Olaf.

 

[Mike Lewis]

Actually the "EMPNO" that appeared in the code 2 already have the NAME & ID FIELD

So, what do you want to do with those two fields? The point is that you are only creating one cDenom record, but you have many EmpNo records. Will the Name and ID be the same for all of them? Or do you only want to store the Name and ID from the first (or last?) EmpNo in cDenom?

Assuming you want to store the first Name and ID, you could do this:

GO TOP IN EmpNo
Select cDenom
Append Blank
Replace m100 with c100,;
        m50 with c50,;
       ....
       ID WITH EmpNo.ID, ;
       Name WITH EmpNo.Name

As Olaf pointed out, the whole code is over-complicated, and can be greatly simplified. In particular, it would be possible to replace the whole thing with a single SQL statement.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Rock4J]

Thanks Olaf and Mike!

Here is some explanation about I need to get the EMPLOYEE NAME and ID from the EMPNO:

- Actually the CDENOM cursor table was purposely created to print REPORT.

- and the CODE2 that I have quoted was created to print a report that will display OVERALL denomination.

- The reason i want to add EMPLOYEE NAME and ID is to create another form with the same code but list out the DENOMINATION, SPECIFICALLY; for EACH EMPLOYEE; per EMPLOYEE. and I can only access the details from the EMPNO.

- The code/project that I'm trying to edit WAS NOT written/designed/created/programmed by me. I'm just replacing the programmer to do develope the application/program. The last programmer left the program without a clue.

- I have no option use other programming language since the company don't allow me to.

I'm new in Foxpro and almost know nothing about it.. that's why I ask for help here..

Thanks anyway.

Regards,
Rocky

 

[Rock4J]

Thanks Olaf and Mike!

Here is some explanation about why I need to get the EMPLOYEE NAME and ID from the EMPNO:

- Actually the CDENOM cursor table was purposely created to print REPORT.

- and the CODE2 that I have quoted was created to print a report that will display OVERALL denomination.

- The reason i want to add EMPLOYEE NAME and ID is to create another form with the same code but list out the DENOMINATION, SPECIFICALLY; for EACH EMPLOYEE; per EMPLOYEE. and I can only access the details from the EMPNO.

- The code/project that I'm trying to edit WAS NOT written/designed/created/programmed by me. I'm just replacing the programmer to do develope the application/program. The last programmer left the program without a clue.

- I have no option use other programming language since the company don't allow me to.

I'm new in Foxpro and almost know nothing about it.. that's why I ask for help here..

Thanks anyway.

Regards,
Rocky

 

[Olaf Doschke]

Rock4J,

you don't have to defend your position, we understood that. That's why I was explaining a bit how foxpro works (ideally). I was just addressing your direct and indirect questions.
Also I was ending in "You are inheriting very convoluted and overcomplicated code there.", didn't I?

I can imagine how you could do many changes much faster, if that program was written in some language you already know. But you're more fighting with the lack of VFP knowledge of your previous collegue, than with VFP itself, you can do things with very few lines of code in VFP, compared to other languages.

For example, as you mentioned the recordset: To get at a field value with a recordset you do

dim Conn,querystring,RS
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DBName
querystring = "SELECT name,email,tel FROM users"
Set RS = Conn.execute(querystring)

if not RS.EOF then
do while not RS.EOF
response.write("Name: " & RS("name").value)
response.write("Email: " & RS("email").value)
response.write("Telephone: " & RS("tel").value)
RS.movenext
loop
end if

RS.Close
Conn.Close
Set Conn = nothing

That's taken from

http://www.globalgraphics.co.uk/blog/entry/classic-asp-recordsets-vs-getrows/

Doesn't matter much, if the database and table would differ, that's about what you need to do with a recordset fetching data, iterating through all records. It's even a bit simplifying the connection, typically you need a connection string more than just the DBname, but you know best.

Here's what you do in FoxPro, having a table users with fields name,email,tel as the query in the ASP classic sample suggests.

Use users
Scan
  ? "Name: ", name
  ? "Email: ", email
  ? "Telephone: ", tel
Endscan

Isn't that much less convoluted? You simply address the field values by the field names, nothing like rs("fieldname").value.

Even if you would insist in ding a sql query (to be able to join other data, apply a where caluse etc., you can do:

Select nam,email,tel from users into cursor curUsers
Scan
  ? "Name: ", name
  ? "Email: ", email
  ? "Telephone: ", tel
Endscan

Actually you could do without any code, simply put the users table into the dataenvironment of a form, then drag & drop the visual table object on the form canvas and have a grid displaying the table.
I'd say if you can work with recordsets, you can also learn VFP. The "impedance mismatch" is much lower than with other languages. And the dataaccess can't be much simpler.

Bye, Olaf.

 

[Mike Lewis]

Rocky,

I understand your position. You need to maintain a program written in Visual FoxPro, even though you are not familiar with the language. That's a difficult situation, although not unusual.

We can answer your specific questions, and show you how to achieve particular goals. But, in the long run, that won't help you, if you don't understand the basics of what you are doing. It would be much better if you could step back from the immediate problem, and spend some time learning the fundamentals of Visual FoxPro.

One way to do that might be to study the code you have inherited, and try to work out exactly what it's doing, on a line by line basis. If necessary, look up the commands and functions in the Help file. Write a comment against each block of code to explain to yourself what it is doing. In general, I've always found that to be a good way of learning a new language.

In addition, there are books available on VFP (but none that is really suitable for beginners), and any number of websites and blogs where you can find examples and explanations of VFP code and techniques.

Once you have a good grasp of the basic language, you will be in much better shape to maintain your existing application (and you can tell your boss I said so).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Rock4J]

Thanks Olaf!

For the kindness and specially for the simple but cool code sample. I'll try to learn and try to use the concept.



Regards,
Rocky

 

[Rock4J]

Thanks too Mike! I understand, sorry for unnecessary things that I've made here..

Regards,
Rocky

 

[Mike Lewis]

Absolutely no need to apologise, Rocky. You've got to start somewhere.

And please be sure to come back when you need any further help.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Totally agreed. It's even not necessary for your püredecessor, that may be old code written in an older VFP version and also with a lack of a deeper VFP understanding. I am merely defineding VFP itself. You don't get around that in a VFP forum

You're welcome.

Bye, Olaf.

 

[Rock4J]

Thanks guys!

Regards,
Rocky