--DELETE FROM customer_card;
--DELETE FROM telephone;
--DELETE FROM address;
--DELETE FROM customer;
DELETE FROM order_status;

--DROP FUNCTION get_range_data(char, char);
DROP FUNCTION old_order(integer);
DROP FUNCTION customer_type_add(char, char);
DROP FUNCTION customer_type_update(char, char, char);
DROP FUNCTION customer_type_delete(char, char);
DROP FUNCTION telephone_add(char,  char, char);
DROP FUNCTION telephone_update(char, char, char); 
DROP FUNCTION telephone_delete(integer, char);
DROP FUNCTION staff_add(char, char, char, char, char, char, boolean, char);
DROP FUNCTION staff_update(char, char, char, char, char, char, char, boolean, char);
DROP FUNCTION staff_delete(char, char);
DROP FUNCTION order_status_add(char, char);
DROP FUNCTION order_status_update(char,  char, char); 
DROP FUNCTION order_status_delete(char, 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(char, char, char); 
DROP FUNCTION card_type_delete(char, char);
DROP FUNCTION card_name_add(char, char);
DROP FUNCTION card_name_update(char,  char, char); 
DROP FUNCTION card_name_delete(char, 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(char,  char, char); 
DROP FUNCTION customer_status_delete(char, char);
DROP FUNCTION invoice_status_add(char, char);
DROP FUNCTION invoice_status_update(char,  char, char); 
DROP FUNCTION invoice_status_delete(char, 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);
DROP FUNCTION product_data_add(char, char, char, char);
DROP FUNCTION product_data_update(char, char, char, char, char);
DROP FUNCTION product_data_delete(char, char);
DROP FUNCTION product_column_add(char, integer, char, char, char, char, char, char);
DROP FUNCTION product_column_update(char, char, integer, char, char, char, char, char, char);
DROP FUNCTION product_column_delete(char, char, char);
DROP FUNCTION product_table_recreate(char);
DROP FUNCTION product_column_swap(char, char, char);
DROP FUNCTION check_if_reserved(char);
DROP FUNCTION package_data_add(char, char, char, char, boolean);
DROP FUNCTION package_data_update(char, char, char, char, boolean);
DROP FUNCTION package_data_delete(char, char);
DROP FUNCTION package_product_add(char, char, decimal);
DROP FUNCTION package_product_update(char, char, char, decimal);
DROP FUNCTION package_product_delete(char, char, char);
DROP FUNCTION price_add(decimal, decimal, decimal, decimal, decimal, decimal);
DROP FUNCTION price_update(integer, decimal, decimal, decimal, decimal, decimal, decimal);
DROP FUNCTION price_delete(integer, char);
DROP FUNCTION product_price_add(char, decimal, decimal, decimal, decimal, decimal, decimal);
DROP FUNCTION product_price_update(char, decimal, decimal, decimal, decimal, decimal, decimal);
DROP FUNCTION product_price_delete(char, char);
DROP FUNCTION package_price_add(char, decimal, decimal, decimal, decimal, decimal, decimal);
DROP FUNCTION package_price_update(char, decimal, decimal, decimal, decimal, decimal, decimal);
DROP FUNCTION package_price_delete(char, char);
DROP FUNCTION product_renewal_select(char, integer);
DROP FUNCTION order_renewal_select(integer, integer);
DROP FUNCTION package_renewal_select(char, integer);
DROP FUNCTION order_data_add(char, char, char, decimal, char, char, char, boolean);
DROP FUNCTION order_data_update(integer, char, char, decimal, char, char, char, boolean);
DROP FUNCTION order_data_delete(integer, char);
DROP FUNCTION order_item_add(integer, char, integer, integer, integer);
DROP FUNCTION order_item_update(integer, char, integer, integer, integer, integer);
DROP FUNCTION order_item_delete(integer, integer, char, char);
DROP FUNCTION order_product_add(integer, char, decimal);
DROP FUNCTION order_product_update(integer, char, char, decimal);
DROP FUNCTION order_product_delete(integer, char, char);
DROP FUNCTION order_package_add(integer, char, decimal);
DROP FUNCTION order_package_update(integer, char, char, decimal);
DROP FUNCTION order_package_delete(integer, char, char);
DROP FUNCTION invoice_add(integer, char, decimal, char, char, boolean, boolean, timestamp);
DROP FUNCTION invoice_update(integer, char, decimal, char, char, boolean, boolean, timestamp);
DROP FUNCTION invoice_delete(integer, char);
DROP FUNCTION get_max_renewal(integer);
DROP FUNCTION calculate_adequate_price(integer);

-------------------------------------------------------------------------------------
CREATE FUNCTION check_if_reserved(char) RETURNS boolean AS 'DECLARE
	w_rese		ALIAS FOR $1;
	w_reserved	varchar(100);
	n_rtn		boolean;
BEGIN
w_reserved=CAST(TRIM(UPPER(w_rese)) AS VARCHAR);
n_rtn=FALSE;

IF STRPOS(''$ABORT$ABS$ABSOLUTE$ACCESS$ACTION$ADA$ADD$ADMIN$AFTER$AGGREGATE$ALIAS$ALL$ALLOCATE$ALTER$ANALYSE$ANALYZE$AND$ANY$ARE$ARRAY$AS$ASC$ASENSITIVE$ASSERTION$ASSIGNMENT$ASYMMETRIC$AT$ATOMIC$AUTHORIZATION$AVG$BACKWARD$BEFORE$BEGIN$BETWEEN$BINARY$BIT$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$BITVAR$BIT_LENGTH$BLOB$BOOLEAN$BOTH$BREADTH$BY$C$CACHE$CALL$CALLED$CARDINALITY$CASCADE$CASCADED$CASE$CAST$CATALOGCATALOG_NAME$CHAIN$CHAR$CHARACTER$CHARACTERISTICS$CHARACTER_LENGTH$CHARACTER_SET_CATALOG$CHARACTER_SET_NAME$CHARACTER_SET_SCHEMA$CHAR_LENGTH$CHECK$CHECKED$CHECKPOINT$CLASS$CLASS_ORIGIN$CLOB$CLOSE$CLUSTER$COALESCE$COBOL$COLLATE$COLLATION$COLLATION_CATALOG$COLLATION_NAME$COLLATION_SCHEMA$COLUMN$COLUMN_NAME$COMMAND_FUNCTION$COMMAND_FUNCTION_CODE$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$COMMENT$COMMIT$COMMITTED$COMPLETION$CONDITION_NUMBER$CONNECT$CONNECTION$CONNECTION_NAME$CONSTRAINT$CONSTRAINTS$CONSTRAINT_CATALOG$CONSTRAINT_NAME$CONSTRAINT_SCHEMA$CONSTRUCTOR$CONTAINS$CONTINUE$CONVERT$COPY$CORRESPONDING$COUNT$CREATE$CREATEDB$CREATEUSERCROSSCUBE$CURRENT$CURRENT_DATE$CURRENT_PATH$CURRENT_ROLE$CURRENT_TIME$CURRENT_TIMESTAMP$CURRENT_USER$CURSOR$CURSOR_NAME$CYCLE$DATA$DATABASE$DATE$DATETIME_INTERVAL_CODE$DATETIME_INTERVAL_PRECISION$DAY$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$DEALLOCATE$DEC$DECIMAL$DECLARE$DEFAULT$DEFERRABLE$DEFERRED$DEFINED$DEFINER$DELETE$DELIMITERS$DEPTH$DEREF$DESC$DESCRIBE$DESCRIPTOR$DESTROY$DESTRUCTOR$DETERMINISTIC$DIAGNOSTICS$DICTIONARY$DISCONNECT$DISPATCH$DISTINCT$DO$DOMAIN$DOUBLE$DROP$DYNAMIC$DYNAMIC_FUNCTION$DYNAMIC_FUNCTION_CODE$EACH$ELSE$ENCODING$END$END-EXEC$EQUALS$ESCAPE$EVERY$EXCEPT$EXCEPTION$EXCLUSIVE$EXEC$EXECUTE$EXISTING$EXISTS$EXPLAIN$EXTEND$EXTERNAL$EXTRACT$FALSE$FETCH$FINAL$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$FIRST$FLOAT$FOR$FORCE$FOREIGN$FORTRAN$FORWARD$FOUND$FREE$FROM$FULL$FUNCTION$G$GENERAL$GENERATEDGET$GLOBAL$GO$GOTO$GRANT$GRANTED$GROUP$GROUPING$HANDLER$HAVING$HIERARCHY$HOLD$HOST$HOUR$IDENTITY$IGNORE$ILIKEIMMEDIATE$IMPLEMENTATION$IN$INCREMENT$INDEX$INDICATOR$INFIX$INHERITS$INITIALIZE$INITIALLY$INNER$INOUT$INPUT$INSENSITIVE$INSERT$INSTANCE$INSTANTIABLE$INSTEAD$INT$INTEGER$INTERSECT$INTERVAL$INTO$INVOKERIS$ISNULL$ISOLATION$ITERATE$JOIN$K$KEY$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$KEY_MEMBER$KEY_TYPE$LANCOMPILER$LANGUAGE$LARGE$LAST$LATERAL$LEADING$LEFT$LENGTH$LESS$LEVEL$LIKE$LIMIT$LISTEN$LOAD$LOCAL$LOCALTIME$LOCALTIMESTAMP$LOCATION$LOCATOR$LOCKLOWER$M$MAP$MATCH$MAX$MAXVALUE$MESSAGE_LENGTH$MESSAGE_OCTET_LENGTH$MESSAGE_TEXT$METHOD$MIN$MINUTE$MINVALUE$MOD$MODE$MODIFIES$MODIFY$MODULE$MONTH$MORE$MOVE$MUMPS$NAME$NAMES$NATIONAL$NATURAL$NCHAR$NCLOB$NEW$NEXT$NO$NOCREATEDB$NOCREATEUSER$NONE$NOT$NOTHING$NOTIFY$NOTNULL$NULL$NULLABLE$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$NULLIF$NUMBER$NUMERIC$OBJECT$OCTET_LENGTH$OF$OFF$OFFSET$OIDS$OLD$ON$ONLY$OPEN$OPERATION$OPERATOR$OPTION$OPTIONS$OR$ORDER$ORDINALITY$OUT$OUTER$OUTPUT$OVERLAPS$OVERLAY$OVERRIDING$OWNER$PAD$PARAMETER$PARAMETERS$PARAMETER_MODE$PARAMETER_NAME$PARAMETER_ORDINAL_POSITION$PARAMETER_SPECIFIC_CATALOG$PARAMETER_SPECIFIC_NAME$PARAMETER_SPECIFIC_SCHEMA$PARTIAL$PASCAL$PASSWORD$PATH$PENDANT$PLI$POSITION$POSTFIX$PRECISION$PREFIX$PREORDER$PREPARE$PRESERVE$PRIMARY$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$PRIOR$PRIVILEGES$PROCEDURAL$PROCEDURE$PUBLIC$READ$READS$REAL$RECURSIVE$REF$REFERENCES$REFERENCING$REINDEX$RELATIVE$RENAME$REPEATABLE$RESET$RESTRICT$RESULT$RETURN$RETURNED_LENGTH$RETURNED_OCTET_LENGTH$RETURNED_SQLSTATE$RETURNS$REVOKE$RIGHT$ROLE$ROLLBACK$ROLLUP$ROUTINE$ROUTINE_CATALOG$ROUTINE_NAME$ROUTINE_SCHEMA$ROW$ROWS$ROW_COUNT$RULE$SAVEPOINT$SCALE$SCHEMA$SCHEMA_NAME$SCOPE$SCROLL$SEARCH$SECOND$SECTION$SECURITY$SELECT$SELF$SENSITIVE$SEQUENCE$SERIAL$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$SERIALIZABLE$SERVER_NAME$SESSION$SESSION_USER$SET$SETOF$SETS$SHARE$SHOW$SIMILAR$SIMPLE$SIZE$SMALLINT$SOME$SOURCE$SPACE$SPECIFIC$SPECIFICTYPE$SPECIFIC_NAME$SQL$SQLCODE$SQLERROR$SQLEXCEPTION$SQLSTATE$SQLWARNING$START$STATE$STATEMENT$STATIC$STDIN$STDOUT$STRUCTURE$STYLE$SUBCLASS_ORIGIN$SUBLIST$SUBSTRING$SUM$SYMMETRIC$SYSID$SYSTEM$SYSTEM_USER$TABLE$TABLE_NAME$TEMP$TEMPLATE$TEMPORARY$TERMINATE$THAN$THEN$TIME$TIMESTAMP$TIMEZONE_HOUR$TIMEZONE_MINUTE$TO$TOAST$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$TRAILING$TRANSACTION$TRANSACTIONS_COMMITTED$TRANSACTIONS_ROLLED_BACK$TRANSACTION_ACTIVE$TRANSFORM$TRANSFORMS$TRANSLATE$TRANSLATION$TREAT$TRIGGER$TRIGGER_CATALOG$TRIGGER_NAME$TRIGGER_SCHEMA$TRIM$TRUE$TRUNCATE$TRUSTED$TYPE$UNCOMMITTED$UNDER$UNION$UNIQUE$UNKNOWN$UNLISTEN$UNNAMED$UNNEST$UNTIL$UPDATE$UPPER$USAGE$USER$USER_DEFINED_TYPE_CATALOG$USER_DEFINED_TYPE_NAME$USER_DEFINED_TYPE_SCHEMA$USING$VACUUM$VALID$VALUE$VALUES$VARCHAR$VARIABLE$VARYING$VERBOSE$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;
IF NOT n_rtn AND STRPOS(''$VERSION$VIEW$WHEN$WHENEVER$WHERE$WITH$WITHOUT$WORK$WRITE$YEAR$ZONE$'', ''$'' || UPPER(w_reserved) || ''$'')>0 THEN
   n_rtn=TRUE;
END IF;

IF n_rtn=TRUE THEN
   RAISE EXCEPTION ''Word "%" is RESERVED word!'', w_reserved;
END IF;

RETURN n_rtn;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------------
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 EXCEPTION ''Your username "%" does not exists in database.'', curr_user;
     RETURN -1;
END IF;

allevel:=TRIM(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 EXCEPTION ''Level you are checking "%" does not exist in database!'', allevel;
     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;

IF allowed=0 THEN
     RAISE EXCEPTION ''Your priviledge level "%" does not allow you this action!'', allevel;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE customer_type IN SHARE MODE;
-- RAISE EXCEPTION ''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 EXCEPTION ''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(char, char, char) RETURNS integer AS 'DECLARE
	w_short_type_old ALIAS FOR $1;
	w_short_type	ALIAS FOR $2;
	w_long_type	ALIAS FOR $3;
	w_aaa		integer;
	n_type_no	integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE customer_type IN SHARE MODE;
SELECT INTO n_type_no type_no FROM customer_type WHERE short_type=w_short_type_old;
IF n_type_no=NULL THEN
   RAISE EXCEPTION ''Customer type "%" could not be found in database.'', w_short_type_old;
   RETURN 1;
END IF;
SELECT INTO w_aaa COUNT(*) FROM customer_type WHERE short_type=w_short_type AND type_no<>n_type_no;
IF w_aaa=0 THEN
   UPDATE customer_type SET type_date=NOW(), short_type=w_short_type,
          full_type=w_long_type WHERE type_no=n_type_no;
ELSE
   RAISE EXCEPTION ''Customer type "%" exists in database.'', w_short_type;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_type_delete(char, char) RETURNS integer AS 'DECLARE
	w_customer_type ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_count	integer;
	w_aaa		integer;
	w_type_no	integer;
BEGIN
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;

SELECT INTO w_type_no type_no FROM customer_type WHERE short_type=w_customer_type;
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_STATUS 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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE customer_status IN SHARE MODE;
-- RAISE EXCEPTION ''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 EXCEPTION ''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(char, char, char) RETURNS integer AS 'DECLARE
	w_short_status_old ALIAS FOR $1;
	w_short_status	ALIAS FOR $2;
	w_long_status	ALIAS FOR $3;
	w_aaa		integer;
	n_status_no	integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE customer_status IN SHARE MODE;
SELECT INTO n_status_no status_no FROM customer_status WHERE short_status=w_short_status_old;
IF n_status_no=NULL THEN
   RAISE EXCEPTION ''Customer status "%" could not be found in database.'', w_short_status_old;
   RETURN 1;
END IF;
SELECT INTO w_aaa COUNT(*) FROM customer_status WHERE short_status=w_short_status AND status_no<>n_status_no;
IF w_aaa=0 THEN
   UPDATE customer_status SET status_date=NOW(), short_status=w_short_status,
          full_status=w_long_status WHERE status_no=n_status_no;
ELSE
   RAISE EXCEPTION ''Customer status "%" exists in database.'', w_short_status;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_status_delete(char, char) RETURNS integer AS 'DECLARE
	w_customer_status ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_count	integer;
	w_aaa		integer;
	w_status_no	integer;
BEGIN
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;

SELECT INTO w_status_no status_no FROM customer_status WHERE short_status=w_customer_status;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	w_aaa		integer;
BEGIN
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE order_status IN SHARE MODE;
SELECT INTO num_no COUNT(*) FROM order_status WHERE status=status_txt;
IF num_no=0 THEN
     INSERT INTO order_status VALUES (NEXTVAL(''order_status_no''), NOW(),  status_txt, status_note);
ELSE
     RAISE EXCEPTION ''This order status address already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

SELECT order_status_add('Active', 'maybe active?');
SELECT order_status_add('Disabled', 'maybe disabled?');


-------------------------------------------------------------------------------
CREATE FUNCTION order_status_update(char, char, char) RETURNS integer AS 'DECLARE
	w_short_status_old ALIAS FOR $1;
	w_short_status	ALIAS FOR $2;
	w_long_status	ALIAS FOR $3;
	w_aaa		integer;
	n_status_no	integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE order_status IN SHARE MODE;
SELECT INTO n_status_no status_no FROM order_status WHERE status=w_short_status_old;
IF n_status_no=NULL THEN
   RAISE EXCEPTION ''Order status "%" could not be found in database.'', w_short_status_old;
   RETURN 1;
END IF;
SELECT INTO w_aaa COUNT(*) FROM order_status WHERE status=w_short_status AND status_no<>n_status_no;
IF w_aaa=0 THEN
   UPDATE order_status SET status_date=NOW(), status=w_short_status,
          note=w_long_status WHERE status_no=n_status_no;
ELSE
   RAISE EXCEPTION ''Order status "%" exists in database.'', w_short_status;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

--------------------------------------------------------------------------------
CREATE FUNCTION order_status_delete(char, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	varchar(50);
	real_delete	boolean DEFAULT FALSE;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
should_delete=UPPER(shld_delete);

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

IF 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
          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 EXCEPTION ''Wrong parameter passed to DELETE_ORDER_STATUS function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM order_status WHERE status=delete_no;     
ELSE
     UPDATE order_status SET deleted=TRUE WHERE status=delete_no;
     UPDATE customer     SET deleted=TRUE WHERE status=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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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 EXCEPTION ''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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

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 EXCEPTION ''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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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 EXCEPTION ''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;
	shld_delete	ALIAS FOR $2;
	should_delete	char(50);
	real_delete	boolean DEFAULT FALSE;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
should_delete=UPPER(shld_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 EXCEPTION ''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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE card_type IN SHARE MODE;
-- RAISE EXCEPTION ''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 EXCEPTION ''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(char, char, char) RETURNS integer AS 'DECLARE
	w_short_type_old ALIAS FOR $1;
	w_short_type	ALIAS FOR $2;
	w_long_type	ALIAS FOR $3;
	w_aaa		integer;
	n_type_no	integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE card_type IN SHARE MODE;
SELECT INTO n_type_no type_no FROM card_type WHERE type=w_short_type_old;
IF n_type_no=NULL THEN
   RAISE EXCEPTION ''Card type "%" could not be found in database.'', w_short_type_old;
   RETURN 1;
END IF;
SELECT INTO w_aaa COUNT(*) FROM card_type WHERE type=w_short_type AND type_no<>n_type_no;
IF w_aaa=0 THEN
   UPDATE card_type SET type_date=NOW(), type=w_short_type,
          note=w_long_type WHERE type_no=n_type_no;
ELSE
   RAISE EXCEPTION ''Card type "%" exists in database.'', w_short_type;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION card_type_delete(char, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	varchar(60);
	real_delete	boolean DEFAULT FALSE;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
should_delete=UPPER(shld_delete);
LOCK TABLE card_type IN SHARE MODE;

IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to DELETE_CARD_TYPE  function. %'', should_delete;
     RETURN 1;
END IF;

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

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION card_name_add(char, char) RETURNS integer AS 'DECLARE
	w_short_name	ALIAS FOR $1;
	w_long_name	ALIAS FOR $2;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE card_name IN SHARE MODE;
-- RAISE EXCEPTION ''CHEcking for: "%"'', w_short_name;
SELECT INTO num_no COUNT(*) FROM card_name WHERE card_short=w_short_name;
IF num_no=0 THEN
     INSERT INTO card_name VALUES (NEXTVAL(''card_no''), NOW(), w_short_name, w_long_name, FALSE);
ELSE 
--     RAISE EXCEPTION ''Data found in card_name table: %'', num_no;
     RAISE EXCEPTION ''This "card_name" already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

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

-------------------------------------------------------------------------------
CREATE FUNCTION card_name_update(char, char, char) RETURNS integer AS 'DECLARE
	w_short_name_old ALIAS FOR $1;
	w_short_name	ALIAS FOR $2;
	w_long_name	ALIAS FOR $3;
	w_aaa		integer;
	n_name_no	integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE card_name IN SHARE MODE;
SELECT INTO n_name_no card_no FROM card_name WHERE card_short=w_short_name_old;
IF n_name_no=NULL THEN
   RAISE EXCEPTION ''Card name "%" could not be found in database.'', w_short_name_old;
   RETURN 1;
END IF;
SELECT INTO w_aaa COUNT(*) FROM card_name WHERE card_short=w_short_name AND card_no<>n_name_no;
IF w_aaa=0 THEN
   UPDATE card_name SET card_date=NOW(), card_short=w_short_name,
          note=w_long_name WHERE card_no=n_name_no;
ELSE
   RAISE EXCEPTION ''Card name "%" exists in database.'', w_short_name;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';



CREATE FUNCTION card_name_delete(char, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	varchar(50);
	real_delete	boolean DEFAULT FALSE;
BEGIN
should_delete=UPPER(shld_delete);
LOCK TABLE  card_name  IN SHARE MODE;

IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to CARD_NAME  function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM  card_name WHERE card_short=delete_no;     
ELSE
     UPDATE  card_name SET deleted=TRUE WHERE  card_short=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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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 EXCEPTION ''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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

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 EXCEPTION ''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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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 EXCEPTION ''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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

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 EXCEPTION ''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);
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''LOGON'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

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 EXCEPTION ''Elapsed time: %'', w_time;
      IF w_time>''00:00:10'' THEN
         UPDATE staff SET last_logoff=NOW() WHERE staff_nick=w_user;
--         RAISE EXCEPTION ''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;
	n_float		decimal;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
   IF TRIM(a10)='''' THEN
      n_float=0;
   ELSE
      n_float =a10;
   END IF;
   IF n_float>CAST(100.00 AS decimal(10, 2)) THEN
      RAISE EXCEPTION ''Ouch! You can not give discount >=% percent'', n_float;
      RETURN 1;
   END IF;
   SELECT INTO num_no customer_data_add(a1, a2, a3, a4, a5, a6, a7, a8, a9, n_float);
   RETURN num_no;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_data_add(char, char, char, char, char, char, char, char, char, decimal(3,2)) 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);
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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(2,2)) 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;
	n_email		integer;
	n_num		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
SELECT INTO n_email customer_no FROM customer WHERE email=a1;
IF n_email=NULL THEN
   RAISE EXCEPTION ''E-mail address "%" not found!'', a1;
   RETURN 1;
END IF;
SELECT INTO n_num customer_data_update(n_email, a2, a3, a4, a5, a6, a7, a8, a9, a10);
RETURN n_num;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION customer_data_update(integer, char, char, char, char, char, char, char, char, decimal(2,2)) 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);
	n_cno		integer;
	n_ldt		timestamp;
	n_refer		integer;
	n_password	char(20);
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
-- RAISE EXCEPTION ''-- % --'', 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;
   SELECT INTO n_cno, n_ldt, n_refer, n_password customer_no, last_date, refer, password
          FROM customer WHERE email=w_email ORDER BY customer_date DESC LIMIT 1;
   UPDATE customer SET deleted=TRUE WHERE customer_no=n_cno;
--   INSERT INTO customer VALUES (
--      n_cno, NOW(), w_email, n_status_no, n_type_no,
--      n_domain_no, w_cust_name, w_cust_surname, w_organization, n_ldt,
--      w_contact, n_password, n_staff_no, w_discount, n_refer, FALSE);

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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

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 EXCEPTION ''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 EXCEPTION ''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 EXCEPTION ''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_card_name	integer;
	n_card_type	integer;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE customer_card IN SHARE MODE;
-- RAISE EXCEPTION ''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 customer_no FROM customer
         WHERE email=w_email;
   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_card_name, n_card_type, w_name, w_expire, w_card_number, FALSE);
   ELSE
      RAISE EXCEPTION ''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_card_name	integer;
	n_card_type	integer;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''CUSTOMER_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
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 EXCEPTION ''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';

-------------------------------------------------------------------------------
CREATE FUNCTION product_data_add(char, char, char, char) RETURNS integer AS 'DECLARE
	w_domain	ALIAS FOR $1;
	w_name		ALIAS FOR $2;
	w_short_desc	ALIAS FOR $3;
	w_desc		ALIAS FOR $4;
	num_no		integer;
	ptbl		char(20);
	a_create	char(1000);
	n_next		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE products IN SHARE MODE;
SELECT COUNT(*) INTO num_no FROM products WHERE name=w_name;
IF num_no=0 THEN
     SELECT INTO num_no domain_no FROM domains WHERE domain = w_domain;
     IF num_no=NULL THEN
        RAISE EXCEPTION ''Domain "%" not found in database'', w_domain;
     RETURN 1;
     END IF;
     SELECT INTO n_next NEXTVAL(''product_no'');
     ptbl = ''product_'' || n_next;
     INSERT INTO products VALUES (n_next, NOW(), num_no, w_name, w_short_desc, w_desc, 0, 0, ptbl, FALSE);
ELSE
     RAISE EXCEPTION ''This PRODUCT already exists in database!'';
     RETURN 1;
END IF;
a_create=''CREATE TABLE '' || ptbl || '' (price_no integer);'';
EXECUTE a_create;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_data_update(char, char, char, char, char) RETURNS integer AS 'DECLARE
	w_domain	ALIAS FOR $1;
	w_name_old	ALIAS FOR $2;
	w_name		ALIAS FOR $3;
	w_short_desc	ALIAS FOR $4;
	w_desc		ALIAS FOR $5;
	n_domain	integer;
	n_product	integer;
	ptbl		char(20);
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE products IN SHARE MODE;
SELECT INTO n_domain, n_product, ptbl domain_no, product_no, product_table FROM products
	WHERE name=w_name_old;
IF n_product=NULL or n_product=0 THEN
   RAISE EXCEPTION ''Product "%" does not exist in database'', w_name_old;
   RETURN 1;
ELSE
--   UPDATE products SET deleted=TRUE WHERE product_no=n_product AND domain_no=n_domain;
--   INSERT INTO products VALUES 
--   (n_product, NOW(), n_domain, w_name, w_short_desc, w_desc, ptbl, FALSE);
   UPDATE products SET name=w_name, short_descript=w_short_desc, description=w_desc WHERE product_no=n_product;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_data_delete(char, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	num_no		integer;
	n_null		integer;
	n_price_no	integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE products IN SHARE MODE;

IF should_delete=NULL 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 EXCEPTION ''product_data_delete(): Wrong parameter passed to PRODUCT_DATA_DELETE function. %'', delete_no;
     RETURN 1;
END IF;

SELECT INTO num_no, n_price_no product_no, price_no FROM products WHERE name=delete_no;
IF num_no=NULL OR num_no=0 THEN
   RAISE EXCEPTION ''product_data_delete(): Product "%" not found.'', delete_no;
   RETURN 1;
END IF;
IF real_delete THEN
     SELECT INTO n_null price_delete(n_price_no, ''DELETE'');
     DELETE FROM product_description WHERE product_no=num_no;
     DELETE FROM products WHERE product_no=num_no;
ELSE
     SELECT INTO n_null price_delete(n_price_no, ''MARK'');
     UPDATE product_description SET deleted=TRUE WHERE product_no=num_no;
     UPDATE products SET deleted=TRUE WHERE product_no=num_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_column_add(char, integer, char, char, char, char, char, char) RETURNS integer AS 'DECLARE
	w_product_name	ALIAS FOR $1;
	w_index		ALIAS FOR $2;
	w_cn		ALIAS FOR $3;
	w_type		ALIAS FOR $4;
	w_short_desc	ALIAS FOR $5;
	w_desc		ALIAS FOR $6;
	w_default	ALIAS FOR $7;
	w_range		ALIAS FOR $8;
	n_product_no	integer;
	num_no		integer;
	f_no		decimal;
	t_no		timestamp;
	tbl_name	char(20);
	w_column_name	char(50);
	n_bool		boolean;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
w_column_name=TRANSLATE(w_cn, '' '', ''_'');
SELECT INTO n_bool check_if_reserved(w_column_name);
IF n_bool THEN
   RAISE EXCEPTION ''Reserved word "%"'', w_column_name;
   RETURN 1;
END IF;

LOCK TABLE products IN SHARE MODE;
SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
IF n_product_no>0 OR n_product_no=0 THEN
     SELECT INTO num_no COUNT(*) FROM product_description
	   WHERE product_no=n_product_no AND column_name=w_column_name;
     IF num_no=0 OR NOT FOUND THEN
        IF w_type=NULL THEN
           RAISE EXCEPTION ''product_column_add(): Column type can not be NULL.'';
        END IF;
        IF w_column_name=NULL THEN
           RAISE EXCEPTION ''product_column_add(): Column name can not be NULL.'';
        END IF;
        IF w_product_name=NULL THEN
           RAISE EXCEPTION ''product_column_add(): Product name can not be NULL.'';
        END IF;

        INSERT INTO product_description VALUES (n_product_no, w_index,
            w_column_name, w_type, w_short_desc, w_desc, w_default, w_range, FALSE);
        SELECT INTO num_no product_table_recreate(w_product_name);
     ELSE
        RAISE EXCEPTION ''product_column_add(): Column "%.%" already exists in database.'', w_product_name, w_column_name;
        RETURN 1;
     END IF;
ELSE
     RAISE EXCEPTION ''product_column_add(): PRODUCT with name "%" does not exists in database!'', w_product_name;
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_column_update(char, char, integer, char, char, char, char, char, char) RETURNS integer AS 'DECLARE
	w_product_name	ALIAS FOR $1;
	w_old_column_name ALIAS FOR $2;
	w_index		ALIAS FOR $3;
	w_column_name	ALIAS FOR $4;
	w_type		ALIAS FOR $5;
	w_short_desc	ALIAS FOR $6;
	w_desc		ALIAS FOR $7;
	w_default	ALIAS FOR $8;
	w_range		ALIAS FOR $9;
	n_product_no	integer;
	num_no		integer;
	tbl_name	char(20);
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE products IN SHARE MODE;
SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
IF n_product_no>0 THEN
     SELECT INTO num_no COUNT(*) FROM product_description
	   WHERE product_no=n_product_no AND column_name=w_old_column_name;
     IF num_no > 0 THEN
	UPDATE product_description SET column_name=w_column_name,
	   column_type=w_type, short_desc=w_short_desc,
 	   description=w_desc, default_data=w_default,
	   range=w_range, column_no=w_index WHERE product_no=n_product_no
	   AND column_name=w_old_column_name;
     ELSE
        RAISE EXCEPTION ''product_column_delete(): Column "%.%" does not exists in database.'', w_product_name, w_old_column_name;
     END IF;
ELSE
     RAISE EXCEPTION ''product_column_delete(): PRODUCT with name "%" does not exists in database!'', w_product_name;
     RETURN 1;
END IF;
SELECT INTO num_no product_table_recreate(w_product_name);
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_table_recreate(char) RETURNS integer AS 'DECLARE
	w_product	ALIAS FOR $1;
	n_tbl_name	varchar(30);
	r_kol		RECORD;
	a_output	varchar(2000);
	d_output	varchar(100);
	n_product_no	integer;
	nn_nn		integer;
	t_no		timestamp;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

RETURN 0;

a_output='''';
   IF w_product=NULL THEN
      RAISE EXCEPTION ''You must supply product data.'';
      RETURN 1;
   END IF;
   SELECT INTO n_tbl_name, n_product_no TRIM(CAST(product_table AS VARCHAR)), product_no FROM products WHERE name=w_product;
   IF n_tbl_name=NULL THEN 
      RAISE EXCEPTION ''Product "%" does not exist.'', w_product;
      RETURN 1;
   END IF;
   FOR r_kol IN SELECT TRIM(column_name) AS cn, TRIM(column_type) AS ct, TRIM(default_data) AS cd, column_no FROM product_description WHERE product_no=n_product_no ORDER BY column_no LOOP
      IF r_kol.cd=NULL THEN
         RAISE EXCEPTION ''Default value must not be NULL'';
         r_kol.cd='''';
      END IF; 
      IF a_output != '''' THEN
         a_output := a_output || '','';
      END IF;         
      IF r_kol.ct=NULL THEN
         r_kol.ct='''';
         RAISE EXCEPTION ''Type must not be NULL'';
      END IF;
      IF r_kol.cn=NULL THEN
         RAISE EXCEPTION ''Name must not be NULL'';
         r_kol.cn='''';
      END IF;
      a_output := a_output || r_kol.cn || '' '' || r_kol.ct;

      IF r_kol.cd != '''' THEN
         IF UPPER(SUBSTR(r_kol.ct, 1, 7))=''INTEGER'' THEN
            a_output = a_output || '' DEFAULT 0'';
         END IF;
         IF UPPER(SUBSTR(r_kol.ct, 1, 4))=''CHAR'' THEN
            a_output = a_output || '' DEFAULT '''''' || r_kol.cd || '''''''';
         END IF;
         IF UPPER(SUBSTR(r_kol.ct, 1, 7))=''DECIMAL'' THEN
            a_output = a_output || '' DEFAULT 0'';
         END IF;
         IF UPPER(SUBSTR(r_kol.ct, 1, 9))=''TIMESTAMP'' THEN
	    SELECT INTO t_no CAST (r_kol.cd AS TIMESTAMP);
            a_output = a_output || '' DEFAULT '' || t_no;
         END IF;
      END IF;
   END LOOP;

IF a_output= '''' THEN
   a_output= ''price_no integer'';
--   RAISE EXCEPTION ''Table has no definition!'';
ELSE
   a_output = ''CREATE TABLE '' || n_tbl_name || '' (price_no integer, '' || a_output || '');'';
END IF;
   d_output = ''DROP TABLE '' || n_tbl_name || '';'';
--RAISE EXCEPTION ''%'', d_output;
   EXECUTE d_output;
--RAISE EXCEPTION ''%'', a_output;
   EXECUTE a_output;
   d_output = ''GRANT ALL ON '' || n_tbl_name || '' TO PUBLIC;'';
   EXECUTE d_output;

   RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_column_delete(char, char, char) RETURNS integer AS 'DECLARE
	w_product	ALIAS FOR $1;
	w_column	ALIAS FOR $2;
	shld_delete	ALIAS FOR $3;
	should_delete	char(20);
	n_product_no	integer;
	real_delete	boolean DEFAULT FALSE;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE products IN SHARE MODE;

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

SELECT INTO n_product_no product_no FROM products WHERE name=w_product;
IF n_product_no=NULL OR n_product_no=0 THEN
   RAISE EXCEPTION ''Product "%" does not exist in database.'', w_product;
   RETURN 1;
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' OR should_delete=''CHECK'' THEN
     real_delete = FALSE;
     IF should_delete=''CHECK'' THEN
        SELECT INTO num_no COUNT(*) FROM orders WHERE product_no=n_product_no;
        IF num_no=0 THEN
           real_delete=TRUE;
	END IF;
     ELSE
        IF should_delete = ''DELETE'' THEN
           real_delete = TRUE;
        END IF;
     END IF;
ELSE
     RAISE EXCEPTION ''Wrong parameter passed to PRODUCT_COLUMN_DELETE function. %'', shld_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM product_description WHERE product_no=n_product_no AND column_name=w_column;
-- Call DROP/CREATE table for this product
ELSE
     UPDATE product_description SET deleted=TRUE WHERE product_no=n_product_no AND column_name=w_column;
END IF;

SELECT INTO num_no product_table_recreate(w_product);

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
/*
CREATE FUNCTION get_range_data(char, char) RETURNS varchar AS 'DECLARE
	a1		ALIAS FOR $1;
	a2		ALIAS FOR $2;
	s_temp		varchar(200);
	q_tmp		varchar(1000);
	s_tmp		varchar(200);
	ai		integer;
	aj		integer;
	n_product_no	integer;
	k_rrr		RECORD;
BEGIN

--SELECT INTO n_product_no product_no FROM products WHERE name=a1;
--IF n_product_no=NULL OR NOT FOUND THEN
--   RAISE EXCEPTION ''Product "%" not found in database.'', a1;
--   RETURN NULL;
--END IF;
--SELECT INTO s_temp range FROM product_description WHERE product_no=n_product_no AND column_name=a2;
--IF s_temp=NULL THEN
--   RETURN NULL;
--END IF;
--q_tmp='''';
--IF UPPER(TRIM(SUBSTR(s_temp, 1, 7)))=''SELECT'' THEN
--   s_tmp = ''FOR k_rrr IN '' || s_temp;
--   RAISE EXCEPTION ''%'', s_tmp;
-- || ''LOOP IF q_tmp<>''''''''''' THEN q_tmp=s_tmp || ''''''',''''''''; END IF; q_tmp = s_tmp || k_rrr.range; END LOOP;'';
--   RETURN q_tmp;
--ELSE 
--   IF UPPER(SUBSTR(s_temp, 1, 6))=''RANGE '' THEN
--      -- Range of numbers ... defined by start/end/step!
--      s_tmp=''FOR i IN '' || SUBSTR(s_temp, 6, STRLEN(s_temp)-6) || '' LOOP q_tmp=q_tmp || '','' || '' i; LOOP;'';
--      EXECUTE s_tmp;
--      RETURN q_tmp;
--   ELSE
--      -- Range of characters ... separated by ,
--      RETURN s_temp;
--   END IF;
--END IF;


RETURN '''';

END;
' LANGUAGE 'plpgsql';

*/

-------------------------------------------------------------------------------
CREATE FUNCTION product_column_swap(char, char, char) RETURNS integer AS 'DECLARE
	w_product_name	ALIAS FOR $1;
	w_column_name_1 ALIAS FOR $2;
	w_column_name_2 ALIAS FOR $3;
	n_cno_1		integer;
	n_cno_2		integer;
	n_product_no	integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
IF n_product_no>0 OR n_product_no=0 THEN   
   LOCK TABLE product_description IN SHARE MODE;
   SELECT INTO n_cno_1 column_no FROM product_description WHERE product_no=n_product_no AND column_name=w_column_name_1 AND deleted=FALSE;
   IF n_cno_1=NULL THEN
      RAISE EXCEPTION ''Column "%.%" does not exist in database.'', w_product_name, w_column_name_1;
      RETURN 1;
   END IF;
   SELECT INTO n_cno_2 column_no FROM product_description WHERE product_no=n_product_no AND column_name=w_column_name_2 AND deleted=FALSE;
   IF n_cno_2=NULL THEN
      RAISE EXCEPTION ''Column "%.%" does not exist in database.'', w_product_name, w_column_name_2;
      RETURN 1;
   END IF;
--   RAISE EXCEPTION ''Zamena: %. %'', n_cno_1, n_cno_2;
   UPDATE product_description SET column_no=-1      WHERE product_no=n_product_no AND column_no=n_cno_1;
   UPDATE product_description SET column_no=n_cno_1 WHERE product_no=n_product_no AND column_no=n_cno_2;
   UPDATE product_description SET column_no=n_cno_2 WHERE product_no=n_product_no AND column_no=-1;
ELSE
   RAISE EXCEPTION ''PRODUCT with name "%" does not exists in database!'', w_product_name;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION package_data_add(char, char, char, char, boolean) RETURNS integer AS 'DECLARE
	w_domain	ALIAS FOR $1;
	w_name		ALIAS FOR $2;
	w_short_desc	ALIAS FOR $3;
	w_desc		ALIAS FOR $4;
	w_visible	ALIAS FOR $5;
	num_no		integer;
	a_create	char(1000);
	n_next		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE package_definition IN SHARE MODE;
SELECT COUNT(*) INTO num_no FROM package_definition WHERE name=w_name;
IF num_no=0 THEN
     SELECT INTO num_no domain_no FROM domains WHERE domain = w_domain;
     IF num_no=NULL THEN
        RAISE EXCEPTION ''Domain "%" not found in database'', w_domain;
     RETURN 1;
     END IF;
     SELECT INTO n_next NEXTVAL(''package_no'');
     INSERT INTO package_definition VALUES (n_next, NOW(), num_no, w_name, w_short_desc, w_desc, NULL, NULL, w_visible, FALSE);
ELSE
     RAISE EXCEPTION ''This PACKAGE already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION package_data_update(char, char, char, char, boolean) RETURNS integer AS 'DECLARE
	w_name_old	ALIAS FOR $1;
	w_name		ALIAS FOR $2;
	w_short_desc	ALIAS FOR $3;
	w_desc		ALIAS FOR $4;
	w_visible	ALIAS FOR $5;
	n_domain	integer;
	n_package	integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE package_definition IN SHARE MODE;
SELECT INTO n_domain, n_package domain_no, package_no  FROM package_definition
	WHERE name=w_name_old;
IF n_package=NULL or n_package=0 THEN
   RAISE EXCEPTION ''Package "%" does not exist in database'', w_name_old;
   RETURN 1;
ELSE
--   UPDATE package_definition SET deleted=TRUE WHERE package_no=n_package AND domain_no=n_domain;
--   INSERT INTO package_definition VALUES (n_package, NOW(), n_domain, w_name, w_short_desc, w_desc, n_price, w_visible, FALSE);
   UPDATE package_definition SET package_date=NOW(), name=w_name,
          package_short_desc=w_short_desc, package_description=w_desc,
          invisible=w_visible WHERE package_no=n_package;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION package_data_delete(char, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	num_no		integer;
	n_null		integer;
	n_price_no	integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE package_definition IN SHARE MODE;
IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to PACKAGE_DATA_DELETE function. %'', delete_no;
     RETURN 1;
END IF;

SELECT INTO num_no, n_price_no package_no, price_no FROM package_definition WHERE name=delete_no;
IF num_no=NULL OR num_no=0 THEN
   RAISE EXCEPTION ''Package "%" not found.'', delete_no;
   RETURN 1;
END IF;
IF real_delete THEN
     SELECT INTO n_null price_delete(n_price_no, ''DELETE'');
     DELETE FROM package_consists_of WHERE package_no=num_no;
     DELETE FROM package_definition  WHERE package_no=num_no;
ELSE
     SELECT INTO n_null price_delete(n_price_no, ''MARK'');
     UPDATE package_consists_of SET deleted=TRUE WHERE package_no=num_no;
     UPDATE package_definition  SET deleted=TRUE WHERE package_no=num_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION package_product_add(char, char, decimal(16,2)) RETURNS integer AS 'DECLARE
	w_package_name	ALIAS FOR $1;
	w_product_name	ALIAS FOR $2;
	w_quantity	ALIAS FOR $3;
	n_package_no	integer;
	n_product_no	integer;
        num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

LOCK TABLE package_consists_of IN SHARE MODE;
SELECT INTO n_package_no package_no FROM package_definition WHERE name=w_package_name;
IF n_package_no>0 THEN
   SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
   IF n_product_no>0 THEN
      SELECT INTO num_no COUNT(*) FROM package_consists_of
	    WHERE package_no=n_package_no AND product_no=n_product_no;
      IF num_no=0 OR NOT FOUND THEN
--         RAISE NOTICE  ''%, %, %'', n_package_no, n_product_no, w_quantity;
         INSERT INTO package_consists_of VALUES (n_package_no, NOW(),
	             n_product_no, w_quantity, FALSE);
      ELSE
         RAISE EXCEPTION ''Product "%" already exists in this package in database.'', w_product_name;
         RETURN 1;
      END IF;
   ELSE 
     RAISE EXCEPTION ''PRODUCT with name "%" does not exists in database!'', w_product_name;
     RETURN 1;
   END IF;
ELSE
     RAISE EXCEPTION ''PACKAGE with name "%" does not exists in database!'', w_package_name;
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION package_product_update(char, char, char, decimal(16,2)) RETURNS integer AS 'DECLARE
	w_package_name	ALIAS FOR $1;
	w_old_product_name ALIAS FOR $2;
	w_product_name	ALIAS FOR $3;
	w_quantity	ALIAS FOR $4;
	n_product_no	integer;
	n_old_product_no integer;
	n_package_no	integer;
	num_no		integer;
	tbl_name	char(20);
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE package_consists_of IN SHARE MODE;
SELECT INTO n_package_no package_no FROM package_definition WHERE name=w_package_name;
IF n_package_no>0 THEN
   SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
   SELECT INTO n_old_product_no product_no FROM products WHERE name=w_old_product_name;
   IF n_old_product_no=0 OR NOT FOUND THEN
      RAISE EXCEPTION ''Product "%" does not exists in database.'', w_old_product_name;
      RETURN 1;
   END IF;
   SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
   IF n_product_no>0 THEN
      SELECT INTO num_no COUNT(*) FROM package_consists_of
	    WHERE package_no=n_package_no AND product_no=n_product_no;
      IF num_no>0 THEN
         UPDATE package_consists_of SET package_date=NOW(), 
                product_no=n_product_no, quantity=w_quantity
                WHERE product_no=n_old_product_no 
                  AND package_no=n_package_no;
      ELSE
         RAISE EXCEPTION ''Product "%" does not exists in this package in database.'', w_product_name;
         RETURN 1;
      END IF;
   ELSE 
     RAISE EXCEPTION ''PRODUCT with name "%" does not exists in database!'', w_product_name;
     RETURN 1;
   END IF;
ELSE
     RAISE EXCEPTION ''PACKAGE with name "%" does not exists in database!'', w_package_name;
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION package_product_delete(char, char, char) RETURNS integer AS 'DECLARE
	w_package_name	ALIAS FOR $1;
	w_product_name	ALIAS FOR $2;
	shld_delete	ALIAS FOR $3;
	should_delete	char(20);
	n_product_no	integer;
	n_package_no	integer;
	real_delete	boolean DEFAULT FALSE;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE package_definition IN SHARE MODE;

IF should_delete=NULL THEN
     should_delete=''MARK'';
END IF;
-- RAISE NOTICE ''Delete package ... %, %'', w_package_name, w_product_name;
SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
IF n_product_no=NULL OR n_product_no=0 THEN
   RAISE EXCEPTION ''Product "%" does not exist in database.'', w_product_name;
   RETURN 1;
END IF;

SELECT INTO n_package_no package_no FROM package_definition WHERE name=w_package_name;
IF n_package_no=NULL OR n_package_no=0 THEN
   RAISE EXCEPTION ''Package "%" does not exist in database.'', w_product_name;
   RETURN 1;
END IF;

IF should_delete=''MARK'' OR should_delete=''DELETE'' OR should_delete=''CHECK'' THEN
     real_delete = FALSE;
     IF should_delete=''CHECK'' THEN
        SELECT INTO num_no COUNT(*) FROM orders WHERE package_no=n_package_no;
        IF num_no=0 THEN
           real_delete=TRUE;
	END IF;
     ELSE
        IF should_delete = ''DELETE'' THEN
           real_delete = TRUE;
        END IF;
     END IF;
ELSE
     RAISE EXCEPTION ''Wrong parameter passed to PACKAGE_PRODUCT_DELETE function. %'', shld_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM package_consists_of WHERE product_no=n_product_no AND package_no=n_package_no;
-- Call DROP/CREATE table for this product
ELSE
     UPDATE package_consists_of SET deleted=TRUE WHERE product_no=n_product_no AND package_no=n_package_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION price_add(decimal, decimal, decimal, decimal, decimal, decimal) RETURNS integer AS 'DECLARE
	w_price_1	ALIAS FOR $1;
	w_price_2	ALIAS FOR $2;
	w_price_3	ALIAS FOR $3;
	w_price_4	ALIAS FOR $4;
	w_price_5	ALIAS FOR $5;
	w_price_6	ALIAS FOR $6;
	n_price_no	integer;
        num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE price IN SHARE MODE;
SELECT INTO n_price_no NEXTVAL(''price_no'');
INSERT INTO price VALUES(n_price_no, NOW(), w_price_1, w_price_2, w_price_3, w_price_4, w_price_5, w_price_6, FALSE);
RETURN n_price_no;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION price_update(integer, decimal, decimal, decimal, decimal, decimal, decimal) RETURNS integer AS 'DECLARE
	n_price_no	ALIAS FOR $1;
	w_price_1	ALIAS FOR $2;
	w_price_2	ALIAS FOR $3;
	w_price_3	ALIAS FOR $4;
	w_price_4	ALIAS FOR $5;
	w_price_5	ALIAS FOR $6;
	w_price_6	ALIAS FOR $7;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE price IN SHARE MODE;
--RAISE NOTICE ''Updating price ...'';
UPDATE price SET price		=w_price_1,
		 price_once	=w_price_2,
		 price_monthly	=w_price_3,
		 price_quarterly=w_price_4,
		 price_yearly	=w_price_5,
		 price_biyearly	=w_price_6
	WHERE price_no=n_price_no;
RETURN n_price_no;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION price_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE price IN SHARE MODE;

IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to PRICE_DELETE function. %'', delete_no;
     RETURN 1;
END IF;

-- RAISE NOTICE ''Price to delete: %'', delete_no;
IF real_delete THEN
     DELETE FROM price WHERE price_no=delete_no;
ELSE
     UPDATE price SET deleted=TRUE WHERE price_no=delete_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_price_add(char, decimal, decimal, decimal, decimal, decimal, decimal) RETURNS integer AS 'DECLARE
	w_product_name	ALIAS FOR $1;
	w_price_1	ALIAS FOR $2;
	w_price_2	ALIAS FOR $3;
	w_price_3	ALIAS FOR $4;
	w_price_4	ALIAS FOR $5;
	w_price_5	ALIAS FOR $6;
	w_price_6	ALIAS FOR $7;
	n_product_no	integer;
        n_price_no	integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE products IN SHARE MODE;
SELECT INTO n_product_no, n_price_no product_no, price_no FROM products WHERE name=w_product_name;
IF n_product_no=NULL OR n_product_no=0 THEN
   RAISE EXCEPTION ''Product "%" not found.'', w_product_name;
   RETURN 1;
ELSE
   IF n_price_no=0 THEN
      SELECT INTO n_price_no price_add(w_price_1, w_price_2, w_price_3, w_price_4, w_price_5, w_price_6);
      IF n_price_no>0 THEN
         UPDATE products SET price_no=n_price_no WHERE product_no=n_product_no;
      END IF;
   ELSE
      RAISE EXCEPTION ''Price for product "%" was already defined!'', w_product_name;
      RETURN 1;
   END IF;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_price_update(char, decimal, decimal, decimal, decimal, decimal, decimal) RETURNS integer AS 'DECLARE
	w_product_name	ALIAS FOR $1;
	w_price_1	ALIAS FOR $2;
	w_price_2	ALIAS FOR $3;
	w_price_3	ALIAS FOR $4;
	w_price_4	ALIAS FOR $5;
	w_price_5	ALIAS FOR $6;
	w_price_6	ALIAS FOR $7;
	n_price_no	integer;
        n_product_no	integer;
	n_null		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE products IN SHARE MODE;
SELECT INTO n_product_no, n_price_no product_no, price_no FROM products WHERE name=w_product_name;
IF n_product_no=NULL OR n_product_no=0 THEN
   RAISE EXCEPTION ''Product "%" not found.'', w_product_name;
   RETURN 1;
ELSE
   IF n_price_no>0 THEN 
      SELECT INTO n_null price_update(n_price_no, w_price_1, w_price_2, w_price_3, w_price_4, w_price_5, w_price_6);
   ELSE
      IF price=NULL THEN
         RAISE EXCEPTION ''Product "%" price is not yet defined.'', w_product_name;
         RETURN -1;
      END IF;
   END IF;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_price_delete(char, char) RETURNS integer AS 'DECLARE
	w_product_name	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	delete_no	integer;
	n_null		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE price IN SHARE MODE;

IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to PRODUCT_PRICE_DELETE function. %'', delete_no;
     RETURN 1;
END IF;

SELECT INTO delete_no price_no FROM products WHERE name=w_product_name;
--IF delete_no=0 THEN 
--   RAISE EXCEPTION ''Price for product "%" is not defined.'', w_product_name; 
--   RETURN 0;
--END IF;
IF delete_no=0 OR delete_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database.'', w_product_name; 
   RETURN 0;
END IF;

SELECT INTO n_null price_delete(delete_no, should_delete);
IF n_null=0 THEN
   UPDATE products SET price_no=0 WHERE name=w_product_name;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION package_price_add(char, decimal, decimal, decimal, decimal, decimal, decimal) RETURNS integer AS 'DECLARE
	w_package_name	ALIAS FOR $1;
	w_price_1	ALIAS FOR $2;
	w_price_2	ALIAS FOR $3;
	w_price_3	ALIAS FOR $4;
	w_price_4	ALIAS FOR $5;
	w_price_5	ALIAS FOR $6;
	w_price_6	ALIAS FOR $7;
	n_package_no	integer;
        n_price_no	integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE package_definition IN SHARE MODE;
SELECT INTO n_package_no, n_price_no package_no, price_no FROM package_definition WHERE name=w_package_name;
IF n_package_no=NULL OR n_package_no=0 THEN
   RAISE EXCEPTION ''Package "%" not found.'', w_package_name;
   RETURN 1;
ELSE
   IF n_price_no=0 OR n_price_no=NULL THEN
      SELECT INTO n_price_no price_add(w_price_1, w_price_2, w_price_3, w_price_4, w_price_5, w_price_6);
      IF n_price_no>0 THEN
         UPDATE package_definition SET price_no=n_price_no WHERE package_no=n_package_no;
      END IF;
   ELSE
      RAISE EXCEPTION ''Price for package "%" was already defined!'', w_package_name;
      RETURN 1;
   END IF;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION package_price_update(char, decimal, decimal, decimal, decimal, decimal, decimal) RETURNS integer AS 'DECLARE
	w_package_name	ALIAS FOR $1;
	w_price_1	ALIAS FOR $2;
	w_price_2	ALIAS FOR $3;
	w_price_3	ALIAS FOR $4;
	w_price_4	ALIAS FOR $5;
	w_price_5	ALIAS FOR $6;
	w_price_6	ALIAS FOR $7;
	n_price_no	integer;
        n_package_no	integer;
	n_null		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE package_definition IN SHARE MODE;
SELECT INTO n_package_no, n_price_no package_no, price_no FROM package_definition WHERE name=w_package_name;
IF n_package_no=NULL OR n_package_no=0 THEN
   RAISE EXCEPTION ''Package "%" not found.'', w_package_name;
   RETURN 1;
ELSE
--   RAISE NOTICE ''Updating ... %'', n_price_no;
   IF n_price_no>0 THEN 
      SELECT INTO n_null price_update(n_price_no, w_price_1, w_price_2, w_price_3, w_price_4, w_price_5, w_price_6);
   ELSE
      IF price=NULL THEN
         RAISE EXCEPTION ''Package "%" price is not yet defined.'', w_package_name;
         RETURN -1;
      END IF;
   END IF;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION package_price_delete(char, char) RETURNS integer AS 'DECLARE
	w_package_name	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	delete_no	integer;
	n_null		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''PRODUCT_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE price IN SHARE MODE;

IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to PACKAGE_PRICE_DELETE function. %'', delete_no;
     RETURN 1;
END IF;

SELECT INTO delete_no price_no FROM package_definition WHERE name=w_package_name;
IF delete_no=0 THEN 
   RAISE EXCEPTION ''Price for package "%" is not defined.'', w_package_name; 
   RETURN 0;
END IF;
IF delete_no=0 OR delete_no=NULL THEN
   RAISE EXCEPTION ''Package"%" not found in database.'', w_package_name; 
   RETURN 0;
END IF;

SELECT INTO n_null price_delete(delete_no, should_delete);
IF n_null=0 THEN
   UPDATE package SET price_no=0 WHERE name=w_package_name;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION product_renewal_select(char, integer) RETURNS integer AS 'DECLARE
	w_product_name		ALIAS FOR $1;
	w_renewal_period	ALIAS FOR $2;
	n_product_no		integer;
	n_null			integer;
BEGIN 
LOCK TABLE products IN SHARE MODE;

SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
IF n_product_no=0 THEN 
   RAISE EXCEPTION ''Product "%" is not jet defined.'', w_product_name;
   RETURN 1;
END IF;
SELECT INTO n_null COUNT(renewal_no) FROM renewal_period WHERE renewal_no=w_renewal_period;
IF n_null=0 THEN 
   RAISE EXCEPTION ''Renewal period "no.%" is not jet defined.'', w_renewal_period;
   RETURN 1;
END IF;
UPDATE products SET renewal_period=w_renewal_period WHERE product_no=n_product_no;
RETURN 0;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION package_renewal_select(char, integer) RETURNS integer AS 'DECLARE
	w_package_name		ALIAS FOR $1;
	w_renewal_period	ALIAS FOR $2;
	n_package_no		integer;
	n_null			integer;
BEGIN 
LOCK TABLE package_definition IN SHARE MODE;

SELECT INTO n_package_no package_no FROM package_definition WHERE name=w_package_name;
IF n_package_no=0 THEN 
   RAISE EXCEPTION ''Package "%" is not jet defined.'', w_package_name;
   RETURN 1;
END IF;
SELECT INTO n_null COUNT(renewal_no) FROM renewal_period WHERE renewal_no=w_renewal_period;
IF n_null=0 THEN 
   RAISE EXCEPTION ''Renewal period "no.%" is not jet defined.'', w_renewal_period;
   RETURN 1;
END IF;
UPDATE package_definition SET renewal_period=w_renewal_period WHERE package_no=n_package_no;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION order_data_add(char, char, char, decimal, char, char, char, boolean) RETURNS integer AS 'DECLARE
	w_email			ALIAS FOR $1;
	w_domain		ALIAS FOR $2;
	w_status		ALIAS FOR $3;
	w_discount		ALIAS FOR $4;
	w_description		ALIAS FOR $5;
	w_note			ALIAS FOR $6;
	w_contact		ALIAS FOR $7;
	w_invisible		ALIAS FOR $8;
	n_customer_no		integer;
	n_domain_no		integer;
	n_status_no		integer;
	n_next			integer;
	curr_user		char(20);
	n_curr_user		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE orders IN SHARE MODE;

SELECT INTO curr_user CURRENT_USER;
SELECT INTO n_curr_user staff_no FROM staff WHERE staff_nick=curr_user;
IF NOT FOUND OR n_curr_user=0 THEN
     RAISE EXCEPTION ''Your username does not exists in database.'';
     RETURN -1;
END IF;

     SELECT INTO n_domain_no domain_no FROM domains WHERE domain = w_domain;
     IF n_domain_no=NULL THEN
        RAISE EXCEPTION ''Domain "%" not found in database'', w_domain;
     RETURN -1;
     END IF;
     SELECT INTO n_customer_no customer_no FROM customer WHERE email = w_email;
     IF n_customer_no=NULL THEN
        RAISE EXCEPTION ''E-mail "%" not found in database'', w_email;
     RETURN -1;
     END IF;
     SELECT INTO n_status_no status_no FROM order_status WHERE status = w_status;
     IF n_status_no=NULL THEN
        RAISE EXCEPTION ''Order status "%" not found in database'', w_status;
     RETURN -1;
     END IF;
     SELECT INTO n_next NEXTVAL(''order_no'');
     IF n_next=0 OR n_next=NULL THEN
        RAISE EXCEPTION ''Sequence ORDER_NO not defined!'';
        RETURN -1;
     END IF;
     INSERT INTO orders VALUES (n_next, NOW(), n_customer_no,
	    n_domain_no, NULL, NULL, n_status_no, w_discount, w_description,
	    w_note, n_curr_user, w_contact, w_invisible, FALSE);

RETURN n_next;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION order_data_update(integer, char, char, decimal, char, char, char, boolean) RETURNS integer AS 'DECLARE
	w_order			ALIAS FOR $1;
	w_domain		ALIAS FOR $2;
	w_status		ALIAS FOR $3;
	w_discount		ALIAS FOR $4;
	w_description		ALIAS FOR $5;
	w_note			ALIAS FOR $6;
	w_contact		ALIAS FOR $7;
	w_invisible		ALIAS FOR $8;
	n_customer_no		integer;
	n_domain_no		integer;
	n_status_no		integer;
	n_staff_no		integer;
	n_found			integer;
	n_price_no		integer;
	n_renewal_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
--RAISE NOTICE ''///%///'', w_discount;
LOCK TABLE orders IN SHARE MODE;
SELECT INTO n_found COUNT(*) FROM orders WHERE order_no=w_order;
IF n_found THEN
     SELECT INTO n_domain_no domain_no FROM domains WHERE domain = w_domain;
     IF n_domain_no=NULL THEN
        RAISE EXCEPTION ''Domain "%" not found in database'', w_domain;
     RETURN 1;
     END IF;
     SELECT INTO n_status_no status_no FROM order_status WHERE status = w_status;
     IF n_domain_no=NULL THEN
        RAISE EXCEPTION ''Order status "%" not found in database'', w_status;
     RETURN 1;
     END IF;
     UPDATE ORDERS SET status_no=n_status_no, discount=w_discount, order_date=NOW(),  
            description=w_description, notes=w_note, contact=w_contact,
            invisible=w_invisible WHERE order_no=w_order;
ELSE
   RAISE EXCEPTION ''Order number % not found in database!'', w_order;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION order_data_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE orders IN SHARE MODE;
IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to ORDER_DATA_DELETE function. %'', delete_no;
     RETURN 1;
END IF;

SELECT INTO num_no COUNT(order_no) FROM orders WHERE order_no=delete_no;
IF num_no=0 THEN
   RAISE EXCEPTION ''Order "%" not found.'', delete_no;
   RETURN 1;
END IF;
IF real_delete THEN
     DELETE FROM order_consists_of WHERE order_no=delete_no;
     DELETE FROM orders WHERE order_no=delete_no;
ELSE
     UPDATE order_consists_of SET deleted=TRUE WHERE order_no=delete_no;
     UPDATE orders SET deleted=TRUE WHERE order_no=delete_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION order_item_add(integer, char, integer, integer, integer) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_type		ALIAS FOR $2;
	w_data_no	ALIAS FOR $3;
	w_quantity	ALIAS FOR $4;
	w_price_no	ALIAS FOR $5;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE order_consists_of IN SHARE MODE;
INSERT INTO order_consists_of VALUES (
	w_order_no, NOW(), w_type, w_data_no, w_quantity, w_price_no, FALSE);
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION order_item_update(integer, char, integer, integer, integer, integer) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_type		ALIAS FOR $2;
	w_old_data_no	ALIAS FOR $3;
	w_data_no	ALIAS FOR $4;
	w_quantity	ALIAS FOR $5;
	w_price_no	ALIAS FOR $6;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE order_consists_of IN SHARE MODE;
UPDATE order_consists_of SET order_date=NOW(), data_type=w_type,
       quantity=w_quantity, price_no=w_price_no
       WHERE order_no=w_order_no AND data_no=w_data_no AND data_type=w_type;
RETURN 0;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION order_item_delete(integer, integer, char, char) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_delete_no	ALIAS FOR $2;
	w_delete_type	ALIAS FOR $3;
	w_shld_delete	ALIAS FOR $4;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(w_shld_delete);

LOCK TABLE order_consists_of IN SHARE MODE;

IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to ORDER_ITEM_DELETE function. %'', w_delete_no;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM order_consists_of WHERE order_no=w_order_no AND data_no=w_delete_no AND data_type=w_delete_type;
ELSE
     UPDATE order_consists_of SET deleted=TRUE WHERE  order_no=w_order_no AND data_no=w_delete_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION order_product_add(integer, char, decimal) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_product_name	ALIAS FOR $2;
	w_quantity	ALIAS FOR $3;
	n_price_no	integer;
	n_product_no	integer;
	n_err		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO n_product_no, n_price_no product_no, price_no FROM products WHERE name=w_product_name;
IF n_product_no=0 OR n_product_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database'', w_product_name;
   RETURN 1;
END IF;
IF n_price_no=0 THEN
   n_price_no=NULL;
END IF;

SELECT INTO n_err COUNT(*) FROM order_consists_of WHERE order_no=w_order_no AND data_no=n_product_no AND data_type=''product'';
IF n_err>0 THEN
   RAISE EXCEPTION ''Product "%" in order no.% already exists.'', w_product_name, w_order_no;
   RETURN 1;
END IF;

SELECT INTO n_err order_item_add(w_order_no, ''product'', n_product_no, w_quantity, n_price_no);
RETURN n_err;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION order_product_update(integer, char, char, decimal) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_old_product_name	ALIAS FOR $2;
	w_product_name ALIAS FOR $3;
	w_quantity	ALIAS FOR $4;
	n_price_no	integer;
	n_old_product_no integer;
	n_product_no	integer;
	n_err		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO n_product_no, n_price_no product_no, price_no FROM products WHERE name=w_product_name;
IF n_product_no=0 OR n_product_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database'', w_product_name;
   RETURN 1;
END IF;
n_old_product_no=n_product_no;
IF n_price_no=0 THEN
   n_price_no=NULL;
END IF;
--RAISE NOTICE ''OrderITEM UPDATE: % % %'', n_product_no, w_quantity, n_price_no;
SELECT INTO n_err order_item_update(w_order_no, ''product'', n_old_product_no, n_product_no, w_quantity, n_price_no);
RETURN n_err;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION order_product_delete(integer, char, char) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_product_name	ALIAS FOR $2;
	should_delete	ALIAS FOR $3;
	n_product_no	integer;
	n_err		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO n_product_no product_no FROM products WHERE name=w_product_name;
IF n_product_no=0 OR n_product_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database'', w_product_name;
   RETURN 1;
END IF;

SELECT INTO n_err order_item_delete(w_order_no, n_product_no, ''product'', should_delete);
RETURN n_err;
END;
' LANGUAGE 'plpgsql';



-------------------------------------------------------------------------------
CREATE FUNCTION order_package_add(integer, char, decimal) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_package_name	ALIAS FOR $2;
	w_quantity	ALIAS FOR $3;
	n_price_no	integer;
	n_package_no	integer;
	n_err		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO n_package_no, n_price_no package_no, price_no FROM package_definition WHERE name=w_package_name;
IF n_package_no=0 OR n_package_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database'', w_package_name;
   RETURN 1;
END IF;
IF n_price_no=0 THEN
   n_price_no=NULL;
END IF;

SELECT INTO n_err COUNT(*) FROM order_consists_of WHERE order_no=w_order_no AND data_no=n_package_no AND data_type=''package'';
IF n_err>0 THEN
   RAISE EXCEPTION ''Product "%" in order no.% already exists.'', w_package_name, w_order_no;
   RETURN 1;
END IF;

SELECT INTO n_err order_item_add(w_order_no, ''package'', n_package_no, w_quantity, n_price_no);
RETURN n_err;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION order_package_update(integer, char, char, decimal) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_package_name	ALIAS FOR $2;
	w_old_package_name ALIAS FOR $3;
	w_quantity	ALIAS FOR $4;
	n_price_no	integer;
	n_old_package_no integer;
	n_package_no	integer;
	n_err		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO n_package_no, n_price_no package_no, price_no FROM package_definition WHERE name=w_package_name;
IF n_package_no=0 OR n_package_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database'', w_package_name;
   RETURN 1;
END IF;
SELECT INTO n_old_package_no package_no FROM package_definition WHERE name=w_old_package_name;
IF n_package_no=0 OR n_package_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database'', w_old_package_name;
   RETURN 1;
END IF;
IF n_price_no=0 THEN
   n_price_no=NULL;
END IF;

--RAISE NOTICE ''% ... % ... % ... % ...'', n_old_package_no, n_package_no, w_quantity, n_price_no;

SELECT INTO n_err order_item_update(w_order_no, ''package'', n_old_package_no, n_package_no, w_quantity, n_price_no);
RETURN n_err;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION order_package_delete(integer, char, char) RETURNS integer AS 'DECLARE
	w_order_no	ALIAS FOR $1;
	w_package_name	ALIAS FOR $2;
	should_delete	ALIAS FOR $3;
	n_package_no	integer;
	n_err		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO n_package_no package_no FROM package_definition WHERE name=w_package_name;
IF n_package_no=0 OR n_package_no=NULL THEN
   RAISE EXCEPTION ''Product "%" not found in database'', w_package_name;
   RETURN 1;
END IF;

SELECT INTO n_err order_item_delete(w_order_no, n_package_no, ''package'', should_delete);
RETURN n_err;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION invoice_add(integer, char, decimal, char, char, boolean, boolean, timestamp) RETURNS integer AS 'DECLARE
	w_order_no		ALIAS FOR $1;
	w_issue_no		ALIAS FOR $2;
	w_amount		ALIAS FOR $3;
	w_status		ALIAS FOR $4;
	w_renewal		ALIAS FOR $5;
	w_to_print		ALIAS FOR $6;
	w_payed			ALIAS FOR $7;
	w_payed_date		ALIAS FOR $8;
	n_status_no		integer;
	n_next			integer;
	curr_user		char(20);
	n_curr_user		integer;
	n_renewal_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE invoice IN SHARE MODE;

SELECT INTO curr_user CURRENT_USER;
SELECT INTO n_curr_user staff_no FROM staff WHERE staff_nick=curr_user;
IF NOT FOUND OR n_curr_user=0 THEN
     RAISE EXCEPTION ''Your username does not exists in database.'';
     RETURN -1;
END IF;

     SELECT INTO n_status_no status_no FROM invoice_status WHERE status_no = w_status;
     IF n_status_no=NULL THEN
        RAISE EXCEPTION ''Invoice status "%" not found in database'', w_status;
        RETURN -1;
     END IF;
     SELECT INTO n_renewal_no renewal_no FROM renewal_period WHERE renewal_text_short = w_renewal;
     IF n_renewal_no=NULL THEN
        RAISE EXCEPTION ''Renewal period  "%" not found in database'', w_renewal;
        RETURN -1;
     END IF;
     SELECT INTO n_next NEXTVAL(''invoice_no'');
     IF n_next=0 OR n_next=NULL THEN
        RAISE EXCEPTION ''Sequence INVOICE_NO not defined!'';
        RETURN -1;
     END IF;
     
     INSERT INTO invoice VALUES (n_next, NOW(), w_order_no, w_issue_no,
            w_amount, n_status_no, n_renewal_no, w_to_print, w_payed,
	    w_payed_date, n_curr_user);

RETURN n_next;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION invoice_update(integer, char, decimal, char, char, boolean, boolean, timestamp) RETURNS integer AS 'DECLARE
	w_invoice_no		ALIAS FOR $1;
	w_issue_no		ALIAS FOR $2;
	w_amount		ALIAS FOR $3;
	w_status		ALIAS FOR $4;
	w_renewal		ALIAS FOR $5;
	w_to_print		ALIAS FOR $6;
	w_payed			ALIAS FOR $7;
	w_payed_date		ALIAS FOR $8;
	n_status_no		integer;
	n_next			integer;
	curr_user		char(20);
	n_curr_user		integer;
	n_renewal_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE invoice IN SHARE MODE;

SELECT INTO curr_user CURRENT_USER;
SELECT INTO n_curr_user staff_no FROM staff WHERE staff_nick=curr_user;
IF NOT FOUND OR n_curr_user=0 THEN
     RAISE EXCEPTION ''Your username does not exists in database.'';
     RETURN -1;
END IF;

     SELECT INTO n_status_no status_no FROM invoice_status WHERE status_no = w_status;
     IF n_status_no=NULL THEN
        RAISE EXCEPTION ''Invoice status "%" not found in database'', w_status;
        RETURN -1;
     END IF;
     SELECT INTO n_renewal_no renewal_no FROM renewal_period WHERE renewal_text_short = w_renewal;
     IF n_renewal_no=NULL THEN
        RAISE EXCEPTION ''Renewal period  "%" not found in database'', w_renewal;
        RETURN -1;
     END IF;
--     SELECT INTO n_next NEXTVAL(''invoice_no'');
--     IF n_next=0 OR n_next=NULL THEN
--        RAISE EXCEPTION ''Sequence INVOICE_NO not defined!'';
--        RETURN -1;
--     END IF;
     
     UPDATE invoice SET invoice_date=NOW(), issue_no=w_issue_no,
	    price=w_amount, status_no=n_status_no, automatic_renewal=n_renewal_no,
	    to_print=w_to_print, payed=w_payed, payed_date=w_payed_date, staff_no=n_curr_user
            WHERE invoice_no=w_invoice_no;

RETURN n_next;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION invoice_delete(integer, char) RETURNS integer AS 'DECLARE
	delete_no	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(20);
	real_delete	boolean DEFAULT FALSE;
	num_no		integer;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE invoice IN SHARE MODE;
IF should_delete=NULL 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 EXCEPTION ''Wrong parameter passed to INVOICE_DELETE function. %'', delete_no;
     RETURN 1;
END IF;

SELECT INTO num_no COUNT(invoice_no) FROM invoice WHERE invoice_no=delete_no;
IF num_no=0 THEN
   RAISE EXCEPTION ''Invoice no.% not found.'', delete_no;
   RETURN 1;
END IF;
IF real_delete THEN
     DELETE FROM invoice  WHERE invoice_no=delete_no;
ELSE
     UPDATE invoice  SET deleted=TRUE WHERE invoice_no=delete_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION order_renewal_select(integer, integer) RETURNS integer AS 'DECLARE
	w_order_name		ALIAS FOR $1;
	w_renewal_period	ALIAS FOR $2;
	n_order_no		integer;
	n_null			integer;
BEGIN 
LOCK TABLE orders IN SHARE MODE;

SELECT INTO n_order_no order_no FROM orders WHERE order_no=w_order_name;
IF n_order_no=0 THEN 
   RAISE EXCEPTION ''Order "%" is not jet defined.'', w_order_name;
   RETURN 1;
END IF;
SELECT INTO n_null COUNT(renewal_no) FROM renewal_period WHERE renewal_no=w_renewal_period;
IF n_null=0 THEN 
   RAISE EXCEPTION ''Renewal period "no.%" is not jet defined.'', w_renewal_period;
   RETURN 1;
END IF;
UPDATE orders SET renewal_period=w_renewal_period WHERE order_no=n_order_no;
RETURN 0;
END;
' LANGUAGE 'plpgsql';


-------------------------------------------------------------------------------
CREATE FUNCTION get_max_renewal(integer) RETURNS char AS 'DECLARE
	w_order_no		ALIAS FOR $1;
	n_null1			integer;
	n_null2			integer;
BEGIN 

SELECT INTO n_null1 COUNT(*) FROM order_consists_of WHERE order_no=w_order_no;
IF n_null1=0 THEN
   RETURN 0;
END IF;
n_null1=0;
n_null2=0;
SELECT INTO n_null1 MAX(pdf.renewal_period)
       FROM package_definition pdf, order_consists_of oco
      WHERE oco.order_no=w_order_no AND oco.data_no=pdf.package_no AND oco.data_type=''package'';
IF n_null1=NULL THEN
   n_null1=0;
END IF;
SELECT INTO n_null2 MAX(pdf.renewal_period)
       FROM products pdf, order_consists_of oco
      WHERE oco.order_no=w_order_no AND oco.data_no=pdf.product_no AND oco.data_type=''product'';
IF n_null2=NULL THEN
   n_null2=0;
END IF;
IF n_null1<n_null2 THEN
   n_null1=n_null2;
END IF;

RETURN n_null1;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION calculate_adequate_price(integer) RETURNS decimal AS 'DECLARE
	w_order_no		ALIAS FOR $1;
	n_null			integer;
	n_price			decimal;
	n_ppp			decimal;
	n_renewal_no		integer;
	n_column		char(30);
	n_query			char(200);
	n_price_no		integer;
	n1			decimal;
	n2			decimal;
	n3			decimal;
	n4			decimal;
	n5			decimal;
	n_rrr			RECORD;
BEGIN 
n_price=0.0;

SELECT INTO n_null, n_price_no order_no, price_no  FROM orders WHERE order_no=w_order_no;
IF n_null=0 OR n_null=NULL OR NOT FOUND THEN
   RAISE EXCEPTION ''Can not find order no.% in database'', w_order_no;
   RETURN 0;
END IF;


SELECT INTO n_renewal_no get_max_renewal(w_order_no);

n_column=''a.price'';
IF n_renewal_no=0 THEN
   n_column=''a.price_once'';
END IF;
IF n_renewal_no=1 THEN
   n_column=''a.price_monthly'';
END IF;
IF n_renewal_no=4 THEN
   n_column=''a.price_quarterly'';
END IF;
IF n_renewal_no=12 THEN
   n_column=''a.price_yearly'';
END IF;
IF n_renewal_no=24 THEN
   n_column=''a.price_biyearly'';
END IF;


FOR n_rrr IN SELECT a.price, a.price_once, a.price_monthly 
		FROM price a, order_consists_of b 
		WHERE a.price_no = b.price_no AND b.order_no=w_order_no LOOP
--   RAISE NOTICE ''%'', n_rrr.price;
END LOOP;

RETURN n_ppp;
END;
' LANGUAGE 'plpgsql';

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

SELECT invoice_status_add('Invoice active','Active Invoice');
SELECT invoice_status_add('Invoice FROZEN','Frozen Invoice');

-------------------------------------------------------------------------------
CREATE FUNCTION invoice_status_update(char, char, char) RETURNS integer AS 'DECLARE
	w_short_status_old ALIAS FOR $1;
	w_short_status	ALIAS FOR $2;
	w_long_status	ALIAS FOR $3;
	w_aaa		integer;
	n_status_no	integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE invoice_status IN SHARE MODE;
SELECT INTO n_status_no status_no FROM invoice_status WHERE short_status=w_short_status_old;
IF n_status_no=NULL THEN
   RAISE EXCEPTION ''Invoice status "%" could not be found in database.'', w_short_status_old;
   RETURN 1;
END IF;
SELECT INTO w_aaa COUNT(*) FROM invoice_status WHERE short_status=w_short_status AND status_no<>n_status_no;
IF w_aaa=0 THEN
   UPDATE invoice_status SET status_date=NOW(), short_status=w_short_status,
          full_status=w_long_status WHERE status_no=n_status_no;
ELSE
   RAISE EXCEPTION ''Invoice status "%" exists in database.'', w_short_status;
   RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION invoice_status_delete(char, 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;
	w_aaa		integer;
BEGIN 
SELECT INTO w_aaa check_auth_level(''INVOICE_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

should_delete=UPPER(shld_delete);

LOCK TABLE invoice_status IN SHARE MODE;
LOCK TABLE invoice	 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(invoice_no) FROM invoice 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 invoice_status WHERE short_status=w_status_no;     
ELSE
     UPDATE invoice_status SET deleted=TRUE WHERE short_status=w_status_no; 
     UPDATE invoice SET deleted=TRUE WHERE short_status=w_status_no;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION staff_add(char, char, char, char, char, char, boolean, char) RETURNS integer AS 'DECLARE
	w_staff_nick	ALIAS FOR $1;
	w_password	ALIAS FOR $2;
	w_domain	ALIAS FOR $3;
	w_name		ALIAS FOR $4;
	w_telephone	ALIAS FOR $5;
	w_email		ALIAS FOR $6;
	w_manager	ALIAS FOR $7;
	w_auth_level	ALIAS FOR $8;
	n_domain_no	integer;
	n_level_no	integer;
	num_no		integer;
	w_aaa		integer;
	s_nnn		varchar(100);
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE staff IN SHARE MODE;
SELECT INTO num_no COUNT(*) FROM staff WHERE staff_nick=w_staff_nick;
IF num_no=0 THEN
   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_level_no level_no
	  FROM auth_level
         WHERE auth_short=w_auth_level;
   IF n_level_no=0 OR n_level_no=NULL OR NOT FOUND THEN
      RAISE EXCEPTION ''Authorization level "%" does not exist!'', w_auth_level;
   END IF;
   INSERT INTO staff VALUES (NEXTVAL(''staff_no''), w_staff_nick, w_password, n_domain_no, w_name, w_telephone, w_email, w_manager, n_level_no, NULL, NULL, NULL, FALSE);
   IF n_level_no=1 THEN
      s_nnn := ''CREATE USER '' || w_staff_nick || '' CREATEUSER;'';
   ELSE
      s_nnn := ''CREATE USER '' || w_staff_nick;
   END IF;
   EXECUTE s_nnn;
ELSE
     RAISE EXCEPTION ''This staff username already exists in database!'';
     RETURN 1;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION staff_update(char, char, char, char, char, char, char, boolean, char) RETURNS integer AS 'DECLARE
	w_staff_nick_old ALIAS FOR $1;
	w_staff_nick	ALIAS FOR $2;
	w_password	ALIAS FOR $3;
	w_domain	ALIAS FOR $4;
	w_name		ALIAS FOR $5;
	w_telephone	ALIAS FOR $6;
	w_email		ALIAS FOR $7;
	w_manager	ALIAS FOR $8;
	w_auth_level	ALIAS FOR $9;
	n_domain_no	integer;
	n_level_no	integer;
	n_staff_no	integer;
	w_aaa		integer;
	s_nnn		varchar(100);
BEGIN 
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;

SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
LOCK TABLE staff IN SHARE MODE;
SELECT INTO n_staff_no staff_no FROM staff WHERE staff_nick=w_staff_nick_old;
IF n_staff_no>0 THEN
   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_level_no level_no
	  FROM auth_level
         WHERE auth_short=w_auth_level;
   IF n_level_no=0 OR n_level_no=NULL OR NOT FOUND THEN
      RAISE EXCEPTION ''Authorization level "%" does not exist!'', w_auth_level;
   END IF;
   UPDATE staff SET staff_nick=w_staff_nick, password=w_password,
                    domain_no=n_domain_no, name=w_name, telephone=w_telephone, 
                    email=w_email, is_manager=w_manager, auth_level=n_level_no
	      WHERE staff_no=n_staff_no;
   s_nnn := ''DROP USER '' || w_staff_nick_old;
   EXECUTE s_nnn;
   IF n_level_no=1 THEN
      s_nnn := ''CREATE USER '' || w_staff_nick || '' CREATEUSER;'';
   ELSE
      s_nnn := ''CREATE USER '' || w_staff_nick;
   END IF;
   EXECUTE s_nnn;
ELSE
     RAISE EXCEPTION ''This staff username does not exist in database!'';
     RETURN 1;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
CREATE FUNCTION staff_delete(char, char) RETURNS integer AS 'DECLARE
	w_staff_nick	ALIAS FOR $1;
	shld_delete	ALIAS FOR $2;
	should_delete	char(10);
	real_delete	boolean DEFAULT FALSE;
	num_count	integer;
	n_staff_no	integer;
	w_aaa		integer;
	s_nnn		varchar(100);
BEGIN
SELECT INTO w_aaa check_auth_level(''STAFF_WRITE'');
IF w_aaa<0 THEN
   RETURN NULL;
END IF;
should_delete=UPPER(shld_delete);
LOCK TABLE staff IN SHARE MODE;

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

SELECT INTO n_staff_no staff_no FROM staff WHERE staff_nick=w_staff_nick;
IF n_staff_no=0 OR n_staff_no='''' OR n_staff_no=NULL OR NOT FOUND THEN
   RAISE EXCEPTION ''This staff nick does not exist in database'';
   RETURN 1;
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=FALSE;
--          END IF;
     END IF;
ELSE
     RAISE EXCEPTION ''Wrong parameter passed to DELETE_STAFF function. %'', should_delete;
     RETURN 1;
END IF;

IF real_delete THEN
     DELETE FROM staff WHERE staff_no=n_staff_no;
     s_nnn = ''DROP USER '' || w_staff_nick || '';'';
     EXECUTE s_nnn;
ELSE
     UPDATE staff SET deleted=TRUE WHERE staff_no=n_staff_no;
     s_nnn = ''DROP USER '' || w_staff_nick || '';'';
     EXECUTE s_nnn;
END IF;

RETURN 0;
END;
' LANGUAGE 'plpgsql';
