/// <summary>
/// Insert mokkan information to database query string
/// </summary>
public String GetInsertQueryString(String glassId)
{
String queryStr = "";
queryStr += "INSERT INTO mka_mokkandata VALUES(";
queryStr += String.Format("'{0}',", RBangou);
queryStr += String.Format("'{0}',", glassId);
queryStr += String.Format("'{0}',", MkaDBConnect.EscapeSQL(KariShakubun));
queryStr += String.Format("'{0}',", MkaDBConnect.EscapeSQL(GaihouShoshuuJyouhou));
queryStr += String.Format("'{0}',", MkaDBConnect.EscapeSQL(ShasinBangouJyouhou));
queryStr += String.Format("'{0}'", MkaDBConnect.EscapeSQL(Bikou));
queryStr += ")";
return(queryStr);
}
/// <summary>
/// Reset database
/// </summary>
private void ResetDb()
{
MkaDBConnect dbCon = new MkaDBConnect();
// open connection
if (dbCon.OpenConnection() == false)
{
MkaMessage.ShowError(MkaMessage.ErrDBConnect);
return;
}
MySqlConnection con = dbCon.GetConnection();
// reset database
MySqlCommand cmd = new MySqlCommand("reset_db", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
// close connection
dbCon.CloseConnection();
}
/// <summary>
/// Get retured result and display on datagrid
/// </summary>
private void LoadPage()
{
btnPrevious.Enabled = (_currentPage == 0) ? false : true;
btnNext.Enabled = (_currentPage == _pageCount - 1) ? false : true;
MkaDBConnect dbCon = new MkaDBConnect();
MySqlConnection con;
MySqlCommand cmd;
int intSkip = _currentPage * _pageSize;
// open connection
if (dbCon.OpenConnection() == false)
{
MkaMessage.ShowError(MkaMessage.ErrDBConnect);
return;
}
con = dbCon.GetConnection();
this.Cursor = Cursors.WaitCursor;
// get search records
cmd = new MySqlCommand("get_records", con);
cmd.CommandTimeout = 600; // 10 minutes
cmd.CommandType = CommandType.StoredProcedure;
if (_chousaJisuu == 0)
{
cmd.Parameters.Add(new MySqlParameter("_chousaJisuu", null));
}
else
{
cmd.Parameters.Add(new MySqlParameter("_chousaJisuu", _chousaJisuu));
}
cmd.Parameters.Add(new MySqlParameter("_ooChikuBangou", _ooChiku));
cmd.Parameters.Add(new MySqlParameter("_chuushouChikuBangou", _chuushouChiku));
cmd.Parameters.Add(new MySqlParameter("_ikouBangou", _ikoumei));
cmd.Parameters.Add(new MySqlParameter("_dosouMei", _dosoumei));
if (_grid == 0)
{
cmd.Parameters.Add(new MySqlParameter("_grid", null));
}
else
{
cmd.Parameters.Add(new MySqlParameter("_grid", _grid));
}
cmd.Parameters.Add(new MySqlParameter("_date", _date));
cmd.Parameters.Add(new MySqlParameter("_batBangou", _batBangou));
cmd.Parameters.Add(new MySqlParameter("_glassBangou", _glassItaBangou));
if (_rBangou == 0)
{
cmd.Parameters.Add(new MySqlParameter("_rBangou", null));
}
else
{
cmd.Parameters.Add(new MySqlParameter("_rBangou", _rBangou));
}
cmd.Parameters.Add(new MySqlParameter("_kariShakubun", _kariShakubun));
cmd.Parameters.Add(new MySqlParameter("_gaihouShoshuuJyouhou", _gaihouShoshuuJyouhou));
cmd.Parameters.Add(new MySqlParameter("_shasinBangouJyouhou", _shashinBangouJyouhou));
cmd.Parameters.Add(new MySqlParameter("_bikou", _bikou));
cmd.Parameters.Add(new MySqlParameter("_pageSize", _pageSize));
cmd.Parameters.Add(new MySqlParameter("_skip", intSkip));
MySqlDataReader rdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr);
Size _resize = new Size(64, 64);
DateTime _dDate;
String path = "", _sDate;
Bitmap bmp = null;
dtgrResult.Rows.Clear();
foreach (DataRow row in dt.Rows)
{
try
{
// get mokkan image
path = String.Format("{0}\\{1}\\{2}\\{3}\\{1}_{2}_{3}_{4:00000}.jpg", MkaDefine.MokkanPath, row[11], row[6], row[5], row[0]);
bmp = new Bitmap(path);
}
catch
{
MessageBox.Show("ダータベースの画像ファイルを開くことできません" + Environment.NewLine +
path + "を見付けません", "メッセージ", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
//_log.Error("Can not open image!: " + path +Environment.NewLine);
// delete mokkan of the glass if existed
//cmd = new MySqlCommand("delete_mokkan", con);
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add(new MySqlParameter("_rBangou", row[0]));
//cmd.ExecuteNonQuery();
//_log.Info("Deleted registered but not existed item.");
continue;
}
bmp = ResizeImage(bmp, _resize);
_dDate = DateTime.ParseExact(row[12].ToString(), MkaDefine.DBDateFormat, null);
_sDate = _dDate.ToString(MkaDefine.DateFormat);
// add row
dtgrResult.Rows.Add(bmp, row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], _sDate);
}
// close connection
dbCon.CloseConnection();
lblPage.Visible = true;
lblPage.Text = String.Format("{0}/{1}", _currentPage + 1, _pageCount);
this.Cursor = Cursors.Default;
}
/// <summary>
/// Get number of returned records
/// </summary>
private void GetCount()
{
MkaDBConnect dbCon = new MkaDBConnect();
MySqlConnection con;
MySqlCommand cmd;
// open connection
if (dbCon.OpenConnection() == false)
{
MkaMessage.ShowError(MkaMessage.ErrDBConnect);
return;
}
con = dbCon.GetConnection();
this.Cursor = Cursors.WaitCursor;
// get search record count
cmd = new MySqlCommand("get_count", con);
cmd.CommandType = CommandType.StoredProcedure;
if (_chousaJisuu == 0)
{
cmd.Parameters.Add(new MySqlParameter("_chousaJisuu", null));
}
else
{
cmd.Parameters.Add(new MySqlParameter("_chousaJisuu", _chousaJisuu));
}
cmd.Parameters.Add(new MySqlParameter("_ooChikuBangou", _ooChiku));
cmd.Parameters.Add(new MySqlParameter("_chuushouChikuBangou", _chuushouChiku));
cmd.Parameters.Add(new MySqlParameter("_ikouBangou", _ikoumei));
cmd.Parameters.Add(new MySqlParameter("_dosouMei", _dosoumei));
if (_grid == 0)
{
cmd.Parameters.Add(new MySqlParameter("_grid", null));
}
else
{
cmd.Parameters.Add(new MySqlParameter("_grid", _grid));
}
cmd.Parameters.Add(new MySqlParameter("_date", _date));
cmd.Parameters.Add(new MySqlParameter("_batBangou", _batBangou));
cmd.Parameters.Add(new MySqlParameter("_glassBangou", _glassItaBangou));
if (_rBangou == 0)
{
cmd.Parameters.Add(new MySqlParameter("_rBangou", null));
}
else
{
cmd.Parameters.Add(new MySqlParameter("_rBangou", _rBangou));
}
cmd.Parameters.Add(new MySqlParameter("_kariShakubun", _kariShakubun));
cmd.Parameters.Add(new MySqlParameter("_gaihouShoshuuJyouhou", _gaihouShoshuuJyouhou));
cmd.Parameters.Add(new MySqlParameter("_shasinBangouJyouhou", _shashinBangouJyouhou));
cmd.Parameters.Add(new MySqlParameter("_bikou", _bikou));
cmd.Parameters.Add(new MySqlParameter("_recCount", _totalRecords));
cmd.Parameters["_recCount"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
_totalRecords = (int)cmd.Parameters["_recCount"].Value;
// close connection
dbCon.CloseConnection();
this.Cursor = Cursors.Default;
}
