private PetaPoco.Page <HistoryEntry> PageInternal(long page, long itemsPerPage, QueryParameters parameters, BonusCalculationType bonusCalculation)
{
Debug.Assert(TableExists(SqlTable.WuHistory));
var select = new PetaPoco.Sql(SqlTableCommandDictionary[SqlTable.WuHistory].SelectSql);
select.Append(WhereBuilder.Execute(parameters));
GetProduction.BonusCalculation = bonusCalculation;
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (var database = new PetaPoco.Database(connection))
{
PetaPoco.Page <HistoryEntry> query = database.Page <HistoryEntry>(page, itemsPerPage, select);
Debug.Assert(query != null);
return(query);
}
}
}
public int ExecuteUpdate(string tableName, string setString, Dictionary <string, object> updateSets, string strWhere, params object[] p)
{
var sql = new PetaPoco.Sql("UPDATE " + tableName + " SET ");
var values = new List <object>();
var list = new List <string>();
if (!string.IsNullOrEmpty(setString))
{
list.Add(setString);
}
var n = 0;
foreach (var kv in updateSets)
{
list.Add(string.Format("{0}=@{1} ", kv.Key, n));
n++;
values.Add(kv.Value);
}
sql.Append(string.Join(",", list.ToArray()), values.ToArray());
sql.Where(strWhere, p);
return(this.Execute(sql));
}
public JsonResult GetCustomerQueries(DTParameters parameters)
{
var columnSearch = parameters.Columns.Select(s => s.Search.Value).Take(CustomerColumns.Count()).ToList();
//XMLPath uses nested queries so to avoid that we construct these 4 filters ourselves
var sql = new PetaPoco.Sql($"Select distinct c.CustomerQueryID,ServiceId, ServiceName, Email, Phone, Substring(Query,1,100) + '...' as Query, FName, SName, CheckIn, Tdate, ServiceTypeId, NoPax, Qty from CustomerQuery c");
var fromsql = new PetaPoco.Sql();
var wheresql = new PetaPoco.Sql("where c.CustomerQueryID >0");
wheresql.Append($"{GetWhereWithOrClauseFromColumns(CustomerColumns, columnSearch)} Order By CustomerQueryID Desc ");
sql.Append(fromsql);
sql.Append(wheresql);
try
{
var res = db.Query <CustomerQuery>(sql).Skip(parameters.Start).Take(parameters.Length).ToList();
var dataTableResult = new DTResult <CustomerQuery>
{
draw = parameters.Draw,
data = res,
recordsFiltered = 10,
recordsTotal = res.Count()
};
return(Json(dataTableResult, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
throw ex;
}
}
public List <Todo> GetFilteredTodos(Todo filter)
{
using (var db = GetDb())
{
PetaPoco.Sql sql = new PetaPoco.Sql("SELECT * FROM Todos");
if (filter.userid != null)
{
sql.Append("WHERE userid=@0", filter.userid);
}
if (filter.completed != null)
{
sql.Append("WHERE completed=@0", filter.completed);
}
if (filter.urgent != null)
{
sql.Append("WHERE urgent=@0", filter.urgent);
}
if (filter.title != null)
{
sql.Append("WHERE title like @0", "%" + filter.title + "%");
}
if (filter.tags != null)
{
sql.Append("WHERE tags like @0", "%" + filter.tags + "%");
}
if (filter.content != null)
{
sql.Append("WHERE content like @0", "%" + filter.content + "%");
}
return(db.Fetch <Todo>(sql));
}
}
public PagedDataModelCollection <CombinedLogDataModel> CombinedPageLog(int pageSize, int pageNumber, string sortColumn, string sortDirection, Dictionary <string, string> searchDictionary)
{
using (AsignioDatabase db = new AsignioDatabase(ConnectionStringName))
{
try
{
PetaPoco.Sql sql = new PetaPoco.Sql();
sql.Append("SELECT ");
sql.Append("user.EmailAddress, log.UserID, log.TimeStamp, log.LogID, log.Level, log.Message, log.Source, log.Important ");
sql.Append("from log ");
sql.Append(" INNER JOIN user on user.userID = log.userID ");
//a boolean to keep track of whether sql string is at first search clause
bool FirstClause = true;
string dateString = "";
foreach (KeyValuePair <string, string> entry in searchDictionary)
{
string userInput = entry.Value;
if (!string.IsNullOrWhiteSpace(userInput))
{
if (entry.Key == "Important") //only get logs that are marked as important
{
if (!FirstClause)
{
sql.Append(string.Format("AND Important != \'\'"));
}
else
{
sql.Append(string.Format("WHERE Important != \'\'"));
}
FirstClause = false;
}
else if (entry.Key == "TimeStamp") //get logs from one specific date
{
if (userInput[0] != '\'')
{
userInput = string.Format("\'{0}\'", userInput);
}
if (!FirstClause)
{
sql.Append(string.Format("AND DATE(TimeStamp) = {0} ", userInput));
}
else
{
sql.Append(string.Format("WHERE DATE(TimeStamp) = {0} ", userInput));
}
FirstClause = false;
}
else if (entry.Key == "beginDate") //format date at beginning of range
{
if (userInput[0] != '\'')
{
userInput = string.Format("\'{0}\'", userInput);
}
dateString = string.Format("DATE(TimeStamp) BETWEEN {0} AND ", userInput);
}
else if (entry.Key == "endDate" && dateString != "") //format date at end of range
{
if (userInput[0] != '\'')
{
userInput = string.Format("\'{0}\'", userInput);
}
if (!FirstClause)
{
sql.Append(string.Format("AND {0} {1} ", dateString, userInput));
}
else
{
sql.Append(string.Format("WHERE {0} {1} ", dateString, userInput));
}
FirstClause = false;
}
else
{
if (userInput.Contains("@")) //format email
{
string[] sections = userInput.Split(new[] { '@' });
sections[1] = sections[1].Insert(0, "@@");
userInput = string.Join("", sections);
}
string newKey = entry.Key; //formatting for "get logs marked as important by a specific username"
if (entry.Key == "UserImportant")
{
newKey = "Important";
}
if (userInput[0] != '\'')
{
userInput = string.Format("\'%{0}%\'", userInput);
}
if (!FirstClause)
{
sql.Append(string.Format("AND {0} LIKE {1} ", newKey, userInput));
}
else
{
sql.Append(string.Format("WHERE {0} LIKE {1} ", newKey, userInput));
}
FirstClause = false;
}
}
}
sql.Append(string.Format("ORDER BY {0} {1}", sortColumn, sortDirection));
PetaPoco.Page <CombinedLogPoco> page = db.Page <CombinedLogPoco>(pageNumber, pageSize, sql);
if (page == null)
{
return(null);
}
return(new PagedDataModelCollection <CombinedLogDataModel>()
{
Items = page.Items.Select(s => s.ToModel()),
PageNumber = pageNumber,
PageSize = pageSize,
TotalItems = page.TotalItems,
TotalPages = page.TotalPages,
SortBy = sortColumn,
SortDirection = sortDirection,
SearchDictionary = searchDictionary,
});
}
catch (Exception ex)
{
string errorMessage = ex.Message;
}
finally { }
}
return(null);
}
public JsonResult GetPkgList(DTParameters parameters, int sid)
{
var columnSearch = parameters.Columns.Select(s => s.Search.Value).Take(PackageColumns.Count()).ToList();
//XMLPath uses nested queries so to avoid that we construct these 4 filters ourselves
string supname = "";
string geos = "";
string cats = "";
string conts = "";
int daysl = 0;
if (columnSearch[2]?.Length > 0)
{
geos = columnSearch[2]; columnSearch[2] = null;
}
if (columnSearch[4]?.Length > 0)
{
cats = columnSearch[4]; columnSearch[4] = null;
}
if (columnSearch[6]?.Length > 0)
{
conts = columnSearch[6]; columnSearch[6] = null;
}
if (columnSearch[5]?.Length > 0)
{
supname = columnSearch[5]; columnSearch[5] = null;
}
if (columnSearch[8]?.Length > 0)
{
int.TryParse(columnSearch[8], out daysl); columnSearch[8] = null;
}
string sortOrder = parameters.SortOrder.Replace("EndDateStr", "ValidTo");
var sql = new PetaPoco.Sql($"Select distinct p.*,ValidTo, (select max(v.ValidTo) from PackageValidity v where v.ServiceID=p.PackageID) as EndDate " +
$"from Package p Left Join PackageValidity v on v.ServiceID=p.PackageID and v.PVId=(select max(PVId) from PackageValidity where PackageId=p.PackageID)");
var fromsql = new PetaPoco.Sql();
var wheresql = new PetaPoco.Sql($" where p.ServiceTypeId={sid} ");
if (geos.Length > 0)
{
fromsql.Append(", Geotree g, package_geotree pg");
wheresql.Append($" and p.packageId=pg.packageId and pg.geotreeid=g.geotreeid and geoname like '%{geos}%'");
}
if (cats.Length > 0)
{
fromsql.Append(", Category c, package_category pc");
wheresql.Append($" and p.packageId=pc.packageId and pc.categoryid=c.categoryid and categoryname like '%{cats}%'");
}
if (supname.Length > 0 || conts.Length > 0)
{
fromsql.Append(", supplier s, package_supplier ps");
wheresql.Append($" and p.packageId=ps.packageId and ps.Supplierid=s.Supplierid ");
if (supname.Length > 0)
{
wheresql.Append($" and Suppliername like '%{supname}%'");
}
if (conts.Length > 0)
{
wheresql.Append($" and ContractNo like '%{conts}%'");
}
}
if (daysl > 0)
{
wheresql.Append($" and datediff(day,GETDATE(), v.ValidTo) <{daysl} ");
}
wheresql.Append($"{GetWhereWithOrClauseFromColumns(PackageColumns, columnSearch)}");
sql.Append(fromsql);
sql.Append(wheresql);
sql.Append($"order by {sortOrder}");
try
{
var res = db.Query <PackageDets>(sql).Skip(parameters.Start).Take(parameters.Length).ToList();
res.ForEach(r =>
{
r.GeoName = String.Join(", ", db.Query <string>("Select GeoName from GeoTree g, package_Geotree pg where g.GeoTreeId=pg.GeoTreeid and pg.packageId=@0", r.PackageID));
r.CategoryName = String.Join(", ", db.Query <string>("Select CategoryName from Category g, package_Category pg where g.CategoryId=pg.Categoryid and pg.packageId=@0", r.PackageID));
r.SupplierNames = String.Join(", ", db.Query <string>("Select SupplierName from Supplier g, package_Supplier pg where g.SupplierId=pg.Supplierid and pg.packageId=@0", r.PackageID));
r.SupplierContractNos = String.Join(", ", db.Query <string>("Select ContractNo from package_supplier where packageId=@0", r.PackageID));
});
var dataTableResult = new DTResult <PackageDets>
{
draw = parameters.Draw,
data = res,
recordsFiltered = 10,
recordsTotal = res.Count()
};
return(Json(dataTableResult, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
throw ex;
}
}
private PetaPoco.Page<HistoryEntry> PageInternal(long page, long itemsPerPage, QueryParameters parameters, BonusCalculationType bonusCalculation)
{
Debug.Assert(TableExists(SqlTable.WuHistory));
var select = new PetaPoco.Sql(SqlTableCommandDictionary[SqlTable.WuHistory].SelectSql);
select.Append(WhereBuilder.Execute(parameters));
GetProduction.BonusCalculation = bonusCalculation;
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (var database = new PetaPoco.Database(connection))
{
PetaPoco.Page<HistoryEntry> query = database.Page<HistoryEntry>(page, itemsPerPage, select);
Debug.Assert(query != null);
return query;
}
}
}
private IList<HistoryEntry> FetchInternal(QueryParameters parameters, BonusCalculationType bonusCalculation)
{
Debug.Assert(TableExists(SqlTable.WuHistory));
var select = new PetaPoco.Sql(SqlTableCommandDictionary[SqlTable.WuHistory].SelectSql);
select.Append(WhereBuilder.Execute(parameters));
GetProduction.BonusCalculation = bonusCalculation;
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (var database = new PetaPoco.Database(connection))
{
List<HistoryEntry> query = database.Fetch<HistoryEntry>(select);
return query;
}
}
}
public ActionResult PackagesPartialView(ServiceTypeEnum?st, IEnumerable <int> CatID, IEnumerable <int> ActID, int?Gsize, int?diff, int?dur, int?GuideLanguageID, IEnumerable <int> AttractID, IEnumerable <int> FacilityID, decimal?maxPrice, decimal?minPrice, int?NoPax, int?large, int?small, int?hasAc, int?HasCarrier, IEnumerable <int> DestIds, int?IsBike)
{
string Dests = "";
if (DestIds != null)
{
Dests = string.Join(",", DestIds.ToArray());
}
PetaPoco.Sql MainSql = new PetaPoco.Sql();
PetaPoco.Sql FromSql = new PetaPoco.Sql();
PetaPoco.Sql WhereSql = new PetaPoco.Sql();
ViewBag.st = st;
if (st == ServiceTypeEnum.Accomodation)
{
ViewBag.ServiceTitle = "Accomodations";
MainSql = new PetaPoco.Sql(" Select Count(rv.ReviewID) as TotalReview,Avg(rv.Value) as AvgReview,a.AccomodationID as ServiceId,@0 as ServiceTypeId, " +
"a.AccomName as ServiceName, substring(a.Description, 0, 100) + '...' as ServiceDescription," +
"g.geoName as ServiceGeoName, min(pr.price) as price ", (int)ServiceTypeEnum.Accomodation);
FromSql = new PetaPoco.Sql("from Accomodation a left join Review rv on rv.ServiceID = a.AccomodationID " +
$"left join GeoTree g on a.geoTreeId = g.GeoTreeId and a.GeoTreeID in (SELECT GeoTreeID FROM STRING_SPLIT('{Dests}', ',') CROSS APPLY dbo.GetChildGeos(value))" +
"left join Prices pr on pr.PriceID = (select top 1 PriceID from Prices where a.AccomodationID = Prices.ServiceID and Prices.WEF < GetDate() order by Prices.WEF desc) " +
"left join OptionType ot on ot.OptionTypeID = pr.OptionTypeID " +
"left join Facility_Accomodation fa on a.AccomodationID = fa.AccomodationID " +
"left join Facility f on f.FacilityID = fa.FacilityID ");
WhereSql = new PetaPoco.Sql($"where ot.ServiceTypeID=@0 ", ServiceTypeEnum.Accomodation);
if (maxPrice != null && minPrice != null)
{
WhereSql.Append(" and Price Between @0 and @1", minPrice, maxPrice);
}
if (FacilityID != null)
{
WhereSql.Append(" and fa.FacilityID in (@0)", FacilityID.ToArray());
}
MainSql.Append(FromSql);
MainSql.Append(WhereSql);
MainSql.Append(" Group by a.AccomodationID, a.AccomName, [Description], g.geoName ");
var accom = db.Query <AccomodationDets>(MainSql);
accom = accom.OrderBy(a => a.AccomName);
}
if (st == ServiceTypeEnum.CarBike)
{
ViewBag.ServiceTitle = "Car And Bikes Rental";
MainSql = new PetaPoco.Sql("Select Count(rv.ReviewID) as TotalReview,Avg(rv.Value) as AvgReview,[CarBikeID] as ServiceId, [CarBikeName] as ServiceName,@0 as ServiceTypeId, g.geoName as ServiceGeoName, " +
"substring(description, 0, 100) + '...' as ServiceDescription, min(pr.price) as price ", (int)ServiceTypeEnum.CarBike);
FromSql = new PetaPoco.Sql("From CarBike c left join Review rv on rv.ServiceID = c.CarBikeID " +
"left join GeoTree g on c.geoTreeId=c.GeoTreeId " +
"left join Prices pr on pr.PriceID = (select top 1 PriceID from Prices where c.CarBikeID = Prices.ServiceID " +
"and Prices.WEF < GetDate() order by Prices.WEF desc) " +
"left join OptionType ot on ot.OptionTypeID = pr.OptionTypeID ");
WhereSql = new PetaPoco.Sql($"where ot.ServiceTypeID=@0 and c.GeoTreeID in (SELECT GeoTreeID " +
$"FROM STRING_SPLIT('{Dests}', ',') CROSS APPLY dbo.GetChildGeos(value)) ", ServiceTypeEnum.CarBike);
if (maxPrice != null && minPrice != null)
{
WhereSql.Append(" and Price Between @0 and @1 ", minPrice, maxPrice);
}
if (NoPax != null)
{
WhereSql.Append(" and c.NoPax<=@0", NoPax);
}
if (large != null)
{
WhereSql.Append(" and c.NoLargeBags<=@0", large);
}
if (small != null)
{
WhereSql.Append(" and c.NoSmallBags<=@0", small);
}
if (hasAc != null)
{
WhereSql.Append(" and c.HasAc=@0", hasAc);
}
if (HasCarrier != null)
{
WhereSql.Append(" and c.Hascarrier=@0", HasCarrier);
}
if (IsBike != null)
{
WhereSql.Append(" and c.IsBike=@0", IsBike);
}
MainSql.Append(FromSql);
MainSql.Append(WhereSql);
MainSql.Append(" Group by c.CarBikeID, c.CarBikeName, [Description], g.geoName ");
}
if (st == ServiceTypeEnum.Packages || st == ServiceTypeEnum.SightSeeing || st == ServiceTypeEnum.Cruise)
{
ViewBag.ServiceTitle = "Our Best Tours And Excursions";
MainSql = new PetaPoco.Sql("Select Count(rv.ReviewID) as TotalReview,Avg(rv.Value) as AvgReview," +
" pv.PackageID as ServiceId,pv.ServiceTypeID ,pv.PackageName as ServiceName, " +
"substring(pv.Description, 0, 100) + '...' as ServiceDescription, min(coalesce(pr.price, 0)) as price ");
FromSql = new PetaPoco.Sql("from Package pv left join Review rv on rv.ServiceID = pv.PackageID " +
"left join Prices pr on pr.PriceID = (select top 1 PriceID from Prices " +
"where pv.PackageID = Prices.ServiceID and Prices.WEF < GetDate() order by Prices.WEF desc) " +
"left join OptionType ot on ot.OptionTypeID = pr.OptionTypeID " +
"left join Package_Category pc on pc.PackageID = pv.PackageID " +
"left join Category c on c.CategoryID = pc.CategoryID " +
"left join Package_Activity pa on pa.PackageID = pv.PackageID " +
"left join Activity a on a.ActivityID = pa.ActivityID " +
"left join Package_Attraction pat on pat.PackageID = pv.PackageID " +
"left join Attraaction at on at.AttractionID = pat.AttractionID " +
"left join Package_Language pl on pl.PackageId = pv.PackageID " +
"left join GuideLanguage gl on gl.GuideLanguageID = pl.GuideLanguageId " +
"left join Package_GeoTree pg on pg.PackageID = pv.PackageID");
WhereSql = new PetaPoco.Sql(" where pv.ServiceTypeID=@0", (int)st);
//WhereSql.Append(" and p.PackageID = pr.ServiceID and ot.OptionTypeID = pr.OptionTypeID and pr.PriceID = (select top 1 PriceID from Prices where p.PackageID = Prices.ServiceID and ot.OptionTypeID = Prices.OptionTypeID and Prices.WEF<GetDate() order by Prices.WEF desc) ");
if (maxPrice != null && minPrice != null)
{
WhereSql.Append(" and Price Between @0 and @1 ", minPrice, maxPrice);
}
if (CatID != null)
{
WhereSql.Append(" and pc.CategoryID in (@0)", CatID.ToArray());
}
if (ActID != null)
{
WhereSql.Append(" and pa.ActivityID in (@0)", ActID.ToArray());
}
if (AttractID != null)
{
WhereSql.Append(" and pat.AttractionID in (@0)", AttractID.ToArray());
}
if (Gsize != null)
{
WhereSql.Append(" and pv.GroupSize <= @0", Gsize);
}
if (diff != null)
{
WhereSql.Append(" and pv.Dificulty <= @0", diff);
}
if (dur != null)
{
WhereSql.Append(" and pv.Duration <= @0", dur);
}
if (GuideLanguageID != null)
{
WhereSql.Append(" and pl.GuideLanguageId =@0 ", GuideLanguageID);
}
//Filter for destination
WhereSql.Append($" and pg.GeoTreeId in (SELECT GeoTreeID FROM STRING_SPLIT('{Dests}', ',') CROSS APPLY dbo.GetChildGeos(value))");
MainSql.Append(FromSql);
MainSql.Append(WhereSql);
MainSql.Append(" Group by pv.PackageID, pv.ServiceTypeID,pv.PackageName, pv.[Description] ");
}
MainSql.Append(" order by min(pr.price)");
var apc = db.Fetch <AccomPackCarBike>(MainSql);
foreach (var i in apc)
{
i.ServicePic = db.FirstOrDefault <PictureDets>("Select Top 1 * From Picture Where ServiceID=@0 and ServiceTypeID=@1", i.ServiceID, i.ServiceTypeID)?.PictureName ?? "";
if (string.IsNullOrWhiteSpace(i.ServiceGeoName))
{
i.ServiceGeoName = db.First <string>("Select GeoName From GeoTree g,Package_GeoTree pg where pg.PackageID=@0 and g.GeoTreeID = pg.GeoTreeID", i.ServiceID);
}
i.Attributes = db.Fetch <Attribute>("Select * from Attribute a, Package_attribute pa where a.attributeID=pa.attributeID and pa.packageID=@0 and pa.serviceTypeId=@1", i.ServiceID, i.ServiceTypeID);
i.Icons = db.Fetch <string>("select IconPath from Icons where ServiceId=@0 and ServiceTypeId=@1", i.ServiceID, i.ServiceTypeID);
}
return(PartialView(apc));
}
public ActionResult DReciept(string DriverName, bool?IsSearch, decimal?OA, int?id, decimal?ManAmt, int?SRID, decimal?TotalAmt, int?check)
{
ViewBag.Type = Enum.GetValues(typeof(AmtType)).Cast <AmtType>().Select(v => new SelectListItem {
Text = v.ToString(), Value = ((int)v).ToString()
}).ToList();
decimal usedAmt = db.ExecuteScalar <decimal?>("Select Coalesce(sum(Amount),0) From DRP_SR Where DRPDID =@0", id) ?? 0;
decimal ActualOA = db.ExecuteScalar <decimal?>("Select Coalesce(sum(Cost),0) From SRdetails Where SRID =@0", SRID) ?? 0;
if (SRID != null)
{
usedAmt += ManAmt ?? OA ?? 0;
if (OA != null || ManAmt != null)
{
if (OA <= TotalAmt || ManAmt <= TotalAmt)
{
if (ManAmt == null)
{
ManAmt = 0;
}
if ((ManAmt < OA && usedAmt <= TotalAmt) || (OA <= TotalAmt && ManAmt <= TotalAmt && usedAmt <= TotalAmt && ManAmt <= OA))
{
var PExist = db.ExecuteScalar <decimal?>("Select Coalesce(Amount,0) From DRP_SR Where DRPDID =@0 and SRID = @1", id, SRID) ?? 0;
if (PExist != 0) //if there is an existing DRP-SR for this booking update it
{
if (ManAmt <= OA && OA <= TotalAmt && PExist < (OA + ActualOA))
{
db.Execute("Delete from DRP_SR Where SRID=@0 and DRPDID =@1", SRID, id);
}
}
if (ManAmt <= OA || (PExist < (OA + ActualOA) && OA <= TotalAmt))
{
if (ManAmt == 0)
{
ManAmt = null;
}
ManAmt += PExist;
OA += PExist;
db.Insert(new DRP_SR {
SRID = (int)SRID, DRPDID = (int)id, Amount = ManAmt ?? OA
});
if (usedAmt == TotalAmt)
{
db.Execute("Update DRPDets set AmtUsed=@0 where DRPDID=@1", true, id);
}
}
}
}
}
}
var NPbkngs = new PetaPoco.Sql($"select sd.SRID,sr.BookingNo,d.DriverID,d.DriverName as UserName,PayTo,sum(SellPrice) as SellPrice ,sum(SellPrice-Cost) as OA," +
$"(select coalesce (sum(Amount),0) from DRP_SR where SRID = sd.SRID ) as PaidAmt " +
$"from SRdetails sd inner join ServiceRequest sr on sd.SRID=sr.SRID inner join Driver d on d.DriverID=sd.DriverID where ");
if (check == 1)
{
NPbkngs.Append($" lower(PayTo) like '%us%' ");
}
else
{
NPbkngs.Append($" lower(PayTo) like '%driver%' ");
}
if (DriverName != null)
{
NPbkngs.Append($" and LOWER(d.DriverName) like '%{DriverName.ToLower()}%'");
}
NPbkngs.Append(" group by d.DriverID,d.DriverName,PayTo,sd.SRID, sr.BookingNo");
var bkngs = db.Query <SRBooking>(NPbkngs).Where(a => a.OA > 0).ToList();
ViewBag.UnUsedP = db.Fetch <RPDetails>("Select rp.CDate as [Date], rp.DRPDID,rp.Amount,rp.Type,(Select Coalesce(Sum(Amount),0) from DRP_SR Where DRPDID = rp.DRPDID) as UnUsedAmt from DRPdets rp where AmtUsed is Null and IsPayment = @0", check);
decimal getT = db.ExecuteScalar <decimal?>("Select Amount from DRPDets Where DRPDID=@0", id) ?? 0;
ViewBag.TotAmt = getT - usedAmt;
ViewBag.Bookings = bkngs;
if (IsSearch == true)
{
ViewBag.DReciepts = "true";
ViewBag.check = check;
ViewBag.DRPDID = id;
return(PartialView("_SearchBooking"));
}
var p = "";
if (check == 1)
{
p = "DriverP";
}
else
{
p = "DriverR";
}
return(View(p, base.BaseCreateEdit <DRPdet>(id, "DRPDID")));
}
public ActionResult Reciept(string CustName, string AgentName, bool?IsSearch, decimal?OA, int?id, decimal?ManAmt, int?SRID, decimal?TotalAmt)
{
ViewBag.Type = Enum.GetValues(typeof(AmtType)).Cast <AmtType>().Select(v => new SelectListItem {
Text = v.ToString(), Value = ((int)v).ToString()
}).ToList();
decimal usedAmt = db.ExecuteScalar <decimal?>("Select Coalesce(sum(Amount),0) From RP_SR Where RPDID =@0", id) ?? 0;
decimal ActualOA = db.ExecuteScalar <decimal?>("Select Coalesce(sum(SellPrice),0) From SRdetails Where SRID =@0", SRID) ?? 0;
if (SRID != null)
{
usedAmt += ManAmt ?? OA ?? 0;
if (OA != null || ManAmt != null)
{
if (OA <= TotalAmt || ManAmt <= TotalAmt)
{
if (ManAmt == null)
{
ManAmt = 0;
}
if ((ManAmt < OA && usedAmt <= TotalAmt) || (OA <= TotalAmt && ManAmt <= TotalAmt && usedAmt <= TotalAmt && ManAmt <= OA))
{
var PExist = db.ExecuteScalar <decimal?>("Select Coalesce(Amount,0) From RP_SR Where RPDID =@0 and SRID = @1", id, SRID) ?? 0;
if (PExist != 0)
{
if (ManAmt <= OA && PExist < (OA + ActualOA))
{
db.Execute("Delete from RP_SR Where SRID=@0 and RPDID =@1", SRID, id);
}
}
if (ManAmt <= OA || (PExist < (OA + ActualOA) && OA <= TotalAmt))
{
if (ManAmt == 0)
{
ManAmt = null;
}
ManAmt += PExist;
OA += PExist;
db.Insert(new RP_SR {
SRID = (int)SRID, RPDID = (int)id, Amount = ManAmt ?? OA
});
if (usedAmt == TotalAmt)
{
db.Execute("Update RPDets set AmtUsed=@0 where RPDID=@1", true, id);
}
int pid = 0;
if (OA != null || OA == ManAmt)
{
pid = (int)PayType.Full_Paid;
}
if (ManAmt != null && (ManAmt <= (OA + PExist)) && OA != ManAmt)
{
pid = (int)PayType.Part_Paid;
}
if (pid == (int)PayType.Full_Paid)
{
db.Execute("Update ServiceRequest set PayStatusID =@0, SRStatusID = @1 Where SRID = @2 ", pid, (int)SRStatusEnum.Confirmed, SRID);
}
else
{
db.Execute("Update ServiceRequest set PayStatusID =@0 Where SRID = @1 ", pid, SRID);
}
}
}
}
}
}
var NPbkngs = new PetaPoco.Sql($"Select distinct sr.SRID,sr.BookingNo, CONCAT(c.FName,' ' ,c.SName) as cName,anu.UserName," +
"(select Coalesce(sum(SellPrice),0) From SRdetails Where SRID =sr.SRID) as OA ," +
"(Select Coalesce(Sum(Amount),0) from RP_SR rs Where SRID=sr.SRID) as PaidAmt " +
"from ServiceRequest sr left join AspNetUsers anu on anu.Id = sr.AgentID left join Customer c on c.CustomerID =sr.CustID " +
$"Where sr.PayStatusID in ({(int)PayType.Not_Paid},{(int)PayType.Part_Paid})");
if (CustName?.Length > 0)
{
NPbkngs.Append($" and LOWER(CONCAT(c.FName,' ' ,c.SName)) like '%{CustName.ToLower()}%'");
}
if (AgentName?.Length > 0)
{
NPbkngs.Append($" and LOWER(anu.UserName) like '%{AgentName}%'");
}
NPbkngs.Append(" order by sr.BookingNo desc");
var bkngs = db.Query <SRBooking>(NPbkngs).Where(a => a.OA > 0).ToList();
ViewBag.UnUsedP = db.Fetch <RPDetails>("Select rp.RPDID,rp.Amount,rp.Type,(Select Coalesce(Sum(Amount),0) from RP_SR Where RPDID = rp.RPDID) as UnUsedAmt from RPdets rp where AmtUsed is Null and IsPayment = @0", false);
decimal getT = db.ExecuteScalar <decimal?>("Select Amount from RPDets Where RPDID=@0", id) ?? 0;
ViewBag.TotAmt = getT - usedAmt;
ViewBag.Bookings = bkngs;
if (IsSearch == true)
{
ViewBag.Reciepts = "true";
ViewBag.RPDID = id;
return(PartialView("_SearchBooking"));
}
return(View(base.BaseCreateEdit <RPdet>(id, "RPDID")));
}
public ActionResult Payment(int?SupplierID, bool?IsSearch, decimal?OA, int?id, decimal?ManAmt, int?SRID, int?SRDID, decimal?TotalAmt)
{
ViewBag.Type = Enum.GetValues(typeof(AmtType)).Cast <AmtType>().Select(v => new SelectListItem {
Text = v.ToString(), Value = ((int)v).ToString()
}).ToList();
decimal usedAmt = db.ExecuteScalar <decimal?>("Select Coalesce(sum(Amount),0) From RP_SR Where RPDID =@0", id) ?? 0;
decimal ActualOA = db.ExecuteScalar <decimal?>("Select Coalesce(sum(Cost),0) From SRdetails Where SRID =@0", SRID) ?? 0;
if (SRID != null)
{
usedAmt += ManAmt ?? OA ?? 0;
if (OA != null || ManAmt != null)
{
if (OA <= TotalAmt || ManAmt <= TotalAmt)
{
if (ManAmt == null)
{
ManAmt = 0;
}
if ((ManAmt < OA && usedAmt <= TotalAmt) || (OA <= TotalAmt && ManAmt <= TotalAmt && usedAmt <= TotalAmt && ManAmt <= OA))
{
var PExist = db.ExecuteScalar <decimal?>("Select Coalesce(Amount,0) From RP_SR Where RPDID =@0 and SRID = @1", id, SRID) ?? 0;
if (PExist != 0)
{
if (ManAmt <= OA && PExist < (OA + ActualOA))
{
db.Execute("Delete from RP_SR Where SRID=@0 and RPDID =@1", SRID, id);
}
}
if (ManAmt <= OA || (PExist < (OA + ActualOA) && OA <= TotalAmt))
{
if (ManAmt == 0)
{
ManAmt = null;
}
ManAmt += PExist;
OA += PExist;
db.Insert(new RP_SR {
SRID = (int)SRID, RPDID = (int)id, Amount = ManAmt ?? OA, SRDID = SupplierID
});
if (usedAmt == TotalAmt)
{
db.Execute("Update RPDets set AmtUsed=@0 where RPDID=@1", true, id);
}
/* int pid = 0;
* if (OA != null || OA == ManAmt)
* {
* pid = (int)PayType.Full_Paid;
* }
* if (ManAmt != null && (ManAmt <= (OA + PExist)) && OA != ManAmt)
* {
* pid = (int)PayType.Part_Paid;
* }
*
*
* db.Execute("Update ServiceRequest set PayStatusID =@0 Where SRID = @1 ", pid, SRID);*/
}
}
}
}
}
var NPbkngs = new PetaPoco.Sql("Select sr.BookingNo, (select sum(Amount) from RP_SR where SRID=sd.SRID and SRDID=sd.SupplierID) as PaidAmt,sd.SRID,sd.SupplierID," +
"s.SupplierName as UserName,Sum(sd.Cost) as OA From SRdetails sd inner join Supplier s on s.SupplierID=sd.SupplierID inner join ServiceRequest sr on sr.SRID =sd.SRID " +
"Where sd.SupplierID is not null and sr.PayStatusID <>@0", PayType.Cancelled);
if (SupplierID != null)
{
NPbkngs.Append($" and sd.SupplierID = {SupplierID}");
}
NPbkngs.Append(" Group By sr.BookingNo,sd.SupplierID,sd.SRID,s.SupplierName");
var bkngs = db.Query <SRBooking>(NPbkngs).Where(a => a.OA > 0).ToList();
ViewBag.UnUsedP = db.Fetch <RPDetails>("Select rp.RPDID,rp.Amount,rp.Type,(Select Coalesce(Sum(Amount),0) from RP_SR Where RPDID = rp.RPDID) as UnUsedAmt from RPdets rp where AmtUsed is Null and IsPayment =@0", true);
decimal getT = db.ExecuteScalar <decimal?>("Select Amount from RPDets Where RPDID=@0", id) ?? 0;
ViewBag.TotAmt = getT - usedAmt;
ViewBag.Bookings = bkngs;
if (IsSearch == true)
{
ViewBag.Payments = "true";
ViewBag.RPDID = id;
return(PartialView("_SearchBooking"));
}
return(View(base.BaseCreateEdit <RPdet>(id, "RPDID")));
}
public JsonResult GetAccomList(DTParameters parameters)
{
var columnSearch = parameters.Columns.Select(s => s.Search.Value).Take(AccomColumns.Count()).ToList();
//XMLPath uses nested queries so to avoid that we construct these 4 filters ourselves
string geos = "";
string fac = "";
if (columnSearch[2]?.Length > 0)
{
geos = columnSearch[2]; columnSearch[2] = null;
}
if (columnSearch[4]?.Length > 0)
{
fac = columnSearch[4]; columnSearch[4] = null;
}
var sql = new PetaPoco.Sql($"Select distinct a.*,Substring(Description,1,100) as Description from Accomodation a");
var fromsql = new PetaPoco.Sql();
var wheresql = new PetaPoco.Sql("where a.AccomodationID >0 ");
if (geos.Length > 0)
{
fromsql.Append(", Geotree g");
wheresql.Append($"and g.GeoTreeID=a.GeoTreeID and geoname like '%{geos}%'");
}
if (fac.Length > 0)
{
fromsql.Append(", Facility f,Facility_Accomodation fa");
wheresql.Append($" and f.FacilityID=fa.FacilityID and a.AccomodationID = fa.AccomodationID and FacilityName like '%{fac}%'");
}
wheresql.Append($"{GetWhereWithOrClauseFromColumns(AccomColumns, columnSearch)}");
sql.Append(fromsql);
sql.Append(wheresql);
try
{
var res = db.Query <AccomodationDets>(sql).Skip(parameters.Start).Take(parameters.Length).ToList();
res.ForEach(r =>
{
r.GeoName = String.Join(", ", db.Query <string>("Select GeoName from GeoTree where GeoTreeID=@0", r.GeoTreeID));
r.FacilityName = String.Join(", ", db.Query <string>("Select FacilityName from Facility f, Facility_Accomodation fa where f.FacilityID=fa.FacilityID and fa.AccomodationID=@0", r.AccomodationID));
});
var dataTableResult = new DTResult <AccomodationDets>
{
draw = parameters.Draw,
data = res,
recordsFiltered = 10,
recordsTotal = res.Count()
};
return(Json(dataTableResult, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
throw ex;
}
}
public void Append_GivenSimpleStrings()
{
_sql.Append("LINE 1");
_sql.Append("LINE 2");
_sql.Append("LINE 3");
Assert.Equal("LINE 1\r\nLINE 2\r\nLINE 3", _sql.SQL);
Assert.Empty(_sql.Arguments);
}
internal static PetaPoco.Sql Append(this PetaPoco.Sql sql, WorkUnitQuery query)
{
return(sql.Append(query.ToSql()));
}