Connection data source=db_server;UID=db_user;PWD=db_pass;initial catalog=db; Connect Timeout=100000000 Connection usmAdmin RayonCode,System.String PaymentCode,System.String countAmount,System.Int32 Amount,System.Decimal countNotReg,System.Int32 sumNotReg,System.Decimal 300000 usmAdmin Connection RayonCodeSenderP,RayonCode,22,3 endDateP,endDate,31,0 startDateP,startDate,31,0 CultureID,languageIDVar,8,0 DECLARE @s TABLE(PaymentCode VARCHAR(8),ChapterName VARCHAR(255),countAmount INT, Amount DECIMAL(20,5),countNotReg INT, sumNotReg DECIMAL(20,5),RayonCode VARCHAR(3)) INSERT INTO @s EXEC usmAdministrationReportPayments @startDateP,@endDateP,@RayonCodeSenderP,@CultureID; SELECT t.RayonCode, t.PaymentCode,SUM(countAmount) countAmount, SUM(Amount) Amount, SUM(countNotReg) countNotReg, SUM(sumNotReg) sumNotReg FROM @s t GROUP BY t.RayonCode, t.PaymentCode RayonStat CodeValue,System.String DisplayText,System.String countAmount,System.Int32 Amount,System.Decimal countNotReg,System.Int32 sumNotReg,System.Decimal 30 RayonStat Connection startDateP,startDate,31,0 endDateP,endDate,31,0 RayonCodeSenderP,RayonCode,22,3 CultureID,languageIDVar,8,0 --DECLARE @startDateP DATE = '2012-03-01'; --DECLARE @endDateP DATE = '2012-03-31'; --DECLARE @RayonCodeSenderP VARCHAR(3) = '00_'; --DECLARE @CultureID INT = 1049; DECLARE @s TABLE(PaymentCode VARCHAR(8),ChapterName VARCHAR(255),countAmount INT, Amount DECIMAL(20,5),countNotReg INT, sumNotReg DECIMAL(20,5),RayonCode VARCHAR(3)) INSERT INTO @s EXEC usmAdministrationReportPayments @startDateP,@endDateP,@RayonCodeSenderP,@CultureID; WITH R AS ( SELECT '00_' as CodeValue ,'Центральный аппарат' as DisplayText,0 as RowNumber UNION ALL SELECT gch.CodeValue, gch.DisplayText, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) RowNumber FROM dbo.GetCodeHierarchy(@RayonCodeSenderP, NULL, 15, 1049, ' ') gch WHERE gch.[TYPE] IN (88,89,92) ), Rayons AS ( SELECT *, '#' + STUFF( ( SELECT '#' + CONVERT(VARCHAR(50), CodeValue) FROM dbo.GetCodeHierarchy(r.CodeValue, NULL, 15, 1049, ' ') t2 FOR XML PATH('') ), 1, 1, '' ) + '#' AS CodeWithChilds FROM R r ), T AS( SELECT * FROM @s s ) SELECT t.CodeValue,t.DisplayText, SUM(countAmount) countAmount, SUM(Amount) Amount, SUM(countNotReg) countNotReg, SUM(sumNotReg) sumNotReg FROM ( SELECT r.CodeValue,r.DisplayText, t.PaymentCode, ChapterName,SUM(countAmount) countAmount, SUM(Amount) Amount, SUM(countNotReg) countNotReg, SUM(sumNotReg) sumNotReg,r.RowNumber FROM Rayons r LEFT JOIN T t ON PATINDEX('%#'+ t.RayonCode +'#%', r.CodeWithChilds) > 0 GROUP BY r.CodeValue,r.DisplayText, t.PaymentCode, T.ChapterName, r.RowNumber --ORDER BY r.RowNumber ) t GROUP BY t.CodeValue,t.DisplayText, --t.[Description], t.RowNumber ORDER BY t.RowNumber admin3 PaymentCode,System.String ChapterName,System.String countAmount,System.Int32 Amount,System.Decimal countNotReg,System.Int32 sumNotReg,System.Decimal RayonCodeSender,System.String startDate,,System.DateTime endDate,,System.DateTime RayonCode,,System.String languageIDVar,,System.Int32 admin3 База un RayonCode un Связь CodeValue ,RayonCode,RayonCode,System.String,_x0030_0_,False,False ,languageIDVar,languageIDVar,System.Int32,_x0031_049,False,False ,startDate,startDate,System.DateTime,_x0033__x002F_1_x002F_2012_x0020_12:00:00_x0020_PM,False,False ,endDate,endDate,System.DateTime,_x0033__x002F_31_x002F_2012_x0020_12:00:00_x0020_PM,False,False EngineV2 None;Black;2;Solid;False;4;Black Transparent Transparent 0,0.4,28.7,1.9 All;Black;1;Solid;False;4;Black Transparent 0,0,12.8,1.4 Arial,9 a7cfa9b1adbe449995f1f69978db6209 Center 0,0,0,0 Text5 Наименование УГНС, Код платежа Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, Expression Center All;Black;1;Solid;False;4;Black Transparent 12.8,0,5.2,0.8 Arial,9 dcdafc15f0814157aa24f1c85ab407cc Center 0,0,0,0 Text6 Всего Black Expression Center All;Black;1;Solid;False;4;Black Transparent 12.8,1.4,2.6,0.5 Arial,8 ee2217ee49624f7aa2bbdd6fcb7d8b4f Center 0,0,0,0 Text23 2 Black Expression Center All;Black;1;Solid;False;4;Black Transparent 18,0,5.2,0.8 Arial,9 71d734cdce9e4cacbe1324cc63581d41 Center 0,0,0,0 Text7 Из них дикарей Black Expression Center All;Black;1;Solid;False;4;Black Transparent 18,0.8,2.6,0.6 Arial,9 2cb753611a524fae8cfb3f21255041d3 Center 0,0,0,0 Text8 Кол-во Black Expression Center All;Black;1;Solid;False;4;Black Transparent 20.6,0.8,2.6,0.6 Arial,9 4a76679e7ea540ec8c85ecdfbf44483a Center 0,0,0,0 Text9 Сумма Black Expression Center All;Black;1;Solid;False;4;Black Transparent 15.4,1.4,2.6,0.5 Arial,8 043fd424bd74430ebcff3f06ada3d313 Center 0,0,0,0 Text25 3 Black Expression Center All;Black;1;Solid;False;4;Black Transparent 18,1.4,2.6,0.5 Arial,8 3f94c6525a2a4e03a04dd94353f6403c Center 0,0,0,0 Text26 4 Black Expression Center All;Black;1;Solid;False;4;Black Transparent 20.6,1.4,2.6,0.5 Arial,8 7002ee73040341598d4a3381e081adc8 Center 0,0,0,0 Text15 5 Black Expression Center All;Black;1;Solid;False;4;Black Transparent 12.8,0.8,2.6,0.6 Arial,9 c9b6bae536514c8a9a2dc978a50f7489 Center 0,0,0,0 Text16 Кол-во Black Expression Center All;Black;1;Solid;False;4;Black Transparent 15.4,0.8,2.6,0.6 Arial,9 3c5d191df1bc4e978ec473b93a5c2b6c Center 0,0,0,0 Text17 Сумма Black Expression Center All;Black;1;Solid;False;4;Black Transparent 0,1.4,12.8,0.5 Arial,8 bd9d27df69ba4000a37b4689e090ccfe Center 0,0,0,0 Text22 1 Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, Expression Center HeaderBand1 True Transparent 0,3.1,28.7,0.6 All;Black;1;Solid;False;4;Black Transparent True 0,0,12.8,0.6 Arial,9 True 0,0,0,0 Text1 {RayonStat.DisplayText} Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, DataColumn Center All;Black;1;Solid;False;4;Black Transparent True 12.8,0,2.6,0.6 Arial,9 True Center 0,0,0,0 Text2 {RayonStat.countAmount} Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, DataColumn Center All;Black;1;Solid;False;4;Black Transparent 15.4,0,2.6,0.6 Arial,9 Center 0,0,0,0 Text3 {RayonStat.Amount} Black n2 DataColumn Center All;Black;1;Solid;False;4;Black Transparent 18,0,2.6,0.6 Arial,9 eca66339fdbc4b059a7b7e79c728fa74 Center 0,0,0,0 Text13 {RayonStat.countNotReg} Black DataColumn Center All;Black;1;Solid;False;4;Black Transparent 20.6,0,2.6,0.6 Arial,9 31c00c014a984693b2184134720d42e9 Center 0,0,0,0 Text18 {RayonStat.sumNotReg} Black n2 DataColumn Center RayonStat DataBand2 True Transparent 0,4.5,28.7,0.6 Top, Right, Bottom;Black;1;Solid;False;4;Black Transparent True 0.6,0,12.2,0.6 Arial,9 True 0,0,0,0 Text4 {usmAdmin.PaymentCode} Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, DataColumn Center All;Black;1;Solid;False;4;Black Transparent True 12.8,0,2.6,0.6 Arial,9 True 20aa4f629b53436586143eb5924c5713 Center 0,0,0,0 Text10 {usmAdmin.countAmount} Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, DataColumn Center All;Black;1;Solid;False;4;Black Transparent True 15.4,0,2.6,0.6 Arial,9 True 6f5cbdb055924349a32bbbdd7faf079b Center 0,0,0,0 Text11 {usmAdmin.Amount} Black n2 HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, DataColumn Center All;Black;1;Solid;False;4;Black Transparent True 18,0,2.6,0.6 Arial,9 True a3df48949f2440938e4227af7c542a76 Center 0,0,0,0 Text12 {usmAdmin.countNotReg} Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, DataColumn Center Top, Left, Bottom;Black;1;Solid;False;4;Black Transparent True 0,0,0.6,0.6 Arial,9 True 0,0,0,0 Text14 Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, All;Black;1;Solid;False;4;Black Transparent True 20.6,0,2.6,0.6 Arial,9 True 770d523d9d8545b883d30c62686a24b5 Center 0,0,0,0 Text19 {usmAdmin.sumNotReg} Black n2 HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20, DataColumn Center Связь usmAdmin DataBand1 None;[193:152:89];1;Solid;False;4;Black Transparent 28.7,16.69,0.38,2.79 Times New Roman,9 0,0,0,0 FooterTextReserved0 12.07.2012 10:56:20 Black HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=False, Angle=90, FirstTabOffset=40, DistanceBetweenTabs=20, 37131552c93c4ddebfab7774dd373a03 True 0.5,0.5,0.5,0.5 Page1 Landscape 21 29.7 Arial,100 [50:0:0:0] System.Dll System.Drawing.Dll System.Windows.Forms.Dll System.Data.Dll System.Xml.Dll Stimulsoft.Controls.Dll Stimulsoft.Base.Dll Stimulsoft.Report.Dll Report 7/12/2012 10:48:41 AM 9/19/2011 2:06:23 PM C:\Users\User\Desktop\for support.mrt 82c75e443e3a44478d7b2b16cb13f789 Report Centimeters 2012.1.1300 CSharp