public JsonResult FindData3(DateTime startDate, DateTime endDate, int draw)
{
var json = new DataTablesJson();
var tempEndDate = endDate.AddDays(1);
//var xx = db.Branches.Select(p => p.Name).ToList();
var sql2 = string.Format(@"
select QuizGroups.AccountNo, QuizGroups.AccountDesription, sum(CashOutDetails.Subtotal ) as summary
FROM CutOffs INNER JOIN
CashCards ON CutOffs.Id = CashCards.CutOffId INNER JOIN
Branches ON CutOffs.BranchId = Branches.Id INNER JOIN
CashOutDetails ON CashCards.Id = CashOutDetails.CashOutId INNER JOIN
Quizs ON CashOutDetails.QuizId = Quizs.Id INNER JOIN
QuizGroups ON Quizs.QuizGroupId = QuizGroups.Id
where CashCards.state = 4 and CutOffs.state=1 and CutOffs.[DateEnd] between '{0}' and '{1}'
group by QuizGroups.AccountNo, QuizGroups.AccountDesription", startDate.ToString("yyyy-MM-dd"),
tempEndDate.ToString("yyyy-MM-dd"));
json.draw = draw;
json.recordsTotal = 0;
IList <string[]> list = new List <string[]>();
var con = db.Database.Connection;
if (con.State != ConnectionState.Open)
{
con.Open();
}
var com = con.CreateCommand();
com.CommandText = sql2;
var reader = com.ExecuteReader();
while (reader.Read())
{
var oo = new string[3];
oo[0] = reader.GetString(0);
oo[1] = reader.GetString(1);
oo[2] = reader.GetValue(2).ToString();
list.Add(oo);
}
reader.Close();
con.Close();
json.data = list;
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult FindData(DateTime startDate, DateTime endDate, int branchId, int draw, int start, int length, Order[] order )
{
var json = new DataTablesJson();
json.draw = draw;
json.recordsTotal = db.CutOffs.Count();
var tempEndDate = endDate.AddDays(1);
var dt =
db.CutOffs.Where(p => p.DateEnd >= startDate && p.DateEnd < tempEndDate && p.BranchId == branchId && p.State == StateCutOff.Close).OrderBy(p=>p.Id)
.Skip(start).Take(length).ToList();
json.recordsFiltered =
db.CutOffs.Count(p => p.DateEnd >= startDate && p.DateEnd < tempEndDate && p.BranchId == branchId && p.State == StateCutOff.Close);
var xxx = dt.Select(co => new[]
{
co.Branch.Name, co.State.ToString(), co.PreviousBallance.ToString("N0"), co.EndBallance.ToString("N0"), co.DateEnd.Value.ToString("yyyy/MM/dd"), co.Id.ToString()
}).ToList();
json.data = xxx;
return Json(json, JsonRequestBehavior.AllowGet);
}
public JsonResult FindData(DateTime startDate, DateTime endDate, int branchId, int draw, int start, int length, Order[] order)
{
var json = new DataTablesJson();
json.draw = draw;
json.recordsTotal = db.CutOffs.Count();
var tempEndDate = endDate.AddDays(1);
var dt =
db.CutOffs.Where(p => p.DateEnd >= startDate && p.DateEnd < tempEndDate && p.BranchId == branchId && p.State == StateCutOff.Close).OrderBy(p => p.Id)
.Skip(start).Take(length).ToList();
json.recordsFiltered =
db.CutOffs.Count(p => p.DateEnd >= startDate && p.DateEnd < tempEndDate && p.BranchId == branchId && p.State == StateCutOff.Close);
var xxx = dt.Select(co => new[]
{
co.Branch.Name, co.State.ToString(), co.PreviousBallance.ToString("N0"), co.EndBallance.ToString("N0"), co.DateEnd.Value.ToString("yyyy/MM/dd"), co.Id.ToString()
}).ToList();
json.data = xxx;
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult FindData1(DateTime startDate, DateTime endDate, int draw)
{
var json = new DataTablesJson();
var tempEndDate = endDate.AddDays(1);
var xx = db.Branches.Select(p => p.Name).ToList();
string listBranch = "";
foreach (var b in xx)
{
listBranch += "[" + b + "], ";
}
if (listBranch.Length > 2)
{
listBranch = listBranch.Substring(0, listBranch.Length - 2);
}
var sql1 = string.Format(@"select * from(
SELECT
case QuizGroups.GroupType
when 0 then 'Promosi'
when 1 then 'Keperluan Kantor'
when 2 then 'Keperluan Operasional'
when 3 then 'Entertainment'
when 4 then 'Komunikasi'
when 5 then 'Hotel'
when 6 then 'Irregularity'
when 7 then 'Transportasi'
when 8 then 'Sob'
else '-n/a-' end as GroupType
,
Branches.Name as Branch, CashOutDetails.SubTotal
FROM CutOffs INNER JOIN
CashCards ON CutOffs.Id = CashCards.CutOffId INNER JOIN
Branches ON CutOffs.BranchId = Branches.Id INNER JOIN
CashOutDetails ON CashCards.Id = CashOutDetails.CashOutId INNER JOIN
Quizs ON CashOutDetails.QuizId = Quizs.Id INNER JOIN
QuizGroups ON Quizs.QuizGroupId = QuizGroups.Id
where CashCards.state = 4 and CutOffs.state=1 and CutOffs.[DateEnd] between '{0}' and '{1}'
) as tbl
pivot(sum(SubTotal) for Branch in
({2} ) ) as sumofbranch", startDate.ToString("yyyy-MM-dd"),
tempEndDate.ToString("yyyy-MM-dd"), listBranch);
json.draw = draw;
json.recordsTotal = 0;
IList <string[]> list = new List <string[]>();
var con = db.Database.Connection;
if (con.State != ConnectionState.Open)
{
con.Open();
}
var com = con.CreateCommand();
com.CommandText = sql1;
var reader = com.ExecuteReader();
while (reader.Read())
{
var oo = new string[xx.Count + 1];
oo[0] = reader.GetString(0);
for (int i = 1; i < xx.Count + 1; i++)
{
var temp = reader.GetValue(i) is DBNull ? "0" : reader.GetInt32(i).ToString();
oo[i] = temp;
}
list.Add(oo);
}
reader.Close();
con.Close();
json.data = list;
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult FindData1(DateTime startDate, DateTime endDate, int draw)
{
var json = new DataTablesJson();
var tempEndDate = endDate.AddDays(1);
var xx = db.Branches.Select(p => p.Name).ToList();
string listBranch = "";
foreach (var b in xx)
{
listBranch += "[" + b + "], ";
}
if (listBranch.Length > 2)
listBranch = listBranch.Substring(0, listBranch.Length - 2);
var sql1 = string.Format(@"select * from(
SELECT
case QuizGroups.GroupType
when 0 then 'Promosi'
when 1 then 'Keperluan Kantor'
when 2 then 'Keperluan Operasional'
when 3 then 'Entertainment'
when 4 then 'Komunikasi'
when 5 then 'Hotel'
when 6 then 'Irregularity'
when 7 then 'Transportasi'
when 8 then 'Sob'
else '-n/a-' end as GroupType
,
Branches.Name as Branch, CashOutDetails.SubTotal
FROM CutOffs INNER JOIN
CashCards ON CutOffs.Id = CashCards.CutOffId INNER JOIN
Branches ON CutOffs.BranchId = Branches.Id INNER JOIN
CashOutDetails ON CashCards.Id = CashOutDetails.CashOutId INNER JOIN
Quizs ON CashOutDetails.QuizId = Quizs.Id INNER JOIN
QuizGroups ON Quizs.QuizGroupId = QuizGroups.Id
where CashCards.state = 4 and CutOffs.state=1 and CutOffs.[DateEnd] between '{0}' and '{1}'
) as tbl
pivot(sum(SubTotal) for Branch in
({2} ) ) as sumofbranch", startDate.ToString("yyyy-MM-dd"),
tempEndDate.ToString("yyyy-MM-dd"), listBranch);
json.draw = draw;
json.recordsTotal = 0;
IList<string[]> list = new List<string[]>();
var con = db.Database.Connection;
if (con.State != ConnectionState.Open)
{
con.Open();
}
var com = con.CreateCommand();
com.CommandText = sql1;
var reader = com.ExecuteReader();
while (reader.Read())
{
var oo = new string[xx.Count + 1];
oo[0] = reader.GetString(0);
for (int i = 1; i < xx.Count + 1; i++)
{
var temp = reader.GetValue(i) is DBNull ? "0" : reader.GetInt32(i).ToString();
oo[i] = temp;
}
list.Add(oo);
}
reader.Close();
con.Close();
json.data = list;
return Json(json, JsonRequestBehavior.AllowGet);
}
public JsonResult FindData3(DateTime startDate, DateTime endDate, int draw)
{
var json = new DataTablesJson();
var tempEndDate = endDate.AddDays(1);
//var xx = db.Branches.Select(p => p.Name).ToList();
var sql2 = string.Format(@"
select QuizGroups.AccountNo, QuizGroups.AccountDesription, sum(CashOutDetails.Subtotal ) as summary
FROM CutOffs INNER JOIN
CashCards ON CutOffs.Id = CashCards.CutOffId INNER JOIN
Branches ON CutOffs.BranchId = Branches.Id INNER JOIN
CashOutDetails ON CashCards.Id = CashOutDetails.CashOutId INNER JOIN
Quizs ON CashOutDetails.QuizId = Quizs.Id INNER JOIN
QuizGroups ON Quizs.QuizGroupId = QuizGroups.Id
where CashCards.state = 4 and CutOffs.state=1 and CutOffs.[DateEnd] between '{0}' and '{1}'
group by QuizGroups.AccountNo, QuizGroups.AccountDesription", startDate.ToString("yyyy-MM-dd"),
tempEndDate.ToString("yyyy-MM-dd"));
json.draw = draw;
json.recordsTotal = 0;
IList<string[]> list = new List<string[]>();
var con = db.Database.Connection;
if (con.State != ConnectionState.Open)
{
con.Open();
}
var com = con.CreateCommand();
com.CommandText = sql2;
var reader = com.ExecuteReader();
while (reader.Read())
{
var oo = new string[3];
oo[0] = reader.GetString(0);
oo[1] = reader.GetString(1);
oo[2] = reader.GetValue(2).ToString();
list.Add(oo);
}
reader.Close();
con.Close();
json.data = list;
return Json(json, JsonRequestBehavior.AllowGet);
}