// GET: CurriculumEntryYear
public ActionResult Index(short?Term_Year_Id, short?Department_Id)
{
if (TempData["gagalHapus"] != null)
{
ViewBag.gagalHapus = TempData["gagalHapus"].ToString();
}
if (TempData["berhasilHapus"] != null)
{
ViewBag.berhasilHapus = TempData["berhasilHapus"].ToString();
}
short termYearId = Term_Year_Id ?? 0;
short departmentId = Department_Id ?? 0;
if (Term_Year_Id != null && Department_Id != null)
{
ViewBag.TermYearId = Term_Year_Id;
ViewBag.DepartmentId = Department_Id;
}
ViewBag.Term_Year_Id = new SelectList(db.Mstr_Term_Year.OrderByDescending(ty => ty.Term_Year_Id), "Term_Year_Id", "Term_Year_Name", Term_Year_Id);
ViewBag.Department_Id = new SelectList(db.Mstr_Department.OrderBy(d => d.Department_Code), "Department_Id", "Department_Name", Department_Id);
using (var context = new ConDataContext())
using (var cmd = context.Connection.CreateCommand())
{
context.Connection.Open();
cmd.CommandText = " exec SP_Crosstab2" +
" 'SELECT idEntryYear=cey.Entry_Year_Id, Angkatan = ey.Entry_Year_Name," +
" clasprog = cast(cey.Class_Prog_Id as Varchar(20)) +''|''+ cast(cp.Class_Program_Name as Varchar(20))," +
" kurikulum = cast(cey.Curriculum_Entry_Year_Id as Varchar(20)) +''|''+ cast(c.Curriculum_Name as Varchar(20))" +
" FROM Acd_Curriculum_Entry_Year cey" +
" INNER JOIN Mstr_Term_Year ty ON ty.Term_Year_Id = cey.Term_Year_Id" +
" INNER JOIN Mstr_Department dp ON dp.Department_Id = cey.Department_Id" +
" INNER JOIN Mstr_Class_Program cp ON cp.Class_Prog_Id = cey.Class_Prog_Id" +
" INNER JOIN Mstr_Entry_Year ey ON ey.Entry_Year_Id = cey.Entry_Year_Id" +
" INNER JOIN Mstr_Curriculum c ON c.Curriculum_Id = cey.Curriculum_Id" +
" WHERE ty.Term_Year_Id = " + termYearId + " AND dp.Department_Id = " + departmentId + "'," +
" 'idEntryYear smallint, Angkatan Varchar(100), clasprog Varchar(100), kurikulum Varchar(100)'," +
" 'clasprog'," +
" 'MAX(kurikulum)'," +
" ''," +
" 'ASC'," +
" 'idEntryYear DESC'";
using (var reader = cmd.ExecuteReader())
{
var model = this.Read(reader).ToList();
context.Connection.Close();
return(View(model));
}
}
}
// GET: CostSched
public ActionResult Index(short?Entry_Year_Id, short?Entry_Period_Type_Id)
{
if (TempData["gagalHapus"] != null)
{
ViewBag.gagalHapus = TempData["gagalHapus"].ToString();
}
if (TempData["berhasilHapus"] != null)
{
ViewBag.berhasilHapus = TempData["berhasilHapus"].ToString();
}
short entryYearId = Entry_Year_Id ?? 0;
short entryPeriodTypeId = Entry_Period_Type_Id ?? 0;
if (Entry_Year_Id != null && Entry_Period_Type_Id != null)
{
ViewBag.EntryYearId = Entry_Year_Id;
ViewBag.EntryPeriodTypeId = Entry_Period_Type_Id;
}
ViewBag.Entry_Year_Id = new SelectList(db.Mstr_Entry_Year.OrderByDescending(ey => ey.Entry_Year_Id), "Entry_Year_Id", "Entry_Year_Id", Entry_Year_Id);
ViewBag.Entry_Period_Type_Id = new SelectList(db.Mstr_Entry_Period_Type, "Entry_Period_Type_Id", "Entry_Period_Type_Name", Entry_Period_Type_Id);
using (var context = new ConDataContext())
using (var cmd = context.Connection.CreateCommand())
{
context.Connection.Open();
cmd.CommandText = " exec SP_Crosstab2" +
" 'SELECT idProdi=ct.Department_Id, [Kode Prodi]=dp.Department_Code, idClassProg=ct.Class_Prog_Id, [Nama Prodi]=dp.Department_Name, [Program Kelas]=cp.Class_Program_Name," +
" Angsuran=ct.Payment_Order," +
" date=cast(ct.Cost_Sched_Id as Varchar(20)) +''|''+ cast(CONVERT(date,ct.Start_Date) as Varchar(20)) +''|''+ cast(CONVERT(date,ct.End_Date) as Varchar(20)) +''|''+ ty.Term_Year_Name" +
" FROM Fnc_Cost_Sched ct" +
" INNER JOIN Mstr_Entry_Year ey ON ey.Entry_Year_Id = ct.Entry_Year_Id" +
" INNER JOIN Mstr_Term_Year ty ON ty.Term_Year_Id = ct.Term_Year_Id" +
" INNER JOIN Mstr_Department dp ON dp.Department_Id = ct.Department_Id" +
" INNER JOIN Mstr_Class_Program cp ON cp.Class_Prog_Id = ct.Class_Prog_Id" +
" INNER JOIN Mstr_Entry_Period_Type ept ON ept.Entry_Period_Type_Id = ct.Entry_Period_Type_Id" +
" WHERE ey.Entry_Year_Id = " + entryYearId + " AND ept.Entry_Period_Type_Id = " + entryPeriodTypeId + "'," +
" 'idProdi int, [Kode Prodi] Varchar(100), idClassProg int, [Nama Prodi] Varchar(100), [Program Kelas] Varchar(100), Angsuran int, date Varchar(100)'," +
" 'Angsuran'," +
" 'MAX(date)'," +
" ''," +
" 'ASC'," +
" '[Kode Prodi], [Program Kelas] DESC'";
using (var reader = cmd.ExecuteReader())
{
var model = this.Read(reader).ToList();
context.Connection.Close();
return(View(model));
}
}
}
// GET: SchedSession
public ActionResult Index(short?Term_Year_Id, short?Sched_Type_Id)
{
if (TempData["gagalHapus"] != null)
{
ViewBag.gagalHapus = TempData["gagalHapus"].ToString();
}
if (TempData["berhasilHapus"] != null)
{
ViewBag.berhasilHapus = TempData["berhasilHapus"].ToString();
}
short termYearId = Term_Year_Id ?? 0;
short schedTypeId = Sched_Type_Id ?? 0;
if (Term_Year_Id != null && Sched_Type_Id != null)
{
ViewBag.TermYearId = Term_Year_Id;
ViewBag.SchedTypeId = Sched_Type_Id;
}
ViewBag.Term_Year_Id = new SelectList(db.Mstr_Term_Year.OrderByDescending(ty => ty.Term_Year_Id), "Term_Year_Id", "Term_Year_Name", Term_Year_Id);
ViewBag.Sched_Type_Id = new SelectList(db.Mstr_Sched_Type, "Sched_Type_Id", "Sched_Type_Name", Sched_Type_Id);
using (var context = new ConDataContext())
using (var cmd = context.Connection.CreateCommand())
{
context.Connection.Open();
cmd.CommandText = " exec SP_Crosstab2" +
" 'SELECT dayId = d.Day_Id, Hari = d.Day_Name," +
" sesi = ss.Order_Id," +
" jam = cast(ss.Sched_Session_Id as Varchar(20)) +''|''+ ss.Time_Start +'' - ''+ ss.Time_End" +
" FROM Acd_Sched_Session ss" +
" INNER JOIN Mstr_Term_Year ty ON ty.Term_Year_Id = ss.Term_Year_Id" +
" INNER JOIN Mstr_Sched_Type st ON st.Sched_Type_Id = ss.Sched_Type_Id" +
" INNER JOIN Mstr_Day d ON d.Day_Id = ss.Day_Id" +
" LEFT JOIN Mstr_Class_Program cp ON cp.Class_Prog_Id = ss.Class_Prog_Id" +
" WHERE ty.Term_Year_Id = " + termYearId + " AND st.Sched_Type_Id = " + schedTypeId + "'," +
" 'dayId smallint, Hari Varchar(100), sesi Varchar(100), jam Varchar(100)'," +
" 'sesi'," +
" 'MAX(jam)'," +
" ''," +
" 'ASC'," +
" 'dayId ASC'";
using (var reader = cmd.ExecuteReader())
{
var model = this.Read(reader).ToList();
context.Connection.Close();
return(View(model));
}
}
}
// GET: CostRegNonDPP
public ActionResult Index(short?Class_Prog_Id, short?Term_Year_Id, short?Department_Id, short?Entry_Period_Type_Id)
{
short clasProgId = Class_Prog_Id ?? 0;
short termYearId = Term_Year_Id ?? 0;
short departmentId = Department_Id ?? 0;
short entryPeriodTypeId = Entry_Period_Type_Id ?? 0;
if (Class_Prog_Id != null && Term_Year_Id != null && Department_Id != null && Entry_Period_Type_Id != null)
{
ViewBag.TermYearId = Term_Year_Id;
ViewBag.DepartmentId = Department_Id;
ViewBag.ClasProgId = Class_Prog_Id;
ViewBag.EntryPeriodTypeId = Entry_Period_Type_Id;
}
ViewBag.Term_Year_Id = new SelectList(db.Mstr_Term_Year.OrderByDescending(ty => ty.Term_Year_Id), "Term_Year_Id", "Term_Year_Name", Term_Year_Id);
ViewBag.Department_Id = new SelectList(db.Mstr_Department, "Department_Id", "Department_Name", Department_Id);
ViewBag.Class_Prog_Id = new SelectList(db.Mstr_Class_Program, "Class_Prog_Id", "Class_Program_Name", Class_Prog_Id);
ViewBag.Entry_Period_Type_Id = new SelectList(db.Mstr_Entry_Period_Type, "Entry_Period_Type_Id", "Entry_Period_Type_Name", Entry_Period_Type_Id);
using (var context = new ConDataContext())
using (var cmd = context.Connection.CreateCommand())
{
context.Connection.Open();
cmd.CommandText = " exec SP_Crosstab2" +
" 'SELECT idCi=ci.Cost_Item_Id, [Kode Biaya]=ci.Cost_Item_Code, [Nama Biaya]=ci.Cost_Item_Name," +
" Angkatan=ey.Entry_Year_Id," +
" amount=cast(cr.Amount as Varchar(20)) +''|''+ cast(cr.Cost_Regular_Id as Varchar(20))" +
" FROM Fnc_Cost_Regular cr" +
" INNER JOIN Fnc_Cost_Item ci ON ci.Cost_Item_Id = cr.Cost_Item_Id" +
" INNER JOIN Mstr_Entry_Year ey ON ey.Entry_Year_Id = cr.Entry_Year_Id" +
" INNER JOIN Mstr_Term_Year ty ON ty.Term_Year_Id = cr.Term_Year_Id" +
" INNER JOIN Mstr_Department dp ON dp.Department_Id = cr.Department_Id" +
" INNER JOIN Mstr_Class_Program cp ON cp.Class_Prog_Id = cr.Class_Prog_Id" +
" INNER JOIN Mstr_Entry_Period_Type ept ON ept.Entry_Period_Type_Id = cr.Entry_Period_Type_Id" +
" WHERE ty.Term_Year_Id = " + termYearId + " AND dp.Department_Id = " + departmentId + " AND cp.Class_Prog_Id = " + clasProgId + " AND ept.Entry_Period_Type_Id = " + entryPeriodTypeId + "'," +
" 'idCi smallint, [Kode Biaya] Varchar(100), [Nama Biaya] Varchar(100), Angkatan Varchar(4), amount Varchar(100)'," +
" 'Angkatan'," +
" 'MAX(amount)'," +
" ''," +
" 'DESC' ";
using (var reader = cmd.ExecuteReader())
{
var model = this.Read(reader).ToList();
context.Connection.Close();
return(View(model));
}
}
}
// GET: CostSchedDetail/Resume
public ActionResult Resume(short?Entry_Year_Id, short?Entry_Period_Type_Id, short?Department_Id, short?Class_Prog_Id)
{
short clasProgId = Class_Prog_Id ?? 0;
short entryYearId = Entry_Year_Id ?? 0;
short departmentId = Department_Id ?? 0;
short entryPeriodTypeId = Entry_Period_Type_Id ?? 0;
if (Class_Prog_Id != null && Entry_Year_Id != null && Department_Id != null && Entry_Period_Type_Id != null)
{
ViewBag.EntryYearId = Entry_Year_Id;
ViewBag.DepartmentId = Department_Id;
ViewBag.ClasProgId = Class_Prog_Id;
ViewBag.EntryPeriodTypeId = Entry_Period_Type_Id;
}
ViewBag.Entry_Year_Id = new SelectList(db.Mstr_Entry_Year.OrderByDescending(ey => ey.Entry_Year_Id), "Entry_Year_Id", "Entry_Year_Id", Entry_Year_Id);
ViewBag.Department_Id = new SelectList(db.Mstr_Department, "Department_Id", "Department_Name", Department_Id);
ViewBag.Class_Prog_Id = new SelectList(db.Mstr_Class_Program, "Class_Prog_Id", "Class_Program_Name", Class_Prog_Id);
ViewBag.Entry_Period_Type_Id = new SelectList(db.Mstr_Entry_Period_Type, "Entry_Period_Type_Id", "Entry_Period_Type_Name", Entry_Period_Type_Id);
using (var context = new ConDataContext())
using (var cmd = context.Connection.CreateCommand())
{
context.Connection.Open();
cmd.CommandText = " exec SP_Crosstab2" +
" 'SELECT [Nama Biaya]=ci.Cost_Item_Name, angsuran=cs.Payment_Order, amount=cast(csd.Cost_Sched_Detail_Id as Varchar(20)) +''|''+ cast(csd.Amount as Varchar(20))" +
" FROM Fnc_Cost_Sched_Detail csd" +
" INNER JOIN Fnc_Cost_Sched cs ON cs.Cost_Sched_Id = csd.Cost_Sched_Id" +
" INNER JOIN Fnc_Cost_Item ci ON ci.Cost_Item_Id = csd.Cost_Item_id" +
" WHERE cs.Entry_Year_Id = " + entryYearId + " AND" +
" cs.Entry_Period_Type_Id = " + entryPeriodTypeId + " AND" +
" cs.Department_Id = " + departmentId + " AND" +
" cs.Class_Prog_Id = " + clasProgId + "'," +
" '[Nama Biaya] Varchar(100), angsuran int, amount Varchar(100)'," +
" 'angsuran'," +
" 'MIN(amount)'," +
" ''," +
" 'ASC'," +
" '[Nama Biaya]' ";
using (var reader = cmd.ExecuteReader())
{
var model = this.Read(reader).ToList();
context.Connection.Close();
return(View(model));
}
}
}
// GET: CourseCostPackage
public ActionResult Index(short?Class_Prog_Id, short?Term_Year_Id, short?Department_Id)
{
short clasProgId = Class_Prog_Id ?? 0;
short termYearId = Term_Year_Id ?? 0;
short departmentId = Department_Id ?? 0;
if (Class_Prog_Id != null && Term_Year_Id != null && Department_Id != null)
{
ViewBag.ClasProgId = Class_Prog_Id;
ViewBag.TermYearId = Term_Year_Id;
ViewBag.DepartmentId = Department_Id;
}
ViewBag.Class_Prog_Id = new SelectList(db.Mstr_Department_Class_Program.Where(dcp => dcp.Department_Id == Department_Id), "Class_Prog_Id", "Mstr_Class_Program.Class_Program_Name", Class_Prog_Id).ToList();
ViewBag.Term_Year_Id = new SelectList(db.Mstr_Term_Year.OrderByDescending(ty => ty.Term_Year_Id), "Term_Year_Id", "Term_Year_Name", Term_Year_Id);
ViewBag.Department_Id = new SelectList(db.Mstr_Department.OrderBy(d => d.Department_Code), "Department_Id", "Department_Name", Department_Id);
using (var context = new ConDataContext())
using (var cmd = context.Connection.CreateCommand())
{
context.Connection.Open();
cmd.CommandText = " exec SP_Crosstab" +
" 'SELECT idCct=cct.Course_Cost_Type_Id, Kode=c.Course_Code, Matakuliah=c.Course_Name," +
" angkatan=ccp.Entry_Year_Id," +
" amount=cast(ccp.Amount_Per_Mk as varchar(20))+''|''+ cast(ccp.Course_Cost_Package_Id as varchar(20))" +
" FROM Fnc_Course_Cost_Package ccp INNER JOIN Fnc_Course_Cost_Type cct ON cct.Course_Cost_Type_Id = ccp.Course_Cost_Type_Id" +
" INNER JOIN Acd_Course c ON c.Course_Id = cct.Course_Id" +
" WHERE cct.Term_Year_Id = " + termYearId + " AND cct.Department_Id = " + departmentId + " AND cct.Class_Prog_Id = " + clasProgId + "'," +
" 'angkatan'," +
" 'MAX(amount)[]'," +
" 'idCct,Kode, Matakuliah'," +
" 'DESC'," +
" 'Kode' ";
using (var reader = cmd.ExecuteReader())
{
var model = this.Read(reader).ToList();
context.Connection.Close();
return(View(model));
}
}
}
// GET: CourseCostSks
public ActionResult Index(short?Class_Prog_Id, short?Term_Year_Id)
{
short clasProgId = Class_Prog_Id ?? 0;
short termYearId = Term_Year_Id ?? 0;
if (Class_Prog_Id != null && Term_Year_Id != null)
{
ViewBag.ClasProgId = Class_Prog_Id;
ViewBag.TermYearId = Term_Year_Id;
}
ViewBag.Class_Prog_Id = new SelectList(db.Mstr_Class_Program, "Class_Prog_Id", "Class_Program_Name", Class_Prog_Id);
ViewBag.Term_Year_Id = new SelectList(db.Mstr_Term_Year.OrderByDescending(ty => ty.Term_Year_Id), "Term_Year_Id", "Term_Year_Name", Term_Year_Id);
using (var context = new ConDataContext())
using (var cmd = context.Connection.CreateCommand())
{
context.Connection.Open();
cmd.CommandText = " exec SP_Crosstab" +
" 'SELECT id=dp.Department_Id, Kode=dp.Department_Code, Prodi=dp.Department_Name," +
" angkatan=ccs.Entry_Year_Id," +
" amount=cast(ccs.Amount_Per_Sks as varchar(20))+''|''+ cast(ccs.Course_Cost_Sks_Id as varchar(20))" +
" FROM Fnc_Course_Cost_Sks ccs" +
" INNER JOIN Mstr_Department dp ON dp.Department_Id = ccs.Department_Id" +
" WHERE ccs.Term_Year_Id = " + termYearId + " AND ccs.Class_Prog_Id = " + clasProgId + "'," +
" 'angkatan'," +
" 'MAX(amount)[]'," +
" 'id,Kode, Prodi'," +
" 'DESC'," +
" 'kode' ";
using (var reader = cmd.ExecuteReader())
{
var model = this.Read(reader).ToList();
context.Connection.Close();
return(View(model));
}
}
}
public ExportConDataController(ConDataContext context)
{
this.context = context;
}
public ConDataService(ConDataContext context, NavigationManager navigationManager)
{
this.context = context;
this.navigationManager = navigationManager;
}
