public static string ReferencePrefix(GlobalEnums.NmvnTaskID nmvnTaskID)
{
switch (nmvnTaskID)
{
case GlobalEnums.NmvnTaskID.PurchaseOrder:
return "D";
case GlobalEnums.NmvnTaskID.PurchaseInvoice:
return "H";
case GlobalEnums.NmvnTaskID.Quotation:
return "B";
case GlobalEnums.NmvnTaskID.SalesInvoice:
return @"CASE WHEN @SalesInvoiceTypeID =
" + (int)GlobalEnums.SalesInvoiceTypeID.VehiclesInvoice + @" THEN 'X' ELSE
CASE WHEN @SalesInvoiceTypeID =
" + (int)GlobalEnums.SalesInvoiceTypeID.PartsInvoice + @" THEN 'P' ELSE
CASE WHEN @SalesInvoiceTypeID =
" + (int)GlobalEnums.SalesInvoiceTypeID.ServicesInvoice + @" THEN 'S' ELSE '#' END
END END";
case GlobalEnums.NmvnTaskID.AccountInvoice:
return "V";
case GlobalEnums.NmvnTaskID.GoodsReceipt:
return "N";
case GlobalEnums.NmvnTaskID.ServiceContract:
return "H";
case GlobalEnums.NmvnTaskID.TransferOrder:
return @"CASE WHEN @StockTransferTypeID =
" + (int)GlobalEnums.StockTransferTypeID.VehicleTransfer + @" THEN 'LX' ELSE
CASE WHEN @StockTransferTypeID =
" + (int)GlobalEnums.StockTransferTypeID.PartTransfer + @" THEN 'LP' ELSE '#' END
END";
case GlobalEnums.NmvnTaskID.StockTransfer:
return @"CASE WHEN @StockTransferTypeID =
" + (int)GlobalEnums.StockTransferTypeID.VehicleTransfer + @" THEN 'DX' ELSE
CASE WHEN @StockTransferTypeID =
" + (int)GlobalEnums.StockTransferTypeID.PartTransfer + @" THEN 'DP' ELSE '#' END
END";
case GlobalEnums.NmvnTaskID.InventoryAdjustment:
return @"CASE WHEN @InventoryAdjustmentTypeID =
" + (int)GlobalEnums.InventoryAdjustmentTypeID.VehicleAdjustment + @" THEN 'AX' ELSE
CASE WHEN @InventoryAdjustmentTypeID =
" + (int)GlobalEnums.InventoryAdjustmentTypeID.PartAdjustment + @" THEN 'AP' ELSE '#' END
END";
default:
return "";
}
}
private string GoodsReceiptJournalBuild(GlobalEnums.GoodsReceiptTypeID goodsReceiptTypeID)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF (@LocalLocationID = 0) " + "\r\n";
queryString = queryString + " " + this.GoodsReceiptJournalBuildDetail(goodsReceiptTypeID, false) + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " " + this.GoodsReceiptJournalBuildDetail(goodsReceiptTypeID, true) + "\r\n";
queryString = queryString + " END " + "\r\n";
return queryString;
}
private string GoodsReceiptJournalBuildDetail(GlobalEnums.GoodsReceiptTypeID goodsReceiptTypeID, bool locationFilter)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
if (goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.AllGoodsReceipt || goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice)
{
queryString = queryString + " SELECT GoodsReceiptDetails.GoodsReceiptID, GoodsReceiptDetails.GoodsReceiptDetailID, GoodsReceiptDetails.EntryDate, GoodsReceiptDetails.GoodsReceiptTypeID, GoodsReceiptDetails.VoucherID, GoodsReceiptDetails.LocationID, Locations.Code AS LocationCode, GoodsReceiptDetails.CommodityID, Commodities.Code, Commodities.Name, Commodities.CommodityTypeID, CommodityTypes.Name AS CommodityTypeName, " + "\r\n";
queryString = queryString + " GoodsReceiptDetails.Quantity, GoodsReceiptDetails.UnitPrice, GoodsReceiptDetails.VATPercent, GoodsReceiptDetails.GrossPrice, GoodsReceiptDetails.Amount, GoodsReceiptDetails.VATAmount, GoodsReceiptDetails.GrossAmount, " + "\r\n";
queryString = queryString + " PurchaseInvoices.SupplierID AS VoucherOwnerID, Suppliers.OfficialName AS VoucherOwner, PurchaseInvoices.VATInvoiceNo AS VoucherReference, PurchaseInvoices.VATInvoiceDate AS VoucherDate, PurchaseOrders.ConfirmReference AS RootVoucherReference, PurchaseOrders.ConfirmDate AS RootVoucherDate, GoodsReceiptDetails.ChassisCode, GoodsReceiptDetails.EngineCode, GoodsReceiptDetails.ColorCode " + "\r\n";
queryString = queryString + " FROM GoodsReceiptDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON GoodsReceiptDetails.EntryDate >= @LocalFromDate AND GoodsReceiptDetails.EntryDate <= @LocalToDate AND GoodsReceiptDetails.GoodsReceiptTypeID = " + (int)GlobalEnums.GoodsReceiptTypeID.PurchaseInvoice + (locationFilter ? " AND GoodsReceiptDetails.LocationID = @LocalLocationID" : "") + " AND GoodsReceiptDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " INNER JOIN PurchaseInvoices ON GoodsReceiptDetails.VoucherID = PurchaseInvoices.PurchaseInvoiceID " + "\r\n";
queryString = queryString + " INNER JOIN CommodityTypes ON Commodities.CommodityTypeID = CommodityTypes.CommodityTypeID " + "\r\n";
queryString = queryString + " INNER JOIN Customers Suppliers ON PurchaseInvoices.SupplierID = Suppliers.CustomerID " + "\r\n";
queryString = queryString + " INNER JOIN Locations ON GoodsReceiptDetails.LocationID = Locations.LocationID " + "\r\n";
queryString = queryString + " INNER JOIN PurchaseOrders ON PurchaseInvoices.PurchaseOrderID = PurchaseOrders.PurchaseOrderID " + "\r\n";
}
if (goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.AllGoodsReceipt)
queryString = queryString + " UNION ALL " + "\r\n";
if (goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.AllGoodsReceipt || goodsReceiptTypeID == GlobalEnums.GoodsReceiptTypeID.StockTransfer)
{
queryString = queryString + " SELECT GoodsReceiptDetails.GoodsReceiptID, GoodsReceiptDetails.GoodsReceiptDetailID, GoodsReceiptDetails.EntryDate, GoodsReceiptDetails.GoodsReceiptTypeID, GoodsReceiptDetails.VoucherID, GoodsReceiptDetails.LocationID, Locations.Code AS LocationCode, GoodsReceiptDetails.CommodityID, Commodities.Code, Commodities.Name, Commodities.CommodityTypeID, CommodityTypes.Name AS CommodityTypeName, " + "\r\n";
queryString = queryString + " GoodsReceiptDetails.Quantity, GoodsReceiptDetails.UnitPrice, GoodsReceiptDetails.VATPercent, GoodsReceiptDetails.GrossPrice, GoodsReceiptDetails.Amount, GoodsReceiptDetails.VATAmount, GoodsReceiptDetails.GrossAmount, " + "\r\n";
queryString = queryString + " StockTransfers.LocationID AS VoucherOwnerID, StockTransferLocations.OfficialName AS VoucherOwner, StockTransfers.Reference AS VoucherReference, StockTransfers.EntryDate AS VoucherDate, TransferOrders.Reference AS RootVoucherReference, TransferOrders.EntryDate AS RootVoucherDate, GoodsReceiptDetails.ChassisCode, GoodsReceiptDetails.EngineCode, GoodsReceiptDetails.ColorCode " + "\r\n";
queryString = queryString + " FROM GoodsReceiptDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON GoodsReceiptDetails.EntryDate >= @LocalFromDate AND GoodsReceiptDetails.EntryDate <= @LocalToDate AND GoodsReceiptDetails.GoodsReceiptTypeID = " + (int)GlobalEnums.GoodsReceiptTypeID.StockTransfer + (locationFilter ? " AND GoodsReceiptDetails.LocationID = @LocalLocationID" : "") + " AND GoodsReceiptDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " INNER JOIN StockTransfers ON GoodsReceiptDetails.VoucherID = StockTransfers.StockTransferID " + "\r\n";
queryString = queryString + " INNER JOIN CommodityTypes ON Commodities.CommodityTypeID = CommodityTypes.CommodityTypeID " + "\r\n";
queryString = queryString + " INNER JOIN Locations StockTransferLocations ON StockTransfers.LocationID = StockTransferLocations.LocationID " + "\r\n";
queryString = queryString + " INNER JOIN Locations ON GoodsReceiptDetails.LocationID = Locations.LocationID " + "\r\n";
queryString = queryString + " LEFT JOIN TransferOrders ON StockTransfers.TransferOrderID = TransferOrders.TransferOrderID " + "\r\n";
}
queryString = queryString + " END " + "\r\n";
return queryString;
}
private void GetStockTransferIndexesBUILDSQL(string storedProcedureName, GlobalEnums.StockTransferTypeID stockTransferTypeID, GlobalEnums.NmvnTaskID nmvnTaskID)
{
string queryString;
queryString = " @AspUserID nvarchar(128), @FromDate DateTime, @ToDate DateTime " + "\r\n";
queryString = queryString + " WITH ENCRYPTION " + "\r\n";
queryString = queryString + " AS " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT Locations.Code AS LocationCode, StockTransfers.StockTransferID, CAST(StockTransfers.EntryDate AS DATE) AS EntryDate, StockTransfers.Reference, Warehouses.Code AS WarehouseCode, StockTransfers.TotalQuantity, StockTransfers.Description, TransferOrders.Reference AS TransferOrderReference, TransferOrders.EntryDate AS TransferOrderEntryDate, TransferOrders.RequestedDate AS TransferOrderRequestedDate " + "\r\n";
queryString = queryString + " FROM StockTransfers INNER JOIN" + "\r\n";
queryString = queryString + " Locations ON StockTransfers.StockTransferTypeID = " + (int)stockTransferTypeID + " AND StockTransfers.EntryDate >= @FromDate AND StockTransfers.EntryDate <= @ToDate AND StockTransfers.OrganizationalUnitID IN (SELECT AccessControls.OrganizationalUnitID FROM AccessControls INNER JOIN AspNetUsers ON AccessControls.UserID = AspNetUsers.UserID WHERE AspNetUsers.Id = @AspUserID AND AccessControls.NMVNTaskID = " + (int)nmvnTaskID + " AND AccessControls.AccessLevel > 0) AND Locations.LocationID = StockTransfers.LocationID INNER JOIN " + "\r\n";
queryString = queryString + " Warehouses ON StockTransfers.WarehouseID = Warehouses.WarehouseID LEFT JOIN " + "\r\n";
queryString = queryString + " TransferOrders ON StockTransfers.TransferOrderID = TransferOrders.TransferOrderID " + "\r\n";
queryString = queryString + " " + "\r\n";
queryString = queryString + " END " + "\r\n";
this.totalBikePortalsEntities.CreateStoredProcedure(storedProcedureName, queryString);
}
private string SalesInvoiceJournalBuildDetail(GlobalEnums.CommodityTypeID commodityTypeID, bool locationFilter)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT SalesInvoiceDetails.EntryDate, Customers.CustomerID, Customers.Name AS CustomerName, Commodities.CommodityID, Commodities.Code, Commodities.Name, SalesInvoiceDetails.CommodityTypeID, SalesInvoiceDetails.WarehouseID, " + "\r\n";
queryString = queryString + " Locations.Code AS LocationCode, VWCommodityCategories.CommodityCategoryID, VWCommodityCategories.Name1 AS CommodityCategory1, VWCommodityCategories.Name2 AS CommodityCategory2, VWCommodityCategories.Name3 AS CommodityCategory3, " + "\r\n";
queryString = queryString + " SalesInvoiceDetails.Quantity, SalesInvoiceDetails.DiscountPercent, SalesInvoiceDetails.UnitPrice, SalesInvoiceDetails.Amount, SalesInvoiceDetails.VATAmount, SalesInvoiceDetails.GrossAmount, " + "\r\n";
if (commodityTypeID == GlobalEnums.CommodityTypeID.Vehicles)
queryString = queryString + " SalesInvoiceDetails.ServiceInvoiceID, GoodsReceiptDetails.ChassisCode, GoodsReceiptDetails.EngineCode, GoodsReceiptDetails.ColorCode, GoodsReceiptDetails.UnitPrice AS CostPrice " + "\r\n";
else
if (commodityTypeID == GlobalEnums.CommodityTypeID.Parts || commodityTypeID == GlobalEnums.CommodityTypeID.Consumables)
queryString = queryString + " SalesInvoiceDetails.ServiceInvoiceID, '' AS ChassisCode, '' AS EngineCode, '' AS ColorCode, WarehouseBalancePrice.UnitPrice AS CostPrice " + "\r\n";
else
queryString = queryString + " SalesInvoiceDetails.SalesInvoiceID AS ServiceInvoiceID, '' AS ChassisCode, '' AS EngineCode, '' AS ColorCode, 0 AS CostPrice " + "\r\n";
queryString = queryString + " FROM SalesInvoiceDetails INNER JOIN " + "\r\n";
queryString = queryString + " Commodities ON SalesInvoiceDetails.EntryDate >= @FromDate AND SalesInvoiceDetails.EntryDate <= @ToDate AND SalesInvoiceDetails.CommodityTypeID = " + (int)commodityTypeID + (locationFilter ? " AND SalesInvoiceDetails.LocationID = @LocationID" : "") + " AND SalesInvoiceDetails.CommodityID = Commodities.CommodityID INNER JOIN " + "\r\n";
queryString = queryString + " Customers ON SalesInvoiceDetails.CustomerID = Customers.CustomerID INNER JOIN " + "\r\n";
queryString = queryString + " Locations ON SalesInvoiceDetails.LocationID = Locations.LocationID INNER JOIN " + "\r\n";
queryString = queryString + " VWCommodityCategories ON Commodities.CommodityCategoryID = VWCommodityCategories.CommodityCategoryID " + "\r\n";
if (commodityTypeID == GlobalEnums.CommodityTypeID.Vehicles)
queryString = queryString + " INNER JOIN GoodsReceiptDetails ON SalesInvoiceDetails.GoodsReceiptDetailID = GoodsReceiptDetails.GoodsReceiptDetailID " + "\r\n";
else
if (commodityTypeID == GlobalEnums.CommodityTypeID.Parts || commodityTypeID == GlobalEnums.CommodityTypeID.Consumables)
queryString = queryString + " INNER JOIN WarehouseBalancePrice ON SalesInvoiceDetails.CommodityID = WarehouseBalancePrice.CommodityID AND MONTH(SalesInvoiceDetails.EntryDate) = MONTH(WarehouseBalancePrice.EntryDate) AND YEAR(SalesInvoiceDetails.EntryDate) = YEAR(WarehouseBalancePrice.EntryDate) " + "\r\n";
queryString = queryString + " END " + "\r\n";
return queryString;
}
private string SalesInvoiceJournalBuild(GlobalEnums.CommodityTypeID commodityTypeID)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF (@LocationID = 0) " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(commodityTypeID, false) + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(commodityTypeID, true) + "\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;
}
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 SalesInvoiceJournalBuildMaster(GlobalEnums.SalesInvoiceTypeID salesInvoiceTypeID, bool locationFilter)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF (@LocalWithAccountInvoice = 0) " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF (@LocalIncludePromotionID = 0) " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(salesInvoiceTypeID, locationFilter, false, 0) + "\r\n";
queryString = queryString + " ELSE IF (@LocalIncludePromotionID = -1) " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(salesInvoiceTypeID, locationFilter, false, -1) + "\r\n";
queryString = queryString + " ELSE " + "\r\n"; //(@LocalIncludePromotionID = 1)
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(salesInvoiceTypeID, locationFilter, false, 1) + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF (@LocalIncludePromotionID = 0) " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(salesInvoiceTypeID, locationFilter, true, 0) + "\r\n";
queryString = queryString + " ELSE IF (@LocalIncludePromotionID = -1) " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(salesInvoiceTypeID, locationFilter, true, -1) + "\r\n";
queryString = queryString + " ELSE " + "\r\n"; //(@LocalIncludePromotionID = 1)
queryString = queryString + " " + this.SalesInvoiceJournalBuildDetail(salesInvoiceTypeID, locationFilter, true, 1) + "\r\n";
queryString = queryString + " END " + "\r\n";
queryString = queryString + " END " + "\r\n";
return queryString;
}
private string SalesInvoiceJournalBuildDetail(GlobalEnums.SalesInvoiceTypeID salesInvoiceTypeID, bool locationFilter, bool withAccountInvoice, int includePromotionID)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " SELECT SalesInvoiceDetails.SalesInvoiceDetailID, SalesInvoiceDetails.EntryDate, Customers.CustomerID, Customers.Name AS CustomerName, Customers.Birthday, Customers.IsFemale, Customers.Telephone, Customers.Facsimile, Customers.AddressNo, EntireTerritories.Name2 AS DistrictName, EntireTerritories.Name1 AS ProvinceName, CustomerCategories.Name AS CustomerCategoryName, " + "\r\n";
queryString = queryString + " Commodities.CommodityID, Commodities.Code, Commodities.Name, SalesInvoiceDetails.CommodityTypeID, SalesInvoiceDetails.WarehouseID, IIF(SalesInvoiceDetails.SalesInvoiceTypeID = " + (int)GlobalEnums.SalesInvoiceTypeID.ServicesInvoice + ", SalesInvoiceDetails.SalesInvoiceID, SalesInvoiceDetails.ServiceInvoiceID) AS ServiceInvoiceID, " + "\r\n";
queryString = queryString + " Locations.Code AS LocationCode, VWCommodityCategories.CommodityCategoryID, VWCommodityCategories.Name1 AS CommodityCategory1, VWCommodityCategories.Name2 AS CommodityCategory2, VWCommodityCategories.Name3 AS CommodityCategory3, " + "\r\n";
if (withAccountInvoice)
queryString = queryString + " AccountInvoices.AccountInvoiceID, VATCustomers.Name AS VATCustomerName, VATCustomers.VATCode, AccountInvoices.VATInvoiceNo, AccountInvoices.VATInvoiceDate, AccountInvoices.VATInvoiceSeries, AccountInvoices.Description AS VATDescription, " + "\r\n";
else
queryString = queryString + " NULL AS AccountInvoiceID, NULL AS VATCustomerName, NULL AS VATCode, NULL AS VATInvoiceNo, NULL AS VATInvoiceDate, NULL AS VATInvoiceSeries, NULL AS VATDescription, " + "\r\n";
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.AllInvoice || salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.VehiclesInvoice)
queryString = queryString + " GoodsReceiptDetails.ChassisCode, GoodsReceiptDetails.EngineCode, GoodsReceiptDetails.ColorCode, " + "\r\n";
else
queryString = queryString + " NULL AS ChassisCode, NULL AS EngineCode, NULL AS ColorCode, " + "\r\n";
queryString = queryString + " SalesInvoiceDetails.PromotionID, " + (includePromotionID == 1 ? " Promotions.Code AS PromotionCode, Promotions.Name AS PromotionName, " : " NULL AS PromotionCode, NULL AS PromotionName, ") + "\r\n";
queryString = queryString + " SalesInvoiceDetails.Quantity, SalesInvoiceDetails.DiscountPercent, SalesInvoiceDetails.UnitPrice, SalesInvoiceDetails.Amount, SalesInvoiceDetails.VATPercent, SalesInvoiceDetails.VATAmount, SalesInvoiceDetails.GrossAmount, " + "\r\n";
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.AllInvoice)
queryString = queryString + " IIF(SalesInvoiceDetails.SalesInvoiceTypeID = " + (int)GlobalEnums.SalesInvoiceTypeID.VehiclesInvoice + ", GoodsReceiptDetails.UnitPrice, (IIF(SalesInvoiceDetails.SalesInvoiceTypeID = " + (int)GlobalEnums.SalesInvoiceTypeID.PartsInvoice + ", WarehouseBalancePrice.UnitPrice, 0))) AS CostPrice " + "\r\n";
else
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.VehiclesInvoice)
queryString = queryString + " GoodsReceiptDetails.UnitPrice AS CostPrice " + "\r\n";
else
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.PartsInvoice)
queryString = queryString + " WarehouseBalancePrice.UnitPrice AS CostPrice " + "\r\n";
else //salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.ServicesInvoice
queryString = queryString + " 0 AS CostPrice " + "\r\n";
queryString = queryString + " FROM SalesInvoiceDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON " + (includePromotionID == -1 ? "(SalesInvoiceDetails.IsBonus IS NULL OR SalesInvoiceDetails.IsBonus = 0) AND " : "") + " SalesInvoiceDetails.EntryDate >= @LocalFromDate AND SalesInvoiceDetails.EntryDate <= @LocalToDate " + (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.AllInvoice ? "" : " AND SalesInvoiceDetails.SalesInvoiceTypeID = @LocalSalesInvoiceTypeID") + (locationFilter ? " AND SalesInvoiceDetails.LocationID = @LocalLocationID" : "") + " AND SalesInvoiceDetails.CommodityID = Commodities.CommodityID " + "\r\n";
if (withAccountInvoice)
{
queryString = queryString + " INNER JOIN AccountInvoiceDetails ON SalesInvoiceDetails.SalesInvoiceDetailID = AccountInvoiceDetails.SalesInvoiceDetailID " + "\r\n";
queryString = queryString + " INNER JOIN AccountInvoices ON AccountInvoiceDetails.AccountInvoiceID = AccountInvoices.AccountInvoiceID " + "\r\n";
queryString = queryString + " INNER JOIN Customers VATCustomers ON AccountInvoices.CustomerID = VATCustomers.CustomerID " + "\r\n";
}
if (includePromotionID == 1)
queryString = queryString + " INNER JOIN Promotions ON SalesInvoiceDetails.IsBonus = 1 AND SalesInvoiceDetails.PromotionID = Promotions.PromotionID " + "\r\n";
queryString = queryString + " INNER JOIN Customers ON SalesInvoiceDetails.CustomerID = Customers.CustomerID " + "\r\n";
queryString = queryString + " INNER JOIN CustomerCategories ON Customers.CustomerCategoryID = CustomerCategories.CustomerCategoryID " + "\r\n";
queryString = queryString + " INNER JOIN EntireTerritories ON Customers.TerritoryID = EntireTerritories.TerritoryID " + "\r\n";
queryString = queryString + " INNER JOIN Locations ON SalesInvoiceDetails.LocationID = Locations.LocationID " + "\r\n";
queryString = queryString + " INNER JOIN VWCommodityCategories ON Commodities.CommodityCategoryID = VWCommodityCategories.CommodityCategoryID " + "\r\n";
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.AllInvoice)
{
queryString = queryString + " LEFT JOIN GoodsReceiptDetails ON SalesInvoiceDetails.GoodsReceiptDetailID = GoodsReceiptDetails.GoodsReceiptDetailID " + "\r\n";
queryString = queryString + " LEFT JOIN WarehouseBalancePrice ON SalesInvoiceDetails.CommodityID = WarehouseBalancePrice.CommodityID AND MONTH(SalesInvoiceDetails.EntryDate) = MONTH(WarehouseBalancePrice.EntryDate) AND YEAR(SalesInvoiceDetails.EntryDate) = YEAR(WarehouseBalancePrice.EntryDate) " + "\r\n";
}
else
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.VehiclesInvoice)
queryString = queryString + " INNER JOIN GoodsReceiptDetails ON SalesInvoiceDetails.GoodsReceiptDetailID = GoodsReceiptDetails.GoodsReceiptDetailID " + "\r\n";
else
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.PartsInvoice)
queryString = queryString + " INNER JOIN WarehouseBalancePrice ON SalesInvoiceDetails.CommodityID = WarehouseBalancePrice.CommodityID AND MONTH(SalesInvoiceDetails.EntryDate) = MONTH(WarehouseBalancePrice.EntryDate) AND YEAR(SalesInvoiceDetails.EntryDate) = YEAR(WarehouseBalancePrice.EntryDate) " + "\r\n";
queryString = queryString + " END " + "\r\n";
return queryString;
}
private string SalesInvoiceJournalBuild(GlobalEnums.SalesInvoiceTypeID salesInvoiceTypeID)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF (@LocalLocationID = 0) " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildMaster(salesInvoiceTypeID, false) + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceJournalBuildMaster(salesInvoiceTypeID, true) + "\r\n";
queryString = queryString + " END " + "\r\n";
return queryString;
}
private string SalesInvoiceByServiceContractBuildWHERE(GlobalEnums.SalesInvoiceTypeID salesInvoiceTypeID, bool locationFilter, string tableName)
{
return tableName + ".EntryDate >= @LocalFromDate AND " + tableName + ".EntryDate <= @LocalToDate " + (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.AllInvoice ? "" : " AND " + tableName + ".SalesInvoiceTypeID = @LocalSalesInvoiceTypeID") + (locationFilter ? " AND " + tableName + ".LocationID = @LocalLocationID" : "");
}
private string SalesInvoiceByServiceContractBuildMaster(GlobalEnums.SalesInvoiceTypeID salesInvoiceTypeID, bool isRegularCheckUps, bool includeVehiclesInvoice)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
queryString = queryString + " IF (@LocalLocationID = 0) " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceByServiceContractBuildDetail(salesInvoiceTypeID, false, isRegularCheckUps, includeVehiclesInvoice) + "\r\n";
queryString = queryString + " ELSE " + "\r\n";
queryString = queryString + " " + this.SalesInvoiceByServiceContractBuildDetail(salesInvoiceTypeID, true, isRegularCheckUps, includeVehiclesInvoice) + "\r\n";
queryString = queryString + " END " + "\r\n";
return queryString;
}
private string SalesInvoiceByServiceContractBuildDetail(GlobalEnums.SalesInvoiceTypeID salesInvoiceTypeID, bool locationFilter, bool isRegularCheckUps, bool includeVehiclesInvoice)
{
string queryString = "";
queryString = queryString + " BEGIN " + "\r\n";
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.ServicesInvoice)
{
queryString = queryString + " INSERT INTO @SalesInvoiceFilter SELECT SalesInvoiceID FROM SalesInvoices WHERE " + this.SalesInvoiceByServiceContractBuildWHERE(salesInvoiceTypeID, locationFilter, "SalesInvoices") + "\r\n";
queryString = queryString + " INSERT INTO @SalesInvoiceFilter SELECT SalesInvoiceID FROM SalesInvoices WHERE ServiceInvoiceID IN (SELECT SalesInvoiceID FROM @SalesInvoiceFilter) " + "\r\n";
}
queryString = queryString + " SELECT SalesInvoiceDetails.SalesInvoiceID, SalesInvoiceDetails.SalesInvoiceDetailID, CASE WHEN SalesInvoiceDetails.SalesInvoiceTypeID = " + (int)GlobalEnums.SalesInvoiceTypeID.ServicesInvoice + " THEN SalesInvoiceDetails.SalesInvoiceID ELSE SalesInvoiceDetails.ServiceInvoiceID END AS ServiceInvoiceID, SalesInvoiceDetails.EntryDate, CAST(SalesInvoiceDetails.EntryDate AS Date) AS EntryDateONLY, CAST(" + (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.ServicesInvoice ? "ISNULL(ServiceInvoices.EntryDate, SalesInvoiceDetails.EntryDate)" : "SalesInvoiceDetails.EntryDate") + " AS Date) AS ServiceEntryDate, SalesInvoiceDetails.LocationID, Locations.Code AS LocationCode, SalesInvoiceDetails.SalesInvoiceTypeID, " + "\r\n";
queryString = queryString + " SalesInvoiceDetails.CommodityID, Commodities.Code, Commodities.Name, SalesInvoiceDetails.Quantity, SalesInvoiceDetails.UnitPrice, SalesInvoiceDetails.VATPercent, SalesInvoiceDetails.GrossPrice, SalesInvoiceDetails.Amount, SalesInvoiceDetails.VATAmount, SalesInvoiceDetails.GrossAmount, " + "\r\n";
queryString = queryString + " ServiceContracts.CommodityID AS VehicleID, ServiceVehicles.Code AS VehicleCode, ServiceVehicles.Name AS VehicleName, ServiceContracts.ChassisCode, ServiceContracts.EngineCode, ServiceContracts.ColorCode, ServiceContracts.LicensePlate, SalesInvoiceDetails.CurrentMeters, " + "\r\n";
queryString = queryString + " " + (includeVehiclesInvoice ? "ISNULL(VehicleInvoiceDetails.EntryDate, ServiceContracts.PurchaseDate)" : "ServiceContracts.PurchaseDate") + " AS PurchaseDate, " + (includeVehiclesInvoice ? "ISNULL(VehicleLocations.OfficialName, ServiceContracts.AgentName)" : "ServiceContracts.AgentName") + " AS AgentName, " + (includeVehiclesInvoice ? "VehicleLocations.OfficialName" : "''") + " AS VehicleLocationName, " + "\r\n";
queryString = queryString + " Customers.CustomerID, Customers.Name AS CustomerName, Customers.Birthday, Customers.IsFemale, Customers.VATCode, Customers.Telephone, Customers.Facsimile, Customers.AddressNo, EntireTerritories.Name2 AS DistrictName, EntireTerritories.Name1 AS ProvinceName, CustomerCategories.Name AS CustomerCategoryName " + "\r\n";
queryString = queryString + " FROM SalesInvoiceDetails " + "\r\n";
queryString = queryString + " INNER JOIN Commodities ON " + (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.ServicesInvoice ? "SalesInvoiceDetails.SalesInvoiceID IN (SELECT SalesInvoiceID FROM @SalesInvoiceFilter)" : this.SalesInvoiceByServiceContractBuildWHERE(salesInvoiceTypeID, locationFilter, "SalesInvoiceDetails")) + (isRegularCheckUps ? " AND Commodities.IsRegularCheckUps = 1" : "") + " AND SalesInvoiceDetails.CommodityID = Commodities.CommodityID " + "\r\n";
queryString = queryString + " INNER JOIN ServiceContracts ON SalesInvoiceDetails.ServiceContractID = ServiceContracts.ServiceContractID " + "\r\n";
queryString = queryString + " INNER JOIN Commodities AS ServiceVehicles ON ServiceContracts.CommodityID = ServiceVehicles.CommodityID " + "\r\n";
queryString = queryString + " INNER JOIN Customers ON ServiceContracts.CustomerID = Customers.CustomerID " + "\r\n";
queryString = queryString + " INNER JOIN CustomerCategories ON Customers.CustomerCategoryID = CustomerCategories.CustomerCategoryID " + "\r\n";
queryString = queryString + " INNER JOIN EntireTerritories ON Customers.TerritoryID = EntireTerritories.TerritoryID " + "\r\n";
queryString = queryString + " INNER JOIN Locations ON SalesInvoiceDetails.LocationID = Locations.LocationID " + "\r\n";
if (includeVehiclesInvoice)
{
queryString = queryString + " LEFT JOIN SalesInvoiceDetails VehicleInvoiceDetails ON ServiceContracts.SalesInvoiceDetailID = VehicleInvoiceDetails.SalesInvoiceDetailID " + "\r\n";
queryString = queryString + " LEFT JOIN Locations VehicleLocations ON VehicleInvoiceDetails.LocationID = VehicleLocations.LocationID " + "\r\n";
}
if (salesInvoiceTypeID == GlobalEnums.SalesInvoiceTypeID.ServicesInvoice)
{
queryString = queryString + " LEFT JOIN SalesInvoices AS ServiceInvoices ON SalesInvoiceDetails.ServiceInvoiceID = ServiceInvoices.SalesInvoiceID " + "\r\n";
}
queryString = queryString + " END " + "\r\n";
return queryString;
}
public AccessLevelAuthorizeAttribute(GlobalEnums.AccessLevel accessLevel)
{
this.accessLevel = accessLevel;
}
protected IQueryable<TransferOrder> GetTransferOrders(GlobalEnums.NmvnTaskID nmvnTaskID, GlobalEnums.StockTransferTypeID stockTransferTypeID)
{
return this.transferOrderRepository.Loading(User.Identity.GetUserId(), nmvnTaskID).Where(ww => ww.StockTransferTypeID == (int)stockTransferTypeID).Include(w => w.Warehouse).Include(l => l.Location);
}
