private Hashtable executeQuery(String sql) { var result = new Hashtable(); try { // auto close using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (var command = conn.CreateCommand()) { command.CommandText = sql; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var expenditure = new EmExpediture(); result.Add(reader.GetString(1), reader.GetString(0)); } } } } } } catch (MySqlException e) { Console.WriteLine("ERROR: " + e.Message); } return(result); }
public List <EmExpediture> GetEmExpediture() { StringBuilder builder = new StringBuilder(); builder.AppendLine("select") .AppendLine(" DPT.Name as Department") .AppendLine(" ,LOC.Name as Location") .AppendLine(" ,SUM(ITEM.Amount * ITEM.UnitPrice) as Expenditure") .AppendLine(" ,DATE_FORMAT(EM.EMRequestDate, '%Y-%m') as RequestMonth") .AppendLine("from") .AppendLine(" orders") .AppendLine("inner join emergencymaintenances EM") .AppendLine(" on EM.ID = orders.EmergencyMaintenancesID") .AppendLine(" and EM.EMEndDate is not null") .AppendLine("inner join OrderItems ITEM") .AppendLine(" on ITEM.OrderID = orders.ID") .AppendLine("inner join assets") .AppendLine(" on assets.ID = EM.AssetID") .AppendLine("inner join departmentlocations DL") .AppendLine(" on DL.ID = assets.DepartmentLocationID") .AppendLine("inner join departments DPT") .AppendLine(" on DPT.ID = DL.DepartmentID") .AppendLine("inner join Locations LOC") .AppendLine(" on LOC.ID = DL.LocationID") .AppendLine("group by RequestMonth") .AppendLine("order by RequestMonth desc"); string sql = builder.ToString(); var results = new List <EmExpediture>(); try { // auto close using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (var command = conn.CreateCommand()) { command.CommandText = sql; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var expenditure = new EmExpediture(); expenditure.Department = reader.GetString(0); expenditure.Location = reader.GetString(1); expenditure.Expenditure = reader.GetInt32(2); expenditure.RequestMonth = reader.GetString(3); results.Add(expenditure); } } } } } } catch (MySqlException e) { Console.WriteLine("ERROR: " + e.Message); } return(results); }
/// <summary> /// 月ごとに最もコストのかかっているアセット /// </summary> /// <returns></returns> public List <Hashtable> GetMostCostlyAssets() { StringBuilder builder = new StringBuilder(); builder.AppendLine("select") .AppendLine(" T1.AssetName") .AppendLine(" , T1.DepartmentName") .AppendLine(" , max(T1.expenditure)") .AppendLine(" , T1.RequestMonth") .AppendLine("from") .AppendLine("(select") .AppendLine(" assets.AssetName as AssetName") .AppendLine(" ,DPT.Name as DepartmentName") .AppendLine(" ,SUM(ITEM.Amount * ITEM.UnitPrice) as expenditure") .AppendLine(" ,DATE_FORMAT(EM.EMRequestDate, '%Y-%m') as RequestMonth") .AppendLine("from") .AppendLine(" orders") .AppendLine("inner join emergencymaintenances EM ") .AppendLine(" on EM.ID = orders.EmergencyMaintenancesID") .AppendLine(" and EM.EMEndDate is not null") .AppendLine("inner join OrderItems ITEM") .AppendLine(" on ITEM.OrderID = orders.ID ") .AppendLine("inner join assets") .AppendLine(" on assets.ID = EM.AssetID") .AppendLine("inner join departmentlocations DL") .AppendLine(" on DL.ID = assets.DepartmentLocationID") .AppendLine("inner join departments DPT") .AppendLine(" on DPT.ID = DL.DepartmentID") .AppendLine("group by RequestMonth, EM.AssetID") .AppendLine("order by RequestMonth desc, expenditure desc) as T1") .AppendLine("group by T1.RequestMonth"); String sql = builder.ToString(); var results = new List <Hashtable>(); results.Add(new Hashtable()); results.Add(new Hashtable()); try { // auto close using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (var command = conn.CreateCommand()) { command.CommandText = sql; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var expenditure = new EmExpediture(); results[0].Add(reader.GetString(3), reader.GetString(0)); results[1].Add(reader.GetString(3), reader.GetString(1)); } } } } } } catch (MySqlException e) { Console.WriteLine("ERROR: " + e.Message); } return(results); }