public static void Update()
{
int TotalCount = 0;
try
{
List <Z_MM_QUBE_MATNR> Z_MM_QUBE_MATNR_List = new List <Z_MM_QUBE_MATNR>();
ConsoleApp1.RFC.serviceSoapClient ws = new ConsoleApp1.RFC.serviceSoapClient();
//延長time out時間
ws.InnerChannel.OperationTimeout = new TimeSpan(0, 20, 0);
string StartDate = "20170101";
string EndDate = DateTime.Now.ToString("yyyyMMdd");
string JsonString = ws.GetAllProducts(StartDate, EndDate);
Z_MM_QUBE_MATNR_List = JsonConvert.DeserializeObject <List <Z_MM_QUBE_MATNR> >(JsonString);
foreach (var item in Z_MM_QUBE_MATNR_List)
{
string SqlCommand = " ";
string ExcuteResult = "";
DataTable dt = new DataTable();
Dictionary <string, object> param = new Dictionary <string, object>();
param.Add("MaterialsID", item.MATNR);
param.Add("WorksName", item.MAKTX);
param.Add("AuthorsNo", item.LIFNR);
param.Add("GenreName", item.WGBEZ);
param.Add("Purchase", Double.Parse(item.NETPR));
param.Add("Cost", Double.Parse(item.DMBTR));
param.Add("Material", item.ZZMATU);
param.Add("Currency", item.WAERS);
param.Add("ZFVOLUME", item.ZFVOLUME);
param.Add("ZZPAGE", item.ZZPAGE);
param.Add("WorkSize", item.ZZSE);
dt = DB.DBQuery("SELECT * From Works where MaterialsID=@MaterialsID AND IsDel='' ", param);
if (dt.Rows.Count > 0)
{
//作品名稱、組件、作品尺寸修改時不可從SAP覆帶到Cube
SqlCommand = "Update Works set " +
"AuthorsNo=@AuthorsNo," +
"Cost=@Cost," +
"Purchase=@Purchase," +
"Currency=@Currency," +
"GenreName=@GenreName," +
"Material=@Material," +
"ZZPAGE=@ZZPAGE," +
"WorkSize=@WorkSize," +
"ChangeState='Update', " +
"ModifyDate=GETDATE()," +
"ModifyUser='******' " +
"where MaterialsID=@MaterialsID ; ";
//更新藝術家作品總數
SqlCommand += "UPDATE Authors set WorksAmount=(SELECT isnull(count(*),0) from Works where AuthorsNo=@AuthorsNo AND IsDel='') where LIFNR=@AuthorsNo ; ";
ExcuteResult = string.Format("修改商品：{0}", item.MAKTX);
}
else
{
SqlCommand = "Insert Into Works (MaterialsID,WorksName,AuthorsNo,Cost,Purchase,Currency,GenreName,Material,ZFVOLUME,ZZPAGE,WorkSize,ChangeState,CreateUser,ModifyUser,ModifyDate) " +
"VALUES (@MaterialsID,@WorksName,@AuthorsNo,@Cost,@Purchase,@Currency,@GenreName,@Material,@ZFVOLUME,@ZZPAGE,@WorkSize,'Create','system','system',GETDATE()); ";
//更新藝術家作品總數
SqlCommand += "UPDATE Authors set WorksAmount=(SELECT isnull(count(*),0) from Works where AuthorsNo=@AuthorsNo AND IsDel='') where LIFNR=@AuthorsNo ; ";
ExcuteResult = string.Format("新增商品：{0}", item.MAKTX);
}
int num = DB.ExecuteNonQuery(SqlCommand, param);
if (num > 0)
{
TotalCount += num;
Console.WriteLine(ExcuteResult);
}
else
{
Console.WriteLine("庫存資料異動失敗");
}
}
Console.WriteLine(string.Format("共完成{0}筆商品資料異動", TotalCount));
}
catch (Exception e)
{
//發送通知信
string strMailTitle = "系統發生錯誤";
string str_mailbody = e.ToString();
Mail.Send(strMailTitle, str_mailbody);
Console.WriteLine(string.Format("系統發生錯誤：{0}", e.ToString()));
}
}