protected DataSet GetZcxDataSetBySp(string inName, OracleParameter[] inParms)
{
using (var cnEdb = new ZCXEntities())
{
using (var spCmd = new OracleCommand())
{
cnEdb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cnEdb.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParms != null)
{
spCmd.Parameters.AddRange(inParms);
}
var da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
public V_BOND_ABS GetBondInfoByUnicode(long bond_Uni_Code)
{
using (var zcxDb = new ZCXEntities())
{
return(zcxDb.V_BOND_ABS.FirstOrDefault(b => b.BOND_UNI_CODE == bond_Uni_Code));
}
}
public void TestWithData()
{
using (var ZCXDB = new ZCXEntities())
{
int parCode = 0;
var q1 = from info in ZCXDB.RATE_ORG_INFO_F
where (info.TYPE_BIG_PAR.Value == parCode || parCode == 0) &&
info.ISVALID.Value == 1
select info;
Console.WriteLine(q1.Count());
}
}
public void test()
{
using (var ZCXDB = new ZCXEntities())
{
var query = ZCXDB.RATE_ORG_INFO_F.Select("new (COM_CHI_NAME, COM_UNI_CODE)").Take(10);
Console.WriteLine(query);
foreach (var item in query)
{
Console.WriteLine(item.ToString());
}
}
}
public CmaFile GetFileById(long id, string para)
{
string path = "";
string fileName = "";
string fileType = "";
if (para == "VAV") //for home page download
{
path = "|VAV|Home";
using (var db = new CMAEntities())
{
fileType = db.HOMEFILEs.Where(f => f.ID == id).Select(f => f.FILETYPE).ToList().FirstOrDefault();
fileName = id + "." + fileType;
}
}
else if (para == "IPP") //for macro-ecnomic 100 charts and user uploaded file
{
using (var db = new IPPEntities())
{
var subPath = (from f in db.FILEINFOs
join t in db.TOPICs on f.TOPICID equals t.ID
join m in db.MODULEINFOs on t.MODULEID equals m.ID
where f.ID == id
select new { m.NAMEEN, t.ID, f.FILETYPE }).ToList().FirstOrDefault();
path = "|IPP|" + subPath.NAMEEN + "|" + subPath.ID;
fileName = id + "." + subPath.FILETYPE;
fileType = subPath.FILETYPE;
}
}
else if (para.Contains("WMP"))//cmafiledb
{
using (var db = new Genius_HistEntities())
{
if (para == "WMP_PROSP")
{
var accRoute = db.BANK_FIN_PRD_PROSP.Where(f => f.INNER_CODE == id).ToList().Select(f => f.ACCE_ROUTE).FirstOrDefault().Replace("\\", "|");
path = "|WMP|RROSP|" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = id.ToString() + accRoute.Substring(accRoute.LastIndexOf("."));
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
else if (para == "WMP_REP")
{
var accRoute = db.FIN_PRD_RPT.Where(f => f.RPT_ID == id).ToList().Select(f => f.ACCE_ROUTE).FirstOrDefault().Replace("\\", "|");
path = "|WMP|PRD_RPT|" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = id.ToString() + accRoute.Substring(accRoute.LastIndexOf("."));
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
else if (para == "WMP_DISC")
{
var accRoute = db.DISC_ACCE_CFP.Where(f => f.SEQ == id).ToList().Select(f => f.ACCE_ROUTE).FirstOrDefault().Replace("\\", "|");
var accOrder = db.DISC_ACCE_CFP.Where(f => f.SEQ == id).ToList().Select(f => f.ACCE_ORDER).FirstOrDefault().ToString();
var disc_id = db.DISC_ACCE_CFP.Where(f => f.SEQ == id).ToList().Select(f => f.DISC_ID).FirstOrDefault().ToString();
path = "|WMP|DISC_CFP|" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = disc_id + "_" + accOrder + accRoute.Substring(accRoute.LastIndexOf("."));
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
}
}
else if (para == "ZCX")
{
using (var db = new ZCXEntities())
{
var accRoute = db.RATE_REP.Where(f => f.RATE_ID == id).ToList().Select(f => f.RATE_FILE_PATH).FirstOrDefault().Replace("/", "|");
path = "|ZCX|RATE" + accRoute.Substring(0, accRoute.LastIndexOf("|"));
fileName = accRoute.Substring(accRoute.LastIndexOf("|") + 1);
fileType = accRoute.Substring(accRoute.LastIndexOf(".") + 1);
}
}
else if (para == "RR")
{
using (var db = new ResearchReportEntities())
{
path = "|RR|" + db.ALLFILESINFOes.Where(f => f.FILEID == id).ToList().Select(f => f.BIZCODE + "|" + f.INSTITUTIONINFOCODE + "|" + f.FILETYPECODE).FirstOrDefault();
fileName = db.ALLFILESINFOes.Where(f => f.FILEID == id).ToList().Select(f => f.FILEID.ToString() + "." + f.EXTENSION).FirstOrDefault();
}
}
else if (para.ToUpper() == "LOGO")
{
using (var db = new ResearchReportEntities())
{
var institution = db.INSTITUTIONINFOes.FirstOrDefault(i => i.ID_C == (decimal)id);
if (institution != null)
{
path = "|RR|Logo";
fileName = string.Format("{0}.{1}", institution.CODE, institution.EXTENSION);
}
}
}
var storage = new StorageServiceClient();
var obj = new fileEntity();
try
{
obj = storage.RetriveFileObj(path, fileName);
}
catch (Exception ex)
{
throw ex;
}
if (obj.fileData == null || obj.fileData.Length == 0)
{
return(null);
}
return(new CmaFile {
Id = id, Content = obj.fileData, FileType = fileType
});
}
public DataTable GetAbsBondList(DateTime startDate, DateTime endDate, string bondClass, string couponClass, string option, string bondRating, string isBondCode, string bondCodeOrIss, string columnList, int startPage, int pageSize, out int total)
{
var paramArray = new[]
{
new OracleParameter("P_StartDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)startDate
},
new OracleParameter("P_EndDate", OracleDbType.TimeStamp)
{
Value = (OracleTimeStamp)endDate
},
new OracleParameter("P_BondClass", OracleDbType.Varchar2)
{
Value = bondClass
},
new OracleParameter("P_CouponClass", OracleDbType.Varchar2)
{
Value = couponClass
},
new OracleParameter("P_Option", OracleDbType.Varchar2)
{
Value = option
},
new OracleParameter("P_BondRating", OracleDbType.Varchar2)
{
Value = bondRating
},
new OracleParameter("P_IsBondeCode", OracleDbType.Varchar2)
{
Value = isBondCode
},
new OracleParameter("P_BondCodeOrIss", OracleDbType.NVarchar2)
{
Value = bondCodeOrIss
},
new OracleParameter("P_Culture", OracleDbType.Varchar2)
{
Value = Thread.CurrentThread.CurrentUICulture.Name
},
new OracleParameter("P_ColumnList", OracleDbType.Varchar2)
{
Value = columnList
},
new OracleParameter("P_StartPage", OracleDbType.Int32)
{
Value = startPage
},
new OracleParameter("P_PageSize", OracleDbType.Int32)
{
Value = pageSize
},
new OracleParameter("P_TOTAL", OracleDbType.Int32, ParameterDirection.Output)
{
Value = 0
},
new OracleParameter("P_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cmaDb = new ZCXEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
cmaDb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString);
spCmd.CommandText = "GetAbsListBondInfo";
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
spCmd.Parameters.AddRange(paramArray);
OracleDataAdapter da = new OracleDataAdapter(spCmd);
var ds = new DataSet();
da.Fill(ds);
total = Convert.ToInt32(spCmd.Parameters["P_TOTAL"].Value.ToString());
return(ds.Tables[0]);
}
}
}
