Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Aug 10th, 2012  |  syntax: None  |  size: 6.16 KB  |  hits: 9  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. //récupération des informations nécessaires
  2. /**********************************************************/
  3. //Sélect
  4. /**********************************************************/
  5. //date ecriture
  6. $query="SELECT Ecriture.dte, ";
  7. //numéro dossier d'écriture
  8. $query.="Ecriture.dossier, ";
  9. //nom du dossier
  10. $query.="Dossier.titre, ";
  11. //date d'ouverture du dossier
  12. $query.="Dossier.date_ouv, ";
  13. //datediff
  14. $query.="datediff(day, date_ouv , ".$debut." ), ";
  15. //avocat responsable
  16. $query.="Dossier.avocat_resp, ";
  17. //avocat d'origine
  18. $query.="Dossier.avocat_origine, ";
  19. //avocat responsable du contact
  20. $query.="(select max(avocat_resp) FROM cic_contact WHERE numero_client = round(numero_ouv/10000,0)) AS 'max_avocat_resp', ";
  21. //i don't know
  22. $query.="(SELECT max(valeur) from cic_dyn_val where cic_dyn_def in
  23.                 (SELECT cic_dyn_def from cic_dyn_def where libelle_court = 'NVCLT')
  24.                 and rang in
  25.                 (SELECT cic_contact_fiche from cic_contact_fiche where type_fiche = -1 and contact in
  26.                         (SELECT contact from cic_contact where numero_client = round(numero_ouv/10000,0))
  27.                 )
  28.         ) ,";
  29. //here too :(
  30. $query.="(SELECT convert(datetime,max(valeur)) from cic_dyn_val where cic_dyn_def in
  31.                 (SELECT cic_dyn_def from cic_dyn_def where libelle_court = 'DTCLT')
  32.                 and rang in (SELECT cic_contact_fiche from cic_contact_fiche where type_fiche = -1 and contact in
  33.                         (SELECT contact from cic_contact where numero_client = round(numero_ouv/10000,0))
  34.                 )
  35.         ),";
  36. //date de la facture
  37. $query.="(SELECT max(e3.dte) from ecriture e3 where e3.facture = ecriture.facture and e3.type_operation = 'FC' and e3.type_prestation = 'H') as date_facture, ";
  38. //numéro facture
  39. $query.="facture, ";
  40. //montant facture
  41. $query.="(SELECT -coalesce(sum(e3.montant+e3.montant_tva),0) from ecriture e3 where e3.facture = ecriture.facture and e3.type_operation = 'FC' and type_prestation = 'H' and e3.montant < 0) as 'montant facture', ";
  42. //règlement facture
  43. $query.="(SELECT sum(e3.montant+e3.montant_tva) from ecriture e3 where e3.facture = ecriture.facture and e3.dte >= ".$debut." and e3.dte <= ".$fin." and e3.type_operation = 'RC' and type_prestation = 'H') as 'montant reglt',";
  44. //travail avr
  45. $query.="(SELECT sum(duree) from evaluation where initiale = '".$avr."' and dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'travail avr', ";
  46. //travail autres assoc
  47. $query.="(SELECT sum(duree) from evaluation where initiale <> '".$avr."' and initiale in (SELECT initial_utils from groupe where numero_groupe = 4) and dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'travail autres assoc', ";
  48. //travail coll
  49. $query.="(SELECT sum(duree) from evaluation where initiale not in (SELECT initial_utils from groupe where numero_groupe = 4) and dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'travail coll', ";
  50. //montant avr
  51. $query.="(SELECT sum(montant) from evaluation where initiale = '".$avr."' and dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'montant avr',";
  52. //montant autres assoc
  53. $query.="(SELECT sum(montant) from evaluation where initiale <> '".$avr."' and initiale in (SELECT initial_utils from groupe where numero_groupe = 4) and dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'montant autres assoc',";
  54. //montant coll
  55. $query.="(SELECT sum(montant) from evaluation where initiale not in (SELECT initial_utils from groupe where numero_groupe = 4) and dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'montant coll',";
  56. //montant initial
  57. $query.="(SELECT sum(montant) from evaluation where dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'montant initial', ";
  58. //montant final
  59. $query.="(SELECT sum(montant_final) from evaluation where dossier = dossier.numero_ouv and evaluation.facture = ecriture.facture and evaluation.tpe in (3,4,5,12) and etat = 1) as 'montant final'";
  60.  
  61. /**********************************************************/
  62. //FROM
  63. /**********************************************************/
  64. $query.=" FROM Ecriture, Dossier  ";
  65.  
  66. /**********************************************************/
  67. //WHERE
  68. /**********************************************************/
  69. //jointure écriture -> dossier
  70. $query.="WHERE ecriture.dossier = dossier.numero_ouv ";
  71.  
  72. //définition de la période et du type de prestation et d'opération et facture NOT NULL
  73. $where="Ecriture.dte >= '".$debut."' and Ecriture.dte <= '".$fin."' and Ecriture.type_operation = 'RC' and Ecriture.type_prestation = 'H' and facture <> 0 ";
  74. $whereE2="facture <> 0 and facture in (SELECT e2.facture from ecriture e2 where e2.dte >= '".$debut."' and e2.dte <= '".$fin."' and e2.type_operation = 'RC' and e2.type_prestation = 'H')";
  75. //multi conditions avec des "OR"
  76. $query.="and (";
  77. //Dossiers ou l'avocat est responsable !
  78. //numéro de client du dossier où l'avocat est responsable
  79. $query.="(".$where." and round(numero_ouv/10000,0) in (SELECT numero_client from cic_contact where avocat_resp = '".$avr."' ))";
  80. //numéro de client du dossier où l'avocat est responsable
  81. $query.="OR (".$where." and dossier in (SELECT dossier from evaluation where initiale = '".$avr."' and tpe in (3,4,5,12) and ".$whereE2."))";
  82.  
  83. //i don't know
  84. $query.="OR (".$where." and (avocat_resp = '".$avr."' or avocat_origine = '".$avr."' )and dossier in (SELECT dossier from evaluation where ".$whereE2.")))";
  85. //Dossier
  86.  
  87. /**********************************************************/
  88. //group by
  89. /**********************************************************/
  90. $query.=" group by Ecriture.dte,Ecriture.dossier,dossier.numero_ouv,Dossier.titre,Dossier.date_ouv,Dossier.avocat_resp,Dossier.avocat_origine, Dossier.desthono,facture ";
  91.  
  92.  
  93. /**********************************************************/
  94. //order by
  95. /**********************************************************/
  96. $query.=" order by dossier.titre,facture";
create a new version of this paste RAW Paste Data