internal static List<ExpenceDetail> GetSapParametersList(DataTable dt)
{
List<ExpenceDetail> oList = new List<ExpenceDetail>();
ExpenceDetail expenceDetail;
decimal tmp;
foreach (DataRow dr in dt.Rows)
{
expenceDetail = new ExpenceDetail();
foreach (PropertyInfo prop in expenceDetail.GetType().GetProperties())
{
if (dt.Columns.Contains(prop.Name))
{
switch (prop.PropertyType.ToString())
{
case "System.Decimal":
if (decimal.TryParse(dr[prop.Name].AsString(), out tmp))
{
prop.SetValue(expenceDetail, tmp, null);
}
break;
case "System.Boolean":
prop.SetValue(expenceDetail, Convert.ToBoolean(int.Parse(dr[prop.Name].AsString())), null);
break;
default:
prop.SetValue(expenceDetail, dr[prop.Name].AsString(), null);
break;
}
}
}
oList.Add(expenceDetail);
}
return oList;
}
private SapParameter GetSapParameterByWorkFlowNumber(string workFlowNumber)
{
DataTable sapDataTable = ExpatriateBenefitClaimCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber("Expatriate Benefit Claim SAP Workflow", workFlowNumber).GetDataTable();
DataTable sapItemDataTable = ExpatriateBenefitClaimCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber("ExpatriateBenefitClaimSAPItems", workFlowNumber).GetDataTable();
if (null == sapDataTable)
{
sapDataTable = new DataTable();
}
if (null == sapItemDataTable)
{
sapItemDataTable = new DataTable();
}
string cashAdvanceWorkFlowNumber = "";
string employeeID = "";
string employeeName = "";
string expenseDescription = "";
StringBuilder logStr = new StringBuilder();
if (sapDataTable != null && sapDataTable.Rows.Count > 0)
{
expenseDescription = sapDataTable.Rows[0]["ExpenseDescription"].ToString();
cashAdvanceWorkFlowNumber = sapDataTable.Rows[0]["CashAdvanceWorkFlowNumber"].ToString();
string name = sapDataTable.Rows[0]["Applicant"].ToString();
Employee employee = UserProfileUtil.GetEmployee(name.Substring(name.IndexOf('(') + 1, name.IndexOf(')') - name.IndexOf('(') - 1));
employeeID = employee.EmployeeID;
employeeName = employee.DisplayName;
}
CashAdvance[] CashAdvances = new CashAdvance[] { };
if (cashAdvanceWorkFlowNumber != "")
{
List<string> list = cashAdvanceWorkFlowNumber.Split(';').ToList<string>();
list.Remove("");
CashAdvances = new CashAdvance[list.Count];
for (int i = 0; i < list.Count; i++)
{
string caWorkFlowNumber = list[i].Substring(0, list[i].IndexOf("-"));
string caAmount = list[i].Substring(list[i].IndexOf("-") + 1);
CashAdvance cashAdvance = new CashAdvance();
cashAdvance.ID = caWorkFlowNumber;
cashAdvance.CashAmount = Int32.Parse(caAmount);
CashAdvances[i] = cashAdvance;
logStr.AppendFormat("CashAdvance：{0} cashAdvance.ID：{1} cashAdvance.CashAmount：{2}\r\n", i, caWorkFlowNumber, Int32.Parse(caAmount));
}
}
SapParameter sapParameters = new SapParameter()
{
BusAct = "RFBU",
CompCode = "CA10",
DocType = "KR",
BusArea = "0001",
Currency = "RMB",
EmployeeID = employeeID,
EmployeeName = employeeName,
ExchRate = 1,
Header = expenseDescription,
RefDocNo = workFlowNumber,
UserName = "******",
CashAdvances = CashAdvances
};
logStr.AppendFormat("WorkFlowNumber：{0}\r\nEmployeeID：{1}\r\nmployeeName：{2}\r\n", workFlowNumber, employeeID, employeeName);
List<ExpenceDetail> expenceDetailsList = new List<ExpenceDetail>();
if (sapItemDataTable != null && sapItemDataTable.Rows.Count > 0)
{
logStr.AppendFormat("====================Expence Details====================\r\n");
foreach (DataRow dr in sapItemDataTable.Rows)
{
if (dr["ExpenseType"].ToString().IndexOf("OR - employee vendor") == -1 && dr["ExpenseType"].ToString().IndexOf("OR - cash advance") == -1)
{
ExpenceDetail expenceDetail = new ExpenceDetail();
expenceDetail.AccountGL = dr["GLAccount"].ToString();
expenceDetail.Amount = decimal.Parse(dr["ItemAmount"].ToString()) < 0 ? 0 - decimal.Parse(dr["ItemAmount"].ToString()) : decimal.Parse(dr["ItemAmount"].ToString());
expenceDetail.CostCenter = dr["CostCenter"].ToString();
expenceDetail.ItemText = employeeName + " " + dr["ExpenseType"].ToString();
expenceDetailsList.Add(expenceDetail);
logStr.AppendFormat("AccountGL：{0} Amount：{1} CostCenter：{2} ItemText：{3}\r\n",
expenceDetail.AccountGL,
expenceDetail.Amount,
expenceDetail.CostCenter,
expenceDetail.ItemText);
}
}
logStr.AppendFormat("====================Expence Details====================\r\n");
}
WriteErrorLog(logStr);
sapParameters.ExpenceDetails = expenceDetailsList;
return sapParameters;
}
private SapParameter GetSapParameterByWorkFlowNumber(string workFlowNumber, out string workFlowNumberAndSAPNO)
{
DataTable sapDataTable = PaymentRequestSAPCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber("Payment Request SAP WorkFlow", workFlowNumber).GetDataTable();
DataTable sapItemDataTable = PaymentRequestSAPCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber("Payment Request SAP Items WorkFlow", workFlowNumber).GetDataTable();
DataTable sapDataTable1 = PaymentRequestSAPCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber2("PaymentRequestItems", workFlowNumber).GetDataTable();
SapParameter mSapParameters = null;
if (null == sapDataTable)
{
sapDataTable = new DataTable();
}
if (null == sapItemDataTable)
{
sapItemDataTable = new DataTable();
}
workFlowNumberAndSAPNO = "";
string employeeID = "";
string employeeName = "";
bool isFromPO = false;
string systemPONo = "";
string paidThisTime = "";
string vendorNo = "";
string vendorName = "";
string pONo = "";
string vendorCity = "";
string vendorCountry = "";
string bankCity = "";
string swiftCode = "";
string bankAccount = "";
string taxPrice = "";
string bankName = "";
string paymentDesc = "";
string currency = "";
string exchRate = "";
if (sapDataTable != null && sapDataTable.Rows.Count > 0)
{
string name = sapDataTable.Rows[0]["Applicant"].ToString();
//systemPONo = sapDataTable.Rows[0]["SystemPONo"].ToString();
systemPONo = sapDataTable1.Rows[0]["SystemPONo"].AsString();
paidThisTime = sapDataTable.Rows[0]["PaidThisTime"].ToString();
vendorNo = sapDataTable.Rows[0]["VendorNo"].AsString();
pONo = sapDataTable.Rows[0]["PONo"].ToString();
vendorName = sapDataTable.Rows[0]["VendorName"].ToString();
isFromPO = sapDataTable.Rows[0]["FromPOStatus"].ToString() == "1" ? true : false;
Employee employee = UserProfileUtil.GetEmployee(name.Substring(name.IndexOf('(') + 1, name.IndexOf(')') - name.IndexOf('(') - 1));
employeeID = employee.EmployeeID;
employeeName = employee.DisplayName;
vendorCity = sapDataTable.Rows[0]["VendorCity"].AsString();
vendorCountry = sapDataTable.Rows[0]["VendorCountry"].AsString();
bankCity = sapDataTable.Rows[0]["BankCity"].AsString();
swiftCode = sapDataTable.Rows[0]["SwiftCode"].AsString();
bankAccount = sapDataTable.Rows[0]["BankAccount"].AsString();
taxPrice = sapDataTable.Rows[0]["TaxPrice"].AsString();
bankName = sapDataTable.Rows[0]["BankName"].AsString();
paymentDesc = sapDataTable.Rows[0]["PaymentDesc"].AsString();
currency = sapDataTable.Rows[0]["Currency"].AsString();
exchRate = sapDataTable.Rows[0]["ExchRate"].AsString();
exchRate = exchRate == "" ? "1" : exchRate;
}
bool isSystemGR = false;
System.Text.StringBuilder strLog = new System.Text.StringBuilder();
if (isFromPO && systemPONo != "")
{
DataTable dt = PaymentRequestSAPCommon.GetEmployeeExpenseClaimSAPItemsByPONumber("Purchase Order Workflow", pONo).GetDataTable();
if (dt != null && dt.Rows.Count > 0)
{
string systemGR = dt.Rows[0]["IsSystemGR"].ToString();
string sapNO = dt.Rows[0]["SapNO"].AsString();
if (systemGR == "1")
{
isSystemGR = true;
workFlowNumberAndSAPNO += workFlowNumber + ";" + sapNO;
}
}
//CommonUtil.logError(string.Format("dt.Rows[0][IsSystemGR].ToString(): {0} \n. dt.Rows.Count: {1}\n", dt.Rows[0]["IsSystemGR"].ToString(), dt.Rows.Count));
if (!isSystemGR)
{
mSapParameters = new SapParameter()
{
DocDate = DateTime.Now.ToString("yyyyMMdd"),
RefDocNo = workFlowNumber, //PO单 POST到SAP时生成的SAP号
Header = workFlowNumber //工作流ID,只要是唯一值就行
};
List<StoresReceiveItem> paymentRequest = new List<StoresReceiveItem>();
DataTable sapItemDataTable1 = PaymentRequestSAPCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber1("PurchaseOrderItems", pONo)
.GetDataTable()
.AsEnumerable()
.Where(dr => dr.Field<string>("ItemCode").AsString().ToLower().IndexOf("x") != 0)
.CopyToDataTable();
foreach (DataRow dr in sapItemDataTable1.Rows)
{
//decimal returnQuantityForSAP = 0;
////(decimal.Parse(dr["TotalQuantity"].ToString()) * Int32.Parse(paidThisTime)) / 100
//if (dr["ReturnQuantityForSAP"] == null)
//{
// returnQuantityForSAP = 0;
//}
//else
//{
// returnQuantityForSAP = decimal.Parse(dr["ReturnQuantityForSAP"].ToString() == "" ? "0" : dr["ReturnQuantityForSAP"].ToString());
//}
//if (returnQuantityForSAP < 0)
//{
// returnQuantityForSAP = 0 - returnQuantityForSAP;
//}
//decimal quantity = ((decimal.Parse(dr["TotalQuantity"].ToString()) - returnQuantityForSAP) * decimal.Parse(paidThisTime)) / 100;
decimal quantity = (decimal.Parse(dr["TotalQuantity"].ToString()) * decimal.Parse(paidThisTime)) / 100;
decimal totalQuantity = Math.Round(quantity, 3);
paymentRequest.Add(new StoresReceiveItem()
{
Quantity = totalQuantity,
SapNumber = dr["SapNO"].ToString(),
ItemNo = Int32.Parse(dr["ItemNO"].ToString()),
ItemText = paymentDesc
});
strLog.AppendFormat("\r\nworkFlowNumber:{0} \r\n ", workFlowNumber);
strLog.AppendFormat("TotalQuantity:{0} \r\n ", dr["TotalQuantity"].ToString());
strLog.AppendFormat("ReturnQuantityForSAP:{0} \r\n ", dr["ReturnQuantityForSAP"].ToString());
strLog.AppendFormat("totalQuantity:{0} \r\n ", totalQuantity.ToString());
strLog.AppendFormat("SapNO:{0} \r\n ", dr["SapNO"].ToString());
strLog.AppendFormat("ItemNO:{0} \r\n ", dr["ItemNO"].ToString());
strLog.AppendFormat("paidThisTime:{0} \r\n ", paidThisTime);
}
List<ExpenceDetail> expenceDetailsList1 = new List<ExpenceDetail>();
mSapParameters.ExpenceDetails = expenceDetailsList1;
mSapParameters.StoresReceiveItems = paymentRequest;
WriteErrorLog(strLog);
return mSapParameters;
}
}
if (!isFromPO)
{
decimal exchrate = 0;
if (currency == "RMB")
{
exchrate = 1;
}
mSapParameters = new SapParameter()
{
BusAct = "RFBU",
CompCode = "CA10",
DocType = "KR",
BusArea = "0001",
Currency = currency,
EmployeeID = vendorNo == "" ? "0099999999" : vendorNo,
EmployeeName = vendorName,
ExchRate = exchrate,
Header = paymentDesc,
RefDocNo = workFlowNumber,
UserName = "******",
Vendor = vendorNo
};
if (vendorNo == "" || vendorNo == "99999999")
{
mSapParameters.VendorInfo = new Vendor()
{
BankAcct = bankAccount, //"234567893456789345678",
BankCity = bankCity, //"CN",
BankNo = swiftCode, //"104290003033",
City = vendorCity, //"上海",
Country = vendorCountry, // "CN",
Name = vendorName //"上海文思创新科技有限公司-上海文思创新科技有限公司"
};
}
System.Text.StringBuilder detailsList = new System.Text.StringBuilder();
List<ExpenceDetail> expenceDetailsList = new List<ExpenceDetail>();
if (sapItemDataTable != null && sapItemDataTable.Rows.Count > 0)
{
foreach (DataRow dr in sapItemDataTable.Rows)
{
if (dr["ExpenseType"].ToString().IndexOf("OP-Non-trade vendor") == -1
&& dr["ExpenseType"].ToString().IndexOf("GRIR vendor code") == -1)
{
ExpenceDetail expenceDetail = new ExpenceDetail();
expenceDetail.AccountGL = dr["GLAccount"].ToString();
expenceDetail.Amount = decimal.Parse(dr["ItemAmount"].ToString()) < 0 ? 0 - decimal.Parse(dr["ItemAmount"].ToString()) : decimal.Parse(dr["ItemAmount"].ToString());
//if (dr["ExpenseType"].ToString().IndexOf("Accrual") != -1
// || dr["ExpenseType"].ToString().IndexOf("Accrued") != -1
// || dr["ExpenseType"].ToString().IndexOf("Prepaid") != -1)
//{
// expenceDetail.CostCenter = "0000000000";
// expenceDetail.BusArea = dr["BusinessArea"].AsString();
//}
//else
//{
// expenceDetail.CostCenter = dr["CostCenter"].AsString() == "" ? "0000000000" : dr["CostCenter"].AsString();
// expenceDetail.BusArea = "";
//}
if (dr["ExpenseType"].ToString().ToLower() == ("Tax payable - VAT input").ToLower())
{
expenceDetail.CostCenter = "0000000000";
}
else
{
if (dr["ExpenseType"].ToString().IndexOf("Accrual") != -1
|| dr["ExpenseType"].ToString().IndexOf("Accrued") != -1
|| dr["ExpenseType"].ToString().IndexOf("Prepaid") != -1)
{
expenceDetail.CostCenter = "";
}
else
{
expenceDetail.CostCenter = dr["CostCenter"].AsString();
}
}
if (dr["BusinessArea"].AsString() != "")
{
expenceDetail.BusArea = dr["BusinessArea"].AsString();
}
//expenceDetail.ItemText = employeeName + " " + dr["ExpenseType"].ToString();
expenceDetail.ItemText = paymentDesc;
expenceDetailsList.Add(expenceDetail);
detailsList.AppendFormat("\r\nAccountGL:{0} \r\n ", dr["GLAccount"].AsString());
detailsList.AppendFormat("\r\nAmount:{0} \r\n ", dr["ItemAmount"].AsString());
detailsList.AppendFormat("\r\nExpenseType:{0} \r\n ", OriginalExpenseType[dr["ExpenseType"].AsString()].AsString());
detailsList.AppendFormat("\r\nCostCenter:{0} \r\n ", expenceDetail.CostCenter);
detailsList.AppendFormat("\r\nBusinessArea:{0} \r\n ", dr["BusinessArea"].AsString());
}
}
}
List<StoresReceiveItem> paymentRequest1 = new List<StoresReceiveItem>();
mSapParameters.StoresReceiveItems = paymentRequest1;
mSapParameters.ExpenceDetails = expenceDetailsList;
strLog.AppendFormat("\r\nvendorNo:{0} \r\n ", vendorNo);
strLog.AppendFormat("vendorName:{0} \r\n ", vendorName);
strLog.AppendFormat("workFlowNumber:{0} \r\n ", workFlowNumber);
strLog.AppendFormat("BankAcct:{0} \r\n ", bankAccount);
strLog.AppendFormat("BankCity:{0} \r\n ", bankCity);
strLog.AppendFormat("BankNo:{0} \r\n ", swiftCode);
strLog.AppendFormat("City:{0} \r\n ", vendorCity);
strLog.AppendFormat("Country:{0} \r\n ", vendorCountry);
strLog.AppendFormat("Name:{0} \r\n ", vendorName);
strLog.Append("\r\n============= Currency、Exchange Rate Info =============\r\n");
strLog.AppendFormat("Currency:{0} \r\n ", currency);
strLog.AppendFormat("Exchange Rate:{0} \r\n ", exchrate);
strLog.Append("\r\n============= Currency、Exchange Rate Info =============\r\n");
strLog.Append("\r\n============= Expence Details List =============\r\n");
strLog.Append(detailsList.ToString());
strLog.Append("\r\n============= Expence Details List =============\r\n");
WriteErrorLog(strLog);
return mSapParameters;
}
WriteErrorLog("null", "null");
return mSapParameters;
}
private SapParameter GetUSDSapParameterByWorkFlowNumber(string workFlowNumber)
{
SapParameter sapUSDParameters = null;
DataTable sapDataTable = CreditCardClaimCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber("Credit Card Claim SAP Workflow", workFlowNumber).GetDataTable();
DataTable sapItemDataTable = CreditCardClaimCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber("Credit Card Claim SAP Detail", workFlowNumber, "RMB").GetDataTable();
DataTable sapUSDItemDataTable = CreditCardClaimCommon.GetEmployeeExpenseClaimSAPItemsByWorkFlowNumber("Credit Card Claim SAP Detail", workFlowNumber, "USD").GetDataTable();
if (null == sapDataTable)
{
sapDataTable = new DataTable();
}
if (null == sapItemDataTable)
{
sapItemDataTable = new DataTable();
}
if (null == sapUSDItemDataTable)
{
sapUSDItemDataTable = new DataTable();
}
string employeeID = "";
string employeeName = "";
string expenseDescription = "";
if (sapDataTable != null && sapDataTable.Rows.Count > 0)
{
expenseDescription = sapDataTable.Rows[0]["ExpenseDescription"].ToString();
string name = sapDataTable.Rows[0]["Applicant"].ToString();
Employee employee = UserProfileUtil.GetEmployee(name.Substring(name.IndexOf('(') + 1, name.IndexOf(')') - name.IndexOf('(') - 1));
employeeID = employee.EmployeeID;
employeeName = employee.DisplayName;
if (sapUSDItemDataTable != null && sapUSDItemDataTable.Rows.Count > 0)
{
if (sapUSDItemDataTable.Rows[0]["Status"].ToString() == "success")
{
return null;
}
}
}
if (sapUSDItemDataTable != null && sapUSDItemDataTable.Rows.Count > 0
&& sapItemDataTable != null && sapItemDataTable.Rows.Count > 0)
{
workFlowNumber = workFlowNumber + "_2";
}
//USD sapParameters
if (sapUSDItemDataTable != null && sapUSDItemDataTable.Rows.Count > 0)
{
sapUSDParameters = new SapParameter()
{
BusAct = "RFBU",
CompCode = "CA10",
DocType = "KR",
BusArea = "0001",
Currency = "USD",
EmployeeID = employeeID,
EmployeeName = employeeName,
ExchRate = 0,
Header = expenseDescription,
RefDocNo = workFlowNumber,
UserName = "******"
};
List<ExpenceDetail> expenceUSDDetailsList = new List<ExpenceDetail>();
foreach (DataRow dr in sapUSDItemDataTable.Rows)
{
if (dr["ExpenseType"].ToString().IndexOf("OR - employee vendor") == -1)
{
ExpenceDetail expenceDetail = new ExpenceDetail();
expenceDetail.AccountGL = dr["GLAccount"].ToString();
expenceDetail.Amount = decimal.Parse(dr["DealAmount"].ToString());
expenceDetail.CostCenter = dr["CostCenter"].ToString();
expenceDetail.ItemText = employeeName + " " + dr["ExpenseType"].ToString();
expenceDetail.RefKey = dr["CreditCardBillID"].AsString();
expenceDetail.Currency = "USD";
expenceDetail.ExchRate = 0;
expenceUSDDetailsList.Add(expenceDetail);
}
}
sapUSDParameters.ExpenceDetails = expenceUSDDetailsList;
}
return sapUSDParameters;
}