public IEnumerable <SelectListItem> NumLotListForDropDown()
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
List <Lot> Lots = LotService.GetAll().ToList();
List <SelectListItem> listItems = new List <SelectListItem>();
listItems.Add(new SelectListItem {
Text = "Tous les lots", Value = "0"
});
Lots.DistinctBy(l => l.NumLot).ForEach(l =>
{
listItems.Add(new SelectListItem {
Text = "Lot " + l.NumLot, Value = l.NumLot
});
});
return(listItems);
}
}
}
public ActionResult ChangePassword(CompteConfigViewModel CompteCVM)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
RoleService RoleService = new RoleService(UOW);
EmployeService EmpService = new EmployeService(UOW);
Employe e = EmpService.GetEmployeByUername(Session["username"].ToString());
if (e.Password == CompteCVM.Password)
{
e.Password = CompteCVM.NewPassword;
e.ConfirmPassword = CompteCVM.NewPassword;
EmpService.Update(e);
EmpService.Commit();
}
else
{
ModelState.AddModelError("Password", "Votre mot de passe actuel est incorrect");
}
return(View());
}
}
}
public ActionResult addLot(Lot lot, int agent)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
Debug.WriteLine(lot);
LotService LotService = new LotService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
LotService.Add(lot);
LotService.Commit();
Affectation affectation = new Affectation
{
DateAffectation = DateTime.Now,
LotId = lot.LotId,
EmployeId = agent
};
AffectationService.Add(affectation);
AffectationService.Commit();
return(RedirectToAction("ConsulterClients", new { numLot = 0, sortOrder = 0 }));
}
}
}
static void Main(string[] args)
{
WakilRecouvContext wrc = new WakilRecouvContext();
Role r1 = new Role
{
role = "admin",
};
Employe emp2 = new Employe
{
Username = "******",
Password = "******",
ConfirmPassword = "******",
RoleId = 1
};
// wrc.Roles.Add(r1);
// wrc.Employes.Add(emp);
// wrc.SaveChanges();
// IEmployeService IEmpService = new EmployeService();
//IRoleService IRoleService = new RoleService();
//IRoleService.Add(r1);
//IRoleService.Commit();
////IEmpService.Add(emp2);
//IEmpService.Commit();
//foreach(var emp in IEmpService.GetEmployeByRole("agent"))
//{
// System.Console.WriteLine(emp.Username);
//}
//System.Console.WriteLine("Fin");
}
public ActionResult AccountList(string type)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
EmployeService EmpService = new EmployeService(UOW);
RoleService RoleService = new RoleService(UOW);
var Roles = RoleService.GetAll();
ViewBag.RoleList = new SelectList(Roles, "RoleId", "role");
if (type == "0")
{
return(View(EmpService.GetAll()));
}
else if (type == "1")
{
return(View(EmpService.GetEmployeByVerified(true)));
}
else if (type == "2")
{
return(View(EmpService.GetEmployeByVerified(false)));
}
else
{
return(View(EmpService.GetAll()));
}
}
}
}
public ActionResult InscriptionCompte(Employe emp)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
RoleService RoleService = new RoleService(UOW);
EmployeService EmpService = new EmployeService(UOW);
var Roles = RoleService.GetAll();
ViewBag.RoleList = new SelectList(Roles, "RoleId", "role");
if (EmpService.GetEmployeByUername(emp.Username) != null)
{
ModelState.AddModelError("Username", "Nom d'utilisateur existe deja !");
}
else
{
EmpService.Add(emp);
EmpService.Commit();
return(RedirectToAction("Login"));
}
//Response.Write("<script>alert('" + emp.RoleId + "')</script>");
return(View());
}
}
}
public ActionResult mensuelStatTraite(string year, string month)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
FormulaireService FormulaireService = new FormulaireService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
EmployeService EmpService = new EmployeService(UOW);
int mensuelPoste1Tot = 0;
int mensuelPoste2Tot = 0;
int mensuelPoste3Tot = 0;
int mensuelPoste4Tot = 0;
int tot = 0;
double mensuelRentaPoste1Tot = 0;
double mensuelRentaPoste2Tot = 0;
double mensuelRentaPoste3Tot = 0;
double mensuelRentaPoste4Tot = 0;
double totRenta = 0;
List <ClientAffecteViewModel> JoinedList = new List <ClientAffecteViewModel>();
JoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).ToList();
if (month != "" && year != "" && month != null && year != null)
{
mensuelPoste1Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE1" && j.Formulaire.TraiteLe.Date.Year + "" == year && j.Formulaire.TraiteLe.Date.Month + "" == month).Count();
mensuelPoste2Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE2" && j.Formulaire.TraiteLe.Date.Year + "" == year && j.Formulaire.TraiteLe.Date.Month + "" == month).Count();
mensuelPoste3Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE3" && j.Formulaire.TraiteLe.Date.Year + "" == year && j.Formulaire.TraiteLe.Date.Month + "" == month).Count();
mensuelPoste4Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE4" && j.Formulaire.TraiteLe.Date.Year + "" == year && j.Formulaire.TraiteLe.Date.Month + "" == month).Count();
tot = mensuelPoste1Tot + mensuelPoste2Tot + mensuelPoste3Tot + mensuelPoste4Tot;
mensuelRentaPoste1Tot = rentabiliteAgents(year, month, "POSTE1", LotService, FormulaireService, AffectationService);
mensuelRentaPoste2Tot = rentabiliteAgents(year, month, "POSTE2", LotService, FormulaireService, AffectationService);
mensuelRentaPoste3Tot = rentabiliteAgents(year, month, "POSTE3", LotService, FormulaireService, AffectationService);
mensuelRentaPoste4Tot = rentabiliteAgents(year, month, "POSTE4", LotService, FormulaireService, AffectationService);
totRenta = mensuelRentaPoste1Tot + mensuelRentaPoste2Tot + mensuelRentaPoste3Tot + mensuelRentaPoste4Tot;
}
return(Json(new { tot = tot, mensuelPoste1Tot = mensuelPoste1Tot, mensuelPoste2Tot = mensuelPoste2Tot, mensuelPoste3Tot = mensuelPoste3Tot, mensuelPoste4Tot = mensuelPoste4Tot, totRenta = String.Format("{0:0.00}", totRenta), mensuelRentaPoste1Tot = mensuelRentaPoste1Tot, mensuelRentaPoste2Tot = mensuelRentaPoste2Tot, mensuelRentaPoste3Tot = mensuelRentaPoste3Tot, mensuelRentaPoste4Tot = mensuelRentaPoste4Tot }));
}
}
}
public ActionResult updateLot(ClientAffecteViewModel cavm)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
Lot newlot = LotService.GetById(cavm.Lot.LotId);
newlot.Adresse = cavm.Lot.Adresse;
newlot.Compte = cavm.Lot.Compte;
newlot.DescIndustry = cavm.Lot.DescIndustry;
newlot.Emploi = cavm.Lot.Emploi;
newlot.IDClient = cavm.Lot.IDClient;
newlot.NomClient = cavm.Lot.NomClient;
newlot.NumLot = cavm.Lot.NumLot;
newlot.PostCode = cavm.Lot.PostCode;
newlot.SoldeDebiteur = cavm.Lot.SoldeDebiteur;
newlot.TelFixe = cavm.Lot.TelFixe;
newlot.TelPortable = cavm.Lot.TelPortable;
newlot.Type = cavm.Lot.Type;
newlot.Emploi = cavm.Lot.Emploi;
LotService.Update(newlot);
LotService.Commit();
Affectation affectation = AffectationService.GetById(cavm.Affectation.AffectationId);
Debug.WriteLine(cavm.Affectation.EmployeId);
if (affectation == null)
{
Affectation aff = new Affectation
{
EmployeId = cavm.Affectation.EmployeId,
LotId = cavm.Lot.LotId,
DateAffectation = DateTime.Now
};
AffectationService.Add(aff);
AffectationService.Commit();
}
else
{
affectation.EmployeId = cavm.Affectation.EmployeId;
affectation.LotId = cavm.Lot.LotId;
affectation.DateAffectation = DateTime.Now;
AffectationService.Update(affectation);
AffectationService.Commit();
}
return(RedirectToAction("ConsulterClients", new { numLot = 0, sortOrder = 0 }));
}
}
}
public ActionResult InscriptionCompte()
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
RoleService RoleService = new RoleService(UOW);
var Roles = RoleService.GetAll();
ViewBag.RoleList = new SelectList(Roles, "RoleId", "role");
return(View("InscriptionCompte"));
}
}
}
public ActionResult deleteLot(int id, string currentFilter, string currentNumLot, string currentPage)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
LotService.Delete(LotService.GetById(id));
LotService.Commit();
return(RedirectToAction("ConsulterClients", "Lot", new { currentFilter, currentNumLot, currentPage }));
}
}
}
public ActionResult UpdateAccount(int id)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
EmployeService EmpService = new EmployeService(UOW);
RoleService RoleService = new RoleService(UOW);
var Roles = RoleService.GetAll();
ViewBag.RoleList = new SelectList(Roles, "RoleId", "role");
return(View(EmpService.GetById(id)));
}
}
}
public ActionResult genererFacture(int?page)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
FactureService factureService = new FactureService(UOW);
List <Facture> factureList = factureService.GetAll().OrderByDescending(f => f.DateExtrait).ToList();
ViewData["list"] = new SelectList(NumLotListForDropDown(LotService), "Value", "Text");
ViewBag.total = factureList.Count();
int pageSize = 10;
int pageNumber = (page ?? 1);
return(View(factureList.ToPagedList(pageNumber, pageSize)));
}
}
}
public IEnumerable <SelectListItem> AgentListForDropDown()
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
EmployeService EmpService = new EmployeService(UOW);
List <Employe> agents = EmpService.GetMany(emp => emp.Role.role.Equals("agent") && emp.IsVerified == true).ToList();
List <SelectListItem> listItems = new List <SelectListItem>();
agents.ForEach(l => {
listItems.Add(new SelectListItem {
Text = l.Username, Value = l.EmployeId + ""
});
});
return(listItems);
}
}
}
public ActionResult Renseigner(int?page)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LettreService lettreService = new LettreService(UOW);
LotService LotService = new LotService(UOW);
EmployeService EmployeService = new EmployeService(UOW);
List <Lettre> lettreList = lettreService.GetAll().OrderByDescending(f => f.DateExtrait).ToList();
ViewData["list"] = new SelectList(NumLotListForDropDown(LotService), "Value", "Text");
ViewBag.AgentList = new SelectList(AgentListForDropDown(EmployeService), "Value", "Text");
ViewBag.total = lettreList.Count();
int pageSize = 10;
int pageNumber = (page ?? 1);
return(View(lettreList.ToPagedList(pageNumber, pageSize)));
}
}
}
public ActionResult Login(Compte compte)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
EmployeService EmpService = new EmployeService(UOW);
Employe emp = EmpService.GetEmployeByUername(compte.Username);
if (emp == null)
{
ModelState.AddModelError("Username", "Nom d'utilisateur inexistant");
}
else if (emp != null)
{
if (emp.Password == compte.Password)
{
if (emp.IsVerified == false)
{
ModelState.AddModelError("Connect", "Votre compte n'est pas encore validé par un administrateur");
}
else if (emp.IsVerified == true)
{
Session["username"] = emp.Username;
Session["role"] = emp.Role.role;
return(RedirectToAction("Index", "Home"));
}
}
else
{
ModelState.AddModelError("Password", "Mot de passe incorrect");
}
}
return(View());
}
}
}
public DataTable GenerateDatatableFromJoinedList(List <ClientCompte> list)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
FormulaireService FormulaireService = new FormulaireService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
EmployeService EmpService = new EmployeService(UOW);
List <ClientCompte> newList = new List <ClientCompte>();
DataTable dataTable = new DataTable();
newList = list.Select(j =>
new ClientCompte
{
Nom = j.Nom,
Compte = j.Compte
}).ToList();
dataTable.Columns.Add("Nom", typeof(string));
dataTable.Columns.Add("Compte", typeof(string));
foreach (ClientCompte c in newList)
{
DataRow row = dataTable.NewRow();
row["Nom"] = c.Nom;
row["Compte"] = c.Compte;
dataTable.Rows.Add(row);
}
Debug.WriteLine("---->" + dataTable.Rows.Count);
return(dataTable);
}
}
}
public ActionResult updateLot(int id)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
EmployeService EmpService = new EmployeService(UOW);
ViewData["list"] = new SelectList(NumLotListForDropDown(), "Value", "Text");
ViewData["agent"] = new SelectList(AgentListForDropDown(), "Value", "Text");
//Lot lot = LotService.GetById(id);
ClientAffecteViewModel cavm = new ClientAffecteViewModel();
cavm = (from a in AffectationService.GetAll()
join l in LotService.GetAll() on a.LotId equals l.LotId
where l.LotId == id
select new ClientAffecteViewModel
{
Affectation = a,
Lot = l
}).FirstOrDefault();
if (cavm == null)
{
cavm = new ClientAffecteViewModel();
Lot lot = LotService.GetById(id);
cavm.Lot = lot;
}
return(View(cavm));
}
}
}
public ActionResult UpdateAccount(int id, Employe e)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
EmployeService EmpService = new EmployeService(UOW);
RoleService RoleService = new RoleService(UOW);
var Roles = RoleService.GetAll();
ViewBag.RoleList = new SelectList(Roles, "RoleId", "role");
Employe emp = EmpService.GetById(id);
emp.RoleId = e.RoleId;
emp.IsVerified = e.IsVerified;
emp.ConfirmPassword = emp.Password;
EmpService.Update(emp);
EmpService.Commit();
return(View("AccountList", EmpService.GetAll()));
}
}
}
public void updateEmploi(string compte, string emploi)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
Lot lot = LotService.Get(l => l.IDClient == compte);
if (lot != null)
{
lot.Emploi = emploi;
Debug.WriteLine(lot.Compte);
LotService.Commit();
}
else
{
Debug.WriteLine("no no ===>" + compte);
}
}
}
}
public DatabaseFactory()
{
dataContext = new WakilRecouvContext();
}
//IDatabaseFactory databaseFactory;
public RepositoryBase(WakilRecouvContext WakilCtx)
{
dataContext = WakilCtx;
//this.databaseFactory = dbFactory;
dbset = WakilCtx.Set <T>();
}
public ActionResult quotidienStatTraite(string date)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
FormulaireService FormulaireService = new FormulaireService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
DateTime d = new DateTime();
int quotidienPoste1Tot = 0;
int quotidienPoste2Tot = 0;
int quotidienPoste3Tot = 0;
int quotidienPoste4Tot = 0;
int tot = 0;
double quotidienRentaPoste1Tot = 0;
double quotidienRentaPoste2Tot = 0;
double quotidienRentaPoste3Tot = 0;
double quotidienRentaPoste4Tot = 0;
double totRenta = 0;
List <ClientAffecteViewModel> JoinedList = new List <ClientAffecteViewModel>();
JoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).ToList();
if (date != "" && date != null)
{
if (DateTime.TryParse(date, out d))
{
quotidienPoste1Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE1" && j.Formulaire.TraiteLe.Date == d.Date).Count();
quotidienPoste2Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE2" && j.Formulaire.TraiteLe.Date == d.Date).Count();
quotidienPoste3Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE3" && j.Formulaire.TraiteLe.Date == d.Date).Count();
quotidienPoste4Tot = JoinedList.Where(j => j.Affectation.Employe.Username == "POSTE4" && j.Formulaire.TraiteLe.Date == d.Date).Count();
tot = quotidienPoste1Tot + quotidienPoste2Tot + quotidienPoste3Tot + quotidienPoste4Tot;
quotidienRentaPoste1Tot = rentabiliteAgents(d, "POSTE1", LotService, FormulaireService, AffectationService);
quotidienRentaPoste2Tot = rentabiliteAgents(d, "POSTE2", LotService, FormulaireService, AffectationService);
quotidienRentaPoste3Tot = rentabiliteAgents(d, "POSTE3", LotService, FormulaireService, AffectationService);
quotidienRentaPoste4Tot = rentabiliteAgents(d, "POSTE4", LotService, FormulaireService, AffectationService);
totRenta = quotidienRentaPoste1Tot + quotidienRentaPoste2Tot + quotidienRentaPoste3Tot + quotidienRentaPoste4Tot;
}
}
return(Json(new { tot = tot, quotidienPoste1Tot = quotidienPoste1Tot, quotidienPoste2Tot = quotidienPoste2Tot, quotidienPoste3Tot = quotidienPoste3Tot, quotidienPoste4Tot = quotidienPoste4Tot, totRenta = String.Format("{0:0.00}", totRenta), quotidienRentaPoste1Tot = quotidienRentaPoste1Tot, quotidienRentaPoste2Tot = quotidienRentaPoste2Tot, quotidienRentaPoste3Tot = quotidienRentaPoste3Tot, quotidienRentaPoste4Tot = quotidienRentaPoste4Tot }));
}
}
}
public ActionResult Index()
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
EmployeService EmpService = new EmployeService(UOW);
RoleService RoleService = new RoleService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
LotService LotService = new LotService(UOW);
FormulaireService FormulaireService = new FormulaireService(UOW);
List <ClientAffecteViewModel> traiteList = new List <ClientAffecteViewModel>();
List <HomeViewModel> result = new List <HomeViewModel>();
List <Lot> lots = new List <Lot>();
string[] lotsLst = { };
lots = LotService.GetAll().ToList();
lotsLst = lots.DistinctBy(l => l.NumLot).Select(l => l.NumLot).ToArray();
List <Affectation> Affectations = new List <Affectation>();
List <Formulaire> Formulaires = new List <Formulaire>();
string[] agents = { };
Affectations = AffectationService.GetAll().ToList();
Formulaires = FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe).ToList();
foreach (string numlot in lotsLst)
{
lots = LotService.GetAll().Where(l => l.NumLot.Equals(numlot)).ToList();
traiteList = (from f in Formulaires
join a in Affectations on f.AffectationId equals a.AffectationId
join l in lots on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).ToList();
var agentLinq = (from a in Affectations
join l in lots on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Affectation = a
});
agents = agentLinq.DistinctBy(a => a.Affectation.Employe.Username).Select(a => a.Affectation.Employe.Username).ToArray();
string agentsStr = string.Join(", ", agents);
float nbAffTotal = agentLinq.Count();
float nbTraite = traiteList.Count();
string avgLot = String.Format("{0:0.00}", (nbTraite / nbAffTotal) * 100);
HomeViewModel homeViewModel = new HomeViewModel
{
agents = agentsStr,
nbAffTotal = nbAffTotal + "",
nbTraite = nbTraite + "",
numLot = numlot,
avancement = avgLot.Replace(",", ".")
};
result.Add(homeViewModel);
}
return(View(result));
}
}
}
public ActionResult StatLot(int numLot, string typeStat, string dateStat, string agent)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
FormulaireService FormulaireService = new FormulaireService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
EmployeService EmpService = new EmployeService(UOW);
ViewData["list"] = new SelectList(NumLotListForDropDown(), "Value", "Text");
ViewData["sortOrder"] = new SelectList(TypeStatForDropDown(), "Value", "Text");
ViewBag.AgentList = new SelectList(AgentListForDropDown(), "Value", "Text");
int nb = 0;
int rdv = 0;
int fn = 0;
int versement = 0;
int encours = 0;
string avgRdv = 0 + "";
string avgVers = 0 + "";
string avgFn = 0 + "";
string avgencours = 0 + "";
ViewData["years"] = new SelectList(YearListForDropDown(), "Value", "Text");
ViewData["month"] = new SelectList(MonthListForDropDown(), "Value", "Text");
List <Affectation> affectations = new List <Affectation>();
List <Lot> lots = new List <Lot>();
List <ClientAffecteViewModel> rdvCAVm = new List <ClientAffecteViewModel>();
List <ClientAffecteViewModel> fnCAVm = new List <ClientAffecteViewModel>();
List <ClientAffecteViewModel> versCAVm = new List <ClientAffecteViewModel>();
List <ClientAffecteViewModel> encoursCAVm = new List <ClientAffecteViewModel>();
List <ClientAffecteViewModel> tot = new List <ClientAffecteViewModel>();
if (numLot == 0)
{
tot = (from a in AffectationService.GetAll()
join l in LotService.GetAll() on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Affectation = a,
Lot = l,
}).ToList();
rdvCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.RDV).ToList();
fnCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.FAUX_NUM).ToList();
versCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.SOLDE_TRANCHE || f.Formulaire.EtatClient == Note.SOLDE).ToList();
encoursCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.REFUS_PAIEMENT || f.Formulaire.EtatClient == Note.RAPPEL || f.Formulaire.EtatClient == Note.RACCROCHE || f.Formulaire.EtatClient == Note.NRP || f.Formulaire.EtatClient == Note.INJOIGNABLE || f.Formulaire.EtatClient == Note.AUTRE || f.Formulaire.EtatClient == Note.A_VERIFIE).ToList();
}
else
{
tot = (from a in AffectationService.GetAll()
join l in LotService.GetAll() on a.LotId equals l.LotId
where l.NumLot == numLot.ToString()
select new ClientAffecteViewModel
{
Affectation = a,
Lot = l,
}).ToList();
rdvCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where l.NumLot == numLot + ""
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.RDV).ToList();
fnCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where l.NumLot == numLot + ""
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.FAUX_NUM).ToList();
versCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where l.NumLot == numLot + ""
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.SOLDE_TRANCHE || f.Formulaire.EtatClient == Note.SOLDE).ToList();
encoursCAVm = (from f in FormulaireService.GetAll().OrderByDescending(o => o.TraiteLe)
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where l.NumLot == numLot + ""
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).DistinctBy(d => d.Formulaire.AffectationId).Where(f => f.Formulaire.EtatClient == Note.REFUS_PAIEMENT || f.Formulaire.EtatClient == Note.RAPPEL || f.Formulaire.EtatClient == Note.RACCROCHE || f.Formulaire.EtatClient == Note.NRP || f.Formulaire.EtatClient == Note.INJOIGNABLE || f.Formulaire.EtatClient == Note.AUTRE || f.Formulaire.EtatClient == Note.A_VERIFIE).ToList();
}
if (typeStat == "1")
{
nb = tot.Count();
rdv = rdvCAVm.Count();
fn = fnCAVm.Count();
versement = versCAVm.Count();
encours = encoursCAVm.Count();
avgRdv = String.Format("{0:0.00}", ((float)rdv / (float)nb) * 100);
avgFn = String.Format("{0:0.00}", ((float)fn / (float)nb) * 100);
avgVers = String.Format("{0:0.00}", ((float)versement / (float)nb) * 100);
avgencours = String.Format("{0:0.00}", ((float)encours / (float)nb) * 100);
}
else if (typeStat == "2")
{
rdv = rdvCAVm.Where(f => f.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
fn = fnCAVm.Where(f => f.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
versement = versCAVm.Where(f => f.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
encours = encoursCAVm.Where(f => f.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
nb = rdv + fn + versement + encours;
avgRdv = String.Format("{0:0.00}", ((float)rdv / (float)nb) * 100);
avgFn = String.Format("{0:0.00}", ((float)fn / (float)nb) * 100);
avgVers = String.Format("{0:0.00}", ((float)versement / (float)nb) * 100);
avgencours = String.Format("{0:0.00}", ((float)encours / (float)nb) * 100);
}
else if (typeStat == "3")
{
rdv = rdvCAVm.Where(a => a.Affectation.EmployeId + "" == agent).Count();
fn = fnCAVm.Where(a => a.Affectation.EmployeId + "" == agent).Count();
versement = versCAVm.Where(a => a.Affectation.EmployeId + "" == agent).Count();
encours = encoursCAVm.Where(a => a.Affectation.EmployeId + "" == agent).Count();
nb = rdv + fn + versement + encours;
avgRdv = String.Format("{0:0.00}", ((float)rdv / (float)nb) * 100);
avgFn = String.Format("{0:0.00}", ((float)fn / (float)nb) * 100);
avgVers = String.Format("{0:0.00}", ((float)versement / (float)nb) * 100);
avgencours = String.Format("{0:0.00}", ((float)encours / (float)nb) * 100);
}
else if (typeStat == "4")
{
rdv = rdvCAVm.Where(a => a.Affectation.EmployeId + "" == agent && a.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
fn = fnCAVm.Where(a => a.Affectation.EmployeId + "" == agent && a.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
versement = versCAVm.Where(a => a.Affectation.EmployeId + "" == agent && a.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
encours = encoursCAVm.Where(a => a.Affectation.EmployeId + "" == agent && a.Formulaire.TraiteLe.Date == DateTime.Parse(dateStat).Date).Count();
nb = rdv + fn + versement + encours;
avgRdv = String.Format("{0:0.00}", ((float)rdv / (float)nb) * 100);
avgFn = String.Format("{0:0.00}", ((float)fn / (float)nb) * 100);
avgVers = String.Format("{0:0.00}", ((float)versement / (float)nb) * 100);
avgencours = String.Format("{0:0.00}", ((float)encours / (float)nb) * 100);
}
StatLot statLot = new StatLot()
{
nb = nb,
rdv = rdv,
fn = fn,
versement = versement,
encours = encours,
avgRdv = avgRdv.Replace(",", "."),
avgFn = avgFn.Replace(",", "."),
avgVers = avgVers.Replace(",", "."),
avgencours = avgencours.Replace(",", ".")
};
return(View("Index", statLot));
}
}
}
public ActionResult extraireFacture(string numLot, string factureNum, string pourcentage, string debutDate, string finDate)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
FormulaireService FormulaireService = new FormulaireService(UOW);
LotService LotService = new LotService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
FactureService factureService = new FactureService(UOW);
ViewData["list"] = new SelectList(NumLotListForDropDown(LotService), "Value", "Text");
DateTime startDate = DateTime.Parse(debutDate);
DateTime endDate = DateTime.Parse(finDate);
double trancheTot = 0;
double soldeTot = 0;
double tot = 0;
float revenuParOp = float.Parse(pourcentage.Replace(".", ","));
List <ClientAffecteViewModel> JoinedList = new List <ClientAffecteViewModel>();
List <ClientAffecteViewModel> TempJoinedList = new List <ClientAffecteViewModel>();
List <ClientAffecteViewModel> AnnexeJoinedList = new List <ClientAffecteViewModel>();
if (numLot == "0")
{
JoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where f.Status == Domain.Entities.Status.VERIFIE
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).Where(j => j.Formulaire.TraiteLe.Date >= startDate.Date && j.Formulaire.TraiteLe.Date <= endDate.Date).ToList();
}
else
{
JoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where f.Status == Domain.Entities.Status.VERIFIE && l.NumLot.Equals(numLot)
select new ClientAffecteViewModel
{
Formulaire = f,
Affectation = a,
Lot = l,
}).Where(j => j.Formulaire.TraiteLe.Date >= startDate.Date && j.Formulaire.TraiteLe.Date <= endDate.Date).ToList();
}
foreach (ClientAffecteViewModel cvm in JoinedList)
{
if (cvm.Formulaire.EtatClient == Domain.Entities.Note.SOLDE_TRANCHE)
{
trancheTot += (cvm.Formulaire.MontantVerseDeclare * revenuParOp) / 100;
cvm.vers = cvm.Formulaire.MontantVerseDeclare;
cvm.recouvre = cvm.Formulaire.MontantVerseDeclare;
AnnexeJoinedList.Add(cvm);
}
if (cvm.Formulaire.EtatClient == Domain.Entities.Note.SOLDE)
{
TempJoinedList = getTraitHist(cvm.Affectation.AffectationId, FormulaireService, AffectationService, LotService);
if (TempJoinedList.Count() == 0)
{
soldeTot += (cvm.Formulaire.MontantDebInitial * revenuParOp) / 100;
cvm.recouvre = cvm.Formulaire.MontantDebInitial;
cvm.vers = cvm.Formulaire.MontantVerseDeclare;
AnnexeJoinedList.Add(cvm);
}
else
{
soldeTot += (TempJoinedList.FirstOrDefault().Formulaire.MontantDebMAJ *revenuParOp) / 100;
cvm.vers = cvm.Formulaire.MontantVerseDeclare;
cvm.recouvre = TempJoinedList.FirstOrDefault().Formulaire.MontantDebMAJ;
AnnexeJoinedList.Add(cvm);
}
}
}
string lotsNames = "";
List <string> listNameLot = JoinedList.DistinctBy(j => j.Lot.NumLot).Select(l => l.Lot.NumLot).ToList();
if (listNameLot.Count() == 1)
{
lotsNames = listNameLot.FirstOrDefault();
}
else if (listNameLot.Count() > 1)
{
string lastlots = JoinedList.DistinctBy(j => j.Lot.NumLot).Select(l => l.Lot.NumLot).LastOrDefault();
listNameLot.RemoveAt(listNameLot.Count - 1);
string lots = String.Join(", ", listNameLot);
lotsNames = lots + " et " + lastlots;
}
tot = soldeTot + trancheTot;
FactureContent factureContent = new FactureContent();
factureContent.FacNum = factureNum;
factureContent.Date = DateTime.Today;
factureContent.Beneficiere = "Zitouna Bank";
factureContent.PrixHT = tot;
factureContent.PrixTVA = (tot * 19) / 100;
factureContent.TimbreFiscal = 0.600;
factureContent.PrixTTC = tot + factureContent.PrixTVA;
string annexeFileName = "Annexe_" + DateTime.Now.ToString("dd.MM.yyyy") + "_" + ((DateTimeOffset)DateTime.UtcNow).ToUnixTimeSeconds() + ".xlsx";
string factureFileName = "Facture_" + DateTime.Now.ToString("dd.MM.yyyy") + "_" + ((DateTimeOffset)DateTime.UtcNow).ToUnixTimeSeconds() + ".pdf";
string pathAnnexe = GetFolderName() + "/" + annexeFileName;
string pathFacture = GetFolderName() + "/" + factureFileName;
Facture facture = new Facture()
{
AnnexePathName = annexeFileName,
FacturePathName = factureFileName,
DateDeb = startDate,
DateFin = endDate,
DateExtrait = DateTime.Now
};
factureService.Add(facture);
factureService.Commit();
GenerateExcel(GenerateDatatableFromJoinedList(AnnexeJoinedList), pathAnnexe, String.Format("{0:0.000}", AnnexeJoinedList.Sum(j => j.recouvre)));
GeneratePDF(pathFacture, lotsNames, factureContent);
return(RedirectToAction("genererFacture", new { page = 1 }));
}
}
}
public ActionResult ExtraireLettreAction(string numLot, string debutDate, string finDate, string agent)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
FormulaireService FormulaireService = new FormulaireService(UOW);
AffectationService AffectationService = new AffectationService(UOW);
LotService LotService = new LotService(UOW);
LettreService lettreService = new LettreService(UOW);
EmployeService EmployeService = new EmployeService(UOW);
ViewData["list"] = new SelectList(NumLotListForDropDown(LotService), "Value", "Text");
ViewBag.AgentList = new SelectList(AgentListForDropDown(EmployeService), "Value", "Text");
string lettreDir = GetFolderNameForLettre();
DateTime startDate = DateTime.Parse(debutDate);
DateTime endDate = DateTime.Parse(finDate);
if (!Directory.Exists(lettreDir))
{
Directory.CreateDirectory(lettreDir);
}
string lettreDirList = Directory.GetDirectories(lettreDir).Length + 1 + "_" + ((DateTimeOffset)DateTime.UtcNow).ToUnixTimeSeconds() + "";
string dirLettre = "";
if (!Directory.Exists(lettreDir + "/" + lettreDirList))
{
Directory.CreateDirectory(lettreDir + "/" + lettreDirList);
dirLettre = lettreDir + "/" + lettreDirList;
}
List <LettreContent> lettreJoinedList = new List <LettreContent>();
if (numLot == "0")
{
lettreJoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where (f.TraiteLe.Date >= startDate.Date && f.TraiteLe.Date <= endDate.Date) && (f.EtatClient == Note.FAUX_NUM || f.EtatClient == Note.NRP || f.EtatClient == Note.INJOIGNABLE) && lettreIsTrue(FormulaireService, a.AffectationId)
select new LettreContent
{
NumLot = l.NumLot,
Adresse = l.Adresse,
NomClient = l.NomClient,
Agence = l.DescIndustry,
Compte = l.Compte
}).ToList();
if (int.Parse(agent) != 0)
{
lettreJoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where (f.TraiteLe.Date >= startDate.Date && f.TraiteLe.Date <= endDate.Date) && (f.EtatClient == Note.FAUX_NUM || f.EtatClient == Note.NRP || f.EtatClient == Note.INJOIGNABLE) && lettreIsTrue(FormulaireService, a.AffectationId) && a.EmployeId == int.Parse(agent)
select new LettreContent
{
NumLot = l.NumLot,
Adresse = l.Adresse,
NomClient = l.NomClient,
Agence = l.DescIndustry,
Compte = l.Compte
}).ToList();
}
else
{
lettreJoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where (f.TraiteLe.Date >= startDate.Date && f.TraiteLe.Date <= endDate.Date) && (f.EtatClient == Note.FAUX_NUM || f.EtatClient == Note.NRP || f.EtatClient == Note.INJOIGNABLE) && lettreIsTrue(FormulaireService, a.AffectationId)
select new LettreContent
{
NumLot = l.NumLot,
Adresse = l.Adresse,
NomClient = l.NomClient,
Agence = l.DescIndustry,
Compte = l.Compte
}).ToList();
}
}
else
{
if (int.Parse(agent) != 0)
{
lettreJoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where (f.TraiteLe.Date >= startDate.Date && f.TraiteLe.Date <= endDate.Date) && (f.EtatClient == Note.FAUX_NUM || f.EtatClient == Note.NRP || f.EtatClient == Note.INJOIGNABLE) && lettreIsTrue(FormulaireService, a.AffectationId) && (l.NumLot.Equals(numLot)) && a.EmployeId == int.Parse(agent)
select new LettreContent
{
NumLot = l.NumLot,
Adresse = l.Adresse,
NomClient = l.NomClient,
Agence = l.DescIndustry,
Compte = l.Compte,
IdAgent = a.EmployeId + ""
}).ToList();
}
else
{
lettreJoinedList = (from f in FormulaireService.GetAll()
join a in AffectationService.GetAll() on f.AffectationId equals a.AffectationId
join l in LotService.GetAll() on a.LotId equals l.LotId
where (f.TraiteLe.Date >= startDate.Date && f.TraiteLe.Date <= endDate.Date) && (f.EtatClient == Note.FAUX_NUM || f.EtatClient == Note.NRP || f.EtatClient == Note.INJOIGNABLE) && lettreIsTrue(FormulaireService, a.AffectationId) && (l.NumLot.Equals(numLot))
select new LettreContent
{
NumLot = l.NumLot,
Adresse = l.Adresse,
NomClient = l.NomClient,
Agence = l.DescIndustry,
Compte = l.Compte,
IdAgent = a.EmployeId + ""
}).ToList();
}
}
int x = 0;
ViewBag.total = lettreJoinedList.Count();
foreach (LettreContent lc in lettreJoinedList)
{
string path = dirLettre + "/" + x + "_" + "lettre" + "_" + lc.Compte + ".docx";
if (Directory.Exists(dirLettre))
{
GenerateWordForLettre(path, lc);
}
ViewData["currClient"] = x;
x++;
}
string zipPath = zipFolderResult(dirLettre);
string adressExcelPath = Server.MapPath("~/Uploads/Lettre/0_Result/") + DateTime.Now.ToString("dd.MM.yyyy") + "_" + Path.GetFileName(dirLettre) + ".xlsx";
GenerateExcelForLettre(GenerateDatatableFromJoinedListForLettre(lettreJoinedList), adressExcelPath);
Lettre lettre = new Lettre
{
DateDeb = startDate.Date,
DateFin = endDate.Date,
DateExtrait = DateTime.Now,
LettrePathName = zipPath,
LettreAdressPathName = Path.GetFileName(adressExcelPath)
};
lettreService.Add(lettre);
lettreService.Commit();
return(RedirectToAction("Renseigner", new { page = 1 }));
}
}
}
public ActionResult UploadExcel(HttpPostedFileBase PostedFile)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
string filename = "";
//Nthabtou li fichier mahouch feragh makenesh nabaathou erreur lel client
if (PostedFile != null)
{
filename = PostedFile.FileName;
//nsobou l fichier aana fel serveur
string filePath = string.Empty;
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(PostedFile.FileName);
string extension = Path.GetExtension(PostedFile.FileName);
string conString = string.Empty;
//nekhdhou num mtaa lot men esm l fichier ex: Lot 11 => 11
string numLot = string.Join(string.Empty, Regex.Matches(PostedFile.FileName, @"\d+").OfType <Match>().Select(m => m.Value));
//string numLot = filename.Split('_')[1];
//Nthaabtou eli l client selectiona fichier excel moush haja okhra makanesh nabaathoulou erreur
if (PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
PostedFile.SaveAs(filePath);
//besh nakhtarou connectionString selon l version mtaa excel (xls = 2003 o xlsx mel 2013 o ahna tal3in)
//L connectionString predefini fel web.config mteena
switch (extension)
{
case ".xls":
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx":
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
//Taoua besh nebdew nakraw l fichier Excel bel library OleDd
DataTable dt = new DataTable();
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
// Houni nebdew naakraw awel sheet name mtaa l document excel mteena (eli ken jina fi table SQL rahou le nom de la table)
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Houni recuperation mtaa les données
connExcel.Open();
cmdExcel.CommandText = "SELECT * FROM [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
//string argNumLot = dt.Columns[0].ColumnName;
string argId = "ID";
string argCompte = "Compte";
string argNomClient = "Nom Client";
string argCompteDebiteur = "Compte Debiteur";
string argTelPortable = "Tel Portable";
string argTelFixe = "Tel Fixe";
string argAgence = "Agence";
string argAdresse = "Adresse";
string argType = "Type";
string argNumero = "Numero";
string argEmploi = "Travail";
/* string Compte1Arg = "Compte1";
* string Compte2Arg = "Compte2";
* string Nom1Arg = "Nom1";
* string Nom2Arg = "Nom2";*/
/* List<ClientCompte> ccList1 = new List<ClientCompte>();
* List<ClientCompte> ccList2 = new List<ClientCompte>();
*/
foreach (DataRow row in dt.Rows)
{
string IdClient = "";
string NomClient = "";
string Compte = "";
string CompteDebiteur = "";
string TelPortable = "";
string TelFixe = "";
string Agence = "";
string Adresse = "";
string Type = "";
string Numero = "";
string Emploi = "";
/* string Compte1 = "";
* string Compte2 = "";
* string Nom1= "";
* string Nom2= "";*/
//updateEmploi(row["Compte"].ToString(), row["Travail"].ToString());
/*
*
* try
* {
* Compte1 = row[Compte1Arg].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + Compte1Arg + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* Compte2 = row[Compte2Arg].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + Compte2Arg + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
*
*/
/*
* try
* {
* Nom1 = row[Nom1Arg].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + Nom1Arg + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
*
* try
* {
* Nom2 = row[Nom2Arg].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + Nom2Arg + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* ClientCompte c1 = new ClientCompte()
* {
*
* Nom = Nom1,
* Compte = Compte1
*
* };
* ClientCompte c2 = new ClientCompte()
* {
*
* Nom = Nom2,
* Compte = Compte2
*
* };*/
/*
* ccList1.Add(c1);
* ccList2.Add(c2);
*/
//Debug.WriteLine(row["Travail"].ToString());
updateEmploi(row["ID"].ToString(), row["Travail"].ToString());
//On verifier aala koll colonne eli l fichier excel il respecte bien le syntaxe mtaa koll colonne bel try/catch
//O nsobou l information/donné fi variable
/*
*
* try
* {
* IdClient = row[argId].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argId + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* NomClient = row[argNomClient].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argNomClient + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* Compte = row[argCompte].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argCompte + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* CompteDebiteur = row[argCompteDebiteur].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argCompteDebiteur + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* TelPortable = row[argTelPortable].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argTelPortable + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* TelFixe = row[argTelFixe].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argTelFixe + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
*
* }
*
* try
* {
* Agence = row[argAgence].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argAgence + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* Adresse = row[argAdresse].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argAdresse + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
* }
*
* try
* {
* Type = row[argType].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argType + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
*
* }
*
* try
* {
* Emploi = row[argEmploi].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argEmploi + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
*
* }
*
* try
* {
* Numero = row[argNumero].ToString();
* }
* catch (ArgumentException e)
* {
* ModelState.AddModelError("Importer", "La colonne " + argNumero + " n'appartient pas à la table Excel, verifier les espaces cachés et les majuscules.");
* return View("ImportLot");
*
* }
*
*
*
* Lot Lot = new Lot()
* {
* NumLot = numLot,
* IDClient = IdClient,
* Compte = Compte,
* NomClient = NomClient,
* SoldeDebiteur = CompteDebiteur.Replace("-", ""),
* TelPortable = TelPortable,
* TelFixe = TelFixe,
* DescIndustry = Agence,
* Adresse = Adresse,
* Type = Type,
* Numero = Numero,
* Emploi = Emploi
*
* };
*/
//On verifie est ce que l client hedha aana menou fel base ouala le
if (LotService.Get(l => l.Compte.Equals(Compte)) != null)
{
//Ken aana menou nshoufou ken identique ouala le
//ken identique manaamlou chay
//ken aaana menou ama fama difference => donc nhotouh a jour
//sinon ken maanesh menou on l'ajouter fel base
//Benesba l ViewData rahom des flag lel affichage
Lot lot = LotService.Get(l => l.Compte.Equals(Compte));
/*
*
* Debug.WriteLine(Compte);
*
* if (lot.Equals(Lot))
* {
* dup++;
* ViewData["nbDup"] = dup;
* if (dup > 0)
* {
* ViewData["dup"] = "1";
* }
* }
* else
* {
* up++;
* ViewData["nbUp"] = up;
* if (up > 0)
* {
* ViewData["up"] = "1";
* }
*
* //lot.NumLot = Lot.NumLot;
*
* lot.NomClient = Lot.NomClient;
* lot.TelPortable = Lot.TelPortable;
* lot.TelFixe = Lot.TelFixe;
* lot.Emploi = Lot.Emploi;
* lot.SoldeDebiteur = Lot.SoldeDebiteur;
* lot.DescIndustry = Lot.DescIndustry;
* lot.Adresse = Lot.Adresse;
* lot.Type = Lot.Type;
* lot.Numero = Lot.Numero;
* lot.PostCode = Lot.PostCode;
*
* LotService.Update(lot);
*
* }
*/
}
else
{
x++;
ViewData["nb"] = x;
if (x > 0)
{
ViewData["noDup"] = "1";
}
//LotService.Add(Lot);
//AffecterClient(Lot, filename);
}
}
/*
* ccList1 = ccList1.Where(c => c.Compte != "").ToList();
* ccList2 = ccList2.Where(c => c.Compte != "").ToList();
*
*/
List <ClientCompte> newList = new List <ClientCompte>();
/*
* foreach(ClientCompte c in ccList1)
* {
*
* if(!ccList2.Contains(c))
* {
*
* newList.Add(c);
*
* }
* else
* {
*
* }
*
* }*/
/*
*
* string path1 = GetFolderName() + "/" + "Diff" + "_MAJ_" + DateTime.Now.ToString("dd.MM.yyyy") + "_" + ((DateTimeOffset)DateTime.UtcNow).ToUnixTimeSeconds() + ".xlsx";
*/
ViewData["nbTotal"] = dt.Rows.Count;
ViewData["finished"] = "1";
LotService.Commit();
/*
* Debug.WriteLine(newList.Count);
*
* GenerateExcel(GenerateDatatableFromJoinedList(newList), path1);
*/
}
}
}
}
else
{
ModelState.AddModelError("Importer", "Le fichier selectionné n'est pas un fichier Excel");
}
}
else
{
ModelState.AddModelError("Importer", "Vous devez sélectionner un fichier");
}
return(View("ImportLot"));
}
}
}
public ActionResult ConsulterClients(string SearchString, string numLot, string currentFilter, string currentNumLot, string currentPage, string CurrentSort, string sortOrder, int?page)
{
using (WakilRecouvContext WakilContext = new WakilRecouvContext())
{
using (UnitOfWork UOW = new UnitOfWork(WakilContext))
{
LotService LotService = new LotService(UOW);
ViewData["list"] = new SelectList(NumLotAllListForDropDown(), "Value", "Text");
ViewData["sortOrder"] = new SelectList(SortOrdrForDropDown(), "Value", "Text");
List <SuiviAffectationViewModel> JoinedList = new List <SuiviAffectationViewModel>();
if (sortOrder != null)
{
//page = 1;
}
else
{
sortOrder = CurrentSort;
}
ViewBag.CurrentSort = sortOrder;
if (page == null)
{
if (currentPage != null)
{
page = int.Parse(currentPage);
}
}
ViewBag.page = page;
if (SearchString != null)
{
// page = 1;
}
else
{
SearchString = currentFilter;
}
ViewBag.currentFilter = SearchString;
if (numLot != null)
{
//page = 1;
}
else
{
numLot = currentNumLot;
}
ViewBag.currentNumLot = numLot;
JoinedList = (from a in LotService.GetAll()
select new SuiviAffectationViewModel
{
LotId = a.LotId
,
Adresse = a.Adresse
,
Compte = a.Compte
,
DescIndustry = a.DescIndustry
,
Emploi = a.Emploi
,
IDClient = a.IDClient
,
NomClient = a.NomClient
,
Numero = a.Numero
,
NumLot = a.NumLot
,
PostCode = a.PostCode
,
SoldeDebiteur = a.SoldeDebiteur
,
TelFixe = a.TelFixe
,
TelPortable = a.TelPortable
,
Type = a.Type
}).ToList();
if (!String.IsNullOrEmpty(numLot))
{
if (numLot.Equals("0") == false)
{
JoinedList = JoinedList.Where(j => j.NumLot.Equals(numLot)).ToList();
}
}
if (!String.IsNullOrEmpty(SearchString))
{
JoinedList = JoinedList.Where(s => s.Adresse.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.Compte.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.DescIndustry.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.IDClient.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.NomClient.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.Numero.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.SoldeDebiteur.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.TelFixe.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower()) ||
s.TelPortable.IfNullOrWhiteSpace("").ToLower().Contains(SearchString.ToLower())
).ToList();
}
switch (sortOrder)
{
case "0":
JoinedList = JoinedList.OrderBy(s => s.NomClient).ToList();
break;
case "1":
JoinedList = JoinedList.OrderByDescending(s => s.SoldeDebiteur).ToList();
break;
case "2":
JoinedList = JoinedList.OrderBy(s => s.SoldeDebiteur).ToList();
break;
default:
break;
}
ViewBag.total = JoinedList.Count();
int pageSize = 10;
int pageNumber = (page ?? 1);
return(View(JoinedList.ToPagedList(pageNumber, pageSize)));
}
}
}