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"); } } }