public async Task DownloadCPSalesDateRange(DateTime startDate, DateTime endDate, int docSetId)
{
StatusModel.Timer("Downloading CP Sales Data...");
using (var ctx = new WaterNutDBEntities()
{
CommandTimeout = 0
})
{
await ctx.ExecuteStoreCommandAsync(@"
DELETE From EntryData
Where EntryDataId in (SELECT distinct INVNO
FROM CounterPointSales
WHERE (DATE >= @StartDate and DATE <= @EndDate))
INSERT INTO EntryData
(EntryDataId, EntryDataDate)
SELECT distinct INVNO, DATE
FROM CounterPointSales
WHERE (DATE >= @StartDate and DATE <= @EndDate)
INSERT INTO AsycudaDocumentSetEntryData
(EntryDataId, AsycudaDocumentSetId)
SELECT distinct INVNO, @AsycudaDocumentSetId
FROM CounterPointSales
WHERE (DATE >= @StartDate and DATE <= @EndDate)
DELETE From EntryData_Sales
Where EntryDataId in (SELECT distinct INVNO
FROM CounterPointSales
WHERE (DATE >= @StartDate and DATE <= @EndDate))
INSERT INTO EntryData_Sales
(EntryDataId, INVNumber, TaxAmount,CustomerName)
SELECT distinct INVNO, INVNO AS Expr1,TAX_AMT, [CUSTOMER NAME]
FROM CounterPointSales
WHERE (DATE >= @StartDate and DATE <= @EndDate)
INSERT INTO InventoryItems
(ItemNumber, Description)
SELECT CounterPointSalesDetails.ITEM_NO, MAX(CounterPointSalesDetails.ITEM_DESCR) AS ITEM_DESCR
FROM CounterPointSalesDetails LEFT OUTER JOIN
InventoryItems AS InventoryItems_1 ON CounterPointSalesDetails.ITEM_NO = InventoryItems_1.ItemNumber
WHERE (CounterPointSalesDetails.DATE >= @StartDate) AND (CounterPointSalesDetails.DATE <= @EndDate) AND (InventoryItems_1.ItemNumber IS NULL)
GROUP BY CounterPointSalesDetails.ITEM_NO
HAVING (LEFT(CounterPointSalesDetails.ITEM_NO, 1) <> '*')
INSERT INTO EntryDataDetails
(EntryDataId, LineNumber, ItemNumber, Quantity, Units, ItemDescription, Cost, UnitWeight, QtyAllocated)
SELECT INVNO, SEQ_NO, ITEM_NO, QUANTITY, QTY_UNIT, ITEM_DESCR, COST, isnull(UNIT_WEIGHT,0),0
FROM CounterPointSalesDetails
WHERE (DATE >= @StartDate and DATE <= @EndDate) AND Left(CounterPointSalesDetails.ITEM_NO,1) <>'*'",
new SqlParameter("@AsycudaDocumentSetId", docSetId),
new SqlParameter("@StartDate", startDate),
new SqlParameter("@EndDate", endDate)).ConfigureAwait(false);
}
StatusModel.StopStatusUpdate();
}
public async Task DownloadCPSales(CounterPointSales c, int docSetId)
{
//WaterNutDBEntities db = BaseDataModel.db;//new WaterNutDBEntities(Properties.Settings.Default.WaterNutDBEntitiesConnection);
if (docSetId != 0)
{
StatusModel.Timer("Downloading CP Sales...");
using (var ctx = new WaterNutDBEntities()
{
CommandTimeout = 0
})
{
await ctx.ExecuteStoreCommandAsync(@"
Delete from EntryData
Where EntryDataId = @INVNumber
Delete from EntryData_Sales
Where EntryDataId = @INVNumber
INSERT INTO EntryData
(EntryDataId, EntryDataDate)
SELECT INVNO, DATE
FROM CounterPointSales
WHERE (INVNO = @INVNumber)
INSERT INTO AsycudaDocumentSetEntryData
(EntryDataId, AsycudaDocumentSetId)
SELECT INVNO, @AsycudaDocumentSetId
FROM CounterPointSales
WHERE (INVNO = @INVNumber)
INSERT INTO EntryData_Sales
(EntryDataId, INVNumber, TaxAmount,CustomerName)
SELECT distinct INVNO, INVNO AS Expr1,TAX_AMT, [CUSTOMER NAME]
FROM CounterPointSales
WHERE (INVNO = @INVNumber)
INSERT INTO InventoryItems
(ItemNumber, Description)
SELECT CounterPointSalesDetails.ITEM_NO, CounterPointSalesDetails.ITEM_DESCR
FROM CounterPointSalesDetails LEFT OUTER JOIN
InventoryItems AS InventoryItems_1 ON CounterPointSalesDetails.ITEM_NO = InventoryItems_1.ItemNumber
WHERE (CounterPointSalesDetails.INVNO = @INVNumber) AND (InventoryItems_1.ItemNumber IS NULL) AND Left(CounterPointSalesDetails.ITEM_NO,1) <>'*'
INSERT INTO EntryDataDetails
(EntryDataId, LineNumber, ItemNumber, Quantity, Units, ItemDescription, Cost, UnitWeight, QtyAllocated)
SELECT INVNO, SEQ_NO, ITEM_NO, QUANTITY, QTY_UNIT, ITEM_DESCR, COST, isnull(UNIT_WEIGHT,0), 0
FROM CounterPointSalesDetails
WHERE (INVNO = @INVNumber) AND Left(CounterPointSalesDetails.ITEM_NO,1) <>'*'",
new SqlParameter("@AsycudaDocumentSetId", docSetId),
new SqlParameter("@INVNumber", c.InvoiceNo)).ConfigureAwait(false);
}
StatusModel.Timer("Refreshing Sales Data");
StatusModel.StopStatusUpdate();
}
}
public async Task DownloadCPO(CounterPointPOs c, int asycudaDocumentSetId)
{
if (c == null)
{
return;
}
if (asycudaDocumentSetId != 0)
{
StatusModel.Timer("Downloading CP Data...");
using (var ctx = new WaterNutDBEntities()
{
CommandTimeout = 0
})
{
await ctx.ExecuteStoreCommandAsync(@"
Delete from EntryData
Where EntryDataId = @PONumber
Delete from EntryData_PurchaseOrders
Where EntryDataId = @PONumber
INSERT INTO EntryData
(EntryDataId, EntryDataDate)
SELECT PO_NO, DATE
FROM CounterPointPOs
WHERE (PO_NO = @PONumber)
INSERT INTO AsycudaDocumentSetEntryData
(EntryDataId, AsycudaDocumentSetId)
SELECT PO_NO, @AsycudaDocumentSetId
FROM CounterPointPOs
WHERE (PO_NO = @PONumber)
INSERT INTO EntryData_PurchaseOrders
(EntryDataId, PONumber)
SELECT distinct PO_NO, PO_NO AS Expr1
FROM CounterPointPOs
WHERE (PO_NO = @PONumber)
INSERT INTO InventoryItems
(ItemNumber, Description)
SELECT Distinct CounterPointPODetails.ITEM_NO, CounterPointPODetails.ITEM_DESCR
FROM CounterPointPODetails LEFT OUTER JOIN
InventoryItems AS InventoryItems_1 ON CounterPointPODetails.ITEM_NO = InventoryItems_1.ItemNumber
WHERE (CounterPointPODetails.PO_NO = @PONumber) AND (InventoryItems_1.ItemNumber IS NULL) AND Left(CounterPointPODetails.ITEM_NO,1) <>'*'
INSERT INTO EntryDataDetails
(EntryDataId, LineNumber, ItemNumber, Quantity, Units, ItemDescription, Cost, UnitWeight, QtyAllocated)
SELECT PO_NO, SEQ_NO, ITEM_NO, ORD_QTY, ORD_UNIT, ITEM_DESCR, ORD_COST, UNIT_WEIGHT, 0
FROM CounterPointPODetails
WHERE (PO_NO = @PONumber) AND Left(CounterPointPODetails.ITEM_NO,1) <>'*'",
new SqlParameter("@AsycudaDocumentSetId", asycudaDocumentSetId), new SqlParameter("@PONumber", c.PurchaseOrderNo)).ConfigureAwait(false);
}
StatusModel.Timer("Refreshing CP Data...");
StatusModel.StopStatusUpdate();
}
else
{
throw new ApplicationException("Please Select a Asycuda Document Set before downloading PO");
}
}
