public static bool CanGenrateFile()
{
string sql = "select count(*) from Iris_Reco_Image";
using (DataAccessBroker broker = DataAccessFactory.Instance())
{
int count = Convert.ToInt32(broker.ExecuteScalar(sql, null, CommandType.Text));
return(count < 5);
}
}
public static LigerGrid ExcuteTable <T>(string hql, DataAccessParameterCollection lstParameter, LigerGrid grid, DataAccessBroker broker) where T : EntityBase, new()
{
object total = broker.ExecuteScalar("select count(1) as Total from (" + hql + ") as ttable", lstParameter, CommandType.Text);
StringBuilder strsql = new StringBuilder();
if (grid.pageIndex != 0 && grid.pageSize != 0)
{
strsql.Append("select * from ( select top " + (grid.pageIndex * grid.pageSize) + " row_number() over(order by " + grid.sortName + " " + grid.sortOrder.ToLower() + ") as Row_Number, ");
strsql.Append(hql.Replace(":", "@").Substring(7));
if (!string.IsNullOrEmpty(grid.Where))
{
strsql.Append(" and (" + grid.Where.Replace(":", "@") + ")");;
}//as
strsql.Append(" ) ttable where Row_Number between " + (((grid.pageIndex - 1) * grid.pageSize) + 1) + " and " + ((grid.pageIndex) * grid.pageSize));
}
else
{
strsql.Append(hql);
if (!string.IsNullOrEmpty(grid.sortName))
{
strsql.Append(" order by " + grid.sortName);
if (!string.IsNullOrEmpty(grid.sortOrder))
{
strsql.Append(" " + grid.sortOrder);
}
else
{
strsql.Append(" ASC");
}
}
}
DataSet ds = broker.FillSQLDataSet(strsql.ToString(), lstParameter);
EntityCollection <T> lstEntity = new EntityCollection <T>();
List <Dictionary <string, object> > dicLstRow = new List <Dictionary <string, object> >();
ToEntityList <T>(ds.Tables[0], ref dicLstRow, ref lstEntity);
if (grid.IsDataEntity)
{
grid.Rows = lstEntity;
}
else
{
grid.Rows = dicLstRow;
}
grid.Total = (total != null ? Convert.ToInt32(total) : 0);
return(grid);
}
private static JqGrid ExcuteTable <T>(string hql, DataAccessParameterCollection lstParameter, JqGrid grid, DataAccessBroker broker) where T : EntityBase, new()
{
int index = 0;
StringBuilder whereSql = new StringBuilder(hql);
if (grid.QueryField["_search"] == "true")
{
foreach (KeyValuePair <string, string> kv in grid.QueryField)
{
if (index > 5)
{
DateTime result = DateTime.Now;
if (DateTime.TryParse(kv.Value, out result))
{
whereSql.AppendLine($" and datediff(day,{kv.Key},'{kv.Value}')=0 ");
}
else
{
whereSql.AppendLine($" and {kv.Key} like N{Utils.ToSqlLikeStr(kv.Value)} ");
}
}
index++;
}
}
//as
object total = broker.ExecuteScalar("select count(1) as Total from (" + whereSql + ") ttable", lstParameter, CommandType.Text);
StringBuilder strsql = new StringBuilder();
if (grid.page != 0 && grid.rows != 0)
{//top " + (grid.page * grid.rows) + "
strsql.Append("select * from ( select row_number() over(order by " + grid.sidx + " " + grid.sord.ToLower() + ") as Row_Number, ");
strsql.Append(whereSql.ToString().Substring(7));
if (!string.IsNullOrEmpty(grid.Where))
{
strsql.Append(" and (" + grid.Where + ")");;
}
//as
strsql.Append(" ) ttable where Row_Number between " + (((grid.page - 1) * grid.rows) + 1) + " and " + ((grid.page) * grid.rows));
}
else
{
strsql.Append(whereSql);
if (!string.IsNullOrEmpty(grid.sidx))
{
strsql.Append(" order by " + grid.sidx);
if (!string.IsNullOrEmpty(grid.sord))
{
strsql.Append(" " + grid.sord);
}
else
{
strsql.Append(" ASC");
}
}
}
DataSet ds = broker.FillSQLDataSet(strsql.ToString(), lstParameter);
EntityCollection <T> lstEntity = new EntityCollection <T>();
List <Dictionary <string, object> > dicLstRow = new List <Dictionary <string, object> >();
ToEntityList <T>(ds.Tables[0], ref dicLstRow, ref lstEntity);
if (grid.IsDataEntity)
{
grid.RowsData = lstEntity;
}
else
{
grid.RowsData = dicLstRow;
}
grid.Total = (total != null ? Convert.ToInt32(total) : 0);
return(grid);
}