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