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