The sum in Custom View not Working and Need invoice file

sakamah · Post by **sakamah** » Wed Mar 15, 2017 3:40 pm

HI

I have three tables

equtable = car main data
mainttable = maintenance
rentaltable = rental

i create custom view to show me for each car total maintenance , total rental and total profit

##############################################
SELECT equtable.ID,
equtable.EQU_TYPE,
equtable.EQU_MARK,
equtable.SN,
equtable.PN,
equtable.COLOR,
equtable.YEAR,
Sum(DISTINCT rentaltable.MONTHLYRATE) AS RentCost,
Sum(DISTINCT mainttable.AMOUNT) AS MaintCost,
(rentaltable.MONTHLYRATE - mainttable.AMOUNT) AS Amount
FROM rentaltable
INNER JOIN equtable ON equtable.SN = rentaltable.SN AND equtable.EQU_TYPE =
rentaltable.EQU_TYPE
INNER JOIN mainttable ON equtable.PN = mainttable.PN AND equtable.SN =
mainttable.SN
GROUP BY equtable.ID,
equtable.EQU_TYPE,
equtable.EQU_MARK,
equtable.SN,
equtable.PN,
equtable.COLOR,
equtable.YEAR,
(rentaltable.MONTHLYRATE - mainttable.AMOUNT)
#################################################

issues:

first one incase that one of these tables for example retaltable just i insert 1 record = 10000 and
the maintable i insert 3 records = 500 / 300 / 500 its will show in custom view two times 10000 and the total profit show = 19200
but the correct profit = 8700
its confused me

the second issue is when one of these tables for example retaltable i have not insert any record and the mainttalbe insert 1 record
so when i need to view the total profit its show me "no records found" even when i change the tables

third thing
how i can get the data from retaltable to show it in invoice to print it

mobhar · Post by **mobhar** » Wed Mar 15, 2017 4:17 pm

Always post your Tables schema, not only Database View. In addition, post also some records example, so others could help you to try/reproduce your issue.

sakamah · Post by **sakamah** » Wed Mar 15, 2017 4:35 pm

The Tables

SELECT
ID, SN, PN, DEC, AMOUNT, DATE, BRANCH, PERSON, INVOICE, NOTE FROM maintform
FROM mainttable

SELECT
ID, SN, PN, EQU_TYPE, DRIVER, COMAPNY, PROJECT, MONTH, CONTRACTPER, MONTHLYRATE, CARD, NOTE
FROM rentaltable

SELECT
ID, EQU_MARK, EQU_TYPE, PN, YEAR, SN, COLOR, STATUS, NOTES
FROM equtable

this example #1 for the first issue which is
((incase that one of these tables for example retaltable just i insert 1 record = 10000 and
the maintable i insert 3 records = 500 / 300 / 500 its will show in custom view two times 10000 and the total profit show = 19200
but the correct profit = 8700))

ID EQU_TYPE EQU_MARK SN PN COLOR YEAR RentCost MaintCost Amount
1 R220LC HUYNDAI HHIHNSY54512 ACG-983 YELLOW 2016 10000 500 9500
1 R220LC HUYNDAI HHIHNSY54512 ACG-983 YELLOW 2016 10000 300 9700

about the second issue i can't put example becuase it's show nothing
((the second issue is when one of these tables for example retaltable i have not insert any record and the mainttalbe insert 1 record
so when i need to view the total profit its show me "no records found" even when i change the tables))

i hope i put all whats you ask for

mobhar · Post by **mobhar** » Wed Mar 15, 2017 5:10 pm

Not yet. I asked you for Tables schema, not just SELECT statement. So, please post the Tables schema by using CREATE TABLE ... statement.

In addition, for record examples, you need to post by using INSERT INTO ... statement, not just the purely data as you posted above.

sakamah · Post by **sakamah** » Wed Mar 15, 2017 5:24 pm

-- mainttable --
CREATE TABLE mainttable (
ID int(11) NOT NULL,
SN varchar(255) DEFAULT NULL,
PN varchar(255) DEFAULT NULL,
DEC longtext,
AMOUNT int(11) DEFAULT '0',
DATE datetime DEFAULT NULL,
BRANCH varchar(255) DEFAULT NULL,
PERSON varchar(255) DEFAULT NULL,
INVOICE longblob,
NOTE longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE mainttable
ADD PRIMARY KEY (ID),
ADD UNIQUE KEY ID (ID);

-- rentaltable --
CREATE TABLE rentaltable (
ID int(11) NOT NULL,
SN varchar(255) DEFAULT NULL,
PN varchar(255) DEFAULT NULL,
EQU_TYPE varchar(255) DEFAULT NULL,
DRIVER varchar(255) DEFAULT NULL,
COMAPNY varchar(255) DEFAULT NULL,
PROJECT varchar(255) DEFAULT NULL,
MONTH varchar(255) DEFAULT NULL,
CONTRACTPER varchar(100) DEFAULT NULL,
MONTHLYRATE int(11) DEFAULT '0',
CARD longblob,
NOTE longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE rentaltable
ADD UNIQUE KEY ID (ID);

-- equtable --
CREATE TABLE equtable (
ID int(11) NOT NULL,
EQU_MARK varchar(255) DEFAULT NULL,
EQU_TYPE varchar(255) DEFAULT NULL,
PN varchar(255) NOT NULL,
YEAR double DEFAULT NULL,
SN varchar(255) NOT NULL,
COLOR varchar(255) DEFAULT NULL,
STATUS varchar(255) DEFAULT NULL,
NOTES longtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE equtable
ADD PRIMARY KEY (SN),
ADD UNIQUE KEY SN (SN),
ADD KEY ID (ID),
ADD KEY SN_2 (SN);

sakamah · Post by **sakamah** » Wed Mar 15, 2017 5:45 pm

INSERT INTO equtable,rentaltable,mainttable
(ID,EQU_TYPE,EQU_MARK,SN.PN,COLOR,YEAR,MONTHLYRATE,AMOUNT) VALUES (1,R220LC,HUYNDAI,HHIHNSY54512,ACG-983,YELLOW,2016,10000,500);

excuse me put i am using custom view sql window my code like this

SELECT equtable.ID,
equtable.EQU_TYPE,
equtable.EQU_MARK,
equtable.SN,
equtable.PN,
equtable.COLOR,
equtable.YEAR,
Sum(DISTINCT rentaltable.MONTHLYRATE) AS RentCost,
Sum(DISTINCT mainttable.AMOUNT) AS MaintCost,
(rentaltable.MONTHLYRATE - mainttable.AMOUNT) AS Amount
FROM rentaltable
INNER JOIN equtable ON equtable.SN = rentaltable.SN AND equtable.EQU_TYPE =
rentaltable.EQU_TYPE
INNER JOIN mainttable ON equtable.PN = mainttable.PN AND equtable.SN =
mainttable.SN
GROUP BY equtable.ID,
equtable.EQU_TYPE,
equtable.EQU_MARK,
equtable.SN,
equtable.PN,
equtable.COLOR,
equtable.YEAR,
(rentaltable.MONTHLYRATE - mainttable.AMOUNT)

mobhar · Post by **mobhar** » Wed Mar 15, 2017 5:57 pm

sakamah wrote:
INSERT INTO equtable,rentaltable,mainttable
(ID,EQU_TYPE,EQU_MARK,SN.PN,COLOR,YEAR,MONTHLYRATE,AMOUNT) VALUES (1,R220LC,HUYNDAI,HHIHNSY54512,ACG-983,YELLOW,2016,10000,500);

Wrong SQL syntax. Please post the correct one.

sakamah · Post by **sakamah** » Wed Mar 15, 2017 11:56 pm

-- equtable --
INSERT INTO equtable (ID,EQU_MARK,EQU_TYPE,SN,PN,YEARS,COLOR,STATUS) VALUES
(1,'R220LC','HUYNDAI','HHIHNSY54512','ACG983',2016,'YELLOW','WORKING');

-- mainttable --
INSERT INTO mainttable (ID,SN,PN,DECC,AMOUNT,DATES,BRANCH,PERSON,NOTE) VALUES
(1,'HHIHNSY54512','ACG983','FILTERS AND OIL',500,'2017-03-10 00:00:00','YANBU','JHONNY','ORDER FROM BOSS');

-- rentaltable --
INSERT INTO mainttable (ID,SN,PN,DRIVER,COMPANY,PROJECT,MONTHS,CONTRACTPER,MONTHLYRATE,NOTE) VALUES
(1,'HHIHNSY54512','ACG983','SALEH ALI','SABIC','YANBU TWO STAGE','JAN-2017','2 MONTHS CONTRACT'15000,'NOTE');

did this helpful :(

mobhar · Post by **mobhar** » Thu Mar 16, 2017 1:24 pm

No, it didn't helpful at all. Many wrong syntax in your INSERT INTO SQL. Double check your script again.

Some of them are caused by unmatched column name/field. The other reasons is caused by missing comma separator between values. Always try that script before post it in this forum.

sakamah · Post by **sakamah** » Thu Mar 16, 2017 2:18 pm

INSERT
INTO
mainttable(
ID,
SN,
PN,
DECC,
AMOUNT,
DATES,
BRANCH,
PERSON,
NOTE
)
VALUES(
1,
'HHIHNSY54512',
'ACG983',
'FILTERS AND OIL',
500,
'2017-03-10 00:00:00',
'YANBU',
'JHONNY',
'ORDER FROM BOSS'
);

INSERT
INTO
RENTALtable(
ID,
SN,
PN,
DRIVER,
COMPANY,
PROJECT,
MONTHS,
CONTRACTPER,
MONTHLYRATE,
NOTE
)
VALUES(
1,
'HHIHNSY54512',
'ACG983',
'SALEH ALI',
'SABIC',
'YANBU TWO STAGE',
'JAN-2017',
'2 MONTHS CONTRACT',
15000,
'NOTE'
);

INSERT
INTO
equtable(
ID,
EQU_MARK,
EQU_TYPE,
SN,
PN,
YEARS,
COLOR,
STATUS
)
VALUES(
1,
'R220LC',
'HUYNDAI',
'HHIHNSY54512',
'ACG983',
2016,
'YELLOW',
'WORKING'
);

mobhar · Post by **mobhar** » Thu Mar 16, 2017 2:22 pm

Unknown column 'DECC' in 'field list'.

Double check your INSERT INTO script, make sure it is always synchronized with the tables schema you sent above.

sakamah · Post by **sakamah** » Thu Mar 16, 2017 2:56 pm

First thing i appreciate your cooperation with me
Second i was putting in this post the code for another database its was an example the below code is working with same table in this post

INSERT
INTO
maintform(
ID,
SN,
PN,
DEC,
AMOUNT,
DATE,
BRANCH,
PERSON,
NOTE
)
VALUES(
1,
'HHIHNSY54512',
'ACG983',
'FILTERS AND OIL',
500,
'2017-03-10 00:00:00',
'YANBU',
'JHONNY',
'ORDER FROM BOSS'
);

INSERT
INTO
rentalform(
ID,
SN,
PN,
DRIVER,
COMPANY,
PROJECT,
MONTH,
CONTRACTPER,
MONTHLYRATE,
NOTE
)
VALUES(
1,
'HHIHNSY54512',
'ACG983',
'SALEH ALI',
'SABIC',
'YANBU TWO STAGE',
'JAN-2017',
'2 MONTHS CONTRACT',
15000,
'NOTE'
);

INSERT
INTO
equform(
ID,
EQU_MARK,
EQU_TYPE,
SN,
PN,
YEAR,
COLOR,
STATUS
)
VALUES(
1,
'R220LC',
'HUYNDAI',
'HHIHNSY54512',
'ACG983',
2016,
'YELLOW',
'WORKING'
);

THNX

mobhar · Post by **mobhar** » Thu Mar 16, 2017 4:06 pm

[Err] 1146 - Table 'invoices.maintform' doesn't exist.

Double check your INSERT INTO script, make sure it is always synchronized with the tables schema you sent above.

sakamah · Post by **sakamah** » Thu Mar 16, 2017 11:04 pm

mobhar wrote:
[Err] 1146 - Table 'invoices.maintform' doesn't exist.

Double check your INSERT INTO script, make sure it is always synchronized
with the tables schema you sent above.
Brother mobhar there is no column by name invoices in MaintForm maybe you meant invoice its as attachment field i removed all attachment fields from insert code.

the insert into script i tried it, its working but my issue when create custom view to show me the sum of (MONTHLYRATE.rentalform) - (AMOUNT.maintform) its working when the data been inserted into all tables also when i insert the AMOUNT or MONTHLYRATE in different rows with same number its only show one row but when been inserted only one table like rentalform the data didn't shown in custom view.

sakamah · Post by **sakamah** » Fri Mar 17, 2017 1:37 am

i solve the issue by create this script :

SELECT F.ID,
F.EQU_TYPE,
F.EQU_MARK,
F.SN,
F.PN,
F.COLOR,
F.YEAR,
S.RentCost,
M.MaintCost,
(S.RentCost) - (M.MaintCost) AS Total Profit
FROM equform AS F
JOIN (SELECT rentalform.SN,
Sum(rentalform.MONTHLYRATE) AS RentCost
FROM rentalform
GROUP BY rentalform.SN) AS S ON F.SN = S.SN
JOIN (SELECT maintform.SN,
Sum(maintform.AMOUNT) AS MaintCost
FROM maintform
GROUP BY maintform.SN) AS M ON F.SN = M.SN

i hope its help for anybody

its left only one issue which is

(( when been inserted only one table like rentalform the data didn't shown in custom view. because there is no data in Maintform table

really thnx waiting response

sakamah · Post by **sakamah** » Sat Mar 18, 2017 2:49 pm

also is there any plugin can i use to create invoice when select any data

sakamah · Post by **sakamah** » Sun Mar 19, 2017 10:14 pm

I am facing also a new issue which is when insert of update the data

all date fields become 00/00/0000

how i can solve it :)