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); }