By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,949 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,949 IT Pros & Developers. It's quick & easy.

specialized Last Business Day of Previous Month

P: 1
Platform: Windows2000, WindowsXP, Windows Vista, etc
Language: C#, ASP.NET
Pre-compiled Libraries: Enterprise Library 3.0 full


I have a requirement to implement in and display in C# and ASP.NET a DataGrid with Updatable rows based on a date retrieved from a data table in SQL Server. Below is the design algorithm.

Expand|Select|Wrap|Line Numbers
  1. 1.  Retrieve the Max(rundate) From MyDataTable
  2. 2.  If the current month -1 = rundate .month from step 1 then
  3.      2a.  Display a message that no updates can be made to the DataGrid at
  4.             this time.
  5.      else
  6.      2b.  Compute the next rundate to insert to MyDataTable based on the
  7.             rundate from step 1.
  8. 3.  Insert a set amount of rows of data (hardcoded values in a stored proc) with
  9.      the computed date and an amount passed to a stored procedure and then
  10.      executed
  11. 4.  Display Updatable grid with new data after insert has committed
  12.  
  13.  

In step 2a I need to get a date. this date has the following requirements:

1. it must be greater than the Max(rundate) from MyDataTable.
2. it must be 1 month in the past.
3. it must be the End of The Month and more specifically it must be
the Last Business Day of the Month (cannot be a Sat Sun Or Fed Holiday).
4. and finally If step 2b is called the date must be the last business day
of the previous month from current aka if the last business day of march
is the 31 and today is the April 1st and the year is 2008 then the date
inserted into MyDataTable should be 2/29/2008

Here is the code ive implemented so far:

Expand|Select|Wrap|Line Numbers
  1. protected void Page_Load(object sender, EventArgs e)
  2.     {
  3.         if (!IsPostBack)
  4.         {
  5.             try
  6.             {
  7.                 localhost.Service svc = new localhost.Service();
  8.  
  9.                 // Step 1 code to get xxxxxx max rundate here:
  10.  
  11.                 DataSet datedata = new DataSet();
  12.                 datedata = svc.QueryDate();
  13.                 DateTime xxxxdate = Convert.ToDateTime(datedata.Tables[0].Rows[0][0].ToString().Trim());
  14.                 DateTime curdate = DateTime.Now;
  15.                 // Step 2 If the current month -1 is equal to the rundate
  16.                 // from XXXXXXX display a message that no updates are possible at this time
  17.                 if (curdate.Month - 1 == xxxxdate.Month & curdate.Year == vfccdate.Year)
  18.                 {
  19.                     throw new Exception("No updates available for the current month. If you wish to update the XXXXXX data for "
  20.                                             + xxxxx + " use the override button below.");
  21.                 }
  22.  
  23.                 // Step 3 Otherwise compute the next rundate based on the max rundate from step 1
  24.                 else
  25.                 {
  26.                     // Last Business Day Of Month Validation.
  27.                     DateTime dtResult = getLastBusinessDayOfMonth(xxxxdate);
  28.  
  29.                      // If the month is more than one month out we need to insert new data rows
  30.                         if (dtResult.Month != DateTime.Now.Month - 1)
  31.                         {
  32.                             curdate = getLastBusinessDayOfMonth(curdate);
  33.                             DataSet dsReturn = new DataSet();
  34.                             dsReturn = svc.populateBaseDataGridsWithDate("MyDataTable", curdate);
  35.  
  36.  
  37.                         }
  38.                         dgMyDataGrid.DataSource = svc.populateBaseDataGridsWithDate("MyDataTable", dtResult);
  39.                         dgMyDataGrid.DataBind();
  40.  
  41.  
  42.  
  43.  
  44.                 }
  45.  
  46.             }
  47.             catch (Exception ex)
  48.             {
  49.                 this.lblErrorMessage.Text = "An error occured in:  " + ex.Message.ToString() +
  50.                     " Raised by the following inner exception:  " + ex.InnerException.ToString();
  51.                 this.btnOverrideData.Enabled = true;
  52.                 this.btnOverrideData.Visible = true;
  53.  
  54.             }
  55.          }
  56.          if (dgVFCC_SettledPositions.Items.Count <= 0)
  57.          {
  58.              dgVFCC_SettledPositions.Visible = false;
  59.              lblErrorMessage.Visible = true;
  60.          }
  61.  
  62.     }
  63.  
  64.    ...
  65.  
  66. private DateTime getLastBusinessDayOfMonth(DateTime dtDate)
  67.     {
  68.         try
  69.         {
  70.             // get the last day of the month
  71.             DateTime dtTest = new DateTime();
  72.             dtTest = dtDate;
  73.             dtTest.AddMonths(1);
  74.             dtTest.AddDays(-(dtTest.Day));
  75.  
  76.  
  77.  
  78.  
  79.             if (dtTest.Month == 5 & dtTest.DayOfWeek == DayOfWeek.Monday)   // Memorial Day can fall on the last day of the month so check for it first
  80.             {
  81.                 dtTest.AddDays(-3);
  82.                 return dtTest;
  83.             }
  84.             else
  85.             {
  86.                 if (dtTest.DayOfWeek == DayOfWeek.Saturday)
  87.                 {
  88.                     // this should insure a Friday if the day of week is a Saturday
  89.                     dtTest.AddDays(-1);
  90.                     return dtTest;
  91.                 }
  92.                 else if (dtTest.DayOfWeek == DayOfWeek.Sunday)
  93.                 {
  94.                     // this should insure a Friday if the day of week is a Sunday
  95.                     dtTest.AddDays(-2);
  96.                     return dtTest;
  97.                 }
  98.                 else
  99.                 {
  100.                     // If dtTest is anything other than this return it its a valid workday
  101.                     return dtTest;
  102.                 }
  103.                 // Return the value regardless of what happened in the conditional
  104.                 //return dtTest;
  105.             }
  106.  
  107.         }
  108.         catch (Exception ex)
  109.         {
  110.             // the only reason we should be in this catch block is if a null date was passed to the function
  111.             // let the user know and exit gracefully.
  112.  
  113.             this.lblErrorMessage.Text = "A NullDate Exception was encountered in " + ex.InnerException + "Contact your Systems Administrator for help with this function.";
  114.             this.lblErrorMessage.Visible = true;
  115.             this.btnOverrideData.Enabled = false;
  116.             this.btnOverrideData.Visible = false;
  117.             this.dgMyDataGrid.Visible=false;
  118.             this.dgMyDataGrid.Enabled=false;
  119.  
  120.  
  121.  
  122.         }
  123.         return dtDate;
  124.     }
  125.  
  126.  
  127. ...
  128.  
  129.  
  130.  
  131.  
  132.  
  133.  
Expand|Select|Wrap|Line Numbers
  1. //Service.asmx relevant code:
  2.  
  3. [WebMethod(EnableSession = true, Description = "Populate a Disconnected Dataset based on TableName for base data tables and a DateTime data value",
  4.         TransactionOption = TransactionOption.Supported)]
  5.     public DataSet populateBaseDataGridsWithDate(string TableName, DateTime rundate)
  6.     {
  7.         return getData(TableName,rundate);
  8.     }
  9.  
  10. private DataSet getData(string TableName, DateTime rundate)
  11.     {
  12.  
  13.  
  14.         Database sqldb = DatabaseFactory.CreateDatabase();
  15.         DataSet ServiceResults = new DataSet();
  16.         try
  17.         {
  18.  
  19.             if (TableName != "MyOtherDataTable")
  20.             {
  21.                 switch (TableName)
  22.                 {
  23.                     case "MyDataTable":
  24.                         DataSet ds5 = new DataSet();
  25.                         string strFlag = isInsertUpdate(rundate);
  26.                         string storedProcName;
  27.                         Decimal dmMyVar = 0.00M;
  28.                         string BusId = "999999";
  29.                         DbCommand cmd; ; 
  30.                         if (strFlag == "Insert")
  31.                         {
  32.                             storedProcName = "usp_InsertMyDataTable";
  33.                             cmd = sqldb.GetStoredProcCommand(storedProcName);
  34.                             sqldb.AddInParameter(cmd, "@myVar", DbType.Currency, dmMyVar);
  35.                             sqldb.AddInParameter(cmd, "@rundate", DbType.DateTime, rundate);
  36.                         }
  37.                         else
  38.                         {
  39.  
  40.                             storedProcName = "usp_getMyDataTable";
  41.                             cmd = sqldb.GetStoredProcCommand(storedProcName);
  42.                             sqldb.AddInParameter(cmd, "@rundate",DbType.DateTime, rundate);
  43.                         }
  44.  
  45.  
  46.                         return ds5 = sqldb.ExecuteDataSet(cmd);
  47.                     default:
  48.                         throw new ArgumentOutOfRangeException("Table Name not recognized");
  49.                 }
  50.  
  51.             }
  52.             else
  53.             {
  54.  
  55.                 DataSet dsExceptions = new DataSet();
  56.                 return dsExceptions = sqldb.ExecuteDataSet("usp_LoadMyOtherDataTable");
  57.  
  58.  
  59.             }
  60.         }
  61.         catch (ArgumentOutOfRangeException aorex)
  62.         {
  63.             Logger.Write(aorex.Message.ToString());
  64.  
  65.         }
  66.         catch (Exception ex)
  67.         {
  68.             Logger.Write(ex.Message.ToString());
  69.  
  70.         }
  71.         return ServiceResults;
  72.  
  73.  
  74.  
  75.  
  76.  
  77.  
  78.  
  79.  
  80.  
  81.     }
  82.  
  83. ...
  84.  
  85. private string isInsertUpdate(DateTime rundate)
  86.     {
  87.         Database sqldb = DatabaseFactory.CreateDatabase();
  88.         string storedProcName = "usp_QueryRunDate";
  89.         DbCommand cmd = sqldb.GetStoredProcCommand(storedProcName);
  90.         sqldb.AddInParameter(cmd, "@querydate", DbType.DateTime, rundate);
  91.         DataSet dsReturn = sqldb.ExecuteDataSet(cmd);
  92.         string ReturnValue = dsReturn.Tables[0].Rows[0][0].ToString();
  93.         if (ReturnValue != "0")
  94.         {
  95.             return "Update";
  96.         }
  97.         else
  98.         {
  99.             return "Insert";
  100.         }
  101.     }
  102.  
  103.  
The base issue is getting all of the requirements met for the rundate I need to try and calculate the DATE not the month day and year Int values that meets the conditions each month.

Any help would be greatly appreciated. If I have been overly verbose let me know and I will try to make this more concise
Apr 1 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.