using Kingdee.BOS.Core.DynamicForm.PlugIn;
|
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
|
using Kingdee.BOS.Core.Metadata.EntityElement;
|
using Kingdee.BOS.JSON;
|
using Kingdee.BOS.Orm.DataEntity;
|
using Kingdee.BOS.ServiceHelper.Excel;
|
using Kingdee.BOS.Util;
|
using System;
|
using System.Collections.Generic;
|
using System.ComponentModel;
|
using System.Data;
|
using Newtonsoft.Json.Linq;
|
using Kingdee.BOS.WebApi.Client;
|
using ZD.Cloud.WebApi;
|
using Kingdee.BOS.Core;
|
using Kingdee.BOS.Core.DynamicForm;
|
using Kingdee.BOS.KDThread;
|
using Kingdee.BOS.ServiceHelper;
|
using Demo.TimedTaskPlugIn.PRD;
|
using ZD.Cloud.Logger;
|
|
|
namespace Demo.BillView.PRD
|
{
|
|
[Description("生产日计划导入")]
|
[HotUpdate]
|
public class Pro_ScDayImport : AbstractDynamicFormPlugIn
|
{
|
CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
|
private string fileFullPath = string.Empty;
|
//public override void BarItemClick(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.BarItemClickEventArgs e)
|
//{
|
// base.BarItemClick(e);
|
// if (e.BarItemKey.ToUpper().Equals("TBIMPORT"))
|
// {
|
// }
|
|
// if (e.BarItemKey.ToUpper().Equals("TBWJSC"))
|
// {
|
// //Control updateControl = this.View.GetControl("fileFullPath");
|
// //// 上传地址
|
// //string uploadUrl = "FileUpLoadServices/FileService.svc/upload2attachment/";
|
// //updateControl.SetCustomPropertyValue("ExtandUrl", uploadUrl);
|
// //// 下载地址
|
// //string downloadUrl = "FileUpLoadServices/download.aspx";
|
// //updateControl.SetCustomPropertyValue("ExDownLoadUrl", downloadUrl);
|
|
// ImportData(fileFullPath);
|
// }
|
|
//}
|
public override void AfterBarItemClick(AfterBarItemClickEventArgs e)
|
{
|
base.AfterBarItemClick(e);
|
if (e.BarItemKey.ToUpper().Equals("TBIMPORT"))
|
{
|
ImportData();
|
}
|
|
if (e.BarItemKey.ToUpper().Equals("TBWJSC"))
|
{
|
//Control updateControl = this.View.GetControl("fileFullPath");
|
//// 上传地址
|
//string uploadUrl = "FileUpLoadServices/FileService.svc/upload2attachment/";
|
//updateControl.SetCustomPropertyValue("ExtandUrl", uploadUrl);
|
//// 下载地址
|
//string downloadUrl = "FileUpLoadServices/download.aspx";
|
//updateControl.SetCustomPropertyValue("ExDownLoadUrl", downloadUrl);
|
|
ImportDataSC(fileFullPath);
|
}
|
}
|
public override void CustomEvents(CustomEventsArgs e)
|
{
|
//触发事件是上传文件有变化
|
if (e.EventName.ToUpper() == "FILECHANGED")
|
{
|
JSONObject jSONObject = KDObjectConverter.DeserializeObject<JSONObject>(e.EventArgs);
|
if (jSONObject != null)
|
{
|
JSONArray jSONArray = new JSONArray(jSONObject["NewValue"].ToString());
|
if (jSONArray.Count > 0)
|
{
|
//获取上传的文件名
|
string text = (jSONArray[0] as Dictionary<string, object>)["ServerFileName"].ToString();
|
//文件上传到服务端的临时目录
|
string directory = "FileUpLoadServices\\UploadFiles";
|
//文件的完整路径
|
fileFullPath = PathUtils.GetPhysicalPath(directory, text);
|
}
|
}
|
}
|
base.CustomEvents(e);
|
}
|
/// <summary>
|
/// 文件上传界面数据显示
|
/// </summary>
|
/// <param name="fileFullPath"></param>
|
private void ImportDataSC(string fileFullPath)
|
{
|
using (ExcelOperation helper = new ExcelOperation(this.View))
|
{
|
if (!string.IsNullOrEmpty(fileFullPath))
|
{
|
DataSet ds = helper.ReadFromFile(fileFullPath, 1);
|
DataTable dt = ds.Tables[0];
|
|
DateTime date = Convert.ToDateTime(this.Model.GetValue("F_Paez_Date"));
|
if (dt.Rows.Count > 0)
|
{
|
//Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
|
//DynamicObjectCollection rows = this.Model.GetEntityDataObject(entity);
|
this.Model.DeleteEntryData("FEntity");
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
//根据生产订单号和物料查询是否存在明细
|
string sql = string.Format(@"/*dialect*/ SELECT T1.FBILLNO,T2.* FROM T_PRD_MO T1
|
LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
|
JOIN T_BD_MATERIAL T3 ON T2.FMATERIALID = T3.FMATERIALID
|
WHERE T1.FBILLNO = '{0}' AND T3.FNUMBER = '{1}'", dt.Rows[i][1].ToString(), dt.Rows[i][5].ToString());
|
DataTable _dt = new DataTable();
|
_dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
|
string FLOT = "";//FLOT 批次
|
string FQTY = "";//FQTY 生产订单数量
|
string FBILLNO = "";//FBILLNO 原单号
|
if (_dt.Rows.Count > 0)
|
{
|
FLOT = _dt.Rows[0]["FLOT"].ToString();
|
FQTY = _dt.Rows[0]["FQTY"].ToString();
|
FBILLNO = _dt.Rows[0]["FBILLNO"].ToString();
|
}
|
//物料编码 + 生产订单号 + 生产资源 去判断是否导入过 带出日计划工单ID及编号
|
string fDayPlanID = "";
|
string fDayPlanBillNo = "";
|
sql = string.Format(@"select a.FID,FBILLNO,FSCOrderNo,FHMaterID,FHSourceID from Sc_WorkBillSortBillMain a JOIN T_BD_MATERIAL b ON a.FHMaterID = b.FMATERIALID
|
left join T_ENG_WORKCENTER c on a.FHSourceID=c.fid
|
where FSCOrderNo ='{0}' and b.FNUMBER ='{1}' and isnull(c.FNUMBER,'') ='{2}' ", dt.Rows[i][1].ToString(), dt.Rows[i][5].ToString(), dt.Rows[i][4].ToString());
|
DataTable dayPlanDt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
|
if (dayPlanDt.Rows.Count > 0)
|
{
|
fDayPlanID = dayPlanDt.Rows[0]["FID"].ToString();
|
fDayPlanBillNo = dayPlanDt.Rows[0]["FBILLNO"].ToString();
|
}
|
//DynamicObject row = new DynamicObject(entity.DynamicObjectType);
|
this.Model.CreateNewEntryRow("FEntity");
|
this.Model.SetValue("FSEQ", i + 1, i);
|
this.Model.SetValue("FSalOrderNo", dt.Rows[i][0].ToString(), i);
|
this.Model.SetValue("FSCOrderNo", dt.Rows[i][1].ToString(), i);
|
this.Model.SetItemValueByNumber("FORGID", dt.Rows[i][2].ToString(), i);
|
this.Model.SetItemValueByNumber("FProductWorkShopId", dt.Rows[i][3].ToString(), i);
|
this.Model.SetItemValueByNumber("FResourcesId", dt.Rows[i][4].ToString(), i);
|
// this.Model.SetValue("FMatrailId", dt.Rows[i][5].ToString(), i);
|
this.Model.SetItemValueByNumber("FMatrailId", dt.Rows[i][5].ToString(), i);
|
this.Model.SetValue("FBatch", dt.Rows[i][9].ToString(), i);
|
this.Model.SetValue("FSalOrderCount", dt.Rows[i][10].ToString(), i);
|
this.Model.SetValue("FCompleteSetCount", dt.Rows[i][12].ToString(), i);
|
this.Model.SetValue("FDeliveryDate", ToDateTimeValue(dt.Rows[i][13].ToString()), i);
|
this.Model.SetValue("FOrderLevel", OrderType(dt.Rows[i][14].ToString()), i);
|
this.Model.SetValue("FOrderQuantity", FQTY, i);
|
this.Model.SetValue("FDayPlanID", fDayPlanID, i);//日计划工单ID
|
this.Model.SetValue("FDayPlanBillNo", fDayPlanBillNo, i);//日工单编号
|
for (int j = 0; j <= 60; j++)
|
{
|
this.Model.SetValue("FT" + "" + j + "", dt.Rows[i][15 + j].ToString(), i);
|
}
|
//调用数量值更新
|
this.View.InvokeFieldUpdateService("FT0", i);
|
}
|
}
|
this.View.UpdateView();
|
for (int j = 0; j <= 60; j++)
|
{
|
this.View.GetControl("FT" + "" + j + "").Text = date.AddDays(j).ToShortDateString();
|
}
|
|
}
|
|
}
|
|
|
}
|
/// <summary>
|
/// 订单状态等级
|
/// </summary>
|
/// <param name="Type"></param>
|
/// <returns></returns>
|
public string OrderType(string Type)
|
{
|
switch (Type)
|
{
|
|
case "特急":
|
return "1";
|
break;
|
case "紧急":
|
return "2";
|
break;
|
case "普通":
|
return "3";
|
break;
|
default:
|
return " ";
|
break;
|
}
|
}
|
/// <summary>
|
/// 时间戳Timestamp转换成日期
|
/// </summary>
|
/// <param name="timeStamp"></param>
|
/// <returns></returns>
|
private DateTime GetDateTime(int timeStamp)
|
{
|
DateTime dtStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
|
long lTime = ((long)timeStamp * 10000000);
|
TimeSpan toNow = new TimeSpan(lTime);
|
DateTime targetDt = dtStart.Add(toNow);
|
return targetDt;
|
}
|
/// <summary>
|
/// 导入功能
|
/// </summary>
|
private void ImportData()
|
{
|
try
|
{
|
LogService.WriteAsync("测试");
|
string sql;
|
DateTime date = Convert.ToDateTime(this.Model.GetValue("F_Paez_Date"));
|
//获取单据体信息
|
Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
|
//单据体信息转换为列表集合
|
DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
|
List<string> sqlList = new List<string>();
|
foreach (DynamicObject current in entityDataObjoct)
|
{
|
if (Convert.ToString(current["FDayPlanID"]) == "0")
|
continue;
|
for (int i = 0; i <= 60; i++)
|
{
|
JObject jsonFPOOrderEntry = new JObject();
|
sql = string.Format($"update SC_WORKBILLSORTBILLSub set FHQTY ='{Convert.ToString(current["FT" + "" + i + ""])}' where FID ={Convert.ToString(current["FDayPlanID"])} and FHMASTERDATE ={date.AddDays(i).ToShortDateString()} and FHLOCKEDSUB = 0");
|
sqlList.Add(sql);
|
}
|
}
|
int res = DBServiceHelper.ExecuteBatch(Context, sqlList);
|
string _result = "";
|
foreach (DynamicObject current in entityDataObjoct)
|
{
|
//根据生产订单号和物料查询是否存在明细
|
sql = string.Format(@"/*dialect*/SELECT T1.FBILLNO,T2.* FROM T_PRD_MO T1
|
LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
|
WHERE T1.FBILLNO = '{0}' AND T2.FMATERIALID = '{1}'", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]));
|
DataTable dt = new DataTable();
|
dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
|
if (dt.Rows.Count == 0)
|
_result += "序号：" + Convert.ToString(current["SEQ"]) + ",生产订单号：" + Convert.ToString(current["FSCOrderNo"]) + "对应的物料" + (current["FMatrailId"] as DynamicObject)["Number"] + "不存在";
|
|
}
|
if (_result != "")
|
{
|
this.View.ShowErrMessage(_result);
|
return;
|
}
|
foreach (DynamicObject current in entityDataObjoct)
|
{
|
if (Convert.ToInt32(current["FNoScheduled"]) < 0)
|
_result += "序号：" + Convert.ToString(current["SEQ"]) + ",日计划总数量超出生产订单数量";
|
}
|
if (_result != "")
|
{
|
this.View.ShowErrMessage(_result);
|
return;
|
}
|
foreach (DynamicObject current in entityDataObjoct)
|
{
|
if (string.IsNullOrEmpty(Convert.ToString(current["FSalOrderNo"])) || string.IsNullOrEmpty(Convert.ToString(current["FSCOrderNo"])))
|
continue;
|
//根据生产订单号和物料查询是否存在明细
|
sql = string.Format(@"/*dialect*/SELECT T1.FBILLNO,T2.* FROM T_PRD_MO T1
|
LEFT JOIN T_PRD_MOENTRY T2 ON T1.FID = T2.FID
|
WHERE T1.FBILLNO = '{0}' AND T2.FMATERIALID = '{1}'", Convert.ToString(current["FSCOrderNo"]), Convert.ToString(current["FMatrailId_Id"]));
|
DataTable dt = new DataTable();
|
dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
|
if (dt.Rows.Count == 0)
|
continue;
|
string FID = "";//FID 源单主内码
|
string FENTRYID = "";//FENTRYID 源单子内码
|
string FLOT = "";//FLOT 批次
|
string FQTY = "";//FQTY 生产订单数量
|
string FBILLNO = "";//FBILLNO 原单号
|
if (dt.Rows.Count > 0)
|
{
|
FID = dt.Rows[0]["FID"].ToString();
|
FENTRYID = dt.Rows[0]["FENTRYID"].ToString();
|
FLOT = dt.Rows[0]["FLOT"].ToString();
|
FQTY = dt.Rows[0]["FQTY"].ToString();
|
FBILLNO = dt.Rows[0]["FBILLNO"].ToString();
|
}
|
JObject jsonRoot = new JObject();
|
jsonRoot.Add("Creator", "");
|
jsonRoot.Add("NeedUpDateFields", new JArray());
|
jsonRoot.Add("NeedReturnFields", new JArray());
|
jsonRoot.Add("IsDeleteEntry", "false");
|
jsonRoot.Add("SubSystemId", "");
|
jsonRoot.Add("IsVerifyBaseDataField", "");
|
JObject jsonModel = new JObject();
|
jsonModel.Add("FPreparatDate", date.ToString());//编制日期
|
jsonModel.Add("FSCOrderNo", current["FSCOrderNo"].ToString()); //生产订单号FSCOrderNo
|
jsonModel.Add("FHSeOrderBillNo", current["FSalOrderNo"].ToString());//销售订单号
|
jsonModel.Add("FHOrderLev", current["FOrderLevel"].ToString());//订单等级
|
jsonModel.Add("FSalOrderCount", current["FOrderLevel"].ToString());//销售订单数量
|
jsonModel.Add("FHOrderCommitDate", current["FDeliveryDate"].ToString());//交货期HOrderCommitDate
|
//jsonModel.Add("FBatch", current["FBatch"].ToString());//批次
|
jsonModel.Add("FBatch", FLOT);//批次
|
jsonModel.Add("FHOrderNeedQty", FQTY);// 订单需求数量
|
jsonModel.Add("FPrdBillNo", FBILLNO); ;//源单编号
|
jsonModel.Add("FPRDMOMAINID", FID); ;//源单主内码
|
jsonModel.Add("FPRDMOENTYID", FENTRYID); ;//源单子内码
|
jsonModel.Add("FOrderQuantity", current["FOrderQuantity"].ToString()); ;//生产订单数量
|
jsonModel.Add("FDayPlanQuantity", current["FDayPlanQuantity"].ToString()); ;//日计划数量
|
jsonModel.Add("FNoScheduled", current["FNoScheduled"].ToString()); ;//未排数量
|
JObject jsonFHMaterID = new JObject();
|
jsonFHMaterID.Add("FNumber", (current["FMatrailId"] as DynamicObject)["Number"].ToString());
|
jsonModel.Add("FHMaterID", jsonFHMaterID);//FHMaterID 物料
|
if (Convert.ToString(current["FORGID_Id"]) != "0")
|
{
|
JObject jsonFHPRDORGID = new JObject();
|
jsonFHPRDORGID.Add("FNumber", (current["FORGID"] as DynamicObject)["Number"].ToString());
|
jsonModel.Add("FHPRDORGID", jsonFHPRDORGID);//FHMaterID 生产组织
|
|
}
|
if (Convert.ToString(current["FResourcesId_Id"]) != "0")
|
{
|
JObject jsonFProductWorkShopId = new JObject();
|
jsonFProductWorkShopId.Add("FNumber", (current["FResourcesId"] as DynamicObject)["Number"].ToString());
|
jsonModel.Add("FHSourceID", jsonFProductWorkShopId);//FHSourceID 生产车间
|
}
|
if (Convert.ToString(current["FProductWorkShopId_Id"]) != "0")
|
{
|
JObject jsonFResourcesId = new JObject();
|
jsonFResourcesId.Add("FNumber", (current["FProductWorkShopId"] as DynamicObject)["Number"].ToString());
|
jsonModel.Add("FHWorkShopID", jsonFResourcesId);//FHWorkShopID 生产资源
|
}
|
JArray Entry = new JArray();
|
if (Convert.ToString(current["FDayPlanID"]) != "0")
|
{
|
sql = $"select max(FHMASTERDATE)FHMASTERDATE from SC_WORKBILLSORTBILLSub where FID={Convert.ToString(current["FDayPlanID"])} ";
|
DateTime EndDate = DBServiceHelper.ExecuteScalar<DateTime>(Context, sql, DateTime.Now);
|
for (int i = 0; i <= 60; i++)
|
{
|
if (EndDate >= date.AddDays(i))
|
continue;
|
JObject jsonFPOOrderEntry = new JObject();
|
jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
|
jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
|
jsonFPOOrderEntry.Add("FColumn", "FT" + "" + i + ""); ;//对应的列头
|
Entry.Add(jsonFPOOrderEntry);
|
}
|
jsonModel.Add("FEntity", Entry);
|
jsonModel.Add("FID", Convert.ToString(current["FDayPlanID"])); ;//源单编号
|
jsonRoot.Add("Model", jsonModel);
|
var result = cloudClient.Save("Paez_Sc_WorkBillAutoSortBill", jsonRoot.ToString());
|
JObject saveObj = JObject.Parse(result);
|
string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
|
LogService.WriteAsync(jsonRoot);
|
if (saveIsSuc == "TRUE")
|
{
|
string FDayPlanBillNo = saveObj["Result"]["Number"].ToString();
|
string FDayPlanID = saveObj["Result"]["Id"].ToString();
|
this.View.ShowMessage("导入成功");
|
}
|
else
|
{
|
LogService.WriteAsync(jsonRoot);
|
LogHelper.Error(jsonRoot.ToString());
|
this.View.ShowErrMessage(jsonRoot.ToString());
|
}
|
}
|
else
|
{
|
for (int i = 0; i <= 60; i++)
|
{
|
JObject jsonFPOOrderEntry = new JObject();
|
jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
|
jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
|
jsonFPOOrderEntry.Add("FColumn", "FT" + "" + i + ""); ;//对应的列头
|
Entry.Add(jsonFPOOrderEntry);
|
}
|
jsonModel.Add("FEntity", Entry);
|
jsonRoot.Add("Model", jsonModel);
|
var result = cloudClient.Save("Paez_Sc_WorkBillAutoSortBill", jsonRoot.ToString());
|
JObject saveObj = JObject.Parse(result);
|
string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
|
LogService.WriteAsync(jsonRoot);
|
if (saveIsSuc == "TRUE")
|
{
|
string FDayPlanBillNo = saveObj["Result"]["Number"].ToString();
|
string FDayPlanID = saveObj["Result"]["Id"].ToString();
|
this.Model.SetValue("FDayPlanBillNo", FDayPlanBillNo, Convert.ToInt32(current["SEQ"]) - 1);//
|
this.Model.SetValue("FDayPlanID", FDayPlanID, Convert.ToInt32(current["SEQ"]) - 1);//
|
this.View.ShowMessage("导入成功");
|
}
|
else
|
{
|
LogService.WriteAsync(jsonRoot);
|
LogHelper.Error(jsonRoot.ToString());
|
this.View.ShowErrMessage(jsonRoot.ToString());
|
}
|
}
|
this.View.UpdateView();
|
}
|
}
|
catch (Exception ex)
|
{
|
|
this.View.ShowErrMessage(ex.ToString());
|
}
|
|
}
|
/// <summary>
|
/// 数字转换时间格式
|
/// </summary>
|
/// <param name="timeStr">数字,如:42095.7069444444/0.650694444444444</param>
|
/// <returns>日期/时间格式</returns>
|
private string ToDateTimeValue(string strNumber)
|
{
|
if (!string.IsNullOrWhiteSpace(strNumber))
|
{
|
Decimal tempValue;
|
//先检查 是不是数字;
|
if (Decimal.TryParse(strNumber, out tempValue))
|
{
|
//天数,取整
|
int day = Convert.ToInt32(Math.Truncate(tempValue));
|
//这里也不知道为什么. 如果是小于32,则减1,否则减2
|
//日期从1900-01-01开始累加
|
// day = day < 32 ? day - 1 : day - 2;
|
DateTime dt = new DateTime(1900, 1, 1).AddDays(day < 32 ? (day - 1) : (day - 2));
|
|
//小时:减掉天数,这个数字转换小时:(* 24)
|
Decimal hourTemp = (tempValue - day) * 24;//获取小时数
|
//取整.小时数
|
int hour = Convert.ToInt32(Math.Truncate(hourTemp));
|
//分钟:减掉小时,( * 60)
|
//这里舍入,否则取值会有1分钟误差.
|
Decimal minuteTemp = Math.Round((hourTemp - hour) * 60, 2);//获取分钟数
|
int minute = Convert.ToInt32(Math.Truncate(minuteTemp));
|
//秒:减掉分钟,( * 60)
|
//这里舍入,否则取值会有1秒误差.
|
Decimal secondTemp = Math.Round((minuteTemp - minute) * 60, 2);//获取秒数
|
int second = Convert.ToInt32(Math.Truncate(secondTemp));
|
|
//时间格式:00:00:00
|
string resultTimes = string.Format("{0}:{1}:{2}",
|
(hour < 10 ? ("0" + hour) : hour.ToString()),
|
(minute < 10 ? ("0" + minute) : minute.ToString()),
|
(second < 10 ? ("0" + second) : second.ToString()));
|
|
if (day > 0)
|
return string.Format("{0} {1}", dt.ToString("yyyy-MM-dd"), resultTimes);
|
else
|
return resultTimes;
|
}
|
}
|
return string.Empty;
|
}
|
}
|
|
}
|
