public static IQueryable <PERSONEL> SearchAdmin(string fname, string lname, string login, string role, string active)
{
LINQDataContext db = new LINQDataContext();
if (active == "A")
{
var result = from adm in db.PERSONEL
where adm.first_name.StartsWith(fname) &&
adm.last_name.StartsWith(lname) &&
adm.login.StartsWith(login) &&
adm.role.StartsWith(role) &&
adm.active.StartsWith("")
select adm;
return(result);
}
else
{
var result = from adm in db.PERSONEL
where adm.first_name.StartsWith(fname) &&
adm.last_name.StartsWith(lname) &&
adm.login.StartsWith(login) &&
adm.role.StartsWith(role) &&
adm.active.StartsWith(active)
select adm;
return(result);
}
}
private void Save(Schedule schedule)
{
int numberOfRooms = Configuration.GetInstance.GetNumberOfRooms();
int daySize = schedule.day_Hours * numberOfRooms;
var db = new LINQDataContext();
db.Classroom_TimeDeleteAll();
foreach (KeyValuePair <CourseClass, int> it in schedule.GetClasses().ToList())
{
// coordinate of time-space slot
int pos = it.Value; // int pos of _slot array
int day = pos / daySize;
int time = pos % daySize; // this is not time now!
int room = time / schedule.day_Hours;
time = time % schedule.day_Hours; // this is a time now!
int dur = it.Key.GetDuration;
CourseClass cc = it.Key;
Algorithm.Room r = Configuration.GetInstance.GetRoomById(room);
//
// Save Classroom_Time
//
db.Classroom_TimeSave(r.Origin_ID_inDB, cc.Class_ID, cc.GetProfessor.GetId, time, dur, day);
//
// Save New_GroupsPerClassroom
//
foreach (var gs in cc.GetGroups)
{
db.New_GroupsPerClassSave(r.Origin_ID_inDB, cc.Class_ID, time, day, gs.GetId);
}
}
}
private void dgvCourse_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
{
var db = new LINQDataContext();
// query for read any course ID and creat new iD
IEnumerable <int> qCourseID = from queryId in db.Courses
select queryId.Course_ID;
List <int> idList_in_db = qCourseID.ToList();
if (e.RowIndex > 0)
{
int ID_Counter = 1;
bool Find = true;
while (Find)
{
Find = false;
for (int rowC = 0; rowC < dgvCourse.RowCount - 2; rowC++)
{
if (int.Parse(dgvCourse[0, rowC].Value.ToString()) == ID_Counter)
{
ID_Counter++;
Find = true;
break;
}
}
if (idList_in_db.Contains(ID_Counter))
{
ID_Counter++;
Find = true;
}
}
dgvCourse.Rows[e.RowIndex - 1].Cells[0].Value = ID_Counter;
}
}
private void ProfessorForm_Load(object sender, EventArgs e)
{
dgvProfessor.Rows.Clear();
var db = new LINQDataContext();
var aryReader = db.Professors.ToArray();
if (aryReader.Length > 0)
{
dgvProfessor.Rows.Add(aryReader.Length);
for (int rowCounter = 0; rowCounter < aryReader.Length; rowCounter++)
{
dgvProfessor[0, rowCounter].Value = aryReader[rowCounter].ID.ToString();
if (aryReader[rowCounter].Name_Professor != null)
{
dgvProfessor[1, rowCounter].Value = aryReader[rowCounter].Name_Professor.ToString();
}
dgvProfessor[2, rowCounter].Value = (aryReader[rowCounter].Branch != null) ? aryReader[rowCounter].Branch.ToString() : "";
dgvProfessor[3, rowCounter].Value = (aryReader[rowCounter].Email != null) ? aryReader[rowCounter].Email.ToString() : "";
dgvProfessor[4, rowCounter].Value = (aryReader[rowCounter].EducationDegree != null) ? aryReader[rowCounter].EducationDegree.ToString() : "";
if (aryReader[rowCounter].Schedule != null)
{
dgvProfessor[6, rowCounter].Value = aryReader[rowCounter].Schedule.ToString();
}
}
}
}
public static IQueryable <CLIENT> GetAllClients()
{
LINQDataContext db = new LINQDataContext();
var result = from clients in db.CLIENT select clients;
return(result);
}
public static IQueryable <string> GetAvailableActivity()
{
LINQDataContext db = new LINQDataContext();
var result = from e in db.ACT_DICT
select e.act_name;
return(result);
}
private void menuChiTietPhong_Click(object sender, EventArgs e)
{
FrmChiTietPhong frm = new FrmChiTietPhong();
frm.MaNV = MaNV;
frm.ShowDialog();
db = new LINQDataContext();
}
public AddEmployeeController(AddEmployee model, AddEmployeeView view, Form curForm)
{
db = new LINQDataContext();
this.model = model;
this.view = view;
this.curForm = curForm;
view.BtnCreate.Click += (sender, e) => btnCreate_Click(sender, e);
}
public static IQueryable <string> GetAllObjectTypes()
{
LINQDataContext db = new LINQDataContext();
var result = from types in db.OBJ_TYPE
select types.name;
return(result);
}
public static IQueryable <REQUEST> SearchRequests()
{
LINQDataContext db = new LINQDataContext();
var result = from e in db.REQUEST
select e;
return(result);
}
/*public static void GetAvailableWorkers(out IQueryable<string> firstName, out IQueryable<string> lastName) //tak powinno byc ale nie dziala
* {
* LINQDataContext db = new LINQDataContext();
* var result = from e in db.PERSONEL
* where e.active.ToString().Equals("T") && e.role.ToString().Equals("Worker")
* select new
* {
* firstName = e.first_name,
* lastName = e.last_name
* };
* }*/
public static IQueryable <string> GetAvailableWorkers()//w tym wypadku jesli bdmy mieli 2 pracownikow o tym samym nazwisku to nwmy ktorego wybieramy
{
LINQDataContext db = new LINQDataContext();
var result = from e in db.PERSONEL
where e.active.ToString().Equals("T") && e.role.ToString().Equals("Worker")
select e.last_name;
return(result);
}
public static IQueryable <OBJECT> SearchObjects2(string id) //po id klienta
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.OBJECT
where adm.id_client.Equals(id)
select adm;
return(result);
}
public EditEmployeeController(EditEmployee model, EditEmployeeView view, Form curForm)
{
db = new LINQDataContext();
this.model = model;
this.view = view;
this.curForm = curForm;
this.view.ComboName.SelectedIndexChanged += (sender, e) => comboName_SelectedIndexChanged(sender, e);
this.view.BtnEdit.Click += (sender, e) => btnEdit_Click(sender, e);
}
public static IQueryable <OBJECT> GetCustomerObject(object id)
{
LINQDataContext db = new LINQDataContext();
var result = from e in db.OBJECT
where e.id_client.Equals(id)
select e;
return(result);
}
public static IQueryable <OBJECT> ChooseObject(object id)
{
LINQDataContext db = new LINQDataContext();
var result = from e in db.OBJECT
where e.nr_object.Equals(id)
select e;
return(result);
}
public static IQueryable <string> SearchObjectTypeFromName(string name)
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.OBJ_TYPE
where adm.name.Equals(name)
select adm.type;
return(result);
}
public static int GetActivityNumber(Int32 id)
{
LINQDataContext db = new LINQDataContext();
var result = from e in db.ACTIVITY
where e.id_request.Equals(id)
select e;
return(result.Count() + 1);
}
public static IQueryable <string> SearchActivityDictionaryType(string act_type)
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.ACT_DICT
where adm.act_type.Equals(act_type)
select adm.act_name;
return(result);
}
public static IQueryable <CLIENT> SearchCustomerFromID(string id)
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.CLIENT
where adm.id_client.Equals(id)
select adm;
return(result);
}
public static IQueryable <ACTIVITY> SearchActivity(string id)
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.ACTIVITY
where adm.id_request.Equals(id)
select adm;
return(result);
}
public static IQueryable <OBJ_TYPE> SearchObjType(string type)
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.OBJ_TYPE
where adm.type.Equals(type)
select adm;
return(result);
}
public static IQueryable <OBJECT> SearchObjects3(string id) //po numerze rej
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.OBJECT
where adm.nr_object.Equals(id)
select adm;
return(result);
}
public static IQueryable <CLIENT> ChooseCustomer(object id)
{
LINQDataContext db = new LINQDataContext();
var result = from e in db.CLIENT
where e.id_client.Equals(id)
select e;
return(result);
}
public static string GetDefaultVehicle()
{
string ss = "";
LINQDataContext db = new LINQDataContext();
var defaultVehicle = from e in db.OBJECT
select e.nr_object;
ss = defaultVehicle.First().ToString();
return(ss);
}
private void dgvType_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
var db = new LINQDataContext();
if (dgvType[e.ColumnIndex, e.RowIndex].Value != null)
{
db.Room_TypeSave(dgvType[e.ColumnIndex, e.RowIndex].Value.ToString());
refreshItems();
dgvRoom.Refresh();
}
}
private void dgvCourse_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if ((e.ColumnIndex == 3 || e.ColumnIndex == 5) && e.RowIndex >= 0 && e.RowIndex < dgvCourse.RowCount - 1)
{
SelectCourseForm scForm = new SelectCourseForm(BranchID);
//
// set CheckBoxListBox Data by db.Course
//
var db = new LINQDataContext();
IEnumerable <string> NameQuery = from strName in db.Courses
where strName.Branch_ID == BranchID
select strName.Name_Course;
scForm.chblstSelectCourse.Items.AddRange(NameQuery.ToArray());
//
// set a Reference for save output data
//
scForm.LstSelectedCourse = (e.ColumnIndex == 3) ?
(DataGridViewTextBoxCell)dgvCourse[2, e.RowIndex] : (DataGridViewTextBoxCell)dgvCourse[4, e.RowIndex];
//
// set SelectCourseForm Location by this locate
//
scForm.Location = (e.ColumnIndex == 3) ?
new Point(this.Location.X + 30 + colID.Width + colTermNo.Width + colInReqCourseID.Width, this.Location.Y + 75) :
new Point(this.Location.X + 30 + colID.Width + colTermNo.Width + colInReqCourseID.Width +
colBtnInReqCourseID.Width + colPreReqCourseID.Width, this.Location.Y + 75);
//
// set Checked Items
//
if (scForm.LstSelectedCourse.Value != null)
{
string strID = "";
foreach (char chID in scForm.LstSelectedCourse.Value.ToString().ToCharArray())
{
if (char.IsDigit(chID)) // find ID in string value
{
strID += chID.ToString();
}
else if (strID != "") // search and save in checked list
{
scForm.chblstSelectCourse.SetItemChecked(
scForm.chblstSelectCourse.Items.IndexOf(
db.CourseSearchID(int.Parse(strID)).ToArray()[0].Name_Course), true);
strID = "";
}
// else continue;
}
}
//
// load form
//
scForm.ShowDialog();
}
}
public static IQueryable <OBJECT> SearchObjects(string nr, string name, string type)
{
LINQDataContext db = new LINQDataContext();
var result = from adm in db.OBJECT
where adm.nr_object.StartsWith(nr) &&
adm.name.StartsWith(name) &&
adm.obj_type.StartsWith(type)
select adm;
return(result);
}
// PANEL LOGOWANIA \/\/\/
public static IQueryable <PERSONEL> CheckLog(string login, string password)
{
LINQDataContext db = new LINQDataContext();
var result = from log in db.PERSONEL
where log.login == login &&
log.password == password &&
log.active == "T"
select log;
return(result);
}
private void CourseForm_FormClosing(object sender, FormClosingEventArgs e)
{
dgvCourse[0, 0].Selected = true;
// ----------------------------------------------------------------------------------------------
//
var db = new LINQDataContext();
for (int rowCounter = 0; rowCounter < dgvCourse.RowCount - 1; rowCounter++)
{
//
// search dgvClass.rows in db.Class
//
int ID_No = 0;
int.TryParse(dgvCourse[0, rowCounter].Value.ToString(), out ID_No);
// Define the query expression.
IEnumerable <int> query =
from course in db.Courses
where course.Course_ID == ID_No
select course.Course_ID;
//
try
{
if (query.ToArray().Length > 0) // EDIT
{
int intBuffer = 0;
decimal decBuffer = 0;
db.CourseEdit(ID_No, BranchID, (dgvCourse[1, rowCounter].Value != null) ? int.Parse(dgvCourse[1, rowCounter].Value.ToString()) : 0,
(dgvCourse[10, rowCounter].Value != null) ? (string)dgvCourse[10, rowCounter].Value.ToString() : "",
(dgvCourse[9, rowCounter].Value != null) ? (string)dgvCourse[9, rowCounter].Value.ToString() : "",
(dgvCourse[11, rowCounter].Value != null) ? decimal.TryParse(dgvCourse[11, rowCounter].Value.ToString(), out decBuffer) ? decBuffer : 0 : 0,
(dgvCourse[6, rowCounter].Value != null) ? (int.TryParse(dgvCourse[6, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[7, rowCounter].Value != null) ? (int.TryParse(dgvCourse[7, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[2, rowCounter].Value != null) ? (string)dgvCourse[2, rowCounter].Value.ToString() : "",
(dgvCourse[4, rowCounter].Value != null) ? (string)dgvCourse[4, rowCounter].Value.ToString() : "");
}
else // save
{
int intBuffer = 0;
decimal decBuffer = 0;
db.CourseSave(ID_No, BranchID, (dgvCourse[1, rowCounter].Value != null) ? int.Parse(dgvCourse[1, rowCounter].Value.ToString()) : 0,
(dgvCourse[10, rowCounter].Value != null) ? (string)dgvCourse[10, rowCounter].Value.ToString() : "",
(dgvCourse[9, rowCounter].Value != null) ? (string)dgvCourse[9, rowCounter].Value.ToString() : "",
(dgvCourse[11, rowCounter].Value != null) ? decimal.TryParse(dgvCourse[11, rowCounter].Value.ToString(), out decBuffer) ? decBuffer : 0 : 0,
(dgvCourse[6, rowCounter].Value != null) ? (int.TryParse(dgvCourse[6, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[7, rowCounter].Value != null) ? (int.TryParse(dgvCourse[7, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[2, rowCounter].Value != null) ? (string)dgvCourse[2, rowCounter].Value.ToString() : "",
(dgvCourse[4, rowCounter].Value != null) ? (string)dgvCourse[4, rowCounter].Value.ToString() : "");
}
}
catch { }
}
db.Dispose();
}
private void dgvType_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
var db = new LINQDataContext();
if (dgvType[e.ColumnIndex, e.RowIndex].Value != null)
{
db.Room_TypeSave(dgvType[e.ColumnIndex, e.RowIndex].Value.ToString());
refreshItems();
dgvRoom.Refresh();
}
}
public static void SetWorkerActive(string id)
{
Int32 _id = Int32.Parse(id);
LINQDataContext db = new LINQDataContext();
var result = (from e in db.PERSONEL
where e.id_personel.Equals(_id)
select e).First();
result.active = "T";
db.SubmitChanges();
}
private void CourseForm_FormClosing(object sender, FormClosingEventArgs e)
{
dgvCourse[0, 0].Selected = true;
// ----------------------------------------------------------------------------------------------
//
var db = new LINQDataContext();
for (int rowCounter = 0; rowCounter < dgvCourse.RowCount - 1; rowCounter++)
{
//
// search dgvClass.rows in db.Class
//
int ID_No = 0;
int.TryParse(dgvCourse[0, rowCounter].Value.ToString(), out ID_No);
// Define the query expression.
IEnumerable<int> query =
from course in db.Courses
where course.Course_ID == ID_No
select course.Course_ID;
//
try
{
if (query.ToArray().Length > 0) // EDIT
{
int intBuffer = 0;
decimal decBuffer = 0;
db.CourseEdit(ID_No, BranchID, (dgvCourse[1, rowCounter].Value != null) ? int.Parse(dgvCourse[1, rowCounter].Value.ToString()) : 0,
(dgvCourse[10, rowCounter].Value != null) ? (string)dgvCourse[10, rowCounter].Value.ToString() : "",
(dgvCourse[9, rowCounter].Value != null) ? (string)dgvCourse[9, rowCounter].Value.ToString() : "",
(dgvCourse[11, rowCounter].Value != null) ? decimal.TryParse(dgvCourse[11, rowCounter].Value.ToString(), out decBuffer) ? decBuffer : 0 : 0,
(dgvCourse[6, rowCounter].Value != null) ? (int.TryParse(dgvCourse[6, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[7, rowCounter].Value != null) ? (int.TryParse(dgvCourse[7, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[2, rowCounter].Value != null) ? (string)dgvCourse[2, rowCounter].Value.ToString() : "",
(dgvCourse[4, rowCounter].Value != null) ? (string)dgvCourse[4, rowCounter].Value.ToString() : "");
}
else // save
{
int intBuffer = 0;
decimal decBuffer = 0;
db.CourseSave(ID_No, BranchID, (dgvCourse[1, rowCounter].Value != null) ? int.Parse(dgvCourse[1, rowCounter].Value.ToString()) : 0,
(dgvCourse[10, rowCounter].Value != null) ? (string)dgvCourse[10, rowCounter].Value.ToString() : "",
(dgvCourse[9, rowCounter].Value != null) ? (string)dgvCourse[9, rowCounter].Value.ToString() : "",
(dgvCourse[11, rowCounter].Value != null) ? decimal.TryParse(dgvCourse[11, rowCounter].Value.ToString(), out decBuffer) ? decBuffer : 0 : 0,
(dgvCourse[6, rowCounter].Value != null) ? (int.TryParse(dgvCourse[6, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[7, rowCounter].Value != null) ? (int.TryParse(dgvCourse[7, rowCounter].Value.ToString(), out intBuffer)) ? intBuffer : 0 : 0,
(dgvCourse[2, rowCounter].Value != null) ? (string)dgvCourse[2, rowCounter].Value.ToString() : "",
(dgvCourse[4, rowCounter].Value != null) ? (string)dgvCourse[4, rowCounter].Value.ToString() : "");
}
}
catch { }
}
db.Dispose();
}
private void TermSchemeForm_Load(object sender, EventArgs e)
{
var db = new LINQDataContext();
dgvTermScheme.DataSource = db.Branches;
System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle3 = new System.Windows.Forms.DataGridViewCellStyle();
System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle4 = new System.Windows.Forms.DataGridViewCellStyle();
//
// colBtnTermScheme
//
this.colBtnTermScheme = new System.Windows.Forms.DataGridViewButtonColumn();
dataGridViewCellStyle3.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;
dataGridViewCellStyle3.NullValue = "Term Scheme";
dataGridViewCellStyle3.Padding = new System.Windows.Forms.Padding(3);
this.colBtnTermScheme.DefaultCellStyle = dataGridViewCellStyle3;
this.colBtnTermScheme.Frozen = false;
this.colBtnTermScheme.HeaderText = "Term Scheme";
this.colBtnTermScheme.Name = "colBtnTermScheme";
this.colBtnTermScheme.Text = "Term Scheme";
this.dgvTermScheme.Columns.Add(this.colBtnTermScheme);
//
// other col setup
//
dataGridViewCellStyle4.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;
dgvTermScheme.Columns[0].DefaultCellStyle = dataGridViewCellStyle4;
dgvTermScheme.Columns[0].Width = 50;
dgvTermScheme.Columns[0].HeaderText = "ID";
dgvTermScheme.Columns[0].ReadOnly = true;
dgvTermScheme.Columns[0].Frozen = true;
dgvTermScheme.Columns[1].DefaultCellStyle = dataGridViewCellStyle4;
dgvTermScheme.Columns[1].Width = 150;
dgvTermScheme.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvTermScheme.Columns[1].ReadOnly = true;
dgvTermScheme.Columns[1].HeaderText = "Education Course Name";
dgvTermScheme.Columns[2].DefaultCellStyle = dataGridViewCellStyle4;
dgvTermScheme.Columns[2].Width = 200;
dgvTermScheme.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvTermScheme.Columns[2].ReadOnly = true;
dgvTermScheme.Columns[2].HeaderText = "Education Degree";
}
private void BranchForm_Load(object sender, EventArgs e)
{
dgvBranch.Rows.Clear();
var db = new LINQDataContext();
var aryBranch = db.Branches.ToArray();
if (aryBranch.Length > 0)
{
dgvBranch.Rows.Add(aryBranch.Length);
for (int rowCounter = 0; rowCounter < aryBranch.Length; rowCounter++)
{
dgvBranch[0, rowCounter].Value = aryBranch[rowCounter].ID_Branch.ToString();
if (aryBranch[rowCounter].Branch_Name != null)
dgvBranch[1, rowCounter].Value = aryBranch[rowCounter].Branch_Name.ToString();
dgvBranch[2, rowCounter].Value = (aryBranch[rowCounter].Degree != null) ?
aryBranch[rowCounter].Degree.ToString() : "";
}
}
}
private void BranchForm_FormClosing(object sender, FormClosingEventArgs e)
{
dgvBranch[0, 0].Selected = true;
// ----------------------------------------------------------------------------------------------
//
var db = new LINQDataContext();
for (int rowCounter = 0; rowCounter < dgvBranch.RowCount - 1; rowCounter++)
{
try
{
//
// search dgvBranch.rows in db.Branch
//
int ID_No = 0;
int.TryParse(dgvBranch[0, rowCounter].Value.ToString(), out ID_No);
// Define the query expression.
var query = (from branch in db.Branches
where branch.ID_Branch == ID_No
select branch).SingleOrDefault();
if (query != null) // EDIT
{
db.BranchEdit(ID_No,
(dgvBranch[1, rowCounter].Value != null) ? (string)dgvBranch[1, rowCounter].Value.ToString() : "",
(dgvBranch[2, rowCounter].Value != null) ? (string)dgvBranch[2, rowCounter].Value.ToString() : "");
}
else // save
{
db.BranchSave(ID_No,
(dgvBranch[1, rowCounter].Value != null) ? (string)dgvBranch[1, rowCounter].Value.ToString() : "",
(dgvBranch[2, rowCounter].Value != null) ? (string)dgvBranch[2, rowCounter].Value.ToString() : "");
}
}
catch { }
}
db.Dispose();
}
private void dgvGroup_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
int id = 0;
if (int.TryParse(e.Row.Cells[0].Value.ToString(), out id))
{
var db = new LINQDataContext();
// Define the query expression.
var cquery = (from classs in db.Group_ID_Lists
where classs.Group_ID == id
select classs).ToArray();
if (cquery != null)
{
string query = @"DELETE From dbo.Group_ID_List Where Group_ID = {0}";
db.ExecuteQuery<object>(query, id);
}
else
{
db.New_GroupsPerClassDeleteByGroupID(id);
db.GroupsDelete(id);
}
db.Dispose();
}
}
private void RoomForm_Load(object sender, EventArgs e)
{
dgvRoom.Rows[0].Height = 30;
dgvType.Rows[0].Height = 30;
var db = new LINQDataContext();
IEnumerable<string> TypeQuery = from tQ in db.Room_Types
select tQ.Type_Name;
var aryRoom_Type = TypeQuery.ToArray();
if (aryRoom_Type.Length > 0)
{
dgvType.Rows.Add(aryRoom_Type.Length);
for (int rowCounter = 0; rowCounter < aryRoom_Type.Length; rowCounter++)
{
dgvType[0, rowCounter].Value = aryRoom_Type[rowCounter];
}
}
refreshItems();
var aryRooms = db.Rooms.ToArray();
if (aryRooms.Length > 0)
{
dgvRoom.Rows.Add(aryRooms.Length);
for (int rowCounter = 0; rowCounter < aryRooms.Length; rowCounter++)
{
dgvRoom[0, rowCounter].Value = aryRooms[rowCounter].Room_ID.ToString();
if (aryRooms[rowCounter].Name_Room != null)
dgvRoom[1, rowCounter].Value = aryRooms[rowCounter].Name_Room.ToString();
dgvRoom[2, rowCounter].Value = aryRooms[rowCounter].Type_Room.ToString();
dgvRoom[3, rowCounter].Value = aryRooms[rowCounter].Size_No.ToString();
}
}
}
//--------------------------------------------------------------------------------
private void pbtnProfessorExport_Click(object sender, EventArgs e)
{
SaveFileDialog save2Excel = new SaveFileDialog();
save2Excel.Filter = @"Excel files|*.xls";
save2Excel.DefaultExt = "ProfessorData.xls";
save2Excel.FileName = "ProfessorData.xls";
if (save2Excel.ShowDialog() == DialogResult.OK)
{
// 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 load from OLEDB db.Professor.mdf database file's
var aryDBProfessor = db.Professors.ToArray();
ExcelFile ef = new ExcelFile();
ef.LimitReached += new LimitEventHandler(ef_LimitReached);
ef.LimitNear += new LimitEventHandler(ef_LimitNear);
ExcelWorksheet ws = ef.Worksheets.Add("Professor");
int NumberSheet = 1;
//
// column header text
ws.Cells[0, 0].Value = "ID";
ws.Cells[0, 1].Value = "Professor Name";
ws.Cells[0, 2].Value = "Branch";
ws.Cells[0, 3].Value = "Email";
ws.Cells[0, 4].Value = "Education Degree";
ws.Cells[0, 5].Value = "SCHEDULE";
//
// column width
ws.Columns[0].Width = 5 * 256;
ws.Columns[1].Width = 30 * 256;
ws.Columns[2].Width = 20 * 256;
ws.Columns[3].Width = 40 * 256;
ws.Columns[4].Width = 20 * 256;
ws.Columns[5].Width = 200 * 256;
//
// Header Hight
ws.Rows[0].Height = 2 * 256;
//
// row data style's
CellStyle tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.LightYellow);
tmpStyle.Font.Weight = ExcelFont.BoldWeight;
tmpStyle.Font.Color = Color.Black;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
if (aryDBProfessor.Length >= 150)
ws.Cells.GetSubrangeAbsolute(1, 0, 149, 5).Style = tmpStyle;
else
ws.Cells.GetSubrangeAbsolute(1, 0, aryDBProfessor.Length, 5).Style = tmpStyle;
//
// Header style's
CellStyle HeaderStyle = new CellStyle();
HeaderStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
HeaderStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
HeaderStyle.FillPattern.SetSolid(Color.Chocolate);
HeaderStyle.Font.Weight = ExcelFont.MaxWeight;
HeaderStyle.Font.Color = Color.White;
HeaderStyle.WrapText = true;
HeaderStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Medium);
ws.Cells.GetSubrangeAbsolute(0, 0, 0, 5).Style = HeaderStyle;
//
// row data
int RowCount = 0;
for (int rowC = 0; rowC < aryDBProfessor.Length; rowC++, RowCount++)
{
if (RowCount >= 149) // Go Next Sheet
{
RowCount = 0;
NumberSheet++;
ws = ef.Worksheets.Add("Professor(" + NumberSheet.ToString() + ")");
//
// column header text
ws.Cells[0, 0].Value = "ID";
ws.Cells[0, 1].Value = "Professor Name";
ws.Cells[0, 2].Value = "Branch";
ws.Cells[0, 3].Value = "Email";
ws.Cells[0, 4].Value = "Education Degree";
ws.Cells[0, 5].Value = "SCHEDULE";
//
// column width
ws.Columns[0].Width = 5 * 256;
ws.Columns[1].Width = 30 * 256;
ws.Columns[2].Width = 20 * 256;
ws.Columns[3].Width = 40 * 256;
ws.Columns[4].Width = 20 * 256;
ws.Columns[5].Width = 200 * 256;
//
// Header Hight
ws.Rows[0].Height = 2 * 256;
//
// row data style's
if (aryDBProfessor.Length - rowC >= 150)
ws.Cells.GetSubrangeAbsolute(1, 0, 149, 5).Style = tmpStyle;
else
ws.Cells.GetSubrangeAbsolute(1, 0, aryDBProfessor.Length - rowC, 5).Style = tmpStyle;
//
// Header style's
ws.Cells.GetSubrangeAbsolute(0, 0, 0, 5).Style = HeaderStyle;
}
ws.Cells[RowCount + 1, 0].Value = aryDBProfessor[rowC].ID.ToString();
ws.Cells[RowCount + 1, 1].Value = (aryDBProfessor[rowC].Name_Professor != null) ?
aryDBProfessor[rowC].Name_Professor.ToString() : "";
ws.Cells[RowCount + 1, 2].Value = (aryDBProfessor[rowC].Branch != null) ?
aryDBProfessor[rowC].Branch.ToString() : "";
ws.Cells[RowCount + 1, 3].Value = (aryDBProfessor[rowC].Email != null) ?
aryDBProfessor[rowC].Email.ToString() : "";
ws.Cells[RowCount + 1, 4].Value = (aryDBProfessor[rowC].EducationDegree != null) ?
aryDBProfessor[rowC].EducationDegree.ToString() : "";
ws.Cells[RowCount + 1, 5].Value = (aryDBProfessor[rowC].Schedule != null) ?
aryDBProfessor[rowC].Schedule.ToString() : "";
ws.Cells[RowCount + 1, 5].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
}
//
// save professor data in save2Excel.FileName path's
//
ef.SaveXls(save2Excel.FileName);
//
// Try to open created excel file's
//
try
{
System.Diagnostics.Process.Start(save2Excel.FileName);
}
catch
{ }
}
}
private void RoomForm_FormClosing(object sender, FormClosingEventArgs e)
{
dgvRoom[0, 0].Selected = true;
// ----------------------------------------------------------------------------------------------
//
var db = new LINQDataContext();
for (int rowCounter = 0; rowCounter < dgvRoom.RowCount - 1; rowCounter++)
{
try
{
//
// search dgvRoom.rows in db.Room
//
int ID_No = 0;
int.TryParse(dgvRoom[0, rowCounter].Value.ToString(), out ID_No);
// Define the query expression.
IEnumerable<int> query =
from room in db.Rooms
where room.Room_ID == ID_No
select room.Room_ID;
if (query.ToArray().Length > 0) // EDIT
{
int size = 0;
db.RoomEdit(ID_No,
(dgvRoom[1, rowCounter].Value != null) ? (string)dgvRoom[1, rowCounter].Value.ToString() : "",
(dgvRoom[2, rowCounter].Value != null) ? dgvRoom[2, rowCounter].Value.ToString() : "",
(dgvRoom[3, rowCounter].Value != null) ? (int.TryParse(dgvRoom[3, rowCounter].Value.ToString(), out size)) ? size : 0 : 0);
}
else
{
int size = 0;
db.RoomSave(ID_No,
(dgvRoom[1, rowCounter].Value != null) ? (string)dgvRoom[1, rowCounter].Value.ToString() : "",
(dgvRoom[2, rowCounter].Value != null) ? dgvRoom[2, rowCounter].Value.ToString() : "",
(dgvRoom[3, rowCounter].Value != null) ? (int.TryParse(dgvRoom[3, rowCounter].Value.ToString(), out size)) ? size : 0 : 0);
}
}
catch { }
}
db.Dispose();
}
private void pbtnBranchImport_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.Branches.mdf database file's
ExcelFile ef = new ExcelFile();
OpenFileDialog openFromExcel = new OpenFileDialog();
openFromExcel.Filter = @"Excel files|*.xls";
if (openFromExcel.ShowDialog() == DialogResult.OK)
{
ExcelWorksheet sheet;
try
{
ef.LoadXls(openFromExcel.FileName);
sheet = ef.Worksheets["Branch"];
}
catch (Exception ex) { MessageBox.Show(ex.Message); return; }
bool FirstRow = true;
foreach (ExcelRow row in sheet.Rows)
{
if (!FirstRow)
{
try
{
db.BranchSave(Convert.ToInt32(row.Cells[0].Value.ToString()),
(row.Cells[1].Value != null) ? row.Cells[1].Value.ToString() : "",
(row.Cells[2].Value != null) ? row.Cells[2].Value.ToString() : "");
}
catch (Exception ex) { MessageBox.Show(ex.Message); return; }
}
else FirstRow = false;
}
}
}
private void saveAndCompile_Groups(string strGroups, int classId)
{
var db = new LINQDataContext();
//
// compile strGroups for Parsing group id
//
char[] spell = strGroups.ToCharArray();
string strBuffer = "";
for (int indexC = 0; indexC < spell.Length; indexC++)
{
if (char.IsWhiteSpace(spell[indexC]))
{
if (strBuffer != "")
{
int intBuffer = int.Parse(strBuffer);
db.Group_ID_ListSave(intBuffer, classId);
strBuffer = "";
}
}
else if (char.IsDigit(spell[indexC]))
{
strBuffer += spell[indexC].ToString();
}
else
{
//error
if (strBuffer != "")
{
int intBuffer = int.Parse(strBuffer);
db.Group_ID_ListSave(intBuffer, classId);
strBuffer = "";
}
}
}
}
private void pbtnBranchExport_Click(object sender, EventArgs e)
{
SaveFileDialog save2Excel = new SaveFileDialog();
save2Excel.Filter = @"Excel files|*.xls";
save2Excel.DefaultExt = "BranchData.xls";
save2Excel.FileName = "BranchData.xls";
if (save2Excel.ShowDialog() == DialogResult.OK)
{
// 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 load from OLEDB db.Branches.mdf database file's
var aryDBBranch = db.Branches.ToArray();
ExcelFile ef = new ExcelFile();
ef.LimitReached += new LimitEventHandler(ef_LimitReached);
ef.LimitNear += new LimitEventHandler(ef_LimitNear);
ExcelWorksheet ws = ef.Worksheets.Add("Branch");
//
// column header text
ws.Cells[0, 0].Value = "ID";
ws.Cells[0, 1].Value = "Education Course Name";
ws.Cells[0, 2].Value = "Education Degree";
//
// column width
ws.Columns[0].Width = 8 * 256;
ws.Columns[1].Width = 50 * 256;
ws.Columns[2].Width = 50 * 256;
//
// Header Hight
ws.Rows[0].Height = 2 * 256;
//
// row data style's
CellStyle tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.LightYellow);
tmpStyle.Font.Weight = ExcelFont.BoldWeight;
tmpStyle.Font.Color = Color.Black;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
ws.Cells.GetSubrangeAbsolute(1, 0, aryDBBranch.Length, 2).Style = tmpStyle;
//
// row data
for (int rowC = 0; rowC < aryDBBranch.Length; rowC++)
{
ws.Cells[rowC + 1, 0].Value = aryDBBranch[rowC].ID_Branch.ToString();
ws.Cells[rowC + 1, 1].Value = aryDBBranch[rowC].Branch_Name.ToString();
ws.Cells[rowC + 1, 2].Value = aryDBBranch[rowC].Degree.ToString();
}
//
// Header style's
tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.Chocolate);
tmpStyle.Font.Weight = ExcelFont.MaxWeight;
tmpStyle.Font.Color = Color.White;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Medium);
ws.Cells.GetSubrangeAbsolute(0, 0, 0, 2).Style = tmpStyle;
//
// save professor data in save2Excel.FileName path's
//
ef.SaveXls(save2Excel.FileName);
//
// Try to open created excel file's
//
try
{
System.Diagnostics.Process.Start(save2Excel.FileName);
}
catch
{ }
}
}
// Parse file and store parsed object
public void ParseFile(LINQDataContext db)
{
// clear previously parsed objects
_professors.Clear();
_studentGroups.Clear();
_courses.Clear();
_rooms.Clear();
_courseClasses.Clear();
Room.RestartIDs();
//
// Save Professor Data
//
foreach (var any in db.Professors)
{
ProfessorInfoCompiler pIc = new ProfessorInfoCompiler();
Professor p;
if (pIc.StartScanner(any.Schedule))
{
p = new Professor(any.ID, any.Name_Professor, pIc.CompiledData);
_professors.Add(p.GetId, p);
}
}
//
// Save StudentsGroup Data
//
foreach (var any in db.Groups)
{
StudentsGroup sg;
string sg_name = string.Format(CultureInfo.CurrentCulture, "{0} {1} {2}-{3}", any.Branch.Degree,
any.Branch.Branch_Name, any.Semester_Entry_Year, (any.Semester_Entry_FS) ? "1" : "2");
sg = new StudentsGroup(any.ID, sg_name, any.Size_No);
_studentGroups.Add(sg.GetId, sg);
}
//
// Save Course Data
//
foreach (var any in db.Courses)
{
Course c;
c = new Course(any.Course_ID, any.Name_Course);
_courses.Add(c.GetId, c);
}
//
// Save Room Data
//
foreach (var any in db.Rooms)
{
Room r;
r = new Room(any.Room_ID, any.Name_Room, any.Type_Room, any.Size_No);
_rooms.Add(r.GetId, r);
}
//
// Save CourseClass Data -----------------------------------------------------------------------------
//
foreach (var any in db.Classes)
{
//
// set Professor by best priority
//
var prof = (from p1 in db.Priority_Professors
join p2 in db.Professors on p1.Professor_ID equals p2.ID
where (p1.Class_ID == any.Class_ID)
orderby p1.Priority
select new
{
p1.Professor_ID,
p2.Name_Professor,
p2.Schedule
}).ToArray()[0];
ProfessorInfoCompiler pIc = new ProfessorInfoCompiler();
Professor p = (pIc.StartScanner(prof.Schedule)) ?
new Professor(prof.Professor_ID, prof.Name_Professor, pIc.CompiledData) :
new Professor(prof.Professor_ID, prof.Name_Professor, pIc.CompiledData);
//
// set selected course for class
//
Course c = new Course(any.Course_ID, any.Course.Name_Course);
//
// set StudentsGroup in List
//
List<StudentsGroup> g = new List<StudentsGroup>();
foreach (var lstGroup in (from gil in db.Group_ID_Lists
join groups in db.Groups on gil.Group_ID equals groups.ID
where gil.Class_ID == any.Class_ID
select new
{
gil.Group_ID,
groups.Size_No,
sg_name = string.Format(CultureInfo.CurrentCulture, "{0} {1} {2}-{3}",
groups.Branch.Degree,
groups.Branch.Branch_Name,
groups.Semester_Entry_Year,
(groups.Semester_Entry_FS) ? "1" : "2")
}))
{
StudentsGroup sg = new StudentsGroup(lstGroup.Group_ID, lstGroup.sg_name, lstGroup.Size_No);
g.Add(sg);
}
//
// save class by created data
//
CourseClass cc = new CourseClass(p, c, g, any.RoomType, (any.Practical_unit + any.Theory_unit), any.Class_ID);
_courseClasses.Add(cc);
}
//----------------------------------------------------------------------------------------------------------------
//
db.Dispose();
_isEmpty = false;
}
private void ClassForm_Load(object sender, EventArgs e)
{
dgvClass.RowsAdded -= new DataGridViewRowsAddedEventHandler(dgvClass_RowsAdded);
PersianCalendar pc = new PersianCalendar();
txtCurrentTerm.Text = pc.GetYear(DateTime.Today).ToString();
cmbSemesterEntry.SelectedIndex = (pc.GetMonth(DateTime.Today) > 7) ? 1 : 0;
//
dgvClass.Rows.Clear();
var db = new LINQDataContext();
this.colCourse.Items.AddRange((from QC in db.Courses select QC.Name_Course).ToArray());
this.ColType.Items.AddRange((from QR in db.Room_Types select QR.Type_Name).ToArray());
var qBName = (from QG in db.Groups
select new
{
GroupName =
(QG.ID.ToString() + ". " +
QG.Branch.Branch_Name + " - " +
QG.Branch.Degree + " [" +
QG.Semester_Entry_Year.ToString() + " " +
((QG.Semester_Entry_FS == true) ? "First" : "Second") + " Entry]")
}).ToArray();
foreach (var anyBN in qBName)
this.cmbGroups.Items.Add(anyBN.GroupName);
//
// read class data in db.Classes
//
var dbClass = (from c in db.Classes
orderby c.Class_ID
select new
{
c.Class_ID,
c.Course.Name_Course,
c.Practical_unit,
c.Theory_unit,
c.Course.CourseCode.Value,
c.RoomType
}).ToArray();
foreach (var row in dbClass)
{
//
// Find Group_ID_List
//
string strGroup_ID_List = "";
foreach (int any in (from GIL in db.Group_ID_Lists
where GIL.Class_ID == row.Class_ID
select GIL.Group_ID).ToArray())
{
strGroup_ID_List += any.ToString() + " ";
}
//
// Find Professor_ID_List And it's Priority
//
var dbPriority = from p in db.Priority_Professors
where p.Class_ID == row.Class_ID
select p;
string strProfessor_Priority = "";
foreach (var anyPriority in dbPriority.ToArray())
{
strProfessor_Priority +=
anyPriority.Professor_ID.ToString() + "("
+ anyPriority.Priority.ToString() + ") ";
}
dgvClass.Rows.Add(new object[]
{
row.Class_ID,
"+ Professor",
strProfessor_Priority,
row.Name_Course,
row.Value,
row.Practical_unit,
row.Theory_unit,
row.RoomType,
"+ Group",
strGroup_ID_List
});
}
dgvClass.RowsAdded += new DataGridViewRowsAddedEventHandler(dgvClass_RowsAdded);
}
private void CourseForm_Load(object sender, EventArgs e)
{
this.Text = BranchName + " - " + BranchDegree + " Course Data";
dgvCourse.Rows.Clear();
var db = new LINQDataContext();
//
// SQL Query for Course list by Branch ID
//
var qCourse = (from lstCourse in db.Courses
where lstCourse.Branch_ID == BranchID
select new
{
lstCourse.Course_ID,
lstCourse.TermNo,
lstCourse.Name_Course,
lstCourse.Type_Course,
lstCourse.CourseCode,
lstCourse.TheoryUnitNo,
lstCourse.PracticalUnitNo,
lstCourse.InRequisite_CourseID,
lstCourse.PreRequisite_CourseID
});
//
var aryQCourse = qCourse.ToArray();
if (aryQCourse.Length > 0)
{
dgvCourse.Rows.Add(aryQCourse.Length);
for (int rowCounter = 0; rowCounter < aryQCourse.Length; rowCounter++)
{
dgvCourse[0, rowCounter].Value = aryQCourse[rowCounter].Course_ID;
dgvCourse[1, rowCounter].Value = aryQCourse[rowCounter].TermNo.Value;
dgvCourse[2, rowCounter].Value = aryQCourse[rowCounter].InRequisite_CourseID;
dgvCourse[4, rowCounter].Value = aryQCourse[rowCounter].PreRequisite_CourseID;
dgvCourse[6, rowCounter].Value = aryQCourse[rowCounter].TheoryUnitNo.Value;
dgvCourse[7, rowCounter].Value = aryQCourse[rowCounter].PracticalUnitNo.Value;
dgvCourse[8, rowCounter].Value = int.Parse(dgvCourse[6, rowCounter].Value.ToString()) + int.Parse(dgvCourse[7, rowCounter].Value.ToString());
dgvCourse[9, rowCounter].Value = aryQCourse[rowCounter].Type_Course;
dgvCourse[10, rowCounter].Value = (aryQCourse[rowCounter].Name_Course != null) ? aryQCourse[rowCounter].Name_Course : "";
dgvCourse[11, rowCounter].Value = aryQCourse[rowCounter].CourseCode.Value;
}
}
}
private void dgvCourse_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
int id = 0;
if (int.TryParse(e.Row.Cells[0].Value.ToString(), out id))
{
var db = new LINQDataContext();
// Define the query expression.
IEnumerable<int> query =
from classs in db.Classes
where classs.Course_ID == id
select classs.Class_ID;
//
// if exist data in class table :
//
if (query.ToArray().Length > 0)
{
string class_ID_List = "";
foreach (var anyClass in query)
{
class_ID_List += anyClass.ToString() + " and ";
}
class_ID_List = class_ID_List.Substring(0, class_ID_List.Length - 4);
e.Cancel = true;
MessageBox.Show("Information classes with class numbers " +
class_ID_List + "is related to Course " +
((e.Row.Cells[10].Value != null) ? e.Row.Cells[10].Value.ToString() : "NULL") +
".\n First, this information can change classes.", "Delete Row Error",
MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
}
else
{
db.CourseDelete(id);
}
db.Dispose();
}
}
private void dgvBranch_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
int id = 0;
if (int.TryParse(e.Row.Cells[0].Value.ToString(), out id))
{
var db = new LINQDataContext();
// Define the query expression.
IEnumerable<int> qGroupID =
from groups in db.Groups
where groups.Branch_Selection == id
select groups.ID;
//
// Define the query expression.
IEnumerable<int> qCourseID =
from course in db.Courses
where course.Branch_ID == id
select course.Course_ID;
//
// if exist data in the Groups table :
//
if (qGroupID.ToArray().Length > 0)
{
string groups_ID_List = "";
foreach (var anyGroup in qGroupID)
{
groups_ID_List += anyGroup.ToString() + " and ";
}
groups_ID_List = groups_ID_List.Substring(0, groups_ID_List.Length - 4);
e.Cancel = true;
MessageBox.Show("Information Groups with group numbers " +
groups_ID_List + "is related to Branch (" +
((e.Row.Cells[1].Value != null) ? e.Row.Cells[1].Value.ToString() : "NULL") +
").\n First, this information can change groups.", "Delete Row Error",
MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
}
//
// if exist data in the Groups table :
//
else if (qCourseID.ToArray().Length > 0)
{
string course_ID_List = "";
foreach (var anyCourse in qCourseID)
{
course_ID_List += anyCourse.ToString() + " and ";
}
course_ID_List = course_ID_List.Substring(0, course_ID_List.Length - 4);
e.Cancel = true;
MessageBox.Show("Information courses with course numbers " +
course_ID_List + "is related to Branch (" +
((e.Row.Cells[1].Value != null) ? e.Row.Cells[1].Value.ToString() : "NULL") +
").\n First, this information can change courses.", "Delete Row Error",
MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1);
}
else
{
db.BranchDelete(id);
}
db.Dispose();
}
}
private void pbtnCourseExport_Click(object sender, EventArgs e)
{
SaveFileDialog save2Excel = new SaveFileDialog();
save2Excel.Filter = @"Excel files|*.xls";
save2Excel.DefaultExt = "CourseData.xls";
save2Excel.FileName = "CourseData.xls";
if (save2Excel.ShowDialog() == DialogResult.OK)
{
// 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 load from OLEDB db.Course.mdf database file's
var aryDBCourse = db.Courses.ToArray();
ExcelFile ef = new ExcelFile();
ef.LimitReached += new LimitEventHandler(ef_LimitReached);
ef.LimitNear += new LimitEventHandler(ef_LimitNear);
ExcelWorksheet ws = ef.Worksheets.Add("Course");
int NumberSheet = 1;
//
// column header text
ws.Cells[0, 0].Value = "Branch ID";
ws.Cells[0, 1].Value = "ID";
ws.Cells[0, 2].Value = "Term No";
ws.Cells[0, 3].Value = "InRequisite Course ID";
ws.Cells[0, 4].Value = "InRequisite Course Name";
ws.Cells[0, 5].Value = "PreRequisite Course ID";
ws.Cells[0, 6].Value = "PreRequisite Course Name";
ws.Cells[0, 7].Value = "Theory Unit No";
ws.Cells[0, 8].Value = "Pratial Unit No";
ws.Cells[0, 9].Value = "Course Type";
ws.Cells[0, 10].Value = "Course Name";
ws.Cells[0, 11].Value = "Course Code";
//
// column width
ws.Columns[0].Width = 8 * 256;
ws.Columns[1].Width = 8 * 256;
ws.Columns[2].Width = 8 * 256;
ws.Columns[3].Width = 25 * 256;
ws.Columns[4].Width = 50 * 256;
ws.Columns[5].Width = 25 * 256;
ws.Columns[6].Width = 50 * 256;
ws.Columns[7].Width = 20 * 256;
ws.Columns[8].Width = 20 * 256;
ws.Columns[9].Width = 15 * 256;
ws.Columns[10].Width = 30 * 256;
ws.Columns[11].Width = 20 * 256;
//
// Header Hight
ws.Rows[0].Height = 2 * 256;
//
// row data style's
CellStyle tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.LightYellow);
tmpStyle.Font.Weight = ExcelFont.BoldWeight;
tmpStyle.Font.Color = Color.Black;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
if (aryDBCourse.Length > 149)
ws.Cells.GetSubrangeAbsolute(1, 0, 149, 11).Style = tmpStyle;
else
ws.Cells.GetSubrangeAbsolute(1, 0, aryDBCourse.Length, 11).Style = tmpStyle;
//
// Header style's
CellStyle headerStyle = new CellStyle();
headerStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
headerStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
headerStyle.FillPattern.SetSolid(Color.Chocolate);
headerStyle.Font.Weight = ExcelFont.MaxWeight;
headerStyle.Font.Color = Color.White;
headerStyle.WrapText = true;
headerStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Medium);
ws.Cells.GetSubrangeAbsolute(0, 0, 0, 11).Style = headerStyle;
//
// row data
int RowCount = 0;
for (int rowC = 0; rowC < aryDBCourse.Length; rowC++, RowCount++)
{
if (RowCount >= 149)
{
RowCount = 0;
NumberSheet++;
ws = ef.Worksheets.Add("Course(" + NumberSheet.ToString() + ")");
//
// column header text
ws.Cells[0, 0].Value = "Branch ID";
ws.Cells[0, 1].Value = "ID";
ws.Cells[0, 2].Value = "Term No";
ws.Cells[0, 3].Value = "InRequisite Course ID";
ws.Cells[0, 4].Value = "InRequisite Course Name";
ws.Cells[0, 5].Value = "PreRequisite Course ID";
ws.Cells[0, 6].Value = "PreRequisite Course Name";
ws.Cells[0, 7].Value = "Theory Unit No";
ws.Cells[0, 8].Value = "Pratial Unit No";
ws.Cells[0, 9].Value = "Course Type";
ws.Cells[0, 10].Value = "Course Name";
ws.Cells[0, 11].Value = "Course Code";
//
// column width
ws.Columns[0].Width = 8 * 256;
ws.Columns[1].Width = 8 * 256;
ws.Columns[2].Width = 8 * 256;
ws.Columns[3].Width = 25 * 256;
ws.Columns[4].Width = 50 * 256;
ws.Columns[5].Width = 25 * 256;
ws.Columns[6].Width = 50 * 256;
ws.Columns[7].Width = 20 * 256;
ws.Columns[8].Width = 20 * 256;
ws.Columns[9].Width = 15 * 256;
ws.Columns[10].Width = 30 * 256;
ws.Columns[11].Width = 20 * 256;
//
// Header Hight
ws.Rows[0].Height = 2 * 256;
//
// row data style's
if (aryDBCourse.Length - rowC > 149)
ws.Cells.GetSubrangeAbsolute(1, 0, 149, 11).Style = tmpStyle;
else
ws.Cells.GetSubrangeAbsolute(1, 0, aryDBCourse.Length - rowC, 11).Style = tmpStyle;
//
// Header style's
ws.Cells.GetSubrangeAbsolute(0, 0, 0, 11).Style = headerStyle;
}
ws.Cells[RowCount + 1, 0].Value = aryDBCourse[rowC].Branch_ID;
ws.Cells[RowCount + 1, 1].Value = aryDBCourse[rowC].Course_ID;
ws.Cells[RowCount + 1, 2].Value = aryDBCourse[rowC].TermNo.Value;
ws.Cells[RowCount + 1, 3].Value = aryDBCourse[rowC].InRequisite_CourseID;
//
// Compile InReq Course iD to Name
//
string InRequisite_CourseName = "";
string buffer = "";
foreach (char reader in aryDBCourse[rowC].InRequisite_CourseID.ToCharArray())
{
if (char.IsDigit(reader))
buffer += reader.ToString();
else if (buffer != "")
{
InRequisite_CourseName += (from Qname in db.Courses
where Qname.Course_ID == int.Parse(buffer)
select Qname.Name_Course).ToArray()[0].ToString() + " & ";
buffer = "";
}
}
InRequisite_CourseName = (InRequisite_CourseName.Length > 3) ?
InRequisite_CourseName.Substring(0, InRequisite_CourseName.Length - 3) :
InRequisite_CourseName;
//
ws.Cells[RowCount + 1, 4].Value = InRequisite_CourseName;
ws.Cells[RowCount + 1, 5].Value = aryDBCourse[rowC].PreRequisite_CourseID;
//
// Compile PreReq Course iD to Name
//
string PreRequisite_CourseName = "";
buffer = "";
foreach (char reader in aryDBCourse[rowC].PreRequisite_CourseID.ToCharArray())
{
if (char.IsDigit(reader))
buffer += reader.ToString();
else if (buffer != "")
{
PreRequisite_CourseName += (from Qname in db.Courses
where Qname.Course_ID == int.Parse(buffer)
select Qname.Name_Course).ToArray()[0].ToString() + @" & ";
buffer = "";
}
}
PreRequisite_CourseName = (PreRequisite_CourseName.Length > 3) ?
PreRequisite_CourseName.Substring(0, PreRequisite_CourseName.Length - 3) :
PreRequisite_CourseName;
//
ws.Cells[RowCount + 1, 6].Value = PreRequisite_CourseName;
ws.Cells[RowCount + 1, 7].Value = aryDBCourse[rowC].TheoryUnitNo.Value;
ws.Cells[RowCount + 1, 8].Value = aryDBCourse[rowC].PracticalUnitNo.Value;
ws.Cells[RowCount + 1, 9].Value = aryDBCourse[rowC].Type_Course;
ws.Cells[RowCount + 1, 10].Value = aryDBCourse[rowC].Name_Course;
ws.Cells[RowCount + 1, 11].Value = aryDBCourse[rowC].CourseCode.Value;
}
//
// save professor data in save2Excel.FileName path's
//
ef.SaveXls(save2Excel.FileName);
//
// Try to open created excel file's
//
try
{
System.Diagnostics.Process.Start(save2Excel.FileName);
}
catch
{ }
}
}
private void dgvCourse_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if ((e.ColumnIndex == 3 || e.ColumnIndex == 5) && e.RowIndex >= 0 && e.RowIndex < dgvCourse.RowCount - 1)
{
SelectCourseForm scForm = new SelectCourseForm(BranchID);
//
// set CheckBoxListBox Data by db.Course
//
var db = new LINQDataContext();
IEnumerable<string> NameQuery = from strName in db.Courses
where strName.Branch_ID == BranchID
select strName.Name_Course;
scForm.chblstSelectCourse.Items.AddRange(NameQuery.ToArray());
//
// set a Reference for save output data
//
scForm.LstSelectedCourse = (e.ColumnIndex == 3) ?
(DataGridViewTextBoxCell)dgvCourse[2, e.RowIndex] : (DataGridViewTextBoxCell)dgvCourse[4, e.RowIndex];
//
// set SelectCourseForm Location by this locate
//
scForm.Location = (e.ColumnIndex == 3) ?
new Point(this.Location.X + 30 + colID.Width + colTermNo.Width + colInReqCourseID.Width, this.Location.Y + 75) :
new Point(this.Location.X + 30 + colID.Width + colTermNo.Width + colInReqCourseID.Width +
colBtnInReqCourseID.Width + colPreReqCourseID.Width, this.Location.Y + 75);
//
// set Checked Items
//
if (scForm.LstSelectedCourse.Value != null)
{
string strID = "";
foreach (char chID in scForm.LstSelectedCourse.Value.ToString().ToCharArray())
{
if (char.IsDigit(chID)) // find ID in string value
{
strID += chID.ToString();
}
else if (strID != "") // search and save in checked list
{
scForm.chblstSelectCourse.SetItemChecked(
scForm.chblstSelectCourse.Items.IndexOf(
db.CourseSearchID(int.Parse(strID)).ToArray()[0].Name_Course), true);
strID = "";
}
// else continue;
}
}
//
// load form
//
scForm.ShowDialog();
}
}
private void newToolStripMenuItem_Click(object sender, EventArgs e)
{
LOCK = true;
try
{
bool emptyDB = !(pbtnProfessorExport.Enabled | pbtnBranchExport.Enabled |
pbtnCourseExport.Enabled | pbtnRoomExport.Enabled |
pbtnGroupExport.Enabled | pbtnClassExport.Enabled);
//
// if Database have data then show a Warning for losing data
if (!emptyDB)
{
if (MessageBox.Show("Are you sure you want to permanently delete \n" +
"full database and create new database?", "Delete Old Database",
MessageBoxButtons.YesNo, MessageBoxIcon.Warning,
MessageBoxDefaultButton.Button2) == System.Windows.Forms.DialogResult.Yes)
{
var db = new LINQDataContext();
string query = @"DELETE FROM dbo.New_GroupsPerClass " +
@"DELETE FROM dbo.Classroom_time " +
@"DELETE FROM dbo.Priority_Professor " +
@"DELETE FROM dbo.Professor " +
@"DELETE FROM dbo.Group_ID_List " +
@"DELETE FROM dbo.Groups " +
@"DELETE FROM dbo.Room " +
@"DELETE FROM dbo.Class " +
@"DELETE FROM dbo.Room_Type " +
@"DELETE FROM dbo.Course " +
@"DELETE FROM dbo.Branch";
db.ExecuteQuery<object>(query);
//
// Disable Export Buttons
pbtnProfessorExport.Enabled = false;
pbtnProfessorExport.Image = global::MakeClassSchedule.Properties.Resources.Export_Disable;
pbtnBranchExport.Enabled = false;
pbtnBranchExport.Image = global::MakeClassSchedule.Properties.Resources.Export_Disable;
pbtnCourseExport.Enabled = false;
pbtnCourseExport.Image = global::MakeClassSchedule.Properties.Resources.Export_Disable;
pbtnRoomExport.Enabled = false;
pbtnRoomExport.Image = global::MakeClassSchedule.Properties.Resources.Export_Disable;
pbtnGroupExport.Enabled = false;
pbtnGroupExport.Image = global::MakeClassSchedule.Properties.Resources.Export_Disable;
pbtnClassExport.Enabled = false;
pbtnClassExport.Image = global::MakeClassSchedule.Properties.Resources.Export_Disable;
pbtnTermSchemeExport.Enabled = false;
pbtnTermSchemeExport.Image = global::MakeClassSchedule.Properties.Resources.Export_Disable;
}
}
}
finally
{
LOCK = false;
}
}
private void dgvCourse_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
{
var db = new LINQDataContext();
// query for read any course ID and creat new iD
IEnumerable<int> qCourseID = from queryId in db.Courses
select queryId.Course_ID;
List<int> idList_in_db = qCourseID.ToList();
if (e.RowIndex > 0)
{
int ID_Counter = 1;
bool Find = true;
while (Find)
{
Find = false;
for (int rowC = 0; rowC < dgvCourse.RowCount - 2; rowC++)
{
if (int.Parse(dgvCourse[0, rowC].Value.ToString()) == ID_Counter)
{
ID_Counter++;
Find = true;
break;
}
}
if (idList_in_db.Contains(ID_Counter))
{
ID_Counter++;
Find = true;
}
}
dgvCourse.Rows[e.RowIndex - 1].Cells[0].Value = ID_Counter;
}
}
private void pbtnClassExport_Click(object sender, EventArgs e)
{
SaveFileDialog save2Excel = new SaveFileDialog();
save2Excel.Filter = @"Excel files|*.xls";
save2Excel.DefaultExt = "ClassData.xls";
save2Excel.FileName = "ClassData.xls";
if (save2Excel.ShowDialog() == DialogResult.OK)
{
// 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 load from OLEDB db.Class.mdf database file's
ExcelFile ef = new ExcelFile();
ef.LimitReached += new LimitEventHandler(ef_LimitReached);
ef.LimitNear += new LimitEventHandler(ef_LimitNear);
#region Class Worksheet's
var aryDBClass = db.Classes.ToArray();
ExcelWorksheet ws = ef.Worksheets.Add("Class");
//
// column header text
ws.Cells[0, 0].Value = "Class ID";
ws.Cells[0, 1].Value = "Professor ID (Priority)";
ws.Cells[0, 2].Value = "Professor Name";
ws.Cells[0, 3].Value = "Course ID";
ws.Cells[0, 4].Value = "Course Name";
ws.Cells[0, 5].Value = "Practical Unit";
ws.Cells[0, 6].Value = "Theory Unit";
ws.Cells[0, 7].Value = "Room Type";
//
// column width
ws.Columns[0].Width = 15 * 256;
ws.Columns[1].Width = 20 * 256;
ws.Columns[2].Width = 30 * 256;
ws.Columns[3].Width = 15 * 256;
ws.Columns[4].Width = 30 * 256;
ws.Columns[5].Width = 15 * 256;
ws.Columns[6].Width = 15 * 256;
ws.Columns[7].Width = 25 * 256;
//
// Header Hight
ws.Rows[0].Height = 2 * 256;
//
// row data style's
CellStyle tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.LightYellow);
tmpStyle.Font.Weight = ExcelFont.BoldWeight;
tmpStyle.Font.Color = Color.Black;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
ws.Cells.GetSubrangeAbsolute(1, 0, aryDBClass.Length, 7).Style = tmpStyle;
//
// row data
for (int rowC = 0; rowC < aryDBClass.Length; rowC++)
{
ws.Cells[rowC + 1, 0].Value = aryDBClass[rowC].Class_ID;
//
// set Professor and Priority
//
var Professor = (from p in db.Priority_Professors
join prof in db.Professors
on p.Professor_ID equals prof.ID
where p.Class_ID == aryDBClass[rowC].Class_ID
select new
{
p.Professor_ID,
p.Priority,
prof.Name_Professor
});
string professor_ID_Priority = "";
string prof_Name = "";
foreach (var anyProf in Professor)
{
professor_ID_Priority += anyProf.Professor_ID + "(" + anyProf.Priority + ") ";
prof_Name += anyProf.Name_Professor + " & ";
}
if (prof_Name.Length >= 5) prof_Name = prof_Name.Substring(0, prof_Name.Length - 5);
//
ws.Cells[rowC + 1, 1].Value = professor_ID_Priority;
ws.Cells[rowC + 1, 2].Value = prof_Name;
ws.Cells[rowC + 1, 3].Value = aryDBClass[rowC].Course_ID;
ws.Cells[rowC + 1, 4].Value = aryDBClass[rowC].Course.Name_Course.ToString();
ws.Cells[rowC + 1, 5].Value = aryDBClass[rowC].Practical_unit;
ws.Cells[rowC + 1, 6].Value = aryDBClass[rowC].Theory_unit;
ws.Cells[rowC + 1, 7].Value = aryDBClass[rowC].RoomType.ToString();
}
//
// Header style's
tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.Chocolate);
tmpStyle.Font.Weight = ExcelFont.MaxWeight;
tmpStyle.Font.Color = Color.White;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Medium);
ws.Cells.GetSubrangeAbsolute(0, 0, 0, 7).Style = tmpStyle;
#endregion
#region Group_ID_List Worksheet's
var aryDBGroup_ID_List = db.Group_ID_Lists.ToArray();
ws = ef.Worksheets.Add("Group_ID_List");
//
// column header text
ws.Cells[0, 0].Value = "Class ID";
ws.Cells[0, 1].Value = "Group ID";
//
// column width
ws.Columns[0].Width = 15 * 256;
ws.Columns[1].Width = 15 * 256;
//
// Header Hight
ws.Rows[0].Height = 2 * 256;
//
// row data style's
tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.AliceBlue);
tmpStyle.Font.Weight = ExcelFont.BoldWeight;
tmpStyle.Font.Color = Color.Black;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
ws.Cells.GetSubrangeAbsolute(1, 0, aryDBGroup_ID_List.Length, 1).Style = tmpStyle;
//
// row data
for (int rowC = 0; rowC < aryDBGroup_ID_List.Length; rowC++)
{
ws.Cells[rowC + 1, 0].Value = aryDBGroup_ID_List[rowC].Class_ID;
ws.Cells[rowC + 1, 1].Value = aryDBGroup_ID_List[rowC].Group_ID;
}
//
// Header style's
tmpStyle = new CellStyle();
tmpStyle.HorizontalAlignment = HorizontalAlignmentStyle.Center;
tmpStyle.VerticalAlignment = VerticalAlignmentStyle.Center;
tmpStyle.FillPattern.SetSolid(Color.Aqua);
tmpStyle.Font.Weight = ExcelFont.MaxWeight;
tmpStyle.Font.Color = Color.White;
tmpStyle.WrapText = true;
tmpStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Medium);
ws.Cells.GetSubrangeAbsolute(0, 0, 0, 1).Style = tmpStyle;
#endregion
//
// save professor data in save2Excel.FileName path's
//
ef.SaveXls(save2Excel.FileName);
//
// Try to open created excel file's
//
try
{
System.Diagnostics.Process.Start(save2Excel.FileName);
}
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
}
}
private void ProfessorForm_FormClosing(object sender, FormClosingEventArgs e)
{
dgvProfessor[0, 0].Selected = true;
// ----------------------------------------------------------------------------------------------
//
var db = new LINQDataContext();
for (int rowCounter = 0; rowCounter < dgvProfessor.RowCount - 1; rowCounter++)
{
//
// search dgvProfessor.rows in db.Professor
//
int ID_No = 0;
int.TryParse(dgvProfessor[0, rowCounter].Value.ToString(), out ID_No);
// Define the query expression.
IEnumerable<int> query =
from prof in db.Professors
where prof.ID == ID_No
select prof.ID;
if (query.ToArray().Length > 0) // EDIT
{
db.ProfessorEdit(ID_No,
(dgvProfessor[1, rowCounter].Value != null) ? (string)dgvProfessor[1, rowCounter].Value.ToString() : "",
(dgvProfessor[2, rowCounter].Value != null) ? (string)dgvProfessor[2, rowCounter].Value.ToString() : "",
(dgvProfessor[3, rowCounter].Value != null && dgvProfessor[3, rowCounter].Value.ToString().ValidateEmail()) ?
(string)dgvProfessor[3, rowCounter].Value.ToString() : "",
(dgvProfessor[4, rowCounter].Value != null) ? (string)dgvProfessor[4, rowCounter].Value.ToString() : "",
(dgvProfessor[6, rowCounter].Value != null) ? (string)dgvProfessor[6, rowCounter].Value.ToString() : "");
}
else // SAVE NEW
{
db.ProfessorSave(ID_No,
(dgvProfessor[1, rowCounter].Value != null) ? (string)dgvProfessor[1, rowCounter].Value.ToString() : "",
(dgvProfessor[2, rowCounter].Value != null) ? (string)dgvProfessor[2, rowCounter].Value.ToString() : "",
(dgvProfessor[3, rowCounter].Value != null && dgvProfessor[3, rowCounter].Value.ToString().ValidateEmail()) ?
(string)dgvProfessor[3, rowCounter].Value.ToString() : "",
(dgvProfessor[4, rowCounter].Value != null) ? (string)dgvProfessor[4, rowCounter].Value.ToString() : "",
(dgvProfessor[6, rowCounter].Value != null) ? (string)dgvProfessor[6, rowCounter].Value.ToString() : "");
}
}
db.Dispose();
}
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 ProfessorForm_Load(object sender, EventArgs e)
{
dgvProfessor.Rows.Clear();
var db = new LINQDataContext();
var aryReader = db.Professors.ToArray();
if (aryReader.Length > 0)
{
dgvProfessor.Rows.Add(aryReader.Length);
for (int rowCounter = 0; rowCounter < aryReader.Length; rowCounter++)
{
dgvProfessor[0, rowCounter].Value = aryReader[rowCounter].ID.ToString();
if (aryReader[rowCounter].Name_Professor != null)
dgvProfessor[1, rowCounter].Value = aryReader[rowCounter].Name_Professor.ToString();
dgvProfessor[2, rowCounter].Value = (aryReader[rowCounter].Branch != null) ? aryReader[rowCounter].Branch.ToString() : "";
dgvProfessor[3, rowCounter].Value = (aryReader[rowCounter].Email != null) ? aryReader[rowCounter].Email.ToString() : "";
dgvProfessor[4, rowCounter].Value = (aryReader[rowCounter].EducationDegree != null) ? aryReader[rowCounter].EducationDegree.ToString() : "";
if (aryReader[rowCounter].Schedule != null)
dgvProfessor[6, rowCounter].Value = aryReader[rowCounter].Schedule.ToString();
}
}
}
private void dgvClass_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
int id = 0;
if (int.TryParse(e.Row.Cells[0].Value.ToString(), out id))
{
var db = new LINQDataContext();
db.Priority_ProfessorDeleteClass(id);
db.Classroom_TimeDeleteClass(id);
db.Group_ID_ListDeleteClass(id);
db.ClassDelete(id);
db.Dispose();
}
}
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 saveAndCompile_Priority(string priority, int classId)
{
var db = new LINQDataContext().Priority_Professors;
//
// Compile the priority
//
Dictionary<int, int> ProfessorKey_PriorityValue = new Dictionary<int, int>();
string keyBuffer = "";
string valueBuffer = "";
bool showParantes = false;
try
{
foreach (char ch in priority.ToCharArray())
{
if (char.IsDigit(ch) && !showParantes) // a Key Number char
{
keyBuffer += ch.ToString();
}
else if (ch == '(') // a '(' before Value Number char
{
showParantes = true;
}
else if (char.IsDigit(ch) && showParantes) // a Value Number char
{
valueBuffer += ch.ToString();
}
else if (ch == ')')
{
showParantes = false;
ProfessorKey_PriorityValue.Add(int.Parse(keyBuffer), int.Parse(valueBuffer));
keyBuffer = string.Empty;
valueBuffer = string.Empty;
}
else
continue;
}
//
// Save by classId
//
foreach (var anyPP in ProfessorKey_PriorityValue)
new LINQDataContext().Priority_ProfessorSave(anyPP.Key, classId, anyPP.Value);
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
}
private void bwExportBtnCheck_DoWork(object sender, DoWorkEventArgs e)
{
if (!LOCK)
{
#region Check Codes
var db = new LINQDataContext();
//
// Professor Export
//
if (db.Professors.ToArray().Length > 0)
{
setCheckerWork(ref pbtnProfessorExport, true);
}
else
{
setCheckerWork(ref pbtnProfessorExport, false);
}
//
// Branch Export
//
if (db.Branches.ToArray().Length > 0)
{
setCheckerWork(ref pbtnBranchExport, true);
}
else
{
setCheckerWork(ref pbtnBranchExport, false);
}
//
// Course Export
//
if (db.Courses.ToArray().Length > 0)
{
setCheckerWork(ref pbtnCourseExport, true);
}
else
{
setCheckerWork(ref pbtnCourseExport, false);
}
//
// Room Export
//
if (db.Rooms.ToArray().Length > 0)
{
setCheckerWork(ref pbtnRoomExport, true);
}
else
{
setCheckerWork(ref pbtnRoomExport, false);
}
//
// Groups Export
//
if (db.Groups.ToArray().Length > 0)
{
setCheckerWork(ref pbtnGroupExport, true);
}
else
{
setCheckerWork(ref pbtnGroupExport, false);
}
//
// TermScheme Export
//
if (db.Courses.ToArray().Length > 0)
{
setCheckerWork(ref pbtnTermSchemeExport, true);
}
else
{
setCheckerWork(ref pbtnTermSchemeExport, false);
}
//
// Class Export
//
if (db.Classes.ToArray().Length > 0)
{
setCheckerWork(ref pbtnClassExport, true);
}
else
{
setCheckerWork(ref pbtnClassExport, false);
}
#endregion
//
// Garbage Collection Run to delete any object without Reference
GC.Collect();
}
}
