private string GetFCUSTOMERID(IRptParams filter) { day.Clear(); this.GetFilter(filter); //获取开始、结束日期 int i, j, k; int days = (this.dtEndDate.Date - this.dtStartDate.Date).Days + 1; //日期差 //转换日期列 string str = string.Empty; double zq2 = Math.Ceiling((double)days / zq);//日期差/周期,全入 jl = Convert.ToInt32(Math.Ceiling((double)days / zq)); if (zq * 2 <= days) //选择日期天数大于等于2个配送周期 { str = "sum(case when fdate=to_date('" + FieldFormatterUtil.GetDateFormatString(this.Context, this.dtEndDate) + "','yyyy-mm-dd') and f>0 then '1' else '0' end) as f0"; day.Add(this.dtEndDate.ToString("yyyy-MM-dd")); for (i = 1; i < zq2 * zq; i++) { str += " ,sum(case when fdate=to_date('" + FieldFormatterUtil.GetDateFormatString(this.Context, this.dtEndDate.AddDays(-i)) + "','yyyy-mm-dd') and f>0 then '1' else '0' end) as f" + i; day.Add(this.dtEndDate.AddDays(-i).ToString("yyyy-MM-dd")); } } else { jl = 2; str = "sum(case when fdate=to_date('" + FieldFormatterUtil.GetDateFormatString(this.Context, this.dtEndDate) + "','yyyy-mm-dd') and f>0 then '1' else '0' end) as f0"; day.Add(this.dtEndDate.ToString("yyyy-MM-dd")); for (i = 1; i < zq * 2; i++) { str += " ,sum(case when fdate=to_date('" + FieldFormatterUtil.GetDateFormatString(this.Context, this.dtEndDate.AddDays(-i)) + "','yyyy-mm-dd') and f>0 then '1' else '0' end) as f" + i; day.Add(this.dtEndDate.AddDays(-i).ToString("yyyy-MM-dd")); } } //日期、销售组织过滤条件 string rqqj = string.Format(@"and b.FSALEORGID='{0}' and b.FDATE<=to_date('{1}','YYYY-MM-DD') and b.FDATE>to_date('{2}','YYYY-MM-DD') {3} {4} {5}", this.Context.CurrentOrganizationInfo.ID, FieldFormatterUtil.GetDateFormatString(this.Context, this.dtEndDate), FieldFormatterUtil.GetDateFormatString(this.Context, this.dtEndDate.AddDays(-i)), FDeptFilter(filter), FDELIVIRYROUND(filter), FLineFilter(filter)); //原始数据sql instr0 = string.Format(@"select fdate, FCUSTOMERID,fline,fsaledeptid,sum(f1) f from ( select b.FDATE,b.FCUSTOMERID,b.fline,b.fsaledeptid,count(b.FCUSTOMERID) f1 from T_SAL_OUTSTOCK b join T_BD_CUSTOMER e on b.FCUSTOMERID = e.FCUSTID where b.fline<>0 and b.fdocumentstatus='C' and b.fvirtual0 = ' ' {0} group by b.FDATE,b.FCUSTOMERID,b.fline,b.fsaledeptid union all select b.FDATE,b.fretcustid,b.fline,b.fsaledeptid,count(b.fretcustid) from T_SAL_RETURNSTOCK b join T_BD_CUSTOMER e on b.fretcustid = e.FCUSTID where b.fline<>0 and b.fdocumentstatus='C' and b.fvirtual0 = ' ' {0} group by b.FDATE,b.fretcustid,b.fline,b.fsaledeptid ) group by FDATE,FCUSTOMERID,fline,fsaledeptid order by FDATE", rqqj); //按日期列整理 string instr1 = string.Format(@"/*dialect*/select/*二开/未按标准配送*/ FCUSTOMERID ,0 fkey,{0} from( {1} ) a group by FCUSTOMERID ", str, instr0); str0 = str; DynamicObjectCollection col = DBUtils.ExecuteDynamicObject(this.Context, instr1); #region 计算 List <int> sl = new List <int>(); int ew = 0; for (int num = 0; num < col.LongCount(); num++)//有num条记录 { sl.Clear(); for (j = 0; j < zq; j++) { for (k = j; k < i; k = k + zq) { ew += Convert.ToInt32(col[num]["f" + k]); } sl.Add(ew); ew = 0; } int pd1 = 0; int pd2 = 0; for (int w = 0; w < sl.Count; w++) { if (sl[w] != (i / zq) && sl[w] != 0) { break; } else { if (w == 0 && sl.Count == 1) { if (sl[w] == (i / zq)) { col[num]["fkey"] = "1"; } } else if (w < sl.Count - 1 && sl.Count > 1) { if (sl[w] == (i / zq)) { pd1++; } else if (sl[w] == 0) { pd2++; } } else if (w == sl.Count - 1 && sl.Count > 1) { if (sl[w] == (i / zq)) { pd1++; } else if (sl[w] == 0) { pd2++; } if (pd1 == 1 && pd2 == (sl.Count - 1)) { col[num]["fkey"] = "1"; } } } } } #endregion string FCUSTOMERID = "";//col中fkey=0的客户即为未按标准配送的客户 StringBuilder builder = new StringBuilder(); for (int t = 0; t < col.LongCount(); t++) { if (0.Equals(Convert.ToInt32(col[t]["fkey"]))) { FCUSTOMERID += Convert.ToInt32(col[t]["FCUSTOMERID"]) + ","; } } if (!string.IsNullOrWhiteSpace(FCUSTOMERID)) { return(FCUSTOMERID.Remove(FCUSTOMERID.Length - 1, 1)); } else { return("0"); } }