daily pastebin goal
47%
SHARE
TWEET

Untitled

a guest Feb 21st, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. private string CreateHTMLTable(Int32 month)
  2. {
  3.     StringBuilder strBuilder = new StringBuilder();
  4.     System.Data.DataTable dtAllStaff = new System.Data.DataTable();
  5.     //get all staff
  6.     PersonelApplication.Classes.PersonelClass PersonnelClass = new PersonelClass();
  7.  
  8.     dtAllStaff = PersonnelClass.GetAllPersonel();
  9.  
  10.     //create manpower data table
  11.     System.Data.DataTable dtManPowerDataTable = new System.Data.DataTable();
  12.  
  13.     //create montlhy dt
  14.     //get number of days in month
  15.     int daysInMonth = DateTime.DaysInMonth(DateTime.Now.Year, month);
  16.     //get first day in month
  17.     DateTime firstDayInMonth = new DateTime(DateTime.Now.Year, month, 1);
  18.     //get last day in month
  19.     DateTime lastDayInMonth = new DateTime();
  20.     lastDayInMonth = firstDayInMonth.AddMonths(1).AddDays(-1);
  21.  
  22.  
  23.  
  24.     //start table
  25.     strBuilder.Append("<table>");
  26.  
  27.     //create header based on number of days in the month
  28.     //append tr strat
  29.     strBuilder.Append("<tr>");
  30.     //add name header for personnle
  31.     strBuilder.Append("<th>");
  32.     strBuilder.Append("Staff");
  33.     strBuilder.Append("</th>");
  34.     for (int i = 1; i <= lastDayInMonth.Day; i++)
  35.     {
  36.         strBuilder.Append("<th>");
  37.         strBuilder.Append(i.ToString() + "/" + month.ToString());
  38.         strBuilder.Append("</th>");
  39.     }
  40.  
  41.     //append tr end to header row
  42.     strBuilder.Append("</tr>");
  43.     System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection();
  44.  
  45.     sqlConn.ConnectionString = ConnectionClass.CreateConnection.getConnectionString();
  46.     using (sqlConn = ConnectionClass.CreateConnection.publicGetConn())
  47.     {
  48.         sqlConn.ConnectionString = ConnectionClass.CreateConnection.getConnectionString();
  49.         try
  50.         {
  51.             sqlConn.Open();
  52.             if (sqlConn.State == ConnectionState.Open)
  53.             {
  54.  
  55.                 foreach (DataRow row in dtAllStaff.Rows)
  56.                 {
  57.                     string personnelName = "";
  58.                     string personnelCode = "";
  59.  
  60.                     Int32 personnelID = 0; ;
  61.  
  62.                     personnelCode = row[1].ToString();
  63.                     strBuilder.Append("<tr>");
  64.                     strBuilder.Append("<td>");
  65.  
  66.                     strBuilder.Append(personnelCode);
  67.                     strBuilder.Append("</td>");
  68.                     for (int i = 1; i <= lastDayInMonth.Day; i++)
  69.                     {
  70.                         //here get the each employee's planned role as well
  71.                         //as actual role
  72.                     }
  73.                     strBuilder.Append("</tr>");
  74.                 }
  75.  
  76.             }
  77.         }
  78.         catch (Exception ex)
  79.         {
  80.             //pouplate later
  81.         }
  82.         finally
  83.         {
  84.  
  85.         }
  86.  
  87.     }
  88.  
  89.     //end table
  90.     strBuilder.Append("</table>");
  91.  
  92.  
  93.     return strBuilder.ToString();
  94. }
  95.    
  96. --actual end date for this role is '08-18-2012'
  97. declare @sdate date
  98. set @sdate= '08-14-2012'
  99. SELECT
  100.     CONVERT(date,startdate,101)
  101.     ,CONVERT(date,EndDate,101)
  102.     ,StartDate
  103.     ,EndDate
  104.      ,fk_PersonelID
  105.      ,fk_RoleID
  106.   FROM [dbo].JobRolesTable
  107.   where @sdate between StartDate and EndDate
  108.     and fk_PersonelID = 40
  109.    
  110. DECLARE @sdate DATE = '20120814';
  111.  
  112. SELECT
  113.     CONVERT(DATE,StartDate,101) -- what is the purpose of 101 here?
  114.     ,CONVERT(DATE,EndDate,101)  -- what is the purpose of 101 here?
  115.     ,StartDate
  116.     ,EndDate
  117.      ,fk_PersonelID
  118.      ,fk_RoleID
  119.   FROM [dbo].JobRolesTable
  120.   WHERE @sdate >= StartDate
  121.     AND @sdate < DATEADD(DAY, 1, EndDate)
  122.     AND fk_PersonelID = 40;
  123.    
  124. SELECT *
  125. FROM Table
  126. WHERE (@SearchDate BETWEEN @StartDate AND @EndDate) AND Id=@Id
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top