query($select)->fetchAll(); $correspondanceIdEquip = []; foreach ($result as $value) { $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idequipement'] = $value['idequipement']; $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idclient'] = $value['idclient']; $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idadresse'] = $value['idadresse']; } $select = "SELECT * FROM sav.tmp_correspondance_horaire_passage"; $result = $connPostgre->query($select)->fetchAll(); $correspondanceHorairePassage = []; foreach ($result as $value) { $correspondanceHorairePassage[$value['idsite']][$value['horaire_passage_oracle']] = $value['idhoraire_passage']; } $correspondanceRetour['0']['SAV'] = 1; $correspondanceRetour['1']['SAV'] = 2; $correspondanceRetour['2']['SAV'] = 3; $correspondanceRetour['3']['SAV'] = 4; $correspondanceRetour['4']['SAV'] = 5; $correspondanceRetour['11']['SAV'] = 6; $correspondanceRetour['12']['SAV'] = 7; $correspondanceRetour['13']['SAV'] = 8; $correspondanceRetour['14']['SAV'] = 9; $correspondanceRetour['15']['SAV'] = 10; $correspondanceRetour['16']['SAV'] = 11; $correspondanceRetour['17']['SAV'] = 12; $correspondanceRetour['21']['SAV'] = 13; $correspondanceRetour['22']['SAV'] = 14; $correspondanceRetour['23']['SAV'] = 15; $correspondanceRetour['24']['SAV'] = 16; $correspondanceRetour['41']['SAV'] = 17; $correspondanceRetour['42']['SAV'] = 18; $correspondanceRetour['51']['SAV'] = 19; $correspondanceRetour['52']['SAV'] = 20; $correspondanceRetour['53']['SAV'] = 21; $correspondanceRetour['54']['SAV'] = 22; $correspondanceRetour['55']['SAV'] = 23; $correspondanceRetour['56']['SAV'] = 24; $correspondanceRetour['58']['SAV'] = 25; $correspondanceRetour['59']['SAV'] = 26; $correspondanceRetour['62']['SAV'] = 27; $correspondanceRetour['81']['SAV'] = 28; $correspondanceRetour['82']['SAV'] = 29; $correspondanceRetour['83']['SAV'] = 30; $correspondanceRetour['84']['SAV'] = 31; $correspondanceRetour['85']['SAV'] = 32; $correspondanceRetour['86']['SAV'] = 33; $correspondanceRetour['87']['SAV'] = 34; $correspondanceRetour['88']['SAV'] = 35; $correspondanceRetour['89']['SAV'] = 36; $correspondanceRetour['90']['SAV'] = 37; $correspondanceRetour['91']['SAV'] = 38; $correspondanceRetour['92']['SAV'] = 39; $correspondanceRetour['93']['SAV'] = 40; $correspondanceRetour['94']['SAV'] = 41; $correspondanceRetour['95']['SAV'] = 42; $correspondanceRetour['96']['SAV'] = 43; $correspondanceRetour['97']['SAV'] = 44; $correspondanceRetour['98']['SAV'] = 45; $correspondanceRetour['99']['SAV'] = 46; $correspondanceRetour['0']['GOSNET'] = 1; $correspondanceRetour['1']['GOSNET'] = 3; $correspondanceRetour['2']['GOSNET'] = 2; $correspondanceRetour['3']['GOSNET'] = 4; $correspondanceRetour['11']['GOSNET'] = 6; $correspondanceRetour['12']['GOSNET'] = 7; $correspondanceRetour['13']['GOSNET'] = 47; $correspondanceRetour['14']['GOSNET'] = 9; $correspondanceRetour['15']['GOSNET'] = 48; $correspondanceRetour['16']['GOSNET'] = 25; $correspondanceRetour['17']['GOSNET'] = 49; $correspondanceRetour['18']['GOSNET'] = 50; $correspondanceRetour['19']['GOSNET'] = 51; $correspondanceRetour['20']['GOSNET'] = 52; $correspondanceRetour['21']['GOSNET'] = 53; $correspondanceRetour['22']['GOSNET'] = 54; $correspondanceRetour['23']['GOSNET'] = 55; $correspondanceRetour['24']['GOSNET'] = 56; $correspondanceRetour['31']['GOSNET'] = 57; $correspondanceRetour['32']['GOSNET'] = 58; $correspondanceRetour['33']['GOSNET'] = 59; $correspondanceRetour['34']['GOSNET'] = 60; $correspondanceRetour['41']['GOSNET'] = 61; $correspondanceRetour['42']['GOSNET'] = 62; $correspondanceRetour['43']['GOSNET'] = 63; $correspondanceRetour['44']['GOSNET'] = 64; $correspondanceRetour['45']['GOSNET'] = 65; $correspondanceRetour['46']['GOSNET'] = 66; $correspondanceRetour['47']['GOSNET'] = 67; $correspondanceRetour['51']['GOSNET'] = 19; $correspondanceRetour['52']['GOSNET'] = 20; $correspondanceRetour['54']['GOSNET'] = 22; $correspondanceRetour['55']['GOSNET'] = 22; $correspondanceRetour['56']['GOSNET'] = 24; $correspondanceRetour['57']['GOSNET'] = 24; $correspondanceRetour['58']['GOSNET'] = 68; $correspondanceRetour['59']['GOSNET'] = 26; $correspondanceRetour['60']['GOSNET'] = 69; $correspondanceRetour['61']['GOSNET'] = 70; $correspondanceRetour['62']['GOSNET'] = 27; $correspondanceRetour['71']['GOSNET'] = 71; $correspondanceRetour['72']['GOSNET'] = 72; $correspondanceRetour['81']['GOSNET'] = 28; $correspondanceRetour['82']['GOSNET'] = 29; $correspondanceRetour['83']['GOSNET'] = 30; $correspondanceRetour['84']['GOSNET'] = 31; $correspondanceRetour['85']['GOSNET'] = 32; $correspondanceRetour['86']['GOSNET'] = 29; $correspondanceRetour['87']['GOSNET'] = 29; $correspondanceRetour['88']['GOSNET'] = 73; $correspondanceRetour['89']['GOSNET'] = 29; $correspondanceRetour['90']['GOSNET'] = 74; $correspondanceRetour['91']['GOSNET'] = 38; $correspondanceRetour['92']['GOSNET'] = 39; $correspondanceRetour['93']['GOSNET'] = 40; $correspondanceRetour['94']['GOSNET'] = 41; $correspondanceRetour['95']['GOSNET'] = 42; $correspondanceRetour['96']['GOSNET'] = 43; $correspondanceRetour['97']['GOSNET'] = 44; $correspondanceRetour['98']['GOSNET'] = 75; $correspondanceRetour['900']['GOSNET'] = 69; $correspondanceRetour['901']['GOSNET'] = 18; $correspondanceRetour['902']['GOSNET'] = 76; $correspondanceRetour['999']['GOSNET'] = 17; $correspondanceRetour['0']['HANNIQUET'] = 1; $correspondanceRetour['1']['HANNIQUET'] = 2; $correspondanceRetour['2']['HANNIQUET'] = 3; $correspondanceRetour['3']['HANNIQUET'] = 4; $correspondanceRetour['4']['HANNIQUET'] = 5; $correspondanceRetour['11']['HANNIQUET'] = 6; $correspondanceRetour['12']['HANNIQUET'] = 7; $correspondanceRetour['13']['HANNIQUET'] = 8; $correspondanceRetour['14']['HANNIQUET'] = 9; $correspondanceRetour['15']['HANNIQUET'] = 10; $correspondanceRetour['16']['HANNIQUET'] = 11; $correspondanceRetour['17']['HANNIQUET'] = 12; $correspondanceRetour['21']['HANNIQUET'] = 13; $correspondanceRetour['22']['HANNIQUET'] = 14; $correspondanceRetour['23']['HANNIQUET'] = 15; $correspondanceRetour['24']['HANNIQUET'] = 16; $correspondanceRetour['31']['HANNIQUET'] = 24; $correspondanceRetour['32']['HANNIQUET'] = 16; $correspondanceRetour['33']['HANNIQUET'] = 59; $correspondanceRetour['41']['HANNIQUET'] = 17; $correspondanceRetour['42']['HANNIQUET'] = 18; $correspondanceRetour['43']['HANNIQUET'] = 34; $correspondanceRetour['44']['HANNIQUET'] = 30; $correspondanceRetour['45']['HANNIQUET'] = 65; $correspondanceRetour['46']['HANNIQUET'] = 23; $correspondanceRetour['51']['HANNIQUET'] = 19; $correspondanceRetour['52']['HANNIQUET'] = 20; $correspondanceRetour['53']['HANNIQUET'] = 21; $correspondanceRetour['54']['HANNIQUET'] = 22; $correspondanceRetour['55']['HANNIQUET'] = 23; $correspondanceRetour['56']['HANNIQUET'] = 24; $correspondanceRetour['57']['HANNIQUET'] = 24; $correspondanceRetour['58']['HANNIQUET'] = 68; $correspondanceRetour['59']['HANNIQUET'] = 26; $correspondanceRetour['61']['HANNIQUET'] = 22; $correspondanceRetour['62']['HANNIQUET'] = 27; $correspondanceRetour['71']['HANNIQUET'] = 71; $correspondanceRetour['72']['HANNIQUET'] = 72; $correspondanceRetour['81']['HANNIQUET'] = 28; $correspondanceRetour['82']['HANNIQUET'] = 29; $correspondanceRetour['83']['HANNIQUET'] = 30; $correspondanceRetour['84']['HANNIQUET'] = 31; $correspondanceRetour['85']['HANNIQUET'] = 32; $correspondanceRetour['86']['HANNIQUET'] = 33; $correspondanceRetour['87']['HANNIQUET'] = 34; $correspondanceRetour['88']['HANNIQUET'] = 35; $correspondanceRetour['89']['HANNIQUET'] = 36; $correspondanceRetour['90']['HANNIQUET'] = 37; $correspondanceRetour['91']['HANNIQUET'] = 38; $correspondanceRetour['92']['HANNIQUET'] = 39; $correspondanceRetour['93']['HANNIQUET'] = 40; $correspondanceRetour['94']['HANNIQUET'] = 41; $correspondanceRetour['95']['HANNIQUET'] = 42; $correspondanceRetour['96']['HANNIQUET'] = 43; $correspondanceRetour['97']['HANNIQUET'] = 44; $correspondanceRetour['98']['HANNIQUET'] = 45; $correspondanceRetour['999']['HANNIQUET'] = 17; $correspondanceRetour['0']['LAHALLE'] = 1; $correspondanceRetour['1']['LAHALLE'] = 3; $correspondanceRetour['2']['LAHALLE'] = 2; $correspondanceRetour['11']['LAHALLE'] = 6; $correspondanceRetour['12']['LAHALLE'] = 7; $correspondanceRetour['13']['LAHALLE'] = 8; $correspondanceRetour['14']['LAHALLE'] = 9; $correspondanceRetour['15']['LAHALLE'] = 10; $correspondanceRetour['21']['LAHALLE'] = 13; $correspondanceRetour['22']['LAHALLE'] = 14; $correspondanceRetour['23']['LAHALLE'] = 15; $correspondanceRetour['31']['LAHALLE'] = 24; $correspondanceRetour['32']['LAHALLE'] = 16; $correspondanceRetour['33']['LAHALLE'] = 59; $correspondanceRetour['41']['LAHALLE'] = 33; $correspondanceRetour['42']['LAHALLE'] = 10; $correspondanceRetour['43']['LAHALLE'] = 34; $correspondanceRetour['44']['LAHALLE'] = 30; $correspondanceRetour['45']['LAHALLE'] = 65; $correspondanceRetour['46']['LAHALLE'] = 23; $correspondanceRetour['51']['LAHALLE'] = 19; $correspondanceRetour['52']['LAHALLE'] = 20; $correspondanceRetour['54']['LAHALLE'] = 22; $correspondanceRetour['55']['LAHALLE'] = 22; $correspondanceRetour['56']['LAHALLE'] = 24; $correspondanceRetour['57']['LAHALLE'] = 24; $correspondanceRetour['58']['LAHALLE'] = 68; $correspondanceRetour['61']['LAHALLE'] = 22; $correspondanceRetour['62']['LAHALLE'] = 27; $correspondanceRetour['71']['LAHALLE'] = 71; $correspondanceRetour['72']['LAHALLE'] = 72; $correspondanceRetour['73']['LAHALLE'] = 1; $correspondanceRetour['81']['LAHALLE'] = 28; $correspondanceRetour['82']['LAHALLE'] = 29; $correspondanceRetour['83']['LAHALLE'] = 30; $correspondanceRetour['84']['LAHALLE'] = 31; $correspondanceRetour['85']['LAHALLE'] = 32; $correspondanceRetour['91']['LAHALLE'] = 38; $correspondanceRetour['92']['LAHALLE'] = 39; $correspondanceRetour['93']['LAHALLE'] = 40; $correspondanceRetour['94']['LAHALLE'] = 41; $correspondanceRetour['95']['LAHALLE'] = 42; $correspondanceRetour['96']['LAHALLE'] = 43; $correspondanceRetour['97']['LAHALLE'] = 44; $correspondanceRetour['98']['LAHALLE'] = 35; $correspondanceRetour['901']['LAHALLE'] = 18; $correspondanceRetour['999']['LAHALLE'] = 17; $correspondanceRetour['0']['VERGNIEUX'] = 1; $correspondanceRetour['1']['VERGNIEUX'] = 2; $correspondanceRetour['2']['VERGNIEUX'] = 3; $correspondanceRetour['3']['VERGNIEUX'] = 4; $correspondanceRetour['4']['VERGNIEUX'] = 5; $correspondanceRetour['11']['VERGNIEUX'] = 6; $correspondanceRetour['12']['VERGNIEUX'] = 7; $correspondanceRetour['13']['VERGNIEUX'] = 8; $correspondanceRetour['14']['VERGNIEUX'] = 9; $correspondanceRetour['15']['VERGNIEUX'] = 10; $correspondanceRetour['16']['VERGNIEUX'] = 11; $correspondanceRetour['17']['VERGNIEUX'] = 12; $correspondanceRetour['21']['VERGNIEUX'] = 13; $correspondanceRetour['22']['VERGNIEUX'] = 14; $correspondanceRetour['23']['VERGNIEUX'] = 15; $correspondanceRetour['24']['VERGNIEUX'] = 16; $correspondanceRetour['41']['VERGNIEUX'] = 17; $correspondanceRetour['42']['VERGNIEUX'] = 18; $correspondanceRetour['51']['VERGNIEUX'] = 19; $correspondanceRetour['52']['VERGNIEUX'] = 20; $correspondanceRetour['53']['VERGNIEUX'] = 21; $correspondanceRetour['54']['VERGNIEUX'] = 22; $correspondanceRetour['55']['VERGNIEUX'] = 23; $correspondanceRetour['56']['VERGNIEUX'] = 24; $correspondanceRetour['58']['VERGNIEUX'] = 68; $correspondanceRetour['62']['VERGNIEUX'] = 27; $correspondanceRetour['81']['VERGNIEUX'] = 28; $correspondanceRetour['82']['VERGNIEUX'] = 29; $correspondanceRetour['83']['VERGNIEUX'] = 30; $correspondanceRetour['84']['VERGNIEUX'] = 31; $correspondanceRetour['85']['VERGNIEUX'] = 32; $correspondanceRetour['86']['VERGNIEUX'] = 33; $correspondanceRetour['87']['VERGNIEUX'] = 34; $correspondanceRetour['88']['VERGNIEUX'] = 35; $correspondanceRetour['89']['VERGNIEUX'] = 36; $correspondanceRetour['90']['VERGNIEUX'] = 37; $correspondanceRetour['91']['VERGNIEUX'] = 38; $correspondanceRetour['92']['VERGNIEUX'] = 39; $correspondanceRetour['93']['VERGNIEUX'] = 40; $correspondanceRetour['94']['VERGNIEUX'] = 41; $correspondanceRetour['95']['VERGNIEUX'] = 42; $correspondanceRetour['96']['VERGNIEUX'] = 43; $correspondanceRetour['97']['VERGNIEUX'] = 44; $correspondanceRetour['98']['VERGNIEUX'] = 45; $correspondanceRetour['0']['CHEZE'] = 1; $correspondanceRetour['1']['CHEZE'] = 5; $correspondanceRetour['2']['CHEZE'] = 77; $correspondanceRetour['3']['CHEZE'] = 3; $correspondanceRetour['4']['CHEZE'] = 2; $correspondanceRetour['11']['CHEZE'] = 77; $correspondanceRetour['12']['CHEZE'] = 6; $correspondanceRetour['13']['CHEZE'] = 55; $correspondanceRetour['14']['CHEZE'] = 9; $correspondanceRetour['15']['CHEZE'] = 15; $correspondanceRetour['16']['CHEZE'] = 7; $correspondanceRetour['17']['CHEZE'] = 1; $correspondanceRetour['21']['CHEZE'] = 29; $correspondanceRetour['22']['CHEZE'] = 30; $correspondanceRetour['23']['CHEZE'] = 6; $correspondanceRetour['24']['CHEZE'] = 54; $correspondanceRetour['25']['CHEZE'] = 55; $correspondanceRetour['26']['CHEZE'] = 30; $correspondanceRetour['27']['CHEZE'] = 30; $correspondanceRetour['28']['CHEZE'] = 1; $correspondanceRetour['31']['CHEZE'] = 59; $correspondanceRetour['32']['CHEZE'] = 24; $correspondanceRetour['33']['CHEZE'] = 19; $correspondanceRetour['34']['CHEZE'] = 13; $correspondanceRetour['35']['CHEZE'] = 16; $correspondanceRetour['36']['CHEZE'] = 1; $correspondanceRetour['41']['CHEZE'] = 33; $correspondanceRetour['42']['CHEZE'] = 10; $correspondanceRetour['43']['CHEZE'] = 34; $correspondanceRetour['44']['CHEZE'] = 30; $correspondanceRetour['45']['CHEZE'] = 65; $correspondanceRetour['46']['CHEZE'] = 1; $correspondanceRetour['51']['CHEZE'] = 77; $correspondanceRetour['52']['CHEZE'] = 74; $correspondanceRetour['53']['CHEZE'] = 77; $correspondanceRetour['54']['CHEZE'] = 38; $correspondanceRetour['55']['CHEZE'] = 37; $correspondanceRetour['56']['CHEZE'] = 77; $correspondanceRetour['57']['CHEZE'] = 11; $correspondanceRetour['58']['CHEZE'] = 1; $correspondanceRetour['61']['CHEZE'] = 5; $correspondanceRetour['62']['CHEZE'] = 19; $correspondanceRetour['63']['CHEZE'] = 19; $correspondanceRetour['64']['CHEZE'] = 77; $correspondanceRetour['65']['CHEZE'] = 20; $correspondanceRetour['66']['CHEZE'] = 21; $correspondanceRetour['67']['CHEZE'] = 24; $correspondanceRetour['68']['CHEZE'] = 1; $correspondanceRetour['69']['CHEZE'] = 1; $correspondanceRetour['71']['CHEZE'] = 1; $correspondanceRetour['72']['CHEZE'] = 77; $correspondanceRetour['73']['CHEZE'] = 77; $correspondanceRetour['74']['CHEZE'] = 77; $correspondanceRetour['75']['CHEZE'] = 24; $correspondanceRetour['76']['CHEZE'] = 22; $correspondanceRetour['77']['CHEZE'] = 17; $correspondanceRetour['78']['CHEZE'] = 1; $correspondanceRetour['81']['CHEZE'] = 77; $correspondanceRetour['82']['CHEZE'] = 73; $correspondanceRetour['83']['CHEZE'] = 68; $correspondanceRetour['84']['CHEZE'] = 1; $correspondanceRetour['91']['CHEZE'] = 77; $correspondanceRetour['92']['CHEZE'] = 71; $correspondanceRetour['93']['CHEZE'] = 72; $correspondanceRetour['94']['CHEZE'] = 1; $correspondanceRetour['101']['CHEZE'] = 28; $correspondanceRetour['102']['CHEZE'] = 29; $correspondanceRetour['103']['CHEZE'] = 30; $correspondanceRetour['104']['CHEZE'] = 39; $correspondanceRetour['105']['CHEZE'] = 38; $correspondanceRetour['106']['CHEZE'] = 57; $correspondanceRetour['107']['CHEZE'] = 2; $correspondanceRetour['108']['CHEZE'] = 1; $correspondanceMomentPassage[0] = 7; $correspondanceMomentPassage[1] = 1; $correspondanceMomentPassage[2] = 4; $correspondanceMomentPassage[3] = 7; $correspondanceMomentPassage[4] = 5; $correspondanceMomentPassage[5] = 2; $correspondanceMomentPassage[6] = 3; $correspondanceMomentPassage[7] = 6; $correspondanceStatut[0] = 0; $correspondanceStatut[1] = 1; $correspondanceStatut[2] = 3; $correspondanceStatut[3] = 2; $correspondanceStatut[4] = 4; $correspondanceStatut[7] = 4; $correspondanceStatut[8] = 4; //agence true si agence, sinon evenement_lieu $correspondanceFormationLieu['5327']['agence'] = true; $correspondanceFormationLieu['5327']['id'] = 4; $correspondanceFormationLieu['5670']['agence'] = true; $correspondanceFormationLieu['5670']['id'] = 2; $correspondanceFormationLieu['5687']['agence'] = false; $correspondanceFormationLieu['5687']['id'] = 2; $correspondanceFormationLieu['28339']['agence'] = false; $correspondanceFormationLieu['28339']['id'] = 1; $correspondanceFormationLieu['1133548']['agence'] = false; $correspondanceFormationLieu['1133548']['id'] = 1; $correspondanceFormationLieu['1010']['agence'] = true; $correspondanceFormationLieu['1010']['id'] = 5; $correspondanceFormationLieu['1280']['agence'] = true; $correspondanceFormationLieu['1280']['id'] = 2; $correspondanceFormationLieu['492210']['agence'] = true; $correspondanceFormationLieu['492210']['id'] = 2; $correspondanceFormationLieu['492211']['agence'] = false; $correspondanceFormationLieu['492211']['id'] = 1; $correspondanceFormationLieu['491366']['agence'] = true; $correspondanceFormationLieu['491366']['id'] = 2; $correspondanceFormationLieu['491367']['agence'] = true; $correspondanceFormationLieu['491367']['id'] = 7; $correspondanceFormationLieu['610617']['agence'] = true; $correspondanceFormationLieu['610617']['id'] = 8; $correspondanceFormationLieu['708336']['agence'] = true; $correspondanceFormationLieu['708336']['id'] = 2; $correspondanceFormationLieu['565517']['agence'] = true; $correspondanceFormationLieu['565517']['id'] = 9; $correspondanceMotifAbsence['5673']['BRON'] = 1; $correspondanceMotifAbsence['5673']['TOUS'] = 1; $correspondanceMotifAbsence['5674']['BRON'] = 8; $correspondanceMotifAbsence['5674']['TOUS'] = 1; $correspondanceMotifAbsence['5675']['BRON'] = 3; $correspondanceMotifAbsence['5675']['TOUS'] = 1; $correspondanceMotifAbsence['5676']['BRON'] = 2; $correspondanceMotifAbsence['5676']['TOUS'] = 1; $correspondanceMotifAbsence['5677']['BRON'] = 3; $correspondanceMotifAbsence['5677']['TOUS'] = 2; $correspondanceMotifAbsence['5680']['BRON'] = 1; $correspondanceMotifAbsence['5680']['TOUS'] = 3; $correspondanceMotifAbsence['5681']['BRON'] = 1; $correspondanceMotifAbsence['5681']['TOUS'] = 7; $correspondanceMotifAbsence['5682']['BRON'] = 1; $correspondanceMotifAbsence['5682']['TOUS'] = 4; $correspondanceMotifAbsence['5683']['BRON'] = 1; $correspondanceMotifAbsence['5683']['TOUS'] = 4; $correspondanceMotifAbsence['178458']['BRON'] = ''; $correspondanceMotifAbsence['231695']['BRON'] = ''; $correspondanceMotifAbsence['556732']['BRON'] = ''; $correspondanceMotifAbsence['14500']['TOUS'] = 5; $correspondanceMotifAbsence['1279']['TOUS'] = 6; $correspondanceTestLieu['5333']['agence'] = true; $correspondanceTestLieu['5333']['id'] = 4; $correspondanceTestLieu['5334']['agence'] = true; $correspondanceTestLieu['5334']['id'] = 2; $correspondanceTestLieu['5335']['agence'] = false; $correspondanceTestLieu['5335']['id'] = 2; $correspondanceTestLieu['5640']['agence'] = false; $correspondanceTestLieu['5640']['id'] = 1; $correspondanceTestLieu['5067356']['agence'] = false; $correspondanceTestLieu['5067356']['id'] = 1; $correspondanceTestLieu['563707']['agence'] = true; $correspondanceTestLieu['563707']['id'] = 2; $correspondanceTestLieu['491373']['agence'] = true; $correspondanceTestLieu['491373']['id'] = 2; $correspondanceTestLieu['823684']['agence'] = true; $correspondanceTestLieu['823684']['id'] = 7; $correspondanceTestLieu['492220']['agence'] = true; $correspondanceTestLieu['492220']['id'] = 6; $correspondanceTestLieu['492221']['agence'] = true; $correspondanceTestLieu['492221']['id'] = 2; $correspondanceTestLieu['1288']['agence'] = true; $correspondanceTestLieu['1288']['id'] = 5; $correspondanceTestLieu['1289']['agence'] = true; $correspondanceTestLieu['1289']['id'] = 2; $correspondanceTestIntitule['374677'] = 63; $correspondanceTestIntitule['5331'] = 61; $correspondanceTestIntitule['5332'] = 61; $correspondanceTestIntitule['5639'] = 61; $correspondanceTestIntitule['5686'] = 61; $correspondanceTestIntitule['1287'] = 61; $correspondanceTestIntitule['492218'] = 61; $correspondanceTestIntitule['492219'] = 64; $correspondanceTestIntitule['491374'] = 62; $correspondanceTestIntitule['563706'] = 61; $motifAbsence['14500'] = 5; $motifAbsence['5673'] = 1; $motifAbsence['5674'] = 1; $motifAbsence['5675'] = 1; $motifAbsence['5676'] = 1; $motifAbsence['5677'] = 2; $motifAbsence['5680'] = 3; $motifAbsence['5681'] = 1; $motifAbsence['5682'] = 4; $motifAbsence['5683'] = 4; $produitAffectation['F'] = 1; $produitAffectation['GT'] = 2; $produitAffectation['GU'] = 3; $produitAffectation['S'] = 4; $moyenPaiement['B'] = 1; $moyenPaiement['C'] = 2; $moyenPaiement['E'] = 3; $moyenPaiement['V'] = 4; $moyenPaiement['W'] = 4; $correspondanceFormationOrganisme['5328'] = 65; $correspondanceFormationOrganisme['10284'] = 66; $correspondanceFormationOrganisme['10286'] = 66; $correspondanceFormationOrganisme['57806'] = 66; $correspondanceFormationOrganisme['1133549'] = 44; $correspondanceFormationOrganisme['10761'] = 44; $correspondanceFormationOrganisme['10909'] = 45; $correspondanceFormationOrganisme['610632'] = 66; $correspondanceFormationOrganisme['610636'] = 66; $correspondanceFormationOrganisme['610638'] = 66; $correspondanceFormationOrganisme['708720'] = 66; $select = "SELECT * FROM sav.tmp_correspondance_formation"; $result = $connPostgre->query($select)->fetchAll(); $correspondanceFormation = []; foreach ($result as $value) { $correspondanceFormation[$value['idsite']][$value['code_oracle']] = $value['idformation']; } $correspondanceVoitureLieu['463932']['agence'] = false; $correspondanceVoitureLieu['463932']['id'] = 17; $correspondanceVoitureLieu['47516']['agence'] = true; $correspondanceVoitureLieu['47516']['id'] = 2; $correspondanceVoitureLieu['5627']['agence'] = false; $correspondanceVoitureLieu['5627']['id'] = 4; $correspondanceVoitureLieu['4409']['agence'] = false; $correspondanceVoitureLieu['4409']['id'] = 3; $correspondanceVoitureLieu['5337']['agence'] = false; $correspondanceVoitureLieu['5337']['id'] = 5; $correspondanceVoitureLieu['2983']['agence'] = false; $correspondanceVoitureLieu['2983']['id'] = 6; $correspondanceVoitureLieu['4266']['agence'] = false; $correspondanceVoitureLieu['4266']['id'] = 8; $correspondanceVoitureLieu['4291']['agence'] = true; $correspondanceVoitureLieu['4291']['id'] = 4; $correspondanceVoitureLieu['1183849']['agence'] = false; $correspondanceVoitureLieu['1183849']['id'] = 7; $correspondanceVoitureLieu['1248455']['agence'] = false; $correspondanceVoitureLieu['1248455']['id'] = 18; $correspondanceVoitureLieu['541446']['agence'] = false; $correspondanceVoitureLieu['541446']['id'] = 16; $correspondanceVoitureLieu['541923']['agence'] = true; $correspondanceVoitureLieu['541923']['id'] = 8; $correspondanceVoitureLieu['491375']['agence'] = true; $correspondanceVoitureLieu['491375']['id'] = 7; $correspondanceVoitureLieu['492222']['agence'] = true; $correspondanceVoitureLieu['492222']['id'] = 6; $correspondanceVoitureLieu['492223']['agence'] = false; $correspondanceVoitureLieu['492223']['id'] = 12; $correspondanceVoitureLieu['731970']['agence'] = false; $correspondanceVoitureLieu['731970']['id'] = 13; $correspondanceVoitureLieu['731971']['agence'] = false; $correspondanceVoitureLieu['731971']['id'] = 14; $correspondanceVoitureLieu['731972']['agence'] = false; $correspondanceVoitureLieu['731972']['id'] = 15; $correspondanceVoitureLieu['482522']['agence'] = true; $correspondanceVoitureLieu['482522']['id'] = 5; $correspondanceVoitureLieu['482526']['agence'] = false; $correspondanceVoitureLieu['482526']['id'] = 9; $correspondanceVoitureLieu['482528']['agence'] = false; $correspondanceVoitureLieu['482528']['id'] = 10; $correspondanceVoitureLieu['482529']['agence'] = false; $correspondanceVoitureLieu['482529']['id'] = 11; $correspondanceVoitureLieu['505680']['agence'] = false; $correspondanceVoitureLieu['505680']['id'] = 17; $correspondanceVoitureAction['463931'] = 53; $correspondanceVoitureAction['758125'] = 55; $correspondanceVoitureAction['5672'] = 47; $correspondanceVoitureAction['2984'] = 46; $correspondanceVoitureAction['2985'] = 47; $correspondanceVoitureAction['4405'] = 48; $correspondanceVoitureAction['4408'] = 46; $correspondanceVoitureAction['4415'] = 50; $correspondanceVoitureAction['4416'] = 50; $correspondanceVoitureAction['79043'] = 51; $correspondanceVoitureAction['79054'] = 53; $correspondanceVoitureAction['746815'] = 54; $correspondanceVoitureAction['746837'] = 54; $correspondanceVoitureAction['5336'] = 47; $correspondanceVoitureAction['5626'] = 47; $correspondanceVoitureAction['5672'] = 47; $correspondanceVoitureAction['5684'] = 47; $correspondanceVoitureAction['5783'] = 47; $correspondanceVoitureAction['2984'] = 47; $correspondanceVoitureAction['2985'] = 47; $correspondanceVoitureAction['57811'] = 47; $correspondanceVoitureAction['57827'] = 47; $correspondanceVoitureAction['57830'] = 47; $correspondanceVoitureAction['83818'] = 47; $correspondanceVoitureAction['83819'] = 47; $correspondanceVoitureAction['83820'] = 47; $correspondanceVoitureAction['83821'] = 47; $correspondanceVoitureAction['83822'] = 47; $correspondanceVoitureAction['83823'] = 47; $correspondanceVoitureAction['83825'] = 47; $correspondanceVoitureAction['83826'] = 47; $correspondanceVoitureAction['4263'] = 72; $correspondanceVoitureAction['4282'] = 72; $correspondanceVoitureAction['10078'] = 52; $correspondanceVoitureAction['54131'] = 46; $correspondanceVoitureAction['56763'] = 73; $correspondanceVoitureAction['414592'] = 51; $correspondanceVoitureAction['1183944'] = 49; $correspondanceVoitureAction['1183945'] = 72; $correspondanceVoitureAction['482521'] = 51; $correspondanceVoitureAction['482532'] = 52; $correspondanceVoitureAction['503544'] = 74; $correspondanceMotifMaladie['5685'] = 56; $correspondanceMotifMaladie['5696'] = 67; $correspondanceMotifMaladie['5784'] = 58; $correspondanceMotifMaladie['3316'] = 59; $correspondanceMotifMaladie['0'] = 57; $correspondanceMotifMaladie['562132'] = 68; $correspondanceMotifMaladie['482534'] = 67; $correspondanceMotifMaladie['122022'] = 60; $correspondanceMotifMaladie['5666694'] = 69; $correspondanceReunionLieu['5329']['agence'] = true; $correspondanceReunionLieu['5329']['id'] = 4; $correspondanceReunionLieu['5624']['agence'] = true; $correspondanceReunionLieu['5624']['id'] = 2; $correspondanceReunionLieu['5671']['agence'] = false; $correspondanceReunionLieu['5671']['id'] = 2; $correspondanceReunionLieu['563729']['agence'] = true; $correspondanceReunionLieu['563729']['id'] = 2; $correspondanceReunionLieu['715420']['agence'] = true; $correspondanceReunionLieu['715420']['id'] = 8; $correspondanceReunionLieu['508196']['agence'] = false; $correspondanceReunionLieu['508196']['id'] = 1; $correspondanceReunionLieu['491368']['agence'] = true; $correspondanceReunionLieu['491368']['id'] = 7; $correspondanceReunionLieu['491369']['agence'] = true; $correspondanceReunionLieu['491369']['id'] = 2; $correspondanceReunionLieu['491370']['agence'] = false; $correspondanceReunionLieu['491370']['id'] = 2; $correspondanceReunionLieu['492212']['agence'] = true; $correspondanceReunionLieu['492212']['id'] = 6; $correspondanceReunionLieu['492213']['agence'] = true; $correspondanceReunionLieu['492213']['id'] = 2; $correspondanceReunionLieu['492214']['agence'] = false; $correspondanceReunionLieu['492214']['id'] = 2; $correspondanceReunionLieu['1282']['agence'] = true; $correspondanceReunionLieu['1282']['id'] = 5; $correspondanceReunionLieu['482514']['agence'] = true; $correspondanceReunionLieu['482514']['id'] = 2; $correspondanceReunionLieu['571136']['agence'] = false; $correspondanceReunionLieu['571136']['id'] = 2; $correspondanceReunionLieu['32081']['agence'] = false; $correspondanceReunionLieu['32081']['id'] = 1; $correspondanceReunionLieu['658680']['agence'] = false; $correspondanceReunionLieu['658680']['id'] = 1; $correspondanceReunionLieu['97194']['agence'] = true; $correspondanceReunionLieu['97194']['id'] = 3; $correspondanceReunionLieu['565518']['agence'] = true; $correspondanceReunionLieu['565518']['id'] = 9; $correspondanceReunionLieu['495395']['agence'] = false; $correspondanceReunionLieu['495395']['id'] = 1; $correspondanceReunionOrdreJour['495396'] = 10; $correspondanceReunionOrdreJour['495397'] = 22; $correspondanceReunionOrdreJour['32076'] = 1; $correspondanceReunionOrdreJour['32078'] = 22; $correspondanceReunionOrdreJour['10866'] = 2; $correspondanceReunionOrdreJour['10867'] = 3; $correspondanceReunionOrdreJour['10868'] = 4; $correspondanceReunionOrdreJour['10869'] = 5; $correspondanceReunionOrdreJour['10870'] = 6; $correspondanceReunionOrdreJour['10871'] = 7; $correspondanceReunionOrdreJour['10878'] = 8; $correspondanceReunionOrdreJour['10879'] = 8; $correspondanceReunionOrdreJour['10880'] = 9; $correspondanceReunionOrdreJour['10881'] = 9; $correspondanceReunionOrdreJour['10882'] = 8; $correspondanceReunionOrdreJour['10883'] = 9; $correspondanceReunionOrdreJour['5330'] = 10; $correspondanceReunionOrdreJour['1758664'] = 11; $correspondanceReunionOrdreJour['3155591'] = 12; $correspondanceReunionOrdreJour['25377'] = 70; $correspondanceReunionOrdreJour['1283'] = 18; $correspondanceReunionOrdreJour['482516'] = 10; $correspondanceReunionOrdreJour['492215'] = 10; $correspondanceReunionOrdreJour['492216'] = 19; $correspondanceReunionOrdreJour['492217'] = 22; $correspondanceReunionOrdreJour['508197'] = 70; $correspondanceReunionOrdreJour['563727'] = 20; $correspondanceReunionOrdreJour['715425'] = 21; $correspondanceReunionOrdreJour['491371'] = 19; $correspondanceReunionOrdreJour['491372'] = 10; $correspondanceReunionOrdreJour['5330'] = 10; $correspondanceReunionOrdreJour['5625'] = 13; $correspondanceReunionOrdreJour['5628'] = 14; $correspondanceReunionOrdreJour['4455'] = 15; $correspondanceReunionOrdreJour['4390'] = 5; $correspondanceReunionOrdreJour['13558'] = 16; $correspondanceReunionOrdreJour['30940'] = 17; $correspondanceReunionOrdreJour['38096'] = 8; $correspondanceReunionOrdreJour['38097'] = 9; $correspondanceReunionOrdreJour['56325'] = 5; $correspondanceReunionOrdreJour['97875'] = 4; $correspondanceMotifReserve['83396'] = 25; $correspondanceMotifReserve['83397'] = 25; $correspondanceMotifReserve['83399'] = 25; $correspondanceMotifReserve['83400'] = 25; $correspondanceMotifReserve['83401'] = 25; $correspondanceMotifReserve['83405'] = 25; $correspondanceMotifReserve['86585'] = 34; $correspondanceMotifReserve['86741'] = 35; $correspondanceMotifReserve['105022'] = 36; $correspondanceMotifReserve['8044'] = 23; $correspondanceMotifReserve['12309'] = 25; $correspondanceMotifReserve['12310'] = 26; $correspondanceMotifReserve['12311'] = 27; $correspondanceMotifReserve['12312'] = 28; $correspondanceMotifReserve['12313'] = 29; $correspondanceMotifReserve['12625'] = 30; $correspondanceMotifReserve['12626'] = 31; $correspondanceMotifReserve['12627'] = 32; $correspondanceMotifReserve['1295'] = 37; $correspondanceMotifReserve['1786'] = 38; $correspondanceMotifReserve['611890'] = 42; $correspondanceMotifReserve['856036'] = 43; $correspondanceMotifReserve['482547'] = 39; $correspondanceMotifReserve['482541'] = 40; $correspondanceMotifReserve['519991'] = 41; $correspondanceMotifReserve['519994'] = 41; $correspondanceMotifReserve['108680'] = 24; $correspondanceMotifReserve['2898801'] = 33; $correspondanceMotifReserve['5637622'] = 71; $select = "SELECT tmp.*, e.idadresse FROM sav.tmp_correspondance_equipement tmp LEFT JOIN sav.equipement e ON e.id = tmp.idequipement "; $result = $connPostgre->query($select)->fetchAll(); $correspondanceIdEquip = []; foreach ($result as $value) { $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idequipement'] = $value['idequipement']; $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idclient'] = $value['idclient']; $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idadresse'] = $value['idadresse']; } $select = "SELECT * FROM sav.tmp_sequenceur"; $result = $connPostgre->query($select)->fetchAll(); $sequenceur = []; foreach ($result as $value) { $sequenceur[$value['idsite']][$value['annee']] = $value['sequenceur']; } $select = "SELECT * FROM sav.type_planification"; $result = $connPostgre->query($select)->fetchAll(); $typeVisite = []; foreach ($result as $value) { $typeVisite[$value['code']] = $value['id']; } $selectId = "select max(id) current_id from sav.facture"; $idFactureCurrent = $connPostgre->query($selectId)->fetch()['current_id']; $select = "SELECT *, EXTRACT('YEAR' from date_debut_contrat) as year, EXTRACT('MONTH' from date_debut_contrat) as month FROM sav.contrat"; $resultContrat = $connPostgre->query($select)->fetchAll(); $contrats = []; foreach ($resultContrat as $value) { $contrats[$value['idequipement']]['01' . '/' . $value['month'] . '/' . $value['year']] = $value; } $selectFactureLigne = "SELECT * FROM sav.facture_ligne WHERE designation like 'Contrat%'"; $resultFacture = $connPostgre->query($selectFactureLigne)->fetchAll(); $facturesContrat = []; foreach ($resultFacture as $value) { $facturesContrat[$value['idfacture']] = $value; } $selectProduit = "SELECT * FROM sav.produit"; $resultProduit = $connPostgre->query($selectProduit)->fetchAll(); $tabProduits = []; foreach ($resultProduit as $value) { $tabProduits[$value['code']] = $value; } $selectUserAdm = "SELECT * FROM sav.utilisateur"; $resultAdm = $connPostgre->query($selectUserAdm)->fetchAll(); $tabAdmin = []; foreach ($resultAdm as $value) { $tabAdmin[$value['login']] = $value; } $selectUserResponsable = "SELECT * FROM sav.tmp_correspondance_agent"; $resultCorrAgent = $connPostgre->query($selectUserResponsable)->fetchAll(); $tabCorrAgent = []; foreach ($resultCorrAgent as $value) { $tabCorrAgent[$value['code_agent_oracle']][$value['idsite']] = $value; } $selectAnomalie = "SELECT id FROM sav.anomalie_oracle"; $stmt = $connPostgre->query($selectAnomalie); $resultIdAnomalie = $stmt->fetchAll(PDO::FETCH_COLUMN); var_dump('fini import sav ' . date('h:i:s')); $idPlanning = 0; $idIntervention = 0; $idAnnulation = 0; $idAnomalie = 0; //new foreach ($sites as $cle => $site) { if (!$connPostgre->inTransaction()) { $connPostgre->beginTransaction(); } oci_close($connOracle[$site['nom']]); $connOracle[$site['nom']] = getConnexionOracle($site['nom'], $settings); var_dump('Planning ' . $site['nom']); $sql = "SELECT * FROM PIECE_INTERVENTION "; $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql); $nbResultProduits = oci_fetch_all($stid, $resultProduits, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW); $tabProduitsOracle = []; foreach ($resultProduits as $cle => $rowProduit) { $tabProduitsOracle[$rowProduit['NUM_FICHE']][] = $rowProduit; } oci_free_statement($stid); $sql = "SELECT * FROM FMOB_ENVOI_CLIENT"; $selectDocuments = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql); $nbResultDocuments = oci_fetch_all($selectDocuments, $documents, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW); $tabDocuments = []; foreach ($documents as $cle => $rowDoc) { $tabDocuments[$rowDoc['IDPLN_PLANNING']][] = $rowDoc; } oci_free_statement($selectDocuments); $sql = "SELECT * FROM SOMMES_A_PERCEVOIR "; $selectPreEncaissement = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql); $nbResultPreEncaissement = oci_fetch_all($selectPreEncaissement, $preEncaissement, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW); $tabPreEncaissement = []; foreach ($preEncaissement as $cle => $rowPre) { $tabPreEncaissement[$rowDoc['IDPLN_PLANNING']][] = $rowPre; } oci_free_statement($selectPreEncaissement); $sql = "SELECT i.IDCHAUDIERE, i.IDPLN_PLANNING, i.DATE_INTERVENTION, i.CODE_AGENT, il.DATE_ACTION, a.CODE_AGENT AS CODE_AGENT_PLANNING FROM INTERVENTION i JOIN INTERV_LOG il ON i.IDCHAUDIERE = il.IDCHAUDIERE AND i.DATE_INTERVENTION = il.DATE_INTERVENTION AND i.CODE_AGENT = il.CODE_AGENT JOIN AGENT_LOGIN_PORTAIL a ON a.LOGIN_PORTAIL = il.USER_ACTION WHERE il.CODE_ACTION IN (1, 2, 3, 4, 53) ORDER BY i.IDPLN_PLANNING, il.DATE_ACTION ASC"; $selectAgentPlanning = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql); $nbResultAgentPlanning = oci_fetch_all($selectAgentPlanning, $agentPlanning, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW); $tabAgentPlanning = []; foreach ($agentPlanning as $cle => $rowAgentPlanning) { $tabAgentPlanning[$rowAgentPlanning['IDPLN_PLANNING']] = $rowAgentPlanning; } oci_free_statement($selectAgentPlanning); var_dump('fini import oracle ' . date('h:i:s')); $sql = "SELECT p.IDPLN_PLANNING, p.HORAIRE_SPECIFIE, p.HEURE_FIN HEURE_FIN_SPECIFIEE, p.ORDRE_AFFICHAGE, p.DUREE, p.status, p.IDPLN_CLIENT_ABSENT, p.IDPLN_TEST, p.TYPEDUREE, p.IDPLN_HORAIRES_PASSAGE, p.IDCHAUDIERE, p.IDSITE_LIVRAISON, p.IDPLN_RESERVE, pp.INTERV_REGROUP, pf.*, pfi.LIB_COURT as CODE_FORMATION, pc.*, pm.IDPLN_MOTIF_MALADIE, pm.IDPLN_MALADIE, pm.DUREE AS DUREE_MALADIE, pm.AUTRE_MOTIF, pr.*, pca.IDPLN_MOTIF_ABSENCE, pca.AUTRE_MOTIF AUTRE_MOTIF_ABSENCE, pca.OBSERVATIONS_ABSENCE, pma.LIB_LONG LIBELLE_MOTIF_ABSENCE, pt.IDPLN_TEST_INTITULE, pt.IDPLN_TEST_LIEU, pre.IDPLN_MOTIF_RESERVE, pre.AUTRE_MOTIF AUTRE_MOTIF_RESERVE, i.DATE_INTERVENTION, i.CODE_INTERVENTION, i.CODE_AGENT, i.NUM_FICHE, i.DATE_PROGRAMMATION, i.DATE_VALIDATION, i.MOMENT, i.AVIS_PASSAGE, i.TEMPS, i.MODE_PAIEMENT, i.MONTANT_ACOMPTE, i.MONTANT_THEORIQUE, i.MONTANT_FACTURE, i.ANOMALIE, i.RETOUR, i.DEVIS, i.BON_COMMANDE, i.TAUX_TVA, i.PRIX_PIECES_HT, i.MONTANT_ACOMPTE_HT, i.MONTANT_THEORIQUE_HT, i.MONTANT_FACTURE_HT, i.HEURE_DEB, i.HEURE_FIN HEURE_FIN_REELLE, i.DEVISE, i.QTE_CO, i.OBSERVATIONS, i.ATTESTATION_ENTRETIEN_SIGNEE, i.CGV_ACCEPTEES, i.COMMENTAIRE_CLIENT, i.METHODE_ENVOI_RAPPORT, i.MOTIF_MODIF_CODE_INT, i.CLI_REFUS_SIGNATURE, i.COMMENTAIRE_REFUS_SIGNATURE, i.FICHE_PAPIER_LIEE, i.CODE_INTERVENTION_INIT, i.ENCAISSEMENT_EFFECTUE, i.AVIS_PASSAGE_MAIL, TO_CHAR(i.DATE_INTERVENTION, 'YY') as ANNEE_FACTURE, TO_CHAR(i.DATE_INTERVENTION, 'YYYY') as ANNEE, TO_CHAR(i.DATE_INTERVENTION, 'MM') as MOIS, i.NB_DEPLACEMENT, i.PRIX_DEPLACEMENT_HT, i.PRIX_DEPLACEMENT, i.NB_MO, i.PRIX_MO_HT, i.PRIX_MO, t.CODE_TVA, v.VALIDE_LE as VALIDATION_TECHNIQUE_LE, v.VALIDE_PAR as VALIDATION_TECHNIQUE_PAR, m.MEMOC, v.TYPE_VALIDATION, v.COMMENTAIRE as COMMENTAIRE_TECHNIQUE FROM INTERVENTION i LEFT JOIN PLN_PLANNING p ON p.IDCHAUDIERE = i.IDCHAUDIERE AND p.IDPLN_PLANNING = i.IDPLN_PLANNING LEFT JOIN PLN_PLANIF_MULTI pp ON p.IDPLN_PLANNING = pp.IDPLN_PLANNING LEFT JOIN PLN_FORMATION pf ON p.IDPLN_FORMATION= pf.IDPLN_FORMATION LEFT JOIN PLN_FORMATION_INTITULE pfi ON pf.IDPLN_FORMATION_INTITULE= pfi.IDPLN_FORMATION_INTITULE LEFT JOIN PLN_CONTROLE_VOITURE pc ON p.IDPLN_CONTROLE_VOITURE= pc.IDPLN_CONTROLE_VOITURE LEFT JOIN PLN_MALADIE pm ON p.IDPLN_MALADIE= pm.IDPLN_MALADIE LEFT JOIN PLN_REUNION pr ON p.IDPLN_REUNION= pr.IDPLN_REUNION LEFT JOIN PLN_CLIENT_ABSENT pca ON p.IDPLN_CLIENT_ABSENT= pca.IDPLN_CLIENT_ABSENT LEFT JOIN PLN_MOTIF_ABSENCE pma ON pca.IDPLN_MOTIF_ABSENCE=pma.IDPLN_MOTIF_ABSENCE LEFT JOIN PLN_TEST pt ON p.IDPLN_TEST= pt.IDPLN_TEST LEFT JOIN PLN_RESERVE pre ON p.IDPLN_RESERVE=pre.IDPLN_RESERVE LEFT JOIN MEMO_INT m ON i.IDPLN_PLANNING = M.IDPLN_PLANNING LEFT JOIN TAUX_TVA t ON t.TAUX = i.TAUX_TVA LEFT JOIN VALIDATION_FICHE v ON v.NUM_FICHE = i.NUM_FICHE"; $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql); //new $insertPlanning = "INSERT INTO sav.planning(id, idtype_planification_init, idutilisateur_agent, idutilisateur_planning, idequipement, date_creation, date, statut, heure_debut, heure_fin, rang_tri, idtype_duree, duree, idmoment_passage, idhoraire_passage, horaire_specifie, heure_debut_specifiee, heure_fin_specifiee, code_regroup, idsite) VALUES "; $insertEvenement = "INSERT INTO sav.evenement(idplanning, idtype_planification, idagence, idevenement_lieu, idevenement_objet, idformation, est_formateur, objet_si_autre, duree) VALUES "; $insertIntervention = "INSERT INTO sav.intervention(id, idplanning, idequipement, avis_passage, avis_passage_mail, memo, temps, idintervention_motif, horaire_a_controler, mesure_co, observations, cgv_acceptees, commentaire_client, idtype_envoi_rapport, motif_modif_code_int, cli_refus_signature, commentaire_refus_signature, fiche_papier_liee, encaissement_effectue, idtype_planification, idutilisateur_validation_technique, date_validation_technique, idtype_validation_technique, commentaire_technique, idutilisateur_validation_adm, date_validation_adm, idtype_validation_adm, entretien, idsite, idretour, numfiche_oracle) VALUES "; $insertInterventionAnnulation = "INSERT INTO sav.intervention_annulation(id, idintervention, idintervention_motif_annulation, idintervention_motif_absence, autre_motif, observations) VALUES "; $insertInterventionAnomalie = "INSERT INTO sav.intervention_anomalie(id, idintervention, idanomalie_oracle) VALUES "; $insertInterventionDocuments = "INSERT INTO sav.intervention_document(idintervention, nom, exclusif_courrier, chemin) VALUES "; $insertInterventionPreEncaissement = "INSERT INTO sav.intervention_pre_encaissement(idintervention, idmoyen_paiement, montant_ttc, idutilisateur_reception, date_reception, idutilisateur_responsable) VALUES"; $insertTmp = "INSERT INTO sav.tmp_correspondance_intervention_planning(idintervention, idpln_planning, idsite) VALUES "; $insertFacture = "INSERT INTO sav.facture(id, num_facture, idequipement, idadresse, idfacture_type, idfacture_categorie, montant_ht, montant_ttc, date_facture, idsite, idintervention, idtype_devise, idfacture_acompte, num_devis_papier) VALUES "; $insertFactureLigne = "INSERT INTO sav.facture_ligne(idfacture, idproduit, code_article, designation, quantite, pu_ht, idtaux_tva, prix_total_ht, idproduit_facture_affectation, motif_service) VALUES "; $insertPaiement = "INSERT INTO sav.paiement(idfacture, idmoyen_paiement, montant_ttc, date) VALUES "; $insertTmpPlanning = "INSERT INTO sav.tmp_correspondance_planning(idsite, idplanning, idplanning_oracle) VALUES "; $count = 0; while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) { if (!isset($row['CODE_AGENT']) || is_null($row['CODE_AGENT'])) { continue; } $count++; if ($count % 5000 == 0) { var_dump($count . ' ' . date('h:i:s')); } if (isset($row['CODE_INTERVENTION']) && ($row['CODE_INTERVENTION'] == 'ABANDON' || $row['CODE_INTERVENTION'] == 'RCGV')) { $idTypeVisite = (isset($row['CODE_INTERVENTION_INIT']) && isset($typeVisite[$row['CODE_INTERVENTION_INIT']])) ? "'" . $typeVisite[$row['CODE_INTERVENTION_INIT']] . "'" : "null"; } else { $idTypeVisite = (isset($row['CODE_INTERVENTION']) && isset($typeVisite[$row['CODE_INTERVENTION']])) ? "'" . $typeVisite[$row['CODE_INTERVENTION']] . "'" : "null"; } $idUser = isset($tabCorrAgent[$row['CODE_AGENT']][$site['id']]) ? $tabCorrAgent[$row['CODE_AGENT']][$site['id']]['idutilisateur'] : "null"; $idUserPlanning = (isset($row['IDPLN_PLANNING']) && isset($tabAgentPlanning[$row['IDPLN_PLANNING']]) && isset($tabAgentPlanning[$row['IDPLN_PLANNING']]['CODE_AGENT_PLANNING']) && isset($tabCorrAgent[$tabAgentPlanning[$row['IDPLN_PLANNING']]['CODE_AGENT_PLANNING']]) && isset($tabCorrAgent[$tabAgentPlanning[$row['IDPLN_PLANNING']]['CODE_AGENT_PLANNING']][$site['id']])) ? $tabCorrAgent[$tabAgentPlanning[$row['IDPLN_PLANNING']]['CODE_AGENT_PLANNING']][$site['id']]['idutilisateur'] : "null"; $idEquipement = (isset($row['IDCHAUDIERE']) && isset($correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']])) ? "'" . $correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idequipement'] . "'" : "null"; $distribution = '1'; $row['STATUS'] = (!isset($row['STATUS']) || is_null($row['STATUS']) || !isset($correspondanceStatut[$row['STATUS']])) ? "null" : "'" . $correspondanceStatut[$row['STATUS']] . "'"; if ($row['STATUS'] == 0) { $distribution = '0'; } $idPlanning++; if (isset($row['IDPLN_FORMATION']) && !is_null($row['IDPLN_FORMATION']) && $row['IDPLN_FORMATION'] != 0) { $agenceFormation = "null"; $lieuFormation = "null"; if (isset($row['IDPLN_FORMATION_LIEU']) && isset($correspondanceFormationLieu[$row['IDPLN_FORMATION_LIEU']])) { if ($correspondanceFormationLieu[$row['IDPLN_FORMATION_LIEU']]['agence']) { $agenceFormation = "'" . $correspondanceFormationLieu[$row['IDPLN_FORMATION_LIEU']]['id'] . "'"; } else { $lieuFormation = "'" . $correspondanceFormationLieu[$row['IDPLN_FORMATION_LIEU']]['id'] . "'"; } } $objetFormation = (isset($row['IDPLN_FORMATION_ORGANISME']) && isset($correspondanceFormationOrganisme[$row['IDPLN_FORMATION_ORGANISME']])) ? "'" . $correspondanceFormationOrganisme[$row['IDPLN_FORMATION_ORGANISME']] . "'" : "null"; $formation = (isset($row['IDPLN_FORMATION_INTITULE']) && isset($correspondanceFormation[$site['id']][$row['IDPLN_FORMATION_INTITULE']])) ? "'" . $correspondanceFormation[$site['id']][$row['IDPLN_FORMATION_INTITULE']] . "'" : "null"; $row['EST_FORMATEUR'] = (!isset($row['EST_FORMATEUR']) || is_null($row['EST_FORMATEUR'])) ? 'false' : 'true'; $insertEvenement .= "('" . $idPlanning . "'," . $idTypeVisite . "," . $agenceFormation . "," . $lieuFormation . "," . $objetFormation . "," . $formation . ", " . $row['EST_FORMATEUR'] . ", null, null),"; } else if (isset($row['IDPLN_CONTROLE_VOITURE']) && !is_null($row['IDPLN_CONTROLE_VOITURE']) && $row['IDPLN_CONTROLE_VOITURE'] != 0) { $agenceVoiture = "null"; $lieuVoiture = "null"; if (isset($row['IDPLN_VOITURE_LIEU']) && isset($correspondanceVoitureLieu[$row['IDPLN_VOITURE_LIEU']])) { if ($correspondanceVoitureLieu[$row['IDPLN_VOITURE_LIEU']]['agence']) { $agenceVoiture = "'" . $correspondanceVoitureLieu[$row['IDPLN_VOITURE_LIEU']]['id'] . "'"; } else { $lieuVoiture = "'" . $correspondanceVoitureLieu[$row['IDPLN_VOITURE_LIEU']]['id'] . "'"; } } $objetVoiture = (isset($row['IDPLN_VOITURE_ACTION']) && isset($correspondanceVoitureAction[$row['IDPLN_VOITURE_ACTION']])) ? "'" . $correspondanceVoitureAction[$row['IDPLN_VOITURE_ACTION']] . "'" : "null"; $insertEvenement .= "('" . $idPlanning . "'," . $idTypeVisite . "," . $agenceVoiture . "," . $lieuVoiture . "," . $objetVoiture . ",null, null, null, null),"; } else if (isset($row['IDPLN_MALADIE']) && !is_null($row['IDPLN_MALADIE']) && $row['IDPLN_MALADIE'] != 0) { $row['AUTRE_MOTIF'] = (!isset($row['AUTRE_MOTIF']) || is_null($row['AUTRE_MOTIF'])) ? "null" : "'" . str_replace("'", "''", $row['AUTRE_MOTIF']) . "'"; $objetMaladie = (isset($row['IDPLN_MOTIF_MALADIE']) && isset($correspondanceMotifMaladie[$row['IDPLN_MOTIF_MALADIE']])) ? "'" . $correspondanceMotifMaladie[$row['IDPLN_MOTIF_MALADIE']] . "'" : "null"; $row['DUREE_MALADIE'] = (!isset($row['DUREE_MALADIE']) || is_null($row['DUREE_MALADIE'])) ? "null" : "'" . $row['DUREE_MALADIE'] . "'"; $insertEvenement .= "('" . $idPlanning . "'," . $idTypeVisite . ",null,null," . $objetMaladie . ",null, null, " . $row['AUTRE_MOTIF'] . ", " . $row['DUREE_MALADIE'] . "),"; } else if (isset($row['IDPLN_REUNION']) && !is_null($row['IDPLN_REUNION']) && $row['IDPLN_REUNION'] != 0) { $agenceReunion = "null"; $lieuReunion = "null"; if (!is_null($row['IDPLN_REUNION_LIEU']) && isset($row['IDPLN_REUNION_LIEU']) && isset($correspondanceReunionLieu[$row['IDPLN_REUNION_LIEU']])) { if ($correspondanceReunionLieu[$row['IDPLN_REUNION_LIEU']]['agence']) { $agenceReunion = "'" . $correspondanceReunionLieu[$row['IDPLN_REUNION_LIEU']]['id'] . "'"; } else { $lieuReunion = "'" . $correspondanceReunionLieu[$row['IDPLN_REUNION_LIEU']]['id'] . "'"; } } $objetReunion = (isset($row['IDPLN_REUNION_ORDRE_JOUR']) && isset($correspondanceReunionOrdreJour[$row['IDPLN_REUNION_ORDRE_JOUR']])) ? "'" . $correspondanceReunionOrdreJour[$row['IDPLN_REUNION_ORDRE_JOUR']] . "'" : "null"; $insertEvenement .= "('" . $idPlanning . "'," . $idTypeVisite . "," . $agenceReunion . "," . $lieuReunion . "," . $objetReunion . ",null, null, null, null),"; } else if (isset($row['IDPLN_TEST']) && !is_null($row['IDPLN_TEST']) && $row['IDPLN_TEST'] != 0) { $agenceTest = "null"; $lieuTest = "null"; if (isset($row['IDPLN_TEST_LIEU']) && isset($correspondanceTestLieu[$row['IDPLN_TEST_LIEU']])) { if ($correspondanceTestLieu[$row['IDPLN_TEST_LIEU']]['agence']) { $agenceTest = "'" . $correspondanceTestLieu[$row['IDPLN_TEST_LIEU']]['id'] . "'"; } else { $lieuTest = "'" . $correspondanceTestLieu[$row['IDPLN_TEST_LIEU']]['id'] . "'"; } } $objetTest = (isset($row['IDPLN_TEST_INTITULE']) && isset($correspondanceTestIntitule[$row['IDPLN_TEST_INTITULE']])) ? "'" . $correspondanceTestIntitule[$row['IDPLN_TEST_INTITULE']] . "'" : "null"; $insertEvenement .= "('" . $idPlanning . "'," . $idTypeVisite . "," . $agenceTest . "," . $lieuTest . "," . $objetTest . ",null, null, null, null),"; } else if (isset($row['IDPLN_RESERVE']) && !is_null($row['IDPLN_RESERVE']) && $row['IDPLN_RESERVE'] != 0) { $row['AUTRE_MOTIF_RESERVE'] = (!isset($row['AUTRE_MOTIF_RESERVE']) || is_null($row['AUTRE_MOTIF_RESERVE'])) ? "null" : "'" . str_replace("'", "''", $row['AUTRE_MOTIF_RESERVE']) . "'"; $objetReserve = (isset($row['IDPLN_MOTIF_RESERVE']) && isset($correspondanceMotifReserve[$row['IDPLN_MOTIF_RESERVE']])) ? "'" . $correspondanceMotifReserve[$row['IDPLN_MOTIF_RESERVE']] . "'" : "null"; $insertEvenement .= "('" . $idPlanning . "'," . $idTypeVisite . ",null,null," . $objetReserve . ",null, null, " . $row['AUTRE_MOTIF_RESERVE'] . ", null),"; } $idAgence = "null"; if (isset($row['IDSITE_LIVRAISON']) && !is_null($row['IDSITE_LIVRAISON']) && $row['IDSITE_LIVRAISON'] != 0) { if ($idAgenceDev == 1) { //import de seulement Bron $idAgence = $idAgenceDev; } else if ($site['nom'] == 'ROSNY') { if ($row['IDSITE_LIVRAISON'] == '798931') { $idAgence = 1; } else if ($row['IDSITE_LIVRAISON'] == '792163') { $idAgence = 2; } else if ($row['IDSITE_LIVRAISON'] == '792164') { $idAgence = 3; } } else { $idAgence = $site['id'] + 2; } } $row['HEURE_DEB'] = (!isset($row['HEURE_DEB']) || is_null($row['HEURE_DEB']) || !preg_match('#^([01]?[0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?$#', $row['HEURE_DEB'])) ? "null" : "'" . $row['HEURE_DEB'] . "'"; $row['HEURE_DEBUT'] = (!isset($row['HEURE_DEBUT']) || is_null($row['HEURE_DEBUT']) || !preg_match('#^([01]?[0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?$#', $row['HEURE_DEBUT'])) ? "null" : "'" . $row['HEURE_DEBUT'] . "'"; $row['HEURE_FIN_REELLE'] = (!isset($row['HEURE_FIN_REELLE']) || is_null($row['HEURE_FIN_REELLE']) || !preg_match('#^([01]?[0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?$#', $row['HEURE_FIN_REELLE'])) ? "null" : "'" . $row['HEURE_FIN_REELLE'] . "'"; $row['HEURE_FIN_SPECIFIEE'] = (!isset($row['HEURE_FIN_SPECIFIEE']) || is_null($row['HEURE_FIN_SPECIFIEE']) || !preg_match('#^([01]?[0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?$#', $row['HEURE_FIN_SPECIFIEE'])) ? "null" : "'" . $row['HEURE_FIN_SPECIFIEE'] . "'"; $row['HORAIRE_SPECIFIE'] = (!isset($row['HORAIRE_SPECIFIE']) || is_null($row['HORAIRE_SPECIFIE']) || $row['HORAIRE_SPECIFIE'] == '0') ? 'false' : 'true'; $momentPassage = (!isset($row['MOMENT']) || is_null($row['MOMENT']) || !isset($correspondanceMomentPassage[$row['MOMENT']])) ? "null" : "'" . $correspondanceMomentPassage[$row['MOMENT']] . "'"; $horairePassage = (!isset($row['IDPLN_HORAIRES_PASSAGE']) || is_null($row['IDPLN_HORAIRES_PASSAGE']) || $row['IDPLN_HORAIRES_PASSAGE'] == '0' || !isset($correspondanceHorairePassage[$site['id']][$row['IDPLN_HORAIRES_PASSAGE']])) ? "null" : "'" . $correspondanceHorairePassage[$site['id']][$row['IDPLN_HORAIRES_PASSAGE']] . "'"; $row['INTERV_REGROUP'] = (!isset($row['INTERV_REGROUP']) || is_null($row['INTERV_REGROUP'])) ? "null" : "'" . $row['INTERV_REGROUP'] . "'"; $row['ORDRE_AFFICHAGE'] = (!isset($row['ORDRE_AFFICHAGE']) || is_null($row['ORDRE_AFFICHAGE'])) ? "null" : "'" . $row['ORDRE_AFFICHAGE'] . "'"; $row['DUREE'] = (!isset($row['DUREE']) || is_null($row['DUREE'])) ? "null" : "'" . $row['DUREE'] . "'"; if (isset($row['TYPEDUREE']) && $row['TYPEDUREE'] > 4 || !isset($row['TYPEDUREE'])) { $row['TYPEDUREE'] = 4; } $dateProgrammation = (!isset($row['DATE_PROGRAMMATION']) || is_null($row['DATE_PROGRAMMATION'])) ? "null" : "'" . $row['DATE_PROGRAMMATION'] . "'"; $insertPlanning .= "('" . $idPlanning . "'," . $idTypeVisite . "," . $idUser . "," . $idUserPlanning . "," . $idEquipement . "," . $dateProgrammation . ",'" . $row['DATE_INTERVENTION'] . "'," . $row['STATUS'] . "," . $row['HEURE_DEB'] . "," . $row['HEURE_FIN_REELLE'] . "," . $row['ORDRE_AFFICHAGE'] . ",'" . $row['TYPEDUREE'] . "'," . $row['DUREE'] . "," . $momentPassage . "," . $horairePassage . "," . $row['HORAIRE_SPECIFIE'] . "," . $row['HEURE_DEBUT'] . "," . $row['HEURE_FIN_SPECIFIEE'] . ", " . $row['INTERV_REGROUP'] . ", '" . $site['id'] . "'),"; //intervention : ajouter seulement les "vraies" interventions //$tabNoIntervention = array("ABSENT", "ABANDON", "CTRL VOITURE", "MAGASIN", "BUREAU", "VOITURE", "FORMATION", "REUNION", "STOCKVOITURE", "TECHNIQUE", "TEST", "REFUS_CGV", "RCGV"); $tabNoIntervention = array("CTRL VOITURE", "MAGASIN", "BUREAU", "VOITURE", "FORMATION", "REUNION", "STOCKVOITURE", "TECHNIQUE", "TEST"); if (isset($row['CODE_INTERVENTION']) && !in_array($row['CODE_INTERVENTION'], $tabNoIntervention)) { $idEquipement = (isset($row['IDCHAUDIERE']) && isset($correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']])) ? "'" . $correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idequipement'] . "'" : "null"; $idAdresse = (isset($row['IDCHAUDIERE']) && isset($correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]) && !is_null($correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idadresse']) && $correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idadresse'] != '') ? "'" . $correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idadresse'] . "'" : "null"; $idIntervention++; $idinterventionMotif = "null"; $row['MEMOC'] = (!isset($row['MEMOC']) || is_null($row['MEMOC'])) ? "null" : "'" . str_replace("'", "''", $row['MEMOC']) . "'"; if (isset($row['BON_COMMANDE']) && !is_null($row['BON_COMMANDE']) && $row['BON_COMMANDE'] != '') { if (in_array($row['BON_COMMANDE'], ['DEGAT DE', 'DEGAT EA', 'DEGAT/EA', 'DEGATS', 'DEGATS D', 'DEGATS E', 'DEGATS/E', 'DEGATSEA', 'FUITE', 'FUITE CO', 'FUITE EA', 'FUITE/EA', 'PLUIE', 'INFILTRA'])) { $idinterventionMotif = '1'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['ORAGE', 'ORAGES', 'FOUDRE'])) { $idinterventionMotif = '2'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['INCENDIE'])) { $idinterventionMotif = '3'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['INNONDAT', 'INONDAT°', 'INONDATI'])) { $idinterventionMotif = '4'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['COURANT', 'ELECTRIC', 'PB ELECT', 'SURTENS', 'SURTENSI', 'SURTENTI'])) { $idinterventionMotif = '5'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['CHAUFFAG'])) { $idinterventionMotif = '6'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['INTEMPER', 'TEMPETE'])) { $idinterventionMotif = '7'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['NUISIBLE', 'THERMISE'])) { $idinterventionMotif = '8'; $row['BON_COMMANDE'] = null; } else if (in_array($row['BON_COMMANDE'], ['TRAVAUX'])) { $idinterventionMotif = '9'; $row['BON_COMMANDE'] = null; } } $row['BON_COMMANDE'] = (!isset($row['BON_COMMANDE']) || is_null($row['BON_COMMANDE'])) ? "null" : "'" . $row['BON_COMMANDE'] . "'"; $row['QTE_CO'] = (!isset($row['QTE_CO']) || is_null($row['QTE_CO'])) ? "null" : "'" . $row['QTE_CO'] . "'"; $row['OBSERVATIONS'] = (!isset($row['OBSERVATIONS']) || is_null($row['OBSERVATIONS'])) ? "null" : "'" . str_replace("'", "''", $row['OBSERVATIONS']) . "'"; $row['ATTESTATION_ENTRETIEN_SIGNEE'] = (!isset($row['ATTESTATION_ENTRETIEN_SIGNEE']) || is_null($row['ATTESTATION_ENTRETIEN_SIGNEE'])) ? "null" : "'" . $row['ATTESTATION_ENTRETIEN_SIGNEE'] . "'"; $row['CGV_ACCEPTEES'] = (!isset($row['CGV_ACCEPTEES']) || is_null($row['CGV_ACCEPTEES'])) ? "null" : "'" . $row['CGV_ACCEPTEES'] . "'"; $row['COMMENTAIRE_CLIENT'] = (!isset($row['COMMENTAIRE_CLIENT']) || is_null($row['COMMENTAIRE_CLIENT'])) ? "null" : "'" . str_replace("'", "''", $row['COMMENTAIRE_CLIENT']) . "'"; $row['CLI_REFUS_SIGNATURE'] = (!isset($row['CLI_REFUS_SIGNATURE']) || is_null($row['CLI_REFUS_SIGNATURE'])) ? "null" : "'" . $row['CLI_REFUS_SIGNATURE'] . "'"; $row['COMMENTAIRE_REFUS_SIGNATURE'] = (!isset($row['COMMENTAIRE_REFUS_SIGNATURE']) || is_null($row['COMMENTAIRE_REFUS_SIGNATURE'])) ? "null" : "'" . str_replace("'", "''", $row['COMMENTAIRE_REFUS_SIGNATURE']) . "'"; $row['FICHE_PAPIER_LIEE'] = (!isset($row['FICHE_PAPIER_LIEE']) || is_null($row['FICHE_PAPIER_LIEE'])) ? "null" : "'" . $row['FICHE_PAPIER_LIEE'] . "'"; $row['MOTIF_MODIF_CODE_INT'] = (!isset($row['MOTIF_MODIF_CODE_INT']) || is_null($row['MOTIF_MODIF_CODE_INT'])) ? "null" : "'" . str_replace("'", "''", $row['MOTIF_MODIF_CODE_INT']) . "'"; $row['ENCAISSEMENT_EFFECTUE'] = (!isset($row['ENCAISSEMENT_EFFECTUE']) || is_null($row['ENCAISSEMENT_EFFECTUE'])) ? "null" : "'" . $row['ENCAISSEMENT_EFFECTUE'] . "'"; $nomSite = ($site['nom'] == "BRON" || $site['nom'] == "ROSNY") ? 'SAV' : $site['nom']; $row['RETOUR'] = (!isset($row['RETOUR']) || is_null($row['RETOUR'])) ? "null" : "'" . $correspondanceRetour[$row['RETOUR']][$nomSite] . "'"; $idTypeEnvoiRapport = "null"; if (!isset($row['METHODE_ENVOI_RAPPORT']) || is_null($row['METHODE_ENVOI_RAPPORT'])) { if ($row['DATE_INTERVENTION'] < date("d/m/y")) { $idTypeEnvoiRapport = "2"; } } else { if ($row['DATE_INTERVENTION'] == 0) { $idTypeEnvoiRapport = "2"; } else { $idTypeEnvoiRapport = "1"; } } if (!isset($row['VALIDE_PAR']) || is_null($row['VALIDE_PAR'])) { $idUserValidationAdm = "null"; $idTypeValidationAdm = "null"; } else if (isset($row['VALIDE_PAR']) && in_array($row['VALIDE_PAR'], ['Validation Automatique', 'Contrôle automatique'])) { $idUserValidationAdm = "null"; $idTypeValidationAdm = "1"; } else { if (isset($tabAdmin[$row['VALIDE_PAR']])) { $idUserValidationAdm = "'" . $tabAdmin[$row['VALIDE_PAR']]['id'] . "'"; } else { $idUserValidationAdm = "null"; } $idTypeValidationAdm = "2"; } $dateValidationAdm = (!isset($row['DATE_VALIDATION']) || is_null($row['DATE_VALIDATION'])) ? "null" : "'" . $row['DATE_VALIDATION'] . "'"; if (!isset($row['VALIDATION_TECHNIQUE_PAR']) || is_null($row['VALIDATION_TECHNIQUE_PAR'])) { $idUserValidationTech = "null"; $commentaireValidationTech = "null"; $idTypeValidationTech = "null"; } else if (isset($row['VALIDATION_TECHNIQUE_PAR']) && in_array($row['VALIDATION_TECHNIQUE_PAR'], ['Validation Automatique', 'Contrôle automatique'])) { $idUserValidationTech = "null"; $commentaireValidationTech = isset($row['COMMENTAIRE_TECHNIQUE']) ? "'" . str_replace("'", "''", $row['COMMENTAIRE_TECHNIQUE']) . "'" : "null"; $idTypeValidationTech = "'" . ($row['TYPE_VALIDATION'] + 1) . "'"; } else { $idUserValidationTech = (isset($tabAdmin[$row['VALIDATION_TECHNIQUE_PAR']])) ? "'" . $tabAdmin[$row['VALIDATION_TECHNIQUE_PAR']]['id'] . "'" : "null"; $commentaireValidationTech = isset($row['COMMENTAIRE_TECHNIQUE']) ? "'" . str_replace("'", "''", $row['COMMENTAIRE_TECHNIQUE']) . "'" : "null"; $idTypeValidationTech = "'" . ($row['TYPE_VALIDATION'] + 1) . "'"; } $dateValidationTech = (!isset($row['VALIDATION_TECHNIQUE_LE']) || is_null($row['VALIDATION_TECHNIQUE_LE'])) ? "null" : "'" . $row['VALIDATION_TECHNIQUE_LE'] . "'"; if ($idTypeValidationTech == '1') {//Suppression du type "non validé" $idTypeValidationTech = "null"; } $entretien = 0; $tabNoIntervention = array("ABSENT", "ABANDON", "REFUS_CGV", "RCGV"); if (isset($row['CODE_INTERVENTION_INIT']) && !in_array($row['CODE_INTERVENTION'], $tabNoIntervention)) { $idTypeVisite = (isset($row['CODE_INTERVENTION_INIT']) && isset($typeVisite[$row['CODE_INTERVENTION_INIT']])) ? "'" . $typeVisite[$row['CODE_INTERVENTION_INIT']] . "'" : "null"; } else { $idTypeVisite = "null"; } if (isset($row['ANOMALIE']) && !is_null($row['ANOMALIE'])) { //regroupé par deux if (strlen($row['ANOMALIE']) % 2 == 1) { $row['ANOMALIE'] = '0' . $row['ANOMALIE']; } $anomalies = str_split($row['ANOMALIE'], 2); $tabAnomalies = []; foreach ($anomalies as $anomalie) { if ($anomalie != 0 && !in_array($anomalie, $tabAnomalies) && in_array($anomalie, $resultIdAnomalie)) { //pas déjà ajoutée et code contenu dans la table anomalie $tabAnomalies[] = $anomalie; $insertInterventionAnomalie .= "('" . $idAnomalie . "','" . $idIntervention . "','" . $anomalie . "'),"; $idAnomalie++; } } } if (isset($row['IDPLN_PLANNING']) && !is_null($row['IDPLN_PLANNING'])) { $insertTmp .= "('" . $idIntervention . "','" . $row['IDPLN_PLANNING'] . "', '" . $site['id'] . "'),"; if (isset($row['NUM_FICHE']) && !is_null($row['NUM_FICHE']) && $row['NUM_FICHE'] != '') { if (isset($tabDocuments[$row['IDPLN_PLANNING']])) { foreach ($tabDocuments[$row['IDPLN_PLANNING']] as $document) { $explodeChemin = explode("\\", $document['CHEMIN_PDF']); $explodeChemin = $explodeChemin[count($explodeChemin) - 1]; $nom = explode('_', $explodeChemin); $nom = ucfirst(mb_strtolower($nom[0] . ' ' . $nom[1])); $exclusif = ($nom == 'Lettre accompagnement' ? '1' : '0'); $entretien = ($nom == 'Attestation entretien' ? '1' : '0'); $insertInterventionDocuments .= "('" . $idIntervention . "','" . $nom . "', '" . $exclusif . "','" . $document['CHEMIN_PDF'] . "'),"; } if (isset($tabPreEncaissement[$row['IDPLN_PLANNING']])) { foreach ($tabPreEncaissement[$row['IDPLN_PLANNING']] as $ligne) { $idMoyenPaiement = (isset($ligne['MODE_PAIEMENT']) && isset($moyenPaiement[$ligne['MODE_PAIEMENT']])) ? "'" . $moyenPaiement[$ligne['MODE_PAIEMENT']] . "'" : "null"; $idUserReception = (isset($tabAdmin[strtolower($ligne['RECUPERE_PAR'])])) ? "'" . $tabAdmin[strtolower($ligne['RECUPERE_PAR'])]['id'] . "'" : "null"; $idUserResponsable = (isset($tabCorrAgent[$ligne['CODE_GROUPE']]) && isset($tabCorrAgent[$ligne['CODE_GROUPE']][$site['id']])) ? "'" . $tabCorrAgent[$ligne['CODE_GROUPE']][$site['id']]['idutilisateur'] . "'" : "null"; $insertInterventionPreEncaissement .= "('" . $idIntervention . "'," . $idMoyenPaiement . ", " . str_replace(',', '.', $ligne['MONTANT']) . "," . $idUserReception . ",'" . $ligne['RECUPERE_LE'] . "'," . $idUserResponsable . "),"; } } } } } $intNumFiche = (!isset($row['NUM_FICHE']) || is_null($row['NUM_FICHE'])) ? 0 : $row['NUM_FICHE']; $row['NUM_FICHE'] = (!isset($row['NUM_FICHE']) || is_null($row['NUM_FICHE'])) ? "null" : "'" . $row['NUM_FICHE'] . "'"; $insertIntervention .= "('" . $idIntervention . "','" . $idPlanning . "'," . $idEquipement . ",'" . (isset($row['AVIS_PASSAGE']) && $row['AVIS_PASSAGE'] == 'O' ? '1' : '0') . "','" . $row['AVIS_PASSAGE_MAIL'] . "'," . $row['MEMOC'] . ",'" . (isset($row['TEMPS']) ? $row['TEMPS'] : 0) . "'," . $idinterventionMotif . ",false," . $row['QTE_CO'] . "," . $row['OBSERVATIONS'] . "," . $row['CGV_ACCEPTEES'] . "," . $row['COMMENTAIRE_CLIENT'] . "," . $idTypeEnvoiRapport . "," . $row['MOTIF_MODIF_CODE_INT'] . "," . $row['CLI_REFUS_SIGNATURE'] . "," . $row['COMMENTAIRE_REFUS_SIGNATURE'] . "," . $row['FICHE_PAPIER_LIEE'] . "," . $row['ENCAISSEMENT_EFFECTUE'] . "," . $idTypeVisite . "," . $idUserValidationTech . "," . $dateValidationTech . "," . $idTypeValidationTech . "," . $commentaireValidationTech . "," . $idUserValidationAdm . "," . $dateValidationAdm . "," . $idTypeValidationAdm . ",'" . $entretien . "', '" . $site['id'] . "'," . $row['RETOUR'] . "," . $row['NUM_FICHE'] . "),"; $idInterventionAnnulation = "null"; if (isset($row['IDPLN_CLIENT_ABSENT']) && !is_null($row['IDPLN_CLIENT_ABSENT']) && $row['IDPLN_CLIENT_ABSENT'] != 0) { $idAnnulation++; $idInterventionAnnulation = $idAnnulation; $nom = ($site['nom'] == "BRON" ? $site['nom'] : 'TOUS'); $autreMotif = "null"; $idMotifAbsence = "null"; if (!is_null($row['IDPLN_MOTIF_ABSENCE']) && $row['IDPLN_MOTIF_ABSENCE'] != 0) { $idMotifAbsence = $correspondanceMotifAbsence[$row['IDPLN_MOTIF_ABSENCE']][$nom]; if ($idMotifAbsence == '') { $autreMotif = "'" . str_replace("'", "''", $row['LIBELLE_MOTIF_ABSENCE']) . "'"; $idMotifAbsence = "null"; } } else { $clob = $row['AUTRE_MOTIF_ABSENCE']; // champ de type CLOB if (is_object($clob) && method_exists($clob, 'load')) { $contenu = $clob->load(); } else { $contenu = $clob; } $autreMotif = "'" . str_replace("'", "''", $contenu) . "'"; } $row['OBSERVATIONS_ABSENCE'] = (!isset($row['OBSERVATIONS_ABSENCE']) || is_null($row['OBSERVATIONS_ABSENCE'])) ? "null" : "'" . str_replace("'", "''", $row['OBSERVATIONS_ABSENCE']) . "'"; $insertInterventionAnnulation .= "(" . $idInterventionAnnulation . ",'" . $idIntervention . "','1'," . $idMotifAbsence . "," . $autreMotif . "," . $row['OBSERVATIONS_ABSENCE'] . "),"; } else if ($row['CODE_INTERVENTION'] == 'ABANDON' || $row['CODE_INTERVENTION'] == 'RCGV') { $idAnnulation++; $idInterventionAnnulation = $idAnnulation; $idMotifAnnulation = '2'; if ($row['CODE_INTERVENTION'] == 'RCGV') { $idMotifAnnulation = '3'; } $insertInterventionAnnulation .= "(" . $idInterventionAnnulation . ",'" . $idIntervention . "'," . $idMotifAnnulation . ",null,null,null),"; $idTypeVisite = (isset($row['CODE_INTERVENTION_INIT']) && isset($typeVisite[$row['CODE_INTERVENTION_INIT']])) ? "'" . $typeVisite[$row['CODE_INTERVENTION_INIT']] . "'" : "null"; } $idFacture = "null"; $contratId = "null"; $ligneContrat = ''; $ligneProduit = ''; //deplacement & MO if (isset($row['NB_DEPLACEMENT']) && $row['NB_DEPLACEMENT'] > 0) { $produitDeplacement = $tabProduits['DEPL']; $nbDeplacement = (!isset($row['NB_DEPLACEMENT']) || is_null($row['NB_DEPLACEMENT'])) ? "null" : "'" . $row['NB_DEPLACEMENT'] . "'"; $prixDeplacementHT = (!isset($row['PRIX_DEPLACEMENT_HT']) || is_null($row['PRIX_DEPLACEMENT_HT'])) ? "null" : "'" . str_replace(',', '.', $row['PRIX_DEPLACEMENT_HT']) . "'"; $prixDeplacementTTC = (!isset($row['PRIX_DEPLACEMENT']) || is_null($row['PRIX_DEPLACEMENT'])) ? "null" : "'" . str_replace(',', '.', $row['PRIX_DEPLACEMENT']) . "'"; $ligneProduit .= "('-idFacture-','" . $produitDeplacement['id'] . "','" . $produitDeplacement['code'] . "','" . str_replace("'", "''", $produitDeplacement['designation']) . "'," . $nbDeplacement . "," . $prixDeplacementHT . ",'4'," . $prixDeplacementHT . ",'1',null),"; } if (isset($row['NB_MO']) && $row['NB_MO'] > 0) { $produitMo = $tabProduits['MO']; // Valeurs numériques pour le calcul $nbMo = (isset($row['NB_MO']) && is_numeric($row['NB_MO'])) ? (float)$row['NB_MO'] : null; $prixMoTotalHT = (isset($row['PRIX_MO_HT']) && is_numeric(str_replace(',', '.', $row['PRIX_MO_HT']))) ? (float)str_replace(',', '.', $row['PRIX_MO_HT']) : null; $prixMoUnitHt = (!is_null($nbMo) && $nbMo != 0 && !is_null($prixMoTotalHT)) ? $prixMoTotalHT / $nbMo : null; $nbMoSql = is_null($nbMo) ? "NULL" : $nbMo; $prixMoTotalHTSql = is_null($prixMoTotalHT) ? "NULL" : $prixMoTotalHT; $prixMoUnitHtSql = is_null($prixMoUnitHt) ? "NULL" : $prixMoUnitHt; $ligneProduit .= "('-idFacture-','" . $produitMo['id'] . "','" . $produitMo['code'] . "','" . str_replace("'", "''", $produitMo['designation']) . "'," . $nbMo . "," . $prixMoUnitHtSql . ",'4'," . $prixMoTotalHTSql . ",'1',null),"; } //facture si pièce, paiement si montant facture != 0 $row['CODE_TVA'] = (!isset($row['CODE_TVA']) || is_null($row['CODE_TVA'])) ? "null" : "'" . $row['CODE_TVA'] . "'"; //if (isset($row['NUM_FICHE']) && !is_null($row['NUM_FICHE']) && $row['NUM_FICHE'] != '') { $prise_contrat = false; $row['MONTANT_FACTURE_HT'] = (!isset($row['MONTANT_FACTURE_HT']) || is_null($row['MONTANT_FACTURE_HT'])) ? "null" : "'" . str_replace(',', '.', $row['MONTANT_FACTURE_HT']) . "'"; $row['MONTANT_FACTURE'] = (!isset($row['MONTANT_FACTURE']) || is_null($row['MONTANT_FACTURE'])) ? "null" : "'" . str_replace(',', '.', $row['MONTANT_FACTURE']) . "'"; if (isset($tabProduitsOracle[$intNumFiche])) { foreach ($tabProduitsOracle[$intNumFiche] as $cle => $rowProduit) { $rowProduit['PRIX_PIECES_HT'] = (isset($rowProduit['PRIX_PIECES_HT']) ? str_replace(',', '.', $rowProduit['PRIX_PIECES_HT']) : '0'); $rowProduit['PRIX_PIECES_TTC'] = (isset($rowProduit['PRIX_PIECES_TTC']) ? str_replace(',', '.', $rowProduit['PRIX_PIECES_TTC']) : '0'); $idTypeDevise = (!isset($row['DEVISE']) || is_null($row['DEVISE']) ? "null" : ($row['DEVISE'] == 'E' ? "1" : "2")); //null, euro ou franc if ($rowProduit['CODE_ARTICLE'] == 'CONTRAT') { //on cherche la ligne de contrat déjà créée et on modifie la facture associée (catégorie + prise_contrat + prix) et ligne facture s'il y en a d'autre et paiement. if (isset($contrats[$idEquipement]) && isset($contrats[$idEquipement]['01/' . $row['MOIS'] . '/' . $row['ANNEE']])) { $contrat = $contrats[$idEquipement]['01/' . $row['MOIS'] . '/' . $row['ANNEE']]; $contratId = "'" . $contrat['id'] . "'"; $prise_contrat = true; $idFactureContrat = $contrat['idfacture']; $idFacture = $idFactureContrat; $updateFacture = "UPDATE sav.facture SET idfacture_categorie = '1', prise_contrat = true, montant_ht = " . $row['MONTANT_FACTURE_HT'] . ", montant_ttc = " . $row['MONTANT_FACTURE'] . ", idintervention = " . $idIntervention . ", idtype_devise = " . $idTypeDevise . " WHERE id = '" . $idFactureContrat . "'"; $connPostgre->exec($updateFacture); if (isset($facturesContrat[$idFactureContrat])) { $factureLigneContrat = $facturesContrat[$idFactureContrat]; $rowProduit['MOTIF_SERVICE'] = (!isset($rowProduit['MOTIF_SERVICE']) || is_null($rowProduit['MOTIF_SERVICE'])) ? "null" : "'" . str_replace("'", "''", $rowProduit['MOTIF_SERVICE']) . "'"; $ligneContrat .= "('-idFacture-',null,null,'" . str_replace("'", "''", $factureLigneContrat['designation']) . "','1','" . $rowProduit['PRIX_PIECES_HT'] . "'," . $row['CODE_TVA'] . ",'" . $rowProduit['PRIX_PIECES_HT'] . "','" . $produitAffectation[$rowProduit['AFFECTATION']] . "'," . $rowProduit['MOTIF_SERVICE'] . "),"; } } } else { if (isset($tabProduits[$rowProduit['CODE_ARTICLE']])) { $produit = $tabProduits[$rowProduit['CODE_ARTICLE']]; $puHt = $rowProduit['PRIX_PIECES_HT']; $prixTotalHt = $rowProduit['PRIX_PIECES_HT'] * $rowProduit['QUANTITE']; $rowProduit['MOTIF_SERVICE'] = (!isset($rowProduit['MOTIF_SERVICE']) || is_null($rowProduit['MOTIF_SERVICE'])) ? "null" : "'" . str_replace("'", "''", $rowProduit['MOTIF_SERVICE']) . "'"; $aff = (isset($rowProduit['AFFECTATION']) && !is_null($rowProduit['AFFECTATION']) && isset($produitAffectation[$rowProduit['AFFECTATION']])) ? "'" . $produitAffectation[$rowProduit['AFFECTATION']] . "'" : "null" ; $rowProduit['QUANTITE'] = (isset($rowProduit['QUANTITE']) && !is_null($rowProduit['QUANTITE'])) ? $rowProduit['QUANTITE'] : 1; $ligneProduit .= "('-idFacture-','" . $produit['id'] . "','" . $produit['code'] . "','" . str_replace("'", "''", $produit['designation']) . "','" . $rowProduit['QUANTITE'] . "','" . $puHt . "'," . $row['CODE_TVA'] . ",'" . $prixTotalHt . "'," . $aff . "," . $rowProduit['MOTIF_SERVICE'] . "),"; } } } } $idFactureAcompte = "null"; $row['DEVIS'] = (!isset($row['DEVIS']) || is_null($row['DEVIS'])) ? "null" : "'" . $row['DEVIS'] . "'"; $coef = 0; // Calcul du taux de TVA pour retrouver les montant HT de l'acompte et du restant de la facture if ((isset($row['MONTANT_THEORIQUE']) && $row['MONTANT_THEORIQUE'] > 0) && (isset($row['MONTANT_THEORIQUE_HT']) && $row['MONTANT_THEORIQUE_HT'] > 0)) { $row['MONTANT_THEORIQUE'] = str_replace(',', '.', $row['MONTANT_THEORIQUE']); $row['MONTANT_THEORIQUE'] = floatval($row['MONTANT_THEORIQUE']); $row['MONTANT_THEORIQUE_HT'] = str_replace(',', '.', $row['MONTANT_THEORIQUE_HT']); $row['MONTANT_THEORIQUE_HT'] = floatval($row['MONTANT_THEORIQUE_HT']); $tauxTVA = ($row['MONTANT_THEORIQUE'] - $row['MONTANT_THEORIQUE_HT']) / $row['MONTANT_THEORIQUE_HT']; // Coefficient pour passer du TTC au HT $coef = 1 / (1 + $tauxTVA); } $idMoyenPaiement = (isset($row['MODE_PAIEMENT']) && isset($moyenPaiement[$row['MODE_PAIEMENT']])) ? "'" . $moyenPaiement[$row['MODE_PAIEMENT']] . "'" : "null"; if ($idMoyenPaiement == "F") { $sql = "SELECT * FROM FACTURE WHERE NUM_FICHE='" . $intNumFiche . "'"; $selectFactures = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql); $nbResultFactures = oci_fetch_all($selectFactures, $factures, 0, -1, OCI_ASSOC + OCI_FETCHSTATEMENT_BY_ROW); foreach ($factures as $cle => $rowFac) { $numFactureOracle = (string)$rowFac['NUM_FACTURE']; if ($numFactureOracle[3] == '2') { //acompte $row['FAC_ACOMPTE'] = $rowFac; } else if ($numFactureOracle[3] == '3') { //solde $row['FAC_SOLDE'] = $rowFac; } } oci_free_statement($selectDocuments); } //facture d'acompte if (isset($row['MONTANT_ACOMPTE']) && $row['MONTANT_ACOMPTE'] > 0) { $idFactureCurrent++; $idFactureAcompte = $idFactureCurrent; if (!isset($sequenceur[$site['id']])) { $sequenceur[$site['id']] = []; } if (isset($sequenceur[$site['id']][$row['ANNEE_FACTURE']])) { $sequenceur[$site['id']][$row['ANNEE_FACTURE']] += 1; } else { $sequenceur[$site['id']][$row['ANNEE_FACTURE']] = 1; } $numFacture = $row['ANNEE_FACTURE'] . '03' . substr('00' . $site['id'], -2) . substr('000000' . $sequenceur[$site['id']][$row['ANNEE_FACTURE']], -5); $row['MONTANT_ACOMPTE'] = str_replace(',', '.', $row['MONTANT_ACOMPTE']); $acompteHT = $row['MONTANT_ACOMPTE']; $datePaiement = $row['DATE_INTERVENTION']; if (isset($row['FAC_ACOMPTE'])) { $acompteHT = $row['FAC_ACOMPTE']['MONTANT_HT']; $idMoyenPaiement = (isset($row['FAC_ACOMPTE']['MODE_PAIEMENT']) && isset($moyenPaiement[$row['FAC_ACOMPTE']['MODE_PAIEMENT']])) ? "'" . $moyenPaiement[$row['FAC_ACOMPTE']['MODE_PAIEMENT']] . "'" : "null"; $datePaiement = $row['FAC_ACOMPTE']['DATE_PAIEMENT']; } else if ($coef > 0) { $acompteHT = round($row['MONTANT_ACOMPTE'] * $coef, 2); } $insertFacture .= "('" . $idFactureAcompte . "','" . $numFacture . "'," . $idEquipement . "," . $idAdresse . ",'1','1'," . $acompteHT . "," . $row['MONTANT_ACOMPTE'] . ",'" . $row['DATE_INTERVENTION'] . "','" . $site['id'] . "'," . $idIntervention . "," . $idTypeDevise . ",null," . $row['DEVIS'] . "),"; $insertPaiement .= "('" . $idFactureAcompte . "'," . $idMoyenPaiement . ", " . $row['MONTANT_ACOMPTE'] . ",'" . $datePaiement . "'),"; $insertFactureLigne .= str_replace('-idFacture-', $idFactureAcompte, $ligneContrat . $ligneProduit); } if (isset($row['MONTANT_FACTURE']) || $ligneProduit != '') { //facture de solde if (!$prise_contrat) { $idFactureCurrent++; $idFacture = $idFactureCurrent; if (!isset($sequenceur[$site['id']])) { $sequenceur[$site['id']] = []; } if (isset($sequenceur[$site['id']][$row['ANNEE_FACTURE']])) { $sequenceur[$site['id']][$row['ANNEE_FACTURE']] += 1; } else { $sequenceur[$site['id']][$row['ANNEE_FACTURE']] = 1; } $factureHT = $row['MONTANT_FACTURE']; $datePaiement = $row['DATE_INTERVENTION']; if (isset($row['FAC_SOLDE'])) { $factureHT = $row['FAC_SOLDE']['MONTANT_HT']; $idMoyenPaiement = (isset($row['FAC_SOLDE']['MODE_PAIEMENT']) && isset($moyenPaiement[$row['FAC_SOLDE']['MODE_PAIEMENT']])) ? "'" . $moyenPaiement[$row['FAC_SOLDE']['MODE_PAIEMENT']] . "'" : "null"; $datePaiement = $row['FAC_SOLDE']['DATE_PAIEMENT']; } else if ($row['MONTANT_FACTURE'] > 0 && $coef > 0) { $factureHT = round($row['MONTANT_FACTURE'] * $coef, 2); } $numFacture = $row['ANNEE_FACTURE'] . '03' . substr('00' . $site['id'], -2) . substr('000000' . $sequenceur[$site['id']][$row['ANNEE_FACTURE']], -5); $idTypeDevise = (!isset($row['DEVISE']) || is_null($row['DEVISE']) ? "null" : ($row['DEVISE'] == 'E' ? "1" : "2")); //null, euro ou franc $insertFacture .= "('" . $idFacture . "','" . $numFacture . "'," . $idEquipement . "," . $idAdresse . ",'2','1'," . $factureHT . "," . $row['MONTANT_FACTURE'] . ",'" . $row['DATE_INTERVENTION'] . "','" . $site['id'] . "'," . $idIntervention . "," . $idTypeDevise . "," . $idFactureAcompte . "," . $row['DEVIS'] . "),"; $insertPaiement .= "('" . $idFacture . "'," . $idMoyenPaiement . ", " . $row['MONTANT_FACTURE'] . ",'" . $datePaiement . "'),"; } $insertFactureLigne .= str_replace('-idFacture-', $idFacture, $ligneProduit); } //} //} } if (isset($row['IDPLN_PLANNING'])) { $insertTmpPlanning .= "('" . $site['id'] . "','" . $idPlanning . "', '" . $row['IDPLN_PLANNING'] . "'),"; } if ($count == 100000) { if (substr($insertPlanning, -1) == ',') { $insertPlanning = substr($insertPlanning, 0, -1); $connPostgre->exec($insertPlanning); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_planning.log', $insertPlanning . ';', FILE_APPEND); $insertTmpPlanning = substr($insertTmpPlanning, 0, -1); $connPostgre->exec($insertTmpPlanning); } if (substr($insertEvenement, -1) == ',') { $insertEvenement = substr($insertEvenement, 0, -1); $connPostgre->exec($insertEvenement); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_evenement.log', $insertEvenement . ';', FILE_APPEND); } if (substr($insertIntervention, -1) == ',') { $insertIntervention = substr($insertIntervention, 0, -1); $connPostgre->exec($insertIntervention); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention.log', $insertIntervention . ';', FILE_APPEND); } if (substr($insertInterventionAnnulation, -1) == ',') { $insertInterventionAnnulation = substr($insertInterventionAnnulation, 0, -1); $connPostgre->exec($insertInterventionAnnulation); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_annulation.log', $insertInterventionAnnulation . ';', FILE_APPEND); } if (substr($insertInterventionPreEncaissement, -1) == ',') { $insertInterventionPreEncaissement = substr($insertInterventionPreEncaissement, 0, -1); $connPostgre->exec($insertInterventionPreEncaissement); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_pre_encaissement.log', $insertInterventionPreEncaissement . ';', FILE_APPEND); } if (substr($insertInterventionDocuments, -1) == ',') { $insertInterventionDocuments = substr($insertInterventionDocuments, 0, -1); $connPostgre->exec($insertInterventionDocuments); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_document.log', $insertInterventionDocuments . ';', FILE_APPEND); } if (substr($insertInterventionAnomalie, -1) == ',') { $insertInterventionAnomalie = substr($insertInterventionAnomalie, 0, -1); $connPostgre->exec($insertInterventionAnomalie); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_anomalie.log', $insertInterventionAnomalie . ';', FILE_APPEND); } if (substr($insertFacture, -1) == ',') { $insertFacture = substr($insertFacture, 0, -1); $connPostgre->exec($insertFacture); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_facture.log', $insertFacture . ';', FILE_APPEND); } if (substr($insertFactureLigne, -1) == ',') { $insertFactureLigne = substr($insertFactureLigne, 0, -1); $connPostgre->exec($insertFactureLigne); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_facture_ligne.log', $insertFactureLigne . ';', FILE_APPEND); } if (substr($insertPaiement, -1) == ',') { $insertPaiement = substr($insertPaiement, 0, -1); $connPostgre->exec($insertPaiement); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_paiement.log', $insertPaiement . ';', FILE_APPEND); } if (substr($insertTmp, -1) == ',') { $insertTmp = substr($insertTmp, 0, -1); $connPostgre->exec($insertTmp); } $connPostgre->commit(); if (!$connPostgre->inTransaction()) { $connPostgre->beginTransaction(); } $count = 0; $insertPlanning = "INSERT INTO sav.planning(id, idtype_planification_init, idutilisateur_agent, idutilisateur_planning, idequipement, date_creation, date, statut, heure_debut, heure_fin, rang_tri, idtype_duree, duree, idmoment_passage, idhoraire_passage, horaire_specifie, heure_debut_specifiee, heure_fin_specifiee, code_regroup, idsite) VALUES "; $insertEvenement = "INSERT INTO sav.evenement(idplanning, idtype_planification, idagence, idevenement_lieu, idevenement_objet, idformation, est_formateur, objet_si_autre, duree) VALUES "; $insertIntervention = "INSERT INTO sav.intervention(id, idplanning, idequipement, avis_passage, avis_passage_mail, memo, temps, idintervention_motif, horaire_a_controler, mesure_co, observations, cgv_acceptees, commentaire_client, idtype_envoi_rapport, motif_modif_code_int, cli_refus_signature, commentaire_refus_signature, fiche_papier_liee, encaissement_effectue, idtype_planification, idutilisateur_validation_technique, date_validation_technique, idtype_validation_technique, commentaire_technique, idutilisateur_validation_adm, date_validation_adm, idtype_validation_adm, entretien, idsite, idretour, numfiche_oracle) VALUES "; $insertInterventionAnnulation = "INSERT INTO sav.intervention_annulation(id, idintervention, idintervention_motif_annulation, idintervention_motif_absence, autre_motif, observations) VALUES "; $insertInterventionAnomalie = "INSERT INTO sav.intervention_anomalie(id, idintervention, idanomalie_oracle) VALUES "; $insertInterventionDocuments = "INSERT INTO sav.intervention_document(idintervention, nom, exclusif_courrier, chemin) VALUES "; $insertInterventionPreEncaissement = "INSERT INTO sav.intervention_pre_encaissement(idintervention, idmoyen_paiement, montant_ttc, idutilisateur_reception, date_reception, idutilisateur_responsable) VALUES"; $insertTmp = "INSERT INTO sav.tmp_correspondance_intervention_planning(idintervention, idpln_planning, idsite) VALUES "; $insertFacture = "INSERT INTO sav.facture(id, num_facture, idequipement, idadresse, idfacture_type, idfacture_categorie, montant_ht, montant_ttc, date_facture, idsite, idintervention, idtype_devise, idfacture_acompte, num_devis_papier) VALUES "; $insertFactureLigne = "INSERT INTO sav.facture_ligne(idfacture, idproduit, code_article, designation, quantite, pu_ht, idtaux_tva, prix_total_ht, idproduit_facture_affectation, motif_service) VALUES "; $insertPaiement = "INSERT INTO sav.paiement(idfacture, idmoyen_paiement, montant_ttc, date) VALUES "; $insertTmpPlanning = "INSERT INTO sav.tmp_correspondance_planning(idsite, idplanning, idplanning_oracle) VALUES "; } } oci_free_statement($stid); if (substr($insertPlanning, -1) == ',') { $insertPlanning = substr($insertPlanning, 0, -1); $connPostgre->exec($insertPlanning); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_planning.log', $insertPlanning . ';', FILE_APPEND); $insertTmpPlanning = substr($insertTmpPlanning, 0, -1); $connPostgre->exec($insertTmpPlanning); } if (substr($insertEvenement, -1) == ',') { $insertEvenement = substr($insertEvenement, 0, -1); $connPostgre->exec($insertEvenement); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_evenement.log', $insertEvenement . ';', FILE_APPEND); } if (substr($insertIntervention, -1) == ',') { $insertIntervention = substr($insertIntervention, 0, -1); $connPostgre->exec($insertIntervention); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention.log', $insertIntervention . ';', FILE_APPEND); } if (substr($insertInterventionAnnulation, -1) == ',') { $insertInterventionAnnulation = substr($insertInterventionAnnulation, 0, -1); $connPostgre->exec($insertInterventionAnnulation); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_annulation.log', $insertInterventionAnnulation . ';', FILE_APPEND); } if (substr($insertInterventionPreEncaissement, -1) == ',') { $insertInterventionPreEncaissement = substr($insertInterventionPreEncaissement, 0, -1); $connPostgre->exec($insertInterventionPreEncaissement); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_pre_encaissement.log', $insertInterventionPreEncaissement . ';', FILE_APPEND); } if (substr($insertInterventionDocuments, -1) == ',') { $insertInterventionDocuments = substr($insertInterventionDocuments, 0, -1); $connPostgre->exec($insertInterventionDocuments); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_document.log', $insertInterventionDocuments . ';', FILE_APPEND); } if (substr($insertInterventionAnomalie, -1) == ',') { $insertInterventionAnomalie = substr($insertInterventionAnomalie, 0, -1); $connPostgre->exec($insertInterventionAnomalie); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_intervention_anomalie.log', $insertInterventionAnomalie . ';', FILE_APPEND); } if (substr($insertFacture, -1) == ',') { $insertFacture = substr($insertFacture, 0, -1); $connPostgre->exec($insertFacture); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_facture.log', $insertFacture . ';', FILE_APPEND); } if (substr($insertFactureLigne, -1) == ',') { $insertFactureLigne = substr($insertFactureLigne, 0, -1); $connPostgre->exec($insertFactureLigne); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_facture_ligne.log', $insertFactureLigne . ';', FILE_APPEND); } if (substr($insertPaiement, -1) == ',') { $insertPaiement = substr($insertPaiement, 0, -1); $connPostgre->exec($insertPaiement); //file_put_contents('log/log_' . date("j.n.Y") . '_' . $site['nom'] . '_paiement.log', $insertPaiement . ';', FILE_APPEND); } if (substr($insertTmp, -1) == ',') { $insertTmp = substr($insertTmp, 0, -1); $connPostgre->exec($insertTmp); } $connPostgre->commit(); } if (!$connPostgre->inTransaction()) { $connPostgre->beginTransaction(); } $idPlanning++; $alter = "ALTER SEQUENCE sav.planning_id_seq RESTART WITH " . ($idPlanning) . ";"; $connPostgre->exec($alter); //if($idInterventionAnnulation != null) { $idAnnulation++; $alter = "ALTER SEQUENCE sav.intervention_annulation_id_seq RESTART WITH " . ($idAnnulation) . ";"; $connPostgre->exec($alter); //} $idIntervention++; $alter = "ALTER SEQUENCE sav.intervention_id_seq RESTART WITH " . ($idIntervention) . ";"; $connPostgre->exec($alter); $idFactureCurrent++; $alter = "ALTER SEQUENCE sav.facture_id_seq RESTART WITH " . ($idFactureCurrent) . ";"; $connPostgre->exec($alter); $connPostgre->commit();