isiweb:mysql

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:mysql [2019/08/28 09:10] (Version actuelle)
Ligne 1: Ligne 1:
 +===== Export comptabilité - CSV global - mySql =====
  
 +SELECT invoice.businessId aS invnum ,
 +invoice.referenceDate AS invdate ,
 +invoice.total as invnet,
 +invoice.vat as invvat,
 +invoice.totalTaxIncluded as invtopay,
 +prestation.dataType,
 +prestation.id AS Lineid ,
 +prestation.hoursDate as prestdate,
 +abstractcompany.businessid as custid,
 +prestation.groupName AS agname,
 +prestation.workerStatus AS statutid,
 +prestation.hoursWeek as pstweek,
 +prestation.hoursYear AS pstyear,
 +prestation.invoicedValue as pstinvamt1 , 
 +prestation.invoiced as pstinvamt2 ,
 +abstractcompany.businessid as clicustid,
 +abstractcompany.businessid as cliaccountid,
 +abstractcompany.businessid as cligroupid,
 +contract.responsibleGroupId aS agenceid,
 +convention.tvaCode as vatcode,
 +hours.tvaRate as vatpercent,
 +businessentitycompany.name as cliadiname,
 +abstractcompany.mainContactMedias_address_street AS CliAdiStreet,
 +abstractcompany.mainContactMedias_address_number AS CliAdiStreetNr,
 +abstractcompany.mainContactMedias_address_box AS CliAdistreetBox,
 +abstractcompany.mainContactMedias_phone AS CliAdiTel,
 +city.zipCode AS CliAdizip,
 +(select city_translations.label from city_translations where city_translations.id = city.id and city_translations.locale = 'fr') AS CliAdiCityF,
 +(select city_translations.label from city_translations where city_translations.id = city.id and city_translations.locale = 'nl') AS CliAdiCityN,
 +country.iso2 as CliAdiLand,
 +hourstype.accountNumber AS Account,
 +company.companynumber as clivat,
 +CONCAT(LPAD(dayscount,2,'0'), '_', payment_term.paymentTermType) aS invdelay,
 +CONCAT(LPAD(dayscount,2,'0'), '_', payment_term.paymentTermType) aS cliinvdelay,
 +convention.preferentialAgentId AS UserContact,
 +convention.invoiceContactCompanyId AS ConventionContact,
 +(select distinct (contactcompany.contactCompanyId) AS nr from contactcompany where contactcompany.preferentialContactCompanyId = prestation.companyId) AS CompanyContact,
 +(select language from person where person.id = convention.preferentialAgentId ) AS userlanguage,
 +(select language from person where person.id = convention.invoiceContactCompanyId ) AS conventionlanguage,
 +(select language from person where person.id in (select distinct (contactcompany.contactCompanyId)
 +from contactcompany
 +where contactcompany.preferentialContactCompanyId = prestation.companyId)) as companylanguage,
 +authgroup.name as cliagenceid,
 +SPACE(30) as cliadiname2,
 +SPACE(5) as cliInvVatid,
 +SPACE(2) as cliRegime,
 +SPACE(1) as cliCat,
 +Contract.workerName as workername,
 +( SELECT DISTINCT(product.businessid) from product, mission_product where product.id = mission_product.productid and mission_product.missionid = mission.id and product.businessid <> 'PAY' ) AS Department
 +FROM invoice,
 +convention,
 +hours,
 +contract,
 +prestation,
 +businessentitycompany,
 +abstractcompany,
 +city,
 +city_translations,
 +country,
 +qualificationposition,
 +hourstype,
 +company,
 +payment_term,
 +authgroup,
 +mission,
 +occupation
 +WHERE invoice.convention = convention.id
 +and invoice.id = hours.invoiceId
 +and contract.id = hours.contractid
 +and prestation.contractId = contract.id
 +and hours.id = prestation.hoursid
 +and businessentitycompany.id = prestation.companyId
 +and company.companyId = prestation.companyId
 +and abstractcompany.abstractCompanyId = prestation.companyId
 +and city.id = abstractcompany.mainContactMedias_address_cityId
 +and city_translations.id = city.id
 +and city_translations.locale = 'fr'
 +and country.id = city.countryId
 +and hours.hoursQualificationId = qualificationposition.id
 +and qualificationposition.hoursType = hourstype.id
 +and convention.paymentTermId = payment_term.id
 +and authgroup.id = contract.responsibleGroupId
 +and contract.occupationcontractsid = occupation.id
 +and occupation.missionoccupationsid = mission.id
 +and prestation.col_hoursid <> 0
 +and **invoice.accountingExportBatchId = 1**
 +
 +**UNION ALL**
 +
 +SELECT invoice.businessId aS invnum ,
 +invoice.referenceDate AS invdate ,
 +invoice.total as invnet,
 +invoice.vat as invvat,
 +invoice.totalTaxIncluded as invtopay,
 +prestation.dataType,
 +prestation.id As LineId ,
 +prestation.hoursDate as prestdate,
 +abstractcompany.businessid as custid,
 +prestation.groupName AS agname,
 +prestation.workerStatus AS statutid,
 +prestation.hoursWeek as pstweek,
 +prestation.hoursYear AS pstyear,
 +prestation.invoicedValue as pstinvamt1 ,
 +prestation.invoiced as pstinvamt2 ,
 +abstractcompany.businessid as clicustid,
 +abstractcompany.businessid as cliaccountid,
 +abstractcompany.businessid as cligroupid,
 +contract.responsibleGroupId aS agenceid,
 +convention.tvaCode as vatcode,
 +premiumandfeequantification.tvaRate as vatpercent,
 +businessentitycompany.name as cliadiname,
 +abstractcompany.mainContactMedias_address_street AS CliAdiStreet,
 +abstractcompany.mainContactMedias_address_number AS CliAdiStreetNr,
 +abstractcompany.mainContactMedias_address_box AS CliAdistreetBox,
 +abstractcompany.mainContactMedias_phone AS CliAdiTel,
 +city.zipCode AS CliAdizip,
 +(select city_translations.label from city_translations where city_translations.id = city.id and city_translations.locale = 'fr') AS CliAdiCityF,
 +(select city_translations.label from city_translations where city_translations.id = city.id and city_translations.locale = 'nl') AS CliAdiCityN,
 +country.iso2 as CliAdiLand,
 +premiumandfeetype.accountNumber AS Account,
 +company.companynumber as clivat,
 +CONCAT(LPAD(payment_term.daysCount,2,'0'), '_', payment_term.paymentTermType) aS invdelay,
 +CONCAT(LPAD(payment_term.daysCount,2,'0'), '_', payment_term.paymentTermType) aS cliinvdelay,
 +convention.preferentialAgentId AS UserContact,
 +convention.invoiceContactCompanyId AS ConventionContact,
 +(select DISTINCT contactcompany.contactCompanyId
 +from contactcompany
 +where contactcompany.preferentialContactCompanyId = prestation.companyId ) AS CompanyContact,
 +(select language from person where person.id = convention.preferentialAgentId ) AS userlanguage,
 +(select language from person where person.id = convention.invoiceContactCompanyId ) AS conventionlanguage,
 +(select language from person where person.id in (select DISTINCT contactcompany.contactCompanyId
 +from contactcompany where contactcompany.preferentialContactCompanyId = prestation.companyId )) as companylanguage,
 +authgroup.name as cliagenceid,
 +SPACE(30) as cliadiname2,
 +SPACE(5) as cliInvVatid,
 +SPACE(2) as cliRegime,
 +SPACE(1) as cliCat,
 +Contract.workerName as workername,
 +( SELECT DISTINCT(product.businessid) from product, mission_product where product.id = mission_product.productid and mission_product.missionid = mission.id and product.businessid <> 'PAY' ) AS Department
 +FROM invoice,
 +premiumandfeequantification,
 +prestation,
 +premiumandfeetype,
 +otherpremiumandfee,
 +premiumandfee,
 +actualotherpremiumandfee,
 +businessentitycompany,
 +contract,
 +company,
 +convention,
 +payment_term,
 +abstractcompany,
 +city,
 +city_translations,
 +country,
 +authgroup,
 +mission,
 +occupation
 +WHERE invoice.convention = convention.id
 +and invoice.id = premiumandfeequantification.invoiceId
 +and premiumandfeequantification.id = prestation.premiumAndFeeQuantificationId
 +and premiumandfeetype.id = otherpremiumandfee.id
 +and otherpremiumandfee.id = premiumandfee.premiumAndFeeTypeId
 +and premiumandfee.id = actualotherpremiumandfee.id
 +and actualotherpremiumandfee.id = premiumandfeequantification.actualPremiumAndFeeId
 +and prestation.contractId = contract.id
 +and convention.paymentTermId = payment_term.id
 +and businessentitycompany.id = prestation.companyId
 +and company.companyId = prestation.companyId
 +and abstractcompany.abstractCompanyId = prestation.companyId
 +and city.id = abstractcompany.mainContactMedias_address_cityId
 +and city_translations.id = city.id
 +and city_translations.locale = 'fr'
 +and country.id = city.countryid
 +and authgroup.id = contract.responsibleGroupId
 +and contract.occupationcontractsid = occupation.id
 +and occupation.missionoccupationsid = mission.id
 +and prestation.premiumAndFeeQuantificationId <> 0
 +and **invoice.accountingExportBatchId = 1**
 +
 +**UNION ALL**
 +
 +SELECT invoice.businessId aS invnum ,
 +invoice.referenceDate AS invdate ,
 +invoice.total as invnet,
 +invoice.vat as invvat,
 +invoice.totalTaxIncluded as invtopay,
 +prestation.dataType,
 +prestation.id As LineId ,
 +prestation.hoursDate as prestdate,
 +abstractcompany.businessid as custid,
 +prestation.groupName AS agname,
 +prestation.workerStatus AS statutid,
 +prestation.hoursWeek as pstweek,
 +prestation.hoursYear AS pstyear,
 +prestation.invoicedValue as pstinvamt1 ,
 +prestation.invoiced as pstinvamt2 ,
 +abstractcompany.businessid as clicustid,
 +abstractcompany.businessid as cliaccountid,
 +abstractcompany.businessid as cligroupid,
 +contract.responsibleGroupId aS agenceid,
 +convention.tvaCode as vatcode,
 +premiumandfeequantification.tvaRate as vatpercent,
 +businessentitycompany.name as cliadiname,
 +abstractcompany.mainContactMedias_address_street AS CliAdiStreet,
 +abstractcompany.mainContactMedias_address_number AS CliAdiStreetNr,
 +abstractcompany.mainContactMedias_address_box AS CliAdistreetBox,
 +abstractcompany.mainContactMedias_phone AS CliAdiTel,
 +city.zipCode AS CliAdizip,
 +(select city_translations.label from city_translations where city_translations.id = city.id and city_translations.locale = 'fr') AS CliAdiCityF,
 +(select city_translations.label from city_translations where city_translations.id = city.id and city_translations.locale = 'nl') AS CliAdiCityN,
 +country.iso2 as CliAdiLand,
 +premiumandfeetype.accountNumber AS Account,
 +company.companynumber as clivat,
 +CONCAT(LPAD(payment_term.daysCount,2,'0'), '_', payment_term.paymentTermType) aS invdelay,
 +CONCAT(LPAD(payment_term.daysCount,2,'0'), '_', payment_term.paymentTermType) aS cliinvdelay,
 +convention.preferentialAgentId AS UserContact,
 +convention.invoiceContactCompanyId AS ConventionContact,
 +(select distinct contactcompany.contactCompanyId
 +from contactcompany
 +where contactcompany.preferentialContactCompanyId = prestation.companyId ) AS CompanyContact,
 +(select language from person where person.id = convention.preferentialAgentId ) AS userlanguage,
 +(select language from person where person.id = convention.invoiceContactCompanyId ) AS conventionlanguage,
 +(select language from person where person.id in (select DISTINCT contactcompany.contactCompanyId
 +from contactcompany
 +where contactcompany.preferentialContactCompanyId = prestation.companyId )) as companylanguage,
 +authgroup.name as cliagenceid,
 +SPACE(30) as cliadiname2,
 +SPACE(5) as cliInvVatid,
 +SPACE(2) as cliRegime,
 +SPACE(1) as cliCat,
 +Contract.workerName as workername,
 +( SELECT DISTINCT(product.businessid) from product, mission_product where product.id = mission_product.productid and mission_product.missionid = mission.id and product.businessid <> 'PAY' ) AS Department
 +FROM invoice,
 +premiumandfeequantification,
 +prestation,
 +premiumandfeetype,
 +premiumandfee,
 +mealticket,
 +actualmealticket,
 +businessentitycompany,
 +contract,
 +company,
 +convention,
 +payment_term,
 +abstractcompany,
 +city,
 +city_translations,
 +country,
 +authgroup,
 +mission,
 +occupation
 +WHERE invoice.convention = convention.id
 +and invoice.id = premiumandfeequantification.invoiceId
 +and premiumandfeequantification.id = prestation.premiumAndFeeQuantificationId
 +and premiumandfeetype.id = mealticket.id
 +and mealticket.id = premiumandfee.premiumAndFeeTypeId
 +and premiumandfee.id = actualmealticket.id
 +and actualmealticket.id = premiumandfeequantification.actualPremiumAndFeeId
 +and prestation.contractId = contract.id
 +and convention.paymentTermId = payment_term.id
 +and businessentitycompany.id = prestation.companyId
 +and company.companyId = prestation.companyId
 +and abstractcompany.abstractCompanyId = prestation.companyId
 +and city.id = abstractcompany.mainContactMedias_address_cityId
 +and city_translations.id = city.id
 +and city_translations.locale = 'fr'
 +and country.id = city.countryId
 +and authgroup.id = contract.responsibleGroupId
 +and contract.occupationcontractsid = occupation.id
 +and occupation.missionoccupationsid = mission.id
 +and prestation.premiumAndFeeQuantificationId <> 0
 +and **invoice.accountingExportBatchId = 1**
 +ORDER BY invnum, lineid
  • isiweb/mysql.txt
  • Dernière modification: 2019/08/28 09:10
  • (modification externe)