public void Update(Branch branch)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] fields = { "BranchCode", "BranchName", "Region", "BranchHead", "ViceHead", "Address", "Phone", "Fax", "Email",
"UMR", "FuelAllowance", "LunchAllowance", "TransportAllowance", "IsActive" };
object[] values = { branch.BranchCode, branch.BranchName, branch.Region, branch.BranchHead, branch.ViceHead,
branch.Address, branch.Phone, branch.Fax, branch.Email, branch.UMR, branch.FuelAllowance,branch.LunchAllowance,
branch.TransportAllowance, branch.IsActive == true?1:0 };
Query q = new Query().Select(fields).From(tableName).Update(values)
.Where("ID").Equal("{" + branch.ID + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public string IsOccupationUsedByEmployee(Guid occupationId)
{
string employeeCode = string.Empty;
string employeeName = string.Empty;
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string sql = "SELECT e.EmployeeCode, e.EmployeeName "
+ "FROM EmployeeOccupation eo INNER JOIN Employee e ON eo.EmployeeId = e.ID "
+ "WHERE eo.OccupationId='{" + occupationId + "}' ";
using (var rdr = em.ExecuteReader(sql))
{
if (rdr.Read())
{
employeeCode = rdr["EmployeeCode"].ToString();
employeeName = rdr["EmployeeName"].ToString();
}
}
}
if (employeeCode != "" && employeeName != null)
{
return(employeeCode + " - " + employeeName);
}
else
{
return(employeeName);
}
}
public void Update(Incentive incentive)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] fields = { "MonthPeriod", "YearPeriod", "EmployeeId",
"Amount", "AmountInWords", "Notes",
"Branch", "Department",
"IsTransfer", "BankName", "AccountNumber",
"IsIncludePayroll", "IsPaid",
"ModifiedDate", "ModifiedBy" };
object[] values = { incentive.MonthPeriod, incentive.YearPeriod, incentive.EmployeeId,
incentive.Amount, incentive.AmountInWords, incentive.Notes,
incentive.Branch, incentive.Department,
incentive.IsTransfer == true?1:0, incentive.BankName, incentive.AccountNumber,
incentive.IsIncludePayroll == true?1:0, incentive.IsPaid == true?1:0,
DateTime.Now.ToShortDateString(), Store.ActiveUser };
Query q = new Query().Select(fields).From(tableName).Update(values)
.Where("ID").Equal("{" + incentive.ID + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(Guid employeeId, List <EmployeeStatus> employeeStatus)
{
Transaction tx = null;
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
tx = em.BeginTransaction();
Delete(em, tx, employeeId);
foreach (var status in employeeStatus)
{
status.EmployeeId = employeeId;
string[] columns = { "ID", "EmployeeId", "EffectiveDate", "IsEnd", "EndDate", "Status", "PaymentType" };
object[] values = { Guid.NewGuid(), status.EmployeeId, status.EffectiveDate.ToShortDateString(), status.IsEnd == true?1:0,
status.EndDate.ToShortDateString(), status.Status, status.PaymentType };
var q = new Query().Select(columns).From(tableName).Insert(values);
em.ExecuteNonQuery(q.ToSql(), tx);
}
tx.Commit();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
public void Update(PTKP ptkp)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] fields = { "EffectiveDate", "PTKPCode", "PTKPName",
"TaxValue", "MaritalValue", "ChildValue",
"Total", "NumberOfChild" };
object[] values = { ptkp.EffectiveDate.ToShortDateString(), ptkp.PTKPCode, ptkp.PTKPName,
ptkp.TaxValue, ptkp.MaritalValue, ptkp.ChildValue,
ptkp.Total, ptkp.NumberOfChild };
Query q = new Query().Select(fields).From(tableName).Update(values)
.Where("ID").Equal("{" + ptkp.ID + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(PayablePayment payablePayment)
{
Transaction tx = null;
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
tx = em.BeginTransaction();
string[] columns = { "PaymentCode", "PaymentDate", "TotalCash", "TotalBank", "TotalGiro", "TotalCorrection", "Notes",
"ModifiedDate", "ModifiedBy" };
object[] values = { payablePayment.PaymentCode, payablePayment.PaymentDate, payablePayment.TotalCash, payablePayment.TotalBank,
payablePayment.TotalGiro, payablePayment.TotalCorrection, payablePayment.Notes,
DateTime.Now.ToShortDateString(), Store.ActiveUser };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("ID").Equal("{" + payablePayment.ID + "}");
em.ExecuteNonQuery(q.ToSql(), tx);
////detail dihapus -> update status = false
var list = payablePaymentItemRepository.GetByPayablePaymentId(payablePayment.ID);
foreach (var payableItem in list)
{
salesRepository.UpdateStatus(em, tx, payableItem.SalesId, false);
payableBalanceRepository.UpdateStatusFromPayment(em, tx, payableItem.Sales.Code, false);
}
payablePaymentItemRepository.Delete(em, tx, payablePayment.ID);
foreach (var payablePaymentItem in payablePayment.PayablePaymentItems)
{
payablePaymentItem.PayablePaymentId = payablePayment.ID;
payablePaymentItemRepository.Save(em, tx, payablePaymentItem);
//update status = lunas
salesRepository.UpdateStatus(em, tx, payablePaymentItem.SalesId, true);
//update status = lunas
payableBalanceRepository.UpdateStatusFromPayment(em, tx, payablePaymentItem.Sales.Code, true);
}
UpdateGrandTotal(em, tx, payablePayment.ID, payablePayment.GrandTotal);
tx.Commit();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
public List <Sales> SearchStatusFalse(string value)
{
List <Sales> sales = new List <Sales>();
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string sql = "SELECT Sales.ID, Sales.SalesCode, Sales.SalesDate, Sales.CustomerId, Customer.CustomerName, Customer.Address, "
+ "Sales.SalesmanId, Salesman.SalesmanName,Sales.PaymentMethod, Sales.Status, Sales.Notes,Sales.GrandTotal, "
+ "Sales.CreatedDate, Sales.ModifiedDate, Sales.CreatedBy, Sales.ModifiedBy, "
+ "Sales.AmountInWords, Sales.DueDate, Sales.PrintCounter, Sales.TermOfPayment "
+ "FROM (Sales INNER JOIN Customer ON Sales.CustomerId = Customer.ID) "
+ "INNER JOIN Salesman ON Sales.SalesmanId = Salesman.ID "
+ "WHERE Sales.Status=false "
+ "AND (Sales.SalesCode like '%" + value + "%' "
+ "OR Sales.Notes like '%" + value + "%' "
+ "OR Customer.CustomerName like '%" + value + "%' "
+ "OR Salesman.SalesmanName like '%" + value + "%') "
+ " ORDER BY Sales.SalesCode DESC";
sales = em.ExecuteList <Sales>(sql, new SalesMapper());
}
return(sales);
}
public void Delete(Guid id)
{
Transaction tx = null;
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
tx = em.BeginTransaction();
var q = new Query().From(tableName).Delete()
.Where("ID").Equal("{" + id + "}");
employeeFamilyRepository.Delete(em, tx, id);
employeeDepatmentRepository.Delete(em, tx, id);
employeeGradeRepository.Delete(em, tx, id);
employeeOccupationRepository.Delete(em, tx, id);
employeePrincipalRepository.Delete(em, tx, id);
employeeStatusRepository.Delete(em, tx, id);
employeeInsuranceRepository.Delete(em, tx, id);
employeeSalaryRepository.Delete(em, tx, id);
em.ExecuteNonQuery(q.ToSql(), tx);
tx.Commit();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
public string GenerateCode(string branchName)
{
string code = string.Empty;
int counter = 0;
using (var em = EntityManagerFactory.CreateInstance(ds))
{
var q = new Query().From("Branch").Where("BranchName").Equal(branchName);
var branch = em.ExecuteObject <Branch>(q.ToSql(), new BranchMapper());
if (branch != null)
{
counter = branch.EmployeeCounter;
}
if (counter == 0)
{
code = "000001";
}
else
{
counter = counter + 1;
code = counter.ToString("D6");
}
}
return(code);
}
public void SaveFromClosingPeriod(DebtBalance debtBalance)
{
Transaction tx = null;
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
Guid ID = Guid.NewGuid();
tx = em.BeginTransaction();
string[] columns = { "ID", "BalanceYear", "BalanceMonth", "PurchaseCode", "PurchaseDate", "SupplierId",
"PaymentMethod", "GrandTotal", "IsStatus", "Notes", "AmountInWords", "DueDate", "TermOfPayment",
"CreatedDate", "ModifiedDate" };
object[] values = { ID, debtBalance.BalanceYear, debtBalance.BalanceMonth, debtBalance.PurchaseCode, debtBalance.PurchaseDate.ToShortDateString(), debtBalance.SupplierId,
debtBalance.PaymentMethod, debtBalance.GrandTotal, debtBalance.IsStatus == true?1:0, debtBalance.Notes,
debtBalance.AmountInWords, debtBalance.DueDate.ToShortDateString(), debtBalance.TermOfPayment,
DateTime.Now.ToShortDateString(), DateTime.Now.ToShortDateString() };
var q = new Query().Select(columns).From(tableName).Insert(values);
em.ExecuteNonQuery(q.ToSql(), tx);
tx.Commit();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
public void UpdateHeader(Employee employee)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] columns = { "OldEmployeeCode", "EmployeeName", "BirthPlace", "BirthDate", "Gender", "Religion", "IsTransfer",
"BankName", "AccountNumber", "MaritalStatus", "NumberOfChilds", "IsInsurance", "IsTax", "NPWP", "PTKPId",
"IsPrincipal", "IsFuelAllowance", "StartDate", "EndDate", "IsActive",
"CreatedDate", "CreatedBy", "ModifiedDate", "ModifiedBy" };
object[] values = { employee.OldEmployeeCode, employee.EmployeeName, employee.BirthPlace, employee.BirthDate.ToShortDateString(),
employee.Gender == true?1:0, employee.Religion, employee.IsTransfer == true?1:0, employee.BankName, employee.AccountNumber,
employee.MaritalStatus == true?1:0, employee.NumberOfChilds, employee.IsInsurance == true?1:0, employee.IsTax == true?1:0, employee.NPWP,
employee.PTKPId, employee.IsPrincipal == true?1:0, employee.IsFuelAllowance == true?1:0, employee.StartDate.ToShortDateString(),
employee.EndDate.ToShortDateString(), employee.IsActive == true?1:0,
DateTime.Now.ToShortDateString(), Store.ActiveUser, DateTime.Now.ToShortDateString(), Store.ActiveUser };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("EmployeeCode").Equal(employee.EmployeeCode);
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateStockCorrectionCounter(int month, int year)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
int stockCorrectionCounter = 0;
var recordCounter = GetByMonthAndYear(month, year);
if (recordCounter != null)
{
stockCorrectionCounter = recordCounter.StockCorrectionCounter;
}
string[] columns = { "StockCorrectionCounter" };
object[] values = { stockCorrectionCounter + 1 };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("ActiveMonth").Equal(month)
.And("ActiveYear").Equal(year);
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(RecordCounter recordCounter)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] columns = { "ActiveYear", "ActiveMonth", "SalesCounter", "PurchaseCounter", "ExpenseCounter",
"PayablePaymentCounter", "DebtPaymentCounter", "BillReceiptCounter", "StockCorrectionCounter",
"ClosingStatus", "ModifiedDate" };
object[] values = { recordCounter.ActiveYear, recordCounter.ActiveMonth,
recordCounter.SalesCounter, recordCounter.PurchaseCounter, recordCounter.ExpenseCounter,
recordCounter.PayablePaymentCounter, recordCounter.DebtPaymentCounter, recordCounter.BillReceiptCounter,
recordCounter.StockCorrectionCounter, recordCounter.ClosingStatus == true?1:0,
DateTime.Now.ToShortDateString() };
var q = new Query().Select(columns).From(tableName).Update(values).Where("ID").Equal("{" + recordCounter.ID + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(Absence absence)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] fields = { "MonthPeriod", "YearPeriod", "AbsenceStartDate", "AbsenceEndDate",
"EmployeeId", "WorkDay", "OnLeaveDay", "OffDay", "Total","Branch", "Department",
"ModifiedDate", "ModifiedBy" };
object[] values = { absence.MonthPeriod, absence.YearPeriod, absence.AbsenceStartDate.ToShortDateString(), absence.AbsenceEndDate.ToShortDateString(),
absence.EmployeeId, absence.WorkDay, absence.OnLeaveDay, absence.OffDay, absence.Total,absence.Branch, absence.Department,
DateTime.Now.ToShortDateString(), Store.ActiveUser };
Query q = new Query().Select(fields).From(tableName).Update(values)
.Where("ID").Equal("{" + absence.ID + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Delete(Guid id)
{
Transaction tx = null;
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
tx = em.BeginTransaction();
var q = new Query().From(tableName).Delete()
.Where("ID").Equal("{" + id + "}");
em.ExecuteNonQuery(q.ToSql(), tx);
insuranceProgramRepository.Delete(em, tx, id);
tx.Commit();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
public void Update(WorkCalendar workCalendar)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] columns = { "MonthPeriod", "YearPeriod",
"WorkDay", "OffDay",
"IsClosed", "IsThrClosed" };
object[] values = { workCalendar.MonthPeriod, workCalendar.YearPeriod,
workCalendar.WorkDay, workCalendar.OffDay,
workCalendar.IsClosed == true?1:0, workCalendar.IsThrClosed == true?1:0 };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("ID").Equal("{" + workCalendar.ID + "}");
em.ExecuteNonQuery(q.ToSql());
UpdateIsThr(workCalendar.YearPeriod, workCalendar.IsThrClosed);
}
}
catch (Exception ex)
{
throw ex;
}
}
public string GenerateGradeCode()
{
int counter = 0;
string code = string.Empty;
string lastInsuranceCode = string.Empty;
using (var em = EntityManagerFactory.CreateInstance(ds))
{
var q = new Query().Select("TOP 1 *").From(tableName).OrderBy("InsuranceCode DESC");
var insurance = em.ExecuteObject <Insurance>(q.ToSql(), new InsuranceMapper());
if (insurance != null)
{
lastInsuranceCode = insurance.InsuranceCode;
counter = counter + 1;
code = (Convert.ToInt32(lastInsuranceCode) + counter).ToString("D3");
}
else
{
code = "001";
}
}
return(code);
}
public void Update(EmployeeDebtItem employeeDebtItem)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] columns = { "EmployeeDebtId", "InstallmentCounter",
"AmountPerMonth",
"IsPaid", "IsIncludePayroll",
"PaymentDate" };
object[] values = { employeeDebtItem.EmployeeDebtId, employeeDebtItem.InstallmentCounter,
employeeDebtItem.AmountPerMonth,
employeeDebtItem.IsPaid == true?1:0, employeeDebtItem.IsIncludePayroll == true?1:0,
employeeDebtItem.PaymentDate.ToShortDateString() };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("ID").Equal("{" + employeeDebtItem.ID + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <Sales> GetByStatusFalse()
{
List <Sales> sales = new List <Sales>();
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string sql = "SELECT Sales.ID, Sales.SalesCode, Sales.SalesDate, Sales.CustomerId, Customer.CustomerName, Customer.Address, "
+ "Sales.SalesmanId, Salesman.SalesmanName,Sales.PaymentMethod, Sales.Status, Sales.Notes,Sales.GrandTotal, "
+ "Sales.CreatedDate, Sales.ModifiedDate, Sales.CreatedBy, Sales.ModifiedBy, "
+ "Sales.AmountInWords, Sales.DueDate, Sales.PrintCounter, Sales.TermOfPayment "
+ "FROM (Sales INNER JOIN Customer ON Sales.CustomerId = Customer.ID) "
+ "INNER JOIN Salesman ON Sales.SalesmanId = Salesman.ID "
+ "WHERE Sales.Status=false "
+ "ORDER BY Sales.SalesCode DESC";
sales = em.ExecuteList <Sales>(sql, new SalesMapper());
foreach (var s in sales)
{
s.SalesItems = salesItemRepository.GetBySalesId(s.ID);
}
}
return(sales);
}
public void UpdateStatus(Guid debtItemId, DateTime paymentDate, string status)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
bool isPaid = false;
if (status == "Lunas")
{
isPaid = true;
}
string[] columns = { "IsPaid", "PaymentDate" };
object[] values = { isPaid == true?1:0, paymentDate.ToShortDateString() };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("ID").Equal("{" + debtItemId + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(SalaryUpdate salaryUpdate)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] columns = { "EffectiveDate", "BranchId", "GradeId", "OccupationId",
"UpdateType", "MainSalary", "LunchAllowance", "TransportAllowance",
"FuelAllowance", "VehicleAllowance", "Notes", "CreatedDate", "CreatedBy",
"ModifiedDate", "ModifiedBy" };
object[] values = { salaryUpdate.EffectiveDate.ToShortDateString(), salaryUpdate.BranchId,
salaryUpdate.GradeId, salaryUpdate.OccupationId, salaryUpdate.UpdateType,
salaryUpdate.MainSalary, salaryUpdate.LunchAllowance, salaryUpdate.TransportAllowance,
salaryUpdate.FuelAllowance, salaryUpdate.VehicleAllowance,salaryUpdate.Notes,
DateTime.Now.ToShortDateString(), Store.ActiveUser, DateTime.Now.ToShortDateString(),
Store.ActiveUser };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("ID").Equal(salaryUpdate.ID);
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(ProductQty productQty)
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
Update(em, null, productQty);
}
}
public void Update(Guid employeeId, List <EmployeeGrade> employeeGrades)
{
Transaction tx = null;
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
tx = em.BeginTransaction();
Delete(em, tx, employeeId);
foreach (var grade in employeeGrades)
{
grade.EmployeeId = employeeId;
string[] columns = { "ID", "EmployeeId", "EffectiveDate", "GradeId" };
object[] values = { Guid.NewGuid(), grade.EmployeeId, grade.EffectiveDate.ToShortDateString(),
grade.GradeId };
var q = new Query().Select(columns).From(tableName).Insert(values);
em.ExecuteNonQuery(q.ToSql(), tx);
}
tx.Commit();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
public List <ProductQty> GetAll(int month, int year)
{
List <ProductQty> productQty = new List <ProductQty>();
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string sql = "SELECT Product.ID, Product.ProductCode, Product.ProductName, "
+ "Product.CategoryId, Product.Unit, Product.Notes, Product.IsActive, "
+ "ProductQty.ProductId, ProductQty.ActiveYear, ProductQty.ActiveMonth, "
+ "ProductQty.QtyBegin, ProductQty.ValueBegin, "
+ "ProductQty.QtyIn, ProductQty.PurchasePrice, "
+ "ProductQty.QtyAvailable, ProductQty.ValueAverage, ProductQty.ValueAvailable,"
+ "ProductQty.QtyOut, ProductQty.SalesPrice, ProductQty.SalesValue, "
+ "ProductQty.QtyEnd, ProductQty.ValueEnd, "
+ "ProductQty.QtyPlusCorrection, ProductQty.QtyMinusCorrection, "
+ "ProductQty.ValuePlusCorrection, ProductQty.ValueMinusCorrection, "
+ "ProductQty.QtyPayment, ProductQty.PaymentPrice, ProductQty.PaymentValue "
+ "FROM Product INNER JOIN ProductQty ON Product.ID = ProductQty.ProductId "
+ "WHERE "
+ "Product.IsActive=true "
+ "AND ProductQty.ActiveMonth=" + month + " AND ProductQty.ActiveYear=" + year + " "
+ "ORDER BY Product.ProductCode ASC";
productQty = em.ExecuteList <ProductQty>(sql, new ProductQtyMapper());
}
return(productQty);
}
public string GenerateDepartmentCode()
{
int counter = 0;
string code = string.Empty;
string lastDepartmentCode = string.Empty;
using (var em = EntityManagerFactory.CreateInstance(ds))
{
var sql = "SELECT TOP 1 d.*,b.BranchCode, b.BranchName "
+ "FROM (Department d INNER JOIN Branch b ON d.BranchId = b.ID) "
+ "ORDER BY DepartmentCode DESC";
var department = em.ExecuteObject <Department>(sql, new DepartmentMapper());
if (department != null)
{
lastDepartmentCode = department.DepartmentCode;
counter = counter + 1;
code = (Convert.ToInt32(lastDepartmentCode) + counter).ToString("D3");
}
else
{
code = "001";
}
}
return(code);
}
public List <THR> GetTransfered(int year)
{
List <THR> thrs = new List <THR>();
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string bankName = string.Empty;
var company = companyRepository.GetById(Guid.Empty);
if (company != null)
{
bankName = company.BankName;
}
var sql = "SELECT t.*, "
+ "e.* "
+ "FROM THR t INNER JOIN Employee e ON t.EmployeeId = e.ID "
+ "WHERE t.IsTransfer=true AND t.BankName='" + bankName + "' "
+ "AND t.YearPeriod=" + year;
thrs = em.ExecuteList <THR>(sql, new THRMapper());
}
return(thrs);
}
public List <Incentive> GetTransfered(int month, int year)
{
List <Incentive> incentives = new List <Incentive>();
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string bankName = string.Empty;
var company = companyRepository.GetById(Guid.Empty);
if (company != null)
{
bankName = company.BankName;
}
var sql = "SELECT i.*, "
+ "e.EmployeeCode, e.EmployeeName "
+ "FROM Incentive i INNER JOIN Employee e ON i.EmployeeId = e.ID "
+ "WHERE i.IsTransfer=true AND i.BankName='" + bankName + "' "
+ "AND i.IsIncludePAyroll=false AND i.IsPaid=false "
+ "AND i.MonthPeriod=" + month + " AND i.YearPeriod=" + year;
incentives = em.ExecuteList <Incentive>(sql, new IncentiveMapper());
}
return(incentives);
}
public void UpdateValue(THR thr)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] fields = { "OtherAmount", "TotalAmount",
"AmountInWords",
"ModifiedDate", "ModifiedBy" };
object[] values = { thr.OtherAmount, thr.TotalAmount,
thr.AmountInWords,
DateTime.Now.ToShortDateString(), Store.ActiveUser };
Query q = new Query().Select(fields).From(tableName).Update(values)
.Where("ID").Equal("{" + thr.ID + "}");
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateCurrentInfo(int month, int year, EmployeeCurrentInfo currentInfo)
{
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
string[] columns = { "Branch", "Department",
"BankName", "AccountNumber" };
object[] values = { currentInfo.BranchName, currentInfo.DepartmentName,
currentInfo.BankName, currentInfo.AccountNumber };
var q = new Query().Select(columns).From(tableName).Update(values)
.Where("EmployeeId").Equal("{" + currentInfo.EmployeeId + "}")
.And("MonthPeriod").GreaterEqualThan(month)
.And("YearPeriod").GreaterEqualThan(year)
.And("IsPaid = false");;
em.ExecuteNonQuery(q.ToSql());
}
}
catch (Exception ex)
{
throw ex;
}
}
public void Save(Branch branch)
{
Transaction tx = null;
try
{
using (var em = EntityManagerFactory.CreateInstance(ds))
{
tx = em.BeginTransaction();
string[] fields = { "ID", "BranchCode", "BranchName", "Region", "BranchHead", "ViceHead", "Address", "Phone", "Fax", "Email",
"UMR", "FuelAllowance", "LunchAllowance", "TransportAllowance", "IsActive" };
object[] values = { Guid.NewGuid(), branch.BranchCode, branch.BranchName, branch.Region, branch.BranchHead, branch.ViceHead,
branch.Address, branch.Phone, branch.Fax, branch.Email, branch.UMR, branch.FuelAllowance,branch.LunchAllowance,
branch.TransportAllowance, branch.IsActive == true?1:0 };
Query q = new Query().Select(fields).From(tableName).Insert(values);
em.ExecuteNonQuery(q.ToSql(), tx);
tx.Commit();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
