private void ClassForm_FormClosing(object sender, FormClosingEventArgs e)
{
dgvClass[0, 0].Selected = true;
// ----------------------------------------------------------------------------------------------
//
var db = new LINQDataContext();
//
// Delete Group_ID_List Where Class_ID == this.ID
//
db.Group_ID_ListDeleteAll();
db.Priority_ProfessorDelete();
for (int rowCounter = 0; rowCounter < dgvClass.RowCount - 1; rowCounter++)
{
try
{
//
// search dgvClass.rows in db.Class
//
int id_class = int.Parse(dgvClass[0, rowCounter].Value.ToString());
var query = (from QC in db.Classes
where QC.Class_ID == id_class
select QC).SingleOrDefault();
if (dgvClass[2, rowCounter].Value.ToString() == "")
{
new LINQDataContext().ClassDelete(int.Parse(dgvClass[0, rowCounter].Value.ToString()));
throw new Exception("Null Professor Value's");
}
if (query != null) // edit (UPDATE)
{
int PU = 0;
int TU = 0;
decimal code = 0;
db.ClassEdit(id_class, (dgvClass[3, rowCounter].Value != null) ?
(from courseID in db.Courses
where (courseID.Name_Course == dgvClass[3, rowCounter].Value.ToString()) &&
(courseID.CourseCode == ((decimal.TryParse(dgvClass[4, rowCounter].Value.ToString(), out code)) ? code : 0))
select courseID.Course_ID).Single() : 0,
(int.TryParse(dgvClass[5, rowCounter].Value.ToString(), out PU)) ? PU : 0,
(int.TryParse(dgvClass[6, rowCounter].Value.ToString(), out TU)) ? TU : 0,
dgvClass[7, rowCounter].Value.ToString());
}
else //save (INSERT or Create NEW)
{
int PU = 0;
int TU = 0;
decimal code = 0;
db.ClassSave(id_class, (dgvClass[3, rowCounter].Value != null) ?
(from courseID in db.Courses
where (courseID.Name_Course == dgvClass[3, rowCounter].Value.ToString()) &&
(courseID.CourseCode == ((decimal.TryParse(dgvClass[4, rowCounter].Value.ToString(), out code)) ? code : 0))
select courseID.Course_ID).Single() : 0,
(int.TryParse(dgvClass[5, rowCounter].Value.ToString(), out PU)) ? PU : 0,
(int.TryParse(dgvClass[6, rowCounter].Value.ToString(), out TU)) ? TU : 0,
dgvClass[7, rowCounter].Value.ToString());
}
//
// Group in db.Group_ID_List
//
saveAndCompile_Groups((dgvClass[9, rowCounter].Value != null) ?
dgvClass[9, rowCounter].Value.ToString() : " ",
Convert.ToInt32(dgvClass[0, rowCounter].Value.ToString()));
//
// Priority in db.Priority_Professors
//
saveAndCompile_Priority((dgvClass[2, rowCounter].Value != null) ?
dgvClass[2, rowCounter].Value.ToString() : " ",
Convert.ToInt32(dgvClass[0, rowCounter].Value.ToString()));
}
catch { }
}
}
private void chkFragmental_CheckedChanged(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
try
{
ResultForm._setting.Fragmental_Classes = chkFragmental.Checked;
if (ResultForm._setting.Fragmental_Classes)
{
#region Fragment the Class more than two units
var dbClasses = DB.Classes.ToList();
//
/// A list for store any class by unit more than 2.
/// Tuple< Classes, Unit >
List <Tuple <Class, int> > lstFragmentClass = new List <Tuple <Class, int> >();
//
// Delete old classroom, because it has this class information!
new LINQDataContext().Classroom_TimeDeleteAll();
//
// Search for any class by unit more than 2.
foreach (var any in dbClasses)
{
if (any.Practical_unit + any.Theory_unit > 2)
{
lstFragmentClass.Add(new Tuple <Class, int>(any, any.Practical_unit + any.Theory_unit));
}
}
//
// Read any Finded Class and Fragment that to 2 Unit Quantums.
foreach (var anyClass in lstFragmentClass)
{
int u = 0;
do
{
int id = findNewClassID(); // create a new class ID
//
// delete old and hyper data
DB.Group_ID_ListDeleteClass(id);
DB.Priority_ProfessorDeleteClass(id);
//
Class c = new Class() // create new Classes
{
Class_ID = id,
Course_ID = anyClass.Item1.Course_ID,
RoomType = anyClass.Item1.RoomType,
Practical_unit = 0,
Theory_unit = ((anyClass.Item2 - u) >= 2) ? 2 : 1
};
//
// create new Group_ID_List for this new Class
List <Group_ID_List> g = new List <Group_ID_List>();
g.AddRange(anyClass.Item1.Group_ID_Lists);
for (int i = 0; i < g.Count; i++)
{
g[i].Class_ID = id;
}
//
// create new Priority_Professor for this new Class
List <Priority_Professor> p = new List <Priority_Professor>();
p.AddRange(anyClass.Item1.Priority_Professors);
for (int i = 0; i < p.Count; i++)
{
p[i].Class_ID = id;
}
//
// add new data to DB
DB.ClassSave(c.Class_ID, c.Course_ID, c.Practical_unit, c.Theory_unit, c.RoomType);
foreach (var anyG in g)
{
DB.Group_ID_ListSave(anyG.Group_ID, anyG.Class_ID);
}
foreach (var anyP in p)
{
DB.Priority_ProfessorSave(anyP.Professor_ID, anyP.Class_ID, anyP.Priority);
}
//DB.Classes.InsertOnSubmit(c);
//DB.SubmitChanges();
//DB.Group_ID_Lists.InsertAllOnSubmit(g);
//DB.Priority_Professors.InsertAllOnSubmit(p);
//DB.SubmitChanges();
u += 2;
}while (anyClass.Item2 - u > 0);
//
// delete old class data (original class who has more than 2 units)
DB.Group_ID_ListDeleteClass(anyClass.Item1.Class_ID);
DB.Priority_ProfessorDeleteClass(anyClass.Item1.Class_ID);
DB.ClassDelete(anyClass.Item1.Class_ID);
}
#endregion
MakeClassSchedule.Algorithm.Configuration.GetInstance.ParseFile(new LINQDataContext());
chkFragmental.Enabled = false;
}
}
finally
{
this.Cursor = Cursors.Default;
}
}
private void ImportMDF()
{
try
{
Set_Cursor_Value("WaitCursor");
var originDB = new LINQDataContext();
var newDB = new LINQDataContext(FileName);
Set_prbMain_Value(0);
#region 1. Professor DATA
//
// Delete old Professor data and Fill it by newDB data
//
var aryProfessor = newDB.Professors.ToArray();
if (aryProfessor.Length > 0)
{
string query = @"DELETE FROM dbo.New_GroupsPerClass " + @"DELETE FROM dbo.Classroom_time "
+ @"DELETE FROM dbo.Priority_Professor " + @"DELETE FROM dbo.Professor";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryProfessor)
{
originDB.ProfessorSave(row.ID, row.Name_Professor, row.Branch, row.Email, row.EducationDegree, row.Schedule);
}
}
#endregion
Set_prbMain_Value(10);
#region 2. Branch DATA
//
// Delete old Branch data and Fill it by newDB data
//
var aryBranch = newDB.Branches.ToArray();
if (aryBranch.Length > 0)
{
string query = @"DELETE FROM dbo.New_GroupsPerClass " + @"DELETE FROM dbo.Group_ID_List " + @"DELETE FROM dbo.Classroom_Time "
+ @"DELETE FROM dbo.Class " + @"DELETE FROM dbo.Course "
+ @"DELETE FROM dbo.Groups " + @"DELETE FROM dbo.Branch";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryBranch)
{
originDB.BranchSave(row.ID_Branch, row.Branch_Name, row.Degree);
}
}
#endregion
Set_prbMain_Value(20);
#region 3. Room_Type DATA
//
// Delete old Room_Type data and Fill it by newDB data
//
var aryRoom_Type = newDB.Room_Types.ToArray();
if (aryRoom_Type.Length > 0)
{
string query = @"DELETE FROM dbo.New_GroupsPerClass " + @"DELETE FROM dbo.Classroom_time "
+ @"DELETE FROM dbo.Room " + @"DELETE FROM dbo.Priority_Professor "
+ @"DELETE FROM dbo.Group_ID_List "
+ @"DELETE FROM dbo.Class " + @"DELETE FROM dbo.Room_Type";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryRoom_Type)
{
originDB.Room_TypeSave(row.Type_Name);
}
}
#endregion
Set_prbMain_Value(30);
#region 4. Course DATA
//
// Delete old Course data and Fill it by newDB data
//
var aryCourse = newDB.Courses.ToArray();
if (aryCourse.Length > 0)
{
string query = @"DELETE FROM dbo.New_GroupsPerClass " + @"DELETE FROM dbo.Classroom_time "
+ @"DELETE FROM dbo.Group_ID_List " + @"DELETE FROM dbo.Priority_Professor "
+ @"DELETE FROM dbo.Class " + @"DELETE FROM dbo.Course";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryCourse)
{
originDB.CourseSave(row.Course_ID, row.Branch_ID,
row.TermNo, row.Name_Course,
row.Type_Course, row.CourseCode,
row.TheoryUnitNo, row.PracticalUnitNo,
row.InRequisite_CourseID, row.PreRequisite_CourseID);
}
}
#endregion
Set_prbMain_Value(40);
#region 5. Groups DATA
//
// Delete old Group data and Fill it by newDB data
//
var aryGroup = newDB.Groups.ToArray();
if (aryGroup.Length > 0)
{
string query = @"DELETE FROM dbo.New_GroupsPerClass " +
@"DELETE FROM dbo.Group_ID_List " +
@"DELETE FROM dbo.Groups";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryGroup)
{
originDB.GroupsSave(row.ID, row.Semester_Entry_Year, row.Semester_Entry_FS, row.Branch_Selection, row.Size_No);
}
}
#endregion
Set_prbMain_Value(50);
#region 6. Room DATA
//
// Delete old Room data and Fill it by newDB data
//
var aryRoom = newDB.Rooms.ToArray();
if (aryRoom.Length > 0)
{
string query = @"DELETE FROM dbo.New_GroupsPerClass " + @"DELETE FROM dbo.Classroom_time " + @"DELETE FROM dbo.Room";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryRoom)
{
originDB.RoomSave(row.Room_ID, row.Name_Room, row.Type_Room, row.Size_No);
}
}
#endregion
Set_prbMain_Value(60);
#region 7. Class DATA
//
// Delete old Class data and Fill it by newDB data
//
var aryClass = newDB.Classes.ToArray();
if (aryClass.Length > 0)
{
string query = @"DELETE FROM dbo.New_GroupsPerClass " + @"DELETE FROM dbo.Group_ID_List " + @"DELETE FROM dbo.Classroom_Time "
+ @"DELETE FROM dbo.Priority_Professor " + @"DELETE FROM dbo.Class";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryClass)
{
originDB.ClassSave(row.Class_ID, row.Course_ID, row.Practical_unit, row.Theory_unit, row.RoomType);
}
}
#endregion
Set_prbMain_Value(70);
#region 8. Group_ID_List DATA
//
// Delete old Group_ID_List data and Fill it by newDB data
//
var aryGroup_ID_List = newDB.Group_ID_Lists.ToArray();
if (aryGroup_ID_List.Length > 0)
{
string query = @"DELETE FROM dbo.Group_ID_List";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryGroup_ID_List)
{
originDB.Group_ID_ListSave(row.Group_ID, row.Class_ID);
}
}
#endregion
Set_prbMain_Value(80);
#region 9. Priority_Professor DATA
//
// Delete old Classroom_Time data and Fill it by newDB data
//
var aryPriority_Professor = newDB.Priority_Professors.ToArray();
if (aryPriority_Professor.Length > 0)
{
string query = @"DELETE FROM dbo.Priority_Professor";
originDB.ExecuteQuery<object>(query);
//
// read and save new data
//
foreach (var row in aryPriority_Professor)
{
originDB.Priority_ProfessorSave(row.Professor_ID, row.Class_ID, row.Priority);
}
}
#endregion
Set_prbMain_Value(90);
#region 10. Classroom_Time DATA
//
// Delete old Classroom_Time data and Fill it by newDB data
//
var aryClassroom_Time = newDB.Classroom_Times.ToArray();
var aryNew_GroupsPerClass = newDB.New_GroupsPerClasses.ToArray();
if (aryClassroom_Time.Length > 0)
{
originDB.Classroom_TimeDeleteAll();
//
// read and save new data about Classroom_Time
//
foreach (var row in aryClassroom_Time)
{
originDB.Classroom_TimeSave(row.Room_ID, row.Class_ID,
row.Professor_ID, row.StartTime, row.Duration, row.Day_No);
}
//
// read and save new data about New_GroupsPerClass
//
foreach (var row in aryNew_GroupsPerClass)
{
originDB.New_GroupsPerClassSave(row.Room_ID, row.Class_ID, row.StartTime, row.Day_No, row.Group_ID);
}
}
#endregion
Set_prbMain_Value(98);
originDB.Connection.Close();
originDB.Dispose();
newDB.Connection.Close();
newDB.Dispose();
Set_prbMain_Value(100);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "اشکال در بازیابی اطلاعات");
thImport.Abort();
return;
}
finally
{
Thread.CurrentThread.Join(1000);
Set_prbMain_Value(0);
Set_Cursor_Value("Default");
bwExportBtnCheck.RunWorkerAsync();
Thread.CurrentThread.Abort();
}
}
private void ClassForm_FormClosing(object sender, FormClosingEventArgs e)
{
dgvClass[0, 0].Selected = true;
// ----------------------------------------------------------------------------------------------
//
var db = new LINQDataContext();
//
// Delete Group_ID_List Where Class_ID == this.ID
//
db.Group_ID_ListDeleteAll();
db.Priority_ProfessorDelete();
for (int rowCounter = 0; rowCounter < dgvClass.RowCount - 1; rowCounter++)
{
try
{
//
// search dgvClass.rows in db.Class
//
int id_class = int.Parse(dgvClass[0, rowCounter].Value.ToString());
var query = (from QC in db.Classes
where QC.Class_ID == id_class
select QC).SingleOrDefault();
if (dgvClass[2, rowCounter].Value.ToString() == "")
{
new LINQDataContext().ClassDelete(int.Parse(dgvClass[0, rowCounter].Value.ToString()));
throw new Exception("Null Professor Value's");
}
if (query != null) // edit (UPDATE)
{
int PU = 0;
int TU = 0;
decimal code = 0;
db.ClassEdit(id_class, (dgvClass[3, rowCounter].Value != null) ?
(from courseID in db.Courses
where (courseID.Name_Course == dgvClass[3, rowCounter].Value.ToString()) &&
(courseID.CourseCode == ((decimal.TryParse(dgvClass[4, rowCounter].Value.ToString(), out code)) ? code : 0))
select courseID.Course_ID).Single() : 0,
(int.TryParse(dgvClass[5, rowCounter].Value.ToString(), out PU)) ? PU : 0,
(int.TryParse(dgvClass[6, rowCounter].Value.ToString(), out TU)) ? TU : 0,
dgvClass[7, rowCounter].Value.ToString());
}
else //save (INSERT or Create NEW)
{
int PU = 0;
int TU = 0;
decimal code = 0;
db.ClassSave(id_class, (dgvClass[3, rowCounter].Value != null) ?
(from courseID in db.Courses
where (courseID.Name_Course == dgvClass[3, rowCounter].Value.ToString()) &&
(courseID.CourseCode == ((decimal.TryParse(dgvClass[4, rowCounter].Value.ToString(), out code)) ? code : 0))
select courseID.Course_ID).Single() : 0,
(int.TryParse(dgvClass[5, rowCounter].Value.ToString(), out PU)) ? PU : 0,
(int.TryParse(dgvClass[6, rowCounter].Value.ToString(), out TU)) ? TU : 0,
dgvClass[7, rowCounter].Value.ToString());
}
//
// Group in db.Group_ID_List
//
saveAndCompile_Groups((dgvClass[9, rowCounter].Value != null) ?
dgvClass[9, rowCounter].Value.ToString() : " ",
Convert.ToInt32(dgvClass[0, rowCounter].Value.ToString()));
//
// Priority in db.Priority_Professors
//
saveAndCompile_Priority((dgvClass[2, rowCounter].Value != null) ?
dgvClass[2, rowCounter].Value.ToString() : " ",
Convert.ToInt32(dgvClass[0, rowCounter].Value.ToString()));
}
catch { }
}
}
private void pbtnClassImport_Click(object sender, EventArgs e)
{
// If using Professional version, put your serial key below. Otherwise, keep following
// line commented out as Free version doesn't have SetLicense method.
// SpreadsheetInfo.SetLicense("YOUR-SERIAL-KEY-HERE");
var db = new LINQDataContext(); // for save to OLEDB db.Class.mdf database file's
ExcelFile ef = new ExcelFile();
OpenFileDialog openFromExcel = new OpenFileDialog();
openFromExcel.Filter = @"Excel files|*.xls";
if (openFromExcel.ShowDialog() == DialogResult.OK)
{
ExcelWorksheet sheetClass;
ExcelWorksheet sheetGroup_ID_List;
try
{
ef.LoadXls(openFromExcel.FileName);
sheetClass = ef.Worksheets["Class"];
sheetGroup_ID_List = ef.Worksheets["Group_ID_List"];
}
catch (Exception ex) { MessageBox.Show(ex.Message); return; }
#region Class Worksheet's
bool FirstRow = true;
foreach (ExcelRow row in sheetClass.Rows)
{
if (!FirstRow)
{
try
{
int buffer = 0;
db.ClassSave(Convert.ToInt32(row.Cells[0].Value.ToString()),
(int.TryParse(row.Cells[3].Value.ToString(), out buffer)) ? buffer : 0,
(int.TryParse(row.Cells[5].Value.ToString(), out buffer)) ? buffer : 0,
(int.TryParse(row.Cells[6].Value.ToString(), out buffer)) ? buffer : 0,
row.Cells[7].Value.ToString());
//
// Compile Professor ID (Priority)
//
saveAndCompile_Priority((row.Cells[1].Value != null) ?
row.Cells[1].Value.ToString() : " ",
Convert.ToInt32(row.Cells[0].Value.ToString()));
}
catch (Exception ex) { MessageBox.Show(ex.Message); return; }
}
else FirstRow = false;
}
#endregion
#region Group_ID_List Worksheet's
FirstRow = true;
foreach (ExcelRow row in sheetGroup_ID_List.Rows)
{
if (!FirstRow)
{
try
{
int buffer_class = 0;
int buffer_group = 0;
db.Group_ID_ListSave(int.TryParse(row.Cells[1].Value.ToString(), out buffer_group) ? buffer_group : 0,
int.TryParse(row.Cells[0].Value.ToString(), out buffer_class) ? buffer_class : 0);
}
catch (Exception ex) { MessageBox.Show(ex.Message); return; }
}
else FirstRow = false;
}
#endregion
}
}