DELETE FROM customer_card;
DELETE FROM telephone;
DELETE FROM address;
DELETE FROM customer;

DROP FUNCTION old_order(integer);
DROP FUNCTION customer_type_add(char, char);
DROP FUNCTION customer_type_update(integer, char, char);
DROP FUNCTION customer_type_delete(integer, char);
DROP FUNCTION telephone_add(char,  char, char);
DROP FUNCTION telephone_update(char, char, char); 
DROP FUNCTION telephone_delete(integer, char);
DROP FUNCTION order_status_add(char, char);
DROP FUNCTION order_status_update(integer,  char, char); 
DROP FUNCTION order_status_delete(integer, char);
DROP FUNCTION renewal_period_add(char, char, timestamp);
DROP FUNCTION renewal_period_update(integer,  char, char, timestamp); 
DROP FUNCTION renewal_period_delete(integer, char);
DROP FUNCTION address_add(char, char, char, char, char, char, char, char);
DROP FUNCTION address_update(char, char, char, char, char, char, char, char); 
DROP FUNCTION address_delete(integer, char);
DROP FUNCTION card_type_add(char, char);
DROP FUNCTION card_type_update(integer, char, char); 
DROP FUNCTION card_type_delete(integer, char);
DROP FUNCTION card_name_add(char, char);
DROP FUNCTION card_name_update(integer,  char, char); 
DROP FUNCTION card_name_delete(integer, char);
DROP FUNCTION payment_type_add(char, char);
DROP FUNCTION payment_type_update(integer,  char, char); 
DROP FUNCTION payment_type_delete(integer, char);
DROP FUNCTION customer_status_add(char, char);
DROP FUNCTION customer_status_update(integer,  char, char); 
DROP FUNCTION customer_status_delete(integer, char);
DROP FUNCTION contactinfo_add(char, char);
DROP FUNCTION contactinfo_update(integer,  char, char); 
DROP FUNCTION contactinfo_delete(integer, char);
DROP FUNCTION list_domains(char);
DROP FUNCTION last_action(char);
DROP FUNCTION user_logon(char, char);
DROP FUNCTION user_logoff(char);
DROP FUNCTION customer_data_add(char, char, char, char, char, char, char, char, char, decimal);
DROP FUNCTION customer_data_add(char, char, char, char, char, char, char, char, char, char);
DROP FUNCTION customer_data_update(char, char, char, char, char, char, char, char, char, decimal);
DROP FUNCTION customer_data_update(integer, char, char, char, char, char, char, char, char, decimal);
DROP FUNCTION customer_data_delete(char, char);
DROP FUNCTION customer_card_add(char, char, char, char, timestamp, char);
DROP FUNCTION customer_card_update(integer, char, char, char, char, timestamp, char);
DROP FUNCTION customer_card_delete(integer, char);
DROP FUNCTION check_auth_level(text);

CREATE FUNCTION check_auth_level(text) RETURNS integer AS 'DECLARE
	a_auth_level	ALIAS FOR $1;
	allevel		char(20);
	curr_user	char(20);
	allowed		integer;
BEGIN

SELECT INTO curr_user CURRENT_USER;
SELECT INTO allowed COUNT(staff_nick) FROM staff WHERE staff_nick=curr_user;
IF NOT FOUND OR allowed=0 THEN
     RAISE NOTICE ''Your username does not exists in database.'';
     RETURN -1;
END IF;

allevel:=UPPER(a_auth_level);

IF allevel = ''LOGON'' THEN
   RETURN 1;
END IF;

SELECT INTO allowed COUNT(auth_short) FROM auth_level WHERE auth_short=allevel;
IF NOT FOUND or allowed=0 THEN
     RAISE NOTICE ''Level you are checking does not exist in database!'';
     RETURN -1;
END IF;

allowed=0;

IF allevel = ''STAFF_READ'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.staff_read=TRUE;
END IF;
IF allevel=''STAFF_WRITE'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.staff_write=TRUE;
END IF;
IF allevel = ''CUSTOMER_READ'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.customer_read=TRUE;
END IF;
IF allevel=''CUSTOMER_WRITE'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.customer_write=TRUE;
END IF;
IF allevel = ''INVOICE_READ'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.invoice_read=TRUE;
END IF;
IF allevel=''INVOICE_WRITE'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.invoice_write=TRUE;
END IF;
IF allevel = ''PRODUCT_READ'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.products_read=TRUE;
END IF;
IF allevel=''PRODUCT_WRITE'' THEN
   SELECT INTO allowed COUNT(*)
               FROM auth_level, staff
               WHERE staff.staff_nick=curr_user
                 AND staff.auth_level=auth_level.level_no
                 AND auth_level.products_write=TRUE;
END IF;

RETURN allowed;
END;
' LANGUAGE 'plpgsql';


--------------------------------------------------------------------------------
DELETE FROM customer_status;
DELETE FROM customer_type;
DELETE FROM card_type;
--------------------------------------------------------------------------------

CREATE FUNCTION old_order(integer) RETURNS text AS 'DECLARE
	ordr_no	ALIAS FOR $1;
	order_row	ROW;
	order_line	text;
BEGIN
IF NOT FOUND THEN
	RETURN NULL;
ELSE
	FOR order_row IN SELECT * FROM orders WHERE order_no=ordr_no ORDER BY,
 	    		        order_date DESC LIMIT 1 LOOP;
     			 	order_line = order_line || '','' || order_row; 
	END LOOP;
	RETURN order_line;
END IF;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_type_add(char, char) RETURNS integer AS 'DECLARE
	w_short_type	ALIAS FOR $1;
	w_long_type	ALIAS FOR $2;
	num_no		integer;
BEGIN 
LOCK TABLE customer_type IN SHARE MODE;
-- RAISE NOTICE ''CHEcking for: "%"'', w_short_type;
SELECT INTO num_no COUNT(*) FROM customer_type WHERE short_type=w_short_type;
IF num_no=0 THEN
     INSERT INTO customer_type VALUES (NEXTVAL(''ctype_no''), NOW(), w_short_type, w_long_type, FALSE);
ELSE 
--     RAISE NOTICE ''Data found in customer_type table: %'', num_no;
     RAISE EXCEPTION ''This "customer_type" already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

SELECT customer_type_add('Customer 001','Customer ... 001');
SELECT customer_type_add('Customer 002','Customer ... 002');

-------------------------------------------------------------------------------
CREATE FUNCTION customer_type_update(integer, char, char) RETURNS integer AS 'DECLARE
	w_type_no	ALIAS FOR $1;
	w_short_type	ALIAS FOR $2;
	w_long_type	ALIAS FOR $3;
BEGIN 
LOCK TABLE customer_type IN SHARE MODE;
INSERT INTO customer_type VALUES (w_type_no, NOW(), w_short_type, w_long_type);
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_type_delete(integer, char) RETURNS integer AS 'DECLARE
	w_type_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_count	integer;
BEGIN

should_delete=UPPER(shld_delete);

LOCK TABLE customer_type IN SHARE MODE;
LOCK TABLE customer	 IN SHARE MODE;

IF should_delete=NULL OR should_delete='''' THEN
     should_delete=''CHECK'';
END IF;

IF should_delete=''CHECK'' OR should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
     IF should_delete=''CHECK'' THEN
          SELECT INTO num_count COUNT(customer_no) FROM customer WHERE type_no=w_type_no;
          IF num_count=0 THEN
             real_delete=TRUE;
          END IF;
     END IF;
ELSE
     RAISE EXCEPTION ''Wrong parameter passed to DELETE_CUSTOMER_TYPE function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM customer_type WHERE type_no=w_type_no;     
ELSE
     UPDATE customer_type SET deleted=TRUE WHERE type_no=w_type_no; 
     UPDATE customer SET deleted=TRUE WHERE type_no=w_type_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_status_add(char, char) RETURNS integer AS 'DECLARE
	w_short_status	ALIAS FOR $1;
	w_long_status	ALIAS FOR $2;
	num_no		integer;
BEGIN 
LOCK TABLE customer_status IN SHARE MODE;
-- RAISE NOTICE ''CHEcking for: "%"'', w_short_status;
SELECT INTO num_no COUNT(*) FROM customer_status WHERE short_status=w_short_status;
IF num_no=0 THEN
     INSERT INTO customer_status VALUES (NEXTVAL(''cs_no''), NOW(), w_short_status, w_long_status, FALSE);
ELSE 
--     RAISE NOTICE ''Data found in customer_status table: %'', num_no;
     RAISE EXCEPTION ''This "customer_status" already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

SELECT customer_status_add('Customer status 001','Customer ... status ... 001');
SELECT customer_status_add('Customer status 002','Customer ... status ... 002');

-------------------------------------------------------------------------------
CREATE FUNCTION customer_status_update(integer, char, char) RETURNS integer AS 'DECLARE
	w_status_no	ALIAS FOR $1;
	w_short_status	ALIAS FOR $2;
	w_long_status	ALIAS FOR $3;
BEGIN 
LOCK TABLE customer_status IN SHARE MODE;
INSERT INTO customer_status VALUES (w_status_no, NOW(), w_short_status, w_long_status);
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_status_delete(integer, char) RETURNS integer AS 'DECLARE
	w_status_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_count	integer;
BEGIN

should_delete=UPPER(shld_delete);

LOCK TABLE customer_status IN SHARE MODE;
LOCK TABLE customer	 IN SHARE MODE;

IF should_delete=NULL OR should_delete='''' THEN
     should_delete=''CHECK'';
END IF;

IF should_delete=''CHECK'' OR should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
     IF should_delete=''CHECK'' THEN
          SELECT INTO num_count COUNT(customer_no) FROM customer WHERE status_no=w_status_no;
          IF num_count=0 THEN
             real_delete=TRUE;
          END IF;
     END IF;
ELSE
     RAISE EXCEPTION ''Wrong parameter passed to DELETE_CUSTOMER_STATUS function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM customer_status WHERE status_no=w_status_no;     
ELSE
     UPDATE customer_status SET deleted=TRUE WHERE status_no=w_status_no; 
     UPDATE customer SET deleted=TRUE WHERE status_no=w_status_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION telephone_add(char, char, char) RETURNS integer AS 'DECLARE
	w_email		ALIAS FOR $1;
	telephone_no	ALIAS FOR $2;
	fax_no		ALIAS FOR $3;
	num_no		integer;
	cus_no		integer;
BEGIN 
LOCK TABLE telephone IN SHARE MODE;
LOCK TABLE customer IN SHARE MODE;
SELECT INTO cus_no customer_no FROM customer WHERE email=w_email;
SELECT INTO num_no COUNT(*) FROM telephone WHERE (telephone=telephone_no OR fax=fax_no) AND customer_no=cus_no;
IF num_no=0 THEN
   INSERT INTO telephone VALUES (NEXTVAL(''telephone_no''), NOW(),  cus_no, telephone_no, fax_no);
ELSE
     RAISE EXCEPTION ''This telephone number already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION telephone_update(char, char, char) RETURNS integer AS 'DECLARE
	w_email		ALIAS FOR $1;
	w_telephone	ALIAS FOR $2;
	w_fax		ALIAS FOR $3;
	n_telephone_no	integer;
	n_customer_no	integer;
BEGIN 
LOCK TABLE telephone IN SHARE MODE;
SELECT INTO n_telephone_no, n_customer_no telephone.telephone_no, 
            customer.customer_no
       FROM telephone, customer
      WHERE customer.email=w_email
        AND customer.customer_no=telephone.customer_no;
IF n_telephone_no>0 THEN
   UPDATE telephone SET telephone_date=NOW(), telephone=w_telephone,
          fax=w_fax WHERE telephone_no=n_telephone_no AND
          customer_no=n_customer_no;
ELSE
   RAISE EXCEPTION ''Telephone not found in database!'';
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION telephone_delete(integer, char) RETURNS integer AS 'DECLARE
	w_status_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_count	integer;
BEGIN
should_delete=UPPER(shld_delete);
LOCK TABLE telephone	IN SHARE MODE;
LOCK TABLE customer	IN SHARE MODE;

IF should_delete=NULL OR should_delete='''' THEN
     should_delete=''CHECK'';
END IF;

IF should_delete=''CHECK'' OR should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
     IF should_delete=''CHECK'' THEN
          SELECT INTO num_count COUNT(customer.customer_no) 
                 FROM customer, telephone 
                WHERE customer.customer_no=telephone.customer_no 
                  AND customer.customer_no=w_status_no;
          IF num_count=0 THEN
             real_delete=TRUE;
          END IF;
     END IF;
ELSE
     RAISE EXCEPTION ''Wrong parameter passed to DELETE_TELEPHONE function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM telephone WHERE telephone_no=w_status_no;     
ELSE
     UPDATE telephone SET deleted=TRUE WHERE telephone_no=w_status_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION order_status_add(char, char) RETURNS integer AS 'DECLARE
	status_txt	ALIAS FOR $1;
	status_note	ALIAS FOR $2;
BEGIN 
LOCK TABLE order_status IN SHARE MODE;
SELECT COUNT(*) FROM order_status WHERE status=status_txt;
IF NOT FOUND THEN
     INSERT INTO order_status VALUES (NEXTVAL(''order_status_no''), NOW(),  status_txt, status_note);
      
ELSE
     RAISE NOTICE ''This order status address already exists in database!''
     RETURN 1
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION order_status_update(integer, char, char) RETURNS integer AS 'DECLARE
	status_no	ALIAS FOR $1;
	status_txt	ALIAS FOR $2;
	status_note	ALIAS FOR $3;
BEGIN 
LOCK TABLE order_status IN SHARE MODE;
INSERT INTO order_status VALUES (status_no, NOW(), status_txt, status_note);
 
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION order_status_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
real_delete	boolean DEFAULT FALSE;
BEGIN db_delete;

LOCK TABLE order_status  IN SHARE MODE;
LOCK TABLE customer     IN SHARE MODE;

IF IS NULL should_delete THEN
     should_delete=''CHECK''
END IF;

IF should_delete=''CHECK'' OR should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
     IF should_delete=''CHECK'' THEN
          SELECT COUNT(order_status_no) FROM orders WHERE status_no=delete_no;
          IF NOT FOUND THEN
             real_delete=TRUE;
          END IF;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to DELETE_ORDER_STATUS function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM order_status WHERE status_no=delete_no;
     
ELSE
     UPDATE order_status SET deleted=TRUE WHERE status_no=delete_no;
     UPDATE customer     SET deleted=TRUE WHERE status_no=delete_no;
     
END IF;


RETURN 0;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION renewal_period_add(char, char, timestamp) RETURNS integer AS 'DECLARE
	renewal_stxt	ALIAS FOR $1;
	renewal_txt	ALIAS FOR $2;
	renewal_date	ALIAS FOR $3;
BEGIN 
LOCK TABLE renewal_period IN SHARE MODE;
SELECT COUNT(*) FROM renewal_period WHERE renewal_text_short=renewal_stxt;
IF NOT FOUND THEN
     INSERT INTO renewal_period VALUES (NEXTVAL(''renewal_no''), NOW(),  renewal_stxt, renewal_txt, renewal_date, FALSE);
      
ELSE
     RAISE NOTICE ''This order status address already exists in database!''
     RETURN 1
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION renewal_period_update(integer, char, char, timestamp) RETURNS integer AS 'DECLARE
	renewal_no	ALIAS FOR $1;
	renewal_stxt	ALIAS FOR $2;
	renewal_txt	ALIAS FOR $3;
BEGIN 
LOCK TABLE renewal_period IN SHARE MODE;
INSERT INTO renewal_period VALUES (renewal_no, NOW(), renewal_stxt, renewal_txt, renewal_date, FALSE);
 
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION renewal_period_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
real_delete	boolean DEFAULT FALSE;
BEGIN db_delete;

LOCK TABLE renewal_period  IN SHARE MODE;

IF IS NULL should_delete THEN
     should_delete=''MARK''
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     real_delete = FALSE;
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to DELETE_RENEWAL_PERIOD function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM renewal_period WHERE renewal_no=delete_no;
     
ELSE
     UPDATE renewal_period SET deleted=TRUE WHERE renewal_no=delete_no;
     
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION address_add(char, char, char, char, char, char, char, char) RETURNS integer AS 'DECLARE
	w_email		ALIAS FOR $1;
	street_1	ALIAS FOR $2;
	street_2	ALIAS FOR $3;
	street_3	ALIAS FOR $4;
	city		ALIAS FOR $5;
	postcode	ALIAS FOR $6;
	county		ALIAS FOR $7;
	country		ALIAS FOR $8;
	w_customer_no	integer;
BEGIN
LOCK TABLE address IN SHARE MODE;
SELECT INTO w_customer_no customer_no FROM customer WHERE email=w_email;
IF w_customer_no>0 THEN
   INSERT INTO address VALUES (NEXTVAL(''address_no''), NOW(), w_customer_no, street_1,street_2, street_3,city,postcode,county,country,FALSE);
ELSE
   RAISE EXCEPTION ''Customer "%" not found in database!'', w_email;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION address_update(char, char, char, char, char, char, char, char)  RETURNS integer AS 'DECLARE
	w_email		ALIAS FOR $1;
	w_street_1	ALIAS FOR $2;
	w_street_2	ALIAS FOR $3;
	w_street_3	ALIAS FOR $4;
	w_city		ALIAS FOR $5;
	w_postcode	ALIAS FOR $6;
	w_county	ALIAS FOR $7;
	w_country	ALIAS FOR $8;
	n_address_no	integer;
BEGIN 
LOCK TABLE address IN SHARE MODE;
SELECT INTO n_address_no address.address_no FROM address, customer 
      WHERE customer.email=w_email AND
	    customer.customer_no=address.customer_no;
IF n_address_no>0 THEN
   UPDATE address SET address_date=NOW(), street_1=w_street_1,
          street_2=w_street_2, street_3=w_street_3, city=w_city,
          postcode=w_postcode, county=w_county, country=w_country
    WHERE address_no=n_address_no;
-- INSERT INTO address VALUES (address_no, NOW(), 
-- customer_no,street_1,street_2, street_3,
-- city,postcode,county,country,FALSE); 
ELSE
   RAISE NOTICE ''Address not found in database!'';
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION address_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
real_delete	boolean DEFAULT FALSE;
BEGIN db_delete;

LOCK TABLE address  IN SHARE MODE;

IF IS NULL should_delete THEN
     should_delete=''MARK''
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to DELETE_ORDER_STATUS function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM address WHERE address_no=delete_no;
     
ELSE
     UPDATE address SET deleted=TRUE WHERE address_no=delete_no;
     
END IF;


RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION card_type_add(char, char) RETURNS integer AS 'DECLARE
	w_short_type	ALIAS FOR $1;
	w_long_type	ALIAS FOR $2;
	num_no		integer;
BEGIN 
LOCK TABLE card_type IN SHARE MODE;
-- RAISE NOTICE ''CHEcking for: "%"'', w_short_type;
SELECT INTO num_no COUNT(*) FROM card_type WHERE type=w_short_type;
IF num_no=0 THEN
     INSERT INTO card_type VALUES (NEXTVAL(''ct_no''), NOW(), w_short_type, w_long_type, FALSE);
ELSE 
--     RAISE NOTICE ''Data found in card_type table: %'', num_no;
     RAISE EXCEPTION ''This "card_type" already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

SELECT card_type_add('Credit card','Credit card option');
SELECT card_type_add('Debit card','Debit card option');
SELECT card_type_add('Check','Someone wants to pay with check');
SELECT card_type_add('Standing order','No idea what the hell is this!');


CREATE FUNCTION card_type_update(integer, char, char) RETURNS integer AS 'DECLARE
	type_no	ALIAS FOR $1;
	card_type	ALIAS FOR $2;
	note		ALIAS FOR $3;
BEGIN 
LOCK TABLE card_type IN SHARE MODE;
INSERT INTO card_type VALUES (type_no, NOW(), card_type, note, FALSE);
 
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION card_type_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	real_delete	boolean DEFAULT FALSE;
BEGIN db_delete;

LOCK TABLE card_type IN SHARE MODE;

IF IS NULL should_delete THEN
     should_delete=''MARK''
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to DELETE_CARD_TYPE  function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM card_type WHERE card_type=delete_no;
     
ELSE
     UPDATE card_type SET deleted=TRUE WHERE card_type=delete_no;
     
END IF;


RETURN 0;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION card_name_add(char, char) RETURNS integer AS 'DECLARE
	card_short	ALIAS FOR $1;
	note		ALIAS FOR $2;
BEGIN 
LOCK TABLE  card_name IN SHARE MODE;
SELECT COUNT(*) FROM card_name WHERE card_short=card_name;
IF NOT FOUND THEN
     INSERT INTO card_name VALUES (NEXTVAL(''card_no''), NOW(), card_name, note, FALSE);
      
ELSE
     RAISE NOTICE ''This order card name already exists in database!''
     RETURN 1
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION card_name_update(integer, char, char) RETURNS integer AS 'DECLARE
	card_no	ALIAS FOR $1;
	card_name	ALIAS FOR $2;
	note		ALIAS FOR $3;
BEGIN 
LOCK TABLE  card_name IN SHARE MODE;
INSERT INTO card_name VALUES (card_no, NOW(), card_name, note, FALSE);
 
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION card_name_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
real_delete	boolean DEFAULT FALSE;
BEGIN db_delete;

LOCK TABLE  card_name  IN SHARE MODE;

IF IS NULL should_delete THEN
     should_delete=''MARK''
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     real_delete = FALSE;
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to CARD_NAME  function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM  card_name WHERE card_no=delete_no;
     
ELSE
     UPDATE  card_name SET deleted=TRUE WHERE  card_no=delete_no;
     
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION payment_type_add(char, char) RETURNS integer AS 'DECLARE
	type_short	ALIAS FOR $1;
	type_full	ALIAS FOR $2;
BEGIN 
LOCK TABLE  payment_type IN SHARE MODE;
SELECT COUNT(*) FROM payment_type WHERE short_type=type_short;
IF NOT FOUND THEN
     INSERT INTO payment_type VALUES (NEXTVAL(''pt_no''), NOW(), type_short,type_full,FALSE);
      
ELSE
     RAISE NOTICE ''This payment type already exists in database!''
     RETURN 1
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION payment_type_update(integer, char, char) RETURNS integer AS 'DECLARE
	type_no	ALIAS FOR $1;
	type_short	ALIAS FOR $2;
	type_full	ALIAS FOR $3;
BEGIN 
LOCK TABLE  payment_type IN SHARE MODE;
INSERT INTO payment_type VALUES (type_no, NOW(), type_short, type_full, FALSE);
 
RETURN 0;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION payment_type_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	real_delete	boolean DEFAULT FALSE;
BEGIN 

LOCK TABLE  payment_type  IN SHARE MODE;

IF IS NULL should_delete THEN
     should_delete=''MARK''
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     real_delete = FALSE;
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to PAYMENT_TYPE  function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM  payment_type WHERE  type_no=delete_no;
     
ELSE
     UPDATE  payment_type SET deleted=TRUE WHERE  type_no=delete_no;
     
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION contactinfo_add(char, char) RETURNS integer AS 'DECLARE
	status_short	ALIAS FOR $1;
	status_full	ALIAS FOR $2;
BEGIN 
LOCK TABLE  contactinfo IN SHARE MODE;
SELECT COUNT(*) FROM contactinfo WHERE short_status= status_short;
IF NOT FOUND THEN
     INSERT INTO contactinfo VALUES (NEXTVAL(''cs_no''), NOW(), status_short, status_full, FALSE);
      
ELSE
     RAISE NOTICE ''This CUSTOMER STATUS  already exists in database!''
     RETURN 1
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION contactinfo_update(integer, char, char) RETURNS integer AS 'DECLARE
status_no	ALIAS FOR $1;
	status_short	ALIAS FOR $2;
	status_full	ALIAS FOR $3;
BEGIN 
LOCK TABLE  contactinfo IN SHARE MODE;
INSERT INTO contactinfo VALUES (status_no, NOW(), status_short, status_full, FALSE);
 
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION contactinfo_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
real_delete	boolean DEFAULT FALSE;
BEGIN db_delete;

LOCK TABLE  contactinfo  IN SHARE MODE;

IF IS NULL should_delete THEN
     should_delete=''MARK''
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     real_delete = FALSE;
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to CONTACTINFO  function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM  contactinfo WHERE status_no=delete_no;
     
ELSE
     UPDATE  contactinfo SET deleted=TRUE WHERE  status_no=delete_no;
     
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION list_domains(char) RETURNS SETOF varchar AS '
SELECT domain_name AS domain_name FROM domains;
' LANGUAGE 'sql';

-------------------------------------------------------------------------------
CREATE FUNCTION last_action(char) RETURNS int4 AS '
UPDATE staff SET last_action=NOW() WHERE staff_nick = $1 AND last_logoff=NULL;
SELECT 1;
' LANGUAGE 'sql';

-------------------------------------------------------------------------------
CREATE FUNCTION user_logon(char, char) RETURNS varchar AS 'DECLARE
	w_user		ALIAS FOR $1;
	w_pass		ALIAS FOR $2;
	w_name		varchar(20);
	w_lac		varchar(30);
	w_logon		varchar(20);
	w_timestamp	timestamp;
	w_time		char(20);
        a_level		varchar(100);
BEGIN

SELECT TRIM(staff.name), TRIM(Cast(staff.last_logon AS CHAR)),
       staff.last_action, TRIM(auth_level.auth_descript)
       INTO w_name, w_lac, w_timestamp, a_level 
       FROM staff, auth_level
       WHERE staff.auth_level=auth_level.level_no 
         AND staff.staff_nick=w_user AND staff.password=w_pass;
IF NOT FOUND THEN
   RAISE EXCEPTION ''You are not authorized to connect to database!'';
   w_name=''ERROR'';
END IF;

SELECT last_logon INTO w_logon FROM staff WHERE staff_nick=w_user AND password=w_pass AND last_logoff IS NULL;
IF FOUND THEN
   IF w_logon IS NOT NULL THEN
      SELECT INTO w_time CAST(NOW() - w_timestamp AS CHAR);
--      RAISE NOTICE ''Elapsed time: %'', w_time;
      IF w_time>''00:00:10'' THEN
         UPDATE staff SET last_logoff=NOW() WHERE staff_nick=w_user;
--         RAISE NOTICE ''You are now logged off.<BR>Please logon again!'';
--         RETURN;
	 w_logon='''';
      END IF;
   END IF;
   IF w_logon='''' OR w_logon IS NULL THEN
      UPDATE staff SET last_logon=NOW(), last_logoff=NULL WHERE staff_nick=w_user AND password=w_pass;
   ELSE
      RAISE EXCEPTION ''%,<BR>You are already connected to database!<BR>Elapsed time: %'', w_name, w_time;
      w_name=''ALREADY CONNECTED'';
   END IF;
ELSE
   UPDATE staff SET last_logon=NOW(), last_logoff=NULL WHERE staff_nick=w_user AND password=w_pass;
END IF;

IF w_lac=NULL THEN
   w_lac  = ''N/A N/A'';
END IF;

SELECT INTO w_logon last_action(w_user);

RETURN w_name || '','' || w_lac || '','' || a_level; 
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION user_logoff(char) RETURNS time AS 'DECLARE
	w_snick		ALIAS FOR $1;
        w_str		char(20);
	w_aaa		integer;
	time_wasted	time;
	l_logon		timestamp;
	l_logoff	timestamp;
BEGIN

SELECT INTO w_aaa check_auth_level(''LOGON'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO w_str CAST(last_logoff AS CHAR) FROM staff WHERE staff_nick=w_snick AND last_logon IS NOT NULL;
IF FOUND THEN
   IF w_str IS NULL OR w_str='''' THEN
      UPDATE staff SET last_logoff=NOW() WHERE staff_nick=w_snick;
      SELECT INTO l_logoff, l_logon last_logoff, last_logon
             FROM staff 
            WHERE staff_nick=w_snick;
      SELECT INTO time_wasted (l_logoff - l_logon);
   ELSE
      RAISE EXCEPTION ''You are not logged on!'';
   END IF;
ELSE
   RAISE EXCEPTION ''You have never logged on before!'';
END IF;

RETURN time_wasted;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_data_add(char, char, char, char, char, char, char, char, char, char) RETURNS integer AS 'DECLARE
	a1		ALIAS FOR $1;
	a2		ALIAS FOR $2;
	a3		ALIAS FOR $3;
	a4		ALIAS FOR $4;
	a5		ALIAS FOR $5;
	a6		ALIAS FOR $6;
	a7		ALIAS FOR $7;
	a8		ALIAS FOR $8;
	a9		ALIAS FOR $9;
	a10		ALIAS FOR $10;
	num_no		integer;
BEGIN
   SELECT INTO num_no customer_data_add(a1, a2, a3, a4, a5, a6, a7, a8, a9, 0.0);
   RETURN num_no;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_data_add(char, char, char, char, char, char, char, char, char, decimal) RETURNS integer AS 'DECLARE
	w_email		ALIAS FOR $1;
	w_status	ALIAS FOR $2;
	w_type		ALIAS FOR $3;
	w_domain	ALIAS FOR $4;
	w_cust_name	ALIAS FOR $5;
	w_cust_surname	ALIAS FOR $6;
	w_organization	ALIAS FOR $7;
	w_contact	ALIAS FOR $8;
	w_password	ALIAS FOR $9;
	w_discount	ALIAS FOR $10;
	num_no		integer;
	n_status_no	integer;
	n_type_no	integer;
	n_domain_no	integer;
	n_staff_no	integer;
	n_staff_chr	char(20);
BEGIN 
LOCK TABLE customer IN SHARE MODE;
SELECT INTO num_no COUNT(*) FROM customer WHERE email=w_email;
IF num_no=0 THEN
   SELECT INTO n_status_no status_no 
	  FROM customer_status
         WHERE short_status=w_status;
   IF n_status_no=0 OR n_status_no=NULL OR NOT FOUND THEN
      RAISE EXCEPTION ''Customer status "%" does not exist!'', w_status;
   END IF;
   SELECT INTO n_type_no type_no 
	  FROM customer_type
         WHERE short_type=w_type;
   IF n_type_no=0 OR n_type_no=NULL OR NOT FOUND THEN
      RAISE EXCEPTION ''Customer type "%" does not exist!'', w_type;
   END IF;
   SELECT INTO n_domain_no domain_no
	  FROM domains
         WHERE domain=w_domain;
   IF n_domain_no=0 OR n_domain_no=NULL OR NOT FOUND THEN
      RAISE EXCEPTION ''Domain "%" does not exist!'', w_domain;
   END IF;
   SELECT INTO n_staff_chr CURRENT_USER;
   SELECT INTO n_staff_no staff_no
          FROM staff
         WHERE staff_nick=n_staff_chr;
   IF n_staff_no=0 OR n_staff_no=NULL OR NOT FOUND THEN
      RAISE EXCEPTION ''User "%" does not exist!'', n_staff_chr;
   END IF;

   INSERT INTO customer VALUES (
      NEXTVAL(''customer_no''), NOW(), w_email, n_status_no, n_type_no,
      n_domain_no, w_cust_name, w_cust_surname, w_organization, NULL,
      w_contact, w_password, n_staff_no, w_discount, NULL, FALSE);
ELSE
     RAISE EXCEPTION ''Customer "%" already exists in database!'', w_email;
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_data_update(char, char, char, char, char, char, char, char, char, decimal) RETURNS integer AS 'DECLARE
	a1		ALIAS FOR $1;
	a2		ALIAS FOR $2;
	a3		ALIAS FOR $3;
	a4		ALIAS FOR $4;
	a5		ALIAS FOR $5;
	a6		ALIAS FOR $6;
	a7		ALIAS FOR $7;
	a8		ALIAS FOR $8;
	a9		ALIAS FOR $9;
	a10		ALIAS FOR $10;
	aAa		integer;
	nnn		integer;
BEGIN
SELECT INTO aAa customer_no FROM customer WHERE email=a1;
IF aaa=NULL THEN
   RAISE EXCEPTION ''E-mail address "%" not found!'', a1;
   RETURN 1;
END IF;
SELECT INTO nnn customer_data_update(aAa, a2, a3, a4, a5, a6, a7, a8, a9, a10);
RETURN nnn;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_data_update(integer, char, char, char, char, char, char, char, char, decimal) RETURNS integer AS 'DECLARE
	w_customer_no	ALIAS FOR $1;
	w_email		ALIAS FOR $2;
	w_status	ALIAS FOR $3;
	w_type		ALIAS FOR $4;
	w_domain	ALIAS FOR $5;
	w_cust_name	ALIAS FOR $6;
	w_cust_surname	ALIAS FOR $7;
	w_organization	ALIAS FOR $8;
	w_contact	ALIAS FOR $9;
	w_discount	ALIAS FOR $10;
	num_no		integer;
	n_status_no	integer;
	n_type_no	integer;
	n_domain_no	integer;
	n_staff_no	integer;
	n_staff_chr	char(20);
BEGIN 
-- RAISE NOTICE ''-- % --'', w_customer_no;
LOCK TABLE customer IN SHARE MODE;

   SELECT INTO n_status_no status_no 
	  FROM customer_status
         WHERE short_status=w_status;
   IF n_status_no=0 OR n_status_no=NULL THEN
      RAISE EXCEPTION ''Customer status "%" does not exist!'', w_status;
   END IF;
   SELECT INTO n_type_no type_no 
	  FROM customer_type
         WHERE short_type=w_type;
   IF n_type_no=0 OR n_type_no=NULL THEN
      RAISE EXCEPTION ''Customer type "%" does not exist!'', w_type;
   END IF;
   SELECT INTO n_domain_no domain_no
	  FROM domains
         WHERE domain=w_domain;
   IF n_domain_no=0 OR n_domain_no=NULL THEN
      RAISE EXCEPTION ''Domain "%" does not exist!'', w_domain;
   END IF;
   SELECT INTO n_staff_chr CURRENT_USER;
   SELECT INTO n_staff_no staff_no
          FROM staff
         WHERE staff_nick=n_staff_chr;
   IF n_staff_no=0 OR n_staff_no=NULL THEN
      RAISE EXCEPTION ''User "%" does not exist!'', n_staff_chr;
   END IF;

UPDATE customer SET email=w_email, status_no=n_status_no,
       type_no=n_type_no, domain_no=n_domain_no, cust_name=w_cust_name,
       cust_surname=w_cust_surname, organization=w_organization,
       contact=w_contact, staff_no=n_staff_no, discount=w_discount
       WHERE customer_no=w_customer_no;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_data_delete(char, char) RETURNS integer AS 'DECLARE
	w_email		ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_no		integer=0;
BEGIN

LOCK TABLE customer	IN SHARE MODE;
LOCK TABLE telephone	IN SHARE MODE;
LOCK TABLE address	IN SHARE MODE;
LOCK TABLE orders	IN SHARE MODE;
LOCK TABLE invoice	IN SHARE MODE;
LOCK TABLE customer_card IN SHARE MODE;

should_delete = shld_delete;

IF should_delete='''' OR should_delete=NULL THEN
     should_delete=''CHECK'';
END IF;

IF should_delete=''CHECK'' OR should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
     IF should_delete=''CHECK'' THEN
-- DODATI PROVERE !!!
--          SELECT INTO num_no COUNT(cust_no) FROM orders WHERE type_no=n_type_no;
--          IF NOT FOUND THEN
--             real_delete=TRUE;
--          END IF;
     END IF;
ELSE
     RAISE NOTICE ''Wrong parameter passed to DELETE_CUSTOMER_TYPE function. %'', should_delete;
     RETURN 1;
END IF;

SELECT INTO num_no customer_no FROM customer WHERE email=w_email;
IF real_delete THEN
     IF num_no>0 THEN
--        RAISE NOTICE ''Real delete of customer data ...'';
	DELETE FROM customer_card WHERE customer_no=num_no;
	DELETE FROM telephone	WHERE customer_no=num_no;
	DELETE FROM address	WHERE customer_no=num_no;
	DELETE FROM customer	WHERE customer_no=num_no;     
     END IF;
ELSE
   IF num_no>0 THEN
--      RAISE NOTICE ''Virtual delete of customer data ...'';
      UPDATE customer_card SET deleted=TRUE WHERE customer_no=num_no;
      UPDATE telephone	SET deleted=TRUE WHERE customer_no=num_no;
      UPDATE address	SET deleted=TRUE WHERE customer_no=num_no;
      UPDATE customer	SET deleted=TRUE WHERE customer_no=num_no;
   END IF;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_card_add(char, char, char, char, timestamp, char) RETURNS integer AS 'DECLARE
	w_email		ALIAS FOR $1;
	w_card_name	ALIAS FOR $2;
	w_card_type	ALIAS FOR $3;
	w_name		ALIAS FOR $4;
	w_expire	ALIAS FOR $5;
	w_card_number	ALIAS FOR $6;
	n_customer_no	integer;
	n_customer_date	timestamp;
        n_card_name	integer;
	n_card_type	integer;
	num_no		integer;
BEGIN 
LOCK TABLE customer_card IN SHARE MODE;
-- RAISE NOTICE ''Checking for: "%, card. no. %"'', w_email, w_card_number;
SELECT INTO num_no COUNT(*) FROM customer_card, customer 
      WHERE customer.customer_no=customer_card.customer_no 
        AND email=w_email 
        AND card_number=w_card_number;
IF num_no=0 THEN
   SELECT DISTINCT INTO n_customer_no, n_customer_date customer_no, customer_date
          FROM customer
         WHERE email=w_email
         ORDER BY customer_date DESC
         LIMIT 1;
   IF n_customer_no>0 THEN
      SELECT INTO n_card_name card_no FROM card_name WHERE card_short=w_card_name;
      IF n_card_name=0 THEN
         RAISE EXCEPTION ''Card name code not found in database!'';
         RETURN 1;
      END IF;
      SELECT INTO n_card_type type_no FROM card_type WHERE type=w_card_type;
      IF n_card_type=0 THEN
         RAISE EXCEPTION ''Card type not found in database!'';
         RETURN 1;
      END IF;
      INSERT INTO customer_card VALUES (
         NEXTVAL(''ccard_no''), NOW(), n_customer_no, n_customer_date,
         n_card_name, n_card_type, w_name, w_expire, w_card_number, FALSE);
   ELSE
      RAISE NOTICE ''Customer not found in customer database!'';
   END IF;
ELSE 
   RAISE EXCEPTION ''This "customer_card" already exists in database!'';
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------------------------
CREATE FUNCTION customer_card_update(integer, char, char, char, char, timestamp, char) RETURNS integer AS 'DECLARE
	w_ccard_no	ALIAS FOR $1;
	w_email		ALIAS FOR $2;
	w_card_name	ALIAS FOR $3;
	w_card_type	ALIAS FOR $4;
	w_name		ALIAS FOR $5;
	w_expire	ALIAS FOR $6;
	w_card_number	ALIAS FOR $7;
	n_customer_no	integer;
	n_customer_date	timestamp;
        n_card_name	integer;
	n_card_type	integer;
	num_no		integer;
BEGIN 
IF w_ccard_no=0 THEN
   SELECT INTO num_no customer_card_add(w_email, w_card_name, w_card_type, w_name, w_expire, w_card_number);
   RETURN 0;
END IF;
LOCK TABLE customer_card IN SHARE MODE;
   SELECT DISTINCT INTO num_no card_no
          FROM customer_card
         WHERE ccard_no=w_ccard_no;
   IF num_no>0 THEN
      SELECT INTO n_card_name card_no FROM card_name WHERE card_short=w_card_name;
      IF n_card_name=0 THEN
         RAISE EXCEPTION ''Card name code not found in database!'';
         RETURN 1;
      END IF;
      SELECT INTO n_card_type type_no FROM card_type WHERE type=w_card_type;
      IF n_card_type=0 THEN
         RAISE EXCEPTION ''Card type not found in database!'';
         RETURN 1;
      END IF;
      UPDATE customer_card SET ccard_date=NOW(), 
             card_no=n_card_name, card_type=n_card_type,
             name_on_card=w_name, card_number=w_card_number
             WHERE ccard_no=w_ccard_no;
   ELSE
      RAISE EXCEPTION ''Customer card not found in customer database!'';
   END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_card_delete(integer, char) RETURNS integer AS 'DECLARE
	w_ccard_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_count	integer;
BEGIN
should_delete=UPPER(shld_delete);

LOCK TABLE customer_card IN SHARE MODE;
LOCK TABLE customer	 IN SHARE MODE;

IF should_delete=NULL OR should_delete='''' THEN
     should_delete=''CHECK'';
END IF;

IF should_delete=''CHECK'' OR should_delete=''MARK'' OR should_delete=''DELETE'' THEN
     IF should_delete = ''DELETE'' THEN
          real_delete = TRUE;
     END IF;
     IF should_delete=''CHECK'' THEN
          SELECT INTO num_count a.ccard_no FROM customer a, customer_Card b
                WHERE ccard_no=w_ccard_no
                  AND a.customer_no=b.customer_no;
          IF num_count=0 THEN
             real_delete=TRUE;
          END IF;
     END IF;
ELSE
     RAISE EXCEPTION ''Wrong parameter passed to DELETE_CUSTOMER_CARD function. %'', should_delete;
     RETURN 1;
END IF;

-- RAISE NOTICE ''Utvrdio : %d'', real_delete;

IF real_delete THEN
     DELETE FROM customer_card WHERE ccard_no=w_ccard_no;
ELSE
     UPDATE customer_card SET deleted=TRUE WHERE ccard_no=w_ccard_no; 
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';
