Commission Caclulation

[Ivan Kohler]

Do use the list for this sort of thing.  Thanks!

On Thu, May 27, 1999 at 05:13:36PM +0000, Donald L. Greer wrote:
>   Ivan,
>   I was going to work on the Commission calculation.
>   I assume that "prog" was for the commission calculation and "freq" was
> to specify how frequently it should be calculated/paid.

Yes.

"prog" is supposed to be implemented along the lines of "setup" and
"recur" from part_pkg - it is evaluated as a perl expression. 

>  There seem to
> be some things missing (like last calculation/next calculation,
> (total/yearly) commission to date, and probably some other things, but I
> think can work around these.

Yes.

>   Here's what I'm going to try:
>   Use "prog" for percentage commission (applied to all sales - I don't
> like that, but I'll have to do it that way because I don't know how to
> modify the existing tables without breaking things).

No, see above; prog should be a program, not a percentage.

A percentage commission would be something like `0.01 * $total_sales'.

>   I'll create a new table "agent_comm" which will be similar to the
> invoice table (_date,commnum,agentnum,owed,paid).

Are there many agent_comm records for each agentnum?  I.e. is the
relationship from the agent table to the agent_comm table one-to-many?

I'd order it ( commnum, agentnum, _date, owed, paid ) but the Freeside
schema needs a major overhaul anyway, so don't pay too much attention to
that.

> This isn't really all
> the data that I want to capture, but it will be enough to get started I
> think.  I will accumulate all commissions for a given dates charges in
> the same record,

That doesn't seem quite right to me.

It seems better to me to put each commission in a separate record (perhaps
you should add custnum to the proposed agent_comm table), and 

You could get the total commission for a given date range (one day, many
days, one hour, anything) by doing a search over that table. 

> then pay a batch of records at a time (I'll have to
> track the last/next payment date by hand until I understand how to
> modify the agent table).

I don't think that's a very good idea.  Why don't you go ahead and
implement what you need instead?

If you want to track a last/next payment date for each agent, and want
field(s) in the agent table for this, go ahead and make the change.

>   Does that sound reasonable?  This is my first attempt at an SQL
> project, so I'm blazing a new trail here and I'm not terribly confident
> about it.

Sounds pretty good so far.

Instead of doing things halfway by hand, though, I suggest you figure out
all the data you want to track, and come up with a well-thought out
schema. 

-- 
Ivan Kohler <ivan at sisd.com> - finger for PGP key - <moc.dsis at navi> Relhok Navi
Open-source billing and administration for ISPs - http://www.sisd.com/freeside
20 4,16 * * * saytime # please don't be surprised if you find me dreaming too