private DataSet GetDataSetBySp(string inName, OracleParameter[] inParams) { using (var IPPDB = new IPPEntities()) { using (var spCmd = new OracleCommand()) { DataSet ds = null; IPPDB.Database.Connection.Open(); spCmd.Connection = new OracleConnection(IPPDB.Database.Connection.ConnectionString); spCmd.CommandText = inName; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; if (inParams != null) { spCmd.Parameters.AddRange(inParams); } var da = new OracleDataAdapter(spCmd); ds = new DataSet(); da.Fill(ds); return(ds); } } }
public List <MODULEINFO> GetModuleList() { using (var IPPDB = new IPPEntities()) { return((from m in IPPDB.MODULEINFOs select m ).OrderBy(m => m.ID).ToList()); } }
public int?GetModuleIdByTopicId(int topId) { using (var IPPDB = new IPPEntities()) { return((from t in IPPDB.TOPICs where t.ID == topId select t.MODULEID).ToList().FirstOrDefault()); } }
public List <string> GetTopicApprovers(int topId) { using (var IPPDB = new IPPEntities()) { return((from t in IPPDB.TOPICAPPROVERs where t.TOPICID == topId select t.APPROVER).ToList()); } }
public List <string> GetTopicApproversByFileId(int fileId) { using (var IPPDB = new IPPEntities()) { var topicId = (from f in IPPDB.FILEINFOs where f.ID == fileId select f.TOPICID).ToList().FirstOrDefault(); return(GetTopicApprovers((int)topicId)); } }
public int GetTopicIdByFileId(long?fileId) { using (var IPPDB = new IPPEntities()) { var topicId = (from f in IPPDB.FILEINFOs where f.ID == fileId select f.TOPICID).ToList().FirstOrDefault(); return((int)topicId); } }
public VAV.Model.Data.IPP.Submitter GeSubmitterById(string id) { using (var IPPDB = new IPPEntities()) { return((from s in IPPDB.SUBMITTERs where s.ID == id select new VAV.Model.Data.IPP.Submitter { ID = s.ID, Email = s.EMAIL, RM = s.RM, Name = s.NAME }).ToList().FirstOrDefault()); } }
public Tuple <bool, bool> GetTopicApporve(int topId) { using (var IPPDB = new IPPEntities()) { var topic = (from t in IPPDB.TOPICs where t.ID == topId select t).ToList(); var topicApp = topic.Select(t => new Tuple <bool, bool>(t.ISAPPROVE == 1, t.ISINTERNALAPPROVE == 1)).ToList().FirstOrDefault(); return(topicApp); } }
protected void ExecNonQuerySp(string inName, OracleParameter[] inParms) { using (var IPPDB = new IPPEntities()) { IPPDB.Database.Connection.Open(); DbCommand cmd = IPPDB.Database.Connection.CreateCommand(); cmd.CommandText = inName; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.AddRange(inParms); cmd.ExecuteNonQuery(); } }
protected void ExecNonQuerySpWithResult(string inName, OracleParameter[] inParms, out string result) { using (var IPPDB = new IPPEntities()) { IPPDB.Database.Connection.Open(); DbCommand cmd = IPPDB.Database.Connection.CreateCommand(); cmd.CommandText = inName; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.AddRange(inParms); cmd.ExecuteNonQuery(); result = Convert.ToString(cmd.Parameters["V_Result"].Value); } }
public static string UpdateFileTopic(DateTime lastSyncTime, DateTime currentSyncTime) { var result = "Success"; using (var IPPDB = new IPPEntities()) { try { IPPDB.Database.Connection.Open(); var cmd = IPPDB.Database.Connection.CreateCommand();; cmd.CommandText = "UpdateFileTopic"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.AddRange(new[] { new OracleParameter("V_LastSyncTime", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)lastSyncTime }, new OracleParameter("V_CurrentSyncTime", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)currentSyncTime } }); OracleParameter outPar = new OracleParameter(); outPar.ParameterName = "V_Result"; outPar.DbType = DbType.String; outPar.Size = 400; outPar.Direction = ParameterDirection.Output; cmd.Parameters.Add(outPar); cmd.ExecuteNonQuery(); var ret = (string)cmd.Parameters["V_Result"].Value; result = ret.Split('!').FirstOrDefault() == "Success" ? "Success" : "Fail"; } catch (Exception ex) { result = "Fail" + ex.Message; } finally { IPPDB.Database.Connection.Close(); } return(result); } }
public List <TOPIC> GetTopicListByModuleId(int moduleId) { if (moduleId != 0) { using (var IPPDB = new IPPEntities()) { return((from m in IPPDB.TOPICs where m.MODULEID == moduleId select m).OrderBy(t => t.ID).ToList()); } } else { using (var IPPDB = new IPPEntities()) { return((from m in IPPDB.TOPICs select m).OrderBy(t => t.ID).ToList()); } } }
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 void UploadFile(FileCreate fileCreate) { var paramArray = new List <OracleParameter> { new OracleParameter("V_ID", OracleDbType.Int64) { Value = fileCreate.ID }, new OracleParameter("V_TitleCn", OracleDbType.NVarchar2) { Value = fileCreate.TitleCn }, new OracleParameter("V_TitleEn", OracleDbType.NVarchar2) { Value = fileCreate.TitleEn }, new OracleParameter("V_TopicID", OracleDbType.Int32) { Value = fileCreate.TopicID }, new OracleParameter("V_Status", OracleDbType.Int32, ParameterDirection.InputOutput) { Value = fileCreate.Status }, new OracleParameter("V_SubmitDate", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)fileCreate.SubmitDate }, new OracleParameter("V_ReportDate", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)fileCreate.ReportDate }, new OracleParameter("V_FileType", OracleDbType.NVarchar2) { Value = fileCreate.FileType }, //new OracleParameter("V_Doc", OracleDbType.VarBinary) {Value = fileCreate.Doc}, new OracleParameter("V_RIC", OracleDbType.NVarchar2) { Value = fileCreate.RIC }, new OracleParameter("V_DescrCn", OracleDbType.NVarchar2) { Value = fileCreate.DescrCn }, new OracleParameter("V_DescrEn", OracleDbType.NVarchar2) { Value = fileCreate.DescrEn }, new OracleParameter("V_AuthorRM", OracleDbType.NVarchar2) { Value = fileCreate.AuthorRM }, new OracleParameter("V_AuthorEmail", OracleDbType.NVarchar2) { Value = fileCreate.AuthorEmail }, new OracleParameter("V_Author", OracleDbType.NVarchar2) { Value = fileCreate.Author }, new OracleParameter("V_Tag", OracleDbType.NVarchar2) { Value = fileCreate.Tag }, new OracleParameter("V_UploadType", OracleDbType.NVarchar2) { Value = fileCreate.UploadType }, new OracleParameter("V_Source", OracleDbType.NVarchar2) { Value = fileCreate.Source }, new OracleParameter("V_FileName", OracleDbType.NVarchar2) { Value = fileCreate.FileName }, new OracleParameter("V_SubmitterID", OracleDbType.NVarchar2) { Value = fileCreate.SubmitterID }, new OracleParameter("V_DisplayOrder", OracleDbType.NVarchar2) { Value = fileCreate.DisplayOrder }, }; OracleParameter outPar = new OracleParameter(); outPar.ParameterName = "V_Result"; outPar.DbType = DbType.Int32; outPar.Size = 400; outPar.Direction = ParameterDirection.Output; paramArray.Add(outPar); string fileId = ""; ExecNonQuerySpWithResult("UploadFile", paramArray.ToArray(), out fileId); if (!string.IsNullOrEmpty(fileId)) { string path = ""; using (var db = new IPPEntities()) { var id = Convert.ToInt32(fileId); 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 }).ToList().FirstOrDefault(); path = "/IPP/" + subPath.NAMEEN + "/" + subPath.ID; } FileService.UploadFile(path, fileId + "." + fileCreate.FileType, fileCreate.Doc); } }