/** 取得目前此段時間屬於哪一個班表 **/
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);
}
