using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using ADODB;
using System.Data.SqlClient;
//using UpdateExpenseToAX.AXExpenseService;
using UpdateExpenseToAX.NewAXExpenseService;
namespace UpdateExpenseToAX
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
long XpenseRecID,XpenseLegalEntity;
int XpenseApprovalStatus;
int XpenseLineNo;
String strFileDate,path;
String strCompany;
String sqlQuery, logUpdateQry;
DateTime dtFileDate, RunDate;
String XpenseNumber;
String ZingClaimID, ZingClaimDetailID;
RunDate = DateTime.Now;
SqlConnection conn = new SqlConnection("Data source=ABS-ADC01\\SQL2012; Database=MSDynamicsAX_Palladion;User Id=salaha.s;Password=Test@123");
//TrvExpenseLineCustomServiceClient client = new TrvExpenseLineCustomServiceClient();
TrvExpenseReportCustomServiceClient client = new TrvExpenseReportCustomServiceClient();
//TrvExpenseReportCustomServiceCreateRequest entry = new TrvExpenseReportCustomServiceCreateRequest();
//TrvExpenseReportCustomServiceCreateResponse entry1 = new TrvExpenseReportCustomServiceCreateResponse();
TrvExpenseReportContract entry = new TrvExpenseReportContract();
TrvExpenseLineCustomServiceClient client2 = new TrvExpenseLineCustomServiceClient();
TrvExpenseLineContract entry2 = new TrvExpenseLineContract();
DataTable Dt5 = new DataTable();
//>> ****************** FOR LOG ************************
dtFileDate = DateTime.Now;
strFileDate = "";
path = "";
try
{
strFileDate = String.Format("{0:dd-MM-yyyy}", dtFileDate);
path = @"C:\TimeSheetLog\ExpenseCreation_" + strFileDate + ".txt";
if(!File.Exists(path))
{
File.Create(path).Dispose();
using (TextWriter tw = new StreamWriter(path))
{
tw.WriteLine("\r\nThe Log file created successfully!");
tw.Close();
}
}
else if(File.Exists(path))
{
using (TextWriter tw = File.AppendText(path))
{
tw.WriteLine("\r\n ***** The Expense Report Details as Follows! *****");
tw.Close();
}
}
}
catch (Exception errorexp)
{
using (TextWriter tw = File.AppendText(path))
{
tw.WriteLine("\r\n Expense Report !\n {0}", errorexp.Message.ToString());
tw.Close();
}
}
//<< ********************* FOR LOG ************************
XpenseRecID = 00000;
conn.Open();
sqlQuery = "select Distinct " +
"Ex.ClaimID,HCMw.RecID,Ex.EmpID,Ex.ClaimDate,Da.ID,Ex.ProjectID,Da.RecID " + //(0:ID,1:RecID,2:ClaimDate,3:LegalEntityID)
"from ABSEXPENSDET Ex, HCMworker HCMw, HCMEmployment HCMe, Dataarea Da " +
"Where Ex.EmpID = HCMw.PersonnelNumber and HCMw.Recid = HCMe.Worker and Da.Recid = HCMe.Legalentity" ;
CallContext callcontext = new CallContext();
SqlDataAdapter Adp = new SqlDataAdapter(sqlQuery, conn);
DataTable Dt = new DataTable();
Adp.Fill(Dt);
//for(int x=0; x < Dt.Rows.Count; x++) Uncomment after Testing
for (int x = 0; x < 1; x++ ) //remove after Testing
{
try
{
ZingClaimID = Dt.Rows[x].ItemArray[0].ToString();
strCompany = Dt.Rows[x].ItemArray[4].ToString();
XpenseLineNo = 0;
callcontext.Company = strCompany;
callcontext.Language = "en-us";
entry.PersonnelNumber = Dt.Rows[x].ItemArray[2].ToString();
entry.CreatedDateTime = DateTime.Parse(Dt.Rows[x].ItemArray[3].ToString());
entry.ReferenceDataAreaId = strCompany;
//entry.CreatedDateTime = DateTime.Now;
entry.ProjId = Dt.Rows[x].ItemArray[5].ToString();
client.create(callcontext, entry);
String sqlQuery1 = "select EXPNUMBER,RECID,PROJID,LEGALENTITY,APPROVALSTATUS from TrvExpTable where recid = (select max(recid) from TrvExpTable)";
SqlDataAdapter Adp1 = new SqlDataAdapter(sqlQuery1, conn);
DataTable Dt1 = new DataTable();
Adp1.Fill(Dt1);
XpenseNumber = "";
for (int i = 0; i < Dt1.Rows.Count; i++)
{
XpenseRecID = Convert.ToInt64(Dt1.Rows[i].ItemArray[1]);
XpenseNumber = Dt1.Rows[i].ItemArray[0].ToString();
XpenseApprovalStatus = Convert.ToInt32(Dt1.Rows[i].ItemArray[4]);
XpenseLegalEntity = Convert.ToInt64(Dt1.Rows[i].ItemArray[3]);
}
Dt1.Clear();
String sqlLnQuery = "select " +
"Distinct XL.ClaimID,XL.ClaimDetailID, XL.ProjectID, XL.WBSID, XL.ClaimSubDesc," +
"XL.EmpID, XL.ClaimDate, XL.ClaimAmount, XL.CurrencyCode " +
"from ABSEXPENSDET XL WHERE " +
"XL.ClaimID = '" + ZingClaimID + "'";
SqlDataAdapter Adp2 = new SqlDataAdapter(sqlLnQuery, conn);
DataTable Dt2 = new DataTable();
Adp2.Fill(Dt2);
for (int i = 0; i < Dt2.Rows.Count; i++)
{
try
{
XpenseLineNo += 1;
DateTime TodayDate = DateTime.Now;
//>> To Fetch ProjStatus ID
String sqlQuery3 = "select LinePropertyID from ProjLinePropertySetup where " +
"CategoryCode = 2 and CategoryRelation = '" + Dt2.Rows[i].ItemArray[4].ToString() + "'";
SqlDataAdapter Adp3 = new SqlDataAdapter(sqlQuery3, conn);
DataTable Dt3 = new DataTable();
Adp3.Fill(Dt3);
if (Dt3.Rows.Count > 0)
{
for (int j = 0; j <= Dt3.Rows.Count; j++)
{
entry2.ProjStatusId = Dt3.Rows[j].ItemArray[0].ToString();
}
}
else
{
entry2.ProjStatusId = "Billable";
}
//<< To Fetch ProjStatus ID
//>> To Fetch ExpType
/*
String sqlQuery4 = "select ExPTYPE from TrvCostType where " +
"Costtype = '" + Dt2.Rows[i].ItemArray[4].ToString() + "'";
SqlDataAdapter Adp4 = new SqlDataAdapter(sqlQuery4, conn);
DataTable Dt4 = new DataTable();
Adp4.Fill(Dt4);
for (int j = 0; j <= Dt4.Rows.Count; j++)
{
//entry2.ExpType = TrvExpType.//(Dt4.Rows[j].ItemArray[0].ToString());
}
*/
//<< To Fetch ExpType
//int ExpTypeInt = 1;
entry2.CostType = Dt2.Rows[i].ItemArray[4].ToString();
entry2.InterCompanyLegalEntity = strCompany;
entry2.ReferenceDataAreaId = strCompany;
entry2.TransDate = TodayDate;
entry2.ExpNumber = XpenseNumber;
entry2.PayMethod = "Employee";
//entry.PersonnelNumber = Dt2.Rows[i].ItemArray[6].ToString();
entry2.PersonnelNumber = Dt.Rows[x].ItemArray[2].ToString();
entry2.ProjId = Dt.Rows[x].ItemArray[5].ToString();
entry2.ProjActivityNumber = Dt2.Rows[i].ItemArray[3].ToString();
entry2.LineNumber = XpenseLineNo;
entry2.LineType = TrvExpLineType.Default;
entry2.ExpType = TrvExpType.Airline;
entry2.Description = Dt2.Rows[i].ItemArray[4].ToString();
entry2.AmountCurr = Convert.ToDecimal(Dt2.Rows[i].ItemArray[7]);
entry2.ExchangeCode = Dt2.Rows[i].ItemArray[8].ToString();
entry2.ApprovalStatus = TrvAppStatus.None;
entry2.InvoiceAmt = Convert.ToDecimal(Dt2.Rows[i].ItemArray[7]);
//entry2.legalEntity = Dt.Rows[x].ItemArray[6].ToString();
entry2.ApplyAction = NoYes.No;
entry2.ProjPolicyStatus = ProjExpPolicyStatus.NoPolicy;
client2.create(callcontext, entry2);
}
catch (Exception exmain)
{
using (TextWriter tw = File.AppendText(path))
{
tw.WriteLine("\nExpense Report Line Creation Fail");
tw.WriteLine("\nEmployeeID: {0} , ErrorMessage :{1}, ProjectID : {2} ", Dt.Rows[x].ItemArray[2].ToString(), exmain.Message.ToString(), Dt.Rows[x].ItemArray[5].ToString());
tw.WriteLine("\n Category : {0}", Dt2.Rows[i].ItemArray[4].ToString());
tw.Close();
}
}
}
try
{
client.submit(callcontext, XpenseNumber, "Posted by C-Sharp");
using (TextWriter tw = File.AppendText(path))
{
tw.WriteLine("\nExpense claim Submission Success!");
tw.WriteLine("\nEmployeeID: {0} ", Dt.Rows[x].ItemArray[2].ToString());
tw.Close();
}
}
catch (Exception exmain)
{
using (TextWriter tw = File.AppendText(path))
{
tw.WriteLine("\nExpense claim Submission Fail");
tw.WriteLine("\nEmployeeID: {0} , ErrorMessage :{1} ", Dt.Rows[x].ItemArray[2].ToString(), exmain.Message.ToString());
tw.Close();
}
}
using (TextWriter tw = File.AppendText(path))
{
tw.WriteLine("\nExpense Report Creation Success!");
tw.Close();
}
}
catch (Exception exmain)
{
using (TextWriter tw = File.AppendText(path))
{
tw.WriteLine("\nExpense Report Creation Fail");
tw.WriteLine("\nEmployeeID: {0} , ErrorMessage :{1} ", Dt.Rows[x].ItemArray[2].ToString(), exmain.Message.ToString());
tw.Close();
}
}
}
logUpdateQry = "update [ABSTimeSheetLog] set RunDate = '" + RunDate.ToString("yyyy-MM-dd H:mm:ss") + "' where Details = 'Timesheet'";
SqlCommand cmd = new SqlCommand(logUpdateQry, conn);
conn.Close();
this.Close();
}
}
}
