MailManager/sql/v2_1 deleteQueue.zsql,NONE,1.1 deleteUserAbility.zsql,NONE,1.1 editQueue.zsql,NONE,1.1 getHistoricalTickets.zsql,NONE,1.1 listAbilities.zsql,NONE,1.1

[Kevin C. <ke...@us...>]

Update of /cvsroot/mailmanager/MailManager/sql/v2_1
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv10436/sql/v2_1

Added Files:
	deleteQueue.zsql deleteUserAbility.zsql editQueue.zsql 
	getHistoricalTickets.zsql listAbilities.zsql 
Log Message:
Adding missing SQL files for queueing support
Adding in test for Sending mail from 2.0
Adding in the QueueReports placeholder


--- NEW FILE: getHistoricalTickets.zsql ---
<dtml-comment>
title:Get historical tickets and history for a range of dates
connection_id: mailmanager_db
max_rows:0
arguments:sqv_message_id sqv_include_body

This method is specifically used for the queue reports, in order to find
tickets in the queued state at a given point of time

</dtml-comment>

<dtml-comment>
  First of all, find the tickets which are initially in the requested 
  situation. 
</dtml-comment>

SELECT 
    FROM <dtml-var schema>mm_ticket
    WHERE <dtml-sqltest sqv_message_id column="message_id" type="int">

SELECT id AS ticket_id, assigned, CURRENT_TIMESTAMP AS itemdate
    FROM lwtestkev10.mm_ticket
    WHERE CURRENT_TIMESTAMP < '2006-02-05'
    
    UNION (
        SELECT 
            ticket_id, assigned, change_date AS itemdate 
            FROM lwtestkev10.mm_history
                WHERE change_date < '2006-02-07'
    )
ORDER BY ticket_id
;

<dtml-comment>
  Now obtain 
</dtml-comment>

--- NEW FILE: deleteUserAbility.zsql ---
<dtml-comment>
title:Delete an ability from a user
connection_id: mailmanager_db
max_rows:0
arguments:sqv_username sqv_catno sqv_ability
</dtml-comment>

DELETE FROM <dtml-var schema>mm_user_abilities_category<dtml-var sqv_catno>
    <dtml-sqlgroup where>
        <dtml-sqltest sqv_username column="username" type="nb" optional>
      <dtml-and>
        <dtml-sqltest sqv_ability column="username" type="nb" optional>
    </dtml-sqlgroup>

--- NEW FILE: deleteQueue.zsql ---
<dtml-comment>
title:Delete a queue
connection_id: mailmanager_db
max_rows:0
arguments:sqv_queue_name
</dtml-comment>
DELETE FROM <dtml-var schema>mm_queue
WHERE <dtml-sqltest sqv_queue_name column="queue_name" type="nb">

--- NEW FILE: listAbilities.zsql ---
<dtml-comment>
title:List Abilities
connection_id:mailmanager_db
arguments:sqv_username sqv_category_id

Obtains all abilities for the given category id for the user

</dtml-comment>

SELECT category<dtml-var sqv_category_id> AS ability FROM <dtml-var schema>mm_user
    LEFT JOIN <dtml-var schema>mm_user_abilities_category<dtml-var sqv_category_id>
        USING (username)


--- NEW FILE: editQueue.zsql ---
<dtml-comment>
title:Edit an existing queue
connection_id: mailmanager_db
max_rows:0
arguments:sqv_queue_name sqv_queue_user sqv_assign_group
</dtml-comment>

UPDATE <dtml-var schema>mm_queue
    SET queue_name = <dtml-sqlvar sqv_queue_name type="nb">,
    queue_user = <dtml-sqlvar sqv_queue_user type="nb">,
    assign_group = <dtml-sqlvar sqv_assign_group type="nb">