有这样一张报表,如图:
要求用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、计划收入 1012 /**//*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});";*/ 1017 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、其他服务收入(除服务费以外) 1034 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、空置房(本年) 1049 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、空置房(往年) 1063 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、空置房(预收) 1077 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; 1091 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); 1106 #endregion 1107 1108 服务费收入#region 服务费收入 1109 //本年度 1110 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 //往年度 1117 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 //预收款 1124 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 //合计 1131 AddSum(ref dt, "所有区域服务费(④=①+②+③)", "合计", 7, "Flag in (11,12,13)"); 1132 1133 1134 //本年收入差异率 1135 CalculatePercent(ref dt, ds.Tables[3]); 1136 1137 #endregion 1138 1139 其他服务收入#region 其他服务收入 1140 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 空置房 1149 //本年度 1150 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 //往年度 1157 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 //预收款 1164 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 1177 row2["Total"] = DBNull.Value;//空值 1178 rowList.Add(dt.Rows[i]); 1179 } 1180 1181 dt.Columns.Remove("myTemp"); 1182 1183 #endregion 1184 } 1185 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 } 1193 } 1194 /**//// <summary> 1195 /// 计算百分比 1196 /// </summary> 1197 /// <param name="dt"></param> 1198 /// <param name="dtData"></param> 1199 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会出错,所以这里有判断。 1217 NewRow[s] = AmbitsWY_CLR.Globe.ConvertToDecimal(row[s]) / d * 100; 1218 else 1219 NewRow[s] = -1; 1220 dTotal += d; 1221 dTotal2 += AmbitsWY_CLR.Globe.ConvertToDecimal(row[s]); 1222 1223 } 1224 if (dTotal != 0)//注意:如果除数为0会出错,所以这里有判断。 1225 NewRow["Total"] = dTotal2 / dTotal * 100; 1226 else 1227 NewRow["Total"] = -1; 1228 1229 } 1230 /**//// <summary> 1231 /// 将数据插入目标表。 1232 /// </summary> 1233 /// <param name="dt"></param> 1234 /// <param name="dtData"></param> 1235 /// <param name="Flag"></param> 1236 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 //} 1256 /**/////dtData.DefaultView.RowFilter = ""; 1257 1258 1259 //每次都以第一条记录作为月汇总的ItemName,因为使用完后会删除掉。 1260 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); 1275 } 1276 //dtData.DefaultView.RowFilter = ""; 1277 } 1278 } 1279 /**//// <summary> 1280 /// 添加合计信息 1281 /// </summary> 1282 /// <param name="dt"></param> 1283 /// <param name="ItemName"></param> 1284 /// <param name="Flag"></param> 1285 /// <param name="Filter"></param> 1286 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 } 1299 /**//// <summary> 1300 /// 获取公司的所有下级公司ID列表,避免重复查询。 1301 /// </summary> 1302 /// <param name="CompanyID"></param> 1303 /// <returns></returns> 1304 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 } 1317 string IDList = ""; 1318 foreach (DataRow row in ds.Tables[0].Rows) 1319 { 1320 IDList += (string.IsNullOrEmpty(IDList) ? "" : ",") + Convert.ToString(row["UnitID"]); 1321 } 1322 return IDList; 1323 } 1324 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 |