public DataTable GetItemFile(string ID) { var paramArray = new[] { new OracleParameter("P_ID", OracleDbType.Int32) { Value = Convert.ToInt32(ID) }, new OracleParameter("P_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; using (var cmaDb = new CMAEntities()) { using (OracleCommand spCmd = new OracleCommand()) { cmaDb.Database.Connection.Open(); spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString); spCmd.CommandText = "GetHomeItem"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paramArray); OracleDataAdapter da = new OracleDataAdapter(spCmd); var ds = new DataSet(); da.Fill(ds); return(ds.Tables[0]); } } }
public void insertReportColumnDefinition() { var newcolumn = new REPORTCOLUMNDEFINITION() { }; using (var cmadb = new CMAEntities()) { var reportList = cmadb.REPORTDEFINITIONs.Where(r => r.ID > 30000).ToList(); var reportColumnDefinitions = new List <REPORTCOLUMNDEFINITION>(); foreach (var reportdefinition in reportList) { using (var srdb = new SRDBEntities()) { var tableConfig = srdb.TABLE_CONFIG.FirstOrDefault(x => x.TABLENAMEEN.ToLower() == reportdefinition.TABLE_NAME.ToLower()); var columnsCn = tableConfig.FIELDNAMESARRCN.Split(';'); var columnsEn = tableConfig.FIELDNAMESARREN.Split(';'); var columnsTypes = tableConfig.FIELDTYPESARR.Split(';'); if (columnsCn.Length == columnsEn.Length && columnsCn.Length == columnsTypes.Length) { for (int i = 0; i < columnsCn.Length; i++) { if (string.IsNullOrEmpty(columnsCn[i])) { continue; } var column = new Column() { ColumnName = columnsEn[i], ColumnHeaderCN = columnsCn[i], ColumnHeaderEN = columnsEn[i], ColumnType = columnsTypes[i] }; var reportColumnDefinition = new REPORTCOLUMNDEFINITION(); reportColumnDefinition.COLUMN_INDEX = i; reportColumnDefinition.COLUMN_NAME = columnsEn[i]; reportColumnDefinition.COLUMN_TYPE = columnsTypes[i] == "number" ? "decimal" : null; reportColumnDefinition.HEADER_TEXT_CN = columnsCn[i]; reportColumnDefinition.HEADER_TEXT_EN = columnsEn[i]; reportColumnDefinition.REPORT_ID = reportdefinition.ID; reportColumnDefinitions.Add(reportColumnDefinition); //System.InvalidOperationException: 表/视图没有定义主键。实体为只读 //cmadb.REPORTCOLUMNDEFINITIONs.Add(reportColumnDefinition); } } } //Console.WriteLine(cmadb.SaveChanges()); } foreach (var reportcolumndefinition in reportColumnDefinitions) { //Console.WriteLine(JsonConvert.SerializeObject(reportcolumndefinition)); Console.WriteLine("INSERT INTO REPORTCOLUMNDEFINITION (REPORT_ID,COLUMN_INDEX,COLUMN_NAME,COLUMN_TYPE,HEADER_TEXT_CN,HEADER_TEXT_EN) " + "VALUES({0},{1},'{2}','{3}',N'{4}','{5}');", reportcolumndefinition.REPORT_ID, reportcolumndefinition.COLUMN_INDEX, reportcolumndefinition.COLUMN_NAME, reportcolumndefinition.COLUMN_TYPE, reportcolumndefinition.HEADER_TEXT_CN, reportcolumndefinition.HEADER_TEXT_EN); } } }
public static CoalFilterItem GetSubDropdownByFilterId(decimal filterid, string selectedPrimaryItem) { COALFILTER filter; using (var db = new CMAEntities()) { filter = db.COALFILTERs.FirstOrDefault(o => o.ID == filterid); } var res = new CoalFilterItem(); res.FilterId = Convert.ToInt32(filterid); res.Name_CN = filter.NAME_CN; res.Name_EN = filter.NAME_EN; res.FieldName = filter.FIELDNAME; //20141027 yy Localization var filterSql = filter.FILERSQL; if (IsEnglishCulture()) { filterSql = filter.FILERSQL_EN; } using (var zcxdb = new CNE_ZCXNewEntities()) { try { zcxdb.Database.Connection.Open(); var cmd = zcxdb.Database.Connection.CreateCommand(); cmd.CommandText = string.Format(filterSql, selectedPrimaryItem); var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var tempRes = new SelectListItem { Value = reader[0].ToString(), Text = reader[1].ToString() }; tempRes.Selected = (tempRes.Value == filter.DEFAULTARG); res.Items.Add(tempRes); } } reader.Close(); } finally { zcxdb.Database.Connection.Close(); } //use first item as default if have no default if (res.Items.Count > 0 && res.Items.Where(o => o.Selected).Count() == 0) { res.Items[0].Selected = true; } } return(res); }
/// <summary> /// Get the css style of the children of node with id /// </summary> /// <param name="id"></param> /// <returns></returns> public IEnumerable<VAV.Model.Data.MenuNodeLayout> GetMenuNodeLayout(int reportId) { using (var CMADB = new CMAEntities()) { return (from n in CMADB.MENUNODELAYOUTs where n.REPORT_ID == reportId select new VAV.Model.Data.MenuNodeLayout { ReportId = (int) n.REPORT_ID, NodeId = (int) n.NODE_ID, Style = n.DISPLAY_STYLE }).ToList(); } }
public void TestGetColumn() { using (var cmadb = new CMAEntities()) { var columns = cmadb.REPORTCOLUMNDEFINITIONs.ToList(); Console.WriteLine(columns.Count(x => x.REPORT_ID == 300100)); foreach (var column in columns) { Console.WriteLine(JsonConvert.SerializeObject(column)); } } }
public DataTable GetHomeItems(int moduleId, string itemName, int startPage, int pageSize, out int total) { var paramArray = new[] { new OracleParameter("P_ModuleId", OracleDbType.Int32) { Value = moduleId }, new OracleParameter("P_ItemName", OracleDbType.Varchar2) { Value = itemName }, new OracleParameter("P_StartPage", OracleDbType.Int32) { Value = startPage }, new OracleParameter("P_PageSize", OracleDbType.Int32) { Value = pageSize }, new OracleParameter("P_Total", OracleDbType.Int32, ParameterDirection.Output), new OracleParameter("P_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; using (var cmaDb = new CMAEntities()) { using (OracleCommand spCmd = new OracleCommand()) { cmaDb.Database.Connection.Open(); spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString); spCmd.CommandText = "GetHomeItems"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paramArray); OracleDataAdapter da = new OracleDataAdapter(spCmd); var ds = new DataSet(); da.Fill(ds); object value; value = spCmd.Parameters["P_Total"].Value; total = value.ToString() != "null" ? Convert.ToInt32(value.ToString()) : 0; return(ds.Tables[0]); } } }
/// <summary> /// Get extra header information by report id. /// </summary> /// <param name="reportId"></param> /// <returns></returns> public IEnumerable <ExtraHeader> GetExtraHeaderById(int reportId) { using (var cmadb = new CMAEntities()) { return(from h in cmadb.REPORTEXTRAHEADERDEFINITIONs where h.REPORT_ID == reportId select new ExtraHeader { HeaderLevel = (int)h.EXTRA_HEADER_LEVEL, HeaderTextCN = h.EXTRA_HEADER_TEXT_CN, HeaderTextEN = h.EXTRA_HEADER_TEXT_EN, HeaderColumnSpan = (int)h.EXTRA_HEADER_COLUMN_SPAN }); } }
public MenuNode GetMenuById(int id) { using (var CMADB = new CMAEntities()) { return CMADB.MENUs.Where(m => m.ID == id).ToList().Select(n => new MenuNode(n.ENGLISH_NAME, n.CHINESE_NAME, (int)n.ID, (int)n.PARENT_ID, (int)(n.GROUP_TYPE ?? -1), (int)(n.REPORT_ID ?? -1), (int)(n.NODEORDER ?? 0), (int)(n.ACTIONTYPE ?? 0), (int)n.ISTREEVISIBLE, n.RIC)).FirstOrDefault(); } }
/// <summary> /// Get report information by report id. /// </summary> /// <returns></returns> public IEnumerable <ReportInfo> GetReportInfo() { using (var CMADB = new CMAEntities()) { return(((from r in CMADB.REPORTDEFINITIONs select r) .AsEnumerable() .Select(n => new ReportInfo( n.VIEW_NAME, n.VIEWMODEL_NAME, (int)n.ID, n.REPORT_TYPE, n.ENGLISH_NAME, n.CHINESE_NAME, n.TABLE_NAME, n.COLUMN_LIST ))).ToList()); } }
/// <summary> /// Get report infomation by report id. /// </summary> /// <param name="id"></param> /// <returns></returns> public ReportInfo GetReportInfoById(int id) { using (var cmadb = new CMAEntities()) { return(((from r in cmadb.REPORTDEFINITIONs where r.ID == id select r) .AsEnumerable() .Select(n => new ReportInfo( n.VIEW_NAME, n.VIEWMODEL_NAME, (int)n.ID, n.REPORT_TYPE, n.ENGLISH_NAME, n.CHINESE_NAME, n.TABLE_NAME ))).ToList().FirstOrDefault()); } }
public CoalChartLegendDisplay GetChartLegendByReportId(int reportId) { COALCHARTLEGEND legend; using (var db = new CMAEntities()) { db.Database.Connection.Open(); try { legend = db.COALCHARTLEGENDs.FirstOrDefault(x => x.REPORTID == reportId); } finally { db.Database.Connection.Close(); } } var chartDisplay = new CoalChartLegendDisplay(Convert.ToInt32(legend.REPORTID), legend.CHARTSQL, legend.UNIT, legend.LEGEND, legend.CHARTYLABEL_CN, legend.CHARTYLABEL_EN, legend.CHARTTITLE_CN, legend.CHARTTITLE_EN); return(chartDisplay); }
public DataSet GetDataSetBySpFromCma(string inName, params OracleParameter[] inParams) { using (var vav = new CMAEntities()) { using (var spCmd = new OracleCommand()) { vav.Database.Connection.Open(); spCmd.Connection = (OracleConnection)(vav.Database.Connection); spCmd.CommandText = inName; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; if (inParams != null) { spCmd.Parameters.AddRange(inParams); } var da = new OracleDataAdapter(spCmd); var ds = new DataSet(); da.Fill(ds); return(ds); } } }
public int DeleteHomeItem(int ID) { var paramArray = new[] { new OracleParameter("P_ID", OracleDbType.Int32) { Value = ID } }; using (var cmaDb = new CMAEntities()) { cmaDb.Database.Connection.Open(); DbCommand spCmd = cmaDb.Database.Connection.CreateCommand(); spCmd.CommandText = "DeleteHomeItemByID"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paramArray); return(spCmd.ExecuteNonQuery()); } }
/// <summary> /// Get the list of menu node /// </summary> /// <returns>List<MenuNode/></returns> public IEnumerable<MenuNode> GetMenuNode() { IEnumerable<MenuNode> nodes; using (var CMADB = new CMAEntities()) { nodes = ((from r in CMADB.MENUs where r.ISVISIBLE.Value == 1 select r) .AsEnumerable() .Select(n => new MenuNode( n.ENGLISH_NAME, n.CHINESE_NAME, (int) n.ID, (int) n.PARENT_ID, (int) (n.GROUP_TYPE ?? -1), (int) (n.REPORT_ID ?? -1), (int) (n.NODEORDER ?? 0), (int) (n.ACTIONTYPE ?? 0), (int) n.ISTREEVISIBLE, n.RIC ))).OrderBy(re => re.ParentId).ThenBy(n => n.NodeOrder).ThenBy(re => re.Id).ToList(); } return nodes; }
public void UpdateUserColumns(string userID, int reportID, string settingName, string columnList) { using (var cmaDB = new CMAEntities()) { var userSetting = cmaDB.USERCOLUMNSETTINGS.FirstOrDefault( x => x.USERID == userID && x.REPORTID == reportID && x.SETTINGNAME == settingName); if (userSetting == null) { userSetting = new USERCOLUMNSETTING { USERID = userID, REPORTID = reportID, SETTINGNAME = settingName, COLUMNLIST = columnList }; cmaDB.USERCOLUMNSETTINGS.Add(userSetting); } else { userSetting.COLUMNLIST = columnList; } cmaDB.SaveChanges(); } }
public void UploadFile(int id, string moduleId, string titleCn, string titleEn, string descrCn, string descrEn, string uploadType, string uploadTypeValue, string typeParam, bool isvalid, string submitter, DateTime submittDate, string fileType, byte[] doc) { var paramArray = new[] { new OracleParameter("P_Id", OracleDbType.Int32) { Value = id }, new OracleParameter("P_ModuleId", OracleDbType.Int32) { Value = Convert.ToInt32(moduleId) }, new OracleParameter("P_TitleCn", OracleDbType.NVarchar2) { Value = titleCn }, new OracleParameter("P_TitleEn", OracleDbType.NVarchar2) { Value = titleEn }, new OracleParameter("P_SubmitDate", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)submittDate }, new OracleParameter("P_Submitter", OracleDbType.NVarchar2) { Value = submitter }, new OracleParameter("P_UploadType", OracleDbType.Varchar2) { Value = uploadType }, new OracleParameter("P_UploadTypeValue", OracleDbType.NVarchar2) { Value = uploadTypeValue }, new OracleParameter("P_TypeParam", OracleDbType.NVarchar2) { Value = typeParam }, new OracleParameter("P_Isvalid", OracleDbType.Int32) { Value = isvalid?1:0 }, new OracleParameter("P_FileType", OracleDbType.NVarchar2) { Value = fileType }, new OracleParameter("P_OutPara", OracleDbType.Int32, ParameterDirection.Output) { Value = 0 }, new OracleParameter("P_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; string fileId = ""; using (var cmaDb = new CMAEntities()) { cmaDb.Database.Connection.Open(); DbCommand spCmd = cmaDb.Database.Connection.CreateCommand(); spCmd.CommandText = "UploadHomeItem"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paramArray); spCmd.ExecuteNonQuery(); fileId = spCmd.Parameters["P_OutPara"].Value.ToString(); } if (!string.IsNullOrEmpty(fileId) && !string.IsNullOrEmpty(fileType)) { FileService.UploadFile("/VAV/Home", fileId + "." + fileType, doc); } }
public DataTable GetPriceChartTable(int itemId, string key, string term, string reDate, int isExport) { DateTime start; DateTime end = Convert.ToDateTime(reDate); switch (term) { case "1D": start = end.Date; end = end.Date.AddHours(23).AddMinutes(59).AddSeconds(59); break; case "1M": start = end.AddMonths(-1); break; case "3M": start = end.AddMonths(-3); break; case "6M": start = end.AddMonths(-6); break; case "1Y": start = end.AddYears(-1); break; case "5Y": start = end.AddYears(-5); break; default: start = end.AddYears(-50); break; } string chartSp; using (var cma = new CMAEntities()) { chartSp = cma.ITEMDEFINITIONs.First(re => re.ITEMID == itemId).CHARTSP1; } var paramArray = new[] { new OracleParameter("P_chartSp", OracleDbType.NVarchar2, 50) { Value = chartSp }, new OracleParameter("P_key", OracleDbType.NVarchar2, 50) { Value = key }, new OracleParameter("P_start", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)start }, new OracleParameter("P_end", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)end }, new OracleParameter("P_isExport", OracleDbType.Int32) { Value = isExport }, new OracleParameter("P_avg", OracleDbType.Double) { Value = 0, Direction = ParameterDirection.Output }, new OracleParameter("P_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; var table = GetDataSetBySp("GDTGetPriceData", paramArray).Tables[0]; return(table); }
public DataTable GetChinaSecurities(string category, string title, string code, DateTime startDate, DateTime endDate, out int total, int startPage = 1, int pageSize = 50) { var paramArray = new[] { new OracleParameter("P_Category", OracleDbType.Varchar2) { Value = category }, new OracleParameter("P_Title", OracleDbType.Varchar2) { Value = title }, new OracleParameter("P_Code", OracleDbType.Varchar2) { Value = code }, new OracleParameter("P_StartDate", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)startDate }, new OracleParameter("P_EndDate", OracleDbType.TimeStamp) { Value = (OracleTimeStamp)endDate }, 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 CMAEntities()) { using (OracleCommand spCmd = new OracleCommand()) { cmaDb.Database.Connection.Open(); spCmd.Connection = new OracleConnection(cmaDb.Database.Connection.ConnectionString); spCmd.CommandText = "GetChinaSecurities"; 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]); } } }
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 }); }