OA2021/DAL/ReportModelService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;

namespace DAL
{
   public class ReportModelService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<ReportModel> excuteReportModelSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<ReportModel>.excuteSql(new ReportModel(), "reportModel", sql, CommandType.Text, param);
        }

       /// <summary>
       /// LiuChengYi  2014/4/25
       /// 用于报表模型的绑定
       /// </summary>
       /// <returns></returns>
        public List<ReportModel> ReportGetAll()
        {
            return excuteReportModelSql(@"SELECT count([VisitCountry]) as num,
	                          [VisitCountry] as Category,
	                          '出访国家数据统计图' as sequence 
	                          from DelegationInfo  
	                          where [VisitCountry] is not null
	                          group by [VisitCountry]");
        }

       /// <summary>
       /// LiuChengYi   2014/4/25
       /// 用于团组和非团组利润的统计
       /// </summary>
       /// <returns></returns>
        public DataTable ReportProfitStatistics(string startTime, string endTime)
        {
            double tzIncome = 0.00;
            double tzOutlay = 0.00;
            double ftzIncome = 0.00;
            double ftzOutlay = 0.00;
            //收入

            DataTable IncomeDt = IncomeMoney(startTime, endTime);
            for (int i = 0; i < IncomeDt.Rows.Count; i++)
            {
                if (IncomeDt.Rows[i]["Category"].ToString().Equals("团组"))
                {
                    tzIncome += Convert.ToDouble(IncomeDt.Rows[i]["num"]);
                }
                else
                {
                    ftzIncome += Convert.ToDouble(IncomeDt.Rows[i]["num"]);
                }
            }
            //支出
            DataTable OutlayDt = OutlayMoney(startTime, endTime);
            for (int j = 0; j < OutlayDt.Rows.Count; j++)
            {

                if (OutlayDt.Rows[j]["Category"].ToString().Equals("团组"))
                {
                    tzOutlay += Convert.ToDouble(OutlayDt.Rows[j]["num"]);
                }
                else
                {
                    ftzOutlay += Convert.ToDouble(OutlayDt.Rows[j]["num"]);
                }
            }

            //团组总利润
            tzIncome = tzIncome - tzOutlay;
            //非团组总利润
            ftzIncome = ftzIncome - ftzOutlay;
            DataTable dt = IncomeDt.Copy();
            dt.Clear();
            DataRow dr = dt.NewRow();
            dt.Rows.Add(dr);
            dt.Rows[0]["num"] = Convert.ToDouble(tzIncome.ToString("F2"));
            dt.Rows[0]["CateGory"] = "团组";

            DataRow dr1 = dt.NewRow();
            dt.Rows.Add(dr1);
            dt.Rows[1]["num"] =Convert.ToDouble(ftzIncome.ToString("F2"));
            dt.Rows[1]["CateGory"] = "非团组";
           return dt;
        }

        /// <summary>
        /// LiuChengYi  2014/4/29
        /// 构造月份的数据字典
        /// 用于日常报表统计
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetListMonth()
        {
            Dictionary<string, string> ListMonth = new Dictionary<string, string>();
            ListMonth.Add("1", "0");
            ListMonth.Add("2", "0");
            ListMonth.Add("3", "0");
            ListMonth.Add("4", "0");
            ListMonth.Add("5", "0");
            ListMonth.Add("6", "0");
            ListMonth.Add("7", "0");
            ListMonth.Add("8", "0");
            ListMonth.Add("9", "0");
            ListMonth.Add("10", "0");
            ListMonth.Add("11", "0");
            ListMonth.Add("12", "0");
            return ListMonth;
        }

        /// <summary>
        /// LiuChengYi  2014/4/29
        /// 日常支付统计
        /// </summary>
        /// <returns></returns>
        public DataTable GetDailyDay(string companyId) 
        {
            int year = Convert.ToInt32(DateTime.Now.ToString("yyyy"));
            SqlParameter[] pars = 
            {  
                new SqlParameter("@companyId",companyId),
                new SqlParameter("@year",year)
            };
            DataTable dt = SqlHelper.TransferProcedure("DailyDay_Report", CommandType.StoredProcedure,pars);

            Dictionary<string, string> ListMonth = GetListMonth();
            for (int i = 0; i <dt.Rows.Count; i++)
            {
                ListMonth[dt.Rows[i]["Category"].ToString()] = dt.Rows[i]["Num"].ToString();
            }
            DataTable table = new DataTable();
            table = dt.Copy();
            table.Clear();
            foreach (KeyValuePair<string, string> category in ListMonth)
            {
                table.Rows.Add(category.Value,category.Key ,"日常支付报表");
            }
            return table;
        }

        /// <summary>
        /// 统计总利润  LiuChengYi  2014/07/29
        /// </summary>
        public DataTable GetCompanyProfitStatisticsReport(string companyId,string year)
        {
            //公司利润=团组收入-（团组支出-日常支出）
            #region 四川公司团组和非团组的每月利润
            SqlParameter[] pars = 
            {
                new SqlParameter("companyId",companyId)
            };

            //团组和非团组收入
            DataTable dt = IncomeMoneyByMonth(year);
            //构造每月收入集合
            Dictionary<string, string> ListMonth = GetListMonth();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ListMonth[dt.Rows[i]["Category"].ToString()] = dt.Rows[i]["Num"].ToString();
            }
            DataTable table = new DataTable();
            table = dt.Copy();
            table.Clear();
            foreach (KeyValuePair<string, string> category in ListMonth)
            {
                table.Rows.Add(category.Value, category.Key, "企业利润报表");
            }
            #endregion
            #region 每月支出 ，日常支出除外
            //团组和非团组支出
            DataTable OutlayDt = OutlayMoneyByMonth(year);
             //构造每月支出集合
            Dictionary<string, string> outlay = GetListMonth();
            for (int j = 0; j < OutlayDt.Rows.Count; j++)
            {
                outlay[OutlayDt.Rows[j]["Category"].ToString()] = OutlayDt.Rows[j]["Num"].ToString();
            }
            OutlayDt.Clear();
            foreach (KeyValuePair<string, string> category in outlay)
            {
                OutlayDt.Rows.Add(category.Value, category.Key, "企业利润报表");
            }
            #endregion
            
            #region 四川公司日常支付
            DataTable DailyDayTable = GetDailyDay("114");
            #endregion
            for (int i = 0; i < DailyDayTable.Rows.Count; i++)
            {
                //double d1 = Convert.ToDouble(table.Rows[i]["Num"]);
                //double d2 = Convert.ToDouble(DailyDayTable.Rows[i]["Num"]);
                //double d3 = d1 - d2;
                table.Rows[i]["Num"] = (Convert.ToDouble(table.Rows[i]["Num"]) - Convert.ToDouble(DailyDayTable.Rows[i]["Num"])-Convert.ToDouble(OutlayDt.Rows[i]["Num"])).ToString();
            }
            return table;
        }

        /// <summary>
        /// LiuChengYi  2014/5/5
        /// 构造天数的集合
        /// </summary>
        /// <param name="Months"></param>
        /// <returns></returns>
        public Dictionary<string, string> GetListDay(string startDate, string endDate)
        {
            List<Calendars> ListMonths = new CalendarsService().GetCalendarMonths(startDate,endDate);
            Dictionary<string, string> dictMonth = new Dictionary<string, string>();
            for (int i = 0; i < ListMonths.Count; i++)
            {
                dictMonth.Add(Convert.ToDateTime(ListMonths[i].CalendarDate).ToString("yyyy-MM-dd"),"");
            }
            return dictMonth;
        }

        /// <summary>
        /// LiuChengYi
        /// 员工考勤报表
        /// </summary>
        /// <param name="employeeId"></param>
        /// <param name="months"></param>
        /// <returns></returns>
        public DataTable EmployeeAttendance(int employeeId, string startDate, string endDate)
        {
            DataTable table = new DataTable();
            //根据员工Id和考勤月份获取员工考勤
            SqlParameter[] pars = 
            {
                new SqlParameter("@startDate",startDate),
                new SqlParameter("@endDate",endDate),
                new SqlParameter("@EmployeeId",employeeId)
            };
            DataTable dt = SqlHelper.TransferProcedure("EmployeeAttendance_Report", CommandType.StoredProcedure, pars);
            //填充已构造好的日期结合
            Dictionary<string, string> dictDay = GetListDay(startDate,endDate);
            if (dt.Rows.Count>0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string c = dt.Rows[i]["Num"].ToString().Replace(":", ".");
                    string b = Convert.ToDouble(c).ToString();
                    dictDay[dt.Rows[i]["Category"].ToString()] = b;
                }

                //构造报表的数据集

                table = dt.Copy();
                table.Clear();
                foreach (KeyValuePair<string, string> category in dictDay)
                {  
                    if (category.Value == "")
                    {
                        table.Rows.Add("0.00", category.Key, "打卡时间");
                    }
                    else
                    {

                        table.Rows.Add(category.Value, category.Key, "打卡时间");
                    }
                }
            }
            return table;
        }

        /// <summary>
        /// LiuChengYi
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public DataTable EmployeesRated(string startDate, string endDate, int employeeId) 
        {
            DataTable table = new DataTable();
            //根据员工Id和考勤月份获取员工考勤
            SqlParameter[] pars = 
            {
                new SqlParameter("@startTime",startDate),
                new SqlParameter("@endTime",endDate),
                new SqlParameter("@EmployeeId",employeeId)
            };
            DataTable dt = SqlHelper.TransferProcedure("EmployeesRated_Report", CommandType.StoredProcedure, pars);
            
            //填充已构造好的日期结合
            Dictionary<string, string> dictDay = GetListDay(startDate, endDate);
            //构造报表的数据集
            if (dt.Rows.Count > 0)
            {
                table = dt.Copy();
                table.Clear();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dictDay[dt.Rows[i]["Category"].ToString()] = dt.Rows[i]["Num"].ToString();
                    foreach (KeyValuePair<string, string> category in dictDay)
                    {
                        string Time = Convert.ToDateTime(category.Key).ToString("yyyy-MM-dd");
                        if (category.Value == "")
                        {
                            table.Rows.Add("0", Time, dt.Rows[i]["Sequence"].ToString());
                        }
                        else
                        {

                            table.Rows.Add(category.Value, Time, dt.Rows[i]["Sequence"].ToString());
                        }
                    }
                }
            }
            return table;
        }


       /// <summary>
        /// 团组与非团组的收入合计 LiuChengYi  2014/07/29
       /// </summary>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <returns></returns>
        public DataTable IncomeMoney(string startTime, string endTime) 
        {
            SqlParameter[] pars = 
            {
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            return SqlHelper.TransferProcedure("IncomeMoney", CommandType.StoredProcedure, pars);
        }

        /// <summary>
        /// 团组与非团组的支出合计 LiuChengYi  2014/07/29
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public DataTable OutlayMoney(string startTime, string endTime)
        {

            SqlParameter[] pars = 
            {
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime)
            };
            return SqlHelper.TransferProcedure("OutlayMoney", CommandType.StoredProcedure, pars);
        }

        /// <summary>
        /// LiuChengYi  2014/07/29
        /// 获取每月的企业利润（团组利润和非团组利润） 
        /// </summary>
        /// <returns></returns>
        public DataTable IncomeMoneyByMonth(string year) 
        {
            
            SqlParameter[] pars = 
            {
                new SqlParameter("@year",year)
            };
            return SqlHelper.TransferProcedure("IncomeMoneyByMonth", CommandType.StoredProcedure, pars);
        }

        /// <summary>
        /// LiuChengYi  2014/07/29
        /// 获取每月支出（团组利润和非团组利润，日常支付除外）
        /// </summary>
        /// <returns></returns>
        public DataTable OutlayMoneyByMonth(string year)
        {
        
            SqlParameter[] pars = 
            {
                new SqlParameter("@year",year)
            };
            return SqlHelper.TransferProcedure("OutlayMoneyByMonth", CommandType.StoredProcedure, pars);
        }

        /// <summary>
        /// LiuChengYi 2014/07/30
        /// 团组利润月份对比统计图
        /// </summary>
        /// <returns></returns>
        public DataTable MissionsProfitByMOnth(string year)
        {

            //公司利润=团组收入-（团组支出-日常支出）
            #region 四川公司团组和非团组的每月利润
            //团组和非团组收入
            DataTable Profitdt = IncomeMoneyByMonth(year);
            //构造每月收入集合
            Dictionary<string, string> zhenFuMonth = GetListMonth();
            Dictionary<string, string> FZFMonth = GetListMonth();
            for (int i = 0; i < Profitdt.Rows.Count; i++)
            {
                if (!Profitdt.Rows[i]["name"].ToString().Equals("非团组"))
                {
                    zhenFuMonth[Profitdt.Rows[i]["Category"].ToString()] = Profitdt.Rows[i]["Num"].ToString();
                }
                else 
                {
                    FZFMonth[Profitdt.Rows[i]["Category"].ToString()] = Profitdt.Rows[i]["Num"].ToString();
                }
                
            }
            
            #endregion
            #region 每月支出 ，日常支出除外
            //团组和非团组支出
            DataTable OutlayDt = OutlayMoneyByMonth(year);
            //构造每月支出集合
            Dictionary<string, string> ZFoutlay = GetListMonth();
            Dictionary<string, string> FZFoutlay = GetListMonth();
            for (int j = 0; j < OutlayDt.Rows.Count; j++)
            {
                if (!Profitdt.Rows[j]["name"].ToString().Equals("非团组"))
                {
                    ZFoutlay[OutlayDt.Rows[j]["Category"].ToString()] = OutlayDt.Rows[j]["Num"].ToString();
                }
                else
                {
                    FZFoutlay[OutlayDt.Rows[j]["Category"].ToString()] = OutlayDt.Rows[j]["Num"].ToString();
                }
            }
            DataTable table = new DataTable();
            table = Profitdt.Copy();
            table.Clear();
            foreach (KeyValuePair<string, string> category in zhenFuMonth)
            {
                foreach (KeyValuePair<string, string> zfoutlayDic in ZFoutlay)
                {
                    if (category.Key.ToString().Equals(zfoutlayDic.Key.ToString())) 
                    {
                        table.Rows.Add((Convert.ToDouble(category.Value.ToString()) - Convert.ToDouble(zfoutlayDic.Value.ToString())).ToString("F2"), category.Key, "企业利润报表", "团组");
                    }
                }
            }
            foreach (KeyValuePair<string, string> category in FZFMonth)
            {
                
                foreach (KeyValuePair<string, string> FzfoutlayDic in FZFoutlay)
                {
                    if (category.Key.ToString().Equals(FzfoutlayDic.Key.ToString()))
                    {
                        table.Rows.Add((Convert.ToDouble(category.Value.ToString()) - Convert.ToDouble(FzfoutlayDic.Value.ToString())).ToString("F2"), category.Key, "企业利润报表", "非团组");
                    }
                }
            }
            OutlayDt.Clear();
            #endregion



            return table;
        }

        /// <summary>
        /// 团组利润年对比图
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable MissionsProfitByYear(string year)
        {
            SqlParameter[] pars = 
            {
               new SqlParameter("@year",year)
            };
            DataTable dtIncome = SqlHelper.TransferProcedure("IncomeMoneyByYear", CommandType.StoredProcedure, pars);
            SqlParameter[] pras = 
            {
               new SqlParameter("@year",year)
            };
            DataTable dtOutlay = SqlHelper.TransferProcedure("OutlayMoneyByYear", CommandType.StoredProcedure, pras);
            //测试所用
            //DataRow dr = dtIncome.NewRow();
            //dr["num"] = "1500000";
            //dr["Category"] = "2013";
            //dr["name"] = "非团组";
            //dtIncome.Rows.Add(dr);

            DataTable dt = new DataTable();
            dt = dtIncome.Copy();
            dt.Clear();
            for (int i = 2; i >=0; i--)
            {
                DataRow dr1 = dt.NewRow();
                dr1["num"] = "0";
                dr1["Category"] =( Convert.ToInt32(year)-i).ToString();
                dr1["name"] = "团组";
                dt.Rows.Add(dr1);
                 DataRow dr2 = dt.NewRow();
                dr2["num"] = "0";
                dr2["Category"] =( Convert.ToInt32(year)-i).ToString();
                dr2["name"] = "非团组";
                dt.Rows.Add(dr2);
            }

            try
            {
                for (int i = 0; i < dtIncome.Rows.Count; i++)
                {
                    if(!dtIncome.Rows[i]["name"].Equals("非团组"))
                      dtIncome.Rows[i]["name"] = "团组";

                    for (int j = 0; j < dtOutlay.Rows.Count; j++)
                    {
                        if (!dtOutlay.Rows[j]["name"].Equals("非团组"))
                            dtOutlay.Rows[j]["name"] = "团组";
                        
                        if (dtIncome.Rows[i]["name"].ToString().Equals(dtOutlay.Rows[j]["name"].ToString()))
                        {
                            if (dtIncome.Rows[i]["Category"].ToString().Equals(dtOutlay.Rows[j]["Category"].ToString()))
                            {
                                for (int h = 0; h < dt.Rows.Count; h++)
                                {

                                    if (dtIncome.Rows[i]["name"].ToString().Equals(dt.Rows[h]["name"].ToString()) && dtIncome.Rows[i]["Category"].ToString().Equals(dt.Rows[h]["Category"].ToString()))
                                    {
                                        dt.Rows[h]["num"] = ((Convert.ToDouble(dtIncome.Rows[i]["num"].ToString()) - (Convert.ToDouble(dtOutlay.Rows[j]["num"].ToString()))).ToString());
                                    }

                                }
                            }
                            else 
                            {
                                for (int h = 0; h < dt.Rows.Count; h++)
                                {

                                    if (dtIncome.Rows[i]["name"].ToString().Equals(dt.Rows[h]["name"].ToString()) && dtIncome.Rows[i]["Category"].ToString().Equals(dt.Rows[h]["Category"].ToString()))
                                    {
                                        dt.Rows[h]["num"] = (Convert.ToDouble(dtIncome.Rows[i]["num"].ToString()));
                                    }

                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                
                throw;
            }
            
            return dt;
        }

       /// <summary>
       /// 团组总利润
       /// </summary>
       /// <param name="year"></param>
       /// <returns></returns>
        public DataTable Total_MissionsProfitByYear(string year) 
        {
            DataTable dt = MissionsProfitByYear(year);
            //重新构造DataTable 
            DataTable dtTotal = new DataTable();
            DataColumn dc =null;
               dc = dtTotal.Columns.Add("Category", Type.GetType("System.String"));
               dc = dtTotal.Columns.Add("num", Type.GetType("System.String"));

            DataRow dr = dtTotal.NewRow();
            dr["Category"] = (Convert.ToInt32(year) - 2).ToString(); 
            dr["num"] = "";

            DataRow dr1 = dtTotal.NewRow();
            dr1["Category"] = (Convert.ToInt32(year) - 1).ToString(); ;
            dr1["num"] = "";

            DataRow dr2 = dtTotal.NewRow();
            dr2["Category"] = year;
            dr2["num"] = "";

            dtTotal.Rows.Add(dr);
            dtTotal.Rows.Add(dr1);
            dtTotal.Rows.Add(dr2);
            
            Double total=0.00;
            //循环填充数据
            for (int i = 0; i < dtTotal.Rows.Count; i++)
                {
                    total = 0.00;
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if(dt.Rows[j]["Category"].ToString().Equals(dtTotal.Rows[i]["Category"].ToString()))
                        {
                            total +=Convert.ToDouble(dt.Rows[j]["num"].ToString());
                        }
                    }
                    dtTotal.Rows[i]["num"] = total.ToString();
                    
                }
            return dtTotal;
        }


        /// <summary>
        /// LiuChengYi 2014/7/31
        /// 出访国家统计
        /// </summary>
        /// <returns></returns>
        public DataTable DelegationByCount(string year)
        {
            DelegationInfoService difs = new DelegationInfoService();
            DataTable dt =difs.ReportDelegationInfo(year);

            return HotelbyDelegionsbySort(dt);
        }

        /// <summary>
        /// LiuChengYi 2014/7/31
        /// 用于预定酒店统计
        /// </summary>
        /// <returns></returns>
        public DataTable HotelReservationsListByCount(string year)
        {
            DelegationInfoService difs = new DelegationInfoService();
            HotelReservationsService hrs = new HotelReservationsService();
            DataTable dt = hrs.ReportHotelReservationsInfo(year);
            
            return  HotelbyDelegionsbySort(dt);
        }
         
        /// <summary>
        /// 排序
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="type"></param>
        /// <param name="year"></param>
        /// <returns></returns>

        public DataTable HotelbyDelegionsbySort(DataTable dt )
        {
            DataView dv = dt.DefaultView;
            dv.Sort = "num desc";
            dt = dv.ToTable();
            return dt;
        }

        public string XmlReport(DataTable dt,string type,int year)
        {
            string xmlStr = "";
            #region 初始化月份数据
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    if(dt.Rows[i][1].ToString().Equals("1"))
            //    {
            //        dt.Rows[i][1] = "一月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("2"))
            //    {
            //        dt.Rows[i][1] = "二月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("3"))
            //    {
            //        dt.Rows[i][1] = "三月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("4"))
            //    {
            //        dt.Rows[i][1] = "四月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("5"))
            //    {
            //        dt.Rows[i][1] = "五月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("6"))
            //    {
            //        dt.Rows[i][1] = "六月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("7"))
            //    {
            //        dt.Rows[i][1] = "七月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("8"))
            //    {
            //        dt.Rows[i][1] = "八月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("9"))
            //    {
            //        dt.Rows[i][1] = "九月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("10"))
            //    {
            //        dt.Rows[i][1] = "十月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("11"))
            //    {
            //        dt.Rows[i][1] = "十一月";
            //    }
            //    if (dt.Rows[i][1].ToString().Equals("12"))
            //    {
            //        dt.Rows[i][1] = "十二月";
            //    }
            //}
            #endregion
            switch (type)
            {
                case "团组月份利润":
                    xmlStr = MissionsProfitDToXmlByMonthOrLine(dt);
                    break;
                case "团/非团组年利润":
                    xmlStr = MissionsProfitDToXmlByYearOrLine(dt, year);
                    break;
                case "查询团组年利润":
                    xmlStr = Total_MissionsProfitByYearDtToXmlByBar(dt);
                    break;
                case "出访国家统计":
                    xmlStr = DataTableToXmlByBar(dt);
                    break;
                case "预订酒店统计":
                    xmlStr = DataTableToXmlByBar(dt);
                    break;
                default:
                    break;
            }
            return xmlStr;
        }
        
        /// <summary>
        /// LiuChengYi  2014/07/29
        /// 转换成XML格式字符串 应用于报表柱状图
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string DataTableToXmlByBar(DataTable dt)
        {
            StringBuilder strXml = new StringBuilder();
            strXml.AppendLine("<JSChart>");
            strXml.AppendLine("<dataset type=\"bar\">");
            for (int i = 0; i < dt.Rows.Count; i++ )
            {
                //dt.Rows[i]["Category"].ToString().Replace("&", " ")
                if (i<10)
                {
                    if (dt.Rows[i]["Category"].ToString().Length > 5)
                    {
                        dt.Rows[i]["Category"] = dt.Rows[i]["Category"].ToString().Substring(0, 5);
                        dt.Rows[i]["Category"] = "" + (i + 1) + "" + "." + dt.Rows[i]["Category"].ToString() + "...";
                    }
                    else 
                    {
                        dt.Rows[i]["Category"] = "" + (i + 1) + "" + "." + dt.Rows[i]["Category"].ToString();
                    }
                    
                    strXml.AppendLine("<data unit=" + "\"" +  dt.Rows[i]["Category"].ToString().Replace("&", " ") + "\"  " + "value=" + "\"" + dt.Rows[i]["num"].ToString() + "\"/>");
                }
               
            }
            strXml.AppendLine("</dataset>");
            //strXml.AppendLine("<colorset>");
            //strXml.AppendLine("<color value= \"#49A5EC\" />");
            //strXml.AppendLine("<color value= \"#61B320\" />");
            //strXml.AppendLine("</colorset>");
            strXml.AppendLine("<optionset>");
            strXml.AppendLine("<option set=\"setSize\"  value=\"800,300\" />");
            strXml.AppendLine("<option set=\"setBarSpacingRatio\"  value=\"20\" />");
            strXml.AppendLine("<option set=\"setBarValues\"  value=\"true\" />");
            strXml.AppendLine("<option set=\"setTitle\"  value=\"'出访国家/预订酒店统计报表'\" />");
            strXml.AppendLine("<option set=\"setAxisNameX\"  value=\"'  '\" />");
            //strXml.AppendLine("<option set=\"setTextPaddingBottom\"  value=\"0\" />");
            strXml.AppendLine("<option set=\"setAxisNameY\"  value=\"'次数'\" />");
            strXml.AppendLine("<option set=\"setAxisPaddingLeft\"  value=\"80\" />");
            
            strXml.AppendLine("</optionset>");
            strXml.AppendLine("</JSChart>");
            return strXml.ToString();
        }

        /// <summary>
        /// LiuChengYi   2014/07/31
        /// 用于团组利润统计
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public string Total_MissionsProfitByYearDtToXmlByBar(DataTable dt)
        {
            StringBuilder strXml = new StringBuilder();
            strXml.AppendLine("<JSChart>");
            strXml.AppendLine("<dataset type=\"bar\">");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strXml.AppendLine("<data unit=" + "\"" + dt.Rows[i]["Category"].ToString() + "\"  " + "value=" + "\"" + dt.Rows[i]["num"].ToString() + "\"/>");
            }
            strXml.AppendLine("</dataset>");
            strXml.AppendLine("<colorset>");
            strXml.AppendLine("<color value= \"#49A5EC\" />");
            strXml.AppendLine("<color value= \"#61B320\" />");
            strXml.AppendLine("<color value= \"#ECB075\" />");
            strXml.AppendLine("</colorset>");
            strXml.AppendLine("<optionset>");
            strXml.AppendLine("<option set=\"setTitle\"  value=\"'团组年份统计报表'\" />");
            strXml.AppendLine("<option set=\"setAxisNameX\"  value=\"'年份'\" />");
            strXml.AppendLine("<option set=\"setAxisNameY\"  value=\"'￥'\" />");
            strXml.AppendLine("<option set=\"setAxisPaddingLeft\"  value=\"80\" />");
            strXml.AppendLine("<option set=\"setAxisPaddingBottom\"  value=\"80\" />");
            strXml.AppendLine("</optionset>");
            strXml.AppendLine("</JSChart>");
            return strXml.ToString();
        }

        /// <summary>
        ///  LiuChengYi  2014/07/29
        /// 转换成四川公司团组月份统计图XML格式字符串 应用于报表折线图
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string MissionsProfitDToXmlByMonthOrLine(DataTable dt)
        {
            StringBuilder strXml = new StringBuilder();
            strXml.AppendLine("<JSChart>");
            string strName = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (!strName.Equals(dt.Rows[i]["name"].ToString())) { 
                strName = dt.Rows[i]["name"].ToString();
                if (!strName.Equals("非团组"))
                {
                    strXml.AppendLine("<dataset type=\"line\"  id=\"blue\"> ");
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (!dt.Rows[j]["name"].ToString().Equals("非团组")) {
                            strXml.AppendLine("<data unit=" + "\"" + dt.Rows[j]["Category"].ToString() + "\"  " + "value=" + "\"" + dt.Rows[j]["num"].ToString() + "\"/>");
                        }
                    }
                    strXml.AppendLine("</dataset>");
                    
                }
                else
                {
                    strXml.AppendLine("<dataset type=\"line\"  id=\"green\"> ");
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[j]["name"].ToString().Equals("非团组"))
                        {
                            strXml.AppendLine("<data unit=" + "\"" + dt.Rows[j]["Category"].ToString() + "\"  " + "value=" + "\"" + dt.Rows[j]["num"].ToString() + "\"/>");
                        }
                     }
                    strXml.AppendLine("</dataset>");
                }
               }
            }
            strXml.AppendLine("<colorset>");
            strXml.AppendLine("<color value= \"#0070C0\" />");
            strXml.AppendLine("<color value= \"#C55A11\" />");
            strXml.AppendLine("</colorset>");
            strXml.AppendLine("<optionset>");
            strXml.AppendLine("<option set=\"setSize\"  value=\"500,300\" />");
            strXml.AppendLine("<option set=\"setAxisPaddingLeft\"  value=\"100\" />");
            strXml.AppendLine("<option set=\"setAxisPaddingBottom\"  value=\"50\" />");
            strXml.AppendLine("<option set=\"setTitle\"  value=\"'团组月份统计报表'\" />");
            strXml.AppendLine("<option set=\"setGraphExtend\"  value=\"true\" />");
            strXml.AppendLine("<option set=\"setShowXValues\"  value=\"false\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[1,'一月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[2,'二月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[3,'三月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[4,'四月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[5,'五月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[6,'六月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[7,'七月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[8,'八月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[9,'九月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[10,'十月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[11,'十一月']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[12,'十二月']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[1,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[2,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[3,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[4,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[5,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[6,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[7,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[8,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[9,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[10,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[11,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[12,' ']\" />");

            //strXml.AppendLine("<option set=\"setGraphLabel\"  value=\"'团组'\" />");
            //strXml.AppendLine("<option set=\"setLegendShow\"  value=\"true\" />");
            strXml.AppendLine("<option set=\"setFlagColor\"  value=\"'#9D16FC'\" />");
            strXml.AppendLine("<option set=\"setLineColor\"  value=\"'#0070C0','blue'\" />");
            strXml.AppendLine("<option set=\"setLineColor\"  value=\"'#C55A11','green'\" />");
            //strXml.AppendLine("<option set=\"setLegendForLine\"  value=\"'blue','团组'\" />");
            //strXml.AppendLine("<option set=\"setLegendForLine\"  value=\"'green' ,'非团组'\"/>");
            strXml.AppendLine("<option set=\"setAxisNameX\"  value=\"'月份'\" />");
            strXml.AppendLine("<option set=\"setAxisNameY\"  value=\"'￥'\" />");
            strXml.AppendLine("</optionset>");
            strXml.AppendLine("</JSChart>");
            return strXml.ToString();
        }

        /// <summary>
        ///  LiuChengYi  2014/07/29
        /// 转换成四川公司团组（团组/非团组）年份统计图XML格式字符串 应用于报表折线图
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string MissionsProfitDToXmlByYearOrLine(DataTable dt,int year)
        {
            StringBuilder strXml = new StringBuilder();
            strXml.AppendLine("<JSChart>");
            string strName = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (!strName.Equals(dt.Rows[i]["name"].ToString()))
                {
                    strName = dt.Rows[i]["name"].ToString();
                    if (!strName.Equals("非团组"))
                    {
                        strXml.AppendLine("<dataset type=\"line\"  id=\"blue\"> ");
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            if (!dt.Rows[j]["name"].ToString().Equals("非团组"))
                            {
                                strXml.AppendLine("<data unit=" + "\"" + dt.Rows[j]["Category"].ToString() + "\"  " + "value=" + "\"" + dt.Rows[j]["num"].ToString() + "\"/>");
                            }
                        }
                        strXml.AppendLine("</dataset>");

                    }
                    else
                    {
                        strXml.AppendLine("<dataset type=\"line\"  id=\"green\"> ");
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            if (dt.Rows[j]["name"].ToString().Equals("非团组"))
                            {
                                strXml.AppendLine("<data unit=" + "\"" + dt.Rows[j]["Category"].ToString() + "\"  " + "value=" + "\"" + dt.Rows[j]["num"].ToString() + "\"/>");
                            }
                        }
                        strXml.AppendLine("</dataset>");
                    }
                }
            }
            strXml.AppendLine("<colorset>");
            strXml.AppendLine("<color value= \"#0070C0\" />");
            strXml.AppendLine("<color value= \"#C55A11\" />");
            strXml.AppendLine("</colorset>");
            strXml.AppendLine("<optionset>");
            strXml.AppendLine("<option set=\"setSize\"  value=\"500,300\" />");
            strXml.AppendLine("<option set=\"setAxisPaddingLeft\"  value=\"100\" />");
            strXml.AppendLine("<option set=\"setAxisPaddingBottom\"  value=\"50\" />");
            strXml.AppendLine("<option set=\"setTitle\"  value=\"'团组年利润统计报表'\" />");
            strXml.AppendLine("<option set=\"setGraphExtend\"  value=\"true\" />");
            strXml.AppendLine("<option set=\"setShowXValues\"  value=\"false\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[2012,'2012年']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[2013,'2013年']\" />");
            strXml.AppendLine("<option set=\"setLabelX\"  value=\"[2014,'2014年']\" />");
        
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[2012,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[2013,' ']\" />");
            strXml.AppendLine("<option set=\"setTooltip\"  value=\"[2014,' ']\" />");
       
            //strXml.AppendLine("<option set=\"setGraphLabel\"  value=\"'团组'\" />");
            //strXml.AppendLine("<option set=\"setLegendShow\"  value=\"true\" />");
            strXml.AppendLine("<option set=\"setFlagColor\"  value=\"'#9D16FC'\" />");
            strXml.AppendLine("<option set=\"setLineColor\"  value=\"'#0070C0','blue'\" />");
            strXml.AppendLine("<option set=\"setLineColor\"  value=\"'#C55A11','green'\" />");
            //strXml.AppendLine("<option set=\"setLegendForLine\"  value=\"'blue','团组'\" />");
            //strXml.AppendLine("<option set=\"setLegendForLine\"  value=\"'green' ,'非团组'\"/>");
            strXml.AppendLine("<option set=\"setAxisNameX\"  value=\"'年份'\" />");
            strXml.AppendLine("<option set=\"setAxisNameY\"  value=\"'￥'\" />");
            strXml.AppendLine("</optionset>");
            strXml.AppendLine("</JSChart>");
            return strXml.ToString();
        }

        #region dataTable转换成Json格式
        /// <summary>      
        /// dataTable转换成Json格式      
        /// </summary>      
        /// <param name="dt"></param>      
        /// <returns></returns>      
        public  string ToJson(DataTable dt)
        {
            StringBuilder jsonBuilder = new StringBuilder();
            jsonBuilder.Append("{\"report");
            jsonBuilder.Append(dt.TableName.ToString());
            jsonBuilder.Append("\":[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                jsonBuilder.Append("{");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    jsonBuilder.Append("\"");
                    jsonBuilder.Append(dt.Columns[j].ColumnName);
                    jsonBuilder.Append("\":\"");
                    jsonBuilder.Append(dt.Rows[i][j].ToString());
                    jsonBuilder.Append("\",");
                }
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
                jsonBuilder.Append("},");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]");
            jsonBuilder.Append("}");
            return jsonBuilder.ToString();
        }

        #endregion dataTable转换成Json格式   
    }
}