/*-------------------------------------------------------------------------
 * Copyright (c) 1999-2004 Kenneth W. Sodemann (stuffle@mac.com)
 *-------------------------------------------------------------------------
 * attrib_dlg_db
 *
 * Synopsis:
 *   Routines to simplify DB access for the attributes data.
 *
 * $Id: attrib_dlg_db.c,v 1.18 2004/11/27 16:32:48 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
 *
 *-------------------------------------------------------------------------
 */
#ifdef HAVE_CONFIG_H
#  include <config.h>
#endif

#include <ctype.h>
#include <libpq-fe.h>
#include <stdlib.h>
#include <string.h>
#include <glib.h>
#ifdef USE_NEW_GTK
#   include <glib/gi18n.h>
#else
#   ifdef ENABLE_NLS
#      include <libintl.h>
#      define _(String) gettext (String)
#      define Q_(String) g_strip_context ((String), gettext (String))
#      ifdef gettext_noop
#         define N_(String) gettext_noop (String)
#      else
#         define N_(String) (String)
#      endif
#   else /* NLS is disabled */
#      define _(String) (String)
#      define Q_(String) (String)
#      define N_(String) (String)
#      define textdomain(String) (String)
#      define gettext(String) (String)
#      define dgettext(Domain,String) (String)
#      define dcgettext(Domain,String,Type) (String)
#      define bindtextdomain(Domain,Directory) (Domain) 
#   endif
#endif /* USE_NEW_GTK */

#include "attrib_dlg_db.h"
#include "db_utils.h"
#include "error_chks.h"
#include "gstr_utils.h"

#define PK_NUM_POS      0
#define NAME_POS        1
#define RANK_POS        2
#define ACTIVE_POS      3
#define DESCR_POS       4
#define DEF_SEVR_POS    5
#define CLOSED_POS      5

/*------------------------------------------------------------------------
 * read_xxxx_data functions
 *------------------------------------------------------------------------ 
 */
PGresult *
read_sevr_data (PGconn *conn, gboolean no_inactive)
{
   GString  *buffer = g_string_new ("");
   PGresult *res;

   buffer = g_string_assign (buffer, "SELECT severity_num, name, ");
   buffer = g_string_append (buffer, "order_num, active, descr ");
   buffer = g_string_append (buffer, "FROM severity ");
   if (no_inactive)
      {
      buffer = g_string_append (buffer, "WHERE active = TRUE ");
      }
   buffer = g_string_append (buffer, "ORDER BY order_num ");

   res = PQexec (conn, buffer->str);

   g_string_free (buffer, TRUE);

   return res;
}

PGresult *
read_stat_data (PGconn *conn, gboolean no_inactive)
{
   GString  *buffer = g_string_new ("");
   PGresult *res;
   
   buffer = g_string_assign (buffer, "SELECT status_num, name, ");
   buffer = g_string_append (buffer, "order_num, active, descr, ");
   buffer = g_string_append (buffer, "is_closed_status ");
   buffer = g_string_append (buffer, "FROM status ");
   if (no_inactive)
      {
      buffer = g_string_append (buffer, "WHERE active = TRUE ");
      }
   buffer = g_string_append (buffer, "ORDER BY order_num ");
   
   res = PQexec (conn, buffer->str);

   g_string_free (buffer, TRUE);

   return res;
}

PGresult *
read_type_data (PGconn *conn, gboolean no_inactive)
{
   GString  *buffer = g_string_new ("");
   PGresult *res;
   
   buffer = g_string_assign (buffer,
                             "SELECT t.type_num, t.name, s.order_num, ");
   buffer = g_string_append (buffer, "t.active, t.descr, s.name ");
   buffer = g_string_append (buffer, "FROM problem_type t, severity s ");
   buffer = g_string_append (buffer, "WHERE s.severity_num = t.severity_num ");
   if (no_inactive)
      {
      buffer = g_string_append (buffer, "AND t.active = TRUE ");
      }
   buffer = g_string_append (buffer, "ORDER BY t.order_num, t.name ");
  
   res = PQexec (conn, buffer->str);

   g_string_free (buffer, TRUE);

   return res;
}


/*------------------------------------------------------------------------
 * create_xxxx functions
 *------------------------------------------------------------------------ 
 */
gboolean
create_severity (PGconn      *conn,
                 const gchar *name,
                 const gchar *descr,
                 gint        *pk_num)
{
   ExecStatusType  db_status;
   PGresult       *res;
   GString        *sql_buffer = g_string_new ("");
   GString        *name_str = g_string_new (name);
   GString        *descr_str = g_string_new (descr);
   gboolean        sql_ok;

   START_TRANSACTION (conn);

   res = PQexec (conn, "select nextval( 'severity_seq' )");
   db_status = PQresultStatus (res);
   if (db_status == PGRES_TUPLES_OK || db_status == PGRES_COMMAND_OK)
      {
      *pk_num = atoi (PQgetvalue (res, 0, 0));
      PQclear (res);

      g_string_prepare_db_instr (name_str);
      g_string_prepare_db_instr (descr_str);

      g_string_printf
         (sql_buffer,
          "insert into severity (severity_num, name, descr, active) "
          "values (%d, %s, %s, TRUE)",
          *pk_num, name_str->str, descr_str->str);

      res = PQexec (conn, sql_buffer->str);
      }
   
   sql_ok = chk_sql_error (res, _("create severity"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (name_str, TRUE);
   g_string_free (descr_str, TRUE);

   return sql_ok;
}


gboolean
create_status (PGconn      *conn,
               const gchar *name,
               const gchar *descr,
               gboolean     closed,
               gint        *pk_num)
{
   ExecStatusType  db_status;
   PGresult       *res;
   GString        *sql_buffer = g_string_new ("");
   GString        *name_str = g_string_new (name);
   GString        *descr_str = g_string_new (descr);
   gboolean        sql_ok;

   START_TRANSACTION (conn);

   res = PQexec (conn, "select nextval( 'status_seq' )");
   db_status = PQresultStatus (res);
   if (db_status == PGRES_TUPLES_OK || db_status == PGRES_COMMAND_OK)
      {
      *pk_num = atoi (PQgetvalue (res, 0, 0));
      PQclear (res);

      g_string_prepare_db_instr (name_str);
      g_string_prepare_db_instr (descr_str);

      g_string_printf
         (sql_buffer,
          "insert into status (status_num, name, descr, active, "
                              "is_closed_status) "
          "values (%d, %s, %s, TRUE, %s)",
          *pk_num, name_str->str, descr_str->str,
          closed ? "TRUE" : "FALSE");

      res = PQexec (conn, sql_buffer->str);
      }

   sql_ok = chk_sql_error (res, _("create status"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (name_str, TRUE);
   g_string_free (descr_str, TRUE);

   return sql_ok;
}


gboolean
create_type (PGconn      *conn,
             const gchar *name,
             const gchar *descr,
             gint         sevr,
             gint        *pk_num)
{
   ExecStatusType  db_status;
   PGresult       *res;
   GString        *sql_buffer = g_string_new ("");
   GString        *name_str = g_string_new (name);
   GString        *descr_str = g_string_new (descr);
   gboolean        sql_ok;

   START_TRANSACTION (conn);

   res = PQexec (conn, "select nextval( 'problem_type_seq' )");
   db_status = PQresultStatus (res);
   if (db_status == PGRES_TUPLES_OK || db_status == PGRES_COMMAND_OK)
      {
      *pk_num = atoi (PQgetvalue (res, 0, 0));
      PQclear (res);

      g_string_prepare_db_instr (name_str);
      g_string_prepare_db_instr (descr_str);

      g_string_printf
         (sql_buffer,
          "insert into problem_type (type_num, name, descr, active, "
                                    "severity_num) "
          "values (%d, %s, %s, TRUE, %d)",
          *pk_num, name_str->str, descr_str->str, sevr);

      res = PQexec (conn, sql_buffer->str);
      }

   sql_ok = chk_sql_error (res, _("create type"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (name_str, TRUE);
   g_string_free (descr_str, TRUE);

   return sql_ok;
}


/*------------------------------------------------------------------------
 * set_xxxx_yyyy functions
 *------------------------------------------------------------------------ 
 */
gboolean
set_severity_active (PGconn *conn, gint pk_num, gboolean value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer, "UPDATE severity set active = %s where severity_num = %d",
       value ? "TRUE" : "FALSE", pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set severity active"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}

gboolean
set_status_active (PGconn *conn, gint pk_num, gboolean value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer, "UPDATE status set active = %s where status_num = %d",
       value ? "TRUE" : "FALSE", pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set status active"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}

gboolean
set_type_active (PGconn *conn, gint pk_num, gboolean value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer,
       "UPDATE problem_type set active = %s where type_num = %d",
       value ? "TRUE" : "FALSE", pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set problem type active"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}


gboolean
set_severity_order (PGconn *conn, gint pk_num, gint value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer,
       "UPDATE severity set order_num = %d where severity_num = %d",
       value, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set severity order"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}

gboolean
set_status_order (PGconn *conn, gint pk_num, gint value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer,
       "UPDATE status set order_num = %d where status_num = %d",
       value, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set status order"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}

gboolean
set_type_order (PGconn *conn, gint pk_num, gint value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer,
       "UPDATE problem_type set order_num = %d where type_num = %d",
       value, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set type order"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}


gboolean
set_severity_name (PGconn *conn, gint pk_num, const gchar *value)
{
   GString  *sql_buffer = g_string_new ("");
   GString  *str = g_string_new (value);
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_prepare_db_instr (str);
   g_string_printf
      (sql_buffer,
       "UPDATE severity set name = %s where severity_num = %d",
       str->str, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set severity name"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (str, TRUE);

   return sql_ok;
}
 
gboolean
set_status_name (PGconn *conn, gint pk_num, const gchar *value)
{
   GString  *sql_buffer = g_string_new ("");
   GString  *str = g_string_new (value);
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_prepare_db_instr (str);
   g_string_printf
      (sql_buffer,
       "UPDATE status set name = %s where status_num = %d",
       str->str, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set status name"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (str, TRUE);

   return sql_ok;
}
 
gboolean
set_type_name (PGconn *conn, gint pk_num, const gchar *value)
{
   GString  *sql_buffer = g_string_new ("");
   GString  *str = g_string_new (value);
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_prepare_db_instr (str);
   g_string_printf
      (sql_buffer,
       "UPDATE problem_type set name = %s where type_num = %d",
       str->str, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set type name"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (str, TRUE);

   return sql_ok;
}
 

gboolean
set_severity_descr (PGconn *conn, gint pk_num, const gchar *value)
{
   GString  *sql_buffer = g_string_new ("");
   GString  *str = g_string_new (value);
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_prepare_db_instr (str);
   g_string_printf
      (sql_buffer,
       "UPDATE severity set descr = %s where severity_num = %d",
       str->str, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set severity descr"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (str, TRUE);

   return sql_ok;
}
 
gboolean
set_status_descr (PGconn *conn, gint pk_num, const gchar *value)
{
   GString  *sql_buffer = g_string_new ("");
   GString  *str = g_string_new (value);
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_prepare_db_instr (str);
   g_string_printf
      (sql_buffer,
       "UPDATE status set descr = %s where status_num = %d",
       str->str, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set status descr"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (str, TRUE);

   return sql_ok;
}
 
gboolean
set_type_descr (PGconn *conn, gint pk_num, const gchar *value)
{
   GString  *sql_buffer = g_string_new ("");
   GString  *str = g_string_new (value);
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_prepare_db_instr (str);
   g_string_printf
      (sql_buffer,
       "UPDATE problem_type set descr = %s where type_num = %d",
       str->str, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set type descr"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);
   g_string_free (str, TRUE);

   return sql_ok;
}


gboolean
set_type_sevr (PGconn *conn, gint pk_num, gint value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer,
       "UPDATE problem_type set severity_num = %d where type_num = %d",
       value, pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set type severity"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}
 

gboolean
set_status_closed (PGconn *conn, gint pk_num, gboolean value)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);

   g_string_printf
      (sql_buffer,
       "UPDATE status set is_closed_status = %s where status_num = %d",
       value ? "TRUE" : "FALSE", pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("set status closed"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}


/*------------------------------------------------------------------------
 * delete_xxxx functions
 *------------------------------------------------------------------------ 
 */
gboolean
delete_severity (PGconn *conn, gint pk_num)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;

   START_TRANSACTION (conn);
   
   g_string_printf (sql_buffer,
                    "DELETE FROM severity WHERE severity_num = %d",
                    pk_num);
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("delete severity"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}

gboolean
delete_status (PGconn  *conn,
               gint     pk_num)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;
   
   START_TRANSACTION (conn);
   g_string_printf (sql_buffer, 
                    "DELETE FROM status WHERE status_num = %d",
                    pk_num);
   
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("delete status"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}

gboolean
delete_type (PGconn  *conn,
             gint     pk_num)
{
   GString  *sql_buffer = g_string_new ("");
   PGresult *res;
   gboolean  sql_ok;
   
   START_TRANSACTION (conn);

   g_string_printf (sql_buffer, 
                    "DELETE FROM problem_type WHERE type_num = %d",
                    pk_num);
   
   res = PQexec (conn, sql_buffer->str);

   sql_ok = chk_sql_error (res, _("delete problem type"));
   finalize_transaction (conn, res);
   PQclear (res);
   g_string_free (sql_buffer, TRUE);

   return sql_ok;
}


/*------------------------------------------------------------------------
 * get_xxxx functions
 *------------------------------------------------------------------------ 
 */
gint
get_severity_pk (PGconn *conn, const gchar *sevr_name)
{
   GString  *sql_buffer = g_string_new ("");
   GString  *str = g_string_new (sevr_name);
   PGresult *res;
   gint      pk = -1;

   g_string_prepare_db_instr (str);
   g_string_printf (sql_buffer,
                    "select severity_num from severity where name = %s",
                    str->str);

   res = PQexec (conn, sql_buffer->str);
   if (PQntuples (res) > 0)
      {
      pk = atoi (PQgetvalue (res, 0, 0));
      }
   PQclear (res);

   g_string_free (sql_buffer, TRUE);
   g_string_free (str, TRUE);

   return pk;
}
                    

gint
get_pk_num (PGresult *res, gint n)
{
   if (n >= PQntuples (res))
      {
      return -1;
      }
   
   return atoi (PQgetvalue (res, n, PK_NUM_POS));
}

const char *
get_name (PGresult *res, gint n)
{
   if (n >= PQntuples (res)) 
      {
      return "";
      }

   return PQgetvalue (res, n, NAME_POS);
}

const char *
get_rank (PGresult *res, gint n)
{
   if (n >= PQntuples (res)) 
      {
      return "";
      }

   return PQgetvalue (res, n, RANK_POS);
}


gboolean
get_active (PGresult *res, gint n)
{
   if (n >= PQntuples (res)) 
      {
      return FALSE;
      }

   if ((PQgetvalue (res, n, ACTIVE_POS))[0] == 't')
      {
      return TRUE;
      }
   
   return FALSE;
}


gboolean
get_closed_status (PGresult *res, gint n)
{
   if (n >= PQntuples (res))
      {
      return FALSE;
      }

   if ((PQgetvalue (res, n, CLOSED_POS))[0] == 't')
      {
      return TRUE;
      }

   return FALSE;
}


const char *
get_active_yn (PGresult *res, gint n)
{
   if (n >= PQntuples (res)) 
      {
      return "";
      }

   if ((PQgetvalue (res, n, ACTIVE_POS))[0] == 't')
      {
      return "Yes";
      }
   
   return "No";
}

const char *
get_descr (PGresult *res, gint n)
{
   if (n >= PQntuples (res))
      {
      return "";
      }
   
   return PQgetvalue (res, n, DESCR_POS);
}


const char *
get_def_sevr (PGresult *res, gint n)
{
   if (n >= PQntuples (res))
      {
      return "";
      }
   
   return PQgetvalue (res, n, DEF_SEVR_POS);
}