| |
 Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'showCollectionOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
showCollectionOpac |
PROCEDURE |
showCollectionOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN keyword TEXT
| |
BEGIN
SELECT c.id,c.`Catalog_id`, m.`Name` media,c.`NomorBarcode`, c.`NoInduk`, c.`CallNumber`, r.`Name` akses, l.`Name` lokasi, s.`Name` ketersediaan, loc.`Name` namaperpus, c.BookingMemberID, c.BookingExpiredDate
FROM collections c
LEFT JOIN collectionmedias m ON c.`Media_id`=m.`ID`
LEFT JOIN collectionrules r ON c.`Rule_id`=r.`ID`
LEFT JOIN collectionstatus s ON c.`Status_id`=s.`ID`
LEFT JOIN locations l ON c.`Location_id`=l.`ID`
LEFT join location_library loc on c.`Location_Library_id` = loc.`id`
WHERE c.`Catalog_id`=keyword;
END
| root@localhost |
2016-06-06 14:58:42 |
2016-06-06 14:58:42 |
NO_AUTO_VALUE_ON_ZERO |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT c.id,c.`Catalog_id`, m.`Name` media,c.`NomorBarcode`, c.`NoInduk`, c.`CallNumber`, r.`Name` akses, l.`Name` lokasi, s.`Name` ketersediaan, loc.`Name` namaperpus, c.BookingMemberID, c.BookingExpiredDate
FROM collections c
LEFT JOIN collectionmedias m ON c.`Media_id`=m.`ID`
LEFT JOIN collectionrules r ON c.`Rule_id`=r.`ID`
LEFT JOIN collectionstatus s ON c.`Status_id`=s.`ID`
LEFT JOIN locations l ON c.`Location_id`=l.`ID`
LEFT join location_library loc on c.`Location_Library_id` = loc.`id`
WHERE c.`Catalog_id`=keyword;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'showKontenDigital\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
showKontenDigital |
PROCEDURE |
showKontenDigital |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN keyword TEXT
| |
BEGIN
Select C.ID,C.Catalog_id,C.FileURL,C.FileFlash,C.IsPublish, (
SELECT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE('.',REVERSE(FileURL)))+2)) as FormatFile
FROM catalogfiles C where IsPublish <> 0 and Catalog_id =keyword;
END
| root@localhost |
2016-06-06 14:58:42 |
2016-06-06 14:58:42 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
Select C.ID,C.Catalog_id,C.FileURL,C.FileFlash,C.IsPublish, (
SELECT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE('.',REVERSE(FileURL)))+2)) as FormatFile
FROM catalogfiles C where IsPublish <> 0 and Catalog_id =keyword;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'topCollectionOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
topCollectionOpac |
PROCEDURE |
topCollectionOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
| |
BEGIN
SELECT COUNT(collection_id) entry, c.title AS title, c.id AS id, c.author AS author,
c.PublishLocation AS PublishLocation, c.publishyear AS YEAR,
CONCAT(c.PublishLocation, ' ', c.Publisher, ' ' ,c.publishyear) AS penerbitan, w.name AS wn,
c.publisher AS publisher, c.publishyear AS YEAR, CONCAT(c.publisher, ' : ', c.publishyear) AS penerbitan,
TRIM(c.coverurl) AS coverurl, c.callnumber AS callnumber
FROM collectionloanitems h
INNER JOIN collections s ON s.id = h.collection_id
INNER JOIN catalogs c ON c.id = s.catalog_id and c.ISOPAC =1
LEFT JOIN worksheets w ON w.ID = c.Worksheet_id
GROUP BY c.id ORDER BY entry DESC;
END
| root@localhost |
2016-06-06 14:58:42 |
2016-06-06 14:58:42 |
NO_AUTO_VALUE_ON_ZERO |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT COUNT(collection_id) entry, c.title AS title, c.id AS id, c.author AS author,
c.PublishLocation AS PublishLocation, c.publishyear AS YEAR,
CONCAT(c.PublishLocation, ' ', c.Publisher, ' ' ,c.publishyear) AS penerbitan, w.name AS wn,
c.publisher AS publisher, c.publishyear AS YEAR, CONCAT(c.publisher, ' : ', c.publishyear) AS penerbitan,
TRIM(c.coverurl) AS coverurl, c.callnumber AS callnumber
FROM collectionloanitems h
INNER JOIN collections s ON s.id = h.collection_id
INNER JOIN catalogs c ON c.id = s.catalog_id and c.ISOPAC =1
LEFT JOIN worksheets w ON w.ID = c.Worksheet_id
GROUP BY c.id ORDER BY entry DESC;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'TO_MARC\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
TO_MARC |
PROCEDURE |
TO_MARC |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN CatID INT
| |
BEGIN
select TO_MARC(CatID);
END
| root@localhost |
2016-06-06 14:58:42 |
2016-06-06 14:58:42 |
NO_AUTO_VALUE_ON_ZERO |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
select TO_MARC(CatID);
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'pencarianSederhanaOpacLimit1\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
pencarianSederhanaOpacLimit1 |
PROCEDURE |
pencarianSederhanaOpacLimit1 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN limit1 INT,
IN limit2 INT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT
| |
BEGIN
SELECT *
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author LIKE concat('%',concat(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear LIKE fPublishYear) AND
IF(fBahasa='',1=1,bahasa LIKE fBahasa) AND
IF(fSubject='',1=1,Subject LIKE fSubject)
LIMIT limit1,limit2;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT *
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author LIKE concat('%',concat(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear LIKE fPublishYear) AND
IF(fBahasa='',1=1,bahasa LIKE fBahasa) AND
IF(fSubject='',1=1,Subject LIKE fSubject)
LIMIT limit1,limit2;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'marcCatalogOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
marcCatalogOpac |
PROCEDURE |
marcCatalogOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN catID int
| |
BEGIN
select * from catalog_ruas where catalogId = catID order by Tag asc;
END
| root@localhost |
2016-06-06 14:58:41 |
2016-06-06 14:58:41 |
NO_AUTO_VALUE_ON_ZERO |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
select * from catalog_ruas where catalogId = catID order by Tag asc;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'newCollectionOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
newCollectionOpac |
PROCEDURE |
newCollectionOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
| |
BEGIN
SELECT c.id AS id, c.title AS title, c.author AS author, c.PublishLocation AS PublishLocation,
c.publishyear AS YEAR, CONCAT(c.PublishLocation, ' ' , c.Publisher, ' ' ,c.publishyear) AS penerbitan,
c.publisher AS publisher, c.publishyear AS YEAR, CONCAT(c.publisher, ' : ', c.publishyear) AS penerbitan,
s.CreateDate, w.name AS wn, TRIM(c.coverurl) AS coverurl, c.callnumber AS callnumber
FROM collections s
INNER JOIN catalogs c ON c.id = s.catalog_id
LEFT JOIN worksheets w ON w.ID = c.Worksheet_id
WHERE c.isopac =1
GROUP BY c.id
ORDER BY s.createdate DESC;
END
| root@localhost |
2016-06-06 14:58:41 |
2016-06-06 14:58:41 |
NO_AUTO_VALUE_ON_ZERO |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT c.id AS id, c.title AS title, c.author AS author, c.PublishLocation AS PublishLocation,
c.publishyear AS YEAR, CONCAT(c.PublishLocation, ' ' , c.Publisher, ' ' ,c.publishyear) AS penerbitan,
c.publisher AS publisher, c.publishyear AS YEAR, CONCAT(c.publisher, ' : ', c.publishyear) AS penerbitan,
s.CreateDate, w.name AS wn, TRIM(c.coverurl) AS coverurl, c.callnumber AS callnumber
FROM collections s
INNER JOIN catalogs c ON c.id = s.catalog_id
LEFT JOIN worksheets w ON w.ID = c.Worksheet_id
WHERE c.isopac =1
GROUP BY c.id
ORDER BY s.createdate DESC;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'get_stat_jenis_pendidikan_tahun\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
get_stat_jenis_pendidikan_tahun |
PROCEDURE |
get_stat_jenis_pendidikan_tahun |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN `Bfrom` TEXT,
IN `Bto` TEXT
| |
BEGIN
SELECT
`mp`.`Nama` AS `Keterangan`,
COUNT(`mem`.`ID`) AS `Jumlah`,
DATE(`mem`.`CreateDate`) AS `Tanggal`
FROM
(`master_pendidikan` `mp`
LEFT JOIN `members` `mem` ON ((`mem`.`EducationLevel_id` = `mp`.`id`)))
WHERE
(`mem`.`EducationLevel_id` IS NOT NULL)
AND
(CAST(`mem`.`CreateDate`
AS DATE) BETWEEN Bfrom AND Bto )
GROUP BY `mp`.`Nama` , DATE_FORMAT(`mem`.`CreateDate`, '%Y')
ORDER BY `mp`.`id` , `mem`.`CreateDate`;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT
`mp`.`Nama` AS `Keterangan`,
COUNT(`mem`.`ID`) AS `Jumlah`,
DATE(`mem`.`CreateDate`) AS `Tanggal`
FROM
(`master_pendidikan` `mp`
LEFT JOIN `members` `mem` ON ((`mem`.`EducationLevel_id` = `mp`.`id`)))
WHERE
(`mem`.`EducationLevel_id` IS NOT NULL)
AND
(CAST(`mem`.`CreateDate`
AS DATE) BETWEEN Bfrom AND Bto )
GROUP BY `mp`.`Nama` , DATE_FORMAT(`mem`.`CreateDate`, '%Y')
ORDER BY `mp`.`id` , `mem`.`CreateDate`;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempSederhanaOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempSederhanaOpac |
PROCEDURE |
insertTempSederhanaOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN keyword TEXT,
IN tag TEXT,
IN worksheet VARCHAR(255),
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN fromTgl TEXT,
IN toTgl TEXT,
IN isLKD TEXT,
IN Location TEXT
| |
BEGIN
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
CallNumber TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF isLKD = 1
THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE('.',REVERSE(FileURL)))+2) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF ON CAT.ID = CF.Catalog_id
WHERE
EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID
)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
HAVING CASE worksheet
WHEN 'pdf' THEN KONTEN_DIGITAL = worksheet
WHEN 'doc' THEN KONTEN_DIGITAL = worksheet
WHEN 'xls' THEN KONTEN_DIGITAL = worksheet
WHEN 'rar' THEN KONTEN_DIGITAL = worksheet
WHEN 'Semua Format File' THEN 1 = 1
END
;
ELSE
IF worksheet='4' THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND IF(fromTgl!='' AND toTgl!='' ,col.TANGGAL_TERBIT_EDISI_SERIAL BETWEEN fromTgl AND toTgl,1=1)
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
;
ELSE
IF location<>0 THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND col.Location_id = Location AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
;
ELSE
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
;
END IF;
END IF;
END IF;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
CallNumber TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF isLKD = 1
THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE('.',REVERSE(FileURL)))+2) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF ON CAT.ID = CF.Catalog_id
WHERE
EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID
)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
HAVING CASE worksheet
WHEN 'pdf' THEN KONTEN_DIGITAL = worksheet
WHEN 'doc' THEN KONTEN_DIGITAL = worksheet
WHEN 'xls' THEN KONTEN_DIGITAL = worksheet
WHEN 'rar' THEN KONTEN_DIGITAL = worksheet
WHEN 'Semua Format File' THEN 1 = 1
END
;
ELSE
IF worksheet='4' THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND IF(fromTgl!='' AND toTgl!='' ,col.TANGGAL_TERBIT_EDISI_SERIAL BETWEEN fromTgl AND toTgl,1=1)
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
;
ELSE
IF location<>0 THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND col.Location_id = Location AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
;
ELSE
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.CallNumber ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
;
END IF;
END IF;
END IF;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'pencarianLanjutLimitOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
pencarianLanjutLimitOpac |
PROCEDURE |
pencarianLanjutLimitOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN limit1 INT,
IN limit2 INT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT
| |
BEGIN
SELECT CatalogId,title kalimat2,author,publisher,PublishLocation,PublishYear,Subject,bahasa,CoverURL,
worksheet_id,worksheet,
JML_BUKU,
ALL_BUKU,
KONTEN_DIGITAL
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author = fAuthor) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear = fPublishYear) AND
IF(fBahasa='',1=1,bahasa LIKE fBahasa) AND
IF(fSubject='',1=1,Subject = fSubject)
LIMIT limit1,limit2;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT CatalogId,title kalimat2,author,publisher,PublishLocation,PublishYear,Subject,bahasa,CoverURL,
worksheet_id,worksheet,
JML_BUKU,
ALL_BUKU,
KONTEN_DIGITAL
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author = fAuthor) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear = fPublishYear) AND
IF(fBahasa='',1=1,bahasa LIKE fBahasa) AND
IF(fSubject='',1=1,Subject = fSubject)
LIMIT limit1,limit2;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempTelusurOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempTelusurOpac |
PROCEDURE |
insertTempTelusurOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN tag TEXT,
IN findby TEXT,
IN fquery TEXT,
IN fquery2 TEXT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN FSubject TEXT,
IN fBahasa TEXT,
IN isLKD TEXT
| |
BEGIN
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
Subject TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
if isLKD = 1 then
INSERT INTO tempCariOpac
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < now()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
When 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
else
INSERT INTO tempCariOpac
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < now()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
When 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
end if;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
Subject TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
if isLKD = 1 then
INSERT INTO tempCariOpac
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < now()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
When 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
else
INSERT INTO tempCariOpac
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < now()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
When 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
end if;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempTelusurArticle\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempTelusurArticle |
PROCEDURE |
insertTempTelusurArticle |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN tag TEXT,
IN findby TEXT,
IN fquery TEXT,
IN fquery2 TEXT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN FSubject TEXT,
IN fBahasa TEXT,
IN isLKD TEXT
| |
BEGIN
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF isLKD = 1 THEN
INSERT INTO tempCariArticle
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < NOW()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF ON CAT.ID = CF.Catalog_id
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
WHEN 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
ELSE
INSERT INTO tempCariArticle
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < NOW()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
WHEN 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
END IF;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF isLKD = 1 THEN
INSERT INTO tempCariArticle
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < NOW()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF ON CAT.ID = CF.Catalog_id
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
WHEN 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
ELSE
INSERT INTO tempCariArticle
SELECT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.Subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND BookingExpiredDate < NOW()) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE CAT.isopac=1 AND
IF(fAuthor='',1=1,CAT.Author = fAuthor) AND
IF(fPublisher='',1=1,CAT.Publisher = fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear = fPublishYear) AND
IF(fSubject='',1=1,CAT.SUBJECT = fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CASE tag
WHEN 'Author' THEN CAT.Author = fquery2
WHEN 'Subject' THEN CAT.subject = fquery2
WHEN 'Publisher' THEN CAT.Publisher = fquery2
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery2
WHEN 'PublishYear' THEN CAT.PublishYear = fquery2
END
AND
CASE findBy
WHEN 'Alphabetical' THEN 1=1
WHEN 'Author' THEN CAT.Author = fquery
WHEN 'Subject' THEN CAT.subject = fquery
WHEN 'Publisher' THEN CAT.Publisher = fquery
WHEN 'PublishLocation' THEN CAT.PublishLocation = fquery
WHEN 'PublishYear' THEN CAT.PublishYear = fquery
END;
END IF;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempSederhanaOpac0\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempSederhanaOpac0 |
PROCEDURE |
insertTempSederhanaOpac0 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN keyword TEXT,
IN tag TEXT,
IN worksheet VARCHAR(255),
IN limit1 INT,
IN limit2 INT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN fromTgl TEXT,
IN toTgl TEXT,
IN isLKD TEXT,
IN Location TEXT
| |
BEGIN
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF isLKD = 1
THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE('.',REVERSE(FileURL)))+2) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF ON CAT.ID = CF.Catalog_id
WHERE
EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID
)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
HAVING CASE worksheet
WHEN 'pdf' THEN KONTEN_DIGITAL = worksheet
WHEN 'doc' THEN KONTEN_DIGITAL = worksheet
WHEN 'xls' THEN KONTEN_DIGITAL = worksheet
WHEN 'rar' THEN KONTEN_DIGITAL = worksheet
WHEN 'Semua Format File' THEN 1 = 1
END
LIMIT limit1,limit2;
ELSE
IF worksheet='4' THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND IF(fromTgl!='' AND toTgl!='' ,col.TANGGAL_TERBIT_EDISI_SERIAL BETWEEN fromTgl AND toTgl,1=1)
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
LIMIT limit1,limit2;
ELSE
IF location<>0 THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.Value LIKE keyword
AND col.Location_id = Location
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
LIMIT limit1,limit2;
ELSE
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
LIMIT limit1,limit2;
END IF;
END IF;
END IF;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF isLKD = 1
THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE('.',REVERSE(FileURL)))+2) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
INNER JOIN catalogfiles CF ON CAT.ID = CF.Catalog_id
WHERE
EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID
)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
HAVING CASE worksheet
WHEN 'pdf' THEN KONTEN_DIGITAL = worksheet
WHEN 'doc' THEN KONTEN_DIGITAL = worksheet
WHEN 'xls' THEN KONTEN_DIGITAL = worksheet
WHEN 'rar' THEN KONTEN_DIGITAL = worksheet
WHEN 'Semua Format File' THEN 1 = 1
END
LIMIT limit1,limit2;
ELSE
IF worksheet='4' THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND IF(fromTgl!='' AND toTgl!='' ,col.TANGGAL_TERBIT_EDISI_SERIAL BETWEEN fromTgl AND toTgl,1=1)
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
LIMIT limit1,limit2;
ELSE
IF location<>0 THEN
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.Value LIKE keyword
AND col.Location_id = Location
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
LIMIT limit1,limit2;
ELSE
INSERT INTO tempCariOpac
SELECT DISTINCT CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < NOW() || BookingExpiredDate IS NULL)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R
WHERE
CASE tag
WHEN 'Judul' THEN R.TAG IN ('240','245','246','440','740')
WHEN 'Pengarang' THEN R.TAG IN ('100','110','111','700','710','711','800','810','811')
WHEN 'Penerbit' THEN R.TAG IN ('260','264')
WHEN 'Subyek' THEN R.TAG IN ('600','610','611','650','651')
WHEN 'Nomor Panggil' THEN R.TAG IN ('090','084')
WHEN 'ISBN' THEN R.TAG IN ('020')
WHEN 'ISSN' THEN R.TAG IN ('022')
WHEN 'ISMN' THEN R.TAG IN ('024')
WHEN 'Semua Ruas' THEN 1 = 1
END
AND R.Value LIKE keyword
AND R.CATALOGID=CAT.ID)
AND IF(worksheet='Semua Jenis bahan',1=1,CAT.Worksheet_id = worksheet)
AND
IF(fAuthor='',1=1,CAT.Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,CAT.Publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,CAT.PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,CAT.PublishYear LIKE fPublishYear) AND
IF(fSubject='',1=1,CAT.Subject LIKE fSubject) AND
IF(fBahasa='',1=1,CAT.Languages LIKE fBahasa) AND
CAT.isopac=1
LIMIT limit1,limit2;
END IF;
END IF;
END IF;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempLanjutOpac0\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempLanjutOpac0 |
PROCEDURE |
insertTempLanjutOpac0 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN worksheet TEXT,
IN bahasa TEXT,
IN targetPembaca TEXT,
IN bentukKarya TEXT,
IN keyword TEXT,
IN limit1 TEXT,
IN limit2 TEXT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN isLKD TEXT,
IN lokasi TEXT
| |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca,queryloc,querylocjoin TEXT;
SET querys='';
SET querys2='';
SET querys3='';
SET queryloc='';
SET querylocjoin='';
SET bhs='';
SET karya='';
SET pembaca='';
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
IF lokasi <> 0 THEN SET queryloc = CONCAT(queryloc,' AND col.Location_id = ''',lokasi,'''');
END IF;
IF lokasi <> 0 THEN SET querylocjoin = CONCAT(querys,' LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID ');
END IF;
IF worksheet <> 'Semua Format FIle' AND isLKD = 1 THEN SET querys2 = CONCAT(querys2,' HAVING KONTEN_DIGITAL = ''',worksheet,'''');
END IF;
IF isLKD = 1 THEN SET querys3 = CONCAT(querys3,' INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id ');
END IF;
SET @query_as_string=CONCAT('
INSERT INTO tempCariOpac
SELECT distinct CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < now() || BookingExpiredDate is null)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
-- LEFT JOIN collections col ON col.Catalog_id = CAT.ID
',querys3,'
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R ',querylocjoin,'
WHERE
',keyword,queryloc,bhs,karya,pembaca,' AND R.CATALOGID=CAT.ID) AND CAT.isopac=1',querys,querys2,' LIMIT ',limit1,',',limit2,' ');
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca,queryloc,querylocjoin TEXT;
SET querys='';
SET querys2='';
SET querys3='';
SET queryloc='';
SET querylocjoin='';
SET bhs='';
SET karya='';
SET pembaca='';
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
SUBJECT TEXT,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
IF lokasi <> 0 THEN SET queryloc = CONCAT(queryloc,' AND col.Location_id = ''',lokasi,'''');
END IF;
IF lokasi <> 0 THEN SET querylocjoin = CONCAT(querys,' LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID ');
END IF;
IF worksheet <> 'Semua Format FIle' AND isLKD = 1 THEN SET querys2 = CONCAT(querys2,' HAVING KONTEN_DIGITAL = ''',worksheet,'''');
END IF;
IF isLKD = 1 THEN SET querys3 = CONCAT(querys3,' INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id ');
END IF;
SET @query_as_string=CONCAT('\r\n INSERT INTO tempCariOpac\r\n SELECT distinct CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id, \r\n (SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,\r\n (SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,\r\n (SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < now() || BookingExpiredDate is null)) JML_BUKU,\r\n (SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,\r\n (SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','') \r\n FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL\r\n FROM catalogs CAT \r\n -- LEFT JOIN collections col ON col.Catalog_id = CAT.ID\r\n \r\n ',querys3,' \r\n WHERE EXISTS \r\n (SELECT 1\r\n FROM catalog_ruas R ',querylocjoin,' \r\n WHERE \r\n \r\n \r\n ',keyword,queryloc,bhs,karya,pembaca,' AND R.CATALOGID=CAT.ID) AND CAT.isopac=1',querys,querys2,' LIMIT ',limit1,',',limit2,' ');
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempSederhanaArticle\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempSederhanaArticle |
PROCEDURE |
insertTempSederhanaArticle |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN keyword TEXT,
IN tag TEXT,
IN isLimit INT,
IN limit1 INT,
IN limit2 INT
| |
BEGIN
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
serial_articles_id INT(11),
Article_type VARCHAR(100),
Creator_article VARCHAR(200),
title_article TEXT,
content_article TEXT,
subject_article VARCHAR(200),
EDISISERIAL VARCHAR(200),
TANGGAL_TERBIT_EDISI_SERIAL VARCHAR(200),
CatalogId INT(11),
title TEXT,
bahasa VARCHAR(100),
author VARCHAR(200),
publisher VARCHAR(100),
PublishLocation VARCHAR(100),
PublishYear VARCHAR(100),
SUBJECT VARCHAR(200),
CoverURL VARCHAR(100),
worksheet_id INT,
worksheet VARCHAR(100),
KONTEN_DIGITAL VARCHAR(100)
);
IF isLimit = 1
THEN
INSERT INTO tempCariArticle
SELECT art.id AS id, art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id,
w.name worksheet,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ') FROM serial_articlefiles WHERE Articles_id = art.id AND art.Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM serial_articles art
-- update nambah join ini
JOIN serial_articles_repeatable sar ON sar.serial_article_ID = art.id
-- batas akhir update
JOIN catalogs CAT ON art.Catalog_id = CAT.ID
lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id
WHERE
CASE tag
WHEN 'Judul' THEN art.Title LIKE keyword
-- update filter
WHEN 'Pengarang' THEN (sar.article_field IN ('Kreator','Kontributor') AND sar.value LIKE keyword)
WHEN 'Subyek' THEN (sar.article_field IN ('Subjek') AND sar.value LIKE keyword)
-- batas update filter
/*WHEN 'Pengarang' THEN art.Creator LIKE keyword
WHEN 'Subyek' THEN art.Subject LIKE keyword*/
WHEN 'Sembarang' THEN 1 = 1
END
AND CAT.isopac=1
AND art.ISOPAC=1
AND w.ISSERIAL = 1
GROUP BY art.id
LIMIT limit1,limit2;
ELSE
INSERT INTO tempCariArticle
SELECT art.id AS id, art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id,
w.name worksheet,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ') FROM serial_articlefiles WHERE Articles_id = art.id AND art.Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM serial_articles art
-- update nambah join ini
JOIN serial_articles_repeatable sar ON sar.serial_article_ID = art.id
-- batas akhir update
JOIN catalogs CAT ON art.Catalog_id = CAT.ID
lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id
WHERE
CASE tag
WHEN 'Judul' THEN art.Title LIKE keyword
-- update filter
WHEN 'Pengarang' THEN (sar.article_field IN ('Kreator','Kontributor') AND sar.value LIKE keyword)
WHEN 'Subyek' THEN (sar.article_field IN ('Subjek') AND sar.value LIKE keyword)
-- batas update filter
/*WHEN 'Pengarang' THEN art.Creator LIKE keyword
WHEN 'Subyek' THEN art.Subject LIKE keyword*/
WHEN 'Sembarang' THEN 1 = 1
END
AND CAT.isopac=1
AND art.ISOPAC=1
AND w.ISSERIAL = 1
GROUP BY art.id;
END IF;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
serial_articles_id INT(11),
Article_type VARCHAR(100),
Creator_article VARCHAR(200),
title_article TEXT,
content_article TEXT,
subject_article VARCHAR(200),
EDISISERIAL VARCHAR(200),
TANGGAL_TERBIT_EDISI_SERIAL VARCHAR(200),
CatalogId INT(11),
title TEXT,
bahasa VARCHAR(100),
author VARCHAR(200),
publisher VARCHAR(100),
PublishLocation VARCHAR(100),
PublishYear VARCHAR(100),
SUBJECT VARCHAR(200),
CoverURL VARCHAR(100),
worksheet_id INT,
worksheet VARCHAR(100),
KONTEN_DIGITAL VARCHAR(100)
);
IF isLimit = 1
THEN
INSERT INTO tempCariArticle
SELECT art.id AS id, art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id,
w.name worksheet,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ') FROM serial_articlefiles WHERE Articles_id = art.id AND art.Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM serial_articles art
-- update nambah join ini
JOIN serial_articles_repeatable sar ON sar.serial_article_ID = art.id
-- batas akhir update
JOIN catalogs CAT ON art.Catalog_id = CAT.ID
lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id
WHERE
CASE tag
WHEN 'Judul' THEN art.Title LIKE keyword
-- update filter
WHEN 'Pengarang' THEN (sar.article_field IN ('Kreator','Kontributor') AND sar.value LIKE keyword)
WHEN 'Subyek' THEN (sar.article_field IN ('Subjek') AND sar.value LIKE keyword)
-- batas update filter
/*WHEN 'Pengarang' THEN art.Creator LIKE keyword
WHEN 'Subyek' THEN art.Subject LIKE keyword*/
WHEN 'Sembarang' THEN 1 = 1
END
AND CAT.isopac=1
AND art.ISOPAC=1
AND w.ISSERIAL = 1
GROUP BY art.id
LIMIT limit1,limit2;
ELSE
INSERT INTO tempCariArticle
SELECT art.id AS id, art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id,
w.name worksheet,
(SELECT GROUP_CONCAT(DISTINCT SUBSTR(fileURL,INSTR(fileURL, '.')+1) SEPARATOR ', ') FROM serial_articlefiles WHERE Articles_id = art.id AND art.Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM serial_articles art
-- update nambah join ini
JOIN serial_articles_repeatable sar ON sar.serial_article_ID = art.id
-- batas akhir update
JOIN catalogs CAT ON art.Catalog_id = CAT.ID
lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id
WHERE
CASE tag
WHEN 'Judul' THEN art.Title LIKE keyword
-- update filter
WHEN 'Pengarang' THEN (sar.article_field IN ('Kreator','Kontributor') AND sar.value LIKE keyword)
WHEN 'Subyek' THEN (sar.article_field IN ('Subjek') AND sar.value LIKE keyword)
-- batas update filter
/*WHEN 'Pengarang' THEN art.Creator LIKE keyword
WHEN 'Subyek' THEN art.Subject LIKE keyword*/
WHEN 'Sembarang' THEN 1 = 1
END
AND CAT.isopac=1
AND art.ISOPAC=1
AND w.ISSERIAL = 1
GROUP BY art.id;
END IF;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempLanjutArticle0\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempLanjutArticle0 |
PROCEDURE |
insertTempLanjutArticle0 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN worksheet TEXT,
IN bahasa TEXT,
IN targetPembaca TEXT,
IN bentukKarya TEXT,
IN keyword TEXT,
IN limit1 TEXT,
IN limit2 TEXT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN isLKD TEXT
| |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca TEXT;
SET querys='';
SET querys2='';
SET querys3='';
SET bhs='';
SET karya='';
SET pembaca='';
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
Article_type VARCHAR(100),
Creator_article VARCHAR(200),
title_article TEXT,
content_article TEXT,
subject_article VARCHAR(200),
EDISISERIAL VARCHAR(200),
TANGGAL_TERBIT_EDISI_SERIAL VARCHAR(200),
CatalogId INT(11),
title TEXT,
bahasa VARCHAR(100),
author VARCHAR(200),
publisher VARCHAR(100),
PublishLocation VARCHAR(100),
PublishYear VARCHAR(100),
SUBJECT VARCHAR(200),
CoverURL VARCHAR(100),
worksheet_id INT,
worksheet VARCHAR(100),
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
SET @query_as_string=CONCAT('
INSERT INTO tempCariArticle
SELECT art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id,
w.name worksheet,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM serial_articles art
JOIN catalogs CAT ON art.Catalog_id = CAT.ID
lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id
',querys3,'
WHERE
',keyword,' AND CAT.isopac=1 AND art.ISOPAC=1
AND w.ISSERIAL = 1',querys,querys2,' LIMIT ',limit1,',',limit2,' ');
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca TEXT;
SET querys='';
SET querys2='';
SET querys3='';
SET bhs='';
SET karya='';
SET pembaca='';
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
Article_type VARCHAR(100),
Creator_article VARCHAR(200),
title_article TEXT,
content_article TEXT,
subject_article VARCHAR(200),
EDISISERIAL VARCHAR(200),
TANGGAL_TERBIT_EDISI_SERIAL VARCHAR(200),
CatalogId INT(11),
title TEXT,
bahasa VARCHAR(100),
author VARCHAR(200),
publisher VARCHAR(100),
PublishLocation VARCHAR(100),
PublishYear VARCHAR(100),
SUBJECT VARCHAR(200),
CoverURL VARCHAR(100),
worksheet_id INT,
worksheet VARCHAR(100),
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
SET @query_as_string=CONCAT('\r\n INSERT INTO tempCariArticle\r\n SELECT art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id, \r\n w.name worksheet,\r\n (SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','') \r\n FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL\r\n FROM serial_articles art \r\n JOIN catalogs CAT ON art.Catalog_id = CAT.ID \r\n lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id\r\n \r\n ',querys3,' \r\n WHERE \r\n \r\n ',keyword,' AND CAT.isopac=1 AND art.ISOPAC=1\r\n AND w.ISSERIAL = 1',querys,querys2,' LIMIT ',limit1,',',limit2,' ');
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempLanjutOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempLanjutOpac |
PROCEDURE |
insertTempLanjutOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN worksheet TEXT,
IN bahasa TEXT,
IN targetPembaca TEXT,
in bentukKarya TEXT,
IN keyword TEXT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN isLKD TEXT,
IN lokasi TEXT
| |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca,queryloc,querylocjoin TEXT;
set querys='';
set querys2='';
set querys3='';
SET queryloc='';
SET querylocjoin='';
set bhs='';
set karya='';
set pembaca='';
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
Subject Text,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
IF lokasi <> 0 THEN SET queryloc = CONCAT(queryloc,' AND col.Location_id = ''',lokasi,'''');
END IF;
IF lokasi <> 0 THEN SET querylocjoin = CONCAT(querys,' LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID ');
END IF;
IF worksheet <> 'Semua Format FIle' and isLKD = 1 THEN SET querys2 = CONCAT(querys2,' HAVING KONTEN_DIGITAL = ''',worksheet,'''');
END IF;
IF isLKD = 1 THEN SET querys3 = CONCAT(querys3,' INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id ');
END IF;
SET @query_as_string=CONCAT('
INSERT INTO tempCariOpac
SELECT distinct CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id,
(SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,
(SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < now() || BookingExpiredDate is null)) JML_BUKU,
(SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM catalogs CAT
-- LEFT JOIN collections col ON col.Catalog_id = CAT.ID
',querys3,'
WHERE EXISTS
(SELECT 1
FROM catalog_ruas R ',querylocjoin,'
WHERE
',keyword,queryloc,bhs,karya,pembaca,' AND R.CATALOGID=CAT.ID) AND CAT.isopac=1',querys,querys2);
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca,queryloc,querylocjoin TEXT;
set querys='';
set querys2='';
set querys3='';
SET queryloc='';
SET querylocjoin='';
set bhs='';
set karya='';
set pembaca='';
DROP TABLE IF EXISTS tempCariOpac;
CREATE TEMPORARY TABLE tempCariOpac
(
CatalogId INT(11),
title TEXT,
author TEXT,
publisher TEXT,
PublishLocation TEXT,
PublishYear TEXT,
Subject Text,
bahasa TEXT,
CoverURL TEXT,
worksheet_id INT,
worksheet TEXT,
ISSERIAL TEXT,
JML_BUKU INT,
ALL_BUKU INT,
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
IF lokasi <> 0 THEN SET queryloc = CONCAT(queryloc,' AND col.Location_id = ''',lokasi,'''');
END IF;
IF lokasi <> 0 THEN SET querylocjoin = CONCAT(querys,' LEFT JOIN collections col ON col.Catalog_id = R.CATALOGID ');
END IF;
IF worksheet <> 'Semua Format FIle' and isLKD = 1 THEN SET querys2 = CONCAT(querys2,' HAVING KONTEN_DIGITAL = ''',worksheet,'''');
END IF;
IF isLKD = 1 THEN SET querys3 = CONCAT(querys3,' INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id ');
END IF;
SET @query_as_string=CONCAT('\r\n INSERT INTO tempCariOpac\r\n SELECT distinct CAT.id CatalogId,CAT.title,CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.Languages,CAT.CoverURL ,CAT.Worksheet_id, \r\n (SELECT NAME FROM worksheets WHERE id=CAT.Worksheet_id) worksheet,\r\n (SELECT ISSERIAL FROM worksheets WHERE id=CAT.Worksheet_id) ISSERIAL,\r\n (SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID AND STATUS_ID=1 AND (BookingExpiredDate < now() || BookingExpiredDate is null)) JML_BUKU,\r\n (SELECT COUNT(1) FROM collections WHERE CATALOG_ID=CAT.ID) ALL_BUKU,\r\n (SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','') \r\n FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL\r\n FROM catalogs CAT \r\n -- LEFT JOIN collections col ON col.Catalog_id = CAT.ID\r\n \r\n ',querys3,' \r\n WHERE EXISTS \r\n (SELECT 1\r\n FROM catalog_ruas R ',querylocjoin,' \r\n WHERE \r\n \r\n \r\n \r\n ',keyword,queryloc,bhs,karya,pembaca,' AND R.CATALOGID=CAT.ID) AND CAT.isopac=1',querys,querys2);
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'insertTempLanjutArticle\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
insertTempLanjutArticle |
PROCEDURE |
insertTempLanjutArticle |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN worksheet TEXT,
IN bahasa TEXT,
IN targetPembaca TEXT,
IN bentukKarya TEXT,
IN keyword TEXT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN isLKD TEXT
| |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca TEXT;
SET querys='';
SET querys2='';
SET querys3='';
SET bhs='';
SET karya='';
SET pembaca='';
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
Article_type VARCHAR(100),
Creator_article VARCHAR(200),
title_article TEXT,
content_article TEXT,
subject_article VARCHAR(200),
EDISISERIAL VARCHAR(200),
TANGGAL_TERBIT_EDISI_SERIAL VARCHAR(200),
CatalogId INT(11),
title TEXT,
bahasa VARCHAR(100),
author VARCHAR(200),
publisher VARCHAR(100),
PublishLocation VARCHAR(100),
PublishYear VARCHAR(100),
SUBJECT VARCHAR(200),
CoverURL VARCHAR(100),
worksheet_id INT,
worksheet VARCHAR(100),
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
SET @query_as_string=CONCAT('
INSERT INTO tempCariArticle
SELECT art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id,
w.name worksheet,
(SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','')
FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL
FROM serial_articles art
JOIN catalogs CAT ON art.Catalog_id = CAT.ID
lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id
',querys3,'
WHERE
',keyword,' AND CAT.isopac=1 AND art.ISOPAC=1
AND w.ISSERIAL = 1',querys,querys2,' ');
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE querys,querys2,querys3,bhs,karya,pembaca TEXT;
SET querys='';
SET querys2='';
SET querys3='';
SET bhs='';
SET karya='';
SET pembaca='';
DROP TABLE IF EXISTS tempCariArticle;
CREATE TEMPORARY TABLE tempCariArticle
(
Article_type VARCHAR(100),
Creator_article VARCHAR(200),
title_article TEXT,
content_article TEXT,
subject_article VARCHAR(200),
EDISISERIAL VARCHAR(200),
TANGGAL_TERBIT_EDISI_SERIAL VARCHAR(200),
CatalogId INT(11),
title TEXT,
bahasa VARCHAR(100),
author VARCHAR(200),
publisher VARCHAR(100),
PublishLocation VARCHAR(100),
PublishYear VARCHAR(100),
SUBJECT VARCHAR(200),
CoverURL VARCHAR(100),
worksheet_id INT,
worksheet VARCHAR(100),
KONTEN_DIGITAL VARCHAR(100)
);
IF bahasa <> '' THEN SET bhs = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,36,3) = ''',bahasa,'''');
END IF;
IF bentukKarya <> '' THEN SET karya = CONCAT(bhs,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,34,1) = ''',bentukKarya,'''');
END IF;
IF targetPembaca <> '' THEN SET pembaca = CONCAT(pembaca,' AND R.TAG IN (''008'') AND SUBSTRING(R.VALUE,23,1) = ''',targetPembaca,'''');
END IF;
IF fAuthor <> '' THEN SET querys = CONCAT(querys,' AND CAT.Author = ''',fAuthor,'''');
END IF;
IF fPublisher <> '' THEN SET querys = CONCAT(querys,' AND CAT.Publisher = ''',fPublisher,'''');
END IF;
IF fPublishLoc <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishLocation = ''',fPublishLoc,'''');
END IF;
IF fPublishYear <> '' THEN SET querys = CONCAT(querys,' AND CAT.PublishYear = ''',fPublishYear,'''');
END IF;
IF fSubject <> '' THEN SET querys = CONCAT(querys,' AND CAT.Subject = ''',fSubject,'''');
END IF;
IF fBahasa <> '' THEN SET querys = CONCAT(querys,' AND CAT.Languages = ''',fBahasa,'''');
END IF;
SET @query_as_string=CONCAT('\r\n INSERT INTO tempCariArticle\r\n SELECT art.Article_type,art.Creator AS Creator_article, art.title AS title_article, art.content AS content_article, art.subject AS subject_article, art.EDISISERIAL, art.TANGGAL_TERBIT_EDISI_SERIAL, CAT.id CatalogId,CAT.title,CAT.Languages, CAT.author,CAT.publisher,CAT.PublishLocation,CAT.PublishYear,CAT.subject,CAT.CoverURL ,CAT.Worksheet_id, \r\n w.name worksheet,\r\n (SELECT GROUP_CONCAT(DISTINCT SUBSTRING(FileURL,(LENGTH(FileURL)-LOCATE(''.'',REVERSE(FileURL)))+2) SEPARATOR '','') \r\n FROM catalogfiles WHERE Catalog_id = CAT.ID) KONTEN_DIGITAL\r\n FROM serial_articles art \r\n JOIN catalogs CAT ON art.Catalog_id = CAT.ID \r\n lEFT JOIN worksheets w ON w.id = CAT.Worksheet_id\r\n \r\n ',querys3,' \r\n WHERE \r\n \r\n ',keyword,' AND CAT.isopac=1 AND art.ISOPAC=1\r\n AND w.ISSERIAL = 1',querys,querys2,' ');
PREPARE statement_1
FROM @query_as_string ;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'facedAuthorOpac1\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
facedAuthorOpac1 |
PROCEDURE |
facedAuthorOpac1 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN maxFaced INT
| |
BEGIN
-- call explode_table(';',fAuthor,fPublisher,fPublishLoc,fPublishYear,fSubject);
-- SELECT COALESCE(Value,'-') Author ,COUNT(1) jml FROM table2 group by COALESCE(Value,'-') order by jml desc LIMIT 0,maxFaced;
SELECT Author,COUNT(1) jml
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear = fPublishYear) AND
IF(fSubject='',1=1,SUBJECT = fSubject) AND
IF(fBahasa='',1=1,bahasa = fBahasa)
GROUP BY Author
ORDER BY jml DESC
LIMIT 0,maxFaced;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
-- call explode_table(';',fAuthor,fPublisher,fPublishLoc,fPublishYear,fSubject);
-- SELECT COALESCE(Value,'-') Author ,COUNT(1) jml FROM table2 group by COALESCE(Value,'-') order by jml desc LIMIT 0,maxFaced;
SELECT Author,COUNT(1) jml
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,Author LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear = fPublishYear) AND
IF(fSubject='',1=1,SUBJECT = fSubject) AND
IF(fBahasa='',1=1,bahasa = fBahasa)
GROUP BY Author
ORDER BY jml DESC
LIMIT 0,maxFaced;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'facedArticle\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
facedArticle |
PROCEDURE |
facedArticle |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN facedType TEXT,
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fSubject TEXT,
IN fBahasa TEXT,
IN maxFaced INT
| |
BEGIN
IF facedType = 'Author'
THEN
SELECT COALESCE(Creator_article,'-') Creator_article,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY Creator_article
ORDER BY jml DESC
LIMIT 0,maxFaced;
ELSE
IF facedType = 'Bahasa'
THEN
SELECT Languages,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY Languages ORDER BY jml DESC
LIMIT 0,maxFaced;
ELSE
IF facedType = 'Subject'
THEN
SELECT COALESCE(subject_article,'-') subject_article,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY subject_article ORDER BY jml DESC
LIMIT 0,maxFaced;
ELSE
IF facedType = 'Publisher'
THEN
SELECT COALESCE(Publisher,'-') Publisher,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY Publisher
ORDER BY jml DESC
LIMIT 0,maxFaced;
END IF;
END IF;
END IF;
END IF;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
IF facedType = 'Author'
THEN
SELECT COALESCE(Creator_article,'-') Creator_article,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY Creator_article
ORDER BY jml DESC
LIMIT 0,maxFaced;
ELSE
IF facedType = 'Bahasa'
THEN
SELECT Languages,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY Languages ORDER BY jml DESC
LIMIT 0,maxFaced;
ELSE
IF facedType = 'Subject'
THEN
SELECT COALESCE(subject_article,'-') subject_article,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY subject_article ORDER BY jml DESC
LIMIT 0,maxFaced;
ELSE
IF facedType = 'Publisher'
THEN
SELECT COALESCE(Publisher,'-') Publisher,COUNT(1) jml
FROM tempCariArticle
WHERE
IF(fAuthor='',1=1,Creator_article LIKE CONCAT('%',CONCAT(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fSubject='',1=1,subject_article = fSubject) AND
IF(fBahasa='',1=1,Languages = fBahasa)
GROUP BY Publisher
ORDER BY jml DESC
LIMIT 0,maxFaced;
END IF;
END IF;
END IF;
END IF;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'countPencarianLanjutOpac1\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
countPencarianLanjutOpac1 |
PROCEDURE |
countPencarianLanjutOpac1 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT
| |
BEGIN
SELECT COUNT(1)
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author = fAuthor) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear = fPublishYear) AND
IF(fSubject='',1=1,Subject = fSubject) AND
IF(fSubject='',1=1,SUBJECT LIKE fSubject);
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT COUNT(1)
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author = fAuthor) AND
IF(fPublisher='',1=1,publisher = fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation = fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear = fPublishYear) AND
IF(fSubject='',1=1,Subject = fSubject) AND
IF(fSubject='',1=1,SUBJECT LIKE fSubject);
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'countPencarianSederhanaOpac1\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
countPencarianSederhanaOpac1 |
PROCEDURE |
countPencarianSederhanaOpac1 |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN fAuthor TEXT,
IN fPublisher TEXT,
IN fPublishLoc TEXT,
IN fPublishYear TEXT,
IN fSubject TEXT,
IN fBahasa TEXT
| |
BEGIN
SELECT COUNT(1)
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author LIKE concat('%',concat(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear LIKE fPublishYear) AND
IF(fBahasa='',1=1,bahasa LIKE fBahasa) AND
IF(fSubject='',1=1,Subject LIKE fSubject) ;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
SELECT COUNT(1)
FROM tempCariOpac
WHERE
IF(fAuthor='',1=1,author LIKE concat('%',concat(fAuthor,'%'))) AND
IF(fPublisher='',1=1,publisher LIKE fPublisher) AND
IF(fPublishLoc='',1=1,PublishLocation LIKE fPublishLoc) AND
IF(fPublishYear='',1=1,PublishYear LIKE fPublishYear) AND
IF(fBahasa='',1=1,bahasa LIKE fBahasa) AND
IF(fSubject='',1=1,Subject LIKE fSubject) ;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'BrowseOpac\' AND `proc`.`type` = \'PROCEDURE\' | inlislite_v3 |
BrowseOpac |
PROCEDURE |
BrowseOpac |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
IN keyword TEXT,
IN keyword2 TEXT,
IN keyword3 TEXT,
IN isLKD TEXT,
IN Location TEXT
| |
BEGIN
IF isLKD = 1
THEN
IF keyword = 'Alphabetical' AND keyword2 = '' THEN
SET @query_as_string=CONCAT("SELECT 'A' AS `A` UNION ALL SELECT 'B' AS `B` UNION ALL SELECT 'C' AS `C` UNION ALL SELECT 'D' AS `D` UNION ALL SELECT 'E' AS `E` UNION ALL SELECT 'F' AS `F` UNION ALL SELECT 'G' AS `G` UNION ALL SELECT 'H' AS `H` UNION ALL SELECT 'I' AS `I` UNION ALL SELECT 'J' AS `J` UNION ALL SELECT 'K' AS `K` UNION ALL SELECT 'L' AS `L` UNION ALL SELECT 'M' AS `M` UNION ALL SELECT 'N' AS `N` UNION ALL SELECT 'O' AS `O` UNION ALL SELECT 'P' AS `P` UNION ALL SELECT 'Q' AS `Q` UNION ALL SELECT 'R' AS `R` UNION ALL SELECT 'S' AS `S` UNION ALL SELECT 'T' AS `T` UNION ALL SELECT 'U' AS `U` UNION ALL SELECT 'V' AS `V` UNION ALL SELECT 'W' AS `W` UNION ALL SELECT 'X' AS `X` UNION ALL SELECT 'Y' AS `Y` UNION ALL SELECT 'Z' AS `Z`");
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword = 'Alphabetical' AND keyword2 <> '' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT
INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id
WHERE CAT.isopac=1
AND CAT.',keyword2,' like "',keyword3,'%"
GROUP BY name
ORDER BY jml desc
limit 0,20');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 = '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword,' as name,COUNT(CAT.',keyword,' ) as jml FROM catalogs CAT
INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id
WHERE CAT.isopac=1
GROUP BY name
ORDER BY jml desc
limit 0,20 ');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 <> '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT
INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id
WHERE CAT.isopac=1 AND
CAT.',keyword,' = "',keyword3,'"
GROUP BY name
ORDER BY jml desc
limit 0,20;');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
ELSE
IF Location <> 0
THEN
IF keyword = 'Alphabetical' AND keyword2 = '' THEN
SET @query_as_string=CONCAT("SELECT 'A' AS `A` UNION ALL SELECT 'B' AS `B` UNION ALL SELECT 'C' AS `C` UNION ALL SELECT 'D' AS `D` UNION ALL SELECT 'E' AS `E` UNION ALL SELECT 'F' AS `F` UNION ALL SELECT 'G' AS `G` UNION ALL SELECT 'H' AS `H` UNION ALL SELECT 'I' AS `I` UNION ALL SELECT 'J' AS `J` UNION ALL SELECT 'K' AS `K` UNION ALL SELECT 'L' AS `L` UNION ALL SELECT 'M' AS `M` UNION ALL SELECT 'N' AS `N` UNION ALL SELECT 'O' AS `O` UNION ALL SELECT 'P' AS `P` UNION ALL SELECT 'Q' AS `Q` UNION ALL SELECT 'R' AS `R` UNION ALL SELECT 'S' AS `S` UNION ALL SELECT 'T' AS `T` UNION ALL SELECT 'U' AS `U` UNION ALL SELECT 'V' AS `V` UNION ALL SELECT 'W' AS `W` UNION ALL SELECT 'X' AS `X` UNION ALL SELECT 'Y' AS `Y` UNION ALL SELECT 'Z' AS `Z`");
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword = 'Alphabetical' AND keyword2 <> '' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT
LEFT JOIN collections col ON col.Catalog_id = CAT.ID
WHERE CAT.isopac=1
AND col.Location_id = ',Location,'
AND CAT.',keyword2,' like "',keyword3,'%"
GROUP BY name
ORDER BY jml desc
limit 0,20');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 = '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword,' as name,COUNT(CAT.',keyword,' ) as jml FROM catalogs CAT
LEFT JOIN collections col ON col.Catalog_id = CAT.ID
WHERE CAT.isopac=1
AND col.Location_id = ',Location,'
GROUP BY name
ORDER BY jml desc
limit 0,20 ');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 <> '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT
LEFT JOIN collections col ON col.Catalog_id = CAT.ID
WHERE CAT.isopac=1
AND col.Location_id = ',Location,'
AND
CAT.',keyword,' = "',keyword3,'"
GROUP BY name
ORDER BY jml desc
limit 0,20;');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
ELSE
IF keyword = 'Alphabetical' AND keyword2 = '' THEN
SET @query_as_string=CONCAT("SELECT 'A' AS `A` UNION ALL SELECT 'B' AS `B` UNION ALL SELECT 'C' AS `C` UNION ALL SELECT 'D' AS `D` UNION ALL SELECT 'E' AS `E` UNION ALL SELECT 'F' AS `F` UNION ALL SELECT 'G' AS `G` UNION ALL SELECT 'H' AS `H` UNION ALL SELECT 'I' AS `I` UNION ALL SELECT 'J' AS `J` UNION ALL SELECT 'K' AS `K` UNION ALL SELECT 'L' AS `L` UNION ALL SELECT 'M' AS `M` UNION ALL SELECT 'N' AS `N` UNION ALL SELECT 'O' AS `O` UNION ALL SELECT 'P' AS `P` UNION ALL SELECT 'Q' AS `Q` UNION ALL SELECT 'R' AS `R` UNION ALL SELECT 'S' AS `S` UNION ALL SELECT 'T' AS `T` UNION ALL SELECT 'U' AS `U` UNION ALL SELECT 'V' AS `V` UNION ALL SELECT 'W' AS `W` UNION ALL SELECT 'X' AS `X` UNION ALL SELECT 'Y' AS `Y` UNION ALL SELECT 'Z' AS `Z`");
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword = 'Alphabetical' AND keyword2 <> '' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT
WHERE CAT.isopac=1
AND CAT.',keyword2,' like "',keyword3,'%"
GROUP BY name
ORDER BY jml desc
limit 0,20');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 = '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword,' as name,COUNT(CAT.',keyword,' ) as jml FROM catalogs CAT
WHERE CAT.isopac=1
GROUP BY name
ORDER BY jml desc
limit 0,20 ');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 <> '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('
SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT
WHERE CAT.isopac=1 AND
CAT.',keyword,' = "',keyword3,'"
GROUP BY name
ORDER BY jml desc
limit 0,20;');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
END IF;
END IF;
END
| root@localhost |
2021-02-10 09:49:45 |
2021-02-10 09:49:45 |
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
IF isLKD = 1
THEN
IF keyword = 'Alphabetical' AND keyword2 = '' THEN
SET @query_as_string=CONCAT("SELECT 'A' AS `A` UNION ALL SELECT 'B' AS `B` UNION ALL SELECT 'C' AS `C` UNION ALL SELECT 'D' AS `D` UNION ALL SELECT 'E' AS `E` UNION ALL SELECT 'F' AS `F` UNION ALL SELECT 'G' AS `G` UNION ALL SELECT 'H' AS `H` UNION ALL SELECT 'I' AS `I` UNION ALL SELECT 'J' AS `J` UNION ALL SELECT 'K' AS `K` UNION ALL SELECT 'L' AS `L` UNION ALL SELECT 'M' AS `M` UNION ALL SELECT 'N' AS `N` UNION ALL SELECT 'O' AS `O` UNION ALL SELECT 'P' AS `P` UNION ALL SELECT 'Q' AS `Q` UNION ALL SELECT 'R' AS `R` UNION ALL SELECT 'S' AS `S` UNION ALL SELECT 'T' AS `T` UNION ALL SELECT 'U' AS `U` UNION ALL SELECT 'V' AS `V` UNION ALL SELECT 'W' AS `W` UNION ALL SELECT 'X' AS `X` UNION ALL SELECT 'Y' AS `Y` UNION ALL SELECT 'Z' AS `Z`");
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword = 'Alphabetical' AND keyword2 <> '' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT\r\n INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id\r\n WHERE CAT.isopac=1\r\n AND CAT.',keyword2,' like "',keyword3,'%" \r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 = '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword,' as name,COUNT(CAT.',keyword,' ) as jml FROM catalogs CAT\r\n INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id\r\n WHERE CAT.isopac=1\r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20 ');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 <> '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT\r\n INNER JOIN catalogfiles CF on CAT.ID = CF.Catalog_id\r\n WHERE CAT.isopac=1 AND\r\n CAT.',keyword,' = "',keyword3,'"\r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20;');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
ELSE
IF Location <> 0
THEN
IF keyword = 'Alphabetical' AND keyword2 = '' THEN
SET @query_as_string=CONCAT("SELECT 'A' AS `A` UNION ALL SELECT 'B' AS `B` UNION ALL SELECT 'C' AS `C` UNION ALL SELECT 'D' AS `D` UNION ALL SELECT 'E' AS `E` UNION ALL SELECT 'F' AS `F` UNION ALL SELECT 'G' AS `G` UNION ALL SELECT 'H' AS `H` UNION ALL SELECT 'I' AS `I` UNION ALL SELECT 'J' AS `J` UNION ALL SELECT 'K' AS `K` UNION ALL SELECT 'L' AS `L` UNION ALL SELECT 'M' AS `M` UNION ALL SELECT 'N' AS `N` UNION ALL SELECT 'O' AS `O` UNION ALL SELECT 'P' AS `P` UNION ALL SELECT 'Q' AS `Q` UNION ALL SELECT 'R' AS `R` UNION ALL SELECT 'S' AS `S` UNION ALL SELECT 'T' AS `T` UNION ALL SELECT 'U' AS `U` UNION ALL SELECT 'V' AS `V` UNION ALL SELECT 'W' AS `W` UNION ALL SELECT 'X' AS `X` UNION ALL SELECT 'Y' AS `Y` UNION ALL SELECT 'Z' AS `Z`");
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword = 'Alphabetical' AND keyword2 <> '' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT\r\n LEFT JOIN collections col ON col.Catalog_id = CAT.ID\r\n WHERE CAT.isopac=1 \r\n AND col.Location_id = ',Location,'\r\n AND CAT.',keyword2,' like "',keyword3,'%" \r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 = '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword,' as name,COUNT(CAT.',keyword,' ) as jml FROM catalogs CAT\r\n LEFT JOIN collections col ON col.Catalog_id = CAT.ID\r\n WHERE CAT.isopac=1\r\n AND col.Location_id = ',Location,'\r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20 ');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 <> '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT\r\n LEFT JOIN collections col ON col.Catalog_id = CAT.ID\r\n WHERE CAT.isopac=1\r\n AND col.Location_id = ',Location,' \r\n AND\r\n CAT.',keyword,' = "',keyword3,'"\r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20;');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
ELSE
IF keyword = 'Alphabetical' AND keyword2 = '' THEN
SET @query_as_string=CONCAT("SELECT 'A' AS `A` UNION ALL SELECT 'B' AS `B` UNION ALL SELECT 'C' AS `C` UNION ALL SELECT 'D' AS `D` UNION ALL SELECT 'E' AS `E` UNION ALL SELECT 'F' AS `F` UNION ALL SELECT 'G' AS `G` UNION ALL SELECT 'H' AS `H` UNION ALL SELECT 'I' AS `I` UNION ALL SELECT 'J' AS `J` UNION ALL SELECT 'K' AS `K` UNION ALL SELECT 'L' AS `L` UNION ALL SELECT 'M' AS `M` UNION ALL SELECT 'N' AS `N` UNION ALL SELECT 'O' AS `O` UNION ALL SELECT 'P' AS `P` UNION ALL SELECT 'Q' AS `Q` UNION ALL SELECT 'R' AS `R` UNION ALL SELECT 'S' AS `S` UNION ALL SELECT 'T' AS `T` UNION ALL SELECT 'U' AS `U` UNION ALL SELECT 'V' AS `V` UNION ALL SELECT 'W' AS `W` UNION ALL SELECT 'X' AS `X` UNION ALL SELECT 'Y' AS `Y` UNION ALL SELECT 'Z' AS `Z`");
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword = 'Alphabetical' AND keyword2 <> '' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT\r\n \r\n WHERE CAT.isopac=1\r\n AND CAT.',keyword2,' like "',keyword3,'%" \r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 = '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword,' as name,COUNT(CAT.',keyword,' ) as jml FROM catalogs CAT\r\n WHERE CAT.isopac=1\r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20 ');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
IF keyword2 <> '' AND keyword <> 'Alphabetical' THEN
SET @query_as_string=CONCAT('\r\n SELECT distinct CAT.',keyword2,' as name,COUNT(CAT.',keyword2,') as jml FROM catalogs CAT\r\n WHERE CAT.isopac=1 AND\r\n CAT.',keyword,' = "',keyword3,'"\r\n GROUP BY name\r\n ORDER BY jml desc\r\n limit 0,20;');
PREPARE statement_1 FROM @query_as_string;
EXECUTE statement_1;
DEALLOCATE PREPARE statement_1;
END IF;
END IF;
END IF;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'GetWarningLoanDueDay\' AND `proc`.`type` = \'FUNCTION\' | inlislite_v3 |
GetWarningLoanDueDay |
FUNCTION |
GetWarningLoanDueDay |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
JenisAnggotaID double, WorksheetID int(11)
|
int(11)
|
BEGIN
declare WarningLoanDueDay int(11);
set WarningLoanDueDay = 0;
set WarningLoanDueDay = (SELECT WarningLoanDueDay FROM jenis_anggota WHERE jenis_anggota.id = JenisAnggotaID);
if WarningLoanDueDay = 0 then set WarningLoanDueDay = (SELECT WarningLoanDueDay FROM worksheets WHERE worksheets.id = WorksheetID); end if;
return WarningLoanDueDay;
END
| root@localhost |
2016-06-06 14:58:36 |
2016-06-06 14:58:36 |
NO_AUTO_VALUE_ON_ZERO |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
declare WarningLoanDueDay int(11);
set WarningLoanDueDay = 0;
set WarningLoanDueDay = (SELECT WarningLoanDueDay FROM jenis_anggota WHERE jenis_anggota.id = JenisAnggotaID);
if WarningLoanDueDay = 0 then set WarningLoanDueDay = (SELECT WarningLoanDueDay FROM worksheets WHERE worksheets.id = WorksheetID); end if;
return WarningLoanDueDay;
END
|
| |
Edit
|
Copy
|
Delete
DELETE FROM `proc` WHERE `proc`.`db` = \'inlislite_v3\' AND `proc`.`name` = \'get_next_no_pemijaman_id\' AND `proc`.`type` = \'FUNCTION\' | inlislite_v3 |
get_next_no_pemijaman_id |
FUNCTION |
get_next_no_pemijaman_id |
SQL |
CONTAINS_SQL |
NO |
DEFINER |
|
varchar(20) CHARSET latin1
|
BEGIN
DECLARE next_no_pemijaman VARCHAR(20);
DECLARE last_no_pemijaman BIGINT(20) DEFAULT 1;
DECLARE pemijaman_count BIGINT(20);
SELECT COUNT(No_pinjaman) INTO pemijaman_count FROM lockers WHERE LEFT(No_pinjaman, 6) = DATE_FORMAT(NOW(),'%Y%m') ;
IF pemijaman_count > 0 THEN
SELECT RIGHT(No_pinjaman, 7) INTO last_no_pemijaman FROM lockers WHERE LEFT(No_pinjaman, 6) = DATE_FORMAT(NOW(),'%Y%m') ORDER BY no_pinjaman DESC LIMIT 0, 1;
SET last_no_pemijaman = last_no_pemijaman+ 1;
ELSE SET last_no_pemijaman =1;
END IF;
SET next_no_pemijaman = CONCAT(DATE_FORMAT(NOW(),'%Y%m'), REPEAT(0, 7 - LENGTH(last_no_pemijaman)), last_no_pemijaman);
RETURN CONCAT(DATE_FORMAT(NOW(),'%Y%m'),REPEAT(0, 7 - LENGTH(last_no_pemijaman)),last_no_pemijaman);
END
| root@localhost |
2016-06-06 14:58:37 |
2016-06-06 14:58:37 |
NO_AUTO_VALUE_ON_ZERO |
| utf8 |
utf8_general_ci |
latin1_swedish_ci |
BEGIN
DECLARE next_no_pemijaman VARCHAR(20);
DECLARE last_no_pemijaman BIGINT(20) DEFAULT 1;
DECLARE pemijaman_count BIGINT(20);
SELECT COUNT(No_pinjaman) INTO pemijaman_count FROM lockers WHERE LEFT(No_pinjaman, 6) = DATE_FORMAT(NOW(),'%Y%m') ;
IF pemijaman_count > 0 THEN
SELECT RIGHT(No_pinjaman, 7) INTO last_no_pemijaman FROM lockers WHERE LEFT(No_pinjaman, 6) = DATE_FORMAT(NOW(),'%Y%m') ORDER BY no_pinjaman DESC LIMIT 0, 1;
SET last_no_pemijaman = last_no_pemijaman+ 1;
ELSE SET last_no_pemijaman =1;
END IF;
SET next_no_pemijaman = CONCAT(DATE_FORMAT(NOW(),'%Y%m'), REPEAT(0, 7 - LENGTH(last_no_pemijaman)), last_no_pemijaman);
RETURN CONCAT(DATE_FORMAT(NOW(),'%Y%m'),REPEAT(0, 7 - LENGTH(last_no_pemijaman)),last_no_pemijaman);
END
|
