public VIPPrasadam getTokenById(int TokenId)
{
using (var db = new eTempleDbDB())
{
return(db.FirstOrDefault <VIPPrasadam>("select v.*, s.Name as ServiceName from VIPPrasadam as v inner join Services as s on v.ServiceId = s.Id where v.Id = @0", TokenId));
}
}
public void SavePattiIn(PattiIn piObj)
{
using (var db = new eTempleDbDB())
{
db.Save(piObj);
}
}
public int UpdatePattiOut(PattiOut poObj)
{
using (var db = new eTempleDbDB())
{
return(db.Update(poObj));
}
}
public List <Designation> GetAllDesignations()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Designation>("Select * From Designations order by Name asc").ToList());
}
}
public Token GetToken(int Id)
{
using (var db = new eTempleDbDB())
{
return(db.SingleOrDefault <Token>(Id));
}
}
public List <StockDetail> GetAllStockDetails()
{
using (var db = new eTempleDbDB())
{
return(db.Query <StockDetail>("SELECT sd.ServiceId, sd.Stock, s.Name as ServiceName FROM StockDetails as sd inner join Services as s on sd.ServiceId = s.Id and ServiceId in (Select Id from Services where Type=2)").ToList());
}
}
public List<Designation> GetAllDesignations()
{
using (var db = new eTempleDbDB())
{
return db.Query<Designation>("Select * From Designations order by Name asc").ToList();
}
}
public Employee GetEmployee(int Id)
{
using (var db = new eTempleDbDB())
{
return db.SingleOrDefault<Employee>(Id);
}
}
public List<Employee> GetLoginIds()
{
using (var db = new eTempleDbDB())
{
return db.Query<Employee>("Select Id, LoginId From Employees where AllowLogin=1 and IsDeleted=0 order by LoginId asc").ToList();
}
}
public void SavePattiOut(PattiOut poObj)
{
using (var db = new eTempleDbDB())
{
db.Save(poObj);
}
}
public int UpdatePattiOut(PattiOut poObj)
{
using (var db = new eTempleDbDB())
{
return db.Update(poObj);
}
}
public List<Prefix> GetAllPrefixes()
{
using (var db = new eTempleDbDB())
{
return db.Query<Prefix>("Select Id, Name from Prefixes").ToList();
}
}
public List<Devotee> FindDevoteesByPhone(string phoneNumber)
{
using (var db = new eTempleDbDB())
{
return db.Query<Devotee>("SELECT * FROM Devotees where PhoneNumber = @0", phoneNumber).ToList();
}
}
public int UpdateVIPPrasadam(VIPPrasadam vObj)
{
using (var db = new eTempleDbDB())
{
return(db.Update(vObj));
}
}
public int UpdatePattiIn(PattiIn piObj)
{
using (var db = new eTempleDbDB())
{
return(db.Update(piObj));
}
}
public void SaveEmployee(Employee emp)
{
using (var db = new eTempleDbDB())
{
db.Save(emp);
}
}
public List <TransactionType> GetAllTransactionTypes()
{
using (var db = new eTempleDbDB())
{
return(db.Query <TransactionType>("SELECT * FROM TransactionType").ToList());
}
}
public int UpdateEmployee(Employee emp)
{
using (var db = new eTempleDbDB())
{
return db.Update(emp);
}
}
public void SaveStockDetail(StockDetail stObj)
{
using (var db = new eTempleDbDB())
{
db.Save(stObj);
}
}
public List<ServiceType> GetAllServiceTypes()
{
using (var db = new eTempleDbDB())
{
return db.Query<ServiceType>("Select * From ServiceTypes order by Name asc").ToList();
}
}
public void SaveDesignation(Designation designationObj)
{
using (var db = new eTempleDbDB())
{
db.Save(designationObj);
}
}
public ServiceType GetServiceType(int Id)
{
using (var db = new eTempleDbDB())
{
return db.SingleOrDefault<ServiceType>(Id);
}
}
public void SaveDesignation(Designation designationObj)
{
using (var db = new eTempleDbDB())
{
db.Save(designationObj);
}
}
public void SaveServiceType(ServiceType servicetypeObj)
{
using (var db = new eTempleDbDB())
{
db.Save(servicetypeObj);
}
}
public double GEtTotalCostByTransactionType(DateTime fromDate, DateTime toDate, String tType)
{
var startDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day);
var endDate = new DateTime(toDate.Year, toDate.Month, toDate.Day);
if (startDate == endDate)
{
endDate.AddDays(1);
}
String strDt = String.Format("{0:yyyy-MM-dd}", startDate);
String toDt = String.Format("{0:yyyy-MM-dd}", endDate);
using (var db = new eTempleDbDB())
{
String SqlQuery = "Select sum(TotalCost) as TotalCost from Tokens where ( CreatedOn between '" + strDt + "' and '" + toDt + "' ) and TransactionType = '" + tType + "' ";
var tokenObj = db.SingleOrDefault <Token>(SqlQuery);
if (tokenObj != null && tokenObj.TotalCost != null)
{
return((double)tokenObj.TotalCost);
}
else
{
return(0);
}
}
}
public int UpdateServiceType(ServiceType servicetypeObj)
{
using (var db = new eTempleDbDB())
{
return db.Update(servicetypeObj);
}
}
public PattiOut GetPattiOut(int Id)
{
using (var db = new eTempleDbDB())
{
return(db.SingleOrDefault <PattiOut>(Id));
}
}
public List<Service> GetAllServicesForSelection()
{
using (var db = new eTempleDbDB())
{
return db.Query<Service>("select Id, Name, Cost from Services").ToList();
}
}
public void SaveBatch(Batch batchObj)
{
using (var db = new eTempleDbDB())
{
db.Save(batchObj);
}
}
public List<StockDetail> GetAllStockDetails()
{
using (var db = new eTempleDbDB())
{
return db.Query<StockDetail>("SELECT sd.ServiceId, sd.Stock, s.Name as ServiceName FROM StockDetails as sd inner join Services as s on sd.ServiceId = s.Id and ServiceId in (Select Id from Services where Type=2)").ToList();
}
}
public void SavePattiIn(PattiIn piObj)
{
using (var db = new eTempleDbDB())
{
db.Save(piObj);
}
}
public StockDetail GetStockDetail(int Id)
{
using (var db = new eTempleDbDB())
{
return db.SingleOrDefault<StockDetail>(Id);
}
}
public List<TransactionType> GetAllTransactionTypes()
{
using (var db = new eTempleDbDB())
{
return db.Query<TransactionType>("SELECT * FROM TransactionType").ToList();
}
}
public void SaveStockDetail(StockDetail stObj)
{
using (var db = new eTempleDbDB())
{
db.Save(stObj);
}
}
public List<StatusCode> GetAllStatusCode()
{
using (var db = new eTempleDbDB())
{
return db.Query<StatusCode>("SELECT * FROM StatusCode").ToList();
}
}
public void UpdateStockDetail(int stockVal, int sId)
{
using (var db = new eTempleDbDB())
{
db.Execute("Update StockDetails set Stock = Stock - @0 where ServiceId = @1 ", stockVal, sId);
}
}
public StockDetail GetStockDetail(int Id)
{
using (var db = new eTempleDbDB())
{
return(db.SingleOrDefault <StockDetail>(Id));
}
}
public List <Employee> GetLoginIds()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Employee>("Select Id, LoginId From Employees where AllowLogin=1 and IsDeleted=0 order by LoginId asc").ToList());
}
}
public void UpdateStockDetail(int stockVal, int sId)
{
using (var db = new eTempleDbDB())
{
db.Execute("Update StockDetails set Stock = Stock - @0 where ServiceId = @1 ", stockVal, sId);
}
}
public Employee GetEmployee(int Id)
{
using (var db = new eTempleDbDB())
{
return(db.SingleOrDefault <Employee>(Id));
}
}
public Designation GetDesignation(int Id)
{
using (var db = new eTempleDbDB())
{
return db.SingleOrDefault<Designation>(Id);
}
}
public void SaveEmployee(Employee emp)
{
using (var db = new eTempleDbDB())
{
db.Save(emp);
}
}
public int UpdateDesignation(Designation designationObj)
{
using (var db = new eTempleDbDB())
{
return db.Update(designationObj);
}
}
public int UpdateEmployee(Employee emp)
{
using (var db = new eTempleDbDB())
{
return(db.Update(emp));
}
}
public Designation GetDesignation(int Id)
{
using (var db = new eTempleDbDB())
{
return(db.SingleOrDefault <Designation>(Id));
}
}
public List <ServiceType> GetAllServiceTypes()
{
using (var db = new eTempleDbDB())
{
return(db.Query <ServiceType>("Select * From ServiceTypes order by Name asc").ToList());
}
}
public int UpdateDesignation(Designation designationObj)
{
using (var db = new eTempleDbDB())
{
return(db.Update(designationObj));
}
}
public void SaveServiceType(ServiceType servicetypeObj)
{
using (var db = new eTempleDbDB())
{
db.Save(servicetypeObj);
}
}
public long UpdateTokenPart(DateTime ServicePerformDate, String TokenStatus, long Id)
{
using (var db = new eTempleDbDB())
{
return(db.Update <Token>("Update Tokens set ServicePerformDate=@0, TokenStatus=@1 where Id=@2", ServicePerformDate, TokenStatus, Id));
}
}
public int UpdateServiceType(ServiceType servicetypeObj)
{
using (var db = new eTempleDbDB())
{
return(db.Update(servicetypeObj));
}
}
public PattiIn GetPattiIn(int Id)
{
using (var db = new eTempleDbDB())
{
return db.SingleOrDefault<PattiIn>(Id);
}
}
public Service GetPrintSeries(int Id)
{
using (var db = new eTempleDbDB())
{
return(db.SingleOrDefault <Service>(Id));
}
}
public int UpdatePattiIn(PattiIn piObj)
{
using (var db = new eTempleDbDB())
{
return db.Update(piObj);
}
}
public int UpdatePrintSeries(Service ps)
{
using (var db = new eTempleDbDB())
{
return(db.Update(ps));
}
}
public void SavePattiOut(PattiOut poObj)
{
using (var db = new eTempleDbDB())
{
db.Save(poObj);
}
}
public List <Service> GetDcrReport(DateTime fromDate, DateTime toDate)
{
var startDate = new DateTime(fromDate.Year, fromDate.Month, fromDate.Day);
var endDate = new DateTime(toDate.Year, toDate.Month, toDate.Day);
if (startDate == endDate)
{
endDate.AddDays(1);
}
using (var db = new eTempleDbDB())
{
//sum(t.Quantity) as SoldQuantity
return(db.Query <Service>(@"select s.Id, s.Name, s.Cost, sum(t.Quantity) as SoldQuantity, s.Type, sum(t.TotalCost) as TotalCollection, max(t.ServiceDailyNumber) as EndingNumber, min(t.ServiceDailyNumber) as StartingNumber
from Services s
left outer join Tokens t on ( s.Id = t.ServiceId and t.CreatedOn between @0 and @1 and t.Status=1 and s.Type not in(16) )
where s.Type != 16
group by s.Id, s.Name", startDate, endDate).ToList());
/*
* return db.Query<Service>(@"select s.Id, s.Name, (max(t.ServiceDailyNumber) - min(t.ServiceDailyNumber) + 1) as SoldQuantity, sum(t.TotalCost) as TotalCollection, max(t.ServiceDailyNumber) as EndingNumber, min(t.ServiceDailyNumber) as StartingNumber
* from Services s
* left outer join Tokens t on ( s.Id = t.ServiceId and t.CreatedOn between @0 and @1)
* group by s.Id, s.Name", startDate, endDate).ToList();
*/
}
}
public Batch GetBatch(int Id)
{
using (var db = new eTempleDbDB())
{
return db.SingleOrDefault<Batch>(Id);
}
}
public List <Service> GetAllServicesForSelection()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Service>("select Id, Name, Cost from Services").ToList());
}
}
public void UpdateBatch(Batch batchObj)
{
using (var db = new eTempleDbDB())
{
db.Update(batchObj);
}
}
public int UpdateRole(Role roleObj)
{
using (var db = new eTempleDbDB())
{
return db.Update(roleObj);
}
}
