DECLARE @Date1 DATE
DECLARE @Date2 DATE

SET @Date1 = CONVERT(DATE, '24/01/1901')
SET @Date2 = CONVERT(DATE, '24/04/2013')

SELECT DISTINCT RO.num_ins AS NumInscrit
	,P.nompers AS Nom
	,Lower(P.prnmpers) AS Prenom
	,Lower(CASE 
			WHEN C.ind_statut_ctrat = - 1
				THEN ' EN PRÉPA'
			WHEN C.ind_statut_ctrat = 0
				THEN ' A ENVOYER'
			WHEN C.ind_statut_ctrat = 1
				THEN ' ENVOYÉ'
			WHEN C.ind_statut_ctrat = 2
				THEN ' REÇU'
			WHEN C.ind_statut_ctrat = 3
				THEN ' A RETRAITER'
			WHEN C.ind_statut_ctrat = 4
				THEN ' A VISER'
			WHEN C.ind_statut_ctrat = 5
				THEN ' VALIDE'
			WHEN C.ind_statut_ctrat = 6
				THEN ' ANNULÉ'
			WHEN C.ind_statut_ctrat = 7
				THEN CASE 
						WHEN C.id_cause_solde_ctrat = 1
							THEN ' SOLDÉ '
						ELSE ' RÉSILIÉ '
						END
			ELSE ''
			END) AS StatutContrat
	,Isnull((
			SELECT TOP 1 SF2.n_statut
			FROM statut_fin SF2
			WHERE SF2.num_ins = RO.num_ins
				AND n_statut NOT IN (
					'BL'
					,'DB'
					)
			ORDER BY SF2.dtrel_statut DESC
			), 'AJ') AS StatutFinancier
	,Isnull((
			SELECT Sum(sssold) AS SOLDEADMIN
			FROM (
				SELECT Sum(CASE ind_credit_lcpt
							WHEN 0
								THEN - 1 * montant_ttc_lcpt
							ELSE montant_ttc_lcpt
							END) AS SSSOLD
				FROM ligne_comptable
				WHERE Isnull(ind_annule_lcpt, 0) <> 1
					AND Isnull(ind_comptable_lcpt, 0) = 1
					AND Datediff(d, Isnull(reg_date_encaissement_lcpt, date_creat_lcpt), Getdate()) >= 0
					AND (
						id_nat_don_cpt = 2
						OR id_nat_don_cpt = 5
						OR id_nat_don_cpt = 7
						OR id_nat_don_cpt = 4
						)
					AND id_ctrat = C.id_ctrat
				
				UNION
				
				SELECT - 1 * Sum(montant_ttc_ech) AS SSSOLD
				FROM echeance
					,echeancier
				WHERE echeancier.id_echier = echeance.id_echier
					AND echeancier.etat_echier = 1
					AND echeancier.id_ctrat = C.id_ctrat
					AND Datediff(d, echeance.date_ech, Getdate()) >= 0
				) AS LIGNES_ADMIN
			), 0) AS SoldeCourantNet
	,Isnull((
			SELECT Sum(CASE ind_credit_lcpt
						WHEN 0
							THEN - 1 * montant_ttc_lcpt
						ELSE montant_ttc_lcpt
						END) AS SOLDECPTABLE
			FROM ligne_comptable
			WHERE Isnull(ind_annule_lcpt, 0) <> 1
				AND Isnull(ind_comptable_lcpt, 0) = 1
				AND Datediff(d, Isnull(reg_date_encaissement_lcpt, date_creat_lcpt), Getdate()) >= 0
				AND id_ctrat = C.id_ctrat
			), 0) AS SoldeComptable
	,isnull((
			SELECT ISNULL(SUM(TOTALRECOUVERTSTATUT.SOLDERECOUVERT), 0)
			FROM (
				SELECT SF1.num_ins
					,CASE SF1.a_statut
						WHEN 'AJ'
							THEN 0
						ELSE (
								SELECT Isnull(Sum(CASE 
												WHEN Isnull(LC.ind_credit_lcpt, 0) = 1
													THEN LC.montant_ttc_lcpt
												ELSE NULL
												END), 0) - Isnull(Sum(CASE 
												WHEN Isnull(LC.ind_credit_lcpt, 0) = 0
													THEN LC.montant_ttc_lcpt
												ELSE NULL
												END), 0) AS SOLDERECOUVERT
								FROM contrat
								LEFT JOIN ligne_comptable AS LC ON contrat.id_ctrat = LC.id_ctrat
								LEFT JOIN type_reglement ON LC.id_typregl = type_reglement.id_typregl
								LEFT JOIN nature_donnee_comptable ON LC.id_nat_don_cpt = nature_donnee_comptable.id_nat_don_cpt
								LEFT JOIN produit ON LC.code_prod = produit.code_prod
								LEFT JOIN ligne_comptable LCM ON LCM.id_lcpt = LC.lig_id_lcpt
								LEFT JOIN payeur ON LCM.id_pay = payeur.id_pay
								LEFT JOIN financeur ON financeur.numpers_fin = payeur.numpers
								LEFT JOIN type_reglement TRR ON LCM.id_typregl = TRR.id_typregl
								WHERE LC.ind_comptable_lcpt = 1
									AND LC.id_typregl IS NOT NULL
									AND Datediff(d, Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt), Getdate()) >= 0
									AND contrat.id_ctrat = C.id_ctrat
									AND CASE 
										WHEN LC.id_nat_don_cpt = 2
											THEN Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt)
										WHEN LC.id_nat_don_cpt = 5
											THEN Isnull(LC.imp_date_impaye_lcpt, LC.date_creat_lcpt)
										ELSE LC.date_creat_lcpt
										END BETWEEN Isnull((
													SELECT TOP 1 CONVERT(DATE, SF2.dtrel_statut)
													FROM statut_fin SF2
													WHERE Datediff(d, SF2.dtrel_statut, SF1.dtrel_statut) > 0
														AND SF2.dtrel_statut BETWEEN @Date1
															AND @Date2
														AND SF2.num_ins = SF1.num_ins
														AND SF2.n_statut NOT IN (
															'BL'
															,'DB'
															)
													ORDER BY dtrel_statut DESC
													), @Date1)
										AND SF1.dtrel_statut
								)
						END AS SOLDERECOUVERT
				FROM statut_fin SF1
				INNER JOIN contrat C ON C.num_ins = SF1.num_ins
				WHERE SF1.n_statut NOT IN (
						'BL'
						,'DB'
						)
					AND SF1.dtrel_statut BETWEEN @Date1
						AND @Date2
				) TOTALRECOUVERTSTATUT
			WHERE TOTALRECOUVERTSTATUT.num_ins = RO.num_ins
			GROUP BY num_ins
			), 0) + CASE ISNULL((
				SELECT TOP 1 SF.n_statut
				FROM statut_fin SF
				WHERE SF.num_ins = RO.num_ins
					AND SF.n_statut NOT IN (
						'BL'
						,'DB'
						)
					AND Datediff(d, CONVERT(DATE, SF.dtrel_statut), @Date2) >= 0
				ORDER BY SF.dtrel_statut DESC
				), 'AJ')
		WHEN 'AJ'
			THEN 0
		ELSE (
				SELECT Isnull(Sum(CASE 
								WHEN Isnull(LC.ind_credit_lcpt, 0) = 1
									THEN LC.montant_ttc_lcpt
								ELSE NULL
								END), 0) - Isnull(SUM(CASE 
								WHEN Isnull(LC.ind_credit_lcpt, 0) = 0
									THEN LC.montant_ttc_lcpt
								ELSE NULL
								END), 0) AS SOLDERECOUVERT
				FROM contrat
				LEFT JOIN ligne_comptable AS LC ON contrat.id_ctrat = LC.id_ctrat
				LEFT JOIN type_reglement ON LC.id_typregl = type_reglement.id_typregl
				LEFT JOIN nature_donnee_comptable ON LC.id_nat_don_cpt = nature_donnee_comptable.id_nat_don_cpt
				LEFT JOIN produit ON LC.code_prod = produit.code_prod
				LEFT JOIN ligne_comptable LCM ON LCM.id_lcpt = LC.lig_id_lcpt
				LEFT JOIN payeur ON LCM.id_pay = payeur.id_pay
				LEFT JOIN financeur ON financeur.numpers_fin = payeur.numpers
				LEFT JOIN type_reglement TRR ON LCM.id_typregl = TRR.id_typregl
				WHERE LC.ind_comptable_lcpt = 1
					AND LC.id_typregl IS NOT NULL
					AND Datediff(d, Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt), Getdate()) >= 0
					AND contrat.id_ctrat = (
						SELECT id_ctrat
						FROM contrat C
						WHERE C.num_ins = RO.num_ins
						)
					AND CONVERT(DATE, CASE 
							WHEN LC.id_nat_don_cpt = 2
								THEN Isnull(LC.reg_date_encaissement_lcpt, LC.date_creat_lcpt)
							WHEN LC.id_nat_don_cpt = 5
								THEN Isnull(LC.imp_date_impaye_lcpt, LC.date_creat_lcpt)
							ELSE LC.date_creat_lcpt
							END, 103) BETWEEN Isnull((
									SELECT TOP 1 CONVERT(DATE, SF.dtrel_statut)
									FROM statut_fin SF
									WHERE SF.num_ins = RO.num_ins
										AND SF.n_statut NOT IN (
											'BL'
											,'DB'
											)
										AND Datediff(d, CONVERT(DATE, SF.dtrel_statut), @Date2) >= 0
										AND CONVERT(DATE, SF.dtrel_statut) BETWEEN @Date1
											AND @Date2
									ORDER BY SF.dtrel_statut DESC
									), @Date1)
						AND @Date2
				)
		END AS SoldeRecouvert
FROM rel_oprec RO
INNER JOIN inscrit I ON RO.num_ins = I.num_ins
INNER JOIN personne P ON P.numpers = I.numpers
INNER JOIN contrat C ON C.num_ins = RO.num_ins
WHERE RO.ind_act = 1
ORDER BY RO.num_ins DESC;