public static void Activities()
{
//TRACK THE ACTIVITIES OF THE USER
//
SqlConnection con = new DBConnection().getConnection();
try
{
con.Open();
SqlCommand cmdActivity = new SqlCommand();
cmdActivity.Connection = con;
cmdActivity.Parameters.Clear();
cmdActivity.Parameters.AddWithValue("@Description", Description);
cmdActivity.Parameters.AddWithValue("@Time", Form1.Dtime);
cmdActivity.Parameters.AddWithValue("@Date", Sdate);
cmdActivity.CommandText = "INSERT INTO Activity(Description,Time,Date) VALUES(@Description,@Time,@Date)";
cmdActivity.ExecuteNonQuery();
con.Close();
}
catch (Exception Ex) {
con.Close();
MessageBox.Show(Ex.Message, "Activity Error");
}
}
public static void PaymentSearch(DataGridView search, DateTimePicker date1, string term, ComboBox feeBox)
{
SqlConnection con = new DBConnection().getConnection();
try
{
total = 0;
if (string.IsNullOrEmpty(term))
{
con.Open();
DataTable dtnow = new DataTable();
SqlDataAdapter danow = new SqlDataAdapter(
"SELECT *FROM Cash WHERE Fee_Name = @feename AND Year LIKE @payYear + '____%'" +
" ORDER BY ID DESC", con);
danow.SelectCommand.Parameters.AddWithValue("@feename", SqlDbType.VarChar).Value = feeBox.Text;
danow.SelectCommand.Parameters.AddWithValue("@payYear", SqlDbType.VarChar).Value =
date1.Value.Year.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]);
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();
}
else
{
con.Open();
DataTable dtnow = new DataTable();
SqlDataAdapter danow = new SqlDataAdapter("SELECT *FROM Cash WHERE Fee_Name = @feename AND Term=@term AND Year LIKE @payYear + '____%'" +
" ORDER BY ID DESC", con);
danow.SelectCommand.Parameters.AddWithValue("@feename", SqlDbType.VarChar).Value = feeBox.Text;
danow.SelectCommand.Parameters.AddWithValue("@payYear", SqlDbType.VarChar).Value = date1.Value.Year.ToString();
danow.SelectCommand.Parameters.AddWithValue("@term", SqlDbType.VarChar).Value = term;
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);
}
}
//SEARCH BUTTON QUERY
public static void PaymentSearchButton(DataGridView search, DateTimePicker pick1, DateTimePicker pick2, TextBox name, string term, ComboBox feeBox)
{
try
{
total = 0;
SqlConnection con = new DBConnection().getConnection();
con.Open();
if (string.IsNullOrEmpty(term) && string.IsNullOrEmpty(feeBox.Text))
{
var query = "SELECT *FROM Cash WHERE Pay_Date BETWEEN @datefrom AND @dateto " +
" AND Names =@namesearch OR Class =@namesearch";
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.SelectCommand.Parameters.AddWithValue("@namesearch", SqlDbType.VarChar).Value = name.Text;
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();
}
else if (string.IsNullOrEmpty(feeBox.Text))
{
var query = "SELECT *FROM Cash WHERE Term =@termsearch AND Pay_Date BETWEEN @datefrom AND @dateto" +
" AND Names =namesearch";
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.SelectCommand.Parameters.AddWithValue("@namesearch", SqlDbType.VarChar).Value = name.Text;
da.SelectCommand.Parameters.AddWithValue("@termsearch", SqlDbType.VarChar).Value = term;
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();
}
else if (string.IsNullOrEmpty(term))
{
var query = "SELECT *FROM Cash WHERE Fee_Name =@feename AND Pay_Date BETWEEN @datefrom AND @dateto" +
" AND Names =@namesearch";
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.SelectCommand.Parameters.AddWithValue("@namesearch", SqlDbType.VarChar).Value = name.Text;
da.SelectCommand.Parameters.AddWithValue("@feename", SqlDbType.VarChar).Value = feeBox.Text;
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();
}
else
{
var query = "SELECT *FROM Cash WHERE Term = @termsearch AND Pay_Date BETWEEN @datefrom AND @dateto" +
" AND Names =@namesearch AND Fee_Name=@feename";
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.SelectCommand.Parameters.AddWithValue("@namesearch", SqlDbType.VarChar).Value = name.Text;
da.SelectCommand.Parameters.AddWithValue("@termsearch", SqlDbType.VarChar).Value = term;
da.SelectCommand.Parameters.AddWithValue("@feename", SqlDbType.VarChar).Value = feeBox.Text;
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)
{
throw new ExceptionHandling("Search error", Ex);
}
}
public static void SearchStockAllWithDate(DataGridView view, DateTimePicker datefrom, DateTimePicker dateto, string searchText)
{
try
{
if (string.IsNullOrEmpty(searchText))
{
SqlConnection con = new DBConnection().getConnection();
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.Parameters.Clear();
cmd.CommandText = " SELECT Stock_Id,Item,Quantity,Selling_Price, Total_Amount,Date_Added FROM SalesStock WHERE Date_Added BETWEEN @datefrom AND @dateto ORDER BY Date_Added DESC";
cmd.Parameters.Add(new SqlParameter("@search", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@datefrom", SqlDbType.VarChar));
cmd.Parameters["@datefrom"].Value = datefrom.Value.ToString();
cmd.Parameters["@dateto"].Value = dateto.Value.ToString();
SqlDataReader reader = cmd.ExecuteReader();
var total = 0;
view.Rows.Clear();
if (reader.HasRows)
{
while (reader.Read())
{
int n = view.Rows.Add();
view.Rows[n].Cells[0].Value = reader[0].ToString();
view.Rows[n].Cells[1].Value = reader[1].ToString();
view.Rows[n].Cells[2].Value = reader[2].ToString();
view.Rows[n].Cells[3].Value = reader[3].ToString();
view.Rows[n].Cells[4].Value = string.Format("{0:00}", reader[4]);
total = total + Convert.ToInt32(reader[4]);
view.Rows[n].Cells[5].Value = string.Format("{0:MM/dd/yyyy}", reader[5]);
Total = total;
}
}
reader.Close();
con.Close();
}
else
{
SqlConnection con = new DBConnection().getConnection();
SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.Parameters.Clear();
cmd.CommandText = " SELECT Stock_Id Item,Quantity,Selling_Price, Total_Amount,Date_Added FROM SalesStock WHERE Date_Added BETWEEN @datefrom AND @dateto Item=@search ORDER BY Date_Added DESC";
cmd.Parameters.Add(new SqlParameter("@search", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@datefrom", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@dateto", SqlDbType.VarChar));
cmd.Parameters["@search"].Value = searchText;
cmd.Parameters["@datefrom"].Value = searchText;
cmd.Parameters["@dateto"].Value = searchText;
SqlDataReader reader = cmd.ExecuteReader();
var total = 0;
view.Rows.Clear();
if (reader.HasRows)
{
while (reader.Read())
{
int n = view.Rows.Add();
view.Rows[n].Cells[0].Value = reader[0].ToString();
view.Rows[n].Cells[1].Value = reader[1].ToString();
view.Rows[n].Cells[2].Value = reader[2].ToString();
view.Rows[n].Cells[3].Value = reader[3].ToString();
view.Rows[n].Cells[4].Value = string.Format("{0:00}", reader[4]);
total = total + Convert.ToInt32(reader[4]);
view.Rows[n].Cells[5].Value = string.Format("{0:MM/dd/yyyy}", reader[5]);
Total = total;
}
}
reader.Close();
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static void AllReportBetweenDate(DataGridView view, DateTimePicker Ddate, string item)
{
SqlConnection con = new DBConnection().getConnection();
try
{
con.Open();
string search;
if (string.IsNullOrEmpty(item))
{
search = "SELECT *FROM All_Sales_Report WHERE Report_Date=@reportdate ORDER BY Report_Date,Item DESC";
SqlDataAdapter Tda = new SqlDataAdapter(search, con);
DataTable dt = new DataTable();
Tda.SelectCommand.Parameters.AddWithValue("@reportdate", SqlDbType.VarChar).Value = Ddate.Value.ToString("MM/dd/yyyy");
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]);
}
}
else
{
search = "SELECT *FROM All_Sales_Report WHERE Report_Date=@reportdate AND Item=@search ORDER BY Report_Date,Item DESC";
SqlDataAdapter Tda = new SqlDataAdapter(search, con);
DataTable dt = new DataTable();
Tda.SelectCommand.Parameters.AddWithValue("@reportdate", SqlDbType.VarChar).Value = Ddate.Value.ToString("MM/dd/yyyy");
Tda.SelectCommand.Parameters.AddWithValue("@search", SqlDbType.VarChar).Value = item;
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);
}
}
