public RetrieveAndWriteData(string quandlCode, DatatableParams datatableParams, ExcelReference currentCellReference)
{
this._quandlCode = quandlCode;
this._datatableParams = datatableParams;
this._currentCellReference = currentCellReference;
this._currentCellRange = Tools.ReferenceToRange(currentCellReference);
}
public DatatableResult GetDatatableList(DatatableParams viewModel)
{
string[] cols = { "Id", "ArabicName", "EnglishName" };
var start = GetStart(viewModel.startString);
var length = GetLength(viewModel.endString);
var lst = GetList();
lst = GetListSearched(viewModel, lst);
lst = GetListSorted(viewModel, cols, lst);
var count = lst.Count();
var data = lst.Skip(start).Take(length).ToList();
var oResult = new DatatableResult
{
draw = viewModel.nDraw
};
foreach (var item in data)
{
oResult.data.Add(item);
}
oResult.recordsTotal = count;
oResult.recordsFiltered = count;
return(oResult);
}
private static Dictionary <string, string> AdditionalInfo(DatatableParams queryParams)
{
var d1 = new Dictionary <string, string>()
{
{ "UDF", "QTABLE" }
};
var d2 = queryParams.QueryParams.Select(entry => new KeyValuePair <string, string>(entry.Key, Utilities.ObjectToHumanString(entry.Value)));
return(d1.Concat(d2).GroupBy(d => d.Key).ToDictionary(d => d.Key, d => d.First().Value));
}
public string GetList(DatatableParams param)
{
try
{
var result = WorkflowtypesRepository.GetDatatableList(param);
return(JsonConvert.SerializeObject(result));
}
catch (Exception e)
{
new ErrorLogsRepository().AddErrorLog(e.ToString(), null, e);
}
return(string.Empty);
}
/// <summary>
/// get collection of WorkflowType filtered based on DatatableParams Search property
/// </summary>
/// <param name="viewModel">Datatable filter/paging/sort details</param>
/// <param name="lst">collection of WorkflowType</param>
/// <returns>filtered collection of WorkflowType</returns>
private IQueryable <WorkflowTypesDTO> GetListSearched(DatatableParams viewModel, IQueryable <WorkflowTypesDTO> lst)
{
if (!string.IsNullOrEmpty(viewModel.search))
{
lst = lst
.Where(a => a.Id.ToString().Contains(viewModel.search) ||
a.ArbicName.ToLower().Contains(viewModel.search.ToLower()) ||
a.EnglishName.ToLower().Contains(viewModel.search.ToLower())
);
}
return(lst);
}
/// <summary>
/// get collection of WorkflowType sorted based on DatatableParams Order , OrderDir properties
/// </summary>
/// <param name="viewModel">Datatable filter/paging/sort details</param>
/// <param name="cols">WorkflowType fields used for sorting</param>
/// <param name="lst">collection of WorkflowType </param>
/// <returns>sorted collection of WorkflowType</returns>
private IQueryable <WorkflowTypesDTO> GetListSorted(DatatableParams viewModel, string[] cols, IQueryable <WorkflowTypesDTO> lst)
{
var orderField = cols[int.Parse(viewModel.order)] == "StatusName" ? "Status" : cols[int.Parse(viewModel.order)];
if (viewModel.orderDir == null || viewModel.orderDir == "asc")
{
lst = lst.OrderBy(orderField);
}
else if (viewModel.orderDir != null && viewModel.orderDir == "desc")
{
lst = lst.OrderByDescending(orderField);
}
else
{
lst = lst.OrderByDescending(a => a.Id);
}
return(lst);
}
public ActionResult records(DatatableParams <Customer> param)
{
var data = new DataRecord <Customer>();
var customers = GetCustomers().AsQueryable();
int totalCount;
totalCount = customers.Count();
customers = param.FilterOnAllSearchable(customers);
customers = param.OrderOnOrderable(customers);
data.draw = param.draw;
data.recordsFiltered = customers.Count();
data.recordsTotal = totalCount;
data.data = customers.Skip(param.start);
return(Json(data, JsonRequestBehavior.AllowGet));
}
private static string Process(ExcelReference currentFormulaCellReference, object rawQuandlCode, object rawColumns, object argName1, object argValue1, object argName2, object argValue2, object argName3, object argValue3, object argName4, object argValue4, object argName5, object argValue5, object argName6, object argValue6)
{
Common.StatusBar.AddMessage(Locale.English.UdfRetrievingData);
var queryParams = new DatatableParams();
try
{
// Parse out all the parameters specified in the UDF.
var quandlCode = Tools.GetStringValue(rawQuandlCode);
var columns = Tools.GetArrayOfValues(rawColumns).Select(s => ((string)s).ToLower()).ToList();
// Add all the query parameters
if (!string.IsNullOrEmpty(QuandlConfig.ApiKey))
{
queryParams.AddInternalParam("api_key", QuandlConfig.ApiKey);
}
if (columns.Count > 0)
{
queryParams.AddInternalParam("qopts.columns", columns);
}
CreateFilterValue(ref argValue1);
CreateFilterValue(ref argValue2);
CreateFilterValue(ref argValue3);
CreateFilterValue(ref argValue4);
CreateFilterValue(ref argValue5);
CreateFilterValue(ref argValue6);
// The user query or additional qopts params
queryParams.AddParam(Tools.GetStringValue(argName1), Tools.GetStringValue(argValue1));
queryParams.AddParam(Tools.GetStringValue(argName2), Tools.GetStringValue(argValue2));
queryParams.AddParam(Tools.GetStringValue(argName3), Tools.GetStringValue(argValue3));
queryParams.AddParam(Tools.GetStringValue(argName4), Tools.GetStringValue(argValue4));
queryParams.AddParam(Tools.GetStringValue(argName5), Tools.GetStringValue(argValue5));
queryParams.AddParam(Tools.GetStringValue(argName6), Tools.GetStringValue(argValue6));
// If the user has not added in any query parameters warn them that its probably not a good idea to continue forward.
if (!ShouldContinueWithoutParams(queryParams.UserParamsGiven))
{
return(Locale.English.AdditionalQueryParamsPleaseAdd);
}
// Pull the metadata first to get the first column name. This is not very efficient as it makes another call just to get one field.
Common.StatusBar.AddMessage(Locale.English.UdfRetrievingData);
queryParams.AddInternalParam("qopts.per_page", 1);
var task = new Web().GetDatatableData(quandlCode, queryParams.QueryParams);
task.Wait();
var firstCellString = task.Result.Columns[0].Name;
// Reset to pull x rows at a time.
queryParams.AddInternalParam("qopts.per_page", RowPullCountMax);
// Pull the data
var retriever = new RetrieveAndWriteData(quandlCode, queryParams, currentFormulaCellReference);
var thready = new Thread(retriever.fetchData);
thready.Priority = ThreadPriority.Normal;
thready.IsBackground = true;
thready.Start();
// Begin the reaping thread. This is necessary to kill off and formula that are functioning for a long time.
var range = Tools.ReferenceToRange(currentFormulaCellReference);
FunctionGrimReaper.AddNewThread(thready, range.Application);
return(Utilities.ValidateEmptyData(firstCellString));
}
catch (DatatableParamError e)
{
Logger.log(e, AdditionalInfo(queryParams));
return(e.Message);
}
catch (System.Exception e)
{
return(Common.HandlePotentialQuandlError(e, true, AdditionalInfo(queryParams)));
}
}
