swad-core/swad_holiday_database.c

// swad_holiday_database.c: holidays operations with database

/*
    SWAD (Shared Workspace At a Distance),
    is a web platform developed at the University of Granada (Spain),
    and used to support university teaching.

    This file is part of SWAD core.
    Copyright (C) 1999-2024 Antonio Ca<EFBFBD>as Vargas

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Affero General Public License as
    published by the Free Software Foundation, either version 3 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 Affero General Public License for more details.

    You should have received a copy of the GNU Affero General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
/*****************************************************************************/
/********************************* Headers ***********************************/
/*****************************************************************************/

#include "swad_database.h"
#include "swad_global.h"
#include "swad_holiday_database.h"

/*****************************************************************************/
/************** External global variables from others modules ****************/
/*****************************************************************************/

extern struct Globals Gbl;

/*****************************************************************************/
/****************** Create a new holiday or no school period *****************/
/*****************************************************************************/

void Hld_DB_CreateHoliday (const struct Hld_Holiday *Hld)
  {
   DB_QueryINSERT ("can not create holiday",
		   "INSERT INTO hld_holidays"
		   " (InsCod,PlcCod,HldTyp,StartDate,EndDate,Name)"
		   " VALUES"
		   " (%ld,%ld,%u,'%04u%02u%02u','%04u%02u%02u','%s')",
	           Gbl.Hierarchy.Node[Hie_INS].HieCod,
	           Hld->PlcCod,
	           (unsigned) Hld->HldTyp,
	           Hld->StartDate.Year,
	           Hld->StartDate.Month,
	           Hld->StartDate.Day,
	           Hld->EndDate.Year,
	           Hld->EndDate.Month,
	           Hld->EndDate.Day,
	           Hld->Name);
  }

/*****************************************************************************/
/************************* Change the place of a holiday *********************/
/*****************************************************************************/

void Hld_DB_ChangePlace (long HldCod,long PlcCod)
  {
   DB_QueryUPDATE ("can not update the place of a holiday",
		   "UPDATE hld_holidays"
		     " SET PlcCod=%ld"
		   " WHERE HldCod=%ld",
                   PlcCod,
                   HldCod);
  }

/*****************************************************************************/
/************************* Change the type of a holiday **********************/
/*****************************************************************************/

void Hld_DB_ChangeType (long HldCod,Hld_HolidayType_t HldTyp)
  {
   DB_QueryUPDATE ("can not update the type of a holiday",
		   "UPDATE hld_holidays"
		     " SET HldTyp=%u,"
		          "EndDate=StartDate"
		   " WHERE HldCod=%ld",
	           (unsigned) HldTyp,
	           HldCod);
  }

/*****************************************************************************/
/**************** Change the start/end date of a holiday *********************/
/*****************************************************************************/

void Hld_DB_ChangeDate (long HldCod,const char *StrStartOrEndDate,
                        const struct Dat_Date *NewDate)
  {
   DB_QueryUPDATE ("can not update the date of a holiday",
		   "UPDATE hld_holidays"
		     " SET %s='%04u%02u%02u'"
		   " WHERE HldCod=%ld",
	           StrStartOrEndDate,
	           NewDate->Year,
	           NewDate->Month,
	           NewDate->Day,
	           HldCod);
  }

/*****************************************************************************/
/************* Update holiday name changing old name by new name *************/
/*****************************************************************************/

void Hld_DB_ChangeName (long HldCod,char NewHldName[Hld_MAX_BYTES_HOLIDAY_NAME + 1])
  {
   DB_QueryUPDATE ("can not update the text of a holiday",
		   "UPDATE hld_holidays"
		     " SET Name='%s'"
		   " WHERE HldCod=%ld",
		   NewHldName,
		   HldCod);
  }

/*****************************************************************************/
/***************************** List all holidays *****************************/
/*****************************************************************************/

unsigned Hld_DB_GetListHolidays (MYSQL_RES **mysql_res,Hld_Order_t SelectedOrder)
  {
   static const char *OrderBySubQuery[Hld_NUM_ORDERS] =
     {
      [Hld_ORDER_BY_PLACE     ] = "Place,"
	                          "StartDate",
      [Hld_ORDER_BY_START_DATE] = "StartDate,"
	                          "Place",
     };

   return (unsigned)
   DB_QuerySELECT (mysql_res,"can not get holidays",
		   "(SELECT hld_holidays.HldCod,"						// row[0]
			   "hld_holidays.PlcCod,"						// row[1]
			   "plc_places.FullName as Place,"					// row[2]
			   "hld_holidays.HldTyp,"						// row[3]
			   "DATE_FORMAT(hld_holidays.StartDate,'%%Y%%m%%d') AS StartDate,"	// row[4]
			   "DATE_FORMAT(hld_holidays.EndDate,'%%Y%%m%%d') AS EndDate,"		// row[5]
			   "hld_holidays.Name"							// row[6]
		     " FROM hld_holidays,"
			   "plc_places"
		    " WHERE hld_holidays.InsCod=%ld"
		      " AND hld_holidays.PlcCod=plc_places.PlcCod"
		      " AND plc_places.InsCod=%ld)"
		    " UNION "
		   "(SELECT HldCod,"								// row[0]
			   "PlcCod,"								// row[1]
			   "'' as Place,"							// row[2]
			   "HldTyp,"								// row[3]
			   "DATE_FORMAT(StartDate,'%%Y%%m%%d') AS StartDate,"			// row[4]
			   "DATE_FORMAT(EndDate,'%%Y%%m%%d') AS EndDate,"			// row[5]
			   "Name"								// row[6]
		     " FROM hld_holidays"
		    " WHERE InsCod=%ld"
		      " AND PlcCod NOT IN"
			   "(SELECT DISTINCT "
			           "PlcCod"
			     " FROM plc_places"
			    " WHERE InsCod=%ld))"
		    " ORDER BY %s",
		   Gbl.Hierarchy.Node[Hie_INS].HieCod,
		   Gbl.Hierarchy.Node[Hie_INS].HieCod,
		   Gbl.Hierarchy.Node[Hie_INS].HieCod,
		   Gbl.Hierarchy.Node[Hie_INS].HieCod,
		   OrderBySubQuery[SelectedOrder]);
  }

/*****************************************************************************/
/************************* Get holiday data by code **************************/
/*****************************************************************************/

unsigned Hld_DB_GetHolidayDataByCod (MYSQL_RES **mysql_res,long HldCod)
  {
   return (unsigned)
   DB_QuerySELECT (mysql_res,"can not get data of a holiday",
		   "(SELECT hld_holidays.HldCod,"				// row[0]
                           "hld_holidays.PlcCod,"				// row[1]
			   "plc_places.FullName as Place,"			// row[2]
			   "hld_holidays.HldTyp,"				// row[3]
			   "DATE_FORMAT(hld_holidays.StartDate,'%%Y%%m%%d'),"	// row[4]
			   "DATE_FORMAT(hld_holidays.EndDate,'%%Y%%m%%d'),"	// row[5]
			   "hld_holidays.Name"					// row[6]
		     " FROM hld_holidays,"
			   "plc_places"
		    " WHERE hld_holidays.HldCod=%ld"
		      " AND hld_holidays.InsCod=%ld"
		      " AND hld_holidays.PlcCod=plc_places.PlcCod"
		      " AND plc_places.InsCod=%ld)"
		    " UNION "
		   "(SELECT HldCod,"						// row[0]
		           "PlcCod,"						// row[1]
			   "'' as Place,"					// row[2]
			   "HldTyp,"						// row[3]
			   "DATE_FORMAT(StartDate,'%%Y%%m%%d'),"		// row[4]
			   "DATE_FORMAT(EndDate,'%%Y%%m%%d'),"			// row[5]
			   "Name"						// row[6]
		     " FROM hld_holidays"
		    " WHERE HldCod=%ld"
		      " AND InsCod=%ld"
		      " AND PlcCod NOT IN"
			   "(SELECT DISTINCT "
			           "PlcCod"
			     " FROM plc_places"
			    " WHERE InsCod=%ld))",
		   HldCod,
		   Gbl.Hierarchy.Node[Hie_INS].HieCod,
		   Gbl.Hierarchy.Node[Hie_INS].HieCod,
		   HldCod,
		   Gbl.Hierarchy.Node[Hie_INS].HieCod,
		   Gbl.Hierarchy.Node[Hie_INS].HieCod);
  }

/*****************************************************************************/
/******************************* Remove a holiday ****************************/
/*****************************************************************************/

void Hld_DB_RemoveHoliday (long HldCod)
  {
   DB_QueryDELETE ("can not remove a holiday",
		   "DELETE FROM hld_holidays"
		   " WHERE HldCod=%ld",
		   HldCod);
  }