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