public List <THONGKE> getThongKe(ThongKeDate tkDate)
{
string query = "select * from dbo.getThongKe(@fromYear,@fromMonth,@toYear,@toMonth)";
string[] para = new string[4] {
"@fromYear", "@fromMonth", "@toYear", "@toMonth"
};
object[] value = new object[4] {
tkDate.fromYear, tkDate.fromMonth, tkDate.toYear, tkDate.toMonth
};
DataTable tb = cn.FillDataTable(query, CommandType.Text, para, value);
List <THONGKE> results = new List <THONGKE>();
foreach (DataRow row in tb.Rows)
{
THONGKE temp = new THONGKE();
temp.ID = Convert.ToInt32(row["ID"]);
temp.NGAY = Convert.ToDateTime(row["NGAY"]);
temp.SANPHAM_ID = Convert.ToInt32(row["SANPHAM_ID"]);
temp.SOLUONGBAN = Convert.ToInt32(row["SOLUONGBAN"]);
temp.DOANHTHU = Convert.ToInt32(row["DOANHTHU"]);
temp.LOINHUAN = Convert.ToInt32(row["LOINHUAN"]);
results.Add(temp);
}
return(results);
}
public List <THONGKE> LoadThongKeNgay(string ngay, string thang, string nam)
{
List <THONGKE> dsThongke = new List <THONGKE>();
string query = "DECLARE @tongtien money SET @tongtien = (SELECT SUM(TongThanhToan) FROM HOADON WHERE NgayLap LIKE '%" + nam + "-" + thang + "-" + ngay + "%');";
query += " DECLARE @tenKM char(5) SET @tenKM = (SELECT KM.MaKM FROM(SELECT TOP 1 COUNT(MaKM)AS tongKM, MaKM FROM HOADON" +
" WHERE NgayLap LIKE '%" + nam + "-" + thang + "-" + ngay + "%' GROUP BY MaKM ORDER BY COUNT(MaKM) DESC) AS KM)"; //khuyen mai su dung nhieu nhat
query += " DECLARE @slKM int SET @slKM = (SELECT KM.tongKM FROM(SELECT TOP 1 COUNT(MaKM)AS tongKM, MaKM FROM HOADON" +
" WHERE NgayLap LIKE '%" + nam + "-" + thang + "-" + ngay + "%' GROUP BY MaKM ORDER BY COUNT(MaKM) DESC) AS KM)"; //so lam ap dung khuyen mai ban chay nhat
query += " DECLARE @maM char(5) SET @maM = (SELECT M.MaM FROM(SELECT DISTINCT TOP 1 MaM, SUM(SL_Mon) AS tongM FROM CTHD, HOADON" +
" WHERE CTHD.MaHD = HOADON.MaHD AND NgayLap LIKE '%" + nam + "-" + thang + "-" + ngay + "%' GROUP BY MaM ORDER BY SUM(SL_Mon) DESC) AS M)" +
" DECLARE @tenM nvarchar(30) SET @tenM=(SELECT TenM FROM MONAN WHERE MaM=@maM)";
query += " DECLARE @slM int SET @slM = (SELECT M.tongM FROM(SELECT DISTINCT TOP 1 MaM, SUM(SL_Mon) AS tongM FROM CTHD, HOADON" +
" WHERE CTHD.MaHD = HOADON.MaHD AND NgayLap LIKE '%" + nam + "-" + thang + "-" + ngay + "%' GROUP BY MaM ORDER BY SUM(SL_Mon) DESC) AS M)";
query += " DECLARE @tongL money SET @tongL = (SELECT SUM(LuongCoBan) + SUM(HeSoLuong) AS luong FROM NHANVIEN, LUONGNV, LUONG" +
" WHERE NHANVIEN.MaNV = LUONGNV.MaNV AND LUONGNV.MaBacLuong = LUONG.MaBacLuong)";
query += " SELECT DISTINCT '" + ngay + "-" + thang + "-" + nam + "' AS ThoiGian, @tongtien as TongDoanhThu, @tenM AS TenMonBanChay, @slM AS TongSoOrder, @tenKM AS TenKhuyenMai, @slKM AS TongLanApDung, @tongL as TongLuongNV FROM HOADON WHERE NgayLap LIKE '%" + nam + "-" + thang + "-" + ngay + "%';";
DataTable data = provider.ExecuteQuery(query);
foreach (DataRow item in data.Rows)
{
THONGKE tk = new THONGKE(item);
dsThongke.Add(tk);
}
return(dsThongke);
}
public void insertTHONGKE(DONHANG dh)
{
List <CHITIETDONHANG> lstCTDH = new DonHangDAO().GetCTDH(dh.ID);
foreach (CHITIETDONHANG c in lstCTDH)
{
THONGKE tk = new THONGKE();
tk.NGAY = (DateTime)dh.NGAYDAT;
tk.SANPHAM_ID = c.SANPHAM_ID;
List <XUATHANG> lstXH = new XuatHangDAO().GetXuatHang(c.ID);
foreach (XUATHANG x in lstXH)
{
if (!CheckExitsThongke(tk.SANPHAM_ID, tk.NGAY))
{
tk.SOLUONGBAN = c.SOLUONG;
tk.DOANHTHU = c.SOLUONG * c.DONGIA;
tk.LOINHUAN = (x.GIABAN - x.GIANHAP) * x.SOLUONGXUAT;
cn.Post <THONGKE>("api/THONGKE/", tk);
}
else
{
tk.SOLUONGBAN += c.SOLUONG;
tk.DOANHTHU += c.SOLUONG * c.DONGIA;
tk.LOINHUAN += (x.GIABAN - x.GIANHAP) * x.SOLUONGXUAT;
cn.Put <THONGKE>("api/THONGKE/", tk);
}
}
}
}
public async Task <IHttpActionResult> GetTHONGKE(int id)
{
THONGKE tHONGKE = await db.THONGKE.FindAsync(id);
if (tHONGKE == null)
{
return(NotFound());
}
return(Ok(tHONGKE));
}
public async Task <IHttpActionResult> PostTHONGKE(THONGKE tHONGKE)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
db.THONGKE.Add(tHONGKE);
await db.SaveChangesAsync();
return(CreatedAtRoute("DefaultApi", new { id = tHONGKE.ID }, tHONGKE));
}
public bool ThemTK(int SoLuongKH, int SoLuongDV, DateTime NgayThongKe, int TongDoanhThu, ref string err)
{
QuanLyKhachSanEntities qlksEntity = new QuanLyKhachSanEntities();
THONGKE tk = new THONGKE();
tk.SoLuongKH = SoLuongKH;
tk.SoLuongDV = SoLuongDV;
tk.NgayThongKe = NgayThongKe;
tk.TongDoanhThu = TongDoanhThu;
qlksEntity.THONGKEs.Add(tk);
qlksEntity.SaveChanges();
return(true);
}
public async Task <IHttpActionResult> DeleteTHONGKE(int id)
{
THONGKE tHONGKE = await db.THONGKE.FindAsync(id);
if (tHONGKE == null)
{
return(NotFound());
}
db.THONGKE.Remove(tHONGKE);
await db.SaveChangesAsync();
return(Ok(tHONGKE));
}
public async Task <IHttpActionResult> PutTHONGKE(THONGKE tHONGKE)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
db.Entry(tHONGKE).State = EntityState.Modified;
try
{
await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
}
return(StatusCode(HttpStatusCode.NoContent));
}
