2. PHPRunner General questions
  3. My next project
This topic is locked

My next project

 		7/20/2012 5:31:25 PM
PHPRunner General questions
S
 swanside author

Hello.

My next project involves the following process.

A customer will login and get presented with a list of tables, The main table is this on where they can add a job breakdown.

A job request will be logged into this table by the customer and saved. Once saved, it will email the customer, and the helpdesk that a job has been requested.

--

-- Table structure for table CustomerJob

--
CREATE TABLE IF NOT EXISTS Customerjob (

Order_Date datetime DEFAULT NULL,

CustomerJobNo int(50) NOT NULL AUTO_INCREMENT,

CustomerId varchar(255) DEFAULT NULL,

Job_Description longtext,

CustomerRef varchar(50) DEFAULT NULL,

Note longtext,

Date_Added timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

LocationId varchar(255) DEFAULT NULL,

Logged_By varchar(200) DEFAULT NULL,

PhoneNumber varchar(20) DEFAULT NULL,

CallerName varchar(50) DEFAULT NULL,

PRIMARY KEY (CustomerJobNo),

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=1000000 ;


The helpdesk will then login and see the information in this table that the customer has submitted, they now have to take all that information and enter it into another table like this one.

--

-- Table structure for table job

--
CREATE TABLE IF NOT EXISTS job (

Order_Date datetime DEFAULT NULL,

AttendBy date DEFAULT NULL,

End_Date date DEFAULT NULL,

File_No varchar(50) DEFAULT NULL,

Job_No int(50) NOT NULL AUTO_INCREMENT,

CustomerId varchar(255) DEFAULT NULL,

Order_Time varchar(50) DEFAULT NULL,

Contract longtext,

Job_Description longtext,

VAT decimal(7,2) DEFAULT NULL,

Invoice_Printed tinyint(1) DEFAULT NULL,

Invoice_Printing_Date date DEFAULT NULL,

Invoice_Tax_Date date DEFAULT NULL,

Payment_Received tinyint(1) DEFAULT NULL,

Job_Finished tinyint(1) DEFAULT NULL,

CustomerRef varchar(50) DEFAULT NULL,

Payment_Due_date date DEFAULT NULL,

Payment_Date date DEFAULT NULL,

Note longtext,

Date_Added timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

EngineerId varchar(50) NOT NULL,

LocationId varchar(255) DEFAULT NULL,

JobTypeId varchar(50) NOT NULL,

Exported tinyint(1) DEFAULT NULL,

Logged_By varchar(200) DEFAULT NULL,

PhoneNumber varchar(20) DEFAULT NULL,

ResponseCatagories varchar(10) DEFAULT NULL,

CallerName varchar(50) DEFAULT NULL,

CustomerEmail varchar(100) DEFAULT NULL,

PRIMARY KEY (Job_No),

UNIQUE KEY File_No (File_No)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT AUTO_INCREMENT=1032168 ;


What I am looking for is an easy way to copy the information from one table to another, either the helpdesk people can log in to the customers table and check a box and that will import the information into the job table, or from the job table, the helpdesk can add a new job and select all the information from the customer table to import.
Is there an easy way of doing this?

Thanks

Paul
C
 		cgphp 7/20/2012
  1. Add a boolean field to the Customerjob table and call it "Imported" for eaxmple.
  2. Run a cron job on hourly or minute basis that select all record from the Customerjob table where the "Imported" field is false
  3. Insert the selected records in the Job table
  4. Update the "Imported" field of the selected records to true
S
 		swanside author 7/20/2012

Thanks for that, but, it may as well be in Swahili I dont have a clue about any of that!! haha
I dont want it to run on its own, it has to be done by the helpdesk operators.

They have to login see the job in the customer table and copy it to the job table,

I will try and do a small demo of what I am looking at.

Cheers

Paul
C
 		cgphp 7/20/2012
  1. Add a custom button to the CustomerJob table
  2. Check the selected records
  3. Copy them to the Job table
    In the "Server" section of the custom button you can access the keys of the selected records.
S
 		swanside author 7/20/2012

OK, I have made it a bit simpler, If I can get this to work, I can figure out the rest.
I have two tables as follows



--

-- Table structure for table customerjob

--
CREATE TABLE IF NOT EXISTS customerjob (

CustomerJobId int(11) NOT NULL AUTO_INCREMENT,

CustomerName varchar(50) DEFAULT NULL,

Date date DEFAULT NULL,

JobRequest varchar(50) DEFAULT NULL,

PRIMARY KEY (CustomerJobId)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;



and



--

-- Table structure for table job

--
CREATE TABLE IF NOT EXISTS job (

JobNo int(11) NOT NULL AUTO_INCREMENT,

CustomerJobId int(11) DEFAULT NULL,

CustomerName varchar(50) DEFAULT NULL,

CustomerRequestDate date DEFAULT NULL,

Date date DEFAULT NULL,

JobRequest varchar(50) DEFAULT NULL,

Job varchar(50) DEFAULT NULL,

JobApproved tinyint(4) DEFAULT NULL,

PRIMARY KEY (JobNo)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


When a customer logs in, they can go to add new and add the information in the customerjob table save it and exit out.
The people on the helpdesk log in, and can see the customerjob and the job tables. They go to the customerjob table and see there is a new job been entered, they then press the copy button and it puts the information into the job table, so the following happens.

The job table creates a new line with the JobNo as Auto Increment

The data from the following copies.

customerjob.CustomerName > job.CustomerName

customerjob.Date > job.CustomerRequestDate

customerjob.JobRequest > job.JobRequest
Then the helpdesk operator can edit the rest of the fields and save it.

I was trying to load it up to the demo account, but it keeps failing with a message to contact support
C
 		cgphp 7/20/2012
  1. Add a custom button to the CustomerJob table
  2. Check the selected records
  3. Copy them to the Job table
    In the "Server" section of the custom button you can access the keys of the selected records.
S
 		swanside author 7/20/2012


  1. Add a custom button to the CustomerJob table
  2. Check the selected records
  3. Copy them to the Job table
    In the "Server" section of the custom button you can access the keys of the selected records.


Thanks,

I put in the server section the following
insert into 'job' $keys['CustomerJobId']
But it says syntax error, unexpected T_STRING in line 1
Cheers

Paul
C
 		cgphp 7/20/2012

The $keys array contains the key values of the selected records. You have to build the select query for the CustomerJob table and the insert query for the Job table.
S
 		swanside author 7/20/2012



The $keys array contains the key values of the selected records. You have to build the select query for the CustomerJob table and the insert query for the Job table.


OK I tried this > INSERT INTO job (CustomerName,CustomerRequestDate,JobRequest)

SELECT customerjob.CustomerName,customerjob.Date,customerjob.JobRequest

FROM customerjob;

where $keys['CustomerJobId']


But it still dont get it!
Thanks

Paul
C
 		cgphp 7/20/2012

There is an alternative approach to get the selected records. Check the example 2 of this page: http://xlinesoft.com/phprunner/docs/inserting_button.htm
C
 		cgphp 7/20/2012

Check the following link if you want to know the correct syntax of the INSERT INTO statement: http://www.w3schools.com/sql/sql_insert.asp
S
 		swanside author 7/20/2012



There is an alternative approach to get the selected records. Check the example 2 of this page: http://xlinesoft.com/phprunner/docs/inserting_button.htm


That is to send an email > Example 2. Send records selected on the List page via email


I want to get the selected records from the customerjob table and copy them into the job table
Thanks

paul
C
 		cgphp 7/20/2012

It's only an example to get inspiration.
S
 		swanside author 7/20/2012



It's only an example to get inspiration.



Haha, OK Thanks.
Its a pity I am working offshore now, as I could really do with a drink now ;(
Cheers

Paul
C
 		cgphp 7/21/2012

In the "Server" section of the custom button, enter the following code:

global $conn;

$result['selected_records'] = 0;

while($data = $button->getNextSelectedRecord())

{

db_exec("INSERT INTO job (CustomerName,Date,JobRequest) VALUES ('".$data['CustomerName']."','".$data['Date']."','".$data['JobRequest']."')",$conn);

$result['selected_records']++;

}


In the "Client after" section of the custom button, enter the following code:

alert(result['selected_records'] + " records have been copied");
S
 		swanside author 7/21/2012



In the "Server" section of the custom button, enter the following code:

global $conn;

$result['selected_records'] = 0;

while($data = $button->getNextSelectedRecord())

{

db_exec("INSERT INTO job (CustomerName,Date,JobRequest) VALUES ('".$data['CustomerName']."','".$data['Date']."','".$data['JobRequest']."')",$conn);

$result['selected_records']++;

}


In the "Client after" section of the custom button, enter the following code:

alert(result['selected_records'] + " records have been copied");



Mate, your a star.

Sorry for the delay, just started shift.

Now I can build on that.

Thanks very much