public int Load(int wkTicketID)
{
//verify parameter is sensible
if (wkTicketID < 0) { return -1; }
//reinitialise lists
wtLogs = new WorkTicketLogs();
//check for any log entries
int logCheck = wtLogs.Populate(wkTicketID);
//not worried if no logs yet. They are added over the course of a job
//Build query string
//Build query string
String queryFull = "SELECT * FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "=" + wkTicketID;
int rCheck = 0; //used for error-checking later
String[] outputVars = new String[numAttribs + 1];
NewOrOld = false; //importing existing record
//attempt DB connection
try
{
dbConnection = new SqlConnection(dbInString);
dbConnection.Open();
}
catch { return -1; }
//build query string
queryFull += queryCondition;
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryFull;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 15;
//Run command, and tidy-up if exceptions occur
try { dbReader = dbCommand.ExecuteReader(); }
catch
{
dbReader.Close();
dbConnection.Close();
return -1;
}
//initialise output array
for (int i = 0; i < outputVars.Length; i++)
{
outputVars[i] = "";
}
//Import values from query into string array
while (dbReader.Read())
{
if (rCheck > 0)
{
return -1; //if more than one result was returned, run hard & run fast!
}
else
{
int i = 0; //iterator for output string
foreach (String att in attribNames)
{
try { outputVars[i] = dbReader[att].ToString(); }
catch { return -1; }
i++;
}
}
rCheck++; //tracks number of results
}
//Verify that at least one result was returned
if (rCheck < 1) { return -1; };
//Import values from Db into class
try { workTicketID = Convert.ToInt32(outputVars[0]); }
catch { return -1; }
workTicketCreator = outputVars[1];
workTicketLocation = outputVars[2];
workTicketAllocatedTo = outputVars[3];
workTicketTitle = outputVars[4];
workTicketDesc = outputVars[5];
try { workTicketApprovalStatus = Convert.ToBoolean(outputVars[6]); }
catch { return -1; }
try { workTicketActive = Convert.ToBoolean(outputVars[7]); }
catch { return -1; }
try { createdTimeStamp = DateTime.Parse(outputVars[8]); }
catch { return -1; }
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
NewOrOld = false; //fail-safe
return 0;
}
public int Save()
{
//Strings to handle query as it's built
String queryFull = "", queryCondition = "";
//Verify workTicketID is something sensible
if (workTicketLogLinkID < 0) { return -1; }
//check the work category chosen exists
List<String> existingWorkCats = wkCategories.getCategories();
bool testCatInput = false;
foreach (String cat in existingWorkCats)
{
if (workTicketLogCategory == cat)
{
testCatInput = true;
break;
}
}
if (testCatInput == false) { return -1; }
//likewise with the user creator record
Users test = new Users();
int result = test.Populate("txtUserName", "'" + workTicketLogCreator + "'");
if (result < 0) { return -1; }
List<UserRecord> testList = test.getUserList();
if (testList.Count() < 1) { return -1; }
//is this a new record? If so, we need an INSERT statement. Else, an UPDATE
if (NewOrOld == true)
{
queryFull = "INSERT INTO " + tableName + " VALUES (";
queryFull += workTicketLogLinkID + ", ";
queryFull += "'" + workTicketLogTime.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
queryFull += "'" + workTicketLogCategory + "', ";
queryFull += "'" + workTicketLogCreator + "', ";
queryFull += "'" + workTicketLogTitle + "', ";
queryFull += "'" + workTicketLogDesc + "', ";
queryFull += "'" + workTicketLogTimeTakenHours + "')";
}
else
{
//verify record exists
int wtlTest = Load(workTicketLogLinkID, workTicketLogTime);
if (wtlTest < 0) { return -1; }
//build query
queryFull = "UPDATE " + tableName + " SET ";
queryCondition = "WHERE " + attribNames[0] + "=" + workTicketLogLinkID + " AND " + attribNames[1] + "='" + workTicketLogTime + "'";
queryFull += attribNames[2] + "='" + workTicketLogCategory + "', ";
queryFull += attribNames[3] + "='" + workTicketLogCreator + "', ";
queryFull += attribNames[4] + "='" + workTicketLogTitle + "', ";
queryFull += attribNames[5] + "='" + workTicketLogDesc + "', ";
queryFull += attribNames[6] + "=" + workTicketLogTimeTakenHours + " ";
queryFull += queryCondition;
}
//attempt DB connection
try
{
dbConnection = new SqlConnection(dbInString);
dbConnection.Open();
}
catch { return -1; }
//create command that will run query
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryFull;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 15;
//Run command, and tidy-up if exceptions occur
try { dbReader = dbCommand.ExecuteReader(); }
catch
{
dbReader.Close();
dbConnection.Close();
return -1;
}
//if new, check saved correctly
if (NewOrOld == true)
{
//int aTest = Load(workTicketLogLinkID, workTicketLogTime);
//if (aTest < 0) { return -1; }
WorkTicketLogs wtList = new WorkTicketLogs();
wtList.Populate(-1, attribNames[0] + "=" + workTicketLogLinkID + " AND CONVERT(char,"+ attribNames[1] + ",120)='" + workTicketLogTime.ToString(workTicketLogTime.ToString("yyyy-MM-dd HH:mm:ss")) + "'");
List<WorkTicketLog> logs = wtList.getWorkTicketLogList();
Boolean testBool = false;
foreach (WorkTicketLog log in logs)
{
if (log.workTicketLogLinkID == workTicketLogLinkID && log.workTicketLogTime == workTicketLogTime)
{
testBool = true;
break;
}
}
if (!testBool) { return -1; }
}
NewOrOld = false; //fail-safe
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}