public static PO_HEADER GetData(SAPbobsCOM.Company oCompany, int id)
{
Utils control = new Utils();
List <PO_DETAIL> details = new List <PO_DETAIL>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select U_PrimaryNumberingPOS, U_DocumentNumberingPOS, docentry, docnum, CardCode, CardName, DocDate, DocDueDate, TaxDate from opor where docentry = " + id;
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
sql = "select t1.LineNum, ItemCode, dscription, UomEntry, UomCode, Quantity, Price, t2.WhsCode, t2.WhsName from por1 t1 " +
"join owhs t2 on t1.WhsCode = t2.WhsCode where t1.docentry = " + id;
rs = control._IDU_Recordset(oCompany, sql);
xDocDetail = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("opor").Element("row");
XElement xEleDetail = xDocDetail.Element("BOM").Element("BO").Element("por1");
foreach (var item in xEleDetail.Elements("row"))
{
PO_DETAIL model_detail = new PO_DETAIL()
{
LineNum = Convert.ToInt32(item.Element("LineNum").Value),
ItemCode = item.Element("ItemCode").Value,
Description = item.Element("dscription").Value,
UomEntry = Convert.ToInt32(item.Element("UomEntry").Value),
UomCode = item.Element("UomCode").Value,
Quantity = Convert.ToDouble(item.Element("Quantity").Value),
Price = Convert.ToDouble(item.Element("Price").Value),
WarehouseCode = item.Element("WhsCode").Value,
WarehouseName = item.Element("WhsName").Value
};
details.Add(model_detail);
}
PO_HEADER model = new PO_HEADER()
{
PrimaryNumberingPOS = xEle.Element("U_PrimaryNumberingPOS").Value,
DocumentNumberingPOS = xEle.Element("U_DocumentNumberingPOS").Value,
DocEntry = xEle.Element("docentry").Value,
DocNum = xEle.Element("docnum").Value,
CardCode = xEle.Element("CardCode").Value,
CardName = xEle.Element("CardName").Value,
DocumentDate = DateTime.ParseExact(xEle.Element("DocDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DocDueDate = DateTime.ParseExact(xEle.Element("DocDueDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
TaxDate = DateTime.ParseExact(xEle.Element("TaxDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
lines = details
};
return(model);
}
public static IT_HEADER GetData(SAPbobsCOM.Company oCompany, int id)
{
Utils control = new Utils();
List <IT_DETAIL> details = new List <IT_DETAIL>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select t0.docentry, t0.docnum, t0.DocDate, t0.TaxDate, t1.whscode, t1.whsname from OWTR t0 " +
"join owhs t1 on t0.filler = t1.whscode where t0.docentry = " + id;
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
sql = "select t1.LineNum, ItemCode, dscription, UomEntry, UomCode, Quantity, t2.WhsCode, t2.WhsName from WTR1 t1 " +
"join owhs t2 on t1.WhsCode = t2.WhsCode where t1.docentry = " + id;
rs = control._IDU_Recordset(oCompany, sql);
xDocDetail = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("OWTR").Element("row");
XElement xEleDetail = xDocDetail.Element("BOM").Element("BO").Element("WTR1");
foreach (var item in xEleDetail.Elements("row"))
{
IT_DETAIL model_detail = new IT_DETAIL()
{
LineNum = Convert.ToInt32(item.Element("LineNum").Value),
ItemCode = item.Element("ItemCode").Value,
Description = item.Element("dscription").Value,
UomEntry = Convert.ToInt32(item.Element("UomEntry").Value),
UomCode = item.Element("UomCode").Value,
Quantity = Convert.ToDouble(item.Element("Quantity").Value),
ToWarehouseCode = item.Element("WhsCode").Value,
ToWarehouseName = item.Element("WhsName").Value
};
details.Add(model_detail);
}
IT_HEADER model = new IT_HEADER()
{
DocEntry = Convert.ToInt32(xEle.Element("docentry").Value),
DocNum = xEle.Element("docnum").Value,
DocumentDate = DateTime.ParseExact(xEle.Element("DocDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
TaxDate = DateTime.ParseExact(xEle.Element("TaxDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
FromWarehouseCode = xEle.Element("whscode").Value,
FromWarehouseName = xEle.Element("whsname").Value,
lines = details
};
return(model);
}
public static List <COA> GetData(SAPbobsCOM.Company oCompany, string id)
{
Utils control = new Utils();
string sql = "AcctCode, AcctName,ActCurr,Postable,FatherNum,Levels,ActType, " +
"(CASE WHEN ActType = 'N' then 'Other' " +
" WHEN ActType = 'I' then 'Sales' " +
" WHEN ActType = 'E' then 'Expenditure'" +
" ELSE 'Not Defined'" +
" end) 'TypeName'" +
"from OACT where AcctCode = '" + id + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(rs.GetAsXML());
List <COA> list = (from data in xDoc.Element("BOM").Element("BO").Element("OACT").Elements("row")
select new COA
{
AccountCode = data.Element("AcctCode").Value,
AccountName = data.Element("AcctName").Value,
AccountCurrency = data.Element("ActCurr").Value,
PostAble = data.Element("PostAble").Value,
FatherAccount = data.Element("FatherNum").Value,
AccountType = data.Element("TypeName").Value
}).ToList();
return(list);
}
public static BP GetData(SAPbobsCOM.Company oCompany, string id)
{
Utils control = new Utils();
string sql = " select CardCode, CardName,Currency,Address,DebPayAcct,CardType, " +
" (CASE WHEN CardType = 'C' Then 'Customer' " +
" WHEN CardType = 'S' Then 'Supplier' " +
" else 'Lead' " +
" end) as 'TypeName' " +
" from ocrd where CardCode = '" + id + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("ocrd").Element("row");
BP model = new BP()
{
CardCode = xEle.Element("CardCode").Value,
CardName = xEle.Element("CardName").Value,
Address = xEle.Element("Address").Value,
Currency = xEle.Element("Currency").Value,
BPType = xEle.Element("TypeName").Value,
DebtPayAccount = xEle.Element("DebPayAcct").Value
};
return(model);
}
public static List <GRPO_HEADER> GetDataList(SAPbobsCOM.Company oCompany, DateTime FromDate, DateTime ToDate)
{
Utils control = new Utils();
List <GRPO_DETAIL> details = new List <GRPO_DETAIL>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select T0.DocEntry ,T0.DocNum as 'NumberForm',T0.DocDate as 'PostingDate',T0.DocDueDate as 'DueDate',T0.TaxDate as 'DocumentDate'" +
",T0.CardCode as 'VendorCode',T0.CardName as 'VendorName',T0.DocStatus as 'Status',T0.Comments as 'Remarks'" +
" FROM OPDN T0 where DocDate between '" + FromDate + "' and '" + ToDate + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
List <GRPO_HEADER> model = (from data in xDoc.Element("BOM").Element("BO").Element("OPDN").Elements("row")
select new GRPO_HEADER
{
DocEntry = data.Element("DocEntry").Value,
NumberForm = data.Element("NumberForm").Value,
VendorCode = data.Element("VendorCode").Value,
VendorName = data.Element("VendorName").Value,
Status = data.Element("Status").Value,
Remarks = data.Element("Remarks").Value,
PostingDate = DateTime.ParseExact(data.Element("PostingDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DueDate = DateTime.ParseExact(data.Element("DueDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DocumentDate = DateTime.ParseExact(data.Element("DocumentDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None)
}
).ToList();
return(model);
}
public static List <BP> GetData(SAPbobsCOM.Company oCompany)
{
Utils control = new Utils();
string sql = "select CardCode, CardName ,Currency,Address,DebPayAcct,CardType, " +
" (CASE WHEN CardType = 'C' Then 'Customer' " +
" WHEN CardType = 'S' Then 'Supplier' " +
" else 'Lead' " +
" end) as 'TypeName' " +
" from ocrd";
Recordset rs = control._IDU_Recordset(oCompany, sql);
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(rs.GetAsXML());
List <BP> list = (from data in xDoc.Element("BOM").Element("BO").Element("ocrd").Elements("row")
select new BP
{
CardCode = data.Element("CardCode").Value,
CardName = data.Element("CardName").Value,
Address = data.Element("Address").Value,
Currency = data.Element("Currency").Value,
BPType = data.Element("TypeName").Value,
DebtPayAccount = data.Element("DebPayAcct").Value
}).ToList();
return(list);
}
public static List <GetListStock> GetData(SAPbobsCOM.Company oCompany, string ItemGroup, DateTime UpdateFrom, DateTime UpdateTo)
{
Utils control = new Utils();
XDocument xDoc = new XDocument();
string sql = "SELECT T1.WhsCode,T2.ItmsGrpNam as 'ItemGroup',T0.ItemCode as 'ItemNo',T0.ItemName as 'ItemDescription', " +
"T0.INUoMEntry as 'UOM',T1.OnHand as 'InStock',T1.IsCommited as 'Commited',T1.OnOrder as 'Ordered', " +
"(T1.OnHand + T1.OnOrder - T1.IsCommited) as 'Available', T1.AvgPrice as 'ItemPrice',((T1.OnHand + T1.OnOrder - T1.IsCommited) * isnull(T1.AvgPrice,0)) as 'Total' " +
" FROM OITM T0" +
" LEFT JOIN OITW T1 on T1.ItemCode = T0.ItemCode" +
" LEFT JOIN OITB T2 on T2.ItmsGrpCod = T2.ItmsGrpCod " +
" Where T2.ItmsGrpNam = '" + ItemGroup + "' and T0.UpdateDate between '" + UpdateFrom + "' and '" + UpdateTo + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
List <GetListStock> List = (from data in xDoc.Element("BOM").Element("BO").Element("OITM").Elements("row")
select new GetListStock
{
WhsCode = data.Element("WhsCode").Value,
ItemGroup = data.Element("ItemGroup").Value,
ItemNo = data.Element("ItemNo").Value,
ItemDescription = data.Element("ItemDescription").Value,
UOM = data.Element("UOM").Value,
InStock = Convert.ToDecimal(data.Element("InStock").Value),
Commited = Convert.ToDecimal(data.Element("Commited").Value),
Ordered = Convert.ToDecimal(data.Element("Ordered").Value),
Available = Convert.ToDecimal(data.Element("Available").Value),
ItemPrice = Convert.ToDecimal(data.Element("ItemPrice").Value),
Total = Convert.ToDecimal(data.Element("Total").Value)
}).ToList();
return(List);
}
public List <DO_HEADER> GetData(SAPbobsCOM.Company oCompany, DateTime Fromdate, DateTime ToDate)
{
Utils control = new Utils();
List <DO_HEADER> details = new List <DO_HEADER>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select T0.DocEntry,T0.DocNum ,T0.Series,T1.SeriesName,T0.DocDate,T0.DocDueDate,T0.TaxDate,T0.CardCode as 'CustomerCode',T0.CardName as 'CustomerName', " +
"(select sum(TA.LineTotal) from DLN1 TA where TA.DocEntry = T0.DocEntry) as 'TotalBeforeDiscount',T0.DocTotal,T2.SlpName as 'SalesEmploye', T0.Comments as 'Remarks' " +
" ,T0.CntctCode as 'ContactPersonCode',T3.[Name] as 'ContactPersonName', T0.NumAtCard as 'CustomerRefNumber',T0.SlpCode, T0.DocTotal " +
"FROM ODLN T0 INNER JOIN NNM1 T1 on T1.Series = T0.Series LEFT JOIN OSLP T2 on T2.SlpCode = T0.SlpCode LEFT JOIN OCPR T3 on T3.CntctCode = T0.CntctCode " +
"Where T0.DocDate between '" + Fromdate + "' and '" + ToDate + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
// XElement xEle = xDoc.Element("BOM").Element("BO").Element("ODLN").Element("row");
List <DO_HEADER> model = (from xEle in xDoc.Element("BOM").Element("BO").Element("ODLN").Elements("row")
select new DO_HEADER
{
DocEntry = xEle.Element("DocEntry").Value,
DocNum = xEle.Element("DocNum").Value,
Series = Convert.ToInt32(xEle.Element("Series").Value),
SeriesName = xEle.Element("SeriesName").Value,
CardCode = xEle.Element("CustomerCode").Value,
CardName = xEle.Element("CustomerName").Value,
SalesPersonCode = xEle.Element("SlpCode").Value,
SalesPersonName = xEle.Element("SalesEmploye").Value,
ContactPersonCode = xEle.Element("ContactPersonCode").Value,
ContactPersonName = xEle.Element("ContactPersonName").Value,
Remarks = xEle.Element("Remarks").Value,
PostingDate = DateTime.ParseExact(xEle.Element("DocDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DocDueDate = DateTime.ParseExact(xEle.Element("DocDueDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
TaxDate = DateTime.ParseExact(xEle.Element("TaxDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
TotalTransaction = Convert.ToDouble(xEle.Element("DocTotal").Value),
TotalBeforeDiscount = Convert.ToDouble(xEle.Element("TotalBeforeDiscount").Value),
CustomerRefNumber = xEle.Element("CustomerRefNumber").Value
}).ToList();
return(model);
}
public List <ITR> GetData(Company oCompany, DateTime FromDate, DateTime ToDate)
{
Utils control = new Utils();
List <ITR> details = new List <ITR>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "SELECT T0.DocEntry, T0.DocNum as 'Number',T1.SeriesName,T0.DocDate as 'PostingDate',T0.TaxDate as 'DocumentDate',T0.Filler as 'FromWhsCode'," +
" T2.WhsName as 'FromWhsName', T0.ToWhsCode, T3.WhsName as 'ToWhsName',T4.ItemCode,T4.Dscription as 'ItemName',isnull(T5.Price,0) as 'PriceList'," +
"T0.JrnlMemo as 'JournalRemarks',T0.Address as 'ShipTo',T6.SlpName,T0.SlpCode,T4.LineNum " +
" FROM OWTQ T0 INNER JOIN NNM1 T1 on T1.Series = T0.Series INNER JOIN OWHS T2 on T2.WhsCode = T0.Filler INNER JOIN OWHS T3 on T3.WhsCode = T0.ToWhsCode " +
" INNER JOIN WTQ1 T4 on T4.DocEntry = T0.DocEntry LEFT JOIN ITM1 T5 on T5.ItemCode = T4.ItemCode and T5.PriceList = 1 LEFT JOIN OSLP T6 on T6.SlpCode = T0.SlpCode " +
" where T0.DocDate Between '" + FromDate + "' and '" + ToDate + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
List <ITR> model = (from item in xDoc.Element("BOM").Element("BO").Element("OWTQ").Elements("row")
select new ITR
{
DocEntry = Convert.ToInt32(item.Element("DocEntry").Value),
Number = Convert.ToInt32(item.Element("Number").Value),
Series = item.Element("SeriesName").Value,
PostingDate = DateTime.ParseExact(item.Element("PostingDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DocumentDate = DateTime.ParseExact(item.Element("DocumentDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
FromWarehouseCode = item.Element("FromWhsCode").Value,
FromWarehouseName = item.Element("FromWhsName").Value,
ToWarehouseCode = item.Element("ToWhsCode").Value,
ToWarehouseName = item.Element("ToWhsName").Value,
LineNum = Convert.ToInt32(item.Element("LineNum").Value),
ItemCode = item.Element("ItemCode").Value,
ItemName = item.Element("ItemName").Value,
PriceList = Convert.ToDecimal(item.Element("PriceList").Value),
ShipTo = item.Element("ShipTo").Value,
SalesEmployee = item.Element("SlpName").Value,
JournalRemarks = item.Element("JournalRemarks").Value
}
).ToList();
return(model);
}
public static ITEM GetData(SAPbobsCOM.Company oCompany, string id)
{
Utils control = new Utils();
string sql = "select ItemCode, ItemName from oitm where ItemCode = '" + id + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("oitm").Element("row");
ITEM model = new ITEM()
{
ItemCode = xEle.Element("ItemCode").Value,
ItemName = xEle.Element("ItemName").Value
};
return(model);
}
public static List <ITEM> GetData(SAPbobsCOM.Company oCompany)
{
Utils control = new Utils();
string sql = "select ItemCode, ItemName from oitm";
Recordset rs = control._IDU_Recordset(oCompany, sql);
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(rs.GetAsXML());
List <ITEM> list = (from data in xDoc.Element("BOM").Element("BO").Element("oitm").Elements("row")
select new ITEM
{
ItemCode = data.Element("ItemCode").Value,
ItemName = data.Element("ItemName").Value
}).ToList();
return(list);
}
public static List <EXR> GetEXR(SAPbobsCOM.Company oCompany, DateTime RateDate1, DateTime RateDate2)
{
Utils control = new Utils();
string sql = "select RateDate,Currency,Rate from ORTT where RateDate between '" + RateDate1.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture) + "' and '" + RateDate2.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture) + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(rs.GetAsXML());
List <EXR> list = (from data in xDoc.Element("BOM").Element("BO").Element("ORTT").Elements("row")
select new EXR
{
DateRate = DateTime.ParseExact(data.Element("RateDate").Value,
"yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None),
Rate = Convert.ToDouble(data.Element("Rate").Value),
Currency = data.Element("Currency").Value
}).ToList();
return(list);
}
public List <PROD> GetDataList(SAPbobsCOM.Company oCompany, DateTime FromDate, DateTime ToDate)
{
Utils control = new Utils();
List <PROD> details = new List <PROD>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select T0.DocEntry,T0.DocNum as 'FormNumber',T0.Status,T0.PostDate as 'OrderDate',T0.DueDate as 'DueDate',T0.Warehouse, " +
" T0.ItemCode as 'ProductNo',T0.ProdName as 'ProductDescription',T0.PlannedQty as 'PlanedQty',T0.Uom, " +
" T0.OcrCode as 'ProjectCode',T1.OcrName as 'ProjectName',T0.Comments as 'Remarks',T0.CardCode as 'VendorCode',T2.CardName as 'VendorName'" +
" from OWOR T0 inner join OOCR T1 on T0.OcrCode = T1.OcrCode INNER JOIN OCRD T2 on T2.CardCode = T0.CardCode" +
" where T0.StartDate between '" + FromDate + "' and '" + ToDate + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
List <PROD> model = (from data in xDoc.Element("BOM").Element("BO").Element("OWOR").Elements("row")
select new PROD
{
DocEntry = Convert.ToInt32(data.Element("DocEntry").Value),
FormNumber = data.Element("FormNumber").Value,
VendorCode = data.Element("VendorCode").Value,
VendorName = data.Element("VendorName").Value,
Status = data.Element("Status").Value,
warehouse = data.Element("Warehouse").Value,
ProductNo = data.Element("ProductNo").Value,
ProductDescription = data.Element("ProductDescription").Value,
plannedQty = Convert.ToDecimal(data.Element("PlanedQty").Value),
UOM = data.Element("Uom").Value,
ProjectCode = data.Element("ProjectCode").Value,
ProjectName = data.Element("ProjectName").Value,
Remarks = data.Element("Remarks").Value,
OrderDate = DateTime.ParseExact(data.Element("OrderDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DueDate = DateTime.ParseExact(data.Element("DueDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
}).ToList();
return(model);
}
public static List <EXR> GetEXR(SAPbobsCOM.Company oCompany)
{
Utils control = new Utils();
string sql = "select Convert(Date,RateDate)as'RateDate',Currency,Rate from ORTT ";
Recordset rs = control._IDU_Recordset(oCompany, sql);
System.Globalization.CultureInfo cultureinfo = new System.Globalization.CultureInfo("id-ID");
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(rs.GetAsXML());
List <EXR> list = (from data in xDoc.Element("BOM").Element("BO").Element("ORTT").Elements("row")
select new EXR
{
DateRate = DateTime.ParseExact(data.Element("RateDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
Rate = Convert.ToDouble(data.Element("Rate").Value),
Currency = data.Element("Currency").Value
}).ToList();
return(list);
}
public RTGIPROD GetInfo(Company oCompany, Int32 DocEntry, string result)
{
Utils control = new Utils();
RTGIPROD info = new RTGIPROD();
XDocument xDoc = new XDocument();
string sql = "SELECT T0.DocNum as 'Number',T3.SeriesName as 'SeriesNo',T4.DocNum as 'OrderNumber',T1.ItemCode ,T1.Dscription as 'ItemName'," +
" T1.WhsCode,T5.WhsName,T1.Quantity,T1.Price,isnull(T2.CalcPrice,0) as 'ItemCost'" +
" FROM OIGN T0 INNER JOIN IGN1 T1 on T0.DocEntry = T1.DocEntry " +
" LEFT JOIN OINM T2 on T2.CreatedBy = T0.DocEntry and T2.TransType = T0.ObjType and T2.ItemCode = T1.ItemCode and T2.BaseLine = T1.LineNum " +
" LEFT JOIN NNM1 T3 on T3.Series = T0.Series LEFT JOIN OWOR T4 on T4.DocEntry = T1.BaseEntry and T4.ObjType = T1.BaseType " +
" LEFT JOIN OWHS T5 on T5.WhsCode = T1.WhsCode " +
"where T0.Docentry = '" + DocEntry + "'";
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("OIGN").Element("row");
RTGIPROD model = new RTGIPROD()
{
ItemCode = xEle.Element("ItemCode").Value,
ItemName = xEle.Element("ItemName").Value,
OrderNumber = xEle.Element("OrderNumber").Value,
SeriesNo = xEle.Element("SeriesNo").Value,
WarehouseCode = xEle.Element("WhsCode").Value,
WarehouseName = xEle.Element("WhsName").Value,
Quantity = Convert.ToDecimal(xEle.Element("Quantity").Value),
UnitPrice = Convert.ToDecimal(xEle.Element("Price").Value),
ItemCost = Convert.ToDecimal(xEle.Element("ItemCost").Value),
value = DocEntry.ToString()
};
return(model);
}
public string GetAsXML()
{
return(recordset.GetAsXML());
}
public static GRPO_HEADER GetData(SAPbobsCOM.Company oCompany, int id)
{
Utils control = new Utils();
List <GRPO_DETAIL> details = new List <GRPO_DETAIL>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select T0.DocEntry ,T0.DocNum as 'NumberForm',T0.DocDate as 'PostingDate',T0.DocDueDate as 'DueDate',T0.TaxDate as 'DocumentDate' " +
",T0.CardCode as 'VendorCode',T0.CardName as 'VendorName',T0.DocStatus as 'Status',T0.Comments as 'Remarks' FROM OPDN T0 where T0.DocEntry = " + id;
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
sql = "Select T1.ItemCode as 'ItemNo',T1.Dscription as 'ItemDescription',T1.Quantity,T1.Price,T1.Currency,T1.LineTotal as 'Total',T1.WhsCode,T1.AcctCode as 'GLAccount',T1.OcrCode as 'CostCenter',T1.unitMsr as 'UOM'" +
",T1.BaseEntry, T1.BaseLine " +
"FROM PDN1 T1 " +
"WHERE T1.docentry = " + id;
rs = control._IDU_Recordset(oCompany, sql);
xDocDetail = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("OPDN").Element("row");
XElement xEleDetail = xDocDetail.Element("BOM").Element("BO").Element("PDN1");
foreach (var item in xEleDetail.Elements("row"))
{
GRPO_DETAIL model_detail = new GRPO_DETAIL()
{
ItemCode = item.Element("ItemNo").Value,
ItemDescription = item.Element("ItemDescription").Value,
UOM = item.Element("UOM").Value,
Quantity = Convert.ToDouble(item.Element("Quantity").Value),
Price = Convert.ToDouble(item.Element("Price").Value),
WarehouseCode = item.Element("WhsCode").Value,
GLAccount = item.Element("GLAccount").Value,
CostCenter = item.Element("CostCenter").Value,
Currency = item.Element("Currency").Value,
Total = Convert.ToDecimal(item.Element("Total").Value)
};
details.Add(model_detail);
}
GRPO_HEADER model = new GRPO_HEADER()
{
DocEntry = xEle.Element("DocEntry").Value,
NumberForm = xEle.Element("NumberForm").Value,
VendorCode = xEle.Element("VendorCode").Value,
VendorName = xEle.Element("VendorName").Value,
Status = xEle.Element("Status").Value,
Remarks = xEle.Element("Remarks").Value,
PostingDate = DateTime.ParseExact(xEle.Element("PostingDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DueDate = DateTime.ParseExact(xEle.Element("DueDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DocumentDate = DateTime.ParseExact(xEle.Element("DocumentDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
lines = details
};
return(model);
}
public static PROD GetData(SAPbobsCOM.Company oCompany, int id)
{
Utils control = new Utils();
List <PROD_DETIL> details = new List <PROD_DETIL>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select T0.DocEntry,T0.DocNum as 'FormNumber',T0.Status,T0.PostDate as 'OrderDate',T0.DueDate as 'DueDate',T0.Warehouse, " +
" T0.ItemCode as 'ProductNo',T0.ProdName as 'ProductDescription',T0.PlannedQty as 'PlanedQty',T0.Uom, " +
" T0.OcrCode as 'ProjectCode',T1.OcrName as 'ProjectName',T0.Comments as 'Remarks'" +
" from OWOR T0 inner join OOCR T1 on T0.OcrCode = T1.OcrCode " +
" where T0.DocEntry = " + id;
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
sql = "select T1.DocEntry, T1.LineNum,T1.ItemCode,T2.ItemName,T1.BaseQty,T1.PlannedQty,T1.IssuedQty,T1.IssueType,T1.wareHouse,T1.Project " +
" from WOR1 T1 inner join OITM T2 on T2.ItemCode = T1.ItemCode where T1.DocEntry = " + id;
rs = control._IDU_Recordset(oCompany, sql);
xDocDetail = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("OWOR").Element("row");
XElement xEleDetail = xDocDetail.Element("BOM").Element("BO").Element("WOR1");
foreach (var item in xEleDetail.Elements("row"))
{
PROD_DETIL model_detail = new PROD_DETIL()
{
ItemCode = item.Element("ItemNo").Value,
ItemName = item.Element("ItemName").Value,
BaseQty = Convert.ToDecimal(item.Element("BaseQty").Value),
PlannedQty = Convert.ToDecimal(item.Element("PlannedQty").Value),
IssueQty = Convert.ToDecimal(item.Element("Price").Value),
warehouse = item.Element("WhsCode").Value,
project = item.Element("GLAccount").Value,
};
details.Add(model_detail);
}
PROD model = new PROD()
{
DocEntry = Convert.ToInt32(xEle.Element("DocEntry").Value),
FormNumber = xEle.Element("FormNumber").Value,
Status = xEle.Element("Status").Value,
warehouse = xEle.Element("OrderDate").Value,
ProjectCode = xEle.Element("ProjectCode").Value,
ProjectName = xEle.Element("ProjectName").Value,
ProductNo = xEle.Element("ProductNo").Value,
ProductDescription = xEle.Element("ProductDescription").Value,
plannedQty = Convert.ToDecimal(xEle.Element("plannedQty").Value),
Remarks = xEle.Element("Remarks").Value,
UOM = xEle.Element("UOM").Value,
OrderDate = DateTime.ParseExact(xEle.Element("OrderDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DueDate = DateTime.ParseExact(xEle.Element("DueDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
Detil = details
};
return(model);
}
public static DO_HEADER GetData(SAPbobsCOM.Company oCompany, int id)
{
Utils control = new Utils();
List <DO_DETAIL> details = new List <DO_DETAIL>();
XDocument xDoc = new XDocument();
XDocument xDocDetail = new XDocument();
string sql = "select T0.DocEntry,T0.DocNum ,T0.Series,T1.SeriesName,T0.DocDate,T0.DocDueDate,T0.TaxDate,T0.CardCode,T0.CardName, " +
"(select sum(TA.LineTotal) from DLN1 TA where TA.DocEntry = T0.DocEntry) as 'TotalBeforeDiscount',T0.DocTotal,T0.SlpCode,T2.SlpName as 'SalesEmploye', T0.Comments as 'Remarks' " +
" ,T0.CntctCode as 'ContactPersonCode',T3.[Name] as 'ContactPersonName' " +
"FROM ODLN T0 INNER JOIN NNM1 T1 on T1.Series = T0.Series LEFT JOIN OSLP T2 on T2.SlpCode = T0.SlpCode LEFT JOIN OCPR T3 on T3.CntctCode = T0.CntctCode" +
" Where T0.DocEntry = " + id;
Recordset rs = control._IDU_Recordset(oCompany, sql);
xDoc = XDocument.Parse(rs.GetAsXML());
sql = "select T0.LineNum,T0.ItemCode,T0.Dscription as 'ItemName',T0.Quantity,T0.Price,T0.DiscPrcnt,(T0.PriceBefDi * T0.DiscPrcnt) as 'DiscSum',T0.LineTotal, " +
" T0.WhsCode as 'WarehouseCode',T4.WhsName 'WarehouseName',T1.UomEntry as 'UOMCode',T1.UomName as 'UOMName', " +
" T0.OcrCode,T2.OcrName,T0.Project,T3.PrjName " +
" FROM DLN1 T0 LEFT JOIN OUOM T1 on T0.UomEntry = T0.UomEntry LEFT JOIN OOCR T2 on T2.OcrCode = T0.OcrCode LEFT JOIN OPRJ T3 on T3.PrjCode = T0.Project" +
" LEFT JOIN OWHS T4 on T4.WhsCode = T0.WhsCode " +
" WHERE T0.DocEntry = " + id;
rs = control._IDU_Recordset(oCompany, sql);
xDocDetail = XDocument.Parse(rs.GetAsXML());
XElement xEle = xDoc.Element("BOM").Element("BO").Element("ODLN").Element("row");
XElement xEleDetail = xDocDetail.Element("BOM").Element("BO").Element("DLN1");
foreach (var item in xEleDetail.Elements("row"))
{
DO_DETAIL model_detail = new DO_DETAIL()
{
LineNum = Convert.ToInt32(item.Element("LineNum").Value),
ItemCode = item.Element("ItemCode").Value,
ItemName = item.Element("ItemName").Value,
UomEntry = Convert.ToInt32(item.Element("UOMCode").Value),
UomCode = item.Element("UOMName").Value,
Quantity = Convert.ToDouble(item.Element("Quantity").Value),
Price = Convert.ToDouble(item.Element("Price").Value),
WarehouseCode = item.Element("WarehouseCode").Value,
WarehouseName = item.Element("WarehouseName").Value,
DiscountPercent = Convert.ToDouble(item.Element("DiscPrcnt").Value),
DiscountSum = Convert.ToDouble(item.Element("DiscSum").Value),
LineTotal = Convert.ToDouble(item.Element("LineTotal").Value),
CostCenterCode = item.Element("OcrCode").Value,
CostCenterName = item.Element("OcrName").Value,
ProjectCode = item.Element("Project").Value,
ProjectName = item.Element("PrjName").Value
};
details.Add(model_detail);
}
DO_HEADER model = new DO_HEADER()
{
DocEntry = xEle.Element("DocEntry").Value,
DocNum = xEle.Element("DocNum").Value,
CustomerCode = xEle.Element("CardCode").Value,
CustomerName = xEle.Element("CardName").Value,
SalesPersonCode = xEle.Element("SlpCode").Value,
SalesPersonName = xEle.Element("SalesEmploye").Value,
PostingDate = DateTime.ParseExact(xEle.Element("DocDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DocDueDate = DateTime.ParseExact(xEle.Element("DocDueDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
DocumentDate = DateTime.ParseExact(xEle.Element("TaxDate").Value, "yyyyMMdd",
CultureInfo.InvariantCulture, DateTimeStyles.None),
TotalTransaction = Convert.ToDouble(xEle.Element("DocTotal").Value),
Lines = details
};
return(model);
}