/** 事假 **/
private DataTable twotable()
{
tenten(context);
string sql;
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;
sql = "SELECT sum(pa60011)/60 as pa60011 ";
sql += "FROM [hrs_mis].[dbo].[WPA60] ";
sql += "where PA60001='" + Enterprise.ConStr_produceline + "' AND PA60004 = '2' and PA60002 = '" + username + "' and PA60006 like '%" + DateTime.Now.ToString("yyyy") + "%'";
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(hrs))
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(ds);
}
//TBI_HRS 的事假時數總和
sql = @"SELECT SUM(PA60011) total_
FROM[TBI_HRS].[dbo].[PA123]
WHERE PA60001 = '" + Enterprise.ConStr_produceline + @"'
AND pa60043 is null
AND PA60002 = '" + username + @"'
AND PA60004 = '2' /* 2 = 事假 */
AND PA60039 in(1,2,3,4)
";
int sum1 = 0;
int sum2 = 0;
DataTable temptable1 = BOSSQL.TBIHRSExecuteQuery(sql);
//TBI_HRS 時數
if (temptable1.Rows[0]["total_"] != DBNull.Value)
{
sum1 = int.Parse(temptable1.Rows[0]["total_"].ToString());
}
//HRS_MIS 時數
if (ds.Tables[0].Rows[0]["PA60011"] != DBNull.Value)
{
sum2 = int.Parse(ds.Tables[0].Rows[0]["PA60011"].ToString());
}
//合併
ds.Tables[0].Rows[0]["PA60011"] = (sum1 + sum2) / 60;
return(ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable());
}
public void deathtwice(HttpContext context)
{
tenten(context);
username = context.Request["Alternative_user"] != null ? context.Request["Alternative_user"].ToString() : username;
string y = now.Substring(0, 4);
string o = now.Substring(4, 2);
string p = now.Substring(6, 2);
string z = DateTime.Parse(now.Substring(0, 4) + "-" + now.Substring(4, 2) + "-" + now.Substring(6, 2)).ToString("yyyy-MM-dd HH:mm:ss.fff");
DateTime a = DateTime.Parse(z);
SqlConnection conn = new SqlConnection(tbiHRS);
conn.Open();
string sql2 = "select top 1 type,theEnd from TBI_HRS.dbo.HRS08 where username = '******' and timeleft != '0' order by ID desc";
DataTable db = BOSSQL.TBIHRSExecuteQuery(sql2);
if (db.Rows.Count > 0) //判斷HRS08是否有剩餘時數，有的話代表還有喪假的週期尚未結束
{
DateTime b = DateTime.Parse(db.Rows[0][1].ToString()); //第一百天
if (a < b) //登入日小於第一百天，才進行選項上鎖
{
string type = db.Rows[0][0].ToString().Trim();
context.Response.Write(type);
}
}
else
{
string sql = "select pa60004,pa60043,pa60044,pa60047 from [TBI_HRS].[dbo].[PA123] ";
sql += "where pa60002 = '" + username + "' and pa60004 = '8' and pa60039 != '0' and pa60039 != '5' and pa60043 is null and pa60044 is not null ";
sql += "and pa60007 > '" + MonthEarly + "'";
DataTable dt = BOSSQL.TBIHRSExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
DateTime b = DateTime.Parse(dt.Rows[0][3].ToString());
DateTime c = b.AddDays(100);
if (a < c)//登入日小於第一百天，才進行選項上鎖
{
string pa60044 = dt.Rows[0][2].ToString();
context.Response.Write(pa60044);
}
}
}
}
public void DeathDateType(HttpContext context)
{
tenten(context);
string user = context.Request["user"].ToString();
int hrsum = Convert.ToInt32(context.Request["hrsum"]);
string death = context.Request["death"].ToString().Trim();
string Dstart = context.Request["Dstart"].ToString();
string sql, sql2;
string z = DateTime.Parse(now.Substring(0, 4) + "-" + now.Substring(4, 2) + "-" + now.Substring(6, 2)).ToString("yyyy-MM-dd HH:mm:ss.fff");
DateTime a = DateTime.Parse(z);//登入日
//判斷該天數別是否還有剩餘時數&百日
sql = "select top 1 * from [TBI_HRS].[dbo].[HRS08] where username = '******' and type = '" + death + "' order by ID desc";
DataTable db = BOSSQL.TBIHRSExecuteQuery(sql);
if (db.Rows.Count > 0)
{
string timeleft = db.Rows[0][5].ToString().Trim();//剩餘時數
int timeleftNumber = Convert.ToInt32(timeleft);
string b = db.Rows[0][4].ToString();
DateTime theEnd = DateTime.Parse(b); //第一百天
DateTime start = DateTime.Parse(Dstart);
if (a > theEnd) //如果登入日期大於百日，一律新增
{
context.Response.Write("HRS insert");
}
else
{
if (timeleft == "0")//給予新週期(insert)
{
context.Response.Write("HRS insert");
}
//else if (start > theEnd)//百日判斷
//{
// context.Response.Write("100");
//}
else//扣舊週期(update)
{
if (hrsum > timeleftNumber && timeleft != "0")//請假時數大於剩餘時數
{
context.Response.Write("igt lefttime");//is greater than
}
else
{
context.Response.Write("HRS update");
}
}
}
}
else
{
sql2 = "select top 1 pa60002,pa60003,pa60004,pa60043,pa60044,pa60047 from [TBI_HRS].[dbo].[PA123] where pa60002 = '" + user + "' ";
sql2 += "and pa60004 = '8' and pa60043 is null and PA60039 != '5' and pa60044 like '" + death + "%' ";
sql2 += "and pa60007 > '" + MonthEarly + "' order by pa60007 desc";
DataTable dc = BOSSQL.TBIHRSExecuteQuery(sql2);
string x = DateTime.Parse(dc.Rows[0][5].ToString()).AddDays(100).ToString("yyyy-MM-dd 23:59:59.000");
DateTime b = DateTime.Parse(x); //第一百天
if (a > b) //如果
{
context.Response.Write("HRS insert");
}
else
{
string pa6044 = dc.Rows[0][4].ToString().Substring(0, 1); //天數別
int pa6044TypeAll = Convert.ToInt32(pa6044) * 8 * 60; //所選天數別總時數
if (hrsum > pa6044TypeAll) //選取總數超過他所選的天數總數
{
context.Response.Write("hrsum");
}
else //沒有剩餘時數 == 給予時數
{
context.Response.Write("HRS insert");
}
}
}
}
public void DeathIgtupdate(HttpContext context)
{
tenten(context);
string sql1, sql2;
string type = context.Request["type"].ToString();
string water = context.Request["water"].ToString();
double hr = Convert.ToInt32(context.Request["hr"]);
string z = DateTime.Parse(now.Substring(0, 4) + "-" + now.Substring(4, 2) + "-" + now.Substring(6, 2)).ToString("yyyy-MM-dd HH:mm:ss.fff");
DateTime a = DateTime.Parse(z);
int typetime = Convert.ToInt32(type) * 8; //天數別總時數
if (hr > typetime) //單筆大於天數別總時數
{
context.Response.Write("igt");
}
else
{
sql2 = "select top 1 timeleft,theEnd from TBI_HRS.dbo.HRS08 where username = '******' and type = '" + type + "' and timeleft != '0' order by ID desc";
DataTable db = BOSSQL.TBIHRSExecuteQuery(sql2);
if (db.Rows.Count > 0)//比較單筆時數是否超過剩餘時數(抓HRS08)
{
DateTime b = DateTime.Parse(db.Rows[0][1].ToString());
if (a < b)//登入日小於喪假第一百天，才進行剩餘時數防呆
{
sql1 = "select sum(PA60011)as pa60011 from TBI_HRS.dbo.PA123 ";
sql1 += "where pa60002 = '" + username + "' and pa60003 != '" + water + "' and pa60004 = '8' and pa60039 != '0' and pa60039 != '5' and pa60044 = '" + type + "' and pa60043 is null ";
sql1 += "and pa60007 > '" + MonthEarly + "'";
DataTable dc = BOSSQL.TBIHRSExecuteQuery(sql1);
if (dc.Rows[0][0].ToString() != "")
{
double timeleft = Convert.ToInt32(db.Rows[0][0]) / 60 - Convert.ToInt32(dc.Rows[0][0]) / 60;
if (timeleft == 0)
{
context.Response.Write("typeover");
}
else if (hr > timeleft)
{
context.Response.Write("timeleft over1");
}
else
{
context.Response.Write("true");
}
}
else
{
if (hr > Convert.ToInt32(db.Rows[0][0]))
{
context.Response.Write("timeleft over1");
}
else
{
context.Response.Write("true");
}
}
}
else
{
context.Response.Write("true");
}
}
else
{
sql1 = "select sum(PA60011)as pa60011 from TBI_HRS.dbo.PA123 ";
sql1 += "where pa60002 = '" + username + "' and pa60003 != '" + water + "' and pa60004 = '8' and pa60039 != '0' and pa60039 != '5' and pa60044 = '" + type + "' and pa60043 is null ";
sql1 += "and pa60007 > '" + MonthEarly + "'";
DataTable dc = BOSSQL.TBIHRSExecuteQuery(sql1);
if (dc.Rows[0][0].ToString() != "")
{
double timeleft2 = typetime - Convert.ToInt32(dc.Rows[0][0]) / 60; //目前可請時數
if (timeleft2 == 0) //如果待審中的喪假已經到達該天數別的上限
{
context.Response.Write("typeover");
}
else if (hr > timeleft2)//該假單時數大於目前剩餘可請時數
{
context.Response.Write("timeleft over2");
}
else
{
context.Response.Write("true");
}
}
else
{
context.Response.Write("true");
}
}
}
}
public void DeathIgt(HttpContext context)
{
tenten(context);
string sql1, sql2;
string type = context.Request["type"].ToString();
double hr = Convert.ToDouble(context.Request["hr"]);
string z = context.Request["from"]; // DateTime.Parse(process.now.Substring(0, 4) + "-" + process.now.Substring(4, 2) + "-" + process.now.Substring(6, 2)).ToString("yyyy-MM-dd HH:mm:ss.fff");
DateTime a = DateTime.Parse(z);
int typetime = Convert.ToInt32(type) * 8; //天數別總時數
string Hundred = context.Request["Hundred"];
if (hr > typetime)//單筆大於天數別總時數
{
context.Response.Write("igt");
}
else
{
sql2 = "select top 1 timeleft,theEnd from TBI_HRS.dbo.HRS08 where username = '******' and type = '" + type + "' and timeleft != '0' order by ID desc";
DataTable db = BOSSQL.TBIHRSExecuteQuery(sql2);
if (db.Rows.Count > 0)//比較單筆時數是否超過剩餘時數(抓HRS08)
{
DateTime b = DateTime.Parse(db.Rows[0][1].ToString());
if (a < b)//登入日小於喪假第一百天，才進行剩餘時數防呆
{
sql1 = "select sum(PA60011)as pa60011 from TBI_HRS.dbo.PA123 ";
sql1 += "where pa60002 = '" + username + "' and pa60004 = '8' and pa60039 != '0' and pa60039 != '5' and pa60044 = '" + type + "' and pa60043 is null ";
sql1 += "and pa60007 >'" + MonthEarly + "'";
DataTable dc = BOSSQL.TBIHRSExecuteQuery(sql1);
if (dc.Rows[0][0].ToString() != "")
{
double timeleft = Convert.ToInt32(db.Rows[0][0]) / 60 - Convert.ToInt32(dc.Rows[0][0]) / 60;
if (timeleft == 0)
{
context.Response.Write("typeover");
}
else if (hr > timeleft)
{
context.Response.Write("timeleft over1");
}
else
{
context.Response.Write("true");
}
}
else
{
if (hr > Convert.ToInt32(db.Rows[0][0]))
{
context.Response.Write("timeleft over1");
}
else
{
context.Response.Write("true");
}
}
}
else
{
context.Response.Write("true");
}
}
else
{
// 前端訃聞日期沒有取得時
if (Hundred.ToUpper() == "NULL")
{
string sql_Death_GetLastestDate = @"SELECT top 1 SUM(pa60011) 目前使用時數,(PA60044*8*60)總時數,PA60047,pa60044
FROM [TBI_HRS].[dbo].[PA123]
where PA60002='" + username + @"'
and PA60001='" + Enterprise.ConStr_produceline + @"'
and PA60043 is null
group by PA60047,pa60044
having SUM(pa60011)<=(PA60044*8*60)
order by pa60047";
sql_Death_GetLastestDate = sql_Death_GetLastestDate.Replace("\r\n", "").Replace("\t", "");
DataTable dt_Death_GetLastestDate = BOSSQL.TBIHRSExecuteQuery(sql_Death_GetLastestDate);
Hundred = dt_Death_GetLastestDate.Rows[0]["PA60047"].ToString();
}
// 請假日期 超過 訃聞日期 100 日 就返回錯誤(超過100日錯誤)
if (a > DateTime.Parse(Hundred).AddDays(100))
{
context.Response.Write("hundredover");
context.Response.End();
}
sql1 = "select sum(PA60011)as pa60011 from TBI_HRS.dbo.PA123 ";
sql1 += "where pa60002 = '" + username + "' and pa60004 = '8' and pa60039 != '0' and pa60039 != '5' and pa60044 = '" + type + "' and pa60043 is null ";
sql1 += "and pa60007 > '" + MonthEarly + "'";
DataTable dc = BOSSQL.TBIHRSExecuteQuery(sql1);
if (dc.Rows[0][0].ToString() != "")
{
double timeleft2 = typetime - Convert.ToInt32(dc.Rows[0][0]) / 60; //目前可請時數
if (timeleft2 == 0) //如果待審中的喪假已經到達該天數別的上限
{
context.Response.Write("typeover");
}
else if (hr > timeleft2)//該假單時數大於目前剩餘可請時數
{
context.Response.Write("timeleft over2");
}
else
{
context.Response.Write("true");
}
}
else
{
context.Response.Write("true");
}
}
}
}
public void eightupdate(HttpContext context)
{
tenten(context);
string sql1, sql2;
string type = context.Request["type"].ToString();
string water = context.Request["water"].ToString();
int typetime = Convert.ToInt32(type) * 8;
string z = DateTime.Parse(now.Substring(0, 4) + "-" + now.Substring(4, 2) + "-" + now.Substring(6, 2)).ToString("yyyy-MM-dd HH:mm:ss.fff");
DateTime a = DateTime.Parse(z);
sql1 = "select top 1 PA60044 from TBI_HRS.dbo.PA123 ";
sql1 += "where pa60002 = '" + username + "' and pa60003 != '" + water + "' and pa60004 = '8' and pa60039 != '0' and pa60039 != '5' and pa60044 = '" + type + "' ";
sql1 += "and pa60043 is null and pa60007 > '" + MonthEarly + "' order by pa60007 desc";
DataTable dt = BOSSQL.TBIHRSExecuteQuery(sql1);
if (dt.Rows.Count > 0)//判斷是否有待審中的假單
{
sql1 = "select sum(PA60011)as pa60011 from TBI_HRS.dbo.PA123 ";
sql1 += "where pa60002 = '" + username + "' and pa60003 != '" + water + "' and pa60004 = '8' and pa60039 != '0' and pa60039 != '5' ";
sql1 += "and pa60044 = '" + type + "' and pa60043 is null and pa60007 > '" + MonthEarly + "'";
DataTable dc = BOSSQL.TBIHRSExecuteQuery(sql1);//將待審中的假單的時數加總起來
sql2 = "select top 1 timeleft,theEnd from TBI_HRS.dbo.HRS08 where username = '******' and type = '" + type + "' and timeleft != '0' order by ID desc";
DataTable db = BOSSQL.TBIHRSExecuteQuery(sql2);
if (db.Rows.Count > 0) //判斷HRS08是否有剩餘時數
{
DateTime b = DateTime.Parse(db.Rows[0][1].ToString()); //第一百天
if (a > b) //登入日超過第一百天，結束這次喪假週期
{
context.Response.Write("false");
}
else
{
double timeleft = Convert.ToInt32(db.Rows[0][0]) / 60 - Convert.ToInt32(dc.Rows[0][0]) / 60;//HRS08有剩餘時數的話，可請時數：HRS08-PA123待審假單總時數
context.Response.Write(timeleft);
}
}
else
{
double timeleft2 = typetime - Convert.ToInt32(dc.Rows[0][0]) / 60;//HRS08沒有剩餘時數的話，可請時數：天數別總時數-PA123待審假單總時數
context.Response.Write(timeleft2);
}
}
else
{
sql2 = "select top 1 timeleft,theEnd from TBI_HRS.dbo.HRS08 where username = '******' and type = '" + type + "' and timeleft != '0' order by ID desc";
DataTable db = BOSSQL.TBIHRSExecuteQuery(sql2);
if (db.Rows.Count > 0)//判斷HRS08是否有剩餘時數
{
double timeleft3 = Convert.ToInt32(db.Rows[0][0]) / 60;
DateTime b = DateTime.Parse(db.Rows[0][1].ToString()); //第一百天
if (a > b) //登入日超過第一百天，結束這次喪假週期
{
context.Response.Write("false");
}
else
{
if (timeleft3 != 0)
{
context.Response.Write(timeleft3);
}
else
{
context.Response.Write("false");
}
}
}
else
{
context.Response.Write("false");
}
}
}