darcs - gombemi

2 dnl

3 dnl This file is part of Gombe-MI.

4 dnl

5 dnl Gombe-MI is free software; you can redistribute it and/or modify

6 dnl it under the terms of the GNU General Public License as published by

7 dnl the Free Software Foundation; either version 3 of the License, or

8 dnl (at your option) any later version.

9 dnl

10 dnl This program is distributed in the hope that it will be useful,

11 dnl but WITHOUT ANY WARRANTY; without even the implied warranty of

12 dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

13 dnl GNU General Public License for more details.

14 dnl

15 dnl You should have received a copy of the GNU General Public License

16 dnl along with Gombe-MI. If not, see <http://www.gnu.org/licenses/>.

17 dnl

18 dnl Delete_gc Delete group comp for a follow.

19 dnl

20 dnl Karl O. Pinc <kop@meme.com>

21 dnl

22 dnl

23 dnl m4 includes

24 include(`copyright.m4')

25 include(`constants.m4')

26 include(`macros.m4')

27 dnl

29 CREATE OR REPLACE FUNCTION delete_gc(followdate DATE

30 , mom VARCHAR(gmi_animid_len))

31 RETURNS BOOLEAN

32 LANGUAGE plpgsql

33 SECURITY DEFINER

34 gmi_function_set_search_path

35 AS $$

36 -- Delete group comp of a follow, specified by date and mom.

37 --

38 -- GPL_notice(` --', `2013', `The Meme Factory, Inc. http://www.meme.com/')

39 --

40 -- Syntax: delete_gc(followdate, mom)

41 --

42 -- Arguments:

43 -- followdate Date of the follow.

44 -- mom Chimpid of mom in the follow.

45 --

46 -- Returns:

47 -- TRUE if group comp rows were deleted and FALSE otherwise.

48 --

49 -- Remarks:

50 --

51 -- Bugs:

53 DECLARE

54 this_followid follows.followid%TYPE;

56 BEGIN

58 -- Find the follow.

59 SELECT follows.followid

60 INTO this_followid

61 FROM follows

62 JOIN followparts ON (followparts.followid = follows.followid)

63 JOIN followroles ON (followroles.role = followparts.role)

64 JOIN chimpids AS fcid ON (fcid.chimpid = followparts.chimpid)

65 JOIN chimpids AS this_cid ON (this_cid.animid = fcid.animid)

66 WHERE follows.date = followdate

67 AND followroles.function = 'gmi_mom'

68 AND this_cid.chimpid = mom;

70 IF NOT FOUND THEN

71 return FALSE;

72 END IF;

74 RETURN delete_gc(this_followid);

75 END;

76 $$;

77 grant_func_priv(`delete_gc(DATE, VARCHAR(gmi_animid_len))')

81 CREATE OR REPLACE FUNCTION delete_gc(this_followid INT)

82 RETURNS BOOLEAN

83 LANGUAGE plpgsql

84 SECURITY DEFINER

85 gmi_function_set_search_path

86 AS $$

87 -- Delete group comp of a follow, specified by followid.

88 --

89 -- GPL_notice(` --', `2013', `The Meme Factory, Inc. http://www.meme.com/')

90 --

91 -- Syntax: delete_gc(followid)

92 --

93 -- Arguments:

94 -- followid FOLLOWS.FollowID of the follow.

95 --

96 -- Returns:

97 -- TRUE if group comp rows were deleted and FALSE otherwise.

98 --

99 -- Remarks:

100 --

101 -- Bugs:

102

103 DECLARE

104 deleted BOOLEAN;

105 this_rgid raw_groups.rgid%TYPE;

106

107 BEGIN

108

109 -- Delete group comps from the beginning of the day to the end.

110 -- This prevents re-assignment of group comps to intervals

111 -- because intervals before the first group comp of the day

112 -- are not assigned to a group comp.

113

114 deleted := FALSE;

115 FOR this_rgid IN

116 SELECT raw_groups.rgid

117 FROM intervals

118 JOIN raw_groups ON (raw_groups.sheetid = intervals.sheetid)

119 WHERE intervals.followid = this_followid

120 GROUP BY raw_groups.rgid

121 ORDER BY raw_groups.time

122 LOOP

123

124 DELETE FROM raw_groups

125 WHERE raw_groups.rgid = this_rgid;

126

127 deleted := TRUE;

128 END LOOP;

129

130 -- Change the fieldfollows.gcstatus to NULL.

131 UPDATE fieldfollows

132 SET gcstatus = NULL

133 FROM (SELECT sheets.ffid

134 FROM intervals

135 JOIN sheets ON (sheets.sheetid = intervals.sheetid)

136 WHERE intervals.followid = this_followid

137 GROUP BY sheets.ffid) AS ffids

138 WHERE fieldfollows.ffid = ffids.ffid;

139

140 RETURN deleted;

141 END;

142 $$;

143 grant_func_priv(`delete_gc(INT)')