public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//创建临时表
IDBService dbService = Kingdee.BOS.App.ServiceHelper.GetService <IDBService>();
temps = dbService.CreateTemporaryTableName(this.Context, 1);
string temp = temps[0];
//调用基类方法，获取初步查询结果到临时表
base.BuilderReportSqlAndTempTable(filter, temp);
//对标准报表所查询的数据进行加工
string sql = string.Format(@"/*dialect*/
SELECT
T1.*
,T2.FOLDNUMBER --旧物料编码
,T2.FCUSTPRODUCTNO FCUSTMATNO --客户货号
,T2.FQYPACKINGQTY FPACKINGQTY --装箱量
,CASE
WHEN T2.FQYPACKINGQTY = 0 THEN 0
ELSE CEILING(T1.FSTOCKJCQTY / T2.FQYPACKINGQTY)
END FJCCARTONQTY --结存箱数 = 结存数量(库存) / 装箱量
INTO {0}
FROM {1} T1
LEFT JOIN T_BD_MATERIAL T2
ON T1.FMATERIALID = T2.FMATERIALID
", tableName, temp);
DBUtils.Execute(this.Context, sql);
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//创建临时表
IDBService dbService = Kingdee.BOS.App.ServiceHelper.GetService <IDBService>();
temps = dbService.CreateTemporaryTableName(this.Context, 1);
string temp = temps[0];
//调用基类方法，获取初步查询结果到临时表
base.BuilderReportSqlAndTempTable(filter, temp);
//对标准报表所查询的数据进行加工
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("/*dialect*/ ");
sqlBuilder.AppendLine(" SELECT ");
sqlBuilder.AppendLine(" T1.* ");
sqlBuilder.AppendLine(" ,CASE ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_Payable' THEN T2.FAPPROVEDATE ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' THEN T3.FAPPROVEDATE ");
sqlBuilder.AppendLine(" ELSE NULL ");
sqlBuilder.AppendLine(" END FAUDITDATE --审核日期 ");
sqlBuilder.AppendLine(" INTO " + tableName + " ");
sqlBuilder.AppendLine(" FROM " + temp + " T1 ");
sqlBuilder.AppendLine(" --应付单 ");
sqlBuilder.AppendLine(" LEFT JOIN T_AP_PAYABLE T2 ");
sqlBuilder.AppendLine(" ON T2.FID = T1.FID ");
sqlBuilder.AppendLine(" --其他应付单 ");
sqlBuilder.AppendLine(" LEFT JOIN T_AP_OTHERPAYABLE T3 ");
sqlBuilder.AppendLine(" ON T3.FID = T1.FID ");
sqlBuilder.AppendLine(" WHERE 1 = 1 ");
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
if (filter.FilterParameter.CustomFilter != null)
{
//起始日期
beginAuditDate = Convert.ToDateTime(dyFilter["FBeginAuditDate_Filter"]);
//截止日期
endAuditDate = Convert.ToDateTime(dyFilter["FEndAuditDate_Filter"]);
if (beginAuditDate != DateTime.MinValue && endAuditDate != DateTime.MinValue)
{
sqlBuilder.AppendLine(" AND CASE WHEN T1.FFORMID = 'AP_Payable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T2.FAPPROVEDATE) >= 0 AND DATEDIFF(DAY, T2.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T3.FAPPROVEDATE) >= 0 AND DATEDIFF(DAY, T3.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ELSE 0 END = 1 ");
}
if (beginAuditDate != DateTime.MinValue && endAuditDate == DateTime.MinValue)
{
sqlBuilder.AppendLine(" AND CASE WHEN T1.FFORMID = 'AP_Payable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T2.FAPPROVEDATE) >= 0 THEN 1 ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' AND DATEDIFF(DAY, '" + beginAuditDate + "', T3.FAPPROVEDATE) >= 0 THEN 1 ELSE 0 END = 1 ");
}
if (beginAuditDate == DateTime.MinValue && endAuditDate != DateTime.MinValue)
{
sqlBuilder.AppendLine(" AND CASE WHEN T1.FFORMID = 'AP_Payable' AND DATEDIFF(DAY, T2.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ");
sqlBuilder.AppendLine(" WHEN T1.FFORMID = 'AP_OtherPayable' AND DATEDIFF(DAY, T3.FAPPROVEDATE, '" + endAuditDate + "') >= 0 THEN 1 ELSE 0 END = 1 ");
}
}
DBUtils.Execute(this.Context, sqlBuilder.ToString());
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
//创建临时表用于存放基类产生的数据
IDBService dbSrv = ServiceFactory.GetDBService(this.Context);
tmpTbName = dbSrv.CreateTemporaryTableName(this.Context);
//调用基类方法并将数据存入临时表
base.BuilderReportSqlAndTempTable(filter, tmpTbName);
var sql = string.Format(@"
select t.*, cl.FName as FCurrencyName into {0}
from {1} t
left join T_BD_CURRENCY_L cl on t.FLOCALCURRID=cl.FCURRENCYID and cl.FLOCALEID=2052", tableName, tmpTbName);
DBUtils.Execute(this.Context, sql);
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//创建临时表，用于存放自己的数据
IDBService dbservice = ServiceHelper.GetService <IDBService>();
materialRptTableNames = dbservice.CreateTemporaryTableName(this.Context, 1);
string strTable = materialRptTableNames[0];
//调用基类的方法，获取初步查询的结果到临时表
base.BuilderReportSqlAndTempTable(filter, strTable);
//对初步查询的结果进行处理，然后写回基类默认的存储查询结果临时表
string strSql = string.Format(@"SELECT t1.*,
CASE
WHEN T1.FFORMID = 'STK_MisDelivery' THEN MIS.FPICKERID
WHEN T1.FFORMID = 'PRD_PickMtrl' THEN MTRL.FPICKERID
WHEN T1.FFORMID = 'PRD_FeedMtrl' THEN FEED.FPICKERID
ELSE NULL
END FPICKERID, --领料人
CASE
WHEN T1.FFORMID = 'PRD_ReturnMtrl' THEN RET.FRETURNERID
ELSE NULL
END FRETURNERID, ----退料人
CASE
WHEN T1.FFORMID = 'PRD_PickMtrl' THEN MTRL.F_KD_WLBM
WHEN T1.FFORMID = 'PRD_FeedMtrl' THEN FEED.F_KD_BASE
WHEN T1.FFORMID = 'PRD_ReturnMtrl' THEN RET.F_KD_WLBM
ELSE NULL
END F_Ls_Material, --主产品物料
CASE
WHEN T1.FFORMID = 'PRD_PickMtrl' THEN MTRL.F_kd_CPPJ
WHEN T1.FFORMID = 'PRD_FeedMtrl' THEN FEED.F_kd_Text
WHEN T1.FFORMID = 'PRD_ReturnMtrl' THEN RET.F_kd_CPPH
ELSE NULL
END F_LS_CPPH --主产品批号
into {0}
FROM {1} T1
LEFT JOIN T_STK_MISDELIVERY MIS
ON T1.FBILLID = MIS.FID
LEFT JOIN T_PRD_PICKMTRL MTRL
ON MTRL.FID = T1.FBILLID
LEFT JOIN T_PRD_FEEDMTRL FEED
ON T1.FBILLID = FEED.FID
LEFT JOIN T_PRD_RETURNMTRL RET
ON T1.FBILLID = RET.FID", tableName, strTable);
DBUtils.Execute(this.Context, strSql);
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
//创建临时表用于存放基类产生的数据
IDBService dbSrv = ServiceFactory.GetDBService(Context);
tmpTbName = dbSrv.CreateTemporaryTableName(Context);
//调用基类方法并将数据存入临时表
base.BuilderReportSqlAndTempTable(filter, tmpTbName);
var sql = string.Format(@"
SELECT t.*, cl.FName AS FCurrencyName INTO {0}
FROM {1} t
INNER JOIN T_PUR_POORDERFIN t2 on (t.FID = t2.FID)
LEFT JOIN T_BD_CURRENCY_L cl on t2.FLOCALCURRID=cl.FCURRENCYID and cl.FLOCALEID=2052", tableName, tmpTbName);
DBUtils.Execute(Context, sql);
}
//public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
//{
// base.BuilderReportSqlAndTempTable(filter, tableName);
// string strSql = "update TMP146B7C23AC5411E989ADD0C6375 set ";
// DynamicObject customFil = filter.FilterParameter.CustomFilter;
// string sWhere = string.Empty;
// sWhere = string.Format("Where FNumber = '{0}' AND FText = '{1}'", customFil["FNumber"], customFil["Ftext"]);
// DBUtils.Execute(this.Context,strSql);
//}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
//base.BuilderReportSqlAndTempTable(filter, tableName);
//创建临时表，用于存放自己的数据
IDBService dbservice = Kingdee.BOS.App.ServiceHelper.GetService <IDBService>();
customRptTempTableNames = dbservice.CreateTemporaryTableName(this.Context, 1);
string strTable = customRptTempTableNames[0];
//调用基类方法，获取初步查询结果到时临时表
base.BuilderReportSqlAndTempTable(filter, strTable);
//获取快捷页签上的字段数据包
DynamicObject customFil = filter.FilterParameter.CustomFilter;
//过滤条件
string sWhere = string.Empty;
//创建人不为空时
if (customFil["F_WWW_Text"] != null)
{
string supplierNmae = customFil["F_WWW_Text"].ToString();
sWhere = string.Format(" where t1.fsuppliergroupname='{0}' ", supplierNmae);
}
//对初步查询结果进行处理，然后写回基类默认的存放查询结果的临时表
StringBuilder sb = new StringBuilder();
string strSql = "/*dialect*/Select t1.* into {0} "
+ " from {1} t1 ";
if (!sWhere.IsNullOrEmpty())
{
strSql = strSql + sWhere;
}
sb.AppendFormat(strSql, tableName, strTable);
DBUtils.Execute(this.Context, sb.ToString());
}
public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
{
//创建临时表
IDBService dbService = Kingdee.BOS.App.ServiceHelper.GetService <IDBService>();
temps = dbService.CreateTemporaryTableName(this.Context, 1);
string temp = temps[0];
//调用基类方法，获取初步查询结果到临时表
base.BuilderReportSqlAndTempTable(filter, temp);
//对标准报表所查询的数据进行加工
string sql = string.Format(@"/*dialect*/
SELECT
T1.*
,T2.FCUSTPRODUCTNO FCUSTMATNO --客户货号
INTO {0}
FROM {1} T1
LEFT JOIN T_BD_MATERIAL T2
ON T1.FMATERIALBASEID = T2.FMATERIALID
", tableName, temp);
DBUtils.Execute(this.Context, sql);
}
/**
* @param filter 过滤条件
* @param string tableName, 目标临时表名称
* **/
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
//创建临时表， 用于存放自己的数据
IDBService dbserver = Kingdee.BOS.App.ServiceHelper.GetService <IDBService>();
string[] customRptTempTableName = dbserver.CreateTemporaryTableName(this.Context, 1);
string strTable = customRptTempTableName[0];
//调用基类的方法把初步查询的数据存放到临时表
base.BuilderReportSqlAndTempTable(filter, strTable);
List <string> list = new List <string>();
using (new SessionScope())
{
list.AddRange(this.MyBuildSumRptData(tableName, strTable));
StringBuilder sb = new StringBuilder();
foreach (string value in list)
{
sb.Append(value);
}
File.WriteAllText("C:\\Users\\Administrator\\Desktop\\debug\\debug.sql", sb.ToString());
DBUtils.ExecuteBatch(base.Context, list, 30);
}
}
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"]);
}
StringBuilder deptnumbersql = new StringBuilder();
if (customFilter["F_PAEZ_DEPT"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)customFilter["F_PAEZ_DEPT"];
int deptsize = 0;
if (cols.Count >= 1)
{
deptnumbersql.Append("in (");
}
foreach (DynamicObject dept in cols)
{
String deptnumber = Convert.ToString(((DynamicObject)dept["F_PAEZ_DEPT"])["Id"]);
deptsize = deptsize + 1;
if (deptsize == cols.Count)
{
deptnumbersql.Append("'" + deptnumber + "')");
}
else
{
deptnumbersql.Append("'" + deptnumber + "',");
}
}
}
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 "+ T_CRM_Clue +" prod on prod.FID=opp.FID ");
sql.AppendFormat(" inner join T_CRM_Clue Clue on Clue.FBillno=opp.FSOURCEBILLNO and F_PEJK_FINALUSER={0} ", endUserID);
}
sql.Append(" where opp.FDOCUMENTSTATUS >='C' ");
sql.Append(" and opp.FSTARTDATE 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 +"' ");
//}
//部门
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
sql.Append(" and opp.FSALEDEPTID ").Append(deptnumbersql);
}
sql.Append(" group by cust.FNUMBER,cust_l.FNAME ");
sql.Append(" ) tt ");
DBUtils.ExecuteDynamicObject(this.Context, sql.ToString());
}
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>();
tmpTables = dbservice.CreateTemporaryTableName(this.Context, 1);
string tempTable1 = tmpTables[0];
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
string fromDate = String.Empty;
string toDate = String.Empty;
string deptID = String.Empty;
string salerID = String.Empty;
string billStatus = String.Empty;
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"]);
}
//if (customFilter["F_xy_Dept"] != null) deptID = customFilter["F_xy_Dept_Id"].ToString();
//if (customFilter["F_xy_Saler"] != null) salerID = customFilter["F_xy_Saler_Id"].ToString();
if (customFilter["F_xy_BillStatus"] != null)
{
billStatus = customFilter["F_xy_BillStatus"].ToString();
}
Boolean hasDept = !String.IsNullOrEmpty(deptID);
Boolean hasSaler = !String.IsNullOrEmpty(salerID);
Boolean hasBillStatus = !String.IsNullOrEmpty(billStatus);
StringBuilder deptnumbersql = new StringBuilder();
if (customFilter["F_PAEZ_DEPT"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)customFilter["F_PAEZ_DEPT"];
int deptsize = 0;
if (cols.Count >= 1)
{
deptnumbersql.Append("in (");
}
foreach (DynamicObject dept in cols)
{
String deptnumber = Convert.ToString(((DynamicObject)dept["F_PAEZ_DEPT"])["Id"]);
deptsize = deptsize + 1;
if (deptsize == cols.Count)
{
deptnumbersql.Append("'" + deptnumber + "')");
}
else
{
deptnumbersql.Append("'" + deptnumber + "',");
}
}
}
StringBuilder billstatussql = new StringBuilder();
StringBuilder salenumbersql = new StringBuilder();
if (customFilter["F_PAEZ_MulBaseSaler"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)customFilter["F_PAEZ_MulBaseSaler"];
int salesize = 0;
if (cols.Count >= 1)
{
salenumbersql.Append("in (");
}
foreach (DynamicObject onesale in cols)
{
String salenumber = Convert.ToString(((DynamicObject)onesale["F_PAEZ_MulBaseSaler"])["Id"]);
salesize = salesize + 1;
if (salesize == cols.Count)
{
salenumbersql.Append("'" + salenumber + "')");
}
else
{
salenumbersql.Append("'" + salenumber + "',");
}
}
}
StringBuilder sql = new StringBuilder();
sql.Append(" select ROW_NUMBER() OVER(ORDER BY deptNO,empName) FIDENTITYID, ");
sql.Append(" deptName,empName, ");
sql.Append(" ISNULL(oppTotalCount,0) oppTotalCount, ");
sql.Append(" ISNULL(oppRegCount,0) oppRegCount, ");
sql.Append(" ISNULL(oppWinBillCount,0) oppWinBillCount, ");
sql.Append(" ISNULL(oppLostBillCount,0) oppLostBillCount, ");
sql.Append(" ISNULL(oppWinBillIncome,0) oppWinBillIncome, ");
sql.Append(" ISNULL(oppLostBillIncome,0) oppLostBillIncome, ");
sql.Append(" ISNULL(oppTotalBillIncome,0) oppTotalBillIncome, ");
sql.Append(" ISNULL(oppQuota,0) oppQuota, ");
sql.Append(" ISNULL(oppUnfinishedCount,0) oppUnfinishedCount ");
sql.Append(" into " + tableName);
sql.Append(" from ( ");
sql.Append(" select empInfo.*,oppStat.*, ");
sql.Append(" oppQuota.F_PEJK_OPPQUNTA oppQuota,oppQuota.F_PEJK_OPPQUNTA-oppStat.oppTotalCount oppUnfinishedCount ");
//商机统计信息
sql.Append(" from ( ");
sql.Append(" select opp.FBEMPID, ");
sql.Append(" count(1) oppTotalCount, "); //需要确认
//sql.Append(" count(1) oppRegCount, ");
sql.Append(" count(case when opp.FBEMPID = opp.FCREATORID then 1 else 0 end) oppRegCount, ");
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(case when opp.FDOCUMENTSTATUS='F' then opp.FESTIMATEINCOME else 0 end) oppLostBillIncome, ");
sql.Append(" sum(opp.FESTIMATEINCOME) oppTotalBillIncome ");
sql.Append(" from T_CRM_OPPORTUNITY opp ");
if (hasBillStatus)
{
sql.Append(" where CHARINDEX(opp.FDOCUMENTSTATUS, '" + billStatus + "') > 0 ");
}
else
{
sql.Append(" where opp.FDOCUMENTSTATUS>='C' ");
}
if (hasDept)
{
sql.Append(" and opp.FSALEDEPTID='"+ deptID + "' ");
}
if (hasSaler)
{
sql.Append(" and opp.FBEMPID='"+ salerID + "' ");
}
//部门
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
sql.Append(" and opp.FSALEDEPTID ").Append(deptnumbersql);
}
//销售员
if (salenumbersql != null && salenumbersql.Length > 0)
{
sql.Append(" and opp.FBEMPID ").Append(salenumbersql);
}
//销售数据隔离
if (flag)
{
sql.AppendLine(" and opp.FBEMPID ").Append(salerLimit);
}
sql.Append(" and opp.FSTARTDATE between '"+ fromDate + "' and '" + toDate + "' ");
sql.Append(" group by opp.FBEMPID ");
sql.Append(" ) oppStat ");
//销售指标
sql.Append(" left join ( ");
sql.Append(" select quota_entry.F_PEJK_SALER,quota_entry.F_PEJK_OPPQUNTA from PEJK_SALERQUNTA quota ");
sql.Append(" inner join PEJK_SALERQUNTAENTRY quota_entry on quota.FID=quota_entry.FID ");
sql.Append(" where quota.FDOCUMENTSTATUS='C' ");
//if (hasDept)
//{
// sql.Append(" and quota.F_PEJK_SALEDEPT='" + deptID + "' ");
//}
if (hasSaler)
{
sql.Append(" and quota_entry.F_PEJK_SALER='"+ salerID + "' ");
}
//部门
//if (deptnumbersql != null && deptnumbersql.Length > 0)
//{
// sql.Append(" and quota.F_PEJK_SALEDEPT ").Append(deptnumbersql);
//}
//销售员
if (salenumbersql != null && salenumbersql.Length > 0)
{
sql.Append(" and quota_entry.F_PEJK_SALER ").Append(salenumbersql);
}
//销售数据隔离
if (flag)
{
sql.AppendLine(" and quota_entry.F_PEJK_SALER ").Append(salerLimit);
}
sql.Append(" and Year(quota.F_PEJK_YEAR)=Year('"+ fromDate + "') ");
sql.Append(" ) oppQuota on oppStat.FBEMPID=oppQuota.F_PEJK_SALER ");
//人员信息
sql.Append(" right join ( ");
sql.Append(" select distinct dept.FNUMBER deptNO,deptl.FNAME deptName,saler.fid salerID,empl.fname empName ");
sql.Append(" from T_CRM_Opportunity opp ");
sql.Append(" inner join V_BD_SALESMAN saler ");
sql.Append(" on opp.FBEMPID = saler.fid ");
sql.Append(" inner join T_BD_STAFF staff on saler.FSTAFFID=staff.FSTAFFID ");
sql.Append(" inner join T_HR_EMPINFO_L empl on empl.FID=staff.FEMPINFOID and empl.FLOCALEID='2052' ");
sql.Append(" inner join T_BD_DEPARTMENT_L deptl on opp.FSALEDEPTID=deptl.FDEPTID and deptl.FLOCALEID='2052' ");
sql.Append(" inner join T_BD_DEPARTMENT dept on deptl.FDEPTID=dept.FDEPTID and dept.FDOCUMENTSTATUS='C' ");
sql.Append(" where saler.FDOCUMENTSTATUS='C' ");
sql.Append(" and saler.FISUSE=1 ");
sql.Append(" and saler.FFORBIDSTATUS='A' ");
//sql.Append(" and saler.FBIZORGID='100041' "); //需要确认
if (hasDept)
{
sql.Append(" and opp.FSALEDEPTID='" + deptID + "' ");
}
if (hasSaler)
{
sql.Append(" and saler.fid='" + salerID + "' ");
}
//部门
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
sql.Append(" and opp.FSALEDEPTID ").Append(deptnumbersql);
}
//销售员
if (salenumbersql != null && salenumbersql.Length > 0)
{
sql.Append(" and saler.fid ").Append(salenumbersql);
}
//销售数据隔离
if (flag)
{
sql.AppendLine(" and saler.fid ").Append(salerLimit);
}
sql.Append(" ) empInfo on oppStat.FBEMPID=empInfo.salerID ");
sql.Append(" ) tt ");
DBUtils.ExecuteDynamicObject(this.Context, sql.ToString());
}
//执行查询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());
}
//向临时表插入报表数据
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 flag0 = 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;
flag0 = 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, 4);
string tmpTable1 = materialRptTableNames[0];
string tmpTable2 = materialRptTableNames[1];
string tmpTable3 = materialRptTableNames[2];
string tmpTable4 = materialRptTableNames[3];
//过滤条件：起始日期/截至日期/部门/业务员
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
String startDate = ""; //起始日期
String endDate = ""; //截至日期
bool flag = false;
//业务员
StringBuilder salerSql = new StringBuilder();
if (dyFilter["F_QSNC_SalesmanFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols1 = (DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"];
int salerNum = 0;
if (cols1.Count >= 1)
{
salerSql.Append(" IN (");
}
foreach (DynamicObject saler in cols1)
{
String salerNumber = Convert.ToString(((DynamicObject)saler["F_QSNC_SalesmanFilter"])["Number"]);
salerNum++;
if (cols1.Count == salerNum)
{
salerSql.Append("'" + salerNumber + "')");
}
else
{
salerSql.Append("'" + salerNumber + "', ");
}
}
}
//部门
StringBuilder deptSql = new StringBuilder();
if (dyFilter["F_QSNC_DepartmentFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols2 = (DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"];
int deptNum = 0;
if (cols2.Count >= 1)
{
deptSql.Append(" IN (");
}
foreach (DynamicObject dept in cols2)
{
String deptNumber = Convert.ToString(((DynamicObject)dept["F_QSNC_DepartmentFilter"])["Number"]);
deptNum++;
if (cols2.Count == deptNum)
{
deptSql.Append("'" + deptNumber + "')");
}
else
{
deptSql.Append("'" + deptNumber + "', ");
}
}
}
//查询出商机中所有的执行部门id -- finish
StringBuilder sql1 = new StringBuilder();
sql1.AppendFormat(@"/*dialect*/ select oppexedept.F_PEJK_EXECUTEDEPTID as exedeptid into {0} ", tmpTable1);
sql1.AppendLine(" from PEJK_OPP_ExecuteDept oppexedept ");
sql1.AppendLine(" left join T_CRM_Opportunity opp ");
sql1.AppendLine(" on oppexedept.FID = opp.FID ");
DBUtils.ExecuteDynamicObject(this.Context, sql1.ToString());
//根据商机中的执行部门，查找每个部门下销售员的 线索数量/商机数量/转化率
StringBuilder sql2 = new StringBuilder();
sql2.AppendFormat(@"/*dialect*/ select deptl.FDEPTID deptid, tmp.salerid salerid, cluenumber, oppnumber, convert(float,round((oppnumber * 1.00 / (cluenumber * 1.00)) * 100, 2)) as conversionrate into {0} ", tmpTable2);
sql2.AppendLine(" from(select salerid, count(cluetmp.salerid) cluenumber, sum(cluetmp.status) oppnumber from ");
sql2.AppendLine(" (select clue.FSALERID as salerid, ");
sql2.AppendLine(" case when clue.FBILLNO in (select opp.FSOURCEBILLNO from T_CRM_Opportunity opp where opp.FBEMPID = clue.FSALERID) then 1 else 0 end as status ");
sql2.AppendLine(" from T_CRM_Clue clue where 1 = 1 ");
//判断起始日期是否有效
if (dyFilter["f_qsnc_startdatefilter"] != null)
{
startDate = Convert.ToDateTime(dyFilter["f_qsnc_startdatefilter"]).ToString("yyyy-MM-dd 00:00:00");
sql2.AppendFormat(" and clue.fcreatedate >= '{0}' ", startDate);
}
//判断截止日期是否有效
if (dyFilter["f_qsnc_enddatefilter"] != null)
{
endDate = Convert.ToDateTime(dyFilter["f_qsnc_enddatefilter"]).ToString("yyyy-MM-dd 23:59:59");
sql2.AppendFormat(" and clue.fcreatedate <= '{0}' ", endDate);
}
sql2.AppendLine(" ) cluetmp ");
sql2.AppendLine(" group by cluetmp.salerid) tmp ");
sql2.AppendLine(" left join V_BD_SALESMAN salesman ");
sql2.AppendLine(" on salesman.fid = tmp.salerid ");
sql2.AppendLine(" left join T_BD_DEPARTMENT_L deptl ");
sql2.AppendLine(" on deptl.FDEPTID = salesman.FDEPTID ");
sql2.AppendFormat(" where deptl.FDEPTID in (select exedeptid from {0}) and deptl.FLOCALEID = 2052 ", tmpTable1);
if (flag0)
{
sql2.AppendLine(" and salesman.fid ").Append(salerLimit);
}
DBUtils.ExecuteDynamicObject(this.Context, sql2.ToString());
//查询出所有部门小计
StringBuilder sql3 = new StringBuilder();
sql3.AppendFormat(@"/*dialect*/ select deptid, sum(cluenumber) as totalclue, sum(oppnumber) as totalopp into {0} from {1} group by deptid ", tmpTable3, tmpTable2);
DBUtils.ExecuteDynamicObject(this.Context, sql3.ToString());
//将销售员名称进行连表查询
StringBuilder sql4 = new StringBuilder();
sql4.AppendFormat(@"/*dialect*/ select deptid, empl.FNAME as saler, cluenumber, oppnumber, conversionrate into {0} ", tmpTable4);
sql4.AppendFormat(" from {0} ", tmpTable2);
sql4.AppendLine(" left join V_BD_SALESMAN salesman on salesman.fid = salerid ");
sql4.AppendLine(" left join T_BD_STAFF staff on staff.FSTAFFID = salesman.FSTAFFID ");
sql4.AppendLine(" inner join T_HR_EMPINFO emp on staff.FEMPINFOID = emp.FID ");
sql4.AppendLine(" left join T_HR_EMPINFO_L empl on empl.FID = emp.FID ");
sql4.AppendLine(" where empl.FLOCALEID = 2052 ");
//判断业务员条件是否有效
if (dyFilter["F_QSNC_SalesmanFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"]).Count > 0)
{
sql4.AppendLine(" and staff.FNUMBER").Append(salerSql);
flag = true;
}
if (flag0)
{
sql4.AppendLine(" and salesman.fid ").Append(salerLimit);
}
DBUtils.ExecuteDynamicObject(this.Context, sql4.ToString());
if (!flag)
{
//将部门小计插入总表中
StringBuilder sql5 = new StringBuilder();
sql5.AppendFormat(@"/*dialect*/ insert into {0} select deptid, '小计', totalclue, totalopp, convert(float,round((totalopp * 1.00 / (totalclue * 1.00)) * 100, 2)) as conversionrate from {1} ", tmpTable4, tmpTable3);
DBUtils.ExecuteDynamicObject(this.Context, sql5.ToString());
}
//显示部门
StringBuilder sql6 = new StringBuilder();
sql6.AppendFormat(@"/*dialect*/ select row_number() over (order by deptl.FNAME) as FSeq, deptl.FNAME as department, saler, cluenumber, oppnumber, cast(conversionrate as varchar)+' %' as rate into {0} from {1} ", tableName, tmpTable4);
sql6.AppendLine(" left join t_bd_department_L deptl ");
sql6.AppendLine(" on deptl.FDEPTID = deptid ");
sql6.AppendLine(" left join t_bd_department dept ");
sql6.AppendLine(" on deptl.FDEPTID = dept.FDEPTID ");
sql6.AppendLine(" where deptl.FLOCALEID = 2052 ");
//判断部门条件是否有效
if (dyFilter["F_QSNC_DepartmentFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"]).Count > 0)
{
sql6.AppendLine(" and dept.FNUMBER").Append(deptSql);
}
DBUtils.ExecuteDynamicObject(this.Context, sql6.ToString());
}
//向临时表插入报表数据
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 flag0 = 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;
flag0 = 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];
//过滤条件：起始日期/截至日期/部门/业务员
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
String startDate = ""; //起始日期
String endDate = ""; //截至日期
bool flag = false;
//业务员
StringBuilder salerSql = new StringBuilder();
if (dyFilter["F_QSNC_SalesmanFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols1 = (DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"];
int salerNum = 0;
if (cols1.Count >= 1)
{
salerSql.Append(" IN (");
}
foreach (DynamicObject saler in cols1)
{
String salerNumber = Convert.ToString(((DynamicObject)saler["F_QSNC_SalesmanFilter"])["Id"]);
salerNum++;
if (cols1.Count == salerNum)
{
salerSql.Append(salerNumber + ")");
}
else
{
salerSql.Append(salerNumber + ", ");
}
}
}
//部门
StringBuilder deptSql = new StringBuilder();
if (dyFilter["F_QSNC_DepartmentFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols2 = (DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"];
int deptNum = 0;
if (cols2.Count >= 1)
{
deptSql.Append(" IN (");
}
foreach (DynamicObject dept in cols2)
{
String deptNumber = Convert.ToString(((DynamicObject)dept["F_QSNC_DepartmentFilter"])["Number"]);
deptNum++;
if (cols2.Count == deptNum)
{
deptSql.Append("'" + deptNumber + "')");
}
else
{
deptSql.Append("'" + deptNumber + "', ");
}
}
}
// ---------------------------------------------------------------------------------------------------------------
// tmpTable1 存放的是线索中的部门、线索的创建人、线索是否转化为商机（1：是；0：否）
StringBuilder tmpSQL1 = new StringBuilder();
tmpSQL1.AppendFormat(@"/*dialect*/ select clue.FCREATORID,
clue.FSALEDEPTID,
case when clue.FBILLNO in (select opp.FSOURCEBILLNO
from T_CRM_Opportunity opp
inner join V_BD_SALESMAN saler on saler.fid = opp.FBEMPID
inner join T_BD_STAFF staff on staff.FSTAFFID = saler.FSTAFFID
inner join T_HR_EMPINFO emp on staff.FEMPINFOID = emp.FID
inner JOIN T_SEC_USER U ON U.FLINKOBJECT = EMP.FPERSONID
where U.FUSERID = clue.FCREATORID) then 1 else 0 end as status
into {0}
from T_CRM_Clue clue
where clue.FCREATORID != 0
and clue.FSALEDEPTID != 0 ", tmpTable1);
//判断起始日期是否有效
if (dyFilter["f_qsnc_startdatefilter"] != null)
{
startDate = Convert.ToDateTime(dyFilter["f_qsnc_startdatefilter"]).ToString("yyyy-MM-dd 00:00:00");
tmpSQL1.AppendFormat(" and clue.F_PEJK_BIZDATE >= '{0}' ", startDate);
}
//判断截止日期是否有效
if (dyFilter["f_qsnc_enddatefilter"] != null)
{
endDate = Convert.ToDateTime(dyFilter["f_qsnc_enddatefilter"]).ToString("yyyy-MM-dd 23:59:59");
tmpSQL1.AppendFormat(" and clue.F_PEJK_BIZDATE <= '{0}' ", endDate);
}
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL1.ToString());
// --------------------------------------------------------------------------------------------------------------------
// 统计每一个销售员的线索数量、商机数量
StringBuilder tmpSQL2 = new StringBuilder();
tmpSQL2.AppendFormat(@"/*dialect*/ SELECT FCREATORID, FSALEDEPTID, COUNT(FCREATORID) CLUENUMBER, SUM(status) OPPNUMBER
INTO {0}
FROM {1}
GROUP BY FCREATORID, FSALEDEPTID ", tmpTable2, tmpTable1);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL2.ToString());
// --------------------------------------------------------------------------------------------------------------------
// 计算每一名销售员的转化率
StringBuilder tmpSQL3 = new StringBuilder();
tmpSQL3.AppendFormat(@"/*dialect*/ SELECT DISTINCT DEPTL.FNAME DEPTNAME,
EMPL.FNAME SALERNAME,
CLUENUMBER,
OPPNUMBER,
CONVERT(FLOAT,ROUND((OPPNUMBER * 1.00 / (CLUENUMBER * 1.00)) * 100, 2)) RATE
INTO {0}
FROM {1} TMP
INNER JOIN T_SEC_USER U ON U.FUSERID = TMP.FCREATORID
INNER JOIN T_HR_EMPINFO EMP ON U.FLINKOBJECT = EMP.FPERSONID
INNER JOIN T_BD_STAFF STAFF on STAFF.FEMPINFOID = EMP.FID
INNER JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FSTAFFID = STAFF.FSTAFFID
INNER JOIN T_HR_EMPINFO_L EMPL ON EMPL.FID = EMP.FID
INNER JOIN t_bd_department_L DEPTL ON DEPTL.FDEPTID = FSALEDEPTID
INNER JOIN t_bd_department DEPT ON DEPTL.FDEPTID = DEPT.FDEPTID
WHERE TMP.FCREATORID <> 0
", tmpTable3, tmpTable2);
// 进行销售员数据隔离
if (flag0)
{
tmpSQL3.AppendLine(" AND SALESMAN.FID ").Append(salerLimit);
}
// 进行销售员过滤条件过滤
if (dyFilter["F_QSNC_SalesmanFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"]).Count > 0)
{
tmpSQL3.AppendLine(" and SALESMAN.FID ").Append(salerSql);
}
// 进行部门条件过滤
//判断部门条件是否有效
if (dyFilter["F_QSNC_DepartmentFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"]).Count > 0)
{
tmpSQL3.AppendLine(" AND DEPT.FNUMBER ").Append(deptSql);
}
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL3.ToString());
// 根据部门进行线索及商机的汇总
// ---------------------------------------------------------------------------------------------------------------------
// 获取到部门小计
StringBuilder tmpSQL4 = new StringBuilder();
tmpSQL4.AppendFormat(@"/*dialect*/ SELECT DEPTNAME, SUM(CLUENUMBER) TOTALCLUE, SUM(OPPNUMBER) TOTALOPP INTO {0} FROM {1} GROUP BY DEPTNAME ", tmpTable4, tmpTable3);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL4.ToString());
// 将部门小计斤系插入明细表
// ----------------------------------------------------------------------------------------------------------------------
StringBuilder tmpSQL5 = new StringBuilder();
tmpSQL5.AppendFormat(@"/*dialect*/ INSERT INTO {0} SELECT DEPTNAME + ' - 小计', '', TOTALCLUE, TOTALOPP, CONVERT(FLOAT,ROUND((TOTALOPP * 1.00 / (TOTALCLUE * 1.00)) * 100, 2)) TOTALRATE FROM {1} ", tmpTable3, tmpTable4);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL5.ToString());
// ------------------------------------------------------------------------------------------------------------------------
// 将总体结果进行插入系统提供的tablename中
StringBuilder tmpSQl6 = new StringBuilder();
tmpSQl6.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY DEPTNAME, SALERNAME) AS FSeq, DEPTNAME, SALERNAME, CLUENUMBER, OPPNUMBER, CONVERT(varchar(60), RATE)+'%' RATE1 INTO {0} FROM {1} ORDER BY DEPTNAME, SALERNAME ", tableName, tmpTable3);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQl6.ToString());
}
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, 2);
DynamicObject cutomerfiler = filter.FilterParameter.CustomFilter;
String year = null;//年份
if (cutomerfiler["F_SXINSUN_YEAR"] != null)
{
year = Convert.ToInt32(cutomerfiler["F_SXINSUN_YEAR"]).ToString();
}
StringBuilder deptnumbersql = new StringBuilder();
if (cutomerfiler["F_PAEZ_DEPT"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_DEPT"];
int deptsize = 0;
if (cols.Count >= 1)
{
deptnumbersql.Append("in (");
}
foreach (DynamicObject dept in cols)
{
String deptnumber = Convert.ToString(((DynamicObject)dept["F_PAEZ_DEPT"])["Number"]);
deptsize = deptsize + 1;
if (deptsize == cols.Count)
{
deptnumbersql.Append("'" + deptnumber + "') ");
}
else
{
deptnumbersql.Append("'" + deptnumber + "',");
}
}
}
StringBuilder salenumbersql = new StringBuilder();
if (cutomerfiler["F_PAEZ_MulBaseSaler"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_MulBaseSaler"];
int salesize = 0;
if (cols.Count >= 1)
{
salenumbersql.Append("in (");
}
foreach (DynamicObject onesale in cols)
{
String salenumber = Convert.ToString(((DynamicObject)onesale["F_PAEZ_MulBaseSaler"])["Number"]);
salesize = salesize + 1;
if (salesize == cols.Count)
{
salenumbersql.Append("'" + salenumber + "') ");
}
else
{
salenumbersql.Append("'" + salenumber + "',");
}
}
}
string temTable1 = materialRptTableNames[0];
string temTable2 = materialRptTableNames[1];
// 拼接过滤条件 ： filter
// 略
//DynamicObject cutomerfiler = filter.FilterParameter.CustomFilter;
//if (cutomerfiler["F_JD_Date"] != null)
//{
// strbydate = Convert.ToDateTime(cutomerfiler["F_JD_Date"]).ToString("yyyy-MM-dd 23:59:59");
// strbydate2 = Convert.ToString(cutomerfiler["F_JD_Date"]);
//}
// 默认排序字段：需要从filter中取用户设置的排序字段
//string seqFld = string.Format(base.KSQL_SEQ, " t0.FID ");
// 取数SQL
// 商机登录部门
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select distinct secuser.fname username ,emp.fnumber empnumber,empl.fname empname,post.fnumber postnumber,post_l.fname postname,dept.fnumber deptnumber,deptl.FNAME deptname ");
stringBuilder.AppendFormat(", opp.FBILLNO,opp.FOPPName ,opp.FCREATEDATE,year(opp.FCREATEDATE) rtyear ,month(opp.FCREATEDATE) rtmonth ,opp.FDOCUMENTSTATUS,activity.FBILLNO activitybillno ,opp.FCloseStatus,activity.Fname activitytitle ,activity.FACTSTARTTIME \n");
stringBuilder.AppendFormat("into {0}", temTable1).AppendLine(" \n");
stringBuilder.AppendLine("from T_CRM_Activity activity \n ");
stringBuilder.AppendLine("left join T_CRM_Opportunity opp on activity.FOPPID=opp.FID \n ");
stringBuilder.AppendLine("left join t_sec_user secuser on opp.FCREATORID= secuser.FUSERID --用户 \n");
stringBuilder.AppendLine("left join V_BD_SALESMAN saleman on saleman.fid = opp.FBEMPID --销售员 \n");
stringBuilder.AppendLine("left join T_BD_STAFF staff on staff.FSTAFFID = saleman.FSTAFFID \n");
stringBuilder.AppendLine("left join T_HR_EMPINFO emp on emp.fid=staff.FEMPINFOID \n");
stringBuilder.AppendLine("left join T_HR_EMPINFO_L empl on empl.FID=emp.FID \n");
stringBuilder.AppendLine("left join T_ORG_POST post on post.FPOSTID = staff.FPOSTID -- 岗位 \n");
stringBuilder.AppendLine("left join T_ORG_POST_L post_l on post_l.FPOSTID = post.FPOSTID \n");
stringBuilder.AppendLine("left join t_bd_department dept on dept.FDEPTID = post.FDEPTID ---- - 部门 \n");
stringBuilder.AppendLine("left join t_bd_department_L deptl on deptl.FDEPTID = dept.FDEPTID \n");
stringBuilder.AppendLine("where secuser.FTYPE=1 and empl.FLOCALEID = 2052 and deptl.FLOCALEID = 2052 ");
if (year != null)
{
stringBuilder.AppendLine(" and year(activity.FACTSTARTTIME)= ");
stringBuilder.AppendLine(year);
}
//部门
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
stringBuilder.AppendLine(" and dept.fnumber ").Append(deptnumbersql);
}
//销售员
if (salenumbersql != null && salenumbersql.Length > 0)
{
stringBuilder.AppendLine(" and emp.fnumber ").Append(salenumbersql);
}
//销售数据隔离
if (flag)
{
stringBuilder.AppendLine(" and saleman.fid ").Append(salerLimit);
}
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select toji2.empnumber, toji2.empname,yue1,yue2,yue3,yue4,yue5,yue6,yue7,yue8, \n ");
stringBuilder.AppendLine("yue9,yue10,yue11,yue12, \n ");
stringBuilder.AppendLine("yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12 zoji,isnull(F_PEJK_ACTIVITYQUNTA,0) hdzb,yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12-isnull(F_PEJK_ACTIVITYQUNTA,0) hdwcqk \n ");
stringBuilder.AppendFormat("into {0}", temTable2).AppendLine(" \n");
stringBuilder.AppendLine("from ( \n ");
stringBuilder.AppendLine("select empnumber, empname, sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4) yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12 \n ");
stringBuilder.AppendLine("from ( \n ");
stringBuilder.AppendLine(" \n ");
stringBuilder.AppendLine("select empnumber, empname, \n ");
stringBuilder.AppendLine("case when rtmonth=1 then oppcounts else 0 end yue1 , \n ");
stringBuilder.AppendLine("case when rtmonth=2 then oppcounts else 0 end yue2, \n ");
stringBuilder.AppendLine("case when rtmonth=3 then oppcounts else 0 end yue3, \n ");
stringBuilder.AppendLine("case when rtmonth=4 then oppcounts else 0 end yue4, \n ");
stringBuilder.AppendLine("case when rtmonth=5 then oppcounts else 0 end yue5, \n ");
stringBuilder.AppendLine("case when rtmonth=6 then oppcounts else 0 end yue6, \n ");
stringBuilder.AppendLine("case when rtmonth=7 then oppcounts else 0 end yue7, \n ");
stringBuilder.AppendLine("case when rtmonth=8 then oppcounts else 0 end yue8, \n ");
stringBuilder.AppendLine("case when rtmonth=9 then oppcounts else 0 end yue9, \n ");
stringBuilder.AppendLine("case when rtmonth=10 then oppcounts else 0 end yue10, \n ");
stringBuilder.AppendLine("case when rtmonth=11 then oppcounts else 0 end yue11, \n ");
stringBuilder.AppendLine("case when rtmonth=12 then oppcounts else 0 end yue12 \n ");
stringBuilder.AppendLine("from ( \n ");
stringBuilder.AppendLine("select empnumber, empname,rtyear,rtmonth, count (distinct activitybillno) oppcounts \n ");
stringBuilder.AppendFormat("from {0} opp \n", temTable1);
stringBuilder.AppendLine("group by empnumber,empname,rtyear,rtmonth \n ");
stringBuilder.AppendLine(") toji0 \n ");
stringBuilder.AppendLine(") tojimonth \n ");
stringBuilder.AppendLine("group by empnumber,empname \n ");
stringBuilder.AppendLine(" \n ");
stringBuilder.AppendLine(") toji2 \n ");
stringBuilder.AppendLine(" \n ");
stringBuilder.AppendLine(" \n ");
stringBuilder.AppendLine("left join \n ");
stringBuilder.AppendLine(" ( \n ");
stringBuilder.AppendLine("select emp.fnumber empnumber,F_PEJK_OPPQUNTA,F_PEJK_OPPTRACKQUNTA ,F_PEJK_ACTIVITYQUNTA \n ");
stringBuilder.AppendLine("from PEJK_SALERQUNTAENTRY SALERQUNTAENTRY \n ");
stringBuilder.AppendLine("left join PEJK_SALERQUNTA SALERQUNTA on SALERQUNTA.fid=SALERQUNTAENTRY.fid \n ");
stringBuilder.AppendLine("left join V_BD_SALESMAN salesman on salesman.fstaffid=SALERQUNTA.F_PEJK_SALER \n ");
stringBuilder.AppendLine("left join T_BD_STAFF staff on staff.FSTAFFID= salesman.fstaffid \n ");
stringBuilder.AppendLine("left join T_HR_EMPINFO emp on staff.FEMPINFOID=emp.FID \n ");
stringBuilder.AppendLine("left join T_HR_EMPINFO_L empl on empl.FID=emp.FID --员工 \n ");
stringBuilder.AppendLine(") zbdj on zbdj.empnumber=toji2.empnumber \n ");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入 总计
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("insert into {0}", temTable2);
stringBuilder.AppendLine("\n select '总计','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12,sum(zoji) zoji,isnull(sum(hdzb),0) hdzb,isnull(sum(hdwcqk),0) hdwcqk ");
stringBuilder.AppendFormat("from {0} ", temTable2);
stringBuilder.AppendLine(" where 1=1 ");
stringBuilder.AppendLine(" ");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(" select ROW_NUMBER() OVER(ORDER BY empnumber) FIDENTITYID,tmp2.* into {0} from {1} tmp2 order by empnumber,empname ", tableName, temTable2);
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
}
}
//执行查询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, 1);
String tmpTable1 = materialRptTableNames[0];
//过滤条件：起始日期/截至日期/部门/业务员
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"])["Number"]);
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"])["Number"]);
deptNum++;
if (cols2.Count == deptNum)
{
deptSql.Append("'" + deptNumber + "') ");
}
else
{
deptSql.Append("'" + deptNumber + "', ");
}
}
}
//报表sql
StringBuilder sql1 = new StringBuilder();
sql1.AppendLine(@"/*dialect*/ SELECT DEPTNAME, SALERNAME, QUOTA, COMPLETEAMOUNT, ");
sql1.AppendFormat(" CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar)+' %' AS COMPLETERATE INTO {0} ", tmpTable1);
sql1.AppendLine(" FROM ");
sql1.AppendLine(" (SELECT DEPTL.FNAME AS DEPTNAME, ");
sql1.AppendLine(" EMPL.FNAME AS SALERNAME, ");
sql1.AppendLine(" (SELECT F_PEJK_CONTRACTQUNTA FROM PEJK_SALERQUNTAENTRY WHERE F_PEJK_SALER = RESOLVESALER.F_PEJK_SALER) AS QUOTA, ");
sql1.AppendLine(" SUM(F_PEJK_DETAILLAMOUNT) AS COMPLETEAMOUNT ");
sql1.AppendLine(" FROM PEJK_SALECONTRACTENTRY RESOLVESALER ");
sql1.AppendLine(" LEFT JOIN PEJK_SALECONTRACTS RESOLVEBASIC ON RESOLVESALER.FID = RESOLVEBASIC.FID ");
sql1.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FID = RESOLVESALER.F_PEJK_SALER ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT_L DEPTL ON DEPTL.FDEPTID = SALESMAN.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT DEPT ON DEPTL.FDEPTID = DEPT.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_STAFF STAFF ON STAFF.FSTAFFID = SALESMAN.FSTAFFID ");
sql1.AppendLine(" LEFT JOIN T_HR_EMPINFO_L EMPL ON EMPL.FID = STAFF.FEMPINFOID ");
sql1.AppendLine(" WHERE DEPTL.FLOCALEID = 2052 AND EMPL.FLOCALEID = 2052 ");
//判断起始日期是否有效
if (dyFilter["F_QSNC_StartDateFilter"] != null)
{
startDate = Convert.ToDateTime(dyFilter["F_QSNC_StartDateFilter"]).ToString("yyyy-MM-dd 00:00:00");
sql1.AppendFormat(" AND RESOLVEBASIC.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 RESOLVEBASIC.F_PEJK_DATE <= '{0}' ", endDate);
}
//判断销售员条件
if (dyFilter["F_QSNC_SalerFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"]).Count > 0)
{
sql1.AppendLine(" and STAFF.FNUMBER").Append(salerSql);
}
//销售员数据隔离
if (flag)
{
sql1.AppendLine(" and SALESMAN.FID ").Append(salerLimit);
}
//判断销售部门条件
if (dyFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"]).Count > 0)
{
sql1.AppendLine(" AND DEPT.FNUMBER").Append(deptSql);
}
sql1.AppendLine(" GROUP BY F_PEJK_SALER, EMPL.FNAME, DEPTL.FNAME) TMP ");
DBUtils.ExecuteDynamicObject(this.Context, sql1.ToString());
// tmpTable1 : DEPTNAME SALERNAME QUOTA COMPLETEAMOUNT COMPLETERATE
StringBuilder sql2 = new StringBuilder();
sql2.AppendFormat(@"/*dialect*/INSERT INTO {0} SELECT '合计' , '', QUOTA, COMPLETEAMOUNT, ", tmpTable1);
sql2.AppendLine(" CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar) + ' %' AS COMPLETERATE ");
sql2.AppendLine(" FROM ");
sql2.AppendFormat(" (SELECT SUM(QUOTA) AS QUOTA, SUM(COMPLETEAMOUNT) AS COMPLETEAMOUNT FROM {0}) TMP ", tmpTable1);
DBUtils.ExecuteDynamicObject(this.Context, sql2.ToString());
StringBuilder sql3 = new StringBuilder();
sql3.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);
sql3.AppendFormat(" FROM {0} ", tmpTable1);
DBUtils.ExecuteDynamicObject(this.Context, sql3.ToString());
}
/// <summary>
/// 构造取数Sql，取数据填充到临时表：tableName
/// </summary>
/// <param name="filter"></param>
/// <param name="tableName"></param>
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
IDBService service = ServiceHelper.GetService <IDBService>();
ColumnTableNamesOld = service.CreateTemporaryTableName(base.Context, 1);
string tb1 = ColumnTableNamesOld[0];//第一次查询使用的临时表
//string tb2 = ColumnTableNamesNew[0];
//父类执行得到原数据源
//base.BuilderReportSqlAndTempTable(filter, tb1);
DynamicObject customFilter = filter.FilterParameter.CustomFilter;
get_params(customFilter, out filters);
string sqlStr = "exec {0} '{1}','{2}','{3}'";
switch (filters["DataSource"])
{
case "Sal_SaleOrder":
sqlStr = String.Format(sqlStr, "[dbo].[z_get_sal_orders]", filters["StartDate"], filters["EndDate"], tb1);
break;
case "Sal_OUtStock":
sqlStr = String.Format(sqlStr, "[dbo].[z_get_sal_outstock]", filters["StartDate"], filters["EndDate"], tb1);
break;
case "AR_ReceiveBill":
sqlStr = String.Format(sqlStr, "[dbo].[z_get_sal_receivable]", filters["StartDate"], filters["EndDate"], tb1);
break;
}
DBUtils.Execute(this.Context, sqlStr);
string sqlStr2 = String.Format("({0}) T", buildSqlstr(tb1));
RankingOnNew = ((customFilter["RankingOnNew"] != null) ? customFilter["RankingOnNew"].ToString() : "");
//StringBuilder stringBuilder = new StringBuilder();
string sql = @"/*dialect*/select IDENTITY(INT,1,1) FIDENTITYID,{0},SUM(isnull(T.FBASEUNITQTY,0)) FSalQty,SUM(isnull(T.FAMOUNT_LC,0)) FAmount,
CASE WHEN SUM(T.FBASEUNITQTY) = 0 THEN 0 ELSE SUM(T.FAMOUNT_LC)/SUM(T.FBASEUNITQTY) END FPrice,
2 as FPRICEDIGITS,2 as FAMOUNTDIGITS,0 as FUnitPrecision
into {1} from {2} group by {3} order by FAmount desc
";
string fields = "";
switch (RankingOnNew)
{
case "COUNTRYMATERIAL":
fields = "T.FMNUMBER,T.FMNAME,T.FModel,T.FGROUPNAME,T.FCOUNTRYNAME";
break;
case "SALERCUSTCOUNTRY":
fields = "T.FSALERNUMBER,T.FSALERNAME,T.FCUSTNUMBER,T.FCUSTNAME,T.FCOUNTRYNAME";
break;
case "MATERIALGROUP":
fields = "T.FGROUPNUMBER,T.FGROUPNAME";
break;
case "BD_CUSTOMER":
fields = "T.FCUSTNUMBER,T.FCUSTNAME,T.FCOUNTRYNAME";
break;
case "SALER":
fields = "T.FSALERNUMBER,T.FSALERNAME";
break;
case "SALEDEPARTMENT":
fields = "T.FDEPNAME";
break;
case "CUSTMATERIALGROUP":
fields = "T.FCUSTNUMBER,T.FCUSTNAME,FGROUPNAME";
break;
case "SALERCUSTMATERIALGROUP":
fields = "T.FSALERNAME,T.FCUSTNUMBER,T.FCUSTNAME,FGROUPNAME";
break;
case "BD_MATERIAL":
fields = "T.FMNUMBER,T.FMNAME,T.FModel,T.FGROUPNAME";
break;
case "SALERMATERIAL":
fields = "T.FSALERNAME,T.FMNUMBER,T.FMNAME,T.FModel,T.FGROUPNAME";
break;
case "SALEDEPMATERIAL":
fields = "T.FDEPNAME,T.FMNUMBER,T.FMNAME,T.FModel,T.FGROUPNAME";
break;
case "CUSTMATERIAL":
fields = "T.FCUSTNUMBER,T.FCUSTNAME,T.FMNUMBER,T.FMNAME,T.FModel,T.FGROUPNAME";
break;
case "CUSTOMERSALERMATERIAL":
fields = "T.FCUSTNUMBER,T.FCUSTNAME,T.FSALERNAME,T.FMNUMBER,T.FMNAME,T.FModel,T.FGROUPNAME";
break;
}
DBUtils.Execute(this.Context, String.Format(sql, fields, tableName, sqlStr2, fields));
}
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, 2);
DynamicObject cutomerfiler = filter.FilterParameter.CustomFilter;
String year = null;//年份
if (cutomerfiler["F_SXINSUN_YEAR"] != null)
{
year = Convert.ToInt32(cutomerfiler["F_SXINSUN_YEAR"]).ToString();
}
StringBuilder deptnumbersql = new StringBuilder();
if (cutomerfiler["F_PAEZ_DEPT"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_DEPT"];
int deptsize = 0;
if (cols.Count >= 1)
{
deptnumbersql.Append("in (");
}
foreach (DynamicObject dept in cols)
{
String deptnumber = Convert.ToString(((DynamicObject)dept["F_PAEZ_DEPT"])["Number"]);
deptsize = deptsize + 1;
if (deptsize == cols.Count)
{
deptnumbersql.Append("'" + deptnumber + "')");
}
else
{
deptnumbersql.Append("'" + deptnumber + "',");
}
}
}
StringBuilder billstatussql = new StringBuilder();
StringBuilder salenumbersql = new StringBuilder();
if (cutomerfiler["F_PAEZ_MulBaseSaler"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_MulBaseSaler"];
int salesize = 0;
if (cols.Count >= 1)
{
salenumbersql.Append("in (");
}
foreach (DynamicObject onesale in cols)
{
String salenumber = Convert.ToString(((DynamicObject)onesale["F_PAEZ_MulBaseSaler"])["Number"]);
salesize = salesize + 1;
if (salesize == cols.Count)
{
salenumbersql.Append("'" + salenumber + "')");
}
else
{
salenumbersql.Append("'" + salenumber + "',");
}
}
}
//添加单据状态过滤
int billstatussize = 0;
if (cutomerfiler["F_PAEZ_BillStatus"] != null)
{
string billstatusenum = Convert.ToString(cutomerfiler["F_PAEZ_BillStatus"]);
if (!string.IsNullOrEmpty(billstatusenum))
{
String[] billstauscol = billstatusenum.Split(',');
if (billstauscol.Length >= 1)
{
billstatussql.Append(" in (");
for (int i = 0; i < billstauscol.Length; i++)
{
billstatussize = billstatussize + 1;
if (billstatussize == billstauscol.Length)
{
billstatussql.Append("'" + billstauscol[i] + "')");
}
else
{
billstatussql.Append("'" + billstauscol[i] + "',");
}
}
}
}
}
string temTable1 = materialRptTableNames[0];
string temTable2 = materialRptTableNames[1];
// 拼接过滤条件 ： filter
// 略
//DynamicObject cutomerfiler = filter.FilterParameter.CustomFilter;
//if (cutomerfiler["F_JD_Date"] != null)
//{
// strbydate = Convert.ToDateTime(cutomerfiler["F_JD_Date"]).ToString("yyyy-MM-dd 23:59:59");
// strbydate2 = Convert.ToString(cutomerfiler["F_JD_Date"]);
//}
// 默认排序字段：需要从filter中取用户设置的排序字段
//string seqFld = string.Format(base.KSQL_SEQ, " t0.FID ");
// 取数SQL
// 商机登录部门
if (Convert.ToBoolean(cutomerfiler["F_PAEZ_ShowDetail"]) == true)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select distinct secuser.fname username ,emp.fnumber empnumber,empl.fname empname,post.fnumber postnumber,post_l.fname postname,dept.fnumber deptnumber,deptl.FNAME deptname ");
stringBuilder.AppendFormat(", opp.FBILLNO,opp.FOPPName ,opp.FSTARTDATE,year(opp.FSTARTDATE) rtyear ,month(opp.FSTARTDATE) rtmonth ,opp.FDOCUMENTSTATUS,activity.FBILLNO activitybillno ,opp.FCloseStatus \n");
stringBuilder.AppendFormat("into {0}", temTable1).AppendLine(" \n");
stringBuilder.AppendLine("from T_CRM_Opportunity opp \n ");
stringBuilder.AppendLine("left join T_CRM_Activity activity on activity.FOPPID = opp.FID \n ");
stringBuilder.AppendLine(" inner join V_BD_SALESMAN saler on opp.FBEMPID = saler.FID ");
stringBuilder.AppendLine(" inner join T_BD_STAFF staff on saler.FSTAFFID=staff.FSTAFFID ");
stringBuilder.AppendLine("inner join T_HR_EMPINFO emp on staff.FEMPINFOID=emp.FID \n");
stringBuilder.AppendLine("inner join T_HR_EMPINFO_L empl on empl.FID=emp.FID \n");
stringBuilder.AppendLine("inner join T_ORG_POST post on post.FPOSTID=staff.FPOSTID \n");
stringBuilder.AppendLine("inner join T_ORG_POST_L post_l on post_l.FPOSTID=post.FPOSTID ");
stringBuilder.AppendLine(" inner join t_sec_user secuser on secuser.FLINKOBJECT=emp.FPERSONID ");
// 商机中的销售部门
stringBuilder.AppendLine("inner join t_bd_department dept on dept.FDEPTID=opp.FSALEDEPTID -----部门 \n");
stringBuilder.AppendLine("inner join t_bd_department_L deptl on deptl.FDEPTID=dept.FDEPTID \n");
stringBuilder.AppendLine("where secuser.FTYPE=1 AND empl.FLOCALEID = 2052 AND post_l.FLOCALEID = 2052 AND deptl.FLOCALEID = 2052 ");
if (year != null && !year.Equals("0"))
{
stringBuilder.AppendLine(" and year(opp.FSTARTDATE)= ");
stringBuilder.AppendLine(year);
}
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
stringBuilder.AppendLine(" and dept.fnumber ").Append(deptnumbersql);
}
if (salenumbersql != null && salenumbersql.Length > 0)
{
stringBuilder.AppendLine(" and staff.fnumber ").Append(salenumbersql);
}
//单据状态
if (billstatussql.Length > 0)
{
stringBuilder.AppendLine(" and opp.FDOCUMENTSTATUS ").Append(billstatussql);
}
//销售员数据隔离
if (flag)
{
stringBuilder.AppendLine(" and OPP.FBEMPID ").Append(salerLimit);
}
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select toji2.deptnumber, toji2.deptname, toji2.empnumber, toji2.empname,yue1,yue2,yue3,yue4,yue5,yue6,yue7,yue8,\n");
stringBuilder.AppendLine("yue9,yue10,yue11,yue12, \n");
stringBuilder.AppendLine("yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12 zoji,isnull(F_PEJK_OPPQUNTA,0) dlzb,yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12-isnull(F_PEJK_OPPQUNTA,0) dlwqqk,gzzj gzzj ,isnull(F_PEJK_OPPTRACKQUNTA,0) gzzb, gzzj-isnull(F_PEJK_OPPTRACKQUNTA,0) gzzbwqqk,activitycount gzhud\n");
stringBuilder.AppendFormat("into {0}", temTable2).AppendLine(" \n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname, sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4) yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12\n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname,\n");
stringBuilder.AppendLine("case when rtmonth=1 then oppcounts else 0 end yue1 ,\n");
stringBuilder.AppendLine("case when rtmonth=2 then oppcounts else 0 end yue2,\n");
stringBuilder.AppendLine("case when rtmonth=3 then oppcounts else 0 end yue3,\n");
stringBuilder.AppendLine("case when rtmonth=4 then oppcounts else 0 end yue4,\n");
stringBuilder.AppendLine("case when rtmonth=5 then oppcounts else 0 end yue5,\n");
stringBuilder.AppendLine("case when rtmonth=6 then oppcounts else 0 end yue6,\n");
stringBuilder.AppendLine("case when rtmonth=7 then oppcounts else 0 end yue7,\n");
stringBuilder.AppendLine("case when rtmonth=8 then oppcounts else 0 end yue8,\n");
stringBuilder.AppendLine("case when rtmonth=9 then oppcounts else 0 end yue9,\n");
stringBuilder.AppendLine("case when rtmonth=10 then oppcounts else 0 end yue10,\n");
stringBuilder.AppendLine("case when rtmonth=11 then oppcounts else 0 end yue11,\n");
stringBuilder.AppendLine("case when rtmonth=12 then oppcounts else 0 end yue12\n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname,rtyear,rtmonth, count (distinct FBILLNO) oppcounts\n");
stringBuilder.AppendFormat("from {0} opp \n", temTable1);
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname,rtyear,rtmonth\n");
stringBuilder.AppendLine(") toji0\n");
stringBuilder.AppendLine(") tojimonth\n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(") toji2\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("left join \n");
stringBuilder.AppendLine(" (\n");
stringBuilder.AppendLine("select distinct emp.fnumber empnumber,F_PEJK_OPPQUNTA,F_PEJK_OPPTRACKQUNTA \n");
stringBuilder.AppendLine("from PEJK_SALERQUNTAENTRY SALERQUNTAENTRY \n");
stringBuilder.AppendLine("left join PEJK_SALERQUNTA SALERQUNTA on SALERQUNTA.fid=SALERQUNTAENTRY.fid \n");
stringBuilder.AppendLine("left join V_BD_SALESMAN salesman on salesman.fid=SALERQUNTAENTRY.F_PEJK_SALER \n");
stringBuilder.AppendLine("left join T_BD_STAFF staff on staff.FSTAFFID= salesman.fstaffid \n");
stringBuilder.AppendLine("left join T_HR_EMPINFO emp on staff.FEMPINFOID=emp.FID \n");
stringBuilder.AppendLine("left join T_HR_EMPINFO_L empl on empl.FID=emp.FID --员工 \n");
stringBuilder.AppendLine(") zbdj on zbdj.empnumber=toji2.empnumber\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("inner join \n");
stringBuilder.AppendLine("(\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname, sum(gzzj) gzzj,sum(activitycount ) activitycount \n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname , count (distinct FBILLNO) gzzj,0 activitycount \n");
stringBuilder.AppendFormat("from {0} opp \n", temTable1);
stringBuilder.AppendLine("where opp.FDOCUMENTSTATUS is not null \n");
stringBuilder.AppendLine("and opp.FCloseStatus='0' --- and opp.FDOCUMENTSTATUS>='C' \n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname \n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("union all\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname , 0 gzzj,count(distinct activitybillno) activitycount \n");
stringBuilder.AppendFormat("from {0} opp \n", temTable1);
stringBuilder.AppendLine("where opp.FDOCUMENTSTATUS is not null \n");
//stringBuilder.AppendLine("and opp.FDOCUMENTSTATUS='E' \n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname \n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(") zjtongji\n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname \n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(") zitongji2 on zitongji2.empnumber=toji2.empnumber where zitongji2.deptnumber = toji2.deptnumber \n");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入部门小计
//
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("insert into {0} ", temTable2);
stringBuilder.AppendLine(" select deptnumber,deptname,'部门小计','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12,sum(zoji) zoji,sum(dlzb) dlzb,sum(dlwqqk) dlwqqk,sum(gzzj) gzzj,sum(gzzb) gzzb,sum(gzzbwqqk) gzzbwqqk,sum(gzhud) gzhud ");
stringBuilder.AppendFormat(" from {0} ", temTable2);
stringBuilder.AppendLine(" group by deptnumber, deptname ");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入 总计
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("insert into {0}", temTable2);
stringBuilder.AppendLine("\n select '总计','','','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12,sum(zoji) zoji,sum(dlzb) dlzb,sum(dlwqqk) dlwqqk,sum(gzzj) gzzj,sum(gzzb) gzzb,sum(gzzbwqqk) gzzbwqqk,sum(gzhud) gzhud ");
stringBuilder.AppendFormat("from {0} ", temTable2);
stringBuilder.AppendLine(" where empnumber<>'部门小计' ");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(" select ROW_NUMBER() OVER(ORDER BY deptnumber) FIDENTITYID,tmp2.* into {0} from {1} tmp2 order by deptnumber,deptname,empnumber,empname ", tableName, temTable2);
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
}
else
{
// 取数SQL
// 商机登录部门
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select distinct secuser.fname username ,emp.fnumber empnumber,empl.fname empname,post.fnumber postnumber,post_l.fname postname,dept.fnumber deptnumber,deptl.FNAME deptname ");
// stringBuilder.AppendFormat(", opp.FBILLNO,opp.FOPPName ,opp.FCREATEDATE,year(opp.FCREATEDATE) rtyear ,month(opp.FCREATEDATE) rtmonth ,opp.FDOCUMENTSTATUS,activity.FBILLNO activitybillno,opp.FCloseStatus \n");
stringBuilder.AppendFormat(", opp.FBILLNO,opp.FOPPName ,opp.FSTARTDATE,year(opp.FSTARTDATE) rtyear ,month(opp.FSTARTDATE) rtmonth ,opp.FDOCUMENTSTATUS,activity.FBILLNO activitybillno,opp.FCloseStatus \n");
stringBuilder.AppendFormat("into {0}", temTable1).AppendLine(" \n");
stringBuilder.AppendLine("from T_CRM_Opportunity opp \n ");
stringBuilder.AppendLine("left join T_CRM_Activity activity on activity.FOPPID = opp.FID \n ");
stringBuilder.AppendLine(" inner join V_BD_SALESMAN saler on opp.FBEMPID = saler.FID ");
stringBuilder.AppendLine(" inner join T_BD_STAFF staff on saler.FSTAFFID=staff.FSTAFFID ");
stringBuilder.AppendLine("inner join T_HR_EMPINFO emp on staff.FEMPINFOID=emp.FID \n");
stringBuilder.AppendLine("inner join T_HR_EMPINFO_L empl on empl.FID=emp.FID \n");
stringBuilder.AppendLine("inner join T_ORG_POST post on post.FPOSTID=staff.FPOSTID \n");
stringBuilder.AppendLine("inner join T_ORG_POST_L post_l on post_l.FPOSTID=post.FPOSTID ");
stringBuilder.AppendLine(" inner join t_sec_user secuser on secuser.FLINKOBJECT=emp.FPERSONID ");
// 商机中的销售部门
stringBuilder.AppendLine("inner join t_bd_department dept on dept.FDEPTID=opp.FSALEDEPTID -----部门 \n");
stringBuilder.AppendLine("inner join t_bd_department_L deptl on deptl.FDEPTID=dept.FDEPTID \n");
stringBuilder.AppendLine("where secuser.FTYPE=1 AND empl.FLOCALEID = 2052 AND post_l.FLOCALEID = 2052 AND deptl.FLOCALEID = 2052 ");
if (year != null && !year.Equals("0"))
{
stringBuilder.AppendLine(" and year(opp.FSTARTDATE)= ");
stringBuilder.AppendLine(year);
}
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
stringBuilder.AppendLine(" and dept.fnumber ").Append(deptnumbersql);
}
if (salenumbersql != null && salenumbersql.Length > 0)
{
stringBuilder.AppendLine(" and emp.fnumber ").Append(salenumbersql);
}
//单据状态
if (billstatussql.Length > 0)
{
stringBuilder.AppendLine(" and opp.FDOCUMENTSTATUS ").Append(billstatussql);
}
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select deptnumber, deptname, yue1,yue2,yue3,yue4,yue5,yue6,yue7,yue8, \n");
stringBuilder.AppendLine("yue9,yue10,yue11,yue12, \n");
stringBuilder.AppendLine("yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12 zoji \n");
stringBuilder.AppendFormat("into {0} ", temTable2);
stringBuilder.AppendLine("from ( \n");
stringBuilder.AppendLine("select deptnumber, deptname, sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4) yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12\n");
stringBuilder.AppendLine("from ( \n");
stringBuilder.AppendLine("select deptnumber, deptname,case when rtmonth=1 then oppcounts else 0 end yue1 , \n");
stringBuilder.AppendLine("case when rtmonth=2 then oppcounts else 0 end yue2, \n");
stringBuilder.AppendLine("case when rtmonth=3 then oppcounts else 0 end yue3, \n");
stringBuilder.AppendLine("case when rtmonth=4 then oppcounts else 0 end yue4, \n");
stringBuilder.AppendLine("case when rtmonth=5 then oppcounts else 0 end yue5, \n");
stringBuilder.AppendLine("case when rtmonth=6 then oppcounts else 0 end yue6, \n");
stringBuilder.AppendLine("case when rtmonth=7 then oppcounts else 0 end yue7, \n");
stringBuilder.AppendLine("case when rtmonth=8 then oppcounts else 0 end yue8, \n");
stringBuilder.AppendLine("case when rtmonth=9 then oppcounts else 0 end yue9, \n");
stringBuilder.AppendLine("case when rtmonth=10 then oppcounts else 0 end yue10, \n");
stringBuilder.AppendLine("case when rtmonth=11 then oppcounts else 0 end yue11, \n");
stringBuilder.AppendLine("case when rtmonth=12 then oppcounts else 0 end yue12 \n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("from ( \n");
stringBuilder.AppendLine("select deptnumber, deptname,rtyear,rtmonth, count (distinct FBILLNO) oppcounts \n");
stringBuilder.AppendFormat("from {0} \n", temTable1);
stringBuilder.AppendLine("group by deptnumber, deptname,rtyear,rtmonth \n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine(") toji \n");
stringBuilder.AppendLine(" ) toji0 \n");
stringBuilder.AppendLine(" group by deptnumber, deptname \n");
stringBuilder.AppendLine(") toji2 \n");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入部门小计
//
//stringBuilder = new StringBuilder();
//stringBuilder.AppendFormat("insert into {0} ", temTable2);
//stringBuilder.AppendLine(" select deptnumber, deptname,'部门小计','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12, sum(zoji) zoji ");
//stringBuilder.AppendFormat(" from {0} ", temTable2);
//stringBuilder.AppendLine(" group by deptnumber, deptname ");
//DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入 总计
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("insert into {0}", temTable2);
stringBuilder.AppendLine("\n select '总计','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12,sum(zoji) zoji ");
stringBuilder.AppendFormat("from {0} ", temTable2);
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(" select ROW_NUMBER() OVER(ORDER BY deptnumber) FIDENTITYID,tmp2.* into {0} from {1} tmp2 order by deptnumber,deptname ", tableName, temTable2);
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
}
}
//向临时表插入报表数据
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
// -----------------------------------------------------------------------------------------------------------------------------------------------
//生成中间临时表
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];
string tmpTable7 = materialRptTableNames[6];
string tmpTable8 = materialRptTableNames[7];
string tmpTable9 = materialRptTableNames[8];
//过滤条件：物料编码/仓库/批号
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
// 物料
StringBuilder materialSql = new StringBuilder();
if (dyFilter["F_scfg_MaterialIdFilter"] != null && ((DynamicObjectCollection)dyFilter["F_scfg_MaterialIdFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols1 = (DynamicObjectCollection)dyFilter["F_scfg_MaterialIdFilter"];
int materialNum = 0;
if (cols1.Count >= 1)
{
materialSql.Append(" IN (");
}
foreach (DynamicObject saler in cols1)
{
String salerNumber = Convert.ToString(((DynamicObject)saler["F_scfg_MaterialIdFilter"])["Id"]);
materialNum++;
if (cols1.Count == materialNum)
{
materialSql.Append(salerNumber + ")");
}
else
{
materialSql.Append(salerNumber + ", ");
}
}
}
// 仓库
StringBuilder stockSql = new StringBuilder();
if (dyFilter["F_scfg_StockIdFilter"] != null && ((DynamicObjectCollection)dyFilter["F_scfg_StockIdFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols2 = (DynamicObjectCollection)dyFilter["F_scfg_StockIdFilter"];
int stockNum = 0;
if (cols2.Count >= 1)
{
stockSql.Append(" IN (");
}
foreach (DynamicObject dept in cols2)
{
String deptNumber = Convert.ToString(((DynamicObject)dept["F_scfg_StockIdFilter"])["Id"]);
stockNum++;
if (cols2.Count == stockNum)
{
stockSql.Append(deptNumber + ")");
}
else
{
stockSql.Append(deptNumber + ", ");
}
}
}
// 批号
StringBuilder batchSql = new StringBuilder();
if (dyFilter["F_scfg_BatchFilter"] != null && ((DynamicObjectCollection)dyFilter["F_scfg_BatchFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols2 = (DynamicObjectCollection)dyFilter["F_scfg_BatchFilter"];
int batchNum = 0;
if (cols2.Count >= 1)
{
batchSql.Append(" IN (");
}
foreach (DynamicObject dept in cols2)
{
String deptNumber = Convert.ToString(((DynamicObject)dept["F_scfg_BatchFilter"])["Id"]);
batchNum++;
if (cols2.Count == batchNum)
{
batchSql.Append(deptNumber + ")");
}
else
{
batchSql.Append(deptNumber + ", ");
}
}
}
// 显示小计
bool flag = false;
if (dyFilter["F_scfg_CheckBoxFilter"] != null && Convert.ToInt32(dyFilter["F_scfg_CheckBoxFilter"]) == 1)
{
flag = true;
}
// 20191225 增加简单生产入库单 简单生产领料单
// ---------------------------------------------------------------------------------------------------------------
// tmpTable1 存放所有仓库进库数量（除了条码拆装单）
StringBuilder tmpSQL1 = new StringBuilder();
tmpSQL1.AppendFormat(@"/*dialect*/ SELECT FMATERIALID, FSTOCKID, FLOT, FAUXPROPID, SUM(FREALQTY) PARTQTY, SUM(F_SCFG_M2NUM) PARTM2, SUM(F_SCFG_ZHANGNUM) PARTZHANG, SUM(F_SCFG_GENUM) PARTGE, SUM(F_scfg_MulNum) PARTMUL INTO {0}
FROM
(SELECT FMATERIALID, FSTOCKID, FLOT, FAUXPROPID, FREALQTY, 0 F_SCFG_M2NUM, 0 F_SCFG_ZHANGNUM, 0 F_SCFG_GENUM, 0 F_scfg_MulNum FROM T_STK_INSTOCKENTRY SE LEFT JOIN t_STK_InStock S ON S.FID = SE.FID WHERE S.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT FMATERIALID, IE.FSTOCKID, FLOT, FAUXPROPID, FREALQTY, F_SCFG_DECIMAL4, F_SCFG_DECIMAL6, F_SCFG_DECIMAL, F_scfg_MulNum FROM T_PRD_INSTOCKENTRY IE LEFT JOIN T_PRD_INSTOCK I ON IE.FID = I.FID WHERE I.F_SCFG_ISPACKAGE = 0 AND I.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT FMATERIALID, FSTOCKID, FLOT, FAUXPROPID, FQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_scfg_MulNum FROM T_STK_MISCELLANEOUSENTRY MCE LEFT JOIN T_STK_MISCELLANEOUS MC ON MCE.FID = MC.FID WHERE MC.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT FMATERIALID, FDESTSTOCKID, FLOT, FAUXPROPID, FQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_scfg_MulNum FROM T_STK_STKTRANSFERINENTRY SSE LEFT JOIN T_STK_STKTRANSFERIN SS ON SSE.FID = SS.FID WHERE SS.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT ID.FMATERIALID, ID.FSTOCKID, ID.FLOT, FAUXPROPID, ID.FBASEQTY, 0 F_SCFG_M2NUM, 0 F_SCFG_ZHANGNUM, 0 F_SCFG_GENUM, 0 F_scfg_MulNum FROM T_STK_INVINITDETAIL ID LEFT JOIN T_STK_INVINIT I ON ID.FID = I.FID WHERE I.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT FMATERIALID, ISE.FSTOCKID, FLOT, FAUXPROPID, FBASEREALQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_SCFG_MULNUM FROM T_SP_INSTOCKENTRY ISE LEFT JOIN T_SP_INSTOCK I ON ISE.FID = I.FID WHERE I.FDOCUMENTSTATUS IN ('B', 'C')
) TMP
WHERE TMP.FMATERIALID != 0 AND TMP.FSTOCKID != 0 AND TMP.FLOT != 0
GROUP BY FMATERIALID, FSTOCKID, FLOT, FAUXPROPID ", tmpTable1);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL1.ToString());
// --------------------------------------------------------------------------------------------------------------------
// tmpTable2 存放全部进库数量
StringBuilder tmpSQL2 = new StringBuilder();
tmpSQL2.AppendFormat(@"/*dialect*/ SELECT TMP.FMATERIALID,
TMP.FSTOCKID,
TMP.FLOT,
TMP.FAUXPROPID,
(TMP.PARTQTY + ISNULL(TMP1.FQTY, 0)) ALLINQTY,
(TMP.PARTM2 + ISNULL(TMP1.F_SCFG_M2NUM, 0)) ALLM2IN,
(TMP.PARTZHANG + ISNULL(TMP1.F_SCFG_ZHANGNUM, 0)) ALLZHANGIN,
(TMP.PARTGE + ISNULL(TMP1.F_SCFG_GENUM, 0)) ALLGEIN,
(TMP.PARTMUL + ISNULL(TMP1.F_scfg_MulNum, 0)) ALLMULIN INTO {0} FROM {1} TMP
LEFT JOIN (SELECT PE.FITEMID, PE.FLOT, BCM.FAUXPROPID, BCM.FQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_scfg_MulNum from t_UN_PackagingEntry PE LEFT JOIN T_BD_BARCODEMAIN BCM ON BCM.FBARCODE = PE.FENTRYBARCODE WHERE PE.FITEMID != 0 AND PE.FLOT != 0) TMP1
ON TMP1.FITEMID = TMP.FMATERIALID AND TMP1.FLOT = TMP.FLOT AND TMP1.FAUXPROPID = TMP.FAUXPROPID ", tmpTable2, tmpTable1);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL2.ToString());
// --------------------------------------------------------------------------------------------------------------------
// tmpTable3中存放部分仓库出数量
StringBuilder tmpSQL3 = new StringBuilder();
tmpSQL3.AppendFormat(@"/*dialect*/ SELECT FMATERIALID, FSTOCKID, FLOT, FAUXPROPID, SUM(FREALQTY) ALLOUTQTY, SUM(F_SCFG_M2NUM) ALLM2OUT, SUM(F_SCFG_ZHANGNUM) ALLZHANGOUT, SUM(F_SCFG_GENUM) ALLGEOUT, SUM(F_scfg_MulNum) ALLMULOUT INTO {0}
FROM
(SELECT OE.FMATERIALID, OE.FSTOCKID, OE.FLOT, FAUXPROPID, OE.FREALQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_scfg_MulNum FROM T_SAL_OUTSTOCKENTRY OE LEFT JOIN T_SAL_OUTSTOCK O ON OE.FID = O.FID WHERE O.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT PE.FMATERIALID, PE.FSTOCKID, PE.FLOT, FAUXPROPID, PE.FACTUALQTY, 0 F_SCFG_M2NUM, 0 F_SCFG_ZHANGNUM, 0 F_SCFG_GENUM, 0 F_scfg_MulNum FROM T_PRD_PICKMTRLDATA PE LEFT JOIN T_PRD_PICKMTRL P ON PE.FID = P.FID WHERE P.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT MDE.FMATERIALID, MDE.FSTOCKID, MDE.FLOT, FAUXPROPID, MDE.FQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_scfg_MulNum FROM T_STK_MISDELIVERYENTRY MDE LEFT JOIN T_STK_MISDELIVERY MD ON MDE.FID = MD.FID WHERE MD.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT SSE.FMATERIALID, SSE.FSRCSTOCKID, SSE.FLOT, FAUXPROPID, SSE.FQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_scfg_MulNum FROM T_STK_STKTRANSFERINENTRY SSE LEFT JOIN T_STK_STKTRANSFERIN SS ON SSE.FID = SS.FID WHERE SS.FDOCUMENTSTATUS IN ('B', 'C')
UNION
SELECT FMATERIALID, FSTOCKID, FLOT, FAUXPROPID, FBASEACTUALQTY, F_SCFG_M2NUM, F_SCFG_ZHANGNUM, F_SCFG_GENUM, F_SCFG_MULNUM FROM T_SP_PICKMTRLDATA PMD LEFT JOIN T_SP_PICKMTRL PM ON PMD.FID = PM.FID WHERE PM.FDOCUMENTSTATUS IN ('B', 'C')
) TMP
GROUP BY FMATERIALID, FSTOCKID, FLOT, FAUXPROPID ", tmpTable3);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL3.ToString());
//// ----------------------------------------------------------------------------------------------------------------------
//// tmpTable4 中存放全部物料的出库数量
//StringBuilder tmpSQL4 = new StringBuilder();
//tmpSQL4.AppendFormat(@"/*dialect*/ SELECT TMP.FMATERIALID,
// TMP.FSTOCKID,
// TMP.FLOT,
// (TMP.PARTOUTQTY + ISNULL(TMP1.F_SCFG_REALOUTQTY, 0)) ALLOUTQTY,
// (TMP.PARTM2 + ISNULL(TMP1.F_SCFG_REALM2NUM, 0)) ALLM2OUT,
// (TMP.PARTZHANG + ISNULL(TMP1.F_SCFG_REALZHANGNUM, 0)) ALLZHANGOUT,
// (TMP.PARTGE + ISNULL(TMP1.F_SCFG_REALGENUM, 0)) ALLGEOUT,
// (TMP.PARTMUL + ISNULL(TMP1.F_scfg_realMulNum, 0)) ALLMULOUT INTO {0} FROM {1} TMP
// LEFT JOIN (SELECT PE.FITEMID, PE.FLOT, PE.F_SCFG_REALOUTQTY, F_SCFG_REALM2NUM, F_SCFG_REALZHANGNUM, F_SCFG_REALGENUM, F_scfg_realMulNum FROM t_UN_PackagingEntry PE LEFT JOIN t_UN_Packaging P ON PE.FID = P.FID WHERE PE.FITEMID != 0 AND PE.FLOT != 0) TMP1
// ON TMP1.FITEMID = TMP.FMATERIALID AND TMP1.FLOT = TMP.FLOT ", tmpTable4, tmpTable3);
//DBUtils.ExecuteDynamicObject(this.Context, tmpSQL4.ToString());
// ----------------------------------------------------------------------------------------------------------------------
// tmpTable5 中存放全部物料现有的库存
StringBuilder tmpSQL5 = new StringBuilder();
tmpSQL5.AppendFormat(@"/*dialect*/ SELECT TMP2.FMATERIALID,
TMP2.FSTOCKID,
TMP2.FLOT,
CASE WHEN FF100001 IS NOT NULL AND FF100002 IS NOT NULL THEN (CASE WHEN FF100001 != '' THEN FF100001 ELSE FF100002 END) ELSE '' END AUXID,
(TMP2.ALLINQTY - ISNULL(TMP4.ALLOUTQTY, 0)) STOCKQTY,
(TMP2.ALLM2IN - ISNULL(TMP4.ALLM2OUT, 0)) M2NUM,
(TMP2.ALLZHANGIN - ISNULL(TMP4.ALLZHANGOUT, 0)) ZHANGNUM,
(TMP2.ALLGEIN - ISNULL(TMP4.ALLGEOUT, 0)) GENUM,
(TMP2.ALLMULIN - ISNULL(TMP4.ALLMULOUT, 0)) MULNUM
INTO {0}
FROM {1} TMP2
LEFT JOIN {2} TMP4 ON TMP2.FMATERIALID = TMP4.FMATERIALID AND TMP2.FSTOCKID = TMP4.FSTOCKID AND TMP2.FLOT = TMP4.FLOT AND TMP2.FAUXPROPID = TMP4.FAUXPROPID
LEFT JOIN T_BD_FlexsItemDetailV FIDV ON FIDV.FID = TMP2.FAUXPROPID ", tmpTable5, tmpTable2, tmpTable3);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL5.ToString());
// ----------------------------------------------------------------------------------------------------------------------
// 增加辅助计量单位携带 FAUXPROPID
// tmpTable6 中存放多计量单位换算报表
StringBuilder tmpSQL6 = new StringBuilder();
tmpSQL6.AppendFormat(@"/*dialect*/ SELECT M.FNUMBER MATERIALNUMBER,
ML.FNAME MATERIALNAME,
ML.FSPECIFICATION MATERIALSPECIFICATION,
S.FNUMBER STOCKNUMBER,
SL.FNAME STOCKNAME,
L.FNUMBER LOTNUMBER,
ISNULL(ADEL.FDATAVALUE, '') FDATAVALUE,
'公斤' BASICUNIT,
CONVERT(FLOAT,ROUND(TMP5.STOCKQTY, 2)) STOCKQTY1,
'平方米' M2,
CONVERT(FLOAT,ROUND(M2NUM, 2)) M2NUM1,
'个' GE,
CONVERT(FLOAT,ROUND(GENUM, 2)) GENUM1,
'张' ZHANG,
CONVERT(FLOAT,ROUND(ZHANGNUM, 2)) ZHANGNUM1,
'箱/卷/件' MUL,
MULNUM MULNUM1
INTO {0}
FROM {1} TMP5
LEFT JOIN T_BD_MATERIAL M ON TMP5.FMATERIALID = M.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L ML ON TMP5.FMATERIALID = ML.FMATERIALID
LEFT JOIN t_BD_Stock S ON TMP5.FSTOCKID = S.FSTOCKID
LEFT JOIN t_BD_Stock_L SL ON TMP5.FSTOCKID = SL.FSTOCKID
LEFT JOIN T_BD_LOTMASTER L ON TMP5.FLOT = L.FLOTID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L ADEL ON ADEL.fentryid = TMP5.AUXID
WHERE CONVERT(FLOAT,ROUND(TMP5.STOCKQTY, 2)) != 0 ", tmpTable6, tmpTable5);
// 物料
if (dyFilter["F_scfg_MaterialIdFilter"] != null && ((DynamicObjectCollection)dyFilter["F_scfg_MaterialIdFilter"]).Count > 0)
{
tmpSQL6.AppendLine(" AND TMP5.FMATERIALID ").Append(materialSql);
}
// 仓库
if (dyFilter["F_scfg_StockIdFilter"] != null && ((DynamicObjectCollection)dyFilter["F_scfg_StockIdFilter"]).Count > 0)
{
tmpSQL6.AppendLine(" AND TMP5.FSTOCKID ").Append(stockSql);
}
// 批号
if (dyFilter["F_scfg_BatchFilter"] != null && ((DynamicObjectCollection)dyFilter["F_scfg_BatchFilter"]).Count > 0)
{
tmpSQL6.AppendLine(" AND TMP5.FLOT ").Append(batchSql);
}
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL6.ToString());
// ---------------------------------------------------------------------------------------------------------------------
// tmpTable7 获取各个仓库多计量单位小计
StringBuilder tmpSQL7 = new StringBuilder();
tmpSQL7.AppendFormat(@"/*dialect*/ SELECT MATERIALNUMBER, MATERIALNAME, MATERIALSPECIFICATION, STOCKNUMBER, STOCKNAME, SUM(STOCKQTY1) TOTALSTOCKQTY, SUM(M2NUM1) TOTALM2NUM, SUM(GENUM1) TOTALGENUM, SUM(ZHANGNUM1) TOTALZHANGNUM, SUM(MULNUM1) TOTALMULNUM INTO {0} FROM {1} GROUP BY MATERIALNUMBER, MATERIALNAME, MATERIALSPECIFICATION, STOCKNUMBER, STOCKNAME ", tmpTable7, tmpTable6);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL7.ToString());
// 计算总计
// ----------------------------------------------------------------------------------------------------------------------
StringBuilder tmpSQL8 = new StringBuilder();
tmpSQL8.AppendFormat(@"/*dialect*/ SELECT SUM(STOCKQTY1) TOTALSTOCKQTY, SUM(M2NUM1) TOTALM2NUM, SUM(GENUM1) TOTALGENUM, SUM(ZHANGNUM1) TOTALZHANGNUM, SUM(MULNUM1) TOTALMULNUM INTO {0} FROM {1} ", tmpTable8, tmpTable6);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL8.ToString());
if (flag)
{
// 将仓库小计斤系插入总表中
// ----------------------------------------------------------------------------------------------------------------------
StringBuilder tmpSQL9 = new StringBuilder();
tmpSQL9.AppendFormat(@"/*dialect*/ INSERT INTO {0} SELECT MATERIALNUMBER, MATERIALNAME, MATERIALSPECIFICATION, STOCKNUMBER, STOCKNAME + ' - 小计', '', '', '', TOTALSTOCKQTY, '', TOTALM2NUM, '', TOTALGENUM, '', TOTALZHANGNUM, '', TOTALMULNUM FROM {1} ", tmpTable6, tmpTable7);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL9.ToString());
}
// ----------------------------------------------------------------------------------------------------------------------
StringBuilder tmpSQL11 = new StringBuilder();
tmpSQL11.AppendFormat(@"/*dialect*/ INSERT INTO {0} SELECT '合计', '', '', '', '', '', '', '', TOTALSTOCKQTY, '', TOTALM2NUM, '', TOTALGENUM, '', TOTALZHANGNUM, '', TOTALMULNUM FROM {1} ", tmpTable6, tmpTable8);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL11.ToString());
// ------------------------------------------------------------------------------------------------------------------------
// 将总体结果进行插入系统提供的tablename中
StringBuilder tmpSQL10 = new StringBuilder();
tmpSQL10.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY MATERIALNUMBER, MATERIALNAME, MATERIALSPECIFICATION, STOCKNUMBER, STOCKNAME) AS FSeq, MATERIALNUMBER, MATERIALNAME, MATERIALSPECIFICATION, STOCKNUMBER, STOCKNAME, LOTNUMBER, FDATAVALUE, BASICUNIT, CONVERT(FLOAT,ROUND(STOCKQTY1, 2)) STOCKQTY2, M2, CONVERT(FLOAT,ROUND(M2NUM1, 2)) M2NUM2, GE, CONVERT(FLOAT,ROUND(GENUM1, 2)) GENUM2, ZHANG, CONVERT(FLOAT,ROUND(ZHANGNUM1, 2)) ZHANGNUM2, MUL, CONVERT(FLOAT,ROUND(MULNUM1, 2)) MULNUM2 INTO {0} FROM {1} ORDER BY MATERIALNUMBER, MATERIALNAME, MATERIALSPECIFICATION, STOCKNUMBER, STOCKNAME ", tableName, tmpTable6);
DBUtils.ExecuteDynamicObject(this.Context, tmpSQL10.ToString());
}
