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