private int Get_NewCID(database datab)
{
int CID;
string CIDquerey = @"select max(CID) as CID from Customer";
DataTable tmptbl = new DataTable();
datab.query(CIDquerey);
tmptbl.Load(datab.myReader);
CID = Convert.ToInt32(tmptbl.Rows[0]["CID"]);
CID += 1;
return(CID);
}
private void VINDropBox_Enter(object sender, EventArgs e)
// get list of cars available for date range selected
// 20190610TAG
{
string start = DateOutPicker.Value.ToString("yyyy-MM-dd");
string end = DateExpectedInPicker.Value.ToString("yyyy-MM-dd");
string command = "select car.VIN " +
"from car " +
"where " +
"NOT EXISTS " +
"(select carid " +
"from rentaltransaction " +
"where car.vin = rentaltransaction.carid and '" + start + "' BETWEEN rentaltransaction.dateout and rentaltransaction.dateexpectedin) " +
"and " +
"NOT EXISTS " +
"(select carid " +
"from rentaltransaction " +
"where car.vin = rentaltransaction.carid and '" + end + "' BETWEEN rentaltransaction.dateout and rentaltransaction.dateexpectedin)" +
"and " +
"NOT EXISTS " +
"(select carid " +
"from rentaltransaction " +
"where car.vin = rentaltransaction.carid and '" + start + "' < rentaltransaction.dateout and '" + end + "' > rentaltransaction.dateexpectedin)";
try
{
datab.query(command);
VINDropBox.Items.Clear();
while (datab.myReader.Read())
{
VINDropBox.Items.Add(datab.myReader[0]);
}
if (VINDropBox.Items.Count == 0)
{
VINDropBox.Items.Add("No cars available - try different dates");
}
datab.myReader.Close();
}
catch (Exception e3)
{
MessageBox.Show(e3.ToString(), "Error");
}
}
private void determineMemberStatus()
{
// get current customer rental transaction for past year, check count
int count = 0;
string command = "SELECT count(rID) " +
"FROM RentalTransaction, " +
"(SELECT * FROM Customer WHERE NOT EXISTS (SELECT cID FROM MemberStatus WHERE MemberStatus.cID = Customer.cID)) as C " +
"WHERE C.cID = RentalTransaction.customerID and " +
"C.name = '" + CustomerIDDropBox.Text + "' and " +
"RentalTransaction.dateOut > '" + DateTime.Now.AddDays(-365) + "';";
try
{
datab.query(command);
while (datab.myReader.Read())
{
count = int.Parse(datab.myReader[0].ToString());
}
datab.myReader.Close();
}
catch (Exception e2)
{
MessageBox.Show(e2.ToString(), "Error");
}
// update to gold member status
if (count >= 3)
{
try
{
string updateCommand = "INSERT into MemberStatus values(" + "(SELECT cID FROM Customer WHERE name = '" + CustomerIDDropBox.Text + "')" + ",'" + DateTime.Now + "');";
datab.insert(updateCommand);
main.TransactionDGV_LoadAll(datab);
}
catch (Exception e2)
{
MessageBox.Show(e2.ToString(), "Error");
}
}
}
