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