public List <Service> get_dcr_reports()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Service>("select Services.Id, Services.Name, Tokens.Quantity, Tokens.TotalCost, Tokens.ServiceDailyNumber from Services, Tokens").ToList());
}
}
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 <Prefix> GetAllPrefixes()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Prefix>("Select Id, Name from Prefixes").ToList());
}
}
public List <PrintSeries> GetAllPrintSeriesBalance()
{
using (var db = new eTempleDbDB())
{
return(db.Query <PrintSeries>("Select p.GivenTo, p.EmployeeName, p.TotalGivenTokens, t.Qty, (p.TotalGivenTokens - t.Qty) as Balance from PSCView as p INNER JOIN TknView as t on(p.GivenTo = t.CreatedBy)").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 List <Designation> GetAllDesignations()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Designation>("Select * From Designations order by Name asc").ToList());
}
}
public List <VIPPrasadam> GetAllVIPPrasadamDetails()
{
using (var db = new eTempleDbDB())
{
return(db.Query <VIPPrasadam>("SELECT v.*, s.Name as ServiceName, e.Name as ReferenceByName FROM VIPPrasadam as v inner join Services as s on (v.ServiceId = s.Id) inner join Employees as e on (v.ReferenceBy = e.Id) order by v.Id desc ").ToList());
}
}
public List <Role> GetAllRoles()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Role>("Select * From Roles order by Name asc").ToList());
}
}
public List<PrintSeries> GetAllPrintSeriesBalance()
{
using (var db = new eTempleDbDB())
{
return db.Query<PrintSeries>("Select p.GivenTo, p.EmployeeName, p.TotalGivenTokens, t.Qty, (p.TotalGivenTokens - t.Qty) as Balance from PSCView as p INNER JOIN TknView as t on(p.GivenTo = t.CreatedBy)").ToList();
}
}
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 List <StatusCode> GetAllStatusCode()
{
using (var db = new eTempleDbDB())
{
return(db.Query <StatusCode>("SELECT * FROM StatusCode").ToList());
}
}
public List<StatusCode> GetAllStatusCode()
{
using (var db = new eTempleDbDB())
{
return db.Query<StatusCode>("SELECT * FROM StatusCode").ToList();
}
}
public List<TransactionType> GetAllTransactionTypes()
{
using (var db = new eTempleDbDB())
{
return db.Query<TransactionType>("SELECT * FROM TransactionType").ToList();
}
}
public List <ServiceType> GetAllServiceTypes()
{
using (var db = new eTempleDbDB())
{
return(db.Query <ServiceType>("Select * From ServiceTypes order by Name asc").ToList());
}
}
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 List <Service> GetAllServicesForSelection()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Service>("select Id, Name, Cost from Services").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 List<ServiceType> GetAllServiceTypes()
{
using (var db = new eTempleDbDB())
{
return db.Query<ServiceType>("Select * From ServiceTypes order by Name asc").ToList();
}
}
public List<VIPPrasadam> GetAllVIPPrasadamDetails()
{
using (var db = new eTempleDbDB())
{
return db.Query<VIPPrasadam>("SELECT v.*, s.Name as ServiceName, e.Name as ReferenceByName FROM VIPPrasadam as v inner join Services as s on (v.ServiceId = s.Id) inner join Employees as e on (v.ReferenceBy = e.Id) order by v.Id desc ").ToList();
}
}
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 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 List<Prefix> GetAllPrefixes()
{
using (var db = new eTempleDbDB())
{
return db.Query<Prefix>("Select Id, Name from Prefixes").ToList();
}
}
public List<Role> GetAllRoles()
{
using (var db = new eTempleDbDB())
{
return db.Query<Role>("Select * From Roles order by Name asc").ToList();
}
}
public List<Service> GetAllServicesForSelection()
{
using (var db = new eTempleDbDB())
{
return db.Query<Service>("select Id, Name, Cost from Services").ToList();
}
}
public List <TransactionType> GetAllTransactionTypes()
{
using (var db = new eTempleDbDB())
{
return(db.Query <TransactionType>("SELECT * FROM TransactionType").ToList());
}
}
public List<Unit> GetAllUnits()
{
using (var db = new eTempleDbDB())
{
var units = db.Query<Unit>("Select * From Units order by Name asc").ToList();
return units;
}
}
public List<Service> GetAllPoojasExceptVIPPrasadam()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query<Service>("Select * From Services where Type not in(16) ").ToList();
return poojas;
}
}
public List<Service> GetAllPoojas()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query<Service>("Select * From Services").ToList();
return poojas;
}
}
public List<Service> GetAllMiscIncomes()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query<Service>("Select * From Services where Type in(14) ").ToList();
return poojas;
}
}
public List<Devotee> getPhoneNumbers(String phno)
{
using (var db = new eTempleDbDB())
{
//var cities = db.Devotees.Select(c => c.cst_City).Distinct().ToArray();
return db.Query<Devotee>("SELECT distinct PhoneNumber FROM Devotees where PhoneNumber like '@0%' limit 0, 10 ", phno).ToList();
}
}
public List <Unit> GetAllUnits()
{
using (var db = new eTempleDbDB())
{
var units = db.Query <Unit>("Select * From Units order by Name asc").ToList();
return(units);
}
}
public List<VIPPrasadam> GetVIPPrasadamDetailsByDate(DateTime dt)
{
String dt1 = String.Format("{0:yyyy-MM-dd}", dt);
using (var db = new eTempleDbDB())
{
return db.Query<VIPPrasadam>("SELECT v.*, s.Name as ServiceName FROM VIPPrasadam as v inner join Services as s on (v.ServiceId = s.Id and DATE(v.VisitedDate) like '@0' ')", dt1).ToList();
}
}
public List<VIPPrasadam> GetDaycount(DateTime dt)
{
String dt1 = String.Format("{0:yyyy-MM-dd}", dt);
using (var db = new eTempleDbDB())
{
return db.Query<VIPPrasadam>("SELECT s.Name as ServiceName, v.ServiceId, sum(v.Qty) as Qty FROM VIPPrasadam as v inner join Services as s on ( s.Id = v.ServiceId and date(v.VisitedDate) = '" + dt1 + "') group by v.ServiceId ").ToList();
}
}
public List <Devotee> getPhoneNumbers(String phno)
{
using (var db = new eTempleDbDB())
{
//var cities = db.Devotees.Select(c => c.cst_City).Distinct().ToArray();
return(db.Query <Devotee>("SELECT distinct PhoneNumber FROM Devotees where PhoneNumber like '@0%' limit 0, 10 ", phno).ToList());
}
}
public List<Service> GetAllPoojasExceptMiscIncomeHundials()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query<Service>("Select * From Services where Type not in(13, 14, 16) ").ToList();
return poojas;
}
}
public List <Service> GetAllHundials1()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query <Service>("Select * From Services where Type in(13) ").ToList();
return(poojas);
}
}
public List <Service> GetAllPoojasExceptVIPPrasadam()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query <Service>("Select * From Services where Type not in(16) ").ToList();
return(poojas);
}
}
public List <Service> GetAllPoojasExceptMiscIncomeHundials()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query <Service>("Select * From Services where Type not in(13, 14, 16) ").ToList();
return(poojas);
}
}
public List <Service> GetAllPoojas()
{
using (var db = new eTempleDbDB())
{
var poojas = db.Query <Service>("Select * From Services").ToList();
return(poojas);
}
}
public List <Timing> GetAllTimings()
{
String timeVal = String.Format("{0: HH:mm:ss}", DateTime.Now);
using (var db = new eTempleDbDB())
{
return(db.Query <Timing>("select Id, TimeName from Timing where Status=1 and TimeVal >= '" + timeVal + "' order by Id asc").ToList());
}
}
public List<Token> GetMiscIncomeDetails()
{
using (var db = new eTempleDbDB())
{
return db.Query<Token>(
@"select t.*, e.LoginId as Created_By from Tokens t
inner join Employees e on (t.CreatedBy = e.Id) and ServiceId in (Select Id from Services where Type = 14) order by t.Id desc").ToList();
}
}
public List <Timing> GetTodayTimingsWithTokens()
{
String dt = String.Format("{0:yyyy-MM-dd}", DateTime.Now);
using (var db = new eTempleDbDB())
{
return(db.Query <Timing>(@"Select t.Id as Id, t.TimeName as TimeName, sum(t1.Quantity) as Quantity from Timing as t left outer join Tokens as t1 on (t.Id = t1.TimeID and DATE(t1.CreatedOn)='" + dt + "' and t1.Status=1 and t1.ServiceId in (Select s.Id from Services as s where s.Type=9)) group by t.TimeName").ToList());
}
}
public List <Batch> GetAllBatches()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Batch>(
@"select b.*, s.Name as ServiceName from Batches b
inner join Services s on (b.ServiceId = s.Id) order by b.Id desc").ToList());
}
}
public List <VIPPrasadam> GetVIPPrasadamDetailsByDate(DateTime dt)
{
String dt1 = String.Format("{0:yyyy-MM-dd}", dt);
using (var db = new eTempleDbDB())
{
return(db.Query <VIPPrasadam>("SELECT v.*, s.Name as ServiceName FROM VIPPrasadam as v inner join Services as s on (v.ServiceId = s.Id and DATE(v.VisitedDate) like '@0' ')", dt1).ToList());
}
}
public List <VIPPrasadam> GetDaycount(DateTime dt)
{
String dt1 = String.Format("{0:yyyy-MM-dd}", dt);
using (var db = new eTempleDbDB())
{
return(db.Query <VIPPrasadam>("SELECT s.Name as ServiceName, v.ServiceId, sum(v.Qty) as Qty FROM VIPPrasadam as v inner join Services as s on ( s.Id = v.ServiceId and date(v.VisitedDate) = '" + dt1 + "') group by v.ServiceId ").ToList());
}
}
public List <Token> GetMiscIncomeDetails()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Token>(
@"select t.*, e.LoginId as Created_By from Tokens t
inner join Employees e on (t.CreatedBy = e.Id) and ServiceId in (Select Id from Services where Type = 14) order by t.Id desc").ToList());
}
}
public List<Batch> GetAllBatches()
{
using (var db = new eTempleDbDB())
{
return db.Query<Batch>(
@"select b.*, s.Name as ServiceName from Batches b
inner join Services s on (b.ServiceId = s.Id) order by b.Id desc").ToList();
}
}
public List <PrintSeries> GetAllPrintSeries()
{
using (var db = new eTempleDbDB())
{
return(db.Query <PrintSeries>(
@"select p.*, e.Loginid as Given_To, e1.LoginId as Given_By from PrintSeries p
inner join Employees e on (p.GivenTo = e.Id)
inner join Employees e1 on (p.CreatedBy = e1.Id) order by p.Id desc").ToList());
}
}
public List<PrintSeries> GetAllPrintSeries()
{
using (var db = new eTempleDbDB())
{
return db.Query<PrintSeries>(
@"select p.*, e.Loginid as Given_To, e1.LoginId as Given_By from PrintSeries p
inner join Employees e on (p.GivenTo = e.Id)
inner join Employees e1 on (p.CreatedBy = e1.Id) order by p.Id desc").ToList();
}
}
public List<PattiOut> GetAllPattiOut(String dt)
{
using (var db = new eTempleDbDB())
{
return db.Query<PattiOut>(
@"select p.*, e.Loginid as Entered_By, s.Name as ServiceName from PattiOut p
inner join Employees e on (p.EnteredBy = e.Id)
inner join Services s on (p.ServiceId = s.Id) Where DATE(p.EnteredOn) like '" + dt + "' order by p.Id desc").ToList();
}
}
public List<Employee> GetAllEmployees()
{
using (var db = new eTempleDbDB())
{
return db.Query<Employee>(
@"select e.*, r.Name as RoleName, d.Name as DesignationName from Employees e
inner join Roles r on (e.Role = r.Id)
inner join Designations d on (e.Designation = d.Id) order by e.Name Asc").ToList();
}
}
public List<PrintSeries> GetAllPrintSeries(String dt, String dt1, int Uid)
{
using (var db = new eTempleDbDB())
{
return db.Query<PrintSeries>(
@"select p.*, e.Loginid as Given_To, e1.LoginId as Given_By from PrintSeries p
inner join Employees e on (p.GivenTo = e.Id)
inner join Employees e1 on (p.CreatedBy = e1.Id) where (DATE(p.CreatedOn) between '" + dt + "' and '" + dt1 + "') and p.GivenTo='" + Uid + "' order by p.Id desc").ToList();
}
}
public List <PattiOut> GetAllPattiOutWithoutDate()
{
using (var db = new eTempleDbDB())
{
return(db.Query <PattiOut>(
@"select p.*, e.Loginid as Entered_By, s.Name as ServiceName from PattiOut p
inner join Employees e on (p.EnteredBy = e.Id)
inner join Services s on (p.ServiceId = s.Id) order by p.Id desc").ToList());
}
}
public List <Employee> GetAllEmployees()
{
using (var db = new eTempleDbDB())
{
return(db.Query <Employee>(
@"select e.*, r.Name as RoleName, d.Name as DesignationName from Employees e
inner join Roles r on (e.Role = r.Id)
inner join Designations d on (e.Designation = d.Id) order by e.Name Asc").ToList());
}
}
public List <PattiOut> GetAllPattiOut(String dt)
{
using (var db = new eTempleDbDB())
{
return(db.Query <PattiOut>(
@"select p.*, e.Loginid as Entered_By, s.Name as ServiceName from PattiOut p
inner join Employees e on (p.EnteredBy = e.Id)
inner join Services s on (p.ServiceId = s.Id) Where DATE(p.EnteredOn) like '" + dt + "' order by p.Id desc").ToList());
}
}
public List<PattiIn> GetAllPattiIn()
{
using (var db = new eTempleDbDB())
{
return db.Query<PattiIn>(
@"select p.*, e.Loginid as Entered_By, s.Name as ServiceName from PattiIn p
inner join Employees e on (p.EnteredBy = e.Id)
inner join Services s on (p.ServiceId = s.Id) order by p.Id desc").ToList();
}
}
public List<Service> GetAllServices()
{
using (var db = new eTempleDbDB())
{
return db.Query<Service>(
@"select s.*, st.Name as ServiceType, e1.LoginId as Created_By, e2.LoginId as Modified_By from Services s
inner join ServiceTypes st on (s.Type = st.Id)
inner join Employees e1 on (e1.Id = s.CreeatedBy)
inner join Employees e2 on (e2.Id = s.ModifiedBy)
order by s.Id desc").ToList();
}
}
public List<Token> GetSelectedServiceDevotees(long startId, long endId, int serviceId)
{
using (var db = new eTempleDbDB())
{
return db.Query<Token>(
@"select t.*, d.DevoteeName as DevoteeName, d.Gothram as Gothram, p.Name as PrefixName from Tokens t
inner join Devotees d on (t.DevoteeId = d.Id)
inner join Prefixes p on (d.Prefix = p.Id)
and ServiceId = @0 and (ServiceDailyNumber between @1 and @2) order by t.Id desc", serviceId, startId, endId).ToList();
}
}
public List<Token> GetServiceReport(int ServiceId, String fldNameValue, String dtval)
{
using (var db = new eTempleDbDB())
{
return db.Query<Token>(@"select t.*, s.Name as ServiceName, s.Type as ServiceTypeID, s.Type as ServiceType, d.Prefix as PrefixId, d.EmailId as EmailId, d.DevoteeName, d.Gothram, d.DoorNumber, d.AreaName, d.City, d.MandalName, d.DistrictName, d.StateName, d.OtherDetails, d.PhoneNumber, d.AdditionalNames as AdditionalNames, d.AdditionalGothrams as AdditionalGothrams, p.Name as PrefixName, e.Loginid as Created_By, e.Name as Entered_By
from Tokens t
join Services s on (t.ServiceId = s.Id)
left outer join Devotees d on (t.DevoteeId = d.Id)
left outer join Prefixes p on (d.Prefix = p.Id)
left outer join Employees e on (e.Id = t.CreatedBy)
where t.Status=1 and t.ServiceId = " + ServiceId + " and DATE(t. " + fldNameValue + ") like '" + dtval + "' order by t.Id ASC").ToList();
}
}