protected void Page_Load(object sender, EventArgs e) { this.yearMonth = DecodedQueryString[FeeMonthInfoConst.YearMonth]; this.feeID = DecodedQueryString[FeeInfoConst.FeeID]; this.parentID = DecodedQueryString[FeeInfoConst.ParentID]; this.action = EnumHelper.Parse <ActionType>(DecodedQueryString[SalaryConst.QueryAction]); String feeType = DecodedQueryString[FeeInfoConst.FeeType], commonFeeType = DecodedQueryString[FeeInfoConst.CommonFeeType]; if (!String.IsNullOrEmpty(feeType)) { this.enumFeeType = EnumHelper.Parse <FeeType>(feeType); this.name = this.enumFeeType.Equals(FeeType.Common) ? "工资组成" : this.enumFeeType.Equals(FeeType.Sum) ? "工资计算" : this.enumFeeType.Equals(FeeType.Parameter) ? "参数" : this.enumFeeType.Equals(FeeType.Tax) ? "个税" : this.name; } if (!String.IsNullOrEmpty(commonFeeType)) { this.enumCommonFeeType = EnumHelper.Parse <CommonFeeType>(commonFeeType); } if (!IsPostBack) { this.InitializeControl(); } else { GridViewControl.ResetGridView(this.gvTaxList); } }
public List<PersonBaseFeeInfo> GetPersonBaseFeeInfoList(PersonBaseFeeTarget feeTarget, String yearMonth, String personID, CommonFeeType commonFeeType) { String columns = String.Empty, columnCals = String.Empty; //取得需计算的列表 WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); if (!String.IsNullOrEmpty(yearMonth)) { builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth); } if (commonFeeType.ToString("D") != "0") { builder.AppendItem(FeeInfoDBConst.CommonFeeType, commonFeeType.ToString("d")); } builder.AppendItem(FeeInfoDBConst.UseFlag, Status.True.ToString("D")); builder.AppendItem(FeeInfoDBConst.FeeType, FeeType.Common.ToString("D")); //builder.AppendItem(String.Concat("ISNULL(",FeeMonthInfoDBConst.CalculateExp,",'')<>''")); List<FeeInfo> feeList = FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder); foreach (FeeInfo feeInfo in feeList) { columns += String.Concat(",[", feeInfo.FeeID, "]"); columnCals += String.IsNullOrEmpty(feeInfo.CalculateExp) ? String.Concat(",Convert(decimal(18,3),[", feeInfo.FeeID, "]) AS [", feeInfo.FeeID, "]") : String.Concat(",Convert(decimal(18,3),", FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeInfo.CalculateExp).Replace(" ", ""), ") AS [", feeInfo.FeeID, "]"); } WhereSqlClauseBuilder builderF = new WhereSqlClauseBuilder(); builderF.AppendItem(String.Concat("F.", FeeInfoDBConst.FeeType), FeeType.Common.ToString("D")); builderF.AppendItem(String.Concat("ISNULL(F.", FeeInfoDBConst.CalculateExp, ",'')=''")); if (!String.IsNullOrEmpty(yearMonth)) { builderF.AppendItem(String.Concat("F.", FeeMonthInfoDBConst.YearMonth), yearMonth); } WhereSqlClauseBuilder builderP = new WhereSqlClauseBuilder(); if (!String.IsNullOrEmpty(personID)) { builderP.AppendItem(String.Concat("P.", PersonInfoDBConst.PersonID), personID); } String sql = String.Format(@"DECLARE @FSQL VARCHAR(8000), @CSQL VARCHAR(8000), @NSQL VARCHAR(8000) SELECT @FSQL=ISNULL(@FSQL + '],[','') + Convert(nvarchar(32),FEE_ID) FROM {0} F WHERE {2} exec ('SELECT D.PERSON_ID,D.PERSON_NAME,D.[FEE_ID],D.[FEE_VALUE],BF.DEPARTMENT_NAME,BF.PROJECT_NAME,BF.DEPARTMENT_ID,BF.PROJECT_ID FROM ( SELECT PERSON_ID, PERSON_NAME, {4} FROM ( SELECT F.FEE_ID,P.PERSON_ID,P.PERSON_NAME,ISNULL(B.FEE_VALUE,F.DEFAULT_VALUE) AS ''PAY_MONEY'' FROM PERSON P CROSS JOIN {0} F LEFT OUTER JOIN {1} B ON P.PERSON_ID=B.PERSON_ID AND F.FEE_ID = B.FEE_ID WHERE {3} AND {6})P PIVOT(MAX(PAY_MONEY) FOR FEE_ID IN ([' + @FSQL + ']))B )T UNPIVOT ([FEE_VALUE] FOR [FEE_ID] IN ({5}))D LEFT OUTER JOIN PERSON_BASE_FEE BF ON D.FEE_ID=BF.FEE_ID AND D.PERSON_ID=BF.PERSON_ID ')" , String.IsNullOrEmpty(yearMonth) ? FeeInfoDBConst.TableName : ORMapping.GetMappingInfo<FeeMonthInfo>().TableName , String.IsNullOrEmpty(yearMonth) ? PersonBaseFeeInfoDBConst.TableName : ORMapping.GetMappingInfo<PersonBaseFeeMonthInfo>().TableName , builderF.ToSqlString() , builderF.ToSqlString().Replace("'", "''") , columnCals.Remove(0, 1), columns.Remove(0, 1) , builderP.ToSqlString().Replace("'", "''")); DataTable dt = _DataHelper.GetDataTable(sql); List<PersonBaseFeeInfo> result = new List<PersonBaseFeeInfo>(); for (Int32 i = 0; i < dt.Rows.Count; i++) { PersonBaseFeeInfo info = feeTarget.Equals(PersonBaseFeeTarget.PersonBaseFeeMonth) ? new PersonBaseFeeMonthInfo() : feeTarget.Equals(PersonBaseFeeTarget.PersonBaseFee) ? new PersonBaseFeeInfo() : new PayMonthInfo(yearMonth); ORMapping.DataRowToObject(dt.Rows[i], info); result.Add(info); } //List<PersonBaseFeeInfo> resultNew = new List<PersonBaseFeeInfo>(); List<PersonBaseFeeInfo> resultNew = result.Join(feeList.Where(f =>f.CommonFeeType==commonFeeType).ToList(), baseFee => baseFee.FeeID, fee => fee.FeeID, (baseFee, fee) => { PersonBaseFeeInfo info = String.IsNullOrEmpty(yearMonth) ? new PersonBaseFeeInfo() : new PersonBaseFeeMonthInfo(yearMonth); info.FeeCode = fee.FeeCode; info.FeeID = baseFee.FeeID; info.FeeName = fee.FeeName; info.FeeValue = baseFee.FeeValue; info.PersonID = baseFee.PersonID; info.PersonName = baseFee.PersonName; info.DepartmentID = baseFee.DepartmentID; info.DepartmentName = baseFee.DepartmentName; info.ProjectID = baseFee.ProjectID; info.ProjectName = baseFee.ProjectName; return info; }).ToList(); return resultNew; }
protected void Page_Load(object sender, EventArgs e) { this.yearMonth = DecodedQueryString[FeeMonthInfoConst.YearMonth]; this.feeID = DecodedQueryString[FeeInfoConst.FeeID]; this.parentID = DecodedQueryString[FeeInfoConst.ParentID]; this.action = EnumHelper.Parse<ActionType>(DecodedQueryString[SalaryConst.QueryAction]); String feeType = DecodedQueryString[FeeInfoConst.FeeType], commonFeeType = DecodedQueryString[FeeInfoConst.CommonFeeType]; if (!String.IsNullOrEmpty(feeType)) { this.enumFeeType = EnumHelper.Parse<FeeType>(feeType); this.name = this.enumFeeType.Equals(FeeType.Common) ? "工资组成" : this.enumFeeType.Equals(FeeType.Sum) ? "工资计算" : this.enumFeeType.Equals(FeeType.Parameter) ? "参数" : this.enumFeeType.Equals(FeeType.Tax) ? "个税" : this.name; } if (!String.IsNullOrEmpty(commonFeeType)) { this.enumCommonFeeType = EnumHelper.Parse<CommonFeeType>(commonFeeType); } if (!IsPostBack) { this.InitializeControl(); } else { GridViewControl.ResetGridView(this.gvTaxList); } }
private void BaseGridViewDataBind(GridView gvList, CommonFeeType commonFeeType) { GridViewControl.GridViewDataBind <PersonBaseFeeInfo>(gvList , PersonBaseFeeInfoAdapter.Instance.GetPersonBaseFeeInfoList(PersonBaseFeeTarget.PersonBaseFee, yearMonth, personID, commonFeeType) .OrderBy(fee => fee.FeeCode).ToList()); }
public List <PersonBaseFeeInfo> GetPersonBaseFeeInfoList(PersonBaseFeeTarget feeTarget, String yearMonth, String personID, CommonFeeType commonFeeType) { String columns = String.Empty, columnCals = String.Empty; //取得需计算的列表 WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); if (!String.IsNullOrEmpty(yearMonth)) { builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth); } if (commonFeeType.ToString("D") != "0") { builder.AppendItem(FeeInfoDBConst.CommonFeeType, commonFeeType.ToString("d")); } builder.AppendItem(FeeInfoDBConst.UseFlag, Status.True.ToString("D")); builder.AppendItem(FeeInfoDBConst.FeeType, FeeType.Common.ToString("D")); //builder.AppendItem(String.Concat("ISNULL(",FeeMonthInfoDBConst.CalculateExp,",'')<>''")); List <FeeInfo> feeList = FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder); foreach (FeeInfo feeInfo in feeList) { columns += String.Concat(",[", feeInfo.FeeID, "]"); columnCals += String.IsNullOrEmpty(feeInfo.CalculateExp) ? String.Concat(",Convert(decimal(18,3),[", feeInfo.FeeID, "]) AS [", feeInfo.FeeID, "]") : String.Concat(",Convert(decimal(18,3),", FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeInfo.CalculateExp).Replace(" ", ""), ") AS [", feeInfo.FeeID, "]"); } WhereSqlClauseBuilder builderF = new WhereSqlClauseBuilder(); builderF.AppendItem(String.Concat("F.", FeeInfoDBConst.FeeType), FeeType.Common.ToString("D")); builderF.AppendItem(String.Concat("ISNULL(F.", FeeInfoDBConst.CalculateExp, ",'')=''")); if (!String.IsNullOrEmpty(yearMonth)) { builderF.AppendItem(String.Concat("F.", FeeMonthInfoDBConst.YearMonth), yearMonth); } WhereSqlClauseBuilder builderP = new WhereSqlClauseBuilder(); if (!String.IsNullOrEmpty(personID)) { builderP.AppendItem(String.Concat("P.", PersonInfoDBConst.PersonID), personID); } String sql = String.Format(@"DECLARE @FSQL VARCHAR(8000), @CSQL VARCHAR(8000), @NSQL VARCHAR(8000) SELECT @FSQL=ISNULL(@FSQL + '],[','') + Convert(nvarchar(32),FEE_ID) FROM {0} F WHERE {2} exec ('SELECT D.PERSON_ID,D.PERSON_NAME,D.[FEE_ID],D.[FEE_VALUE],BF.DEPARTMENT_NAME,BF.PROJECT_NAME,BF.DEPARTMENT_ID,BF.PROJECT_ID FROM ( SELECT PERSON_ID, PERSON_NAME, {4} FROM ( SELECT F.FEE_ID,P.PERSON_ID,P.PERSON_NAME,ISNULL(B.FEE_VALUE,F.DEFAULT_VALUE) AS ''PAY_MONEY'' FROM PERSON P CROSS JOIN {0} F LEFT OUTER JOIN {1} B ON P.PERSON_ID=B.PERSON_ID AND F.FEE_ID = B.FEE_ID WHERE {3} AND {6})P PIVOT(MAX(PAY_MONEY) FOR FEE_ID IN ([' + @FSQL + ']))B )T UNPIVOT ([FEE_VALUE] FOR [FEE_ID] IN ({5}))D LEFT OUTER JOIN PERSON_BASE_FEE BF ON D.FEE_ID=BF.FEE_ID AND D.PERSON_ID=BF.PERSON_ID ')" , String.IsNullOrEmpty(yearMonth) ? FeeInfoDBConst.TableName : ORMapping.GetMappingInfo <FeeMonthInfo>().TableName , String.IsNullOrEmpty(yearMonth) ? PersonBaseFeeInfoDBConst.TableName : ORMapping.GetMappingInfo <PersonBaseFeeMonthInfo>().TableName , builderF.ToSqlString() , builderF.ToSqlString().Replace("'", "''") , columnCals.Remove(0, 1), columns.Remove(0, 1) , builderP.ToSqlString().Replace("'", "''")); DataTable dt = _DataHelper.GetDataTable(sql); List <PersonBaseFeeInfo> result = new List <PersonBaseFeeInfo>(); for (Int32 i = 0; i < dt.Rows.Count; i++) { PersonBaseFeeInfo info = feeTarget.Equals(PersonBaseFeeTarget.PersonBaseFeeMonth) ? new PersonBaseFeeMonthInfo() : feeTarget.Equals(PersonBaseFeeTarget.PersonBaseFee) ? new PersonBaseFeeInfo() : new PayMonthInfo(yearMonth); ORMapping.DataRowToObject(dt.Rows[i], info); result.Add(info); } //List<PersonBaseFeeInfo> resultNew = new List<PersonBaseFeeInfo>(); List <PersonBaseFeeInfo> resultNew = result.Join(feeList.Where(f => f.CommonFeeType == commonFeeType).ToList(), baseFee => baseFee.FeeID, fee => fee.FeeID, (baseFee, fee) => { PersonBaseFeeInfo info = String.IsNullOrEmpty(yearMonth) ? new PersonBaseFeeInfo() : new PersonBaseFeeMonthInfo(yearMonth); info.FeeCode = fee.FeeCode; info.FeeID = baseFee.FeeID; info.FeeName = fee.FeeName; info.FeeValue = baseFee.FeeValue; info.PersonID = baseFee.PersonID; info.PersonName = baseFee.PersonName; info.DepartmentID = baseFee.DepartmentID; info.DepartmentName = baseFee.DepartmentName; info.ProjectID = baseFee.ProjectID; info.ProjectName = baseFee.ProjectName; return(info); }).ToList(); return(resultNew); }
private void BaseGridViewDataBind(GridView gvList, CommonFeeType commonFeeType) { GridViewControl.GridViewDataBind<PersonBaseFeeInfo>(gvList , PersonBaseFeeInfoAdapter.Instance.GetPersonBaseFeeInfoList(PersonBaseFeeTarget.PersonBaseFee, yearMonth, personID, commonFeeType) .OrderBy(fee => fee.FeeCode).ToList()); }