// only write not system generated tasks
// write to cmsdat.excotask set flags to 1 for spark and emd
// otherwise write to dbo.d_task set flags to 1
public void MarkComplete(Database database)
{
if (!isSystemGenerated)
{
ExcoODBC odbcConnection = ExcoODBC.Instance;
string query;
if (460 != seqNum && 470 != seqNum)
{
odbcConnection.Open(Database.DECADE_MARKHAM);
query = "update dbo.d_task set flags=1 where id=" + decadeID.ToString();
if (1 != odbcConnection.RunQueryWithoutReader(query))
{
throw new Exception("Faild to run query: " + query);
}
}
else
{
odbcConnection.Open(database);
if (460 == seqNum)
{
query = "update cmsdat.excotask set flags=1 where sonum=" + soNum.ToString() + " and empnum=" + empNum.ToString() + " and prtcode='" + type + side + "' and task='S0' and tasktime='" + taskTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and station='SPKC' and rtype='RTM'";
}
else
{
query = "update cmsdat.excotask set flags=1 where sonum=" + soNum.ToString() + " and empnum=" + empNum.ToString() + " and prtcode='" + type + side + "' and task='W0' and tasktime='" + taskTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and station='WOUT' and rtype='RTM'";
}
if (1 != odbcConnection.RunQueryWithoutReader(query))
{
throw new Exception("Faild to run query: " + query);
}
}
}
}
public FormCAMCheckOut()
{
try
{
InitializeComponent();
// get machine id
if (File.Exists(empFile))
{
string[] fileContent = File.ReadAllLines(empFile);
if (2 != fileContent.Length)
{
throw new Exception("Invalid employee file!");
}
else
{
empNum = fileContent[0].Trim();
machineID = fileContent[1].Trim();
}
// submit check in to decade
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE);
string query = "insert into dbo.d_task values (999999, " + empNum + ", 'OO', 'X', 'X', '" + machineID + "', CURRENT_TIMESTAMP, 0, 0)";
if (1 != database.RunQueryWithoutReader(query))
{
throw new Exception("Failed to update decade!");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void RunQueryWithoutReaderTest()
{
// insert 1 row
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.PRODTEST);
string query = "select count(*) from cmsdat.aprq";
OdbcDataReader reader = database.RunQuery(query);
Assert.IsTrue(reader.Read());
Assert.AreEqual(0, Convert.ToInt32(reader[0]));
reader.Close();
query = "insert into cmsdat.aprq (b02comn) values (99)";
int colChanged = database.RunQueryWithoutReader(query);
Assert.AreEqual(1, colChanged);
query = "select count(*) from cmsdat.aprq";
reader = database.RunQuery(query);
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, Convert.ToInt32(reader[0]));
reader.Close();
// insert 1 row
colChanged = 0;
Assert.AreEqual(0, colChanged);
query = "insert into cmsdat.aprq (b02comn) values (98)";
colChanged = database.RunQueryWithoutReader(query);
Assert.AreEqual(1, colChanged);
query = "select count(*) from cmsdat.aprq";
reader = database.RunQuery(query);
Assert.IsTrue(reader.Read());
Assert.AreEqual(2, Convert.ToInt32(reader[0]));
reader.Close();
// delete 2 rows
colChanged = 0;
Assert.AreEqual(0, colChanged);
query = "delete from cmsdat.aprq where b02comn=98 or b02comn=99";
colChanged = database.RunQueryWithoutReader(query);
Assert.AreEqual(2, colChanged);
query = "select count(*) from cmsdat.aprq";
reader = database.RunQuery(query);
Assert.IsTrue(reader.Read());
Assert.AreEqual(0, Convert.ToInt32(reader[0]));
reader.Close();
}
private void buttonCheckOut_Click(object sender, EventArgs e)
{
try
{
empNum = textEmpNum.Text.Trim();
// check if employee number is right
string[] fileContent = File.ReadAllLines(idFile);
int index = -1;
foreach (string line in fileContent)
{
if (line.Contains(empNum))
{
index = line.IndexOf("CM");
if (-1 == index)
{
index = line.IndexOf("DS");
if (-1 == index)
{
throw new Exception("CAM STATION file format error!");
}
}
machineID = line.Substring(index, 4);
break;
}
}
if (-1 == index)
{
throw new Exception("Employee number " + empNum + " is invalid, please retype!");
}
// submit check out to decade
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.DECADE);
string query = "insert into dbo.d_task values (999999, " + empNum + ", 'II', 'X', 'X', '" + machineID + "', CURRENT_TIMESTAMP, 0, 0)";
if (1 == database.RunQueryWithoutReader(query))
{
// update employee number file
File.WriteAllText(empFile, empNum + Environment.NewLine + machineID);
Application.Exit();
}
else
{
throw new Exception("Failed to update decade!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
textEmpNum.Clear();
}
}
public void TestMarkComplete()
{
// test regular case
Task task = new Task(238033, 10187, 'F', 'B', "CM", new DateTime(2011, 5, 30, 12, 0, 0), "CM17", 100);
// at first flags is 0
string query = "select id, flags from dbo.d_task where ordernumber=" + task.soNum.ToString() + " and employeenumber=" + task.empNum.ToString() + " and part='" + task.type + "' and subpart='" + task.side + "' and task='" + task.taskCode + "' and tasktime='" + task.taskTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and station='" + task.station + "'";
ExcoODBC odbcConnection = ExcoODBC.Instance;
odbcConnection.Open(Database.DECADE_MARKHAM);
OdbcDataReader reader = odbcConnection.RunQuery(query);
Assert.IsTrue(reader.Read());
Assert.AreEqual(0, Convert.ToInt32(reader["flags"]));
task.decadeID = Convert.ToInt32(reader["id"]);
reader.Close();
// then change to 1
task.MarkComplete(Database.DECADE_MARKHAM);
// check if changed to 1
query = "select flags from dbo.d_task where id=" + task.decadeID;
reader = odbcConnection.RunQuery(query);
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, Convert.ToInt32(reader[0]));
reader.Close();
// then change back to 0
query = "update dbo.d_task set flags=0 where id=" + task.decadeID;
Assert.AreEqual(1, odbcConnection.RunQueryWithoutReader(query));
// check if changed back to 0
query = "select flags from dbo.d_task where id=" + task.decadeID;
reader = odbcConnection.RunQuery(query);
Assert.IsTrue(reader.Read());
Assert.AreEqual(0, Convert.ToInt32(reader[0]));
reader.Close();
// test 460
// test 470
}
// clear all typed text
protected void SubmitButton_Click(object sender, EventArgs e)
{
// check if mandatory fields are filled and if data is valid
if (DateText.Text.Length == 0)
{
Response.Write("<SCRIPT LANGUAGE=\"JavaScript\">alert(\"Please input visit date!\")</SCRIPT>");
}
// write to decade
else
{
/* validation
* // check if type is ok
* try
* {
* Convert.ToInt32(PressText.Text);
* }
* catch
* {
* Response.Write("<SCRIPT LANGUAGE=\"JavaScript\">alert(\"Please check number of presses!\")</SCRIPT>");
* return;
* }
*
* try
* {
* Convert.ToDouble(ExpenseText.Text);
* }
* catch
* {
* Response.Write("<SCRIPT LANGUAGE=\"JavaScript\">alert(\"Please check Estimated Die Expense/Mn!\")</SCRIPT>");
* return;
* }
*/
// get customer id
string query = "select bvcust from cmsdat.cust where bvname='" + CustomerList.Text + "' order by bvcust";
string customerID = string.Empty;
ExcoODBC database = ExcoODBC.Instance;
database.Open(Database.CMSDAT);
OdbcDataReader reader = database.RunQuery(query);
if (reader.Read())
{
customerID = reader["bvcust"].ToString().Trim();
}
else
{
throw new Exception("Getting customer id failed!");
}
reader.Close();
// write to database
if (isEdit)
{
query = "update [tiger].[dbo].[Sales_Call_Report] set [CustomerName] = '" + CustomerList.Text.Replace("'", "''") + "', [CustomerID] = '" + customerID + "', [Date] = '" + DateText.Text + "', [Attendee] = '" + AttendeeText.Text.Replace("'", "''") + "', [NumberOfPresses] = '" + PressText.Text.Replace("'", "''") + "', [DieExpense] = '" + ExpenseText.Text.Replace("'", "''") + "', [OtherSuppliers] = '" + SupplierText.Text.Replace("'", "''") + "', [PurposeOfVisit] = '" + PurposeText.Text.Replace("'", "''") + "', [AdditionalInformation] = '" + InformationText.Text.Replace("'", "''") + "', [HowIncreaseBusiness] = '" + IncreaseText.Text.Replace("'", "''") + "', [FollowUpDate] = '" + FollowUpText.Text.Replace("'", "''") + "' where [ID]= '" + Session["_SalesCallReportID"] + "'";
}
else
{
query = "insert into [tiger].[dbo].[Sales_Call_Report] values ('" + CustomerList.Text.Replace("'", "''") + "', '" + customerID + "', '" + User.Identity.Name + "', '" + DateText.Text + "', '" + AttendeeText.Text + "', '" + PressText.Text.Replace("'", "''") + "', '" + ExpenseText.Text + "', '" + SupplierText.Text.Replace("'", "''") + "', '" + PurposeText.Text.Replace("'", "''") + "', '" + InformationText.Text.Replace("'", "''") + "', '" + IncreaseText.Text.Replace("'", "''") + "', '" + FollowUpText.Text + "')";
}
database.Open(Database.DECADE_MARKHAM);
if (1 == database.RunQueryWithoutReader(query))
{
// generate pdf file
string filePath = GeneratePDFFile(customerID);
// send to recipients
//SendFileViaEmail(filePath);
//System.Diagnostics.Process.Start(filePath);
//Response.Write("<SCRIPT LANGUAGE=\"JavaScript\">alert(\"Submit report done!\")</SCRIPT>");
Response.Redirect("EmailPDF.aspx?filesubPath=" + filesubPath);
}
else
{
Response.Write("<SCRIPT LANGUAGE=\"JavaScript\">alert(\"Submit report failed!\")</SCRIPT>");
}
}
isEdit = false;
}