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 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 GetAllProducts(string ProductsList)
{
string[] ProductArray = ProductsList.Split(',');
IRfcTable IRetTable = null;
List <Z_MM_QUBE_MATNR> Z_MM_QUBE_MATNR_List = new List <Z_MM_QUBE_MATNR>();
RfcConfigParameters parms = RFC.GetSettingParms();
RfcDestination rfcDest = RfcDestinationManager.GetDestination(parms);
RfcRepository rfcRep = rfcDest.Repository;
IRfcFunction IReader = rfcRep.CreateFunction("Z_MM_QUBE_MATNR");
IReader.Invoke(rfcDest);
IRfcTable Itab = IReader.GetTable("IT_MATNR");
for (int i = 0; i < ProductArray.Length; i++)
{
Itab.Append();
Itab[i].SetValue("MATNR", ProductArray[i].ToString());
}
IReader.SetValue("IT_MATNR", Itab);
IReader.Invoke(rfcDest);
IRetTable = IReader.GetTable("ET_MATNR");
Z_MM_QUBE_MATNR_List = IRetTable.AsQueryable().Select(x => new Z_MM_QUBE_MATNR
{
MATNR = x.GetString("MATNR") ?? "",
MAKTX = x.GetString("MAKTX") ?? "",
ZZCT2 = x.GetString("ZZCT2") ?? "",
LIFNR = x.GetString("LIFNR") ?? "",
ZZMATU = x.GetString("ZZMATU") ?? "",
ZFVOLUME = x.GetString("ZFVOLUME") ?? "",
ZZPAGE = x.GetString("ZZPAGE") ?? "",
WGBEZ = x.GetString("WGBEZ") ?? "",
ZFPRESERVE_D = x.GetString("ZFPRESERVE_D") ?? "",
ZZSE = x.GetString("ZZSE") ?? "",
NETPR = x.GetString("NETPR") ?? "",
WAERS = x.GetString("WAERS") ?? "",
DMBTR = x.GetString("DMBTR") ?? "",
ERDAT = x.GetString("ERDAT") ?? "",
UDATE = x.GetString("UDATE") ?? ""
}
).ToList();
AddTempTable(Z_MM_QUBE_MATNR_List);
string result = JsonConvert.SerializeObject(Z_MM_QUBE_MATNR_List);
return(result);
}
public static string GetAllProducts(string P_DATE_from, string P_DATE_TO)
{
string result = "";
try {
IRfcTable IRetTable = null;
List <Z_MM_QUBE_MATNR> Z_MM_QUBE_MATNR_List = new List <Z_MM_QUBE_MATNR>();
RfcConfigParameters parms = RFC.GetSettingParms();
RfcDestination rfcDest = RfcDestinationManager.GetDestination(parms);
RfcRepository rfcRep = rfcDest.Repository;
IRfcFunction IReader = rfcRep.CreateFunction("Z_MM_QUBE_MATNR");
IReader.SetValue("P_DATE_from", P_DATE_from);
IReader.SetValue("P_DATE_TO", P_DATE_TO);
IReader.Invoke(rfcDest);
IRetTable = IReader.GetTable("ET_MATNR");
Z_MM_QUBE_MATNR_List = IRetTable.AsQueryable().Select(x => new Z_MM_QUBE_MATNR
{
MATNR = x.GetString("MATNR") ?? "",
MAKTX = x.GetString("MAKTX") ?? "",
ZZCT2 = x.GetString("ZZCT2") ?? "",
LIFNR = x.GetString("LIFNR") ?? "",
ZZMATU = x.GetString("ZZMATU") ?? "",
ZFVOLUME = x.GetString("ZFVOLUME") ?? "",
ZZPAGE = x.GetString("ZZPAGE") ?? "",
WGBEZ = x.GetString("WGBEZ") ?? "",
ZFPRESERVE_D = x.GetString("ZFPRESERVE_D") ?? "",
ZZSE = x.GetString("ZZSE") ?? "",
NETPR = x.GetString("NETPR") ?? "",
WAERS = x.GetString("WAERS") ?? "",
DMBTR = x.GetString("DMBTR") ?? "",
ERDAT = x.GetString("ERDAT") ?? "",
UDATE = x.GetString("UDATE") ?? ""
}
).ToList();
AddTempTable(Z_MM_QUBE_MATNR_List);
result = JsonConvert.SerializeObject(Z_MM_QUBE_MATNR_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 void tableBLEorg(string numero)
{
connect();
List <Schedule_SapTable> tableBLEorg = new List <Schedule_SapTable>();
RfcDestination rfcDest = RfcDestinationManager.GetDestination(parms);
RfcRepository rfcRep = rfcDest.Repository;
IRfcFunction IReader = rfcRep.CreateFunction("RFC_READ_TABLE");
IReader.SetValue("QUERY_TABLE", "/SSCN/BL_EORG");
IReader.SetValue("ROWCOUNT", "0");
//IReader.Invoke(rfcDest);
IRfcTable optionsTable = IReader.GetTable("OPTIONS");
optionsTable.Append();
optionsTable.SetValue("TEXT", "PLANNING_ID = '" + numero + "'");
IReader.Invoke(rfcDest);
IRfcTable optionData = IReader.GetTable("DATA");
Directory.CreateDirectory("Relatorio");
tableBLEorg = optionData.AsQueryable().Select(x => new Schedule_SapTable
{
PLANNING_ID = x.GetString("WA").Substring(3, 32).TrimEnd() ?? "",
CENTRO_TRAB = x.GetString("WA").Substring(39, 8).TrimEnd() ?? ""
}
).ToList();
string s = JsonConvert.SerializeObject(tableBLEorg);
string path = Path.GetFullPath("Relatorio/pathBLEorg.txt");
System.IO.File.WriteAllText(path, s);
List <Schedule_SapTable> jsonList = JsonConvert.DeserializeObject <List <Schedule_SapTable> >(s);
if (jsonList.Count() > 0)
{
//file
CreateExcel4 = new Schedule_BLEorgExcel();
CreateExcel4.CreateWorkbook("Relatorio/DadosBLEorg.xlsx");
//Adicionar Celulas
jsonList
.Select((mandante, x) => new { mandante, position = x })
.ToList()
.ForEach(item => CreateExcel4.AddCell(item.position, item.mandante));
//Salvando dados no excel
CreateExcel4.Save();
}
}
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);
}