From 4867748500123402b5949151e438eda99a1fec8f Mon Sep 17 00:00:00 2001 From: kambizzandi Date: Fri, 1 Apr 2022 20:05:29 +0430 Subject: [PATCH 1/2] ticket department and unit created --- .../Ticketing/moduleSrc/ORM/Departments.cpp | 75 ++++++++++++++++++ Modules/Ticketing/moduleSrc/ORM/Departments.h | 61 +++++++++++++++ Modules/Ticketing/moduleSrc/ORM/Tickets.cpp | 11 +++ Modules/Ticketing/moduleSrc/ORM/Tickets.h | 5 +- Modules/Ticketing/moduleSrc/ORM/Units.cpp | 78 +++++++++++++++++++ Modules/Ticketing/moduleSrc/ORM/Units.h | 62 +++++++++++++++ Modules/Ticketing/moduleSrc/Ticketing.cpp | 14 +++- Modules/Ticketing/moduleSrc/Ticketing.h | 4 +- Modules/Ticketing/moduleSrc/moduleSrc.pro | 4 + conf/api.conf | 2 +- 10 files changed, 310 insertions(+), 6 deletions(-) create mode 100644 Modules/Ticketing/moduleSrc/ORM/Departments.cpp create mode 100644 Modules/Ticketing/moduleSrc/ORM/Departments.h create mode 100644 Modules/Ticketing/moduleSrc/ORM/Units.cpp create mode 100644 Modules/Ticketing/moduleSrc/ORM/Units.h diff --git a/Modules/Ticketing/moduleSrc/ORM/Departments.cpp b/Modules/Ticketing/moduleSrc/ORM/Departments.cpp new file mode 100644 index 00000000..0aed15d6 --- /dev/null +++ b/Modules/Ticketing/moduleSrc/ORM/Departments.cpp @@ -0,0 +1,75 @@ +/****************************************************************************** +# TargomanAPI: REST API for Targoman +# +# Copyright 2014-2020 by Targoman Intelligent Processing +# +# TargomanAPI 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. +# +# TargomanAPI 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 Targoman. If not, see . + ******************************************************************************/ +/** + * @author S. Mehran M. Ziabary + * @author Kambiz Zandi + */ + +#include "Departments.h" +#include "Interfaces/AAA/AAA.hpp" +#include "Defs.hpp" + +using namespace Targoman::API::ORM; + +namespace Targoman::API::TicketingModule::ORM { + +/******************************************************************************/ +/******************************************************************************/ +/******************************************************************************/ +Departments::Departments() : + intfSQLBasedModule( + TicketingSchema, + tblDepartments::Name, + {///< ColName Type Validation Default UpBy Sort Filter Self Virt PK + { tblDepartments::depID, ORM_PRIMARYKEY_32 }, + { tblDepartments::depName, S(QString), QFV, QRequired, UPNone }, + { tblDepartments::depCreationDateTime, ORM_CREATED_ON }, + { tblDepartments::depCreatedBy_usrID, ORM_CREATED_BY }, + }, + {///< Col Reference Table ForeignCol Rename LeftJoin + ORM_RELATION_OF_CREATOR(tblDepartments::depCreatedBy_usrID), + } + ) +{} + +QVariant Departments::apiGET(GET_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_GET, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->Select(*this, GET_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +quint32 Departments::apiCREATE(CREATE_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_PUT, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->Create(*this, CREATE_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +bool Departments::apiUPDATE(UPDATE_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_PATCH, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->Update(*this, UPDATE_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +bool Departments::apiDELETE(DELETE_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_DELETE, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->DeleteByPks(*this, DELETE_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +} //namespace Targoman::API::TicketingModule::ORM diff --git a/Modules/Ticketing/moduleSrc/ORM/Departments.h b/Modules/Ticketing/moduleSrc/ORM/Departments.h new file mode 100644 index 00000000..21f4a481 --- /dev/null +++ b/Modules/Ticketing/moduleSrc/ORM/Departments.h @@ -0,0 +1,61 @@ +/****************************************************************************** +# TargomanAPI: REST API for Targoman +# +# Copyright 2014-2020 by Targoman Intelligent Processing +# +# TargomanAPI 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. +# +# TargomanAPI 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 Targoman. If not, see . + ******************************************************************************/ +/** + * @author S. Mehran M. Ziabary + * @author Kambiz Zandi + */ + +#ifndef TARGOMAN_API_MODULES_TICKETING_ORM_DEPARTMENTS_H +#define TARGOMAN_API_MODULES_TICKETING_ORM_DEPARTMENTS_H + +#include "Interfaces/API/intfSQLBasedModule.h" + +namespace Targoman::API::TicketingModule { + +//structures and enumes goes here + +namespace ORM { + +#pragma GCC diagnostic push +#pragma GCC diagnostic ignored "-Wunused-variable" +namespace tblDepartments { + constexpr char Name[] = "tblDepartments"; + TARGOMAN_CREATE_CONSTEXPR(depID); + TARGOMAN_CREATE_CONSTEXPR(depName); + TARGOMAN_CREATE_CONSTEXPR(depCreationDateTime); + TARGOMAN_CREATE_CONSTEXPR(depCreatedBy_usrID); +} +#pragma GCC diagnostic pop + +class Departments : public intfSQLBasedModule +{ + Q_OBJECT + TARGOMAN_DEFINE_API_SUBMODULE(Ticketing, Departments) + +private slots: + QVariant ORMGET("Get departments") + quint32 ORMCREATE("Create a new department") + bool ORMUPDATE("Update department") + bool ORMDELETE("Delete a department") +}; + +} //namespace ORM +} //namespace Targoman::API::TicketingModule + +#endif // TARGOMAN_API_MODULES_TICKETING_ORM_DEPARTMENTS_H diff --git a/Modules/Ticketing/moduleSrc/ORM/Tickets.cpp b/Modules/Ticketing/moduleSrc/ORM/Tickets.cpp index f776b153..0baf3808 100644 --- a/Modules/Ticketing/moduleSrc/ORM/Tickets.cpp +++ b/Modules/Ticketing/moduleSrc/ORM/Tickets.cpp @@ -25,6 +25,8 @@ #include "Interfaces/AAA/AAA.hpp" #include "Defs.hpp" //#include "Interfaces/ORM/APIQueryBuilders.h" +#include "Units.h" +#include "Departments.h" TAPI_REGISTER_TARGOMAN_ENUM(Targoman::API::TicketingModule, enuTicketStatus); @@ -72,6 +74,7 @@ Tickets::Tickets() : { tblTickets::tktID, ORM_PRIMARYKEY_64 }, { tblTickets::tktTarget_usrID, S(NULLABLE_TYPE(quint64)), QFV.integer().minValue(1), QNull, UPNone }, { tblTickets::tkt_svcID, S(NULLABLE_TYPE(quint32)), QFV.integer().minValue(1), QNull, UPNone }, + { tblTickets::tkt_untID, S(NULLABLE_TYPE(quint32)), QFV.integer().minValue(1), QNull, UPNone }, { tblTickets::tktBase_tktID, S(NULLABLE_TYPE(quint64)), QFV.integer().minValue(1), QNull, UPNone }, { tblTickets::tktInReply_tktID, S(NULLABLE_TYPE(quint64)), QFV.integer().minValue(1), QNull, UPNone }, { tblTickets::tktType, S(Targoman::API::TicketingModule::enuTicketType::Type), QFV, Targoman::API::TicketingModule::enuTicketType::Message, UPNone }, @@ -86,6 +89,7 @@ Tickets::Tickets() : { tblTickets::tktInReply_tktID, R(TicketingSchema,tblTickets::Name), tblTickets::tktID, "InReply_" , true }, { tblTickets::tktTarget_usrID, R(AAASchema,tblUser::Name), tblUser::usrID, "Target_" , true }, { tblTickets::tktID, R(TicketingSchema,tblTicketRead::Name), tblTicketRead::tkr_tktID, "ReadInfo_", true }, + { tblTickets::tkt_untID, R(TicketingSchema, tblUnits::Name), tblUnits::untID }, ORM_RELATION_OF_CREATOR(tblTickets::tktCreatedBy_usrID), ORM_RELATION_OF_UPDATER(tblTickets::tktUpdatedBy_usrID), } @@ -130,6 +134,7 @@ QVariant Tickets::apiGET( .addCol(tblTickets::tktID) .addCol(tblTickets::tktTarget_usrID) .addCol(tblTickets::tkt_svcID) + .addCol(tblTickets::tkt_untID) .addCol(tblTickets::tktBase_tktID) .addCol(tblTickets::tktInReply_tktID) .addCol(tblTickets::tktType) @@ -140,6 +145,12 @@ QVariant Tickets::apiGET( .addCol(tblTickets::tktCreatedBy_usrID) .addCol(tblTickets::tktUpdatedBy_usrID) .addCol(CURRENT_TIMESTAMP) + + .leftJoin(tblUnits::Name) + .leftJoin(tblDepartments::Name, clsCondition(tblDepartments::Name, tblDepartments::depID, enuConditionOperator::Equal, tblUnits::Name, tblUnits::unt_depID)) + .addCol(tblUnits::untName) + .addCol(tblDepartments::depID) + .addCol(tblDepartments::depName) ; if (_inReplyTicketID > 0) diff --git a/Modules/Ticketing/moduleSrc/ORM/Tickets.h b/Modules/Ticketing/moduleSrc/ORM/Tickets.h index 77f3ea29..a7e5203f 100644 --- a/Modules/Ticketing/moduleSrc/ORM/Tickets.h +++ b/Modules/Ticketing/moduleSrc/ORM/Tickets.h @@ -50,6 +50,7 @@ namespace tblTickets { TARGOMAN_CREATE_CONSTEXPR(tktID); TARGOMAN_CREATE_CONSTEXPR(tktTarget_usrID); TARGOMAN_CREATE_CONSTEXPR(tkt_svcID); + TARGOMAN_CREATE_CONSTEXPR(tkt_untID); TARGOMAN_CREATE_CONSTEXPR(tktBase_tktID); TARGOMAN_CREATE_CONSTEXPR(tktInReply_tktID); TARGOMAN_CREATE_CONSTEXPR(tktType); @@ -82,8 +83,8 @@ private slots: // GET_METHOD_ARGS_HEADER_APICALL, TAPI::JWT_t _JWT, TAPI::PKsByPath_t _pksByPath = {}, - quint64 _offset = 0, - quint16 _limit = 10, + quint64 _pageIndex = 0, + quint16 _pageSize = 20, // TAPI::Cols_t _cols = {}, // TAPI::Filter_t _filters = {}, TAPI::OrderBy_t _orderBy = {}, diff --git a/Modules/Ticketing/moduleSrc/ORM/Units.cpp b/Modules/Ticketing/moduleSrc/ORM/Units.cpp new file mode 100644 index 00000000..400d231d --- /dev/null +++ b/Modules/Ticketing/moduleSrc/ORM/Units.cpp @@ -0,0 +1,78 @@ +/****************************************************************************** +# TargomanAPI: REST API for Targoman +# +# Copyright 2014-2020 by Targoman Intelligent Processing +# +# TargomanAPI 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. +# +# TargomanAPI 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 Targoman. If not, see . + ******************************************************************************/ +/** + * @author S. Mehran M. Ziabary + * @author Kambiz Zandi + */ + +#include "Units.h" +#include "Interfaces/AAA/AAA.hpp" +#include "Defs.hpp" +#include "Departments.h" + +using namespace Targoman::API::ORM; + +namespace Targoman::API::TicketingModule::ORM { + +/******************************************************************************/ +/******************************************************************************/ +/******************************************************************************/ +Units::Units() : + intfSQLBasedModule( + TicketingSchema, + tblUnits::Name, + {///< ColName Type Validation Default UpBy Sort Filter Self Virt PK + { tblUnits::untID, ORM_PRIMARYKEY_32 }, + { tblUnits::unt_depID, S(quint32), QFV.integer().minValue(1), QRequired, UPNone }, + { tblUnits::untName, S(QString), QFV, QRequired, UPNone }, + { tblUnits::untCreationDateTime, ORM_CREATED_ON }, + { tblUnits::untCreatedBy_usrID, ORM_CREATED_BY }, + }, + {///< Col Reference Table ForeignCol Rename LeftJoin + { tblUnits::unt_depID, R(TicketingSchema, tblDepartments::Name), tblDepartments::depID }, + ORM_RELATION_OF_CREATOR(tblUnits::untCreatedBy_usrID), + } + ) +{} + +QVariant Units::apiGET(GET_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_GET, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->Select(*this, GET_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +quint32 Units::apiCREATE(CREATE_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_PUT, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->Create(*this, CREATE_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +bool Units::apiUPDATE(UPDATE_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_PATCH, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->Update(*this, UPDATE_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +bool Units::apiDELETE(DELETE_METHOD_ARGS_IMPL_APICALL) +{ + Authorization::checkPriv(_JWT, this->privOn(EHTTP_DELETE, this->moduleBaseName())); + return /*Targoman::API::Query::*/this->DeleteByPks(*this, DELETE_METHOD_CALL_ARGS_INTERNAL_CALL); +} + +} //namespace Targoman::API::TicketingModule::ORM diff --git a/Modules/Ticketing/moduleSrc/ORM/Units.h b/Modules/Ticketing/moduleSrc/ORM/Units.h new file mode 100644 index 00000000..93199b8f --- /dev/null +++ b/Modules/Ticketing/moduleSrc/ORM/Units.h @@ -0,0 +1,62 @@ +/****************************************************************************** +# TargomanAPI: REST API for Targoman +# +# Copyright 2014-2020 by Targoman Intelligent Processing +# +# TargomanAPI 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. +# +# TargomanAPI 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 Targoman. If not, see . + ******************************************************************************/ +/** + * @author S. Mehran M. Ziabary + * @author Kambiz Zandi + */ + +#ifndef TARGOMAN_API_MODULES_TICKETING_ORM_UNITS_H +#define TARGOMAN_API_MODULES_TICKETING_ORM_UNITS_H + +#include "Interfaces/API/intfSQLBasedModule.h" + +namespace Targoman::API::TicketingModule { + +//structures and enumes goes here + +namespace ORM { + +#pragma GCC diagnostic push +#pragma GCC diagnostic ignored "-Wunused-variable" +namespace tblUnits { + constexpr char Name[] = "tblUnits"; + TARGOMAN_CREATE_CONSTEXPR(untID); + TARGOMAN_CREATE_CONSTEXPR(unt_depID); + TARGOMAN_CREATE_CONSTEXPR(untName); + TARGOMAN_CREATE_CONSTEXPR(untCreationDateTime); + TARGOMAN_CREATE_CONSTEXPR(untCreatedBy_usrID); +} +#pragma GCC diagnostic pop + +class Units : public intfSQLBasedModule +{ + Q_OBJECT + TARGOMAN_DEFINE_API_SUBMODULE(Ticketing, Units) + +private slots: + QVariant ORMGET("Get units") + quint32 ORMCREATE("Create a new unit") + bool ORMUPDATE("Update unit") + bool ORMDELETE("Delete a unit") +}; + +} //namespace ORM +} //namespace Targoman::API::TicketingModule + +#endif // TARGOMAN_API_MODULES_TICKETING_ORM_UNITS_H diff --git a/Modules/Ticketing/moduleSrc/Ticketing.cpp b/Modules/Ticketing/moduleSrc/Ticketing.cpp index a3b0d997..cfd19be3 100644 --- a/Modules/Ticketing/moduleSrc/Ticketing.cpp +++ b/Modules/Ticketing/moduleSrc/Ticketing.cpp @@ -24,6 +24,8 @@ #include "Ticketing.h" #include "libQFieldValidator/QFieldValidator.h" #include "ORM/Defs.hpp" +#include "ORM/Departments.h" +#include "ORM/Units.h" #include "ORM/Tickets.h" #include "ORM/TicketAttachments.h" #include "Interfaces/AAA/AAA.hpp" @@ -47,6 +49,8 @@ Ticketing::Ticketing() : TARGOMAN_API_IMPLEMENT_ACTIONLOG(Ticketing, TicketingSchema) TARGOMAN_API_IMPLEMENT_OBJECTSTORAGE(Ticketing, TicketingSchema) + this->addSubModule(&Departments::instance()); + this->addSubModule(&Units::instance()); this->addSubModule(&Tickets::instance()); this->addSubModule(&TicketRead::instance()); this->addSubModule(&TicketAttachments::instance()); @@ -60,7 +64,8 @@ quint64 Ticketing::insertTicket( enuTicketType::Type _ticketType, const QString &_title, const QString &_body, - const TAPI::Files_t &_files + const TAPI::Files_t &_files, + NULLABLE_TYPE(quint32) _unitID ) { TAPI::ORMFields_t CreateFields({ @@ -78,6 +83,9 @@ quint64 Ticketing::insertTicket( if (_inReplyTicketID > 0) CreateFields.insert(tblTickets::tktInReply_tktID, _inReplyTicketID); + if (NULLABLE_HAS_VALUE(_unitID)) + CreateFields.insert(tblTickets::tkt_untID, NULLABLE_GET(_unitID)); + quint64 TicketID = this->Create(Tickets::instance(), _createdBy, CreateFields); if (_files.isEmpty() == false) @@ -122,6 +130,7 @@ QVariantMap Ticketing::apiPUTnewMessage( const QString &_body, quint32 _serviceID, quint64 _targetUserID, + NULLABLE_TYPE(quint32) _unitID, const TAPI::stuFileInfo &_file ) { @@ -138,7 +147,8 @@ QVariantMap Ticketing::apiPUTnewMessage( _targetUserID ? enuTicketType::Message : enuTicketType::Broadcast, _title, _body, - Files + Files, + _unitID ); return QVariantMap({ diff --git a/Modules/Ticketing/moduleSrc/Ticketing.h b/Modules/Ticketing/moduleSrc/Ticketing.h index 8ca79815..2f59307a 100644 --- a/Modules/Ticketing/moduleSrc/Ticketing.h +++ b/Modules/Ticketing/moduleSrc/Ticketing.h @@ -55,7 +55,8 @@ class Ticketing : public intfSQLBasedWithActionLogsModule enuTicketType::Type _ticketType, const QString &_title, const QString &_body, - const TAPI::Files_t &_files = {} + const TAPI::Files_t &_files = {}, + NULLABLE_TYPE(quint32) _unitID = NULLABLE_NULL_VALUE ); private slots: @@ -67,6 +68,7 @@ private slots: const QString &_body, quint32 _serviceID, quint64 _targetUserID = 0, + NULLABLE_TYPE(quint32) _unitID = NULLABLE_NULL_VALUE, const TAPI::stuFileInfo &_file = {} ), "create new message targeting a user or all users (if target user is 0)" diff --git a/Modules/Ticketing/moduleSrc/moduleSrc.pro b/Modules/Ticketing/moduleSrc/moduleSrc.pro index ecc1695e..1cf34111 100644 --- a/Modules/Ticketing/moduleSrc/moduleSrc.pro +++ b/Modules/Ticketing/moduleSrc/moduleSrc.pro @@ -9,12 +9,16 @@ ModuleName=Ticketing HEADERS += \ + ORM/Departments.h \ + ORM/Units.h \ Ticketing.h \ ORM/TicketAttachments.h \ ORM/Tickets.h \ ORM/Defs.hpp \ SOURCES += \ + ORM/Departments.cpp \ + ORM/Units.cpp \ Ticketing.cpp \ ORM/TicketAttachments.cpp \ ORM/Tickets.cpp \ diff --git a/conf/api.conf b/conf/api.conf index 522eb2d2..9e14fe84 100644 --- a/conf/api.conf +++ b/conf/api.conf @@ -198,7 +198,7 @@ WarningLevel = 9 # Valid values: 0 to 65535 # Default value: 300 #--------------------------------------- -;TTL = 300 +TTL = 36000 #%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% # @@@ Logger SECTION @@@ From 049de342ab12ce084adf1ad53d3abb2e32af0ae3 Mon Sep 17 00:00:00 2001 From: kambizzandi Date: Fri, 1 Apr 2022 20:09:24 +0430 Subject: [PATCH 2/2] migration for create ticketing department and unit --- ...eting_create_table_department_and_unit.sql | 33 +++++++++++++++++++ 1 file changed, 33 insertions(+) create mode 100644 Modules/Ticketing/migrations/db/m20220401_200609_Ticketing_create_table_department_and_unit.sql diff --git a/Modules/Ticketing/migrations/db/m20220401_200609_Ticketing_create_table_department_and_unit.sql b/Modules/Ticketing/migrations/db/m20220401_200609_Ticketing_create_table_department_and_unit.sql new file mode 100644 index 00000000..6503ada6 --- /dev/null +++ b/Modules/Ticketing/migrations/db/m20220401_200609_Ticketing_create_table_department_and_unit.sql @@ -0,0 +1,33 @@ +/* Migration File: m20220401_200609_Ticketing_create_table_department_and_unit.sql */ + +CREATE TABLE `tblDepartments` ( + `depID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `depName` VARCHAR(64) NOT NULL COLLATE 'utf8mb4_general_ci', + `depCreationDateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, + `depCreatedBy_usrID` BIGINT(20) UNSIGNED NULL DEFAULT NULL, + PRIMARY KEY (`depID`) USING BTREE +) +COLLATE='utf8mb4_general_ci' +ENGINE=InnoDB +; + +CREATE TABLE `tblUnits` ( + `untID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `unt_depID` INT(10) UNSIGNED NOT NULL, + `untName` VARCHAR(64) NOT NULL COLLATE 'utf8mb4_general_ci', + `untCreationDateTime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, + `untCreatedBy_usrID` BIGINT(20) UNSIGNED NOT NULL, + PRIMARY KEY (`untID`) USING BTREE, + INDEX `FK_tblUnits_tblDepartments` (`unt_depID`) USING BTREE, + CONSTRAINT `FK_tblUnits_tblDepartments` FOREIGN KEY (`unt_depID`) REFERENCES `dev_Ticketing`.`tblDepartments` (`depID`) ON UPDATE NO ACTION ON DELETE NO ACTION +) +COLLATE='utf8mb4_general_ci' +ENGINE=InnoDB +; + +ALTER TABLE `tblTickets` + ADD COLUMN `tkt_untID` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `tkt_svcID`; + +ALTER TABLE `tblTickets` + ADD CONSTRAINT `FK_tblTickets_tblUnits` FOREIGN KEY (`tkt_untID`) REFERENCES `tblUnits` (`untID`) ON UPDATE NO ACTION ON DELETE NO ACTION; +