public When_mapping_dynamic_object_to_type_with_different_property_types()
{
dynamicObject = new DynamicObject
{
{ "Int32Value", 1.23456789 },
};
}
/// <summary>
/// Ensures the row object.
/// </summary>
private void EnsureRowObject()
{
if (this.rowObject == null)
{
this.rowObject = (DynamicObject)Activator.CreateInstance(this.owner.ElementType);
}
}
public void TestPropertyOfMultipleLinkedObject()
{
var obj1 = new { Test = "XXX" };
var obj2 = new { Value = 15.5m };
DynamicObject viewData = new DynamicObject(obj1,obj2);
object value;
Type type;
Assert.AreEqual("XXX", viewData["Test"]);
Assert.AreEqual(15.5m, viewData["Value"]);
Assert.IsTrue(viewData.TryGetValue("Test", out value, out type));
Assert.IsInstanceOf<string>(value);
Assert.AreEqual(typeof(string), type);
Assert.AreEqual("XXX", value);
Assert.IsTrue(viewData.TryGetValue("Value", out value, out type));
Assert.IsInstanceOf<decimal>(value);
Assert.AreEqual(typeof(decimal), type);
Assert.AreEqual(15.5m, value);
Assert.IsFalse(viewData.TryGetValue("Test2", out value, out type));
Assert.IsFalse(viewData.TryGetValue("Value2", out value, out type));
}
public When_mapping_int_as_string_and_long_as_long()
{
var dynamicObjectMapper = new CustomMapper();
dynamicObjectWithInt = dynamicObjectMapper.MapObject(123);
dynamicObjectWithLong = dynamicObjectMapper.MapObject(456L);
}
public void ShouldSetTypePropertyWhenPassingTypeInfoToConstructor()
{
var typeInfo = new TypeInfo(typeof(CustomClass));
var dynamicObject = new DynamicObject(typeInfo);
dynamicObject.Type.ShouldBe(typeInfo);
}
public void TestApply()
{
var obj1 = new { Test = "XXX" };
var obj2 = new { Value = 15.5m };
DynamicObject viewData1 = new DynamicObject(obj1);
DynamicObject viewData2 = new DynamicObject(obj2);
DynamicObject viewData = new DynamicObject();
viewData.Apply(viewData1);
viewData.Apply(viewData2);
object value;
Type type;
Assert.AreEqual("XXX", viewData["Test"]);
Assert.AreEqual(15.5m, viewData["Value"]);
Assert.IsTrue(viewData.TryGetValue("Test", out value, out type));
Assert.IsInstanceOfType(value, typeof(string));
Assert.AreEqual(typeof(string), type);
Assert.AreEqual("XXX", value);
Assert.IsTrue(viewData.TryGetValue("Value", out value, out type));
Assert.IsInstanceOfType(value, typeof(decimal));
Assert.AreEqual(typeof(decimal), type);
Assert.AreEqual(15.5m, value);
Assert.IsFalse(viewData.TryGetValue("Test2", out value, out type));
Assert.IsFalse(viewData.TryGetValue("Value2", out value, out type));
}
public void ShouldAssignSelfToMixinAwareInstance()
{
IMixinAware test = mock.NewMock<IMixinAware>();
DynamicObject dynamic = new DynamicObject(new object());
Expect.Once.On(test).SetProperty("Self").To(dynamic);
dynamic.MixWith(test);
}
public When_created_based_on_object_with_indexer()
{
source = new ClassWithIndexerAndItemProperty();
source.Item = "ItemValue1";
source["K1"] = new object();
dynamicObject = new DynamicObject(source);
}
public When_converting_to_anonymous_type()
{
dynamicObject = new DynamicObject()
{
{ "Int32Value", Int32Value },
{ "DoubleValue", DoubleValue },
{ "StringValue", StringValue },
};
}
public When_created_based_on_object_with_enum_property()
{
source = new ClassWithEnum
{
EnumProperty = CustomEnum.Value2
};
dynamicObject = new DynamicObject(source);
}
public void ShouldBeAbleToAddNewMethod()
{
IntegerOperation addBody = delegate(int a, int b) { return a + b; };
DynamicObject dynamic = new DynamicObject(new object());
dynamic.AddMethod("Add", addBody);
int result = (int) dynamic.Methods["Add"](1, 1);
Assert.AreEqual(2, result);
}
public void ShouldCallTargetMethod()
{
string methodName = "TargetMethod";
MockClass mockTarget = new MockClass();
DynamicObject dynamic = new DynamicObject(mockTarget);
dynamic.Methods[methodName]();
Assert.AreEqual(1, mockTarget.CallCount, "The target method was not called!");
}
public When_converting_to_object_based_on_typeinfo()
{
var dynamicObject = new DynamicObject(typeof(CustomType))
{
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject() as CustomType;
}
public void ShouldBeAbleToMixAnotherClassInstance()
{
MockClass test = new MockClass();
DynamicObject dynamic = new DynamicObject(new object());
string methodName = "TargetMethod";
dynamic.MixWith(test);
dynamic.Methods[methodName]();
Assert.AreEqual(1, test.CallCount);
}
public When_converting_to_serializable_object_with_additional_properies()
{
var dynamicObject = new DynamicObject()
{
{ "Int32Value", Int32Value },
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject<SerializableType>();
}
public When_mapping_reducing_property_set_for_seriable_type()
{
var dynamicObjectMapper = new CustomMapper();
dynamicObject = dynamicObjectMapper.MapObject(new DataObject
{
PropertyOne = "one",
PropertyTwo = "two"
});
}
public When_converting_to_object_with_additional_properties()
{
var dynamicObject = new DynamicObject
{
{ "Int32Value", Int32Value },
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject<CustomType>();
}
public When_converting_to_different_target_type()
{
var dynamicObject = new DynamicObject(typeof(SourceType))
{
{ "StringValue", StringValue },
{ "Int32Value", Int32Value },
};
obj = dynamicObject.CreateObject(typeof(TargetType)) as TargetType;
}
public void ShouldCreateObjectWithIndexerBasedOnDynamicObject()
{
var dynamicObject = new DynamicObject
{
{ "Item", "ItemValue1" }
};
var obj = dynamicObject.CreateObject<ClassWithIndexerAndItemProperty>();
obj.Item.ShouldBe("ItemValue1");
}
public When_converting_to_serializable_object_with_private_properties()
{
var dynamicObject = new DynamicObject
{
{ "Int32Value", Int32Value },
{ "DoubleValue", DoubleValue },
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject<SerializableType>();
}
void Awake()
{
entity = GetComponent<Entity>();
entityKnowledge = entity.getWorkingKnowledge();
meshObject = GetComponentsInChildren<MeshRenderer>();
iterAff = new bool[meshObject.Length];
for (int i = 0; i < meshObject.Length; i++)
iterAff[i] = false;
init = false;
}
public When_converting_to_type_requiring_ctor_and_property_setter()
{
var dynamicObject = new DynamicObject()
{
{ "Id", Int32Value },
{ "DoubleValue", DoubleValue },
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject<Data>();
}
public void ShouldBeAbleToImplicitlyCastAnExpandoObjectToADuckTypeIfExpandoLooksLikeDuckType()
{
var mock = new MockClass();
var target = new DynamicObject(mock);
dynamic expando = new ExpandoObject(target);
ITest test = expando;
test.TargetMethod();
Assert.AreEqual(1, mock.CallCount);
}
public void ShouldBeAbleToAddNewMethodByAssigningADelegateToAPropertySetter()
{
var target = new DynamicObject();
dynamic expando = new ExpandoObject(target);
Func<int, int, int> addition = (a, b) => a + b;
expando.Add = addition;
var result = expando.Add(2, 2);
Assert.AreEqual(4, result);
}
public When_converting_to_object_with_private_property_setter()
{
var dynamicObject = new DynamicObject
{
{ "Int32Value", Int32Value },
{ "DoubleValue", DoubleValue },
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject<CustomType>();
}
private static async Task ChangeMethodAtRuntime(ILogger logger, DeadLetterObject deadletter)
{
Console.WriteLine("Define method at runtime using dynamic object");
var dynamicObject = new DynamicObject(logger);
Console.WriteLine("Define method at runtime [1st call - undefined]");
await deadletter.Send(dynamicObject, new GreetMessage());
Console.WriteLine("Define method at runtime [2nd call]");
dynamicObject.AddOrUpdateMethod<GreetMessage>(new Method(async sender => await Task.Run( () => Console.WriteLine("Howdy!"))));
await deadletter.Send(dynamicObject, new GreetMessage());
Console.WriteLine();
}
public When_created_based_on_object_with_guid_properties()
{
source = new ClassWithGuidProperties
{
Guid1 = Guid.NewGuid(),
Guid2 = Guid.Empty,
Guid3 = null,
};
dynamicObject = new DynamicObject(source);
}
public When_converting_to_object_with_missing_properties()
{
var dynamicObject = new DynamicObject
{
{ "Int32Value", Int32Value },
{ "DoubleValue", DoubleValue },
{ "NullableDateTimeValue", NullableDateTimeValue },
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject<CustomType>();
}
public When_converting_to_object_with_different_property_types_assignable()
{
var dynamicObject = new DynamicObject
{
{ "DoubleValue", Int32Value },
{ "NullableIntValue", Int32Value },
{ "Timestamp", Timestamp },
{ "StringValue", StringValue },
};
obj = dynamicObject.CreateObject<CustomType>();
}
public void ShouldOnlyExecuteReplacementMethodAfterReplacingExpandoMethod()
{
var target = new DynamicObject();
dynamic expando = new ExpandoObject(target);
Func<int, int, int> addition = (a, b) => a + b;
Func<int, int, int> subtraction = (a, b) => a - b;
expando.Operation = addition;
expando.Operation = subtraction;
int result = expando.Operation(2, 2);
Assert.AreEqual(0, result);
}
public BundleObject(BundlePlugin plugin, string name) : base(plugin)
{
if (name == null)
{
throw new ArgumentNullException(nameof(name));
}
Name = name;
Links = new List <BundleLink>();
SetValue(BundleObjectProperties.Name, Name, true);
SetValue(BundleObjectProperties.Links, Links, true);
UrlDestination = new DynamicObject <BundleObject>(this)
{
[BundleObjectProperties.JsType] = "/js/",
[BundleObjectProperties.CssType] = "/css/"
};
SetValue(BundleObjectProperties.UrlDestination, UrlDestination, true);
MinifyExtension = ".min";
this.Import(BundleObjectProperties.JsType, (Action <object, string, string>)AddJs);
this.Import(BundleObjectProperties.CssType, (Action <object, string>)AddCss);
this.Import(BundleObjectProperties.ContentType, (Action <object, string, string>)AddContent);
}
public bool TryMapToDynamicObject(IMapToDynamicObjectContext context, out DynamicObject dto)
{
Type objType = context.Object.GetType();
if (objType == typeof(string))
{
dto = null;
return(false);
}
Type elementType = Utils.TryGetSequenceType(objType);
if (elementType == null || elementType == typeof(DynamicObject))
{
dto = null;
return(false);
}
dto = new DynamicObject(typeof(IEnumerable <>).MakeGenericType(elementType));
context.AddToCache(dto);
dto.Add(
Elements,
new DynamicObject(((IEnumerable)context.Object).Cast <object>().Select(context.MapToDynamicObjectGraph).ToList()));
if (objType != typeof(List <>).MakeGenericType(elementType))
{
bool hasDefaultCtor = objType.GetTypeInfo().DeclaredConstructors
.Any(c => !c.IsStatic && c.IsPublic && c.GetParameters().Length == 0);
if (hasDefaultCtor)
{
dto.Add(CollectionType, new TypeInfo(objType, includePropertyInfos: false));
}
}
return(true);
}
private void RefreshStkCntBillAuditEntry(StockOrgOperateResult opResult, ErrType errtype)
{
Entity entryEntity = this.View.BusinessInfo.GetEntryEntity("FStkCountBillAuditEntry");
DynamicObjectType dynamicObjectType = entryEntity.DynamicObjectType;
DynamicObjectCollection entityDataObject = this.Model.GetEntityDataObject(entryEntity);
entityDataObject.Clear();
if (((opResult != null) && (opResult.StkCountBillAuditErrInfo != null)) && (opResult.StkCountBillAuditErrInfo.Count > 0))
{
foreach (OperateErrorInfo info in opResult.StkCountBillAuditErrInfo)
{
DynamicObject item = new DynamicObject(dynamicObjectType)
{
["CtbaErrType"] = info.ErrType,
["CtbaErrObjType"] = info.ErrObjType,
["CtbaErrObjKeyField"] = info.ErrObjKeyField,
["CtbaErrObjKeyID"] = info.ErrObjKeyID,
["CtbaErrMsg"] = info.ErrMsg
};
entityDataObject.Add(item);
}
}
this.View.UpdateView("FStkCountBillAuditEntry");
}
/// <summary>
/// Renders the format settings.
/// </summary>
/// <param name="config"></param>
internal void Render(dynamic config)
{
dynamic views = new DynamicObject();
if (ShouldSerializeDayViewFormat())
{
views.day = new { columnFormat = this.DayViewFormat };
}
if (ShouldSerializeWeekViewFormat())
{
views.week = new { columnFormat = this.WeekViewFormat };
}
if (ShouldSerializeMonthViewFormat())
{
views.month = new { columnFormat = this.MonthViewFormat };
}
if (!views.IsEmpty)
{
config.views = views;
}
}
public override void BeginOperationTransaction(BeginOperationTransactionArgs e)
{
base.BeginOperationTransaction(e);
if (e.DataEntitys != null && e.DataEntitys.Count() > 0)
{
foreach (DynamicObject dataEntity in e.DataEntitys)
{
long StockOrgId = Convert.ToInt64(dataEntity["StockOrgId_Id"]); //退货组织ID
Customer cust = XSXServiceHelper.XSXServiceHelper.GetIntCustomerProperty(this.Context, StockOrgId);
DynamicObject custObject = XSXServiceHelper.XSXServiceHelper.GetBasicObject(this.Context, "BD_Customer", cust.custID); //申请客户
DynamicObject BelongCust = XSXServiceHelper.XSXServiceHelper.GetBasicObject(this.Context, "BD_Customer", cust.BelongCustID); //服务端获取所属区域对象
DynamicObject CustType = XSXServiceHelper.XSXServiceHelper.GetBasicObject(this.Context, "PAEZ_CUSTTYPE", cust.CustTypeID); //服务端获取所属区域对象//客户类别
DynamicObject OrgType = ((DynamicObject)dataEntity["ApplicationOrgId"])["FORGTYPE"] as DynamicObject; //组织类型
dataEntity["FApplyCust"] = custObject;
dataEntity["FApplyCust_Id"] = cust.custID;
dataEntity["FBelongCust"] = BelongCust;
dataEntity["FBelongCust_Id"] = cust.BelongCustID;
dataEntity["FCUSTTYPE"] = CustType;
dataEntity["FCUSTTYPE_Id"] = cust.CustTypeID;
dataEntity["FORGTYPE"] = OrgType;
dataEntity["FORGTYPE_Id"] = Convert.ToInt64(OrgType["Id"]);
}
}
}
/// <summary>
/// Adds or updates a tracked property state.
/// </summary>
/// <param name="propertyName">The tracked property name</param>
/// <param name="targetObject">The object owning the tracked property</param>
public void AddOrUpdateTracking(string propertyName, DynamicObject targetObject = null)
{
Contract.Requires(propertyName != null, "Since this is tracking a property, the property property itself cannot be null or empty");
lock (_syncLock)
{
targetObject = targetObject ?? TargetObject as DynamicObject;
Contract.Assert(targetObject != null, "To add or update a tracking by the property name, the object owning the property must be a dynamic object");
object currentValue = targetObject.GetDynamicMember(propertyName);
ObjectPropertyChangeTracking existingTracking = null;
Contract.Assert(DynamicPropertyTrackings != null, "Cannot add a property tracking if tracking collection is null");
IObjectPropertyChangeTracking tracking = null;
if (!DynamicPropertyTrackings.TryGetValue(propertyName, out existingTracking))
{
ObjectPropertyChangeTracking propertyTracking = new ObjectPropertyChangeTracking(Configuration, TrackableObjectFactory, this, targetObject, propertyName, currentValue);
DynamicPropertyTrackings.Add(propertyName, propertyTracking);
tracking = propertyTracking;
}
else if (existingTracking != null)
{
existingTracking.CurrentValue = currentValue;
tracking = existingTracking;
}
if (tracking != null && tracking.HasChanged)
{
_Changed?.Invoke(this, new ObjectChangeEventArgs(targetObject, tracking));
}
}
}
static bool NotIntersecting(CachedShadowMapLight a_light, DynamicObject dynamic_object)
{
if (dynamic_object && dynamic_object.isActiveAndEnabled && dynamic_object.gameObject.activeInHierarchy)
{
switch (a_light.LightComponent.type)
{
case LightType.Spot:
return(!ConeSphereIntersection(spot_light: a_light, dynamic_object: dynamic_object));
case LightType.Point:
return(!SphereSphereIntersection(point_light: a_light, dynamic_object: dynamic_object));
case LightType.Directional:
case LightType.Area:
case LightType.Disc:
break;
default:
throw new ArgumentOutOfRangeException();
}
}
return(true);
}
public static string CheckBeforeAction(DynamicObject doOPP, Enum_OPPAction action)
{
string result = "";
switch (action)
{
case Enum_OPPAction.WIN:
result = CheckBeforeWin(doOPP);
break;
case Enum_OPPAction.UNWIN:
result = CheckBeforeUnWin(doOPP);
break;
case Enum_OPPAction.LOSE:
result = CheckBeforeLose(doOPP);
break;
case Enum_OPPAction.UNLOSE:
result = CheckBeforeUnLose(doOPP);
break;
}
return(result);
}
public void Execute(IBindingEnvironment environment)
{
DynamicObject dynobj = new DynamicObject();
int k = 0;
if (this.memberExpressions != null)
{
foreach (IExpression expression in this.memberExpressions)
{
string name = this.memberNames[k++];
object value = null;
if (expression != null)
{
value = expression.Evaluate(environment);
}
dynobj.SetValue(name, value);
}
}
environment.SetValue(this.name, dynobj);
}
public static string CheckBeforeLose(DynamicObject doOPP)
{
string a = doOPP["FCloseStatus"].ToString();
string a2 = doOPP["FDocumentStatus"].ToString();
string arg = doOPP["FBillNo"].ToString();
if (a == Convert.ToString(1))
{
return(string.Format(ResManager.LoadKDString("商机{0}已经关闭！", "006008030001261", SubSystemType.CRM, new object[0]), arg));
}
if (a2 == "E")
{
return(string.Format(ResManager.LoadKDString("商机{0}已经进行赢单操作！", "006008030001269", SubSystemType.CRM, new object[0]), arg));
}
if (a2 == "F")
{
return(string.Format(ResManager.LoadKDString("商机{0}已经进行输单操作！", "006008030001270", SubSystemType.CRM, new object[0]), arg));
}
if (a2 != "G")
{
return(string.Format(ResManager.LoadKDString("商机{0}只能对执行中的商机执行输单操作！", "006008030001271", SubSystemType.CRM, new object[0]), arg));
}
return("");
}
public Task <DynamicObject> UpdateAsync(Utilizer utilizer, string membershipId, string userId, DynamicObject model)
{
throw new NotImplementedException();
}
public override void AfterConvert(AfterConvertEventArgs e)
{
ExtendedDataEntity[] entitys = e.Result.FindByEntityKey("FBillHead");
DataTable dataTable = new DataTable();
//dataTable.Columns.Add("FMaterialId");
dataTable.Columns.Add("FCOLOR");
List <long> lstAuxPropId = new List <long>();
#region 第一步获取物料+辅助属性集合
foreach (ExtendedDataEntity entity in entitys)
{
DynamicObjectCollection entryDatas = (DynamicObjectCollection)entity.DataEntity["TreeEntity"];
if (entryDatas != null && entryDatas.Count > 0)
{
foreach (var item in entryDatas)
{
DataRow dataRow = dataTable.NewRow();
// DynamicObject material = item["MaterialId"] as DynamicObject;
//dataRow["FMaterialId"] = material != null ? Convert.ToInt64(material["Id"]) : 0;
DynamicObject auxprop = item["AuxPropId"] as DynamicObject;
string color = auxprop != null?Convert.ToString(auxprop["F100001_Id"]) : "";
dataRow["FCOLOR"] = color;
dataTable.Rows.Add(dataRow);
}
}
}
if (dataTable.Rows.Count == 0)
{
return;
}
BatchSqlParam batchSqlParam = new BatchSqlParam("JHF_T_ROUTMATTAB", dataTable, "");
batchSqlParam.TableAliases = "TR";
// batchSqlParam.AddWhereExpression("FMaterialId", KDDbType.Int64, "F_JHF_MATERIALID", "TR");
batchSqlParam.AddWhereExpression("FCOLOR", KDDbType.String, "F_JHF_COLOR", "TR");
string selectFieldSql = " distinct TR.F_JHF_COLOR,TR.F_JHF_ROUTID";
string where = string.Format(" TR.FDOCUMENTSTATUS='C' AND TR.FFORBIDSTATUS='A' ");
DynamicObjectCollection dyobjs = DBUtils.ExecuteDynamicObject(base.Context, batchSqlParam, selectFieldSql, where);
Dictionary <string, long> dicMatRout = new Dictionary <string, long>();
if (dyobjs != null && dyobjs.Count > 0)
{
foreach (var objs in dyobjs)
{
string color = Convert.ToString(objs["F_JHF_COLOR"]);
if (!dicMatRout.ContainsKey(color))
{
dicMatRout.Add(color, Convert.ToInt64(objs["F_JHF_ROUTID"]));
}
}
}
#endregion
#region 重新赋值工艺路线
foreach (ExtendedDataEntity entity in entitys)
{
DynamicObjectCollection entryDatas = (DynamicObjectCollection)entity.DataEntity["TreeEntity"];
if (entryDatas != null && entryDatas.Count > 0)
{
foreach (var item in entryDatas)
{
DynamicObject material = item["MaterialId"] as DynamicObject;
string materialId = material != null?Convert.ToString(material["Id"]) : "";
DynamicObject auxprop = item["AuxPropId"] as DynamicObject;
string color = auxprop != null?Convert.ToString(auxprop["F100001_Id"]) : "";
if (dicMatRout.ContainsKey(color))
{
long rountId = dicMatRout[color];
//赋值工艺路线
SetRountIdFiled(e.TargetBusinessInfo, item, "RoutingId", "FRoutingId", rountId);
}
}
}
}
#endregion
}
public override ReportHeader GetReportHeaders(IRptParams filter)
{
ReportHeader header = new ReportHeader();
DynamicObject cutomerfiler = filter.FilterParameter.CustomFilter;
String year = null;//年份
if (cutomerfiler["F_SXINSUN_YEAR"] != null)
{
year = Convert.ToInt32(cutomerfiler["F_SXINSUN_YEAR"]).ToString();
}
if (cutomerfiler["F_PAEZ_DEPT"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_DEPT"];
foreach (DynamicObject dept in cols)
{
String deptnumber = Convert.ToString(((DynamicObject)dept["F_PAEZ_DEPT"])["Number"]);
}
}
if (cutomerfiler["F_PAEZ_MulBaseSaler"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_MulBaseSaler"];
foreach (DynamicObject onesale in cols)
{
String salenumber = Convert.ToString(((DynamicObject)onesale["F_PAEZ_MulBaseSaler"])["Number"]);
}
}
if (Convert.ToBoolean(cutomerfiler["F_PAEZ_ShowDetail"]) == false)
{
// 编号
var deptname = header.AddChild("deptnumber", new LocaleValue("部门编码"));
var deptnumber = header.AddChild("deptname", new LocaleValue("部门"));
//var empnumber = header.AddChild("empnumber", new LocaleValue("员工编码"));
// var empname = header.AddChild("empname", new LocaleValue("员工"));
var yue1 = header.AddChild("yue1", new LocaleValue("1月份"));
//billNo.IsHyperlink = true; // 支持超链接
var yue2 = header.AddChild("yue2", new LocaleValue("2月份"));
var yue3 = header.AddChild("yue3", new LocaleValue("3月份"));
var yue4 = header.AddChild("yue4", new LocaleValue("4月份"));
var yue5 = header.AddChild("yue5", new LocaleValue("5月份"));
var yue6 = header.AddChild("yue6", new LocaleValue("6月份"));
var yue7 = header.AddChild("yue7", new LocaleValue("7月份"));
var yue8 = header.AddChild("yue8", new LocaleValue("8月份"));
var yue9 = header.AddChild("yue9", new LocaleValue("9月份"));
var yue10 = header.AddChild("yue10", new LocaleValue("10月份"));
var yue11 = header.AddChild("yue11", new LocaleValue("11月份"));
var yue12 = header.AddChild("yue12", new LocaleValue("12月份"));
var zoji = header.AddChild("zoji", new LocaleValue("总计"));
}
//var zoji = header.AddChild("gzzj", new LocaleValue("总计"));
// sum(dlzb) dlzb,sum(dlwqqk) dlwqqk,sum(gzzj) gzzj,sum(gzzb) gzzb,sum(gzzbwqqk) gzzbwqqk,sum(gzhud) gzhud
else
{
var deptname = header.AddChild("deptnumber", new LocaleValue("部门编码"));
var deptnumber = header.AddChild("deptname", new LocaleValue("部门"));
var empnumber = header.AddChild("empnumber", new LocaleValue("员工编码"));
var empname = header.AddChild("empname", new LocaleValue("员工"));
var yue1 = header.AddChild("yue1", new LocaleValue("1月份"));
//billNo.IsHyperlink = true; // 支持超链接
var yue2 = header.AddChild("yue2", new LocaleValue("2月份"));
var yue3 = header.AddChild("yue3", new LocaleValue("3月份"));
var yue4 = header.AddChild("yue4", new LocaleValue("4月份"));
var yue5 = header.AddChild("yue5", new LocaleValue("5月份"));
var yue6 = header.AddChild("yue6", new LocaleValue("6月份"));
var yue7 = header.AddChild("yue7", new LocaleValue("7月份"));
var yue8 = header.AddChild("yue8", new LocaleValue("8月份"));
var yue9 = header.AddChild("yue9", new LocaleValue("9月份"));
var yue10 = header.AddChild("yue10", new LocaleValue("10月份"));
var yue11 = header.AddChild("yue11", new LocaleValue("11月份"));
var yue12 = header.AddChild("yue12", new LocaleValue("12月份"));
var zoji = header.AddChild("zoji", new LocaleValue("总计"));
var dlzb = header.AddChild("dlzb", new LocaleValue("登录指标"));
var dlwqqk = header.AddChild("dlwqqk", new LocaleValue("完成情况"));
var gzzj = header.AddChild("gzzj", new LocaleValue("总计跟踪"));
var gzzb = header.AddChild("gzzb", new LocaleValue("跟踪指标"));
var gzzbwqqk = header.AddChild("gzzbwqqk", new LocaleValue("完成情况"));
var gzhud = header.AddChild("gzhud", new LocaleValue("跟踪更新"));
}
return(header);
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
// 根据当前用户的UserId 查询出其personId
StringBuilder sql0 = new StringBuilder();
sql0.AppendFormat(@"/*dialect*/ SELECT FLINKOBJECT FROM T_SEC_USER WHERE FUSERID = {0} ", this.Context.UserId);
DynamicObjectCollection collection = DBUtils.ExecuteDynamicObject(this.Context, sql0.ToString());
StringBuilder salerLimit = new StringBuilder();
Boolean flag = false;
if (collection.Count > 0)
{
//获取当前用户personId
DynamicObject personIdObj = (DynamicObject)collection[0];
int personId = Convert.ToInt32(personIdObj["FLINKOBJECT"]);
//销售员数据隔离
if (CRMServiceHelper.getSalerPersonids(this.Context, personId) != null)
{
List <long> salerList = CRMServiceHelper.getSalerPersonids(this.Context, personId);
int len = 0;
flag = true;
if (salerList.Count >= 1)
{
salerLimit.Append(" IN ( ");
}
foreach (long salerId in salerList)
{
len++;
if (len == salerList.Count)
{
salerLimit.Append(" " + salerId + " ) ");
}
else
{
salerLimit.Append(" " + salerId + ", ");
}
}
}
}
IDBService dbservice = ServiceHelper.GetService <IDBService>();
materialRptTableNames = dbservice.CreateTemporaryTableName(this.Context, 2);
DynamicObject cutomerfiler = filter.FilterParameter.CustomFilter;
String year = null;//年份
if (cutomerfiler["F_SXINSUN_YEAR"] != null)
{
year = Convert.ToInt32(cutomerfiler["F_SXINSUN_YEAR"]).ToString();
}
StringBuilder deptnumbersql = new StringBuilder();
if (cutomerfiler["F_PAEZ_DEPT"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_DEPT"];
int deptsize = 0;
if (cols.Count >= 1)
{
deptnumbersql.Append("in (");
}
foreach (DynamicObject dept in cols)
{
String deptnumber = Convert.ToString(((DynamicObject)dept["F_PAEZ_DEPT"])["Number"]);
deptsize = deptsize + 1;
if (deptsize == cols.Count)
{
deptnumbersql.Append("'" + deptnumber + "')");
}
else
{
deptnumbersql.Append("'" + deptnumber + "',");
}
}
}
StringBuilder billstatussql = new StringBuilder();
StringBuilder salenumbersql = new StringBuilder();
if (cutomerfiler["F_PAEZ_MulBaseSaler"] != null)
{
DynamicObjectCollection cols = (DynamicObjectCollection)cutomerfiler["F_PAEZ_MulBaseSaler"];
int salesize = 0;
if (cols.Count >= 1)
{
salenumbersql.Append("in (");
}
foreach (DynamicObject onesale in cols)
{
String salenumber = Convert.ToString(((DynamicObject)onesale["F_PAEZ_MulBaseSaler"])["Number"]);
salesize = salesize + 1;
if (salesize == cols.Count)
{
salenumbersql.Append("'" + salenumber + "')");
}
else
{
salenumbersql.Append("'" + salenumber + "',");
}
}
}
//添加单据状态过滤
int billstatussize = 0;
if (cutomerfiler["F_PAEZ_BillStatus"] != null)
{
string billstatusenum = Convert.ToString(cutomerfiler["F_PAEZ_BillStatus"]);
if (!string.IsNullOrEmpty(billstatusenum))
{
String[] billstauscol = billstatusenum.Split(',');
if (billstauscol.Length >= 1)
{
billstatussql.Append(" in (");
for (int i = 0; i < billstauscol.Length; i++)
{
billstatussize = billstatussize + 1;
if (billstatussize == billstauscol.Length)
{
billstatussql.Append("'" + billstauscol[i] + "')");
}
else
{
billstatussql.Append("'" + billstauscol[i] + "',");
}
}
}
}
}
string temTable1 = materialRptTableNames[0];
string temTable2 = materialRptTableNames[1];
// 拼接过滤条件 ： filter
// 略
//DynamicObject cutomerfiler = filter.FilterParameter.CustomFilter;
//if (cutomerfiler["F_JD_Date"] != null)
//{
// strbydate = Convert.ToDateTime(cutomerfiler["F_JD_Date"]).ToString("yyyy-MM-dd 23:59:59");
// strbydate2 = Convert.ToString(cutomerfiler["F_JD_Date"]);
//}
// 默认排序字段：需要从filter中取用户设置的排序字段
//string seqFld = string.Format(base.KSQL_SEQ, " t0.FID ");
// 取数SQL
// 商机登录部门
if (Convert.ToBoolean(cutomerfiler["F_PAEZ_ShowDetail"]) == true)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select distinct secuser.fname username ,emp.fnumber empnumber,empl.fname empname,post.fnumber postnumber,post_l.fname postname,dept.fnumber deptnumber,deptl.FNAME deptname ");
stringBuilder.AppendFormat(", opp.FBILLNO,opp.FOPPName ,opp.FSTARTDATE,year(opp.FSTARTDATE) rtyear ,month(opp.FSTARTDATE) rtmonth ,opp.FDOCUMENTSTATUS,activity.FBILLNO activitybillno ,opp.FCloseStatus \n");
stringBuilder.AppendFormat("into {0}", temTable1).AppendLine(" \n");
stringBuilder.AppendLine("from T_CRM_Opportunity opp \n ");
stringBuilder.AppendLine("left join T_CRM_Activity activity on activity.FOPPID = opp.FID \n ");
stringBuilder.AppendLine(" inner join V_BD_SALESMAN saler on opp.FBEMPID = saler.FID ");
stringBuilder.AppendLine(" inner join T_BD_STAFF staff on saler.FSTAFFID=staff.FSTAFFID ");
stringBuilder.AppendLine("inner join T_HR_EMPINFO emp on staff.FEMPINFOID=emp.FID \n");
stringBuilder.AppendLine("inner join T_HR_EMPINFO_L empl on empl.FID=emp.FID \n");
stringBuilder.AppendLine("inner join T_ORG_POST post on post.FPOSTID=staff.FPOSTID \n");
stringBuilder.AppendLine("inner join T_ORG_POST_L post_l on post_l.FPOSTID=post.FPOSTID ");
stringBuilder.AppendLine(" inner join t_sec_user secuser on secuser.FLINKOBJECT=emp.FPERSONID ");
// 商机中的销售部门
stringBuilder.AppendLine("inner join t_bd_department dept on dept.FDEPTID=opp.FSALEDEPTID -----部门 \n");
stringBuilder.AppendLine("inner join t_bd_department_L deptl on deptl.FDEPTID=dept.FDEPTID \n");
stringBuilder.AppendLine("where secuser.FTYPE=1 AND empl.FLOCALEID = 2052 AND post_l.FLOCALEID = 2052 AND deptl.FLOCALEID = 2052 ");
if (year != null && !year.Equals("0"))
{
stringBuilder.AppendLine(" and year(opp.FSTARTDATE)= ");
stringBuilder.AppendLine(year);
}
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
stringBuilder.AppendLine(" and dept.fnumber ").Append(deptnumbersql);
}
if (salenumbersql != null && salenumbersql.Length > 0)
{
stringBuilder.AppendLine(" and staff.fnumber ").Append(salenumbersql);
}
//单据状态
if (billstatussql.Length > 0)
{
stringBuilder.AppendLine(" and opp.FDOCUMENTSTATUS ").Append(billstatussql);
}
//销售员数据隔离
if (flag)
{
stringBuilder.AppendLine(" and OPP.FBEMPID ").Append(salerLimit);
}
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select toji2.deptnumber, toji2.deptname, toji2.empnumber, toji2.empname,yue1,yue2,yue3,yue4,yue5,yue6,yue7,yue8,\n");
stringBuilder.AppendLine("yue9,yue10,yue11,yue12, \n");
stringBuilder.AppendLine("yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12 zoji,isnull(F_PEJK_OPPQUNTA,0) dlzb,yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12-isnull(F_PEJK_OPPQUNTA,0) dlwqqk,gzzj gzzj ,isnull(F_PEJK_OPPTRACKQUNTA,0) gzzb, gzzj-isnull(F_PEJK_OPPTRACKQUNTA,0) gzzbwqqk,activitycount gzhud\n");
stringBuilder.AppendFormat("into {0}", temTable2).AppendLine(" \n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname, sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4) yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12\n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname,\n");
stringBuilder.AppendLine("case when rtmonth=1 then oppcounts else 0 end yue1 ,\n");
stringBuilder.AppendLine("case when rtmonth=2 then oppcounts else 0 end yue2,\n");
stringBuilder.AppendLine("case when rtmonth=3 then oppcounts else 0 end yue3,\n");
stringBuilder.AppendLine("case when rtmonth=4 then oppcounts else 0 end yue4,\n");
stringBuilder.AppendLine("case when rtmonth=5 then oppcounts else 0 end yue5,\n");
stringBuilder.AppendLine("case when rtmonth=6 then oppcounts else 0 end yue6,\n");
stringBuilder.AppendLine("case when rtmonth=7 then oppcounts else 0 end yue7,\n");
stringBuilder.AppendLine("case when rtmonth=8 then oppcounts else 0 end yue8,\n");
stringBuilder.AppendLine("case when rtmonth=9 then oppcounts else 0 end yue9,\n");
stringBuilder.AppendLine("case when rtmonth=10 then oppcounts else 0 end yue10,\n");
stringBuilder.AppendLine("case when rtmonth=11 then oppcounts else 0 end yue11,\n");
stringBuilder.AppendLine("case when rtmonth=12 then oppcounts else 0 end yue12\n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname,rtyear,rtmonth, count (distinct FBILLNO) oppcounts\n");
stringBuilder.AppendFormat("from {0} opp \n", temTable1);
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname,rtyear,rtmonth\n");
stringBuilder.AppendLine(") toji0\n");
stringBuilder.AppendLine(") tojimonth\n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(") toji2\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("left join \n");
stringBuilder.AppendLine(" (\n");
stringBuilder.AppendLine("select distinct emp.fnumber empnumber,F_PEJK_OPPQUNTA,F_PEJK_OPPTRACKQUNTA \n");
stringBuilder.AppendLine("from PEJK_SALERQUNTAENTRY SALERQUNTAENTRY \n");
stringBuilder.AppendLine("left join PEJK_SALERQUNTA SALERQUNTA on SALERQUNTA.fid=SALERQUNTAENTRY.fid \n");
stringBuilder.AppendLine("left join V_BD_SALESMAN salesman on salesman.fid=SALERQUNTAENTRY.F_PEJK_SALER \n");
stringBuilder.AppendLine("left join T_BD_STAFF staff on staff.FSTAFFID= salesman.fstaffid \n");
stringBuilder.AppendLine("left join T_HR_EMPINFO emp on staff.FEMPINFOID=emp.FID \n");
stringBuilder.AppendLine("left join T_HR_EMPINFO_L empl on empl.FID=emp.FID --员工 \n");
stringBuilder.AppendLine(") zbdj on zbdj.empnumber=toji2.empnumber\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("inner join \n");
stringBuilder.AppendLine("(\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname, sum(gzzj) gzzj,sum(activitycount ) activitycount \n");
stringBuilder.AppendLine("from (\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname , count (distinct FBILLNO) gzzj,0 activitycount \n");
stringBuilder.AppendFormat("from {0} opp \n", temTable1);
stringBuilder.AppendLine("where opp.FDOCUMENTSTATUS is not null \n");
stringBuilder.AppendLine("and opp.FCloseStatus='0' --- and opp.FDOCUMENTSTATUS>='C' \n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname \n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("union all\n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine("select deptnumber, deptname,empnumber, empname , 0 gzzj,count(distinct activitybillno) activitycount \n");
stringBuilder.AppendFormat("from {0} opp \n", temTable1);
stringBuilder.AppendLine("where opp.FDOCUMENTSTATUS is not null \n");
//stringBuilder.AppendLine("and opp.FDOCUMENTSTATUS='E' \n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname \n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(") zjtongji\n");
stringBuilder.AppendLine("group by deptnumber, deptname,empnumber,empname \n");
stringBuilder.AppendLine("\n");
stringBuilder.AppendLine(") zitongji2 on zitongji2.empnumber=toji2.empnumber where zitongji2.deptnumber = toji2.deptnumber \n");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入部门小计
//
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("insert into {0} ", temTable2);
stringBuilder.AppendLine(" select deptnumber,deptname,'部门小计','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12,sum(zoji) zoji,sum(dlzb) dlzb,sum(dlwqqk) dlwqqk,sum(gzzj) gzzj,sum(gzzb) gzzb,sum(gzzbwqqk) gzzbwqqk,sum(gzhud) gzhud ");
stringBuilder.AppendFormat(" from {0} ", temTable2);
stringBuilder.AppendLine(" group by deptnumber, deptname ");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入 总计
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("insert into {0}", temTable2);
stringBuilder.AppendLine("\n select '总计','','','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12,sum(zoji) zoji,sum(dlzb) dlzb,sum(dlwqqk) dlwqqk,sum(gzzj) gzzj,sum(gzzb) gzzb,sum(gzzbwqqk) gzzbwqqk,sum(gzhud) gzhud ");
stringBuilder.AppendFormat("from {0} ", temTable2);
stringBuilder.AppendLine(" where empnumber<>'部门小计' ");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(" select ROW_NUMBER() OVER(ORDER BY deptnumber) FIDENTITYID,tmp2.* into {0} from {1} tmp2 order by deptnumber,deptname,empnumber,empname ", tableName, temTable2);
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
}
else
{
// 取数SQL
// 商机登录部门
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select distinct secuser.fname username ,emp.fnumber empnumber,empl.fname empname,post.fnumber postnumber,post_l.fname postname,dept.fnumber deptnumber,deptl.FNAME deptname ");
// stringBuilder.AppendFormat(", opp.FBILLNO,opp.FOPPName ,opp.FCREATEDATE,year(opp.FCREATEDATE) rtyear ,month(opp.FCREATEDATE) rtmonth ,opp.FDOCUMENTSTATUS,activity.FBILLNO activitybillno,opp.FCloseStatus \n");
stringBuilder.AppendFormat(", opp.FBILLNO,opp.FOPPName ,opp.FSTARTDATE,year(opp.FSTARTDATE) rtyear ,month(opp.FSTARTDATE) rtmonth ,opp.FDOCUMENTSTATUS,activity.FBILLNO activitybillno,opp.FCloseStatus \n");
stringBuilder.AppendFormat("into {0}", temTable1).AppendLine(" \n");
stringBuilder.AppendLine("from T_CRM_Opportunity opp \n ");
stringBuilder.AppendLine("left join T_CRM_Activity activity on activity.FOPPID = opp.FID \n ");
stringBuilder.AppendLine(" inner join V_BD_SALESMAN saler on opp.FBEMPID = saler.FID ");
stringBuilder.AppendLine(" inner join T_BD_STAFF staff on saler.FSTAFFID=staff.FSTAFFID ");
stringBuilder.AppendLine("inner join T_HR_EMPINFO emp on staff.FEMPINFOID=emp.FID \n");
stringBuilder.AppendLine("inner join T_HR_EMPINFO_L empl on empl.FID=emp.FID \n");
stringBuilder.AppendLine("inner join T_ORG_POST post on post.FPOSTID=staff.FPOSTID \n");
stringBuilder.AppendLine("inner join T_ORG_POST_L post_l on post_l.FPOSTID=post.FPOSTID ");
stringBuilder.AppendLine(" inner join t_sec_user secuser on secuser.FLINKOBJECT=emp.FPERSONID ");
// 商机中的销售部门
stringBuilder.AppendLine("inner join t_bd_department dept on dept.FDEPTID=opp.FSALEDEPTID -----部门 \n");
stringBuilder.AppendLine("inner join t_bd_department_L deptl on deptl.FDEPTID=dept.FDEPTID \n");
stringBuilder.AppendLine("where secuser.FTYPE=1 AND empl.FLOCALEID = 2052 AND post_l.FLOCALEID = 2052 AND deptl.FLOCALEID = 2052 ");
if (year != null && !year.Equals("0"))
{
stringBuilder.AppendLine(" and year(opp.FSTARTDATE)= ");
stringBuilder.AppendLine(year);
}
if (deptnumbersql != null && deptnumbersql.Length > 0)
{
stringBuilder.AppendLine(" and dept.fnumber ").Append(deptnumbersql);
}
if (salenumbersql != null && salenumbersql.Length > 0)
{
stringBuilder.AppendLine(" and emp.fnumber ").Append(salenumbersql);
}
//单据状态
if (billstatussql.Length > 0)
{
stringBuilder.AppendLine(" and opp.FDOCUMENTSTATUS ").Append(billstatussql);
}
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendLine("select deptnumber, deptname, yue1,yue2,yue3,yue4,yue5,yue6,yue7,yue8, \n");
stringBuilder.AppendLine("yue9,yue10,yue11,yue12, \n");
stringBuilder.AppendLine("yue1+yue2+yue3+yue4+yue5+yue6+yue7+yue8+yue9+yue10+yue11+yue12 zoji \n");
stringBuilder.AppendFormat("into {0} ", temTable2);
stringBuilder.AppendLine("from ( \n");
stringBuilder.AppendLine("select deptnumber, deptname, sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4) yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12\n");
stringBuilder.AppendLine("from ( \n");
stringBuilder.AppendLine("select deptnumber, deptname,case when rtmonth=1 then oppcounts else 0 end yue1 , \n");
stringBuilder.AppendLine("case when rtmonth=2 then oppcounts else 0 end yue2, \n");
stringBuilder.AppendLine("case when rtmonth=3 then oppcounts else 0 end yue3, \n");
stringBuilder.AppendLine("case when rtmonth=4 then oppcounts else 0 end yue4, \n");
stringBuilder.AppendLine("case when rtmonth=5 then oppcounts else 0 end yue5, \n");
stringBuilder.AppendLine("case when rtmonth=6 then oppcounts else 0 end yue6, \n");
stringBuilder.AppendLine("case when rtmonth=7 then oppcounts else 0 end yue7, \n");
stringBuilder.AppendLine("case when rtmonth=8 then oppcounts else 0 end yue8, \n");
stringBuilder.AppendLine("case when rtmonth=9 then oppcounts else 0 end yue9, \n");
stringBuilder.AppendLine("case when rtmonth=10 then oppcounts else 0 end yue10, \n");
stringBuilder.AppendLine("case when rtmonth=11 then oppcounts else 0 end yue11, \n");
stringBuilder.AppendLine("case when rtmonth=12 then oppcounts else 0 end yue12 \n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine("from ( \n");
stringBuilder.AppendLine("select deptnumber, deptname,rtyear,rtmonth, count (distinct FBILLNO) oppcounts \n");
stringBuilder.AppendFormat("from {0} \n", temTable1);
stringBuilder.AppendLine("group by deptnumber, deptname,rtyear,rtmonth \n");
stringBuilder.AppendLine(" \n");
stringBuilder.AppendLine(") toji \n");
stringBuilder.AppendLine(" ) toji0 \n");
stringBuilder.AppendLine(" group by deptnumber, deptname \n");
stringBuilder.AppendLine(") toji2 \n");
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入部门小计
//
//stringBuilder = new StringBuilder();
//stringBuilder.AppendFormat("insert into {0} ", temTable2);
//stringBuilder.AppendLine(" select deptnumber, deptname,'部门小计','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12, sum(zoji) zoji ");
//stringBuilder.AppendFormat(" from {0} ", temTable2);
//stringBuilder.AppendLine(" group by deptnumber, deptname ");
//DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
//插入 总计
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("insert into {0}", temTable2);
stringBuilder.AppendLine("\n select '总计','',sum(yue1) yue1,sum(yue2) yue2,sum(yue3) yue3,sum(yue4)yue4,sum(yue5) yue5,sum(yue6) yue6,sum(yue7) yue7,sum(yue8) yue8,sum(yue9) yue9,sum(yue10) yue10,sum(yue11) yue11,sum(yue12) yue12,sum(zoji) zoji ");
stringBuilder.AppendFormat("from {0} ", temTable2);
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(" select ROW_NUMBER() OVER(ORDER BY deptnumber) FIDENTITYID,tmp2.* into {0} from {1} tmp2 order by deptnumber,deptname ", tableName, temTable2);
DBUtils.ExecuteDynamicObject(this.Context, stringBuilder.ToString());
}
}
/// <summary>
/// 收款退款单明细
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private List <ReFundBillEntry> GetReFundEntry(Context ctx, DynamicObject obj)
{
List <ReFundBillEntry> entries = null;
ReFundBillEntry entry = null;
if (obj != null)
{
DynamicObject cust = obj["F_HS_B2CCUSTID"] as DynamicObject;
string custNo = SQLUtils.GetFieldValue(cust, "Number");
DynamicObject curr = obj["CURRENCYID"] as DynamicObject;
string currNo = SQLUtils.GetFieldValue(curr, "Number");
decimal custCredit = LogHelper.GetCustCreditLine(ctx, custNo, "F_HS_CreditLineUSD");
decimal resiCustCredit = LogHelper.GetCustCreditLine(ctx, custNo, "F_HS_SurplusCreditUSD");
decimal rateToUSA = GetExchangeRate(ctx, currNo);
DynamicObjectCollection coll = obj["REFUNDBILLENTRY"] as DynamicObjectCollection;
if (coll != null && coll.Count > 0)
{
entries = new List <ReFundBillEntry>();
foreach (var item in coll)
{
if (item != null)
{
entry = new ReFundBillEntry();
entry.FEntryId = Convert.ToInt32(SQLUtils.GetFieldValue(item, "Id"));
entry.FRealReFundAmountFor_D = Math.Round(Convert.ToDecimal(SQLUtils.GetFieldValue(item, "REALREFUNDAMOUNTFOR")), 2);
DynamicObject sType = item["SETTLETYPEID"] as DynamicObject;
entry.FSettleTypeId = SQLUtils.GetFieldValue(sType, "Number");
decimal reFundTurnUSD = Math.Round(entry.FRealReFundAmountFor_D / rateToUSA, 2);
if (custCredit > 0)
{
if (reFundTurnUSD >= (custCredit - resiCustCredit))
{
entry.F_HS_CreditLineRechargeUSD = custCredit - resiCustCredit;
entry.F_HS_BalanceRechargeUSD = reFundTurnUSD - entry.F_HS_CreditLineRechargeUSD;
}
else
{
entry.F_HS_CreditLineRechargeUSD = reFundTurnUSD;
entry.F_HS_BalanceRechargeUSD = 0;
}
}
else if (custCredit == 0)
{
entry.F_HS_CreditLineRechargeUSD = 0;
entry.F_HS_BalanceRechargeUSD = reFundTurnUSD;
}
entries.Add(entry);
}
}
}
}
return(entries);
}
public override void AfterConvert(AfterConvertEventArgs e)
{
base.AfterConvert(e);
//获取整个下推的数据
var dataObjs = e.Result.FindByEntityKey("FBillHead");
if (dataObjs != null)
{
foreach (var extendedDataEntity in dataObjs)
{
DynamicObjectCollection EntryCollect = null;
DynamicObject BillObject = extendedDataEntity.DataEntity;
//生产入库单明细
EntryCollect = extendedDataEntity.DataEntity["Entity"] as DynamicObjectCollection;
Entity entity2 = e.TargetBusinessInfo.GetEntity("FEntity_Link");
if (EntryCollect != null && EntryCollect.Count > 0)
{
foreach (DynamicObject row in EntryCollect)
{
string fmoent = "";
string sql = "select FENTRYID FROM T_PRD_MOENTRY where FID= "
+ row["MoId"]
+ "and FSEQ= "
+ row["MoEntrySeq"]; //sql拼接
DataSet sqltable = DBServiceHelper.ExecuteDataSet(this.Context, sql); //放入DataSet
if (sqltable != null && sqltable.Tables.Count > 0 && sqltable.Tables[0].Rows.Count > 0)
{
fmoent = sqltable.Tables[0].Rows[0][0].ToString();
}
//源单类型
row["SrcBillType"] = "PRD_MO";
//源单编号
row["SrcBillNo"] = row["MoBillNo"];
//源单内码
row["SrcInterId"] = row["MoId"];
//源单分录内码
row["SrcEntryId"] = fmoent;
//源单行号
row["SrcEntrySeq"] = row["MoEntrySeq"];
DynamicObjectCollection linkCollect = row["FEntity_Link"] as DynamicObjectCollection;
linkCollect[0]["RuleId"] = "PRD_MO2INSTOCK";//PRD_INSTOCK
linkCollect[0]["STableName"] = "T_PRD_MOENTRY";
linkCollect[0]["SBillId"] = row["MoId"];
//生产订单分录内码
linkCollect[0]["SId"] = fmoent;
}
}
}
}
}
public DynamicObjectFieldInfoDescriptionsProvider(DynamicObject item)
{
this.firstItem = item;
}
/// <summary> /// Set object value /// </summary> /// <param name="name">property name</param> /// <param name="value">value</param> public void Set(string name, DynamicObject value) => InnerSet(name, value);
/// <summary>
/// 实际运行的Run 方法
/// </summary>
/// <param name="ctx"></param>
/// <param name="schedule"></param>
public void Run(Context ctx, Schedule schedule)
{
DateTime currentTime = new System.DateTime();
JNBandPara actband = new JNBandPara();
JNBand findbandservice = new JNBand();
currentTime = TimeServiceHelper.GetSystemDateTime(ctx);
string sql = "";
sql = string.Format(@"select t1.FENTRYID,t1.F_JNOBSSID,t2.FACCOUNTID from T_AP_PAYBILLENTRY_B t1
join T_AP_PAYBILLENTRY t2 on t2.fid=t1.fid
join T_AP_PAYBILL t3 on t1.FID=t3.FID where DATEDIFF(DAY,GETDATE(),FAPPROVEDATE)>-10
and (t1.F_JNOBSSID is not null and t1.F_JNOBSSID <>'')");
DynamicObjectCollection rundatas = DBUtils.ExecuteDynamicObject(ctx, sql);
string token = "";
int i = 0;
foreach (var rundata in rundatas)
{
FormMetadata formMetadata = MetaDataServiceHelper.Load(ctx, "CN_BANKACNT") as FormMetadata;
DynamicObject FrACCOUNT = BusinessDataServiceHelper.LoadSingle(
ctx,
rundata["FACCOUNTID"],
formMetadata.BusinessInfo.GetDynamicObjectType());
DynamicObject FrBAND = FrACCOUNT["BANKID"] as DynamicObject;
actband.bandid = Convert.ToInt32(FrACCOUNT["Id"]);
//actband.addr = Convert.ToString(FrACCOUNT["BANKADDRESS"]);
actband.name = Convert.ToString(FrACCOUNT["Name"]);
actband.bandnum = Convert.ToString(FrACCOUNT["ACNTBRANCHNUMBER"]);
actband.cn = Convert.ToString(FrACCOUNT["NUMBER"]);
actband.bandname = Convert.ToString(FrBAND["Name"]);
string obssid = Convert.ToString(rundata["F_JNOBSSID"]);
if (i == 0)//首行获取令牌
{
token = findbandservice.checkin(ctx, actband);
i++;
}
string result = "";
if (obssid.Length > 1)
{
result = findbandservice.findPay(ctx, actband, obssid, token);
}
if (result.Length > 0)
{
switch (result)
{
default:
BusinessDataServiceHelper.SetState(ctx, "T_AP_PAYBILLENTRY_B", "FBankStatus", "A", "F_JNOBSSID", new object[] { obssid });
break;
case "待授权":
BusinessDataServiceHelper.SetState(ctx, "T_AP_PAYBILLENTRY_B", "FBankStatus", "B", "F_JNOBSSID", new object[] { obssid });
break;
case "ok":
BusinessDataServiceHelper.SetState(ctx, "T_AP_PAYBILLENTRY_B", "FBankStatus", "C", "F_JNOBSSID", new object[] { obssid });
break;
case "授权拒绝":
BusinessDataServiceHelper.SetState(ctx, "T_AP_PAYBILLENTRY_B", "FBankStatus", "D", "F_JNOBSSID", new object[] { obssid });
break;
case "交易处理中":
BusinessDataServiceHelper.SetState(ctx, "T_AP_PAYBILLENTRY_B", "FBankStatus", "B", "F_JNOBSSID", new object[] { obssid });
break;
}
}
//Thread.Sleep(5000);
}
//throw new NotImplementedException();
if (token.Length > 1)
{
findbandservice.checkout(ctx, actband);
}
}
public static void Main(string[] args)
{
#if DEBUG
UnitTest();
#endif
Benchmark(new[]
{
new Tuple <string, Action <int, object[], int> >(
"dynamic ",
(loop, objs, typeN) =>
{
for (var i = 0; i < loop; ++i)
{
dynamic o = objs[i % typeN];
var v = o.Count + o.Count * o.Capacity;
}
}
),
new Tuple <string, Action <int, object[], int> >(
"reflection ",
(loop, objs, typeN) =>
{
for (var i = 0; i < loop; ++i)
{
var o = objs[i % typeN];
var mcount = o.GetType().GetMethod(MethodName_Count);
var mcapacity = o.GetType().GetMethod(MethodName_Capacity);
var v = (int)mcount.Invoke(o, null) + (int)mcount.Invoke(o, null) * (int)mcapacity.Invoke(o, null);
}
}
),
new Tuple <string, Action <int, object[], int> >(
"reflection optimize",
(loop, objs, typeN) =>
{
if (typeN == 1)
{
var o = objs[0];
var mcount = o.GetType().GetMethod(MethodName_Count);
var mcapacity = o.GetType().GetMethod(MethodName_Capacity);
for (var i = 0; i < loop; ++i)
{
var v = (int)mcount.Invoke(o, null) + (int)mcount.Invoke(o, null) * (int)mcapacity.Invoke(o, null);
}
}
else
{
for (var i = 0; i < loop; ++i)
{
var o = objs[i % typeN];
var mcount = o.GetType().GetMethod(MethodName_Count);
var mcapacity = o.GetType().GetMethod(MethodName_Capacity);
var v = (int)mcount.Invoke(o, null) + (int)mcount.Invoke(o, null) * (int)mcapacity.Invoke(o, null);
}
}
}
),
new Tuple <string, Action <int, object[], int> >(
"monomorphic",
(loop, objs, typeN) =>
{
if (typeN > 1)
{
throw new Exception("Out of time");
}
for (var i = 0; i < loop; ++i)
{
var o = objs[i % typeN];
var v = MonoCache_Count.Get(o) + MonoCache_Count.Get(o) * MonoCache_Capacity.Get(o);
}
}
),
new Tuple <string, Action <int, object[], int> >(
"polymorphic",
(loop, objs, typeN) =>
{
if (typeN > 4)
{
throw new Exception("Out of time");
}
for (var i = 0; i < loop; ++i)
{
var o = objs[i % typeN];
var v = PolyCache_Count.Get(o) + PolyCache_Count.Get(o) * PolyCache_Capacity.Get(o);
}
}
),
new Tuple <string, Action <int, object[], int> >(
"megamorphic",
(loop, objs, typeN) =>
{
for (var i = 0; i < loop; ++i)
{
var o = objs[i % typeN];
var v = MegaCache_Count.Get(o) + MegaCache_Count.Get(o) * MegaCache_Capacity.Get(o);
}
}
),
new Tuple <string, Action <int, object[], int> >(
"callsite",
(loop, objs, typeN) =>
{
for (var i = 0; i < loop; ++i)
{
var o = objs[i % typeN];
var v = CallSite_Count.Get(o) + CallSite_Count.Get(o) * CallSite_Capacity.Get(o);
}
}
),
new Tuple <string, Action <int, object[], int> >(
"simple",
(loop, objs, typeN) =>
{
for (var i = 0; i < loop; ++i)
{
var o = objs[i % typeN];
var v = SimpleCache_Count.Get(o) + SimpleCache_Count.Get(o) * SimpleCache_Capacity.Get(o);
}
}
),
new Tuple <string, Action <int, object[], int> >(
"tointerface",
(loop, objs, typeN) =>
{
for (var i = 0; i < loop; ++i)
{
var icc = DynamicObject.ToInterface <ICountCapacity>(objs[i % typeN]);
var v = icc.get_Count() + icc.get_Count() * icc.get_Capacity();
}
}
),
new Tuple <string, Action <int, object[], int> >(
"tointerface2 (ineq)",
(loop, objs, typeN) =>
{
for (var i = 0; i < typeN; ++i)
{
var icc = DynamicObject.ToInterface <ICountCapacity>(objs[i]);
for (var j = i; j < loop; j += typeN)
{
var v = icc.get_Count() + icc.get_Count() * icc.get_Capacity();
}
}
}
),
});
}
public Department(DynamicObject obj) : base(obj)
{
}
private void InsertData(IRptParams filter)
{
#region 过滤条件
//过滤条件
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
//获取当前库存组织
DynamicObject nowOrg = dyFilter["F_PAEZ_OrgId"] as DynamicObject;
long nowOrgId = Convert.ToInt64(nowOrg["Id"]);
//获取发出仓库
DynamicObject outStock = dyFilter["F_PAEZ_OutWareHouse"] as DynamicObject;
long outStockId = Convert.ToInt64(outStock["Id"]);
//获取返箱仓库
DynamicObject returnStock = dyFilter["F_PAEZ_InWareHouse"] as DynamicObject;
long returnStockId = Convert.ToInt64(returnStock["Id"]);
//过滤条件 获取分销站
DynamicObject Fxz = dyFilter["F_PAEZ_Dept"] as DynamicObject;
if (Fxz != null)
{
FxzId = Convert.ToInt64(Fxz["Id"]);
}
//过滤条件 获取线路
DynamicObject line = dyFilter["F_PAEZ_LINE"] as DynamicObject;
if (line != null)
{
lineId = Convert.ToInt64(line["Id"]);
}
//当前日期月份
DateTime nowDate = Convert.ToDateTime(dyFilter["F_PAEZ_Date"]);
int nowYear = Convert.ToInt32(nowDate.Year);
int nowMouth = Convert.ToInt32(nowDate.Month);
int nowDay = Convert.ToInt32(nowDate.Day);
string nowYearString = Convert.ToString(nowYear);
string nowMouthString = Convert.ToString(nowMouth);
string nowDayString = Convert.ToString(nowDay);
string nowDateString = nowYearString + "/" + nowMouthString + "/" + nowDayString;
//上月日期月份
int lastYear = nowYear - 1;
int lastMouth;
string lastYearString;
string lastMouthString;
string lastString;
//所选日期月份
string selectString;
selectString = nowYear + "/" + nowMouth + "/" + 20;//选择月第20号
DateTime last;
DateTime select = Convert.ToDateTime(selectString);
//如果月份为1月，年份减一 12月
if (nowMouth <= 1)
{
lastMouth = 12;
lastYearString = Convert.ToString(lastYear);
lastMouthString = Convert.ToString(lastMouth);
lastString = Convert.ToString(lastYearString + "/" + lastMouthString + "/" + 21);
last = Convert.ToDateTime(lastString);
}
else
{
lastMouth = nowMouth - 1;
lastYearString = Convert.ToString(nowYear);
lastMouthString = Convert.ToString(lastMouth);
lastString = Convert.ToString(lastYearString + "/" + lastMouthString + "/" + 21);
last = Convert.ToDateTime(lastString);
}
#endregion
#region 线路发箱
string sqlLX = string.Format(@"insert into {4}
select
0 seq,
F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
sum(F_PAEZ_LXFXZFX) F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select /*销售出库单KA客户*/ m4.fname F_PAEZ_XL,sum(t2.FREALQTY) F_PAEZ_LXFXZFX
from T_SAL_OUTSTOCK t1
inner join T_SAL_OUTSTOCKENTRY t2 on t1.fid=t2.fid
inner join t_bd_material m3 on t2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = t1.fcustomerid
where t1.FSALEORGID = {3} and t1.FDATE >= to_Date ('{0}','yyyy/MM/dd') and t1.FDATE <= to_Date('{1}','yyyy/MM/dd')
and t2.FSTOCKID = {2}
and ( m3.fnumber='07040000' and m3.fuseorgid={3})
and t1.FSALEDEPTID = {5}
and t1.FLINE = {6}
group by t1.FCUSTOMERID,t2.FMATERIALID,m4.fname)
group by F_PAEZ_XL
", lastString, selectString, outStockId, nowOrgId, tempTable1, FxzId, lineId);
DBUtils.Execute(base.Context, sqlLX);
string sqlBX = string.Format(@"insert into {4}
select
0 seq,
F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
sum(F_PAEZ_BXFXZFX) F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select /*销售出库单KA客户*/ m4.fname F_PAEZ_XL,sum(t2.FREALQTY) F_PAEZ_BXFXZFX
from T_SAL_OUTSTOCK t1
inner join T_SAL_OUTSTOCKENTRY t2 on t1.fid=t2.fid
inner join t_bd_material m3 on t2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = t1.fcustomerid
where t1.FSALEORGID = {3} and t1.FDATE >= to_Date ('{0}','yyyy/MM/dd') and t1.FDATE <= to_Date('{1}','yyyy/MM/dd')
and t2.FSTOCKID = {2}
and ( m3.fnumber='07040008' and m3.fuseorgid={3})
and t1.FSALEDEPTID = {5}
and t1.FLINE = {6}
group by t1.FCUSTOMERID,t2.FMATERIALID,m4.fname )
group by F_PAEZ_XL
", lastString, selectString, outStockId, nowOrgId, tempTable2, FxzId, lineId);
DBUtils.Execute(base.Context, sqlBX);
#endregion
#region 返站箱数
string sql2LX = string.Format(@"insert into {4}
select
0 seq,
F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
sum(F_PAEZ_LXFZXS) F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select/*销售退货（KA客户）*/ m4.fname as F_PAEZ_XL,sum(s2.FREALQTY) as F_PAEZ_LXFZXS
from T_SAL_RETURNSTOCK s1
inner join T_SAL_RETURNSTOCKENTRY s2 on s1.fid=s2.fid
inner join t_bd_material m3 on s2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = s1.fretcustid
where s1.FSALEORGID = {3} and s1.FDATE >= to_Date ('{0}','yyyy/MM/dd') and s1.FDATE <= to_Date('{1}','yyyy/MM/dd')
and s2.FSTOCKID = {2}
and s1.FRETURNTYPE0 = 2
and s1.FSALEDEPTID = {5}
and s1.FLINE = {6}
and ( m3.fnumber='07040000' and m3.fuseorgid={3})
group by m4.fname )
group by F_PAEZ_XL
", lastString, selectString, outStockId, nowOrgId, tempTable3, FxzId, lineId);
DBUtils.Execute(base.Context, sql2LX);
string sql2BX = string.Format(@"insert into {4}
select
0 seq,
F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
sum(F_PAEZ_BXFZXS) F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select/*销售退货（KA客户）*/ m4.fname as F_PAEZ_XL,sum(s2.FREALQTY) as F_PAEZ_BXFZXS
from T_SAL_RETURNSTOCK s1
inner join T_SAL_RETURNSTOCKENTRY s2 on s1.fid=s2.fid
inner join t_bd_material m3 on s2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = s1.fretcustid
where s1.FSALEORGID = {3} and s1.FDATE >= to_Date ('{0}','yyyy/MM/dd') and s1.FDATE <= to_Date('{1}','yyyy/MM/dd')
and s2.FSTOCKID = {2}
and s1.FRETURNTYPE0 = 2
and s1.FSALEDEPTID = {5}
and s1.FLINE = {6}
and ( m3.fnumber='07040004' and m3.fuseorgid={3})
group by m4.fname )
group by F_PAEZ_XL
", lastString, selectString, outStockId, nowOrgId, tempTable4, FxzId, lineId);
DBUtils.Execute(base.Context, sql2BX);
#endregion
#region 丢箱数
string sql3LX = string.Format(@"/*dialect*/insert into {4}
select
0 seq,
F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
sum(F_PAEZ_LXDXS) F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select/*其他出库单(KA客户丢箱)*/ m4.fname as F_PAEZ_XL,sum(d2.FQTY) as F_PAEZ_LXDXS
from T_STK_MISDELIVERY d1
inner join T_STK_MISDELIVERYENTRY d2 on d2.fid = d1.fid
inner join t_bd_stock d3 on d3.fstockid=d2.fstockid
inner join t_bd_material m3 on d2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = d1.FCUSTID
where d1.FSTOCKORGID = {3} and d1.FDATE >= to_Date ('{0}','yyyy/MM/dd') and d1.FDATE <= to_Date('{1}','yyyy/MM/dd')
and （d1.FBILLTYPEID ='5d9748dd76f550' or d1.FBILLTYPEID='5d9748bd76f4ca')
and d2.FSTOCKID = {2} /*发货仓库=发出仓库*/
and ( m3.fnumber='07040000' and m3.fuseorgid={3})
and d1.FDEPTID = {5}
and d1.FCUSTID = null
and d1.F_PAEZ_LINE = {6}
group by m4.fname )
group by F_PAEZ_XL
", lastString, selectString, outStockId, nowOrgId, tempTable5, FxzId, lineId);
DBUtils.Execute(base.Context, sql3LX);
string sql3BX = string.Format(@"/*dialect*/insert into {4}
select
0 seq,
F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
sum(F_PAEZ_BXDXS) F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select/*其他出库单(KA客户丢箱)*/ m4.fname as F_PAEZ_XL,sum(d2.FQTY) as F_PAEZ_BXDXS
from T_STK_MISDELIVERY d1
inner join T_STK_MISDELIVERYENTRY d2 on d2.fid = d1.fid
inner join t_bd_stock d3 on d3.fstockid=d2.fstockid
inner join t_bd_material m3 on d2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = d1.FCUSTID
where d1.FSTOCKORGID = {3} and d1.FDATE >= to_Date ('{0}','yyyy/MM/dd') and d1.FDATE <= to_Date('{1}','yyyy/MM/dd')
and （d1.FBILLTYPEID ='5d9748dd76f550' or d1.FBILLTYPEID='5d9748bd76f4ca')
and d2.FSTOCKID = {2} /*发货仓库=发出仓库*/
and ( m3.fnumber='07040004' and m3.fuseorgid={3})
and d1.FDEPTID = {5}
and d1.FCUSTID = null
and d1.F_PAEZ_LINE = {6}
group by m4.fname )
group by F_PAEZ_XL
", lastString, selectString, outStockId, nowOrgId, tempTable6, FxzId, lineId);
DBUtils.Execute(base.Context, sql3BX);
#endregion
#region 前期库存，前期发出-前期返箱-前期市场丢货
//绿箱前期库存
string sql4LX = string.Format(@"/*dialect*/
insert into {4}
select
0 seq,
F_PAEZ_XL,
sum(F_PAEZ_LXQQKC) F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select F_PAEZ_XL,sum(F_PAEZ_LXQQKC) F_PAEZ_LXQQKC from (
select /*销售出库单KA客户*/ m4.fname F_PAEZ_XL,sum(t2.FREALQTY) F_PAEZ_LXQQKC
from T_SAL_OUTSTOCK t1
inner join T_SAL_OUTSTOCKENTRY t2 on t1.fid=t2.fid
inner join t_bd_material m3 on t2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = t1.fcustomerid
where t1.FSALEORGID = {3} and t1.FDATE < to_Date ('{0}','yyyy/MM/dd')
and t2.FSTOCKID = {2}
and ( m3.fnumber='07040000' and m3.fuseorgid={3})
and t1.FSALEDEPTID = {5}
and t1.FLINE = {6}
group by t1.FCUSTOMERID,t2.FMATERIALID,m4.fname
union all
select/*销售退货（KA客户）*/ m4.fname as F_PAEZ_XL,-sum(s2.FREALQTY) as F_PAEZ_LXQQKC
from T_SAL_RETURNSTOCK s1
inner join T_SAL_RETURNSTOCKENTRY s2 on s1.fid=s2.fid
inner join t_bd_material m3 on s2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = s1.fretcustid
where s1.FSALEORGID = {3} and s1.FDATE < to_Date ('{0}','yyyy/MM/dd')
and s2.FSTOCKID = {2}
and s1.FRETURNTYPE0 = 2
and s1.FSALEDEPTID = {5}
and s1.FLINE = {6}
and ( m3.fnumber='07040000' and m3.fuseorgid={3})
group by m4.fname
union all
select/*其他出库单(KA客户丢箱)*/ m4.fname as F_PAEZ_XL,sum(d2.FQTY) as F_PAEZ_LXQQKC
from T_STK_MISDELIVERY d1
inner join T_STK_MISDELIVERYENTRY d2 on d2.fid = d1.fid
inner join t_bd_stock d3 on d3.fstockid=d2.fstockid
inner join t_bd_material m3 on d2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = d1.FCUSTID
where d1.FSTOCKORGID = {3} and d1.FDATE < to_Date ('{0}','yyyy/MM/dd')
and （d1.FBILLTYPEID ='5d9748dd76f550' or d1.FBILLTYPEID='5d9748bd76f4ca')
and d2.FSTOCKID = {2} /*发货仓库=发出仓库*/
and ( m3.fnumber='07040000' and m3.fuseorgid={3})
and d1.FDEPTID = {5}
and d1.FCUSTID <> null
and d1.F_PAEZ_LINE = {6}
group by m4.fname
) group by F_PAEZ_XL
)group by F_PAEZ_XL
", lastString, null, outStockId, nowOrgId, tempTable7, FxzId, lineId);
DBUtils.Execute(this.Context, sql4LX);
//白箱前期库存
string sql4BX = string.Format(@"/*dialect*/
insert into {4}
select
0 seq,
F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
0 F_PAEZ_LXBQKC,
sum(F_PAEZ_BXQQKC) F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
0 F_PAEZ_BXBQKC from (
select F_PAEZ_XL,sum(F_PAEZ_BXQQKC) F_PAEZ_BXQQKC from (
select /*销售出库单KA客户*/ m4.fname F_PAEZ_XL,sum(t2.FREALQTY) F_PAEZ_BXQQKC
from T_SAL_OUTSTOCK t1
inner join T_SAL_OUTSTOCKENTRY t2 on t1.fid=t2.fid
inner join t_bd_material m3 on t2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = t1.fcustomerid
where t1.FSALEORGID = {3} and t1.FDATE < to_Date ('{0}','yyyy/MM/dd')
and t2.FSTOCKID = {2}
and ( m3.fnumber='07040004' and m3.fuseorgid={3})
and t1.FSALEDEPTID = {5}
and t1.FLINE = {6}
group by t1.FCUSTOMERID,t2.FMATERIALID,m4.fname
union all
select/*销售退货（KA客户）*/ m4.fname as F_PAEZ_XL,-sum(s2.FREALQTY) as F_PAEZ_BXQQKC
from T_SAL_RETURNSTOCK s1
inner join T_SAL_RETURNSTOCKENTRY s2 on s1.fid=s2.fid
inner join t_bd_material m3 on s2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = s1.fretcustid
where s1.FSALEORGID = {3} and s1.FDATE < to_Date ('{0}','yyyy/MM/dd')
and s2.FSTOCKID = {2}
and s1.FRETURNTYPE0 = 2
and s1.FSALEDEPTID = {5}
and s1.FLINE = {6}
and ( m3.fnumber='07040004' and m3.fuseorgid={3})
group by m4.fname
union all
select/*其他出库单(KA客户丢箱)*/ m4.fname as F_PAEZ_XL,-sum(d2.FQTY) as F_PAEZ_BXQQKC
from T_STK_MISDELIVERY d1
inner join T_STK_MISDELIVERYENTRY d2 on d2.fid = d1.fid
inner join t_bd_stock d3 on d3.fstockid=d2.fstockid
inner join t_bd_material m3 on d2.fmaterialid = m3.fmaterialid
inner join t_bd_customer_l m4 on m4.fcustid = d1.FCUSTID
where d1.FSTOCKORGID = {3} and d1.FDATE < to_Date ('{0}','yyyy/MM/dd')
and （d1.FBILLTYPEID ='5d9748dd76f550' or d1.FBILLTYPEID='5d9748bd76f4ca')
and d2.FSTOCKID = {2} /*发货仓库=发出仓库*/
and ( m3.fnumber='07040004' and m3.fuseorgid={3})
and d1.FDEPTID = {5}
and d1.FCUSTID <> null
and d1.F_PAEZ_LINE = {6}
group by m4.fname
) group by F_PAEZ_XL
)group by F_PAEZ_XL
", lastString, null, outStockId, nowOrgId, tempTable8, FxzId, lineId);
DBUtils.Execute(this.Context, sql4BX);
#endregion
#region 本期库存，前期库存+线路发箱-返线箱数-丢箱数
string sql5LX = string.Format(@" /*dialect*/
insert into {0}
select
0 seq,
finall.F_PAEZ_XL F_PAEZ_XL,
0 F_PAEZ_LXQQKC,
0 F_PAEZ_LXFXZFX,
0 F_PAEZ_LXFZXS,
0 F_PAEZ_LXDXS,
sum(finall.F_PAEZ_LXQQKC+finall.F_PAEZ_LXFXZFX-finall.F_PAEZ_LXFZXS-finall.F_PAEZ_LXDXS) F_PAEZ_LXBQKC,
0 F_PAEZ_BXQQKC,
0 F_PAEZ_BXFXZFX,
0 F_PAEZ_BXFZXS,
0 F_PAEZ_BXDXS,
sum(finall.F_PAEZ_BXQQKC+finall.F_PAEZ_BXFXZFX-finall.F_PAEZ_BXFZXS-finall.F_PAEZ_BXDXS) F_PAEZ_BXBQKC
from (
select * from {1}
union
select * from {2}
union
select * from {3}
union
select * from {4}
union
select * from {5}
union
select * from {6}
union
select * from {7}
union
select * from {8}
)finall group by finall.F_PAEZ_XL", tempTable9, tempTable1, tempTable2, tempTable3, tempTable4, tempTable5, tempTable6, tempTable7, tempTable8);
DBUtils.Execute(this.Context, sql5LX);
#endregion
#region 所有数据插入到基表中
string strSql = string.Format(@"insert into {0}
select 0 seq,
F_PAEZ_XL,
sum(finall.F_PAEZ_LXQQKC) F_PAEZ_LXQQKC,
sum(finall.F_PAEZ_LXFXZFX) F_PAEZ_LXFXZFX,
sum(finall.F_PAEZ_LXFZXS) F_PAEZ_LXFZXS,
sum(finall.F_PAEZ_LXDXS) F_PAEZ_LXDXS,
sum(finall.F_PAEZ_LXBQKC) F_PAEZ_LXBQKC,
sum(finall.F_PAEZ_BXQQKC) F_PAEZ_BXQQKC,
sum(finall.F_PAEZ_BXFXZFX) F_PAEZ_BXFXZFX,
sum(finall.F_PAEZ_BXFZXS) F_PAEZ_BXFZXS,
sum(finall.F_PAEZ_BXDXS) F_PAEZ_BXDXS,
sum(finall.F_PAEZ_BXBQKC) F_PAEZ_BXBQKC
from (
select A.* from {1} A
union
select B.* from {2} B
union
select C.* from {3} C
union
select D.* from {4} D
union
select E.* from {5} E
union
select F.* from {6} F
union
select G.* from {7} G
union
select H.* from {8} H
union
select I.* from {9} I
) finall group by finall.F_PAEZ_XL
", tempTable, tempTable1, tempTable2, tempTable3, tempTable4, tempTable5, tempTable6, tempTable7, tempTable8, tempTable9);
DBUtils.Execute(this.Context, strSql);
#endregion
}
public override void AfterConvert(Kingdee.BOS.Core.Metadata.ConvertElement.PlugIn.Args.AfterConvertEventArgs e)
{
base.AfterConvert(e);
//获取头数据
var headEntity = e.Result.FindByEntityKey("FBillHead");
//获取元数据 用于获取源单需要转化的动态集合
object[] pkids = sourceDataCollection.Select(p => p["FID"]).Distinct().ToArray();
FormMetadata metadata = MetaDataServiceHelper.Load(this.Context, "PAEZ_ICMO") as FormMetadata;
DynamicObject[] objs = BusinessDataServiceHelper.Load(this.Context, pkids, metadata.BusinessInfo.GetDynamicObjectType());
Entity FEntityWorkSeq = e.TargetBusinessInfo.GetEntity("FEntity");
Entity FentityICMO = e.TargetBusinessInfo.GetEntity("FEntityICMOIN");
DynamicObjectCollection sourceProdPlanCollection = null;
DynamicObjectCollection sourceICMOCollection = null;
foreach (var extendDataEntity in headEntity)
{
DynamicObjectCollection objectProdPlanCollect = extendDataEntity["FEntity"] as DynamicObjectCollection;
DynamicObjectCollection objectMetailICOMCollection = extendDataEntity["PAEZ_SHWorkMaterialIN"] as DynamicObjectCollection;
for (int i = objectProdPlanCollect.Count - 1; i >= 0; i--)
{
objectProdPlanCollect.RemoveAt(i);
}
DynamicObjectCollection objectICMO = null;
foreach (var sourceItem in objs)
{
sourceProdPlanCollection = sourceItem["PAEZ_ICMO_Entry_ProProcedure"] as DynamicObjectCollection;
sourceICMOCollection = sourceItem["PAEZ_ICMO_Entry_ICItem"] as DynamicObjectCollection;
}
foreach (var sourceItem in sourceProdPlanCollection)
{
DynamicObject objectProdPlan = new DynamicObject(FEntityWorkSeq.DynamicObjectType);
objectProdPlan["seq"] = sourceItem["seq"];
objectProdPlan["F_PAEZ_ProduceID_Id"] = sourceItem["F_PAEZ_ProduceID_Id"];
objectProdPlan["F_PAEZ_ProduceID"] = sourceItem["F_PAEZ_ProduceID"];
objectProdPlan["F_PAEZ_Times"] = sourceItem["F_PAEZ_Times"];
objectProdPlan["F_PAEZ_Equipment_Id"] = sourceItem["F_PAEZ_MachineID_Id"];
objectProdPlan["F_PAEZ_Equipment"] = sourceItem["F_PAEZ_MachineID"];
objectProdPlan["F_PAEZ_Rolling"] = sourceItem["F_PAEZ_Rolling"];
objectProdPlan["F_PAEZ_Notes"] = sourceItem["F_PAEZ_Notes"];
objectProdPlanCollect.Add(objectProdPlan);
}
foreach (var sourceItem in sourceICMOCollection)
{
DynamicObject sourceICMO = new DynamicObject(FentityICMO.DynamicObjectType);
sourceICMO["Seq"] = sourceItem["seq"];
sourceICMO["F_PAEZ_Material"] = sourceItem["F_PAEZ_ICItemID"]; //原料代码
sourceICMO["F_PAEZ_Material_Id"] = sourceItem["F_PAEZ_ICItemID_Id"]; //ID
// sourceICMO["F_PAEZ_BaseProperty"] = sourceItem["F_PAEZ_ICItemName"]; //原料名称
// sourceICMO["F_PAEZ_BaseProperty2_Id"] = sourceItem["F_PAEZ_ICItemMaterial"]; //材质
sourceICMO["F_PAEZ_IcItemLevel"] = sourceItem["F_PAEZ_IcItemLevel"]; //等级
sourceICMO["F_PAEZ_Model"] = sourceItem["F_PAEZ_ICItemModel"]; //规格
sourceICMO["F_PAEZ_LotMa"] = sourceItem["F_PAEZ_Lot"]; //批号
sourceICMO["F_PAEZ_ReelItemIN"] = sourceItem["F_PAEZ_ICItemReelNO"]; //卷号
sourceICMO["F_PAEZ_MaWeight"] = sourceItem["F_PAEZ_ICItemWeight"]; //重量
sourceICMO["F_PAEZ_UnitIDMAWeight"] = sourceItem["F_PAEZ_ICItemWeightUNITID"]; //重量单位
sourceICMO["F_PAEZ_QtyMa"] = sourceItem["F_PAEZ_IcItemQty"]; //数量
sourceICMO["F_PAEZ_UnitID1"] = sourceItem["F_PAEZ_ICItemQtyUnitID"]; //数量单位
sourceICMO["F_PAEZ_MaWidth"] = sourceItem["F_PAEZ_ICItemWidth"]; //宽度
sourceICMO["F_PAEZ_MaThickness"] = sourceItem["F_PAEZ_ICItemThickness"]; //厚度
sourceICMO["F_PAEZ_MaLength"] = sourceItem["F_PAEZ_ICItemLength"]; //长度
sourceICMO["F_PAEZ_MaWidthRE"] = sourceItem["F_PAEZ_ICItemRefWidth"]; //参宽
sourceICMO["F_PAEZ_MaThicknessRE"] = sourceItem["F_PAEZ_ICItemRefThickness"]; //參厚
sourceICMO["F_PAEZ_CCONTENTSItemIN"] = sourceItem["F_PAEZ_ICItemCContents"]; //碳含量
sourceICMO["F_PAEZ_CRCONTENTSItemIN"] = sourceItem["F_PAEZ_ICItemCRContents"]; //铬含量
sourceICMO["F_PAEZ_PRODUCTEDGEItemIN"] = sourceItem["F_PAEZ_ICItemEdge"]; //边部状况
// sourceICMO["F_PAEZ_StockLot"] = sourceItem["F_PAEZ_LotStock"]; //库存批次
objectMetailICOMCollection.Add(sourceICMO);
}
}
}
internal DynamicDictionaryMetaObject(Expression parameter, DynamicObject value)
: base(parameter, BindingRestrictions.Empty, value)
{
}
/// <summary>
/// 执行外部模型的操作
/// </summary>
/// <param name="model">外部的模型</param>
/// <param name="operation">外部的模型的操作</param>
/// <param name="arg">参数 arg的Model为源模型</param>
/// <param name="id">外部模型的ID</param>
/// <returns>生成的ObjectProxy</returns>
public IObjectProxy ExcuteOperation(Model model, IOperation operation, IObjectProxy arg, object id)
{
//如果为空，返回默认值
//明天个性
//Set Value 如果为BusinisObject 返回默认值 ~~~ 笨的!!!!
#region 初始化要操作的值,即要返回的值resultProxy
var db = new SqlServer.dbContext((model.SqlCon == null ? model.Module.SqlCon : model.SqlCon), this.ContextFac);
IObjectProxy resultproxy = null;
if (operation == null)
{
return(db.GetDetail(model, id));
}
List <object> invokeparams = new List <object>();
switch (operation.BaseOperationType)
{
case BaseOperationType.Create:
resultproxy = new SqlDataProxy(model, this.ContextFac, LoadType.Null, this.Con);
break;
case BaseOperationType.Delete:
case BaseOperationType.Update:
resultproxy = db.GetDetail(model, id);
break;
default:
return(null);
}
#endregion
foreach (var command in operation.GetCommands().OrderBy(p => p.Index))
{
switch (command.CommandsType)
{
case CommandsType.SetAccess:
resultproxy.NotifyPropertyCanSet(command.Property, System.Convert.ToBoolean(command.Exp));
break;
case CommandsType.SetValue:
resultproxy[command.Property] = GetValue(command.Property, arg, command.Exp);
break;
case CommandsType.ExuteProprtyModelMethod:
DynamicObject proxy = resultproxy[command.Property] as DynamicObject;
object ob1 = new object();
proxy.TryInvokeMember(new ObjectInvokeMemberBinder(command.Exp), new object[] { }
, out ob1);
break;
case CommandsType.ExuteOutModelMethod:
var desmodel = this.ExcuteOperation(
command.ArgModel,
command.ArgModel.Operations.FirstOrDefault(p => p.Name.Trim().ToUpper() == command.Exp.Trim().ToUpper()),
resultproxy,
GetValue(command.Property, resultproxy, command.ArgSourceIdExp));
if (desmodel != null && command.Property != null)
{
resultproxy[command.Property] = GetValue(command.Property, desmodel, command.ArgPropertyExp);
}
break;
case CommandsType.Filter:
CheckCommand(command, resultproxy);
break;
default:
break;
}
}
var db2 = new SqlServer.dbContext((model.SqlCon == null ? model.Module.SqlCon : model.SqlCon), this.ContextFac);
switch (operation.BaseOperationType)
{
case BaseOperationType.Create:
db2.Create(resultproxy);
break;
case BaseOperationType.Delete:
db2.Delete(resultproxy);
break;
case BaseOperationType.Update:
db2.Save(resultproxy);
break;
default:
break;
}
return(resultproxy);
}
//反写报表过滤信息
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_DeptFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"]).Count > 0)
{
StringBuilder deptName = new StringBuilder();
DynamicObjectCollection cols = (DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"];
foreach (DynamicObject dept in cols)
{
String tmpName = Convert.ToString(((DynamicObject)dept["F_QSNC_DeptFilter"])["Name"]);
deptName.Append(tmpName + "; ");
}
result.AddTitle("F_QSNC_Department", deptName.ToString());
}
else
{
result.AddTitle("F_QSNC_Department", "全部");
}
//销售员
if (dyFilter["F_QSNC_SalerFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"]).Count > 0)
{
StringBuilder salerName = new StringBuilder();
DynamicObjectCollection cols = (DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"];
foreach (DynamicObject saler in cols)
{
String tmpName = Convert.ToString(((DynamicObject)saler["F_QSNC_SalerFilter"])["Name"]);
salerName.Append(tmpName + "; ");
}
result.AddTitle("F_QSNC_Saler", salerName.ToString());
}
else
{
result.AddTitle("F_QSNC_Saler", "全部");
}
}
return(result);
}
public void Should_have_type_property_set_to_specified_type()
{
var dynamicObject = new DynamicObject(typeof(CustomClass));
dynamicObject.Type.ToType().ShouldBe(typeof(CustomClass));
}
//执行查询sql 按时间 销售员 销售部门进行过滤
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
base.BuilderReportSqlAndTempTable(filter, tableName);
// 根据当前用户的UserId 查询出其personId
StringBuilder sql0 = new StringBuilder();
sql0.AppendFormat(@"/*dialect*/ SELECT FLINKOBJECT FROM T_SEC_USER WHERE FUSERID = {0} ", this.Context.UserId);
DynamicObjectCollection collection = DBUtils.ExecuteDynamicObject(this.Context, sql0.ToString());
StringBuilder salerLimit = new StringBuilder();
Boolean flag = false;
if (collection.Count > 0)
{
//获取当前用户personId
DynamicObject personIdObj = (DynamicObject)collection[0];
int personId = Convert.ToInt32(personIdObj["FLINKOBJECT"]);
//获取当前用户权限内的销售数据
if (CRMServiceHelper.getSalerPersonids(this.Context, personId) != null)
{
//获取当前用户可见的销售员集合
List <long> salerList = CRMServiceHelper.getSalerPersonids(this.Context, personId);
int len = 0;
flag = true;
if (salerList.Count >= 1)
{
salerLimit.Append(" IN ( ");
}
foreach (long salerId in salerList)
{
len++;
if (len == salerList.Count)
{
salerLimit.Append(" " + salerId + " ) ");
}
else
{
salerLimit.Append(" " + salerId + ", ");
}
}
}
}
//生成中间临时表
IDBService dbservice = ServiceHelper.GetService <IDBService>();
materialRptTableNames = dbservice.CreateTemporaryTableName(this.Context, 1);
String tmpTable1 = materialRptTableNames[0];
//过滤条件：起始日期/截至日期/部门/业务员
DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
String startDate = ""; //起始日期
String endDate = ""; //截至日期
//销售员
StringBuilder salerSql = new StringBuilder();
if (dyFilter["F_QSNC_SalerFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols1 = (DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"];
int salerNum = 0;
if (cols1.Count >= 1)
{
salerSql.Append(" IN (");
}
foreach (DynamicObject saler in cols1)
{
String salerNumber = Convert.ToString(((DynamicObject)saler["F_QSNC_SalerFilter"])["Number"]);
salerNum++;
if (cols1.Count == salerNum)
{
salerSql.Append("'" + salerNumber + "') ");
}
else
{
salerSql.Append("'" + salerNumber + "', ");
}
}
}
//部门
StringBuilder deptSql = new StringBuilder();
if (dyFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"]).Count > 0)
{
//获取到多选基础资料中所有选中项
DynamicObjectCollection cols2 = (DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"];
int deptNum = 0;
if (cols2.Count >= 1)
{
deptSql.Append(" IN (");
}
foreach (DynamicObject dept in cols2)
{
String deptNumber = Convert.ToString(((DynamicObject)dept["F_QSNC_DeptFilter"])["Number"]);
deptNum++;
if (cols2.Count == deptNum)
{
deptSql.Append("'" + deptNumber + "') ");
}
else
{
deptSql.Append("'" + deptNumber + "', ");
}
}
}
//报表sql
StringBuilder sql1 = new StringBuilder();
sql1.AppendLine(@"/*dialect*/ SELECT DEPTNAME, SALERNAME, QUOTA, COMPLETEAMOUNT, ");
sql1.AppendFormat(" CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar)+' %' AS COMPLETERATE INTO {0} ", tmpTable1);
sql1.AppendLine(" FROM ");
sql1.AppendLine(" (SELECT DEPTL.FNAME AS DEPTNAME, ");
sql1.AppendLine(" EMPL.FNAME AS SALERNAME, ");
sql1.AppendLine(" (SELECT F_PEJK_CONTRACTQUNTA FROM PEJK_SALERQUNTAENTRY WHERE F_PEJK_SALER = RESOLVESALER.F_PEJK_SALER) AS QUOTA, ");
sql1.AppendLine(" SUM(F_PEJK_DETAILLAMOUNT) AS COMPLETEAMOUNT ");
sql1.AppendLine(" FROM PEJK_SALECONTRACTENTRY RESOLVESALER ");
sql1.AppendLine(" LEFT JOIN PEJK_SALECONTRACTS RESOLVEBASIC ON RESOLVESALER.FID = RESOLVEBASIC.FID ");
sql1.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FID = RESOLVESALER.F_PEJK_SALER ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT_L DEPTL ON DEPTL.FDEPTID = SALESMAN.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT DEPT ON DEPTL.FDEPTID = DEPT.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_STAFF STAFF ON STAFF.FSTAFFID = SALESMAN.FSTAFFID ");
sql1.AppendLine(" LEFT JOIN T_HR_EMPINFO_L EMPL ON EMPL.FID = STAFF.FEMPINFOID ");
sql1.AppendLine(" WHERE DEPTL.FLOCALEID = 2052 AND EMPL.FLOCALEID = 2052 ");
//判断起始日期是否有效
if (dyFilter["F_QSNC_StartDateFilter"] != null)
{
startDate = Convert.ToDateTime(dyFilter["F_QSNC_StartDateFilter"]).ToString("yyyy-MM-dd 00:00:00");
sql1.AppendFormat(" AND RESOLVEBASIC.F_PEJK_DATE >= '{0}' ", startDate);
}
//判断截止日期是否有效
if (dyFilter["F_QSNC_EndDateFilter"] != null)
{
endDate = Convert.ToDateTime(dyFilter["F_QSNC_EndDateFilter"]).ToString("yyyy-MM-dd 23:59:59");
sql1.AppendFormat(" AND RESOLVEBASIC.F_PEJK_DATE <= '{0}' ", endDate);
}
//判断销售员条件
if (dyFilter["F_QSNC_SalerFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_SalerFilter"]).Count > 0)
{
sql1.AppendLine(" and STAFF.FNUMBER").Append(salerSql);
}
//销售员数据隔离
if (flag)
{
sql1.AppendLine(" and SALESMAN.FID ").Append(salerLimit);
}
//判断销售部门条件
if (dyFilter["F_QSNC_DeptFilter"] != null && ((DynamicObjectCollection)dyFilter["F_QSNC_DeptFilter"]).Count > 0)
{
sql1.AppendLine(" AND DEPT.FNUMBER").Append(deptSql);
}
sql1.AppendLine(" GROUP BY F_PEJK_SALER, EMPL.FNAME, DEPTL.FNAME) TMP ");
DBUtils.ExecuteDynamicObject(this.Context, sql1.ToString());
// tmpTable1 : DEPTNAME SALERNAME QUOTA COMPLETEAMOUNT COMPLETERATE
StringBuilder sql2 = new StringBuilder();
sql2.AppendFormat(@"/*dialect*/INSERT INTO {0} SELECT '合计' , '', QUOTA, COMPLETEAMOUNT, ", tmpTable1);
sql2.AppendLine(" CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar) + ' %' AS COMPLETERATE ");
sql2.AppendLine(" FROM ");
sql2.AppendFormat(" (SELECT SUM(QUOTA) AS QUOTA, SUM(COMPLETEAMOUNT) AS COMPLETEAMOUNT FROM {0}) TMP ", tmpTable1);
DBUtils.ExecuteDynamicObject(this.Context, sql2.ToString());
StringBuilder sql3 = new StringBuilder();
sql3.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY DEPTNAME) AS FSeq, DEPTNAME, SALERNAME, CONVERT(FLOAT, ROUND(QUOTA, 2)) AS TOTALQUOTA, CONVERT(FLOAT, ROUND(COMPLETEAMOUNT, 2)) AS TOTALAMOUNT, COMPLETERATE INTO {0} ", tableName);
sql3.AppendFormat(" FROM {0} ", tmpTable1);
DBUtils.ExecuteDynamicObject(this.Context, sql3.ToString());
}
/// <summary>
/// 事务后事件
/// </summary>
/// <param name="e"></param>
public override void BeginOperationTransaction(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.BeginOperationTransactionArgs e)
{
base.BeginOperationTransaction(e);
if (e.DataEntitys == null)
{
return;
}
var billGroups = e.DataEntitys;
//List<string> sql = new List<string>();
foreach (var billGroup in billGroups)
{
bool flag = false;
int Id = Convert.ToInt32(billGroup["Id"]);
DateTime date = Convert.ToDateTime(billGroup["Date"]);
DynamicObjectCollection dynamicOs = billGroup["SaleOrderEntry"] as DynamicObjectCollection;//分录明细
foreach (var dynamicO in dynamicOs)
{
decimal taxPrice = Convert.ToDecimal(dynamicO["TaxPrice"]); //含税单价
int auxPropId = Convert.ToInt32(dynamicO["AuxPropId_Id"]); //辅助属性
int materialId = Convert.ToInt32(dynamicO["MaterialId_Id"]); //当前物料
DynamicObject PriceListEntry = dynamicO["PriceListEntry"] as DynamicObject; //行价目表
if (PriceListEntry == null)
{
continue;
}
DynamicObjectCollection salPriceListtrys = PriceListEntry["SAL_PRICELISTENTRY"] as DynamicObjectCollection; //价目表明细
var priceList = salPriceListtrys.Where(f => Convert.ToInt32(f["MaterialId_Id"]) == materialId && Convert.ToDateTime(f["EffectiveDate"]) <= date && Convert.ToDateTime(f["ExpiryDate"]) > date); //物料、生效（失效）日期判断,
if (priceList.Count() == 1)
{
//string sqlUpdate = string.Format("update T_SAL_ORDER set FJNPRICECHANGE='1' where FID={0}", Id);
//sql.Add(sqlUpdate);
foreach (var obj in priceList)
{
//当分录中存在含税单价低于最低限价时，限价标识自动勾选上
if (taxPrice < Convert.ToDecimal(obj["DownPrice"]))
{
flag = true;
break;
}
}
}
if (priceList.Count() > 1)
{
//辅助属性判断
var priceEntry = priceList.Where(f => Convert.ToInt32(f["AuxPropId_Id"]) == auxPropId);
if (priceEntry.Count() == 1)
{
//string sqlUpdate = string.Format("update T_SAL_ORDER set FJNPRICECHANGE='1' where FID={0}", Id);
//sql.Add(sqlUpdate);
foreach (var obj in priceEntry)
{
//当分录中存在含税单价低于最低限价时，限价标识自动勾选上
if (taxPrice < Convert.ToDecimal(obj["DownPrice"]))
{
flag = true;
break;
}
}
}
}
}
if (flag)
{
billGroup["FJNPRICECHANGE"] = true;
//JN.K3.YDL.Core.AppServiceContext.SaveService.Save(this.Context, new DynamicObject[] { billGroup });//保存数据包
}
}
//if (sql.Count > 0)
//{
// DBUtils.ExecuteBatch (this.Context, sql,50);//批量处理sql，50表示一次提交50条
//}
}
public override IEnumerable <AbsSynchroDataInfo> GetK3Datas(Context ctx, List <DynamicObject> objects, ref HttpResponseResult result)
{
List <AbsDataInfo> reFunds = null;
AbsDataInfo reFund = null;
result = new HttpResponseResult();
result.Success = true;
if (objects != null && objects.Count > 0)
{
reFunds = new List <AbsDataInfo>();
foreach (var obj in objects)
{
if (obj != null)
{
string reFundMethod = SQLUtils.GetReFundMethod(ctx, obj, "F_HS_RefundMethod_Id");
bool isSyn = Convert.ToBoolean(SQLUtils.GetFieldValue(obj, "F_HS_YNSync"));
DynamicObject org = obj["SALEORGID"] as DynamicObject;
string orgNo = SQLUtils.GetFieldValue(org, "Number");
string custNo = SQLUtils.GetCustomerNo(ctx, obj, "F_HS_B2CCUSTID_Id");
if (!isSyn)
{
if (!string.IsNullOrWhiteSpace(reFundMethod))
{
List <ReFundBillEntry> entrys = GetReFundEntry(ctx, obj);
if (reFundMethod.CompareTo("TKDKHYE") == 0 || reFundMethod.CompareTo("TJF") == 0)
{
if (entrys != null && entrys.Count > 0)
{
foreach (var entry in entrys)
{
if (entry != null)
{
reFund = new AbsDataInfo(this.DataType);
reFund.FSaleOrgId = orgNo;
reFund.SrcNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + entry.FEntryId;
reFund.FBillNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + entry.FEntryId;
reFund.FEntryId = entry.FEntryId;
reFund.F_HS_RefundMethod = reFundMethod;
reFund.F_HS_RefundType = SQLUtils.GetReFundType(ctx, obj, "F_HS_RefundType_Id");
reFund.F_HS_B2CCustId = custNo;
reFund.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "DATE")));
reFund.FSettleCurrId = SQLUtils.GetSettleCurrNo(ctx, obj, "CURRENCYID_Id");
reFund.F_HS_RateToUSA = GetExchangeRate(ctx, reFund.FSettleCurrId);
reFund.FSettleTypeId = entry.FSettleTypeId;
reFund.FBusinessTime = SQLUtils.GetFieldValue(obj, "ApproveDate");
if (reFund.F_HS_RefundMethod.CompareTo("TJF") == 0)
{
reFund.F_HS_BalanceRechargeUSD = 0;
}
else if (reFund.F_HS_RefundMethod.CompareTo("TKDKHYE") == 0)
{
reFund.F_HS_BalanceRechargeUSD = entry.F_HS_BalanceRechargeUSD;
reFund.F_HS_CreditLineRechargeUSD = entry.F_HS_CreditLineRechargeUSD;
if (reFund.F_HS_CreditLineRechargeUSD < 0)
{
reFund.F_HS_BalanceRechargeUSD = entry.FRealReFundAmountFor_D / reFund.F_HS_RateToUSA;
}
}
reFunds.Add(reFund);
}
}
}
}
if (reFundMethod.CompareTo("JYE01") == 0)
{
decimal balance = LogHelper.GetCustBalance(ctx, custNo, orgNo);
decimal amount = 0;
int count = 0;
if (entrys != null && entrys.Count > 0)
{
foreach (var entry in entrys)
{
count++;
amount += entry.FRealReFundAmountFor_D;
if (count < entrys.Count)
{
continue;
}
if (balance > 0 && amount <= balance)
{
foreach (var en in entrys)
{
if (entry != null)
{
reFund = new AbsDataInfo(this.DataType);
reFund.FSaleOrgId = orgNo;
reFund.SrcNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + en.FEntryId;
reFund.FBillNo = SQLUtils.GetFieldValue(obj, "BILLNo") + "_" + en.FEntryId;
reFund.FEntryId = en.FEntryId;
reFund.F_HS_RefundMethod = reFundMethod;
reFund.F_HS_RefundType = SQLUtils.GetReFundType(ctx, obj, "F_HS_RefundType_Id");
reFund.F_HS_B2CCustId = custNo;
reFund.FDate = TimeHelper.GetTimeStamp(Convert.ToDateTime(SQLUtils.GetFieldValue(obj, "DATE")));
reFund.FSettleCurrId = SQLUtils.GetSettleCurrNo(ctx, obj, "CURRENCYID_Id");
reFund.F_HS_RateToUSA = GetExchangeRate(ctx, reFund.FSettleCurrId);
reFund.FSettleTypeId = en.FSettleTypeId;
reFund.F_HS_BalanceRechargeUSD = -en.FRealReFundAmountFor_D / GetExchangeRate(ctx, "USD");
reFunds.Add(reFund);
}
}
}
}
}
}
}
}
else
{
result.Success = false;
result.Message = "收款退款单已同步" + Environment.NewLine;
LogUtils.WriteSynchroLog(ctx, SynchroDataType.ReFundBill, result.Message);
}
}
}
}
return(reFunds);
}