public BillInfo GetOneBillInfo(int bill_id)
{
BillInfo bills = new BillInfo();
if (connection.State != ConnectionState.Open)
{
ConnectToSQLDatabase();
}
cmd.CommandText = string.Format("select * from Bill where Bill_ID = @ID");
cmd.Parameters.Clear();
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = bill_id;
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
bills.Number_table = (int)reader["Number_TB"];
bills.USER_Name = (string)reader["ID_User"];
bills.Voucher = (string)reader["Vourcher"];
bills.Take = (int)reader["TaKe"];
bills.DAY = (DateTime)reader["Time"];
bills.ID = (int)reader["BILL_ID"];
bills.TOTAL = (long)reader["Total"];
}
else
{
return(null);
}
}
bills.ProductBills = GetDetailBillInfo(bills.ID);
return(bills);
}
public List <BillInfo> GetBillInfo(DateTime from, DateTime to, int start, int lenght)
{
List <BillInfo> bills = new List <BillInfo>();
if (connection.State != ConnectionState.Open)
{
ConnectToSQLDatabase();
}
cmd.CommandText = string.Format("select * from(select ROW_NUMBER() over(order by Time DESC) as [STT], BILL_ID, Number_TB, ID_User, Vourcher, Total, Take, Give, Time from BILL where Time >= @from and TIME <= @to) as foo where STT >= @start and STT <= @end");
// select* from(select ROW_NUMBER() over(order by Bill_ID) as [STT], Number_TB, ID_User, Vourcher, Total, Take, Give, Time from BILL) as foo where STT >= 1 and STT <= 5 and Time >= '2001/11/08' and TIME<= '2090/10/19'
cmd.Parameters.Clear();
cmd.Parameters.Add("@start", SqlDbType.Int).Value = start;
cmd.Parameters.Add("@end", SqlDbType.Int).Value = start + lenght - 1;
cmd.Parameters.Add("@from", SqlDbType.DateTime).Value = from;
cmd.Parameters.Add("@to", SqlDbType.DateTime).Value = to;
BillInfo bill;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
bill = new BillInfo();
bill.Number_table = (int)reader["Number_TB"];
bill.USER_Name = (string)reader["ID_User"];
bill.Voucher = (string)reader["Vourcher"];
bill.Take = (int)reader["TaKe"];
bill.DAY = (DateTime)reader["Time"];
bill.ID = (int)reader["BILL_ID"];
bill.TOTAL = (long)reader["Total"];
bills.Add(bill);
}
}
return(bills);
}
static public void Insert_Bill(BillInfo bill)
{
using (DatabaseController database = new DatabaseController())
{
database.insert_Bill(bill);
database.Insert_Detail_Bill(bill.ProductBills);
}
}
public FormBill(BillInfo bill)
{
InitializeComponent();
setData(bill);
IsReadonly = true;
btnCashier.Click += BtnCashier_Click;
btnCancel.Click += BtnCancel_Click;
textBox4.KeyPress += TextBox4_KeyPress;
}
public bool createBill(BillInfo info)
{
//check if file is open
try
{
using (FileStream r = File.OpenWrite(dest)) { }
}
catch (FileNotFoundException e)
{
Console.WriteLine("File not exists, create new file");
}
catch (Exception e)
{
Console.WriteLine("File is used by another process");
MessageBox.Show("Tệp tin đang được mở, vui lòng tắt tệp tin trước rồi thử lại", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
os = new FileStream(dest, FileMode.Create, FileAccess.ReadWrite, FileShare.Read);
defaultSize = new Rectangle(PageSize.A5.Width, 312 + info.ProductBills.Count * 24 + info.ProductBills.Count(i => i.Name.Length > 14) * 15);
doc = new Document(defaultSize);
doc.SetMargins(0, 0, 0, 0);
writer = PdfWriter.GetInstance(doc, os);
//start write
doc.Open();
//add title paragraph
doc.Add(this.createTitle());
//add name and date of the bill
doc.Add(createInfoPurchase(info));
//add product table
doc.Add(this.createTableInfoProduct(info));
//add end paragraph
doc.Add(createEndBill());
//end write
doc.Close();
os.Close();
writer.Close();
doc.Dispose();
os.Dispose();
writer.Dispose();
Process.Start(dest);
Console.WriteLine("Create file PDF success");
return(true);
}
public void setData(TableBillInfo table)
{
//
//Gán giá trị ban đầu khi mở form lên
//
info = new BillInfo();
if (info == null)
{
MessageBox.Show("Lỗi thông tin món ăn");
return;
}
else
{
info.Number_table = table.ID;
info.DAY = DateTime.Now;
info.Voucher = "#####";
//Thêm product vào trong bảng thanh toán
foreach (var i in table.ProductInTable)
{
/* Products p = new Products();
* p.Name = i.Name;
* p.NumberProduct = i.NumberProduct;
* p.Price = i.Price;*/
info.ProductBills.Add(i);
tlpProduct.Controls.Add(new Label()
{
Text = (tlpProduct.RowCount - 1).ToString(), TextAlign = ContentAlignment.MiddleCenter
}, 0, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Name, TextAlign = ContentAlignment.MiddleLeft
}, 1, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.NumberProduct.ToString(), TextAlign = ContentAlignment.MiddleCenter
}, 2, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Price.ToString("N0") + "VND", TextAlign = ContentAlignment.MiddleLeft
}, 3, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = (i.NumberProduct * i.Price).ToString("N0") + "VND", TextAlign = ContentAlignment.MiddleLeft
}, 4, tlpProduct.RowCount - 1);
tlpProduct.RowCount++;
tlpProduct.RowStyles.Add(new RowStyle(SizeType.Absolute, 30F));
}
Info_PropertyChanged(this, new PropertyChangedEventArgs("init bill"));
info.PropertyChanged += Info_PropertyChanged;
Init_Bill();
}
}
public void setData(TableBillInfo table)
{
//
//Gán giá trị ban đầu khi mở form lên
//
info = new BillInfo();
if (info == null)
{
MessageBox.Show(ProError, Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
info.Number_table = table.ID;
info.DAY = DateTime.Now;
info.Voucher = "#####";
info.USER_Name = StoreAssistant_Authenticater.Authenticator.CurrentUser.UserName;
//Thêm product vào trong bảng thanh toán
foreach (var i in table.ProductInTable)
{
info.ProductBills.Add(i);
tlpProduct.Controls.Add(new Label()
{
Text = (tlpProduct.RowCount - 1).ToString(), TextAlign = ContentAlignment.MiddleCenter
}, 0, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Name, TextAlign = ContentAlignment.MiddleLeft
}, 1, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.NumberProduct.ToString(), TextAlign = ContentAlignment.MiddleCenter
}, 2, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Price.ToString("N0") + "VND", TextAlign = ContentAlignment.MiddleLeft
}, 3, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = (i.NumberProduct * i.Price).ToString("N0") + "VND", TextAlign = ContentAlignment.MiddleLeft
}, 4, tlpProduct.RowCount - 1);
tlpProduct.RowCount++;
tlpProduct.RowStyles.Add(new RowStyle(SizeType.Absolute, 30F));
}
Info_PropertyChanged(this, new PropertyChangedEventArgs("Init bill"));
info.PropertyChanged += Info_PropertyChanged;
Init_Bill();
}
}
public void setData(BillInfo table)
{
//
//Gán giá trị ban đầu khi mở form lên
//
info = table;
info.Price_Bill = info.ProductBills.Sum(i => i.NumberProduct * i.Price);
info.Give = info.Take - info.TOTAL;
info.Voucher = info.Voucher.Trim();
if (info == null)
{
MessageBox.Show(ProError, Error, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
//Thêm product vào trong bảng thanh toán
foreach (Products i in table.ProductBills)
{
tlpProduct.Controls.Add(new Label()
{
Text = (tlpProduct.RowCount - 1).ToString(), TextAlign = ContentAlignment.MiddleCenter
}, 0, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Name, TextAlign = ContentAlignment.MiddleLeft
}, 1, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.NumberProduct.ToString(), TextAlign = ContentAlignment.MiddleCenter
}, 2, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Price.ToString(), TextAlign = ContentAlignment.MiddleLeft
}, 3, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = (i.NumberProduct * i.Price).ToString(), TextAlign = ContentAlignment.MiddleLeft
}, 4, tlpProduct.RowCount - 1);
tlpProduct.RowCount++;
//tlpProduct.RowStyles.Add(new RowStyle(SizeType.Absolute, 30F));
}
//Info_PropertyChanged(this, new PropertyChangedEventArgs("init bill"));
Init_Bill();
}
}
public void setData(BillInfo table)
{
//
//Gán giá trị ban đầu khi mở form lên
//
info = table;
info.Price_Bill = info.ProductBills.Sum(i => i.NumberProduct * i.Price);
info.Give = info.Take - info.TOTAL;
if (info == null)
{
MessageBox.Show("Lỗi thông tin món ăn");
return;
}
else
{
//Thêm product vào trong bảng thanh toán
foreach (Products i in table.ProductBills)
{
tlpProduct.Controls.Add(new Label()
{
Text = (tlpProduct.RowCount - 1).ToString()
}, 0, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Name
}, 1, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.NumberProduct.ToString()
}, 2, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = i.Price.ToString()
}, 3, tlpProduct.RowCount - 1);
tlpProduct.Controls.Add(new Label()
{
Text = (i.NumberProduct * i.Price).ToString()
}, 4, tlpProduct.RowCount - 1);
tlpProduct.RowCount++;
tlpProduct.RowStyles.Add(new RowStyle(SizeType.Absolute, 30F));
}
//Info_PropertyChanged(this, new PropertyChangedEventArgs("init bill"));
Init_Bill();
}
}
/* public List<string> Get_Name_Product(string Name)
* {
* if (connection.State != ConnectionState.Open) { ConnectToSQLDatabase(); }
*
* cmd.CommandText = string.Format("select PD_Name from TB_product ");
* cmd.Connection = connection;
*
* using (SqlDataReader reader = cmd.ExecuteReader())
* {
* int i = 0;
* List<string> Product = new List<string>();
* List<string> ConsultPro = new List<string>();
* while (reader.Read())
* {
* object tmp = reader["PD_Name"];
* if (tmp != DBNull.Value)
* {
* ProductAddWithValue(reader["PD_Name"].ToString());
* }
++i;
* }
* ConsultPro = Product.FindAll(s => { return (s.Contains(Name)); });
* return ConsultPro;
* }
* }*/
public void insert_Bill(BillInfo bill)
{
if (connection.State != ConnectionState.Open)
{
ConnectToSQLDatabase();
}
cmd.CommandText = string.Format("insert into BILL (Number_TB, ID_User, Vourcher, Total, Take, Give, Time) values (@Number_Tb, @Id_User,@Vourcher, @total, @Take, @Give, @Time)");
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue(string.Format("@Number_Tb"), bill.Number_table);
cmd.Parameters.AddWithValue(string.Format("@Id_User"), "admin");
cmd.Parameters.AddWithValue(string.Format("@Vourcher"), bill.Voucher);
cmd.Parameters.AddWithValue(string.Format("@Total"), bill.TOTAL);
cmd.Parameters.AddWithValue(string.Format("@Take"), bill.Take);
cmd.Parameters.AddWithValue(string.Format("@Give"), bill.Give);
cmd.Parameters.AddWithValue(string.Format("@Time"), bill.DAY);
cmd.ExecuteNonQuery();
}
public FormBill(BillInfo bill)
{
InitializeComponent();
if (Lang != AppManager.CurrentLanguage)
{
Lang = AppManager.CurrentLanguage;
SetLanguage();
}
Language.ChangeLanguage += VoucherView_ChangeLanguage;
setData(bill);
IsReadonly = true;
btnCashier.Click += BtnCashier_Click;
btnCancel.Click += BtnCancel_Click;
textBox4.KeyPress += TextBox4_KeyPress;
this.Layout += FormBill_Layout;
}
private PdfPTable createInfoPurchase(BillInfo info)
{
Font font = new Font(basef, 12);
PdfPTable table = new PdfPTable(2);
float[] columns = new float[2];
columns[0] = 40;
columns[1] = 40;
table.SetWidths(columns);
table.AddCell(new PdfPCell(new Phrase("Số bàn: " + info.Number_table, font))
{
HorizontalAlignment = PdfPCell.ALIGN_LEFT, Border = PdfPCell.NO_BORDER
});
table.AddCell(new PdfPCell(new Phrase("Ngày mua:" + info.DAY.ToString("dd/MM/yyyy"), font))
{
HorizontalAlignment = PdfPCell.ALIGN_RIGHT, Border = PdfPCell.NO_BORDER
});
table.SpacingAfter = 20;
return(table);
}
private PdfPTable createTableInfoProduct(BillInfo info)
{
Font font = new Font(basef, 12);
PdfPTable table = new PdfPTable(4);
table.DefaultCell.Border = PdfPCell.NO_BORDER;
//set height column of table
int[] columnWidths = new int[4];
columnWidths[0] = 50;
columnWidths[1] = 40;
columnWidths[2] = 50;
columnWidths[3] = 40;
table.SetWidths(columnWidths);
int defaultBorder = PdfPCell.BOTTOM_BORDER | PdfPCell.TOP_BORDER;
table.AddCell(new PdfPCell(new Phrase("Tên món ăn", new Font(basef, 12, Font.BOLD)))
{
Border = defaultBorder, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
table.AddCell(new PdfPCell(new Phrase("Số lượng", new Font(basef, 12, Font.BOLD)))
{
HorizontalAlignment = PdfPCell.ALIGN_CENTER, Border = defaultBorder, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
table.AddCell(new PdfPCell(new Phrase("Đơn giá", new Font(basef, 12, Font.BOLD)))
{
HorizontalAlignment = PdfPCell.ALIGN_CENTER, Border = defaultBorder, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
table.AddCell(new PdfPCell(new Phrase("Thành tiền", new Font(basef, 12, Font.BOLD)))
{
HorizontalAlignment = PdfPCell.ALIGN_RIGHT, Border = defaultBorder, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
//add list product
foreach (var item in info.ProductBills)
{
table.AddCell(new PdfPCell(new Phrase(item.Name, font))
{
Border = PdfPCell.NO_BORDER, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
table.AddCell(new PdfPCell(new Phrase(item.NumberProduct.ToString(), font))
{
Border = PdfPCell.NO_BORDER, HorizontalAlignment = PdfPCell.ALIGN_CENTER, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
table.AddCell(new PdfPCell(new Phrase(item.Price.ToString("N0", nfi), font))
{
Border = PdfPCell.NO_BORDER, HorizontalAlignment = PdfPCell.ALIGN_CENTER, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
table.AddCell(new PdfPCell(new Phrase((item.Price * item.NumberProduct).ToString("N0", nfi), font))
{
Border = PdfPCell.NO_BORDER, HorizontalAlignment = PdfPCell.ALIGN_RIGHT, VerticalAlignment = PdfPCell.ALIGN_MIDDLE, PaddingBottom = 10
});
}
//----------------------------------------------------------------------------------------------------
//add table sum price of bill
table.AddCell(new PdfPCell(new Phrase("Thành tiền:", new Font(basef, 12, Font.BOLD)))
{
Colspan = 3, Border = PdfPCell.TOP_BORDER, PaddingTop = 5
});
table.AddCell(new PdfPCell(new Phrase(info.TOTAL.ToString("N0", nfi), new Font(basef, 12, Font.BOLD)))
{
HorizontalAlignment = PdfPCell.ALIGN_RIGHT, Border = PdfPCell.TOP_BORDER, PaddingTop = 5
});
table.AddCell(new PdfPCell(new Phrase("Giảm giá: ", new Font(basef, 10)))
{
Colspan = 3, Border = PdfPCell.NO_BORDER, PaddingBottom = 10
});
table.AddCell(new PdfPCell(new Phrase((info.TOTAL - info.Price_Bill).ToString("N0", nfi), new Font(basef, 10)))
{
HorizontalAlignment = PdfPCell.ALIGN_RIGHT, Border = PdfPCell.NO_BORDER, PaddingBottom = 10
});
//-----------------------------------------------------------------------------------------------------
table.AddCell(new PdfPCell(new Phrase("Tổng cộng:", new Font(basef, 13, Font.BOLD)))
{
Colspan = 3, Border = PdfPCell.TOP_BORDER, PaddingTop = 5
});
table.AddCell(new PdfPCell(new Phrase(info.Price_Bill.ToString("N0", nfi), new Font(basef, 13, Font.BOLD)))
{
HorizontalAlignment = PdfPCell.ALIGN_RIGHT, Border = PdfPCell.TOP_BORDER, PaddingTop = 5
});
table.AddCell(new PdfPCell(new Phrase("Tiền khách đưa:", new Font(basef, 12, Font.BOLD)))
{
Colspan = 3, Border = PdfPCell.NO_BORDER
});
table.AddCell(new PdfPCell(new Phrase(info.Take.ToString("N0", nfi), new Font(basef, 12, Font.BOLD)))
{
HorizontalAlignment = PdfPCell.ALIGN_RIGHT, Border = PdfPCell.NO_BORDER
});
table.AddCell(new PdfPCell(new Phrase("Tiền trả khách:", new Font(basef, 10, Font.BOLD)))
{
Colspan = 3, Border = PdfPCell.BOTTOM_BORDER, PaddingBottom = 20
});
table.AddCell(new PdfPCell(new Phrase(info.Give.ToString("N0", nfi), new Font(basef, 10, Font.BOLD)))
{
HorizontalAlignment = PdfPCell.ALIGN_RIGHT, Border = PdfPCell.BOTTOM_BORDER, PaddingBottom = 20
});
//-----------------------------------------------------------------------------------------------------
return(table);
}
