public IEnumerable <VMTREFDB> Get(VMTREFDB mtrefdb)
{
SqlString = GetSqlQuery(mtrefdb);
System.Data.DataTable dt = DAO.oracleCmdDataSetSP(this.config, SqlString).Tables[0];
MTREFDBList = ConvertToTankReadings(dt);
//MTDBList = MTDBList.Where(x => x.DOCNUM == docnum);
return(MTREFDBList);
}
private int CheckIsNullOrEmpty(VMTREFDB mtrefdb)
{
int rslt = 0;
foreach (PropertyInfo prop in mtrefdb.GetType().GetProperties())
{
var varprop = mtrefdb.GetType().GetProperty(prop.Name);
if (varprop.PropertyType.Name == "String")
{
rslt = string.IsNullOrEmpty(varprop.GetValue(mtrefdb, null) as string) ? rslt : ++rslt;
}
if (varprop.PropertyType.Name == "Int32")
{
rslt = ((varprop.GetValue(mtrefdb, null) as int?).HasValue) ? rslt : ++rslt;
}
if (varprop.PropertyType.Name == "DateTime")
{
rslt = ((varprop.GetValue(mtrefdb, 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(mtrefdb, null) as DateTime?;
rslt = (!value.HasValue) ? rslt : ++rslt;
}
if (propertyType.Name == "Int32")
{
int?value = varprop.GetValue(mtrefdb, null) as int?;
rslt = (!value.HasValue) ? rslt : ++rslt;
}
if (propertyType.Name == "Decimal")
{
decimal?value = varprop.GetValue(mtrefdb, null) as decimal?;
rslt = (!value.HasValue) ? rslt : ++rslt;
}
}
}
//rslt = (mtdb.CREATEDATE.HasValue) ? rslt : rslt++;
return(rslt);
}
private void MT_REFDB(string optradio, string partner, string division, string region,
string isasenderid, string isareceiverid,
string gssenderid, string edimsgtype)
{
VMTREFDB vmtrefdb = new VMTREFDB();
vmtrefdb.PARTNER = (string.IsNullOrEmpty(partner)) ? string.Empty : string.Empty;
vmtrefdb.DIVISION = (string.IsNullOrEmpty(division)) ? string.Empty : string.Empty;
vmtrefdb.REGION = (string.IsNullOrEmpty(region)) ? string.Empty : string.Empty;
vmtrefdb.ISASENDERID = (string.IsNullOrEmpty(isasenderid)) ? string.Empty : string.Empty;
vmtrefdb.ISARECEIVERID = (string.IsNullOrEmpty(isareceiverid)) ? string.Empty : string.Empty;
vmtrefdb.GSSENDERID = (string.IsNullOrEmpty(gssenderid)) ? string.Empty : string.Empty;
vmtrefdb.EDIMSGTYPE = (string.IsNullOrEmpty(edimsgtype)) ? string.Empty : string.Empty;
optradio = Constant.PIQServer;
optradio = string.IsNullOrEmpty(optradio) ? Constant.PIQServer : optradio;
//IMTRef imtref = DataAccess.CreateMTREFDB(optradio);
IMTREFDBCollection imtrefdb = DataAccess.CreateMTREFDBCollection(optradio);
vmtrefdbList = imtrefdb.Get(vmtrefdb);
IList <string> partners = vmtrefdbList.OrderBy(x => x.PARTNER).Select(x => x.PARTNER.ToUpper().Trim()).Distinct().ToList();
IList <string> divisions = vmtrefdbList.OrderBy(x => x.DIVISION).Select(x => x.DIVISION.ToUpper().Trim()).Distinct().ToList();
IList <string> regions = vmtrefdbList.OrderBy(x => x.REGION).Select(x => x.REGION.ToUpper().Trim()).Distinct().ToList();
IList <string> isasenderids = vmtrefdbList.OrderBy(x => x.ISASENDERID).Select(x => x.ISASENDERID.ToUpper().Trim()).Distinct().ToList();
IList <string> isareceiverids = vmtrefdbList.OrderBy(x => x.ISARECEIVERID).Select(x => x.ISARECEIVERID.ToUpper().Trim()).Distinct().ToList();
IList <string> gssenderids = vmtrefdbList.OrderBy(x => x.GSSENDERID).Select(x => x.GSSENDERID.ToUpper().Trim()).Distinct().ToList();
IList <string> edimsgtypes = vmtrefdbList.OrderBy(x => x.EDIMSGTYPE).Select(x => x.EDIMSGTYPE.ToUpper().Trim()).Distinct().ToList();
ViewData["Partners"] = partners;
ViewData["Divisions"] = divisions;
ViewData["Regions"] = regions;
ViewData["ISASenderIds"] = isasenderids;
ViewData["ISAReceiverIds"] = isareceiverids;
ViewData["GSSenderIds"] = gssenderids;
ViewData["EDIMsgTypes"] = edimsgtypes;
}
public IEnumerable <VMTREFDB> Get(VMTREFDB vmtrefdb)
{
mtrefdbcollection = new MTREFDBCollection(config);
mtrefdbcollection.Get(vmtrefdb);
return(mtrefdbcollection.MTREFDBList);
}
private string GetSqlQuery(VMTREFDB mtrefdb)
{
StringBuilder sql = new StringBuilder();
StringBuilder sbwhere = new StringBuilder();
string space = " ";
string select = string.Empty;
string where = string.Empty;
string orderby = string.Empty;
select = @"SELECT /*+" + Constant.TSQL_HINT + @"*/ * FROM MT_REF";
if (CheckIsNullOrEmpty(mtrefdb) == 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<=1000 AND NVL(PARTNER, ' ') != ' '");
}
else
{
sbwhere.Append("WHERE"); sbwhere.Append(space);
int icond = 0;
foreach (PropertyInfo prop in mtrefdb.GetType().GetProperties())
{
var varprop = mtrefdb.GetType().GetProperty(prop.Name);
if (varprop.PropertyType.Name == "String")
{
string value = varprop.GetValue(mtrefdb, null) as string;
if (!string.IsNullOrEmpty(value))
{
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++;
}
} // end if
} // 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);
orderby = "ORDER BY 1 ASC";
sql.Append(select);
sql.Append(space);
sql.Append(sbwhere.ToString());
sql.Append(space);
sql.Append(orderby);
} // end if
return(sql.ToString());
}
