public FILEDETAIL GetFileDetailById(int id)
{
using (var db = new ResearchReportEntities())
{
return(db.FILEDETAILs.FirstOrDefault(f => f.FILEID == id));
}
}
public List <INSTITUTIONINFO> GetInstitutions()
{
using (var db = new ResearchReportEntities())
{
return(db.INSTITUTIONINFOes.Include("FILEDETAIL").Include("FILEDETAIL.FILETYPEINFO").Where(i => i.ISVALID == 1).ToList());
}
}
/// <summary>
/// Get data by store procedure
/// </summary>
/// <param name="inName">sp name</param>
/// <param name="inParms">parameters</param>
/// <returns></returns>
protected DataSet GetDataSetBySp(string inName, OracleParameter[] inParms)
{
using (var cmafdb = new ResearchReportEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
DataSet ds = null;
cmafdb.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cmafdb.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParms != null)
{
spCmd.Parameters.AddRange(inParms);
}
OracleDataAdapter da = new OracleDataAdapter(spCmd);
ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
public byte[] GetInstLogoBytesByCode(string code)
{
using (var db = new ResearchReportEntities())
{
INSTITUTIONINFO institutionInfo = db.INSTITUTIONINFOes.FirstOrDefault(i => i.CODE == code);
if (institutionInfo != null)
{
var cmaFile = FileService.GetFileById((long)institutionInfo.ID_C, "LOGO");
if (cmaFile != null)
{
return(cmaFile.Content);
}
}
return(null);
}
}
//reportType need to be englishname
public List <SelectListItem> GetReportTypeOptions(string orgCode, string reportType)
{
List <SelectListItem> typeList;
List <string> typeCodeList;
using (var db = new ResearchReportEntities())
{
if (orgCode == "all")
{
typeCodeList = (from f in db.FILEDETAILs
select new { f.INSTITUTIONINFOCODE, f.FILETYPECODE })
.AsEnumerable()
.Select(f => f.FILETYPECODE).Distinct().ToList();
}
else
{
typeCodeList = (from f in db.FILEDETAILs
select new { f.INSTITUTIONINFOCODE, f.FILETYPECODE })
.AsEnumerable()
.Where(f => orgCode.Contains(f.INSTITUTIONINFOCODE))
.Select(f => f.FILETYPECODE).Distinct().ToList();
}
typeList = (from p in db.FILETYPEINFOes
where p.ISVALID == 1
select p).Distinct()
.OrderBy(x => x.CODE)
.AsEnumerable()
.Where(f => typeCodeList.Contains(f.CODE))
.Select(x => new SelectListItem {
Text = x.DisplayName, Value = x.FILENAMEEN
}).Distinct(new UsuersComparer()).ToList();
if (orgCode == "all" || reportType == "all")
{
typeList.ForEach(t => t.Selected = true);
}
else
{
typeList.Where(t => t.Value == reportType).ToList().ForEach(t => t.Selected = true);
}
return(typeList);
}
}
public void AddFileVisitLog(int id, string userId)
{
using (var db = new ResearchReportEntities())
{
try
{
var fileVisitLog = new FILEVISITLOG
{
FILEID = id,
USERID = userId,
VISITDATETIME = DateTime.Now,
VISITLOGID = db.FILEVISITLOGs.Max(re => re.VISITLOGID) + 1
};
db.FILEVISITLOGs.Add(fileVisitLog);
db.SaveChanges();
}
finally
{
db.Database.Connection.Close();
}
}
}
public List <SelectListItem> GetOrgOptions(string orgCode)
{
using (var db = new ResearchReportEntities())
{
var orgList = (from p in db.INSTITUTIONINFOes
where p.ISVALID == 1
select p).Distinct().OrderBy(
x => x.CODE).AsEnumerable().Select(x => new SelectListItem {
Text = x.DisplayName, Value = x.CODE
}).ToList();
if (orgCode != "all")
{
orgList.Where(o => o.Value == orgCode).ToList().ForEach(o => o.Selected = true);
}
else
{
orgList.ForEach(o => o.Selected = true);
}
return(orgList);
}
}
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
});
}