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