protected override void RegisterTypes(IContainerRegistry containerRegistry)
{
SQLiteConnection connection = DatabaseConnectionHelper.GetDatebaseConnection();
containerRegistry.RegisterInstance <ICitiesRepository>(new CitiesRepository(connection));
containerRegistry.RegisterInstance <ICitiesService>(new CitiesService(connection));
containerRegistry.RegisterInstance <IWeatherService>(new WeatherService());
containerRegistry.RegisterForNavigation <NavigationPage>();
containerRegistry.RegisterForNavigation <MainPage, MainPageViewModel>();
containerRegistry.RegisterForNavigation <CurrentWeatherPage, CurrentWeatherPageViewModel>();
containerRegistry.RegisterForNavigation <ForecastPage, ForecastPageViewModel>();
containerRegistry.RegisterForNavigation <SettingsPage, SettingsPageViewModel>();
}
public HttpResponseMessage GetItem()
{
var res = Request.CreateResponse(HttpStatusCode.OK); //通信成功
helper = new DatabaseConnectionHelper("SELECT DISTINCT 型番,部品名 FROM 型式;"); //製品一覧を引っ張ってくるSQL
//データをリストに格納するためのやーつ
string id;
string name;
List <Item> item = new List <Item>();
//データベースへ接続、結果を格納
SqlDataReader reader = helper.DatabaseConnect();
//データの取得に失敗した場合
if (reader == null)
{
res = Request.CreateResponse(HttpStatusCode.NotFound);
jsonString = "error";
res.Content = new StringContent(jsonString, Encoding.UTF8, "application/json");
return(res);
}
//ExamControllerを参照のこと
LItem wk = new LItem()
{
title = "製品一覧",
item = null
};
//データのセット
while (reader.Read())
{
id = (string)reader.GetValue(0);
name = (string)reader.GetValue(1);
item.Add(new Item(id, name));
}
wk.item = item;
reader.Close();
helper.closeDb();
jsonString = JsonConvert.SerializeObject(wk);
Debug.WriteLine(jsonString);
res.Content = new StringContent(jsonString, Encoding.UTF8, "application/json"); //Content-Typeを指定
return(res);
}
public HttpResponseMessage GetString()
{
var res = Request.CreateResponse(HttpStatusCode.OK); //コード200：通信成功
helper = new DatabaseConnectionHelper(
"SELECT 従業員コード,従業員名,入社日,従業員種別名 FROM 従業員,従業員種別 " +
"WHERE 従業員.従業員種別コード = 従業員種別.従業員種別コード;");
//データをリストに格納するためのやーつ
int workerId;
string name;
DateTime entryDate;
string workerType;
List <Worker> worker = new List <Worker>();
//データベースへ接続、結果を格納
//最初に空のリストを生成、セット可能とする
SqlDataReader reader = helper.DatabaseConnect();
LWorker wk = new LWorker()
{
title = "従業員",
worker = null
};
//データのセット
while (reader.Read())
{
workerId = int.Parse(reader.GetValue(0).ToString());
name = (string)reader.GetValue(1);
entryDate = DateTime.Parse(reader.GetValue(2).ToString());
workerType = reader.GetValue(3).ToString();
worker.Add(new Worker(workerId, name, entryDate, workerType));
}
wk.worker = worker;
reader.Close();
helper.closeDb();
jsonString = JsonConvert.SerializeObject(wk);
//Debug.WriteLine(jsonString);
//StringBuilderクラスのインスタンス//Content-Typeを指定する
res.Content = new StringContent(jsonString, Encoding.UTF8, "application/json"); //Content-Typeを指定
return(res);
}
private void SetValueCore(DatabaseManagement mgmt)
{
// synchronized insert
try
{
using (Descriptor.AcquireWriteLock())
using (var con = Descriptor.GetConnection())
using (var tr = con.BeginTransaction(DatabaseConnectionHelper.DefaultIsolationLevel))
{
con.Execute(this.TableInserter, mgmt);
tr.Commit();
}
}
catch (Exception ex)
{
throw DatabaseConnectionHelper.WrapException(ex, "SetValueCore", this.TableInserter);
}
}
//計測ポイント数を返却するメソッド
public static int getMeasureCount(string item)
{
DatabaseConnectionHelper helper = new DatabaseConnectionHelper(
"SELECT 計測ポイント数 FROM 型式 " +
"WHERE 型番 = '" + item + "';");
//製品の計測ポイント数…繰り返し数を引っ張る
//読み込めなかった場合は0を返す
SqlDataReader itemLenRead = helper.DatabaseConnect();
if (!itemLenRead.Read())
{
return(0);
}
else
{
return(int.Parse(itemLenRead.GetValue(0).ToString()));
}
}
public long GetId(string screenName)
{
// synchronized read
var sql = "select Id from " + TableName + " where LOWER(ScreenName) = @ScreenName limit 1;";
try
{
using (Descriptor.AcquireReadLock())
using (var con = Descriptor.GetConnection())
{
return(con.Query <long>(sql, new { ScreenName = screenName.ToLower() })
.SingleOrDefault());
}
}
catch (Exception ex)
{
throw DatabaseConnectionHelper.WrapException(ex, "GetId", sql);
}
}
private DatabaseManagement GetValueCore(long id)
{
// synchronized read
var sql = this.CreateSql("Id = @Id");
try
{
using (Descriptor.AcquireReadLock())
using (var con = Descriptor.GetConnection())
{
return(con.Query <DatabaseManagement>(sql, new { Id = id })
.SingleOrDefault());
}
}
catch (Exception ex)
{
throw DatabaseConnectionHelper.WrapException(ex, "GetValueCore", sql);
}
}
internal async Task VacuumAsync()
{
await Descriptor
.GetTaskFactory(true)
.StartNew(() =>
{
// should execute WITHOUT transaction.
try
{
using (Descriptor.AcquireWriteLock())
using (var con = Descriptor.GetConnection())
{
con.Execute("VACUUM;");
}
}
catch (Exception ex)
{
throw DatabaseConnectionHelper.WrapException(ex, "VacuumAsync", this.TableInserter);
}
});
}
public HttpResponseMessage GetData(int year, int month, int day, string item)
{
int measurePoints = 0;
string dateStr = year + "-" + month + "-" + day;
var res = Request.CreateResponse(HttpStatusCode.OK); //通信成功
//部品名指定…その部品の検査状況を返却するSQL発行
//部品名未指定…全部品の検査状況の（ry
if (item == "all")
{
sqlCommand = "select count(*) AS 生産数," +
"count(case when 判定 = 1 then 判定 else null end) AS 良品," +
"count(case when 判定 = 0 then 判定 else null end) AS 不良品 " +
"FROM 計測詳細 " +
"WHERE 計測日時 BETWEEN '" + dateStr + " 00:00:00' AND '" + dateStr + " 23:59:59';";
}
else
{
measurePoints = HistoryController.getMeasureCount(item);
sqlCommand = "select count(*) AS 生産数," +
"count(case when 判定 = 1 then 判定 else null end) AS 良品," +
"count(case when 判定 = 0 then 判定 else null end) AS 不良品 " +
"FROM 計測詳細 " +
"WHERE 型番 = '" + item + "' " +
"AND 計測日時 BETWEEN '" + dateStr + " 00:00:00' AND '" + dateStr + " 23:59:59';";
}
helper = new DatabaseConnectionHelper(sqlCommand);
int total, correct, failure; //検査数・良品・不良品
List <Monitor> monitor = new List <Monitor>();
//データベースへ接続、結果を格納
SqlDataReader reader = helper.DatabaseConnect();
//データの取得に失敗した場合
if (reader == null)
{
res = Request.CreateResponse(HttpStatusCode.NotFound);
jsonString = "error";
res.Content = new StringContent(jsonString, Encoding.UTF8, "application/json");
return(res);
}
LMonitor wk = new LMonitor()
{
title = "検査状況",
monitor = null
};
//データのセット
while (reader.Read())
{
total = Int32.Parse(reader.GetValue(0).ToString());
correct = Int32.Parse(reader.GetValue(1).ToString());
failure = Int32.Parse(reader.GetValue(2).ToString());
if (item != "all")
{
monitor.Add(new Monitor(total / measurePoints, correct / measurePoints, failur / measurePoints));
}
else
{
monitor.Add(new Monitor(total, correct, failure));
}
}
wk.monitor = monitor;
reader.Close();
helper.closeDb();
jsonString = JsonConvert.SerializeObject(wk);
Debug.WriteLine(jsonString);
res.Content = new StringContent(jsonString, Encoding.UTF8, "application/json");
//res = monitorDebugData(res);
return(res);
}
public HttpResponseMessage GetDatabase(int year, int month, int day, string item)
{
string jsonString = null;
var res = Request.CreateResponse(HttpStatusCode.OK); //コード200：通信成功
String dateParam = year + "-" + month + "-" + day.ToString("00");
DatabaseConnectionHelper helper = new DatabaseConnectionHelper();
//itemがallであるなら、全消去
if (item == "all")
{
helper.setSqlString("DELETE FROM 計測詳細;");
if (helper.DatabaseConnect("delete"))
{
jsonString = "successed.";
res.Content = new StringContent(jsonString, System.Text.Encoding.UTF8, "application/json");
Debug.WriteLine("Data deleted by Android.");
return(res);
}
else
{
jsonString = "failure.";
res = Request.CreateResponse(HttpStatusCode.BadRequest); //コード400：削除失敗
res.Content = new StringContent(jsonString, System.Text.Encoding.UTF8, "application/json");
return(res);
}
}
//リクエストが0000-00-00であったら、itemを全消去
if ((year + month + day) == 0)
{
helper.setSqlString("DELETE FROM 計測詳細 WHERE 型番 = '" + item + "';");
if (helper.DatabaseConnect("delete"))
{
jsonString = "successed.";
}
else
{
jsonString = "failure.";
res = Request.CreateResponse(HttpStatusCode.BadRequest); //コード400：削除失敗
}
}
//全消しではない場合
else
{
helper.setSqlString("DELETE FROM 計測詳細 " +
"WHERE 計測日時 BETWEEN '" + dateParam + " 00:00:00 ' AND '" + dateParam + " 23:59:59' " +
"AND 型番 = '" + item + "';");
if (helper.DatabaseConnect("delete"))
{
jsonString = "successed.";
}
else
{
jsonString = "failure.";
res = Request.CreateResponse(HttpStatusCode.BadRequest); //コード400：削除失敗
}
}
//StringBuilderクラスのインスタンス//Content-Typeを指定する
res.Content = new StringContent(jsonString, System.Text.Encoding.UTF8, "application/json"); //Content-Typeを指定
return(res);
}
public HttpResponseMessage GetString(int year, int month, int day, string item)
{
bool isExistData = false;
bool isEndOfData = false;
var res = Request.CreateResponse(HttpStatusCode.OK); //コード200：通信成功
//jsonString = "Date>>>" + year + "/" + month + "/" + day + "\nItem>>>" + item;
String dateParam = year + "-" + month + "-" + day.ToString("00");
String mainSql = //これに実際の検査記録を引っ張ってくるSQLを格納している
"SELECT 計測日時,シリアルNo,従業員名,加工機.加工機ID,計測ポイントID,正寸値.正寸値,計測詳細.計測値,計測詳細.判定 " +
"FROM 正寸値,計測詳細,型式,加工機,従業員,製造情報 " +
"WHERE 製造情報.型番 = 計測詳細.型番 " +
"AND 製造情報.製造順No = 計測詳細.製造順No " +
"AND 製造情報.従業員コード = 従業員.従業員コード " +
"AND 計測詳細.ロット位置 = 製造情報.ロットNo " +
"AND 製造情報.加工機ID = 加工機.加工機ID " +
"AND 計測ポイント = 正寸値.計測ポイントID " +
"AND 型式.型番 = 計測詳細.型番 " +
"AND 計測詳細.型番 = 正寸値.型番 " +
"AND 計測詳細.型番 = '" + item + "' " +
"AND 計測日時 BETWEEN '" + dateParam + " 00:00:00 ' AND '" + dateParam + " 23:59:59' " +
"ORDER BY 計測日時 ASC , シリアルNo ASC ,計測ポイントID ASC; ";
//offset...は特定の範囲のデータのみを取得する「SQL Server」専用のSQL文
DatabaseConnectionHelper helper = new DatabaseConnectionHelper(mainSql);
//計測ポイント数
int itemLen = getMeasureCount(item);
//データをリストに格納するためのやーつ
List <IHistory> iHistory = new List <IHistory>();
DateTime date;
string serial = "", name = "", dateStr = null;
int point, machineId = 0;
double correct, measure;
string result;
//データベースへ接続、結果を格納
SqlDataReader reader = helper.DatabaseConnect();
//データのセット
do
{
List <ErrorDetail> errList = new List <ErrorDetail>();
for (int i = 0; i < itemLen; i++)
{
dateStr = null;
if (reader.Read())
{
//IHistoryに入れるフィールドを先に取得する
date = (DateTime)reader.GetValue(0);
dateStr = date.ToString("yyyy-MM-dd HH:mm:ss");
serial = reader.GetValue(1).ToString();
name = reader.GetValue(2).ToString();
machineId = int.Parse(reader.GetValue(3).ToString());
//IHistoryのListに入れるErrorDetailを取得し格納
point = int.Parse(reader.GetValue(4).ToString());
correct = double.Parse(reader.GetValue(5).ToString());
measure = double.Parse(reader.GetValue(6).ToString());
bool isCorrect = (bool)reader.GetValue(7);
if (!isCorrect)
{
result = "NG";
}
else
{
result = "OK";
}
errList.Add(new ErrorDetail(point, correct, measure, result));
}
//計測ポイント数に到達せず、データが終了したらそこまでのデータを取得し格納する
else
{
//レコードが何か一つでもあったら
if (!(dateStr == null))
{
iHistory.Add(new IHistory(serial, dateStr, name, machineId, errList));
isExistData = true;
isEndOfData = true;
}
//データが1つもないなら
else
{
isEndOfData = true;
isExistData = false;
}
break; //forを抜ける
}
}//for
//データ終了でない場合は（全計測ポイントを収集できたら）
if (!isEndOfData)
{
try
{
iHistory.Add(new IHistory(serial, dateStr, name, machineId, errList));
}catch (OutOfMemoryException e)
{
iHistory = null;
errList = null;
GC.Collect();
isExistData = false;
break;
}
isExistData = true;
}
//データの終了フラグが立っていたらループを抜ける
} while (!isEndOfData);
LHistory wk = new LHistory()
{
title = "検査履歴",
history = iHistory
};
reader.Close();
helper.closeDb();
jsonString = JsonConvert.SerializeObject(wk);
Debug.WriteLine(jsonString);
//StringBuilderクラスのインスタンス//Content-Typeを指定する
res.Content = new StringContent(jsonString, Encoding.UTF8, "application/json"); //Content-Typeを指定
GC.Collect();
return(res);
}
