public List <PartitionBallanceTableData> GetPartitionBallenceData(string areaCode, string productCode, List <string> years) { var ds = new DataTable(); var paras = new[] { new OracleParameter("P_AreaCode", areaCode), new OracleParameter("P_productCode", productCode), new OracleParameter("P_myear1", years[2]), new OracleParameter("P_myear2", years[1]), new OracleParameter("P_myear3", years[0]), new OracleParameter("P_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; using (var zcxDb = new CNE_ZCXNewEntities()) { using (var spCmd = new OracleCommand()) { zcxDb.Database.Connection.Open(); spCmd.Connection = (OracleConnection)zcxDb.Database.Connection; spCmd.CommandText = "GetPartitionBallenceData"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paras); var da = new OracleDataAdapter(spCmd); da.Fill(ds); } } return(DataTableSerializer.ToList <PartitionBallanceTableData>(ds)); }
public List <BallanceFilter> GetAreaFilters(int reportId, string productCode) { var ds = new DataTable(); var paras = new[] { new OracleParameter("P_reportID", reportId), new OracleParameter("P_ProductCode", productCode), new OracleParameter("P_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; using (var zcxDb = new CNE_ZCXNewEntities()) { using (var spCmd = new OracleCommand()) { zcxDb.Database.Connection.Open(); spCmd.Connection = (OracleConnection)zcxDb.Database.Connection; spCmd.CommandText = "GetAreaFiltes"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paras); var da = new OracleDataAdapter(spCmd); da.Fill(ds); } } return(DataTableSerializer.ToList <BallanceFilter>(ds)); }
public static DataTable GetCoalChartDataTable(int reportId, string key) { //check params if (string.IsNullOrEmpty(key)) { return(new DataTable()); } COALCHARTLEGEND chartL; using (var db = new CMAEntities()) { chartL = db.COALCHARTLEGENDs.FirstOrDefault(o => o.REPORTID == reportId); } var sqlWhere = key.Replace("^|^", "=").Replace("||", " AND "); var sqlStr = string.Format(chartL.CHARTSQL, sqlWhere); var ds = new DataSet(); using (var zcxdb = new CNE_ZCXNewEntities()) { using (var adp = new OracleDataAdapter(sqlStr, zcxdb.Database.Connection.ConnectionString)) { adp.Fill(ds); } } return(ds.Tables[0]); }
public static List <CoalTrafficPortChartEntity> GetCoalTrafficPortData(string key) { const string proName = "CoalTrafficPort_ChartData"; var paras = new[] { new OracleParameter("P_key", key), new OracleParameter("P_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; var chartTable = new DataTable(); using (var cnE = new CNE_ZCXNewEntities()) { using (var spCmd = new OracleCommand()) { cnE.Database.Connection.Open(); spCmd.Connection = (OracleConnection)(cnE.Database.Connection); spCmd.CommandText = proName; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paras); var da = new OracleDataAdapter(spCmd); da.Fill(chartTable); } } return(DataTableSerializer.ToList <CoalTrafficPortChartEntity>(chartTable)); }
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); }
public List <string> GetBallanceColumnNames(int reportId, string areacode, string productCode) { var ds = new DataTable(); using (var zcxDb = new CNE_ZCXNewEntities()) { using (var spCmd = new OracleCommand()) { string str = "select distinct MARKET_YEAR from GetBalanceTableData where length(MARKET_YEAR)=9 "; if (!string.IsNullOrEmpty(areacode)) { str += " and areacode=" + areacode; } if (!string.IsNullOrEmpty(productCode)) { str += " and productCode=" + productCode; } str += " and reportid=" + reportId + " order by MARKET_YEAR desc"; zcxDb.Database.Connection.Open(); spCmd.Connection = (OracleConnection)zcxDb.Database.Connection; spCmd.CommandText = str; spCmd.CommandTimeout = 0; var da = new OracleDataAdapter(spCmd); da.Fill(ds); } } var columnNames = new List <string>(); var rowItor = ds.Rows.GetEnumerator(); while (rowItor.MoveNext()) { string years = ((DataRow)rowItor.Current)[0].ToString(); int startYear = Convert.ToInt32(years.Substring(0, 4)); int endYear = Convert.ToInt32(years.Substring(5, 4)); if (endYear == startYear + 1) { columnNames.Add(years); } } if (columnNames.Count == 0) { columnNames.Add((DateTime.Now.Year - 1) + "/" + DateTime.Now.Year); columnNames.Add((DateTime.Now.Year - 2) + "/" + (DateTime.Now.Year - 1)); columnNames.Add((DateTime.Now.Year - 3) + "/" + (DateTime.Now.Year - 2)); } return(columnNames); }
private List <string> GetCategoryIdWhichHasData(string tableName, string categoryName) { string proName = "select distinct " + categoryName + " from " + tableName; var dt = new DataTable(); using (var cnE = new CNE_ZCXNewEntities()) { using (var spCmd = new OracleCommand()) { cnE.Database.Connection.Open(); spCmd.Connection = (OracleConnection)(cnE.Database.Connection); spCmd.CommandText = proName; spCmd.CommandType = CommandType.Text; spCmd.CommandTimeout = 0; var da = new OracleDataAdapter(spCmd); da.Fill(dt); } } return((from DataRow dr in dt.Rows select dr[0] == null ? "" : dr[0].ToString()).ToList()); }
public DataTable GetPagedTableData(IEnumerable <REPORTCOLUMNDEFINITION> columns, string tableName, string strOrder, string strWhere, int pageIndex, out int recordCount) { var strGetFields = columns.Select(x => x.COLUMN_NAME).Aggregate((a, b) => a + "," + string.Format("{0}", b)); if (string.IsNullOrEmpty(tableName)) { recordCount = 0; return(new DataTable()); } else//20141027 yy Localization { if (IsEnglishCulture()) { tableName = tableName + "_EN"; } } if (string.IsNullOrEmpty(strOrder)) { strOrder = "parcode"; } var paramArray = new[] { new OracleParameter("tblName", OracleDbType.NVarchar2) { Value = tableName }, new OracleParameter("strGetFields", OracleDbType.NVarchar2) { Value = strGetFields }, new OracleParameter("strOrder", OracleDbType.NVarchar2) { Value = strOrder }, new OracleParameter("strWhere", OracleDbType.NVarchar2) { Value = strWhere }, new OracleParameter("pageIndex", OracleDbType.Int32) { Value = pageIndex }, new OracleParameter("recordCount", OracleDbType.Int32, ParameterDirection.Output) { Value = 0 }, new OracleParameter("O_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; using (var cnEdb = new CNE_ZCXNewEntities()) { using (var spCmd = new OracleCommand()) { cnEdb.Database.Connection.Open(); spCmd.Connection = (OracleConnection)(cnEdb.Database.Connection); spCmd.CommandText = "GetDataPaged"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paramArray); var da = new OracleDataAdapter(spCmd); var ds = new DataSet(); da.Fill(ds); recordCount = Convert.ToInt32(spCmd.Parameters["recordCount"].Value.ToString()); return(ds.Tables[0]); } } }
public static DataTable GetCoalPortSchedularPagedData(string tableName, string strGetFields, string strOrder, string strWhere, int pageIndex, int doCount, int pageSize, out int recordCount) { var paramArray = new[] { new OracleParameter("tblName", OracleDbType.NVarchar2) { Value = tableName }, new OracleParameter("strGetFields", OracleDbType.NVarchar2) { Value = strGetFields }, new OracleParameter("strOrder", OracleDbType.NVarchar2) { Value = strOrder }, new OracleParameter("strWhere", OracleDbType.NVarchar2) { Value = strWhere }, new OracleParameter("pageIndex", OracleDbType.Int32) { Value = pageIndex }, new OracleParameter("pageSize", OracleDbType.Int32) { Value = pageSize }, new OracleParameter("recordCount", OracleDbType.Int32, ParameterDirection.Output) { Value = pageSize }, new OracleParameter("doCount", OracleDbType.Int32) { Value = doCount }, new OracleParameter("isExcelReport", OracleDbType.Int32) { Value = 0 }, new OracleParameter("O_CUR", OracleDbType.RefCursor) { Direction = ParameterDirection.Output } }; using (var cnEdb = new CNE_ZCXNewEntities()) { using (var spCmd = new OracleCommand()) { cnEdb.Database.Connection.Open(); spCmd.Connection = (OracleConnection)(cnEdb.Database.Connection); spCmd.CommandText = "GetDataPagedWithExportExcel"; spCmd.CommandType = CommandType.StoredProcedure; spCmd.CommandTimeout = 0; spCmd.Parameters.AddRange(paramArray); var da = new OracleDataAdapter(spCmd); var ds = new DataSet(); da.Fill(ds); recordCount = Convert.ToInt32(spCmd.Parameters["recordCount"].Value.ToString()); return(ds.Tables[0]); } } }