/** 取得目前此段時間屬於哪一個班表 **/ private void Get_CurrentTime_ClsTime() { string Alternative = context.Request["user"].ToString(); username = Alternative != "" && Alternative != null && Alternative != "null" ? Alternative.Length == 5 ? Alternative.Substring(1) : Alternative : username; //如果是現場&非排班人員不需要跑此段 Function if (Spot.IsSchedulePerson(username) == false) { //丟空值回前端 context.Response.Write(JsonConvert.SerializeObject(new { })); return; } DateTime leavedate = DateTime.Parse(context.Request["leavedate"]); DateTime toleavedate = DateTime.Parse(context.Request["toleavedate"]); string leavedate_t = leavedate.ToString("yyyy/MM/dd"); string toleavedate_t = toleavedate.ToString("yyyy/MM/dd"); string SQL = @"/*PB29004 1=排班 3=排休*/ select WPB29.PB29002,WPB29.PB29003,WPB29.PB29004,WPB29.PB29005,WPA20.PA20003 from (select * from hrs_mis.dbo.WPB29 where pb29003='" + username + @"' AND PB29001='" + Enterprise.ConStr_produceline + @"' AND PB29002 between CAST('" + leavedate_t + @" 00:00' as datetime) and CAST('" + toleavedate_t + @" 00:00' as datetime)) WPB29 join (select * from hrs_mis.dbo.WPA20 where PA20001='" + Enterprise.ConStr_produceline + "') WPA20 on WPB29.PB29005=WPA20.PA20002"; SQL = SQL.Replace("\r\n", "").Replace("\t", ""); DataTable dt = BOSSQL.hrsExecuteQuery(SQL); //如果沒資料 if (dt.Rows.Count <= 0) { JArray error_array = new JArray(); JObject obj = new JObject(); obj.Add("Range", "此範圍無法申請"); obj.Add("ClsName", ""); error_array.Add(obj); //丟回前端 context.Response.Write(JsonConvert.SerializeObject(error_array)); return; } string start_time = dt.Rows[0]["PB29002"].ToString(); string class_code = dt.Rows[0]["PB29005"].ToString(); string class_time = dt.Rows[0]["PA20003"].ToString(); JArray result = new JArray(); //裁切當中不同的資料,存成一個 陣列 foreach (DataRow i in dt.Rows) { //假如目前讀取的班別 代碼 跟現在不同 就執行 if (i["PB29005"].ToString() != class_code) { int last_time = dt.Rows.IndexOf(i) - 1; JObject obj = new JObject(); obj.Add("Range", DateTime.Parse(start_time).ToString("yyyy/MM/dd") + "~" + DateTime.Parse(dt.Rows[last_time]["PB29002"].ToString()).ToString("yyyy/MM/dd")); obj.Add("ClsName", dt.Rows[last_time]["PA20003"].ToString()); result.Add(obj); start_time = i["PB29002"].ToString(); //將開始時間設定目前時間 class_code = i["PB29005"].ToString(); //將 TimeCode 設定目前的 TimeCode } //將最後一個丟進去 陣列 if (dt.Rows.IndexOf(i) == dt.Rows.Count - 1) { JObject obj = new JObject(); obj.Add("Range", DateTime.Parse(start_time).ToString("yyyy/MM/dd") + "~" + DateTime.Parse(i["PB29002"].ToString()).ToString("yyyy/MM/dd")); obj.Add("ClsName", i["PA20003"].ToString()); result.Add(obj); break; } } //丟回前端 context.Response.Write(JsonConvert.SerializeObject(result)); }
/** 事假 **/ /** 特休累計 **/ public void ninehr(HttpContext context) { tenten(context); username = context.Request["Alternative_user"] != null ? context.Request["Alternative_user"].ToString().Length == 5 ? context.Request["Alternative_user"].ToString().Substring(1) : context.Request["Alternative_user"].ToString() : username; string sql; //sql = @"select sum(PA86010) as PA86010, sum(PA86011) as PA86011 // from [hrs_mis].[dbo].[WPA86] // where PA86002='" + leftleave.username + @"' // and PA86001='001' // and GETDATE() between PA86007 and PA86008".Replace("\r\n", "").Replace("\t", ""); sql = @"select sum(PA86010) as PA86010, sum(PA86011) as PA86011 from [hrs_mis].[dbo].[WPA86] where PA86001='" + Enterprise.ConStr_produceline + "' AND PA86002='" + username + "' and PA86004='" + DateTime.Now.ToString("yyyy") + "' and PA86007 <= getdate()".Replace("\r\n", "").Replace("\t", ""); //sql = " select sum(PA86010) as PA86010, sum(PA86011) as PA86011 "; //sql += "from [hrs_mis].[dbo].[WPA86] "; //sql += "where PA86002 = '" + leftleave.username + "' and PA86004 = '" + DateTime.Now.ToString("yyyy-MM-dd").Substring(0, 4) + "' "; //sql += "and PA86007 <= '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and PA86001 = '001' "; //不用設定特休的終止日期 可以延用同年的特休假 //sql += "and PA86008 >= '" + DateTime.Now.ToString("yyyy-MM-dd") + "'"; DataTable dt = new DataTable(); dt = BOSSQL.hrsExecuteQuery(sql); //if (dt.Rows[0][0].ToString() != "") //{ // int pa8610 = Convert.ToInt16(dt.Rows[0][0].ToString()); // int pa8611 = Convert.ToInt16(dt.Rows[0][1].ToString()); // int min = pa8610 - pa8611; // if (min == 0) // { // sql = " select PA86010, PA86011, PA86007 "; // sql += "from [hrs_mis].[dbo].[WPA86] "; // sql += "where PA86002 = '" + leftleave.username + "' and PA86004 = '" + DateTime.Now.ToString("yyyy-MM-dd").Substring(0, 4) + "' "; // sql += "and PA86007 > '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and PA86001 = '001' order by PA86007 "; // dt = BOSSQL.hrsExecuteQuery(sql); // } //} //else //{ // sql = " select PA86010, PA86011, PA86007 "; // sql += "from [hrs_mis].[dbo].[WPA86] "; // sql += "where PA86002 = '" + leftleave.username + "' and PA86004 = '" + DateTime.Now.ToString("yyyy-MM-dd").Substring(0, 4) + "' "; // sql += "and PA86007 > '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and PA86001 = '001' order by PA86007 "; // dt = BOSSQL.hrsExecuteQuery(sql); //} //------------------WPA60 特休時數 + WPA123 特休時數----------------- //dt_wpa123 [請假仿WPA60資料表] - 取得目前特休所有剩餘時數,並和WPA86已使用時數加總 string user = username.TrimStart('0').PadLeft(4, '0'); string sql_wpa123 = @"/*WPA73*/ SELECT * FROM [TBI_HRS].[dbo].[PA123] where PA60001='" + conStr + @"' and PA60004=9 and PA60043 is null and PA60039 not in (0,5) and PA60002='" + user + "'"; sql_wpa123 = sql_wpa123.Replace("\r\n", "").Replace("\t", ""); DataTable dt_wpa123 = new DataTable(); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["tbiHRSConnectionString"].ConnectionString)) { using (SqlCommand comm = new SqlCommand()) { using (SqlDataAdapter adapter = new SqlDataAdapter(comm)) { comm.Connection = conn; comm.CommandText = sql_wpa123; conn.Open(); adapter.Fill(dt_wpa123); } } } float cost_wpa123 = dt_wpa123.Rows.Cast <DataRow>().Sum(z => int.Parse(z["PA60011"].ToString())); //設定 ReadOnly 才可更改資料 dt.Columns["PA86011"].ReadOnly = false; int usedTime = 0; if (dt.Rows[0]["PA86011"] != DBNull.Value) { usedTime = int.Parse(dt.Rows[0]["PA86011"].ToString()); } dt.Rows[0]["PA86011"] = usedTime + cost_wpa123; //將未來啟用時間加入傳回資料項目中 IsFutureHasDate(dt, user); string str_json = JsonConvert.SerializeObject(dt, Formatting.Indented); context.Response.Write(str_json); }