Database
Connecteren met de database
MySql
Volgende zaken zijn noodzakelijk:
- poort 3306 (voor mySql) dient geopend te zijn.
- mySql moet admin op afstand toelaten
- gebruikerscode- en paswoord
- Schema : ISIWEB
Om te connecteren vanuit Windows, kan men gebruik maken van de applicatie GUi de mySql
Qualification & Premium and Cost
- authGroup (.id)
- Contract (.id)
- contractsalary (.contractid)
- Premiumandfeequantification (.contractid)
- authgroup (.responsibleGroupId)
- mission (.id)
- occupation (.missionOccupationsId)
- occupation (.id)
- contract (.occupationContractsId)
- Premiumandfeequantification (.id)
- actualotherpremiumandfee
- premiumandfee
- premiumandfeetype
- otherpremiumandfee
Worker
- Person (id) : entiteit die de gegevens van een persoon herneemt
- Worker (workerId) (voor werknemers)
- Contact
- User
Customer
- companyNumber : comvat het nummer van het bedrijf
Company
- AbstractCompany
- businessId : businesscode
- socialSecretariatPartitioningRef : referentie sociaal secretariaat
- accountigCompanyRef : referentie boekhouding
- availableSelfservice : self-service beschikbaar
- mainContactMedias_xxx : Telefoon, email, fax, adres
- relantionTypeId : deprecated
- companysourceid : bron van het bedrijf
- Company (company.companyid = abstractcompany.id)
- Name2 : contactpersoon van het bedrijf
- companynumber : nummer van het bedrijf
- website : web-site
- onssNumber : RSZ-identificatienummer
- PermanentWorkerCount : aantal vaste werknemers (graydon)
- Score : score Graydon
- Validated : gevalideerd (J/N)
- UnionRepresentative (Yes/NO/?)
- CompanySizeId : grootte van het bedrijf
- socialReasonId :juridische vorm
- relationTypeId : type relatie
- importMissionId : import via gebruik opdrachtcode (J/N)
- CompanysizeConstruct : grootte van het bouwbedrijf
- niss : insz-nummer van de fysieke persoon
- mailCorrespondance : mailverkeer (J/N)
- Holding : holdingidentificatie ( company.holdingcompaniesId = holding.id )
- ContactCompany
- Preferentialcontactcompany : voorkeurcontact (= veld contactsCompanyid) indien niet gekend : null
- Contact
- contactType : type contact
- contactMedias_xxx : telefoon, gsm, fax, adres
- Person : fysieke persoon (identiek aan worker &t user) ( contact.personid = persone.id )
- Firstname : voornaam
- LastName : familienaam
- personalPhone : persoonlijk telefoonnummerl
- personalMobile : GSM
- language : taal
- enabled : geactiveerd (j/n)
- nationalytid : code nationaliteit
- title : titel
- account : self-service account (contact.accountid = account.id)
Overeenkomst
- Overeenkomst : Bepaalt hoe de klant wordt beheerd (facturatie, … )
- BusinessId : identificatienummer van de overeenkomst
- enabled : actief (J/N)
- creationDate : aanmaakdatum van de overeenkomst
- startDate : begindatum overeenkomst
- endDate : einddatum overeenkomst
- identiyCardRequired : identiteitskaart vereist
- medicalExamination : medisch attest vereist
- ContractGroupingType : wijze waarop de contracten worden gegroepeerd (BYSTAT, BYCONTACTPERSON, BYCOMMAND)
- ContractPeriodicity : frekwentie van de contracten (WEEK, MONTH,NONE)
- useTimeSlotGrid : gebruik van de prestatiestaat
- invoiceGroupingType : wijze waarop de facturen worden gegroepeerd (BYSTAT, BYCONTACTPERSON, BYCOMMAND)
- Discount : korting
- invoicetype : facturatietypen (POSITIONS_QUALIFICATION, RESUME)
- invoiceContent : alleen de reeds betaalde prestaties factureren of alles (QUALIFIED, PAID)
- tvaCode : gebruikte BTW-code
- paymentTermid : betaaltermijn
- preferentialAgency : code van de voorkeursgroep
- invoiceFrequency : facturatiefrekwentie (WEEK, MONTH)
- invoiceGroupingbyMission : opdracht in 1 groep gegroepeerd op de factuur (ipv per contract) (J/N)
- splitKey : facturatie opgesplitst per week, maand, niks (WEEK, MONTH, NONE)
- CompanyName : naam van het bedrijf
- NumberOfWorkerContractCopies : aantal exemplaren werknemerscontract (specifiek voor deze klant)
- NumberofCustomerContractCopies : aantal exemplaren klantencontract (specifiek voor deze klant)
- NumberofInvoiceCopies : aantal exemplaren factuur (specifiek voor deze klant)
- company : bedrijf (convention.companyid = company.companyid)
- Contact : contactpersoon voor de facturatie (convention.invoicecontactCompanyid = contact.id)
- Contact : contactpersoon voor de contracten (convention.contractcontactCompanyid = contact.id )
- basicPosition : posten gelinkt aan de overeenkomst (convention.id = basicposition.conventionId)
- BusinessId : code van de post
- enabled : geactiveerd (J/N)
- CompanyName : bedrijfsnaam
- labelFr : omschrijving FR
- labelNl : omschrijving NL
- artist : artiest (J/N)
- AutomaticQualificationForPublicHoliday
- basic_qualification_position : link naar de gelinkte kwalificaties voor de post
- position : informatie van de post identiek aan de opdracht (basicposition.id = position.id )
- category : categorie
- preferentialAgentid : voorkeurs-agent
- positionAttributeForOccupationID : attributen van de tewerkstelling (zie verder)
- positionAttributeForContractId : attributen van het contract (zie verder)
- defaultGroupid : standaardgroep
- position_translation : omschrijving uit de post
Contract
- Contract
- Occupation : Berekende tewrkstellingen voor de sociale documenten (contract.occupationContractsId = occupation.id)
- mission : (occupation.missionOccupationsId = mission.id)
- authgroup : Groepen (contract.responsibleGroupId = authgroup.id)
- contractsalary : lijst van de lonen gelinkt aan het contract (.contractid)
- Premiumandfeequantification : lijst van de prestaties gelinkt aan het contract (.contractid)
- positionattributesforcontract : attributen van het contract (contract.positionAttributesForcontractId = positionattributesforcontract.id )
- userclassification : classificatie van de functie bij de gebruiker
- professionalqualification : beroepskwalificatie
- allocationActiva : Recht op werkuitkering (J/N)
- CoefficientbyDefault : standaardcoefficient van het contract
- positionattributesforoccupation : (contract.positionAttributesForOccupationId = positionattributesforoccupation.id )
- ContractType : contracttype (CDD, CDI)
- jointcommitteeid : identificatie van de Par.Com.
- worksystem : (positionattributesforoccupation.workSystemId = worksystem.id)
- weeklytime : gemiddeld aantal uren op weekbasis
- annualtime : gemiddeld aantal uren op jaarbasis
- partialtime : aantal uren voor deeltijds stelsel
- realworkhours : aantal werkelijk gepresteerde uren
- isPartialTime : deeltijds (j/n)
- days : aantal dagen
- variabletimetable : variabel uurrooster
- cycleworksystem : systeem van cyclische uurroosters
- trialdays : aantal dagen proefperiode
- CompensationMode : compensatiewijze
- Verlenging van het contract (monday, daily, …)
- specific_premiumandfee_positionattributesforcontract : lijst van de premies & kosten gelinkt aan het contract (contract.positionAttributesForContractId = specific_premiumandfee_positionattributesforcontract.positionAttributesForContractId )
- specificmealticket : maaltijdcheques (specific_premiumandfee_positionattributesforcontract.premiumandfeeId = specificmealticket.id )
- …
Prestatie
- Hours : uren
- Contract : contract ( contract.id = hours.contractId )
- authgroup : groep (contract.responsibleGroupId = authgroup.id)
- Occupation : occupation (occupation.id = contract.occupationContractsId)
- mission.id = occupation.missionOccupationsId
- Qualificationposition : ( qualificationposition.id = hours.hoursQualificationId )
- Hourstype : uurtype - admin (qualificationposition.hoursType = hourstype.id)
- hoursqualification : ( hoursqualification.id = hours.hoursQualificationId )
- premiumandfeequantification :
- Contract : contract (
- Occupation : occupation (occupation.id = contract.occupationContractsId)
- mission.id = occupation.missionOccupationsId
- Actualotherpremiumandfee : (premiumandfeequantification.actualPremiumAndFeeId = actualotherpremiumandfee.id)
- Premiumandfee : (premiumandfee.id = actualotherpremiumandfee.id)
- otherpremiumandfee : (otherpremiumandfee.id = premiumandfee.premiumAndFeeTypeId)
- premiumandfeetype : (premiumandfeetype.id = otherpremiumandfee.id)
- actualmealticket : (premiumandfeequantification.actualPremiumAndFeeId = actualmealticket.id)
- Premiumandfee : (premiumandfee.id = actualmealticket.id)
- mealticket : (mealticket.id = premiumandfee.premiumAndFeeTypeId)
- premiumandfeetype : (premiumandfeetype.id = mealticket.id)
OLAP View
Hours olap view
Veld | Omschrijving |
---|---|
Hoursid | id van de prestatie |
hoursdate | datum van de prestatie |
workername | naam van de werknemmer |
contractid | id van het contract |
workerid | id van de werknemmer |
companyid | id van de firma |
customername | Naam van de firma |
groupname | Groep Naam |
CorporateId | Id van de interim bedrijf in ISIWEB |
workerstatus | ID van het statuut |
hoursamount | aantal uren |
hours week | Week van de prestatie |
hours year | Jaar van de prestatie |
HoursTypeBusinessCode | Codificatie van type uren (zoals mutualiteit informatie) |
brutbyhour | uurloon |
paidvalue | eenheidsprijs van de kost |
invoicedvalue | eenheidsprijs van de factuur |
payunit | type van eenheid kost (PURCENTATE, BEDRAG) |
invoiceunit | type van eenheid factuur |
coefficient | Coef van de prestatie voor de uren |
invoiced | totaal bedrag gefactureerd voor de prestatie |
payed | totaal bedrag betaald voor de prestatie |
datatype | Type van prestatie (uren, kost, maaltijdcheque, premie) |
ordernumber | bestelling nummer van de prestatie |
socialSecretariatRef | code van het sociaal secretariat |
invoiceid | ID van het bestand van facturen (als leeg → nog niet gefactureerd) |
exportbatchid | id van het bestand voor het sociaal secretariaat (als leeg → nog niet betaald) |
workerParticipation | participatie van de WKN (Maaltijdcheque) |
creationDate | Aanmaakdatum van de prestatie |
LastupdateDate | Laatste wijzigingsdatum van de prestatie |
Historiek | |
2009/09/03 | Code van soc.sec., Id van factuur en link naar soc.sec. |
2009/09/08 | Rechtzettings van de bedarg kost van de WKN |
2009/09/11 | Toevoeging van de ID customer, ID werknemmer |
2009/12/01 | Bedrag van inhoud maaltijdcheques |
2010/02/01 | aanpassing link naar klant |
2010/04/21 | Aantal van P&K |
2010/05/05 | Omschrijving van codes |
2010/06/21 | rechtzettings van bedrag factureerd voor uren gebaseerd op bedrag |
2011/01/21 | Toevoegen van de aanmaakdatum en de datum laatste wijziging |
SQL-statement | |
DROP VIEW IF EXISTS `hoursolap`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`bots`@`%` SQL SECURITY DEFINER VIEW `hoursolap` AS (select `hours`.`creationdate` AS `creationdate`, `hours`.`lastupdatedate` AS `lastupdatedate`, `hours`.`id` AS `hoursId`,`hours`.`date` AS `hoursDate`,`contract`.`workerName` AS `workerName`,`contract`.`id` AS `contractId`,`mission`.`workerMissionsId` AS `workerId`,`mission`.`customerId` AS `companyId`,`contract`.`customerName` AS `customerName`,`authgroup`.`name` AS `groupName`,`authgroup`.`corporateId` AS `corporateId`,`mission`.`workerStatus` AS `workerStatus`,`hours`.`amount` AS `hoursAmount`,week(`hours`.`date`,1) AS `hoursWeek`,year(`hours`.`date`) AS `hoursYear`,`hourstype`.`businessCode` AS `hoursTypeBusinessCode`,`hoursqualification`.`brutByHour` AS `brutByHour`,`qualificationposition`.`paidValue` AS `paideValue`,`qualificationposition`.`invoicedValue` AS `invoicedValue`,`qualificationposition`.`payUnit` AS `payUnit`,`qualificationposition`.`invoiceUnit` AS `invoiceUnit`,`qualificationposition`.`coefficient` AS `coefficient`,if((`hours`.`invoicedAmount` = 0),(((`hours`.`amount` * (`qualificationposition`.`invoicedValue` / 100)) * `hoursqualification`.`brutByHour`) * `qualificationposition`.`coefficient`),`hours`.`invoicedAmount`) AS `invoiced`,`hours`.`cost` AS `payed`,'HOURS' AS `dataType`,`contract`.`orderNumber` AS `orderNumber`,`hourstype`.`socialSecretariatRef` AS `socialSecretariatRef`,`hours`.`invoiceId` AS `invoiceId`,`hours`.`exportBatchId` AS `exportBatchId`,0 AS `workerParticipation`,0 AS `qt`,`hours`.`orderNumber` AS `prestationOrderNumber`,(select `qualificationposition_translations`.`label` AS `label` from `qualificationposition_translations` where ((`qualificationposition_translations`.`locale` = 'fr') and (`qualificationposition_translations`.`id` = `hoursqualification`.`id`))) AS `labelFR`,(select `qualificationposition_translations`.`label` AS `label` from `qualificationposition_translations` where ((`qualificationposition_translations`.`locale` = 'nl') and (`qualificationposition_translations`.`id` = `hoursqualification`.`id`))) AS `labelNL` from (((((((`hours` join `contract`) join `authgroup`) join `qualificationposition`) join `hoursqualification`) join `hourstype`) join `mission`) join `occupation`) where ((`contract`.`id` = `hours`.`contractId`) and (`contract`.`responsibleGroupId` = `authgroup`.`id`) and (`qualificationposition`.`id` = `hours`.`hoursQualificationId`) and (`qualificationposition`.`hoursType` = `hourstype`.`id`) and (`hoursqualification`.`id` = `hours`.`hoursQualificationId`) and (`mission`.`id` = `occupation`.`missionOccupationsId`) and (`occupation`.`id` = `contract`.`occupationContractsId`))) union (select `premiumandfeequantification`.`creationdate` AS `creationdate`, `premiumandfeequantification`.`lastupdateDate` AS `lastupdateDate`, `premiumandfeequantification`.`id` AS `hoursId`,`premiumandfeequantification`.`date` AS `hoursDate`,`contract`.`workerName` AS `workerName`,`contract`.`id` AS `contractId`,`mission`.`workerMissionsId` AS `workerId`,`mission`.`customerId` AS `companyId`,`contract`.`customerName` AS `customerName`,`authgroup`.`name` AS `groupName`,`authgroup`.`corporateId` AS `corporateId`,`mission`.`workerStatus` AS `workerStatus`,0 AS `hoursAmount`,week(`premiumandfeequantification`.`date`,1) AS `hoursWeek`,year(`premiumandfeequantification`.`date`) AS `hoursYear`,`premiumandfeetype`.`businessCode` AS `hoursTypeBusinessCode`,0 AS `brutByHour`,`actualotherpremiumandfee`.`payedValue` AS `paideValue`,`actualotherpremiumandfee`.`invoiceValue` AS `invoicedValue`,`actualotherpremiumandfee`.`payUnit` AS `payUnit`,`actualotherpremiumandfee`.`invoiceUnit` AS `invoiceUnit`,0 AS `coefficient`,`premiumandfeequantification`.`invoicedAmount` AS `invoiced`,`premiumandfeequantification`.`cost` AS `payed`,`otherpremiumandfee`.`otherPremiumAndFeeType` AS `dataType`,`contract`.`orderNumber` AS `orderNumber`,`premiumandfeetype`.`socialSecretariatRef` AS `socialSecretariatRef`,`premiumandfeequantification`.`invoiceId` AS `invoiceId`,`premiumandfeequantification`.`exportBatchId` AS `exportBatchId`,0 AS `workerParticipation`,`premiumandfeequantification`.`amount` AS `qt`,`premiumandfeequantification`.`orderNumber` AS `prestationOrderNumber`,(select `premiumandfee_label`.`label` AS `label` from `premiumandfee_label` where ((`premiumandfee_label`.`locale` = 'fr') and (`premiumandfee_label`.`id` = `premiumandfee`.`id`))) AS `labelFR`,(select `premiumandfee_label`.`label` AS `label` from `premiumandfee_label` where ((`premiumandfee_label`.`locale` = 'nl') and (`premiumandfee_label`.`id` = `premiumandfee`.`id`))) AS `labelNL` from ((((((((`premiumandfeequantification` join `contract`) join `authgroup`) join `mission`) join `occupation`) join `actualotherpremiumandfee`) join `premiumandfee`) join `premiumandfeetype`) join `otherpremiumandfee`) where ((`contract`.`id` = `premiumandfeequantification`.`contractId`) and (`contract`.`responsibleGroupId` = `authgroup`.`id`) and (`mission`.`id` = `occupation`.`missionOccupationsId`) and (`occupation`.`id` = `contract`.`occupationContractsId`) and (`premiumandfeequantification`.`actualPremiumAndFeeId` = `actualotherpremiumandfee`.`id`) and (`premiumandfee`.`id` = `actualotherpremiumandfee`.`id`) and (`otherpremiumandfee`.`id` = `premiumandfee`.`premiumAndFeeTypeId`) and (`premiumandfeetype`.`id` = `otherpremiumandfee`.`id`))) union (select `premiumandfeequantification`.`creationdate` AS `creationdate`, `premiumandfeequantification`.`lastupdateDate` AS `lastupdateDate`, `premiumandfeequantification`.`id` AS `hoursId`,`premiumandfeequantification`.`date` AS `hoursDate`,`contract`.`workerName` AS `workerName`,`contract`.`id` AS `contractId`,`mission`.`workerMissionsId` AS `workerId`,`mission`.`customerId` AS `companyId`,`contract`.`customerName` AS `customerName`,`authgroup`.`name` AS `groupName`,`authgroup`.`corporateId` AS `corporateId`,`mission`.`workerStatus` AS `workerStatus`,0 AS `hoursAmount`,week(`premiumandfeequantification`.`date`,1) AS `hoursWeek`,year(`premiumandfeequantification`.`date`) AS `hoursYear`,`premiumandfeetype`.`businessCode` AS `hoursTypeBusinessCode`,0 AS `brutByHour`,`actualmealticket`.`faceValue` AS `paideValue`,`actualmealticket`.`invoiceValue` AS `invoicedValue`,'AMOUNT' AS `payUnit`,'AMOUNT' AS `invoiceUnit`,0 AS `coefficient`,`premiumandfeequantification`.`invoicedAmount` AS `invoiced`,`premiumandfeequantification`.`cost` AS `payed`,'MEALTICKET' AS `dataType`,`contract`.`orderNumber` AS `orderNumber`,`premiumandfeetype`.`socialSecretariatRef` AS `socialSecretariatRef`,`premiumandfeequantification`.`invoiceId` AS `invoiceId`,`premiumandfeequantification`.`exportBatchId` AS `exportBatchId`,`actualmealticket`.`workerParticipation` AS `workerParticipation`,`premiumandfeequantification`.`amount` AS `qt`,`premiumandfeequantification`.`orderNumber` AS `prestationOrderNumber`,(select `premiumandfee_label`.`label` AS `label` from `premiumandfee_label` where ((`premiumandfee_label`.`locale` = 'fr') and (`premiumandfee_label`.`id` = `premiumandfee`.`id`))) AS `labelFR`,(select `premiumandfee_label`.`label` AS `label` from `premiumandfee_label` where ((`premiumandfee_label`.`locale` = 'nl') and (`premiumandfee_label`.`id` = `premiumandfee`.`id`))) AS `labelNL` from ((((((((`premiumandfeequantification` join `contract`) join `authgroup`) join `mission`) join `occupation`) join `actualmealticket`) join `premiumandfee`) join `premiumandfeetype`) join `mealticket`) where ((`contract`.`id` = `premiumandfeequantification`.`contractId`) and (`contract`.`responsibleGroupId` = `authgroup`.`id`) and (`mission`.`id` = `occupation`.`missionOccupationsId`) and (`occupation`.`id` = `contract`.`occupationContractsId`) and (`premiumandfeequantification`.`actualPremiumAndFeeId` = `actualmealticket`.`id`) and (`premiumandfee`.`id` = `actualmealticket`.`id`) and (`mealticket`.`id` = `premiumandfee`.`premiumAndFeeTypeId`) and (`premiumandfeetype`.`id` = `mealticket`.`id`))); |
Contract View
- id : id van het contract (opgelet deze id kan veranderd worden op basis van aanpassing)
- contractBusinessId : id business van het contract
- label : functie
- startDate : Begin datum van het contract
- theoricalEndDate : Einde datum van het contract
- practicalEndDate : Effectieve einde datum
- Enable : geactiveerd contract (J/N)
- workerName : Naam van de werknemmer
- customerName : Naam van de klant
- occupationContractsId : id van de tewerkstelling
- abstractCompanyId : id van de firma (van de persooncontact)
- companyId : id van de firma
- workermissionId : id van de werknemmer
- missionBusinessId : id van de opdracht (voor de importatie van excel gebaseerd om business id van ISIWEB)
- name : Groep Naam
- weeklyTime : Uren per week
- annualTime : Uren per week op jaar basis
- partialTime : Uren per week deeltijd
- 13/10/2009 :
- externalRef van de opdracht (link naar secr.soc. manueel)
- valueFace : maaltijdcheque waard
Aantal dagen van studenten
select studentquarter.type, studentquarter.year, studentquarter.nbOtherDays, studentquarter.nbWorkingDays, contract.workerName, contract.customerName from studentquarter, person, contract, occupation, mission where studentquarter.workerId=person.id and mission.workerMissionsId = person.id and contract.occupationContractsId=occupation.id and occupation.missionOccupationsId=mission.id and contract.enabled=1 and contract.cancelled=0 group by studentquarter.type, studentquarter.year, contract.workerName, contract.customerName
Joint committee
* Mission :
select * from mission, position, positionattributesforoccupation where mission.id=position.id and position.positionAttributesForOccupationId=positionattributesforoccupation.id
* Occupation :
select * from occupation, positionattributesforoccupation where occupation.positionAttributesForOccupationId=positionattributesforoccupation.id
* Contract :
select * from contract, positionattributesforoccupation where contract.positionAttributesForOccupationId=positionattributesforoccupation.id
Mealticket Olap
* Neemt alle maaltiojdcheque per bestelling
DROP VIEW IF EXISTS `mealticketbatcholap`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`bots`@`%` SQL SECURITY DEFINER VIEW `mealticketbatcholap` AS select `mealticketbatch`.`creationDate` AS `creationDate`,`mealticketbatch`.`limitDate` AS `limitDate`,`premiumandfeequantification`.`id` AS `hoursId`,`premiumandfeequantification`.`date` AS `hoursDate`,`contract`.`workerName` AS `workerName`,`mission`.`workerMissionsId` AS `workerId`,`mission`.`abstractCompanyId` AS `companyId`,`contract`.`customerName` AS `customerName`,`authgroup`.`name` AS `groupName`,`mission`.`workerStatus` AS `workerStatus`,0 AS `hoursAmount`,week(`premiumandfeequantification`.`date`,1) AS `hoursWeek`,year(`premiumandfeequantification`.`date`) AS `hoursYear`,`premiumandfeetype`.`businessCode` AS `hoursTypeBusinessCode`,0 AS `brutByHour`,0 AS `paideValue`,`actualmealticket`.`invoiceValue` AS `invoicedValue`,'AMOUNT' AS `payUnit`,'AMOUNT' AS `invoiceUnit`,0 AS `coefficient`,`premiumandfeequantification`.`invoicedAmount` AS `invoiced`,(`premiumandfeequantification`.`amount` * (`actualmealticket`.`faceValue` - `actualmealticket`.`workerParticipation`)) AS `payed`,'MEALTICKET' AS `dataType`,`actualmealticket`.`faceValue` AS `faceValue` from (((((((((`premiumandfeequantification` join `contract`) join `authgroup`) join `mission`) join `occupation`) join `actualmealticket`) join `premiumandfee`) join `premiumandfeetype`) join `mealticket`) join `mealticketbatch`) where ((`contract`.`id` = `premiumandfeequantification`.`contractId`) and (`contract`.`responsibleGroupId` = `authgroup`.`id`) and (`mission`.`id` = `occupation`.`missionOccupationsId`) and (`occupation`.`id` = `contract`.`occupationContractsId`) and (`premiumandfeequantification`.`actualPremiumAndFeeId` = `actualmealticket`.`id`) and (`premiumandfee`.`id` = `actualmealticket`.`id`) and (`mealticket`.`id` = `premiumandfee`.`premiumAndFeeTypeId`) and (`premiumandfeetype`.`id` = `mealticket`.`id`) and (`mealticketbatch`.`id` = `premiumandfeequantification`.`mealTicketBatchId`));