Author: Martin Donovan | GitHub gist | profile.json
const { request } = require("express");
const express = require("express");
const pool = require("../../config/database");
const router = express.Router();
const pgdb = require("../../config/database");
const multer = require("multer");
const path = require("path");
const { ensureAuthenticated } = require("../../config/auth");
const ProductInstance = require("../../models/ProductInstance.js");
const pg = require("pg");
const { LargeObjectManager } = require("pg-large-object");
const { createReadStream } = require("fs");
const { createWriteStream } = require("fs");
const passport = require("passport");
const db = require("../../config/pgp_database");
const storage = multer.diskStorage({
destination: "./public/uploads/",
filename: function (req, file, cb) {
cb(null, req.params.id + path.extname(file.originalname));
},
});
const upload = multer({
storage: storage,
limits: { fileSize: 1024 * 1024 * 10 },
fileFilter: function (req, file, cb) {
checkFileType(file, cb);
},
}).single("imagefile");
let checkFileType = (file, cb) => {
const fileTypes = /jpeg|jpg|png|gif/;
const extName = fileTypes.test(path.extname(file.originalname).toLowerCase());
const mimeTypes = fileTypes.test(file.mimetype);
if (mimeTypes && extName) {
return cb(null, true);
} else {
cb("Error - Images Only");
}
};
let insertImageToDB = async (filename, piid, extension) => {
let objID;
db.tx((tx) => {
const man = new LargeObjectManager({ pgPromise: tx });
const bufferSize = 16384;
return man
.createAndWritableStreamAsync(bufferSize)
.then(([oid, stream]) => {
objID = oid;
const fileStream = createReadStream(`./public/uploads/${filename}`);
fileStream.pipe(stream);
return new Promise((resolve, reject) => {
stream.on("finish", resolve);
stream.on("error", reject);
});
});
})
.then(async () => {
await pool.query(
`update product_instances set label_image_oid = $1 , label_image_type = $2 where product_instance_id = $3`,
[objID, extension, piid]
);
require("fs").unlinkSync(`./public/uploads/${filename}`);
})
.catch((error) => {
console.log("Error", error);
});
};
let getImageFromDB = async (piid) => {
const fs = require("fs");
const dir = "./public/images/";
if (!fs.existsSync(dir)) {
fs.mkdirSync(dir);
}
let result = await pool.query(
`select label_image_oid, label_image_type
from product_instances where product_instance_id = $1`,
[piid]
);
if (result.rowCount > 0) {
await db
.tx((tx) => {
const man = new LargeObjectManager({ pgPromise: tx });
const oid = result.rows[0].label_image_oid;
const bufferSize = 16384;
return man
.openAndReadableStreamAsync(oid, bufferSize)
.then(([size, stream]) => {
const fileStream = createWriteStream(`./public/images/${piid}.jpg`);
stream.pipe(fileStream);
return new Promise((resolve, reject) => {
stream.on("end", resolve);
stream.on("error", reject);
});
});
})
.then(() => {})
.catch((error) => {
console.log("Error:", error);
});
}
};
router.get("/:id/labelimage", ensureAuthenticated, async (req, res) => {
const fs = require("fs");
const stream = require("stream");
let piid = parseInt(req.params.id);
await getImageFromDB(parseInt(req.params.id));
const r = fs.createReadStream(`./public/images/${piid}.jpg`);
const ps = new stream.PassThrough();
ps.on("finish", () => {
if (fs.existsSync(`./public/images/${piid}.jpg`)) {
fs.unlinkSync(`./public/images/${piid}.jpg`);
}
});
stream.pipeline(
r,
ps,
(err) => {
if (err) {
console.log(err); // No such file or any other kind of error
return res.sendStatus(400);
}
}
);
ps.pipe(res);
});
router.post("/:id/labelimage", ensureAuthenticated, async (req, res) => {
if (req.user.write_access) {
upload(req, res, async (err) => {
if (err) {
console.log(err);
res.status(500).json({ success: false, msg: "Wrong File Type" });
} else {
if (req.file == undefined) {
res.status(500).json({ success: false, msg: "No File Sent" });
} else {
await insertImageToDB(
req.file.filename,
parseInt(req.params.id),
path.extname(req.file.originalname).toLowerCase()
);
res.status(200).json({ success: true, msg: "File Uploaded" });
}
}
});
} else {
res.status(403).json({ success: false, msg: "Not Authorized" });
}
});
router.put("/:id/multicategories", ensureAuthenticated, async (req, res) => {
if (req.user.write_access) {
const client = await pool.connect();
try {
await client.query("BEGIN");
await client.query(
`delete from product_instance_multi_wine_list_category_associations
where product_instance_id = $1`,
[parseInt(req.params.id)]
);
for (const catid of req.body.wlmulticats) {
await client.query(
`insert into product_instance_multi_wine_list_category_associations
(product_instance_id, wine_list_category_id) values ($1,$2)`,
[parseInt(req.params.id), parseInt(catid)]
);
}
await client.query("COMMIT");
res.status(200).json({ success: true, msg: "Success" });
} catch (error) {
await client.query("ROLLBACK");
throw error;
} finally {
client.release();
}
} else {
res.status(403).json({ success: false, msg: "Not Authorized" });
}
});
router.get("/:id/formulaprice", ensureAuthenticated, (req, res) => {
pool.query(
`select * from get_formula_prices() where piid = $1`,
[req.params.id],
(error, results) => {
if (error) {
throw error;
} else {
res.status(200).json(results.rows[0]);
}
}
);
});
router.put("/:id/ratings", ensureAuthenticated, async (req, res) => {
if (req.user.write_access) {
let qr1 = await pool.query(
`delete from product_instance_ratings where product_instance_id = $1`,
[parseInt(req.params.id)]
);
let qr2 = await pool.query(
`insert into product_instance_ratings
(product_instance_id, ratings_source_id, rating_value)
values ($1,$2,$3)`,
[
parseInt(req.params.id),
parseInt(req.body.ratings_source_id),
parseInt(req.body.rating_value),
]
);
if (qr1.rows > 0 && qr2.rowCount > 0) {
res.status(200).json({ success: true, msg: "Rating added" });
} else {
res.status(500).json({ success: false, msg: "DB Error" });
}
} else {
res.status(403).json({ success: false, msg: "Not authorized" });
}
});
router.get("/:id/ratings", ensureAuthenticated, (req, res) => {
pool.query(
`select * from product_instance_ratings where product_instance_id = $1`,
[parseInt(req.params.id)],
(error, results) => {
if (error) {
throw error;
} else {
res.status(200).json(results.rows);
}
}
);
});
router.delete("/:id/ratings", ensureAuthenticated, (req, res) => {
if (req.user.write_access) {
pool.query(
`delete from product_instance_ratings where product_instance_id = $1`,
[parseInt(req.params.id)],
(error, results) => {
if (error) {
throw error;
} else {
res.status(200).json({ success: true, msg: "Rating Deleted" });
}
}
);
} else {
res.status(403).json({ success: false, msg: "Not Authorized" });
}
});
router.get("/:id/locations", ensureAuthenticated, (req, res) => {
let enddate = new Date().toISOString().slice(0, 10);
pool.query(
`select product_instance_location_associations.*, rooms.room_name,
round(coalesce(get_theoreticals_all.theoretical_on_hand,0.00),2) as theoretical_on_hand
from product_instance_location_associations inner join
rooms on
rooms.room_id = product_instance_location_associations.location_room_id
left outer join get_theoreticals_all($1::date)
on product_instance_location_associations.product_instance_location_id =
get_theoreticals_all.product_instance_location_id
where product_instance_location_associations.product_instance_id = $2
order by active_location desc, default_purchase_location desc, default_sale_location desc, theoretical_on_hand desc`,
[enddate, parseInt(req.params.id)],
(error, results) => {
if (error) {
throw error;
} else {
res.status(200).json(results.rows);
}
}
);
});
router.get("/:id/multicategories", ensureAuthenticated, (req, res) => {
pool.query(
`select * from product_instance_multi_wine_list_category_associations where product_instance_id = $1`,
[parseInt(req.params.id)],
(error, results) => {
if (error) {
throw error;
} else {
res.status(200).json(results.rows);
}
}
);
});
router.post("/", ensureAuthenticated, (req, res) => {
if (req.body !== undefined) {
if (req.user.write_access) {
req.body.product_id = parseInt(req.body.product_id);
req.body.vintage_id = isNaN(parseInt(req.body.vintage_id))
? null
: parseInt(req.body.vintage_id);
req.body.vintage_id === 0 ? null : req.body.vintage_id;
req.body.bottle_size_id = isNaN(parseInt(req.body.bottle_size_id))
? null
: parseInt(req.body.bottle_size_id);
req.body.supplier_company_id = isNaN(
parseInt(req.body.supplier_company_id)
)
? null
: parseInt(req.body.supplier_company_id);
req.body.reorder_level = isNaN(parseFloat(req.body.reorder_level))
? null
: parseFloat(req.body.reorder_level);
req.body.sale_price = isNaN(parseFloat(req.body.sale_price))
? null
: parseFloat(req.body.sale_price);
req.body.glass_sale_price = isNaN(parseFloat(req.body.glass_sale_price))
? null
: parseFloat(req.body.glass_sale_price);
req.body.wine_list_category_id = isNaN(
parseInt(req.body.wine_list_category_id)
)
? null
: parseInt(req.body.wine_list_category_id);
req.body.anticipated_maturity_end = isNaN(
parseInt(req.body.anticipated_maturity_end)
)
? null
: parseInt(req.body.anticipated_maturity_end);
req.body.original_sale_price = isNaN(
parseFloat(req.body.original_sale_price)
)
? null
: parseFloat(req.body.original_sale_price);
req.body.to_go_price = isNaN(parseInt(req.body.to_go_price))
? null
: parseInt(req.body.to_go_price);
pool.query(
`insert into product_instances
(
product_id, inventory_name, active, vintage_id, bottle_size_id,
supplier_company_id, reorder_level, wine_list_name,
sale_price, glass_sale_price, comment, is_club_list_selection,
wine_list_category_id, anticipated_maturity_end,
is_by_the_glass, is_clearance, original_sale_price,
varietal_id, fintech_id
)
values
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19) returning *`,
[
req.body.product_id,
req.body.inventory_name,
true,
req.body.vintage_id,
req.body.bottle_size_id,
req.body.supplier_company_id,
req.body.reorder_level,
req.body.wine_list_name,
req.body.sale_price,
req.body.glass_sale_price,
req.body.comment,
req.body.is_club_list_selection || false,
req.body.wine_list_category_id,
req.body.anticipated_maturity_end,
req.body.is_by_the_glass || false,
req.body.is_clearance || false,
req.body.original_sale_price,
req.body.varietal_id,
req.body.fintech_id,
],
(error, results) => {
if (error) {
throw error;
} else {
res.status(200).json(results.rows[0]);
}
}
);
} else {
res.status(403).json({ success: false, msg: "Not Authorized" });
}
} else {
res.status(400).json({ success: false, msg: "No Message Body" });
}
});
router.put("/:id/unitprice", ensureAuthenticated, async (req, res) => {
if (req.user.write_access) {
if (req.query.unit_price) {
const piid = parseInt(req.params.id);
const up = parseFloat(req.query.unit_price);
let result = await pool.query(
`update product_instances set unit_price = $1 where product_instance_id = $2`,
[up, piid]
);
if (result.rowCount > 0) {
res.status(200).json({ success: true, msg: "Succesful Update" });
} else {
res.status(500).json({ success: false, msg: "No Data" });
}
} else {
res.status(400).json({ success: false, msg: "No Data Sent" });
}
} else {
res.status(403).json({ success: false, msg: "Not Authorized" });
}
});
router.put(["/", "/:id"], ensureAuthenticated, (req, res) => {
if (req.body !== undefined) {
if (req.user.write_access) {
req.body.vintage_id = isNaN(parseInt(req.body.vintage_id))
? null
: parseInt(req.body.vintage_id);
req.body.vintage_id === 0 ? null : req.body.vintage_id;
req.body.bottle_size_id = isNaN(parseInt(req.body.bottle_size_id))
? null
: parseInt(req.body.bottle_size_id);
req.body.supplier_company_id = isNaN(
parseInt(req.body.supplier_company_id)
)
? null
: parseInt(req.body.supplier_company_id);
req.body.reorder_level = isNaN(parseFloat(req.body.reorder_level))
? null
: parseFloat(req.body.reorder_level).toFixed(2);
//changed toFixed to 2 here
req.body.sale_price = isNaN(parseFloat(req.body.sale_price))
? null
: parseFloat(req.body.sale_price).toFixed(2);
req.body.unit_price = isNaN(parseFloat(req.body.unit_price))
? null
: parseFloat(req.body.unit_price).toFixed(2);
req.body.glass_sale_price = isNaN(
parseFloat(req.body.glass_sale_price).toFixed(2)
)
? null
: parseFloat(req.body.glass_sale_price).toFixed(2);
req.body.wine_list_category_id = isNaN(
parseInt(req.body.wine_list_category_id)
)
? null
: parseInt(req.body.wine_list_category_id);
req.body.anticipated_maturity_end = isNaN(
parseInt(req.body.anticipated_maturity_end)
)
? null
: parseInt(req.body.anticipated_maturity_end);
req.body.original_sale_price = isNaN(
parseFloat(req.body.original_sale_price)
)
? null
: parseFloat(req.body.original_sale_price).toFixed(2);
req.body.product_instance_id = parseInt(req.body.product_instance_id);
req.body.wine_list_bin_number = parseInt(req.body.wine_list_bin_number);
req.body.wine_list_bin_number = isNaN(req.body.wine_list_bin_number)
? null
: req.body.wine_list_bin_number;
pool.query(
`update product_instances
set inventory_name = $1,
active = $2,
vintage_id = $3,
bottle_size_id = $4,
supplier_company_id = $5,
reorder_level = $6,
wine_list_name = $7,
sale_price = $8,
glass_sale_price = $9,
comment = $10,
is_club_list_selection = $11,
wine_list_category_id = $12,
anticipated_maturity_end = $13,
is_by_the_glass = $14,
is_clearance = $15,
original_sale_price = $16,
varietal_id = $17,
fintech_id = $18,
pos_story = $19,
unit_price = $20,
wine_list_bin_number = $21
where product_instance_id = $22 returning *`,
[
req.body.inventory_name,
req.body.active,
req.body.vintage_id,
req.body.bottle_size_id,
req.body.supplier_company_id,
req.body.reorder_level,
req.body.wine_list_name,
req.body.sale_price,
req.body.glass_sale_price,
req.body.comment,
req.body.is_club_list_selection,
req.body.wine_list_category_id,
req.body.anticipated_maturity_end,
req.body.is_by_the_glass,
req.body.is_clearance,
req.body.original_sale_price,
req.body.varietal_id,
req.body.fintech_id,
req.body.pos_story,
req.body.unit_price,
req.body.wine_list_bin_number,
req.body.product_instance_id,
],
(error, results) => {
if (error) {
throw error;
} else {
res.status(200).json(results.rows[0]);
}
}
);
} else {
res.status(403).json({ success: false, msg: "Not Authorized" });
}
} else {
res.status(400).json({ success: false, msg: "No Body" });
}
});
router.get("/:id/posplus", ensureAuthenticated, (req, res) => {
pool.query(
`select pos_plu, product_instance_id,
is_inventory_unit, round(number_sales_units,2) as number_sales_units,
is_wine_list_bin_number, vu.volume_unit_id, volume_unit_name,
round(equivalent_ml,2) as euqivalent_ml
from pos_plu_product_instance_associations pppia,
volume_units vu where vu.volume_unit_id = pppia.sales_unit_id
and product_instance_id = $1
order by is_wine_list_bin_number desc, number_sales_units desc`,
[parseInt(req.params.id)],
(error, results) => {
if (error) {
throw error;
}
res.status(200).json(results.rows);
}
);
});
router.get("/:id/averagevaluation", ensureAuthenticated, async (req, res) => {
const piid = parseInt(req.params.id);
const enddate = new Date().toISOString().slice(0, 10);
let numberPurchased = 0;
let extended = 0;
let result1 = await pool.query(
`select
sum(theoretical_on_hand) as on_hand
from get_theoreticals_all($1::date)
where product_instance_id = $2`,
[enddate, piid]
);
let onHand = await result1.rows[0].on_hand;
let result2 = await pool.query(
`select i.payment_date, id.invoice_id,
sum(number_received) as invoice_number_received,
case when sum(id.number_received) = 0
then null
else sum(number_received * price_per_unit) / sum(number_received)
end invoice_average_cost
from invoices i, invoice_details id
where i.invoice_id = id.invoice_id and
id.product_instance_id = $1
group by id.invoice_id, i.payment_date
order by i.payment_date desc `,
[piid]
);
let purchases = result2.rows;
purchases.forEach((p) => {
if (p.invoice_average_cost !== null) {
p.invoice_number_received = parseInt(p.invoice_number_received);
p.invoice_average_cost = parseFloat(p.invoice_average_cost);
if (numberPurchased < onHand) {
let numRemaining = onHand - numberPurchased;
if (numRemaining >= p.invoice_number_received) {
numberPurchased += p.invoice_number_received;
extended += p.invoice_number_received * p.invoice_average_cost;
} else {
numberPurchased += numRemaining;
extended += numRemaining * p.invoice_average_cost;
}
}
}
});
let averageValue = Math.round((extended / numberPurchased) * 100) / 100;
res.status(200).json({
numberPurchased: numberPurchased,
extended: extended,
averageValuation: averageValue,
});
});
router.get("/", ensureAuthenticated, async (req, res) => {
const activeOnly = req.query.active === "true";
const productId = isNaN(parseInt(req.query.productid))
? null
: parseInt(req.query.productid);
let results = await pool.query(
`select pi.*, pi.product_instance_id as value, pi.inventory_name as text
from product_instances pi, products p
where p.product_id = pi.product_id
and ($1::boolean = false or pi.active = true)
and ($2::int is null or pi.product_id = $2)
and p.major_category_id = (
select major_category_id from major_categories
where lower(major_category_name) = 'wine'
)
order by inventory_name`,
[activeOnly, productId]
);
res.status(200).json(results.rows);
});
router.get("/:id", ensureAuthenticated, async (req, res) => {
let results = await pool.query(
`select *
from product_instances
where product_instance_id = $1`,
[parseInt(req.params.id)]
);
res.status(200).json(results.rows[0]);
});
module.exports = router;