public void dailyReportInsert(TextBox income, TextBox expense, TextBox balance, string cash) { try { SqlConnection con = new DBConnection().getConnection(); con.Open(); //INSERT EXPENSE VALUES SqlCommand cmd = new SqlCommand("INSERT INTO DailyReport VALUES(@income, @expense,@bal,@cash,@date)", con); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqlParameter("@income", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@expense", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@bal", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@cash", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@date", SqlDbType.VarChar)); cmd.Parameters["@income"].Value = income.Text; cmd.Parameters["@expense"].Value = expense.Text; cmd.Parameters["@bal"].Value = balance.Text; cmd.Parameters["@cash"].Value = cash; cmd.Parameters["@date"].Value = dataAccess.Sdate; cmd.ExecuteNonQuery(); con.Close(); dataAccess.Description = "Recorded Income Report"; dataAccess.Activities(); } catch (Exception Ex) { MessageBox.Show(Ex.Message + "\nCould not Save", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public static void ShowSecurityQuestions() { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@AdminID", Resetuserid); cmd.CommandText = "SELECT *FROM UserSecurity WHERE AdminID =@AdminID"; SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Question1 = reader["Question1"].ToString(); Answer1 = reader["Answer1"].ToString(); Question2 = reader["Question2"].ToString(); Answer2 = reader["Answer2"].ToString(); } } reader.Close(); con.Close(); } catch (Exception ex) { throw new Exception(ex.Message + "\nshow questions error"); } }
public static void AdminlogsToday(DataGridView log) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); DataTable dtToday = new DataTable(); SqlDataAdapter daToday = new SqlDataAdapter( "SELECT *from AdminLog WHERE Date =@date", con); daToday.SelectCommand.Parameters.AddWithValue("@date", SqlDbType.VarChar).Value = dataAccess.Sdate; daToday.Fill(dtToday); log.Rows.Clear(); foreach (DataRow drToday in dtToday.Rows) { //DISPLAY THE RECORDS IN THE GRID VIEW CONTROL AS THEY ARE SEARCH IN THE DATABASE int n = log.Rows.Add(); log.Rows[n].Cells[0].Value = drToday[0].ToString(); log.Rows[n].Cells[1].Value = drToday[1].ToString(); log.Rows[n].Cells[2].Value = drToday[2].ToString(); log.Rows[n].Cells[3].Value = drToday[3]; log.Rows[n].Cells[4].Value = string.Format("{0:MM/dd/yyyy}", drToday[4]); } } catch (Exception Ex) { con.Close(); MessageBox.Show(Ex.Message + "\nLog Event Error", "Cash Desk", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public static void AllReportBetweenDate(DataGridView view, DateTimePicker from, DateTimePicker to) { SqlConnection con = new DBConnection().getConnection(); try { SqlDataAdapter Tda = new SqlDataAdapter("SELECT *FROM All_Sales_Report WHERE Date_Sold BETWEEN @datefrom AND @dateto ORDER BY Report_Date,Item DESC", con); Tda.SelectCommand.Parameters.AddWithValue("@datefrom", SqlDbType.VarChar).Value = from.Value.ToString("MM/dd/yyyy"); Tda.SelectCommand.Parameters.AddWithValue("@dateto", SqlDbType.VarChar).Value = to.Value.ToString("MM/dd/yyyy"); DataTable dt = new DataTable(); Tda.Fill(dt); Total = 0; view.Rows.Clear(); foreach (DataRow dr in dt.Rows) { int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = dr[0].ToString(); view.Rows[n].Cells[1].Value = dr[1].ToString(); view.Rows[n].Cells[2].Value = dr[2].ToString(); int today = Convert.ToInt32(dr[3]); Total = Total + today; view.Rows[n].Cells[3].Value = string.Format("{0:00.#0}", dr[3]); view.Rows[n].Cells[4].Value = string.Format("{0:MM/dd/yyyy}", dr[4]); view.Rows[n].Cells[5].Value = string.Format("{0:MM/dd/yyyy}", dr[5]); } } catch (Exception ex) { con.Close(); throw new ExceptionHandling("Sales report failed to Load\nTry again", ex); } }
public static void DisplayReport(DataGridView view) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlDataAdapter da = new SqlDataAdapter("SELECT *FROM All_Sales_Report ORDER BY Report_Date,Item DESC", con); DataTable dt = new DataTable(); da.Fill(dt); Total = 0; view.Rows.Clear(); foreach (DataRow dr in dt.Rows) { int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = dr[0].ToString(); view.Rows[n].Cells[1].Value = dr[1].ToString(); view.Rows[n].Cells[2].Value = dr[2].ToString(); int today = Convert.ToInt32(dr[3]); Total = Total + today; view.Rows[n].Cells[3].Value = string.Format("{0:00.#0}", dr[3]); view.Rows[n].Cells[4].Value = string.Format("{0:MM/dd/yyyy}", dr[4]); view.Rows[n].Cells[5].Value = string.Format("{0:MM/dd/yyyy}", dr[5]); } }catch (Exception ex) { con.Close(); throw new ExceptionHandling("Sales report failed to Load\nTry again", ex); } }
public static void DisplaySelectedReport(DataGridView view, string search) { SqlConnection con = new DBConnection().getConnection(); try { string query = "SELECT *FROM All_Sales_Report WHERE Item =@search ORDER BY Item,Report_Date DESC"; SqlDataAdapter da = new SqlDataAdapter(query, con); da.SelectCommand.Parameters.AddWithValue("@search", SqlDbType.VarChar).Value = search; DataTable dt = new DataTable(); da.Fill(dt); Total = 0; view.Rows.Clear(); foreach (DataRow dr in dt.Rows) { int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = dr[0].ToString(); view.Rows[n].Cells[1].Value = dr[1].ToString(); view.Rows[n].Cells[2].Value = dr[2].ToString(); int today = Convert.ToInt32(dr[3]); Total = Total + today; view.Rows[n].Cells[3].Value = string.Format("{0:00.#0}", dr[3]); view.Rows[n].Cells[4].Value = string.Format("{0:MM/dd/yyyy}", dr[4]); view.Rows[n].Cells[5].Value = string.Format("{0:MM/dd/yyyy}", dr[5]); } } catch (Exception ex) { con.Close(); throw new ExceptionHandling("Sales report failed to Load\nTry again", ex); } }
public void ReportInsert(TextBox income, TextBox expense, TextBox cash, DateTimePicker date) { try { SqlConnection con = new DBConnection().getConnection(); con.Open(); //INSERT EXPENSE VALUES long inc = Convert.ToInt32(income.Text); long exp = Convert.ToInt32(expense.Text); long balance = inc - exp; SqlCommand cmd = new SqlCommand("INSERT INOT DailyReport VALUES(@income, @expense,@bal,@cash,@date)", con); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); cmd.Parameters.Add(new SqlParameter("@income", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@expense", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@bal", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@cash", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@date", SqlDbType.VarChar)); cmd.Parameters["@income"].Value = income.Text; cmd.Parameters["@expense"].Value = expense.Text; cmd.Parameters["@bal"].Value = balance; cmd.Parameters["@cash"].Value = cash.Text; cmd.Parameters["@date"].Value = date.Value; cmd.ExecuteNonQuery(); con.Close(); dataAccess.Description = "Recorded Income Report"; dataAccess.Activities(); } catch (Exception Ex) { throw new Exception("Error At ReportInsert Class Level ", Ex); } }
public static void TodayReport(DataGridView today, TextBox fee) { SqlConnection con = new DBConnection().getConnection(); try { total = 0; con.Open(); DataTable dtToday = new DataTable(); SqlDataAdapter daToday = new SqlDataAdapter("SELECT *FROM Cash WHERE Pay_Date =@paydate AND Fee_Name =@fee", con); daToday.SelectCommand.Parameters.AddWithValue("@paydate", SqlDbType.VarChar).Value = RecordDate; daToday.SelectCommand.Parameters.AddWithValue("@fee", SqlDbType.VarChar).Value = fee.Text; daToday.Fill(dtToday); today.Rows.Clear(); foreach (DataRow drToday in dtToday.Rows) { int n = today.Rows.Add(); today.Rows[n].Cells[0].Value = drToday[0].ToString(); today.Rows[n].Cells[1].Value = drToday[1].ToString(); today.Rows[n].Cells[2].Value = drToday[2].ToString(); today.Rows[n].Cells[3].Value = drToday[3].ToString(); //GET THE FEE NAME Feename = drToday[3].ToString(); today.Rows[n].Cells[4].Value = drToday[4].ToString(); long amount = Convert.ToInt64(drToday[5]); total = total + amount; today.Rows[n].Cells[5].Value = string.Format("{0:00.#0}", drToday[5]); today.Rows[n].Cells[6].Value = string.Format("{0:MM/dd/yyyy}", drToday[6]); RecordDate = drToday[6].ToString(); today.Rows[n].Cells[7].Value = drToday[7].ToString(); today.Rows[n].Cells[8].Value = drToday[8].ToString(); today.Rows[n].Cells[9].Value = string.Format("{0:MM/dd/yyyy}", drToday[9]); today.Rows[n].Cells[10].Value = drToday[10].ToString(); } con.Close(); } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("today search error", Ex); } }
public static void DispayStudentNames(DataGridView view, string name) { try { SqlConnection con = new DBConnection().getConnection(); con.Open(); string Query; if (string.IsNullOrEmpty(name)) { Query = Query = "SELECT Name_id,First_Name,Last_Name FROM Names"; SqlDataAdapter da = new SqlDataAdapter(Query, con); DataTable dt = new DataTable(); da.Fill(dt); view.Rows.Clear(); foreach (DataRow dr in dt.Rows) { int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = dr[0].ToString(); view.Rows[n].Cells[1].Value = dr[1].ToString(); view.Rows[n].Cells[2].Value = dr[2].ToString(); } con.Close(); } else { Query = "SELECT Name_id,First_Name,Last_Name FROM Names WHERE First_Name LIKE'%' + @name + '%' OR Last_Name LIKE'%' + @name + '%' ORDER BY First_Name "; SqlDataAdapter da = new SqlDataAdapter(Query, con); da.SelectCommand.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = name; DataTable dt = new DataTable(); da.Fill(dt); view.Rows.Clear(); foreach (DataRow dr in dt.Rows) { int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = dr[0].ToString(); view.Rows[n].Cells[1].Value = dr[1].ToString(); view.Rows[n].Cells[2].Value = dr[2].ToString(); } con.Close(); } } catch (Exception) { } }
public static void PaymentSearch(DataGridView search, TextBox searchText) { SqlConnection con = new DBConnection().getConnection(); try { total = 0; con.Open(); var query = "SELECT *FROM Cash WHERE Names like '%' +@search +'%' " + " OR Class LIKE '%' + @search +'%' OR Amount LIKE '%' + @search +'%'" + " OR Fee_Name LIKE '%' + @search + '%' OR Term LIKE '%' + @search + '%'" + " OR Fee_Type LIKE '%' + @search + '%' "; SqlDataAdapter danow = new SqlDataAdapter(query, con); danow.SelectCommand.Parameters.AddWithValue("@search", SqlDbType.VarChar).Value = searchText.Text; DataTable dtnow = new DataTable(); danow.Fill(dtnow); search.Rows.Clear(); foreach (DataRow drnow in dtnow.Rows) { int n = search.Rows.Add(); search.Rows[n].Cells[0].Value = drnow[0].ToString(); search.Rows[n].Cells[1].Value = drnow[1].ToString(); search.Rows[n].Cells[2].Value = drnow[2].ToString(); search.Rows[n].Cells[3].Value = drnow[3].ToString(); search.Rows[n].Cells[4].Value = drnow[4].ToString(); long amount = Convert.ToInt64(drnow[5]); total = total + amount; search.Rows[n].Cells[5].Value = string.Format("{0:00.#0}", drnow[5]); search.Rows[n].Cells[6].Value = string.Format("{0:MM/dd/yyyy}", drnow[6]); search.Rows[n].Cells[7].Value = drnow[7].ToString(); search.Rows[n].Cells[8].Value = drnow[8].ToString(); search.Rows[n].Cells[9].Value = string.Format("{0:MM/dd/yyyy}", drnow[9]); search.Rows[n].Cells[10].Value = drnow[10].ToString(); } con.Close(); } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("search error", Ex); } }
public static void DeletFee(string feeid) { SqlConnection con = new DBConnection().getConnection(); try{ con.Open(); SqlCommand cmd = new SqlCommand("DELETE FROM SetFees WHERE Set_Id ='" + feeid + "'"); cmd.ExecuteNonQuery(); con.Close(); } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("Fee could not be update", Ex); } }
public static void todaySeacrchRecords(DataGridView search, TextBox searchtext) { SqlConnection con = new DBConnection().getConnection(); try { total = 0; con.Open(); DataTable dtnow = new DataTable(); SqlDataAdapter danow = new SqlDataAdapter("SELECT *FROM Cash WHERE Pay_Date IN(SELECT Pay_Date FROM Cash WHERE Pay_Date=@today) AND Names LIKE '%' + @search +'%' " + " OR Fee_Name LIKE '%' + @search + '%' OR Class=@search ", con); danow.SelectCommand.Parameters.AddWithValue("@search", SqlDbType.Text).Value = searchtext.Text; danow.SelectCommand.Parameters.AddWithValue("@today", SqlDbType.VarChar).Value = dataAccess.Sdate.ToString(); danow.Fill(dtnow); search.Rows.Clear(); foreach (DataRow drnow in dtnow.Rows) { int n = search.Rows.Add(); search.Rows[n].Cells[0].Value = drnow[0].ToString(); search.Rows[n].Cells[1].Value = drnow[1].ToString(); search.Rows[n].Cells[2].Value = drnow[2].ToString(); search.Rows[n].Cells[3].Value = drnow[3].ToString(); search.Rows[n].Cells[4].Value = drnow[4].ToString(); long amount = Convert.ToInt64(drnow[5]); total = total + amount; search.Rows[n].Cells[5].Value = string.Format("{0:00.#0}", drnow[5]); search.Rows[n].Cells[6].Value = string.Format("{0:MM/dd/yyyy}", drnow[6]); //GET THE DATE OF THE RECORDS RecordDate = drnow[6].ToString(); search.Rows[n].Cells[7].Value = drnow[7].ToString(); search.Rows[n].Cells[8].Value = drnow[8].ToString(); search.Rows[n].Cells[9].Value = string.Format("{0:MM/dd/yyyy}", drnow[9]); search.Rows[n].Cells[10].Value = drnow[10].ToString(); } con.Close(); } catch (Exception) { con.Close(); MessageBox.Show("There was error in the search"); } }
public static void DeleteDailyReport(string id) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlCommand cmdCommand = new SqlCommand("DELETE DailyFeesPayReport WHERE ID='" + id + "'", con); cmdCommand.ExecuteNonQuery(); con.Close(); } catch (Exception Ex) { con.Close(); MessageBox.Show(Ex.Message + "\nOperation Not Successful", "Cash Desk", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public static void DispayItems(DataGridView view, string item) { try { SqlConnection con = new DBConnection().getConnection(); con.Open(); string Query; if (string.IsNullOrEmpty(item)) { Query = Query = "SELECT Item_Id,Item FROM SetSales"; SqlDataAdapter da = new SqlDataAdapter(Query, con); DataTable dt = new DataTable(); da.Fill(dt); view.Rows.Clear(); foreach (DataRow dr in dt.Rows) { int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = dr[0].ToString(); view.Rows[n].Cells[1].Value = dr[1].ToString(); } con.Close(); } else { Query = "SELECT Item_Id,Item FROM SetSales WHERE Item LIKE'%' + @item + '%'"; SqlDataAdapter da = new SqlDataAdapter(Query, con); da.SelectCommand.Parameters.AddWithValue("@item", SqlDbType.VarChar).Value = item; DataTable dt = new DataTable(); da.Fill(dt); view.Rows.Clear(); foreach (DataRow dr in dt.Rows) { int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = dr[0].ToString(); view.Rows[n].Cells[1].Value = dr[1].ToString(); } con.Close(); } } catch (Exception ex) { } }
public static void Report(DataGridView view, string feename, DateTimePicker datepick) { SqlConnection con = new DBConnection().getConnection(); try { total = 0; con.Open(); DataTable dts = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Cash WHERE Fee_Name=@feename and Pay_Date=@paydate", con); da.SelectCommand.Parameters.AddWithValue("@paydate", SqlDbType.VarChar).Value = datepick.Value.ToString("MM/dd/yyyy"); da.SelectCommand.Parameters.AddWithValue("@feename", SqlDbType.VarChar).Value = feename; da.Fill(dts); view.Rows.Clear(); foreach (DataRow drRec in dts.Rows) { //DISPLAY THE RECORDS IN THE GRID VIEW C int n = view.Rows.Add(); view.Rows[n].Cells[0].Value = drRec[0].ToString(); view.Rows[n].Cells[1].Value = drRec[1].ToString(); view.Rows[n].Cells[2].Value = drRec[2].ToString(); view.Rows[n].Cells[3].Value = drRec[3].ToString(); view.Rows[n].Cells[4].Value = drRec[4].ToString(); long amount = Convert.ToInt64(drRec[5]); total = total + amount; view.Rows[n].Cells[5].Value = string.Format("{0:00.#0}", drRec[5]); view.Rows[n].Cells[6].Value = string.Format("{0:MM/dd/yyyy}", drRec[6]); RecordDate = drRec[6].ToString(); view.Rows[n].Cells[7].Value = drRec[7].ToString(); view.Rows[n].Cells[8].Value = drRec[8].ToString(); view.Rows[n].Cells[9].Value = string.Format("{0:MM/dd/yyyy}", drRec[9]); view.Rows[n].Cells[10].Value = drRec[10]; PaymentRecordSearch.total = total; } con.Close(); } catch (Exception ex) { con.Close(); throw new ExceptionHandling("there was error in the search ", ex); } }
public static void PaymentSearch(DataGridView search, DateTimePicker pick1, DateTimePicker pick2) { SqlConnection con = new DBConnection().getConnection(); try { total = 0; con.Open(); var query = "SELECT *FROM Cash WHERE Pay_Date BETWEEN @datefrom AND @dateto "; DataTable dtS = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(query, con); da.SelectCommand.Parameters.AddWithValue("@datefrom", SqlDbType.VarChar).Value = pick1.Value; da.SelectCommand.Parameters.AddWithValue("@dateto", SqlDbType.VarChar).Value = pick2.Value; da.Fill(dtS); search.Rows.Clear(); foreach (DataRow drS in dtS.Rows) { int n = search.Rows.Add(); search.Rows[n].Cells[0].Value = drS[0].ToString(); search.Rows[n].Cells[1].Value = drS[1].ToString(); search.Rows[n].Cells[2].Value = drS[2].ToString(); search.Rows[n].Cells[3].Value = drS[3].ToString(); search.Rows[n].Cells[4].Value = drS[4].ToString(); long amount = Convert.ToInt64(drS[5]); total = total + amount; search.Rows[n].Cells[5].Value = string.Format("{0:00.#0}", drS[5]); search.Rows[n].Cells[6].Value = string.Format("{0:MM/dd/yyyy}", drS[6]); search.Rows[n].Cells[7].Value = drS[7].ToString(); search.Rows[n].Cells[8].Value = drS[8].ToString(); search.Rows[n].Cells[9].Value = string.Format("{0:MM/dd/yyyy}", drS[9]); search.Rows[n].Cells[10].Value = drS[10].ToString(); } con.Close(); } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("search error", Ex); } }
public static void DeleleAdminLogs() { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand("Delete AdminLog", con); cmd.ExecuteNonQuery(); con.Close(); dataAccess.Description = "Deleted Admin Logs"; dataAccess.Activities(); } catch (Exception Ex) { con.Close(); MessageBox.Show(Ex.Message + "\nLog Event Error", "Cash Desk", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public static void PaymentSearch(DataGridView search, DateTimePicker date1, DateTimePicker date2, ComboBox feeBox) { SqlConnection con = new DBConnection().getConnection(); try { total = 0; con.Open(); DataTable dtnow = new DataTable(); SqlDataAdapter danow = new SqlDataAdapter("SELECT *FROM Cash WHERE Fee_Name = @feename AND Pay_Date BETWEEN @datefrom AND @dateto" + " ORDER BY ID DESC", con); danow.SelectCommand.Parameters.AddWithValue("@feename", SqlDbType.VarChar).Value = feeBox.Text; danow.SelectCommand.Parameters.AddWithValue("@datefrom", SqlDbType.VarChar).Value = date1.Value.ToString("d"); danow.SelectCommand.Parameters.AddWithValue("@dateto", SqlDbType.VarChar).Value = date2.Value.ToString("d"); danow.Fill(dtnow); search.Rows.Clear(); foreach (DataRow drnow in dtnow.Rows) { int n = search.Rows.Add(); search.Rows[n].Cells[0].Value = drnow[0].ToString(); search.Rows[n].Cells[1].Value = drnow[1].ToString(); search.Rows[n].Cells[2].Value = drnow[2].ToString(); search.Rows[n].Cells[3].Value = drnow[3].ToString(); search.Rows[n].Cells[4].Value = drnow[4].ToString(); long amount = Convert.ToInt64(drnow[5]); total = total + amount; search.Rows[n].Cells[5].Value = string.Format("{0:00.#0}", drnow[5]); search.Rows[n].Cells[6].Value = string.Format("{0:MM/dd/yyyy}", drnow[6]); search.Rows[n].Cells[7].Value = drnow[7].ToString(); search.Rows[n].Cells[8].Value = drnow[8].ToString(); search.Rows[n].Cells[9].Value = string.Format("{0:MM/dd/yyyy}", drnow[9]); search.Rows[n].Cells[10].Value = drnow[10].ToString(); } con.Close(); } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("search error", Ex); } }
public static void CreateAdmin(string user, string pass) { SqlConnection con = new DBConnection().getConnection(); try { if (ForgottenPassword == true) { UpdateAdminUsername(Admin, user); UpdateAdminPass(pass); ForgottenPassword = false; } else if (ResetPassword == true) { UpdateAdminPass(pass); ResetPassword = false; } else { CheckPrvilege(); CheckUserName(user); con.Open(); DataTable dt = new DataTable(); DataSet ds = new DataSet(); SqlCommand cmdcreateCommand = new SqlCommand(); cmdcreateCommand.Connection = con; cmdcreateCommand.Parameters.Clear(); cmdcreateCommand.Parameters.AddWithValue("@UserName", user); cmdcreateCommand.Parameters.AddWithValue("@Password", PasswordEncryptor.Encrypt(pass)); cmdcreateCommand.CommandText = "INSERT INTO Admin(UserName,Password) VALUES(@UserName,@Password)"; cmdcreateCommand.ExecuteNonQuery(); con.Close(); } } catch (Exception Ex) { con.Close(); throw new Exception(Ex.Message + "\nAdmin Not Created"); } }
public void deleteReport(Label id) { SqlConnection con = new DBConnection().getConnection(); con.Open(); SqlCommand cmd = new SqlCommand("DELETE DailyReport WHERE Report_Id='" + id.Text + "'", con); cmd.ExecuteNonQuery(); con.Close(); dataAccess.Description = "Deleted Income Report"; dataAccess.Activities(); }
public static void DeleteReport(int reportid) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar)); cmd.Parameters["@id"].Value = reportid; cmd.CommandText = "DELETE All_Sales_Report WHERE Report_Id=@id"; cmd.ExecuteNonQuery(); con.Close(); } catch (Exception ex) { con.Close(); throw new ExceptionHandling("Delete Error", ex); } }
public static void LogIn(string admin, string pass) { SqlConnection con = new DBConnection().getConnection(); try { if (string.IsNullOrEmpty(admin)) { throw new Exception("Enter Username"); } else if (string.IsNullOrEmpty(pass)) { throw new Exception("Enter Password"); } con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@UserName", admin); cmd.Parameters.AddWithValue("@Password", PasswordEncryptor.Encrypt(pass)); cmd.CommandText = "SELECT *FROM Admin WHERE UserName =@UserName AND Password=@Password "; SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Admin = reader.GetString(1); AdminPass = reader.GetString(2); } } reader.Close(); con.Close(); } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("Username or Password was not provided\n Login Not Successful", Ex); } }
public static void DeleteReport(DateTimePicker deletedate) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("@date", SqlDbType.VarChar)); cmd.Parameters["@date"].Value = deletedate.Value.ToString("MM/dd/yyyy"); cmd.CommandText = "DELETE All_Sales_Report WHERE Report_Date=@date"; cmd.ExecuteNonQuery(); con.Close(); } catch (Exception ex) { con.Close(); throw new ExceptionHandling("Delete Error", ex); } }
public void setNewSession(DateTimePicker startyear, DateTimePicker endyear, DateTimePicker setDate) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlDataAdapter da = new SqlDataAdapter("SELECT Start_Year,End_year FROM Session WHERE Start_Year = @startyear", con); da.SelectCommand.Parameters.AddWithValue("@startyear", SqlDbType.VarChar).Value = startyear.Value.ToString(); DataTable dt = new DataTable(); da.Fill(dt); int a = dt.Rows.Count; if (a > 0) { throw new Exception("Session already Exist"); } else { SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.Parameters.AddWithValue("@Start_Year", startyear.Value); cmd.Parameters.AddWithValue("@End_Year", endyear.Value); cmd.Parameters.AddWithValue("@Set_Date", setDate.Value.ToString()); cmd.CommandText = "INSERT INTO Session VALUES(@Start_Year,@End_Year,@Set_Date)"; cmd.ExecuteNonQuery(); MessageBox.Show("Session Set successfully", "Session"); dataAccess.Description = "Created New Session"; dataAccess.Activities(); con.Close(); } } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("Session Already Exist or Date format is invalid", Ex); } }
public static void makeSale(int itemid, int qty_Avail, string Quantity, string cost, string total, DateTimePicker dDate, string discount, int credit = 0, string nameid = null) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Item_Id", itemid); cmd.Parameters.AddWithValue("@UserName", AdminLog.Admin); cmd.Parameters.AddWithValue("@Qty", Quantity); cmd.Parameters.AddWithValue("@Cost", cost); cmd.Parameters.AddWithValue("@Discount", discount); cmd.Parameters.AddWithValue("@Amount", total); cmd.Parameters.AddWithValue("@Sold_Date", dDate.Value.ToString("MM/dd/yyy")); cmd.Parameters.AddWithValue("@Qty_Avail", qty_Avail); if (nameid == null) { cmd.CommandText = "INSERT INTO Sales(Item_Id,UserName,Qty,Cost,Discount,Amount,Sold_Date,Qty_Avail) VALUES(@Item_Id,@UserName,@Qty,@Cost,@Discount,@Amount,@Sold_Date,@Qty_Avail)"; } else { cmd.Parameters.AddWithValue("@Name_id", nameid); cmd.CommandText = "INSERT INTO Sales(Item_Id,UserName,Qty,Cost,Discount,Amount,Sold_Date,Name_id,Qty_Avail) VALUES(@Item_Id,@UserName,@Qty,@Cost,@Discount,@Amount,@Sold_Date,@Name_id,@Qty_Avail)"; } cmd.ExecuteNonQuery(); if (credit != 0) { SqlDataAdapter da = new SqlDataAdapter("SELECT Max(Sales_Id) FROM Sales ", con); DataTable dt = new DataTable(); da.Fill(dt); var saleId = 0; foreach (DataRow dr in dt.Rows) { saleId = Int32.Parse(dr[0].ToString()); } CreditSales creditsale = new CreditSales(); CreditSales.RecordCreditor(saleId, creditsale.GetCreditorFirstname(), creditsale.GetCreditorLastname(), creditsale.GetCreditorContact()); } SaleMade = true; con.Close(); } catch (Exception Ex) { throw new ExceptionHandling("An Error Occurred", Ex); } }
public void setFees(string section, string feename, string amount, int install, string dateset) { SqlConnection con = new DBConnection().getConnection(); try { con.Open(); //GET THE SESSION ID FROM SESSION TABLE SqlCommand cmd = new SqlCommand();//"INSERT INTO SetFees VALUES('" + id + "','" + section + "', '" + feename + "','" + amount + "','" + install + "','" + dateset + "' )", con); cmd.Connection = con; cmd.Parameters.Clear(); cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@section", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@feename", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@amount", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@install", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@dateset", SqlDbType.VarChar)); cmd.Parameters["@id"].Value = dataAccess.GetSesseionID(); cmd.Parameters["@section"].Value = section; cmd.Parameters["@feename"].Value = feename; cmd.Parameters["@amount"].Value = amount; cmd.Parameters["@install"].Value = install; cmd.Parameters["@dateset"].Value = dateset; cmd.CommandText = "INSERT INTO SetFees VALUES(@id , @section , @feename , @amount , @install , @dateset )"; cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Fee Set Successful", "Fee Set", MessageBoxButtons.OK, MessageBoxIcon.Information); dataAccess.Description = "Set Fees Successfully"; dataAccess.Activities(); } catch (Exception Ex) { con.Close(); throw new ExceptionHandling("Create Academic Session First", Ex); } }
public static void DeleteSale(int itemid) { try{ SqlConnection con = new DBConnection().getConnection(); con.Open(); SqlCommand cmd = new SqlCommand("DELETE Sales WHERE Sales_Id =" + itemid + "", con); cmd.ExecuteNonQuery(); con.Close(); SaleDeleted = true; } catch (Exception ex) { throw new ExceptionHandling("Record could not be deleted", ex); } }
public static void GetlastReport(string feename, string date) { try { SqlConnection con = new DBConnection().getConnection(); if (string.IsNullOrEmpty(feename)) { throw new Exception("Select a Feename"); } if (string.IsNullOrEmpty(feename)) { throw new Exception("There is no such Fee for today"); } con.Open(); DataTable dtToday = new DataTable(); SqlDataAdapter daToday = new SqlDataAdapter( "SELECT Fee_Name,Date FROM DailyFeesPayReport WHERE Fee_Name=@feename AND Date=@date", con); daToday.SelectCommand.Parameters.AddWithValue("@feename", SqlDbType.VarChar).Value = feename; daToday.SelectCommand.Parameters.AddWithValue("@date", SqlDbType.VarChar).Value = date; daToday.Fill(dtToday); int x = dtToday.Rows.Count; if (x > 0) { con.Close(); throw new Exception(feename + " Has been already Reported"); } con.Close(); } catch (Exception Ex) { throw new Exception(Ex.Message); } }
public static string InsertDailyReport(string Fname, long amount, string date) { SqlConnection con = new DBConnection().getConnection(); try { //check if there is actually any file in the db before insertion if (string.IsNullOrEmpty(Fname)) { throw new Exception("No record Found"); } con.Open(); SqlCommand cmdCommand = new SqlCommand();// "INSERT INTO DailyFeesPayReport VALUES('" + Fname + "','" + amount + "','" + string.Format("{0:MM/dd/yyyy}", date )+ "')", con); cmdCommand.Connection = con; cmdCommand.Parameters.Clear(); cmdCommand.CommandType = CommandType.Text; cmdCommand.Parameters.Add(new SqlParameter("@fname", SqlDbType.VarChar)); cmdCommand.Parameters.Add(new SqlParameter("@amount", SqlDbType.VarChar)); cmdCommand.Parameters.Add(new SqlParameter("@date", SqlDbType.VarChar)); cmdCommand.Parameters["@fname"].Value = Fname; cmdCommand.Parameters["@amount"].Value = amount; cmdCommand.Parameters["@date"].Value = string.Format("{0:MM/dd/yyyy}", date); cmdCommand.CommandText = "INSERT INTO DailyFeesPayReport VALUES(@fname,@amount,@date)"; cmdCommand.ExecuteNonQuery(); con.Close(); MessageBox.Show(Fname + " Reported Successfully", "Cash Desk", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception Ex) { con.Close(); MessageBox.Show(Ex.Message + "\nOperation Not Successful", "Cash Desk", MessageBoxButtons.OK, MessageBoxIcon.Information); } return(addedFeename = Fname); }
public static void SearchDailyReport(DataGridView reports, DateTimePicker date1, DateTimePicker date2, TextBox search) { //CODE LATER SqlConnection con = new DBConnection().getConnection(); try { con.Open(); DataTable dtReport = new DataTable(); SqlDataAdapter daReport = new SqlDataAdapter("SELECT * FROM DailyFeesPayReport WHERE Date BETWEEN @datefrom AND @dateto " + "AND Fee_Name =@search ORDER BY ID DESC", con); daReport.SelectCommand.Parameters.AddWithValue("@datefrom", SqlDbType.VarChar).Value = date1.Value; daReport.SelectCommand.Parameters.AddWithValue("@dateto", SqlDbType.VarChar).Value = date2.Value; daReport.SelectCommand.Parameters.AddWithValue("@search", SqlDbType.VarChar).Value = search.Text; daReport.Fill(dtReport); reports.Rows.Clear(); Total = 0; foreach (DataRow dr in dtReport.Rows) { int n = reports.Rows.Add(); reports.Rows[n].Cells[0].Value = dr[0].ToString(); reports.Rows[n].Cells[1].Value = dr[1].ToString(); reports.Rows[n].Cells[2].Value = string.Format("{0:00.#0}", dr[2]); var total = Convert.ToInt32(dr[2]); Total = Total + total; reports.Rows[n].Cells[3].Value = string.Format("{0:MM/dd/yyyy}", dr[3]); } con.Close(); } catch (Exception) { con.Close(); } }