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