Boost Database

• QuantificationId : id van de code
• QuantificationDate : datum van de uren/Premies& kosten
• Workername : naam van de werknemmer
• workerContractId : Id van het workercontract
• workerId : ID van de werknemmer
• customerId : Id van de klant
• customerName : Naam van de klant
• Salary : Uurloon van de werknemmer
• AgencyName : Naam van de Groep
• Amount : Aantal van uren, …
• dateWeek : week van de datum
• dateYear : Jaar van de datum
• quantificationType : Soort van quantification : HOURS - uren, BONUS - premies, COST - kosten
• QuantificationState : Statuut van de bewerking (exported of niet)
• SocialSecretariatId : Code voor het social secretariaat
• payRate : % van de betling
• label : omschrijving van de quantification
• exportBatchId : Id van de link naar soc.secr.

• Voorschotten in het overzicht toevoegen

SELECT quantification.id AS quantificationId, quantification.date AS quantificationDate, worker.lastName + ' ' + worker.firstName AS workerName, worker_contract.id AS workerContractId, worker.id AS workerId, customer.id AS customerId, customer.lastName + ' ' + customer.firstName AS customerName, agency.name AS agencyName, quantification.amount AS amout, DATEPART(week, quantification.date) AS dateWeek, YEAR(quantification.date) AS dateYear, occupation.salary AS Salary, occupation.hours AS Q, 'HOURS' AS quantificationType, dbo.hours.state AS QuantificationState, q1.socialSecretariatId AS SocialSecretariatId, q1.payRate, q1.onssType, qualification_label.label AS label, quantification.exportBatchId FROM dbo.occupation_table AS occupation INNER JOIN dbo.customer AS customer RIGHT OUTER JOIN dbo.occurrence AS occurrence RIGHT OUTER JOIN dbo.worker_contract AS worker_contract INNER JOIN dbo.workertimeslot_quantification AS workertimeslot_quantification INNER JOIN dbo.quantification AS quantification ON workertimeslot_quantification.id = quantification.id INNER JOIN dbo.worker AS worker INNER JOIN dbo.worker_timeslot AS worker_timeslot ON worker.id = worker_timeslot.workerId ON workertimeslot_quantification.timeSlotId = worker_timeslot.id ON worker_contract.workerId = worker.id INNER JOIN dbo.agency AS agency ON worker.preferentialAgencyId = agency.id ON occurrence.currentAssignationId = workertimeslot_quantification.timeSlotId LEFT OUTER JOIN dbo.request AS request ON occurrence.requestId = request.id ON customer.id = request.customerId ON occupation.contractId = worker_contract.id INNER JOIN dbo.hours ON dbo.hours.id = quantification.id INNER JOIN dbo.qualification AS q1 ON q1.id = dbo.hours.qualificationId INNER JOIN qualification_label ON qualification_label.id = q1.id LEFT JOIN dbo.export_batch_quantification AS export_batch_quantification ON quantification.id = export_batch_quantification.quantificationID WHERE ((quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND qualification_label.locale = 'fr' UNION SELECT dbo.quantification.id AS quantificationId, dbo.quantification.date AS quantificationDate, dbo.worker.lastName + ' ' + dbo.worker.firstName AS workerName, dbo.worker_contract.id AS workerContractId, dbo.worker.id AS workerId, NULL AS customerId, '' AS customerName, dbo.agency.name AS agencyName, dbo.quantification.amount AS amout, DATEPART(week, dbo.quantification.date) AS dateWeek, YEAR(dbo.quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'EXPENSE' AS quantificationType, dbo.premiumandfeeamount.state AS QuantificationState, dbo.expense.socialSecretariatId, 0 AS payrate, '' AS OnssType, expense_label.label AS label, quantification.exportBatchId FROM dbo.worker_contract INNER JOIN dbo.occupation_table AS occupation ON dbo.worker_contract.id = occupation.contractId INNER JOIN dbo.worker ON dbo.worker_contract.workerId = dbo.worker.id INNER JOIN dbo.worker_quantification ON dbo.worker.id = dbo.worker_quantification.workerId INNER JOIN dbo.quantification ON dbo.worker_quantification.id = dbo.quantification.id INNER JOIN dbo.premiumandfeeamount ON dbo.worker_quantification.id = dbo.premiumandfeeamount.id INNER JOIN dbo.expense ON dbo.premiumandfeeamount.premiumAndFeeId = dbo.expense.id INNER JOIN expense_label ON expense_label.id = expense.id INNER JOIN dbo.agency ON dbo.worker.preferentialAgencyId = dbo.agency.id LEFT JOIN dbo.export_batch_quantification AS export_batch_quantification ON quantification.id = export_batch_quantification.quantificationID WHERE ((dbo.quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (dbo.quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND expense_label.locale = 'fr' UNION SELECT dbo.quantification.id AS quantificationId, dbo.quantification.date AS quantificationDate, dbo.worker.lastName + ' ' + dbo.worker.firstName AS workerName, dbo.worker_contract.id AS workerContractId, dbo.worker.id AS workerId, NULL AS customerId, '' AS customerName, dbo.agency.name AS agencyName, dbo.quantification.amount AS amout, DATEPART(week, dbo.quantification.date) AS dateWeek, YEAR(dbo.quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'benefit' AS quantificationType, dbo.premiumandfeeamount.state AS QuantificationState, dbo.benefit.socialSecretariatId, 0 AS payrate, '' AS OnssType, benefit_label.label AS label, quantification.exportBatchId FROM dbo.worker_contract INNER JOIN dbo.occupation_table AS occupation ON dbo.worker_contract.id = occupation.contractId INNER JOIN dbo.worker ON dbo.worker_contract.workerId = dbo.worker.id INNER JOIN dbo.worker_quantification ON dbo.worker.id = dbo.worker_quantification.workerId INNER JOIN dbo.quantification ON dbo.worker_quantification.id = dbo.quantification.id INNER JOIN dbo.premiumandfeeamount ON dbo.worker_quantification.id = dbo.premiumandfeeamount.id INNER JOIN dbo.benefit ON dbo.premiumandfeeamount.premiumAndFeeId = dbo.benefit.id INNER JOIN benefit_label ON benefit_label.id = dbo.benefit.id INNER JOIN dbo.agency ON dbo.worker.preferentialAgencyId = dbo.agency.id LEFT JOIN dbo.export_batch_quantification AS export_batch_quantification ON quantification.id = export_batch_quantification.quantificationID WHERE ((dbo.quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (dbo.quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND benefit_label.locale = 'fr' UNION SELECT dbo.quantification.id AS quantificationId, dbo.quantification.date AS quantificationDate, dbo.worker.lastName + ' ' + dbo.worker.firstName AS workerName, dbo.worker_contract.id AS workerContractId, dbo.worker.id AS workerId, NULL AS customerId, '' AS customerName, dbo.agency.name AS agencyName, dbo.quantification.amount AS amout, DATEPART(week, dbo.quantification.date) AS dateWeek, YEAR(dbo.quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'premium' AS quantificationType, dbo.premiumandfeeamount.state AS QuantificationState, dbo.premium.socialSecretariatId, 0 AS payrate, '' AS OnssType, premium_label.label AS label, quantification.exportBatchId FROM dbo.worker_contract INNER JOIN dbo.occupation_table AS occupation ON dbo.worker_contract.id = occupation.contractId INNER JOIN dbo.worker ON dbo.worker_contract.workerId = dbo.worker.id INNER JOIN dbo.worker_quantification ON dbo.worker.id = dbo.worker_quantification.workerId INNER JOIN dbo.quantification ON dbo.worker_quantification.id = dbo.quantification.id INNER JOIN dbo.premiumandfeeamount ON dbo.worker_quantification.id = dbo.premiumandfeeamount.id INNER JOIN dbo.premium ON dbo.premiumandfeeamount.premiumAndFeeId = dbo.premium.id INNER JOIN premium_label ON premium_label.id = premium.id INNER JOIN dbo.agency ON dbo.worker.preferentialAgencyId = dbo.agency.id LEFT JOIN dbo.export_batch_quantification AS export_batch_quantification ON quantification.id = export_batch_quantification.quantificationID WHERE ((dbo.quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (dbo.quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND premium_label.locale = 'fr' UNION SELECT dbo.quantification.id AS quantificationId, dbo.quantification.date AS quantificationDate, dbo.worker.lastName + ' ' + dbo.worker.firstName AS workerName, dbo.worker_contract.id AS workerContractId, dbo.worker.id AS workerId, NULL AS customerId, '' AS customerName, dbo.agency.name AS agencyName, dbo.quantification.amount AS amout, DATEPART(week, dbo.quantification.date) AS dateWeek, YEAR(dbo.quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'mealticket' AS quantificationType, dbo.premiumandfeeamount.state AS QuantificationState, 'X01', 0 AS payrate, '' AS OnssType, meal_ticket_label.label AS label, quantification.exportBatchId FROM dbo.worker_contract INNER JOIN dbo.occupation_table AS occupation ON dbo.worker_contract.id = occupation.contractId INNER JOIN dbo.worker ON dbo.worker_contract.workerId = dbo.worker.id INNER JOIN dbo.worker_quantification ON dbo.worker.id = dbo.worker_quantification.workerId INNER JOIN dbo.quantification ON dbo.worker_quantification.id = dbo.quantification.id INNER JOIN dbo.premiumandfeeamount ON dbo.worker_quantification.id = dbo.premiumandfeeamount.id INNER JOIN dbo.mealticket ON dbo.premiumandfeeamount.premiumAndFeeId = dbo.mealticket.id INNER JOIN meal_ticket_label ON meal_ticket_label.id = mealticket.id INNER JOIN dbo.agency ON dbo.worker.preferentialAgencyId = dbo.agency.id LEFT JOIN dbo.export_batch_quantification AS export_batch_quantification ON quantification.id = export_batch_quantification.quantificationID WHERE ((dbo.quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (dbo.quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND meal_ticket_label.locale = 'fr' UNION SELECT dbo.quantification.id AS quantificationId, dbo.quantification.date AS quantificationDate, dbo.worker.lastName + ' ' + dbo.worker.firstName AS workerName, dbo.worker_contract.id AS workerContractId, dbo.worker.id AS workerId, NULL AS customerId, '' AS customerName, dbo.agency.name AS agencyName, dbo.quantification.amount AS amout, DATEPART(week, dbo.quantification.date) AS dateWeek, YEAR(dbo.quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'Acompte' AS quantificationType, dbo.advance.state AS QuantificationState, 'acc' AS socialSecretariatId, 0 AS payrate, '' AS OnssType, 'Acompte' AS label, quantification.exportBatchId FROM dbo.worker_contract INNER JOIN dbo.occupation_table AS occupation ON dbo.worker_contract.id = occupation.contractId INNER JOIN dbo.worker ON dbo.worker_contract.workerId = dbo.worker.id INNER JOIN dbo.worker_quantification ON dbo.worker.id = dbo.worker_quantification.workerId INNER JOIN dbo.quantification ON dbo.worker_quantification.id = dbo.quantification.id INNER JOIN dbo.advance ON dbo.worker_quantification.id = dbo.advance.id INNER JOIN dbo.agency ON dbo.worker.preferentialAgencyId = dbo.agency.id LEFT JOIN dbo.export_batch_quantification AS export_batch_quantification ON quantification.id = export_batch_quantification.quantificationID WHERE ((dbo.quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (dbo.quantification.date > occupation.startDate) AND (occupation.endDate IS NULL))

SELECT quantification.id AS quantificationId, quantification.date AS quantificationDate, worker.lastName + ' ' + worker.firstName AS workerName, worker_contract.id AS workerContractId, worker.id AS workerId, customer.id AS customerId, customer.lastName + ' ' + customer.firstName AS customerName, agency.name AS agencyName, quantification.amount AS amout, DATE_FORMAT(quantification.date, '%u') AS dateWeek, YEAR(quantification.date) AS dateYear, occupation.salary AS Salary, occupation.hours AS Q, 'HOURS' AS quantificationType, hours.state AS QuantificationState, q1.socialSecretariatId AS SocialSecretariatId, q1.payRate, q1.onssType, qualification_label.label AS label, quantification.exportBatchId FROM occupation AS occupation INNER JOIN customer AS customer RIGHT OUTER JOIN occurrence AS occurrence RIGHT OUTER JOIN worker_contract AS worker_contract INNER JOIN workertimeslot_quantification AS workertimeslot_quantification INNER JOIN quantification AS quantification ON workertimeslot_quantification.id = quantification.id INNER JOIN worker AS worker INNER JOIN worker_timeslot AS worker_timeslot ON worker.id = worker_timeslot.workerId AND workertimeslot_quantification.timeSlotId = worker_timeslot.id AND worker_contract.workerId = worker.id INNER JOIN agency AS agency ON worker.preferentialAgencyId = agency.id ON occurrence.currentAssignationId = workertimeslot_quantification.timeSlotId LEFT OUTER JOIN request AS request ON occurrence.requestId = request.id ON customer.id = request.customerId AND occupation.workerContractId = worker_contract.id INNER JOIN hours ON hours.id = quantification.id INNER JOIN qualification AS q1 ON q1.id = hours.qualificationId INNER JOIN qualification_label ON qualification_label.id = q1.id WHERE ((quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND qualification_label.locale = 'fr' AND worker_contract.enabled = 1 AND worker_contract.state <> 'CANCELLED' UNION SELECT quantification.id AS quantificationId, quantification.date AS quantificationDate, worker.lastName + ' ' + worker.firstName AS workerName, worker_contract.id AS workerContractId, worker.id AS workerId, NULL AS customerId, '' AS customerName, agency.name AS agencyName, quantification.amount AS amout, DATE_FORMAT(quantification.date, '%u') AS dateWeek, YEAR(quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'EXPENSE' AS quantificationType, premiumandfeeamount.state AS QuantificationState, expense.socialSecretariatId, 0 AS payrate, '' AS OnssType, expense_label.label AS label, quantification.exportBatchId FROM worker_contract INNER JOIN occupation AS occupation ON worker_contract.id = occupation.workerContractId INNER JOIN worker ON worker_contract.workerId = worker.id INNER JOIN worker_quantification ON worker.id = worker_quantification.workerId INNER JOIN quantification ON worker_quantification.id = quantification.id INNER JOIN premiumandfeeamount ON worker_quantification.id = premiumandfeeamount.id INNER JOIN expense ON premiumandfeeamount.premiumAndFeeId = expense.id INNER JOIN expense_label ON expense_label.id = expense.id INNER JOIN agency ON worker.preferentialAgencyId = agency.id WHERE ((quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND expense_label.locale = 'fr' AND worker_contract.enabled = 1 AND worker_contract.state <> 'CANCELLED' UNION SELECT quantification.id AS quantificationId, quantification.date AS quantificationDate, worker.lastName + ' ' + worker.firstName AS workerName, worker_contract.id AS workerContractId, worker.id AS workerId, NULL AS customerId, '' AS customerName, agency.name AS agencyName, quantification.amount AS amout, DATE_FORMAT(quantification.date, '%u') AS dateWeek, YEAR(quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'benefit' AS quantificationType, premiumandfeeamount.state AS QuantificationState, benefit.socialSecretariatId, 0 AS payrate, '' AS OnssType, benefit_label.label AS label, quantification.exportBatchId FROM worker_contract INNER JOIN occupation AS occupation ON worker_contract.id = occupation.workerContractId INNER JOIN worker ON worker_contract.workerId = worker.id INNER JOIN worker_quantification ON worker.id = worker_quantification.workerId INNER JOIN quantification ON worker_quantification.id = quantification.id INNER JOIN premiumandfeeamount ON worker_quantification.id = premiumandfeeamount.id INNER JOIN benefit ON premiumandfeeamount.premiumAndFeeId = benefit.id INNER JOIN benefit_label ON benefit_label.id = benefit.id INNER JOIN agency ON worker.preferentialAgencyId = agency.id WHERE ((quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND benefit_label.locale = 'fr' AND worker_contract.enabled = 1 AND worker_contract.state <> 'CANCELLED' UNION SELECT quantification.id AS quantificationId, quantification.date AS quantificationDate, worker.lastName + ' ' + worker.firstName AS workerName, worker_contract.id AS workerContractId, worker.id AS workerId, NULL AS customerId, '' AS customerName, agency.name AS agencyName, quantification.amount AS amout, DATE_FORMAT(quantification.date, '%u') AS dateWeek, YEAR(quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'premium' AS quantificationType, premiumandfeeamount.state AS QuantificationState, premium.socialSecretariatId, 0 AS payrate, '' AS OnssType, premium_label.label AS label, quantification.exportBatchId FROM worker_contract INNER JOIN occupation AS occupation ON worker_contract.id = occupation.workerContractId INNER JOIN worker ON worker_contract.workerId = worker.id INNER JOIN worker_quantification ON worker.id = worker_quantification.workerId INNER JOIN quantification ON worker_quantification.id = quantification.id INNER JOIN premiumandfeeamount ON worker_quantification.id = premiumandfeeamount.id INNER JOIN premium ON premiumandfeeamount.premiumAndFeeId = premium.id INNER JOIN premium_label ON premium_label.id = premium.id INNER JOIN agency ON worker.preferentialAgencyId = agency.id WHERE ((quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND premium_label.locale = 'fr' AND worker_contract.enabled = 1 AND worker_contract.state <> 'CANCELLED' UNION SELECT quantification.id AS quantificationId, quantification.date AS quantificationDate, worker.lastName + ' ' + worker.firstName AS workerName, worker_contract.id AS workerContractId, worker.id AS workerId, NULL AS customerId, '' AS customerName, agency.name AS agencyName, quantification.amount AS amout, DATE_FORMAT(quantification.date, '%u') AS dateWeek, YEAR(quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'mealticket' AS quantificationType, premiumandfeeamount.state AS QuantificationState, 'X01', 0 AS payrate, '' AS OnssType, meal_ticket_label.label AS label, quantification.exportBatchId FROM worker_contract INNER JOIN occupation AS occupation ON worker_contract.id = occupation.workerContractId INNER JOIN worker ON worker_contract.workerId = worker.id INNER JOIN worker_quantification ON worker.id = worker_quantification.workerId INNER JOIN quantification ON worker_quantification.id = quantification.id INNER JOIN premiumandfeeamount ON worker_quantification.id = premiumandfeeamount.id INNER JOIN mealticket ON premiumandfeeamount.premiumAndFeeId = mealticket.id INNER JOIN meal_ticket_label ON meal_ticket_label.id = mealticket.id INNER JOIN agency ON worker.preferentialAgencyId = agency.id WHERE ((quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND meal_ticket_label.locale = 'fr' AND worker_contract.enabled = 1 AND worker_contract.state <> 'CANCELLED' UNION SELECT quantification.id AS quantificationId, quantification.date AS quantificationDate, worker.lastName + ' ' + worker.firstName AS workerName, worker_contract.id AS workerContractId, worker.id AS workerId, NULL AS customerId, '' AS customerName, agency.name AS agencyName, quantification.amount AS amout, DATE_FORMAT(quantification.date, '%u') AS dateWeek, YEAR(quantification.date) AS dateYear, occupation.salary AS Expr1, occupation.hours AS Q, 'Acompte' AS quantificationType, advance.state AS QuantificationState, 'acc' AS socialSecretariatId, 0 AS payrate, '' AS OnssType, 'Acompte' AS label, quantification.exportBatchId FROM worker_contract INNER JOIN occupation AS occupation ON worker_contract.id = occupation.workerContractId INNER JOIN worker ON worker_contract.workerId = worker.id INNER JOIN worker_quantification ON worker.id = worker_quantification.workerId INNER JOIN quantification ON worker_quantification.id = quantification.id INNER JOIN advance ON worker_quantification.id = advance.id INNER JOIN agency ON worker.preferentialAgencyId = agency.id WHERE ((quantification.date BETWEEN occupation.startDate AND occupation.endDate) OR (quantification.date > occupation.startDate) AND (occupation.endDate IS NULL)) AND worker_contract.enabled = 1 AND worker_contract.state <> 'CANCELLED'

• Sociaal Fonds - eindejaarspremie: fondssoc.zip (de selectie van prestatiedata wijzigen en rekening houden met codes van het sociaal secretariaat)
• Controle des prestations : controle.zip (de selectie om het kiezen van prestatiedata wijzigen)