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