//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_);
}
