KKFeuP22

1 // BuildMyString.com generated code. Please enjoy your string responsibly.
2
3StringBuilder industryReportSP = new StringBuilder();
4
5industryReportSP.AppendLine("IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[usp_IndustryRegistrationReport]') AND TYPE IN (N'P',N'PC'))");
6industryReportSP.AppendLine("                DROP PROCEDURE [dbo].usp_IndustryRegistrationReport");
7industryReportSP.AppendLine("            GO");
8industryReportSP.AppendLine("             ");
9industryReportSP.AppendLine("            SET ANSI_NULLS ON");
10industryReportSP.AppendLine("            GO");
11industryReportSP.AppendLine("             ");
12industryReportSP.AppendLine("            SET QUOTED_IDENTIFIER ON");
13industryReportSP.AppendLine("            GO");
14industryReportSP.AppendLine("             ");
15industryReportSP.AppendLine("            CREATE PROCEDURE dbo.usp_IndustryRegistrationReport");
16industryReportSP.AppendLine("                @StartDate DATETIME,");
17industryReportSP.AppendLine("                @EndDate DATETIME");
18industryReportSP.AppendLine("            AS");
19industryReportSP.AppendLine("            BEGIN");
20industryReportSP.AppendLine("               ");
21industryReportSP.AppendLine("                IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp");
22industryReportSP.AppendLine("                SELECT");
23industryReportSP.AppendLine("                    IndustryCategoryId,");
24industryReportSP.AppendLine("                    IndustrySubCategoryId,");
25industryReportSP.AppendLine("                    1 OrderId,");
26industryReportSP.AppendLine("                    IndustryName,");
27industryReportSP.AppendLine("                    MAX(id.Industrialist) AS Industrialist,");
28industryReportSP.AppendLine("                    MAX(StreetName)+','+MAX(Municipal)+'-'+ CAST( MAX(WardNumber) AS VARCHAR(15))+ ',' + MAX(di.DistrictNameUniCode) AS [Address],");
29industryReportSP.AppendLine("                    SUM(id.FixedCapital) AS FixedCapital,");
30industryReportSP.AppendLine("                    SUM(id.CurrentCapital) AS CurrentCapital,");
31industryReportSP.AppendLine("                    SUM(id.TotalCapital) AS TotalCapital,");
32industryReportSP.AppendLine("                    SUM(MaleEmployeeCount) AS MaleEmployeeCount,");
33industryReportSP.AppendLine("                    SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,");
34industryReportSP.AppendLine("                    SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,");
35industryReportSP.AppendLine("                    SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,");
36industryReportSP.AppendLine("                    SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,");
37industryReportSP.AppendLine("                    SUM(ElectricalConsumption) AS ElectricalConsumption,");
38industryReportSP.AppendLine("                    SUM(RegistrationNumber) AS RegistrationNumber,");
39industryReportSP.AppendLine("                    MAX(RegistrationDate) AS RegistrationDate,");
40industryReportSP.AppendLine("                    MAX(IndustryPurpose) AS IndustryPurpose,");
41industryReportSP.AppendLine("                    MAX(id.IndustryState) AS IndustryState,");
42industryReportSP.AppendLine("                    CASE WHEN MAX(ilsi.Name) = N'प्रा.फ' Then MAX(ilsi.Name) END AS Prafa,");
43industryReportSP.AppendLine("                    CASE WHEN MAX(ilsi.Name) = N'प्रा.लि' Then MAX(ilsi.Name) END AS Prali,");
44industryReportSP.AppendLine("                    CASE WHEN MAX(ilsi.Name) = N'सा.फ' Then MAX(ilsi.Name) END AS Safa,");
45industryReportSP.AppendLine("                    CASE WHEN MAX(ity.Name) = N'साना' Then MAX(ity.Name) END AS Sana,");
46industryReportSP.AppendLine("                    CASE WHEN MAX(ity.Name) = N'घरेलु' Then MAX(ity.Name) END AS Gharelu,");
47industryReportSP.AppendLine("                    'record' AS Class          ");
48industryReportSP.AppendLine("                INTO #temp");
49industryReportSP.AppendLine("                FROM IndustryDetails id");
50industryReportSP.AppendLine("                INNER JOIN IndustryLegalStructureInfo ilsi ON id.LegalStructureTypeId = ilsi.Id");
51industryReportSP.AppendLine("                INNER JOIN IndustryTypes ity ON id.IndustryTypeId = ity.Id");
52industryReportSP.AppendLine("				INNER JOIN DistrictInfo di ON id.DistrictId = di.Id");
53industryReportSP.AppendLine("                WHERE RegistrationDate >= @StartDate AND");
54industryReportSP.AppendLine("                      RegistrationDate <= @EndDate         ");
55industryReportSP.AppendLine("                GROUP BY IndustryCategoryId,");
56industryReportSP.AppendLine("                            IndustrySubCategoryId,");
57industryReportSP.AppendLine("                            IndustryName");
58industryReportSP.AppendLine("             ");
59industryReportSP.AppendLine("                SELECT");
60industryReportSP.AppendLine("                    ROW_NUMBER() OVER(ORDER BY IndustryCategoryId, IndustrySubCategoryId, OrderId ASC) AS Id,");
61industryReportSP.AppendLine("                    IndustryName,");
62industryReportSP.AppendLine("                    Industrialist,");
63industryReportSP.AppendLine("                    [Address],");
64industryReportSP.AppendLine("                    FixedCapital,");
65industryReportSP.AppendLine("                    CurrentCapital,");
66industryReportSP.AppendLine("                    TotalCapital,");
67industryReportSP.AppendLine("                    MaleEmployeeCount,");
68industryReportSP.AppendLine("                    FemaleEmployeeCount,");
69industryReportSP.AppendLine("                    MaleEntrepreneurCount,");
70industryReportSP.AppendLine("                    FemaleEntrepreneurCount,");
71industryReportSP.AppendLine("                    YearlyProductionCapacity,");
72industryReportSP.AppendLine("                    Prafa,");
73industryReportSP.AppendLine("                    Prali,");
74industryReportSP.AppendLine("                    Safa,");
75industryReportSP.AppendLine("                    Sana,");
76industryReportSP.AppendLine("                    Gharelu,");
77industryReportSP.AppendLine("                    ElectricalConsumption,");
78industryReportSP.AppendLine("                    RegistrationNumber,");
79industryReportSP.AppendLine("                    RegistrationDate,");
80industryReportSP.AppendLine("                    IndustryPurpose,");
81industryReportSP.AppendLine("                    IndustryState,");
82industryReportSP.AppendLine("                    Class");
83industryReportSP.AppendLine("                   ");
84industryReportSP.AppendLine("                FROM (");
85industryReportSP.AppendLine("                    SELECT");
86industryReportSP.AppendLine("                              IndustryCategoryId,");
87industryReportSP.AppendLine("                              99999 AS IndustrySubCategoryId,");
88industryReportSP.AppendLine("                              3 OrderId,");
89industryReportSP.AppendLine("                              N'जम्मा ' + ic.Name IndustryName,");
90industryReportSP.AppendLine("                              '' Industrialist,");
91industryReportSP.AppendLine("                              '' [Address],");
92industryReportSP.AppendLine("                              SUM(id.FixedCapital) AS FixedCapital,");
93industryReportSP.AppendLine("                              SUM(id.CurrentCapital) AS CurrentCapital,");
94industryReportSP.AppendLine("                              SUM(id.TotalCapital) AS TotalCapital,");
95industryReportSP.AppendLine("                              SUM(MaleEmployeeCount) AS MaleEmployeeCount,");
96industryReportSP.AppendLine("                              SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,");
97industryReportSP.AppendLine("                              SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,");
98industryReportSP.AppendLine("                              SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,");
99industryReportSP.AppendLine("                              SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,");
100industryReportSP.AppendLine("                              SUM(ElectricalConsumption) AS ElectricalConsumption,");
101industryReportSP.AppendLine("                              '' AS RegistrationNumber,");
102industryReportSP.AppendLine("                              null AS RegistrationDate,");
103industryReportSP.AppendLine("                              '' AS IndustryPurpose,");
104industryReportSP.AppendLine("                              '' AS IndustryState,");
105industryReportSP.AppendLine("                             CAST(COUNT(id.Prafa) AS VARCHAR(10)) AS Prafa,");
106industryReportSP.AppendLine("                             CAST(COUNT(id.Prali) AS VARCHAR(10)) AS Prali,");
107industryReportSP.AppendLine("                             CAST(COUNT(id.Safa) AS VARCHAR(10)) AS Safa,");
108industryReportSP.AppendLine("                             CAST(COUNT(id.Sana) AS VARCHAR(10)) AS Sana,");
109industryReportSP.AppendLine("                             CAST(COUNT(id.Gharelu) AS VARCHAR(10)) AS Gharelu,");
110industryReportSP.AppendLine("                             'categoryTotal' AS Class");
111industryReportSP.AppendLine("                    FROM #temp id");
112industryReportSP.AppendLine("                    INNER JOIN IndustryCategories ic ON id.IndustryCategoryId = ic.Id");
113industryReportSP.AppendLine("                    GROUP BY IndustryCategoryId,");
114industryReportSP.AppendLine("                            ic.Name");
115industryReportSP.AppendLine("             ");
116industryReportSP.AppendLine("                UNION");
117industryReportSP.AppendLine("             ");
118industryReportSP.AppendLine("                    SELECT");
119industryReportSP.AppendLine("                              id.IndustryCategoryId,");
120industryReportSP.AppendLine("                              IndustrySubCategoryId,");
121industryReportSP.AppendLine("                              2 OrderId,");
122industryReportSP.AppendLine("                              N'जम्मा ' + isc.Name IndustryName,");
123industryReportSP.AppendLine("                              '' Industrialist,");
124industryReportSP.AppendLine("                              '' [Address],");
125industryReportSP.AppendLine("                              SUM(id.FixedCapital) AS FixedCapital,");
126industryReportSP.AppendLine("                              SUM(id.CurrentCapital) AS CurrentCapital,");
127industryReportSP.AppendLine("                              SUM(id.TotalCapital) AS TotalCapital,");
128industryReportSP.AppendLine("                              SUM(MaleEmployeeCount) AS MaleEmployeeCount,");
129industryReportSP.AppendLine("                              SUM(FemaleEmployeeCount) AS FemaleEmployeeCount,");
130industryReportSP.AppendLine("                              SUM(MaleEntrepreneurCount) AS MaleEntrepreneurCount,");
131industryReportSP.AppendLine("                              SUM(FemaleEntrepreneurCount) AS FemaleEntrepreneurCount,");
132industryReportSP.AppendLine("                              SUM(YearlyProductionCapacity) AS YearlyProductionCapacity,");
133industryReportSP.AppendLine("                              SUM(ElectricalConsumption) AS ElectricalConsumption,");
134industryReportSP.AppendLine("                              '' RegistrationNumber,");
135industryReportSP.AppendLine("                              null AS RegistrationDate,");
136industryReportSP.AppendLine("                              '' IndustryPurpose,");
137industryReportSP.AppendLine("                              '' IndustryState,");
138industryReportSP.AppendLine("                              CAST(COUNT(id.Prafa) AS VARCHAR(10)),");
139industryReportSP.AppendLine("                              CAST(COUNT(id.Prali) AS VARCHAR(10)),");
140industryReportSP.AppendLine("                              CAST(COUNT(id.Safa) AS VARCHAR(10)),");
141industryReportSP.AppendLine("                              CAST(COUNT(id.Sana) AS VARCHAR(10)),");
142industryReportSP.AppendLine("                              CAST(COUNT(id.Gharelu) AS VARCHAR(10)),");
143industryReportSP.AppendLine("                              'subCategoryTotal' AS Class");
144industryReportSP.AppendLine("                    FROM #temp id");
145industryReportSP.AppendLine("                    INNER JOIN IndustrySubCategories isc ON id.IndustrySubCategoryId = isc.Id");
146industryReportSP.AppendLine("                    GROUP BY id.IndustryCategoryId,");
147industryReportSP.AppendLine("                             IndustrySubCategoryId,");
148industryReportSP.AppendLine("                             isc.Name");
149industryReportSP.AppendLine("             ");
150industryReportSP.AppendLine("                UNION");
151industryReportSP.AppendLine("             ");
152industryReportSP.AppendLine("                SELECT * FROM #temp    ");
153industryReportSP.AppendLine("                       ");
154industryReportSP.AppendLine("                UNION");
155industryReportSP.AppendLine("             ");
156industryReportSP.AppendLine("                        SELECT");
157industryReportSP.AppendLine("                                  99999 AS IndustryCategoryId,");
158industryReportSP.AppendLine("                                  99999 AS IndustrySubCategoryId,");
159industryReportSP.AppendLine("                                  4 OrderId,");
160industryReportSP.AppendLine("                                  N'कुल जम्मा ' IndustryName,");
161industryReportSP.AppendLine("                                  '' Industrialist,");
162industryReportSP.AppendLine("                                  '' [Address],");
163industryReportSP.AppendLine("                                  SUM(id.FixedCapital),");
164industryReportSP.AppendLine("                                  SUM(id.CurrentCapital),");
165industryReportSP.AppendLine("                                  SUM(id.TotalCapital),");
166industryReportSP.AppendLine("                                  SUM(MaleEmployeeCount),");
167industryReportSP.AppendLine("                                  SUM(FemaleEmployeeCount),");
168industryReportSP.AppendLine("                                  SUM(MaleEntrepreneurCount),");
169industryReportSP.AppendLine("                                  SUM(FemaleEntrepreneurCount),");
170industryReportSP.AppendLine("                                  SUM(YearlyProductionCapacity),");
171industryReportSP.AppendLine("                                  SUM(ElectricalConsumption),");
172industryReportSP.AppendLine("                                  '' RegistrationNumber,");
173industryReportSP.AppendLine("                                  null AS RegistrationDate,");
174industryReportSP.AppendLine("                                  '' AS IndustryPurpose,");
175industryReportSP.AppendLine("                                  '' AS IndustryState,");
176industryReportSP.AppendLine("                                  CAST(COUNT(id.Prafa) AS VARCHAR(10)),");
177industryReportSP.AppendLine("                                  CAST(COUNT(id.Prali) AS VARCHAR(10)),");
178industryReportSP.AppendLine("                                  CAST(COUNT(id.Safa) AS VARCHAR(10)),");
179industryReportSP.AppendLine("                                  CAST(COUNT(id.Sana) AS VARCHAR(10)),");
180industryReportSP.AppendLine("                                  CAST(COUNT(id.Gharelu) AS VARCHAR(10)),");
181industryReportSP.AppendLine("                                  'grandTotal' AS Class");
182industryReportSP.AppendLine("                        FROM #temp id) x");
183industryReportSP.AppendLine("                ORDER BY IndustryCategoryId, IndustrySubCategoryId, OrderId");
184industryReportSP.AppendLine("             ");
185industryReportSP.AppendLine("            END");
186industryReportSP.AppendLine("             ");
187industryReportSP.AppendLine("            GO");