1073 lines
31 KiB
Go
1073 lines
31 KiB
Go
package domain
|
|
|
|
import (
|
|
"nearle/db"
|
|
"nearle/models"
|
|
"nearle/utils"
|
|
"strconv"
|
|
"strings"
|
|
)
|
|
|
|
// base = `SELECT DISTINCT a.orderheaderid, a.applocationid, h.locationname AS applocation, a.tenantid, a.locationid, a.partnerid, a.configid, a.categoryid, a.subcategoryid, a.moduleid,
|
|
// a.orderid, a.orderstatus, a.orderdate, a.ordernotes, a.itemcount, a.deliverytime AS deliverydate,
|
|
// a.pending, a.processing, a.ready, a.delivered AS completed, a.cancelled, f.orderstatus AS deliverystatus,
|
|
// f.assigntime, f.starttime, f.arrivaltime, f.pickuptime, f.deliverytime, f.canceltime,
|
|
// a.deliverycharge, a.kms, f.actualkms, f.deliveryamt,
|
|
// a.customerid, a.pickuplocationid, a.pickupaddress, a.pickuplat, a.pickuplong,
|
|
// a.pickupcustomer, a.pickupcontactno, a.pickuplocation as pickupsuburb, a.pickupcity,
|
|
// a.deliveryid AS deliverycustomerid, a.deliveryaddress, a.deliverylat, a.deliverylong, a.deliverytype,
|
|
// a.deliverycustomer,a.deliverycontactno,a.deliverylocation as deliverysuburb, a.deliverycity, a.paymenttype, a.smsdelivery, b.customertoken,
|
|
// c.tenantname, c.tenanttoken, c.primarycontact AS tenantcontactno, c.postcode AS tenantpostcode, c.suburb AS tenantsuburb, c.city AS tenantcity,
|
|
// d.locationname, d.contactno AS locationcontactno, d.postcode AS locationpostcode, d.suburb AS locationsuburb, d.city AS locationcity, g.firstname AS rider, g.contactno AS ridercontactno,
|
|
// g.userfcmtoken as ridertoken
|
|
// FROM orders a
|
|
// INNER JOIN customers b ON a.customerid = b.customerid
|
|
// INNER JOIN tenants c ON a.tenantid = c.tenantid
|
|
// INNER JOIN tenantlocations d ON a.locationid = d.locationid
|
|
// LEFT JOIN deliveries f ON a.orderheaderid = f.orderheaderid
|
|
// LEFT JOIN app_users g ON f.userid = g.userid
|
|
// INNER JOIN app_location h ON a.applocationid = h.applocationid
|
|
// INNER JOIN app_locationconfig i ON a.applocationid = i.applocationid`
|
|
|
|
const (
|
|
base = `SELECT DISTINCT a.orderheaderid, a.applocationid, h.locationname AS applocation, a.tenantid, a.locationid, a.partnerid, a.configid, a.categoryid, a.subcategoryid, a.moduleid,
|
|
a.orderid, a.orderstatus, a.orderdate, a.ordernotes, a.itemcount, a.deliverytime AS deliverydate,
|
|
a.pending, a.processing, a.ready, a.delivered AS completed, a.cancelled,
|
|
a.deliverycharge, a.kms,
|
|
a.customerid, a.pickuplocationid, a.pickupaddress, a.pickuplat, a.pickuplong, a.pickupslot,
|
|
a.pickupcustomer, a.pickupcontactno, a.pickuplocation as pickupsuburb, a.pickupcity,
|
|
a.deliveryid AS deliverycustomerid, a.deliveryaddress, a.deliverylat, a.deliverylong, a.deliverytype,
|
|
a.deliverycustomer,a.deliverycontactno,a.deliverylocation as deliverysuburb, a.deliverycity, a.paymenttype, a.smsdelivery, a.quantity, a.collectionamt, b.customertoken,
|
|
c.tenantname, c.tenanttoken, c.primarycontact AS tenantcontactno, c.postcode AS tenantpostcode, c.suburb AS tenantsuburb, c.city AS tenantcity,
|
|
d.locationname, d.contactno AS locationcontactno, d.postcode AS locationpostcode, d.suburb AS locationsuburb, d.city AS locationcity,
|
|
f.slab, f.pricingdate, f.baseprice, f.minkm, f.priceperkm, f.maxkm, f.orders, f.othercharges, f.surgecharges
|
|
FROM orders a
|
|
LEFT JOIN customers b ON a.customerid = b.customerid
|
|
INNER JOIN tenants c ON a.tenantid = c.tenantid
|
|
INNER JOIN tenantlocations d ON a.locationid = d.locationid
|
|
INNER JOIN app_location h ON a.applocationid = h.applocationid
|
|
INNER JOIN app_locationconfig i ON a.applocationid = i.applocationid
|
|
LEFT JOIN tenantpricing f ON a.tenantid = f.tenantid`
|
|
|
|
orderdetails = `SELECT DISTINCT a.orderheaderid, a.applocationid,
|
|
a.tenantid, a.locationid, a.partnerid, a.configid, a.categoryid, a.subcategoryid, a.moduleid,
|
|
a.orderid, a.orderstatus, a.orderdate, a.ordernotes, a.itemcount, a.deliverytime AS deliverydate,
|
|
a.pending, a.processing, a.ready, a.delivered AS completed, a.cancelled,
|
|
a.deliverycharge, a.kms,
|
|
a.customerid, a.pickupaddress, a.pickuplat, a.pickuplong, a.pickupslot,
|
|
a.pickupcustomer, a.pickupcontactno, a.pickuplocation as pickupsuburb, a.pickupcity,
|
|
a.deliveryid AS deliverycustomerid, a.deliveryaddress, a.deliverylat, a.deliverylong, a.deliverytype,
|
|
a.deliverycustomer,a.deliverycontactno, a.deliverylocation as deliverysuburb, a.deliverycity, a.paymenttype, a.smsdelivery, a.orderamount, a.quantity, a.collectionamt,
|
|
b.tenantname, b.tenanttoken, b.primarycontact AS tenantcontactno, b.postcode AS tenantpostcode, b.suburb AS tenantsuburb,b.city AS tenantcity, b.address AS tenantaddress,
|
|
c.locationname, c.contactno AS locationcontactno, c.postcode AS locationpostcode, c.suburb AS locationsuburb, c.city AS locationcity, c.address AS locationaddress,
|
|
d.locationname AS applocation, f.slab, f.pricingdate, f.baseprice, f.minkm, f.priceperkm, f.maxkm, f.orders, f.othercharges, f.surgecharges
|
|
FROM orders a
|
|
INNER JOIN tenants b ON a.tenantid = b.tenantid
|
|
INNER JOIN tenantlocations c ON a.locationid = c.locationid
|
|
INNER JOIN app_location d ON a.applocationid = d.applocationid
|
|
INNER JOIN app_locationconfig e ON d.applocationid = e.applocationid
|
|
LEFT JOIN tenantpricing f ON a.tenantid = f.tenantid`
|
|
)
|
|
|
|
func GetTenantLocationOrders(input models.DeliveryQuery) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
var query string
|
|
var params []interface{}
|
|
|
|
offset := (input.Pageno - 1) * input.Pagesize
|
|
baseQuery := base + ` WHERE c.moduleid = 6 and a.tenantid = ? and a.locationid = ?`
|
|
params = append(params, input.Tenantid, input.Locationid)
|
|
|
|
utils.Info("Fetching Tenant Location Orders")
|
|
// Order status filtering
|
|
if input.Status == "ongoing" {
|
|
query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
|
|
} else {
|
|
query = baseQuery + ` AND a.orderstatus = ?`
|
|
params = append(params, input.Status)
|
|
}
|
|
|
|
// Date filtering
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, input.Fromdate, input.ToDate)
|
|
|
|
// 🔍 Case-insensitive keyword filter
|
|
if input.Keyword != "" {
|
|
|
|
k := "%" + strings.ToLower(input.Keyword) + "%"
|
|
|
|
query += ` AND (
|
|
LOWER(a.pickupcustomer) LIKE ? OR
|
|
LOWER(c.tenantname) LIKE ? OR
|
|
LOWER(a.deliverycustomer) LIKE ? OR
|
|
LOWER(a.pickupcontactno) LIKE ? OR
|
|
LOWER(a.deliverycontactno) LIKE ? OR
|
|
LOWER(a.orderid) LIKE ?
|
|
)`
|
|
|
|
params = append(params, k, k, k, k, k, k)
|
|
}
|
|
|
|
// 🔧 Config ID filter
|
|
if input.Configid != 0 {
|
|
query += ` AND a.configid = ?`
|
|
params = append(params, input.Configid)
|
|
}
|
|
|
|
// Final ordering & paging
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, input.Pagesize, offset)
|
|
|
|
// Debug log
|
|
utils.Logger.Debugf("Executing: %s", query)
|
|
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
return data
|
|
}
|
|
func GetTenantLocationAppOrders(input models.DeliveryQuery) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
var query string
|
|
var params []interface{}
|
|
|
|
offset := (input.Pageno - 1) * input.Pagesize
|
|
|
|
baseQuery := base + ` WHERE c.moduleid = 6
|
|
AND a.tenantid = ?
|
|
AND a.locationid = ?
|
|
AND a.applocationid = ?`
|
|
|
|
params = append(params, input.Tenantid, input.Locationid, input.Applocationid)
|
|
|
|
// status
|
|
if input.Status == "ongoing" {
|
|
query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
|
|
} else {
|
|
query = baseQuery + ` AND a.orderstatus = ?`
|
|
params = append(params, input.Status)
|
|
}
|
|
|
|
// date filter
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, input.Fromdate, input.ToDate)
|
|
|
|
// keyword filter
|
|
if input.Keyword != "" {
|
|
k := "%" + strings.ToLower(input.Keyword) + "%"
|
|
query += ` AND (
|
|
LOWER(a.pickupcustomer) LIKE ? OR
|
|
LOWER(c.tenantname) LIKE ? OR
|
|
LOWER(a.deliverycustomer) LIKE ? OR
|
|
LOWER(a.pickupcontactno) LIKE ? OR
|
|
LOWER(a.deliverycontactno) LIKE ? OR
|
|
LOWER(a.orderid) LIKE ?
|
|
)`
|
|
params = append(params, k, k, k, k, k, k)
|
|
}
|
|
|
|
// configid filter
|
|
if input.Configid != 0 {
|
|
query += ` AND a.configid = ?`
|
|
params = append(params, input.Configid)
|
|
}
|
|
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, input.Pagesize, offset)
|
|
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
return data
|
|
}
|
|
|
|
func GetTenantAppOrders(input models.DeliveryQuery) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
var query string
|
|
var params []interface{}
|
|
|
|
offset := (input.Pageno - 1) * input.Pagesize
|
|
|
|
// Base query for Tenant + AppLocation
|
|
baseQuery := base + ` WHERE c.moduleid = 6
|
|
AND a.tenantid = ?
|
|
AND a.applocationid = ?`
|
|
|
|
params = append(params, input.Tenantid, input.Applocationid)
|
|
|
|
// 🔥 Status filter
|
|
if input.Status == "ongoing" {
|
|
query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
|
|
} else {
|
|
query = baseQuery + ` AND a.orderstatus = ?`
|
|
params = append(params, input.Status)
|
|
}
|
|
|
|
// 📅 Date filter
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, input.Fromdate, input.ToDate)
|
|
|
|
// 🔍 Keyword (case-insensitive)
|
|
if input.Keyword != "" {
|
|
k := "%" + strings.ToLower(input.Keyword) + "%"
|
|
|
|
query += ` AND (
|
|
LOWER(a.pickupcustomer) LIKE ? OR
|
|
LOWER(c.tenantname) LIKE ? OR
|
|
LOWER(a.deliverycustomer) LIKE ? OR
|
|
LOWER(a.pickupcontactno) LIKE ? OR
|
|
LOWER(a.deliverycontactno) LIKE ? OR
|
|
LOWER(a.orderid) LIKE ?
|
|
)`
|
|
params = append(params, k, k, k, k, k, k)
|
|
}
|
|
|
|
// ⚙️ Config ID filter
|
|
if input.Configid != 0 {
|
|
query += ` AND a.configid = ?`
|
|
params = append(params, input.Configid)
|
|
}
|
|
|
|
// 📦 Order & Pagination
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, input.Pagesize, offset)
|
|
|
|
// Execute
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
|
|
return data
|
|
}
|
|
|
|
func GetAppOrders(input models.DeliveryQuery) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
var query string
|
|
var params []interface{}
|
|
|
|
offset := (input.Pageno - 1) * input.Pagesize
|
|
|
|
// Base query for only AppLocation
|
|
baseQuery := base + ` WHERE c.moduleid = 6
|
|
AND a.applocationid = ?`
|
|
|
|
params = append(params, input.Applocationid)
|
|
|
|
// 🔥 Status filter
|
|
if input.Status == "ongoing" {
|
|
query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
|
|
} else {
|
|
query = baseQuery + ` AND a.orderstatus = ?`
|
|
params = append(params, input.Status)
|
|
}
|
|
|
|
// 📅 Date filter
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, input.Fromdate, input.ToDate)
|
|
|
|
// 🔍 Keyword (case-insensitive)
|
|
if input.Keyword != "" {
|
|
k := "%" + strings.ToLower(input.Keyword) + "%"
|
|
|
|
query += ` AND (
|
|
LOWER(a.pickupcustomer) LIKE ? OR
|
|
LOWER(c.tenantname) LIKE ? OR
|
|
LOWER(a.deliverycustomer) LIKE ? OR
|
|
LOWER(a.pickupcontactno) LIKE ? OR
|
|
LOWER(a.deliverycontactno) LIKE ? OR
|
|
LOWER(a.orderid) LIKE ?
|
|
)`
|
|
params = append(params, k, k, k, k, k, k)
|
|
}
|
|
|
|
// ⚙️ Config ID filter
|
|
if input.Configid != 0 {
|
|
query += ` AND a.configid = ?`
|
|
params = append(params, input.Configid)
|
|
}
|
|
|
|
// 📦 Order & Pagination
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, input.Pagesize, offset)
|
|
|
|
// Execute
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
|
|
return data
|
|
}
|
|
|
|
func GetTenantOrders(input models.DeliveryQuery) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
var query string
|
|
var params []interface{}
|
|
|
|
offset := (input.Pageno - 1) * input.Pagesize
|
|
baseQuery := base + ` WHERE c.moduleid = 6 and a.tenantid = ?`
|
|
params = append(params, input.Tenantid)
|
|
|
|
utils.Info("Fetching Tenant Orders")
|
|
// Order status filtering
|
|
if input.Status == "ongoing" {
|
|
query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
|
|
} else {
|
|
query = baseQuery + ` AND a.orderstatus = ?`
|
|
params = append(params, input.Status)
|
|
}
|
|
|
|
// Date filtering
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, input.Fromdate, input.ToDate)
|
|
|
|
// 🔍 Keyword filter (case-insensitive)
|
|
if input.Keyword != "" {
|
|
|
|
keyword := strings.ToLower(input.Keyword)
|
|
like := "%" + keyword + "%"
|
|
|
|
query += ` AND (
|
|
LOWER(a.pickupcustomer) LIKE ? OR
|
|
LOWER(c.tenantname) LIKE ? OR
|
|
LOWER(a.deliverycustomer) LIKE ? OR
|
|
LOWER(a.pickupcontactno) LIKE ? OR
|
|
LOWER(a.deliverycontactno) LIKE ? OR
|
|
LOWER(a.orderid) LIKE ?
|
|
)`
|
|
|
|
params = append(params, like, like, like, like, like, like)
|
|
}
|
|
|
|
// 🔧 Config ID filter
|
|
if input.Configid != 0 {
|
|
query += ` AND a.configid = ?`
|
|
params = append(params, input.Configid)
|
|
}
|
|
|
|
// Final ordering & paging
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, input.Pagesize, offset)
|
|
|
|
// Debug log
|
|
utils.Logger.Debugf("Executing: %s", query)
|
|
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
return data
|
|
}
|
|
|
|
func GetTenantOrdersv2(stat, fdate, tdate string, tid, pageno, pagesize int) []models.OrderInfo {
|
|
|
|
var q1 string
|
|
|
|
var data []models.OrderInfo
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
if stat == "ongoing" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.tenantid=? and a.orderstatus in ('pending','processing','ready') and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.tenantid=? and a.orderstatus= '` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.deliverytime LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
// print(q1)
|
|
db.DB.Raw(q1, tid).Find(&data)
|
|
|
|
return data
|
|
|
|
}
|
|
|
|
func GetPartnerOrders(stat, fdate, tdate string, pid, pageno, pagesize int, keyword string) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
|
|
if pageno <= 0 {
|
|
pageno = 1
|
|
}
|
|
if pagesize <= 0 {
|
|
pagesize = 10
|
|
}
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
utils.Info("Getting partner order details")
|
|
|
|
// Base SELECT query (with joins)
|
|
query := orderdetails + ` WHERE b.moduleid = 6 and a.partnerid = ?`
|
|
params := []interface{}{pid}
|
|
|
|
// Filter conditions
|
|
if fdate != "" && tdate != "" {
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, fdate, tdate)
|
|
}
|
|
if stat != "" {
|
|
query += ` AND a.orderstatus = ?`
|
|
params = append(params, stat)
|
|
}
|
|
if keyword != "" {
|
|
query += ` AND (
|
|
a.pickupcustomer LIKE ? OR
|
|
b.tenantname LIKE ? OR
|
|
a.deliverycustomer LIKE ? OR
|
|
a.pickupcontactno LIKE ? OR
|
|
a.deliverycontactno LIKE ? OR
|
|
a.orderid LIKE ?
|
|
)`
|
|
like := "%" + keyword + "%"
|
|
params = append(params, like, like, like, like, like, like)
|
|
}
|
|
|
|
// ✅ Count query for pagination
|
|
var total int64
|
|
countQuery := `
|
|
SELECT COUNT(DISTINCT a.orderheaderid)
|
|
FROM orders a
|
|
INNER JOIN tenants b ON a.tenantid = b.tenantid
|
|
INNER JOIN tenantlocations c ON a.locationid = c.locationid
|
|
INNER JOIN app_location d ON a.applocationid = d.applocationid
|
|
INNER JOIN app_locationconfig e ON d.applocationid = e.applocationid
|
|
WHERE a.partnerid = ?`
|
|
countParams := []interface{}{pid}
|
|
|
|
// Reapply filters to countQuery
|
|
if fdate != "" && tdate != "" {
|
|
countQuery += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
countParams = append(countParams, fdate, tdate)
|
|
}
|
|
if stat != "" {
|
|
countQuery += ` AND a.orderstatus = ?`
|
|
countParams = append(countParams, stat)
|
|
}
|
|
if keyword != "" {
|
|
countQuery += ` AND (
|
|
a.pickupcustomer LIKE ? OR
|
|
b.tenantname LIKE ? OR
|
|
a.deliverycustomer LIKE ? OR
|
|
a.pickupcontactno LIKE ? OR
|
|
a.deliverycontactno LIKE ? OR
|
|
a.orderid LIKE ?
|
|
)`
|
|
like := "%" + keyword + "%"
|
|
countParams = append(countParams, like, like, like, like, like, like)
|
|
}
|
|
|
|
db.DB.Raw(countQuery, countParams...).Scan(&total)
|
|
|
|
// ✅ Offset boundary fix
|
|
if int64(offset) >= total {
|
|
offset = 0
|
|
pageno = 1
|
|
}
|
|
|
|
// Append pagination
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, pagesize, offset)
|
|
|
|
utils.Logger.Debugf("QUERY: %s", query)
|
|
utils.Logger.Debugf("PARAMS: %v", params)
|
|
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
utils.Logger.Infof("RESULT COUNT: %d", len(data))
|
|
|
|
return data
|
|
}
|
|
|
|
func GetPartnerOrdersv2(stat, fdate, tdate string, pid, pageno, pagesize int) []models.OrderInfo {
|
|
|
|
var data []models.OrderInfo
|
|
var q1 string
|
|
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
if stat != "" {
|
|
|
|
if fdate != "" && tdate != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.partnerid=` + strconv.Itoa(pid) + ` and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.partnerid=` + strconv.Itoa(pid) + ` and a.orderstatus='` + stat + `'
|
|
order by a.deliverytime LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.partnerid=` + strconv.Itoa(pid) + ` and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
|
|
// print(q1)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
return data
|
|
}
|
|
|
|
func GetAllOrders(stat, fdate, tdate string, aid, pageno, pagesize int, keyword string) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
offset := (pageno - 1) * pagesize
|
|
utils.Info("All orders")
|
|
|
|
// Base query
|
|
query := orderdetails + `
|
|
WHERE b.moduleid = 6
|
|
AND a.deliverytime::date BETWEEN ? AND ?
|
|
`
|
|
params := []interface{}{fdate, tdate}
|
|
|
|
// 🔹 applocationid filter
|
|
if aid != 0 {
|
|
query += ` AND a.applocationid = ?`
|
|
params = append(params, aid)
|
|
}
|
|
|
|
// 🔹 Status filter
|
|
if stat != "" {
|
|
query += ` AND a.orderstatus = ?`
|
|
params = append(params, stat)
|
|
}
|
|
|
|
// 🔹 Keyword filter (case-insensitive)
|
|
if keyword != "" {
|
|
k := "%" + strings.ToLower(keyword) + "%"
|
|
|
|
query += `
|
|
AND (
|
|
LOWER(a.pickupcustomer) LIKE ? OR
|
|
LOWER(b.tenantname) LIKE ? OR
|
|
LOWER(a.deliverycustomer) LIKE ? OR
|
|
LOWER(a.pickupcontactno) LIKE ? OR
|
|
LOWER(a.deliverycontactno) LIKE ? OR
|
|
LOWER(a.orderid) LIKE ?
|
|
)`
|
|
|
|
params = append(params, k, k, k, k, k, k)
|
|
}
|
|
|
|
// Sorting + pagination
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, pagesize, offset)
|
|
|
|
// Debug
|
|
utils.Logger.Debugf("Executing query: %s", query)
|
|
print(query)
|
|
// DB execution
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
|
|
return data
|
|
}
|
|
|
|
func GetAllOrdersv2(stat, fdate, tdate string, pageno, pagesize int) []models.OrderInfo {
|
|
|
|
var data []models.OrderInfo
|
|
var q1 string
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
if stat != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
|
|
utils.Logger.Debugf("Query: %s", q1)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
return data
|
|
}
|
|
|
|
func GetUserOrdersv2(stat, fdate, tdate string, uid, pageno, pagesize int) []models.OrderInfo {
|
|
|
|
var data []models.OrderInfo
|
|
var q1 string
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
if uid != 0 {
|
|
|
|
if fdate != "" && tdate != "" {
|
|
|
|
if stat != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and e.status = 'Active' and e.userid=` + strconv.Itoa(uid) + ` and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and e.status = 'Active' and e.userid=` + strconv.Itoa(uid) + ` and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
|
|
} else if stat != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and e.status = 'Active' and e.userid=` + strconv.Itoa(uid) + ` and a.orderstatus='` + stat + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
|
|
}
|
|
|
|
// print(q1)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
return data
|
|
}
|
|
|
|
func GetLocationOrders(stat, fdate, tdate string, lid, pageno, pagesize int, keyword string) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
utils.Info("Getting location order details")
|
|
|
|
// Start query and params
|
|
query := orderdetails + `
|
|
WHERE b.moduleid = 6 and e.status = 'Active' AND a.locationid = ?
|
|
`
|
|
params := []interface{}{lid}
|
|
|
|
// Add date filter
|
|
if fdate != "" && tdate != "" {
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, fdate, tdate)
|
|
}
|
|
|
|
// Add status filter
|
|
if stat != "" {
|
|
query += ` AND a.orderstatus = ?`
|
|
params = append(params, stat)
|
|
}
|
|
|
|
// Add keyword filter
|
|
if keyword != "" {
|
|
query += `
|
|
AND (
|
|
a.pickupcustomer LIKE ? OR
|
|
a.tenantname LIKE ? OR
|
|
a.deliverycustomer LIKE ? OR
|
|
a.pickupcontactno LIKE ? OR
|
|
a.deliverycontactno LIKE ? OR
|
|
a.orderid LIKE ?
|
|
)`
|
|
likeKeyword := "%" + keyword + "%"
|
|
params = append(params, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword)
|
|
}
|
|
|
|
// Add sorting and pagination
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, pagesize, offset)
|
|
|
|
// Debugging
|
|
utils.Logger.Debugf("Executing query: %s", query)
|
|
|
|
// Execute
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
|
|
return data
|
|
}
|
|
|
|
func GetUserOrders(stat, fdate, tdate string, uid, pageno, pagesize int, keyword string) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
utils.Info("Getting user order details")
|
|
|
|
// Start query and params
|
|
query := orderdetails + `
|
|
WHERE b.moduleid = 6 and e.status = 'Active' AND e.userid = ?
|
|
`
|
|
params := []interface{}{uid}
|
|
|
|
// Add date filter
|
|
if fdate != "" && tdate != "" {
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, fdate, tdate)
|
|
}
|
|
|
|
// Add status filter
|
|
if stat != "" {
|
|
query += ` AND a.orderstatus = ?`
|
|
params = append(params, stat)
|
|
}
|
|
|
|
// Add keyword filter
|
|
if keyword != "" {
|
|
query += `
|
|
AND (
|
|
a.pickupcustomer LIKE ? OR
|
|
a.tenantname LIKE ? OR
|
|
a.deliverycustomer LIKE ? OR
|
|
a.pickupcontactno LIKE ? OR
|
|
a.deliverycontactno LIKE ? OR
|
|
a.orderid LIKE ?
|
|
)`
|
|
likeKeyword := "%" + keyword + "%"
|
|
params = append(params, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword)
|
|
}
|
|
|
|
// Add sorting and pagination
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, pagesize, offset)
|
|
|
|
// Debugging
|
|
utils.Logger.Debugf("Executing query: %s", query)
|
|
|
|
// Execute
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
|
|
return data
|
|
}
|
|
|
|
func GetAdminOrders(stat, fdate, tdate string, aid, pageno, pagesize int, keyword string) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
utils.Info("Getting admin order details")
|
|
|
|
// Start query and params
|
|
query := orderdetails + ` WHERE 1=1`
|
|
var params []interface{}
|
|
|
|
// applocationid filter (aid)
|
|
if aid != 0 {
|
|
query += ` AND a.applocationid = ?`
|
|
params = append(params, aid)
|
|
}
|
|
|
|
// date filter
|
|
if fdate != "" && tdate != "" {
|
|
query += ` AND a.deliverytime::date BETWEEN ? AND ?`
|
|
params = append(params, fdate, tdate)
|
|
}
|
|
|
|
// status filter
|
|
if stat != "" {
|
|
query += ` AND a.orderstatus = ?`
|
|
params = append(params, stat)
|
|
}
|
|
|
|
// keyword search
|
|
if keyword != "" {
|
|
query += `
|
|
AND (
|
|
a.pickupcustomer LIKE ? OR
|
|
a.tenantname LIKE ? OR
|
|
a.deliverycustomer LIKE ? OR
|
|
a.pickupcontactno LIKE ? OR
|
|
a.deliverycontactno LIKE ? OR
|
|
a.orderid LIKE ?
|
|
)`
|
|
likeKeyword := "%" + keyword + "%"
|
|
params = append(params,
|
|
likeKeyword, likeKeyword, likeKeyword,
|
|
likeKeyword, likeKeyword, likeKeyword,
|
|
)
|
|
}
|
|
|
|
// Sorting and pagination
|
|
query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, pagesize, offset)
|
|
|
|
// Debugging
|
|
utils.Logger.Debugf("Executing query: %s", query)
|
|
|
|
// Execute the query
|
|
db.DB.Raw(query, params...).Find(&data)
|
|
|
|
return data
|
|
}
|
|
|
|
func GetAdminOrdersv2(stat, fdate, tdate string, aid, pageno, pagesize int) []models.OrderInfo {
|
|
|
|
var data []models.OrderInfo
|
|
var q1 string
|
|
|
|
utils.Info("Getting admin order details")
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
if aid != 0 {
|
|
|
|
if fdate != "" && tdate != "" {
|
|
|
|
if stat != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.applocationid=` + strconv.Itoa(aid) + ` and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.applocationid=` + strconv.Itoa(aid) + ` and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
|
|
} else if stat != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.applocationid=` + strconv.Itoa(aid) + ` and a.orderstatus='` + stat + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
} else {
|
|
|
|
if stat != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
|
|
order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)
|
|
|
|
}
|
|
|
|
}
|
|
|
|
utils.Logger.Debugf("Query: %s", q1)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
return data
|
|
}
|
|
|
|
func GetCustomerOrders(stat, fdate, tdate string, cid, mid, pageno, pagesize int, keyword string) []models.OrderInfo {
|
|
var data []models.OrderInfo
|
|
utils.Info("Getting customer order details")
|
|
// Safety defaults
|
|
if pageno <= 0 {
|
|
pageno = 1
|
|
}
|
|
if pagesize <= 0 {
|
|
pagesize = 10
|
|
}
|
|
offset := (pageno - 1) * pagesize
|
|
|
|
// Prepare base query
|
|
baseQuery := orderdetails + ` WHERE b.moduleid = 6 and a.customerid = ?`
|
|
params := []interface{}{cid}
|
|
|
|
// Filters
|
|
if mid != 0 {
|
|
baseQuery += ` AND a.moduleid = ?`
|
|
params = append(params, mid)
|
|
}
|
|
if fdate != "" && tdate != "" {
|
|
baseQuery += ` AND a.orderdate::date BETWEEN ? AND ?`
|
|
params = append(params, fdate, tdate)
|
|
}
|
|
if stat != "" {
|
|
baseQuery += ` AND a.orderstatus = ?`
|
|
params = append(params, stat)
|
|
}
|
|
if keyword != "" {
|
|
baseQuery += ` AND (
|
|
a.pickupcustomer LIKE ? OR
|
|
b.tenantname LIKE ? OR
|
|
a.deliverycustomer LIKE ? OR
|
|
a.pickupcontactno LIKE ? OR
|
|
a.deliverycontactno LIKE ? OR
|
|
a.orderid LIKE ?
|
|
)`
|
|
like := "%" + keyword + "%"
|
|
params = append(params, like, like, like, like, like, like)
|
|
}
|
|
|
|
// Count total
|
|
var total int64
|
|
countQuery := strings.Replace(baseQuery, "SELECT DISTINCT a.orderheaderid, ", "SELECT COUNT(DISTINCT a.orderheaderid) ", 1)
|
|
db.DB.Raw(countQuery, params...).Scan(&total)
|
|
|
|
// Prevent out-of-bound offset
|
|
if int64(offset) >= total {
|
|
offset = 0
|
|
pageno = 1
|
|
}
|
|
|
|
// Final paginated query
|
|
finalQuery := baseQuery + ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
|
|
params = append(params, pagesize, offset)
|
|
|
|
utils.Logger.Debugf("QUERY: %s", finalQuery)
|
|
utils.Logger.Debugf("PARAMS: %v", params)
|
|
|
|
res := db.DB.Raw(finalQuery, params...).Find(&data)
|
|
if res.Error != nil {
|
|
utils.Error("ERROR querying customer orders", "error", res.Error)
|
|
}
|
|
utils.Logger.Infof("RESULT COUNT: %d", len(data))
|
|
|
|
print(finalQuery)
|
|
|
|
return data
|
|
}
|
|
|
|
func GetCustomerOrdersv2(stat, fdate, tdate string, cid, mid, pageno, pagesize int) []models.OrderInfo {
|
|
|
|
var data []models.OrderInfo
|
|
var q1 string
|
|
|
|
if stat != "" {
|
|
|
|
if fdate != "" && tdate != "" {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.customerid=` + strconv.Itoa(cid) + ` and a.orderstatus='` + stat + `' and a.orderdate::date between '` + fdate + `' and '` + tdate + `' order by a.orderheaderid desc`
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=6 and a.customerid=` + strconv.Itoa(cid) + ` and a.orderstatus='` + stat + `' order by a.orderheaderid desc`
|
|
|
|
}
|
|
|
|
} else {
|
|
|
|
if mid != 0 {
|
|
|
|
q1 = orderdetails + ` where a.moduleid=` + strconv.Itoa(mid) + ` and a.customerid=` + strconv.Itoa(cid) + ` and a.orderdate::date between '` + fdate + `' and '` + tdate + `' order by a.orderheaderid desc`
|
|
|
|
} else {
|
|
|
|
q1 = orderdetails + ` where a.customerid=` + strconv.Itoa(cid) + ` and a.orderdate::date between '` + fdate + `' and '` + tdate + `' order by a.orderheaderid desc`
|
|
|
|
}
|
|
|
|
}
|
|
|
|
// print(q1)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
return data
|
|
}
|
|
|
|
func GetOrderbyid(oid int) models.OrderInfo {
|
|
|
|
var data models.OrderInfo
|
|
|
|
q1 := orderdetails + ` where a.orderheaderid=` + strconv.Itoa(oid)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
|
|
return data
|
|
|
|
}
|
|
|
|
func GetOrderbyStatus(uid int, stat string) models.OrderInfo {
|
|
|
|
var data models.OrderInfo
|
|
|
|
q1 := orderdetails + ` where a.orderheaderid=` + strconv.Itoa(uid)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
|
|
return data
|
|
|
|
}
|
|
|
|
func GetOrderbyidV2(oid int) models.OrderInfo {
|
|
|
|
var data models.OrderInfo
|
|
|
|
q1 := orderdetails + ` where a.orderheaderid=` + strconv.Itoa(oid)
|
|
|
|
db.DB.Raw(q1).Find(&data)
|
|
|
|
return data
|
|
|
|
}
|
|
|
|
func GetOrderDetailsByHeaderID(orderHeaderID int) ([]models.OrderDetails, float64, float64, error) {
|
|
var details []models.OrderDetails
|
|
var orderAmount float64
|
|
var totalTaxAmount float64
|
|
|
|
query := `
|
|
SELECT
|
|
a.orderdetailid,
|
|
a.orderheaderid,
|
|
a.tenantid,
|
|
a.locationid,
|
|
a.productid,
|
|
a.productname,
|
|
a.productdescription,
|
|
a.supplyqty,
|
|
a.balanceqty,
|
|
a.orderqty,
|
|
a.price,
|
|
a.unitid,
|
|
a.unitname,
|
|
a.productaddonid,
|
|
a.addontypeid,
|
|
a.productmapid,
|
|
a.productvariantid,
|
|
a.productaddondescription,
|
|
a.discountid,
|
|
a.discountname,
|
|
a.discountcode,
|
|
a.discountterms,
|
|
a.discountpercentage,
|
|
a.discountamount,
|
|
a.landingamount,
|
|
a.taxpercentage,
|
|
a.taxamount,
|
|
a.productsumprice,
|
|
a.itemstatus,
|
|
a.delivered,
|
|
a.taxamount,
|
|
COALESCE(b.orderamount, 0) as orderamount,
|
|
COALESCE(b.taxamount, 0) as totaltaxamount,
|
|
c.productimage
|
|
FROM orderdetails a
|
|
LEFT JOIN orders b ON b.orderheaderid = a.orderheaderid
|
|
LEFT JOIN products c ON a.productid = c.productid
|
|
WHERE a.orderheaderid = ?
|
|
`
|
|
|
|
err := db.DB.Raw(query, orderHeaderID).Scan(&details).Error
|
|
if err != nil {
|
|
return nil, 0, 0, err
|
|
}
|
|
|
|
if len(details) > 0 {
|
|
orderAmount = details[0].Orderamount
|
|
}
|
|
|
|
if len(details) > 0 {
|
|
totalTaxAmount = details[0].Totaltaxamount
|
|
}
|
|
|
|
return details, orderAmount, totalTaxAmount, nil
|
|
}
|
|
|
|
func GetCustomerOrderByLocation(customerid int) ([]models.CustomerOrderLocation, error) {
|
|
query := `
|
|
SELECT
|
|
c.locationname AS locationname,
|
|
COUNT(a.orderid) AS ordercount,
|
|
c.contactno AS contactno,
|
|
c.address AS address,
|
|
c.suburb AS suburb,
|
|
c.city AS city,
|
|
c.state AS state,
|
|
c.postcode AS postcode,
|
|
c.latitude AS latitude,
|
|
c.longitude AS longitude
|
|
FROM orders a
|
|
LEFT JOIN tenantlocations c ON a.locationid = c.locationid
|
|
WHERE a.customerid = ?
|
|
GROUP BY
|
|
c.locationname
|
|
`
|
|
|
|
var result []models.CustomerOrderLocation
|
|
err := db.DB.Raw(query, customerid).Scan(&result).Error
|
|
|
|
// Debug actual data
|
|
utils.Logger.Debugf("result: %+v", result)
|
|
|
|
// return [] instead of null
|
|
if result == nil {
|
|
result = []models.CustomerOrderLocation{}
|
|
}
|
|
|
|
return result, err
|
|
}
|