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); } }