SISTEM MAKLUMAT PENGURUSAN KES

Fungsi

1) Fungsi getmilikan

Fungsi ini digunakan untuk mendapat maklumat pemilik vesel, apabila menghantar no vesel kepada fungsi ini.

DROP PROCEDURE IF EXISTS `getmilikan`;

CREATE DEFINER = `root`@`%` PROCEDURE `getmilikan`(v varchar(40))

begin

SELECT

i.in_id AS in_id,

i.in_name AS in_name,

i.in_no AS in_no,

i.in_no_old AS in_no_old,

i.in_pass AS in_pass,

r.race_desc AS race_desc,

g.gender_desc_may AS gender_desc_may,

kc.kc_name AS kc_name,

d.district_name AS district_name,

s.sta_name AS sta_name,

c.c_name AS c_name,

a.aktif_name AS aktif_name,

i.sta_id AS sta_id,

i.district_id AS district_id,

im.milik_id

FROM

(((((((((tbl_individu AS i

LEFT JOIN tbl_individu_milik AS im ON ((i.in_id = im.in_id))))

LEFT JOIN tbl_race AS r ON ((i.race_id = r.race_id)))

LEFT JOIN sys_gender AS g ON ((i.gender_id = g.gender_id)))

LEFT JOIN sys_state AS s ON ((i.sta_id = s.sta_id)))

LEFT JOIN sys_district AS d ON ((i.district_id = d.district_id)))

LEFT JOIN tbl_kru_cat AS kc ON ((i.kc_id = kc.kc_id)))

LEFT JOIN sys_country AS c ON ((i.c_id = c.c_id)))

LEFT JOIN tbl_aktif AS a ON ((i.in_status = a.aktif_id)))

WHERE milik_id=getmilikanid(v)

group by `i`.`in_id` ;

end ;

2) Fungsi getmilikanid

Fungsi ini digunakan untuk mendapat no ic/kad pengenalan apabila dihantar kepada fungsi ini.

DROP FUNCTION IF EXISTS `getmilikanid`;

CREATE DEFINER = `root`@`%` FUNCTION `getmilikanid`(v VARCHAR(40))

 RETURNS varchar(40)

    DETERMINISTIC

BEGIN

declare mlaid VARCHAR(40);

set mlaid=(SELECT

tbl_milik.milik_id

FROM

tbl_milik

INNER JOIN tbl_milik_vesel ON tbl_milik_vesel.milik_id = tbl_milik.milik_id

INNER JOIN tbl_vesel ON tbl_milik_vesel.v_id = tbl_vesel.v_id WHERE tbl_vesel.v_no=v);

return mlaid;

end ;

3) Fungsi insertcardinfo

Fungsi ini digunakan untuk mendapat info

BEGIN

DECLARE cardno VARCHAR(10);

DECLARE runningno INT;

DECLARE norecord INT;

SELECT ISNULL(MAX(runningnumberbystate)) INTO norecord FROM nelayancard WHERE state = stateid;

IF norecord = 1 THEN

SET cardno = CONCAT(stateid, 'T', LPAD('1', 6,'0'));

INSERT INTO nelayancard (idnelayan, state, runningnumberbystate, reprintcount, cardnumber) VALUES

(nelayanid, stateid, 1, 0, cardno);

ELSE

INSERT INTO nelayancard (runningnumberbystate) SELECT MAX(runningnumberbystate) + 1 FROM nelayancard WHERE state = stateid;

SELECT runningnumberbystate INTO runningno FROM nelayancard WHERE id = LAST_INSERT_ID();

SET cardno = CONCAT(stateid, 'T', LPAD(runningno, 6,'0'));

UPDATE nelayancard SET idnelayan = nelayanid, state = stateid, reprintcount = 0, cardnumber = cardno

WHERE id = LAST_INSERT_ID();

END IF;

RETURN cardno;

Pangkalan Data

Antara jadual views yang telah ditambah ke dalam ekes untuk memudahkan proses pengiraan dan paparan ialah:

• getkrubyvid

• getlesenowner

• getveseldetails

• getveselowner

• nelayandetails

• pemeriksaanview

• tbl_nelayanview

• tbl_veselview

• tbl_veselviewlesen

• tbl_veselviewlesen3

• tbl_viewlesen

• tbl_viewlesen2

• tbl_viewzon

Hak Cipta Terpelihara Jabatan Perikanan Malaysia 2016