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)); }