public bool Insert_LichTuan(DOLichLamViec dto)
{
if (Exist_LichTuanNhanVien(dto.NGAY_DAU_TUAN, dto.NV_ID))
return false;
string sql = "Insert into LICH_LAM_VIEC(ID,NV_ID,NGAY_DAU_TUAN,ST2,CT2,ST3,CT3,ST4,CT4,ST5,CT5,ST6,CT6,ST7,CT7,ST8,CT8,GHI_CHU) values(@ID,@NV_ID,@NGAY_DAU_TUAN,";
for (int i = 0; i < dto.NGAY.Length; i++)
{
sql += "'" + dto.NGAY[i] + "',";
}
sql += "'" + dto.GHI_CHU + "')";
db = HelpDB.getDatabase();
cmd = db.GetSQLStringCommand(sql);
if (dto.ID.ToString() != "")
db.AddInParameter(cmd, "@ID", System.Data.DbType.Int64, dto.ID);
else
db.AddInParameter(cmd, "@ID", System.Data.DbType.Int64, getGenID());
db.AddInParameter(cmd, "@NV_ID", System.Data.DbType.Int64, dto.NV_ID);
db.AddInParameter(cmd, "@NGAY_DAU_TUAN", System.Data.DbType.DateTime, dto.NGAY_DAU_TUAN);
if (db.ExecuteNonQuery(cmd) > 0)
return true;
return false;
}
public DataTable TimKiem(DateTime InputNgayDauTuan)
{
try
{
string sql = "select llv.*, nv.NAME from LICH_LAM_VIEC llv, DM_NHAN_VIEN nv where nv.ID = llv.NV_ID and NGAY_DAU_TUAN = @NGAY_DAU_TUAN order by nv.NAME";
ds = new DataSet();
db = HelpDB.getDatabase();
cmd = db.GetSQLStringCommand(sql);
db.AddInParameter(cmd, "@NGAY_DAU_TUAN", System.Data.DbType.DateTime, InputNgayDauTuan);
db.LoadDataSet(cmd, ds, "LICH_LAM_VIEC");
return ds.Tables[0];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getLichCoDinh()
{
try
{
string sql = "select llv.*, nv.NAME "
+ "from LICH_LAM_VIEC llv, DM_NHAN_VIEN nv "
+ "where nv.ID = llv.NV_ID "
+ "and NGAY_DAU_TUAN is null order by nv.NAME";
ds = new DataSet();
db = HelpDB.getDatabase();
cmd = db.GetSQLStringCommand(sql);
db.LoadDataSet(cmd, ds, "LICH_LAM_VIEC");
return ds.Tables[0];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public bool Insert_LichCoDinh(DOLichLamViec dto)
{
if (this.Exist_LCDNhanVien(dto.NV_ID))
return false;
string sql = "Insert into LICH_LAM_VIEC(ID,NV_ID,ST2,CT2,ST3,CT3,ST4,CT4,ST5,CT5,ST6,CT6,ST7,CT7,ST8,CT8,GHI_CHU) values(@ID,@NV_ID,";
for (int i = 0; i < dto.NGAY.Length; i++)
{
sql += "'" + dto.NGAY[i] + "',";
}
sql += "@GHI_CHU)";
db = HelpDB.getDatabase();
cmd = db.GetSQLStringCommand(sql);
db.AddInParameter(cmd, "@ID", System.Data.DbType.Int64, dto.ID);
db.AddInParameter(cmd, "@NV_ID", System.Data.DbType.Int64, dto.NV_ID);
db.AddInParameter(cmd, "@GHI_CHU", System.Data.DbType.String, dto.GHI_CHU);
if (db.ExecuteNonQuery(cmd) > 0)
return true;
return false;
}
public bool Exist_LichTuanNhanVien(DateTime Ngay, long NV_ID)
{
string sql = "select * from LICH_LAM_VIEC where NV_ID = @NV_ID and NGAY_DAU_TUAN = @NGAY";
db = HelpDB.getDatabase();
cmd = db.GetSQLStringCommand(sql);
db.AddInParameter(cmd, "@NV_ID", DbType.Int64, NV_ID);
db.AddInParameter(cmd, "@NGAY", DbType.DateTime, Ngay);
if (db.LoadDataSet(cmd, "AA").Tables[0].Rows.Count > 0)
return true;
return false;
}
public bool Exist_LCDNhanVien(long NV_ID)
{
string sql = "select * from LICH_LAM_VIEC where NV_ID = @NV_ID and NGAY_DAU_TUAN is null";
db = HelpDB.getDatabase();
cmd = db.GetSQLStringCommand(sql);
db.AddInParameter(cmd, "@NV_ID", DbType.Int64, NV_ID);
DataSet ds = new DataSet();
db.LoadDataSet(cmd, ds, "AA");
if (ds.Tables[0].Rows.Count > 0)
return true;
return false;
}
public bool DeleteRow(long ID, long NV_ID)
{
string sql = "delete from LICH_LAM_VIEC where NV_ID = @NV_ID and ID = @ID";
db = HelpDB.getDatabase();
cmd = db.GetSQLStringCommand(sql);
db.AddInParameter(cmd, "@NV_ID", DbType.Int64, NV_ID);
db.AddInParameter(cmd, "@ID", DbType.Int64, ID);
if (db.ExecuteNonQuery(cmd) > 0)
return true;
return false;
}
public void update(DbCommand cmd, DbTransaction dbTrans, DatabaseFB db, DOBangTheoDoi doBangTheoDoi)
{
cmd = db.GetSQLStringCommand("update BANG_THEO_DOI set name=@name,ngay_lap=@ngay_lap,nguoi_lap=@nguoi_lap,ngay_ky=@ngay_ky,nguoi_ky=@nguoi_ky,ngay_hieu_luc=@ngay_hieu_luc,nguoi_quan_ly=@nguoi_quan_ly,tai_lieu=@tai_lieu,ten_tai_lieu=@ten_tai_lieu,tinh_trang=@tinh_trang,hinh_thuc_thong_bao=@hinh_thuc_thong_bao,ghi_chu=@ghi_chu,nguoi_cap_nhat=@nguoi_cap_nhat,ngay_cap_nhat=@ngay_cap_nhat "
+ "where btd_id=@btd_id" );
db.AddInParameter(cmd, "@btd_id", DbType.Int64, doBangTheoDoi.BTD_ID);
db.AddInParameter(cmd, "@name", DbType.String, doBangTheoDoi.NAME);
db.AddInParameter(cmd, "@ngay_lap", DbType.DateTime, doBangTheoDoi.NGAY_LAP);
db.AddInParameter(cmd, "@nguoi_lap", DbType.Int64, doBangTheoDoi.NGUOI_LAP);
db.AddInParameter(cmd, "@ngay_ky", DbType.DateTime, doBangTheoDoi.NGAY_KY);
db.AddInParameter(cmd, "@nguoi_ky", DbType.Int64, doBangTheoDoi.NGUOI_KY);
db.AddInParameter(cmd, "@ngay_hieu_luc", DbType.DateTime, doBangTheoDoi.NGAY_HIEU_LUC);
db.AddInParameter(cmd, "@nguoi_quan_ly", DbType.Int64, doBangTheoDoi.NGUOI_QUAN_LY);
db.AddInParameter(cmd, "@tai_lieu", DbType.Binary, doBangTheoDoi.TAI_LIEU);
db.AddInParameter(cmd, "@ten_tai_lieu", DbType.String, doBangTheoDoi.TEN_TAI_LIEU);
db.AddInParameter(cmd, "@tinh_trang", DbType.Int64, doBangTheoDoi.TINH_TRANG);
db.AddInParameter(cmd, "@hinh_thuc_thong_bao", DbType.Int32, doBangTheoDoi.HINH_THUC_THONG_BAO);
db.AddInParameter(cmd, "@ghi_chu", DbType.String, doBangTheoDoi.GHI_CHU);
db.AddInParameter(cmd, "@nguoi_cap_nhat", DbType.Int64, doBangTheoDoi.NGUOI_CAP_NHAT);
db.AddInParameter(cmd, "@ngay_cap_nhat", DbType.DateTime, doBangTheoDoi.NGAY_CAP_NHAT);
int result2 = db.ExecuteNonQuery(cmd, dbTrans);
}
public void insert(DbCommand cmd, DbTransaction dbTrans, DatabaseFB db, DOBangTheoDoi doBangTheoDoi)
{
cmd = db.GetSQLStringCommand("insert into BANG_THEO_DOI(btd_id,ma_btd,name,ngay_lap,nguoi_lap,ngay_ky,nguoi_ky,ngay_hieu_luc,nguoi_quan_ly,tai_lieu,ten_tai_lieu,tinh_trang,hinh_thuc_thong_bao,ghi_chu,nguoi_cap_nhat,ngay_cap_nhat) "
+ "values(@btd_id,@ma_btd,@name,@ngay_lap,@nguoi_lap,@ngay_ky,@nguoi_ky,@ngay_hieu_luc,@nguoi_quan_ly,@tai_lieu,@ten_tai_lieu,@tinh_trang,@hinh_thuc_thong_bao,@ghi_chu,@nguoi_cap_nhat,@ngay_cap_nhat)");
db.AddInParameter(cmd, "@btd_id", DbType.Int64, doBangTheoDoi.BTD_ID);
db.AddInParameter(cmd, "@ma_btd", DbType.String, doBangTheoDoi.MA_BTD);
db.AddInParameter(cmd, "@name", DbType.String, doBangTheoDoi.NAME);
db.AddInParameter(cmd, "@ngay_lap", DbType.DateTime, doBangTheoDoi.NGAY_LAP);
db.AddInParameter(cmd, "@nguoi_lap", DbType.Int64, doBangTheoDoi.NGUOI_LAP);
db.AddInParameter(cmd, "@ngay_ky", DbType.DateTime, doBangTheoDoi.NGAY_KY);
db.AddInParameter(cmd, "@nguoi_ky", DbType.Int64, doBangTheoDoi.NGUOI_KY);
db.AddInParameter(cmd, "@ngay_hieu_luc", DbType.DateTime, doBangTheoDoi.NGAY_HIEU_LUC);
db.AddInParameter(cmd, "@nguoi_quan_ly", DbType.Int64, doBangTheoDoi.NGUOI_QUAN_LY);
db.AddInParameter(cmd, "@tai_lieu", DbType.Binary, doBangTheoDoi.TAI_LIEU);
db.AddInParameter(cmd, "@ten_tai_lieu", DbType.String, doBangTheoDoi.TEN_TAI_LIEU);
db.AddInParameter(cmd, "@tinh_trang", DbType.Int64, doBangTheoDoi.TINH_TRANG);
db.AddInParameter(cmd, "@hinh_thuc_thong_bao", DbType.Int32, doBangTheoDoi.HINH_THUC_THONG_BAO);
db.AddInParameter(cmd, "@ghi_chu", DbType.String, doBangTheoDoi.GHI_CHU);
db.AddInParameter(cmd, "@nguoi_cap_nhat", DbType.Int64, doBangTheoDoi.NGUOI_CAP_NHAT);
db.AddInParameter(cmd, "@ngay_cap_nhat", DbType.DateTime, doBangTheoDoi.NGAY_CAP_NHAT);
int result2 = db.ExecuteNonQuery(cmd, dbTrans);
}
