# Every 7days Budget or 1 week RSS

## 12 replies

Last post Jul 18, 2017 10:55 AM by AngelinaJolie

Member

14 Points

143 Posts

### Every 7days Budget or 1 week

Good day to all master in asp forum,

I want my budget per department  7 days or one week budgets

like for example in my calendar

//8-Saturday,9-Sunday,10-Monday,11-Tuesday,12-Wednesday, 13-Thursday and 14-Friday

workweek28 - 8,9,10,11,12,13,14  is equal for 7 days or one week budget

//15-Saturday,16-Sunday,17-Monday,18-Tuesday,19-Wednesday, 20-Thursday and 21-Friday

workweek29 - 15,16,17,18,19,20,21 another 7 days budgets or one week budget

see my code below

how can a fixed this

```var Month = DateTime.Now.Month;
var Year = DateTime.Now.Year;

var Budget = BudgetDb.List()
.Where(c => c.YEAR == 2017 && c.MONTH == Month && c.DEPARTMENTID.Trim() == Employee.DEPTCODE.Trim())
.Select(i => i.BUDGET).FirstOrDefault();

var Expences = (from i in TRANSACTIONSISSUED
join r in ListTrans
on i.REQUESTNO.Trim() equals r.REQUESTNO.Trim()
where r.DEPARTMENT.Trim() == Employee.DEPTCODE.Trim()
&& r.DATETIME.Month == Month && r.DATETIME.Year == Year
select new
{
Cost = i.COST,
Qty  = i.QTY
}).Sum(e => e.Cost*e.Qty);

var Balance = Budget - Expences;

ViewBag.Budget = Budget;
ViewBag.Expences = Expences;
ViewBag.Balance = Balance;```

Thank you

Member

14 Points

143 Posts

### Re: Every 7days Budget or 1 week

#### Cloudfiers

Good day to all master in asp forum,

I want my budget per department  7 days or one week budgets

like for example in my calendar

//8-Saturday,9-Sunday,10-Monday,11-Tuesday,12-Wednesday, 13-Thursday and 14-Friday

workweek28 - 8,9,10,11,12,13,14  is equal for 7 days or one week budget

//15-Saturday,16-Sunday,17-Monday,18-Tuesday,19-Wednesday, 20-Thursday and 21-Friday

workweek29 - 15,16,17,18,19,20,21 another 7 days budgets or one week budget

see my code below

how can a fixed this

```var Month = DateTime.Now.Month;
var Year = DateTime.Now.Year;

var Budget = BudgetDb.List()
.Where(c => c.YEAR == 2017 && c.MONTH == Month && c.DEPARTMENTID.Trim() == Employee.DEPTCODE.Trim())
.Select(i => i.BUDGET).FirstOrDefault();

var Expences = (from i in TRANSACTIONSISSUED
join r in ListTrans
on i.REQUESTNO.Trim() equals r.REQUESTNO.Trim()
where r.DEPARTMENT.Trim() == Employee.DEPTCODE.Trim()
&& r.DATETIME.Month == Month && r.DATETIME.Year == Year
select new
{
Cost = i.COST,
Qty  = i.QTY
}).Sum(e => e.Cost*e.Qty);

var Balance = Budget - Expences;

ViewBag.Budget = Budget;
ViewBag.Expences = Expences;
ViewBag.Balance = Balance;```

Thank you

I need your help  guys, how can i achieve this?

thank you

All-Star

46584 Points

10228 Posts

### Re: Every 7days Budget or 1 week

Jul 10, 2017 03:09 PM|bruce (sqlwork.com)|LINK

you would need to describe your requirements more. weeks cross year and month boundaries. so you do have a fiscal calendar you use?  common approach is to create a Date dimension table that you join to

```create table DateDim (
TheDate Datetime not null primary key,
FiscalYear int not null,
FiscalWeek int not null,
FiscalDayOfYear int not null,
FiscalQuarter int not null,
DayOfWeek int not null
)```

bruce (sqlwork.com)

Contributor

6167 Points

1807 Posts

### Re: Every 7days Budget or 1 week

#### bruce (sqlwork.com)

you would need to describe your requirements more. weeks cross year and month boundaries. so you do have a fiscal calendar you use?  common approach is to create a Date dimension table that you join to

```create table DateDim (
TheDate Datetime not null primary key,
FiscalYear int not null,
FiscalWeek int not null,
FiscalDayOfYear int not null,
FiscalQuarter int not null,
DayOfWeek int not null
)```

You are a smart MOFO Bruce.  I've learned a lot from you over the years.

Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.

Member

14 Points

143 Posts

### Re: Every 7days Budget or 1 week

Good Day sir bruce,

I don't have fiscal calendar used, but now i already created the table  as you said,

my problem sir bruce how can i communicate with my controller approving

see my code below

```var Month = DateTime.Now.Month;
var Year = DateTime.Now.Year;

var Budget = BudgetDb.List()
.Where(c => c.YEAR == 2017 && c.MONTH == Month && c.DEPARTMENTID.Trim() == Employee.DEPTCODE.Trim())
.Select(i => i.BUDGET).FirstOrDefault();

var Expences = (from i in TRANSACTIONSISSUED
join r in ListTrans
on i.REQUESTNO.Trim() equals r.REQUESTNO.Trim()
where r.DEPARTMENT.Trim() == Employee.DEPTCODE.Trim()
&& r.DATETIME.Month == Month && r.DATETIME.Year == Year
select new
{
Cost = i.COST,
Qty  = i.QTY
}).Sum(e => e.Cost*e.Qty);

var Balance = Budget - Expences;

ViewBag.Budget = Budget;
ViewBag.Expences = Expences;
ViewBag.Balance = Balance;```

Thank  you sir bruce

Member

14 Points

143 Posts

### Re: Every 7days Budget or 1 week

and also here is my budget model how can i communicate with new table fiscal year,

see the code below

```using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.Web.Mvc;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;

namespace WarehouseRtoRSystem.Models
{

public class BudgetModel
{
public int MONTH { get; set; }
public int YEAR { get; set; }

public string DEPARTMENTID { get; set; }
public DateTime DATETIME { get; set; }
public double BUDGET { get; set; }

}

public class BudgetViewModel : BudgetModel
{

public string DEPARTMENTNAME { get; set; }
public double EXPENCES { get; set; }
public double BALANCE { get; set; }

}

public class BudgetContext
{
private readonly OracleCommand cmd = new OracleCommand();
private OracleConnection Conn = new OracleConnection();
private readonly OracleConnModel ORCONN = new OracleConnModel();

public List<BudgetViewModel> List()
{
var Departments = new List<BudgetViewModel>();

///SQL QUERY
Conn = ORCONN.con;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
try
{

cmd.Connection = Conn;
cmd.CommandText = "SELECT * From PH.SYSTEMBUDGET";
cmd.CommandType = CommandType.Text;

{
var Dept = new BudgetViewModel();
Dept.MONTH = dr.GetInt32(0);
Dept.YEAR = dr.GetInt32(1);
Dept.DEPARTMENTID = dr.GetString(2);
Dept.DATETIME = dr.GetDateTime(3);
Dept.BUDGET = dr.GetDouble(4);

}
}
finally
{
Conn.Close();
}
return Departments;
}

public string insert(BudgetViewModel model)
{
Conn = ORCONN.con;
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}

try
{

cmd.Connection = Conn;
//var date = new DateTime();
// date = DateTime.Now;

var query = "INSERT into PH.SYSTEMBUDGET(";

query += "MONTH,";
query += "YEAR,";
query += "DEPARTMENTID,";
query += "DATETIME,";
query += "BUDGET";

query += ")";

query += "VALUES(";

query += "'" + model.MONTH + "',";
query += "'" + model.YEAR + "',";
query += "'" + model.DEPARTMENTID + "',";
query += "TO_DATE('" + DateTime.Now + "','MM/DD/YYYY HH:MI:SS AM'),";
query +=  "'"+ model.BUDGET + "'" ;

query += ")";

cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

}
catch(Exception e)
{

Console.WriteLine("{0} Exception caught.", e);

}
finally
{
Conn.Close();
}

return "Seccessfully inserted";
}
}
}```

Thank you again

Member

14 Points

143 Posts

### Re: Every 7days Budget or 1 week

I need you help guys

Member

14 Points

143 Posts

### Re: Every 7days Budget or 1 week

any update sir

Contributor

2420 Points

1194 Posts

### Re: Every 7days Budget or 1 week

Hi Cloudfiers,

Based on your code ,what I could think first is to find which day is the first day and which day is the end day.

1. pass a date which user select(Here is DateTime.Now.DayOfWeek in this example):

```  var first=   DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek)))).ToShortDateString();  //q: "7/9/2017" , for now ,7.9 is the first day.
var last = DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek)))).ToShortDateString();//w: "7/15/2017"```

2:

```var Budget = BudgetDb
.Where(c => c.YEAR == 2017 && c.MONTH == Month && c.DEPARTMENTID.Trim() == Employee.DEPTCODE.Trim()   && c.Date >= (First day of which you need 7/9/2017)  && c.Date<= last)
so that  you could get the one week Budget .```

With Regards,

Angelina Jolie

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Member

14 Points

143 Posts

### Re: Every 7days Budget or 1 week

#### AngelinaJolie

Hi Cloudfiers,

Based on your code ,what I could think first is to find which day is the first day and which day is the end day.

1. pass a date which user select(Here is DateTime.Now.DayOfWeek in this example):

```  var first=   DateTime.Now.AddDays(Convert.ToDouble((0 - Convert.ToInt16(DateTime.Now.DayOfWeek)))).ToShortDateString();
//q: "7/9/2017" , for now ,7.9 is the first day.
var last = DateTime.Now.AddDays(Convert.ToDouble((6 - Convert.ToInt16(DateTime.Now.DayOfWeek)))).ToShortDateString();
//w: "7/15/2017"```

2:

```var Budget = BudgetDb
.Where(c => c.YEAR == 2017 && c.MONTH == Month && c.DEPARTMENTID.Trim() == Employee.DEPTCODE.Trim()   && c.Date >= (First day of which you need 7/9/2017)  && c.Date<= last)
so that  you could get the one week Budget .```

With Regards,

Angelina Jolie

I need to create new table ? for the first day and end day?

can you give a sample program based on my related problem

thank you

Contributor

2420 Points

1194 Posts

### Re: Every 7days Budget or 1 week

Hi Cloudfiers,

#### Cloudfiers

I need to create new table ? for the first day and end day?

Sorry for my later .No ,we doesn't need to create new table and what my idea is just show user the calendar, when use click a specify date ,so we could get a date(like 7/14/2017),

Next step to refer to my code above ;

And something js code could shows week calendar like this https://mvc.daypilot.org/tutorial/dayweekmonth/

Hope it is helpful to you.

With Regards,

Angelina Jolie

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Member

14 Points

143 Posts

### Re: Every 7days Budget or 1 week

#### AngelinaJolie

Hi Cloudfiers,

#### Cloudfiers

I need to create new table ? for the first day and end day?

Sorry for my later .No ,we doesn't need to create new table and what my idea is just show user the calendar, when use click a specify date ,so we could get a date(like 7/14/2017),

Next step to refer to my code above ;

And something js code could shows week calendar like this https://mvc.daypilot.org/tutorial/dayweekmonth/

Hope it is helpful to you.

With Regards,

Angelina Jolie

good day jolie ,  thank you for the info. but I don't get it

can provide me the code based on my code post..

Thank you

Contributor

2420 Points

1194 Posts