public List <Tuple <DateTime, decimal, decimal> > GetSDLocalRefineryExcelIndex()
{
var data = new List <Tuple <DateTime, decimal, decimal> >();
using (var db = new CneNewEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "select a.redate,a.gasoline as gasoline,b.dieselindex as Diesel from GasolineIndex a join DieselIndex b on a.redate = b.redate order by a.ReDate";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
data.Add(new Tuple <DateTime, decimal, decimal>(reader.GetDateTime(0), reader.GetDecimal(1), reader.GetDecimal(2)));
}
}
reader.Close();
}
finally
{
db.Database.Connection.Close();
}
}
return(data);
}
/// <summary>
/// Get data by store procedure
/// </summary>
/// <param name="inName">sp name</param>
/// <param name="inParms">parameters</param>
/// <returns></returns>
protected DataSet GetDataSetBySp(string inName, OracleParameter[] inParms)
{
using (var cnEDB = new CneNewEntities())
{
using (OracleCommand spCmd = new OracleCommand())
{
DataSet ds = null;
cnEDB.Database.Connection.Open();
spCmd.Connection = new OracleConnection(cnEDB.Database.Connection.ConnectionString);
spCmd.CommandText = inName;
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.CommandTimeout = 0;
if (inParms != null)
{
spCmd.Parameters.AddRange(inParms);
}
OracleDataAdapter da = new OracleDataAdapter(spCmd);
ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
}
public List <Tuple <DateTime, double, double, double, double> > GetSDLocalRefineryExcelStock()
{
var oil = new List <Tuple <DateTime, double, double, double, double> >();
using (var db = new CneNewEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "select ReDate,GasolineStock,DieselStock,nvl(GasolineCapacityRate,0) GasolineCapacityRate,nvl(DieselCapacityRate,0) DieselCapacityRate from TotalStock order by ReDate";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var pointline = new Tuple <DateTime, double, double, double, double>(reader.GetDateTime(0), reader.GetDouble(1), reader.GetDouble(2), reader.GetDouble(3) * 100, reader.GetDouble(4) * 100);
oil.Add(pointline);
}
}
reader.Close();
}
finally
{
db.Database.Connection.Close();
}
}
return(new List <Tuple <DateTime, double, double, double, double> >(oil));
}
/// <summary>
/// Non-query store procedure
/// </summary>
/// <param name="inName">sp name</param>
/// <param name="inParms">parameters</param>
protected void ExecNonQuerySp(string inName, OracleParameter[] inParms)
{
using (var cnEDB = new CneNewEntities())
{
cnEDB.Database.Connection.Open();
DbCommand cmd = cnEDB.Database.Connection.CreateCommand();
cmd.CommandText = inName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.Parameters.AddRange(inParms);
cmd.ExecuteNonQuery();
}
}
public DataTable GetEnergyInvntoryData(string tableName, string columns, string order, string filter = "1=1")
{
using (var cnE = new CneNewEntities())
{
using (var spCmd = new OracleCommand())
{
var ds = new DataSet();
cnE.Database.Connection.Open();
spCmd.Connection = (OracleConnection)cnE.Database.Connection;
spCmd.CommandText = "SELECT " + columns + " FROM " + tableName + " where " + filter + " order by " + order;
spCmd.CommandTimeout = 0;
var da = new OracleDataAdapter(spCmd);
da.Fill(ds);
return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
}
}
}
public object GetSDLocalRefineryValuation()
{
var gasoline = new List <object>();
var diesel = new List <object>();
using (var db = new CneNewEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "select a.PriceDate,a.Price as gasoline,b.Price as Diesel from GasolineValuation a join DieselValuation b on a.PriceDate = b.PriceDate where a.PriceDate>sysdate-365 order by a.PriceDate";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0));
var pointGasoline = new List <object>
{
date.TotalMilliseconds,
reader.GetDecimal(1)
};
gasoline.Add(pointGasoline);
var pointDiesel = new List <object>
{
date.TotalMilliseconds,
reader.GetDecimal(2)
};
diesel.Add(pointDiesel);
}
}
reader.Close();
}
finally
{
db.Database.Connection.Close();
}
}
return(new { gasoline, diesel });
}
public DataTable GetSdLocalRefineryCompany(bool isEnglish)
{
var dt = new DataTable();
string sql = isEnglish ? "SELECT DISTINCT Code,ItemName_EN as ItemName FROM View_DeviceStatisticsMap ORDER BY Code" : "SELECT DISTINCT Code,ItemName_CN as ItemName FROM View_DeviceStatisticsMap ORDER BY Code";
using (var db = new CneNewEntities())
{
var cmd = new OracleCommand
{
Connection = (OracleConnection)(db.Database.Connection),
CommandText = sql
};
var da = new OracleDataAdapter(cmd);
da.Fill(dt);
db.Database.Connection.Close();
}
return(dt);
}
public DataTable GetSdLocalRefineryDeviceInfoTable(bool isEnglish, string code, string reDate)
{
var dt = new DataTable();
string sql = isEnglish ? "SELECT code,Device_EN as Device,YieldByTon,YieldByBarrel FROM View_DeviceStatisticsMap where Code='" + code + "'" : "SELECT code,Device,YieldByTon,YieldByBarrel FROM View_DeviceStatisticsMap where Code='" + code + "' order by row_num";
using (var db = new CneNewEntities())
{
var cmd = new OracleCommand
{
Connection = (OracleConnection)(db.Database.Connection),
CommandText = sql
};
var da = new OracleDataAdapter(cmd);
da.Fill(dt);
db.Database.Connection.Close();
}
return(dt);
}
public Tuple <List <Tuple <DateTime, double, double> >, List <Tuple <DateTime, double> > > GetSDLocalRefinerExcelData()
{
var oil = new List <Tuple <DateTime, double, double> >();
var operating = new List <Tuple <DateTime, double> >();
using (var db = new CneNewEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "select Month,Gasoline,Diesel from MonthProduction order by Month";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var pointline = new Tuple <DateTime, double, double>(reader.GetDateTime(0), reader.GetDouble(1), reader.GetDouble(2));
oil.Add(pointline);
}
}
// Move to second result set and read Posts
cmd.CommandText = "select ReDate,OperatingRate from TotalOperatingRate order by ReDate";
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var pointOperating = new Tuple <DateTime, double>(reader.GetDateTime(0), reader.GetDouble(1));
operating.Add(pointOperating);
}
}
reader.Close();
}
finally
{
db.Database.Connection.Close();
}
}
return(new Tuple <List <Tuple <DateTime, double, double> >, List <Tuple <DateTime, double> > >(oil, operating));
}
public DataTable GetSdLocalRefineryDailyOutputTable(DateTime start, DateTime end, string code, out string reDate)
{
using (var db = new CneNewEntities())
{
var connection = (OracleConnection)(db.Database.Connection);
connection.Open();
var cmd = new OracleCommand
{
Connection = connection,
CommandText =
"SELECT max(ReDate)FROM View_DeviceStatisticsMap"
};
var obj = cmd.ExecuteScalar();
if (obj == DBNull.Value)
{
reDate = "";
}
else
{
reDate = Convert.ToDateTime(obj).ToString("yyyy-MM-dd");
}
connection.Close();
}
var dt = new DataTable();
using (var db = new CneNewEntities())
{
var cmd = new OracleCommand
{
Connection = (OracleConnection)(db.Database.Connection),
CommandText =
"SELECT ReDate,ProcessCapacity,Gasoline,Diesel FROM DayProduction where Code='" + code + "' and ReDate>='" + start.ToString("dd-MMM-yyyy") + "' and ReDate<='" + end.ToString("dd-MMM-yyyy") + "' order by ReDate desc"
};
var da = new OracleDataAdapter(cmd);
da.Fill(dt);
db.Database.Connection.Close();
}
return(dt);
}
public new DataSet GetDataSetBySp(string inName, params OracleParameter[] inParams)
{
using (var cnE = new CneNewEntities())
{
using (var spCmd = new OracleCommand())
{
cnE.Database.Connection.Open();
spCmd.Connection = (OracleConnection)(cnE.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 DataTable GetDataPaged(string tableName, string strGetFields, string strOrder, string strWhere, int pageIndex, int pageSize, int doCount, int isExcel, 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 = isExcel
},
new OracleParameter("O_CUR", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
}
};
using (var cnEDB = new CneNewEntities())
{
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 object GetSDLocalRefineryChartData()
{
var gasoline = new List <object>();
var diesel = new List <object>();
var operating = new List <object>();
using (var db = new CneNewEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "select Month,Gasoline,Diesel from MonthProduction order by Month";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0));
var pointGasoline = new List <object>
{
date.TotalMilliseconds,
reader.GetDouble(1)
};
gasoline.Add(pointGasoline);
var pointDiesel = new List <object>
{
date.TotalMilliseconds,
reader.GetDouble(2)
};
diesel.Add(pointDiesel);
}
}
// Move to second result set and read Posts
cmd.CommandText = "select ReDate,OperatingRate from TotalOperatingRate order by ReDate";
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0));
var pointOperating = new List <object>
{
date.TotalMilliseconds,
reader.GetDouble(1) * 100
};
operating.Add(pointOperating);
}
}
reader.Close();
}
finally
{
db.Database.Connection.Close();
}
}
return(new { gasoline, diesel, operating });
}
public object GetSDLocalRefineryStock()
{
var gasolineStock = new List <object>();
var dieselStock = new List <object>();
var gasolineRate = new List <object>();
var dieselRate = new List <object>();
using (var db = new CneNewEntities())
{
// Create a SQL command to execute the sproc
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "select ReDate,GasolineStock,DieselStock,GasolineCapacityRate,DieselCapacityRate from TotalStock order by ReDate";
try
{
db.Database.Connection.Open();
// Run the sproc
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var date = reader.GetDateTime(0).Subtract(new DateTime(1970, 1, 1, 0, 0, 0));
var pointGasoline = new List <object>
{
date.TotalMilliseconds,
reader.GetDouble(1)
};
gasolineStock.Add(pointGasoline);
var pointDiesel = new List <object>
{
date.TotalMilliseconds,
reader.GetDouble(2)
};
dieselStock.Add(pointDiesel);
if (reader[3] != DBNull.Value)
{
var pointGasolineRate = new List <object>
{
date.TotalMilliseconds,
reader.GetDouble(3) * 100
};
gasolineRate.Add(pointGasolineRate);
}
if (reader[4] != DBNull.Value)
{
var pointDieselRate = new List <object>
{
date.TotalMilliseconds,
reader.GetDouble(4) * 100
};
dieselRate.Add(pointDieselRate);
}
}
}
reader.Close();
}
finally
{
db.Database.Connection.Close();
}
}
return(new { gasolineStock, dieselStock, gasolineRate, dieselRate });
}
