public string LoadBankPOSFile(byte[] formData)
{
using (IDAL dal = this.DAL)
{
dal.BeginTransaction();
try
{
_logger.Debug($"Pos Transfer file read started : {DateTime.Now.ToLongTimeString()}");
ExcelOperations exOp = new ExcelOperations(dal);
string result = exOp.ReadExceltoDataTable(formData, "Pos Transfer", 1); // filetype 1: xlsx
if (result.Length == 0)
{
DataTable dt = exOp.ExcelTable;
result = insertDataTabletoPOSTable(dt, dal, 1);
}
if (result.Length == 0)
{
dal.CommitTransaction();
}
else
{
dal.RollbackTransaction();
}
return(result);
}
catch (Exception ex)
{
dal.RollbackTransaction();
throw ex;
}
}
}
public string ZiraatLoadBankPOSFile(byte[] formData)
{
using (IDAL dal = this.DAL)
{
dal.BeginTransaction();
try
{
_logger.Debug($"Ziraat Pos Transfer file read started : {DateTime.Now.ToLongTimeString()}");
ExcelOperations exOp = new ExcelOperations(dal);
long eventId = _parameterReader.ReadEventId("System");
long organizationId = OTApplication.Context.Organization.Id;
string result = exOp.ReadExceltoDataTable(formData, "Ziraat POS", 2); // filetype 2: csv
if (result.Length == 0)
{
DataTable dt = exOp.ExcelTable;
dt.Columns.Add("ADET", typeof(decimal));
dt.Columns.Add("BORCTUTARI", typeof(decimal));
dt.Columns.Add("KOMISYON", typeof(decimal));
var newDt = dt.AsEnumerable()
.GroupBy(r => new { TERMID = r.Field <string>("TERMID"), UYENO = r.Field <string>("UYENO"), BLOKETARIHI = r.Field <DateTime>("BLOKETARIHI"), BLOKEVALORU = r.Field <DateTime>("BLOKEVALORU") })
.Select(g =>
{
var row = dt.NewRow();
row["UYENO"] = g.Key.UYENO;
row["TERMID"] = g.Key.TERMID.TrimStart(new Char[] { '0' });
row["BLOKETARIHI"] = g.Key.BLOKETARIHI;
row["BLOKEVALORU"] = g.Key.BLOKEVALORU;
row["ALACAKTUTARI"] = g.Sum(r => r.Field <decimal>("ALACAKTUTARI"));
row["ADET"] = g.Count();
row["BORCTUTARI"] = 0;
row["KOMISYON"] = 0;
return(row);
}).CopyToDataTable();
result = insertDataTabletoPOSTable(newDt, dal, 4);
}
if (result.Length == 0)
{
dal.CommitTransaction();
}
else
{
dal.RollbackTransaction();
}
return(result);
}
catch (Exception ex)
{
dal.RollbackTransaction();
throw ex;
}
}
}
public void AddProductsFromExcel(byte[] formData, long storeOrderId)
{
try
{
ExcelOperations exOp = new ExcelOperations(_dal);
string result = exOp.ReadExceltoDataTable(formData, "OrderProducts", 1);
if (result.Length == 0)
{
DataTable dt = exOp.ExcelTable;
IUniParameter prmStoreOrderId = _dal.CreateParameter("StoreOrderId", storeOrderId);
IEnumerable <StoreOrderDetail> details = _dal.List <StoreOrderDetail>("WHS_LST_STOREORDERDETAIL_SP", prmStoreOrderId).ToList();
IEnumerable <Product> products = _dal.List <Product>("PRD_LST_PRODUCT_SP").ToList();
foreach (DataRow dr in dt.Rows)
{
var detailRow = details.Where <StoreOrderDetail>(detail => detail.ProductCode == dr[0].ToString()).FirstOrDefault();
if (detailRow != null)
{
var product = products.Where <Product>(row => row.Code == dr[0].ToString()).First();
if (detailRow.StoreOrderDetailId > 0)
{
detailRow.OrderQuantity = (decimal)dr[2] / product.PackageQuantity;
_dal.Update <StoreOrderDetail>(detailRow);
}
else
{
detailRow.StoreOrder = storeOrderId;
detailRow.Product = product.ProductId;
detailRow.OrderQuantity = (decimal)dr[2] / product.PackageQuantity;
detailRow.ShippedQuantity = (decimal)dr[2] / product.PackageQuantity;
detailRow.RevisedQuantity = (decimal)dr[2] / product.PackageQuantity;
detailRow.IntakeQuantity = (decimal)dr[2] / product.PackageQuantity;
detailRow.PackageQuantity = product.PackageQuantity;
_dal.Create <StoreOrderDetail>(detailRow);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/*Section="CustomCodeRegion"*/
#region Customized
public string LoadBankStatementFile(byte[] formData, string bankName)
{
Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("tr-TR");
using (IDAL dal = this.DAL)
{
dal.BeginTransaction();
try
{
//_logger.Debug($"Bank Statement file read started : {DateTime.Now.ToLongTimeString()}");
ExcelOperations exOp = new ExcelOperations(dal);
long eventId = _parameterReader.ReadEventId("System");
long organizationId = OTApplication.Context.Organization.Id;
string result = "";
int bankId = -1;
switch (bankName)
{
case "TEB": bankId = 1; exOp.ReadExceltoDataTable(formData, "TEB Bank Statement", 1); break;
case "Vakıfbank": bankId = 2; result = exOp.ReadExceltoDataTable(formData, "Vakıf Bank Statement", 1); break;
case "ING Bank": bankId = 3; result = exOp.ReadExceltoDataTable(formData, "ING Bank Statement", 1); break;
}
//if (result.Length == 0)
//{
DataTable dt = exOp.ExcelTable;
IList <BankStatement> t = new List <BankStatement>();
foreach (DataRow dr in dt.Rows)
{
if (dr["Tarih"].GetType().Equals(System.Type.GetType("System.DateTime")))
{
BankStatement pt = new BankStatement();
pt.Bank = bankId; //Vakifbank ID
pt.Date = (DateTime)dr["Tarih"];
pt.Description = dr["Açıklama"].ToString();
pt.TransactionAmount = (decimal)dr["Tutar"];
pt.Balance = (decimal)dr["Bakiye"];
pt.Channel = dr["Kanal"].ToString();
pt.Event = eventId;
pt.Organization = organizationId;
t.Add(pt);
}
//_logger.Debug($"File read to dataobject array : {DateTime.Now.ToLongTimeString()}");
}
foreach (BankStatement rec in t)
{
dal.Create <BankStatement>(rec);
}
if (result.Length == 0)
{
dal.CommitTransaction();
}
else
{
dal.RollbackTransaction();
throw new Exception(result);
}
//}
return("");
}
catch (Exception ex)
{
dal.RollbackTransaction();
_logger.Error($"servicename : BankStatementService method : LoadINGBankStatementFile exception : {ex.ToString()}");
throw ex;
// return "Dosyayı kontrol ediniz";
}
}
}
/*Section="CustomCodeRegion"*/
#region Customized
// Keep your custom code in this region.
public string LoadWorkingHoursFile(byte[] formData)
{
using (IDAL dal = this.DAL)
{
dal.BeginTransaction();
try
{
_logger.Debug($"WorkingHour file read started : {DateTime.Now.ToLongTimeString()}");
ExcelOperations exOp = new ExcelOperations(dal);
DateTime reportDate;
// get report date
try
{
reportDate = DateTime.ParseExact(
exOp.GetSpesificCellContent(formData, "WorkingHours", 18, 8).Substring(0, 10),
"dd.MM.yyyy",
System.Globalization.CultureInfo.GetCultureInfo("en-US")
);
}
catch (Exception ex)
{
_logger.Error($"serviceName : WorkingHoursService, methodName : LoadWorkingHoursFile, Datetime parsing failed, Exception : {ex.Message}");
throw new Exception($"Datetime parsing failed, Exception : {ex.Message}");
}
string result = exOp.ReadExceltoDataTable(formData, "WorkingHours", 1); // filetype 1: xlsx
if (result.Length == 0)
{
DataTable dt = exOp.ExcelTable;
IList <WorkingHours> whFailList = new List <WorkingHours>();
WorkingHours wh = new WorkingHours();
wh.Organization = OTApplication.Context.Organization.Id;
wh.Event = _parameterReader.ReadEventId("Import Workinghours");
foreach (DataRow dr in dt.Rows)
{
// InnerRows
IList <string> openUserList = dr["ACAN"].ToString().Trim().Split('\n').ToList();
IList <string> closeUserList = dr["KAPATAN"].ToString().Trim().Split('\n').ToList();
IList <string> openingTimeList = dr["ACILISZAMANI"].ToString().Trim().Split('\n').ToList();
IList <string> closingTimeList = dr["KAPANISZAMANI"].ToString().Trim().Split('\n').ToList();
wh.StoreCode = dr["ABONENO"].ToString();
wh.StoreName = dr["ABONEADI"].ToString();
wh.Note = dr["NOTLAR"].ToString();
var processWorkingHoursResultTuple = processWorkingHours(dr, reportDate, whFailList, wh, openUserList, closeUserList, openingTimeList, closingTimeList);
if (processWorkingHoursResultTuple.Item1)
{
updateYesterdayRecord(dal, reportDate, wh, openUserList, closeUserList, openingTimeList, closingTimeList);
}
if (processWorkingHoursResultTuple.Item2)
{
insertWorkingHours(dal, reportDate, wh, openUserList, closeUserList, openingTimeList, closingTimeList);
}
}
if (whFailList.Count > 0)
{
result = $"{dt.Rows.Count } kaydın {whFailList.Count} adedi aktarılamadı. Lütfen kayıtları kontrol ediniz.";
}
if (String.IsNullOrEmpty(result))
{
_logger.Debug($"WorkingHour file read finished : {DateTime.Now.ToLongTimeString()}");
}
}
dal.CommitTransaction();
return(result);
}
catch (Exception ex)
{
dal.RollbackTransaction();
throw ex;
}
}
}
/*Section="CustomCodeRegion"*/
#region Customized
public void LoadLoomisFile(byte[] formData)
{
using (IDAL dal = this.DAL)
{
dal.BeginTransaction();
try
{
_logger.Debug($"Loomis Transfer file read started : {DateTime.Now.ToLongTimeString()}");
ExcelOperations exOp = new ExcelOperations(dal);
long eventId = _parameterReader.ReadEventId("System");
long organizationId = OTApplication.Context.Organization.Id;
string result = exOp.ReadExceltoDataTable(formData, "Loomis", 1);
DataTable dt = exOp.ExcelTable;
if (result.Length == 0 && dt.Rows.Count > 0) // if no error and there are records in excel
{
// find min max dates from excel datatable
DateTime minDate, maxDate;
minDate = (DateTime)dt.Rows[0]["SAYIMTARIHI"];
maxDate = (DateTime)dt.Rows[0]["SAYIMTARIHI"];
foreach (DataRow dr in dt.Rows)
{
DateTime saleDate = (DateTime)dr["SAYIMTARIHI"];
if (saleDate < minDate)
{
minDate = saleDate;
}
if (saleDate > maxDate)
{
maxDate = saleDate;
}
}
_logger.Debug($"Min and Max Dates found in excel file minDate = {minDate.ToString("dd.MM.yyyy")} maxDate = {maxDate.ToString("dd.MM.yyyy")}: {DateTime.Now.ToLongTimeString()}");
// read current records in Maktech DB
IUniParameter prmStartDate = dal.CreateParameter("StartDate", minDate);
IUniParameter prmEndDate = dal.CreateParameter("EndDate", maxDate);
IList <Loomis> maktechRecords = dal.List <Loomis>("ACC_LST_LOOMISALL_SP", prmStartDate, prmEndDate).ToList();
_logger.Debug($"Maktech records was read: {DateTime.Now.ToLongTimeString()}");
// process all rows to Maktech DB
foreach (DataRow dr in dt.Rows)
{
DateTime saleDate = (DateTime)dr["SAYIMTARIHI"];
int store = int.Parse(dr["HNKODU"].ToString());
decimal actualAmount = (decimal)dr["SAYILAN"];
string sealNo = dr["MUHURNO"].ToString().Trim();
if (sealNo == "0" || sealNo.Length == 0)
{
continue;
}
var storedayrec = maktechRecords.Where(o => (o.Store == store && o.LoomisDate == saleDate));
/* update aynı tarih ve mağazalı 2 kayıtın toplamını değil, sonuncusunu aldığından problem oluyor, kapatıyorum. Taylan - 2020-01-20
* // Taylan - 2020-01-17 : Bu kısım kapalıydı, niye kapattığımı hatırlamıyorum ama güncellenmesi gerektiğinden tekrar açıyorum.
* if (storedayrec.Count() > 0)
* {
* Loomis updateRec = storedayrec.First();
* if (updateRec.ActualAmount != actualAmount)
* {
* updateRec.ActualAmount = actualAmount;
* dal.Update(updateRec);
* }
* }
*/
if (storedayrec.Count() == 0)
{
Loomis createRec = new Loomis();
createRec.SaleDate = saleDate.AddDays(-1);
createRec.LoomisDate = saleDate;
createRec.Store = store;
createRec.ActualAmount = actualAmount;
if (dr["BEYANEDILEN"] != DBNull.Value)
{
createRec.DeclaredAmount = (decimal)dr["BEYANEDILEN"];
}
else
{
createRec.DeclaredAmount = null;
}
createRec.FakeAmount = (decimal)dr["GECERSIZ"];
createRec.SealNo = sealNo;
createRec.Explanation = dr["ACIKLAMA"].ToString();
createRec.MikroStatusCode = 0;
createRec.Organization = organizationId;
createRec.Event = eventId;
dal.Create(createRec);
}
}
_logger.Debug($"All rows processed : {DateTime.Now.ToLongTimeString()}");
dal.CommitTransaction();
}
else if (result.Length > 0)
{
throw new Exception(result);
}
}
catch (Exception ex)
{
dal.RollbackTransaction();
_logger.Error($" ServiceName : LoomisService, MethodName : LoadLoomisFile, Exception : {ex.Message}");
throw ex;
}
}
}
public void LoadHRFile(byte[] formData)
{
Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("tr-TR");
using (IDAL dal = this.DAL)
{
long employeeId = 0;
DataRow processrow = null;
try
{
ExcelOperations exOp = new ExcelOperations(dal);
long eventId = _parameterReader.ReadEventId("System");
long organizationId = OTApplication.Context.Organization.Id;
string result = exOp.ReadExceltoDataTable(formData, "Payroll", 1);
_logger.Debug($" Excel loaded to data table");
DataTable dt = exOp.ExcelTable;
if (result.Length == 0 && dt.Rows.Count > 0) // if no error and there are records in excel
{
// Get All Departments
IEnumerable <AccountingDepartment> departments = dal.List <AccountingDepartment>().ToList();
// process all rows to Maktech DB
foreach (DataRow dr in dt.Rows)
{
processrow = dr;
if (dr["SICILNO"] != DBNull.Value)
{
dal.BeginTransaction();
StoreOperations sop = new StoreOperations(dal);
// check if employee exists, if not exists create it or update
employeeId = long.Parse(dr["SICILNO"].ToString());
Employee e = dal.Read <Employee>(employeeId);
// find department code
string departmentName = dr["DEPARTMENT_NM"].ToString();
AccountingDepartment d = departments.Where(row => (row.DepartmentName.ToUpper() == departmentName.ToUpper())).FirstOrDefault();
#region create employee
if (e == null)
{
try
{
Employee newEmployee = new Employee();
newEmployee.EmployeeId = employeeId;
newEmployee.EmployeeName = dr["EMPLOYEE"].ToString();
newEmployee.Organization = organizationId;
newEmployee.StartDate = (DateTime)dr["START_DT"];
if (dr["QUIT_DT"] != DBNull.Value)
{
newEmployee.QuitDate = (DateTime)dr["QUIT_DT"];
}
if (dr["INCENTIVEACT_CD"] != DBNull.Value)
{
newEmployee.IncentiveActCode = int.Parse(dr["INCENTIVEACT_CD"].ToString());
}
newEmployee.WorkingType = dr["WORKINGTYPE_DSC"].ToString();
newEmployee.DepartmentName = departmentName;
if (d != null)
{
newEmployee.DepartmentCode = (int)d.AccountingDepartmentId;
}
sop.CreateEmployee(newEmployee);
// dal.Create(newEmployee);
}
catch (Exception ex)
{
_logger.Error($" ServiceName : PayrollService, MethodName : create employee, EmployeeId : {employeeId} Exception : {ex.ToString()}");
throw ex;
}
}
#endregion create employee
#region update employee
else
{
try
{
e.EmployeeName = dr["EMPLOYEE"].ToString();
e.StartDate = (DateTime)dr["START_DT"];
if (dr["QUIT_DT"] != DBNull.Value)
{
e.QuitDate = (DateTime)dr["QUIT_DT"];
}
else
{
e.QuitDate = null;
}
if (dr["INCENTIVEACT_CD"] != DBNull.Value)
{
e.IncentiveActCode = int.Parse(dr["INCENTIVEACT_CD"].ToString());
}
else
{
e.IncentiveActCode = null;
}
e.WorkingType = dr["WORKINGTYPE_DSC"].ToString();
e.DepartmentName = departmentName;
if (d != null)
{
e.DepartmentCode = (int)d.AccountingDepartmentId;
}
sop.UpdateEmployee(e);
// dal.Update(e);
}
catch (Exception ex)
{
_logger.Error($" ServiceName : PayrollService, MethodName : update employee, EmployeeId : {employeeId} Exception : {ex.ToString()}");
throw ex;
}
}
#endregion update employee
dal.CommitTransaction();
}
}
foreach (DataRow dr in dt.Rows)
{
processrow = dr;
if (dr["SICILNO"] != DBNull.Value)
{
// check if payroll record exist
employeeId = long.Parse(dr["SICILNO"].ToString());
int yr = int.Parse(dr["YEAR_CD"].ToString());
int month = int.Parse(dr["MONTH_CD"].ToString());
Payroll p = readEmployeePayroll(dal, employeeId, yr, month);
dal.BeginTransaction();
#region update payroll
if (p != null)
{
try
{
if (dr["WORKINGDAY_CNT"] != DBNull.Value)
{
p.WorkingDay = int.Parse(dr["WORKINGDAY_CNT"].ToString());
}
if (dr["WORKINGHOURS_AMT"] != DBNull.Value)
{
p.WorkingHours = (decimal)dr["WORKINGHOURS_AMT"];
}
if (dr["PAY_AMT"] != DBNull.Value)
{
p.PayAmount = (decimal)dr["PAY_AMT"];
}
if (dr["ANNUALLEAVEDAY_CNT"] != DBNull.Value)
{
p.AnnualLeave = int.Parse(dr["ANNUALLEAVEDAY_CNT"].ToString());
}
if (dr["ANNUALLEAVEHOURS_AMT"] != DBNull.Value)
{
p.AnnualLeaveHours = (decimal)dr["ANNUALLEAVEHOURS_AMT"];
}
if (dr["ANNUALLEAVEPAY_AMT"] != DBNull.Value)
{
p.AnnualLeavePay = (decimal)dr["ANNUALLEAVEPAY_AMT"];
}
if (dr["PAIDLEAVEWEXCUSEDAY_CNT"] != DBNull.Value)
{
p.PaidLeavewithExcuseDayCount = int.Parse(dr["PAIDLEAVEWEXCUSEDAY_CNT"].ToString());
}
if (dr["PAIDLEAVEWEXCUSEHOURS_AMT"] != DBNull.Value)
{
p.PaidLeavewithExcuseHours = (decimal)dr["PAIDLEAVEWEXCUSEHOURS_AMT"];
}
if (dr["PAIDLEAVEWEXCUSEPAY_AMT"] != DBNull.Value)
{
p.PaidLeavewithExcusePayAmount = (decimal)dr["PAIDLEAVEWEXCUSEPAY_AMT"];
}
if (dr["LEAVENOPAYWMEDREPDAY_CNT"] != DBNull.Value)
{
p.LeaveNoPaywithMedicalReportDayCount = int.Parse(dr["LEAVENOPAYWMEDREPDAY_CNT"].ToString());
}
if (dr["LEAVENOPAYWMEDREPHOURS_AMT"] != DBNull.Value)
{
p.LeaveNoPaywithMedicalReportHours = (decimal)dr["LEAVENOPAYWMEDREPHOURS_AMT"];
}
if (dr["LEAVENOPAYWOUTEXCUSEDAY_CNT"] != DBNull.Value)
{
p.LeaveNoPaywithoutExcuseDayCount = int.Parse(dr["LEAVENOPAYWOUTEXCUSEDAY_CNT"].ToString());
}
if (dr["LEAVENOPAYWOUTEXCUSEHOURS_AMT"] != DBNull.Value)
{
p.LeaveNoPaywithoutExcuseHours = (decimal)dr["LEAVENOPAYWOUTEXCUSEHOURS_AMT"];
}
if (dr["ABSENCEDAY_CNT"] != DBNull.Value)
{
p.AbsenceDayCount = int.Parse(dr["ABSENCEDAY_CNT"].ToString());
}
if (dr["ABSENCEHOURS_AMT"] != DBNull.Value)
{
p.AbsenceHours = (decimal)dr["ABSENCEHOURS_AMT"];
}
if (dr["LEGALHOLIDAYHOURS_AMT"] != DBNull.Value)
{
p.LegalHolidayHours = (decimal)dr["LEGALHOLIDAYHOURS_AMT"];
}
if (dr["LEGALHOLIDAYPAY_AMT"] != DBNull.Value)
{
p.LegalHolidayPayAmount = (decimal)dr["LEGALHOLIDAYPAY_AMT"];
}
if (dr["OVERTIMEHOURS_AMT"] != DBNull.Value)
{
p.OvertimeHours = (decimal)dr["OVERTIMEHOURS_AMT"];
}
if (dr["OVERTIMEPAY_AMT"] != DBNull.Value)
{
p.OvertimePayAmount = (decimal)dr["OVERTIMEPAY_AMT"];
}
if (dr["CASHINDEMNITY_AMT"] != DBNull.Value)
{
p.CashIndemnityAmount = (decimal)dr["CASHINDEMNITY_AMT"];
}
if (dr["FOODALLOWANCE_AMT"] != DBNull.Value)
{
p.FoodAllowanceAmount = (decimal)dr["FOODALLOWANCE_AMT"];
}
if (dr["FOODALLOWANCEDAY_CNT"] != DBNull.Value)
{
p.FoodAllowanceDayCount = Convert.ToInt32((decimal)dr["FOODALLOWANCEDAY_CNT"]);
}
if (dr["LEAVEPAY_AMT"] != DBNull.Value)
{
p.LeavePayAmount = (decimal)dr["LEAVEPAY_AMT"];
}
if (dr["ADVANCE_AMT"] != DBNull.Value)
{
p.AdvanceAmount = (decimal)dr["ADVANCE_AMT"];
}
if (dr["PAYCUT_AMT"] != DBNull.Value)
{
p.PayCutAmount = (decimal)dr["PAYCUT_AMT"];
}
if (dr["INSTALLMENTPAYCUT_AMT"] != DBNull.Value)
{
p.InstallmentPayCutAmount = (decimal)dr["INSTALLMENTPAYCUT_AMT"];
}
if (dr["INSURANCECUT_AMT"] != DBNull.Value)
{
p.InsuranceCutAmount = (decimal)dr["INSURANCECUT_AMT"];
}
if (dr["ALIMONYCUT_AMT"] != DBNull.Value)
{
p.AlimonyCutAmount = (decimal)dr["ALIMONYCUT_AMT"];
}
if (dr["EXECUTIONDEDUCTION1_AMT"] != DBNull.Value)
{
p.ExecutionDeduction1Amount = (decimal)dr["EXECUTIONDEDUCTION1_AMT"];
}
if (dr["EXECUTIONDEDUCTION2_AMT"] != DBNull.Value)
{
p.ExecutionDeduction2Amount = (decimal)dr["EXECUTIONDEDUCTION2_AMT"];
}
if (dr["EXECUTIONDEDUCTION3_AMT"] != DBNull.Value)
{
p.ExecutionDeduction3Amount = (decimal)dr["EXECUTIONDEDUCTION3_AMT"];
}
if (dr["INSURANCEDAY_CNT"] != DBNull.Value)
{
p.InsuranceDayCount = int.Parse(dr["INSURANCEDAY_CNT"].ToString());
}
if (dr["ASSESSMENT_AMT"] != DBNull.Value)
{
p.AssessmentAmount = (decimal)dr["ASSESSMENT_AMT"];
}
if (dr["INSURANCECUTWORKER_AMT"] != DBNull.Value)
{
p.InsuranceCutWorkerAmount = (decimal)dr["INSURANCECUTWORKER_AMT"];
}
if (dr["INSURANCECUTEMPLOYER_AMT"] != DBNull.Value)
{
p.InsuranceCutEmployerAmount = (decimal)dr["INSURANCECUTEMPLOYER_AMT"];
}
if (dr["UNEMPLOYMENTPREMIUMWORKER_AMT"] != DBNull.Value)
{
p.UnemploymentPremiumWorkerAmount = (decimal)dr["UNEMPLOYMENTPREMIUMWORKER_AMT"];
}
if (dr["UNEMPLOYMENTPREMIUMEMPLOYER_AMT"] != DBNull.Value)
{
p.UnemploymentPremiumEmployerAmount = (decimal)dr["UNEMPLOYMENTPREMIUMEMPLOYER_AMT"];
}
if (dr["STAMPTAX_AMT"] != DBNull.Value)
{
p.StampTaxAmount = (decimal)dr["STAMPTAX_AMT"];
}
if (dr["TAXINCENTIVE_AMT"] != DBNull.Value)
{
p.TaxIncentiveAmount = (decimal)dr["TAXINCENTIVE_AMT"];
}
if (dr["PREVTAXASSESSMENT_AMT"] != DBNull.Value)
{
p.PreviousTaxAssessmentAmount = (decimal)dr["PREVTAXASSESSMENT_AMT"];
}
if (dr["TAXASSESSMENT_AMT"] != DBNull.Value)
{
p.TaxAssessmentAmount = (decimal)dr["TAXASSESSMENT_AMT"];
}
if (dr["INCOMETAX_AMT"] != DBNull.Value)
{
p.IncomeTaxAmount = (decimal)dr["INCOMETAX_AMT"];
}
if (dr["INCOMETAX_RT"] != DBNull.Value)
{
p.IncomeTaxRate = (decimal)dr["INCOMETAX_RT"];
}
if (dr["TOTALWITHHOLDING_AMT"] != DBNull.Value)
{
p.TotalWithholdingAmount = (decimal)dr["TOTALWITHHOLDING_AMT"];
}
if (dr["MINLIVINGALLOWANCE_AMT"] != DBNull.Value)
{
p.MinimumLivingAllowanceAmount = (decimal)dr["MINLIVINGALLOWANCE_AMT"];
}
if (dr["NET_AMT"] != DBNull.Value)
{
p.NetAmount = (decimal)dr["NET_AMT"];
}
if (dr["TOTALGROSSREVENUE_AMT"] != DBNull.Value)
{
p.TotalGrossRevenueAmount = (decimal)dr["TOTALGROSSREVENUE_AMT"];
}
if (dr["INCENTIVESHARE5510_AMT"] != DBNull.Value)
{
p.IncentiveShare5510Amount = (decimal)dr["INCENTIVESHARE5510_AMT"];
}
if (dr["INCENTIVESHARE6111_AMT"] != DBNull.Value)
{
p.IncentiveShare6111Amount = (decimal)dr["INCENTIVESHARE6111_AMT"];
}
if (dr["INCENTIVESHARE2828_AMT"] != DBNull.Value)
{
p.IncentiveShare2828Amount = (decimal)dr["INCENTIVESHARE2828_AMT"];
}
if (dr["INCENTIVESHARE27103_AMT"] != DBNull.Value)
{
p.IncentiveShare27103Amount = (decimal)dr["INCENTIVESHARE27103_AMT"];
}
if (dr["INCENTIVESHARE14857_AMT"] != DBNull.Value)
{
p.IncentiveShare14857Amount = (decimal)dr["INCENTIVESHARE14857_AMT"];
}
if (dr["INCOMETAXINCENTIVESHARE_AMT"] != DBNull.Value)
{
p.IncomeTaxIncentiveShareAmount = (decimal)dr["INCOMETAXINCENTIVESHARE_AMT"];
}
if (dr["UNEMPLOYMENTINCENTIVESHARE_AMT"] != DBNull.Value)
{
p.UnemploymentIncentiveShareAmount = (decimal)dr["UNEMPLOYMENTINCENTIVESHARE_AMT"];
}
if (dr["SEVERANCEPAY_AMT"] != DBNull.Value)
{
p.SeverancePayAmount = (decimal)dr["SEVERANCEPAY_AMT"];
}
if (dr["UNEMPLOYMENTINCENTIVE687_AMT"] != DBNull.Value)
{
p.UnemploymentIncentive687Amount = (decimal)dr["UNEMPLOYMENTINCENTIVE687_AMT"];
}
if (dr["TOTALINSURANCEPREM_AMT"] != DBNull.Value)
{
p.TotalInsurancePremAmount = (decimal)dr["TOTALINSURANCEPREM_AMT"];
}
if (dr["TOTALUNEMPLOYMENTPREM_AMT"] != DBNull.Value)
{
p.TotalUnemploymentPremAmount = (decimal)dr["TOTALUNEMPLOYMENTPREM_AMT"];
}
if (dr["CASHDEFICIT_AMT"] != DBNull.Value)
{
p.CashDeficitAmount = (decimal)dr["CASHDEFICIT_AMT"];
}
dal.Update(p);
}
catch (Exception ex)
{
_logger.Error($" ServiceName : PayrollService, MethodName : update payroll, EmployeeId : {employeeId} Exception : {ex.ToString()}");
throw ex;
}
}
#endregion update payroll
#region create payroll
else
{
try
{
Payroll newp = new Payroll();
newp.Employee = employeeId;
newp.YearCode = yr;
newp.MonthCode = month;
newp.Organization = organizationId;
newp.Event = eventId;
if (dr["WORKINGDAY_CNT"] != DBNull.Value)
{
newp.WorkingDay = int.Parse(dr["WORKINGDAY_CNT"].ToString());
}
if (dr["WORKINGHOURS_AMT"] != DBNull.Value)
{
newp.WorkingHours = (decimal)dr["WORKINGHOURS_AMT"];
}
if (dr["PAY_AMT"] != DBNull.Value)
{
newp.PayAmount = (decimal)dr["PAY_AMT"];
}
if (dr["ANNUALLEAVEDAY_CNT"] != DBNull.Value)
{
newp.AnnualLeave = int.Parse(dr["ANNUALLEAVEDAY_CNT"].ToString());
}
if (dr["ANNUALLEAVEHOURS_AMT"] != DBNull.Value)
{
newp.AnnualLeaveHours = (decimal)dr["ANNUALLEAVEHOURS_AMT"];
}
if (dr["ANNUALLEAVEPAY_AMT"] != DBNull.Value)
{
newp.AnnualLeavePay = (decimal)dr["ANNUALLEAVEPAY_AMT"];
}
if (dr["PAIDLEAVEWEXCUSEDAY_CNT"] != DBNull.Value)
{
newp.PaidLeavewithExcuseDayCount = int.Parse(dr["PAIDLEAVEWEXCUSEDAY_CNT"].ToString());
}
if (dr["PAIDLEAVEWEXCUSEHOURS_AMT"] != DBNull.Value)
{
newp.PaidLeavewithExcuseHours = (decimal)dr["PAIDLEAVEWEXCUSEHOURS_AMT"];
}
if (dr["PAIDLEAVEWEXCUSEPAY_AMT"] != DBNull.Value)
{
newp.PaidLeavewithExcusePayAmount = (decimal)dr["PAIDLEAVEWEXCUSEPAY_AMT"];
}
if (dr["LEAVENOPAYWMEDREPDAY_CNT"] != DBNull.Value)
{
newp.LeaveNoPaywithMedicalReportDayCount = int.Parse(dr["LEAVENOPAYWMEDREPDAY_CNT"].ToString());
}
if (dr["LEAVENOPAYWMEDREPHOURS_AMT"] != DBNull.Value)
{
newp.LeaveNoPaywithMedicalReportHours = (decimal)dr["LEAVENOPAYWMEDREPHOURS_AMT"];
}
if (dr["LEAVENOPAYWOUTEXCUSEDAY_CNT"] != DBNull.Value)
{
newp.LeaveNoPaywithoutExcuseDayCount = int.Parse(dr["LEAVENOPAYWOUTEXCUSEDAY_CNT"].ToString());
}
if (dr["LEAVENOPAYWOUTEXCUSEHOURS_AMT"] != DBNull.Value)
{
newp.LeaveNoPaywithoutExcuseHours = (decimal)dr["LEAVENOPAYWOUTEXCUSEHOURS_AMT"];
}
if (dr["ABSENCEDAY_CNT"] != DBNull.Value)
{
newp.AbsenceDayCount = int.Parse(dr["ABSENCEDAY_CNT"].ToString());
}
if (dr["ABSENCEHOURS_AMT"] != DBNull.Value)
{
newp.AbsenceHours = (decimal)dr["ABSENCEHOURS_AMT"];
}
if (dr["LEGALHOLIDAYHOURS_AMT"] != DBNull.Value)
{
newp.LegalHolidayHours = (decimal)dr["LEGALHOLIDAYHOURS_AMT"];
}
if (dr["LEGALHOLIDAYPAY_AMT"] != DBNull.Value)
{
newp.LegalHolidayPayAmount = (decimal)dr["LEGALHOLIDAYPAY_AMT"];
}
if (dr["OVERTIMEHOURS_AMT"] != DBNull.Value)
{
newp.OvertimeHours = (decimal)dr["OVERTIMEHOURS_AMT"];
}
if (dr["OVERTIMEPAY_AMT"] != DBNull.Value)
{
newp.OvertimePayAmount = (decimal)dr["OVERTIMEPAY_AMT"];
}
if (dr["CASHINDEMNITY_AMT"] != DBNull.Value)
{
newp.CashIndemnityAmount = (decimal)dr["CASHINDEMNITY_AMT"];
}
if (dr["FOODALLOWANCE_AMT"] != DBNull.Value)
{
newp.FoodAllowanceAmount = (decimal)dr["FOODALLOWANCE_AMT"];
}
if (dr["FOODALLOWANCEDAY_CNT"] != DBNull.Value)
{
newp.FoodAllowanceDayCount = Convert.ToInt32((decimal)dr["FOODALLOWANCEDAY_CNT"]);
}
if (dr["LEAVEPAY_AMT"] != DBNull.Value)
{
newp.LeavePayAmount = (decimal)dr["LEAVEPAY_AMT"];
}
if (dr["ADVANCE_AMT"] != DBNull.Value)
{
newp.AdvanceAmount = (decimal)dr["ADVANCE_AMT"];
}
if (dr["PAYCUT_AMT"] != DBNull.Value)
{
newp.PayCutAmount = (decimal)dr["PAYCUT_AMT"];
}
if (dr["INSTALLMENTPAYCUT_AMT"] != DBNull.Value)
{
newp.InstallmentPayCutAmount = (decimal)dr["INSTALLMENTPAYCUT_AMT"];
}
if (dr["INSURANCECUT_AMT"] != DBNull.Value)
{
newp.InsuranceCutAmount = (decimal)dr["INSURANCECUT_AMT"];
}
if (dr["ALIMONYCUT_AMT"] != DBNull.Value)
{
newp.AlimonyCutAmount = (decimal)dr["ALIMONYCUT_AMT"];
}
if (dr["EXECUTIONDEDUCTION1_AMT"] != DBNull.Value)
{
newp.ExecutionDeduction1Amount = (decimal)dr["EXECUTIONDEDUCTION1_AMT"];
}
if (dr["EXECUTIONDEDUCTION2_AMT"] != DBNull.Value)
{
newp.ExecutionDeduction2Amount = (decimal)dr["EXECUTIONDEDUCTION2_AMT"];
}
if (dr["EXECUTIONDEDUCTION3_AMT"] != DBNull.Value)
{
newp.ExecutionDeduction3Amount = (decimal)dr["EXECUTIONDEDUCTION3_AMT"];
}
if (dr["INSURANCEDAY_CNT"] != DBNull.Value)
{
newp.InsuranceDayCount = int.Parse(dr["INSURANCEDAY_CNT"].ToString());
}
if (dr["ASSESSMENT_AMT"] != DBNull.Value)
{
newp.AssessmentAmount = (decimal)dr["ASSESSMENT_AMT"];
}
if (dr["INSURANCECUTWORKER_AMT"] != DBNull.Value)
{
newp.InsuranceCutWorkerAmount = (decimal)dr["INSURANCECUTWORKER_AMT"];
}
if (dr["INSURANCECUTEMPLOYER_AMT"] != DBNull.Value)
{
newp.InsuranceCutEmployerAmount = (decimal)dr["INSURANCECUTEMPLOYER_AMT"];
}
if (dr["UNEMPLOYMENTPREMIUMWORKER_AMT"] != DBNull.Value)
{
newp.UnemploymentPremiumWorkerAmount = (decimal)dr["UNEMPLOYMENTPREMIUMWORKER_AMT"];
}
if (dr["UNEMPLOYMENTPREMIUMEMPLOYER_AMT"] != DBNull.Value)
{
newp.UnemploymentPremiumEmployerAmount = (decimal)dr["UNEMPLOYMENTPREMIUMEMPLOYER_AMT"];
}
if (dr["STAMPTAX_AMT"] != DBNull.Value)
{
newp.StampTaxAmount = (decimal)dr["STAMPTAX_AMT"];
}
if (dr["TAXINCENTIVE_AMT"] != DBNull.Value)
{
newp.TaxIncentiveAmount = (decimal)dr["TAXINCENTIVE_AMT"];
}
if (dr["PREVTAXASSESSMENT_AMT"] != DBNull.Value)
{
newp.PreviousTaxAssessmentAmount = (decimal)dr["PREVTAXASSESSMENT_AMT"];
}
if (dr["TAXASSESSMENT_AMT"] != DBNull.Value)
{
newp.TaxAssessmentAmount = (decimal)dr["TAXASSESSMENT_AMT"];
}
if (dr["INCOMETAX_AMT"] != DBNull.Value)
{
newp.IncomeTaxAmount = (decimal)dr["INCOMETAX_AMT"];
}
if (dr["INCOMETAX_RT"] != DBNull.Value)
{
newp.IncomeTaxRate = (decimal)dr["INCOMETAX_RT"];
}
if (dr["TOTALWITHHOLDING_AMT"] != DBNull.Value)
{
newp.TotalWithholdingAmount = (decimal)dr["TOTALWITHHOLDING_AMT"];
}
if (dr["MINLIVINGALLOWANCE_AMT"] != DBNull.Value)
{
newp.MinimumLivingAllowanceAmount = (decimal)dr["MINLIVINGALLOWANCE_AMT"];
}
if (dr["NET_AMT"] != DBNull.Value)
{
newp.NetAmount = (decimal)dr["NET_AMT"];
}
if (dr["TOTALGROSSREVENUE_AMT"] != DBNull.Value)
{
newp.TotalGrossRevenueAmount = (decimal)dr["TOTALGROSSREVENUE_AMT"];
}
if (dr["INCENTIVESHARE5510_AMT"] != DBNull.Value)
{
newp.IncentiveShare5510Amount = (decimal)dr["INCENTIVESHARE5510_AMT"];
}
if (dr["INCENTIVESHARE6111_AMT"] != DBNull.Value)
{
newp.IncentiveShare6111Amount = (decimal)dr["INCENTIVESHARE6111_AMT"];
}
if (dr["INCENTIVESHARE2828_AMT"] != DBNull.Value)
{
newp.IncentiveShare2828Amount = (decimal)dr["INCENTIVESHARE2828_AMT"];
}
if (dr["INCENTIVESHARE27103_AMT"] != DBNull.Value)
{
newp.IncentiveShare27103Amount = (decimal)dr["INCENTIVESHARE27103_AMT"];
}
if (dr["INCENTIVESHARE14857_AMT"] != DBNull.Value)
{
newp.IncentiveShare14857Amount = (decimal)dr["INCENTIVESHARE14857_AMT"];
}
if (dr["INCOMETAXINCENTIVESHARE_AMT"] != DBNull.Value)
{
newp.IncomeTaxIncentiveShareAmount = (decimal)dr["INCOMETAXINCENTIVESHARE_AMT"];
}
if (dr["UNEMPLOYMENTINCENTIVESHARE_AMT"] != DBNull.Value)
{
newp.UnemploymentIncentiveShareAmount = (decimal)dr["UNEMPLOYMENTINCENTIVESHARE_AMT"];
}
if (dr["SEVERANCEPAY_AMT"] != DBNull.Value)
{
newp.SeverancePayAmount = (decimal)dr["SEVERANCEPAY_AMT"];
}
if (dr["UNEMPLOYMENTINCENTIVE687_AMT"] != DBNull.Value)
{
newp.UnemploymentIncentive687Amount = (decimal)dr["UNEMPLOYMENTINCENTIVE687_AMT"];
}
if (dr["TOTALINSURANCEPREM_AMT"] != DBNull.Value)
{
newp.TotalInsurancePremAmount = (decimal)dr["TOTALINSURANCEPREM_AMT"];
}
if (dr["TOTALUNEMPLOYMENTPREM_AMT"] != DBNull.Value)
{
newp.TotalUnemploymentPremAmount = (decimal)dr["TOTALUNEMPLOYMENTPREM_AMT"];
}
if (dr["CASHDEFICIT_AMT"] != DBNull.Value)
{
newp.CashDeficitAmount = (decimal)dr["CASHDEFICIT_AMT"];
}
// _logger.Debug($"Payroll will be created EmployeeId : {Newtonsoft.Json.JsonConvert.SerializeObject(newp)}");
dal.Create(newp);
// _logger.Debug($"Payroll created EmployeeId : {Newtonsoft.Json.JsonConvert.SerializeObject(newp)}");
}
catch (Exception ex)
{
_logger.Error($" ServiceName : PayrollService, MethodName : create payroll, EmployeeId : {employeeId} Exception : {ex.ToString()}");
throw ex;
}
}
#endregion create payroll
dal.CommitTransaction();
}
}
}
else if (result.Length > 0)
{
throw new Exception(result);
}
}
catch (Exception ex)
{
dal.RollbackTransaction();
_logger.Error($" ServiceName : PayrollService, MethodName : LoadHRFile, EmployeeId : {employeeId} Datarow : {Newtonsoft.Json.JsonConvert.SerializeObject(processrow)} Exception : {ex.ToString()}");
throw ex;
}
}
}