isiweb:database

L'envoi a échoué. Les autorisations sont-elles correctes ?

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

isiweb:database [2019/08/28 09:10] (Version actuelle)
Ligne 1: Ligne 1:
  
 +
 +
 +
 +====== Reports ======
 +  * Rapport des régions : {{:isiweb:rapport_region_wallone.zip|}}
 +
 +====== Database ======
 +
 +
 +
 +
 +===== 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
 +
 +
 +  - SpecificOtherPremiumandFee
 +    - Invoiceunit 
 +      - AMOUNT : montant facturé
 +      - COEFFICIENT : Coefficient
 +      - NONE : pas facturé
 +    - InvoiceSubUnit :
 +      - AMOUNT_NO_ADMIN_VALUE : montant facturé définit dans le p&f
 +      - COEFFICIENT_ADMIN_VALUE : coef. définit en admin (et se retrouvant dans le p&F)
 +      - COEFFICIENT_MISSION : coef définit dans le contrat
 +      - COEFFICIENT_NO_ADMIN_VALUE : coef définit dans le p&f
 +===== Worker =====
 +
 +  - Person (id) : entité reprenant les informations d'une personne
 +    - Worker (workerId) (pour les travailleurs)
 +    - Contact
 +    - User
 +    - Account (accountid = id )
 +
 +  - Agent (agentid)
 +    - Person (id)
 +      - account (accountid = id)
 +
 +===== Customer =====
 +  - companyNumber : champs contenant le numéro d'entreprise
 +
 +===== Company =====
 +
 +  - AbstractCompany
 +    * businessId : code business
 +    * socialSecretariatPartitioningRef : référence sec.soc.
 +    * accountigCompanyRef : référence comptabilité
 +    * availableSelfservice : service service disponible
 +    * mainContactMedias_xxx : Téléphone, email, fax, adresse
 +    * relantionTypeId : deprecated
 +    * companysourceid : source de l'entreprise
 +    - Businessentitycompany (Businessentitycompany.id = abstractcompany.id)
 +      * Name : nom
 +      * Enabled : actif (O/N)
 +      * frozen : bloqué (O/N)
 +      * freezingreason : : raison du blocage
 +      * creationdate : date de création
 +      * lastupdatedate : date de mise à jour
 +      * preferentialagentid : agent préférentiel
 +      * automaticcontactid : contact par défaut
 +      * defaultGroupId : groupe par défaut
 +      * externalrefid : référence externe (zone libre pour mapping vers ancien ou autre système)
 +    - Company (company.companyid = abstractcompany.id)
 +      * Name2 : C/O de l'entreprise
 +      * companynumber : numéro d'entreprise
 +      * website : site web
 +      * onssNumber : numéro ONSS
 +      * PermanentWorkerCount : nombre de travailleur fixe (graydon)
 +      * Score : score de Graydon
 +      * Validated : validé (O/N)
 +      * UnionRepresentative (Yes/NO/?)
 +      * CompanySizeId : taille de l'entreprise
 +      * socialReasonId : raison social
 +      * relationTypeId : type de relation
 +      * importMissionId : import utilisant le code mission (O/N)
 +      * CompanysizeConstruct : taille de l'entreprise de construction
 +      * niss : niss de la personne physique
 +      * mailCorrespondance : reception de mail (O/N)
 +      - Holding : enseigne ( company.holdingcompaniesId = holding.id )
 +      - ContactCompany
 +        * Preferentialcontactcompany : rempli avec l'id de la company préférentielle (= zone contactsCompanyid) sinon null
 +        * contactscompanyid : rempli avec l'id de la companie liée
 +        * contactcompanyid : id du contact lié
 +        - Contact
 +          * contactType : type de contact
 +          * contactMedias_xxx : téléphone, mobile, fax, adresse 
 +          - Person : personne physique (identique à worker et user) ( contact.personid = persone.id )
 +            * Firstname : prénom
 +            * LastName : nom
 +            * personalPhone : téléphone personnel
 +            * personalMobile : GSM
 +            * language : langue
 +            * enabled : activé (o/n)
 +            * nationalytid : code nationalité
 +            * title : titre
 +            - account : compte self-service (contact.accountid = account.id)
 +
 +===== Convention =====
 +
 +  * Convention : Comment est facturé géré le client 
 +    * BusinessId : identification de la convention
 +    * enabled : actif (O/N)
 +    * creationDate : date de création de la convention
 +    * startDate : date de début de la convention
 +    * endDate : date de fin de la convention
 +    * identiyCardRequired : carte d'identité requise
 +    * medicalExamination : examen médical requis
 +    * ContractGroupingType : mode de regroupement des contrats (BYSTAT, BYCONTACTPERSON, BYCOMMAND)
 +    * ContractPeriodicity : fréquence de regroupement (WEEK, MONTH,NONE)
 +    * useTimeSlotGrid : utilise la grille de prestation
 +    * invoiceGroupingType : mode de regroupement des factures (BYSTAT, BYCONTACTPERSON, BYCOMMAND)
 +    * Discount : escompte
 +    * invoicetype : type de facturation (POSITIONS_QUALIFICATION, RESUME)
 +    * invoiceContent : facturation de ce qui est payé ou indéfférent (QUALIFIED, PAID)
 +    * tvaCode : code tva utilisé
 +    * paymentTermid : délai de paiement
 +    * preferentialAgency : code du groupe par défaut
 +    * invoiceFrequency : fréquence de facturation (WEEK, MONTH)
 +    * invoiceGroupingbyMission : mission groupée sur la facture en 1 groupe (au lieu de par contrat) (O/N)
 +    * splitKey : facturation séparée par Semaine, mois, rien (WEEK, MONTH, NONE)
 +    * CompanyName : nom de la société
 +    * NumberOfWorkerContractCopies : nombre de copie contrat travailleur (spécifique pour ce client)
 +    * NumberofCustomerContractCopies :  nombre de copie contrat travailleur (spécifique pour ce client)
 +    * NumberofInvoiceCopies : nombre de copie facture (spécifique pour ce client)
 +    - company : société (convention.companyid = company.companyid) 
 +    - Contact : personne de contact pour la facturation (convention.invoicecontactCompanyid = contact.id)
 +    - Contact : personne de contact pour les contrats (convention.contractcontactCompanyid = contact.id )
 +    - basicPosition : postes liés à la convention (convention.id = basicposition.conventionId)
 +      * BusinessId : code du poste
 +      * enabled : activé (O/N)
 +      * CompanyName : nom de la société
 +      * labelFr : libellé FR
 +      * labelNl : libellé NL
 +      * artist : artiste (O/N)
 +      * AutomaticQualificationForPublicHoliday
 +      - basic_qualification_position : lien vers les qualifications liées au poste
 +      - position : information du poste identique à la mission (basicposition.id = position.id )
 +        * category : categorie
 +        - preferentialAgentid : agent préférentiel
 +        - positionAttributeForOccupationID : attributs de l'occupation (voir plus bas)
 +        - positionAttributeForContractId : attributs du contrat (voir plus bas)
 +        - defaultGroupid : identifiant du groupe
 +        - position_translation : libellé traduit du poste
 +===== Contract =====
 +
 +  * Contract
 +    - Occupation : Occupations caclulées pour les doc.sociaux (contract.occupationContractsId = occupation.id)
 +      - mission : (occupation.missionOccupationsId = mission.id)
 +    - authgroup : Groupes (contract.responsibleGroupId = authgroup.id)
 +    - contractsalary : liste des salaires liés au contrat (.contractid)
 +    - Premiumandfeequantification : liste des prestations liées au contrat (.contractid)
 +    - positionattributesforcontract : attribut du contrat (contract.positionAttributesForcontractId = positionattributesforcontract.id )
 +      * userclassification : classification de la fonction chez l'utilisateur
 +      * professionalqualification : qualification professionnelle
 +      * allocationActiva : Droit a allocation de travail (O/N)
 +      * CoefficientbyDefault : coefficient par défaut du contrat
 +    - positionattributesforoccupation : (contract.positionAttributesForOccupationId = positionattributesforoccupation.id )
 +      * ContractType : type de contrat (CDD, CDI)
 +      * jointcommitteeid : id du la com.par.
 +      - worksystem : (positionattributesforoccupation.workSystemId = worksystem.id)
 +        * weeklytime : nombre d'heure hebdommadaire
 +        * annualtime : nombre d'heure hebdommadaire annuel
 +        * partialtime : nombre d'heure temps partiel
 +        * realworkhours : nombre d'heure réellement prestés
 +        * isPartialTime : temps partiel (o/n)
 +        * days : nombre de jour
 +        * variabletimetable : horaire variable
 +        * cycleworksystem : système d'horaire cyclique
 +        * trialdays : jours d'essai
 +        * CompensationMode : mode de compensation
 +        * Renouvellement de contrat (monday, daily, ...) 
 +    - specific_premiumandfee_positionattributesforcontract : liste des primes et frais liés au contrat (contract.positionAttributesForContractId = specific_premiumandfee_positionattributesforcontract.positionAttributesForContractId )
 +      - specificmealticket : Chèque repas (specific_premiumandfee_positionattributesforcontract.premiumandfeeId = specificmealticket.id )
 +      - ...
 +
 +===== Prestation =====
 +
 +  * Hours : heures
 +    * amount : montant des heures
 +    * creationDate : date de création
 +    * date : date de l'heure
 +    * lastupdatedate : date de la dernière modification
 +    * validatebyagent : validé par l'agent
 +    * nopaid : pas payé
 +    * ordernNumber : bon de commande sur la prestation
 +    * ordernumberOnContract : Bon de commande sur le contrat ? (O/N)
 +    * state : état de la prestation
 +      * NEW : nouvelle
 +      * INVOICED : facturée
 +      * EXPORTED : exportée vers le secr.soc.
 +      * INVOICED_AND_EXPORTED : facturée et exportée
 +    * InvoiceAmount : montant facturé
 +    * brutAmount : montant payé
 +    * cost : coût
 +    * tvarate : taux de tva appliqué
 +    - Contract : Contrat ( contract.id = hours.contractId )
 +      - authgroup : groupe (contract.responsibleGroupId = authgroup.id)
 +      - Occupation : occupation (occupation.id = contract.occupationContractsId)
 +        - mission.id = occupation.missionOccupationsId
 +    - Qualificationposition : ( qualificationposition.id = hours.hoursQualificationId )
 +      * coefficient : coef. appliqué
 +      * InvoicedValue : pourcentage facturé
 +      * PayedValue : pourcentage payé
 +      * rtttopay : (O/N)
 +      * rttToInvoice : (O/N)
 +      * payunit : unité du montant payé (pourcentage ou montant)
 +      * invoiceunit : unité du montant facturé (pourcentage ou montant)
 +      - Hourstype : type d'heure - admin (qualificationposition.hoursType = hourstype.id)
 +    - hoursqualification : ( hoursqualification.id = hours.hoursQualificationId )
 +      * brutbyHour : salaire horaire
 +      - qualificationposition_translations : ( qualificationposition_translations.id = hoursqualification.id )
 +    - Invoice : facture liée (hours.invoiceid = invoice.id)
 +    - Exportbatch : lien sec.soc lié (hours.exportbatchid = exportbatch.id)
 +    - col_hoursid : .
 +
 +
 +
 +  * premiumandfeequantification : 
 +    - Contract : Contrat (
 +      - 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)
 +
 +===== Frontoffice =====
 +
 +==== Recrutement ====
 +
 +    * Workflow
 +      * workingGroupid : Groupe par défaut
 +      - Recruitment (Workflow.id = Recruitment.recruitmentid)
 +      *  
 +
 +===== OLAP View =====
 +
 +==== Hours olap view ====
 +
 +  * Hoursid : id de la prestation
 +  * hoursdate : date de la prestation
 +  * workername : nom du travailleur
 +  * contractid : id du contrat
 +  * workerid : id du travailleur
 +  * companyid : id de la société
 +  * customername : Nom de la société
 +  * groupname : Nom du groupe
 +  * CorporateId : Id de l'entreprise ISIWEB
 +  * workerstatus : ID du statut du travailleur
 +  * hoursamount : Nombre d'heure prestée (si d'application)
 +  * hours week : Semaine de la prestation
 +  * hours year : Année de la prestation
 +  * HoursTypeBusinessCode : Codification du type d'heure (comme rapport mutuelle ou résumé travailleur)
 +  * brutbyhour : Salaire horaire
 +  * paidvalue : prix unitaire du coût
 +  * invoicedvalue : prix unitaire facturé
 +  * payunit : type de prix unitaire (pourcentage, montant)
 +  * invoiceunit : type d'unité de facturation
 +  * coefficient : Coefficient appliqué sur les prestations
 +  * invoiced : Montant total facturé de la ligne prestation
 +  * payed : Montant total payé de la ligne prestation
 +  * datatype : Type de prestation (heure, frais, chèque repas, prime)
 +  * ordernumber : Numéro de bon de commande
 +  * socialSecretariatRef : Code secr.soc.
 +  * invoiceid : ID de la facture (Si vide -> pas encore facturé)
 +  * exportbatchid : id du fichier secr.soc. (si vide -> pas encore envoyé)
 +  * workerParticipation : Montant de la participation chèque repas
 +  * creationDate : date de création de la prestation
 +  * LastupdateDate : date de dernière mise à jour
 +
 +  * 2009/09/03 : ajout de l'information du code secr.soc, Id de facture et de lien secr.soc
 +  * 2009/09/08 : correction du montant du coût travailleur
 +  * 2009/09/11 : Ajout de l'information de la société (companyId) et du contract (contractId)
 +  * 2009/12/01 : Ajout de la valeur retenue du CR du travailleur
 +  * 2010/02/01 : Correction du lien vers le client
 +  * 2010/04/21 : Ajout de QT de primes et frais et pu du chèque repas
 +  * 2010/05/05 : Ajout des libellés de qualification et bon de commande prestation
 +  * 2010/06/21 : Correction des heures payée et facturé par montant
 +  * 2011/01/21 : Ajoute des dates de création et mise à jour
 +
 +%%
 +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`)));%%
 +
 +* SQL
 +
 +%%
 +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, datepart(week, hours.date) 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, (CASE WHEN hours.invoicedAmount = 0 or  hours.invoicedAmount is null  THEN hours.amount * (qualificationposition.invoicedValue / 100) 
 +                      * hoursqualification.brutByHour * qualificationposition.coefficient ELSE hours.invoicedAmount END) 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, contract, authgroup, qualificationposition, hoursqualification, hourstype, mission, 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, datepart(week, 
 +                      premiumandfeequantification.date) 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, contract, authgroup, mission, occupation, actualotherpremiumandfee, premiumandfee, premiumandfeetype, 
 +                      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, DatePart(week, 
 +                      premiumandfeequantification.date) 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, contract, authgroup, mission, occupation, actualmealticket, premiumandfee, premiumandfeetype, 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%%
 +
 +==== Vue horaire contrat ====
 +
 +Pour visualiser les horaires et produire une fichier d'encodage de prestation
 +
 +  * ID : Id du contrat vers la table contract (dans cette table se trouve les noms des candidats et clients)
 +
 +%%
 +DROP VIEW IF EXISTS `timetable_view`;
 +CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`bots`@`%` SQL SECURITY DEFINER VIEW `timetable_view` AS (select `timetable`.`id` AS `id`,`timetable`.`cycleWeekNumber` AS `cycleWeekNumber`,`timetable`.`numberOfHours` AS `numberOfHours`,`timetable`.`creationDate` AS `creationDate`,`timetable`.`start_hours` AS `start_hours`,`timetable`.`start_minutes` AS `start_minutes`,`timetable`.`end_hours` AS `end_hours`,`timetable`.`end_minutes` AS `end_minutes`,`timetable`.`dayOfWeek` AS `dayOfWeek`,`timetable`.`timeTableDivision` AS `timeTableDivision` from ((((`contract` join `positionattributesforoccupation`) join `worksystem`) join `worksystem_timetable`) join `timetable`) where ((`contract`.`positionAttributesForOccupationId` = `positionattributesforoccupation`.`id`) and (`positionattributesforoccupation`.`workSystemId` = `worksystem`.`id`) and (`worksystem_timetable`.`workSystemId` = `worksystem`.`id`) and (`timetable`.`id` = `worksystem_timetable`.`timeTableId`) and isnull(`worksystem`.`timeTableOtherId`) and (`timetable`.`id` = `worksystem_timetable`.`timeTableId`))) union (select `timetable`.`id` AS `id`,`timetable`.`cycleWeekNumber` AS `cycleWeekNumber`,`timetable`.`numberOfHours` AS `numberOfHours`,`timetable`.`creationDate` AS `creationDate`,`timetable`.`start_hours` AS `start_hours`,`timetable`.`start_minutes` AS `start_minutes`,`timetable`.`end_hours` AS `end_hours`,`timetable`.`end_minutes` AS `end_minutes`,`timetable`.`dayOfWeek` AS `dayOfWeek`,`timetable`.`timeTableDivision` AS `timeTableDivision` from ((((((`contract` join `positionattributesforoccupation`) join `worksystem`) join `worksystem_timetableother`) join `timetableother`) join `timetableother_timetable`) join `timetable`) where ((`contract`.`positionAttributesForOccupationId` = `positionattributesforoccupation`.`id`) and (`positionattributesforoccupation`.`workSystemId` = `worksystem`.`id`) and (`worksystem_timetableother`.`workSystemId` = `worksystem`.`id`) and (`worksystem_timetableother`.`timeTableOtherId` = `timetableother`.`id`) and (`timetable`.`id` = `timetableother_timetable`.`timeTableId`) and (`timetableother_timetable`.`timeTableOtherId` = `timetableother`.`id`) and (`worksystem`.`timeTableOtherId` = `timetableother`.`id`) and (`worksystem`.`timeTableOtherId` is not null)));
 +%%
 +
 +
 +==== Reductions structurelles ====
 +
 +%%DROP VIEW IF EXISTS `redustru`;
 +CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`bots`@`%` SQL SECURITY DEFINER VIEW `redustru` AS select `hoursolap31`.`hoursYear` AS `hoursYear`, `hoursolap31`.`hoursWeek` AS `hoursWeek`, MIN( `hoursolap31`.`hoursDate` ) AS minDate, MAX(`hoursolap31`.`hoursDate` ) AS maxDate,  `hoursolap31`.`workerStatus` AS `workerStatus`,`hoursolap31`.`corporateId` AS `corporateId`,`hoursolap31`.`hoursDate` AS `hoursDate`,`hoursolap31`.`groupName` AS `groupName`,`hoursolap31`.`workerName` AS `workerName`,sum(if(((`hoursolap31`.`dataType` = 'HOURS') or (`hoursolap31`.`dataType` = 'PREMIUM')),`hoursolap31`.`payed`,0)) AS `BRPA`,`hoursolap31`.`dataType` AS `dataType`,sum(if((`hoursolap31`.`payed` <> 0),`hoursolap31`.`hoursAmount`,0)) AS `hours`,`hoursolap31`.`hoursTypeBusinessCode` AS `hoursTypeBusinessCode`,`occupation1`.`startDate` AS `startDate`,`occupation1`.`endDate` AS `endDate`,`worksystem1`.`weeklyTime` AS `weeklyTime`,`worksystem1`.`annualTime` AS `annualTime`,`worksystem1`.`partialTime` AS `partialTime`,`worksystem1`.`days` AS `days` from ((((`hoursolap` `hoursolap31` join `contract` `contract1` on((`hoursolap31`.`contractId` = `contract1`.`id`))) left join `occupation` `occupation1` on((`contract1`.`occupationContractsId` = `occupation1`.`id`))) join `positionattributesforoccupation` `positionattributesforoccupation1` on((`occupation1`.`positionAttributesForOccupationId` = `positionattributesforoccupation1`.`id`))) join `worksystem` `worksystem1` on((`positionattributesforoccupation1`.`workSystemId` = `worksystem1`.`id`))) group by `hoursolap31`.`hoursYear`, `hoursolap31`.`hoursWeek`, `hoursolap31`.`groupName`,`hoursolap31`.`workerName`,`occupation1`.`startDate`;%%
 +
 +==== Contrat ====
 +
 +  * Vue des contrats
 +    * id : id du contrat (attention celui-ci peut changer s'il y a modification du contrat -> historisation)
 +    * contractBusinessId : id business du contrat (celui-ci peut changer aussi !)
 +    * label : function
 +    * startDate : Date de début du contrat
 +    * theoricalEndDate : date de fin initiale du contrat
 +    * practicalEndDate : date de fin prévue
 +    * Enable : contrat activé (O/N)
 +    * workerName : Nom du travailleur
 +    * customerName : Nom du client
 +    * occupationContractsId : id de l'occupation (ceci peut changer s'il y a une modification des occupations)
 +    * abstractCompanyId : id de la société (de la personne de contact)
 +    * companyId : id de la société 
 +    * workermissionId : id du travailleur
 +    * missionBusinessId : id de la mission (pour importation excel basée sur id ISIWEB)
 +    * name : Nom du groupe
 +    * weeklyTime : heure semaine
 +    * annualTime : S sur base annuelle
 +    * partialTime : heure temps partiel
 +  * 13/10/2009 : Ajouts de zones
 +    * externalRef de la mission (pour export secr.soc. manuel)
 +    * valueFace : Valeur faciale du chèque repas
 +  * 15/10/2009 : Groupé sur l'Id du contrat (pour éviter des doubles lignes)
 +    * Ajouter : customerId : id de la société
 +  * 20/10/2009 : correction problème de chèque repas
 +
 +=== mySql ===
 +%%
 +DROP VIEW IF EXISTS `contractview`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`bots`@`%` SQL SECURITY DEFINER VIEW `contractview` AS select `contract`.`id` AS `id`,`contract`.`businessId` AS `contractBusinessId`,`contract`.`label` AS `label`,`contract`.`startDate` AS `startDate`,`contract`.`theoreticalEndDate` AS `theoreticalEndDate`,`contract`.`practicalEndDate` AS `practicalEndDate`,`contract`.`enabled` AS `enabled`,`contract`.`workerName` AS `workerName`,`contract`.`customerName` AS `customerName`,`contract`.`occupationContractsId` AS `occupationContractsId`,`mission`.`customerId` AS `customerId`,`mission`.`abstractCompanyId` AS `abstractCompanyId`,`mission`.`workerMissionsId` AS `workerMissionsId`,`mission`.`importTimeSheetId` AS `importTimeSheetId`,`mission`.`businessId` AS `missionBusinessId`,`mission`.`externalRef` AS `missionExternalRef`,`authgroup`.`name` AS `name`,`worksystem`.`weeklyTime` AS `weeklyTime`,`worksystem`.`annualTime` AS `annualTime`,`worksystem`.`partialTime` AS `partialTime`,`worksystem`.`realWorkerHours` AS `realWorkerHours`, MAX(`specificmealticket`.`faceValue`) AS `faceValue` from (((((((`contract` join `occupation` on((`contract`.`occupationContractsId` = `occupation`.`id`))) join `authgroup` on((`contract`.`responsibleGroupId` = `authgroup`.`id`))) left join `specific_premiumandfee_positionattributesforcontract` on((`contract`.`positionAttributesForContractId` = `specific_premiumandfee_positionattributesforcontract`.`positionAttributesForContractId`))) join `positionattributesforoccupation` on((`contract`.`positionAttributesForOccupationId` = `positionattributesforoccupation`.`id`))) join `worksystem` on((`positionattributesforoccupation`.`workSystemId` = `worksystem`.`id`))) join `mission` on((`occupation`.`missionOccupationsId` = `mission`.`id`))) LEFT join `specificmealticket` on((`specific_premiumandfee_positionattributesforcontract`.`premiumandfeeId` = `specificmealticket`.`id`))) where (`contract`.`enabled` = 1) group by `contract`.`id`;
 +%%
 +
 +=== SQL 2008 ===
 +%%SELECT     dbo.contract.id, dbo.contract.businessId AS contractBusinessId, dbo.contract.label, dbo.contract.startDate, dbo.contract.theoreticalEndDate, 
 +                      dbo.contract.practicalEndDate, dbo.contract.enabled, dbo.contract.workerName, dbo.contract.customerName, dbo.contract.occupationContractsId, 
 +                      dbo.mission.customerId, dbo.mission.abstractCompanyId, dbo.mission.workerMissionsId, dbo.mission.importTimeSheetId, 
 +                      dbo.mission.businessId AS missionBusinessId, dbo.mission.externalRef AS missionExternalRef, dbo.authgroup.name, dbo.worksystem.weeklyTime, 
 +                      dbo.worksystem.annualTime, dbo.worksystem.partialTime, dbo.worksystem.realWorkerHours
 +FROM         dbo.contract INNER JOIN
 +                      dbo.occupation ON dbo.contract.occupationContractsId = dbo.occupation.id INNER JOIN
 +                      dbo.authgroup ON dbo.contract.responsibleGroupId = dbo.authgroup.id INNER JOIN
 +                      dbo.positionattributesforoccupation ON dbo.contract.positionAttributesForOccupationId = dbo.positionattributesforoccupation.id INNER JOIN
 +                      dbo.worksystem ON dbo.positionattributesforoccupation.workSystemId = dbo.worksystem.id INNER JOIN
 +                      dbo.mission ON dbo.occupation.missionOccupationsId = dbo.mission.id
 +WHERE     (dbo.contract.enabled = 1)%%
 +
 +===== Nombre des jours des étudiants =====
 +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
 +
 +
 +===== Chèque repas olap =====
 +
 +* Prends tous les Chèques repris par bon de commande (sodexo ou accor)
 +
 +%%
 +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`));%%
  • isiweb/database.txt
  • Dernière modification: 2019/08/28 09:10
  • (modification externe)