/// <summary>
/// get info by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public maintenance_log get_info_by_id(int id)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
maintenance_log obj = null;
if (db.open_connection())
{
string sql_command = "SELECT maintenance_logs.*, users.user_name, users.name FROM maintenance_logs, users ";
sql_command += " WHERE maintenance_logs.user_id = users.id ";
sql_command += " AND id = " + id;
sql_command += " LIMIT 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = new maintenance_log();
obj = (maintenance_log)_get_info(reader);
break;
}
reader.Close();
db.close_connection();
return(obj);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
public IEnumerable <maintenance_log> get_all_by_date(string strDate)
{
List <maintenance_log> listmaintenance_log = new List <maintenance_log>();
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "SELECT maintenance_logs.*, users.user_name, users.name FROM maintenance_logs, users ";
sql_command += " WHERE maintenance_logs.user_id = users.id ";
sql_command += " AND date(start_time) = '" + strDate + "'";
sql_command += " ORDER BY start_time ASC";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
maintenance_log obj = new maintenance_log();
obj = (maintenance_log)_get_info(reader);
listmaintenance_log.Add(obj);
}
reader.Close();
db.close_connection();
return(listmaintenance_log);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// update
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int update(ref maintenance_log obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE maintenance_logs set " +
" user_id = :user_id, " +
" start_time = :start_time, " +
" end_time = :end_time, " +
" maintenance_reason = :maintenance_reason, " +
" tn = :tn " +
" tp = :tp " +
" toc = :toc " +
" mps = :mps " +
" auto_sampler = :auto_sampler " +
" pumping_system = :pumping_system " +
" other = :other " +
" other_para = :other_para " +
" note = :note " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":user_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.user_id;
cmd.Parameters.Add(":start_time", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.start_time;
cmd.Parameters.Add(":end_time", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.end_time;
cmd.Parameters.Add(":maintenance_reason", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.maintenance_reason;
cmd.Parameters.Add(":tn", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tn;
cmd.Parameters.Add(":tp", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tp;
cmd.Parameters.Add(":toc", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.toc;
cmd.Parameters.Add(":mps", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.mps;
cmd.Parameters.Add(":auto_sampler", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.auto_sampler;
cmd.Parameters.Add(":pumping_system", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.pumping_system;
cmd.Parameters.Add(":other", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.other;
cmd.Parameters.Add(":other_para", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.other_para;
cmd.Parameters.Add(":note", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.note;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
private maintenance_log _get_info(NpgsqlDataReader dataReader)
{
maintenance_log obj = new maintenance_log();
try
{
if (!DBNull.Value.Equals(dataReader["id"]))
{
obj.id = Convert.ToInt32(dataReader["id"].ToString().Trim());
}
else
{
obj.id = 0;
}
if (!DBNull.Value.Equals(dataReader["user_id"]))
{
obj.user_id = Convert.ToInt32(dataReader["user_id"].ToString().Trim());
}
else
{
obj.user_id = 0;
}
if (!DBNull.Value.Equals(dataReader["start_time"]))
{
obj.start_time = Convert.ToDateTime(dataReader["start_time"].ToString().Trim());
}
else
{
obj.start_time = DateTime.Now;
}
if (!DBNull.Value.Equals(dataReader["end_time"]))
{
obj.end_time = Convert.ToDateTime(dataReader["end_time"].ToString().Trim());
}
else
{
obj.end_time = DateTime.Now;
}
if (!DBNull.Value.Equals(dataReader["maintenance_reason"]))
{
obj.maintenance_reason = Convert.ToInt32(dataReader["maintenance_reason"].ToString().Trim());
}
else
{
obj.maintenance_reason = 0;
}
if (!DBNull.Value.Equals(dataReader["tn"]))
{
obj.tn = Convert.ToInt32(dataReader["tn"].ToString().Trim());
}
else
{
obj.tn = 0;
}
if (!DBNull.Value.Equals(dataReader["tp"]))
{
obj.tp = Convert.ToInt32(dataReader["tp"].ToString().Trim());
}
else
{
obj.tp = 0;
}
if (!DBNull.Value.Equals(dataReader["toc"]))
{
obj.toc = Convert.ToInt32(dataReader["toc"].ToString().Trim());
}
else
{
obj.toc = 0;
}
if (!DBNull.Value.Equals(dataReader["mps"]))
{
obj.mps = Convert.ToInt32(dataReader["mps"].ToString().Trim());
}
else
{
obj.mps = 0;
}
if (!DBNull.Value.Equals(dataReader["auto_sampler"]))
{
obj.auto_sampler = Convert.ToInt32(dataReader["auto_sampler"].ToString().Trim());
}
else
{
obj.auto_sampler = 0;
}
if (!DBNull.Value.Equals(dataReader["pumping_system"]))
{
obj.pumping_system = Convert.ToInt32(dataReader["pumping_system"].ToString().Trim());
}
else
{
obj.pumping_system = 0;
}
if (!DBNull.Value.Equals(dataReader["other"]))
{
obj.other = Convert.ToInt32(dataReader["other"].ToString().Trim());
}
else
{
obj.other = 0;
}
if (!DBNull.Value.Equals(dataReader["other_para"]))
{
obj.other_para = dataReader["other_para"].ToString().Trim();
}
else
{
obj.other_para = "";
}
if (!DBNull.Value.Equals(dataReader["note"]))
{
obj.note = dataReader["note"].ToString().Trim();
}
else
{
obj.note = "";
}
if (!DBNull.Value.Equals(dataReader["user_name"]))
{
obj.user_name = dataReader["user_name"].ToString().Trim();
}
else
{
obj.user_name = "";
}
if (!DBNull.Value.Equals(dataReader["name"]))
{
obj.name = dataReader["name"].ToString().Trim();
}
else
{
obj.name = "";
}
}
catch (Exception ex)
{
throw ex;
}
return(obj);
}
//#region Public procedure
/// <summary>
/// add new
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int add(ref maintenance_log obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO maintenance_logs (user_id, " +
" start_time, end_time, maintenance_reason, tn, tp, toc, " +
" mps, auto_sampler, pumping_system, other, other_para, note)" +
" VALUES (:user_id, " +
" :start_time, :end_time, :maintenance_reason, :tn, :tp, :toc, " +
" :mps, :auto_sampler, :pumping_system, :other, :other_para, :note)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":user_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.user_id;
cmd.Parameters.Add(":start_time", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.start_time;
cmd.Parameters.Add(":end_time", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.end_time;
cmd.Parameters.Add(":maintenance_reason", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.maintenance_reason;
cmd.Parameters.Add(":tn", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tn;
cmd.Parameters.Add(":tp", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tp;
cmd.Parameters.Add(":toc", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.toc;
cmd.Parameters.Add(":mps", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.mps;
cmd.Parameters.Add(":auto_sampler", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.auto_sampler;
cmd.Parameters.Add(":pumping_system", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.pumping_system;
cmd.Parameters.Add(":other", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.other;
cmd.Parameters.Add(":other_para", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.other_para;
cmd.Parameters.Add(":note", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.note;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
