/*==============================================================*/ /* DBMS name: MySQL 4.11 */ /* Created on: 13/07/2005 09:07:06 p.m. */ /*==============================================================*/ alter table accessProfileRoles drop foreign key FK_accessProfile1; alter table accessProfileRoles drop foreign key FK_systemModule1; alter table capital drop foreign key FK_currency11; alter table capital drop foreign key FK_project16; alter table capitalMovement drop foreign key FK_capitalMovementPayback1; alter table capitalMovement drop foreign key FK_capitalMovementType1; alter table capitalMovement drop foreign key FK_capitalParticipant1; alter table capitalMovement drop foreign key FK_capitalParticipant2; alter table capitalMovement drop foreign key FK_currency12; alter table capitalMovement drop foreign key FK_employee17; alter table capitalMovementPayback drop foreign key FK_capitalMovement1; alter table capitalMovementPayback drop foreign key FK_capitalMovementType2; alter table capitalParticipant drop foreign key FK_capital1; alter table company drop foreign key FK_serverFile1; alter table companyData drop foreign key FK_company3; alter table companyDepartment drop foreign key FK_company1; alter table companyDepartment drop foreign key FK_serverFile6; alter table companyPosition drop foreign key FK_company4; alter table companyPosition drop foreign key FK_companyDepartment2; alter table companyPosition drop foreign key FK_serverFile5; alter table currencyChangeRate drop foreign key FK_currency3; alter table currencyChangeRate drop foreign key FK_currency4; alter table currencyChangeRate drop foreign key FK_employee8; alter table employee drop foreign key FK_infEmployeeType; alter table employeeAccessProfile drop foreign key FK_accessProfile2; alter table employeeAccessProfile drop foreign key FK_employee3; alter table employeeAccessProfile drop foreign key FK_project7; alter table employeeCompany drop foreign key FK_company2; alter table employeeCompany drop foreign key FK_companyDepartment1; alter table employeeCompany drop foreign key FK_companyPosition1; alter table employeeCompany drop foreign key FK_employee5; alter table employeeData drop foreign key FK_employee1; alter table employeeUser drop foreign key FK_employee2; alter table hys_salesUnit1 drop foreign key FK_hys_salesUnit1; alter table paymentRequest drop foreign key FK_company5; alter table paymentRequest drop foreign key FK_currency8; alter table paymentRequest drop foreign key FK_employee13; alter table paymentRequest drop foreign key FK_paymentRequestState1; alter table paymentRequest drop foreign key FK_paymentType1; alter table paymentRequest drop foreign key FK_project12; alter table paymentRequest drop foreign key FK_provider2; alter table paymentRequest drop foreign key FK_serverFile4; alter table paymentRequestDetails drop foreign key FK_paymentRequest1; alter table paymentRequestDetails drop foreign key FK_projectActivity4; alter table paymentRequestDetails drop foreign key FK_projectParcel2; alter table paymentRequestDetails drop foreign key FK_projectSubParcel1; alter table paymentRequestDetails drop foreign key FK_projectTask3; alter table paymentRequestStateHistory drop foreign key FK_paymentRequest2; alter table paymentRequestStateHistory drop foreign key FK_paymentRequestState2; alter table paymentRequestStateHistory drop foreign key Fk_employee14; alter table project drop foreign key FK_currency1; alter table project drop foreign key FK_salesPaymentPlan2; alter table project drop foreign key FK_serverFile2; alter table project drop foreign key FK_serverFile8; alter table projectActivity drop foreign key FK_currency5; alter table projectActivity drop foreign key FK_employee9; alter table projectActivity drop foreign key FK_project8; alter table projectActivityHistory drop foreign key FK_currency13; alter table projectActivityHistory drop foreign key FK_employee21; alter table projectActivityHistory drop foreign key FK_employee22; alter table projectActivityHistory drop foreign key FK_project18; alter table projectActivityHistory drop foreign key FK_projectActivity5; alter table projectInfo drop foreign key FK_project2; alter table projectParcel drop foreign key FK_currency7; alter table projectParcel drop foreign key FK_employee11; alter table projectParcel drop foreign key FK_project10; alter table projectParcel drop foreign key FK_projectActivity2; alter table projectParcel drop foreign key FK_projectTask1; alter table projectParcelHistory drop foreign key FK_currency15; alter table projectParcelHistory drop foreign key FK_employee25; alter table projectParcelHistory drop foreign key FK_employee26; alter table projectParcelHistory drop foreign key FK_project20; alter table projectParcelHistory drop foreign key FK_projectActivity7; alter table projectParcelHistory drop foreign key FK_projectParcel3; alter table projectParcelHistory drop foreign key FK_projectTask5; alter table projectPhoto drop foreign key FK_project1; alter table projectPhoto drop foreign key FK_serverFile3; alter table projectProvider drop foreign key FK_project22; alter table projectProvider drop foreign key FK_provider3; alter table projectReportComment drop foreign key FK_employee28; alter table projectReportComment drop foreign key FK_project23; alter table projectReportComment drop foreign key FK_projectReportCommentType1; alter table projectRoles drop foreign key FK_project17; alter table projectSubParcel drop foreign key FK_currency9; alter table projectSubParcel drop foreign key FK_employee12; alter table projectSubParcel drop foreign key FK_project11; alter table projectSubParcel drop foreign key FK_projectActivity3; alter table projectSubParcel drop foreign key FK_projectParcel1; alter table projectSubParcel drop foreign key FK_projectTask2; alter table projectSubParcelHistory drop foreign key FK_currency16; alter table projectSubParcelHistory drop foreign key FK_employee27; alter table projectSubParcelHistory drop foreign key FK_project21; alter table projectSubParcelHistory drop foreign key FK_projectActivity8; alter table projectSubParcelHistory drop foreign key FK_projectParcel4; alter table projectSubParcelHistory drop foreign key FK_projectSubParcel2; alter table projectSubParcelHistory drop foreign key FK_projectTask6; alter table projectSubParcelHistory drop foreign key FK_PROJECTSUB_REFE_EMPLOYEE; alter table projectTask drop foreign key FK_currency6; alter table projectTask drop foreign key FK_employee10; alter table projectTask drop foreign key FK_project9; alter table projectTask drop foreign key FK_projectActivity1; alter table projectTaskHelper drop foreign key FK_projectActivityHelper1; alter table projectTaskHistory drop foreign key FK_currency14; alter table projectTaskHistory drop foreign key FK_employee23; alter table projectTaskHistory drop foreign key FK_employee24; alter table projectTaskHistory drop foreign key FK_project19; alter table projectTaskHistory drop foreign key FK_projectActivity6; alter table projectTaskHistory drop foreign key FK_projectTask4; alter table providerData drop foreign key FK_provider1; alter table repositoryDocument drop foreign key FK_repositoryFolder1; alter table repositoryDocument drop foreign key FK_serverFile7; alter table repositoryFolder drop foreign key FK_project15; alter table salesClient drop foreign key FK_employee6; alter table salesClientData drop foreign key FK_salesClient1; alter table salesClientData drop foreign key FK_salesContact2; alter table salesClientData drop foreign key FK_salesContactMedia2; alter table salesClientUnit drop foreign key FK_currency10; alter table salesClientUnit drop foreign key FK_employee7; alter table salesClientUnit drop foreign key FK_project13; alter table salesClientUnit drop foreign key FK_salesClient2; alter table salesClientUnit drop foreign key FK_salesClientState1; alter table salesClientUnit drop foreign key FK_salesContact1; alter table salesClientUnit drop foreign key FK_salesContactMedia1; alter table salesClientUnit drop foreign key FK_salesPaymentPlan1; alter table salesClientUnit drop foreign key FK_salesUnit1; alter table salesClientUnitPaymentSchema drop foreign key FK_salesClientUnit2; alter table salesClientUnitReject drop foreign key FK_salesClient3; alter table salesClientUnitReject drop foreign key FK_salesClientUnit1; alter table salesClientUnitReject drop foreign key FK_salesRejectOption1; alter table salesContact drop foreign key FK_project5; alter table salesContactMedia drop foreign key FK_project6; alter table salesPayment drop foreign key FK_employee16; alter table salesPayment drop foreign key FK_salesClientUnit3; alter table salesPayment drop foreign key FK_salesPaymentType; alter table salesPaymentPlan drop foreign key FK_project4; alter table salesUnit drop foreign key FK_currency2; alter table salesUnit drop foreign key FK_project3; alter table salesUnit drop foreign key FK_projectPhoto1; alter table salesUnit drop foreign key FK_salesUnitState1; alter table salesUnit drop foreign key FK_salesUnitType1; alter table sellerClientProject drop foreign key FK_employee15; alter table sellerClientProject drop foreign key FK_project14; alter table sellerClientProject drop foreign key FK_salesClient4; alter table serverFile drop foreign key FK_employee4; alter table serverFile drop foreign key FK_serverFileType1; drop index AP_name on accessProfile; drop index AP_unique on accessProfile; drop index APR_idAccessProfile on accessProfileRoles; drop index APR_idSystemModule on accessProfileRoles; drop index APR_roles on accessProfileRoles; drop index APR_unique on accessProfileRoles; drop index capital_unique on capital; drop index COMP_unique on company; drop index COMPD_unique on companyData; drop index COMPDEP_unique on companyDepartment; drop index companyPosition_unique on companyPosition; drop index CURR_name on currency; drop index CURCHRT_unique on currencyChangeRate; drop index EMP_idEmployeeType on employee; drop index EMP_unique on employee; drop index EAP_idAccessProfile on employeeAccessProfile; drop index EAP_idEmployee on employeeAccessProfile; drop index EAP_unique on employeeAccessProfile; drop index EMPLCOMP_unique on employeeCompany; drop index ED_idEmployee on employeeData; drop index EMPT_unique on employeeType; drop index EU_idEmployee on employeeUser; drop index EU_unique on employeeUser; drop index PAYREQ_unique on paymentRequest; drop index PAYRD_idPaymentRequest on paymentRequestDetails; drop index PAYRD_idProjectActivity on paymentRequestDetails; drop index PAYRD_idProjectParcel on paymentRequestDetails; drop index PAYRD_idProjectSubParcel on paymentRequestDetails; drop index PAYRD_idProjectTask on paymentRequestDetails; drop index PAYRD_unique on paymentRequestDetails; drop index PRQST_unique on paymentRequestState; drop index paymentRequestStateHistory_unique on paymentRequestStateHistory; drop index PRJACT_unique on projectActivity; drop index PRJPRCL_unique on projectParcel; drop index projectProvider_unique on projectProvider; drop index projectRoles_unique on projectRoles; drop index PRJSUBPRCL_unique on projectSubParcel; drop index PRJTSK_unique on projectTask; drop index PROV_unique on provider; drop index PROVD_unique on providerData; drop index repositoryFolder_unique on repositoryFolder; drop index SLS_CS_name on salesClientState; drop index salesClientUnitPaymentSchema_unique on salesClientUnitPaymentSchema; drop index sellerClientProject_unique on sellerClientProject; drop index SERV_FL_idEmployee on serverFile; drop index SERV_FL_idServerFileType on serverFile; drop index SERV_FL_unique on serverFile; drop index SMOD_unique on systemModule; drop table if exists accessProfile; drop table if exists accessProfileRoles; drop table if exists capital; drop table if exists capitalMovement; drop table if exists capitalMovementHistory; drop table if exists capitalMovementPayback; drop table if exists capitalMovementType; drop table if exists capitalParticipant; drop table if exists company; drop table if exists companyData; drop table if exists companyDepartment; drop table if exists companyPosition; drop table if exists currency; drop table if exists currencyChangeRate; drop table if exists employee; drop table if exists employeeAccessProfile; drop table if exists employeeCompany; drop table if exists employeeData; drop table if exists employeeType; drop table if exists employeeUser; drop table if exists hys_salesUnit1; drop table if exists paymentRequest; drop table if exists paymentRequestDetails; drop table if exists paymentRequestState; drop table if exists paymentRequestStateHistory; drop table if exists paymentType; drop table if exists project; drop table if exists projectActivity; drop table if exists projectActivityHelper; drop table if exists projectActivityHistory; drop table if exists projectInfo; drop table if exists projectParcel; drop table if exists projectParcelHistory; drop table if exists projectPhoto; drop table if exists projectProvider; drop table if exists projectReportComment; drop table if exists projectReportCommentType; drop table if exists projectRoles; drop table if exists projectSubParcel; drop table if exists projectSubParcelHistory; drop table if exists projectTask; drop table if exists projectTaskHelper; drop table if exists projectTaskHistory; drop table if exists provider; drop table if exists providerData; drop table if exists repositoryDocument; drop table if exists repositoryFolder; drop table if exists salesClient; drop table if exists salesClientData; drop table if exists salesClientState; drop table if exists salesClientUnit; drop table if exists salesClientUnitPaymentSchema; drop table if exists salesClientUnitReject; drop table if exists salesContact; drop table if exists salesContactMedia; drop table if exists salesPayment; drop table if exists salesPaymentPlan; drop table if exists salesPaymentType; drop table if exists salesRejectOption; drop table if exists salesUnit; drop table if exists salesUnitState; drop table if exists salesUnitType; drop table if exists sellerClientProject; drop table if exists serverFile; drop table if exists serverFileType; drop table if exists systemModule; /*==============================================================*/ /* Table: accessProfile */ /*==============================================================*/ create table accessProfile ( idAccessProfile int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idAccessProfile) ) type = InnoDB; INSERT INTO accessProfile VALUES("1", "Administrador (i-pro)"); INSERT INTO accessProfile VALUES("20", "Direccion"); INSERT INTO accessProfile VALUES("21", "Direccion general"); INSERT INTO accessProfile VALUES("22", "Direccion de arquitectura"); INSERT INTO accessProfile VALUES("23", "Direccion de finanzas"); INSERT INTO accessProfile VALUES("24", "Direccion de operaciones"); INSERT INTO accessProfile VALUES("30", "Contador"); INSERT INTO accessProfile VALUES("40", "Gerente de proyecto"); INSERT INTO accessProfile VALUES("50", "Gerente de ventas"); INSERT INTO accessProfile VALUES("60", "Cobranza"); INSERT INTO accessProfile VALUES("70", "Administrativo A"); INSERT INTO accessProfile VALUES("80", "Administrativo B"); INSERT INTO accessProfile VALUES("81", "Recursos humanos"); INSERT INTO accessProfile VALUES("90", "Ejecutivo de ventas"); /*==============================================================*/ /* Index: AP_unique */ /*==============================================================*/ create unique index AP_unique on accessProfile ( name ); /*==============================================================*/ /* Index: AP_name */ /*==============================================================*/ create index AP_name on accessProfile ( name, idAccessProfile ); /*==============================================================*/ /* Table: accessProfileRoles */ /*==============================================================*/ create table accessProfileRoles ( idAccessProfile int not null, idSystemModule int not null, roles varchar(255) not null ) type = InnoDB; INSERT INTO accessProfileRoles VALUES("1", "1000", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1100", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1200", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1300", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1400", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1900", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1910", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1920", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1930", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1940", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "1950", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "2000", "create,modify,delete,viewShort,viewFull"); #humanResources INSERT INTO accessProfileRoles VALUES("1", "2100", "create,modify,delete,viewShort,viewFull"); #humanResources/employee INSERT INTO accessProfileRoles VALUES("1", "2110", "create,modify,delete,viewShort,viewFull"); #humanResources/employeeData INSERT INTO accessProfileRoles VALUES("1", "2120", "create,modify,delete,viewShort,viewFull"); #humanResources/employeeUser INSERT INTO accessProfileRoles VALUES("1", "2130", "create,modify,delete,viewShort,viewFull"); #humanResources/employeeAccessProfile INSERT INTO accessProfileRoles VALUES("1", "2140", "create,modify,delete,viewShort,viewFull"); #humanResources/employeeCompany INSERT INTO accessProfileRoles VALUES("1", "2200", "create,modify,delete,viewShort,viewFull"); #humanResources/company INSERT INTO accessProfileRoles VALUES("1", "2210", "create,modify,delete,viewShort,viewFull"); #humanResources/companyData INSERT INTO accessProfileRoles VALUES("1", "2300", "create,modify,delete,viewShort,viewFull"); #humanResources/companyDepartment INSERT INTO accessProfileRoles VALUES("1", "2400", "create,modify,delete,viewShort,viewFull"); #humanResources/companyPosition INSERT INTO accessProfileRoles VALUES("1", "3000", "create,modify,delete,viewShort,viewFull"); #administration INSERT INTO accessProfileRoles VALUES("1", "3100", "create,modify,delete,viewShort,viewFull"); #administration/project INSERT INTO accessProfileRoles VALUES("1", "3110", "create,modify,delete,viewShort,viewFull"); #administration/projectInfo INSERT INTO accessProfileRoles VALUES("1", "3115", "create,modify,delete,viewShort,viewFull"); #administration/projectRoles INSERT INTO accessProfileRoles VALUES("1", "3120", "create,modify,delete,viewShort,viewFull"); #administration/projectPhoto INSERT INTO accessProfileRoles VALUES("1", "3200", "create,modify,delete,viewShort,viewFull"); #administration/projectActivity INSERT INTO accessProfileRoles VALUES("1", "3300", "create,modify,delete,viewShort,viewFull"); #administration/projectTask INSERT INTO accessProfileRoles VALUES("1", "3400", "create,modify,delete,viewShort,viewFull"); #administration/projectParcel INSERT INTO accessProfileRoles VALUES("1", "3500", "create,modify,delete,viewShort,viewFull"); #administration/projectSubParcel INSERT INTO accessProfileRoles VALUES("1", "3600", "create,modify,delete,viewShort,viewFull"); #administration/paymentRequest INSERT INTO accessProfileRoles VALUES("1", "3610", "create,modify,delete,viewShort,viewFull"); #administration/paymentRequestDetails INSERT INTO accessProfileRoles VALUES("1", "3620", "create,modify,delete,viewShort,viewFull"); #administration/paymentRequestStateHistory INSERT INTO accessProfileRoles VALUES("1", "3700", "create,modify,delete,viewShort,viewFull"); #administration/provider INSERT INTO accessProfileRoles VALUES("1", "3710", "create,modify,delete,viewShort,viewFull"); #administration/providerData INSERT INTO accessProfileRoles VALUES("1", "4000", "create,modify,delete,viewShort,viewFull"); #financial INSERT INTO accessProfileRoles VALUES("1", "4100", "create,modify,delete,viewShort,viewFull"); #financial/capital INSERT INTO accessProfileRoles VALUES("1", "4200", "create,modify,delete,viewShort,viewFull"); #financial/capitalMovement INSERT INTO accessProfileRoles VALUES("1", "4210", "create,modify,delete,viewShort,viewFull"); #financial/capitalMovementPayback INSERT INTO accessProfileRoles VALUES("1", "4900", "create,modify,delete,viewShort,viewFull"); #financial/capitalParticipant INSERT INTO accessProfileRoles VALUES("1", "4910", "create,modify,delete,viewShort,viewFull"); #financial/capitalMovementType INSERT INTO accessProfileRoles VALUES("1", "7000", "create,modify,delete,viewShort,viewFull"); #repository INSERT INTO accessProfileRoles VALUES("1", "7100", "create,modify,delete,viewShort,viewFull"); #repository/repositoryDocument INSERT INTO accessProfileRoles VALUES("1", "7200", "create,modify,delete,viewShort,viewFull"); #repository/repositoryFolder INSERT INTO accessProfileRoles VALUES("1", "8000", "viewShort"); #reports INSERT INTO accessProfileRoles VALUES("1", "8100", "viewShort"); #reports/reportProyectStatus INSERT INTO accessProfileRoles VALUES("1", "9000", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "9100", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "9110", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "8200", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "9300", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "9400", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "9500", "create,modify,delete,viewShort,viewFull"); INSERT INTO accessProfileRoles VALUES("1", "9510", "create,modify,delete,viewShort,viewFull"); /*==============================================================*/ /* Index: APR_unique */ /*==============================================================*/ create unique index APR_unique on accessProfileRoles ( idAccessProfile, idSystemModule ); /*==============================================================*/ /* Index: APR_idAccessProfile */ /*==============================================================*/ create index APR_idAccessProfile on accessProfileRoles ( idAccessProfile ); /*==============================================================*/ /* Index: APR_idSystemModule */ /*==============================================================*/ create index APR_idSystemModule on accessProfileRoles ( idSystemModule ); /*==============================================================*/ /* Index: APR_roles */ /*==============================================================*/ create index APR_roles on accessProfileRoles ( roles ); /*==============================================================*/ /* Table: capital */ /*==============================================================*/ create table capital ( idCapital int not null AUTO_INCREMENT, idProject int, idCurrency int not null, name varchar(255) not null, amount double not null, amountIn double not null, amountInSales double not null, amountOut double not null, amountOutProject double not null, primary key (idCapital) ) type = InnoDB; /*==============================================================*/ /* Index: capital_unique */ /*==============================================================*/ create unique index capital_unique on capital ( idProject, name ); /*==============================================================*/ /* Table: capitalMovement */ /*==============================================================*/ create table capitalMovement ( idCapitalMovement int not null AUTO_INCREMENT, idCapitalParticipantFrom int not null, idCapitalParticipantTo int not null, idCurrency int not null, idEmployee int not null, idCapitalMovementType int not null, idCapitalMovementPayback int, dateRegistered datetime not null, amount double not null, description text not null, primary key (idCapitalMovement) ); /*==============================================================*/ /* Table: capitalMovementHistory */ /*==============================================================*/ create table capitalMovementHistory ( dateHistory datetime not null, idRegistrant int not null, idCapitalMovement int not null, idCapitalParticipantFrom int not null, idCapitalParticipantTo int not null, idCurrency int not null, idEmployee int not null, idCapitalMovementType int not null, idCapitalMovementPayback int, dateRegistered datetime not null, amount double not null, description text not null ); /*==============================================================*/ /* Table: capitalMovementPayback */ /*==============================================================*/ create table capitalMovementPayback ( idCapitalMovementPayback int not null AUTO_INCREMENT, idCapitalMovement int not null, idCapitalMovementType int not null, dateExpected date not null, amount double not null, description text not null, primary key (idCapitalMovementPayback) ) type = InnoDB; /*==============================================================*/ /* Table: capitalMovementType */ /*==============================================================*/ create table capitalMovementType ( idCapitalMovementType int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idCapitalMovementType) ) type = InnoDB; insert into capitalMovementType values (10,"Inversion"); insert into capitalMovementType values (11,"Recuperacion de inversion"); insert into capitalMovementType values (20,"Prestamo"); insert into capitalMovementType values (21,"Devolucion de prestamo"); insert into capitalMovementType values (30,"Utilidad"); insert into capitalMovementType values (40,"Pago de servicios"); insert into capitalMovementType values (50,"Comision de venta"); /*==============================================================*/ /* Table: capitalParticipant */ /*==============================================================*/ create table capitalParticipant ( idCapitalParticipant int not null AUTO_INCREMENT, idCapital int, name varchar(255) not null, accountNumber varchar(255) not null, contactName varchar(255) not null, phone varchar(255) not null, primary key (idCapitalParticipant) ) type = InnoDB; /*==============================================================*/ /* Table: company */ /*==============================================================*/ create table company ( idCompany int not null AUTO_INCREMENT, idServerFile int, name varchar(255) not null, primary key (idCompany) ) type = InnoDB; /*==============================================================*/ /* Index: COMP_unique */ /*==============================================================*/ create unique index COMP_unique on company ( name ); /*==============================================================*/ /* Table: companyData */ /*==============================================================*/ create table companyData ( idCompany int not null, documentNumber varchar(255), address varchar(255), address1 varchar(255), address2 varchar(255), city varchar(255), state varchar(255), postalCode varchar(255), phone1 varchar(255), phone2 varchar(255), phone3 varchar(255), email varchar(255) ) type = InnoDB; /*==============================================================*/ /* Index: COMPD_unique */ /*==============================================================*/ create unique index COMPD_unique on companyData ( idCompany ); /*==============================================================*/ /* Table: companyDepartment */ /*==============================================================*/ create table companyDepartment ( idCompanyDepartment int not null AUTO_INCREMENT, idCompany int not null, idServerFile int, name varchar(255) not null, primary key (idCompanyDepartment) ) type = InnoDB; /*==============================================================*/ /* Index: COMPDEP_unique */ /*==============================================================*/ create unique index COMPDEP_unique on companyDepartment ( name, idCompany ); /*==============================================================*/ /* Table: companyPosition */ /*==============================================================*/ create table companyPosition ( idCompanyPosition int not null AUTO_INCREMENT, idCompany int not null, idCompanyDepartment int not null, idServerFile int, name varchar(255) not null, primary key (idCompanyPosition) ) type = InnoDB; /*==============================================================*/ /* Index: companyPosition_unique */ /*==============================================================*/ create unique index companyPosition_unique on companyPosition ( idCompany, idCompanyDepartment, name ); /*==============================================================*/ /* Table: currency */ /*==============================================================*/ create table currency ( idCurrency int not null AUTO_INCREMENT, name varchar(64) not null, primary key (idCurrency) ) type = InnoDB; /*!40000 ALTER TABLE `currency` DISABLE KEYS */; LOCK TABLES `currency` WRITE; INSERT INTO `currency` VALUES (1,'Pesos'),(3,'UDI'),(2,'USD'); UNLOCK TABLES; /*!40000 ALTER TABLE `currency` ENABLE KEYS */; /*==============================================================*/ /* Index: CURR_name */ /*==============================================================*/ create index CURR_name on currency ( name, idCurrency ); /*==============================================================*/ /* Table: currencyChangeRate */ /*==============================================================*/ create table currencyChangeRate ( idCurrencyFrom int not null, idCurrencyTo int not null, idEmployee int, value double not null, dateRegistration datetime not null ) type = InnoDB; /*==============================================================*/ /* Index: CURCHRT_unique */ /*==============================================================*/ create unique index CURCHRT_unique on currencyChangeRate ( idCurrencyFrom, idCurrencyTo, dateRegistration ); /*==============================================================*/ /* Table: employee */ /*==============================================================*/ create table employee ( idEmployee int not null AUTO_INCREMENT, idEmployeeType int not null, nameFirst varchar(255) not null, nameFirst1 varchar(255) not null, nameLast varchar(255) not null, nameLast1 varchar(255) not null, documentNumber varchar(255) not null, dateRegistered date not null, dateBirthday date, primary key (idEmployee) ) type = InnoDB; INSERT INTO employee VALUES("1", "1", "Admin", "", "Admin", "", "YAVI771214FM9",now(),"1980/05/10"); /*==============================================================*/ /* Index: EMP_unique */ /*==============================================================*/ create unique index EMP_unique on employee ( nameFirst, nameFirst1, nameLast, nameLast1 ); /*==============================================================*/ /* Index: EMP_idEmployeeType */ /*==============================================================*/ create index EMP_idEmployeeType on employee ( idEmployeeType ); /*==============================================================*/ /* Table: employeeAccessProfile */ /*==============================================================*/ create table employeeAccessProfile ( idEmployee int not null, idAccessProfile int not null, idProject int ) type = InnoDB; INSERT INTO employeeAccessProfile VALUES("1", "1", NULL); /*==============================================================*/ /* Index: EAP_unique */ /*==============================================================*/ create unique index EAP_unique on employeeAccessProfile ( idEmployee, idProject ); /*==============================================================*/ /* Index: EAP_idEmployee */ /*==============================================================*/ create index EAP_idEmployee on employeeAccessProfile ( idEmployee ); /*==============================================================*/ /* Index: EAP_idAccessProfile */ /*==============================================================*/ create index EAP_idAccessProfile on employeeAccessProfile ( idAccessProfile ); /*==============================================================*/ /* Table: employeeCompany */ /*==============================================================*/ create table employeeCompany ( idEmployee int not null, idCompany int, idCompanyDepartment int, idCompanyPosition int ) type = InnoDB; /*==============================================================*/ /* Index: EMPLCOMP_unique */ /*==============================================================*/ create unique index EMPLCOMP_unique on employeeCompany ( idEmployee ); /*==============================================================*/ /* Table: employeeData */ /*==============================================================*/ create table employeeData ( idEmployee int not null, address varchar(255) not null, address1 varchar(255) not null, address2 varchar(255) not null, city varchar(255) not null, state varchar(255) not null, postalCode varchar(255) not null, phone1 varchar(255) not null, phone2 varchar(255) not null, phone3 varchar(255) not null, email varchar(255) not null ) type = InnoDB; /*==============================================================*/ /* Index: ED_idEmployee */ /*==============================================================*/ create unique index ED_idEmployee on employeeData ( idEmployee ); /*==============================================================*/ /* Table: employeeType */ /*==============================================================*/ create table employeeType ( idEmployeeType int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idEmployeeType) ) type = InnoDB; INSERT INTO employeeType (idEmployeeType, name) VALUES (1, 'Interno'); INSERT INTO employeeType (idEmployeeType, name) VALUES (2, 'Externo'); /*==============================================================*/ /* Index: EMPT_unique */ /*==============================================================*/ create unique index EMPT_unique on employeeType ( name ); /*==============================================================*/ /* Table: employeeUser */ /*==============================================================*/ create table employeeUser ( idEmployee int not null, username varchar(255) not null, password varchar(255) not null, roles varchar(255) not null ) type = InnoDB; INSERT INTO employeeUser VALUES("1", "admin", "admin", ""); /*==============================================================*/ /* Index: EU_unique */ /*==============================================================*/ create unique index EU_unique on employeeUser ( username ); /*==============================================================*/ /* Index: EU_idEmployee */ /*==============================================================*/ create unique index EU_idEmployee on employeeUser ( idEmployee ); /*==============================================================*/ /* Table: hys_salesUnit1 */ /*==============================================================*/ create table hys_salesUnit1 ( idSalesUnit int not null, recamaras varchar(255) not null, banos varchar(255) not null, estudio varchar(255) not null, dobleAltura varchar(255) not null, niveles varchar(255) not null, m2interiores double not null, m2patio double not null, m2terraza double not null, cajones varchar(255) not null, bodega varchar(255) not null, cuartoServicio varchar(255) not null, tvRoom varchar(255) not null, descripcion blob not null, primary key (idSalesUnit) ) type = InnoDB; /*==============================================================*/ /* Table: paymentRequest */ /*==============================================================*/ create table paymentRequest ( idPaymentRequest int not null AUTO_INCREMENT, idPaymentRequestState int not null, idEmployee int, idProject int not null, idProvider int not null, idServerFile int, idPaymentType int, idCurrency int, idCompany int, dateRegistered datetime not null, billNumber varchar(255) not null, billConcept varchar(255) not null, billDate date, amountNoTax double not null, amountTaxToPay double not null, amountTaxToHold1 double not null, amountTaxToHold2 double not null, amount double not null, paymentTypeInfo text, comment text, primary key (idPaymentRequest) ) type = InnoDB; /*==============================================================*/ /* Index: PAYREQ_unique */ /*==============================================================*/ create unique index PAYREQ_unique on paymentRequest ( idProject, idProvider, billNumber, billConcept ); /*==============================================================*/ /* Table: paymentRequestDetails */ /*==============================================================*/ create table paymentRequestDetails ( idPaymentRequestDetails int not null AUTO_INCREMENT, idPaymentRequest int not null, idProjectActivity int, idProjectTask int, idProjectParcel int, idProjectSubParcel int, amount double not null, primary key (idPaymentRequestDetails) ) type = InnoDB; /*==============================================================*/ /* Index: PAYRD_idPaymentRequest */ /*==============================================================*/ create index PAYRD_idPaymentRequest on paymentRequestDetails ( idPaymentRequest ); /*==============================================================*/ /* Index: PAYRD_idProjectActivity */ /*==============================================================*/ create index PAYRD_idProjectActivity on paymentRequestDetails ( idProjectActivity ); /*==============================================================*/ /* Index: PAYRD_idProjectTask */ /*==============================================================*/ create index PAYRD_idProjectTask on paymentRequestDetails ( idProjectTask ); /*==============================================================*/ /* Index: PAYRD_idProjectParcel */ /*==============================================================*/ create index PAYRD_idProjectParcel on paymentRequestDetails ( idProjectParcel ); /*==============================================================*/ /* Index: PAYRD_idProjectSubParcel */ /*==============================================================*/ create index PAYRD_idProjectSubParcel on paymentRequestDetails ( idProjectSubParcel ); /*==============================================================*/ /* Index: PAYRD_unique */ /*==============================================================*/ create unique index PAYRD_unique on paymentRequestDetails ( idPaymentRequest, idProjectActivity, idProjectTask, idProjectParcel, idProjectSubParcel ); /*==============================================================*/ /* Table: paymentRequestState */ /*==============================================================*/ create table paymentRequestState ( idPaymentRequestState int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idPaymentRequestState) ) type = InnoDB; insert into paymentRequestState values (0,"Creado"); /*==============================================================*/ /* Index: PRQST_unique */ /*==============================================================*/ create unique index PRQST_unique on paymentRequestState ( name ); /*==============================================================*/ /* Table: paymentRequestStateHistory */ /*==============================================================*/ create table paymentRequestStateHistory ( idPaymentRequestStateHistory int not null AUTO_INCREMENT, idPaymentRequest int not null, idPaymentRequestState int not null, idEmployee int, dateRegistered datetime not null, comment blob not null, primary key (idPaymentRequestStateHistory) ); /*==============================================================*/ /* Index: paymentRequestStateHistory_unique */ /*==============================================================*/ create unique index paymentRequestStateHistory_unique on paymentRequestStateHistory ( idPaymentRequest, idPaymentRequestState ); /*==============================================================*/ /* Table: paymentType */ /*==============================================================*/ create table paymentType ( idPaymentType int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idPaymentType) ) type = InnoDB; insert into paymentType values (1,"Cheque"); insert into paymentType values (2,"Transferencia bancaria"); insert into paymentType values (3,"Efectivo"); /*==============================================================*/ /* Table: project */ /*==============================================================*/ create table project ( idProject int not null AUTO_INCREMENT, idServerFile int, idServerFile1 int, idCurrency int, idSalesPaymentPlan int, name varchar(255) not null, primary key (idProject) ) type = InnoDB; /*==============================================================*/ /* Table: projectActivity */ /*==============================================================*/ create table projectActivity ( idProjectActivity int not null AUTO_INCREMENT, idParent int, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, primary key (idProjectActivity) ) type = InnoDB; /*==============================================================*/ /* Index: PRJACT_unique */ /*==============================================================*/ create unique index PRJACT_unique on projectActivity ( idProject, name ); /*==============================================================*/ /* Table: projectActivityHelper */ /*==============================================================*/ create table projectActivityHelper ( idProjectActivity int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idProjectActivity) ) type = InnoDB; # # Dumping data for table 'projectActivityHelper' # INSERT INTO projectActivityHelper VALUES("1", "01. Terreno"); INSERT INTO projectActivityHelper VALUES("2", "02. Estructuración"); INSERT INTO projectActivityHelper VALUES("3", "03. Licencias / Legales"); INSERT INTO projectActivityHelper VALUES("4", "04. Comision Venta"); INSERT INTO projectActivityHelper VALUES("5", "05. Gerencia de Proyecto"); INSERT INTO projectActivityHelper VALUES("6", "06. Proyecto Ejecutivo"); INSERT INTO projectActivityHelper VALUES("7", "07. Construcción"); INSERT INTO projectActivityHelper VALUES("8", "08. Supervisión"); INSERT INTO projectActivityHelper VALUES("9", "09. Credito Puente"); INSERT INTO projectActivityHelper VALUES("10", "10. Estudios / SP Luz / Factibilidad"); INSERT INTO projectActivityHelper VALUES("11", "11. Otros"); /*==============================================================*/ /* Table: projectActivityHistory */ /*==============================================================*/ create table projectActivityHistory ( dateRegistered datetime not null, idRegistrant int not null, idProjectActivity int not null, idParent int, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, comment blob ) type = InnoDB; /*==============================================================*/ /* Table: projectInfo */ /*==============================================================*/ create table projectInfo ( idProject int not null, phrase varchar(255) not null, address blob not null, siteURL varchar(255) not null, arquitect varchar(255) not null, descriptionUnits blob not null, descriptionGeneral blob not null, descriptionAccessory blob not null, descriptionAttributes blob not null, descriptionAdditional blob not null, dateStart date not null, dateEnd date not null ) type = InnoDB; /*==============================================================*/ /* Table: projectParcel */ /*==============================================================*/ create table projectParcel ( idProjectParcel int not null AUTO_INCREMENT, idParent int, idProjectTask int not null, idProjectActivity int not null, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, primary key (idProjectParcel) ) type = InnoDB; /*==============================================================*/ /* Index: PRJPRCL_unique */ /*==============================================================*/ create unique index PRJPRCL_unique on projectParcel ( idProjectTask, name ); /*==============================================================*/ /* Table: projectParcelHistory */ /*==============================================================*/ create table projectParcelHistory ( dateRegistered datetime not null, idRegistrant int not null, idProjectParcel int not null, idParent int, idProjectTask int not null, idProjectActivity int not null, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, comment blob ) type = InnoDB; /*==============================================================*/ /* Table: projectPhoto */ /*==============================================================*/ create table projectPhoto ( idProjectPhoto int not null AUTO_INCREMENT, idProject int not null, idServerFile int not null, primary key (idProjectPhoto) ) type = InnoDB; /*==============================================================*/ /* Table: projectProvider */ /*==============================================================*/ create table projectProvider ( idProject int not null, idProvider int not null ) type = InnoDB; /*==============================================================*/ /* Index: projectProvider_unique */ /*==============================================================*/ create unique index projectProvider_unique on projectProvider ( idProject, idProvider ); /*==============================================================*/ /* Table: projectReportComment */ /*==============================================================*/ create table projectReportComment ( idProject int not null, idProjectReportCommentType int not null, idEmployee int not null, dateRegistered datetime not null, isVisible char(1) not null default 'Y', notes text not null ); /*==============================================================*/ /* Table: projectReportCommentType */ /*==============================================================*/ create table projectReportCommentType ( idProjectReportCommentType int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idProjectReportCommentType) ); insert into projectReportCommentType values (1,"Puntos importantes"); insert into projectReportCommentType values (2,"Lecciones aprendidas"); insert into projectReportCommentType values (3,"Sugerencias"); insert into projectReportCommentType values (100,"Arquitectura"); insert into projectReportCommentType values (101,"Gestoria"); insert into projectReportCommentType values (102,"Legal/Regimen de condominio"); insert into projectReportCommentType values (103,"Contabilidad"); insert into projectReportCommentType values (104,"Financiera (cobranza)"); insert into projectReportCommentType values (105,"Promocion y publicidad "); insert into projectReportCommentType values (106,"Supervision"); insert into projectReportCommentType values (107,"Ventas"); insert into projectReportCommentType values (108,"Construccion"); insert into projectReportCommentType values (109,"Clientes"); /*==============================================================*/ /* Table: projectRoles */ /*==============================================================*/ create table projectRoles ( idProject int not null, role1 varchar(255) not null, role2 varchar(255) not null, role3 varchar(255) not null ) type = InnoDB; /*==============================================================*/ /* Index: projectRoles_unique */ /*==============================================================*/ create unique index projectRoles_unique on projectRoles ( idProject ); /*==============================================================*/ /* Table: projectSubParcel */ /*==============================================================*/ create table projectSubParcel ( idProjectSubParcel int not null AUTO_INCREMENT, idParent int, idProjectParcel int not null, idProjectTask int not null, idProjectActivity int not null, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, primary key (idProjectSubParcel) ) type = InnoDB; /*==============================================================*/ /* Index: PRJSUBPRCL_unique */ /*==============================================================*/ create unique index PRJSUBPRCL_unique on projectSubParcel ( idProjectParcel, name ); /*==============================================================*/ /* Table: projectSubParcelHistory */ /*==============================================================*/ create table projectSubParcelHistory ( dateRegistered datetime not null, idRegistrant int not null, idProjectSubParcel int not null, idParent int, idProjectParcel int not null, idProjectTask int not null, idProjectActivity int not null, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, comment blob ) type = InnoDB; /*==============================================================*/ /* Table: projectTask */ /*==============================================================*/ create table projectTask ( idProjectTask int not null AUTO_INCREMENT, idParent int, idProjectActivity int not null, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, primary key (idProjectTask) ) type = InnoDB; /*==============================================================*/ /* Index: PRJTSK_unique */ /*==============================================================*/ create unique index PRJTSK_unique on projectTask ( idProjectActivity, name ); /*==============================================================*/ /* Table: projectTaskHelper */ /*==============================================================*/ create table projectTaskHelper ( idProjectTask int not null AUTO_INCREMENT, idProjectActivity int not null, name varchar(255) not null, primary key (idProjectTask) ) type = InnoDB; # # Dumping data for table 'projectTaskHelper' # INSERT INTO projectTaskHelper VALUES("1", "1", "01-A. Factibilidad / Normatividad"); INSERT INTO projectTaskHelper VALUES("2", "1", "01-B. Revisión fiscal"); INSERT INTO projectTaskHelper VALUES("3", "1", "01-C. Revisión financiera"); INSERT INTO projectTaskHelper VALUES("4", "1", "01-D. Revisión legal"); INSERT INTO projectTaskHelper VALUES("5", "1", "01-E. Compra"); INSERT INTO projectTaskHelper VALUES("6", "2", "02-A. Pago derechos hacienda"); INSERT INTO projectTaskHelper VALUES("7", "2", "02-B. Pago notario"); INSERT INTO projectTaskHelper VALUES("8", "2", "02-C. Pago escritura"); INSERT INTO projectTaskHelper VALUES("9", "2", "02-D. Pago comisión vendedor"); INSERT INTO projectTaskHelper VALUES("10", "3", "03-A. Pago derechos"); INSERT INTO projectTaskHelper VALUES("11", "3", "03-B. Gestoría"); INSERT INTO projectTaskHelper VALUES("12", "3", "03-C. Factibilidad"); INSERT INTO projectTaskHelper VALUES("13", "3", "03-D. Regimen Condominio"); INSERT INTO projectTaskHelper VALUES("14", "3", "03-E. Asesoria Fiscal / Legal"); INSERT INTO projectTaskHelper VALUES("15", "4", "04-A. Comisiones"); INSERT INTO projectTaskHelper VALUES("16", "4", "04-B. Gastos indirectos ventas"); INSERT INTO projectTaskHelper VALUES("17", "4", "04-C. Promoción / Publicidad"); INSERT INTO projectTaskHelper VALUES("18", "5", "05-A. Crédito / Cobranza"); INSERT INTO projectTaskHelper VALUES("19", "5", "05-B. Ventas"); INSERT INTO projectTaskHelper VALUES("20", "5", "05-C. Asignación indirectos"); INSERT INTO projectTaskHelper VALUES("21", "6", "06-A. Anteproyecto"); INSERT INTO projectTaskHelper VALUES("22", "6", "06-B. Proyecto arquitectónico"); INSERT INTO projectTaskHelper VALUES("23", "6", "06-C. Ingenierias"); INSERT INTO projectTaskHelper VALUES("24", "6", "06-D. Especificaciones y detalles"); INSERT INTO projectTaskHelper VALUES("25", "7", "07-A. Demolición"); INSERT INTO projectTaskHelper VALUES("26", "7", "07-B. Subcontratista"); INSERT INTO projectTaskHelper VALUES("27", "7", "07-C. Administración obra"); INSERT INTO projectTaskHelper VALUES("28", "8", "08-A. Catalogo conceptos"); INSERT INTO projectTaskHelper VALUES("29", "8", "08-B. Supervisión"); INSERT INTO projectTaskHelper VALUES("30", "10", "10-A. Estudio ambiental"); INSERT INTO projectTaskHelper VALUES("31", "10", "10-B. SP luz"); INSERT INTO projectTaskHelper VALUES("32", "10", "10-C. Otros"); INSERT INTO projectTaskHelper VALUES("33", "11", "11-A. Otros"); /*==============================================================*/ /* Table: projectTaskHistory */ /*==============================================================*/ create table projectTaskHistory ( dateRegistered datetime not null, idRegistrant int not null, idProjectTask int not null, idParent int, idProjectActivity int not null, idProject int not null, idCurrency int, idEmployee int, name varchar(255) not null, amountAuthorized double not null, amountUsed double not null, dateStart date, dateEnd date, dateEndReal date, comment blob ) type = InnoDB; /*==============================================================*/ /* Table: provider */ /*==============================================================*/ create table provider ( idProvider int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idProvider) ) type = InnoDB; /*==============================================================*/ /* Index: PROV_unique */ /*==============================================================*/ create unique index PROV_unique on provider ( name ); /*==============================================================*/ /* Table: providerData */ /*==============================================================*/ create table providerData ( idProvider int not null, documentNumber varchar(255), address varchar(255), address1 varchar(255), address2 varchar(255), city varchar(255), state varchar(255), postalCode varchar(255), phone1 varchar(255), phone2 varchar(255), phone3 varchar(255), email varchar(255) ) type = InnoDB; /*==============================================================*/ /* Index: PROVD_unique */ /*==============================================================*/ create unique index PROVD_unique on providerData ( idProvider ); /*==============================================================*/ /* Table: repositoryDocument */ /*==============================================================*/ create table repositoryDocument ( idRepositoryDocument int not null AUTO_INCREMENT, idRepositoryFolder int not null, idServerFile int not null, primary key (idRepositoryDocument) ); /*==============================================================*/ /* Table: repositoryFolder */ /*==============================================================*/ create table repositoryFolder ( idRepositoryFolder int not null AUTO_INCREMENT, idParent int, idProject int, name varchar(255) not null, description varchar(255) not null, primary key (idRepositoryFolder) ) type = InnoDB; /*==============================================================*/ /* Index: repositoryFolder_unique */ /*==============================================================*/ create unique index repositoryFolder_unique on repositoryFolder ( idProject, name ); /*==============================================================*/ /* Table: salesClient */ /*==============================================================*/ create table salesClient ( idSalesClient int not null AUTO_INCREMENT, idEmployee int, dateRegistration datetime not null, nameFirst varchar(255) not null, nameFirst1 varchar(255) not null, nameLast varchar(255) not null, nameLast1 varchar(255) not null, documentNumber varchar(255) not null, primary key (idSalesClient) ) type = InnoDB; /*==============================================================*/ /* Table: salesClientData */ /*==============================================================*/ create table salesClientData ( idSalesClient int not null, idSalesContactMedia int, idSalesContact int, address varchar(255) not null, address1 varchar(255) not null, address2 varchar(255) not null, city varchar(255) not null, state varchar(255) not null, postalCode varchar(255) not null, phone1 varchar(255) not null, phone2 varchar(255) not null, phone3 varchar(255) not null, email varchar(255) not null ) type = InnoDB; /*==============================================================*/ /* Table: salesClientState */ /*==============================================================*/ create table salesClientState ( idSalesClientState int not null AUTO_INCREMENT, name varchar(64) not null, primary key (idSalesClientState) ) type = InnoDB; /*!40000 ALTER TABLE `salesClientState` DISABLE KEYS */; LOCK TABLES `salesClientState` WRITE; INSERT INTO salesClientState VALUES("10", "Pendiente"); INSERT INTO salesClientState VALUES("20", "Apartado"); INSERT INTO salesClientState VALUES("30", "Cliente"); INSERT INTO salesClientState VALUES("40", "Escriturado"); INSERT INTO salesClientState VALUES("999", "Rechazo"); INSERT INTO salesClientState VALUES("1000", "Prospecto vencido"); UNLOCK TABLES; /*!40000 ALTER TABLE `salesClientState` ENABLE KEYS */; /*==============================================================*/ /* Index: SLS_CS_name */ /*==============================================================*/ create index SLS_CS_name on salesClientState ( name, idSalesClientState ); /*==============================================================*/ /* Table: salesClientUnit */ /*==============================================================*/ create table salesClientUnit ( idSalesClientUnit int not null AUTO_INCREMENT, idSalesClient int not null, idSalesUnit int not null, idEmployee int not null, idSalesClientState int not null, idSalesContact int, idSalesContactMedia int, idProject int, idCurrency int, idSalesPaymentPlan int, dateRegistration datetime not null, sellPrice double not null, price double, primary key (idSalesClientUnit) ) type = InnoDB; /*==============================================================*/ /* Table: salesClientUnitPaymentSchema */ /*==============================================================*/ create table salesClientUnitPaymentSchema ( idSalesClientUnit int not null, booking double not null, dateBooking date, hook double not null, dateHook date, monthly double not null, monthes int not null, dateMonthlyPaymentStart date, lastMonthlyPaymentDay int not null, final1 double not null, final2 double not null, dateFinal1 date, dateFinal2 date, penalty double not null, dateDelivery date ) type = InnoDB; /*==============================================================*/ /* Index: salesClientUnitPaymentSchema_unique */ /*==============================================================*/ create unique index salesClientUnitPaymentSchema_unique on salesClientUnitPaymentSchema ( idSalesClientUnit ); /*==============================================================*/ /* Table: salesClientUnitReject */ /*==============================================================*/ create table salesClientUnitReject ( idSalesClientUnitReject int not null AUTO_INCREMENT, idSalesClient int not null, idSalesClientUnit int not null, idSalesRejectOption int not null, date_created datetime not null, description blob not null, primary key (idSalesClientUnitReject) ) type = InnoDB; /*==============================================================*/ /* Table: salesContact */ /*==============================================================*/ create table salesContact ( idSalesContact int not null AUTO_INCREMENT, idProject int, name varchar(255) not null, primary key (idSalesContact) ) type = InnoDB; /*==============================================================*/ /* Table: salesContactMedia */ /*==============================================================*/ create table salesContactMedia ( idSalesContactMedia int not null AUTO_INCREMENT, idProject int, name varchar(255) not null, primary key (idSalesContactMedia) ) type = InnoDB; /*==============================================================*/ /* Table: salesPayment */ /*==============================================================*/ create table salesPayment ( idSalesPayment int not null AUTO_INCREMENT, dateRegistration datetime, idSalesPaymentType int not null, idSalesClientUnit int not null, idEmployee int, datePaymentExpected date, datePayment date, amountExpected double not null, amount double not null, primary key (idSalesPayment) ); /*==============================================================*/ /* Table: salesPaymentPlan */ /*==============================================================*/ create table salesPaymentPlan ( idSalesPaymentPlan int not null AUTO_INCREMENT, idProject int, name varchar(255) not null, increment double not null, booking double not null, hook double not null, monthly double not null, dateMonthlyEnd date, final1 double not null, final2 double not null, dateFinal1 date, dateFinal2 date, description blob not null, primary key (idSalesPaymentPlan) ) type = InnoDB; /*==============================================================*/ /* Table: salesPaymentType */ /*==============================================================*/ create table salesPaymentType ( idSalesPaymentType int not null AUTO_INCREMENT, name varchar(64) not null, primary key (idSalesPaymentType) ) type = InnoDB; INSERT INTO salesPaymentType VALUES("1", "Apartado"); INSERT INTO salesPaymentType VALUES("2", "Enganche"); INSERT INTO salesPaymentType VALUES("3", "Mensualidad"); INSERT INTO salesPaymentType VALUES("4", "Liquidacion 1"); INSERT INTO salesPaymentType VALUES("5", "Liquidacion 2"); /*==============================================================*/ /* Table: salesRejectOption */ /*==============================================================*/ create table salesRejectOption ( idSalesRejectOption int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idSalesRejectOption) ) type = InnoDB; /*!40000 ALTER TABLE `salesRejectOption` DISABLE KEYS */; LOCK TABLES `salesRejectOption` WRITE; INSERT INTO `salesRejectOption` VALUES (1,'Precio'), (2,'Ingresos'), (3,'Ubicacion'), (4,'Credito rechazado'), (5,'Preferencia estatica'), (6,'Fecha de entrega'), (7,'Dimensiones'), (8,'Areas comunes'), (9,'Acabados'), (10,'Otros'); UNLOCK TABLES; /*!40000 ALTER TABLE `salesRejectOption` ENABLE KEYS */; /*==============================================================*/ /* Table: salesUnit */ /*==============================================================*/ create table salesUnit ( idSalesUnit int not null AUTO_INCREMENT, idProject int not null, idSalesUnitType int not null, idSalesUnitState int not null, idCurrency int not null, idProjectPhoto int, floor varchar(64) not null, number varchar(255) not null, measureUnit varchar(255) not null, quantity double not null, priceUnitary double not null, price double not null, primary key (idSalesUnit) ) type = InnoDB; /*==============================================================*/ /* Table: salesUnitState */ /*==============================================================*/ create table salesUnitState ( idSalesUnitState int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idSalesUnitState) ) type = InnoDB; /*!40000 ALTER TABLE `salesUnitState` DISABLE KEYS */; LOCK TABLES `salesUnitState` WRITE; INSERT INTO `salesUnitState` VALUES (1,'Disponible'), (2,'Disponible con interesados'), (3,'Bloqueado'), (4,'Apartado'), (5,'Vendido'); UNLOCK TABLES; /*!40000 ALTER TABLE `salesUnitState` ENABLE KEYS */; /*==============================================================*/ /* Table: salesUnitType */ /*==============================================================*/ create table salesUnitType ( idSalesUnitType int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idSalesUnitType) ) type = InnoDB; /*!40000 ALTER TABLE `salesUnitType` DISABLE KEYS */; LOCK TABLES `salesUnitType` WRITE; INSERT INTO `salesUnitType` VALUES (1,'Unidad%20Estandar'); UNLOCK TABLES; /*!40000 ALTER TABLE `salesUnitType` ENABLE KEYS */; /*==============================================================*/ /* Table: sellerClientProject */ /*==============================================================*/ create table sellerClientProject ( idEmployee int, idSalesClient int not null, idProject int, dateRegistration datetime not null ) type = InnoDB; /*==============================================================*/ /* Index: sellerClientProject_unique */ /*==============================================================*/ create unique index sellerClientProject_unique on sellerClientProject ( idSalesClient, idProject ); /*==============================================================*/ /* Table: serverFile */ /*==============================================================*/ create table serverFile ( idServerFile int not null AUTO_INCREMENT, idServerFileType int not null, idEmployee int, dateCreated datetime not null, displayName varchar(255) not null, clientFileName varchar(255) not null, contentType varchar(64) not null, fileSize int not null, serverFileName varchar(255) not null, primary key (idServerFile) ) type = InnoDB; /*==============================================================*/ /* Index: SERV_FL_unique */ /*==============================================================*/ create unique index SERV_FL_unique on serverFile ( idServerFileType, displayName ); /*==============================================================*/ /* Index: SERV_FL_idServerFileType */ /*==============================================================*/ create index SERV_FL_idServerFileType on serverFile ( idServerFileType ); /*==============================================================*/ /* Index: SERV_FL_idEmployee */ /*==============================================================*/ create index SERV_FL_idEmployee on serverFile ( idEmployee ); /*==============================================================*/ /* Table: serverFileType */ /*==============================================================*/ create table serverFileType ( idServerFileType int not null AUTO_INCREMENT, name varchar(255) not null, primary key (idServerFileType) ) type = InnoDB; insert into serverFileType values (1,"Logo de la empresa"); insert into serverFileType values (2,"Logo del proyecto"); insert into serverFileType values (3,"Imagen de proyecto"); insert into serverFileType values (4,"Imagen de la factura"); insert into serverFileType values (100,"Company department description"); insert into serverFileType values (101,"Company position description"); insert into serverFileType values (4000,"Documento de repositorio"); /*==============================================================*/ /* Table: systemModule */ /*==============================================================*/ create table systemModule ( idSystemModule int not null AUTO_INCREMENT, idParent int not null default 0, location varchar(255) not null, showInMenu char(1) not null default '666666', displayName varchar(255) not null, primary key (idSystemModule) ) type = InnoDB; INSERT INTO systemModule VALUES("1000", "0", "sales", "Y", "Ventas"); INSERT INTO systemModule VALUES("1100", "1000", "sales/salesClient", "Y", "Prospectos/Clientes"); INSERT INTO systemModule VALUES("1110", "1000", "sales/salesClientData", "N", "Prospectos - Datos de cliente"); INSERT INTO systemModule VALUES("1120", "1000", "sales/salesClientUnit", "N", "Prospectos - Asignacion de unidades"); INSERT INTO systemModule VALUES("1130", "1000", "sales/salesClientUnitPaymentSchema", "N", "Prospectos - Datos de contrato"); INSERT INTO systemModule VALUES("1200", "1000", "sales/salesUnit", "Y", "Unidades"); INSERT INTO systemModule VALUES("1300", "1000", "sales/salesPaymentPlan", "Y", "Planes de pago"); INSERT INTO systemModule VALUES("1400", "1000", "sales/salesPayment", "Y", "Registro de pagos"); INSERT INTO systemModule VALUES("1900", "1000", "sales/salesContact", "Y", "Contactos"); INSERT INTO systemModule VALUES("1910", "1000", "sales/salesContactMedia", "Y", "Medios de contacto"); INSERT INTO systemModule VALUES("1920", "1000", "sales/salesUnitType", "Y", "Tipos de las unidades"); INSERT INTO systemModule VALUES("1930", "1000", "sales/salesClientState", "Y", "Estados de cliente"); INSERT INTO systemModule VALUES("1940", "1000", "sales/salesUnitState", "Y", "Estdos de unidad"); INSERT INTO systemModule VALUES("1950", "1000", "sales/salesRejectOption", "Y", "Opciones de rechazo"); INSERT INTO systemModule VALUES("2000", "0", "humanResources", "Y", "Recursos humanos"); INSERT INTO systemModule VALUES("2100", "2000", "humanResources/employee", "Y", "Empleados"); INSERT INTO systemModule VALUES("2110", "2000", "humanResources/employeeData", "N", "Empleados - datos de contacto"); INSERT INTO systemModule VALUES("2120", "2000", "humanResources/employeeUser", "N", "Empleados - usuario asignado"); INSERT INTO systemModule VALUES("2130", "2000", "humanResources/employeeAccessProfile", "N", "Empleados - perfiles de acceso"); INSERT INTO systemModule VALUES("2140", "2000", "humanResources/employeeCompany", "N", "Empleados - datos de empleo"); INSERT INTO systemModule VALUES("2200", "2000", "humanResources/company", "Y", "Empresas"); INSERT INTO systemModule VALUES("2210", "2000", "humanResources/companyData", "N", "Empresas - datos de contacto"); INSERT INTO systemModule VALUES("2300", "2000", "humanResources/companyDepartment", "Y", "Areas"); INSERT INTO systemModule VALUES("2400", "2000", "humanResources/companyPosition", "Y", "Puestos"); INSERT INTO systemModule VALUES("3000", "0", "administration", "Y", "Administracion"); INSERT INTO systemModule VALUES("3100", "3000", "administration/project", "Y", "Proyectos"); INSERT INTO systemModule VALUES("3110", "3000", "administration/projectInfo", "N", "Proyectos - descripcion"); INSERT INTO systemModule VALUES("3115", "3000", "administration/projectRoles", "N", "Proyectos - roles en proyecto"); INSERT INTO systemModule VALUES("3120", "3000", "administration/projectPhoto", "N", "Proyectos - fotos"); INSERT INTO systemModule VALUES("3200", "3000", "administration/projectActivity", "Y", "Actividades"); INSERT INTO systemModule VALUES("3300", "3000", "administration/projectTask", "Y", "Tareas"); INSERT INTO systemModule VALUES("3400", "3000", "administration/projectParcel", "Y", "Partida"); INSERT INTO systemModule VALUES("3500", "3000", "administration/projectSubParcel", "Y", "Sub-partida"); INSERT INTO systemModule VALUES("3600", "3000", "administration/paymentRequest", "Y", "Requisiciones de pago"); INSERT INTO systemModule VALUES("3610", "3000", "administration/paymentRequestDetails", "N", "Requisiciones de pago - detalles"); INSERT INTO systemModule VALUES("3620", "3000", "administration/paymentRequestStateHistory", "N", "Requisiciones de pago - estado"); INSERT INTO systemModule VALUES("3700", "3000", "administration/provider", "Y", "Proveedores"); INSERT INTO systemModule VALUES("3710", "3000", "administration/providerData", "N", "Proveedores - datos de contacto"); INSERT INTO systemModule VALUES("3720", "3000", "administration/projectProvider", "N", "Proveedores - asignacion a proyectos"); INSERT INTO systemModule VALUES("4000", "0", "financial", "Y", "Finanzas"); INSERT INTO systemModule VALUES("4100", "4000", "financial/capital", "Y", "Capital"); INSERT INTO systemModule VALUES("4200", "4000", "financial/capitalMovement", "Y", "Movimientos de capital"); INSERT INTO systemModule VALUES("4210", "4000", "financial/capitalMovementPayback", "N", "Esquema de movimientos de pago"); INSERT INTO systemModule VALUES("4900", "4000", "financial/capitalParticipant", "Y", "Participantes de movimientos"); INSERT INTO systemModule VALUES("4910", "4000", "financial/capitalMovementType", "Y", "Tipos de movimientos"); INSERT INTO systemModule VALUES("7000", "0", "repository", "Y", "Repositorio"); INSERT INTO systemModule VALUES("7100", "7000", "repository/repositoryDocument", "Y", "Documentos"); INSERT INTO systemModule VALUES("7200", "7000", "repository/repositoryFolder", "Y", "Carpetas"); INSERT INTO systemModule VALUES("8000", "0", "report", "Y", "Reportes"); INSERT INTO systemModule VALUES("8100", "8000", "report/reportProyectStatus", "Y", "Estado de proyecto"); INSERT INTO systemModule VALUES("9000", "0", "configuration", "Y", "Configuracion"); INSERT INTO systemModule VALUES("9100", "9000", "configuration/accessProfile", "Y", "Perfiles de acceso"); INSERT INTO systemModule VALUES("9110", "9000", "configuration/systemModule", "Y", "Modulos de sistema"); INSERT INTO systemModule VALUES("9200", "9000", "configuration/employeeType", "Y", "Tipos de empleados"); INSERT INTO systemModule VALUES("9300", "9000", "configuration/paymentType", "Y", "Tipos de pago"); INSERT INTO systemModule VALUES("9400", "9000", "configuration/paymentRequestState", "Y", "Estados de requisicion de pago"); INSERT INTO systemModule VALUES("9500", "9000", "configuration/currency", "Y", "Monedas"); INSERT INTO systemModule VALUES("9510", "9000", "configuration/currencyChangeRate", "Y", "Valores de cambio"); /*==============================================================*/ /* Index: SMOD_unique */ /*==============================================================*/ create unique index SMOD_unique on systemModule ( idParent, location ); alter table accessProfileRoles add constraint FK_accessProfile1 foreign key (idAccessProfile) references accessProfile (idAccessProfile) on delete cascade on update cascade; alter table accessProfileRoles add constraint FK_systemModule1 foreign key (idSystemModule) references systemModule (idSystemModule) on delete cascade on update cascade; alter table capital add constraint FK_currency11 foreign key (idCurrency) references currency (idCurrency) on delete cascade on update cascade; alter table capital add constraint FK_project16 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table capitalMovement add constraint FK_capitalMovementPayback1 foreign key (idCapitalMovementPayback) references capitalMovementPayback (idCapitalMovementPayback) on delete restrict on update cascade; alter table capitalMovement add constraint FK_capitalMovementType1 foreign key (idCapitalMovementType) references capitalMovementType (idCapitalMovementType) on delete cascade on update cascade; alter table capitalMovement add constraint FK_capitalParticipant1 foreign key (idCapitalParticipantFrom) references capitalParticipant (idCapitalParticipant) on delete cascade on update cascade; alter table capitalMovement add constraint FK_capitalParticipant2 foreign key (idCapitalParticipantTo) references capitalParticipant (idCapitalParticipant) on delete cascade on update cascade; alter table capitalMovement add constraint FK_currency12 foreign key (idCurrency) references currency (idCurrency) on delete restrict on update cascade; alter table capitalMovement add constraint FK_employee17 foreign key (idEmployee) references employee (idEmployee) on delete cascade on update cascade; alter table capitalMovementPayback add constraint FK_capitalMovement1 foreign key (idCapitalMovement) references capitalMovement (idCapitalMovement) on delete cascade on update cascade; alter table capitalMovementPayback add constraint FK_capitalMovementType2 foreign key (idCapitalMovementType) references capitalMovementType (idCapitalMovementType) on delete cascade on update cascade; alter table capitalParticipant add constraint FK_capital1 foreign key (idCapital) references capital (idCapital) on delete cascade on update cascade; alter table company add constraint FK_serverFile1 foreign key (idServerFile) references serverFile (idServerFile) on delete set null on update cascade; alter table companyData add constraint FK_company3 foreign key (idCompany) references company (idCompany) on delete cascade on update cascade; alter table companyDepartment add constraint FK_company1 foreign key (idCompany) references company (idCompany) on delete cascade on update cascade; alter table companyDepartment add constraint FK_serverFile6 foreign key (idServerFile) references serverFile (idServerFile) on delete set null on update cascade; alter table companyPosition add constraint FK_company4 foreign key (idCompany) references company (idCompany) on delete cascade on update cascade; alter table companyPosition add constraint FK_companyDepartment2 foreign key (idCompanyDepartment) references companyDepartment (idCompanyDepartment) on delete cascade on update cascade; alter table companyPosition add constraint FK_serverFile5 foreign key (idServerFile) references serverFile (idServerFile) on delete set null on update cascade; alter table currencyChangeRate add constraint FK_currency3 foreign key (idCurrencyFrom) references currency (idCurrency) on delete cascade on update cascade; alter table currencyChangeRate add constraint FK_currency4 foreign key (idCurrencyTo) references currency (idCurrency) on delete cascade on update cascade; alter table currencyChangeRate add constraint FK_employee8 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table employee add constraint FK_infEmployeeType foreign key (idEmployeeType) references employeeType (idEmployeeType) on delete restrict on update restrict; alter table employeeAccessProfile add constraint FK_accessProfile2 foreign key (idAccessProfile) references accessProfile (idAccessProfile) on delete cascade on update cascade; alter table employeeAccessProfile add constraint FK_employee3 foreign key (idEmployee) references employee (idEmployee) on delete cascade on update cascade; alter table employeeAccessProfile add constraint FK_project7 foreign key (idProject) references project (idProject) on delete set null on update cascade; alter table employeeCompany add constraint FK_company2 foreign key (idCompany) references company (idCompany) on delete set null on update cascade; alter table employeeCompany add constraint FK_companyDepartment1 foreign key (idCompanyDepartment) references companyDepartment (idCompanyDepartment) on delete set null on update cascade; alter table employeeCompany add constraint FK_companyPosition1 foreign key (idCompanyPosition) references companyPosition (idCompanyPosition) on delete set null on update cascade; alter table employeeCompany add constraint FK_employee5 foreign key (idEmployee) references employee (idEmployee) on delete cascade on update cascade; alter table employeeData add constraint FK_employee1 foreign key (idEmployee) references employee (idEmployee) on delete cascade on update cascade; alter table employeeUser add constraint FK_employee2 foreign key (idEmployee) references employee (idEmployee) on delete cascade on update cascade; alter table hys_salesUnit1 add constraint FK_hys_salesUnit1 foreign key (idSalesUnit) references salesUnit (idSalesUnit) on delete cascade on update cascade; alter table paymentRequest add constraint FK_company5 foreign key (idCompany) references company (idCompany) on delete set null on update cascade; alter table paymentRequest add constraint FK_currency8 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table paymentRequest add constraint FK_employee13 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table paymentRequest add constraint FK_paymentRequestState1 foreign key (idPaymentRequestState) references paymentRequestState (idPaymentRequestState) on delete cascade on update cascade; alter table paymentRequest add constraint FK_paymentType1 foreign key (idPaymentType) references paymentType (idPaymentType) on delete set null on update cascade; alter table paymentRequest add constraint FK_project12 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table paymentRequest add constraint FK_provider2 foreign key (idProvider) references provider (idProvider) on delete restrict on update cascade; alter table paymentRequest add constraint FK_serverFile4 foreign key (idServerFile) references serverFile (idServerFile) on delete set null on update cascade; alter table paymentRequestDetails add constraint FK_paymentRequest1 foreign key (idPaymentRequest) references paymentRequest (idPaymentRequest) on delete cascade on update cascade; alter table paymentRequestDetails add constraint FK_projectActivity4 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete set null on update cascade; alter table paymentRequestDetails add constraint FK_projectParcel2 foreign key (idProjectParcel) references projectParcel (idProjectParcel) on delete set null on update cascade; alter table paymentRequestDetails add constraint FK_projectSubParcel1 foreign key (idProjectSubParcel) references projectSubParcel (idProjectSubParcel) on delete set null on update cascade; alter table paymentRequestDetails add constraint FK_projectTask3 foreign key (idProjectTask) references projectTask (idProjectTask) on delete set null on update cascade; alter table paymentRequestStateHistory add constraint FK_paymentRequest2 foreign key (idPaymentRequest) references paymentRequest (idPaymentRequest) on delete cascade on update cascade; alter table paymentRequestStateHistory add constraint FK_paymentRequestState2 foreign key (idPaymentRequestState) references paymentRequestState (idPaymentRequestState) on delete cascade on update cascade; alter table paymentRequestStateHistory add constraint Fk_employee14 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table project add constraint FK_currency1 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table project add constraint FK_salesPaymentPlan2 foreign key (idSalesPaymentPlan) references salesPaymentPlan (idSalesPaymentPlan) on delete set null on update cascade; alter table project add constraint FK_serverFile2 foreign key (idServerFile) references serverFile (idServerFile) on delete set null on update cascade; alter table project add constraint FK_serverFile8 foreign key (idServerFile1) references serverFile (idServerFile) on delete set null on update cascade; alter table projectActivity add constraint FK_currency5 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectActivity add constraint FK_employee9 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table projectActivity add constraint FK_project8 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectActivityHistory add constraint FK_currency13 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectActivityHistory add constraint FK_employee21 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table projectActivityHistory add constraint FK_employee22 foreign key (idRegistrant) references employee (idEmployee) on delete cascade on update cascade; alter table projectActivityHistory add constraint FK_project18 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectActivityHistory add constraint FK_projectActivity5 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete cascade on update cascade; alter table projectInfo add constraint FK_project2 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectParcel add constraint FK_currency7 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectParcel add constraint FK_employee11 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table projectParcel add constraint FK_project10 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectParcel add constraint FK_projectActivity2 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete cascade on update cascade; alter table projectParcel add constraint FK_projectTask1 foreign key (idProjectTask) references projectTask (idProjectTask) on delete cascade on update cascade; alter table projectParcelHistory add constraint FK_currency15 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectParcelHistory add constraint FK_employee25 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table projectParcelHistory add constraint FK_employee26 foreign key (idRegistrant) references employee (idEmployee) on delete cascade on update cascade; alter table projectParcelHistory add constraint FK_project20 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectParcelHistory add constraint FK_projectActivity7 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete cascade on update cascade; alter table projectParcelHistory add constraint FK_projectParcel3 foreign key (idProjectParcel) references projectParcel (idProjectParcel) on delete cascade on update cascade; alter table projectParcelHistory add constraint FK_projectTask5 foreign key (idProjectTask) references projectTask (idProjectTask) on delete cascade on update cascade; alter table projectPhoto add constraint FK_project1 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectPhoto add constraint FK_serverFile3 foreign key (idServerFile) references serverFile (idServerFile) on delete cascade on update cascade; alter table projectProvider add constraint FK_project22 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectProvider add constraint FK_provider3 foreign key (idProvider) references provider (idProvider) on delete cascade on update cascade; alter table projectReportComment add constraint FK_employee28 foreign key (idEmployee) references employee (idEmployee) on delete cascade on update cascade; alter table projectReportComment add constraint FK_project23 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectReportComment add constraint FK_projectReportCommentType1 foreign key (idProjectReportCommentType) references projectReportCommentType (idProjectReportCommentType) on delete cascade on update cascade; alter table projectRoles add constraint FK_project17 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectSubParcel add constraint FK_currency9 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectSubParcel add constraint FK_employee12 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table projectSubParcel add constraint FK_project11 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectSubParcel add constraint FK_projectActivity3 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete cascade on update cascade; alter table projectSubParcel add constraint FK_projectParcel1 foreign key (idProjectParcel) references projectParcel (idProjectParcel) on delete cascade on update cascade; alter table projectSubParcel add constraint FK_projectTask2 foreign key (idProjectTask) references projectTask (idProjectTask) on delete cascade on update cascade; alter table projectSubParcelHistory add constraint FK_currency16 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectSubParcelHistory add constraint FK_employee27 foreign key (idRegistrant) references employee (idEmployee) on delete cascade on update cascade; alter table projectSubParcelHistory add constraint FK_project21 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectSubParcelHistory add constraint FK_projectActivity8 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete cascade on update cascade; alter table projectSubParcelHistory add constraint FK_projectParcel4 foreign key (idProjectParcel) references projectParcel (idProjectParcel) on delete cascade on update cascade; alter table projectSubParcelHistory add constraint FK_projectSubParcel2 foreign key (idProjectSubParcel) references projectSubParcel (idProjectSubParcel) on delete cascade on update cascade; alter table projectSubParcelHistory add constraint FK_projectTask6 foreign key (idProjectTask) references projectTask (idProjectTask) on delete cascade on update cascade; alter table projectSubParcelHistory add constraint FK_PROJECTSUB_REFE_EMPLOYEE foreign key (idEmployee) references employee (idEmployee) on delete restrict on update restrict; alter table projectTask add constraint FK_currency6 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectTask add constraint FK_employee10 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table projectTask add constraint FK_project9 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectTask add constraint FK_projectActivity1 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete cascade on update cascade; alter table projectTaskHelper add constraint FK_projectActivityHelper1 foreign key (idProjectActivity) references projectActivityHelper (idProjectActivity) on delete cascade on update cascade; alter table projectTaskHistory add constraint FK_currency14 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table projectTaskHistory add constraint FK_employee23 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table projectTaskHistory add constraint FK_employee24 foreign key (idRegistrant) references employee (idEmployee) on delete cascade on update cascade; alter table projectTaskHistory add constraint FK_project19 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table projectTaskHistory add constraint FK_projectActivity6 foreign key (idProjectActivity) references projectActivity (idProjectActivity) on delete cascade on update cascade; alter table projectTaskHistory add constraint FK_projectTask4 foreign key (idProjectTask) references projectTask (idProjectTask) on delete cascade on update cascade; alter table providerData add constraint FK_provider1 foreign key (idProvider) references provider (idProvider) on delete cascade on update cascade; alter table repositoryDocument add constraint FK_repositoryFolder1 foreign key (idRepositoryFolder) references repositoryFolder (idRepositoryFolder) on delete cascade on update cascade; alter table repositoryDocument add constraint FK_serverFile7 foreign key (idServerFile) references serverFile (idServerFile) on delete cascade on update cascade; alter table repositoryFolder add constraint FK_project15 foreign key (idProject) references project (idProject) on delete set null on update cascade; alter table salesClient add constraint FK_employee6 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table salesClientData add constraint FK_salesClient1 foreign key (idSalesClient) references salesClient (idSalesClient) on delete cascade on update cascade; alter table salesClientData add constraint FK_salesContact2 foreign key (idSalesContact) references salesContact (idSalesContact) on delete set null on update cascade; alter table salesClientData add constraint FK_salesContactMedia2 foreign key (idSalesContactMedia) references salesContactMedia (idSalesContactMedia) on delete set null on update cascade; alter table salesClientUnit add constraint FK_currency10 foreign key (idCurrency) references currency (idCurrency) on delete set null on update cascade; alter table salesClientUnit add constraint FK_employee7 foreign key (idEmployee) references employee (idEmployee) on delete cascade on update cascade; alter table salesClientUnit add constraint FK_project13 foreign key (idProject) references project (idProject) on delete set null on update cascade; alter table salesClientUnit add constraint FK_salesClient2 foreign key (idSalesClient) references salesClient (idSalesClient) on delete cascade on update cascade; alter table salesClientUnit add constraint FK_salesClientState1 foreign key (idSalesClientState) references salesClientState (idSalesClientState) on delete cascade on update cascade; alter table salesClientUnit add constraint FK_salesContact1 foreign key (idSalesContact) references salesContact (idSalesContact) on delete set null on update cascade; alter table salesClientUnit add constraint FK_salesContactMedia1 foreign key (idSalesContactMedia) references salesContactMedia (idSalesContactMedia) on delete set null on update cascade; alter table salesClientUnit add constraint FK_salesPaymentPlan1 foreign key (idSalesPaymentPlan) references salesPaymentPlan (idSalesPaymentPlan) on delete set null on update cascade; alter table salesClientUnit add constraint FK_salesUnit1 foreign key (idSalesUnit) references salesUnit (idSalesUnit) on delete cascade on update cascade; alter table salesClientUnitPaymentSchema add constraint FK_salesClientUnit2 foreign key (idSalesClientUnit) references salesClientUnit (idSalesClientUnit) on delete cascade on update cascade; alter table salesClientUnitReject add constraint FK_salesClient3 foreign key (idSalesClient) references salesClient (idSalesClient) on delete cascade on update cascade; alter table salesClientUnitReject add constraint FK_salesClientUnit1 foreign key (idSalesClientUnit) references salesClientUnit (idSalesClientUnit) on delete cascade on update cascade; alter table salesClientUnitReject add constraint FK_salesRejectOption1 foreign key (idSalesRejectOption) references salesRejectOption (idSalesRejectOption) on delete cascade on update cascade; alter table salesContact add constraint FK_project5 foreign key (idProject) references project (idProject) on delete set null on update cascade; alter table salesContactMedia add constraint FK_project6 foreign key (idProject) references project (idProject) on delete set null on update cascade; alter table salesPayment add constraint FK_employee16 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table salesPayment add constraint FK_salesClientUnit3 foreign key (idSalesClientUnit) references salesClientUnit (idSalesClientUnit) on delete cascade on update cascade; alter table salesPayment add constraint FK_salesPaymentType foreign key (idSalesPaymentType) references salesPaymentType (idSalesPaymentType) on delete cascade on update cascade; alter table salesPaymentPlan add constraint FK_project4 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table salesUnit add constraint FK_currency2 foreign key (idCurrency) references currency (idCurrency) on delete cascade on update cascade; alter table salesUnit add constraint FK_project3 foreign key (idProject) references project (idProject) on delete cascade on update cascade; alter table salesUnit add constraint FK_projectPhoto1 foreign key (idProjectPhoto) references projectPhoto (idProjectPhoto) on delete set null on update cascade; alter table salesUnit add constraint FK_salesUnitState1 foreign key (idSalesUnitState) references salesUnitState (idSalesUnitState) on delete cascade on update cascade; alter table salesUnit add constraint FK_salesUnitType1 foreign key (idSalesUnitType) references salesUnitType (idSalesUnitType) on delete cascade on update cascade; alter table sellerClientProject add constraint FK_employee15 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table sellerClientProject add constraint FK_project14 foreign key (idProject) references project (idProject) on delete set null on update cascade; alter table sellerClientProject add constraint FK_salesClient4 foreign key (idSalesClient) references salesClient (idSalesClient) on delete cascade on update cascade; alter table serverFile add constraint FK_employee4 foreign key (idEmployee) references employee (idEmployee) on delete set null on update cascade; alter table serverFile add constraint FK_serverFileType1 foreign key (idServerFileType) references serverFileType (idServerFileType) on delete restrict on update restrict;