/// <summary> /// 根据对象构造条件语句 /// </summary> /// <param name="tMisContractFee"></param> /// <returns></returns> public string BuildWhereStatement(TMisContractFeeVo tMisContractFee) { StringBuilder strWhereStatement = new StringBuilder(" Where 1=1 "); if (null != tMisContractFee) { //ID if (!String.IsNullOrEmpty(tMisContractFee.ID.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND ID = '{0}'", tMisContractFee.ID.ToString())); } //委托书ID if (!String.IsNullOrEmpty(tMisContractFee.CONTRACT_ID.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND CONTRACT_ID = '{0}'", tMisContractFee.CONTRACT_ID.ToString())); } //监测费用,监测费用明细总和 if (!String.IsNullOrEmpty(tMisContractFee.TEST_FEE.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND TEST_FEE = '{0}'", tMisContractFee.TEST_FEE.ToString())); } //附加总费用,附加费用明细总和 if (!String.IsNullOrEmpty(tMisContractFee.ATT_FEE.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND ATT_FEE = '{0}'", tMisContractFee.ATT_FEE.ToString())); } //预算总费用,监测总费用+附加总费用 if (!String.IsNullOrEmpty(tMisContractFee.BUDGET.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND BUDGET = '{0}'", tMisContractFee.BUDGET.ToString())); } //实际收费 if (!String.IsNullOrEmpty(tMisContractFee.INCOME.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND INCOME = '{0}'", tMisContractFee.INCOME.ToString())); } //是否缴费 if (!String.IsNullOrEmpty(tMisContractFee.IF_PAY.ToString().Trim())) { if (tMisContractFee.IF_PAY.ToString().Trim() == "0") { strWhereStatement.Append(string.Format(" AND (IF_PAY = '{0}' or IF_PAY IS NULL)", tMisContractFee.IF_PAY.ToString())); } else { strWhereStatement.Append(string.Format(" AND IF_PAY = '{0}'", tMisContractFee.IF_PAY.ToString())); } } //备注1 if (!String.IsNullOrEmpty(tMisContractFee.REMARK1.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND REMARK1 = '{0}'", tMisContractFee.REMARK1.ToString())); } //备注2 if (!String.IsNullOrEmpty(tMisContractFee.REMARK2.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND REMARK2 = '{0}'", tMisContractFee.REMARK2.ToString())); } //备注3 if (!String.IsNullOrEmpty(tMisContractFee.REMARK3.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND REMARK3 = '{0}'", tMisContractFee.REMARK3.ToString())); } //备注4 if (!String.IsNullOrEmpty(tMisContractFee.REMARK4.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND REMARK4 = '{0}'", tMisContractFee.REMARK4.ToString())); } //备注5 if (!String.IsNullOrEmpty(tMisContractFee.REMARK5.ToString().Trim())) { strWhereStatement.Append(string.Format(" AND REMARK5 = '{0}'", tMisContractFee.REMARK5.ToString())); } } return(strWhereStatement.ToString()); }
/// <summary> /// 对象明细 /// </summary> /// <param name="tMisContractFee">对象条件</param> /// <returns>对象</returns> public TMisContractFeeVo Details(TMisContractFeeVo tMisContractFee) { string strSQL = String.Format("select * from T_MIS_CONTRACT_FEE " + this.BuildWhereStatement(tMisContractFee)); return(SqlHelper.ExecuteObject(new TMisContractFeeVo(), strSQL)); }
/// <summary> /// 获取统计缴费信息记录数 /// </summary> /// <param name="tMisContract"></param> /// <param name="tMisContractFee"></param> /// <returns></returns> public DataTable SelectTableForContractFreeCount(TMisContractVo tMisContract, TMisContractFeeVo tMisContractFee) { string StartDate = "", EndDate = ""; string strSQL = String.Format(@"SELECT CASE WHEN B.IF_PAY IS NULL THEN '未缴费' WHEN B.IF_PAY='0' THEN '未缴费' ELSE '已缴费' END AS FINISHTYPE,COUNT(*) AS FINISHSUM FROM T_MIS_CONTRACT A LEFT JOIN T_MIS_CONTRACT_FEE B ON A.ID=B.CONTRACT_ID LEFT JOIN dbo.T_MIS_CONTRACT_COMPANY C ON C.ID=TESTED_COMPANY_ID LEFT JOIN T_SYS_DICT D ON D.DICT_CODE=A.CONTRACT_TYPE AND D.DICT_TYPE='Contract_Type' WHERE A.CONTRACT_STATUS!='0'"); if (!String.IsNullOrEmpty(tMisContract.PROJECT_NAME)) { strSQL += String.Format(" AND A.PROJECT_NAME", tMisContract.PROJECT_NAME); } if (!String.IsNullOrEmpty(tMisContract.CONTRACT_CODE)) { strSQL += String.Format(" AND A.CONTRACT_CODE LIKE '%{0}%'", tMisContract.CONTRACT_CODE); } if (!String.IsNullOrEmpty(tMisContract.TESTED_COMPANY_ID)) { strSQL += String.Format(" AND C.COMPANY_NAME LIKE '%{0}%'", tMisContract.TESTED_COMPANY_ID); } if (!String.IsNullOrEmpty(tMisContract.CONTRACT_TYPE)) { strSQL += String.Format(" AND A.CONTRACT_TYPE='{0}'", tMisContract.CONTRACT_TYPE); } if (!String.IsNullOrEmpty(tMisContract.REMARK3)) { //月度 strSQL += String.Format(" AND CONVERT(DATETIME, CONVERT(VARCHAR(100), A.ASKING_DATE,23),111) "); if (!String.IsNullOrEmpty(tMisContract.REMARK4) && String.IsNullOrEmpty(tMisContract.REMARK5)) { StartDate = String.Format(" {0}-{1}-01", tMisContract.REMARK3, tMisContract.REMARK4); EndDate = String.Format(" {0}-{1}-31", tMisContract.REMARK3, tMisContract.REMARK4); } //季度 if (String.IsNullOrEmpty(tMisContract.REMARK4) && !String.IsNullOrEmpty(tMisContract.REMARK5)) { StartDate = String.Format(" {0}-{1}-01", tMisContract.REMARK3, tMisContract.REMARK5); DateTime strMonth = DateTime.Parse(StartDate); EndDate = String.Format(" {0}-{1}-31", tMisContract.REMARK3, strMonth.AddMonths(+2).Month.ToString()); } //年度 if (String.IsNullOrEmpty(tMisContract.REMARK4) && String.IsNullOrEmpty(tMisContract.REMARK5)) { StartDate = String.Format(" {0}-01-01", tMisContract.REMARK3); EndDate = String.Format(" {0}-12-31", tMisContract.REMARK3); } strSQL += String.Format(" BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR(100),'{0}' ,23),111) AND CONVERT(DATETIME, CONVERT(VARCHAR(100), '{1}' ,23),111) ", StartDate, EndDate); } strSQL += " GROUP BY B.IF_PAY"; return(SqlHelper.ExecuteDataTable(strSQL)); }
/// <summary> /// 获得查询结果总行数,用于分页 /// </summary> /// <param name="tMisContractFee">对象</param> /// <returns>返回行数</returns> public int GetSelectResultCount(TMisContractFeeVo tMisContractFee) { string strSQL = "select Count(*) from T_MIS_CONTRACT_FEE " + this.BuildWhereStatement(tMisContractFee); return(Convert.ToInt32(SqlHelper.ExecuteScalar(strSQL))); }
/// <summary> /// 获取统计缴费信息 /// </summary> /// <param name="tMisContract"></param> /// <param name="tMisContractFee"></param> /// <returns></returns> public DataTable SelectTableForContractFree(TMisContractVo tMisContract, TMisContractFeeVo tMisContractFee, bool flag) { string StartDate = "", EndDate = ""; string strSQL = String.Format(@"SELECT ROW_NUMBER() OVER(ORDER BY A.ID) AS ROWNUM,A.ID,A.CONTRACT_CODE,A.PROJECT_NAME,A.CONTRACT_YEAR,A.ASKING_DATE,B.TEST_FEE,B.ATT_FEE,BUDGET, CONVERT(DECIMAL(18,2),CASE WHEN B.INCOME IS NULL THEN '0' WHEN B.INCOME='NULL' THEN '0' ELSE B.INCOME END) AS INCOME, CASE WHEN B.IF_PAY IS NULL THEN '未缴费' WHEN B.IF_PAY='0' THEN '未缴费' ELSE '已缴费' END AS IF_PAY, C.COMPANY_NAME,D.DICT_TEXT AS CONTRACT_TYPE FROM T_MIS_CONTRACT A LEFT JOIN T_MIS_CONTRACT_FEE B ON A.ID=B.CONTRACT_ID LEFT JOIN dbo.T_MIS_CONTRACT_COMPANY C ON C.ID=TESTED_COMPANY_ID LEFT JOIN T_SYS_DICT D ON D.DICT_CODE=A.CONTRACT_TYPE AND D.DICT_TYPE='Contract_Type' WHERE A.CONTRACT_STATUS!='0'"); if (!String.IsNullOrEmpty(tMisContract.PROJECT_NAME)) { strSQL += String.Format(" AND A.PROJECT_NAME", tMisContract.PROJECT_NAME); } if (!String.IsNullOrEmpty(tMisContract.CONTRACT_CODE)) { strSQL += String.Format(" AND A.CONTRACT_CODE LIKE '%{0}%'", tMisContract.CONTRACT_CODE); } if (!String.IsNullOrEmpty(tMisContract.TESTED_COMPANY_ID)) { strSQL += String.Format(" AND C.COMPANY_NAME LIKE '%{0}%'", tMisContract.TESTED_COMPANY_ID); } if (!String.IsNullOrEmpty(tMisContract.CONTRACT_TYPE)) { strSQL += String.Format(" AND A.CONTRACT_TYPE='{0}'", tMisContract.CONTRACT_TYPE); } if (!String.IsNullOrEmpty(tMisContract.REMARK3)) { //月度 strSQL += String.Format(" AND CONVERT(DATETIME, CONVERT(VARCHAR(100), A.ASKING_DATE,23),111) "); if (!String.IsNullOrEmpty(tMisContract.REMARK4) && String.IsNullOrEmpty(tMisContract.REMARK5)) { StartDate = String.Format(" {0}-{1}-01", tMisContract.REMARK3, tMisContract.REMARK4); EndDate = String.Format(" {0}-{1}-31", tMisContract.REMARK3, tMisContract.REMARK4); } //季度 if (String.IsNullOrEmpty(tMisContract.REMARK4) && !String.IsNullOrEmpty(tMisContract.REMARK5)) { StartDate = String.Format(" {0}-{1}-01", tMisContract.REMARK3, tMisContract.REMARK5); DateTime strMonth = DateTime.Parse(StartDate); EndDate = String.Format(" {0}-{1}-31", tMisContract.REMARK3, strMonth.AddMonths(+2).Month.ToString()); } //年度 if (String.IsNullOrEmpty(tMisContract.REMARK4) && String.IsNullOrEmpty(tMisContract.REMARK5)) { StartDate = String.Format(" {0}-01-01", tMisContract.REMARK3); EndDate = String.Format(" {0}-12-31", tMisContract.REMARK3); } strSQL += String.Format(" BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR(100),'{0}' ,23),111) AND CONVERT(DATETIME, CONVERT(VARCHAR(100), '{1}' ,23),111) ", StartDate, EndDate); } if (flag) { if (!String.IsNullOrEmpty(tMisContractFee.IF_PAY) && tMisContractFee.IF_PAY == "1") { strSQL += String.Format(" AND B.IF_PAY='{0}'", tMisContractFee.IF_PAY); } else { strSQL += String.Format(" AND (B.IF_PAY IS NULL or B.IF_PAY='0')"); } } return(SqlHelper.ExecuteDataTable(strSQL)); }
/// <summary> /// 对象添加 /// </summary> /// <param name="tMisContractFee">对象</param> /// <returns>是否成功</returns> public bool Create(TMisContractFeeVo tMisContractFee) { string strSQL = SqlHelper.BuildInsertExpress(tMisContractFee, TMisContractFeeVo.T_MIS_CONTRACT_FEE_TABLE); return(SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL) > 0 ? true : false); }
/// <summary> /// 根据对象获取全部数据,用Table承载 /// 数据量较小时使用【不推荐】 /// </summary> /// <param name="tMisContractFee"></param> /// <returns></returns> public DataTable SelectByTable(TMisContractFeeVo tMisContractFee) { string strSQL = "select * from T_MIS_CONTRACT_FEE " + this.BuildWhereStatement(tMisContractFee); return(SqlHelper.ExecuteDataTable(strSQL)); }