//列表
        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));
        }
Beispiel #6
0
        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));
        }
Beispiel #8
0
        //列表
        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));
        }
Beispiel #10
0
        //列表
        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));
        }
Beispiel #15
0
        //列表
        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));
        }
Beispiel #16
0
        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));
        }
Beispiel #18
0
        /// <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));
        }
Beispiel #19
0
        //列表
        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));
        }
Beispiel #20
0
        //列表
        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));
        }
Beispiel #21
0
        //列表
        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));
        }
Beispiel #22
0
        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);
        }
Beispiel #24
0
        //列表
        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));
        }