Пример #1
0
    protected void Delete_Click(object sender, EventArgs e)
    {
        ETrainingSeminar c = new ETrainingSeminar();

        c.TrainingSeminarID = CurID;
        if (db.select(dbConn, c))
        {
            DBFilter trainingSeminarFilter = new DBFilter();
            trainingSeminarFilter.add(new Match("TrainingSeminarID", CurID));
            EEmpTrainingEnroll.db.delete(dbConn, trainingSeminarFilter);

            WebUtils.StartFunction(Session, FUNCTION_CODE);
            db.delete(dbConn, c);
            WebUtils.EndFunction(dbConn);
        }
        HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "Training_Seminar_List.aspx");
    }
Пример #2
0
    protected bool loadObject()
    {
        obj = new ETrainingSeminar();
        bool isNew = WebFormWorkers.loadKeys(db, obj, DecryptedRequest);

        if (!db.select(dbConn, obj))
        {
            return(false);
        }

        Hashtable values = new Hashtable();

        db.populate(obj, values);
        binding.toControl(values);

        return(true);
    }
    protected void Save_Click(object sender, EventArgs e)
    {
        ETrainingSeminar c = new ETrainingSeminar();

        Hashtable values = new Hashtable();

        binding.toValues(values);

        PageErrors errors = PageErrors.getErrors(db, Page.Master);

        errors.clear();


        db.validate(errors, values);

        if (!errors.isEmpty())
        {
            return;
        }


        db.parse(values, c);

        WebUtils.StartFunction(Session, FUNCTION_CODE);
        if (CurID < 0)
        {
//            Utils.MarkCreate(Session, c);

            db.insert(dbConn, c);
            CurID = c.TrainingSeminarID;
//            url = Utils.BuildURL(-1, CurID);
        }
        else
        {
//            Utils.Mark(Session, c);
            db.update(dbConn, c);
        }

        DBFilter filter = new DBFilter();

        filter.add(new Match("TrainingSeminarID", c.TrainingSeminarID));

        WebUtils.EndFunction(dbConn);

        HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "Training_Seminar_View.aspx?TrainingSeminarID=" + CurID);
    }
Пример #4
0
        public static string GetValueFromID(DatabaseConnection dbConn, string fieldName, string fieldValue)
        {
            try
            {
                string tmpfieldName = fieldName;
                if (tmpfieldName.Equals("PreviousEmpID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("NewEmpID", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "EmpID";
                }
                if (tmpfieldName.EndsWith("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.EndsWith("PayCodeID", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "PaymentCodeID";
                }
                if (tmpfieldName.Equals("DefaultMPFPlanID", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "MPFPlanID";
                }
                if (tmpfieldName.Equals("AttendanceFormulaPayFormID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("ReferencePayFormID", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "PayFormID";
                }
                if (tmpfieldName.StartsWith("LeaveCode", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "PayFormID";
                }
                if (tmpfieldName.StartsWith("PayGroup", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "PayFormID";
                }
                if (tmpfieldName.Equals("CurrentPayPeriodID", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "PayPeriodID";
                }
                if (tmpfieldName.Equals("EmpPosDefaultRosterCodeID", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "RosterCodeID";
                }
                if (tmpfieldName.Equals("RosterClientMappingSiteCodeToHLevelID", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "HLevelID";
                }
                if (tmpfieldName.Equals("EmpFirstAuthorizationGp", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("EmpSecondAuthorizationGp", StringComparison.CurrentCultureIgnoreCase))
                {
                    tmpfieldName = "AuthorizationGroupID";
                }



                if (tmpfieldName.StartsWith("A", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("ALProrataRoundingRuleID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EALProrataRoundingRule obj = new EALProrataRoundingRule();
                        obj.ALProrataRoundingRuleID = int.Parse(fieldValue);
                        if (EALProrataRoundingRule.db.select(dbConn, obj))
                        {
                            return(obj.ALProrataRoundingRuleCode + " - " + obj.ALProrataRoundingRuleDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("AttendancePlanID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EAttendancePlan obj = new EAttendancePlan();
                        obj.AttendancePlanID = int.Parse(fieldValue);
                        if (EAttendancePlan.db.select(dbConn, obj))
                        {
                            return(obj.AttendancePlanCode + " - " + obj.AttendancePlanDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("AuthorizationGroupID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EAuthorizationGroup obj = new EAuthorizationGroup();
                        obj.AuthorizationGroupID = int.Parse(fieldValue);
                        if (EAuthorizationGroup.db.select(dbConn, obj))
                        {
                            return(obj.AuthorizationCode + " - " + obj.AuthorizationDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("AVCPlanID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EAVCPlan obj = new EAVCPlan();
                        obj.AVCPlanID = int.Parse(fieldValue);
                        if (EAVCPlan.db.select(dbConn, obj))
                        {
                            return(obj.AVCPlanCode + " - " + obj.AVCPlanDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("C", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("CessationReasonID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ECessationReason obj = new ECessationReason();
                        obj.CessationReasonID = int.Parse(fieldValue);
                        if (ECessationReason.db.select(dbConn, obj))
                        {
                            return(obj.CessationReasonCode + " - " + obj.CessationReasonDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("CompanyID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ECompany obj = new ECompany();
                        obj.CompanyID = int.Parse(fieldValue);
                        if (ECompany.db.select(dbConn, obj))
                        {
                            return(obj.CompanyCode + " - " + obj.CompanyName);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }


                    if (tmpfieldName.Equals("CostAllocationDetailID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ECostAllocationDetail obj = new ECostAllocationDetail();
                        obj.CostAllocationDetailID = int.Parse(fieldValue);
                        if (ECostAllocationDetail.db.select(dbConn, obj))
                        {
                            return(GetValueFromID(dbConn, "PayCodeID", obj.PaymentCodeID.ToString()) + ", " + GetValueFromID(dbConn, "CostCenterID", obj.CostCenterID.ToString()));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("CostAllocationID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ECostAllocation obj = new ECostAllocation();
                        obj.CostAllocationID = int.Parse(fieldValue);
                        if (ECostAllocation.db.select(dbConn, obj))
                        {
                            return(GetValueFromID(dbConn, "EmpPayrollID", obj.EmpPayrollID.ToString()));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("CostCenterID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ECostCenter obj = new ECostCenter();
                        obj.CostCenterID = int.Parse(fieldValue);
                        if (ECostCenter.db.select(dbConn, obj))
                        {
                            return(obj.CostCenterCode + " - " + obj.CostCenterDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("D", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("DocumentTypeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EDocumentType obj = new EDocumentType();
                        obj.DocumentTypeID = int.Parse(fieldValue);
                        if (EDocumentType.db.select(dbConn, obj))
                        {
                            return(obj.DocumentTypeCode + " - " + obj.DocumentTypeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("E", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("EmpAccID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EEmpBankAccount obj = new EEmpBankAccount();
                        obj.EmpBankAccountID = int.Parse(fieldValue);
                        if (EEmpBankAccount.db.select(dbConn, obj))
                        {
                            return(obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + obj.EmpAccountNo);
                        }
                        //return obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + string.Empty.PadRight(obj.EmpAccountNo.Length, 'X');
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("EmpCostCenterID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EEmpCostCenter obj = new EEmpCostCenter();
                        obj.EmpCostCenterID = int.Parse(fieldValue);
                        if (EEmpCostCenter.db.select(dbConn, obj))
                        {
                            return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString()));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("EmpExtraFieldID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EEmpExtraField obj = new EEmpExtraField();
                        obj.EmpExtraFieldID = int.Parse(fieldValue);
                        if (EEmpExtraField.db.select(dbConn, obj))
                        {
                            return(obj.EmpExtraFieldName);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("EmpID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EEmpPersonalInfo obj = new EEmpPersonalInfo();
                        obj.EmpID = int.Parse(fieldValue);
                        if (EEmpPersonalInfo.db.select(dbConn, obj))
                        {
                            return(obj.EmpNo + " - " + obj.EmpEngFullName);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("EmpPayrollID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EEmpPayroll obj = new EEmpPayroll();
                        obj.EmpPayrollID = int.Parse(fieldValue);
                        if (EEmpPayroll.db.select(dbConn, obj))
                        {
                            return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString()) + " : " + GetValueFromID(dbConn, "PayPeriodID", obj.PayPeriodID.ToString()));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("EmpPosID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EEmpPositionInfo obj = new EEmpPositionInfo();
                        obj.EmpPosID = int.Parse(fieldValue);
                        if (EEmpPositionInfo.db.select(dbConn, obj))
                        {
                            return(obj.EmpPosEffFr.ToString("yyyy-MM-dd") + " - " + (obj.EmpPosEffTo.Ticks.Equals(0) ? "Present" : obj.EmpPosEffTo.ToString("yyyy-MM-dd")));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("EmploymentTypeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EEmploymentType obj = new EEmploymentType();
                        obj.EmploymentTypeID = int.Parse(fieldValue);
                        if (EEmploymentType.db.select(dbConn, obj))
                        {
                            return(obj.EmploymentTypeCode + " - " + obj.EmploymentTypeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("F", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("FunctionID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ESystemFunction obj = new ESystemFunction();
                        obj.FunctionID = int.Parse(fieldValue);
                        if (ESystemFunction.db.select(dbConn, obj))
                        {
                            return(obj.FunctionCode + " - " + obj.Description);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("H", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("HElementID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EHierarchyElement obj = new EHierarchyElement();
                        obj.HElementID = int.Parse(fieldValue);
                        if (EHierarchyElement.db.select(dbConn, obj))
                        {
                            return(obj.HElementCode + " - " + obj.HElementDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("HLevelID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EHierarchyLevel obj = new EHierarchyLevel();
                        obj.HLevelID = int.Parse(fieldValue);
                        if (EHierarchyLevel.db.select(dbConn, obj))
                        {
                            return(obj.HLevelCode + " - " + obj.HLevelDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("L", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("LeaveTypeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ELeaveType obj = new ELeaveType();
                        obj.LeaveTypeID = int.Parse(fieldValue);
                        if (ELeaveType.db.select(dbConn, obj))
                        {
                            return(obj.LeaveType + " - " + obj.LeaveTypeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("LeaveCodeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ELeaveCode obj = new ELeaveCode();
                        obj.LeaveCodeID = int.Parse(fieldValue);
                        if (ELeaveCode.db.select(dbConn, obj))
                        {
                            return(obj.LeaveCode + " - " + obj.LeaveCodeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("LeavePlanID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ELeavePlan obj = new ELeavePlan();
                        obj.LeavePlanID = int.Parse(fieldValue);
                        if (ELeavePlan.db.select(dbConn, obj))
                        {
                            return(obj.LeavePlanCode + " - " + obj.LeavePlanDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("M", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("MPFPlanID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EMPFPlan obj = new EMPFPlan();
                        obj.MPFPlanID = int.Parse(fieldValue);
                        if (EMPFPlan.db.select(dbConn, obj))
                        {
                            return(obj.MPFPlanCode + " - " + obj.MPFPlanDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }

                    if (tmpfieldName.Equals("MPFSchemeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EMPFScheme obj = new EMPFScheme();
                        obj.MPFSchemeID = int.Parse(fieldValue);
                        if (EMPFScheme.db.select(dbConn, obj))
                        {
                            return(obj.MPFSchemeCode + " - " + obj.MPFSchemeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("MPFSchemeCessationReasonID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EMPFSchemeCessationReason obj = new EMPFSchemeCessationReason();
                        obj.MPFSchemeCessationReasonID = int.Parse(fieldValue);
                        if (EMPFSchemeCessationReason.db.select(dbConn, obj))
                        {
                            return(obj.MPFSchemeCessationReasonCode + " - " + obj.MPFSchemeCessationReasonDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("O", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("ORSOPlanID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EORSOPlan obj = new EORSOPlan();
                        obj.ORSOPlanID = int.Parse(fieldValue);
                        if (EORSOPlan.db.select(dbConn, obj))
                        {
                            return(obj.ORSOPlanCode + " - " + obj.ORSOPlanDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("P", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("PayGroupID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayrollGroupID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EPayrollGroup obj = new EPayrollGroup();
                        obj.PayGroupID = int.Parse(fieldValue);
                        if (EPayrollGroup.db.select(dbConn, obj))
                        {
                            return(obj.PayGroupCode + " - " + obj.PayGroupDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("PayFormID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EPayrollProrataFormula obj = new EPayrollProrataFormula();
                        obj.PayFormID = int.Parse(fieldValue);
                        if (EPayrollProrataFormula.db.select(dbConn, obj))
                        {
                            return(obj.PayFormCode + " - " + obj.PayFormDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayCodeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EPaymentCode obj = new EPaymentCode();
                        obj.PaymentCodeID = int.Parse(fieldValue);
                        if (EPaymentCode.db.select(dbConn, obj))
                        {
                            return(obj.PaymentCode + " - " + obj.PaymentCodeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("PaymentTypeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EPaymentType obj = new EPaymentType();
                        obj.PaymentTypeID = int.Parse(fieldValue);
                        if (EPaymentType.db.select(dbConn, obj))
                        {
                            return(obj.PaymentTypeCode + " - " + obj.PaymentTypeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("PayPeriodID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EPayrollPeriod obj = new EPayrollPeriod();
                        obj.PayPeriodID = int.Parse(fieldValue);
                        if (EPayrollPeriod.db.select(dbConn, obj))
                        {
                            return(GetValueFromID(dbConn, "PayrollGroupID", obj.PayGroupID.ToString()) + ": " + obj.PayPeriodFr.ToString("yyyy-MM-dd") + " to " + obj.PayPeriodTo.ToString("yyyy-MM-dd"));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("PermitTypeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EPermitType obj = new EPermitType();
                        obj.PermitTypeID = int.Parse(fieldValue);
                        if (EPermitType.db.select(dbConn, obj))
                        {
                            return(obj.PermitTypeCode + " - " + obj.PermitTypeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("PositionID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EPosition obj = new EPosition();
                        obj.PositionID = int.Parse(fieldValue);
                        if (EPosition.db.select(dbConn, obj))
                        {
                            return(obj.PositionCode + " - " + obj.PositionDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("Q", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("QualificationID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EQualification obj = new EQualification();
                        obj.QualificationID = int.Parse(fieldValue);
                        if (EQualification.db.select(dbConn, obj))
                        {
                            return(obj.QualificationCode + " - " + obj.QualificationDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("R", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("RankID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ERank obj = new ERank();
                        obj.RankID = int.Parse(fieldValue);
                        if (ERank.db.select(dbConn, obj))
                        {
                            return(obj.RankCode + " - " + obj.RankDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("ReminderTypeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EReminderType obj = new EReminderType();
                        obj.ReminderTypeID = int.Parse(fieldValue);
                        if (EReminderType.db.select(dbConn, obj))
                        {
                            return(obj.ReminderTypeCode + " - " + obj.ReminderTypeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("RosterClientID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ERosterClient obj = new ERosterClient();
                        obj.RosterClientID = int.Parse(fieldValue);
                        if (ERosterClient.db.select(dbConn, obj))
                        {
                            return(obj.RosterClientCode + " - " + obj.RosterClientName);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("RosterClientSiteID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ERosterClientSite obj = new ERosterClientSite();
                        obj.RosterClientSiteID = int.Parse(fieldValue);
                        if (ERosterClientSite.db.select(dbConn, obj))
                        {
                            return(obj.RosterClientSiteCode);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("RosterCodeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ERosterCode obj = new ERosterCode();
                        obj.RosterCodeID = int.Parse(fieldValue);
                        if (ERosterCode.db.select(dbConn, obj))
                        {
                            return(obj.RosterCode + " - " + obj.RosterCodeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("S", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("SkillID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ESkill obj = new ESkill();
                        obj.SkillID = int.Parse(fieldValue);
                        if (ESkill.db.select(dbConn, obj))
                        {
                            return(obj.SkillCode + " - " + obj.SkillDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("SkillLevelID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ESkillLevel obj = new ESkillLevel();
                        obj.SkillLevelID = int.Parse(fieldValue);
                        if (ESkillLevel.db.select(dbConn, obj))
                        {
                            return(obj.SkillLevelCode + " - " + obj.SkillLevelDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("StaffTypeID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EStaffType obj = new EStaffType();
                        obj.StaffTypeID = int.Parse(fieldValue);
                        if (EStaffType.db.select(dbConn, obj))
                        {
                            return(obj.StaffTypeCode + " - " + obj.StaffTypeDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("T", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("TaxCompID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ETaxCompany obj = new ETaxCompany();
                        obj.TaxCompID = int.Parse(fieldValue);
                        if (ETaxCompany.db.select(dbConn, obj))
                        {
                            return(obj.TaxCompEmployerName);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("TaxEmpID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ETaxEmp obj = new ETaxEmp();
                        obj.TaxEmpID = int.Parse(fieldValue);
                        if (ETaxEmp.db.select(dbConn, obj))
                        {
                            return(obj.TaxEmpSurname + ", " + obj.TaxEmpOtherName + ", " + GetValueFromID(dbConn, "TaxFormID", obj.TaxFormID.ToString()));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("TaxFormID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ETaxForm obj = new ETaxForm();
                        obj.TaxFormID = int.Parse(fieldValue);
                        if (ETaxForm.db.select(dbConn, obj))
                        {
                            return("Tax Year :" + obj.TaxFormYear + ", Form: IR56" + obj.TaxFormType);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("TaxPayID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ETaxPayment obj = new ETaxPayment();
                        obj.TaxPayID = int.Parse(fieldValue);
                        if (ETaxPayment.db.select(dbConn, obj))
                        {
                            return(obj.TaxPayCode + " - " + obj.TaxPayDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("TrainingCourseID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ETrainingCourse obj = new ETrainingCourse();
                        obj.TrainingCourseID = int.Parse(fieldValue);
                        if (ETrainingCourse.db.select(dbConn, obj))
                        {
                            return(obj.TrainingCourseCode + " - " + obj.TrainingCourseName);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("TrainingSeminarID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        ETrainingSeminar obj = new ETrainingSeminar();
                        obj.TrainingSeminarID = int.Parse(fieldValue);
                        if (ETrainingSeminar.db.select(dbConn, obj))
                        {
                            return(GetValueFromID(dbConn, "TrainingCourseID", obj.TrainingCourseID.ToString()) + ": " + obj.TrainingSeminarDateFrom.ToString("yyyy-MM-dd") + " to " + obj.TrainingSeminarDateTo.ToString("yyyy-MM-dd"));
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("U", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("UserID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EUser obj = new EUser();
                        obj.UserID = int.Parse(fieldValue);
                        if (EUser.db.select(dbConn, obj))
                        {
                            return(obj.LoginID + " - " + obj.UserName);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                    if (tmpfieldName.Equals("UserGroupID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EUserGroup obj = new EUserGroup();
                        obj.UserGroupID = int.Parse(fieldValue);
                        if (EUserGroup.db.select(dbConn, obj))
                        {
                            return(obj.UserGroupName + " - " + obj.UserGroupDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }
                else if (tmpfieldName.StartsWith("Y", StringComparison.CurrentCultureIgnoreCase))
                {
                    if (tmpfieldName.Equals("YebPlanID", StringComparison.CurrentCultureIgnoreCase))
                    {
                        EYEBPlan obj = new EYEBPlan();
                        obj.YEBPlanID = int.Parse(fieldValue);
                        if (EYEBPlan.db.select(dbConn, obj))
                        {
                            return(obj.YEBPlanCode + " - " + obj.YEBPlanDesc);
                        }
                        else
                        {
                            return(string.Empty);
                        }
                    }
                }

                if (tmpfieldName.EndsWith("ID") && !tmpfieldName.EndsWith("HKID") && !tmpfieldName.EndsWith("LoginID") && !tmpfieldName.EndsWith("CurrencyID") && !tmpfieldName.EndsWith("LeaveAppID") && !tmpfieldName.EndsWith("EmpPaymentID") && !tmpfieldName.EndsWith("PayBatchID") && !tmpfieldName.EndsWith("PayRecID") && !tmpfieldName.EndsWith("RosterTableID") && !tmpfieldName.EndsWith("SynID") && !tmpfieldName.EndsWith("CNDImportBatchID"))
                {
                    if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y"))
                    {
                        throw new Exception("ID field not define:" + fieldName);
                    }
                }
                return(string.Empty);
            }
            catch (Exception ex)
            {
                if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y"))
                {
                    throw ex;
                }
                else
                {
                    return(string.Empty);
                }
            }
        }
    private void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy)
    {
        DataTable dataTable = new DataTable();

        dataTable.Columns.Add("EmpID", typeof(int));
        dataTable.Columns.Add("EmpNo", typeof(string));
        dataTable.Columns.Add("EmpName", typeof(string));
        dataTable.Columns.Add("EmpAlias", typeof(string));
        dataTable.Columns.Add("Company", typeof(string));

        DBFilter  hierarchyLevelFilter    = new DBFilter();
        Hashtable hierarchyLevelHashTable = new Hashtable();

        hierarchyLevelFilter.add("HLevelSeqNo", true);
        ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);

        foreach (EHierarchyLevel hlevel in hierarchyLevelList)
        {
            dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
            hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
        }
        dataTable.Columns.Add("Position", typeof(string));

        dataTable.Columns.Add("TrainingSeminarID", typeof(int));
        dataTable.Columns.Add("TrainingCourseName", typeof(string));
        dataTable.Columns.Add("TrainingSeminarDesc", typeof(string));
        dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime));
        dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime));
        dataTable.Columns.Add("TrainingSeminarDuration", typeof(double));
        dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string));
        dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string));


        foreach (EEmpPersonalInfo empInfo in empList)
        {
            if (EEmpPersonalInfo.db.select(dbConn, empInfo))
            {
                EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID);

                ECompany  company          = new ECompany();
                EPosition position         = new EPosition();
                ArrayList empHierarchyList = new ArrayList();
                if (empPos != null)
                {
                    company.CompanyID = empPos.CompanyID;
                    ECompany.db.select(dbConn, company);
                    //row["Company"] = company.CompanyCode;
                    DBFilter empHierarchyFilter = new DBFilter();
                    empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
                    empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
                    //foreach (EEmpHierarchy empHierarchy in empHierarchyList)
                    //{
                    //    EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
                    //    if (hierarchyLevel != null)
                    //    {
                    //        EHierarchyElement hierarchyElement = new EHierarchyElement();
                    //        hierarchyElement.HElementID = empHierarchy.HElementID;
                    //        if (EHierarchyElement.db.select(dbConn, hierarchyElement))
                    //            row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
                    //    }
                    //}

                    position.PositionID = empPos.PositionID;
                    EPosition.db.select(dbConn, position);
                }

                DBFilter empTrainingSeminar = new DBFilter();
                empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID));

                OR orTrainingCourse = null;
                foreach (ListItem item in TrainingCourseList.Items)
                {
                    if (item.Selected)
                    {
                        if (orTrainingCourse == null)
                        {
                            orTrainingCourse = new OR();
                        }
                        orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value));
                    }
                }
                DBFilter trainingSeminarFilter = new DBFilter();
                if (!PeriodFrom.Ticks.Equals(0))
                {
                    trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom));
                }
                if (!PeriodTo.Ticks.Equals(0))
                {
                    trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo));
                }
                if (orTrainingCourse != null)
                {
                    trainingSeminarFilter.add(orTrainingCourse);
                }
                empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter));
                ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar);
                foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList)
                {
                    ETrainingSeminar trainingSeminar = new ETrainingSeminar();
                    trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID;
                    if (ETrainingSeminar.db.select(dbConn, trainingSeminar))
                    {
                        DataRow row = dataTable.NewRow();
                        row["EmpID"]    = empInfo.EmpID;
                        row["EmpNo"]    = empInfo.EmpNo;
                        row["EmpName"]  = empInfo.EmpEngFullName;
                        row["EmpAlias"] = empInfo.EmpAlias;
                        row["Company"]  = company.CompanyCode;
                        foreach (EEmpHierarchy empHierarchy in empHierarchyList)
                        {
                            EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
                            if (hierarchyLevel != null)
                            {
                                EHierarchyElement hierarchyElement = new EHierarchyElement();
                                hierarchyElement.HElementID = empHierarchy.HElementID;
                                if (EHierarchyElement.db.select(dbConn, hierarchyElement))
                                {
                                    row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
                                }
                            }
                        }
                        row["Position"] = position.PositionDesc;

                        row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID;
                        ETrainingCourse trainingCourse = new ETrainingCourse();
                        trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID;
                        if (ETrainingCourse.db.select(dbConn, trainingCourse))
                        {
                            row["TrainingCourseName"] = trainingCourse.TrainingCourseName;
                        }
                        else
                        {
                            row["TrainingCourseName"] = string.Empty;
                        }

                        row["TrainingSeminarDesc"]     = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc;
                        row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom;
                        row["TrainingSeminarDateTo"]   = trainingSeminar.TrainingSeminarDateTo;
                        row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration;
                        if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H"))
                        {
                            row["TrainingSeminarDurationUnit"] = "Hour(s)";
                        }
                        else
                        {
                            row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit;
                        }

                        row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer;

                        dataTable.Rows.Add(row);
                    }
                }
            }
        }

        //org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument();
        //org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report");

        NPOI.HSSF.UserModel.HSSFWorkbook workbook  = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.HSSF.UserModel.HSSFSheet    worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report");

        NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
        boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

        NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        reportHeaderStyle.SetFont(boldFont);

        NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        columnHeaderStyleCenter.SetFont(boldFont);
        columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

        //NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
        //numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");

        int rowCount = 0;

        //worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true;
        NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0);
        reportHeaderCell.SetCellValue("Training Report");
        reportHeaderCell.CellStyle = reportHeaderStyle;
        rowCount++;

        if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0))
        {
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
            worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
            rowCount++;
        }
        else if (!PeriodTo.Ticks.Equals(0))
        {
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
            worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
            rowCount++;
        }
        else if (!PeriodFrom.Ticks.Equals(0))
        {
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy"));
            worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy"));
            rowCount++;
        }

        if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase))
        {
            DataView dataView = new DataView(dataTable);
            dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo";
            DataTable sortedTable = dataView.ToTable();
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
            //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
            //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
            //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
            //worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
            NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow  = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
            NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
            columnHeaderCell.SetCellValue("Training Date");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
            columnHeaderCell.SetCellValue("Course Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
            columnHeaderCell.SetCellValue("Description");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
            columnHeaderCell.SetCellValue("Duration");
            columnHeaderCell.CellStyle = columnHeaderStyleCenter;
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
            columnHeaderCell.SetCellValue("Trainer");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
            columnHeaderCell.SetCellValue("Employee No.");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
            columnHeaderCell.SetCellValue("Employee Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
            columnHeaderCell.SetCellValue("Alias");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
            columnHeaderCell.SetCellValue("Position");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
            columnHeaderCell.SetCellValue("Company");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int colCount = 10;
            foreach (EHierarchyLevel hlevel in hierarchyLevelList)
            {
                colCount++;
                //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
                columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
                columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
                columnHeaderCell.CellStyle = reportHeaderStyle;
            }

            int currentTrainingSeminarID = 0;
            foreach (DataRow row in sortedTable.Rows)
            {
                rowCount++;
                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);

                if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"]))
                {
                    if (row["TrainingSeminarDateFrom"] != DBNull.Value)
                    {
                        DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
                        DateTime trainingTo   = ((DateTime)row["TrainingSeminarDateTo"]);
                        if (trainingFrom.Equals(trainingTo))
                        {
                            //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy"));
                            detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
                        }
                        else
                        {
                            //worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                            detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                        }
                    }
                }
                currentTrainingSeminarID = ((int)row["TrainingSeminarID"]);

                //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]);
                //worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]);
                //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
                //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
                //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
                //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
                //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
                //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
                //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
                //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);

                detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString());
                detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString());
                detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
                //detailRow.GetCell(3).CellStyle = numericStyle;
                detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
                detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
                detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
                detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
                detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
                detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
                detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());

                colCount = 10;
                foreach (EHierarchyLevel hlevel in hierarchyLevelList)
                {
                    colCount++;
                    if (row[hlevel.HLevelDesc] != DBNull.Value)
                    {
                        //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
                        detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
                    }
                }
            }
        }
        else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase))
        {
            DataView dataView = new DataView(dataTable);
            dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName ";
            DataTable sortedTable = dataView.ToTable();
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true;
            NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow  = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
            NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
            columnHeaderCell.SetCellValue("Position");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
            columnHeaderCell.SetCellValue("Employee No.");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
            columnHeaderCell.SetCellValue("Employee Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
            columnHeaderCell.SetCellValue("Alias");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4);
            columnHeaderCell.SetCellValue("Company");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int colCount = 4;
            foreach (EHierarchyLevel hlevel in hierarchyLevelList)
            {
                colCount++;
                //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
                columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
                columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
                columnHeaderCell.CellStyle = reportHeaderStyle;
            }

            //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty);
            //worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5);
            //worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
            //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true;

            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1);
            columnHeaderCell.SetCellValue("Course Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2);
            columnHeaderCell.SetCellValue("Description");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3);
            columnHeaderCell.SetCellValue("Training Date");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4);
            columnHeaderCell.SetCellValue("Duration");
            columnHeaderCell.CellStyle = columnHeaderStyleCenter;
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5));
            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6);
            columnHeaderCell.SetCellValue("Trainer");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int currentEmpID = 0;
            foreach (DataRow row in sortedTable.Rows)
            {
                rowCount++;
                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);

                if (!currentEmpID.Equals((int)row["EmpID"]))
                {
                    //worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]);
                    //worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]);
                    //worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]);
                    //worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]);
                    //worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]);
                    detailRow.CreateCell(0).SetCellValue(row["Position"].ToString());
                    detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString());
                    detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString());
                    detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString());
                    detailRow.CreateCell(4).SetCellValue(row["Company"].ToString());


                    colCount = 4;
                    foreach (EHierarchyLevel hlevel in hierarchyLevelList)
                    {
                        colCount++;
                        if (row[hlevel.HLevelDesc] != DBNull.Value)
                        {
                            //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
                            detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
                        }
                    }
                }
                currentEmpID = ((int)row["EmpID"]);

                //worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]);
                //worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]);
                detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString());
                detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString());
                if (row["TrainingSeminarDateFrom"] != DBNull.Value)
                {
                    DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
                    DateTime trainingTo   = ((DateTime)row["TrainingSeminarDateTo"]);
                    if (trainingFrom.Equals(trainingTo))
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
                    }
                    else
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                    }
                }

                //worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]);
                //worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]);
                //worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]);
                detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]);
                //detailRow.GetCell(colCount + 4).CellStyle = numericStyle;
                detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
                detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString());
            }
        }
        else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase))
        {
            DataView dataView = new DataView(dataTable);
            dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo";
            DataTable sortedTable = dataView.ToTable();
            rowCount++;
            //worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
            //worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
            //worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
            //worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
            //worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
            //worksheet.Cells.Merge(rowCount, rowCount, 4, 5);

            NPOI.HSSF.UserModel.HSSFRow  columnHeaderRow  = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
            NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
            columnHeaderCell.SetCellValue("Course Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
            columnHeaderCell.SetCellValue("Description");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
            columnHeaderCell.SetCellValue("Training Date");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
            columnHeaderCell.SetCellValue("Duration");
            columnHeaderCell.CellStyle = columnHeaderStyleCenter;
            worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
            columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
            columnHeaderCell.SetCellValue("Trainer");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
            columnHeaderCell.SetCellValue("Employee No.");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
            columnHeaderCell.SetCellValue("Employee Name");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
            columnHeaderCell.SetCellValue("Alias");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
            columnHeaderCell.SetCellValue("Position");
            columnHeaderCell.CellStyle = reportHeaderStyle;
            columnHeaderCell           = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
            columnHeaderCell.SetCellValue("Company");
            columnHeaderCell.CellStyle = reportHeaderStyle;

            int colCount = 10;
            foreach (EHierarchyLevel hlevel in hierarchyLevelList)
            {
                colCount++;
                //worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
                columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
                columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
                columnHeaderCell.CellStyle = reportHeaderStyle;
            }

            foreach (DataRow row in sortedTable.Rows)
            {
                rowCount++;

                NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
                //worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]);
                //worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]);
                detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString());
                detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString());

                if (row["TrainingSeminarDateFrom"] != DBNull.Value)
                {
                    DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
                    DateTime trainingTo   = ((DateTime)row["TrainingSeminarDateTo"]);
                    if (trainingFrom.Equals(trainingTo))
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
                    }
                    else
                    {
                        //worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                        detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
                    }
                }

                //worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
                //worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
                //worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
                //worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
                //worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
                //worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
                //worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
                //worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
                detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
                //detailRow.GetCell(3).CellStyle = numericStyle;
                detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
                detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
                detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
                detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
                detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
                detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
                detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());

                colCount = 10;
                foreach (EHierarchyLevel hlevel in hierarchyLevelList)
                {
                    colCount++;
                    if (row[hlevel.HLevelDesc] != DBNull.Value)
                    {
                        //worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
                        detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
                    }
                }
            }
        }
        string exportFileName = System.IO.Path.GetTempFileName();

        System.IO.File.Delete(exportFileName);
        exportFileName += ".xls";
        //document.FileName = exportFileName;
        //document.Save();
        System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
        workbook.Write(file);
        file.Close();
        string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls";

        WebUtils.TransmitFile(Response, exportFileName, filename, true);
        return;
    }