Gsmarket/Sources/GS-MarketMG-2018/SysDataLibs/ChartReportUtils.cs

using System;
using System.Collections.Generic;
using SysBaseLibs;

namespace SysDataLibs
{
    public class ChartReportUtils
    {
        /********************************************************
        *            Category1   Category2   Category3  Category4
        * Series1     25          28            32          31
        * Series2     35          44            28          36
       *********************************************************/
        // 将数据库返回结果转换成按列存放的格式
        // 查询语句返回列的第一个是Category，其余的每个列都视作一个Series
        // 如果Category是日期类型，查询语句最好按日期排序
        static public void PrepareData(rsQuery loQuery, ref List<Array> ListData)
        {
            int i = 0;

            try
            {
                if (loQuery != null && loQuery.IsOpened)
                {
                    if (loQuery.RecCount > 0)
                    {
                        ListData = new List<Array>();

                        // 和查询列数一致
                        for (i = 0; i < loQuery.Columns.Count; i++)
                        {
                            // 一共有loQuery.RecCount行记录
                            Array arrayData = Array.CreateInstance(typeof(string), loQuery.RecCount);
                            ListData.Add(arrayData);
                        }

                        // 查询列名称
                        Array arrayFields = UtilStr.StrToArrayEx(loQuery.AllFields, ",");

                        // 轮询各行
                        for (i = 0; i < loQuery.RecCount; i++)
                        {
                            // 对每个Field进行查询
                            for (int j = 0; j < arrayFields.Length; j++)
                            {
                                string strField = arrayFields.GetValue(j).ToString();
                                string strVal = loQuery.GetString(strField);

                                // 数据存到对应的列缓存中
                                ListData[j].SetValue(strVal, i);
                            }

                            // 下一条记录
                            loQuery.MoveNext();
                        }//for
                    }
                }
            }
            catch (Exception err)
            {
                ThreadLog.LogException(err);
                ListData = null;
            }
            finally
            {
            }
        }

        /********************************************************
        *          Category1   Category2   Category3
        * Series1     25           null        30
        * Series2     35           44          66
        * Series3     null         88          99
        * Series4     15           17          13
       *********************************************************/
        // 要求各Series有相同的x轴Category，在某个Category上没有数据时，用null填充
        // 查询语句返回列的第一个是Category，第二个列是Series，其余列为数据
        // 如果要求CategoryList排序返回,则必须把Category放在查询语句排序的第一个
        static public void PrepareGridData(rsQuery loQuery,
            ref List<string> CategoryList,
            ref List<string> SeriesList,
            ref List<Array> ListData)
        {
            int i = 0, j = 0;
            string strTemp = string.Empty;

            try
            {
                if (loQuery != null && loQuery.IsOpened)
                {
                    // 该查询返回的列数>=3, Category / Series / 数值列1 / 数值列2 ....
                    if ((loQuery.RecCount > 0) && (loQuery.Columns.Count >= 3))
                    {
                        // Category列表
                        CategoryList = new List<string>();

                        // Series列表
                        SeriesList = new List<string>();

                        // 列个数
                        int nColumnCount = loQuery.Columns.Count;

                        // 各列的名称
                        Array arrayFields = UtilStr.StrToArrayEx(loQuery.AllFields, ",");
                        string strField = string.Empty;
                        string strCategory = string.Empty;
                        string strSeries = string.Empty;

                        // 缓存数据库查询到的各行(每行以Array方式)
                        List<Array> listRows = new List<Array>();

                        for (i = 0; i < loQuery.RecCount; i++)
                        {
                            // SQL语句返回列的第一个必须是Category
                            strField = arrayFields.GetValue(0).ToString();
                            strCategory = loQuery.GetString(strField);
                            if (strCategory != "")
                            {
                                if (CategoryList.IndexOf(strCategory) < 0)
                                {
                                    CategoryList.Add(strCategory);
                                }
                            }

                            // SQL语句返回列的第二个必须是Series
                            strField = arrayFields.GetValue(1).ToString();
                            strSeries = loQuery.GetString(strField);
                            if (SeriesList.IndexOf(strSeries) < 0)
                            {
                                SeriesList.Add(strSeries);
                            }

                            Array arrayOneRow = Array.CreateInstance(typeof(string), nColumnCount);
                            arrayOneRow.SetValue(strCategory, 0);
                            arrayOneRow.SetValue(strSeries, 1);

                            // 查询数值列的数据
                            for (j = 2; j < nColumnCount; j++)
                            {
                                strField = arrayFields.GetValue(j).ToString();
                                strTemp = loQuery.GetString(strField);
                                arrayOneRow.SetValue(strTemp, j);
                            }

                            // 加入缓存
                            listRows.Add(arrayOneRow);

                            // 下一条
                            loQuery.MoveNext();
                        }

                        // 根据Series数创建对应数量的Array
                        ListData = new List<Array>();
                        for (i = 0; i < SeriesList.Count; i++)
                        {
                            // 每个Series都有CategoryList.Count个
                            Array arraySeries = Array.CreateInstance(typeof(Array), CategoryList.Count);

                            for (j = 0; j < CategoryList.Count; j++)
                            {
                                // 当前Series里面的每个节点是数值列的Array
                                Array arrayVal = Array.CreateInstance(typeof(string), nColumnCount - 2);
                                for (int k = 0; k < nColumnCount - 2; k++)
                                {
                                    // 初始化为"null"
                                    arrayVal.SetValue("null", k);
                                }
                                arraySeries.SetValue(arrayVal, j);
                            }

                            // 添加当前Series
                            ListData.Add(arraySeries);
                        }//for

                        // 轮询listRows缓存,把数据设置到返回结果集ListData
                        foreach (Array rec in listRows)
                        {
                            int nIdxCategory = CategoryList.IndexOf(rec.GetValue(0).ToString());
                            int nIdxSeries = SeriesList.IndexOf(rec.GetValue(1).ToString());
                            if ((nIdxCategory >= 0) && (nIdxSeries >= 0))
                            {
                                Array arrayVal = Array.CreateInstance(typeof(string), nColumnCount - 2);
                                for (i = 0; i < nColumnCount - 2; i++)
                                {
                                    strTemp = rec.GetValue(i + 2).ToString();
                                    arrayVal.SetValue(strTemp, i);
                                }

                                // 设置到指定Series的指定Category位置
                                ListData[nIdxSeries].SetValue(arrayVal, nIdxCategory);
                            }
                        }

                        /*// 转换List到Array格式
                        _arrayGoods = Array.CreateInstance(typeof(string), GoodsList.Count);
                        for (i = 0; i < GoodsList.Count; i++)
                        {
                            _arrayGoods.SetValue(GoodsList[i], i);
                        }*/
                    }//if loQuery.RecCount
                }
            }
            catch (Exception err)
            {
                ThreadLog.LogException(err);
                CategoryList = null;
                SeriesList = null;
                ListData = null;
            }
            finally
            {
            }
        }

        /********************************************************
        *          Category1   Category2   Category3    Category4
        * Series1     25                       30
        * Series2     35           44          66           48
        * Series3                  88                       69
        * Series4     15           17          13
       *********************************************************/
        // 各Series的数据个数可能不一样，无统一的x轴Category
        static public void PrepareDataNonfixedCat(rsQuery loQuery,
            ref List<string> SeriesList,
            ref List<List<Array>> ListData)
        {
            int i = 0, j = 0;
            string strTemp = string.Empty;

            try
            {
                if (loQuery != null && loQuery.IsOpened)
                {
                    // 该查询返回的列数>=3, Category / Series / 数值列1 / 数值列2 ....
                    if ((loQuery.RecCount > 0) && (loQuery.Columns.Count >= 3))
                    {
                        // 返回数据.每个Series对应一个List
                        ListData = new List<List<Array>>();

                        // Series列表
                        SeriesList = new List<string>();

                        // 各列的名称
                        Array arrayFields = UtilStr.StrToArrayEx(loQuery.AllFields, ",");
                        string strField = string.Empty;
                        string strCategory = string.Empty;
                        string strSeries = string.Empty;

                        // 列个数
                        int nColumnCount = loQuery.Columns.Count;

                        for (i = 0; i < loQuery.RecCount; i++)
                        {
                            // SQL语句返回列的第一个必须是Category
                            strField = arrayFields.GetValue(0).ToString();
                            strCategory = loQuery.GetString(strField);

                            // SQL语句返回列的第二个必须是Series
                            strField = arrayFields.GetValue(1).ToString();
                            strSeries = loQuery.GetString(strField);

                            Array arrayOneRow = Array.CreateInstance(typeof(string), nColumnCount);
                            arrayOneRow.SetValue(strCategory, 0);
                            arrayOneRow.SetValue(strSeries, 1);

                            // 查询数值列的数据
                            for (j = 2; j < nColumnCount; j++)
                            {
                                strField = arrayFields.GetValue(j).ToString();
                                strTemp = loQuery.GetString(strField);
                                arrayOneRow.SetValue(strTemp, j);
                            }

                            int nIdx = SeriesList.IndexOf(strSeries);
                            if (nIdx < 0)
                            {
                                // 为新的Series添加
                                SeriesList.Add(strSeries);
                                List<Array> ListForCurSeries = new List<Array>();
                                ListForCurSeries.Add(arrayOneRow);
                                ListData.Add(ListForCurSeries);
                            }
                            else
                            {
                                // 添加到已有的Series
                                ListData[nIdx].Add(arrayOneRow);
                            }

                            // 下一条
                            loQuery.MoveNext();
                        }
                    }
                }
            }
            catch (Exception err)
            {
                ThreadLog.LogException(err);
                SeriesList = null;
                ListData = null;
            }
            finally
            {
            }

        }
    }
}