Gsmarket/Sources/GS-MarketMG-2018/GSMarketSys/Controllers/DataChartsController.cs

using SysBaseLibs;
using SysDataLibs;
using SysDataLibs.TableClass;
using System;
using System.Collections.Generic;
using System.Data;
using System.Web.Mvc;
using Newtonsoft.Json;

namespace GSMarketSys.Controllers
{
    public class DataChartsController : BaseController
    {
		public ActionResult ChartsData(string reportType,string reportTitle)
		{
			ViewBag.ReportType = reportType;
			ViewBag.ReportTitle = reportTitle?? "检测统计分析";
			ViewBag.IsMarketSysAccount = UserSessionInfo.IsMarketSysAccount ? "0" : "1";
			//ViewBag.IsSystem = UserSessionInfo.IsSystem ? "0" : "1";
			ViewBag.MarketID = UserSessionInfo.MarketId;
			ViewBag.RegionID = UserSessionInfo.UserInfo.RegionID;
			ViewBag.SelRegionInfo = GetData.Instance.GetRegionSelStr(Regions_info.cRegionID, Regions_info.cNames, Tn.Regions, UserSessionInfo.DBConn);
			ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
			ViewBag.SelCheckProjectTypeInfo = GetData.Instance.GetSelStr("CheckProjectTypeID", "Name", "CheckProjectType", "IsLock=0", UserSessionInfo.DBConn);
			//ViewBag.SelLittleKindInfo = GetData.Instance.GetSelStr("LittleKindID", "Name", "LittleKind", "IsLock=0 ORDER by Name", UserSessionInfo.DBConn);
			ViewBag.SelLittleKindInfo = GetData.Instance.GetSelStr("VarietyCategoryNo", "VarietyCategoryName", "VarietyCategory", "IsLock='N' AND Depth=1 ORDER by VarietyCategoryNo", UserSessionInfo.DBConn);
			ViewBag.SelSampleNameInfo = GetData.Instance.GetSelStr("SpecialsID", "Name", "Specials", "IsLock=0 ORDER by Name,SortNumber", UserSessionInfo.DBConn);
			ViewBag.SelCorpKindInfo = GetData.Instance.GetSelStr("Names", "Names", "CorporationType", UserSessionInfo.DBConn);
			return View();
		}

		#region 检测数统计---------->
		public ActionResult ChartsYearCount()
		{
			return RedirectToAction("ChartsData", new { reportType = "YEARCOUNT", reportTitle = "检测抽样数——年统计" });
		}
		public ActionResult ChartsMonthCount()
		{
			return RedirectToAction("ChartsData", new { reportType = "MONTHCOUNT", reportTitle = "检测抽样数——月统计" });
		}
		public ActionResult ChartsCheckProjectType()
		{
			return RedirectToAction("ChartsData", new { reportType = "CHECKPROJECTTYPEID", reportTitle = "检测抽样数——检测项目统计" });
		}
		public ActionResult ChartsCheckUnit()
		{
			return RedirectToAction("ChartsData", new { reportType = "CHECKUNIT", reportTitle = "检测抽样数——检测单位统计" });
		}
		public ActionResult ChartsLittleKindName()
		{
			return RedirectToAction("ChartsData", new { reportType = "LITTLEKINDNAME", reportTitle = "检测抽样数——食品小类统计" });
		}
		public ActionResult ChartsProduceUnit()
		{
			return RedirectToAction("ChartsData", new { reportType = "PRODUCEUNIT", reportTitle = "检测抽样数——生产单位统计" });
		}
		public ActionResult ChartsSampleName()
		{
			return RedirectToAction("ChartsData", new { reportType = "SAMPLENAME", reportTitle = "检测抽样数——检测样品统计" });
		}
		#endregion

		#region 合格率统计---------->

		public ActionResult ChartsYearQualified()
		{
			return RedirectToAction("ChartsData", new { reportType = "YEARHGL", reportTitle = "年合格率统计" });
		}

		public ActionResult ChartsMonthQualified()
		{
			return RedirectToAction("ChartsData", new { reportType = "MONTHHGL", reportTitle = "月合格率统计" });
		}

		public ActionResult ChartsCheckProjectTypeQualified()
		{
			return RedirectToAction("ChartsData", new { reportType = "CHECKPROJECTTYPEHGL", reportTitle = "检测项目合格率统计" });
		}

		public ActionResult ChartsCheckUnitQualified()
		{
			return RedirectToAction("ChartsData", new { reportType = "CHECKUNITHGL", reportTitle = "检测单位合格率统计" });
		}

		public ActionResult ChartsLittleKindNameQualified()
		{
			return RedirectToAction("ChartsData", new { reportType = "LITTLEKINDNAMEHGL", reportTitle = "食品小类合格率统计" });
		}

		public ActionResult ChartsProduceUnitQualified()
		{
			return RedirectToAction("ChartsData", new { reportType = "SAMPLENAMEHGL", reportTitle = "检测样品合格率统计" });
		}

		public ActionResult ChartsSampleNameQualified()
		{
			return RedirectToAction("ChartsData", new { reportType = "PRODUCEUNITHGL", reportTitle = "生产单位合格率统计" });
		}
        #endregion

        #region 园区主要商品价格走势图------------>
        public ActionResult MajorQuoteTrendHighCharts()
        {
         
            ViewBag.ReportTitle = "园区主要商品价格走势图 ";
            ViewBag.IsMarketSysAccount = UserSessionInfo.IsMarketSysAccount ? "0" : "1";
            //ViewBag.IsSystem = UserSessionInfo.IsSystem ? "0" : "1";
            ViewBag.MarketID = UserSessionInfo.MarketId;
            ViewBag.RegionID = UserSessionInfo.UserInfo.RegionID;
            //ViewBag.SelRegionInfo = GetData.Instance.GetSelStr(Regions_info.cRegionID, Regions_info.cNames, Tn.Regions, UserSessionInfo.DBConn);
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
            ViewBag.SelRegionID = GetData.Instance.GetRegionsTreeList(UserSessionInfo.DBConn);
            ViewBag.SelMajorGoods = GetData.Instance.GetSelStr(VarietyCategory_info.cVarietyCategoryName, VarietyCategory_info.cVarietyCategoryName, Tn.VarietyCategory, " " + VarietyCategory_info.cIsMajorCost + " = 'Y'", UserSessionInfo.DBConn);
            //ViewBag.SelLittleKindInfo = GetData.Instance.GetSelStr("LittleKindID", "Name", "LittleKind", "IsLock=0 ORDER by Name", UserSessionInfo.DBConn);
            //ViewBag.SelSampleNameInfo = GetData.Instance.GetSelStr("SpecialsID", "Name", "Specials", "IsLock=0 ORDER by Name,SortNumber", UserSessionInfo.DBConn);
            //ViewBag.SelCorpKindInfo = GetData.Instance.GetSelStr("Names", "Names", "CorporationType", UserSessionInfo.DBConn);
            return View();
        }
        public ActionResult ShowChartMajorQuoteTrendHighCharts()
        {
            string RegionID = Request["RegionID"];
            string MarketID = Request["MarketID"];
            string MajorGoods = Request["MajorGoods"];
            string StartTime = Request["StartTime"];
            string EndTime = Request["EndTime"];
            string ChartType = Request["ChartType"];
            string cmbQueryCycle = Request["cmbQueryCycle"];
            string IsExportImage = Request["IsExportImage"];
            if (IsExportImage == "Y")
            {
                return Content("{\"__totalcount\":0}");
            }
            string lcSql = "";
            if ((!string.IsNullOrEmpty(StartTime)) && (!string.IsNullOrEmpty(EndTime)))
            {
                if ((StartTime != "1900-1-1") && (EndTime != "1900-1-1"))
                {
                    lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + ">='" + StartTime;
                    lcSql += "' AND " + MarketMajorQuote_info.cQuoteDate + "<='" + EndTime + "'";
                }
                else if (StartTime != "1900-01-01")
                {
                    lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
                }
            }
            else if (!string.IsNullOrEmpty(StartTime))
            {
                lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
            }
            //隶属市场
            
            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " AND " + MarketMajorQuote_info.cMarketID + "='" + MarketID + "'";
            }
            else
            {
                lcSql += " AND " + MarketMajorQuote_info.cMarketID + " IN ("+Markets_info.GetAllMarketIDByRegionId(RegionID, UserSessionInfo.DBConn)+" )";
            }

            string strAVGs = " AVG(" + MarketMajorQuote_info.cQuoteHigh + ") AS QUOTE_HIGH, " +
               " AVG(" + MarketMajorQuote_info.cQuoteLow + ") AS QUOTE_LOW, " +
               " AVG(" + MarketMajorQuote_info.cQuoteAverage + ") AS QUOTE_AVG ";

            // 周期
            string strDateSql = string.Empty;
            if (cmbQueryCycle == "1")  // 日
            {
                strDateSql = " Convert(Char(10)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "2") // 月
            {
                strDateSql = " Convert(Char(7)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "3") // 季
            {
                strDateSql = " Convert(Char(4),year(" + MarketMajorQuote_info.cQuoteDate +
                    ")) + '/' + Convert(Char(1),DATEPART(qq," + MarketMajorQuote_info.cQuoteDate + ")) ";
            }
            else  // 年
            {
                strDateSql = " year(" + MarketMajorQuote_info.cQuoteDate + ") ";
            }

            string SearchStrHead = " SELECT " + strDateSql + " AS QUOTE_DATE, " + strAVGs + " FROM " + Tn.MarketMajorQuote + " WHERE 1=1  ";
            string SearchStrRear = " GROUP BY " + strDateSql;
            string SearchStrOrderBy = " ORDER BY " + strDateSql;
            // 拼装SQL查询语句
            lcSql = SearchStrHead + " AND " + MarketMajorQuote_info.cMajorGoodsName + " = '" + MajorGoods + "'" + " " + lcSql;
            lcSql += "  " + SearchStrRear;

            // 统计按照时间排序
            if (SearchStrOrderBy.Length > 0)
                lcSql += "  " + SearchStrOrderBy;
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);


            string strJson = "";
            string strReportType = Request["ReportType"];
            if ((strReportType == null) || (strReportType == string.Empty))
                strReportType = "CHART";
            strReportType = strReportType.ToUpper();
            List<Array> listData = null;
            try
            {
                // 生成图片
                if (strReportType == "CHART")
                {
                    string strChartType = ChartType;
                    if (strChartType != null)
                    {
                        strChartType = strChartType.ToUpper();

                        // 将数据库返回结果转换成按列存放的格式
                        ChartReportUtils.PrepareData(loQuery, ref listData);

                        // 生产Json格式的返回数据
                        if (strChartType == "PIE")
                            strJson = GetData.Instance.GenOutputDataForPie(loQuery.RecCount, listData);
                        else
                            strJson = GetData.Instance.GenOutputData(loQuery.RecCount, listData);
                    }
                }
            }
            catch
            {
                strJson = string.Empty;
            }
            //strJson = "{\"__totalcount\":2,\"__categories\":[\"2011\",\"2012\"],\"__series\":[[5,3],[5,7],[9,5]]}";
            // 处理异常或者无数据返回
            if (strJson == string.Empty)
                strJson = "{\"__totalcount\":0}";

            return Content(strJson);
        }
        #endregion


        #region 园区主要商品价格走势图 New ------------
        public ActionResult HighChartsMajorQuoteTrend()
        {

            ViewBag.ReportTitle = "园区主要商品价格走势图 ";
            ViewBag.IsMarketSysAccount = UserSessionInfo.IsMarketSysAccount ? "0" : "1";
            ViewBag.MarketID = UserSessionInfo.MarketId;
            ViewBag.RegionID = UserSessionInfo.UserInfo.RegionID;
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true), UserSessionInfo.DBConn);
            ViewBag.SelRegionID = GetData.Instance.GetRegionsTreeList(UserSessionInfo.DBConn,false);
            ViewBag.SelMajorGoods = GetData.Instance.GetSelStr(VarietyCategory_info.cVarietyCategoryName, VarietyCategory_info.cVarietyCategoryName, Tn.VarietyCategory, " " + VarietyCategory_info.cIsMajorCost + " = 'Y'", UserSessionInfo.DBConn);
           
            return View();
        }

        public ActionResult ShowChartMajorQuoteTrend()
        {
            string RegionID = Request["RegionID"];
            string MarketID = Request["MarketID"];
            string MajorGoods = Request["MajorGoods"];
            string StartTime = Request["StartTime"];
            string EndTime = Request["EndTime"];
            string cmbQueryCycle = Request["cmbQueryCycle"];
           
            string lcSql = "";
            if ((!string.IsNullOrEmpty(StartTime)) && (!string.IsNullOrEmpty(EndTime)))
            {
                if ((StartTime != "1900-1-1") && (EndTime != "1900-1-1"))
                {
                    lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + ">='" + StartTime;
                    lcSql += "' AND " + MarketMajorQuote_info.cQuoteDate + "<='" + EndTime + "'";
                }
                else if (StartTime != "1900-01-01")
                {
                    lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
                }
            }
            else if (!string.IsNullOrEmpty(StartTime))
            {
                lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
            }
            //隶属市场

            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " AND " + MarketMajorQuote_info.cMarketID + "='" + MarketID + "'";
            }
            else
            {
                lcSql += " AND " + MarketMajorQuote_info.cMarketID + " IN (" + Markets_info.GetAllMarketIDByRegionId(RegionID, UserSessionInfo.DBConn) + " )";
            }

            string strAVGs = " AVG(" + MarketMajorQuote_info.cQuoteHigh + ") AS QUOTE_HIGH, " +
               " AVG(" + MarketMajorQuote_info.cQuoteLow + ") AS QUOTE_LOW, " +
               " AVG(" + MarketMajorQuote_info.cQuoteAverage + ") AS QUOTE_AVG ";

            // 周期
            string strDateSql = string.Empty;
            if (cmbQueryCycle == "1")  // 日
            {
                strDateSql = " Convert(Char(10)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "2") // 月
            {
                strDateSql = " Convert(Char(7)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "3") // 季
            {
                strDateSql = " Convert(Char(4),year(" + MarketMajorQuote_info.cQuoteDate +
                    ")) + '/' + Convert(Char(1),DATEPART(qq," + MarketMajorQuote_info.cQuoteDate + ")) ";
            }
            else  // 年
            {
                strDateSql = " year(" + MarketMajorQuote_info.cQuoteDate + ") ";
            }

            string SearchStrHead = " SELECT " + strDateSql + " AS QUOTE_DATE, " + strAVGs + " FROM " + Tn.MarketMajorQuote + " WHERE 1=1  ";
            string SearchStrRear = " GROUP BY " + strDateSql;
            string SearchStrOrderBy = " ORDER BY " + strDateSql;
            // 拼装SQL查询语句
            lcSql = SearchStrHead + " AND " + MarketMajorQuote_info.cMajorGoodsName + " = '" + MajorGoods + "'" + " " + lcSql;
            lcSql += "  " + SearchStrRear;

            // 统计按照时间排序
            if (SearchStrOrderBy.Length > 0)
                lcSql += "  " + SearchStrOrderBy;
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            if (loQuery.IsOpened && loQuery.RecCount > 0)
            {
                return Content(JsonConvert.SerializeObject(loQuery.CurrentTable));
            }
            return Content("{}");
        }
        #endregion
        #region 园区主要商品价格走势市场对比------------>
        public ActionResult MajorQuoteMarketCmpHighCharts()
        {
            ViewBag.ReportTitle = "园区主要商品价格走势市场对比";
            ViewBag.IsMarketSysAccount = UserSessionInfo.IsMarketSysAccount ? "0" : "1";
            string lcMarketSql = "";
            if (UserSessionInfo.IsMarketSysAccount)
            {
                lcMarketSql = " and " + Markets_info.cMarketID + " = '" + UserSessionInfo.MarketId + "'";
            }
            //ViewBag.IsSystem = UserSessionInfo.IsSystem ? "0" : "1";
            ViewBag.MarketID = UserSessionInfo.MarketId;
            ViewBag.RegionID = UserSessionInfo.UserInfo.RegionID;
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true)+ lcMarketSql, UserSessionInfo.DBConn);
            ViewBag.SelMajorGoods = GetData.Instance.GetSelStr(VarietyCategory_info.cVarietyCategoryName, VarietyCategory_info.cVarietyCategoryName, Tn.VarietyCategory, " "+VarietyCategory_info.cIsMajorCost+" = 'Y'", UserSessionInfo.DBConn);
            return View();
        }
        public ActionResult ShowMajorQuoteMarketCmpHighCharts()
        {
           
            string MarketIDs = Request["MarketIDs"];
            string MajorGoods = Request["MajorGoods"];
            string StartTime = Request["StartTime"];
            string EndTime = Request["EndTime"];
            string ChartType = Request["ChartType"];
            string cmbQueryCycle = Request["cmbQueryCycle"];
            string lcSql = "";
            string lcDateSql = "";
            string SearchStrOrderBy = " ";
            string SearchStrDate = "";
            string IsExportImage = Request["IsExportImage"];
            if (IsExportImage == "Y")
            {
                return Content("{\"__totalcount\":0}");
            }
            // 周期
            if (cmbQueryCycle == "1")  // 日
            {
                SearchStrDate = " Convert(Char(10)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "2") // 月
            {
                SearchStrDate = " Convert(Char(7)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "3") // 季
            {
                SearchStrDate = " Convert(Char(4),year(" + MarketMajorQuote_info.cQuoteDate +
                    ")) + '/' + Convert(Char(1),DATEPART(qq," + MarketMajorQuote_info.cQuoteDate + ")) ";
            }
            else  // 年
            {
                SearchStrDate = " year(" + MarketMajorQuote_info.cQuoteDate + ") ";
            }

            string SearchStrHead = " SELECT QUOTE_DATE, QUOTE_MARKET, QUOTE_AVG FROM ";
            string SearchStrGroupBy = " GROUP BY " + MarketMajorQuote_info.cMarketID + "," + SearchStrDate;
            SearchStrOrderBy = " ORDER BY QUOTE_DATE, QUOTE_MARKET";

            if ((!string.IsNullOrEmpty(StartTime)) && (!string.IsNullOrEmpty(EndTime)))
            {
                if ((StartTime != "1900-1-1") && (EndTime != "1900-1-1"))
                {
                    lcDateSql += " AND " + MarketMajorQuote_info.cQuoteDate + ">='" + StartTime;
                    lcDateSql += "' AND " + MarketMajorQuote_info.cQuoteDate + "<='" + EndTime + "'";
                }
                else if (StartTime != "1900-01-01")
                {
                    lcDateSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
                }
            }
            else if (!string.IsNullOrEmpty(StartTime))
            {
                lcDateSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
            }
            //隶属市场
            string lcMarketsSql = "";
            if (!string.IsNullOrEmpty(MarketIDs))
            {
                lcMarketsSql += " and " + Markets_info.cMarketID  +"  IN ('" + MarketIDs.Replace(",","','") + "' )";
            }

            string lcSqlLeft = " (SELECT " + Markets_info.cMarketID + "," + Markets_info.cNames + " AS QUOTE_MARKET FROM ";
            lcSqlLeft += Tn.Markets + " WHERE 1=1" +  lcMarketsSql + " ) AS L ";

            string lcSqlRight = " (SELECT " + SearchStrDate + " AS QUOTE_DATE," + MarketMajorQuote_info.cMarketID + ", AVG(" + MarketMajorQuote_info.cQuoteAverage + ") AS QUOTE_AVG  ";
            lcSqlRight += " FROM " + Tn.MarketMajorQuote + " WHERE " + MarketMajorQuote_info.cMajorGoodsName + "= '" + MajorGoods + "'";
            lcSqlRight += lcDateSql  + lcMarketsSql;
            lcSqlRight += SearchStrGroupBy + " ) AS R ";

            // LEFT OUTER JOIN 是为了将无数据的市场显示在Series中.如想不显示,改为INNER JOIN
            string strSqlJoin = " LEFT OUTER JOIN ";
            //if (ChkBoxDisplayAll.Checked == false)
            //    strSqlJoin = " INNER JOIN";

            lcSql = SearchStrHead + lcSqlLeft + strSqlJoin + lcSqlRight + " ON L." + Markets_info.cMarketID + "=R." + MarketMajorQuote_info.cMarketID + " ";

            // 统计按照时间排序
            if (SearchStrOrderBy.Length > 0)
                lcSql += "  " + SearchStrOrderBy;


            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);


            string strJson = "";
            string strReportType = Request["ReportType"];
            if ((strReportType == null) || (strReportType == string.Empty))
                strReportType = "CHART";
            strReportType = strReportType.ToUpper();
            List<Array> listData = null;
            List<string> _listCategory = null;
            List<string> _listSeries = null;
            List<List<Array>> _listDataEx = null;
            try
            {
                // 生成图片
                if (strReportType == "CHART")
                {
                    string strChartType = ChartType;
                    if (strChartType != null)
                    {
                        strChartType = strChartType.ToUpper();

                        // 将数据库返回结果转换成按列存放的格式
                        //ChartReportUtils.PrepareData(loQuery, ref listData);
                        ChartReportUtils.PrepareDataNonfixedCat(loQuery, ref _listSeries, ref _listDataEx);
                        // 生产Json格式的返回数据
                        // 生产Json格式的返回数据
                        int nCount = 0;
                        if (_listDataEx.Count >= 1)
                        {
                           
                            strJson = GenOutputDataNonfixedCat(false, _listDataEx, _listSeries);
                        }
                    }
                }
            }
            catch
            {
                strJson = string.Empty;
            }
            //strJson = "{\"__totalcount\":2,\"__categories\":[\"2011\",\"2012\"],\"__series\":[[5,3],[5,7],[9,5]]}";
            // 处理异常或者无数据返回
            if (strJson == string.Empty)
                strJson = "{\"__totalcount\":0}";

            return Content(strJson);
        }
        // 生成返回的Json格式数据
        protected string GenOutputDataNonfixedCat(bool CategoryIsDatetime, List<List<Array>> _listDataEx, List<string> _listSeries)
        {
            string strRet = string.Empty;
            int nCount = 0;

            if ((_listDataEx != null) && (_listDataEx.Count >= 1))
            {
                // 数据列表,数目和Series个数一致
                Array arrayValue = Array.CreateInstance(typeof(string), _listSeries.Count);

                for (int i = 0; i < _listDataEx.Count; i++)
                {
                    // 当前Series数据
                    List<Array> ListCurSeries = _listDataEx[i];

                    // 临时Array,大小为当前Series的容量
                    Array ArrayTemp = Array.CreateInstance(typeof(string), ListCurSeries.Count);

                    // 当前Series的每个元素转换为格式:  {"x":"2010/1/1", "y":29.9}
                    for (int j = 0; j < ListCurSeries.Count; j++)
                    {
                        if (ListCurSeries[j].GetValue(0) != "")
                        {
                            // 将Category和第一个数值列拼接
                            string strItem = "\"x\":\"" + ListCurSeries[j].GetValue(0) + "\",\"y\":" + ListCurSeries[j].GetValue(2);
                            strItem = "{" + strItem + "}";
                            ArrayTemp.SetValue(strItem, j);
                        }
                        else
                        {
                            ArrayTemp.SetValue("", j);
                        }
                    }

                    // Series的最大容量
                    if (ListCurSeries.Count > nCount)
                    {
                        nCount = ListCurSeries.Count;
                    }

                    // 将当前Series转换为格式：[{"x":"2010/1/1", "y":29.9},{"x:"2010/1/2", "y":71.5}]                
                    string strData = ArrayToString(ArrayTemp);
                    strData = UtilStr.GetDelimitedStr(strData);
                    // 并加入数据列表
                    arrayValue.SetValue(strData, i);
                }

                // 数据列表转换成string
                string strValues = ArrayToString(arrayValue);
                strValues = UtilStr.GetDelimitedStr(strValues);

                // 各Series的名称
                string strNames = ListToStringEx(_listSeries);
                strNames = UtilStr.GetDelimitedStr(strNames);

                // 返回结果            
                strRet = "{\"__totalcount\":" + nCount + ",\"__names\":" + strNames + ",\"__series\":";
                strRet += strValues + "}";
            }

            return strRet;
        }
        // 生成返回的Json格式数据
        public string GenOutputData(int nCount,List<Array> _listData,List<string> _listCategory, List<string> _listSeries)
        {
            string strRet = string.Empty;

            if ((_listData != null) && (_listData.Count >= 1))
            {
                // x轴的Categories,注意各项都有双引号,用逗号相隔开
                string strCategories = ListToStringEx(_listCategory);
                strCategories = UtilStr.GetDelimitedStr(strCategories);

                // y轴数据列表.Array格式类似于：[[533,924,554],[100,99,98],[300,299,198]]
                Array arrayValue = Array.CreateInstance(typeof(string), _listSeries.Count);

                for (int i = 0; i < _listSeries.Count; i++)
                {
                    Array arrayData = _listData[i];
                    Array arrayTemp = Array.CreateInstance(typeof(string), _listCategory.Count);
                    for (int j = 0; j < _listCategory.Count; j++)
                    {
                        Array arrayVal = (Array)(arrayData.GetValue(j));
                        //获取对应查询语句中的第几个Val值(对于本页面的查询，就1个，编号是0)
                        string strVal = arrayVal.GetValue(0).ToString();
                        arrayTemp.SetValue(strVal, j);
                    }

                    // 当前Series的数据[533,924,554]
                    string strData = ArrayToString(arrayTemp);
                    strData = UtilStr.GetDelimitedStr(strData);
                    arrayValue.SetValue(strData, i);
                }

                // y轴数据列表转换成string
                string strValues = ArrayToString(arrayValue);
                strValues = UtilStr.GetDelimitedStr(strValues);

                // 各Series的名称
                string strNames = ListToStringEx(_listSeries);
                strNames = UtilStr.GetDelimitedStr(strNames);

                // 返回结果            
                strRet = "{\"__totalcount\":" + nCount + ",\"__categories\":" + strCategories +
                    ",\"__names\":" + strNames + ",\"__series\":" + strValues + "}";
            }

            //strRet = "{\"__totalcount\":3,\"__categories\":[\"2012/02\",\"2012/03\",\"2012/04\"],\"__series\":[[533,924,554],[100,99,98],[300,299,198]]}";
            return strRet;
        }
        // 将一个Array各项用逗号隔开，拼成一个string
        public string ArrayToString(Array paArray)
        {
            string lcStr = "";
            foreach (string lcStr2 in paArray)
            {
                lcStr = lcStr + ((lcStr == "") ? "" : ",") + lcStr2;
            }
            return lcStr;
        }

        // 将一个Array各项左右加双引号,再用逗号隔开，拼成一个string
        public string ArrayToStringEx(Array paArray)
        {
            string lcStr = "";
            foreach (string lcStr2 in paArray)
            {
                lcStr = lcStr + ((lcStr == "") ? "" : ",") + "\"" + lcStr2 + "\"";
            }
            return lcStr;
        }
        // 将一个List各项左右加双引号,再用逗号隔开，拼成一个string
        public string ListToStringEx(List<string> paList)
        {
            string lcStr = "";
            foreach (string lcStr2 in paList)
            {
                lcStr = lcStr + ((lcStr == "") ? "" : ",") + "\"" + lcStr2 + "\"";
            }
            return lcStr;
        }
        #endregion

        #region
        public ActionResult MajorQuoteMultiHighCharts()
        {
            ViewBag.ReportTitle = "  园区主要商品价格走势对比 ";
            ViewBag.IsMarketSysAccount = UserSessionInfo.IsMarketSysAccount ? "0" : "1";
            string lcMarketSql = "";
            if (UserSessionInfo.IsMarketSysAccount)
            {
                lcMarketSql = " and " + Markets_info.cMarketID + " = '" + UserSessionInfo.MarketId + "'";
            }
            //ViewBag.IsSystem = UserSessionInfo.IsSystem ? "0" : "1";
            ViewBag.MarketID = UserSessionInfo.MarketId;
            ViewBag.RegionID = UserSessionInfo.UserInfo.RegionID;
            ViewBag.SelRegionID = GetData.Instance.GetRegionsTreeList(UserSessionInfo.DBConn);
            ViewBag.SelMarketInfo = GetData.Instance.GetSelStr(Markets_info.cMarketID, Markets_info.cNames, Tn.Markets, Markets_info.cIsLock + "='0' " + UserSessionInfo.MarketDataSql(true) + lcMarketSql, UserSessionInfo.DBConn);
            //ViewBag.SelMajorGoods = GetData.Instance.GetSelStr("MajorSpecialName", "MajorSpecialName", "MajorSpecials", "IsLock='N' ", UserSessionInfo.DBConn);
            string lcSql = " Select "+VarietyCategory_info.cVarietyCategoryName+" from "+Tn.VarietyCategory+ " where "+ VarietyCategory_info.cIsMajorCost+" = 'Y'";
            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);
            string MajorGoodsHtm = "";
            if (loQuery.IsOpened && loQuery.RecCount > 0)
            {
                loQuery.MoveFirst();
                for(int i = 0; i < loQuery.RecCount; i++)
                {
                    if(i % 5==0&&i!=0)
                    {
                        MajorGoodsHtm += "<br>";
                    }
                    MajorGoodsHtm += "<input type='checkbox' name='chkMajor' id='chkMajor" +i+ "'  value='"+loQuery.GetString("VarietyCategoryName") + "'/><label for='chkMajor" + i + "'>" +
                        loQuery.GetString("VarietyCategoryName") + "</label>&nbsp;&nbsp;&nbsp;";
                    loQuery.MoveNext();
                }
            }
            ViewBag.SelMajorGoods = MajorGoodsHtm;
            return View();
        }

        public ActionResult ShowMajorQuoteMultiHighCharts()
        {
            string MarketID = Request["MarketID"];
            string MajorGoods = Request["MajorGoods"];
            string StartTime = Request["StartTime"];
            string EndTime = Request["EndTime"];
            string ChartType = Request["ChartType"];
            string cmbQueryCycle = Request["cmbQueryCycle"];
            string strFixedCategory = Request["fixedCategory"];
            string RegionID = Request["RegionID"];
            string lcSql = "";
            //string lcDateSql = "";
            string SearchStrOrderBy = " ";
            //string SearchStrDate = "";
            string IsExportImage = Request["IsExportImage"];
            if (IsExportImage == "Y")
            {
                return Content("{\"__totalcount\":0}");
            }
            // 周期
            string strAVGs = " AVG(" + MarketMajorQuote_info.cQuoteAverage + ") AS QUOTE_AVG ";

            // 周期
            string strDateSql = string.Empty;
            if (cmbQueryCycle == "1")  // 日
            {
                strDateSql = " Convert(Char(10)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "2") // 月
            {
                strDateSql = " Convert(Char(7)," + MarketMajorQuote_info.cQuoteDate + ", 111) ";
            }
            else if (cmbQueryCycle == "3") // 季
            {
                strDateSql = " Convert(Char(4),year(" + MarketMajorQuote_info.cQuoteDate +
                    ")) + '/' + Convert(Char(1),DATEPART(qq," + MarketMajorQuote_info.cQuoteDate + ")) ";
            }
            else  // 年
            {
                strDateSql = " year(" + MarketMajorQuote_info.cQuoteDate + ") ";
            }
            // SELECT的第一个列必须是Category(本页面为日期)
            string SearchStrHead = " SELECT " + strDateSql + " AS QUOTE_DATE, ";
            // SELECT的第二个列必须是Series(本页面为商品品种)
            SearchStrHead += MarketMajorQuote_info.cMajorGoodsName + " AS QUOTE_NAME, ";
            // SELECT的第三个列开始是数值列
            SearchStrHead += strAVGs + " FROM " + Tn.MarketMajorQuote + " WHERE 1=1  ";
            // GROUP及排序
            string SearchStrRear = " GROUP BY " + MarketMajorQuote_info.cMajorGoodsName + "," + strDateSql;
            SearchStrOrderBy = " ORDER BY " + strDateSql + "," + MarketMajorQuote_info.cMajorGoodsName;
           

            if ((!string.IsNullOrEmpty(StartTime)) && (!string.IsNullOrEmpty(EndTime)))
            {
                if ((StartTime != "1900-1-1") && (EndTime != "1900-1-1"))
                {
                    lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + ">='" + StartTime;
                    lcSql += "' AND " + MarketMajorQuote_info.cQuoteDate + "<='" + EndTime + "'";
                }
                else if (StartTime != "1900-01-01")
                {
                    lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
                }
            }
            else if (!string.IsNullOrEmpty(StartTime))
            {
                lcSql += " AND " + MarketMajorQuote_info.cQuoteDate + "='" + StartTime + "'";
            }
            //隶属市场
          
            if (!string.IsNullOrEmpty(MarketID))
            {
                lcSql += " and " + Markets_info.cMarketID + "  = '" + MarketID + "' ";
            }else
            {
                lcSql += " AND " + MarketMajorQuote_info.cMarketID + " IN (" + Markets_info.GetAllMarketIDByRegionId(RegionID, UserSessionInfo.DBConn) + " )";
            }
            string lcMajorGoods = "";
            if (!string.IsNullOrEmpty(MajorGoods))
            {
                lcMajorGoods = " AND " + MarketMajorQuote_info.cMajorGoodsName + " IN ('" + MajorGoods.Replace(",", "','") + "')";
            }
            // 拼装SQL查询语句
            lcSql = SearchStrHead + lcMajorGoods + " " + lcSql;
            lcSql += "  " + SearchStrRear;

            // 统计按照时间排序
            if (SearchStrOrderBy.Length > 0)
                lcSql += "  " + SearchStrOrderBy;


            rsQuery loQuery = UserSessionInfo.DBConn.OpenQuery(lcSql);

            // 是否固定x轴category参数
            bool bFixedCategory = false;
            //string strFixedCategory = Request["fixedCategory"];
            if ((strFixedCategory == null) || (strFixedCategory == string.Empty))
                bFixedCategory = true;
            else
            {
                strFixedCategory = strFixedCategory.ToUpper();
                if ((strFixedCategory == "1") || (strFixedCategory == "TRUE") || (strFixedCategory == "YES") || (strFixedCategory == "Y"))
                    bFixedCategory = true;
            }

            string strJson = "";
            string strReportType = Request["ReportType"];
            if ((strReportType == null) || (strReportType == string.Empty))
                strReportType = "CHART";
            strReportType = strReportType.ToUpper();
            List<Array> _listData = null;
            List<string> _listCategory = null;
            List<string> _listSeries = null;
            List<List<Array>> _listDataEx = null;
            try
            {
                // 生成图片
                if (strReportType == "CHART")
                {
                    //string strChartType = ChartType;
                    //if (strChartType != null)
                    //{
                    //    strChartType = strChartType.ToUpper();

                    //    // 将数据库返回结果转换成按列存放的格式
                    //    //ChartReportUtils.PrepareData(loQuery, ref listData);
                    //    ChartReportUtils.PrepareDataNonfixedCat(loQuery, ref _listSeries, ref _listDataEx);
                    //    // 生产Json格式的返回数据
                    //    // 生产Json格式的返回数据
                    //    int nCount = 0;
                    //    if (_listDataEx.Count >= 1)
                    //    {

                    //        strJson = GenOutputDataNonfixedCat(false, _listDataEx, _listSeries);
                    //    }
                    //}
                    if (bFixedCategory == true)
                    {
                        // 将数据库返回结果转换成按列存放的格式{
                        ChartReportUtils.PrepareGridData(loQuery, ref _listCategory, ref _listSeries, ref _listData);

                        // 生产Json格式的返回数据
                        int nCount = 0;
                        if (_listData.Count >= 1)
                        {
                            nCount = _listCategory.Count;
                            strJson = GenOutputData(nCount, _listData, _listCategory, _listSeries);
                        }
                    }
                    else
                    {
                        // 将数据库返回结果转换成按列存放的格式{
                        ChartReportUtils.PrepareDataNonfixedCat(loQuery, ref _listSeries, ref _listDataEx);

                        // 生产Json格式的返回数据
                        if (_listDataEx.Count >= 1)
                        {
                            strJson = GenOutputDataNonfixedCat(false, _listDataEx, _listSeries);
                        }
                    }
                }
            }
            catch
            {
                strJson = string.Empty;
            }
            //strJson = "{\"__totalcount\":2,\"__categories\":[\"2011\",\"2012\"],\"__series\":[[5,3],[5,7],[9,5]]}";
            // 处理异常或者无数据返回
            if (strJson == string.Empty)
                strJson = "{\"__totalcount\":0}";

            return Content(strJson);
        }
      
        #endregion

    }
}