private int GetLastMemoNumber()
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQ = "select MAX(Memo_No) from Deliveri_Memo_Counter";
SqlCommand command = new SqlCommand(selectQ, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader[0].ToString().Equals(""))
{
memoNumver = 1;
}
else
{
memoNumver = Convert.ToInt16(reader[0]);
}
}
connection.Close();
connection.Open();
return memoNumver;
}
public void GetAllOrders()
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "SELECT * From Orders";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
dataTable = new DataTable();
adapter.Fill(dataTable);
BindingSource binding = new BindingSource();
binding.DataSource = dataTable;
dataGridView1.DataSource = dataTable;
adapter.Update(dataTable);
}
catch (SqlException exception)
{
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (e.RowIndex >= 0)
{
DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];
string serialNo = row.Cells["S.No"].Value.ToString();
DBManager manager=new DBManager();
SqlConnection connection = manager.Connection();
string query = "delete from Books Where [S.No]='" + serialNo + "'";
SqlCommand delCommand=new SqlCommand(query,connection);
connection.Open();
System.Windows.Forms.DialogResult result =
MessageBox.Show("Do you wnat to delete the selected book?", "Message", MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
delCommand.ExecuteNonQuery();
MessageBox.Show("deleted", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
TestBook();
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.StackTrace);
}
}
public string SaveOrder(Order anOrder)
{
try
{
if (anOrder.Quantity <= 0)
{
MessageBox.Show("Enter the book quantity", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (anOrder.Advance < 0)
{
MessageBox.Show("Enter the advance ammount", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//int quantity =;
double total = anOrder.Quantity*anOrder.UnitPrice;
double due = total - anOrder.Advance;
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string insertQuery = "INSERT INTO Orders values(@name,@phone,@bname,@bwriter,@bEdition,@type,@bPrint,@buyUnitprice,@bQuantity,@unitprice,@total,@advance,@due,@odrderDate,@supplyDate)";
//string insertQuery = "INSERT INTO Orders values('" + anOrder.CustomerName + "','" +
// anOrder.CustomerPhone + "','" + anOrder.BookName + "','" + anOrder.WriterName +
// "','" + anOrder.Edition + "','" + anOrder.BookPrint + "','" + anOrder.BuyUnitPrice + "','" + anOrder.Quantity + "','" + anOrder.UnitPrice +
// "','" + total + "','" + anOrder.Advance + "','" + due + "','" +
// DateTime.Now.ToShortDateString() + "','" + anOrder.SupplyDate + "')";
SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
connection.Open();
insertCommand.Parameters.Clear();
insertCommand.Parameters.AddWithValue("@name", anOrder.CustomerName);
insertCommand.Parameters.AddWithValue("@phone", anOrder.CustomerPhone);
insertCommand.Parameters.AddWithValue("@bname", anOrder.BookName);
insertCommand.Parameters.AddWithValue("@bwriter", anOrder.WriterName);
insertCommand.Parameters.AddWithValue("@bEdition", anOrder.Edition);
insertCommand.Parameters.AddWithValue("@type", anOrder.TypeOfBook);
insertCommand.Parameters.AddWithValue("@bPrint", anOrder.BookPrint);
insertCommand.Parameters.AddWithValue("@buyUnitprice", anOrder.BuyUnitPrice);
insertCommand.Parameters.AddWithValue("@bQuantity", anOrder.Quantity);
insertCommand.Parameters.AddWithValue("@unitprice", anOrder.UnitPrice);
insertCommand.Parameters.AddWithValue("@total", total);
insertCommand.Parameters.AddWithValue("@advance", anOrder.Advance);
insertCommand.Parameters.AddWithValue("@due", due);
insertCommand.Parameters.AddWithValue("@odrderDate", DateTime.Now.ToShortDateString());
insertCommand.Parameters.AddWithValue("@supplyDate", anOrder.SupplyDate);
int i = insertCommand.ExecuteNonQuery();
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
return " Order Saved";
}
public int LastAddedInvestlNo()
{
int a = lastAdded;
InvestmentGateway inGateway = new InvestmentGateway();
Invest invest = new Invest();
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "SELECT Id From InvestmentCost";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<int> totalId = new List<int>();
while (reader.Read())
{
int aId = Convert.ToInt16(reader[0]);
totalId.Add(aId);
}
if (totalId.Count.Equals(0))
{
return 0;
}
else
{
int x = totalId.Max();
return x;
}
}
public void TestBook()
{
try
{
DBManager manager1 = new DBManager();
SqlConnection connection = manager1.Connection();
string seletQuery = "select * from Books Order by Name";
SqlCommand selectCmd = new SqlCommand(seletQuery, connection);
connection.Open();
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = selectCmd;
dt = new DataTable();
myAdapter.Fill(dt);
BindingSource bSource = new BindingSource();
bSource.DataSource = dt;
dataGridView1.DataSource = dt;
myAdapter.Update(dt);
//return null;
}
catch (Exception obj)
{
throw new Exception("Error", obj);
}
}
public void SaveSelfOrder(SelfOrder selfOrder)
{
if (HasThisBookalradyinList(selfOrder.BName,selfOrder.BWriter,selfOrder.BEdition) == false)
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
connection.Close();
string selectQuery = "Insert into Self_Order values('" + selfOrder.BName + "','" +
selfOrder.BWriter + "','" + selfOrder.BEdition + "','" + selfOrder.Quantity +
"','" + DateTime.Now.ToShortDateString() + "')";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open();
int i = cmd.ExecuteNonQuery();
MessageBox.Show("Saved", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("This book already include.","Message",MessageBoxButtons.OK,MessageBoxIcon.Warning);
}
}
private void searchButton(object sender, EventArgs e)
{
DBManager manger = new DBManager();
List<Book> books = GetALLBooks();
}
public void GetAllBook()
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string query = "select * from Books";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string name = reader[1].ToString();
//comboBox1.Items.Add(name);
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void showTotalButton_Click(object sender, EventArgs e)
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery =
"select SUM(From_Book), sum(From_Photocopy),sum(From_Others),sum(Total_Income) from Income";
connection.Open();
SqlCommand cmd = new SqlCommand(selectQuery, connection);
SqlDataReader reader = cmd.ExecuteReader();
List<double> sellBooks = new List<double>();
while (reader.Read())
{
try
{
double fbook = Convert.ToInt16(reader[0]);
double fphotocopy = Convert.ToDouble(reader[1]);
double fothers = Convert.ToDouble(reader[2]);
double total = Convert.ToDouble(reader[3]);
bookTextBox.Text = fbook.ToString();
photocopyTextBox.Text = fphotocopy.ToString();
othersTextBox.Text = fothers.ToString();
sumofAllTextBox.Text = total.ToString();
}
catch (Exception)
{
MessageBox.Show("NO data found", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
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 void LoadAllBook()
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "SELECT * From Books";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
//connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
dataTable = new DataTable();
adapter.Fill(dataTable);
BindingSource binding = new BindingSource();
binding.DataSource = dataTable;
dataGridView1.DataSource = dataTable;
adapter.Update(dataTable);
}
catch ( SqlException exception)
{
MessageBox.Show(exception.Message);
}
}
public void SaveTempOrer(TempOrder aOrder)
{
try
{
DBManager maneageManager = new DBManager();
SqlConnection connection1 = maneageManager.Connection();
string query = "Insert into Temp_Order_Hold values(@serial,@customerName,@mobile,@bookName,@writerName,@edition,@type,@print,@quantity,@unitPrice,@total,@advance,@due,@supplydate,@memoNumber)";
SqlCommand command = new SqlCommand(query, connection1);
command.Parameters.Clear();
command.Parameters.AddWithValue("@serial", aOrder.SerialNo);
command.Parameters.AddWithValue("@customerName", aOrder.CustomerName);
command.Parameters.AddWithValue("@mobile", aOrder.MobileNo);
command.Parameters.AddWithValue("@bookName", aOrder.BookName);
command.Parameters.AddWithValue("@writerName", aOrder.WriterName);
command.Parameters.AddWithValue("@edition", aOrder.Edition);
command.Parameters.AddWithValue("@type", aOrder.Type);
command.Parameters.AddWithValue("@print", aOrder.Print);
command.Parameters.AddWithValue("@quantity", aOrder.Quantity);
command.Parameters.AddWithValue("@unitPrice", aOrder.Unitprice);
command.Parameters.AddWithValue("@total", aOrder.Total);
command.Parameters.AddWithValue("@advance", aOrder.Advance);
command.Parameters.AddWithValue("@due", aOrder.Due);
command.Parameters.AddWithValue("@supplydate", aOrder.SupplyDate);
command.Parameters.AddWithValue("@memoNumber", aOrder.MemoNumber);
connection1.Open();
command.ExecuteNonQuery();
MessageBox.Show("Saved", "Save Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void GetAllOrders1()
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "SELECT Quantity From Orders";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open()
;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string q = reader[0].ToString();
if (q.Equals(""))
{
qunatityOfRemaingBook = 0;
}
else
{
qunatityOfRemaingBook = Convert.ToInt16(q);
}
}
connection.Close();
}
catch (SqlException exception)
{
MessageBox.Show(exception.Message);
}
}
public int LastAddedInvestlNo()
{
OrderGateway gateway=new OrderGateway();
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "SELECT Order_No From Orders";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<int> totalId = new List<int>();
while (reader.Read())
{
int aId = Convert.ToInt16(reader[0]);
totalId.Add(aId);
}
if (totalId.Count<=0)
{
return 0;
}
else
{
int x = totalId.Max();
return x;
}
}
public void TestBook()
{
try
{
DBManager manager1 = new DBManager();
SqlConnection connection = manager1.Connection();
string seletQuery = "select * from ALl_Table_Name";
SqlCommand selectCmd = new SqlCommand(seletQuery, connection);
connection.Open();
SqlDataReader reader = selectCmd.ExecuteReader();
while (reader.Read())
{
string name = reader[1].ToString();
comboBox2.Items.Add(name);
//textBox3.Text = name;
}
//return null;
}
catch (Exception obj)
{
throw new Exception("Error", obj);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "select * from Income";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open();
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = cmd;
dataTable = new DataTable();
myAdapter.Fill(dataTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dataTable;
dataGridView1.DataSource = bSource;
myAdapter.Update(dataTable);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "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);
}
}
public void UpdateBookInfo()
{
try
{
//start here
DBManager manager2 = new DBManager();
SqlConnection connection2 = manager2.Connection();
string selectQuery = "select Quantiy,B_Unit_Price,Total_Price from Books where [S.No]='" + serialTextBox.Text + "'";
connection2.Open();
SqlCommand cmd2 = new SqlCommand(selectQuery, connection2);
SqlDataReader reader = cmd2.ExecuteReader();
List<Book> sellBooks = new List<Book>();
while (reader.Read())
{
quantity = Convert.ToInt16(reader[0]);
double unitPrice = Convert.ToDouble(reader[1]);
double T_Price;
int toatalQuantity = quantity + (Convert.ToInt16(quantityTextBox.Text));
T_Price = toatalQuantity*unitPrice;
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
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", T_Price);
cmd.Parameters.AddWithValue("@date", dateTimePiker.Text);
cmd.Parameters.AddWithValue("@serial", newBook.SerialNo);
cmd.ExecuteNonQuery();
int i = cmd.ExecuteNonQuery();
MessageBox.Show(i + " Row updated","Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string sltQ = "SELECT Total From DueRecords where Serial ='" + aDue.Serial + "' ";
SqlCommand sltCommand=new SqlCommand(sltQ,connection);
connection.Open();
SqlDataReader aReader = sltCommand.ExecuteReader();
while (aReader.Read())
{
double totalDue = Convert.ToDouble(aReader[0]);
if (aDue.Total==totalDue)
{
connection.Close();
string insert = "INsert INto Due_Collection values('" + totalDue + "','"+0+"','"+0+"','"+DateTime.Now.ToShortDateString()+"')";
SqlCommand cMdCommand=new SqlCommand(insert,connection);
connection.Open();
cMdCommand.ExecuteNonQuery();
connection.Close();
string selectQuery = "Delete from DueRecords where Serial ='" + aDue.Serial + "'";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
connection.Open();
int i = cmd.ExecuteNonQuery();
MessageBox.Show(aDue.Name + " has been paid his/her due.", "Information", MessageBoxButtons.OK,
MessageBoxIcon.Information);
this.Close();
}
return;
}
ClearAlltextBox();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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";
}
public void GetAllPhotocopyIncomeRecord()
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string query = "select * from Photocopy";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader[0].ToString().Equals("")&& reader[1].ToString().Equals("")
&& reader[2].ToString().Equals("")&&reader[3].ToString().Equals("")
&& reader[4].ToString().Equals("")&&reader[5].ToString().Equals("")
&&reader[6].ToString().Equals(""))
{
MessageBox.Show("NO data found", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int serial = Convert.ToInt16(reader[0]);
int machine_1 = Convert.ToInt16(reader[1]);
int machine_2 = Convert.ToInt16(reader[2]);
int machine_3 = Convert.ToInt16(reader[3]);
int machine_4 = Convert.ToInt16(reader[4]);
double total_Copy = Convert.ToDouble(reader[5]);
double copyRate = Convert.ToDouble(reader[6]);
double totalTaka = Convert.ToDouble(reader[7]);
string date = reader[8].ToString();
ListViewItem item=new ListViewItem(serial.ToString());
item.SubItems.Add(machine_1.ToString());
item.SubItems.Add(machine_2.ToString());
item.SubItems.Add(machine_3.ToString());
item.SubItems.Add(machine_4.ToString());
item.SubItems.Add(total_Copy.ToString());
item.SubItems.Add(copyRate.ToString());
item.SubItems.Add(totalTaka.ToString());
item.SubItems.Add(date.ToString());
listView1.Items.Add(item);
}
}
}
public void LoadAllDeliveryReport()
{
DBManager manager=new DBManager();
SqlConnection connection = manager.Connection();
string selectQuery = "Select * from Delivery_Report";
SqlCommand command=new SqlCommand(selectQuery,connection);
SqlDataAdapter adapter=new SqlDataAdapter();
adapter.SelectCommand = command;
dataTable=new DataTable();
adapter.Fill(dataTable);
BindingSource bSource=new BindingSource();
bSource.DataSource = dataTable;
dataGridView1.DataSource = dataTable;
adapter.Update(dataTable);
}
public void GetCurrentReading()
{
DBManager manager=new DBManager();
SqlConnection connection = manager.Connection();
string query = "select * from CurrentReading";
SqlCommand command=new SqlCommand(query,connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string s1 = reader[1].ToString();
string s2 = reader[2].ToString();
string s3 = reader[3].ToString();
string s4 = reader[4].ToString();
if (s1.Equals("")||s1.Equals("0"))
{
currentReadingMachine_1 = 0;
}
if (s2.Equals("") || s2.Equals("0"))
{
currentReadingMachine_2 = 0;
}
if (s3.Equals("") || s3.Equals("0"))
{
currentReadingMachine_3 = 0;
}
if (s4.Equals("") || s4.Equals("0"))
{
currentReadingMachine_4 = 0;
}
else
{
currentReadingMachine_1 = Convert.ToInt16(s1);
currentReadingMachine_2 = Convert.ToInt16(s2);
currentReadingMachine_3 = Convert.ToInt16(s3);
currentReadingMachine_4 = Convert.ToInt16(s4);
}
}
}
private void SaveButton_Click(object sender, EventArgs e)
{
try
{
Invest anInvest = new Invest();
anInvest.Id = Convert.ToInt16(idTextBox.Text);
anInvest.Book = Convert.ToDouble(bookTextBox.Text);
anInvest.Paper = Convert.ToDouble(paperTextBox.Text);
anInvest.Ink = Convert.ToDouble(inkTextBOx.Text);
anInvest.Equipment = Convert.ToDouble(equipmentTextBox.Text);
anInvest.Others = Convert.ToDouble(othersTextBox.Text);
anInvest.Date = dateTimePicker1.Text;
lastAdded = Convert.ToInt16(idTextBox.Text);
InvestmentGateway gateway = new InvestmentGateway();
string status = gateway.SaveTotalInvestment(anInvest);
MessageBox.Show(status,"Status",MessageBoxButtons.OK,MessageBoxIcon.Information);
LastAddedInvestlNo();
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string updateQuery = "UPDATE FInvestmentCost set FBook='" + bookTextBox.Text + "',FPaper='" + paperTextBox.Text + "',FInk='" + inkTextBOx.Text + "',FEquipment='" + equipmentTextBox.Text + "',FOthers='" + othersTextBox.Text + "',FInvestment_Date='" + dateTimePicker1.Text + "' WHERE FId='" + 1 + "'";
SqlCommand updateCmd = new SqlCommand(updateQuery, connection);
connection.Open();
int x = updateCmd.ExecuteNonQuery();
connection.Close();
string query = "INSert INTo InvestmentCost values('" + bookTextBox.Text + "','" + paperTextBox.Text + "','" + inkTextBOx.Text + "','" + equipmentTextBox.Text + "','" + othersTextBox.Text + "','" + dateTimePicker1.Text + "')";
SqlCommand command3=new SqlCommand(query,connection);
connection.Open();
int y = command3.ExecuteNonQuery();
last = LastAddedInvestlNo();
idTextBox.Text = last.ToString();
connection.Close();
ClearAlltextBox();
}
catch (FormatException)
{
MessageBox.Show("Please fill every field properly.","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
public string SaveTotalInvestment(DAO.Invest anInvest)
{
try
{
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string insertQuery = "INSERT INTO InvestmentCost values('" + anInvest.Book + "','" + anInvest.Paper + "','" + anInvest.Ink + "','" + anInvest.Equipment + "','" + anInvest.Others + "','" + anInvest.Date + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connection);
connection.Open();
int i = cmd.ExecuteNonQuery();
//MessageBox.Show("Saved");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return "Saved";
}
public string SaveOrder(Order anOrder)
{
try
{
if (anOrder.Quantity <= 0)
{
MessageBox.Show("Enter the book quantity", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (anOrder.Advance < 0)
{
MessageBox.Show("Enter the advance ammount", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
//int quantity =;
double total = anOrder.Quantity*anOrder.UnitPrice;
double due = total - anOrder.Advance;
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string insertQuery = "INSERT INTO Orders values('" + anOrder.CustomerName + "','" +
anOrder.CustomerPhone + "','" + anOrder.BookName + "','" + anOrder.WriterName +
"','" + anOrder.Edition + "','"+anOrder.BuyUnitPrice+"','" + anOrder.Quantity + "','" + anOrder.UnitPrice +
"','" + total + "','" + anOrder.Advance + "','" + due + "','" +
DateTime.Now.ToShortDateString() + "','" + anOrder.SupplyDate + "')";
SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
connection.Open();
int i = insertCommand.ExecuteNonQuery();
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
return " Order Saved";
}
