public List <STPERMISSIONSRes> GroupUserActive(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "Select G.GUCODE , G.GUNAME , P.COMPANY , max(P.INPDT) as INPDT ";
sql += " ,(max(P.INPID) + ' : ' + (select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= max(P.INPID)) ) as INPID";
sql += " from STPERMISSIONS as P ";
sql += " right outer join STMENU as M on M.MENUCODE = P.MENUCODE";
sql += " right outer join [FT_STGROUPUSER]() as G on G.GUCODE = P.GUCODE and G.COMPANY = P.COMPANY";
sql += " Where M.FLAG = '1' ";
if (!String.IsNullOrEmpty(d.GUCODE))
{
sql += " and G.GUCODE = @GUCODE ";
param.Add("@GUCODE", d.GUCODE);
}
if (!String.IsNullOrEmpty(d.COMPANY))
{
sql += " and P.COMPANY = @COMPANY ";
param.Add("@COMPANY", d.COMPANY);
}
sql += "group by G.GUCODE , G.GUNAME , P.COMPANY";
var res = Query <STPERMISSIONSRes>(sql, param, conStr).ToList();
return(res);
}
public List <STPERMISSIONS> Get(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "Select * from STPERMISSIONS";
sql += " Where 1 = 1";
if (!String.IsNullOrEmpty(d.MENUCODE))
{
sql += " and MENUCODE = @MENUCODE ";
param.Add("@MENUCODE", d.MENUCODE);
}
if (!String.IsNullOrEmpty(d.GUCODE))
{
sql += " and GUCODE = @GUCODE ";
param.Add("@GUCODE", d.GUCODE);
}
if (!String.IsNullOrEmpty(d.COMPANY))
{
sql += " and COMPANY = @COMPANY ";
param.Add("@COMPANY", d.COMPANY);
}
if (!String.IsNullOrEmpty(d.P_ACCESS))
{
sql += " and P_ACCESS = @P_ACCESS ";
param.Add("@P_ACCESS", d.P_ACCESS);
}
if (!String.IsNullOrEmpty(d.P_MANAGE))
{
sql += " and P_MANAGE = @P_MANAGE ";
param.Add("@P_MANAGE", d.P_MANAGE);
}
if (!String.IsNullOrEmpty(d.P_DELETE))
{
sql += " and P_DELETE = @P_DELETE ";
param.Add("@P_DELETE", d.P_DELETE);
}
if (!String.IsNullOrEmpty(d.P_APPROVE))
{
sql += " and P_APPROVE = @P_APPROVE ";
param.Add("@P_APPROVE", d.P_APPROVE);
}
if (!String.IsNullOrEmpty(d.P_EXPORT))
{
sql += " and P_EXPORT = @P_EXPORT ";
param.Add("@P_EXPORT", d.P_EXPORT);
}
var res = Query <STPERMISSIONS>(sql, param, conStr).ToList();
return(res);
}
private PERMISSIONSRes validPermission(PERMISSIONSReq dataReq, string conStr = null)
{
var res = new PERMISSIONSRes();
try
{
var userReq = new ASSETKKF_MODEL.Data.Mssql.Asset.STUSERASSET()
{
UCODE = dataReq.INPID,
COMPANY = dataReq.COMPANY,
};
var userLst = ASSETKKF_ADO.Mssql.Asset.STUSERASSETAdo.GetInstant().Search(userReq, null, conStr);
var objUser = userLst != null?userLst.FirstOrDefault() : null;
var gucode = objUser != null ? objUser.GUCODE : null;
var req = new STPERMISSIONS()
{
MENUCODE = dataReq.MENUCODE,
GUCODE = gucode,
COMPANY = dataReq.COMPANY,
P_ACCESS = dataReq.P_ACCESS,
P_MANAGE = dataReq.P_MANAGE,
P_DELETE = dataReq.P_DELETE,
P_APPROVE = dataReq.P_APPROVE,
P_EXPORT = dataReq.P_EXPORT,
INPID = dataReq.INPID,
};
var lst = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().Valid(req, null, conStr);
res.PERMISSIONSLST = lst;
if (lst != null && lst.Count > 0)
{
res.hasPermission = true;
res._result._code = "200";
res._result._message = "";
res._result._status = "OK";
}
else
{
res.hasPermission = false;
res._result._code = "404";
res._result._message = "ไม่พบข้อมูล";
res._result._status = "Not Found";
}
}
catch (Exception ex)
{
res.hasPermission = false;
res._result._code = "500 ";
res._result._message = ex.Message;
res._result._status = "Internal Server Error";
}
return(res);
}
private PERMISSIONSRes deleteByGroup(PERMISSIONSReq dataReq, string conStr = null)
{
var res = new PERMISSIONSRes();
try
{
var lst = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().Get(new STPERMISSIONS()
{
MENUCODE = dataReq.MENUCODE, GUCODE = dataReq.GUCODE, COMPANY = dataReq.COMPANY
}, null, conStr);
if (lst != null && lst.Count > 0)
{
var req = new STPERMISSIONS()
{
MENUCODE = dataReq.MENUCODE,
GUCODE = dataReq.GUCODE,
COMPANY = dataReq.COMPANY,
P_ACCESS = dataReq.P_ACCESS,
P_MANAGE = dataReq.P_MANAGE,
P_DELETE = dataReq.P_DELETE,
P_APPROVE = dataReq.P_APPROVE,
P_EXPORT = dataReq.P_EXPORT,
INPID = dataReq.INPID,
};
var state = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().DeleteAllByGroup(req, null, conStr);
res._result._code = "200";
res._result._message = "ลบข้อมูลเรียบร้อยแล้ว";
res._result._status = "OK";
}
else
{
res._result._code = "404";
res._result._message = "ไม่พบข้อมูล";
res._result._status = "Not Found";
}
}
catch (Exception ex)
{
res._result._code = "500 ";
res._result._message = ex.Message;
res._result._status = "Internal Server Error";
}
finally
{
var newList = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().ListActive(new STPERMISSIONS()
{
COMPANY = dataReq.COMPANY
}, null, conStr);
res.PERMISSIONSLST = newList;
}
return(res);
}
private PERMISSIONSRes insert(PERMISSIONSReq dataReq, string conStr = null)
{
var res = new PERMISSIONSRes();
try
{
var lst = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().Get(new STPERMISSIONS()
{
MENUCODE = dataReq.MENUCODE, GUCODE = dataReq.GUCODE, COMPANY = dataReq.COMPANY
}, null, conStr);
if (lst == null || (lst != null && lst.Count == 0))
{
var req = new STPERMISSIONS()
{
MENUCODE = dataReq.MENUCODE,
GUCODE = dataReq.GUCODE,
COMPANY = dataReq.COMPANY,
P_ACCESS = dataReq.P_ACCESS,
P_MANAGE = dataReq.P_MANAGE,
P_DELETE = dataReq.P_DELETE,
P_APPROVE = dataReq.P_APPROVE,
P_EXPORT = dataReq.P_EXPORT,
INPID = dataReq.INPID,
};
var state = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().Insert(req, null, conStr);
res._result._code = "200";
res._result._message = "บันทึกข้อมูลเรียบร้อยแล้ว";
res._result._status = "OK";
}
else
{
throw new Exception("กำหนดสิทธิ์การใช้งานซ้ำ");
}
}
catch (Exception ex)
{
res._result._code = "500 ";
res._result._message = ex.Message;
res._result._status = "Internal Server Error";
}
finally
{
var newList = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().ListActive(new STPERMISSIONS()
{
COMPANY = dataReq.COMPANY
}, null, conStr);
res.PERMISSIONSLST = newList;
}
return(res);
}
public int DeleteAllByGroup(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
param.Add("@GUCODE", d.GUCODE);
param.Add("@COMPANY", d.COMPANY);
string cmd = "Delete From STPERMISSIONS " +
" Where GUCODE = @GUCODE and COMPANY =@COMPANY ;";
var res = ExecuteNonQuery(transac, cmd, param, conStr);
return(res);
}
public int Update(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
param.Add("@MENUCODE", d.MENUCODE);
param.Add("@GUCODE", d.GUCODE);
param.Add("@COMPANY", d.COMPANY);
param.Add("@INPID", d.INPID);
string cmd = "Update STPERMISSIONS SET " +
" INPID = @INPID, INPDT = GETDATE()";
if (!String.IsNullOrEmpty(d.P_ACCESS))
{
cmd += " , P_ACCESS = @P_ACCESS ";
param.Add("@P_ACCESS", d.P_ACCESS);
}
if (!String.IsNullOrEmpty(d.P_MANAGE))
{
cmd += " , P_MANAGE = @P_MANAGE ";
param.Add("@P_MANAGE", d.P_MANAGE);
}
if (!String.IsNullOrEmpty(d.P_DELETE))
{
cmd += " , P_DELETE = @P_DELETE ";
param.Add("@P_DELETE", d.P_DELETE);
}
if (!String.IsNullOrEmpty(d.P_APPROVE))
{
cmd += " ,P_APPROVE = @P_APPROVE ";
param.Add("@P_APPROVE", d.P_APPROVE);
}
if (!String.IsNullOrEmpty(d.P_EXPORT))
{
cmd += " , P_EXPORT = @P_EXPORT ";
param.Add("@P_EXPORT", d.P_EXPORT);
}
cmd += " Where MENUCODE = @MENUCODE and GUCODE = @GUCODE and COMPANY =@COMPANY ;";
var res = ExecuteNonQuery(transac, cmd, param, conStr);
return(res);
}
private PERMISSIONSRes getGroupUser(PERMISSIONSReq dataReq, string conStr = null)
{
var res = new PERMISSIONSRes();
try
{
var req = new STPERMISSIONS()
{
MENUCODE = dataReq.MENUCODE,
GUCODE = dataReq.GUCODE,
COMPANY = dataReq.COMPANY,
P_ACCESS = dataReq.P_ACCESS,
P_MANAGE = dataReq.P_MANAGE,
P_DELETE = dataReq.P_DELETE,
P_APPROVE = dataReq.P_APPROVE,
P_EXPORT = dataReq.P_EXPORT,
INPID = dataReq.INPID,
};
var lst = ASSETKKF_ADO.Mssql.Asset.STPERMISSIONSAdo.GetInstant().getGROUPUSER(new STPERMISSIONS()
{
COMPANY = dataReq.COMPANY
}, null, conStr);
res.GROUPUSERLST = lst;
if (lst != null && lst.Count > 0)
{
res._result._code = "200";
res._result._message = "";
res._result._status = "OK";
}
else
{
res._result._code = "404";
res._result._message = "ไม่พบข้อมูล";
res._result._status = "Not Found";
}
}
catch (Exception ex)
{
res._result._code = "500 ";
res._result._message = ex.Message;
res._result._status = "Internal Server Error";
}
return(res);
}
public string getGUCODE(STPERMISSIONS d)
{
string GUCODE = null;
var user = ASSETKKF_ADO.Mssql.Asset.STUSERASSETAdo.GetInstant().Search(new ASSETKKF_MODEL.Data.Mssql.Asset.STUSERASSET()
{
UCODE = d.INPID.Trim()
});
if (user != null)
{
var obj = user.FirstOrDefault();
GUCODE = obj.GUCODE;
}
return(GUCODE);
}
public List <STGROUPUSER> getGROUPUSER(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "select gucode,guname from [FT_STGROUPUSER]() where 1 =1 ";
if (!String.IsNullOrEmpty(d.COMPANY))
{
sql += " and COMPANY = @COMPANY ";
param.Add("@COMPANY", d.COMPANY);
}
sql += " group by gucode,guname";
var res = Query <STGROUPUSER>(sql, param, conStr).ToList();
return(res);
}
public int Insert(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
param.Add("@MENUCODE", d.MENUCODE);
param.Add("@GUCODE", d.GUCODE);
param.Add("@COMPANY", d.COMPANY);
param.Add("@P_ACCESS", d.P_ACCESS);
param.Add("@P_MANAGE", d.P_MANAGE);
param.Add("@P_DELETE", d.P_DELETE);
param.Add("@P_APPROVE", d.P_APPROVE);
param.Add("@P_EXPORT", d.P_APPROVE);
param.Add("@INPID", d.INPID);
string cmd = "INSERT INTO STPERMISSIONS (MENUCODE, GUCODE, COMPANY, P_ACCESS, P_MANAGE, P_DELETE, P_APPROVE,P_EXPORT, INPID, INPDT) " +
"VALUES (@MENUCODE, @GUCODE, @COMPANY, @P_ACCESS, @P_MANAGE, @P_DELETE, @P_APPROVE, @P_EXPORT, @INPID, GETDATE());";
var res = ExecuteNonQuery(transac, cmd, param, conStr);
return(res);
}
public List <STMENU> ListMenu(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "Select * from STMENU Where FLAG = '1' ";
sql += " and MENUCODE not in (select MENUCODE from STPERMISSIONS where 1 =1";
if (!String.IsNullOrEmpty(d.COMPANY))
{
sql += " and COMPANY = @COMPANY ";
param.Add("@COMPANY", d.COMPANY);
}
if (!String.IsNullOrEmpty(d.GUCODE))
{
sql += " and GUCODE = @GUCODE ";
param.Add("@GUCODE", d.GUCODE);
}
sql += " )";
var res = Query <STMENU>(sql, param, conStr).ToList();
return(res);
}
public List <STPERMISSIONSRes> GroupPermissions(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "Select M.MENUCODE , M.MENUNAME , P.COMPANY, max(P.INPDT) as INPDT,max(P.gucode) as gucode,max(G.guname) as guname";
sql += " ,P.P_ACCESS ,P.P_MANAGE , P.P_DELETE , P.P_APPROVE, P_EXPORT";
sql += " ,(max(P.INPID) + ' : ' + (select NAMEMPT from [CENTRALDB].[centraldb].[dbo].[vTEMPLOY] where [CODEMPID]= max(P.INPID)) ) as INPID";
sql += " from STPERMISSIONS as P ";
sql += " right outer join STMENU as M on M.MENUCODE = P.MENUCODE";
sql += " right outer join [FT_STGROUPUSER]() as G on G.GUCODE = P.GUCODE and G.COMPANY = P.COMPANY";
sql += " Where M.FLAG = '1' ";
if (!String.IsNullOrEmpty(d.MENUCODE))
{
sql += " and M.MENUCODE = @MENUCODE ";
param.Add("@MENUCODE", d.MENUCODE);
}
if (!String.IsNullOrEmpty(d.GUCODE))
{
sql += " and G.GUCODE = @GUCODE ";
param.Add("@GUCODE", d.GUCODE);
}
if (!String.IsNullOrEmpty(d.COMPANY))
{
sql += " and P.COMPANY = @COMPANY ";
param.Add("@COMPANY", d.COMPANY);
}
if (!String.IsNullOrEmpty(d.P_ACCESS))
{
sql += " and P_ACCESS = @P_ACCESS ";
param.Add("@P_ACCESS", d.P_ACCESS);
}
if (!String.IsNullOrEmpty(d.P_MANAGE))
{
sql += " and P_MANAGE = @P_MANAGE ";
param.Add("@P_MANAGE", d.P_MANAGE);
}
if (!String.IsNullOrEmpty(d.P_DELETE))
{
sql += " and P_DELETE = @P_DELETE ";
param.Add("@P_DELETE", d.P_DELETE);
}
if (!String.IsNullOrEmpty(d.P_APPROVE))
{
sql += " and P_APPROVE = @P_APPROVE ";
param.Add("@P_APPROVE", d.P_APPROVE);
}
if (!String.IsNullOrEmpty(d.P_EXPORT))
{
sql += " and P_EXPORT = @P_EXPORT ";
param.Add("@P_EXPORT", d.P_EXPORT);
}
sql += "group by M.MENUCODE , M.MENUNAME , P.COMPANY ,P.P_ACCESS ,P.P_MANAGE , P.P_DELETE , P.P_APPROVE, P_EXPORT ";
var res = Query <STPERMISSIONSRes>(sql, param, conStr).ToList();
return(res);
}
public List <STPERMISSIONSRes> ListActive(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "Select G.GUCODE , G.GUNAME , M.MENUCODE , M.MENUNAME , P.COMPANY";
sql += " ,P.P_ACCESS ,P.P_MANAGE , P.P_DELETE , P.P_APPROVE, P_EXPORT";
sql += " from STPERMISSIONS as P ";
sql += " right outer join STMENU as M on M.MENUCODE = P.MENUCODE";
sql += " right outer join [FT_STGROUPUSER]() as G on G.GUCODE = P.GUCODE and G.COMPANY = P.COMPANY";
sql += " Where M.FLAG = '1' ";
if (!String.IsNullOrEmpty(d.MENUCODE))
{
sql += " and M.MENUCODE = @MENUCODE ";
param.Add("@MENUCODE", d.MENUCODE);
}
if (!String.IsNullOrEmpty(d.GUCODE))
{
sql += " and G.GUCODE = @GUCODE ";
param.Add("@GUCODE", d.GUCODE);
}
if (!String.IsNullOrEmpty(d.COMPANY))
{
sql += " and P.COMPANY = @COMPANY ";
param.Add("@COMPANY", d.COMPANY);
}
if (!String.IsNullOrEmpty(d.P_ACCESS))
{
sql += " and P_ACCESS = @P_ACCESS ";
param.Add("@P_ACCESS", d.P_ACCESS);
}
if (!String.IsNullOrEmpty(d.P_MANAGE))
{
sql += " and P_MANAGE = @P_MANAGE ";
param.Add("@P_MANAGE", d.P_MANAGE);
}
if (!String.IsNullOrEmpty(d.P_DELETE))
{
sql += " and P_DELETE = @P_DELETE ";
param.Add("@P_DELETE", d.P_DELETE);
}
if (!String.IsNullOrEmpty(d.P_APPROVE))
{
sql += " and P_APPROVE = @P_APPROVE ";
param.Add("@P_APPROVE", d.P_APPROVE);
}
if (!String.IsNullOrEmpty(d.P_EXPORT))
{
sql += " and P_EXPORT = @P_EXPORT ";
param.Add("@P_EXPORT", d.P_EXPORT);
}
var res = Query <STPERMISSIONSRes>(sql, param, conStr).ToList();
return(res);
}
public List <STPERMISSIONSRes> Valid(STPERMISSIONS d, SqlTransaction transac = null, string conStr = null)
{
DynamicParameters param = new DynamicParameters();
sql = "Select * from STPERMISSIONS as P ";
sql += " left outer join STMENU as M on M.MENUCODE = P.MENUCODE";
sql += " Where 1 = 1";
if (!String.IsNullOrEmpty(d.MENUCODE))
{
sql += " and P.MENUCODE = @MENUCODE ";
param.Add("@MENUCODE", d.MENUCODE);
}
if (!String.IsNullOrEmpty(d.GUCODE))
{
sql += " and GUCODE = @GUCODE ";
param.Add("@GUCODE", d.GUCODE);
}
if (!String.IsNullOrEmpty(d.COMPANY))
{
var comp = "";
comp = "'" + d.COMPANY.Replace(",", "','") + "'";
sql += " and P.COMPANY in (" + comp + ") ";
//sql += " and COMPANY = @COMPANY ";
//param.Add("@COMPANY", d.COMPANY);
}
if (!String.IsNullOrEmpty(d.P_ACCESS))
{
sql += " and P_ACCESS = @P_ACCESS ";
param.Add("@P_ACCESS", d.P_ACCESS);
}
if (!String.IsNullOrEmpty(d.P_MANAGE))
{
sql += " and P_MANAGE = @P_MANAGE ";
param.Add("@P_MANAGE", d.P_MANAGE);
}
if (!String.IsNullOrEmpty(d.P_DELETE))
{
sql += " and P_DELETE = @P_DELETE ";
param.Add("@P_DELETE", d.P_DELETE);
}
if (!String.IsNullOrEmpty(d.P_APPROVE))
{
sql += " and P_APPROVE = @P_APPROVE ";
param.Add("@P_APPROVE", d.P_APPROVE);
}
if (!String.IsNullOrEmpty(d.P_EXPORT))
{
sql += " and P_EXPORT = @P_EXPORT ";
param.Add("@P_EXPORT", d.P_EXPORT);
}
var res = Query <STPERMISSIONSRes>(sql, param, conStr).ToList();
return(res);
}