public static void Update()
{
int TotalCount = 0;
try {
List <Z_MM_QUBE_WERKS> Z_MM_QUBE_WERKS_List = new List <Z_MM_QUBE_WERKS>();
ConsoleApp1.RFC.serviceSoapClient ws = new ConsoleApp1.RFC.serviceSoapClient();
//延長time out時間
ws.InnerChannel.OperationTimeout = new TimeSpan(0, 20, 0);
string JsonString = ws.GetAllWarehouses();
if (string.IsNullOrEmpty(JsonString) == false)
{
string SqlCommand = " ";
Z_MM_QUBE_WERKS_List = JsonConvert.DeserializeObject <List <Z_MM_QUBE_WERKS> >(JsonString);
foreach (var item in Z_MM_QUBE_WERKS_List)
{
DataTable dt = new DataTable();
Dictionary <string, object> param = new Dictionary <string, object>();
param.Add("werks", item.WERKS);
param.Add("WareTypeName", item.NAME1);
dt = DB.DBQuery("SELECT * FROM [dbo].[Menu_WareType] where werks=@werks ", param);
//寫入到Cube的DB
if (dt.Rows.Count > 0)
{
SqlCommand = "UPDATE Menu_WareType set WareTypeName=@WareTypeName where werks=@werks ;";
Console.WriteLine(string.Format("更新倉庫：{0}", item.NAME1));
}
else
{
SqlCommand = "Insert Into Menu_WareType (WareTypeName,Werks) VALUES(@WareTypeName,@werks) ;";
Console.WriteLine(string.Format("新增倉庫：{0}", item.NAME1));
}
int num = DB.ExecuteNonQuery(SqlCommand, param);
TotalCount += num;
}
}
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()));
}
}
public static void Update()
{
int TotalCount = 0;
try {
List <Z_MM_QUBE_LIFNR> Z_MM_QUBE_LIFNR_List = new List <Z_MM_QUBE_LIFNR>();
ConsoleApp1.RFC.serviceSoapClient ws = new ConsoleApp1.RFC.serviceSoapClient();
//延長time out時間
ws.InnerChannel.OperationTimeout = new TimeSpan(0, 20, 0);
string StartDate = "20160101";
string EndDate = DateTime.Now.ToString("yyyyMMdd");
string JsonString = ws.GetAllVendors(StartDate, EndDate);
Z_MM_QUBE_LIFNR_List = JsonConvert.DeserializeObject <List <Z_MM_QUBE_LIFNR> >(JsonString);
foreach (var item in Z_MM_QUBE_LIFNR_List)
{
string SqlCommand = " ";
string ExcuteResult = "";
DataTable dt = new DataTable();
Dictionary <string, object> param = new Dictionary <string, object>();
param.Add("LIFNR", item.LIFNR);
param.Add("AuthorsCName", item.NAME1);
param.Add("LAND1", item.LAND1);
dt = DB.DBQuery("SELECT * FROM [dbo].[Authors] where LIFNR=@LIFNR ", param);
if (dt.Rows.Count > 0)
{
SqlCommand = "Update Authors set AuthorsCName=@AuthorsCName,LAND1=@LAND1,ModifyUser='******',ModifyDate=GETDATE() where LIFNR=@LIFNR ";
ExcuteResult = string.Format("更新供應商：{0}", item.NAME1);
}
else
{
SqlCommand = "Insert Into Authors (AuthorsCName,LAND1,CreateUser,CreateDate,ModifyUser,ModifyDate,,Rating,WorksAmount,LIFNR) VALUES (@AuthorsCName,@LAND1,'system',GETDATE(),'system',GETDATE(),0,0,@LIFNR); ";
ExcuteResult = string.Format("新增供應商：{0}", item.NAME1);
}
int num = DB.ExecuteNonQuery(SqlCommand, param);
TotalCount += num;
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()));
}
}
public static void Update()
{
int TotalCount = 0;
try
{
List <Z_MM_QUBE_MENGE> Z_MM_QUBE_MENGE_List = new List <Z_MM_QUBE_MENGE>();
ConsoleApp1.RFC.serviceSoapClient ws = new ConsoleApp1.RFC.serviceSoapClient();
//延長time out時間
ws.InnerChannel.OperationTimeout = new TimeSpan(0, 20, 0);
string P_DATE = DateTime.Now.ToString("yyyyMMdd");
string JsonString = ws.GetAllStock(P_DATE);
Z_MM_QUBE_MENGE_List = JsonConvert.DeserializeObject <List <Z_MM_QUBE_MENGE> >(JsonString);
Dictionary <string, object> empty = new Dictionary <string, object>();
DB.ExecuteNonQuery("Delete From WorksStocks", empty);
foreach (var item in Z_MM_QUBE_MENGE_List)
{
string SqlCommand = "";
string ExcuteResult = "";
DataTable dt = new DataTable();
Dictionary <string, object> param = new Dictionary <string, object>();
param.Add("MaterialsID", item.MATNR);
param.Add("WERKS", item.WERKS);
param.Add("MENGE", Double.Parse(item.MENGE));
//dt = DB.DBQuery(" SELECT * from WorksStocks where MaterialsID=@MaterialsID and WERKS=@WERKS ", param);
//if (dt.Rows.Count > 0)
//{
// SqlCommand = "UPDATE WorksStocks set MENGE=@MENGE,ModifyDate=GetDate() where MaterialsID=@MaterialsID and WERKS=@WERKS; ";
// ExcuteResult = string.Format("更新庫存數：{0}_{1}", item.MATNR, item.WERKS);
//}
//else
//{
// SqlCommand = "Insert Into WorksStocks (MaterialsID,WERKS,MENGE,CreateDate) VALUES (@MaterialsID,@WERKS,@MENGE,GetDate()); ";
// ExcuteResult = string.Format("新增庫存數：{0}_{1}", item.MATNR, item.WERKS);
//}
SqlCommand = "Insert Into WorksStocks (MaterialsID,WERKS,MENGE,CreateDate) VALUES (@MaterialsID,@WERKS,@MENGE,GetDate()); ";
ExcuteResult = string.Format("新增庫存數：{0}_{1}", item.MATNR, item.WERKS);
//更新該商品的庫存總數
SqlCommand += "UPDATE works set TotalInventory=(SELECT isnull(sum(MENGE),0) FROM [dbo].[WorksStocks] where MaterialsID=@MaterialsID) ";
SqlCommand += "Where MaterialsID =@MaterialsID; ";
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()));
}
}
public static void Update()
{
int TotalCount = 0;
try
{
List <Z_MM_QUBE_VKP0> Z_MM_QUBE_VKP0_List = new List <Z_MM_QUBE_VKP0>();
ConsoleApp1.RFC.serviceSoapClient ws = new ConsoleApp1.RFC.serviceSoapClient();
//延長time out時間
ws.InnerChannel.OperationTimeout = new TimeSpan(0, 20, 0);
string P_DATE = DateTime.Now.ToString("yyyyMMdd");
string JsonString = ws.GetAllPrice(P_DATE);
Z_MM_QUBE_VKP0_List = JsonConvert.DeserializeObject <List <Z_MM_QUBE_VKP0> >(JsonString);
foreach (var item in Z_MM_QUBE_VKP0_List)
{
string SqlCommand = " ";
string ExcuteResult = "";
DataTable dt = new DataTable();
Dictionary <string, object> param = new Dictionary <string, object>();
param.Add("MaterialsID", item.MATNR);
param.Add("Currency", item.WAERS);
param.Add("Price", Double.Parse(item.BRTWR));
param.Add("PricingDate", item.DATAB);
param.Add("Source", "SAP");
dt = DB.DBQuery("SELECT * FROM WorksPrices where MaterialsID=@MaterialsID and Currency=@Currency ", param);
if (dt.Rows.Count > 0)
{
SqlCommand = "UPDATE WorksPrices set Price=@Price,PricingDate=@PricingDate,ModifyDate=GetDate(),Source=@Source where MaterialsID=@MaterialsID and Currency=@Currency; ";
ExcuteResult = string.Format("更新售價：{0}_{1}", item.MATNR, item.WAERS);
}
else
{
SqlCommand = "Insert Into WorksPrices (MaterialsID,Currency,Price,PricingDate,CreateDate,Source) VALUES (@MaterialsID,@Currency,@Price,@PricingDate,GetDate(),@Source); ";
ExcuteResult = string.Format("更新售價：{0}_{1}", item.MATNR, item.WAERS);
}
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()));
}
}
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()));
}
}
