create database db1; use db1; CREATE TABLE `DocumentsRows` ( `DFYID` tinyint(3) unsigned NOT NULL, `DocumentID` mediumint(8) unsigned NOT NULL, `RowID` smallint(5) unsigned NOT NULL, `Sub2AccSub1AccGAID` tinyint(3) unsigned NOT NULL default '0', `Sub2AccSub1AccID` smallint(5) unsigned NOT NULL default '0', `Sub2AccID` smallint(5) unsigned NOT NULL default '0', `Description` text NOT NULL, `Debit` bigint(20) unsigned NOT NULL, `Credit` bigint(20) unsigned NOT NULL, PRIMARY KEY (`DFYID`,`DocumentID`,`RowID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; delimiter //; CREATE FUNCTION `AccNeg` (SumOfCredit bigint, SumOfDebit bigint, GeneralAccountID tinyint) RETURNS tinyint unsigned DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER COMMENT 'آیا ماهیت حساب نقض شده است؟' BEGIN DECLARE Cash bigint; DECLARE AccType tinyint unsigned default 3; SET Cash = SumOfCredit - SumOfDebit; SET AccType = IF((IF(Cash >= 0, 2, 1) & AccType) > 0, 0, 1); RETURN AccType; END; // delimiter ;// CREATE SQL SECURITY INVOKER VIEW `BalanceSheetGeneral` AS SELECT `DFYID`, `Sub2AccSub1AccGAID`, Sum(`Debit`) AS `TotalDebit`, Sum(`Credit`) AS `TotalCredit`, Sum(`Credit`) - Sum(`Debit`) AS `Cash`, `AccNeg`(Sum(`Credit`), Sum(`Debit`), `Sub2AccSub1AccGAID`) AS `AN` FROM `DocumentsRows` GROUP BY `DFYID`, `Sub2AccSub1AccGAID` ORDER BY `DFYID`, `Sub2AccSub1AccGAID`; CREATE SQL SECURITY INVOKER VIEW `GeneralLedgerOnly` AS (SELECT `DFYID`,`Sub2AccSub1AccGAID`, `DocumentID`, `Debit` AS TotalDebit, `Credit` AS TotalCredit, 0 AS `Cash`, 0 AS `AN` FROM `DocumentsRows`) UNION (SELECT `DFYID`, `Sub2AccSub1AccGAID`, 16777215 AS `DocumentID`, `TotalDebit`, `TotalCredit`, `Cash`, `AN` FROM `BalanceSheetGeneral`) ORDER BY `DFYID`, `Sub2AccSub1AccGAID`, `DocumentID` ; CREATE USER 'Accountant'@'%'; GRANT SELECT ON TABLE `DocumentsRows` TO 'Accountant'@'%'; GRANT EXECUTE ON FUNCTION `AccNeg` TO 'Accountant'@'%'; GRANT SELECT, SHOW VIEW ON TABLE `BalanceSheetGeneral` TO 'Accountant'@'%'; GRANT SELECT, SHOW VIEW ON TABLE `GeneralLedgerOnly` TO 'Accountant'@'%'; GRANT SELECT ON TABLE `DocumentsRows` TO 'Accountant'@'localhost'; GRANT EXECUTE ON FUNCTION `AccNeg` TO 'Accountant'@'localhost'; GRANT SELECT, SHOW VIEW ON TABLE `BalanceSheetGeneral` TO 'Accountant'@'localhost'; GRANT SELECT, SHOW VIEW ON TABLE `GeneralLedgerOnly` TO 'Accountant'@'localhost'; connect (Accountant, localhost, Accountant,,); connection Accountant; use db1; select * from BalanceSheetGeneral; select * from `GeneralLedgerOnly`;