//Quering agains DB according to user input //>>??? Find GP realization of different queries in model according to user select in view private IQueryable <IDates> ConditionalQuering(DWH_REPLICAEntities db, Parameters pm) { IQueryable <IDates> result = null; switch (pm.entitySelected) { case "ACQ": result = QueriesToDbSet.GetByDate(db.FD_ACQ_D, pm.dateFrom, pm.dateTo); break; case "ECOMM": result = QueriesToDbSet.GetByDate(db.FD_ACQ_M, pm.dateFrom, pm.dateTo); break; case "MT": break; } if (pm.listInclude) { result = QueriesToDbSet.GetByMerchants(result); } return(result); }
public void GetACQbyMonth(DWH_REPLICAEntities db, DateTime dateFrom, DateTime dateTo) { DateTime fromDate = dateFrom.Date; DateTime toDate = dateTo.Date; FD_ACQ = new List <FD_ACQ_DTO>(); var a = from s in db.FD_ACQ_D where DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, 01, 0, 0, 0) == DbFunctions.CreateDateTime(fromDate.Year, fromDate.Month, 01, 0, 0, 0) group s by new { DT_REG = DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, 01, 0, 0, 0) , TYPE_TRANSACTION = s.TYPE_TRANSACTION, PAY_SYSTEM = s.PAY_SYS } into g select new { DT_REG = g.Key.DT_REG, TYPE_TRANSACTION = g.Key.TYPE_TRANSACTION, PAY_SYSTEM = g.Key.PAY_SYSTEM, AMT = g.Sum(s => s.AMT) }; foreach (var b in a) { this.FD_ACQ.Add(new FD_ACQ_DTO { DT_REG = b.DT_REG, TYPE_TRANSACTION = b.TYPE_TRANSACTION, AMT = b.AMT, PAY_SYS = b.PAY_SYSTEM }); } }
public void linqTest(DWH_REPLICAEntities db) { db.Database.CommandTimeout = 180; var a = from s in db.FD_ACQ_D where DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, s.DT_REG.Day, 00, 00, 00) == DbFunctions.CreateDateTime(2016, 09, 01, 00, 00, 00) group s by new { DATE = DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, s.DT_REG.Day, 01, 01, 01) , PAY_SYS = s.PAY_SYS , TRAN_TYPE = s.TYPE_TRANSACTION , MERCH = s.MERCHANT } into g select new { DATE = g.Key.DATE , TRAN_TYPE = g.Key.TRAN_TYPE , PAY_SYS = g.Key.PAY_SYS , MERCH = g.Key.MERCH , AMT = g.Sum(t => t.AMT) }; var b = a.Count(); }
private void DataDelete() { DWH_REPLICAEntities db = new DWH_REPLICAEntities(); var arr = from s in db.parsedValues where s.USER_ID == currentUser select s; db.parsedValues.RemoveRange(arr); db.SaveChanges(); }
public Test(DWH_REPLICAEntities db) { /* * delegateTest(); * lambdaTest(); * linqTest(db); */ }
//Get data from DB with predefine LINQ by paramteters from MVC POSTED parameters class public void GetJSONbyParams(DWH_REPLICAEntities db, DateTime dateFrom, DateTime dateTo, string dateType, bool checkBox, List <string> selectedList, string entityType) { DateTime fromDate = dateFrom.Date; DateTime toDate = dateTo.Date; try { paramsSelected = selectedList; } catch (Exception e) { } FD_ACQ = new List <FD_ACQ_DTO>(); FIELDS_REF.paramsInit(); FIELDS_REF.paramsCheck(paramsSelected); LINQ_QUERIES.db = db; LINQ_QUERIES.fromDate = dateFrom; LINQ_QUERIES.toDate = dateTo; Parameters pm = new Parameters(); pm.dateFrom = dateFrom; pm.dateTo = dateTo; pm.formatSelcted = dateType; pm.listInclude = checkBox; pm.ParametersPublish = selectedList; switch (dateType) { case "DAY": this.FD_ACQ = LINQ_QUERIES.func_d.Invoke(db).ToList(); break; case "MONTH": this.FD_ACQ = LINQ_QUERIES.func_m.Invoke(db).ToList(); break; case "YEAR": this.FD_ACQ = LINQ_QUERIES.func_y.Invoke(db).ToList(); break; } int count = FD_ACQ_GROUP.list_group(pm).Count(); /* * this.FD_ACQ_D = a.ToList(); * this.FD_ACQ_D = compiledQuery_1.Invoke(db, "9290572320").ToList(); * this.FD_ACQ_D = compiledQuery_m.Invoke(db).ToList(); * * foreach (var b in a) * { * this.FD_ACQ_D.Add(new FD_RES { DT_REG = b.DT_REG, TYPE_TRANSACTION = b.TYPE_TRANSACTION, AMT = b.AMT, PAY_SYS = b.PAY_SYS }); * } */ }
private void DataInsert() { if (parsedValues.Count != 0) { foreach (parsedValues pv in parsedValues) { DWH_REPLICAEntities db = new DWH_REPLICAEntities(); db.parsedValues.Add(new DAL.parsedValues { ITEM_ID = pv.ITEM_ID, USER_ID = pv.USER_ID }); db.SaveChanges(); } } }
public Execution(DWH_REPLICAEntities db_ = null, MainModel mm_ = null, Parameters pm_ = null) { if (db_ != null) { this.db = db_; } if (mm_ != null) { mainmodel = mm_; } if (pm_ != null) { parameters = pm_; } }
public virtual IList <T> GetAll(params Expression <Func <T, object> >[] navProp) { List <T> list = new List <T>(); using (var context = new DWH_REPLICAEntities()) { IQueryable <T> dbQ = context.Set <T>(); foreach (Expression <Func <T, object> > navProp_ in navProp) { dbQ = dbQ.Include <T, object>(navProp_); } } return(list); }
//>>!!! remove - not model methods? all rewrote in Execution class //Get data from DB with predefine LINQ by paramteters from JS POSTED parameters public void GetJSONbyParams(DWH_REPLICAEntities db, DateTime dateFrom, DateTime dateTo, string dateType, bool checkBox, string selectedList) { DateTime fromDate = dateFrom.Date; DateTime toDate = dateTo.Date; try { paramsSelected = JsonConvert.DeserializeObject <List <string> >(selectedList); } catch (Exception e) { } FD_ACQ = new List <FD_ACQ_DTO>(); FIELDS_REF.paramsInit(); FIELDS_REF.paramsCheck(paramsSelected); LINQ_QUERIES.db = db; LINQ_QUERIES.fromDate = dateFrom; LINQ_QUERIES.toDate = dateTo; switch (dateType) { case "DAY": this.FD_ACQ = LINQ_QUERIES.func_d.Invoke(db).ToList(); break; case "MONTH": this.FD_ACQ = LINQ_QUERIES.func_m.Invoke(db).ToList(); break; case "YEAR": this.FD_ACQ = LINQ_QUERIES.func_y.Invoke(db).ToList(); break; } /* * this.FD_ACQ_D = a.ToList(); * this.FD_ACQ_D = compiledQuery_1.Invoke(db, "9290572320").ToList(); * this.FD_ACQ_D = compiledQuery_m.Invoke(db).ToList(); * * foreach (var b in a) * { * this.FD_ACQ_D.Add(new FD_RES { DT_REG = b.DT_REG, TYPE_TRANSACTION = b.TYPE_TRANSACTION, AMT = b.AMT, PAY_SYS = b.PAY_SYS }); * } */ }
public static string GetList() { string result = ""; using (DWH_REPLICAEntities db = new DWH_REPLICAEntities()) { try { result = (from s in db.REFMERCHANTS select s).Count().ToString(); } catch (Exception e) { result = "Error : " + e.Message; } } return(result); }
public void GetACQ(DWH_REPLICAEntities db) { DateTime fromDate = new DateTime(2016, 09, 02).Date; DateTime toDate = new DateTime(2016, 09, 03).Date; FD_ACQ = new List <FD_ACQ_DTO>(); //test check //IEnumerable<FD_ACQ_D> a = from s in db.FD_ACQ_D where s.ID >= 496912 && s.ID <= 496977 select s; //aggregate sum //db.FD_ACQ_D.Where(s => DbFunctions.TruncateTime(s.DT_REG) >= fromDate //&& DbFunctions.TruncateTime(s.DT_REG) <= toDate) //.GroupBy(s => new { s.DT_REG, s.TYPE_TRANSACTION }) //.Select(s => new FD_RES { AMT = s.Sum(g => g.AMT), FEE = s.Sum(g => g.FEE) }).ToList(); var a = from s in db.FD_ACQ_D where DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, 01, 0, 0, 0) == DbFunctions.CreateDateTime(fromDate.Year, fromDate.Month, 01, 0, 0, 0) group s by new { DT_REG = DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, 01, 0, 0, 0), TYPE_TRANSACTION = s.TYPE_TRANSACTION, PAY_SYSTEM = s.PAY_SYS } into g select new { DT_REG = g.Key.DT_REG, TYPE_TRANSACTION = g.Key.TYPE_TRANSACTION, PAY_SYSTEM = g.Key.PAY_SYSTEM, AMT = g.Sum(s => s.AMT) }; foreach (var b in a) { this.FD_ACQ.Add(new FD_ACQ_DTO { DT_REG = b.DT_REG, TYPE_TRANSACTION = b.TYPE_TRANSACTION, AMT = b.AMT, PAY_SYS = b.PAY_SYSTEM }); } }
public void excelExportTest() { DWH_REPLICAEntities db = new DWH_REPLICAEntities(); MainModel model = new MainModel(); Parameters parameters_ = new Parameters(); Execution execution = new Execution(); parameters_.entitySelected = "ACQ"; execution.parameters = parameters_; model.entity = QueriesToDbSet.GetByDate(db.FD_ACQ_D, new DateTime(2016, 09, 01, 00, 00, 00), new DateTime(2016, 09, 03, 00, 00, 00)); string codeBase = System.Reflection.Assembly.GetExecutingAssembly().CodeBase; UriBuilder uri = new UriBuilder(codeBase); string path = Directory.GetParent(Directory.GetParent(uri.Path).ToString()).ToString(); path = path + "\\App_Data\\report.xlsx"; ExcelPackage e = new ExcelPackage(new FileInfo(path)); execution.ExcelExport(path, model); }
/// <summary> /// passes context and parameters input from view to parsing method /// stores result in MainModel entity /// </summary> /// <param name="db_"></param> /// <param name="pm_"></param> public void QueryFromParameters(DWH_REPLICAEntities db_, Parameters pm_) { this.mm.entity = ConditionalQuering(db_, pm_); }