public bool insertLapLichBoPhong(LichDayVO ld)
{
try
{
string query = string.Format("insert into LichDayLyThuyet(MaGV,MaMH,MaLop,Tuan,Thu,Tiet) Values(@MaGV,@MaMH,@MaLop,@Tuan,@Thu,@Tiet)");
SqlParameter[] sqlParameters = new SqlParameter[6];
sqlParameters[0] = new SqlParameter("@MaGV", SqlDbType.VarChar);
sqlParameters[0].Value = Convert.ToString(ld.MaGV);
sqlParameters[1] = new SqlParameter("@MaMH", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(ld.MaMH);
sqlParameters[2] = new SqlParameter("@MaLop", SqlDbType.NVarChar);
sqlParameters[2].Value = Convert.ToString(ld.MaLop);
sqlParameters[3] = new SqlParameter("@Tuan", SqlDbType.VarChar);
sqlParameters[3].Value = Convert.ToString(ld.Tuan);
sqlParameters[4] = new SqlParameter("@Thu", SqlDbType.VarChar);
sqlParameters[4].Value = Convert.ToString(ld.Thu);
sqlParameters[5] = new SqlParameter("@Tiet", SqlDbType.VarChar);
sqlParameters[5].Value = Convert.ToString(ld.Tiet);
return(conn.executeInsertQuery(query, sqlParameters));
}
catch
{
return(false);
}
}
//gan phong cho cac Lich Day trong 1 BUOI(sang or chieu).
private void ganPhongChoNhom(List <LichDayVO> listLichDay)
{
List <LopVO> listLopHoc = new List <LopVO>();
// lay danh sach cac lop hoc tuong ung dua vao lich day
// va sap xep lop hoc giam dan theo so luong sinh vien.
listLopHoc = getListLopHoc(listLichDay);
List <LichDayVO> lichDayCoPhongTemp = new List <LichDayVO>();
List <LichDayVO> lichDayTemp = new List <LichDayVO>();
lichDayTemp = listLichDay;
// so luong lich day cua buoi dang can xep PHONG.
int soluongLichDay = listLichDay.Count;
PhongVO phongDuSoMay = new PhongVO();
while (listPhongTam.Count > 0 && listLichDay.Count > 0)
{
LichDayVO temp = new LichDayVO();
for (int i = 0; i < listLopHoc.Count; ++i)
{
temp = getLichDayByMaTrong1List(listLichDay, listLopHoc[i].MaLop);
phongDuSoMay = kiemPhongDuSoMay(listLopHoc[i], temp);
//kiem dc phong du so may
if (phongDuSoMay != null)//?
{
//
temp.MaPhong = phongDuSoMay.MaPhong;
listPhongTam.Remove(phongDuSoMay);
listLichDayCoPhong.Add(temp);
listLichDay.Remove(temp);
////lichDayCoPhongTemp.Add(listLichDay[j]);
//so lich day da dc xep phong tang len
demSoLichDayDcXep++;
}
else
{
//tao 1 list bao gom cac phong duoc ghep
List <PhongVO> listCacPhongDanhCho1LTH = new List <PhongVO>();
listCacPhongDanhCho1LTH = kiemThemPhongDuSoMay(listLopHoc[i], listPhongTam);
demSoLichDayTaoThem = demSoLichDayTaoThem + listCacPhongDanhCho1LTH.Count - 1;
if (listCacPhongDanhCho1LTH.Count > 1)
{
//gan nhieu phong cho 1 lichthucHanh
addLichThucHanhCungThay(temp, listCacPhongDanhCho1LTH);
lichThucHanhNonPhong.Remove(temp);
removeInListPhongTam(listPhongTam, listCacPhongDanhCho1LTH);
}
listLichDay.Remove(temp);
}
}
}
}
// kiem tra xem lich day la sang hay chieu
private bool isBuoiSang(LichDayVO LD)
{
string a = LD.Tiet.Split('-')[0];
if (Convert.ToInt32(a) < 6)
{
return(true);
}
return(false);
}
private bool checkExits(LichDayVO lichday, List <LichDayVO> lichDayDBList)
{
foreach (LichDayVO lich in lichDayDBList)
{
if (checkOverlap(lich, lichday))
{
return(true);
}
}
return(false);
}
private PhongVO kiemPhongDuSoMay(LopVO lopHocVO, LichDayVO lichDayNho)
{
for (int k = 0; k < listPhongTam.Count; k++)
{
if (listPhongTam[k].SoMay >= lopHocVO.SoLuongSV)
{
return(listPhongTam[k]);
}
}
return(null);
}
public bool themLapLichBoPhong(LichDayVO LL)
{
if (lapLichDAO.insertLapLichBoPhong(LL) == true)
{
return(true);
}
else
{
return(false);
}
}
// compare 2 dailySchedules
//if 2 dailySchedules are same (only different week)
//return true, otherwise return false
private bool compareDailySchedule(LichDayVO a, LichDayVO b)
{
if (a.MaGV.Equals(b.MaGV) && a.MaLop.Equals(b.MaLop) &&
a.MaMH.Equals(b.MaMH) && a.Thu.Equals(b.Thu) && a.Tiet.Equals(b.Tiet) && !(a.Tuan.Equals(b.Tuan)))
{
return(true);
}
else
{
return(false);
}
}
private bool cungNhom(LichDayVO a, LichDayVO b)
{
//neu a va b cung TUAN, THU, BUOI(cung sang hoac cung chieu) thì chúng chung nhóm
if (a.Tuan == b.Tuan && a.Thu == b.Thu)
{
if ((isBuoiSang(a) && isBuoiSang(b)) || ((!isBuoiSang(a)) && (!isBuoiSang(b))))
{
return(true);
}
}
return(false);
}
// kiem tra xem 2 LichDay co overlap gio nhau khong?
// ex: LichDay A: 1 - 4, LichDay B: 1 - 3 => true
private bool checkOverlap(LichDayVO a, LichDayVO b)
{
String[] arrayA = a.Tiet.Split('-');
// tiet bat dau cua LichDay A
int startA = Int32.Parse(arrayA[0]);
// tiet ket thuc cua LichDay A
int endA = Int32.Parse(arrayA[1]);
String[] arrayB = b.Tiet.Split('-');
// tiet bat dau cua LichDay A
int startB = Int32.Parse(arrayB[0]);
// tiet ket thuc cua LichDay A
int endB = Int32.Parse(arrayB[1]);
return((startA <= endB) && (endA >= startB));
}
private void btnThemLich_Click(object sender, EventArgs e)
{
if (txtTietStart.Text == "" || txtTietEnd.Text == "")
{
MessageBox.Show("Hãy điền tiết bắt đầu và kết thúc cho đầy đủ", "Thông Báo");
}
else
{
if (!lapLichBUS.checkSVPhong(cbbLop.SelectedValue + "", cbbPhong.SelectedValue + ""))
{
MessageBox.Show("Số Lượng Máy của Phòng Thực Hành nhỏ hơn Số Lượng Sinh Viên", "Thông Báo Thất Bại", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
int start = Convert.ToInt32(txtTietStart.Text);
int end = Convert.ToInt32(txtTietEnd.Text);
if (start <= end)
{
LichDayVO oneSchedule = new LichDayVO();
oneSchedule.MaGV = cbbGiaoVien.SelectedValue + "";
oneSchedule.MaLop = cbbLop.SelectedValue + "";
oneSchedule.MaMH = cbbMon.SelectedValue + "";
oneSchedule.MaPhong = cbbPhong.SelectedValue + "";
oneSchedule.Thu = Convert.ToString(((Item)cbbThu.SelectedItem).Value);
oneSchedule.Tuan = ((Item)cbbTuan.SelectedItem).Value;
oneSchedule.Tiet = txtTietStart.Text + "-" + txtTietEnd.Text;
if (lapLichBUS.themLapLich(oneSchedule))
{
MessageBox.Show("Lập Lịch Thành Công", "Thông Báo Thành Công", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Lịch Đã Trùng", "Thông Báo Thất Bại", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Nhập sai tiết", "Thông Báo Thất Bại", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void addLichThucHanhCungThay(LichDayVO lichCungThay, List <PhongVO> listCacPhongDanhCho1LTH)
{
for (int i = 0; i < listCacPhongDanhCho1LTH.Count; i++)
{
LichDayVO a = new LichDayVO();
a.MaGV = lichCungThay.MaGV;
a.MaLop = lichCungThay.MaLop;
a.MaMH = lichCungThay.MaMH;
a.MaPhong = listCacPhongDanhCho1LTH[i].MaPhong;
//lichDayTam.Ngay = lichCungThay.Ngay;
a.Thu = lichCungThay.Thu;
a.Tiet = lichCungThay.Tiet;
a.Tuan = lichCungThay.Tuan;
//
listLichDayCoPhong.Add(a);
demSoLichDayDcXep++;
}
}
public DataTable getLichDayTuongTu(LichDayVO lichDay)
{
DataTable dt = new DataTable();
string query = string.Format("select * from LichDayThucHanh where Tuan = @Tuan and Thu = @Thu and MaPhong = @MaPhong");
SqlParameter[] sqlParameters = new SqlParameter[3];
sqlParameters[0] = new SqlParameter("@Tuan", SqlDbType.Int);
sqlParameters[0].Value = lichDay.Tuan;
sqlParameters[1] = new SqlParameter("@Thu", SqlDbType.VarChar);
sqlParameters[1].Value = lichDay.Thu;
sqlParameters[2] = new SqlParameter("@MaPhong", SqlDbType.VarChar);
sqlParameters[2].Value = lichDay.MaPhong;
return(conn.executeSelectQuery(query, sqlParameters));
}
//convert from weeks of concept schedule to weeks of practice schedule.
private void convertToTuanThucHanh(List <LichDayVO> inputList)
{
if (inputList.Count > 0)
{
LichDayVO a = inputList[0];
List <Int32> arrayWeek = new List <Int32>();
// arrayweek: mang luu cac tuan cua ly thuyet.
foreach (LichDayVO lichDay in inputList)
{
arrayWeek.Add(lichDay.Tuan);
}
for (int i = 1; i <= 15; i++)
{
if (!arrayWeek.Contains(i))
{
lichThucHanhNonPhong.Add(new LichDayVO(a.MaGV, a.MaMH, a.MaLop, a.MaPhong, i, a.Thu, a.Tiet));
}
}
}
}
public bool themLapLich(LichDayVO LL)
{
DataTable dt = new DataTable();
dt = lapLichDAO.getLichDayTuongTu(LL);
if (dt != null && dt.Rows.Count != 0)
{
List <LichDayVO> dsLichDay = new List <LichDayVO>();
foreach (DataRow dr in dt.Rows)
{
LichDayVO lichDayVO = new LichDayVO();
lichDayVO.MaGV = dr[0].ToString();
lichDayVO.MaMH = dr[1].ToString();
lichDayVO.MaLop = dr[2].ToString();
lichDayVO.MaPhong = dr[3].ToString();
lichDayVO.Tuan = Convert.ToInt32(dr[5]);
lichDayVO.Thu = dr[6].ToString();
lichDayVO.Tiet = dr[7].ToString();
dsLichDay.Add(lichDayVO);
}
if (checkExits(LL, dsLichDay))
{
return(false);
}
}
if (lapLichDAO.insertLapLichThucHanh(LL) == true)
{
return(true);
}
else
{
return(false);
}
}
private void importExelToSQL(DataTable dt)
{
if (dt != null)
{
for (int i = 3; i < dt.Rows.Count; i++)
{
for (int j = 3; j < dt.Columns.Count; j++)
{
string str = dt.Rows[i].ItemArray[j].ToString();
if (str != "")
{
if (str != " " || str != "\n" || str != " " || str != " ")
{
String[] mang = str.Split('(');
String[] ten = mang[0].Split('-');
string tenGV = ten[0];
string tenMH = ten[1];
string tenLop = ten[2];
string tiet = mang[1].Split(')')[0];
GiaoVienVO GV = new GiaoVienVO();
MonHocVO MH = new MonHocVO();
LopVO LH = new LopVO();
//lay ma GV thong qua TenGV
GV.TenGV = tenGV;
//tao maGV dua vao ten
//GV.TenGV=tenGV.Split(' ')o
GiaoVienVO gv = giaoVienBUS.getGiaoVienByName(GV);
//lay maMH thong qua TenMH
MH.TenMonHoc = tenMH;
MonHocVO mh = monHocBUS.getMonHocByName(MH);
//Lay MaLop Thong Qua Ten
LH.TenLop = tenLop;
LopVO lh = lopHocBUS.getLopHocByName(LH);
LichDayVO LD = new LichDayVO();
LD.MaGV = gv.MaGV;
LD.MaMH = mh.MaMH;
LD.MaLop = lh.MaLop;
LD.Thu = j - 1 + "";
LD.Tiet = tiet;
//cat chuoi lay tuan
string chuoi = dt.Rows[2].ItemArray[0].ToString();
string layChuoiCoTuan = (chuoi.Split('\n')[0]).Trim();
string tuanDangString = (layChuoiCoTuan.Split(' ')[1]).Trim();
int tuan = Convert.ToInt32(tuanDangString);
LD.Tuan = tuan;
lapLichBUS.themLapLichBoPhong(LD);
//LD.MaPhong = "P001"; ->khoi truyen
//if (lapLichBUS.themLapLichBoPhong(LD))
// MessageBox.Show("Da Them Vao CSDL");
//else
// MessageBox.Show("ko them vao CSDL duoc");
}
}
}
}
}
}
public void createDoc()
{
try
{
//listResult = lapLichBUS.layLichDayThucHanhDaCoPhong();
System.Data.DataTable dtLichThucHanh = new System.Data.DataTable();
dtLichThucHanh = lapLichDAO.GetAllLichDayThucHanh();
if (dtLichThucHanh != null && dtLichThucHanh.Rows.Count != 0)
{
foreach (DataRow dr in dtLichThucHanh.Rows)
{
LichDayVO lichDayVO = new LichDayVO();
lichDayVO.MaGV = dr[0].ToString();
lichDayVO.MaMH = dr[1].ToString();
lichDayVO.MaLop = dr[2].ToString();
lichDayVO.MaPhong = dr[3].ToString();
lichDayVO.Tuan = Convert.ToInt32(dr[5]);
lichDayVO.Thu = dr[6].ToString();
lichDayVO.Tiet = dr[7].ToString();
listResult.Add(lichDayVO);
}
}
listPhong = getListPhong();
Console.Write(listPhong.Count);
Application oXL = new Application();
oXL.Visible = false;
oXL.StandardFont = "Times New Roman";
oXL.StandardFontSize = 12;
Workbook oWB = oXL.Workbooks.Add(missing);
Worksheet oSheet15 = oWB.ActiveSheet as Worksheet
as Worksheet;
oSheet15.Name = "T15";
Worksheet oSheet14 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet14.Name = "T14";
Worksheet oSheet13 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet13.Name = "T13";
Worksheet oSheet12 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet12.Name = "T12";
Worksheet oSheet11 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet11.Name = "T11";
Worksheet oSheet10 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet10.Name = "T10";
Worksheet oSheet9 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet9.Name = "T09";
Worksheet oSheet8 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet8.Name = "T08";
Worksheet oSheet7 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet7.Name = "T07";
Worksheet oSheet6 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet6.Name = "T06";
Worksheet oSheet5 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet5.Name = "T05";
Worksheet oSheet4 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet4.Name = "T04";
Worksheet oSheet3 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet3.Name = "T03";
Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet2.Name = "T02";
Worksheet oSheet1 = oWB.Sheets.Add(missing, missing, 1, missing)
as Worksheet;
oSheet1.Name = "T01";
List <Worksheet> listWorksheet = new List <Worksheet>();
createSheet(oSheet1);
createSheet(oSheet2);
createSheet(oSheet3);
createSheet(oSheet4);
createSheet(oSheet5);
createSheet(oSheet6);
createSheet(oSheet7);
createSheet(oSheet8);
createSheet(oSheet9);
createSheet(oSheet10);
createSheet(oSheet11);
createSheet(oSheet12);
createSheet(oSheet13);
createSheet(oSheet14);
createSheet(oSheet15);
listWorksheet.Add(oSheet1);
listWorksheet.Add(oSheet2);
listWorksheet.Add(oSheet3);
listWorksheet.Add(oSheet4);
listWorksheet.Add(oSheet5);
listWorksheet.Add(oSheet6);
listWorksheet.Add(oSheet7);
listWorksheet.Add(oSheet8);
listWorksheet.Add(oSheet9);
listWorksheet.Add(oSheet10);
listWorksheet.Add(oSheet11);
listWorksheet.Add(oSheet12);
listWorksheet.Add(oSheet13);
listWorksheet.Add(oSheet14);
listWorksheet.Add(oSheet15);
// add add to each sheet.
List <LichDayVO> temp = new List <LichDayVO>();
foreach (Worksheet workSheet in listWorksheet)
{
int week = Int32.Parse(workSheet.Name.Substring(1));
temp.Clear();
temp = getLichDayByWeek(week);
addDataToExcel(workSheet, temp);
}
string fileName = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)
+ "\\LichThucHanhKhoaCNTT.xlsx";
//oWB.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
// missing, missing, missing, missing,
// Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
// missing, missing, missing, missing, missing);
//oWB.Close(missing, missing, missing);
oXL.Visible = true;
oXL.UserControl = true;
//oXL.Quit();
}
catch (Exception e)
{
Console.Write("Error");
}
finally
{
}
}
public int taoLichThucHanh()
{
int dem = 0, soLuongLTH = 0;
lichThucHanhNonPhong = new List <LichDayVO>();
DataTable dataTable = new DataTable();
// lay lich ly thuyet tu database len va map tao thanh List<LichDay> lythuyet
dataTable = lapLichDAO.GetAllLichDayLyThuyet();
if (dataTable != null)
{
List <LichDayVO> dsLichDay = new List <LichDayVO>();
foreach (DataRow dr in dataTable.Rows)
{
LichDayVO lichDayVO = new LichDayVO();
lichDayVO.MaGV = dr[0].ToString();
lichDayVO.MaMH = dr[1].ToString();
lichDayVO.MaLop = dr[2].ToString();
lichDayVO.MaPhong = dr[3].ToString();
lichDayVO.Tuan = Convert.ToInt32(dr[5]);
lichDayVO.Thu = dr[6].ToString();
lichDayVO.Tiet = dr[7].ToString();
dsLichDay.Add(lichDayVO);
}
LichDayVO lichTam = new LichDayVO();
List <LichDayVO> dsLichDayNho = new List <LichDayVO>();
while (dsLichDay.Count > 0)
{
dsLichDayNho.Clear();
// add first element of list into dsLichDayNho
// and remove it from dsLichDay
lichTam = dsLichDay[0];
dsLichDayNho.Add(lichTam);
dsLichDay.Remove(lichTam);
for (int i = dsLichDay.Count - 1; i >= 0; i--)
{
// cung giao vien, cung mon hoc, cung lop, cung thu, cung tiet, KHAC TUAN
if (compareDailySchedule(dsLichDay[i], lichTam))
{
dsLichDayNho.Add(dsLichDay[i]);
dsLichDay.RemoveAt(i);
}
}
// chuyen sang Tuan Thuc Hanh
// vi du ly thuyet mon a, giao vien b, lop c, hoc thứ 2, tiet (1- 5) tuần 2, 3, 4, 6
// -> tuan 1,5,7,8,9,10,11,12,13,14,15 (tuong ung tuan thuc hanh)
convertToTuanThucHanh(dsLichDayNho);
}
// so luong lich thuc hanh
soLuongLTH = lichThucHanhNonPhong.Count;
lichThucHanhNonPhong.Count();
//dem la so luong lichThucHanhNonPhong da duoc xep phong
dem = xepPhong(lichThucHanhNonPhong);
listLichDayCoPhong.Count();
insertToSQLLichThucHanh();
}
if (dem == 0)
{
return(0);
}
else
if (dem == soLuongLTH)
{
return(1);
}
else
{
return(2);
}
}
