private JsonTable BuildCoalJsonTable(int reportId, int currentPage, string queryStr, string order, out string selectKey)
{
var coalRepository = new CoalRepository();
var columns = coalRepository.GetColumnDefinitionByReportId(reportId);
var totalRows = 0;
var rptInfo = ReportService.GetReportInfoById(reportId);
if (string.IsNullOrWhiteSpace(queryStr))
{
var filterObj = coalRepository.GetDataFiltersWhichHasDataByReportId(reportId, rptInfo.TableName);
if (filterObj.PrimaryDropdown != null && filterObj.PrimaryDropdown.Items.Count > 0)
{
queryStr = filterObj.PrimaryDropdown.FieldName + "= '" +
filterObj.PrimaryDropdown.Items.First(x => x.Selected).Value + "' ";
}
if (filterObj.SecondDropdown != null && filterObj.SecondDropdown.Items.Count > 0)
{
queryStr += " and " + filterObj.SecondDropdown.FieldName + "= '" +
filterObj.SecondDropdown.Items.First(x => x.Selected).Value + "' ";
}
}
var dt = coalRepository.GetPagedTableData(columns, rptInfo.TableName, order, queryStr, currentPage, out totalRows);
if (dt.Rows.Count > 0)
{
selectKey = dt.Rows[0]["KeyWord"].ToString();
}
else
{
selectKey = string.Empty;
}
var jsonTable = BuildJsonTable(dt, columns, totalRows, currentPage, 50);
return(jsonTable);
}
public JsonResult GetPortSchedularPagedData(string strOrder, string strWhere, int pageIndex)
{
int recordCount = 0;
var tb = CoalRepository.GetCoalPortSchedularPagedData("Coal_port_traffic_MaxTable", "area_uni_code,area_chi_name,end_date,A001,A002,A003,A004,A005,A006,A007,A008,A009,A010,A011,A012,A013,par_name", strOrder, strWhere, pageIndex, 1, 50, out recordCount);
var jtb = BuidJsonTable(tb);
jtb.CurrentPage = pageIndex;
jtb.Total = recordCount;
jtb.PageSize = 50;
return(Json(jtb, JsonRequestBehavior.AllowGet));
}
public ActionResult ExportExcelForCoalChartDetail(int reportId, string key, string name)
{
var table = CoalRepository.GetCoalChartDataTable(reportId, key);
var coalRepo = new CoalRepository();
var chart = coalRepo.GetChartLegendByReportId(reportId);
return(new ExcelResult(table.AsEnumerable().AsQueryable(),
new[] { Resources.Global.Date, chart.ChartYLabel_DisplayName },
new[] { "PointDate", "PointValue" },
name,
name
));
}
private object BuildCoalChartData(int reportId, string key)
{
var table = CoalRepository.GetCoalChartDataTable(reportId, key);
var data = new List <object>();
foreach (DataRow row in table.Rows)
{
var point = new List <object>
{
(Convert.ToDateTime(row[0])).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)).TotalMilliseconds,
Convert.ToDouble(row[1])
};
data.Add(point.ToArray());
}
return(data);
}
public override ActionResult GetReport(int id)
{
var reportInfo = ReportService.GetReportInfoById(id);
ViewBag.ID = reportInfo.ReportId;
ViewBag.Name = reportInfo.DisplayName;
if (reportInfo.ViewName == "Coal")
{
var coalRepo = new CoalRepository();
var chart = coalRepo.GetChartLegendByReportId(id);
ViewBag.ChartYLabel = chart.ChartYLabel_DisplayName;
ViewBag.ChartTitle = chart.ChartTitle_DisplayName;
ViewBag.Legend = chart.Legend;
ViewBag.Unit = chart.Unit;
ViewBag.Filters = coalRepo.GetDataFiltersWhichHasDataByReportId(id, reportInfo.TableName);
}
return(PartialView(reportInfo.ViewName));
}
public void TestGetCoalChartDataTable()
{
var rid = 10009;
var kk = "AREA_UNI_CODE^|^'401200366'||MAC_IDX_PAR^|^'46'";
var res = CoalRepository.GetCoalChartDataTable(rid, kk);
var str = "";
foreach (DataColumn item in res.Columns)
{
str += item.ColumnName + " | ";
}
Console.WriteLine(str);
foreach (DataRow dr in res.Rows)
{
str = string.Join(" | ", dr.ItemArray);
Console.WriteLine(str);
}
}
public ExcelResult ExportExcelForPortSchedularTable(string strOrder, string strWhere, int pageIndex, string strHeader, int isChart = 0, string key = "", int term = 0, string startTime = "")
{
strOrder = isChart == 0 ? (string.IsNullOrEmpty(strOrder) ? "area_uni_code desc" : strOrder) : (string.IsNullOrEmpty(strOrder) ? "END_DATE desc" : " END_DATE desc," + strOrder);
string tableName = isChart == 0 ? "Coal_port_traffic_MaxTable" : "Coal_port_traffic";
int pageSize = isChart == 0 ? 50 : 1000;
pageIndex = isChart == 0 ? pageIndex : 1;
string starttime = !string.IsNullOrEmpty(startTime) ? DateTime.Parse(startTime).AddMonths(term).ToString("yyyy-MM-dd") : "";
string endTime = startTime;
string filterWhere = term == -100 ? "area_uni_code='" + key + "'" : "area_uni_code='" + key + "' and END_DATE>='" + starttime + "' and END_DATE<='" + endTime + "'";
strWhere = isChart == 0 ? strWhere : filterWhere;
int total;
var tableData = CoalRepository.GetCoalPortSchedularPagedData(tableName, "area_chi_name,end_date,A001,A002,A003,A004,A005,A006,A007,A008,A009,A010,A011,A012,A013,par_name", strOrder, strWhere, pageIndex, 1, pageSize, out total);
string[] strs = strHeader.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var excelTable = new DataTable();
var columns = new List <DataColumn>();
strs.ToList().ForEach((s) => { columns.Add(new DataColumn(s)); });
excelTable.Columns.Clear();
excelTable.Columns.AddRange(columns.ToArray());
var itor = tableData.Rows.GetEnumerator();
while (itor.MoveNext())
{
DataRow dr = (DataRow)itor.Current;
DataRow excelRow = excelTable.NewRow();
for (int i = 0; i < tableData.Columns.Count; i++)
{
excelRow[i] = dr[i];
}
excelTable.Rows.Add(excelRow);
}
string name = Resources.CnE.Coal_Schedular;
var data = excelTable.AsEnumerable().AsQueryable();
return(new ExcelResult(data, strs, strs, name, name));
}
public void GetDataFiltersByReportID()
{
var rid = 10003;
var res = new CoalRepository().GetDataFiltersByReportId(rid);
var fom = "isprim:{0},txt:{1},val:{2},issel:{3}";
if (res.PrimaryDropdown != null && res.PrimaryDropdown.Items != null)
{
foreach (var item in res.PrimaryDropdown.Items)
{
Console.WriteLine(string.Format(fom, 1, item.Text, item.Value, item.Selected));
}
}
if (res.SecondDropdown != null && res.SecondDropdown.Items != null)
{
foreach (var item in res.SecondDropdown.Items)
{
Console.WriteLine(string.Format(fom, 0, item.Text, item.Value, item.Selected));
}
}
}
private object GetCoalChartData(string key)
{
var chart = new ChartJsonData();
var dataSeries = new List <object>();
var dataA004 = new List <object>();
var dataA005 = new List <object>();
var dataA007 = new List <object>();
var entrys = CoalRepository.GetCoalTrafficPortData(key);
entrys.ForEach(t =>
{
var point = new List <object>
{
(Convert.ToDateTime(t.end_date)).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)).TotalMilliseconds,
Convert.ToDouble(t.A004)
};
dataA004.Add(point.ToArray());
var pointA005 = new List <object>
{
(Convert.ToDateTime(t.end_date)).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)).TotalMilliseconds,
Convert.ToDouble(t.A005)
};
dataA005.Add(pointA005.ToArray());
var pointA007 = new List <object>
{
(Convert.ToDateTime(t.end_date)).Subtract(new DateTime(1970, 1, 1, 0, 0, 0)).TotalMilliseconds,
Convert.ToDouble(t.A007)
};
dataA007.Add(pointA007.ToArray());
});
dataSeries.Add(new { data = dataA004.ToArray(), name = entrys[0].PortName + "-" + Resources.CnE.Coal_Traffic_Railway, marker = new { enabled = false } });
dataSeries.Add(new { data = dataA005.ToArray(), name = entrys[0].PortName + "-" + Resources.CnE.Coal_Traffic_Road, marker = new { enabled = false } });
dataSeries.Add(new { data = dataA007.ToArray(), name = entrys[0].PortName + "-" + Resources.CnE.Coal_Traffic_Trough, marker = new { enabled = false } });
chart.SeriesData = dataSeries.ToArray();
return(chart.ToJson());
}
public ActionResult GetSubFilter(int sfilterId, string selectedPrimaryItem)
{
var filter = CoalRepository.GetSubDropdownByFilterId(sfilterId, selectedPrimaryItem);
return(Json(filter, JsonRequestBehavior.AllowGet));
}
