private void UpdateWorker(RunActionProgress progress)
{
double percent = progress.NbData > 0 ? ((double)progress.Cursor / progress.NbData) * 100 : 100;
bgWorker.ReportProgress((int)percent, progress);
Thread.Sleep(1000);
}
private static void ReloadInvoiceNumbersFrom(int fromNo, RunAction callbackProgress)
{
var progress = new RunActionProgress()
{
Cursor = 0, NbData = 5, Message = "Reset invoice numbering"
};
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("TRUNCATE TABLE artrantmp");
DBManager.ExecuteQuery(string.Format("DBCC CHECKIDENT (artrantmp, RESEED, {0})", (fromNo)));
DBManager.ExecuteQuery(string.Format("INSERT INTO artrantmp(invno) SELECT -invno from arcash WHERE invno >={0} GROUP BY -invno order by MAX(invdate)", fromNo));
progress.Cursor = 1;
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("update ar SET ar.invno=(-ar.invno) FROM artran ar where invno in (select (invno * -1) FROM artrantmp)");
DBManager.ExecuteQuery("update ar SET ar.invno=artrantmp.id from artran ar inner join artrantmp on ar.invno=artrantmp.invno");
progress.Cursor = 2;
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("update ar SET ar.invno=-ar.invno FROM arcash ar where invno in (select (invno * - 1) FROM artrantmp)");
DBManager.ExecuteQuery("update ar SET ar.invno=artrantmp.id, ponum='Payment on invoice ' + CAST(artrantmp.id AS VARCHAR), octn='R' + CAST(artrantmp.id AS VARCHAR), applyto='l' + CAST(artrantmp.id AS VARCHAR), ar.receiptno=(artrantmp.id + 1) from arcash ar inner join artrantmp on ar.invno=artrantmp.invno");
progress.Cursor = 3;
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("update ic SET ic.docno=-ic.docno FROM ictran ic WHERE docno IN (SELECT (artrantmp.invno * -1) FROM artrantmp)");
DBManager.ExecuteQuery("update ic SET ic.docno=artrantmp.id, reference='Invoice ' + CAST(artrantmp.id AS VARCHAR) from ictran ic inner join artrantmp on ic.docno=artrantmp.invno WHERE ic.trantype='l'");
progress.Cursor = 4;
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("update ar SET ar.invno=-ar.invno from armaster ar WHERE ar.invno IN(SELECT (artrantmp.invno * -1) FROM artrantmp)");
DBManager.ExecuteQuery("update ar SET ar.invno=artrantmp.id, octn='l' + CAST(artrantmp.id AS VARCHAR) from armaster ar inner join artrantmp on ar.invno=artrantmp.invno");
int total = 0;
double ca = 0;
DBManager.RunQueryResults("SELECT MAX(invno) total FROM artran", (SqlDataReader reader) =>
{
int.TryParse(reader["total"].ToString(), out total);
});
DBManager.RunQueryResults("SELECT (CASE WHEN SUM(paidamt) IS NOT NULL THEN SUM(paidamt) ELSE 0 END) + (select MAX(startamt) from arshiftclose) ca FROM arcash;", (SqlDataReader r) =>
{
double.TryParse(r["ca"].ToString(), out ca);
});
//DBManager.ExecuteQuery(string.Format("update arshiftclose SET invcount={0}, totalvoucher={0}, totalamt={1};", total, ca));
DBManager.ExecuteQuery(string.Format("update sysdata SET int1={0}, int2={1}, int3={1} WHERE sysid='AR';", total, total + 1));
DBManager.ExecuteQuery(string.Format("update sysdata SET int1={0} WHERE sysid='GL';", total + 1));
progress.Cursor = 5;
callbackProgress?.Invoke(progress);
}
private static void ReloadStockFromInvoices(List <Artran> invoices, bool rollback, RunAction callbackProgress = null)
{
var consumptions = GetItemConsumptions(invoices);
var progress = new RunActionProgress()
{
Cursor = 0, NbData = 5, Message = "Reload Stock"
};
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("ALTER TABLE itemtrantmp ALTER COLUMN itemcode [nvarchar](25) COLLATE Croatian_BIN");
DBManager.ExecuteQuery("DELETE FROM itemtrantmp;");
StoreOrderedItems(consumptions, rollback);
progress.Cursor = 1;
callbackProgress?.Invoke(progress);
string op = rollback ? "+" : "-";
DBManager.ExecuteQuery(string.Format(@"UPDATE itemtrantmp SET
totalqtyorder=(SELECT SUM(artran.qtyorder) FROM artran where artran.itemcode=itemtrantmp.itemcode),
lastsale=(select MAX(artran.invdate) FROM artran where artran.itemcode=itemtrantmp.itemcode),
cost=(SELECT SUM(artran.cost * artran.qtyorder) FROM artran where artran.itemcode=itemtrantmp.itemcode),
qtystock=(
(
SELECT SUM(stockqty)
FROM ictran
where ictran.itemcode=itemtrantmp.itemcode AND trantype='R'
) -
(
SELECT (CASE WHEN SUM(artran.qtyorder) IS NOT NULL THEN SUM(artran.qtyorder) ELSE 0 END)
FROM artran where artran.itemcode=itemtrantmp.itemcode)
) {0} itemtrantmp.qtyorder
FROM itemtrantmp inner join artran on itemtrantmp.itemcode=artran.itemcode", op));
/*if (rollback)
* {
* DBManager.ExecuteQuery("UPDATE ic SET ic.tranqty=(ic.tranqty + tmp.qtyorder), ic.stockqty=(ic.tranqty + tmp.qtyorder) FROM ictran ic inner join itemtrantmp tmp on tmp.itemcode=ic.itemcode WHERE trantype='R' AND tranno=(SELECT MAX(tranno) FROM ictran ic2 where ic2.itemcode=tmp.itemcode AND trantype='R')");
* }
* else
* {
*
* /*double tranqty;
* List<string> queries = new List<string>();
* Dictionary<string, int> stocks = new Dictionary<string, int>();
*
* DBManager.ExecuteQuery("UPDATE ic SET ic.tranqty=(CASE WHEN (ic.tranqty - tmp.qtyorder > 0) THEN (ic.tranqty - tmp.qtyorder) ELSE 0 END), ic.stockqty=(CASE WHEN (ic.tranqty - tmp.qtyorder > 0) THEN (ic.tranqty - tmp.qtyorder) ELSE 0 END) FROM ictran ic inner join itemtrantmp tmp on tmp.itemcode=ic.itemcode WHERE trantype='R' AND tranno=(SELECT MAX(tranno) FROM ictran ic2 where ic2.itemcode=tmp.itemcode AND trantype='R') AND (SELECT COUNT(*) FROM ictran ic3 where ic3.itemcode=tmp.itemcode AND trantype='R') <= 1;");
*
* DBManager.RunQueryResults("SELECT ic.*, (SELECT COUNT(*) FROM ictran ic2 WHERE ic2.itemcode = ic.itemcode AND ic2.trantype = 'R') total FROM ictran ic WHERE ic.trantype='R' AND ic.itemcode IN (SELECT itemcode FROM itemtrantmp) AND (SELECT COUNT(*) FROM ictran ic3 WHERE ic3.itemcode = ic.itemcode AND ic3.trantype = 'R') > 1 ORDER BY itemcode ASC, trandate DESC", (SqlDataReader r) =>
* {
* int total = 0;
* int.TryParse(r["total"].ToString(), out total);
*
* if (!stocks.ContainsKey(r["itemcode"].ToString())) stocks.Add(r["itemcode"].ToString(), total);
*
* if (consumptions.ContainsKey(r["itemcode"].ToString()) && consumptions[r["itemcode"].ToString()] > 0 && double.TryParse(r["tranqty"].ToString(), out tranqty))
* {
* if (tranqty > consumptions[r["itemcode"].ToString()])
* {
* var totalRemaining = tranqty - consumptions[r["itemcode"].ToString()];
*
* queries.Add(string.Format("UPDATE ictran SET tranqty={0}, stockqty={0} WHERE tranno={1}", totalRemaining, r["tranno"].ToString()));
*
* consumptions[r["itemcode"].ToString()] = 0;
* }
* else
* {
* if (stocks[r["itemcode"].ToString()] > 1)
* {
* queries.Add(string.Format("DELETE FROM ictran WHERE tranno={0}", r["tranno"].ToString()));
* stocks[r["itemcode"].ToString()]--;
* consumptions[r["itemcode"].ToString()] -= tranqty;
* }
* else
* {
* queries.Add(string.Format("UPDATE ictran SET tranqty=0, stockqty=0 WHERE tranno={0}", r["tranno"].ToString()));
* consumptions[r["itemcode"].ToString()] = 0;
* }
* }
* }
* });
*
* DBManager.ExecuteQueries(queries);
* }*/
DBManager.ExecuteQuery("UPDATE icitemlocation SET ytdsaleqty=itemtrantmp.totalqtyorder, lastreceipt=(SELECT MAX(trandate) FROM ictran WHERE itemcode=icitemlocation.itemcode AND trantype='R'), ptdsaleqty=itemtrantmp.totalqtyorder, ptdsalevalue=itemtrantmp.cost, ytdsalevalue=itemtrantmp.cost, lastsale=itemtrantmp.lastsale, edtdatetime=itemtrantmp.lastsale FROM icitemlocation inner join itemtrantmp ON icitemlocation.itemcode=itemtrantmp.itemcode;");
progress.Cursor = 2;
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("UPDATE icitemqty SET issuedate=itemtrantmp.lastsale, edtdatetime=itemtrantmp.lastsale, salesynqty=itemtrantmp.totalqtyorder FROM icitemqty inner join itemtrantmp on itemtrantmp.itemcode=icitemqty.itemcode;");
progress.Cursor = 3;
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("UPDATE icitemmaster SET ytdsaleqty=itemtrantmp.totalqtyorder, lastreceipt=(SELECT MAX(trandate) FROM ictran WHERE itemcode=icitemmaster.itemcode AND trantype='R'), ptdsaleqty=itemtrantmp.totalqtyorder, ytdsalevalue=itemtrantmp.cost, lastsale=itemtrantmp.lastsale FROM icitemmaster inner join itemtrantmp on itemtrantmp.itemcode=icitemmaster.itemcode;");
progress.Cursor = 4;
callbackProgress?.Invoke(progress);
DBManager.ExecuteQuery("ALTER TABLE itemtrantmp ALTER COLUMN itemcode [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS");
progress.Cursor = 5;
callbackProgress?.Invoke(progress);
//DBManager.ExecuteQuery("UPDATE iccost SET orgqty=itemtrantmp.qtystock FROM iccost inner join itemtrantmp on itemtrantmp.itemcode=iccost.itemcode;");
//progress.Cursor = 6;
//callbackProgress?.Invoke(progress);
}
