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 List<TempOrder> GetempOrders()
{
List<TempOrder> orders = new List<TempOrder>();
try
{
connection = manager.Connection();
string selectQuery = "select * from Temp_Order_Hold";
SqlCommand command = new SqlCommand(selectQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TempOrder aTempOrder = new TempOrder();
aTempOrder.SerialNo = reader[0].ToString();
aTempOrder.CustomerName = reader[1].ToString();
aTempOrder.MobileNo = reader[2].ToString();
aTempOrder.BookName = reader[3].ToString();
aTempOrder.WriterName = reader[4].ToString();
aTempOrder.Edition = reader[5].ToString();
aTempOrder.Type = reader[6].ToString();
aTempOrder.Print = reader[7].ToString();
aTempOrder.Quantity = Convert.ToInt16(reader[8]);
aTempOrder.Unitprice = Convert.ToDouble(reader[9]);
aTempOrder.Total = Convert.ToDouble(reader[10]);
aTempOrder.Advance = Convert.ToDouble(reader[11]);
aTempOrder.Due = Convert.ToDouble(reader[12]);
aTempOrder.SupplyDate = reader[13].ToString();
aTempOrder.Id = Convert.ToInt16(reader[14]);
aTempOrder.MemoNumber = Convert.ToInt16(reader[15]);
orders.Add(aTempOrder);
}
connection.Close();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return orders;
}
//save an Order
private void orderAcceptButton_Click(object sender, EventArgs e)
{
try
{
OrderGateway gateway = new OrderGateway();
Order anOrder = new Order();
anOrder.CustomerName = customerNameTextBox.Text;
anOrder.CustomerPhone = customerMobileTextBox.Text;
anOrder.BookName = bookNameTextBox.Text;
anOrder.WriterName = writerNameTextBox.Text;
anOrder.Edition = bookEditionTextBox.Text;
anOrder.TypeOfBook = typeOfBookTextBox.Text;
anOrder.BookPrint = bookPrintTextbox.Text;
anOrder.BuyUnitPrice = Convert.ToDouble(buyingUnitPriceTextBox.Text);
string quantiy = bookQuantityTextBOx.Text;
anOrder.Quantity = Convert.ToInt16(quantiy);
anOrder.UnitPrice = Convert.ToDouble(bookUnitPriceTextBOx.Text);
anOrder.Advance = Convert.ToDouble(advanceTextBox.Text);
anOrder.SupplyDate = dateTimePicker1.Text;
string st = gateway.SaveOrder(anOrder);
////InvoiceUI invoice=new InvoiceUI(serialTextBox.Text,customerNameTextBox.Text,customerMobileTextBox.Text,bookNameTextBox.Text,writerNameTextBox.Text,bookEditionTextBox.Text,typeOfBookTextBox.Text,bookPrintTextbox.Text,bookQuantityTextBOx.Text,bookUnitPriceTextBOx.Text,advanceTextBox.Text,dueTextBox.Text,dateTimePicker1.Text);
//invoice.ShowDialog();
TempOrder aOrder=new TempOrder();
aOrder.SerialNo = serialTextBox.Text;
aOrder.CustomerName = customerNameTextBox.Text;
aOrder.MobileNo = customerMobileTextBox.Text;
aOrder.BookName = bookNameTextBox.Text;
aOrder.WriterName = writerNameTextBox.Text;
aOrder.Edition = bookEditionTextBox.Text;
aOrder.Type = typeOfBookTextBox.Text;
aOrder.Print = bookPrintTextbox.Text;
aOrder.Quantity = Convert.ToInt16(bookQuantityTextBOx.Text);
aOrder.Unitprice = Convert.ToDouble(bookUnitPriceTextBOx.Text);
aOrder.Total =
(Convert.ToInt16(bookQuantityTextBOx.Text)*Convert.ToDouble(bookUnitPriceTextBOx.Text));
aOrder.Advance = Convert.ToDouble(advanceTextBox.Text);
aOrder.Due = Convert.ToDouble(dueTextBox.Text);
aOrder.SupplyDate = dateTimePicker1.Text;
aOrder.MemoNumber = memoNumver;
TempOrderGateway gateway1=new TempOrderGateway();
gateway1.SaveTempOrer(aOrder);
MessageBox.Show(st,"Information",MessageBoxButtons.OK,MessageBoxIcon.Information);
ClearALLtextBox();
LoadAllBook();
serialTextBox.Text = LastAddedInvestlNo().ToString();
CustomerNamteSuggestion();
AtocompleteTextBox();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
//MessageBox.Show("Please fill every fields properly.", "Error", MessageBoxButtons.OK,
// MessageBoxIcon.Error);
}
}
private void DeliveryButton(object sender, EventArgs e)
{
try
{
if (dueTextBox.Text.Equals(payingAmountTextBOX.Text))
{
double due, pay, final_Due;
int quantity, serial_No;
double unitprice, advance, BuyUnitPrice;
double a, b;
a = Convert.ToDouble(dueTextBox.Text);
b = Convert.ToDouble(payingAmountTextBOX.Text);
if (dueTextBox.Text.Equals("")&&payingAmountTextBOX.Text.Equals(""))
{
MessageBox.Show("There are no order for delivery.", "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else if(a<b)
{
MessageBox.Show("Please check the paying amount.", "Message", MessageBoxButtons.OK,
MessageBoxIcon.Warning);
}
else
{
due = Convert.ToDouble(dueTextBox.Text);
pay = Convert.ToDouble(payingAmountTextBOX.Text);
final_Due = due - pay;
BuyUnitPrice = Convert.ToDouble(buyingUnitPriceTextBox.Text);
quantity = Convert.ToInt16(bookQuantityTextBOx.Text);
unitprice = Convert.ToDouble(bookUnitPriceTextBOx.Text);
advance = Convert.ToDouble(advanceTextBox.Text);
serial_No = Convert.ToInt16(serialTextBox.Text);
string print = bookPrintTextBox.Text;
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string insertQuery = "INSERT INTO Delivery_Report values(@serial,@cname,@phone,@bookName,@writer,@edition,@print,@quantity,@buyUnitprice,@unitpricecell,@advance,@due,@payingAmount,@finaldue,@date)";
SqlCommand command = new SqlCommand(insertQuery, connection);
connection.Open();
command.Parameters.Clear();
command.Parameters.Add("@serial", serialTextBox.Text);
command.Parameters.Add("@cname", customerNameTextBox.Text);
command.Parameters.Add("@phone", customerMobileTextBox.Text);
command.Parameters.Add("@bookName", bookNameTextBox.Text);
command.Parameters.Add("@writer", writerNameTextBox.Text);
command.Parameters.Add("@edition", bookEditionTextBox.Text);
command.Parameters.Add("@print", bookPrintTextBox.Text);
command.Parameters.Add("@quantity", quantity);
command.Parameters.Add("@buyUnitprice", Convert.ToDouble(BuyUnitPrice));
command.Parameters.Add("@unitpricecell", unitprice);
command.Parameters.Add("@advance", advance);
command.Parameters.Add("@due", due);
command.Parameters.Add("@payingAmount", pay);
command.Parameters.Add("@finaldue", final_Due);
command.Parameters.Add("@date", DateTime.Now.ToShortDateString());
command.ExecuteNonQuery();
DBManager manager6 = new DBManager();
SqlConnection connection6 = manager6.Connection();
string selectQuery6 = "insert into Due_Collection values(@pay,@empty,@payConleection,@date)";
SqlCommand cmd6 = new SqlCommand(selectQuery6, connection6);
connection6.Open()
;
cmd6.Parameters.Clear();
cmd6.Parameters.Add("@pay","0");
cmd6.Parameters.Add("@empty", "0");
cmd6.Parameters.AddWithValue("@payConleection", pay);
cmd6.Parameters.Add("@date", DateTime.Now.ToShortDateString());
cmd6.ExecuteNonQuery();
connection.Close();
string deleteQuery = "delete from Orders where Order_No='" + serial_No + "'";
SqlCommand command1 = new SqlCommand(deleteQuery, connection);
connection.Open();
int x = command1.ExecuteNonQuery();
MessageBox.Show(customerNameTextBox.Text + " paid his/her due.", "Message",
MessageBoxButtons.OK, MessageBoxIcon.Information);
connection.Close();
//update books
string name = bookNameTextBox.Text;
string writer = writerNameTextBox.Text;
string edition = bookEditionTextBox.Text;
String selectQ = "select Quantiy,B_Unit_Price from Books where Name=@name and Edition=@edition and Writer=@writer";
SqlCommand sletCom = new SqlCommand(selectQ, connection);
connection.Open();
sletCom.Parameters.Clear();
sletCom.Parameters.AddWithValue("@name", name);
sletCom.Parameters.AddWithValue("@edition", edition);
sletCom.Parameters.AddWithValue("@writer", writer);
SqlDataReader reader = sletCom.ExecuteReader();
while (reader.Read())
{
int quantity4 = Convert.ToInt16(reader[0]);
double unitprice5 = Convert.ToDouble(reader[1]);
connection.Close();
int quantityReudceBook, final_quantity;
quantityReudceBook = Convert.ToInt16(bookQuantityTextBOx.Text);
final_quantity = quantity4 - quantityReudceBook;
double T_Price = final_quantity*unitprice5;
string updateQuery = "UPDATE Books set Quantiy=@quantity,Total_Price=@totalPrice where Name=@name and Edition=@edition and Writer=@writer";
SqlCommand upCommand = new SqlCommand(updateQuery, connection);
upCommand.Parameters.Clear();
upCommand.Parameters.AddWithValue("@quantity", final_quantity);
upCommand.Parameters.AddWithValue("@totalPrice", T_Price);
upCommand.Parameters.AddWithValue("@name", name);
upCommand.Parameters.AddWithValue("@edition", edition);
upCommand.Parameters.AddWithValue("@writer", writer);
connection.Open();
int s = upCommand.ExecuteNonQuery();
MessageBox.Show("Updated","Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
//temp order delivery report save
TempOrder anOrder=new TempOrder(serialTextBox.Text,customerNameTextBox.Text,customerMobileTextBox.Text,bookNameTextBox.Text,writerNameTextBox.Text,bookEditionTextBox.Text,bookPrintTextBox.Text,Convert.ToInt16(bookQuantityTextBOx.Text),Convert.ToDouble(bookUnitPriceTextBOx.Text
), Convert.ToDouble(Convert.ToInt16(bookQuantityTextBOx.Text)*Convert.ToDouble(bookUnitPriceTextBOx.Text)),Convert.ToDouble(advanceTextBox.Text),final_Due,Convert.ToDouble(payingAmountTextBOX.Text),memoNumver);
TempOrderGateway gateway=new TempOrderGateway();
gateway.SaveDeliveryReport(anOrder);
GetAllOrders();
GetAllOrders1();
ClearAllTextBox();
connection.Close();
return;
}
}
}
else
{
double due, pay, final_Due;
int quantity, serial_No;
double unitprice, advance, BuyUnitPrice;
quan = Convert.ToInt16(bookQuantityTextBOx.Text);
double a, b;
a = Convert.ToDouble(dueTextBox.Text);
b = Convert.ToDouble(payingAmountTextBOX.Text);
if (dueTextBox.Text.Equals("0") || payingAmountTextBOX.Text.Equals("0"))
{
final_Due = 0;
}
else if (a < b)
{
MessageBox.Show("Please check the paying amount.", "Message", MessageBoxButtons.OK,
MessageBoxIcon.Warning);
}
else
{
due = Convert.ToDouble(dueTextBox.Text);
pay = Convert.ToDouble(payingAmountTextBOX.Text);
final_Due = due - pay;
BuyUnitPrice = Convert.ToDouble(buyingUnitPriceTextBox.Text);
quantity = Convert.ToInt16(bookQuantityTextBOx.Text);
unitprice = Convert.ToDouble(bookUnitPriceTextBOx.Text);
advance = Convert.ToDouble(advanceTextBox.Text);
serial_No = Convert.ToInt16(serialTextBox.Text);
DBManager manager = new DBManager();
SqlConnection connection = manager.Connection();
string insertQuery = "INSERT INTO Delivery_Report values(@serial,@cname,@phone,@bookName,@writer,@edition,@print,@quantity,@buyUnitprice,@unitpricecell,@advance,@due,@payingAmount,@finaldue,@date)";
SqlCommand command = new SqlCommand(insertQuery, connection);
connection.Open();
command.Parameters.Clear();
command.Parameters.Add("@serial", serialTextBox.Text);
command.Parameters.Add("@cname", customerNameTextBox.Text);
command.Parameters.Add("@phone", customerMobileTextBox.Text);
command.Parameters.Add("@bookName", bookNameTextBox.Text);
command.Parameters.Add("@writer", writerNameTextBox.Text);
command.Parameters.Add("@edition", bookEditionTextBox.Text);
command.Parameters.Add("@print", bookPrintTextBox.Text);
command.Parameters.Add("@quantity", quantity);
command.Parameters.Add("@buyUnitprice", Convert.ToDouble(BuyUnitPrice));
command.Parameters.Add("@unitpricecell", unitprice);
command.Parameters.Add("@advance", advance);
command.Parameters.Add("@due", due);
command.Parameters.Add("@payingAmount", pay);
command.Parameters.Add("@finaldue", final_Due);
command.Parameters.Add("@date", DateTime.Now.ToShortDateString());
command.ExecuteNonQuery();
connection.Close();
DBManager manager5 = new DBManager();
SqlConnection connection5 = manager5.Connection();
string selectQuery5 = "SELECT Quantity,Unit_Price From Orders where Order_No=@serial";
SqlCommand cmd5 = new SqlCommand(selectQuery5, connection5);
cmd5.Parameters.Clear();
cmd5.Parameters.AddWithValue("@serial", serial_No);
connection5.Open()
;
SqlDataReader reader5 = cmd5.ExecuteReader();
while (reader5.Read())
{
string q = reader5[0].ToString();
string r = reader5[1].ToString();
if (q.Equals("") && r.Equals(""))
{
qunatityOfRemaingBook = 0;
unit_Price = 0;
}
else
{
qunatityOfRemaingBook = Convert.ToInt16(q);
unit_Price = Convert.ToDouble(r);
}
}
connection.Close();
//Due Collection table Update
double s_due = Convert.ToDouble(dueTextBox.Text) - Convert.ToDouble(payingAmountTextBOX.Text);
DBManager manager6 = new DBManager();
SqlConnection connection6 = manager6.Connection();
string selectQuery6 = "insert into Due_Collection values(@pay,@s_due,@payCollection,@date)";
SqlCommand cmd6 = new SqlCommand(selectQuery6, connection6);
connection6.Open()
;
cmd6.Parameters.Clear();
cmd6.Parameters.Add("@pay", "0");
cmd6.Parameters.Add("@s_due", s_due);
cmd6.Parameters.AddWithValue("@payCollection", pay);
cmd6.Parameters.Add("@date", DateTime.Now.ToShortDateString());
cmd6.ExecuteNonQuery();
int sa = qunatityOfRemaingBook - quan;
string deleteQuery = "Update Orders set Quantity=@quantity,Total_price=@totalprice, Due=@due where Order_No=@serial";
SqlCommand command1 = new SqlCommand(deleteQuery, connection);
command1.Parameters.Clear();
command1.Parameters.AddWithValue("@quantity", sa);
command1.Parameters.AddWithValue("@totalprice", (sa*unit_Price));
command1.Parameters.AddWithValue("@due", final_Due);
command1.Parameters.AddWithValue("@serial", serial_No);
connection.Open();
int x = command1.ExecuteNonQuery();
MessageBox.Show(customerNameTextBox.Text + " Your current due is "+final_Due+".", "Message",
MessageBoxButtons.OK, MessageBoxIcon.Information);
connection.Close();
//books update
string name = (bookNameTextBox.Text).Trim();
string writer = writerNameTextBox.Text;
string edition = bookEditionTextBox.Text;
string selectQ = "select Quantiy,B_Unit_Price from Books where Name=@name and Edition=@edition and Writer=@writer";
SqlCommand sletCom = new SqlCommand(selectQ, connection);
sletCom.Parameters.Clear();
sletCom.Parameters.AddWithValue("@name", name);
sletCom.Parameters.AddWithValue("@edition", edition);
sletCom.Parameters.AddWithValue("@writer", writer);
connection.Open();
SqlDataReader reader = sletCom.ExecuteReader();
while (reader.Read())
{
int quantity4 = Convert.ToInt16(reader[0]);
double unitprice5 = Convert.ToDouble(reader[1]);
connection.Close();
int quantityReudceBook, final_quantity;
quantityReudceBook = Convert.ToInt16(bookQuantityTextBOx.Text);
final_quantity = quantity4 - quantityReudceBook;
double T_Price = final_quantity*unitprice5;
string updateQuery = "UPDATE Books set Quantiy=@finalQuantity,Total_Price=@totalprice where Name=@name and Edition=@edition and Writer=@writer";
SqlCommand upCommand = new SqlCommand(updateQuery, connection);
upCommand.Parameters.Clear();
upCommand.Parameters.AddWithValue("@finalQuantity", final_quantity);
upCommand.Parameters.AddWithValue("@totalprice", T_Price);
upCommand.Parameters.AddWithValue("@name", name);
upCommand.Parameters.AddWithValue("@edition", edition);
upCommand.Parameters.AddWithValue("@writer", writer);
connection.Open();
upCommand.ExecuteNonQuery();
//for partial pay
TempOrder anOrder = new TempOrder(serialTextBox.Text, customerNameTextBox.Text, customerMobileTextBox.Text, bookNameTextBox.Text, writerNameTextBox.Text, bookEditionTextBox.Text, bookPrintTextBox.Text, Convert.ToInt16(bookQuantityTextBOx.Text), Convert.ToDouble(bookUnitPriceTextBOx.Text
), Convert.ToDouble(Convert.ToInt16(bookQuantityTextBOx.Text) * Convert.ToDouble(bookUnitPriceTextBOx.Text)), Convert.ToDouble(advanceTextBox.Text),final_Due, Convert.ToDouble(payingAmountTextBOX.Text),memoNumver);
TempOrderGateway gateway = new TempOrderGateway();
gateway.SaveDeliveryReport(anOrder);
GetAllOrders();
GetAllOrders1();
ClearAllTextBox();
connection.Close();
MessageBox.Show("Updated","Message",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
}
}
}
catch (Exception formatException)
{
MessageBox.Show(formatException.Message,"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
public void SaveDeliveryReport(TempOrder aOrder)
{
try
{
connection = manager.Connection();
string query =
"Insert into Temp_Order_Delivery values(@serial,@customerName,@mobile,@bookName,@writerName,@edition,@print,@quantity,@unitPrice,@total,@advance,@due,@payingAmount,@memoNo)";
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@serial", aOrder.SerialNo);
cmd.Parameters.AddWithValue("@customerName", aOrder.CustomerName);
cmd.Parameters.AddWithValue("@mobile", aOrder.MobileNo);
cmd.Parameters.AddWithValue("@bookName", aOrder.BookName);
cmd.Parameters.AddWithValue("@writerName", aOrder.WriterName);
cmd.Parameters.AddWithValue("@edition", aOrder.Edition);
cmd.Parameters.AddWithValue("@print", aOrder.Print);
cmd.Parameters.AddWithValue("@quantity", aOrder.Quantity);
cmd.Parameters.AddWithValue("@unitPrice", aOrder.Unitprice);
cmd.Parameters.AddWithValue("@total", aOrder.Total);
cmd.Parameters.AddWithValue("@advance", aOrder.Advance);
cmd.Parameters.AddWithValue("@due", aOrder.Due);
cmd.Parameters.AddWithValue("@payingAmount", aOrder.PayingAmmount);
cmd.Parameters.AddWithValue("@memoNo", aOrder.MemoNumber);
cmd.ExecuteNonQuery();
// MessageBox.Show("Saved into temp delivery report");
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
connection.Close();
}
}
