public List <ExportDTO> loadAllExport()
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: get information
string sql = "SELECT * FROM Export";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbDataReader reader = command.ExecuteReader();
List <ExportDTO> result = new List <ExportDTO>();
while (reader.Read())
{
ExportDTO export = new ExportDTO();
export.ID = reader.GetInt32(0);
export.Product = reader.GetInt32(1);
export.Customer = reader.GetInt32(2);
export.Amount = reader.GetInt32(3);
export.DateOutput = reader.GetString(4);
export.Total = reader.GetInt32(5);
result.Add(export);
}
reader.Close();
// B3: Close connection
connection.Close();
return(result);
}
public void updateExport(ExportDTO export)
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: update by ID
string sql = "UPDATE Export SET Product = @product, Customer = @customer, Amount = @amount, DateOutput = @dateoutput, Total = @total WHERE ID=@ID";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbParameter para = command.Parameters.Add("@product", OleDbType.Integer);
para.Value = export.Product;
para = command.Parameters.Add("@customer", OleDbType.Integer);
para.Value = export.Customer;
para = command.Parameters.Add("@amount", OleDbType.Integer);
para.Value = export.Amount;
para = command.Parameters.Add("@dateoutput", OleDbType.VarChar);
para.Value = export.DateOutput;
para = command.Parameters.Add("@total", OleDbType.Integer);
para.Value = export.Total;
para = command.Parameters.Add("@ID", OleDbType.Integer);
para.Value = export.ID;
OleDbDataReader reader = command.ExecuteReader();
reader.Close();
//Step 3: Close connection
connection.Close();
return;
}
public ExportDTO LoadOneExport(int id)
{
ExportDAO dao = new ExportDAO();
ExportDTO result = dao.loadExportByID(id);
return(result);
}
public ExportDTO loadExportByID(int id)
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: get information by ID
string sql = "SELECT * FROM Export WHERE ID=@ID";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbParameter para = command.Parameters.Add("@ID", OleDbType.Integer);
para.Value = id;
OleDbDataReader reader = command.ExecuteReader();
ExportDTO result = new ExportDTO();
while (reader.Read())
{
result.ID = reader.GetInt32(0);
result.Product = reader.GetInt32(1);
result.Customer = reader.GetInt32(2);
result.Amount = reader.GetInt32(3);
result.DateOutput = reader.GetString(4);
result.Total = reader.GetInt32(5);
}
reader.Close();
// B3: Close connection
connection.Close();
return(result);
}
private void btnSell_Clicked(object sender, RoutedEventArgs e)
{
if (txtAmount.Text.Count() == 0 || btnProduct.Content.ToString().Count() == 0 || btnCustomer.Content.ToString().Count() == 0)
{
lblNotice.Content = "Please fill all information.";
lblNotice.Visibility = Visibility.Visible;
return;
}
int amount;
bool isNumeric = Int32.TryParse(txtAmount.Text, out amount);
if (!isNumeric)
{
lblNotice.Content = "Amount isn't number!";
lblNotice.Visibility = Visibility.Visible;
return;
}
ProductBUS bus = new ProductBUS();
ProductDTO result = bus.LoadOneProduct(Global.IDProduct);
if (result.IvenNum < amount)
{
lblNotice.Content = $"There are just {result.IvenNum} crate in warehouse";
lblNotice.Visibility = Visibility.Visible;
return;
}
result.IvenNum = result.IvenNum - amount;
bus.UpdateProduct(result);
CustomerBUS bUS = new CustomerBUS();
CustomerDTO customer = bUS.LoadOneCustomer(Global.IDCustomer);
customer.Paid = customer.Paid + Int32.Parse(lblTotal.Content.ToString());
bUS.UpdateCustomer(customer);
ExportBUS exportBUS = new ExportBUS();
ExportDTO export = new ExportDTO();
export.Amount = amount;
export.Customer = Global.IDCustomer;
export.DateOutput = lblDate.Content.ToString();
export.Product = Global.IDProduct;
export.Total = Int32.Parse(lblTotal.Content.ToString());
exportBUS.AddNewExport(export);
this.Close();
}
// 0: failed
// 1: success
public int AddNewExport(ExportDTO export)
{
//Step 1: connect to database
OleDbConnection connection = Connect();
//Step 2: add to the database
string sql = "INSERT INTO Export (Product, Customer, Amount, DateOutput, Total) VALUES(@product, @customer, @amount, @dateoutput, @total)";
OleDbCommand command = new OleDbCommand(sql, connection);
OleDbParameter para = command.Parameters.Add("@product", OleDbType.Integer);
para.Value = export.Product;
para = command.Parameters.Add("@customer", OleDbType.Integer);
para.Value = export.Customer;
para = command.Parameters.Add("@amount", OleDbType.Integer);
para.Value = export.Amount;
para = command.Parameters.Add("@dateoutput", OleDbType.VarChar);
para.Value = export.DateOutput;
para = command.Parameters.Add("@total", OleDbType.Integer);
para.Value = export.Total;
int count = command.ExecuteNonQuery();
if (count < 1)
{
count = 0;
}
else
{
count = 1;
}
// Step 3: Close connection
connection.Close();
return(count);
}
public void UpdateExport(ExportDTO export)
{
ExportDAO dao = new ExportDAO();
dao.updateExport(export);
}
public int AddNewExport(ExportDTO export)
{
ExportDAO dao = new ExportDAO();
return(dao.AddNewExport(export));
}
public void GetExport(int cruiseId)
{
var exports = Module.IvsGetExport(DateTime.Now.AddDays(-15), cruiseId);
var list = new List <ExportDTO>();
foreach (IvExport r in exports)
{
var exportDto = new ExportDTO();
exportDto.ModifiedDate = r.ModifiedDate;
exportDto.Name = r.Name;
if (r.BookingRoom != null)
{
exportDto.BookingRoomId = r.BookingRoom.Id;
}
exportDto.Code = r.Code;
exportDto.CreatedDate = r.CreatedDate;
exportDto.CustomerName = r.CustomerName;
exportDto.Deleted = r.Deleted;
exportDto.Detail = r.Detail;
exportDto.ExportId = r.Id;
exportDto.Pay = r.Pay;
if (r.Room != null)
{
exportDto.RoomId = r.Room.Id;
}
exportDto.Status = r.Status;
//exportDto.StorageId = r.Storage.Id;
exportDto.ExportDate = r.ExportDate;
exportDto.Total = r.Total;
exportDto.CruiseId = r.Cruise.Id;
exportDto.IsDebt = r.IsDebt;
exportDto.Agency = r.Agency;
exportDto.ExportProducts = new EditableList <ExportProductDTO>();
var products = Module.IvProductExportGetByExport(exportDto.ExportId);
if (products != null && products.Count > 0)
{
foreach (IvProductExport productExport in products)
{
var exportProductDto = new ExportProductDTO();
exportProductDto.Discount = productExport.Discount;
exportProductDto.DiscountType = productExport.DiscountType;
exportProductDto.ExportId = productExport.Export.Id;
exportProductDto.ExportProductId = productExport.Id;
if (productExport.Product != null)
{
exportProductDto.ProductId = productExport.Product.Id;
}
exportProductDto.QuanityRateParentUnit = productExport.QuanityRateParentUnit;
exportProductDto.Quantity = productExport.Quantity;
if (productExport.Storage != null)
{
exportProductDto.StorageId = productExport.Storage.Id;
}
exportProductDto.Total = productExport.Total;
exportProductDto.UnitPrice = productExport.UnitPrice;
if (productExport.Unit != null)
{
exportProductDto.UnitId = productExport.Unit.Id;
}
exportDto.ExportProducts.Add(exportProductDto);
}
}
list.Add(exportDto);
}
HttpContext.Current.Response.ContentType = "application/json";
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.Write(JsonConvert.SerializeObject(list));
}
private void myBgw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
this.ExportLoading.Visibility = Visibility.Hidden;
bool isList = false;
object[] myResult = e.Result as object[];
if (myResult[0] is List <ExportDTO> )
{
isList = true;
var data = (List <ExportDTO>)myResult[0];
DateTime now = DateTime.Now;
string nameFile = myResult[1] + "\\" + "export" + now.ToString("ddMMyyyy") + "-" + now.ToString("HHmmsstt") + ".csv";
int lastUser = -1;
bool firsttime = true;
ExportDTO lastDto = new ExportDTO();
using (var writer = new StreamWriter(nameFile, false, Encoding.UTF8))
using (var csvWriter = new CsvWriter(writer))
{
csvWriter.Configuration.Delimiter = ",";
//HEADER (Event, barcode, onsite, all differents fields from csv, printdate, printby)
foreach (ExportDTO dto in data)
{
if (lastUser == -1)
{
lastUser = dto.EventFieldUserExport.UserID_User;
csvWriter.WriteField("Event");
csvWriter.WriteField("Barcode");
csvWriter.WriteField("Onsite");
}
if (lastUser != dto.EventFieldUserExport.UserID_User)
{
break;
}
csvWriter.WriteField(dto.EventFieldUserExport.EventFieldSet.FieldSet.Name);
}
csvWriter.WriteField("PrintDate");
csvWriter.WriteField("PrintBy");
csvWriter.NextRecord();
//FIELDS
foreach (ExportDTO dto in data)
{
if (firsttime)
{
lastUser = dto.EventFieldUserExport.UserID_User;
csvWriter.WriteField(dto.EventFieldUserExport.EventFieldSet.EventSet.Name);
csvWriter.WriteField(dto.EventFieldUserExport.UserSet.Barcode);
csvWriter.WriteField(dto.EventFieldUserExport.UserSet.Onsite);
firsttime = false;
}
if (lastUser != dto.EventFieldUserExport.UserID_User)
{
csvWriter.WriteField(lastDto.PrintBadgeExport == null ? "//" : lastDto.PrintBadgeExport.PrintDate.ToString());
csvWriter.WriteField(lastDto.PrintBadgeExport == null ? "//" : lastDto.PrintBadgeExport.PrintBy);
csvWriter.NextRecord();
lastUser = dto.EventFieldUserExport.UserID_User;
csvWriter.WriteField(dto.EventFieldUserExport.EventFieldSet.EventSet.Name);
csvWriter.WriteField(dto.EventFieldUserExport.UserSet.Barcode);
csvWriter.WriteField(dto.EventFieldUserExport.UserSet.Onsite);
}
csvWriter.WriteField(dto.EventFieldUserExport.Value);
lastDto = dto;
}
csvWriter.WriteField(lastDto.PrintBadgeExport == null ? "//" : lastDto.PrintBadgeExport.PrintDate.ToString());
csvWriter.WriteField(lastDto.PrintBadgeExport == null ? "//" : lastDto.PrintBadgeExport.PrintBy);
writer.Flush();
}
}
this.ButtonEnabled = true;
this.ShowNotification(isList ? MESSAGE_ALL : (string)e.Result);
}
