private string GoodsReceiptSaveRelativeBuildSQL(GlobalEnums.GoodsReceiptTypeID goodsReceiptTypeID)
{
string queryString;
string entityDetail = goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice ? "PurchaseInvoiceDetails" : goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer ? "StockTransferDetails" : "";
string entityID = goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice ? "PurchaseInvoiceID" : goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer ? "StockTransferID" : "";
string entityDetailID = goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice ? "PurchaseInvoiceDetailID" : goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer ? "StockTransferDetailID" : "";
queryString = " IF @GoodsReceiptTypeID = " + (int)goodsReceiptTypeID + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " UPDATE " + entityDetail + "\r\n";
queryString = queryString + " SET " + entityDetail + ".QuantityReceipt = ROUND(" + entityDetail + ".QuantityReceipt + GoodsReceiptDetails.Quantity * @SaveRelativeOption, 0) " + "\r\n";
queryString = queryString + " FROM GoodsReceiptDetails INNER JOIN " + "\r\n";
queryString = queryString + " " + entityDetail + " ON GoodsReceiptDetails.GoodsReceiptID = @EntityID AND GoodsReceiptDetails.VoucherDetailID = " + entityDetail + "." + entityDetailID + "\r\n";
queryString = queryString + " END " + "\r\n";
return(queryString);
}
private string GetGoodsReceiptViewDetailsBuilSQL(GlobalEnums.GoodsReceiptTypeID goodsReceiptTypeID)
{
string queryString; string queryEdit; string queryNew = ""; string querySort;
string entity = goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice ? "PurchaseInvoices" : goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer ? "StockTransfers" : "";
string entityDetail = goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice ? "PurchaseInvoiceDetails" : goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer ? "StockTransferDetails" : "";
string entityID = goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice ? "PurchaseInvoiceID" : goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer ? "StockTransferID" : "";
string entityDetailID = goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice ? "PurchaseInvoiceDetailID" : goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer ? "StockTransferDetailID" : "";
if (goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice)
{
queryNew = " SELECT " + entityDetail + "." + entityID + " AS VoucherID, 0 AS GoodsReceiptDetailID, 0 AS GoodsReceiptID, " + entityDetail + "." + entityDetailID + " AS VoucherDetailID, " + "\r\n";
queryNew = queryNew + " " + entityDetail + ".SupplierID, " + entityDetail + ".CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, " + entityDetail + ".Origin, " + entityDetail + ".Packing, ROUND(" + entityDetail + ".Quantity - " + entityDetail + ".QuantityReceipt, 0) AS QuantityRemains, " + "\r\n";
queryNew = queryNew + " 0.0 AS Quantity, " + entityDetail + ".UnitPrice, " + entityDetail + ".VATPercent, " + entityDetail + ".GrossPrice, 0.0 AS Amount, 0.0 AS VATAmount, 0.0 AS GrossAmount, " + "\r\n";
queryNew = queryNew + " LWarehouses.WarehouseID, LWarehouses.Code AS WarehouseCode, " + entityDetail + ".ChassisCode, " + entityDetail + ".EngineCode, " + entityDetail + ".ColorCode, " + entityDetail + ".Remarks " + "\r\n";
queryNew = queryNew + " FROM " + entityDetail + " INNER JOIN " + "\r\n";
queryNew = queryNew + " Commodities ON " + entityDetail + ".CommodityID = Commodities.CommodityID INNER JOIN " + "\r\n";
queryNew = queryNew + " (SELECT DISTINCT LocationID, WarehouseID, Code FROM Warehouses) LWarehouses ON " + entityDetail + ".LocationID = LWarehouses.LocationID " + "\r\n"; //INIT Warehouses BY LOCATION: NOTE: IN ACCESS CONTROL: USER SHOULD ONLY HAVE EDITABLE PERMISSION IN THIER LOCATIONID ONLY
queryNew = queryNew + " WHERE (@VoucherID = 0 OR " + entityDetail + "." + entityID + " = @VoucherID) AND ROUND(" + entityDetail + ".Quantity - " + entityDetail + ".QuantityReceipt, 0) > 0 " + "\r\n"; //These WHERE CLAUSE are the same WHEN ANY goodsReceiptTypeID //AND Approved = 1
}
if (goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer)
{
queryNew = " SELECT " + entityDetail + "." + entityID + " AS VoucherID, 0 AS GoodsReceiptDetailID, 0 AS GoodsReceiptID, " + entityDetail + "." + entityDetailID + " AS VoucherDetailID, " + "\r\n";
queryNew = queryNew + " " + entityDetail + ".SupplierID, " + entityDetail + ".CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, GoodsReceiptDetails.Origin, GoodsReceiptDetails.Packing, ROUND(" + entityDetail + ".Quantity - " + entityDetail + ".QuantityReceipt, 0) AS QuantityRemains, " + "\r\n";
queryNew = queryNew + " 0.0 AS Quantity, ISNULL(GoodsReceiptDetails.UnitPrice, 0.0) AS UnitPrice, ISNULL(GoodsReceiptDetails.VATPercent, 0.0) AS VATPercent, ISNULL(GoodsReceiptDetails.GrossPrice, 0.0) AS GrossPrice, 0.0 AS Amount, 0.0 AS VATAmount, 0.0 AS GrossAmount, " + "\r\n";
queryNew = queryNew + " " + entity + ".WarehouseID, Warehouses.Code AS WarehouseCode, GoodsReceiptDetails.ChassisCode, GoodsReceiptDetails.EngineCode, GoodsReceiptDetails.ColorCode, " + entityDetail + ".Remarks " + "\r\n";
queryNew = queryNew + " FROM " + entity + " INNER JOIN " + "\r\n";
queryNew = queryNew + " " + entityDetail + " ON " + entity + "." + entityID + " = " + entityDetail + "." + entityID + " INNER JOIN " + "\r\n";
queryNew = queryNew + " Warehouses ON " + entity + ".WarehouseID = Warehouses.WarehouseID INNER JOIN " + "\r\n"; //Need to join Warehouses to get: WarehouseID, Warehouses.Name: NOT ALLOW CHANGE IN GoodsReceipts WHEN the goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer
queryNew = queryNew + " Commodities ON " + entityDetail + ".CommodityID = Commodities.CommodityID LEFT JOIN " + "\r\n";
queryNew = queryNew + " GoodsReceiptDetails ON " + entityDetail + ".GoodsReceiptDetailID = GoodsReceiptDetails.GoodsReceiptDetailID " + "\r\n"; //Need to left join GoodsReceiptDetails to get: Origin, ChassisCode, ... (VehicleTransfer)
queryNew = queryNew + " WHERE (@VoucherID = 0 OR " + entityDetail + "." + entityID + " = @VoucherID) AND ROUND(" + entityDetail + ".Quantity - " + entityDetail + ".QuantityReceipt, 0) > 0 " + "\r\n"; //These WHERE CLAUSE are the same WHEN ANY goodsReceiptTypeID //AND Approved = 1
}
queryEdit = " SELECT " + entityDetail + "." + entityID + " AS VoucherID, GoodsReceiptDetails.GoodsReceiptDetailID, GoodsReceiptDetails.GoodsReceiptID, " + entityDetail + "." + entityDetailID + " AS VoucherDetailID, " + "\r\n";
queryEdit = queryEdit + " GoodsReceiptDetails.SupplierID, " + entityDetail + ".CommodityID, Commodities.Code AS CommodityCode, Commodities.Name AS CommodityName, Commodities.CommodityTypeID, GoodsReceiptDetails.Origin, GoodsReceiptDetails.Packing, ROUND(" + entityDetail + ".Quantity - " + entityDetail + ".QuantityReceipt + GoodsReceiptDetails.Quantity, 0) AS QuantityRemains, " + "\r\n";
queryEdit = queryEdit + " GoodsReceiptDetails.Quantity, GoodsReceiptDetails.UnitPrice, GoodsReceiptDetails.VATPercent, GoodsReceiptDetails.GrossPrice, GoodsReceiptDetails.Amount, GoodsReceiptDetails.VATAmount, GoodsReceiptDetails.GrossAmount, " + "\r\n";
queryEdit = queryEdit + " Warehouses.WarehouseID, Warehouses.Code AS WarehouseCode, GoodsReceiptDetails.ChassisCode, GoodsReceiptDetails.EngineCode, GoodsReceiptDetails.ColorCode, GoodsReceiptDetails.Remarks " + "\r\n";
queryEdit = queryEdit + " FROM " + entityDetail + " INNER JOIN " + "\r\n";
queryEdit = queryEdit + " GoodsReceiptDetails ON GoodsReceiptDetails.GoodsReceiptTypeID = " + (int)goodsReceiptTypeID + " AND " + entityDetail + "." + entityDetailID + " = GoodsReceiptDetails.VoucherDetailID INNER JOIN " + "\r\n";
queryEdit = queryEdit + " Commodities ON GoodsReceiptDetails.CommodityID = Commodities.CommodityID INNER JOIN " + "\r\n";
queryEdit = queryEdit + " Warehouses ON GoodsReceiptDetails.WarehouseID = Warehouses.WarehouseID " + "\r\n";
queryEdit = queryEdit + " WHERE (@VoucherID = 0 OR " + entityDetail + "." + entityID + " = @VoucherID) AND GoodsReceiptDetails.GoodsReceiptID = @GoodsReceiptID " + "\r\n";
querySort = " ORDER BY " + entityDetail + "." + entityDetailID;
queryString = " IF @GoodsReceiptTypeID = " + (int)goodsReceiptTypeID;
queryString = queryString + " BEGIN ";
queryString = queryString + " IF (@GoodsReceiptID <= 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " " + queryNew + "\r\n";
queryString = queryString + " " + querySort + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " IF (@IsReadonly = 1) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " " + queryEdit + "\r\n";
queryString = queryString + " " + querySort + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " ELSE " + "\r\n"; //FULL SELECT FOR EDIT MODE
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " " + queryNew + " AND " + entityDetail + "." + entityDetailID + " NOT IN (SELECT VoucherDetailID FROM GoodsReceiptDetails WHERE GoodsReceiptTypeID = " + (int)goodsReceiptTypeID + " AND GoodsReceiptID = @GoodsReceiptID) " + "\r\n";
queryString = queryString + " UNION ALL " + "\r\n";
queryString = queryString + " " + queryEdit + "\r\n";
queryString = queryString + " " + querySort + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " END ";
return(queryString);
}
