public RolesStruct getStaffRoles(string sID)
{
RolesStruct returnValue = new RolesStruct();
string LimitRoles = "";
ManageDataBase msg = new ManageDataBase();
string[] MembershipStaffRoles = msg.getMembershipStaffRoles(sID);
for (int i = 1; i < MembershipStaffRoles.Length; i++)
{ //i=0=>Roles DB ID
if (MembershipStaffRoles[i] != "0")
{
if (LimitRoles.Length > 0)
{
LimitRoles += " OR ";
}
LimitRoles += " cR.ID=" + MembershipStaffRoles[i];
}
}
if (LimitRoles.Length > 0)
{
LimitRoles = " AND " + LimitRoles;
}
if (sID.Length > 0 && LimitRoles.Length > 0)
{
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT cR.*,ISNULL(cRUnit.CaseManagement,0) as CaseUnit , ISNULL(cRUnit.ListeningManagement,0) as hearingUnit, " +
"ISNULL(cRUnit.TeachingManagement,0) as teachUnit, ISNULL(cRUnit.Payroll,0) as payrollUnit, ISNULL(cRUnit.Attendance,0) as aUnit, " +
"ISNULL(cRUnit.PersonnelManagement,0) as personnelUnit, ISNULL(cRUnit.PropertyApplyManagement,0) as paUnit, " +
"ISNULL(cRUnit.PropertyManagement,0) as propertyUnit, ISNULL(cRUnit.LibraryManagement,0) as LUnit, " +
"ISNULL(cRUnit.ServiceFees,0) as sUnit, ISNULL(cRUnit.CaseTemperature,0) as cBTUnit, ISNULL(cRUnit.TeachersTemperature,0) as tBTUnit, " +
"ISNULL(cRUnit.StationeryManagement,0) as stationeryUnit, ISNULL(cRUnit.RemindeSystem,0) as remindeUnit " +
"FROM Competence_Roles cR LEFT JOIN Competence_Roles_Unit cRUnit ON cR.ID=cRUnit.cRolesID AND cRUnit.isDeleted=0 " +
"WHERE cR.isDeleted=0 " + LimitRoles;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(sID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string[] RolesCompareReturn = this.RolesCompare(returnValue.caseStu, dr["CaseManagement"].ToString(), dr["CaseUnit"].ToString());
returnValue.caseStu[0] = RolesCompareReturn[0];
returnValue.caseStu[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.hearing, dr["ListeningManagement"].ToString(), dr["hearingUnit"].ToString());
returnValue.hearing[0] = RolesCompareReturn[0];
returnValue.hearing[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.teach, dr["TeachingManagement"].ToString(), dr["teachUnit"].ToString());
returnValue.teach[0] = RolesCompareReturn[0];
returnValue.teach[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.salary, dr["Payroll"].ToString(), dr["payrollUnit"].ToString());
returnValue.salary[0] = RolesCompareReturn[0];
returnValue.salary[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.attendance, dr["Attendance"].ToString(), dr["aUnit"].ToString());
returnValue.attendance[0] = RolesCompareReturn[0];
returnValue.attendance[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.personnel, dr["PersonnelManagement"].ToString(), dr["personnelUnit"].ToString());
returnValue.personnel[0] = RolesCompareReturn[0];
returnValue.personnel[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.apply, dr["PropertyApplyManagement"].ToString(), dr["paUnit"].ToString());
returnValue.apply[0] = RolesCompareReturn[0];
returnValue.apply[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.property, dr["PropertyManagement"].ToString(), dr["propertyUnit"].ToString());
returnValue.property[0] = RolesCompareReturn[0];
returnValue.property[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.library, dr["LibraryManagement"].ToString(), dr["LUnit"].ToString());
returnValue.library[0] = RolesCompareReturn[0];
returnValue.library[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.serviceFees, dr["ServiceFees"].ToString(), dr["sUnit"].ToString());
returnValue.serviceFees[0] = RolesCompareReturn[0];
returnValue.serviceFees[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.caseBT, dr["CaseTemperature"].ToString(), dr["cBTUnit"].ToString());
returnValue.caseBT[0] = RolesCompareReturn[0];
returnValue.caseBT[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.teachBT, dr["TeachersTemperature"].ToString(), dr["tBTUnit"].ToString());
returnValue.teachBT[0] = RolesCompareReturn[0];
returnValue.teachBT[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.stationery, dr["StationeryManagement"].ToString(), dr["stationeryUnit"].ToString());
returnValue.stationery[0] = RolesCompareReturn[0];
returnValue.stationery[1] = RolesCompareReturn[1];
RolesCompareReturn = this.RolesCompare(returnValue.remind, dr["RemindeSystem"].ToString(), dr["remindeUnit"].ToString());
returnValue.remind[0] = RolesCompareReturn[0];
returnValue.remind[1] = RolesCompareReturn[1];
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue.checkNo = "-1";
returnValue.errorMsg = e.Message;
}
}
}
return returnValue;
}
public string[] OutPropertyRecordDataBase(string rID, string unit)
{
PropertyDataBase pDB = new PropertyDataBase();
StaffDataBase sDB = new StaffDataBase();
ManageDataBase msg = new ManageDataBase();
string[] MembershipStaffRoles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
string StaffRoles = string.Join(",",MembershipStaffRoles);
int aa = StaffRoles.IndexOf("15");
int aab = StaffRoles.IndexOf("4");
if (StaffRoles.IndexOf("15") > -1 || StaffRoles.IndexOf("4") > -1)
{
return pDB.OutPropertyRecordData(rID, unit);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public string[] SearchStaffDataBaseWorkAllCount(int Year, int Month,int Day)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
DataBase Base = new DataBase();
string SearchStaffCondition = "and (ResignationDate='1900-01-01' or ResignationDate > @ResignationDate ) and AppointmentDate<@SearchDate ";
//List<string> UserFile = getStaffDataName(HttpContext.Current.User.Identity.Name);
//this.personnelFunction();
ManageDataBase msg = new ManageDataBase();
bool hasRoles = false;
string[] Roles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
for (int i = 1; i < Roles.Length; i++)
{
if (Roles[i] == "4" || Roles[i] == "19")
{
hasRoles = true;
}
}
if (!hasRoles) {
SearchStaffCondition += " and staffID = @StffID ";
}
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT COUNT(*) AS QCOUNT FROM StaffDatabase WHERE isDeleted=0 " + SearchStaffCondition;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
//ResignationDate
cmd.Parameters.Add("@ResignationDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction( Year + "-" + (Month>0 ? Month : 1 ) + "-" + (Day>0?Day:1));
cmd.Parameters.Add("@SearchDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(Year + "-" + (Month > 0 ? Month : 12) + "-" + (Day > 0 ? Day : 28));//bug 就算了
cmd.Parameters.Add("@StffID", HttpContext.Current.User.Identity.Name);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
public string ManagePageRoles()
{
string returnValue = "";
ManageDataBase msg = new ManageDataBase();
string[] MembershipStaffRoles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
bool StaffRolesBool = false;
foreach (string item in MembershipStaffRoles)
{
if (item == "4") {
StaffRolesBool = true;
}
}
if (StaffRolesBool)
{
returnValue = "<input type=\"button\" value=\"系統管理頁\" onclick=\"window.location.href='./manage/admin.aspx';\"/>";
}
return returnValue;
}
public List<WorkRecordAll> SearchStaffDataBaseWorkAll(int Year, int Month, int indexpage, int Day)
{
List<WorkRecordAll> returnValue = new List<WorkRecordAll>();
DataBase Base = new DataBase();
// string SearchStaffCondition = "and (a.ResignationDate='1900-01-01' or a.ResignationDate>@SearchDate ) and a.AppointmentDate<@SearchDate";
string SearchStaffCondition = "and (ResignationDate='1900-01-01' or ResignationDate > @ResignationDate ) and AppointmentDate<@SearchDate";
ManageDataBase msg = new ManageDataBase();
bool hasRoles = false;
string[] Roles = msg.getMembershipStaffRoles(HttpContext.Current.User.Identity.Name);
for (int i = 1; i < Roles.Length; i++)
{
if (Roles[i] == "4" || Roles[i] == "19")
{
hasRoles = true;
}
}
if (!hasRoles)
{
SearchStaffCondition += " and a.staffID = @StffID ";
}
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM " +
"( " +
"SELECT ROW_NUMBER() OVER (ORDER BY Unit,staffid asc)AS RowNum, * " +
"FROM " +
"( select a.staffid ,a.staffname,a.id,a.Unit, b.*,c.* from StaffDatabase a " +
" left join ( select StaffID as TID,YearVaction,workadd,WorkMinus from YearVaction where Year = @Year ) c on a.StaffID = c.TID "+
" left join " +
" (" +
" select StaffID as Teacherid , " +
" SUM(case vacationType when 2 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v1', " +
" SUM(case vacationType when 3 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v2', " +
" SUM(case vacationType when 4 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v3', " +
" SUM(case vacationType when 5 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v4', " +
" SUM(case vacationType when 6 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v5', " +
" SUM(case vacationType when 7 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v6', " +
" SUM(case vacationType when 8 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v7', " +
" SUM(case vacationType when 9 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v8', " +
" SUM(case vacationType when 10 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v9', " +
" SUM(case vacationType when 11 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v10', " +
" SUM(case vacationType when 12 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v11', " +
//" SUM(case vacationType when 13 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v12' " +
" SUM(case vacationType when 14 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v14', " +
" SUM(case vacationType when 15 then ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end) else 0 end) as 'v15', " +
" SUM(case vacationType when 13 then ABS(starttime - endtime) /8 else 0 end) as 'v12' " +
//" SUM(case vacationType when 2 then ABS(starttime - endtime) /8 else 0 end) as 'v1', " +
//" SUM(case vacationType when 3 then ABS(starttime - endtime) /8 else 0 end) as 'v2', " +
//" SUM(case vacationType when 4 then ABS(starttime - endtime) /8 else 0 end) as 'v3', " +
//" SUM(case vacationType when 5 then ABS(starttime - endtime) /8 else 0 end) as 'v4', " +
//" SUM(case vacationType when 6 then ABS(starttime - endtime) /8 else 0 end) as 'v5', " +
//" SUM(case vacationType when 7 then ABS(starttime - endtime) /8 else 0 end) as 'v6', " +
//" SUM(case vacationType when 8 then ABS(starttime - endtime) /8 else 0 end) as 'v7', " +
//" SUM(case vacationType when 9 then ABS(starttime - endtime) /8 else 0 end) as 'v8', " +
//" SUM(case vacationType when 10 then ABS(starttime - endtime) /8 else 0 end) as 'v9', " +
//" SUM(case vacationType when 11 then ABS(starttime - endtime) /8 else 0 end) as 'v10', " +
//" SUM(case vacationType when 12 then ABS(starttime - endtime) /8 else 0 end) as 'v11', " +
// " SUM(case vacationType when 13 then ABS(starttime - endtime) /8 else 0 end) as 'v12' " +
" from WorkRecordManage " +
" where 1=1 and Year([date]) = @Year ";
//WHO 改 年月日 (有點爛 待改) ( Case when((starttime <=12 and endtime >=13 ) or (endtime <=12 and starttime >=13 )) then (ABS(starttime - endtime )- 1) /8 else ABS(starttime - endtime) /8 end)
if ( Month > 0 && Day > 0 )
{
sql += " and MONTH([date]) = @Month and Day([date]) = @Day";
}
else if (Month > 0 && Day < 0) {
sql += " and MONTH([date]) = @Month ";
}
sql += " group by StaffID " +
" ) b on a.staffid = b.Teacherid " +
" where a.isDeleted=0 " + SearchStaffCondition +
") as alltable " +
") " +
"AS NewTable ";
sql += " WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage) ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ResignationDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(Year + "-" + (Month > 0 ? Month : 1) + "-" + (Day > 0 ? Day : 1));
cmd.Parameters.Add("@SearchDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(Year + "-" + (Month > 0 ? Month : 12) + "-" + (Day > 0 ? Day : 28));//bug 就算了
cmd.Parameters.Add("@StffID", HttpContext.Current.User.Identity.Name);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@Year", SqlDbType.Int).Value = Year;
cmd.Parameters.Add("@Month", SqlDbType.Int).Value = Month;
cmd.Parameters.Add("@Day", SqlDbType.Int).Value = Day;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
WorkRecordAll addValue = new WorkRecordAll();
addValue.StaffID = dr["StaffID"].ToString();
addValue.StaffName = dr["StaffName"].ToString();
addValue.YearVaction = dr["yearvaction"].ToString();
addValue.WorkAdd = dr["workadd"].ToString();
addValue.WorkMinus = dr["workminus"].ToString();
addValue.V1 = dr["V1"].ToString();
addValue.V2 = dr["V2"].ToString();
addValue.V3 = dr["V3"].ToString();
addValue.V4 = dr["V4"].ToString();
addValue.V5 = dr["V5"].ToString();
addValue.V6 = dr["V6"].ToString();
addValue.V7 = dr["V7"].ToString();
addValue.V8 = dr["V8"].ToString();
addValue.V9 = dr["V9"].ToString();
addValue.V10 = dr["V10"].ToString();
addValue.V11 = dr["V11"].ToString();
addValue.V12 = dr["V12"].ToString();
addValue.V14 = dr["V14"].ToString();
addValue.V15 = dr["V15"].ToString();
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
string Ex = e.Message.ToString();
//StaffDataList addValue = new StaffDataList();
//addValue.checkNo = "-1";
//addValue.errorMsg = e.Message.ToString();
//returnValue.Add(addValue);
}
}
return returnValue;
}