string[] Get_working_History(string Empl_ID, DateTime date) { string[] empl_info = { "", "", "", "" }; SQL_API.SQL_ATC spl_obj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sqlcmd = @"SELECT distinct [Date] ,[ShiftName] ,[LineID] ,[WST_ID] ,[SubLine_ID] ,[Empl_ID] ,[Empl_Name] ,[From_Time] FROM [JOB_ASSIGNMENT_DB].[dbo].[P007_P008_Tracking]"; sqlcmd += @" WHERE Date = '" + date.ToString("dd MMM yyyy") + "' and Empl_ID = '" + Empl_ID + "'"; sqlcmd += @" AND (([ShiftName] = 'Shift_1' AND [From_Time] BETWEEN '06:00:00' and '14:00:00' or [To_Time] is null)"; sqlcmd += @" OR ([ShiftName] = 'Shift_2' AND [From_Time] BETWEEN '14:00:00' and '22:00:00' or [To_Time] is null)"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '22:00:00' and '23:59:59' or [To_Time] is null)"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '00:00:00' and '06:00:00' or [To_Time] is null))"; sqlcmd += @" ORDER by [From_Time] DESC"; spl_obj.GET_SQL_DATA(sqlcmd); // cái này chưa đúng if ((spl_obj.DaTable != null) && (spl_obj.DaTable.Rows.Count > 0)) { empl_info[0] = spl_obj.DaTable.Rows[0]["ShiftName"].ToString().Trim(); empl_info[1] = spl_obj.DaTable.Rows[0]["LineID"].ToString().Trim(); empl_info[2] = spl_obj.DaTable.Rows[0]["SubLine_ID"].ToString().Trim(); empl_info[3] = spl_obj.DaTable.Rows[0]["WST_ID"].ToString().Trim(); } return empl_info; }
private DataTable Get_Empl() { string cmd = @"select distinct Empl_ID, Empl_Name FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_002_Empl_Skill]"; SQL_API.SQL_ATC sqlobj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); sqlobj.GET_SQL_DATA(cmd); return sqlobj.DaTable; }
private bool Create_Empl_Plan(DateTime date) { string sql_cmd, mess; bool b; int count; int i = 0, total; StatusLabel1.Visible = true; ProgressBar1.Visible = true; StatusLabel1.Text = "Create Empl Working Plan"; SQL_API.SQL_ATC all_empl_list = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); // lay du lieu ke hoach sx theo line cua ngay da chon sql_cmd = String.Format("SELECT * FROM [P_005_EmplWorkingPlan] WHERE [Date] = '{0}' order by LineId", date.ToString("yyyy-MMM-dd")); b = EmplWorkingPlan_MasterDatabase.MasterDatabase_GridviewTBL.Load_DataBase(MasterDatabase_Connection_Str, sql_cmd); if (b == false) { return false; } count = EmplWorkingPlan_MasterDatabase.MasterDatabase_GridviewTBL.Data_dtb.Rows.Count; if (count > 0) { mess = "Plan for date:" + date.ToString("dd MMM yyyy") + "was existing\n"; mess += "Do you want to delete and create the new one?"; if (MessageBox.Show(mess, "Warning", MessageBoxButtons.YesNo) == DialogResult.No) { return false; } DeleteEmpl_plan(date); EmplWorkingPlan_MasterDatabase.MasterDatabase_GridviewTBL.Load_DataBase(MasterDatabase_Connection_Str, sql_cmd); } // Load All Empl sql_cmd = @"Select Distinct [Empl_ID], [Empl_Name] FROM [MDB_002_Empl_Skill]"; all_empl_list.GET_SQL_DATA(sql_cmd); total = all_empl_list.DaTable.Rows.Count; foreach (DataRow row in all_empl_list.DaTable.Rows) { DataRow newrow = EmplWorkingPlan_MasterDatabase.MasterDatabase_GridviewTBL.Data_dtb.NewRow(); newrow["Empl_ID"] = row["Empl_ID"]; newrow["Empl_Name"] = row["Empl_Name"]; newrow["Date"] = date.ToString("MM/dd/yyyy"); if (GetEmplPlan(ref newrow) == true) { EmplWorkingPlan_MasterDatabase.MasterDatabase_GridviewTBL.Data_dtb.Rows.Add(newrow); } i++; ProgressBar1.Value = i * 100 / total; } Update_SQL_Data(EmplWorkingPlan_MasterDatabase.MasterDatabase_GridviewTBL.Data_da, EmplWorkingPlan_MasterDatabase.MasterDatabase_GridviewTBL.Data_dtb); StatusLabel1.Visible = false; ProgressBar1.Visible = false; return true; }
private bool DeleteEmpl_plan(DateTime date) { bool result; SQL_API.SQL_ATC sql_api = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"Delete FROM [P_005_EmplWorkingPlan] WHERE [Date] = '" + date.ToString("dd MMM yyyy") + "'"; result = sql_api.Execute_SQL_CMD(cmd); return result; }
DataTable Load_Empl_In_WST() { string sql_cmd = @"SELECT WST_ID, COUNT(WST_ID) as 'num_of_person_do_WST' FROM [JOB_ASSIGNMENT_DB].[dbo].[R007_Employee_In_WST] GROUP BY WST_ID "; SQL_API.SQL_ATC sqlobj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); sqlobj.GET_SQL_DATA(sql_cmd); return sqlobj.DaTable; }
DataTable Load_all_WST() { string sql_cmd = @"SELECT Distinct [WST_ID] ,[LineID] ,[SubLine_ID] ,[GroupID] FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_004_LineSkillRequest] "; SQL_API.SQL_ATC sqlobj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); sqlobj.GET_SQL_DATA(sql_cmd); return sqlobj.DaTable; }
string GetGroupID(string lineID) { SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string groupid = ""; string cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_004_LineSkillRequest]"; cmd += @"Where [LineID] ='" + lineID + "'"; sqlObj.GET_SQL_DATA(cmd); if (sqlObj.DaTable != null && sqlObj.DaTable.Rows.Count > 0) { groupid = sqlObj.DaTable.Rows[0]["GroupID"].ToString().Trim() ; } return groupid; }
//public string Get_Empl_Name(string cur_msnv) //{ // string empl_name = "", msnv; // foreach (DataRow row in All_Empl_List_Tbl.Rows) // { // msnv = row["Empl_ID"].ToString().Trim(); // if (msnv == cur_msnv) // { // empl_name = row["Last_Name"].ToString().Trim(); // empl_name += " " + row["Mid_Name"].ToString().Trim(); // empl_name += " " + row["First_Name"].ToString().Trim(); // break; // } // } // return empl_name; //} public string Get_Empl_Name(string cur_msnv) { string empl_name = ""; SQL_API.SQL_ATC sql_cm = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sql_cmd = @"SELECT * FROM [MDB_002_Empl_Skill] WHERE [Empl_ID] = '" + cur_msnv.Trim() + "'"; sql_cm.GET_SQL_DATA(sql_cmd); if ((sql_cm.DaTable != null) && (sql_cm.DaTable.Rows.Count > 0)) { empl_name = sql_cm.DaTable.Rows[0]["Empl_Name"].ToString().Trim(); } return empl_name; }
public DataTable Load_Cur_JobsPlan(string empl_id, DateTime date) { SQL_API.SQL_ATC job_plan_table = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); if (empl_id == "") { return null; } string date_str = date.ToString("dd MMM yyyy"); string time_str = date.ToString("HH:mm"); string sql_cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[P_003_KeHoachSanXuatTheoLine] "; sql_cmd += " WHERE Empl_ID = '" + empl_id + "'"; sql_cmd += " AND Date = '" + date_str + "'"; sql_cmd += " AND ('" + time_str + "' BETWEEN From_Time AND To_Time"; sql_cmd += " OR ('" + time_str + "' BETWEEN '18:00:00' AND '23:59:59' AND ShiftName = 'SHIFT_3')"; sql_cmd += " OR ('" + time_str + "' BETWEEN '00:00:00' AND '06:00:00' AND ShiftName = 'SHIFT_3'))"; // edit Thuy: them so 0 truoc so 6 (6:00:00) job_plan_table.GET_SQL_DATA(sql_cmd); return job_plan_table.DaTable; }
private bool Empl_Skill_List_Init() { if (Empl_Skill_List_Exist == true) { if (tabControl1.TabPages.Contains(Empl_Skill_List_MasterDatabase.MasterDatabase_Tab) == false) { tabControl1.TabPages.Insert(tabControl1.TabPages.Count, Empl_Skill_List_MasterDatabase.MasterDatabase_Tab); } tabControl1.SelectTab("Employee_vs_Skill"); return true; } Empl_Skill_List_Exist = true; Init_Empl_Skill_Excel(); Empl_Skill_List_MasterDatabase = new MaterDatabase(OpenXL, tabControl1, "Employee_vs_Skill", SkillList_Index, MasterDatabase_Connection_Str, Empl_Skill_List_Init_Database_CMD, Empl_Skill_List_Select_CMD, 3, Empl_Skill_Excel_Struct, filterStatusLabel, showAllLabel, StatusLabel1, StatusLabel2, ProgressBar1); // Empl_Skill_List_MasterDatabase.MasterDatabase_GridviewTBL.dataGridView_View.Columns["Line_ID"].Frozen = true; Empl_Skill_List_MasterDatabase.MasterDatabase_GridviewTBL.GridView.BackgroundColor = Color.White; All_Skill_List = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sql_cmd = @"SELECT [Skill_ID] ,[Skill_Name] FROM [MDB_001_Skill_List_Tbl]"; All_Skill_List.GET_SQL_DATA(sql_cmd); DataGridViewMultiColumnComboBoxColumn col = new DataGridViewMultiColumnComboBoxColumn(); col.Name = "Skill_ID"; col.DataPropertyName = "Skill_ID"; col.ValueMember = "Skill_ID"; col.DataSource = All_Skill_List.DaTable; col.ColumnWidths = new List<string>() { "60", "150" }; if (Empl_Skill_List_MasterDatabase.MasterDatabase_GridviewTBL.GridView.Columns.Contains("Skill_ID")) { int index = Empl_Skill_List_MasterDatabase.MasterDatabase_GridviewTBL.GridView.Columns["Skill_ID"].Index; Empl_Skill_List_MasterDatabase.MasterDatabase_GridviewTBL.GridView.Columns.RemoveAt(index); Empl_Skill_List_MasterDatabase.MasterDatabase_GridviewTBL.GridView.Columns.Insert(index, col); col.HeaderCell = new DataGridViewAutoFilterColumnHeaderCell(col.HeaderCell); } Empl_Skill_List_MasterDatabase.MasterDatabase_GridviewTBL.GridView.CellValueChanged += new DataGridViewCellEventHandler(Empl_Skill_List_MasterDatabase_GridView_CellValueChanged); return true; }
string[] Get_Tracking_Empl(DateTime date, string shift, string line, string wst_id) { string []empl_info = {"", ""}; SQL_API.SQL_ATC spl_obj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sqlcmd = @"SELECT distinct [Date] ,[ShiftName] ,[LineID] ,[LineName] ,[SubLine_ID] ,[SubLine_Name] ,[WST_ID] ,[WST_Name] ,[Empl_ID] ,[Empl_Name] ,[From_Time] ,[Out_Manual] ,[To_Time] ,[WorkingTime] FROM [JOB_ASSIGNMENT_DB].[dbo].[P007_P008_Tracking]"; sqlcmd += @" WHERE Date = '" + date.ToString("dd MMM yyyy") + "' and ShiftName = '" + shift + "' and LineID = '" + line + "' and WST_ID = '" + wst_id + "'"; //sqlcmd += @" WHERE Date = '" + date.ToString("dd MMM yyyy") + "' and LineID = '" + line + "' and WST_ID = '" + wst_id + "'"; sqlcmd += @" AND (([ShiftName] = 'Shift_1' AND [From_Time] BETWEEN '06:00:00' and '14:00:00')"; sqlcmd += @" OR ([ShiftName] = 'Shift_2' AND [From_Time] BETWEEN '14:00:00' and '22:00:00')"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '22:00:00' and '23:59:59')"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '00:00:00' and '06:00:00'))"; sqlcmd += @" ORDER by [From_Time] DESC"; spl_obj.GET_SQL_DATA(sqlcmd); DataTable d = spl_obj.DaTable; // cái này chưa đúng if ((spl_obj.DaTable != null) && (spl_obj.DaTable.Rows.Count > 0)) { empl_info[0] = spl_obj.DaTable.Rows[0]["Empl_ID"].ToString().Trim(); empl_info[1] = spl_obj.DaTable.Rows[0]["Empl_Name"].ToString().Trim(); } return empl_info; }
string[] Get_employee_History(string lineID, string wst, string shift, DateTime date) { string[] empl_info = { "", "" }; SQL_API.SQL_ATC spl_obj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sqlcmd = @"SELECT distinct [Empl_ID] ,[Empl_Name], [From_Time] FROM [JOB_ASSIGNMENT_DB].[dbo].[P007_P008_Tracking]"; if (shift == SHIFT_1) { sqlcmd += @" WHERE Date = '" + date.AddDays(-1).ToString("dd MMM yyyy") + "' and LineID = '" + lineID + "' and WST_ID = '" + wst + "'"; sqlcmd += @" AND (([ShiftName] = 'Shift_1' AND [From_Time] BETWEEN '06:00:00' and '14:00:00')"; sqlcmd += @" OR ([ShiftName] = 'Shift_2' AND [From_Time] BETWEEN '14:00:00' and '22:00:00')"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '22:00:00' and '23:59:59')"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '00:00:00' and '06:00:00'))"; sqlcmd += @" AND ([ShiftName] = '" + shift + "')"; sqlcmd += @" ORDER by [From_Time] DESC"; } else { sqlcmd += @" WHERE Date = '" + date.AddDays(-2).ToString("dd MMM yyyy") + "' and LineID = '" + lineID + "' and WST_ID = '" + wst + "'"; sqlcmd += @" AND (([ShiftName] = 'Shift_1' AND [From_Time] BETWEEN '06:00:00' and '14:00:00')"; sqlcmd += @" OR ([ShiftName] = 'Shift_2' AND [From_Time] BETWEEN '14:00:00' and '22:00:00')"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '22:00:00' and '23:59:59')"; sqlcmd += @" OR ([ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '00:00:00' and '06:00:00'))"; sqlcmd += @" AND ([ShiftName] = '" + shift + "')"; sqlcmd += @" ORDER by [From_Time] DESC"; } spl_obj.GET_SQL_DATA(sqlcmd); if ((spl_obj.DaTable != null) && (spl_obj.DaTable.Rows.Count > 0)) { empl_info[0] = spl_obj.DaTable.Rows[0]["Empl_ID"].ToString().Trim(); empl_info[1] = spl_obj.DaTable.Rows[0]["Empl_Name"].ToString().Trim(); } return empl_info; }
DataTable Get_Empl_HadJob(DateTime date) { SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT distinct [Empl_ID] FROM [JOB_ASSIGNMENT_DB].[dbo].[P_003_KeHoachSanXuatTheoLine] Where [Empl_ID] != '' and [Empl_ID] is not NULL"; cmd += " AND Date = '" + date.ToString("dd MMM yyyy") + "'"; sqlObj.GET_SQL_DATA(cmd); return sqlObj.DaTable; }
private bool Is_Done_PO(string po) { SQL_API.SQL_ATC sql_cm = new SQL_API.SQL_ATC(Kitting_Connection_Str); string sql_cmd = @"SELECT [Sector] ,[Series] ,[TopPONumber] ,[TopModel] ,[TypeCO] ,[Priority] ,[POQty] FROM [OpenPOPlanner] WHERE [TopPONumber] = '" + po.Trim() + "'"; sql_cm.GET_SQL_DATA(sql_cmd); string type_po = sql_cm.DaTable.Rows[0]["TypeCO"].ToString().Trim(); if (type_po == "Done PO") { return true; } return false; }
private bool IsForceClose() { SQL_API.SQL_ATC sql_cm = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT [ForceTurnOff] FROM [ForceTurnOff]"; bool force_clode = true; sql_cm.GET_SQL_DATA(cmd); if ((sql_cm.DaTable != null) && (sql_cm.DaTable.Rows.Count > 0)) { try { force_clode = (bool)(sql_cm.DaTable.Rows[0]["ForceTurnOff"] == null ? false : sql_cm.DaTable.Rows[0]["ForceTurnOff"]); } catch { } } return force_clode; }
DataTable Get_ForceList() { DataTable retult; string filter = string.Empty; //filter += string.Format("[Position] = 'Lead' OR [Position] = 'TS1' OR [Position] = 'TS2' "); //Lead ko nằm trong danh sách force, chỉ nằm trong danh sách ưu tiên (nếu ko tìm được chỗ, vẫn có thể qua wst #) //Force: ko tìm được chỗ phù hợp ==> Qua stand filter += string.Format("[Position] = 'TS1' OR [Position] = 'TS2' "); SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_007_Fix_Position] Where "+ filter; sqlObj.GET_SQL_DATA(cmd); retult = sqlObj.DaTable; CheckAndInsertColumIfNotExist(ref retult, "ForceShift", -1, typeof(string)); CheckAndInsertColumIfNotExist(ref retult, "ForceWST", -1, typeof(string)); foreach (DataRow row in retult.Rows) { string Position = row["Position"].ToString(); if (Position == "TS1" || Position == "TS2") { row["ForceShift"] = "Shift_1"; } if (Position == "TS1") { row["ForceWST"] = row["WST_ID"].ToString(); } } return retult; }
DataTable Get_TS_List() { //string[] conditions = new string[] {"TS1","TS2"}; string[] conditions = new string[] {"TS1"}; string filter = string.Empty; foreach (var condition in conditions) { if (filter != string.Empty) { filter += " OR "; } filter += string.Format("[Position] = '{0}'", condition.ToString().Trim()); } SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_007_Fix_Position] Where " + filter; sqlObj.GET_SQL_DATA(cmd); return sqlObj.DaTable; }
DataTable GetFruPlan(DateTime date) { SQL_API.SQL_ATC sql_obj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"Select * FROM [P_003_KeHoachSanXuatTheoLine] WHERE [Date] = '" + date.ToString("dd MMM yyyy") + "'"; cmd += " AND LineID = 'FRU'"; sql_obj.GET_SQL_DATA(cmd); return sql_obj.DaTable; }
// add by thuy private string[] Get_Empl_Du(DateTime date, string close_his_PO, string wst) { string[] empl_info = { "", "", "" }; SQL_API.SQL_ATC spl_obj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sql_cmd = @"SELECT Empl_ID, Empl_Name, WST_ID, From_Time FROM [JOB_ASSIGNMENT_DB].[dbo].[P007_P008_Tracking] "; sql_cmd += "WHERE [Date] = '" + date.ToString("dd MMM yyyy") + "' AND [PO] = '" + close_his_PO + "' AND WST_ID = '" + wst + "' "; sql_cmd += "ORDER BY WST_ID, From_Time desc"; spl_obj.GET_SQL_DATA(sql_cmd); if ((spl_obj.DaTable != null) && (spl_obj.DaTable.Rows.Count > 0)) { empl_info[0] = spl_obj.DaTable.Rows[0]["Empl_ID"].ToString().Trim(); empl_info[1] = spl_obj.DaTable.Rows[0]["Empl_Name"].ToString().Trim(); empl_info[2] = spl_obj.DaTable.Rows[0]["WST_ID"].ToString().Trim(); } return empl_info; }
string Get_Plan_Shift(string empl, DateTime date) { string shift = ""; SQL_API.SQL_ATC sql_obj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[P_003_KeHoachSanXuatTheoLine]"; cmd += " Where [Empl_ID] = '" + empl + "'"; cmd += " AND [Date] = '" + date.ToString("dd MMM yyyy") + "'"; sql_obj.GET_SQL_DATA(cmd); if ((sql_obj.DaTable != null) && (sql_obj.DaTable.Rows.Count > 0)) { shift = sql_obj.DaTable.Rows[0]["ShiftName"].ToString().Trim(); } return shift; }
DataTable Get_Empl_Skill_List() { SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); //Dho: Hiện tại, nếu sử dụng câu query bên trên --> ứng với mỗi nhân viên sẽ tìm được nhiều records //(mỗi record ứng với 1 skill...) //Việc này có lẽ ko cần thiết ??? Chỉ cần return danh sách employee (1 employee <-> 1 record) //Các thông tin cần thiết khác, engine trong NewEmployeeAssignmnet đã tự lấy string cmd = @"Select Distinct [Empl_ID], [Empl_Name] FROM [MDB_002_Empl_Skill]"; sqlObj.GET_SQL_DATA(cmd); return sqlObj.DaTable; }
DataTable Get_Fixposition() { SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_007_Fix_Position] Where [Position] != 'Lead' OR [Position] is null"; sqlObj.GET_SQL_DATA(cmd); return sqlObj.DaTable; }
DataTable Get_Fixposition_WST() { SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_007_Fix_Position] Where [WST_ID] <> '' or [WST_ID] is NULL"; sqlObj.GET_SQL_DATA(cmd); return sqlObj.DaTable; }
//DataTable GetTrackingHistory( DateTime date) //{ // DateTime from_date; // from_date = date.AddDays(-1); // SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); // string cmd = @"SELECT distinct [Date],[From_Time],[ShiftName],[Empl_ID],[Empl_Name],[WST_ID],[LineID] FROM P007_P008_Tracking"; // cmd += @" WHERE (([Date] = '" + from_date.ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_1' AND [From_Time] BETWEEN '6:00:00' and '14:00:00')"; // cmd += @" OR ([Date] = '" + from_date.AddDays(-1).ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_2' AND [From_Time] BETWEEN '14:00:00' and '22:00:00')"; // cmd += @" OR ([Date] = '" + from_date.AddDays(-1).ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '22:00:00' and '23:59:59')"; // cmd += @" OR ([Date] = '" + from_date.AddDays(-1).ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_3' AND [From_Time] BETWEEN '00:00:00' and '06:00:00'))"; // cmd += @" AND ([WST_ID] is not NULL AND [WST_ID] != '')"; // cmd += @" AND ([SubLine_ID] != 'FRU')"; // //if ((date.DayOfWeek == DayOfWeek.Tuesday) && (DateTime.Now.DayOfWeek == DayOfWeek.Monday)) // //{ // // cmd += @" WHERE (([Date] = '" + date.AddDays(-1).ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_1')"; // // cmd += @" OR ([Date] = '" + from_date.ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_2')"; // // cmd += @" OR ([Date] = '" + from_date.ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_3'))"; // // cmd += @" AND ([WST_ID] is not NULL AND [WST_ID] != '')"; // // cmd += @" AND ([SubLine_ID] != 'FRU')"; // //} // //else if ((date.DayOfWeek == DayOfWeek.Tuesday) && (DateTime.Now.Date == date.Date)) // //{ // // cmd += @" WHERE (([Date] = '" + from_date.ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_1')"; // // cmd += @" OR ([Date] = '" + from_date.ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_2')"; // // cmd += @" OR ([Date] = '" + from_date.ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_3'))"; // // cmd += @" AND ([WST_ID] is not NULL AND [WST_ID] != '')"; // // cmd += @" AND ([SubLine_ID] != 'FRU')"; // //} // //else // //{ // // cmd += @" WHERE (([Date] = '" + from_date.ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_1')"; // // cmd += @" OR ([Date] = '" + from_date.AddDays(-1).ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_2')"; // // cmd += @" OR ([Date] = '" + from_date.AddDays(-1).ToString("dd MMM yyyy") + "' AND [ShiftName] = 'Shift_3'))"; // // cmd += @" AND ([WST_ID] is not NULL AND [WST_ID] != '')"; // // cmd += @" AND ([SubLine_ID] != 'FRU')"; // //} // cmd += @" AND ([LineID] is not NULL AND [LineID] != '')" + " ORDER by [Date] DESC ,[From_Time] DESC "; // sqlObj.GET_SQL_DATA(cmd); // return sqlObj.DaTable; //} DataTable GetTrackingHistory(DateTime date) { DateTime from_date; if (date.DayOfWeek == DayOfWeek.Monday) { from_date = date.AddDays(-2); } else { from_date = date.AddDays(-1); } SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT distinct [Date],[From_Time],[ShiftName],[Empl_ID],[Empl_Name],[WST_ID],[LineID] FROM P_003_KeHoachSanXuatTheoLine"; cmd += @" WHERE ([Date] = '" + from_date.ToString("dd MMM yyyy") + "')"; cmd += @" AND ([WST_ID] is not NULL AND [WST_ID] != '')"; cmd += @" AND ([SubLine_ID] != 'FRU')"; cmd += @" AND ([LineID] is not NULL AND [LineID] != '')" + " ORDER by [Date] DESC ,[From_Time] DESC "; sqlObj.GET_SQL_DATA(cmd); return sqlObj.DaTable; }
private bool GetEmplPlan(ref DataRow row) { string empl_id, cmd; DateTime date; string shiftName; string empl_id_leave; string cmd_sql; SQL_API.SQL_ATC line_plan = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); try { empl_id = row["Empl_ID"].ToString().Trim(); date = (DateTime)row["Date"]; // Load Line Plan cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[P_003_KeHoachSanXuatTheoLine]"; cmd += @" WHERE Date = '" + date.ToString("dd MMM yyyy") + "'"; cmd += @" AND Empl_ID = '" + empl_id + "'"; line_plan.GET_SQL_DATA(cmd); DataTable leave_register = Load_Leave_Register(date); if ((line_plan.DaTable != null) && (line_plan.DaTable.Rows.Count > 0)) { shiftName = line_plan.DaTable.Rows[0]["ShiftName"].ToString().Trim(); row["ShiftName"] = line_plan.DaTable.Rows[0]["ShiftName"]; row["LineID"] = line_plan.DaTable.Rows[0]["LineID"]; row["LineName"] = line_plan.DaTable.Rows[0]["LineName"]; row["SubLine_ID"] = line_plan.DaTable.Rows[0]["SubLine_ID"]; row["SubLine_Name"] = line_plan.DaTable.Rows[0]["SubLine_Name"]; row["WST_ID"] = line_plan.DaTable.Rows[0]["WST_ID"]; row["WST_Name"] = line_plan.DaTable.Rows[0]["WST_Name"]; cmd_sql = @"SELECT ShiftName, LineID, WST_ID FROM [P_003_KeHoachSanXuatTheoLine] "; // kiem tra dieu kien neu thu 2 tru 2 ngay, cacs ngay khac tru` 1 if (date.DayOfWeek == DayOfWeek.Monday) { cmd_sql += @"WHERE [date] = '" + date.AddDays(-2).ToString("dd MMM yyyy") + "' and Empl_ID ='" + empl_id + "' "; } else { cmd_sql += @"WHERE [date] = '" + date.AddDays(-1).ToString("dd MMM yyyy") + "' and Empl_ID ='" + empl_id + "' "; } line_plan.GET_SQL_DATA(cmd_sql); foreach (DataRow his in line_plan.DaTable.Rows) { row["Shift_His"] = his["ShiftName"].ToString().Trim(); row["LineID_His"] = his["LineID"].ToString().Trim(); row["WST_ID_His"] = his["WST_ID"].ToString().Trim(); } } else { foreach (DataRow leave in leave_register.Rows) { empl_id_leave = leave["Empl_ID"].ToString().Trim(); if (empl_id == empl_id_leave) { row["LeaveCode"] = leave["LeaveCode"].ToString().Trim(); } } cmd_sql = @"SELECT ShiftName, LineID, WST_ID FROM [P_003_KeHoachSanXuatTheoLine] "; // kiem tra dieu kien neu thu 2 tru 2 ngay, cacs ngay khac tru` 1 if (date.DayOfWeek == DayOfWeek.Monday) { cmd_sql += @"WHERE [date] = '" + date.AddDays(-2).ToString("dd MMM yyyy") + "' and Empl_ID ='" + empl_id + "' "; } else { cmd_sql += @"WHERE [date] = '" + date.AddDays(-1).ToString("dd MMM yyyy") + "' and Empl_ID ='" + empl_id + "' "; } line_plan.GET_SQL_DATA(cmd_sql); foreach (DataRow his in line_plan.DaTable.Rows) { row["Shift_His"] = his["ShiftName"].ToString().Trim(); row["LineID_His"] = his["LineID"].ToString().Trim(); row["WST_ID_His"] = his["WST_ID"].ToString().Trim(); } } return true; } catch { return false; } }
private string[] Get_Empl_for_WST(string wst, string shift) { string [] empl_info = {"", ""}; bool out_manual; SQL_API.SQL_ATC sql_cm = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sql_cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[P007_P008_Tracking]"; sql_cmd += " WHERE [Date] = '" + Cur_Date.ToString("dd MMM yyyy") + "'"; sql_cmd += " AND [WST_ID] = '" + wst.Trim() + "'"; sql_cmd += " AND [ShiftName] ='" + shift + "'"; sql_cmd += " AND [To_Time] IS NOT NULL"; sql_cmd += " AND [Empl_ID] != '' AND [Empl_ID] is not NULL ORDER by [To_Time] DESC"; sql_cm.GET_SQL_DATA(sql_cmd); if ((sql_cm.DaTable != null) && (sql_cm.DaTable.Rows.Count > 0)) { out_manual = sql_cm.DaTable.Rows[0]["Out_Manual"] == DBNull.Value ? false : (bool)sql_cm.DaTable.Rows[0]["Out_Manual"]; if (out_manual == false) { empl_info[0] = sql_cm.DaTable.Rows[0]["Empl_ID"].ToString().Trim(); empl_info[1] = sql_cm.DaTable.Rows[0]["Empl_Name"].ToString().Trim(); } } return empl_info; }
private DataTable Get_Kitting_Cur_PO(string lineid) { SQL_API.SQL_ATC sql_cm = new SQL_API.SQL_ATC(Kitting_Connection_Str); string sql_cmd = @"SELECT [Sector] ,[Series] ,[TopPONumber] ,[TopModel] ,[TypeCO] ,[Priority] ,[POQty] FROM [OpenPOPlanner] WHERE [lineid] like '%" + lineid.Trim() + "%'"; sql_cm.GET_SQL_DATA(sql_cmd); return sql_cm.DaTable; }
private DataTable Get_Line_Cur_PO(string lineid, DateTime date) { SQL_API.SQL_ATC sql_cm = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string sql_cmd = @"SELECT distinct [PO] FROM [JOB_ASSIGNMENT_DB].[dbo].[P007_P008_Tracking]"; sql_cmd += " WHERE [Date] = '" + date.ToString("dd MMM yyyy") + "'"; sql_cmd += " AND [LineID] = '" + Cur_Line_ID.Trim() + "'"; sql_cmd += " AND [To_Time] IS NULL"; sql_cm.GET_SQL_DATA(sql_cmd); return sql_cm.DaTable; }
string Get_Current_Shift_Name(string line, DateTime date) { SQL_API.SQL_ATC sql_cmn = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string date_str = date.ToString("dd MMM yyyy"); string time_str = date.ToString("HH:mm"); string sql_cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[P_003_KeHoachSanXuatTheoLine] "; sql_cmd += " WHERE LineID = '" + line + "'"; sql_cmd += " AND Date = '" + date_str + "'"; sql_cmd += " AND ('" + time_str + "' BETWEEN From_Time AND To_Time"; sql_cmd += " OR ('" + time_str + "' BETWEEN '18:00:00' AND '23:59:59' AND ShiftName = 'SHIFT_3')"; sql_cmd += " OR ('" + time_str + "' BETWEEN '00:00:00' AND '06:00:00' AND ShiftName = 'SHIFT_3'))"; string shift = ""; sql_cmn.GET_SQL_DATA(sql_cmd); if ((sql_cmn.DaTable != null) && (sql_cmn.DaTable.Rows.Count > 0)) { shift = sql_cmn.DaTable.Rows[0]["ShiftName"].ToString().Trim(); } if (shift == "") { shift = Get_Shift_ID(Cur_Date); } return shift; }
string Get_GroupofLine(string line) { string group = ""; SQL_API.SQL_ATC sqlObj = new SQL_API.SQL_ATC(MasterDatabase_Connection_Str); string cmd = @"SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[MDB_004_LineSkillRequest] Where [LineID] = '" + line + "'"; sqlObj.GET_SQL_DATA(cmd); if ((sqlObj.DaTable != null) && (sqlObj.DaTable.Rows.Count > 0)) { group = sqlObj.DaTable.Rows[0]["GroupID"].ToString().Trim(); } return group; }