private static string GenerateConditon(string prjName, string prjCode, DateTime?start, DateTime?end, int?memberFowState, int?prjState, string userCode, string prjManagerName) { StringBuilder builder = new StringBuilder(); builder.Append("1=1"); if (!string.IsNullOrEmpty(userCode)) { string[] arr = PrivHelper.GetBusiDataId("project", userCode).ToArray <string>(); if (arr.Length == 0) { builder.AppendFormat(" AND vProject.PrjGuid is null \n", new object[0]); } else { builder.AppendFormat(" AND vProject.PrjGuid IN({0}) \n", DBHelper.GetInParameterSql(arr)); } } if (!string.IsNullOrEmpty(prjName)) { builder.AppendFormat(" AND PrjName LIKE '%{0}%' ", prjName.Trim()); } if (!string.IsNullOrEmpty(prjCode)) { builder.AppendFormat(" AND PrjCode LIKE '%{0}%' ", prjCode.Trim()); } if (!string.IsNullOrEmpty(prjManagerName)) { builder.AppendFormat(" AND PrjMangerName LIKE '%{0}%' ", prjManagerName.Trim()); } if (start.HasValue) { builder.AppendFormat(" AND StartDate >= '{0}' ", Common2.GetTime(start)); } if (end.HasValue) { builder.AppendFormat(" AND StartDate <='{0}' ", Common2.GetTime(end)); } if (memberFowState.HasValue) { builder.AppendFormat(" AND MemberFlowState='{0}' ", memberFowState); } if (prjState.HasValue) { builder.AppendFormat(" AND PrjState='{0}'", prjState); } return(builder.ToString()); }
public DataTable getPayoutPlanInfo(string userCode, int planYear, int planMonth, string prjName, string contract, string planType) { int num = 0; int num2 = 0; if (planMonth == 1) { num = planYear - 1; num2 = 12; } else { num = planYear; num2 = planMonth - 1; } StringBuilder builder = new StringBuilder(); builder.Append("select * from ("); builder.Append(" select 0 as xh,prjName,contractName,beforePlanMoney,planMoney,conPayMoney,"); builder.Append(" convert(varchar(20),convert(decimal(18,3),case when beforePlanMoney=0 then 0 else (conPayMoney/beforePlanMoney)*100 end ))+'%' as ExecuteRatio,"); builder.Append(" convert(decimal(18,3),(conPayMoney-beforePlanMoney) ) as ExecuteVariation,"); builder.Append(" ReMark,prjGuid,contractid from ("); builder.Append("SELECT * FROM ("); builder.Append("select ptPrj.prjName,con.contractName,"); builder.Append(" isnull((select planMoney from dbo.Fund_Plan_MonthDetail planM where planM.contractId=con.contractid and planM.MonthPlanID in "); builder.Append(string.Concat(new object[] { " (select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where pM.PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", num, " and pM.PlanMonth=", num2, " and pM.PrjGuid=ptPrj.prjGuid) " })); builder.Append(" ),0) as beforePlanMoney,"); if (planType == "payout") { builder.Append(" isnull((select sum(PaymentMoney) from Con_Payout_Payment conPay where conPay.ContractID=con.contractid and conPay.FlowState=1 and "); builder.Append(" conPay.MonthPlanUID="); builder.Append("\t(select UID from dbo.Fund_Plan_MonthDetail planM where planM.contractId=con.contractid and planM.MonthPlanID in "); builder.Append(string.Concat(new object[] { "\t\t\t(select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where pM.PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", num, " and pM.PlanMonth=", num2, " and pM.PrjGuid=ptPrj.prjGuid) " })); builder.Append("\t)),0) as conPayMoney,"); } else { builder.Append(" isnull((select sum(CllectionPrice) from Con_Incomet_Payment conPay where conPay.ContractID=con.contractid and "); builder.Append(" conPay.MonthPlanUID="); builder.Append("\t(select UID from dbo.Fund_Plan_MonthDetail planM where planM.contractId=con.contractid and planM.MonthPlanID in "); builder.Append(string.Concat(new object[] { "\t\t\t(select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where pM.PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", num, " and pM.PlanMonth=", num2, " and pM.PrjGuid=ptPrj.prjGuid) " })); builder.Append("\t)),0) as conPayMoney,"); } builder.Append(" isnull((select planMoney from dbo.Fund_Plan_MonthDetail planM where planM.contractId=con.contractid and planM.MonthPlanID in "); builder.Append(" (select MonthPlanID from dbo.Fund_Plan_MonthMain as pM "); builder.Append(string.Concat(new object[] { " where pM.PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", planYear, " and pM.PlanMonth=", planMonth, " and pM.PrjGuid=ptPrj.prjGuid) " })); builder.Append(" ),0) as planMoney,"); builder.Append(" (SELECT PD.Remark FROM dbo.Fund_Plan_MonthDetail PD LEFT JOIN Fund_Plan_MonthMain AS pM ON PD.MonthPlanID=PM.MonthPlanID "); builder.Append(string.Concat(new object[] { " where PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", planYear, " and pM.PlanMonth=", planMonth, " and pM.PrjGuid=ptPrj.prjGuid AND PD.ContractID=con.contractid" })); builder.Append(" ) as ReMark ,"); builder.Append(" ptPrj.prjGuid,con.contractid"); builder.Append(" from pt_prjInfo as ptPrj "); if (planType == "payout") { builder.Append(" left join Con_Payout_Contract as con on ptPrj.prjGuid=con.prjGuid"); } else { builder.Append(" left join Con_Incomet_Contract as con on ptPrj.prjGuid=con.project"); } builder.Append(" where isValid=1 ) as selInfo) as prj "); builder.Append(" union"); builder.Append(" select 1, ptPrj.prjName ,'小计' ,0,0,0,'',0 , '',ptPrj.prjGuid ,null from pt_prjInfo as ptPrj where isValid=1"); builder.Append(" ) as allInfo "); builder.Append(" where 1=1 "); if (contract.ToString() != "") { builder.Append(" and ContractID='" + contract.ToString() + "'"); } if (!string.IsNullOrEmpty(prjName)) { builder.Append(string.Format(" and prjName like '%{0}%'", prjName)); } if (!string.IsNullOrEmpty(userCode)) { StringBuilder builder2 = new StringBuilder(); IList <string> busiDataId = PrivHelper.GetBusiDataId("project", userCode); if ((busiDataId != null) && (busiDataId.Count > 0)) { string[] strArray = null; foreach (string str in busiDataId) { if (builder2.Length != 0) { builder2.Append(","); } if (str.Contains(",")) { strArray = str.Split(new char[] { ',' }); for (int i = 0; i < (strArray.Length - 1); i++) { if (!string.IsNullOrEmpty(strArray[i])) { builder2.Append(string.Format("'{0}'", strArray[i])); } } } builder2.Append(string.Format("'{0}'", str)); } builder2.Insert(0, "("); builder2.Insert(builder2.Length, ")"); // TODO 2015-7-28 @tao 修改:将下面原代码移动到判断内部 builder.Append(string.Format(" and allInfo.prjGuid In {0}", builder2.ToString())); } //原代码:builder.Append(string.Format(" and allInfo.prjGuid In {0}", builder2.ToString())); } builder.Append(" order by prjGuid,xh"); return(publicDbOpClass.DataTableQuary(builder.ToString())); }
public DataTable GetPlanInfo(string userCode, int planYear, int planMonth, string planType, string prjName) { int num = 0; int num2 = 0; if (planMonth == 1) { num = planYear - 1; num2 = 12; } else { num = planYear; num2 = planMonth - 1; } StringBuilder builder = new StringBuilder(); builder.Append(" select *,"); builder.Append(" convert(varchar(20),convert(decimal(18,3),case when planMoney=0 then 0 else (conPayMoney/planMoney)*100 end ))+'%' as ratio, "); builder.Append(string.Concat(new object[] { " (select Remark from dbo.Fund_Plan_MonthMain as pM where PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", planYear, " and pM.PlanMonth=", planMonth, " and pM.PrjGuid=PlanInfo.prjGuid) as Remark" })); builder.Append(" from ("); builder.Append(" select "); builder.Append(" ptPrj.prjName,"); builder.Append(" isnull((select sum(planMoney) from Fund_Plan_MonthDetail as planDetail "); builder.Append("\t\t\twhere planDetail.MonthPlanID=(select MonthPlanID from dbo.Fund_Plan_MonthMain "); builder.Append("\t\t\t\t\twhere PrjGuid=ptPrj.PrjGuid and FlowState=1 and PlanType='" + planType + "'"); builder.Append(string.Concat(new object[] { "\t\t\t\t\t\t\tand PlanYear=", num, " and PlanMonth=", num2, " " })); builder.Append("\t)),0) as planMoney,"); if (planType == "payout") { builder.Append(" isnull(convert(decimal(18,3),(select sum(PaymentMoney) from Con_Payout_Payment conPay where conPay.FlowState=1 and "); builder.Append(" conPay.ContractID in "); builder.Append("\t\t(select ContractID from dbo.Fund_Plan_MonthDetail planM where planM.MonthPlanID = "); builder.Append(string.Concat(new object[] { "\t\t\t\t(select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", num, " and pM.PlanMonth=", num2, " and pM.PrjGuid=ptPrj.prjGuid)" })); builder.Append("\t\t)"); builder.Append(" and "); builder.Append("\tconPay.MonthPlanUID in"); builder.Append("\t\t(select UID from dbo.Fund_Plan_MonthDetail planM where planM.MonthPlanID in "); builder.Append(string.Concat(new object[] { "\t\t\t\t(select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", num, " and pM.PlanMonth=", num2, " and pM.PrjGuid=ptPrj.prjGuid) " })); builder.Append("\t\t)"); builder.Append(" )),0) as conPayMoney,"); } else { builder.Append(" isnull(convert(decimal(18,3),(select sum(CllectionPrice) from Con_Incomet_Payment conPay where "); builder.Append(" conPay.ContractID in "); builder.Append("\t\t(select ContractID from dbo.Fund_Plan_MonthDetail planM where planM.MonthPlanID = "); builder.Append(string.Concat(new object[] { "\t\t\t\t(select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", num, " and pM.PlanMonth=", num2, " and pM.PrjGuid=ptPrj.prjGuid)" })); builder.Append("\t\t)"); builder.Append(" and "); builder.Append("\tconPay.MonthPlanUID in"); builder.Append("\t\t(select UID from dbo.Fund_Plan_MonthDetail planM where planM.MonthPlanID in "); builder.Append(string.Concat(new object[] { "\t\t\t\t(select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", num, " and pM.PlanMonth=", num2, " and pM.PrjGuid=ptPrj.prjGuid) " })); builder.Append("\t\t)"); builder.Append(" )),0) as conPayMoney,"); } builder.Append(" isnull((select sum(planMoney) from Fund_Plan_MonthDetail as planDetail "); builder.Append("\twhere planDetail.MonthPlanID="); builder.Append(string.Concat(new object[] { "\t\t(select MonthPlanID from dbo.Fund_Plan_MonthMain as pM where PlanType='", planType, "' and pM.FlowState=1 and pM.PlanYear=", planYear, " and pM.PlanMonth=", planMonth, " and pM.PrjGuid=ptPrj.prjGuid) " })); builder.Append("\t),0) as NewPlanMoney,"); builder.Append(" ptPrj.prjGuid"); builder.Append(" from pt_prjInfo as ptPrj where isValid=1 "); if (!string.IsNullOrEmpty(userCode)) { StringBuilder builder2 = new StringBuilder(); IList <string> busiDataId = PrivHelper.GetBusiDataId("project", userCode); if ((busiDataId != null) && (busiDataId.Count > 0)) { string[] strArray = null; foreach (string str in busiDataId) { if (builder2.Length != 0) { builder2.Append(","); } if (str.Contains(",")) { strArray = str.Split(new char[] { ',' }); for (int i = 0; i < (strArray.Length - 1); i++) { if (!string.IsNullOrEmpty(strArray[i])) { builder2.Append(string.Format("'{0}'", strArray[i])); } } } builder2.Append(string.Format("'{0}'", str)); } builder2.Insert(0, "("); builder2.Insert(builder2.Length, ")"); // TODO 2015-7-28 @tao 修改:将下面原代码移动到判断内部 builder.Append(string.Format(" and ptPrj.prjGuid In {0}", builder2.ToString())); } //原代码:builder.Append(string.Format(" and ptPrj.prjGuid In {0}", builder2.ToString())); } builder.Append(" ) as PlanInfo"); if (!string.IsNullOrEmpty(prjName)) { builder.Append(string.Format(" where prjName like '%{0}%'", prjName)); } return(publicDbOpClass.DataTableQuary(builder.ToString())); }