public List <ListInfo> GetListInfos()
{
if (!string.IsNullOrWhiteSpace(GetSql()))
{
DynamicObjectCollection collection = DBServiceHelper.ExecuteDynamicObject(this.K3CloudContext, GetSql());
List <ListInfo> infos = new List <ListInfo>();
ListInfo info = default(ListInfo);
if (collection != null)
{
if (collection.Count > 0)
{
foreach (var item in collection)
{
info = new ListInfo();
info.SrcNo = SQLUtils.GetFieldValue(item, "F_HS_ListID");
info.F_HS_ListID = SQLUtils.GetFieldValue(item, "F_HS_ListID");
info.F_HS_ListName = SQLUtils.GetFieldValue(item, "F_HS_LISTNAME");
//info.F_HS_BatteryMod = SQLUtils.GetFieldValue(item, "F_HS_BatteryMod");
//info.F_HS_IsPuHuo = SQLUtils.GetFieldValue(item, "F_HS_IsPuHuo");
//info.F_HS_IsOil = SQLUtils.GetFieldValue(item, "F_HS_IsOil");
infos.Add(info);
}
}
return(infos);
}
}
return(default(List <ListInfo>));
}
/// <summary>
/// 获取库存数据
/// </summary>
/// <returns></returns>
public List <InventoryInfo> GetInventorys()
{
string sql = GetSql();
if (!string.IsNullOrWhiteSpace(sql))
{
DynamicObjectCollection collection = DBServiceHelper.ExecuteDynamicObject(this.K3CloudContext, sql);
InventoryInfo inventory = default(InventoryInfo);
List <InventoryInfo> inventories = new List <InventoryInfo>();
if (collection != null)
{
if (collection.Count > 0)
{
foreach (var item in collection)
{
inventory = new InventoryInfo();
inventory.StockId = SQLUtils.GetFieldValue(item, "FSTOCKID");
inventory.SrcNo = SQLUtils.GetFieldValue(item, "FNUMBER");
inventory.FixId = SQLUtils.GetFieldValue(item, "FNUMBER");
inventory.Quantity = Convert.ToDouble(SQLUtils.GetFieldValue(item, "availableQOH"));
inventories.Add(inventory);
}
}
return(inventories);
}
}
return(default(List <InventoryInfo>));
}
/// <summary>
/// 同步通过Excel导入数据库的客户资料
/// </summary>
/// <param name="ctx"></param>
/// <returns></returns>
public static List <K3CustomerInfo> DownLoadCustomersByExcel(Context ctx)
{
string sql = string.Format(@"/*dialect*/ select * from customers a where not exists(select FNUMBER from T_BD_CUSTOMER where FNUMBER = a.customers_id) and a.customers_email_address is not null ");
DynamicObjectCollection coll = DBServiceHelper.ExecuteDynamicObject(ctx, sql);
List <K3CustomerInfo> custs = BuildSynObjByCollection(ctx, coll);
return(custs);
}
/// <summary>
/// 获取表中字段的值
/// </summary>
/// <param name="ctx"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DynamicObjectCollection GetObjects(Context ctx, string sql)
{
if (!string.IsNullOrWhiteSpace(sql))
{
return(DBServiceHelper.ExecuteDynamicObject(ctx, sql));
}
return(null);
}
/// <summary>
/// 获取采购目录表
/// </summary>
/// <param name="ctx"></param>
/// <param name="materilId"></param>
/// <param name="supplierId"></param>
/// <param name="applicationDate"></param>
/// <returns></returns>
public DynamicObjectCollection GetPriceListId(Context ctx, long materilId, long supplierId, DateTime applicationDate)
{
string strSQL = string.Format(@"select top 1 t2.FID,T2.FPRICE,T2.FTAXPRICE from t_PUR_PriceListEntry t2
inner join t_PUR_PriceList t3 on t3.FID=t2.FID where t2.FMATERIALID={0}
and t3.FEFFECTIVEDATE<= '{1}' and t3.FEXPIRYDATE>='{1}' and t3.FSUPPLIERID={2}
and t3.FDOCUMENTSTATUS='C' and t3.FForbidStatus='A' and t2.FDisableStatus='B' order by t2.FEFFECTIVEDATE DESC ", materilId, applicationDate, supplierId);
return(DBServiceHelper.ExecuteDynamicObject(ctx, strSQL));
}
/// <summary>
/// 返回供应商数据
/// </summary>
/// <returns>返回服务结果。</returns>
public ServiceResult ExecuteService()
{
var result = new ServiceResult <List <JSONObject> >();
var ctx = this.KDContext.Session.AppContext;
// 检查上下文对象
if (this.IsContextExpired(result))
{
return(result);
}
// 检查传入参数
//获取相关信息
try
{
StringBuilder sql_builder = new StringBuilder("/*dialect*/ select v.FID ,v.FNUMBER,v1.FNAME,v.fformid as FFORMID from dbo.BAH_V_BD_CONTACT v ");
sql_builder.Append(" inner join dbo.BAH_V_BD_CONTACT_L v1 on v.FID = v1.FID");
sql_builder.Append(" where v.FDOCUMENTSTATUS = 'C' and v.FFORBIDSTATUS = 'A' AND V1.FLOCALEID = 2052");
sql_builder.Append(" order by v.FNUMBER ");
DynamicObjectCollection query_result = DBServiceHelper.ExecuteDynamicObject(ctx, sql_builder.ToString(), null, null, System.Data.CommandType.Text);
if (query_result.Count == 0)
{
result.Code = (int)ResultCode.Fail;
result.Message = "未检索到对应信息！";
}
else
{
result.Code = (int)ResultCode.Success;
JSONObject Finaldata = new JSONObject();
List <JSONObject> return_data = new List <JSONObject>();
//构建JSONObject数据
foreach (DynamicObject data_obj in query_result)
{
JSONObject data = new JSONObject();
data.Add("FID", data_obj["FID"]);
data.Add("FNUMBER", data_obj["FNUMBER"]);
data.Add("FNAME", data_obj["FNAME"]);
data.Add("FFORMID", data_obj["FFORMID"]);
return_data.Add(data);
}
Finaldata.Add("Contacter", return_data);
//返回数据
result.Code = (int)ResultCode.Success;
result.Data = return_data;
result.Message = "成功返回数据！";
}
}
catch (Exception ex)
{
result.Code = (int)ResultCode.Fail;
result.Message = ex.Message;
}
return(result);
}
public static string GetSeeedSystemProfile(Context ctx, string key)
{
var sql = string.Format(@"select FKEY,FVALUE from T_BAS_SYSTEMPROFILE
Where FCATEGORY='Hands' And FKEY = '{0}' ", key);
var data = DBServiceHelper.ExecuteDynamicObject(ctx, sql);
if (data == null || data.Count == 0)
{
return("");
}
return(Convert.ToString(data[0]["FVALUE"]));
}
/// <summary>
/// 获取价目表信息
/// </summary>
/// <param name="ctx"></param>
/// <param name="materilId"></param>
/// <param name="supplierId"></param>
/// <returns></returns>
public DynamicObjectCollection GetPriceListInfo(Context ctx, long materilId, long supplierId = 0)
{
if (materilId <= 0)
{
return(null);
}
var sql = string.Format(@"SELECT TOP 1 T2.FPRICE,T2.FTAXPRICE,T2.FTAXRATE FROM T_PUR_PRICELISTENTRY T2 INNER JOIN T_PUR_PRICELIST T3 ON T3.FID=T2.FID where t2.FMATERIALID={0} ", materilId);
if (supplierId > 0)
{
sql += string.Format(" AND FSUPPLIERID={0}", supplierId);
}
sql += " order by t2.FEFFECTIVEDATE DESC";
return(DBServiceHelper.ExecuteDynamicObject(ctx, sql));
}
public static Dictionary <string, string> getToOrgID(Context ctx, List <string> numbers, string model)
{
if (SupplierSQLObject.Supplier.Equals(model))
{
SQL4currOrg = "select FSUPPLIERID,FUSEORGID from t_BD_Supplier where fnumber = '{0}'";
}
else
{
SQL4currOrg = "select FCUSTID,FUSEORGID from T_BD_CUSTOMER where fnumber = '{0}'";
}
string SQL4AllOrg = "select FORGID from T_ORG_Organizations";
string currOrgID = "";
// string[] fmaterID = new string[] { };
string fmaterID = "";
List <string> orgs = new List <string>();
foreach (string number in numbers)
{
string executeSQL = string.Format(SQL4currOrg, number);
DynamicObjectCollection ds = DBServiceHelper.ExecuteDynamicObject(ctx, executeSQL);
foreach (DynamicObject doname in ds)
{
currOrgID = doname["FUSEORGID"].ToString();
if (SupplierSQLObject.Supplier.Equals(model))
{
fmaterID = doname["FSUPPLIERID"].ToString();
}
else
{
fmaterID = doname["FCUSTID"].ToString();
}
}
DynamicObjectCollection dynamicObjectCollection = DBUtils.ExecuteDynamicObject(ctx, SQL4AllOrg);
foreach (DynamicObject allorg in dynamicObjectCollection)
{
orgs.Add(allorg["FORGID"].ToString());
}
orgs.Remove(currOrgID);
Dictionary <string, string> outResult = new Dictionary <string, string>();
outResult.Add(fmaterID, string.Join(",", orgs.ToArray()));
return(outResult);
}
return(null);
}
/// <summary>
/// 界面数据发生变化之后，需要处理的功能
/// </summary>
/// <param name="e"></param>
public override void DataChanged(DataChangedEventArgs e)
{
switch (e.Field.Key.ToUpperInvariant())
{
case "FDELIVERYNOTICE":
if (this.Model.GetValue("FDeliveryNotice", e.Row) is DynamicObject notice)
{
DynamicObject cust_no = notice["CustId"] as DynamicObject;
this.Model.SetValue("FCustNo", cust_no["Number"].ToString(), e.Row);
DynamicObject mat_no = notice["MaterialID"] as DynamicObject;
this.Model.SetValue("FMatNo", mat_no["Number"].ToString(), e.Row);
DynamicObject currency = notice["CurrencyId"] as DynamicObject;
this.Model.SetItemValueByNumber("FOrderCurrency", currency["Number"].ToString(), e.Row);
this.Model.SetItemValueByNumber("FCurrencyId", currency["Number"].ToString(), e.Row);
this.Model.SetValue("FOrderPrice", notice["TaxPrice"], e.Row);
DynamicObjectCollection material = DBServiceHelper.ExecuteDynamicObject(this.Context, String.Format("select FBASEUNITID from t_bd_materialbase where FMATERIALID={0}", mat_no["Id"]));
this.Model.SetItemValueByID("FUnitID", material[0]["FBASEUNITID"], e.Row);
}
else
{
this.Model.SetValue("FCustNo", null, e.Row);
this.Model.SetValue("FMatNo", null, e.Row);
this.Model.SetValue("FOrderCurrency", null, e.Row);
this.Model.SetValue("FCurrencyId", null, e.Row);
this.Model.SetValue("FOrderPrice", null, e.Row);
this.Model.SetValue("FUnitID", null, e.Row);
}
break;
case "FPRICE":
case "FQTY":
decimal price, qty;
Decimal.TryParse(this.Model.GetValue("FPRICE", e.Row).ToString(), out price);
Decimal.TryParse(this.Model.GetValue("FQTY", e.Row).ToString(), out qty);
this.Model.SetValue("FAmount", price * qty, e.Row);
break;
case "FCurrencyId":
break;
default:
break;
}
}
//internal List<DynamicStockObject> getStockID(BOS.Context context, int id)
//{
// DynamicObjectCollection whCol = null;
// string cknameSql = " select a.stockid from T_BD_STOCK a inner join T_BD_STOCKGROUP b on b.FID=a.FGROUP WHERE b.fnumber = '02' order by a.fnumber ";
// whCol = DBServiceHelper.ExecuteDynamicObject(context, cknameSql) as DynamicObjectCollection;
// foreach (DynamicObject ckname in whCol)
// {
// fldKeyList.Add(Convert.ToString(ckname["STOCKID"]));
// }
// return fldKeyList;
// throw new NotImplementedException();
//}
internal List <DynamicStockObject> getStockID(BOS.Context context)
{
DynamicObjectCollection whCol = null;
string cknameSql = " SELECT a.FSTOCKID AS STOCKID, a.FNUMBER AS FNUMBER ,c.FNAME AS FNAME FROM T_BD_STOCK a INNER JOIN T_BD_STOCKGROUP b ON b.FID = a.FGROUP " +
"LEFT JOIN T_BD_STOCK_L C ON C.FSTOCKID = a.FSTOCKID WHERE b.fnumber = '02' ORDER BY a.fnumber";
whCol = DBServiceHelper.ExecuteDynamicObject(context, cknameSql) as DynamicObjectCollection;
foreach (DynamicObject ckname in whCol)
{
DynamicStockObject stockObject = new DynamicStockObject();
stockObject.stockid = Convert.ToInt32(ckname["STOCKID"]);
stockObject.ckName = Convert.ToString(ckname["FNUMBER"]);
stockObject.StockName = Convert.ToString(ckname["FNAME"]);
fldKeyList.Add(stockObject);
}
return(fldKeyList);
throw new NotImplementedException();
}
/// <summary>
/// 获取表中某个指定字段的值
/// </summary>
/// <param name="ctx"></param>
/// <param name="sql"></param>
/// <param name="selectItem"></param>
/// <returns></returns>
public static object GetObject(Context ctx, string sql, string selectItem)
{
if (ctx != null)
{
DynamicObjectCollection coll = DBServiceHelper.ExecuteDynamicObject(ctx, sql);
if (coll.Count > 0)
{
foreach (var item in coll)
{
if (item != null)
{
return(item[selectItem]);
}
}
}
}
return(default(object));
}
//创建销售预测变更单
private void CreateForecastChangeBill()
{
//分组集合
List <string> lstGroups = new List <string>();
string sGroup = string.Empty;
foreach (DynamicObject lstEntry in lstEntrys)
{
sGroup = Convert.ToString(lstEntry["FSaleOrgId_ID"]) + "+" + Convert.ToString(lstEntry["FSaleDeptId_ID"]) + "+" +
Convert.ToString(lstEntry["FSaleGroupId_ID"]) + "+" + Convert.ToString(lstEntry["FSalerId_ID"]);
if (!lstGroups.Contains(sGroup))
{
lstGroups.Add(sGroup);
}
}
//插入数据
//销售预测变更单的信息
BusinessInfo businessInfo = ((FormMetadata)MetaDataServiceHelper.Load(this.Context, "JN_YDL_SAL_ForecastChange", true)).BusinessInfo;
DynamicObject billHead = null;
DynamicObjectCollection billEntrys = null;
List <DynamicObject> lstBills = new List <DynamicObject>();
List <DynamicObject> lstSelectEntrys;
long lSaleOrgId = 0;
long lSaleDeptId = 0;
long lSaleGroupId = 0;
long lSalerId = 0;
foreach (string lstGroup in lstGroups)
{
//单据头
billHead = businessInfo.GetDynamicObjectType().CreateInstance() as DynamicObject;
//单据体
billEntrys = billHead["FEntity"] as DynamicObjectCollection;
if (billHead == null || billEntrys == null)
{
continue;
}
string[] sGroupSplit = lstGroup.Split('+');
if (sGroupSplit == null || sGroupSplit.Count() != 4)
{
continue;
}
lSaleOrgId = Convert.ToInt64(sGroupSplit[0]);
lSaleDeptId = Convert.ToInt64(sGroupSplit[1]);
lSaleGroupId = Convert.ToInt64(sGroupSplit[2]);
lSalerId = Convert.ToInt64(sGroupSplit[3]);
billHead["FBillTypeID_Id"] = "58b2a721c7f776";
billHead["FJNSaleOrgId_Id"] = lSaleOrgId;
billHead["FJNSaleDeptId_Id"] = lSaleDeptId;
billHead["FJNSaleGroupId_Id"] = lSaleGroupId;
billHead["FJNSalerId_Id"] = lSalerId;
billHead["FDocumentStatus"] = "A";
billHead["FJNDate"] = DateTime.Now;
billHead["FDirection"] = sAdjustType == "A" ? "B" : "A";
billHead["FCreateDate"] = DateTime.Now;
billHead["FCreatorId_Id"] = this.Context.UserId;
lstSelectEntrys = lstEntrys.Where(p => Convert.ToInt64(p["FSaleOrgId_Id"]) == lSaleOrgId && Convert.ToInt64(p["FSaleDeptId_Id"]) == lSaleDeptId && Convert.ToInt64(p["FSaleGroupId_Id"]) == lSaleGroupId && Convert.ToInt64(p["FSalerId_Id"]) == lSalerId).ToList();
if (lstSelectEntrys == null || lstSelectEntrys.Count() <= 0)
{
continue;
}
int seq = 1;
foreach (DynamicObject entry in lstSelectEntrys)
{
DynamicObject billEntry = new DynamicObject(billEntrys.DynamicCollectionItemPropertyType);
billEntry["Seq"] = seq;
billEntry["FJNMaterialId_Id"] = entry["FMaterialId_Id"];
billEntry["FJNAUXPROP_Id"] = entry["FAuxPropId_Id"];
billEntry["FJNForecastQty"] = entry["FAdjustQty"];
billEntry["FJNUnitID_Id"] = entry["FUnitID_Id"];
billEntry["FJNBaseUnitID_Id"] = entry["FUnitID_Id"];
billEntry["FJNBaseUnitQty"] = entry["FAdjustQty"];
billEntry["FJNStockOrg_Id"] = this.Context.CurrentOrganizationInfo.ID;
billEntry["FJNSettleOrg_Id"] = this.Context.CurrentOrganizationInfo.ID;
billEntry["FJNSupplyOrg_Id"] = this.Context.CurrentOrganizationInfo.ID;
DynamicObject material = entry["FMaterialId"] as DynamicObject;
if (material != null)
{
string Materialname = material["Name"].ToString();
string sql = "";
if (Materialname.IndexOf("(内蒙）") > 0 || Materialname.IndexOf("(内蒙)") > 0 || Materialname.IndexOf("（内蒙）") > 0)
{
billEntry["FJNSupplyOrg_Id"] = 100063;
sql = string.Format(@"select t2.FWORKSHOPID as FWORKSHOPID from T_BD_MATERIAL t1
join T_BD_MATERIALPRODUCE t2 on t1.FMATERIALID=t2.FMATERIALID
where t1.FMASTERID in(
select FMASTERID from T_BD_MATERIAL where FMATERIALID={0})
and t1.FUSEORGID={1}", Convert.ToInt32(entry["FMaterialId_Id"]), 100063);
}
else
{
billEntry["FJNSupplyOrg_Id"] = 100062;
sql = string.Format(@"select t2.FWORKSHOPID as FWORKSHOPID from T_BD_MATERIAL t1
join T_BD_MATERIALPRODUCE t2 on t1.FMATERIALID=t2.FMATERIALID
where t1.FMASTERID in(
select FMASTERID from T_BD_MATERIAL where FMATERIALID={0})
and t1.FUSEORGID={1}", Convert.ToInt32(entry["FMaterialId_Id"]), 100062);
}
DynamicObjectCollection FWORKSHOPID = DBServiceHelper.ExecuteDynamicObject(this.Context, sql);
if (FWORKSHOPID.Count > 0)
{
int WORKSHOP = Convert.ToInt32(FWORKSHOPID[0]["FWORKSHOPID"]);
billEntry["F_VTR_PrdDeptId_Id"] = WORKSHOP;
}
/* else
* {
* billEntry["F_VTR_PrdDeptId"] = 0;
* }*/
}
billEntrys.Add(billEntry);
seq++;
}
lstBills.Add(billHead);
}
if (lstBills.Count <= 0)
{
return;
}
//生成编码
MakeBillNo(lstBills);
Kingdee.BOS.ServiceHelper.DBServiceHelper.LoadReferenceObject(this.Context, lstBills.ToArray(), businessInfo.GetDynamicObjectType(), false);
DynamicObject[] billDatas = BusinessDataServiceHelper.Save(this.Context, lstBills.ToArray());
if (billDatas == null || billDatas.Count() <= 0)
{
//K3DisplayerModel model = K3DisplayerModel.Create(this.Context, "调整失败，未成功创建销售预测变更单");
//// 创建一个交互提示错误对象，并设置错误来源，相互隔离
//KDInteractionException ie = new KDInteractionException("错误提示");
//ie.InteractionContext.InteractionFormId = FormIdConst.BOS_K3Displayer; // 提示信息显示界面
//ie.InteractionContext.K3DisplayerModel = model; // 提示内容
//ie.InteractionContext.IsInteractive = true; // 是否需要交互
//throw ie; // 抛出错误，终止流程
this.OperationResult.OperateResult[0].SuccessStatus = false;
this.OperationResult.OperateResult[0].Message = "创建销售预测变更单失败";
}
else
{
OperateResult result;
foreach (var item in billDatas)
{
result = new OperateResult
{
SuccessStatus = true,
Message = "创建销售预测变更单成功",
MessageType = MessageType.Normal,
Name = "生成销售预测变更单:" + Convert.ToString(item["FBillNo"]) + "成功",
PKValue = item
};
this.OperationResult.OperateResult.Add(result);
}
}
this.OperationResult.IsShowMessage = true;
}
/// <summary>
/// 获取销售预测结余表信息
/// </summary>
/// <param name="ctx"></param>
/// <param name="OrgId"></param>
/// <param name="SDate"></param>
/// <param name="EDate"></param>
/// <returns></returns>
public DynamicObjectCollection GetForecastBalanceInfo(Context ctx, long OrgId, long DeptId, long GroupId, long SalerId, DateTime SDate, DateTime EDate)
{
if (OrgId <= 0)
{
return(null);
}
string sql = string.Format(@"select tx.FSaleOrgId,tx.FSalerId,tx.FMaterialId,tx.FAuxPropId
,tx.FUnitID,FconsumQty=(case when tm.FconsumQty1 is null then 0 else tm.FconsumQty1 end) - (case when tm.FconsumQty2 is null then 0 else tm.FconsumQty2 end)
,FreductQty=(case when tm.FreductQty is null then 0 else tm.FreductQty end),FaddQty=(case when tm.FaddQty is null then 0 else tm.FaddQty end),FQty=(case when tx.FQty is null then 0 else tx.FQty end)
,tx.FSaleDeptId
,FforecastQty=(case when tw.FconsumQty1 is null then 0 else tw.FconsumQty1 end)-(case when tw.FconsumQty2 is null then 0 else tw.FconsumQty2 end)
,FRate=case when ((case when tw.FconsumQty1 is null then 0 else tw.FconsumQty1 end)-(case when tw.FconsumQty2 is null then 0 else tw.FconsumQty2 end)+(case when tm.FaddQty is null then 0 else tm.FaddQty end))=0 then 0
else ((case when tx.FQty is null then 0 else tx.FQty end)+(case when tm.FreductQty is null then 0 else tm.FreductQty end))/((case when tw.FconsumQty1 is null then 0 else tw.FconsumQty1 end)-(case when tw.FconsumQty2 is null then 0 else tw.FconsumQty2 end)+(case when tm.FaddQty is null then 0 else tm.FaddQty end))*100 end
from JN_T_SAL_ForecastBack tx
left join
(select t1.FSaleOrgId,t1.FSalerId,t1.FSaleDeptId,t1.FMaterialId,t1.FAuxPropId,
FconsumQty1=sum((case when t1.FDirection='B' and t1.FBillType in ('A','B','C') then t1.FAdjustQty else 0 end)),
FconsumQty2=sum((case when t1.FDirection='A' and t1.FBillType in ('A','B','C') then t1.FAdjustQty else 0 end)),
FreductQty=sum((case when t1.FDirection='B' and t1.FBillType='E' then t1.FAdjustQty else 0 end)),
FaddQty=sum((case when t1.FDirection='A' and t1.FBillType='E' then t1.FAdjustQty else 0 end))
from JN_T_SAL_ForecastLog t1
where t1.FSaleOrgId={0} ", OrgId);
if (DeptId > 0)
{
sql = sql + string.Format(@" and t1.FSaleDeptId={0}", DeptId);
}
if (GroupId > 0)
{
sql = sql + string.Format(@" and t1.FSaleGroupId={0}", GroupId);
}
if (SalerId > 0)
{
sql = sql + string.Format(@" and t1.FSalerId={0}", SalerId);
}
sql = sql + string.Format(@" group by t1.FSaleOrgId,t1.FSalerId,t1.FMaterialId,t1.FAuxPropId,t1.FSaleDeptId) tm
on tx.FSaleOrgId=tm.FSaleOrgId and tx.FSalerId=tm.FSalerId and tx.FMaterialId=tm.FMaterialId and tx.FAuxPropId=tm.FAuxPropId
and tx.FSaleDeptId=tm.FSaleDeptId
left join
(select t2.FSaleOrgId,t2.FSalerId,t2.FSaleDeptId,t2.FMaterialId,t2.FAuxPropId,
FconsumQty1=sum(case when t2.FDirection='A' and t2.FBillType='D' then t2.FAdjustQty else 0 end)
,FconsumQty2=sum(case when t2.FDirection='B' and t2.FBillType='D' then t2.FAdjustQty else 0 end)
,FconsumQty3=sum(case when t2.FDirection='A' and t2.FBillType='E' then t2.FAdjustQty else 0 end)
,FconsumQty4=sum(case when t2.FDirection='B' and t2.FBillType='E' then t2.FAdjustQty else 0 end)
from JN_T_SAL_ForecastLog t2
where t2.FSaleOrgId={0} ", OrgId);
if (DeptId > 0)
{
sql = sql + string.Format(@" and t2.FSaleDeptId={0}", DeptId);
}
if (GroupId > 0)
{
sql = sql + string.Format(@" and t2.FSaleGroupId={0}", GroupId);
}
if (SalerId > 0)
{
sql = sql + string.Format(@" and t2.FSalerId={0}", SalerId);
}
sql = sql + string.Format(@" group by t2.FSaleOrgId,t2.FSalerId,t2.FMaterialId,t2.FAuxPropId,t2.FSaleDeptId) tw
on tx.FSaleOrgId=tw.FSaleOrgId and tx.FSalerId=tw.FSalerId and tx.FMaterialId=tw.FMaterialId and tx.FAuxPropId=tw.FAuxPropId
and tx.FSaleDeptId=tw.FSaleDeptId
where tx.FSaleOrgId={0} ", OrgId);
if (DeptId > 0)
{
sql = sql + string.Format(@" and tx.FSaleDeptId={0}", DeptId);
}
if (GroupId > 0)
{
sql = sql + string.Format(@" and tx.FSaleGroupId={0}", GroupId);
}
if (SalerId > 0)
{
sql = sql + string.Format(@" and tx.FSalerId={0}", SalerId);
}
return(DBServiceHelper.ExecuteDynamicObject(ctx, sql));
}
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
DynamicObject ProdFilter = filter.FilterParameter.CustomFilter;
//DynamicObject material =ProdFilter["F_PAEZ_Material"] as DynamicObject;
//DynamicObject materialGroupobj = ProdFilter["F_PAEZ_BaseProperty"] as DynamicObject;
int materialID = Convert.ToInt32(ProdFilter["F_PAEZ_Material_Id"]);
string materialType = Convert.ToString(ProdFilter["F_PAEZ_ErpClsID"]);
DynamicObjectCollection proCol = ProdFilter["F_PAEZ_BaseProperty"] as DynamicObjectCollection;
string materialGroup = "";
for (int i = 0; i < proCol.Count; i++)
{
if (i == 0)
{
materialGroup = Convert.ToString(proCol[i]["F_PAEZ_BaseProperty_Id"]);
}
else
{
materialGroup = materialGroup + "," + Convert.ToString(proCol[i]["F_PAEZ_BaseProperty_Id"]);
}
}
// int materialGroup = Convert.ToInt32(ProdFilter["F_PAEZ_BaseProperty_Id"]);
tempTables = "";
tempTable = "";
tempTableList = new List <string>();
string whSql = " select a.FNUMBER from T_BD_STOCK a inner join T_BD_STOCKGROUP b on b.FID=a.FGROUP WHERE b.fnumber = '02' OR a.FNUMBER IN ('CK002','CK010') order by a.fnumber ";
whCol = DBServiceHelper.ExecuteDynamicObject(this.Context, whSql) as DynamicObjectCollection;
for (int i = 0; i < whCol.Count; i++)
{
tempTable = tempTable + Convert.ToString(whCol[i]["FNUMBER"]) + ',';
tempTableList.Add(Convert.ToString(whCol[i]["FNUMBER"]));
}
tempTables = tempTable.Substring(0, tempTable.Length - 1);
//and a.FStockStatusId = 10000 库存状态可用
string AllFilter = @"/*dialect*/
SELECT
p.*,
(
SELECT
SUM (a.FBaseQty)
FROM
T_STK_INVENTORY a
INNER JOIN T_BD_STOCK b ON a.fstockid = b.fstockid and b.FNUMBER IN('CK002','CK010')
WHERE
a.FMaterialId = p.FMaterialId and a.FStockStatusId = 10000
) AS PRODTOTAL,
(
SELECT
SUM (a.FBaseQty)
FROM
T_STK_INVENTORY a
INNER JOIN T_BD_STOCK b ON a.fstockid = b.fstockid
inner join T_BD_STOCKGROUP c on c.FID=b.FGROUP
WHERE a.FMaterialId = p.FMaterialId
AND c.FNUMBER = '02' and a.FStockStatusId = 10000
) AS SUBSTOCKTOTAL,
(
SELECT
SUM (a.FBaseQty)
FROM
T_STK_INVENTORY a
INNER JOIN T_BD_STOCK b ON a.fstockid = b.fstockid
WHERE
a.FMaterialId = p.FMaterialId
AND b.FNUMBER IN('{2}') and a.FStockStatusId = 10004
) AS onTheWay,
(
SELECT
case when SUM (a.FBaseQty) is null then 0 else SUM (a.FBaseQty) end
FROM
T_STK_INVENTORY a
INNER JOIN T_BD_STOCK b ON a.fstockid = b.fstockid
WHERE
a.FMaterialId = p.FMaterialId
AND b.FNUMBER IN('{2}') and a.FStockStatusId in (10000,10004)
) AS TOTALALL,
(
SELECT
case when ( SUM(c.FBASEREMAINOUTQTY) ) is null then 0 else SUM(c.FBASEREMAINOUTQTY) end
FROM
T_SAL_ORDERENTRY a
INNER JOIN T_SAL_ORDER b ON a.FID=b.FID and b.FDOCUMENTSTATUS = 'C'
LEFT JOIN T_SAL_ORDERENTRY_R c on a.FEntryID=c.FEntryID
WHERE
a.FMaterialId = p.FMaterialId and c.FBASEREMAINOUTQTY>0
) AS LEFTSALEORDERTOTAL,
0 AS LACKTOTAL,
(
SELECT
SUM(c.FNOSTOCKINQTY)
FROM
T_PRD_MOENTRY a
inner JOIN T_PRD_MO b on a.fid =b.fid and b.FDOCUMENTSTATUS='C'
inner JOIN T_PRD_MOENTRY_Q c on c.FEntryId= a.FEntryId
inner JOIN T_PRD_MOENTRY_A d on d.FEntryId= a.FEntryId and d.FSTATUS in (2,3,4,5)
WHERE
a.FMaterialId = p.FMaterialId
) AS PRODPLANTOTAL,
0 AS PRODTOTALAMT,
0 AS SUBSTOCKTOTALAMT,
0 AS onTheWayAMT,
0 AS TOTALALLAMT,
0 AS LEFTSALEORDERTOTALAMT,
0 AS LACKTOTALAMT,
";
string others = "";
for (int i = 0; i < whCol.Count; i++)
{
string othersql = "0 as " + Convert.ToString(whCol[i]["FNUMBER"]) + "notouttoal, " +
" 0 AS " + Convert.ToString(whCol[i]["FNUMBER"]) + "notoutamt, " +
" 0 AS " + Convert.ToString(whCol[i]["FNUMBER"]) + "amt, ";
othersql = string.Format(othersql);
others = others + othersql;
}
string leftsql = " row_number() over (ORDER BY P.FMaterialId) as FIDENTITYID into {0} FROM ( SELECT " +
"A.FMaterialId as FMaterialId, " +
"A.FNUMBER AS MATERIALFNUMBER," +
"A.foldnumber as foldnumber," +
"T5.FNAME as FMATERIALGROUP," +
"T4.fid as FMATERIALGROUPID," +
"t21.FNAME as FNAME, " +
"A.F_kd_Text as FHANYU," +
"isnull(t6.FREFCOST,0) as FREFCOST," +
"t22.FErpClsID AS FErpClsID," +
"CASE t22.FErpClsID" +
" WHEN '1' THEN '外购'" +
" WHEN '2' THEN '自制'" +
" WHEN '3' THEN '委外'" +
" WHEN '9' THEN '配置'" +
" WHEN '10' THEN '资产'" +
" WHEN '4' THEN '特征'" +
" WHEN '11' THEN '费用'" +
" WHEN '5' THEN '虚拟'" +
" WHEN '12' THEN '模型'" +
" WHEN '6' THEN '服务'" +
" WHEN '7' THEN '一次性'" +
" ELSE '其他' END " +
" AS FErpClsName, "+
"t26.FPrecision as JinDu," +
"B.* from T_BD_MATERIAL A LEFT JOIN (SELECT " +
"T1.FMaterialId as BFMaterialId, " +
"t3.fnumber as fnumber," +
"sum(t1.FBaseQty) as FBaseQty" +
" FROM T_STK_INVENTORY T1 " +
" INNER JOIN T_BD_MATERIAL t2 on T1.FMATERIALID = t2.FMATERIALID" +
" LEFT JOIN T_BD_STOCK t3 on t1.fstockid=t3.fstockid" +
" WHERE T2.FFORBIDSTATUS = 'A' and t2.FDocumentStatus='C' and t1.FStockStatusId = 10000 and ( T2.FNUMBER like 'P%' or T2.FNUMBER like 'S%' or T2.FNUMBER like 'Z%') " +
" group by T1.FMaterialId,t3.fnumber,T1.FID" +
" ) t " +
" PIVOT (" +
" sum(FBaseQty) FOR fnumber IN({1}) ) AS B ON A.FMaterialId=B.BFMaterialId " +
"INNER JOIN T_BD_MATERIAL_L t21 on t21.FMATERIALID = A.FMATERIALID and t21.FLOCALEID=2052" +
" INNER JOIN t_BD_MaterialBase t22 on t22.fmaterialID = A.FMATERIALID" +
" INNER JOIN T_BD_UNIT t26 on t26.FUNITID = t22.FBASEUNITID" +
" LEFT JOIN T_BD_MATERIALGROUP T4 ON A.FMATERIALGROUP=T4.FID" +
" LEFT JOIN T_BD_MATERIALGROUP_L T5 ON T5.FID=T4.FID and t5.FLOCALEID=2052" +
" LEFT JOIN t_BD_MaterialStock T6 ON T6.FMATERIALID=A.FMATERIALID " +
" WHERE A.FFORBIDSTATUS = 'A' and A.FDocumentStatus='C' and ( A.FNUMBER like 'P%' or A.FNUMBER like 'S%' or A.FNUMBER like 'Z%') ) AS p ";
string sqlFilterAll = "";
string sqlall = AllFilter + others + leftsql;
if (materialID != 0 || materialType != "" || materialGroup != "")
{
sqlall = sqlall + " WHERE ";
}
if (materialID != 0)
{
sqlall = sqlall + "P.FMaterialId = {3}";
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList), materialID);
}
if (materialType != "")
{
if (materialID != 0)
{
sqlall = sqlall + "AND P.FErpClsID = '{4}'";
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList), materialID, materialType);
}
else
{
sqlall = sqlall + " P.FErpClsID = '{3}'";
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList), materialType);
}
}
if (materialGroup != "")
{
if (materialType != "")
{
if (materialID != 0)
{
sqlall = sqlall + " AND P.FMATERIALGROUPID in ({5})";
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList), materialID, materialType, materialGroup);
}
else
{
sqlall = sqlall + " AND P.FMATERIALGROUPID in ({4})";
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList), materialType, materialGroup);
}
}
else
if (materialID != 0)
{
sqlall = sqlall + " AND P.FMATERIALGROUPID in ({4})";
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList), materialID, materialGroup);
}
else
{
sqlall = sqlall + " P.FMATERIALGROUPID in ({3})";
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList), materialGroup);
}
}
if (materialID == 0 && materialType == "" && materialGroup == "")
{
sqlFilterAll = string.Format(sqlall, tableName, tempTables, string.Join("','", tempTableList));
}
DBUtils.Execute(this.Context, sqlFilterAll);
List <SqlObject> updateNotTotalList = new List <SqlObject>();
List <SqlObject> updateNotTotalList2 = new List <SqlObject>();
List <SqlObject> updateamtList = new List <SqlObject>();
for (int i = 0; i < whCol.Count; i++)
{
string clonumName = Convert.ToString(whCol[i]["FNUMBER"]);
if (clonumName == "CK002" || clonumName == "CK010")
{
continue;
}
else
{ // 更新分公司 未出数量 字段 剩余未出数量（销售基本） FBASEREMAINOUTQTY sum(a.FQty) - sum(c.FSTOCKOUTQTY)
string updateothersql = "merge into {0} temp using ( SELECT a.FMaterialId, sum(c.FBASEREMAINOUTQTY) as qqty FROM T_SAL_ORDERENTRY a " +
" LEFT JOIN T_SAL_ORDER b ON a.FID=b.FID and b.FDOCUMENTSTATUS = 'C' " +
" LEFT JOIN T_SAL_ORDERENTRY_R c ON a.FEntryID = c.FEntryID " +
" LEFT JOIN T_BD_STOCK d on b.F_KD_BASE =d.fstockid where c.FBASEREMAINOUTQTY>0 and d.FNUMBER = '{1}' " +
" group by a.FMaterialId ) t2 on temp.FMaterialId=t2.FMaterialId" +
" when matched then update set temp." + Convert.ToString(whCol[i]["FNUMBER"]) + "notouttoal = isnull(t2.qqty,0) ";
//"notouttoal = case when isnull(t2.qqty,0)-isnull(temp." + Convert.ToString(whCol[i]["FNUMBER"]) +
//" ,0) >0 then isnull(t2.qqty,0)-isnull(temp." + Convert.ToString(whCol[i]["FNUMBER"]) + " ,0) else 0 end ;";
string updatenotouttotalsql = string.Format(updateothersql, tableName, Convert.ToString(whCol[i]["FNUMBER"]));
updateNotTotalList.Add(new SqlObject(updatenotouttotalsql, new List <SqlParam>()));
//string updatenotouttotalsql2 = "update " + tableName + " set " + Convert.ToString(whCol[i]["FNUMBER"]) + "notouttoal =isnull(" +
// Convert.ToString(whCol[i]["FNUMBER"]) + "notouttoal,0)-isnull("+ Convert.ToString(whCol[i]["FNUMBER"])+
// ",0) where isnull(" +Convert.ToString(whCol[i]["FNUMBER"]) + "notouttoal,0) >isnull("+ Convert.ToString(whCol[i]["FNUMBER"]) +",0) ;";
//updateNotTotalList2.Add(new SqlObject(updatenotouttotalsql2, new List<SqlParam>()));
// 更新分公司 未出金额 、库存金额 字段
string fengongsiamt = "update {0} " +
"set " + Convert.ToString(whCol[i]["FNUMBER"]) + "amt = ISNULL(" + Convert.ToString(whCol[i]["FNUMBER"]) + "* FREFCOST,0) ," +
Convert.ToString(whCol[i]["FNUMBER"]) + "notoutamt = ISNULL(" + Convert.ToString(whCol[i]["FNUMBER"]) + "notouttoal* FREFCOST,0) ; ";
string fengongsiamtsql = string.Format(fengongsiamt, tableName, Convert.ToString(whCol[i]["FNUMBER"]), tableName);
updateamtList.Add(new SqlObject(fengongsiamtsql, new List <SqlParam>()));
}
}
DBUtils.ExecuteBatch(this.Context, updateNotTotalList);
//DBUtils.ExecuteBatch(this.Context, updateNotTotalList2);
DBUtils.ExecuteBatch(this.Context, updateamtList);
//更新不足数量
String updateTemp = "update " + tableName + " set LACKTOTAL = LEFTSALEORDERTOTAL - TOTALALL where LEFTSALEORDERTOTAL>TOTALALL";
DBUtils.Execute(this.Context, updateTemp);
//更新金额
string updateAmt = "update {0} set PRODTOTALAMT=isnull(PRODTOTAL*FREFCOST,0) ," +
" SUBSTOCKTOTALAMT=isnull(SUBSTOCKTOTAL*FREFCOST,0) ," +
" onTheWayAMT=isnull(onTheWay*FREFCOST,0) ," +
" TOTALALLAMT=isnull(TOTALALL*FREFCOST,0) ," +
" LEFTSALEORDERTOTALAMT=isnull(LEFTSALEORDERTOTAL*FREFCOST,0) ," +
" LACKTOTALAMT=isnull(LACKTOTAL*FREFCOST,0) ;";
updateAmt = string.Format(updateAmt, tableName);
DBUtils.Execute(this.Context, updateAmt);
}