Cust.Invoices Close

[Henry Olive 5]

I wish everyone a healthy day.
Interbase 2007 - D-XE

 

I have a view which has all datas from the customers.

(CustNo, TDate(Trans.Date) ,TName(Trans.Name/Buy, Sell), Debit, Credit, DueDate, Status etc.)

 

I want to close (Status=Paid) all invoices of A customer which paid

Is it possible to solve this issue with a s.proc ? (or a better way)
Can someone please help me ?

Thank You

[FPiette 383]

You can do it with a stored procedure but personally I always prefer to put SQL code in my Delphi source code so that when I read my program source code I see everything.

 

The exact SQL request needed depends on the details of your database structure. I cannot the you exactly without knowing it. It is probably a "simple" update setting the Status field to "paid" with a WHERE clause selecting the correct record to be updated.

 

[bobD 6]

16 minutes ago, Henry Olive said:

Is it possible to solve this issue with a s.proc ?

Certainly. but the details of that would depend entirely on the database schema, including knowledge of the table structures, the view you're referring to, and any/all other relevant complications (such as db triggers and rights of the the user connection.)

One of the first questions that occurs to me is "what do you mean by an 'invoice'? My tendency is to regard invoices as simply statements (ie, point-in-time reports) of the financial status of an account. The 'real' underlying objects being reported on are the account and its billable orders. Credits applied to an account would normally be applied to each billable order amount in sequence from oldest to newest until the credit was exhausted, w/ any amount left over then becoming a positive balance (credit) on the account. A superseding invoice or refund might then be generated to reflect the new account status.  

bobD

[Guest]

a "View", in SQL, is a table in "Read-Only" mode, then, you can have access in all records, BUT, to update it, you need access the table source. Some DB, you can access and update by "views" but, normally, only in the table-main.

a "Stored Procedure" as like a "procedure/function" in Delphi code, then, you can pass params! ok?

in SQL, you can use "FOR SELECT" looping to analize the condition and "UPDATE" command to updates it.

In summary, yes, you can use it!

 

Simple way to "UPDATE" command: (you use many fields, on command. you can use a SELECT resulted to get the values... etc..)

UPDATE <<table-name>>
SET <<field-name>> = <<value to compare>>
WHERE <<field-name>> = <<value to compare>>

you can use a "FOR SELECT" when is necessary more analize before update the values

FOR <select-stmt>
   INTO <var> [, <var> ...]
DO
  ....
   <psql-stmt>
<select-stmt>  ::=  A valid SELECT statement.
<var>          ::=  A PSQL variable name, optionally preceded by “:”
<psql-stmt>    ::=  A single statement or a block of PSQL code.

the possibilities is so much, depend of your DB and "how you desired do it"

my tip, is read about your SQL DB commands and samples.

 

a sample by Firebird on official doc-page

create procedure deltown (towntodelete varchar(24))
  returns (town varchar(24), pop int)
as
begin
  for select town, pop from towns into :town, :pop as cursor tcur do
  begin
    if (town = towntodelete)
      then delete from towns where current of tcur; // deleting a records by conditional, but, you can "update it"
      else suspend;
  end
end

pay attention, mainly, if a transaction is active in your code.

NOTE: in SQL Stored Procedure, the transaction current will used by default!

 

hug

[Henry Olive 5]

Thank you so much FPiyette, Bob, EMailX45

View is like below (view gets datas from Invoice, Bank, Cash, Cheque)

(I know releated table name via TName) like Sell=Invoice, B.Trs=Bank, Cash=Cash etc.

ID......DocNo...TDate...........TName....CustNo....Debit........Credit.....DueDate.......Status

5...........0001....01/25/20.......Sell..............1...........100,00...........0,00......02/25/20

24........0025.....02/15/20.......Sell.............1...........100,00...........0,00......04/15/20

132.....1268.....02/25/20.......B.Trs............1.............. 0,00......100,00

 

customer paid 0001 invoice number invoice  on 02/25/20 by bank transfer

Now i want to update Invoice Table's(ID=5 ) Status field=Closed

Thank You

 

[FPiette 383]

UPDATE Invoice SET Status = "Closed" where ID = 5

 

[Guest]

maybe, use "INVOICE" number, would can be better choice, because "IT" is receiving the "Status" as paid or not!

then:

UPDATE <<table-name>>
set <<field1>> = <<new value>> .... field1 = x, field2 = y, etc... list 
where <<field-condition>> = <<condition>>  ..... you can have more fields conditions of coure

One tip:

• for fields like "Status", if possible, always a char or integer to simulate a boolean value, then stay some like this:  Status=1 = to TRUE, Status=0 to FALSE // "1" or "0" to char type
• later, you can use Delphi resource to transform it in boolean, for example, using CHECKBOX components! or similars. you see?
• other languages can use it too!
• on the end, 1 byte to 1.000.000 records = 1MI, against "Closed" (or any other) = 6MI

hug