public static PopularBlock GetBlock(DataQuery query, int max) {
AnalyticsDataResponse data;
// Fetch the data
try {
data = query.GetCachedData("Popular", new AnalyticsDataOptions {
StartDate = query.CurrentStartDate,
EndDate = query.CurrentEndDate,
Metrics = AnalyticsMetric.Visits,
Dimensions = AnalyticsDimension.PagePath,
Sorting = new AnalyticsSortOptions().AddDescending(AnalyticsMetric.Visits),
MaxResults = 10
});
} catch (Exception ex) {
throw new DashboardException(ex.Message + " (Unable to fetch data for period from " + query.PreviousStartDate.ToString("yyyy-MM-dd") + " to " + query.PreviousEndDate.ToString("yyyy-MM-dd") + " for block \"Popular\")");
}
// Get the root node of the site
IPublishedContent rootNode = UmbracoContext.Current.ContentCache.GetById(query.Site.Id);
List<object> items = new List<object>();
foreach (AnalyticsDataRow row in data.Rows) {
int value = row.GetInt32(AnalyticsMetric.Visits);
string path = row.GetString(AnalyticsDimension.PagePath);
items.Add(new {
url = path,
fullurl = (rootNode == null ? null : rootNode.UrlWithDomain().TrimEnd('/') + path),
visits = new {
raw = value,
text = DashboardContext.Current.Format(value)
}
});
}
return new PopularBlock {
HasData = data.Rows.Length > 0,
Items = items.Take(max).ToArray()
};
}
public void ApiDesign()
{
var query = new DataQuery();
var and = new AndCriterion();
and.Add(new DimensionCriterion() { Name = "JD_CATEGORY", Value = "A" });
and.Add(new DimensionCriterion() { Name = "FREQ", Value = "A" });
and.Add(new DimensionCriterion() { Name = "FREQ", Value = "M" });
and.Add(new TimePeriodCriterion()
{
StartTime = TimePeriod.FromDate(new DateTime(2000, 1, 1)),
EndTime = TimePeriod.FromDate(new DateTime(2000, 12, 31))
});
var or = new OrCriterion();
or.Add(new DataSetCriterion() { Name = "JD014" });
and.Add(or);
query.Criterion = and;
}
/// <summary>
/// Fetches data for the "Visits" block. Calling this method will
/// result on two calls (it not already cached) to the Analytics API.
/// The first call will fetch data for the previous period, while the
/// second call will fetch data for the current period.
/// </summary>
public static VisitsBlock GetBlock(DataQuery query) {
// Declare the data options
AnalyticsDataOptions options = new AnalyticsDataOptions {
Metrics = new[] {
AnalyticsMetric.Visits,
AnalyticsMetric.Pageviews,
AnalyticsMetric.NewVisits,
AnalyticsMetric.AvgTimeOnSite
},
Sorting = new AnalyticsSortOptions().AddDescending(AnalyticsMetric.Visits)
};
switch (query.Type) {
case DataQueryType.Page:
throw new NotImplementedException();
//options.Filters = query.CreateFilterOptionsFromPageUrls();
break;
}
// Fetch the data
AnalyticsDataResponse data1;
AnalyticsDataResponse data2;
query.GetCachedDataPreviousAndCurrent("Visits", out data1, out data2, options);
// Get the first row of each dataset
var row1 = data1.Rows.FirstOrDefault();
var row2 = data2.Rows.FirstOrDefault();
return new VisitsBlock {
HasData = (row1 != null && row1.GetInt32(AnalyticsMetric.Pageviews) > 0) || (row2 != null && row2.GetInt32(AnalyticsMetric.Pageviews) > 0),
Items = new[] {
query.FormatVisitDataInt32(AnalyticsMetric.Visits, row1, row2),
query.FormatVisitDataInt32(AnalyticsMetric.Pageviews, row1, row2),
query.FormatVisitDataInt32(AnalyticsMetric.NewVisits, row1, row2),
query.FormatVisitDataDouble(AnalyticsMetric.AvgTimeOnSite, row1, row2)
}
};
}
protected void btnExport_Click(object sender, EventArgs e)
{
int reportType = Utils.GetIntegerOnly(ddlReportType.SelectedValue);
CsvExport export = new CsvExport();
string fileName = "export";
if(reportType==0){
//Sales summary
QueryData qd = GetSalesSummaryQuery();
UniqueList<string> values = lstSalesSummary.GetAllValues();
//IAqlQuery
IAqlQuery q = new DataQuery(qd);
List<string> fieldNames = q.GetFieldNames();
AqlResultSet rs = q.Execute(true);
while(rs.Read()){
export.AddRow();
export[fieldNames[0]] = rs[0]; //Period
export[fieldNames[1]] = rs[1]; //Number of orders
export[fieldNames[2]] = rs[2]; //Total revenue
export[fieldNames[3]] = rs[3]; // Average order value
export[fieldNames[4]] = rs[4]; //biggest order
int mostSoldProductRevenue = Utils.GetIntegerOnly(rs[5].ToString());
if (mostSoldProductRevenue > 0) {
ProductBase pMostSoldRevenue = WAFContext.Session.GetContent<ProductBase>(mostSoldProductRevenue);
export[fieldNames[5]] = pMostSoldRevenue.Name + " (" + mostSoldProductRevenue + ")"; //MostPopularProductRevenue
} else {
export[fieldNames[5]] = "";
}
int mostSoldProductNumItems = Utils.GetIntegerOnly(rs[5].ToString());
if (mostSoldProductNumItems > 0) {
ProductBase pMostSoldItems = WAFContext.Session.GetContent<ProductBase>(mostSoldProductNumItems);
export[fieldNames[6]] = pMostSoldItems.Name + " (" + mostSoldProductNumItems + ")"; //MostPopularProductNumItems
} else {
export[fieldNames[6]] = "";
}
}
fileName = "sales_summary";
}else if(reportType ==1){
//Sales over time
AqlQuery q = null;
if (txtStartDate.Text.Length > 0) {
DateTime startDT = DateTime.Parse(txtStartDate.Text);
DateTime endDT = DateTime.Now;
if (txtEndDate.Text.Length > 0) {
endDT = DateTime.Parse(txtEndDate.Text);
}
ReportPeriod period = new ReportPeriod();
period.FromDate = startDT;
period.ToDate = endDT;
q = WebshopStatistics.GetGroupSalesData(period, GetDateTimePart());
} else {
q = WebshopStatistics.GetGroupSalesData(this.GetReportPeriod(DefaultReportPeriod.AllTime), GetDateTimePart());
}
List<string> fieldNames = q.GetFieldNames();
AqlResultSet rs = q.Execute(true);
while (rs.Read()) {
export.AddRow();
export["Period"] = GetPeriodDescription(Utils.GetIntegerOnly(rs[0]), Utils.GetIntegerOnly(rs[3])); //DateTimePart
export["Revenue in period"] = ((double)rs[1]).ToString("N2"); //Revenue
export["Number of orders in period"] = rs[2]; //NumOrders
}
fileName = "sales_over_time";
}else if(reportType == 2){
//stock list
AqlQuery q = null;
if (txtStartDate.Text.Length > 0) {
DateTime startDT = DateTime.Parse(txtStartDate.Text);
DateTime endDT = DateTime.Now;
if (txtEndDate.Text.Length > 0) {
endDT = DateTime.Parse(txtEndDate.Text);
}
ReportPeriod period = new ReportPeriod();
period.FromDate = startDT;
period.ToDate = endDT;
q = WebshopStatistics.GetProductStockReportData(period, null);
} else {
q = WebshopStatistics.GetProductStockReportData(this.GetReportPeriod(DefaultReportPeriod.AllTime), null);
}
List<string> fieldNames = q.GetFieldNames();
AqlResultSet rs = q.Execute(true);
while (rs.Read()) {
export.AddRow();
export["NodeId"] = rs[0];
export["Item number"] = rs[1];
export["Product name"] = rs[2];
export["Number in stock"] = rs[3];
}
fileName = "stock_list";
}else if(reportType == 3){
//product sales report
AqlQuery q = null;
if (txtStartDate.Text.Length > 0) {
DateTime startDT = DateTime.Parse(txtStartDate.Text);
DateTime endDT = DateTime.Now;
if (txtEndDate.Text.Length > 0) {
endDT = DateTime.Parse(txtEndDate.Text);
}
ReportPeriod period = new ReportPeriod();
period.FromDate = startDT;
period.ToDate = endDT;
q = WebshopStatistics.GetProductSalesData(period, null);
} else {
q = WebshopStatistics.GetProductSalesData(this.GetReportPeriod(DefaultReportPeriod.AllTime), null);
}
List<string> fieldNames = q.GetFieldNames();
q.IncludeUnpublished = true;
AqlResultSet rs = q.Execute(true);
while (rs.Read()) {
export.AddRow();
export["NodeId"] = rs[0];
export["Item number"] = rs[1];
export["Product name"] = rs[2];
export["Numberof items sold"] = rs[3];
export["Revenue"] = rs[4];
export["Last sale"] = rs[5];
export["First sale"] = rs[6];
}
fileName = "products";
}else if(reportType == 4){
//customers
fileName = "customers";
}
//foreach (ProductBase p in products) {
// export.AddRow();
// export["Name"] = p.Name;
// export["NodeId"] = p.NodeId;
// export["SiteId"] = p.SiteId;
// export["Currency5PriceExVat"] = p.Currency5PriceExVat;
//}
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename="+fileName+"_" + DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year + ".csv");
//Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Default;//Encoding.GetEncoding("iso-8859-1"); //Encoding.GetEncoding(System.Globalization.CultureInfo.CurrentCulture.TextInfo.OEMCodePage); //Encoding.Default;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "text/csv";
Response.Write(export.Export());
Response.End();
}
public static int Main(string[] args)
{
// Explicit scope + data query
ManagementScope scope = new ManagementScope("root/cimv2");
DataQuery query = new DataQuery("select * from Win32_process");
ManagementObjectSearcher searcher =
new ManagementObjectSearcher(query);
ManagementObjectCollection processes = searcher.Get();
foreach (ManagementBaseObject process in processes)
{
Console.WriteLine(process["Name"]);
}
// Implicit scope + data query
DataQuery query2 = new DataQuery("select * from win32_logicaldisk");
ManagementObjectSearcher searcher2 =
new ManagementObjectSearcher(query2);
ManagementObjectCollection disks = searcher2.Get();
foreach (ManagementObject disk in disks)
{
Console.WriteLine(disk["Freespace"]);
}
// Implicit scope + select query
ManagementObjectSearcher searcher3 =
new ManagementObjectSearcher(new SelectQuery("win32_service"));
foreach (ManagementObject service in searcher3.Get())
{
Console.WriteLine(service["Name"]);
}
//Asynchronous query
ManagementOperationWatcher l = new ManagementOperationWatcher();
ObjectHandler objHandler = new ObjectHandler("LaLa");
ObjectHandler objHandler2 = new ObjectHandler("Po");
CompletionHandler completionHandler = new CompletionHandler();
l.ObjectReady += new ObjectReadyEventHandler(objHandler.NewObject);
l.ObjectReady += new ObjectReadyEventHandler(objHandler2.NewObject);
l.Completed += new CompletedEventHandler(completionHandler.Done);
searcher2.Get(l);
while (!completionHandler.IsComplete)
{
System.Threading.Thread.Sleep(1000);
}
//Event watcher
ManagementEventWatcher w = new ManagementEventWatcher(
"select * from __instancemodificationevent within 5 where targetinstance isa 'Win32_Process'");
try {
ManagementBaseObject o = w.WaitForNextEvent();
Console.WriteLine(o["__CLASS"]);
} catch (ManagementException e) {
Console.WriteLine("Exception: " + e.Message);
}
return(0);
}
/// <summary>查询所有相关记录</summary>
/// <param name="query">数据查询参数</param>
/// <param name="length">条数</param>
/// <returns>返回所有实例<see cref="ConnectAccessTokenInfo"/>的详细信息</returns>
public IList <ConnectAccessTokenInfo> FindAll(DataQuery query)
{
return(this.provider.FindAll(query));
}
private string GetReportStoreLotJoin(DataQuery querySet)
{
return(querySet.UseLots ? "LEFT JOIN lots ON store.LotID = lots.ID" : string.Empty);
}
public override string GetReportLotJoin(DataQuery querySet)
{
return(querySet.UseLots ? "LEFT JOIN lots ON operations.LotID = lots.ID" : string.Empty);
}
/// <summary>
/// 验证表
/// </summary>
public static void Check(DataQuery item, string tableName, List <PropertyInfo> listInfo, List <Attribute> listAttribute)
{
try
{
if (item.Config.DesignModel == FastData.Core.Base.Config.CodeFirst)
{
var table = new TableModel();
if (IsExistsTable(item, tableName))
{
//存在表
table = GetTable(item, tableName);
var model = PropertyCache.GetAttributesColumnInfo(tableName, listInfo);
if (model.Count >= table.Column.Count)
{
model.ForEach(p => {
var tempSql = new List <string>();
var info = table.Column.Find(a => a.Name.ToLower() == p.Name.ToLower()) ?? new ColumnModel();
var result = CheckModel.CompareTo <ColumnModel>(info, p);
if (result.IsUpdate)
{
table.Column.Remove(p);
table.Column.Add(result.Item);
UpdateTable(item, result, tableName);
}
});
}
else
{
var tempColumn = new List <ColumnModel>();
tempColumn = table.Column;
tempColumn.ForEach(p => {
var tempSql = new List <string>();
var info = table.Column.Find(a => a.Name.ToLower() == p.Name.ToLower()) ?? new ColumnModel();
var result = CheckModel.CompareTo <ColumnModel>(p, info);
if (result.IsUpdate)
{
table.Column.Remove(p);
table.Column.Add(result.Item);
UpdateTable(item, result, tableName);
}
if (result.IsDelete)
{
model.Remove(p);
UpdateTable(item, result, tableName);
}
});
table.Column = model;
}
var comments = PropertyCache.GetAttributesTableInfo(listAttribute);
if (table.Comments != comments)
{
table.Comments = comments;
UpdateComments(item, table.Comments, tableName);
}
}
else
{
table.Column = PropertyCache.GetAttributesColumnInfo(tableName, listInfo);
table.Name = tableName;
table.Comments = PropertyCache.GetAttributesTableInfo(listAttribute);
AddTable(item, table.Column, tableName);
UpdateComments(item, table.Comments, tableName);
}
}
}
catch (Exception ex)
{
DbLog.LogException(item.Config.IsOutError, item.Config.DbType, ex, string.Format("Check_{0}", tableName), "");
}
}
void setQueries()
{
AqlQuery q = WAFContext.Session.CreateQuery();
q.IncludeUnpublished = true;
q.IncludeHiddenNodes = true;
UniqueList<int> recent = WAFContext.Session.GetRecentlyEditedNodeIds();
if (recent.Count > 0) {
q.Where(Aql.In(AqlContent.NodeId, recent));
} else {
q.Where(AqlContent.NodeId == -1);
}
q.OrderBy(AqlContent.ChangeDate, true);
lstRecent.Query = q;
int userId = WAFContext.Session.UserId;
AqlQuery qr = WAFContext.Session.CreateQuery();
qr.From<ContentBase>();
qr.Select<ContentBase>();
qr.Select(AqlContent.NodeId);
qr.IncludeHiddenNodes = false;
qr.IncludeUnpublished = true;
qr.Where(
AqlContent.AuthorId == userId
| AqlContent.CreatedById == userId
| AqlContent.PublicationApprovedById == userId
| AqlContent.ChangedById == userId
);
qr.OrderBy(AqlContent.ChangeDate, true);
lstMyContent.Query = qr;
QueryData qd = new QueryData();
qd.AddField("FilterNam", DataValueType.ShortStringType);
qd.AddField("Number", DataValueType.ShortStringType);
qd.AddField("Name", DataValueType.ShortStringType);
addRow(qd, "PublicationRequestToYou", RevisionStateRequest.AwaitingPublicationApproval, NodeStateRequest.None, true, "publication requests waiting for your approval. ", "publication request waiting for your approval. ");
addRow(qd, "DeleteRequestToYou", RevisionStateRequest.AwaitingDeleteApproval, NodeStateRequest.None, true, "delete requests waiting for your approval. ", "delete request waiting for your approval. ");
addRow(qd, "UnPublicationRequestToYou", RevisionStateRequest.AwaitingUnPublicationApproval, NodeStateRequest.None, true, "unpublication requests waiting for your approval. ", "unpublication request waiting for your approval. ");
addRow(qd, "PublicationRequestFromYou", RevisionStateRequest.AwaitingPublicationApproval, NodeStateRequest.None, false, "publication requests from you. ", "publication request from you. ");
addRow(qd, "DeleteRequestFromYou", RevisionStateRequest.AwaitingDeleteApproval, NodeStateRequest.None, false, "delete requests from you. ", "delete request from you. ");
addRow(qd, "UnPublicationRequestFromYou", RevisionStateRequest.AwaitingUnPublicationApproval, NodeStateRequest.None, false, "unpublication requests from you. ", "unpublication request from you. ");
addRow(qd, "NodeDeleteRequestToYou", RevisionStateRequest.None, NodeStateRequest.AwaitingDeleteApproval, true, "node delete requests waiting for your approval. ", "delete request waiting for your approval. ");
addRow(qd, "NodeDeleteRequestFromYou", RevisionStateRequest.None, NodeStateRequest.AwaitingDeleteApproval, false, "node delete requests from you. ", "node delete request from you. ");
addRow(qd, "NodeRestoreRequestToYou", RevisionStateRequest.None, NodeStateRequest.AwaitingRestoreApproval, true, "node restore requests waiting for your approval. ", "node restore request waiting for your approval. ");
addRow(qd, "NodeRestoreRequestFromYou", RevisionStateRequest.None, NodeStateRequest.AwaitingRestoreApproval, false, "node restore requests from you. ", "node restore request from you. ");
DataQuery q2 = new DataQuery(qd);
lstTasks.IQuery = q2;
}
public void DataQueryFiltering_WhenFilteringForDatetimeValueBetweenTwoColumnsWhichIsNotProvided_ThenDataAndCountQueriesDontContainWhereClause()
{
DataQuery <Data> query = BuildQuery(b => b.SearchDateToBeWithinColumnPeriod("Column1", "Column2", null));
ShouldNotContainWhere(query);
}
public void DataQueryFiltering_WhenSearchingColumnToBeWithinDateRangeWhichIsNotProvided_ThenDataAndCountQueriesDontContainWhereClause()
{
DataQuery <Data> query = BuildQuery(b => b.SearchColumnDoBeWithinDatePeriod("Column1", null, null));
ShouldNotContainWhere(query);
}
public void DataQueryFiltering_WhenSearchingForValueOnMultipleColumns_ThenDataAndCountQueriesDontContainWhereClause()
{
DataQuery <Data> query = BuildQuery(b => b.SearchValueOnMultipleColumns(default(int?), "Column1", "Column2", "Column3"));
ShouldNotContainWhere(query);
}
/// <summary>
/// 分页数据查询
/// </summary>
/// <param name="sourcename">数据源名</param>
/// <param name="modes">过滤条件集合</param>
/// <param name="fields">数据列名</param>
/// <param name="map">键值对</param>
/// <param name="_orders">排序键值对</param>
/// <param name="_needToGroup">是否要组合列</param>
/// <param name="statisticsEngName">统计英文名</param>
/// <param name="handler">数据处理委托</param>
/// <param name="form">父容器控件</param>
/// <param name="startindex">数据开始行数</param>
/// <param name="pageCount">页数</param>
/// <param name="queryHandle">分页数据查询委托</param>
/// <returns>查询到的数据集</returns>
public static DataSet QueryPageData(String sourcename, List <FilterMode> modes, String[] fields,
Dictionary <GROUPTYPE, String> map, Dictionary <String, bool> _orders, bool _needToGroup,
String statisticsEngName, DelegateMgr.SendBackHandle handler, int startindex, int pageCount, DelegateMgr.QueryHandle queryHandle
//,Dictionary<String,String[]> blockfilters
)
{
String log = null;
if (String.IsNullOrEmpty(sourcename))
{
//res = 0;
return(null);
}
//记录数据源名
log = "数据源: " + sourcename;
if (_needToGroup)
{
log = ",统计名: " + statisticsEngName;
}
log = log + "\r\n";
if (_orders != null)
{
log = log + "排序信息:";
foreach (KeyValuePair <String, bool> keyValuePair in _orders)
{
log = log + "\r\n " + keyValuePair.Key;
if (keyValuePair.Value)
{
log = log + ": 升序";
}
else
{
log = log + ": 升序";
}
}
log = log + "\r\n";
}
log = log + PrintFilterLog(modes);
CommonService.Log(log);
//业务平台调用;提取参数过滤排序信息
if (CommonService.ISCLIENT == ClientType.Config)
{
SetFilterLog(sourcename, modes, fields, map, _orders, _needToGroup, statisticsEngName);
}
List <DataTransmission> requests = new List <DataTransmission>();
DataTransmission dt = new DataTransmission();
List <Expression> filters = new List <Expression>();
//Expression bolckexp = null;
//if (blockfilters != null)
// bolckexp = GetBlockExpression(blockfilters);
dt.DataSource = sourcename;
if (modes != null && modes.Count > 0)
{
Expression e = null;
foreach (FilterMode mode in modes)
{
if (!mode.IsSendFilter)
{
continue;
}
String[] values = mode.Value as String[];
if (values != null)
{
Expression eps = null;
eps = Expression.In(mode.BindParam, values);
if (eps != null)
{
filters.Add(eps);
}
continue;
}
else
{
Expression exp = GetExpression(mode.BindParam, mode.Filter, mode.Value);
if (exp != null)
{
filters.Add(exp);
}
}
}
}
//if (bolckexp != null)
// filters.Add(bolckexp);
dt.Selector = filters;
if (map != null && map.Count > 0)
{
Expression ep = Expression.Computing();
foreach (GROUPTYPE g in map.Keys)
{
switch (g)
{
case GROUPTYPE.GROUP:
ep = ep.GroupBy(map[g]);
break;
case GROUPTYPE.MAX:
ep = ep.Max(map[g]);
break;
case GROUPTYPE.MIN:
ep = ep.Min(map[g]);
break;
default:
break;
}
}
dt.Computing = ep;
}
if (_orders != null && _orders.Count > 0)
{
dt.Orders = GetOrders(_orders);
}
dt.FirstResult = startindex;
//if (!(CommonService.ISCLIENT == ClientType.Client || CommonService.ISCLIENT == ClientType.Config))
//{
// dt.MaxResult = 500;
// dt.IsPaging = true;
//}
dt.Fields = fields;
//dt.MaxResult = pageCount;
dt.MaxResult = int.MaxValue;
dt.IsPaging = true;
//if (CommonService.ISCLIENT != "1"||CommonService.ISCLIENT!="2")
//{
// dt.MaxResult = 500;
// dt.IsPaging = true;
//}
requests.Add(dt);
DataQuery dq = null;
//try
//{
dq = new DataQuery();
//}
//catch
//{
// dq = new DataQuery();
//}
//System.Diagnostics.Stopwatch ss = new System.Diagnostics.Stopwatch();
//ss.Start();
DataSet result = null;
//lock (lockObj)
//{
if (_needToGroup)
{
try
{
dt.DataSource = sourcename;
if (String.IsNullOrEmpty(statisticsEngName))
{
dt.StatisticsEngName = sourcename;
}
else
{
dt.StatisticsEngName = statisticsEngName;
}
dt.Filters = filters;
if (handler != null)
{
dq.QueryStaticsForPaging(dt, handler);
}
else if (queryHandle != null)
{
String threadid = "";
dq.QueryStatics(dt, out threadid, queryHandle);
CommonService.Log("开始执行QueryHandler ID:" + threadid);
CommonContant.QueryThreadIds.Add(threadid);
}
else
{
// form.CurRequestCount++;
result = dq.QueryStatics(dt).Data;
//return null;
}
CommonService.Log("请求数据DataQuery数据：\n 调用方法【ResultData QueryStatics(DataTransmission dt)】成功");
}
catch
{
CommonService.ClearMemory();
return(null);
}
finally
{
}
}
else
{
try
{
if (handler == null)
{
//result = dq.Query(requests);
// String threadid;
result = dq.Query(requests);
}
else
{
if (startindex == 0)
//dq.Query(requests, handler);
{
String threadid = "";
dq.QueryForPagging(dt, out threadid, handler);
CommonContant.QueryThreadIds.Add(threadid);
CommonService.Log(String.Format("发起数据请求: {0}", threadid));
}
//lock (CommonContant.LockRequestid)
//{
// TaskHelper.CloseProgress(CommonContant.Requestid);
// TaskHelper.StartProgress(ref CommonContant.Requestid);
//}
//res = 1;
CommonService.ClearMemory();
return(null);
}
}
catch (Exception e)
{
// res = 0;
CommonService.Log(e);
CommonService.ClearMemory();
return(null);
}
finally
{
CommonService.Log(String.Format("执行到位置{0}", 17));
}
//}
}
//dq.Dispose();
//ss.Stop();
// res = 0;
CommonService.ClearMemory();
return(result);
}
/// <summary>
/// 数据查询
/// </summary>
/// <param name="sourcename">数据源名</param>
/// <param name="modes">过滤条件集合</param>
/// <param name="fields">数据列名</param>
/// <param name="map">键值对</param>
/// <param name="_orders">排序键值对</param>
/// <param name="_needToGroup">是否要组合列</param>
/// <param name="statisticsEngName">统计英文名</param>
/// <param name="handler">数据处理委托</param>
/// <param name="form">父容器控件</param>
/// <returns>查询到的数据集</returns>
public static DataSet QueryData(String sourcename, List <FilterMode> modes, String[] fields,
Dictionary <GROUPTYPE, String> map, Dictionary <String, bool> _orders, bool _needToGroup,
String statisticsEngName, DelegateMgr.QueryHandle handler
//,Dictionary<String,String[]> blockfilters
)
{
String log = null;
if (String.IsNullOrEmpty(sourcename))
{
// res = 0;
return(null);
}
if (!_needToGroup)
{
log = "\n\n\nQueryData请求数据描述开始-------------->\n专题:数据源名 " + sourcename + " ";
}
else
{
log = "\n\n\nQueryData请求数据描述开始-------------->\n专题:统计名名 " + statisticsEngName + " " + _needToGroup + " SourceName: " +
sourcename;
}
log = "\n" + log + "\n请求参数描述:\n" + PrintFilterLog(modes) + "\nQueryData请求数据描述结束-------------->\n\n\n";
if (_orders != null)
{
foreach (KeyValuePair <String, bool> keyValuePair in _orders)
{
log = log + "r\n" + "排序字段: " + keyValuePair.Key + keyValuePair.Value;
}
}
CommonService.Log(log);
try
{
//业务平台调用;提取参数过滤排序信息
if (CommonService.ISCLIENT == ClientType.Config)
{
SetFilterLog(sourcename, modes, fields, map, _orders, _needToGroup, statisticsEngName);
}
}
catch { }
List <DataTransmission> requests = new List <DataTransmission>();
DataTransmission dt = new DataTransmission();
List <Expression> filters = new List <Expression>();
//Expression bolckexp = null;
//if (blockfilters != null)
// bolckexp = GetBlockExpression(blockfilters);
dt.DataSource = sourcename;
if (modes != null && modes.Count > 0)
{
Expression e = null;
foreach (FilterMode mode in modes)
{
if (!mode.IsSendFilter)
{
continue;
}
String[] values = mode.Value as String[];
if (values != null && values.Length >= 2)
{
Expression eps = null;
eps = Expression.In(mode.BindParam, values);
if (eps != null)
{
filters.Add(eps);
}
continue;
}
else
{
Expression exp = GetExpression(mode.BindParam, mode.Filter, mode.Value);
if (exp != null)
{
filters.Add(exp);
}
}
}
}
//if (bolckexp != null)
// filters.Add(bolckexp);
dt.Selector = filters;
if (map != null && map.Count > 0)
{
Expression ep = Expression.Computing();
foreach (GROUPTYPE g in map.Keys)
{
switch (g)
{
case GROUPTYPE.GROUP:
ep = ep.GroupBy(map[g]);
break;
case GROUPTYPE.MAX:
ep = ep.Max(map[g]);
break;
case GROUPTYPE.MIN:
ep = ep.Min(map[g]);
break;
default:
break;
}
}
dt.Computing = ep;
}
if (_orders != null && _orders.Count > 0)
{
dt.Orders = GetOrders(_orders);
}
dt.FirstResult = 0;
if (!(CommonService.ISCLIENT == ClientType.Client || CommonService.ISCLIENT == ClientType.Config))
{
dt.MaxResult = 500;
dt.IsPaging = true;
}
//if (CommonService.ISCLIENT != "1"||CommonService.ISCLIENT!="2")
//{
// dt.MaxResult = 500;
// dt.IsPaging = true;
//}
requests.Add(dt);
DataQuery dq = new DataQuery();
//System.Diagnostics.Stopwatch ss = new System.Diagnostics.Stopwatch();
//ss.Start();
DataSet result = null;
lock (lockObj)
{
if (_needToGroup)
{
try
{
dt.DataSource = sourcename;
if (String.IsNullOrEmpty(statisticsEngName))
{
dt.StatisticsEngName = sourcename;
}
else
{
dt.StatisticsEngName = statisticsEngName;
}
dt.Filters = filters;
CommonService.Log("开始请求数据DataQuery数据：\n 调用方法【ResultData QueryStatics(DataTransmission dt)】");
if (handler == null)
{
result = dq.QueryStatics(dt).Data;
}
else
{
dq.QueryStatics(dt, handler);
//res = 1;
CommonService.ClearMemory();
return(null);
}
CommonService.Log("请求数据DataQuery数据：\n 调用方法【ResultData QueryStatics(DataTransmission dt)】成功");
}
catch
{
// res = 0;
CommonService.Log("请求数据DataQuery数据：\n 调用方法【ResultData QueryStatics(DataTransmission dt)】失败"); CommonService.ClearMemory();
return(null);
}
finally
{
}
}
else
{
try
{
if (handler == null)
{
result = dq.Query(requests);
}
else
{
dq.Query(requests, handler);
CommonService.ClearMemory();
return(null);
}
}
catch
{
CommonService.ClearMemory();
return(null);
}
finally
{
}
}
}
//dq.Dispose();
//ss.Stop();
//res = 0;
CommonService.ClearMemory();
return(result);
}
public static LazyListModel <Transfer> GetAll(DataQuery dataQuery)
{
AddPartnerLocationFilters(ref dataQuery, false);
return(BusinessDomain.DataAccessProvider.GetAllTransfers <Transfer> (dataQuery));
}
public DataQuery Actualizacion(int Id, int IdConsultoria, string NombEntregable, string Descripcion, DateTime FechEntrega,
string Duracion, char EstadoEntregable, string UrlEntregable, string ObservacionEntregable, double MontoEntregable, string IdAspirante,
string LoginUsuario, TipoActualizacion OpcionActualizacion)
{
DataQuery objResultado = new DataQuery();
try
{
string StrCommand = "";
switch (OpcionActualizacion)
{
case TipoActualizacion.Adicionar:
StrCommand = "SP_TB_CONSULTORIA_ENTREGABLE_INSERT";
break;
case TipoActualizacion.Actualizar:
StrCommand = "SP_TB_CONSULTORIA_ENTREGABLE_UPDATE";
break;
case TipoActualizacion.Eliminar:
StrCommand = "";
break;
case TipoActualizacion.No_Definida:
objResultado.CodigoError = -1;
objResultado.MensajeError = "Opcion de Actualizacion No Definida. Objeto COpcionesSistemas. Metodo Actualizacion";
//return objResultado;
break;
}
ObjConnection = new SqlConnection(_ConexionData);
ObjCommand = new SqlCommand(StrCommand, ObjConnection);
ObjParam = new SqlParameter();
ObjCommand.CommandType = CommandType.StoredProcedure;
if (OpcionActualizacion == TipoActualizacion.Adicionar)
{
ObjParam = ObjCommand.Parameters.Add("@ID", SqlDbType.Int, 0);
ObjParam.Direction = ParameterDirection.Output;
}
else
{
ObjCommand.Parameters.AddWithValue("@ID", Id);
}
ObjCommand.Parameters.AddWithValue("@ID_CONSULTORIA", IdConsultoria);
ObjCommand.Parameters.AddWithValue("@DS_ENTREGABLE", NombEntregable);
ObjCommand.Parameters.AddWithValue("@DS_DESCRIPCION_ENT", Descripcion);
ObjCommand.Parameters.AddWithValue("@FECH_ENTREGA_ENT", FechEntrega);
ObjCommand.Parameters.AddWithValue("@DS_DURACION_ENT", Duracion);
ObjCommand.Parameters.AddWithValue("@CD_ESTADO_ENTREGABLE", EstadoEntregable);
ObjCommand.Parameters.AddWithValue("@URL_ENTREGABLE", UrlEntregable);
ObjCommand.Parameters.AddWithValue("@DS_OBSERVACION_ENTREGABLE", ObservacionEntregable);
ObjCommand.Parameters.AddWithValue("@NM_MONTO_ENTREGABLE", MontoEntregable);
ObjCommand.Parameters.AddWithValue("@ID_ASPIRANTE", IdAspirante);
ObjCommand.Parameters.AddWithValue("@LOGIN_USUARIO", LoginUsuario);
ObjParam = ObjCommand.Parameters.Add("@FILAS_AFECTADAS", SqlDbType.Int, 0);
ObjParam.Direction = ParameterDirection.Output;
ObjParam = ObjCommand.Parameters.Add("@NumeroError", SqlDbType.Decimal);
ObjParam.Precision = 38;
ObjParam.Scale = 0;
ObjParam.Direction = ParameterDirection.Output;
ObjParam = ObjCommand.Parameters.Add("@MensajeError", SqlDbType.NVarChar, 4000);
ObjParam.Direction = ParameterDirection.Output;
ObjConnection.Open();
ObjCommand.ExecuteNonQuery();
objResultado.CodigoAuxiliar = (object)ObjCommand.Parameters["@ID"].Value;
objResultado.FilasAfectadas = (int)ObjCommand.Parameters["@FILAS_AFECTADAS"].Value;
objResultado.CodigoError = (decimal)ObjCommand.Parameters["@NumeroError"].Value;
objResultado.MensajeError = (string)ObjCommand.Parameters["@MensajeError"].Value;
ObjConnection.Close();
if (ObjConnection.State != ConnectionState.Closed)
{
ObjConnection.Close();
}
}
catch (Exception ex)
{
objResultado.CodigoError = -1;
objResultado.MensajeError = ex.Message;
}
return(objResultado);
}
public void DataQueryFiltering_WhenSearchingForTextToBeEqualEmptyString_ThenDataAndCountQueriesDontContainWhereClause()
{
DataQuery <Data> query = BuildQuery(b => b.SearchTextToBeEqual("Column1", string.Empty));
ShouldNotContainWhere(query);
}
/// <summary>分页函数</summary>
/// <param name="startIndex">开始行索引数,由0开始统计</param>
/// <param name="pageSize">页面大小</param>
/// <param name="whereClause">WHERE 查询条件</param>
/// <param name="orderBy">ORDER BY 排序条件</param>
/// <param name="rowCount">行数</param>
/// <returns>返回一个列表实例<see cref="BugCategoryQueryInfo"/></returns>
public IList <BugCategoryQueryInfo> GetQueryObjectPaging(int startIndex, int pageSize, DataQuery query, out int rowCount)
{
Dictionary <string, object> args = new Dictionary <string, object>();
args.Add("WhereClause", query.GetWhereSql(new Dictionary <string, string>()
{
{ "CategoryIndex", "LIKE" }, { "Status", "IN" }
}));
args.Add("OrderBy", query.GetOrderBySql(" ModifiedDate DESC "));
args.Add("StartIndex", startIndex);
args.Add("PageSize", pageSize);
IList <BugCategoryQueryInfo> list = this.ibatisMapper.QueryForList <BugCategoryQueryInfo>(StringHelper.ToProcedurePrefix(string.Format("{0}_GetQueryObjectPaging", this.tableName)), args);
rowCount = Convert.ToInt32(this.ibatisMapper.QueryForObject(StringHelper.ToProcedurePrefix(string.Format("{0}_GetRowCount", this.tableName)), args));
return(list);
}
public void DataQueryFiltering_WhenSearchingForTextToBeEqualValueWhichIsNotProvided_ThenDataAndCountQueriesDontContainWhereClause()
{
DataQuery <Data> query = BuildQuery(b => b.SearchTextToBeEqual("Column1", null));
ShouldNotContainWhere(query);
}
static void Main(string[] args)
{
//test limiter
//TestLimiter();
//initialize a new limiter with 10 concurrent and 10 requests per second.
Limiter limiter = new Limiter(TimeSpan.FromSeconds(1), 10, 10);
string username = "******";
string password = "******";
//first, initialize a service, its the query provider.
var service = new Google.GData.Analytics.AnalyticsService("AnalyticsReader");
//you can also use service.setUserCredentials(username,password)
service.Credentials = new Google.GData.Client.GDataCredentials(username, password);
//first, query for all accounts
var accountquery = new Google.GData.Analytics.AccountQuery();
accountquery.NumberToRetrieve = 10000; //Maximum
limiter.Enter();
var accountresult = service.Query(accountquery);
limiter.Exit();
//for each account, retrieve records
foreach (AccountEntry account in accountresult.Entries)
{
//get the timezone for the account, as all data is saved based on that timezone.
Property timezone = account.Properties.Where(i => i.Name == "ga:timezone").FirstOrDefault();
Console.WriteLine(
"\nProfile Title = " + account.Title.Text +
"\nProfile ID = " + account.ProfileId.Value +
"\nTimeZone = " + timezone.Value);
TzTimeZone tzinfo = null;
if (timezone != null)
{
tzinfo = TzTimeZone.GetTimeZone(timezone.Value);
}
//retrieve analytics data
DataQuery query = new DataQuery();
query.Ids = account.ProfileId.Value;
//Data
//dimensions are the "group by"
query.Dimensions = "ga:date,ga:hour,ga:hostname,ga:country,ga:keyword,ga:source,ga:referralPath";
//metrics are the values
query.Metrics = "ga:bounces,ga:newVisits,ga:pageviews,ga:timeOnSite,ga:visits";
//start from yesterday
query.GAStartDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
//until today
query.GAEndDate = DateTime.Now.ToString("yyyy-MM-dd");
query.StartIndex = 1;
//Maximum allowed by Quota.
query.NumberToRetrieve = 10000;
limiter.Enter();
DataFeed dataFeedVisits = service.Query(query);
limiter.Exit();
//process each record returned
foreach (DataEntry dentry in dataFeedVisits.Entries)
{
var line = new Dictionary <string, string>();
//insert into directory for each processing
for (int i = 0; i < dentry.Metrics.Count; i++)
{
line[dentry.Metrics[i].Name] = dentry.Metrics[i].Value;
}
for (int i = 0; i < dentry.Dimensions.Count; i++)
{
line[dentry.Dimensions[i].Name] = dentry.Dimensions[i].Value;
}
//get the UTC datetime from the ga:date + ga:hour + profile timezone combination
DateTime date = DateTime.ParseExact(line["ga:date"], "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
int hour = Convert.ToInt32(line["ga:hour"]);
date = date.AddHours(hour);
line["dateutc"] = ConvertToUtc(date, tzinfo).ToString();
//dump to console all data found
Console.Write("UTC: {0}, ", line["dateutc"]);
for (int i = 0; i < dentry.Dimensions.Count; i++)
{
Console.Write("{0}: {1}, ", dentry.Dimensions[i].Name, dentry.Dimensions[i].Value);
}
for (int i = 0; i < dentry.Metrics.Count; i++)
{
Console.Write("{0}: {1}, ", dentry.Metrics[i].Name, dentry.Metrics[i].Value);
}
Console.WriteLine();
}
}
}
public void DataQueryFiltering_WhenSearchingForNonNullValues_ThenDataAndCountQueriesContainsWhereClauseWithProperCondition()
{
DataQuery <Data> query = BuildQuery(b => b.WhereIsNotNull("Column1"));
ShouldContainWhere(query, "WHERE Column1 IS NOT NULL");
}
/// <summary>
/// 获取表结构
/// </summary>
private static TableModel GetTable(DataQuery item, string tableName)
{
var result = new TableModel();
result.Column = result.Column ?? new List <ColumnModel>();
using (var db = new DataContext(item.Key))
{
var param = new List <DbParameter>();
if (item.Config.DbType == DataDbType.Oracle)
{
#region oracle
//参数
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "name";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
//表
var sql = "select a.table_name,comments from user_tables a inner join user_tab_comments b on a.TABLE_NAME = b.TABLE_NAME and a.table_name = :name";
var dic = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList[0];
result.Name = dic.GetValue("table_name").ToStr();
result.Comments = dic.GetValue("comments").ToStr();
//列
sql = string.Format(@"select a.column_name,data_type,data_length,b.comments,
(select count(0) from user_cons_columns aa, user_constraints bb where aa.constraint_name = bb.constraint_name and bb.constraint_type = 'P' and bb.table_name = :name and aa.column_name = a.column_name) iskey,
nullable,data_precision,data_scale
from user_tab_columns a inner join user_col_comments b
on a.table_name =:name and a.table_name = b.table_name and a.column_name = b.column_name order by a.column_id asc");
var dicList = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList;
dicList.ForEach(a => {
var model = new ColumnModel();
model.Comments = a.GetValue("comments").ToStr();
model.DataType = a.GetValue("data_type").ToStr();
model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false;
model.IsNull = a.GetValue("nullable").ToStr() == "Y" ? true : false;
model.Length = a.GetValue("data_length").ToStr().ToInt(0);
model.Name = a.GetValue("column_name").ToStr();
model.Precision = a.GetValue("data_precision").ToStr().ToInt(0);
model.Scale = a.GetValue("data_scale").ToStr().ToInt(0);
result.Column.Add(model);
});
#endregion
}
if (item.Config.DbType == DataDbType.MySql)
{
#region MySql
//参数
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "name";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
//表
var sql = "select table_name,table_comment count from information_schema.tables where upper(table_name)=?name";
var dic = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList[0];
result.Name = dic.GetValue("table_name").ToStr();
result.Comments = dic.GetValue("table_comment").ToStr();
//列
sql = string.Format(@"select column_name,data_type,character_maximum_length,column_comment,
(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a where upper(TABLE_NAME)=?name and constraint_name='PRIMARY' and c.column_name=a.column_name) iskey,
is_nullable,numeric_precision,numeric_scale from information_schema.columns c where upper(table_name)=?name order by ordinal_position asc");
var dicList = FastRead.ExecuteSql(sql, param.ToArray()) ?? new List <Dictionary <string, object> >();
dicList.ForEach(a => {
var model = new ColumnModel();
model.Comments = a.GetValue("column_comment").ToStr();
model.DataType = a.GetValue("data_type").ToStr();
model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false;
model.IsNull = a.GetValue("is_nullabl").ToStr() == "YES" ? true : false;
model.Length = a.GetValue("character_maximum_length").ToStr().ToInt(0);
model.Name = a.GetValue("column_name").ToStr();
model.Precision = a.GetValue("numeric_precision").ToStr().ToInt(0);
model.Scale = a.GetValue("numeric_scale").ToStr().ToInt(0);
result.Column.Add(model);
});
#endregion
}
if (item.Config.DbType == DataDbType.SqlServer)
{
#region SqlServer
//参数
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "name";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
//表
var sql = "select name,(select top 1 value from sys.extended_properties where major_id=object_id(a.name) and minor_id=0) as value from sys.objects a where type = 'U'and upper(name) = @name";
var dic = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList[0];
result.Name = dic.GetValue("name").ToStr();
result.Comments = dic.GetValue("value").ToStr();
//列
sql = string.Format(@"select a.name,(select top 1 name from sys.systypes c where a.xtype=c.xtype) as type ,
length,b.value,(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='@name' and COLUMN_NAME=a.name) as iskey,
isnullable,prec,scale
from syscolumns a left join sys.extended_properties b
on major_id = id and minor_id = colid and b.name ='MS_Description'
where a.id=object_id('@name') order by a.colid asc");
var dicList = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList;
dicList.ForEach(a => {
var model = new ColumnModel();
model.Comments = a.GetValue("value").ToStr();
model.DataType = a.GetValue("type").ToStr();
model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false;
model.IsNull = a.GetValue("isnullable").ToStr() == "1" ? true : false;
model.Length = a.GetValue("length").ToStr().ToInt(0);
model.Name = a.GetValue("name").ToStr();
model.Precision = a.GetValue("prec").ToStr().ToInt(0);
model.Scale = a.GetValue("scale").ToStr().ToInt(0);
result.Column.Add(model);
});
#endregion
}
return(result);
}
}
private static void ShouldNotContainWhere(DataQuery <Data> query)
{
query.SelectQuery.ShouldNotContain("WHERE");
}
public override string GetReportLotGroup(DataQuery querySet)
{
return(querySet.UseLots ? @", operations.Lot, lots.SerialNo, lots.EndDate, lots.ProductionDate, lots.Location" : string.Empty);
}
private static void ShouldContainWhere(DataQuery <Data> query, string clause)
{
query.SelectQuery.ShouldContain(clause);
}
private string GetReportStoreLotGroup(DataQuery querySet)
{
return(querySet.UseLots ? @", store.Lot, lots.SerialNo, lots.EndDate, lots.ProductionDate, lots.Location" : string.Empty);
}
public void DataQueryFiltering_WhenSearchingForBoolValueWhichIsNotProvided_ThenDataAndCountQueriesDontContainWhereClause()
{
DataQuery <Data> query = BuildQuery(b => b.Search("Column1", default(bool?)));
ShouldNotContainWhere(query);
}
// -------------------------------------------------------
// 自定义功能
// -------------------------------------------------------
#region 函数:GetPaging(int startIndex, int pageSize, DataQuery query, out int rowCount)
/// <summary>分页函数</summary>
/// <param name="startIndex">开始行索引数,由0开始统计</param>
/// <param name="pageSize">页面大小</param>
/// <param name="query">数据查询参数</param>
/// <param name="rowCount">行数</param>
/// <returns>返回一个列表实例</returns>
public IList <ConnectAccessTokenInfo> GetPaging(int startIndex, int pageSize, DataQuery query, out int rowCount)
{
return(this.provider.GetPaging(startIndex, pageSize, query, out rowCount));
}
public DataQuery Acutalizacion(int Id, string AreaEmpresa,
int OpcionConsulta, string LoginUsuario, TipoActualizacion OpcionActualizacion)
{
DataQuery objResultado = new DataQuery();
try
{
string StrCommand = "";
switch (OpcionActualizacion)
{
case TipoActualizacion.Adicionar:
StrCommand = " ";
break;
case TipoActualizacion.Actualizar:
StrCommand = " ";
break;
case TipoActualizacion.Eliminar:
StrCommand = " ";
break;
case TipoActualizacion.No_Definida:
objResultado.CodigoError = -1;
objResultado.MensajeError = "Opcion de Actualizacion No Definida. Objeto COpcionesSistemas. Metodo Actualizacion";
//return objResultado;
break;
}
ObjConnection = new SqlConnection(_ConexionData);
ObjCommand = new SqlCommand(StrCommand, ObjConnection);
ObjParam = new SqlParameter();
ObjCommand.CommandType = CommandType.StoredProcedure;
if (OpcionActualizacion == TipoActualizacion.Adicionar)
{
ObjParam = ObjCommand.Parameters.Add("@ID", SqlDbType.Int, 0);
ObjParam.Direction = ParameterDirection.Output;
}
else
{
ObjCommand.Parameters.AddWithValue("@ID", Id);
}
ObjCommand.Parameters.AddWithValue("@DS_AREA", AreaEmpresa);
ObjCommand.Parameters.AddWithValue("@LOGIN_USUARIO", LoginUsuario);
ObjParam = ObjCommand.Parameters.Add("@FILAS_AFECTADAS", SqlDbType.Int, 0);
ObjParam.Direction = ParameterDirection.Output;
ObjParam = ObjCommand.Parameters.Add("@NumeroError", SqlDbType.Decimal);
ObjParam.Precision = 38;
ObjParam.Scale = 0;
ObjParam.Direction = ParameterDirection.Output;
ObjParam = ObjCommand.Parameters.Add("@MensajeError", SqlDbType.NVarChar, 4000);
ObjParam.Direction = ParameterDirection.Output;
ObjConnection.Open();
ObjCommand.ExecuteNonQuery();
objResultado.CodigoAuxiliar = (object)ObjCommand.Parameters["@ID"].Value;
objResultado.FilasAfectadas = (int)ObjCommand.Parameters["@FILAS_AFECTADAS"].Value;
objResultado.CodigoError = (decimal)ObjCommand.Parameters["@NumeroError"].Value;
objResultado.MensajeError = (string)ObjCommand.Parameters["@MensajeError"].Value;
ObjConnection.Close();
if (ObjConnection.State != ConnectionState.Closed)
{
ObjConnection.Close();
}
}
catch (Exception ex)
{
objResultado.CodigoError = -1;
objResultado.MensajeError = ex.Message;
}
return(objResultado);
}
private Dictionary<string, Dictionary<string, object>> GetDataSet(DataQuery query)
{
string setName = query.Collection.Name;
Dictionary<string, Dictionary<string, object>> set = _data[setName];
if (set == null) {
set = new Dictionary<string, Dictionary<string, object>>();
_data[setName] = set;
}
return set;
}
private static object GetCellData(AnalyticsDataCell cell, DataQuery query) {
object raw;
object value;
switch (cell.Column.DataType) {
case "dINTEGER":
raw = cell.GetInt32();
value = cell.GetInt32().ToString("N0", query.Context.Culture);
break;
case "dDOUBLE":
raw = cell.GetDouble();
value = cell.GetDouble().ToString("N2", query.Context.Culture);
break;
default:
raw = cell.GetString();
value = cell.GetString() + " (" + cell.Column.ColumnType + ")";
break;
}
return new {
raw, value
};
}
//Connect to the database, query, record data and then DataQuery is be destroyed
public static void Init()
{
//Remember to assert the loading of database and use try around the queries
DataQuery query = new DataQuery();
hasLoaded = true; //This will go in the final area postceeding try-catch
}
/// <summary>
/// Load Columns names with Query.
/// </summary>
protected void LoadFromQuery()
{
string queryName = hdnClassNames.Value;
var q = new DataQuery(queryName);
if (q.HasResults())
{
DataView dv = q.Result.Tables[0].DefaultView;
string[,] mLI = new string[dv.Table.Columns.Count, 2];
int i = 0;
foreach (DataColumn dc in dv.Table.Columns)
{
mLI[i, 0] = i.ToString();
mLI[i, 1] = dc.ColumnName;
i++;
}
ItemSelection1.LeftItems = mLI;
ItemSelection1.fill();
}
}
public static void Delete(DataQuery filter)
{
BusinessDomain.DataAccessProvider.DeleteECRReceipts(filter);
}
public abstract DataQueryResult ExecuteReport(DataQuery dataQuery);
public static LineChartBlock GetBlock(DataQuery query) {
// Declare the data options
AnalyticsDataOptions options = new AnalyticsDataOptions {
StartDate = query.CurrentStartDate,
EndDate = query.CurrentEndDate,
Metrics = AnalyticsMetric.Visits + AnalyticsMetric.Pageviews
};
if (query.Days <= 1) {
options.Dimensions = AnalyticsDimension.Hour;
} else if (query.Days <= 31) {
options.Dimensions = AnalyticsDimension.Date;
//options.Sorting = new AnalyticsSortOptions().AddAscending(AnalyticsDimension.Date);
} else {
options.Dimensions = AnalyticsDimension.YearWeek;
}
// Add any extra options?
switch (query.Type) {
case DataQueryType.Page:
throw new NotImplementedException();
//options.Filters = query.CreateFilterOptionsFromPageUrls();
break;
}
// Fetch the data
AnalyticsDataResponse data;
try {
data = query.GetCachedData("LineChart", options);
} catch (Exception ex) {
throw new DashboardException(ex.Message + " (Unable to fetch data for period from " + query.PreviousStartDate.ToString("yyyy-MM-dd") + " to " + query.PreviousEndDate.ToString("yyyy-MM-dd") + " for block \"LineChart\")");
}
object ddata;
if (data.Rows.Length == 0) {
ddata = new {
columns = new object[0],
rows = new object[0]
};
} else {
object[] columns = new object[data.ColumnHeaders.Length];
object[] rows = new object[data.Rows.Length];
for (int i = 0; i < data.ColumnHeaders.Length; i++) {
var column = data.ColumnHeaders[i];
columns[i] = new {
alias = column.Name.Substring(3),
label = query.Context.Translate(column.Name)
};
}
for (int i = 0; i < data.Rows.Length; i++) {
AnalyticsDataRow row = data.Rows[i];
object[] rowdata = new object[row.Cells.Length];
for (int j = 0; j < row.Cells.Length; j++) {
rowdata[j] = GetCellData(row.Cells[j], query);
}
rows[i] = rowdata;
}
ddata = new { columns, rows };
}
var datasets = new object[] {
new {
label = DashboardContext.Current.Translate(AnalyticsMetric.Pageviews),
fillColor = "#35353d",
strokeColor = "#35353d"
},
new {
label = DashboardContext.Current.Translate(AnalyticsMetric.Visits),
fillColor = "red",//"rgba(141, 146, 157, 1)",
strokeColor = "red"//"rgba(141, 146, 157, 1)"
}
};
object[] items = (
from row in data.Rows
let first = row.Cells[0]
select (object) new {
label = query.FormatCell(first),
visits = query.FormatInt32(AnalyticsMetric.Visits, row),
pageviews = query.FormatInt32(AnalyticsMetric.Pageviews, row)
}
).ToArray();
return new LineChartBlock {
HasData = data.Rows.Any(x => x.GetInt32(AnalyticsMetric.Visits) > 0),
Data = ddata,
Datasets = datasets,
Items = items.ToArray(),
Debug = new {
query = data.Query.ToJson(),
days = query.Days
}
};
}
public override DataQueryResult ExecuteReport(DataQuery dataQuery)
{
return(BusinessDomain.DataAccessProvider.ReportPartners(dataQuery));
}
/// <summary>
/// Gets the where condition for the selected object type.
/// </summary>
/// <param name="selectedObjectType">Selected object type</param>
public string GetWhereCondition(string selectedObjectType)
{
if (Object != null)
{
string where = null;
var relatedObject = RelatedObject;
if (relatedObject != null)
{
// Get the site name
var relatedTypeInfo = relatedObject.TypeInfo;
if (relatedTypeInfo.SiteIDColumn != ObjectTypeInfo.COLUMN_NAME_UNKNOWN)
{
if (siteSelector.DropDownSingleSelect.Items.Count == 0)
{
siteSelector.Value = SiteContext.CurrentSiteID;
}
if (siteSelector.HasData)
{
// Set the site name for registration
int selectedSiteId = ValidationHelper.GetInteger(siteSelector.Value, 0);
if (selectedSiteId >= 0)
{
string siteQuery = new DataQuery(relatedTypeInfo.ObjectType, null)
.Column(relatedTypeInfo.IDColumn)
.Where(SqlHelper.GetSiteIDWhereCondition(relatedTypeInfo.SiteIDColumn, selectedSiteId))
.QueryText;
// Where condition for the left object
string rightWhere = ObjectRelationshipInfoProvider.GetWhereCondition(ObjectID, ObjectType, 0, false, true, selectedObjectType);
rightWhere += " AND RelationshipLeftObjectID IN (" + siteQuery + ")";
// Where condition for the left object
string leftWhere = ObjectRelationshipInfoProvider.GetWhereCondition(ObjectID, ObjectType, 0, true, false, selectedObjectType);
leftWhere += " AND RelationshipRightObjectID IN (" + siteQuery + ")";
// --- Add site conditions here
where = SqlHelper.AddWhereCondition(leftWhere, rightWhere, "OR");
}
}
}
}
if (String.IsNullOrEmpty(where))
{
// Get using regular where
where = ObjectRelationshipInfoProvider.GetWhereCondition(ObjectID, ObjectType, 0, true, true, selectedObjectType);
}
return where;
}
return null;
}
// -------------------------------------------------------
// 自定义功能
// -------------------------------------------------------
#region 属性:GetPaging(int startIndex, int pageSize, DataQuery query, out int rowCount)
/// <summary>分页函数</summary>
/// <param name="startIndex">开始行索引数,由0开始统计</param>
/// <param name="pageSize">页面大小</param>
/// <param name="query">数据查询参数</param>
/// <param name="rowCount">记录行数</param>
/// <returns>返回一个列表实例<see cref="IMemberInfo"/></returns>
public IList <IMemberInfo> GetPaging(int startIndex, int pageSize, DataQuery query, out int rowCount)
{
return(provider.GetPaging(startIndex, pageSize, query, out rowCount));
}
public static DevicesBlock GetBlock(DataQuery query) {
// Declare the data options
var options = new AnalyticsDataOptions {
StartDate = query.CurrentStartDate,
EndDate = query.CurrentEndDate,
Metrics = AnalyticsMetric.Visits + AnalyticsMetric.Pageviews,
Dimensions = AnalyticsDimension.DeviceCategory
};
switch (query.Type) {
case DataQueryType.Page:
throw new NotImplementedException();
//options.Filters = query.CreateFilterOptionsFromPageUrls();
break;
}
// Fetch the data
AnalyticsDataResponse data;
try {
data = query.GetCachedData("Devices", options);
} catch (Exception ex) {
throw new DashboardException(ex.Message + " (Unable to fetch date for period from " + query.PreviousStartDate.ToString("yyyy-MM-dd") + " to " + query.PreviousEndDate.ToString("yyyy-MM-dd") + " for block \"Devices\")");
}
// Filter out any rows where the the "ga:visits" value is either "NaN" or "0"
AnalyticsDataRow[] rows = data.Rows.Where(x => x.GetString(AnalyticsMetric.Visits) != "NaN" && x.GetString(AnalyticsMetric.Visits) != "0").ToArray();
// Calculate the total visits and pageviews
int totalVisits = 0;
int totalPageviews = 0;
foreach (AnalyticsDataRow row in rows) {
totalVisits += row.GetInt32(AnalyticsMetric.Visits);
totalPageviews += row.GetInt32(AnalyticsMetric.Pageviews);
}
if (data.Rows.Length == 0) {
return new DevicesBlock {
HasData = false,
Items = new object[0]
};
}
List<object> items = new List<object>();
// Iterate through the rows
foreach (AnalyticsDataRow row in rows) {
int visits = row.GetInt32(AnalyticsMetric.Visits);
int pageviews = row.GetInt32(AnalyticsMetric.Pageviews);
double visitsPercent = visits / (double) totalVisits * 100;
double pageviewsPercent = pageviews / (double) totalPageviews * 100;
string category = row.GetString(AnalyticsDimension.DeviceCategory);
items.Add(new {
category = category,
text = query.Context.Translate("device_" + category),
visits = new {
raw = visits,
text = query.Context.Format(visits),
percent = new {
raw = visitsPercent,
text = query.Context.Format(visitsPercent) + "%"
}
},
pageviews = new {
raw = pageviews,
text = DashboardContext.Current.Format(pageviews),
percent = new {
raw = pageviewsPercent,
text = query.Context.Format(pageviewsPercent) + "%"
}
}
});
}
return new DevicesBlock {
HasData = items.Count > 0,
Items = items.ToArray()
};
}
/// <summary>
/// 修改表结构
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="info"></param>
private static void UpdateTable(DataQuery item, CompareModel <ColumnModel> info, string tableName)
{
using (var db = new DataContext(item.Key))
{
//add colunm
info.AddName.ForEach(a => {
var tempSql = string.Format("alter table {0} add {1} {2}", tableName, a.Name, GetFieldType(a));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
});
//修改列不为空
info.RemoveNull.ForEach(a => {
var tempSql = "";
//删除主键
var isKey = CheckKey(item, a.Name, tableName);
if (isKey)
{
tempSql = string.Format("alter table {0} drop constraint pk_{0}_{1}", tableName, a.Name);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
if (item.Config.DbType == DataDbType.SqlServer)
{
tempSql = string.Format("alter table {0} alter column {1} {2} not null", tableName, a.Name, GetFieldType(a));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
if (item.Config.DbType == DataDbType.MySql || item.Config.DbType == DataDbType.Oracle)
{
tempSql = string.Format("alter table {0} modify {1} {2} not null", tableName, a.Name, GetFieldType(a));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
//增加主键
if (isKey)
{
tempSql = string.Format("alter table {0} add constraint pk_{0}_{1} primary key ({1})", tableName, a.Name);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
});
//修改列空
info.AddNull.ForEach(a => {
var tempSql = "";
//删除主键
var isKey = CheckKey(item, a.Name, tableName);
if (isKey)
{
tempSql = string.Format("alter table {0} drop constraint pk_{0}_{1}", tableName, a.Name);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
if (item.Config.DbType == DataDbType.SqlServer)
{
tempSql = string.Format("alter table {0} alter column {1} {2} null", tableName, a.Name, GetFieldType(a));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
if (item.Config.DbType == DataDbType.MySql || item.Config.DbType == DataDbType.Oracle)
{
tempSql = string.Format("alter table {0} modify {1} {2} null", tableName, a.Name, GetFieldType(a));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
//增加主键
if (isKey)
{
tempSql = string.Format("alter table {0} add constraint pk_{0}_{1} primary key ({1})", tableName, a.Name);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
});
//删除主键
info.RemoveKey.ForEach(a => {
var tempSql = string.Format("alter table {0} drop constraint pk_{0}_{1}", tableName, a);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
});
//增加主键
info.AddKey.ForEach(a => {
var tempSql = "";
if (item.Config.DbType == DataDbType.SqlServer)
{
tempSql = string.Format("alter table {0} alter column {1} {2} not null", tableName, a.Name, GetFieldType(a));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
if (item.Config.DbType == DataDbType.MySql || item.Config.DbType == DataDbType.Oracle)
{
tempSql = string.Format("alter table {0} modify {1} {2} not null", tableName, a.Name, GetFieldType(a));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
tempSql = string.Format("alter table {0} add constraint pk_{0}_{1} primary key ({1})", tableName, a.Name);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
});
//修改列
info.Type.ForEach(p => {
var tempSql = "";
if (!info.AddName.Exists(a => a.Name == p.Name))
{
//删除主键
var isKey = CheckKey(item, p.Name, tableName);
if (isKey)
{
tempSql = string.Format("alter table {0} drop constraint pk_{0}_{1}", tableName, p.Name);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
if (item.Config.DbType == DataDbType.SqlServer)
{
tempSql = string.Format("alter table {0} alter column {1} {2}", tableName, p.Name, GetFieldType(p));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
if (item.Config.DbType == DataDbType.MySql || item.Config.DbType == DataDbType.Oracle)
{
tempSql = string.Format("alter table {0} modify {1} {2}", tableName, p.Name, GetFieldType(p));
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
//增加主键
if (isKey)
{
tempSql = string.Format("alter table {0} add constraint pk_{0}_{1} primary key ({1})", tableName, p.Name);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
}
}
});
//删除列
info.RemoveName.ForEach(a => {
var tempSql = string.Format("alter table {0} drop column {1}", tableName, a);
db.ExecuteSql(tempSql, null, false, item.Config.IsOutSql);
});
//修改列备注
info.Comments.ForEach(a => {
UpdateColumn(item, a.Name, a.Comments, GetFieldType(a.Type), tableName);
});
}
}
