private string GetFilterSqlLast(IRptParams filter)
{
this.GetFilter(filter);
string strstartday = this.dtStartDate == DateTime.MinValue ? "" : FieldFormatterUtil.GetDateFormatString(this.Context, this.dtStartDate.AddYears(-1));
string strendday = this.dtEndDate == DateTime.MaxValue ? "" : FieldFormatterUtil.GetDateFormatString(this.Context, this.dtEndDate.AddYears(-1));
StringBuilder builder = new StringBuilder();
if (!string.IsNullOrWhiteSpace(strstartday) && !string.IsNullOrWhiteSpace(strendday))
{
builder.AppendLine(string.Format(" and b.FDATE>=to_date('{0}','YYYY-MM-DD') and b.FDATE<=to_date('{1}','YYYY-MM-DD')", strstartday, strendday));
return(builder.ToString());
}
else if (!string.IsNullOrWhiteSpace(strstartday) && string.IsNullOrWhiteSpace(strendday))
{
builder.AppendLine(string.Format(" and b.FDATE>=to_date('{0}','YYYY-MM-DD')", strstartday));
return(builder.ToString());
}
else if (string.IsNullOrWhiteSpace(strstartday) && !string.IsNullOrWhiteSpace(strendday))
{
builder.AppendLine(string.Format(" and b.FDATE<=to_date('{0}','YYYY-MM-DD')", strendday));
return(builder.ToString());
}
else
{
return("");
}
}
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");
}
}