public void Run(Context ctx, Schedule schedule)
{
//0、获取本次同步订单数据
DynamicObjectCollection salNoCol = getSalNo(ctx);
if (salNoCol != null && salNoCol.Count() > 0)
{
//1、同步物料
List <string> salNosList_1 = SyncMaterial(ctx, salNoCol);
if (salNosList_1 != null)
{
//2、同步BOM清单
List <string> salNosList_2 = SyncBomBill(ctx, salNosList_1);
if (salNosList_2.Count() > 0 && salNosList_2 != null)
{
//3、同步销售订单
SyncSalBill(ctx, salNosList_2);
}
}
}
else
{
string saleBIllNos = "";
IsDelBill(ctx, saleBIllNos);
}
}
/// <summary>
/// 点击按钮传递平均单价
/// </summary>
/// <param name="e"></param>
public override void AfterButtonClick(AfterButtonClickEventArgs e)
{
base.AfterButtonClick(e);
if (e.Key.ToUpperInvariant().Equals("FPASSONMEDPRICE"))
{
string strSql = string.Format(@"/*dialect*/SELECT MAT.FNUMBER,
sum(spData.FAMOUNT)/sum(spData.FACTUALQTY) avgPrice
FROM T_SP_PICKMTRL sp
INNER JOIN T_SP_PICKMTRLDATA spData
ON sp.FID = spData.FID
INNER JOIN T_BD_MATERIAL MAT
ON spData.FMATERIALID = MAT.FMATERIALID
WHERE sp.FPRDORGID = 111934
group by MAT.FNUMBER");
DynamicObjectCollection result = DBUtils.ExecuteDynamicObject(this.Context, strSql);
if (result != null && result.Count() > 0)
{
string sJson = CreateJson(result);
if (!sJson.Equals("传递参数拼接失败"))
{
WebMesTest.MaterialMessageFromCappServiceService requestMes = new WebMesTest.MaterialMessageFromCappServiceService();
requestMes.WeightingAvgNumerAsync(sJson);
//ResManager
//调用MES提供的接口
}
}
}
}
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 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 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
inner join TABLE(fn_StrSplit(@FID,',',1)) tb on b.Fid=tb.Fid
where b.FDirection='A' 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 override void AfterConvert(Kingdee.BOS.Core.Metadata.ConvertElement.PlugIn.Args.AfterConvertEventArgs e)
{
base.AfterConvert(e);
ExtendedDataEntity[] entityArray = e.Result.FindByEntityKey("FbillHead");
if (entityArray == null || entityArray.Count() <= 0)
{
return;
}
List <long> lstMaterialID = new List <long>();
foreach (ExtendedDataEntity entity in entityArray)
{
if (!lstMaterialID.Contains(Convert.ToInt64(entity.DataEntity["MaterialId_Id"])))
{
lstMaterialID.Add(Convert.ToInt64(entity.DataEntity["MaterialId_Id"]));
}
}
if (lstMaterialID.Count() <= 0)
{
return;
}
SqlParam param = new SqlParam("@FID", KDDbType.udt_inttable, lstMaterialID.ToArray());
string strSql = string.Format(@"select a.FID,a.FMaterialId from T_ENG_BOM a
INNER JOIN TABLE(fn_StrSplit(@FID,',',1)) b on a.FMaterialId=b.FID ");
DynamicObjectCollection dycBomCollections = DBUtils.ExecuteDynamicObject(this.Context, strSql, null, null, CommandType.Text, new SqlParam[] { param });
if (dycBomCollections == null || dycBomCollections.Count() <= 0)
{
return;
}
foreach (ExtendedDataEntity entity in entityArray)
{
DynamicObject dycBom = entity["BomId"] as DynamicObject;
if (dycBom == null || Convert.ToInt64(dycBom["Id"]) == 0)
{
DynamicObject dycSelect = dycBomCollections.Where(o => Convert.ToInt64(o["FMaterialId"]) == Convert.ToInt64(entity.DataEntity["MaterialId_Id"])).FirstOrDefault();
if (dycSelect != null)
{
entity.DataEntity["BomId_Id"] = dycSelect["FID"];
}
}
Kingdee.BOS.ServiceHelper.DBServiceHelper.LoadReferenceObject(this.Context, new DynamicObject[] { entity.DataEntity }, e.TargetBusinessInfo.GetDynamicObjectType());
}
}
/// <summary>
/// 根据合同号判断是否到全货
/// </summary>
/// <param name="ctx"></param>
/// <param name="ContractNo">合同号</param>
/// <param name="orp">是否反写采购合同到全日期，默认 0,1为反写</param>
/// <returns></returns>
public Boolean isSendAllMaterial(Context ctx, string ContractNo, string orp)
{
string strSql = string.Format(@"/*dialect*/select porderid,poorderno ,sum(fstockinqty)-sum(fqty) qty
from (
select poorder.fid porderid, --采购订单单据ID
poorder.fbillno poorderno, --采购订单号
orderentry.fentryid entryid, --采购订单分录id
orderentry_r.freceiveqty, --累计收料数量
orderentry_r.fstockinqty,
orderentry.fqty
from t_PUR_POOrder poorder --采购订单
inner join t_PUR_POOrderEntry orderentry on orderentry.fid = poorder.fid--采购订单明细分录
inner join t_Pur_Poorderentry_r orderentry_r on orderentry_r.fentryid = orderentry.fentryid-- 扩展表
inner join t_Pur_Poorderentry_lk lk on lk.fentryid = orderentry.fentryid -- 关联表
inner join T_PUR_CONTRACT contract on contract.fid=lk.fsbillid --采购合同单据头
where contract.F_HTBH = '{0}'
)
group by porderid, poorderno
having sum(fstockinqty) - sum(fqty) >= 0 ", ContractNo);
// StringBuilder sql = new StringBuilder();
// sql.AppendFormat(@"select porderid,poorderno ,sum(fstockinqty)-sum(fqty)
//from (
//select poorder.fid porderid, --采购订单单据ID
//poorder.fbillno poorderno, --采购订单号
//orderentry.fentryid entryid, --采购订单分录id
//orderentry_r.freceiveqty, --累计收料数量
//orderentry_r.fstockinqty,
//orderentry.fqty
//from t_PUR_POOrder poorder --采购订单
//inner join t_PUR_POOrderEntry orderentry on orderentry.fid = poorder.fid--采购订单明细分录
//inner join t_Pur_Poorderentry_r orderentry_r on orderentry_r.fentryid = orderentry.fentryid-- 扩展表
//inner join t_Pur_Poorderentry_lk lk on lk.fentryid = orderentry.fentryid -- 关联表
//inner join T_PUR_CONTRACT contract on contract.fid=lk.fsbillid --采购合同单据头
//where contract.fbillno = '{0}' ", ContractNo );
//sql.AppendFormat(@" )
//group by porderid, poorderno
//having sum(fstockinqty) - sum(fqty) >= 0 ");
DynamicObjectCollection orderCol = DBUtils.ExecuteDynamicObject(ctx, strSql);
if (orderCol.Count() > 0)
{
return(true);
}
else
{
return(false);
}
// throw new NotImplementedException();
}
/// <summary>
/// 同步销售订单
/// </summary>
/// <param name="saleBIllNos"></param>
/// <param name="ctx"></param>
public string SyncSaleBill(Context ctx, string saleBIllNos)
{
//获取同步销售订单的数据
string strSql = string.Format(@"/*dialect*/SELECT FID, FSALBILLNO, FDATE, FORGNO, FCUSTNO, FPROJECTNO, FREMARK
FROM SALE_MAIN
WHERE FSALBILLNO in ({0})", saleBIllNos);
DynamicObjectCollection queryResult = DBUtils.ExecuteDynamicObject(ctx, strSql) as DynamicObjectCollection;
if (queryResult != null && queryResult.Count() > 0)
{
foreach (DynamicObject qResult in queryResult)
{
saveSaleBill(ctx, qResult);
}
}
return(null);
}
public override void AfterExecuteOperationTransaction(AfterExecuteOperationTransaction e)
{
base.AfterExecuteOperationTransaction(e);
if (e.DataEntitys != null && e.DataEntitys.Count <DynamicObject>() > 0)
{
foreach (DynamicObject item in e.DataEntitys)
{
long materialId = Convert.ToInt64(item["id"]);
//查询批号主档，确认该物料是否在系统中存在历史批号
//通过物料id获取masterID
string masterSql = string.Format(@"/*dialect*/SELECT FMASTERID FROM T_BD_MATERIAL WHERE FMATERIALID = {0}", materialId);
long masterId = DBUtils.ExecuteScalar <long>(this.Context, masterSql, 0, null);
if (masterId != 0)
{
//通过masterId获取所有materialIds
string materialidSql = string.Format(@"/*dialect*/SELECT FMATERIALID FROM T_BD_MATERIAL WHERE FMASTERID = {0}", masterId);
DynamicObjectCollection doc = DBUtils.ExecuteDynamicObject(this.Context, materialidSql) as DynamicObjectCollection;
string materialIds = "";
foreach (DynamicObject dt in doc)
{
materialIds += "," + Convert.ToInt64(dt["FMATERIALID"]) + "";
}
materialIds = materialIds.Substring(1);
string phSql = string.Format(@"/*dialect*/ SELECT FNUMBER FROM T_BD_LOTMASTER WHERE FMATERIALID in ({0})", materialIds);
string result = DBUtils.ExecuteScalar <string>(this.Context, phSql, "noResult", null);
DynamicObjectCollection phcol = DBUtils.ExecuteDynamicObject(this.Context, phSql) as DynamicObjectCollection;
if (phcol.Count() <= 0)
{
//更新语句
string strSql = string.Format(@"/*dialect*/UPDATE T_BD_MATERIALINVPTY SET FISAFFECTCOST=1 WHERE FINVPTYID=10004 and FISENABLE=1 and FISAFFECTCOST=0 and FMATERIALID = {0}", materialId);
DBUtils.Execute(this.Context, strSql);
}
////批号主档没有对应物料批号
//if (result.Equals("noResult"))
//{
// //更新语句
// string strSql = string.Format(@"/*dialect*/UPDATE T_BD_MATERIALINVPTY SET FISAFFECTCOST=1 WHERE FINVPTYID=10004 and FISENABLE=1 and FISAFFECTCOST=0 and FMATERIALID = {0}", materialId);
// DBUtils.Execute(this.Context, strSql);
//}
}
}
}
}
private void GetNonSychroRevSalOrder(Context ctx, DateTime begin, DateTime end)
{
DateTime firstDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
string sql = string.Format(@"/*dialect*/ select distinct FBillNo,FDate,j.FNUMBER as custNo,o.FNumber,F_HS_RateToUSA,a.F_HS_TransactionID,d.FBillAmount,q.FNUMBER as paymentMehtod
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
inner join T_BAS_ASSISTANTDATAENTRY g ON f.FentryID=g.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 T_BD_CUSTOMER j on j.FCUSTID = a.F_HS_B2CCUSTID
inner join T_BD_CURRENCY o on o.FCURRENCYID = d.FSettleCurrId
inner join T_BAS_ASSISTANTDATAENTRY_L p ON a.F_HS_PaymentModeNew=p.FENTRYID
inner join T_BAS_ASSISTANTDATAENTRY q ON q.FentryID=p.FentryID
where a.FDOCUMENTSTATUS = 'C'and a.FsaleOrgID = 100035 and a.FCANCELSTATUS<>'B' and h.FNUMBER='XSDD01_SYS' and z.FNUMBER='100.01' and g.FNUMBER = 'HCWebProcessingOder'
and a.FCREATEDATE between '{0} 00:00:00' and getdate()
and a.fbillno not in
(select l.FRECEIVEITEM from T_AR_RECEIVEBILL k
inner join T_AR_RECEIVEBILLENTRY l on l.FID = K.FID
where k.FCREATEDATE between '{1} 00:00:00' and getdate()
and l.FRECEIVEITEM not like 'SO%' and l.FRECEIVEITEM <> '')" , firstDay, firstDay
);
DynamicObjectCollection coll = SQLUtils.GetObjects(ctx, sql);
if (coll != null && coll.Count() > 0)
{
foreach (var item in coll)
{
if (item != null)
{
}
}
}
}
public List <DateTime> getDateListByContract(Context ctx, string ContractNo, string o)
{
StringBuilder sql = new StringBuilder();
List <DateTime> list = new List <DateTime>();
sql.AppendFormat(@" ---采购入库单 --采购订单 --收料通知单
select distinct inst.fapprovedate instdate, recbill.fapprovedate recbilldate, inst.fdate, inste.FSRCBILLNO,FSRCROWID,FSRCBILLTYPEID,recbill.fbillno
from t_STK_InStock inst --采购入库单
inner join T_STK_INSTOCKENTRY inste on inst.fid=inste.fid ---采购入库单明细
inner join t_PUR_POOrderEntry orderentry on orderentry.fentryid= inste.FPOORDERENTRYID ---采购订单明细分录
inner join t_PUR_POOrder poorder on poorder.fid=orderentry.fid --采购订单单据头
inner join t_Stk_Instockentry_Lk lk on lk.fentryid=inste.fentryid ---采购入库单关联关系表
inner join T_PUR_ReceiveEntry recrentry on lk.fsid=recrentry.fentryid and recrentry.fid=lk.fsbillid --采购收料通知单 明细
inner join T_PUR_Receive recbill on recbill.fid =recrentry.fid --采购收入通知单头
where inst.fapprovedate is not null and poorder.fbillno='{0}' ,", ContractNo);
sql.AppendFormat(@" order by recbill.fapprovedate desc, inst.fapprovedate desc ");
DynamicObjectCollection orderCol = DBUtils.ExecuteDynamicObject(ctx, sql.ToString());
if (orderCol.Count() > 0)
{
if (orderCol[0]["instdate"] != null)
{
list.Add(Convert.ToDateTime(orderCol[0]["instdate"]));
}
if (orderCol[0]["recbilldate"] != null)
{
list.Add(Convert.ToDateTime(orderCol[0]["recbilldate"]));
}
}
else
{
return(null);
}
return(null);
}