///<summary>Inserts one JobNote into the database. Returns the new priKey.</summary>
public static long Insert(JobNote jobNote)
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
jobNote.JobNoteNum = DbHelper.GetNextOracleKey("jobnote", "JobNoteNum");
int loopcount = 0;
while (loopcount < 100)
{
try {
return(Insert(jobNote, true));
}
catch (Oracle.ManagedDataAccess.Client.OracleException ex) {
if (ex.Number == 1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated"))
{
jobNote.JobNoteNum++;
loopcount++;
}
else
{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else
{
return(Insert(jobNote, false));
}
}
public bool Post([FromBody] JobNote j)
{
using (var db = DBConnection.GetConnection())
{
return(JobNote.Persist <JobNote>(db, j));
}
}
public static JobNote getEstimateNote(string jobID)
{
JobNote note = new JobNote();
using (MySqlConnection con = new MySqlConnection(connection))
{
string query = "SELECT notes.* FROM JobNotes AS notes, JobNotesJob AS job WHERE job.jobID = '" + jobID + "' AND notes.noteID = job.noteID AND notes.type = 'Estimate'";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (MySqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
note = new JobNote
{
id = (sdr["noteID"]).ToString(),
body = sdr["body"].ToString(),
type = sdr["type"].ToString(),
time = DateTime.Parse(sdr["time"].ToString()),
user = UserProvider.getUserFromUsername(sdr["username"].ToString())
};
}
}
con.Close();
}
}
return(note);
}
public IEnumerable <JobNote> Get()
{
using (var db = DBConnection.GetConnection())
{
return(JobNote.GetAll <JobNote>(db));
}
}
public IActionResult AddNote(string type, string body, string jobID)
{
if (!isAuthenticated())
{
return(RedirectToAction("Login", "Auth"));
}
JobNote note = new JobNote
{
id = JobProvider.GetUniqueKey(255),
type = type,
body = body,
time = DateTime.Now,
user = UserProvider.getUserFromUsername(HttpContext.Session.GetString("user"))
};
JobProvider.addJobNote(note, jobID);
if (type == "Invoice")
{
JobProvider.updateStatus(jobID, "Complete - Awaiting Payment");
}
return(RedirectToAction("ViewJob", "Job", new { id = jobID }));
}
public bool Delete(JobNote j)
{
using (var db = DBConnection.GetConnection())
{
return(JobNote.Delete <JobNote>(db, j));
}
}
public IEnumerable <JobNote> GetByParentId(int id)
{
using (var db = DBConnection.GetConnection())
{
return(JobNote.GetByJobId(db, id));
}
}
public JobNote Get(int id)
{
using (var db = DBConnection.GetConnection())
{
return(JobNote.GetById <JobNote>(db, id));
}
}
///<summary>Updates one JobNote in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables. Returns true if an update occurred.</summary>
public static bool Update(JobNote jobNote, JobNote oldJobNote)
{
string command = "";
if (jobNote.JobNum != oldJobNote.JobNum)
{
if (command != "")
{
command += ",";
}
command += "JobNum = " + POut.Long(jobNote.JobNum) + "";
}
if (jobNote.UserNum != oldJobNote.UserNum)
{
if (command != "")
{
command += ",";
}
command += "UserNum = " + POut.Long(jobNote.UserNum) + "";
}
if (jobNote.DateTimeNote != oldJobNote.DateTimeNote)
{
if (command != "")
{
command += ",";
}
command += "DateTimeNote = " + POut.DateT(jobNote.DateTimeNote) + "";
}
if (jobNote.Note != oldJobNote.Note)
{
if (command != "")
{
command += ",";
}
command += "Note = " + DbHelper.ParamChar + "paramNote";
}
if (jobNote.NoteType != oldJobNote.NoteType)
{
if (command != "")
{
command += ",";
}
command += "NoteType = " + POut.Int((int)jobNote.NoteType) + "";
}
if (command == "")
{
return(false);
}
if (jobNote.Note == null)
{
jobNote.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(jobNote.Note));
command = "UPDATE jobnote SET " + command
+ " WHERE JobNoteNum = " + POut.Long(jobNote.JobNoteNum);
Db.NonQ(command, paramNote);
return(true);
}
private void butDelete_Click(object sender, EventArgs e)
{
if (!MsgBox.Show(this, MsgBoxButtons.OKCancel, "Delete note?"))
{
return;
}
_jobNote = null;
DialogResult = DialogResult.OK;
}
public ActionResult CreateJobNote(int?id)
{
ViewBag.JobMainId = new SelectList(db.JobMains, "Id", "Description", id);
JobNote jn = new JobNote();
jn.Sort = 10 * (1 + db.JobNotes.Where(d => d.JobMainId == id).ToList().Count());
ViewBag.templateNotes = db.PreDefinedNotes.ToList();
return(View(jn));
}
public ActionResult CreateJobNote([Bind(Include = "Id,JobMainId,Sort,Note")] JobNote jobnote)
{
if (ModelState.IsValid)
{
db.JobNotes.Add(jobnote);
db.SaveChanges();
return(RedirectToAction("JobNotes", new { id = jobnote.JobMainId }));
}
ViewBag.JobMainId = new SelectList(db.JobMains, "Id", "Description", jobnote.JobMainId);
return(View(jobnote));
}
public IActionResult BookVehicle(string type, string issue, string cosmetic, string resolution, string labour, string bay, string customerID, string vrm, string mechanic)
{
if (!isAuthenticated())
{
return(RedirectToAction("Login", "Auth"));
}
JobNote estimate = new JobNote
{
id = JobProvider.GetUniqueKey(255),
type = "Estimate",
body = "Issue: " + issue + " Cosmetic Condition: " + cosmetic + " Proposed Resolution: " + resolution,
time = DateTime.Now,
user = UserProvider.getUserFromUsername(HttpContext.Session.GetString("user"))
};
JobNote labourEstimate = new JobNote
{
id = JobProvider.GetUniqueKey(255),
type = "Labour Estimate",
body = labour,
time = DateTime.Now,
user = UserProvider.getUserFromUsername(HttpContext.Session.GetString("user"))
};
List <JobNote> notes = new List <JobNote>();
notes.Add(estimate);
notes.Add(labourEstimate);
Job job = new Job
{
jobID = JobProvider.GetUniqueNumber(8),
start = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day),
end = null,
paid = null,
bay = Int32.Parse(bay),
status = "Ongoing",
type = type,
customer = CustomerProvider.getCustomerFromID(customerID),
vehicle = VehicleProvider.getVehicleFromVRM(vrm),
notes = notes,
labour = new Dictionary <string, float>(),
mechanic = UserProvider.getUserFromUsername(mechanic)
};
JobProvider.createJob(job);
TempData["JobID"] = job.jobID;
return(RedirectToAction("ViewJob", new { id = job.jobID }));
}
///<summary>Inserts one JobNote into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(JobNote jobNote)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
return(InsertNoCache(jobNote, false));
}
else
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
jobNote.JobNoteNum = DbHelper.GetNextOracleKey("jobnote", "JobNoteNum"); //Cacheless method
}
return(InsertNoCache(jobNote, true));
}
}
public ActionResult EditJobNote(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
JobNote jobNote = db.JobNotes.Find(id);
if (jobNote == null)
{
return(HttpNotFound());
}
ViewBag.JobMainId = new SelectList(db.JobMains, "Id", "Description", jobNote.JobMainId);
return(View(jobNote));
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <JobNote> TableToList(DataTable table)
{
List <JobNote> retVal = new List <JobNote>();
JobNote jobNote;
foreach (DataRow row in table.Rows)
{
jobNote = new JobNote();
jobNote.JobNoteNum = PIn.Long(row["JobNoteNum"].ToString());
jobNote.JobNum = PIn.Long(row["JobNum"].ToString());
jobNote.UserNum = PIn.Long(row["UserNum"].ToString());
jobNote.DateTimeNote = PIn.DateT(row["DateTimeNote"].ToString());
jobNote.Note = PIn.String(row["Note"].ToString());
retVal.Add(jobNote);
}
return(retVal);
}
///<summary>Updates one JobNote in the database.</summary>
public static void Update(JobNote jobNote)
{
string command = "UPDATE jobnote SET "
+ "JobNum = " + POut.Long(jobNote.JobNum) + ", "
+ "UserNum = " + POut.Long(jobNote.UserNum) + ", "
+ "DateTimeNote= " + POut.DateT(jobNote.DateTimeNote) + ", "
+ "Note = " + DbHelper.ParamChar + "paramNote "
+ "WHERE JobNoteNum = " + POut.Long(jobNote.JobNoteNum);
if (jobNote.Note == null)
{
jobNote.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(jobNote.Note));
Db.NonQ(command, paramNote);
}
public ActionResult EditJobNote([Bind(Include = "Id,JobMainId,Sort,Note")] JobNote jobNote)
{
if (ModelState.IsValid)
{
db.Entry(jobNote).State = EntityState.Modified;
if ((int)jobNote.Sort % 10 != 0)
{
jobNote.Sort = (int)jobNote.Sort * 10;
}
db.SaveChanges();
return(RedirectToAction("JobNotes", new { id = jobNote.JobMainId }));
}
ViewBag.JobMainId = new SelectList(db.JobMains, "Id", "Description", jobNote.JobMainId);
return(View(jobNote));
}
///<summary>Returns true if Update(JobNote,JobNote) would make changes to the database.
///Does not make any changes to the database and can be called before remoting role is checked.</summary>
public static bool UpdateComparison(JobNote jobNote, JobNote oldJobNote)
{
if (jobNote.JobNum != oldJobNote.JobNum)
{
return(true);
}
if (jobNote.UserNum != oldJobNote.UserNum)
{
return(true);
}
if (jobNote.DateTimeNote != oldJobNote.DateTimeNote)
{
return(true);
}
if (jobNote.Note != oldJobNote.Note)
{
return(true);
}
return(false);
}
///<summary>Inserts one JobNote into the database. Provides option to use the existing priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(JobNote jobNote, bool useExistingPK)
{
bool isRandomKeys = Prefs.GetBoolNoCache(PrefName.RandomPrimaryKeys);
string command = "INSERT INTO jobnote (";
if (!useExistingPK && isRandomKeys)
{
jobNote.JobNoteNum = ReplicationServers.GetKeyNoCache("jobnote", "JobNoteNum");
}
if (isRandomKeys || useExistingPK)
{
command += "JobNoteNum,";
}
command += "JobNum,UserNum,DateTimeNote,Note,NoteType) VALUES(";
if (isRandomKeys || useExistingPK)
{
command += POut.Long(jobNote.JobNoteNum) + ",";
}
command +=
POut.Long(jobNote.JobNum) + ","
+ POut.Long(jobNote.UserNum) + ","
+ DbHelper.Now() + ","
+ DbHelper.ParamChar + "paramNote,"
+ POut.Int((int)jobNote.NoteType) + ")";
if (jobNote.Note == null)
{
jobNote.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(jobNote.Note));
if (useExistingPK || isRandomKeys)
{
Db.NonQ(command, paramNote);
}
else
{
jobNote.JobNoteNum = Db.NonQ(command, true, "JobNoteNum", "jobNote", paramNote);
}
return(jobNote.JobNoteNum);
}
public IActionResult Pay(string jobID)
{
if (!isAuthenticated())
{
return(RedirectToAction("Login", "Auth"));
}
JobProvider.updateStatus(jobID, "Complete - Paid");
JobNote note = new JobNote
{
id = JobProvider.GetUniqueKey(255),
type = "Payment",
body = "Customer payment taken.",
time = DateTime.Now,
user = UserProvider.getUserFromUsername(HttpContext.Session.GetString("user"))
};
JobProvider.addJobNote(note, jobID);
return(RedirectToAction("ViewJob", "Job", new { id = jobID }));
}
///<summary>Inserts one JobNote into the database. Provides option to use the existing priKey.</summary>
public static long Insert(JobNote jobNote, bool useExistingPK)
{
if (!useExistingPK && PrefC.RandomKeys)
{
jobNote.JobNoteNum = ReplicationServers.GetKey("jobnote", "JobNoteNum");
}
string command = "INSERT INTO jobnote (";
if (useExistingPK || PrefC.RandomKeys)
{
command += "JobNoteNum,";
}
command += "JobNum,UserNum,DateTimeNote,Note) VALUES(";
if (useExistingPK || PrefC.RandomKeys)
{
command += POut.Long(jobNote.JobNoteNum) + ",";
}
command +=
POut.Long(jobNote.JobNum) + ","
+ POut.Long(jobNote.UserNum) + ","
+ DbHelper.Now() + ","
+ DbHelper.ParamChar + "paramNote)";
if (jobNote.Note == null)
{
jobNote.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(jobNote.Note));
if (useExistingPK || PrefC.RandomKeys)
{
Db.NonQ(command, paramNote);
}
else
{
jobNote.JobNoteNum = Db.NonQ(command, true, "JobNoteNum", "jobNote", paramNote);
}
return(jobNote.JobNoteNum);
}
public static void addJobNote(JobNote note, string jobID)
{
using (MySqlConnection con = new MySqlConnection(connection))
{
string query = "INSERT INTO JobNotes VALUES (@noteID, @body, @type, @time, @username)";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Parameters.AddWithValue("@noteID", note.id);
cmd.Parameters.AddWithValue("@type", note.type);
cmd.Parameters.AddWithValue("@body", note.body);
cmd.Parameters.AddWithValue("@time", note.time);
cmd.Parameters.AddWithValue("@username", note.user.username);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
query = "INSERT INTO JobNotesJob VALUES (@noteID, @jobID)";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Parameters.AddWithValue("@noteID", note.id);
cmd.Parameters.AddWithValue("@jobID", jobID);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
///<summary>Inserts one JobNote into the database. Returns the new priKey.</summary>
public static long Insert(JobNote jobNote)
{
return(Insert(jobNote, false));
}
public FormJobNoteEdit(JobNote jobNote)
{
_jobNote = jobNote.Copy();
InitializeComponent();
Lan.F(this);
}
///<summary>Inserts one JobNote into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(JobNote jobNote)
{
return(InsertNoCache(jobNote, false));
}