/*-------------------------------------------------------------------------
 * Copyright (c) 2004-2005 Kenneth W. Sodemann (stuffle@mac.com)
 *-------------------------------------------------------------------------
 * sqlstr
 *
 * Synopsis:
 *    Builds common SQL strings used by several windows.  In all cases,
 *    it is the callers responsibility to free the GStrings.
 *
 * $Id: sqlstr.c,v 1.7 2005/04/03 23:00:00 stuffle Exp $
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to 
 * Free Software Foundation, Inc. 
 * 59 Temple Place, Suite 330 
 * Boston, MA  02111-1307  USA
 *
 *-------------------------------------------------------------------------
 */

#include <glib.h>
#include <gconf/gconf-client.h>

#include "props.h"
#include "sqlstr.h"

void
prj_sql_str (GString *str, queryLevel lvl, gboolean leader,
             gboolean submitter, gboolean grp_member, gboolean admin,
             GConfClient *client, gint pk_num)
{
   SortType  st;

   str = g_string_assign (str,
         "select project_num, name, active, open_version ");
   str = g_string_append (str, "from project prj ");  

   /*
    * Start the WHERE clause by adding the access restrictions.
    */
   str = g_string_append (str, "where (1 = 0 ");  
   if (admin)
      {
      str = g_string_append (str, "or exists (select * ");
      str = g_string_append (str, "from person where access_flag = 1 ");
      str = g_string_append (str, "and login_id = getpgusername()) ");
      }

   if (grp_member)
      {
      str = g_string_append (str, "or exists (select * ");
      str = g_string_append (str, "from responsible ");
      str = g_string_append (str, "where project_num = prj.project_num ");
      str = g_string_append (str, "and login_id = getpgusername()) ");
      }

   if (submitter)
      {
      str = g_string_append (str, "or exists (select * ");
      str = g_string_append (str, "from submitter ");
      str = g_string_append (str, "where project_num = prj.project_num ");
      str = g_string_append (str, "and login_id = getpgusername()) ");
      }

   if (leader)
      {
      str = g_string_append (str, "or prj.login_id = getpgusername() ");
      }
   str = g_string_append (str, ") ");

   switch (lvl)
      {
      case ACTIVE_ITEMS_ONLY:
         str = g_string_append (str, "and prj.active = TRUE ");
         break;

      case ACTIVE_ITEMS_PLUS_IN_USE:
         str = g_string_append (str, "and (prj.active = TRUE or ");
         str = g_string_append (str, "prj.project_num = (select project_num ");
         str = g_string_append (str, "from problem_report ");
         g_string_append_printf (str, "where problem_num = %d)) ", pk_num);
         break;

      case ALL_ITEMS:
         break;

      default:
         g_assert_not_reached();
      }

   st = project_sort_order (client);

   switch (st)
      {
      case SORT_ALPHA:
         str = g_string_append (str, "order by prj.name");
         break;

      case SORT_NEW_FIRST:
         str = g_string_append (str, "order by prj.creation_date desc");
         break;

      case SORT_OLD_FIRST:
         str = g_string_append (str, "order by prj.creation_date");
         break;

      default:
         g_assert_not_reached();
      }
}


void
prj_ver_sql_str (GString    *str,
                 queryLevel  lvl,
                 gint        prj_pk,
                 gint        ver_num)
{
   str = g_string_assign (str, "select version_num, version_text");
   str = g_string_append (str, " from project_version");
   g_string_append_printf (str, " where project_num = %d", prj_pk);

   switch (lvl)
      {
      case ACTIVE_ITEMS_ONLY:
         str = g_string_append (str, " and active = TRUE");
         break;

      case ACTIVE_ITEMS_PLUS_IN_USE:
         /*
          * This level really only makes sense for the closing version.
          */
         str = g_string_append (str, " and (active = TRUE or");
         g_string_append_printf (str, " version_num = %d)", ver_num);
         break;

      case ALL_ITEMS:
         break;

      default:
         g_assert_not_reached();
      }
   
   str = g_string_append (str, " order by version_num");
}


void
pr_type_sql_str (GString     *str,
                 queryLevel   lvl,
                 GConfClient *client,
                 gint         pk_num)
{
   SortType  st;

   str = g_string_assign (str,
         "select p.type_num, p.name,p.active, p.severity_num");
   str = g_string_append (str, " from problem_type p");

   switch (lvl)
      {
      case ACTIVE_ITEMS_ONLY:
         str = g_string_append (str, " WHERE p.active = TRUE");
         break;

      case ACTIVE_ITEMS_PLUS_IN_USE:
         str = g_string_append (str, ", problem_report pr");
         str = g_string_append (str, " WHERE (p.type_num = pr.type_num OR");
         str = g_string_append (str, " p.active = TRUE)");
         g_string_append_printf (str, " AND pr.problem_num = %d", pk_num);
         break;

      case ALL_ITEMS:
         break;

      default:
         g_assert_not_reached();
      }

   st = attribute_sort_order (client);
   switch (st)
      {
      case SORT_USER_DEF:
         str = g_string_append (str, " order by p.order_num");
         break;

      case SORT_ALPHA:
         str = g_string_append (str, " order by p.name");
         break;

      case SORT_NEW_FIRST:
         str = g_string_append (str, " order by p.creation_date desc");
         break;

      case SORT_OLD_FIRST:
         str = g_string_append (str, " order by p.creation_date");
         break;

      default:
         g_assert_not_reached();
      }
}


void
stat_sql_str (GString     *str,
              queryLevel   lvl,
              GConfClient *client,
              gint         pk_num)
{
   SortType  st;

   str = g_string_assign (str, " select s.status_num, s.name, s.active");
   str = g_string_append (str, " from status s");
   
   switch (lvl)
      {
      case ACTIVE_ITEMS_ONLY:
         str = g_string_append (str, " WHERE s.active = TRUE");
         break;

      case ACTIVE_ITEMS_PLUS_IN_USE:
         str = g_string_append (str, ", problem_report pr");
         str = g_string_append (str,
               " WHERE (s.status_num = pr.status_num OR s.active = TRUE)");
         g_string_append_printf (str, " AND pr.problem_num = %d", pk_num);
         break;

      case ALL_ITEMS:
         break;

      default:
         g_assert_not_reached();
      }

   st = attribute_sort_order (client);
   switch (st)
      {
      case SORT_USER_DEF:
         str = g_string_append (str, " order by s.order_num");
         break;

      case SORT_ALPHA:
         str = g_string_append (str, " order by s.name");
         break;

      case SORT_NEW_FIRST:
         str = g_string_append (str, " order by s.creation_date desc");
         break;

      case SORT_OLD_FIRST:
         str = g_string_append (str, " order by s.creation_date");
         break;

      default:
         g_assert_not_reached();
      }
}


void
sevr_sql_str (GString     *str,
              queryLevel   lvl,
              GConfClient *client,
              gint         pk_num)
{
   SortType  st;

   str = g_string_assign (str, " select s.severity_num, s.name, s.active");
   str = g_string_append (str, " from severity s");
   
   switch (lvl)
      {
      case ACTIVE_ITEMS_ONLY:
         str = g_string_append (str, " WHERE s.active = TRUE");
         break;

      case ACTIVE_ITEMS_PLUS_IN_USE:
         str = g_string_append (str, ", problem_report pr");
         str = g_string_append (str,
               " WHERE (s.severity_num = pr.severity_num OR");
         str = g_string_append (str, " s.active = TRUE)");
         g_string_append_printf (str, " AND pr.problem_num = %d", pk_num);
         break;

      case ALL_ITEMS:
         break;

      default:
         g_assert_not_reached();
      }

   st = attribute_sort_order (client);
   switch (st)
      {
      case SORT_USER_DEF:
         str = g_string_append (str, " order by s.order_num");
         break;

      case SORT_ALPHA:
         str = g_string_append (str, " order by s.name");
         break;

      case SORT_NEW_FIRST:
         str = g_string_append (str, " order by s.creation_date desc");
         break;

      case SORT_OLD_FIRST:
         str = g_string_append (str, " order by s.creation_date");
         break;

      default:
         g_assert_not_reached();
      }
}