Post
Ask Question

Dynamic Project Scheduling Using MVC and AngularJS



In this article we can see how to create a simple Dynamic Project Scheduling from a database using a Stored Procedure with Pivot result. Display the result to MVC view using AngularJS and Web API 2 without using Entity Framework.

In this example I didn't use Entity Framework. The reason for not using EF is for EF we need to get the result of the select with fixed columns (the columns need to be defined) for example from our Stored Procedure we usually do a select result like “select column1,column2,column3 from table”. But for our example I have used the pivot result and the columns will be displayed dynamically depending on the date range and I am using the “exec sp_executesql @SQLquery;” in my SP to execute the dynamic query I. Insted of using the Entity Framework in my Web API I will be connecting to the database directly and execute the SP to return the result. From my AngularJS Controller I will call the Web API method to return the result.

Project Scheduling

Project Scheduling is a very important part in project planning. The project might be any type, for example software project development planning, production planning and so on. For a realistic example let's consider a car seat manufacturing company. Every week they will produce, for example, 100 sets of seats for a model of car. In the factory everything will go as planned, for example from this week beginning Monday to this week ending Friday a total of 100 seats need to be manufactured and delivered to the customer. Here we can see this is the plan since we need to produce 100 seats and deliver them to the customer. But for some reason the production could only make 90 sets of seats or production has made 100 set of seats in time. To track the production plan with the actual plan we use the Production Schedule Chart. The production plan will have both a start and end date, when the production must be started and when the production needs to be finished. The actual date is the real production start and the end date. The actual start and end dates will be set after the production is completed. If the actual date is the same or below the production end date then it's clear that the production is on time and it can be delivered to the customer. If the actual end date is past the production plan date then the production line must be closely watched and again the next time the same delay should be avoided.

In the project there might be 2 dates available, one is the scheduled start and end dates (this is the initial planned date or the target date for our project) and another one is the actual start and end date (this is when the project is actually started and completed). For all the projects we need to compare both the scheduled and actual dates, if there are greater differences in both of the dates then we need to check whether the project is completed within the scheduled time or if there was a delay in project development.

  1. Create Database and Table

    We will create a SCHED_Master table under the Database 'projectDB'. The following is the script to create a database, table and sample insert query. Run this script in your SQL Server. I have used SQL Server 2012.

    table
    1. --Script to create DB,Table and sample Insert data  
    2. USE MASTER  
    3. GO  
    4.   
    5. -- 1) Check for the Database Exists .If the database is exist then drop and create new DB  
    6. IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'projectDB' )  
    7. DROP DATABASE projectDB  
    8. GO  
    9.   
    10. CREATE DATABASE projectDB  
    11. GO  
    12.   
    13. USE projectDB  
    14. GO  
    15.   
    16.   
    17. CREATE TABLE [dbo].[SCHED_Master](  
    18.     [ID] [int] NOT NULL,  
    19.     [ProjectName] [varchar](100) NULL,  
    20.     [ProjectType] int NULL,  
    21.     [ProjectTypeName] [varchar](100) NULL,  
    22.     [SCHED_ST_DT] [datetime] NULL,  
    23.     [SCHED_ED_DT] [datetime] NULL,    
    24.     [ACT_ST_DT] [datetime] NULL,  
    25.     [ACT_ED_DT] [datetime] NULL,  
    26.     [status] int null  
    27. PRIMARY KEY CLUSTERED   
    28. (  
    29.     [ID] ASC  
    30. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
    31. ) ON [PRIMARY]  
    32.   
    33. -- Insert Query  
    34.   
    35.   
    36.   
    37. INSERT INTO [dbo].SCHED_Master  
    38.            ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])  
    39.      VALUES  
    40.            (1001,'Project1',1,'Urgent','2015-06-01 00:00:00.000','2015-09-02 00:00:00.000'  
    41.             ,'2015-06-22 00:00:00.000','2015-08-26 00:00:00.000',1)  
    42.   
    43.   
    44. INSERT INTO [dbo].SCHED_Master  
    45.            ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])  
    46.      VALUES  
    47.            (1002,'Project1',2,'Important','2015-09-22 00:00:00.000','2015-12-22 00:00:00.000'  
    48.             ,'2015-09-19 00:00:00.000','2015-12-29 00:00:00.000',1)  
    49.   
    50. INSERT INTO [dbo].SCHED_Master  
    51.            ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])  
    52.      VALUES  
    53.            (1003,'Project1',3,'Normal','2016-01-01 00:00:00.000','2016-03-24 00:00:00.000'  
    54.             ,'2016-01-01 00:00:00.000','2016-03-14 00:00:00.000',1)  
    55.   
    56.   
    57. INSERT INTO [dbo].SCHED_Master  
    58.            ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])  
    59.      VALUES  
    60.            (1004,'Project2',1,'Urgent','2015-07-01 00:00:00.000','2015-09-02 00:00:00.000'  
    61.             ,'2015-07-22 00:00:00.000','2015-08-26 00:00:00.000',1)  
    62.   
    63.   
    64. INSERT INTO [dbo].SCHED_Master  
    65.            ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])  
    66.      VALUES  
    67.            (1005,'Project2',2,'Important','2015-09-29 00:00:00.000','2015-12-22 00:00:00.000'  
    68.             ,'2015-09-08 00:00:00.000','2015-12-14 00:00:00.000',1)  
    69.   
    70. INSERT INTO [dbo].SCHED_Master  
    71.            ([ID],[ProjectName],[ProjectType],[ProjectTypeName],[SCHED_ST_DT],[SCHED_ED_DT],[ACT_ST_DT],[ACT_ED_DT],[status])  
    72.      VALUES  
    73.            (1006,'Project2',3,'Normal','2016-01-01 00:00:00.000','2016-03-04 00:00:00.000'  
    74.             ,'2016-01-01 00:00:00.000','2016-02-24 00:00:00.000',1)  
    75.   
    76.   
    77. -- Select Query  
    78.   
    79. select ID,ProjectName,ProjectType,ProjectTypeName,SCHED_ST_DT,SCHED_ED_DT,ACT_ST_DT,ACT_ED_DT,status from SCHED_Master  

Insert Query

  1. INSERT INTO[dbo].SCHED_Master([ID], [ProjectName], [ProjectType], [ProjectTypeName], [SCHED_ST_DT], [SCHED_ED_DT], [ACT_ST_DT], [ACT_ED_DT], [status])  
  2. VALUES(1001, 'Project1', 1, 'Urgent', '2015-06-01 00:00:00.000', '2015-09-02 00:00:00.000', '2015-06-22 00:00:00.000', '2015-08-26 00:00:00.000', 1)  
  3.   
  4.   
  5. INSERT INTO[dbo].SCHED_Master([ID], [ProjectName], [ProjectType], [ProjectTypeName], [SCHED_ST_DT], [SCHED_ED_DT], [ACT_ST_DT], [ACT_ED_DT], [status])  
  6. VALUES(1002, 'Project1', 2, 'Important', '2015-09-22 00:00:00.000', '2015-12-22 00:00:00.000', '2015-09-19 00:00:00.000', '2015-12-29 00:00:00.000', 1)  
  7.   
  8. INSERT INTO[dbo].SCHED_Master([ID], [ProjectName], [ProjectType], [ProjectTypeName], [SCHED_ST_DT], [SCHED_ED_DT], [ACT_ST_DT], [ACT_ED_DT], [status])  
  9. VALUES(1003, 'Project1', 3, 'Normal', '2016-01-01 00:00:00.000', '2016-03-24 00:00:00.000', '2016-01-01 00:00:00.000', '2016-03-14 00:00:00.000', 1)  
  10.   
  11.   
  12. INSERT INTO[dbo].SCHED_Master([ID], [ProjectName], [ProjectType], [ProjectTypeName], [SCHED_ST_DT], [SCHED_ED_DT], [ACT_ST_DT], [ACT_ED_DT], [status])  
  13. VALUES(1004, 'Project2', 1, 'Urgent', '2015-07-01 00:00:00.000', '2015-09-02 00:00:00.000', '2015-07-22 00:00:00.000', '2015-08-26 00:00:00.000', 1)  
  14.   
  15.   
  16. INSERT INTO[dbo].SCHED_Master([ID], [ProjectName], [ProjectType], [ProjectTypeName], [SCHED_ST_DT], [SCHED_ED_DT], [ACT_ST_DT], [ACT_ED_DT], [status])  
  17. VALUES(1005, 'Project2', 2, 'Important', '2015-09-29 00:00:00.000', '2015-12-22 00:00:00.000', '2015-09-08 00:00:00.000', '2015-12-14 00:00:00.000', 1)  
  18.   
  19. INSERT INTO[dbo].SCHED_Master([ID], [ProjectName], [ProjectType], [ProjectTypeName], [SCHED_ST_DT], [SCHED_ED_DT], [ACT_ST_DT], [ACT_ED_DT], [status])  
  20. VALUES(1006, 'Project2', 3, 'Normal', '2016-01-01 00:00:00.000', '2016-03-04 00:00:00.000', '2016-01-01 00:00:00.000', '2016-02-24 00:00:00.000', 1)  
Select Query
  1. select ID,ProjectName,ProjectType,ProjectTypeName,SCHED_ST_DT,SCHED_ED_DT,ACT_ST_DT,ACT_ED_DT,status from SCHED_Master;  
After creating our table we will create a Stored Procedure to display the project schedule result using a Pivot query.

I will explain each step of my procedure so that you can understand it clearly to make your own with your table formats.

Step 1

Create the procedure with a parameter and declare the variable inside the procedure to be used in the SP.

Note here I have set the Fromdate and Todate as static. You can change this as a parametter from SP to get the dynamic results depending on your date range.
  1. Alter PROCEDURE [dbo].[usp_ProjectSchedule_Select]   
  2. @projectId VARCHAR(10) = ''   
  3. AS   
  4. BEGIN   
  5. -- 1. Declared for setting the Schedule Start and End date  
  6. --1.Start /////////////  
  7. Declare @FromDate VARCHAR(20) = '2015-06-08'--DATEADD(mm,-12,getdate())   
  8. Declare @ToDate VARCHAR(20) = '2016-05-06'--DATEADD(mm, 1, getdate())   
  9. -- used for the pivot table result  
  10. DECLARE @MyColumns AS NVARCHAR(MAX),  
  11. @SQLquery AS NVARCHAR(MAX)  
Step 2 

We have defined our project start from date and end date. Now we need to search the project schedule result from the given date.The main purpose of the Project Schedule chart is do display the data range as weeks, months, years or days of any one format with a continuous result within the range.To get the continuous result I will get the days of Sundays from the start and end date. I will display the result as a display of a week so here I have used every week for the Sunday date and stored all the dates to a temp table to display the result.

  1. -- 2.This Temp table is to created for  get all the days between the start date and end date to display as the Column Header                                                        
  2.  --2.Start /////////////                                                                  
  3.  IF OBJECT_ID('tempdb..#TEMP_EveryWk_Sndays') IS NOT NULL                                                                            
  4.     DROP TABLE #TEMP_EveryWk_Sndays                                                                         
  5.                                                                             
  6.  DECLARE @TOTALCount INT                                            
  7.     Select  @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);             
  8.    WITH d AS                                                                         
  9.             (                                                                        
  10.               SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()                                                                         
  11.                 OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))                                                                        
  12.               FROM sys.all_objects                                               
  13.             )                                                                        
  14.                                                                               
  15.          SELECT  distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE))WkStartSundays  ,1 as status                                                                
  16.                                                                              
  17.  into #TEMP_EveryWk_Sndays                                                                      
  18.     FROM d                               
  19.    where                            
  20.         AllDays  <= @ToDate                                        
  21.    AND AllDays  >= @FromDate          
  22.      
  23.    -- test the sample temptable with select query  
  24.   -- select * from #TEMP_EveryWk_Sndays  
  25.    --///////////// End of 2.  

Step 3 

I will join the preceding temp table to the actual Schedule table to compare the dates and produce the result. First I will check for the Schedule result and using the union I will combine the result to the actual result and insert the final result to another temp table to generate our pivot result.

Search Project Detail

Note

For the actual data in the Pivot list I will display the result as: 
  • “-1” : For End Date of both the scheduled and actual result. In my program I will check for the produced value, if its “-1” then I will display the text as “END” with Red background color to notify the user for the end date of each project.

  • “0” : If the result value is “0” then it means the days are not in any schedule or actual days so it should be left blank.

  • “1” : If the result is “1” is to indicate as the scheduled start and end days. I will be using Blue to display the schedule days.

  • “2” : If the result is “1” is to indicate the actual start and end days. I will be using Green to display the schedule days.

This is only a sample procedure that provides a sample program for the project schedule. You can custamize this table, procedure and program depending on your requirements. You can set your own rule and status to display the result.

  1. -- 3. This temp table is created toScedule details with result here i have used the Union ,  
  2.    --the 1st query return the Schedule Project result and the 2nd query returns the Actual Project result both this query will be inserted to a Temp Table  
  3.  --3.Start /////////////  
  4.  IF OBJECT_ID('tempdb..#TEMP_results') IS NOT NULL                                                                            
  5.     DROP TABLE #TEMP_results     
  6.    
  7.        SELECT ProjectName,viewtype,ProjectType,resultnew,YMWK  
  8.        INTO #TEMP_results  
  9.        FROM(  
  10.                 SELECT                                                                  
  11.                          A.ProjectName ProjectName   -- Our Project Name                                         
  12.                         ,'1-Scd' viewtype            -- Our View type first we display Schedule Data and then Actual                                                   
  13.                         , A. ProjectType ProjectType -- Our Project type here you can use your own status as Urgent,normal and etc   
  14.                         ,  Case when   cast(DATEPART( wk, max(A.SCHED_ED_DT)) as varchar(2)) =  cast(DATEPART( wk, WkStartSundays) as varchar(2))  then -1 else  
  15.                             case when min(A.SCHED_ST_DT)<= F.WkStartSundays AND max(A.SCHED_ED_DT) >= F.WkStartSundays                                                          
  16.                           then 1 else 0  end end resultnew  -- perfectResult as i expect     
  17.                         ,  RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+convert(varchar(2),Case when len(DATEPART( wk, WkStartSundays))='1' then '0'+                                  
  18.                           cast(DATEPART( wk, WkStartSundays) as varchar(2)) else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END                               
  19.                           ) as 'YMWK'  -- Here we display Year/month and Week of our Schedule which will be displayed as the Column                   
  20.   
  21.               FROM   -- here you can youe your own table                                                            
  22.                          SCHED_Master A (NOLOCK)         
  23.                                  LEFT OUTER JOIN   
  24.                          #TEMP_EveryWk_Sndays F (NOLOCK)  ON A.status= F.status                                                              
  25.                                                         
  26.                 WHERE  -- Here you can check your own where conditions       
  27.                         A.ProjectName like '%' + @projectId                                                        
  28.                     AND A.status=1                                                                            
  29.                     AND A.ProjectType in (1,2,3)   
  30.                     AND A.SCHED_ST_DT  <= @ToDate                                            
  31.                     AND A.SCHED_ED_DT  >= @FromDate    
  32.                 GROUP BY                                                               
  33.                        A.ProjectName                                                           
  34.                      , A. ProjectType    
  35.                      ,A.SCHED_ED_DT                     
  36.                     ,F.WkStartSundays  
  37.   
  38.     UNION  -- This query is to result the Actual result  
  39.             SELECT                                                                  
  40.                          A.ProjectName ProjectName   -- Our Project Name                                         
  41.                         ,'2-Act' viewtype            -- Our View type first we display Schedule Data and then Actual                                                   
  42.                         , A. ProjectType ProjectType -- Our Project type here you can use your own status as Urgent,normal and etc   
  43.                         ,  Case when   cast(DATEPART( wk, max(A.ACT_ED_DT)) as varchar(2)) =  cast(DATEPART( wk, WkStartSundays) as varchar(2))  then -1 else  
  44.                             case when min(A.ACT_ST_DT)<= F.WkStartSundays AND max(A.ACT_ED_DT) >= F.WkStartSundays                                                          
  45.                            then 2 else 0  end end resultnew  -- perfectResult as i expect   
  46.                           
  47.                         , RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+convert(varchar(2),Case when len(DATEPART( wk, WkStartSundays))='1' then '0'+                                  
  48.                               cast(DATEPART( wk, WkStartSundays) as varchar(2)) else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END                               
  49.                               ) as 'YMWK'  -- Here we display Year/month and Week of our Schedule which will be displayed as the Column                   
  50.   
  51.               FROM   -- here you can youe your own table                                                            
  52.                          SCHED_Master A (NOLOCK)         
  53.                                  LEFT OUTER JOIN   
  54.                          #TEMP_EveryWk_Sndays F (NOLOCK)  ON A.status= F.status                                                              
  55.                                                         
  56.                 WHERE  -- Here you can check your own where conditions        
  57.                         A.ProjectName like '%' + @projectId                                                       
  58.                     AND A.status=1                                                                            
  59.                     AND A.ProjectType in (1,2,3)   
  60.                     AND A.ACT_ST_DT  <= @ToDate                                            
  61.                     AND A.ACT_ED_DT  >= @FromDate    
  62.                 GROUP BY                                                               
  63.                        A.ProjectName                                                           
  64.                      , A. ProjectType    
  65.                      ,A.SCHED_ED_DT                     
  66.                     ,F.WkStartSundays  
  67.   
  68.      )  q                   
  69.   
  70.  --3.End /////////////  
Step 4 

Here I will display the final result using the Pivot query from the final result of temp table result.

  1. --4.Start /////////////  
  2.    
  3.  --here first we get all the YMWK which should be display in Columns we use this in our next pivot query  
  4. select @MyColumns = STUFF((SELECT ',' + QUOTENAME(YMWK)   
  5.                     FROM #TEMP_results  
  6.                     GROUP BY YMWK  
  7.                     ORDER BY YMWK  
  8.             FOR XML PATH(''), TYPE  
  9.             ).value('.', 'NVARCHAR(MAX)')   
  10.         ,1,1,'')  
  11.  --here we use the above all YMWK  to disoplay its result as column and row display  
  12. set @SQLquery = N'SELECT ProjectName,viewtype,ProjectType,' + @MyColumns + N' from   
  13.              (  
  14.                  SELECT   
  15.        ProjectName,   
  16.        viewtype,  
  17.        ProjectType,  
  18.        YMWK,  
  19.         resultnew as resultnew   
  20.     FROM #TEMP_results  
  21.             ) x  
  22.             pivot   
  23.             (  
  24.                  sum(resultnew)  
  25.                 for YMWK in (' + @MyColumns + N')  
  26.             ) p  order by ProjectName, ProjectType,viewtype'  
  27.   
  28. exec sp_executesql @SQLquery;  

Here is the complete code for the Stored Procedure.

  1. Alter PROCEDURE [dbo].[usp_ProjectSchedule_Select]                                                      
  2. @projectId           VARCHAR(10)  = ''                                                                   
  3.                                                            
  4. AS                                                                        
  5. BEGIN                                                         
  6.        
  7.  -- 1. Declared for setting the Schedule Start and End date  
  8.  --1.Start /////////////  
  9.   Declare   @FromDate          VARCHAR(20)  = '2015-06-08'--DATEADD(mm,-12,getdate())                                                             
  10.   Declare   @ToDate            VARCHAR(20)  = '2016-05-06'--DATEADD(mm, 1, getdate())    
  11.   -- used for the pivot table result  
  12.   DECLARE @MyColumns AS NVARCHAR(MAX),  
  13.     @SQLquery  AS NVARCHAR(MAX)       
  14.   --// End of 1.  
  15.     
  16.   -- 2.This Temp table is to created for  get all the days between the start date and end date to display as the Column Header                                                        
  17.  --2.Start /////////////                                                                  
  18.  IF OBJECT_ID('tempdb..#TEMP_EveryWk_Sndays') IS NOT NULL                                                                            
  19.     DROP TABLE #TEMP_EveryWk_Sndays                                                                         
  20.                                                                             
  21.  DECLARE @TOTALCount INT                                            
  22.     Select  @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);             
  23.    WITH d AS                                                                         
  24.             (                                                                        
  25.               SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()                                                                         
  26.                 OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))                                                                        
  27.               FROM sys.all_objects                                               
  28.             )                                                                        
  29.                                                                               
  30.          SELECT  distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE))WkStartSundays  ,1 as status                                                                
  31.                                                                              
  32.  into #TEMP_EveryWk_Sndays                                                                      
  33.     FROM d                               
  34.    where                            
  35.         AllDays  <= @ToDate                                        
  36.    AND AllDays  >= @FromDate          
  37.      
  38.    -- test the sample temptable with select query  
  39.   -- select * from #TEMP_EveryWk_Sndays  
  40.    --///////////// End of 2.  
  41.      
  42.    -- 3. This temp table is created toScedule details with result here i have used the Union ,  
  43.    --the 1st query return the Schedule Project result and the 2nd query returns the Actual Project result both this query will be inserted to a Temp Table  
  44.  --3.Start /////////////  
  45.  IF OBJECT_ID('tempdb..#TEMP_results') IS NOT NULL                                                                            
  46.     DROP TABLE #TEMP_results     
  47.    
  48.        SELECT ProjectName,viewtype,ProjectType,resultnew,YMWK  
  49.        INTO #TEMP_results  
  50.        FROM(  
  51.                 SELECT                                                                  
  52.                          A.ProjectName ProjectName   -- Our Project Name                                         
  53.                         ,'1-Scd' viewtype            -- Our View type first we display Schedule Data and then Actual                                                   
  54.                         , A. ProjectType ProjectType -- Our Project type here you can use your own status as Urgent,normal and etc   
  55.                         ,  Case when   cast(DATEPART( wk, max(A.SCHED_ED_DT)) as varchar(2)) =  cast(DATEPART( wk, WkStartSundays) as varchar(2))  then -1 else  
  56.                             case when min(A.SCHED_ST_DT)<= F.WkStartSundays AND max(A.SCHED_ED_DT) >= F.WkStartSundays                                                          
  57.                           then 1 else 0  end end resultnew  -- perfectResult as i expect     
  58.                         ,  RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+convert(varchar(2),Case when len(DATEPART( wk, WkStartSundays))='1' then '0'+                                  
  59.                           cast(DATEPART( wk, WkStartSundays) as varchar(2)) else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END                               
  60.                           ) as 'YMWK'  -- Here we display Year/month and Week of our Schedule which will be displayed as the Column                   
  61.   
  62.               FROM   -- here you can youe your own table                                                            
  63.                          SCHED_Master A (NOLOCK)         
  64.                                  LEFT OUTER JOIN   
  65.                          #TEMP_EveryWk_Sndays F (NOLOCK)  ON A.status= F.status                                                              
  66.                                                         
  67.                 WHERE  -- Here you can check your own where conditions       
  68.                         A.ProjectName like '%' + @projectId                                                        
  69.                     AND A.status=1                                                                            
  70.                     AND A.ProjectType in (1,2,3)   
  71.                     AND A.SCHED_ST_DT  <= @ToDate                                            
  72.                     AND A.SCHED_ED_DT  >= @FromDate    
  73.                 GROUP BY                                                               
  74.                        A.ProjectName                                                           
  75.                      , A. ProjectType    
  76.                      ,A.SCHED_ED_DT                     
  77.                     ,F.WkStartSundays  
  78.   
  79.     UNION  -- This query is to result the Actual result  
  80.             SELECT                                                                  
  81.                          A.ProjectName ProjectName   -- Our Project Name                                         
  82.                         ,'2-Act' viewtype            -- Our View type first we display Schedule Data and then Actual                                                   
  83.                         , A. ProjectType ProjectType -- Our Project type here you can use your own status as Urgent,normal and etc   
  84.                         ,  Case when   cast(DATEPART( wk, max(A.ACT_ED_DT)) as varchar(2)) =  cast(DATEPART( wk, WkStartSundays) as varchar(2))  then -1 else  
  85.                             case when min(A.ACT_ST_DT)<= F.WkStartSundays AND max(A.ACT_ED_DT) >= F.WkStartSundays                                                          
  86.                            then 2 else 0  end end resultnew  -- perfectResult as i expect   
  87.                           
  88.                         , RIGHT(YEAR(WkStartSundays), 2)+'-'+'W'+convert(varchar(2),Case when len(DATEPART( wk, WkStartSundays))='1' then '0'+                                  
  89.                               cast(DATEPART( wk, WkStartSundays) as varchar(2)) else cast(DATEPART( wk, WkStartSundays) as varchar(2)) END                               
  90.                               ) as 'YMWK'  -- Here we display Year/month and Week of our Schedule which will be displayed as the Column                   
  91.   
  92.               FROM   -- here you can youe your own table                                                            
  93.                          SCHED_Master A (NOLOCK)         
  94.                                  LEFT OUTER JOIN   
  95.                          #TEMP_EveryWk_Sndays F (NOLOCK)  ON A.status= F.status                                                              
  96.                                                         
  97.                 WHERE  -- Here you can check your own where conditions        
  98.                         A.ProjectName like '%' + @projectId                                                       
  99.                     AND A.status=1                                                                            
  100.                     AND A.ProjectType in (1,2,3)   
  101.                     AND A.ACT_ST_DT  <= @ToDate                                            
  102.                     AND A.ACT_ED_DT  >= @FromDate    
  103.                 GROUP BY                                                               
  104.                        A.ProjectName                                                           
  105.                      , A. ProjectType    
  106.                      ,A.SCHED_ED_DT                     
  107.                     ,F.WkStartSundays  
  108.   
  109.      )  q                   
  110.   
  111.  --3.End /////////////  
  112.   
  113.  --4.Start /////////////  
  114.    
  115.  --here first we get all the YMWK which should be display in Columns we use this in our next pivot query  
  116. select @MyColumns = STUFF((SELECT ',' + QUOTENAME(YMWK)   
  117.                     FROM #TEMP_results  
  118.                     GROUP BY YMWK  
  119.                     ORDER BY YMWK  
  120.             FOR XML PATH(''), TYPE  
  121.             ).value('.', 'NVARCHAR(MAX)')   
  122.         ,1,1,'')  
  123.  --here we use the above all YMWK  to disoplay its result as column and row display  
  124. set @SQLquery = N'SELECT ProjectName,viewtype,ProjectType,' + @MyColumns + N' from   
  125.              (  
  126.                  SELECT   
  127.        ProjectName,   
  128.        viewtype,  
  129.        ProjectType,  
  130.        YMWK,  
  131.         resultnew as resultnew   
  132.     FROM #TEMP_results  
  133.             ) x  
  134.             pivot   
  135.             (  
  136.                  sum(resultnew)  
  137.                 for YMWK in (' + @MyColumns + N')  
  138.             ) p  order by ProjectName, ProjectType,viewtype'  
  139.   
  140. exec sp_executesql @SQLquery;  
  141.                                      
  142. END  
If we run the procedure the final output will be like this. Here we can see I will display the result of every week using the Pivot query.

output

Create our MVC Web Application in Visual Studio 2015

After installing our Visual Studio 2015 click Start -> Programs then select Visual Studio 2015 then click Visual Studio 2015 RC.



Click New -> Project - > Select Web -> ASP.NET Web Application. Select your project location and enter your web application Name.



Select MVC and in Add Folders and Core reference for. Select the Web API and click OK.



Procedure to add our Web API Controller

Right-click the Controllers folder then click Add then click Controller.



Since we will create our Web API Controller, select Controller and add Empty Web API 2 Controller. Provide the name for the Web API controller and click OK. Here for my Web API Controller I have used the name “ScheduleController”.



Since we have created a Web API controller, we can see our controller has been inherited ApiController.



Since we all know Web API is a simple and easy to build HTTP Services for Browsers and Mobiles.

Web API has the four methods Get/Post/Put and Delete where:
  • Get is to request data (select).
  • Post is to create data (insert).
  • Put is to update data.
  • Delete is to delete data.
In our example we will use both Get since we need to get all the project schedules from somewhere.

Get Method

In our example I have used only the Get method since I am using only a Stored Procedure. Since I didn't use the Entity Framework, here I have ben connecting to a database and gotten the result of the Stored Procedure to the Datatable.
  1. public class scheduleController: ApiController   
  2. {  
  3.     // to Search Student Details and display the result  
  4.     [HttpGet]  
  5.     public DataTable projectScheduleSelect(string projectID)   
  6.     {  
  7.         string connStr = ConfigurationManager.ConnectionStrings["shanuConnectionString"].ConnectionString;  
  8.         DataTable dt = new DataTable();  
  9.   
  10.         SqlConnection objSqlConn = new SqlConnection(connStr);  
  11.   
  12.         objSqlConn.Open();  
  13.         SqlCommand command = new SqlCommand("usp_ProjectSchedule_Select", objSqlConn);  
  14.         command.CommandType = CommandType.StoredProcedure;  
  15.         command.Parameters.Add("@projectId", SqlDbType.VarChar).Value = projectID;  
  16.         SqlDataAdapter da = new SqlDataAdapter(command);  
  17.   
  18.         da.Fill(dt);  
  19.   
  20.         return dt;  
  21.     }  
  22. }  
In WebConfig I have set the database connection string. In the Web API get method I read the connection string and established the DB Connection. Using the SQL Adapter I got the result from the Stored Procedure by passing the argument and bound the final result to the DataTable and returned the DataTable.

Creating AngularJs Controller

First create a folder inside the Script Folder and I gave the folder the name “MyAngular”.



Now add your Angular Controller inside the folder.

Right-click the MyAngular folder and click Add and New Item then select Web then select AngularJs Controller and provide a name for the Controller. I have given my AngularJs Controller the name “Controller.js”



Once the AngularJs Controller is created, we can see by default the controller will have the code with default module definition and all.



I have changed the preceding code like adding a Module and controller as in the following.

If the AngularJS package is missing then add the package to your project.

Right-click your MVC project and click Manage NuGet Packages. Search for AngularJs and click Install.

NuGet

Now we can see all the AngularJs packages have been installed and we can see all the files in the Script folder.

My Angular

The following is the procedure to create AngularJS script files.

Modules.js
 
Here we add the reference to the Angular.js JavaScript and create a Angular Module named “RESTClientModule”.
  1. // <reference path="../angular.js" />   
  2. /// <reference path="../angular.min.js" />   
  3. /// <reference path="../angular-animate.js" />   
  4. /// <reference path="../angular-animate.min.js" />   
  5. var app;  
  6. (function()   
  7. {  
  8.     app = angular.module("RESTClientModule", ['ngAnimate']);  
  9. })();  
Controllers

In the AngularJS Controller I have done all the business logic and returned the data from the Web API to our MVC HTML page.

First I declared the entire local variable to be used. I have used only one method, “selectScheduleDetails“. In that method I called the Web API method with Project ID as parameter passing and the returned result I stored in the AngularJS variable to display in the MVC HTML page. In the search button click I will be calling the same method by passing the ProjectID as the search parameter.
  1. app.controller("AngularJs_studentsController", function($scope, $timeout, $rootScope, $window, $http) {  
  2.     $scope.date = new Date();  
  3.     $scope.projectId = "";  
  4.     selectScheduleDetails($scope.projectId);  
  5.   
  6.     function selectScheduleDetails(projectId)   
  7.     {  
  8.         $http.get('/api/schedule/projectScheduleSelect/',   
  9.         {  
  10.             params: {  
  11.                 projectId: projectId  
  12.             }  
  13.         }).success(function(data)   
  14.         {  
  15.             $scope.Schedules = data;  
  16.             if ($scope.Schedules.length > 0) {}  
  17.         })  
  18.             .error(function()   
  19.             {  
  20.             $scope.error = "An Error has occured while loading posts!";  
  21.         });  
  22.     }  
  23.   
  24.   
  25.     //Search  
  26.     $scope.searchScheduleDetails = function()   
  27.     {  
  28.   
  29.         selectScheduleDetails($scope.projectId);  
  30.     }  
  31.   
  32. });  
MVC HTML Page

All the final result I will be display in the HTML page. Since here we receive all the result as dynamic we cannot predefine any value in our HTML page. The HTML table header and data we need to be generated dynamically.

Since we need to display the dynamic header and Dynamic data I will be using the nested “ng-repeat” to display the dynamic results in the Header to avoid the duplicate result. I have limited the result to be displayed as 1 ”limitTo:”.



Note: I have used the {{key}} to first display the header result and I have used {{val}} to display the result data.

As I have explained, you already have the numbers are “-1, 0, 1, 2" in the data display. Our final result in the HTML table is the graphical project Scheduling Chart.

I have used the Span tag to display the result in a graphical manner as a chart of the table with appropriate colors filled in by each status.
  1. <table style=" background-color:#FFFFFF; border: solid 2px #6D7B8D; width: 99%;table-layout:fixed;" cellpadding="0" cellspacing="0">  
  2.     <tr style="height: 30px; background-color:#336699 ; color:#FFFFFF ;border: solid 1px #659EC7;" ng-repeat="item in Schedules | limitTo:1">  
  3.         <td width="80" align="center" ng-repeat="(key, val) in item | limitTo:1">  
  4.             <table>  
  5.                 <tr>  
  6.                     <td >  
  7.                                             {{key}}  
  8.   
  9.                     </td>  
  10.                 </tr>  
  11.             </table>  
  12.         </td>  
  13.     </tr>  
  14.     <tr style="height: 30px;  color:darkred ;border: solid 1px #659EC7;" ng-repeat="item in Schedules" >  
  15.         <td width="80" style="border: solid 1px #659EC7;table-layout:fixed;padding:0;" align="center" ng-repeat="(key, val) in item"  >  
  16.             <table cellpadding="0" cellspacing="0">  
  17.                 <tr>  
  18.                     <td align="center" width="60" style="padding:0;">  
  19.                         <div ng-if="key == 'ProjectName' ">  
  20.                                                     {{val}}  
  21.                                                     </div>  
  22.                         <div ng-if="key == 'viewtype' ">  
  23.                                                     {{val}}  
  24.                                                 </div>  
  25.                         <div ng-if="key == 'ProjectType' " >  
  26.                                                     {{val}}  
  27.                                                   
  28.                                                 </div>  
  29.                         <div ng-if="val == '0' && key != 'ProjectType' " ></div>  
  30.                         <div ng-if="val == '1' && key != 'ProjectType'"  >  
  31.                             <span style="background-color: deepskyblue; width: 100%; float:left; display: inline;margin-right:76px;"  > </span>  
  32.                         </div>  
  33.                         <div ng-if="val == '2' && key != 'ProjectType'">  
  34.                             <span style="background-color: limegreen; width: 100%; float:left; display: inline;margin-right:76px;"> </span>  
  35.                         </div>  
  36.                         <div ng-if="val == '-1' && key != 'ProjectType'">  
  37.                             <span style="background-color: red; width: 100%; float:left; display: inline;margin-right:48px;color:white">END</span>  
  38.                         </div>  
  39.                     </td>  
  40.                 </tr>  
  41.             </table>  
  42.         </td>  
  43.     </tr>  </table>  

Final Output

The final output will be like this.As i have already told all the final result will be get from stored procedure using the data Status like “-1,0,1,2” I will display the span tag inside HTML table to display or Project Schedule Chart.

We display Schedule and Actual result inside the HTML table as below.

Here we can see for each project I will display the Schedule chart as blue color with Schedule End date as red color and similarly for each project Actual start date I wil display with green color and End Date with red color.

In my Stored procedure I will check for each Schedule and Actual Start and End date range and I will display the result in Week Order with range to display as the status as 1 for Schedule and 2 for Actual.

For example lets take for result below we can see project1,1-Scd in our table we insert the:

  • Schedule Start Date - 2015-06-01 (If we check for the Week no of this Date it will be as Week 24)
  • Schedule End Date - 2015-09-02 (If we check for the Week no of this Date it will be as Week 36)
  • Actual Start Date - 2015-06-22 (If we check for the Week no of this Date it will be as Week 27)
  • Actual End Date - 2015-08-26 (If we check for the Week no of this Date it will be as Week 35)

We can see in my Stored procedure result for the Start and End date range for each week I will display the result as 1 for Schedule and 2 for Actual. In my MVC HTML page using the AngularJS ng-repeat and ng-if="val == '1' I will check for each result and display the Chart inside Table like below:

 

Note: In the WebConfig file you can find “shanuConnectionString” to change the connection string depending on your SQL Server connection.

Supported Browsers: Chrome and Firefox.


Similar Articles