[smalpierre]

Am I on the right track so far?

What platform do you want to run this on - desktop, web app, or mobile? Or as a combination of any / all of the above?

 

[smalpierre]

Also - if you want to pull all work orders an installer has assigned but are not complete you'd do this:

select * from salesorders where InstallerId = 1 and InstallComplete is null

Oh, and you'll probably want to order the list to show the records in some order - like ordered by the date completed, newest first.

To do that, you'd do this:

select * from salesorders where InstallerId = 1 and InstallComplete is not null order by InstallComplete desc

If that returns them in the wrong order, change desc to asc. they stand for ascending, and descending.

 

[Needles]

Am I on the right track so far?

What platform do you want to run this on - desktop, web app, or mobile? Or as a combination of any / all of the above?

Python

 

[smalpierre]

Python

Python is a scripting language, not a platform

Why Python? I know some people that like it. I tried it years ago but quickly determined that it just wasn't what I wanted.

Desktop apps I do in C++ or ObjectivePascal. Web apps, it's PHP all the way. Well, I've seen some really bad ass web apps built with Pascal running as an ISAPI (or NSAPI) on the webserver.

If you're looking for the most blazing speed possible in a webapp, that's the fastest by a long shot - all compiled code, no interpreter to slow things down.

They can be made in C/C++ as well.

 

[smalpierre]

Ok, I had a cancellation - so I figured I'd play with this for a minute.

What you might be wondering is how do you pull a record set where each line contains data from multiple tables? That's easy - you use join syntax in the sql.

At this point, you cannot use * to select all fields, if you want them all you have to explicitly declare them all. Usually this isn't the case, and you'll only want to pull data that you need on that report.

Here's the query - I'll post again with a photo of my sql console with the expected outputted data set. There isn't a full set of test data, so it'll only display 1 row. Even on simple queries like this one you will want a good data set with multiple scenarios to run the query against to make SURE that you're filtering what you want to get rid of, and not filtering anything you want to keep.

EDIT: Formatting with tabs and spaces is stripped by the forum editor. I'll leave this so you can copy / paste, refer to the next post's photo for how I actually format this.

select salesorders.InstallAddress,
salesorders.SalePrice,
salesorders.OrderDate,
salesorders.InstallComplete,
floorplans.UnitName,
floorplans.Description,
installers.InstallerName
from installers
inner join salesorders on salesorders.InstallerId = installers.InstallerId
inner join floorplans on floorplans.FloorplanId = salesorders.FloorplanId
where installers.installerId = 1
and salesorders.InstallComplete is null;

This select pulls an installer, joins sales orders that he is attached to, then joins floorplans table to retrieve more data. Since there are multiple tables, and the fields may have the same name (like InstallerId is in multiple tables) so you use tablename.fieldname to pick a field from whichever table you want. The rest works the same.

White space in sql does not matter - the spaces and tabs. Only commas, and semicolons denote separation of fields, and end of command btw - figured I'd mention that since it's no longer a single line query.

 

[smalpierre]

Oh forgot to mention - that query is for sales orders that are assigned to Driftweed and not yet completed - hence the InstallComplete is null.

But we're not done yet - you might want to add contact data for the apartment manager, or maintenance supervisor so you know who to call about it.

Later in the report, if you select the sales prices you can have your report add up the gross reciepts for that installer too. Starts to get much more complex very quickly if you want to do profitability by job or by year reports, but it's a start. That's all done by the front end though. All the database does is store and retrieve data - well you CAN make the database do it, but you can hammer screws into drywall too

Here's a screen cap of my sql console with the outputted data:

 

[smalpierre]

At this point you can roll the joins through the relationships until you hit the contact info. At this point, we only have 1 contact stored for the company "Slum Villas" so this will work:

select salesorders.InstallAddress,
salesorders.SalePrice,
salesorders.OrderDate,
salesorders.InstallComplete,
floorplans.UnitName,
floorplans.Description,
installers.InstallerName
from installers
inner join salesorders on salesorders.InstallerId = installers.InstallerId
inner join floorplans on floorplans.FloorplanId = salesorders.FloorplanId
inner join company on company.CompanyId = floorplans.CompanyId
inner join company_contacts on company_contacts.CompanyId = company.CompanyId
inner join contacts on contacts.ContactId = company_contacts.ContactId
where installers.installerId = 1
and salesorders.InstallComplete is null;

What happens when we add another contact for that company? I'll show you. I added another contact for the company like this:

insert into contacts (
Position,
FirstName,
LastName,
OfficePhone,
CellPhone,
Email
)
values (
'Maintenance Supervisor',
'Bob',
'Dole',
'555 555 5555',
'555 234 3456',
'[email protected]'
);

Since it's autoincremented userid, and he's the second user added in a row I know his ID is going to be 2. The company, being the first and only one added is company id 1. So we add him to the many to many table that links contacts with companies like this:

insert into company_contacts (companyid, contactid) values (1,2);

 

[smalpierre]

Now we get two rows back for the same sales order, because there are more than one contacts - so it makes a row for each.

If you've got 10 contacts for that company - 10 rows ...

Note the double dashes in front of two fields in the query, that tells the interpreter to ignore that line, which is why the description and order date do not show up in the record set. Did it so it would all fit on the pretty picture

 

[smalpierre]

What I would do about this, is in the front end, I would NOT add contacts to the record set. Instead, I would have the front end display the single record, and display the contacts with a separate query.

So you'd see the single record without contacts. You can set another field for contacts, where you click on a row and it pulls the contact info, or a popup/windows form ... Basically you would want to do some front end processing to handle that.

 

[smalpierre]

Ok, who's got a headache?

 

[Needles]

Python is a scripting language, not a platform

Why Python? I know some people that like it. I tried it years ago but quickly determined that it just wasn't what I wanted.

I was just messing with you. :jester:

I took a interactive python course for game making last year. That is pretty much why I said it.

I know MySql can be programmed with python scripting. Using the MySQLdb module. I am not that talented though.

 

[smalpierre]

You can access any database with python. I don't like the way the syntax works, and I'm more versed in PHP, so that's what I use for webstuff.

Python is actually supposed to be really bad ass for a scripting language, and more polished than PHP in a lot of ways - but I'm so used to C/C++ style syntax ...

 

[smalpierre]

For desktop apps, it's C++ or ObjectPascal for me - compiled machine code will always outperform an interpreted scripting language, and there's the issue of pointers ...

 

[smalpierre]

I use Lazarus for ObjectPascal - it's pretty much a Delphi clone. Very easy to learn, and ObjectPascal forces good programming practices that C++ doesn't.

The only thing I've found that's a major difference is that ObjectPascal doesn't support multiple inheritance - but there are ways around that, and multiple inheritance is kind of a monstrosity anyway.

 

[Needles]

No issue with pointers they don't exist in python unless you are hybridizing with a C module. Use variables.

Python was easy to pick up. I pretty much quit learning C for py. Which I haven't used in a year. I need to relearn some things and the ways of py 3.0.

 

[smalpierre]

That IS the issue with pointers - their lack of existance. Pointers are the single most useful feature of an object oriented language. Some people say that without pointers, it is not really object oriented ...

 

[smalpierre]

Oh, and explicit memory management ...

 

[Needles]

That IS the issue with pointers - their lack of existance. Pointers are the single most useful feature of an object oriented language. Some people say that without pointers, it is not really object oriented ...

I guess. I took a object oriented python course after doing a month or two of basic python at codeacademy. :thumbsup:

 

[macmikeman]

Well if this circle jerk hasn't chased off the OP by now, nothing will.......

 

[Driftweed]

Nope still here...laptop been in the shop...should get it back tomorow.

Buncha nerds... Carry on

So then have we agreed we are going to use mysql over access? Or both?