/// <summary>
/// Pbiera dane wydatków z ostatniego tygodnia
/// </summary>
/// <returns></returns>
public IEnumerable GetReceiptExpensesChartsPerWeek()
{
var last7Days = (from b in Enumerable.Range(1, DateTime.Now.Subtract(DateTime.Now.Date.AddDays(-7)).Days)
select new { Date = DateTime.Now.Date.AddDays(-7).AddDays(b).Date }).ToList();
var q = (from Receipt rs in ReceiptDB.Receipt
where rs.ReceiptDate.Date >= DateTime.Now.Date.AddDays(-7).Date&& rs.ReceiptDate.Date <= DateTime.Now.Date
group rs by rs.ReceiptDate.Date into g
select new
{
ReceiptDate = g.Key,
ReceiptSum = g.Sum(rs => rs.ReceiptMoney)
})
.ToList();
// .OrderBy(o => DateHelper.GetDayOfWeekNumber(o.ReceiptDateOrgName));
var q2 = (from dm in last7Days
join dd in q
on dm.Date equals dd.ReceiptDate into JoinedEmpDept
from dd in JoinedEmpDept.DefaultIfEmpty()
select new
{
ReceiptDate = String.Format("{0}", CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedDayName(dm.Date.DayOfWeek)),
ReceiptSum = dd != null ? dd.ReceiptSum : 0.0
}).ToList();
return(q2);
}
/// <summary>
/// 根据人员查询项目
/// </summary>
public List <XMNick> GetXMProjectListSS(int customerId, int ProjectName)
{
IQueryable <XMNick> query = from b in this._context.XMNicks
join a in this._context.XMProjects
on b.ProjectId equals a.Id
into JoinedEmpDept
from a in JoinedEmpDept.DefaultIfEmpty()
join c in this._context.XMNickCustomerMappings
on b.nick_id equals c.NickId
into JoinedEmpDept2
from c in JoinedEmpDept2.DefaultIfEmpty()
where (a.customerId == customerId || c.CustomerID == customerId) &&
a.IsEnable == true && b.isEnable == true && (a.ProjectTypeId == ProjectName || ProjectName == 0)
//group b by new { nick = b.nick, nick_id = b.nick_id } into p
select b;
return(new List <XMNick>(query.ToList().Distinct()));
}
/// <summary>
/// Pbiera dane wydatków z ostatniego miesiąca
/// </summary>
/// <returns></returns>
public IEnumerable GetReceiptExpensesChartsPerMonth()
{
//var q2 = Enumerable.Range(1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month)) // Days: 1, 2 ... 31 etc.
// .Select(day => new DateTime(DateTime.Now.Year, DateTime.Now.Month, day)) // Map each day to a date
// .AsEnumerable();
var dayOfMonth = (from b in Enumerable.Range(1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month))
select new { Number = b }).ToList();
var q = (from Receipt rs in ReceiptDB.Receipt
where rs.ReceiptDate.Month == DateTime.Now.Month
group rs by new { date = rs.ReceiptDate } into g
select new
{
ReceiptDate = g.Key.date.Day,
ReceiptSum = g.Sum(rs => rs.ReceiptMoney)
}).ToList();
var q2 = (from dm in dayOfMonth
join dd in q
on dm.Number equals dd.ReceiptDate into JoinedEmpDept
from dd in JoinedEmpDept.DefaultIfEmpty()
select new
{
ReceiptDate = dm.Number,
ReceiptSum = dd != null ? dd.ReceiptSum : 0.0
}).ToList();
MathHelper.TrendLineOperand(q2.Select(d => d.ReceiptSum).ToArray());
var q3 = (from dm in q2 select new
{
dm.ReceiptDate,
dm.ReceiptSum,
ReceiptTrend = MathHelper.GetTrendLine(dm.ReceiptDate)
}).ToList();
return(q3);
}
static void Main(string[] args)
{
Project P1 = new Project()
{
ProjectID = 1, ProjectName = "UID"
};
Project P2 = new Project()
{
ProjectID = 2, ProjectName = "RBS"
};
Project P3 = new Project()
{
ProjectID = 3, ProjectName = "XYZ"
};
// Employee List
List <Employee> ListOfEmployees = new List <Employee>();
ListOfEmployees.AddRange((new Employee[]
{
new Employee()
{
ID = 1, Name = "Sunil", ProjectID = 1
},
new Employee()
{
ID = 1, Name = "Anil", ProjectID = 1
},
new Employee()
{
ID = 1, Name = "Suman", ProjectID = 2
},
new Employee()
{
ID = 1, Name = "Ajay", ProjectID = 3
},
new Employee()
{
ID = 1, Name = "Jimmy", ProjectID = 4
}
}));
//Project List
List <Project> ListOfProject = new List <Project>();
ListOfProject.AddRange(new Project[] { P1, P2, P3 });
//Left join
var Ljoin = from emp in ListOfEmployees
join proj in ListOfProject
on emp.ProjectID equals proj.ProjectID into JoinedEmpDept
from proj in JoinedEmpDept.DefaultIfEmpty()
select new
{
EmployeeName = emp.Name,
ProjectName = proj != null ? proj.ProjectName : null
};
//Right outer join
var RJoin = from proj in ListOfProject
join employee in ListOfEmployees
on proj.ProjectID equals employee.ProjectID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new
{
EmployeeName = employee != null ? employee.Name : null,
ProjectName = proj.ProjectName
};
//Printing result of left join
Console.Write("--------------------LEFT JOIN-----------------------");
Console.WriteLine(string.Join("\n", Ljoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray <string>()));
Console.Write("--------------------RIGHT JOIN-----------------------");
//printing result of right outer join
Console.WriteLine(string.Join("\n", RJoin.Select(emp => " Employee Name = " +
emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray <string>()));
Console.ReadLine();
}
