public DataTable getListBillInfoByBillID(BillDTO bill)
{
string sql = "SELECT ID_TV , COUNT FROM BILL_INFO WHERE ID_BILL = " + bill.ID;
DataTable result = DataProvider.Instance.ExecuteQuery(sql);
return(result);
}
public Bill MapBill(BillDTO value)
{
var listAcc = accRepo.GetAll();
var list = new List <Account>();
if (value.Accounts != null)
{
foreach (var item in value.Accounts)
{
foreach (var acc in listAcc)
{
if (item.Id == acc.Id)
{
list.Add(acc);
goto Next;
}
}
Next :;
}
}
return(new Bill
{
Id = value.Id,
Sum = value.Sum,
Accounts = list
});
}
public BillDTO FindById(int id)
{
string sql = "select * from Bill where Id = " + id;
DataTable dt = dal.Query(sql);
try
{
BillDTO dto = new BillDTO();
dto.Id = int.Parse(dt.Rows[0].ItemArray[0].ToString());
dto.RoomId = int.Parse(dt.Rows[0].ItemArray[1].ToString());
dto.MemberName = dt.Rows[0].ItemArray[2].ToString();
dto.RoomMoney = int.Parse(dt.Rows[0].ItemArray[3].ToString());
dto.ElectricMoney = int.Parse(dt.Rows[0].ItemArray[4].ToString());
dto.WaterMoney = int.Parse(dt.Rows[0].ItemArray[5].ToString());
dto.NetworkMoney = int.Parse(dt.Rows[0].ItemArray[6].ToString());
dto.OtherMoney = int.Parse(dt.Rows[0].ItemArray[7].ToString());
dto.LastElectricNumber = int.Parse(dt.Rows[0].ItemArray[8].ToString());
dto.ElectricNumber = int.Parse(dt.Rows[0].ItemArray[9].ToString());
dto.LastWaterNumber = int.Parse(dt.Rows[0].ItemArray[10].ToString());
dto.WaterNumber = int.Parse(dt.Rows[0].ItemArray[11].ToString());
dto.TotalMoney = int.Parse(dt.Rows[0].ItemArray[12].ToString());
dto.ModifiedBy = dt.Rows[0].ItemArray[13].ToString();
dto.CreatedDate = DateTime.Parse(dt.Rows[0].ItemArray[14].ToString());
//dto.ModifiedDate = DateTime.Parse(dt.Rows[0].ItemArray[15].ToString());
dto.Status = bool.Parse(dt.Rows[0].ItemArray[16].ToString());
return(dto);
}
catch (Exception ex)
{
return(null);
}
}
public ResponseDTO EditBill(BillDTO billDTO)
{
_logger.LogInformation("Executing EditBill method");
if (_context.Bill.Where(bill => bill.Id == billDTO.Id).Count() == 0)
{
return(new ResponseDTO()
{
Code = 400, Message = $"Bill of the users's id", Status = "Error"
});
}
try
{
Bill mappedFrombillDTO = _mapper.Map <Bill>(billDTO);
_context.Bill.Update(mappedFrombillDTO);
_context.SaveChanges();
}
catch (Exception e)
{
return(new ResponseDTO()
{
Code = 400, Message = e.Message, Status = "Error during updating bill"
});
}
return(new ResponseDTO()
{
Code = 200, Message = "Edite bill in db", Status = "Success"
});
}
private void btnCreateBill_Click(object sender, EventArgs e)
{
try
{
int lastWater = int.Parse(txtLastWater.Text);
int lastElectric = int.Parse(txtLastElectric.Text);
BillDTO billDTO = new BillDTO();
billDTO.LastElectricNumber = lastElectric;
billDTO.LastWaterNumber = lastWater;
billDTO.RoomId = roomId;
if (billBll.Insert(billDTO))
{
MessageBox.Show("Tạo hóa đơn thành công", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Tạo hóa đơn thất bại", "Thông báo",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("Phải nhập số", "Lỗi",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool editCost(BillDTO bll)
{
string query = string.Empty;
query += "UPDATE bill SET [cost] = @cost WHERE [idr] = @idr and [idc] = @idc and [checkin] = @checkin and [checkout] = @checkout";
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idr", bll.IDR);
cmd.Parameters.AddWithValue("@idc", bll.IDC);
cmd.Parameters.AddWithValue("@cost", bll.COST);
cmd.Parameters.AddWithValue("@checkin", bll.CheckIn);
cmd.Parameters.AddWithValue("@checkout", bll.CheckOut);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(true);
}
public ResponseDTO EditBill(BillDTO billDTO)
{
logger.LogInformation("Executing EditBill method");
if (context.Bill.Where(b => b.Name == billDTO.Name).Count() == 0)
{
return(new ResponseDTO()
{
Code = 400, Message = $"Bill about id {billDTO.Name} doesn't exist in db", Status = "Error"
});
}
try
{
context.Bill.Update(mapper.Map <Bill>(billDTO));
context.SaveChanges();
}
catch (Exception e)
{
return(new ResponseDTO()
{
Code = 400, Message = e.Message, Status = "Error"
});
}
return(new ResponseDTO()
{
Code = 200, Message = "Edit bill in db", Status = "Success"
});
}
/// <summary>
/// Handles the Click event of the CloseButton control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
private void CloseButtonClick(object sender, System.EventArgs e)
{
BillDTO bill = CheckOutServices.Close();
EnableCheckOutControls(false);
Display(bill);
}
public bool addBill(Connection conn, BillDTO data)
{
string queryString = "";
queryString = "insert into bill(exportDate, customer, saler, productsID, defectiveProductsID) " +
"values(" +
"'" + String.Format("{0:yyyy/MM/dd}", data.date) + "'," +
"'" + data.customerName + "'," +
"'" + data.salerName + "'," +
"'" + this.BuildListIDString(data.products) + "'" +
"'" + this.BuildListIDString(data.defectiveProducts) + "'" +
")";
Console.WriteLine(queryString);
conn.cmd.CommandText = queryString;
try
{
conn.cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
return(false);
}
return(true);
}
public bool Add(BillDTO bill)
{
DBConnection conn = new();
if (conn.Open())
{
int temp = 0;
if (bill.Archived) temp = 1;
string cmd =
"INSERT INTO bill (" +
"tableNumber," +
"billStatus," +
"tip," +
"archived" +
") values(" +
$"'{bill.TableNumber}'," +
$"'{(int)bill.Status}'," +
$"'{(float)bill.Tip}', " +
$"'{temp}'" +
");";
conn.RunCommand(cmd);
conn.Close();
}
return false;
}
public ResponseDTO EditBill(BillDTO billDTO)
{
logger.LogInformation("EditBill");
if (context.Bills.Where(b => b.Name == billDTO.Name).Count() == 0)
{
return(new ResponseDTO()
{
Code = 400,
Message = "Bill not exist",
Status = "Error"
});
}
try
{
context.Bills.Update(mapper.Map <Bill>(billDTO));
context.SaveChanges();
}
catch (Exception e)
{
return(new ResponseDTO()
{
Code = 400,
Message = e.Message,
Status = "Error"
});
}
return(new ResponseDTO()
{
Code = 200,
Message = "Updated bill",
Status = "Success"
});
}
public ActionResult PostBill(BillDTO billDto)
{
_billService.Add(billDto);
//var bill = _billService.GetBy(billDto.BillId);
return(Ok(new { success = true, message = "Tạo hóa đơn thành công" }));
}
public BillDTO GetTipConversion(string sku, string currency)
{
BillDTO result = null;
try
{
List <RateDM> readRates = RateRepository.ReadAll();
List <RateBE> ratesBusiness = Mapper.MapList <RateDM, RateBE>(readRates);
List <TransactionDM> readTransactions = TransactionRepository.ReadBySku(sku);
List <TransactionBE> transactionsBusiness = Mapper.MapList <TransactionDM,
TransactionBE>(readTransactions);
BillBE billCalculated = TipCalculatorService.GetBill(ratesBusiness, transactionsBusiness, currency);
result = Mapper.Map <BillBE, BillDTO>(billCalculated);
}
#region Exceptions
catch (VuelingExamDomainException e)
{
Log.Error(e.Message);
Log.Warning(e.StackTrace);
throw new VuelingExamApplicationException(e.Message, e.InnerException);
}
catch (VuelingExamInfrastructureException e)
{
Log.Error(e.Message);
Log.Warning(e.StackTrace);
throw new VuelingExamApplicationException(e.Message, e.InnerException);
}
#endregion
return(result);
}
public async Task <List <BillDTO> > GetUserBills(Guid UserId)
{
var bills = await ServiceRepository.GetUserBills(UserId);
var billDtos = new List <BillDTO>();
foreach (var bill in bills)
{
var trainingsInBills = await ServiceUnitOfWork.TrainingInBillRepository.GetTrainingsInBill(bill.Id);
var trainings = new List <TrainingDTO>();
foreach (var trainingInBill in trainingsInBills)
{
var training = await ServiceUnitOfWork.TrainingRepository.FirstOrDefaultAsync(trainingInBill.TrainingId);
var trainingDTO = new TrainingDTO()
{
TrainingDate = training.Start,
Description = training.Description
};
trainings.Add(trainingDTO);
}
var billDto = new BillDTO()
{
Total = bill.Total,
Deadline = bill.Deadline,
Trainings = trainings
};
billDtos.Add(billDto);
}
return(billDtos);
}
public ResponseDTO AddBill(BillDTO billDTO)
{
logger.LogInformation("AddBill");
try
{
var a = mapper.Map <Bill>(billDTO);
context.Bills.Add(a);
context.SaveChanges();
}
catch (Exception e)
{
return(new ResponseDTO()
{
Code = 400,
Message = e.Message,
Status = "Error"
});
}
return(new ResponseDTO()
{
Code = 200,
Message = "Added bill",
Status = "Success"
});
}
public ResponseDTO AddBill(BillDTO billDTO)
{
_logger.LogInformation("Executing AddBill method");
try
{
var mappedFrombillDTO = _mapper.Map <Bill>(billDTO);
_context.Bill.Add(mappedFrombillDTO);
_context.SaveChanges();
}
catch (Exception e)
{
return(new ResponseDTO()
{
Code = 400,
Message = e.Message,
Status = "Error during add bill"
});
}
return(new ResponseDTO()
{
Code = 200,
Message = "Added bill to db",
Status = "Success"
});
}
public bool insertBill(BillDTO dto)
{
bool check = false;
SqlConnection con = DBUtils.MakeConnection();
try
{
if (con != null)
{
string sql = "Insert Bill(id,dateCheckIn,nameTable,status) Values(@ID,@Date,@Table,@Status)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@ID", dto.ID);
cmd.Parameters.AddWithValue("@Date", dto.DateCheckIn);
cmd.Parameters.AddWithValue("@Table", dto.NameTable);
cmd.Parameters.AddWithValue("@Status", dto.Status);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
check = cmd.ExecuteNonQuery() > 0;
}
}catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
return(check);
}
public bool delete(BillDTO bll)
{
string query = string.Empty;
query += "DELETE FROM bill WHERE [idb] = @idb";;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idb", bll.IDB);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(true);
}
public Dictionary <int, List <string> > AddBill(List <CartItem> carts, BillDTO model, int userid)
{
Dictionary <int, List <string> > hash = new Dictionary <int, List <string> >();
// list sản phẩm k đủ số lượng
List <string> lst = new List <string>();
using (var dbContextTransaction = db.Database.BeginTransaction())
{
try
{
foreach (var item in carts)
{
var bill = new Bill
{
unit = item.quantity,
name = item.catalog.name,
price = item.catalog.price * item.quantity,
pictureuri = item.catalog.pictureuri,
catalogid = item.catalog.id,
status = 0,
userid = userid,
shiptoaddress = model.shiptoaddress,
notebuy = model.notebuy,
createdAt = DateTime.Now,
updatedAt = DateTime.Now,
phone = model.phone,
username = model.username
};
db.Bills.Add(bill);
var catalog = db.Catalogs.Find(item.catalog.id);
if (catalog.quantity >= item.quantity)
{
var msgBill = db.MessageBills.Find(1);
msgBill.updateChoDuyetAt = DateTime.Now;
catalog.quantity -= item.quantity;
}
else
{
lst.Add(catalog.name);
}
}
if (lst.Count > 0)
{
hash.Add(0, lst);
return(hash);
}
db.SaveChanges();
hash.Add(1, null);
dbContextTransaction.Commit();
return(hash);
}
catch (Exception)
{
dbContextTransaction.Rollback();
hash.Add(-1, null);
return(hash);
}
}
}
public BillDTO GetDate()
{
BillDTO result = new BillDTO();
string sql = "SELECT MAX(DATE_CHECKIN) FROM BILL_OUT";
result.DateCheckin = Convert.ToDateTime(DataProvider.Instance.ExecuteQuery(sql).Rows[0][0]);
return(result);
}
public void SoldInDay(Label result)
{
BillDTO billDTO = new BillDTO();
BillBUS billBUS = new BillBUS();
billDTO = billBUS.GetDate();
result.Text = data.SoldInDay(billDTO).Rows[0][0].ToString();
}
public async Task <BillDTO> UpdateAsync(BillDTO dto)
{
var model = _mapper.Map <Bill>(dto);
_unitOfWork.BillRepository.Update(model);
await _unitOfWork.SaveAsync();
return(_mapper.Map <BillDTO>(model));
}
public List <BillDTO> search(string Keyword)
{
string query = string.Empty;
query += "SELECT [idb], [idr], [idc], [cost], [checkin], [checkout]";
query += "FROM [bill]";
query += " WHERE ([checkin] LIKE CONCAT('%',@Keyword,'%'))";
query += " OR ([checkout] LIKE CONCAT('%',@Keyword,'%'))";
query += " OR ([idb] LIKE CONCAT('%',@Keyword,'%'))";
query += " OR ([idr] LIKE CONCAT('%',@Keyword,'%'))";
query += " OR ([idc] LIKE CONCAT('%',@Keyword,'%'))";
query += " OR ([cost] LIKE CONCAT('%',@Keyword,'%'))";
List <BillDTO> lsTimKiem = new List <BillDTO>();
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@Keyword", Keyword);
try
{
con.Open();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
BillDTO bll = new BillDTO();
bll.IDB = reader["idb"].ToString();
bll.IDR = reader["idr"].ToString();
bll.IDC = reader["idc"].ToString();
bll.COST = Decimal.Parse(reader["cost"].ToString());
bll.CheckIn = DateTime.Parse(reader["checkin"].ToString());
bll.CheckOut = DateTime.Parse(reader["checkout"].ToString());
lsTimKiem.Add(bll);
}
}
con.Close();
con.Dispose();
}
catch (Exception ex)
{
con.Close();
Console.WriteLine(ex);
throw;
}
}
}
return(lsTimKiem);
}
public void DeleteProduct(ProductDTO product)
{
BillInfoDTO billInfo = new BillInfoDTO();
BillDTO billDTO = new BillDTO();
billInfo.Tivi = product;
billInfoBUS.DelNGetBill(billInfo);
data.DeleteProduct(product);
}
public BillDTO createBill(CustomerDTO custommer)
{
BillDTO bill = new BillDTO();
bill.Customer = custommer;
bill.Status = 0;
bill.TotalPrice = 0;
bill.ID = data.createBillNGetIDBill(bill);
return(bill);
}
private void button3_Click(object sender, EventArgs e)
{
string billId = "BI001";
int billPayment = 10;
BillDTO billDTO = new BillDTO(billId, billPayment);
string TableQuery = "insert into BILL values('" + billDTO.BillId + "','" + billDTO.BillPayment + "');";
BillDAL billDAL = new BillDAL();
billDAL.InsertBill(billDTO);
}
public bool CreateBill(BillDTO bill)
{
return(_billRepository.CreateBill(new BillRecord
{
Amount = bill.OriginalAmount,
DueDate = bill.DueDate,
PaymentDate = bill.PaymentDate,
Name = bill.Name
}));
}
public ActionResult Payment(BillDTO model)
{
if (ModelState.IsValid)
{
var carts = (List <CartItem>)Session[CommonConstant.CART_SESSION];
var user = (UserSession)Session[CommonConstant.USER_SESSION];
decimal totalPrice = 0;
string tblData = "";
Dictionary <int, List <string> > hash = new BillDao().AddBill(carts, model, user.UserId);
if (hash.ContainsKey(1))
{
try
{
string content = System.IO.File.ReadAllText(Server.MapPath("~/Assets/user/orderBill.html"));
content = content.Replace("{{username}}", user.UserName);
content = content.Replace("{{customername}}", model.username);
content = content.Replace("{{phone}}", model.phone);
content = content.Replace("{{shiptoaddress}}", model.shiptoaddress);
content = content.Replace("{{email}}", model.email);
foreach (var item in carts)
{
decimal p = item.catalog.price * item.quantity;
totalPrice += p;
tblData += "<tr><td>" + item.catalog.name + "</td><td>" + item.quantity + "</td><td>" + string.Format("{0:0,0} VNĐ", p) + "</td></tr>";
}
content = content.Replace("{{tblData}}", tblData);
content = content.Replace("{{price}}", string.Format("{0:0,0 VNĐ}", totalPrice));
new MailHelper().SendEmail(model.email, "Hóa đơn thanh toán từ Mobile World", content);
Session[CommonConstant.CART_SESSION] = null;
Session[CommonConstant.CART_TOTAL_PRICE_SESSION] = 0;
TempData["Message"] = "Đặt hàng thành công. Hóa đơn được gửi vào email của bạn";
return(RedirectToAction("index", "cart"));
}
catch (Exception)
{
return(RedirectToAction("index", "error"));
}
}
else if (hash.ContainsKey(0))
{
string msg = "Có sản phẩm đã hết hàng: ";
msg += hash[0][0];
for (int i = 1; i < hash[0].Count; i++)
{
msg += ", " + hash[0][i];
}
TempData["Message"] = msg;
}
else
{
TempData["Message"] = "Lỗi đặt hàng!";
}
}
return(View("index"));
}
public int GetBillIDByRoomcode(int roomcode)
{
DataTable data = DataProvide.Instance.ExecuteQuery("select * from PHIEUTHUEPHONG where MaPhong =" + roomcode.ToString());
if (data.Rows.Count > 0)
{
BillDTO bill = new BillDTO(data.Rows[0]);
return(bill.RoomCode1);
}
return(-1);
}
public int GetBillIDByCustomerName(string customername)
{
DataTable data = DataProvide.Instance.ExecuteQuery("select p.MaPT,p.MaPhong,p.MaHinhThucThue,p.NgayGioBatDauThue,p.SoKhach,p.TinhTrangThanhToan from CHITIET_PHIEUTHUE c join PHIEUTHUEPHONG p on c.MaPT=p.MaPT where TenKhachHang=" + customername);
if (data.Rows.Count > 0)
{
BillDTO bill = new BillDTO(data.Rows[0]);
return(bill.RoomCode1);
}
return(-1);
}
