private void BindDetails(int id)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
int busid = General.GetBusID(cmbBusName.Text, General.BUS);
int type = General.GetBusID(cmbScheduleType.Text, General.SCHEDULETYPE);
string qry = "SELECT ";
qry += " SM.ID, BM.Name AS [Bus Name], ST.ScheduleType AS [Type], SM.OldDate AS [Old Date], SM.LastDate As [Last Date], SM.ReminderDate AS [Reminder Date]";
qry += " FROM ScheduleMaster SM INNER JOIN BusMaster BM ON SM.BusMaster_ID = BM.ID ";
qry += " INNER JOIN ScheduleType ST ON SM.Type_ID = ST.ID ";
qry += " WHERE BM.ID = " + busid;
if (id == 1)
{
qry += " AND ST.ID = " + type;
groupBox3.Enabled = true;
}
else
{
groupBox3.Enabled = false;
}
DataSet dsbus = new DataSet();
SqlDataAdapter dabus = new SqlDataAdapter(qry, conn);
dabus.Fill(dsbus);
gvScheduleData.DataSource = dsbus.Tables[0].DefaultView;
gvScheduleData.Columns[0].Visible = false;
}
private void bUpdate_Click(object sender, EventArgs e)
{
int busid = General.GetBusID(cmbBusName.Text, General.BUS);
int type = General.GetBusID(cmbScheduleType.Text, General.SCHEDULETYPE);
int day = Convert.ToInt32(tbDay.Text);
int month = Convert.ToInt32(tbMonth.Text);
int year = Convert.ToInt32(tbYear.Text);
int recurring = 1;
int reminderday = Convert.ToInt32(tbRemind.Text);
DateTime olddate = Convert.ToDateTime(dateTimePicker1.Text);
DateTime lastdate = olddate;
string format = "";
if (rbDay.Checked)
{
lastdate = lastdate.AddDays(Convert.ToInt32(tbDay.Text));
format = General.DAY;
}
else if (rbMonth.Checked)
{
lastdate = lastdate.AddMonths(Convert.ToInt32(tbMonth.Text));
format = General.MONTH;
}
else if (rbYear.Checked)
{
lastdate = lastdate.AddYears(Convert.ToInt32(tbYear.Text));
format = General.YEAR;
}
DateTime reminderdate = lastdate.AddDays((-1 * Convert.ToDouble(tbRemind.Text)));
string qry = "UPDATE ScheduleMaster SET ";
qry += " BusMaster_ID = " + busid + ",";
qry += " Type_ID = " + type + ",";
qry += " Day= " + day + ",";
qry += " Month= " + month + ",";
qry += " Year= " + year + ",";
qry += " Recurring = " + recurring + ",";
qry += " ReminderDay = " + reminderday + ",";
qry += " OldDate = '" + olddate.ToString("yyyy-MM-dd") + "',";
qry += " LastDate = '" + lastdate.ToString("yyyy-MM-dd") + "',";
qry += " ReminderDate = '" + reminderdate.ToString("yyyy-MM-dd") + "',";
qry += " Format = '" + format + "'";
qry += " WHERE ID =" + globalid;
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
SqlCommand cmd = new SqlCommand(qry, conn);
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show(General.SUCCESS_MSG);
BindDetails(1);
}
private void cmbBusName_SelectedIndexChanged(object sender, EventArgs e)
{
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
int busid = General.GetBusID(cmbBusName.Text, General.BUS);
conn.Open();
string qry1 = "";
if (cmbBusName.Text == "__All__")
{
qry1 = "SELECT LastDate,ReminderDate,ScheduleType+'-'+Name+'-'+RegNo AS Name,Color FROM ScheduleMaster S INNER JOIN BusMaster B ON S.BusMaster_ID = B.ID INNER JOIN ScheduleType ST On ST.ID = S.Type_ID";
}
else
{
qry1 = "SELECT LastDate,ReminderDate,ScheduleType+'-'+Name+'-'+RegNo AS Name,Color FROM ScheduleMaster S INNER JOIN BusMaster B ON S.BusMaster_ID = B.ID INNER JOIN ScheduleType ST On ST.ID = S.Type_ID Where BusMaster_ID = " + busid;
}
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(qry1, conn);
da.Fill(ds);
conn.Close();
int length = ds.Tables[0].Rows.Count;
calendar1.CalendarEvents.Clear();
for (int i = 0; i < length; i++)
{
DateTime lastdate = Convert.ToDateTime(ds.Tables[0].Rows[i]["LastDate"].ToString());
DateTime reminderDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["ReminderDate"].ToString());
while (lastdate >= reminderDate)
{
Appointment event1 = new Appointment();
event1.StartTime = reminderDate;
event1.EndTime = reminderDate;
event1.BackColor = System.Drawing.ColorTranslator.FromHtml(ds.Tables[0].Rows[i]["Color"].ToString());
event1.Text = ds.Tables[0].Rows[i]["Name"].ToString();
calendar1.CalendarEvents.Add(event1);
reminderDate = reminderDate.AddDays(1);
dateTimePicker1.Text = DateTime.Today.ToShortDateString();
calendar1.CurrentDate = DateTime.Today;
}
}
}
private void calendar1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (calendar1.SelectedAppointment != null)
{
string[] value = calendar1.SelectedAppointment.Text.Split('-');
string type = value[0];
string name = value[1];
DialogResult dr = MessageBox.Show("Do you Want to Re-Schedule it?", "Re-Schedule", MessageBoxButtons.YesNo);
if (dr == DialogResult.Yes)
{
int busid = General.GetBusID(name, General.BUS);
int typeid = General.GetBusID(type, General.SCHEDULETYPE);
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
string qry1 = "SELECT * FROM ScheduleMaster Where BusMaster_ID = " + busid + " and type_id =" + typeid;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(qry1, conn);
da.Fill(ds);
conn.Close();
DateTime olddate = Convert.ToDateTime(ds.Tables[0].Rows[0]["OldDate"].ToString());
DateTime lastdate = Convert.ToDateTime(ds.Tables[0].Rows[0]["LastDate"].ToString());
DateTime reminderDate = Convert.ToDateTime(ds.Tables[0].Rows[0]["ReminderDate"].ToString());
int day = Convert.ToInt32(ds.Tables[0].Rows[0]["Day"].ToString());
int Month = Convert.ToInt32(ds.Tables[0].Rows[0]["Month"].ToString());
int Year = Convert.ToInt32(ds.Tables[0].Rows[0]["Year"].ToString());
string Format = ds.Tables[0].Rows[0]["Format"].ToString();
int remidnerday = Convert.ToInt32(ds.Tables[0].Rows[0]["ReminderDay"].ToString());
olddate = lastdate;
if (Format == General.DAY)
{
lastdate = lastdate.AddDays(day);
}
else if (Format == General.MONTH)
{
lastdate = lastdate.AddMonths(Month);
}
else if (Format == General.YEAR)
{
lastdate = lastdate.AddYears(Year);
}
reminderDate = lastdate.AddDays(-1 * remidnerday);
string qry = "UPDATE ScheduleMaster SET ";
qry += " OldDate = '" + olddate.ToString("yyyy-MM-dd") + "',";
qry += " LastDate = '" + lastdate.ToString("yyyy-MM-dd") + "',";
qry += " ReminderDate = '" + reminderDate.ToString("yyyy-MM-dd") + "'";
qry += " WHERE BusMaster_ID =" + busid;
qry += " AND Type_ID=" + typeid;
conn.Open();
SqlCommand cmd = new SqlCommand(qry, conn);
cmd.ExecuteNonQuery();
conn.Close();
dateTimePicker1.Text = DateTime.Today.ToShortDateString();
calendar1.CurrentDate = DateTime.Today;
button1_Click(sender, e);
MessageBox.Show("Re-Scheduled Successfully");
cmbBusName_SelectedIndexChanged(sender, e);
}
else if (dr == DialogResult.No)
{
MessageBox.Show("Waiting for Rescheduled");
}
else
{
MessageBox.Show("else");
}
calendar1.SelectedAppointment = null;
}
}
private void bAdd_Click(object sender, EventArgs e)
{
try
{
int busid = General.GetBusID(cmbBusName.Text, General.BUS);
int type = General.GetBusID(cmbScheduleType.Text, General.SCHEDULETYPE);
int day = Convert.ToInt32(tbDay.Text);
int month = Convert.ToInt32(tbMonth.Text);
int year = Convert.ToInt32(tbYear.Text);
int recurring = 1;
int reminderday = Convert.ToInt32(tbRemind.Text);
DateTime olddate = Convert.ToDateTime(dateTimePicker1.Text);
DateTime lastdate = olddate;
string format = "";
if (rbDay.Checked)
{
lastdate = lastdate.AddDays(Convert.ToInt32(tbDay.Text));
format = General.DAY;
}
else if (rbMonth.Checked)
{
lastdate = lastdate.AddMonths(Convert.ToInt32(tbMonth.Text));
format = General.MONTH;
}
else if (rbYear.Checked)
{
lastdate = lastdate.AddYears(Convert.ToInt32(tbYear.Text));
format = General.YEAR;
}
DateTime reminderdate = lastdate.AddDays((-1 * Convert.ToDouble(tbRemind.Text)));
string qry = "INSERT INTO ScheduleMaster VALUES( ";
qry += busid + ",";
qry += type + ",";
qry += day + ",";
qry += month + ",";
qry += year + ",";
qry += recurring + ",";
qry += reminderday + ",";
qry += "'" + olddate.ToString("yyyy-MM-dd") + "',";
qry += "'" + lastdate.ToString("yyyy-MM-dd") + "',";
qry += "'" + reminderdate.ToString("yyyy-MM-dd") + "',";
qry += "'" + format + "')";
string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["BUS"].ConnectionString.ToString();
SqlConnection conn = new SqlConnection(connstring);
conn.Open();
SqlCommand cmd = new SqlCommand(qry, conn);
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show(General.SUCCESS_MSG);
BindDetails(1);
ClearResult();
this.SelectNextControl(cmbBusName, true, true, true, true);
}
catch (Exception ex)
{
if (ex.Message.IndexOf("IX_ScheduleMaster") > 0)
{
MessageBox.Show("Data Already Exists");
}
}
}
