public String getVoucherPerDepartment() { BanroWebApp.Models.BANROEntities db = new BanroWebApp.Models.BANROEntities(); List <String> lDict = new List <String>(); var Query = from ds in db.t_beneficiaires join vouchers in db.t_bon_commandes on ds.C_id equals vouchers.C_id_bene where vouchers.C_datedeb.EndsWith("/" + DateTime.Now.Year) select new { ds, vouchers }; foreach (var item in Query) { Dictionary <String, Object> dictionary = new Dictionary <string, object>(); String typeB = ""; String idEmployee = ""; String company = ""; // String statusVoucher = ""; String Department = ""; if (!String.IsNullOrEmpty(item.ds.C_mat)) { typeB = "Employee"; } if (!String.IsNullOrEmpty(item.ds.C_id_visitor)) { typeB = "Visitor"; } if (!String.IsNullOrEmpty(item.ds.C_id_partenaire) && String.IsNullOrEmpty(item.ds.C_mat)) { typeB = "Partner"; } if (!String.IsNullOrEmpty(item.ds.C_id_parent)) { typeB = "Children"; } if (typeB.Equals("Employee")) { company = db.t_succursales.Where(s => s.C_id.Equals(item.ds.C_id_succ)).FirstOrDefault().C_name; int currentID = (int)item.ds.C_id_depart; Department = ( db.t_departement.Where(e => e.C_id == currentID) == null ? "" : db.t_departement.Where(e => e.C_id == currentID).FirstOrDefault().C_id_depart ); if (!Department.Equals("")) { lDict.Add(Department); } idEmployee = item.ds.C_mat; } if (typeB.Equals("Partner")) { int id = int.Parse(item.ds.C_id_partenaire); var QueryEmployee = (from ds in db.t_beneficiaires join succ in db.t_succursales on ds.C_id_succ equals succ.C_id where ds.C_id == id select new { ds, succ }).FirstOrDefault(); if (QueryEmployee != null) { company = QueryEmployee.succ.C_name; idEmployee = QueryEmployee.ds.C_mat; int currentID = (int)QueryEmployee.ds.C_id_depart; Department = ( db.t_departement.Where(e => e.C_id == currentID) == null ? "" : db.t_departement.Where(e => e.C_id == currentID).FirstOrDefault().C_id_depart ); if (!Department.Equals("")) { //dictionary.Add("dep", Department); //dictionary.Add("voucher", item.vouchers.C_id_bon); lDict.Add(Department); } } } if (typeB.Equals("Children")) { int id = int.Parse(item.ds.C_id_parent); var QueryEmployee = (from ds in db.t_beneficiaires join succ in db.t_succursales on ds.C_id_succ equals succ.C_id where ds.C_id == id select new { ds, succ }).FirstOrDefault(); if (QueryEmployee != null) { int currentID = (int)QueryEmployee.ds.C_id_depart; Department = ( db.t_departement.Where(e => e.C_id == currentID) == null ? "" : db.t_departement.Where(e => e.C_id == currentID).FirstOrDefault().C_id_depart ); if (!Department.Equals("")) { //dictionary.Add("dep", Department); //dictionary.Add("voucher", item.vouchers.C_id_bon); lDict.Add(Department); } company = QueryEmployee.succ.C_name; idEmployee = QueryEmployee.ds.C_mat; } } // lDict.Add(dictionary); } System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer(); List <Object> lstDep = new List <Object>(); List <Object> dataVouchers = new List <Object>(); List <List <Object> > DataFullGet = new List <List <object> >(); foreach (var item in db.t_departement) { int ctr = 0; lstDep.Add(item.C_id_depart); var sqlDict = from data in lDict where data.Equals(item.C_id_depart) select data; dataVouchers.Add(sqlDict.ToList().Count); } DataFullGet.Add(lstDep); DataFullGet.Add(dataVouchers); return(js.Serialize(DataFullGet)); }
public String getVoucherByBeneficiairy() { BanroWebApp.Models.BANROEntities db = new BanroWebApp.Models.BANROEntities(); List <String> lDict = new List <String>(); List <String> lemployee = new List <String>(); List <String> lspouse = new List <String>(); List <String> lchildren = new List <String>(); List <String> lcasual = new List <String>(); List <String> lcontractor = new List <String>(); List <String> lvisitor = new List <String>(); var Query = from ds in db.t_beneficiaires join vouchers in db.t_bon_commandes on ds.C_id equals vouchers.C_id_bene where vouchers.C_datedeb.EndsWith("/" + DateTime.Now.Year) select new { ds, vouchers }; foreach (var item in Query) { Dictionary <String, Object> dictionary = new Dictionary <string, object>(); String typeB = ""; String idEmployee = ""; String company = ""; // String statusVoucher = ""; String Department = ""; if (!String.IsNullOrEmpty(item.ds.C_mat)) { typeB = "Employee"; } if (!String.IsNullOrEmpty(item.ds.C_id_visitor)) { typeB = "Visitor"; } if (!String.IsNullOrEmpty(item.ds.C_id_partenaire) && String.IsNullOrEmpty(item.ds.C_mat)) { typeB = "Partner"; } if (!String.IsNullOrEmpty(item.ds.C_id_parent)) { typeB = "Children"; } if (typeB.Equals("Employee")) { company = db.t_succursales.Where(s => s.C_id.Equals(item.ds.C_id_succ)).FirstOrDefault().C_name; lemployee.Add(item.vouchers.C_id_bon.ToString()); } if (typeB.Equals("Partner")) { lspouse.Add(item.vouchers.C_id_bon.ToString()); } if (typeB.Equals("Children")) { lchildren.Add(item.vouchers.C_id_bon.ToString()); } if (typeB.Equals("Visitor")) { lvisitor.Add(item.vouchers.C_id_bon.ToString()); } // lDict.Add(dictionary); } System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer(); List <Object> lstCategory = new List <Object>() { "Employees", "Spouses", "Children", "Casual", "Contractor", "Visitor" }; lcasual.Add ( ( db.t_vouchers_casuals.Where(e => e.C_date_casual.EndsWith("/2018")) == null ? "0" : db.t_vouchers_casuals.Where(e => e.C_date_casual.EndsWith("/2018")).ToList().Count.ToString() ) ); lcontractor.Add ( ( db.t_vouchers_contractor.Where(e => e.C_datedeb.EndsWith("/2018")) == null ? "0" : db.t_vouchers_contractor.Where(e => e.C_datedeb.EndsWith("/2018")).ToList().Count.ToString() ) ); List <Object> dataVouchers = new List <Object>() { lemployee.Count, lspouse.Count, lchildren.Count, lcasual.Count, lcontractor.Count, lvisitor.Count }; List <List <Object> > DataFullGet = new List <List <object> >() { lstCategory, dataVouchers }; return(js.Serialize(DataFullGet)); }