public UpdateAndSellUI(Book aBook)
{
this.newBook = aBook;
InitializeComponent();
serialTextBox.Text = newBook.SerialNo;
nameTextBox.Text = newBook.BookName;
writernameTextBox.Text = newBook.AuthorName;
editionTextBox.Text = newBook.Edition;
if (radioButton1.Text.Equals(newBook.TypeOfBook))
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
book_Print = newBook.BookPrint;
string s = book_Print.Trim();
foreach (var bP in groupBox4.Controls)
{
RadioButton button = (RadioButton) bP;
if (button.Text.Equals(s))
{
button.Checked = true;
}
}
int a= newBook.Quantity;
double b = newBook.UnitPrice;
quantityTextBox.Text = a.ToString();
uintPriceTextBox.Text = b.ToString();
dateTimePiker.Text = newBook.PurchasesDate;
memoNumver = GetLastMemoNumber();
}
private void saveButton_Click(object sender, EventArgs e)
{
try
{
SearchAndEdit Search = new SearchAndEdit();
DBManager manager = new DBManager();
Book aBook = new Book();
aBook.SerialNo = serialTextBox.Text;
aBook.BookName = nameTextBox.Text;
aBook.AuthorName = writernameTextBox.Text;
aBook.Edition = comboBox1.SelectedItem.ToString();
aBook.TypeOfBook = b_type;
aBook.Quantity = Convert.ToInt16(quantityTextBox.Text);
aBook.UnitPrice = Convert.ToDouble(uintPriceTextBox.Text);
aBook.PurchasesDate =dateTimePiker.Text;
string status =manager.SaveBookS(aBook);
MessageBox.Show(status,"Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
ClearAllText();
serialTextBox.Text=LastAddedInvestlNo().ToString();
Search.GetALLBooks();
}
catch(FormatException)
{
MessageBox.Show("Please fill up every field properly","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
catch (Exception)
{
MessageBox.Show("Book id must be unique.","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
public string SaveBook(Book aBook)
{
double totalPrice=Convert.ToDouble(aBook.Quantity)*Convert.ToDouble(aBook.UnitPrice);
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string InsertQuerty = "Insert into Books values(@name,@writer,@edition,@type,@print,@quantity,@unitprice,@totalprice,@pdate)";
// string InsertQuerty = string.Format("Insert into Books values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",aBook.BookName,aBook.AuthorName,aBook.Edition,aBook.TypeOfBook,aBook.BookPrint,aBook.Quantity,aBook.UnitPrice,totalPrice,aBook.PurchasesDate);
// string insertQuery = "INSERT INTO Books values('"+aBook.BookName+"','"+aBook.AuthorName+"','"+aBook.Edition+"','"+aBook.TypeOfBook+"','"+aBook.BookPrint+"','"+aBook.Quantity+"','"+aBook.UnitPrice+"','"+totalPrice+"','"+aBook.PurchasesDate+"')";
SqlCommand cmd = new SqlCommand(InsertQuerty, connection);
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@name", aBook.BookName);
cmd.Parameters.AddWithValue("@writer", aBook.AuthorName);
cmd.Parameters.AddWithValue("@edition", aBook.Edition);
cmd.Parameters.AddWithValue("@type", aBook.TypeOfBook);
cmd.Parameters.AddWithValue("@print", aBook.BookPrint);
cmd.Parameters.AddWithValue("@quantity", aBook.Quantity);
cmd.Parameters.AddWithValue("@unitprice", aBook.UnitPrice);
cmd.Parameters.AddWithValue("@totalprice", totalPrice);
cmd.Parameters.AddWithValue("@pdate", aBook.PurchasesDate);
int i = cmd.ExecuteNonQuery();
return i + " Book Saved";
// connection.Close();
}
public void LoadAllBook()
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "select * from Books where Name LIKE '%" + textBox1.Text + "%'";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader();
List<Book> books = new List<Book>();
while (myReader.Read())
{
string serial = myReader[0].ToString();
string name = myReader[1].ToString();
string authorName = myReader[2].ToString();
string edition = myReader[3].ToString();
string type = myReader[4].ToString();
int quantity = Convert.ToInt16(myReader[5]);
double unitprice = Convert.ToDouble(myReader[6]);
string date = myReader[7].ToString();
Book aBook = new Book();
aBook.SerialNo = serial;
aBook.BookName = name;
aBook.AuthorName = authorName;
aBook.Edition = edition;
aBook.TypeOfBook = type;
aBook.Quantity = quantity;
aBook.UnitPrice = unitprice;
aBook.PurchasesDate = date;
ListViewItem item = new ListViewItem(aBook.SerialNo);
item.SubItems.Add(aBook.BookName);
item.SubItems.Add(aBook.AuthorName);
item.SubItems.Add(aBook.Edition);
item.SubItems.Add(aBook.TypeOfBook);
item.SubItems.Add(aBook.Quantity.ToString());
item.SubItems.Add(aBook.UnitPrice.ToString());
item.SubItems.Add(aBook.PurchasesDate);
item.Tag = aBook;
listView1.Items.Add(item);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void saveButton_Click(object sender, EventArgs e)
{
try
{
SearchAndEdit Search = new SearchAndEdit();
DBManager manager = new DBManager();
Book aBook = new Book();
aBook.SerialNo = serialTextBox.Text;
aBook.BookName = nameTextBox.Text;
aBook.AuthorName = writernameTextBox.Text;
aBook.Edition = textBox1.Text;
aBook.TypeOfBook = b_type;
aBook.BookPrint = book_print;
aBook.Quantity = Convert.ToInt16(quantityTextBox.Text);
aBook.UnitPrice = Convert.ToDouble(uintPriceTextBox.Text);
aBook.PurchasesDate =dateTimePiker.Text;
string status =manager.SaveBookS(aBook);
//if (status!=null)
//{
// reader.Dispose();
// reader=new SpeechSynthesizer();
// reader.SpeakAsync(" Saved");
MessageBox.Show(status, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
//}
ClearAllText();
serialTextBox.Text=LastAddedInvestlNo().ToString();
Search.GetALLBooks();
//28-02-2015
ShowAllBook();
ShowAllOldBook();
totalBooks();
nameTextBox.TabIndex = 0;
}
catch(FormatException)
{
MessageBox.Show("Please fill up every field properly","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
catch (Exception)
{
MessageBox.Show("Book Couldn't Save.","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
public string SaveBook(Book aBook)
{
double totalPrice=Convert.ToDouble(aBook.Quantity)*Convert.ToDouble(aBook.UnitPrice);
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string insertQuery = "INSERT INTO Books values('"+aBook.BookName+"','"+aBook.AuthorName+"','"+aBook.Edition+"','"+aBook.TypeOfBook+"','"+aBook.Quantity+"','"+aBook.UnitPrice+"','"+totalPrice+"','"+aBook.PurchasesDate+"')";
SqlCommand cmd = new SqlCommand(insertQuery, connection);
connection.Open();
int i = cmd.ExecuteNonQuery();
return i + " Book Saved";
// connection.Close();
}
public string DeleteInfo(Book aBook)
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string deleteQuery = "Delete from Books where [S.No]='"+aBook.SerialNo+"'";
SqlCommand cmd = new SqlCommand(deleteQuery, connection);
connection.Open();
int i = cmd.ExecuteNonQuery();
return i + "row deleted";
}
private void saveToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
BookGateway gateway=new BookGateway();
List<Book> books = new List<Book>();
int count = dataGridView1.RowCount;
for (int i = 0; i < count; i++)
{
DataGridViewRow row = this.dataGridView1.Rows[i];
string s1 = row.Cells["S#No"].Value.ToString();
string name = row.Cells["Name"].Value.ToString();
string writer = row.Cells["Writer"].Value.ToString();
string edition = row.Cells["Edition"].Value.ToString();
string type = row.Cells["Type"].Value.ToString();
string print = row.Cells["Book_Print"].Value.ToString();
int quantity = Convert.ToInt16(row.Cells["Quantiy"].Value);
double bookUnitPrice = Convert.ToDouble(row.Cells["B_Unit_Price"].Value);
//double totalPrice = Convert.ToDouble(row.Cells["Total_Price"].Value);
string date = row.Cells["Purchase_Date"].Value.ToString();
Book aBook=new Book();
aBook.SerialNo = s1;
aBook.BookName = name;
aBook.AuthorName = writer;
aBook.Edition = edition;
aBook.TypeOfBook = type;
aBook.BookPrint = print;
aBook.Quantity = quantity;
aBook.UnitPrice = bookUnitPrice;
aBook.PurchasesDate = date;
gateway.SaveBook(aBook);
//books.Add(aBook);
}
}
catch (Exception exception)
{
MessageBox.Show("Saved");
}
}
public void GetAll()
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "select * from sell_Counter";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<Book> sellBooks = new List<Book>();
while(reader.Read())
{
string serial = reader[0].ToString();
string name = reader[1].ToString();
string AName = reader[2].ToString();
string edition = reader[3].ToString();
string type = reader[4].ToString();
string print = reader[5].ToString();
int quantity = Convert.ToInt16(reader[6]);
double unitpricecell = Convert.ToDouble(reader[7]);
double unitPrice = Convert.ToDouble(reader[8]);
double total = Convert.ToDouble(reader[9]);
string date = reader[10].ToString();
Book aBook = new Book();
aBook.SerialNo = serial;
aBook.BookName = name;
aBook.AuthorName = AName;
aBook.PurchasesDate = date;
aBook.Edition = edition;
aBook.TypeOfBook = type;
aBook.BookPrint = print;
aBook.Quantity = quantity;
aBook.UnitPrice = unitPrice;
sellBooks.Add(aBook);
}
;
}
public UpdateAndSellUI(Book aBook)
{
this.newBook = aBook;
InitializeComponent();
serialTextBox.Text = newBook.SerialNo;
nameTextBox.Text = newBook.BookName;
writernameTextBox.Text = newBook.AuthorName;
editionTextBox.Text = newBook.Edition;
if (radioButton1.Text.Equals(newBook.TypeOfBook))
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
int a= newBook.Quantity;
double b = newBook.UnitPrice;
quantityTextBox.Text = a.ToString();
uintPriceTextBox.Text = b.ToString();
dateTimePiker.Text = newBook.PurchasesDate;
}
public string SaveBookS(Book aBook)
{
string st = bGateway.SaveBook(aBook);
return st;
}
//sell Button
private void SellButton(object sender, EventArgs e)
{
try
{
double unitPrice;
double total;
int a, b,quantity;
double totalBookPrice;
int toatalQuantity;
a = Convert.ToInt16(quantityTextBox.Text);
b = Convert.ToInt16(quantitySellTextBox.Text);
if (a <= 0 ||( a<Convert.ToInt16(quantitySellTextBox.Text)))
{
MessageBox.Show("There are no available book for sell.","Sorry",MessageBoxButtons.OK,MessageBoxIcon.Exclamation);
toatalQuantity = a;
return;
}
{
toatalQuantity = Convert.ToInt16(a - b);
}
DBManager manager3 = new DBManager();
SqlConnection connection3 = manager3.Connection();
string selectQuery1 = "select Quantiy,B_Unit_Price,Total_Price from Books where [S.No]='" + serialTextBox.Text + "'";
connection3.Open();
SqlCommand cmd3 = new SqlCommand(selectQuery1, connection3);
SqlDataReader reader1 = cmd3.ExecuteReader();
Book abook = new Book();
while (reader1.Read())
{
quantity = Convert.ToInt16(reader1[0]);
unitPrice = Convert.ToDouble(reader1[1]);
total = Convert.ToDouble(reader1[2]);
abook.Quantity = quantity;
abook.UnitPrice = unitPrice;
totalBookPrice = total - (unitPrice * Convert.ToInt16(quantitySellTextBox.Text));
totalBookBuyAmount(totalBookPrice);
}
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string updateQuery = "UPDATE Books SET Name='" + nameTextBox.Text + "',Writer='" + writernameTextBox.Text + "',Edition='" + editionTextBox.Text + "',Type='" + b_type + "',Quantiy='" + toatalQuantity + "',B_Unit_Price='" + uintPriceTextBox.Text + "',Total_Price='" + ((abook.UnitPrice*abook.Quantity)-(b*abook.UnitPrice))+ "',Purchase_Date='" + dateTimePiker.Text + "' where [S.No]='" + newBook.SerialNo + "'";
SqlCommand cmd = new SqlCommand(updateQuery, connection);
connection.Open();
int i = cmd.ExecuteNonQuery();
connection.Close();
double unitprice, pay, due;
double benifit,loss;
double buy_total =Convert.ToDouble(uintPriceTextBox.Text)*Convert.ToDouble(quantitySellTextBox.Text);
quantity = Convert.ToInt16(quantitySellTextBox.Text);
unitprice = Convert.ToDouble(unitpriceSellTextBox.Text);
pay = Convert.ToDouble(payTextBox.Text);
due = sell_total - Convert.ToDouble(payTextBox.Text);
if (pay <sell_total)
{
loss = buy_total- pay;
benifit = 0;
}
else
{
benifit = pay-buy_total;
loss = 0;
}
string insertQuery = "Insert into sell_Counter values('" + serialTextBox.Text + "','" + nameTextBox.Text + "','" + writernameTextBox.Text + "','" + editionTextBox.Text + "','" + b_type + "','" + quantitySellTextBox.Text + "','" + unitpriceSellTextBox.Text + "','" + totalTextBox.Text + "','" + uintPriceTextBox.Text + "','" + buy_total + "','"+payTextBox.Text+"','"+due+"','" + benifit + "','" + loss + "','" + DateTime.Today.ToShortDateString() + "')";
SqlCommand insCmd = new SqlCommand(insertQuery, connection);
connection.Open();
int c = insCmd.ExecuteNonQuery();
MessageBox.Show("Successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
dueTextBox.Text = due.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void GetAll()
{
try
{
DBManager manager3 = new DBManager();
SqlConnection connection3 = manager3.Connection();
string selectQuery1 = "select * from Books where [S.No]='" + serialTextBox.Text + "'";
connection3.Open();
SqlCommand cmd3 = new SqlCommand(selectQuery1, connection3);
SqlDataReader reader1 = cmd3.ExecuteReader();
Book abook = new Book();
while (reader1.Read())
{
nameTextBox.Text = reader1[1].ToString();
writernameTextBox.Text = reader1[2].ToString();
editionTextBox.Text = reader1[3].ToString();
string type = reader1[4].ToString();
string print = reader1[5].ToString();
quantityTextBox.Text = Convert.ToInt16(reader1[6]).ToString();
uintPriceTextBox.Text = Convert.ToDouble(reader1[7]).ToString();
foreach (var control in groupBox2.Controls)
{
RadioButton radio = (RadioButton)control;
if (type.Equals(radio.Text))
{
radio.Checked = true;
}
}
foreach (var control in groupBox4.Controls)
{
RadioButton radio = (RadioButton)control;
if (print.Equals(radio.Text))
{
radio.Checked = true;
}
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//sell Button
private void SellButton(object sender, EventArgs e)
{
try
{
double total, unitPrice, totalBookPrice;
int a, b,quantity;
int toatalQuantity;
a = Convert.ToInt16(quantityTextBox.Text);
b = Convert.ToInt16(quantitySellTextBox.Text);
if (a <= 0 || (a < Convert.ToInt16(quantitySellTextBox.Text)))
{
MessageBox.Show("There are no available book for sell.", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
toatalQuantity = a;
//return;
}
{
toatalQuantity = Convert.ToInt16(a - b);
}
DBManager manager3 = new DBManager();
SqlConnection connection3 = manager3.Connection();
string selectQuery1 = "select Quantiy,B_Unit_Price,Total_Price from Books where [S.No]=@serialNo";
connection3.Open();
SqlCommand cmd3 = new SqlCommand(selectQuery1, connection3);
cmd3.Parameters.Clear();
cmd3.Parameters.AddWithValue("@serialNo", serialTextBox.Text);
SqlDataReader reader1 = cmd3.ExecuteReader();
Book abook = new Book();
while (reader1.Read())
{
quantity = Convert.ToInt16(reader1[0]);
unitPrice = Convert.ToDouble(reader1[1]);
total = Convert.ToDouble(reader1[2]);
abook.Quantity = quantity;
abook.UnitPrice = unitPrice;
totalBookPrice = total - (unitPrice * Convert.ToInt16(quantitySellTextBox.Text));
totalBookBuyAmount(totalBookPrice);
}
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
double total_Price = ((abook.UnitPrice*abook.Quantity) - (b*abook.UnitPrice));
String updateQuery = "update Books set Name=@name,Writer=@writer,Edition=@edition,Type=@type,Book_Print=@print,Quantiy=@quantity,B_Unit_Price=@bunitprice,Total_Price=@total,Purchase_Date=@date where [S.No]=@serial";
SqlCommand cmd = new SqlCommand(updateQuery, connection);
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@name",nameTextBox.Text );
cmd.Parameters.AddWithValue("@writer", writernameTextBox.Text);
cmd.Parameters.AddWithValue("@edition",editionTextBox.Text );
cmd.Parameters.AddWithValue("@type",b_type );
cmd.Parameters.AddWithValue("@print", book_Print);
cmd.Parameters.AddWithValue("@quantity",toatalQuantity );
cmd.Parameters.AddWithValue("@bunitprice", uintPriceTextBox.Text);
cmd.Parameters.AddWithValue("@total",total_Price );
cmd.Parameters.AddWithValue("@date", dateTimePiker.Text);
cmd.Parameters.AddWithValue("@serial", newBook.SerialNo);
cmd.ExecuteNonQuery();
connection.Close();
double unitprice, pay, due;
double benifit,loss;
double buy_total =Convert.ToDouble(uintPriceTextBox.Text)*Convert.ToDouble(quantitySellTextBox.Text);
quantity = Convert.ToInt16(quantitySellTextBox.Text);
unitprice = Convert.ToDouble(unitpriceSellTextBox.Text);
pay = Convert.ToDouble(payTextBox.Text);
due = sell_total - Convert.ToDouble(payTextBox.Text);
if (pay <sell_total)
{
loss = buy_total- pay;
benifit = 0;
}
else
{
benifit = pay-buy_total;
loss = 0;
}
string insertQuery = "Insert into sell_Counter values(@serial,@name,@writer,@edition,@type,@print,@quantity,@unitpricesell,@totalpricesell,@unitpriceBuy,@buy_total,@paytotal,@dueTotal,@benifitTotal,@lossTotal,@pdate)";
SqlCommand insCmd = new SqlCommand(insertQuery, connection);
connection.Open();
insCmd.Parameters.Clear();
insCmd.Parameters.Add("@serial", serialTextBox.Text);
insCmd.Parameters.Add("@name", nameTextBox.Text);
insCmd.Parameters.Add("@writer", writernameTextBox.Text);
insCmd.Parameters.Add("@edition", editionTextBox.Text);
insCmd.Parameters.Add("@type", b_type);
insCmd.Parameters.Add("@print", book_Print);
insCmd.Parameters.Add("@quantity", quantitySellTextBox.Text);
insCmd.Parameters.Add("@unitpricesell", unitpriceSellTextBox.Text);
insCmd.Parameters.Add("@totalpricesell", totalTextBox.Text);
insCmd.Parameters.Add("@unitpriceBuy", uintPriceTextBox.Text);
insCmd.Parameters.Add("@buy_total", buy_total);
insCmd.Parameters.Add("@paytotal", payTextBox.Text);
insCmd.Parameters.Add("@dueTotal", due);
insCmd.Parameters.Add("@benifitTotal", benifit);
insCmd.Parameters.Add("@lossTotal", loss);
insCmd.Parameters.Add("@pdate",DateTime.Now.ToShortDateString());
double x, y;
x = Convert.ToDouble(totalTextBox.Text);
y = Convert.ToDouble(payTextBox.Text);
if (y>x)
{
MessageBox.Show("Please check the paying amount.", "Message", MessageBoxButtons.OK,
MessageBoxIcon.Warning);
}
else
{
int i=insCmd.ExecuteNonQuery();
MessageBox.Show("Successful", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (i == 1)
{
string query = "Insert Into Temp_Sell_Counter values(@bookname,@writerName,@edition,@type,@print,@quantity,@unitprice,@total,@pay,@due,@memoNumber)";
SqlCommand command=new SqlCommand(query,connection);
command.Parameters.Clear();
command.Parameters.AddWithValue("@bookname", nameTextBox.Text);
command.Parameters.AddWithValue("@writerName", writernameTextBox.Text);
command.Parameters.AddWithValue("@edition",editionTextBox.Text);
command.Parameters.AddWithValue("@type",b_type);
command.Parameters.AddWithValue("@print",book_Print);
command.Parameters.AddWithValue("@quantity",Convert.ToInt16(quantitySellTextBox.Text));
command.Parameters.AddWithValue("@unitprice",Convert.ToDouble(unitpriceSellTextBox.Text));
command.Parameters.AddWithValue("@total",Convert.ToDouble(totalTextBox.Text));
command.Parameters.AddWithValue("@pay",Convert.ToDouble(payTextBox.Text));
//if (dueTextBox.Text.Equals("")||dueTextBox.Text.Equals("0"))
//{
command.Parameters.AddWithValue("@due", due);
command.Parameters.AddWithValue("@memoNumber", memoNumver+1);
command.ExecuteNonQuery();
}
GetAll();
dueTextBox.Text = due.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}