0

In my Application which is a POS software for restaurants, there is a form which the restaurant owner can use to get the daily, weekly, monthly and custom reports for his/her sales, as well as the total income in that period of time and the number of sold items. Now for the daily report and Custom report, I got no problems, but for the weekly report and monthly, I don't know, how to get the report of sold items of the current week or month. I searched a lot on google but I could not find the solution for my problem.

For example, Today is Monday, the first day of the week. I sold like 30 Burgers and some pizzas. After three days, I want to check the sales report for the current week, meaning from Monday. My app gives me the report from the same day of the last week until the current day. Here are the codes I used for the weekly report.

Connection.Open();
OleDbCommand Cmnd = new OleDbCommand("SELECT SomeFields FROM Table WHERE [Date] between @Date2 and @Date1",Connection);

Cmnd.Parameters.Add("@Date2", DateTime.Now.AddDays(-7).ToShortDateString());
Cmnd.Parameters.Add("@Date1", DateTime.Now.ToShortDateString());

 OleDbDataAdapter GetList = new OleDbDataAdapter(Cmnd);
                DataTable DataT = new DataTable();
                GetList.Fill(DataT);
                Datagridview.DataSource = DataT;
Connection.Close();

Now, this code gives me the sales report from the last 7 days until today, but what I want is to get the report only for the current week. So if it is the 3rd day of the week, it will give me only the report of 3 days.

haku
  • 4,105
  • 7
  • 38
  • 63
Ala Hirorî
  • 7
  • 7
  • 2
    This should really be solved, before going to the database, by figuring out what the start of the current week is, as a `DateTime` or (perferably) a `DateTimeOffset` and _that_ question [has an answer](https://stackoverflow.com/questions/38039/how-can-i-get-the-datetime-for-the-start-of-the-week?rq=1) already. – clarkitect Aug 04 '19 at 23:47
  • Why are you converting dates to strings for the query? Even MS Access has a workable date type – Ňɏssa Pøngjǣrdenlarp Aug 05 '19 at 00:03
  • @ŇɏssaPøngjǣrdenlarp, My Date field Datatype is Date/Time, but I still got Datatype Mismatch Error, and I have been getting so much of the same Error, so I am more comfortable with Converting, to make sure I get no errors. – Ala Hirorî Aug 05 '19 at 00:11
  • It is *impossible* to compare 2 dates and get a data type mismatch error. – Ňɏssa Pøngjǣrdenlarp Aug 05 '19 at 00:21
  • @ŇɏssaPøngjǣrdenlarp, I also find it strange, but even the solution below I used, I did it without converting to string, I got mismatch error until I converted to string and it worked. – Ala Hirorî Aug 05 '19 at 00:52
  • @clarkitect Thanks for letting me know. However, I guess I got a better solution to my problem by asking my own question. Again, Thanks! – Ala Hirorî Aug 05 '19 at 00:55

1 Answers1

0

In your case you need to identify whats the beginning date for the week and the end date for the week. One way to do it is to have a function that will tell you how many days have passed since the beginning of the week. In your case, monday is the beginning so one simple way to do is:

 private int DaysSinceBeginOfWeek(DateTime dt)
    {
        switch (dt.DayOfWeek)
        {
            case DayOfWeek.Monday:
                return 0;
            case DayOfWeek.Tuesday:
                return 1;
            case DayOfWeek.Wednesday:
                return 2;
            case DayOfWeek.Thursday:
                return 3;
            case DayOfWeek.Friday:
                return 4;
            case DayOfWeek.Saturday:
                return 5;
            case DayOfWeek.Sunday:
                return 6;
        }
        throw new Exception("impossible happened");
    }

Then you use it determine begin and end date for the week. Like:

 var beginOfWeek = DateTime.Now.AddDays(-1 * DaysSinceBeginOfWeek(DateTime.Now));
 var endOfWeek = beginOfWeek.AddDays(6);

To find the beginning and the end of month, you could do something like:

 var firstOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
 var endOfMonth = firstOfMonth.AddMonths(1).AddDays(-1);

Plug them in your query and you should get the desired result.

haku
  • 4,105
  • 7
  • 38
  • 63
  • I used your code, it works perfectly as I wanted, but I just made a little change, I made Monday to return 1 and Tuesday 2 and so on, because Monday was not being counted as a day of the current week. After I put the codes in, I just went and ordered something and came back to check the report, Monday was not in, so I just made Monday to return 1 instead of 0 and it worked. Thank you so much for your help. However, I also wanted to ask you, how can I do this for the Current Month also? – Ala Hirorî Aug 05 '19 at 00:48
  • Happy to help! Glad to hear you updated the code to match your situation. Way to go! Also, added snippet on how to determine beginning and end of current month. – haku Aug 05 '19 at 01:25