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());
}