private void btnSave_Click(object sender, EventArgs e)
{
int category_id = AddQuation.getCatId(this.cmbInfoCategory.Text);
string answer = this.txtInfoAnswer.Text;
string sql = "UPDATE Quations SET answer = '" + answer + "', category_id = " + category_id;
if (ofd.FileName != "")
{
string imageName = DateTime.Now.ToString("yyyyMMddHHssmm") + ofd.SafeFileName;
Extentions.ImageUpload(ofd, imageName, "Quations_Images");
Extentions.DeleteFile(this.seleced.Image, "Quations_Images");
sql += ", image = '" + imageName + "' WHERE id = " + this.seleced.Id;
}
else
{
sql += " WHERE id = " + this.seleced.Id;
}
SQLiteCommand com = new SQLiteCommand(sql, con);
con.Open();
com.ExecuteNonQuery();
con.Close();
//this.fillPanel(this.getAllQuations());
int id = AddQuation.getCatId(this.cmbCategory.Text);
this.fillPanel(this.getAllQuations(null, id));
this.grpInfo.Visible = false;
}
private void delete(object sender, EventArgs e)
{
if (DialogResult.Yes == MessageBox.Show("Also 'Delete' all Tickets with releted this category", "Delete Quation", MessageBoxButtons.YesNo))
{
this.selectedNumber = null;
this.pnlAllQuations.Controls.Clear();
Button btn = sender as Button;
string[] data = btn.Name.Split('_');
deleteTickets(Convert.ToInt32(data[0]));
string sql = "DELETE FROM Quations WHERE id = " + Convert.ToInt32(data[0]);
SQLiteCommand com = new SQLiteCommand(sql, con);
con.Open();
com.ExecuteNonQuery();
con.Close();
Extentions.DeleteFile(data[1], "Quations_Images");
this.fillPanel(this.getAllQuations());
}
}
void deleteAllQuations(int catId)
{
List <int> ticket_ids = new List <int>();
SQLiteDataAdapter da = new SQLiteDataAdapter();
DataTable dt = new DataTable();
string sql = "SELECT * FROM Quations WHERE category_id = " + catId;
SQLiteCommand com = new SQLiteCommand(sql, con);
da.SelectCommand = com;
da.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
sql = "DELETE FROM Quations WHERE category_id = " + Convert.ToInt32(row["category_id"]);
com.CommandText = sql;
com.Connection = con;
con.Open();
com.ExecuteNonQuery();
con.Close();
Extentions.DeleteFile(row["image"].ToString(), "Quations_Images");
using (SQLiteConnection conn = new SQLiteConnection(Login.connection))
{
sql = "SELECT ticket_id FROM P_TicketAndQuation WHERE quation_id = " + Convert.ToInt32(row["id"]);
SQLiteCommand command = new SQLiteCommand(sql, conn);
conn.Open();
SQLiteDataReader dr = command.ExecuteReader();
while (dr.Read())
{
int ticket_id = Convert.ToInt32(dr[0]);
if (!ticket_ids.Contains(ticket_id))
{
ticket_ids.Add(ticket_id);
}
}
con.Close();
}
}
}
if (ticket_ids.Count > 0)
{
foreach (int id in ticket_ids)
{
using (SQLiteConnection connection = new SQLiteConnection(Login.connection))
{
string query = "DELETE FROM P_TicketAndQuation WHERE ticket_id = " + id;
SQLiteCommand command = new SQLiteCommand(query, connection);
connection.Open();
command.ExecuteNonQuery();
}
using (SQLiteConnection connection = new SQLiteConnection(Login.connection))
{
string query = "DELETE FROM Tickets WHERE id = " + id;
SQLiteCommand command = new SQLiteCommand(query, connection);
connection.Open();
command.ExecuteNonQuery();
}
}
}
}