private static List <Artran> PrepareInvoiceNumbers(List <Artran> invoices, RunAction callbackProgress, bool rollback = true)
{
callbackProgress?.Invoke(new RunActionProgress()
{
Cursor = 0, NbData = invoices.Count, Message = "Prepare invoices"
});
Dictionary <string, string> numbers = new Dictionary <string, string>();
int i = 0,
maxArtrand = GetMaxInvno("artrand"),
maxArtran = GetMaxInvno();
int max = maxArtran > maxArtrand ? maxArtran : maxArtrand;
Dictionary <int, int> idSwitch = new Dictionary <int, int>();
foreach (var invoice in invoices)
{
int no = 0;
if (int.TryParse(invoice.invno, out no))
{
int newNo = 0;
if (idSwitch.ContainsKey(no))
{
newNo = idSwitch[no];
}
else
{
i++;
newNo = max + i;
idSwitch.Add(no, newNo);
}
invoice.invno = newNo.ToString();
}
}
SetRemovedInvoiceNumber(idSwitch, rollback);
callbackProgress?.Invoke(new RunActionProgress()
{
Cursor = i, NbData = invoices.Count, Message = "Prepare invoices"
});
return(invoices);
}
private static int RemoveData(List <Artran> invoices, RunAction callbackProgress = null)
{
if (invoices.Count == 0)
{
return(0);
}
int fstNo = invoices.Min(i => i.GetInvnoToInt());
foreach (var inv in invoices)
{
Console.WriteLine(inv.invno);
}
invoices = PrepareInvoiceNumbers(invoices, callbackProgress, false);
var keys = string.Join(",", invoices.Select(invoice => invoice.invno));
callbackProgress?.Invoke(new RunActionProgress()
{
Cursor = 0, NbData = keys.Length, Message = "Delete invoices"
});
if (SwitchDataFromTableToTable("artran", "artrand", keys))
{
SwitchDataFromTableToTable("arcash", "arcashd", keys);
SwitchDataFromTableToTable("ictran", "ictrand", keys, "docno", new List <string>()
{
"trantype='I'"
});
SwitchDataFromTableToTable("armaster", "armasterd", keys);
}
callbackProgress?.Invoke(new RunActionProgress()
{
Cursor = keys.Length, NbData = keys.Length, Message = "Delete invoices"
});
ReloadStockFromInvoices(invoices, false, callbackProgress);
ReloadInvoiceNumbersFrom(fstNo, callbackProgress);
return(invoices.Count);
}
public static void RestoreInvoices(List <Artran> invoices, RunAction callbackProgress)
{
int fstNo = 0;
DateTime fstDate = invoices.Min(i => i.GetInvdateToDateTime());
DBManager.RunQueryResults(string.Format("SELECT MIN(invno) invno FROM arcash WHERE invdate>='{0}'", GetStrDateDbFormat(fstDate)), (SqlDataReader r) =>
{
int.TryParse(r["invno"].ToString(), out fstNo);
});
invoices = PrepareInvoiceNumbers(invoices, callbackProgress);
var keys = string.Join(",", invoices.Select(invoice => invoice.invno));
callbackProgress?.Invoke(new RunActionProgress()
{
Cursor = 0, NbData = keys.Length, Message = "Restore invoices"
});
if (SwitchDataFromTableToTable("artrand", "artran", keys))
{
SwitchDataFromTableToTable("arcashd", "arcash", keys);
SwitchDataFromTableToTable("ictrand", "ictran", keys, "docno", new List <string>()
{
"trantype='I'"
});
SwitchDataFromTableToTable("armasterd", "armaster", keys);
}
callbackProgress?.Invoke(new RunActionProgress()
{
Cursor = keys.Length, NbData = keys.Length, Message = "Restore invoices"
});
ReloadStockFromInvoices(invoices, true, callbackProgress);
ReloadInvoiceNumbersFrom(fstNo, callbackProgress);
}
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);
}
public void Update()
{
if (m_IsPause)
return;
//计算delay 时间
if (UnityEngine.Time.realtimeSinceStartup >=m_CurRunTime + m_DelayTime+m_LastPauseTime)
{
if (!IsRunning)
{
m_CurRunTime = UnityEngine.Time.realtimeSinceStartup;
m_DelayTime = 0;
StartAction?.Invoke();
}
IsRunning = true;
}
if (!IsRunning)
return;
//计算interval
if (UnityEngine.Time.realtimeSinceStartup >= m_CurRunTime + m_Pausetime)
{
m_CurRunTime = UnityEngine.Time.realtimeSinceStartup + m_Interval;
m_Pausetime = 0;
RunAction?.Invoke(m_Loop - m_CurLoop);
//Loop即使为0也执行一次
if (m_Loop != -1)
{
if (m_CurLoop >= m_Loop)
{
CompleteAction?.Invoke();
Stop();
}
m_CurLoop++;
}
}
}
public void Run(DateTime now)
{
if (IsStillRunning)
{
return;
}
LastRunTime = now;
Task.Run(() =>
{
IsStillRunning = true;
try
{
RunAction?.Invoke();
}
catch { }
IsStillRunning = false;
});
}
protected override void RunTool()
{
var runner = new ScriptyRunner(FileSystem, Environment, ProcessRunner, Tools, ProjectFilePath);
RunAction?.Invoke(runner);
}
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);
}
/// <summary>
/// 执行流程
/// </summary>
internal FlowItem Run(PenKey penKey)
{
this.PenKey = penKey;
return(RunAction?.Invoke(this));
}
private void OnRunAction()
{
RunAction?.Invoke(this, EventArgs.Empty);
}