public static List <tbNhomChucnangModel> getnhomchucnang(string username)
{
dbOAMSEntities db = new dbOAMSEntities();
var i = db.Database.SqlQuery <tbNhomChucnangModel>("select * from [adm].[tbNhomchucnang] order by THUTU ").ToList();
return(i);
}
public IHttpActionResult getalldatafilter(UserAParModel par)
{
try
{
dbOAMSEntities db = new dbOAMSEntities();
string tablename = par.valstring1.checkIsNull();
tablename = CryptData.querydecrypt(tablename);
var rfun = db.Database.SqlQuery <tbFunction>("select f.* from bnle.tbFunction f where f.MAFUNC = @MAFUNC and KICHTHOAT = 1"
, new SqlParameter("@MAFUNC", tablename)).FirstOrDefault();
if (rfun != null)
{
if (rfun.TABLEPARENT.checkIsNull() != "")
{
DataGridFModel result = new DataGridFModel();
string sqlPARENT = string.Format("select {0} as [CODE],{1} as [VALUENAME] from {2}",
rfun.PARENTKEY, rfun.PARENTNAME, rfun.TABLEPARENT);
var dirs = db.Database.SqlQuery <DataDirControl>(sqlPARENT).ToList();
result.datafilter = dirs;
result.tablename = rfun.PARENTNAME;
result.formname = rfun.TENFUNC;
result.viewmode = rfun.VIEWMODE;
result.widthfilter = string.Format("col-xs-{0}", rfun.PARENTWITHD);
result.widthgrid = string.Format("col-xs-{0}", 12 - Convert.ToInt32(rfun.PARENTWITHD));
return(Ok(result));
}
}
return(Ok(new List <DataDirControl>()));
}
catch
{
return(Ok(new List <DataDirControl>()));
}
}
public IHttpActionResult deletedata(DataParnew par)
{
try
{
dbOAMSEntities db = new dbOAMSEntities();
string tablename = par.Tablename;
tablename = CryptData.querydecrypt(tablename);
string ID = par.id.checkIsNull();
var rfun = db.Database.SqlQuery <tbFunction>("select f.* from bnle.tbFunction f where f.MAFUNC = @MAFUNC and KICHTHOAT = 1"
, new SqlParameter("@MAFUNC", tablename)).FirstOrDefault();
if (rfun != null)
{
if (ID != "")
{
StringBuilder sqlcom = new StringBuilder("");
sqlcom.Append("delete " + rfun.TABLENAME + " where " + rfun.IDKEY + " = @" + rfun.IDKEY);
var vrs = db.Database.SqlQuery <tbFORMFUNC>("select f.* from bnle.tbFORMFUNC f where f.MAFUNC = @MAFUNC and MATRUONG = @MATRUONG "
, new SqlParameter("@MAFUNC", tablename), new SqlParameter("@MATRUONG", rfun.IDKEY)).ToList();
string keytype = "";
for (int i = 0; i < vrs.Count; i++)
{
if (rfun.IDKEY == vrs[i].MATRUONG)
{
keytype = vrs[i].KIEUDULIEU; break;
}
}
SqlParameter parsql;
switch (keytype)
{
case "text": parsql = new SqlParameter("@" + rfun.IDKEY, ID); break;
case "number": parsql = new SqlParameter("@" + rfun.IDKEY, Convert.ToInt32(ID)); break;
default: parsql = new SqlParameter("@" + rfun.IDKEY, ID); break;
}
db.Database.ExecuteSqlCommand(sqlcom.ToString(), parsql);
}
}
return(Ok(1));
}
catch
{
return(BadRequest());
}
}
public static List <tbChucnangModel> getchucnang(string username)
{
dbOAMSEntities db = new dbOAMSEntities();
if (username == "Administrator")
{
var i = db.Database.SqlQuery <tbChucnangModel>("select * from [adm].[tbChucnang] ").ToList();
return(i);
}
else
{
var i = db.Database.SqlQuery <tbChucnangModel>("select distinct c.* from [adm].[tbChucnang] c join [users].[tbNhom_Chucnang] g on g.CHUCNANGID = c.ID and g.ALLACTION = 1 " +
" join [users].[tbNhom_Users] n on g.MANHOM = n.MANHOM join [users].[tbNguoidung] u on u.USERNAME = n.USERNAME " +
" where u.USERNAME = @username",
new System.Data.SqlClient.SqlParameter("@username", username)).ToList();
return(i);
}
}
public async Task <IHttpActionResult> Register(RegisterBindingModel model)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
var user = new FSMIdentityUser()
{
UserName = model.Username, Email = model.Email
};
IdentityResult result = await UserManager.CreateAsync(user, model.Password);
if (!result.Succeeded)
{
return(GetErrorResult(result));
}
try
{
dbOAMSEntities db = new dbOAMSEntities();
if (model.CHUCVU.checkIsNull() == "")
{
db.Database.ExecuteSqlCommand("insert into [users].[tbNguoidung](ID,USERNAME,HOLOT,TEN,BOPHAN,NGAYTAO,Email,HANCHE,KHOA) values(" +
" @ID, @USERNAME, @HOLOT, @TEN, @BOPHAN, @NGAYTAO, @Email, @HANCHE, @KHOA)",
new System.Data.SqlClient.SqlParameter("@ID", user.Id),
new System.Data.SqlClient.SqlParameter("@USERNAME", model.Username),
new System.Data.SqlClient.SqlParameter("@HOLOT", model.HOLOT),
new System.Data.SqlClient.SqlParameter("@TEN", model.TEN),
new System.Data.SqlClient.SqlParameter("@BOPHAN", model.BOPHAN.checkIsNull()),
new System.Data.SqlClient.SqlParameter("@NGAYTAO", DateTime.Now),
new System.Data.SqlClient.SqlParameter("@Email", model.Email),
new System.Data.SqlClient.SqlParameter("@HANCHE", false),
new System.Data.SqlClient.SqlParameter("@KHOA", model.KHOA.checkBoolIsNull())
);
}
else
{
db.Database.ExecuteSqlCommand("insert into [users].[tbNguoidung](ID,USERNAME,HOLOT,TEN,BOPHAN,CHUCVU,NGAYTAO,Email,HANCHE,KHOA) values(" +
" @ID, @USERNAME, @HOLOT, @TEN, @BOPHAN, @CHUCVU, @NGAYTAO, @Email, @HANCHE, @KHOA)",
new System.Data.SqlClient.SqlParameter("@ID", user.Id),
new System.Data.SqlClient.SqlParameter("@USERNAME", model.Username),
new System.Data.SqlClient.SqlParameter("@HOLOT", model.HOLOT),
new System.Data.SqlClient.SqlParameter("@TEN", model.TEN),
new System.Data.SqlClient.SqlParameter("@BOPHAN", model.BOPHAN.checkIsNull()),
new System.Data.SqlClient.SqlParameter("@CHUCVU", model.CHUCVU.checkIsNull()),
new System.Data.SqlClient.SqlParameter("@NGAYTAO", DateTime.Now),
new System.Data.SqlClient.SqlParameter("@Email", model.Email),
new System.Data.SqlClient.SqlParameter("@HANCHE", false),
new System.Data.SqlClient.SqlParameter("@KHOA", model.KHOA.checkBoolIsNull())
);
}
return(Ok(1));
}
catch (Exception e)
{
dbOAMSEntities db = new dbOAMSEntities();
db.Database.ExecuteSqlCommand("delete [dbo].[AspNetUsers] where Id = @id",
new System.Data.SqlClient.SqlParameter("@id", user.Id));
return(BadRequest());
}
return(Ok(1));
}
public IHttpActionResult getalldatapagef(UserAParModel par)
{
try
{
dbOAMSEntities db = new dbOAMSEntities();
int p = par.valint1.checkIsNumber();
int nums = CommonSystem._itemsofpage * (p - 1);
string tablename = par.valstring1.checkIsNull();
string filterkey = par.valstring2.checkIsNull();
tablename = CryptData.querydecrypt(tablename);
var rfun = db.Database.SqlQuery <tbFunction>("select f.* from bnle.tbFunction f where f.MAFUNC = @MAFUNC and KICHTHOAT = 1"
, new SqlParameter("@MAFUNC", tablename)).FirstOrDefault();
if (rfun != null)
{
var vrs = db.Database.SqlQuery <tbFORMFUNC>("select f.* from bnle.tbFORMFUNC f where f.MAFUNC = @MAFUNC and HIENTHI = 1 and THUTUGRID > 0 order by THUTUGRID"
, new SqlParameter("@MAFUNC", tablename)).ToList();
List <DataControlCol> headercol = new List <DataControlCol>();
StringBuilder sqlcom = new StringBuilder("select ");
StringBuilder sqlcount = new StringBuilder("");
for (int i = 0; i < vrs.Count; i++)
{
DataControlCol h = new DataControlCol();
h.id = "idvb_" + vrs[i].ID;
sqlcom.Append(vrs[i].MATRUONG);
if ((i < vrs.Count - 1))
{
sqlcom.Append(",");
}
h.label = vrs[i].TENTRUONG;
h.namecol = vrs[i].MATRUONG;
h.width = vrs[i].WITHDGRID;
h.viewgrid = vrs[i].VIEWGRID.checkIsNull() == "" ? "text-align:center" : vrs[i].VIEWGRID;
headercol.Add(h);
}
if (rfun.IDKEY == "ID")
{
sqlcom.Append(",ID");
}
DataGridModel result = new DataGridModel();
result.datacols = headercol;
if (filterkey != "" && rfun.KEYFOREIGN.checkIsNull() != "")
{
sqlcount.Append("select count(*) from " + rfun.TABLENAME + " where " + rfun.KEYFOREIGN + " = @" + rfun.KEYFOREIGN);
sqlcom.Append(" from " + rfun.TABLENAME + " where " + rfun.KEYFOREIGN + " = @" + rfun.KEYFOREIGN + " ORDER BY " + rfun.ORDERBY + " OFFSET @nums ROWS FETCH NEXT @nume ROWS ONLY");
}
else
{
sqlcom.Append(" from " + rfun.TABLENAME + " ORDER BY " + rfun.ORDERBY + " OFFSET @nums ROWS FETCH NEXT @nume ROWS ONLY");
sqlcount.Append("select count(*) from " + rfun.TABLENAME);
}
result.datarows = new List <DataControlCols>();
result.formname = rfun.TENFUNC;
result.viewmode = rfun.VIEWMODE;
if (filterkey != "" && rfun.KEYFOREIGN.checkIsNull() != "")
{
var controws = db.Database.SqlQuery <int>(sqlcount.ToString(), new SqlParameter("@" + rfun.KEYFOREIGN, filterkey)).FirstOrDefault();
result.TotalItems = controws;
}
else
{
var controws = db.Database.SqlQuery <int>(sqlcount.ToString()).FirstOrDefault();
result.TotalItems = controws;
}
var dt = new DataTable();
result.PerPage = CommonSystem._itemsofpage;
var conn = db.Database.Connection;
var connectionState = conn.State;
try
{
if (connectionState != ConnectionState.Open)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sqlcom.ToString();
cmd.CommandType = CommandType.Text;
if (filterkey != "" && rfun.KEYFOREIGN.checkIsNull() != "")
{
cmd.Parameters.Add(new SqlParameter("@" + rfun.KEYFOREIGN, filterkey));
}
cmd.Parameters.Add(new SqlParameter("@nums", nums));
cmd.Parameters.Add(new SqlParameter("@nume", CommonSystem._itemsofpage));
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}
catch (Exception ex)
{
if (connectionState != ConnectionState.Closed)
{
conn.Close();
}
}
finally
{
if (connectionState != ConnectionState.Closed)
{
conn.Close();
}
}
if (dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
DataControlCols item = new DataControlCols();
item.items = new List <DataControlRow>();
foreach (var j in headercol)
{
DataControlRow i = new DataControlRow();
i.namecol = j.namecol;
i.ctype = "";
i.values = r[j.namecol].ToString();
i.viewgrid = j.viewgrid;
item.items.Add(i);
}
item.keyrow = r[rfun.IDKEY].ToString();
result.datarows.Add(item);
}
}
return(Ok(result));
}
return(Ok(new DataGridModel()));
}
catch
{
return(Ok(new DataGridModel()));
}
}
public IHttpActionResult saveeditdata(DataSaveControl par)
{
try
{
dbOAMSEntities db = new dbOAMSEntities();
string tablename = par.Tablename;
tablename = CryptData.querydecrypt(tablename);
string ID = par.id.checkIsNull();
var rfun = db.Database.SqlQuery <tbFunction>("select f.* from bnle.tbFunction f where f.MAFUNC = @MAFUNC and KICHTHOAT = 1"
, new SqlParameter("@MAFUNC", tablename)).FirstOrDefault();
if (rfun != null)
{
//var vrs = db.Database.SqlQuery<tbFORMFUNC>("select f.* from bnle.tbFORMFUNC f where f.MAFUNC = @MAFUNC and HIENTHI = 1 order by THUTUNHAP"
//, new SqlParameter("@MAFUNC", tablename)).ToList();
string keytype = "";
StringBuilder sqlcom = new StringBuilder("update " + rfun.TABLENAME + " set ");
int countpar = 0;
if (rfun.IDKEY == "ID")
{
countpar = par.items.Count + 1;
}
else
{
countpar = par.items.Count;
}
SqlParameter[] sqlp = new SqlParameter[countpar];
int k = 0;
for (int i = 0; i < par.items.Count; i++)
{
if (rfun.IDKEY != par.items[i].namecol)
{
sqlcom.Append(par.items[i].namecol + " = @" + par.items[i].namecol);
if ((i < par.items.Count - 1))
{
sqlcom.Append(",");
}
switch (par.items[i].ctype)
{
case "text":
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, par.items[i].values.checkIsNull()); break;
case "number":
if (par.items[i].values != null)
{
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, Convert.ToInt32(par.items[i].values));
}
else
{
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, DBNull.Value);
}
break;
case "select":
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, par.items[i].values); break;
case "textarea":
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, par.items[i].values); break;
case "autocomplete":
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, par.items[i].values); break;
case "calendar":
if (par.items[i].values != null)
{
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, Convert.ToDateTime(par.items[i].values));
}
else
{
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, DBNull.Value);
}
break;
case "check":
if (par.items[i].values != null)
{
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, Convert.ToBoolean(par.items[i].values));
}
else
{
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, DBNull.Value);
}
break;
default:
sqlp[k++] = new SqlParameter("@" + par.items[i].namecol, par.items[i].values); break;
}
}
else
{
keytype = par.items[i].ctype;
}
}
sqlcom.Append(" where " + rfun.IDKEY + " = @" + rfun.IDKEY);
switch (keytype)
{
case "text": sqlp[k] = new SqlParameter("@" + rfun.IDKEY, ID); break;
case "number": sqlp[k] = new SqlParameter("@" + rfun.IDKEY, Convert.ToInt32(ID)); break;
default: sqlp[k] = new SqlParameter("@" + rfun.IDKEY, ID); break;
}
db.Database.ExecuteSqlCommand(sqlcom.ToString(), sqlp);
}
return(Ok(1));
}
catch
{
return(BadRequest());
}
}
public IEnumerable <DataControl> getnewdatatable(DataParnew par)
{
try
{
dbOAMSEntities db = new dbOAMSEntities();
string tablename = par.Tablename;
tablename = CryptData.querydecrypt(tablename);
string ID = par.id.checkIsNull();
var rfun = db.Database.SqlQuery <tbFunction>("select f.* from bnle.tbFunction f where f.MAFUNC = @MAFUNC and KICHTHOAT = 1"
, new SqlParameter("@MAFUNC", tablename)).FirstOrDefault();
if (rfun != null)
{
int numcoll = rfun.NUMCOL.Value;
string widthlabel = "";
widthlabel = string.Format("col-md-{0} col-sm-{0} col-xs-3", rfun.WITHDLABEL.Value);
var vrs = db.Database.SqlQuery <tbFORMFUNC>("select f.* from bnle.tbFORMFUNC f where f.MAFUNC = @MAFUNC and HIENTHI = 1 order by THUTUNHAP"
, new SqlParameter("@MAFUNC", tablename)).ToList();
List <DataControl> hsc = new List <DataControl>();
bool newrow = false;
string keytype = "";
int cur = 0;
bool idkey = false;
for (int i = 0; i < vrs.Count; i++)
{
if (vrs[i].MATRUONG == rfun.IDKEY)
{
idkey = true;
}
DataControl h = new DataControl();
h.widthlabel = widthlabel;
h.id = "idvb_" + vrs[i].ID;
h.label = vrs[i].TENTRUONG;
h.namecol = vrs[i].MATRUONG;
h.ctype = vrs[i].KIEUDULIEU;
h.width = string.Format("col-md-{0} col-sm-{0} col-xs-12", vrs[i].WITHDFORM.Value);
if (vrs[i].WITHDFORM.Value > 0)
{
if (cur == 0)
{
h.viewed = "0";
cur = vrs[i].WITHDFORM.Value + rfun.WITHDLABEL.Value;
}
else if (vrs[i].WITHDFORM.Value + rfun.WITHDLABEL.Value + cur > 12)
{
h.viewed = "0";
cur = 0;
}
else
{
h.viewed = "1";
cur += vrs[i].WITHDFORM.Value + rfun.WITHDLABEL.Value;
}
}
else
{
h.width = "0";
}
h.required = vrs[i].BATBUOC == 1 ? true : false;
if (vrs[i].RANGBUOC.checkIsNull() != "")
{
string wheredir = "";
if (vrs[i].TUDIEN.checkIsNull() != "")
{
wheredir = "where " + vrs[i].TUDIEN;
}
string sqldir = string.Format("select {0} as [CODE],{1} as [VALUENAME] from {2} {3}",
vrs[i].KEYFOR, vrs[i].NAMEFOR, vrs[i].RANGBUOC, wheredir);
var dirs = db.Database.SqlQuery <DataDirControl>(sqldir).ToList();
h.dictionary = dirs;
}
else
{
if (vrs[i].TUDIEN.checkIsNull() != "")
{
var dirs = db.Database.SqlQuery <DataDirControl>("select [CODE], [VALUENAME] from [adm].[tbDanhmuc] where LOAIDM = @LOAIDM"
, new SqlParameter("@LOAIDM", vrs[i].TUDIEN)).ToList();
h.dictionary = dirs;
}
else
{
h.dictionary = new List <DataDirControl>();
}
}
h.values = vrs[i].MACDINH;
if (rfun.IDKEY == vrs[i].MATRUONG)
{
keytype = h.ctype;
}
h.disable = false;
hsc.Add(h);
}
if (!idkey)
{
DataControl h = new DataControl();
h.widthlabel = widthlabel;
h.id = "IDKEY";
h.label = rfun.IDKEY;
h.namecol = rfun.IDKEY;
h.ctype = rfun.IDKEY;
}
if (ID != "")
{
StringBuilder sqlcom = new StringBuilder("select *");
sqlcom.Append(" from " + rfun.TABLENAME + " where " + rfun.IDKEY + " = @" + rfun.IDKEY);
var dt = new DataTable();
var conn = db.Database.Connection;
var connectionState = conn.State;
try
{
if (connectionState != ConnectionState.Open)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sqlcom.ToString();
cmd.CommandType = CommandType.Text;
switch (keytype)
{
case "text": cmd.Parameters.Add(new SqlParameter("@" + rfun.IDKEY, ID)); break;
case "number": cmd.Parameters.Add(new SqlParameter("@" + rfun.IDKEY, Convert.ToInt32(ID))); break;
default: cmd.Parameters.Add(new SqlParameter("@" + rfun.IDKEY, ID)); break;
}
using (var reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
}
}
catch (Exception ex)
{
if (connectionState != ConnectionState.Closed)
{
conn.Close();
}
}
finally
{
if (connectionState != ConnectionState.Closed)
{
conn.Close();
}
}
if (dt.Rows.Count > 0)
{
DataControlCols item = new DataControlCols();
item.items = new List <DataControlRow>();
foreach (var j in hsc)
{
j.values = dt.Rows[0][j.namecol].ToString();
j.disable = false;
if (rfun.IDKEY == j.namecol)
{
j.disable = true;
}
}
}
}
return(hsc.AsEnumerable());
}
return((new List <DataControl>()).AsEnumerable());
}
catch
{
return((new List <DataControl>()).AsEnumerable());
}
}
public override System.Threading.Tasks.Task OnConnected()
{
string userName = "******";
if (Context.User.Identity.IsAuthenticated)
{
userName = Context.User.Identity.Name;
}
string connectionId = Context.ConnectionId;
var user = Users.GetOrAdd(userName, _ => new UserChat
{
Name = userName,
ConnectionIds = new HashSet <string>(),
FullName = "",
Fileimage = "",
ID = ""
});
dbOAMSEntities db = new dbOAMSEntities();
lock (user.ConnectionIds)
{
if (user.ConnectionIds.Count == 0)
{
var us = (from i in db.tbNguoidungs where i.USERNAME.Equals(userName) && i.KHOA == false select i).FirstOrDefault();
if (us != null)
{
UserInfo result = new UserInfo();
result.Name = us.USERNAME;
result.FullName = us.HOLOT + " " + us.TEN;
result.Fileimage = us.FILEANH;
user.Fileimage = us.FILEANH;
user.FullName = us.HOLOT + " " + us.TEN;
user.ID = us.ID;
Clients.Others.userNewconnected(result);
}
}
user.ConnectionIds.Add(connectionId);
// TODO: Broadcast the connected user
}
//dbAIMSEntities obj = new dbAIMSEntities();
//int s = 0;
//int e = CommonAIMS._itemsofpage;
//var re = obj.Database.SqlQuery<EventUserModel>("proc_getallEvent @username",
// new SqlParameter("@username", userName)).ToList();
DataChatInnit dataUser = new DataChatInnit();
dataUser.Notifications = new List <EventUser>();
dataUser.dataUserOnline = new List <UserInfo>();
foreach (var j in Users)
{
string item = j.Value.Name;
if (userName != item)
{
dataUser.dataUserOnline.Add(new UserInfo()
{
Name = j.Value.Name,
Fileimage = j.Value.Fileimage,
FullName = j.Value.FullName
});
}
}
//foreach (var d in re)
//{
// ls.Add(new EventUser()
// {
// intkey = d.ID,
// thoigian = d.NGAYYC.ToString("dd/MM/yyyy"),
// inttype = d.TypeYC,
// soyeucau = d.SOYC,
// strname = d.HOTEN,
// intkey1 = d.IDCT
// });
//}
//----------------------------
//ls.Add(new EventUser() { intkey = 1, inttype = 1, strname = "Aloo" });
//ls.Add(new EventUser() { intkey = 2, inttype = 2, strname = "Aloo111" });
return(Clients.Caller.onConnected(connectionId, userName, 0, dataUser));
}
