private int CheckIsNullOrEmpty(MT_DB mtdb)
{
int rslt = 0;
DateTime?dtCDTFrom = mtdb.GetCreateDateFrom();
DateTime?dtCDTEnd = mtdb.GetCreateDateEnd();
foreach (PropertyInfo prop in mtdb.GetType().GetProperties())
{
var varprop = mtdb.GetType().GetProperty(prop.Name);
if (dtCDTFrom.HasValue)
{
rslt++;
}
if (dtCDTEnd.HasValue)
{
rslt++;
}
if (varprop.PropertyType.Name == "String")
{
rslt = string.IsNullOrEmpty(varprop.GetValue(mtdb, null) as string) ? rslt : ++rslt;
}
if (varprop.PropertyType.Name == "Int32")
{
rslt = ((varprop.GetValue(mtdb, null) as int?).HasValue) ? rslt : ++rslt;
}
if (varprop.PropertyType.Name == "DateTime")
{
rslt = ((varprop.GetValue(mtdb, null) as DateTime?).HasValue) ? rslt : ++rslt;
}
if (varprop.PropertyType.IsGenericType &&
varprop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var propertyType = varprop.PropertyType.GetGenericArguments()[0].UnderlyingSystemType;
if (propertyType.Name == "DateTime")
{
DateTime?value = varprop.GetValue(mtdb, null) as DateTime?;
rslt = (!value.HasValue) ? rslt : ++rslt;
}
if (propertyType.Name == "Int32")
{
int?value = varprop.GetValue(mtdb, null) as int?;
rslt = (!value.HasValue) ? rslt : ++rslt;
}
if (propertyType.Name == "Decimal")
{
decimal?value = varprop.GetValue(mtdb, null) as decimal?;
rslt = (!value.HasValue) ? rslt : ++rslt;
}
}
}
//rslt = (mtdb.CREATEDATE.HasValue) ? rslt : rslt++;
return(rslt);
}
public IEnumerable <VMTDB> Get(MT_DB mtdb)
{
SqlString = GetSqlQuery(mtdb);
System.Data.DataTable dt = DAO.oracleCmdDataSetSP(this.config, SqlString).Tables[0];
MTDBList = ConvertToTankReadings(dt);
//MTDBList = MTDBList.Where(x => x.DOCNUM == docnum);
return(MTDBList);
}
public IEnumerable <VMTDB> Get(MT_DB mtdb)
{
mtdbcollection = new MTDBCollection(config);
mtdbcollection.Get(mtdb);
return(mtdbcollection.MTDBList);
}
private string GetSqlQuery(MT_DB mtdb)
{
StringBuilder sql = new StringBuilder();
StringBuilder sbwhere = new StringBuilder();
string space = " ";
string select = string.Empty;
string where = string.Empty;
string orderby = string.Empty;
string MSGID = mtdb.MSGID;
DateTime?dtCDTFrom = mtdb.GetCreateDateFrom();
DateTime?dtCDTEnd = mtdb.GetCreateDateEnd();
select = @"SELECT /*+" + Constant.TSQL_HINT + @"*/ * FROM MT_DB";
if (CheckIsNullOrEmpty(mtdb) == 0)
{
sql.Append(select);
sql.Append(space);
//TO_DATE('2016/5/18 00:50:00','yyyy/MM/dd HH24:MI:SS')
sql.Append("WHERE ROWNUM=0 AND NVL(DOCNUM, ' ') != ' ' ORDER BY CREATEDATE DESC");
}
else
{
sbwhere.Append("WHERE"); sbwhere.Append(space);
int icond = 0;
foreach (PropertyInfo prop in mtdb.GetType().GetProperties())
{
var varprop = mtdb.GetType().GetProperty(prop.Name);
if (varprop.PropertyType.Name == "String")
{
string value = varprop.GetValue(mtdb, null) as string;
if (!string.IsNullOrEmpty(value))
{
/*
* if (varprop.Name == "CONTROLNUM" || varprop.Name == "IDOC")
* {
* int ivalue = 0;
* value = int.TryParse(value, out ivalue) ? ivalue.ToString("000000000") : value.Trim();
* }
*/
if (varprop.Name == "KEYWORD_SEARCH")
{
string[] values = value.Split(' ');
StringBuilder sbSubwhere = new StringBuilder();
int isubcond = 0;
foreach (string item in values)
{
where = "TRIM(" + varprop.Name + ") LIKE '%@%'"; where = where.Replace("@", item.Trim());
if (isubcond > 0)
{
sbSubwhere.Append(space);
sbSubwhere.Append("OR");
sbSubwhere.Append(space);
}
sbSubwhere.Append(where);
isubcond++;
}
where = "(" + sbSubwhere.ToString() + ")";
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
icond++;
}
else
{
string outstring = ConvertToStringArray(value, varprop.Name);
where = varprop.Name + " IN (@)"; where = where.Replace("@", outstring);
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
icond++;
}
}
}
if (varprop.PropertyType.Name == "Int32")
{
int?value = varprop.GetValue(mtdb, null) as int?;
if (value.HasValue)
{
where = varprop.Name + " = @"; where = where.Replace("@", value.Value.ToString());
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
icond++;
}
}
if (varprop.PropertyType.Name == "DateTime")
{
DateTime?value = varprop.GetValue(mtdb, null) as DateTime?;
if (value.HasValue)
{
where = varprop.Name + " = '@'"; where = where.Replace("@", value.Value.ToString());
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
icond++;
}
} // end if
if (varprop.PropertyType.IsGenericType &&
varprop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var propertyType = varprop.PropertyType.GetGenericArguments()[0].UnderlyingSystemType;
if (propertyType.Name == "DateTime")
{
DateTime?value = varprop.GetValue(mtdb, null) as DateTime?;
if (value.HasValue)
{
where = varprop.Name + " = '@'"; where = where.Replace("@", value.Value.ToString());
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
icond++;
}
}
if (propertyType.Name == "Int32")
{
Int32?value = varprop.GetValue(mtdb, null) as Int32?;
if (value.HasValue)
{
where = varprop.Name + " = @"; where = where.Replace("@", value.Value.ToString());
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
icond++;
}
}
if (propertyType.Name == "Decimal")
{
decimal?value = varprop.GetValue(mtdb, null) as decimal?;
if (value.HasValue)
{
where = varprop.Name + " = @"; where = where.Replace("@", value.Value.ToString());
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
icond++;
}
}
}
} // end foreach
//where = "ROWNUM <= @"; where = where.Replace("@", 100.ToString());
where = "1=1";
if (icond > 0)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
}
sbwhere.Append(where);
if (dtCDTFrom.HasValue)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
where = "CREATEDATE >= TO_DATE('@', 'yyyy/MM/dd HH24:MI:SS')";
where = where.Replace("@", dtCDTFrom.Value.ToString("yyyy/MM/dd HH:mm:ss"));
sbwhere.Append(where);
}
if (dtCDTEnd.HasValue)
{
sbwhere.Append(space);
sbwhere.Append("AND");
sbwhere.Append(space);
where = "CREATEDATE <= TO_DATE('@', 'yyyy/MM/dd HH24:MI:SS')";
where = where.Replace("@", dtCDTEnd.Value.ToString("yyyy/MM/dd HH:mm:ss"));
sbwhere.Append(where);
sbwhere.Append(space);
}
orderby = "ORDER BY CREATEDATE DESC";
sql.Append(select);
sql.Append(space);
sql.Append(sbwhere.ToString());
sql.Append(space);
sql.Append(orderby);
} // end if
return(sql.ToString());
}
