public override void BeginOperationTransaction(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.BeginOperationTransactionArgs e)
{
if (e.DataEntitys != null && e.DataEntitys.Count <DynamicObject>() > 0)
{
foreach (DynamicObject item in e.DataEntitys)
{
long id = Convert.ToInt64(item["id"]);
long custId = Convert.ToInt64(item["FCUSTOMERID_Id"]);
long orgId = Convert.ToInt64(item["F_PAEZ_OrgId_Id"]);
string beginDate = Convert.ToDateTime(item["FBeginDate"]).ToString();
DateTime endDate = Convert.ToDateTime(item["FEndDate"]);
string bfYear = Convert.ToString(endDate.Year - 1);//上一年
string strSql = string.Format(@"/*dialect*/select FRECEIVEAMOUNT,FREFUNDBILL,FJXAMOUNT,FSOAMOUNT,FREBATAMOUNT,FSALERETURNAMOUNT,FALLAMOUNT,FBalance from K_CS_FINALSTATEMENT where FCSYEAR = {0} and F_PAEZ_OrgId = {1} and FCUSTOMERID = {2}", bfYear, orgId, custId);
DynamicObjectCollection queryResult = DBUtils.ExecuteDynamicObject(this.Context, strSql);
if (queryResult.Count != 0)
{
//上一年存在年结单
}
else
{
//上一年不存在年结单
beginDate = "1970/1/1 0:00:00";
}
//获取上一年年结单中各个指标的数据
//Decimal RECEIVEAMOUN = Convert.ToDecimal(queryResult[0]["FRECEIVEAMOUNT"]);//累计收款
//Decimal JXAMOUNT = Convert.ToDecimal(queryResult[0]["FJXAMOUNT"]);//累计利息
//Decimal SOAMOUNT = Convert.ToDecimal(queryResult[0]["FSOAMOUNT"]);//累计订单
//Decimal REBATAMOUNT = Convert.ToDecimal(queryResult[0]["FREBATAMOUNT"]);//累计返利费用
//Decimal SALERETURNAMOUNT = Convert.ToDecimal(queryResult[0]["FSALERETURNAMOUNT"]);//累计退货
//Decimal ALLAMOUNT = Convert.ToDecimal(queryResult[0]["FALLAMOUNT"]);//累计应收费用
//今年各项指标的数据
//获取临时表名
TimeSpan ts = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1);
string tem = ts.TotalMilliseconds.ToString();
int index = tem.IndexOf('.');
string tp = tem.Substring(0, index);
string em = tem.Substring(index + 1, tem.Length - 1 - index);
string tmpTableName = "TMP" + tp + em;
string createSql = string.Format(@"/*dialect*/ create table {0} (FSeq bigint,FBILLNO nvarchar(255),FormId nvarchar(255),
FDATE datetime ,FCUSTOMERID int,FORGID int,FAMOUNT decimal(38, 10),
FCUSTOMERNUMBER nvarchar(255),FORMNAME nvarchar(255),FCUSTOMERNAME nvarchar(255))", tmpTableName);
DBUtils.Execute(this.Context, createSql);
string strSql_1 = string.Format(@"/*dialect*/
insert into {0} Select distinct ROW_NUMBER() OVER(order by FBILLNO) AS FSeq,
C.*
,TMPC.FNUMBER AS FCUSTOMERNUMBER
,OL.FNAME AS FORMNAME
,TMPCL.FNAME AS FCUSTOMERNAME
FROM (
SELECT
R.FBILLNO,
'AR_RECEIVEBILL' AS FormId,
R.FDATE,
R.FCONTACTUNIT AS FCUSTOMERID,
r.FSETTLEORGID AS FORGID,
SUM(RE.FRECTOTALAMOUNTFOR) AS FAMOUNT
FROM T_AR_RECEIVEBILL R
INNER JOIN T_AR_RECEIVEBILLENTRY RE ON R.FID=RE.FID
WHERE R.FBILLTYPEID='36cf265bd8c3452194ed9c83ec5e73d2'
AND R.FDOCUMENTSTATUS='C'
AND R.FCONTACTUNITTYPE='BD_Customer'
GROUP BY R.FBILLNO,R.FDATE,R.FCONTACTUNIT ,FSETTLEORGID
UNION ALL
SELECT
R.FBILLNO,
'AR_REFUNDBILL' AS FormId,
R.FDATE,
R.FCONTACTUNIT AS FCUSTOMERID,
r.FSETTLEORGID AS FORGID,
SUM(RE.FREFUNDAMOUNTFOR) AS FAMOUNT
FROM T_AR_REFUNDBILL R
INNER JOIN T_AR_REFUNDBILLENTRY RE ON R.FID=RE.FID
WHERE R.FDOCUMENTSTATUS='C'
AND R.FCONTACTUNITTYPE='BD_Customer'
GROUP BY R.FBILLNO,R.FDATE,R.FCONTACTUNIT ,FSETTLEORGID
UNION ALL
SELECT
T1.FBILLNO,
'KLM_SKJXSUMBILL' AS FormId,
T1.FREQDATE AS FDATE,
T2.FKLMCUSTOMER AS FCUSTOMERID,
t2.FKLMORGID AS FORGID,
SUM(T2.FKLMSUMAMOUNT) AS FAMOUNT
FROM T_KLMSKJXSUM T1
INNER JOIN T_KLMSKJXSUMENTRY T2 ON T1.FID=T2.FID
WHERE T1.FDOCUMENTSTATUS='C'
GROUP BY T1.FBILLNO,T1.FREQDATE,T2.FKLMCUSTOMER,t2.FKLMORGID
UNION ALL
SELECT
R.FBILLNO,
'SAL_RETURNSTOCK' AS FormId,
R.FDATE,
R.FRETCUSTID AS FCUSTOMERID,
FSALEORGID AS FORGID,
SUM(RE.FREALQTY*REF.FTAXPRICE) AS FAMOUNT
FROM T_SAL_RETURNSTOCK R
INNER JOIN T_SAL_RETURNSTOCKENTRY RE ON R.FID=RE.FID
INNER JOIN T_SAL_RETURNSTOCKENTRY_F REF ON RE.FENTRYID=REF.FENTRYID
WHERE R.FDOCUMENTSTATUS='C'
GROUP BY R.FBILLNO,R.FDATE,R.FRETCUSTID,FSALEORGID
UNION ALL
SELECT
SO.FBILLNO,
'SAL_SaleOrder' AS FormId,
SO.FDATE,SO.FCUSTID AS FCUSTOMERID,
SO.FSALEORGID AS FORGID,
SUM(SOEF.FALLAMOUNT) AS FAMOUNT
FROM T_SAL_ORDER SO
INNER JOIN T_SAL_ORDERENTRY SOE ON SO.FID=SOE.FID
INNER JOIN T_SAL_ORDERENTRY_F SOEF ON SOE.FENTRYID=SOEF.FENTRYID
WHERE SO.FDOCUMENTSTATUS='C'
AND SO.FCANCELSTATUS='A' AND SOE.FENTRYID NOT IN(
SELECT T5.FENTRYID
FROM T_SAL_OUTSTOCK T1
INNER JOIN T_SAL_OUTSTOCKENTRY T2 ON T1.FID=T2.FID
INNER JOIN T_SAL_OUTSTOCKENTRY_R T2R ON T2.FENTRYID=T2R.FENTRYID
INNER JOIN T_AR_RECEIVABLEENTRY_LK T3LK ON T3LK.FSID=T2.FENTRYID AND T3LK.FSBILLID=T2.FID AND T3LK.FSTABLENAME='T_SAL_OUTSTOCKENTRY'
INNER JOIN T_AR_RECEIVABLEENTRY T3 ON T3.FENTRYID=T3LK.FENTRYID
INNER JOIN T_AR_RECEIVABLE T4 ON T3.FID=T4.FID AND T4.FSETACCOUNTTYPE='3'
INNER JOIN T_SAL_ORDERENTRY T5 ON T5.FENTRYID=T2R.FSOENTRYID
INNER JOIN T_SAL_ORDER T6 ON T6.FBILLNO=T2R.FSOORDERNO
)
GROUP BY SO.FBILLNO,SO.FDATE,SO.FCUSTID,SO.FSALEORGID
UNION ALL
SELECT
SO.FBILLNO,
'SAL_OUTSTOCK' AS FormId,
SO.FDATE,SO.FCustomerID AS FCUSTOMERID,
SO.FSALEORGID AS FORGID,
SUM(SOEF.FALLAMOUNT) AS FAMOUNT
FROM T_SAL_OUTSTOCK SO
INNER JOIN T_SAL_OUTSTOCKENTRY SOE ON SO.FID=SOE.FID
INNER JOIN T_SAL_OUTSTOCKENTRY_R SOER ON SOE.FENTRYID=SOER.FENTRYID
INNER JOIN T_SAL_OUTSTOCKENTRY_F SOEF ON SOE.FENTRYID=SOEF.FENTRYID
WHERE SO.FDOCUMENTSTATUS='C'
AND SO.FCANCELSTATUS='A'AND SOER.FSRCTYPE =''
AND SOE.FENTRYID NOT IN(
SELECT DISTINCT T2.FENTRYID
FROM T_SAL_OUTSTOCK T1
INNER JOIN T_SAL_OUTSTOCKENTRY T2 ON T1.FID=T2.FID
INNER JOIN T_SAL_OUTSTOCKENTRY_R T2R ON T2.FENTRYID=T2R.FENTRYID
INNER JOIN T_AR_RECEIVABLEENTRY_LK T3LK ON T3LK.FSID=T2.FENTRYID AND T3LK.FSBILLID=T2.FID AND T3LK.FSTABLENAME='T_SAL_OUTSTOCKENTRY'
INNER JOIN T_AR_RECEIVABLEENTRY T3 ON T3.FENTRYID=T3LK.FENTRYID
INNER JOIN T_AR_RECEIVABLE T4 ON T3.FID=T4.FID
WHERE FSRCTYPE ='' and t4.FSETACCOUNTTYPE='3')
GROUP BY SO.FBILLNO,SO.FDATE,SO.FCustomerID,SO.FSALEORGID
UNION ALL
SELECT
R.FBILLNO,
'KLM_RebateBill' AS FormId,
R.FDATE,
R.FCUSTOMERID AS FCUSTOMERID,
R.F_KLM_ORGID AS FORGID,
SUM(FAMOUNT) AS FAMOUNT
FROM T_KLM_RebateBill R
GROUP BY R.FBILLNO,R.FDATE,R.FCUSTOMERID,R.F_KLM_ORGID
UNION ALL
select FBILLNO,'AR_receivable' as FormId,FDATE,FCUSTOMERID,FPAYORGID AS FORGID,sum(FALLAMOUNT) as FAMOUNT
from t_AR_receivable TAR,t_AR_receivableFIN TARF
where TAR.FID=TARF.FID and FBILLTYPEID='00505694799c955111e325bc9e6eb056' and FDOCUMENTSTATUS='c'
GROUP BY FBILLNO,FDATE,FCUSTOMERID,FPAYORGID
UNION ALL
select FBILLNO,'PAEZ_Balance_Aadjustment' as FormId, FDATE,FCUSTOMERID,F_PAEZ_OrgId as FORGID , sum(FAmount) as FAMOUNT
from K_CS_CAUSEOfADJUSTMENT
where FDOCUMENTSTATUS = 'c'
GROUP BY FBILLNO,FDATE,FCUSTOMERID,F_PAEZ_OrgId
) AS C
INNER JOIN T_BD_CUSTOMER TMPC ON C.FCUSTOMERID=TMPC.FCUSTID
INNER JOIN T_BD_CUSTOMER_L TMPCL ON TMPC.FCUSTID=TMPCL.FCUSTID AND TMPCL.FLOCALEID=2052
INNER JOIN T_META_OBJECTTYPE_L OL ON C.FORMID=OL.FID AND OL.FLOCALEID=2052
where tmpc.FCUSTID={1} and c.FDATE>= '{2}' and c.FDATE<= '{3}' and c.FORGID ={4}
", tmpTableName, custId, beginDate, endDate, orgId);
DBUtils.Execute(this.Context, strSql_1);
string strSql_2 = string.Format(@"/*dialect*/
SELECT
FRebatAMOUNT + {0} as FRebatAMOUNT,
FREFUNDBILL + {8} as FREFUNDBILL,
FSOAMOUNT + {1} as FSOAMOUNT,
FReceiveAMOUNT+ {2} as FReceiveAMOUNT,
FSaleReturnAMOUNT+ {3} as FSaleReturnAMOUNT,
FJXAMOUNT+ {4} as FJXAMOUNT,
Freceivable+ {5} as Freceivable,
FBalance+{6} as FBalance
FROM (
select FCUSTOMERName,FCUSTOMERNUMBER,FCUSTOMERID,FORGID,
cast(sum(case when formId='KLM_RebateBill' then fAmount else 0 end ) as decimal(18,2)) as FRebatAMOUNT,
cast(sum(case when formId='AR_REFUNDBILL' then fAmount else 0 end ) as decimal(18,2)) as FREFUNDBILL,
cast(sum(case when formId='SAL_SaleOrder' then fAmount else 0 end ) as decimal(18,2)) as FSOAMOUNT,
cast(sum(case when formId='AR_RECEIVEBILL' then fAmount else 0 end ) as decimal(18,2)) as FReceiveAMOUNT,
cast(sum(case when formId='SAL_RETURNSTOCK' then fAmount else 0 end ) as decimal(18,2)) as FSaleReturnAMOUNT,
cast(sum(case when formId='KLM_SKJXSUMBILL' then fAmount else 0 end ) as decimal(18,2)) as FJXAMOUNT,
cast(sum(case when formId='AR_receivable' then fAmount else 0 end ) as decimal(18,2)) as Freceivable,
cast(sum(case when formId='PAEZ_Balance_Aadjustment' then fAmount else 0 end ) as decimal(18,2)) as FBalance
from {7}
group by FCUSTOMERName,FCUSTOMERNUMBER,FCUSTOMERID,FORGID
) C
", queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FREBATAMOUNT"]), queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FSOAMOUNT"]),
queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FRECEIVEAMOUNT"]), queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FSALERETURNAMOUNT"]),
queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FJXAMOUNT"]), queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FALLAMOUNT"]), queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FBalance"]), tmpTableName, queryResult.Count == 0 ? 0 : Convert.ToDecimal(queryResult[0]["FREFUNDBILL"]));
DynamicObjectCollection finalResult = DBUtils.ExecuteDynamicObject(this.Context, strSql_2);
if (finalResult.Count <= 0)
{
string strSql_5 = string.Format(@"/*dialect*/UPDATE K_CS_FINALSTATEMENT set FRECEIVEAMOUNT = 0,FJXAMOUNT = 0,FSOAMOUNT = 0,FREBATAMOUNT = 0,FSALERETURNAMOUNT = 0,FALLAMOUNT = 0,FBalance = 0,FCSAMOUNT = 0,FREFUNDBILL = 0 where FID = {0}", id);
DBUtils.Execute(this.Context, strSql_5);
string strSql_6 = string.Format(@"/*dialect*/drop table {0}", tmpTableName);
DBUtils.Execute(this.Context, strSql_6);
continue;
}
string strSql_3 = string.Format(@"/*dialect*/UPDATE K_CS_FINALSTATEMENT set FRECEIVEAMOUNT = {0},FJXAMOUNT = {1},FSOAMOUNT = {2},FREBATAMOUNT = {3},FSALERETURNAMOUNT = {4},FALLAMOUNT = {5},FBalance = {6},FCSAMOUNT = {7},FREFUNDBILL ={9} where FID = {8}",
finalResult[0]["FReceiveAMOUNT"], finalResult[0]["FJXAMOUNT"], finalResult[0]["FSOAMOUNT"], finalResult[0]["FRebatAMOUNT"], finalResult[0]["FSaleReturnAMOUNT"], finalResult[0]["Freceivable"], finalResult[0]["FBalance"],
Convert.ToDecimal(finalResult[0]["FReceiveAMOUNT"]) + Convert.ToDecimal(finalResult[0]["FJXAMOUNT"]) - Convert.ToDecimal(finalResult[0]["FSOAMOUNT"]) + Convert.ToDecimal(finalResult[0]["FRebatAMOUNT"]) + Convert.ToDecimal(finalResult[0]["FSaleReturnAMOUNT"]) - Convert.ToDecimal(finalResult[0]["Freceivable"]) + Convert.ToDecimal(finalResult[0]["FBalance"]) - Convert.ToDecimal(finalResult[0]["FREFUNDBILL"]), id, finalResult[0]["FREFUNDBILL"]);
DBUtils.Execute(this.Context, strSql_3);
string strSql_4 = string.Format(@"/*dialect*/drop table {0}", tmpTableName);
DBUtils.Execute(this.Context, strSql_4);
}
}
}
public override void BeforeExecuteOperationTransaction(BeforeExecuteOperationTransaction e)
{
base.BeforeExecuteOperationTransaction(e);
IEnumerable <ExtendedDataEntity> extendedDataEntities = e.SelectedRows;
foreach (ExtendedDataEntity extendedDataEntity in extendedDataEntities)
{
DynamicObject billInfo = extendedDataEntity.DataEntity;
DynamicObject bomInfor = billInfo["FBOMID"] as DynamicObject;
if (bomInfor == null)
{
continue;
}
DynamicObjectCollection subItemInfo = bomInfor["TreeEntity"] as DynamicObjectCollection;
if (subItemInfo == null || subItemInfo.Count == 0)
{
continue;
}
DynamicObjectCollection entryRows = billInfo["PPBomEntry"] as DynamicObjectCollection;
if (entryRows == null || entryRows.Count == 0)
{
continue;
}
foreach (var item in entryRows)
{
decimal qty = Convert.ToDecimal(item["FJNUnitEnzymes"]);
if (qty > 0)
{
continue;
}
var subQty = subItemInfo.FirstOrDefault(f => f["MATERIALIDCHILD_Id"].ToString() == item["MaterialID_Id"].ToString() &&
f["AuxPropID_Id"].ToString() == item["AuxPropId_Id"].ToString());
if (subQty != null)
{
var jnqty = Convert.ToDecimal(subQty["FJNCompanyEA"]);
var bomQty = Convert.ToDecimal(item["F_JN_BOMQty"]);
item["FJNUnitEnzymes"] = jnqty;
item["F_JN_EnzymeSumQty"] = jnqty * bomQty;
continue;
}
subQty = subItemInfo.FirstOrDefault(f => f["MATERIALIDCHILD_Id"].ToString() == item["MaterialID_Id"].ToString());
if (subQty != null)
{
var jnqty = Convert.ToDecimal(subQty["FJNCompanyEA"]);
var bomQty = Convert.ToDecimal(item["F_JN_BOMQty"]);
item["FJNUnitEnzymes"] = jnqty;
item["F_JN_EnzymeSumQty"] = jnqty * bomQty;
}
}
}
}
/// <summary>
/// 获取数据规则下销售员id集合
/// </summary>
/// <param name="ctx"></param>
/// <param name="personId"></param>
/// <returns></returns>
public List <long> getSalerPersonids(Context ctx, long personId)
{
List <long> salerPersonIds = new List <long>();
//判断personId是否在CRM汇报关系设置表中
string strSql_1 = string.Format(@"/*dialect*/SELECT * FROM PEJK_RPTSHIP WHERE F_PEJK_MANAGER = {0}", personId);
DynamicObjectCollection headCol = DBUtils.ExecuteDynamicObject(ctx, strSql_1);
if (headCol == null || headCol.Count == 0)
{
string strSql_2 = string.Format(@"/*dialect*/SELECT * FROM PEJK_RPTSHIPENTRY WHERE F_PEJK_TEAMMEMBER = {0}", personId);
DynamicObjectCollection entryCol = DBUtils.ExecuteDynamicObject(ctx, strSql_2);
if (entryCol == null || entryCol.Count == 0)
{
return(null);
}
}
string sql = string.Format(@"/*dialect*/SELECT B.F_PEJK_TEAMMEMBER
FROM PEJK_RPTSHIP A
INNER JOIN PEJK_RPTSHIPENTRY B
ON A.FID = B.FID
WHERE A.F_PEJK_MANAGER = {0}
UNION ALL
SELECT BB.F_PEJK_TEAMMEMBER
FROM PEJK_RPTSHIP A
INNER JOIN PEJK_RPTSHIPENTRY B
ON A.FID = B.FID
INNER JOIN PEJK_RPTSHIP AA
ON B.F_PEJK_TEAMMEMBER = AA.F_PEJK_MANAGER
INNER JOIN PEJK_RPTSHIPENTRY BB
ON AA.FID = BB.FID
WHERE A.F_PEJK_MANAGER = {0}
UNION ALL
SELECT BBB.F_PEJK_TEAMMEMBER
FROM PEJK_RPTSHIP A
INNER JOIN PEJK_RPTSHIPENTRY B
ON A.FID = B.FID
INNER JOIN PEJK_RPTSHIP AA
ON B.F_PEJK_TEAMMEMBER = AA.F_PEJK_MANAGER
INNER JOIN PEJK_RPTSHIPENTRY BB
ON AA.FID = BB.FID
INNER JOIN PEJK_RPTSHIP AAA
ON BB.F_PEJK_TEAMMEMBER = AAA.F_PEJK_MANAGER
INNER JOIN PEJK_RPTSHIPENTRY BBB
ON AAA.FID = BBB.FID
WHERE A.F_PEJK_MANAGER = {0}
UNION ALL
SELECT BBBB.F_PEJK_TEAMMEMBER
FROM PEJK_RPTSHIP A
INNER JOIN PEJK_RPTSHIPENTRY B
ON A.FID = B.FID
INNER JOIN PEJK_RPTSHIP AA
ON B.F_PEJK_TEAMMEMBER = AA.F_PEJK_MANAGER
INNER JOIN PEJK_RPTSHIPENTRY BB
ON AA.FID = BB.FID
INNER JOIN PEJK_RPTSHIP AAA
ON BB.F_PEJK_TEAMMEMBER = AAA.F_PEJK_MANAGER
INNER JOIN PEJK_RPTSHIPENTRY BBB
ON AAA.FID = BBB.FID
INNER JOIN PEJK_RPTSHIP AAAA
ON BBB.F_PEJK_TEAMMEMBER = AAAA.F_PEJK_MANAGER
INNER JOIN PEJK_RPTSHIPENTRY BBBB
ON AAAA.FID = BBBB.FID
WHERE A.F_PEJK_MANAGER = {0}
UNION ALL
SELECT {0}", personId);
DynamicObjectCollection personIds = DBUtils.ExecuteDynamicObject(ctx, sql);
if (personIds != null && personIds.Count() > 0)
{
foreach (var item in personIds)
{
salerPersonIds.Add(Convert.ToInt64(item["F_PEJK_TEAMMEMBER"]));
}
string Ids = string.Join(",", salerPersonIds);
string strSql = string.Format(@"/*dialect*/SELECT V.FID
FROM T_BD_PERSON A
INNER JOIN T_BD_STAFF B
ON A.FPERSONID = B.FPERSONID
INNER JOIN V_BD_SALESMAN V
ON B.FSTAFFID = V.FSTAFFID
WHERE A. FPERSONID IN ({0})
", Ids);
DynamicObjectCollection salerIds = DBUtils.ExecuteDynamicObject(ctx, strSql);
if (salerIds != null && salerIds.Count() > 0)
{
salerPersonIds.Clear();
foreach (var item in salerIds)
{
salerPersonIds.Add(Convert.ToInt64(item["FID"]));
}
return(salerPersonIds);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
/// <summary>
/// 获取全部线下订单
/// </summary>
/// <param name="flag"></param>
/// <param name="saleOrderNo"></param>
/// <returns></returns>
public List <K3SalOrderInfo> GetAllSalOrders(bool flag = true, string saleOrderNo = null)
{
List <K3SalOrderInfo> orders = null;
K3SalOrderInfo order = null;
List <K3SalOrderEntryInfo> entries = null;
string sql = string.Empty;
if (flag)
{
sql = string.Format(@"/*dialect*/select FBillNo,F_HS_OriginOnlineOrderNo,FDate,a.FNOTE,r.FNUMBER as FCUSTID,o.FNUMBER as F_HS_B2CCustId,FSalerId,g.FNUMBER as F_HS_SaleOrderSource,w.FNUMBER as FSettleCurrId,F_HS_RateToUSA,x.FNUMBER as F_HS_RecipientCountry,F_HS_DeliveryProvinces
,F_HS_DeliveryCity,F_HS_DeliveryAddress,F_HS_PostCode,F_HS_DeliveryName,F_HS_BillAddress,F_HS_MobilePhone,F_HS_ShippingMethod,n.FNUMBER as F_HS_PaymentModeNew
,F_HS_PaymentStatus,F_HS_ShippingMethodRemark,F_HS_OnlineOrderWay,F_HS_CouponAmount,F_HS_IntegralDeduction,v.FNUMBER as FMaterialId,c.FTAXPRICE,FAmount,FQty,k.FNUMBER as F_HS_StockID
,F_HS_IsVirtualEntry,F_HS_IsEmptyStock,c.FIsFree,a.FApproveDate,F_HS_CollectionTime
from T_SAL_ORDER a
inner join T_SAL_ORDERENTRY b on b.FID = a.FID
inner join T_SAL_ORDERENTRY_F c on c.FENTRYID = b.FENTRYID and c.FID = b.FID
inner join T_SAL_ORDERFIN d on d.FID = a.FID
inner join T_BD_CUSTOMER e on e.FCUSTID= a.FCUSTID
inner join T_BAS_ASSISTANTDATAENTRY_L f ON a.F_HS_SaleOrderSource=f.FENTRYID and f.FLOCALEID = 2052
inner join T_BAS_ASSISTANTDATAENTRY g ON f.FENTRYID=g.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY_L m ON a.F_HS_PaymentModeNew=m.FENTRYID and m.FLOCALEID = 2052
inner join T_BAS_ASSISTANTDATAENTRY n ON m.FENTRYID=n.FENTRYID
left join T_BAS_BILLTYPE h on a.FBILLTypeID=h.FBILLTypeID
inner join T_ORG_ORGANIZATIONS z on a.FSALEORGID=z.FORGID
inner join VW_BAS_ASSISTANTDATA_CountryName x on x.FCountry = a.F_HS_RecipientCountry
inner join T_BD_MATERIAL v on v.FMATERIALID = b.FMATERIALID
inner join T_BD_CURRENCY w on w.FCURRENCYID = d.FSETTLECURRID
inner join T_BD_CUSTOMER r on r.FCUSTID = a.FCUSTID
inner join T_BD_CUSTOMER o on o.FCUSTID = a.F_HS_B2CCustId
left join T_BD_STOCK i on i.FSTOCKID=b.F_HS_StockID
left join T_BAS_ASSISTANTDATAENTRY_L j ON i.F_HS_DLC=j.FENTRYID and j.FLOCALEID = 2052
left join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where (a.FDate >= DATEADD(day,-180,GETDATE()) or a.F_HS_CollectionTime >= DATEADD(day,-180,GETDATE()))and a.FDOCUMENTSTATUS = 'C' and g.fnumber = 'XXBJDD' and FBillNo like 'SO%' and a.FsaleOrgID = 100035 and a.FCANCELSTATUS<>'B' and h.FNUMBER='XSDD01_SYS' and z.FNUMBER='100.01'
and not exists(select * From T_SAL_ORDERentry where fid=a.fid and a.FCLOSESTATUS='B' and FMRPCLOSESTATUS='A')
and a.fchangeDate>= DATEADD(day,-180,GETDATE())
");
}
else
{
sql = string.Format(@"/*dialect*/select FBillNo,F_HS_OriginOnlineOrderNo,FDate,a.FNOTE,r.FNUMBER as FCUSTID,o.FNUMBER as F_HS_B2CCustId,FSalerId,g.FNUMBER as F_HS_SaleOrderSource,w.FNUMBER as FSettleCurrId,F_HS_RateToUSA,x.FNUMBER as F_HS_RecipientCountry,F_HS_DeliveryProvinces
,F_HS_DeliveryCity,F_HS_DeliveryAddress,F_HS_PostCode,F_HS_DeliveryName,F_HS_BillAddress,F_HS_MobilePhone,F_HS_ShippingMethod,n.FNUMBER as F_HS_PaymentModeNew
,F_HS_PaymentStatus,F_HS_ShippingMethodRemark,F_HS_OnlineOrderWay,F_HS_CouponAmount,F_HS_IntegralDeduction,v.FNUMBER as FMaterialId,c.FTAXPRICE,FAmount,FQty,k.FNUMBER as F_HS_StockID
,F_HS_IsVirtualEntry,F_HS_IsEmptyStock,c.FIsFree,a.FApproveDate,F_HS_CollectionTime
from T_SAL_ORDER a
inner join T_SAL_ORDERENTRY b on b.FID = a.FID
inner join T_SAL_ORDERENTRY_F c on c.FENTRYID = b.FENTRYID and c.FID = b.FID
inner join T_SAL_ORDERFIN d on d.FID = a.FID
inner join T_BD_CUSTOMER e on e.FCUSTID= a.FCUSTID
inner join T_BAS_ASSISTANTDATAENTRY_L f ON a.F_HS_SaleOrderSource=f.FENTRYID and f.FLOCALEID = 2052
inner join T_BAS_ASSISTANTDATAENTRY g ON f.FENTRYID=g.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY_L m ON a.F_HS_PaymentModeNew=m.FENTRYID and m.FLOCALEID = 2052
inner join T_BAS_ASSISTANTDATAENTRY n ON m.FENTRYID=n.FENTRYID
left join T_BAS_BILLTYPE h on a.FBILLTypeID=h.FBILLTypeID
inner join T_ORG_ORGANIZATIONS z on a.FSALEORGID=z.FORGID
inner join VW_BAS_ASSISTANTDATA_CountryName x on x.FCountry = a.F_HS_RecipientCountry
inner join T_BD_MATERIAL v on v.FMATERIALID = b.FMATERIALID
inner join T_BD_CURRENCY w on w.FCURRENCYID = d.FSETTLECURRID
inner join T_BD_CUSTOMER r on r.FCUSTID = a.FCUSTID
inner join T_BD_CUSTOMER o on o.FCUSTID = a.F_HS_B2CCustId
left join T_BD_STOCK i on i.FSTOCKID=b.F_HS_StockID
left join T_BAS_ASSISTANTDATAENTRY_L j ON i.F_HS_DLC=j.FENTRYID and j.FLOCALEID = 2052
left join T_BAS_ASSISTANTDATAENTRY k ON j.FentryID=k.FentryID
where (a.FDate >= DATEADD(day,-45,GETDATE()) or a.F_HS_CollectionTime >= DATEADD(day,-45,GETDATE()))and a.FDOCUMENTSTATUS = 'C' and g.fnumber = 'XXBJDD' and FBillNo like 'SO%' and a.FsaleOrgID = 100035 and a.FCANCELSTATUS<>'B' and h.FNUMBER='XSDD01_SYS' and z.FNUMBER='100.01'
and not exists(select * From T_SAL_ORDERentry where fid=a.fid and a.FCLOSESTATUS='B' and FMRPCLOSESTATUS='A')
");
}
DynamicObjectCollection coll = SQLUtils.GetObjects(this.K3CloudContext, sql);
orders = GetK3SalOrderInfo(coll);
if (orders != null && orders.Count > 0)
{
var group = from o in orders group o by o.FBillNo into g select g;
if (group != null && group.Count() > 0)
{
orders = new List <K3SalOrderInfo>();
entries = new List <K3SalOrderEntryInfo>();
foreach (var g in group)
{
if (g != null && g.Count() > 0)
{
order = g.ElementAt(0) as K3SalOrderInfo;;
entries = GetK3SalOrderEntryInfo(g.ToList <K3SalOrderInfo>());
order.OrderEntry = null;
order.OrderEntry = entries;
order.F_HS_Total = order.F_HS_Subtotal + order.F_HS_Shipping;//优惠后金额
orders.Add(order);
}
}
}
}
return(orders);
}
/// <summary>
/// 获取销售政策内容
/// </summary>
/// <param name="ctx"></param>
/// <param name="deptId">销售部门</param>
/// <param name="Date">业务日期</param>
/// <returns></returns>
public Dictionary <string, double> GetDiscounts(Context ctx, long deptId, DateTime Date)
{
Dictionary <string, double> disCounts = new Dictionary <string, double>();
//0面包，1粽子，2月饼
//获取部门面包折扣
string breadDiscountSql = string.Format(@"select distinct bb.fcategory, bb.fdiscount
from paez_salepolicy aa
inner join paez_salepolicyentry bb
on aa.fid = bb.fid
and aa.fcreatedate =
(select max(a.fcreatedate)
from paez_salepolicy a
inner join paez_salepolicyentry b
on a.fid = b.fid
where to_date('{0}','yyyy/MM/dd') >= a.fstartdate
and to_date('{0}','yyyy/MM/dd') <= a.fenddate
and b.fdeptid = {1}
and b.fcategory = 0)
and bb.fdeptid = {1} and bb.fcategory = 0", Date.ToShortDateString(), deptId);
DynamicObjectCollection breadDiscounts = DBUtils.ExecuteDynamicObject(ctx, breadDiscountSql);
if (breadDiscounts != null && breadDiscounts.Count() > 0)
{
disCounts.Add(Convert.ToString(breadDiscounts[0]["fcategory"]), Convert.ToDouble(breadDiscounts[0]["fdiscount"]));
}
//获取部门月饼折扣
string moonCakeDiscountSql = string.Format(@"select distinct bb.fcategory, bb.fdiscount
from paez_salepolicy aa
inner join paez_salepolicyentry bb
on aa.fid = bb.fid
and aa.fcreatedate =
(select max(a.fcreatedate)
from paez_salepolicy a
inner join paez_salepolicyentry b
on a.fid = b.fid
where to_date('{0}','yyyy/MM/dd') >= a.fstartdate
and to_date('{0}','yyyy/MM/dd') <= a.fenddate
and b.fdeptid = {1}
and b.fcategory = 2)
and bb.fdeptid = {1} and bb.fcategory = 2", Date.ToShortDateString(), deptId);
DynamicObjectCollection moonCakeDiscounts = DBUtils.ExecuteDynamicObject(ctx, moonCakeDiscountSql);
if (moonCakeDiscounts != null && moonCakeDiscounts.Count() > 0)
{
disCounts.Add(Convert.ToString(moonCakeDiscounts[0]["fcategory"]), Convert.ToDouble(moonCakeDiscounts[0]["fdiscount"]));
}
//获取部门粽子折扣
string ChineseCakeDiscountSql = string.Format(@"select distinct bb.fcategory, bb.fdiscount
from paez_salepolicy aa
inner join paez_salepolicyentry bb
on aa.fid = bb.fid
and aa.fcreatedate =
(select max(a.fcreatedate)
from paez_salepolicy a
inner join paez_salepolicyentry b
on a.fid = b.fid
where to_date('{0}','yyyy/MM/dd') >= a.fstartdate
and to_date('{0}','yyyy/MM/dd') <= a.fenddate
and b.fdeptid = {1}
and b.fcategory = 1)
and bb.fdeptid = {1} and bb.fcategory = 1", Date.ToShortDateString(), deptId);
DynamicObjectCollection ChineseCakeDiscounts = DBUtils.ExecuteDynamicObject(ctx, ChineseCakeDiscountSql);
if (ChineseCakeDiscounts != null && ChineseCakeDiscounts.Count() > 0)
{
disCounts.Add(Convert.ToString(ChineseCakeDiscounts[0]["fcategory"]), Convert.ToDouble(ChineseCakeDiscounts[0]["fdiscount"]));
}
return(disCounts);
}
public void Run(Context ctx, Schedule schedule)
{
ctx.UserId = 101901;
ctx.UserName = "******";
string strDateSql = string.Format(@"/*dialect*/select top 1 fdate from altablein where status=3 and isPur =0 and ferrorstatus=0 order by fdate");
DynamicObjectCollection dateData = DBUtils.ExecuteDynamicObject(ctx, strDateSql, null);
if (dateData.Count() > 0)
{
//添加按照车间分组取不同的车间
string strproducesql = string.Format(@"/*dialect*/select distinct tbm.FWORKSHOPID FWORKSHOPID
from altablein alin
left join T_SAL_ORDER tso on alin.salenumber=tso.FBILLNO
left join T_SAL_ORDERENTRY tsoe on tso.fid=tsoe.FID and alin.Linenumber=tsoe.FSEQ
left join t_BD_MaterialBase tbmb on tbmb.FMATERIALID=tsoe.FMATERIALID
left join t_BD_MaterialProduce tbm on tbm.FMATERIALID=tbmb.FMATERIALID
where status=3 and tbm.FWORKSHOPID>0 and tbm.FWORKSHOPID>0 and isPur =0 and ferrorstatus=0 and alin.fdate='{0}'", Convert.ToDateTime(dateData[0]["fdate"]));
DynamicObjectCollection workshopDataObject = DBUtils.ExecuteDynamicObject(ctx, strproducesql, null);
foreach (DynamicObject itemworkshopDataObject in workshopDataObject)
{
long workshopid = Convert.ToInt64(itemworkshopDataObject["FWORKSHOPID"]);//取车间ID
if (_3DServiceHelper.isTransfer(ctx, ObjectEnum.AlInStock, UpdateAltableinEnum.BeforeSave))
{
//调用新的根据生产车间ID
transferData = _3DServiceHelper.getALInstockSaveData(ctx, UpdateAltableinEnum.BeforeSave, ObjectEnum.AlInStock, workshopid); //本次执行计划处理的数据
long[] bb = transferData.SalOrder2DirectTrans.Select(s => s.altID).ToArray(); //本次执行计划要处理的数据的主键ID
//根据主键对数据标识进行更新，同时设置更新时间戳
_3DServiceHelper.updateAltableStatus(ctx, UpdateAltableinEnum.AfterGetDate, ObjectEnum.AlInStock, bb);
List <DynamicObject> modelList = new List <DynamicObject>();
Action <IDynamicFormViewService> fillBillPropertys = new Action <IDynamicFormViewService>(fillPropertys);
DynamicObject billModel = _3DServiceHelper.CreateBillMode(ctx, "SP_InStock", fillBillPropertys);
modelList.Add(billModel);
DynamicObject[] model = modelList.Select(p => p).ToArray() as DynamicObject[];
IOperationResult saveResult = _3DServiceHelper.BatchSave(ctx, "SP_InStock", model);
String id = Convert.ToString(model[0]["id"]);
//对结果进行处理
//数据包整理完成后，将实际的分录主键反写回altablein表，便于未来反写其他状态所用
List <UpdateAltableinEntity> updateAltList = _3DServiceHelper.InstallUpdateAlPackage(ctx, UpdateAltableinEnum.AfterCreateModel, ObjectEnum.AlInStock, model, null, null, null, null, 0, "", "Entity");
if (updateAltList != null)
{
_3DServiceHelper.updateAltableStatus(ctx, UpdateAltableinEnum.AfterCreateModel, ObjectEnum.AlInStock, null, updateAltList);
}
//处理保存结果，成功、失败
if (saveResult.SuccessDataEnity != null)
{
object[] ids = (from c in saveResult.SuccessDataEnity
select c[0]).ToArray();//保存成功的结果
IOperationResult submitResult = ids.Count() > 0 ? _3DServiceHelper.Submit(ctx, "SP_InStock", ids) : null;
if (submitResult.SuccessDataEnity != null)
{
List <UpdateAltableinEntity> exceptPrtList = new List <UpdateAltableinEntity>();
List <UpdateAltableinEntity> successPrtList = new List <UpdateAltableinEntity>();
foreach (DynamicObject item in submitResult.SuccessDataEnity)
{
object[] ips = new object[] { item[0] };
IOperationResult auditResult = _3DServiceHelper.Audit(ctx, "SP_InStock", ips);
if (auditResult.IsSuccess)
{
successPrtList = _3DServiceHelper.InstallUpdateAlPackage(ctx, UpdateAltableinEnum.AuditSucess, ObjectEnum.AlInStock, null, null, successPrtList, auditResult, null);
//更新altablein表审核成功信息
_3DServiceHelper.updateAltableStatus(ctx, UpdateAltableinEnum.AuditSucess, ObjectEnum.AlInStock, null, successPrtList);
}
else if (!auditResult.InteractionContext.SimpleMessage.Equals("") && auditResult.InteractionContext.SimpleMessage != null)
{
exceptPrtList = _3DServiceHelper.InstallUpdateAlPackage(ctx, UpdateAltableinEnum.AuditError, ObjectEnum.AlInStock, null, null, exceptPrtList, auditResult, item);
//更新altablein表 审核错误信息
_3DServiceHelper.updateAltableStatus(ctx, UpdateAltableinEnum.AuditError, ObjectEnum.AlInStock, null, exceptPrtList);
//插入审核错误信息进入错误信息表
_3DServiceHelper.insertAllocationtableTable(ctx, UpdateAltableinEnum.AuditError, ObjectEnum.AlInStock, id);
}
}
}
}
if (((List <ValidationErrorInfo>)saveResult.ValidationErrors).Count() > 0)
{
List <UpdateAltableinEntity> updateVoSavePrtList = _3DServiceHelper.InstallUpdateAlPackage(ctx, UpdateAltableinEnum.SaveError, ObjectEnum.AlInStock, null, saveResult);
//更新altablein表状态
_3DServiceHelper.updateAltableStatus(ctx, UpdateAltableinEnum.SaveError, ObjectEnum.AlInStock, bb, updateVoSavePrtList);
//插入保存错误信息进入错误信息表
_3DServiceHelper.insertAllocationtableTable(ctx, UpdateAltableinEnum.SaveError, ObjectEnum.AlInStock, id);
}
}
}
}
}
public void GetBudgetDimensionInfo()
{
DynamicObjectCollection objects = this.dyRptScheme["DimensionEntity"] as DynamicObjectCollection;
this.dicDimensionInfo.Clear();
this.dicDimensionField.Clear();
int num = 1;
foreach (DynamicObject obj2 in objects)
{
DynamicObject obj3 = obj2["DimensionID"] as DynamicObject;
if (((obj3 != null) && (Convert.ToString(obj3["BaseDataType_Id"]) != "BM_BUDGETCALENDAR")) && (Convert.ToString(obj3["BaseDataType_Id"]) != "BM_DEPTORG"))
{
string str = Convert.ToString(obj3["Category"]);
string formId = Convert.ToString(obj3["BaseDataType_Id"]);
int key = Convert.ToInt32(obj3["Id"]);
DimensionInfo info = new DimensionInfo {
Id = key,
Name = Convert.ToString(obj3["Name"]),
Category = str,
DataControlType = 1
};
if (str == "10")
{
formId = "BOS_ASSISTANTDATA_SELECT";
info.PKFieldName = "FEntryID";
info.TableName = "T_BAS_ASSISTANTDATAENTRY";
info.NumberFieldName = "FNUMBER";
info.NameFieldName = "FDATAVALUE";
info.NameTableName = "T_BAS_ASSISTANTDATAENTRY_L";
}
else
{
info.FormId = formId;
BusinessInfo demensionMetaData = this.GetDemensionMetaData(formId);
info.PKFieldName = demensionMetaData.GetForm().PkFieldName;
string nameFieldKey = demensionMetaData.GetForm().NameFieldKey;
if (!string.IsNullOrWhiteSpace(nameFieldKey))
{
info.NameFieldName = nameFieldKey;
if (demensionMetaData.GetField(nameFieldKey) is MultiLangTextField)
{
info.NameTableName = demensionMetaData.GetField(nameFieldKey).TableName + "_L";
}
else
{
info.NameTableName = demensionMetaData.GetField(nameFieldKey).TableName;
}
}
info.NumberFieldName = demensionMetaData.GetForm().NumberFieldKey;
info.TableName = demensionMetaData.GetField(demensionMetaData.GetForm().NumberFieldKey).TableName;
int baseDataType = ServiceHelper.GetService <IOrganizationService>().GetBaseDataType(base.Context, formId);
if (baseDataType == 2)
{
string masterPKFieldName = demensionMetaData.GetForm().MasterPKFieldName;
if (!string.IsNullOrWhiteSpace(masterPKFieldName))
{
info.DataControlType = baseDataType;
info.MasterIDFieldName = masterPKFieldName;
}
}
}
if (this.filterParameter.DicDimissionFilter.ContainsKey(key))
{
info.IsShowField = true;
string str5 = string.Format("FDIMENSIONID{0}", num);
string str6 = string.Format("FDIMENSIONNAME{0}", num);
info.ShowFieldId = str5;
info.ShowFieldName = str6;
this.dicDimensionField.Add(str5, str6);
num++;
}
else
{
info.IsShowField = false;
}
this.dicDimensionInfo.Add(key, info);
}
}
}
private void SetBillValue(Context context, DataSet fillData, IBillView view, int erow, int XQty, DynamicObject orgobj, string NoType)
{
DateTime dt = DateTime.Now;
if (fillData != null && fillData.Tables.Count > 0 && fillData.Tables[0].Rows.Count > 0)
{
string value = "";
DynamicObjectCollection entry1 = this.Model.DataObject["SZXY_XYDCJXXBEntry"] as DynamicObjectCollection;
for (int i = 0; i < fillData.Tables[0].Rows.Count; i++)
{
int mm = erow + i;
if (Convert.ToString(fillData.Tables[0].Rows[i]["性能等级"]).IsNullOrEmptyOrWhiteSpace() && Convert.ToInt32(fillData.Tables[0].Rows[i]["性能等级"]) <= 0)
{
this.View.ShowWarnningMessage("此编号没有性能等级！");
return;
}
this.Model.CreateNewEntryRow("F_SZXY_XYDCJXXBEntity");
this.View.GetControl <EntryGrid>("F_SZXY_XYDCJXXBEntity").SetEnterMoveNextColumnCell(true);
//this.View.UpdateView("F_SZXY_XYDCJXXBEntity");
IViewService viewService = ServiceHelper.GetService <IViewService>();
//物料编码
value = Convert.ToString(fillData.Tables[0].Rows[i]["产品型号"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
string RMat = Utils.GetRootMatId(value, orgobj["Id"].ToString(), Context);
DynamicObject F_SZXY_Material = this.Model.GetValue("F_SZXY_Material", mm) as DynamicObject;
Utils.SetBaseDataValue(viewService, entry1[mm], (BaseDataField)this.View.BusinessInfo.GetField("F_SZXY_Material"), Convert.ToInt64(RMat), ref F_SZXY_Material, Context);
}
if (this.Model.GetValue("F_SZXY_operator") is DynamicObject opDo)
{
value = Convert.ToString(opDo["Id"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_operator1", value, mm);
}
this.View.UpdateView("F_SZXY_operator1", mm);
}
this.Model.SetValue("F_SZXY_IsCheck", "true", mm);
if (NoType == "FQJY")
{
//this.Model.SetValue("F_SZXY_NO", Convert.ToString(fillData.Tables[0].Rows[i]["分切编号"]), mm);
//DynamicObjectCollection entry1 = this.Model.DataObject["SZXY_XYDCJXXBEntry"] as DynamicObjectCollection;
entry1[mm]["F_SZXY_NO"] = Convert.ToString(fillData.Tables[0].Rows[i]["分切编号"]);
}
//班组
value = Convert.ToString(fillData.Tables[0].Rows[i]["班组"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_Team", value, mm);
}
value = Convert.ToString(fillData.Tables[0].Rows[i]["班次"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_Class", value, mm);
}
//工位
value = Convert.ToString(fillData.Tables[0].Rows[i]["工位"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_station", value, mm);
}
value = Convert.ToString(fillData.Tables[0].Rows[i]["特殊标志"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_SpecialMark", value, mm);
}
//性能等级
value = Convert.ToString(fillData.Tables[0].Rows[i]["性能等级"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_XNDJ", value, mm);
}
//厚度
value = Convert.ToString(fillData.Tables[0].Rows[i]["厚度"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_Ply", value, mm);
}
//宽度
value = Convert.ToString(fillData.Tables[0].Rows[i]["宽度"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_Width", value, mm);
}
//长度
value = Convert.ToString(fillData.Tables[0].Rows[i]["长度"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_Len", value, mm);
}
//面积
value = Convert.ToString(fillData.Tables[0].Rows[i]["面积"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_Area", value, mm);
}
value = Convert.ToString(fillData.Tables[0].Rows[i]["生产日期"]);
if (!value.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_Date1", value, mm);
}
DynamicObject Teamobj = this.Model.GetValue("F_SZXY_Team", mm) as DynamicObject;
//string GX = "";
string PudType = "";
if (View.Model.GetValue("F_SZXY_Material", mm) is DynamicObject Material1)
{
if (Material1["F_SZXY_Assistant"] is DynamicObject PudTypeObj)
{
PudType = Convert.ToString(PudTypeObj["Number"]);
}
}
string GX = "";
if (PudType.EqualsIgnoreCase("GF"))
{
GX = "2";
}
else if (PudType.EqualsIgnoreCase("SF"))
{
GX = "1";
}
else if (PudType.Contains("TF"))
{
GX = "3";
}
else if (PudType == "")
{
View.ShowWarnningMessage("请检查产品型号的产品类型！");
return;
}
if (NoType == "FQ")
{
if (this.Model.GetValue("F_SZXY_BoxNo") is string NewBox)
{
this.Model.SetValue("F_SZXY_BoxNoE", NewBox, mm);
}
if (mm > 0 && (mm % Convert.ToInt32(XQty)) == 0)
{
string BoxNo = GenNo(Context, this.View.BusinessInfo.GetForm().Id, orgobj, Convert.ToDateTime(this.Model.GetValue("FDate")), Teamobj, GX);
if (!BoxNo.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_BoxNo", BoxNo);
this.Model.SetValue("F_SZXY_BoxNoE", BoxNo, mm);//
}
}
}
//初始化生成箱号
if (NoType == "FQJY")
{
if (mm == 0)
{
KBNO = GenNo(Context, this.View.BusinessInfo.GetForm().Id, orgobj, Convert.ToDateTime(this.Model.GetValue("FDate")), Teamobj, GX);
if (!KBNO.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_BoxNo", KBNO.ToString()); //
this.Model.SetValue("F_SZXY_BoxNoE", KBNO.ToString(), mm); //
}
}
else
{
this.Model.SetValue("F_SZXY_BoxNoE", KBNO, mm);//
}
}
if (erow == 0 && mm == 0 && NoType == "FQ")
{
string KBNO = GenNo(Context, this.View.BusinessInfo.GetForm().Id, orgobj, Convert.ToDateTime(this.Model.GetValue("FDate")), Teamobj, GX);
if (!KBNO.IsNullOrEmptyOrWhiteSpace())
{
this.Model.SetValue("F_SZXY_BoxNo", KBNO);
this.Model.SetValue("F_SZXY_BoxNoE", KBNO, erow);
}
}
this.Model.DataObject["FFormId"] = this.View.BusinessInfo.GetForm().Id;
Utils.Save(View, new DynamicObject[] { this.Model.DataObject }, Kingdee.BOS.Orm.OperateOption.Create(), Context);
this.View.UpdateView("F_SZXY_XYDCJXXBEntity");
this.View.SetEntityFocusRow("F_SZXY_XYDCJXXBEntity", mm + 1);
}
}
else
{
this.View.ShowWarnningMessage("没有匹配到数据！"); return;
}
}
public override void EndOperationTransaction(BOS.Core.DynamicForm.PlugIn.Args.EndOperationTransactionArgs e)
{
lotIds.Clear();
List <SqlObject> sqlList = new List <SqlObject>();
foreach (var item in e.DataEntitys)
{
string formId = "";
if (item.DynamicObjectType.Name.Equals("InvInit"))
{
formId = "STK_InvInit";
}
else
{
formId = item["FFormId"].ToString();
}
DynamicObjectCollection details = null;
switch (formId)
{
case "STK_MISCELLANEOUS":
details = item["STK_MISCELLANEOUSENTRY"] as DynamicObjectCollection;
break;
case "STK_InStock":
details = item["InStockEntry"] as DynamicObjectCollection;
break;
case "SP_InStock":
details = item["Entity"] as DynamicObjectCollection;
break;
case "STK_OEMInStock":
details = item["OEMInStockEntry"] as DynamicObjectCollection;
break;
case "STK_InitInStock":
details = item["InitInStockEntry"] as DynamicObjectCollection;
break;
case "STK_InvInit":
details = item["InvInitDetail"] as DynamicObjectCollection;
break;
default: break;
}
if (details == null)
{
return;
}
foreach (var entryItem in details)
{
Object lotObj = entryItem["LOT"];
if (null != lotObj && Convert.ToInt16((lotObj as DynamicObject)["BizType"]) == 1)
{
sqlList.Add(this.getSqlParams(formId, entryItem, item));
}
}
}
if (sqlList.Count > 0 && lotIds.Count > 0)
{
DBUtils.ExecuteBatch(this.Context, sqlList);
string filter = string.Format("FLOTID IN ({0})", string.Join(",", lotIds));
OQLFilter of = OQLFilter.CreateHeadEntityFilter(filter);
DynamicObject[] lotMasters = BusinessDataServiceHelper.Load(this.Context, "BD_BatchMainFile", null, of);
ISaveService service = Kingdee.BOS.App.ServiceHelper.GetService <ISaveService>();
service.Save(this.Context, lotMasters);
}
}
public override void Validate(Kingdee.BOS.Core.ExtendedDataEntity[] dataEntities, ValidateContext validateContext, Kingdee.BOS.Context ctx)
{
if (dataEntities == null)
{
return;
}
Field billNoField = validateContext.BusinessInfo.GetBillNoField();
if (dataEntities != null && dataEntities.Count() > 0)
{
List <long> lstFids = new List <long>();
foreach (var data in dataEntities)
{
lstFids.Add(Convert.ToInt64(data.DataEntity["ID"]));
}
if (lstFids.Count() <= 0)
{
return;
}
SqlParam param = new SqlParam("@FID", KDDbType.udt_inttable, lstFids.ToArray());
string sql = string.Format(@"
select a.FID,b.FEntryID,b.FSeq
from JN_T_SAL_ForecastChange a
inner join TABLE(fn_StrSplit(@FID,',',1)) tb on a.Fid=tb.Fid
inner join JN_T_SAL_ForecastChangeEntry b on a.FID=b.FID
where not exists (select 1 from JN_T_SAL_ForecastBack c where a.FJNSALEORGID=c.FSALEORGID and a.FJNSALERID=c.FSALERID
and a.FJNSaleDeptId=c.FSaleDeptId
and b.FJNMATERIALID=c.FMATERIALID and b.FJNAUXPROP=c.FAUXPROPID
and b.FJNBaseUnitID=c.FUnitID)
and a.FDirection='B'
union all
select b.FID,c.FEntryID,c.FSeq
from JN_T_SAL_ForecastBack a
inner join JN_T_SAL_ForecastChange b on a.FSALEORGID=b.FJNSALEORGID and a.FSALERID=b.FJNSALERID
and a.FSaleDeptId=b.FJNSaleDeptId
inner join TABLE(fn_StrSplit(@FID,',',1)) tb on b.Fid=tb.Fid
inner join JN_T_SAL_ForecastChangeEntry c on b.FID=c.FID and a.FMATERIALID=c.FJNMATERIALID
and a.FAUXPROPID=c.FJNAUXPROP and c.FJNBaseUnitID=a.FUnitID
where b.FDirection='B' and a.FQTY-c.FJNBaseUnitQty<0");
DynamicObjectCollection docForecast = DBUtils.ExecuteDynamicObject(this.Context, sql, null, null, CommandType.Text, new SqlParam[] { param });
if (docForecast == null || docForecast.Count() <= 0)
{
return;
}
foreach (var data in dataEntities)
{
List <DynamicObject> docSelect = docForecast.Where(p => Convert.ToInt64(data.DataEntity["ID"]) == Convert.ToInt64(p["FID"])).ToList();
if (docSelect == null || docSelect.Count() <= 0)
{
continue;
}
foreach (var item in docSelect)
{
AddMsg(validateContext, data, billNoField.Key
, string.Format(@"第{0}行审核之后结余数小于0,不能审核!", item["FSeq"]));
}
}
}
}
//public IOperationResult CreateNewBillFromInNoticeEntry(Context ctx, PutDetailLinkInDetailDto.InDetailEntryLinkInNotice data)
//{
// return this.CreateNewBillsFromInNoticeEntities(ctx, new PutDetailLinkInDetailDto.InDetailEntryLinkInNotice[] { data });
//}//end method
/// <summary>
/// 关联发货明细批量新增拣货明细。
/// </summary>
/// <param name="ctx">上下文对象。</param>
/// <param name="dataArray">拣货明细关联发货通知数据实体数组。</param>
/// <returns>返回新建保存事务结果。</returns>
public IOperationResult CreateNewBillsFromInNoticeEntities(Context ctx, IEnumerable <PickDetailLinkInDetailDto.PickDetailEntryLinkInNotice> dataArray)
{
//取默认转换规则。
var rule = ConvertServiceHelper.GetConvertRules(ctx, "BAH_WMS_OutNotice", "BAH_WMS_Outbound")
.Where(element => element.IsDefault)
.FirstOrDefault();
if (rule == null)
{
throw new KDBusinessException("RuleNotFound", "未找到发货通知至发货明细之间，启用的转换规则，无法自动下推！");
}
ListSelectedRowCollection listSelectedRowCollection = new ListSelectedRowCollection();
foreach (var data in dataArray)
{
var row = new ListSelectedRow(data.SourceBillId.ToString(), data.SourceEntryId.ToString(), 0, rule.SourceFormId)
{
EntryEntityKey = "FEntity"
};
listSelectedRowCollection.Add(row);
}//end foreach
//将需要传入的数量作为参数传递进下推操作，并执行下推操作。
PushArgs args = new PushArgs(rule, listSelectedRowCollection.ToArray());
var inDetailDataObjects = ConvertServiceHelper.Push(ctx, args)
.Adaptive(result => result.ThrowWhenUnSuccess(op => op.GetResultMessage()))
.Adaptive(result => result.TargetDataEntities.Select(entity => entity.DataEntity).ToArray());
//修改明细行数据包。
var inDetailMetadata = FormMetaDataCache.GetCachedFormMetaData(ctx, rule.TargetFormId);
var inDetailBillView = inDetailMetadata.CreateBillView(ctx);
var inDetailDynamicFormView = inDetailBillView as IDynamicFormViewService;
var inDetailBusinessInfo = inDetailMetadata.BusinessInfo;
var inDetailEntryEntity = inDetailBusinessInfo.GetEntity("FEntity");
var inDetailEntryLink = inDetailBusinessInfo.GetForm().LinkSet.LinkEntitys.FirstOrDefault();
foreach (var data in inDetailDataObjects)
{
//新建并加载已有数据包。
inDetailBillView.AddNew(data);
//逐行检索，并关联复制行。
var entryCollection = inDetailBillView.Model.GetEntityDataObject(inDetailEntryEntity);
var entryMirrorArray = entryCollection.ToArray();
foreach (var entry in entryMirrorArray)
{
var sources = dataArray.Join(entry.Property <DynamicObjectCollection>(inDetailEntryLink.Key),
left => new { SourceEntryId = left.SourceEntryId, SourceBillId = left.SourceBillId },
right => new { SourceEntryId = right.Property <long>("SId"), SourceBillId = right.Property <long>("SBillId") },
(left, right) => left).ToArray();
for (int i = 0; i < sources.Count(); i++)
{
var entryIndex = entryCollection.IndexOf(entry);
var rowIndex = entryIndex + i;
if (i > 0)
{
inDetailBillView.Model.CopyEntryRow("FEntity", entryIndex, rowIndex, true);
}//end if
DynamicObject FMaterialId = inDetailBillView.Model.GetValue("FMaterialId", rowIndex) as DynamicObject;
DynamicObjectCollection WarehouseSub = FMaterialId["WarehouseSub"] as DynamicObjectCollection;
string EnableCapacity = WarehouseSub.FirstOrDefault()["EnableCapacity"].ToString();
var item = sources.ElementAt(i);
inDetailDynamicFormView.SetItemValueByID("FPHMXWgt", item.PHMXWgt, rowIndex);
inDetailDynamicFormView.SetItemValueByID("FLocId", item.FromLocId, rowIndex);
if (EnableCapacity == "False")
{
inDetailDynamicFormView.SetItemValueByID("FUnitId", item.ToUnitId, rowIndex);
inDetailDynamicFormView.UpdateValue("FQty", rowIndex, item.ToQty);
}
else
{
if (item.ToAvgCty == 0)
{
inDetailDynamicFormView.UpdateValue("FCty", rowIndex, item.ToCty);
}
else
{
inDetailDynamicFormView.UpdateValue("FAvgCty", rowIndex, item.ToAvgCty);
inDetailDynamicFormView.SetItemValueByID("FUnitId", item.ToUnitId, rowIndex);
inDetailDynamicFormView.UpdateValue("FQty", rowIndex, item.ToQty);
}
}
//inDetailDynamicFormView.SetItemValueByID("FToUnitId", item.ToUnitId, rowIndex);
//inDetailDynamicFormView.UpdateValue("FToQty", rowIndex, item.ToQty);
//inDetailDynamicFormView.SetItemValueByID("FStockId", item.StockId, rowIndex);
//inDetailDynamicFormView.SetItemValueByID("FStockPlaceId", item.StockPlaceId, rowIndex);
//inDetailDynamicFormView.UpdateValue("FProduceDate", rowIndex, item.KFDate);
//inDetailDynamicFormView.UpdateValue("FExpPeriod", rowIndex, item.ExpPeriod);
//inDetailDynamicFormView.UpdateValue("FSerialNo", rowIndex, item.SerialNo);
//inDetailDynamicFormView.UpdateValue("FTrayNo", rowIndex, item.TrayNo);
//inDetailDynamicFormView.UpdateValue("FEntryRemark", rowIndex, item.Remark);
} //end for
} //end foreach
} //end foreach
//调用上传操作，将暂存、保存、提交、审核操作放置在同一事务中执行。
return(inDetailDataObjects.DoNothing(ctx, inDetailBusinessInfo, "Upload"));
}//end method
DateTime date = new DateTime();//元年
public void Run(Context ctx, Schedule schedule)
{
//读取系统公共参数
//服务参数
//组织内码:如果参数与组织无关，设置为0
//long orgId = ctx.CurrentOrganizationInfo.ID;
long orgId = 100041;
//会计账簿内码：如果参数与账簿无关，设置为0
long acctBookId = 0;
//系统参数对象标识
string parameterObjId = "PEJK_OppAutoClose"; //商机自动关闭期限
//选项字段的绑定实体属性
string parameterCloseDueTime = "F_PEJK_CloseDueTime"; //自动关闭期限
string parameterActivityWarning = "F_PEJK_ActivityWarning"; //活动期限预警
//读取系统参数：自动关闭期限返回值可能为null
var CloseDueTime = SystemParameterServiceHelper.GetParamter(
ctx,
orgId,
acctBookId,
parameterObjId,
parameterCloseDueTime
);
//读取系统参数：预警期限返回值可能为null
var ActivityWarningTime = SystemParameterServiceHelper.GetParamter(
ctx,
orgId,
acctBookId,
parameterObjId,
parameterActivityWarning
);
double CloseTime = 0;
double WarningTime = 0;
if (CloseDueTime != null)
{
CloseTime = Convert.ToDouble(CloseDueTime);
}
if (ActivityWarningTime != null)
{
WarningTime = Convert.ToDouble(ActivityWarningTime);
}
//判断商机状态为执行中的商机，最大的活动时间和当前时间的间隔是否大于CloseTime，如果大于自动关闭商机，如果商机一条活动也没有通过审核时间和CloseTime进行对比。
string strSql = string.Format("/*dialect*/select opp.FID,opp.F_PEJK_AuditDate,max(act.FACTSTARTTIME) as FACTSTARTTIME from T_CRM_Opportunity opp left join T_CRM_Activity act on opp.FID = act.FOPPID where opp.FDOCUMENTSTATUS = 'G' and opp.FCLOSESTATUS = 0 group by opp.FID,opp.F_PEJK_AuditDate");
DynamicObjectCollection dbcol = DBUtils.ExecuteDynamicObject(ctx, strSql);
if (dbcol != null && dbcol.Count() > 0)
{
List <object> ids = new List <object>();
foreach (DynamicObject item in dbcol)
{
DateTime AuditDate = Convert.ToDateTime(item["F_PEJK_AuditDate"]);
DateTime ACTSTARTTIME = Convert.ToDateTime(item["FACTSTARTTIME"]);
if (ACTSTARTTIME == date)
{
string strSQL = string.Format(@"/*dialect*/update T_CRM_Opportunity set F_PEJK_WarningDate = '{0}' where FID = {1}", AuditDate.AddDays(WarningTime), Convert.ToInt64(item["FID"]));
DBUtils.Execute(ctx, strSQL);
TimeSpan span = DateTime.Now.Subtract(AuditDate);
if (span.Days < 0)
{
continue;
}
if (span.Days + 1 > CloseTime)
{
ids.Add(Convert.ToInt64(item["FID"]));
}
}
else
{
string strSQL = string.Format(@"/*dialect*/update T_CRM_Opportunity set F_PEJK_WarningDate = '{0}' where FID = {1}", ACTSTARTTIME.AddDays(WarningTime), Convert.ToInt64(item["FID"]));
DBUtils.Execute(ctx, strSQL);
TimeSpan span = DateTime.Now.Subtract(ACTSTARTTIME);
int days = span.Days + 1;
if (span.Days < 0)
{
continue;
}
if (span.Days + 1 > CloseTime)
{
ids.Add(Convert.ToInt64(item["FID"]));
}
}
}
if (ids != null && ids.Count() > 0)
{
object[] pkValues = ids.ToArray();
KEEPER.K3.CRM.CRMServiceHelper.CRMServiceHelper.setState(ctx, "T_CRM_Opportunity", "FCLOSESTATUS", "1", "FID", pkValues);
}
}
}
//执行查询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.AppendFormat(@"/*dialect*/ SELECT DEPTNAME, SALERNAME, QUOTA, CONTRACTNUM, COMPLETEAMOUNT 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_QSNC_SALER) AS QUOTA,");
//sql1.AppendLine(" F_QSNC_CONTRACTNUM AS CONTRACTNUM,");
//sql1.AppendLine(" F_QSNC_DISTAMOUNT AS COMPLETEAMOUNT");
//sql1.AppendLine(" FROM QSNC_SaleResolve_Saler RESOLVESALER");
//sql1.AppendLine(" LEFT JOIN QSNC_SaleResolve_Basic RESOLVEBASIC ON RESOLVEBASIC.FID = RESOLVESALER.FID");
//sql1.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FID = RESOLVESALER.F_QSNC_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 ");
// 百分号 CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar) + ' %'
//报表sql
StringBuilder sql1 = new StringBuilder();
sql1.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY DEPTL.FNAME) AS FSeq, DEPTL.FNAME AS DEPTNAME, EMPL.FNAME AS SALERNAME, FBILLNO, CONVERT(FLOAT, ROUND(F_PEJK_SUMAMOUNT, 2)) AS SUMAMOUNT, F_PEJK_PRONO, CONVERT(FLOAT, ROUND(F_PEJK_FPAmount, 2)) AS FPAMOUNT, CAST(CONVERT(FLOAT, ROUND(F_PEJK_SumRatio * 100, 3)) as varchar) + ' %' AS RATIO INTO {0} ", tableName);
sql1.AppendLine(" FROM PEJK_PRODETAIL DETAIL ");
sql1.AppendLine(" LEFT JOIN T_CRM_CONTRACT CONTRACT ON DETAIL.FID = CONTRACT.FID ");
sql1.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FID = CONTRACT.FSALERID ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT_L DEPTL ON DEPTL.FDEPTID = CONTRACT.FSALEDEPTID ");
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 CONTRACT.FDATE >= '{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 CONTRACT.FDATE <= '{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);
}
DBUtils.ExecuteDynamicObject(this.Context, sql1.ToString());
// tmpTable1 : DEPTNAME SALERNAME QUOTA COMPLETEAMOUNT COMPLETERATE
//StringBuilder sql2 = new StringBuilder();
//sql2.AppendFormat(@"/*dialect*/INSERT INTO {0} SELECT '合计' , '', TOTALQUOTA, '', TOTALAMOUNT ", tmpTable1);
//sql2.AppendLine(" FROM ");
//sql2.AppendFormat(" (SELECT SUM(QUOTA) AS TOTALQUOTA, SUM(COMPLETEAMOUNT) AS TOTALAMOUNT 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, CONTRACTNUM, CONVERT(FLOAT, ROUND(COMPLETEAMOUNT, 2)) AS TOTALAMOUNT, CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar) + ' %' AS COMPLETERATE INTO {0} ", tableName);
//sql3.AppendFormat(" FROM {0} ", tmpTable1);
//DBUtils.ExecuteDynamicObject(this.Context, sql3.ToString());
}
public override void AfterBarItemClick(AfterBarItemClickEventArgs e)
{
base.AfterBarItemClick(e);
if (e.BarItemKey.EqualsIgnoreCase("SZXY_tbGridPrintNo"))
{
this.Model.DataObject["FFormId"] = this.View.BusinessInfo.GetForm().Id;
Utils.Save(View, new DynamicObject[] { this.Model.DataObject }, Kingdee.BOS.Orm.OperateOption.Create(), Context);
#region
if (this.Model.GetValue("F_SZXY_OrgId") is DynamicObject OrgObj)
{
long orgid = Convert.ToInt64(OrgObj["Id"]);
//是否指定标签模板
string PJSQL = " ";
//DynamicObject PrintTemp = this.Model.GetValue("F_SZXY_LabelPrint") as DynamicObject;
//if (PrintTemp != null)
//{
// string PId = Convert.ToString(PrintTemp["Id"]);
// if (!PId.IsNullOrEmptyOrWhiteSpace())
// {
// PJSQL = $" and T1.Fid={PId} ";
// }
//}
string MacInfo = Utils.GetMacAddress();
Logger.Debug("当前MAC地址", MacInfo);
string F_SZXY_CustId = "";
string material = "";
string CustName = "";
DynamicObjectCollection entry1 = this.Model.DataObject["SZXY_CPCHBGDEntry"] as DynamicObjectCollection;
if (entry1 != null)
{
StringBuilder STR = new StringBuilder();
DataSet PrintModelDS = null;
int ckb = 0;
foreach (var item in entry1.Where(m => !Convert.ToString(m["F_SZXY_Barcode"]).IsNullOrEmptyOrWhiteSpace()))
{
string BarNo = Convert.ToString(item["F_SZXY_Barcode"]);
if (item["F_SZXY_CustId"] is DynamicObject cust)
{
F_SZXY_CustId = Convert.ToString(cust["Id"]);
CustName = Convert.ToString(cust["Name"]);
}
if (item["F_SZXY_Material"] is DynamicObject Mat)
{
material = Convert.ToString(Mat["Name"]);
}
if (!MacInfo.IsNullOrEmptyOrWhiteSpace())
{
Logger.Debug("调用匹配模板前客户为", CustName);
PrintModelDS = XYStraddleCut.getPrintModel(this.View, Context, PJSQL, orgid.ToString(), MacInfo, F_SZXY_CustId, material, BarNo, "BarNo", ref ckb);
if (PrintModelDS != null)
{
XYStraddleCut.Print(PrintModelDS, ckb, Context, this.View, $"'{BarNo}'", "BarNo");
}
}
}
}
#endregion
}
}
}
public string APPtest(string parameter)
{
string value = HttpContext.Current.Request.Form["Data"];
JObject jObject = (JObject)JsonConvert.DeserializeObject(value);
string DBID = jObject["DBID"].ToString();
string UserName = jObject["UserName"].ToString();
string PassWord = jObject["PassWord"].ToString();
//过滤条件：起始日期/截至日期/部门/业务员
string PersonId = jObject["PersonId"].ToString();
string SalesId = jObject["SalesId"].ToString();
string DeptId = jObject["DeptId"].ToString();
string startDate = jObject["startDate"].ToString();
string endDate = jObject["endDate"].ToString();
JObject jsonRoot = new JObject();
JArray Arr = new JArray();
JObject basedata = new JObject();
KEEPER.K3.App.CRMService crm = new KEEPER.K3.App.CRMService();
string sContent = "";
Context ctx = getContext(UserName, PassWord, 2052, DBID, "http://localhost/K3Cloud/");
ApiClient client = new ApiClient("http://localhost/K3Cloud/");
bool bLogin = client.Login(DBID, UserName, PassWord, 2052);
if (bLogin)//登录成功
{
string salerLimit = "";
Boolean flag = false;
//获取当前用户权限内的销售数据
if (crm.getSalerPersonids(ctx, Convert.ToInt64(PersonId)) != null)
{
//获取当前用户可见的销售员集合
List <long> salerList = crm.getSalerPersonids(ctx, Convert.ToInt64(PersonId));
if (salerList != null)
{
var a = from Id in salerList select Id;
string ids = string.Join(",", a.ToArray());
salerLimit = string.Format(@"and SALESMAN.FID in ({0})", ids);
}
}
//报表sql
StringBuilder sql1 = new StringBuilder();
sql1.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY DEPTL.FNAME) AS FSeq, DEPTL.FNAME AS DEPTNAME, EMPL.FNAME AS SALERNAME, FBILLNO, CONVERT(FLOAT, ROUND(F_PEJK_SUMAMOUNT, 2)) AS SUMAMOUNT, F_PEJK_PRONO, CONVERT(FLOAT, ROUND(F_PEJK_FPAmount, 2)) AS FPAMOUNT, CAST(CONVERT(FLOAT, ROUND(F_PEJK_SumRatio * 100, 3)) as varchar) + ' %' AS RATIO ");
sql1.AppendLine(" FROM PEJK_PRODETAIL DETAIL ");
sql1.AppendLine(" LEFT JOIN T_CRM_CONTRACT CONTRACT ON DETAIL.FID = CONTRACT.FID ");
sql1.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FID = CONTRACT.FSALERID ");
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 (flag)
{
sql1.AppendLine(salerLimit);
}
if (!SalesId.Equals("") && SalesId != null)
{
sql1.AppendLine(" and STAFF.fstaffid in (").Append(SalesId).Append(") ");
}
if (!DeptId.Equals("") && DeptId != null)
{
sql1.AppendLine(" and AND DEPT.fdeptid in (").Append(DeptId).Append(") ");
}
if (!startDate.Equals("") && startDate != null)
{
sql1.AppendFormat(" AND CONTRACT.FDATE >= '{0}' ", startDate);
}
if (!endDate.Equals("") && endDate != null)
{
sql1.AppendFormat(" AND CONTRACT.FDATE <= '{0}' ", endDate);
}
DynamicObjectCollection periodColl = DBUtils.ExecuteDynamicObject(ctx, sql1.ToString());
foreach (DynamicObject item in periodColl)
{
basedata = new JObject();
basedata.Add("FSeq", Convert.ToInt64(item["FSeq"]));
basedata.Add("DEPTNAME", Convert.ToInt64(item["DEPTNAME"]));
basedata.Add("SALERNAME", Convert.ToInt64(item["SALERNAME"]));
basedata.Add("FBILLNO", Convert.ToInt64(item["FBILLNO"]));
basedata.Add("SUMAMOUNT", Convert.ToInt64(item["SUMAMOUNT"]));
basedata.Add("F_PEJK_PRONO", Convert.ToInt64(item["F_PEJK_PRONO"]));
basedata.Add("FPAMOUNT", Convert.ToInt64(item["FPAMOUNT"]));
basedata.Add("RATIO", Convert.ToInt64(item["RATIO"]));
jsonRoot.Add("Result", basedata);
}
}
sContent = JsonConvert.SerializeObject(jsonRoot);
return(sContent);
}
public override void EndOperationTransaction(EndOperationTransactionArgs e)
{
base.EndOperationTransaction(e);
if (e.DataEntitys != null && e.DataEntitys.Count() > 0)
{
foreach (DynamicObject DataEntity in e.DataEntitys)
{
//佣金合计大于0并且客户类型=有区域门店，业务类型 = 有区域门店订货。审核时需要自动生成费用申请单并且自动生成生产公司给区域的其他应付单
if (Convert.ToDouble(DataEntity["FCommissionSum"]) > 0 && Convert.ToString(((DynamicObject)DataEntity["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.YQYMDNO) && Convert.ToInt64(((DynamicObject)DataEntity["FYWTYPE"])["Id"]) == ConstantBaseData.YQYMDDHID)
{
belongCustNumbaer = Convert.ToString(((DynamicObject)DataEntity["FBelongCust"])["Number"]);
brandNumber = Convert.ToString(((DynamicObject)DataEntity["FSHBRAND"])["Number"]);
regionNumber = Convert.ToString(((DynamicObject)DataEntity["FSHREGION"])["Number"]);
ywTypeNumber = Convert.ToString(((DynamicObject)DataEntity["FYWTYPE"])["Number"]);
YJSum = Convert.ToDouble(DataEntity["FCommissionSum"]);
DynamicObjectCollection DataEntityFIn = DataEntity["SAL_OUTSTOCKFIN"] as DynamicObjectCollection;
Amount = Convert.ToDouble(DataEntityFIn[0]["BillAmount"]);
long BillID = Convert.ToInt64(DataEntity["Id"]);
BillNo = Convert.ToString(DataEntity["BillNo"]);
Action <IDynamicFormViewService> fillBillPropertys = new Action <IDynamicFormViewService>(fillPropertys);
DynamicObject billModel = XSXServiceHelper.XSXServiceHelper.CreateBillMode(this.Context, "ER_ExpenseRequest", fillBillPropertys);
IOperationResult saveResult = XSXServiceHelper.XSXServiceHelper.Save(this.Context, "ER_ExpenseRequest", billModel);
XSXServiceHelper.XSXServiceHelper.Log(this.Context, "Save", saveResult);
if (!saveResult.IsSuccess)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("自动创建费用申请单失败原因：");
foreach (var operateResult in saveResult.OperateResult)
{
sb.AppendLine(operateResult.Message);
}
throw new KDBusinessException("AutoOperate", sb.ToString());
}
if (saveResult.IsSuccess)
{
//反写"已生成费用申请单"，并反写单据编号
string costRequestNum = saveResult.OperateResult[0].Number;
string updateSql = string.Format(@"/*dialect*/Update T_SAL_OUTSTOCK set FISCREATEAPPLY = 1,FCOSTAPPLYNO = '{0}' where FID = {1}", costRequestNum, BillID);
DBUtils.Execute(this.Context, updateSql);
}
//生成生产公司给区域的其他应付单
Action <IDynamicFormViewService> fillAPBillPropertys = new Action <IDynamicFormViewService>(fillAPPropertys);
DynamicObject apModel = XSXServiceHelper.XSXServiceHelper.CreateBillMode(this.Context, "AP_OtherPayable", fillAPBillPropertys);
IOperationResult apSaveResult = XSXServiceHelper.XSXServiceHelper.Save(this.Context, "AP_OtherPayable", apModel);
XSXServiceHelper.XSXServiceHelper.Log(this.Context, "Save", apSaveResult);
if (!apSaveResult.IsSuccess)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("自动创建生产公司-区域其他应付单失败原因：");
foreach (var operateResult in apSaveResult.OperateResult)
{
sb.AppendLine(operateResult.Message);
}
throw new KDBusinessException("AP_OtherPayable", sb.ToString());
}
if (apSaveResult.IsSuccess)
{
//反写"已生成其他应付单"，并反写单据编号
string OtherApBilltNum = apSaveResult.OperateResult[0].Number;
string updateSql = string.Format(@"/*dialect*/Update T_SAL_OUTSTOCK set FISCREATEOTHERAP = 1,FOTHERAPNo = '{0}' where FID = {1}", OtherApBilltNum, BillID);
DBUtils.Execute(this.Context, updateSql);
}
}
//额外提点合计大于0并且客户类型=门店，审核时需要自动生成费用申请单
if (Convert.ToDouble(DataEntity["FBONUSSUM"]) > 0 && Convert.ToString(((DynamicObject)DataEntity["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.YQYMDNO) && Convert.ToInt64(((DynamicObject)DataEntity["FYWTYPE"])["Id"]) == ConstantBaseData.YQYMDDHID)
{
belongCustNumbaer = (belongCustNumbaer != null && !belongCustNumbaer.Equals(" ")) ? belongCustNumbaer: Convert.ToString(((DynamicObject)DataEntity["FBelongCust"])["Number"]);
brandNumber = (brandNumber != null && !brandNumber.Equals(" ")) ? brandNumber : Convert.ToString(((DynamicObject)DataEntity["FSHBRAND"])["Number"]);
regionNumber = (regionNumber != null && !regionNumber.Equals(" ")) ? regionNumber : Convert.ToString(((DynamicObject)DataEntity["FSHREGION"])["Number"]);
ywTypeNumber = (ywTypeNumber != null && !ywTypeNumber.Equals(" ")) ? ywTypeNumber : Convert.ToString(((DynamicObject)DataEntity["FYWTYPE"])["Number"]);
//belongCustNumbaer = Convert.ToString(((DynamicObject)DataEntity["FBelongCust"])["Number"]);
BONUS = Convert.ToDouble(DataEntity["FBONUSSUM"]);//佣金提点
//DynamicObjectCollection DataEntityFIn = DataEntity["SAL_OUTSTOCKFIN"] as DynamicObjectCollection;
//Amount = Convert.ToDouble(DataEntityFIn[0]["BillAmount"]);
long BillID = Convert.ToInt64(DataEntity["Id"]);
BillNo = Convert.ToString(DataEntity["BillNo"]);
Action <IDynamicFormViewService> fillTDBillPropertys = new Action <IDynamicFormViewService>(fillTDPropertys);
DynamicObject billModel = XSXServiceHelper.XSXServiceHelper.CreateBillMode(this.Context, "ER_ExpenseRequest", fillTDBillPropertys);
IOperationResult saveResult = XSXServiceHelper.XSXServiceHelper.Save(this.Context, "ER_ExpenseRequest", billModel);
XSXServiceHelper.XSXServiceHelper.Log(this.Context, "Save", saveResult);
if (!saveResult.IsSuccess)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("自动创建费用申请单失败原因：");
foreach (var operateResult in saveResult.OperateResult)
{
sb.AppendLine(operateResult.Message);
}
throw new KDBusinessException("AutoOperate", sb.ToString());
}
if (saveResult.IsSuccess)
{
//反写"已生成费用申请单"，并反写单据编号
string costRequestNum = saveResult.OperateResult[0].Number;
string updateSql = string.Format(@"/*dialect*/Update T_SAL_OUTSTOCK set FISCREATEBOUNS = 1,FBOUNSAPPLYBILLNO = '{0}' where FID = {1}", costRequestNum, BillID);
DBUtils.Execute(this.Context, updateSql);
}
}
//管销运费合计大于0并且客户类型=门店，审核时需要自动生成费用申请单
if (Convert.ToDouble(DataEntity["FLOGISTICAMOUNT"]) > 0 && Convert.ToString(((DynamicObject)DataEntity["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.YQYMDNO) && Convert.ToInt64(((DynamicObject)DataEntity["FYWTYPE"])["Id"]) == ConstantBaseData.YQYMDDHID)
{
belongCustNumbaer = (belongCustNumbaer != null && !belongCustNumbaer.Equals(" ")) ? belongCustNumbaer : Convert.ToString(((DynamicObject)DataEntity["FBelongCust"])["Number"]);
brandNumber = (brandNumber != null && !brandNumber.Equals(" ")) ? brandNumber : Convert.ToString(((DynamicObject)DataEntity["FSHBRAND"])["Number"]);
regionNumber = (regionNumber != null && !regionNumber.Equals(" ")) ? regionNumber : Convert.ToString(((DynamicObject)DataEntity["FSHREGION"])["Number"]);
ywTypeNumber = (ywTypeNumber != null && !ywTypeNumber.Equals(" ")) ? ywTypeNumber : Convert.ToString(((DynamicObject)DataEntity["FYWTYPE"])["Number"]);
//belongCustNumbaer = Convert.ToString(((DynamicObject)DataEntity["FBelongCust"])["Number"]);
LOGISTICA = Convert.ToDouble(DataEntity["FLOGISTICAMOUNT"]);//管销费用
//DynamicObjectCollection DataEntityFIn = DataEntity["SAL_OUTSTOCKFIN"] as DynamicObjectCollection;
//Amount = Convert.ToDouble(DataEntityFIn[0]["BillAmount"]);
long BillID = Convert.ToInt64(DataEntity["Id"]);
BillNo = Convert.ToString(DataEntity["BillNo"]);
Action <IDynamicFormViewService> fillGXBillPropertys = new Action <IDynamicFormViewService>(fillGXPropertys);
DynamicObject billModel = XSXServiceHelper.XSXServiceHelper.CreateBillMode(this.Context, "ER_ExpenseRequest", fillGXBillPropertys);
IOperationResult saveResult = XSXServiceHelper.XSXServiceHelper.Save(this.Context, "ER_ExpenseRequest", billModel);
XSXServiceHelper.XSXServiceHelper.Log(this.Context, "Save", saveResult);
if (!saveResult.IsSuccess)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("自动创建费用申请单失败原因：");
foreach (var operateResult in saveResult.OperateResult)
{
sb.AppendLine(operateResult.Message);
}
throw new KDBusinessException("AutoOperate", sb.ToString());
}
if (saveResult.IsSuccess)
{
//反写"已生成费用申请单"，并反写单据编号
string costRequestNum = saveResult.OperateResult[0].Number;
string updateSql = string.Format(@"/*dialect*/Update T_SAL_OUTSTOCK set FISCREATELOGISTICS = 1,FLOGISTICSAPPLYBILLNO = '{0}' where FID = {1}", costRequestNum, BillID);
DBUtils.Execute(this.Context, updateSql);
}
}
}
}
}
/// <summary>
/// 输入物料fID 返回绑定的包装信息
/// </summary>
/// <returns>返回服务结果。</returns>
public ServiceResult ExecuteService(string materialFid)
{
var result = new ServiceResult <List <JSONObject> >();
var ctx = this.KDContext.Session.AppContext;
// 检查上下文对象
if (this.IsContextExpired(result))
{
return(result);
}
// 检查传入参数
if (string.IsNullOrWhiteSpace(materialFid))
{
result.Code = (int)ResultCode.Fail;
result.Message = "物料主键不能为空！";
return(result);
}
//获取相关信息
try
{
string sqlSelect = string.Format(@"/*dialect*/
SELECT t2.FID, t2.FNUMBER, t3.FNAME
FROM dbo.BAH_T_BD_MATERIAL t
LEFT JOIN BAH_T_BD_MATPACKAGE t1 ON t.FID = t1.FID
LEFT JOIN BAH_T_BD_PACKAGE t2 ON t1.FPACKAGEID = t2.FID
LEFT JOIN BAH_T_BD_PACKAGE_L t3 ON t2.FID = t3.FID
WHERE t.FDOCUMENTSTATUS = 'C'
AND t.FFORBIDSTATUS = 'A'
AND t2.FDOCUMENTSTATUS = 'C'
AND t2.FFORBIDSTATUS = 'A'
AND t.FID = '{0}'
order by t2.FNUMBER
;", materialFid);// or a.num is null
DynamicObjectCollection query_result = DBUtils.ExecuteDynamicObject(ctx, sqlSelect, null, null);
if (query_result.Count == 0)
{
result.Code = (int)ResultCode.Fail;
result.Message = "未检索到对应信息！";
}
else
{
result.Code = (int)ResultCode.Success;
JSONObject Finaldata = new JSONObject();
List <JSONObject> return_data = new List <JSONObject>();
//构建JSONObject数据
foreach (DynamicObject data_obj in query_result)
{
JSONObject data = new JSONObject();
data.Add("FID", data_obj["FID"]);
data.Add("FNUMBER", data_obj["FNUMBER"]);
data.Add("FNAME", data_obj["FNAME"]);
return_data.Add(data);
}
Finaldata.Add("PackName", return_data);
//返回数据
result.Code = (int)ResultCode.Success;
result.Data = return_data;
result.Message = "成功返回数据！";
}
}
catch (Exception ex)
{
result.Code = (int)ResultCode.Fail;
result.Message = ex.Message;
}
return(result);
}
/// <summary>
/// 主单据体的字段携带完毕，与源单的关联关系创建好之后，触发此事件
/// </summary>
/// <param name="e"></param>
public override void OnAfterCreateLink(CreateLinkEventArgs e)
{
// 预先获取一些必要的元数据，后续代码要用到：
// 源单第二单据体
Entity srcSecondEntity = e.SourceBusinessInfo.GetEntity("F_PAEZ_OtherEntity");
// 目标单第一单据体
Entity destMainEntity = e.TargetBusinessInfo.GetEntity("FEntity");
// 目标单第二单据体
Entity destSecondEntity = e.TargetBusinessInfo.GetEntity("F_PAEZ_OtherEntity");
// 目标单关联子单据体
Entity linkEntity = null;
Form form = e.TargetBusinessInfo.GetForm();
if (form.LinkSet != null &&
form.LinkSet.LinkEntitys != null &&
form.LinkSet.LinkEntitys.Count != 0)
{
linkEntity = e.TargetBusinessInfo.GetEntity(
form.LinkSet.LinkEntitys[0].Key);
}
if (linkEntity == null)
{
return;
}
// 获取生成的全部下游单据
ExtendedDataEntity[] billDataEntitys = e.TargetExtendedDataEntities.FindByEntityKey("FBillHead");
// 对下游单据，逐张单据进行处理
foreach (var item in billDataEntitys)
{
DynamicObject destBillObject = item.DataEntity;
// 定义一个集合，用于收集本单对应的源单内码
HashSet <long> srcBillIds = new HashSet <long>();
// 开始到主单据体中，读取关联的源单内码
DynamicObjectCollection destMainEntryRows =
destMainEntity.DynamicProperty.GetValue(destBillObject) as DynamicObjectCollection;
foreach (var mainEntityRow in destMainEntryRows)
{
DynamicObjectCollection linkRows =
linkEntity.DynamicProperty.GetValue(mainEntityRow) as DynamicObjectCollection;
foreach (var linkRow in linkRows)
{
long srcBillId = Convert.ToInt64(linkRow["SBillId"]);
if (srcBillId != 0 && srcBillIds.Contains(srcBillId) == false)
{
srcBillIds.Add(srcBillId);
}
}
}
if (srcBillIds.Count == 0)
{
continue;
}
// 开始加载源单第二单据体上的字段
// 确定需要加载的源单字段（仅加载需要携带的字段）
List <SelectorItemInfo> selector = new List <SelectorItemInfo>();
selector.Add(new SelectorItemInfo("F_PAEZ_Desc"));
// TODO: 继续添加其他需要携带的字段，示例代码略
// 设置过滤条件
string filter = string.Format(" {0} IN ({1}) ",
e.SourceBusinessInfo.GetForm().PkFieldName,
string.Join(",", srcBillIds));
OQLFilter filterObj = OQLFilter.CreateHeadEntityFilter(filter);
// 读取源单
IViewService viewService = ServiceHelper.GetService <IViewService>();
var srcBillObjs = viewService.Load(this.Context,
e.SourceBusinessInfo.GetForm().Id,
selector,
filterObj);
// 开始把源单单据体数据，填写到目标单上
DynamicObjectCollection destSecondEntryRows =
destSecondEntity.DynamicProperty.GetValue(destBillObject) as DynamicObjectCollection;
destSecondEntryRows.Clear(); // 删除空行
foreach (var srcBillObj in srcBillObjs)
{
DynamicObjectCollection srcEntryRows =
srcSecondEntity.DynamicProperty.GetValue(srcBillObj) as DynamicObjectCollection;
foreach (var srcEntryRow in srcEntryRows)
{
// 目标单添加新行，并接受源单字段值
DynamicObject newRow = new DynamicObject(destSecondEntity.DynamicObjectType);
destSecondEntryRows.Add(newRow);
// 填写字段值
newRow["F_PAEZ_Desc"] = srcEntryRow["F_PAEZ_Desc"];
// TODO: 逐个填写其他字段值，示例代码略
}
}
}
}
public override void OnGetSourceData(GetSourceDataEventArgs e)
{
base.OnGetSourceData(e);
DynamicObjectCollection a = e.SourceData as DynamicObjectCollection;
}
public override void EndOperationTransaction(EndOperationTransactionArgs e)
{
base.EndOperationTransaction(e);
// 条码拆装单保存时进行多维度单位换算
if (this.FormOperation.Operation.Equals("Save"))
{
if (e.DataEntitys != null && e.DataEntitys.Count <DynamicObject>() > 0)
{
foreach (DynamicObject item in e.DataEntitys)
{
// 获取条码拆装箱明细
DynamicObjectCollection barCodeEntry = item["UN_PackagingEntry"] as DynamicObjectCollection;
if (barCodeEntry != null && barCodeEntry.Count > 0)
{
double totalCount = barCodeEntry.Count;
// 遍历当前条码拆装单明细行
foreach (DynamicObject obj1 in barCodeEntry)
{
// 获取当前明细行的内码
long entryId = Convert.ToInt64(obj1["Id"]);
// 赋值托数
StringBuilder tmpSQL4 = new StringBuilder();
tmpSQL4.AppendFormat(@"/*dialect*/ UPDATE t_UN_PackagingEntry SET F_SCFG_TUONUM = {0} WHERE FENTRYID = {1} ", (1 / totalCount), entryId);
DBUtils.Execute(this.Context, tmpSQL4.ToString());
// 获取当前明细行物料的条形码，并根据条形码查询条码主档获取该物料的公斤数量
String barCode = Convert.ToString(obj1["FEntryBarCode"]);
StringBuilder tmpSQL1 = new StringBuilder();
tmpSQL1.AppendFormat(@"/*dialect*/ SELECT * FROM T_BD_BARCODEMAIN WHERE FBARCODE = '{0}' ", barCode);
DynamicObjectCollection col1 = DBUtils.ExecuteDynamicObject(this.Context, tmpSQL1.ToString());
if (col1 != null && col1.Count > 0)
{
// 获取公斤数量
double realWeight = Convert.ToDouble(col1[0]["FQTY"]);
// 获得物料编码
DynamicObject materialObj = col1[0]["FMATERIALID"] as DynamicObject;
if (materialObj != null)
{
String materialId = Convert.ToString(materialObj["Id"]);
StringBuilder tmpSQL2 = new StringBuilder();
tmpSQL2.AppendFormat(@"/*dialect*/ SELECT * FROM T_BD_UNITCONVERTRATE UC LEFT JOIN T_BD_UNIT_L UL ON UL.FUNITID = UC.FCURRENTUNITID WHERE FMATERIALID = '{0}' ", materialId);
DynamicObjectCollection col2 = DBUtils.ExecuteDynamicObject(this.Context, tmpSQL2.ToString());
if (col2 != null && col2.Count > 0)
{
// 遍历当前物料的标准称重单位（公斤）与其他称重单位的转换参数
foreach (DynamicObject obj2 in col2)
{
// 目标称重单位数量
double rate1 = Convert.ToDouble(obj2["FCONVERTDENOMINATOR"]);
// 公斤称重单位数量
double rate2 = Convert.ToDouble(obj2["FCONVERTNUMERATOR"]);
// 计算公斤数量转换为各个称重单位的数值
double realOtherWeight = (realWeight / rate2) * rate1;
StringBuilder tmpSQL3 = new StringBuilder();
String where = "";
switch (Convert.ToString(obj2["FNAME"]))
{
case "平方米":
where = "F_SCFG_M2NUM";
break;
case "张":
where = "F_SCFG_ZHANGNUM";
break;
case "个":
where = "F_SCFG_GENUM";
break;
case "箱":
where = "F_SCFG_XIANGNUM";
break;
case "件":
where = "F_SCFG_JIANNUM";
break;
default:
break;
}
if (!String.IsNullOrWhiteSpace(where))
{
tmpSQL3.AppendFormat(@"/*dialect*/ UPDATE t_UN_PackagingEntry SET {0} = {1} WHERE FENTRYID = {2} ", where, realOtherWeight, entryId);
DBUtils.Execute(this.Context, tmpSQL3.ToString());
}
}
}
}
}
}
}
}
}
}
}
/// <summary>
/// 获取销售订单
/// </summary>
/// <param name="coll"></param>
/// <returns></returns>
private List <K3SalOrderInfo> GetK3SalOrderInfo(DynamicObjectCollection coll)
{
K3SalOrderInfo order = null;
List <K3SalOrderInfo> orders = null;
if (coll != null && coll.Count > 0)
{
orders = new List <K3SalOrderInfo>();
foreach (var obj in coll)
{
if (obj != null)
{
if (SQLUtils.GetFieldValue(obj, "FBillNo").StartsWith("SO"))
{
order = new K3SalOrderInfo();
order.SrcNo = SQLUtils.GetFieldValue(obj, "FBillNo");
order.FBillNo = SQLUtils.GetFieldValue(obj, "FBillNo"); //订单号
order.F_HS_OriginOnlineOrderNo = SQLUtils.GetFieldValue(obj, "F_HS_OriginOnlineOrderNo"); //原线下订单单号
order.FDate = Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "FDate")); //订单日期
order.PurseDate = TimeHelper.GetTimeStamp(order.FDate);
order.FCustId = SQLUtils.GetFieldValue(obj, "FCUSTID"); //客户编码
order.F_HS_B2CCustId = SQLUtils.GetFieldValue(obj, "F_HS_B2CCustId"); //客户真是编码
order.FSalerId = SQLUtils.GetSellerNo(this.K3CloudContext, obj, "FSalerId"); //销售员编码
order.OrderSource = SQLUtils.GetFieldValue(obj, "F_HS_SaleOrderSource"); //订单来源
order.FSettleCurrId = SQLUtils.GetFieldValue(obj, "FSettleCurrId"); //结算币别
order.F_HS_RateToUSA = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_RateToUSA")); //汇率
order.F_HS_RecipientCountry = SQLUtils.GetFieldValue(obj, "F_HS_RecipientCountry"); //国家
order.F_HS_DeliveryProvinces = SQLUtils.GetFieldValue(obj, "F_HS_DeliveryProvinces"); //省份/州
order.F_HS_DeliveryCity = SQLUtils.GetFieldValue(obj, "F_HS_DeliveryCity"); //城市
order.F_HS_DeliveryAddress = SQLUtils.GetFieldValue(obj, "F_HS_DeliveryAddress"); //具体地址
order.F_HS_PostCode = SQLUtils.GetFieldValue(obj, "F_HS_PostCode"); //邮编
order.F_HS_DeliveryName = SQLUtils.GetFieldValue(obj, "F_HS_DeliveryName"); //收货人姓名
order.F_HS_BillAddress = SQLUtils.GetFieldValue(obj, "F_HS_BillAddress"); //账单地址
order.F_HS_MobilePhone = SQLUtils.GetFieldValue(obj, "F_HS_MobilePhone"); //收货人联系电话
order.F_HS_ShippingMethod = SQLUtils.GetFieldValue(obj, "F_HS_ShippingMethod"); //发货方式
order.F_HS_PaymentModeNew = SQLUtils.GetFieldValue(obj, "F_HS_PaymentModeNew"); //付款方式
order.F_HS_PaymentStatus = SQLUtils.GetFieldValue(obj, "F_HS_PaymentStatus"); //付款状态
order.F_HS_Shipping = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_Shipping")); //运费
order.FCustLevel = SQLUtils.GetFieldValue(obj, "F_HS_FGroup"); //分组
order.FNote = SQLUtils.GetFieldValue(obj, "FNOTE"); //备注
order.F_HS_Channel = SQLUtils.GetFieldValue(obj, "F_HS_OnlineOrderWay"); //订单渠道
order.F_HS_CouponAmount = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_CouponAmount")); //优惠金额
order.F_HS_IntegralDeduction = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_IntegralDeduction")); //积分金额
order.F_HS_DiscountedAmount = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "F_HS_DiscountedAmount")); //已优惠金额
order.FApproveDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "FApproveDate"))); //订单审核时间
order.F_HS_CollectionTime = Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "F_HS_CollectionTime")); //CEO特批已到款时间
order.PayedTime = TimeHelper.GetTimeStamp(order.F_HS_CollectionTime); //CEO特批已到款时间(时间戳)
K3SalOrderEntryInfo entry = new K3SalOrderEntryInfo();
entry.FMaterialId = SQLUtils.GetFieldValue(obj, "FMaterialId"); //物料编码
entry.FTAXPRICE = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "FTAXPRICE")); //单价
entry.FTAXAmt = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "FAmount")); //商品总价
entry.FQTY = Convert.ToDecimal(SQLUtils.GetFieldValue(obj, "FQTY")); //数量
entry.FStockId = SQLUtils.GetFieldValue(obj, "F_HS_StockID"); //仓库地理编码
entry.F_HS_IsVirtualEntry = SQLUtils.GetFieldValue(obj, "F_HS_IsVirtualEntry").CompareTo("1") == 1 ? true : false; //是否虚拟
entry.F_HS_IsEmptyStock = SQLUtils.GetFieldValue(obj, "F_HS_IsEmptyStock").CompareTo("1") == 1 ? true : false; //是否清仓
entry.FIsFree = SQLUtils.GetFieldValue(obj, "FIsFree").CompareTo("1") == 1 ? true : false; //是否为赠品
order.OrderEntry.Add(entry);
orders.Add(order);
}
}
}
}
return(orders);
}
//准备报表的表头信息
public override ReportTitles GetReportTitles(IRptParams filter)
{
var result = base.GetReportTitles(filter);
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
if (dyFilter != null)
{
if (result == null)
{
result = new ReportTitles();
}
//反写过滤条件
//起始日期
if (dyFilter["F_QSNC_StartDateFilter"] == null)
{
result.AddTitle("F_QSNC_StartDate", DateTime.Now.ToShortDateString());
}
else
{
result.AddTitle("F_QSNC_StartDate", Convert.ToString(dyFilter["F_QSNC_StartDateFilter"]));
}
//截止日期
if (dyFilter["F_QSNC_EndDateFilter"] == null)
{
result.AddTitle("F_QSNC_EndDate", DateTime.Now.ToShortDateString());
}
else
{
result.AddTitle("F_QSNC_EndDate", Convert.ToString(dyFilter["F_QSNC_EndDateFilter"]));
}
//部门
if (dyFilter["F_QSNC_DepartmentFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"]).Count > 0)
{
StringBuilder deptName = new StringBuilder();
DynamicObjectCollection cols = (DynamicObjectCollection)dyFilter["F_QSNC_DepartmentFilter"];
foreach (DynamicObject dept in cols)
{
String tmpName = Convert.ToString(((DynamicObject)dept["F_QSNC_DepartmentFilter"])["Name"]);
deptName.Append(tmpName + "; ");
}
result.AddTitle("F_QSNC_Department", deptName.ToString());
}
else
{
result.AddTitle("F_QSNC_Department", "全部");
}
//业务员
if (dyFilter["F_QSNC_SalesmanFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"]).Count > 0)
{
StringBuilder salerName = new StringBuilder();
DynamicObjectCollection cols = (DynamicObjectCollection)dyFilter["F_QSNC_SalesmanFilter"];
foreach (DynamicObject saler in cols)
{
String tmpName = Convert.ToString(((DynamicObject)saler["F_QSNC_SalesmanFilter"])["Name"]);
salerName.Append(tmpName + "; ");
}
result.AddTitle("F_QSNC_Salesman", salerName.ToString());
}
else
{
result.AddTitle("F_QSNC_Salesman", "全部");
}
}
result.AddTitle("F_QSNC_BillDate", DateTime.Now.ToShortDateString());//报表日期
return(result);
}
public override void BeforeExecuteOperationTransaction(BeforeExecuteOperationTransaction e)
{
base.BeforeExecuteOperationTransaction(e);
HashSet <string> poBillNos = new HashSet <string>();
Entity entity = this.BusinessInfo.GetEntity("FInStockEntry");
Entity linkEntry = this.BusinessInfo.GetEntity("FInStockEntry_Link");
Field fldSrcFormId = this.BusinessInfo.GetField("FSRCBILLTYPEID");
Field fldSrcBillNo = this.BusinessInfo.GetField("FSrcBillNo");
// 对单据体进行循环，取关联的源单编号
foreach (var billObj in e.SelectedRows)
{
DynamicObjectCollection entryRows = entity.DynamicProperty.GetValue(billObj.DataEntity)
as DynamicObjectCollection;
foreach (var entryRow in entryRows)
{
string srcFormId = fldSrcFormId.DynamicProperty.GetValue <string>(entryRow);
string srcSrcBillNo = fldSrcBillNo.DynamicProperty.GetValue <string>(entryRow);
if (string.IsNullOrWhiteSpace(srcFormId) || !srcFormId.EqualsIgnoreCase(POFormId))
{// 源单不是采购订单，略过
continue;
}
// 源单编号已经登记，不再重复记录，略过
if (poBillNos.Contains(srcSrcBillNo))
{
continue;
}
// Link已经记录了源单信息，略过
DynamicObjectCollection linkRows = linkEntry.DynamicProperty.GetValue(entryRow)
as DynamicObjectCollection;
if (linkRows.Count > 0)
{
continue;
}
poBillNos.Add(srcSrcBillNo);
}
}
if (poBillNos.Count == 0)
{
return;
}
DynamicObject[] poObjs = this.LoadPurchaseOrder(poBillNos);
if (poObjs == null || poObjs.Length == 0)
{
return;
}
Dictionary <string, Dictionary <string, DynamicObject> > dctAllBills = this.BuildDictionary(poObjs);
string srcTableNumber = this.GetPOEntryTableNumber();
List <DynamicObject> allNewLinkRows = new List <DynamicObject>();
// 循环单据体，为单据体，建立起源单关联信息：
foreach (var billObj in e.SelectedRows)
{
DynamicObjectCollection entryRows = entity.DynamicProperty.GetValue(billObj.DataEntity)
as DynamicObjectCollection;
foreach (var entryRow in entryRows)
{
string srcFormId = fldSrcFormId.DynamicProperty.GetValue <string>(entryRow);
string srcSrcBillNo = fldSrcBillNo.DynamicProperty.GetValue <string>(entryRow);
if (string.IsNullOrWhiteSpace(srcFormId) || !srcFormId.EqualsIgnoreCase(POFormId))
{// 源单不是采购订单，略过
continue;
}
Dictionary <string, DynamicObject> dctOneBill = null;
if (dctAllBills.TryGetValue(srcSrcBillNo, out dctOneBill) == false)
{
continue;
}
DynamicObject materialObj = entryRow["MaterialId"] as DynamicObject;
if (materialObj == null)
{
continue;
}
string materialNumber = Convert.ToString(materialObj["number"]);
DynamicObject srcRow = null;
if (dctOneBill.TryGetValue(materialNumber, out srcRow) == false)
{
continue;
}
// Link已经记录了源单信息，略过
DynamicObjectCollection linkRows = linkEntry.DynamicProperty.GetValue(entryRow) as DynamicObjectCollection;
if (linkRows.Count > 0)
{
continue;
}
DynamicObject linkRow = new DynamicObject(linkEntry.DynamicObjectType);
linkRow["STableName"] = srcTableNumber;
this.FillLinkRow(srcRow, entryRow, linkRow);
linkRows.Add(linkRow);
allNewLinkRows.Add(linkRow);
}
}
// 为新建的源单关联信息，设置内码
IDBService dbService = ServiceHelper.GetService <IDBService>();
dbService.AutoSetPrimaryKey(this.Context, allNewLinkRows.ToArray(), linkEntry.DynamicObjectType);
}
//向临时表插入报表数据
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 AfterConvert(AfterConvertEventArgs e)
{
base.AfterConvert(e);
IViewService Services = ServiceHelper.GetService <IViewService>();
//获取整个下推的数据
var dataObjs = e.Result.FindByEntityKey("FBillHead");
if (dataObjs != null)
{
foreach (var extendedDataEntity in dataObjs)
{
DynamicObjectCollection EntryCollect = null;//
//获取目标单的明细信息
Entity entity = e.TargetBusinessInfo.GetEntity("FEntity");
DynamicObject BillObject = extendedDataEntity.DataEntity;
EntryCollect = extendedDataEntity.DataEntity["Entity"] as DynamicObjectCollection;//生产领料单
if (EntryCollect != null && EntryCollect.Count > 0)
{
foreach (DynamicObject Row in EntryCollect)
{
string MoId = Convert.ToString(Row["MoId"]); //生产订单内码
int MoSeq = Convert.ToInt32(Row["MoEntrySeq"]); //生产订单行号
string FMATERIALID = Convert.ToString(Row["MaterialId_Id"]); //原料
//加载生产订单
if (MoId != "0")
{
DynamicObject MoObejct = Utils.LoadFIDBillObject(this.Context, "PRD_MO", MoId);
long MoEntryId = 0;//生产订单分录内码
if (MoObejct["TreeEntity"] is DynamicObjectCollection MoEntry)
{
MoEntryId = Convert.ToInt64(MoEntry[MoSeq - 1]["Id"]);
}
if (MoObejct != null && !Convert.ToString(Row["MoEntrySeq"]).IsNullOrEmptyOrWhiteSpace() && !FMATERIALID.IsNullOrEmptyOrWhiteSpace())
{
string MoBillNo = Convert.ToString(MoObejct["BillNo"]);//生产订单编号
string SelUseSql = "/*dialect*/select T1.FBILLNO,T1.FID,T2.FENTRYID,T2.FSEQ from T_PRD_PPBOM T1 left join T_PRD_PPBOMENTRY T2 on t1.FID=T2.FID " +
$"where T1.FMOBILLNO = '{MoBillNo}' and T1.FMOENTRYSEQ = '{MoSeq}' and T2.FMATERIALID = '{FMATERIALID}'";
DataSet Ds = Utils.CBData(SelUseSql, Context);
if (Ds != null && Ds.Tables.Count > 0 && Ds.Tables[0].Rows.Count > 0)
{
string UseFBILLNO = Convert.ToString(Ds.Tables[0].Rows[0]["FBILLNO"]); //用料清单编号
long UseFID = Convert.ToInt64(Ds.Tables[0].Rows[0]["FID"]); //用料清单ID
long UseEntryId = Convert.ToInt64(Ds.Tables[0].Rows[0]["FENTRYID"]); //用料清单行Id
string UseSeq = Convert.ToString(Ds.Tables[0].Rows[0]["FSEQ"]); //用料清单Seq
Row["MoEntryId"] = MoEntryId;
Row["PPBomEntryId"] = UseEntryId;
//用料清单编号
Row["MoId"] = MoId;
Row["MoEntrySeq"] = MoSeq;
Row["PPBomBillNo"] = UseFBILLNO;
Row["SrcBillType"] = "PRD_PPBOM";
//源单编号
Row["SrcBillNo"] = UseFBILLNO;
//源单内码
Row["SrcInterId"] = UseFID;
//源单分录内码
Row["SrcEnteryId"] = UseEntryId;
//源单行号
Row["SrcEntrySeq"] = UseSeq;
DynamicObjectCollection linkCollect = Row["FEntity_Link"] as DynamicObjectCollection;
linkCollect[0]["RuleId"] = "PRD_PPBOM2PICKMTRL_NORMAL";//PRD_INSTOCK
linkCollect[0]["STableName"] = "T_PRD_PPBOMENTRY";
linkCollect[0]["SBillId"] = UseFID;
//生产订单分录内码
linkCollect[0]["SId"] = UseEntryId;
}
}
}
}
}
}
}
}
public override void BeforeSave(Kingdee.BOS.Core.Bill.PlugIn.Args.BeforeSaveEventArgs e)
{
// 获取销售出库单明细行
DynamicObjectCollection col1 = this.View.Model.DataObject["SAL_OUTSTOCKENTRY"] as DynamicObjectCollection;
// 遍历物料明细行
for (int i = 0; i < col1.Count; i++)
{
// 获取当前行的物料编码信息
DynamicObject materialObj = col1[i]["MaterialID"] as DynamicObject;
if (materialObj != null)
{
// 获取当前物料的内码
long materialId = Convert.ToInt64(materialObj["Id"]);
// 获取当前物料行的批号
String lotNo = Convert.ToString(col1[i]["Lot_Text"]);
// 根据物料条码查找条码主档中的数量字段得到公斤数，根据动态换算关系计算5个计量单位的数量，并赋值到各个字段上
StringBuilder tmpSQL2 = new StringBuilder();
tmpSQL2.AppendFormat(@"/*dialect*/ SELECT * FROM T_scfg_MaterialConvert MC LEFT JOIN T_BD_UNIT_L UL ON UL.FUNITID = MC.FUNITID WHERE MC.FMATERIALNUMBER = '{0}' AND F_SCFG_LOTNO = '{1}' ", materialId, lotNo);//批号 对应简单生产入库的出库
DynamicObjectCollection col2 = DBUtils.ExecuteDynamicObject(this.Context, tmpSQL2.ToString());
// 销售出库单中的物料出库数量
double realWeight = Convert.ToDouble(col1[i]["RealQty"]);
if (col2 != null && col2.Count > 0)
{
// 遍历当前物料的标准称重单位（公斤）与其他称重单位的转换参数
foreach (DynamicObject obj2 in col2)
{
// 目标称重单位数量
double rate1 = Convert.ToDouble(obj2["FQTY"]);
// 计算公斤数量转换为各个称重单位的数值
double realOtherWeight = Math.Round((double)(realWeight * rate1), 2, MidpointRounding.AwayFromZero);
StringBuilder tmpSQL3 = new StringBuilder();
String where = "";
String key = "";
switch (Convert.ToString(obj2["FNAME"]))
{
case "平方米":
where = "F_SCFG_M2NUM";
key = "M2NUM";
break;
case "张":
where = "F_SCFG_ZHANGNUM";
key = "ZHANGNUM";
break;
case "个":
where = "F_SCFG_GENUM";
key = "GENUM";
break;
case "箱":
where = "F_SCFG_MULNUM";
key = "MULNUM";
break;
default:
break;
}
if (!String.IsNullOrWhiteSpace(where) && !String.IsNullOrWhiteSpace(key))
{
this.View.Model.SetValue(where, Convert.ToDouble(realOtherWeight), i);
}
if (where.Equals("F_SCFG_MULNUM"))
{
this.View.Model.SetValue(where, Convert.ToDouble(Math.Ceiling(realOtherWeight)), i);
}
}
}
}
}
base.BeforeSave(e);
}
public ServiceResult ExecuteService(string data)
{
ServiceResult result = new ServiceResult();
String EnableCapacity;
if (data.IsNullOrEmptyOrWhiteSpace())
{
return(result);
}
var ctx = this.KDContext.Session.AppContext;
// 检查传入字符串数据
if (this.IsContextExpired(result))
{
return(result);
}
// 检查传入参数
if (string.IsNullOrWhiteSpace(data))
{
result.Code = (int)ResultCode.Fail;
result.Message = "传入表单数据不能为空！";
return(result);
}
try
{
//序列化
JavaScriptSerializer Serializer = new JavaScriptSerializer();
List <Ajust> input = Serializer.Deserialize <List <Ajust> >(data);
//
var formId = "BAH_WMS_Move";
var metadata = FormMetaDataCache.GetCachedFormMetaData(ctx, formId);
var billView = metadata.CreateBillView(ctx);
var billService = billView.AsDynamicFormViewService();
billView.AddNew();
var items = input[0];
billService.SetItemValueByID("FBatchFromWHId", items.FFROMWHID, -1);
billService.SetItemValueByID("FBatchFromOwnerId", items.FFROMOWNERID, -1);
//
billView.Model.ClearNoDataRow();
billView.Model.BatchCreateNewEntryRow("FEntity", input.Count());
for (int i = 0; i < input.Count(); i++)
{
billView.Model.SetItemValueByID("FMaterialId", input[i].FMaterialId, i);
DynamicObject FMaterialId = billView.Model.GetValue("FMaterialId", i) as DynamicObject;
if (FMaterialId == null)
{
result.Code = (int)ResultCode.Fail;
result.Message = "该物料不存在或已禁用！";
return(result);
}
else
{
DynamicObjectCollection WarehouseSub = FMaterialId["WarehouseSub"] as DynamicObjectCollection;
EnableCapacity = WarehouseSub.FirstOrDefault()["EnableCapacity"].ToString();
}
billView.Model.SetValue("FFromTrackNo", input[i].FFROMTrackNo, i);
billView.Model.SetItemValueByID("FFromLocId", input[i].FFROMLocId, i);
billView.Model.SetItemValueByID("FToLocId", input[i].FTOLocId, i);
billView.Model.SetValue("FToTrackNo", input[i].FTOTrackNo, i);
//billView.Model.SetItemValueByID("FPackageId",input[i].FPackageId, i);
billView.Model.SetItemValueByID("FFromPackageId", input[i].FFromPackageId, i);
billView.Model.SetValue("FExpPeriod", input[i].FExpPeriod, i);
billView.Model.SetValue("FExpUnit", input[i].FExpUnit, i);
if (EnableCapacity == "False")
{
billView.Model.SetValue("FFromQty", input[i].FFromQty, i);
billView.Model.SetItemValueByID("FFromUnitId", input[i].FFromUnitId, i);
}
else
{
if (input[i].FFromAvgCty > 0)
{
billView.Model.SetValue("FFromAvgCty", input[i].FFromAvgCty, i);
billView.Model.SetValue("FFromQty", input[i].FFromQty, i);
billView.Model.SetItemValueByID("FFromUnitId", input[i].FFromUnitId, i);
}
else
{
billView.Model.SetValue("FFromAvgCty", input[i].FFromAvgCty, i);
billView.Model.SetValue("FFromQty", input[i].FFromQty, i);
billView.Model.SetValue("FFromCty", input[i].FFromCty, i);
}
}
billView.Model.SetValue("FLotNo", input[i].FLotNo, i);
if (input[i].FProduceDate != null)
{
billView.Model.SetValue("FProduceDate", input[i].FProduceDate, i);
}
}
billView.Model.ClearNoDataRow();
var op = billView.Model.DataObject.DoNothing(ctx, billView.BillBusinessInfo, "Upload");
result.Code = op.IsSuccess ? (int)ResultCode.Success : (int)ResultCode.Fail;
result.Message = op.GetResultMessage();
}
catch (Exception ex)
{
result.Code = (int)ResultCode.Fail;
result.Message = ex.Message;
Logger.Error(this.GetType().AssemblyQualifiedName, ex.Message, ex);
}
return(result);
}
/// <summary>
/// 目标单单据构建完毕，且已经创建好与源单的关联关系之后，触发此事件
/// </summary>
/// <param name="e"></param>
/// <remarks>
/// 本事件的时机，刚好能够符合需求，
/// 而AfterConvert事件，则在执行表单服务策略之后
/// </remarks>
public override void OnAfterCreateLink(CreateLinkEventArgs e)
{
// 目标单单据体元数据
Entity entity = e.TargetBusinessInfo.GetEntity("FPAYBILLSRCENTRY");
Entity es = e.SourceBusinessInfo.GetEntity("FEntity");
// 读取已经生成的付款单
ExtendedDataEntity[] bills = e.TargetExtendedDataEntities.FindByEntityKey("FBillHead");
// 定义一个集合，存储新拆分出来的单据体行
List <ExtendedDataEntity> newRows = new List <ExtendedDataEntity>();
// 对目标单据进行循环
foreach (var bill in bills)
{
// 取单据体集合
DynamicObjectCollection rowObjs = entity.DynamicProperty.GetValue(bill.DataEntity)
as DynamicObjectCollection;
// 对单据体进行循环：从后往前循环，新拆分的行，避开循环
int rowCount = rowObjs.Count;
int newRowCount = 1;
double EXCHANGERATE = Convert.ToDouble(bill["EXCHANGERATE"]);
for (int i = rowCount - 1; i >= 0; i--)
{
DynamicObject rowObj = rowObjs[i];
DynamicObject SRCCOSTID = rowObj["SRCCOSTID"] as DynamicObject;
string SRCCOSTIDname = Convert.ToString(SRCCOSTID["Name"]);
if (SRCCOSTIDname != "路桥费")
{
double F_JNRoadAmount = Convert.ToDouble(rowObj["F_JNRoadAmount"]);
double PLANPAYAMOUNT = Convert.ToDouble(rowObj["PLANPAYAMOUNT"]);
double AFTTAXTOTALAMOUNT = Convert.ToDouble(rowObj["AFTTAXTOTALAMOUNT"]);
double F_JNTAXAmount = Convert.ToDouble(rowObj["F_JNTAXAmount"]);
DynamicObject newRowObj = null;
rowObj["PLANPAYAMOUNT"] = PLANPAYAMOUNT - F_JNRoadAmount - F_JNTAXAmount;
rowObj["AFTTAXTOTALAMOUNT"] = AFTTAXTOTALAMOUNT - F_JNRoadAmount - F_JNTAXAmount;
rowObj["REALPAYAMOUNT"] = rowObj["PLANPAYAMOUNT"];
rowObj["F_JNRoadAmount"] = 0;
rowObj["FPLANPAYAMOUNTLOC"] = Convert.ToDouble(rowObj["PLANPAYAMOUNT"]) * EXCHANGERATE;
rowObj["FREALPAYAMOUNTLOC"] = Convert.ToDouble(rowObj["REALPAYAMOUNT"]) * EXCHANGERATE;
rowObj["F_JNSRCNoTaxAmount"] = Convert.ToDouble(rowObj["REALPAYAMOUNT"]);
// 根据规则进行拆分：
// 示例代码略过拆分规则，强制对每行进行拆分
// 通过复制的方式，产生新行：确保字段值、关联关系与原行一致
if (F_JNRoadAmount > 0 && SRCCOSTIDname != "路桥费")
{
//var SRCCOSTID = rowObj["SRCCOSTID"] as DynamicObject;
DynamicObject SRCCOST = (DynamicObject)SRCCOSTID.Clone(true, false);
newRowObj = (DynamicObject)rowObj.Clone(false, true);
//DynamicObject newRowObj = rowObj.Clone(;
//DynamicObject newRowObj = (DynamicObject)rowObj.Clone(true, true);
newRowObj["PLANPAYAMOUNT"] = F_JNRoadAmount;
newRowObj["AFTTAXTOTALAMOUNT"] = F_JNRoadAmount;
newRowObj["F_JNRoadAmount"] = 0;
newRowObj["REALPAYAMOUNT"] = F_JNRoadAmount;
newRowObj["FPLANPAYAMOUNTLOC"] = F_JNRoadAmount * EXCHANGERATE;
newRowObj["FREALPAYAMOUNTLOC"] = F_JNRoadAmount * EXCHANGERATE;
newRowObj["F_JNSRCNoTaxAmount"] = F_JNRoadAmount;
newRowObj["F_JNTAXAmount"] = 0;
newRowObj["FTAXAMOUNT"] = 0;
newRowObj["FTAXAMOUNTLOC"] = 0;
newRowObj["F_JNSRCTAX"] = 0;
//newRowObj["Seq"] = i + 1;
QueryBuilderParemeter queryParam = new QueryBuilderParemeter();
queryParam.FormId = "BD_Expense";
queryParam.SelectItems.Add(new SelectorItemInfo("FMasterId"));
queryParam.SelectItems.Add(new SelectorItemInfo("FNumber"));
queryParam.SelectItems.Add(new SelectorItemInfo("FName"));
queryParam.FilterClauseWihtKey = string.Format(" FNumber = '{0}' ", "FYXM23");
var expense = QueryServiceHelper.GetDynamicObjectCollection(this.Context, queryParam);
newRowObj["SRCCOSTID_Id"] = expense[0]["FMasterId"];
SRCCOST["Id"] = expense[0]["FMasterId"];
SRCCOST["msterId"] = expense[0]["FMasterId"];
SRCCOST["Name"] = expense[0]["FName"];
SRCCOST["Number"] = expense[0]["FNumber"];
newRowObj["SRCCOSTID"] = SRCCOST;
// 把新行，插入到单据中，排在当前行之后
rowObjs.Insert(i + 1, newRowObj);
//newRowObj["SRCCOSTID_Id"] = 131120;
}
if (F_JNTAXAmount > 0)
{
//var SRCCOSTID = rowObj["SRCCOSTID"] as DynamicObject;
DynamicObject SRCCOST = (DynamicObject)SRCCOSTID.Clone(true, false);
newRowObj = (DynamicObject)rowObj.Clone(false, true);
//DynamicObject newRowObj = rowObj.Clone(;
//DynamicObject newRowObj = (DynamicObject)rowObj.Clone(true, true);
newRowObj["PLANPAYAMOUNT"] = F_JNTAXAmount;
newRowObj["AFTTAXTOTALAMOUNT"] = F_JNTAXAmount;
newRowObj["F_JNRoadAmount"] = 0;
newRowObj["REALPAYAMOUNT"] = F_JNTAXAmount;
newRowObj["FPLANPAYAMOUNTLOC"] = F_JNTAXAmount * EXCHANGERATE;
newRowObj["FREALPAYAMOUNTLOC"] = F_JNTAXAmount * EXCHANGERATE;
//newRowObj["F_JNSRCNoTaxAmount"] = F_JNTAXAmount;
newRowObj["F_JNSRCNoTaxAmount"] = 0;
//newRowObj["Seq"] = i + 1;
QueryBuilderParemeter queryParam = new QueryBuilderParemeter();
queryParam.FormId = "BD_Expense";
queryParam.SelectItems.Add(new SelectorItemInfo("FMasterId"));
queryParam.SelectItems.Add(new SelectorItemInfo("FNumber"));
queryParam.SelectItems.Add(new SelectorItemInfo("FName"));
queryParam.FilterClauseWihtKey = string.Format(" FNumber = '{0}' ", "FYXM98");
var expense = QueryServiceHelper.GetDynamicObjectCollection(this.Context, queryParam);
newRowObj["SRCCOSTID_Id"] = expense[0]["FMasterId"];
SRCCOST["Id"] = expense[0]["FMasterId"];
SRCCOST["msterId"] = expense[0]["FMasterId"];
SRCCOST["Name"] = expense[0]["FName"];
SRCCOST["Number"] = expense[0]["FNumber"];
newRowObj["SRCCOSTID"] = SRCCOST;
// 把新行，插入到单据中，排在当前行之后
rowObjs.Insert(i + 1, newRowObj);
//newRowObj["SRCCOSTID_Id"] = 131120;
}
if (newRowObj != null)
{
// 为新行创建一个ExtendedDataEntity对象，表单服务策略需要此对象
ExtendedDataEntity newRow = new ExtendedDataEntity(
newRowObj, bill.DataEntityIndex, rowCount + newRowCount);
newRows.Add(newRow);
newRowCount++;
}
}
}
}
// 把新拆分出来的单据体行，加入到下推结果中
// 特别说明：如果去掉此语句，新拆分的行，不会执行表单服务策略
e.TargetExtendedDataEntities.AddExtendedDataEntities("FEntity", newRows.ToArray());
}
public override void Validate(ExtendedDataEntity[] dataEntities, ValidateContext validateContext, Context ctx)
{
if (dataEntities.IsNullOrEmpty() || dataEntities.Length == 0)
{
return;
}
foreach (ExtendedDataEntity item in dataEntities)
{
DynamicObject requestDynamic = item.DataEntity;
//有区域门店订货校验起订量
if ((Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.YQYMDNO) && Convert.ToInt64(((DynamicObject)requestDynamic["FYWTYPE"])["id"]) == ConstantBaseData.YQYMDDHID))
{
double TotalAmount = Convert.ToDouble(requestDynamic["TotalAmount"]);
if (TotalAmount < 1000)
{
string msg = string.Format("单据：{0},订货金额:{1}小于门店起订金额1000", requestDynamic["BillNo"], TotalAmount);
var errInfo = new ValidationErrorInfo(
item.BillNo,
item.DataEntity["Id"].ToString(),
item.DataEntityIndex,
item.RowIndex,
"Valid019",
msg,
" ",
Kingdee.BOS.Core.Validation.ErrorLevel.Error);
validateContext.AddError(item.DataEntity, errInfo);
}
}
//有区域门店，订货可发量控制
if ((Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.YQYMDNO) && Convert.ToInt64(((DynamicObject)requestDynamic["FYWTYPE"])["id"]) == ConstantBaseData.YQYMDDHID))
{
long custId = Convert.ToInt64(((DynamicObject)requestDynamic["FAPPLYCUST"])["Id"]);
DynamicObjectCollection dyObjectCol = requestDynamic["FEntity"] as DynamicObjectCollection;
foreach (DynamicObject dyObject in dyObjectCol)
{
long stockOrgId = Convert.ToInt64(((DynamicObject)dyObject["FDispatchOrgIdDetail"])["Id"]);
long masterId = Convert.ToInt64(((DynamicObject)dyObject["MaterialId"])["msterID"]);
long baseUnitId = Convert.ToInt64(((DynamicObjectCollection)((DynamicObject)dyObject["MaterialId"])["MaterialBase"])[0]["BaseUnitId_Id"]);
long stockUnitId = Convert.ToInt64(((DynamicObjectCollection)((DynamicObject)dyObject["MaterialId"])["MaterialStock"])[0]["StoreUnitID_Id"]);
double kfQty = XSXServiceHelper.XSXServiceHelper.GetKFQty(this.Context, stockOrgId, masterId, custId, baseUnitId, stockUnitId);
double reqNum = Convert.ToDouble(dyObject["ReqQty"]);
if (reqNum > kfQty)
{
string msg = string.Format("单据：{0},第{1}行申请数量：{2}超出库存可发量：{3},不允许进行订货", requestDynamic["BillNo"], dyObject["Seq"], reqNum, reqNum - kfQty);
var errInfo = new ValidationErrorInfo(
item.BillNo,
item.DataEntity["Id"].ToString(),
item.DataEntityIndex,
item.RowIndex,
"Valid019",
msg,
" ",
Kingdee.BOS.Core.Validation.ErrorLevel.Error);
validateContext.AddError(item.DataEntity, errInfo);
continue;
}
}
}
//客户类别：区域：QY
if (Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.QYMDNO) && Convert.ToInt64(((DynamicObject)requestDynamic["FYWTYPE"])["id"]) == ConstantBaseData.QYDHID)
{
string strSql = string.Format(@"/*dialect*/select a.FBILLNO, b.FSEQ
from T_SCMS_ApplyGools a
inner join T_SCMS_ApplyGoolsEntry b
on a.FID = b.FID
where a.FAPPLYCUST = {0}
and b.FMRPCLOSESTATUS <> 'B'
and a.FCUSTTYPE = {1}
and a.FID <> {2} ", Convert.ToInt64(((DynamicObject)requestDynamic["FAPPLYCUST"])["Id"]), Convert.ToInt64(((DynamicObject)requestDynamic["FCUSTTYPE"])["Id"]), Convert.ToInt64(requestDynamic["Id"]));
using (IDataReader reader = DBUtils.ExecuteReader(this.Context, strSql))
{
while (reader.Read())
{
string msg = string.Format("历史单据：{0},第{1}行业务没有关闭,不允许审核", reader["FBILLNO"], reader["FSEQ"]);
var errInfo = new ValidationErrorInfo(
item.BillNo,
item.DataEntity["Id"].ToString(),
item.DataEntityIndex,
item.RowIndex,
"Valid019",
msg,
" ",
Kingdee.BOS.Core.Validation.ErrorLevel.Error);
validateContext.AddError(item.DataEntity, errInfo);
continue;
}
reader.Close();
}
}
//区域可订货余额校验
if (Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.QYMDNO) && Convert.ToInt64(((DynamicObject)requestDynamic["FYWTYPE"])["id"]) == ConstantBaseData.QYDHID)
{
double TotalAmount = Convert.ToDouble(requestDynamic["TotalAmount"]);
double QYAmount = XSXServiceHelper.XSXServiceHelper.GetQYAmount(this.Context, Convert.ToString(((DynamicObject)requestDynamic["FApplyCust"])["Number"]));
if (TotalAmount > QYAmount)
{
string msg = string.Format("单据：{0},订货金额:{1}超出可用额度:{2}", requestDynamic["BillNo"], TotalAmount, TotalAmount - QYAmount);
var errInfo = new ValidationErrorInfo(
item.BillNo,
item.DataEntity["Id"].ToString(),
item.DataEntityIndex,
item.RowIndex,
"Valid019",
msg,
" ",
Kingdee.BOS.Core.Validation.ErrorLevel.Error);
validateContext.AddError(item.DataEntity, errInfo);
}
}
//有区域门店，无区域门店可订货余额检验
if ((Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.YQYMDNO) && Convert.ToInt64(((DynamicObject)requestDynamic["FYWTYPE"])["id"]) == ConstantBaseData.YQYMDDHID) || (Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.WQYMDNO) && Convert.ToInt64(((DynamicObject)requestDynamic["FYWTYPE"])["id"]) == ConstantBaseData.WQYMDDHID))
{
double TotalAmount = Convert.ToDouble(requestDynamic["TotalAmount"]);
double MDAmount = XSXServiceHelper.XSXServiceHelper.GetMDAmount(this.Context, Convert.ToString(((DynamicObject)requestDynamic["FApplyCust"])["Number"]), Convert.ToInt64(((DynamicObject)requestDynamic["FApplyCust"])["Id"]));
if (TotalAmount > MDAmount)
{
string msg = string.Format("单据：{0},订货金额:{1}超出可用额度:{2}", requestDynamic["BillNo"], TotalAmount, TotalAmount - MDAmount);
var errInfo = new ValidationErrorInfo(
item.BillNo,
item.DataEntity["Id"].ToString(),
item.DataEntityIndex,
item.RowIndex,
"Valid019",
msg,
" ",
Kingdee.BOS.Core.Validation.ErrorLevel.Error);
validateContext.AddError(item.DataEntity, errInfo);
}
}
if (Convert.ToInt64(((DynamicObject)requestDynamic["FYWTYPE"])["id"]) == ConstantBaseData.MDYJID && (Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.YQYMDNO) || Convert.ToString(((DynamicObject)requestDynamic["FCUSTTYPE"])["Number"]).Equals(ConstantBaseData.WQYMDNO)))
{
double TotalAmount = Convert.ToDouble(requestDynamic["TotalAmount"]);
double YJAmount = XSXServiceHelper.XSXServiceHelper.GetYJAmount(this.Context, Convert.ToString(((DynamicObject)requestDynamic["FApplyCust"])["Number"]), Convert.ToInt64(((DynamicObject)requestDynamic["FApplyCust"])["Id"]));
if (TotalAmount > YJAmount)
{
string msg = string.Format("单据：{0},订货金额:{1}超出营建可用额度:{2}", requestDynamic["BillNo"], TotalAmount, TotalAmount - YJAmount);
var errInfo = new ValidationErrorInfo(
item.BillNo,
item.DataEntity["Id"].ToString(),
item.DataEntityIndex,
item.RowIndex,
"Valid019",
msg,
" ",
Kingdee.BOS.Core.Validation.ErrorLevel.Error);
validateContext.AddError(item.DataEntity, errInfo);
}
}
}
}
/// <summary>
/// 主单据体的字段携带完毕，与源单的关联关系创建好之后，触发此事件
/// </summary>
/// <param name="e"></param>
public override void OnAfterCreateLink(CreateLinkEventArgs e)
{
base.OnAfterCreateLink(e);
//预先获取一些必要的元数据，后续代码要用到
//源单第一单据体
Entity srcFirstEntity = e.SourceBusinessInfo.GetEntity("FEntity");
//目标单第一单据体
Entity mainEntity = e.TargetBusinessInfo.GetEntity("FEntityDetail");
// 目标单关联子单据体
Entity linkEntity = null;
Form form = e.TargetBusinessInfo.GetForm();
if (form.LinkSet != null &&
form.LinkSet.LinkEntitys != null &&
form.LinkSet.LinkEntitys.Count != 0)
{
linkEntity = e.TargetBusinessInfo.GetEntity(
form.LinkSet.LinkEntitys[0].Key);
}
if (linkEntity == null)
{
return;
}
// 获取生成的全部下游单据
ExtendedDataEntity[] billDataEntitys = e.TargetExtendedDataEntities.FindByEntityKey("FBillHead");
// 对下游单据，逐张单据进行处理
foreach (var item in billDataEntitys)
{
DynamicObject dataObject = item.DataEntity;
// 定义一个集合，用于收集本单对应的源单内码
HashSet <long> srcBillIds = new HashSet <long>();
// 开始到主单据体中，读取关联的源单内码
DynamicObjectCollection mainEntryRows =
mainEntity.DynamicProperty.GetValue(dataObject) as DynamicObjectCollection;
DynamicObject mainEntityRow = mainEntryRows[0];
DynamicObjectCollection linkRows =
linkEntity.DynamicProperty.GetValue(mainEntityRow) as DynamicObjectCollection;
long srcBillId = Convert.ToInt64(linkRows[0]["SBillId"]);
if (srcBillId != 0 &&
srcBillIds.Contains(srcBillId) == false)
{
srcBillIds.Add(srcBillId);
}
if (srcBillIds.Count == 0)
{
continue;
}
#region 隐藏
//foreach (var mainEntityRow in mainEntryRows)
//{
// DynamicObjectCollection linkRows =
// linkEntity.DynamicProperty.GetValue(mainEntityRow) as DynamicObjectCollection;
// foreach (var linkRow in linkRows)
// {
// long srcBillId = Convert.ToInt64(linkRow["SBillId"]);
// if (srcBillId != 0
// && srcBillIds.Contains(srcBillId) == false)
// {
// srcBillIds.Add(srcBillId);
// }
// }
//}
//if (srcBillIds.Count == 0)
//{
// continue;
//}
#endregion
// 开始加载源单第二单据体上的字段
// 确定需要加载的源单字段（仅加载需要携带的字段）
List <SelectorItemInfo> selector = new List <SelectorItemInfo>();
selector.Add(new SelectorItemInfo("FDate")); //日期
selector.Add(new SelectorItemInfo("FCustomerID")); //客户
selector.Add(new SelectorItemInfo("FSaleDeptID")); //销售部门
selector.Add(new SelectorItemInfo("FMaterialID")); //物料
selector.Add(new SelectorItemInfo("FTaxPrice")); //含税单价
// TODO: 继续添加其他需要携带的字段，示例代码略
// 设置过滤条件
string filter = string.Format(" {0} IN ({1}) ",
e.SourceBusinessInfo.GetForm().PkFieldName,
string.Join(",", srcBillIds));
OQLFilter filterObj = OQLFilter.CreateHeadEntityFilter(filter);
// 读取源单
Kingdee.BOS.Contracts.IViewService viewService = Kingdee.BOS.App.ServiceHelper.GetService <Kingdee.BOS.Contracts.IViewService>();
var srcBillObjs = viewService.Load(this.Context,
e.SourceBusinessInfo.GetForm().Id,
selector,
filterObj);
#region 隐藏
// 开始把源单单据体数据，填写到目标单上
//DynamicObjectCollection secondEntryRows =
// secondEntity.DynamicProperty.GetValue(dataObject) as DynamicObjectCollection;
//secondEntryRows.Clear(); // 删除空行
#endregion
foreach (DynamicObject srcBillObj in srcBillObjs)
{
DynamicObject dept = srcBillObj["SaleDeptID"] as DynamicObject; //销售部门
DynamicObject Cust = srcBillObj["CustomerID"] as DynamicObject; //客户
//bool isRight = !Cust["FKHBD"].Equals("1") && !Cust["FKHBD"].Equals("2") ? Convert.ToInt32(Cust["FSAP"]) == 2 : Convert.ToInt32(Cust["FKHBD"]) == 1 ? true: Convert.ToInt32(Cust["FKHBD"]) == 2 ? true:false;
//补差、丢货或者客户结算方式是现金
bool isRight = (Cust["FKHBD"].Equals("1") || Cust["FKHBD"].Equals("2")) ? true : Convert.ToInt32(Cust["FSAP"]) == 2 ? true : false;
//if ((Convert.ToInt32(Cust["FSAP"]) == 2 || Convert.ToInt32(Cust["FKHBD"]) == 1 || Convert.ToInt32(Cust["FKHBD"]) == 2) && Convert.ToInt32(dept["FDEPTTYPE"]) == 4)
if (isRight && Convert.ToInt32(dept["FDEPTTYPE"]) == 4)//部门类别外埠
{
//外埠现金客户执行销售政策折扣
Dictionary <string, double> disCounts = TLMBServiceHelper.GetDiscounts(base.Context, Convert.ToInt64(dept["id"]), Convert.ToDateTime(srcBillObj["Date"]));
//遍历应收单，通过销售折扣计算折后的含税单价赋值。
foreach (DynamicObject mainEntryRow in mainEntryRows)
{
double discount = 0.0;
if (disCounts.TryGetValue(Convert.ToString(((DynamicObject)mainEntryRow["MATERIALID"])["FWLFL"]), out discount))
{
double ZK = Convert.ToDouble(disCounts["" + Convert.ToString(((DynamicObject)mainEntryRow["MATERIALID"])["FWLFL"]) + ""]);
mainEntryRow["TaxPrice"] = Convert.ToDouble(mainEntryRow["TaxPrice"]) * ZK;
mainEntryRow["F_PAEZ_DisCount"] = ZK;
}
}
}
//DynamicObjectCollection srcEntryRows =
// srcSecondEntity.DynamicProperty.GetValue(srcBillObj) as DynamicObjectCollection;
//foreach (var srcEntryRow in srcEntryRows)
//{
// // 目标单添加新行，并接受源单字段值
// DynamicObject newRow = new DynamicObject(secondEntity.DynamicObjectType);
// secondEntryRows.Add(newRow);
// // 填写字段值
// newRow["F_JD_Text"] = srcEntryRow["F_JD_Text"];
// // TODO: 逐个填写其他字段值，示例代码略
//}
}
}
}
