//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL = @"SELECT * FROM BbsContent bbs {0}";
string strConditions = string.Empty;
Conditions conditionsDic = null;
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
//conditionsDic = new Conditions() { { "@queryText", String.Format("{0}", slParms.QueryText.Trim()) } };
strConditions = String.Format("where bbs.bbs_title like '%" + slParms.QueryText.Trim() + "%' ");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"bbs_id", "bbs_title", "bbs_content", "bbs_http", "bbs_photo", "bbs_file", "startdatetime", "enddatetime", "Creator", "CreateDate", "Modifier", "ModifyDate",
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL = @"SELECT * FROM SignProcedure {0}";
string strConditions = string.Empty;
Conditions conditionsDic = null;
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic = new Conditions()
{
{ "@queryText", String.Format("{0}%", slParms.QueryText.Trim()) }
};
strConditions = String.Format("where SignID like @queryText or SignLevel like @queryText or MaxLevel like @queryText");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"SignID", "SignLevel", "MaxLevel", "Disabled", "DisabledDate", "Creator", "CreateDate", "Modifier", "ModifyDate"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string DataSource = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"];
string strSQL = String.Format(
@"SELECT C.CODENAME DeptName,B.CostDepartmentID DeptID, B.EmployeeID, B.EmployeeName, A.PAYYYYYMM, A.OVERWORK1, A.OVERWORK2, A.OVERWORK3, A.OVERWORK4, A.OVERWORK5, A.RECREATEDAYS,
A.OFFWORK1, A.OFFWORK2,
(SELECT CASE COUNT(*) WHEN 0 THEN 0 ELSE SUM(C.OFFWORK3) END AS EXPR2
FROM {0}.dbo.DAILYOFF AS C
WHERE (B.EmployeeID = EMPLOYECD) AND (PAYYYYYMM = A.PAYYYYYMM)) AS OFFWORK3, A.OFFWORK5M, A.OFFWORK9, A.OFFWORK6M, A.OFFHOURS,
ISNULL(A.ADDHOURS,0) as ADDHOURS, A.OVERWORKHOURS, A.OFFWORKHOURS, A.LOSTTIMES, A.OffWork14, A.MEALDELAY,
(SELECT CASE COUNT(*) WHEN 0 THEN 0 ELSE SUM(C.MEALDELAY2) END AS Expr1
FROM {0}.dbo.DAILYON AS C
WHERE (B.EmployeeID = EMPLOYECD) AND (PAYYYYYMM = A.PAYYYYYMM)) AS mealdelay2,ISNULL(E.ADDOFFHOURS,0) as ADDOFFHOURS
FROM {0}.dbo.MONTHLYONOFF AS A
INNER JOIN dbo.Employee AS B ON A.EMPLOYECD = B.EmployeeID
LEFT OUTER JOIN {0}.dbo.CODEDTL AS C ON B.CostDepartmentID=C.CODECD and C.TYPECD = 'UNIT'
LEFT OUTER JOIN {0}.dbo.EMPLOYEE AS E ON A.EMPLOYECD = E.EMPLOYECD AND A.PAYYYYYMM =
(SELECT PAYYYYYMM
FROM (SELECT TOP (1) STR(SYSTEMDATE) + SYSTEMTIME AS DAYTIME, (CASE RIGHT(PAYYYYYMM, 2)
WHEN 1 THEN PAYYYYYMM ELSE (PAYYYYYMM - 1) END) AS PAYYYYYMM
FROM {0}.dbo.RUNSALARY
WHERE (RUNTYPE = 'RUNM')
ORDER BY DAYTIME DESC) AS AA)
WHERE (B.Disabled ='false') AND (LEFT(A.PAYYYYYMM, 4) BETWEEN CAST(YEAR(GETDATE()) - 1 AS char) AND CAST(YEAR(GETDATE()) + 1 AS char)) and B.EmployeeID=@EmployeeID ", DataSource);
var conditionsDic = new Conditions()
{
{ "@EmployeeID", slParms.EmployeeID_FK }
};
int PageCount = 0;
if (slParms.PageName == "Default")
{
PageCount = 3;
}
else
{
PageCount = 24;
}
var paginationParms = new PaginationParms()
{
QueryString = strSQL,
QueryConditions = conditionsDic,
//PageIndex = pParms.PageIndex,
PageSize = PageCount
};
//pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "PAYYYYYMM" : pParms.OrderField;
//string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
string orderExpression = String.Format("{0}{1}", "-", "PAYYYYYMM");
var allowColumns = new List <string>
{
"DeptName", "DeptID", "EmployeeID", "EmployeeName", "PAYYYYYMM", "OVERWORK1", "OVERWORK2", "OVERWORK3", "OVERWORK4", "OVERWORK5", "RECREATEDAYS", "OFFWORK1", "OFFWORK2", "OFFWORK3", "OFFWORK5M", "OFFWORK9", "OFFWORK6M", "OFFHOURS", "ADDHOURS", "OVERWORKHOURS", "OFFWORKHOURS", "LOSTTIMES", "OffWork14", "MEALDELAY", "mealdelay2", "ADDOFFHOURS"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT distinct
CASE
WHEN signdocid_fk <> '' THEN signdocid_fk
ELSE detailsigndocid_fk
END AS SignDocID_FK,
smain.FormID_FK,
formtype,
smain.EmployeeID_FK EmployeeID_FK,
emp.employeename employeename,
departmentname,
smain.FinalStatus FinalStatus
FROM signform_log slog
LEFT OUTER JOIN SignForm_Main smain
ON slog.detailsigndocid_fk=smain.SignDocID
LEFT OUTER JOIN signtype type
ON smain.formid_fk = type.formid
LEFT OUTER JOIN employee emp
ON smain.EmployeeID_FK = emp.employeeid
LEFT OUTER JOIN department dept
ON emp.DepartmentID_FK = dept.departmentid
LEFT OUTER join Employee chief
ON slog.ChiefID_FK = chief.EmployeeID
{0}";
string strConditions = "where (slog.Status='3' or slog.Status='4') and chief.ADAccount=@ADAccount ";
//and chief.ADAccount=@ADAccount
var conditionsDic = new Conditions()
{
{ "@ADAccount", slParms.Member.ADAccount }
};
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic.Add("@queryText", String.Format("{0}%", slParms.QueryText.Trim()));
strConditions += String.Format(" and smain.EmployeeID_FK like @queryText or emp.EmployeeName like @queryText or CASE WHEN signdocid_fk <> '' THEN signdocid_fk ELSE detailsigndocid_fk END like @queryText and (slog.Status='3' or slog.Status='4') ");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
//string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
string orderExpression = "SignDocID_FK";
var allowColumns = new List <string>
{
"SignDocID_FK", "formtype", "EmployeeID_FK", "EmployeeName", "departmentname", "FinalStatus"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL = @"SELECT * FROM BbsContent bbs {0}";
string strConditions = string.Empty;
Conditions conditionsDic = null;
//組成查詢條件 SQL
//if (!String.IsNullOrWhiteSpace(slParms.QueryText))
//{
// //conditionsDic = new Conditions() { { "@queryText", String.Format("{0}", slParms.QueryText.Trim()) } };
// strConditions = String.Format("where bbs.bbs_title like '%" + slParms.QueryText.Trim() + "%' ");
//}
//依照公佈開始時間至結束時間篩選
strConditions = String.Format("where Convert(varchar(10),Getdate(),112) between Convert(varchar(10),startdatetime,112) and Convert(varchar(10),enddatetime,112) ");
string orderExpression = String.Format("{0}{1}", "-", "StartDateTime");
//string orderExpression = "startdatetime";
//string strOrder = " order by startdatetime Desc";
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = 5
};
var allowColumns = new List <string>
{
"bbs_id", "bbs_title", "bbs_content", "bbs_http", "bbs_photo", "bbs_file", "startdatetime", "enddatetime", "Creator", "CreateDate", "Modifier", "ModifyDate",
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
public virtual void ConstructPage(SignListParms slParms, PaggerParms pParms, ISignRepository repo)
{
var pagination = repo.GetPagination(slParms, pParms);
if (pagination == null)
{
return;
}
if (0 == pagination.TotalItems)
{
slParms.NoDataTip.Visible = true; slParms.NoDataTip.Text = "查無資料";
}
//設定 gridView Source
ViewUtils.SetGridView(slParms.GridView, pagination.Data);
slParms.TotalRowsCount.Text = pagination.TotalItems.ToString();
if (slParms.PageName == "Daily_Result")
{
lbl_Count.Text = pagination.TotalItems.ToString();
}
//Pagination Bar Generator
string paginationHtml = WebUtils.GetPagerNumericString(pagination, Request);
slParms.PaginationBar.InnerHtml = paginationHtml;
}
public Pagination GetOvertimeListPagination(SignListParms slParms, PaggerParms pParms)
{
var strSQL = getOvertimeFormSQL();
//組排序SQL
string strOrder = " order by DepartmentID Asc ";
string strConditions = " where SignDocID_FK = @SignDocID_FK";
var orgData = _rootRepo.QueryForOvertimeFormDataBySignDocID(slParms.SignDocID);
if (orgData == null)
{
return(null);
}
var dutyDate = (DateTime?)orgData.FirstOrDefault()["StartDateTime"];
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, String.Concat(RepositoryFactory.SmartManConn["DataSource"], ".", RepositoryFactory.SmartManConn["Catelog"]), strConditions, strOrder),
QueryConditions = new Conditions()
{
{ "@DutyDate", dutyDate.HasValue ? dutyDate.Value.ToString("yyyyMMdd") : (string)null }, { "@SignDocID_FK", slParms.SignDocID }
},
PageIndex = pParms.PageIndex,
PageSize = 5
};
return(_dc.QueryForPagination(paginationParms));
}
//列表
public Pagination GetPagination(ManageListParms mlParms, PaggerParms pParms)
{
//組出SQL查詢語句
string strSQL =
@"select distinct * from (select TOP 18000 * from ( SELECT overtime.employeeid_fk,
emp.employeename,
right(overtime.supportdeptid_fk,4) supportdeptid_fk,
dept.departmentname,
overtime.startdatetime,
overtime.enddatetime,
overtime.paytypekey,
overtime.mealorderkey,
emp.nationaltype
FROM overtimeform overtime
LEFT OUTER JOIN employee emp
ON overtime.employeeid_fk = emp.employeeid
LEFT OUTER JOIN department dept
ON overtime.supportdeptid_fk = dept.departmentid
LEFT OUTER JOIN SignForm_Main Sign_M
ON overtime.SignDocID_FK=Sign_M.SignDocID
{0} ) as aa) as overtime
";
//設定Where條件
string strConditions = String.Format(
@"WHERE overtime.startdatetime >= @StartDatetime
AND overtime.enddatetime < @EndDatetime and Sign_M.FinalStatus <>'5' and emp.CostDepartmentID not like'39%' ");
//設定參數話查詢欄位
var conditionsDic = new Conditions()
{
{ "@StartDatetime", mlParms.StartDateTime.HasValue ? mlParms.StartDateTime.Value : UTC_START_DATETIME },
{ "@EndDatetime", mlParms.EndDateTime.HasValue ? mlParms.EndDateTime.Value : DateTime.Now }
};
//設定資料所需參數(PaginationParms為資料查詢模型)
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "NationalType" : pParms.OrderField;
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"EmployeeID_FK", "EmployeeName", "SupportDeptID_FK", "DepartmentName", "StartDatetime", "EndDatetime", "PayTypeKey", "MealOrderKey", "NationalType"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//明細列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT
otf.SN SN,
otf.FormID_FK FormID,
otf.SignDocID_FK SignDocID,
otf.ApplyID_FK ApplyID,
otf.ApplyDateTime ApplyDateTime,
otf.EmployeeID_FK EmployeeID,
emp.EmployeeName EmployeeName,
dept.DepartmentID DepartmentID,
dept.DepartmentName DepartmentName,
otf.StartDateTime StartDateTime,
otf.EndDateTime EndDateTime,
supdept.DepartmentID SupportDeptID,
supdept.DepartmentName SupportDeptName,
otf.PayTypeKey PayTypeKey,
otf.MealOrderKey MealOrderKey,
otf.Note Note,
emp.NationalType NationalType
FROM overtimeform otf
LEFT OUTER JOIN employee emp
ON otf.employeeid_fk = emp.employeeid
LEFT OUTER JOIN department dept
ON otf.departmentid_fk = dept.departmentid
LEFT OUTER JOIN department supDept
ON otf.supportdeptid_fk = supdept.departmentid {0} {1}";
//組排序SQL
pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "sn" : pParms.OrderField;
string strOrder = String.Format(" order by {0} {1}", pParms.OrderField, pParms.Descending ? "Desc" : "Asc");
string strConditions = " where SignDocID_FK = @SignDocID_FK";
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions, strOrder),
QueryConditions = new Conditions()
{
{ "@SignDocID_FK", slParms.SignDocID }
},
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT sn,
CASE
WHEN signdocid_fk <> '' THEN signdocid_fk
ELSE detailsigndocid_fk
END AS SignDocID_FK,
formtype,
senddate,
departmentname,
finalstatus,
employeename,
remark,
status,
logdatetime
FROM signform_log slog
LEFT OUTER JOIN signtype type
ON slog.formid_fk = type.formid
LEFT OUTER JOIN department dept
ON slog.currentsignleveldeptid_fk = dept.departmentid
LEFT OUTER JOIN employee emp
ON slog.chiefid_fk = emp.employeeid
{0}";
string strConditions = " where slog.SignDocID_FK = @SignDocID_FK or slog.DetailSignDocID_FK = @SignDocID_FK ";
var employeeData = _rootRepo.QueryForEmployeeByADAccount(slParms.Member.ADAccount);
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = new Conditions()
{
{ "@SignDocID_FK", slParms.SignDocID },
},
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "sn" : pParms.OrderField;
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"FormType", "SendDate", "DepartmentName", "FinalStatus", "EmployeeName", "Remark", "Status", "LogDatetime", "sn"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
/// <summary>
/// 分頁列表順帶資料
/// </summary>
/// <param name="slParms"></param>
/// <param name="pParms"></param>
/// <returns></returns>
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT emp.*,
dep.departmentname AS DepartmentName,
emp2.employeename AS AgentName
FROM employee emp
LEFT OUTER JOIN department dep
ON emp.departmentid_fk = dep.departmentid
LEFT OUTER JOIN employee emp2
ON emp.agentid = emp2.employeeid
{0}";
string strConditions = string.Empty;
Conditions conditionsDic = null;
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic = new Conditions()
{
{ "@queryText", String.Format("{0}%", slParms.QueryText.Trim()) }
};
strConditions = String.Format("where emp.EmployeeID like @queryText or emp.EmployeeName like @queryText or emp.DepartmentID_FK like @queryText");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"EmployeeID", "EmployeeName", "DepartmentID_FK", "DepartmentName", "AgentID", "AgentName", "Disabled", "DisabledDate",
"ADAccount", "Creator", "CreateDate", "Modifier", "ModifyDate"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"select distinct signM.*,emp.EmployeeName EmployeeName, typ.FormType FormType, dept.DepartmentName CurrentSignLevelDeptName, dept.ChiefID_FK, chief.ADAccount chiefADAccount, signD.Status
from SignForm_Main signM
left outer join SignForm_Detail signD on signM.SignDocID = signD.SignDocID_FK
left outer join Employee emp on signM.EmployeeID_FK = emp.EmployeeID
left outer join SignType typ on signM.FormID_FK = typ.FormID
left outer join Department dept on signM.CurrentSignLevelDeptID_FK = dept.DepartmentID
left outer join Employee chief on signD.ChiefID_FK = chief.EmployeeID
{0}";
string strConditions = " where chief.ADAccount = @ADAccount and signD.Status = 2 ";
var conditionsDic = new Conditions()
{
{ "@ADAccount", slParms.Member.ADAccount }
};
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic.Add("@queryText", String.Format("{0}%", slParms.QueryText.Trim()));
//20170221 修正查詢條件(避免查詢到未送簽)
strConditions += String.Format(" and (EmployeeID_FK like @queryText or emp.EmployeeName like @queryText or SignDocID like @queryText)");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"SignDocID", "FormType", "EmployeeID_FK", "EmployeeName", "SendDate", "CurrentSignLevelDeptID_FK",
"CurrentSignLevelDeptName", "FinalStatus", "Creator", "CreateDate", "Modifier", "ModifyDate"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT dpt.*, emp.EmployeeName as ChiefName, emp2.EmployeeName as FilingEmployeeName, dpt2.DepartmentName as upperDepartmentName FROM Department dpt
left outer join Employee emp on dpt.chiefID_FK = emp.EmployeeID
left outer join Employee emp2 on dpt.FilingEmployeeID_FK = emp2.EmployeeID
left outer join Department dpt2 on dpt.upperDepartmentID = dpt2.DepartmentID
{0}";
string strConditions = string.Empty;
Conditions conditionsDic = null;
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic = new Conditions()
{
{ "@queryText", String.Format("{0}%", slParms.QueryText.Trim()) }
};
strConditions = String.Format("where dpt.DepartmentID like @queryText or dpt.DepartmentName like @queryText or emp.EmployeeName like @queryText");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"DepartmentID", "DepartmentName", "ChiefID_FK", "ChiefName", "UpperDepartmentID", "UpperDepartmentName", "DepartmentLevel", "FilingEmployeeID_FK",
"FilingEmployeeName", "Disabled", "DisabledDate", "Creator", "CreateDate", "Modifier", "ModifyDate",
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(ManageListParms mlParms, PaggerParms pParms)
{
string tableName = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"] + ".dbo.DailyOnOff";
//20170223 修改convert (nvarchar, startDateTime, 112) = onoff.RealOnDate 為 convert (nvarchar, startDateTime, 112) = onoff.DUTYDATE
//怕志元沒寫值進RealOnDate欄位
string strSQL = String.Format(
@"SELECT overtime.*, onoff.*, emp.CostDepartmentID FROM overtimeform overtime
left outer join {0} onoff
on overtime.employeeID_FK = onoff.employecd
left outer join employee emp
on overtime.employeeID_FK = emp.employeeID
where autoinsert = 'False' and signDocID_FK = @SignDocID_FK and convert (nvarchar, startDateTime, 112) = onoff.DUTYDATE
and convert (nvarchar, startDateTime, 112) = onoff.DUTYDATE ", tableName);
var conditionsDic = new Conditions()
{
{ "@SignDocID_FK", mlParms.SignDocID },
};
var overtimeList = _rootRepo.QueryForOvertimeFormDataBySignDocID(mlParms.SignDocID);
var paginationParms = new PaginationParms()
{
QueryString = strSQL,
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = overtimeList.Count
};
pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "SignDocID_FK" : pParms.OrderField;
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"SignDocID_FK", "EmployeeID_FK", "ApplyID_FK", "ApplyDateTime", "EmployeeID_FK", "DepartmentID_FK", "StartDateTime", "EndDateTime", "SupportDeptID_FK", "PayTypeKey", "MealOrderKey", "AutoInsert", "IsHoliday", "TotalHours", "Note"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT typ.*,
dpt.departmentname AS FilingDepartmentName
FROM signtype typ
LEFT OUTER JOIN department dpt
ON typ.filingdepartmentid_fk = dpt.departmentid
{0}";
string strConditions = string.Empty;
Conditions conditionsDic = null;
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic = new Conditions()
{
{ "@queryText", String.Format("{0}%", slParms.QueryText.Trim()) }
};
strConditions = String.Format("where FormType like @queryText or FormID like @queryText or SignID_FK like @queryText");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"FormID", "FormType", "SignID_FK", "FilingDepartmentID_FK", "Creator", "CreateDate", "Modifier", "ModifyDate"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
public virtual void ConstructPage(ManageListParms mlParm, PaggerParms pParms, IManageRepository repo)
{
var pagination = repo.GetPagination(mlParm, pParms);
if (pagination == null)
{
return;
}
if (0 == pagination.TotalItems)
{
mlParm.NoDataTip.Visible = true; mlParm.NoDataTip.Text = "查無資料";
}
//設定 gridView Source
ViewUtils.SetGridView(mlParm.GridView, pagination.Data);
mlParm.TotalRowsCount.Text = pagination.TotalItems.ToString();
//Pagination Bar Generator
string paginationHtml = WebUtils.GetPagerNumericString(pagination, Request);
mlParm.PaginationBar.InnerHtml = paginationHtml;
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT agt.*,emp.employeeName FROM SignAgent agt
left outer join Employee emp
on agt.EmployeeID_FK = emp.EmployeeID
{0}";
string strConditions = string.Empty;
Conditions conditionsDic = null;
//組成查詢條件 SQL
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic = new Conditions()
{
{ "@queryText", String.Format("{0}%", slParms.QueryText.Trim()) }
};
strConditions = String.Format("where agt.EmployeeID_FK like @queryText or agt.SN like @queryText");
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"SN", "EmployeeID_FK", "EmployeeName", "BeginDate", "EndDate", "Creator", "CreateDate", "Modifier", "ModifyDate",
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
/// <summary>
/// 建構頁面處理常式
/// </summary>
/// <param name="mlParm"></param>
/// <param name="pParms"></param>
/// <param name="repo"></param>
public void ConstructPage(ManageListParms mlParm, PaggerParms pParms, MealTaxiRepository repo)
{
//分頁資訊(含資料) 資料型別DataTable
var pagination = repo.GetPagination(mlParm, pParms);
#region 0007 加班餐車資料報表依照加班單位代碼小排到大 by 小遇
//重新排序
if (pagination.Data != null)
{
IEnumerable <DataRow> dataRows = pagination.Data.Rows.Cast <DataRow>().OrderBy(row => row["supportdeptid_fk"]);
pagination.Data = dataRows.CopyToDataTable();
}
#endregion 0007 加班餐車資料報表依照加班單位代碼小排到大 by 小遇
if (pagination == null)
{
return;
}
if (0 == pagination.TotalItems)
{
mlParm.NoDataTip.Visible = true; mlParm.NoDataTip.Text = "查無資料";
}
//設定 gridView Source 將分頁資訊裡的資料繫結到GridView
ViewUtils.SetGridView(mlParm.GridView, pagination.Data);
mlParm.TotalRowsCount.Text = pagination.TotalItems.ToString();
//Pagination Bar Generator
string paginationHtml = WebUtils.GetPagerNumericString(pagination, Request);
mlParm.PaginationBar.InnerHtml = paginationHtml;
//設定 Meal gridView Source
ViewUtils.SetGridView(mlParm.MealSummary, repo.GetMealSummary(mlParm));
//設定 Taxi gridView Source
ViewUtils.SetGridView(mlParm.TaxiSummary, repo.GetTaxiSummary(mlParm));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string DataSource = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"];
string strSQL = String.Format(
@"SELECT EMPLOYECD, YEAR, COUNTBEGDATE, COUNTENDDATE, RECREATEDAYS, INCREASEDAYS, LASTYEARDAYS,
USEDDAYS, RECREATEDAYS + INCREASEDAYS + LASTYEARDAYS -
(SELECT ISNULL(SUM(RECREATEDAYS), 0) AS Expr1
FROM {0}.dbo.DAILYOFF AS D
WHERE (EMPLOYECD = R.EMPLOYECD) AND (DUTYDATE BETWEEN R.COUNTBEGDATE AND R.COUNTENDDATE)) AS USD
FROM {0}.dbo.RECREATEDAY AS R
WHERE YEAR >= YEAR(GETDATE()) and EMPLOYECD=@EMPLOYECD ", DataSource);
var conditionsDic = new Conditions()
{
{ "@EMPLOYECD", slParms.EmployeeID_FK }
};
var paginationParms = new PaginationParms()
{
QueryString = strSQL,
QueryConditions = conditionsDic,
//PageIndex = pParms.PageIndex,
PageSize = 2
};
//pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "PAYYYYYMM" : pParms.OrderField;
//string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
string orderExpression = String.Format("{0}{1}", "", "YEAR");
var allowColumns = new List <string>
{
"EMPLOYECD", "YEAR", "COUNTBEGDATE", "COUNTENDDATE", "RECREATEDAYS", "USEDDAYS", "USD"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"select distinct signM.*,signD.ChiefID_FK ChiefID_Up,emp.EmployeeName EmployeeName, typ.FormID FormID, typ.FormType FormType, dept.DepartmentName CurrentSignLevelDeptName, dept.ChiefID_FK, chief.ADAccount chiefADAccount, signD.Status
from SignForm_Main signM
left outer join SignForm_Detail signD on signM.SignDocID = signD.SignDocID_FK
left outer join Employee emp on signM.EmployeeID_FK = emp.EmployeeID
left outer join SignType typ on signM.FormID_FK = typ.FormID
left outer join Department dept on signM.CurrentSignLevelDeptID_FK = dept.DepartmentID
left outer join Employee chief on dept.ChiefID_FK = chief.EmployeeID
{0}";
var conditionsDic = new Conditions()
{
{ "@queryText", String.Format("{0}%", slParms.QueryText.Trim()) }
};
string strConditions = " where FinalStatus<>'5' and SignDocID like @queryText ";
//if (parms.Member.IsChief)
//{
// //組成查詢條件 SQL
// if (!String.IsNullOrWhiteSpace(parms.QueryText))
// {
// strConditions += String.Format(" or EmployeeID_FK like @queryText or emp.EmployeeName like @queryText");
// }
//}
//else
//{
conditionsDic.Add("@EmployeeID_FK", slParms.Member.EmployeeID);
strConditions += String.Format(" and EmployeeID_FK = @EmployeeID_FK");
if (!String.IsNullOrWhiteSpace(slParms.FinalStatus))
{
conditionsDic.Add("@FinalStatus", slParms.FinalStatus);
strConditions += String.Format(" and FinalStatus = @FinalStatus");
}
else
{
strConditions += " And finalStatus <> '6'";
}
//conditionsDic.Add("@FinalStatus", slParms.FinalStatus);
//strConditions += String.Format(" and FinalStatus = @FinalStatus");
//}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"SignDocID", "FormType", "EmployeeID_FK", "EmployeeName", "SendDate", "CurrentSignLevelDeptID_FK", "CurrentSignLevelDeptName", "FinalStatus", "Status", "CreateDate"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
string strSQL =
@"SELECT
UU.qno qno,
UU.codename codename,
C.description description
FROM (SELECT R.clid,
FS.sid,
R.table_id,
FS.signdocid,
F.serial_no,
F.qno,
Q.anstype,
Q.codename
FROM form_records R
JOIN form_format F
ON ( R.table_id = F.table_id )
AND ( R.edition_id = F.edition_id )
JOIN questions Q
ON F.qno = Q.qno AND Q.qno <> 44
JOIN form_sign FS
ON ( R.table_id = FS.table_id )
WHERE R.table_id = '02') UU
JOIN character_answer C
ON UU.clid = C.clid
AND UU.sid = C.sid
AND UU.qno = C.qno
WHERE 1 = 1 {0}";
var paginationParms = new PaginationParms()
{
QueryString = strSQL,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
if (!String.IsNullOrWhiteSpace(slParms.EmployeeID_FK))
{
paginationParms.QueryConditions.Add("@SID", slParms.EmployeeID_FK);
paginationParms.QueryString = String.Format(paginationParms.QueryString, "AND C.sid = @SID {0}");
}
if (!String.IsNullOrWhiteSpace(slParms.CLID))
{
paginationParms.QueryConditions.Add("@CLID", slParms.CLID);
paginationParms.QueryString = String.Format(paginationParms.QueryString, "AND UU.clid = @CLID {0}");
}
if (!String.IsNullOrWhiteSpace(slParms.SignDocID))
{
paginationParms.QueryConditions.Add("@SignDocID", slParms.SignDocID);
paginationParms.QueryString = String.Format(paginationParms.QueryString, "AND UU.SignDocID = @SignDocID");
}
paginationParms.QueryString = String.Format(paginationParms.QueryString, "");
pParms.OrderField = "qno".Equals(pParms.OrderField) ? "qno" : pParms.OrderField;
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string> {
"qno", "codename", "description",
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
public static void ParseQueryStringTest(HttpRequest request, PaggerParms expect)
{
var actual = WebUtils.ParseQueryString <PaggerParms>(request);
Assert.Equal(expect, actual, new ParmsCompare <PaggerParms>());
}
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
//string strCatalog = RepositoryFactory.PotalConn["Catalog"];
string strSQL = "";
switch (slParms.TABLE_ID)
{
case "01":
strSQL = @"SELECT distinct
class.clid CLID,
class.clname CLNAME,
class.start_date STARTDATE,
class.hours HOURS,
stu.sid SID,
stu.sname SNAME,
class.unitid UNITID,
class.unitname UNITNAME,
[sign].signdocid SIGNDOCID
FROM rtclass class
JOIN students stu
ON class.clid = stu.clid
LEFT OUTER JOIN (select distinct table_id from FORM_FORMAT)as F on 1=1
LEFT OUTER JOIN (select CLID,SID,TABLE_ID,count(*) as A_COUNT from(
select CLID, SID, TABLE_ID, QNO, SERIAL_NO, DESCRIPTION ANS, EDIT_DATE, EDIT_PERSON from CHARACTER_ANSWER
union
select CLID, SID, TABLE_ID, QNO, SERIAL_NO,CONVERT(varchar, ANS) ANS, EDIT_DATE, EDIT_PERSON from NUMERIC_ANSWER) A
group by CLID,SID,TABLE_ID) as count_ans on count_ans.CLID=class.CLID and count_ans.SID=stu.SID and count_ans.TABLE_ID=f.TABLE_ID
LEFT OUTER JOIN form_sign [sign]
ON [sign].clid = stu.clid and [sign].TABLE_ID=F.table_id and stu.sid=[sign].SID
WHERE class.CLID NOT LIKE '9999%' and ((F.TABLE_ID=@TABLE_ID AND [sign].issigned = 'False' ) or (F.TABLE_ID=@TABLE_ID AND [sign].issigned is null )) and ISNULL(count_ans.A_COUNT,0)=0
{0}" ;
break;
case "02":
string strCate = RepositoryFactory.PotalConn["Catelog"];
strSQL = String.Format(@"SELECT distinct
class.clid CLID,
class.clname CLNAME,
class.start_date STARTDATE,
class.hours HOURS,
stu.sid SID,
stu.sname SNAME,
class.unitid UNITID,
class.unitname UNITNAME,
[sign].signdocid SIGNDOCID
FROM rtclass class
JOIN students stu
ON class.clid = stu.clid
LEFT OUTER JOIN (select distinct table_id from FORM_FORMAT)as F on 1=1
LEFT OUTER JOIN (select CLID,SID,TABLE_ID,count(*) as A_COUNT from(
select CLID, SID, TABLE_ID, QNO, SERIAL_NO, DESCRIPTION ANS, EDIT_DATE, EDIT_PERSON from CHARACTER_ANSWER
union
select CLID, SID, TABLE_ID, QNO, SERIAL_NO,CONVERT(varchar, ANS) ANS, EDIT_DATE, EDIT_PERSON from NUMERIC_ANSWER) A
group by CLID,SID,TABLE_ID) as count_ans on count_ans.CLID=class.CLID and count_ans.SID=stu.SID and count_ans.TABLE_ID=f.TABLE_ID
LEFT OUTER JOIN form_sign [sign]
ON [sign].clid = stu.clid and [sign].TABLE_ID=F.table_id and stu.sid=[sign].SID
left OUTER join {0}..SignForm_Main Sign_M ON Sign_M.SignDocID=[sign].SignDocID
WHERE class.CLID NOT LIKE '9999%' and ((F.TABLE_ID=@TABLE_ID AND [sign].issigned = 'False' ) or (F.TABLE_ID=@TABLE_ID AND [sign].issigned is null )) and ISNULL(Sign_M.FinalStatus,0) not in (2,3,4,6) and ISNULL(count_ans.A_COUNT,0)=0", strCate);
strSQL = strSQL + "{0}";
break;
case "03":
string strCatelog = RepositoryFactory.PotalConn["Catelog"];
strSQL = String.Format(@"SELECT distinct
class.clid CLID,
class.clname CLNAME,
class.start_date STARTDATE,
class.hours HOURS,
stu.sid SID,
stu.sname SNAME,
class.unitid UNITID,
class.unitname UNITNAME,
[sign].signdocid SIGNDOCID
FROM rtclass class
JOIN students stu
ON class.clid = stu.clid
JOIN {0}..Employee Emp
ON stu.sid = Emp.EmployeeID
JOIN {0}..Department Dept
ON Emp.DepartmentID_FK = Dept.DepartmentID
LEFT OUTER JOIN (select distinct table_id from FORM_FORMAT)as F on 1=1
LEFT OUTER JOIN (select CLID,SID,TABLE_ID,count(*) as A_COUNT from(
select CLID, SID, TABLE_ID, QNO, SERIAL_NO, DESCRIPTION ANS, EDIT_DATE, EDIT_PERSON from CHARACTER_ANSWER
union
select CLID, SID, TABLE_ID, QNO, SERIAL_NO,CONVERT(varchar, ANS) ANS, EDIT_DATE, EDIT_PERSON from NUMERIC_ANSWER) A
group by CLID,SID,TABLE_ID) as count_ans on count_ans.CLID=class.CLID and count_ans.SID=stu.SID and count_ans.TABLE_ID=f.TABLE_ID
LEFT OUTER JOIN form_sign [sign]
ON [sign].clid = stu.clid and [sign].TABLE_ID=F.table_id and stu.sid=[sign].SID
WHERE class.CLID NOT LIKE '9999%' and ((F.TABLE_ID=@TABLE_ID AND [sign].issigned = 'False' ) or (F.TABLE_ID=@TABLE_ID AND [sign].issigned is null )) and ISNULL(count_ans.A_COUNT,0)=0", strCatelog);
strSQL = strSQL + "{0}";
break;
default:
throw new Exception("無此問卷代號!");
}
#region 備份SQL
// if (slParms.TABLE_ID=="02")
// {
// strSQL = @"SELECT distinct
// class.clid CLID,
// class.clname CLNAME,
// class.start_date STARTDATE,
// class.hours HOURS,
// stu.sid SID,
// stu.sname SNAME,
// class.unitid UNITID,
// class.unitname UNITNAME,
// [sign].signdocid SIGNDOCID
// FROM rtclass class
// JOIN students stu
// ON class.clid = stu.clid
// LEFT OUTER JOIN (select distinct table_id from FORM_FORMAT)as F on 1=1
// LEFT OUTER JOIN form_sign [sign]
// ON [sign].clid = stu.clid and [sign].TABLE_ID=F.table_id and stu.sid=[sign].SID
// left OUTER join RinnaiPortal..SignForm_Main Sign_M ON Sign_M.SignDocID=[sign].SignDocID
// WHERE class.CLID NOT LIKE '9999%' and ((F.TABLE_ID=@TABLE_ID AND [sign].issigned = 'False' ) or (F.TABLE_ID=@TABLE_ID AND [sign].issigned is null )) and ISNULL(Sign_M.FinalStatus,0) not in (2,3)
// {0}";
// }
// else if (slParms.TABLE_ID == "03")
// {
// string strCatelog = RepositoryFactory.PotalConn["Catelog"];
// strSQL = String.Format(@"SELECT distinct
// class.clid CLID,
// class.clname CLNAME,
// class.start_date STARTDATE,
// class.hours HOURS,
// stu.sid SID,
// stu.sname SNAME,
// class.unitid UNITID,
// class.unitname UNITNAME,
// [sign].signdocid SIGNDOCID
// FROM rtclass class
// JOIN students stu
// ON class.clid = stu.clid
// JOIN {0}..Employee Emp
// ON stu.sid = Emp.EmployeeID
// JOIN {0}..Department Dept
// ON Emp.DepartmentID_FK = Dept.DepartmentID
// LEFT OUTER JOIN (select distinct table_id from FORM_FORMAT)as F on 1=1
// LEFT OUTER JOIN form_sign [sign]
// ON [sign].clid = stu.clid and [sign].TABLE_ID=F.table_id and stu.sid=[sign].SID
// WHERE class.CLID NOT LIKE '9999%' and ((F.TABLE_ID=@TABLE_ID AND [sign].issigned = 'False' ) or (F.TABLE_ID=@TABLE_ID AND [sign].issigned is null ))", strCatelog);
// strSQL = strSQL + "{0}";
// }
// else
// {
// strSQL = @"SELECT distinct
// class.clid CLID,
// class.clname CLNAME,
// class.start_date STARTDATE,
// class.hours HOURS,
// stu.sid SID,
// stu.sname SNAME,
// class.unitid UNITID,
// class.unitname UNITNAME,
// [sign].signdocid SIGNDOCID
// FROM rtclass class
// JOIN students stu
// ON class.clid = stu.clid
// LEFT OUTER JOIN (select distinct table_id from FORM_FORMAT)as F on 1=1
// LEFT OUTER JOIN form_sign [sign]
// ON [sign].clid = stu.clid and [sign].TABLE_ID=F.table_id and stu.sid=[sign].SID
// WHERE class.CLID NOT LIKE '9999%' and ((F.TABLE_ID=@TABLE_ID AND [sign].issigned = 'False' ) or (F.TABLE_ID=@TABLE_ID AND [sign].issigned is null ))
// {0}";
// }
#endregion 備份SQL
string strConditions = string.Empty;
Conditions conditionsDic = new Conditions()
{
//{ "@SID", slParms.Member.EmployeeID },
{ "@TABLE_ID", slParms.TABLE_ID }
};
//組成查詢條件 SQL
if (slParms.TABLE_ID == "03")
{
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic.Add("@queryText", String.Format("{0}", slParms.QueryText.Trim()));
strConditions = String.Format("AND (class.CLID=@queryText OR stu.sid = @queryText)");
//conditionsDic.Add("@ChiefID", String.Format("{0}", slParms.Member.EmployeeID.Trim()));
//strConditions += String.Format("AND Dept.ChiefID_FK = @ChiefID");
}
//else
//{
conditionsDic.Add("@ChiefID", String.Format("{0}", slParms.Member.EmployeeID.Trim()));
strConditions += String.Format("AND Dept.ChiefID_FK = @ChiefID");
//}
}
else
{
if (!String.IsNullOrWhiteSpace(slParms.QueryText))
{
conditionsDic.Add("@queryText", String.Format("{0}", slParms.QueryText.Trim()));
strConditions = String.Format("AND (class.CLID=@queryText OR stu.sid = @queryText)");
}
else
{
conditionsDic.Add("@SID", String.Format("{0}", slParms.Member.EmployeeID.Trim()));
strConditions = String.Format("AND stu.sid = @SID");
}
}
//#0001 2017-07-19 by 俊晨 開課日期 < 2017/7/1的資料因不簽核故不顯示(經理指示)。
//#0019 受訓心得報告不顯示B10，C01，A09 的課程代碼
//#0023 主管成效評核追縱維護新增時不顯示B10 ，C01，A09 的課程代碼 by 淑娟 原slParms.TABLE_ID == "02"增加slParms.TABLE_ID == "03"
strSQL += " and class.START_DATE >= '2017-07-01' ";
if (slParms.TABLE_ID == "02" || slParms.TABLE_ID == "03")
{
strSQL += " and class.CTID not in ('B10','C01','A09') ";
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"CLID", "CLNAME", "STARTDATE", "HOURS", "SID", "SNAME", "UNITID", "UNITNAME", "SIGNDOCID",
};
Pagination resultPagination = _dc.QueryForPagination(paginationParms, orderExpression, allowColumns);
#region #0010 增加判斷評核表不該出現受訓心得未填寫之學員
//增加判斷未填寫受訓心得不該出現在評核列表上
if (slParms.TABLE_ID == "03")
{
Dictionary <string, string> delDataIdentify = new Dictionary <string, string>();
int beforePageSize = paginationParms.PageSize;
paginationParms.PageIndex = 1;
paginationParms.PageSize = 99999;
Pagination getResult = _dc.QueryForPagination(paginationParms, orderExpression, allowColumns);
if (resultPagination.Data != null)
{
var filterData = getResult.Data.Rows.Cast <DataRow>().ToList();
string queryHasFeedbackSQL = @"
SELECT distinct
class.clid CLID,
class.clname CLNAME,
class.start_date STARTDATE,
class.hours HOURS,
stu.sid SID,
stu.sname SNAME,
class.unitid UNITID,
class.unitname UNITNAME,
[sign].signdocid SIGNDOCID
FROM rtclass class
JOIN students stu
ON class.clid = stu.clid
LEFT OUTER JOIN (select distinct table_id from FORM_FORMAT)as F on 1=1
LEFT OUTER JOIN (select CLID,SID,TABLE_ID,count(*) as A_COUNT from(
select CLID, SID, TABLE_ID, QNO, SERIAL_NO, DESCRIPTION ANS, EDIT_DATE, EDIT_PERSON from CHARACTER_ANSWER
union
select CLID, SID, TABLE_ID, QNO, SERIAL_NO,CONVERT(varchar, ANS) ANS, EDIT_DATE, EDIT_PERSON from NUMERIC_ANSWER) A
group by CLID,SID,TABLE_ID) as count_ans on count_ans.CLID=class.CLID and count_ans.SID=stu.SID and count_ans.TABLE_ID=f.TABLE_ID
LEFT OUTER JOIN form_sign [sign]
ON [sign].clid = stu.clid and [sign].TABLE_ID=F.table_id and stu.sid=[sign].SID
left OUTER join RinnaiPortal_Formal..SignForm_Main Sign_M ON Sign_M.SignDocID=[sign].SignDocID
WHERE class.CLID NOT LIKE '9999%' and ((F.TABLE_ID=@TABLE_ID AND [sign].issigned = 'False' ) or (F.TABLE_ID=@TABLE_ID AND [sign].issigned is null )) and ISNULL(Sign_M.FinalStatus,0) not in (2,3,4,6) and ISNULL(count_ans.A_COUNT,0)=0AND stu.sid = @SID and class.START_DATE >= '2017-07-01'
AND class.CLID = @CLID
";
foreach (DataRow data in filterData)
{
string studentID = data["SID"].ToString();
string classID = data["CLID"].ToString();
Conditions conditionsDicForQueryHasAns = new Conditions();
conditionsDicForQueryHasAns.Add("@TABLE_ID", "02");
conditionsDicForQueryHasAns.Add("@SID", String.Format("{0}", studentID));
conditionsDicForQueryHasAns.Add("@CLID", String.Format("{0}", classID));
//var paginationParmsForQueryHasAns = new PaginationParms()
//{
// QueryString = StudyOpinionFeedbackSQL,
// QueryConditions = conditionsDicForQueryHasAns,
// PageIndex = pParms.PageIndex,
// PageSize = pParms.PageSize
//};
DataRow result = _dc.QueryForDataRow(queryHasFeedbackSQL, conditionsDicForQueryHasAns);
//!= null為受訓心得尚未填寫 不可呈現於列表
if (result != null)
{
delDataIdentify[classID] = studentID;
}
}
//刪除未填寫受訓心得的條件
foreach (var di in delDataIdentify)
{
try
{
var query = getResult.Data.AsEnumerable().Where(r => r.Field <string>("CLID") == di.Key && r.Field <string>("SID") == di.Value);
foreach (var row in query.ToList())
{
getResult.Data.Rows.Remove(row);
}
}
catch (Exception ex)
{
throw ex;
}
}
if (getResult.Data.Rows.Count > 0)
{
int startRow = (pParms.PageIndex - 1) * beforePageSize;
Pagination pagination = new Pagination(getResult.Data, getResult.Data.Rows.Count, pParms.PageIndex);
pagination.Data = pagination.Data.Rows.Cast <DataRow>().Skip(startRow).Take(beforePageSize).CopyToDataTable();
resultPagination = pagination;
}
else
{
resultPagination.Data = null;
}
}
}
#endregion #0010 增加判斷評核表不該出現受訓心得未填寫之學員
//根據 SQL取得 Pagination
return(resultPagination);
}
//列表
public Pagination GetPagination(SignListParms slParms, PaggerParms pParms)
{
//string DataSource = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"] ;
string strSQL = String.Format(
@"SELECT * from (SELECT AA.EMPLOYECD AS USERID, AA.DUTYDATE, MAX(AA.PAYYYYYMM) AS PAYYYYYMM, ISNULL(MAX(AA.BEGINTIME),'') AS BeginTime, ISNULL(MAX(AA.ENDTIME),'') AS EndTime,
SUM(AA.OverWorkHours) AS OverWorkHours, SUM(AA.RecreateDays) AS RecreateDays, SUM(AA.OffWork1) AS OffWork1, SUM(AA.OffWork2) AS OffWork2,
SUM(AA.OFFWORK3) AS OffWork3, SUM(AA.OffHours) AS OffHours, SUM(AA.OffWork5M) AS OffWork5M, SUM(AA.OffWork6M) AS OffWork6M, SUM(AA.OffWork8)
AS OffWork8, SUM(AA.OffWork9) AS OffWork9, SUM(AA.OffWorkHours) AS OffWorkHours, SUM(AA.OffWork14) AS OffWork14, SUM(AA.MEALDELAY) AS MealDelay,
SUM(AA.LOSTTIMES) AS LostTimes, SUM(AA.AddHours) AS AddHours, SUM(AA.OverWork1) AS OverWork1, SUM(AA.OverWork2) AS OverWork2, SUM(AA.OverWork3)
AS OverWork3, SUM(AA.OverWork4) AS OverWork4, MAX(Department.CODENAME) AS Department_ID,
ISNULL(MAX(AA.RemarkOff),'') AS RemarkOff
FROM (SELECT DO.EMPLOYECD, DO.DUTYDATE, DO.PAYYYYYMM, DO.BEGINTIME, DO.ENDTIME, 0 AS OverWorkHours, 0 AS RecreateDays, 0 AS OffWork1,
0 AS OffWork2, CASE WHEN DO.DUTYDATE = CAST(CONVERT(varchar, getdate(), 112) AS DECIMAL) THEN 0 ELSE F.OffWork3 END AS OFFWORK3,
0 AS OffHours, F.OffWork5M, F.OffWork6M, 0 AS OffWork8, 0 AS OffWork9, 0 AS OffWorkHours, 0 AS OffWork14, DO.MEALDELAY,
CASE WHEN DO.DUTYDATE = CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DECIMAL) THEN 0 ELSE DO.LOSTTIMES END AS LOSTTIMES, 0 AS AddHours,
0 AS OverWork1, 0 AS OverWork2, 0 AS OverWork3, 0 AS OverWork4, DO.RemarkOff
FROM ITEIP.HRIS.dbo.DAILYONOFF AS DO INNER JOIN
ITEIP.HRIS.dbo.EMPLOYEE AS EM ON EM.EMPLOYECD = DO.EMPLOYECD AND DO.EMPLOYECD = EM.EMPLOYECD LEFT OUTER JOIN
ITEIP.HRIS.dbo.Dailyonoff_View AS F ON F.Employecd = DO.EMPLOYECD AND F.DutyDate = DO.DUTYDATE
WHERE (LEFT(DO.DUTYDATE, 4) BETWEEN CAST(YEAR(GETDATE()) - 1 AS DECIMAL) AND CAST(YEAR(GETDATE()) + 1 AS DECIMAL)) AND
(EM.EMPLOYECD IN
(SELECT EMPLOYECD
FROM ITEIP.HRIS.dbo.EMPLOYEE AS Sub
WHERE (1 = 1))) AND (DO.COMPANYCD = 'A') AND (EM.HIRETYPE IN ('A', 'B', 'C', 'D', 'E', 'F', ''))
UNION ALL
SELECT EMPLOYECD, DUTYDATE, '' AS PAYYYYYMM, '' AS BeginTime, '' AS EndTime, 0 AS OverWorkHours, ISNULL(SUM(RECREATEDAYS), 0) AS RecreateDays,
ISNULL(SUM(OFFWORK1), 0) AS OffWork1, ISNULL(SUM(OFFWORK2), 0) AS OffWork2, 0 AS OffWork3, ISNULL(SUM(OFFHOURS), 0) AS OffHours,
0 AS Offwork5M, 0 AS Offwork6M, ISNULL(SUM(OFFWORK8), 0) AS OffWork8, ISNULL(SUM(OFFWORK9), 0) AS OffWork9, ISNULL(SUM(OFFWORKHOURS), 0)
AS OffWorkHours, ISNULL(SUM(OffWork14), 0) AS OffWork14, 0 AS MealDelay, 0 AS LostTimes, 0 AS AddHours, 0 AS OverWork1, 0 AS OverWork2,
0 AS OverWork3, 0 AS OverWork4, '' AS REMARKOFF
FROM ITEIP.HRIS.dbo.DAILYOFF
WHERE (LEFT(DUTYDATE, 4) BETWEEN CAST(YEAR(GETDATE()) - 1 AS DECIMAL) AND CAST(YEAR(GETDATE()) + 1 AS DECIMAL))
GROUP BY EMPLOYECD, DUTYDATE
UNION ALL
SELECT EMPLOYECD, DUTYDATE, '' AS PAYYYYYMM, '' AS BeginTime, '' AS EndTime, ISNULL(SUM(OVERWORKHOURS), 0) AS OverWorkHours,
0 AS RecreateDays, 0 AS OffWork1, 0 AS OffWork2, 0 AS OffWork3, 0 AS OffHours, 0 AS Offwork5M, 0 AS Offwork6M, 0 AS OffWork8, 0 AS OffWork9,
0 AS OffWorkHours, 0 AS OffWork14, 0 AS MealDelay, 0 AS LostTimes, ISNULL(SUM(ADDHOURS), 0) AS AddHours, ISNULL(SUM(OVERWORK1), 0)
AS OverWork1, ISNULL(SUM(OVERWORK2), 0) AS OverWork2, ISNULL(SUM(OVERWORK3), 0) AS OverWork3, ISNULL(SUM(OVERWORK4), 0)
AS OverWork4, '' AS REMARKOFF
FROM ITEIP.HRIS.dbo.DAILYON
WHERE (LEFT(DUTYDATE, 4) BETWEEN CAST(YEAR(GETDATE()) - 1 AS DECIMAL) AND CAST(YEAR(GETDATE()) + 1 AS DECIMAL))
GROUP BY EMPLOYECD, DUTYDATE) AS AA
INNER JOIN ITEIP.HRIS.dbo.Employee AS EMPLOYEE ON AA.EMPLOYECD = Employee.EMPLOYECD
INNER JOIN ITEIP.HRIS.dbo.CODEDTL AS Department ON Employee.UNITCD = Department.CODECD and Department.TYPECD = 'UNIT'
WHERE (EMPLOYEE.QUITDATE='0') GROUP BY AA.EMPLOYECD, AA.DUTYDATE ) as Daily
where USERID=@USERID and PAYYYYYMM=@PAYYYYYMM");
var conditionsDic = new Conditions()
{
{ "@USERID", slParms.EmployeeID_FK },
{ "@PAYYYYYMM", slParms.payYYYYMM }
};
int PageCount = 0;
if (slParms.PageName == "Default")
{
PageCount = 6;
}
else
{
PageCount = 40;
}
var paginationParms = new PaginationParms()
{
QueryString = strSQL,
QueryConditions = conditionsDic,
//PageIndex = pParms.PageIndex,
PageSize = PageCount
};
//pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "PAYYYYYMM" : pParms.OrderField;
//string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
string orderExpression = String.Format("{0}{1}", "-", "PAYYYYYMM");
var allowColumns = new List <string>
{
"USERID", "DUTYDATE", "PAYYYYYMM", "BeginTime", "EndTime", "OverWorkHours", "RecreateDays", "OffWork1", "OffWork2", "OffWork3", "OffHours", "OffWork5M", "OffWork6M", "OffWork8", "OffWork9", "OffWorkHours", "OffWork14", "MealDelay", "LostTimes", "AddHours", "OverWork1", "OverWork2", "OverWork3", "OverWork4", "Department_ID", "RemarkOff"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
//列表
public Pagination GetPagination(ManageListParms mlParms, PaggerParms pParms)
{
string strSQL =
@"SELECT main.signdocid SignDocID,
overtime.ApplyID_FK ApplyID,
emp2.employeename Applyname,
overtime.employeeid_fk EmployeeID,
emp.employeename EmployeeName,
main.senddate SendDate,
main.finalstatus FinalStatus,
main.remainder Remainder,
detail.chiefid_fk ChiefID,
chief.employeename ChiefName,
detail.status Status,
overtime.paytypekey PayType,
sup.departmentname SupportDeptName,
overtime.startdatetime StartDateTime,
overtime.enddatetime EndDateTime,
overtime.totalhours TotalHours,
overtime.isholiday IsHoliday,
overtime.autoinsert AutoInsert
FROM signform_main main
LEFT OUTER JOIN signform_detail detail
ON main.signdocid = detail.signdocid_fk
LEFT OUTER JOIN overtimeform overtime
ON main.signdocid = overtime.signdocid_fk
LEFT OUTER JOIN employee emp
ON overtime.employeeid_fk = emp.employeeid
LEFT OUTER JOIN employee emp2
ON overtime.ApplyID_FK = emp2.employeeid
LEFT OUTER JOIN employee chief
ON detail.chiefid_fk = chief.employeeid
LEFT OUTER JOIN department sup
ON overtime.supportdeptid_fk = sup.departmentid
{0}";
string strConditions = String.Format(
@"WHERE overtime.startdatetime >= @StartDatetime
AND overtime.enddatetime < @EndDatetime and finalStatus<>'5'" );
var conditionsDic = new Conditions()
{
{ "@StartDatetime", mlParms.StartDateTime.HasValue ? mlParms.StartDateTime.Value : UTC_START_DATETIME },
{ "@EndDatetime", mlParms.EndDateTime.HasValue ? mlParms.EndDateTime.Value : DateTime.Now },
{ "@FinalStatus", mlParms.FinalStatus }
};
if (!String.IsNullOrWhiteSpace(mlParms.FinalStatus))
{
strConditions += " And finalStatus = @FinalStatus";
}
else
{
strConditions += " And finalStatus <> '6'";
}
var paginationParms = new PaginationParms()
{
QueryString = String.Format(strSQL, strConditions),
QueryConditions = conditionsDic,
PageIndex = pParms.PageIndex,
PageSize = pParms.PageSize
};
pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "SignDocID" : pParms.OrderField;
string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);
var allowColumns = new List <string>
{
"SignDocID", "EmployeeID", "EmployeeName", "SendDate", "FinalStatus", "Remainder", "ChiefID", "ChiefName", "Status", "PayType", "StartDateTime", "EndDateTime", "SupportDeptName", "AutoInsert", "IsHoliday", "TotalHours"
};
//根據 SQL取得 Pagination
return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
}
