protected override string BuilderTempTableOrderBySQL(IRptParams filter)
{
return(base.BuilderTempTableOrderBySQL(filter));
}
/// <summary>
/// 向报表临时表，插入报表数据
/// </summary>
/// <param name="filter"></param>
/// <param name="tableName"></param>
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
// 拼接过滤条件 ： filter
// 略
temTableName = AppServiceContext.DBService.CreateTemporaryTableName(this.Context);
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
string FilterString = filter.FilterParameter.FilterString;
string SupperId = "";
string OrgId = "";
string sWhere = "";
if (dyFilter["F_SZXY_OrgId"] is DynamicObject OrgDo)
{
OrgId = Convert.ToString(OrgDo["Id"]);
sWhere += $" and t0.F_SZXY_ORGID={OrgId} ";
}
if (dyFilter["F_SZXY_GX"] is string GX)
{
sWhere += $" and t0.F_SZXY_GX={GX} ";
}
if (dyFilter["F_SZXY_SDate"] is DateTime Sdate && dyFilter["F_SZXY_EDate"] is DateTime Edate)
{
if (Sdate != DateTime.MinValue && Sdate != null && Edate != DateTime.MinValue && Edate != null)
{
string sdate = dyFilter["F_SZXY_SDate"].ToString();
string edate = dyFilter["F_SZXY_EDate"].ToString();
sWhere += $" and CONVERT(date,T0.F_SZXY_Date, 23) between CONVERT(date, '{sdate}', 23) and CONVERT(date, '{edate}', 23) ";
}
}
// 默认排序字段：需要从filter中取用户设置的排序字段
string seqFld = string.Format(base.KSQL_SEQ, " T0.FEntryId ");
string sql2 = string.Format(@" /*dialect*/SELECT CASE F_SZXY_GX
WHEN 'SZXY_XYFQ'
THEN '分切'
WHEN 'SZXY_XYBZ'
THEN '包装'
END '工序'
,F_SZXY_ReCordCode '打印记录号'
,T1.FNAME '打印人N'
,T0.F_SZXY_Recorder '打印人'
,F_SZXY_Date '日期'
,F_SZXY_PLy '厚度'
,F_SZXY_Width '宽度'
,F_SZXY_Len '长度'
,F_SZXY_Area '面积'
,F_SZXY_MoNO '生产订单号'
,F_SZXY_MoLineNo '生产订单行号'
,F_SZXY_CustID '客户'
,T2.FNAME '客户N'
,{0}
INTO {1}
FROM SZXY_t_PrintRecordEntry T0
LEFT JOIN T_HR_EMPINFO_L T1 ON T1.FID = T0.F_SZXY_Recorder
AND T1.FLOCALEID = '2052'
LEFT JOIN T_BD_CUSTOMER_L T2 ON t2.FCUSTID = T0.F_SZXY_CustID
",
seqFld, tableName, sWhere);
DBUtils.ExecuteDynamicObject(this.Context, sql2);
}
protected override System.Data.DataTable GetReportData(IRptParams filter)
{
return(base.GetReportData(filter));
}
protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
{
string result = base.CreateGroupSummaryData(filter, tablename);
return(result);
}
public override List <long> GetOrgIdList(IRptParams filter)
{
var result = base.GetOrgIdList(filter);
return(result);
}
public override int GetRowsCount(IRptParams filter)
{
var result = base.GetRowsCount(filter);
return(result);
}
protected override string BuilderSelectFieldSQL(IRptParams filter)
{
string result = base.BuilderSelectFieldSQL(filter);
return(result);
}
/// <summary>
/// 向报表临时表，插入报表数据
/// </summary>
/// <param name="filter">过滤信息</param>
/// <param name="tableName">临时表名</param>
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
//默认排序字段：需要从filter中取用户设置的排序字段
//KSQL_SEQ: ROW_NUMBER() OVER(ORDER BY {0} ) FIDENTITYID
if (filter.FilterParameter.SortString.IsNullOrEmptyOrWhiteSpace())
{
KSQL_SEQ = string.Format(KSQL_SEQ, " t1.FMATERIALID asc");
}
else
{
KSQL_SEQ = string.Format(KSQL_SEQ, filter.FilterParameter.SortString);
}
string sql = string.Format(
@"
SELECT t0.FID ,
t1.FENTRYID ,
t1.FMATERIALID ,
t1M_L.FNAME AS FMATERIALNAME ,
t1.FQTY ,
t1U_L.FNAME AS FUNITNAME ,
t1f.FTAXPRICE ,
t1f.FALLAMOUNT ,
t1f.FTAXRATE ,
t0.FBILLNO ,
t0.FDATE ,
0 AS FPRICE ,
{0}
INTO {1}
FROM T_PUR_POORDER t0
INNER JOIN T_PUR_POORDERENTRY t1 ON ( t0.FID = t1.FID )
INNER JOIN T_PUR_POORDERENTRY_F t1F ON ( t1.FENTRYID = t1f.FENTRYID )
LEFT JOIN T_BD_MATERIAL_L t1M_L on (t1.FMATERIALID = t1m_l.FMATERIALID and t1M_L.FLOCALEID = 2052)
LEFT JOIN T_BD_UNIT t1U ON ( t1f.FPRICEUNITID = t1u.FUNITID )
LEFT JOIN T_BD_UNIT_L t1U_L ON ( t1U.FUNITID = t1U_L.FUNITID AND t1U_L.FLOCALEID = 2052 )
WHERE 1 = 1 ", KSQL_SEQ, tableName);
//添加快捷过滤
if (filter.FilterParameter.CustomFilter != null &&
filter.FilterParameter.CustomFilter.Contains("FMATERIAL"))
{
var material = filter.FilterParameter.CustomFilter["FMATERIAL"] as DynamicObject;
if (material != null)
{
var quickStr = string.Format("t1.FMATERIALID='{0}'", material[0]);
sql += string.Format("AND {0}", quickStr);
}
}
//添加条件过滤
string conditionStr = filter.FilterParameter.FilterString;
if (!conditionStr.IsNullOrEmptyOrWhiteSpace())
{
sql += string.Format("AND {0}", conditionStr);
}
DBUtils.Execute(Context, sql);
}
//反写报表过滤信息
public override ReportTitles GetReportTitles(IRptParams filter)
{
var result = base.GetReportTitles(filter);
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
if (dyFilter != null)
{
if (result == null)
{
result = new ReportTitles();
}
//反写过滤条件
//起始日期
if (dyFilter["F_QSNC_StartDateFilter"] == null)
{
result.AddTitle("F_QSNC_StartDate", "");
}
else
{
result.AddTitle("F_QSNC_StartDate", Convert.ToString(dyFilter["F_QSNC_StartDateFilter"]));
}
//截止日期
if (dyFilter["F_QSNC_EndDateFilter"] == null)
{
result.AddTitle("F_QSNC_EndDate", "");
}
else
{
result.AddTitle("F_QSNC_EndDate", Convert.ToString(dyFilter["F_QSNC_EndDateFilter"]));
}
//销售部门
if (dyFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"]).Count > 0)
{
StringBuilder deptName = new StringBuilder();
DynamicObjectCollection cols = (DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"];
foreach (DynamicObject dept in cols)
{
String tmpName = Convert.ToString(((DynamicObject)dept["F_QSNC_DeptFilter"])["Name"]);
deptName.Append(tmpName + "; ");
}
result.AddTitle("F_QSNC_Department", deptName.ToString());
}
else
{
result.AddTitle("F_QSNC_Department", "全部");
}
//销售员
if (dyFilter["F_QSNC_SalerFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"]).Count > 0)
{
StringBuilder salerName = new StringBuilder();
DynamicObjectCollection cols = (DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"];
foreach (DynamicObject saler in cols)
{
String tmpName = Convert.ToString(((DynamicObject)saler["F_QSNC_SalerFilter"])["Name"]);
salerName.Append(tmpName + "; ");
}
result.AddTitle("F_QSNC_Saler", salerName.ToString());
}
else
{
result.AddTitle("F_QSNC_Saler", "全部");
}
}
return(result);
}
public override List <Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
{
return(base.GetTreeNodes(filter));
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
IDBService dbservice = ServiceHelper.GetService <IDBService>();
userTempTable = dbservice.CreateTemporaryTableName(this.Context, 1);
string tempTable1 = userTempTable[0];
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
string customerIndustryID = String.Empty;
string customerRelationshipID = String.Empty;
string endUserID = String.Empty;
string salesDeptID = String.Empty;
string fromDate = String.Empty;
string toDate = String.Empty;
if (customFilter["F_xy_CustomerIndustry"] != null)
{
customerIndustryID = customFilter["F_xy_CustomerIndustry_Id"].ToString();
}
if (customFilter["F_xy_CustomerRelationship"] != null)
{
customerRelationshipID = customFilter["F_xy_CustomerRelationship_Id"].ToString();
}
if (customFilter["F_xy_EndUser"] != null)
{
endUserID = customFilter["F_xy_EndUser_Id"].ToString();
}
if (customFilter["F_xy_SalesDept"] != null)
{
salesDeptID = customFilter["F_xy_SalesDept_Id"].ToString();
}
if (customFilter["F_xy_FromDate"] != null)
{
fromDate = string.Format("{0:yyyy-MM-dd}", customFilter["F_xy_FromDate"]);
}
if (customFilter["F_xy_ToDate"] != null)
{
toDate = string.Format("{0:yyyy-MM-dd}", customFilter["F_xy_ToDate"]);
}
Boolean hasCustomerIndustry = !String.IsNullOrEmpty(customerIndustryID);
Boolean hasCustomerRelationship = !String.IsNullOrEmpty(customerRelationshipID);
Boolean hasEndUser = !String.IsNullOrEmpty(endUserID);
Boolean hasSaleDept = !String.IsNullOrEmpty(salesDeptID);
if (hasEndUser)
{
String s = String.Format(" select distinct fid into {0} from T_CRM_OpportunityProduct where F_PEJK_FINALU='{1}' ", tempTable1, endUserID);
DBUtils.ExecuteDynamicObject(this.Context, s);
}
StringBuilder sql = new StringBuilder();
sql.Append(" select ROW_NUMBER() over(order by customerNO) FIDENTITYID, ");
sql.Append(" customerNo,customerName,oppTotalCount,oppWinBillCount,oppLostBillCount, ");
sql.Append(" oppTotalCount-oppWinBillCount-oppLostBillCount oppUnfinishedCount, ");
sql.Append(" oppWinBillIncome,oppTotalIncome ");
sql.Append(" into " + tableName);
sql.Append(" from ( ");
sql.Append(" select cust.FNUMBER customerNO,cust_l.FNAME customerName, ");
sql.Append(" count(opp.FID) oppTotalCount, ");
sql.Append(" sum(case when opp.FDOCUMENTSTATUS='E' then 1 else 0 end) oppWinBillCount, ");
sql.Append(" sum(case when opp.FDOCUMENTSTATUS='F' then 1 else 0 end) oppLostBillCount, ");
sql.Append(" sum(case when opp.FDOCUMENTSTATUS='E' then opp.FESTIMATEINCOME else 0 end) oppWinBillIncome, ");
sql.Append(" sum(opp.FESTIMATEINCOME) oppTotalIncome ");
sql.Append(" from T_CRM_OPPORTUNITY opp ");
sql.Append(" inner join T_BD_CUSTOMER cust on opp.FCUSTOMERID=cust.FCUSTID ");
sql.Append(" inner join T_BD_CUSTOMER_L cust_l on opp.FCUSTOMERID=cust_l.FCUSTID ");
if (hasEndUser)
{
sql.Append(" inner join "+ tempTable1 + " prod on prod.FID=opp.FID ");
}
sql.Append(" where opp.FDOCUMENTSTATUS >='C' ");
sql.Append(" and opp.F_PEJK_AUDITDATE between '"+ fromDate + "' and '" + toDate + "' ");
if (hasCustomerIndustry)
{
sql.Append(" and opp.F_PEJK_CUSTINDUSTRY='"+ customerIndustryID + "' ");
}
if (hasCustomerRelationship)
{
sql.Append(" and opp.F_PEJK_CUSTSHIP='"+ customerRelationshipID + "' ");
}
if (hasSaleDept)
{
sql.Append(" and opp.FSALEDEPTID='"+ salesDeptID + "' ");
}
sql.Append(" group by cust.FNUMBER,cust_l.FNAME ");
sql.Append(" ) tt ");
DBUtils.ExecuteDynamicObject(this.Context, sql.ToString());
}
public override List <long> GetOrgIdList(IRptParams filter)
{
return(base.GetOrgIdList(filter));
}
public override System.Data.DataTable GetList(IRptParams filter)
{
return(base.GetList(filter));
}
protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
{
return(base.CreateGroupSummaryData(filter, tablename));
}
protected override System.Data.DataTable GetReportData(IRptParams filter)
{
var result = base.GetReportData(filter);
return(result);
}
//执行查询sql 按时间 销售员 销售部门进行过滤
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
// 根据当前用户的UserId 查询出其personId
StringBuilder sql0 = new StringBuilder();
sql0.AppendFormat(@"/*dialect*/ SELECT FLINKOBJECT FROM T_SEC_USER WHERE FUSERID = {0} ", this.Context.UserId);
DynamicObjectCollection collection = DBUtils.ExecuteDynamicObject(this.Context, sql0.ToString());
StringBuilder salerLimit = new StringBuilder();
Boolean flag = false;
if (collection.Count > 0)
{
//获取当前用户personId
DynamicObject personIdObj = (DynamicObject)collection[0];
int personId = Convert.ToInt32(personIdObj["FLINKOBJECT"]);
//获取当前用户权限内的销售数据
if (CRMServiceHelper.getSalerPersonids(this.Context, personId) != null)
{
//获取当前用户可见的销售员集合
List <long> salerList = CRMServiceHelper.getSalerPersonids(this.Context, personId);
int len = 0;
flag = true;
if (salerList.Count >= 1)
{
salerLimit.Append(" IN ( ");
}
foreach (long salerId in salerList)
{
len++;
if (len == salerList.Count)
{
salerLimit.Append(" " + salerId + " ) ");
}
else
{
salerLimit.Append(" " + salerId + ", ");
}
}
}
}
//生成中间临时表
IDBService dbservice = ServiceHelper.GetService <IDBService>();
materialRptTableNames = dbservice.CreateTemporaryTableName(this.Context, 10);
string tmpTable1 = materialRptTableNames[0];
string tmpTable2 = materialRptTableNames[1];
string tmpTable3 = materialRptTableNames[2];
string tmpTable4 = materialRptTableNames[3];
string tmpTable5 = materialRptTableNames[4];
string tmpTable6 = materialRptTableNames[5];
//过滤条件：起始日期/截至日期/部门/业务员
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
String startDate = ""; //起始日期
String endDate = ""; //截至日期
//销售员
StringBuilder salerSql = new StringBuilder();
if (dyFilter["F_QSNC_SalerFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols1 = (DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"];
int salerNum = 0;
if (cols1.Count >= 1)
{
salerSql.Append(" IN (");
}
foreach (DynamicObject saler in cols1)
{
String salerNumber = Convert.ToString(((DynamicObject)saler["F_QSNC_SalerFilter"])["Id"]);
salerNum++;
if (cols1.Count == salerNum)
{
salerSql.Append(salerNumber + ") ");
}
else
{
salerSql.Append(salerNumber + ", ");
}
}
}
//部门
StringBuilder deptSql = new StringBuilder();
if (dyFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols2 = (DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"];
int deptNum = 0;
if (cols2.Count >= 1)
{
deptSql.Append(" IN (");
}
foreach (DynamicObject dept in cols2)
{
String deptNumber = Convert.ToString(((DynamicObject)dept["F_QSNC_DeptFilter"])["Id"]);
deptNum++;
if (cols2.Count == deptNum)
{
deptSql.Append(deptNumber + ") ");
}
else
{
deptSql.Append(deptNumber + ", ");
}
}
}
//报表sql
String yearFilter = "(";
if (dyFilter["F_QSNC_StartDateFilter"] != null)
{
String year1 = Convert.ToDateTime(dyFilter["F_QSNC_StartDateFilter"]).Year.ToString();
yearFilter += "'" + year1 + "',";
}
if (dyFilter["F_QSNC_EndDateFilter"] != null)
{
String year2 = Convert.ToDateTime(dyFilter["F_QSNC_EndDateFilter"]).Year.ToString();
yearFilter += "'" + year2 + "',";
}
if (dyFilter["F_QSNC_StartDateFilter"] != null || dyFilter["F_QSNC_EndDateFilter"] != null)
{
yearFilter = yearFilter.TrimEnd(',');
}
yearFilter += ")";
// ---------------------------------------------------------------------------------
// tmpTable1中存放所有进行合同指标规划的 销售部门、销售员、合同指标
StringBuilder sql1 = new StringBuilder();
sql1.AppendFormat(@"/*dialect*/ SELECT F_PEJK_SALEDEPT, SQE.F_PEJK_SALER, SUM(F_PEJK_CONTRACTQUNTA) YEARQUOTA INTO {0} FROM PEJK_SALERQUNTAENTRY SQE LEFT JOIN PEJK_SALERQUNTA SQH ON SQE.FID = SQH.FID WHERE SQH.FDOCUMENTSTATUS = 'C' AND SQH.F_PEJK_SALEDEPT != 0 ", tmpTable1);
if (dyFilter["F_QSNC_StartDateFilter"] != null || dyFilter["F_QSNC_EndDateFilter"] != null)
{
sql1.AppendFormat(" AND datepart(yyyy,F_PEJK_YEAR) IN {0} ", yearFilter);
}
sql1.AppendLine(" GROUP BY F_PEJK_SALEDEPT, SQE.F_PEJK_SALER ");
DBUtils.ExecuteDynamicObject(this.Context, sql1.ToString());
// ---------------------------------------------------------------------------------
// tmpTable2存放所有进行销售合同分解的中销售员、合同分解金额
StringBuilder sql2 = new StringBuilder();
sql2.AppendFormat(@"/*dialect*/ SELECT F_PEJK_SALER, SUM(F_PEJK_DETAILLAMOUNT) FINISHAMOUNT INTO {0} FROM PEJK_SALECONTRACTENTRY E LEFT JOIN PEJK_SALECONTRACTS H ON E.FID = H.FID WHERE F_PEJK_SALER != 0 AND FDOCUMENTSTATUS = 'C' GROUP BY F_PEJK_SALER ", tmpTable2);
if (dyFilter["F_QSNC_StartDateFilter"] != null)
{
startDate = Convert.ToDateTime(dyFilter["F_QSNC_StartDateFilter"]).ToString("yyyy-MM-dd 00:00:00");
sql1.AppendFormat(" AND H.F_PEJK_DATE >= '{0}' ", startDate);
}
if (dyFilter["F_QSNC_EndDateFilter"] != null)
{
endDate = Convert.ToDateTime(dyFilter["F_QSNC_EndDateFilter"]).ToString("yyyy-MM-dd 23:59:59");
sql1.AppendFormat(" AND H.F_PEJK_DATE <= '{0}' ", endDate);
}
DBUtils.ExecuteDynamicObject(this.Context, sql2.ToString());
// ---------------------------------------------------------------------------------
// tmpTable3
StringBuilder sql3 = new StringBuilder();
sql3.AppendFormat(@"/*dialect*/ SELECT TMP1.F_PEJK_SALEDEPT, TMP1.F_PEJK_SALER, TMP1.YEARQUOTA QUOTA, ISNULL(TMP2.FINISHAMOUNT, 0) COMPLETEAMOUNT INTO {0} FROM {1} TMP1 LEFT JOIN {2} TMP2 ON TMP1.F_PEJK_SALER = TMP2.F_PEJK_SALER ", tmpTable3, tmpTable1, tmpTable2);
DBUtils.ExecuteDynamicObject(this.Context, sql3.ToString());
// ---------------------------------------------------------------------------------
// tmpTable4
StringBuilder sql4 = new StringBuilder();
sql4.AppendFormat(@"/*dialect*/ SELECT DEPTL.FNAME DEPTNAME, SALESMANL.FNAME SALERNAME, QUOTA, COMPLETEAMOUNT, CASE WHEN QUOTA = 0 THEN '0 %' ELSE CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar)+' %' END AS COMPLETERATE INTO {0} FROM {1} TMP1
LEFT JOIN V_BD_SALESMAN_L SALESMANL ON SALESMANL.FID = TMP1.F_PEJK_SALER
LEFT JOIN T_BD_DEPARTMENT_L DEPTL ON DEPTL.FDEPTID = TMP1.F_PEJK_SALEDEPT
LEFT JOIN T_BD_DEPARTMENT DEPT ON DEPTL.FDEPTID = DEPT.FDEPTID WHERE SALESMANL.FNAME IS NOT NULL ", tmpTable4, tmpTable3);
//判断销售员条件
if (dyFilter["F_QSNC_SalerFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"]).Count > 0)
{
sql1.AppendLine(" AND TMP1.F_PEJK_SALER ").Append(salerSql);
}
//销售员数据隔离
if (flag)
{
sql1.AppendLine(" AND TMP1.F_PEJK_SALER ").Append(salerLimit);
}
//判断销售部门条件
if (dyFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"]).Count > 0)
{
sql1.AppendLine(" AND TMP1.F_PEJK_SALEDEPT ").Append(deptSql);
}
DBUtils.ExecuteDynamicObject(this.Context, sql4.ToString());
StringBuilder sql5 = new StringBuilder();
sql5.AppendFormat(@"/*dialect*/INSERT INTO {0} SELECT '最终合计' DEPTNAME, '' SALERNAME, ISNULL(TOTALQUOTA, 0) TOTALQUOTA1, ISNULL(TOTALCOMPLETEAMOUNT, 0) TOTALCOMPLETEAMOUNT1, CASE WHEN TOTALQUOTA IS NULL THEN '0 %' ELSE CAST(CONVERT(FLOAT, ROUND((TOTALCOMPLETEAMOUNT * 1.00 / (TOTALQUOTA * 1.00)) * 100, 3)) as varchar) + ' %' END AS COMPLETERATE
FROM
(SELECT SUM(QUOTA) TOTALQUOTA, SUM(COMPLETEAMOUNT) TOTALCOMPLETEAMOUNT FROM {1}) TMP ", tmpTable4, tmpTable4);
DBUtils.ExecuteDynamicObject(this.Context, sql5.ToString());
StringBuilder sql6 = new StringBuilder();
sql6.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY DEPTNAME) AS FSeq, DEPTNAME, SALERNAME, CONVERT(FLOAT, ROUND(QUOTA, 2)) AS TOTALQUOTA, CONVERT(FLOAT, ROUND(COMPLETEAMOUNT, 2)) AS TOTALAMOUNT, COMPLETERATE INTO {0} ", tableName);
sql6.AppendFormat(" FROM {0} ", tmpTable4);
DBUtils.ExecuteDynamicObject(this.Context, sql6.ToString());
}
protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
{
var result = base.GetReportData(tablename, filter);
return(result);
}
//构建报表列
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
//通知单厂家
var tzdcj = header.AddChild("通知单厂家", new Kingdee.BOS.LocaleValue("通知单/厂家"));
tzdcj.ColIndex = 0;
//工序说明
var gxsm = header.AddChild("工序说明", new Kingdee.BOS.LocaleValue("工序说明"));
gxsm.ColIndex = 1;
//生产车间
var sccj = header.AddChild("生产车间", new Kingdee.BOS.LocaleValue("生产车间"));
sccj.ColIndex = 2;
//型号规格
var xhgg = header.AddChild("型号规格", new Kingdee.BOS.LocaleValue("型号规格"));
xhgg.ColIndex = 3;
//计划产量
var quota = header.AddChild("计划产量", new Kingdee.BOS.LocaleValue("计划产量"));
quota.ColIndex = 4;
//交货日期
var jhrq = header.AddChild("交货日期", new Kingdee.BOS.LocaleValue("交货日期"));
jhrq.ColIndex = 5;
//延长米
var ycm = header.AddChild("延长米", new Kingdee.BOS.LocaleValue("延长米"));
ycm.ColIndex = 6;
//子物料名称
var zwlmc = header.AddChild("子物料名称", new Kingdee.BOS.LocaleValue("子物料名称"));
zwlmc.ColIndex = 7;
//原纸规格
var yzgg = header.AddChild("原纸规格", new Kingdee.BOS.LocaleValue("原纸规格"));
yzgg.ColIndex = 8;
//原纸数量
var yzsl = header.AddChild("原纸数量", new Kingdee.BOS.LocaleValue("原纸数量"));
yzsl.ColIndex = 9;
//布板规格
var bbgg = header.AddChild("布板规格", new Kingdee.BOS.LocaleValue("布板规格"));
bbgg.ColIndex = 10;
//布板数量
var bbsl = header.AddChild("布板数量", new Kingdee.BOS.LocaleValue("布板数量"));
bbsl.ColIndex = 11;
//包材规格
var bcgg = header.AddChild("包材规格", new Kingdee.BOS.LocaleValue("包材规格"));
bcgg.ColIndex = 12;
//包材数量
var bcsl = header.AddChild("包材数量", new Kingdee.BOS.LocaleValue("包材数量"));
bcsl.ColIndex = 13;
//开始时间
var kssj = header.AddChild("开始时间", new Kingdee.BOS.LocaleValue("开始时间"));
kssj.ColIndex = 14;
//结束时间
var jssj = header.AddChild("结束时间", new Kingdee.BOS.LocaleValue("结束时间"));
jssj.ColIndex = 15;
//计划工时
var jhgs = header.AddChild("计划工时", new Kingdee.BOS.LocaleValue("计划工时"));
jhgs.ColIndex = 16;
//包装方式
var bzfs = header.AddChild("包装方式", new Kingdee.BOS.LocaleValue("包装方式"));
bzfs.ColIndex = 17;
//备注
var bz = header.AddChild("备注", new Kingdee.BOS.LocaleValue("备注"));
bz.ColIndex = 18;
return(header);
}
protected override string BuilderFromWhereSQL(IRptParams filter)
{
string result = base.BuilderFromWhereSQL(filter);
return(result);
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"/*dialect*/SELECT * INTO {0} ", tableName).Append("\n");
sql.AppendLine(" FROM (").Append("\n");
sql.AppendLine(" SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY 通知单号,通知单厂家) AS FSeq,").Append("\n");
sql = sql.AppendFormat(@"tmp.通知单号,tmp.通知单厂家,tmp.工序说明,tmp.生产车间,tmp.型号规格,tmp.计划产量,tmp.交货日期,tmp.延长米,tmp.子物料名称,tmp.原纸规格,tmp.原纸数量,tmp.布板规格,tmp.布板数量,tmp.包材规格,tmp.包材数量,tmp.开始时间,tmp.结束时间,tmp.计划工时,tmp.包装方式,tmp.备注
FROM (SELECT tmp0.FMONUMBER 通知单号,tmp1.FCREATEDATE 创建日期,tmp1.FNAME 生产车间,tmp0.FOPERDESCRIPTION 工序说明,tmp1.FMOBILLNO + '-' + CONVERT (CHAR, tmp1.khbm) 通知单厂家,tmp1.FSPECIFICATION 型号规格,tmp0.FOPERQTY 计划产量,tmp1.F_SCFG_DATETIME1 交货日期,tmp1.F_scfg_Qty1 延长米,tmp1.wlbm 子物料编码,tmp1.wlmc 子物料名称,tmp1.yzgg 原纸规格,tmp2.yzsl 原纸数量,tmp3.bbgg 布板规格,tmp4.bbsl 布板数量,tmp5.bcgg 包材规格,tmp6.bcsl 包材数量,NULL 开始时间,NULL 结束时间,NULL 计划工时,tmp1.fdatavalue 包装方式,NULL 备注
FROM
(
SELECT a.FMONUMBER,a.FMOENTRYSEQ,cl.FOPERDESCRIPTION,FOPERQTY
FROM
T_SFC_OPERPLANNING a
INNER JOIN T_SFC_OPERPLANNINGSEQ b ON a.fid = b.fid
INNER JOIN T_SFC_OPERPLANNINGDETAIL c ON b.FENTRYID = c.FENTRYID
INNER JOIN T_SFC_OPERPLANNINGDETAIL_L cl on c.FDETAILID = cl.FDETAILID
WHERE 1=1
--and a.FMONUMBER='12105'
) tmp0
INNER JOIN(
--原纸规格
SELECT
tpp.FMOBILLNO,tbml0.FSPECIFICATION,tpp.FWORKSHOPID,tpdl.FNAME,tbm.FNUMBER wlbm,tbml.FNAME wlmc,tbml.FSPECIFICATION AS yzgg,CONVERT (CHAR, tbc.FNUMBER) khbm,tpp.F_SCFG_DATETIME1,tpp.F_scfg_Qty1,tbal.fdatavalue,tpp.FCREATEDATE
FROM
T_PRD_PPBOM tpp
LEFT JOIN T_PRD_PPBOMENTRY tppe ON tpp.fid = tppe.fid
LEFT JOIN T_BD_MATERIAL tbm0 ON tpp.FMATERIALID = tbm0.FMATERIALID
INNER JOIN T_BD_MATERIAL_L tbml0 ON tbm0.FMATERIALID = tbml0.FMATERIALID
LEFT JOIN T_BD_MATERIAL tbm ON tppe.FMATERIALID = tbm.FMATERIALID
INNER JOIN T_BD_MATERIAL_L tbml ON tbm.FMATERIALID = tbml.FMATERIALID
LEFT JOIN T_BD_CUSTOMER tbc --客户
ON tbc.FCUSTID = tpp.F_SCFG_BASE
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L tbal --辅助资料
ON tpp.F_SCFG_ASSISTANT = tbal.fentryid
INNER JOIN T_BD_DEPARTMENT_L tpdl
ON tpp.FWORKSHOPID=tpdl.FDEPTID
WHERE
tbm.FNUMBER LIKE '01.01%'
-- and tpp.FMOBILLNO ='12105'
) tmp1
ON tmp0.FMONUMBER=tmp1.FMOBILLNO
INNER JOIN
--数量
(SELECT
tpp.FMOBILLNO,
SUM (tppe.FMUSTQTY) yzsl
FROM
T_PRD_PPBOM tpp
LEFT JOIN T_PRD_PPBOMENTRY tppe ON tpp.fid = tppe.fid
LEFT JOIN T_BD_MATERIAL tbm ON tppe.FMATERIALID = tbm.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L tbml ON tbm.FMATERIALID = tbml.FMATERIALID
WHERE
tbm.FNUMBER LIKE '01.01%'
-- and tpp.FMOBILLNO ='12105'
GROUP BY
tpp.FMOBILLNO) tmp2
ON tmp1.FMOBILLNO=tmp2.FMOBILLNO
INNER JOIN(
--布板规格
SELECT
tpp.FMOBILLNO,tbml.FSPECIFICATION AS bbgg
FROM
T_PRD_PPBOM tpp
LEFT JOIN T_PRD_PPBOMENTRY tppe ON tpp.fid = tppe.fid
LEFT JOIN T_BD_MATERIAL tbm ON tppe.FMATERIALID = tbm.FMATERIALID
INNER JOIN T_BD_MATERIAL_L tbml ON tbm.FMATERIALID = tbml.FMATERIALID
WHERE
tbm.FNUMBER LIKE '01.02%'
-- and tpp.FMOBILLNO ='12105'
) tmp3
ON tmp2.FMOBILLNO=tmp3.FMOBILLNO
LEFT JOIN
--布板数量
(SELECT
tpp.FMOBILLNO,
SUM (tppe.FMUSTQTY) bbsl
FROM
T_PRD_PPBOM tpp
LEFT JOIN T_PRD_PPBOMENTRY tppe ON tpp.fid = tppe.fid
LEFT JOIN T_BD_MATERIAL tbm ON tppe.FMATERIALID = tbm.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L tbml ON tbm.FMATERIALID = tbml.FMATERIALID
WHERE
tbm.FNUMBER LIKE '01.02%'
-- and tpp.FMOBILLNO ='12105'
GROUP BY
tpp.FMOBILLNO) tmp4
ON tmp3.FMOBILLNO=tmp4.FMOBILLNO
INNER JOIN(
--包材规格
SELECT
tpp.FMOBILLNO,tbml.FSPECIFICATION AS bcgg
FROM
T_PRD_PPBOM tpp
LEFT JOIN T_PRD_PPBOMENTRY tppe ON tpp.fid = tppe.fid
LEFT JOIN T_BD_MATERIAL tbm ON tppe.FMATERIALID = tbm.FMATERIALID
INNER JOIN T_BD_MATERIAL_L tbml ON tbm.FMATERIALID = tbml.FMATERIALID
WHERE
tbm.FNUMBER LIKE '03%'
-- and tpp.FMOBILLNO ='12105'
) tmp5
ON tmp4.FMOBILLNO=tmp5.FMOBILLNO
INNER JOIN
--包材数量
(SELECT
tpp.FMOBILLNO,
SUM (tppe.FMUSTQTY) bcsl
FROM
T_PRD_PPBOM tpp
LEFT JOIN T_PRD_PPBOMENTRY tppe ON tpp.fid = tppe.fid
LEFT JOIN T_BD_MATERIAL tbm ON tppe.FMATERIALID = tbm.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L tbml ON tbm.FMATERIALID = tbml.FMATERIALID
WHERE
tbm.FNUMBER LIKE '03%'
-- and tpp.FMOBILLNO ='12105'
GROUP BY
tpp.FMOBILLNO) tmp6
ON tmp5.FMOBILLNO=tmp6.FMOBILLNO
WHERE 1=1
");
if (dyFilter["F_scfg_prodIdFilterStr"] != null)
{
string prodId = Convert.ToString(dyFilter["F_scfg_prodIdFilterStr"]);//生产通知单号
sql.AppendFormat(" and tmp0.FMONUMBER='{0}'", prodId);
}
if (dyFilter["F_scfg_DeptIdFilter"] != null)
{
string deptId = Convert.ToString(dyFilter["F_scfg_DeptIdFilter_Id"]);//生产车间
sql.AppendFormat(" and tmp1.FWORKSHOPID='{0}'", deptId);
}
//判断统计日期是否有效
if (dyFilter["F_scfg_CreateDateFilter"] != null)
{
string createDateStart = Convert.ToDateTime(dyFilter["F_scfg_CreateDateFilter"]).ToString("yyyy-MM-dd 00:00:00");
string createDateEnd = Convert.ToDateTime(dyFilter["F_scfg_CreateDateFilter"]).ToString("yyyy-MM-dd 23:59:59");
sql.AppendFormat(" AND tmp1.FCREATEDATE >= '{0}' ", createDateStart);
sql.AppendFormat(" AND tmp1.FCREATEDATE <= '{0}' ", createDateEnd);
}
sql.AppendLine(") tmp");
sql.AppendLine(" ORDER BY tmp.通知单号,tmp.通知单厂家 DESC");
sql.AppendLine(") A");
DBUtils.Execute(this.Context, sql.ToString());
}
protected override string BuilderTempTableOrderBySQL(IRptParams filter)
{
string result = base.BuilderTempTableOrderBySQL(filter);
return(result);
}
//构建报表列
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
//首次录入时间
var firstDate = header.AddChild("firstDate", new Kingdee.BOS.LocaleValue("首次录入时间"));
firstDate.ColIndex = 0;
firstDate.Width = 100;
//更新日期
var updateDate = header.AddChild("updateDate", new Kingdee.BOS.LocaleValue("更新日期"));
updateDate.ColIndex = 1;
updateDate.Width = 100;
//区域
var area = header.AddChild("area", new Kingdee.BOS.LocaleValue("区域"));
area.ColIndex = 2;
area.Width = 150;
//负责人
header.AddChild("saler", new Kingdee.BOS.LocaleValue("负责人")).ColIndex = 3;
//部门
header.AddChild("department", new Kingdee.BOS.LocaleValue("部门")).ColIndex = 4;
//代理商
header.AddChild("agent", new Kingdee.BOS.LocaleValue("代理商")).ColIndex = 5;
//最终用户
header.AddChild("user", new Kingdee.BOS.LocaleValue("最终用户")).ColIndex = 6;
//设备使用地址
var address = header.AddChild("address", new Kingdee.BOS.LocaleValue("设备使用地址"));
address.ColIndex = 7;
address.Width = 150;
//产品类别
var category = header.AddChild("category", new Kingdee.BOS.LocaleValue("产品类别"));
category.ColIndex = 8;
category.Width = 150;
//产品型号
var model = header.AddChild("model", new Kingdee.BOS.LocaleValue("产品型号"));
model.ColIndex = 9;
model.Width = 150;
//特殊参数要求
var special = header.AddChild("special", new Kingdee.BOS.LocaleValue("特殊参数要求"));
special.ColIndex = 10;
special.Width = 150;
//台数
header.AddChild("count", new Kingdee.BOS.LocaleValue("台数")).ColIndex = 11;
//单价
header.AddChild("price", new Kingdee.BOS.LocaleValue("单价(万元)")).ColIndex = 12;
//总金额
header.AddChild("amount", new Kingdee.BOS.LocaleValue("总金额(万元)")).ColIndex = 13;
//项目进展
var progress = header.AddChild("progress", new Kingdee.BOS.LocaleValue("项目进展"));
progress.ColIndex = 14;
progress.Width = 150;
//丢单原因
var reason = header.AddChild("reason", new Kingdee.BOS.LocaleValue("丢单原因"));
reason.ColIndex = 15;
reason.Width = 150;
//下一步计划
var plan = header.AddChild("plan", new Kingdee.BOS.LocaleValue("下一步计划"));
plan.ColIndex = 16;
plan.Width = 150;
//主要竞争对手
var rival = header.AddChild("rival", new Kingdee.BOS.LocaleValue("主要竞争对手"));
rival.ColIndex = 17;
rival.Width = 150;
//竞争对手产品型号
var rivalModel = header.AddChild("rivalModel", new Kingdee.BOS.LocaleValue("竞争对手产品型号"));
rivalModel.ColIndex = 18;
rivalModel.Width = 150;
//竞争型号单价
header.AddChild("rivalPrice", new Kingdee.BOS.LocaleValue("竞争型号单价(万元)")).ColIndex = 19;
//预计下单时间
var orderDate = header.AddChild("orderDate", new Kingdee.BOS.LocaleValue("预计下单时间"));
orderDate.ColIndex = 20;
orderDate.Width = 100;
//备注
var remark = header.AddChild("remark", new Kingdee.BOS.LocaleValue("备注"));
remark.ColIndex = 21;
remark.Width = 150;
return(header);
}
public override System.Data.DataTable GetList(IRptParams filter)
{
var result = base.GetList(filter);
return(result);
}
//向临时表中插入报表数据
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
// 根据当前用户的UserId 查询出其personId
StringBuilder sql0 = new StringBuilder();
sql0.AppendFormat(@"/*dialect*/ SELECT FLINKOBJECT FROM T_SEC_USER WHERE FUSERID = {0} ", this.Context.UserId);
DynamicObjectCollection collection = DBUtils.ExecuteDynamicObject(this.Context, sql0.ToString());
StringBuilder salerLimit = new StringBuilder();
Boolean flag = false;
if (collection.Count > 0)
{
//获取当前用户personId
DynamicObject personIdObj = (DynamicObject)collection[0];
int personId = Convert.ToInt32(personIdObj["FLINKOBJECT"]);
//销售员数据隔离
if (CRMServiceHelper.getSalerPersonids(this.Context, personId) != null)
{
List <long> salerList = CRMServiceHelper.getSalerPersonids(this.Context, personId);
int len = 0;
flag = true;
if (salerList.Count >= 1)
{
salerLimit.Append(" IN ( ");
}
foreach (long salerId in salerList)
{
len++;
if (len == salerList.Count)
{
salerLimit.Append(" " + salerId + " ) ");
}
else
{
salerLimit.Append(" " + salerId + ", ");
}
}
}
}
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
//部门
StringBuilder deptSql = new StringBuilder();
if (customFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)customFilter["F_QSNC_DeptFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols2 = (DynamicObjectCollection)customFilter["F_QSNC_DeptFilter"];
int deptNum = 0;
if (cols2.Count >= 1)
{
deptSql.Append(" IN (");
}
foreach (DynamicObject dept in cols2)
{
String deptNumber = Convert.ToString(((DynamicObject)dept["F_QSNC_DeptFilter"])["Number"]);
deptNum++;
if (cols2.Count == deptNum)
{
deptSql.Append("'" + deptNumber + "')");
}
else
{
deptSql.Append("'" + deptNumber + "', ");
}
}
}
StringBuilder sql = new StringBuilder();
sql.AppendLine(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY CLUE.FCREATEDATE) AS FSeq, CONVERT(varchar(100), CLUE.FCREATEDATE, 23) AS 'firstDate', "); //首次录入时间
sql.AppendLine(" CONVERT(varchar(100), OPP.FMODIFYDATE, 23) AS 'updateDate', "); //更新日期
sql.AppendLine(" (SELECT (ASS1.FDATAVALUE + ' ' + ASS2.FDATAVALUE) ");
sql.AppendLine(" FROM T_BD_CUSTOMEREXT CUSTEXT ");
sql.AppendLine(" LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L ASS1 ");
sql.AppendLine(" ON ASS1.FENTRYID = CUSTEXT.FPROVINCE ");
sql.AppendLine(" LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L ASS2 ");
sql.AppendLine(" ON ASS2.FENTRYID = CUSTEXT.FCITY ");
sql.AppendLine(" WHERE ASS1.FLOCALEID = 2052 AND ASS2.FLOCALEID = 2052 AND CUSTEXT.FCUSTID = OPP.FCUSTOMERID) AS 'area', "); // CRM客户--区域
sql.AppendLine(" EMPL.FNAME AS 'saler', "); //负责人
sql.AppendLine(" DEPTL.FNAME AS 'department', "); //部门
sql.AppendLine(" CUST.F_PEJK_AGENT AS 'agent', "); //代理商
sql.AppendLine(" FINUSER.FNAME AS 'user', "); //最终用户
sql.AppendLine(" F_PEJK_SBSYDZ AS 'address', "); //设备使用地址
sql.AppendLine(" F_PEJK_CRMPRONAME AS 'category', "); //产品类别
sql.AppendLine(" F_PEJK_GGXH AS 'model', "); //产品型号
sql.AppendLine(" F_PEJK_SPECPARAM AS 'special', "); //特殊参数要求
sql.AppendLine(" CAST(F_PEJK_QTY AS INT) AS 'count', "); //台数(整数)
sql.AppendLine(" (CONVERT(FLOAT, F_PEJK_PRICE, 2) / 10000) AS 'price', "); //单价
sql.AppendLine(" (CONVERT(FLOAT, F_PEJK_AMOUNT, 2) / 10000) AS 'amount', "); //总金额
sql.AppendLine(" PROJECTPRO.FNAME AS 'progress', "); //项目进展
sql.AppendLine(" F_PEJK_DDRESON AS 'reason', "); //丢单原因
sql.AppendLine(" F_PEJK_XYBJH AS 'plan', "); //下一步计划
sql.AppendLine(" F_PEJK_ZZJZDS AS 'rival', "); //主要竞争对手
sql.AppendLine(" F_PEJK_JZDSCPXH AS 'rivalModel', "); //竞争对手产品型号
sql.AppendLine(" (CONVERT(FLOAT, F_PEJK_JZPRICE, 2) / 10000) AS 'rivalPrice', "); //竞争型号单价
sql.AppendLine(" F_PEJK_DATE AS 'orderDate', "); //预计下单时间
sql.AppendLine(" F_PEJK_REMARKDETAIL AS 'remark' "); //备注
sql.AppendFormat(" INTO {0} ", tableName);
sql.AppendLine(" FROM T_CRM_OpportunityProduct OPPPRO ");
sql.AppendLine(" LEFT JOIN T_CRM_Opportunity OPP ");
sql.AppendLine(" ON OPPPRO.FID = OPP.FID ");
sql.AppendLine(" LEFT JOIN PEJK_PROJECTPROCESS_L PROJECTPRO ");
sql.AppendLine(" ON PROJECTPRO.FID = OPPPRO.F_PEJK_PROPROCESS ");
sql.AppendLine(" LEFT JOIN PEJK_FINALU_L FINUSER ");
sql.AppendLine(" ON FINUSER.FID = OPPPRO.F_PEJK_FINALU ");
sql.AppendLine(" LEFT JOIN T_CRM_Clue CLUE ");
sql.AppendLine(" ON OPP.FSOURCEBILLNO = CLUE.FBILLNO ");
sql.AppendLine(" LEFT JOIN T_CRM_Clue_Cust CUST ");
sql.AppendLine(" ON CLUE.FID = CUST.FID ");
sql.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ");
sql.AppendLine(" ON SALESMAN.FID = OPP.FBEMPID ");
sql.AppendLine(" LEFT JOIN T_BD_STAFF STAFF ");
sql.AppendLine(" ON STAFF.FSTAFFID = SALESMAN.FSTAFFID ");
sql.AppendLine(" INNER JOIN T_HR_EMPINFO EMP ");
sql.AppendLine(" ON STAFF.FEMPINFOID = EMP.FID ");
sql.AppendLine(" LEFT JOIN T_HR_EMPINFO_L EMPL ");
sql.AppendLine(" ON EMPL.FID = EMP.FID ");
sql.AppendLine(" LEFT JOIN T_BD_DEPARTMENT_L DEPTL ");
sql.AppendLine(" ON DEPTL.FDEPTID = SALESMAN.FDEPTID ");
sql.AppendLine(" LEFT JOIN T_BD_DEPARTMENT DEPT ON DEPTL.FDEPTID = DEPT.FDEPTID ");
sql.AppendLine(" where EMPL.FLOCALEID = 2052 AND DEPTL.FLOCALEID = 2052 AND PROJECTPRO.FLOCALEID = 2052 AND FINUSER.FLOCALEID = 2052 ");
//判断销售部门条件
if (customFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)customFilter["F_QSNC_DeptFilter"]).Count > 0)
{
sql.AppendLine(" AND DEPT.FNUMBER").Append(deptSql);
}
if (flag)
{
sql.AppendLine(" and SALESMAN.FID ").Append(salerLimit);
}
DBUtils.ExecuteDynamicObject(this.Context, sql.ToString());
}
public override List <Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
{
var result = base.GetTreeNodes(filter);
return(result);
}
/// <summary>
/// 构建出报表列
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
/// <remarks>
/// // 如下代码，演示如何设置同一分组的分组头字段合并
/// // 需配合Initialize事件，设置分组依据字段(PrimaryKeyFieldName)
/// ReportHeader header = new ReportHeader();
/// header.Mergeable = true;
/// int width = 80;
/// ListHeader headChild1 = header.AddChild("FBILLNO", new LocaleValue("应付单号"));
/// headChild1.Width = width;
/// headChild1.Mergeable = true;
///
/// ListHeader headChild2 = header.AddChild("FPURMAN", new LocaleValue("采购员"));
/// headChild2.Width = width;
/// headChild2.Mergeable = true;
/// </remarks>
public override ReportHeader GetReportHeaders(IRptParams filter)
{
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
ReportHeader header = new ReportHeader();
// 序号
var FID = header.AddChild("FIDENTITYID", new LocaleValue("序号"));
FID.ColIndex = 0;
// 单据编码
var billNo = header.AddChild("工序", new LocaleValue("工序"));
billNo.ColIndex = 1;
// 打印记录号
var FSTOCKORGID = header.AddChild("打印记录号", new LocaleValue("打印记录号"));
FSTOCKORGID.ColIndex = 2;
// 打印人
var FMaterialCode = header.AddChild("打印人N", new LocaleValue("打印人"));
FMaterialCode.ColIndex = 3;
// 日期
var FMaterialName = header.AddChild("日期", new LocaleValue("日期"), SqlStorageType.SqlDatetime);
FMaterialName.ColIndex = 4;
// 厚度
var FSpecification = header.AddChild("厚度", new LocaleValue("厚度"), SqlStorageType.SqlDecimal);
FSpecification.ColIndex = 5;
// 宽度
var FSTOCKID = header.AddChild("宽度", new LocaleValue("宽度"), SqlStorageType.SqlDecimal);
FSTOCKID.ColIndex = 6;
//长度
var FSTOCKNAME = header.AddChild("长度", new LocaleValue("长度"), SqlStorageType.SqlDecimal);
FSTOCKNAME.ColIndex = 7;
// 面积
var FLOT = header.AddChild("面积", new LocaleValue("面积"), SqlStorageType.SqlDecimal);
FLOT.ColIndex = 8;
// 生产订单号
var FOWNERID = header.AddChild("生产订单号", new LocaleValue("生产订单号"));
FOWNERID.ColIndex = 9;
// 生产订单行号
var FMINDATE = header.AddChild("生产订单行号", new LocaleValue("生产订单行号"), SqlStorageType.SqlDecimal);
FMINDATE.ColIndex = 10;
// 客户
var FSTOCKQTY = header.AddChild("客户", new LocaleValue("客户"));
FSTOCKQTY.ColIndex = 13;
return(header);
}
protected override string AnalyzeDspCloumn(IRptParams filter, string tablename)
{
return(base.AnalyzeDspCloumn(filter, tablename));
}
protected override string AnalyzeDspCloumn(IRptParams filter, string tablename)
{
string result = base.AnalyzeDspCloumn(filter, tablename);
return(result);
}
protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
{
return(base.GetReportData(tablename, filter));
}
protected override string BuilderSelectFieldSQL(IRptParams filter)
{
return(base.BuilderSelectFieldSQL(filter));
}
