1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276
|
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
packagedao;
import jpa2.Anneecomptable;
import myEntities.MyListecaisseGenereVentillees;
import myEntities.MyMouvementsCaisse;
import myEntities.MyReglement;
import myEntities.MyVentillationCaisse;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
/**
*
* @author IPPCDSI
*/
@Stateless
public class FlashActiviteSession {
@PersistenceContext(unitName = "iplansERPw-ejbPU2")
public EntityManager em2;
public Calendar debutCumul = Calendar.getInstance();
public Anneecomptable verifAnneeComptable(String site) throws Exception{
String req = "SELECT * FROM anneecomptable WHERE etat=1 AND Site='"+site+"'";
Query q = em2.createNativeQuery(req, com.iplans.jpa2.Anneecomptable.class);
Anneecomptable result = (Anneecomptable) q.getSingleResult();
return result;
}
double fondVeille = 0.0;
double soldeVeille = 0.0;
double ventesComptant = 0.0;
double fAVentesComptantCumul = 0.0;
double fARecouvrement = 0.0;
double fARecouvrementCumul = 0.0;
double fADeposit = 0.0;
double fADepositCumul = 0.0;
double fADepenses = 0.0;
double fADepensesCumul = 0.0;
double fAVersementBanque = 0.0;
double fAVersementBanqueCumul = 0.0;
double fARetraitBanque = 0.0;
double fARetraitBanqueCumul = 0.0;
public List<String> listerModeReg(String site) {
String reqModePaiement = "SELECT DISTINCT Initiale FROM modereglement WHERE NEng<>0 ";
if (site != "TOUS") {
reqModePaiement += "AND modereglement.Site='" + site + "'";
}
List<String> listeModePaiement = new ArrayList<>();
Query q = em2.createNativeQuery(reqModePaiement);
listeModePaiement = q.getResultList();
return listeModePaiement;
}
public double calculFondveille(String mode, int d1)throws Exception{
String reqFondVeille = "SELECT * FROM listecaissesgenereesventillees WHERE IDDateCaisse=? AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFondVeille, com.iplans.myEntities.MyListecaisseGenereVentillees.class).setParameter(1, d1);
List<MyListecaisseGenereVentillees> resultat4 = q4.getResultList();
double fAFondVeille =0.0;
for(int i=0; i<resultat4.size(); i++){
fAFondVeille =fAFondVeille + resultat4.get(i).getFondVeille();
}
return fAFondVeille;
}
public double calculFondSolde(String mode, int d2){
String reqSolde= "SELECT * FROM listecaissesgenereesventillees WHERE IDDateCaisse="+d2+" AND ModeReglement='"+mode+"' ";
Query q4 = em2.createNativeQuery(reqSolde, com.iplans.myEntities.MyListecaisseGenereVentillees.class);
List<MyListecaisseGenereVentillees> resultat4 = q4.getResultList();
double fAFondSolde =0.0;
for(int i=0; i<resultat4.size(); i++){
fAFondSolde =fAFondSolde + resultat4.get(i).getSolde();
}
return fAFondSolde;
}
public double calculVentesComptant(String mode, int d1, int d2){
//mode = "ESP";
String reqFAVentesComptant = "SELECT reglements.* FROM reglements, facturestpv WHERE EnCours=0 AND facturestpv.Indexe=reglements.IndexFacture AND ModeReglement<>'Debiteur' AND (IndexDebiteur='0' OR IndexDebiteur='') AND ModeReglement<>'Creancier' AND reglements.IDJour>="+d1+" AND reglements.IDJour<="+d2+" AND reglements.TPV<>'Back Office' AND ModeReglement='"+mode+"' ";
Query q4 = em2.createNativeQuery(reqFAVentesComptant, com.iplans.myEntities.MyReglement.class);
List<MyReglement> resultat4 = q4.getResultList();
double fAVentesComptant =0.0;
for(int i=0; i<resultat4.size(); i++){
// System.out.println("dedans ");
fAVentesComptant =fAVentesComptant + resultat4.get(i).getMontant();
}
//System.out.println("ici vente comptant "+fAVentesComptant);
return fAVentesComptant;
}
public double calculVentesComptantCumul(String mode, int d3, int d2){
String reqFAVentesComptantCumul = "SELECT reglements.* FROM reglements, facturestpv WHERE EnCours=0 AND facturestpv.Indexe=reglements.IndexFacture AND ModeReglement<>'Debiteur' AND (IndexDebiteur='0' OR IndexDebiteur='') AND ModeReglement<>'Creancier' AND reglements.IDJour>="+d3+" AND reglements.IDJour<="+d2+" AND reglements.TPV<>'Back Office' AND ModeReglement='"+mode+"' ";
Query q4 = em2.createNativeQuery(reqFAVentesComptantCumul, com.iplans.myEntities.MyReglement.class);
List<MyReglement> resultat4 = q4.getResultList();
double faVentesComptantCumul =0.0;
for(int i=0; i<resultat4.size(); i++){
faVentesComptantCumul = faVentesComptantCumul + resultat4.get(i).getMontant();
}
return faVentesComptantCumul;
}
public double calculRecouvrement(String mode, int d1, int d2){
String reqFARecouvrement = "SELECT reglements.* FROM reglements, facturestpv WHERE EnCours=0 AND facturestpv.Indexe=reglements.IndexFacture AND ModeReglement<>'Debiteur' AND ModeReglement<>'Creancier' AND reglements.IDJour>=" + d1 + " AND reglements.IDJour<=" + d2 + " AND Regle=1 AND SourceReglement<>'0' AND IndexDebiteur<>'0' AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFARecouvrement, com.iplans.myEntities.MyReglement.class);
List<MyReglement> resultat4 = q4.getResultList();
double faRecouvrement =0.0;
for(int i=0; i<resultat4.size(); i++){
faRecouvrement = faRecouvrement + resultat4.get(i).getMontant();
}
return faRecouvrement;
}
public double calculRecouvrementCumul(String mode, int d3, int d2){
String reqFARecouvrementCumul = "SELECT reglements.* FROM reglements, facturestpv WHERE EnCours=0 AND facturestpv.Indexe=reglements.IndexFacture AND ModeReglement<>'Debiteur' AND ModeReglement<>'Creancier' AND reglements.IDJour>=" + d3 + " AND reglements.IDJour<=" + d2 + " AND Regle=1 AND SourceReglement<>'0' AND IndexDebiteur<>'0' AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFARecouvrementCumul, com.iplans.myEntities.MyReglement.class);
List<MyReglement> resultat4 = q4.getResultList();
double faRecouvrementCumul =0.0;
for(int i=0; i<resultat4.size(); i++){
faRecouvrementCumul = faRecouvrementCumul + resultat4.get(i).getMontant();
}
return faRecouvrementCumul;
}
public double calculDeposit(String mode, int d1, int d2){
String reqFADeposit = "SELECT reglements.* FROM reglements, facturestpv WHERE EnCours=0 AND facturestpv.Indexe=reglements.IndexFacture AND ModeReglement<>'Debiteur' AND ModeReglement<>'Creancier' AND reglements.IDJour>=" + d1 + " AND reglements.IDJour<=" + d2 + " AND IndexCreancier<>'0' AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFADeposit, com.iplans.myEntities.MyReglement.class);
List<MyReglement> resultat4 = q4.getResultList();
double faDeposit =0.0;
for(int i=0; i<resultat4.size(); i++){
faDeposit = faDeposit + resultat4.get(i).getMontant();
}
return faDeposit;
}
public double calculDepositCumul(String mode, int d3, int d2){
String reqFADepositCumul = "SELECT reglements.* FROM reglements, facturestpv WHERE EnCours=0 AND facturestpv.Indexe=reglements.IndexFacture AND ModeReglement<>'Debiteur' AND ModeReglement<>'Creancier' AND reglements.IDJour>=" + d3 + " AND reglements.IDJour<=" + d2 + " AND IndexCreancier<>'0' AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFADepositCumul, com.iplans.myEntities.MyReglement.class);
List<MyReglement> resultat4 = q4.getResultList();
double faDepositCumul =0.0;
for(int i=0; i<resultat4.size(); i++){
faDepositCumul = faDepositCumul + resultat4.get(i).getMontant();
}
return faDepositCumul;
}
public double calculDepenses(String mode, int d1, int d2){
String reqFADepenses = "SELECT * FROM mouvementcaisseligne WHERE IndexLiaison='' AND (IndexBanque='0' OR IndexBanque='') AND IDJour>="+d1+" AND IDJour<="+d2+" AND ModeReglement='"+mode+"' ";
Query q4 = em2.createNativeQuery(reqFADepenses, com.iplans.myEntities.MyMouvementsCaisse.class);
List<MyMouvementsCaisse> resultat4 = q4.getResultList();
double faDepenses =0.0;
for(int i=0; i<resultat4.size(); i++){
faDepenses = faDepenses + resultat4.get(i).getSortie();
}
return faDepenses;
}
public double calculDepensesCumul (String mode, int d3, int d2){
String reqFADepensesCumul = "SELECT * FROM mouvementcaisseligne WHERE IndexLiaison='' AND (IndexBanque='0' OR IndexBanque='') AND IDJour>="+d3+" AND IDJour<="+d2+" AND ModeReglement='"+mode+"' ";
Query q4 = em2.createNativeQuery(reqFADepensesCumul, com.iplans.myEntities.MyMouvementsCaisse.class);
List<MyMouvementsCaisse> resultat4 = q4.getResultList();
double faDepensesCumul =0.0;
for(int i=0; i<resultat4.size(); i++){
faDepensesCumul = faDepensesCumul + resultat4.get(i).getSortie();
}
return faDepensesCumul;
}
public double calculVersementBanque(String mode, int d1, int d2){
String reqFAVersementBanque = "SELECT * FROM mouvementcaisseligne WHERE Entree=0 AND IndexLiaison='' AND (IndexBanque<>'0' AND IndexBanque<>'') AND IDJour>=" + d1 + " AND IDJour<=" + d2 + " AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFAVersementBanque, com.iplans.myEntities.MyMouvementsCaisse.class);
List<MyMouvementsCaisse> resultat4 = q4.getResultList();
double faVersementBanque =0.0;
for(int i=0; i<resultat4.size(); i++){
faVersementBanque = faVersementBanque + resultat4.get(i).getSortie();
}
return faVersementBanque;
}
public double calculVersementBanqueCumul (String mode, int d3, int d2){
String reqFAVersementBanqueCumul = "SELECT * FROM mouvementcaisseligne WHERE Entree=0 AND IndexLiaison='' AND (IndexBanque<>'0' AND IndexBanque<>'') AND IDJour>=" + d3 + " AND IDJour<=" + d2 + " AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFAVersementBanqueCumul, com.iplans.myEntities.MyMouvementsCaisse.class);
List<MyMouvementsCaisse> resultat4 = q4.getResultList();
double faVersementBanqueCumul =0.0;
for(int i=0; i<resultat4.size(); i++){
faVersementBanqueCumul = faVersementBanqueCumul + resultat4.get(i).getSortie();
}
return faVersementBanqueCumul;
}
public double calculRetraitBanque(String mode, int d1, int d2){
String reqFARetraitBanque = "SELECT * FROM mouvementcaisseligne WHERE Sortie=0 AND IndexLiaison='' AND (IndexBanque<>'0' AND IndexBanque<>'') AND IDJour>=" + d1 + " AND IDJour<=" + d2 + " AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFARetraitBanque, com.iplans.myEntities.MyMouvementsCaisse.class);
List<MyMouvementsCaisse> resultat4 = q4.getResultList();
double faRetraitBanque =0.0;
for(int i=0; i<resultat4.size(); i++){
faRetraitBanque = faRetraitBanque + resultat4.get(i).getEntree();
}
return faRetraitBanque;
}
public double calculRetraitBanqueCumul(String mode, int d3, int d2){
String reqFARetraitBanqueCumul = "SELECT * FROM mouvementcaisseligne WHERE Sortie=0 AND IndexLiaison='' AND (IndexBanque<>'0' AND IndexBanque<>'') AND IDJour>=" + d3 + " AND IDJour<=" + d2 + " AND ModeReglement='" + mode + "' ";
Query q4 = em2.createNativeQuery(reqFARetraitBanqueCumul, com.iplans.myEntities.MyMouvementsCaisse.class);
List<MyMouvementsCaisse> resultat4 = q4.getResultList();
double faRetraitBanqueCumul =0.0;
for(int i=0; i<resultat4.size(); i++){
faRetraitBanqueCumul = faRetraitBanqueCumul + resultat4.get(i).getEntree();
}
return faRetraitBanqueCumul;
}
public List<MyVentillationCaisse> calculFlashActivite(String site, String user, int d1, int d2, int d3) throws Exception{
String req = "SELECT * FROM ventillationfa WHERE User='"+user+"' AND TypeFA='FAFinances' ORDER BY NEng DESC";
Query q = em2.createNativeQuery(req, com.iplans.myEntities.MyVentillationCaisse.class);
List<MyVentillationCaisse> result = q.getResultList();
List<MyVentillationCaisse> laliste = new ArrayList<>();
MyVentillationCaisse leflash = new MyVentillationCaisse();
List<String> listeMode = listerModeReg(site);
int i=0;
while ( i < listeMode.size()) {
leflash = new MyVentillationCaisse();
ventesComptant = calculVentesComptant(listeMode.get(i), d1, d2);
leflash.setnEng(result.get(0).getnEng());
leflash.setIndexe(result.get(0).getIndexe());
leflash.setDesignation(listeMode.get(i));
System.out.println(calculVentesComptant(listeMode.get(i), d1, d2));
leflash.setFAFondVeille(calculFondveille(listeMode.get(i), d1));
leflash.setFAVentesComptant(ventesComptant);
leflash.setFAVentesComptantCumul(calculDepensesCumul(listeMode.get(i), d3, d2));
leflash.setFARecouvrement(calculRecouvrement(listeMode.get(i), d1, d2));
leflash.setFARecouvrementCumul(calculRecouvrementCumul(listeMode.get(i), d3, d2));
leflash.setFADeposit(calculDeposit(listeMode.get(i), d1, d2));
leflash.setFADepositCumul(calculDepositCumul(listeMode.get(i), d3, d2));
leflash.setFADepenses(calculDepenses(listeMode.get(i), d1, d2));
leflash.setFADepensesCumul(calculDepensesCumul(listeMode.get(i), d3, d2));
leflash.setFAVersementBanque(calculVersementBanque(listeMode.get(i), d1, d2));
leflash.setFAVersementBanqueCumul(calculVersementBanqueCumul(listeMode.get(i), d3, d2));
leflash.setFARetraitBanque(calculRetraitBanque(listeMode.get(i), d1, d2));
leflash.setFARetraitBanqueCumul(calculRetraitBanqueCumul(listeMode.get(i), d3, d2));
leflash.setFAFondSolde(calculFondSolde(listeMode.get(i), d2));
laliste.add(leflash);
i++;
}
return laliste;
}
} |
Partager