public static bool GetAksesRole(int USER_ID = 0, int MENU_ID = 0, int ACCESS_ID = 0, string TYPE = "view")
{
//var ACCESS_DETAIL_TYPE = 0;
//if (TYPE == "view")
//{
// ACCESS_DETAIL_TYPE = 1;
//}
//else if (TYPE == "create")
//{
// ACCESS_DETAIL_TYPE = 2;
//}
//else if (TYPE == "update")
//{
// ACCESS_DETAIL_TYPE = 3;
//}
//else if (TYPE == "delete")
//{
// ACCESS_DETAIL_TYPE = 4;
//}
//else if (TYPE == "approve")
//{
// ACCESS_DETAIL_TYPE = 5;
//}
//else if (TYPE == "print")
//{
// ACCESS_DETAIL_TYPE = 6;
//}
var CekAkses = false;
using (var db = new SISPKEntities())
{
int id = db.Database.SqlQuery <int>("SELECT FROM DUAL").SingleOrDefault();
return(CekAkses);
}
}
public static string buat_title(int parent = 0)
{
using (var db = new SISPKEntities())
{
var menu = ""; // inisialisasi awal
var tipe = HttpContext.Current.Request.RequestContext.RouteData.Values["tipe"];
var controller = HttpContext.Current.Request.RequestContext.RouteData.Values["controller"];
var action = HttpContext.Current.Request.RequestContext.RouteData.Values["action"];
var host = "/" + tipe + "/" + controller;
var hasil = db.Database.SqlQuery <SYS_MENU>("select * from ( select * from SYS_MENU WHERE MENU_URL LIKE '" + host + "%' order by MENU_PARENT_ID ASC ) where ROWNUM = 1").SingleOrDefault();
if (hasil != null)
{
if (hasil.MENU_PARENT_ID == 0)
{
menu += hasil.MENU_NAME;
}
else
{
var hasil2 = db.Database.SqlQuery <SYS_MENU>("SELECT * FROM SYS_MENU WHERE MENU_ID='" + hasil.MENU_PARENT_ID + "'").SingleOrDefault();
menu += hasil2.MENU_NAME + " - " + hasil.MENU_NAME;
}
}
return(menu);
}
}
public static int GetSequence(String _table)
{
using (var db = new SISPKEntities())
{
int id = db.Database.SqlQuery <int>("SELECT SEQ_" + _table + ".NEXTVAL FROM DUAL").SingleOrDefault();
return(id);
}
}
public static Decimal punya_sub(Decimal id)
{
using (var db = new SISPKEntities())
{
var USER_ACCESS_ID = Convert.ToInt32(System.Web.HttpContext.Current.Session["USER_ACCESS_ID"]);
var jml = db.Database.SqlQuery <Decimal>("SELECT CAST(COUNT(*) AS NUMBER) FROM SYS_MENU WHERE MENU_PARENT_ID=" + id + " AND MENU_ID IN(SELECT SYS_MENU.MENU_ID FROM SYS_ACCESS_DETAIL INNER JOIN SYS_ACCESS ON SYS_ACCESS_DETAIL.ACCESS_DETAIL_ACCESS_ID = SYS_ACCESS.ACCESS_ID AND SYS_ACCESS.ACCESS_STATUS = 1 INNER JOIN SYS_MENU ON SYS_ACCESS_DETAIL.ACCESS_DETAIL_MENU_ID = SYS_MENU.MENU_ID AND SYS_MENU.MENU_STATUS = 1 WHERE SYS_ACCESS.ACCESS_ID = " + USER_ACCESS_ID + " AND SYS_ACCESS_DETAIL.ACCESS_DETAIL_STATUS = 1 AND SYS_ACCESS_DETAIL.ACCESS_DETAIL_TYPE = 1 GROUP BY SYS_MENU.MENU_ID) ORDER BY MENU_SORT ASC").SingleOrDefault();
return(jml);
}
}
public static string buat_notif(int id = 1)
{
using (var db = new SISPKEntities())
{
var notif = ""; // inisialisasi awal
var BIDANG_ID = Convert.ToInt32(HttpContext.Current.Session["BIDANG_ID"]);
if (id == 1)
{
if (Convert.ToInt32(HttpContext.Current.Session["IS_KOMTEK"]) != 1)
{
//var hasil = db.Database.SqlQuery<VIEW_NOTIFIKASI>("SELECT * FROM VIEW_NOTIFIKASI WHERE JUMLAH > 0 AND NOTIF_TYPE = 1 "+((BIDANG_ID != 0) ? "AND KOMTEK_BIDANG_ID IN(" + BIDANG_ID+",0)" : "")).ToList();
var hasil = db.Database.SqlQuery <VIEW_NOTIFIKASI>("SELECT MAX(AA.NOTIF_ID) NOTIF_ID, AA.JENIS_NOTIF, SUM(AA.JUMLAH) JUMLAH, AA.NOTIF_SORT, AA.NOTIF_TYPE, AA.NOTIF_LINK, MAX(AA.KOMTEK_ID) KOMTEK_ID, MAX(AA.KOMTEK_BIDANG_ID) KOMTEK_BIDANG_ID FROM VIEW_NOTIFIKASI AA WHERE AA.JUMLAH > 0 AND NOTIF_TYPE = 1 " + ((BIDANG_ID != 0) ? "AND KOMTEK_BIDANG_ID IN(" + BIDANG_ID + ",0)" : "") + " GROUP BY AA.JENIS_NOTIF, AA.NOTIF_SORT, AA.NOTIF_TYPE, AA.NOTIF_LINK").ToList();
if (hasil != null)
{
foreach (var n in hasil)
{
notif += "<li><a href='" + n.NOTIF_LINK + "'><span style='float:right;' class='badge badge-info'>" + n.JUMLAH + " </span><span class='details'><i class='fa fa-angle-right'></i>" + n.JENIS_NOTIF + "</span></a></li>";
}
}
}
else
{
var KOMTEK_ID = Convert.ToInt32(HttpContext.Current.Session["KOMTEK_ID"]);
var hasil = db.Database.SqlQuery <VIEW_NOTIFIKASI>("SELECT * FROM VIEW_NOTIFIKASI WHERE JUMLAH > 0 AND NOTIF_TYPE = 2 AND KOMTEK_ID = " + KOMTEK_ID).ToList();
if (hasil != null)
{
foreach (var n in hasil)
{
notif += "<li><a href='" + n.NOTIF_LINK + "'><span style='float:right;' class='badge badge-info'>" + n.JUMLAH + " </span><span class='details'><i class='fa fa-angle-right'></i>" + n.JENIS_NOTIF + "</span></a></li>";
}
}
}
}
else
{
if (Convert.ToInt32(HttpContext.Current.Session["IS_KOMTEK"]) != 1)
{
var hasil = db.Database.SqlQuery <decimal>("SELECT CAST(NVL(SUM(JUMLAH),0) AS NUMBER) AS JUMLAH FROM VIEW_NOTIFIKASI AA WHERE AA.JUMLAH > 0 AND NOTIF_TYPE = 1 " + ((BIDANG_ID != 0) ? "AND KOMTEK_BIDANG_ID IN(" + BIDANG_ID + ",0)" : "") + "").SingleOrDefault();
//var hasil = db.Database.SqlQuery<decimal>("SELECT CAST(NVL(SUM(JUMLAH),0) AS NUMBER) AS JUMLAH FROM VIEW_NOTIFIKASI WHERE JUMLAH > 0 AND NOTIF_TYPE = 1 " + ((BIDANG_ID != 0) ? "AND KOMTEK_BIDANG_ID = " + BIDANG_ID : "")).SingleOrDefault();
notif = Convert.ToString(hasil);
}
else
{
var KOMTEK_ID = Convert.ToInt32(HttpContext.Current.Session["KOMTEK_ID"]);
var hasil = db.Database.SqlQuery <decimal>("SELECT CAST(NVL(SUM(JUMLAH),0) AS NUMBER) AS JUMLAH FROM VIEW_NOTIFIKASI WHERE JUMLAH > 0 AND NOTIF_TYPE = 2 AND KOMTEK_ID = " + KOMTEK_ID).SingleOrDefault();
notif = Convert.ToString(hasil);
}
}
return(notif);
}
}
public static String InsertLog(String code, String objek, int action)
{
using (var db = new SISPKEntities())
{
var GetIP = db.Database.SqlQuery <SYS_CONFIG>("SELECT * FROM SYS_CONFIG WHERE CONFIG_ID = 12").FirstOrDefault();
var GetUser = db.Database.SqlQuery <SYS_CONFIG>("SELECT * FROM SYS_CONFIG WHERE CONFIG_ID = 13").FirstOrDefault();
var GetPassword = db.Database.SqlQuery <SYS_CONFIG>("SELECT * FROM SYS_CONFIG WHERE CONFIG_ID = 14").FirstOrDefault();
using (OracleConnection con = new OracleConnection("Data Source=" + GetIP.CONFIG_VALUE + ";User ID=" + GetUser.CONFIG_VALUE + ";PASSWORD="******";"))
{
con.Open();
using (OracleCommand cmd = new OracleCommand())
{
int id = db.Database.SqlQuery <int>("SELECT SEQ_SYS_LOGS.NEXTVAL FROM DUAL").SingleOrDefault();
string UserId = HttpContext.Current.Session["USER_ID"].ToString();
string AccesId = HttpContext.Current.Session["USER_ACCESS_ID"].ToString();
var tipe = Convert.ToString(HttpContext.Current.Request.RequestContext.RouteData.Values["tipe"]);
var controller = Convert.ToString(HttpContext.Current.Request.RequestContext.RouteData.Values["controller"]);
var menu_url = ("/" + tipe + "/" + controller).ToLower();
var menu_id = db.Database.SqlQuery <Nullable <Int32> >("SELECT CAST(MENU_ID AS INT) FROM SYS_MENU WHERE LOWER(MENU_URL) = '" + menu_url + "'").FirstOrDefault();
var fname = "LOG_ID, LOG_CODE, LOG_MENU_ID, LOG_USER, LOG_USER_TYPE, LOG_ACTION, LOG_OBJECT, LOG_DATE";
var fvalue = "'" + id + "', " +
"'" + code + "', " +
"'" + menu_id + "', " +
"'" + UserId + "', " +
"'" + AccesId + "', " +
"'" + action + "', " +
":parameter, " +
"SYSDATE";
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = " INSERT INTO SYS_LOGS (" + fname + ") VALUES (" + fvalue + ") ";
OracleParameter oracleParameterClob = new OracleParameter();
oracleParameterClob.OracleDbType = OracleDbType.Clob;
//1 million long string
oracleParameterClob.Value = objek;
cmd.Parameters.Add(oracleParameterClob);
cmd.ExecuteNonQuery();
}
con.Close();
}
return("1");
}
}
public static string buat_menu(Decimal parent = 0)
{
using (var db = new SISPKEntities())
{
var menu = ""; // inisialisasi awal
var USER_ACCESS_ID = Convert.ToInt32(System.Web.HttpContext.Current.Session["USER_ACCESS_ID"]);
var hasil = db.Database.SqlQuery <SYS_MENU>("SELECT * FROM SYS_MENU WHERE MENU_PARENT_ID=" + parent + " AND MENU_ID IN(SELECT SYS_MENU.MENU_ID FROM SYS_ACCESS_DETAIL INNER JOIN SYS_ACCESS ON SYS_ACCESS_DETAIL.ACCESS_DETAIL_ACCESS_ID = SYS_ACCESS.ACCESS_ID AND SYS_ACCESS.ACCESS_STATUS = 1 INNER JOIN SYS_MENU ON SYS_ACCESS_DETAIL.ACCESS_DETAIL_MENU_ID = SYS_MENU.MENU_ID AND SYS_MENU.MENU_STATUS = 1 WHERE SYS_ACCESS.ACCESS_ID = " + USER_ACCESS_ID + " AND SYS_ACCESS_DETAIL.ACCESS_DETAIL_STATUS = 1 GROUP BY SYS_MENU.MENU_ID) ORDER BY MENU_SORT ASC").ToList();
foreach (var res in hasil)
{
if (res.MENU_PARENT_ID == parent)
{
var host = System.Web.HttpContext.Current.Request.Url.AbsolutePath;
var NewHost = res.MENU_URL;
if (host == NewHost)
{
if (punya_sub(res.MENU_ID) == 0)
{
menu += "<li class='start active open'><a href='" + res.MENU_URL + "'><i class='" + res.MENU_ICON + "'></i><span class='title'> " + res.MENU_NAME + "</span><span class='selected'></span>";
}
else
{
menu += "<li class='start active open'><a href='" + res.MENU_URL + "'><i class='" + res.MENU_ICON + "'></i><span class='title'> " + res.MENU_NAME + "</span><span class='selected'></span><span class='arrow open'></span>";
}
}
else
{
menu += "<li class=''><a href='" + res.MENU_URL + "'><i class='" + res.MENU_ICON + "'></i> <span class='title'> " + res.MENU_NAME + "</span>";
}
if (punya_sub(res.MENU_ID) > 0)
{
menu += "<span class='arrow'></span>";
}
menu += "</a>";
if (punya_sub(res.MENU_ID) > 0)
{
menu += "<ul class='sub-menu'>";
menu += buat_menu(res.MENU_ID);
menu += "</ul>";
}
menu += "</li>";
}
}
menu += "";
return(menu);
}
}
public static String InsertLogActivate(String code, int userid, int accessid, String objek, int action)
{
using (var db = new SISPKEntities())
{
int id = db.Database.SqlQuery <int>("SELECT SEQ_SYS_LOGS.NEXTVAL FROM DUAL").SingleOrDefault();
var fname = "LOG_ID, LOG_CODE, LOG_USER, LOG_USER_TYPE, LOG_ACTION, LOG_OBJECT, LOG_DATE";
var fvalue = "'" + id + "', " +
"'" + code + "', " +
"'" + userid + "', " +
"'" + accessid + "', " +
"'" + action + "', " +
"'" + objek + "', " +
"SYSDATE";
db.Database.ExecuteSqlCommand("INSERT INTO SYS_LOGS (" + fname + ") VALUES (" + fvalue + ")");
return("2");
}
}
public static string GetListICS(string ics = "")
{
string Output = "";
using (var db = new SISPKEntities())
{
string[] newIcs = ics.Split(',');
if (newIcs.Count() > 0)
{
foreach (string i in newIcs)
{
var newData = db.Database.SqlQuery <MASTER_ICS>("SELECT * FROM MASTER_ICS WHERE ICS_ID = '" + i + "'").SingleOrDefault();
Output += "<center>" + newData.ICS_CODE + "</center><br />";
}
}
}
return(Output);
}
public static String InsertLog(String code, String objek, int action)
{
using (var db = new SISPKEntities())
{
int id = db.Database.SqlQuery <int>("SELECT SEQ_SYS_LOGS.NEXTVAL FROM DUAL").SingleOrDefault();
string UserId = HttpContext.Current.Session["USER_ID"].ToString();
string AccesId = HttpContext.Current.Session["USER_ACCESS_ID"].ToString();
var fname = "LOG_ID, LOG_CODE, LOG_USER, LOG_USER_TYPE, LOG_ACTION, LOG_OBJECT, LOG_DATE";
var fvalue = "'" + id + "', " +
"'" + code + "', " +
"'" + UserId + "', " +
"'" + AccesId + "', " +
"'" + action + "', " +
"'" + objek + "', " +
"SYSDATE";
db.Database.ExecuteSqlCommand("INSERT INTO SYS_LOGS (" + fname + ") VALUES (" + fvalue + ")");
return("1");
}
}
public static String GetLogCode()
{
using (var db = new SISPKEntities())
{
string LogId = "";
string InitialCode = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString().PadLeft(2, '0') + DateTime.Now.Day.ToString().PadLeft(2, '0');
int NextNumber = 1;
var GetLast = db.Database.SqlQuery <String>("SELECT MAX(LOG_CODE) AS CODE FROM SYS_LOGS WHERE LOG_CODE LIKE '" + InitialCode + "%'").SingleOrDefault();
if (GetLast != null)
{
LogId = GetLast.Replace(InitialCode, "");
int.TryParse(LogId, out NextNumber);
NextNumber = NextNumber + 1;
LogId = "";
}
LogId = InitialCode + NextNumber.ToString().PadLeft(8, '0');
return(LogId);
}
}
public static string GetInfoSNIRegtek(int regtek_id = 0)
{
string Output = "";
using (var db = new SISPKEntities())
{
var datasni = db.Database.SqlQuery <VIEW_SNI>("SELECT T2.* FROM TRX_REGULASI_TEKNIS_DETAIL T1 INNER JOIN VIEW_SNI T2 ON T1.RETEK_DETAIL_SNI_ID = T2.SNI_ID WHERE T1.RETEK_DETAIL_RETEK_ID = '" + regtek_id + "'").ToList();
if (datasni != null)
{
Output += "<ol style='padding-left:15px;'>";
foreach (var i in datasni)
{
Output += "<li><a href='/SNI/DetailSNI/" + i.SNI_ID + "'>" + i.SNI_NOMOR + "</a> " + i.SNI_JUDUL + "</li>";
}
Output += "</ol>";
}
}
return(Output);
}
void IActionFilter.OnActionExecuting(ActionExecutingContext filterContext)
{
base.OnActionExecuting(filterContext);
SISPKEntities db = new SISPKEntities();
var USER_ACCESS_ID = Convert.ToInt32(HttpContext.Current.Session["USER_ACCESS_ID"]);
var USER_ID = Convert.ToInt32(HttpContext.Current.Session["USER_ID"]);
if (USER_ID == 0)
{
filterContext.Result = new RedirectToRouteResult(
new RouteValueDictionary
{
{ "tipe", "Apps" },
{ "controller", "Auth" },
{ "action", "Logout" }
});
}
var IsRead = 0;
var IsCreate = 0;
var IsUpdate = 0;
var IsDelete = 0;
var IsApprove = 0;
var IsPrint = 0;
var tipe = Convert.ToString(HttpContext.Current.Request.RequestContext.RouteData.Values["tipe"]);
var controller = Convert.ToString(HttpContext.Current.Request.RequestContext.RouteData.Values["controller"]);
var menu_url = ("/" + tipe + "/" + controller).ToLower();
var host = System.Web.HttpContext.Current.Request.Url.AbsolutePath;
var menu_id = db.Database.SqlQuery <Nullable <Int32> >("SELECT CAST(MENU_ID AS INT) FROM SYS_MENU WHERE LOWER(MENU_URL) = '" + menu_url + "'").FirstOrDefault();
filterContext.Controller.ViewBag.menu_id = menu_id;
//return Json(new { Hasil = Query }, JsonRequestBehavior.AllowGet);
var back_url = Convert.ToString(System.Web.HttpContext.Current.Request.ServerVariables["HTTP_REFERER"]);
filterContext.Controller.ViewBag.menu_url = menu_url;
var ThisAksesTrue = db.Database.SqlQuery <Nullable <Int32> >("SELECT CAST(COUNT(*) AS INT) FROM SYS_ACCESS_DETAIL T1 INNER JOIN SYS_MENU T2 ON T1.ACCESS_DETAIL_MENU_ID = T2.MENU_ID WHERE T2.MENU_ID = " + menu_id + " AND T1.ACCESS_DETAIL_ACCESS_ID = " + USER_ACCESS_ID + " AND T1.ACCESS_DETAIL_STATUS = 1 AND T1.ACCESS_DETAIL_TYPE = " + RoleTipe).FirstOrDefault();
if (ThisAksesTrue == 0)
{
//filterContext.Result = new RedirectResult(back_url);
//filterContext.Controller.ViewBag.back_url = back_url;
filterContext.Result = new RedirectToRouteResult(
new RouteValueDictionary
{
{ "tipe", "Apps" },
{ "controller", "Error" },
{ "returnurl", back_url }
});
}
var Access = db.Database.SqlQuery <Nullable <Int32> >("SELECT CAST(T1.ACCESS_DETAIL_TYPE AS INT) FROM SYS_ACCESS_DETAIL T1 INNER JOIN SYS_MENU T2 ON T1.ACCESS_DETAIL_MENU_ID = T2.MENU_ID WHERE T2.MENU_ID = " + menu_id + " AND T1.ACCESS_DETAIL_ACCESS_ID = " + USER_ACCESS_ID + " AND T1.ACCESS_DETAIL_STATUS = 1 ORDER BY T1.ACCESS_DETAIL_TYPE ASC").ToList();
foreach (var i in Access)
{
if (i == 1)
{
IsRead = 1;
}
else if (i == 2)
{
IsCreate = 1;
}
else if (i == 3)
{
IsUpdate = 1;
}
else if (i == 4)
{
IsDelete = 1;
}
else if (i == 5)
{
IsApprove = 1;
}
else if (i == 6)
{
IsPrint = 1;
}
}
filterContext.Controller.ViewBag.IsRead = IsRead;
filterContext.Controller.ViewBag.IsCreate = IsCreate;
filterContext.Controller.ViewBag.IsUpdate = IsUpdate;
filterContext.Controller.ViewBag.IsDelete = IsDelete;
filterContext.Controller.ViewBag.IsApprove = IsApprove;
filterContext.Controller.ViewBag.IsPrint = IsPrint;
filterContext.Controller.ViewBag.USER_ACCESS_ID = (USER_ACCESS_ID == 0 ? "xx" : Convert.ToString(USER_ACCESS_ID));
}
public static string buat_breadcrumb(int parent = 0)
{
using (var db = new SISPKEntities())
{
var menu = ""; // inisialisasi awal
//var host = System.Web.HttpContext.Current.Request.Url.AbsolutePath;
var tipe = HttpContext.Current.Request.RequestContext.RouteData.Values["tipe"];
var controller = HttpContext.Current.Request.RequestContext.RouteData.Values["controller"];
var action = Convert.ToString(HttpContext.Current.Request.RequestContext.RouteData.Values["action"]).ToLower();
var host = "/" + tipe + "/" + controller;
var hasil = db.Database.SqlQuery <SYS_MENU>("select * from ( select * from SYS_MENU WHERE MENU_URL LIKE '" + host + "%' order by MENU_PARENT_ID ASC ) where ROWNUM = 1").SingleOrDefault();
if (hasil != null)
{
var hasil2 = db.Database.SqlQuery <SYS_MENU>("select * from ( select * from SYS_MENU WHERE MENU_ID = " + hasil.MENU_PARENT_ID + " order by MENU_PARENT_ID ASC ) where ROWNUM = 1").SingleOrDefault();
var newaction = "";
if (action == "index" || action == "baru")
{
newaction = "Daftar";
}
else if (action == "create")
{
newaction = "Tambah Data";
}
else if (action == "detail")
{
newaction = "Lihat Data";
}
else if (action == "update")
{
newaction = "Ubah Data";
}
else if (action == "assignkomtek")
{
newaction = "Persetujuan";
}
else if (action == "comment")
{
newaction = "Komentar ";
}
else if (action == "setting")
{
newaction = "Setting ";
}
else if (action == "approval" || action == "approvalusulan")
{
newaction = "Persetujuan";
}
else if (action == "pengesahan")
{
newaction = "Pengesahan";
}
else
{
newaction = action;
}
if (hasil2 != null)
{
menu += "<li>";
menu += "<i class='" + hasil2.MENU_ICON + "'></i> ";
menu += "<a href='" + hasil2.MENU_URL + "'>" + hasil2.MENU_NAME + "</a>";
menu += "<i class='fa fa-angle-right'></i>";
menu += "</li>";
}
menu += "<li>";
menu += "<a href='" + hasil.MENU_URL + "'>" + hasil.MENU_NAME + "</a>";
menu += "<i class='fa fa-angle-right'></i>";
menu += "</li>";
menu += "<li>";
menu += "<a href='" + hasil.MENU_URL + "'>" + newaction + " " + hasil.MENU_NAME + "</a>";
menu += "</li>";
}
return(menu);
}
}
