public List <HOADON> dsHoaDon()
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
List <HOADON> dsSP = context.HOADONs.ToList();
return(dsSP);
}
public List <SANPHAM> dsSanPham()
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
List <SANPHAM> dsSP = context.SANPHAMs.ToList();
return(dsSP);
}
public DataSet GetDayShift(int day1, int day2)
{
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
// quanLy.
var que = from pc in quanLy.PHANCONGs
join nv in quanLy.NHANVIENs
on pc.MaNV equals nv.MaNV
where pc.MaCa >= day1
where pc.MaCa <= day2
select new { pc.MaCa, nv.MaNV, nv.HoTen };
DataTable tablee = new DataTable();
tablee.Columns.Add("");
tablee.Columns.Add("");
tablee.Columns.Add("");
foreach (var item in que)
{
tablee.Rows.Add(item.MaCa, item.MaNV, item.HoTen);
}
ds.Tables.Add(tablee);
return(ds);
//string str = string.Format("select pc.MaCa, nv.MaNV, nv.HoTen " +
// "from PHANCONG pc, NHANVIEN nv " +
// "where pc.MaNV = nv.MaNV and pc.MaCa between {0} and {1} order by pc.MaCa",day1,day2);
//ds.Tables.Add(main.LoadData(str));
//return ds;
}
public bool Update(SANPHAM sp, out string message)
{
message = "";
try
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
SANPHAM sanpham = context.SANPHAMs.FirstOrDefault(x => x.MaSP == sp.MaSP);
if (sanpham != null)
{
sanpham.TenSP = sp.TenSP;
sanpham.HinhSP = sp.HinhSP;
sanpham.GiaSP = sp.GiaSP;
sanpham.GiaBan = sp.GiaBan;
sanpham.TT_Ban = sp.TT_Ban;
context.SubmitChanges();
return(true);
}
message = "Khong co gia tri";
return(false);
}
catch (Exception e)
{
message = e.Message;
}
return(false);
}
public List <CHITIET_HD> dsChiTiet_HD()
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
List <CHITIET_HD> dsSP = context.CHITIET_HDs.ToList();
return(dsSP);
}
//public DataSet LoadData(int nam,int thang)
//{
// ds = new DataSet();
// string str = string.Format("Select * from DOANHTHU_THANG where ThangBH = {0} and NamBH = {1}", thang,nam);
// ds.Tables.Add(dbDT.LoadData(str));
// return ds;
//}
public List <int> MaxDate()
{
List <int> list = new List <int>();
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
var que = (from dth in quanLy.DOANHTHU_THANGs
select dth).OrderByDescending(i => i.NamBH).FirstOrDefault();
// DOANHTHU_THANG dt_maxnam = que.Aggregate((val1, val2) => val1.NamBH > val2.NamBH ? val1 : val2);
var que1 = (from dth in quanLy.DOANHTHU_THANGs
where dth.NamBH == que.NamBH
select dth).OrderByDescending(i => i.ThangBH).FirstOrDefault();
// DOANHTHU_THANG dt_max = que.Aggregate((val1, val2) => val1.ThangBH > val2.ThangBH ? val1 : val2);
list.Add(que1.ThangBH);
list.Add(que1.NamBH);
//ds = new DataSet();
//DataTable tablee = new DataTable();
//tablee.Rows.Add("Max Month");
//tablee.Rows.Add("Max Year");
//tablee.Rows.Add(dt_max.ThangBH, dt_max.NamBH);
//ds.Tables.Add(tablee);
return(list);
}
public bool Update(HOADON hd, out string message)
{
message = "";
try
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
HOADON hoadon = context.HOADONs.FirstOrDefault(x => x.MaHD == hd.MaHD);
if (hoadon != null)
{
hoadon.Ngay = hd.Ngay;
hoadon.Thang = hd.Thang;
hoadon.Nam = hd.Nam;
hoadon.TongGiaSP = hd.TongGiaSP;
hoadon.TongTien = hd.TongTien;
hoadon.TT_HD = hd.TT_HD;
context.SubmitChanges();
return(true);
}
message = "Khong co gia tri";
return(false);
}
catch (Exception e)
{
message = e.Message;
}
return(false);
}
public DataSet Calculate_SumOf_Salaries(int date)
{
ds = new DataSet();
//string str = string.Format("Select sum(Luong) as Salary " +
// "from LUONG " +
// "group by NgayTL " +
// "having NgayTL = {0}", date);
//ds.Tables.Add(dbDT.LoadData(str));
QuanLyBanHangDataContext quanly = new QuanLyBanHangDataContext();
var que = (from luong in quanly.LUONGs
where luong.NgayTL == date
select luong);
var que2 = (from lg_by_my in que
group lg_by_my by new
{
lg_by_my.NgayTL
} into somenew
select new
{
TongLuong = somenew.Sum(y => y.Luong1)
}).SingleOrDefault();
DataTable tb = new DataTable();
tb.Columns.Add("Tong Tien Luong");
tb.Rows.Add(que2.TongLuong);
ds.Tables.Add(tb);
return(ds);
}
public bool Update(NHANVIEN nv, out string message)
{
message = "";
try
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
NHANVIEN nhanvien = context.NHANVIENs.FirstOrDefault(x => x.MaNV == nv.MaNV);
if (nhanvien != null)
{
nhanvien.HoTen = nv.HoTen;
nhanvien.CMND = nv.CMND;
nhanvien.GT = nv.GT;
nhanvien.MatKhau = nv.MatKhau;
nhanvien.SDT = nv.SDT;
nhanvien.QuanLi = nv.QuanLi;
nhanvien.TT_LamViec = nv.TT_LamViec;
context.SubmitChanges();
return(true);
}
message = "Khong co gia tri";
return(false);
}
catch (Exception e)
{
message = e.Message;
}
return(false);
}
public List <NHANVIEN> dsNhanVien()
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
List <NHANVIEN> dsNV = context.NHANVIENs.ToList();
return(dsNV);
}
public bool Insert(HOADON hd, out string message)
{
message = "Thanh toán thành công";
try
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
context.HOADONs.InsertOnSubmit(hd);
context.SubmitChanges();
return(true);
}
catch (Exception e)
{
message = e.Message;
}
return(false);
}
public bool Insert(NHANVIEN nv, out string message)
{
message = "";
try
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
context.NHANVIENs.InsertOnSubmit(nv);
context.SubmitChanges();
return(true);
}
catch (Exception e)
{
message = e.Message;
}
return(false);
}
//public DataSet GetShifts(int id)
//{
// string str = string.Format("select ca.MaCa " +
// "from CA ca inner join PHANCONG pc on ca.MaCa = pc.MaCa" +
// " where pc.MaNV={0}",id);
// ds.Tables.Add(dbLuong.LoadData(str));
// return ds;
//}
public void Calculate_salaries(int date)
{
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
List <LUONG> dsLuong = new List <LUONG>();
LUONG luong = new LUONG();
try
{
var phancong = (from pc in quanLy.PHANCONGs
join ca in quanLy.CAs on pc.MaCa equals ca.MaCa
select new OrderView
{
MaNV = pc.MaNV,
NgayTL = date,
Luong1 = pc.HeSo * ca.LuongCa
}
)
.GroupBy(pc => pc.MaNV).AsEnumerable().Select(g => new OrderView
{
MaNV = g.Key,
NgayTL = date,
Luong1 = g.Sum(x => x.Luong1)
});
List <LUONG> dsl = phancong.Cast <LUONG>().ToList();
foreach (LUONG item in dsl)
{
LUONG luongg = new LUONG();
luongg.MaNV = item.MaNV;
luongg.NgayTL = item.NgayTL;
luongg.Luong1 = item.Luong1;
quanLy.LUONGs.InsertOnSubmit(luongg);
quanLy.SubmitChanges();
}
MessageBox.Show("SUCCESS");
}
catch
{
MessageBox.Show("Đã tính lương tháng này");
}
foreach (var item in quanLy.PHANCONGs)
{
item.HeSo = 4;
}
}
public List <int> GetShifts(int id)
{
//DataSet ds = new DataSet();
List <int> shift = new List <int>();
QuanLyBanHangDataContext qlBH = new QuanLyBanHangDataContext();
var query = from cca in qlBH.CAs
join ppc in qlBH.PHANCONGs on cca.MaCa equals ppc.MaCa
where ppc.MaNV == id
select ppc.MaCa;
foreach (var item in query)
{
shift.Add(item);
}
return(shift);
}
public bool AddPC(NHANVIEN nv, CA ca)
{
try
{
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
PHANCONG pc = new PHANCONG();
pc.MaNV = nv.MaNV;
pc.MaCa = ca.MaCa;
pc.HeSo = 4;
quanLy.PHANCONGs.InsertOnSubmit(pc);
quanLy.SubmitChanges();
return(true);
}
catch
{
return(false);
}
}
public DataSet LoadData(int date)
{
DataSet ds = new DataSet();
QuanLyBanHangDataContext qlBH = new QuanLyBanHangDataContext();
var que = (from a in qlBH.LUONGs
where a.NgayTL == date
select a);
DataTable tablee = new DataTable();
tablee.Columns.Add("");
tablee.Columns.Add("");
tablee.Columns.Add("");
foreach (LUONG item in que)
{
tablee.Rows.Add(item.MaNV, item.NgayTL, item.Luong1);
}
ds.Tables.Add(tablee);
return(ds);
}
public bool Update(PHANCONG pc, out string message)
{
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
var que = (from pcc in quanLy.PHANCONGs
where pcc.MaCa == pc.MaCa
where pcc.MaNV == pc.MaNV
select pcc).SingleOrDefault();
if (que != null)
{
que.HeSo = pc.HeSo;
quanLy.SubmitChanges();
message = "Success";
return(true);
}
else
{
message = "Fail";
return(false);
}
}
public bool EditShift(CA ca, int luong)
{
try
{
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
var que = (from caaa in quanLy.CAs
where caaa.MaCa == ca.MaCa
select caaa).SingleOrDefault();
if (que != null)
{
que.LuongCa = luong;
quanLy.SubmitChanges();
return(true);
}
return(false);
}
catch
{
return(false);
}
}
public bool Insert(DOANHTHU_THANG dthu, out string message)
{
try
{
QuanLyBanHangDataContext quanly = new QuanLyBanHangDataContext();
DOANHTHU_THANG dtt = new DOANHTHU_THANG();
dtt.NamBH = dthu.NamBH;
dtt.ThangBH = dthu.ThangBH;
dtt.TongGiaSP_TH = dthu.TongGiaSP_TH;
dtt.TongLuong_NV = dthu.TongLuong_NV;
dtt.DoanhThu_TH = dthu.DoanhThu_TH;
quanly.DOANHTHU_THANGs.InsertOnSubmit(dtt);
quanly.SubmitChanges();
message = "SUCCESS";
return(true);
}
catch
{
message = "FAIL";
return(false);
}
}
public bool Update(CHITIET_HD ct, out string message)
{
message = "";
try
{
QuanLyBanHangDataContext context = new QuanLyBanHangDataContext();
CHITIET_HD chitiet = context.CHITIET_HDs.FirstOrDefault(x => x.MaHD == ct.MaHD && x.MaSP == ct.MaSP);
if (chitiet != null)
{
chitiet.SoLuong = ct.SoLuong;
context.SubmitChanges();
return(true);
}
message = "Khong co gia tri";
return(false);
}
catch (Exception e)
{
message = e.Message;
}
return(false);
}
public bool EditPC(NHANVIEN nv1, NHANVIEN nv2, CA ca)
{
try
{
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
PHANCONG pc1 = new PHANCONG();
pc1.MaNV = nv1.MaNV;
pc1.MaCa = ca.MaCa;
pc1.HeSo = 4;
quanLy.PHANCONGs.DeleteOnSubmit(pc1);
quanLy.SubmitChanges();
PHANCONG pc2 = new PHANCONG();
pc2.MaNV = nv2.MaNV;
pc2.MaCa = ca.MaCa;
pc2.HeSo = 4;
quanLy.PHANCONGs.InsertOnSubmit(pc2);
quanLy.SubmitChanges();
return(true);
}
catch
{
return(false);
}
}
public DataSet Caculate_Revenue(int nam, int thang)
{
//ds = new DataSet();
//string str = string.Format("select sum(TongTien) as Revenue, sum(TongGiaSP) as PrimeCost " +
// "from HOADON" +
// " group by Thang, Nam " +
// "having Thang = {0} and nam = {1}", thang, nam);
//ds.Tables.Add(dbDT.LoadData(str));
//return ds;
ds = new DataSet();
QuanLyBanHangDataContext quanly = new QuanLyBanHangDataContext();
var que = (from hd in quanly.HOADONs
where hd.Thang == thang
where hd.Nam == nam
select hd);
var que2 = (from hd_by_my in que
group hd_by_my by new
{
hd_by_my.Thang,
hd_by_my.Nam
} into somenew
select new
{
TongTien = somenew.Sum(x => x.TongTien),
TongGiaSP = somenew.Sum(y => y.TongGiaSP)
}).SingleOrDefault();
DataTable tb = new DataTable();
tb.Columns.Add("Tong Tien");
tb.Columns.Add("Von");
tb.Rows.Add(que2.TongTien, que2.TongGiaSP);
ds.Tables.Add(tb);
return(ds);
}
public List <CA> LoadShift()
{
QuanLyBanHangDataContext quanLy = new QuanLyBanHangDataContext();
return(quanLy.CAs.ToList());
}
public XuLyDAO()
{
db = new QuanLyBanHangDataContext();
}
