public static string GetAllWarehouse()
{
string result = "";
try
{
IRfcTable IRetTable = null;
List <Z_MM_QUBE_WERKS> Z_MM_QUBE_WERKS_List = new List <Z_MM_QUBE_WERKS>();
RfcConfigParameters parms = RFC.GetSettingParms();
RfcDestination rfcDest = RfcDestinationManager.GetDestination(parms);
RfcRepository rfcRep = rfcDest.Repository;
IRfcFunction IReader = rfcRep.CreateFunction("Z_MM_QUBE_WERKS");
IReader.Invoke(rfcDest);
IRetTable = IReader.GetTable("ET_WERKS");
Z_MM_QUBE_WERKS_List = IRetTable.AsQueryable().Select(x => new Z_MM_QUBE_WERKS
{
WERKS = x.GetString("WERKS") ?? "",
NAME1 = x.GetString("NAME1") ?? "",
EKORG = x.GetString("EKORG") ?? ""
}
).ToList();
using (var db = new CubeRFCEntities())
{
var WarehouseList = db.Warehouse.AsEnumerable().Select(x => x.BatchNo).ToList();
int BatchNo = WarehouseList.Count == 0?1:WarehouseList.Max() + 1;
foreach (var item in Z_MM_QUBE_WERKS_List)
{
db.Warehouse.Add(new Warehouse()
{
WERKS = item.WERKS,
NAME1 = item.NAME1,
EKORG = item.EKORG,
CreateDate = DateTime.Now,
BatchNo = BatchNo
});
}
db.SaveChanges();
}
result = JsonConvert.SerializeObject(Z_MM_QUBE_WERKS_List);
} catch (Exception e)
{
result = e.ToString();
//發送通知信給開發者
string strMailTitle = "系統發生錯誤";
string str_mailbody = e.ToString();
Mail.Send(strMailTitle, result);
}
return(result);
}
public static void AddTempTable(List <Z_MM_QUBE_MATNR> Z_MM_QUBE_MATNR_List)
{
using (var db = new CubeRFCEntities())
{
var ProductList = db.Product.AsEnumerable().Select(x => x.BatchNo).ToList();
int BatchNo = ProductList.Count == 0?1:ProductList.Max() + 1;
foreach (var item in Z_MM_QUBE_MATNR_List)
{
if (item.UDATE == "0000-00-00")
{
item.UDATE = "1900-01-01";
}
string InsertCommand = "Insert Into Product " +
"(MATNR,MAKTX,ZZCT2,LIFNR,ZZMATU,ZFVOLUME,ZZPAGE,WGBEZ,ZFPRESERVE_D,ZZSE,NETPR,WAERS,DMBTR,ERDAT,UDATE,CreateDate,BatchNo) " +
"values(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,GetDate(),@p15) ;";
db.Database.ExecuteSqlCommand(InsertCommand.ToString(), item.MATNR, item.MAKTX, item.ZZCT2, item.LIFNR, item.ZZMATU, item.ZFVOLUME, item.ZZPAGE, item.WGBEZ, item.ZFPRESERVE_D, item.ZZSE, item.NETPR, item.WAERS, item.DMBTR, item.ERDAT, item.UDATE, BatchNo);
}
#region EntityFrameWork的寫法
//foreach (var item in Z_MM_QUBE_MATNR_List)
//{
// db.Product.Add(new Product()
// {
// MATNR = item.MATNR,
// MAKTX = item.MAKTX,
// ZZCT2 = item.ZZCT2,
// LIFNR = item.LIFNR,
// ZZMATU = item.ZZMATU,
// ZFVOLUME = item.ZFVOLUME,
// ZZPAGE = item.ZZPAGE,
// WGBEZ = item.WGBEZ,
// ZFPRESERVE_D = item.ZFPRESERVE_D,
// ZZSE = item.ZZSE,
// NETPR = item.NETPR,
// WAERS = item.WAERS,
// DMBTR = item.DMBTR,
// ERDAT = DateTime.Parse(item.ERDAT),
// UDATE = item.UDATE == "0000-00-00" ? DateTime.Parse("1900-01-01") : DateTime.Parse(item.UDATE),
// CreateDate = DateTime.Now
// });
//}
//try { db.SaveChanges(); }
//catch (System.Data.Entity.Validation.DbEntityValidationException ex)
//{
// throw ex;
//}
#endregion
}
}
public static string GetAllPrice(string P_DATE)
{
string result = "";
try {
IRfcTable IRetTable = null;
List <Z_MM_QUBE_VKP0> Z_MM_QUBE_VKP0_List = new List <Z_MM_QUBE_VKP0>();
RfcConfigParameters parms = RFC.GetSettingParms();
RfcDestination rfcDest = RfcDestinationManager.GetDestination(parms);
RfcRepository rfcRep = rfcDest.Repository;
IRfcFunction IReader = rfcRep.CreateFunction("Z_MM_QUBE_VKP0");
IReader.SetValue("P_DATE", P_DATE);
IReader.Invoke(rfcDest);
IRetTable = IReader.GetTable("ET_VKP0");
Z_MM_QUBE_VKP0_List = IRetTable.AsQueryable().Select(x => new Z_MM_QUBE_VKP0
{
MATNR = x.GetString("MATNR") ?? "",
WAERS = x.GetString("WAERS") ?? "",
BRTWR = x.GetString("BRTWR") ?? "",
DATAB = x.GetString("DATAB") ?? "",
DATBI = x.GetString("DATBI") ?? ""
}
).ToList();
using (var db = new CubeRFCEntities())
{
var PricesList = db.Price.AsEnumerable().Select(x => x.BatchNo).ToList();
int BatchNo = PricesList.Count == 0?1:PricesList.Max() + 1;
StringBuilder SQLcommand = new StringBuilder();
foreach (var item in Z_MM_QUBE_VKP0_List)
{
string InsertCommand = string.Format("Insert Into Price (MATNR,WAERS,BRTWR,DATAB,DATBI,CreateDate,BatchNo) values('{0}','{1}','{2}','{3}','{4}',GetDate(),{5}) ;", item.MATNR, item.WAERS, item.BRTWR, item.DATAB, item.DATBI, BatchNo);
SQLcommand.AppendLine(InsertCommand);
}
db.Database.ExecuteSqlCommand(SQLcommand.ToString());
}
result = JsonConvert.SerializeObject(Z_MM_QUBE_VKP0_List);
} catch (Exception e)
{
result = e.ToString();
//發送通知信給開發者
string strMailTitle = "系統發生錯誤";
string str_mailbody = e.ToString();
Mail.Send(strMailTitle, result);
}
return(result);
}
public static string GetAllStock(string P_DATE)
{
string result = "";
try {
IRfcTable IRetTable = null;
List <Z_MM_QUBE_MENGE> Z_MM_QUBE_MENGE_List = new List <Z_MM_QUBE_MENGE>();
RfcConfigParameters parms = RFC.GetSettingParms();
RfcDestination rfcDest = RfcDestinationManager.GetDestination(parms);
RfcRepository rfcRep = rfcDest.Repository;
IRfcFunction IReader = rfcRep.CreateFunction("Z_MM_QUBE_MENGE");
IReader.SetValue("P_DATE", P_DATE);
IReader.Invoke(rfcDest);
IRetTable = IReader.GetTable("ET_MENGE");
Z_MM_QUBE_MENGE_List = IRetTable.AsQueryable().Select(x => new Z_MM_QUBE_MENGE
{
MATNR = x.GetString("MATNR") ?? "",
MENGE = x.GetString("MENGE") ?? "",
WERKS = x.GetString("WERKS") ?? ""
}
).ToList();
using (var db = new CubeRFCEntities())
{
var StockList = db.Stock.AsEnumerable().Select(x => x.BatchNo).ToList();
int BatchNo = StockList.Count == 0?1:StockList.Max() + 1;
StringBuilder SQLcommand = new StringBuilder();
foreach (var item in Z_MM_QUBE_MENGE_List)
{
string InsertCommand = string.Format("Insert Into Stock (MATNR,MENGE,WERKS,CreateDate,BatchNo) values('{0}','{1}','{2}',GetDate(),{3}) ;", item.MATNR, item.MENGE, item.WERKS, BatchNo);
SQLcommand.AppendLine(InsertCommand);
}
db.Database.ExecuteSqlCommand(SQLcommand.ToString());
}
result = JsonConvert.SerializeObject(Z_MM_QUBE_MENGE_List);
} catch (Exception e)
{
result = e.ToString();
string strMailTitle = "系統發生錯誤";
Mail.Send(strMailTitle, result);
}
return(result);
}
public static string GetAllVendor(string P_DATE_from, string P_DATE_TO)
{
string result = "";
try
{
IRfcTable IRetTable = null;
List <Z_MM_QUBE_LIFNR> Z_MM_QUBE_LIFNR_List = new List <Z_MM_QUBE_LIFNR>();
RfcConfigParameters parms = RFC.GetSettingParms();
RfcDestination rfcDest = RfcDestinationManager.GetDestination(parms);
RfcRepository rfcRep = rfcDest.Repository;
IRfcFunction IReader = rfcRep.CreateFunction("Z_MM_QUBE_LIFNR");
IReader.SetValue("P_DATE_from", P_DATE_from);
IReader.SetValue("P_DATE_TO", P_DATE_TO);
IReader.Invoke(rfcDest);
IRetTable = IReader.GetTable("ET_LFA1");
Z_MM_QUBE_LIFNR_List = IRetTable.AsQueryable().Select(x => new Z_MM_QUBE_LIFNR
{
LIFNR = x.GetString("LIFNR") ?? "",
NAME1 = x.GetString("NAME1") ?? "",
ZZAUTH = x.GetString("ZZAUTH") ?? "",
LAND1 = x.GetString("LAND1") ?? "",
SORTL = x.GetString("SORTL") ?? "",
ERDAT = x.GetString("ERDAT") ?? "",
UDATE = x.GetString("UDATE") ?? ""
}
).ToList();
using (var db = new CubeRFCEntities())
{
var VendorsList = db.Vendor.AsEnumerable().Select(x => x.BatchNo).ToList();
int BatchNo = VendorsList.Count == 0?1:VendorsList.Max() + 1;
foreach (var item in Z_MM_QUBE_LIFNR_List)
{
db.Vendor.Add(new Vendor()
{
LIFNR = item.LIFNR,
NAME1 = item.NAME1,
ZZAUTH = item.ZZAUTH,
LAND1 = item.LAND1,
SORTL = item.SORTL,
ERDAT = DateTime.Parse(item.ERDAT),
UDATE = item.UDATE == "0000-00-00" ? DateTime.Parse("1900-01-01") : DateTime.Parse(item.UDATE),
CreateDate = DateTime.Now,
BatchNo = BatchNo
});
}
db.SaveChanges();
}
result = JsonConvert.SerializeObject(Z_MM_QUBE_LIFNR_List);
}
catch (Exception e)
{
result = e.ToString();
//發送通知信給開發者
string strMailTitle = "系統發生錯誤";
string str_mailbody = e.ToString();
Mail.Send(strMailTitle, result);
}
return(result);
}
