C#中编写sqlserver中自定义函数，实现复杂报表

有这样一张报表，如图：

要求用GridView显示，所以查询的结果要么是一个临时表或者是一个查询视图，想到sql2005中加入CLR的支持，有关CLR的操作请看体验：用C#写存储过程(VS.NET 2005) 或者在sqlserver2005中部署C#编写的自定义函数 ，实现的类如下

   1using System;
   2using System.Collections.Generic;
   3using System.Data;
   4using System.Data.SqlClient;
   5using System.Data.SqlTypes;
   6using Microsoft.SqlServer.Server;
   7using System.Collections;
   8
   9public partial class WYTableFunction
  10{
  11      Fun_FactIncome_CLR#region Fun_FactIncome_CLR
  12    //这个特性定义了一个sql表值函数，此函数返回的表的定义为：String nvarchar(200)
  13    //并且指定了填充这个表的行的方法是FillRow 方法
  14    //注意这个方法返回的一定是一个IEnumerable类型的，并且为公开，静态，这个方法的入参就是sql函数的入参
  15    [SqlFunction(DataAccess = DataAccessKind.Read, TableDefinition = @"tid    int ,unitname nvarchar(100),itemname nvarchar(100),
  16                jan    decimal(18,2),feb    decimal(18,2),    mar    decimal(18,2),    apr    decimal(18,2),
  17                may    decimal(18,2),jun    decimal(18,2),    jul    decimal(18,2),    aug    decimal(18,2),
  18                sep    decimal(18,2),oct    decimal(18,2),    nov    decimal(18,2),    dec    decimal(18,2),
  19                total decimal(18,2),    flag int ", FillRowMethodName = "FillRow3")]
  20    public static IEnumerable Fun_FactIncome_CLR(int iYear, string CompanyID, int type)
  21    {
  22        List<DataRow> rowList = new List<DataRow>();
  23        DataTable dt = new DataTable();
  24        表结构#region 表结构
  25        DataColumn col = new DataColumn("tid", typeof(int));
  26        dt.Columns.Add(col);
  27        dt.Columns.Add("unitname", typeof(string));
  28        dt.Columns.Add("itemname", typeof(string));
  29
  30        AddColumns(ref dt, new string[] { "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec", "total" });
  31
  32        col = new DataColumn("flag", typeof(int));
  33        col.DefaultValue = 0;
  34        dt.Columns.Add(col);
  35
  36        col = new DataColumn("myTemp", typeof(decimal));
  37        col.Expression = "jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec";
  38        dt.Columns.Add(col);
  39        #endregion
  40        if (type == 0)//总部查询
  41        {
  42            string CompanyList = GetCompanyList(CompanyID);
  43            string UnitName = "";
  44            SearchCompany(iYear, CompanyID, rowList, ref dt, CompanyList, UnitName);
  45        }
  46        else if (type == 1)//区域查询
  47        {
  48            string CompanyList = GetCompanyList(CompanyID);
  49            string UnitName = "";
  50            SearchRegion(iYear, CompanyID, rowList, ref dt, CompanyList, UnitName);
  51        }
  52        else if (type == 2)//所有楼盘
  53        {
  54            string CompanyList = GetCompanyList(CompanyID);
  55            string UnitName = "";
  56            SearchBuilding(iYear, CompanyID, rowList, ref dt, CompanyList, UnitName);
  57        }
  58        else if (type == 3)//自定义楼盘查询
  59        {
  60            string CompanyList = GetUnitList(CompanyID);
  61            string UnitName = "";
  62            dt = SearchBuildingDef(iYear, rowList, dt, CompanyList);
  63        }
  64        else if (type == 4)//自定义区域查询
  65        {
  66            string CompanyList = GetUnitList(CompanyID);
  67            string UnitName = "";
  68            SearchRegionDef(iYear, CompanyID, rowList, ref dt, CompanyList, UnitName);
  69        }
  70        return rowList as IEnumerable;
  71        //返回一个string 数组，这个数组符合IEnumerable接口，当然你也可以返回hashtable等类型。
  72
  73    }
  74
  75    //自定义楼盘查询
  76    private static DataTable SearchBuildingDef(int iYear, List<DataRow> rowList, DataTable dt, string CompanyList)
  77    {
  78        数据查询#region 数据查询
  79
  80        string Err = null;
  81
  82        //1、服务费收入(本年度)[包括应收部分和优惠]
  83        string sql = @"select sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
  84                                    from finance_preceipt fp 
  85                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
  86                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
  87                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
  88                                    join system_framework as sf on sf.unitid=fp.companyid
  89                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
  90                                    and fp.companyid in ({1})
  91                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
  92                        union all
  93                                    select  sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
  94                                    from Pb_Genledger fp 
  95                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
  96                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
  97                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
  98                                    join system_framework as sf on sf.unitid=fp.companyid
  99                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 100                                    and fp.companyid in ({1})
 101                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate);";
 102        //2、服务费收入(往年)[包括应收部分和优惠]
 103        sql += @"select sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 104                                    from finance_preceipt fp 
 105                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 106                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 107                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 108                                    join system_framework as sf on sf.unitid=fp.companyid
 109                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 110                                    and fp.companyid in ({1})
 111                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
 112                        union all
 113                                    select  sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 114                                    from Pb_Genledger fp 
 115                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 116                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 117                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 118                                    join system_framework as sf on sf.unitid=fp.companyid
 119                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 120                                    and fp.companyid in ({1})
 121                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate);";
 122        //3、服务费收入(预收)
 123        sql += @"    select sf.UnitName,se.[Name] as itemname,month(fi.gatherdate) as [Month],sum(fi.incomeMoney) as TotalMoney    
 124                                from finance_rmoney fr 
 125                                join finance_income fi on fr.rmoneyid = fi.rmoneyid
 126                                join finance_chargeitemset as fc on fc.itemid=fr.itemid
 127                                join Sys_Exestype  as se on se.ExecTypeID=fc.ExesTypeID 
 128                                join system_framework as sf on sf.unitid=fr.companyid
 129                                where fi.incomemoney > 0 and year(fi.gatherdate) = {0} and fc.ExesTypeID=7
 130                                and fr.companyid in ({1})    
 131                                group by sf.unitcode,sf.UnitName,se.[Name],month(fi.gatherdate);";
 132
 133        //4、计划收入
 134        /**//*sql += @"select  '所有区域' as UnitName,    sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
 135                        sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
 136                        sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
 137                        sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as dec
 138                    from plan_income where planyear = {0} and companyID in ({1});";*/
 139        sql += @"select '所有楼盘',sum(p.jan) as jan ,sum(p.feb) as feb,sum(p.mar) as mar,sum(p.apr) as apr,sum(p.may) as may,
 140                        sum(p.jun) as jun, sum(p.jul) as jul,sum(p.aug) as aug,sum(p.sep) as sep, sum(p.oct) as oct,sum(p.nov) as nov, sum(p.[dec]) as [dec]
 141                        from (
 142                        select sf.unitcode,sf.UnitName, sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
 143                        sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
 144                        sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
 145                        sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as [dec]
 146                        from plan_income 
 147                        join system_framework as sf on sf.unitid=plan_income.companyid
 148                        where planyear = {0} and companyID in ({1})
 149                        group by sf.unitcode,sf.UnitName
 150                        ) as p;";
 151        //5、其他服务收入（除服务费以外）                                                
 152        sql += @"select sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 153                                from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 154                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 155                                left join finance_chargeitemset ft on ft.itemid=fa.itemid
 156                                left join Sys_ExesGenre on Sys_ExesGenre.ExesGenreID=ft.ExesGenreID
 157                                join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 158                                left join system_framework as sf on sf.unitid=fa.companyid 
 159                                where year(fp.gatherdate)= {0} and  fa.itemtype <> 7 and Sys_ExesGenre.ExesGenreID=1
 160                                    and fp.companyid in ({1})
 161                                group by sf.unitcode,sf.unitname, se.[Name] ,month(fp.gatherdate) ;";
 162        //6、空置房（本年）        
 163        sql += @"select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 164                                from finance_preceipt fp 
 165                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 166                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 167                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 168                                left join system_framework as sf on sf.unitid=fa.companyid 
 169                                where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 170                                and fa.itemtype=7 and fp.companyid in ({1}) 
 171                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate) ;";
 172        //7、空置房（往年）        
 173        sql += @"select sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 174                                from finance_preceipt fp 
 175                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 176                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 177                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 178                                left join system_framework as sf on sf.unitid=fa.companyid 
 179                                where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 180                                and fa.itemtype=7 and fp.companyid in ({1}) 
 181                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate) ;";
 182        //8、空置房（预收）    
 183        sql += @"select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 184                            from finance_preceipt fp 
 185                            join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 186                            join finance_areceivable fa on far.areceivableid = fa.areceivableid
 187                            join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 188                            left join system_framework as sf on sf.unitid=fa.companyid 
 189                            where fa.GainMoney>0 and year(fp.gatherdate)= {0} and year(fa.enddate) > {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 190                            and fa.itemtype=7 and fp.companyid in ({1}) 
 191                            group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate) ;";
 192        //UnitName = sqlHead;
 193        sql = string.Format(sql, iYear, CompanyList);
 194
 195        // throw new Exception();
 196        DataSet ds = AmbitsWY_CLR.Globe.ExecSqlForDataSet(sql, out Err);
 197
 198
 199        if (Err != null)
 200        {
 201            throw new Exception("错误01:" + Err + "\r\n语句：\r\n" + sql);
 202
 203        }
 204
 205        //col = new DataColumn("myTemp", typeof(decimal));
 206        //col.Expression = "jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec";
 207        //dt.Columns.Add(col);
 208        #endregion
 209
 210        服务费收入#region 服务费收入
 211        //本年度
 212        DataRow row = dt.NewRow();
 213        row["UnitName"] = "服务费收入(本年度)";
 214        dt.Rows.Add(row);
 215        InsertData(ref dt, ds.Tables[0], 1);
 216        AddSum(ref dt, "①", "小计", 11, "flag = 1 ");
 217
 218        //往年度
 219        row = dt.NewRow();
 220        row["UnitName"] = "服务费收入(往年度)";
 221        dt.Rows.Add(row);
 222        InsertData(ref dt, ds.Tables[1], 2);
 223        AddSum(ref dt, "②", "小计", 12, "flag = 2 ");
 224
 225        //预收款
 226        row = dt.NewRow();
 227        row["UnitName"] = "服务费收入(预收款)";
 228        dt.Rows.Add(row);
 229        InsertData(ref dt, ds.Tables[2], 3);
 230        AddSum(ref dt, "③", "小计", 13, "flag = 3 ");
 231
 232        //合计
 233        AddSum(ref dt, "所有楼盘服务费(④=①+②+③)", "合计", 7, "Flag in (11,12,13)");
 234
 235
 236        //本年收入差异率
 237        CalculatePercent(ref dt, ds.Tables[3]);
 238
 239        #endregion
 240
 241        其他服务收入#region 其他服务收入
 242        row = dt.NewRow();
 243        row["UnitName"] = "其他服务收入";
 244        dt.Rows.Add(row);
 245        InsertData(ref dt, ds.Tables[4], 8);
 246        AddSum(ref dt, "⑤", "小计", 18, "flag = 8 ");
 247        AddSum(ref dt, "所有楼盘总收入(⑥=④+⑤)", "共计", 18, "flag in (7,8) ");
 248        #endregion
 249
 250        空置房#region 空置房
 251        //本年度
 252        row = dt.NewRow();
 253        row["UnitName"] = "空置房(本年度)";
 254        dt.Rows.Add(row);
 255        InsertData(ref dt, ds.Tables[5], 4);
 256        AddSum(ref dt, "⑦", "小计", 4, "flag = 4 ");
 257
 258        //往年度
 259        row = dt.NewRow();
 260        row["UnitName"] = "空置房(往年度)";
 261        dt.Rows.Add(row);
 262        InsertData(ref dt, ds.Tables[6], 5);
 263        AddSum(ref dt, "⑧", "小计", 15, "flag = 5 ");
 264
 265        //预收款
 266        row = dt.NewRow();
 267        row["UnitName"] = "空置房(预收款)";
 268        dt.Rows.Add(row);
 269        InsertData(ref dt, ds.Tables[7], 6);
 270        AddSum(ref dt, "⑨", "小计", 16, "flag = 6 ");
 271        dt.AcceptChanges();
 272        for (int i = 0; i < dt.Rows.Count; i++)
 273        {
 274            dt.Rows[i]["tid"] = i + 1;
 275            DataRow row2 = dt.Rows[i];
 276            if (AmbitsWY_CLR.Globe.ConvertToInt(row2["Flag"]) > 0)
 277                row2["Total"] = row2["myTemp"];
 278            else
 279                row2["Total"] = DBNull.Value;//空值
 280            rowList.Add(dt.Rows[i]);
 281        }
 282
 283        dt.Columns.Remove("myTemp");
 284
 285        #endregion
 286        return dt;
 287    }
 288    //自定义区域查询
 289    private static void SearchRegionDef(int iYear, string CompanyID, List<DataRow> rowList, ref DataTable dt, string CompanyList, string UnitName)
 290    {
 291        数据查询#region 数据查询
 292        string Err = null;
 293        //1、服务费收入(本年度)[包括应收部分和优惠]
 294        string sql = @"select  (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 295                                    a.itemname,a.[month],a.[totalmoney]
 296                                    from (select sf.unitcode,sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 297                                    from finance_preceipt fp 
 298                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 299                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 300                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 301                                    join system_framework as sf on sf.unitid=fp.companyid
 302                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 303                                    and fp.companyid in ({1})
 304                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
 305                                    ) as a join system_framework as b on a.unitcode=b.unitcode
 306
 307                        union all
 308                                    select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 309                                    a.itemname,a.[month],a.[totalmoney]
 310                                    from (select  sf.unitcode,sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 311                                    from Pb_Genledger fp 
 312                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 313                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 314                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 315                                    join system_framework as sf on sf.unitid=fp.companyid
 316                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 317                                    and fp.companyid in ({1})
 318                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate)
 319                                    ) as a join system_framework as b on a.unitcode=b.unitcode;";
 320        //2、服务费收入(往年)[包括应收部分和优惠]
 321        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 322                                    a.itemname,a.[month],a.[totalmoney]
 323                                    from (select sf.unitcode,sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 324                                    from finance_preceipt fp 
 325                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 326                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 327                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 328                                    join system_framework as sf on sf.unitid=fp.companyid
 329                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 330                                    and fp.companyid in ({1})
 331                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
 332                                    ) as a join system_framework as b on a.unitcode=b.unitcode
 333
 334                        union all
 335                                    select  (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 336                                    a.itemname,a.[month],a.[totalmoney]
 337                                    from (select  sf.unitcode,sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 338                                    from Pb_Genledger fp 
 339                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 340                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 341                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 342                                    join system_framework as sf on sf.unitid=fp.companyid
 343                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 344                                    and fp.companyid in ({1})
 345                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate)
 346                                    ) as a join system_framework as b on a.unitcode=b.unitcode;";
 347        //3、服务费收入(预收)
 348        sql += @"    select  (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 349                                a.itemname,a.[month],a.[totalmoney]
 350                                from (
 351                                select sf.unitcode,sf.UnitName,se.[Name] as itemname,month(fi.gatherdate) as [Month],sum(fi.incomeMoney) as TotalMoney    
 352                                from finance_rmoney fr 
 353                                join finance_income fi on fr.rmoneyid = fi.rmoneyid
 354                                join finance_chargeitemset as fc on fc.itemid=fr.itemid
 355                                join Sys_Exestype  as se on se.ExecTypeID=fc.ExesTypeID 
 356                                join system_framework as sf on sf.unitid=fr.companyid
 357                                where fi.incomemoney > 0 and year(fi.gatherdate) = {0} and fc.ExesTypeID=7
 358                                and fr.companyid in ({1})    
 359                                group by sf.unitcode,sf.UnitName,se.[Name],month(fi.gatherdate)
 360                                ) as a join system_framework as b on a.unitcode=b.unitcode;";
 361
 362        //4、计划收入
 363        /**//*sql += @"select  '所有区域' as UnitName,    sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
 364                        sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
 365                        sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
 366                        sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as dec
 367                    from plan_income where planyear = {0} and companyID in ({1});";*/
 368        sql += @"select '所有区域',sum(p.jan) as jan ,sum(p.feb) as feb,sum(p.mar) as mar,sum(p.apr) as apr,sum(p.may) as may,
 369                                sum(p.jun) as jun, sum(p.jul) as jul,sum(p.aug) as aug,sum(p.sep) as sep, sum(p.oct) as oct,sum(p.nov) as nov, sum(p.[dec]) as [dec]
 370                                from (
 371                                select b.punitcode as unitcode,(select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 372                                a.jan,a.feb,a.mar,a.apr,a.may,a.jun,a.jul,a.aug,a.sep,a.oct,a.nov,a.[dec]
 373                                from (
 374                                select sf.unitcode,sf.UnitName, sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
 375                                sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
 376                                sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
 377                                sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as [dec]
 378                                from plan_income 
 379                                join system_framework as sf on sf.unitid=plan_income.companyid
 380                                where planyear = {0} and companyID in ({1})
 381                                group by sf.unitcode,sf.UnitName
 382                                ) as a join system_framework as b on a.unitcode=b.unitcode
 383                                ) as p;";
 384        //5、其他服务收入（除服务费以外）                                                
 385        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 386                                a.itemname,a.[month],a.[totalmoney]
 387                                from (
 388                                select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 389                                from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 390                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 391                                left join finance_chargeitemset ft on ft.itemid=fa.itemid
 392                                left join Sys_ExesGenre on Sys_ExesGenre.ExesGenreID=ft.ExesGenreID
 393                                join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 394                                left join system_framework as sf on sf.unitid=fa.companyid 
 395                                where year(fp.gatherdate)= {0} and  fa.itemtype <> 7 and Sys_ExesGenre.ExesGenreID=1
 396                                    and fp.companyid in ({1})
 397                                group by sf.unitcode,sf.unitname, se.[Name] ,month(fp.gatherdate)
 398                                )as a join system_framework as b on a.unitcode=b.unitcode ;";
 399        //6、空置房（本年）        
 400        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 401                                a.itemname,a.[month],a.[totalmoney]
 402                                from (
 403                                select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 404                                from finance_preceipt fp 
 405                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 406                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 407                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 408                                left join system_framework as sf on sf.unitid=fa.companyid 
 409                                where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 410                                and fa.itemtype=7 and fp.companyid in ({1}) 
 411                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate)
 412                                )as a join system_framework as b on a.unitcode=b.unitcode ;";
 413        //7、空置房（往年）        
 414        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 415                                a.itemname,a.[month],a.[totalmoney]
 416                                from (
 417                                select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 418                                from finance_preceipt fp 
 419                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 420                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 421                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 422                                left join system_framework as sf on sf.unitid=fa.companyid 
 423                                where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 424                                and fa.itemtype=7 and fp.companyid in ({1}) 
 425                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate)
 426                                )as a join system_framework as b on a.unitcode=b.unitcode ;";
 427        //8、空置房（预收）    
 428        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 429                            a.itemname,a.[month],a.[totalmoney]
 430                            from (
 431                            select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 432                            from finance_preceipt fp 
 433                            join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 434                            join finance_areceivable fa on far.areceivableid = fa.areceivableid
 435                            join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 436                            left join system_framework as sf on sf.unitid=fa.companyid 
 437                            where fa.GainMoney>0 and year(fp.gatherdate)= {0} and year(fa.enddate) > {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 438                            and fa.itemtype=7 and fp.companyid in ({1}) 
 439                            group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate)
 440                            )as a join system_framework as b on a.unitcode=b.unitcode ;";
 441        //UnitName = sqlHead;
 442        sql = string.Format(sql, iYear, CompanyList);
 443
 444
 445        DataSet ds = AmbitsWY_CLR.Globe.ExecSqlForDataSet(sql, out Err);
 446
 447
 448        if (Err != null)
 449        {
 450            throw new Exception("错误01:" + Err + "\r\n语句：\r\n" + sql);
 451
 452        }
 453
 454        //col = new DataColumn("myTemp", typeof(decimal));
 455        //col.Expression = "jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec";
 456        //dt.Columns.Add(col);
 457        #endregion
 458
 459        服务费收入#region 服务费收入
 460        //本年度
 461        DataRow row = dt.NewRow();
 462        row["UnitName"] = "服务费收入(本年度)";
 463        dt.Rows.Add(row);
 464        InsertData(ref dt, ds.Tables[0], 1);
 465        AddSum(ref dt, "①", "小计", 11, "flag = 1 ");
 466
 467        //往年度
 468        row = dt.NewRow();
 469        row["UnitName"] = "服务费收入(往年度)";
 470        dt.Rows.Add(row);
 471        InsertData(ref dt, ds.Tables[1], 2);
 472        AddSum(ref dt, "②", "小计", 12, "flag = 2 ");
 473
 474        //预收款
 475        row = dt.NewRow();
 476        row["UnitName"] = "服务费收入(预收款)";
 477        dt.Rows.Add(row);
 478        InsertData(ref dt, ds.Tables[2], 3);
 479        AddSum(ref dt, "③", "小计", 13, "flag = 3 ");
 480
 481        //合计
 482        AddSum(ref dt, "所有区域服务费(④=①+②+③)", "合计", 7, "Flag in (11,12,13)");
 483
 484
 485        //本年收入差异率
 486        CalculatePercent(ref dt, ds.Tables[3]);
 487
 488        #endregion
 489
 490        其他服务收入#region 其他服务收入
 491        row = dt.NewRow();
 492        row["UnitName"] = "其他服务收入";
 493        dt.Rows.Add(row);
 494        InsertData(ref dt, ds.Tables[4], 8);
 495        AddSum(ref dt, "⑤", "小计", 18, "flag = 8 ");
 496        AddSum(ref dt, "所有区域总收入(⑥=④+⑤)", "共计", 18, "flag in (7,8) ");
 497        #endregion
 498
 499        空置房#region 空置房
 500        //本年度
 501        row = dt.NewRow();
 502        row["UnitName"] = "空置房(本年度)";
 503        dt.Rows.Add(row);
 504        InsertData(ref dt, ds.Tables[5], 4);
 505        AddSum(ref dt, "⑦", "小计", 4, "flag = 4 ");
 506
 507        //往年度
 508        row = dt.NewRow();
 509        row["UnitName"] = "空置房(往年度)";
 510        dt.Rows.Add(row);
 511        InsertData(ref dt, ds.Tables[6], 5);
 512        AddSum(ref dt, "⑧", "小计", 15, "flag = 5 ");
 513
 514        //预收款
 515        row = dt.NewRow();
 516        row["UnitName"] = "空置房(预收款)";
 517        dt.Rows.Add(row);
 518        InsertData(ref dt, ds.Tables[7], 6);
 519        AddSum(ref dt, "⑨", "小计", 16, "flag = 6 ");
 520        dt.AcceptChanges();
 521        for (int i = 0; i < dt.Rows.Count; i++)
 522        {
 523            dt.Rows[i]["tid"] = i + 1;
 524            DataRow row2 = dt.Rows[i];
 525            if (AmbitsWY_CLR.Globe.ConvertToInt(row2["Flag"]) > 0)
 526                row2["Total"] = row2["myTemp"];
 527            else
 528                row2["Total"] = DBNull.Value;//空值
 529            rowList.Add(dt.Rows[i]);
 530        }
 531
 532        dt.Columns.Remove("myTemp");
 533
 534        #endregion
 535    }
 536    //总部查询
 537    private static void SearchCompany(int iYear, string CompanyID, List<DataRow> rowList, ref DataTable dt, string CompanyList, string UnitName)
 538    {
 539        数据查询#region 数据查询
 540        string Err = null;
 541
 542        //得到公司名称
 543        string sqlHead = string.Format("select UnitName from System_Framework where UnitCode='{0}'", CompanyID);
 544        DataSet uds = AmbitsWY_CLR.Globe.ExecSqlForDataSet(sqlHead, out Err);
 545        if (uds.Tables[0].Rows.Count > 0)
 546            UnitName = uds.Tables[0].Rows[0]["UnitName"].ToString();
 547
 548        //1、服务费收入(本年度)[包括应收部分和优惠]
 549        string sql = @"select '{2}' as UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 550                            from finance_preceipt fp 
 551                            join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 552                            join finance_areceivable fa on far.areceivableid = fa.areceivableid
 553                            join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 554                            where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 555                            and fp.companyid in ({1})
 556                            group by se.[Name],month(fp.gatherdate)
 557
 558                        union all
 559                            select '{2}' as UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 560                                                from Pb_Genledger fp join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 561                                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 562                                                join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 563                                                where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 564                                                    and fp.companyid in ({1})
 565                            group by   se.[Name],Month(fp.gatherdate);";
 566        //2、服务费收入(往年)[包括应收部分和优惠]
 567        sql += @"select '{2}' as UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 568                            from finance_preceipt fp 
 569                            join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 570                            join finance_areceivable fa on far.areceivableid = fa.areceivableid
 571                            join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 572                            where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 573                            and fp.companyid in ({1})
 574                            group by se.[Name],month(fp.gatherdate)
 575
 576                        union all
 577                            select '{2}' as UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 578                                                from Pb_Genledger fp join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 579                                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 580                                                join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 581                                                where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 582                                                    and fp.companyid in ({1})
 583                            group by   se.[Name],Month(fp.gatherdate);";
 584        //3、服务费收入(预收)
 585        sql += @"    select '{2}' as UnitName,se.[Name] as itemname,month(fi.gatherdate) as [Month],sum(fi.incomeMoney) as TotalMoney    
 586                    from finance_rmoney fr join finance_income fi on fr.rmoneyid = fi.rmoneyid
 587                    join finance_chargeitemset as fc on fc.itemid=fr.itemid
 588                    join Sys_Exestype  as se on se.ExecTypeID=fc.ExesTypeID 
 589                    where fi.incomemoney > 0 and year(fi.gatherdate) = {0} and fc.ExesTypeID=7
 590                        and fr.companyid in ({1})    
 591                    group by se.[Name],month(fi.gatherdate);    ";
 592
 593        //4、本年收入差异率
 594        sql += @"select  '{2}' as UnitName,    sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
 595                            sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
 596                            sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
 597                            sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as dec
 598                        from plan_income where planyear = {0} and companyID in ({1});";
 599        //5、其他服务收入（除服务费以外）                                                
 600        sql += @"select '{2}' as UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 601                    from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 602                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 603                    join finance_chargeitemset ft on ft.itemid=fa.itemid
 604                    join Sys_ExesGenre on Sys_ExesGenre.ExesGenreID=ft.ExesGenreID
 605                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemid
 606                    where year(fp.gatherdate)= {0} and  fa.itemtype <> 7 and (Sys_ExesGenre.ExesGenreID=1 or Sys_ExesGenre.ExesGenreID=5)
 607                        and fp.companyid in ({1})
 608                    group by se.[Name] ,month(fp.gatherdate) ;";
 609        //6、空置房（本年）        
 610        sql += @"select '{2}' as UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 611                    from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 612                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 613                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 614                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 615                    and fa.itemtype=7 and fp.companyid in ({1}) group by se.[Name],month(fp.gatherdate);";
 616        //7、空置房（往年）        
 617        sql += @"select  '{2}' as UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 618                    from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 619                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 620                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 621                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 622                        and fp.companyid in ({1}) and fa.itemtype=7
 623                    group by se.[Name],month(fp.gatherdate) ;";
 624        //8、空置房（预收）    
 625        sql += @"select  '{2}' as UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 626                    from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 627                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 628                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 629                    where fa.GainMoney>0 and year(fp.gatherdate)= {0} and year(fa.enddate) > {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 630                        and fp.companyid in ({1}) and fa.itemtype=7
 631                    group by se.[Name],month(fp.gatherdate);";
 632        //UnitName = sqlHead;
 633        sql = string.Format(sql, iYear, CompanyList, UnitName);
 634
 635
 636        DataSet ds = AmbitsWY_CLR.Globe.ExecSqlForDataSet(sql, out Err);
 637
 638
 639        if (Err != null)
 640        {
 641            throw new Exception("错误01:" + Err + "\r\n语句：\r\n" + sql);
 642
 643        }
 644
 645        //col = new DataColumn("myTemp", typeof(decimal));
 646        //col.Expression = "jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec";
 647        //dt.Columns.Add(col);
 648        #endregion
 649
 650        服务费收入#region 服务费收入
 651        //本年度
 652        DataRow row = dt.NewRow();
 653        row["UnitName"] = "服务费收入(本年度)";
 654        dt.Rows.Add(row);
 655        InsertData(ref dt, ds.Tables[0], 1);
 656        AddSum(ref dt, "①", "小计", 11, "flag = 1 ");
 657
 658        //往年度
 659        row = dt.NewRow();
 660        row["UnitName"] = "服务费收入(往年度)";
 661        dt.Rows.Add(row);
 662        InsertData(ref dt, ds.Tables[1], 2);
 663        AddSum(ref dt, "②", "小计", 12, "flag = 2 ");
 664
 665        //预收款
 666        row = dt.NewRow();
 667        row["UnitName"] = "服务费收入(预收款)";
 668        dt.Rows.Add(row);
 669        InsertData(ref dt, ds.Tables[2], 3);
 670        AddSum(ref dt, "③", "小计", 13, "flag = 3 ");
 671
 672        //合计
 673        AddSum(ref dt, "④=①+②+③", "合计", 7, "Flag in (11,12,13)");
 674
 675
 676        //本年收入差异率
 677        CalculatePercent(ref dt, ds.Tables[3]);
 678
 679        #endregion
 680
 681        其他服务收入#region 其他服务收入
 682        row = dt.NewRow();
 683        row["UnitName"] = "其他服务收入";
 684        dt.Rows.Add(row);
 685        InsertData(ref dt, ds.Tables[4], 8);
 686        AddSum(ref dt, "⑤", "小计", 18, "flag = 8 ");
 687        AddSum(ref dt, "总收入(⑥=④+⑤)", "共计", 18, "flag in (7,8) ");
 688        #endregion
 689
 690        空置房#region 空置房
 691        //本年度
 692        row = dt.NewRow();
 693        row["UnitName"] = "空置房(本年度)";
 694        dt.Rows.Add(row);
 695        InsertData(ref dt, ds.Tables[5], 4);
 696        AddSum(ref dt, "⑦", "小计", 4, "flag = 4 ");
 697
 698        //往年度
 699        row = dt.NewRow();
 700        row["UnitName"] = "空置房(往年度)";
 701        dt.Rows.Add(row);
 702        InsertData(ref dt, ds.Tables[6], 5);
 703        AddSum(ref dt, "⑧", "小计", 15, "flag = 5 ");
 704
 705        //预收款
 706        row = dt.NewRow();
 707        row["UnitName"] = "空置房(预收款)";
 708        dt.Rows.Add(row);
 709        InsertData(ref dt, ds.Tables[7], 6);
 710        AddSum(ref dt, "⑨", "小计", 16, "flag = 6 ");
 711        dt.AcceptChanges();
 712        for (int i = 0; i < dt.Rows.Count; i++)
 713        {
 714            dt.Rows[i]["tid"] = i + 1;
 715            DataRow row2 = dt.Rows[i];
 716            if (AmbitsWY_CLR.Globe.ConvertToInt(row2["Flag"]) > 0)
 717                row2["Total"] = row2["myTemp"];
 718            else
 719                row2["Total"] = DBNull.Value;//空值
 720            rowList.Add(dt.Rows[i]);
 721        }
 722
 723        dt.Columns.Remove("myTemp");
 724
 725        #endregion
 726    }
 727    //楼盘查询
 728    private static void SearchBuilding(int iYear, string CompanyID, List<DataRow> rowList, ref DataTable dt, string CompanyList, string UnitName)
 729    {
 730        string Err = null;
 731        数据查询#region 数据查询
 732        //1、服务费收入(本年度)[包括应收部分和优惠]
 733        string sql = @"select sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 734                                    from finance_preceipt fp 
 735                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 736                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 737                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 738                                    join system_framework as sf on sf.unitid=fp.companyid
 739                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 740                                    and fp.companyid in ({1})
 741                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
 742                        union all
 743                                    select  sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 744                                    from Pb_Genledger fp 
 745                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 746                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 747                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 748                                    join system_framework as sf on sf.unitid=fp.companyid
 749                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 750                                    and fp.companyid in ({1})
 751                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate);";
 752        //2、服务费收入(往年)[包括应收部分和优惠]
 753        sql += @"select sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 754                                    from finance_preceipt fp 
 755                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 756                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 757                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 758                                    join system_framework as sf on sf.unitid=fp.companyid
 759                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 760                                    and fp.companyid in ({1})
 761                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
 762                        union all
 763                                    select  sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 764                                    from Pb_Genledger fp 
 765                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 766                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 767                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 768                                    join system_framework as sf on sf.unitid=fp.companyid
 769                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 770                                    and fp.companyid in ({1})
 771                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate);";
 772        //3、服务费收入(预收)
 773        sql += @"    select sf.UnitName,se.[Name] as itemname,month(fi.gatherdate) as [Month],sum(fi.incomeMoney) as TotalMoney    
 774                                from finance_rmoney fr 
 775                                join finance_income fi on fr.rmoneyid = fi.rmoneyid
 776                                join finance_chargeitemset as fc on fc.itemid=fr.itemid
 777                                join Sys_Exestype  as se on se.ExecTypeID=fc.ExesTypeID 
 778                                join system_framework as sf on sf.unitid=fr.companyid
 779                                where fi.incomemoney > 0 and year(fi.gatherdate) = {0} and fc.ExesTypeID=7
 780                                and fr.companyid in ({1})    
 781                                group by sf.unitcode,sf.UnitName,se.[Name],month(fi.gatherdate);";
 782
 783        //4、计划收入
 784        /**//*sql += @"select  '所有区域' as UnitName,    sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
 785                        sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
 786                        sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
 787                        sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as dec
 788                    from plan_income where planyear = {0} and companyID in ({1});";*/
 789        sql += @"select '所有楼盘',sum(p.jan) as jan ,sum(p.feb) as feb,sum(p.mar) as mar,sum(p.apr) as apr,sum(p.may) as may,
 790                        sum(p.jun) as jun, sum(p.jul) as jul,sum(p.aug) as aug,sum(p.sep) as sep, sum(p.oct) as oct,sum(p.nov) as nov, sum(p.[dec]) as [dec]
 791                        from (
 792                        select sf.unitcode,sf.UnitName, sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
 793                        sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
 794                        sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
 795                        sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as [dec]
 796                        from plan_income 
 797                        join system_framework as sf on sf.unitid=plan_income.companyid
 798                        where planyear = {0} and companyID in ({1})
 799                        group by sf.unitcode,sf.UnitName
 800                        ) as p;";
 801        //5、其他服务收入（除服务费以外）                                                
 802        sql += @"select sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 803                                from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 804                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 805                                left join finance_chargeitemset ft on ft.itemid=fa.itemid
 806                                left join Sys_ExesGenre on Sys_ExesGenre.ExesGenreID=ft.ExesGenreID
 807                                join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 808                                left join system_framework as sf on sf.unitid=fa.companyid 
 809                                where year(fp.gatherdate)= {0} and  fa.itemtype <> 7 and Sys_ExesGenre.ExesGenreID=1
 810                                    and fp.companyid in ({1})
 811                                group by sf.unitcode,sf.unitname, se.[Name] ,month(fp.gatherdate) ;";
 812        //6、空置房（本年）        
 813        sql += @"select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 814                                from finance_preceipt fp 
 815                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 816                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 817                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 818                                left join system_framework as sf on sf.unitid=fa.companyid 
 819                                where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 820                                and fa.itemtype=7 and fp.companyid in ({1}) 
 821                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate) ;";
 822        //7、空置房（往年）        
 823        sql += @"select sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 824                                from finance_preceipt fp 
 825                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 826                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
 827                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 828                                left join system_framework as sf on sf.unitid=fa.companyid 
 829                                where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 830                                and fa.itemtype=7 and fp.companyid in ({1}) 
 831                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate) ;";
 832        //8、空置房（预收）    
 833        sql += @"select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
 834                            from finance_preceipt fp 
 835                            join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 836                            join finance_areceivable fa on far.areceivableid = fa.areceivableid
 837                            join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
 838                            left join system_framework as sf on sf.unitid=fa.companyid 
 839                            where fa.GainMoney>0 and year(fp.gatherdate)= {0} and year(fa.enddate) > {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
 840                            and fa.itemtype=7 and fp.companyid in ({1}) 
 841                            group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate) ;";
 842        //UnitName = sqlHead;
 843        sql = string.Format(sql, iYear, CompanyList);
 844
 845
 846        DataSet ds = AmbitsWY_CLR.Globe.ExecSqlForDataSet(sql, out Err);
 847
 848
 849        if (Err != null)
 850        {
 851            throw new Exception("错误01:" + Err + "\r\n语句：\r\n" + sql);
 852
 853        }
 854
 855        //col = new DataColumn("myTemp", typeof(decimal));
 856        //col.Expression = "jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec";
 857        //dt.Columns.Add(col);
 858        #endregion
 859
 860        服务费收入#region 服务费收入
 861        //本年度
 862        DataRow row = dt.NewRow();
 863        row["UnitName"] = "服务费收入(本年度)";
 864        dt.Rows.Add(row);
 865        InsertData(ref dt, ds.Tables[0], 1);
 866        AddSum(ref dt, "①", "小计", 11, "flag = 1 ");
 867
 868        //往年度
 869        row = dt.NewRow();
 870        row["UnitName"] = "服务费收入(往年度)";
 871        dt.Rows.Add(row);
 872        InsertData(ref dt, ds.Tables[1], 2);
 873        AddSum(ref dt, "②", "小计", 12, "flag = 2 ");
 874
 875        //预收款
 876        row = dt.NewRow();
 877        row["UnitName"] = "服务费收入(预收款)";
 878        dt.Rows.Add(row);
 879        InsertData(ref dt, ds.Tables[2], 3);
 880        AddSum(ref dt, "③", "小计", 13, "flag = 3 ");
 881
 882        //合计
 883        AddSum(ref dt, "所有楼盘服务费(④=①+②+③)", "合计", 7, "Flag in (11,12,13)");
 884
 885
 886        //本年收入差异率
 887        CalculatePercent(ref dt, ds.Tables[3]);
 888
 889        #endregion
 890
 891        其他服务收入#region 其他服务收入
 892        row = dt.NewRow();
 893        row["UnitName"] = "其他服务收入";
 894        dt.Rows.Add(row);
 895        InsertData(ref dt, ds.Tables[4], 8);
 896        AddSum(ref dt, "⑤", "小计", 18, "flag = 8 ");
 897        AddSum(ref dt, "所有楼盘总收入(⑥=④+⑤)", "共计", 18, "flag in (7,8) ");
 898        #endregion
 899
 900        空置房#region 空置房
 901        //本年度
 902        row = dt.NewRow();
 903        row["UnitName"] = "空置房(本年度)";
 904        dt.Rows.Add(row);
 905        InsertData(ref dt, ds.Tables[5], 4);
 906        AddSum(ref dt, "⑦", "小计", 4, "flag = 4 ");
 907
 908        //往年度
 909        row = dt.NewRow();
 910        row["UnitName"] = "空置房(往年度)";
 911        dt.Rows.Add(row);
 912        InsertData(ref dt, ds.Tables[6], 5);
 913        AddSum(ref dt, "⑧", "小计", 15, "flag = 5 ");
 914
 915        //预收款
 916        row = dt.NewRow();
 917        row["UnitName"] = "空置房(预收款)";
 918        dt.Rows.Add(row);
 919        InsertData(ref dt, ds.Tables[7], 6);
 920        AddSum(ref dt, "⑨", "小计", 16, "flag = 6 ");
 921        dt.AcceptChanges();
 922        for (int i = 0; i < dt.Rows.Count; i++)
 923        {
 924            dt.Rows[i]["tid"] = i + 1;
 925            DataRow row2 = dt.Rows[i];
 926            if (AmbitsWY_CLR.Globe.ConvertToInt(row2["Flag"]) > 0)
 927                row2["Total"] = row2["myTemp"];
 928            else
 929                row2["Total"] = DBNull.Value;//空值
 930            rowList.Add(dt.Rows[i]);
 931        }
 932
 933        dt.Columns.Remove("myTemp");
 934
 935        #endregion
 936    }
 937    //区域查询
 938    private static void SearchRegion(int iYear, string CompanyID, List<DataRow> rowList, ref DataTable dt, string CompanyList, string UnitName)
 939    {
 940        数据查询#region 数据查询
 941        string Err = null;
 942        //1、服务费收入(本年度)[包括应收部分和优惠]
 943        string sql = @"select  (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 944                                    a.itemname,a.[month],a.[totalmoney]
 945                                    from (select sf.unitcode,sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 946                                    from finance_preceipt fp 
 947                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 948                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 949                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 950                                    join system_framework as sf on sf.unitid=fp.companyid
 951                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 952                                    and fp.companyid in ({1})
 953                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
 954                                    ) as a join system_framework as b on a.unitcode=b.unitcode
 955
 956                        union all
 957                                    select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 958                                    a.itemname,a.[month],a.[totalmoney]
 959                                    from (select  sf.unitcode,sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 960                                    from Pb_Genledger fp 
 961                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 962                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 963                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 964                                    join system_framework as sf on sf.unitid=fp.companyid
 965                                    where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and  fa.itemtype = 7 
 966                                    and fp.companyid in ({1})
 967                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate)
 968                                    ) as a join system_framework as b on a.unitcode=b.unitcode;";
 969        //2、服务费收入(往年)[包括应收部分和优惠]
 970        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 971                                    a.itemname,a.[month],a.[totalmoney]
 972                                    from (select sf.unitcode,sf.UnitName,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney
 973                                    from finance_preceipt fp 
 974                                    join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
 975                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 976                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 977                                    join system_framework as sf on sf.unitid=fp.companyid
 978                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 979                                    and fp.companyid in ({1})
 980                                    group by sf.unitcode,sf.UnitName,se.[Name],month(fp.gatherdate)
 981                                    ) as a join system_framework as b on a.unitcode=b.unitcode
 982
 983                        union all
 984                                    select  (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 985                                    a.itemname,a.[month],a.[totalmoney]
 986                                    from (select  sf.unitcode,sf.UnitName,se.[Name] as itemname,Month(fp.gatherdate) as [Month],sum(far.GainPbMoney) as TotalMoney                        
 987                                    from Pb_Genledger fp 
 988                                    join Pb_Datail far on fp.GenledgerID = far.GenledgerID
 989                                    join finance_areceivable fa on far.areceivableid = fa.areceivableid
 990                                    join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
 991                                    join system_framework as sf on sf.unitid=fp.companyid
 992                                    where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and  fa.itemtype = 7 
 993                                    and fp.companyid in ({1})
 994                                    group by sf.unitcode,sf.UnitName,se.[Name],Month(fp.gatherdate)
 995                                    ) as a join system_framework as b on a.unitcode=b.unitcode;";
 996        //3、服务费收入(预收)
 997        sql += @"    select  (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
 998                                a.itemname,a.[month],a.[totalmoney]
 999                                from (
1000                                select sf.unitcode,sf.UnitName,se.[Name] as itemname,month(fi.gatherdate) as [Month],sum(fi.incomeMoney) as TotalMoney    
1001                                from finance_rmoney fr 
1002                                join finance_income fi on fr.rmoneyid = fi.rmoneyid
1003                                join finance_chargeitemset as fc on fc.itemid=fr.itemid
1004                                join Sys_Exestype  as se on se.ExecTypeID=fc.ExesTypeID 
1005                                join system_framework as sf on sf.unitid=fr.companyid
1006                                where fi.incomemoney > 0 and year(fi.gatherdate) = {0} and fc.ExesTypeID=7
1007                                and fr.companyid in ({1})    
1008                                group by sf.unitcode,sf.UnitName,se.[Name],month(fi.gatherdate)
1009                                ) as a join system_framework as b on a.unitcode=b.unitcode;";
1010
1011        //4、计划收入
        /**//*sql += @"select  '所有区域' as UnitName,    sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
1013                        sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
1014                        sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
1015                        sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as dec
1016                    from plan_income where planyear = {0} and companyID in ({1});";*/
        sql += @"select '所有区域',sum(p.jan) as jan ,sum(p.feb) as feb,sum(p.mar) as mar,sum(p.apr) as apr,sum(p.may) as may,
1018                                sum(p.jun) as jun, sum(p.jul) as jul,sum(p.aug) as aug,sum(p.sep) as sep, sum(p.oct) as oct,sum(p.nov) as nov, sum(p.[dec]) as [dec]
1019                                from (
1020                                select b.punitcode as unitcode,(select unitname from system_framework where unitcode=b.punitcode) as unitname ,
1021                                a.jan,a.feb,a.mar,a.apr,a.may,a.jun,a.jul,a.aug,a.sep,a.oct,a.nov,a.[dec]
1022                                from (
1023                                select sf.unitcode,sf.UnitName, sum(isnull(janmoney,0))as jan,sum(isnull(febmoney,0))as feb,sum(isnull(marmoney,0))as mar,
1024                                sum(isnull(aprmoney,0))as apr,sum(isnull(maymoney,0))as may,sum(isnull(junmoney,0))as jun,
1025                                sum(isnull(julmoney,0))as jul,sum(isnull(augmoney,0))as aug,sum(isnull(septmoney,0))as sep,
1026                                sum(isnull(octMoney,0))as oct,sum(isnull(NovMoney,0))as nov,sum(isnull(DecMoney,0))as [dec]
1027                                from plan_income 
1028                                join system_framework as sf on sf.unitid=plan_income.companyid
1029                                where planyear = {0} and companyID in ({1})
1030                                group by sf.unitcode,sf.UnitName
1031                                ) as a join system_framework as b on a.unitcode=b.unitcode
1032                                ) as p;";
1033        //5、其他服务收入（除服务费以外）                                                
        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
1035                                a.itemname,a.[month],a.[totalmoney]
1036                                from (
1037                                select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
1038                                from finance_preceipt fp join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
1039                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
1040                                left join finance_chargeitemset ft on ft.itemid=fa.itemid
1041                                left join Sys_ExesGenre on Sys_ExesGenre.ExesGenreID=ft.ExesGenreID
1042                                join Sys_Exestype  as se on se.ExecTypeID=fa.itemtype
1043                                left join system_framework as sf on sf.unitid=fa.companyid 
1044                                where year(fp.gatherdate)= {0} and  fa.itemtype <> 7 and Sys_ExesGenre.ExesGenreID=1
1045                                    and fp.companyid in ({1})
1046                                group by sf.unitcode,sf.unitname, se.[Name] ,month(fp.gatherdate)
1047                                )as a join system_framework as b on a.unitcode=b.unitcode ;";
1048        //6、空置房（本年）        
        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
1050                                a.itemname,a.[month],a.[totalmoney]
1051                                from (
1052                                select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
1053                                from finance_preceipt fp 
1054                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
1055                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
1056                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
1057                                left join system_framework as sf on sf.unitid=fa.companyid 
1058                                where year(fp.gatherdate)= {0} and year(fa.enddate) = {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
1059                                and fa.itemtype=7 and fp.companyid in ({1}) 
1060                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate)
1061                                )as a join system_framework as b on a.unitcode=b.unitcode ;";
1062        //7、空置房（往年）        
        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
1064                                a.itemname,a.[month],a.[totalmoney]
1065                                from (
1066                                select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
1067                                from finance_preceipt fp 
1068                                join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
1069                                join finance_areceivable fa on far.areceivableid = fa.areceivableid
1070                                join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
1071                                left join system_framework as sf on sf.unitid=fa.companyid 
1072                                where year(fp.gatherdate)= {0} and year(fa.enddate) < {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
1073                                and fa.itemtype=7 and fp.companyid in ({1}) 
1074                                group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate)
1075                                )as a join system_framework as b on a.unitcode=b.unitcode ;";
1076        //8、空置房（预收）    
        sql += @"select (select unitname from system_framework where unitcode=b.punitcode) as unitname ,
1078                            a.itemname,a.[month],a.[totalmoney]
1079                            from (
1080                            select sf.unitcode,sf.unitname,se.[Name] as itemname,month(fp.gatherdate) as [Month],sum(far.gainmoney) as TotalMoney                        
1081                            from finance_preceipt fp 
1082                            join finance_arecei_receiptlist far on fp.receiptid = far.receiptid
1083                            join finance_areceivable fa on far.areceivableid = fa.areceivableid
1084                            join Sys_Exestype as se on se.ExecTypeID=fa.itemtype
1085                            left join system_framework as sf on sf.unitid=fa.companyid 
1086                            where fa.GainMoney>0 and year(fp.gatherdate)= {0} and year(fa.enddate) > {0} and fa.UnitOrindividualID is not null and isnull(fa.FavourableSign,0) !=1
1087                            and fa.itemtype=7 and fp.companyid in ({1}) 
1088                            group by sf.unitcode,sf.unitname,se.[Name],month(fp.gatherdate)
1089                            )as a join system_framework as b on a.unitcode=b.unitcode ;";
1090        //UnitName = sqlHead;
        sql = string.Format(sql, iYear, CompanyList);
1092
1093
1094        DataSet ds = AmbitsWY_CLR.Globe.ExecSqlForDataSet(sql, out Err);
1095
1096
1097        if (Err != null)
1098        {
1099            throw new Exception("错误01:" + Err + "\r\n语句：\r\n" + sql);
1100
1101        }
1102
1103        //col = new DataColumn("myTemp", typeof(decimal));
1104        //col.Expression = "jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec";
1105        //dt.Columns.Add(col);
        #endregion
1107
1108        服务费收入#region 服务费收入
        //本年度
        DataRow row = dt.NewRow();
1111        row["UnitName"] = "服务费收入(本年度)";
1112        dt.Rows.Add(row);
1113        InsertData(ref dt, ds.Tables[0], 1);
1114        AddSum(ref dt, "①", "小计", 11, "flag = 1 ");
1115
1116        //往年度
        row = dt.NewRow();
1118        row["UnitName"] = "服务费收入(往年度)";
1119        dt.Rows.Add(row);
1120        InsertData(ref dt, ds.Tables[1], 2);
1121        AddSum(ref dt, "②", "小计", 12, "flag = 2 ");
1122
1123        //预收款
        row = dt.NewRow();
1125        row["UnitName"] = "服务费收入(预收款)";
1126        dt.Rows.Add(row);
1127        InsertData(ref dt, ds.Tables[2], 3);
1128        AddSum(ref dt, "③", "小计", 13, "flag = 3 ");
1129
1130        //合计
        AddSum(ref dt, "所有区域服务费(④=①+②+③)", "合计", 7, "Flag in (11,12,13)");
1132
1133
1134        //本年收入差异率
        CalculatePercent(ref dt, ds.Tables[3]);
1136
1137        #endregion
1138
1139        其他服务收入#region 其他服务收入
        row = dt.NewRow();
1141        row["UnitName"] = "其他服务收入";
1142        dt.Rows.Add(row);
1143        InsertData(ref dt, ds.Tables[4], 8);
1144        AddSum(ref dt, "⑤", "小计", 18, "flag = 8 ");
1145        AddSum(ref dt, "所有区域总收入(⑥=④+⑤)", "共计", 18, "flag in (7,8) ");
1146        #endregion
1147
1148        空置房#region 空置房
        //本年度
        row = dt.NewRow();
1151        row["UnitName"] = "空置房(本年度)";
1152        dt.Rows.Add(row);
1153        InsertData(ref dt, ds.Tables[5], 4);
1154        AddSum(ref dt, "⑦", "小计", 4, "flag = 4 ");
1155
1156        //往年度
        row = dt.NewRow();
1158        row["UnitName"] = "空置房(往年度)";
1159        dt.Rows.Add(row);
1160        InsertData(ref dt, ds.Tables[6], 5);
1161        AddSum(ref dt, "⑧", "小计", 15, "flag = 5 ");
1162
1163        //预收款
        row = dt.NewRow();
1165        row["UnitName"] = "空置房(预收款)";
1166        dt.Rows.Add(row);
1167        InsertData(ref dt, ds.Tables[7], 6);
1168        AddSum(ref dt, "⑨", "小计", 16, "flag = 6 ");
1169        dt.AcceptChanges();
1170        for (int i = 0; i < dt.Rows.Count; i++)
1171        {
1172            dt.Rows[i]["tid"] = i + 1;
1173            DataRow row2 = dt.Rows[i];
1174            if (AmbitsWY_CLR.Globe.ConvertToInt(row2["Flag"]) > 0)
1175                row2["Total"] = row2["myTemp"];
1176            else
                row2["Total"] = DBNull.Value;//空值
            rowList.Add(dt.Rows[i]);
1179        }
1180
1181        dt.Columns.Remove("myTemp");
1182
1183        #endregion
    }
    private static void AddColumns(ref DataTable dt, string[] colArray)
1186    {
1187        for (int i = 0; i < colArray.Length; i++)
1188        {
1189            DataColumn col = new DataColumn(colArray[i], typeof(decimal));
1190            col.DefaultValue = 0;
1191            dt.Columns.Add(col);
1192        }
    }
    /**//// <summary>
    /// 计算百分比
1196    /// </summary>
    /// <param name="dt"></param>
    /// <param name="dtData"></param>
    private static void CalculatePercent(ref DataTable dt, DataTable dtData)
1200    {
1201        string[] sa = new string[] { "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec" };
1202        decimal dTotal = 0, dTotal2 = 0;
1203        dt.DefaultView.RowFilter = "Flag=7";
1204        DataRow row = dt.DefaultView[0].Row;
1205        dt.DefaultView.RowFilter = "";
1206
1207        DataRow NewRow = dt.NewRow();
1208        NewRow["UnitName"] = "--";
1209        NewRow["ItemName"] = "本年收入差异率";
1210        NewRow["flag"] = 20;
1211        dt.Rows.Add(NewRow);
1212
1213        foreach (string s in sa)
1214        {
1215            decimal d = AmbitsWY_CLR.Globe.ConvertToDecimal(dtData.Rows[0][s]);
1216            if (d != 0)//注意：如果除数为0会出错，所以这里有判断。
                NewRow[s] = AmbitsWY_CLR.Globe.ConvertToDecimal(row[s]) / d * 100;
1218            else
                NewRow[s] = -1;
1220            dTotal += d;
1221            dTotal2 += AmbitsWY_CLR.Globe.ConvertToDecimal(row[s]);
1222
1223        }
        if (dTotal != 0)//注意：如果除数为0会出错，所以这里有判断。
            NewRow["Total"] = dTotal2 / dTotal * 100;
1226        else
            NewRow["Total"] = -1;
1228
1229    }
    /**//// <summary>
    /// 将数据插入目标表。
1232    /// </summary>
    /// <param name="dt"></param>
    /// <param name="dtData"></param>
    /// <param name="Flag"></param>
    private static void InsertData(ref DataTable dt, DataTable dtData, int Flag)
1237    {
1238        string[] sa = new string[] { "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec" };
1239        while (dtData.Rows.Count > 0)
1240        {
1241            /**/////每次都以第一条记录作为月汇总的ItemName，因为使用完后会删除掉。
1242            //string sName = Convert.ToString(dtData.Rows[0]["ItemName"]);
1243            //dtData.DefaultView.RowFilter = "itemName='" + sName + "'";
1244            //DataRow dRow = dt.NewRow();
1245            //dRow["ItemName"] = sName;
1246            //dRow["Flag"] = Flag;
1247            //dt.Rows.Add(dRow);
1248            //foreach (DataRowView row2 in dtData.DefaultView)
1249            //{
1250
1251            //    int iMonth = Convert.ToInt32(row2["Month"]);
1252            //    dRow[sa[iMonth - 1]] = row2["TotalMoney"];
1253            //    dtData.Rows.Remove(row2.Row);//删除，已经用完。
1254            //    //dtData.DefaultView.Delete(0);
1255            //}
            /**/////dtData.DefaultView.RowFilter = "";
1257
1258
1259            //每次都以第一条记录作为月汇总的ItemName，因为使用完后会删除掉。
            string sName = Convert.ToString(dtData.Rows[0]["ItemName"]);
1261            string unitName = Convert.ToString(dtData.Rows[0]["UnitName"]);
1262            dtData.DefaultView.RowFilter = "UnitName='" + unitName + "' and itemName='" + sName + "'";
1263            DataRow dRow = dt.NewRow();
1264            dRow["UnitName"] = unitName;
1265            dRow["ItemName"] = sName;
1266            dRow["Flag"] = Flag;
1267            dt.Rows.Add(dRow);
1268            foreach (DataRowView row2 in dtData.DefaultView)
1269            {
1270
1271                int iMonth = Convert.ToInt32(row2["Month"]);
1272                dRow[sa[iMonth - 1]] = row2["TotalMoney"];
1273                dtData.Rows.Remove(row2.Row);//删除，已经用完。
1274                //dtData.DefaultView.Delete(0);
            }
            //dtData.DefaultView.RowFilter = "";
        }
    }
    /**//// <summary>
    /// 添加合计信息
1281    /// </summary>
    /// <param name="dt"></param>
    /// <param name="ItemName"></param>
    /// <param name="Flag"></param>
    /// <param name="Filter"></param>
    private static void AddSum(ref DataTable dt, string UnitName, string ItemName, int Flag, string Filter)
1287    {
1288        DataRow row = dt.NewRow();
1289        row["UnitName"] = UnitName;
1290        row["ItemName"] = ItemName;
1291        row["Flag"] = Flag;
1292        string[] sa = new string[] { "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec" };
1293        foreach (string s in sa)
1294        {
1295            row[s] = AmbitsWY_CLR.Globe.ConvertToDecimal(dt.Compute(string.Format("sum({0})", s), Filter));
1296        }
1297        dt.Rows.Add(row);
1298    }
    /**//// <summary>
    /// 获取公司的所有下级公司ID列表，避免重复查询。
1301    /// </summary>
    /// <param name="CompanyID"></param>
    /// <returns></returns>
    private static string GetCompanyList(string CompanyID)
1305    {
1306
1307        string Err = null;
1308        string sCode = AmbitsWY_CLR.Globe.GetSqlStringValue("select UnitCode from System_Framework where UnitCode=" + CompanyID, "--");
1309        if (sCode == "--")
1310            throw new Exception("获取公司编码出错");
1311        DataSet ds = AmbitsWY_CLR.Globe.ExecSqlForDataSet("select UnitID from System_Framework where UnitCode like '" + sCode + "%'", out Err);
1312        if (Err != null)
1313        {
1314            throw new Exception("获取公司信息出错：" + Err);
1315
1316        }
        string IDList = "";
1318        foreach (DataRow row in ds.Tables[0].Rows)
1319        {
1320            IDList += (string.IsNullOrEmpty(IDList) ? "" : ",") + Convert.ToString(row["UnitID"]);
1321        }
        return IDList;
1323    }
    private static string GetUnitList(string CompanyID)
1325    {
1326
1327        string Err = null;
1328        string sqlstr = string.Format("select unitcode From System_FrameWork Where UnitCode in(select Context from CW_F_GetStringsTable_CLR('{0}',','))", CompanyID);
1329        DataSet ds = AmbitsWY_CLR.Globe.ExecSqlForDataSet(sqlstr, out Err);
1330        if (Err != null)
1331        {
1332            throw new Exception("获取公司信息出错：" + Err);
1333
1334        }
1335        string IDList = "";
1336        foreach (DataRow row in ds.Tables[0].Rows)
1337        {
1338            string sCode = row["unitcode"].ToString();
1339            DataSet ds1 = AmbitsWY_CLR.Globe.ExecSqlForDataSet("select UnitID from System_Framework where UnitCode like '" + sCode + "%'", out Err);
1340            foreach (DataRow thisRow in ds1.Tables[0].Rows)
1341            {
1342                IDList += (string.IsNullOrEmpty(IDList) ? "" : ",") + Convert.ToString(thisRow["UnitID"]);
1343            }
1344
1345        }
1346        return IDList;
1347    }
1348    //填充返回表的行的方法，这个方法有一定的规定：
1349    //一定是空返回的void类型，并且入参的第一个必须为object，其后面的参数都必须为out类型
1350    //参数的类型，个数和顺序由返回表的列结构决定！（在TableDefinition = " String nvarchar(200)"中定义的表结构）
1351    public static void FillRow3(object row, out int ID, out string UnitName, out string ItemName, out decimal jan, out decimal feb, out decimal mar, out decimal apr, out decimal may, out decimal jun, out decimal jul, out decimal aug, out decimal sep, out decimal oct, out decimal nov, out decimal dec, out decimal Total, out int Flag)
1352    {
1353        //这个object 其实就是GetStrings(string x,char y)函数返回的迭代，这样你直接赋值给那个列就可以了。
1354        DataRow objRow = row as DataRow;
1355        if (objRow == null)
1356        {
1357            ID = 0;
1358            UnitName = "";
1359            ItemName = "";
1360            jan = 0;
1361            feb = 0;
1362            mar = 0;
1363            apr = 0;
1364            may = 0;
1365            jun = 0;
1366            jul = 0;
1367            aug = 0;
1368            sep = 0;
1369            oct = 0;
1370            nov = 0;
1371            dec = 0;
1372            Total = 0;
1373            Flag = 0;
1374        }
1375        else
1376        {
1377            ID = AmbitsWY_CLR.Globe.ConvertToInt(objRow["tID"], -1);
1378            UnitName = Convert.ToString(objRow["UnitName"]);
1379            ItemName = Convert.ToString(objRow["ItemName"]);
1380            jan = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["jan"]);
1381            feb = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["feb"]);
1382            mar = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["mar"]);
1383            apr = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["apr"]);
1384            may = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["may"]);
1385            jun = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["jun"]);
1386            jul = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["jul"]);
1387            aug = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["aug"]);
1388            sep = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["sep"]);
1389            oct = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["oct"]);
1390            nov = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["nov"]);
1391            dec = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["dec"]);
1392
1393            Total = AmbitsWY_CLR.Globe.ConvertToDecimal(objRow["total"]);
1394            Flag = AmbitsWY_CLR.Globe.ConvertToInt(objRow["Flag"]);
1395        }
1396    }
1397    #endregion
1398
1399}
1400
1401
1402
1403